SQLite 3 interface for PicoLisp

This PicoLisp library acts as an interface to the command line sqlite3 binary program. It can be included in other PicoLisp programs and can be used to execute pre-defined SQL queries with optional parameters.

1. Requirements
2. Getting started
3. Usage
4. Examples
5. How it works
6. Testing
7. Contributing
8. License

Requirements

• PicoLisp 64-bit pil21
• SQLite3 binary

Getting started

This library requires PicoLisp and the sqlite3 binary. On Debian systems, they can be installed with:

apt-get install picolisp sqlite3

To ensure everything works on your system, run the tests first:

make check

Usage

The library file to be loaded from PicoLisp programs is: db.l.

To start, the following default variables are defined:

The above variables can be overwritten on the command line, example:

export SQLITE_QUERY_TABLE=my-table.l

They can also be overwritten in PicoLisp before loading db.l, example:

: (sys "SQLITE_QUERY_TABLE" "my-table.l")
-> "my-table.l"
: (load "db.l")
-> db-result

Examples

The following example is also used by the Makefile for testing the library with test.l.

Build the database

This generates a test database using the test database schema file test.db.schema.

make test.db

Set some variables

This exports the test query table and database file names to be used by db.l.

export SQLITE_QUERY_TABLE=test-table.l
export SQLITE_DATABASE=test.db

Load the library in PicoLisp

This starts PicoLisp and loads db.l.

pil db.l

Execute some queries

This executes four SQL queries defined in test-table.l.

# Example

: (sql 'array-usernames)
-> ("alice" "bob" "charlie")
: (sql 'object-user-info '((User_id "1")))
-> (("bob" "2" "22") ("charlie" "3" "23"))
: (sql 'single-user-id '((Username "alice")))
-> "1"
: (sql 'generic-users '((Order "RANDOM()")))
-> ("charlie" "3")

How it works

All SQL commands must be executed by the public (sql) function. It takes 2 arguments: the query, and optional key-value pair arguments.

The query is a function name that is prefixed by either single-, array-, object-, or somethingelse-. This can be seen in the (db-result) code definition.

All queries are fixed and defined in the (db-query-table) function of the *SQLITE_QUERY_TABLE variable (defaults to table.l). See test-table.l for an example.

The second argument of each query is a string which acts as a comment for your future self. The third argument is the actual SQL query, which can contain variables loaded from the environment (i.e: passed as key-value pair arguments).

Let’s look at a simple example table.l:

[de db-query-table
  (single-user-id   "The user id of a specific user given the 'Username'"
                    (pack "SELECT user_id FROM users WHERE username='" Username "'") )
    ]

In the above example, the query type is single, and it accepts one argument Username. It would be called from PicoLisp like this:

(sql 'single-user-id '((Username "bob")))

In the above example, the only argument is the key-value pair (Username "bob") where Username in the single-user-id query will be substituted by the value bob, and the final SQL query will look like this:

SELECT user_id FROM users WHERE username='bob'

Of course, this will not prevent an SQL injection attack with the key-value pair (Username "'; DROP TABLE users;'"), so please use proper form and data validation prior to submitting values for the SQL query.

Testing

This library includes a small suite of unit tests. To run the tests, type:

make check