Re: [sqlite] ftruncate implementation

[David Garfield]

no, it is more handwaving.  The dirty marking you describe will be
done for a partial page at the old end of the file.  Any additional
pages will normally be left unallocated, and the operating system will
provide zeros at read time when you try to read them.  This makes them
appear zero-filled, without actually zero filling anything.

Having said that, I would expect certain operating systems (like DOS)
to actually do the zero-filling and complete it before returning from
the call.

--David Garfield

On Tue, Jan 24, 2012 at 14:00, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 24 Jan 2012, at 6:51pm, David Garfield wrote:
>
>> From the ftruncate page: "If the file size is increased, the extended
>> area shall appear as if it were zero-filled".
>> It doesn't have to write zeros, just act like it did.
>
> That's a bit of handwaving.  What happens is that the file system doesn't 
> itself write zeros to the disk surface.  It puts zeros into the cache of the 
> sector in memory and marks that sector as dirty (i.e. needing to eventually 
> be written back to disk).  So if the program doesn't write to that sector and 
> just releases it, those zeros do eventually get written to disk.  But if the 
> program does write to that sector the disk surface only gets touched once, 
> not twice, which is faster.
>
> So it doesn't write zeros to disk, but it does write zeros to the bit of 
> memory that represents that bit of disk, and arrange that (unless the program 
> overwrites them) they'll be written to disk eventually.
>
> Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ftruncate implementation

[David Garfield]

>From the ftruncate page: "If the file size is increased, the extended
area shall appear as if it were zero-filled".
It doesn't have to write zeros, just act like it did.

--David Garfield

On Tue, Jan 24, 2012 at 08:19, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 24 Jan 2012, at 6:43am, David Henry wrote:
>
>> I am working without an operating system so there are no other agents trying
>> to steal data.
>
> You're writing an operating system ?  That can be lots of fun.  Good luck.
>
>> Bearing that in mind, is it still necessary to actually write
>> zero data to the sectors allocated? Is SQLite expecting it?
>
> I haven't looked though the code with that in mind, but as far as I know, 
> SQLite does not make any assumption about what will be in newly-assigned 
> sectors.  If it wants zeros there it'll write them itself.
>
> It is ftruncate itself which chooses to write zeros to any newly-assigned 
> pages of disk space.  This is part of the specification of ftruncate, and 
> documented here:
>
> <http://pubs.opengroup.org/onlinepubs/007908799/xsh/ftruncate.html>
>
> If your version of ftruncate doesn't write the zeros, you haven't implemented 
> ftruncate properly.  But if you're not trying to reproduce UNIX, I guess it 
> doesn't matter.
>
> 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] Bin parameters by name - generic function

[David Garfield]

I believe the secret is: don't bother.

sqlite does type conversion as needed, so you can just pass the strings
you've parsed out of your larger string to sqlite3_bind_text().

If the issue is that you need to decode your TIMESTAMP1 column to get the
same format as TIMESTAMP2, and you want to use the datatype listed on the
column as a hint, then I can't help you.

--David Garfield

On Tue, Jan 17, 2012 at 19:32, Bill McCormick <wpmccorm...@gmail.com> wrote:

> I'm trying to write a function with a sig like this:
>
> int BindParameter(sqlite3_stmt* stmt, int sqlType, const char* pname,
> char* value);
>
> Somewhere before the call(s) to BindParameter, I'll have a string that
> looks like this:
>(FOO=some text value, BAR=3.141, ZIP=45, TIMESTAMP1=01/17/12 17:54:00,
> TIMESTAMP2=542312453423)
>
> My parameter values will always be input from a char string (as above),
> but the data types in the table are INTEGER, TEXT, REAL and NUMERIC. I was
> thinking that there must already be some easy way of getting to the correct
> bind call. In the end, I would like to have something like this inside the
> BindParameter function:
>
> case INTEGER:
>  sqlite3_bind_int(stmt, sqlite3_bind_parameter_index(**stmt, pname),
> data);
>
> case REAL:
>sqlite3_bind_double(stmt, sqlite3_bind_parameter_index(**stmt, pname),
> data);
>
> case TEXT:
>sqlite3_bind_text(stmt, sqlite3_bind_parameter_index(**stmt, pname),
> value);
>
> case NUMERIC:
>??? I don't see a bind
>
> This seems like something somebody would have come across  before, so I'd
> rather not re-invent.
>
> My questions are:
> 1. Is there already some other way of doing what I want? Or am I already
> on the right track?
> 2. If, is there already some enum or #define that would work for sqlType?
>(SQLITE_INTEGER, SQLITE_FLOAT, SQLITE_BLOB, SQLITE_NULL, SQLITE_TEXT
> don't seem to cover every DT)
> 3. What is the correct bind call for a NUMERIC parameter?
>
> Thanks!!!
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<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] Using non-aggregate columns in group by (with an additional issue)

[David Garfield]

A  very minor change on your query is all that is needed.

select max(x.timestamp),x.value,x.person from table as x,
(select person,max(value) as maxvalue from table group by person order by
maxvalue desc) as y
where x.person = y.person
and x.value = y.maxvalue
group by x.person, x.value


I don't know how this compares in terms of performance with Igor's solution.

--David Garfield

On Fri, Jan 13, 2012 at 06:07, Dilip Ranganathan <misc.us...@gmail.com>wrote:

> I have a table that looks like something like this:
>
>timestampvalue   person
>===
>2010-01-12 00:00:00   33  emp1
>2010-01-12 11:00:00   22  emp1
>2010-01-12 09:00:00   16  emp2
>2010-01-12 08:00:00   16  emp2
>2010-01-12 12:12:00   45  emp3
>2010-01-12 13:44:00   64  emp4
>2010-01-12 06:00:00   33  emp1
>2010-01-12 15:00:00   12  emp5
>
> I wanted to find the maximum value associated with each person. The obvious
> query was:
>
> select person,max(value) from table group by person
>
> Now I wanted to include the timestamp associated with each max(value). I
> could not use timestamp column in the above query because as everyone
> knows, it won't appear in the group by clause. So I wrote this instead:
>
> select x.timestamp,x.value,x.person from table as x,
> (select person,max(value) as maxvalue from table group by person order by
> maxvalue desc) as y
> where x.person = y.person
> and x.value = y.maxvalue
>
> This works -- to an extent. I now see:
>
>timestampvalue   person
>===
>2010-01-12 13:44:00   64  emp4
>2010-01-12 12:12:00   45  emp3
>2010-01-12 06:00:00   33  emp1
>2010-01-12 00:00:00   33  emp1
>2010-01-12 08:00:00   16  emp2
>2010-01-12 09:00:00   16  emp2
>2010-01-12 15:00:00   12  emp5
>
> The problem is now I get all the entries for emp1 and emp2 that ends up
> with the same max(value).
>
> Suppose among emp1 and emp2, I only want to see the entry with the latest
> timestamp. IOW, I want this:
>
>timestampvalue   person
>===
>2010-01-12 13:44:00   64  emp4
>2010-01-12 12:12:00   45  emp3
>2010-01-12 06:00:00   33  emp1
>2010-01-12 09:00:00   16  emp2
>2010-01-12 15:00:00   12  emp5
>
> What kind of query would I have to write? Is it possible to extend the
> nested query I wrote to achieve what I want or does one have to rewrite
> everything from the scratch?
>
> If its important, timestamps are actually stored as julian days. I use the
> datetime() function to convert them back to a string representation in
> every query.
> ___
> 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] Named parameters and spaces.

[David Garfield]

The named parameters need not match the column names, and the column names
are probably what you want to match the CSV file.  As such, you could make
P1 throught P25 to be the named parameters for your 25 columns, and insert
them into columns with the real name.  The named parameters are there only
so that the SQL command and the API user can be matched up, particularly
when one or more value need to be reused.

--David Garfield

On Wed, Jan 11, 2012 at 19:01, Steven Michalske <smichal...@gmail.com>wrote:

