'''[http://www.sqlite.org/%|%SQLite]''' is a software library that implements a self-contained, serverless, zero-configuration, transactional [SQL] [database] engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain. ** Attributes ** current version: 3.8.8.2 release time: 2015-01-30 contact: [D. Richard Hipp] ** Resources ** [http://www.sqlite.org/src/wiki?name=Bug+Reports%|%Report A Bug]: ** Documentation ** [http://www.sqlite.org/faq.html%|%FAQ]: [http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html%|%SQLite Optimization FAQ]: [http://www.tcl.tk/community/tcl2004/Papers/D.RichardHipp/drh.html%|%SQLite and Tcl], by [D. Richard Hipp]: [http://www.ddj.com/cpp/184401773%|%The SQLite Database Engine], Michael Owens, 2004-03: [http://blog.cleverly.com/permalinks/247.html%|%Summary of 2006 SQLite Keynote], [Michael Cleverly], [Thirteenth Annual Tcl/Tk Conference], 2006-10-11: ** Press ** [http://opensource.sys-con.com/read/259652.htm%|%PalmSource Releases ALP Component To Open Source Community], 20016-08-14: discusses libsqlfs, an add-on to SQLite created as part of the ACCESS Linux Platform (ALP) [http://www.theguardian.com/technology/2007/jun/21/it.guardianweeklytechnologysection%|%Size isn't everything for the modest creator of SQLite], Tim Anderson, 2007-06-20: ** Books ** ''[http://www.unixreview.com/documents/s=10089/ur0606g/ur0606g.htm%|%The Definitive Guide to SQLite]'', by Mike Owens, Apress, 2006, ISBN 1-59059-673-0, 464 pages ''[http://www.amazon.com/SQLite-Developer-Library-Chris-Newman/dp/067232685X/%|%SQLite (Developer's Library)]'', By Chris Newman ''[http://www.cbook24.com/bm_detail.asp?sku=4798109436%|%SQLite??]'', by Naoki Nishizawa ** Tcl Bindings ** SQLite was designed from the beginning to be used with Tcl. Tcl bindings have been in the SQLite core since before version 1.0 and almost half of the SQLite source code base consists of regression test scripts written in Tcl. SQLite wants to be programmed in Tcl. [TDBC]: provides a SQLite driver, included in standard Tcl distributions since version 8.6 [SQL Relay]: ** Interactive Interfaces ** [console sqlite manager]: [SQLiteStudio]: [TkSQLite]: [sqlitetablelist]: designer/viewer and editor for SQLite3 databases ** Tools ** [An SQL database backed VFS]: A [tclvfs] for SQLite: [GEB], by [GCS%|%Gerry Snyder]: A general-purpose SQLite [GUI] and database manipulation tool. It includes facilities for storing, examining, searching, editing, and executing text stored in the `tclcode` table of the main and any attached DB file. [Object-oriented SQLite wrapper]: [TkSQLite]: A [GUI] database manager for [SQLite] ===none What: '''sqliteodbc''' Where: http://www.ch-werner.de/sqliteodbc/ Description: [ODBC] driver for SQLite. Currently at version 0.66. Updated: 05/2006 Contact: See web site === ===none What: '''[nsdbilite]''' Description: Native SQLite driver for the [nsdbi] database interface. Updated: 06/2008 === ===none What: '''javasqlite''' Where: http://www.ch-werner.de/javasqlite/ Description: [Java] [JDBC] wrapper for sqlite. Currently at version 20050608. Updated: 05/2006 Contact: See web site === ===none What: '''wisql''' Where: http://www.ucolick.org/%7Ede/ http://www.ucolick.org/cgi-bin/Tcl/wisql.cgi ftp://ftp.ucolick.org/pub/UCODB/wisql5.1.tar.gz Description: UCO wisql is an "upscale" version of Tom Poindexter's wisqlite according to the WWW page above. Requires Tcl/Tk/[tclX]/[sybtcl] and [tkbind]. Many other Sybase related tools are found at the above site include a forms GUI interface to Sybase called fosql. Updated: 02/2000 Contact: mailto:de@ucolick.org (De Clarke) === [Acacio Cruz] Compiled 3.3.13 Tcl-bindings for Mac OS X 10.4 PowerPC. Available here: http://www.acacio.com/tcl/libsqlite3.3.13-macosx10.4-PPC.tar.gz Latest build: macosx 10.5, Intel: http://www.acacio.com/tcl/tclsqlite3.6.7-macosx10.5-tcl8.6-x86.tgz tclsqlite 3.0.8 extension library for Linux on an iPaq or Zaurus: http://www.sr-tech.com/testing/libtclsqlite3.so There is a complete sqlite extension for [Jim] with a nice command based interface. See [Jim Extensions] for more information. [Tclkit Kitgen Build System] contain sqlite 3.5.9 and tksqlite 0.5.6 as library and starpack. https://github.com/otoolep/rqlite%|%rqlite%|%: Replicating SQLite using Raft Consensus ([http://www.philipotoole.com/replicating-sqlite-using-raft-consensus/%|%blog post] - [https://news.ycombinator.com/item?id=9092110%|%discussion]) ** Users ** [http://mozilla.org%|%Firefox] 3.0: uses SQLite for storage of browsing history, bookmarks, cookies, favicons etc. [TWS]: uses SQLite as database engine ** See Also ** [datatype impedance mismatch between tcl and SQLite]: [Ratcl-like API with SQLite backend]: [Built-in SQLite rdbms]: [http://blog.michaeltrier.com/2008/7/13/full-text-search-on-sqlite%|%"Full-Text Search on SQLite"] ''(Broken Link 2011-11-17)'', unsure of original link but [http://www.sqlite.org/fts3.html%|%SQLite FTS3 and FTS4 Extensions] is the SQLite docs on FTS: [SQLite introspection]: [SQLite Optimization]: tips for getting more performance out of SQLite [sqlite_master]: The table that holds the database schema in SQLite. ** Description ** An [SQL] database (written in [C] but with [Tcl] [extension]/bindings as part of the source tree and with Tcl used for most testing). Author is [D. Richard Hipp]. Features include: * Atomic, Consistent, Isolated, and Durable [ACID] transactions * Zero-configuration - there is no setup or administration needed * Simple usage from Tcl * A complete database is stored as a single cross-platform disk file * Supports huge databases (tebibytes) and huge strings and blobs (gibibytes) * Small footprint ("lean", not "crippled") Supports a large subset of SQL92. Comes with bindings for Tcl/Tk. The Tcl binding is stubs-enabled so it can be wrapped in a starkit if need be. SQLite is not intended to be a replacement for client/server enterprise database engines like [Oracle], [MySQL], or [PostgreSQL]. SQLite is intended to replace the use of [open] to write and create flat files in proprietary formats used to store miscellaneous information. SQLite is especially useful in Tcl/Tk programs as an application file format. When the user selects the File->Open menu option, the application can connect to an SQLite database instead of reading and parsing a file. This makes the file format cross-platform, gives the application ACID I/O semantics and rollback capabilities, and provides a powerful query mechanism for accessing and updating application data. The [C]-code portion of SQLite is used extensively in commercial products and is likely the single most widely deployed SQL database engine. Despite the fact that most people use only the C-code portions of SQLite, the author has stated that he considers SQLite to be a Tcl extension, not a C library that happens to have Tcl bindings. The author points out the well over half of SQLite (specifically all of the test scripts) are written in Tcl and that SQLite would never have been possible without Tcl. [http://www.sqlite.org/version3.html%|%Version 3.0] provides a lot of important new and improved functionality, including user-defined collating, limited table-level locking, 64-bit rowids, "manifest typing", proper UTF-{8,16} support, new concurrency possibilities, and SQLite-side dereferencing of Tcl variables (see below). ** Differences from Other Databases ** SQLite has [http://www.sqlite.org/omitted.html%|%omitted some features] that a typical database implements By default, SQLite doesn't constrain referential integrity. It parses foreign keys, it builds an internal data structure to describe them--- but normally stops there. An equivalent constraint enforcement can be achieved using triggers. CHECK constraints are supported as of version 3.3.0 (the pragma 'PRAGMA foreign_key_list(table-name)' provides introspection on the foreign keys). With SQLite 3.6.19 and up, FOREIGN KEY constraint enforcement can be enabled via a [http://www.sqlite.org/foreignkeys.html#fk_enable%|%PRAGMA]. SQLite uses manifest typing. This means, the datatype is a property of the value, not of the column. SQLite allows to store any value of any datatype into any column regardless of the declared type of that column. SQLite attempts to coerce values into the declared datatype of the column when it can. (Exception: INTEGER PRIMARY KEY column may only store integers). ** Configuration under Windows and MacOS X ** Configuration under [Windows] and [MacOS] is only a bit cumbersome, if you compile yourself. The FAQ advises for the former: "Create a subdirectory named tclsqlite into the lib directory of your tcl installation (usually C:\tcl\lib) and put there the tclsqlite.dll library. Then, put the line: ====== load tclsqlite ====== at the beginning of your code, and you can then use the sqlite command in your tcl script." Another way: Get a copy of tclsqlite3.dll, save it to e.g. `\winnt\system32\` and then, in Tcl, do: ====== load tclsqlite3.dll Sqlite3 ====== Anther variation is to to put tclsqlite.dll in `c:\tcl\lib\sqlite` and create a `pkgIndex.tcl` file that says something like: ====== package ifneeded sqlite 2.0 [list load [file join $dir \ tclsqlite[info sharedlibextension]]] ====== Then, `package require sqlite` in the script. Or, for SQLite 3: ====== package ifneeded sqlite3 3.0 [list load [file join $dir \ tclsqlite3[info sharedlibextension]] tclsqlite3] ====== Then, `package require sqlite'''3'''` in the script. There is also a simpler way. The tclsqlite.dll file can be in a directory totally unrelated to Tcl. The directory only needs to be in the '''$PATH'''. Then just '''load tclsqlite.dll''' and it works ([LES]). ** Usage examples ** The best place to start is [http://www.sqlite.org/tclsqlite.html%|%The Tcl interface to the SQLite library]. SQLite partakes of the Tcl spirit in that it's easy to learn by starting it up and asking it about itself. If someone presents you with a database image '''`unknown.db`''', for example, you can begin to introspect: ======none # sqlite unknown.db ... sqlite> .help ... sqlite> .tables ... sqlite> .schema sqlite_master ... sqlite> select * from sqlite_master; ... ====== and so on. Notice that `sqlite_master` here is a keyword that names the table that holds the schema for `unknown.db`. [kostix]: SQLite creates its command for the opened database in the global namespace, i.e.: ======none % namespace eval ::foo { sqlite3 db mydbfile } % info comm ::foo::* % % info comm ::db* % ::db o you must fully qualify the first argument to '''sqlite''' command if you want the database handling command to be created in a different namespace, like this: % namespace eval ::foo { sqlite3 [namespace current]::db mydbfile } % info comm ::foo::* % ::foo::db ====== ( Side note: the hexadecimal number that returns from the '''sqlite3''' command is the value of the '''sqlite3''' pointer. It is used for testing and will likely be replaced by something less kludgy in the future. So don't use it.) ** Tcl variable handling ** *** Dereferencing *** SQLite 3.0 introduced the capability of sqlite-side dereferencing of Tcl variables. This means that ====== db eval {INSERT INTO table1 VALUES($name,$vx)} ====== will be interpreted sensibly, and, moreover, is both faster and more correct than ====== #warning! exploitable code ahead! db eval "INSERT INTO table1 VALUES($name,$vx)" ====== or its variants in the cases where the latter embeds SQL-privileged characters such as `'`. Note, that dereferencing of array variables does only work partly: ====== set item one set data(one) {a number} db eval {insert into table1 values ($data($item))} ====== will not do what you expect, but ====== db eval {insert into table1 values ($data(one))} ====== will do the right thing. So, you should never use variables as keys in arrays, when SQLite is supposed to dereference the variable. The dereferencing only works at specific places. The following is wrong: ====== set table project set field projectName db eval {SELECT * FROM $table WHERE $field LIKE $searchterm) ====== A variable is not allowed as the table name. Here's one way to accomplish varying the table name: ====== db eval [format {SELECT * FROM %s WHERE %s LIKE $searchterm} $table $field] ====== Which would expand to: ====== db eval {SELECT * FROM project WHERE projectName LIKE 'some search term'} ====== Of course, you can also just use double quotes and backslashes where appropriate: ====== db eval "SELECT * FROM $table WHERE $field LIKE \$searchterm" ====== [MJ]: If you create SQL queries like this, you better be very sure that `$searchterm` is under your control, because otherwise you are asking for SQL [injection attacks%|%injection attacks]. [DKF]: It's actually easier to use the other variable dereference format (with a colon, which is ?Standard SQL? AIUI) where you can in situations like this. For example: ====== db eval "INSERT INTO $table (a,b,c) VALUES (:tclVarA, :tclVarB, :tclVarC)" ====== If you're doing this, take care to put a space after the table name if you want to use a parenthesis straight afterwards (or use `${...}`. Otherwise Tcl will assume you're doing an array dereference; probably not what you want. *** Local variables *** Sqlite creates local variables when a statement is issued in the form: ====== db eval {select id from table1} {puts $id} ====== Here, the variable $id is automatically created by sqlite. This is always done, when you specify a script argument. This is very handy! But you must be carefull when doing queries like: ====== db eval {select * from table1} {} ====== because you get a bunch of predefined variables matching the resulting columns without ever really specifying them. If the table has the columns ''a,b,c'' you get variables with the same names and these may interfere with your own variables... If you want to retain control over what variables exist in the calling context, then supply the name of an array variable instead: ====== array unset name_of_array ;#Make sure array doesn't have extra column names from a previous resultset! db eval {select * from table1} name_of_array {parray name_of_array} ====== So, instead of a variable per column, you get an additional array element for each column (plus the extra element '*' which contains the column names). Try this particular example only on a small table! [parray] is called for each row. A statement like the following will not work, however: ====== db eval {select s.name, f.parent, f.name from files f, shares s where s.name = f.share ... } { puts ${s.name} } ====== The problem here is that both "shares" and "files" tables contain column named "name" so the disambiguation table prefixes were necessary. But Tcl variable binding mechanism of SQLite doesn't take those prefixes into account, so in the case above you'll end up with '''two''' variables "name" and one variable "parent". This behaviour is consistent with "big" RDBMSs like [PostgreSQL] in that column names of the result set produced by any SQL statement are arbitrary. The way to resolve this problem is to use '''as''' clauses, like this: ====== db eval { select s.name as sname, f.parent as fparent, f.name as fname ... } ====== Thus, you will get variable names corresponding to the aliases: sname, fparent, and fname. ** SQLite functions ** [TR] [MJ]: Apart from the built-in functions in SQLite, you can make your own. SQLite functions can give you capabilities similar to for example Oracle's PL/SQL. So how difficult is it to add [regexp] support? Easy. Just do: ====== $dbhandle function regexp regexp ====== and then you have it! Usage like this: ====== $dbhandle eval {select column1,column2 from table where column2 regexp '^something\s.*'} ====== See [http://www.sqlite.org/lang_expr.html%|%SQL As Understood by SQLite] for reference. Note, that the Tcl command given with `$dbhandle function ...` can only be a single word, not a script with several statements. E.g., if you want to do something with the arguments before handing them over to the Tcl command, it must be done like this: ====== sqlite db {} # Tcl proc we want to use (assuming its implementation cannot be changed for whatever reason), it takes 3 single arguments: proc tclcalc {a b c} {expr {$a+2*$b + 3*$c}} # we need a wrapper in order to call it from sqlite: proc tclcalcwrapper {args} {tclcalc {*}$args} # now, the args get processed correctly for the db function to work: db function calc tclcalcwrapper # use the function: db eval "select calc(1,2,3)" ====== Here is another simple example, that adds a function to double a number: ====== package require sqlite3 sqlite3 db {} # create a Tcl procedure, that will get called when the db function is invoked proc double {num} { return [expr {$num*2}] } # register this procedure to the SQL engine db function double double # create a test table and fill it with some data db eval {create table test(i integer, i2 integer)} for {set i 0} {$i < 100} {incr i} { db eval {insert into test values($i,NULL)} } # show the records db eval {SELECT * from test} { puts "$i: $i2" } # update the values using the registered procedure # this is a contrived example, normally you would just use i2=2*i db eval {UPDATE test SET i2=double(i) WHERE i2 isnull} db eval {SELECT * from test} { puts "$i: $i2" } ====== ** Useful code snippets ** [US]: This recipe is for a '''hot backup''' of a sqlite database file (confirmed by [drh] on the sqlite mailing list) and translated to a short Tcl code snippet: ====== # Connect to the database sqlite3 db $dbfile # Lock the database, copy and commit or rollback if {[catch {db transaction immediate {file copy $dbfile ${dbfile}.bak}} res]} { puts "Backup failed: $res" } else { puts "Backup succeeded" } ====== [AMG]: How does this compare to `[http://www.sqlite.org/tclsqlite.html#backup%|%database backup]`? [Googie] 2011-10-25: I believe both ways realize pretty much the same thing. They both keep write-lock during operation, but other connections can read the database. The difference is when you do a backup of in-memory database (a dump of "temp" database to file), or backup of ATTACHed database - these are supported by `database backup`, but not by simple file copying. sisusimple 2011-10-26: The above backup code snippet was developed before the sqlite backup interface/api was developed. Both can still be used. The advantage of the backup interface is that the source db needs not to be locked for the complete time of the backup. See also [http://www.sqlite.org/backup.html%|%Using the SQLite Online Backup API]. ---- Here is a work-around to '''abort a sqlite-operation''': ====== sqlite3 db $dbFileName # process events during query db progress 100 {update} # this forces a "callback requested query abort"-error to be generated if button is pressed while query runs button .cancelBtn -text "cancel query" -command {db progress 100 {set notExistingVar 1}} pack .cancelBtn # start the query db eval $veryLongRunningQuery ====== If you catch the "callback ..."-error you can return an empty string or handle it in another way. Don't forget to call ====== db progress 100 {update} ====== once more, after aborting, otherwise all later queries will abort as well. The above is even easier using the new "interrupt" method to the SQLite database object. See http://www.sqlite.org/cvstrac/tktview?tn=1889 for additional information. [JOB]: '''Days between dates''' code snipped. How many days have been passed by, since ... ? Type the following: ====== SELECT julianday ( current_date ) - julianday( DATE('2011-09-29')); ====== Note: In sqlite is not necessary to specify "select ... from dual". ---- [AMG]: Here's a Tcl 8.6 [proc] to run and pretty-print a query with everything aligned nicely: ====== proc query {db query} { $db eval $query out { if {![info exists widths]} { lappend grid $out(*) set widths [lmap column $out(*) {string length $column}] } lappend grid [set row [lmap column $out(*) {set out($column)}]] set widths [lmap width $widths value $row {expr { max($width, [string length $value]) }}] } set format %-[join $widths "s %-"]s foreach row $grid { lappend display [format $format {*}$row] } join $display \n } ====== ** Discussions ** *** Tcl variables as ''value-list''s *** [AMG]: Feature request! I'd like to use lists stored in Tcl variables as SQL ''value-list''s. Of course there needs to be a way to differentiate between the Tcl variable expanding to a single value versus a list of values, so I will borrow [{*}], but by no means am I married to this particular notation! Here are some examples of what I'd like to do: ====== # Define data. db eval {create table rolodex (name, number)} set insert {{{Andy Goth} 222-333-5555} {{Chris Goth} 444-777-5555} {{Dick Goth} 999-888-5555}} set delete {{Andy Goth} {Chris Goth}} # Insert some rows. foreach entry $insert { db eval {insert into rolodex values({*}$entry)} } # Delete some rows. db eval {delete from rolodex where name in ({*}$delete)} ====== Using current SQLite, the above is coded: ====== # Insert some rows. foreach entry $insert { lassign $entry name number db eval {insert into rolodex values($name, $number)} } # Delete some rows. foreach name $delete { db eval {delete from rolodex where name = $name} } ====== which requires more local variables, more effort on Tcl's part, and more SQLite invocations. I'm not sure if it's necessary to support combinations like the following: ====== # More data. set delete2 {Keith Vetter} set delete3 {{Larry Virden} {Richard Suchenwirth}} # Delete lots of rows. (Pretend I inserted them earlier.) db eval {delete from rolodex where name in ({*}$delete, $delete2, {*}$delete3, "Jean-Claude Wippler")} ====== [AMG], update: 1. After thinking about it some more, I have decided that it's best ''not'' to support these arbitrary expansion combinations. This would needlessly bloat SQLite, as it is reasonable to use Tcl scripting to assemble the list. 2. If it's not legal to mix expansion-to-multiple-values with anything else, then it's needlessly* verbose to say '''`({*}$var)`'''. Instead just say '''`$var`''', ''without parentheses''. ''* Today's Wiki edit is brought to you by the word "needlessly" and the number [pi]!'' Here's an example showing (1) and (2) above: ====== set victims [list {*}$delete $delete2 {*}$delete3 {Jean-Claude Wippler}] db eval {delete from rolodex where name in $victims} ====== See, no parentheses. If the query had instead been written `{delete from rolodex where name in ($victims)}`, it likely would have had no effect, as it would be equivalent to `{delete from rolodex where name = $victims}`. I can only find two places where SQLite supports ''value-list''s: 1. The "IN" and "NOT IN" expressions. 2. The "VALUES" clause of "INSERT" and its alias "REPLACE". And both places I think would greatly benefit from the ability to expand a Tcl variable to an entire ''value-list'' rather than a single ''value''. [ZB] 2008-09-30: "After thinking about it some more" I'm pretty sure, that ability to use "value list" during INSERT would be very helpful. Currently one has to "manually" change appropriate place in script, when the amount of values has been changed (how can you dynamically resize "values($name, $number)" in your "Insert some rows" example?). So, it's not necessary to describe every new - really useful - feature as "bloat". [AMG]: It is bloat because it duplicates existing functionality. Tcl excels at list processing, so why not let Tcl do its job? I'm not saying that I have second thoughts about the whole feature request, only about the support for `[{*}]`. Instead, all I ask is for the ability to supply an entire value list from a single Tcl object, rather than being required to supply each element of the list from a separate Tcl object. The syntax I propose is to drop the parentheses when the entire value list is to be taken from a single Tcl object. [AMG]: I noticed that [Dossy] requested a feature very similar (possibly identical) to my ''value-list'' proposal. Search for dossy@panoptic.com on the [http://www.sqlite.org/cvstrac/wiki?p=UnsupportedSql%|%sqlite - Unsupported Sql] page. *** Performance benefit of explicit transactions *** [AMG]: Today I spent some time learning about how SQLite [http://www.sqlite.org/atomiccommit.html%|%guarantees atomicity] in the face of software, filesystem, and power failures. It occurred to me that it takes an awful lot of work to do it right. Next I realized that all this setup and tear-down work has to be done for each transaction, ''not'' for each statement. This explains why a simple database generation utility I wrote is so slow, even though it does nothing more than build a database from scratch to store precomputed data. (It's automatically generated [C] code, by the way.) It doesn't explicitly use transactions, so each statement is implicitly a separate transaction. After learning about the overhead involved in each transaction, I decided to add '''BEGIN''' and '''COMMIT''' to my utility. Here's the result: &| Database generation time without '''BEGIN'''/'''COMMIT''' | 7.347 seconds |& &| Database generation time with '''BEGIN'''/'''COMMIT''' | 0.070 seconds |& Yup, it's now 100 times faster! The moral of the story: '''Always use explicit transactions when executing more than one statement at a time''', even if your specific application would not benefit from atomicity. [Scott Beasley]: Transactions are a good rule for all multi-sql runs for most any dB, not just Sqlite. I bulk load 2-6million blobs a night and Transactions are the only way to get it done in a reasonable amount of time. Another performance helper is to make the journal file persistent, for long running insert/update jobs. *** Performance differences on various filesystems *** 2009-June-29: According to some tests (http://www.phoronix.com/scan.php?page=article&item=ext4_btrfs_nilfs2&num=2) there are shocking differences on various filesystems, which should be taken into consideration: [http://www.phoronix.com/data/img/results/ext4_btrfs_nilfs2/3.png] *** Precompiled tcl-sqlite *** [AMG]: Where are the precompiled binaries for tcl-sqlite? They used to be available on the download page [http://www.sqlite.org/download.html], but they're not there now. Anyway, I just compiled an x86 Windows binary of tcl-sqlite 3.7.4; it can be found here: [http://andy.junkdrome.org/devel/tcl/sqlite374.dll]. Here's how you [load] it: ====== load sqlite374.dll Sqlite3 ====== [DDG]: A starkit for version 3.7.4 containing builds for [Linux] (x86 and x86_64), [Micorosft Windows] (x86) and [Mac OS X%|%OS-X] Darwin (ppc and x86). Also additional math, string and aggregate functions are available: Math: acos, asin, atan, atn2, atan2, acosh, asinh, atanh, difference, degrees, radians, cos, sin, tan, cot, cosh, sinh, tanh, coth, exp, log, log10, power, sign, sqrt, square, ceil, floor, pi. String: replicate, charindex, leftstr, rightstr, ltrim, rtrim, trim, replace, reverse, proper, padl, padr, padc, strfilter. Aggregate: stdev, variance, mode, median, lower_quartile, upper_quartile. [https://bitbucket.org/mittelmark/tcl-code/downloads/sqlite-3.7.4.kit] I updated the starkit to version 3.7.10 and added as well a few more sqlite functions md5, md5_crypt, format (like tcl's format command), cflormat (clock format), uuid (from the tcllib package uuid). The download page is: https://bitbucket.org/mittelmark/tcl-code/downloads/ the starkit should run on all flavours of OSX- Linux and Win32 from tclkit 8.4, 8.5, 8.6. ---- A sample session on [Microsoft Windows%|%Windows]: ====== $ /c/ActiveTcl8.4.13/bin/tclkitsh856.exe % source sqlite-3.7.4.kit % package require sqlite3 3.7.4 % sqlite3 sql sample.db {} % sql eval "create table test (col1,col2)" % sql eval "insert into test (col1,col2) values (2,3)" % sql eval "insert into test (col1,col2) values (3,4)" % sql eval "insert into test (col1,col2) values (10,1)" % sql eval "select median(col1),stdev(col1),sin(col2),upper_quartile(col2) from test" 3 4.358898943540674 0.8414709848078965 4 % exit ====== *** Column names for an empty table *** [AMG]: I can get the names of the columns using the `*` element of the array produced by `$db eval`, but what do I do when the table is empty? [Googie]: You can use following before evaluating actual select from table: ====== db eval {PRAGMA table_info('table_name');} row { # Here use $row(name) as column name } ====== [AMG]: Thanks, that works great! I was sure that if anyone knew the answer, it would be you. ;^) Now, it would be Really Cool to have an constraint_info() that can also tell us all the constraints on a table, although this could get really fancy and tricky to design properly. But if done right, maybe you can avoid having to parse SQL in [SQLiteStudio]. [Googie]: It's not that simple subject, believe me. But this is not the place to discuss it. [AMG]: table_info doesn't work all that well when the table's in an attached database. In 3.7.7.1, it's a syntax error to prefix the table name with the database name in the argument to table_info. If the table name is unique across all attached databases and the main database, just leave the database part out and you're fine. But if there's a conflict, I see no alternative to creating a separate SQLite database connection object with the database containing the table as its main (and only) database. [Googie] 2011-08-08: You still can use table_info() pragma, but you need to do it (the pragma, not its argument) on specific database: ====== PRAGMA attached_db_name.table_info('table_name'); ====== Pragma always works on its local database, including table name that you pass in argument, so you have to execute whole pragma on desired database. Note, that following diagram [http://sqlite.org/syntaxdiagrams.html#pragma-stmt] of PRAGMA syntax represents exactly what I just described. ** Troubleshooting ** *** Unable to open database error *** Sqlite3 data folder and database must be rw accessible to the web server user. Sqlite3 may also need to create temporary files in the data folder during transactions. The following works on a Slackware 10.2 install. 1. Look in your web server's config files (/etc/apache/httpd.conf) for the default user/group. 2. Make both the Sqlite3 folder and database file rw for this user. 3. Better yet, move the Sqlite3 database file to its own folder in the htdocs subtree before you adjust the ownership. Note that the sqlite data file must also be on a real file system. Thus, if your [starkit]/[starpack] application needs to use sqlite, the database is going to have to be copied out to a real filesystem before use. If the database also needs to be written, then it is going to be tricky, because I seem to recall that on some (most? all?) systems, the starpack that is running can't be modified... ***Bus Error (coredump)*** I have this script on a SPARC Solaris 9 system. ====== package require sqlite3 sqlite3 db1 /tmp/testdb db1 eval {CREATE TABLE geom1(rect_id int, x0 real, y0 real, x1 real, y1 real)} db1 eval {INSERT INTO geom1 VALUES(1, 0.0, 0.0, 20.1, 3.1415)} db1 eval {INSERT INTO geom1 VALUES(2, -10.0, -20.0, 200.123, -0.1234)} db1 eval {INSERT INTO geom1 VALUES(3, -100.0, -200.0, 300.0, 400.0)} set x [db1 eval {SELECT * from geom1 ORDER BY rect_id}] puts $x db1 close ====== When I attempt to run it with [ActiveTcl] 8.5.4, I get a Bus Error(coredump) when attempting to perform the CREATE TABLE. Does anyone know what might cause this sort of problem? [LV]: While I don't know what causes this problem, if I download the sqlite3 source code, build and install it on my machine, then use the version I build and install, the script in question runs to completion. When I try either ActiveTcl 8.4.19 or 8.5.4 I get the bus error. After exchanging emails with ActiveState support staff, they provided a fresh build for activetcl 8.4.19 and sqlite3. With that, the core dump did not occur. If you encounter a similar issue using ActiveTcl, submit a bug report providing details about your platform and the version of ActiveTcl you are using with a small coherent example that demonstrates the problem. [DKF]: Also note that this is the wrong place to report bugs in sqlite or ActiveState's build of it since there's no guarantee that anyone who can fix things will watch this page. [LV]: Certainly I agree. I was asking a question here first because I thought the problem '''might''' have been my usage of sqlite3. After I found more information that confirmed that my usage wasn't wrong, I submitted the bug to ActiveState and worked out a resolution with them. *** `$db incrblob` and asynchronous `[chan copy]` *** [AMG]: I can't seem to get ''asynchronous'' `[chan copy]` to work with `$db incrblob`. Synchronous works just fine, but when I do asynchronous, the copy never starts. I'm copying from the incrblob to a network socket. (Someday I might also copy from a network socket to the incrblob, but this is doubtful.) I tried an asynchronous copy from the incrblob to a local file, and that hung too. No, I'm not forgetting to enter the event loop. ;^) The copy code I've written works fine when the source channel is an on-disk file, just not when it's an incrblob. Is this an SQLite bug, a design limitation, or a problem in my code? [AMG]: At the [Eighteenth Annual Tcl/Tk Conference (2011)], [AK], [DRH], and I had a nice discussion concerning this issue, and we identified a potential fix. [AK]'s [memchan] provides an example of how to support asynchronous `[chan copy]`. *** SQLite, Web SQL, and IndexedDB *** [LV]: [http://news.cnet.com/8301-30685_3-20028379-264.html?tag=nl.e703%|%Firefox beta getting new database standard], Stephen Shankland, 2011-01-13, has a brief mention of SQLite as an underlying technology related to [http://en.wikipedia.org/wiki/Web_SQL_Database%|%Web SQL], and which has now been rejected in favor of [http://www.w3.org/TR/IndexedDB/%|%IndexedDB]. Minor edit above, and the following, from [EMJ]. [http://blogs.msdn.com/b/pablo/archive/2010/01/04/html5-does-databases.aspx%|%HTML5 does databases], Pablo M. Castro, 2010-01-04, describes IndexedDB. ''For database people, this is basically an [http://en.wikipedia.org/wiki/ISAM%|%ISAM] API with [Javascript] objects as the record format. You can create indexes to speed up lookups or scans in particular orders. Other than that there is no schema (any clonable Javascript object will do) and no query language.'' So not a database at all then, more a way for people to write a lot of rubbish software. Another case of people with limited experience enthusiastically solving the wrong problem. (Apologies for rant.) [LV]: I was a bit puzzled by the whole thing - unless IndexedDB has multiple implementations, then it is no better than Web SQL. It looks, to me, like a case of [NIH] in action. [CliC]: Neither [Microsoft] nor [Mozilla] "invented" it, according to the CNET article, but rather someone from [Oracle] (maybe formerly from [COMPANY: Sun Microsystems, Inc.%|%Sun]?). They talk about using it to store gobs of data for working offline, a la Google Gears, so I'm guessing they didn't feel need the data guarantees, and did not want the structure, either, of a real SQL database. (They also mention that [Google] will replace [http://en.wikipedia.org/wiki/Gears_%28software%29%|%Gears] with IndexedDB in some future [http://www.google.com/chrome/%|%Chrome] release.) [EMJ]: Glad you put "invented" in quotes. It doesn't matter who they are, they haven't invented anything. They've just designed an API to tie Javascript to a fairly low-level storage mechanism that is not a new concept at all (reminds me of http://en.wikipedia.org/wiki/Btrieve%|%Btrieve%|% (1987)). And they do have transactions, but without any structure at all it is '''not''' a database (BTW, SQL has nothing to do with whether or not something is a database). *** Prepared statement caching *** [AMG]: I had always assumed SQLite cached prepared statements inside a custom [Tcl_Obj] type. However, is actually not the case, at least not in 3.7.7.1. `tclsqlite.c` instead maintains its own prepared statement cache mapping from the text of the SQL query to the prepared statement object. This design approach has some interesting properties: * '''+''': Executing an SQL query won't change the internal representation of a Tcl_Obj, so no other special intrep will be lost. (However, I can't imagine an SQL query being anything other than plain text.) * '''+''': Prepared statement cache lookups aren't hindered by [shimmering]. As long as the string representation remains consistent, the prepared statement will be found. * '''+''': The cache will still work even if the statement is dynamically regenerated, if it winds up having the same string representation each time. I guess you could call this immunity to meta-shimmering. * '''-''': The size of the prepared statement cache is independent of the number of extant Tcl_Objs containing (textual) SQL statements. If the program cycles through executing (at least) ''n+1'' queries when the size of the cache is only ''n'', the queries will get recompiled every time. See [http://www.sqlite.org/tclsqlite.html#cache] for more information on the cache size, which defaults to 10. * '''-''': Prepared statements are not automatically flushed from the cache when the Tcl_Objs they were created from are deleted. This could be a plus, if the program depends on dynamic regeneration of statements. *** Transactions trying to nest themself *** [Googie] 2011-10-30: I have very weird problem in my application. SQLite reports error on executing `BEGIN` statement, because it says I'm trying to nest transaction (execute `BEGIN` inside of previous `BEGIN`). The exact SQLite error message is: "cannot start a transaction within a transaction". Well, I added debug info to application and here's a trace of BEGIN/COMMIT/ROLLBACK statements executed: ======none BEGIN [14:20:45.834 26.10.2011] BEGIN succeed [14:20:45.834 26.10.2011] COMMIT [14:20:45.834 26.10.2011] COMMIT succeed [14:20:46.832 26.10.2011] BEGIN [14:21:33.414 26.10.2011] BEGIN failed [14:21:33.414 26.10.2011] ====== This trace is done with my wrapper around `db eval`, and here's a trace made with `db trace`, so - as I believe - there's no way to skip anything, it logs everything that is executed on database connection, so here is is: ======none SQLite trace: BEGIN TRANSACTION COMMIT TRANSACTION BEGIN TRANSACTION ====== `db errorcode` returns 1, so this is "SQL error or missing database". It cannot be missing database, because this error happens just after other SQL statement is executed on that database - 2 simple code lines earlier. Also the error is always raised by exactly the same "BEGIN" execution, but there are plenty other places with "BEGIN" and only this one causes problem. SQL error doesn't seem to be an answer as well. I receive bug report like these twice a week. This must be something obvious :( I run out of ideas. How can it happen? Note, that I cannot reproduce it. These all are reports from end-users. ** Historical ** [http://sourceforge.net/projects/tcltkaqua/%|%Tcl/Tk Aqua Batteries-Included], a [BI] distribution of [Tcl/Tk Aqua] includes SQLite. The last release in 2005 included Tcl-8.410 and tcssqlite versions 2.8.16 and 3.2.1. <> Application | Package | Database