Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[Simon Slavin]

On 13 Mar 2020, at 6:50pm, Thomas Kurz  wrote:

> Why can't the forum just forward all new postings to this mailing list and 
> vice versa? Then everyone could chose what to use ;)

I think one of the objectives of moving away from email is to prevent email 
spam.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[Simon Slavin]

Well, that'll annoy the nabble people.  And I can live with that.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Maintaing a Last updated timestamp column using triggers

[Simon Slavin]

On 12 Mar 2020, at 3:36pm, David Blake  wrote:

> What stops the
> UPDATE ports SET timeofday = CURRENT_TIMESTAMP WHERE id = NEW.id ;
> from also triggering the AFTER UPDATE ON recursively?
> 
> Perhaps a pragma or inSQLite are  triggers non-recursive by default?

Bingo.


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

Re: [sqlite] Is this an SQL parsing / ambiguity bug ?

[Simon Slavin]

On 9 Mar 2020, at 8:40pm, Vladimir Vysotsky  wrote:

> sqlite> insert into dst(id) select id from src on conflict do nothing;
> Error: near "do": syntax error

SQLite does not understand "DO NOTHING".  You probably want "ON CONFLICT 
IGNORE".


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

Re: [sqlite] No feedback for executing a mathematical expression

[Simon Slavin]

On 10 Mar 2020, at 12:40pm, Jose Isaias Cabrera  wrote:

> Simon Slavin, on Tuesday, March 10, 2020 03:25 AM, wrote...
> 
>> That's going in my list of annoying questions.  Thank you.
> 
> Simon, with all due respect, and grateful for all the answers you have 
> provided to me, this is not an annoying question.  Not everyone in the world 
> knows what you know.

Jose, I must apologise for not explaining myself better.

It's a delightful annoying question.  Anyone who asks that question should be 
annoyed, at computers in general and the one they're swearing at in particular. 
 I love those questions and I giggle over them when people tell me that 
programming must be easy because computers are simple and logical.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] No feedback for executing a mathematical expression

[Simon Slavin]

On 10 Mar 2020, at 7:21am, Octopus ZHANG  wrote:

> sqlite> select 99-(55/(30/57));
> 
> Should I expect it to return nothing?

That's going in my list of annoying questions.  Thank you.

sqlite> select 99.0-(55.0/(30.0/57.0));
-5.5

Now you can work out what the problem is.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

[Simon Slavin]

On 9 Mar 2020, at 2:16pm, David Raymond  wrote:

> In general I believe the last connection tries to do a complete checkpoint 
> when it closes, and if it succeeds then it'll delete the -wal and -shm files. 
> If you have automatic checkpointing turned off (maybe you're doing regular 
> checkpoints from a separate dedicated process) then it's possible for all 
> connections to finish and close the database without error, but still have 
> those files sitting around because they haven't been checkpointed yet.

If your .shm and .wal files still exist when no apps are accessing the 
database, the most likely cause is that at least one of the apps is not closing 
its connection correctly.

If your app runs code to close connections, and the connections are still not 
closing properly, then the most likely cause is that you have a statement 
active when you close the connection.

To make sure your statements are not active, make sure you call 
sqlite3_finalize() on each statement when you no longer need it.  If you are 
not using the C library to execute SQLite calls, look for some equivalent in 
your SQLite library.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

[Simon Slavin]

[This explanation is much simplified for clarity.  Before filling in missing 
details please consider what the OP wants.  Don't just show off your exhaustive 
knowledge of SQLite.]

A database is normally in delete journal mode, as if you'd executed

PRAGMA journal_mode=DELETE

In this journal mode you can't read from a database which is being changed.  If 
one process is changing the database it has the database locked.  Another 
process cannot read it until it is unlocked.  Because the reading process might 
read some of the row before it is changed, and the rest of the row after it is 
changed.

To fix this, change the journal mode to WAL:

PRAGMA journal_mode=WAL

In this mode one process can read a database while another process is changing 
it.  The process that reads the database gets the data as it was before the 
change.

For further details see


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

Re: [sqlite] Trigger name missing

[Simon Slavin]

On 7 Mar 2020, at 4:49pm, John G  wrote:

> Would it be possible to create an SQL verification program, which just like 
> 'sqlite3_analyzer' and 'sqldiff' could be run separately?
> It could *warn* about apparently incompletely defined triggers and other 
> possible pitfalls.

The shell tool has this ability, by executing the following PRAGMAs:





Used in the above order it will spot several kinds of corruption.  It can be 
scripted from your OS command-line shell:


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

Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?

[Simon Slavin]

On 7 Mar 2020, at 1:52am, Xinyue Chen  wrote:

> If I change IS NOT FALSE to IS TRUE, the results will be different. I
> assume they should perform in the same way?

No.  Because NULL is not TRUE and is not FALSE.

SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT NULL IS TRUE;
0
sqlite> SELECT NULL IS FALSE;
0
sqlite> SELECT NULL IS NOT TRUE;
1
sqlite> SELECT NULL IS NOT FALSE;
1

Once you can have NULL values, you have to know the rules very well when you 
apply logic.  Other values make sense, but NULL is not logical.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting all changes within a begin; end; transaction

[Simon Slavin]

On 4 Mar 2020, at 3:28pm, Jose Isaias Cabrera  wrote:

> The reason why I know is that if I have 238 INSERTS, but I have a constraint 
> , there should be 238 INSERTs the first time I run a set of SQL, but if I run 
> the same SQL again, there should not be any records INSERTED, and thus, the 
> amount should be 0, correct?

Can you try the same thing but instead of INSERT use INSERT OR IGNORE ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting all changes within a begin; end; transaction

[Simon Slavin]

On 4 Mar 2020, at 3:28pm, Jose Isaias Cabrera  wrote:

> However, I have found that it does not actually provide the **ACTUAL** 
> changes, but a count of the possible changes.

Hmm.  I understand you.  Does this do something more like what you need ?


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

Re: [sqlite] Getting all changes within a begin; end; transaction

[Simon Slavin]

On 4 Mar 2020, at 2:37pm, Jose Isaias Cabrera  wrote:

> Is there a way to know all the changes that may have happened within the full 
> BEGIN and END?  Thanks.

Use this function



before and after your block, and subtract one from another.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Shell Feature Request: spaces before dot commands

[Simon Slavin]

On 4 Mar 2020, at 12:19pm, no...@null.net wrote:

>I like to sometimes indent a block of SQL and change
> settings or run dot commands within a transaction, e.g.:
> 
>BEGIN
>.mode csv
>.import ...
>COMMIT;

Dot commands are not SQL commands, they are instructions to the shell 
application.  Transactions, CASE structures, WITH structures, and other such 
things do not affect them.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Select statement with ORDER BY specified by column value

[Simon Slavin]

On 29 Feb 2020, at 8:37am, Marco Bambini  wrote:

> ORDER BY (prop_tag='ios') LIMIT 1;
> 
> I would like to prioritise results based on the fact that the prop_tag column 
> is 'ios'.

SQLite has a conditional construction:

CASE prop_tag WHEN 'ios' THEN 0 ELSE 1 END

So do

SELECT …
ORDER BY CASE prop_tag WHEN 'ios' THEN 0 ELSE 1 END
LIMIT 1;
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Intersecting multiple queries

[Simon Slavin]

