SQL: A Structured Notation for SQL Statements
(require sql)
需要安装sql包。
sql模块以s表达式的方式来式SQL的一个子集。它可以生成直接在db模块中可以用的语句。除此之外还提供宏和函数用以创建并操控SQL的AST。
创建一个表:
#lang racket
(require sql db)
(define dbc (sqlite3-connect #:database 'memory))
(query-exec dbc
(create-table #:temporary the_numbers
#:columns
[n integer #:not-null]
[d varchar]))
插入内容
(query-exec dbc
(insert #:into the_numbers #:set [n 0] [d "nothing"]))
可以直接使用unquote操作来插入变量:
(query-exec dbc
(insert #:into the_numbers #:set
[n ,n1]
[d ,d1]))
sql模块会根据数据库连接的类型来生成合适的语句。
可以显示指定语句参数:
(insert #:into the_numbers #:set [n ?] [d ?])
但是不能把unquote和显示指定的混在一起,下面是不行的:
(insert #:into the_numbers #:set [n ,3] [d ?])
但是混合常量字面值和unquote形式是可以的:
> (query-exec pgc
(insert #:into the_numbers #:set [n 3] [d ,"a crowd"]))
select的例子:
> (query-row pgc
(select n d #:from the_numbers #:where (< n 1)))
2 Statement Forms
(current-sql-dialect)
控制所采用的sql的格式。当current-sql-dialect #f)
时,使用SQL1992方言。
sql生成的语句符合db库的statement?
判定诀的要求。
相关的语法:
(sql statement)
(sql ddl-statement)
(select select-item ... select-clause ...)
(select select-clause ...)
(insert #:into table-name assign-clause maybe-conflict)
(insert #:into table-name maybe-columns #:from table-expr maybe-conflict)
(update table-name assign-clause maybe-where)
(delete #:from table-name maybe-where)
(create-table maybe-temp maybe-ifnotexists table-name
#:columns column-def ...
maybe-constraints)
(create-table maybe-temp maybe-ifnotexists table-name
#:as statement)
(create-view maybe-temp view-name
statement)
相关执行诀:
(sql-statement? v)
3 S-expression Syntax for SQL
SQL的s表达式按照非端子组织。每个非端子具有如下内容:
- S-表达式语法
- AST类型判定
- 一个准引用宏来从S表达式语法生成AST值
(sql-ast->string ast [dialect])
将SQL的s表达式转为字符串。
3.1 SQL Names and Identifiers
(Ident: symbol)
(Ident: string)
(Name: name ...+)
标识符相关的时准引用以及判定诀:
(ident-qq indent)
(indent-ast? v)
(name-qq name)
(name-ast? v)
3.2 SQL Scalar Expressions
(scalar-expr-qq scalar-expr)
(scalar-expr-ast? v)
3.2.1 Special Scalar Expressions
(operator/special scalar-expr ...)
3.2.2 Placeholders and Unquoted Parameters
略
3.3 SQL Table References and Expressions
(table-ref-qq table-ref)
(table-ref-ast? v)
(table-expr-qq table-expr)
(table-expr-ast? v)
3.4 SQL Statements
(statement-qq statement)
(statement-ast? v)
(select-item-qq select-item)
(select-item-ast? v)
3.5 SQL DDL Statements
(ddl-qq ddl-statement)
(ddl-ast? v)
3.6 SQL Dialect
(current-sql-dialect)
(current-sql-dialect dialect)
3.7 Dynamic Statement Composition and SQL Injection
(make-ident-ast s)
(make-name-ast s)
(value->scalar-expr-ast v)
(make-values*-table-expr-ast l-rows)
SQLite Table
(require sqlite-table)
用于方便地将lis-of-lists,list-of-vectors,list-of-sequences数据转为SQLite表格。本库将所有永久地表格保存在/tmp/racket-tables.sqlite
。
make-table
用来创建一个表格,例如:
(make-table '(student a b)
'(#("bob" 3 8)
#("annie" 4 9)
#("bob" 6 12)))
其他执行诀:
(find-table name)
(table-size table)
(table-select ---)
(inner-join ---)
(in-table-column table column)
(table? t)
(本篇完)