sqlite3(n) 3.6.13 "An interface to the SQLite3 database engine"

Name

sqlite3 - An interface to the SQLite3 database engine

Table Of Contents

• Table Of Contents
• Synopsis
• Description
• API
• Object API
• Keywords

Synopsis

Description

SQLite3 is a self-contained, zero-configuration, transactional SQL database engine. This extension provides an easy to use interface for accessing SQLite database files from Tcl.

API

The interface to the SQLite library consists of single tcl command named sqlite (version 2.8) or sqlite3 (version 3.0). Because there is only this one command, the interface is not placed in a separate namespace.

The sqlite3 command is used as follows:

sqlite3 dbcmd database-name

The sqlite3 command opens the database named in the second argument. If the database does not already exist, it is automatically created. The sqlite3 command also creates a new Tcl command to control the database. The name of the new Tcl command is given by the first argument. This approach is similar to the way widgets are created in Tk.

The name of the database is just the name of a disk file in which the database is stored.

Once an SQLite database is open, it can be controlled using methods of the dbcmd. There are currently 19 methods defined. The use of each of these methods will be explained in the next section.

Object API

Each database command created via sqlite or sqlite3 provides the following methods:

dbcmd close

As its name suggests, calling this method just closes the database. This has the side-effect of deleting the dbcmd Tcl command. Here is an example of opening and then immediately closing a database:

    sqlite3 db1 ./testdb
    db1 close 

If you delete the dbcmd directly, that has the same effect as invoking the close method. So the following code is equivalent to the previous:

    sqlite3 db1 ./testdb
    rename db1 {} 

dbcmd eval sql ?array-name? ?script?

This is the most useful dbcmd method. It is used to execute SQL on the database. The job of the eval method is to execute the SQL statement or statements given in the second argument. For example, to create a new table in a database, you can do this:

    sqlite3 db1 ./testdb
    db1 eval {CREATE TABLE t1(a int, b text)} 

The above code creates a new table named t1 with columns a and b. What could be simpler?

Query results are returned as a list of column values. If a query requests 2 columns and there are 3 rows matching the query, then the returned list will contain 6 elements. For example:

    db1 eval {INSERT INTO t1 VALUES(1,'hello')}
    db1 eval {INSERT INTO t1 VALUES(2,'goodbye')}
    db1 eval {INSERT INTO t1 VALUES(3,'howdy!')}
    set x [db1 eval {SELECT * FROM t1 ORDER BY a}] 

The variable $x is set by the above code to

    1 hello 2 goodbye 3 howdy! 

You can also process the results of a query one row at a time by specifying the name of an array variable and a script following the SQL code. For each row of the query result, the values of all columns will be inserted into the array variable and the script will be executed. For instance:

    db1 eval {SELECT * FROM t1 ORDER BY a} values {
        parray values
        puts ""
    } 

This last code will give the following output:

    values(*) = a b
    values(a) = 1
    values(b) = hello
    values(*) = a b
    values(a) = 2
    values(b) = goodbye
    values(*) = a b
    values(a) = 3
    values(b) = howdy! 

For each column in a row of the result, the name of that column is used as an index into the array. The value of the column is stored in the corresponding array entry. The special array index * is used to store a list of column names in the order that they appear.

If the array variable name is omitted or is the empty string, then the value of each column is stored in a variable with the same name as the column itself. For example:

    db1 eval {SELECT * FROM t1 ORDER BY a} {
        puts "a=$a b=$b"
    } 

From this we get the following output

    a=1 b=hello
    a=2 b=goodbye
    a=3 b=howdy! 

Tcl variable names can appear in the SQL statement of the second argument in any position where it is legal to put a string or number literal. The value of the variable is substituted for the variable name. If the variable does not exist a NULL values is used. For example:

    db1 eval {INSERT INTO t1 VALUES(5,$bigblob)} 

Note that it is not necessary to quote the $bigblob value. That happens automatically. If $bigblob is a large string or binary object, this technique is not only easier to write, it is also much more efficient since it avoids making a copy of the content of $bigblob.

dbcmd transaction ?transation-type? script

This method is used to execute a TCL script inside an SQLite database transaction. The transaction is committed when the script completes, or it rolls back if the script fails. If the transaction occurs within another transaction (even one that is started manually using BEGIN) it is a no-op.

The transaction command can be used to group together several SQLite commands in a safe way. You can always start transactions manually using BEGIN, of course. But if an error occurs so that the COMMIT or ROLLBACK are never run, then the database will remain locked indefinitely. Also, BEGIN does not nest, so you have to make sure no other transactions are active before starting a new one. The transaction method takes care of all of these details automatically.

The transaction-type can be one of deferred, exclusive or immediate. The default is deferred.

dbcmd complete sql

This method takes a string of supposed SQL as its only argument. It returns TRUE if the string is a complete statement of SQL and FALSE if there is more to be entered.

It is useful when building interactive applications in order to know when the user has finished entering a line of SQL code. This is really just an interface to the sqlite3_complete() C function. Refer to the C/C++ interface specification for additional information.

dbcmd copy conflict-algorithm table-name file-name ?column-separator? ?null-indicator?

This method copies data from a file into a table. It returns the number of rows processed successfully from the file.

Conflict-algorithm must be one of the SQLite conflict algorithms for the INSERT statement: rollback, abort, fail, ignore, or replace. See the SQLite Language section for ON CONFLICT for more information. The conflict-algorithm must be specified in lower case.

Table-name must already exists as a table. File-name must exist, and each row must contain the same number of columns as defined in the table. If a line in the file contains more or less than the number of columns defined, the copy method rollbacks any inserts, and returns an error.

Column-separator is an optional column separator string. The default is the ASCII tab character \t.

