Tcl/Tk and SQLite

D. Richard Hipp
[email protected]

11^th Annual Tcl/Tk Conference
New Orleans
2004 October 14

Overview Of SQLite

Page 2 of 41 [prev] [next]

Overview Of SQLite

Page 3 of 41 [prev] [next]

• Most SQL database engines are client-server
• A separate server process must be configured and running

Overview Of SQLite

Page 4 of 41 [prev] [next]

• SQLite is serverless
• Individual applications talk directly to the disk file

• No server process to configure, start, administer, and/or debug

Users Of SQLite

Page 5 of 41 [prev] [next]

History Of SQLite

Page 6 of 41 [prev] [next]

• Project started on 2000-May-29
• Version 1.0 based GDBM release 2000-Aug-27
• Version 2.0 using B-Trees on 2001-Sep-28
• Intense development culminates in Version 2.7, 2002-Aug-25
• Relatively little change until Version 3.0, 2004-June-30

Why Tcl And SQLite Make A Good Team

Page 7 of 41 [prev] [next]

SQLite helps fill the data structure gap in Tcl SQLite and Tcl agree on their data typing philosophy SQLite comes bundled with bindings for Tcl SQLite includes SQL language extensions designed to make it work better with Tcl Unlike other scripting languages, Tcl syntax can be adapted to the needs of SQLite Both SQLite and Tcl are implemented using simple and well-commented C code SQLite depends on Tcl for its regression testing - SQLite would not have been possible without Tcl

The fact that both use a feather icon is purely coincidence...

Using SQLite With Tcl - Getting Started

Page 9 of 41 [prev] [next]

• Interface designed for ease of use

load ./tclsqlite.so Sqlite3 sqlite3 db test.db db eval {CREATE TABLE t1(a,b,c)} db eval {INSERT INTO t1 VALUES(1,2,3)} db eval {INSERT INTO t1 VALUES('hello',NULL,'world')} db close

• You can, of course, use package require instead of an explicit load.
• The library defines a single new Tcl command: sqlite3.
• Database filenames do not require any particular extension.
• Name of the connection is arbitrary
• Multiple connections can be open at once

Using SQLite with Tcl - Querying

Page 10 of 41 [prev] [next]

• The eval method returns a Tcl list of query results

sqlite3 db test.db puts [db eval {SELECT * FROM t1}] 1 2 3 hello {} world

• NULLs are translated into empty strings

Using SQLite with Tcl - Querying

Page 11 of 41 [prev] [next]

• A script at the end of then eval command is executed once for each row

db eval {SELECT * FROM t1} { puts a=$a puts b=$b puts c=$c } a=1 b=2 c=3 a=hello b= c=world

• Local variables are set to the columns for each row
• Aside: The fact that Tcl can be extended like this is one of the key reasons why it is a better language than Python/Perl/PHP.

Using SQLite with Tcl - Querying

Page 12 of 41 [prev] [next]

• The AS clause in the SQL statement causes different local variables to be used

db eval {SELECT a AS x, b AS y, c AS z FROM t1} { puts x=$x puts y=$y puts z=$z } x=1 y=2 z=3 x=hello y= z=world

Using SQLite with Tcl - Querying

Page 13 of 41 [prev] [next]

• Use an array variable name in between the SQL statement and the Tcl script to cause values to be loaded into an array

db eval {SELECT a AS x, b AS y, c AS z FROM t1} abc { parray abc } abc(*) = x y z abc(x) = 1 abc(y) = 2 abc(z) = 3 abc(*) = x y z abc(x) = hello abc(y) = abc(z) = world

• The special (*) element of the array holds a list of all columns in column order

Using SQLite with Tcl - Querying

Page 14 of 41 [prev] [next]

• break and continue work as you expect them to

db eval {SELECT * FROM t1} { if {$a==1} break } puts a=$a puts b=$b puts c=$c a=1 b=2 c=3

• Variables persist after the query exits
• List of query results is not returned when there is a script

Using SQLite with Tcl - Querying

Page 15 of 41 [prev] [next]

