[sqlite] autoincrement and fts2?
Is there any way to use a INTEGER PRIMARY KEY AUTOINCREMENT on a table that has FTS2? Specifying it in the obvious manner looks like it works, but the column just ends up with nulls in it. - a -- PGP/GPG: 5C9F F366 C9CF 2145 E770 B1B8 EFB1 462D A146 C380 - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Does Transaction object roll back automatically on exceptions?
On Mon, 2007-07-16 at 16:47 -0400, Ahmed Sulaiman wrote: > Hi all, > > Is it necessary to run a "SELECT" command in between a transaction? I > have few places in my code where I have a command that reads some data > from a table, and I wonder if I should begin/commit a transaction? Is > there any performance issues if I did or didn't do that? It's not necessary, and unlikely to have a noticeable effect on performance. This decision should be made based on the logical requirements of the system (i.e. is it important that SQL statements executed after the SELECT operate on a consistent snapshot of the database). Dan. > Regards > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] autoincrement and fts2?
On 7/16/07, Adam Megacz <[EMAIL PROTECTED]> wrote: Is there any way to use a INTEGER PRIMARY KEY AUTOINCREMENT on a table that has FTS2? Specifying it in the obvious manner looks like it works, but the column just ends up with nulls in it. In fts tables all columns other than rowid are of type TEXT. It doesn't matter what you put in the type, they will be of type TEXT. The rowid is the standard SQLite rowid, so it does provide an INTEGER PRIMARY KEY AUTOINCREMENT column. The standard way to have non-TEXT information associated with rows in an fts table would be a separate table which joins with the fts table on rowid. -scott - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] autoincrement and fts2?
>The rowid is the standard SQLite rowid, so it does provide an INTEGER >PRIMARY KEY AUTOINCREMENT column. > >The standard way to have non-TEXT information associated with rows in >an fts table would be a separate table which joins with the fts table >on rowid. I have not tested this, but if the FTS2 rowid is the standard SQLite rowid, I believe that it will be affected by VACUUM change of rowids recently reported on this list? If so, could this be fixed? Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Finding record position
Hi Assume I have a simple table for a phone directory - names, addresses, and phone numbers etc. Then assume this is a vast table with many millions of records, and that there are indices on name, phone number, etc. Then assume I'm writing an app that displays the entire directory in a window, ordered by name/number etc using a scrollbar to move up and down. So I might have: CREATE TABLE pb (id INT, name VARCHAR(40),...); CREATE INDEX pb_idx_name ON pb(name); .. Not a problem. I can use "SELECT * FROM pb ORDER BY name LIMIT x,y" etc as my user pages up and down the list, or drags a scrollbar. Next, assume the user wants to jump to a specific record in the list, or to (say) the first entry for a specific name. How do you do this with sqlite, without forcing the the app to fetch every record and then do a manual comparison in the app? E.g. my user wants the app's window to 'jump' to the first record with name 'John Smith'. I.e what my windowed app wants to do is to find the 'position' x of the first record in the name index that matches 'John Smith', then use that x to both (i) set its vertical scrollbar position, and (ii) to then issue something like "SELECT * FROM pb ORDER BY name LIMIT x,20", whilst retaining the illusion that the window contains the whole phone directory. I do not want to do "SELECT * FROM pb WHERE name LIKE 'John Smith' ORDER BY name" because that will just return the subset of matching records. It seems to me that I need an interface to sqlite that says 'given a specific index, and a tuple of values for the columns upon which that index is constructed, how many records appear before and after the index position given by that tuple' Any ideas, or am I missing something obvious? Sorry if this is a dumb question. Thanks -- No virus found in this outgoing message. Checked by AVG. Version: 7.5.476 / Virus Database: 269.10.6/902 - Release Date: 15/07/2007 14:21 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Finding record position
Colin Manning wrote: Hi Assume I have a simple table for a phone directory - names, addresses, and phone numbers etc. Next, assume the user wants to jump to a specific record in the list, or to (say) the first entry for a specific name. How do you do this with sqlite, without forcing the the app to fetch every record and then do a manual comparison in the app? First thing that comes to my mind is to do select count(*) from pb where name <= 'John Doe' -veikko - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Finding record position
Colin Manning <[EMAIL PROTECTED]> wrote: Then assume I'm writing an app that displays the entire directory in a window, ordered by name/number etc using a scrollbar to move up and down. So I might have: CREATE TABLE pb (id INT, name VARCHAR(40),...); CREATE INDEX pb_idx_name ON pb(name); .. Not a problem. I can use "SELECT * FROM pb ORDER BY name LIMIT x,y" etc as my user pages up and down the list, or drags a scrollbar. Next, assume the user wants to jump to a specific record in the list, or to (say) the first entry for a specific name. How do you do this with sqlite, without forcing the the app to fetch every record and then do a manual comparison in the app? select * from pb where name>='Specific Name' ORDER BY name LIMIT x,y; E.g. my user wants the app's window to 'jump' to the first record with name 'John Smith'. I.e what my windowed app wants to do is to find the 'position' x of the first record in the name index that matches 'John Smith', select count(*) from pb where name < 'John Smith'; Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Finding record position
First, think about using FTS2 for your text lookup instead of using LIKE and having to perform row scans. Think about changing your logic to just read from the database instead of blowing out your memory by holding the database on disk, then in disk cache and again in application memory. If you want to show a table of closely related entries, use the LIMIT feature on SELECT. Colin Manning wrote: Hi Assume I have a simple table for a phone directory - names, addresses, and phone numbers etc. Then assume this is a vast table with many millions of records, and that there are indices on name, phone number, etc. Then assume I'm writing an app that displays the entire directory in a window, ordered by name/number etc using a scrollbar to move up and down. So I might have: CREATE TABLE pb (id INT, name VARCHAR(40),...); CREATE INDEX pb_idx_name ON pb(name); .. Not a problem. I can use "SELECT * FROM pb ORDER BY name LIMIT x,y" etc as my user pages up and down the list, or drags a scrollbar. Next, assume the user wants to jump to a specific record in the list, or to (say) the first entry for a specific name. How do you do this with sqlite, without forcing the the app to fetch every record and then do a manual comparison in the app? E.g. my user wants the app's window to 'jump' to the first record with name 'John Smith'. I.e what my windowed app wants to do is to find the 'position' x of the first record in the name index that matches 'John Smith', then use that x to both (i) set its vertical scrollbar position, and (ii) to then issue something like "SELECT * FROM pb ORDER BY name LIMIT x,20", whilst retaining the illusion that the window contains the whole phone directory. I do not want to do "SELECT * FROM pb WHERE name LIKE 'John Smith' ORDER BY name" because that will just return the subset of matching records. It seems to me that I need an interface to sqlite that says 'given a specific index, and a tuple of values for the columns upon which that index is constructed, how many records appear before and after the index position given by that tuple' Any ideas, or am I missing something obvious? Sorry if this is a dumb question. Thanks - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Finding record position
On 7/17/07 5:13 PM, "Colin Manning" <[EMAIL PROTECTED]> wrote: > > Not a problem. I can use "SELECT * FROM pb ORDER BY name LIMIT x,y" etc as > my user pages up and down the list, or drags a scrollbar. > > Next, assume the user wants to jump to a specific record in the list, or to > (say) the first entry for a specific name. > > How do you do this with sqlite, without forcing the the app to fetch every > record and then do a manual comparison in the app? SELECT MIN(rowid) FROM pb ORDER WHERE name LIKE 'John Smith' Will return first index of the row matching the given condition. Hope this answers your question. Cheers, Bharath Booshan L. --- Robosoft Technologies - Come home to Technology Disclaimer: This email may contain confidential material. If you were not an intended recipient, please notify the sender and delete all copies. Emails to and from our network may be logged and monitored. This email and its attachments are scanned for virus by our scanners and are believed to be safe. However, no warranty is given that this email is free of malicious content or virus. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Finding record position
Colin Manning <[EMAIL PROTECTED]> wrote: > Hi > > Assume I have a simple table for a phone directory - names, addresses, and > phone numbers etc. > > Then assume this is a vast table with many millions of records, and that > there are indices on name, phone number, etc. > > Then assume I'm writing an app that displays the entire directory in a > window, ordered by name/number etc using a scrollbar to move up and down. > [...] > > Not a problem. I can use "SELECT * FROM pb ORDER BY name LIMIT x,y" etc as > my user pages up and down the list, or drags a scrollbar. This is not a good way of solving the problem. The OFFSET clause works by running the query from the beginning and discarding the first x records. So when you get down toward the bottom of your millions-long table, the SELECTs are going to start getting really slow. A better approach is to remember the name and rowid of the record at the top and bottom of your display window. Call these values name_top, rowid_top, name_btm, and rowid_btm. To scroll down to subsequent entries, do this: SELECT rowid, * FROM pb WHERE name>=$name_btm ORDER BY name ASC. If the same name occurs multiple times, you might get some duplicates at the beginning of your query. Use the rowid to resolve duplicates. Run the query until you have as many records as you need to fill your screen then cancel the query using sqlite3_reset(). To scroll up: SELECT rowid, * FROM pb WHERE name<=$name_top ORDER BY name DESC. Once again, you may get duplicates at the beginning of the query, which you should skip over. > > Next, assume the user wants to jump to a specific record in the list, or to > (say) the first entry for a specific name. > Just set name_btm to the name you are looking for and set rowid_btm to 0. Then run the first query. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Does Transaction object roll back automatically on exceptions?
Note that by default the ADO.NET wrapper executes transactions in immediate mode which is not desirable for read-only data. To start a deferred transaction, you need to use the SQLite.NET-specific overload BeginTransaction(true) which is not available if using the DbProvider object model. Best regards, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Ahmed Sulaiman [mailto:[EMAIL PROTECTED] Sent: Monday, July 16, 2007 4:47 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Does Transaction object roll back automatically on exceptions? Hi all, Is it necessary to run a "SELECT" command in between a transaction? I have few places in my code where I have a command that reads some data from a table, and I wonder if I should begin/commit a transaction? Is there any performance issues if I did or didn't do that? Regards - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Finding record position
select count(*) from pb where name < 'John Smith'; Thx... duh guess I should have thought of that. -- No virus found in this outgoing message. Checked by AVG. Version: 7.5.476 / Virus Database: 269.10.6/902 - Release Date: 15/07/2007 14:21 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] autoincrement and fts2?
>>The standard way to have non-TEXT information associated with rows in
>>an fts table would be a separate table which joins with the fts table
>>on rowid.
>
>I have not tested this, but if the FTS2 rowid is the standard SQLite rowid, I
>believe that it will be affected by VACUUM change of rowids recently reported
>on this list? If so, could this be fixed?
VACUUM does modify FTS2 rowids. Here is the test:
drop table if exists a;
create virtual table a using fts2 (t);
insert into a (t) values ('one');
insert into a (t) values ('two');
insert into a (t) values ('three');
select rowid, * from a;
delete from a where t = 'two';
vacuum;
select rowid, * from a;
Unfortunately there is no workaround since table a is auto-generated by the
FTS2 module. Created ticket #2510.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Re: UPDATE during SELECT
Larry Lewis wrote: If I am stepping through the results of a SELECT and want to UPDATE values in the currently selected row prior to completion of the SELECT query, will this work? Yes, in the recent enough SQLite version. What if there is already a pending writer lock on the database from a different thread? SQLite will detect the deadlock, and fail your UPDATE statement with SQLITE_BUSY error. Your only option at this point would be to reset the SELECT statement and finish the transaction this query was part of (if any). Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] autoincrement and fts2?
WTH! Wow, this is a very unexpected change. I must have not been
paying attention at some point.
-scott
On 7/17/07, Ralf Junker <[EMAIL PROTECTED]> wrote:
>>The standard way to have non-TEXT information associated with rows in
>>an fts table would be a separate table which joins with the fts table
>>on rowid.
>
>I have not tested this, but if the FTS2 rowid is the standard SQLite rowid, I
believe that it will be affected by VACUUM change of rowids recently reported on
this list? If so, could this be fixed?
VACUUM does modify FTS2 rowids. Here is the test:
drop table if exists a;
create virtual table a using fts2 (t);
insert into a (t) values ('one');
insert into a (t) values ('two');
insert into a (t) values ('three');
select rowid, * from a;
delete from a where t = 'two';
vacuum;
select rowid, * from a;
Unfortunately there is no workaround since table a is auto-generated by the
FTS2 module. Created ticket #2510.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] autoincrement and fts2?
I've updated the bug with an example of how this breaks fts tables
(fts1 or fts2). I'm thinking on the problem.
http://www.sqlite.org/cvstrac/tktview?tn=2510
Summary: In sqlite 3.4, running vacuum with fts2 or fts1 tables can
break the table if you've done any deletions.
I'll try to add more constraints to the summary today,
-scott
On 7/17/07, Scott Hess <[EMAIL PROTECTED]> wrote:
WTH! Wow, this is a very unexpected change. I must have not been
paying attention at some point.
-scott
On 7/17/07, Ralf Junker <[EMAIL PROTECTED]> wrote:
>
> >>The standard way to have non-TEXT information associated with rows in
> >>an fts table would be a separate table which joins with the fts table
> >>on rowid.
> >
> >I have not tested this, but if the FTS2 rowid is the standard SQLite rowid,
I believe that it will be affected by VACUUM change of rowids recently reported on
this list? If so, could this be fixed?
>
> VACUUM does modify FTS2 rowids. Here is the test:
>
> drop table if exists a;
>
> create virtual table a using fts2 (t);
>
> insert into a (t) values ('one');
> insert into a (t) values ('two');
> insert into a (t) values ('three');
>
> select rowid, * from a;
>
> delete from a where t = 'two';
> vacuum;
>
> select rowid, * from a;
>
> Unfortunately there is no workaround since table a is auto-generated by the
FTS2 module. Created ticket #2510.
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Enum user defined functions from code
Hi all Is there any way to get the list of registered user defined functions from code or SQL, and their parameters? I need to provide our users with some GUI's to generate SQL and I would like to make the user defined functions available, I know as it is user defined functions I should know what they are, but its simply a case of twice the work, adding the function and then coding it into the gui, vs if possible just add them once and the gui updates dynamically, and then obviously the possibility of the two being out of sync. Thanks.
RE: [sqlite] Sharing an in-memory database between applications
Hi Rob, This maybe a dumb idea but have you tried a software RAM disk? Or maybe even a physical RAM disk. http://www.tomshardware.com/2005/09/07/can_gigabyte/index.html Kind Regards, Lodewijk -Original Message- From: Rob Richardson [mailto:[EMAIL PROTECTED] Sent: Friday, July 13, 2007 5:35 PM To: sqlite-users@sqlite.org Subject: [sqlite] Sharing an in-memory database between applications Greetings! We are using an SQLite database to store process data that will eventually be displayed on a graph. The database design is simple, including only six tables, but the table containing the data points for the graph could contain a few million records. By using the simplest possible query and asking for the bare minimum of data I need at any one point, I've managed to get the time to display the graph down from a few minutes to about 15 seconds for a sample database with 1.3 million records. But I'm wondering if I can use an in-memory database to improve this dramatically. The data is collected by a Windows service that collects data and adds it to the database once a minute. If the service would also store the data into an in-memory database, and the graphing application could somehow read the same database, I ought to be able to get unbelievable speed. Is this feasible? If so, how would I set it up? Another possibility might be to read the entire database from disk into an in-memory database when the graphing application starts up, if there's a way to do that that is much faster than a set of INSERT INTO newtable SELECT * FROM oldtable (or whatever -- you get the idea) statements. Thank you very much. Rob Richardson RAD-CON INC. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] autoincrement and fts2?
Scott, I've attached a possible patch to the ticket.
It seems to work, but I may have missed some something.
Tell me what you think.
--- Scott Hess <[EMAIL PROTECTED]> wrote:
> I've updated the bug with an example of how this breaks fts tables
> (fts1 or fts2). I'm thinking on the problem.
> http://www.sqlite.org/cvstrac/tktview?tn=2510
>
> Summary: In sqlite 3.4, running vacuum with fts2 or fts1 tables can
> break the table if you've done any deletions.
>
> I'll try to add more constraints to the summary today,
>
> -scott
>
>
> On 7/17/07, Scott Hess <[EMAIL PROTECTED]> wrote:
> > WTH! Wow, this is a very unexpected change. I must have not been
> > paying attention at some point.
> >
> > -scott
> >
> >
> > On 7/17/07, Ralf Junker <[EMAIL PROTECTED]> wrote:
> > >
> > > >>The standard way to have non-TEXT information associated with rows in
> > > >>an fts table would be a separate table which joins with the fts table
> > > >>on rowid.
> > > >
> > > >I have not tested this, but if the FTS2 rowid is the standard SQLite
> > > >rowid, I believe that
> it will be affected by VACUUM change of rowids recently reported on this
> list? If so, could this
> be fixed?
> > >
> > > VACUUM does modify FTS2 rowids. Here is the test:
> > >
> > > drop table if exists a;
> > >
> > > create virtual table a using fts2 (t);
> > >
> > > insert into a (t) values ('one');
> > > insert into a (t) values ('two');
> > > insert into a (t) values ('three');
> > >
> > > select rowid, * from a;
> > >
> > > delete from a where t = 'two';
> > > vacuum;
> > >
> > > select rowid, * from a;
> > >
> > > Unfortunately there is no workaround since table a is auto-generated by
> > > the FTS2 module.
> Created ticket #2510.
> > >
> > >
> > > -
> > > To unsubscribe, send email to [EMAIL PROTECTED]
> > > -
> > >
> > >
> >
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
Looking for a deal? Find great prices on flights and hotels with Yahoo!
FareChase.
http://farechase.yahoo.com/
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Enum user defined functions from code
"Andre du Plessis" <[EMAIL PROTECTED]> wrote: > > Is there any way to get the list of registered user defined functions > from code or SQL, and their parameters? > No. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] autoincrement and fts2?
You can, and I'm working on a patch to do this to see how it might look.
There's the question of how to handle existing tables.
-scott
On 7/17/07, Chris Wedgwood <[EMAIL PROTECTED]> wrote:
On Tue, Jul 17, 2007 at 09:37:43AM -0700, Scott Hess wrote:
> Summary: In sqlite 3.4, running vacuum with fts2 or fts1 tables can
> break the table if you've done any deletions.
Can you not not have the fts[12] code explicitly mark the rowid
columns as integer primary key in the schema? Then vacuum will leave
them alone.
sqlite> create table t2 ( oid integer primary key, c1 text );
sqlite> insert into t2(c1) values ('one');
sqlite> insert into t2(c1) values ('two');
sqlite> select oid,c1 from t2;
1|one
2|two
sqlite> delete from t2 where oid=1;
sqlite> vacuum;
sqlite> select oid,c1 from t2;
2|two
I opened a bug about this subtle difference a while ago (I think it's
fine, it should just be documented).
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Re: Enum user defined functions from code
Andre du Plessis <[EMAIL PROTECTED]> wrote: Is there any way to get the list of registered user defined functions from code or SQL, and their parameters? None that I know of. Note that you don't specify the number of parameters when registering a custom function, so SQLite doesn't know it either (some functions are actually designed to work with variable number of parameters). Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: UPDATE during SELECT
Igor,
Thanks for your help. I've tested the first case successfully.
For a multi-threaded application using an in-memory database (":memory:"),
would you recommend:
a) an external mutex to synchronize exclusive access to the database --
probably the safest
b) an external read-write lock to allow concurrent reads but only one write
(parallel of SQLite locking as I understand it)
c) rely on SQLite locking and handle SQLITE_BUSY cases as described below
Both SELECTs and UPDATEs will be occurring from multiple threads approximately
10 times/second, and my original question below will be quite common (SELECT a
group of records and make UPDATEs as I step through them).
Larry
- Original Message
Larry Lewis wrote:
> If I am stepping through the results of a SELECT and want to UPDATE
> values in the currently selected row prior to completion of the
> SELECT query, will this work?
Yes, in the recent enough SQLite version.
> What if there is already a pending writer lock on the database from a
> different thread?
SQLite will detect the deadlock, and fail your UPDATE statement with
SQLITE_BUSY error. Your only option at this point would be to reset the
SELECT statement and finish the transaction this query was part of (if
any).
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] malloc failure in sqlite3_prepare
All: I am writing a program that reads large blob data (~15 MB) from a sqlite database, then writes the data out to the sound card. After this time, some results are calculated and inserted into the same database. In my code, I have 1 database handle from which I do a prepare to extract the waveform data, then I use the same handle to do the result insert... I get the following error results from a backtrace. I copy the data I get back from sqlite to my own private buffer. The odd thing is that some runs this works just fine, and some runs it does not... However it is the same blob data each time. Any assistance would be appreciated, I can supply more information upon request. (gdb) bt #0 0xb7d4899f in ?? () from /lib/tls/i686/cmov/libc.so.6 #1 0x0001 in ?? () #2 0xb7f94ea3 in sqlite3MallocFailed () from /home/enguser/libsqlite3.so.0 #3 0xb7d4ae38 in ?? () from /lib/tls/i686/cmov/libc.so.6 #4 0x0963cf85 in ?? () #5 0xb7fae418 in ?? () from /lib/ld-linux.so.2 #6 0xbfdcc834 in ?? () #7 0xb7fb6b79 in ?? () from /lib/ld-linux.so.2 #8 0xb7d4c60e in malloc () from /lib/tls/i686/cmov/libc.so.6 #9 0xb7f8437f in sqlite3GenericMalloc () from /home/enguser/libsqlite3.so.0 #10 0xb7f94f98 in sqlite3MallocRaw () from /home/enguser/libsqlite3.so.0 #11 0xb7f950fe in sqlite3Malloc () from /home/enguser/libsqlite3.so.0 #12 0xb7f951cc in sqlite3MallocX () from /home/enguser/libsqlite3.so.0 #13 0xb7f88785 in sqlite3ParserAlloc () from /home/enguser/libsqlite3.so.0 #14 0xb7f923c8 in sqlite3RunParser () from /home/enguser/libsqlite3.so.0 #15 0xb7f8c087 in sqlite3Prepare () from /home/enguser/libsqlite3.so.0 #16 0xb7f8c42b in sqlite3_prepare_v2 () - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] autoincrement and fts2?
[Forwarding gist of an offline conversation with Joe.]
Looks about like what my patch looks like. Needs to additionally
handle %_segments.rowid (same problem, but you need to insert more
than 16 docs to see it).
I'm also tossing in some test cases. My patch should be ready this
afternoon. I'm not going to check it in, yet, because there's the
question of backwards/forwards compatibility to be addressed.
-scott
On 7/17/07, Joe Wilson <[EMAIL PROTECTED]> wrote:
Scott, I've attached a possible patch to the ticket.
It seems to work, but I may have missed some something.
Tell me what you think.
--- Scott Hess <[EMAIL PROTECTED]> wrote:
> I've updated the bug with an example of how this breaks fts tables
> (fts1 or fts2). I'm thinking on the problem.
> http://www.sqlite.org/cvstrac/tktview?tn=2510
>
> Summary: In sqlite 3.4, running vacuum with fts2 or fts1 tables can
> break the table if you've done any deletions.
>
> I'll try to add more constraints to the summary today,
>
> -scott
>
>
> On 7/17/07, Scott Hess <[EMAIL PROTECTED]> wrote:
> > WTH! Wow, this is a very unexpected change. I must have not been
> > paying attention at some point.
> >
> > -scott
> >
> >
> > On 7/17/07, Ralf Junker <[EMAIL PROTECTED]> wrote:
> > >
> > > >>The standard way to have non-TEXT information associated with rows in
> > > >>an fts table would be a separate table which joins with the fts table
> > > >>on rowid.
> > > >
> > > >I have not tested this, but if the FTS2 rowid is the standard SQLite
rowid, I believe that
> it will be affected by VACUUM change of rowids recently reported on this
list? If so, could this
> be fixed?
> > >
> > > VACUUM does modify FTS2 rowids. Here is the test:
> > >
> > > drop table if exists a;
> > >
> > > create virtual table a using fts2 (t);
> > >
> > > insert into a (t) values ('one');
> > > insert into a (t) values ('two');
> > > insert into a (t) values ('three');
> > >
> > > select rowid, * from a;
> > >
> > > delete from a where t = 'two';
> > > vacuum;
> > >
> > > select rowid, * from a;
> > >
> > > Unfortunately there is no workaround since table a is auto-generated by
the FTS2 module.
> Created ticket #2510.
> > >
> > >
> > >
-
> > > To unsubscribe, send email to [EMAIL PROTECTED]
> > >
-
> > >
> > >
> >
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
Looking for a deal? Find great prices on flights and hotels with Yahoo!
FareChase.
http://farechase.yahoo.com/
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Re: Re: UPDATE during SELECT
Larry Lewis wrote:
For a multi-threaded application using an in-memory database
(":memory:"), would you recommend: a) an external mutex to
synchronize exclusive access to the database -- probably the safest
b) an external read-write lock to allow concurrent reads but only
one write (parallel of SQLite locking as I understand it) c) rely on
SQLite locking and handle SQLITE_BUSY cases as described below
c) is not an option, since you cannot have multiple connections to the
same in-memory database. SQLite locking only works to arbitrate between
multiple connections to the same database.
I'm not sure how safe it is to use the same SQLite connection from
multiple threads. In the past, this has been explicitly prohibited.
Restrictions have been relaxed in recent versions, but I must admit I've
lost track of the state of the art in this area. Hopefully somebody more
knowlegeable will chime in at this point and describe what exactly is
and is not allowed nowadays.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: UPDATE during SELECT
We implement pthread read/write locks around Sqlite in a multi-threaded
environment and disable the fcntl file locking and ignore busy logic.
It has the downside of losing some concurrency compared to the Sqlite
pending and reserved lock strategy, but we have not suffered a
performance hit yet.
Using a mutex alone restricts concurrent reading, not a good idea.
Larry Lewis wrote:
Igor,
Thanks for your help. I've tested the first case successfully.
For a multi-threaded application using an in-memory database (":memory:"),
would you recommend:
a) an external mutex to synchronize exclusive access to the database --
probably the safest
b) an external read-write lock to allow concurrent reads but only one write
(parallel of SQLite locking as I understand it)
c) rely on SQLite locking and handle SQLITE_BUSY cases as described below
Both SELECTs and UPDATEs will be occurring from multiple threads approximately
10 times/second, and my original question below will be quite common (SELECT a
group of records and make UPDATEs as I step through them).
Larry
- Original Message
Larry Lewis wrote:
If I am stepping through the results of a SELECT and want to UPDATE
values in the currently selected row prior to completion of the
SELECT query, will this work?
Yes, in the recent enough SQLite version.
What if there is already a pending writer lock on the database from a
different thread?
SQLite will detect the deadlock, and fail your UPDATE statement with
SQLITE_BUSY error. Your only option at this point would be to reset the
SELECT statement and finish the transaction this query was part of (if
any).
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Replicating table data from sqlite to ms sql server
Hi maitong, On 7/16/07, maitong uy <[EMAIL PROTECTED]> wrote: The scenario would be the sqlite database is managed using CGI C, resides in Linux environment, and accessed through the web. Then the sql server would be replicating whatever changes would occur in the sqlite database (both sqlite and sql server have the same tables). This will also happen vice versa wherein any change in sql server will be replicated in the sqlite. Sql server is managed using ASP and resides in windows server 2003. Assuming you have both the servers in one room/location/place, A listener daemon on Linux to listen for the replication-specific messages from the SQL Server machine and a listener service on Windows to listen for the replication messages from Sqlite. You might want to use the "Java Service Wrapper" to develop a single listener software that does the kind of replication you are talking about on both the servers (Linux as well as Windows) using JDBC and install it as a service (on Linux as well as on Windows). You will then have to write separate pieces of code on Linux & Windows to communicate with the service hosted on the respective OS using the data to be replicated as parameter which in turn will communicate with the corresponding service on the other OS (send the data to another machine/OS for replication) and the corresponding listener service on that machine will take the data and update the local database. You can also try to write the database update code transparently - it will be a good exercise. You might also want to use some kind of a design pattern to isolate the database updating code in a separate Java object (based on whether you want your Java code to update an Sqlite database or an SQL Server one) so that you can select run the appropriate (single piece of) code dynamically at run time on both the machines. You might also want to look at "Microsoft Windows Services for Unix" - may be that's what you need. -- Best regards, Asif - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Interrupt SQLite
Is it somehow possible to interrupt an ongoing INSERT operation? I made a mistake in an index and now got into a very long process that I would like to stop. I am running this from VBA via the dll from Olaf Schmidt, dhSQLite.dll. I don't want to kill Excel as I would lose some work. Thanks for any advice. RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Interrupt SQLite
"RB Smissaert" <[EMAIL PROTECTED]> wrote: > Is it somehow possible to interrupt an ongoing INSERT operation? I made a > mistake in an index and now got into a very long process that I would like > to stop. I am running this from VBA via the dll from Olaf Schmidt, > dhSQLite.dll. I don't want to kill Excel as I would lose some work. > Thanks for any advice. > Try making the journal file read-only. That should stop it. And force a rollback. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: UPDATE during SELECT
To summarize, if compiling with the THREADSAFE macro set to 1 and sharing a
memory database connection between multiple threads, SQLite will handle all
read/write synchronization without providing any external locking. Is that
basically what you're saying, John?
Are there still cases where an SQLITE_BUSY will be returned due to potential
deadlocks as mentioned by Igor (for example, thread 1 is reading, thread 2 is
waiting for write, thread 1 tries to upgrade to write without relinquishing
read)? I'm confused by the statement "ignore busy logic."
Thanks for your help.
Larry
- Original Message
We implement pthread read/write locks around Sqlite in a multi-threaded
environment and disable the fcntl file locking and ignore busy logic.
It has the downside of losing some concurrency compared to the Sqlite
pending and reserved lock strategy, but we have not suffered a
performance hit yet.
Using a mutex alone restricts concurrent reading, not a good idea.
Larry Lewis wrote:
> Igor,
>
> Thanks for your help. I've tested the first case successfully.
>
> For a multi-threaded application using an in-memory database (":memory:"),
> would you recommend:
> a) an external mutex to synchronize exclusive access to the database --
> probably the safest
> b) an external read-write lock to allow concurrent reads but only one write
> (parallel of SQLite locking as I understand it)
> c) rely on SQLite locking and handle SQLITE_BUSY cases as described below
>
> Both SELECTs and UPDATEs will be occurring from multiple threads
> approximately 10 times/second, and my original question below will be quite
> common (SELECT a group of records and make UPDATEs as I step through them).
>
> Larry
>
> - Original Message
> Larry Lewis wrote:
>
>>If I am stepping through the results of a SELECT and want to UPDATE
>>values in the currently selected row prior to completion of the
>>SELECT query, will this work?
>
>
> Yes, in the recent enough SQLite version.
>
>
>>What if there is already a pending writer lock on the database from a
>>different thread?
>
>
> SQLite will detect the deadlock, and fail your UPDATE statement with
> SQLITE_BUSY error. Your only option at this point would be to reset the
> SELECT statement and finish the transaction this query was part of (if
> any).
>
> Igor Tandetnik
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] "Library routine called out of sequence" and user-defined aggregates
I'm getting "Library routine called out of sequence" when I try to execute an
insertion inside an aggregate that I have created myself. Here's a sample c
program:
#include
#include
void MyAggregateFuncStep(sqlite3_context* context,int argc,sqlite3_value**
argv) {
// Do nothing
}
void MyAggregateFuncFinal(sqlite3_context* context) {
sqlite3 *db;
char *e;
db=sqlite3_user_data(context);
sqlite3_exec(db,"insert into t2 values(0);",NULL,NULL,);
if (e) printf("MyAggregateFuncFinal: %s\n",e);
sqlite3_result_null(context);
}
main() {
sqlite3 *db;
char *e;
sqlite3_open(NULL,);
sqlite3_create_function(db,"MyAggregateFunc",1,SQLITE_ANY,db,NULL,
MyAggregateFuncStep,MyAggregateFuncFinal);
sqlite3_exec(db,"create table t1(a integer);\
create table t2(a integer);\
insert into t1 values(0);\
select MyAggregateFunc(a) from t1;",NULL,NULL,);
if (e) printf("main: %s\n",e);
};
There seems to be no problem with scalar functions.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] performance issue
Hi I am using SQLite on MVL OS for ARM processor based embedded platform. I am using SQLite version 3.3.13. We use SQLite APIs for DB operation. I am facing following issue. While testing I observed INSERT and UPDATE command is taking more time than SELECT queries. For example one select query is taking 1 to 2 mili sec where as one INSERT or UPDATE takes 40 to 100 mili secs. We are seeing very high latency for write queries. We tried some performance enhancement flags and PRAGMA settings. Is there any performance enhancement settings in SQLite? Or any known issue? Thanks & Regards Suresh *** Aricent-Restricted *** "DISCLAIMER: This message is proprietary to Aricent and is intended solely for the use of the individual to whom it is addressed. It may contain privileged or confidential information and should not be circulated or used for any purpose other than for what it is intended. If you have received this message in error, please notify the originator immediately. If you are not the intended recipient, you are notified that you are strictly prohibited from using, copying, altering, or disclosing the contents of this message. Aricent accepts no responsibility for loss or damage arising from the use of the information transmitted by this email including damage from virus."
