jsqlite

jsqlite is a Jim Tcl extension that provides a small SQLite interface implemented in C.

The extension creates one command, jsqlite.open. Each successful call to jsqlite.open returns a new database command such as jsql0, jsql1, and so on. Database operations are performed by calling subcommands on that returned command.

This document describes the interface implemented by jsqlite.c.

Synopsis

load ./jsqlite.so

set db [jsqlite.open :memory:]

$db do {
    create table person(
        id integer primary key,
        name text not null
    );
}

$db query {insert into person(name) values(?)} Alice
$db query {insert into person(name) values(?)} Bob

set rows [$db query {select id, name from person order by id}]
# rows => {1 Alice 2 Bob}

$db close

Loading

load ./jsqlite.so

Loading the extension registers:

jsqlite.open

Opening a database

jsqlite.open dbname ?option ...?

Returns a database command name. The command name is generated internally and normally looks like jsql0, jsql1, etc.

Examples:

set db [jsqlite.open test.db]
set db [jsqlite.open :memory:]
set db [jsqlite.open test.db ro]
set db [jsqlite.open test.db -ro]
set db [jsqlite.open test.db mem]
set db [jsqlite.open test.db safe]
set db [jsqlite.open test.db jim]
set db [jsqlite.open test.db nojim]
set db [jsqlite.open test.db nodeserialize]
set db [jsqlite.open test.db -extension ./myext.so]

Options may be written with or without a leading - where supported by the C implementation.

Open options

`ro`, `-ro`

Open the database read-only.

set db [jsqlite.open app.db ro]

Internally this uses SQLITE_OPEN_READONLY.

`mem`, `-mem`

Open the named database, copy it into an in-memory database, and operate on the in-memory copy.

set db [jsqlite.open app.db mem]

When the database command is destroyed, the in-memory database is backed up to the original disk database.

This is not the same thing as opening :memory:. To create an ordinary SQLite in-memory database, use:

set db [jsqlite.open :memory:]

`safe`, `-safe`

Enable safe mode.

set db [jsqlite.open app.db safe]

Safe mode disables jim(), disables deserialize, rejects -extension, and installs a restrictive SQLite authorizer.

`jim`, `-jim`

Enable the SQL function jim() for this connection.

set db [jsqlite.open :memory: jim]

jim() is disabled by default.

`nojim`, `-nojim`

Disable the SQL function jim().

set db [jsqlite.open :memory: jim nojim]

If both jim and nojim are specified, the later option wins, except that safe always disables jim().

`deserialize`, `-deserialize`

Allow the deserialize subcommand. This is the default unless safe mode is used.

set db [jsqlite.open :memory: deserialize]

`nodeserialize`, `-nodeserialize`

Disable the deserialize subcommand for this connection.

set db [jsqlite.open :memory: nodeserialize]

`extension FILE`, `-extension FILE`

Load a compiled SQLite extension through the SQLite C API during database opening.

set db [jsqlite.open :memory: -extension ./myext.so]

The option may be repeated:

set db [jsqlite.open :memory: \
    -extension ./math.so \
    -extension ./regexp.so]

If loading fails, jsqlite.open fails and names the extension file that failed.

-extension is disabled in safe mode.

Database command methods

After opening a database:

set db [jsqlite.open :memory:]

use the returned command:

$db subcommand ?args ...?

Implemented subcommands are:

authorize
cache
changes
close
deserialize
do
exists
lastid
limit
query
rules
serialize

An explicitly instrumented developer build may also include:

printdatastructures

This internal diagnostic is excluded by default, including from ordinary debug builds. Enable it only with make DEBUG_API=1; it is not a supported public API.

`$db do sql_stmts`

Execute SQL using sqlite3_exec().

$db do {
    create table t(id integer primary key, name text);
    insert into t(name) values('Alice');
}

do is best for schema creation, schema changes, PRAGMAs, and multi-statement scripts.

Important properties:

It is not part of the prepared statement cache.
It accepts multiple SQL statements in one string.
It does not bind parameters.
It does not return query rows.
On success it returns an empty result.
On error it raises a Jim error of the form $db do: message.

Use do for this:

$db do {
    create table person(id integer primary key, name text);
    create index person_name_idx on person(name);
}

Do not use do for untrusted values. It does not perform parameter binding.

