r8811: add support for usql backend, integrate Marcus Pearce <ek735@soi.city.ac.uk...

[clsql.git] / db-sqlite / sqlite-usql.lisp

diff --git a/db-sqlite/sqlite-usql.lisp b/db-sqlite/sqlite-usql.lisp
index 4d66be7ca29d9782a49c49b0bf18a3a9d18eea2d..852cf923e4cfc6686b1fd430678927d80d3573b1 100644 (file)
--- a/db-sqlite/sqlite-usql.lisp
+++ b/db-sqlite/sqlite-usql.lisp
@@ -18,9 +18,57 @@
 
 (in-package :clsql-sqlite)
 
+(defmethod database-list-tables ((database sqlite-database) &key owner)
+  (declare (ignore owner))
+  ;; Query is copied from .table command of sqlite comamnd line utility.
+  (remove-if #'(lambda (s)
+                 (and (>= (length s) 10)
+                      (string= (subseq s 0 10) "_usql_seq_")))
+             (mapcar #'car (database-query
+                            "SELECT name FROM sqlite_master WHERE type='table' UNION ALL SELECT name FROM sqlite_temp_master WHERE type='table' ORDER BY name"
+                            database '()))))
+
+(defmethod database-list-views ((database sqlite-database)
+                                &key (owner nil))
+  (declare (ignore owner))
+  (mapcar #'car (database-query
+                 "SELECT name FROM sqlite_master WHERE type='view' UNION ALL SELECT name FROM sqlite_temp_master WHERE type='view' ORDER BY name"
+                 database nil)))
+
+(defmethod database-list-indexes ((database sqlite-database)
+                                  &key (owner nil))
+  (declare (ignore owner))
+  (mapcar #'car (database-query
+                 "SELECT name FROM sqlite_master WHERE type='index' UNION ALL SELECT name FROM sqlite_temp_master WHERE type='index' ORDER BY name"
+                 database nil)))
+
+(declaim (inline sqlite-table-info))
+(defun sqlite-table-info (table database)
+  (database-query (format nil "PRAGMA table_info('~A')" table)
+                         database '()))
+
+(defmethod database-list-attributes (table (database sqlite-database)
+                                           &key (owner nil))
+  (declare (ignore owner))
+  (mapcar #'(lambda (table-info) (second table-info))
+         (sqlite-table-info table database)))
+
+(defmethod database-attribute-type (attribute table 
+                                   (database sqlite-database)
+                                    &key (owner nil))
+  (declare (ignore owner))
+  (loop for field-info in (sqlite-table-info table database)
+       when (string= attribute (second field-info))
+       return (third field-info)))
+
 (defun %sequence-name-to-table-name (sequence-name)
   (concatenate 'string "_usql_seq_" (sql-escape sequence-name)))
 
+(defun %table-name-to-sequence-name (table-name)
+  (and (>= (length table-name) 10)
+       (string= (subseq table-name 0 10) "_usql_seq_")
+       (subseq table-name 10)))
+
 (defmethod database-create-sequence (sequence-name
                                     (database sqlite-database))
   (let ((table-name (%sequence-name-to-table-name sequence-name)))
@@ -39,32 +87,35 @@
                (%sequence-name-to-table-name sequence-name)) 
    database))
 
+(defmethod database-list-sequences ((database sqlite-database)
+                                    &key (owner nil))
+  (declare (ignore owner))
+  (mapcan #'(lambda (s)
+              (let ((sn (%table-name-to-sequence-name (car s))))
+                (and sn (list sn))))
+          (database-query
+           "SELECT name FROM sqlite_master WHERE type='table' UNION ALL SELECT name FROM sqlite_temp_master WHERE type='table' ORDER BY name"
+           database '())))
+
 (defmethod database-sequence-next (sequence-name (database sqlite-database))
   (let ((table-name (%sequence-name-to-table-name sequence-name)))
     (database-execute-command
      (format nil "UPDATE ~A SET id=(SELECT id FROM ~A)+1"
             table-name table-name)
-     database))
-  (sqlite:sqlite-last-insert-rowid (sqlite-db database)))
-
-(defmethod database-list-tables ((database sqlite-database) &key system-tables)
-  (declare (ignore system-tables))
-  ;; Query is copied from .table command of sqlite comamnd line utility.
-  (mapcar #'car (database-query
-                "SELECT name FROM sqlite_master WHERE type='table' UNION ALL SELECT name FROM sqlite_temp_master WHERE type='table' ORDER BY name"
-                database '())))
-
-(declaim (inline sqlite-table-info))
-(defun sqlite-table-info (table database)
-  (database-query (format nil "PRAGMA table_info('~A')" table)
-                         database '()))
+     database)
+    (sqlite:sqlite-last-insert-rowid (sqlite-db database))
+    (parse-integer
+     (caar (database-query (format nil "SELECT id from ~A" table-name)
+                           database nil)))))
 
-(defmethod database-list-attributes (table (database sqlite-database))
-  (mapcar #'(lambda (table-info) (third table-info))
-         (sqlite-table-info table database)))
+(defmethod database-set-sequence-position (sequence-name
+                                           (position integer)
+                                           (database sqlite-database))
+  (let ((table-name (%sequence-name-to-table-name sequence-name)))
+    (database-execute-command
+     (format nil "UPDATE ~A SET id=~A" table-name position)
+     database)
+    (sqlite:sqlite-last-insert-rowid (sqlite-db database))))
 
-(defmethod database-attribute-type (attribute table 
-                                   (database sqlite-database))
-  (loop for field-info in (sqlite-table-info table database)
-       when (string= attribute (second field-info))
-       return (third field-info)))
+(defmethod database-sequence-last (sequence-name (database sqlite-database))
+  (declare (ignore sequence-name database)))
\ No newline at end of file