6.3 Defining SQL accessors
| (require toolbox/db/define) | package: toolbox-db-lib |
In addition to the bindings documented in this section, the toolbox/db/define module also re-exports field from racket/class, which is recognized as part of the syntax of define-sql-table.
syntax
(define-sql-table table-name-id table-option ... (field field-name-id field-option ...) ...)
table-option = #:sql-name table-name-expr | #:resolve resolve-expr | #:deleter maybe-name-id field-option = #:sql-name field-name-expr | #:getter maybe-name-id | #:setter maybe-name-id | #:convert sql->racket-expr racket->sql-expr table-name-expr = name-expr field-name-expr = name-expr maybe-name-id =
| name-id
name-expr : symbol?
resolve-expr : (or/c (-> any/c #:who symbol? any/c) #f)
sql->racket-expr : (-> any/c any/c)
racket->sql-expr : (-> any/c any/c)
The name of the SQL table is given by the result of table-name-expr. If no table-name-expr is provided, the SQL table name is inferred from table-name-id by replacing all occurrences of - with _ and replacing a trailing ? with the prefix is_. For example, if table-name-id were user-friend?, the inferred SQL name would be is_user_friend.
If the #:deleter name-id option is provided, name-id is defined as a deleter procedure produced by make-sql-deleter. If #:deleter is provided with no name-id, the name delete-table-name-id! is used, instead.
Each provided field clause controls generation of getter and setter procedures for individual fields (columns) of the table. The SQL name of each field is given by field-name-expr. If no field-name-expr is provided, the SQL name is inferred from field-name-id in the same way the table name may be inferred from table-name-id.
If the #:getter name-id option is provided for a field, name-id is defined as a getter procedure produced by make-sql-getter. If #:getter is provided with no name-id, the name table-name-id-field-name-id is used, instead.
Likewise, if the #:setter name-id option is provided for a field, name-id is defined as a setter procedure produced by make-sql-setter. If #:setter is provided with no name-id, the name set-table-name-id-field-name-id! is used, instead.
If the #:convert option is provided for a field, the sql->racket-expr and racket->sql-expr expressions are used as the #:convert arguments to make-sql-getter and make-sql-setter, respectively.
If the #:resolve table option is provided, the procedure produced by resolve-expr is used as the #:resolve argument to make-sql-deleter, make-sql-getter, and make-sql-setter.
> (current-db (sqlite3-connect #:database 'memory))
> (query-exec (~sql "CREATE TABLE user" "( id INTEGER NOT NULL PRIMARY KEY" ", name TEXT NOT NULL" ", is_admin INTEGER NOT NULL DEFAULT (0)" " CHECK (is_admin IN (0, 1)) )"))
> (define-sql-table user (field name #:getter #:setter) (field admin? #:getter #:setter #:convert integer->boolean boolean->integer))
> (query-exec (~sql "INSERT INTO user(id, name) VALUES (1, 'Alyssa'), (2, 'Ben')")) > (user-name 1) "Alyssa"
> (user-name 2) "Ben"
> (set-user-admin?! 1 #t) > (user-admin? 1) #t
> (user-admin? 2) #f
procedure
(make-sql-deleter #:table table-name #:who who [ #:resolve resolve-proc]) → (->* [any/c] [#:who symbol? #:resolve? any/c] void?) table-name : symbol? who : symbol? resolve-proc : (or/c (-> any/c #:who symbol? any/c) #f) = #f
DELETE FROM (sql:id table-name) WHERE id = ?
If resolve-proc is not #f, it is used to compute a primary key from the argument provided to the deleter procedure unless #:resolve? #f is supplied. The call to resolve-proc and the DELETE statement are both executed within the same database transaction.
The who argument is used as the name of the deleter procedure, as returned by object-name, and it is used in error messages reported by the deleter procedure. It is also passed to resolve-proc, if provided, via the #:who keyword argument.
procedure
(make-sql-getter #:table table-name #:field field-name #:who who [ #:resolve resolve-proc #:convert convert-proc]) → (->* [any/c] [#:who symbol? #:resolve? any/c] any/c) table-name : symbol? field-name : symbol? who : symbol? resolve-proc : (or/c (-> any/c #:who symbol? any/c) #f) = #f convert-proc : (-> any/c any/c) = values
SELECT (sql:id field-name) FROM (sql:id table-name) WHERE id = ?
The convert-proc argument is applied to the result of the SELECT statement to produce a result for the getter procedure.
If resolve-proc is not #f, it is used to compute a primary key from the argument provided to the getter procedure unless #:resolve? #f is supplied. The call to resolve-proc and the SELECT statement are both executed within the same database transaction.
The who argument is used as the name of the getter procedure, as returned by object-name, and it is used in error messages reported by the getter procedure. It is also passed to resolve-proc, if provided, via the #:who keyword argument.
procedure
(make-sql-setter #:table table-name #:field field-name #:who who [ #:resolve resolve-proc #:convert convert-proc]) → (->* [any/c any/c] [#:who symbol? #:resolve? any/c] void?) table-name : symbol? field-name : symbol? who : symbol? resolve-proc : (or/c (-> any/c #:who symbol? any/c) #f) = #f convert-proc : (-> any/c any/c) = values
UPDATE (sql:id table-name) SET (sql:id field-name) = ? WHERE id = ?
The convert-proc argument is applied to the second argument of the of the setter procedure to produce a value to be used as the first parameter of the UPDATE statement.
If resolve-proc is not #f, it is used to compute a primary key from the first argument provided to the setter procedure unless #:resolve? #f is supplied. The call to resolve-proc and the UPDATE statement are both executed within the same database transaction.
The who argument is used as the name of the setter procedure, as returned by object-name, and it is used in error messages reported by the setter procedure. It is also passed to resolve-proc, if provided, via the #:who keyword argument.