`$db query ?-null null_string? SQL ?arg ...?`

Prepare, cache, bind, step, and return results from a single SQL statement.

set rows [$db query {select id, name from person where name = ?} Alice]

query is the main interface for parameterized SQL.

Important properties:

It uses the prepared-statement cache.
It accepts exactly one SQL statement.
It rejects extra SQL after the first statement, except whitespace and comments.
It binds Tcl arguments to SQLite parameters by position.
It returns a flat Jim list containing all result columns from all result rows.
It can be used for select, insert, update, delete, and other single statements.

Example insert with binding:

$db query {insert into person(name) values(?)} Alice

Example select with binding:

set rows [$db query {
    select id, name
    from person
    where name = ?
} Alice]

Example with a custom NULL representation:

set rows [$db query -null {<NULL>} {
    select id, deleted_at from person
}]

By default, SQL NULL result values are returned as the empty string.

`$db exists SQL ?arg ...?`

Return 1 if the query returns at least one row, otherwise 0.

if {[$db exists {select 1 from person where name = ?} Alice]} {
    puts "Alice exists"
}

exists uses the same prepared-statement cache and binding rules as query.

It also accepts exactly one SQL statement and rejects extra SQL after the first statement.

`$db changes`

Return the number of rows changed by the most recent insert, update, or delete on this connection.

$db query {update person set name = ? where id = ?} Alicia 1
puts [$db changes]

This wraps sqlite3_changes().

`$db lastid`

Return the last inserted rowid for this connection.

$db query {insert into person(name) values(?)} Alice
set id [$db lastid]

This wraps sqlite3_last_insert_rowid().

`$db close`

Close the database command.

$db close

The command deletes itself. A later attempt to call it will fail because the command no longer exists.

The return value is SQLite's memory high-water mark, reset when the database was opened.

For mem connections, closing also backs up the in-memory database to the disk database.

`$db cache N`

Set the maximum number of cached prepared statements.

$db cache 50

N must be greater than zero.

If the new cache size is smaller than the previous cache size, the cache is cleared.

`$db cache reset`

Clear all cached prepared statements.

$db cache reset

This is useful after unusual schema churn or when you want to release cached statement resources.

`$db serialize`

Serialize the main database to a Jim string containing the SQLite database image.

set image [$db serialize]

This wraps sqlite3_serialize() on the main database.

`$db deserialize db_data`

Replace the main database with the supplied serialized database image.

set image [$db1 serialize]
$db2 deserialize $image

deserialize clears the statement cache before installing the new database image.

It is disabled if the connection was opened with nodeserialize or safe.

An empty db_data string is rejected.

`$db limit`

Reapply jsqlite's default security limits to the connection.

$db limit

The default limits are also applied immediately after opening a database.

`$db limit LIMIT_NAME N`

Set one SQLite limit for this connection.

$db limit SQLITE_LIMIT_VARIABLE_NUMBER 20

Supported limit names are:

SQLITE_LIMIT_LENGTH
SQLITE_LIMIT_SQL_LENGTH
SQLITE_LIMIT_COLUMN
SQLITE_LIMIT_EXPR_DEPTH
SQLITE_LIMIT_COMPOUND_SELECT
SQLITE_LIMIT_VDBE_OP
SQLITE_LIMIT_FUNCTION_ARG
SQLITE_LIMIT_ATTACHED
SQLITE_LIMIT_LIKE_PATTERN_LENGTH
SQLITE_LIMIT_VARIABLE_NUMBER
SQLITE_LIMIT_TRIGGER_DEPTH
SQLITE_LIMIT_WORKER_THREADS

The defaults compiled into jsqlite.c are:

SQLITE_LIMIT_LENGTH              1000000
SQLITE_LIMIT_SQL_LENGTH          100000
SQLITE_LIMIT_COLUMN              128
SQLITE_LIMIT_LIKE_PATTERN_LENGTH 50
SQLITE_LIMIT_EXPR_DEPTH          10
SQLITE_LIMIT_COMPOUND_SELECT     10
SQLITE_LIMIT_VDBE_OP             25000
SQLITE_LIMIT_FUNCTION_ARG        8
SQLITE_LIMIT_ATTACHED            0
SQLITE_LIMIT_VARIABLE_NUMBER     10
SQLITE_LIMIT_TRIGGER_DEPTH       10
SQLITE_LIMIT_WORKER_THREADS      0

