[sqlite] DbFunctions.TruncateTime

[Steffen Mangold]

Hi Ryan,

I get your point. :) 
It seems the I was misunderstanding this help mailing list. I thought it's also 
support for 'System.Data.SQLite'.

In the way 'System.Data.SQLite' is an ADO.NET provider for SQLite and also give 
support for entity framework.
that because I was asking if it support 'DbFunctions' like 'TruncateTime'.
I'm asking because I want to know if I simpley miss the a way or a SQLite 
ADO.Net class to do this.

I hope I make it a little more clear why I'm asking in this mailing list.

Regards Steffen

[sqlite] design problem involving trigger

[Will Parsons]

On Saturday, 22 Aug 2015  1:05 PM -0400, R.Smith wrote:
>
>
> On 2015-08-21 11:23 PM, Will Parsons wrote:
>> On 21 Aug 2015, R.Smith wrote:
>>>
>>> On 2015-08-21 04:47 AM, Will Parsons wrote:
 I'm working on a program that involves a catalogue of books.  Part of
 the database schema looks like this:

 create table Books(id integer primary key,
  title text collate nocase not null,
  author references Authors(id),
  ...
  unique(title, author));

 create table Authors(id integer primary key,
name text unique not null check(name <> ''));

 The idea here is that the title+author of a book must be unique, but a
 book may not necessarily have an author associated with it.  But, the
 schema fragment as I have presented it does not disallow entering the
 same title with a null author multiple times, which is clearly
 undesirable.

 In thinking about how to avoid this, one possibility that has occurred
 to me is to add an entry to the Authors table with id=0, name=NULL, and
 to modify the Books table to be:

 create table Books(id integer primary key,
  title text collate nocase not null,
  author references Authors(id) not null,
  ...
  unique(title, author));

 With this, entries in the Books table that presently have the author
 field set to NUll would instead have author=0.

 What I would like to have is a trigger that when an attempt is made to
 enter a new record into the Books table with a NULL author field, is
 to force the author field to 0 instead.  I can't see how to do this
 with a "before insert" trigger, though.  Perhaps I'm approaching this
 the wrong way; in any case I'd appreciate advice.

>>> Nothing wrong with your approach, simply declare the Author to be NOT
>>> NULL and if it is possible to have non-Authors you could do what you
>>> suggest, add a 0-Author to the parent table, or, you could use a partial
>>> Index (available after SQLite 3.7 only I think) which would allow you to
>>> have NULLS but still maintain a strict relation, something like this:
>>>
>>> create table Books(id integer primary key,
>>>  title text collate nocase not null,
>>>  author int not null references Authors(id),
>>>  ...
>>>  );
>>>
>>> create unique index uBookAuth on Books(title,author) where author is not 
>>> null;
>>>
>>> create table Authors(id integer primary key,
>>>name text unique not null check(name <> ''));
>> I guess I don't understand how having a partial index where author is
>> not null prevents adding two entries with the same title and a null
>> author.  How is this different from what I have now with the
>> "unique(title, author)" specification?
>>
>
> I think I may have misinterpreted slightly...
>
> To clarify: are the book titles unique or are they not?

No - the combination title + author is unique.

> If they are Unique, i.e if no book title can ever appear twice, 
> regardless who authored it, then simply declare a unique index on title.
> If they are NOT Unique, i.e. if a title can appear more than once, then 
> why is it not working for you?
>
> Essentially, if I interpret correctly, you are asking the DB to NOT 
> limit the number of same-titled books, except when you have supplied an 
> author and the same author has already such a titled book, but then when 
> you don't supply an author, it should know to now also limit the copies 
> of  no-author books?
>
> I assumed before that you only added NULL for author if you don't know 
> the author yet (which would make sense and can later be updated) but 
> then you can't force the unique constraint, there may be many books with 
> coinciding titles and not-yet-known authors.

The use of NULL as author is not for a case where the author is
presently unknown, but where there is no meaningful author.

> Reading again, I am now thinking that's not the case, you might add NULL 
> authors to books which simply don't have authors (well, all books have 
> authors, but the author might be unknown and accepted to be of unknown 
> status for time to come), in which case, there might be many same-titled 
> unknown-author books.

It's theoretically possible, but in that case I would be content to
force a difference in the title.  It should be possible to have the
following:

'History of Scotland' | -> 'A. Jones'
'History of Scotland' | -> 'T. Smith'
'Manual of DOS'   | NULL

But, an attempt to insert another record 'Manual of DOS' with a NULL
author should fail.

(In the above, I've used "->" to indicate the author field is actually
a foreign key reference to another table.)

It *is* possible to have the same book in 

[sqlite] DbFunctions.TruncateTime

[Steffen Mangold]

