cl-sql notes

Source: https://www.reddit.com/r/Common_Lisp/comments/7s53qi/what_do_you_recommend_to_work_with_sql_databases/dt2oeiu/

I’m no master and my snippets are in spanish, but basically… This is what you can do as quick examples of a way of using it, assuming you have loaded clsql and you have a connection to the db.

What you can do, that is, the pattern I suggest, is the following. For example you need to generate a SELECT statement for a specific purpose. You can define a macro like this (let’s suppose your select only needs to select for a specific column on a specific table):

(defmacro sql-select-document-numbers (&optional args)
 `(sql-operation 'select
                 (col :doc-num :ld)
                 :from (table :ld)
                 ,@args))

For this, you create two utility functions:

(table ) will give you the table name (string) based on a keyword (or symbol or whatever). This is so real table names are never hardcoded.
(col ) will take the keyword of the column name and the keyword of the table name and return you the column name in a way that clsql likes:
(sql-expression :attribute column-name)

So with these two functions, actual table and column names are not hardcoded. You can then put your column names and table names in a separate file.

The macro “sql-select-document-numbers” thus returns a sql-operation that can later be used to perform a select. The @args there allow you to easily pass more selection criteria, like for example a “WHERE …” clause to your sql-operation, so the SQL statement generates with more specific criteria.

If you call (sql-select-document-numbers ), it will give you back an clsql object that can be queried, by using, for example (query …) or (print-query …) or other clsql functions. Or that you can put inside another statement, for example an INSERT that uses the records from your SELECT:

(insert-records :into (table :docs)
               :attributes (list (col :doc-num))
               :query ,my-query)

Or more complex queries, like SELECT … where … IN . Or for example SELECT … INTERSECT SELECT . You get the idea. For example, here (sorry, cheap translation):

  (defmacro execute-complex-stuff (part counterpart)
   `(%create-temp-table-and-execute-queries 
     ;; query that return documents that comply with 'part' and 'counterpart'
     (sql-select-document-numbers (:where
                                  ,part
                                  :set-operation 
                                  (sql-intersect
                                   (sql-select-document-numbers
					(:where 
                                     ,counterpart)))))
     ;; query that returns entries that comply with either 'part' or 'counterpart'
     (sql-select-entries (:where
                           (sql-and
				(sql-in
                             (col :doc-num)
                             (sql-operation 'select
						(col :doc-num)
						:from
						(table :docs)))
				;; and entries should have the following criteria
				(sql-or ,part ,counterpart))))))

What i do here is i assemble two sql statements, one is a Select … INTERSECT that uses a where clause from the “contraparte” argument, another is a Select … where :doc-num IN (… another select …) AND records comply with OR , where “parte” and “contraparte” arguments will further specify which records to select. These two statements i bring to a function that will do further things with them – create temporary tables, execute, etc.

The point is that you can easily create flexible Sql statements that are used inside other more complex sql statements and so on.

So on to what I wrote regarding creating tables from CLSQL. This would create a table from scratch, for example:

(defun crear-tabla-fechas ()
     (drop-table (table :fechas) :if-does-not-exist :ignore)
     (create-table (table :fechas) 
                   `((,(col :fecha :fechas) :db-type "DATE")
                    (,(col :fin-de-mes :fechas) :db-type "BIT")
                    (,(col :dia :fechas) :db-type "INTEGER")
                    (,(col :dia-semana :fechas) :dbtype "INTEGER")
                    (,(col :dia-semana-texto :fechas) :dbtype "VARCHAR(20)"))))

Note that here i’m specifying the real, actual database type of each column, with the :db-type option. (The default way of using clsql is by using clsql data types, which clsql maps automatically to whatever database types clsql thinks are good.)

You can also infer what is the information (type, precision, etc) of each column of an existing data table by using (attribute-type), for example in this way:

(multiple-value-bind
   (type precision scale nulls-accepted) 
   (attribute-type (col column-key table-key)
                   (table table-key)))

So with these snippets you can generate tables from zero, or create a table using the columns of another table, etc etc.

priyadarshan/cl-sql.org