On 29 Feb 2020, at 12:45am, Keith Medcalf  wrote:

> select * from data where (...) AND (...) AND (...) ;

SELECT uuid FROM Data
   WHERE filter LIKE ?
  OR filter LIKE ?
  OR filter LIKE ?
  OR filter LIKE ? …

would probably be an efficient way to do it if you could construct your entire 
statement.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Intersecting multiple queries

[Simon Slavin]

On 28 Feb 2020, at 11:02pm, Hamish Allan  wrote:

> What I'm wondering is if there's a shortcut to avoid having to build the UUID 
> list in app code

I would probably start by building a list of the search patterns then see what 
I could do with it:

(pattern1,pattern2,pattern3)

Would a Common Table Expression do it ?



WITH pattern in (pattern1,pattern2,pattern3)
 … UNION ALL …
   SELECT uuid FROM Data WHERE filter LIKE pattern

Unfortunately I've not used them and don't know the right way to do it.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to prevent sqlite_reset reporting an already known error

[Simon Slavin]

On 26 Feb 2020, at 11:58pm, mailing lists  wrote:

> The issue is that sqlite_reset() reports the same error that already 
> sqlite3_step() reported. How can I prevent that sqlite_reset() reports the 
> same error.

You can't.  It's working as designed.

One would normally test the result that sqlite_reset() returns, making sure it 
is either SQLITE_OK or the error you got from sqlite3_step(). However I see 
you're using a wrapper that makes it difficult.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] inserting new data only

[Simon Slavin]

On 26 Feb 2020, at 5:18pm, Przemek Klosowski 
 wrote:

> This 'store only changed values' is
> intended for situations like that.
> Another suitable candidate for that treatment might be a status, for
> instance 'on battery' value for a UPS monitoring system. I can't think of a 
> scenario where storing it every time would be better.

Suppose some horrible disaster happens to your chemical plant at 4pm.  Your log 
for a particular sensor shows an acceptable value, but the timestamp on that 
reading is 3:15pm.  But only changed readings are logged.

Perhaps a fault occurred in the sampling circuit at 3:16pm, so no further 
readings were taken, so an alarm was not raised.  Had every reading been logged 
you could have proved that another reading was taken at 3:58pm still showing 
the same acceptable value.

So it depends why you're doing what you're doing.  Different circumstances 
suggest different tactics.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger name missing

[Simon Slavin]

On 26 Feb 2020, at 2:15pm, Dan Kennedy  wrote:

> A statement like the following creates a "BEFORE" trigger named "AFTER". Does 
> that explain things?
> 
>   CREATE TRIGGER AFTER INSERT ON t1 BEGIN
> ...
>   END;
> 
> I find I fall into this trap about once every 18 months...

If only you knew someone with the power to make this generate SQLITE_MISUSE so 
that you, and other programmers, don't have this problem.  Oh well.

(Backward compatibility ?  Do you think anyone who used the word AFTER really 
wants a BEFORE trigger ?  More likely to be a bug they should know about.)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] inserting new data only

[Simon Slavin]

This strikes me as best solved in the programming language.

If a single set of data points is being acquired in real time, and you have a 
programming language (or script) generating the INSERT commands, why not simply 
keep the most recently inserted temperature in a variable ?

On the other hand, if you have multiple sensors, or out-of-order insertion, or 
a stateless insertion program, you could insert every reading and before 
reporting use a 'cleanup' procedure to remove redundant readings.

Both the above would be faster than having SQL execute a search every time a 
new reading is added.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance Issue on Large Table

[Simon Slavin]

On 23 Feb 2020, at 7:23pm, Richard Damon  wrote:

> I'm not sure that form of division would be good. One basic rule of database 
> normalization is that you don't break-up data based on the value of one of 
> the fields because you can't make the table to lookup data from be taken from 
> a field you get in a query.

People who study the bible often want to look up different versions of the same 
verse at once, so they can compare the translations and wording.  This suggests 
that splitting up the text by different versions would be a bad idea.

I would rather figure out the underlying problem, but I made a decision some 
time ago not to understand the FTS parts of SQLite (because I never needed it 
for my own work).  So I'm sorry I can't help on the performance issues.  The 
timings just seem too long for a SQLite database which isn't using FTS.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Setting auto_vacuum=2 doesn't work after setting journal_mode=WAL

[Simon Slavin]

On 22 Feb 2020, at 2:28am, Keith Medcalf  wrote:

> When a database is to be created these commands must be given BEFORE any 
> command which opens or creates the database:
> 
> pragma auto_vacuum
> pragma encoding
> pragma page_size
> pragma data_store_directory
> 
> The issuance (or preparation) of ANY OTHER COMMAND will cause a new blank 
> database to be created using the values of auto_vacuum, encoding, and 
> page_size in effect at the time that command is issued (prepared).

The problem is, Jens points out, that this is not documented.  The 
documentation doesn't distinguish between those four PRAGMAs and other PRAGMAs 
which stop those four from working.

I've previously suggested that the PRAGMAs should be divided into categories.  
Perhaps this should be a new category: those which do not create a database but 
have to be done before anything that creates the database.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] DRH interview on why/how SQLite succeeded

[Simon Slavin]

Podcast / transcription of DRH interview:

" We talked to Richard about the history of SQLite, where it came from, why it 
succeeded as a database, how its development is sustainably funded and also how 
it’s the most widely deployed database engine in the world. "

Not a formal treatment, but you can piece together what's said and get a lot of 
information on how SQLite developed into what it is today, who is in the 
development team, and how it is funded through the SQLite Consortium.  Plus 
random anecdotes about computing over the last 40 years.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does VACUUM imply REINDEX?

[Simon Slavin]

On 19 Feb 2020, at 4:36pm, Olivier Mascia  wrote:

> having an apparently sane database (pragma integrity_check and pragma 
> foreign_key_check successful at least), it would make sense to do : REINDEX, 
> ANALYZE and then VACUUM as part of final preparation step for archive (the DB 
> would be seldom queried later).

That would seem to the a good order in which to do those five things.

If I was setting up a new system for customer use, I would give them a 
maintenance procedure which did those things, let them run it whenever they 
wanted, but tell them to run it at least once a year.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does VACUUM imply REINDEX?

[Simon Slavin]

On 19 Feb 2020, at 4:18pm, Richard Hipp  wrote:

> There is no benefit to running
> ANALYZE after running VACUUM if the ANALYZE data (the content of the
> sqlite_stat1 table) is still up-to-date.

However, if you have a yearly maintenance procedure, it might make sense to 
include ANALYZE in it.  This would take care of circumstances where your 
business has changed character, for instance changing from selling a lot to a 
few customers, to selling a little to many customers.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to dump a sqlite db that has an associated -wal file?

[Simon Slavin]

On 16 Feb 2020, at 8:44pm, Stefan Brüns  wrote:

> On Sonntag, 16. Februar 2020 21:26:00 CET Simon Slavin wrote:
> 
>>> One use case I am aware of (although this targets places.sqlite, not 
>>> cookies.sqlite) is reading the history, bookmarks and tags.
>> 
>> These things can be done using the bookmarks API, WebExtensions API, and 
>> other methods.  Reading the SQLite database is actually more difficult.
> 
> AFAIK this only works while FF is running ...

