User Guide

Author : Ashley G Truter
Updated: 5-Nov-2006
Purpose: Describes the use of SQL & the DB access functions.

SQL

The following SQL statements and expressions are supported by the SQLite3 library.

TypeStatements
ConnectionsATTACH
DETACH
TransactionsBEGIN
COMMIT
ROLLBACK
Data DefinitionALTER
CREATE [ INDEX | TABLE | TRIGGER | VIEW ]
DROP [ INDEX | TABLE | TRIGGER | VIEW ]
Data MaintenanceANALYZE
REINDEX
VACUUM
Data ManipulationDELETE
EXPLAIN
INSERT
SELECT
UPDATE
expressions

Database access functions

Nine database access functions are used to open, access and close SQLite database files.

CONNECT

USAGE:
    CONNECT database /create /flat /direct /timeout retries /format /info /log

DESCRIPTION:
    Open a SQLite database.

ARGUMENTS:
    database -- (Type: file block)

REFINEMENTS:
    /create -- Create database if non-existant
    /flat -- Do not return rows as blocks
    /direct -- Do not mold/load REBOL values
    /timeout -- Specify alternate retry limit (default is 5)
        retries -- Number of 1 second interval retries if SQLITE_BUSY (Type: integer)
    /format -- Format output
    /info -- Obtain column names and widths
    /log -- Log all SQL statements

If database is provided as a block of files then the first file specified will be opened and the remaining files (up to a limit of ten) attached to it. Tables that are unique across all attached databases do not need to be qualified (with the database name) when referenced within SQL statements.

Create

The CONNECT function is used to open a SQLite database file. An error will occur if this file does not exist, but using the /create refinement will create the database file if it does not already exist.

Flat

This refinement controls how values are returned from a SELECT statement; either with each row in its own block (the default) or all values in a single block. As an example, assume we have a table with two columns and two rows.

>> connect %test.db
>> sql "select * from t"
== [["A" 1] ["B" 2]]

and:

>> connect/flat %test.db
>> sql "select * from t"
== ["A" 1 "B" 2]

Although the first form is often easier to work with, the second is much more efficient; especially when large numbers of rows are returned.

Direct

By default, the driver will mold non-numeric values that are inserted into tables and load them when selected. This ensures values like:

"A string"
a-word
1-Jan-2006

are stored as SQLite TEXT in the form:

{"A string"}
{a-word}
{1-Jan-2006}

which load subsequently returns to their original REBOL datatype(s).

Apart from the conversion overhead, there is an obvious two byte storage overhead with each and every string! value. If your database only needs to store and access numerical data and strings (i.e. you are not interested in other REBOL datatypes) then using the /direct refinement will bypass this conversion and save storage space.

Timeout

When a SQL statement receives a SQLITE_BUSY return code, because another process has a file lock, the statement will be retried up to five times (by default) at one second intervals. This refinement enables you to specify an alternate retry limit that better suits your operating environment.

Format

This causes all output to be printed to the console in a MySQL-like format.

Note that as the width of each value must be individually determined it is not recommended that you use this refinement with large result sets (like any other console output it can always be stopped by pressing ESC).

Info

Every SQL statement will have its columns stored in SQLite/columns and its column widths stored in SQLite/widths. This refinement is for those who wish to create their own SQL display clients and carries similar performance penalties as covered in the format refinement above.

Log

Every connect, disconnect, error, SQL statement and statement retry will be logged to %sqlite.log. While this can be useful to monitor what SQL statements are being issued and what the volume and distribution is; be sure to monitor the size of this file in high transaction environments.

DATABASE

USAGE:
    DATABASE /analyze /vacuum /check

DESCRIPTION:
    Database tasks.

REFINEMENTS:
    /analyze -- Gather statistics on indexes
    /vacuum -- Reclaim unused space
    /check -- Perform an integrity check

analyze

