Re: [sqlite] SQL join syntax

[Keith Medcalf]

On Thursday, 27 April, 2017 17:31, Joseph L. Casale  
inquired:

> Hey guys,
> So what are the semantics of the predicate on the join versus the where
> clause?

> Just curious...

When the joins are only equijoins (inner joins) of any type:

SELECT 
FROM 
JOIN  ON 
JOIN  ON 
WHERE 

is syntactic sugar for:

SELECT 
FROM , , 
WHERE ()
AND ()
AND ()

and the latter case is the one that is always processed.  The processing is as 
if there were a cross join of all the tables done and then all the conditions 
applied to select the returned rows from the selection.  In actual fact the 
query optimizer decides which order to process the table loops and were to 
apply the conditions to generate the result most efficiently.  In other words, 
for normal equijoins, the ON clause is just a where clause and there is no 
special significance attached.

You could also say:

SELECT 
FROM 
JOIN  ON () AND () AND ()
JOIN 

or even think of it as (though this is an error)

SELECT 
FROM  ON 
JOIN  ON 
JOIN  ON 

and it would have the same effect.

However, if you use "CROSS JOIN" then you are prohibiting the query planner 
from re-ordering the nested-loop order of the two tables mentioned to the left 
and right of the CROSS JOIN keywords.  The processing of the ON/WHERE clauses 
is, however, identical to an normal inner join.  CROSS JOIN merely specifies a 
specific visitation order for the tables to its left and right (outer and inner 
respectively).

However, in the case of a LEFT or OUTER join, the ON conditions attached to the 
table on the rhs of the join is "glued" to the inner loop scanning that table, 
and any tables in mentioned in that ON clause must be visited before the OUTER 
JOIN can be processed and ONLY the conditions in that ON clause are used in the 
inner loop of the rhs table.  Otherwise all equijoin ON clauses become WHERE 
clause filters and the query optimizer is free to re-order and optimize how 
they are visited and how the filtering is applied.

So, the processing order is 
(1) "glue" any ON conditions that belong to an OUTER JOINed rhs table to that 
tables' inner loop, push all other ON conditions down into the WHERE clause.
(2) optimize the visitation order of the tables and apply applicable WHERE 
conditions into the outer loops.  Only the specified conditions in the ON 
clause of an outer  join are used to select candidates from the inner loop of 
an outer join (other conditions from the WHERE clause cannot be applied)
(3) apply any leftover WHERE conditions
(4) apply any GROUP BY (which may be optimized up to step 2)
(5) apply any HAVING conditions


** All tables mentioned in the ON clauses glued to an OUTER join rhs table must 
be visited before that outer join can be visited.

See https://sqlite.org/queryplanner.html
And https://sqlite.org/optoverview.html
And https://sqlite.org/queryplanner-ng.html




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

Re: [sqlite] SQL join syntax

[Igor Tandetnik]

On 4/27/2017 7:15 PM, Joseph L. Casale wrote:

Hey guys,
Need a pointer some join syntax.

I have a couple where TableA is one to many on TableB.
TableB structure is three columns Id,Key,Value. For each
row I select in Table, I join TableB several times for each
Key whose value I want in the single row result. How do
you construct the join and predicate so that for when a
given key in TableB is not present, the result is null? A left
join alone is not enough, the WHERE/AND excludes all data
when one of the joins is not satisfied.

SELECT TableA.ColA Name, B.Value BV, C.Value CV
FROM TableB
LEFT JOIN TableB B ON TableA.Id=B.Id
LEFT JOIN TableB C ON TableA.Id=C.Id
WHERE TableA.ColB=42
 AND B.Key='BAR'
 AND C.Key='BAZ'
GROUP BY Name

Works fine when BAR and BAZ values exist in TableB.Key.