That is the problem that started this thread: that the database file could not 
be opened while FF was running.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to dump a sqlite db that has an associated -wal file?

[Simon Slavin]

On 16 Feb 2020, at 8:02pm, Stefan Brüns  wrote:

> Will this stop anyone from just copying the DB without the -wal file? 
> Afterwards, the DB can be read, as there is no longer any associated log.

The purpose of the locking is to prevent changes being made to the database 
during a browser session.  The problem does not occur if changes are made 
between sessions.  Having a third-party app read the database could be 
considered a vulnerability (a question for the Mozilla developer team, not me), 
but it is not related to this specific problem.

> One use case I am aware of (although this targets places.sqlite, not  
> cookies.sqlite) is reading the history, bookmarks and tags.

These things can be done using the bookmarks API, WebExtensions API, and other 
methods.  Reading the SQLite database is actually more difficult.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITHOUT ROWID tables

[Simon Slavin]

On 16 Feb 2020, at 6:00pm, Bernardo Ramos  wrote:

> I've got no rows for both with and without rowid tables:



" as of SQLite version 3.30.0 on 2019-10-04 "
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to dump a sqlite db that has an associated -wal file?

[Simon Slavin]

On 16 Feb 2020, at 5:15pm, Peng Yu  wrote:

> Why the database can not be read by another sqlite3 session when the
> corresponding -wal file exists? Thanks.

This is done on purpose by the developers of Firefox to prevent a security 
vulnerability which I will not describe in public.

One of the Mozilla developers involved in the decision reads this list.  If you 
have a good reason why you want to open the SQLite database while Firefox is 
running, you could post it here.  You might be able to persuade them to 
reconsider the decision.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Searching by Guid index in table (.NET)? I can write and read, but not search by the Guid

[Simon Slavin]

On 15 Feb 2020, at 8:12pm, J Decker  wrote:

> memcmp is harder than strcmp?
> and with blob I suppose you don't have 'if UTF8 or UTF16 do
> different things' so should compare slightly faster?

It's not cut-and-dried, but you can take things into consideration.

Once data is in a database it usually just sits there.  You're mostly 
interested in input and output.

Think of the number of times you're going to do conversions, and what formats 
you're converting from and to.  How much processing is involved in each 
conversion.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITHOUT ROWID tables

[Simon Slavin]

On 15 Feb 2020, at 3:14pm, sky5w...@gmail.com wrote:

>> To determine if table XYZ is a WITHOUT ROWID table, run "PRAGMA
>> index_info('XYZ');".  If you get back one or more rows, then XYZ is a
>> WITHOUT ROWID table.  If you get back no rows, then XYZ is a rowid
>> table.
>> 
> Confused...What if I made an index on a ROWID table?
> CREATE INDEX "Z" ON "DOC" ("n_id");

The parameter in index_info() is normally the name of an index.  So if you 
create an index "Z" and ask for index_info("Z") you will get information on 
that index.

If you create a WITHOUT ROWID table with name 'Y", and ask for index_info("Y") 
you will get information on the primary key of that table.

If both exist, you get information about the index.

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

Re: [sqlite] Searching by Guid index in table (.NET)? I can write and read, but not search by the Guid

[Simon Slavin]

On 14 Feb 2020, at 2:59pm, Andy KU7T  wrote:

> Do you suggest me keeping storing TEXT (and declaring the column as TEXT 
> instead of GUID) or storing and declaring BLOB (and remove HEX)?

TEXT.  Makes sorting and searching easier.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can I search all tables and columns of SQLite database for a specific text string?

[Simon Slavin]

On 13 Feb 2020, at 2:01pm, Scott  wrote:

> Can I search all tables and columns of SQLite database for a specific text 
> string?

No.  There's no way to do this, and there's no way to say "all tables" in SQL.  
In other words it's not easy to write such a thing.

I like Thomas Kurz's solution, to dump the database as SQL commands.  
Alternatively you could write a text exporter for all the columns you think 
might hold the string, then search that text.

If you want better solutions, it might help to tell us whether this is a 
one-time problem, something you (the developer) might want to do repeatedly, or 
something you want the user to be able to do repeatedly.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Make sqlite3 database searchable on Mac OS X

[Simon Slavin]

On 11 Feb 2020, at 4:10am, Peng Yu  wrote:

> It seems that sqlite3 databases are not searchable by Spotlight on Mac OS X. 
> Is there a way to make them searchable? Thanks.

A long time ago I wrote a SQLite indexer for … I think it was Sherlock back 
then.  It went through all text fields (ignoring numbers and BLOB) and indexed 
them.  It worked fine. But every time even a single field in a big SQLite 
database changed, my Mac slowed to a crawl, because the entire file got 
reindexed.  That was how it worked.

Hmm.  I see Core Spotlight allows entity registering.  You'd add hooks to 
SQLite so that every time a row was added, updated or deleted another routine 
was called, which told Spotlight about the new (or vanished) entity.  Problem 
is, it would work only for apps which used your own library with the extra 
programming in.  Without that, you're back to reindexing the entire database 
file any time anything in it changes.

Whichever way you did it, it would slow down SQLite a lot.  Sorry.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to group this?

[Simon Slavin]

On 10 Feb 2020, at 10:41pm, Wolfgang Enzinger  wrote:

> Am Mon, 10 Feb 2020 01:42:14 + schrieb Simon Slavin:
> 
>> On 10 Feb 2020, at 1:25am, no...@null.net wrote:
>> 
>> create two VIEWs, [...].  Index both VIEWs on (id, date),
> 
> I don't think that creating an index on a view actually works, does it?

You're right.  What was I thinking ?  Maybe I've used another implementation of 
SQL that it does work on.  Thanks for picking me up on it.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Simon Slavin]

Does this problem affect unnumbered indexes too ?  In other words if I have

(?,?,?,?,?)

and bind to the fifth one using the index do I have the same problems as having

(?1,?2,?3,?4,?5)

and bind to the fifth one using its number ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] notify all processes of database modification

[Simon Slavin]

On 10 Feb 2020, at 4:26am, Rowan Worth  wrote:

> See also PRAGMA data_version when it comes to polling the DB, the return 
> value of which changes when another process modifies the DB. IIRC the 
> implementation of this depends on a value in the DB header page, so it may be 
> sufficient to only monitor the main DB file for changes.

Theoretically, one monitors the database file for its "last change" timestamp 
(you might know this as the "touch timestamp").  In practise, some operating 
systems don't notice that this has been modified until other things have 
happened (directory info has been updated, storage driver has finished session, 
etc.).  But SQlite should always see a change in the content of the file first, 
so use the PRAGMA or manually read those bytes from the header.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to group this?

[Simon Slavin]

On 10 Feb 2020, at 1:25am, no...@null.net wrote:

> Here is one way that appears to generate the correct result.

Another way: create two VIEWs, one for systolic, one for diasystolic.  Index 
both VIEWs on (id, date), then JOIN ON id AND date.  If you want to, you could 
use this to make a third VIEW which would have the systolic and diasystolic 
figures on the same row.

If you are going to do more than just this on that data convert the dates to 
MMDD form or to an integer day number to make searching and sorting easier. 
 If you're not confident with programming you could do this in SQLite using 
CASE for the month name.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Documentation improvement request

[Simon Slavin]

Some pages in the documentation started small but had stuff gradually added to 
them over the years.  For the pages




