Unable to create sqlite database

[robdyck]

The documentation for opensips-cli database module describes the configuration and use of a mysql databse. I am unable to adapt those instructions to create an sqlite database. I tried adding the path to the db in the admin url. I also tried adding it to the command line like "database create sqlite:///path/to/db. As a result I am prompted with "ERROR: Bad URL, it should resemble: sqlite:///path/to/db".

When reading the sqlalchemy documentation I get the impression that sqlite isn't accessed the same way as mysql. It seems that the user, password, host and port don't apply here.

[tvandergeer]

Has there been any progress on this? I'm setting up a new system with OpenSips v3.1.1 and want to initialise/create an SQLite DB with opensips-cli, but I'm running into this issue.

Is there any workaround so I can get the sqlite DB seeded with the version table?

[robdyck]

Can you get opensipsctl? Or perhaps find the schema in the opensips-cli download and add the schema using sqlite's cli.

[tvandergeer]

Yes, this is what I did:

apt install -y sqlite3
sqlite3 opensips.sqlite < /usr/src/opensips-3.1.1/scripts/sqlite/standard-create.sql
sqlite3 opensips.sqlite < /usr/src/opensips-3.1.1/scripts/sqlite/dialog-create.sql

Thanks!

[robdyck]

I made some small progress on my own by reading the sqlite3 documentation and examining some of the python code. It should be noted that I know almost nothing of python.

Apperently sqlite3 wants 4 slashes in an absolute path such as sqlite:////usr/local/etc/opensips/sqlite. Changing the cfg to reflect this I got a slightly different result from the user module. Instead of telling me that it couldn't connect it now says ------

DEBUG: running command 'add' '['123@192.168.1.2', 'huh']'
DEBUG: connecting to sqlite://///usr/local/etc/opensips/sqlite
ERROR: failed to connect to sqlite://///usr/local/etc/opensips/sqlite
ERROR: (sqlite3.OperationalError) near "USE": syntax error
[SQL: USE opensips]
(Background on this error at: http://sqlalche.me/e/e3q8)

I don't believe sqlite3 recognizes the USE keyword. Other db engines name the database to use.

The other change I noticed is that the database module now asks me for root's password before telling that it didn't like the URL. Sqlite doesn;t require a user and password.

(opensips-cli): database add pua
DEBUG: running command 'add' '['pua']'
DEBUG: DB URL: 'sqlite://///usr/local/etc/opensips/sqlite'
Password for admin SQLite user (root): 
DEBUG: read password: ''
DEBUG: admin DB URL: 'sqlite://root:@localhost'
ERROR: Bad URL, it should resemble: sqlite:///path/to/db