[sqlite] Change in behavior from 2.x to 3.x
When querying multiple tables I was relying on SQLite to return the column names with the table name/designator prepended to it. The following works in 2.x but not in 3.x: SQLite version 2.8.17 Enter .help for instructions sqlite .header on sqlite SELECT E.*, P.Name FROM Edition AS E, Publisher AS P WHERE E.GameID = 126 AND E.PublisherID = P.PublisherID ORDER BY E.Name, E.EditionID; E.EditionID|E.GameID|E.Name|E.PublisherID|E.Own|P.Name 130|126|Roads Boats|46||Splotter Spellen SQLite version 3.3.3 Enter .help for instructions sqlite .header on sqlite SELECT E.*, P.Name FROM Edition AS E, Publisher AS P WHERE E.GameID = 126 AND E.PublisherID = P.PublisherID ORDER BY E.Name, E.EditionID; EditionID|GameID|Name|PublisherID|Own|Name 130|126|Roads Boats|46||Splotter Spellen This is reflected in my Perl program, where my scripts are now broken after upgrading to a new version of SQLite, as I am looking for data in E.Name and P.Name, and finding neither (in fact, I have no value for Name returned at all). Is there any way to get the old behavior back? Is there some other work-around? -- Regards, joe Joe Casadonte [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Change in behavior from 2.x to 3.x
If you use the SQLite API rather than shelling to the SQLite command line utility you may get more satisfactory results. The API between 2.x and 3.x is quite different, but the column headers are readily available. Clay Dowling Joe Casadonte said: When querying multiple tables I was relying on SQLite to return the column names with the table name/designator prepended to it. The following works in 2.x but not in 3.x: SQLite version 2.8.17 Enter .help for instructions sqlite .header on sqlite SELECT E.*, P.Name FROM Edition AS E, Publisher AS P WHERE E.GameID = 126 AND E.PublisherID = P.PublisherID ORDER BY E.Name, E.EditionID; E.EditionID|E.GameID|E.Name|E.PublisherID|E.Own|P.Name 130|126|Roads Boats|46||Splotter Spellen SQLite version 3.3.3 Enter .help for instructions sqlite .header on sqlite SELECT E.*, P.Name FROM Edition AS E, Publisher AS P WHERE E.GameID = 126 AND E.PublisherID = P.PublisherID ORDER BY E.Name, E.EditionID; EditionID|GameID|Name|PublisherID|Own|Name 130|126|Roads Boats|46||Splotter Spellen This is reflected in my Perl program, where my scripts are now broken after upgrading to a new version of SQLite, as I am looking for data in E.Name and P.Name, and finding neither (in fact, I have no value for Name returned at all). Is there any way to get the old behavior back? Is there some other work-around? -- Regards, joe Joe Casadonte [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] - -- Simple Content Management http://www.ceamus.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Change in behavior from 2.x to 3.x
On Wed, 27 Dec 2006, Clay Dowling wrote: If you use the SQLite API rather than shelling to the SQLite command line utility you may get more satisfactory results. The API between 2.x and 3.x is quite different, but the column headers are readily available. I actually need this capability in the DBD::SQLite Perl module; I was using the command line tool as an example. What can I look for in the API, to point the DBD::SQLite maintainer at? Thanks for the help! -- Regards, joe Joe Casadonte [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Change in behavior from 2.x to 3.x
Search for these pragmas in the perl module. There might be a method wrapping them. If there isn't, just execute these pragmas just after opening the database. sqlite select E.*, t2.* from t1 E, t2; E.a|t2.a 4|5 --- Joe Casadonte [EMAIL PROTECTED] wrote: When querying multiple tables I was relying on SQLite to return the column names with the table name/designator prepended to it. The following works in 2.x but not in 3.x: SQLite version 2.8.17 Enter .help for instructions sqlite .header on sqlite SELECT E.*, P.Name FROM Edition AS E, Publisher AS P WHERE E.GameID = 126 AND E.PublisherID = P.PublisherID ORDER BY E.Name, E.EditionID; E.EditionID|E.GameID|E.Name|E.PublisherID|E.Own|P.Name 130|126|Roads Boats|46||Splotter Spellen SQLite version 3.3.3 Enter .help for instructions sqlite .header on sqlite SELECT E.*, P.Name FROM Edition AS E, Publisher AS P WHERE E.GameID = 126 AND E.PublisherID = P.PublisherID ORDER BY E.Name, E.EditionID; EditionID|GameID|Name|PublisherID|Own|Name 130|126|Roads Boats|46||Splotter Spellen This is reflected in my Perl program, where my scripts are now broken after upgrading to a new version of SQLite, as I am looking for data in E.Name and P.Name, and finding neither (in fact, I have no value for Name returned at all). Is there any way to get the old behavior back? Is there some other work-around? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Change in behavior from 2.x to 3.x
The pragmas would help... sqlite PRAGMA short_column_names = 0; sqlite PRAGMA full_column_names = 1; sqlite select t1.*, t2.* from t1, t2; t1.a|t2.a 4|5 --- Joe Wilson [EMAIL PROTECTED] wrote: Search for these pragmas in the perl module. There might be a method wrapping them. If there isn't, just execute these pragmas just after opening the database. sqlite select E.*, t2.* from t1 E, t2; E.a|t2.a 4|5 --- Joe Casadonte [EMAIL PROTECTED] wrote: When querying multiple tables I was relying on SQLite to return the column names with the table name/designator prepended to it. The following works in 2.x but not in 3.x: SQLite version 2.8.17 Enter .help for instructions sqlite .header on sqlite SELECT E.*, P.Name FROM Edition AS E, Publisher AS P WHERE E.GameID = 126 AND E.PublisherID = P.PublisherID ORDER BY E.Name, E.EditionID; E.EditionID|E.GameID|E.Name|E.PublisherID|E.Own|P.Name 130|126|Roads Boats|46||Splotter Spellen SQLite version 3.3.3 Enter .help for instructions sqlite .header on sqlite SELECT E.*, P.Name FROM Edition AS E, Publisher AS P WHERE E.GameID = 126 AND E.PublisherID = P.PublisherID ORDER BY E.Name, E.EditionID; EditionID|GameID|Name|PublisherID|Own|Name 130|126|Roads Boats|46||Splotter Spellen This is reflected in my Perl program, where my scripts are now broken after upgrading to a new version of SQLite, as I am looking for data in E.Name and P.Name, and finding neither (in fact, I have no value for Name returned at all). Is there any way to get the old behavior back? Is there some other work-around? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] UPDATE multiple fields
What would the query construction be in SQLite to update multiple fields? I have tried all sort of syntaxes, but sofar no success yet. I now have to do it in a loop, but that is a bit slow: For i = 2 To lMaxEntryCount For c = 1 To UBound(arrFields) strUPDATE = arrFields2(c) _E i strSQL = UPDATE strNewTable SET _ strUPDATE = (SELECT arrFields(c) _ FROM GROUP_ i T WHERE PATIENT_ID = T.PID) RunSQLiteActionQuery2 strDB, strSQL, False, True, False, True, , True Next Next Thanks for any advice. RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: UPDATE multiple fields
[EMAIL PROTECTED] wrote: What would the query construction be in SQLite to update multiple fields? I have tried all sort of syntaxes, but sofar no success yet. I now have to do it in a loop, but that is a bit slow: For i = 2 To lMaxEntryCount For c = 1 To UBound(arrFields) strUPDATE = arrFields2(c) _E i strSQL = UPDATE strNewTable SET _ strUPDATE = (SELECT arrFields(c) _ FROM GROUP_ i T WHERE PATIENT_ID = T.PID) RunSQLiteActionQuery2 strDB, strSQL, False, True, False, True, , True Next Next SQLite supports a syntax like this: UPDATE newTable SET field1 = (SELECT field1 FROM oldTable T WHERE PATIENT_ID = T.PID), field2 = (SELECT field2 FROM oldTable T WHERE PATIENT_ID = T.PID), field3 = (SELECT field3 FROM oldTable T WHERE PATIENT_ID = T.PID); I'm not sure this is going to be significantly faster than the loop you have now. Unfortunately, SQLite doesn't support UPDATE...FROM syntax some other engines use, as in -- doesn't work with SQLite UPDATE newTable SET field1=T.field1, field2=T.field2, field3=T.field3 FROM oldTable T WHERE PATIENT_ID = T.PID; Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Change in behavior from 2.x to 3.x
On Wed, 27 Dec 2006, Joe Wilson wrote: Search for these pragmas in the perl module. There might be a method wrapping them. If there isn't, just execute these pragmas just after opening the database. Can't figure out how to execute them via Perl (yet). Interestingly, they only half work in the command-line version (I get Publisher.Name instead of P.Name): SQLite version 3.3.3 Enter .help for instructions sqlite PRAGMA full_column_names=1; sqlite PRAGMA short_column_names=0; sqlite .header on sqlite SELECT E.*, P.Name FROM Edition AS E, Publisher AS P WHERE E.GameID = 126 AND E.PublisherID = P.PublisherID ORDER BY E.Name, E.EditionID; E.EditionID|E.GameID|E.Name|E.PublisherID|E.Own|Publisher.Name 130|126|Roads Boats|46||Splotter Spellen Thanks! -- Regards, joe Joe Casadonte [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: UPDATE multiple fields
Thanks, that was very helpful. In fact it looks it as many times faster as the number of fields to be done, so in my particular case 5 times faster! Maybe somebody who knows the inner workings of SQLite could explain why this is. Will see if I can apply this to some other places in my app. RBS SQLite supports a syntax like this: UPDATE newTable SET field1 = (SELECT field1 FROM oldTable T WHERE PATIENT_ID = T.PID), field2 = (SELECT field2 FROM oldTable T WHERE PATIENT_ID = T.PID), field3 = (SELECT field3 FROM oldTable T WHERE PATIENT_ID = T.PID); I'm not sure this is going to be significantly faster than the loop you have now. Unfortunately, SQLite doesn't support UPDATE...FROM syntax some other engines use, as in -- doesn't work with SQLite UPDATE newTable SET field1=T.field1, field2=T.field2, field3=T.field3 FROM oldTable T WHERE PATIENT_ID = T.PID; Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: UPDATE multiple fields
Actually it is even better as I can combine all the UPDATE statements both from the inner and the outer loop and run only one UPDATE, so it is more than the number of fields times as fast, although not quite i times c times as fast. Thanks again! RBS Thanks, that was very helpful. In fact it looks it as many times faster as the number of fields to be done, so in my particular case 5 times faster! Maybe somebody who knows the inner workings of SQLite could explain why this is. Will see if I can apply this to some other places in my app. RBS SQLite supports a syntax like this: UPDATE newTable SET field1 = (SELECT field1 FROM oldTable T WHERE PATIENT_ID = T.PID), field2 = (SELECT field2 FROM oldTable T WHERE PATIENT_ID = T.PID), field3 = (SELECT field3 FROM oldTable T WHERE PATIENT_ID = T.PID); I'm not sure this is going to be significantly faster than the loop you have now. Unfortunately, SQLite doesn't support UPDATE...FROM syntax some other engines use, as in -- doesn't work with SQLite UPDATE newTable SET field1=T.field1, field2=T.field2, field3=T.field3 FROM oldTable T WHERE PATIENT_ID = T.PID; 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] Change in behavior from 2.x to 3.x
my $dbh = DBI-connect($db);
...
$dbh-do('PRAGMA full_column_names=1');
$dbh-do('PRAGMA short_column_names=0');
The aliases in SQLite have always been finicky.
Instead of using aliases, just use the table names throughout, as in
SELECT Edition.*, Publisher.Name FROM Edition, Publisher WHERE ...
And the output will be more predictable, at the cost of more verbose SELECTs.
--- Joe Casadonte [EMAIL PROTECTED] wrote:
On Wed, 27 Dec 2006, Joe Wilson wrote:
Search for these pragmas in the perl module. There might be a method
wrapping them. If there isn't, just execute these pragmas just after
opening the database.
Can't figure out how to execute them via Perl (yet). Interestingly,
they only half work in the command-line version (I get Publisher.Name
instead of P.Name):
SQLite version 3.3.3
Enter .help for instructions
sqlite PRAGMA full_column_names=1;
sqlite PRAGMA short_column_names=0;
sqlite .header on
sqlite SELECT E.*, P.Name FROM Edition AS E, Publisher AS P WHERE E.GameID =
126 AND
E.PublisherID = P.PublisherID ORDER BY E.Name, E.EditionID;
E.EditionID|E.GameID|E.Name|E.PublisherID|E.Own|Publisher.Name
130|126|Roads Boats|46||Splotter Spellen
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Change in behavior from 2.x to 3.x
On Wed, 27 Dec 2006, Joe Wilson wrote:
my $dbh = DBI-connect($db);
...
$dbh-do('PRAGMA full_column_names=1');
$dbh-do('PRAGMA short_column_names=0');
Yeah, I eventually figured that out. This works as well:
my $dbh = DBI-connect($db);
...
my($sth) = $dbh-prepare('PRAGMA full_column_names=1');
$sth-execute;
At least insofar as the pragmas change in the sqlite engine itself.
But neither method has an effect on the output of the Perl code. He
has his own FullCol/ShortCol variables in select.c where he figures
out what to return. These are set via flags, which in turn are set in
pragma.c, assuming that the function sqlite3Pragma() is called
correctly. I'm thinking this is not a SQLite issue, and will contact
someone on the Perl side of things.
Thanks for the help!
--
Regards,
joe
Joe Casadonte
[EMAIL PROTECTED]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Change in behavior from 2.x to 3.x
sqlite3_column_name would be favorite, assuming that the DBD provider uses the prepared statements API (which it should be doing). Clay Dowling Joe Casadonte said: On Wed, 27 Dec 2006, Clay Dowling wrote: If you use the SQLite API rather than shelling to the SQLite command line utility you may get more satisfactory results. The API between 2.x and 3.x is quite different, but the column headers are readily available. I actually need this capability in the DBD::SQLite Perl module; I was using the command line tool as an example. What can I look for in the API, to point the DBD::SQLite maintainer at? Thanks for the help! -- Regards, joe Joe Casadonte [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] - -- Simple Content Management http://www.ceamus.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Change in behavior from 2.x to 3.x
--- Joe Casadonte [EMAIL PROTECTED] wrote: At least insofar as the pragmas change in the sqlite engine itself. But neither method has an effect on the output of the Perl code. He has his own FullCol/ShortCol variables in select.c where he figures out what to return. These are set via flags, which in turn are set in pragma.c, assuming that the function sqlite3Pragma() is called correctly. I'm thinking this is not a SQLite issue, and will contact someone on the Perl side of things. The select.c file in the Perl DBD Sqlite module is actually a source file from the SQLite distro and should not be changed. You may want to look at: http://search.cpan.org/src/MSERGEANT/DBD-SQLite-1.13/dbdimp.c The main source files for DBD::SQLite are: dbdimp.c dbdimp.h SQLite.xs SQLiteXS.h lib/DBD/SQLite.pm __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Change in behavior from 2.x to 3.x
--- Joe Casadonte [EMAIL PROTECTED] wrote:
At least insofar as the pragmas change in the sqlite engine itself.
But neither method has an effect on the output of the Perl code. He
has his own FullCol/ShortCol variables in select.c where he figures
out what to return. These are set via flags, which in turn are set in
pragma.c, assuming that the function sqlite3Pragma() is called
correctly. I'm thinking this is not a SQLite issue, and will contact
someone on the Perl side of things.
The select.c file in the Perl DBD Sqlite module is actually a
source file from the SQLite distro and should not be changed.
You may want to look at:
http://search.cpan.org/src/MSERGEANT/DBD-SQLite-1.13/dbdimp.c
Here's the problem - the DBD::SQLite author explicitly removes the
table prefix in
http://search.cpan.org/src/MSERGEANT/DBD-SQLite-1.13/dbdimp.c
See drop table name from field name below...
SV *
sqlite_st_FETCH_attrib (SV *sth, imp_sth_t *imp_sth, SV *keysv)
{
char *key = SvPV_nolen(keysv);
SV *retsv = NULL;
int i,n;
if (!DBIc_ACTIVE(imp_sth)) {
return NULL;
}
/* warn(fetch: %s\n, key); */
i = DBIc_NUM_FIELDS(imp_sth);
if (strEQ(key, NAME)) {
AV *av = newAV();
/* warn(Fetch NAME fields: %d\n, i); */
av_extend(av, i);
retsv = sv_2mortal(newRV(sv_2mortal((SV*)av)));
for (n = 0; n i; n++) {
/* warn(Fetch col name %d\n, n); */
const char *fieldname = sqlite3_column_name(imp_sth-stmt, n);
if (fieldname) {
/* warn(Name [%d]: %s\n, n, fieldname); */
char *dot = instr(fieldname, .);
if (dot) /* drop table name from field name */
fieldname = ++dot;
Perhaps if you get rid of the last two lines above it may do
what you want. Happy hacking.
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Tokenizing in a trigger
Hi all, I have two tables: CREATE TABLE data (id INTEGER PRIMARY KEY, data TEXT); CREATE TABLE tokens (token TEXT, data INTEGER); Where tokens contains a list of each data row broken down into multiple tokens (split on semicolons). I want to create a trigger that on updates to data will remove all tokens for the item from the tokens table and then re-tokenize and insert the new tokens into the tokens table. Is there any way to get a trigger to insert a variable number of rows based on a single row being updated? Is there a way to write a custom function to do this? I've been thinking of writing a virtual table that would take the input in a WHERE clause and dump the tokens out, one per row, but I'm hoping that there is a simpler way to do this. Thanks.
Re: [sqlite] Change in behavior from 2.x to 3.x
On Wed, 27 Dec 2006, Joe Wilson wrote: Here's the problem - the DBD::SQLite author explicitly removes the table prefix in http://search.cpan.org/src/MSERGEANT/DBD-SQLite-1.13/dbdimp.c See drop table name from field name below... Thanks, Joe! -- Regards, joe Joe Casadonte [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Tokenizing in a trigger
Ron Stevens sqlite-Y9FGH9USQxS1Z/[EMAIL PROTECTED] wrote: Is there any way to get a trigger to insert a variable number of rows based on a single row being updated? A trigger can call a custom function, which can do pretty much anything, including executing one or more SQL statements. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Tokenizing in a trigger
Can you get a valid sqlite3* database connection from a function's sqlite3_context* without relying on the user data when registering the function? --- Igor Tandetnik [EMAIL PROTECTED] wrote: A trigger can call a custom function, which can do pretty much anything, including executing one or more SQL statements. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Tokenizing in a trigger
Joe Wilson developir-/[EMAIL PROTECTED] wrote: Can you get a valid sqlite3* database connection from a function's sqlite3_context* without relying on the user data when registering the function? Not in a documented way, as far as I can tell. What's wrong with relying on user data? Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: Tokenizing in a trigger
--- Igor Tandetnik [EMAIL PROTECTED] wrote: Joe Wilson developir-/[EMAIL PROTECTED] wrote: Can you get a valid sqlite3* database connection from a function's sqlite3_context* without relying on the user data when registering the function? Not in a documented way, as far as I can tell. What's wrong with relying on user data? Nothing. It's just such a common thing to want to get a database connection from a function context that I thought there might (or ought to be) a better way. After all, you must pass an sqlite3* to register a function in the first place, so the information could be made available easily enough without relying on specialized user code. Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite performance, locking threading
I am developing a multithreaded C++ library which uses sqlite as an embedded database alongside the mysql client as a simple sql api. Both databases share a common interface which supports statements, prepared statements, recordsets, records, and transactions. As part of some research and testing i have been playing around with creating a small filesystem based search engine index. Each document within the index is stored as a file in a folder tree structure, but the contents of the documents are catalogued in two sqlite databases at the root of the folder tree. As each document gets added to the index, either or both of the sqlite databases are updated. The goal is to be able to index something in the order of 10 to 100 documents a second. This will allow indexing of around 8 million documents per day on a single machine. As such i have been investigating the ways in which i can get the most performance out of sqlite. I began by wrapping the entire indexing process in transactions, this immediately resulted in a huge performance gain but the entire process was still very synchronous, only indexing one document at a time. The next logical step was to add multiple threads to make better use of the operating system and interleave the various indexing tasks. At this point i ran into the limitations which sqlite places on threading, and after several crashes and error messages concerning locks and routines called out of sequence i modified the api to ensure that each thread was given its own sqlite3 * structure. However in doing this i was penalised by no longer being able to take advantage of the application wide transaction (or at least that was my understanding), so i then had to increase the granularity of the transactions to be within the normal thread synchronisation blocks. This decreased the performance to such a degree that it counteracted any gains which could be made by using multiple threads. I also attempted to improve performance by using multiple inserts but discovered that sqlite does not support the same multiple sets of values that mysql does, insert into table(a,b) values (1,2),(3,4),(5,6). I then tried to use prepared statements but suffered several scary looking crashes deep within the sqlite code responsible for closing file handles which i have yet to debug properly. I then discovered the pragma directives such as pragma synchronise=off and tried those, but did not get the performance gain that i was looking for either. My feelings are that the major gain that i am missing is being able to have an application wide transaction and multiple threads and also being able to execute multiple inserts using the values syntax or something similar. Having read the information on file locking and concurrency ( http://www.sqlite.org/lockingv3.html) and browsed the wiki's and various other online forums i have some questions regarding the above situation. The first question is why database locking has been enabled on a per thread basis instead of per process so that the normal methods of thread synchronisation (mutexes, ciritcal sections) could not be used for maintaining consistency within the application. The programmer can easily ensure that no two statements are executed at the same time just as they would ensure that no more than one operation on a linked list was performed at the same time. I read somewhere that there is a technical reason for this behaviour in sqlite, such as the fcntl() function taking out per thread locks. But i dont understand why file locking is used at all. Given that all platforms, and indeed file systems, have different locking semantics, would it not be easier to either use a .lock file (in a similar way to the ,journal file) if you really needed to support concurrent access from other machines. Or just use a much faster IPC mechanism like shared memory and assume that all access will be from multiple processes on the same machine (a reasonable assumption for an embedded database). And the second question is simply how hard is it to support the multiple insert syntax discussed above, or is it simply a case of there being nothing to be gained here because the normal insert statement is already fast enough ? Surely not... Also are any other optimisation techniques which you can suggest which i might be missing ? Apologies for the length, hopefully im not covering well trodden ground here although i suspect i am. Emerson
[sqlite] SQLite is amazing
I am once again experiencing one of those moments of awe at how incredible SQLite is. I was about to write some custom functions for some complex date manipulation but I find it's already there. The speed, robustness, professionalism in this product is fantastic. Thank you Dr. Richard Hipp! - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] multiple selects in a single prepare
Hi, Just wanted to know can we have multiple quries in a single prepare statement seperated by semicolons.Something like, Select count(*) from tbl where name=foo;select count(*) from tbl1 where name = bar ... Chetana. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[sqlite] Re: multiple selects in a single prepare
chetana bhargav bhargav_chetana-/[EMAIL PROTECTED] wrote: Just wanted to know can we have multiple quries in a single prepare statement seperated by semicolons.Something like, Select count(*) from tbl where name=foo;select count(*) from tbl1 where name = bar sqlite3_prepare will parse a single statement, and return a pointer to the first character of the next statement. You would need to call it again to prepare the next statement. Note also that string literals should be enclosed in single quotes, as in name = 'foo'. SQLite tolerates double quotes but sometimes this may lead to surprising effects. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: multiple selects in a single prepare
* chetana bhargav [EMAIL PROTECTED] [2006-12-28 06:00]: Just wanted to know can we have multiple quries in a single prepare statement seperated by semicolons.Something like, Select count(*) from tbl where name=foo;select count(*) from tbl1 where name = bar Just how is that supposed to work? Are you looking for the UNION operator, perchance? Regards, -- Aristotle Pagaltzis // http://plasmasturm.org/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite performance, locking threading
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Emerson Clarke wrote: | modified the api to ensure that each thread was given its own sqlite3 * | structure. I would assume that the actual indexing is the expensive part since it involves a lot of I/O (SQLite page size is 1KB). Why don't you do this with worker threads (better on Windows) or worker processes (better on Unix)? Have a queue object that filenames to index are put into. Then have each worker thread or worker process take a filename from the queue, do whatever work it is they need to do and put their results into a result queue. You can have one thread then taking those results from the result queue and doing a commit every n results or n seconds. It is possible for multiple connections in the same thread to share a cache as well as having multiple queries in progress. See http://sqlite.org/capi3ref.html#sqlite3_enable_shared_cache and http://www.sqlite.org/sharedcache.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFFk2o0mOOfHg372QQRAid0AJ0Xq6pMsbpCMZVr7dBUEPrElhwtAQCeI3aR AcoNPTAv0C9kdEXuNJio28I= =gQr8 -END PGP SIGNATURE- - To unsubscribe, send email to [EMAIL PROTECTED] -
