[sqlite] Primary key values can be NULL

[R Smith]

On 2016/04/14 10:23 PM, Cecil Westerhof wrote:
> 2016-04-14 22:10 GMT+02:00 Richard Hipp :
>
>> On 4/14/16, Cecil Westerhof  wrote:
>>> ?Yes that makes sense. But could not a type of PRAGMA be used? So if the
>>> PRAGMA is not defined the old functionality and your historical data is
>>> save. And if the PRAGMA is defined the new functionality.
>>>
>> Easier:  Just declare the columns in question as NOT NULL.  Or use a
>> WITHOUT ROWID table which *does* enforce NOT NULL.
>>
> ?The problem is that you do not expect that values in the primary key can
> be NULL, so you do not use NOT NULL. I now know that I need to do this and
> I do it now, but the unaware will be bitten, just as I was. It was not a
> big problem, because I just started playing with SQLite, but when you have
> a very big database which gets a lot of data every day and a year later you
> find out you are bitten by this ?

What makes you "expect" this?  Nothing in the SQLite documentation I 
hope. Is it perhaps habit based on how some other DBs do it?

How will "those who don't know about NOT NULL" be better serviced by a 
pragma which they also don't know about?
The documentation is very clear on the matter, even a cursory glance 
would educate "the unaware".

Making more things with more documentation to read, in order to help 
people with other documentation they didn't read - not a salient course 
of action I think.

Cheers,
Ryan

[sqlite] Patch that add table alias capability to delete/update statements.

[Domingo Alvarez Duarte]

Hello !  

Here is a patch that add table alias capability to delete/update statements. 


https://github.com/mingodad/sqlite/commit/817636a48e5c92f7b747f3ddf487cbda21ec
8fa5  

Cheers !

[sqlite] Primary key values can be NULL

[Cecil Westerhof]

2016-04-14 22:10 GMT+02:00 Richard Hipp :

> On 4/14/16, Cecil Westerhof  wrote:
> >
> > ?Yes that makes sense. But could not a type of PRAGMA be used? So if the
> > PRAGMA is not defined the old functionality and your historical data is
> > save. And if the PRAGMA is defined the new functionality.
> >
>
> Easier:  Just declare the columns in question as NOT NULL.  Or use a
> WITHOUT ROWID table which *does* enforce NOT NULL.
>

?The problem is that you do not expect that values in the primary key can
be NULL, so you do not use NOT NULL. I now know that I need to do this and
I do it now, but the unaware will be bitten, just as I was. It was not a
big problem, because I just started playing with SQLite, but when you have
a very big database which gets a lot of data every day and a year later you
find out you are bitten by this ?

-- 
Cecil Westerhof

[sqlite] Using SQLite for storing photo's

[Cecil Westerhof]

2016-04-14 22:07 GMT+02:00 Clemens Ladisch :

> Cecil Westerhof wrote:
> > what is the best way to make a backup?
>
> With the backup API: .
> (Also available as .backup in the shell.)
>

?I should be more precise in my communication. :'-(

I prefer to make my backup with .dump. Then I have just a text based
backup. This works fine for the regular data, but not for photos: they can
be 13 MB big. (And I am not even using raw. When I am going to do that,
they will become much bigger.) So I would like to have my photos split out
when making the dump.

But maybe it will be better to use external blobs.

Something to think about.

-- 
Cecil Westerhof

[sqlite] Using SQLite for storing photo's

[Cecil Westerhof]

2016-04-14 21:37 GMT+02:00 Simon Slavin :

>
> On 14 Apr 2016, at 8:10pm, Cecil Westerhof  wrote:
>
> > ?I would like to have everything in one file.
>
> From previous discussions on this list, what you want to do will work
> fine.  Of course you should make sure your operating system is able to deal
> with a very big file, and that your backup protocol works.
>

?Well, I will try. I have a ?few? applications that I want to write using
SQLite, so I will not get bored for some time. :-D

-- 
Cecil Westerhof

[sqlite] Using SQLite for storing photo's

[Teg]

Hello Cecil,

I  have multiple 30-40 GB Sqlite DB's which contain images as blobs. I
back   them   up   by   copying   them  to  my  backup  drive.  Unless
your application runs 24x7, I see no reason to do anything complicated with
the backup.

My  image  blobs are in a table which contains an image and a rowid to
ID  the  image. The meta data is all in a different table. It would be
simply  to  split  this  out into multiple DB's but from a performance
perspective,  I  don't  see  the point. It's plenty fast enough for my
usage.

I'm  not  using  the Blob IO routines. I just copy them into memory in
one  go  when  I  need  them.  If  my machine was slower I might do it
differently.  As long as I can retrieve and view the images as fast as
I  can  page them, I'm satisfied.  I do have export routines to export
them as images or CBR/CBZ files. 

I like them all in one file too.

C


Thursday, April 14, 2016, 4:18:58 PM, you wrote:

CW> 2016-04-14 22:07 GMT+02:00 Clemens Ladisch :

>> Cecil Westerhof wrote:
>> > what is the best way to make a backup?
>>
>> With the backup API: .
>> (Also available as .backup in the shell.)
>>

CW> ?I should be more precise in my communication. :'-(

CW> I prefer to make my backup with .dump. Then I have just a text based
CW> backup. This works fine for the regular data, but not for photos: they can
CW> be 13 MB big. (And I am not even using raw. When I am going to do that,
CW> they will become much bigger.) So I would like to have my photos split out
CW> when making the dump.

CW> But maybe it will be better to use external blobs.

CW> Something to think about.




-- 
 Tegmailto:Teg at djii.com

[sqlite] Using SQLite for storing photo's

[Clemens Ladisch]

Cecil Westerhof wrote:
> what is the best way to make a backup?

With the backup API: .
(Also available as .backup in the shell.)


Regards,
Clemens

[sqlite] Primary key values can be NULL

[Cecil Westerhof]

2016-04-14 21:46 GMT+02:00 Richard Hipp :

> On 4/14/16, Cecil Westerhof  wrote:
> > For example a database with partial tables can not be read with a
> > SQLite before 3.8.0. So why is SQLite not changed so primary keys can not
> > have the value NULL?
>
> You fail to distinguish between "forwards compatible" and "backwards
> compatible".
>
> Upgrading your program from version X to version Y (with Y>X) should
> never cause your historical data to become unreadable.  That's what
> "backwards compatible" means.  Upgrading does not break stuff.  SQLite
> is backwards compatible.
>

?Yes that makes sense. But could not a type of PRAGMA be used? So if the
PRAGMA is not defined the old functionality and your historical data is
save. And if the PRAGMA is defined the new functionality.

