Hi,
I am new to SQL and SQLite, so please excuse me if I appear thick at
times.
I have an application that generates data from a moving vehicle. At a
position there may be 10s of pairs of data. Position is given as an
integer, and the data pairs are an integer and a float (real). So I set
up a database like this (in Tcl);
db eval {CREATE TABLE position(\
position_id INTEGER PRIMARY_KEY, \
odo INTEGER, \
time CURRENT_TIMESTAMP);}
db eval {CREATE TABLE data(\
data_id INTEGER PRIMARY_KEY, \
position_id INTEGER, \
x INTEGER, \
y REAL);}
So far so good. Now I have a heap of stored data in a binary file, that
I use to insert into the tables, with statements like;
db eval {BEGIN;}
# loop reading in file...
while {..} {
#when a new odo value is read...
db eval {INSERT INTO position(odo) values($odo);}
set pos_id [db eval {SELECT last_insert_rowid();}]
#for every data pair
db eval {INSERT INTO data(position_id, x, y) values($pos_id, $x, $y);}
}
db eval {COMMIT;}
There are no errors returned. Now I try a couple of queries, which
return data, but not quite what I expect.
#This kinda works as expected, but position_id and time columns are not
#poulated. Why not?
db eval {SELECT * FROM position WHERE ROWID BETWEEN 100 AND 101;}
#This does not work, returns nothing. I thought (after reading the
#SQLite doco) that position_id would be populated with ROWID...
db eval {SELECT * FROM position WHERE position_id BETWEEN 100 AND 101;}
#This kinda works, but data_id is not populated...
db eval {SELECT * FROM data where position_id BETWEEN 100 AND 101;}
#A more complicated query...runs quite slowly. How can this be sped up?
db eval {SELECT position.odo, data.x, data.y from position, data WHERE
position.odo BETWEEN 10000 AND 10020;}
Is SQLite going to be able to handle, say, 2,000,000 data pairs, and say
60,000 positions, efficiently and quickly?
How can I help SQLite perform queries like the last above, as fast as
possible?
Regards,
James.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