>
> how can I trunc time in EntityFramework?
>
> I tried it this way:
>
>   model.Datas
>.GroupBy(d => 
> DbFunctions.TruncateTime(d.TimeStamp))
>.Select(d => d.Key.Value)
>.ToArray();
>
> But get this error:
>   "SQLite error (1): no such function: TruncateTime"
>
> How else can I use the "date(timestring) function in EntityFramework?
>

No solution for this? :(

Regards Steffen

[sqlite] design problem involving trigger

[R.Smith]

On 2015-08-21 11:23 PM, Will Parsons wrote:
> On 21 Aug 2015, R.Smith wrote:
>>
>> On 2015-08-21 04:47 AM, Will Parsons wrote:
>>> I'm working on a program that involves a catalogue of books.  Part of
>>> the database schema looks like this:
>>>
>>> create table Books(id integer primary key,
>>>  title text collate nocase not null,
>>>  author references Authors(id),
>>>  ...
>>>  unique(title, author));
>>>
>>> create table Authors(id integer primary key,
>>>name text unique not null check(name <> ''));
>>>
>>> The idea here is that the title+author of a book must be unique, but a
>>> book may not necessarily have an author associated with it.  But, the
>>> schema fragment as I have presented it does not disallow entering the
>>> same title with a null author multiple times, which is clearly
>>> undesirable.
>>>
>>> In thinking about how to avoid this, one possibility that has occurred
>>> to me is to add an entry to the Authors table with id=0, name=NULL, and
>>> to modify the Books table to be:
>>>
>>> create table Books(id integer primary key,
>>>  title text collate nocase not null,
>>>  author references Authors(id) not null,
>>>  ...
>>>  unique(title, author));
>>>
>>> With this, entries in the Books table that presently have the author
>>> field set to NUll would instead have author=0.
>>>
>>> What I would like to have is a trigger that when an attempt is made to
>>> enter a new record into the Books table with a NULL author field, is
>>> to force the author field to 0 instead.  I can't see how to do this
>>> with a "before insert" trigger, though.  Perhaps I'm approaching this
>>> the wrong way; in any case I'd appreciate advice.
>>>
>> Nothing wrong with your approach, simply declare the Author to be NOT
>> NULL and if it is possible to have non-Authors you could do what you
>> suggest, add a 0-Author to the parent table, or, you could use a partial
>> Index (available after SQLite 3.7 only I think) which would allow you to
>> have NULLS but still maintain a strict relation, something like this:
>>
>> create table Books(id integer primary key,
>>  title text collate nocase not null,
>>  author int not null references Authors(id),
>>  ...
>>  );
>>
>> create unique index uBookAuth on Books(title,author) where author is not 
>> null;
>>
>> create table Authors(id integer primary key,
>>name text unique not null check(name <> ''));
> I guess I don't understand how having a partial index where author is
> not null prevents adding two entries with the same title and a null
> author.  How is this different from what I have now with the
> "unique(title, author)" specification?
>

I think I may have misinterpreted slightly...

To clarify: are the book titles unique or are they not?

If they are Unique, i.e if no book title can ever appear twice, 
regardless who authored it, then simply declare a unique index on title.
If they are NOT Unique, i.e. if a title can appear more than once, then 
why is it not working for you?

Essentially, if I interpret correctly, you are asking the DB to NOT 
limit the number of same-titled books, except when you have supplied an 
author and the same author has already such a titled book, but then when 
you don't supply an author, it should know to now also limit the copies 
of  no-author books?

I assumed before that you only added NULL for author if you don't know 
the author yet (which would make sense and can later be updated) but 
then you can't force the unique constraint, there may be many books with 
coinciding titles and not-yet-known authors.

Reading again, I am now thinking that's not the case, you might add NULL 
authors to books which simply don't have authors (well, all books have 
authors, but the author might be unknown and accepted to be of unknown 
status for time to come), in which case, there might be many same-titled 
unknown-author books.

If this is the case and you still want to limit unknown author books to 
just 1 instance, I would suggest to use an explicit author name, maybe 
something like "(by Unknown)" which would be happily subdued by the 
Unique constraint if violated. Another reader suggested the empty 
string, which will also count as a valid author and do the limiting you 
require. Your own suggestion of using a zero ID in stead will also do 
the job just fine. In fact, this is the only reason we'd ever put NULL 
into a field such as this, precisely so that the Unique constraint is 
not forced, because for any valid value, it will be.

A note on NULL and why: NULL + NULL != 2(NULL).

NULL is not a valid value, in fact it isn't a value at all, it is not 
meant to appear in anything data related barring to indicate a fault or 
voidness. You can't have 

[sqlite] : it seems to be only orber by and group

[Clemens Ladisch]

