Hi All
I have an extremely simple little database with one table. I have an embedded app that keeps the database open forever and this table is manipulated by a single thread. I need to make sure this table doesn't get too big - no more than N rows. There is a Start_Time column which holds the UNIX timestamp (as a long integer) at which an event occurs. The user can manually delete rows by specifying a "cutoff" timestamp, at which point I delete rows with lesser Start_Time. Rows are never deleted in the middle. My plan is to count the number of rows when the thread starts and then increment the count after each insert. Inserts can happen very frequently - as many as a few hundred per second, maybe, or as fast as my code and SQLite can handle them. When the count exceeds N + M (I don't want to do an insert followed by a delete of a single row when the threshold is reached, so I will let the table get a bit bigger than N rows), I want to delete all but the latest N rows. I am hoping the rowid is sequential. Then I can simply get the biggest rowid (using sqlite3_last_insert_rowid), subtract N from it and delete rows where rowid < last_rowid - N. Can anyone confirm the rowid is guaranteed sequential in my scenario? Are the any gotchas I should know about? I suspect this is a fairly common use case, so someone has likely done something like this. I am open to a better approach if anyone has one to offer. Thanks, John _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
