[sqlite] FTS5 query that to match all rows.

On 04/13/2016 11:24 PM, John Found wrote: > What FTS5 query should I use in order to match all rows in the table? > Can you use "SELECT * FROM fts_table;"? Dan.

[sqlite] Get fields type

On 13 Apr 2016, at 2:40pm, hfiandor wrote: > I?m trying to do an application for coping from Excel table to SQLite table. > I have already identified both tables has the same field?s names and the > order. But not yet I have obtained the field?s types in the SQLite table. You can use

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

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

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

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.

[sqlite] Strange behaviour of select.

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

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

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

[sqlite] Why pragma case_sensitive_like do not return theactualsetting ?

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

[sqlite] Why pragma case_sensitive_like do not return theactualsetting ?

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

[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

[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

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

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

[sqlite] Strange behaviour of select.

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

[sqlite] Why pragma case_sensitive_like do not return theactualsetting ?

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

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

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

[sqlite] Strange behaviour of select.

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

[sqlite] Why pragma case_sensitive_like do not return theactualsetting ?

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

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

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 =

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

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

[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

[sqlite] Strange behaviour of select.

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.

[sqlite] Strange behaviour of select.

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

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

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

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

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,

[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

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

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)

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

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

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

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

[sqlite] Strange behaviour of select.

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

[sqlite] Why pragma case_sensitive_like do not return theactualsetting ?

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

[sqlite] Strange behaviour of select.

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] Using SQLite for storing photo's

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

[sqlite] Using SQLite for storing photo's

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] Working with booleans

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] Journal 4.6 KB, after commit database 6 or 7 KB bigger

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] Exclude NULL from index

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] Working with booleans

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

[sqlite] Exclude NULL from index

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?

[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'

[sqlite] Strange behaviour of select.

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

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

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

[sqlite] Strange behaviour of select.

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

[sqlite] Strange behaviour of select.

> 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

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

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

[sqlite] Exclude NULL from index

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.

[sqlite] Using SQLite for storing photo's

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

[sqlite] Checks with dates

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

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

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

[sqlite] Checks with dates

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

[sqlite] Working with UUID

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

[sqlite] Primary key values can be NULL

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

[sqlite] Using SQLite for storing photo's

On 14 Apr 2016, at 8:10pm, Cecil Westerhof wrote: > ?I would like to have everything in one file.

[sqlite] Primary key values can be NULL

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

[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. > > > >

[sqlite] Primary key values can be NULL

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

[sqlite] Using SQLite for storing photo's

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

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

[sqlite] Using SQLite for storing photo's

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

[sqlite] Using SQLite for storing photo's

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

[sqlite] Primary key values can be NULL

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

[sqlite] Primary key values can be NULL

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

[sqlite] Working with booleans

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

[sqlite] Checks with dates

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

[sqlite] Using SQLite for storing photo's

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