-- 
Cecil Westerhof

[sqlite] Checks with dates

[Cecil Westerhof]

2016-04-14 21:26 GMT+02:00 Richard Hipp :

> On 4/14/16, Cecil Westerhof  wrote:
> > I have a table where I have two fields: toStart and finishBefore. They
> are
> > both dates and when filled the format should be %Y-%m-%d. How can this be
> > checked? 2016-04-31 should not be accepted.
> >
> > The second part is that when both are filled, then finishBefore should be
> > after toStart. Is that possible to check?
>
> Untested:
>
> CHECK( toStart=date(toStart)
> AND finishBefore=date(finishBefore)
> AND finishBefore>toStart )
>

?Does not work, but I have something to play with. ;-)

-- 
Cecil Westerhof

[sqlite] Primary key values can be NULL

[Cecil Westerhof]

I was bitten by the fact that values of a primary key can be NULL. I
understood that this is for historical reasons. But there has been a lot of
changes. For example a database with partial tables can not be read with a
SQLite before 3.8.0. So why is SQLite not changed so primary keys can not
have the value NULL?

-- 
Cecil Westerhof

[sqlite] Working with UUID

[Cecil Westerhof]

I want to work with UUID's. (Version 4.) What is the smartest way to put a
check on this?

Also: when using text to save them, you need 36 bytes instead of four
bytes. When using a lot, it is better to use blob. I have to post the
peculiarities I found with that another time. (For example when writing a
lot and crashing the database is changed instead off rolled back.) I am
leaning to using blob's (or at least for join tables). Are there reasons to
go for one or the another, or is it just individual taste and efficiency
when needed?

-- 
Cecil Westerhof

[sqlite] Table alias not accepted with "delete/update" ?

[Domingo Alvarez Duarte]

PostgreSQL do accept table? alias on both delete/update.

[sqlite] Checks with dates

[Cecil Westerhof]

I have a table where I have two fields: toStart and finishBefore. They are
both dates and when filled the format should be %Y-%m-%d. How can this be
checked? 2016-04-31 should not be accepted.

The second part is that when both are filled, then finishBefore should be
after toStart. Is that possible to check?

-- 
Cecil Westerhof

[sqlite] Strange behaviour of select.

[John Found]

On Thu, 14 Apr 2016 13:57:20 -0400
Richard Hipp  wrote:

> On 4/14/16, Richard Hipp  wrote:
> > On 4/14/16, John Found  wrote:
> >> But after some time working in wild (executing the more complex original
> >> query), the performance decreased again and despite of existing the
> >> index,
> >> the result of the "explain query plan" is again:
> >>
> >> SCAN TABLE T
> >> USE TEMP B-TREE FOR ORDER BY
> >
> > Can you make a copy of the database when it gets into this state, and
> > send it to us for analysis?
> 
> [The database was sent in off-list.  This post is a follow-up.]
> 
> It appears that you did the CREATE INDEX after running ANALYZE because
> there is no entry in the sqlite_stat1 table for the index you think
> should be used and the absence of an sqlite_stat1 entry for that index
> while entries are available for other indexes on the same table has
> the query planner confused.
> 
> As it happens, we checked in a change for this very same problem about
> 10 days ago.  See http://www.sqlite.org/cgi/src/info/e375fe52cea7903c
> for the patch.  Please try using the latest trunk version of SQLite
> which should solve your problem.  

Thanks for the help. I am not sure, I can compile the code from
the repository (because of my low C/C++ programming skills), but will try with 
the latest amalgamation from the download page.
Hope it will be enough.

> Or (better) always rerun ANALYZE
> after doing a CREATE INDEX.
> 

Optimizing further on the same database (but the table Posts), I hit the 
reverse problem. The index: 

create index idxPostsThreadUser on posts(threadid, userid);

works only if I do not run "ANALYZE" after dropping and
recreating it. :)

Hope, the update will fix this problem as well.

Once again, thanks for the help.

-- 
http://fresh.flatassembler.net
http://asm32.info
John Found 

[sqlite] Using SQLite for storing photo's

[Cecil Westerhof]

2016-04-14 19:41 GMT+02:00 Simon Slavin :

>
> On 14 Apr 2016, at 6:37pm, Cecil Westerhof  wrote:
>
> > I am thinking about storing my photo's in SQLite.
>
> Doesn't answer your question, but you should definitely read
>
> 
>

?I would like to have everything in one file. Performance is not very high
on my list, because it will be a single user application. But it is
something to think about?.

-- 
Cecil Westerhof

[sqlite] Exclude NULL from index

[Cecil Westerhof]

2016-04-14 19:49 GMT+02:00 Dominique Devienne :

> On Thu, Apr 14, 2016 at 7:48 PM, Cecil Westerhof 
> wrote:
>
> > If I would have a table where a lot of values are NULL. Would it be
> > possible to exclude those records from the index? I would only search
> for a
> > certain value not for NULL. Or is it the default that they are excluded?
>
>
> https://www.sqlite.org/partialindex.html


?OK, I have to exclude it myself. (Is the sane option I think.)

I have to study the page: it has the solution of another problem I was
contemplating. From active records the description should be UNIQUE, but it
is not necessary for inactive records. I saw that this page has the
solution. :-)

-- 
Cecil Westerhof

[sqlite] Journal 4.6 KB, after commit database 6 or 7 KB bigger

[Cecil Westerhof]

2016-04-14 20:00 GMT+02:00 Richard Hipp :

> On 4/14/16, Cecil Westerhof  wrote:
> > I have seen several times that the journal was 4.6 KB, but that fter
> > committing the database had grown with 6 or 7 KB. No big problem, but I
> > find it strange. What could be happening here? I would expect it not
> togrow
> > more as 5 KB.
> >
>
> The rollback journal records the original content of pages that
> already existed in the database at the start of the transaction.  New
> pages added to the end of the database can be rolled back simply by
> truncating the database file, so those pages are not stored in the
> rollback journal.
>
> In WAL mode, the WAL file is a "roll-forward journal" and so the size
> of the WAL file is proportional to the size of the transaction, as you
> expected.
>

?But how is it possible that ?it is smaller? Not really important, but I
just like to know ?everything?. ;-)

-- 
Cecil Westerhof

[sqlite] Using SQLite for storing photo's

[Simon Slavin]

On 14 Apr 2016, at 8:10pm, Cecil Westerhof  wrote:

> ?I would like to have everything in one file.

[sqlite] Checks with dates

[Keith Medcalf]