• The onecolumn method returns the first column of the first row of the result.

set a [db onecolumn {SELECT * FROM t1}] puts a=$a a=1

• Various ways to do the same thing

set a [db onecolumn {SELECT a FROM t1}] set a [lindex [db eval {SELECT a FROM t1 LIMIT 1}] 0] db eval {SELECT a FROM t1} break

Using SQLite with Tcl - Updates

Page 16 of 41 [prev] [next]

• The naive way to insert data is to build an SQL statement manually

set fd [open bigfile.txt] set content [read $fd] close $fd set qcontent [string map {' ''} $content] db eval "INSERT INTO t1 VALUES(1,2,'$qcontent')"

• Error prone - lots of syntax to get right
• Requires copying the content multiple times
• Does not work if the content contains \000

Using SQLite with Tcl - Updates

Page 17 of 41 [prev] [next]

• SQLite understands Tcl variables and will read them directly

set fd [open bigfile.txt] set content [read $fd] close $fd db eval {INSERT INTO t1 VALUES(1,2,$content)}

• SQL statement in curly braces - like the expression on an if
• Simplified syntax
• Reaches in and pulls content directly from Tcl variable - no extra copies made
• Works even if content contains \000

Using SQLite with Tcl - User Defined Functions

Page 18 of 41 [prev] [next]

• You can define new SQL functions that invoke Tcl scripts

proc sql_sqrt {x} {return [expr {sqrt($x)}]} db function sqrt sql_sqrt

• Any number of arguments
• Need not be a simple math function. Consider:

proc sql_eval {script} {return [uplevel #0 $script]} db function eval sql_eval db eval {SELECT eval(cmd) FROM cmdset}

Using SQLite with Tcl - Other Interface Features

Page 19 of 41 [prev] [next]

• The authorizer method defines a callback that can validate user-entered SQL
• The busy and timeout methods control behavior when the database is locked
• The changes and total_changes methods return counts of the number of rows modified
• The collate and collation_needed methods define new text collating sequences
• The commit_hook method defines a callback that runs prior to each transaction commit
• The complete method determines if its argument is a a complete SQL statement or if additional input is required
• The errorcode method returns the error code returned by the last SQL command
• The last_insert_rowid returns the ROWID of the most recently inserted row in the database
• The progress method registers a callback that is invoked periodically during long-running SQL statements
• The trace method registers a callback that is invoked prior to executing each SQL statement

SQL Language Understood By SQLite

Page 21 of 41 [prev] [next]

SQLite Datatypes

Page 22 of 41 [prev] [next]

• Declared column types are mostly ignored
• Type is associated with a value, not with the values container
• Five datatypes supported:

  SQL Type	Equivalent Tcl Type
  NULL	Empty String
  64-bit Signed Integer	WideInt
  64-bit IEEE Float	Double
  Text	String
  BLOB	ByteArray

• Any value can be inserted into most any column
  • Exception: INTEGER PRIMARY KEY can only hold an integer
  • Exception: NULLs cannot be inserted into a NOT NULL column
• Flexible typing provides a good match with Tcl code

Datatypes (continued)

Page 23 of 41 [prev] [next]

• Column Affinity - values try to convert to the column type if they can do so without loss of information
  • Integer
  • Numeric
  • Text
  • None
• Affinity determined by declared type:

  If type declaration contains...	Then the affinity is...
  "INT"	Integer
  "CHAR" or "CLOB" or "TEXT"	Text
  "BLOB" or there is no type declaration	None
  Otherwise	Numeric

• Strict Affinity Mode:
  • Raises an exception if values cannot converted into the column type
  • Makes SQLite work more like statically typed databases
  • Not yet implemented

ROWIDs

Page 24 of 41 [prev] [next]

• Every table entry has a 64-bit signed integer rowid.
• The rowid is used as the key in the underlying B-Tree.
• Called "ROWID" or "_ROWID_" or "OID".
• Querying the value of the rowid:

  SELECT rowid, * FROM table1;
  

• Specifying the rowid on an INSERT:

  CREATE TABLE table2(x);
  INSERT INTO table2(rowid, x) VALUES(123,'hi');
  

• Changing the value of the rowid:

  UPDATE table2 SET rowid=rowid-1 WHERE rowid=123;
  

• Columns of type INTEGER PRIMARY KEY become an alias for the ROWID.
• Inserting NULL into ROWID fills it in with the next available integer, or a random integer if the largest integer is already taken
• Use the last_insert_rowid method to get the ROWID that was used for the most recent insert

Database Schema Information

Page 25 of 41 [prev] [next]

• Original text of statements used to create table, indices, views, and triggers is stored in the SQLITE_MASTER table.

  CREATE TABLE sqlite_master (
    type text,
    name text,
    tbl_name text,
    rootpage integer,
    sql text
  );
  

• Temporary objects stored in SQLITE_TEMP_MASTER.
• These tables are read-only.
• Parsed information available using PRAGMAs.
  • PRAGMA table_info(table)
  • PRAGMA index_info(index)
  • PRAGMA index_list(table)

Querying The Database Schema

Page 26 of 41 [prev] [next]

• Example: How to list all tables in a database

puts [db eval {SELECT name FROM sqlite_master WHERE type='table'}] t1

• Example: How to see the structure of a single table

puts [db onecolumn {SELECT sql FROM sqlite_master WHERE name='t1'}] CREATE TABLE t1(a,b,c)

Working With Multiple Databases

Page 27 of 41 [prev] [next]

• ATTACH DATABASE 'example1.db' AS ex1;
• The default database has the name "main". TEMP tables are in a separate database named "temp".
• DETACH DATABASE ex2;
• Up to 10 attached databases (in addition to "main" and "temp")
• Joins can read from multiple databases.

  SELECT * FROM main.t1, ex1.t1

• Example: A quick way to move data from one database to another:

  ATTACH DATABASE 'backup.db' AS backup; CREATE TABLE backup.table1 AS SELECT * FROM table1; DETACH DATABASE backup;

Conflict Resolution Algorithms

Page 28 of 41 [prev] [next]

• Constraint violations can be handled in 5 ways:
  • ROLLBACK - Undo all changes and exit the transaction.
  • ABORT - Current statement is undone. Prior statements remain. (Default)
  • FAIL - Statement stops. Partial results are retained.
  • IGNORE - No change to current row. Continue executing.
  • REPLACE - Delete prior rows that caused the conflict.

• CREATE TABLE tbl1(
     a INTEGER UNIQUE ON CONFLICT IGNORE,
     b ANY NOT NULL ON CONFLICT ROLLBACK
  );
  

• CREATE UNIQUE INDEX idx1 ON tbl1(a,b) ON CONFLICT REPLACE;
  

• INSERT OR REPLACE INTO tbl1 VALUES(1,2);
  • Or use just "REPLACE" as shorthand for "INSERT OR REPLACE"
• UPDATE OR IGNORE tbl1 SET a=a+1 WHERE b=2;

SQLite In Traditional SQL Roles

Page 30 of 41 [prev] [next]

Stand-in for an enterprise client/server database engine

• Reduces administration burden during product development
• Simplifies demos and trial version installation
• At least one user is replacing Informix with SQLite in a large Tcl/Tk application after finding that SQLite performs faster and more reliably

 

Websites

• Integrate with tclhttpd?
• Already part of TWS
• Very handy in TCL-based CGI scripts
• SQLite is standard with PHP and with XAMPP

SQLite As Replacement For Ad Hoc Data Files

Page 31 of 41 [prev] [next]

• Instead of using open, puts, and gets for data I/O, open an SQLite database
• Storage for...
  • User preferences
  • N most recently accessed files
  • Event log with automatic expiration
  • Shared local cache of remote files
  • Last known location of windows
• No need to rewrite the whole file after each change - just do an UPDATE
• Structured data storage without having to write a formatter and parser
• Changes to the file are atomic and durable
• SQLite ensures that two or more applications do not try to change the file at the same instant
• On read back, use queries rather than searching through the entire file

SQLite As Internal Structured Data Format

Page 32 of 41 [prev] [next]

• Open a memory-only database using the special :memory: filename
• Structured data is easier to understand and work with than a pile of array variables
• Advanced queries in SQL can replace entire subroutines of Tcl
• Advanced data sorting capabilities
• Indexing strategy can be changed without impact to code
• Triggers can be used to automate data structure maintenance
• No disk I/O so operation is fast

SQLite As Application File Format

Page 33 of 41 [prev] [next]

• Consider using SQLite to store application data instead of XML or ad hoc text. Examples:
  • Spreadsheet-like applications
  • Single-user financial programs
  • Vector graphics programs
  • Presentation graphics
  • Help viewers
  • Planning and decision support tools
  • Anything that has a data focus (not a text focus) and has File/Open and File/Save items on the main menu.
• No need to write a parser and formatter
• Updates are atomic and durable
• Application data can be queried and changed using an external SQLite command-line client
• Simplifies sharing of application file format among several different programs - Each program only deals with those parts of the file it cares about

SQLite As Application File Format - First Approach

Page 34 of 41 [prev] [next]

Features:

• File/Open menu option does sqlite3 db $filename
• All changes written directly to the disk file - there is no File/Save menu option

Advantages:

• Do not need to read in the whole file - only those parts in active use
• Little or no work is lost if there is a crash or power failure
• Allows multiple simultaneous edits of the same application file

Disadvantages:

• Different from what most users are familiar with
• No way to abandon changes - needs a good undo mechanism to compensate

SQLite As Application File Format - Second Approach

Page 35 of 41 [prev] [next]

Features:

• File/Open menu option does sqlite3 db $filename
• Immediately begin a transaction: db eval BEGIN
• File/Save commits the transaction: db eval COMMIT

Advantages:

• Do not need to read in the whole file - only those parts in active use
• Uncommitted changes automatically rollback after a crash or power failure
• User can abandon changes using ROLLBACK

Disadvantages:

• Work might be lost following a crash or power failure
• Commit might fail if there are other readers active

SQLite As Application File Format - Third Approach

Page 36 of 41 [prev] [next]

Features:

• File/Open menu option creates an in-memory database then copies the disk database into the in-memory database
• File/Save copies the in-memory database back to disk
• Use the SQLite ATTACH command to do the copying efficiently

Advantages:

• Similar to the traditional application metaphor and hence more familiar to both users and programmers
• Changes can be abandoned at any time

Disadvantages:

• Requires enough memory to hold the entire application file
• Work might be lost following a crash or power failure
• Slower operation of File/Open and File/Save due to the need to copy the entire database

SQLite As Application File Format - Undo/Redo

Page 37 of 41 [prev] [next]

• A simple script automatically creates TRIGGERs that record all changes to tables in the database which is the application file
• To undo/redo:

  • Replay relevant parts of the change log
  • Restore any Tcl variables saved in the change log
  • Repaint the screen based on new database content

• A complete Undo/Redo module is a few hundred lines of Tcl code
• Because the history file is in a database on disk, undo/redo histories can grow very large without consuming excess memory
• Once written, the same Undo/Redo module can be reused for different applications!

SQLite Database As Program

Page 38 of 41 [prev] [next]

• Store Tcl procedures in individual records of a database instead of lumped together in a text file
• Interpreter initializes by looking up and evaluating the main procedure
• The unknown mechanism automatically loads additional procedures as needed
• Built-in editor allows procedures and variables to be edited on-the-fly
• Reclaim memory by periodically deleting procedures - procedures will automatically reload themselves if needed
• Historical versions of procedures can be saved - built-in configuration management system

Summary And Conclusions

Page 40 of 41 [prev] [next]

• SQLite provides a fast, lightweight, embedded, zero-configuration SQL database engine for Tcl
• Tightly integrated with the Tcl language
• Tcl-compatible license
• Helps close the Tcl data structure gap
• Useful in ways you have not previously thought about using an SQL database