• authorizer
• backup
• busy
• cache
• changes
• close
• collate
• collation_needed
• commit_hook
• complete
• copy

• enable_load_extension
• errorcode
• eval
• exists
• function
• incrblob
• last_insert_rowid
• nullvalue
• onecolumn
• profile
• progress

• restore
• rollback_hook
• status
• timeout
• total_changes
• trace
• transaction
• unlock_notify
• update_hook
• version
• wal_hook

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

The "eval" method

The most usefuldbcmdmethod 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 namedt1with columnsaandb. What could be simpler?

Query results are returned as a list of columnvalues. 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 t1VALUES(1,'hello')}
db1 eval {INSERT INTO t1VALUES(2,'goodbye')}
db1 eval {INSERT INTO t1VALUES(3,'howdy!')}
set x [db1 eval {SELECT * FROM t1 ORDER BY a}]

The variable$xis 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,thevaluesof 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{
parrayvalues
puts""
}

This last code will give the following output:

values(*) = a b
values(a) = 1
values(b) = hello

values(a) = 2
values(b) = goodbye

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 and the value of the column is stored in the corresponding array entry. (Caution: If two or more columns in the result set of a query have the same name,then the last column with that name will overwrite priorvaluesand earlier columns with the same name will be inaccessible.) 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 NULLvaluesis used. For example:

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,use a "@" character to in place of the "$". Like this:

If the variable does not have a bytearray representation,then "@" works just like "$". Note that ":" works like "$" in all cases so the following is another way to express the same statement:

The use of ":" instead of "$" before the name of a variable can sometimes be useful if the SQL text isenclosedin double-quotes "..." instead of curly-braces {...}. When the SQL is contained within double-quotes "..." then TCL will do the substitution of $-variables,which can lead to SQL injection if extreme care is not used. But TCL will never substitute a :-variable regardless of whether double-quotes "..." or curly-braces {...} are used to enclose the SQL,so the use of :-variables adds an extra measure of defense against SQL injection.

The "close" method

As its name suggests,the "close" method to an SQLite database just closes the database. This has the side-effect of deleting thedbcmdTcl command. Here is an example ofopening and then immediately closing a database:

testdb
db1 close

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

testdb
rename db1 {}

The "transaction" method

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

Thetransactioncommand can be used to group together several SQLite commands in a safe way. You can always starttransactions 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 othertransactions 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

Thetransaction-typecan be one ofdeferred,exclusiveorimmediate. The default is deferred.

The "cache" method

The "eval" method describedabovekeeps a cache ofprepared statementsfor 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 methodfinalizesall prepared statements currently in the cache.

The "complete" method

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 thesqlite3_complete()C function.

The "copy" method

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-algorithmtable-namefile-name? column-separator? ? null-indicator?

Conflict-algorithm must be one of the SQLite conflict algorithms for the INSERT statement:rollback,abort,fail,ignore,orreplace. See the SQLite Language section forON CONFLICTfor 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 specified and precede the null-indicator argument.

The copy method implements similar functionality to the.importSQLite shell command.The "timeout" method

The "timeout" method is used to control how long the SQLite library will wait for locks to clear before giving up on a databasetransaction. 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 toopena database and finds that it is locked,it can optionally delay for a short while and try toopenthe 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.

The "busy" method

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 toopena 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 toopenthe database and should return "1" if it wants SQLite to abandon the current operation.The "enable_load_extension" method

The extension loading mechanism of SQLite (accessed using theload_extension()SQL function) is turned off by default. This is a security precaution. If an application wants to make use of theload_extension()function it must first turn the capability on using this method.

This method takes a single boolean argument which will turn the extension loading functionality on or off.

This method maps to thesqlite3_enable_load_extension()C/C++ interface.

The "exists" method

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 existence 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
}

The "last_insert_rowid" method

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

The "function" method

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}

The "function" method accepts the following options:

-argcount INTEGER

Specify the number of arguments that the SQL function accepts. The default value of -1 means any number of arguments.

-deterministic

This option indicates that the function will always return the same answer given the same argumentvalues. The SQLite query optimizer uses this information to cache answers from function calls with constant inputsand reuse the result rather than invoke the function repeatedly.