can a "Table Of Contents" be added at the top, like the one on



?  I don't know whether this can be done automatically.  Possibly for any page 
which has section headers.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SqlLite.Net: Tables creation and schema changes strategies

[Simon Slavin]

On 8 Feb 2020, at 6:25pm, Andy KU7T  wrote:

> IF isnull(version) THEN
> -- create table, indexes etc.
> 
> ELSE
> -- make incremental changes
> 
> END IF

You're going to have to give up the idea that you can do all this in one 
complicated SQL command.  You're going to end up doing a lot of

1) use your software to execute SQL command
2) use your software to check the result and decide what to do

For instance you can't do the enclosing if checking the version number.  You 
have to do that in your software.

There is a form of of CREATE TABLE which goes

CREATE TABLE IF NOT EXISTS …

which might help.  Also SQL does have an 'IF' construction, but can't be used 
to make different changes, it is used to return different values.  Look for 
"The CASE expression" on this page:


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

Re: [sqlite] SqlLite.Net: Tables creation and schema changes strategies

[Simon Slavin]

On 8 Feb 2020, at 3:10pm, Andy KU7T  wrote:

> I'm looking for best practices on when and where to create/initialize your 
> tables when using SqlLite.Net as well as how to properly handle schema 
> changes from one version to another.

I urge you not to use CreateTable, but instead to use SQL commands to make and 
convert tables.  That way anyone who knows SQL will understand what you're 
doing, and you can more easily transfer your code to something other than .net.

In terms of version control, SQLite has space to store a version number which 
is ideal for that:



You can store whatever integer you like in there and recall it whenever you 
want.  Put a 1 in there for the first version of your program.  If you ever 
need to make schema changes, start using it to keep track of your schema 
versions.

If you would prefer not to use a SQLite-only facility, you might create a 
configuration table, with key and value rows, and have a row of that table 
indicate your schema version.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: Patch: VTable Column Affinity Question and Change Request (WIthdrawn)

[Simon Slavin]

On 8 Feb 2020, at 4:05am, Keith Medcalf  wrote:

> filename=FILENAME is the csv filename in quotes.

Please mention in documentation whether full or partial paths can be included.

> header=BOOL   will "parse" the first row to get the column names unless a 
> SCHEMA is specified.

Looks good until I see

> skip=N 

Perhaps it would be better to use "header=N" to nominate a header row.  The 
first row is row 1.  "header=0" means "there is no header row".

Having made this change, "skip=N" says how many lines after the header line 
should be skipped.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does a foreign key field need a separate index?

[Simon Slavin]

On 6 Feb 2020, at 9:23pm, Rael Bauer  wrote:

> Is a foreign key field automatically indexed, or will it benefit from a 
> separately created index?

No indexes for either lookup are automatically created.  You should create 
indexes which will help lookup in both directions, unless one of the tables is 
small or a useful index exists for some other reason, e.g. because the key is 
declared as UNIQUE.


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

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Simon Slavin]

On 4 Feb 2020, at 7:13pm, Deon Brewis  wrote:

> WHERE x IN (?1,?2,?3,?4...,?1000 )

People really do this ?  Sheesh.

> The alternate to this is to create a virtual table wrapper over the internal 
> datasets of the app. Which is of course better, but harder. (We need an STL 
> for SQLite. SqliteTL?).

One alternative is to create and populate a temporary table, then use an INNER 
JOIN or an EXCEPT.  You can keep the temporary table around until the app quits.

Another alternative is to construct the command as a string.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Simon Slavin]

On 4 Feb 2020, at 6:27pm, Alex Bronstein  wrote:

> In such cases, you can easily end
> up with more than 999 parameters.

I'm curious.  Can you show us some place where using 999 parameters is a 
reasonable way to use SQLite ?

> PostgreSQL and some other databases support a 16 bit parameter count (64K 
> parameters). Given current memory availability, can sqlite's default be 
> raised to something similar to that?

Might help to know that using the 64K'th parameter would cause SQLite to 
maintain a list 64K items long.  It's an array, not a key/value lookup.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] csv extension/feature requests: new options for field separator different from default ', ' and skip=N to skip first N lines

[Simon Slavin]

On 4 Feb 2020, at 12:18pm, Robert M. Münch  wrote:

> - sep=';': field separator character (different from default ',')

If you provide this facility, please don't add it to anything called 'csv' 
since the 'c' stands for 'comma'.

For those playing along at home, csv files using semi-colon are a result of a 
bug in Excel.  Windows has a setting for a 'list separator'.  The two most 
usual values are ',' and ';'.  The CSV export filter in Excel takes its 
separator from this field rather than always using a comma, because it was 
written by someone who wasn't aware of, didn't understand, or was intentionally 
trying to disrupt the standard.  Decades after being told about the bug, 
Microsoft hasn't fixed it.

There are a couple of other errors in Excel's CSV filters including how strings 
are quoted and how a blank value differs from a zero-length string.  The best 
way I've seen to handle this was to add a new filter to your software, similar 
to 'csv', called something like 'exceltext' which did things the Excel way.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unicode quote characters parsing in FTS5 queries

[Simon Slavin]

On 3 Feb 2020, at 9:30pm, David Guillen Fandos  wrote:

> However my queries where choking on some input that contained the U+2018 and 
> U+2019 characters. It seems like these chars are being treated like a regular 
> quote character

At least one version of SQL respects those characters for quoting strings.  
Can't remember which one at the moment.  I suspect that the problem you're 
encountering is intentional.

One test you can try is to double that character (I'm typing the code, you type 
the character)

'abcU+2018U+2018def'

and see whether this is still a syntax error, and whether you get zero, one or 
two of those characters in the output string.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is the table created by "create temp table" in the database file or in memory?

[Simon Slavin]

On 1 Feb 2020, at 1:30pm, Peng Yu  wrote:

> https://www.sqlite.org/lang_createtable.html
> 
> "The database in which the new table is created. Tables may be created in the 
> main database, the temp database, or in any attached database."
> 
> It is not clear to me in the doc. If a table is created by "create
> temp table", is it temporarily a database file but later got deleted
> upon termination of the sqlite3 session? Or it is in memory? Thanks.

Further down the same page:

" If the "TEMP" or "TEMPORARY" keyword occurs between the "CREATE" and "TABLE" 
then the new table is created in the temp database. "
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check constrain execution timing change?

[Simon Slavin]

On 1 Feb 2020, at 1:28am, Simon Slavin  wrote:

> How do you feel about that ?

Sorry, I didn't read the rest of the list first.  I see it's already answered.  
Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check constrain execution timing change?

[Simon Slavin]

On 31 Jan 2020, at 10:51pm, Richard Hipp  wrote:

> CREATE TABLE t1(x INTEGER CHECK(typeof(x)=='text'));
> INSERT INTO t1 VALUES('123');
> PRAGMA integrity_check;

Short and sweet.  That's what I was talking about.

How do you feel about that ?  Should a programmer be able to create a failure 
in integrity_check that easily ?  It's just two legit lines of SQL.  My gut 
feeling is that it points to something wrong with SQLite.  But there are lot of 
people here who know more about it than me.

Would be interesting to see an example which depends only on features of SQL, 
avoiding typeof().  Might make it more obvious whether it's a bug.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check constrain execution timing change?

[Simon Slavin]

