branch: elpa/emacsql
commit f51bb4bfafbc0d7ef217ef4c6e4a14a485709bf6
Author: Christopher Wellons <[email protected]>
Commit: Christopher Wellons <[email protected]>
Allow foreign key specifications within columns.
---
README.md | 9 +++++----
emacsql-compiler.el | 19 +++++++++++++------
emacsql-tests.el | 9 +++++----
3 files changed, 23 insertions(+), 14 deletions(-)
diff --git a/README.md b/README.md
index 07af67621f..15c4db7b44 100644
--- a/README.md
+++ b/README.md
@@ -75,9 +75,10 @@ allowed) types are `integer`, `float`, and `object`
(default).
Columns constraints include `:primary` (aka `PRIMARY KEY`),
`:autoincrement`, `:unique`, `:non-nil` (aka `NOT NULL`), `:default`,
-and `:check`.
+`:check`, and `:references` (foreign key).
-Table constraints can be `:primary`, `:unique`, `:check`, and `:foreign`.
+Table constraints can be `:primary`, `:unique`, `:check`, and
+`:references`.
```el
;; No constraints schema with four columns:
@@ -96,7 +97,7 @@ or `:on-update` and possible actions are `:set-nil`,
`:set-default`,
`:restrict`, `:cascade`. See [the SQLite documentation][foreign] for
the details on what each of these means.
-`:foreign (<child-keys> <parent-table> <parent-keys> [<actions>])`.
+`:references (<child-keys> <parent-table> <parent-keys> [<actions>])`
```el
;; "subject" table
@@ -104,7 +105,7 @@ the details on what each of these means.
;; "tag" table references subjects
([(subjectid integer) tag]
- :foreign (subjectid subject id :on-delete :cascade))
+ :references (subjectid subject id :on-delete :cascade))
```
Put the keys in a vector if the reference is composite. Also remember
diff --git a/emacsql-compiler.el b/emacsql-compiler.el
index 13296b585f..e97e566adc 100644
--- a/emacsql-compiler.el
+++ b/emacsql-compiler.el
@@ -218,6 +218,8 @@ definitions for return from a `emacsql-defexpander'."
(push (var (pop column) :value) output))
(:check (push "CHECK" output)
(push (format "(%s)" (expr (pop column))) output))
+ (:references
+ (push (combine (emacsql--references (pop column))) output))
((integer float object)
(setf type (cadr (assoc next emacsql-type-map))))
(otherwise
@@ -237,14 +239,13 @@ definitions for return from a `emacsql-defexpander'."
collect (combine (emacsql--column-to-string column)) into parts
finally (cl-return (mapconcat #'identity parts ", ")))))
-(defun emacsql--foreign-key (spec)
- (emacsql-with-vars "FOREIGN KEY "
- (cl-destructuring-bind (child table parent . actions) (cl-coerce spec
'list)
+(defun emacsql--references (spec)
+ (emacsql-with-vars "REFERENCES "
+ (cl-destructuring-bind (table parent . actions) (cl-coerce spec 'list)
(mapconcat
#'identity
(cons
- (format "(%s) REFERENCES %s (%s)" (idents child) (var table
:identifier)
- (idents parent))
+ (format "%s (%s)" (var table :identifier) (idents parent))
(cl-loop for (key value) on actions by #'cddr collect
(cl-case key
(:on-update "ON UPDATE")
@@ -259,6 +260,12 @@ definitions for return from a `emacsql-defexpander'."
(otherwise (emacsql-error "Invalid action: %S" key)))))
" "))))
+(defun emacsql--foreign-key (spec)
+ (emacsql-with-vars "FOREIGN KEY "
+ (cl-destructuring-bind (child . references) (cl-coerce spec 'list)
+ (format "(%s) %s" (idents child)
+ (combine (emacsql--references references))))))
+
(defun emacsql--schema-to-string (schema)
(cl-typecase schema
(vector (emacsql--columns-to-string schema))
@@ -273,7 +280,7 @@ definitions for return from a `emacsql-defexpander'."
(:primary (format "PRIMARY KEY (%s)" (idents value)))
(:unique (format "UNIQUE (%s)" (idents value)))
(:check (format "CHECK (%s)" (expr value)))
- (:foreign (combine (emacsql--foreign-key value)))
+ (:references (combine (emacsql--foreign-key value)))
(otherwise
(emacsql-error "Invalid table constraint: %S" key)))))
", ")))
diff --git a/emacsql-tests.el b/emacsql-tests.el
index 31c6567bf6..27ca4731aa 100644
--- a/emacsql-tests.el
+++ b/emacsql-tests.el
@@ -111,10 +111,10 @@
"CREATE TABLE foo (a NONE, b NONE, c NONE, PRIMARY KEY (a, c));")
([:create-table foo ([a b c] :unique [a b c])] '()
"CREATE TABLE foo (a NONE, b NONE, c NONE, UNIQUE (a, b, c));")
- ([:create-table foo ([a b] :check (< a b)) ] '()
+ ([:create-table foo ([a b] :check (< a b))] '()
"CREATE TABLE foo (a NONE, b NONE, CHECK (a < b));")
([:create-table foo
- ([a b c] :foreign ([a b] bar [aa bb] :on-delete :cascade))] '()
+ ([a b c] :references ([a b] bar [aa bb] :on-delete :cascade))] '()
(concat "CREATE TABLE foo (a NONE, b NONE, c NONE, FOREIGN KEY (a, b) "
"REFERENCES bar (aa, bb) ON DELETE CASCADE);"))
;; Drop table
@@ -218,8 +218,9 @@
(emacsql-with-connection (db (emacsql-sqlite nil))
(emacsql-thread db
[:create-table person [(id integer :primary) name]]
- [:create-table likes ([(personid integer) color]
- :foreign (personid person id :on-delete :cascade))]
+ [:create-table likes
+ ([(personid integer) color]
+ :references (personid person id :on-delete :cascade))]
[:replace :into person :values ([0 "Chris"] [1 "Brian"])])
(should (equal (emacsql db [:select * :from person :order-by id])
'((0 "Chris") (1 "Brian"))))
