[elpa] externals/triples de8867e079 07/10: Fix more fundamental differences between sqlite and emacsql

[ELPA Syncer]

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

    Fix more fundamental differences between sqlite and emacsql
    
    Previously, for unknown reasons (probably just a mistake), the built-in 
sqlite
    version of the subject column was TEXT, while the emacsql had no
    type (defaulting it to BLOB).  This fixes that discrepancy, so now the 
sqlite
    subject column also is created with no type.
    
    Additionally, the previous version fixing the data could lose data, since it
    would transform any strings starting with a number, while it should just
    transform strings that are complete numbers.
    
    Because of these changes, we now just always move the existing triples to a 
temp
    table, create a new one (the right way now), copy everything over, then go
    through the subjects one by one to make sure we only change things that are
    really numbers.
    
    This does not handle any cases where users create a database with sqlite, 
but
    transition to 0.3 using emacsql, which we assume will not happen.  At any 
rate,
    from 0.3 on, things will hopefully be correct by default.
---
 triples-upgrade.el | 67 +++++++++++++++++++++++++++++++++++++++++++++++-------
 triples.el         | 16 +++++++++----
 2 files changed, 70 insertions(+), 13 deletions(-)

diff --git a/triples-upgrade.el b/triples-upgrade.el
index 17d0f6d1c5..317bdce0a0 100644
--- a/triples-upgrade.el
+++ b/triples-upgrade.el
@@ -26,6 +26,19 @@
 ;;; Code:
 
 (require 'triples)
+(require 'rx)
+(require 'sqlite)
+
+(defun triples-upgrade-to-0.3-simplified (db)
+  "Upgrade the database to version 0.3.
+For sqlite, we want to convert the storage to be more compatible
+with emacsql's version, which had subject as a BLOB type (the
+default column type). In the builtin database, it was a TEXT
+type. This function fixes the schema."
+  (if (or (version< emacs-version "29")
+          (not (eq (type-of db) 'sqlite)))
+      (message "Upgrade is only needed for the built-in sqlite databases used 
by emacs 29+")
+    ))
 
 (defun triples-upgrade-to-0.3 (db)
   "Upgrade the DB to version 0.3.
@@ -33,19 +46,57 @@ This will convert all stringified integers stored with 
sqlite to
 actual integers. On emacs version before 29, it will not do
 anything, since only the built-in sqlite data needs upgrading.
 Callers should force a backup to happen before calling this,
-with `(triples-backup db file most-positive-fixnum)'."
+with `(triples-backup db file most-positive-fixnum)'.
+
+This function only handles the case where users transition from
+emacsql to sqlite, it is assumed that users don't transition from
+sqlite to emacsql after first creating their database.
+
+After triples version 0.3, everything should be created
+correctly, so databases created at that version or later should
+be correct by default."
   (if (or (version< emacs-version "29")
           (not (eq (type-of db) 'sqlite)))
       (message "Upgrade is only needed for the built-in sqlite databases used 
by emacs 29+")
+    (message "triples: Upgrading triples schema to 0.3")
     (triples-with-transaction
       db
-      (mapc (lambda (column)
-              (sqlite-execute
-               db
-               (format "UPDATE OR IGNORE triples SET %s = cast(REPLACE(%s, 
'\"', '') as integer) WHERE cast(REPLACE(%s, '\"', '') as integer) > 0"
-                       column column column)))
-            '("subject" "object"))
-      (message "Upgraded all stringified integers in triple database to actual 
integers"))))
+      (sqlite-execute db "ALTER TABLE triples RENAME TO triples_old")
+      (triples-setup-table-for-builtin db)
+      (sqlite-execute db "INSERT INTO triples (subject, predicate, object, 
properties) SELECT subject, predicate, object, properties FROM triples_old")
+      (sqlite-execute db "DROP TABLE triples_old"))
+    (let ((replace-approved))
+        (mapc (lambda (column)
+                ;; This would all be easier if sqlite supported REGEXP, but
+                ;; instead we have to programmatically examine each string to 
see if it
+                ;; is an integer.
+                (mapc (lambda (row)
+                        (let ((string-val (car row)))
+                          (when (string-match (rx (seq string-start (opt ?\") 
(group-n 1 (1+ digit))) (opt ?\") string-end)
+                                              string-val)
+                            (message "triples: Upgrading %s with integer 
string value %s to a real integer" column string-val)
+                            ;; Subject transformations have to be treated
+                            ;; carefully, since they could end up duplicating
+                            ;; predicates.
+                            (let ((int-val (string-to-number (match-string 1 
string-val))))
+                              (when (equal column "subject")
+                                (when (and (> (caar (sqlite-execute db "SELECT 
count(*) FROM triples WHERE subject = ? AND typeof(subject) = 'integer'"
+                                                                              
(list int-val))) 0)
+                                               (or replace-approved
+                                                   (y-or-n-p (format "triples: 
For subject %d, existing real integer subject found.  Replace for this and 
others? "
+                                                                     
int-val))))
+                                      (setq replace-approved t)
+                                        (sqlite-execute db "DELETE FROM 
triples WHERE subject = ? AND typeof(subject) = 'integer"
+                                                        (list int-val))))
+                              (sqlite-execute db (format "UPDATE OR REPLACE 
triples SET %s = cast(REPLACE(%s, '\"', '') as integer) WHERE %s = ?"
+                                                         column column column)
+                                              (list string-val))))))
+                      (sqlite-select
+                       db
+                       (format "SELECT %s from triples WHERE cast(REPLACE(%s, 
'\"', '') as integer) > 0 AND typeof(%s) = 'text' GROUP BY %s"
+                               column column column column))))
+              '("subject" "object"))
+        (message "Upgraded all stringified integers in triple database to 
actual integers"))))
 
 (provide 'triples-upgrade)
 ;; triples-upgrade ends here
diff --git a/triples.el b/triples.el
index 3ec8464982..fdb083da53 100644
--- a/triples.el
+++ b/triples.el
@@ -65,11 +65,7 @@ If FILE is nil, use `triples-default-database-filename'."
   (let ((file (or file triples-default-database-filename)))
     (pcase triples-sqlite-interface
       ('builtin (let* ((db (sqlite-open file)))
-                  (sqlite-execute db "CREATE TABLE IF NOT EXISTS 
triples(subject TEXT NOT NULL, predicate TEXT NOT NULL, object NOT NULL, 
properties TEXT NOT NULL)")
-                  (sqlite-execute db "CREATE INDEX IF NOT EXISTS subject_idx 
ON triples (subject)")
-                  (sqlite-execute db "CREATE INDEX IF NOT EXISTS 
subject_predicate_idx ON triples (subject, predicate)")
-                  (sqlite-execute db "CREATE INDEX IF NOT EXISTS 
predicate_object_idx ON triples (predicate, object)")
-                  (sqlite-execute db "CREATE UNIQUE INDEX IF NOT EXISTS 
subject_predicate_object_properties_idx ON triples (subject, predicate, object, 
properties)")
+                  (triples-setup-table-for-builtin db)
                   db))
       ('emacsql
        (require 'emacsql)
@@ -89,6 +85,16 @@ If FILE is nil, use `triples-default-database-filename'."
            (emacsql db [:create-unique-index 
subject_predicate_object_properties_idx :on triples [subject predicate object 
properties]]))
          db)))))
 
+(defun triples-setup-table-for-builtin (db)
+  "Set up the triples table in DB.
+This is a separate function due to the need to use it during
+upgrades to version 0.3"
+  (sqlite-execute db "CREATE TABLE IF NOT EXISTS triples(subject NOT NULL, 
predicate TEXT NOT NULL, object NOT NULL, properties TEXT NOT NULL)")
+  (sqlite-execute db "CREATE INDEX IF NOT EXISTS subject_idx ON triples 
(subject)")
+  (sqlite-execute db "CREATE INDEX IF NOT EXISTS subject_predicate_idx ON 
triples (subject, predicate)")
+  (sqlite-execute db "CREATE INDEX IF NOT EXISTS predicate_object_idx ON 
triples (predicate, object)")
+  (sqlite-execute db "CREATE UNIQUE INDEX IF NOT EXISTS 
subject_predicate_object_properties_idx ON triples (subject, predicate, object, 
properties)"))
+
 (defun triples-close (db)
   "Close sqlite database DB."
   (pcase triples-sqlite-interface