Re: [sqlite] database package recommendations?
On 4/7/07, John Stanton <[EMAIL PROTECTED]> wrote: DISAM, a CISAM clone might suit your needs. Thanks John. I'll look into that. :)
Re: [sqlite] database package recommendations?
DISAM, a CISAM clone might suit your needs. Jay Sprenkle wrote: Good morning all, Sorry to spam the sqlite list but I thought this might be the best place to ask. I'm trying to find an (embedded) database package to fill a specific need and wondered if anyone can provide a recommendation? Requirements: * disk based storage * As freaking FAST as possible! (this implies a single file for the database, non sparse files are best) * small footprint * can store binary data up to multi megabyte * must be able to quickly locate groups of keys (or provide in order traversal and seek to a substring of a key) * thread safe is optional, but nice * does not need any kind of query language * low numbers of records (100's - 10,000's) * small keys (<100 bytes) * no locking or concurrency * linux and windows compatibility a big plus I'm considering some of the dbm variants but so far only qdbm comes close. I'm doing timing tests on qdbm now but I believe that design has an undesirable feature for my use. It's very fast generally but occasionally takes several seconds (ouch!) to perform a next key retrieval. I believe the btree node retrieval is at fault. Thanks for your help, Jay - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] last N records
try this: delete from mytable where id not in ( select id from mytable order by id desc limit n ) On 4/7/07, Eric S. Johansson <[EMAIL PROTECTED]> wrote: what is the easiest way to hold on to the last N records and delete all older? I can't figure out the right where expression. -- -- The PixAddixImage Collector suite: http://groups-beta.google.com/group/pixaddix SqliteImporter and SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite Cthulhu Bucks! http://www.cthulhubucks.com
[sqlite] last N records
what is the easiest way to hold on to the last N records and delete all older? I can't figure out the right where expression. --- eric -- Speech-recognition in use. It makes mistakes, I correct some. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] database package recommendations?
Good morning all, Sorry to spam the sqlite list but I thought this might be the best place to ask. I'm trying to find an (embedded) database package to fill a specific need and wondered if anyone can provide a recommendation? Requirements: * disk based storage * As freaking FAST as possible! (this implies a single file for the database, non sparse files are best) * small footprint * can store binary data up to multi megabyte * must be able to quickly locate groups of keys (or provide in order traversal and seek to a substring of a key) * thread safe is optional, but nice * does not need any kind of query language * low numbers of records (100's - 10,000's) * small keys (<100 bytes) * no locking or concurrency * linux and windows compatibility a big plus I'm considering some of the dbm variants but so far only qdbm comes close. I'm doing timing tests on qdbm now but I believe that design has an undesirable feature for my use. It's very fast generally but occasionally takes several seconds (ouch!) to perform a next key retrieval. I believe the btree node retrieval is at fault. Thanks for your help, Jay
Re: [sqlite] Master table with child FTS table
Ok, then I'm out. I don't have an answer for the last one either - however you could do this on a view, which joins both tables. Mike -Ursprüngliche Nachricht- Von: Paul Quinn [mailto:[EMAIL PROTECTED] Gesendet: Samstag, 7. April 2007 17:54 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Master table with child FTS table I did set up a trigger for DELETEs, but even then the fts_table would find a way to have more rows than master after a short while. Some delete's are simply being ignored/failed by the FTS module it seems. I would have liked to also use triggers for INSERTs, but how do you create a trigger when the data to be inserted on the trigger is not part of the 'NEW' reference? -PQ - Original Message - From: "Michael Ruck" <[EMAIL PROTECTED]> To: Sent: Saturday, April 07, 2007 8:35 AM Subject: AW: [sqlite] Master table with child FTS table How about managing fts_table using triggers attached to the master table? That should take care of synchronization issues IMHO. Mike - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Master table with child FTS table
I did set up a trigger for DELETEs, but even then the fts_table would find a way to have more rows than master after a short while. Some delete's are simply being ignored/failed by the FTS module it seems. I would have liked to also use triggers for INSERTs, but how do you create a trigger when the data to be inserted on the trigger is not part of the 'NEW' reference? -PQ - Original Message - From: "Michael Ruck" <[EMAIL PROTECTED]> To: Sent: Saturday, April 07, 2007 8:35 AM Subject: AW: [sqlite] Master table with child FTS table How about managing fts_table using triggers attached to the master table? That should take care of synchronization issues IMHO. Mike - To unsubscribe, send email to [EMAIL PROTECTED] -
AW: [sqlite] Master table with child FTS table
How about managing fts_table using triggers attached to the master table? That should take care of synchronization issues IMHO. Mike -Ursprüngliche Nachricht- Von: Paul Quinn [mailto:[EMAIL PROTECTED] Gesendet: Samstag, 7. April 2007 09:08 An: sqlite-users@sqlite.org Betreff: [sqlite] Master table with child FTS table I have a database setup that creates a master table, and a child 'Full Text Search' (FTS) table that matches it by rowid. My problem is with keeping the FTS table in sync with the master. Let me explain in SQL: setup like so: CREATE TABLE master (a, b); CREATE VIRTUAL TABLE fts_table USING fts2(words); inserts like so: INSERT INTO master (a, b) VALUES (blah, blah); INSERT INTO fts_table (rowid, words) VALUES (last_insert_rowid(), 'some words'); searches like so: SELECT a, b, words FROM master JOIN fts_table ON master.rowid == fts_table.rowid WHERE words MATCH 'word'; In my scenario, I need to do a number of updates and deletes to the master table and the fts_table needs to follow suit. However a number of problems have surfaced: FTS does not work for INSERT OR REPLACE. At least that is what I have found, didn't find any docs on this. So I resort to using delete/insert like so: DELETE FROM fts_table WHERE rowid = ; INSERT INTO fts_table (rowid, words) VALUES (, 'some words'); However, I am finding that this is not reliable. The FTS table will still go out of sync. Row counts will not match after some indeterminant amount of updates. It seems either some DELETE's are failing, or the INSERTs into existing rowid's are failing (silently - the return error is always ok). And after the out of sync problems start occuring, sometimes the FTS goes to pot and the SELECT sql's start failing with SQLITE_ERROR. So, is there a better way to do what I am trying to do? I'm using SQLite 3.3.13, and I've tried both FTS1 and FTS2 (using FTS2 right now). Thanks in advance for any help. -PQ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] in memory databases
On Fri, 2007-04-06 at 13:22 -0700, Mike Johnston wrote: > Hi, > Is it possible to have memory databases located at a specific memory > address? I have a battery backed memory I'd like to store specific > information apart from the main database. Not really possible at the moment. An in-memory database will be trashed when the database connection is closed, even if the memory it was using was persistent. I'm guessing that's probably not what you're after, right? Maybe your operating system can mount the memory as a file-system and then you can create a database in that file-system? Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Master table with child FTS table
I have a database setup that creates a master table, and a child 'Full Text Search' (FTS) table that matches it by rowid. My problem is with keeping the FTS table in sync with the master. Let me explain in SQL: setup like so: CREATE TABLE master (a, b); CREATE VIRTUAL TABLE fts_table USING fts2(words); inserts like so: INSERT INTO master (a, b) VALUES (blah, blah); INSERT INTO fts_table (rowid, words) VALUES (last_insert_rowid(), 'some words'); searches like so: SELECT a, b, words FROM master JOIN fts_table ON master.rowid == fts_table.rowid WHERE words MATCH 'word'; In my scenario, I need to do a number of updates and deletes to the master table and the fts_table needs to follow suit. However a number of problems have surfaced: FTS does not work for INSERT OR REPLACE. At least that is what I have found, didn't find any docs on this. So I resort to using delete/insert like so: DELETE FROM fts_table WHERE rowid = ; INSERT INTO fts_table (rowid, words) VALUES (, 'some words'); However, I am finding that this is not reliable. The FTS table will still go out of sync. Row counts will not match after some indeterminant amount of updates. It seems either some DELETE's are failing, or the INSERTs into existing rowid's are failing (silently - the return error is always ok). And after the out of sync problems start occuring, sometimes the FTS goes to pot and the SELECT sql's start failing with SQLITE_ERROR. So, is there a better way to do what I am trying to do? I'm using SQLite 3.3.13, and I've tried both FTS1 and FTS2 (using FTS2 right now). Thanks in advance for any help. -PQ
