S-SQL Examples A

Alter Table

(query (:alter-table "countries" :add-column "stuff4" :type integer :default 0))

(query (:alter-table "countries" :add-column "stuff4" :type integer))

(query (:alter-table "countries" :add-column "stuff4" :type (or db-null integer)))

The difference between these versions is how they handle nulls. The first will generate a column that cannot be null and will set a default of 0. The second will not set a default but will still generate a column that cannot be null. The third will not set a default and the column is allowed to be null. Note that because the table name is in double quotes, if you have a multiple word table name, the words must be separated by the normal sql underscores, not the normal "lispy" hyphens.

You can use sql-compile to build the alter-table statements dynamically like so:

(let ((table "countries") (column "stuff4"))
  (query (sql-compile `(:alter-table ,table :add-column ,column
                        :type (or db-null integer)))))

Altering Columns

At the moment, altering columns requires that you still include a blank :type keyword.

As an example, assume you created a table named "test2" with a column named "description" with a not null constraint

and you want to drop the not null constraint on the table. Either of the following will work

(the only differences being whether you quote the names or use strings for the names):

(query (:alter-table "test2" :alter-column "description" :type (:drop not-null)))

(query (:alter-table 'test2 :alter-column 'description :type (:drop not-null)))

And

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

  (("Bermuda") ("Canada") ("Greenland") ("Mexico") ("US"))

  (query (:select 'countries.name
                  :from 'countries 'regions
                  :where (:and (:= 'region-id 'regions.id)
                               (:= 'regions.name "Central America")
                               (:< 'latitude 12))))

(("Costa Rica") ("Panama"))

Analyze

The official Postgresql documents note that analyze collects statistics about a database and provides various options, whether you want specific tables and columns or the entire database, etc. The following is a series of examples of how it can be used in s-sql with increasing levels of complexity. Options can be set to true with t, 1 or :on and set to false with :nil 0 or off.

(query (:analyze))

(query (:analyze :verbose))
;; Now specifying just analyzing table t1
(query (:analyze :verbose :tables 't1))

(query (:analyze :verbose :tables :t1))
;; Now specifying just analyzing tables t1 and t2
(query (:analyze :verbose :tables :t1 :t2))

(query (:analyze :verbose :tables 't1 't2))
;; Now specifying columns in tables t1 and t2
(query (:analyze :verbose
        :tables (:t1 't1c1 't1c2) (:t2 't2c1 't2c2 't2c3)))
;; Alternative syntax for the columns
(query (:analyze :verbose :tables (:t1 :t1c1 :t1c2)))
;; Starting to look at more specific options
(query (:analyze :option (:verbose t)
                 :tables (:t1 't1c1 't1c2) (:t2 't2c1 't2c)))
;; The following will set option verbose to true, skip-locked to false
;; and buffer-usage-limit to 70MB
(query (:analyze :option (:verbose 1) (:skip-locked 0) (:buffer-usage-limit "70MB")
                 :tables (:t1 't1c1 't1c2) (:t2 't2c1 't2c)))
;; The following will set option verbose to true, skip-locked to false
;; and buffer-usage-limit to 70MB
(query (:analyze :option (:verbose t) (:skip-locked nil) (:buffer-usage-limit "70MB")
                 :tables (:t1 't1c1 't1c2) (:t2 't2c1 't2c)))

Any, Any*

Any needs to be considered as a special case. Quoting Marijn Haverbeke here,"Postgres has both a function-call-style any and an infix any, and S-SQL's syntax doesn't allow them to be distinguished." As a result, postmodern has a regular :any sql-op and a :any* sql-op, which expand slightly differently.

In general, the any qualifier in an sql statement looks at a subquery and does a comparison against that subquery. Sticking with our countries table, we have latitude, longitude data for every country (I'm not sure whether my table pulled the capital cities or the geographic center) and some designated a region for each country, so we have a region-id that matches the primary key 'id' in a regions table.

Out of curiosity, let's determine which countries in "North America" have a longitude less than any country in "South America". The standard sql could look like this:

  (query "select countries.name
               from countries,regions
               where regions.id=region_id
               and regions.name='North America'
               and longitude > any(select longitude
                                          from countries, regions
                                          where region_id = regions.id
                                          and regions.name='South America')")

(("Bermuda") ("Greenland"))

This can be re-phrased in s-sql as

(query  (:select 'countries.name
         :from 'countries 'regions
                 :where (:and (:= 'regions.id 'region-id)
                              (:= 'regions.name "North America")
                              (:> 'longitude
                                  (:any
                                   (:select 'longitude
                                    :from 'countries 'regions
                                            :where (:and (:= 'regions.id 'region-id)
                                                         (:= 'regions.name
                                                             "South America"))))))))

(("Bermuda") ("Greenland"))

Subselects work fine in both regular sql and s-sql. If you have already calculated your subselect and put it in a variable, that variable can be a list or a vector and whether you should use the :any sql-op or the :any* sql-op depends on your phrasing. (Notice that the second variation has an asterisk).

The SQL keyword ANY can be used in a parameterized sql statement. The following two toy examples work in raw sql.

(query "select name from countries where id=any($1)"
       (vector 21 22))

(("Iceland") ("US"))

(let ((toy-query (vector 21 22)))
  (query "select name from countries where id=any($1)"
         toy-query))

(("Iceland") ("US"))

Now using s-sql and keeping with the toy example, notice that using :any does not work, but using :any* does work.

(let ((toy-query '(21 22)))
  (query (:select 'name
          :from 'countries
          :where (:= 'id (:any '$1)))
         toy-query))

;; Evaluation aborted on
;; #<CL-POSTGRES-ERROR:SYNTAX-ERROR-OR-ACCESS-VIOLATION {10030AF6A1}>.

(let ((toy-query '(21 22)))
  (query (:select 'name
          :from 'countries
          :where (:= 'id (:any* '$1)))
         toy-query))

(("Iceland") ("US"))

Arrays - see Array-Notes

As (sometimes referred to as Alias)

  (query (:select (:as 'countries.name 'country)
                (:as 'regions.name 'region)
                :from 'countries 'regions
                :where (:and (:= 'regions.id 'region-id)
                             (:= 'regions.name "Central America")))
       :alists)

(((:COUNTRY . "Belize") (:REGION . "Central America"))
 ((:COUNTRY . "Costa Rica")  (:REGION . "Central America"))
 ((:COUNTRY . "El Salvador")  (:REGION . "Central America"))
 ((:COUNTRY . "Guatemala")  (:REGION . "Central America"))
 ((:COUNTRY . "Panama") (:REGION . "Central America"))
 ((:COUNTRY . "Nicaragua") (:REGION . "Central America")))

The following uses aliases for both columns and tables in the from and inner-join clauses:

(query (:order-by
      (:select (:as 'recs.firstname 'firstname)
               (:as 'recs.surname 'surname)
               :distinct
               :from (:as 'cd.members 'mems)
               :inner-join (:as 'cd.members 'recs)
               :on (:= 'recs.memid 'mems.recommendedby))
      'surname 'firstname))

Note: Postmodern does not allow you to create an unescaped string alias. In other words, you cannot generate this:

"select sum(slots as "Total Slots" from cd.bookings"

without using :raw

Avg

Simple example easily applicable to max and min:

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

((-17939/200))

S-SQL Examples Home Page