Error when using :default option in sxql:create-table

[Yadaxim]

(ql:quickload 'dbi)
(ql:quickload 'sxql)
(sb-ext:run-program "/usr/bin/dropdb" '("testDB"))
(sb-ext:run-program "/usr/bin/createdb" '("testDB"))
(dbi:with-connection
    (con :postgres :database-name "testDB" :username "user" :password "pass")
  (dbi:execute
   (dbi:prepare
    con
    (sxql:yield
     (sxql:create-table :users
         ((user_id  :type 'integer
                    :primary-key t)
          (attr   :type 'integer
                    :not-null t
                    :default 5)))))))

(print " DB made ")

[fukamachi]

sxql:yield returns multiple values: a SQL and parameters.

(sxql:yield
 (sxql:create-table :users
    ((user_id  :type 'integer
               :primary-key t)
     (attr   :type 'integer
             :not-null t
             :default 5))))
;=> "CREATE TABLE users (user_id INTEGER PRIMARY KEY, attr INTEGER NOT NULL DEFAULT ?)"
;   (5)

The parameters should be passed to dbi:execute. So, this should work.

(dbi:with-connection
    (con :postgres :database-name "testDB" :username "user" :password "pass")
  (multiple-value-bind (sql binds)
      (sxql:yield
       (sxql:create-table :users
                          ((user_id  :type 'integer
                                     :primary-key t)
                           (attr   :type 'integer
                                   :not-null t
                                   :default 5))))
    (dbi:execute (dbi:prepare con sql) binds)))

[mfiano]

The above does NOT work, at least with sqlite3. (dbi:prepare con sql) will error when encountering a ?, if the statement is a CREATE TABLE statement - for others it works fine. This needs looking at.

(defun test ()
  (dbi:with-connection
      (con :sqlite3 :database-name #P"/tmp/test.db")
    (multiple-value-bind (sql binds)
        (sxql:yield
         (sxql:create-table :users
             ((user_id  :type 'integer
                        :primary-key t)
              (attr   :type 'integer
                      :not-null t
                      :default 5))))
      (dbi:execute (dbi:prepare con sql) binds))))

Results in:

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

[mfiano]

This seems to be because cl-sqlite does not allow it:

(sqlite:prepare-statement (dbi.driver::connection-handle *connection*) "CREATE TABLE user (name TEXT DEFAULT ?)")

Could not prepare an sqlite statement.
Code ERROR: near "?": syntax error.
Database: /tmp/site.db
SQL: CREATE TABLE user (name TEXT DEFAULT ?)
   [Condition of type SQLITE:SQLITE-ERROR]