SQL error: near "AUTOINCREMENT": syntax error

[Bigfootmech]

Hey, I could be totally wrong here. New to this.

Trying to setup an "id" field (with datatype "int", primary key, autoincrement, not null, and unique

Except, if I make it "autoincrement" and "unique", Godot-SQLite fails with an SQL syntax error

eg these work fine: native_example["id"] = {"data_type":"int", "primary_key": true, "not_null": true, "auto_increment":true} native_example["id"] = {"data_type":"int", "primary_key": true, "not_null": true, "unique": true} this one will crash: native_example["id"] = {"data_type":"int", "primary_key": true, "not_null": true, "auto_increment":true, "unique": true}

Running a "query" with a create table statement made by "DB Browser for SQLite"

CREATE TABLE "Food_Simple" (
    "id"    INTEGER NOT NULL UNIQUE,
    "name"  TEXT NOT NULL UNIQUE,
    "calories"  REAL NOT NULL,
    PRIMARY KEY("id" AUTOINCREMENT)
);

also works just fine

So I'm assuming it's some internal logic to this extension? :S

[2shady4u]

Hello @Bigfootmech,

What is the SQL syntax error?

[Bigfootmech]

Good question. Not entirely sure. How do I get Godot to show me a bigger stacktrace / more explanation?

[Bigfootmech]

I turned up the verbosity on the database, it seems that this statement is the one that fails

CREATE TABLE IF NOT EXISTS Food_Simple (id INTEGER PRIMARY KEY UNIQUE AUTOINCREMENT NOT NULL,name text NOT NULL);

An online SQL validator also complains https://www.eversql.com/sql-syntax-check-validator/

[Bigfootmech]

If I remove "Unique", the SQL statement ends up as:

CREATE TABLE IF NOT EXISTS Food_Simple (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,name text NOT NULL);

which according to SQL checkers "shouldn't" work, but somehow does :S

I'm out of my depth here

[2shady4u]

Seems like this is an order problem:

Does not work: CREATE TABLE IF NOT EXISTS Food_Simple (id INTEGER PRIMARY KEY UNIQUE AUTOINCREMENT NOT NULL,name text NOT NULL);

Works: CREATE TABLE IF NOT EXISTS Food_Simple (id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,name text NOT NULL);

I'll probably have to swap the order of AUTOINCREMENTand UNIQUEin the source code. For now, as a work-around, you can do this: db.query("CREATE TABLE IF NOT EXISTS Food_Simple (id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,name text NOT NULL);")

(Instead of using the create_table convenience method)

[2shady4u]

I have pushed a fix in c2bdbab This fix will be part of the next release.

[2shady4u]

Fix is implemented in the latest release! (v4.3)