Re: [sqlite] SQL join syntax

2017-04-27 Thread 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 s

Re: [sqlite] SQL join syntax

2017-04-27 Thread 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 singl

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

2017-04-27 Thread 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 dow

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

2017-04-27 Thread 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&cache=shared", &db); I'm testing a database having this configuration in

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

2017-04-27 Thread 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.

Re: [sqlite] SQL join syntax

2017-04-27 Thread 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.sqlit

Re: [sqlite] SQL join syntax

2017-04-27 Thread 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

Re: [sqlite] SQL join syntax

2017-04-27 Thread 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@mailing

Re: [sqlite] SQL join syntax

2017-04-27 Thread 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ʎ ɟı

Re: [sqlite] SQL join syntax

2017-04-27 Thread 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

2017-04-27 Thread 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

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

2017-04-27 Thread 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:s

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

2017-04-27 Thread 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_pGZI0o9WkWdd

[sqlite] SQLite automatic backup database on Windows Server

2017-04-27 Thread 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 d

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

2017-04-27 Thread 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