The SQLite History extension

The article is not completed!

The History SQLite extension may be used for table history logging.

This extension has been tested with SQLite versions 3.6.x running under Linux Debian.

The code is public domain.

Link: http://mobigroup.ru/files/sqlite-ext/history/

Suggests: ENV extension

Usage

There are anly 2 functions in the extension:

history(table ,column) - add history logging support for the table [table] by key column [column].
unhistory(table) - drop history logging triggers for the table [table]. The history table is saved! Only triggers will be drops.

The *_history table consists all fields of the [table] table without any checks or constraints and some additional fields:

CREATE TABLE [table]_history(
...
_date REAL,
_action TEXT,
_user TEXT,
_host TEXT
);

The _action field has action names where 'I' - insert, 'U' - update, 'D' - delete and '' - initial import.

The index for common queries to history table is built autimatically:

sqlite> explain query plan select * from sessions_history where key='test key 0' order by _date;
0|0|TABLE sessions_history WITH INDEX sessions_history_complex_idx ORDER BY

Note: the last record version does not have duplicate in the history table. So "insert" operations does not do logging but all "update" and "delete" operations do logged.

example.sql

g$ sqlite3
SQLite version 3.6.21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .load './libsqliteversioning.so'

sqlite> 
.load './libsqlitehistory.so'

CREATE TABLE IF NOT EXISTS sessions (
    id INTEGER PRIMARY KEY,
    key text not null unique on conflict replace,
    value text not null
);
insert into sessions (key,value) values ('test key','test value');
select history('sessions', 'key'); -- create history table and triggers
select history('sessions', 'key'); -- no action
insert into sessions (key,value) values ('test key','test value');
.schema

insert into sessions (key,value) values ('test key 0','test value 0');
insert into sessions (key,value) values ('test key 1','test value 1');
insert into sessions (key,value) values ('test key 2','test value 2');

.header on
select * from sessions_history;

update sessions set value='new value' where key='test key 0';
delete from sessions where key='test key 0';
select datetime(_date),* from sessions_history where key='test key 0' order by _date desc;

.header off
explain query plan select * from sessions_history where key='test key 0' order by _date;
select unhistory('sessions');
.schema

Example results

sqlite> .read example.sql 

CREATE TABLE sessions (
    id INTEGER PRIMARY KEY,
    key text not null unique on conflict replace,
    value text not null
);
CREATE TABLE sessions_history(
  id INT,
  "key" TEXT,
  value TEXT,
  _date REAL,
  _action TEXT,
  _user TEXT,
  _host TEXT
);
CREATE INDEX sessions_history_complex_idx on sessions_history(key, _date);
CREATE TRIGGER sessions_history_d BEFORE DELETE ON sessions
BEGIN INSERT INTO sessions_history SELECT *, julianday('now'), 'D', sqlite_user(), 
  sqlite_host() FROM sessions WHERE rowid=OLD.rowid;
END;
CREATE TRIGGER sessions_history_i AFTER INSERT ON sessions
BEGIN INSERT INTO sessions_history (key, _date, _action, _user, _host)
  VALUES (NEW.key, julianday('now'), 'I', sqlite_user(), sqlite_host());
END;
CREATE TRIGGER sessions_history_u BEFORE UPDATE ON sessions BEGIN
  SELECT RAISE (ABORT,'The key field "key" can not be modified') WHERE NEW.key != OLD.key;
  INSERT INTO sessions_history SELECT *, julianday('now'), 'U', sqlite_user(),
    sqlite_host() FROM sessions WHERE rowid=OLD.rowid;
END;
id|key|value|_date|_action|_user|_host
|test key||2455178.31008024|||
1|test key|test value|2455178.31008028|D||
|test key||2455178.31008028|I||
|test key 0||2455178.31008029|I||
|test key 1||2455178.31008029|I||
|test key 2||2455178.31008029|I||
datetime(_date)|id|key|value|_date|_action|_user|_host
2009-12-12 19:26:30|3|test key 0|new value|2455178.3100803|D||
2009-12-12 19:26:30|3|test key 0|test value 0|2455178.3100803|U||
2009-12-12 19:26:30||test key 0||2455178.31008029|I||
0|0|TABLE sessions_history WITH INDEX sessions_history_complex_idx ORDER BY

CREATE TABLE sessions (
    id INTEGER PRIMARY KEY,
    key text not null unique on conflict replace,
    value text not null
);
CREATE TABLE sessions_history(
  id INT,
  "key" TEXT,
  value TEXT,
  _date REAL,
  _action TEXT,
  _user TEXT,
  _host TEXT
);
CREATE INDEX sessions_history_complex_idx on sessions_history(key, _date);

Saving the host and user information
The SQLite is commonly used as embedded database engine and has no host and user access control mechanisms. But we can get this information from environment variables or by call of application functions. These two variants are described below.

ENV extension provided sqlite_* functions
The Env extension provide access to enviromnent variables, current user information, etc.

$ SQLITE_USER=USERNAME SQLITE_HOST=HOSTNAME sqlite3
SQLite version 3.6.21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select sqlite_user();
USERNAME
sqlite> select sqlite_host();
HOSTNAME

Application-defined sqlite_* functions

# install functions to db 
proc ::dataset::install {} {
    foreach func {sqlite_user sqlite_host} {
        $db function $func [namespace current]::$func
    }
}
proc ::dataset::sqlite_user {} {
    return [ns_user id]
}
proc ::dataset::sqlite_host {} {
    return [ns_conn server]
}

::dataset::eval db :memory: {
    ns_html::puts [db onecolumn {select sqlite_user()}]
    ns_html::br
    ns_html::puts [db onecolumn {select sqlite_host()}]
}

TODO

1. Multikey support
versioning(table ,column1, column2, ...)

2. No-key support
versioning(table)