X-Git-Url: http://git.kpe.io/?a=blobdiff_plain;f=db-sqlite%2Fsqlite-sql.lisp;h=9b7a8fceae594b08d404e4541d544fa0d1a23e6c;hb=cc92d162f24648d65ad872098353305a5baf91d7;hp=9b67f380b25cc7f9a1f35a9b3e69e23eedc2e0a0;hpb=c4da3cfcbb955395d8a556e1f89aadad696302b7;p=clsql.git

diff --git a/db-sqlite/sqlite-sql.lisp b/db-sqlite/sqlite-sql.lisp
index 9b67f38..9b7a8fc 100644
--- a/db-sqlite/sqlite-sql.lisp
+++ b/db-sqlite/sqlite-sql.lisp
@@ -41,6 +41,8 @@
   (handler-case
       (make-instance 'sqlite-database
 		     :name (database-name-from-spec connection-spec :sqlite)
+		     :database-type :sqlite
+		     :connection-spec connection-spec
 		     :sqlite-db (sqlite:sqlite-open (first connection-spec)))
     (sqlite:sqlite-error (err)
       (error 'clsql-connect-error
@@ -186,7 +188,7 @@
   ;; Query is copied from .table command of sqlite comamnd line utility.
   (remove-if #'(lambda (s)
                  (and (>= (length s) 11)
-                      (string= (subseq s 0 11) "_clsql_seq_")))
+                      (string= (subseq s 0 11) "_CLSQL_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 '()))))
@@ -205,6 +207,18 @@
                  "SELECT name FROM sqlite_master WHERE type='index' UNION ALL SELECT name FROM sqlite_temp_master WHERE type='index' ORDER BY name"
                  database nil)))
 
+(defmethod database-list-table-indexes (table (database sqlite-database)
+					&key (owner nil))
+  (declare (ignore owner))
+  (let ((*print-circle* nil))
+    (mapcar #'car 
+	    (database-query
+	     (format
+	      nil
+	      "SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='~A' UNION ALL SELECT name FROM sqlite_temp_master WHERE type='index' AND tbl_name='~A' ORDER BY name"
+	      table table)
+	     database nil))))
+
 (declaim (inline sqlite-table-info))
 (defun sqlite-table-info (table database)
   (database-query (format nil "PRAGMA table_info('~A')" table)
@@ -225,29 +239,30 @@
 	return (third field-info)))
 
 (defun %sequence-name-to-table-name (sequence-name)
-  (concatenate 'string "_clsql_seq_" (sql-escape sequence-name)))
+  (concatenate 'string "_CLSQL_SEQ_" (sql-escape sequence-name)))
 
 (defun %table-name-to-sequence-name (table-name)
   (and (>= (length table-name) 11)
-       (string= (subseq table-name 0 11) "_clsql_seq_")
+       (string= (subseq table-name 0 11) "_CLSQL_SEQ_")
        (subseq table-name 11)))
 
+
 (defmethod database-create-sequence (sequence-name
 				     (database sqlite-database))
   (let ((table-name (%sequence-name-to-table-name sequence-name)))
     (database-execute-command
      (concatenate 'string "CREATE TABLE " table-name
-		  " (id INTEGER PRIMARY KEY)")
+		  " (last_value integer PRIMARY KEY, increment_by integer, min_value integer, is_called char(1))")
      database)
     (database-execute-command 
-     (format nil "INSERT INTO ~A VALUES (-1)" table-name)
+     (concatenate 'string "INSERT INTO " table-name
+		  " VALUES (1,1,1,'f')")
      database)))
 
 (defmethod database-drop-sequence (sequence-name
 				   (database sqlite-database))
   (database-execute-command
-   (concatenate 'string "DROP TABLE "
-		(%sequence-name-to-table-name sequence-name)) 
+   (concatenate 'string "DROP TABLE " (%sequence-name-to-table-name sequence-name)) 
    database))
 
 (defmethod database-list-sequences ((database sqlite-database)
@@ -261,27 +276,45 @@
            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))
-    (parse-integer
-     (caar (database-query (format nil "SELECT id from ~A" table-name)
-                           database nil)))))
+  (without-interrupts
+   (let* ((table-name (%sequence-name-to-table-name sequence-name))
+	  (tuple
+	   (car (database-query 
+		 (concatenate 'string "SELECT last_value,is_called FROM " 
+			      table-name)
+		 database
+		 :auto))))
+     (cond
+       ((char-equal (schar (second tuple) 0) #\f)
+	(database-execute-command
+	 (format nil "UPDATE ~A SET is_called='t'" table-name)
+	 database)
+	(parse-integer (car tuple)))
+       (t
+	(let ((new-pos (1+ (parse-integer (car tuple)))))
+	 (database-execute-command
+	  (format nil "UPDATE ~A SET last_value=~D" table-name new-pos)
+	  database)
+	 new-pos))))))
+	     
+(defmethod database-sequence-last (sequence-name (database sqlite-database))
+  (without-interrupts
+    (parse-integer 
+     (caar (database-query 
+	    (concatenate 'string "SELECT last_value FROM " 
+			 (%sequence-name-to-table-name sequence-name))
+	    database
+	    :auto)))))
 
 (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-sequence-last (sequence-name (database sqlite-database))
-  (declare (ignore sequence-name)))
+  (database-execute-command
+   (format nil "UPDATE ~A SET last_value=~A,is_called='t'" 
+	   (%sequence-name-to-table-name sequence-name)
+           position)
+   database)
+  position)
 
 (defmethod database-create (connection-spec (type (eql :sqlite)))
   (declare (ignore connection-spec))
@@ -296,7 +329,14 @@
 
 (defmethod database-probe (connection-spec (type (eql :sqlite)))
   (destructuring-bind (name) connection-spec
-    ;; TODO: Add a test that this file is a real sqlite database 
-    (and (probe-file name) t)))
+    ;; TODO: Add a test that this file is a real sqlite database
+    (or (string-equal ":memory:" name)
+	(and (probe-file name) t))))
+
+;;; Database capabilities
+
+(defmethod db-type-has-boolean-where? ((db-type (eql :sqlite)))
+  nil)
+