On 31 Jan 2020, at 9:27pm, Keith Medcalf  wrote:

> You are however correct that this is an "application consistency" problem 
> more than an SQLite problem and it is a lot of change for little actual 
> benefit.

How about this ?

A program (possibly part of the SQLite precompiled suite, possibly from a third 
party) goes through any SQLite database as an integrity checker.  One of its 
jobs is to check that column constraints are not violated.  This cannot 
possible be wrong.  A SQLite database with violated CHECK clauses is, by 
definition, corrupt.

However, because the checks are performed on the values input, not the values 
stored, some data in the database does violate a CHECK constraint.

Can this happen, given the behaviour Keith identified ?  If so, I would say 
that something is wrong.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] "Standard SQL" ?

[Simon Slavin]

I would appreciate your help.  Reading a technical article today, I came across 
a casual reference to "Standard SQL" as if it was a well-known thing.  This 
worried me since I've never heard the term and I'm meant to know about such 
things.

It doesn't seem to refer to the official standard for SQL, which is huge and 
contains a plethora of features implemented once or never.  The author seemed 
to think it was a sort of 'core SQL' – features identically implemented by all, 
or most, of the well-known SQL engines.

The one possibility I can think of is SQL:1999.  This is the first version 
which has features marked as 'mandatory' or 'optional'.  A full implementation 
of all mandatory features could, I suppose, be called "Standard SQL", but I've 
never heard of that term being used for that.

Have any of you been using this term for a meaning other than "Fully conforming 
to SQL:2019 (or whatever version you think current) ?  Do you have 
documentation somewhere ?  Or are my suspicions correct and there's no such 
thing ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New word to replace "serverless"

[Simon Slavin]

On 28 Jan 2020, at 6:05pm, Roman Fleysher  
wrote:

> I would like to ask, why is it so important to indicate that SQLite, in 
> reference to threads or client/server,  " does not work that way". I think 
> this might help to find the words to describe it. 

What a great point.

As a (retired) computer admin, the point about server-based systems that most 
put me off was the admin.  You have to install server software.  Configure it.  
Decide what privs it should have.  Back up its software and configuration 
files.  Back up its data files.  Update it when new releases come out.  Deal 
with problems when OS updates turn out to be incompatible with the server 
software.

So from my point of view one very encouraging way to say "serverless" is "zero 
administration".
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New word to replace "serverless"

[Simon Slavin]

On 27 Jan 2020, at 11:53pm, Donald Shepherd  wrote:

> In-process? Same concept but defining it by what it is rather than what it 
> isn't.

This comes closest to what I think needs stating.  What you're trying to say is 
that there's no process (on the accessing computer or some other computer 
across a network) which handles and coordinates all the database access.

I was thinking about the word 'decentralised' but that seems to mean something 
that used to be central.  'uncentralised' is a word which just needs 
explaining.  And 'uncoordinated' doesn't mean the right thing in English.

One problem is that 'server' has too many meanings now.  SQLite is certainly 
'serverless', but that doesn't say enough.  Does 'decentralised' mean anything 
useful to the sort of person who might need to read that description ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL CASE WHEN THEN ELSE END

[Simon Slavin]

On 27 Jan 2020, at 2:44am, Jose Isaias Cabrera  wrote:

> CASE
>(
>  SELECT WYear FROM t2 WHERE pid = a.a
>)
>WHEN c.WYear = 2020 THEN “YES”
>ELSE “NO” END

That's not the structure of a CASE statement.

After CASE comes an expression.
After WHEN comes another expression.
If they equal one another, then the the bit after the THEN is returned.

You want something more like

SELECT 
(CASE WYear WHEN 2020 THEN 'YES' ELSE 'NO' END) AS DIGITAL
FROM t2 WHERE pid = a.a

but you'll have to fit this in with how your overall SELECT works.

Sse "The CASE expression" on this page for more details:



Also note that to quote strings you use a single apostrophe at both ends, not 
directed speech marks, just as you had in your INSERT commands.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 64 bits version missing

[Simon Slavin]

On 23 Jan 2020, at 10:22pm, Evert van Dijken  wrote:

> The 64-bits DLL is missing from the download page,

"sqlite-dll-win64-x64-331.zip" works without problems for me.  Please try 
again, or tell us what other thing you're referring to.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row length in SQLITE

[Simon Slavin]

On 22 Jan 2020, at 11:44pm, Deon Brewis  wrote:

> Is there any way to get the length of rows in a table / index in sqlite?

Do you mean the count of rows in a table / index ?

   SELECT count(*) FROM MyTable

There's no easy fast way to do this because SQLite doesn't keep that number 
handy anywhere.  It stores the entries in a tree and it would have to manually 
count the leaves of the tree.



Or do you mean the count of columns in a table / index ?

SELECT * FROM MyTable LIMIT 1

and count the number of columns returned.  Or in C

sqlite3_column_count()
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Find schema of a table in a query

[Simon Slavin]

On 20 Jan 2020, at 2:53pm, x  wrote:

> Is this possible in sql?

In SQL schema names and table names are entities.  You cannot bind a parameter 
to a either of them.

This is why, instead of keeping data in many different databases, you make one 
big schema, and use that value as a column.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] To edit in sqlite3 CLI multiline SQL statements?

[Simon Slavin]

On 18 Jan 2020, at 9:30pm, Csanyi Pal  wrote:

> can one edit a multiline SQL statement in the sqlite3 CLI?

No.

But if you make a multiline SQL statement in a text file you can paste it into 
the CLI all in one operation.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How can I detect rows with non-ASCII values?

[Simon Slavin]

On 18 Jan 2020, at 12:12pm, Rocky Ji  wrote:

> By question marks, I meant- that some text, like Dutch programmers names, and 
> address in Nordic locations, have accents and umaults and other such 
> modifications done to English-alphabets. These get displayed as ? or box

SQLite doesn't display anything.  It's a database.  It stores things in a file 
and allows you to recall them.  It provides these facilities to programs other 
people write.

So you must be using a program which isn't SQLite to display those characters.  
That program will be choosing whether to display them as alphabet characters 
with accents, or strange things like boxes and question-marks.

What program are you using to display those characters ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bad database manager. No biscuit.

[Simon Slavin]

tl;dr: CSV is a useful standard which, like most things in computing, has a 
couple of 'gotchas'.  Not all '.CSV' files are CSV files.

Can't give details but I just identified a problem which had caused the SQL 
database owner £100,000 == US$130,000 == €117,000 of damage, mostly because 
end-of-day crashed three days in a row.  Plus my company's fees.

The cause was some or all of the following, depending on your philosophy.

A) SQL schema had constraints defined for columns, but the person who did it 
didn't think of all the possible things that might go wrong, or didn't think 
they were all worth guarding against.
B) A CSV file was imported by someone who didn't understand the concepts of 
escaping quote characters and how missing values are handled.
C) Excel's CSV exporter does not conform to RFC 4180, the standard for CSV 
files.
D) An organisational culture which allows one geek to do a special one-off 
operation, outside his specialist area, without another geek casting a 
skeptical eye over the procedure or results.
E) Software which sanitises the data it put into the database very carefully, 
but didn't expect anything else to mess with the database.
F) Original writers of the software no longer work for the company.  New 
maintainers have never had anything serious go wrong before and didn't know how 
to debug serious problems.  Good enough to do their regular job, not good 
enough to deal with emergencies.