create table x
(
  ds text not null check(date(ds) is not null),
  de text not null check(date(de) is not null),
  check(ds < de)
);
insert into x values ('2016-04-15', '2016-04-13');
insert into x values ('2016-04-15', '2016-04-17');
insert into x values ('2016-04-15', '2016-04-32');

The constraint on the column values is that date() is not null (ie, a valid 
date)

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Cecil Westerhof
> Sent: Thursday, 14 April, 2016 16:04
> To: SQLite mailing list
> Subject: Re: [sqlite] Checks with dates
> 
> 2016-04-14 21:26 GMT+02:00 Richard Hipp :
> 
> > On 4/14/16, Cecil Westerhof  wrote:
> > > I have a table where I have two fields: toStart and finishBefore. They
> > are
> > > both dates and when filled the format should be %Y-%m-%d. How can this
> be
> > > checked? 2016-04-31 should not be accepted.
> > >
> > > The second part is that when both are filled, then finishBefore should
> be
> > > after toStart. Is that possible to check?
> >
> > Untested:
> >
> > CHECK( toStart=date(toStart)
> > AND finishBefore=date(finishBefore)
> > AND finishBefore>toStart )
> >
> 
> ?Does not work, but I have something to play with. ;-)
> 
> --
> Cecil Westerhof
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Working with booleans

[Keith Medcalf]

Not to mention that 0 is false and 1 is true.  If you use 1 and 0, then you can 
write commands like:

select  from  where isActive;

and have it work as you expect.  Whereas if isActive contains 'T' or 'F' then 
the above will not work and you will have to issue the same select as

select  from  where isActive = 'T';

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Cecil Westerhof
> Sent: Thursday, 14 April, 2016 13:53
> To: SQLite mailing list
> Subject: Re: [sqlite] Working with booleans
> 
> 2016-04-14 19:49 GMT+02:00 Jay Kreibich :
> 
> >
> > On Apr 14, 2016, at 12:42 PM, Cecil Westerhof 
> > wrote:
> >
> > > When working with booleans at the moment I use:
> > >isActive  INTEGER NOT NULL CHECK(isActive in (0, 1))
> > >
> > > Is this a good way, or would be using a CHAR with a check op 'T', or
> 'F'
> > be
> > > better?
> > >
> >
> > Integer 1 and 0 will be the most compact representation in the file
> format.
> >
> 
> ?OK, I will stick with it then.
> 
> --
> Cecil Westerhof
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Working with booleans

[Cecil Westerhof]

2016-04-14 19:49 GMT+02:00 Jay Kreibich :

>
> On Apr 14, 2016, at 12:42 PM, Cecil Westerhof 
> wrote:
>
> > When working with booleans at the moment I use:
> >isActive  INTEGER NOT NULL CHECK(isActive in (0, 1))
> >
> > Is this a good way, or would be using a CHAR with a check op 'T', or 'F'
> be
> > better?
> >
>
> Integer 1 and 0 will be the most compact representation in the file format.
>

?OK, I will stick with it then.

-- 
Cecil Westerhof

[sqlite] Exclude NULL from index

[Dominique Devienne]

On Thu, Apr 14, 2016 at 7:48 PM, Cecil Westerhof 
wrote:

> If I would have a table where a lot of values are NULL. Would it be
> possible to exclude those records from the index? I would only search for a
> certain value not for NULL. Or is it the default that they are excluded?


https://www.sqlite.org/partialindex.html

[sqlite] Exclude NULL from index

[Cecil Westerhof]

If I would have a table where a lot of values are NULL. Would it be
possible to exclude those records from the index? I would only search for a
certain value not for NULL. Or is it the default that they are excluded?

-- 
Cecil Westerhof

[sqlite] Journal 4.6 KB, after commit database 6 or 7 KB bigger

[Cecil Westerhof]

I have seen several times that the journal was 4.6 KB, but that fter
committing the database had grown with 6 or 7 KB. No big problem, but I
find it strange. What could be happening here? I would expect it not togrow
more as 5 KB.

-- 
Cecil Westerhof

[sqlite] Working with booleans

[Cecil Westerhof]

When working with booleans at the moment I use:
isActive  INTEGER NOT NULL CHECK(isActive in (0, 1))

Is this a good way, or would be using a CHAR with a check op 'T', or 'F' be
better?

-- 
Cecil Westerhof

[sqlite] Using SQLite for storing photo's

[Cecil Westerhof]

I am thinking about storing my photo's in SQLite. This has to be done in a
blob of-course, but I was wondering if there are any caveats, or if anyone
has helpful tips.

One thing I was wondering: what is the best way to make a backup? I would
think a normal dump is not very handy. It would probably better to store
the pictures them-self in one file pro picture and only store the
(meta-)data in SQL. What would be a good way?

-- 
Cecil Westerhof

[sqlite] Strange behaviour of select.

[Simon Slavin]

> On 14 Apr 2016, at 7:13pm, John Found  wrote:
> 
> Thanks for the help. I am not sure, I can compile the code from
> the repository (because of my low C/C++ programming skills), but will try 
> with  the latest amalgamation from the download page.

No need to worry too much.  Just stay with your current version and remember to 
run ANALYZE /after/ creating or destroying indexes.

Simon.

[sqlite] Strange behaviour of select.

[John Found]

On Thu, 14 Apr 2016 09:19:06 -0400
Richard Hipp  wrote:

> On 4/14/16, John Found  wrote:
> >
> > But after some time working in wild (executing the more complex original
> > query), the performance decreased again and despite of existing the index,
> > the result of the "explain query plan" is again:
> >
> > SCAN TABLE T
> > USE TEMP B-TREE FOR ORDER BY
> 
> Can you make a copy of the database when it gets into this state, and
> send it to us for analysis?

Yes, of course. It is around 800KB compressed. 
Can I attach it to a personal email to you?

-- 
http://fresh.flatassembler.net
http://asm32.info
John Found 

[sqlite] Using SQLite for storing photo's

[Simon Slavin]

On 14 Apr 2016, at 6:37pm, Cecil Westerhof  wrote:

> I am thinking about storing my photo's in SQLite.

Doesn't answer your question, but you should definitely read



Simon.

[sqlite] sqlite3_close does not propagate the error from sqlite3_io_methods.xClose

[Cezary H. Noweta]

Hello,

On 2016-04-14 16:16, Dobrean, Adrian wrote:
> Is xSync guaranteed to be called only once, same as for xClose, if it is then 
> from flush I/O point of view we should be almost no difference.

No --- it is not guaranteed even once, as I described in previous post.

> However still xClose releases some resources we would need to know when they 
> fail.

You should consider redesigning of your VFS driver architecture in such 
a way that releasing is always successful.

