On Tue, May 27, 2008 at 4:15 PM, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > >De: [EMAIL PROTECTED] > >Fecha: 27/05/2008 19:56 > > > >It's not supposed to, according to > >http://sqlite.org/lang_createtrigger.html . The syntax > >only allows select, insert, update and delete statements. > > > >What are you trying to achieve? > > I need to handle tables with several million records, on realtime, from > RAM. > One of the fields takes few values (say, company website id), but new ids > are > added from time to time. I would prefer to dynamically create a set of > tables > when a new id shows up, for the improved locality of reference plus reduced > overhead from the website id and its non-unique index. > Okay, this system seems to be screaming "doing something wrong" to me. First: "Handle tables with several million records in realtime" is incredibly vague and ambiguous. First off, I have no idea what constitutes "handling". Updating? Inserting? Selecting? Secondly, I have no idea what constitutes "realtime". How fast is real time? Once per second? One billion per second? CREATE TABLE ttt ( t INTEGER PRIMARY KEY ); > > CREATE TRIGGER ttt_new_trigger AFTER INSERT ON ttt FOR EACH ROW > BEGIN > CREATE TABLE uuu ( u INTEGER PRIMARY KEY ); > END; > This is really nonsensical. You can only have one table named 'uuu' at any given moment. Even if SQLite supported the syntax you're using, that table would be recreated on the first insert to 'ttt' and then subsequent attempts would fail with a 'table already exists' error. One of the fields takes few values (say, company website id), but new ids > are > added from time to time. I would prefer to dynamically create a set of > tables > when a new id shows up, for the improved locality of reference plus reduced > overhead from the website id and its non-unique index. This statement is really confusing me. "New IDs are added from time to time" sounds like "New IDs are added, but not very often" which conflicts with your "realtime" assertion. You say want to dynamically create a set of tables when a new ID shows up, yet your example only tries to add one table. My next quesiton would have been, "Why don't you just pregenerate your tables" except for the "locality of reference" explanation. Unfortunately, this indicates to me that you don't really understand how SQLite works. SQLite groups data in pages -- once data is split across two pages, you can't really assume those two pages are anywhere close to each other in the database file. Throw in filesystem fragmentation and even fewer assumptions can be made. So creating a table at the last minute doesn't mean its data will be in a different area of the file -- that all depends on when the data was added to the database. -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
