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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
