[sqlite] Maximum database size?

[Collin Capano]

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

[Simon Slavin]

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?

[Simon Slavin]

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 ?

[Pavel Ivanov]

> 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

[Pavel Ivanov]

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

[Jens Frøkjær]

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?

[Adam DeVita]

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 ?

[Max Vlasov]

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?

[Kavita Raghunathan]

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?

[A.]

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?

[Simon Slavin]

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?

[Kavita Raghunathan]

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?

[Wilson, Ronald]

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

[Wilson, Ronald]

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*), "Print only the basis in report."},
 {OPT_FLAG, "c", (char*), "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*), "Print grammar without actions."},
+    {OPT_FLAG, "h", (char*), "Print this help message."},
 {OPT_FLAG, "m", (char*), "Output a makeheaders compatible file."},
 {OPT_FLAG, "l", (char*), "Do not print #line statements."},
 {OPT_FLAG, "q", (char*), "(Quiet) Don't print the report file."},
 {OPT_FLAG, "s", (char*),
    "Print parser stats to standard output."},
-    {OPT_FLAG, "x", (char*), "Print the version number."},
+    {OPT_FLAG, "v", (char*), "Print the version number and continue."},
+    {OPT_FLAG, "x", (char*), "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(, 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?

[Schrum, Allan]

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?

[Scott Hess]

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?

[Wilson, Ronald]

> 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?

[Jean-Christophe Deschamps]

>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?

[Wilson, Ronald]

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()

[Dan Kennedy]

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

[WClark]

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 a

Re: [sqlite] FTS & Doc Compression

[Jason Lee]

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 ?

[Pavel Ivanov]

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

[Ralf Junker]

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()

[Jonathan Kew]

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

[Ray Irvine]

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

[Teg]

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 ?

[Max Vlasov]

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

[Max Vlasov]

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

[Max Vlasov]

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

[Simon Davies]

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

[Simon Slavin]

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

[Matt Eeles]

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

[ramesh.kotabagi]

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