Situation was complicated by happening just after the Christmas holiday, so 
some people thought that Christmas disruption or an end-of-year run was part of 
the problem.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Not Null Constraint Issue?

[Simon Slavin]

On 17 Jan 2020, at 6:39pm, Justin Gielski  wrote:

> After looking into the issue a bit more, it appears the INTEGER value was 
> actually saved into the DB as an EMPTY not a NULL, which is currently allowed 
> by the database constraints.

Could you show us the constraint you feel disallows those things ?  We might be 
able to find a loophole in the exact way you phrased it.

Also, could someone explain to me what EMPTY means here ?  I've seen '' called 
"empty string" but it doesn't seem to be normal SQL language.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll

[Simon Slavin]

On 16 Jan 2020, at 4:19pm, Simon Slavin  wrote:

> The actual limit is 125.  You can set SQLITE_MAX_ATTACHED to more than 125.

'can' should be 'can't'.  Sorry.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll

[Simon Slavin]

On 16 Jan 2020, at 3:21pm, Keith Bertram  wrote:

> Ok. I was hoping I could set the value to a value higher than 10 without 
> compiling. I'm curious why the limit is set by default to 10.

The actual limit is 125.  You can set SQLITE_MAX_ATTACHED to more than 125.

It's worth explaining why you wouldn't want to attach 200 databases to the same 
connection.  Each time you refer to an attached database, SQLite has to search 
for that database in a list, meaning it has to match the schema name, then 
iterate through, on average, n/2 entries to find the attachment number.  The 
longer that list gets, the slower every operation not on 'main' or 'temp' gets. 
 (It's possible that SQLite hashes schema names, but I don't remember anyone 
ever saying that.)

I have seen posts to this list that say things like "We make one database file 
per day, and I want to search them all at once so I want to attach 500 files to 
my connection.".  It's a terrible idea and would result in slow performance.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Not Null Constraint Issue?

[Simon Slavin]

On 15 Jan 2020, at 9:44pm, Justin Gielski  wrote:

> *"database is locked release restore point sqlite"*

If there's nothing in your code that caused that to happen, then I would 
suspect a transient hardware glitch.  Does your code use SAVEPOINTs ?

> The database locking mode is set to NORMAL but the database is always
> opened exclusively. Could this been a concurrency issue in which 2
> connections hit the database at the exact same time?

SQLite is not meant to allow that, with the existance of the 
journal/shared-memory files acting as a mutex.  If it actually did happen, and 
you're not violating anything in the following document, then either you found 
a bug in SQLite, or you had hardware problems.

>  No data loss was
> reported yet.

If you haven't already run "PRAGMA integrity_check" on a copy of the database 
file, do so.  And tell us whether it reports corruption.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory usage when increasing SQLITE_MAX_VARIABLE_NUMBER to 32768

[Simon Slavin]

On 14 Jan 2020, at 10:56pm, Alexandre Doubov  wrote:

> 1) Does the act of bumping this limit up have an effect on memory at all 
> (assuming that no more than 999 arguments are passed into statements)?

Section 9:



If I read this correctly, memory is reserved only to the highest number you 
actually bind to, not to the number you set as SQLITE_MAX_VARIABLE_NUMBER.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

[Simon Slavin]

On 14 Jan 2020, at 4:14pm, Jean-Baptiste Gardette  wrote:

> The reason i asked this is that i have a query in wich one condition 
> filtering the recordset involves
> an UDF and this UDF needs to be processed after all table filters have been 
> applied

You cannot guarantee this.  And even if you find a solution that works now, the 
optimizer in a future version of SQLite might work differently.

Would it be possible to phrase your SELECT as a SELECT with a sub-SELECT ?  
Have the sub-SELECT figure out which rows you want in which order, then use a 
SELECT to apply your UDF to them ?  It is guaranteed that the sub-SELECT is 
processed before the SELECT.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] More job postings mention SQL than anything else

[Simon Slavin]

If you want to justify the hours you spend fiddling with SQLite you should feel 
a little happier today.



Graph from Burning Glass Analytics comparing demand for various buzzwords in 
job postings.  SQL is mentioned >10% more than any other computer language or 
technology.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unexplained table bloat

[Simon Slavin]

On 13 Jan 2020, at 9:26am, Dominique Devienne  wrote:

> Which implies length(text_val) is O(N), while
> length(blob_val) is O(1),
> something I never quite realized.

For this reason, and others discussed downthread, some languages which store 
Unicode strings store the number of graphemes as well as its contents.  So 
functions which care about the … let's call it "width" … just retrieve that 
number rather than having to parse the string to figure out the length.

In a Unicode string 'length' can mean

1) octet count (number of 8-bit bytes used to store the string)
2) number of code points (basic unicode unit)
3) number of code units (how code points get arranged in UTF8, UTF16, etc., not 
as simple as it looks)
4) length in graphemes (space-using units)
5) length in glyphs (font-rendering units)

and probably others I've forgotten.  Not to mention that I simplified the 
definitions of the above and may have got them wrong.

An application centred around rendering text (e.g. vector graphics drawing 
apps) might have each piece of text stored with all five of those numbers, just 
to save it from having to constantly recalculate them.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to disable dot commands?

[Simon Slavin]

On 13 Jan 2020, at 1:43am, Xingwei Lin  wrote:

> Is there any way can we disable the dot commands feature in sqlite?

SQLite – the library you call from C and other programming languages – does not 
support the dot commands.  It doesn't recognise them.  If you try to use them 
you will get a complaint about bad syntax.

The dot commands are part of the SQLite command line shell program.  Only this 
one program understands the dot commands.  The source code for this program is 
part of the SQLite download package.

You could make your own copy of that program which did not support the dot 
commands.  But that would not stop someone else from using their copy on your 
own database files.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unexplained table bloat

[Simon Slavin]

On 12 Jan 2020, at 10:25pm, Tom Browder  wrote:

> I assume that is just for SQLite, or am I wrong again?

You're generally right.  SQLite always uses affinities (more or less 'weak 
typing') rather than strong typing.  I don't know of any other SQL 
implementations which allow this without explicit declaration, and most don't 
allow it at all.

You can enforce strong typing in SQLite using a constraint.  But most people 
enforce type in their own code, before the value gets to SQLite.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database speed comparison

[Simon Slavin]

Are you aware of



?  Now all you have to do is write comparative tests for other SQL 
implementations.  However, from what I've seen, realistic speed tests tend to 
come down to cache sizes.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sharing an SQLite database across containers

[Simon Slavin]

" Just exactly how is a file under constant modification safely shared across 
dozens of containers, all needing concurrent access? And you’re really going to 
read this file from completely different pieces of software?

[...] does a database on a Docker shared volume in multiple containers even 
work? Why yes it does. Check. Does this work at all under load? Wait, let me 
turn on WAL mode. Now it does. Check. Do processes block each other under load? 
No! Check. "

I don't know enough about containers to understand this article, but I'm sure 
some of you do.  Though you might like to see it.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unexplained table bloat

[Simon Slavin]

On 10 Jan 2020, at 3:11pm, Ryan Mack  wrote:

> OK, I think I've got a better sense now. Hex encoding the column shows that 
> there's actually a huge amount of data stored in there. For some reason 
> length() isn't revealing it even if the column type is blob. Dumping and 
> restoring the table is truncating the data.

