[sqlite] Maximum database size?
Hello SQLite users, I've been running into some disk I/O errors when doing things such as vacuuming and/or inserting things into temp tables in a database. The databases that are giving me trouble are quite large: between 29 and 55GB. However, as large as that is, I don't think running out of disk space is the issue as I have about 3TB of free space on the disk. So, my question is, is there a maximum size that databases can be? If so, what is the limiting factor? The databases in question don't seem to be corrupt; I can open them on the command line and in python programs (using pysqlite) and can read triggers from them just fine. It's just when I try to vacuum and create temp tables that I run into trouble. If you need to know, I am running sqlite version 3.5.9 on CentOS 5.3. Thanks, Collin Capano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Order of triggers
On 2 Mar 2010, at 8:38pm, Jens Frøkjær wrote: > So, please consider this a feature request: "Deterministic order of > triggers". I understand what you want, but I don't think you'll get it. SQL is full of ambiguity about orders. For instance suppose you execute an UPDATE command which changes many rows. There is nothing in the documentation that tells you which order those rows will be updated in. Might be in the order they were originally created. Might be in the reverse of that. Might be in some order set by some index the optimiser found convenient. Not only do different SQL engines do this differently, but version increments of one SQL engine might change how this is implemented. If statements as simple as DELETE and UPDATE don't have a deterministic execution order, I don't think the order of TRIGGER execution is going to be set in stone. Since ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The character "'" not liked by sqlite?
On 2 Mar 2010, at 7:45pm, Adam DeVita wrote: > If you look in http://www.sqlite.org/capi3ref.html#sqlite3_bind_blob > for the function > > int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*)); > > This will allow you to bind any character into an SQL statement. > There are other benefits to using this technique. Do you know whether Kavita should need to treat the directional quote characters specially, given standard use of the API ? As far as I can see he or she may be having code page or unicode problems, since these characters don't appear in ASCII. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A question about sqlite : How could i get the rows affected by current sql statement ?
> This function returns the number of row changes caused by INSERT, UPDATE or >> DELETE statements since the database connection was opened. >> > > Either you're or this sentence on the site should be changed (in the final > part) Oops, missed the last part. But it seems not very useful for OP because it shows all changes on all trigger levels which can significantly differ from what sqlite3_changes() gives. Pavel On Tue, Mar 2, 2010 at 2:43 PM, Max Vlasov wrote: > On Tue, Mar 2, 2010 at 6:36 PM, Pavel Ivanov wrote: > >> OK, now I see the problem, but sqlite3_total_changes() will not help >> here too - it behaves the same way as sqlite3_changes(), it doesn't >> accumulate changes over several statements. >> > > Hmm... are you sure about this? > A quote from the docs: > > This function returns the number of row changes caused by INSERT, UPDATE or >> DELETE statements since the database connection was opened. >> > > Either you're or this sentence on the site should be changed (in the final > part) > > Max > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Order of triggers
What's wrong with adding new code to existing triggers instead of creating new ones? Pavel On Tue, Mar 2, 2010 at 3:38 PM, Jens Frøkjær wrote: > Hi, > Sorry to BUMP this thread. I was hoping someone would come along with a > better answer. > This means that executing the same deterministic piece of SQL on two > identical databases could yield different results. And I don't mean > different in "order of rows in tables" or something similar. My databases > would not contain the same data. Not close. This seems quite frighting to > me. > Is this something there could be an interest in changing? I don't request > any specific order. SQLite-developers, define an order, any order. Document > it, and stick with it. Thats all I want for Christmas. > So, please consider this a feature request: "Deterministic order of > triggers". > -- > Jens F! > > > On Mon, Feb 22, 2010 at 10:17 PM, Pavel Ivanov wrote: >> >> I cannot find right now details on this in SQLite documentation but >> AFAIK order of triggers execution is undefined and you cannot rely on >> any of them. >> >> >> Pavel >> >> On Mon, Feb 22, 2010 at 3:15 PM, Jens Frøkjær wrote: >> > Hi, >> > >> > I was wondering in what order triggers are executed. I'm using the after >> > update, and have both "column-based" and "row-based" triggers. By >> > column-based, i simply mean triggers that only fire if a specific column >> > is >> > updated. >> > >> > I did a bit of googling myself, and came up with [1]. It is pretty >> > clear, >> > triggers should be executed alphabetically. That actually seemes like a >> > great idea, because, then I have full control. I also did my own >> > testing, >> > which turned up reverse-creating ordering. That means, the newest >> > created >> > trigger is called first. >> > >> > Is the order actually fixed, or can it be "any order"? And if it is >> > fixed, >> > can I, to some degree, trust that it will not change in future relases? >> > >> > [1]: http://code.google.com/p/sqlite-fk-triggers/wiki/TriggerOrder >> > >> > Best regards, >> > Jens F! >> > ___ >> > sqlite-users mailing list >> > sqlite-users@sqlite.org >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Order of triggers
Hi, Sorry to BUMP this thread. I was hoping someone would come along with a better answer. This means that executing the same deterministic piece of SQL on two identical databases could yield different results. And I don't mean different in "order of rows in tables" or something similar. My databases would not contain the same data. Not close. This seems quite frighting to me. Is this something there could be an interest in changing? I don't request any specific order. SQLite-developers, define an order, any order. Document it, and stick with it. Thats all I want for Christmas. So, please consider this a feature request: "Deterministic order of triggers". -- Jens F! On Mon, Feb 22, 2010 at 10:17 PM, Pavel Ivanov wrote: > I cannot find right now details on this in SQLite documentation but > AFAIK order of triggers execution is undefined and you cannot rely on > any of them. > > > Pavel > > On Mon, Feb 22, 2010 at 3:15 PM, Jens Frøkjær wrote: > > Hi, > > > > I was wondering in what order triggers are executed. I'm using the after > > update, and have both "column-based" and "row-based" triggers. By > > column-based, i simply mean triggers that only fire if a specific column > is > > updated. > > > > I did a bit of googling myself, and came up with [1]. It is pretty clear, > > triggers should be executed alphabetically. That actually seemes like a > > great idea, because, then I have full control. I also did my own testing, > > which turned up reverse-creating ordering. That means, the newest created > > trigger is called first. > > > > Is the order actually fixed, or can it be "any order"? And if it is > fixed, > > can I, to some degree, trust that it will not change in future relases? > > > > [1]: http://code.google.com/p/sqlite-fk-triggers/wiki/TriggerOrder > > > > Best regards, > > Jens F! > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The character "'" not liked by sqlite?
Good day, If you look in http://www.sqlite.org/capi3ref.html#sqlite3_bind_blob for the function int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*)); This will allow you to bind any character into an SQL statement. There are other benefits to using this technique. regards, Adam On Tue, Mar 2, 2010 at 2:37 PM, Kavita Raghunathan < kavita.raghunat...@skyfiber.com> wrote: > Simon and Gabriel, > > I'm using the C API, I'm inserting strings. One of the strings happens to > have an "'" in it. I have to write extra code to parse the character and > escape it, I'll do that if I have to. I have not tried the command line > tool. I'll try it and get back to you. > > Kavita > > On 3/2/10 12:56 PM, "Simon Slavin" wrote: > > > > > On 2 Mar 2010, at 6:51pm, Kavita Raghunathan wrote: > > > >> I notice that when I try to insert the character ³¹² as part of a string > into > >> the sqlite database, my updates don¹t work. Any ideas why? The same > string > >> without the ³¹² character works. I have not debugged to see where > exactly in > >> sqlite it fails. > >> > >> I¹m inserting a text like this: ³Rootuser¹s desktop² does not work. > ³Rootuser > >> desktop² works, the update to database suceeds and I¹m able to view it > using > >> select. > > > > What API or toolkit are you using ? Have you tried executing the same > command > > with the command-line tool ? > > > > Simon. > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A question about sqlite : How could i get the rows affected by current sql statement ?
On Tue, Mar 2, 2010 at 6:36 PM, Pavel Ivanov wrote: > OK, now I see the problem, but sqlite3_total_changes() will not help > here too - it behaves the same way as sqlite3_changes(), it doesn't > accumulate changes over several statements. > Hmm... are you sure about this? A quote from the docs: This function returns the number of row changes caused by INSERT, UPDATE or > DELETE statements since the database connection was opened. > Either you're or this sentence on the site should be changed (in the final part) Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The character "'" not liked by sqlite?
Simon and Gabriel, I'm using the C API, I'm inserting strings. One of the strings happens to have an "'" in it. I have to write extra code to parse the character and escape it, I'll do that if I have to. I have not tried the command line tool. I'll try it and get back to you. Kavita On 3/2/10 12:56 PM, "Simon Slavin" wrote: > > On 2 Mar 2010, at 6:51pm, Kavita Raghunathan wrote: > >> I notice that when I try to insert the character ³¹² as part of a string into >> the sqlite database, my updates don¹t work. Any ideas why? The same string >> without the ³¹² character works. I have not debugged to see where exactly in >> sqlite it fails. >> >> I¹m inserting a text like this: ³Rootuser¹s desktop² does not work. ³Rootuser >> desktop² works, the update to database suceeds and I¹m able to view it using >> select. > > What API or toolkit are you using ? Have you tried executing the same command > with the command-line tool ? > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The character "'" not liked by sqlite?
You should be using prepared statements. If that's not possible, then escape the "'", for example: INSERT INTO "this" VALUES ('Rootuser''s Desktop') On Tue, 2010-03-02 at 12:51 -0600, Kavita Raghunathan wrote: > I notice that when I try to insert the character “’” as part of a string into > the sqlite database, my updates don’t work. Any ideas why? The same string > without the “’” character works. I have not debugged to see where exactly in > sqlite it fails. > > I’m inserting a text like this: “Rootuser’s desktop” does not work. “Rootuser > desktop” works, the update to database suceeds and I’m able to view it using > select. > > Regards, > Kavita > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The character "'" not liked by sqlite?
On 2 Mar 2010, at 6:51pm, Kavita Raghunathan wrote: > I notice that when I try to insert the character “’” as part of a string into > the sqlite database, my updates don’t work. Any ideas why? The same string > without the “’” character works. I have not debugged to see where exactly in > sqlite it fails. > > I’m inserting a text like this: “Rootuser’s desktop” does not work. “Rootuser > desktop” works, the update to database suceeds and I’m able to view it using > select. What API or toolkit are you using ? Have you tried executing the same command with the command-line tool ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] The character "'" not liked by sqlite?
I notice that when I try to insert the character “’” as part of a string into the sqlite database, my updates don’t work. Any ideas why? The same string without the “’” character works. I have not debugged to see where exactly in sqlite it fails. I’m inserting a text like this: “Rootuser’s desktop” does not work. “Rootuser desktop” works, the update to database suceeds and I’m able to view it using select. Regards, Kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] why is underscore like dash?
Oh snap! Well, the reason I bring it up is because fossil uses the LIKE operator to compare file names when adding new files to a fossil repository. If the file name you're adding has an underscore, then craziness ensues. Thanks for the quick answer and your patience with my ignorance. I'll take this up on the fossil list. RW Ron Wilson, Engineering Project Lead (o) 434.455.6453, (m) 434.851.1612, www.harris.com HARRIS CORPORATION | RF Communications Division assuredcommunications(tm) > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Schrum, Allan > Sent: Tuesday, March 02, 2010 12:50 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] why is underscore like dash? > > > Perhaps because underscore is considered to be a wild-card search > character. > > Take a look at: http://sqlite.org/lang_expr.html#like > > If you want to match underscore literally, use an optional escape > character clause and escape the underscore. > > -Allan > > > -Original Message- > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > > boun...@sqlite.org] On Behalf Of Wilson, Ronald > > Sent: Tuesday, March 02, 2010 10:41 AM > > To: General Discussion of SQLite Database > > Subject: [sqlite] why is underscore like dash? > > > > This test was performed on Windows XP: > > > > PS C:\Documents and Settings\ma088024> sqlite3 > > SQLite version 3.6.22 > > Enter ".help" for instructions > > Enter SQL statements terminated with a ";" > > sqlite> create table test (text); > > sqlite> insert into test values('_'); > > sqlite> insert into test values('-'); > > sqlite> select * from test where text like '-'; > > - > > sqlite> select * from test where text like '_'; > > _ > > - > > sqlite> .quit > > > > RW > > > > Ron Wilson, Engineering Project Lead > > (o) 434.455.6453, (m) 434.851.1612, www.harris.com > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] lemon mods
With all the good changes to lemon recently, I thought I'd post changes that I have made to my personal copy. One of my customers requires that I demonstrate the version of all build tools I use at build time, so I added a few command line parameters to help with that. It would be nice if the version identifier for lemon would increment as it matures, but I understand why it doesn't currently increment. It doesn't make sense to tie the version of lemon.exe to sqlite3.exe. I've toyed with using the build script to query fossil finfo lemon.c --limit 1 and use the artifact id or version id to uniquely identify a particular lemon build. RW Ron Wilson, Engineering Project Lead (o) 434.455.6453, (m) 434.851.1612, www.harris.com project-name: SQLite repository: C:/rev/fossil/sqlite3.f local-root: C:/rev/src/sqlite3/ user-home: C:/Documents and Settings/ma088024/Application Data project-code: 2ab58778c2967968b94284e989e43dc11791f548 server-code: dcd2b579c69c56d9973f76b372db9806df4a3252 checkout: 1e8b842039cc06b57a321226633c55b94eb8dcd7 2010-02-22 19:37:44 UTC parent: a8076aede33c07e9a2aaa05be8a888f37b45e41c 2010-02-22 19:32:32 UTC parent: 721f33e7221c5fc907e9e293ac3242843f4fcfb7 2010-02-17 20:31:32 UTC tags: trunk --- lemon.c +++ lemon.c @@ -4,10 +4,13 @@ ** single file to make it easy to include LEMON in the source tree ** and Makefile of another program. ** ** The author of this program disclaims copyright. */ + +#define SZVERSION "1.0" + #include #include #include #include #include @@ -99,10 +102,11 @@ enum option_type type; const char *label; char *arg; const char *message; }; +void OptVersion(void); int OptInit(char**,struct s_options*,FILE*); int OptNArgs(void); char *OptArg(int); void OptErr(int); void OptPrint(void); @@ -1395,10 +1399,12 @@ } /* The main program. Parse the command line and do it... */ int main(int argc, char **argv) { + static int help = 0; + static int versiononly = 0; static int version = 0; static int rpflag = 0; static int basisflag = 0; static int compress = 0; static int quiet = 0; @@ -1409,31 +1415,46 @@ {OPT_FLAG, "b", (char*)&basisflag, "Print only the basis in report."}, {OPT_FLAG, "c", (char*)&compress, "Don't compress the action table."}, {OPT_FSTR, "D", (char*)handle_D_option, "Define an %ifdef macro."}, {OPT_FSTR, "T", (char*)handle_T_option, "Specify a template file."}, {OPT_FLAG, "g", (char*)&rpflag, "Print grammar without actions."}, + {OPT_FLAG, "h", (char*)&help, "Print this help message."}, {OPT_FLAG, "m", (char*)&mhflag, "Output a makeheaders compatible file."}, {OPT_FLAG, "l", (char*)&nolinenosflag, "Do not print #line statements."}, {OPT_FLAG, "q", (char*)&quiet, "(Quiet) Don't print the report file."}, {OPT_FLAG, "s", (char*)&statistics, "Print parser stats to standard output."}, - {OPT_FLAG, "x", (char*)&version, "Print the version number."}, + {OPT_FLAG, "v", (char*)&version, "Print the version number and continue."}, + {OPT_FLAG, "x", (char*)&versiononly, "Print the version number and exit."}, {OPT_FLAG,0,0,0} }; int i; int exitcode; struct lemon lem; atexit(LemonAtExit); OptInit(argv,options,stderr); + + if ( help ) { + OptVersion(); + printf("Valid command line options are:\n"); + OptPrint(); + exit(0); + } + if( versiononly ){ + OptVersion(); + exit(0); + } if( version ){ - printf("Lemon version 1.0\n"); - exit(0); + OptVersion(); } if( OptNArgs()!=1 ){ fprintf(stderr,"Exactly one filename argument is required.\n"); + OptVersion(); + printf("Valid command line options are:\n"); + OptPrint(); exit(1); } memset(&lem, 0, sizeof(lem)); lem.errorcnt = 0; @@ -1821,10 +1842,15 @@ (*(void(*)(char *))(op[j].arg))(sv); break; } } return errcnt; +} + +void OptVersion() +{ + printf("Lemon version %s\n", SZVERSION); } int OptInit(char **a, struct s_options *o, FILE *err) { int errcnt = 0; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] why is underscore like dash?
Perhaps because underscore is considered to be a wild-card search character. Take a look at: http://sqlite.org/lang_expr.html#like If you want to match underscore literally, use an optional escape character clause and escape the underscore. -Allan > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Wilson, Ronald > Sent: Tuesday, March 02, 2010 10:41 AM > To: General Discussion of SQLite Database > Subject: [sqlite] why is underscore like dash? > > This test was performed on Windows XP: > > PS C:\Documents and Settings\ma088024> sqlite3 > SQLite version 3.6.22 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> create table test (text); > sqlite> insert into test values('_'); > sqlite> insert into test values('-'); > sqlite> select * from test where text like '-'; > - > sqlite> select * from test where text like '_'; > _ > - > sqlite> .quit > > RW > > Ron Wilson, Engineering Project Lead > (o) 434.455.6453, (m) 434.851.1612, www.harris.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] why is underscore like dash?
On Tue, Mar 2, 2010 at 9:41 AM, Wilson, Ronald wrote: > sqlite> select * from test where text like '_'; from http://www.sqlite.org/lang_expr.html > An underscore ("_") in the LIKE pattern matches any single character in the > string. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] why is underscore like dash?
> This test was performed on Windows XP: > > PS C:\Documents and Settings\ma088024> sqlite3 > SQLite version 3.6.22 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> create table test (text); > sqlite> insert into test values('_'); > sqlite> insert into test values('-'); > sqlite> select * from test where text like '-'; > - > sqlite> select * from test where text like '_'; > _ > - > sqlite> .quit > > RW > > Ron Wilson, Engineering Project Lead > (o) 434.455.6453, (m) 434.851.1612, www.harris.com Simplified: SQLite version 3.6.22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> select '_' like '-'; 0 sqlite> select '-' like '_'; 1 sqlite> .quit Ron Wilson, Engineering Project Lead (o) 434.455.6453, (m) 434.851.1612, www.harris.com HARRIS CORPORATION | RF Communications Division assuredcommunications(tm) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] why is underscore like dash?
>sqlite> select * from test where text like '_'; Underscore '_' is LIKE wildcard for any single character, percent '%' matches any substring. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] why is underscore like dash?
This test was performed on Windows XP: PS C:\Documents and Settings\ma088024> sqlite3 SQLite version 3.6.22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table test (text); sqlite> insert into test values('_'); sqlite> insert into test values('-'); sqlite> select * from test where text like '-'; - sqlite> select * from test where text like '_'; _ - sqlite> .quit RW Ron Wilson, Engineering Project Lead (o) 434.455.6453, (m) 434.851.1612, www.harris.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] possible buffer over-read in sqlite3VXPrintf()
On Mar 2, 2010, at 6:54 PM, Jonathan Kew wrote: > I've run into what appears to be a small bug in this function (from > sqlite3.c, v 3.6.22). Suggested patch: > > diff --git a/sqlite3.c b/sqlite3.c > --- a/sqlite3.c > +++ b/sqlite3.c > @@ -16938,17 +16938,17 @@ SQLITE_PRIVATE void sqlite3VXPrintf( > int i, j, k, n, isnull; > int needQuote; > char ch; > char q = ((xtype==etSQLESCAPE3)?'"':'\''); /* Quote > character */ > char *escarg = va_arg(ap,char*); > isnull = escarg==0; > if( isnull ) escarg = (xtype==etSQLESCAPE2 ? "NULL" : > "(NULL)"); > k = precision; > -for(i=n=0; (ch=escarg[i])!=0 && k!=0; i++, k--){ > +for(i=n=0; k!=0 && (ch=escarg[i])!=0; i++, k--){ > if( ch==q ) n++; > } > needQuote = !isnull && xtype==etSQLESCAPE2; > n += i + 1 + needQuote*2; > if( n>etBUFSIZE ){ > bufpt = zExtra = sqlite3Malloc( n ); > if( bufpt==0 ){ > pAccum->mallocFailed = 1; > > (The original code is found in src/printf.c.) > > The issue here is that when k reaches zero, the access to escarg[i] > may try to look one byte beyond the end of the allocated buffer; to > avoid this, simply reverse the order of the tests so that k is > checked for non-zero first. > > The error is normally harmless, testing a "random" byte and then > exiting the loop anyway because of the value of k, but it can cause > a bus error in the (extremely rare) event that the buffer is > allocated exactly at the end of a virtual memory page, and the > following page is unallocated. (This was encountered when running > under Guard Malloc.) Shouldn't escarg[] contain a nul-terminated string? How did you provoke the error under Guard Malloc? Do you have a stack trace? I'm thinking the error might be caused by some bug in the caller. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Type affinity changed by HAVING clause
Hi, I think I may have found a bug where affinities change through the HAVING expression. For example, under v3.6.22, if I do... create table t1(a text, b int); insert into t1 values(123, 456); select typeof(a), a from t1 group by a having ahttp://www.sqlite.org/cvstrac/tktview?tn=3493). Can anyone confirm that this is indeed a bug? I've not provided the sql statement I'm trying to run, but instead created a set of statements to match the style used in tkt3493.test to better isolate the problem as I see it. I trust this is more helpful. Thanks Will ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS & Doc Compression
My db definitely did go up in size with fts - which I think is ok just because that's what needs to be when using fts. So I'm not concerned so much about the stop words and things, although I agree that adjusting that list would definitely help. Since I'm on a mobile device, space is key. I think if I wasn't using fts, I'd still want to compress the db. But the fact that I need both has led me to try to figure this out. I was just wondering if by some chance someone had done the hard work already of wrapping in some compression functions into the amalgamation src - saves me some work. But if they haven't then this might be something I will have to do over the next few months. While there would probably be some sort of speed hit, I think compression, especially for mobile devices, would definitely be useful. In the case for the app I'm working on, our writes can afford to be slowest and therefore use the max compression, which could possibly give us a nice small db size. Alexey's code provided a good starting point, so I'll probably start there. Thanks for the reply. - jason On Tue, Mar 2, 2010 at 4:25 AM, Max Vlasov wrote: > On Tue, Mar 2, 2010 at 2:41 AM, Jason Lee wrote: > >> Hi all, >> >> I've been playing around with the FTS3 (via the amalgamation src) on a >> mobile device and it's working well. But my db file size is getting >> pretty big and I was looking for a way to compress it. >> > > > Jason, can you calculate the ratio between your text data and fts3 data? > From my tests it showed that fts eats not so much. For example, once I tried > en wikipedia abstracts as a test file (downloadable xml, I took title and > abstract from it), it's 3M records, 500M file without fts, after indexing > the size has changed to 1,5G. And I even didn't use stop-words. So with > proper stop-words usage the ratio can even be better. > > Max > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A question about sqlite : How could i get the rows affected by current sql statement ?
OK, now I see the problem, but sqlite3_total_changes() will not help here too - it behaves the same way as sqlite3_changes(), it doesn't accumulate changes over several statements. So without introducing some difference between SELECT queries and any data-changing queries in your program you won't be able to distinguish them using SQLite API. Maybe PRAGMA count_changes can be of any help (http://www.sqlite.org/pragma.html#pragma_count_changes) but I really doubt about it. Actually I believe it's pretty easy to distinguish it by simple looking into query text. SELECT statements always begin with the word "SELECT". Statements changing something in the database begin with words "INSERT", "REPLACE", "UPDATE" or "DELETE". Pavel On Tue, Mar 2, 2010 at 7:45 AM, Max Vlasov wrote: > On Mon, Mar 1, 2010 at 6:31 PM, Pavel Ivanov wrote: > >> sqlite3_changes() is exactly what you should use in this case. And I >> didn't understand why did you find it unsuitable for you? >> >> Pavel >> > > > I think I understand his confusion. Imagine if for some reason you don't > know whether last query is amongst INSERT, UPDATE, or DELETE (for example it > could be SELECT). But he probably wants that some call or fragment of code > return number of changes or 0 for any recent operation including SELECT. If > he just relies on sqlite3_changes() after INSERT with two rows affected and > simple SELECT afterward, this call will still return 2. In this case I'd > recommend using difference between consequitive sqlite3_total_changes() > values. For any read-only query this difference will always be zero. > > Max > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3BtreeSetPageSize() compile error with SQLITE_OMIT_PAGER_PRAGMAS and SQLITE_OMIT_VACUUM
The current Fossil trunk [dd4962aa34] does not compile with both * SQLITE_OMIT_PAGER_PRAGMAS * SQLITE_OMIT_VACUUM enabled. These defines exclude btree.c sqlite3BtreeSetPageSize(), but it is still referenced from build.c. The problem was introduced by Check-in [5dcfb0c9e4]: "Make the TEMP file tables use the page size set for the main database." Here is the link: http://www.sqlite.org/src/ci/5dcfb0c9e420d27e54a299b3991b98776651a507 Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] possible buffer over-read in sqlite3VXPrintf()
I've run into what appears to be a small bug in this function (from sqlite3.c, v 3.6.22). Suggested patch: diff --git a/sqlite3.c b/sqlite3.c --- a/sqlite3.c +++ b/sqlite3.c @@ -16938,17 +16938,17 @@ SQLITE_PRIVATE void sqlite3VXPrintf( int i, j, k, n, isnull; int needQuote; char ch; char q = ((xtype==etSQLESCAPE3)?'"':'\''); /* Quote character */ char *escarg = va_arg(ap,char*); isnull = escarg==0; if( isnull ) escarg = (xtype==etSQLESCAPE2 ? "NULL" : "(NULL)"); k = precision; -for(i=n=0; (ch=escarg[i])!=0 && k!=0; i++, k--){ +for(i=n=0; k!=0 && (ch=escarg[i])!=0; i++, k--){ if( ch==q ) n++; } needQuote = !isnull && xtype==etSQLESCAPE2; n += i + 1 + needQuote*2; if( n>etBUFSIZE ){ bufpt = zExtra = sqlite3Malloc( n ); if( bufpt==0 ){ pAccum->mallocFailed = 1; (The original code is found in src/printf.c.) The issue here is that when k reaches zero, the access to escarg[i] may try to look one byte beyond the end of the allocated buffer; to avoid this, simply reverse the order of the tests so that k is checked for non-zero first. The error is normally harmless, testing a "random" byte and then exiting the loop anyway because of the value of k, but it can cause a bus error in the (extremely rare) event that the buffer is allocated exactly at the end of a virtual memory page, and the following page is unallocated. (This was encountered when running under Guard Malloc.) Jonathan Kew ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Maybe just a Question
Is there something that I do not know about the protocol around here? http://sqlite.org:8080/cgi-bin/mailman/confirm/sqlite-users/ff1e1920f75999f00d53fb1c451753e70393fcf7 URL says that the item has expired after 3 days. How do I find out what happened? Where do i go to find out the disposition of my e-mail? Thank you Begin forwarded message: > From: Ray Irvine > Date: March 1, 2010 8:52:27 AM PST > To: sqlite-users@sqlite.org > Subject: Fwd: Maybe just a Question > >> I did not get an e-mail response and the status check URL has expired. >> Thank you >> >> I have found the following: >> >> BEGIN; UPDATE entry SET Spinnaker="No" ,Singlehand="Doublehand" >> ,Doublehand="Third" ,Multi="Multihull" ,Furler=" " WHERE ROWID="45"; COMMIT >> >> Results in the value of the column Singlehand to be set to the current value >> on the column Doublehand for this ROWID. >> >> Experiment show that when the value of the Column (say A) is the same as the >> name of another column (say B), the first column (A) is set to the current >> value of the second (B). >> >> THis is all being run on an IMAC using MACOS X version 10.6.2. I have not >> run these test on another a system. >> >> Is there something that I am missing? >> >> Thank you for your assistance. >> >> Best Regards, >> >> Ray >> >> Ray Irvine >> Crew's Nest - #1383 >> c34irvine1...@comcast.net >> http://www.flickr.com/photos/crews_nest/ >> >> >> >> >> >> > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BLOBs' affect on DB performance
Hello Paul, My experiences with blobs suggests it's better to keep them in a different DB file. My uses sounded very similar to yours, tables of normal data interleaved with blob inserts. The physical process of having to move from page to page seems to be the bottleneck, not Sqlite itself. I was working with multi-gig DB files so, this might not be as true for smaller files. C Tuesday, March 2, 2010, 2:54:06 AM, you wrote: PV> Hi there, PV> I'm wondering how larger BLOBs in a database affect performance PV> of accessing the non-blob data. We've got a database with PV> potentially a few million records in the main tables (of just PV> strings and ints), but joined to that data set we want to store up PV> to thousands (maybe 75000 max) of blobs ranging in size from 75kB PV> - 4MB. If it comes down to it, we can use the filesystem for PV> these, but we'd prefer to store them in a database if it didn't PV> bog things down (cleaner to manage implementation-wise and PV> user-experience-wise).Now I'm guessing that storing all those PV> blobs will slow down access to the main tables (assuming records PV> are added gradually - most without associated blobs, some with), PV> because records would be spread out over many more pages (more PV> seeking / disk access) - is that correct?Would performance PV> likely be better if the blob table were stored in a separate PV> database file? Also, is it possible to adjust page size on a per-database basis PV> (I'm thinking larger pages for the blob database would be PV> better, right?)? Any other suggestions to do this efficiently? PV> Sorry if this is answered elsewhere; when searching, most of the PV> BLOB performance posts I've seen are about storing / retrieving PV> the blobs. We're not so worried about the time to retrieve / PV> store the blobs as much as time to access the main tables. PV> Thanks! PV> Paul PV> ___ PV> sqlite-users mailing list PV> sqlite-users@sqlite.org PV> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A question about sqlite : How could i get the rows affected by current sql statement ?
On Mon, Mar 1, 2010 at 6:31 PM, Pavel Ivanov wrote: > sqlite3_changes() is exactly what you should use in this case. And I > didn't understand why did you find it unsuitable for you? > > Pavel > I think I understand his confusion. Imagine if for some reason you don't know whether last query is amongst INSERT, UPDATE, or DELETE (for example it could be SELECT). But he probably wants that some call or fragment of code return number of changes or 0 for any recent operation including SELECT. If he just relies on sqlite3_changes() after INSERT with two rows affected and simple SELECT afterward, this call will still return 2. In this case I'd recommend using difference between consequitive sqlite3_total_changes() values. For any read-only query this difference will always be zero. Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS & Doc Compression
On Tue, Mar 2, 2010 at 2:41 AM, Jason Lee wrote: > Hi all, > > I've been playing around with the FTS3 (via the amalgamation src) on a > mobile device and it's working well. But my db file size is getting > pretty big and I was looking for a way to compress it. > Jason, can you calculate the ratio between your text data and fts3 data? >From my tests it showed that fts eats not so much. For example, once I tried en wikipedia abstracts as a test file (downloadable xml, I took title and abstract from it), it's 3M records, 500M file without fts, after indexing the size has changed to 1,5G. And I even didn't use stop-words. So with proper stop-words usage the ratio can even be better. Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BLOBs' affect on DB performance
On Tue, Mar 2, 2010 at 10:54 AM, Paul Vercellotti wrote: > Now I'm guessing that storing all those blobs will slow down access to the > main tables (assuming records are added gradually - most without associated > blobs, some with), because records would be spread out over many more pages > (more seeking / disk access) - is that correct? > It depends on your operations. For example querying simple SELECT rowid from table actually means reading all the pages with the table data (but not the overflow pages when a record does not fit in one page). And for such query lowering the size of the record will lead to fewer pages, lower fragmentation so faster access/reading. But everything changes if your query involves indexes, since starting this your performance will depend not on the size of the original record, but the size of the "index" record (the fields that is listed in CREATE INDEX). This one's because the sqlite index actually contains only the data it needs + rowid and to effectively filter something related to index data sqlite does not need the main table data. Ironically that post and tests allowed me to find out that creating separate index on rowid alias (one that has something like [Id] Primary Key Autoincrement in CREATE TABLE) makes sense and the SELECT I mentioned in the beginning being changed to SELECT Id FROM Table Order By Id started to work faster and the time no longer depended on the size of the record. This was possible thanks to this new index that was absolute wasting of space (duplicating to the primary index), but gaining advantages from the facts that it was effectively packed in much fewer pages. So, my suggestion for you is to analyze your scheme and future queries and if expensive parts of them involves just indexes (based on anything but your blobs) and these blobs are accessed on some final stage of the query, go with blobs inside sqlite base. Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE
On 2 March 2010 11:31, Matt Eeles wrote: > Hi, > > > > I'm trying to update a field of the last record using UPDATE and MAX(). > The following query parses ok but updates all records. Any reason why ? > > > > UPDATE logs SET Stop = DATETIME('NOW') WHERE (SELECT MAX(ID) FROM logs) (SELECT MAX(ID) FROM logs) will only ever be false if the maximum id is 0 perhaps you want 'WHERE ID = (SELECT MAX(ID) FROM logs)' > > > > Thanks, > > > > Matt. > Rgds, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE
On 2 Mar 2010, at 11:31am, Matt Eeles wrote: > I'm trying to update a field of the last record using UPDATE and MAX(). > The following query parses ok but updates all records. Any reason why ? > > > > UPDATE logs SET Stop = DATETIME('NOW') WHERE (SELECT MAX(ID) FROM logs) Your WHERE clause tells it to. SQLite is updating all records where the WHERE clause evaluates to TRUE. What you want might be more like ... WHERE (SELECT MAX(ID) FROM logs) = ID I'm at work and can't test it right now. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UPDATE
Hi, I'm trying to update a field of the last record using UPDATE and MAX(). The following query parses ok but updates all records. Any reason why ? UPDATE logs SET Stop = DATETIME('NOW') WHERE (SELECT MAX(ID) FROM logs) Thanks, Matt. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Possible Corruption for DB malformation
Hi, Below is the part of code which gives error, when running the corrupted DB with gdb, i guess this may help to find the root cause for DB corruption, The DB was able to recover with "vacuum" command also. SQLite version 3.6.22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> pragma integrity_check; *** in database main *** Main freelist: 21 of 21 pages missing from overflow list starting at 0 Page 1604: btreeInitPage() returns error code 11 Page 1461 is never used Page 1468 is never used Page 1469 is never used Page 1472 is never used Page 1473 is never used Page 1474 is never used Page 1475 is never used Page 1478 is never used Page 1480 is never used Page 1482 is never used Page 1484 is never used Page 1485 is never used Page 1486 is never used Page 1488 is never used Page 1489 is never used Page 1491 is never used Page 1517 is never used Page 1531 is never used Page 1536 is never used Page 1578 is never used Page 1581 is never used (gdb) b btreeInitPage Breakpoint 1 at 0x40031130: file sqlite3.c, line 38929. (gdb) list ... .. (gdb) 39024 if( nFree>usableSize ){ 39025 return SQLITE_CORRUPT_BKPT; 39026 } 39027 pPage->nFree = (u16)(nFree - iCellFirst); 39028 pPage->isInit = 1; 39029 } 39030 return SQLITE_OK; 39031 } 39032 39033 /* (gdb) b 39025 Breakpoint 2 at 0x4003134c: file sqlite3.c, line 39025. (gdb)del 1 (gdb) c Continuing. Breakpoint 2, btreeInitPage (pPage=0x82986d4) at sqlite3.c:39025 39025 return SQLITE_CORRUPT_BKPT; (gdb) bt #0 btreeInitPage (pPage=0x82986d4) at sqlite3.c:39025 #1 0x4003b0fb in checkTreePage (pCheck=0xbfffced0, iPage=1604, zParentContext=0xbfffcda0 "On page 226 at right child: ") ... ... (gdb) print nFree $1 = 0 (gdb) print usableSize $2 = 2053 (gdb) This shows it returns from here if( nFree>usableSize ){ return SQLITE_CORRUPT_BKPT; } Thanks and Regards, Ramesh Please do not print this email unless it is absolutely necessary. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users