PostgreSQL 与 Postmodern 的简单使用
Postmodern 是一个连接关系数据库 PostgreSQL 的库。它提供了对 PostgreSQL 的几种访问级别,从表示为字符串或列表的 SQL 查询到对象关系映射的执行。
可以使用以下 SQL 语句创建以下示例中使用的数据库:
create table employees
(empid integer not null primary key,
name text not null,
birthdate date not null,
skills text[] not null);
insert into employees (empid, name, birthdate, skills) values
(1, 'John Orange', '1991-07-26', '{C, Java}'),
(2, 'Mary Red', '1989-04-14', '{C, Common Lisp, Hunchentoot}'),
(3, 'Ron Blue', '1974-01-17', '{JavaScript, Common Lisp}'),
(4, 'Lucy Green', '1968-02-02', '{Java, JavaScript}');
第一个示例显示返回关系的简单查询的结果:
CL-USER> (ql:quickload "postmodern") ; load the system postmodern (nickname: pomo)
("postmodern")
CL-USER> (let ((parameters '("database" "dbuser" "dbpass" "localhost")))
(pomo:with-connection parameters
(pomo:query "select name, skills from employees")))
(("John Orange" #("C" "Java")) ; output manually edited!
("Mary Red" #("C" "Common Lisp" "Hunchentoot"))
("Ron Blue" #("JavaScript" "Common Lisp"))
("Lucy Green" #("Java" "JavaScript")))
4 ; the second value is the size of the result
请注意,结果可以作为 alist 或 plists 的列表返回,将可选参数:alists
或:plists
添加到查询函数中。
query
的替代方法是 doquery
,用于迭代查询结果。它的参数是 query (&rest names) &body body
,其中名称绑定到每次迭代时行中的值:
CL-USER> (let ((parameters '("database" "dbuser" "dbpass" "localhost")))
(pomo:with-connection parameters
(format t "The employees that knows Java are:~%")
(pomo:doquery "select empid, name from employees where skills @> '{Java}'" (i n)
(format t "~a (id = ~a)~%" n i))))
The employees that knows Java are:
John Orange (id = 1)
Lucy Green (id = 4)
NIL
2
当查询需要参数时,可以使用预准备语句:
CL-USER> (let ((parameters '("database" "dbuser" "dbpass" "localhost")))
(pomo:with-connection parameters
(funcall
(pomo:prepare "select name, skills from employees where skills @> $1")
#("Common Lisp")))) ; find employees with skills including Common Lisp
(("Mary Red" #("C" "Common Lisp" "Hunchentoot"))
("Ron Blue" #("JavaScript" "Common Lisp")))
2
函数 prepare
接收带占位符 $1
,$2
等的查询,并返回一个新函数,该函数需要每个占位符一个参数,并在使用正确数量的参数调用时执行查询。
在更新的情况下,函数 exec
返回修改的元组数(两个 DDL 语句包含在事务中):
CL-USER> (let ((parameters '("database" "dbuser" "dbpass" "localhost")))
(pomo:with-connection parameters
(pomo:ensure-transaction
(values
(pomo:execute "alter table employees add column salary integer")
(pomo:execute "update employees set salary =
case when skills @> '{Common Lisp}'
then 100000 else 50000 end")))))
0
4
除了将 SQL 查询编写为字符串之外,还可以使用关键字,符号和常量列表,其语法与 lisp(S-SQL)相似:
CL-USER> (let ((parameters '("database" "dbuser" "dbpass" "localhost")))
(pomo:with-connection parameters
(pomo:query (:select 'name :from 'employees :where (:> 'salary 60000)))))
(("Mary Red") ("Ron Blue"))
2