Sqlite update column update#

The above recipe expects the triggers to be installed on the table before any rows are written to it. If tables have a id integer primary key column SQLite does NOT reuse rowids, as explained here. This means that your accompanying records in the _dqe_changes table will have stale rowids, which could lead to surprising behaviour. There's one catch with the above recipe: if you delete ALL of the rows from a table ( delete * from foo) SQLite defaults to reusing rowids in that table, starting again from 1.

Sqlite update column update#

Sqlite> update foo set name = 'hello6' where name = 'hello' Sqlite> delete from foo where name = 'hello3' SQLite has an implicit auto increment feature that takes place for any non-composite primary-key column that is specifically created using INTEGER. To test this I ran sqlite3 (with no arguments, which provides an in-memory database to play with), pasted in the above and then ran this: sqlite>. Rename a column (added supported in version 3.20. INSERT OR REPLACE INTO _dqe_changes(table_id, rowid, deleted, updated_ms) By using an SQLite ALTER TABLE statement, you can perform two actions: Rename a table. Strftime( '%s ', 'now ') || substr(strftime( '%f ', 'now '), 4)ĬREATE TRIGGER IF NOT EXISTS AFTER UPDATE ON ĬREATE TRIGGER IF NOT EXISTS AFTER DELETE ON This is a recipe for timestamp in milliseconds, from. ( select id from _dqe_tables where = 'foo '), INSERT OR REPLACE INTO _dqe_changes(table_id, rowid, updated_ms) INSERT OR IGNORE INTO _dqe_tables() VALUES ( 'foo ') Each table needs to have these triggers created on it: CREATE TRIGGER IF NOT EXISTS AFTER INSERT ON

An example table: CREATE TABLE foo (name text) Create an index for polling against CREATE INDEX _dqe_changes_updated_ms ON _dqe_changes(updated_ms) This table records the timestamped changes CREATE TABLE _dqe_changes(ĭeleted integer, - treated as a null or 1 booleanįOREIGN KEY (table_id) REFERENCES _dqe_tables(id)

This table exists just for table -> integer lookups, - to save space in the _dqe_changes table CREATE TABLE _dqe_tables(