> On Tue, Jan 10, 2012 at 4:11 PM, Simon Slavin <slav...@bigfraud.org>
> wrote:
> >
> > On 10 Jan 2012, at 11:57pm, Steven Michalske wrote:
> >
> >> Some day they might have a field that is named with the underscore as
> >> a second column; not saying it makes sense.  We find all kinds on the
> >> internet :-)
> >
> > Well some day they might have a field that is named with a space, too.
>  There's nothing in SQLite to forbid it.  The real solution is to use named
> parameters the way they were designed, not try to match the name of the
> parameter with the name of the field.
>
> This statements is not quite true.
> I want to auto generate the fields based on csv files that may or may
> not have spaces in them.
> This makes the named parameter match the column names in the CSV file.
>  I used the python csv DictReader object that creates a key value
> mapping for column name and value.  i wanted to not have to manipulate
> the field names and such.
> ___
> 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] primary key and rowid

[David Garfield]

Simon Slavin writes:
> 
> On 19 Dec 2011, at 7:04am, YJM YAN wrote:
> > //Second way create primary key:
> > CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x AUTOINCREMENT));
> > "x" being an alias for the rowid?
> 
> Second way will work too.  It correctly declares "x INTEGER" and SQLite 
> understands this.  See
> 
> <http://www.sqlite.org/lang_createtable.html#rowid>
> 
> Simon.

If I am reading that page correctly, AUTOINCREMENT is not permitted in
this context.  Is that right?  Or are the diagrams incomplete in this?
(I will admit that it doesn't really make sense for a multi-column
constraint.)

--David Garfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PHP & SQLite examples

[David Garfield]

Please, be correct.  There are no secure ways to communicate.  Period.

Neither "persistent SQL datastores" or "websockets" will get you
secure communication.  Nor will SSL, or anything else.

Paranoia is essential in writing communication software.  (Of course,
paranoia is important in writing any software, but in communications,
you can't even trust yourself.)

http://xkcd.com/327/

--David Garfield

Simon Slavin writes:
> 
> On 14 Nov 2011, at 5:53pm, Dotan Cohen wrote:
> 
> > I recommend against formulating the SQL statements in Javascript.
> > Because if I find that page, I _will_ try to inject my own SQL.
> 
> My code on the PHP side executes only the first SQL command.  And
> there a hash.  But yes, people should be careful with doing things
> like that.
> 
> Unfortunately there are no secure ways to communicate between
> JavaScript and PHP.  Because whatever you do, you're still sending a
> text string from one to another.  You might have a protocol that the
> text string is XML or JSON but when it comes down to it, you hacker
> will figure that out too.  It's a nasty security problem with
> AJAX/SOAP/REST web apps which will be solved only when we all move
> to persistent SQL datastores or to websockets, both of which are in
> HTML5.
> 
> 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] Help with sqlite3OsRead - porting to a new OS

[David Garfield]

Also, if this is debugger output (as it appears), it could be that an
optimizer is confusing the parameters.  I see that all the time on GCC
i386 when I set a breakpoint at the start of a function.

--David

David Garfield writes:
> Sounds like it could be a difference in calling convention...  Check
> compile options and function declaration modifiers.
> 
> --David
> 
> Stuart Thomson writes:
> > Hi,
> > 
> > I'm in the middle of porting sqlite3 to a new Operating System and have 
> > come across a problem with the sqlite3OsRead function in the 
> > sqlite3PagerReadFileheader function body.
> > 
> > Before the sqlite3OsRead function is called, the parameters are correctly 
> > populated
> > 
> > for example:
> > pPager->fd= \u565c,  0x6208565c
> > pDest= \ufea8,   0x2000fea8
> > N  = d,  0x0064
> > Offset= 0
> > 
> > But when the function sqlite3OsRead is actually implemented the parameters 
> > are in the incorrect order. More specifically:
> > 
> > Sqlite3_file *id= d,  0x0064
> > Void *pBuf = \u565c,  0x6208565c
> > Int amt  = \ufea8,   0x2000fea8
> > I64 offset   = \0,  0x2000fea8
> > 
> > I have built a Database using sqlite3 3.7.5 and the code is built to 3.7.5.
> > 
> > Any ideas are welcome,
> > 
> > Cheers
> > Stuart
> > 
> > 
> > 
> > BitWise Ltd - Crescent House, Carnegie Campus, Dunfermline, KY11 8GR, 
> > United Kingdom
> > tel: +44 (0)1383 625151   -mob:
> > web: BitWise Group <http://www.bitwisegroup.com>
> > 
> > This e-mail may be confidential and privileged. Do not open it if you are 
> > in any doubt that you are the intended recipient. You must scan this e-mail 
> > and any attachments for the presence of viruses or any other unwelcome 
> > content. This e-mail must be read in conjunction with the important legal 
> > notice at BitWise Group/Legal <http://www.bitwisegroup.com/legal>
> > ___
> > 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] Help with sqlite3OsRead - porting to a new OS

[David Garfield]

Sounds like it could be a difference in calling convention...  Check
compile options and function declaration modifiers.

--David

Stuart Thomson writes:
> Hi,
> 
> I'm in the middle of porting sqlite3 to a new Operating System and have come 
> across a problem with the sqlite3OsRead function in the 
> sqlite3PagerReadFileheader function body.
> 
> Before the sqlite3OsRead function is called, the parameters are correctly 
> populated
> 
> for example:
> pPager->fd= \u565c,  0x6208565c
> pDest= \ufea8,   0x2000fea8
> N  = d,  0x0064
> Offset= 0
> 
> But when the function sqlite3OsRead is actually implemented the parameters 
> are in the incorrect order. More specifically:
> 
> Sqlite3_file *id= d,  0x0064
> Void *pBuf = \u565c,  0x6208565c
> Int amt  = \ufea8,   0x2000fea8
> I64 offset   = \0,  0x2000fea8
> 
> I have built a Database using sqlite3 3.7.5 and the code is built to 3.7.5.
> 
> Any ideas are welcome,
> 
> Cheers
> Stuart
> 
> 
> 
> BitWise Ltd - Crescent House, Carnegie Campus, Dunfermline, KY11 8GR, United 
> Kingdom
> tel: +44 (0)1383 625151   -mob:
> web: BitWise Group 
> 
> This e-mail may be confidential and privileged. Do not open it if you are in 
> any doubt that you are the intended recipient. You must scan this e-mail and 
> any attachments for the presence of viruses or any other unwelcome content. 
> This e-mail must be read in conjunction with the important legal notice at 
> BitWise Group/Legal 
> ___
> 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 by creating function is not sorting for input order and alphabet

[David Garfield]

Suggestions:

1) Try:

   select cli_id, antok(cli_id) as antok_cli_id .. order by antok_cli_id

   This should at least show you exactly what it thinks it sorted.

2) Don't do regcomp() more than once.  It can be very costly.  And
   cache input.c_str() and input.length() while you are at it.
   (Actually... should it really be a "string" in the first place?)

3) digits[] may be insufficient in some cases (though not in your
   samples).

--David Garfield