-- best regards

Cezary H. Noweta

[sqlite] sqlite3_close does not propagate the error from sqlite3_io_methods.xClose

[Cezary H. Noweta]

Hello,

On 2016-04-14 15:40, Richard Hipp wrote:
> On 4/14/16, Dobrean, Adrian  wrote:
>>
>> The problem is that when I close there are still I/O operations that
>> sometimes need to happen, e.g. flush of some buffers, and I need to know if
>> those operations finished successfully or not.

> This shouldn't ever come up.  xSync should have been called to flush
> all I/O operations prior to xClose.

``xSync'' is not called when ``PRAGMA synchronous = OFF''. You should 
consider monitoring a state of ``synchronous'' by using 
``SQLITE_FCNTL_PRAGMA''

> The xClose method is just releasing resources.  Kind of like free() in
> the standard library.  Why doesn't standard-library free() return an
> error code?

These being said you should flush all buffers by ``xSync'', or disable 
all buffering and immediately pass all data down after ``PRAGMA 
synchronous = OFF'' (http://sqlite.org/pragma.html#pragma_synchronous).

That is a designed behavior of a VFS driver. If you didn't comply with 
that schema, you would play on your own with SQLite's sources.

-- best regards

Cezary H. Noweta

[sqlite] Primary key values can be NULL

[J Decker]

I would total expect any column I created without NOT NULL (double
negative) to allow NULL whether INDEX or UNIQUE or PRIMARY is applied
additionallywhat database does otherwise?  MSSQL?

On Thu, Apr 14, 2016 at 2:56 PM, R Smith  wrote:
>
>
> On 2016/04/14 10:23 PM, Cecil Westerhof wrote:
>>
>> 2016-04-14 22:10 GMT+02:00 Richard Hipp :
>>
>>> On 4/14/16, Cecil Westerhof  wrote:

 Yes that makes sense. But could not a type of PRAGMA be used? So if the
 PRAGMA is not defined the old functionality and your historical data is
 save. And if the PRAGMA is defined the new functionality.

>>> Easier:  Just declare the columns in question as NOT NULL.  Or use a
>>> WITHOUT ROWID table which *does* enforce NOT NULL.
>>>
>> The problem is that you do not expect that values in the primary key can
>> be NULL, so you do not use NOT NULL. I now know that I need to do this and
>> I do it now, but the unaware will be bitten, just as I was. It was not a
>> big problem, because I just started playing with SQLite, but when you have
>> a very big database which gets a lot of data every day and a year later
>> you
>> find out you are bitten by this ?
>
>
> What makes you "expect" this?  Nothing in the SQLite documentation I hope.
> Is it perhaps habit based on how some other DBs do it?
>
> How will "those who don't know about NOT NULL" be better serviced by a
> pragma which they also don't know about?
> The documentation is very clear on the matter, even a cursory glance would
> educate "the unaware".
>
> Making more things with more documentation to read, in order to help people
> with other documentation they didn't read - not a salient course of action I
> think.
>
> Cheers,
> Ryan
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Primary key values can be NULL

[Richard Hipp]

On 4/14/16, Cecil Westerhof  wrote:
>
> ?Yes that makes sense. But could not a type of PRAGMA be used? So if the
> PRAGMA is not defined the old functionality and your historical data is
> save. And if the PRAGMA is defined the new functionality.
>

Easier:  Just declare the columns in question as NOT NULL.  Or use a
WITHOUT ROWID table which *does* enforce NOT NULL.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Primary key values can be NULL

[Richard Hipp]

On 4/14/16, Cecil Westerhof  wrote:
> For example a database with partial tables can not be read with a
> SQLite before 3.8.0. So why is SQLite not changed so primary keys can not
> have the value NULL?

You fail to distinguish between "forwards compatible" and "backwards
compatible".

Upgrading your program from version X to version Y (with Y>X) should
never cause your historical data to become unreadable.  That's what
"backwards compatible" means.  Upgrading does not break stuff.  SQLite
is backwards compatible.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Checks with dates

[Richard Hipp]

On 4/14/16, Cecil Westerhof  wrote:
> I have a table where I have two fields: toStart and finishBefore. They are
> both dates and when filled the format should be %Y-%m-%d. How can this be
> checked? 2016-04-31 should not be accepted.
>
> The second part is that when both are filled, then finishBefore should be
> after toStart. Is that possible to check?

Untested:

CHECK( toStart=date(toStart)
AND finishBefore=date(finishBefore)
AND finishBefore>toStart )
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Strange behaviour of select.

[John Found]

On Thu, 14 Apr 2016 13:44:45 +0200
R Smith  wrote:

> 
> 
> On 2016/04/14 10:46 AM, John Found wrote:
> > Playing with optimization of my queries, I stuck on very strange (for me) 
> > behaviour of
> > sqlite.
> >
> > The query is pretty complex, but for the experiment I simplified it to:
> >
> > select a, b from t order by a desc, b desc;
> >
> > The result of "explain query plan" is as expected:
> >
> > SCAN TABLE T
> > USE TEMP B-TREE FOR ORDER BY
> >
> > Then I created index:
> >
> > create index i on T(a desc, b desc);
> >
> > After creating index, the explain query plan looks like:
> >
> > SCAN TABLE T USING COVERING INDEX i
> >
> > The query speed raises significantly and I leaved it this way. The original 
> > more complex query also started to use the index and increased its speed 
> > significantly.
> >
> > But after some time working in wild (executing the more complex original 
> > query), the performance decreased again and despite of existing the index, 
> > the result of the "explain query plan" is again:
> >
> > SCAN TABLE T
> > USE TEMP B-TREE FOR ORDER BY
> >
> > After dropping the index and recreating, everything repeats from the 
> > beginning - short period of high performance queries and then not using 
> > index again.
> >
> > The database is not changed, at least for the tables used in the query. 
> > Only select statements are executed, by several connections.
> >
> > What can be the reason for such strange behaviour?
> >
> 
> Are you running periodic ANALYZE perhaps?
> 

Yes, but from time to time, manually. How often should I run it? 
I mean there should be some reason for SQLite to ignore the existing index on 
not changed database.

-- 
http://fresh.flatassembler.net
http://asm32.info
John Found 

[sqlite] sqlite3_close does not propagate the error from sqlite3_io_methods.xClose

[Dobrean, Adrian]

Is xSync guaranteed to be called only once, same as for xClose, if it is then 
from flush I/O point of view we should be almost no difference.

However still xClose releases some resources we would need to know when they 
fail.
Is there a "SQLite" (other than having for example a static variable) way to 
propagate any error through any other means than modifying SQLite code, e.g. 
similar to errno, sqlite3_errcode?

With best regards,

Adrian Dobrean
905-315-4953


-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp
Sent: Thursday, April 14, 2016 9:41 AM
To: SQLite mailing list
Subject: Re: [sqlite] sqlite3_close does not propagate the error from 
sqlite3_io_methods.xClose

On 4/14/16, Dobrean, Adrian  wrote:
>
> The problem is that when I close there are still I/O operations that 
> sometimes need to happen, e.g. flush of some buffers, and I need to 
> know if those operations finished successfully or not.

This shouldn't ever come up.  xSync should have been called to flush all I/O 
operations prior to xClose.  And the return code from xSync is definitely 
propagated back up to the application, as that is important.

The xClose method is just releasing resources.  Kind of like free() in the 
standard library.  Why doesn't standard-library free() return an error code?
--
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] Why pragma case_sensitive_like do not return theactualsetting ?

