Here's some code I'm working on for a sqlite3 database to store backup file content. Might add a BLOB type later for storing binary data. Not sure yet.
PRAGMA foreign_keys=OFF;BEGIN TRANSACTION;CREATE TABLE files (id INTEGER PRIMARY KEY, name TEXT, content TEXT, created_ts TEXT, modified_ts TEXT);CREATE TABLE files_log(id INTEGER PRIMARY KEY, fileid TEXT, name TEXT, content TEXT, created_ts TEXT, modified_ts TEXT, dt TEXT);CREATE TRIGGER files_trig AFTER INSERT ON filesBEGIN update files SET created_ts = datetime('now','localtime') WHERE id = NEW.id;END;CREATE TRIGGER files_update_trig AFTER UPDATE ON filesBEGIN update files SET modified_ts = datetime('now','localtime') WHERE id = OLD.id; insert into files_log(fileid, name, content, created_ts, modified_ts) values(OLD.id, OLD.name, OLD.content, OLD.created_ts, OLD.modified_ts);END;CREATE TRIGGER files_log_update_trig AFTER INSERT ON files_logBEGIN update files_log SET dt = datetime('now', 'localtime') WHERE id = NEW.id;END;COMMIT;