Driver Guide

Author : Ashley G Truter
Updated: 16-Oct-2011
Purpose: Describes the design and operation of the SQLite Driver.

The SQLite Driver object

The driver is implemented as a context named SQLite with nine functions exported to the global context. By convention, and to distinguish these “database” words from other words, the function names always appear in upper-case.

Result codes

Of the twenty nine SQLite result codes defined here, only four are used by the driver.

CodeMeaning
SQLITE_OKSuccessful result.
SQLITE_BUSYThe database file is locked.
SQLITE_ROWsqlite_step has another row ready.
SQLITE_DONEsqlite_step has finished executing.

Library functions

Nineteen SQLite library functions are implemented via matching routine! names, except that the sqlite3_ prefix is replaced with an aster and underscores are replaced with hyphens. All these routines, apart from *open and *close, are used in the SQL function.

REBOL RoutineC/C++ API
bind-blobsqlite3_bind_blob
bind-doublesqlite3_bind_double
bind-intsqlite3_bind_int
bind-nullsqlite3_bind_null
bind-textsqlite3_bind_text
closesqlite3_close
column-blobsqlite3_column_blob
column-countsqlite3_column_count
column-doublesqlite3_column_double
column-integersqlite3_column_int
column-namesqlite3_column_name
column-textsqlite3_column_text
column-typesqlite3_column_type
errmsgsqlite3_errmsg
finalizesqlite3_finalize
opensqlite3_open
preparesqlite3_prepare
resetsqlite3_reset
stepsqlite3_step

Database access functions

The nine database access functions are exported to the global context and use the routines described above to open, access and close SQLite database files.

FunctionRefinement(s)
CONNECT
Open a SQLite database
create - Create database if non-existent
flat - Do not return rows as blocks
direct - Do not mold/load REBOL values
timeout - Specify alternate retry limit (default is 5)
format - Format output
info - Obtain column names and widths
log - Log all SQL statements
DATABASE
Database tasks
analyze - Gather statistics on indexes
vacuum - Reclaim unused space
check - Perform an integrity check
DESCRIBE
Information about a database object
index - Describes an index
indexes - Indexes on table
fkeys - Foreign keys that reference table
DISCONNECT
Close database connection
NA
EXPLAIN
Explain an SQL statement
NA
EXPORT
Export result table to a CSV file
NA
IMPORT
Import CSV file into a table
no-header - Use generic column names
INDEXES
List all indexes
NA
ROWS
Return row count
NA
SQL
Prepare and execute an SQL statement
direct - Do not mold/load REBOL values
TABLES
List all tables
NA

Directives

Seven directives, all of them set via CONNECT refinements, control various aspects of the driver's behaviour.

DirectiveDescription
retryNumber of 1 second intervals to try if SQLITE_BUSY
flat?Don't return rows as blocks
direct?Bypass mold/load conversions
log?SQL statement logging
format?Format output
col-info?column names and widths

While it is rare that you need to change directives from the console, it can be quite useful when debugging to toggle the format? directive as follows:

>> SQLite/format?: true
>> SQL "select * from my-table"
>> SQLite/format?: false

Using the Driver

SQL Statements

The SQL function supports statements in one of two forms:

Examples of some string statements are:

CONNECT/create %test.db
SQL "create table t (col_1, col_2, col_3)"
SQL {insert into t values (1, '1-Jan-2000', '"A string"')}
SQL "select * from t where col_1 = 1"
SQL "select * from t where col_2 = '1-Jan-2000'"
SQL {select * from t where col_3 = '"A string"'}

If the database was opened with CONNECT/direct then the last statement would be written as:

SQL "select * from t where col_3 = 'A string'"

Value Binding

The SQLite driver supports value binding which makes it much easier to generate dynamic SQL statements within your code. Value binding works by replacing each unquoted ? within your statement with the next value in the statement block. Using value binding, the examples above would be written as:

SQL ["insert into t values (?, ?, ?)" 1 1-Jan-2000 "A string"]
SQL ["select * from t where col_1 = ?" 1]
SQL ["select * from t where col_2 = ?" 1-Jan-2000]
SQL ["select * from t where col_3 = ?" "A string"]

As can be seen from these examples, value binding implicitly quotes REBOL values so you don't have to construct cumbersome statement strings yourself!

Column Names

Sometimes you may need to know the column names (and widths) used in the last SELECT statement. These are available in the SQLite/columns block and can be used as follows:

>> SQLite/col-info?: true
== true
>> SQL "select * from t"
== []
>> SQLite/columns
== ["c1" "c2" "c3"]
>> SQL "select count(*) from t"
== [[0]]
>> SQLite/columns
== ["count(*)"]
>> SQLite/col-info?: false
== false

SQL Buffer

SQL statements return their result set in a 32Kb value buffer which is returned as a reference. If you need to preserve a copy of these values, because another SQL statement will be executed, then make sure you copy the result set; as in:

data: copy SQL "select * from t"

Unlike most REBOL functions, which return a copy of their result set, the SQLite driver returns a reference for several good reasons:

Transactions

SQLite is auto-commit by default which means that the changes caused by each statement are written out at the conclusion of the statement. This is good for concurrency (lock duration is minimized) but not so good when you need a set of statements to succeed or fail together (i.e. a logical "transaction"), or you have an INSERT in a tight loop. Consider the following:

repeat i 1000 [
    SQL reduce [
        "insert into t values (?, ?, ?)" i now/date + i join "String " i
    ]
]

Not only will this take a long time, but it will cause significant disk thrashing.

Fortunately, SQLite lets you turn auto-commit off and on with the begin and commit statements. Doing the following:

SQL "begin"
repeat i 1000 [
    SQL reduce [
        "insert into t values (?, ?, ?)" i now/date + i join "String " i
    ]
]
SQL "commit"

will dramatically improve the speed of this operation.

Don't forget to commit as the database file will be locked until auto-commit is turned back on.