[Dominique Devienne]

On Thu, Apr 14, 2016 at 1:45 PM, Domingo Alvarez Duarte <
sqlite-mail at dev.dadbiz.es> wrote:

> So there is a way to have a callback that intercept pragmas ?
>
> Because if we make a piece of derived/composed software that can be used by
> third party how to maintain our overloads/overwrites ?
>

https://www.sqlite.org/c3ref/set_authorizer.html with SQLITE_PRAGMA? --DD

[sqlite] Strange behaviour of select.

[Simon Slavin]

On 14 Apr 2016, at 1:01pm, John Found  wrote:

> R Smith  wrote:
> 
>> Are you running periodic ANALYZE perhaps?
> 
> Yes, but from time to time, manually. How often should I run it?

Once.

> I mean there should be some reason for SQLite to ignore the existing index on 
> not changed database.

I also wondered whether ANALYZE had anything to do with your problem but I 
don't understand why it should.

ANALYZE looks at your data and, among other things, works out how 'chunky' it 
is.  For instance, you might have a column for 'surname' in your table and 
every member might have a different surname.  But you might have a another 
column for 'sex' and only have two sexes in it.  This information helps the 
search optimizer figure out whether it should use existing indexes, make up its 
own, etc..

Unless the nature of your data changes there's no reason to run ANALYZE.  So if 
you have already run it, perhaps after you have all your existing data loaded, 
or perhaps when you have a year's worth of data in your database, the 
'chunkiness' of your data isn't going to change, and the relative lengths of 
your tables isn't going to change much, the same search strategies will be 
optimal, and there's no point in running ANALYZE again.  However it should 
never do any harm.  If you're running ANALYZE and it makes things slow down, 
something is wrong with your database.

First, run "PRAGMA integrity_check".  If that doesn't spot anything the next 
time you recreate your index run ANALYZE immediately after that.  See if that 
changes your pattern of fast-slow-fast-slow.

Simon.

[sqlite] Journal 4.6 KB, after commit database 6 or 7 KB bigger

[Richard Hipp]

On 4/14/16, Cecil Westerhof  wrote:
> I have seen several times that the journal was 4.6 KB, but that fter
> committing the database had grown with 6 or 7 KB. No big problem, but I
> find it strange. What could be happening here? I would expect it not togrow
> more as 5 KB.
>

The rollback journal records the original content of pages that
already existed in the database at the start of the transaction.  New
pages added to the end of the database can be rolled back simply by
truncating the database file, so those pages are not stored in the
rollback journal.

In WAL mode, the WAL file is a "roll-forward journal" and so the size
of the WAL file is proportional to the size of the transaction, as you
expected.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Strange behaviour of select.

[Richard Hipp]

On 4/14/16, Richard Hipp  wrote:
> On 4/14/16, John Found  wrote:
>> But after some time working in wild (executing the more complex original
>> query), the performance decreased again and despite of existing the
>> index,
>> the result of the "explain query plan" is again:
>>
>> SCAN TABLE T
>> USE TEMP B-TREE FOR ORDER BY
>
> Can you make a copy of the database when it gets into this state, and
> send it to us for analysis?

[The database was sent in off-list.  This post is a follow-up.]

It appears that you did the CREATE INDEX after running ANALYZE because
there is no entry in the sqlite_stat1 table for the index you think
should be used and the absence of an sqlite_stat1 entry for that index
while entries are available for other indexes on the same table has
the query planner confused.

As it happens, we checked in a change for this very same problem about
10 days ago.  See http://www.sqlite.org/cgi/src/info/e375fe52cea7903c
for the patch.  Please try using the latest trunk version of SQLite
which should solve your problem.  Or (better) always rerun ANALYZE
after doing a CREATE INDEX.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Table alias not accepted with "delete/update" ?

[Domingo Alvarez Duarte]

I knew your alternative solution, but depending on the table content it can
be terrible for performance.  

Cheers !  
>  Thu Apr 14 2016 01:17:31 PM CEST from "Keith Medcalf"
>  Subject: Re: [sqlite] Table alias not accepted with
>"delete/update" ?
>
>  UPDATE __table_metadata
> SET list_table_id = NULL
> WHERE list_table_id NOT IN (select distinct id
> from __tables_metadata);
> 
> You are correct though, aliases of the table being updated are normally
>only assigned in the FROM clause, not in the UPDATE clause, though many
>vendors have implemented proprietary extenstions which permit the assignment
>of table alias names in the UPDATE clause.
> 
>  
>>-Original Message-
>> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
>> bounces at mailinglists.sqlite.org] On Behalf Of Domingo Alvarez Duarte
>> Sent: Thursday, 14 April, 2016 06:43
>> To: SQLite mailing list
>> Subject: [sqlite] Table alias not accepted with "delete/update" ?
>> 
>> Hello !
>> 
>> Today I'm trying to execute this statememt and it works:
>> 
>> SELECT * FROM __tables_metadata a WHERE NOT EXISTS(SELECT 1 FROM
>> sqlite_master where tbl_name=a.name);
>> 
>> Then I tried this one and it fails:
>> 
>> DELETE FROM __tables_metadata a WHERE NOT EXISTS(SELECT 1 FROM
>> sqlite_master
>> where tbl_name=a.name);
>> 
>> Then I changed it to and it works:
>> 
>> DELETE FROM __tables_metadata WHERE NOT EXISTS(SELECT 1 FROM sqlite_master
>> where tbl_name=__tables_metadata.name);
>> 
>> Then I needed this and it doesn't work (here I'm referring the same table
>> twice):
>> 
>> UPDATE __tables_metadata a set list_table_id=NULL where NOT EXISTS(SELECT
>> 1
>> FROM __tables_metadata WHERE id=a.list_table_id);
>> 
>> 
>> 
>> It seems to be a bug/missing implementation ?
>> 
>> Cheers !
>> 
>> ___
>> 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] Why pragma case_sensitive_like do not return theactualsetting ?