ChingChang Hsiao writes:
> 
> The purpose of function antok is that solve the problem of "order by cli_id".
> 
> Function antok converts,
> 
> X86-1  -> x186-01
> X86-2  -> x186-02
> X86-10 -> x186-110
> X86-111 -> x186-2111
> X86- -> x186-3
> 1.1/12-ds3 -> 01.01/112-ds03
> 
> Examples,
> Input cli_id   x86-2, x86-10, x86-1
> 
> Order by cli_id
> X86-1
> X86-10
> X86-2
> 
> Order by antok(cli_id)
> X86-2
> X86-1
> X86-10
> 
> Antok solves the problem of x86-1,x86-10 sorting, but it didn't sort x86-2, 
> x86-1(input order).
> 
> My solution is,
> 
> order by antok(cli_id) asc,cli_id asc
> 
> X86-1
> X86-2
> X86-10
> 
> 
> But It solves the same category, there are still problems for alphabet shown 
> as below.
> 
> 0.1
> 0.2
> 0.3
> 0.4
> 0.5
> x86-1
> x86-2
> chdlc-1
> chdlc-2
> chdlc-3
> x86-10
> 
> It should be,
> 
> 0.1
> 0.2
> 0.3
> 0.4
> 0.5
> chdlc-1
> chdlc-2
> chdlc-3
> x86-1
> x86-2
> x86-10
> 
> Do you know why x86-1,x86-2 jump ahead chdlc-1 when I use "order by 
> antok(cli_id)" or "order by antok(cli_id) asc,cli_id asc"?
> 
> 
> ChingChang
> 
> Here are the source code shown as below.
> 
> int32
> DbHandle::registerOvnFuncs( sqlite3* db ) {
> 
>   // Add a alpha numeric token generator
>   sqlite3_create_function(db, "antok", 1, SQLITE_UTF8, NULL,
>   , NULL, NULL);
>   return 0;
> }
> 
> static void
> dbAnTokenFunc( sqlite3_context *context, int argc, sqlite3_value **argv )
> {
>   assert( argc==1 );
>   switch( sqlite3_value_type(argv[0]) ){
>   case SQLITE_INTEGER:
> sqlite3_result_int64( context, sqlite3_value_int64(argv[0]) );
> break;
>   case SQLITE_NULL:
> sqlite3_result_null( context );
> break;
>   case SQLITE_TEXT:
>   default:
> SqlSortHelper sqlSortHelper;
> string token;
> token = sqlSortHelper.GetAlphaNumericOrderToken( 
> (char*)sqlite3_value_text(argv[0]) );
> sqlite3_result_text( context, token.c_str(), token.length(), NULL );
> break;
>   }
> }
> 
> #define POTENTIAL_MATCHES 1
> 
> string SqlSortHelper::GetAlphaNumericOrderToken( string input ) {
>   regex_t re;
>   regmatch_t pmatch[POTENTIAL_MATCHES];
>   string token;
>   uint32 pos = 0;
>   char digits[10] = "012345678";
> 
>   memset( pmatch, -1, sizeof(pmatch) );
>   regcomp( , "[0-9]+", REG_EXTENDED);
>   while (pos < input.length())
>   {
> regexec( , input.c_str()+pos, 1, pmatch, 0);
> if ( pmatch[0].rm_so != -1 )
> {
> token.append( (input.c_str() + pos), pmatch[0].rm_so);
> token.push_back( digits[((pmatch[0].rm_eo-pmatch[0].rm_so)-1)] );
> token.append( (input.c_str() + pos + pmatch[0].rm_so), 
> pmatch[0].rm_eo - pmatch[0].rm_so );
> pos = pos + pmatch[0].rm_eo;
> }
> else
> {
> if (pos == 0) {
> regfree( );
> return input;
> }
> else
> break;
> }
>   }
>   if (pos < input.length())
> token.append( (input.c_str() + pos), input.length()-pos );
>   regfree( );
>   return token;
> }
> 
> ___
> 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] Doc bug in on-line description of round() function

[David Garfield]

I think the use of "integer" was the mathematical concept, not the
sqlite concept.  That makes that part of the description correct.

There are other possible issues:

1) It says "truncated" not "rounded".   1.7 truncated is 1, rounded is 2.

2) It says nothing about negative values of Y.  For me at least, these
   are treated as 0.  This might be system dependent or undefined
   behavior.

3) It says nothing about non-integer values of Y.  For me at least,
   they are truncated (not rounded), so round(1.234,1.8) is 1.2.
   Again, this might be system dependent or undefined behavior.

--David Garfield

Peter Aronson writes:
> Here's where I let my pedantic side out to play.  The documentation for the 
> round() function on the SQLite website at 
> http://www.sqlite.org/lang_corefunc.html says:
> 
> "The round(X,Y) function returns a string representation of the 
> floating-point 
> value X rounded to Y digits to the right of the decimal point. If the Y 
> argument 
> is omitted, the X value is truncated to an integer."
> 
> However, if you actually say execute this SQL:
> 
> select typeof(round(1.1));
> 
> You get real as a result, not text.  Looking at roundFunc()'s source code in 
> sqlite3.c (from func.c) it indeed calls sqlite3_result_double(), not 
> sqlite3_result_text().
> 
> The "Using SQLite" book, I notice gets it right, however.
> 
> Best,
> 
> Peter
> ___
> 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] sqlite3_open_v2 vs. *ppDb=NULL

[David Garfield]

The documentation also says (http://sqlite.org/c3ref/open.html):

   If the 3rd parameter to sqlite3_open_v2() is not one of the
   combinations shown above optionally combined with other
   SQLITE_OPEN_* bits then the behavior is undefined.

Seems like the undefined behavior was non-dangerous.

That sqlite3_errmsg() had to guess and guessed wrong is not
surprising.

--David Garfield

Krystian Bigaj writes:
> Hi,
> 
> Documentation says that:
> 
> "The only exception is that if SQLite is unable to allocate memory to hold
> the sqlite3 object, a NULL will be written into *ppDb instead of a pointer
> to the sqlite3 object"
> 
> but I see it's not entirely true. This example below stores NULL in *ppDb
> even when (I think) no memory allocation problems occurs:
> 
> void TestOpenMisuse(void)
> {
>   sqlite3 *ppDb;
>   int err = sqlite3_open_v2("whatever", , SQLITE_OPEN_CREATE , 0);
>   if (ppDb == NULL)
>   {
> printf("ppDb==NULL, but err==%d, sqlite3_errmsg(ppDb)==%s",
> err, sqlite3_errmsg(ppDb));
>   }
> }
> 
> 
> Error code in this case will be err==21 (SQLITE_MISUSE) because
> flag SQLITE_OPEN_CREATE cannot be there without SQLITE_OPEN_READWRITE -
> which is docummented.
> But sqlite3_errmsg(ppDb) will return "out of memory" (because ppDb==NULL),
> which can be sometimes little misleading (for me as a SQLite newbie it was
> ;).
> 
> SQLite 3.7.8 (amalgamation)
> 
> -- 
> Best regards,
> Krystian Bigaj
> ___
> 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] Using COUNT(*) in unusual circumstances

[David Garfield]

Puneet Kishor writes:
> Especially, note Pavel's recent, wonderful explanation of how SQLite steps 
> through the result set handing you the data row by row. It *has* to go 
> through the set to know how big the set is... there is no way around it.
> 

Actually, sometimes there are.  Consider:

SELECT COUNT(*) FROM t1, t2;

SELECT (SELECT COUNT(*) FROM t1) * (SELECT COUNT(*) FROM t2);

Of course, those are special cases.  Application programmers should
probably watch for this kind of thing, but it probably wasn't what the
programmer wanted to count in the first place.

--David Garfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using COUNT(*) in unusual circumstances

[David Garfield]

WHERE or HAVING clauses that refer back to named results could be a
problem with the simple replacement.

SELECT a,b,a+b AS ab FROM t WHERE ab>10

Igor's suggestion work there too.   

--David Garfield

Simon Slavin writes:
> I'm trying to write some code which has to be useful under many different 
> circumstances.  Something I want to be able to do is to take an arbitrary 
> SELECT statement and replace the columns which would normally be returned 
> with COUNT(*) to find out how many rows would be returned.  To do this I 
> replace the text between "SELECT" and "FROM".
> 
> I suspect this won't work well with unusual SELECTs which include 
> sub-selects.  That's okay.
> 
> I'm trying to get my head around whether JOINs could be a problem.
> 
> Also, does anyone know whether some combination of NULs might make COUNT(*) 
> give the wrong result ?
> 
> And anything else relevant anyone wants to mention is fine too.
> 
> 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] Porting a simple logon script to SQLite3 from MySQL

[David Garfield]

