Hi Jay, Wow, thanks for your detailed message below.....much appreciated ;-)
I will try the PRAGMA and also the "msg_seq".....great..... Lynton On 10/05/2011 19:00, Jay A. Kreibich wrote: > On Tue, May 10, 2011 at 12:42:14PM +0200, Lynton Grice scratched on the wall: >> Hi all, >> >> Thanks for your comments...much appreciated...... >> >> BTW: if I am using SQLIte as a logger, what PRAGMA statement can I use >> to say FIX the sqlite database size to say "5 MB"? > PRAGMA max_page_count = N > > http://www.sqlite.org/pragma.html#pragma_max_page_count > > This simply fixes the total size of the database. If you attempt to > perform an operation (such as an INSERT) that would push it over the > limit, you get an out-of-space error. > > This PRAGMA needs to be set every time the database is opened. > >> Also, lets say I have a AUTOINCREMENT INTEGER PRIMARY KEY, what will >> happen when it reaches 5 MB? Will it just keep returning SQLITE_FULL or >> similar? > Yes. > >> I guess I am looking for a "round robin queue" here? > I'd do something like this. This keeps a constant number of messages > in the log. The "msg_id" provides a message counter, while the > "msg_seq" is used to keep the round-robin offset. The use of a view > is required to enable a INSTEAD OF trigger. There might be a way to > do this directly against the a table, but I'm not all that > experienced with SQLite triggers. > > =========================================================================== > > -- Create storage table: > CREATE TABLE log_t ( > msg_id INTEGER PRIMARY KEY AUTOINCREMENT, > msg_seq INTEGER UNIQUE, > time TEXT DEFAULT CURRENT_TIMESTAMP, > msg TEXT ); > > -- Init sqlite_sequence table: > INSERT INTO log_t ( msg_seq, msg ) VALUES ( -1, 'init' ); > DELETE FROM log_t WHERE msg_seq = -1; > > -- Create view: > CREATE VIEW log AS SELECT msg_id, msg_seq, time, msg FROM log_t; > > CREATE TRIGGER log_seq_trg > INSTEAD OF INSERT ON log > FOR EACH ROW > BEGIN > INSERT OR REPLACE INTO log_t ( msg_seq, msg ) VALUES ( > ( SELECT seq + 1 FROM sqlite_sequence WHERE name = 'log_t' ) % > > -- THIS VALUE CONTROLS THE NUMBER OF MESSAGES KEPT IN THE LOG: > 10, > > NEW.msg ); > END; > > =========================================================================== > > To test: > > =========================================================================== > INSERT INTO log ( msg ) VALUES ( 'a' ); > INSERT INTO log ( msg ) VALUES ( 'b' ); > INSERT INTO log ( msg ) VALUES ( 'c' ); > INSERT INTO log ( msg ) VALUES ( 'd' ); > INSERT INTO log ( msg ) VALUES ( 'e' ); > INSERT INTO log ( msg ) VALUES ( 'f' ); > INSERT INTO log ( msg ) VALUES ( 'g' ); > INSERT INTO log ( msg ) VALUES ( 'h' ); > INSERT INTO log ( msg ) VALUES ( 'i' ); > INSERT INTO log ( msg ) VALUES ( 'j' ); > INSERT INTO log ( msg ) VALUES ( 'k' ); > INSERT INTO log ( msg ) VALUES ( 'l' ); > INSERT INTO log ( msg ) VALUES ( 'm' ); > INSERT INTO log ( msg ) VALUES ( 'n' ); > INSERT INTO log ( msg ) VALUES ( 'o' ); > INSERT INTO log ( msg ) VALUES ( 'p' ); > INSERT INTO log ( msg ) VALUES ( 'q' ); > INSERT INTO log ( msg ) VALUES ( 'r' ); > INSERT INTO log ( msg ) VALUES ( 's' ); > INSERT INTO log ( msg ) VALUES ( 't' ); > INSERT INTO log ( msg ) VALUES ( 'u' ); > INSERT INTO log ( msg ) VALUES ( 'v' ); > INSERT INTO log ( msg ) VALUES ( 'w' ); > INSERT INTO log ( msg ) VALUES ( 'x' ); > INSERT INTO log ( msg ) VALUES ( 'y' ); > INSERT INTO log ( msg ) VALUES ( 'z' ); > =========================================================================== > > sqlite> SELECT * FROM log; > 18|8|2011-05-10 13:38:14|q > 19|9|2011-05-10 13:38:14|r > 20|0|2011-05-10 13:38:14|s > 21|1|2011-05-10 13:38:14|t > 22|2|2011-05-10 13:38:14|u > 23|3|2011-05-10 13:38:14|v > 24|4|2011-05-10 13:38:14|w > 25|5|2011-05-10 13:38:14|x > 26|6|2011-05-10 13:38:14|y > 27|7|2011-05-10 13:38:14|z > > =========================================================================== > > Note that because I'm grabbing the msg_seq value out of the > sqlite_sequence table, there is some chance of minor screw-ups if the > logging system is deeply involved in transactions and check-points > that might invalidate a sequence number. Don't do that. > > >> While I'm on it, if I have an AUTOINCREMENT INTEGER PRIMARY KEY with a >> LOGGER implementation, and the integer reaches it's limit > It won't. It's a 63 bit value**. Even if you're inserting a million > rows per second, it will take almost 300,000 years to run out of > numbers. The only way you'll run out is if a very high value is > manually inserted, throwing off the sequence. > > ** OK, technically it is a 64 bit signed value, but sequences start > at 1 by default, limiting them to half the number domain-- hence 63. > >> will the sqlite database assign "un-used >> primary keys" (previously deleted) to any NEW inserts? > No, not with an AUTOINCREMENT: http://www.sqlite.org/autoinc.html > > "If the table has previously held a row with the largest possible > ROWID, then new INSERTs are not allowed and any attempt to insert > a new row will fail with an SQLITE_FULL error." > > > > -j > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