[Domingo Alvarez Duarte]

So there is a way to have a callback that intercept pragmas ?  

Because if we make a piece of derived/composed software that can be used by
third party how to maintain our overloads/overwrites ?  

Cheers !  
>  Thu Apr 14 2016 01:21:08 PM CEST from "Richard Hipp"  
>Subject: Re: [sqlite] Why pragma case_sensitive_like do not return
>theactualsetting ?
>
>  On 4/14/16, Domingo Alvarez Duarte  wrote:
>  
>>Also I'm noticing that when the "pragma case_sensitive_like" is executed
>> there is two internal functions
>> "sqlite3RegisterLikeFunctions/setLikeOptFlag"
>> been called to swap the built-in LIKE and GLOB functions.
>> 
>> What this mean for user installed collates/functions overloading ?
>> 

>  From the docs: "This pragma uses sqlite3_create_function() to
> overload the LIKE and GLOB functions, which may override previous
> implementations of LIKE and GLOB registered by the application."
> 
> So if you register your own LIKE and GLOB functions and then invoke the
> "case_sensitive_like" pragma, your private LIKE and GLOB functions are
> replaced by one of the built-in LIKE or GLOB implementations.
> 
> 
> -- 
> 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] Strange behaviour of select.

[R Smith]

On 2016/04/14 10:46 AM, John Found wrote:
> Playing with optimization of my queries, I stuck on very strange (for me) 
> behaviour of
> sqlite.
>
> The query is pretty complex, but for the experiment I simplified it to:
>
> select a, b from t order by a desc, b desc;
>
> The result of "explain query plan" is as expected:
>
> SCAN TABLE T
> USE TEMP B-TREE FOR ORDER BY
>
> Then I created index:
>
> create index i on T(a desc, b desc);
>
> After creating index, the explain query plan looks like:
>
> SCAN TABLE T USING COVERING INDEX i
>
> The query speed raises significantly and I leaved it this way. The original 
> more complex query also started to use the index and increased its speed 
> significantly.
>
> But after some time working in wild (executing the more complex original 
> query), the performance decreased again and despite of existing the index, 
> the result of the "explain query plan" is again:
>
> SCAN TABLE T
> USE TEMP B-TREE FOR ORDER BY
>
> After dropping the index and recreating, everything repeats from the 
> beginning - short period of high performance queries and then not using index 
> again.
>
> The database is not changed, at least for the tables used in the query. Only 
> select statements are executed, by several connections.
>
> What can be the reason for such strange behaviour?
>

Are you running periodic ANALYZE perhaps?

[sqlite] Why pragma case_sensitive_like do not return the actual setting ?

[Simon Slavin]

On 14 Apr 2016, at 9:31am, Domingo Alvarez Duarte  wrote:

> using
> "pragma case_sensitive_like;" without a boolean parameter

If you look at the formatting of the pragma page



Almost all pragmas list

PRAGMA thing
PRAGMA thing = boolean

however the 'case_sensitive_like' pragma lists only the second form.  I don't 
know whether this genuinely means tnat the first form isn't implemented or is 
an error in documentation.

Simon.

[sqlite] sqlite3_close does not propagate the error from sqlite3_io_methods.xClose

[Dobrean, Adrian]

Thank you.

The problem is that when I close there are still I/O operations that sometimes 
need to happen, e.g. flush of some buffers, and I need to know if those 
operations finished successfully or not.
For example, the close operation fails to flush some of the buffers reports an 
error that is discarded and the application wouldn't know there is something 
wrong with the DB.

I am not so sure why the error is discarded, even if I make sure the DB is 
consistent before the close (e.g. flush it to disk), there could be cases where 
the application may need to know if something wrong during xClose.

With best regards,

Adrian Dobrean
905-315-4953

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp
Sent: Thursday, April 14, 2016 9:06 AM
To: SQLite mailing list
Subject: Re: [sqlite] sqlite3_close does not propagate the error from 
sqlite3_io_methods.xClose

On 4/14/16, Dobrean, Adrian  wrote:
>
> Is there a way or what should be the proper way to capture the error 
> returned by sqlite3_io_methods.xClose?
>

No.  It looks like the return value from sqlite3_io_methods.xClose is always 
ignored and discarded.

Why is that a problem?

--
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] sqlite3_close does not propagate the error from sqlite3_io_methods.xClose

[Dobrean, Adrian]

Hi,

It looks like the error returned by sqlite3_io_methods.xClose is not propagated 
all the way to sqlite3_close caller.
According to a comment in sqlite3_close this function (excepting SQLITE_BUSY) 
always returns true.

Is there a way or what should be the proper way to capture the error returned 
by sqlite3_io_methods.xClose?

With best regards,

Adrian Dobrean

[sqlite] Working with booleans

[Jay Kreibich]

On Apr 14, 2016, at 12:42 PM, Cecil Westerhof  wrote:

> When working with booleans at the moment I use:
>isActive  INTEGER NOT NULL CHECK(isActive in (0, 1))
> 
> Is this a good way, or would be using a CHAR with a check op 'T', or 'F' be
> better?
> 

Integer 1 and 0 will be the most compact representation in the file format.

 -j


--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

[sqlite] Table alias not accepted with "delete/update" ?

[Domingo Alvarez Duarte]

Hello !  

Today I'm trying to execute this statememt and it works:  

SELECT * FROM __tables_metadata a WHERE NOT EXISTS(SELECT 1 FROM
sqlite_master where tbl_name=a.name);  

Then I tried this one and it fails:  

DELETE FROM __tables_metadata a WHERE NOT EXISTS(SELECT 1 FROM sqlite_master
where tbl_name=a.name);  

Then I changed it to and it works:  

DELETE FROM __tables_metadata WHERE NOT EXISTS(SELECT 1 FROM sqlite_master
where tbl_name=__tables_metadata.name);  

Then I needed this and it doesn't work (here I'm referring the same table
twice):  

UPDATE __tables_metadata a set list_table_id=NULL where NOT EXISTS(SELECT 1
FROM __tables_metadata WHERE id=a.list_table_id);  

?  

It seems to be a bug/missing implementation ?  

Cheers !

[sqlite] Strange behaviour of select.

[Richard Hipp]

On 4/14/16, John Found  wrote:
>>
>> Can you make a copy of the database when it gets into this state, and
>> send it to us for analysis?
>
> Yes, of course. It is around 800KB compressed.
> Can I attach it to a personal email to you?
>

