[GH-ISSUE #270] SQLite support #221

New issue

Closed

opened 2026-02-26 09:36:52 +03:00 by kerem · 1 comment

kerem commented

2026-02-26 09:36:52 +03:00

Owner

Originally created by @slavkoja on GitHub (Jul 17, 2020).
Original GitHub issue: https://github.com/opensolutions/ViMbAdmin/issues/270

I found ViMbAdmin only recently and i found note about SQLite problem on related wiki page. While i am not very experiented with PHP nor here used DB library, i want to share SQLite specific PRIMARY KEY and Null behavior.

Basically, the SQL implies NOT NULL at PRIMARY KEY fields, but SQLite doesn't follow this and behaves differently. It allows NULL values on PRIMARY KEY fields, except if NOT NULL is specified. If NOT NULL is defined, it refuses NULL values for field at all. But when field is defined as INTEGER PRIMARY KEY (without NOT NULL), it replaces NULL value on INSERT by new, unique number. The new number is not monotonic, unless the AUTOINCREMENT is defined too. But using AUTOINCREMENT is discouraged at all, because it requires more CPU and I/O and limits max row count to whole table lifetime.

See details here https://sqlite.org/autoinc.html and here https://sqlite.org/lang_createtable.html#not_null_constraints.

I tried to find, where and how is the admin table created, but i found only the vagrant-base.sql file, where the id field is defined as BIGINT(20) NOT NULL AUTO_INCREMENT and if it is related, then the NOT NULL is root of problem, which AUTOINCREMENT doesn't solve. If this schema is related, here will be needed separate schema where the primary keys will be defined as INTEGER PRIMARY KEY, to get it work (with best performance) on SQLite.

If you feel, that this is not useful, be free to close it

regards

Originally created by @slavkoja on GitHub (Jul 17, 2020). Original GitHub issue: https://github.com/opensolutions/ViMbAdmin/issues/270 I found ViMbAdmin only recently and i found note about SQLite problem on related wiki page. While i am not very experiented with PHP nor here used DB library, i want to share SQLite specific PRIMARY KEY and Null behavior. Basically, the SQL implies NOT NULL at PRIMARY KEY fields, but SQLite doesn't follow this and behaves differently. It allows NULL values on PRIMARY KEY fields, except if NOT NULL is specified. If NOT NULL is defined, it refuses NULL values for field at all. But when field is defined as INTEGER PRIMARY KEY (without NOT NULL), it replaces NULL value on INSERT by new, unique number. The new number is not monotonic, unless the AUTOINCREMENT is defined too. But using AUTOINCREMENT is discouraged at all, because it requires more CPU and I/O and limits max row count to whole table lifetime. See details here https://sqlite.org/autoinc.html and here https://sqlite.org/lang_createtable.html#not_null_constraints. I tried to find, where and how is the admin table created, but i found only the vagrant-base.sql file, where the id field is defined as BIGINT(20) NOT NULL AUTO_INCREMENT and if it is related, then the NOT NULL is root of problem, which AUTOINCREMENT doesn't solve. If this schema is related, here will be needed separate schema where the primary keys will be defined as INTEGER PRIMARY KEY, to get it work (with best performance) on SQLite. If you feel, that this is not useful, be free to close it regards