Gathers and stores statistics about tables and indices so the query optimizer can use them to help make better query planning choices. See http://www.sqlite.org/lang_analyze.html for more details.

vacuum

Rebuilds the entire database. See http://www.sqlite.org/lang_vacuum.html for more details.

check

Performs an inegrity check looking for out-of-order records, missing pages, malformed records, and corrupt indices. See http://www.sqlite.org/pragma.html#pragma_integrity_check for more details.

DESCRIBE

USAGE:
    DESCRIBE object /index /indexes /fkeys

DESCRIPTION:
    Information about a database object (default is table).

ARGUMENTS:
    object -- (Type: string)

REFINEMENTS:
    /index -- Describes an index
    /indexes -- Indexes on table
    /fkeys -- Foreign keys that reference table

By default this function returns a flat block (see the /flat refinement of CONNECT) consisting of the following six values per column, in ascending column number order.

ColumnTypeDescription
cidintegerColumn ID
namestringColumn name
typestringColumn type
notnullintegerNot null flag
dflt_valueanyDefault value
pkintegerPrimary key flag

Index

This refinement instead returns information about a specific index.

ColumnTypeDescription
seqnointegerSequence number
cidintegerColumn ID
namestringColumn name

Indexes

This refinement returns information about the indexes on a table.

ColumnTypeDescription
seqintegerSequence
namestringIndex name
uniqueintegerUnique flag

Fkeys

This refinement returns information about the foreign keys (if any) that reference a table.

ColumnTypeDescription
idintegerThe index of the foreign key in the list of foreign keys for the table - 0-based
seqintegerThe index of the column referenced in the foreign key - 0-based
tablestringThe name of the referenced table
fromstringThe column name in the local table
tostringThe column name in the referenced table

DISCONNECT

USAGE:
    DISCONNECT

DESCRIPTION:
    Close database connection.

This function closes the current database file using the Database Identifier stored in SQLite/dbid.

INDEXES

USAGE:
    INDEXES

DESCRIPTION:
    List all indexes.

This function returns three values for each index.

ColumnTypeDescription
tbl_namestringTable name
namestringIndex name
sqlstringCreate syntax

EXPLAIN

USAGE:

EXPLAIN statement 

DESCRIPTION:

Explain an SQL statement.
EXPLAIN is a function value.

ARGUMENTS:

statement -- SQL statement (Type: string block)

EXPORT

USAGE:
    EXPORT file statement 

DESCRIPTION:
    Export result table to a CSV file.
    EXPORT is a function value.

ARGUMENTS:
    file -- CSV file to export to (Type: file)
    statement -- SQL statement (Type: string block)

This function exports the result table to the specified file.

IMPORT

USAGE:
    IMPORT file /no-header 

DESCRIPTION:
    Import CSV file into a table.
    IMPORT is a function value.

ARGUMENTS:
    file -- CSV file to import from (Type: file)

REFINEMENTS:
    /no-header -- Use generic column names

This function imports a CSV file into a table with the same name as the file (less extension and spaces replaced with underscores).

ROWS

USAGE:
    ROWS table

DESCRIPTION:
    Return row count.

ARGUMENTS:
    table -- (Type: string)

SQL

USAGE:
    SQL statement /direct

DESCRIPTION:
    Prepare and execute an SQL statement.

ARGUMENTS:
    statement -- SQL statement (Type: string block)

REFINEMENTS:
    /direct -- Do not mold/load REBOL values

This function lets you issue SQL statements such as SELECT, INSERT, UPDATE and DELETE against a SQLite database. See the Driver Guide for information on value binding and retrieval.

Flat

This refinement forces the current statement to be processed as if the /flat directive were in effect.

Direct

This refinement forces the current statement to be processed as if the /direct directive were in effect.

TABLES

USAGE:
    TABLES

DESCRIPTION:
    List all tables.

This function returns two values for each table.

ColumnTypeDescription
tbl_namestringTable name
sqlstringSQL create syntax