Using DBI to Run Scripts & Load Prepared Statement Scripts

There are a couple of utility functions that I have designed into DBI that was not previously discussed. They are both oriented to work with SQL scripts.

I don't know about you, but I like to write SQL statements in SQL scripts:

-- a sql query inside a sql script
select * 
  from table1;

instead of embedding them as strings in programming languages:

;; a sql query embedded in scheme code 
(exec handle "select * from table1")

- it just looks so much nicer.

If you have a ton of complex prepared statements, you'll find you'll have such statements littered everywhere, which makes them difficult to maintain.

Of course - a possible solution to this problem is to create a DSL so the SQL strings can be generated. We might eventually entertain such solution, but it's clear that any such DSL will be non-trivial.

A simpler but equally as powerful of an idea is to move all such queries into SQL scripts, and have the database handle load the scripts and convert them into prepared statements.

Loading Prepared Statement Scripts

You can supply a #:load parameter to the three RDBMS drivers' connect procedure:

;; example - connect to spgsql 
(define handle 
  (connect 'spgsql <regular-args> ... 
           '#:load <path-string? or (listof path-string?)>))

The #:load parameter takes either a path-string? or a list of path-string?, all of which needs to point to valid prepared statement scripts. The connect procedure will then load each of the scripts and convert them into prepared statements.

Format of the Prepared Statement Scripts

In order for this to work - you'll need to follow some conventions:

• You can have multiple prepared statements in a single script, and it must follow the order of <name-of-the-statement>\n+ <statement>\n+ ...
• the name of the statement must be on a line starting with --;;-- , follow by the name itself, and then nothing else (besides additional whitespace)
• the name of the statement is basically a regular scheme symbol, but more restricted - like a regular scheme function name, which would consist of alphanumeric, -, _, ?, and !

An example will make it more clear - below is a sample script that contains 2 prepared statements:

--;;-- session-ref 
select session_value
  from session_value_t 
 where session_uuid = ?uuid 
   and session_key = ?key 

--;;-- session-update! 
update session_value_t 
   set session_value = ?val
 where session_uuid = ?uuid 
   and session_key = ?key

The first statement's name is session-ref, and the second is session-update!. If you need more in a single script - just keep extending it with the same format. You can add other regular SQL comment lines in between, just make sure it does not start with --;;--.

Because the #:load parameter can load a list of scripts, you can organize your scripts however you want.

Run a SQL Script

The other script-related capability that DBI has is to run a SQL script. This is useful if you want to have installation scripts that has a set of SQL queries that needs to be executed sequentially (like creating a bunch of tables). All you need to do is to write up the script, and then:

(run-script! handle <path-to-script> <query-args>) 

The args have the same format as the args for query (a list of key/value pairs). This means that your script can also contain placeholders like your prepared statements - they'll get passed to the statements as usual. Note because such script will contain multiple queries, if you do not want to pass the same value to a subsequent query, you must use a different placeholder name for the subsequent query.

Now - such a script also has a special format to ensure it works correctly. Basically, you must use the semicolon (;) terminator for the queries - below is an example script - notice the semicolon:

create table session_t 
( session_id integer primary key auto_increment not null 
, session_uuid varchar(32) unique not null 
, expiration date not null) ; -- query terminator 

create table session_value_t 
( session_value_id integer primary key auto_increment not null 
, session_uuid varchar (32) not null 
, session_key varchar (128) not null 
, session_value text null 
, unique ( session_uuid , session_key ) 
)

You'll also notice that the second statement does not have a terminator so there won't be an empty query.

This is capability is only available since planet version (1 3), i.e.

(require (planet bzlib/dbi:1:3)) 

The two capability should help you a lot in writing complex sql queries and scripts.

That's it for now. Enjoy.