Null-indicator is an optional string that indicates a column value is null. The default is an empty string.

Note that column-separator and null-indicator are optional positional arguments; if null-indicator is specified, a column-separator argument must be specifed and precede the null-indicator argument.

The copy method implements similar functionality to the .import SQLite shell command. The SQLite 2.x COPY statement (using the PostgreSQL COPY file format) can be implemented with this method as:

    dbcmd copy $conflictalgo $tablename $filename \t    \\N 

dbcmd timeout delay

This method is used to control how long the SQLite library will wait for locks to clear before giving up on a database transaction. The default timeout is 0 milliseconds. (In other words, the default behavior is not to wait at all.)

The SQLite database allows multiple simultaneous readers or a single writer but not both. If any process is writing to the database no other process is allows to read or write. If any process is reading the database other processes are allowed to read but not write. The entire database shared a single lock.

When SQLite tries to open a database and finds that it is locked, it can optionally delay for a short while and try to open the file again. This process repeats until the query times out and SQLite returns a failure. The timeout is adjustable. It is set to 0 by default so that if the database is locked, the SQL statement fails immediately. But you can use the timeout method to change the timeout value to a positive number. For example:

    db1 timeout 2000

The argument to the timeout method is the maximum number of milliseconds to wait for the lock to clear. So in the example above, the maximum delay would be 2 seconds.

dbcmd busy cmd

This method, like method timeout, only comes into play when the database is locked. But the busy method gives the programmer much more control over what action to take.

The users of this method specifies a callback Tcl procedure that is invoked whenever SQLite tries to open a locked database. This callback can do whatever is desired. Presumably, the callback will do some other useful work for a short while (such as service GUI events) then return so that the lock can be tried again. The callback procedure should return 0 if it wants SQLite to try again to open the database and should return 1 if it wants SQLite to abandon the current operation.

dbcmd last_insert_rowid

This method returns an integer which is the ROWID of the most recently inserted database row.

dbcmd function fun-name cmdprefix

This method registers new SQL functions with the SQLite engine. The arguments are the name of the new SQL function and a TCL command that implements that function. Arguments to the function are appended to the TCL command before it is invoked.

The following example creates a new SQL function named "hex" that converts its numeric argument in to a hexadecimal encoded string:

    db function hex {format 0x%X} 

dbcmd nullvalue nullrep

This method changes the representation for NULL returned as result of the eval method.

    db1 nullvalue NULL 

It is useful to distinguish between NULL and empty column values as Tcl lacks a NULL representation. The default representation for NULL values is an empty string.

dbcmd onecolumn sql

This method works like eval in that it evaluates the SQL query statement given as its argument. The difference is that onecolumn returns a single element which is the first column of the first row of the query result.

This is a convenience method. It saves the user from having to do a "[lindex ... 0]" on the results of an eval in order to extract a single column result.

dbcmd changes

This method returns an integer which is the number of rows in the database that were inserted, deleted, and/or modified by the most recent eval method.

dbcmd total_changes

This method returns an integer which is the number of rows in the database that were inserted, deleted, and/or modified since the current database connection was first opened.

dbcmd authorizer ?cmd?

This method provides access to the sqlite3_set_authorizer C/C++ interface. The argument to authorizer is the name of a procedure that is called when SQL statements are being compiled in order to authorize certain operations. The callback procedure takes 5 arguments which describe the operation being coded. If the callback returns the text string SQLITE_OK, then the operation is allowed. If it returns SQLITE_IGNORE, then the operation is silently disabled. If the return is SQLITE_DENY then the compilation fails with an error.

If the argument is an empty string then the authorizer is disabled. If the argument is omitted, then the current authorizer is returned.

dbcmd progress n cmd

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, and the TCL command 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.

dbcmd collate seq-name cmd

This method registers new text collating sequences. There are two arguments: the name of the collating sequence and the name of a TCL procedure that implements a comparison function for the collating sequence.

For example, the following code implements a collating sequence called "NOCASE" that sorts in text order without regard to case:

    proc nocase_compare {a b} {
        return [string compare [string tolower $a] [string tolower $b]]
    }
    db collate NOCASE nocase_compare

dbcmd collation_needed cmd

This method registers a callback routine that is invoked when the SQLite engine needs a particular collating sequence but does not have that collating sequence registered. The callback can register the collating sequence. The callback is invoked with a single parameter which is the name of the needed collating sequence.

dbcmd commit_hook cmd

This method registers a callback routine that is invoked just before SQLite tries to commit changes to a database. If the callback throws an exception or returns a non-zero result, then the transaction rolls back rather than commit.

dbcmd errorcode

This method returns the numeric error code that resulted from the most recent SQLite operation.

dbcmd trace cmdprefix

This method registers a callback that is invoked as each SQL statement is compiled. The text of the SQL is appended as a single string to the command before it is invoked. This can be used (for example) to keep a log of all SQL operations that an application performs.

dbcmd backup ?source-database? backup-filename

This method makes a backup copy of a live database.

The optional source-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 auxiliary database added to the connection using the SQL ATTACH command, use the name of that database as it was assigned in the ATTACH command.

The backup-filename is the name of a file into which the backup is written. The file does not have to exist ahead of time, but if it does, it must be a well-formed SQLite database.

dbcmd restore ?target-database? source-filename

This method copies the content a separate database file into the current database connection, overwriting any preexisting content.

The optional target-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 auxiliary database added to the connection using the SQL ATTACH command, use the name of that database as it was assigned in the ATTACH command.

The source-filename is the name of a existing well-formed SQLite database file from which the content is extracted.

Keywords

data management, database, persistent storage, relational database, sql, table, transaction, trigger, view