Auto generated primary key in sqlite3 not working

[lucashpandolfo]

(defclass tweet ()
  ((status :type text
           :initarg :status
           :accessor tweet-status)
   (user :type (varchar 32)
         :initarg :user
         :accessor tweet-user))
  (:metaclass integral:<dao-table-class>))

(integral:connect-toplevel :sqlite3
                           :database-name "/tmp/mydb.db")

(integral:ensure-table-exists 'tweet)

DB Error: near "UNSIGNED": syntax error (Code: ERROR)
   [Condition of type DBI.ERROR:<DBI-PROGRAMMING-ERROR>]

The generated query is

CREATE TABLE IF NOT EXISTS "tweet" ("%oid" BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY, "status" TEXT, "user" VARCHAR(32))

Both "BIGINT(20)" and "BIGINT UNSIGNED" works, but "BIGINT(20) UNSIGNED" not.

In MySQL the table is created, but object saving is not working due to #33 (field names are not escaped either): "SELECT %oid AS last_insert_id FROM tweet ORDER BY %oid DESC LIMIT 1"

DB Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%oid AS last_insert_id FROM tweet ORDER BY %oid DESC LIMIT 1' at line 1 (Code: 1064) [Condition of type DBI.ERROR:]

[lucashpandolfo]

Also, according to https://www.sqlite.org/lang_createtable.html, UNSIGNED BIGINT(20) should work.

[hipeta]

type.lisp

(define-column-type bigserial ()
  '(:|unsigned bigint| 20))

I found this code creates UNSIGNED BIGINT(20) string and it works correctly in sqlite3 (though it is a bit dirty...). But in mysql, it does not work, so if integral.connection:database-type (or something like it) is available in type.lisp, this issue will be solved like

(define-column-type bigserial ()
  (case (database-type)
     (:sqlite3 '(:|unsigned bigint| 20))
     (t '(:bigint 20 :unsigned))))

Sqlite3 ignores UNISGNED BIGINT and it simply turns into INTEGER, though.

[hipeta]

Or in table.lisp, replacing oid-slot-definition to

(defun oid-slot-definition ()
  `(:name %oid :col-type ,(case (database-type) (:sqlite3 'serial) (t 'bigserial)) :auto-increment t :primary-key t :readers (getoid)))

works correctly too. But this change requires all classes whose metaclass is must be evaluated after connecting database.