C Lindgren writes:
>   $sql=$db->exec("INSERT INTO users(ID,username,password)
>   VALUES 
> ('0','".$username."','".$password."')");

In MYSQL, null and 0 are magic for a INTEGER PRIMARY KEY
AUTO_INCREMENT column.

In SQLite, only null is magic for a INTEGER PRIMARY KEY column.

Use NULL instead of '0' in both platforms.

--David Garfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query Problems - Keyword? Encoding?

[David Garfield]

If the value in key is a blob, then like matches it and = does not.

Because like has to do a string conversion on key, it also doesn't use
the index.

Try:

SELECT * FROM recordings WHERE "key" =
cast('4df0247ce1a97685a782d2cb051b48ed952e666c' as blob);

Or try inserting the key as text in the first place.

Or use a binary blob instead of a hex dump of it, and use (note the
"x"):

SELECT * FROM recordings WHERE "key" =
x'4df0247ce1a97685a782d2cb051b48ed952e666c';

--David Garfield

Eric Anderson writes:
> The below statement returns records:
> 
> SELECT * FROM recordings WHERE "key" LIKE
> '4df0247ce1a97685a782d2cb051b48ed952e666c';
> 
> But this one does not:
> 
> SELECT * FROM recordings WHERE "key" =
> '4df0247ce1a97685a782d2cb051b48ed952e666c';
> 
> The only difference is that = and LIKE have been swapped.
> 
> I realize "key" is a keyword but it is quoted properly from what I can
> tell. I'm on sqlite version 3.7.4 (standard one with Mint 11 Linux).
> Any ideas? The only thing I can think of is some sort of encoding
> issue that the LIKE operator is getting around somehow. Or perhaps the
> fact that it is a keyword?
> 
> Any ideas on how to debug?
> 
> -- 
> http://saveyourcall.com - Easily record phone calls from any phone
> http://pixelwareinc.com - Atlanta-based web development and design
> ___
> 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] feed "endless" data into sqlite, thru a shell script

[David Garfield]

Patrick Proniewski writes:
> On 27 sept. 2011, at 20:14, David Garfield wrote:
> 
> > Any entry in a pipe could be buffering.  In a quick test here, awk is
> > buffering.  To find the buffering, try using the pieces up to a given
> > stage with " | cat " added at the end.  If this buffers, you've found
> > the problem.
> 
> as far as my understanding goes, the simple fact I don't have my last output 
> into a tty is enough to trigger buffering.

Actually, any program that doesn't explicitly block buffering and uses
stdout will get buffering.  Some block buffering.  Some (like probably
iostat) explicitly flush the buffers.  Some don't use stdout.  Others
get buffered.

> >  Unbuffered output is usually slower, so it is normally
> > done only to a terminal.  I think the only easy way to externally
> > disable the buffer is to wrap the program in a pseudo-tty.
> 
> apparently... not so easy by the way :)

Well, I think there are three choices for arbitrary programs:
1) Wrap it in a pseudo-tty.  I think I've seen a program to do this,
   but I don't remember where/what.
2) Override isatty() through an LD_PRELOAD.
3) Change the source, either to the program or to libc.

> > Alternatively, look for an option that lets you explicitly unbuffer.
> > (for instance, in perl, do: $| = 1; )
> 
> nothing in awk, but I could try sed instead (-l  Make output line 
> buffered)
> 
> regards,
> patpro

--David Garfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

[David Garfield]

Any entry in a pipe could be buffering.  In a quick test here, awk is
buffering.  To find the buffering, try using the pieces up to a given
stage with " | cat " added at the end.  If this buffers, you've found
the problem.  Unbuffered output is usually slower, so it is normally
done only to a terminal.  I think the only easy way to externally
disable the buffer is to wrap the program in a pseudo-tty.
Alternatively, look for an option that lets you explicitly unbuffer.
(for instance, in perl, do: $| = 1; )

--David Garfield

Patrick Proniewski writes:
> On 27 sept. 2011, at 18:31, Roger Andersson wrote:
> 
> > I do not know if tee makes any difference or if it's available on Mac?
> > http://unixhelp.ed.ac.uk/CGI/man-cgi?tee
> 
> tee is available, but no more luck here, as it won't allow to disable the 
> buffer.
> 
> 
> > iostat -d -w 10 disk0 | tee -a logfile
> > and then
> > tail -f logfile | awk '!/[a-zA-Z]/ {print "INSERT INTO io 
> > VALUES(datetime(\"now\",\"localtime\"),"$1","$2","$3");"}' |\
> > sqlite3 iostat.db
> 
> same problem here ;)
> 
> patpro
> ___
> 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] NUL handling bugs (was Re: c-api document suggestion)

[David Garfield]

Yes, but an API does not have to permit memory areas that large, so
can restrict itself to int, if it chooses.  It isn't necessarily a
good choice, but it isn't necessarily wrong either.  Especially if int
is properly defined (which, of course, it isn't in one of your
examples).  A simple example might be something that reads a name from
a user.  If the input is defined to be at most 1000 characters, then a
length that can hold at minimum 32767 is sufficient, and performance
considerations can be taken into account.

And yes, there are some odd needs for size_t at times.  Like that in
the x86 real mode "large" model size_t can be unsigned int or long,
and ptrdiff_t must be long.  [Personally, I've always been
disappointed that the 386 and later 32 bit (and larger) OSes don't
seem to support anything but "tiny" model.]

Jan Hudec writes:
> On Mon, Sep 26, 2011 at 12:44:08 -0400, David Garfield wrote:
> > Using int instead of size_t A) unnaturally limits data length,
> > possibly to 64K and B) doesn't well describe the data.  Beyond that,
> > it isn't necessarily a bug.  The language needed size_t to provide a
> > type guaranteed to be sufficient to hold any sizeof() result, but no
> > API need use it.
> 
> Quite the oposite. size_t can, by definition, be used to represent size of
> any object that may ever exist in memory on given platform. For static

Isn't that what I said?

> arrays, the sizeof returns their total size in bytes, so size_t (being the
> return type of sizeof) has to be large enough to hold that value. For dynamic
> arrays malloc takes it's argument as size_t, so obviously size of block it
> returns is representable by size_t.
> 
> Now I don't know any platform where size_t is smaller than int, but I know at
> least two cases where size_t is larger than int:
>  - All 64-bit platforms have 32-bit int, but 64-bit size_t.
>  - x86 in 16-bit real mode had 16-bit int, but the "huge" memory model
>allowed allocating blocks larger than 64K and thus had to have 32-bit
>size_t.
> 
> -- 
>Jan 'Bulb' Hudec <b...@ucw.cz>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

--David Garfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] NUL handling bugs (was Re: c-api document suggestion)

[David Garfield]

Yes, I am referring to functions in the SQL, not functions in the
API.  And I suppose the alternative to fixing them in SQLite is to
document them.

I suppose if it were to be fixed, it could break people depending on
the bad behavior.  On the other hand, the existing behavior means that
some unexpected things are broken.  For instance:

create table t(a unique);
insert into t values ('one');
insert into t values ('two');
insert into t values ('two' || x'00');
.dump

Yields:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t(a unique);
INSERT INTO "t" VALUES('one');
INSERT INTO "t" VALUES('two');
INSERT INTO "t" VALUES('two');
COMMIT;

Which will not even load back in.


Having said that, yes, it could break existing usage, so any fix
should probably include a compatibility mode.  


An alternative variant might be to enforce that NUL cannot occur in
text (i.e. makes it error out).  This should be orthogonal to making
NULs well-behaved in SQL functions.  I wouldn't recommend it, but it
might be a useful transition tool, or a useful third variant.


One interesting thought:  It might actually make the functions faster
to not have to count the length of strings.


--David