These are deliberately conservative security-oriented limits.

`$db rules DFLT_STATUS ?ACTION THIRD FOURTH STATUS ...?`

Create an authorizer rule set and return its numeric index.

set idx [$db rules SQLITE_OK \
    SQLITE_UPDATE person secret SQLITE_DENY]

The first argument is the default status returned when no rule matches.

Each rule has four fields:

ACTION THIRD FOURTH STATUS

Statuses are:

SQLITE_OK
SQLITE_DENY
SQLITE_IGNORE

Actions are SQLite authorizer action names such as:

SQLITE_SELECT
SQLITE_READ
SQLITE_UPDATE
SQLITE_INSERT
SQLITE_DELETE
SQLITE_PRAGMA
SQLITE_TRANSACTION
SQLITE_ATTACH
SQLITE_DETACH
SQLITE_CREATE_TABLE
SQLITE_DROP_TABLE
SQLITE_FUNCTION

The full action list accepted by the implementation is the list in actionCodes[] in jsqlite.c.

Use * to match any required argument:

set idx [$db rules SQLITE_OK \
    SQLITE_READ * * SQLITE_DENY]

Use !name to match anything except name:

set idx [$db rules SQLITE_DENY \
    SQLITE_READ !secret * SQLITE_OK \
    SQLITE_SELECT {} {} SQLITE_OK]

Some authorizer actions require empty arguments, some require one argument, and some require two. If the wrong shape is supplied, rules returns an error.

Examples:

# Deny all SELECT statements.
set idx [$db rules SQLITE_OK \
    SQLITE_SELECT {} {} SQLITE_DENY]
$db authorize $idx

# Deny UPDATE of person.secret only.
set idx [$db rules SQLITE_OK \
    SQLITE_UPDATE person secret SQLITE_DENY]
$db authorize $idx

# Allow only page_size PRAGMA queries.
set idx [$db rules SQLITE_DENY \
    SQLITE_PRAGMA page_size {} SQLITE_OK]
$db authorize $idx

`$db authorize RULE_SET_IDX`

Install an authorizer rule set created by rules.

set idx [$db rules SQLITE_OK SQLITE_SELECT {} {} SQLITE_DENY]
$db authorize $idx

`$db authorize off`

Remove the custom authorizer.

$db authorize off

authorize is disabled in safe mode.

`do` versus `query`

Use do for SQL scripts and schema work:

$db do {
    create table article(
        id integer primary key,
        title text not null,
        body text not null
    );

    create index article_title_idx on article(title);
}

Use query for single statements, especially statements with values:

$db query {
    insert into article(title, body)
    values(?, ?)
} $title $body

do:

calls sqlite3_exec();
is not cached;
allows multiple statements;
has no argument binding;
returns no rows.

query:

calls sqlite3_prepare_v2() through the statement cache;
caches prepared statements by exact SQL text;
allows one statement only;
binds Tcl arguments to SQLite parameters;
returns a flat list of result values.

This means the following is valid with do:

$db do {
    create table a(x);
    create table b(y);
}

but invalid with query:

$db query {select 1; select 2}
# error: extra "select 2" after SQL stmt

Query parameters and binding rules

query and exists both bind arguments by parameter position.

$db query {select ?, ?} 42 hello
# => {42 hello}

The number of Tcl arguments after the SQL string must exactly match the number of SQL parameters reported by SQLite.

$db query {select ?} 1 2
# error: 1 SQL param/s, 2 arg/s

Binding type rules

For ordinary parameters such as ?, ?1, :name, or $name, jsqlite chooses the SQLite binding type from the Jim object type:

Jim integer object       -> sqlite3_bind_int64()
Jim double/coerced-double -> sqlite3_bind_double()
Empty string object      -> sqlite3_bind_null()
Other values             -> sqlite3_bind_text()

Examples:

$db query {select typeof(?)} 42
# => integer

$db query {select typeof(?)} 3.14
# => real

$db query {select typeof(?)} hello
# => text

$db query {select typeof(?)} ""
# => null

Blob binding with `@` parameters

If the SQLite parameter name starts with @, jsqlite always binds the corresponding Jim value as a blob.

$db query {select typeof(@p), length(@p), hex(@p)} "\x00ABC"
# => {blob 4 00414243}

