Or just a function to return the size of the current DB. Mind you, automatically deleting rows from a log table isn't enough: you may have to periodically VACUUM the DB, or you may have to setup auto_vacuum (and incremental_vacuum).
I have code like this in one DB: CREATE TABLE IF NOT EXISTS LogConfig (opt TEXT PRIMARY KEY NOT NULL UNIQUE, val); INSERT OR IGNORE INTO LogConfig (opt, val) VALUES ('replication_retain_time', '7 days'); INSERT OR IGNORE INTO LogConfig (opt, val) VALUES ('replication_retain_count', '10000'); INSERT OR IGNORE INTO LogConfig (opt, val) VALUES ('history_retain_time', '7 days'); INSERT OR IGNORE INTO LogConfig (opt, val) VALUES ('history_retain_count', '10000'); ... DROP TRIGGER IF EXISTS EntryLog_prune; CREATE TRIGGER EntryLog_prune AFTER INSERT ON EntryLog FOR EACH ROW BEGIN DELETE FROM EntryLog WHERE mtime < strftime('%s', 'now', '-' || ( SELECT lc.val FROM LogConfig lc WHERE lc.opt = 'history_retain_time')) AND tx < ((SELECT t.tx FROM TX t ORDER by t.tx DESC LIMIT 1) - ( SELECT val FROM LogConfig WHERE opt = 'history_retain_count')); END; It'd be simple to change the above to use a max DB size, if there was a function to return the current DB size. See above note regarding vacuum. Something like this: CREATE TRIGGER EntryLog_prune AFTER INSERT ON EntryLog FOR EACH ROW BEGIN DELETE FROM EntryLog WHERE tx < (SELECT min(el.tx) FROM EntryLog el) - ( SELECT lc.val FROM LogConfig lc WHERE lc.opt = 'delete_at_once') AND db_size() > (SELECT lc.val FROM LogConfig lc WHERE lc.opt = 'max_db_size'); END; _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users