afriendandmore wrote:
> The table shall be ordered according to A. (Order by A)
> But if C ist true and to the value B1 in this Record, there exists another 
> Record with B2 and B2 = B1, then B1 should be ordered in the row above B2.

Do those two records have the same A values?


Regards,
Clemens

[sqlite] : it seems to be only orber by and group

[Kevin Benson]

On Sat, Aug 22, 2015

> afriendandmore wrote:
> > The table shall be ordered according to A. (Order by A)
> > But if C ist true and to the value B1 in this Record, there exists
> another Record with B2 and B2 = B1, then B1 should be ordered in the row
> above B2.


If the B1 and B2, to which you refer, are just two equal values in
different rows of column B, then it sounds like you want:

SELECT * FROM table
 ORDER BY columnA, columnB
 CASE WHEN columnC = 1
 THEN 0 ELSE 1 END;

If neither ASC or DESC are specified, rows are sorted in ascending (smaller
values first) order by default.
--
   --
  --
 --???--
K e V i N

[sqlite] : it seems to be only orber by and group

[afriendandmore]

but I fail to get it done.


Hello,

I got the following problem, that I cannot get worked out.

I have a table that has 3 columns A, B and C.
C is a boolean Column, that indicates if the record has been logically 
deleted, It shall not be removed from the database.
The table shall be ordered according to A. (Order by A)
But if C ist true and to the value B1 in this Record, there exists 
another Record with B2 and B2 = B1, then B1 should be ordered in the row 
above B2.

B



A



C 1=true, 0 = false

307



{null}



1

307



{null}



1

307



{null}



1

307



1.7



0

301



1,5



1

301



2



0

304



3



0

305



3



1

305



3



0


Thank you very much for any help.

[sqlite] Stuck in busy handler

[Simon Slavin]

On 22 Aug 2015, at 2:33pm, Jon Kuhn  wrote:

> I am working on project that uses SQLite to save records in an in memory
> database and periodically push them to an on-disk database.  The project is
> multi-threaded with several threads accessing the databases (memory and
> on-disk) at the same time.  Each thread uses its own connection to the
> database.
> 
> We have built a layer on top of SQLite that, among other things, manages
> retrying when a database operation returns SQLITE_LOCKED or SQLITE_BUSY.
> When a transaction is first attempted, it is started with BEGIN DEFERRED
> TRANSACTION and when it is retried it is started with BEGIN EXCLUSIVE
> TRANSACTION.  This is done with the intent to prevent starvation.

Unfortunately you're feeding one thread by starving another.  SQLite has its 
own backoff algorithm which should prevent any situation where both threads 
will be locked for more than in instant.

Here's the key:

> - Not using a busy handler seems to fix the problem.

You are doing things in your layer which defeat SQLite's locking, or which just 
reproduce the things SQLite would be doing automatically.

For diagnostic purposes do the following:

Strip out your code which manages retrying.  Strip out your busy handler.  
Strip out anything you do to handle contention, SQLITE_BUSY or any other 
temporary access problems.  Use 'BEGIN' for all transactions, rather than 
trying to specify your own DEFERRED or IMMEDIATE.  Pretend you've never seen 
_BUSY or _LOCKED before.

Set a retry period of two minutes (I'm serious: it should be long enough that 
your users conclude that the application has crashed and reboot it.) using one 
of the following:




It doesn't matter which one, they do the same thing.

Now try your application again.  Does it work better or worse ?

If it's worse, look at how long you are keeping your transactions open.  You 
should never do a BEGIN unless you have all the data ready to complete the 
transaction and can immediately issue all the commands needed up to and 
including the END.  Is there something in your design which prevents that ?

Simon.

[sqlite] There is any reason to sqlite not expand "*" in function calls ?

[R.Smith]

On 2015-08-22 11:42 AM, sqlite-mail wrote:
> Hello !
>
> I'm testing the new json functions and when I tried this:
>
> select json_array(*) as json from one_table;
>
> I've got :
>
> []
>
> []
>
> ..
>
> Then I tried with some custom functions accepting variable number of
> parameters and realize that "*" is not expanded for function calls.
>
> There is any reason for it or it's a forgotten implementation ?

I don't think it is "forgotten", it simply can't really work. The 
asterisk "*" is a SELECT short-hand, not a function short-hand, not in 
any SQL that I have ever used (but I could be wrong, maybe someone has a 
contrary example).

It is impossible for the vast majority of functions to take an 
indefinite parameter count that would conform to * semantics, that is:
  -  might be Zero parameters,
  -  might be any number higher than zero
  -  might include NULLs (such as in LEFT-JOIN conditions)
  -  might contain any mix of value types (especially in SQLite where no 
column is guranateed to only contain the value type set in the Schema)