Yes

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Strange behaviour of select.

[John Found]

Playing with optimization of my queries, I stuck on very strange (for me) 
behaviour of 
sqlite. 

The query is pretty complex, but for the experiment I simplified it to:

select a, b from t order by a desc, b desc;

The result of "explain query plan" is as expected:

SCAN TABLE T
USE TEMP B-TREE FOR ORDER BY

Then I created index:

create index i on T(a desc, b desc);

After creating index, the explain query plan looks like:

SCAN TABLE T USING COVERING INDEX i

The query speed raises significantly and I leaved it this way. The original 
more complex query also started to use the index and increased its speed 
significantly.

But after some time working in wild (executing the more complex original 
query), the performance decreased again and despite of existing the index, the 
result of the "explain query plan" is again:

SCAN TABLE T
USE TEMP B-TREE FOR ORDER BY

After dropping the index and recreating, everything repeats from the beginning 
- short period of high performance queries and then not using index again.

The database is not changed, at least for the tables used in the query. Only 
select statements are executed, by several connections.

What can be the reason for such strange behaviour?

-- 
http://fresh.flatassembler.net
http://asm32.info
John Found 

[sqlite] Why pragma case_sensitive_like do not return theactualsetting ?

[Domingo Alvarez Duarte]

It seems that we have a mess here.  

How would we overload/replace the "like" function with things like this done
internally by sqlite without public documentaion ?  

Also I do not see a setting of SQLITE_DETERMINISTIC isn't like deterministic
?  

Cheers !  

/*
** Set the LIKEOPT flag on the 2-argument function with the given name.
*/
static void setLikeOptFlag(sqlite3 *db, const char *zName, u8 flagVal){
? FuncDef *pDef;
? pDef = sqlite3FindFunction(db, zName, 2, SQLITE_UTF8, 0);
? if( ALWAYS(pDef) ){
??? pDef->funcFlags |= flagVal;
? }
}

/*
** Register the built-in LIKE and GLOB functions.? The caseSensitive
** parameter determines whether or not the LIKE operator is case
** sensitive.? GLOB is always case sensitive.
*/
void sqlite3RegisterLikeFunctions(sqlite3 *db, int caseSensitive){
? struct compareInfo *pInfo;
? if( caseSensitive ){
??? pInfo = (struct compareInfo*)&likeInfoAlt;
? }else{
??? pInfo = (struct compareInfo*)&likeInfoNorm;
? }
? sqlite3CreateFunc(db, "like", 2, SQLITE_UTF8, pInfo, likeFunc, 0, 0, 0);
? sqlite3CreateFunc(db, "like", 3, SQLITE_UTF8, pInfo, likeFunc, 0, 0, 0);
? sqlite3CreateFunc(db, "glob", 2, SQLITE_UTF8, 
? (struct compareInfo*)&globInfo, likeFunc, 0, 0, 0);
? setLikeOptFlag(db, "glob", SQLITE_FUNC_LIKE | SQLITE_FUNC_CASE);
? setLikeOptFlag(db, "like", 
? caseSensitive ? (SQLITE_FUNC_LIKE | SQLITE_FUNC_CASE) :
SQLITE_FUNC_LIKE);
}

/*
** pExpr points to an expression which implements a function.? If
** it is appropriate to apply the LIKE optimization to that function
** then set aWc[0] through aWc[2] to the wildcard characters and
** return TRUE.? If the function is not a LIKE-style function then
** return FALSE.
**
** *pIsNocase is set to true if uppercase and lowercase are equivalent for
** the function (default for LIKE).? If the function makes the distinction
** between uppercase and lowercase (as does GLOB) then *pIsNocase is set to
** false.
*/
int sqlite3IsLikeFunction(sqlite3 *db, Expr *pExpr, int *pIsNocase, char
*aWc){
? FuncDef *pDef;
? if( pExpr->op!=TK_FUNCTION 
?? || !pExpr->x.pList 
?? || pExpr->x.pList->nExpr!=2
? ){
??? return 0;
? }
? assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
? pDef = sqlite3FindFunction(db, pExpr->u.zToken, 2, SQLITE_UTF8, 0);
? if( NEVER(pDef==0) || (pDef->funcFlags & SQLITE_FUNC_LIKE)==0 ){
??? return 0;
? }

? /* The memcpy() statement assumes that the wildcard characters are
? ** the first three statements in the compareInfo structure.? The
? ** asserts() that follow verify that assumption
? */
? memcpy(aWc, pDef->pUserData, 3);
? assert( (char*)&likeInfoAlt == (char*)&likeInfoAlt.matchAll );
? assert( &((char*)&likeInfoAlt)[1] == (char*)&likeInfoAlt.matchOne );
? assert( &((char*)&likeInfoAlt)[2] == (char*)&likeInfoAlt.matchSet );
? *pIsNocase = (pDef->funcFlags & SQLITE_FUNC_CASE)==0;
? return 1;
}  

?

[sqlite] Why pragma case_sensitive_like do not return theactualsetting ?

[Domingo Alvarez Duarte]

See bellow the setLikeOptFlag internal function, if we have installed custom
"like" functions what would be the return value of "sqlite3FindFunction" be ?


It seems that it needs to add the collation also to be able to find the
correct function to set the flags.  

/*
** Set the LIKEOPT flag on the 2-argument function with the given name.
*/
static void setLikeOptFlag(sqlite3 *db, const char *zName, u8 flagVal){
? FuncDef *pDef;
? pDef = sqlite3FindFunction(db, zName, 2, SQLITE_UTF8, 0);
? if( ALWAYS(pDef) ){
??? pDef->funcFlags |= flagVal;
? }
}  


Cheers !

[sqlite] Why pragma case_sensitive_like do not return the actualsetting ?

[Domingo Alvarez Duarte]

Also I'm noticing that when the "pragma case_sensitive_like" is executed
there is two internal functions "sqlite3RegisterLikeFunctions/setLikeOptFlag"
been called to swap the built-in LIKE and GLOB functions.  

What this mean for user installed collates/functions overloading ?  

I have some custom collates and also I overload the "like" function for those
collates, but I didn't saw any mention on the docs about "setLikeOptFlag"
that seems to add info to help the query planner, shouldn't this also be
documented and made public ? This way third party collate/functions could
also benefit from it.  

Cheers !

[sqlite] Why pragma case_sensitive_like do not return the actual setting ?

[Domingo Alvarez Duarte]

Hello !  

