Re: [sqlite] Prepared Statement (select * from x where y in ());
Long ago and far away, I build a database abstraction layer which used
?@ for this. So you'd say something like:
stmt = prepare("select * from table where xyz in (?@)");
bind_array(stmt, 0, arrayRef);
The library would take the array, quote each element, and separate
them with commas. It was pretty nice when you needed it.
Unfortunately, this wasn't for sqlite :-).
One option would be to write code to build the statement with the
right number of ?, then bind that many. Very annoying.
Another option would be to create a temporary table, run through the
array doing individual INSERT statements, then "select * from table
where xyz in (select * from temp_table)". Also very annoying.
-scott
On 7/18/07, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
May be my Q is not clear,
Problem is as shown below,
SQL = "select * from table where xyz in (:abc);"
bind_text("'ab','xy','zx','123'")
bind_text will append '' at the begining and at end making it as one string
rather
than ab,xy,zx.
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is intended only for the person or entity whose address is listed above.
Any use of the information contained herein in any way (including, but not
limited to, total or partial disclosure, reproduction, or dissemination) by
persons other than the intended recipient(s) is prohibited. If you receive this
e-mail in error, please notify the sender by phone or email immediately and
delete it!
*
- Original Message -
From: Bharath Booshan L <[EMAIL PROTECTED]>
Date: Thursday, July 19, 2007 11:56 am
Subject: Re: [sqlite] Prepared Statement (select * from x where y in ());
>
>
>
> > 1)How can we prepare a SQliteStatement for the following type of
> select,> select * from tablename where column in (?);
> > ?: we don;t know the length of this string.
>
>
> Pass any value less than zero as 3rd parameter to sqlite3_prepare
> function,in which case, characters up to the first null terminator
> will be
> considered.
>
> 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]
> ---
> --
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Prepared Statement (select * from x where y in ());
May be my Q is not clear,
Problem is as shown below,
SQL = "select * from table where xyz in (:abc);"
bind_text("'ab','xy','zx','123'")
bind_text will append '' at the begining and at end making it as one string
rather
than ab,xy,zx.
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is intended only for the person or entity whose address is listed above.
Any use of the information contained herein in any way (including, but not
limited to, total or partial disclosure, reproduction, or dissemination) by
persons other than the intended recipient(s) is prohibited. If you receive this
e-mail in error, please notify the sender by phone or email immediately and
delete it!
*
- Original Message -
From: Bharath Booshan L <[EMAIL PROTECTED]>
Date: Thursday, July 19, 2007 11:56 am
Subject: Re: [sqlite] Prepared Statement (select * from x where y in ());
>
>
>
> > 1)How can we prepare a SQliteStatement for the following type of
> select,> select * from tablename where column in (?);
> > ?: we don;t know the length of this string.
>
>
> Pass any value less than zero as 3rd parameter to sqlite3_prepare
> function,in which case, characters up to the first null terminator
> will be
> considered.
>
> 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]
> ---
> --
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] UPDATE during SELECT
[EMAIL PROTECTED] wrote: John Stanton <[EMAIL PROTECTED]> wrote: Yes, each connection has a cache. A lot of concurrent connections means a lot of memory allocated to cache and potentially a lot of duplicated cached items. See shared cache mode for relief. Yes. But remember that shared cache mode has limitations: * When shared cache mode is enabled, you cannot use a connection in a thread other than the thread in which it was originally created. * Only connections opened in the same thread share a cache. The shared cache mode is designed for building a "server thread" that accepts connection requests and SQL statements via messages from "client threads", acts upon those requests, and returns the result. -- D. Richard Hipp <[EMAIL PROTECTED]> I suppose that I could accomplish almost the same thing in 2.8.17, even though shared cache mode is not available in that version. I could have a server thread that opens the database, and then accepts and processes SQL statements via messages from client threads. The only difference would be that the client threads could not send connection requests. There would be only one connection, and it would be opened implicitly by the server thread at system startup. The benefit would be that all the client threads would effectively share the same cache, since there would in fact be only one connection. The cost would be that each SQL statement would require an additional two context switches to execute. In my application (TiVo-like Personal Video Recorder functionality in a set-top box), the benefit of memory savings far outweighs the cost of a performance hit due to extra context switches. - Richard - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: UPDATE during SELECT
John Stanton wrote: Richard Klein wrote: John Stanton wrote: Using a single Sqlite connection for each database and holding the connection open means that maximum effect is obtained from Sqlite's cache. > Why is that? Is there a separate cache (I hope not!) for each connection? Thanks, - Richard (new to the group) Yes, each connection has a cache. A lot of concurrent connections means a lot of memory allocated to cache and potentially a lot of duplicated cached items. See shared cache mode for relief. Follow this thread for reasons why the approach above is not a valid solution. I should have mentioned that I am using SQLite version 2.8.17, so shared cache mode is not even an option for me. However, it appears that I would not be able to use it anyway, since only connections opened by the same thread can share a cache. I want to be able to share a cache between two different threads. Was it true even in version 2 that each connection had its own cache? I was hoping that the simpler concurrency model in version 2 would allow the sharing of a single cache between two different threads. Regards, - Richard - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Prepared Statement (select * from x where y in ());
> 1)How can we prepare a SQliteStatement for the following type of select, > select * from tablename where column in (?); > ?: we don;t know the length of this string. Pass any value less than zero as 3rd parameter to sqlite3_prepare function, in which case, characters up to the first null terminator will be considered. 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] -
[sqlite] Prepared Statement (select * from x where y in ());
HI, 1)How can we prepare a SQliteStatement for the following type of select, select * from tablename where column in (?); ?: we don;t know the length of this string. 2) Can we mix SqliteStatement and sqlite3_exec functions? regads ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] autoincrement and fts2?
--- Scott Hess <[EMAIL PROTECTED]> wrote:
> S, as far as I can tell, this behaviour changed in October, with
> http://www.sqlite.org/cvstrac/chngview?cn=3470 . Which is before fts2
> even existed! So fts2 has been broken in this way essentially
> forever. *sigh*. [I'm not entirely clear why that change introduced
> this difference, but it did, so be it.]
I'm confused by what behavior you mean...
VACUUM has changed implicit rowids long before that patch:
SQLite version 3.2.0 (~ March 2005)
Enter ".help" for instructions
sqlite> CREATE TABLE t(a);
sqlite> INSERT INTO "t" VALUES('one');
sqlite> INSERT INTO "t" VALUES('two');
sqlite> INSERT INTO "t" VALUES('three');
sqlite> select rowid, * from t;
1|one
2|two
3|three
sqlite> delete from t where a = 'one';
sqlite> select rowid, * from t;
2|two
3|three
sqlite> vacuum;
sqlite> select rowid, * from t;
1|two
2|three
> Anyhow, moving on... anyone have opinions on how to handle this? The
> patches to fix fts1/2 are simple, but mean that older code will break
> if it tries to read the database created by the patched version.
> Older code is already broken, but only if you use vacuum. My
> inclination is to add code to fts2 and fts1 to upgrade tables, and put
> a prominent disclaimer somewhere.
If you upgrade the tables in a new fts1/fts2 library, you cannot return
to using the old code. This may surprise people when they revert to a
previous version to test. I think there's no good way out of this except
to rename the fts1/fts2 modules to something else.
Other alternatives:
* Lobby drh to retain implicit rowids across a VACUUM.
That way you can keep the old code without modifications.
* Make VACUUM a no-op if FTS is installed (either at compile time
or run-time).
I'm not saying these alternatives are good or likely to happen.
Sick sense of humor? Visit Yahoo! TV's
Comedy with an Edge to see what's on, when.
http://tv.yahoo.com/collections/222
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] UPDATE during SELECT
John Stanton <[EMAIL PROTECTED]> wrote: > > Yes, each connection has a cache. A lot of concurrent connections means > a lot of memory allocated to cache and potentially a lot of duplicated > cached items. See shared cache mode for relief. > Yes. But remember that shared cache mode has limitations: * When shared cache mode is enabled, you cannot use a connection in a thread other than the thread in which it was originally created. * Only connections opened in the same thread share a cache. The shared cache mode is designed for building a "server thread" that accepts connection requests and SQL statements via messages from "client threads", acts upon those requests, and returns the result. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] "Library routine called out of sequence" and user-defined aggregates
<[EMAIL PROTECTED]> wrote: > John Stanton wrote: > > > Have you checked to ensure that you database handle is correct? > > Your function inherits it as user data, but is it corrupted? > > The db handle is passed correctly. > > I'm starting to suspect that this is a bug of sqlite. It is not a bug - it is a limitation. Aggregate functions are not reentrant. Changing this for the step function appears to be just two lines of code. Probably it two additional lines of code to do the same for the finalizer function. Testing the change to verify that it works and does not introduce new bugs, however, will likely take a few hundred lines of test code and corresponding time. Please write an enhancement request ticket and we will get to it as we are able. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] "Library routine called out of sequence" and user-defined aggregates
John Stanton wrote:
> Have you checked to ensure that you database handle is correct?
> Your function inherits it as user data, but is it corrupted?
The db handle is passed correctly.
I'm starting to suspect that this is a bug of sqlite. Even a simple "select 0"
fails inside an aggregate. Here's some code that exemplifies this:
#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,"select 0;",NULL,NULL,&e);
if (e) printf("MyAggregateFuncFinal: %s\n",e);
sqlite3_result_null(context);
}
main() {
sqlite3 *db;
char *e;
sqlite3_open(NULL,&db);
sqlite3_create_function(db,"MyAggregateFunc",1,SQLITE_ANY,db,NULL,
MyAggregateFuncStep,MyAggregateFuncFinal);
sqlite3_exec(db,"select MyAggregateFunc(0);",NULL,NULL,&e);
if (e) printf("main: %s\n",e);
};
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: UPDATE during SELECT
Richard Klein wrote: John Stanton wrote: Using a single Sqlite connection for each database and holding the connection open means that maximum effect is obtained from Sqlite's cache. > Why is that? Is there a separate cache (I hope not!) for each connection? Thanks, - Richard (new to the group) Yes, each connection has a cache. A lot of concurrent connections means a lot of memory allocated to cache and potentially a lot of duplicated cached items. See shared cache mode for relief. Follow this thread for reasons why the approach above is not a valid solution. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UPDATE during SELECT
[EMAIL PROTECTED] wrote: > A single connection can only be used by a single thread at > a time. If you have multiple threads running at the same > time, they each need to have their own connection. > > If you are not running on a Linux 2.4 kernel, then you can > pass connections from one thread to another. So thread A > can do a little work, then hand the connection of to thread B to continue. But thread A and thread B should not > try to use the same connection at the same time. > > The reason for this should be obvious. A "connection" is > a structure, with various fields holding state information. > Each SQLite API call expects the connection to be in a > consistent state when it is first invoked and each API call > leaves the connection in a consistent state when it returns. > But while the API is running, the structure can be in various > transient states that are not strictly "consistent". We cannot, > after all, make multiple changes to a structure atomically > on real hardware. If two threads try to modify the same structure at the same time, they will be seeing each others > inconsistent transient state and chaos will ensue. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > Thankyou for the explananation. A further quaestion. When an Sqlite thread is performing a read (SELECT) what elements of the shared structure are being altered? In other worlds what needs to be thread local or single streamed to make a read thread safe for reading? You probably see what I am getting at. The aim is to share a single cache between all users. The shared cache option has some limitaions which are ideally avoided. = I have an answer to my own question. A little thought and searching and I realize that a file descriptor is a per-process structure which when used in threaded operations is made safe by serializing with a mutex. Since the underlying file descriptors is not inherently thread safe any connection inherits that state. The solution I seek is to use the Sqlite shared cache capability and figure out another way to use FTS2 or to implement a text search. Some tests showed that short transactions, taking around 400uS provide a high debree of concurrency without clashes, but that is luck, not a certainty. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] mailing list slow?
I think that any e-mail should be delivered within a few seconds in this day and age. Also real time mailing should improve discussions on the mailing list. Just an opinion. Kind regards, Lodewijk -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 18, 2007 7:39 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] mailing list slow? Joe Wilson <[EMAIL PROTECTED]> wrote: > I noticed delays of an hour or so in posts hitting the mailing list recently. > Or is it just my mail server? > The server (www.sqlite.org) seems to be doing OK. Load average is 0.13. Nothing unusual in the logs. There are currently 1281 people on the mailing list. The mailing list manager (ezmlm) will send messages to at most 20 people at at time. If each send requires 20 seconds, that means about 1200 seconds to relay a message, or about 20 minutes. Might be your mail server. Spam loads continue to increase. My spam/email ratio is approaching 30:1. In other words, I get about 30 spams for each legitimate email now. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: UPDATE during SELECT
John Stanton wrote: Using a single Sqlite connection for each database and holding the connection open means that maximum effect is obtained from Sqlite's cache. > Why is that? Is there a separate cache (I hope not!) for each connection? Thanks, - Richard (new to the group) - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] autoincrement and fts2?
S, as far as I can tell, this behaviour changed in October, with
http://www.sqlite.org/cvstrac/chngview?cn=3470 . Which is before fts2
even existed! So fts2 has been broken in this way essentially
forever. *sigh*. [I'm not entirely clear why that change introduced
this difference, but it did, so be it.]
Anyhow, moving on... anyone have opinions on how to handle this? The
patches to fix fts1/2 are simple, but mean that older code will break
if it tries to read the database created by the patched version.
Older code is already broken, but only if you use vacuum. My
inclination is to add code to fts2 and fts1 to upgrade tables, and put
a prominent disclaimer somewhere.
-scott
On 7/17/07, Scott Hess <[EMAIL PROTECTED]> wrote:
[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]
-
Re: [sqlite] mailing list slow?
I don't know about slow, but I wish getting on and off the list was *reliable* - ie: as advertised. I've been on other lists, and gotten off without too much effort. I'd like to be able to leave for now, and come back in a few months. But I seem to be stuck here forever. Any chance of getting help on this? I'd really appreciate it. <[EMAIL PROTECTED]> 07/18/2007 01:38 To PM cc Please respond to Subject <[EMAIL PROTECTED] Re: [sqlite] mailing list slow? ite.org> Joe Wilson <[EMAIL PROTECTED]> wrote: > I noticed delays of an hour or so in posts hitting the mailing list recently. > Or is it just my mail server? > The server (www.sqlite.org) seems to be doing OK. Load average is 0.13. Nothing unusual in the logs. There are currently 1281 people on the mailing list. The mailing list manager (ezmlm) will send messages to at most 20 people at at time. If each send requires 20 seconds, that means about 1200 seconds to relay a message, or about 20 minutes. Might be your mail server. Spam loads continue to increase. My spam/email ratio is approaching 30:1. In other words, I get about 30 spams for each legitimate email now. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UPDATE during SELECT
[EMAIL PROTECTED] wrote: Joe Wilson <[EMAIL PROTECTED]> wrote: --- John Stanton <[EMAIL PROTECTED]> wrote: Using a single Sqlite connection for each database and holding the connection open means that maximum effect is obtained from Sqlite's cache. As far as we can ascertain avoiding fcntl removes any issues regarding multiple threads accessing a single connection. I did not know that a single sqlite3 connection could be used concurrently by readers from multiple threads. Previous postings on this list suggested that although an sqlite3 connection could be passed from thread to thread (as is common in a connection pool scenario), only one thread may use that connection at a given time. Can the SQLite authors confirm that simultaneous use of a single connection by multiple reader threads is supported without external mutexes? A single connection can only be used by a single thread at a time. If you have multiple threads running at the same time, they each need to have their own connection. If you are not running on a Linux 2.4 kernel, then you can pass connections from one thread to another. So thread A can do a little work, then hand the connection of to thread B to continue. But thread A and thread B should not try to use the same connection at the same time. The reason for this should be obvious. A "connection" is a structure, with various fields holding state information. Each SQLite API call expects the connection to be in a consistent state when it is first invoked and each API call leaves the connection in a consistent state when it returns. But while the API is running, the structure can be in various transient states that are not strictly "consistent". We cannot, after all, make multiple changes to a structure atomically on real hardware. If two threads try to modify the same structure at the same time, they will be seeing each others inconsistent transient state and chaos will ensue. -- D. Richard Hipp <[EMAIL PROTECTED]> Thankyou for the explananation. A further quaestion. When an Sqlite thread is performing a read (SELECT) what elements of the shared structure are being altered? In other worlds what needs to be thread local or single streamed to make a read thread safe for reading? You probably see what I am getting at. The aim is to share a single cache between all users. The shared cache option has some limitaions which are ideally avoided. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] mailing list slow?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Joe Wilson wrote: > I noticed delays of an hour or so in posts hitting the mailing list recently. > Or is it just my mail server? Note that many people use greylisting on their mail servers which temporarily refuse mail from previously unseen senders/hosts. Most spammers give up and move on to the next victim. Most real servers try again a short while later which can be anywhere from 5 minutes to several hours. These temporary retries are not shown in the mail headers so you have no idea they happened unless you can read the mail server logs of the sender or recipient. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGnntpmOOfHg372QQRAlcJAKCY2w45GlRkJJgewyPBMqR7xGLsfQCfRAjq e8z/nJTBVcAwIFJQtkU8HNA= =bwCZ -END PGP SIGNATURE- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] mailing list slow?
On 7/18/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Joe Wilson <[EMAIL PROTECTED]> wrote: > I noticed delays of an hour or so in posts hitting the mailing list recently. > Or is it just my mail server? > The server (www.sqlite.org) seems to be doing OK. Load average is 0.13. Nothing unusual in the logs. There are currently 1281 people on the mailing list. The mailing list manager (ezmlm) will send messages to at most 20 people at at time. If each send requires 20 seconds, that means about 1200 seconds to relay a message, or about 20 minutes. Nice math ;) -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] malloc failure in sqlite3_prepare
Hi Dan Thanks for the kick in the butt :). I have been developing an application for the ARM platform (for which there is no valgrind port) but this application is running on an x86. I will try that and report back anything I find out. On 7/18/07, Dan Kennedy <[EMAIL PROTECTED]> wrote: > (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 () The most likely cause is that something in the process is corrupting the heap (double-free, freeing a pointer not allocated with malloc(), that sort of thing). It could be SQLite code doing so, or it might not be. Run the application using valgrind and you will probably get some useful information. Dan. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] mailing list slow?
Same here -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 18, 2007 6:01 PM To: sqlite-users@sqlite.org Subject: [sqlite] mailing list slow? I noticed delays of an hour or so in posts hitting the mailing list recently. Or is it just my mail server? Be a PS3 game guru. Get your game face on with the latest PS3 news and previews at Yahoo! Games. http://videogames.yahoo.com/platform?platform=120121 - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UPDATE during SELECT
--- [EMAIL PROTECTED] wrote: > A single connection can only be used by a single thread at > a time. If you have multiple threads running at the same > time, they each need to have their own connection. > > If you are not running on a Linux 2.4 kernel, then you can > pass connections from one thread to another. So thread A > can do a little work, then hand the connection of to > thread B to continue. But thread A and thread B should not > try to use the same connection at the same time. > > The reason for this should be obvious. Nothing is obvious in threadsafe APIs. It means different things to different programmers. Hypothetically, a library API could safely clone a connection without the knowledge of the user using mutexes where appropriate, or use a threadsafe work queue approach - you never know. You have to either rely on the author's documentation, or examine every line of code in the library. Boardwalk for $500? In 2007? Ha! Play Monopoly Here and Now (it's updated for today's economy) at Yahoo! Games. http://get.games.yahoo.com/proddesc?gamekey=monopolyherenow - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] mailing list slow?
Joe Wilson <[EMAIL PROTECTED]> wrote: > I noticed delays of an hour or so in posts hitting the mailing list recently. > Or is it just my mail server? > The server (www.sqlite.org) seems to be doing OK. Load average is 0.13. Nothing unusual in the logs. There are currently 1281 people on the mailing list. The mailing list manager (ezmlm) will send messages to at most 20 people at at time. If each send requires 20 seconds, that means about 1200 seconds to relay a message, or about 20 minutes. Might be your mail server. Spam loads continue to increase. My spam/email ratio is approaching 30:1. In other words, I get about 30 spams for each legitimate email now. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sorting of blobs
Well done, you have answered the man's question. Joe Wilson wrote: --- John Stanton <[EMAIL PROTECTED]> wrote: Try looking at the SORT opcode. You can track it through the Sqlite source. OP_Sort doesn't sort any longer. It just does a rewind on the cursor. /* Opcode: Sort P1 P2 * ** ** This opcode does exactly the same thing as OP_Rewind except that ** it increments an undocumented global variable used for testing. ** ** Sorting is accomplished by writing records into a sorting index, ** then rewinding that index and playing it back from beginning to ** end. We use the OP_Sort opcode instead of OP_Rewind to do the ** rewinding so that the global variable will be incremented and ** regression tests can determine whether or not the optimizer is ** correctly optimizing out sorts. */ Shape Yahoo! in your own image. Join our Network Research Panel today! http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UPDATE during SELECT
Joe Wilson <[EMAIL PROTECTED]> wrote: > --- John Stanton <[EMAIL PROTECTED]> wrote: > > Using a single Sqlite connection for each database and holding the > > connection open means that maximum effect is obtained from Sqlite's > > cache. As far as we can ascertain avoiding fcntl removes any issues > > regarding multiple threads accessing a single connection. > > I did not know that a single sqlite3 connection could be used > concurrently by readers from multiple threads. > > Previous postings on this list suggested that although an sqlite3 > connection could be passed from thread to thread (as is common in > a connection pool scenario), only one thread may use that connection > at a given time. > > Can the SQLite authors confirm that simultaneous use of a single > connection by multiple reader threads is supported without external > mutexes? > A single connection can only be used by a single thread at a time. If you have multiple threads running at the same time, they each need to have their own connection. If you are not running on a Linux 2.4 kernel, then you can pass connections from one thread to another. So thread A can do a little work, then hand the connection of to thread B to continue. But thread A and thread B should not try to use the same connection at the same time. The reason for this should be obvious. A "connection" is a structure, with various fields holding state information. Each SQLite API call expects the connection to be in a consistent state when it is first invoked and each API call leaves the connection in a consistent state when it returns. But while the API is running, the structure can be in various transient states that are not strictly "consistent". We cannot, after all, make multiple changes to a structure atomically on real hardware. If two threads try to modify the same structure at the same time, they will be seeing each others inconsistent transient state and chaos will ensue. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: UPDATE during SELECT
Joe Wilson wrote: --- John Stanton <[EMAIL PROTECTED]> wrote: Using a single Sqlite connection for each database and holding the connection open means that maximum effect is obtained from Sqlite's cache. As far as we can ascertain avoiding fcntl removes any issues regarding multiple threads accessing a single connection. I did not know that a single sqlite3 connection could be used concurrently by readers from multiple threads. Previous postings on this list suggested that although an sqlite3 connection could be passed from thread to thread (as is common in a connection pool scenario), only one thread may use that connection at a given time. Can the SQLite authors confirm that simultaneous use of a single connection by multiple reader threads is supported without external mutexes? My understanding is that the multi-thread contention is due to POSIX file locks. Avoid them and replace the function with read/write locks and the problem goes away. We are having no problems, and my perusal of the code does not show up a potential problem, but I may have missed something and can stand corrected by a more knowledgable person. In software we have distributed for years we have had sporadic problems with b-trees being broken by unreliable file locks. Early versions of Linux even up to 2.4 had problems. Avoiding these locks whereever possible is prudent. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: UPDATE during SELECT
> > Can the SQLite authors confirm that simultaneous use of a single > connection by multiple reader threads is supported without external > mutexes? It is not supported. Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] mailing list slow?
I noticed delays of an hour or so in posts hitting the mailing list recently. Or is it just my mail server? Be a PS3 game guru. Get your game face on with the latest PS3 news and previews at Yahoo! Games. http://videogames.yahoo.com/platform?platform=120121 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sorting of blobs
--- John Stanton <[EMAIL PROTECTED]> wrote: > Try looking at the SORT opcode. You can track it through the Sqlite source. OP_Sort doesn't sort any longer. It just does a rewind on the cursor. /* Opcode: Sort P1 P2 * ** ** This opcode does exactly the same thing as OP_Rewind except that ** it increments an undocumented global variable used for testing. ** ** Sorting is accomplished by writing records into a sorting index, ** then rewinding that index and playing it back from beginning to ** end. We use the OP_Sort opcode instead of OP_Rewind to do the ** rewinding so that the global variable will be incremented and ** regression tests can determine whether or not the optimizer is ** correctly optimizing out sorts. */ Shape Yahoo! in your own image. Join our Network Research Panel today! http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: UPDATE during SELECT
--- John Stanton <[EMAIL PROTECTED]> wrote: > Using a single Sqlite connection for each database and holding the > connection open means that maximum effect is obtained from Sqlite's > cache. As far as we can ascertain avoiding fcntl removes any issues > regarding multiple threads accessing a single connection. I did not know that a single sqlite3 connection could be used concurrently by readers from multiple threads. Previous postings on this list suggested that although an sqlite3 connection could be passed from thread to thread (as is common in a connection pool scenario), only one thread may use that connection at a given time. Can the SQLite authors confirm that simultaneous use of a single connection by multiple reader threads is supported without external mutexes? Get your own web address. Have a HUGE year through Yahoo! Small Business. http://smallbusiness.yahoo.com/domains/?p=BESTDEAL - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] "Library routine called out of sequence" and user-defined aggregates
Have you checked to ensure that you database handle is correct? Your
function inherits it as user data, but is it corrupted?
[EMAIL PROTECTED] wrote:
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,&e);
if (e) printf("MyAggregateFuncFinal: %s\n",e);
sqlite3_result_null(context);
}
main() {
sqlite3 *db;
char *e;
sqlite3_open(NULL,&db);
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,&e);
if (e) printf("main: %s\n",e);
};
There seems to be no problem with scalar functions.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] sorting of blobs
B V, Phanisekhar wrote: Assume I have an albumtable: create table albumtable (albumid INTEGER PRIMARY KEY, album BLOB); Now I do a query to return the entire albums in the albumtable table in alphabetical order: The instructions for the above query are given below: explain select album from albumtable order by album; addr opcode p1 p2 p3 -- -- -- - 0 OpenEphemeral 1 3 keyinfo(1,BINARY) 1 Goto0 26 2 Integer 0 0 3 OpenRead0 2 4 SetNumColumns 0 2 5 Rewind 0 14 6 Column 0 1 7 MakeRecord 1 0 8 Column 0 1 9 Sequence1 0 10Pull2 0 11MakeRecord 3 0 12IdxInsert 1 0 13Next0 6 14Close 0 0 15OpenPseudo 2 0 16SetNumColumns 2 1 17Sort1 24 18Integer 1 0 19Column 1 2 20Insert 2 0 21Column 2 0 22Callback1 0 23Next1 18 24Close 2 0 25Halt0 0 26Transaction 0 0 27VerifyCookie0 1 28Goto0 2 29Noop0 0 I would like to know which portion of the above code does the sorting of the blobs "album". Basically would like to know where exactly the comparison "album A > album B" is done. Regards, Phanisekhar Try looking at the SORT opcode. You can track it through the Sqlite source. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] REGEXP implementation with pcre
On Wed, Jul 11, 2007 at 09:07:39AM -0500, Neil Best wrote: > see code from original post at > http://article.gmane.org/gmane.comp.db.sqlite.general/23584 BTW, the code has been released under the same terms as SQLite itself. http://git.altlinux.org/people/at/packages/?p=sqlite3-pcre.git git://git.altlinux.org/people/at/packages/sqlite3-pcre.git > I came across this note from last fall while looking for information on > how to activate the REGEXP functionalilty. Not only would I like to know > whether this particular solution is correct and appropriate, but I am > generally curious about why no regexp() function has found its way into > the SQLite code for general distribution. Because you need a regexp libaray, which can be as complicated as sqlite itself. Basically you need pcre library, which is simply not part of sqlite. This is why the best way to implement regexp() function is with loadable module, as far as I see. pgpOX1ABVxnKi.pgp Description: PGP signature
Re: [sqlite] Re: UPDATE during SELECT
In our particular application we use Sqlite embedded in a multi-threaded
application server. The databases are only accessed by that process.
We compile Sqlite without the fcntl file locking and place a pthread
read/write lock around accesses to the connection for each database.
That way we have no busies and never poll the connection for busy. The
calling thread blocks on the rwlock.
This method is not perfect because it loses some concurrency compared to
the Sqlite use of pending and reserved locks but for our purposes is
more efficient and less complex. Resolving busy contentions with
minumum latency with some form of busy wait is something to be avoided
if possible in our way of thinking. Using delays blows out the latency.
Using a single Sqlite connection for each database and holding the
connection open means that maximum effect is obtained from Sqlite's
cache. As far as we can ascertain avoiding fcntl removes any issues
regarding multiple threads accessing a single connection.
Benchmarks show an ability to handle over 1,000 concurrent application
users on a minimal server (a test machine recycled from a dumpster with
a 400MHz AMD processor, 384K of memory and a 5,400 rpm IDE disk).
Note that the application is designed to suit Sqlite and have short
transactions. For a different application we would not use Sqlite but
deploy PostgreSQL.
Larry Lewis wrote:
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]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] sorting of blobs
On Wed, 2007-07-18 at 16:03 +0530, B V, Phanisekhar wrote: > Assume I have an albumtable: > > create table albumtable (albumid INTEGER PRIMARY KEY, album BLOB); > > > > Now I do a query to return the entire albums in the albumtable table in > alphabetical order: > > > > The instructions for the above query are given below: > > > > explain select album from albumtable order by album; > > > > addr opcode p1 p2 p3 > > -- -- -- > - > > 0 OpenEphemeral 1 3 keyinfo(1,BINARY) > > 1 Goto0 26 > > 2 Integer 0 0 > > 3 OpenRead0 2 > > 4 SetNumColumns 0 2 > > 5 Rewind 0 14 > > 6 Column 0 1 > > 7 MakeRecord 1 0 > > 8 Column 0 1 > > 9 Sequence1 0 > > 10Pull2 0 > > 11MakeRecord 3 0 > > 12IdxInsert 1 0 > > 13Next0 6 > > 14Close 0 0 > > 15OpenPseudo 2 0 > > 16SetNumColumns 2 1 > > 17Sort1 24 > > 18Integer 1 0 > > 19Column 1 2 > > 20Insert 2 0 > > 21Column 2 0 > > 22Callback1 0 > > 23Next1 18 > > 24Close 2 0 > > 25Halt0 0 > > 26Transaction 0 0 > > 27VerifyCookie0 1 > > 28Goto0 2 > > 29Noop0 0 > > > > I would like to know which portion of the above code does the sorting of > the blobs "album". > > Basically would like to know where exactly the comparison "album A > > album B" is done. Instructions 6-13 are a loop inserting rows into a temporary b-tree structure. The value of the "album" column is used as the key for this table. Instructions 17-23 iterate through this table, pulling records out in sorted order. So the comparison is done in instruction 12. Dan. > > > > Regards, > > Phanisekhar > > > > > > > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sorting of blobs
Assume I have an albumtable: create table albumtable (albumid INTEGER PRIMARY KEY, album BLOB); Now I do a query to return the entire albums in the albumtable table in alphabetical order: The instructions for the above query are given below: explain select album from albumtable order by album; addr opcode p1 p2 p3 -- -- -- - 0 OpenEphemeral 1 3 keyinfo(1,BINARY) 1 Goto0 26 2 Integer 0 0 3 OpenRead0 2 4 SetNumColumns 0 2 5 Rewind 0 14 6 Column 0 1 7 MakeRecord 1 0 8 Column 0 1 9 Sequence1 0 10Pull2 0 11MakeRecord 3 0 12IdxInsert 1 0 13Next0 6 14Close 0 0 15OpenPseudo 2 0 16SetNumColumns 2 1 17Sort1 24 18Integer 1 0 19Column 1 2 20Insert 2 0 21Column 2 0 22Callback1 0 23Next1 18 24Close 2 0 25Halt0 0 26Transaction 0 0 27VerifyCookie0 1 28Goto0 2 29Noop0 0 I would like to know which portion of the above code does the sorting of the blobs "album". Basically would like to know where exactly the comparison "album A > album B" is done. Regards, Phanisekhar
Re: [sqlite] malloc failure in sqlite3_prepare
> (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 () The most likely cause is that something in the process is corrupting the heap (double-free, freeing a pointer not allocated with malloc(), that sort of thing). It could be SQLite code doing so, or it might not be. Run the application using valgrind and you will probably get some useful information. Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