Is there a chance that some part of the software thinks that 0x00 is a 
terminator for BLOBs, and some other part ignores the 0x00 and abides strictly 
by the length ?  I've seen that before.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unexplained table bloat

[Simon Slavin]

On 10 Jan 2020, at 2:06pm, David Raymond  wrote:

> Well something's weird anyway. When I open it with the command line tool it 
> queries it just fine

Did you run an integrity_check on the database ?  It looks from your posts as 
if it's corrupt.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to pass -Dxxx compile option

[Simon Slavin]

On 9 Jan 2020, at 6:47am, Xingwei Lin  wrote:

> How can I pass -Dxxx compile option when I build sqlite? Such as, -
> DSQLITE_ENABLE_INTERNAL_FUNCTIONS.

Depends on which compiler you're using.  See examples for gcc on this page:


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

Re: [sqlite] Magellan 2.0 Vulnerabilities

[Simon Slavin]

On 9 Jan 2020, at 12:18am, Ware, Ryan R  wrote:

> I see absolutely nothing on sqlite.org or in the mail list archive 
> specifically about these issues

If someone reports a vulnerability here, it gets acknowledged here.  But I 
don't think Tencent posts here.

On 8 Jan 2020, at 10:27pm, Ware, Ryan R  wrote:

> We've been following the Magellan 2.0 
> (https://blade.tencent.com/magellan2/index_en.html) issues found by Tencent.

From the page at that URL:

" If you are using a software that is using SQLite as component (without the 
latest patch, which is 13 Dec 2019), and it supports external SQL queries. Or, 
you are using Chrome that is prior to 79.0.3945.79 with WebSQL enabled, you may 
be affected. "

In other words, the problem reported was patched in SQLite on 2019/12/13, and 
patched in version 79.0.3945.79 of Chrome.

> Does anyone here know if someone is working on updating the CPE info in these 
> 5 CVEs?

You would need to ask someone who works on the CPE database.  That's not us.  
However, from



"Known Affected Software Configurations: Up to (excluding)
79.0.3945.79"

In other words, the problem was fixed in Chrome 79.0.3945.79. That information 
was placed on the page on or before 2019/12/16.  I'm not sure what more you 
expect them to do.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TO

[Simon Slavin]

On 8 Jan 2020, at 4:13pm, R Smith  wrote:

> Anyone have an idea where the word TO is used in SQL in SQLite?

I seem to remember that the language used by the SQLite parser works from a 
table of data.  Would questions like this be easy to answer using that table ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] NOTNULL

[Simon Slavin]

On 8 Jan 2020, at 11:15am, R Smith  wrote:

> I find the keyword NOTNULL listed among known SQLite keywords -
> no. 88 on this page: https://sqlite.org/lang_keywords.html
> 
> But cannot find a single mention of it or place to use it in SQLite, nor get 
> any hit on the sqlite.org search except in reference to the above list.
> 
> 1. What is it for?
> 
> 2. Does it have/need any documentation?

You can use NOTNULL as a condition.  It's the opposite of ISNULL.  You see it 
usually as a constraint, to ensure that a field has a value.

However, NOT NULL with a space works too, and that version seems to be more 
popular these days.

CREATE TABLE company (
code TEXT NOT NULL,
name TEXT NOT NULL)

You're right.  I don't think I see NOTNULL mentioned anywhere else in the 
SQLite documentation.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best way to store key,value pairs

[Simon Slavin]

On 8 Jan 2020, at 3:11am, Jens Alfke  wrote:

> Consider encoding the headers as JSON and storing them in a single column. 
> SQLite has a JSON extension that makes it easy to access values from JSON 
> data in a query. You can even index them.
> 
> Simon’s suggestion (a row per header) is correct in theory, but the large 
> numbers of headers you’ll be storing in an NNTP database will make that 
> approach pretty expensive, I think. 

I believe Jens' point is valid, as long as you don't have to search/scan 
headers.  I'm not used to being able to access JSON inside SQLite yet.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Cascaded delete unexpectedly triggered by upsert

[Simon Slavin]

I advise you avoid the idea of UPSERT when dealing with SQLite (or better 
still, all SQL).  It is rarely implemented as a single operation, and you can 
get unexpected results with triggers and foreign key children.

Think of your operation as a combinations of INSERT, DELETE and UPDATE and you 
will be able to correctly predict its effects.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Cascaded delete unexpectedly triggered by upsert

[Simon Slavin]

On 8 Jan 2020, at 12:00am, Michael Kappert  wrote:

> REPLACE INTO

REPLACE INTO is an alias for INSERT OR REPLACE.  So you should assume that the 
command will do either an INSERT or a REPLACE.

See the notes about REPLACE on this page:



Is the behaviour you're seeing consistent with that documentation ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best way to store key,value pairs

[Simon Slavin]

On 7 Jan 2020, at 7:13pm, Andy  wrote:

> But it is variable number this fields and column are strictly defined. Maybe 
> all in one Sqlite text field or define for example 10 field which often will 
> empty?

Parent Child database.  One table has one entry per message.  The other table 
has one entry per header.  This is a natural conclusion for anyone uses SQL.

I suggest you pause the work on your project and read some SQL tutorials.  Read 
how relational databases work.  This is about SQL, no matter which 
implementation.  It is the same for SQLite, MS SQL, MySQL, and all other 
implementations of SQL.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How create database, tables,indices...

[Simon Slavin]

On 7 Jan 2020, at 2:11pm, Andy  wrote:

> Which function are for crating new database, tables, indices, columns, fill 
> tables.

You do this with SQL commands, just like the commands you use to read data out 
of the database.



On 7 Jan 2020, at 2:23pm, Andy  wrote:

> New database is simply open not existing file in path?

Your path is for files you execute.  You do not execute a database file.  So if 
the database is not in your current directory you must specify the directory it 
is in.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size limits

[Simon Slavin]

On 7 Jan 2020, at 10:29am, Andy  wrote:

> What are reasonable limits for size Sqlite3 database file and large blobs?

Unfortunately, recommendations (rather than hard limits) are closely tied to 
your OS and hardware.  This is because the things you want to avoid are things 
like busting your cache, or memory faults, and the sizes of those are set by 
your own computer and OS.

Experience of this list has shown that we really can't make any firm limits 
without knowledge of your setup, and you'd have to find someone with an 
identical setup to have a useful conversation.  We can only urge you to test 
things out and see for yourself where your performance drops off.

In terms of hard limits, you might like to read this:


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

Re: [sqlite] A hang in Sqlite

[Simon Slavin]

On 6 Jan 2020, at 2:40pm, Yongheng Chen  wrote:

> I am sorry if I was polluting the mail list. As nobody mentioned that before 
> and reporting bugs to this mail list is what is said in the official website, 
> I just keep doing this.

You are going what we told you to do.  It is our fault.  You are doing very 
well.

DRH has changed things and you may now send you email messages to 
b...@sqlite.org.  This should make more people more happy.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A hang in Sqlite

[Simon Slavin]

On 6 Jan 2020, at 2:27pm, Noel Frankinet  wrote:

> What is fuzzer sql ? Is it some sort of random sql generator ?