SELECT TableA.ColA Name, B.Value BV, C.Value CV
FROM TableA
LEFT JOIN TableB B ON (TableA.Id=B.Id AND B.Key='BAR')
LEFT JOIN TableB C ON (TableA.Id=C.Id AND C.Key='BAZ')
WHERE TableA.ColB=42
GROUP BY Name

The condition in the ON clause is evaluated while the join is performed; 
conceptually, for each pair of rows in the two tables.


The condition in the WHERE clause is evaluated *after* all joins are 
performed, further filtering the resultset thus produced. In case of an 
outer join, this means it has to be prepared to deal with rows that have 
NULL in some columns. If one is not careful, it's easy to accidentally 
filter those rows out, thus defeating the point of an outer join.


In other words, when the condition is false in the ON clause of a LEFT 
JOIN, the result is the row from left table combined with NULLs for 
right table. When the condition is false in WHERE clause, the whole row 
is dropped.


For a "normal" inner join, a condition has the same effect either way, 
whether it appears in ON or WHERE clause.

--
Igor Tandetnik

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

Re: [sqlite] Controlling the lifetime of shared-cache, in-memory SQLite databases.

[Simon Slavin]

On 28 Apr 2017, at 2:14am, Randall Smith  wrote:

> (a) Is this right?  A shared-cache, in-memory database is "persistent" 
> across connections from the same process that do not overlap in time?

Correct.  SQLite doesn’t count connections and do anything special when the 
number gets back down to zero.

> (b) Is there some way to reliably blow away one of these databases so I 
> know there will be no hangover state after that operation and can start fresh 
> with a virgin database?

Take a look at



Calling sqlite3_shutdown() will release all the resources SQLite uses.

Technically, we’re all meant to be calling sqlite3_initialize() and 
sqlite3_shutdown().  In practise, nobody does because if you forget to 
initialize the other routines will do it for you, and quitting the process will 
release everything.

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

[sqlite] Controlling the lifetime of shared-cache, in-memory SQLite databases.

[Randall Smith]

SQLite has the useful ability to provide an in-memory database that can be 
shared by multiple connections in the same process by opening the DB with the 
following syntax:

rc = sqlite3_open("file:memdb1?mode=memory=shared", );

I'm testing a database having this configuration in a unit test framework I 
have, and I'm finding that when I re-run the unit test, the data in the 
in-memory DB seems to still be around from the previous unit test run.  This 
sort of makes sense; the underlying idea here is something else in the same 
process can (re)-open the database via the same URI.

My questions are:

(a) Is this right?  A shared-cache, in-memory database is "persistent" 
across connections from the same process that do not overlap in time?
(b) Is there some way to reliably blow away one of these databases so I 
know there will be no hangover state after that operation and can start fresh 
with a virgin database?

Thanks in advance for any information or ideas.

Randall.


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

[sqlite] Semantics regarding command instances and queries in the C# client

[Joseph L. Casale]

Typically I open a connection and a command instance and associate instantiated
parameters with the command instances, then process in a loop simply changing
parameter values.

I have a situation when I am needing to insert and select based on several 
criteria
and that practice looks a bit ugly.

What are the guidelines surrounding command instances, parameters and query
text with the client in terms of best practice when performance is a 
consideration?

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

Re: [sqlite] SQL join syntax

[Simon Slavin]

On 28 Apr 2017, at 12:31am, Joseph L. Casale  wrote:

> So what are the semantics of the predicate on the join versus the where 
> clause?
> Just curious...

SELECT  FROM 
JOIN  WHERE 

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

Re: [sqlite] SQL join syntax

[Joseph L. Casale]

From: sqlite-users  on behalf of 
Simon Slavin 
Sent: Thursday, April 27, 2017 5:26 PM
To: SQLite mailing list
Subject: Re: [sqlite] SQL join syntax
    
> On 28 Apr 2017, at 12:21am, Keith Medcalf  wrote:
> 
>> SELECT TableA.ColA Name, B.Value BV, C.Value CV
>> FROM TableB
>
> I think Keith means "FROM TableA" on that second line.  The rest looks 
> perfect.

