Raphaël KINDT wrote: > > All events come sequentially (because it's a time line). > I can have more than one event during the same time but I use 1 different > bit to identify each event types. > > I think it's important to use this sequential constraint to do the delete > request. > But SQLite3 use a B-Tree algorithm... > When I delete some rows it's always for oldest events (before a specific > time = 'Ts'). > I mean I delete all rows before 'Ts' (like a fifo...) > I don't want to check all my event table but only the oldest events (first > rows). > When I find a time greater than 'Ts' I can stop the delete operation. > > Do you think It's better to use one more table for optimization? >
A normal index on time will allow SQLite to locate the rows with time less than Ts very quickly. I don't see any need for another table to optimize this function. > For example, creating a "summarised" table which could, in 1 row, summarise > all the events (detailed in the general table) that have happened in a time > lapse (= time between > two rows). This "summarised" table could be created with the following > request : > > CREATE TABLE bloc_events( > bloc_id INTEGER NOT NULL PRIMARY KEY, > bloc_start_time REAL, > events_resume BLOB > ); > > And now my event table looks like this: > > CREATE TABLE events( > time REAL NOT NULL PRIMARY KEY, > event BLOB, > bloc_id INTEGER > ); > > 'events_resume' is a logical 'OR' result of all event types detected in the > correspondent event groups in the events table. > 'bloc_start_time' is the start time of a new group of events. > The difference between two successive bloc_events rows is the group > precision (maybe 10 minutes) > To know the oldest event (bloc_id) to delete I check bloc_events table > first. > Then I delete row on events table with the same bloc_id found on bloc_events > table. > > How to create a FOREIGN KEY with SQLite3? > SQLite allows you to use foreign keys for joins, but it does not enforce any referential integrity when rows are deleted. There are triggers you can add to your table to do this if need be, but it is often just a easy to do it in your application. See http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers for more info. HTH Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
