The cl-yesql Reference Manual

Table of Contents

Next: , Previous: , Up: (dir)   [Contents][Index]

The cl-yesql Reference Manual

This is the cl-yesql Reference Manual, generated automatically by Declt version 2.4 "Will Decker" on Wed Jun 20 11:28:43 2018 GMT+0.


Next: , Previous: , Up: Top   [Contents][Index]

1 Introduction

CL-Yesql is a Common Lisp library for using SQL, based on Clojure’s Yesql.

Besides being useful in itself, this library also serves as a demonstration of writing a language for Overlord, as well as the advantages of allowing (as Overlord does) the same file to be loaded as different modules in different languages.

The Yesql file

For SQL files, the syntax supported by cl-yesql is (or should be) a superset of the syntax supported by the original Clojure library.

CL-Yesql understands more affixes than Clojure’s Yesql does. The original understands fn! (meaning that the function returns nothing) and fn<! (meaning that the function should return the last id). CL-Yesql also understands fn? or fn-p (meaning that the function returns a boolean) and count-fn (meaning that the function returns a number).

However, affixes are a limited solution. CL-Yesql lets you annotate a function definition with a specification of what the function returns.

-- name: users-by-country @rows
-- Counts the users in a given country.
SELECT count(*) AS count
FROM user
WHERE country_code = :country_code

-- name: user-count @single
-- Counts all the users.
SELECT count(*) AS count
FROM user

-- name: young-user-names-by-country @column
SELECT name
FROM user
WHERE (
  country_code = ?
  OR
  country_code = ?
)
AND age < :max_age

The full list of annotations:

Annotation | Meaning ---------- | ------- @rows | default @row | one row @values | one row as multiple values @column | one column @single | a single value @execute | no return value (same as !) @last-id | ID of the last row inserted (same as <!)

While the exact signature of functions depends on the language (Postmodern does not require passing in the database connection, for example, while SQLite does), you may expect to provide positional arguments and keyword arguments as you would to any Lisp function.

(users-by-country :country-code "USA")

(young-user-names-by-country "GB" "US" :max-age 18)

Importing

Importing from Yesql files is done in the usual way, through Overlord.

At the moment, that looks like this:

;; Importing everything.
(overlord:import my-queries
  :from "sql/queries.sql"
  :as :cl-yesql/postmodern
  :binding :all-as-functions)

