dbi_sqlite3

dbi_interface ?objectName?

DESCRIPTION

The dbi_sqlite command creates an object objectName that can be used to connect to sqlite databases using its native API. If objectName is not provided, a name will be automatically generated and returned by the command The object created supports the dbi interface for accessing and querying databases.

The object supports transactions, lines, serials, views and checks. Foreign keys are accepted, but not checked. sqlite3 dbi objects do not support column permissions, roles, domains or permissions, as these are not supported by sqlite3.

Extra commands supported

objectName function name script

Create an Sqlite function with name name. script will be executed when the function is called (with parameters apended to it).

objectName collate name script

Create a new Sqlite collation with name name. script will be executed when the collation is used with to strings as parameters. The application defined collation routine should return negative, zero or positive if the first string is less than, equal to, or greater than the second string

objectName backup ?database? filename

The "backup" method makes a backup copy of a live database. The optional database argument tells which database in the current connection should be backed up. The default value is main (or, in other words, the primary database file). To back up TEMP tables use temp. To backup an auxilary database added to the connection using the ATTACH command, use the name of that database as it was assigned in the ATTACH command. filename is the name of a file into which the backup is written. filename does not have to exist ahead of time, but if it does, it must be a well-formed SQLite database.

objectName restore ?database? filename

The "restore" method copies the content a separate database file into the current database connection, overwriting any preexisting content. The optional database argument tells which database in the current connection should be overwritten with new content. The default value is main (or, in other words, the primary database file). To repopulate the TEMP tables use temp. To overwrite an auxilary database added to the connection using the ATTACH command, use the name of that database as it was assigned in the ATTACH command. filename is the name of a existing well-formed SQLite database file from which the content is extracted.

objectName progress n callback

This method registers a callback that is invoked periodically during query processing. There are two arguments: the number of SQLite virtual machine opcodes between invocations n, and the TCL command callback to invoke. Setting the progress callback to an empty string disables it. The progress callback can be used to display the status of a lengthy query or to process GUI events during a lengthy query.

objectName incrblob ?-readonly? ?db? table column rowid

This method opens a TCL channel that can be used to read or write into a preexisting BLOB in the database, identified by table column, rowid, and optionally db. This optional database name is not the filename that contains the database but rather the symbolic name of the database that is assigned when the database is connected using ATTACH. For the main database file, the database name is "main". For TEMP tables, the database name is "temp". The command returns a new TCL channel for reading or writing to the BLOB. The channel is opened using the underlying sqlite3_blob_open() C-langauge interface. Close the channel using the close command of Tcl.

objectName import conflict-algorithm table filename ?separator? ?nullindicator? ?header?

This method imports the data from the file filename in the table table. By default different fields are separated by tabs; the separator can be changed using the separator option. An empty value is imported as a NULL value. This can be changed by using the nullindicator option. As in the default sqlite imnport, the method does not properly support quoting in csv files (yet), meaning fields cannot contain the separator character, or be multiline.

header: By default, the method assumes that the file loaded will contain the same number of columns in the same order as the the table, and no header. If header is given, but empty, the first line in the file is taken as a header to determine what columns are in the file. If not empty, it is used as a list of fields in the file.

conflict-algorithm can be either of rollback,abort,fail,ignore,replace. It gives the conflict resolution algorithm that will be used when errors occur during inserts.

Extra collations

DICT

dictionary sort: Sort alphabetically, but numbers are treated as integers: e.g. test2 wil be sorted before test10

DICTREAL

dictionary sort: Sort alphabetically, but numbers are treated as reals: e.g. a2.0555 wil be sorted before a2.08.

Extra functions

regexp

This function has to parameters: the pattern and the value. Thus allows the use of regular expressions in queries, e.g.: where "first_name" regexp '^[Jj]o'

list_concat

This aggregate function returns a proper list of all elements in the query

SEE ALSO

interface_dbi interface_dbi_admin

KEYWORDS

• dbi
• sqlite
• sqlite3
• interface
• database
• sql

Peter De Rijk

hosted at