Hey guys,
So what are the semantics of the predicate on the join versus the where clause?
Just curious...

Thanks a lot Keith and Simon!
jlc
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL join syntax

[Simon Slavin]

On 28 Apr 2017, at 12:21am, Keith Medcalf  wrote:

> SELECT TableA.ColA Name, B.Value BV, C.Value CV
> FROM TableB

I think Keith means "FROM TableA" on that second line.  The rest looks perfect.

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

Re: [sqlite] SQL join syntax

[Keith Medcalf]

SELECT TableA.ColA Name, B.Value BV, C.Value CV
FROM TableB
LEFT JOIN TableB B ON TableA.Id=B.Id
LEFT JOIN TableB C ON TableA.Id=C.Id
WHERE TableA.ColB=42
 AND (B.Key='BAR' or B.Key is null)
 AND (C.Key='BAZ' or C.Key is null)
GROUP BY Name


-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Joseph L. Casale
> Sent: Thursday, 27 April, 2017 17:15
> To: 'sqlite-users@mailinglists.sqlite.org'
> Subject: [sqlite] SQL join syntax
> 
> Hey guys,
> Need a pointer some join syntax.
> 
> I have a couple where TableA is one to many on TableB.
> TableB structure is three columns Id,Key,Value. For each
> row I select in Table, I join TableB several times for each
> Key whose value I want in the single row result. How do
> you construct the join and predicate so that for when a
> given key in TableB is not present, the result is null? A left
> join alone is not enough, the WHERE/AND excludes all data
> when one of the joins is not satisfied.
> 
> SELECT TableA.ColA Name, B.Value BV, C.Value CV
> FROM TableB
> LEFT JOIN TableB B ON TableA.Id=B.Id
> LEFT JOIN TableB C ON TableA.Id=C.Id
> WHERE TableA.ColB=42
>  AND B.Key='BAR'
>  AND C.Key='BAZ'
> GROUP BY Name
> 
> Works fine when BAR and BAZ values exist in TableB.Key.
> 
> Thanks guys,
> jlc
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] SQL join syntax

[Joseph L. Casale]

> Works fine when BAR and BAZ values exist in TableB.Key.

Move the predicate on to the join it seems?


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

[sqlite] SQL join syntax

[Joseph L. Casale]

Hey guys,
Need a pointer some join syntax.

I have a couple where TableA is one to many on TableB.
TableB structure is three columns Id,Key,Value. For each
row I select in Table, I join TableB several times for each
Key whose value I want in the single row result. How do
you construct the join and predicate so that for when a
given key in TableB is not present, the result is null? A left
join alone is not enough, the WHERE/AND excludes all data
when one of the joins is not satisfied.

SELECT TableA.ColA Name, B.Value BV, C.Value CV
FROM TableB
LEFT JOIN TableB B ON TableA.Id=B.Id
LEFT JOIN TableB C ON TableA.Id=C.Id
WHERE TableA.ColB=42
 AND B.Key='BAR'
 AND C.Key='BAZ'
GROUP BY Name

Works fine when BAR and BAZ values exist in TableB.Key.

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

Re: [sqlite] SQLite automatic backup database on Windows Server

[Hick Gunter]

SQLite is a library, not a server. There is no automatic backup. You can copy 
the .db file when the application is inactive, or have the application call the 
SQLite backup API to perform the backup whenever it feels the urge to do so.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dusan Petrovic
Gesendet: Mittwoch, 26. April 2017 22:15
An: sqlite-users@mailinglists.sqlite.org
Betreff: [sqlite] SQLite automatic backup database on Windows Server

Hey guys,

I am new on SQLite engine. Sorry for rookie mistakes. My company is running an 
application on windows server(hosted on Amazon Web Service) and the application 
contains a database. Because it is sensitive data, the database need to be 
automatically backed up. There is .db file on the database. I installed sqlite 
and sqlite browser on the server. How can I set automatic database backup for 
the database? Thanks a lot ___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] SQLite memory calculator (was Multiple sessions, page sizes and cache)

