From 2f8d7092bc2c9609fa98d6888106b96f38b22828 Mon Sep 17 00:00:00 2001 From: dan miller Date: Sun, 21 Oct 2007 08:36:32 +0000 Subject: libraries moved to opensim-libs, a new repository --- .../sqlite/unix/sqlite-3.5.1/www/tclsqlite.tcl | 666 --------------------- 1 file changed, 666 deletions(-) delete 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 deleted file mode 100644 index 141cb5e..0000000 --- a/libraries/sqlite/unix/sqlite-3.5.1/www/tclsqlite.tcl +++ /dev/null @@ -1,666 +0,0 @@ -# -# 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