Sqlite3 bug?

BIZ TMS Data Modeler
1

I am using sqlite3.exe to run the following very reduced script to successfully create a database. I was able to open and create and commit records using sqlBrowser.

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "tblContent" (
"ID" INTEGER,
"subject" TEXT,
"order" TEXT,
"topic" TEXT,
"notes" TEXT,
PRIMARY KEY("ID" AUTOINCREMENT)
);COMMIT;

Data Modeler 3..3.7.0 reports "Error processing CREATE TABLE statement at position 131.

<span style=": rgb24, 26, 27; color: rgb195, 191, 182; --darkreader-inline-:#181a1b; --darkreader-inline-color:#c3bfb6;" -darkreader-inline-="" -darkreader-inline-color="">Windows 10 all patches.
<span style=": rgb24, 26, 27; color: rgb195, 191, 182; --darkreader-inline-:#181a1b; --darkreader-inline-color:#c3bfb6;" -darkreader-inline-="" -darkreader-inline-color="">
<span style=": rgb24, 26, 27; color: rgb195, 191, 182; --darkreader-inline-:#181a1b; --darkreader-inline-color:#c3bfb6;" -darkreader-inline-="" -darkreader-inline-color="">What can I try next?
2

I wasn't very clear above. I was trying to use Data Modeler to open the resulting sqlite3 db and generate the Aurelius table classes. Can anyone confirm this error?

3

I don't think such SQL is a valid SQLite syntax? 

According to the documentation: https://www.sqlite.org/syntax/indexed-column.html you can't have the AUTOINCREMENT clause in the Primary Key clause.
The AUTOINCREMENT must be in the column definition clause:
https://www.sqlite.org/syntax/column-constraint.html, i.e.:



PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "tblContent" (
	"ID"	INTEGER PRIMARY KEY AUTOINCREMENT,
	"subject"	TEXT,
	"order"	TEXT,
	"topic"	TEXT,
	"notes"	TEXT
);COMMIT;
4

Changing the SQL statements as you directed did fix the problem.  The other SQL syntax was created by SQLite Browser.  I am noting that SQLite3.exe from the SQLite website does successfully execute the other version of the CREATE statement and adds it to the SQLite db schema.  Maybe DataModeler should also allow this alternative syntax for SQLite3?


Many thanks for your help!

-Jon Grewer
5

It seems that SQLite accepts such "non-official" syntax, but well, it's not official. Actually other tool we use for SQLite development also raises an error saying such SQL is invalid.

6

Thank you for your help. :)