Quickdocs

Q

sxql

2024-10-12

A SQL generator

Upstream URL

github.com/fukamachi/sxql

Author

Eitaro Fukamachi

License

BSD 3-Clause
README
Provided Systems

SxQL - An SQL generator.

Build Status

Usage

(select (:id :name :sex)
  (from (:as :person :p))
  (where (:and (:>= :age 18)
               (:< :age 65)))
  (order-by (:desc :age)))
;=> #<SXQL-STATEMENT: SELECT id, name, sex FROM person AS p WHERE ((age >= 18) AND (age < 65)) ORDER BY age DESC>

(yield *)

;=> "SELECT id, name, sex FROM person AS p WHERE ((age >= ?) AND (age < ?)) ORDER BY age DESC"
;   (18 65)

(sql-compile **)
;=> #<SXQL-COMPILED: SELECT id, name, sex FROM person AS p WHERE ((age >= ?) AND (age < ?)) ORDER BY age DESC [18, 65]>

(union-queries * (select (:id :name :sex) (from '(:as animal a))))
;=> #<SXQL-OP: (SELECT id, name, sex FROM (person AS p) WHERE ((age >= ?) AND (age < ?)) ORDER BY age DESC) UNION (SELECT id, name, sex FROM (animal AS a))>

(yield *)
;=> "(SELECT id, name, sex FROM (person AS p) WHERE ((age >= ?) AND (age < ?)) ORDER BY age DESC) UNION (SELECT id, name, sex FROM (animal AS a))"
;   (18 65)

SQL Statements

select (field &body clauses)

Creates a SELECT query. It takes a field (or a list of fields) and SQL Clauses.

(select ((:+ 1 1)))
;=> #<SXQL-STATEMENT: SELECT (1 + 1)>

(select :name
  (from :person)
  (where (:> :age 20)))
;=> #<SXQL-STATEMENT: SELECT name FROM person WHERE (age > 20)>

(select (:id :name)
  (from (:as :person :p))
  (left-join :person_config :on (:= :person.config_id :person_config.id))
  (where (:and (:> :age 20)
               (:<= :age 65)))
  (order-by :age)
  (limit 5))
;=> #<SXQL-STATEMENT: SELECT id, name FROM (person AS p) LEFT JOIN person_config ON (person.config_id = person_config.id) WHERE ((age > 20) AND (age <= 65)) ORDER BY age LIMIT 5>

(select (:sex (:count :*)) (from :person) (group-by :sex))
;=> #<SXQL-STATEMENT: SELECT sex, COUNT(*) FROM person GROUP BY sex>

(select (:sex (:as (:count :*) :num)) 
  (from :person)
  (group-by :sex)
  (order-by (:desc :num)))
;=> #<SXQL-STATEMENT: SELECT sex, COUNT(*) AS num FROM person GROUP BY sex ORDER BY num DESC>

insert-into (table &body clauses)

(insert-into :person
  (set= :sex "male"
        :age 25
        :name "Eitaro Fukamachi"))
;=> #<SXQL-STATEMENT: INSERT INTO person SET sex = 'male', age = 25, name = 'Eitaro Fukamachi'>

(insert-into :person
  (:sex :age :name)
  (list "male" 25 "Eitaro Fukamachi"))
;=> #<SXQL-STATEMENT: INSERT INTO person SET sex = 'male', age = 25, name = 'Eitaro Fukamachi'>

(insert-into :person
  (:sex :age :name)
  (list (list "male" 25 "Eitaro Fukamachi")
        (list "female" 16 "Miku Hatsune")))
;=> #<SXQL-STATEMENT: INSERT INTO person (sex, age, name) VALUES ('male', 25, 'Eitaro Fukamachi'), ('female', 16, 'Miku Hatsune')>

(insert-into :users
  (set= :name "Jack"
        :jinbei-size "small")
  (returning :id))
;=> #<SXQL-STATEMENT: INSERT INTO `users` (`name`, `jinbei-size`) VALUES ('Jack', 'small') RETURNING `id`>

(insert-into :person
  (:id :name)
  (select (:id :name)
    (from :person_tmp)))
;=> #<SXQL-STATEMENT: INSERT INTO person (id, name) SELECT id, name FROM person_tmp>

update (table &body clauses)

(update :person
  (set= :age 26)
  (where (:like :name "Eitaro %")))
;=> #<SXQL-STATEMENT: UPDATE person SET age = 26 WHERE (name LIKE 'Eitaro %')>

delete-from (table &body clauses)

(delete-from :person
  (where (:= :name "Eitaro Fukamachi")))
;=> #<SXQL-STATEMENT: DELETE FROM person WHERE (name = 'Eitaro Fukamachi')>

union-queies (&rest statements)

(union-queries
 (select (:name :birthday) (from :fulltime))
 (select (:name :birthday) (from :parttime)))
;=> #<SXQL-OP: (SELECT name, birthday FROM fulltime) UNION (SELECT name, birthday FROM parttime)>

union-all-queries (&rest statements)

(union-all-queries
 (select (:name :birthday) (from :fulltime))
 (select (:name :birthday) (from :parttime)))
;=> #<SXQL-OP: (SELECT name, birthday FROM fulltime) UNION ALL (SELECT name, birthday FROM parttime)>

create-table (table column-definitions &body options)

(create-table :enemy
    ((name :type 'string
           :primary-key t)
     (age :type 'integer
          :not-null t)
     (address :type 'text
              :not-null nil)
     (fatal_weakness :type 'text
                     :not-null t
                     :default "None")
     (identifying_color :type '(:char 20)
                        :unique t)))
;=> #<SXQL-STATEMENT: CREATE TABLE enemy (name STRING PRIMARY KEY, age INTEGER NOT NULL, address TEXT, fatal_weakness TEXT NOT NULL DEFAULT 'None', identifying_color CHAR(20) UNIQUE)>

(yield *)
;=> "CREATE TABLE enemy (name STRING PRIMARY KEY, age INTEGER NOT NULL, address TEXT, fatal_weakness TEXT NOT NULL DEFAULT ?, identifying_color CHAR(20) UNIQUE)"
;   ("None")

(create-table (:enemy :if-not-exists t)
    ((name :type 'string
           :primary-key t)
     (age :type 'integer
          :not-null t)
     (address :type 'text
              :not-null nil)
     (fatal_weakness :type 'text
                     :not-null t
                     :default "None")
     (identifying_color :type '(:char 20)
                        :unique t)))
;=> #<SXQL-STATEMENT: CREATE TABLE IF NOT EXISTS enemy (name STRING PRIMARY KEY, age INTEGER NOT NULL, address TEXT, fatal_weakness TEXT NOT NULL DEFAULT 'None', identifying_color CHAR(20) UNIQUE)>

drop-table (table &key if-exists)

(drop-table :enemy)
;=> #<SXQL-STATEMENT: DROP TABLE enemy>

(drop-table :enemy :if-exists t)
;=> #<SXQL-STATEMENT: DROP TABLE IF EXISTS enemy>

alter-table (table &body clauses)

(alter-table :tweet
  (add-column :id :type 'bigint :primary-key t :auto-increment t :first t)
  (add-column :updated_at :type 'timestamp))
;=> #<SXQL-STATEMENT: ALTER TABLE tweet ADD COLUMN id BIGINT AUTO_INCREMENT PRIMARY KEY FIRST, ADD COLUMN updated_at TIMESTAMP>

create-index (index-name &key unique using on)

(create-index "index_name"
              :unique t
              :using :btee
              :on '(:table :column1 :column2))
;=> #<SXQL-STATEMENT: CREATE UNIQUE INDEX index_name USING BTEE ON table (column1, column2)>

drop-index (index-name &key if-exists on)

(drop-index "index_name" :if-exists t :on :person)
;=> #<SXQL-STATEMENT: DROP INDEX IF EXISTS index_name ON person>

SQL Clauses

fields

(fields :id)
;=> #<SXQL-CLAUSE: id>

(fields (:count :id))
;=> #<SXQL-CLAUSE: COUNT(id)>

(fields :id (:sum :amount))
;=> #<SXQL-CLAUSE: id, SUM(amount)>

from

(from :person)
;=> #<SXQL-CLAUSE: FROM person>

(from :person :person_config)
;=> #<SXQL-CLAUSE: FROM person, person_config>

(from (select :* (from :person) (where (:= :is_active 1))))
;=> #<SXQL-CLAUSE: FROM (SELECT * FROM person WHERE (is_active = 1))>

where

(where (:and (:> :age 20) (:<= :age 65)))
;=> #<SXQL-CLAUSE: WHERE ((age > 20) AND (age <= 65))>

(yield *)
;=> "WHERE ((age > ?) AND (age <= ?))"
;   (20 65)

order-by

(order-by :age)
;=> #<SXQL-CLAUSE: ORDER BY age>

(order-by :age (:desc :id))
;=> #<SXQL-CLAUSE: ORDER BY age, id DESC>
;   NIL

group-by

(group-by :sex)
;=> #<SXQL-CLAUSE: GROUP BY sex>

having

(having (:>= (:sum :hoge) 88))
;=> #<SXQL-CLAUSE: HAVING (SUM(`hoge`) >= 88)>

returning

(returning :id)
;=> #<SXQL-CLAUSE: RETURNING `id`>

limit

(limit 10)
;=> #<SXQL-CLAUSE: LIMIT 10>

(limit 0 10)
;=> #<SXQL-CLAUSE: LIMIT 0, 10>

(yield *)
;=> "LIMIT 0, 10"
;   NIL

offset

(offset 0)
;=> #<SXQL-CLAUSE: OFFSET 0>

(yield *)
;=> "OFFSET 0"
;   NIL

inner-join, left-join, right-join, full-join

(inner-join :person_config :on (:= :person.config_id :person_config.id))
;=> #<SXQL-CLAUSE: INNER JOIN person_config ON (person.config_id = person_config.id)>

(left-join :person_config :on (:= :person.config_id :person_config.id))
;=> #<SXQL-CLAUSE: LEFT JOIN person_config ON (person.config_id = person_config.id)>

(left-join :person_config :using :config_id)
;=> #<SXQL-CLAUSE: LEFT JOIN person_config USING config_id>

primary-key

(primary-key :id)
;=> #<SXQL-CLAUSE: PRIMARY KEY (id)>

(primary-key '(:id))
;=> #<SXQL-CLAUSE: PRIMARY KEY (id)>

(primary-key "id_index" '(:id))
;=> #<SXQL-CLAUSE: PRIMARY KEY 'id_index' (id)>

unique-key

(unique-key '(:name :country))
;=> #<SXQL-CLAUSE: UNIQUE (name, country)>

(unique-key "name_and_country_index" '(:name :country))
;=> #<SXQL-CLAUSE: UNIQUE 'name_and_country_index' (name, country)>

index-key

(index-key (:name :country))
;=> #<SXQL-CLAUSE: KEY (name, country)>

(index-key "name_and_country_index" '(:name :country))
;=> #<SXQL-CLAUSE: KEY 'name_and_country_index' (name, country)>

foreign-key

(foreign-key '(:project_id) :references '(:project :id))
;=> #<SXQL-CLAUSE: FOREIGN KEY (project_id) REFERENCES project (id)>

(foreign-key '(:user_id) :references '(:user :id) :on-delete :cascade)
;=> #<SXQL-CLAUSE: FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE>

add-column

(add-column :updated_at :type 'integer :default 0 :not-null t :after :created_at)
;=> #<SXQL-CLAUSE: ADD COLUMN updated_at INTEGER NOT NULL DEFAULT 0 AFTER created_at>

modify-column

(modify-column :updated_at :type 'datetime :not-null t)
;=> #<SXQL-CLAUSE: MODIFY COLUMN updated_at DATETIME NOT NULL>

alter-column

(alter-column :user :type '(:varchar 64))
;=> #<SXQL-CLAUSE: ALTER COLUMN user TYPE VARCHAR(64)>

(alter-column :id :set-default 1)
;=> #<SXQL-CLAUSE: ALTER COLUMN id SET DEFAULT 1>

(alter-column :id :drop-default t)
;=> #<SXQL-CLAUSE: ALTER COLUMN id DROP DEFAULT>

(alter-column :profile :not-null t)
;=> #<SXQL-CLAUSE: ALTER COLUMN profile SET NOT NULL>

change-column

(change-column :updated_at :updated_on)
;=> #<SXQL-CLAUSE: CHANGE COLUMN updated_at updated_on>

drop-column

(drop-column :updated_on)
;=> #<SXQL-CLAUSE: DROP COLUMN updated_on>

add-primary-key

(add-primary-key :id :name)
;=> #<SXQL-CLAUSE: ADD PRIMARY KEY (id, name)>

drop-primary-key

(drop-primary-key)
;=> #<SXQL-CLAUSE: DROP PRIMARY KEY>

rename-to

(rename-to :users)
;=> #<SXQL-CLAUSE: RENAME TO `users`>

(alter-table :user
  (rename-to :users))
;=> #<SXQL-STATEMENT: ALTER TABLE `user` RENAME TO `users`>

on-duplicate-key-update

Support MySQL's INSERT ... ON DUPLICATE KEY UPDATE syntax.

(on-duplicate-key-update :age (:+ :age 1))
;=> #<SXQL-CLAUSE: ON DUPLICATE KEY UPDATE `age` = (`age` + 1)>

(insert-into :person
  (set= :sex "male"
        :age 25
        :name "Eitaro Fukamachi")
  (on-duplicate-key-update :age (:+ :age 1)))
;=> #<SXQL-STATEMENT: INSERT INTO `person` (`sex`, `age`, `name`) VALUES ('male', 25, 'Eitaro Fukamachi') ON DUPLICATE KEY UPDATE `age` = (`age` + 1)>

on-coflict-do-nothing

Support PostgreSQL's INSERT ... ON CONFLICT DO NOTHING syntax.

(on-conflict-do-nothing)
;=> #<SXQL-CLAUSE: ON CONFLICT DO NOTHING>

(on-conflict-do-nothing :index_name)
;=> #<SXQL-CLAUSE: ON CONFLICT ON CONSTRAINT index_name DO NOTHING>

(on-conflict-do-nothing '(:column1 :column2 :column3))
;=> #<SXQL-CLAUSE: ON CONFLICT (column1, column2, column3) DO NOTHING>

on-coflict-do-update

Support PostgreSQL's INSERT ... ON CONFLICT ... DO UPDATE syntax.

(on-conflict-do-update :index_name (set= :x 1 :y 2))
;=> #<SXQL-CLAUSE: ON CONFLICT ON CONSTRAINT index_name DO UPDATE SET x = 1, y = 2>

(on-conflict-do-update '(:column1 :column2 :column3) (set= :x 1 :y 2))
;=> #<SXQL-CLAUSE: ON CONFLICT (column1, column2, column3) DO UPDATE SET x = 1, y = 2>

(insert-into :person
  (set= :sex "male"
        :age 25
        :name "Eitaro Fukamachi")
  (on-conflict-do-update '(:name)
                         (set= :age (:+ :age 1))
                         (where (:< :age 99))))
;=> #<SXQL-STATEMENT: INSERT INTO person (sex, age, name) VALUES ('male', 25, 'Eitaro Fukamachi') ON CONFLICT (name) DO UPDATE SET age = (age + 1) WHERE (age < 99)>

SQL Operators

  • :not
  • :is-null, :not-null
  • :asc, :desc
  • :distinct
  • :=, :!=
  • :<, :>, :<= :>=
  • :a<, :a>
  • :as
  • :in, :not-in
  • :like
  • :and, :or
  • :+, :-, :* :/ :%
  • :raw
  • :is-distinct-from, :is-not-distinct-from (Postgres)

Set a quote character

*quote-character* is the character that a table or column name will be quoted with. The default value is NIL (not quote).

(yield (select :* (from 'table)))
;=> "SELECT * FROM table"
;   NIL

;; for MySQL
(let ((*quote-character* #\`))
  (yield (select :* (from 'table))))
;=> "SELECT * FROM `table`"
;   NIL

;; for PostgreSQL
(let ((*quote-character* #\"))
  (yield (select :* (from 'table))))
;=> "SELECT * FROM "table""
;   NIL

Author

Copyright

Copyright (c) 2013-2014 Eitaro Fukamachi (e.arrows@gmail.com)

License

Licensed under the BSD 3-Clause License.

Dependencies (9)

  • alexandria
  • cl-annot
  • cl-package-locks
  • iterate
  • named-readtables
  • prove
  • split-sequence
  • trivia
  • trivial-types

Dependents (7)

  • GitHub
  • Quicklisp