Hey NIco,
Now this is great.....in fact I was playing with an "update hook" the
other day....and was going to put the deletion logic under the
SQLITE_INSERT below....
But your code looks better ;-) Thanks !
void update_callback( void* udp, int type, const char* db_name, const
char* tbl_name, sqlite3_int64 rowid ){
switch(type){
case(SQLITE_INSERT):
//Do deletion logic
break;
case(SQLITE_DELETE):
//Do something
break;
};
}
On 10/05/2011 15:36, Nico Williams wrote:
> 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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
