postmodern-intro-to-s-sql

Intro to S-SQL

Postmodern can use any sql string in a query. It also has its own lispy syntax called s-sql. Various examples using postmodern will be given in both standard sql and s-sql. Note that not all of sql has been implemented in the s-sql syntax. Postmodern is fully capable of handling any sql string. It just looks a little ugly once you get used to looking at lisp type syntax.

Consider the following database calls and the return. Note that all query functions are postmodern functions, after this next example, I'm going to shorten the function call and drop the "postmodern:". 

(postmodern:query "select id,name from countries where name=$1" "Vietnam")

((68 "Vietnam"))

This can be rephrased in s-sql as: 

(query (:order-by 
        (:select 'countries.name 
                 :from 'countries
                 :where (:= 'name '$1))) 
        "Vietnam")

You will notice that the commas have dropped out, columns and table names are inital-quoted and the sql operators have colons in from. It does look more "lispy" doesn't it?

Profiling

I took the following two toy functions and timed them. 

(defun get-countries-geodata (country-list &optional (geo-type "latitude"))
  "Takes a list of country names and, optionally, a specific geo-type, 
and returns a list of countries and that geo-type."
  (let ((allowable-type (list "latitude" "longitude" "iso" "currency" ))
        (geo-category (substitute #\_ #\- geo-type)))
    (if (find geo-category allowable-type :test 'string=) 
        (let* ((geos
                (flatten
                 (postmodern:query 
                  (format nil "select ~a from countries where name in (~{'~a'~^,~})" 
                          geo-category country-list) )))
               (float-geos (mapcar #'(lambda (x) (/ x 100)) geos)))
          float-geos))))

(defun get-countries-geodata1 (country-list &optional (geo-type "latitude"))
  "Takes a list of country names and, optionally, a specific geo-type, 
and returns a list of countries and that geo-type."
  (let ((allowable-type (list "latitude" "longitude" "iso" "currency" ))
        (geo-category (substitute #\_ #\- geo-type)))
    (if (find geo-category allowable-type :test 'string=)
        (let* ((geos
                (flatten (query (:select 'latitude :from 'countries :where 
                                         (:in 'name (:set country-list))))))
               (float-geos (mapcar #'(lambda (x) (/ x 100)) geos)))
          float-geos))))


(defun ns ()
  (let ((r 1000)) 
      (print 'n1) (time (dotimes (i r) (get-countries-geodata (list "Argentina" "Australia" "New Zealand"))))
      (print 'n2) (time (dotimes (i r) (get-countries-geodata1 (list "Argentina" "Australia" "New Zealand"))))))

(ns)

N1 
Evaluation took:
  0.295 seconds of real time
  0.095985 seconds of total run time (0.051992 user, 0.043993 system)
  32.54% CPU
  627,637,784 processor cycles
  3,723,456 bytes consed


N2 
Evaluation took:
  0.301 seconds of real time
  0.102985 seconds of total run time (0.062991 user, 0.039994 system)
  34.22% CPU
  641,741,648 processor cycles
  5,853,440 bytes consed

The end result is that it looks like the syntactic benefits of postmodern s-sql is slightly less efficient than building an sql string directly.

Simple Database Queries


It can be handy to note that replacing "query" with "sql" returns the sql statement rather than trying to execute the query. This can be helpful in designing s-sql queries. Thus: 

(sql (:select 'countries.name 'regions.name :distinct
              :from 'regions 'countries 
              :where (:= 'regions.id 'countries.region_id)))

 "(SELECT DISTINCT countries.name, regions.name FROM regions, countries 
WHERE (regions.id = countries.region_id))"

Sql-escape, Sql-escape-string

Does what it says on the tin. It escapes a string so that you can safely include the string in an sql query.

(let ((x "\#udlsh29c#^")) (sql-escape x))
"E'#udlsh29c#^'"

(sql-escape-string "\#udlsh29c#^")
"E'#udlsh29c#^'"

Sql-compile

sql-compile is the run-time version of the sql macro, which means that it converts a list into an sql query. See the following as an example. Note carefully the backquotes and commas. 

(defun create-table1 (table-name-string &rest rest)
  "Each of the parameters after the table-name must be in the form of
a two parameter list - the column name as a string and the type as a symbol. 
See the following as an example"
  (query (postmodern:sql-compile
          `(:create-table ,table-name-string ,(loop for y in rest collect
                                    (list (first y)
                                          :type (second y)))))))

(create-table1 "test25" (list "name" 'string) (list "address" 'string))


You also can see how it is used in the following queries handling some insertions and updates in which plists were providing the source of columns and values.
(query
 (sql-compile
  (append `(:insert-into ,table :set) plst)))
(query
 (sql-compile (append (append `(:update ,table :set)
                              plst) `(:where (:= 'id ,id)))))

Remember, if you are using sql-compile or any other method to create dynamic queries, you are responsible for ensuring the security. All user input should be sanitized.

Return Types

Keywords

You can give postmodern various directions, using keywords, for way that values get returned. Some of these keywords will be used in various examples to follow.

Keyword Description
:none Ignore the result values
:lists, :rows Return a list of lists, each list containing the values for a row.


:alists Return a list of aslists which map column names to values with the names as keywords
:alist Return a single row as an alist
:str-alists like :alists, but use the original column names
:str-alist Return a single row as an alist, with strings for names
:plists Return a list of plists which map column names to values with the names as keywords
:plist Return a single row as a plist
:column Return a single column as a list
:single Return a single value
:single! Like :single, but raise an error if the number of selected rows <> 1
The first examples are just using straight sql. Consider the following database calls, written in s-sql and the return, noting how the ending keywords affect the type of return: 
(query (:select 'name 
                :from 'countries
                :where (:> 'latitude '$1)) 
       60)

(("Faroe Islands") ("Finland") ("Greenland") ("Iceland") ("Norway") ("Sweden"))

:none

(query (:select 'name 
                :from 'countries
                :where (:> 'latitude '$1)) 
       60 :none)
NIL


:lists

(query (:select 'name 
                :from 'countries
                :where (:> 'latitude '$1)) 
       60 :lists)
(("Faroe Islands") ("Iceland") ("Greenland") ("Sweden") ("Norway") ("Finland"))


:rows

 (query (:select 'name 
                :from 'countries
                :where (:> 'latitude '$1)) 
       60 :rows)
(("Faroe Islands") ("Iceland") ("Greenland") ("Sweden") ("Norway") ("Finland"))


:alist

(query (:select 'name 
                :from 'countries
                :where (:> 'latitude '$1)) 
       60 :alist)
((:NAME . "Faroe Islands"))


:str-alist

(query (:select 'name 
                :from 'countries
                :where (:> 'latitude '$1)) 
       60 :str-alist)
(("name" . "Faroe Islands"))


:alists

(query (:select 'name 
                :from 'countries
                :where (:> 'latitude '$1)) 
       60 :alists)

(((:NAME . "Faroe Islands")) ((:NAME . "Finland")) ((:NAME . "Greenland")) 
((:NAME . "Iceland")) ((:NAME . "Norway")) ((:NAME . "Sweden")))


:str-alists

(query (:select 'name 
                :from 'countries
                :where (:> 'latitude '$1)) 
       60 :str-alists)

((("name" . "Faroe Islands")) (("name" . "Finland")) (("name" . "Greenland")) 
(("name" . "Iceland")) (("name" . "Norway")) (("name" . "Sweden")))


:plist

(query (:select 'name 
                :from 'countries
                :where (:> 'latitude '$1)) 
       60 :plist)
(:NAME "Faroe Islands")


:plists

(query (:select 'name 
                :from 'countries
                :where (:> 'latitude '$1)) 
       60 :plists)
((:NAME "Faroe Islands") (:NAME "Iceland") (:NAME "Greenland") 
(:NAME "Sweden") (:NAME "Norway") (:NAME "Finland"))


:single

(query (:select 'name 
                :from 'countries
                :where (:> 'latitude '$1)) 
       60 :single)

"Faroe Islands"


:single!

(query (:select 'name 
                :from 'countries
                :where (:> 'latitude '$1)) 
       60 :single!)
; Evaluation aborted on #<CL-POSTGRES:DATABASE-ERROR {100E83B813}>.
LISP-TAX-TEST> Database error: Query for a single row returned 6 rows.
   [Condition of type DATABASE-ERROR]



:column

(query (:select 'name 
                :from 'countries
                :where (:> 'latitude '$1)) 
       60 :column)

("Faroe Islands" "Finland" "Greenland" "Iceland" "Norway" "Sweden")

As or Alias

Suppose you want to return an identifier as a key with the value, but you don't want to use the column name. You can use the as keyword, or as you would expect having just seen a little s-sql, the :as keyword. 

(first (query (:order-by 
        (:select (:as 'countries.name 'countryname)
                 :from 'countries) 
        'countryname ) :alists))
((:COUNTRYNAME . "Afghanistan"))


You can also do this with table names.
(first (query (:order-by 
               (:select 't1.name
                        :from (:as 'countries 't1)) 
               'name ) :alists))
((:NAME . "Afghanistan"))

:|| Concatenating Columns

The concatenation operator combines two or more columns into a single column return. First, consider the query on a raw sql string: 

(query "(SELECT countries.id, (countries.name || '-' || regions.name) 
        FROM countries, regions 
        WHERE ((regions.id = countries.region_id) and (countries.name = 'US')))")
((21 "US-North America"))

Now consider the result using s-sql. 

(query (:select 'countries.id (:|| 'countries.name "-" 'regions.name) 
                :from 'countries 'regions
                :where (:and (:= 'regions.id 'countries.region-id)
                             (:= 'countries.name "US"))))
((21 "US-North America"))

Select / SQL Operators

We've already seen the very simplest selection examples. Now we can start looking at all the different operators.

[Special Characters][A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [P] [Q] [R] [S] [T] [U] [V] [W] [X] [Y] [Z]

sql-op

sql-op :+, :*, :%, :&, :|, :||, :=, :/, :!=, :<, :<>, :>, :<=, :>=, :^, *, /, , <,  >, and, any, as, avg, between, case,  coalesce, commit, constraints, count, current-date, current-timestamp, datatypes, date-part,date-trunc,desc, distinct, distinct-on, enum, except, :except-all exists, group-by, having, in, insert, :intersect, :intersect-all, is-null, join cross-join, join inner-join, join left-join, join outer-join, like, limit, not,now, null, on, or, or, order-by, overlaps, raw, rollback, select, set, some, sum, transactions, truncate, union, union-all, unique, update, using, when, while


Comments