This is the important rule for binary data.

Use ordinary parameters for text/numeric/null values:

$db query {insert into t(name) values(?)} $name

Use @name parameters for blobs:

$db query {insert into files(data) values(@data)} $bytes

An empty string bound to an ordinary parameter becomes SQL NULL:

$db query {select typeof(?)} ""
# => null

An empty string bound to an @ parameter becomes a zero-length blob:

$db query {select typeof(@p), length(@p)} ""
# => {blob 0}

Parameter names do not create Tcl named arguments

Although SQLite supports named parameters, jsqlite still binds by position. The Tcl argument list is positional.

$db query {select :a, :b} one two
# :a receives "one", :b receives "two"

Do not write this expecting name lookup from Tcl variables:

# Not supported as named binding syntax
$db query {select :name} name Alice

Use plain positional arguments:

$db query {select :name} Alice

Result format

query returns a flat Jim list containing all column values from all rows, in row order.

$db do {
    create table t(id integer, name text);
    insert into t values(1, 'Alice');
    insert into t values(2, 'Bob');
}

$db query {select id, name from t order by id}
# => {1 Alice 2 Bob}

For a one-column query, the result is a flat list of that column:

$db query {select name from t order by id}
# => {Alice Bob}

For a one-row, one-column query, the result appears as a single value:

$db query {select count(*) from t}
# => 2

For no rows, the result is an empty list:

$db query {select name from t where id = -1}
# => {}

For multi-column results, group the flat list yourself if needed:

set rows [$db query {select id, name from t order by id}]
foreach {id name} $rows {
    puts "$id $name"
}

There is no separate row object format in the C implementation.

Statement cache

query and exists use an LRU cache of prepared statements.

The default maximum cache size is:

20 statements

The cache key is the exact SQL string. SQL strings that differ in whitespace, comments, or literal values are different cache entries.

Good cache use:

$db query {select id from person where name = ?} Alice
$db query {select id from person where name = ?} Bob

Poor cache use:

$db query "select id from person where name = 'Alice'"
$db query "select id from person where name = 'Bob'"

Change cache size:

$db cache 100

Clear cache:

$db cache reset

The cache protects against recursive reuse of a statement that is already active. If the same cached statement is invoked recursively before it has reset, jsqlite returns:

cached SQL statement is already active

NULLs, empty strings, text, and blobs

Result NULLs

By default, SQL NULL result values are returned as an empty string.

$db query {select NULL}
# => {}

That can be ambiguous because an actual empty string result also appears empty.

Use -null to choose a custom representation for result NULLs:

$db query -null {<NULL>} {select NULL, 1, NULL}
# => {<NULL> 1 <NULL>}

The -null setting is per call. It does not persist.

Input NULLs

When binding ordinary parameters, an empty Jim string becomes SQL NULL:

$db query {select typeof(?)} ""
# => null

Input blobs

Use an @ parameter name to bind a value as a blob:

$db query {insert into file(data) values(@data)} $bytes

This preserves embedded NUL bytes and leading NUL bytes.

SQLite extensions

jsqlite.open can load one or more compiled SQLite extensions during construction.

set db [jsqlite.open :memory: -extension ./testext.so]

This uses the SQLite C API:

sqlite3_load_extension()

It does not use the SQL interface:

select load_extension(...)

During extension loading, C API extension loading is enabled temporarily, then disabled again.

If an extension fails to load, opening fails:

jsqlite.open: failed to load extension "./missing.so": ...

Extensions execute native code in the current process. Only load trusted extensions.

-extension is rejected in safe mode.

Safe mode

Open with:

set db [jsqlite.open :memory: safe]

Safe mode does the following:

disables jim();
disables deserialize;
rejects -extension;
disables the authorize subcommand;
installs a SQLite authorizer that denies selected dangerous operations.

The safe-mode authorizer denies:

SQLITE_ATTACH
SQLITE_DETACH
SQLITE_CREATE_TRIGGER
SQLITE_CREATE_TEMP_TRIGGER
SQLITE_CREATE_VIEW
SQLITE_CREATE_TEMP_VIEW
SQLITE_CREATE_VTABLE
SQLITE_DROP_TRIGGER
SQLITE_DROP_TEMP_TRIGGER
SQLITE_DROP_VIEW
SQLITE_DROP_TEMP_VIEW
SQLITE_DROP_VTABLE