Most functions return a value based on an input that is specific. What 
would you envision this sort of function-call might return:
SELECT Round(*) FROM users;

SELECT min(*) FROM contacts;

SELECT date(*) FROM schedule1;

Even if you had a use-case for a table that included one column only, or 
perhaps a few columns all of the same type (perhaps all REAL columns) 
that might satisfy the circumstances to be able to run a query like the 
first of the three above, it would still be such an outwardly unique 
use-case that any effort to try and make it work like that would be 
vastly more than the collective global efforts to simply type the column 
names out when that situation arise.

Lastly, even if you did try to write such a function, the function has 
no access to the SQL engine, it only gets the parameter the SQL engine 
feeds it and it must give back a single value that will be reported back 
as the column value for that iteration of the Cursor. Neither can you 
dictate from within a function to the data cursor to reshape the data 
cursor's column layout - even if this was programmatically possible, it 
would probably break relational theory.

(I know with the json function you simply output a string, so the last 
point doesn't matter to it specifically, but the point is that such 
functions are not supported because they would, in the general case, not 
be useful)

[sqlite] There is any reason to sqlite not expand "*" in function calls ?

[sqlite-mail]

Thanks for the answer !  

How hard would be to implement something like that ?  

O maybe a function called "expand" that would return the expanded wildcard
parameters ?  

Ex: select json_array(expand(*)) as json from one_table;  

Ex2 inside a trigger: select raise("Something is not right here !") where
my_generic_trigger_function(trigger.type, new.*, old.*) = 1;  

For triggers would be nice to have a metadata parameter maybe called
"trigger" with info about "table name", "field name", "before/after/instead
insert/update/delete".  

?  

Maybe now with this new "table-valued functions" we can have an easier way to
emulate "create function", it seems that we can somehow pass parameters and
retrieve variable number of columns.


I always miss something like this mainly on triggers to make generic user
defined functions.  

Anyone have any idea about this ?  

?  

Cheers !  

?  
>  Sat Aug 22 2015 13:01:32 CEST from "Richard Hipp"  
>Subject: Re: [sqlite] There is any reason to sqlite not expand "*" in
>function calls ?
>
>  On 8/22/15, sqlite-mail  wrote:
> 
>  
>>Then I tried with some custom functions accepting variable number of
>> parameters and realize that "*" is not expanded for function calls.
>> 
>> There is any reason for it or it's a forgotten implementation ?
>> 
>> 

>  Well, one reason is that "somefunction(*)" does not expand the "*" to
> a list of all columns in any other function in any other SQL database
> engine, that I am aware of. That behavior is without precedent.
> 
> -- 
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] Compile warnings

[Richard Hipp]

On 8/22/15, Scott Robison  wrote:
>  I don't object to a change to accommodate C99 null
> pointer requirements

Please note that the warning in question has nothing to do with NULL
pointers.  The pointer parameter (0) is non-null.  The issue is that
the last parameter (2) which is a size_t might sometimes be zero and
the compiler felt compelled to share that bit of knowledge, thinking
that we might have meant to put the zero in the penultimate parameter
(1) which is of type int.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] When sqlite3_close() returns SQL_BUSY

[Eduardo Morras]

On Sat, 22 Aug 2015 05:07:55 -0500
Jeff M  wrote:

> The doc page for SQLITE_BUSY covers SQLITE_BUSY when there are
> multiple connections to the database.
> 
>   SQLite Result Codes (SQLITE_BUSY)
>   http://www.sqlite.org/rescode.html#busy
> 
> But, I have only one connection.  I believe the case where
> SQLITE_BUSY is returned by sqlite_close() due to unfinalized prepared
> statements should be mentioned there.

Perhaps you forget to call sqlite3_finalize() on at least one query of your 
code.

> Jeff


---   ---
Eduardo Morras 

[sqlite] libtclsqlite3 assistance

[Eduardo Morras]

On Fri, 21 Aug 2015 20:19:38 -0700
jungle Boogie  wrote:

> Hi Dr. H,
> On 21 August 2015 at 14:23, Richard Hipp  wrote:
> > On 8/21/15, jungle Boogie  wrote:
> >>
> >> Is it possible to compile in libtclsqlite3?
> >>
> >
> > Dunno.  But you can compile the libtclsqlite3.so yourself:
> >
> > make tclsqlite3.c
> > gcc -fPIC -shared -I. -o libtclsqlite3.so tclsqlite3.c
> 
> 
> Made it fine but compiling not so much:
> 
> /usr/local/bin/gcc48 -fPIC -shared -I. -o libtclsqlite3.so
> tclsqlite3.c tclsqlite3.c:162240:17: fatal error: tcl.h: No such file
> or directory
>  #include "tcl.h"
>  ^
> compilation terminated.
> 
> I have tcl.h here:
> /usr/local/include/tcl8.6/generic/tcl.h
> /usr/local/include/tcl8.6/tcl.h