I'm trying to find why in my application I'm getting a case sensitive like
behavior by default and when trying to see what is the actual setting using
"pragma case_sensitive_like;" without a boolean parameter I was expecting to
get back the actual active setting but nothing comes back.  

?  

Why this happen, other pragmas return their current setting.  

?  

Cheers !  

?

[sqlite] Why pragma case_sensitive_like do not return theactualsetting ?

[Scott Robison]

On Apr 14, 2016 5:46 AM, "Domingo Alvarez Duarte" 
wrote:
>
> So there is a way to have a callback that intercept pragmas ?
>
> Because if we make a piece of derived/composed software that can be used
by
> third party how to maintain our overloads/overwrites ?

Since SQLite swaps like/glob functions, it seems the simple fix is to not
use that pragma for custom like/glob functions. Communicate the case
sensitivity status through some other channel, or have two versions of the
functions that vary based on case sensitivity and replace based on current
needs instead of calling the pragma.

>
> Cheers !
> >  Thu Apr 14 2016 01:21:08 PM CEST from "Richard Hipp" 
> >Subject: Re: [sqlite] Why pragma case_sensitive_like do not return
> >theactualsetting ?
> >
> >  On 4/14/16, Domingo Alvarez Duarte  wrote:
> >
> >>Also I'm noticing that when the "pragma case_sensitive_like" is executed
> >> there is two internal functions
> >> "sqlite3RegisterLikeFunctions/setLikeOptFlag"
> >> been called to swap the built-in LIKE and GLOB functions.
> >>
> >> What this mean for user installed collates/functions overloading ?
> >>
>
> >  From the docs: "This pragma uses sqlite3_create_function() to
> > overload the LIKE and GLOB functions, which may override previous
> > implementations of LIKE and GLOB registered by the application."
> >
> > So if you register your own LIKE and GLOB functions and then invoke the
> > "case_sensitive_like" pragma, your private LIKE and GLOB functions are
> > replaced by one of the built-in LIKE or GLOB implementations.
> >
> >
> > --
> > 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-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_close does not propagate the error from sqlite3_io_methods.xClose

[Richard Hipp]

On 4/14/16, Dobrean, Adrian  wrote:
>
> The problem is that when I close there are still I/O operations that
> sometimes need to happen, e.g. flush of some buffers, and I need to know if
> those operations finished successfully or not.

This shouldn't ever come up.  xSync should have been called to flush
all I/O operations prior to xClose.  And the return code from xSync is
definitely propagated back up to the application, as that is
important.

The xClose method is just releasing resources.  Kind of like free() in
the standard library.  Why doesn't standard-library free() return an
error code?
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] sqlite3_close does not propagate the error from sqlite3_io_methods.xClose

[Richard Hipp]

On 4/14/16, Dobrean, Adrian  wrote:
>
> I am not so sure why the error is discarded,

Because (1) failure is impossible on most implementations of xClose
and nearly so for the rest, and (2) propagating the error back up to
the application would require a lot of code that needs to be written,
tested, and maintained.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Strange behaviour of select.

[Richard Hipp]

On 4/14/16, John Found  wrote:
>
> But after some time working in wild (executing the more complex original
> query), the performance decreased again and despite of existing the index,
> the result of the "explain query plan" is again:
>
> SCAN TABLE T
> USE TEMP B-TREE FOR ORDER BY

Can you make a copy of the database when it gets into this state, and
send it to us for analysis?
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] sqlite3_close does not propagate the error from sqlite3_io_methods.xClose

[Richard Hipp]

On 4/14/16, Dobrean, Adrian  wrote:
>
> Is there a way or what should be the proper way to capture the error
> returned by sqlite3_io_methods.xClose?
>

No.  It looks like the return value from sqlite3_io_methods.xClose is
always ignored and discarded.

Why is that a problem?

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Fwd: odd schema resulting from creating a table out of a join with a subselect

[Clemens Ladisch]

Hinrichsen, John wrote:
> Could someone point me to documentation on how the schema generation
> works when using a subselect in a join?

:
| The name of a result column is the value of the "AS" clause for that
| column, if there is an AS clause. If there is no AS clause then the
| name of the column is unspecified and may change from one release of
| SQLite to the next.


Regards,
Clemens

[sqlite] Why pragma case_sensitive_like do not return the actualsetting ?

[Richard Hipp]

On 4/14/16, Domingo Alvarez Duarte  wrote:
> Also I'm noticing that when the "pragma case_sensitive_like" is executed
> there is two internal functions
> "sqlite3RegisterLikeFunctions/setLikeOptFlag"
> been called to swap the built-in LIKE and GLOB functions.
>
> What this mean for user installed collates/functions overloading ?

>From the docs:  "This pragma uses sqlite3_create_function() to
overload the LIKE and GLOB functions, which may override previous
implementations of LIKE and GLOB registered by the application."

So if you register your own LIKE and GLOB functions and then invoke the
"case_sensitive_like" pragma, your private LIKE and GLOB functions are
replaced by one of the built-in LIKE or GLOB implementations.


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Table alias not accepted with "delete/update" ?

[Keith Medcalf]

UPDATE __table_metadata
  SET list_table_id = NULL
WHERE list_table_id NOT IN (select distinct id
  from __tables_metadata);

You are correct though, aliases of the table being updated are normally only 
assigned in the FROM clause, not in the UPDATE clause, though many vendors have 
implemented proprietary extenstions which permit the assignment of table alias 
names in the UPDATE clause.

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Domingo Alvarez Duarte
> Sent: Thursday, 14 April, 2016 06:43
> To: SQLite mailing list
> Subject: [sqlite] Table alias not accepted with "delete/update" ?
> 
> Hello !
> 
> Today I'm trying to execute this statememt and it works:
> 
> SELECT * FROM __tables_metadata a WHERE NOT EXISTS(SELECT 1 FROM
> sqlite_master where tbl_name=a.name);
> 
> Then I tried this one and it fails:
> 
> DELETE FROM __tables_metadata a WHERE NOT EXISTS(SELECT 1 FROM
> sqlite_master
> where tbl_name=a.name);
> 
> Then I changed it to and it works:
> 
> DELETE FROM __tables_metadata WHERE NOT EXISTS(SELECT 1 FROM sqlite_master
> where tbl_name=__tables_metadata.name);
> 
> Then I needed this and it doesn't work (here I'm referring the same table
> twice):
> 
> UPDATE __tables_metadata a set list_table_id=NULL where NOT EXISTS(SELECT
> 1
> FROM __tables_metadata WHERE id=a.list_table_id);
> 
> 
> 
> It seems to be a bug/missing implementation ?
> 
> Cheers !
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users