It also denies the SQL function:

load_extension

and denies these PRAGMAs:

writable_schema
journal_mode
synchronous
foreign_keys
trusted_schema

Safe mode is a useful hardening layer, but it should not be treated as a complete sandbox for hostile SQL.

Authorizer rules

rules and authorize provide a programmable wrapper around SQLite's authorizer mechanism.

Create a ruleset:

set idx [$db rules SQLITE_OK \
    SQLITE_UPDATE person secret SQLITE_DENY]

Enable it:

$db authorize $idx

Disable it:

$db authorize off

Deny all SELECT statements

set idx [$db rules SQLITE_OK \
    SQLITE_SELECT {} {} SQLITE_DENY]
$db authorize $idx

Deny reads of one column

set idx [$db rules SQLITE_OK \
    SQLITE_READ person password_hash SQLITE_DENY]
$db authorize $idx

Deny writes to one column

set idx [$db rules SQLITE_OK \
    SQLITE_UPDATE person admin SQLITE_DENY]
$db authorize $idx

Allow only selected reads

set idx [$db rules SQLITE_DENY \
    SQLITE_SELECT {} {} SQLITE_OK \
    SQLITE_READ public_table * SQLITE_OK]
$db authorize $idx

PRAGMA rules

For PRAGMAs, the third field is the PRAGMA name and the fourth field is the first PRAGMA argument, if present.

Allow only page_size query mode:

set idx [$db rules SQLITE_DENY \
    SQLITE_PRAGMA page_size {} SQLITE_OK]
$db authorize $idx

Deny journal_mode = WAL but allow other PRAGMAs:

set idx [$db rules SQLITE_OK \
    SQLITE_PRAGMA journal_mode WAL SQLITE_DENY \
    SQLITE_PRAGMA * * SQLITE_OK]
$db authorize $idx

`jim()` SQL function

The SQL function jim() is disabled by default.

Enable it at open time:

set db [jsqlite.open :memory: jim]

Call a Jim command or proc from SQL:

proc add {a b} {
    expr {$a + $b}
}

$db query {select jim('add', 40, 2)}
# => 42

The first SQL argument must be text and names the Jim command/proc to call.

SQLite values are converted to Jim values:

SQL NULL    -> empty Jim string
SQL integer -> Jim integer
SQL float   -> Jim double
SQL text    -> Jim string
SQL blob    -> Jim string containing bytes

The Jim result is converted back to SQLite:

empty Jim string       -> SQL NULL
Jim integer object     -> SQL integer
Jim double object      -> SQL real
other Jim string       -> SQL text or blob

If any input argument after the command name was a blob, the string result is returned as a blob. If the string inputs were text only, the string result is returned as text.

jim() is created with SQLITE_DIRECTONLY, so SQLite should reject indirect use from schema objects such as views.

Errors in the Jim command propagate as SQL errors.

jim() is disabled by nojim and by safe.

Serialization and deserialization

serialize returns a SQLite database image:

set image [$db serialize]

deserialize installs a database image:

set db2 [jsqlite.open :memory:]
$db2 deserialize $image

Example:

set db1 [jsqlite.open :memory:]
$db1 do {
    create table t(x);
    insert into t values(42);
}
set image [$db1 serialize]

set db2 [jsqlite.open :memory:]
$db2 deserialize $image
$db2 query {select x from t}
# => 42

deserialize clears the statement cache.

deserialize may accept byte strings that are not valid SQLite databases. In that case, the error may appear on a later SQL operation when SQLite inspects the image.

Limits

jsqlite applies default SQLite limits at open time. They are conservative and security-oriented.

The most visible default is:

SQLITE_LIMIT_VARIABLE_NUMBER = 10

So this fails by default:

$db query {select ?,?,?,?,?,?,?,?,?,?,?} 1 2 3 4 5 6 7 8 9 10 11
# too many SQL variables

Raise it for trusted use:

$db limit SQLITE_LIMIT_VARIABLE_NUMBER 1000

Reset defaults:

$db limit

Errors

Most failures raise a Jim error.

if {[catch {$db query {select * from missing_table}} msg]} {
    puts $msg
}

Common error forms:

jsqlite.open: option "rw" unknown
jsqlite.open: option "-extension" requires a file
jsqlite.open: failed to load extension "./x.so": ...
jsql0: unknown subcmd "bogus"
jsql0 do: ...
jsql0 query: ...
jsql0 exists: ...
jsql0 query: extra "..." after SQL stmt
jsql0 query: 1 SQL param/s, 2 arg/s
jsql0 query: SQL contains embedded NUL
jsql0 query: cached SQL statement is already active

Gotchas

`query` returns a flat list, not rows

set rows [$db query {select id, name from person order by id}]
foreach {id name} $rows {
    puts "$id $name"
}

`query` allows one statement only

Use do for multi-statement SQL scripts.

`do` has no parameter binding

Do not concatenate untrusted values into SQL passed to do.

Use query:

$db query {insert into person(name) values(?)} $name

Empty string input becomes SQL NULL unless you use `@` blob parameters

$db query {select typeof(?)} ""
# null

$db query {select typeof(@p), length(@p)} ""
# blob 0

`@name` means blob binding, not Tcl named binding

This binds the first Tcl argument as a blob:

$db query {insert into file(data) values(@data)} $bytes

All binding is still positional.

Default variable limit is low

By default, only 10 SQL variables are allowed. Increase it explicitly if your application needs more.

SQL text with embedded NUL is rejected

This is intentional. Blob data should be passed as a bound @ parameter, not embedded in SQL text.

`jim()` is powerful and disabled by default

Only enable it for trusted SQL.

Extensions are native code

Only load trusted compiled extensions.

Safe mode is restrictive

Safe mode blocks views, triggers, virtual tables, attach/detach, selected PRAGMAs, deserialize, jim(), and extension loading.

Examples

Create schema and insert rows

load ./jsqlite.so

set db [jsqlite.open people.db]

$db do {
    create table if not exists person(
        id integer primary key,
        name text not null,
        age integer
    );
}

$db query {insert into person(name, age) values(?, ?)} Alice 30
$db query {insert into person(name, age) values(?, ?)} Bob 25

foreach {id name age} [$db query {select id, name, age from person order by id}] {
    puts "$id: $name ($age)"
}

$db close

Store and retrieve a blob

set db [jsqlite.open :memory:]

$db do {create table file(name text, data blob);}

set bytes "\x00ABC\xff"
$db query {insert into file(name, data) values(?, @data)} sample $bytes

set info [$db query {select typeof(data), length(data), hex(data) from file}]
# => {blob 5 00414243FF}

Use `exists`

if {[$db exists {select 1 from person where name = ?} Alice]} {
    puts "found"
}

Use a custom NULL marker

$db do {
    create table event(id integer, finished_at text);
    insert into event values(1, NULL);
}

set rows [$db query -null {not-finished} {
    select id, finished_at from event
}]
# => {1 not-finished}

Cache parameterized statements

$db cache 100

foreach name {Alice Bob Charlie} {
    $db query {insert into person(name) values(?)} $name
}

The same SQL text is reused, so the prepared statement cache is effective.

Load a compiled SQLite extension

set db [jsqlite.open :memory: -extension ./testext.so]
set answer [$db query {select jsqlite_ext_answer()}]
# => 42

Serialize and restore

set db1 [jsqlite.open :memory:]
$db1 do {create table t(x); insert into t values(123);}
set image [$db1 serialize]

set db2 [jsqlite.open :memory:]
$db2 deserialize $image
$db2 query {select x from t}
# => 123

Safe mode

set db [jsqlite.open :memory: safe]

catch {$db do {create view v as select 1}} msg
puts $msg
# not authorized / authorization denied

Custom authorizer

set db [jsqlite.open :memory:]
$db do {
    create table account(id integer, public text, secret text);
    insert into account values(1, 'hello', 'hidden');
}

set idx [$db rules SQLITE_OK \
    SQLITE_READ account secret SQLITE_DENY]
$db authorize $idx

catch {$db query {select secret from account}} msg
puts $msg
# not authorized

$db authorize off

Build and test

A typical local build is:

make
make test

The extension links against SQLite and includes Jim Tcl headers from ../jimtcl by default. Override paths if needed:

make JIMDIR=/path/to/jimtcl JIMSH=/path/to/jimtcl/jimsh test

Useful test targets include:

