Sqlite3 support

[lucashpandolfo]

Setup and configuration

CL-USER> (ql:quickload 'crane)
To load "crane":
  Load 1 ASDF system:
    crane
; Loading "crane"
...
(CRANE)
CL-USER> (crane:setup
          :migrations-directory
          "/tmp/migrations/"
          :databases
          '(:main
            (:type :sqlite3
             :name "/tmp/myapp_db.db")))

(:MIGRATIONS-DIRECTORY "/tmp/migrations/" :DATABASES
 (:MAIN (:TYPE :SQLITE3 :NAME "/tmp/myapp_db.db")) :DEBUG NIL)
CL-USER> (crane:connect)
To load "dbd-sqlite3":
  Load 1 ASDF system:
    dbd-sqlite3
; Loading "dbd-sqlite3"
..
:MAIN

Works as expected.

Table creation

CL-USER> (crane:deftable ship ()
           (name :type text :uniquep t)
           (tonnage :type integer))
Query: CREATE TABLE "ship" (    "id" INTEGER INTEGER AUTOINCREMENT,     "tonnage" INTEGER,     "name" TEXT,    CONSTRAINT "crane_ship_id_nullp" CHECK ("id" IS NOT NULL),     CONSTRAINT "crane_ship_id_primaryp" PRIMARY KEY ("id"),     CONSTRAINT "crane_ship_name_uniquep" UNIQUE ("name"));

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

The generated statement:

CREATE TABLE "ship" (    "id" INTEGER INTEGER AUTOINCREMENT,     "tonnage" INTEGER,     "name" TEXT,    CONSTRAINT "crane_ship_id_nullp" CHECK ("id" IS NOT NULL),     CONSTRAINT "crane_ship_id_primaryp" PRIMARY KEY ("id"),     CONSTRAINT "crane_ship_name_uniquep" UNIQUE ("name"));

doesn't seem to be valid for sqlite. The next one works fine.

CREATE TABLE "ship" (    "id" INTEGER PRIMARY KEY AUTOINCREMENT,     "tonnage" INTEGER,     "name" TEXT,    CONSTRAINT "crane_ship_id_nullp" CHECK ("id" IS NOT NULL),  CONSTRAINT "crane_ship_name_uniquep" UNIQUE ("name"));

Another valid statement is

CREATE TABLE "ship" (    "id" INTEGER INTEGER,     "tonnage" INTEGER,     "name" TEXT,    CONSTRAINT "crane_ship_id_nullp" CHECK ("id" IS NOT NULL),     CONSTRAINT "crane_ship_id_primaryp" PRIMARY KEY ("id"),     CONSTRAINT "crane_ship_name_uniquep" UNIQUE ("name"));

But it is not autoincremental. The AUTOINCREMENT part is enforced for INTEGER PRIMARY KEY rows (just one INTEGER).

Object creation

CL-USER> 

(let ((instance (crane:create 'ship :name "Dalliance"
                              :tonnage 77)))
  ;; FIXME: It's back luck to rename a ship
  (setf (name instance) "Serenity")
  ;; Expand the cargo hold
  (incf (tonnage instance) 25)
  ;; Save these changes!
  (crane:save instance)
  ;; Time to retire
  (crane:del instance))

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

The RETURNING keyword is not valid for sqlite3. To get the id value the last_insert_rowid() function can be used.

 SELECT last_insert_rowid();

or something like

(let ((sxql:*QUOTE-CHARACTER* nil))
  (query (select :|last_insert_rowid()|)))

So if i modify create%

(defmacro create% (obj)
  `(let* ((obj ,obj)
          (table-name (table-name (class-of obj)))
          (results
           (progn (query (sxql:insert-into
                             table-name
                           (apply #'sxql.clause:make-clause
                                  (cons :set=
                                        (make-set obj)))
                                        ;(sxql:make-op :returning :id)
                           )
                   (db (class-of obj)))
                  (let ((sxql:*QUOTE-CHARACTER* nil))
                    (query (sxql:select :|last_insert_rowid()|)))))
          (id (getf (first results) :|last_insert_rowid()|)))
     ;; Query its ID
     (setf (,(intern "ID" *package*) obj) id)
     obj))

Then create works as "expected" (works for sqlite3, breaks everything else).

CL-USER> (describe (crane:create 'ship :name "Ship1" :tonnage 1))

#<SHIP {1003CB03A3}>
  [standard-object]

Slots with :INSTANCE allocation:
  ID       = 1
  TONNAGE  = 1
  NAME     = "Ship1"
; No value
CL-USER> (describe (crane:create 'ship :name "Ship2" :tonnage 2))

#<SHIP {10050B06F3}>
  [standard-object]

Slots with :INSTANCE allocation:
  ID       = 2
  TONNAGE  = 2
  NAME     = "Ship2"
; No value

Filter

CL-USER> (crane:filter 'ship)

There is no applicable method for the generic function
  #<STANDARD-GENERIC-FUNCTION CRANE.INFLATE-DEFLATE:INFLATE (3)>
when called with arguments
  ("Ship1" TEXT).
   [Condition of type SIMPLE-ERROR]

But then, i think this is an error in the manual/main page, as inflate seems to be implemented for STRING, not TEXT.

CL-USER> (crane:deftable ship ()
           (name :type string :uniquep t)
           (tonnage :type integer))
NIL
CL-USER> (crane:filter 'ship)

(#<SHIP {10068CB013}> #<SHIP {10068CBED3}>)

Cheers.

[eudoxia0]

Thank you for reporting this. I currently only test on PostgreSQL because Crane doesn't support migrations for SQLite3 yet and I'm not familiar with MySQL. I'll poke around and see how I can integrate these changes without breaking much.

[lucashpandolfo]

Ok, thanks. It's working now. The only remaining issue is the same as https://github.com/eudoxia0/crane/issues/6. So this one can be closed.

[eudoxia0]

Thank you for the feedback, and also for the code, which saved me a lot of trial and error.