# sqlite-history
[](https://pypi.org/project/sqlite-history/)
[](https://github.com/simonw/sqlite-history/releases)
[](https://github.com/simonw/sqlite-history/blob/main/LICENSE)
Track changes to SQLite tables using triggers
## Installation
Install this library using `pip`:
pip install sqlite-history
## Usage
This library can be used to configure triggers on a SQLite database such that any inserts, updates or deletes against a table will have their changes recorded in a separate table.
You can enable history tracking for a table using the `enable_history()` function:
import sqlite_history
import sqlite3
conn = sqlite3.connect("data.db")
conn.execute("CREATE TABLE table1 (id INTEGER PRIMARY KEY, name TEXT)")
sqlite_history.configure_history(conn, "table1")
Or you can use the CLI interface, available via `python -m sqlite_history`:
python -m sqlite_history data.db table1 [table2 table3 ...]
Use `--all` to configure it for all tables:
python -m sqlite_history data.db --all
## How this works
Given a table with the following schema:
<!-- [[[cog
import cog
create_table_sql = """
CREATE TABLE people (
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER,
weight REAL
);
""".strip()
cog.out(
"```sql\n{}\n```".format(create_table_sql)
)
]]] -->
```sql
CREATE TABLE people (
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER,
weight REAL
);
```
<!-- [[[end]]] -->
This library will create a new table called `_people_history` with the following schema:
<!-- [[[cog
from sqlite_history import sql
import sqlite3
db = sqlite3.connect(":memory:")
db.execute(create_table_sql)
columns_and_types = sql.table_columns_and_types(db, "people")
history_schema = sql.history_table_sql("people", columns_and_types)
cog.out(
"```sql\n{}\n```".format(history_schema.strip())
)
]]] -->
```sql
CREATE TABLE _people_history (
_rowid INTEGER,
id INTEGER,
name TEXT,
age INTEGER,
weight REAL,
_version INTEGER,
_updated INTEGER,
_mask INTEGER
);
CREATE INDEX idx_people_history_rowid ON _people_history (_rowid);
```
<!-- [[[end]]] -->
The `_rowid` column references the `rowid` of the row in the original table that is being tracked. If a row has been updated multiple times there will be multiple rows with the same `_rowid` in this table.
The `id`, `name`, `age` and `weight` columns represent the new values assigned to the row when it was updated. These can also be `null`, which might represent no change or might represent the value being set to `null` (hence the `_mask` column).
The `_version` column is a monotonically increasing integer that is incremented each time a row is updated.
The `_updated` column is a timestamp showing when the change was recorded. This is stored in milliseconds since the Unix epoch - to convert that to a human-readable UTC date you can use `strftime('%Y-%m-%d %H:%M:%S', _updated / 1000, 'unixepoch')` in your SQL queries.
The `_mask` column is a bit mask that indicates which columns changed in an update. The bit mask is calculated by adding together the following values:
1: id
2: name
4: age
8: weight
Tables with different schemas will have different `_mask` values.
A `_mask` of `-1` indicates that the row was deleted.
The following triggers are created to populate the `_people_history` table:
<!-- [[[cog
triggers_sql = sql.triggers_sql("people", [c[0] for c in columns_and_types])
cog.out(
"```sql\n{}\n```".format(triggers_sql.strip())
)
]]] -->
```sql
CREATE TRIGGER people_insert_history
AFTER INSERT ON people
BEGIN
INSERT INTO _people_history (_rowid, id, name, age, weight, _version, _updated, _mask)
VALUES (new.rowid, new.id, new.name, new.age, new.weight, 1, cast((julianday('now') - 2440587.5) * 86400 * 1000 as integer), 15);
END;
CREATE TRIGGER people_update_history
AFTER UPDATE ON people
FOR EACH ROW
BEGIN
INSERT INTO _people_history (_rowid, id, name, age, weight, _version, _updated, _mask)
SELECT old.rowid,
CASE WHEN old.id != new.id then new.id else null end,
CASE WHEN old.name != new.name then new.name else null end,
CASE WHEN old.age != new.age then new.age else null end,
CASE WHEN old.weight != new.weight then new.weight else null end,
(SELECT MAX(_version) FROM _people_history WHERE _rowid = old.rowid) + 1,
cast((julianday('now') - 2440587.5) * 86400 * 1000 as integer),
(CASE WHEN old.id != new.id then 1 else 0 end) + (CASE WHEN old.name != new.name then 2 else 0 end) + (CASE WHEN old.age != new.age then 4 else 0 end) + (CASE WHEN old.weight != new.weight then 8 else 0 end)
WHERE old.id != new.id or old.name != new.name or old.age != new.age or old.weight != new.weight;
END;
CREATE TRIGGER people_delete_history
AFTER DELETE ON people
BEGIN
INSERT INTO _people_history (_rowid, id, name, age, weight, _version, _updated, _mask)
VALUES (
old.rowid,
old.id, old.name, old.age, old.weight,
(SELECT COALESCE(MAX(_version), 0) from _people_history WHERE _rowid = old.rowid) + 1,
cast((julianday('now') - 2440587.5) * 86400 * 1000 as integer),
-1
);
END;
```
<!-- [[[end]]] -->
## Development
To contribute to this library, first checkout the code. Then create a new virtual environment:
cd sqlite-history
python -m venv venv
source venv/bin/activate
Now install the dependencies and test dependencies:
pip install -e '.[test]'
To run the tests:
pytest
Raw data
{
"_id": null,
"home_page": "https://github.com/simonw/sqlite-history",
"name": "sqlite-history",
"maintainer": "",
"docs_url": null,
"requires_python": ">=3.7",
"maintainer_email": "",
"keywords": "",
"author": "Simon Willison",
"author_email": "",
"download_url": "https://files.pythonhosted.org/packages/9d/5f/aec91eebf8508a1e8d814ceb9bd27a09ee95245ebe22dbf3c3ba2db1ef79/sqlite-history-0.1.tar.gz",
"platform": null,
"description": "# sqlite-history\n\n[](https://pypi.org/project/sqlite-history/)\n[](https://github.com/simonw/sqlite-history/releases)\n[](https://github.com/simonw/sqlite-history/blob/main/LICENSE)\n\nTrack changes to SQLite tables using triggers\n\n## Installation\n\nInstall this library using `pip`:\n\n pip install sqlite-history\n\n## Usage\n\nThis library can be used to configure triggers on a SQLite database such that any inserts, updates or deletes against a table will have their changes recorded in a separate table.\n\nYou can enable history tracking for a table using the `enable_history()` function:\n\n import sqlite_history\n import sqlite3\n\n conn = sqlite3.connect(\"data.db\")\n conn.execute(\"CREATE TABLE table1 (id INTEGER PRIMARY KEY, name TEXT)\")\n sqlite_history.configure_history(conn, \"table1\")\n\nOr you can use the CLI interface, available via `python -m sqlite_history`:\n\n python -m sqlite_history data.db table1 [table2 table3 ...]\n\nUse `--all` to configure it for all tables:\n\n python -m sqlite_history data.db --all\n\n## How this works\n\nGiven a table with the following schema:\n\n<!-- [[[cog\nimport cog\ncreate_table_sql = \"\"\"\nCREATE TABLE people (\n id INTEGER PRIMARY KEY,\n name TEXT,\n age INTEGER,\n weight REAL\n);\n\"\"\".strip()\ncog.out(\n \"```sql\\n{}\\n```\".format(create_table_sql)\n)\n]]] -->\n```sql\nCREATE TABLE people (\n id INTEGER PRIMARY KEY,\n name TEXT,\n age INTEGER,\n weight REAL\n);\n```\n<!-- [[[end]]] -->\n\nThis library will create a new table called `_people_history` with the following schema:\n\n<!-- [[[cog\nfrom sqlite_history import sql\nimport sqlite3\ndb = sqlite3.connect(\":memory:\")\ndb.execute(create_table_sql)\ncolumns_and_types = sql.table_columns_and_types(db, \"people\")\nhistory_schema = sql.history_table_sql(\"people\", columns_and_types)\ncog.out(\n \"```sql\\n{}\\n```\".format(history_schema.strip())\n)\n]]] -->\n```sql\nCREATE TABLE _people_history (\n _rowid INTEGER,\n id INTEGER,\n name TEXT,\n age INTEGER,\n weight REAL,\n _version INTEGER,\n _updated INTEGER,\n _mask INTEGER\n);\nCREATE INDEX idx_people_history_rowid ON _people_history (_rowid);\n```\n<!-- [[[end]]] -->\nThe `_rowid` column references the `rowid` of the row in the original table that is being tracked. If a row has been updated multiple times there will be multiple rows with the same `_rowid` in this table.\n\nThe `id`, `name`, `age` and `weight` columns represent the new values assigned to the row when it was updated. These can also be `null`, which might represent no change or might represent the value being set to `null` (hence the `_mask` column).\n\nThe `_version` column is a monotonically increasing integer that is incremented each time a row is updated.\n\nThe `_updated` column is a timestamp showing when the change was recorded. This is stored in milliseconds since the Unix epoch - to convert that to a human-readable UTC date you can use `strftime('%Y-%m-%d %H:%M:%S', _updated / 1000, 'unixepoch')` in your SQL queries.\n\nThe `_mask` column is a bit mask that indicates which columns changed in an update. The bit mask is calculated by adding together the following values:\n\n 1: id\n 2: name\n 4: age\n 8: weight\n\nTables with different schemas will have different `_mask` values.\n\nA `_mask` of `-1` indicates that the row was deleted.\n\nThe following triggers are created to populate the `_people_history` table:\n<!-- [[[cog\ntriggers_sql = sql.triggers_sql(\"people\", [c[0] for c in columns_and_types])\ncog.out(\n \"```sql\\n{}\\n```\".format(triggers_sql.strip())\n)\n]]] -->\n```sql\nCREATE TRIGGER people_insert_history\nAFTER INSERT ON people\nBEGIN\n INSERT INTO _people_history (_rowid, id, name, age, weight, _version, _updated, _mask)\n VALUES (new.rowid, new.id, new.name, new.age, new.weight, 1, cast((julianday('now') - 2440587.5) * 86400 * 1000 as integer), 15);\nEND;\n\nCREATE TRIGGER people_update_history\nAFTER UPDATE ON people\nFOR EACH ROW\nBEGIN\n INSERT INTO _people_history (_rowid, id, name, age, weight, _version, _updated, _mask)\n SELECT old.rowid, \n CASE WHEN old.id != new.id then new.id else null end, \n CASE WHEN old.name != new.name then new.name else null end, \n CASE WHEN old.age != new.age then new.age else null end, \n CASE WHEN old.weight != new.weight then new.weight else null end,\n (SELECT MAX(_version) FROM _people_history WHERE _rowid = old.rowid) + 1,\n cast((julianday('now') - 2440587.5) * 86400 * 1000 as integer),\n (CASE WHEN old.id != new.id then 1 else 0 end) + (CASE WHEN old.name != new.name then 2 else 0 end) + (CASE WHEN old.age != new.age then 4 else 0 end) + (CASE WHEN old.weight != new.weight then 8 else 0 end)\n WHERE old.id != new.id or old.name != new.name or old.age != new.age or old.weight != new.weight;\nEND;\n\nCREATE TRIGGER people_delete_history\nAFTER DELETE ON people\nBEGIN\n INSERT INTO _people_history (_rowid, id, name, age, weight, _version, _updated, _mask)\n VALUES (\n old.rowid,\n old.id, old.name, old.age, old.weight,\n (SELECT COALESCE(MAX(_version), 0) from _people_history WHERE _rowid = old.rowid) + 1,\n cast((julianday('now') - 2440587.5) * 86400 * 1000 as integer),\n -1\n );\nEND;\n```\n<!-- [[[end]]] -->\n\n## Development\n\nTo contribute to this library, first checkout the code. Then create a new virtual environment:\n\n cd sqlite-history\n python -m venv venv\n source venv/bin/activate\n\nNow install the dependencies and test dependencies:\n\n pip install -e '.[test]'\n\nTo run the tests:\n\n pytest\n",
"bugtrack_url": null,
"license": "Apache License, Version 2.0",
"summary": "Track changes to SQLite tables using triggers",
"version": "0.1",
"split_keywords": [],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "0460aff893bea7b44caf54f9a30b55500b6bae449cac86025e0d1d8859fde6ad",
"md5": "298354d54caa12262e8c18fb6ffd9214",
"sha256": "bcfb10dae3307a08bcc07f95b67510d13132a3765c46f16ca4477de52e7bfe82"
},
"downloads": -1,
"filename": "sqlite_history-0.1-py3-none-any.whl",
"has_sig": false,
"md5_digest": "298354d54caa12262e8c18fb6ffd9214",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.7",
"size": 11096,
"upload_time": "2023-04-09T05:09:58",
"upload_time_iso_8601": "2023-04-09T05:09:58.193972Z",
"url": "https://files.pythonhosted.org/packages/04/60/aff893bea7b44caf54f9a30b55500b6bae449cac86025e0d1d8859fde6ad/sqlite_history-0.1-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "9d5faec91eebf8508a1e8d814ceb9bd27a09ee95245ebe22dbf3c3ba2db1ef79",
"md5": "e8a73e1d6d665c22fbdf9b80f09ef91f",
"sha256": "d8d95776d6c633e4dbfd48ac59eca0f4f760bc2fcc63a2131c65efcffbdf5621"
},
"downloads": -1,
"filename": "sqlite-history-0.1.tar.gz",
"has_sig": false,
"md5_digest": "e8a73e1d6d665c22fbdf9b80f09ef91f",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.7",
"size": 10786,
"upload_time": "2023-04-09T05:09:59",
"upload_time_iso_8601": "2023-04-09T05:09:59.889145Z",
"url": "https://files.pythonhosted.org/packages/9d/5f/aec91eebf8508a1e8d814ceb9bd27a09ee95245ebe22dbf3c3ba2db1ef79/sqlite-history-0.1.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-04-09 05:09:59",
"github": true,
"gitlab": false,
"bitbucket": false,
"github_user": "simonw",
"github_project": "sqlite-history",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "sqlite-history"
}
JSON