Roger Binns writes:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> On 09/26/2011 01:13 PM, David Garfield wrote:
> > In documented one case
> > (prepare), NULs are string terminators even with the count, and there
> > is an extra optimization you may apply.  All this works (I think).
> 
> Agreed, ugly but documented.  BTW in my wrapper the code leading up to a
> call to prepare was a bottleneck that I spent a lot of time profiling
> and optimising since it involved cache lookups, UTF8 conversion,
> multiple statements in one string, memory copies etc.
> 
> > SQLite has a number of string manipulation functions.
> 
> To be clear all the instances you are talking about are not the C API
> (which kept being mentioned) but rather user defined functions (in the
> sense of a SQL syntax entity).
> 
> Some of the functions work just fine.
> 
> Others have issues including stopping at the first NUL instead of the
> string length, working on blobs which makes no sense for string
> operations etc.
> 
> They have been "broken" for many years and this breakage has been
> mentioned here over the years, but rarely. (ie doesn't affect many people).
> 
> The workaround is to register your own functions of the same name with
> your own desired behaviour.
> 
> It is conceivable that fixing the functions could break some existing
> code somewhere where someone has abused embedded NUL support (eg storing
> a list of NUL separated strings where only the first entry matters for
> user defined functions).
> 
> Generally the team are very concerned about breaking even one deployment
> which is why this kind of thing tends not to get changed.  They'll have
> to chime in this time, but I wouldn't get my hopes up.
> 
> I'll create an entry in the bug tracker when I get home.
> 
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
> 
> iEYEARECAAYFAk6A9pgACgkQmOOfHg372QQADQCferKj6ud1csg9aSjzVetXs5mo
> imsAnjWDgwD863PxAXJVbDdYh/qzGY7/
> =fJnR
> -END PGP SIGNATURE-
> ___
> 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] NUL handling bugs (was Re: c-api document suggestion)

[David Garfield]

OK Roger, lets start over.

The API has functions that deal with strings.  They take either a NUL
terminated buffer with a length specified as negative, or a buffer and
length in which NULs are not special.  In documented one case
(prepare), NULs are string terminators even with the count, and there
is an extra optimization you may apply.  All this works (I think).

SQLite has a number of string manipulation functions.  Many of these,
despite being in an environment where the strings are all counted
strings, terminate processing at the first NUL.  This is the bug.
They should not treat NUL as special.  Some functions are fine (hex(),
relational operators, group by, ...), some are broken (quote(),
upper(), lower(), length(), ...), and some are half-broken (replace(),
trim(), rtrim(), ...).  When I say broken, I mean they stop at the NUL
when they should not.

Yes, I know you could replace the built-in functions.  You should not
have to.

--David Garfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] NUL handling bugs (was Re: c-api document suggestion)

[David Garfield]

Roger Binns writes:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> On 09/23/2011 05:51 PM, David Garfield wrote:
> >> SQLite's API supports both (mostly).  Internally, you must use one or
> >> the other (or hideously duplicate code),
> 
> Not really.  If your own code only uses NUL termination then use that form
> of APIs.  If you use counted strings then use that form.  As a developer
> using SQLite you do not have to use both.  And you can mix and match with
> what is most convenient at each call, although supporting embedded NUL
> requires the counted form for obvious reasons.

As the implementer of an API, you must use one form or the other, or
hideously duplicate code.  It is TRIVIAL to convert from the
NUL-terminated form to the counted form, so that is what is usually
done.  (I will admit to being surprised that SQLite goes into two
levels of internal function calls before it converts, especially since
by that time it has put the text and text16 forms together.)

> >> and SQLite uses the second --
> >> except for some functions (which use the hybrid model).  That
> >> exception is the bug.
> 
> The "bug" is that a performance optimisation is mentioned in the doc.  The
> internal SQL parsing code always stops at a NUL, and requires that a string
> be NUL terminated.  If you do not explicitly provide one then it will copy
> the string in order to NUL terminate it.  Sure this is a little messy and
> could be explained a little better but it isn't a bug.  The internal code
> could also change in the future to avoid the NUL requirement but I'd expect
> that to be *really* low in the list of priorities.

I went and looked for that performance optimization, and that
documentation.  It applies only to sqlite3_prepare*().  The various
data methods do not have the same clause.  And the data methods are
the only ones I would really expect NUL handling to be right.

> >> Correction: with the exception of a number of BUILT IN functions.
> 
> I meant user defined functions in the sense of components of a SQL statement
> (like verbs, operators and collations are components).  Yet another pesky
> ambiguation introduced by the user word!
> 
> Note that you can override all built in user defined functions - just
> register one with the same name.  You do however have to ensure that you
> register variants for the different Unicode encodings.
> 
> ie you can make your installation of SQLite behave exactly how you want.
> Should the built in implementations be fixed?  IMHO yes, but it isn't a
> priority. In the 6 years since SQLite 3 has been available you are only the
> second person to complain.  (I was the first :-)

So is it in the bug system?  Or is it being dismissed or ignored?  I
would have been willing to accept that this was a known bug and
documented bug, but that isn't what I'm hearing here.  Indeed, Richard
claimed that this was a "feature", and likened it to ignoring UTF-8
encoding errors.

And I am not the second.  I started this subject as a follow-up to a
message from "Mira Suk" <mira@centrum.cz> complaining about the
same thing, so I am at least the third.  There are probably more.

> >> sqlite3_value_*() and sqlite3_result_*() are fully capable of using
> >> the counted model,
> 
> Indeed.  It is how I ensure my code is NUL safe/correct.  A far bigger bug
> for those functions is that they use int for the size of data rather than
> size_t.  I did a survey a few years back using google code search and every
> instance I could find where -1 was not passed in as the length treated them
> as though they used size_t and would result in (silent) truncation on 64 bit
> machines.  My own code explicitly makes sure the values about to be passed
> in are less than 2GB.

Using int instead of size_t A) unnaturally limits data length,
possibly to 64K and B) doesn't well describe the data.  Beyond that,
it isn't necessarily a bug.  The language needed size_t to provide a
type guaranteed to be sufficient to hold any sizeof() result, but no
API need use it.

> >> Of course, the SQLite shell does it anyway.  So "cannot" is not really
> >> correct.
> 
> Well you can always spew arbitrary bytes to stdout which generally works for
> people who only ever use ASCII.  But the rule really is that bytes cannot be
> converted to characters without knowing the encoding.

Right.  The SQLite shell just assumes you are running in a uniform
character encoding environment (usually safe), and that it is UTF-8
(safe for many users). 

> > The SQLite shell isn't particular well structured for easy developer
> > extension.
> >> I've seen that...  ouch.
> 
> It is best to think of the shell as a convenience tool for the SQLite
> devel

Re: [sqlite] NUL handling bugs (was Re: c-api document suggestion)

[David Garfield]

Roger Binns writes:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> On 09/23/2011 08:47 AM, David Garfield wrote:
> > But that is the point.  Strings are generally defined in two ways.
> > Either:
> > 
> > 1) a pointer, and count every byte up to but not including a NUL.
> > 
> > 2) a pointer and a length, and count every byte in the specified length.
> 
> You insist on a dichotomy but SQLite supports both conventions
> simultaneously.  You'll only have some difficulty if you use the second
> convention as some builtin functions operate on the first convention, but
> your own user defined functions/collations etc can do the right thing.

I insist on the dichotomy because the dichotomy is real.  Think about
the semantics of honoring NUL as end of string in the data stored by
sqlite in the file.  And there is a third hybrid model (NUL or count)
that is so little used that most people don't recognize it when it is
placed in front of them.

SQLite's API supports both (mostly).  Internally, you must use one or
the other (or hideously duplicate code), and SQLite uses the second --
except for some functions (which use the hybrid model).  That
exception is the bug.

> > Internally, SQLite uses counted strings.  It should treat NUL as just
> > one more character, since it is just one more character.
> 
> It does with the exception of some user defined functions.

Correction: with the exception of a number of BUILT IN functions.

sqlite3_value_*() and sqlite3_result_*() are fully capable of using
the counted model, and have the same shortcuts for the NUL-terminated
model.

> > ... user's database ...
> 
> The same database can be populated by many different programs, and you
> should really distinguish between users and developers - sometimes they are
> the same person and sometimes not.  Eg whose database is the one used by
> Chrome and Firefox?

In this case, by "user" I meant the user of SQLite API, which is in
fact a developer.

> > Actually, I guess I am more concerned with the "shell".
> 
> The shell is not a formal part of SQLite.  It doesn't have the same level of
> testing or backwards/forwards compatibility requirements.  It is still
> completely open code with no restrictions on how it operates so you can
> modify the code to do whatever you want.
> 
> > I use blob
> > columns that are partly text and would like to have the text portions
> > at least be visible.
> 
> You cannot display bytes as text unless you know the encoding.  There is
> nothing stopping you from altering the shell to do this, adding a hexdump
> output mode, adding a user defined function etc.