Add the include path to /usr/local/include/tcl8.6

gcc -fPIC -shared -I. -I/usr/local/include/tcl8.6 -o libtclsqlite3.so 
tclsqlite3.c

---   ---
Eduardo Morras 

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[Kees Nuyt]

On Fri, 21 Aug 2015 22:58:51 +0200, "sqlite-mail"
 wrote:

> That's why I think that expose a basic SQL ANSI catalog would make this kind
> of work and others a lot easier.  

That would be nice to have in some development environment (you
could develop one, if nobody else has done it), but in my
opinion it does not belong in the core engine that SQLite is.
It's lite for good reasons.

It even would be fine to me if all ALTER TABLE features would be
removed.

-- 
Regards, 

Kees Nuyt

[sqlite] There is any reason to sqlite not expand "*" in function calls ?

[sqlite-mail]

Hello !  

I'm testing the new json functions and when I tried this:  

select json_array(*) as json from one_table;  

I've got :  

[]  

[]  

..  

Then I tried with some custom functions accepting variable number of
parameters and realize that "*" is not expanded for function calls.  

There is any reason for it or it's a forgotten implementation ?  

Cheers !

[sqlite] Compile warnings

[Scott Robison]

On Sat, Aug 22, 2015 at 10:31 AM, Richard Hipp  wrote:

> On 8/22/15, Scott Robison  wrote:
> >  I don't object to a change to accommodate C99 null
> > pointer requirements
>
> Please note that the warning in question has nothing to do with NULL
> pointers.  The pointer parameter (0) is non-null.  The issue is that
> the last parameter (2) which is a size_t might sometimes be zero and
> the compiler felt compelled to share that bit of knowledge, thinking
> that we might have meant to put the zero in the penultimate parameter
> (1) which is of type int.
>