;; Importing individual functions.
(overlord:import my-queries
  :from "sql/queries.sql"
  :as :cl-yesql/postmodern
  :binding (#'database-size #'thing-tags))

Note that paths are relative to the base of the system, not the current file.

Note also that the :cl-yesql/postmodern system must be loaded before the above code is compiled.

Overlord is still experimental, however, so the syntax may change.

Languages

The Postgres language

Support for Postgres is provided by the package :cl-yesql/postmodern, provided by the system of the same name. Obviously it builds on the Postmodern library.

The Postmodern language implicitly prepares (using postmodern:prepare) all queries when the Yesql file is loaded. It is not necessary to do anything else to prepare them.

The SQLite languages

Support for SQLite is provided through two languages, with different semantics.

Simple SQLite

For querying from an SQLite database, and for discrete inserts and updates, the right language to use is :cl-yesql/sqlite, provided by the system of the same name.

Note that (unlike for the Postmodern language) functions exported by the SQLite language expect a database handle as their first argument.

Prepared SQLite

The language :cl-yesql/sqlite-prepared is designed for bulk inserts. The functions exported by the prepared SQLite language are not intended for direct use. Instead, they return templates for use with cl-yesql/sqlite-prepared:with-prepared-statement.

(overlord:import sqlite-prepared
  :from "sql/sqlite.sql"
  :as :cl-yesql/sqlite-prepared
  :values (#'record-kv))

(defun save-kv-data (db plist)
  (sqlite:with-transaction db
    (cl-yesql/sqlite-prepared:with-prepared-statement
        (record #'record-kv db)
      (doplist (k v data)
        (record :key (string k) :value v)))))

The language :cl-yesql/sqlite-prepared is provided by the package of the same name.

Other languages?

If you want to add another SQL backend to Yesql, I suggest you begin by looking over one of the existing implementations. The SQLite integration is the simplest. Essentially all you need to do is to define a package to serve as the language and, in that package, create an appropriate binding for defquery.


Next: , Previous: , Up: Top   [Contents][Index]

2 Systems

The main system appears first, followed by any subsystem dependency.


Next: , Previous: , Up: Systems   [Contents][Index]

2.1 cl-yesql

Author

Paul M. Rodriguez <pmr@ruricolist.com>

License

MIT

Description

Common Lisp library for using SQL.

Defsystem Dependency

asdf-package-system

Dependency

cl-yesql/cl-yesql (system)

Source

cl-yesql.asd (file)


Next: , Previous: , Up: Systems   [Contents][Index]

2.2 cl-yesql/cl-yesql

Dependencies
Source

cl-yesql.asd (file)

Component

lisp.lisp (file)


Next: , Previous: , Up: Systems   [Contents][Index]

2.3 cl-yesql/defgrammar

Dependencies
Source

cl-yesql.asd (file)

Component

lisp.lisp (file)


Previous: , Up: Systems   [Contents][Index]

2.4 cl-yesql/esrap-user

Dependency

esrap

Source

cl-yesql.asd (file)

Component

lisp.lisp (file)


Next: , Previous: , Up: Top   [Contents][Index]

3 Files

Files are sorted by type and then listed depth-first from the systems components trees.


Previous: , Up: Files   [Contents][Index]

3.1 Lisp


Next: , Previous: , Up: Lisp files   [Contents][Index]

3.1.1 cl-yesql.asd

Location

cl-yesql.asd

Systems

Next: , Previous: , Up: Lisp files   [Contents][Index]

3.1.2 cl-yesql/cl-yesql/lisp.lisp

Parent

cl-yesql/cl-yesql (system)

Location

cl-yesql.lisp

Packages
Exported Definitions
Internal Definitions

Next: , Previous: , Up: Lisp files   [Contents][Index]

3.1.3 cl-yesql/defgrammar/lisp.lisp

Parent

cl-yesql/defgrammar (system)

Location

defgrammar.lisp

Packages

cl-yesql/defgrammar

Exported Definitions
Internal Definitions

Previous: , Up: Lisp files   [Contents][Index]

3.1.4 cl-yesql/esrap-user/lisp.lisp

Parent

cl-yesql/esrap-user (system)

Location

esrap-user.lisp

Packages

cl-yesql/esrap-user


Next: , Previous: , Up: Top   [Contents][Index]

4 Packages

Packages are listed by definition order.


Next: , Previous: , Up: Packages   [Contents][Index]

4.1 cl-yesql-user

Source

lisp.lisp (file)


Next: , Previous: , Up: Packages   [Contents][Index]

4.2 cl-yesql

Source

lisp.lisp (file)

Nickname

yesql

Use List
Exported Definitions
Internal Definitions

Next: , Previous: , Up: Packages   [Contents][Index]

4.3 cl-yesql/defgrammar

Source

lisp.lisp (file)

Use List
Used By List

cl-yesql

Exported Definitions
Internal Definitions

Previous: , Up: Packages   [Contents][Index]

4.4 cl-yesql/esrap-user

Source

lisp.lisp (file)

Use List

Next: , Previous: , Up: Top   [Contents][Index]

5 Definitions

Definitions are sorted by export status, category, package, and then by lexicographic order.


Next: , Previous: , Up: Definitions   [Contents][Index]

5.1 Exported definitions


Next: , Previous: , Up: Exported definitions   [Contents][Index]

5.1.1 Macros

Macro: defgrammar NAME &body BODY
Package

cl-yesql/defgrammar

Source

lisp.lisp (file)


Next: , Previous: , Up: Exported definitions   [Contents][Index]

5.1.2 Functions

Function: .parse GRAMMAR RULE STRING &rest ARGS
Package

cl-yesql/defgrammar

Source

lisp.lisp (file)

Function: .trace GRAMMAR RULE &rest ARGS
Package

cl-yesql/defgrammar

Source

lisp.lisp (file)

Function: parse-queries S
Package

cl-yesql

Source

lisp.lisp (file)

Function: query-args Q
Package

cl-yesql

Source

lisp.lisp (file)

Function: query-id Q
Package

cl-yesql

Source

lisp.lisp (file)

Function: read-module SOURCE STREAM
Package

cl-yesql

Source

lisp.lisp (file)

Function: yesql-reader PATH STREAM
Package

cl-yesql

Source

lisp.lisp (file)

Function: yesql-static-exports FILE
Package

cl-yesql

Source

lisp.lisp (file)


Next: , Previous: , Up: Exported definitions   [Contents][Index]

5.1.3 Generic functions

Generic Function: parse-query S
Package

cl-yesql

Methods
Method: parse-query (P pathname)
Source

lisp.lisp (file)

Method: parse-query (S string)
Source

lisp.lisp (file)

Generic Function: query-annotation OBJECT
Package

cl-yesql

Methods
Method: query-annotation (SELF query) around
Source

lisp.lisp (file)

Method: query-annotation (QUERY query)

automatically generated reader method

Source

lisp.lisp (file)

Generic Function: query-docstring OBJECT
Generic Function: (setf query-docstring) NEW-VALUE OBJECT
Package

cl-yesql

Methods
Method: query-docstring (QUERY query)

automatically generated reader method

Source

lisp.lisp (file)

Method: (setf query-docstring) NEW-VALUE (QUERY query)

automatically generated writer method

Source

lisp.lisp (file)

Generic Function: query-name OBJECT
Package

cl-yesql

Methods
Method: query-name (QUERY query)

automatically generated reader method

Source

lisp.lisp (file)

Generic Function: query-statement OBJECT
Generic Function: (setf query-statement) NEW-VALUE OBJECT
Package

cl-yesql

Methods
Method: query-statement (SELF query) around
Source

lisp.lisp (file)

Method: query-statement (QUERY query)

automatically generated reader method

Source

lisp.lisp (file)

Method: (setf query-statement) NEW-VALUE (QUERY query)

automatically generated writer method

Source

lisp.lisp (file)

Generic Function: query-vars OBJECT
Generic Function: (setf query-vars) NEW-VALUE OBJECT
Package

cl-yesql

Methods
Method: query-vars (QUERY query)

automatically generated reader method

Source

lisp.lisp (file)

Method: (setf query-vars) NEW-VALUE (QUERY query)

automatically generated writer method

Source

lisp.lisp (file)


Next: , Previous: , Up: Exported definitions   [Contents][Index]

5.1.4 Classes

Class: query ()
Package

cl-yesql

Source

lisp.lisp (file)

Direct superclasses

standard-object (class)

Direct methods
Direct slots
Slot: name
Type

string

Initargs

:name

Readers

query-name (generic function)

Slot: annotation
Type

cl-yesql:annotation

Initargs

:annotation

Readers

query-annotation (generic function)

Slot: docstring
Type

string

Readers

query-docstring (generic function)

Writers

(setf query-docstring) (generic function)

Slot: statement
Type

(or string list)

Initargs

:statement

Readers

query-statement (generic function)

Writers

(setf query-statement) (generic function)

Slot: vars
Type

list

Readers

query-vars (generic function)

Writers

(setf query-vars) (generic function)

Direct Default Initargs
InitargValue
:statement(alexandria.0.dev:required-argument (quote cl-yesql::statement))
:name(alexandria.0.dev:required-argument (quote cl-yesql::name))
:annotation:rows

Previous: , Up: Exported definitions   [Contents][Index]

5.1.5 Types

Type: annotation ()
Package

cl-yesql

Source

lisp.lisp (file)


Previous: , Up: Definitions   [Contents][Index]

5.2 Internal definitions


Next: , Previous: , Up: Internal definitions   [Contents][Index]

5.2.1 Special variables

Special Variable: *positional-args*
Package

cl-yesql

Source

lisp.lisp (file)


Next: , Previous: , Up: Internal definitions   [Contents][Index]

5.2.2 Symbol macros

Symbol Macro: annotations
Package

cl-yesql

Source

lisp.lisp (file)

Expansion

cl-yesql::*storage-for-deflex-var-annotations*

Symbol Macro: queries
Package

cl-yesql

Source

lisp.lisp (file)

Expansion

cl-yesql::*storage-for-deflex-var-queries*

Symbol Macro: statement
Package

cl-yesql

Source

lisp.lisp (file)

Expansion

cl-yesql::*storage-for-deflex-var-statement*


Next: , Previous: , Up: Internal definitions   [Contents][Index]

5.2.3 Macros

Macro: with-grammar (NAME EXPR) &body BODY
Package

cl-yesql/defgrammar

Source

lisp.lisp (file)


Next: , Previous: , Up: Internal definitions   [Contents][Index]

5.2.4 Functions

Function: .describe GRAMMAR RULE
Package

cl-yesql/defgrammar

Source

lisp.lisp (file)

Function: ensure-trailing-newline S
Package

cl-yesql

Source

lisp.lisp (file)

Function: lispify-sql-id ID &key PACKAGE
Package

cl-yesql

Source

lisp.lisp (file)

Function: make-grammar ()
Package

cl-yesql/defgrammar

Source

lisp.lisp (file)

Function: make-query &rest ARGS
Package

cl-yesql

Source

lisp.lisp (file)

Function: parse-statement S
Package

cl-yesql

Source

lisp.lisp (file)

Function: positional-arg? ARG
Package

cl-yesql

Source

lisp.lisp (file)

Function: print-sql X S
Package

cl-yesql

Source

lisp.lisp (file)

Function: query-affix Q
Package

cl-yesql

Source

lisp.lisp (file)


Previous: , Up: Internal definitions   [Contents][Index]

5.2.5 Generic functions

Generic Function: statement-vars STATEMENT
Package

cl-yesql

Methods
Method: statement-vars (S string)
Source

lisp.lisp (file)

Method: statement-vars (STATEMENT list)
Source

lisp.lisp (file)


Previous: , Up: Top   [Contents][Index]

Appendix A Indexes


Next: , Previous: , Up: Indexes   [Contents][Index]

A.1 Concepts

Jump to:   C   F   L  
Index Entry  Section

C
cl-yesql.asd: The cl-yesql<dot>asd file
cl-yesql/cl-yesql/lisp.lisp: The cl-yesql/cl-yesql/lisp<dot>lisp file
cl-yesql/defgrammar/lisp.lisp: The cl-yesql/defgrammar/lisp<dot>lisp file
cl-yesql/esrap-user/lisp.lisp: The cl-yesql/esrap-user/lisp<dot>lisp file

F
File, Lisp, cl-yesql.asd: The cl-yesql<dot>asd file
File, Lisp, cl-yesql/cl-yesql/lisp.lisp: The cl-yesql/cl-yesql/lisp<dot>lisp file
File, Lisp, cl-yesql/defgrammar/lisp.lisp: The cl-yesql/defgrammar/lisp<dot>lisp file
File, Lisp, cl-yesql/esrap-user/lisp.lisp: The cl-yesql/esrap-user/lisp<dot>lisp file

L
Lisp File, cl-yesql.asd: The cl-yesql<dot>asd file
Lisp File, cl-yesql/cl-yesql/lisp.lisp: The cl-yesql/cl-yesql/lisp<dot>lisp file
Lisp File, cl-yesql/defgrammar/lisp.lisp: The cl-yesql/defgrammar/lisp<dot>lisp file
Lisp File, cl-yesql/esrap-user/lisp.lisp: The cl-yesql/esrap-user/lisp<dot>lisp file

Jump to:   C   F   L  

Next: , Previous: , Up: Indexes   [Contents][Index]

A.2 Functions

Jump to:   (   .  
D   E   F   G   L   M   P   Q   R   S   W   Y  
Index Entry  Section

(
(setf query-docstring): Exported generic functions
(setf query-docstring): Exported generic functions
(setf query-statement): Exported generic functions
(setf query-statement): Exported generic functions
(setf query-vars): Exported generic functions
(setf query-vars): Exported generic functions

.
.describe: Internal functions
.parse: Exported functions
.trace: Exported functions

D
defgrammar: Exported macros

E
ensure-trailing-newline: Internal functions

F
Function, .describe: Internal functions
Function, .parse: Exported functions
Function, .trace: Exported functions
Function, ensure-trailing-newline: Internal functions
Function, lispify-sql-id: Internal functions
Function, make-grammar: Internal functions
Function, make-query: Internal functions
Function, parse-queries: Exported functions
Function, parse-statement: Internal functions
Function, positional-arg?: Internal functions
Function, print-sql: Internal functions
Function, query-affix: Internal functions
Function, query-args: Exported functions
Function, query-id: Exported functions
Function, read-module: Exported functions
Function, yesql-reader: Exported functions
Function, yesql-static-exports: Exported functions

G
Generic Function, (setf query-docstring): Exported generic functions
Generic Function, (setf query-statement): Exported generic functions
Generic Function, (setf query-vars): Exported generic functions
Generic Function, parse-query: Exported generic functions
Generic Function, query-annotation: Exported generic functions
Generic Function, query-docstring: Exported generic functions
Generic Function, query-name: Exported generic functions
Generic Function, query-statement: Exported generic functions
Generic Function, query-vars: Exported generic functions
Generic Function, statement-vars: Internal generic functions

L
lispify-sql-id: Internal functions

M
Macro, defgrammar: Exported macros
Macro, with-grammar: Internal macros
make-grammar: Internal functions
make-query: Internal functions
Method, (setf query-docstring): Exported generic functions
Method, (setf query-statement): Exported generic functions
Method, (setf query-vars): Exported generic functions
Method, parse-query: Exported generic functions
Method, parse-query: Exported generic functions
Method, query-annotation: Exported generic functions
Method, query-annotation: Exported generic functions
Method, query-docstring: Exported generic functions
Method, query-name: Exported generic functions
Method, query-statement: Exported generic functions
Method, query-statement: Exported generic functions
Method, query-vars: Exported generic functions
Method, statement-vars: Internal generic functions
Method, statement-vars: Internal generic functions

P
parse-queries: Exported functions
parse-query: Exported generic functions
parse-query: Exported generic functions
parse-query: Exported generic functions
parse-statement: Internal functions
positional-arg?: Internal functions
print-sql: Internal functions

Q
query-affix: Internal functions
query-annotation: Exported generic functions
query-annotation: Exported generic functions
query-annotation: Exported generic functions
query-args: Exported functions
query-docstring: Exported generic functions
query-docstring: Exported generic functions
query-id: Exported functions
query-name: Exported generic functions
query-name: Exported generic functions
query-statement: Exported generic functions
query-statement: Exported generic functions
query-statement: Exported generic functions
query-vars: Exported generic functions
query-vars: Exported generic functions

R
read-module: Exported functions

S
statement-vars: Internal generic functions
statement-vars: Internal generic functions
statement-vars: Internal generic functions

W
with-grammar: Internal macros

Y
yesql-reader: Exported functions
yesql-static-exports: Exported functions

Jump to:   (   .  
D   E   F   G   L   M   P   Q   R   S   W   Y  

Next: , Previous: , Up: Indexes   [Contents][Index]

A.3 Variables

Jump to:   *  
A   D   N   Q   S   V  
Index Entry  Section

*
*positional-args*: Internal special variables

A
annotation: Exported classes
annotations: Internal symbol macros

D
docstring: Exported classes

N
name: Exported classes

Q
queries: Internal symbol macros

S
Slot, annotation: Exported classes
Slot, docstring: Exported classes
Slot, name: Exported classes
Slot, statement: Exported classes
Slot, vars: Exported classes
Special Variable, *positional-args*: Internal special variables
statement: Exported classes
statement: Internal symbol macros
Symbol Macro, annotations: Internal symbol macros
Symbol Macro, queries: Internal symbol macros
Symbol Macro, statement: Internal symbol macros

V
vars: Exported classes

Jump to:   *  
A   D   N   Q   S   V  

Previous: , Up: Indexes   [Contents][Index]

A.4 Data types

Jump to:   A   C   P   Q   S   T  
Index Entry  Section

A
annotation: Exported types

C
cl-yesql: The cl-yesql system
cl-yesql: The cl-yesql package
cl-yesql-user: The cl-yesql-user package
cl-yesql/cl-yesql: The cl-yesql/cl-yesql system
cl-yesql/defgrammar: The cl-yesql/defgrammar system
cl-yesql/defgrammar: The cl-yesql/defgrammar package
cl-yesql/esrap-user: The cl-yesql/esrap-user system
cl-yesql/esrap-user: The cl-yesql/esrap-user package
Class, query: Exported classes

P
Package, cl-yesql: The cl-yesql package
Package, cl-yesql-user: The cl-yesql-user package
Package, cl-yesql/defgrammar: The cl-yesql/defgrammar package
Package, cl-yesql/esrap-user: The cl-yesql/esrap-user package

Q
query: Exported classes

S
System, cl-yesql: The cl-yesql system
System, cl-yesql/cl-yesql: The cl-yesql/cl-yesql system
System, cl-yesql/defgrammar: The cl-yesql/defgrammar system
System, cl-yesql/esrap-user: The cl-yesql/esrap-user system

T
Type, annotation: Exported types

Jump to:   A   C   P   Q   S   T