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 -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