Of course, the SQLite shell does it anyway.  So "cannot" is not really
correct.

> The SQLite shell isn't particular well structured for easy developer
> extension.
I've seen that...  ouch.

> My Python SQLite wrapper includes a compatible shell that is
> easy to augment and extend:

And your python wrapper is probably implemented using the counted
string form exclusively.  :-)

>   http://apidoc.apsw.googlecode.com/hg/shell.html
> 
> Roger
> 
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
> 
> iEYEARECAAYFAk59DPMACgkQmOOfHg372QRh4gCdE+pNpGL7GG3FD9YVA161JdZj
> gYYAn2lrpRpKsNerKWOv6o47EH0AL/Jf
> =BPPC
> -END PGP SIGNATURE-
> ___
> 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] NUL handling bugs (was Re: c-api document suggestion)

[David Garfield]

But that is the point.  Strings are generally defined in two ways.
Either:

1) a pointer, and count every byte up to but not including a NUL.

2) a pointer and a length, and count every byte in the specified length.

If you have a specified length, the length matters, and NULs do not.
NUL is a perfectly valid character in such a string.

Internally, SQLite uses counted strings.  It should treat NUL as just
one more character, since it is just one more character.

If a USER decides to use a negative length in sqlite3_bind_text() and
to use sqlite3_column_text() without using sqlite3_column_bytes(),
then that user's database doesn't have NULs in the strings, and
everything just works for him.  For the database as a whole to ASSUME
this breaks SQLite for users who do want NULs in their text data.

Actually, I guess I am more concerned with the "shell".  I use blob
columns that are partly text and would like to have the text portions
at least be visible.

I suspect that at least part of this problem came from SQLite's
history as a TCL add-on.  I suspect TCL, at least in it's early days,
didn't allow NUL in a string.

--David Garfield



