Handle dashes in fields and large Postgres IDs (#5)

Also tests for the same in the integration tests

4 files changed, 42 insertions, 38 deletions

diff --git a/NEWS.org b/NEWS.org
index c4cb5f1..cb2eddf 100644
--- a/NEWS.org
+++ b/NEWS.org
@@ -1,3 +1,5 @@
+* Version 0.2.1
+- Fixes for variable names with dashes and large ids for Postgres backend
 * Version 0.2
 - Add Postgres backend
 * Version 0.1
diff --git a/vecdb-integration-test.el b/vecdb-integration-test.el
index f99ef5c..3912fdd 100644
--- a/vecdb-integration-test.el
+++ b/vecdb-integration-test.el
@@ -176,43 +176,42 @@ The collection is created before BODY and deleted afterwards."
         (vecdb-delete current-provider collection)))))
 
 (vecdb-test--deftest-for-providers vecdb-test-create-exists-delete-collection
-  #'vecdb-test-create-exists-delete-collection-body
-  "Test `vecdb-create', `vecdb-exists', and `vecdb-delete'.")
+                                   #'vecdb-test-create-exists-delete-collection-body
+                                   "Test `vecdb-create', `vecdb-exists', and `vecdb-delete'.")
 
 (defun vecdb-test-upsert-get-delete-items-body (current-provider)
   "Core logic for testing upsert and get items."
   (let* ((collection-name "test-collection-ug")
          (vector-size 3)
          (items (list
-                 (make-vecdb-item :id 1 :vector [0 1 2] :payload '(:val 1))
-                 (make-vecdb-item :id 2 :vector [0 1 2] :payload '(:val 2))
-                 (make-vecdb-item :id 3 :vector [0 1 2] :payload '(:val 3)))))
-    (with-test-collection current-provider current-collection collection-name `(:vector-size ,vector-size :payload-fields ((val . integer)))
-                          (vecdb-upsert-items current-provider current-collection items t)
-                          (dolist (item items)
-                            (let ((retrieved-item (vecdb-get-item current-provider current-collection (vecdb-item-id item))))
-                              (should retrieved-item)
-                              (should (equal (vecdb-item-id item) (vecdb-item-id retrieved-item)))
-                              ;; We don't test to see if the vector is equal,
-                              ;; because it could be normalized.
-                              (should (equal (vecdb-item-payload item) (vecdb-item-payload retrieved-item)))))
+                 (make-vecdb-item :id 10000000000 :vector [0 1 2] :payload '(:my-val 1))
+                 (make-vecdb-item :id 20000000000 :vector [0 1 2] :payload '(:my-val 2))
+                 (make-vecdb-item :id 30000000000 :vector [0 1 2] :payload '(:my-val 3)))))
+    (with-test-collection current-provider current-collection collection-name `(:vector-size ,vector-size :payload-fields ((my-val . integer)))
+                          (vecdb-upsert-items current-provider current-collection items t)(dolist (item items)
+                                                                                            (let ((retrieved-item (vecdb-get-item current-provider current-collection (vecdb-item-id item))))
+                                                                                              (should retrieved-item)
+                                                                                              (should (equal (vecdb-item-id item) (vecdb-item-id retrieved-item)))
+                                                                                              ;; We don't test to see if the vector is equal,
+                                                                                              ;; because it could be normalized.
+                                                                                              (should (equal (vecdb-item-payload item) (vecdb-item-payload retrieved-item)))))
                           (vecdb-delete-items current-provider current-collection (mapcar #'vecdb-item-id items) t)
                           (dolist (item items)
                             (should-not (vecdb-get-item current-provider current-collection (vecdb-item-id item)))))))
 
 (vecdb-test--deftest-for-providers vecdb-test-upsert-get-delete-items
-  #'vecdb-test-upsert-get-delete-items-body
-  "Test `vecdb-upsert-items', `vecdb-get-item' and `vecdb-delete-items'.")
+                                   #'vecdb-test-upsert-get-delete-items-body
+                                   "Test `vecdb-upsert-items', `vecdb-get-item' and `vecdb-delete-items'.")
 
 (defun vecdb-test-search-by-vector-body (current-provider)
   "Core logic for testing search by vector."
   (let* ((collection-name "test-collection-sv")
          (vector-size 3)
-         (item1 (make-vecdb-item :id 1 :vector [0.1 0.2 0.3] :payload '(:val 1)))
-         (item2 (make-vecdb-item :id 2 :vector [0.4 0.5 0.6] :payload '(:val 2)))
-         (item3 (make-vecdb-item :id 3 :vector [0.7 0.8 0.9] :payload '(:val 3)))
+         (item1 (make-vecdb-item :id 10000000000 :vector [0.1 0.2 0.3] :payload '(:my-val 1)))
+         (item2 (make-vecdb-item :id 20000000000 :vector [0.4 0.5 0.6] :payload '(:my-val 2)))
+         (item3 (make-vecdb-item :id 30000000000 :vector [0.7 0.8 0.9] :payload '(:my-val 3)))
          (items (list item1 item2 item3)))
-    (with-test-collection current-provider current-collection collection-name `(:vector-size ,vector-size :payload-fields ((val . integer)))
+    (with-test-collection current-provider current-collection collection-name `(:vector-size ,vector-size :payload-fields ((my-val . integer)))
                           (vecdb-upsert-items current-provider current-collection items t)
                           ;; Search for a vector similar to item2
                           (let ((results (vecdb-search-by-vector current-provider current-collection [0.41 0.51 0.61] 3)))
@@ -228,9 +227,9 @@ The collection is created before BODY and deleted afterwards."
                                               items))))))
 
 (vecdb-test--deftest-for-providers
-  vecdb-test-search-by-vector
-  #'vecdb-test-search-by-vector-body
-  "Test `vecdb-search-by-vector'.")
+ vecdb-test-search-by-vector
+ #'vecdb-test-search-by-vector-body
+ "Test `vecdb-search-by-vector'.")
 
 (provide 'vecdb-integration-test)
 
diff --git a/vecdb-psql.el b/vecdb-psql.el
index 5348949..123f7cc 100644
--- a/vecdb-psql.el
+++ b/vecdb-psql.el
@@ -48,10 +48,10 @@ DBNAME is the database name, which must have been created by the user."
          (connection (gethash key vecdb-psql-connection-cache)))
     (unless connection
       (setq connection
-            (pg-connect
+            (pg-connect-plist
              (vecdb-psql-provider-dbname provider)
              (vecdb-psql-provider-username provider)
-             (vecdb-psql-provider-password provider)))
+             :password (vecdb-psql-provider-password provider)))
       (puthash key connection vecdb-psql-connection-cache))
     connection))
 
@@ -63,26 +63,27 @@ DBNAME is the database name, which must have been created by the user."
   "Convert COLLECTION-TYPE to a PostgreSQL type string."
   (pcase collection-type
     ('string "TEXT")
-    ('integer "INTEGER")
+    ('integer "INT8")
     ('float "FLOAT")
     (_ (error "Unsupported field type: %s" collection-type))))
 
 (defun vecdb-psql-oid (collection-type)
   "Convert COLLECTION-TYPE to a psql OID."
   (pcase collection-type
-    ('string "text")
-    ('integer "int8")
-    ('float "float8")
+    ('string "TEXT")
+    ('integer "INT8")
+    ('float "FLOAT8")
     (_ (error "Unsupported field type: %s" collection-type))))
 
 (cl-defmethod vecdb-create ((provider vecdb-psql-provider)
                             (collection vecdb-collection))
   "Create COLLECTION in database PROVIDER."
+  (pg-vector-setup (vecdb-psql-get-connection provider))
   (pg-exec (vecdb-psql-get-connection provider)
            (format "CREATE EXTENSION IF NOT EXISTS vector;"))
   (pg-exec (vecdb-psql-get-connection provider)
            (format "CREATE TABLE IF NOT EXISTS %s (
-                     id INTEGER PRIMARY KEY,
+                     id INT8 PRIMARY KEY,
                      vector VECTOR(%d) NOT NULL%s
                      %s
                    );"
@@ -91,7 +92,7 @@ DBNAME is the database name, which must have been created by the user."
                    (if (vecdb-collection-payload-fields collection) "," "")
                    (mapconcat
                     (lambda (field)
-                      (format "%s %s NULL"
+                      (format "\"%s\" %s NULL"
                               (car field)
                               (vecdb-psql-type (cdr field))))
                     (vecdb-collection-payload-fields collection)
@@ -102,7 +103,7 @@ DBNAME is the database name, which must have been created by the user."
                    (vecdb-psql-table-name (vecdb-collection-name collection))))
   (mapc (lambda (field)
           (pg-exec (vecdb-psql-get-connection provider)
-                   (format "CREATE INDEX IF NOT EXISTS %s_%s_idx ON %s (%s)"
+                   (format "CREATE INDEX IF NOT EXISTS \"%s_%s_idx\" ON %s (\"%s\")"
                            (vecdb-psql-table-name (vecdb-collection-name collection))
                            (car field)
                            (vecdb-psql-table-name (vecdb-collection-name collection))
@@ -139,6 +140,7 @@ DBNAME is the database name, which must have been created by the user."
                                   data-list &optional _)
   "Upsert items into the COLLECTION in the database PROVIDER.
 All items in DATA-LIST must have the same payloads."
+  (pg-vector-setup (vecdb-psql-get-connection provider))
   (let ((arg-count 0))
     (funcall #'pg-exec-prepared
              (vecdb-psql-get-connection provider)
@@ -147,8 +149,9 @@ All items in DATA-LIST must have the same payloads."
                      (vecdb-psql-table-name (vecdb-collection-name collection))
                      (if (vecdb-collection-payload-fields collection) ", " "")
                      ;; We assume every vecdb-item has the same payload structure
-                     (mapconcat #'identity (vecdb-psql--plist-keys
-                                            (vecdb-item-payload (car data-list)))
+                     (mapconcat (lambda (field) (format "\"%s\"" field))
+                                (vecdb-psql--plist-keys
+                                 (vecdb-item-payload (car data-list)))
                                 ", ")
                      (mapconcat (lambda (item)
                                   (format "(%s)"
@@ -161,7 +164,7 @@ All items in DATA-LIST must have the same payloads."
                      (if (vecdb-collection-payload-fields collection) ", " "")
                      (mapconcat
                       (lambda (field)
-                        (format "%s = EXCLUDED.%s" (car field) (car field)))
+                        (format "\"%s\" = EXCLUDED.\"%s\"" (car field) (car field)))
                       (vecdb-collection-payload-fields collection)
                       ", "))
              (mapcan (lambda (item)
@@ -203,7 +206,7 @@ PROVIDER specifies the database that the collection is in."
                                     (if (vecdb-collection-payload-fields collection) ", " "")
                                     (mapconcat
                                      (lambda (field)
-                                       (format "%s" (car field)))
+                                       (format "\"%s\"" (car field)))
                                      (vecdb-collection-payload-fields collection)
                                      ", ")
                                     (vecdb-psql-table-name (vecdb-collection-name collection)))
@@ -243,7 +246,7 @@ PROVIDER is the database that the collection is in."
                                        (if (vecdb-collection-payload-fields collection) ", " "")
                                        (mapconcat
                                         (lambda (field)
-                                          (format "%s" (car field)))
+                                          (format "\"%s\"" (car field)))
                                         (vecdb-collection-payload-fields collection)
                                         ", ")
                                        (vecdb-psql-table-name (vecdb-collection-name collection))
diff --git a/vecdb.el b/vecdb.el
index 945c406..9a1eeb8 100644
--- a/vecdb.el
+++ b/vecdb.el
@@ -5,7 +5,7 @@
 ;; Author: Andrew Hyatt <ahyatt@gmail.com>
 ;; Homepage: https://github.com/ahyatt/vecdb
 ;; Package-Requires: ((emacs "29.1") (plz "0.8") (pg "0.56"))
-;; Package-Version: 0.2
+;; Package-Version: 0.2.1
 ;; SPDX-License-Identifier: GPL-3.0-or-later
 ;;
 ;; This program is free software; you can redistribute it and/or