The "nullvalue" method

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 columnvaluesas Tcl lacks a NULL representation. The default representation for NULLvaluesis an empty string.

The "onecolumn" method

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.

The "changes" method

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.

The "total_changes" method

The "total_changes" method returns an integer which is the number of rows in the database that were inserted,and/or modified since the current database connection was firstopened.

The "authorizer" method

The "authorizer" method provides access to thesqlite3_set_authorizerC/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.

The "progress" method

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.

The "collate" method

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

The "collation_needed" method

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.

The "commit_hook" method

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 thetransactionrolls back rather than commit.

The "rollback_hook" method

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

The "status" method

This method returns status information from the most recently evaluated SQL statement. The status method takes a single argument which should be either "steps" or "sorts". If the argument is "steps",then the method returns the number of full table scan steps that the previous SQL statement evaluated. If the argument is "sorts",the method returns the number of sort operations. This information can be used to detect queries that are not using indices to speed search or sorting.

The status method is basically a wrapper on thesqlite3_stmt_status()C-language interface.

The "update_hook" method

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:

• The keyword "INSERT","UPDATE",or "DELETE",as appropriate
• The name of the database which is being changed
• The table that is being changed
• The rowid of the row in the table being changed

The "wal_hook" method

This method registers a callback routine that is invoked aftertransactioncommit when the database is inWAL mode. Two arguments are appended to the callback command before it is invoked:

• The name of the database on which thetransactionwas committed
• The number of entries in the write-ahead log (WAL) file for that database

This method might decide to run acheckpointeither itself or as a subsequent idle callback. Note that SQLite only allows a single WAL hook. By default this single WAL hook is used for the auto-checkpointing. If you set up an explicit WAL hook,then that one WAL hook must ensure that checkpoints are occurring since the auto-checkpointing mechanism will be disabled.

The "incrblob" method

This methodopens 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?TABLECOLUMNROWID

The command returns a new TCL channel for reading or writing to the BLOB. The channel isopened using the underlyingsqlite3_blob_open()C-language interface. Close the channel using theclosecommand of TCL.

The "errorcode" method

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

The "trace" method

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.

The "backup" method

The "backup" method makes a backup copy of a live database. The command syntax is like this:

dbcmd backup? source-database? backup-filename

The optionalsource-databaseargument tells which database in the current connection should be backed up. The default value ismain(or,in other words,the primary database file). To back up TEMP tables usetemp. To backup an auxiliary database added to the connection using theATTACHcommand,use the name of that database as it was assigned in theATTACHcommand.

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

The "restore" method

The "restore" method copies the content from a separate database file into the current database connection,overwriting any preexisting content. The command syntax is like this:

dbcmd restore? target-database? source-filename

The optionaltarget-databaseargument tells which database in the current connection should be overwritten with new content. The default value ismain(or,the primary database file). To repopulate the TEMP tables usetemp. To overwrite an auxiliary database added to the connection using theThesource-filenameis the name of an existing well-formed SQLite database file from which the content is extracted.

The "version" method Return the current library version. For example,"3.6.17". The "profile" method

This method is used to profile the execution of SQL statements run by the application. The syntax is as follows:

dbcmd profile? script?

Unlessscriptis an empty string,this method arranges for thescriptto be evaluated after the execution of each SQL statement. Two arguments are appended toscriptbefore it is invoked: the text of the SQL statement executed and the time elapsed while executing the statement,in nanoseconds.

A database handle may only have a single profile script registered at any time. If there is already a script registered when the profile method is invoked,the previous profile script is replaced by the new one. If thescriptargument is an empty string,any previously registered profile callback is canceled but no new profile script is registered.

参考:

2016/2/15


1) Title: The Tcl interface to the SQLite library
URL: https://www.sqlite.org/tclsqlite.html


2) Title: SQLite In 5 Minutes Or Less
URL: https://www.sqlite.org/quickstart.html


3) Title: SQLite and Tcl
URL: http://nut.sourceforge.net/drh.html

2016/2/15 1) Title: Tcl Data Types URL: http://www.tutorialspoint.com/tcl-tk/tcl_data_types.htm

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!