From e36d23a85ebff914d74bb541558c2b6082b78edb Mon Sep 17 00:00:00 2001 From: dan miller Date: Sat, 20 Oct 2007 02:49:29 +0000 Subject: sqlite source (unix build) added to libraries --- .../sqlite/unix/sqlite-3.5.1/www/tclsqlite.tcl | 666 +++++++++++++++++++++ 1 file changed, 666 insertions(+) create mode 100644 libraries/sqlite/unix/sqlite-3.5.1/www/tclsqlite.tcl (limited to 'libraries/sqlite/unix/sqlite-3.5.1/www/tclsqlite.tcl') diff --git a/libraries/sqlite/unix/sqlite-3.5.1/www/tclsqlite.tcl b/libraries/sqlite/unix/sqlite-3.5.1/www/tclsqlite.tcl new file mode 100644 index 0000000..141cb5e --- /dev/null +++ b/libraries/sqlite/unix/sqlite-3.5.1/www/tclsqlite.tcl @@ -0,0 +1,666 @@ +# +# Run this Tcl script to generate the tclsqlite.html file. +# +set rcsid {$Id: tclsqlite.tcl,v 1.17 2007/06/19 17:48:57 drh Exp $} +source common.tcl +header {The Tcl interface to the SQLite library} +proc METHOD {name text} { + puts "\n

The \"$name\" method

\n" + puts $text +} +puts { +

The Tcl interface to the SQLite library

+ +

The SQLite library is designed to be very easy to use from +a Tcl or Tcl/Tk script. This document gives an overview of the Tcl +programming interface.

+ +

The API

+ +

The interface to the SQLite library consists of single +tcl command named sqlite3 +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. If the name of the database is an empty +string or the special name ":memory:" then a new database is created +in memory. +

+ +

+Once an SQLite database is open, it can be controlled using +methods of the dbcmd. There are currently 22 methods +defined.

+ +

+

 +

+ +

The use of each of these methods will be explained in the sequel, though +not in the order shown above.

+ +} + +############################################################################## +METHOD eval { +

+The most useful dbcmd method is "eval". The eval method is used +to execute SQL on the database. The syntax of the eval method looks +like this:

+ +
+dbcmd  eval  sql +    ?array-name ? ?script? +
+ +

+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 in to 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,$bigstring)} +
+ +

+Note that it is not necessary to quote the $bigstring value. That happens +automatically. If $bigstring 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 $bigstring. +

+ +

+If the $bigstring variable has both a string and a "bytearray" representation, +then TCL inserts the value as a string. If it has only a "bytearray" +representation, then the value is inserted as a BLOB. To force a +value to be inserted as a BLOB even if it also has a text representation, +us a "@" character to in place of the "$". Like this: +

+ +
+db1 eval {INSERT INTO t1 VALUES(5,@bigstring)} +
+ +

+If the variable does not have a bytearray representation, then "@" works +just like "$". +

+ +} + +############################################################################## +METHOD close { + +

+As its name suggests, the "close" method to an SQLite database 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 {} +
+} + +############################################################################## +METHOD transaction { + +

+The "transaction" 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 syntax looks like this: +

+ +
+dbcmd  transaction  ?transaction-type? +  SCRIPT, +
+ + +

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

+} + +############################################################################## +METHOD cache { + +

+The "eval" method described above keeps a cache of +prepared statements +for recently evaluated SQL commands. +The "cache" method is used to control this cache. +The first form of this command is:

+ +
+dbcmd  cache size  N +
+ +

This sets the maximum number of statements that can be cached. +The upper limit is 100. The default is 10. If you set the cache size +to 0, no caching is done.

+ +

The second form of the command is this:

+ + +
+dbcmd  cache flush +
+ +

The cache-flush method +finalizes +all prepared statements currently +in the cache.

+ +} + +############################################################################## +METHOD complete { + +

+The "complete" 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.

+ +

The "complete" method 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. +} + +############################################################################## +METHOD copy { + +

+The "copy" method copies data from a file into a table. +It returns the number of rows processed successfully from the file. +The syntax of the copy method looks like this:

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

Conflict-alogrithm 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 +
+ +} + +############################################################################## +METHOD timeout { + +

The "timeout" 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 millisecond. (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.

+} + +############################################################################## +METHOD busy { + +

The "busy" method, like "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 "busy" 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. +} + +############################################################################## +METHOD exists { + +

The "exists" method is similar to "onecolumn" and "eval" in that +it executes SQL statements. The difference is that the "exists" method +always returns a boolean value which is TRUE if a query in the SQL +statement it executes returns one or more rows and FALSE if the SQL +returns an empty set.

+ +

The "exists" method is often used to test for the existance of +rows in a table. For example:

+ +
+if {[db exists {SELECT 1 FROM table1 WHERE user=$user}]} {
+   # Processing if $user exists
+} else {
+   # Processing if $user does not exist
+} +
+} + + +############################################################################## +METHOD last_insert_rowid { + +

The "last_insert_rowid" method returns an integer which is the ROWID +of the most recently inserted database row.

+} + +############################################################################## +METHOD function { + +

The "function" 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} +
+ +} + +############################################################################## +METHOD nullvalue { + +

+The "nullvalue" method changes the representation for NULL returned +as result of the "eval" method.

+ +
+db1 nullvalue NULL +
+ +

The "nullvalue" method is useful to differ between NULL and empty +column values as Tcl lacks a NULL representation. The default +representation for NULL values is an empty string.

+} + + + +############################################################################## +METHOD onecolumn { + +

The "onecolumn" 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.

+} + +############################################################################## +METHOD changes { + +

The "changes" 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.

+} + +############################################################################## +METHOD total_changes { + +

The "total_changes" 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.

+} + +############################################################################## +METHOD authorizer { + +

The "authorizer" 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.

+} + +############################################################################## +METHOD progress { + +

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.

+} + + +############################################################################## +METHOD collate { + +

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
+
+} + +############################################################################## +METHOD collation_needed { + +

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.

+} + +############################################################################## +METHOD commit_hook { + +

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.

+} + +############################################################################## +METHOD rollback_hook { + +

This method registers a callback routine that is invoked just before +SQLite tries to do a rollback. The script argument is run without change.

+} + +############################################################################## +METHOD update_hook { + +

This method registers a callback routine that is invoked just before +each row is modified by an UPDATE, INSERT, or DELETE statement. Four +arguments are appended to the callback before it is invoked:

+ + +} + +############################################################################## +METHOD incrblob { + +

This method opens a TCL channel that can be used to read or write +into a preexisting BLOB in the database. The syntax is like this:

+ +
+dbcmd  incrblob  ?-readonly?? +  ?DB?  TABLE  COLUMN  ROWID +
+ +

+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. +

+} + +############################################################################## +METHOD errorcode { + +

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

+} + +############################################################################## +METHOD trace { + +

The "trace" 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. +

+} + + +footer $rcsid -- cgit v1.1