Richard Hipp writes:
> On Thu, Sep 22, 2011 at 7:53 PM, David Garfield <
> garfi...@irving.iisd.sra.com> wrote:
> 
> > As far as I am concerned, this is a SERIOUS bug in sqlite.
> 
> 
> SQLite does whatever you ask it to do.  It makes no attempt to enforce good
> string hygiene.  If you hand it well-formed strings, it gives the expected
> results and always returning well-formed strings.  If, on the other hand,
> you give it malformed strings with embedded NULs and/or illegal UTF
> characters, SQLite will muddle through as best it can using whatever you
> gave it, without complaining, and without overflowing buffers or segfaulting
> or leaking memory or otherwise failing.  This is an intentional feature, not
> a bug.
> 
> 
> 
> 
> >  When you
> > have counted strings, which is all sqlite has (except for some API
> > functions), NULs have no special meaning.  This is fairly easily seen
> > in the command line interface, where the code ignores the length and
> > uses strlen forms.
> >
> > Testing various functions:
> >
> > hex(), relational operators, and group by get it right.  max() and
> > min() seem to get it right (but I may not have tested enough).
> >
> > length(), quote(), upper(), lower(), like(), and glob() get it wrong.
> > group_concat() gets it wrong returning the result, but looks like it
> > may have built the right thing internally.
> >
> > replace(), trim(), ltrim(), rtrim() get it right in the first
> > parameter, but not in the second parameter.  replace() gets it right
> > in the third parameter.
> >
> > I'm not sure what else would be expected to allow NULs, so I didn't
> > try numeric and date/time functions.
> >
> > Testing can be a bit of a pain.  Things like:
> >
> > BEGIN TRANSACTION;
> > CREATE TABLE t(a);
> > insert into t values (cast(x'6F6E65202020' as text));
> > insert into t values (cast(x'6F6E6520202030' as text));
> > insert into t values (cast(x'6F6E6520202000' as text));
> > insert into t values (cast(x'6F6E6520202078' as text));
> > insert into t values (cast(x'6F6E650074776F20' as text));
> > insert into t values (cast(x'20006F6E6520' as text));
> > insert into t values (cast(x'00206F6E6520' as text));
> > COMMIT;
> > .mode column
> > .width 10 20 20 20
> > .header on
> > select a, hex(a), hex(trim(a)), hex(trim(a,'one'||x'2000')) from t;
> >
> > Yields:
> >
> > a   hex(a)hex(trim(a))
> >  hex(trim(a,'one'||x'
> > --    
> >  
> > one 6F6E65202020  6F6E65
> > one   0 6F6E65202020306F6E652020203030
> > one 6F6E65202020006F6E652020200000
> > one   x 6F6E65202020786F6E652020207878
> > one 6F6E650074776F20  6F6E650074776F007477
> >20006F6E6520  006F6E65  00
> >00206F6E6520  00206F6E6500
> >
> > What I expect (including column screw-ups because NUL is zero width in
> > xterm) is:
> >
> > a   hex(a)hex(trim(a))
> >  hex(trim(a,'one'||x'
> > --    
> >  
> > one 6F6E65202020  6F6E65
> > one   0 6F6E65202020306F6E652020203030
> > one6F6E65202020006F6E6520202000
> > one   x 6F6E6520202078   

[sqlite] NUL handling bugs (was Re: c-api document suggestion)

[David Garfield]

As far as I am concerned, this is a SERIOUS bug in sqlite.  When you
have counted strings, which is all sqlite has (except for some API
functions), NULs have no special meaning.  This is fairly easily seen
in the command line interface, where the code ignores the length and
uses strlen forms.

Testing various functions:

hex(), relational operators, and group by get it right.  max() and
min() seem to get it right (but I may not have tested enough).

length(), quote(), upper(), lower(), like(), and glob() get it wrong.
group_concat() gets it wrong returning the result, but looks like it
may have built the right thing internally.

replace(), trim(), ltrim(), rtrim() get it right in the first
parameter, but not in the second parameter.  replace() gets it right
in the third parameter.

I'm not sure what else would be expected to allow NULs, so I didn't
try numeric and date/time functions.

Testing can be a bit of a pain.  Things like:

BEGIN TRANSACTION;
CREATE TABLE t(a);
insert into t values (cast(x'6F6E65202020' as text));   
insert into t values (cast(x'6F6E6520202030' as text)); 
insert into t values (cast(x'6F6E6520202000' as text)); 
insert into t values (cast(x'6F6E6520202078' as text)); 
insert into t values (cast(x'6F6E650074776F20' as text));   
insert into t values (cast(x'20006F6E6520' as text));   
insert into t values (cast(x'00206F6E6520' as text));   
COMMIT;
.mode column
.width 10 20 20 20
.header on
select a, hex(a), hex(trim(a)), hex(trim(a,'one'||x'2000')) from t;

Yields:

a   hex(a)hex(trim(a))  hex(trim(a,'one'||x'
--      
one 6F6E65202020  6F6E65
one   0 6F6E65202020306F6E652020203030  
one 6F6E65202020006F6E652020200000  
one   x 6F6E65202020786F6E652020207878  
one 6F6E650074776F20  6F6E650074776F007477  
20006F6E6520  006F6E65  00  
00206F6E6520  00206F6E6500  

What I expect (including column screw-ups because NUL is zero width in
xterm) is:

a   hex(a)hex(trim(a))  hex(trim(a,'one'||x'
--      
one 6F6E65202020  6F6E65
one   0 6F6E65202020306F6E652020203030  
one6F6E65202020006F6E6520202000  
one   x 6F6E65202020786F6E652020207878  
onetwo 6F6E650074776F20  6F6E650074776F7477  
 two   20006F6E6520  006F6E65
 two   00206F6E6520  00206F6E65  


Without the hex() calls, you can't even tell what worked and what didn't.

--David Garfield

Mira Suk writes:
> On 9/21/2011 21:22 Igor Tandetnik wrote:
> 
> > You can include the NUL terminator, if you want it to actually be stored 
> > in the database.
> 
> > Igor Tandetnik
> 
> Actually you can't - if you do all SQL string functions will not work.
> to be clear -
> SELECT TRIM(what ever text column you stored with including null on end of 
> string)
> will not trim that string.
>  
> found out the hard way.
> 
> ___
> 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] Bug in GCC - suggestions sought for a backup tool chain

[David Garfield]

Well, I just looked in the 3.7.2 amalgamation version I use.

1) sqlite3SrcListShiftJoinType was declared with SQLITE_PRIVATE.  Even
   if the static isn't here, if it is in the prototype, it can apply.
   Since this is an amalgamation-only feature, this might not be the
   issue.  (3.7.7.1 src did not have SQLITE_PRIVATE, but 3.7.7.1
   amalgamation did.)

2) Member "a" was an array, not a pointer.  Arrays cannot be null.
   This was the same in 3.7.7.1.  Array members can only be null if:

   A) they are the first member of the structure and the pointer to
  the structure is null.  In this case, it is not the first
  member, and you tested the pointer to the structure in any case.

   B) You wrap-around memory, which is undefined behavior.

In short, GCC 4.1.0 got it RIGHT.  GCC 4.5.2 optimized less.  I
suspect that either the default optimization has changed or the level
for this optimization has changed.

Having said this, it may well be reasonable to leave the test in place
in case you someday choose to stop using the struct hack (which is
undefined behavior as well), knowing that the compiler will optimize
out the dead test.

--David Garfield

Richard Hipp writes:
> Consider this line of code in the "build.c" source file of SQLite:
> 
>   http://www.sqlite.org/src/artifact/77be7c217430?ln=3372
> 
> It appears that GCC 4.1.0 is not generating any code for the second test in
> the conditional.  In other words, GCC 4.1.0 is compiling that statement as
> if it omitted the "&& p->a" term and looked like this:
> 
>   if( p ){ 
> 
> You can see this for yourself by downloading the file above and then
> running:
> 
>   gcc -g -S build.c
> 
> And then looking at the build.s output file.  With GCC 4.1.0, I get this:
> 
> .loc 1 3372 0
> cmpl$0, 8(%ebp)
> je.L920
> 
> Looks like only one test to me.  But with GCC 4.5.2 I get this:
> 
> .loc 1 3372 0
> cmpq$0, -24(%rbp)
> je.L611
> .loc 1 3372 0 is_stmt 0 discriminator 1
> movq-24(%rbp), %rax
> addq$8, %rax
> testq%rax, %rax
> je.L611
> 
> Both tests appear to be coded this time.
> 
> As it happens, the GCC bug is harmless in this case.  SQLite never invokes
> the sqlite3SrcListShiftJoinType() function with a non-NULL SrcList pointer
> that has a NULL p->a value.  So the p->a!=NULL test really is always true.
> (Note that the GCC optimizer has no way of knowing that because the function
> has external linkage.)  And so it didn't matter that the test was omitted.
> I didn't notice the problem until this morning, when I upgraded my desktop
> to the latest Ubuntu containing GCC 4.5.2, and reran the full branch
> coverage tests.  GCC 4.5.2 was showing that the p->a!=NULL branch was always
> true.  Further investigation shows that it has always been always true but
> that the GCC 4.1.0 bug simply masked the error up until now.
> 
> I see two take-aways from this episode:
> 
> (1) Compilers sometimes make mistakes.  So it is important that you test
> your object code - not just your source code.  That means running your test
> cases using exactly the same *.o files that you use for delivery.  "Fly what
> you test and test what you fly."
> 
> (2) I need to come up with a second, independent method of verifying branch
> test coverage in SQLite.  I have been using GCC+GCOV and it does a great job
> and I fully intend to continue using it as the primary tool chain for
> development and testing.  But in this case, because GCC was omitting a test,
> it missed the fact that there was no test coverage for the omitted test.  So
> it would be nice to have an independently developed tool chain that can be
> used to confirm the results we get from GCOV.  Anybody have any suggestions?
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] EINTR and write() in os_unix.c

[David Garfield]

I think you are right, that it is too easy, at least on its own.  You
also should account for partial writes.

I think the general rule is: if it wrote anything, it tells you how
much it wrote, which can be everything you asked it to write, or less.
If it wrote nothing, it usually returns -1 and sets errno based on why
it failed.  You then can keep retrying if the error is EINTR or
EAGAIN, or maybe some others.

I HOPE that an interrupt in a large interrupted write will NOT return
EINTR, because if it does, the partial write is screwed up.


Stephan Beal writes:
> On Thu, Aug 18, 2011 at 4:39 PM, Pavel Ivanov  wrote:
> 
> > If you don't want your library behavior to be changed when process
> > receives some signals then you have to use such or some similar
> > approach.
> >
> 
> i understand the reason for it, but before i go adding this to my storage
> API i just wanted to ask for some opinions as to whether this approach is
> generically both technically and philosophically sound, or whether the fact
> that it works at all relies on other voodoo deep within os_unix.
> 
> The main thing that troubles me is that this approach just seems "too easy"
> given this particular problem, and i'm suspicious because of that. It
> _seems_ to be exactly what i'm looking for, but i also know that my
> knowledge for the effect of EINTR on system calls is too constrained for me
> to have my own educated, confident opinion on it.
> 
> -- 
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> ___
> 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] Maximum number of tables in a data file

[David Garfield]

Richard Hipp writes:
> This is a locality of reference problem.  The caching mechanisms (both in
> SQLite and in the filesystem of your computer) begins to break down when the
> size of the database exceeds available RAM.  And when the cache stops
> working well, you have to wait on physical I/O which is very slow.

And, of course, this "locality of reference problem" is going to exist
no matter what database you use.  One might reduce it a bit (even
orders of magnitude) using specialized indexes, but it is not
something that can be eliminated.  So all the references to other
databases are just ways to waste ones time.

Having said that, let me present a database for consideration: Any
filesystem.  Split the hex of the MD5 into directory levels and make
what you need.  Might be slower, particularly with some OSes, but the
tools are easy.

--David Garfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] group_concat and empty strings

[David Garfield]

sqlite> .null NULL
sqlite> select group_concat('');
NULL
sqlite> select group_concat(x'');
NULL
sqlite> 

I would think it is a bug.  At the very least, it is not consistent
with the description of the function.  It seams to happen only when
the only thing being concatenated is a zero length string or blob.

--David Garfield

Jan writes:
> On http://www.sqlite.org/cvstrac/tktview?tn=3806
> this bug is fixed.
> 
> But I still get a NULL with group_concat(distinct x) with the latest 
> windows sqlite shell:
> 
> SQLite version 3.7.7.1 2011-06-28 17:39:05
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> .null NULL
> sqlite> create table tbl (a int, b text);
> sqlite> insert into tbl values(1, '');
> sqlite> insert into tbl values(1, '');
> sqlite> select a, group_concat(b) from tbl group by a;
> 1|,
> sqlite> select a, group_concat(distinct b) from tbl group by a;
> 1|NULL
> 
> Is this behaviour correct?
> 
> Thank you,
> Jan
> ___
> 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] LevelDB benchmark

[David Garfield]

They used REPLACE.  See
http://code.google.com/p/leveldb/source/browse/trunk/doc/bench/db_bench_sqlite3.cc#492

They used explicit transactions, and tested with both single REPLACE
transactions and 1000 REPLACE transactions.  Section 1A would be the
single REPLACE transactions, while 2B is the batches.

--David Garfield

Simon Slavin writes:
> 
> On 28 Jul 2011, at 2:22am, Stephan Wehner wrote:
> 
> > There are some benchmark's at 
> > http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html
> > 
> > I don't have anything to point to, but I thought sqlite3 does better 
> > than stated there.
> 
> i looked through their source code, trying to see if they defined 
> transactions.  But I couldn't even find an INSERT command.
> 
> 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] Anyway to order query results by "in" list?

[David Garfield]

Two implementations:

CREATE TABLE t (a int, b int);
INSERT INTO t VALUES(1,1);
INSERT INTO t VALUES(2,4);
INSERT INTO t VALUES(3,9);
INSERT INTO t VALUES(4,16);
INSERT INTO t VALUES(5,25);
INSERT INTO t VALUES(6,36);
INSERT INTO t VALUES(7,49);
INSERT INTO t VALUES(8,64);
INSERT INTO t VALUES(9,81);
INSERT INTO t VALUES(10,100);
INSERT INTO t VALUES(11,121);
INSERT INTO t VALUES(12,144);
INSERT INTO t VALUES(13,169);
INSERT INTO t VALUES(14,196);
INSERT INTO t VALUES(15,225);
INSERT INTO t VALUES(16,256);
INSERT INTO t VALUES(17,289);
INSERT INTO t VALUES(18,324);
INSERT INTO t VALUES(19,361);
INSERT INTO t VALUES(20,400);
INSERT INTO t VALUES(21,441);

SELECT * FROM t WHERE a IN (5, 3, 10) 
ORDER BY CASE a WHEN 5 THEN 1 WHEN 3 THEN 2 WHEN 10 THEN 3 ELSE -1 END;

SELECT a, b FROM (
SELECT (CASE a WHEN 5 THEN 1 WHEN 3 THEN 2 WHEN 10 THEN 3 ELSE -1 END) o,
a, b FROM t
) t WHERE o>= 0 ORDER BY o;


Both yield:
a   b 
--  --
5   25
3   9 
10  100   


The first is simpler, but requires you to list your items twice.
The second fails to use an index, but only names the items once.

Try the first unless you know a full table scan is acceptable.

--David Garfield

Michael Stephenson writes:
> Wondering if anyone has a way to execute a query that selects rows based on
> a list of rowids and returns the results in the order of the rowids passed
> in.
> 
> Thanks,
> 
> ~Mike
> 
> ___
> 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] Dynamic SQL for SQLite?

[David Garfield]

Wonderful.

The other answer is that one probably should not have a table with
seven columns and one row when one could have a table with two columns
(day of week and value) and seven rows.  Like the view you are
suggesting.

--David

Nico Williams writes:
> On May 11, 2011 7:14 PM, "John"  wrote:
> > let's say I have a table with columns for each day of the week
> >
> > create table seven_days
> > (monday_value integer,
> >  tueday_value integer,
> >  wednesday_value integer,
> >  ...   );
> >
> > I want to select value from whatever day it is today. So if today is
> > Tuesday, select from tuesday_value.
> >
> > Can I do it with pure SQlite?
> 
> Sure.  First setup a view that consists of a union of queries one for each
> day, with a column whose value is the day of the week.  Then query that
> view.
> 
> Nico
> --
> ___
> 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] Advice to choose an index for quad tree?

[David Garfield]

Actually, for what he wants, you don't need anything fancy.  A simple
multi-column index is enough.

The R-Tree is to allow queries of a sparse dataset, that might also
have overlaps.

So: A simple index for your background imagery.  An R-Tree index for
the features added on top of your background imagery.

--David Garfield

Enrico Thierbach writes:
> Hi,
> 
> I think an R Tree is what you are after.
> 
> http://www.sqlite.org/rtree.html
> 
> /eno
> 
> ___
> 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] Documentation error was Re: date field with default current date

[David Garfield]

http://www.sqlite.org/lang_datefunc.html

This page has an error in documenting the range of values when using
modifier 'unixepoch'.  It says the limit is 10675199167.  There should
be one more digit in that to get the documented year 5352 result.

--David Garfield

Mihai Militaru writes:
> On Thu, 21 Apr 2011 15:17:00 +0200
> Fabio Spadaro <fabiolinos...@gmail.com> wrote:
> 
> > Does not work on python with sqlite3 module
> 
> Try using the date and time functions, 'date' or 'datetime' in your case:
> INSERT INTO table(..., date) VALUES(..., datetime('now'));
> 
> http://www.sqlite.org/lang_datefunc.html
> 
> -- 
> Mihai Militaru <mihai.milit...@xmpp.ro>
> ___
> 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] Request for help with SQLite Query to return missing Date/Time Ranges

[David Garfield]

Actually, with this criteria, you can -- in a roundabout way.

select the timestamps by a large period, perhaps daily or monthly.
aggregate on that rough timestamp, and also select the count.
Anywhere the count is lower than expected, you are missing data.

You can then repeat over relevant ranges with finer aggregation to
identify exactly what is missing.

A completely missing block might not be noticed, but unless the stop
and start exactly matched the missing data, you would see the border
anomalies.

A having clause would be useful for selecting just the anomalous
aggregations (provided they are uniform).

--David Garfield


Simon Slavin writes:
> 
> On 21 Apr 2011, at 12:34am, Andrew Lindsay wrote:
> 
> > I am trying to search an SQL database that is meant to have entries logged
> > every minute for a period of approximately 15 months.
> > 
> > 
> > 
> > I want to create a query that will search through the database and tell me
> > for which periods I do not have any entries.
> 
> You can't find missing data.  Your fastest way to do it would just be to 
> write code retrieve all the datastamps, turn them into period numbers, then 
> look down the list for missing ones.  A slower way would be to use a SELECT 
> which retrieved all entries, with a sub-SELECT which looked for all entries 
> in the following period.
> 
> 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] updating records in table A from joined records in table B

[David Garfield]

A bit redundant, but how about:

CREATE TABLE table_a (akey integer, avalue float);
CREATE TABLE table_b (bkey integer, bvalue float);
INSERT INTO table_a (akey, avalue) VALUES (1,   1.0);
INSERT INTO table_a (akey, avalue) VALUES (2,   2.0);
INSERT INTO table_a (akey, avalue) VALUES (3,   3.0);
INSERT INTO table_b (bkey, bvalue) VALUES (1, 101.0);
INSERT INTO table_b (bkey, bvalue) VALUES (2, 102.0);
INSERT INTO table_b (bkey, bvalue) VALUES (4, 104.0);

UPDATE table_a SET avalue=(SELECT bvalue FROM table_b WHERE akey=bkey)
WHERE EXISTS(SELECT 1 FROM table_b WHERE akey=bkey);

I think there is no multi-table update idiom in SQLite because there
isn't one in SQL.

--David 

Robert Poor writes:
> I'd like to be able to update specific records in table A from joined
> records in table B. So for example:
> 
> CREATE TABLE "table_a" ("key" integer, "value" float);
> CREATE TABLE "table_b" ("key" integer, "value" float);
> INSERT INTO "table_a" ("key", "value") VALUES (1,   1.0), (2,   2.0),(3,   
> 3.0);
> INSERT INTO "table_b" ("key", "value") VALUES (1, 101.0), (2, 102.0),(4, 
> 104.0);
> 
> In an imaginary version of SQLite ;) this might be written as:
> 
> # UPDATE table_a
> #   JOIN table_b
> #SET table_a.value = table_b.value
> #  WHERE table_a.key1 = table_b.key
> 
> resulting in table_a:
> 
> key | value
> 1   | 101
> 2   | 102
> 3   |   3
> 
> ... that is to say, update table_a.value from table_b.value, but only
> on rows where table_a.key = table_b.key
> 
> I've pored over the UPDATE syntax, but I don't see a way to do this.
> What's the idiom in SQLite?
> ___
> 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] Insert BLOB data from the command line

[David Garfield]

Nice.

For general use, you may also need to include duplicates in "od".  For
the GNU coreutils and Solaris 10 implementations, this would be the -v
or --output-duplicates option.

--David Garfield

Kees Nuyt writes:
> On Wed, 30 Mar 2011 12:51:11 -0400, "Santin, Gloria"
> <sant...@westinghouse.com> wrote:
> 
> >Sorry, my question was not clear enough...
> >I need to open a file and store the contents into the BLOB field.  Some of 
> >the files are images and some are document templates.
> >Can I do that using just SQL commands from a command line?
> >
> >Thanks,
> >Gloria
> 
> Yes, here's a oneliner (tested on Solaris):
> 
> d=test.db3 ; echo "CREATE TABLE Files (name TEXT PRIMARY
> KEY,contents BLOB);" | sqlite3 $d ; for f in yourfiles* ; do echo
> "INSERT INTO Files (name,contents) VALUES ('$f',X'$(od -A n -t x1
> $f|tr -d '\r\n\t ')');" | sqlite3 $d ; done 
> 
> HTH
> -- 
>   (  Kees Nuyt
>   )
> c[_]
> ___
> 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