Yes.  A fuzzer is a program.  You feed it some SQL commands which obey syntax 
and work fine.  Then it mixes them and tries small changes to make many other 
SQL commands.  Almost all of these will work or generate syntax errors.  There 
are no problems with any of these.  But some of these new commands may reveal 
bugs, crashes or hangs in SQLite.

The SQLite team has previously used a fuzzer on SQLite.  But there are billions 
of possible combinations of SQLite commands.  Two fuzzers which work in 
different ways will generate different commands.

It is a technique used on many command-line programs.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite command-line result is different from Perl DBI::Sqlite result (Keith Medcalf)

[Simon Slavin]

On 5 Jan 2020, at 5:01pm, Amer Neely  wrote:

> But the question still remains, why the different results?

The optimizer gets improved from time to time in SQLite versions.  SQLite 
solved the problem faster by breaking down your query differently: deciding 
whether to do one scan or use two indexes, which table to search first, etc..

Your query was appears to violate the SQL standard, by failing to define the 
JOIN.  It should probably have resulted in a syntax error, but it didn't.  And 
it just happened that one version of SQLite interpreted the query one way, 
another interpreted it another way.

I advise you change your software to use one of the queries DRH supplied:

>   SELECT * FROM a, (b JOIN c USING(id));
> 
>   SELECT * FROM (a,b) JOIN c USING (id);

This will remove the ambiguity, meaning that however SQLite interprets the 
query in the future, or even if you switch to a different SQL engine, you get 
the result you wanted.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Questions about your "Performance Matters" talk re SQLite

[Simon Slavin]

On 4 Jan 2020, at 5:49pm, Doug  wrote:

> for (i=1000; i--; i>0);

If you have optimization turned on, your compiler might turn that into "i = 0". 
 Optimization messes with a lot of benchmarks.  Checking which optimization 
setting was used is one aspect of what Howard Chu was talking about: benchmark 
figures in isolation don't mean much.

I'm not a big fan of general statements about which form is faster, uses less 
memory, whatever, than which other form.  I want to say "try both ways and keep 
the one which is best".
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about passwords in System.Data.Sqlite

[Simon Slavin]

On 4 Jan 2020, at 7:23am, Mike King  wrote:

> This is the subject:
> 
> Hex Password with System.Data.Sqlite (.Net Core)

I suppose the bot thought you wanted to change your password for accessing the 
mailing list.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about passwords in System.Data.Sqlite

[Simon Slavin]

On 3 Jan 2020, at 12:44am, Mike King  wrote:

> Ok thanks. As this is a hobby project I don't have any cash for commercial
> extensions.

So the question becomes whether you actually need encryption in your hobby 
project, or you were just using encryption because you started off with an 
encrypted database.

There are free encryption extensions for SQLite.  But the world doesn't seem to 
have standardised on any one of them.  Which suggests that there's not one of 
them much better than the others.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Causal profiling

[Simon Slavin]

On 31 Dec 2019, at 3:03pm, Richard Damon  wrote:

> I will give a short answer, in my opinion, of a few reasons on why C is still 
> a viable language.

5) SQLite has to work on your set top box.  On your Wifi base station.  On your 
Home hub.  On the machine that runs the car park.  All these things have C 
compilers.  Not all these things have C++ compilers.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Causal profiling

[Simon Slavin]

On 31 Dec 2019, at 2:21am, Alexander Vega  wrote:

> Could there be some compile time option to force Linux or Windows
> statically?

One of the problems with this is that there are a lot of utility libraries 
built around SQLite.  These are libraries provided for general-purpose use, 
often a language shim to make it easy to call SQLite from some language which 
isn't C or C++.

Compiler options which make SQLite behave significantly different generate 
fault reports (support calls, messages to this list, etc.).  Because someone 
gets the 'different' version of SQLite and wonders why it doesn't do what the 
version they're used to does.

If SQLite3 ever moves to SQLite4, a big change in behaviour may be acceptable: 
people are used to that in full version increases.  But while SQLite remains 
version 3.x.x, an option like that would have to be thought through very 
carefully.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL help

[Simon Slavin]

On 28 Dec 2019, at 10:49pm, Barry Smith  wrote:

> Why does section 6.4.3.a.i of that linked standard not apply to his sub 
> select?

I was thinking of the matching on (3,3), but perhaps you are right.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL help

[Simon Slavin]

On 28 Dec 2019, at 5:19pm, Barry Smith  wrote:

> Is not identifier resolution (search in the current scope first then step 
> out) part of the SQL standard?

The way the SELECT statements are nested in the question is itself non-standard 
in any version of SQL I can cite in this message.

> Damn closed standards and their inability to check without paying through the 
> nose.

For that reason, we quote the 180,000 word SQL-92 …



… quite a lot, and the nine parts of SQL:2016 never.  (Hmm.  No, there's no 
SQL:2019 yet.)

> Even if not, and this is in fact undocumented, I would be amazed if it 
> changed, purely for the sheer amount of software it would break.


Oh, me too.  But I wouldn't intentionally write code that depended on it.  Nor 
would I recommend that to anyone else.  Every time a developer says "We do 
 for backward compatibility." I die a little.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL help

[Simon Slavin]

On 28 Dec 2019, at 3:46pm, x  wrote:

> In the embedded select it takes c from the outer table t1 but doesn’t flag 
> ‘ambiguous column’ for b in the embedded select. Is it standard that tables 
> at the same level are searched first for the column and tables from the 
> surrounding  levels are only searched if it’s not found?

Undocumented.  Therefore even if someone told you the what's done now, it might 
change in some future version of SQLite.  Either using a different level, or 
deciding to report the ambiguity.

To make your code dependable, instead of "b" specify "t1.b" or "t2.b", as you 
did in your WHERE clause.  This will ensure that your code is understood 
correctly by SQLite, and it will also help anyone who has to read your code in 
the future.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When not using threads: should I switch to single-thread mode

[Simon Slavin]

On 27 Dec 2019, at 9:57pm, Keith Medcalf  wrote:

> Setting "SINGLETHREAD" does indeed disable the multithreaded sorters.  When 
> in one of the multithreaded modes, that query utilizes an average of 60% CPU, 
> compared to 12% when running singlethreaded. 

So if I understand this right, SQLite in multithread mode can itself use 
multiple threads at once, which means it can use many cores at once, which 
means it might be faster, most likely for complicated queries which involve 
lots of different things to be done.

I'm slightly stunned.  That had never occurred to me.  Thank you.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When not using threads: should I switch to single-thread mode

[Simon Slavin]

On 27 Dec 2019, at 7:46pm, Keith Medcalf  wrote:

> Setting "SINGLE THREADED" mode *increased* the elapsed time to 4 minutes.  
> (Perhaps it disables some of the internal multithreaded sorters -- I don't 
> know). 

Can anyone explain this ?

(To save you reading all the stuff I snipped, it's one extremely complicated 
query which takes 2 minutes in multi-threaded mode.)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When not using threads: should I switch to single-thread mode

[Simon Slavin]

On 27 Dec 2019, at 3:06pm, Cecil Westerhof  wrote:

> My applications only use one thread (for the db stuff). Would it be a good 
> idea to switch to single-thread mode, or does that not give a real 
> performance improvement?

On a desktop computer, or a mobile phone, the increase in speed is not large.  
Maybe a few percent.  If your application is already fast enough. to please 
your users, I would not do the switching.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users