Common Lisp
Need help with sxql?
Click the “chat” button below for chat support from the developer who created it, or find similar developers for support.
fukamachi

Description

An SQL generator for Common Lisp.

216 Stars 21 Forks BSD 3-Clause "New" or "Revised" License 187 Commits 12 Opened issues

Services available

Need anything else?

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)))
;=> #= 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>= ?) AND (age < ?)) ORDER BY age DESC [18, 65]>

(union-queries * (select (:id :name :sex) (from '(:as animal a)))) ;=> #<sxql-op: id name sex from as p where>= ?) 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)))
;=> #

(select :name (from :person) (where (:> :age 20))) ;=> #<sxql-statement: select name from person where> 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 as p left join person_config on person_config.id where> 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>

(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 order desc>

insert-into (table &body clauses)

(insert-into :person
  (set= :sex "male"
        :age 25
        :name "Eitaro Fukamachi"))
;=> #

(insert-into :users (set= :name "Jack" :jinbei-size "small") (returning :id)) ;=> #<sxql-statement: insert into values returning>

(insert-into :person (:id :name) (select (:id :name) (from :person_tmp))) ;=> #<sxql-statement: insert into person name select id from person_tmp>

update (table &body clauses)

(update :person
  (set= :age 26)
  (where (:like :name "Eitaro %")))
;=> #

delete-from (table &body clauses)

(delete-from :person
  (where (:= :name "Eitaro Fukamachi")))
;=> #

union-queies (&rest statements)

(union-queries
 (select (:name :birthday) (from :fulltime))
 (select (:name :birthday) (from :parttime)))
;=> #

union-all-queries (&rest statements)

(union-all-queries
 (select (:name :birthday) (from :fulltime))
 (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)))
;=> #

(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 string primary key age integer null address text fatal_weakness default identifying_color char unique>

drop-table (table &key if-exists)

(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))
;=> #

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

(create-index "index_name"
              :unique t
              :using :btee
              :on '(:table :column1 :column2))
;=> #

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

(drop-index "index_name" :if-exists t :on :person)
;=> #

SQL Clauses

fields

(fields :id)
;=> #

(fields (:count :id)) ;=> #<sxql-clause: count>

(fields :id (:sum :amount)) ;=> #<sxql-clause: id sum>

from

(from :person)
;=> #

(from :person :person_config) ;=> #<sxql-clause: from person person_config>

(from (select :* (from :person) (where (:= :is_active 1)))) ;=> #<sxql-clause: from person where>

where

(where (:and (:> :age 20) (:<= :age 65)))
;=> # 20) AND (age <= 65))>

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

order-by

(order-by :age)
;=> #

(order-by :age (:desc :id)) ;=> #<sxql-clause: order by age id desc> ; NIL

group-by

(group-by :sex)
;=> #

having

(having (:>= (:sum :hoge) 88))
;=> #= 88)>

returning

(returning :id)
;=> #

limit

(limit 10)
;=> #

(limit 0 10) ;=> #<sxql-clause: limit>

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

offset

(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))
;=> #

(left-join :person_config :on (:= :person.config_id :person_config.id)) ;=> #<sxql-clause: left join person_config on person_config.id>

(left-join :person_config :using :config_id) ;=> #<sxql-clause: left join person_config using config_id>

primary-key

(primary-key :id)
;=> #

(primary-key '(:id)) ;=> #<sxql-clause: primary key>

(primary-key "id_index" '(:id)) ;=> #<sxql-clause: primary key>

unique-key

(unique-key '(:name :country))
;=> #

(unique-key "name_and_country_index" '(:name :country)) ;=> #<sxql-clause: unique country>

index-key

(index-key (:name :country))
;=> #

(index-key "name_and_country_index" '(:name :country)) ;=> #<sxql-clause: key country>

foreign-key

(foreign-key '(:project_id) :references '(:project :id))
;=> #

(foreign-key '(:user_id) :references '(:user :id) :on-delete :cascade) ;=> #<sxql-clause: foreign key references user on delete cascade>

add-column

(add-column :updated_at :type 'integer :default 0 :not-null t :after :created_at)
;=> #

modify-column

(modify-column :updated_at :type 'datetime :not-null t)
;=> #

alter-column

(alter-column :user :type '(:varchar 64))
;=> #

(alter-column :id :set-default 1) ;=> #<sxql-clause: alter column id set default>

(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)
;=> #

drop-column

(drop-column :updated_on)
;=> #

add-primary-key

(add-primary-key :id :name)
;=> #

drop-primary-key

(drop-primary-key)
;=> #

rename-to

(rename-to :users)
;=> #

(alter-table :user (rename-to :users)) ;=> #<sxql-statement: alter table rename to>

on-duplicate-key-update

Support MySQL's

INSERT ... ON DUPLICATE KEY UPDATE
syntax.
(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 values fukamachi on duplicate key update>

on-coflict-do-nothing

Support PostgreSQL's

INSERT ... ON CONFLICT DO NOTHING
syntax.
(on-conflict-do-nothing)
;=> #

(on-conflict-do-nothing :index_name) ;=> #<sxql-clause: on conflict constraint index_name do nothing>

(on-conflict-do-nothing '(:column1 :column2 :column3)) ;=> #<sxql-clause: on conflict 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))
;=> #

(on-conflict-do-update '(:column1 :column2 :column3) (set= :x 1 :y 2)) ;=> #<sxql-clause: on conflict 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 age name values fukamachi on conflict do update set where>

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 ([email protected])

License

Licensed under the BSD 3-Clause License.

We use cookies. If you continue to browse the site, you agree to the use of cookies. For more information on our use of cookies please see our Privacy Policy.