[elpa] externals/triples ddeb338751 2/3: Fix for duplicate rows when upgrading from emacsql to builtin

[ELPA Syncer]

branch: externals/triples
commit ddeb338751fe84c1a1f1ea66be54efa7beaa15bd
Author: Andrew Hyatt <ahyatt@gmail.com>
Commit: Andrew Hyatt <ahyatt@gmail.com>

    Fix for duplicate rows when upgrading from emacsql to builtin
    
    This should fix https://github.com/ahyatt/triples/issues/5.
---
 triples-test.el | 18 ++++++++++++++++++
 triples.el      | 21 ++++++++++++++++++++-
 2 files changed, 38 insertions(+), 1 deletion(-)

diff --git a/triples-test.el b/triples-test.el
index ed2008b48b..039aa74d18 100644
--- a/triples-test.el
+++ b/triples-test.el
@@ -227,6 +227,24 @@ easily debug into it.")
                    (triples-close db))
                  ;; Just so the last close will work.
                  (setq db (triples-connect db-file))))))
+
+(ert-deftest triples-test-emacsql-to-sqlite-dup-fixing ()
+  (let ((triples-sqlite-interface 'emacsql)
+        (db-file (make-temp-file "triples-test"))
+        (db))
+    (setq triples-test-db-file db-file)
+    (setq db (triples-connect db-file))
+    (triples-add-schema db 'person '(name :base/unique t :base/type string))
+    (triples-set-type db 1 'person :name "Alice Aardvark")
+    (triples-close db)
+    (setq triples-sqlite-interface 'builtin)
+    (setq db (triples-connect db-file))
+    (triples-set-type db 1 'person :name "Alice Aardvark")
+    ;; Should just be one plist key and value, so two values. However, if we
+    ;; don't fix things up, we get two because there is a dup row.
+    (should (= 2 (length (triples-get-subject db 1))))
+    (triples-close db)
+    (delete-file db-file)))
     
 
 ;; After this we don't bother testing both with emacsql and the builtin sqlite,
diff --git a/triples.el b/triples.el
index de143ed6b6..a59516e6c7 100644
--- a/triples.el
+++ b/triples.el
@@ -64,6 +64,23 @@ This is used in upgrades and when problems are detected."
       (sqlite-execute db "INSERT INTO triples (subject, predicate, object, 
properties) SELECT DISTINCT subject, predicate, object, properties FROM 
triples_old")
       (sqlite-execute db "DROP TABLE triples_old")))
 
+(defun triples-maybe-upgrade-to-builtin (db)
+  "Check to see if DB needs to be upgraded from emacsql to builtin."
+  ;; Check to see if this was previously an emacsql database, and if so,
+  ;; change the property column to be standard for builtin sqlite.
+  (when (> (caar (sqlite-select db "SELECT COUNT(*) FROM triples WHERE 
properties = '(:t t)'"))
+           0)
+    (if (> (caar (sqlite-select db "SELECT COUNT(*) FROM triples WHERE 
properties = '()'"))
+            0)
+        (progn
+          (message "triples: detected data written with both builtin and 
emacsql, upgrading and removing duplicates")
+          ;; Where we can, let's just upgrade the old data.  However, 
sometimes we cannot due to duplicates.
+          (sqlite-execute db "UPDATE OR IGNORE triples SET properties = '()' 
WHERE properties = '(:t t)'")
+          ;; Remove any duplicates that we cannot upgrade.
+          (sqlite-execute db "DELETE FROM triples WHERE properties = '(:t 
t)'"))
+      (message "triples: detected previously used emacsql database, converting 
to builtin sqlite")
+      (sqlite-execute db "UPDATE triples SET properties = '()' WHERE 
properties = '(:t t)'"))))
+
 (defun triples-connect (&optional file)
   "Connect to the database FILE and make sure it is populated.
 If FILE is nil, use `triples-default-database-filename'."
@@ -76,7 +93,9 @@ If FILE is nil, use `triples-default-database-filename'."
     (pcase triples-sqlite-interface
       ('builtin (let* ((db (sqlite-open file)))
                   (condition-case nil
-                      (triples-setup-table-for-builtin db)
+                      (progn
+                        (triples-setup-table-for-builtin db)
+                        (triples-maybe-upgrade-to-builtin db))
                     (error
                      (message "triples: failed to ensure proper database 
tables and indexes.  Trying an automatic fix.")
                      (triples-rebuild-builtin-database db)