USAGE:
CONNECT database /create /flat /direct /no-copy /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
/no-copy -- Clear buffer instead of copy
/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.
2.1.1 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.
2.1.2 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.
2.1.3 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.
2.1.4 No-copy
By default the SQLite driver copies the buffer block returned by the SQL function. For large numbers of values it may be more efficient to pass the buffer as a reference instead.
2.1.5 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.
2.1.6 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).
2.1.7 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.
2.1.8 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.
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.
|
cid
|
integer
|
Column ID
|
|
name
|
string
|
Column name
|
|
type
|
string
|
Column type
|
|
notnull
|
integer
|
Not null flag
|
|
dflt_value
|
any
|
Default value
|
|
pk
|
integer
|
Primary key flag
|
2.3.1 Index
This refinement instead returns information about a specific index.
|
seqno
|
integer
|
Sequence number
|
|
cid
|
integer
|
Column ID
|
|
name
|
string
|
Column name
|
2.3.2 Indexes
This refinement returns information about the indexes on a table.
|
seq
|
integer
|
Sequence
|
|
name
|
string
|
Index name
|
|
unique
|
integer
|
Unique flag
|
2.3.3 Fkeys
This refinement returns information about the foreign keys (if any) that reference a table.
|
id
|
integer
|
The index of the foreign key in the list of foreign keys for the table, 0-based
|
|
seq
|
integer
|
The index of the column referenced in the foreign key, 0-based
|
|
table
|
string
|
The name of the referenced table
|
|
from
|
string
|
The column name in the local table
|
|
to
|
string
|
The column name in the referenced table
|
USAGE:
SQL statement /flat /direct
DESCRIPTION:
Prepare and execute an SQL statement.
ARGUMENTS:
statement -- SQL statement (Type: string block)
REFINEMENTS:
/flat -- Do not return rows as blocks
/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.
2.7.1 Flat
This refinement forces the current statement to be processed as if the /flat directive were in effect.
2.7.2 Direct
This refinement forces the current statement to be processed as if the /direct directive were in effect.