[Kim Gräsman]

On Wed, Apr 26, 2017 at 11:00 PM, Kim Gräsman  wrote:
>
> I've used a spreadsheet internally to forecast how much memory SQLite
> will need in a given configuration.
>
> I've cleaned/generalised it a little and posted here:
> https://docs.google.com/spreadsheets/d/1cCawZE9AdF4jlmgAMjbz_pGZI0o9WkWddPyV2YglFCY/edit#gid=0
>
> Since we now get out-of-memory with a fixed heap, I'm guessing there's
> some kind of think-o in the calculations above.

Let me confirm that.

I'd completely neglected to perform the actual Robson calculation...
So the totals I'd calculated were just a detailed forecast for the M
part of Robson's proof.

I still need to scale it by n to get a proper heap size, N:
https://www.sqlite.org/malloc.html#_mathematical_guarantees_against_memory_allocation_failures

Sorry about the noise.

This is actually simple to add to the spreadsheet, so I'll extend it
to cover Robson end-to-end.

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

[sqlite] SQLite automatic backup database on Windows Server

[Dusan Petrovic]

Hey guys,

I am new on SQLite engine. Sorry for rookie mistakes. My company is running an 
application on windows server(hosted on Amazon Web Service) and the application 
contains a database. Because it is sensitive data, the database need to be 
automatically backed up. There is .db file on the database. I installed sqlite 
and sqlite browser on the server. How can I set automatic database backup for 
the database? Thanks a lot
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite memory calculator (was Multiple sessions, page sizes and cache)

[Kim Gräsman]

Hi Simon,

On Wed, Apr 26, 2017 at 11:46 PM, Simon Slavin  wrote:
>
> On 26 Apr 2017, at 10:00pm, Kim Gräsman  wrote:
>
>> Specifically, I wonder if 4MiB for the general-purpose heap is maybe
>> entirely unreasonable? Is there a way to forecast how much memory will
>> be necessary for transactions and query processing, or does that
>> depend entirely on the workload?
>
> Depends on a number of factors, including the data present in the
> database.  In other words, you can forecast memory-usage based on the
> data in your test database, but that does not allow you to predict
> memory-use for your customer’s databases unless you know how many rows
> they have in their tables, whether the chunkiness of their data has
> changed since the last ANALYZE and such things.  This isn’t the way
> you’re meant to use SQL.  You’re meant to issue your commands and have
> the SQL engine worry about executing them.

https://www.sqlite.org/malloc.html leads me to believe otherwise.
Especially the zero-malloc feature-set seems exactly aimed at
controlling this.

> So do you have good indexes ?

Yes. And we force index use explicitly to make sure we get consistent
results, instead of relying on the query planner.

> With them, SQLite can just iterate through the entries in the table, as
> indicated by index order.  This dramatically reduces usage of the cache.

Cache usage should not be a problem -- we can control that with the
soft heap limit to have it auto-adjust.

Compared to page cache, lookaside and scratch, general-purpose memory
is not as well-described. I think that makes sense -- it's essentially
"everything else" -- but I was expecting and hoping SQLite wouldn't
indiscriminately use general-purpose memory for transaction/query
processing. That said, it's hard to see through all the layers here.
Some of this is described in:
https://www.sqlite.org/malloc.html#_computing_and_controlling_parameters_m_and_n

I don't see anything there that applies directly to our situation.

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

Re: [sqlite] Increasing performance of query

[Clemens Ladisch]

Gabriele Lanaro wrote:
> Are there any tips to make sure that the cache is used properly?

The default is 2000 pages (8 MB with the default page size), and in most
cases should be increased.

But what works best in your specific case depends on the hardware, the
OS, the software, the database schema, and the data.  You have to test
and measure yourself.


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