I understand that. I was just using that as an example of a relatively
recent change that was made to accommodate C99. I know this more recent
warning has nothing to do with standards compliance. The similarity just
has to do with making changes to an ANSI-C code base (
https://www.sqlite.org/howtocompile.html) to accommodate non-ANSI-C
compilers or compilers which issue warnings for cases which they are not
"smart enough" to recognize the context. This is why they are warnings in
the first place. If the compiler knew with absolute certainty that the code
in question was erroneous, I have to believe they'd issue an error instead.
They don't / can't know this, so they issue a "hey, you'd better double
check this, but I'll defer to your judgement" message instead.

Just yesterday we had a member of our build team report that a build was
broken to a dev in a neighboring cubicle. It was broken because warnings
were being treated as errors in the build system, and there was a warning
in a third party library we use (not SQLite). As I've said before, I'm all
for trying to eliminate all warnings, and that is what I strive to do.
Sometimes you have to suppress the warning (or start maintaining patches to
a third party code base). There are often ways to do so in a fine grained
way so that you don't lose the value of the warning in all cases, just
those you've confirmed are valid. And there are warnings that are worthless
and must be suppressed completely (such as the Visual C++ warnings having
to do with safety of certain standard library functions; the same is true
of OpenBSD, and many others I am sure). If I am trying to write portable
code that can build in many varying environments, I need to use standard
library functions at times, not environment specific replacements. Or
pepper my code with conditionals, or abstract away all functions that might
generate warnings into a set of shims, or whatever. I prefer to just use
the standard functions the way they were intended.

Not that you were claiming otherwise. I'll stop raving like a lunatic now.

-- 
Scott Robison

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[Tim Streater]

On 22 Aug 2015 at 11:01, Kees Nuyt  wrote: 

> It even would be fine to me if all ALTER TABLE features would be
> removed.

I disagree with this, although I'd be satisfied if there were a separate 
library and API for such things. With an evolving application I need to have 
ALTER TABLE. Even the reduced version we have now is adequate.

--
Cheers  --  Tim

[sqlite] Compile warnings

[Scott Robison]

Unless of course your objective is to write maximally portable code. It's
not perfect, and certainly things have been done to accommodate more recent
standards, but C89 compilers (including compilers that claim to support it
through a switch) are more common than even full C99 implementations.

Don't get me wrong, I don't object to a change to accommodate C99 null
pointer requirements or even (necessarily) a change to suppress warnings,
even if both requirements are needlessly strict in some situations. I just
don't think that C99 or some compilers warning setup should mandate a
change.
On Aug 22, 2015 8:36 AM, "David Bennett"  wrote:

> True. The C89/90 standard has of course been withdrawn and I don't have a
> copy. However, based on the drafts I have available the entirety of the
> text
> I quoted was added after the last correction to C89/90 and first appears in
> C99. In my opinion it would be unwise to rely on the omission of material
> from the earlier version of the standard to justify code that would be
> non-compliant with C99 and all later standards.
>
> Regards
> David M Bennett FACS
>
> Andl - A New Database Language - andl.org
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Scott
> Robison
> Sent: Saturday, 22 August 2015 2:05 AM
> To: General Discussion of SQLite Database
> ; davidb at pfxcorp.com
> Subject: Re: [sqlite] Compile warnings
>
> And C89 doesn't have the valid pointer requirement On Aug 21, 2015 7:03 AM,
> "David Bennett"  wrote:
>
> > Addressing only standards compliance, the C99 (n1256) standard says as
> > follows.
> >
> > 7.21.1 /2
> > Where an argument declared as size_t n specifies the length of the
> > array for a function, n can have the value zero on a call to that
> > function. Unless explicitly stated otherwise in the description of a
> > particular function in this subclause, pointer arguments on such a
> > call shall still have valid values, as described in 7.1.4. On such a
> > call, a function that locates a character finds no occurrence, a
> > function that compares two character sequences returns zero, and a
> > function that copies characters copies zero characters.
> >
> > Later versions of the standard contain similar wording.
> >
> > A zero value for the third argument of memset() is standards
> > compliant. Any warning by any compiler is for the convenience of
> > developers and may be safely disabled or ignored while remaining
> standards
> compliant.
> >
> > In my opinion, disabling or simply ignoring the warning are both
> > legitimate choices. Modifying the code to suppress the warning is NOT.
> >
> > Regards
> > David M Bennett FACS
> >
> > Andl - A New Database Language - andl.org
> >
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org
> > [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of
> > Scott Robison
> > Sent: Friday, 21 August 2015 3:05 AM
> > To: General Discussion of SQLite Database
> > 
> > Subject: Re: [sqlite] Compile warnings
> >
> > On Thu, Aug 20, 2015 at 10:46 AM, Scott Doctor 
> > wrote:
> >
> > > My opinion is to keep it portable. The warning is there for a reason.
> > > you are worried about performance yet are calling a function that
> > > does nothing, which will take more memory cycles than a simple check
> for
> zero.
> > >
> >
> > I didn't say don't make a change. I'm observing there are things to
> > consider. In particular the optimization strategies that the SQLite
> > team have pursued for several years now have been *tiny* little
> > optimizations that would never be justifiable on their own but that
> > really add up, particularly for small devices probably running on
> batteries.
> >
> >
> > > Trying to memset a zero length is a bug, not the warning. Add an if
> > > statement around it. If the variable is local, it will probably be
> > > optimized as a register variable and a zero check of a register is a
> > > single op-code.
> > >
> >
> > Trying to memset a zero length buffer is not a bug if the length of
> > the sequence of bytes that needs to be set is zero. C89 in particular
> > does not disallow this use.
> >
> >
> > > the problem with disabling warnings is that even if this instance is
> > > not an error, some other part of the code may end up with the same
> > > situation but is an error in the coding. I would prefer code that
> > > can be compiled with all warnings turned on that gives no warnings
> > > than have a potential problem because of a glitch in the code.
> > >
> >
> > I don't think anyone called for disabling the warning. I think it
> > should just be ignored in this case, just as I ignore warnings that
> > are generated by Visual C++ when I compile SQLite there.
> >
> > --
> > Scott Robison
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > 

[sqlite] Compile warnings

[David Bennett]

True. The C89/90 standard has of course been withdrawn and I don't have a
copy. However, based on the drafts I have available the entirety of the text
I quoted was added after the last correction to C89/90 and first appears in
C99. In my opinion it would be unwise to rely on the omission of material
from the earlier version of the standard to justify code that would be
non-compliant with C99 and all later standards.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Scott
Robison
Sent: Saturday, 22 August 2015 2:05 AM
To: General Discussion of SQLite Database
; davidb at pfxcorp.com
Subject: Re: [sqlite] Compile warnings

And C89 doesn't have the valid pointer requirement On Aug 21, 2015 7:03 AM,
"David Bennett"  wrote:

> Addressing only standards compliance, the C99 (n1256) standard says as 
> follows.
>
> 7.21.1 /2
> Where an argument declared as size_t n specifies the length of the 
> array for a function, n can have the value zero on a call to that 
> function. Unless explicitly stated otherwise in the description of a 
> particular function in this subclause, pointer arguments on such a 
> call shall still have valid values, as described in 7.1.4. On such a 
> call, a function that locates a character finds no occurrence, a 
> function that compares two character sequences returns zero, and a 
> function that copies characters copies zero characters.
>
> Later versions of the standard contain similar wording.
>
> A zero value for the third argument of memset() is standards 
> compliant. Any warning by any compiler is for the convenience of 
> developers and may be safely disabled or ignored while remaining standards
compliant.
>
> In my opinion, disabling or simply ignoring the warning are both 
> legitimate choices. Modifying the code to suppress the warning is NOT.
>
> Regards
> David M Bennett FACS
>
> Andl - A New Database Language - andl.org
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of 
> Scott Robison
> Sent: Friday, 21 August 2015 3:05 AM
> To: General Discussion of SQLite Database 
> 
> Subject: Re: [sqlite] Compile warnings
>
> On Thu, Aug 20, 2015 at 10:46 AM, Scott Doctor 
> wrote:
>
> > My opinion is to keep it portable. The warning is there for a reason.
> > you are worried about performance yet are calling a function that 
> > does nothing, which will take more memory cycles than a simple check for
zero.
> >
>
> I didn't say don't make a change. I'm observing there are things to 
> consider. In particular the optimization strategies that the SQLite 
> team have pursued for several years now have been *tiny* little 
> optimizations that would never be justifiable on their own but that 
> really add up, particularly for small devices probably running on
batteries.
>
>
> > Trying to memset a zero length is a bug, not the warning. Add an if 
> > statement around it. If the variable is local, it will probably be 
> > optimized as a register variable and a zero check of a register is a 
> > single op-code.
> >
>
> Trying to memset a zero length buffer is not a bug if the length of 
> the sequence of bytes that needs to be set is zero. C89 in particular 
> does not disallow this use.
>
>
> > the problem with disabling warnings is that even if this instance is 
> > not an error, some other part of the code may end up with the same 
> > situation but is an error in the coding. I would prefer code that 
> > can be compiled with all warnings turned on that gives no warnings 
> > than have a potential problem because of a glitch in the code.
> >
>
> I don't think anyone called for disabling the warning. I think it 
> should just be ignored in this case, just as I ignore warnings that 
> are generated by Visual C++ when I compile SQLite there.
>
> --
> Scott Robison
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Stuck in busy handler

[Jon Kuhn]

I am running into a situation with 2 threads where a busy handler is being
called on one thread and unless the busy handler returns 0 and the calling
code retries the query, neither thread will be able to make progress.
Below is some background information:

I am working on project that uses SQLite to save records in an in memory
database and periodically push them to an on-disk database.  The project is
multi-threaded with several threads accessing the databases (memory and
on-disk) at the same time.  Each thread uses its own connection to the
database.

We have built a layer on top of SQLite that, among other things, manages
retrying when a database operation returns SQLITE_LOCKED or SQLITE_BUSY.
When a transaction is first attempted, it is started with BEGIN DEFERRED
TRANSACTION and when it is retried it is started with BEGIN EXCLUSIVE
TRANSACTION.  This is done with the intent to prevent starvation.

We also register a busy handler.  For a given connection the busy handler
will return 1 for up to a minute and then, after 1 minute has passed return
0.  If 1 minute passes it is regarded as an error.

I have peeled back the layers and written some plain C++11 sample code that
demonstrates the situation I am running in to.  I have omitted error
checking and cleanup code to make the code easier to read.  The code can be
found here: https://copy.com/yeT6DShawgrzg27X  Also, a visualization of the
callstacks can be found here:  https://copy.com/kdrtb2h18vqIVrvB

I have identified the following workarounds that seem to resolve this
issue, but I would like to understand which is the best solution and why (I
want to know specifically what rules/best practices we may be violating):
 - Opening the on-disk database in shared-cache mode seems to fix the
problem.  (We tried this since the in-memory database must be opened in
shared-cache mode to share it between threads, and thought it may be good
to have both using the same mode)
 - Repeating transactions with BEGIN DEFERRED TRANSACTION rather than BEGIN
EXCLUSIVE TRANSACTION seems to fix the problem.
 - Not using a busy handler seems to fix the problem.

Thanks,
Jon

[sqlite] There is any reason to sqlite not expand "*" in function calls ?

[Richard Hipp]

On 8/22/15, sqlite-mail  wrote:
>
> Then I tried with some custom functions accepting variable number of
> parameters and realize that "*" is not expanded for function calls.
>
> There is any reason for it or it's a forgotten implementation ?
>

Well, one reason is that "somefunction(*)" does not expand the "*" to
a list of all columns in any other function in any other SQL database
engine, that I am aware of.  That behavior is without precedent.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] When sqlite3_close() returns SQL_BUSY

[Jeffrey Mattox]

No, I needed to set a timeout (see previous messages in this thread).  I've 
fixed my problem.  I'm suggesting now that the documentation for SQLITE_BUSY  
is incomplete.

Jeff

> On Aug 22, 2015, at 5:13 AM, Eduardo Morras  wrote:
> 
> On Sat, 22 Aug 2015 05:07:55 -0500
> Jeff M  wrote:
> 
>> The doc page for SQLITE_BUSY covers SQLITE_BUSY when there are
>> multiple connections to the database.
>> 
>>SQLite Result Codes (SQLITE_BUSY)
>>http://www.sqlite.org/rescode.html#busy
>> 
>> But, I have only one connection.  I believe the case where
>> SQLITE_BUSY is returned by sqlite_close() due to unfinalized prepared
>> statements should be mentioned there.
> 
> Perhaps you forget to call sqlite3_finalize() on at least one query of your 
> code.
> 
>> Jeff
> 
> 
> ---   ---
> Eduardo Morras 
> 

[sqlite] When sqlite3_close() returns SQL_BUSY

[Jeff M]

The doc page for SQLITE_BUSY covers SQLITE_BUSY when there are multiple 
connections to the database.

SQLite Result Codes (SQLITE_BUSY)
http://www.sqlite.org/rescode.html#busy

But, I have only one connection.  I believe the case where SQLITE_BUSY is 
returned by sqlite_close() due to unfinalized prepared statements should be 
mentioned there.

Jeff


> On Aug 21, 2015, at 3:51 AM, R.Smith  wrote:
> 
> Hi Jeff,
> 
> On 2015-08-21 07:30 AM, Jeff M wrote:
>> Sometimes my iOS app creates an unreasonable number of prepared statements 
>> (perhaps 1,000, an app bug that I'm fixing).  These prepared statements are 
>> later finalized just prior to doing sqlite3_close(), which sometimes returns 
>> SQL_BUSY.  The docs say SQL_BUSY will be returned if I haven't finalized all 
>> prepared statements, but I believe I have done so.  My iOS app has only one 
>> connection to the DB and I'm doing all this work on the main thread.
>> 
>> 1.  The docs don't say what to do in the case of SQL_BUSY.  Does that mean 
>> I've certainly failed to finalize one or more prepared statements, or does 
>> SQLite just need more time (in which case can I loop on sqlite3_close() 
>> until I get SQLITE_OK)?
> 
> SQL_BUSY does not mean anything bad except that you are trying to do some 
> work on a query (read: prepared statement) while another is still not done 
> with its duties. These duties may in your case simply mean that the "closing" 
> of a previous prepared statement is still under way, so yes, it just needs a 
> moment. You can wait a moment and try again.
> 
> I will mention (as Simon is likely to point out soon!) that the good news is: 
> SQLite will do this waiting-and-retrying for you if you simply set a suitable 
> time-out, perhaps in the order of a minute or more, using the pragma:
> 
> http://www.sqlite.org/pragma.html#pragma_busy_timeout
> 
> or, if you prefer using the C-interface:
> http://www.sqlite.org/c3ref/busy_timeout.html
> 

[sqlite] BUG: sqlite 3.8.11.x left join

[Mark Brand]

Just realized that this simpler case shows the same problem:

SELECT *
FROM (
 SELECT 'apple' fruit
 UNION ALL SELECT 'banana'
) a
LEFT JOIN (
 SELECT 1 isyellow
) c ON a.fruit='banana'
;

On 22/08/15 00:58, Mark Brand wrote:
> Hi,
>
> For the query below, versions 3.8.11.0 and 3.8.11.1 return only 1 row. 
> For some reason, the LEFT JOIN seems to behave like a JOIN.
>
> Older versions (tested 3.8.7.4 and 3.8.10.2) correctly return 2 rows.
>
> SELECT *
> FROM (
> SELECT 'apple' fruit
> UNION ALL SELECT 'banana'
> ) a
> JOIN (
> SELECT 'apple' fruit
> UNION ALL SELECT 'banana'
> ) b ON a.fruit=b.fruit
> LEFT JOIN (
> SELECT 1 isyellow
> ) c ON b.fruit='banana'
> ;
>
> regards,
>
> Mark

[sqlite] BUG: sqlite 3.8.11.x left join

[Mark Brand]

Hi,

For the query below, versions 3.8.11.0 and 3.8.11.1 return only 1 row. 
For some reason, the LEFT JOIN seems to behave like a JOIN.

Older versions (tested 3.8.7.4 and 3.8.10.2) correctly return 2 rows.

SELECT *
FROM (
 SELECT 'apple' fruit
 UNION ALL SELECT 'banana'
) a
JOIN (
 SELECT 'apple' fruit
 UNION ALL SELECT 'banana'
) b ON a.fruit=b.fruit
LEFT JOIN (
 SELECT 1 isyellow
) c ON b.fruit='banana'
;

regards,

Mark