make test
make test-repeat
make memcheck
make sanitize
make test-sanitize
make fuzz
make fuzz-deserialize
make do-all

License

See the license notice in jsqlite.c.

Production build and verification

For a hardened optimized build:

make release

For the normal regression suite and lifecycle repetition:

make test
N=100 make test-repeat

For AddressSanitizer and UndefinedBehaviorSanitizer checking of the extension:

make sanitize
make test-sanitize

For full-process sanitizer checking, including Jim Tcl itself:

make jim-sanitize
make sanitize
make test-sanitize

For Valgrind checking:

make memcheck

For line, branch, and call coverage using gcov:

make coverage

The internal diagnostic subcommand can be compiled only when actively developing the extension:

make DEBUG_API=1

Do not ship a production build with DEBUG_API=1.

Name		Name	Last commit message	Last commit date
Latest commit History 3 Commits
LICENSE		LICENSE
Makefile		Makefile
README.md		README.md
SECURITY.md		SECURITY.md
fuzz_jsqlite.tcl		fuzz_jsqlite.tcl
fuzz_jsqlite_deserialize.tcl		fuzz_jsqlite_deserialize.tcl
jsqlite-logo.png		jsqlite-logo.png
jsqlite.c		jsqlite.c
jsqlite.test.tcl		jsqlite.test.tcl
testext.c		testext.c
testhash.c		testhash.c

Folders and files

Latest commit

History

Repository files navigation

jsqlite

Contents

Synopsis

Loading

Opening a database

Open options

ro, -ro

mem, -mem

safe, -safe

jim, -jim

nojim, -nojim

deserialize, -deserialize

nodeserialize, -nodeserialize

extension FILE, -extension FILE

Database command methods

$db do sql_stmts

$db query ?-null null_string? SQL ?arg ...?

$db exists SQL ?arg ...?

$db changes

$db lastid

$db close

$db cache N

$db cache reset

$db serialize

$db deserialize db_data

$db limit

$db limit LIMIT_NAME N

$db rules DFLT_STATUS ?ACTION THIRD FOURTH STATUS ...?

$db authorize RULE_SET_IDX

$db authorize off

do versus query

Query parameters and binding rules

Binding type rules

Blob binding with @ parameters

Parameter names do not create Tcl named arguments

Result format

Statement cache

NULLs, empty strings, text, and blobs

Result NULLs

Input NULLs

Input blobs

SQLite extensions

Safe mode

Authorizer rules

Deny all SELECT statements

Deny reads of one column

Deny writes to one column

Allow only selected reads

PRAGMA rules

jim() SQL function

Serialization and deserialization

Limits

Errors

Gotchas

query returns a flat list, not rows

query allows one statement only

do has no parameter binding

Empty string input becomes SQL NULL unless you use @ blob parameters

@name means blob binding, not Tcl named binding

Default variable limit is low

SQL text with embedded NUL is rejected

jim() is powerful and disabled by default

Extensions are native code

Safe mode is restrictive

Examples

Create schema and insert rows

Store and retrieve a blob

Use exists

Use a custom NULL marker

Cache parameterized statements

Load a compiled SQLite extension

Serialize and restore

Safe mode

Custom authorizer

Build and test

License

`ro`, `-ro`

`mem`, `-mem`

`safe`, `-safe`

`jim`, `-jim`

`nojim`, `-nojim`

`deserialize`, `-deserialize`

`nodeserialize`, `-nodeserialize`

`extension FILE`, `-extension FILE`

`$db do sql_stmts`

`$db query ?-null null_string? SQL ?arg ...?`

`$db exists SQL ?arg ...?`

`$db changes`

`$db lastid`

`$db close`

`$db cache N`

`$db cache reset`

`$db serialize`

`$db deserialize db_data`

`$db limit`

`$db limit LIMIT_NAME N`

`$db rules DFLT_STATUS ?ACTION THIRD FOURTH STATUS ...?`

`$db authorize RULE_SET_IDX`

`$db authorize off`

`do` versus `query`

Blob binding with `@` parameters

`jim()` SQL function

`query` returns a flat list, not rows

`query` allows one statement only

`do` has no parameter binding

Empty string input becomes SQL NULL unless you use `@` blob parameters

`@name` means blob binding, not Tcl named binding

`jim()` is powerful and disabled by default

Use `exists`

Packages