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

[Barry Smith]

Regardless of whether you decide to store GUIDs as text or binary, things will 
be easier if you set your connection string appropriately. BinaryGUID is the 
parameter you want to change. 

See https://www.connectionstrings.com/sqlite-net-provider/store-guid-as-text/

For performance binary is better than text representation. The text UID 
requires twice as many bytes to store, twice as much in cache. If the UID is a 
significant portion of your row, this could be significant. If you’re running 
the latest version of SQLite you can make a computed column to get the text 
representation for manual/CLI access if you need it.

Also ‘randomblob(16)’ will not give you a real GUID. It’d work for most 
purposes but why not call it something else, at least that’d give you a red 
flag to perhaps expect problems when you pass it to a library that expects a 
real GUID and might check the format bits.

> On 14 Feb 2020, at 6:09 am, Andy KU7T  wrote:
> 
> Hi,
> 
> I am having trouble searching for a Guid. I think some conversion is missing. 
>  I wonder if someone can point me to the error…
> 
> Column:
> [ID] GUID(16)
> Index:
> CREATE UNIQUE INDEX [ID_INDEX] ON [DXLOG]([ID] COLLATE [BINARY] ASC);
> Trigger:
> CREATE TRIGGER [AUTOGENERATE_ID] AFTER INSERT ON [DXLOG] FOR EACH ROW WHEN 
> ([NEW].[ID] IS NULL)
> BEGIN
>  UPDATE
>[DXLOG]
>  SET
>[ID] = HEX (RANDOMBLOB (16))
>  WHERE
>[NEW].[ID] IS NULL AND [DXLOG].[ROWID] = [NEW].[ROWID];
> END;
> 
> Inserting a row simply will create a new guid for me. Good. Reading it like 
> this:
> 
> SELECT ID FROM [Dxlog] WHERE RowId = @RowID
> 
> Where @RowId is the LastInsertedRowId.
> And getting it in code:
> 
> Dim rdr2 As SQLiteDataReader = Nothing
> rdr2 = theDB.ExecuteQuery("SELECT ID FROM [Dxlog] WHERE RowId = @RowID", 
> sqlParameters)
> rdr2.Read()
> Me.mvarId = rdr2.Item("ID")
> 
> The returned ID is indeed a Guid. In SQLite it looks like this:
> 
> 40FD6722384053ED3BA45CD1C5FDB30D
> And in .NET: {40fd6722-3840-53ed-3ba4-5cd1c5fdb30d}
> 
> Now, when I am trying to search for it like this, I get nothing:
> 
>sqlParameters = New List(Of SQLiteParameter)()
>AddSqlParam(sqlParameters, "@ID", DbType.Guid, Me.Id)
> 
>rdr2 = theDB.ExecuteQuery("SELECT * FROM [Dxlog] WHERE ID 
> = @ID", sqlParameters)
>rdr2.Read()
> 
> 
> Do I have to converte the Guid to binary somehow?
> 
> Thanks
> Andy
> 
> Sent from Mail for Windows 10
> 
> ___
> 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] Unexplained table bloat

[Barry Smith]

On the original topic...

How does one end up with a database in this state? I.e with a binary value that 
contains 0x00 bytes followed by other bytes but a type of TEXT?

If the definition of a text string in SQLite is that it ends at the first 0x00 
byte, then it seems that anything stored as a text string should adhere to that.

So a database with a TEXT value that contains characters after the first 0x00 
should be considered corrupt. Given that to retrieve the actual contents of the 
cell it must be cast to BLOB, why not force the storage of any string that 
contains 0x00 as a BLOB in the first place?

What am I missing here?

On 13 Jan 2020, at 6:02 am, Simon Slavin  wrote:
> 
> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Barry Smith]

One thing that really stands is “creates 64 threads that operate on independent 
tables in the sqlite database, performing operations that should be almost 
entirely independent.”

But that’s not how SQLite works - at least not when writing data. SQLite takes 
a lock on the entire database, there is no fine granularity locking that allows 
you to perform simultaneous writes to different tables.

It seems attempting to do this - use 64 threads to write to a database - is a 
highly inefficient use of the library.

> On 2 Jan 2020, at 1:54 pm, Doug  wrote:
> 
> I asked for some information from Emery Berger about his video talk on 
> performance where he said they got a 25% improvement in SQLite performance. 
> Here is the reply I got back.
> 
> 
> 
> I know there has been a lot of talk about what can and cannot be done with 
> the C calling interface because of compatibility issues and the myriad set of 
> wrappers on various forms. I’m having a hard time letting go of a possible 
> 25% performance improvement.
> 
> 
> 
> I don’t have the slightest idea on how to run a benchmark (but I could 
> learn). I wonder if the current set of benchmarks used by SQLite developers 
> actually measure throughput using wall-clock numbers. It might be a good idea 
> to put a wrapper around all the benchmarks to capture how long they took to 
> run (wall-clock), and include things like number and type of cpu cores, 
> average cpu busy time, and other relevant numbers. If the benchmarks are run 
> on lots of different machines (all over the world?), it would provide an 
> excellent view of what changes in SQLite made a difference in performance.
> 
> 
> 
> Doug
> 
> 
> 
> From: Curtsinger, Charlie  
> Sent: Thursday, January 02, 2020 10:55 AM
> To: dougf@comcast.net
> Cc: Emery D Berger 
> Subject: Re: Questions about your "Performance Matters" talk re SQLite
> 
> 
> 
> Hello Doug,
> 
> 
> 
> I was able to track down the sqlite benchmark I ran for the paper, and I’ve 
> checked it into the github repository at 
> https://github.com/plasma-umass/coz/tree/master/benchmarks/sqlite. This 
> benchmark creates 64 threads that operate on independent tables in the sqlite 
> database, performing operations that should be almost entirely independent. 
> This benchmark exposes contention inside of sqlite, since running it with a 
> larger number of hardware threads will hurt performance. I see a performance 
> improvement of nearly 5x when I run this on a two-core linux VM versus a 
> 64-thread Xeon machine, since there are fewer opportunities for the threads 
> to interfere with each other.
> 
> 
> 
> You can also find the modified version of sqlite with the same benchmark at 
> https://github.com/plasma-umass/coz/tree/master/benchmarks/sqlite-modified. 
> There are just a few changes from indirect to direct calls in the sqlite3.c 
> file.
> 
> 
> 
> I reran the experiment on the same machine we used for the original Coz 
> paper, and saw a performance improvement of around 20% with the modified 
> version of sqlite. That’s slightly less than what we originally found, but I 
> didn’t do many runs (just five) and there’s quite a bit of variability. The 
> compiler has been upgraded on this machine as well, so there could be some 
> effect there as well. On a much-newer 64-thread Xeon machine I see a 
> difference of just 5%, still in favor of the modified version of sqlite. 
> That’s not terribly surprising, since Intel has baked a lot of extra 
> pointer-chasing and branch prediction smarts into processors in the years 
> since we set up the 64-core AMD machine we originally used for the Coz 
> benchmarks.
> 
> 
> 
> As far as measuring performance, I’d encourage you *not* to use cpu cycles as 
> a proxy for runtime. Dynamic frequency scaling can mess up these 
> measurements, especially if the clock frequency is dropped in response to the 
> program’s behavior. Putting many threads to sleep might allow the OS to drop 
> the CPU frequency, thereby reducing the number of CPU cycles. That doesn’t 
> mean the program will actually run in a shorter wall clock time. Some CPUs 
> have a hardware event that counts “clock cycles” at a constant rate even with 
> frequency scaling, but these are really just high-precision timers and would 
> be perfectly fine for measuring runtime. I’m thinking of the “ref-cycles” 
> event from perf here.
> 
> 
> 
> Hope this helps,
> 
> - Charlie
> 
> ___
> 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 help

[Barry Smith]

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

In the quote below, CR is Column Reference, CN is Column Name.

3) If CR does not contain a , then CR shall be contained
within the scope of one or more s or s whose associated tables include a column whose  is CN. Let the phrase possible qualifiers denote those
s and s.

a) Case:

  i) If the most local scope contains exactly one possible
 qualifier, then the qualifier Q equivalent to that unique
  or  is implicit.



> On 28 Dec 2019, at 9:48 am, Simon Slavin  wrote:
> 
> 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 …
> 
> <https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt>
> 
> … 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL help

[Barry Smith]

Is not identifier resolution (search in the current scope first then step out) 
part of the SQL standard? Damn closed standards and their inability to check 
without paying through the nose.

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.

> On 28 Dec 2019, at 8:19 am, Simon Slavin  wrote:
> 
> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] AVG Function HowTo

[Barry Smith]

SQL is a declarative language, not a procedural one. Therefore I find it helps 
to think in declarative terms rather than procedural.

What that means practically in this case is don’t think in terms of loops. 
Think about what you want, and think about how your data is related.

I don’t entirely understand what you want. Am I correct in restating it as:
‘You want to know the expenses in each earning period.‘

Expenses and earnings are related by date.

So, I suggest:
 - you want to group your expenses data by the last earning date.
  - you want to run an aggregate function (likely SUM, but I’m not entirely 
sure what you’re trying to do) within each group.
 - SQL supports comparison operators on dates (assuming you store them in an 
appropriate format, like integer timestamp or ISO86somethingsomething).

Something like the following might give you what you want:

SELECT
 SUM(expense) as period_expenses,
 (SELECT MAX(ear.Date) FROM earnings AS ear WHERE ear.Date <= exp.Date) AS 
last_earning_date
FROM
 Expenses AS exp
 GROUP BY
 last_earning_date

This might not be what you want but is perhaps a starting point of how things 
are done in SQL.

You can also achieve that without the sub select by using joins and window 
functions or a variety of other ways, the above is just how I think about it.

Also you might want to use date functions to strip out only the date if you’re 
storing date+time in your date field.

> On 28 Dec 2019, at 8:45 am, Luuk  wrote:
> 
> 
>> On 28-12-2019 13:07, Csanyi Pal wrote:
>> Hi,
>> 
>> I have attached here the exported sql file of my little sqlite database.
>> 
>> I am a newbe in the sqlite language so I ask here only an advice in which 
>> direction to go, eg. which functions of sqlite language to use to achieve my 
>> goal?
>> 
>> 
> SQList is able to calulate averages. lets give simple example
> 
> select min(a),avg(a),max(a)
> from (select 1 as a union all select 2 union all select 3 union all select 4);
> 
> It will output:
> 
> 1|2.5|4
> 
> because 1 is the minimum values of the selected values 1,2,3,4
> 
> 2.5 is the average of the selected values 1,2,3,4
> 
> and 4 is the max values of those.
> 
> 
> For calculating with date (and/or time) function you should read this page:
> https://www.sqlite.org/lang_datefunc.html
> 
> If you created an example that 'does not work' (i do mean 'that does not do 
> what you expect it to do' ), come back here with that example, and i'm sure 
> someone will help you
> 
> post the database structure, the query and some example data, and if possible 
> the expected output...
> 
> 
>> Do I think well?
>> 
>> Any advices will be appreciated!
>> 
> ___
> 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] Row locking sqlite3

[Barry Smith]

You might be interested in the BEGIN CONCURRENT branch. It does page level 
locking (not quite as granular as row level).

https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md

> On 22 Mar 2019, at 11:48 am, Thomas Kurz  wrote:
> 
> This sounds interesting. I have some questions about:
> 
>> Row lock information is shared with processes. If a process finished 
>> unexpectedly, unnecessary lock information might be stayed. In order to 
>> unlock them, please use sqlumdash_cleaner.exe which clears all record 
>> information. If there is a process which is in a transaction, 
>> sqlumdash_cleaner.exe should be called after end the transaction.
> 
> - Where is row lock information stored? In database file, in journal file, in 
> WAL file, or in memory?
> - Why should the cleaner be called after the end of a transaction?
> - I don't like the idea of calling an external exe in case of problems (and 
> more than that, after every transaction??). Couldn't you introduce a PRAGMA 
> for unlocking rows?
> 
> 
> 
> - Original Message - 
> From: Peng Yu 
> To: SQLite mailing list 
> Sent: Friday, March 22, 2019, 15:25:24
> Subject: [sqlite] Row locking sqlite3
> 
> Hi,
> 
> I see that sqlite3 still does not support row locking. This package
> tries to resolve this problem. But it does not have a standard build
> process for Linux.
> 
> https://github.com/sqlumdash/sqlumdash/
> 
> Are there other packages similar to sqlite3 but support row locking? Thanks.
> 
> -- 
> Regards,
> Peng
> ___
> 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
___
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 use sqlite online-backup system in an ongoing manner?

[Barry Smith]

I think the sessions extension can be used for what you want to do. I haven't 
used it myself, but from it's documented behaviour it looks like you could 
record change sets for every three second interval then apply them back to your 
database on disk. If your app is multi-threaded it might be a pain (unless 
there's an atomic 'stop this changeset and immediately start another one'

> On 13 Mar 2019, at 8:40 am, Simon Slavin  wrote:
> 
>> On 13 Mar 2019, at 2:31pm, John Smith  wrote:
>> 
>> I am working with IN-MEMORY database.
>> When my program starts I load data from file-system DB into my IN-MEMORY DB.
>> All other SQL operations are performed directly on my IN-MEMORY database.
>> This is in order to keep performance high.
> 
> First, make sure you really need to do this.  SQLite performance is normally 
> very high, even without taking special measures.  You may be wasting 
> programming time and introducing complexity which will be difficult to debug. 
>  Run some time-trials.  Of course, you may have already run some time-trials.
> 
>>// Save only intermediate changes (?)
>>sqlite3_backup_step(p, -1); // Backup all modifications from last time
> 
> You cannot combine these two things.  The Online Backup API backs up an 
> entire database.  It does it page by page, without understanding individual 
> rows of data.  It cannot select only changes.  So you might want to use it, 
> but if you do you'll create a new copy of the entire database every time.
> 
> You might want to instead use the Resumable Bulk Update extension:
> 
> 
> 
> " An RBU Update is a bulk update of a database file that may include many 
> insert, update and delete operations on one or more tables. "
> 
> Simon.
> ___
> 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] [EXTERNAL] SQLite error (5): database is locked

[Barry Smith]

That's not how you set a busy timeout using a connection string. That's not how 
you set any (pragma) options with a connection string. Check the 
System.Data.SQLite documentation (or google) to find out connection string 
parameters, or play around with the SQLiteConnectionStringBuilder.

The busy timeout pragma will not help if you end up in a deadlock. One way a 
deadlock can occur is as follows:
 1) Connection A starts a transaction and reads the database - it takes out a 
shared lock
 2) Connection B starts a transaction and reads the database - it too takes out 
a shared lock
 3) Connection A attempts to write to the database, so it upgrades to a 
reserved lock and waits for all readers to close (all shared locks to be 
released).
 4) Connection B attempts to write to the database. It keeps its shared lock 
and tries to upgrade to reserved. Because A already owns a reserved lock, 
connection B is denied its lock. No amount of waiting will solve this problem 
because A is waiting for B to release its lock so it can have an exclusive 
lock. SQLite knows this, so it returns SQLITE_BUSY immediately.

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

Note that the above is true for databases with an old style (non-WAL) journal. 
I assume similar protections and situations exist in WAL, but can't be certain 
and they may use a different mechanism.

You can figure out whether you have a deadlock or simply the wait timed out by 
looking at how quickly the error was returned. You can also look at where other 
threads (or processes if your debugger can attach to multiple processes) are 
when the error occurs.

Are you using explicit transactions? Entity Franework shouldn't cause deadlocks 
unless you are manually taking control of the transactions. EF can also take a 
very long time to SaveChanges if you have a large number of entities...

> On 14 Jan 2019, at 10:14 pm, Urs Wagner  wrote:
> 
> We are using entity framework
> 
> The timeout pragma does not work. Is think the timeout is not set, see below
> 
>var esb = new EntityConnectionStringBuilder
>{
>Metadata = 
> "res://*/RadaxModel.csdl|res://*/RadaxModel.ssdl|res://*/RadaxModel.msl",
>Provider = "System.Data.SQLite.EF6",
>ProviderConnectionString = @"data source=" + _dataBase + 
> ";PRAGMA foreign_keys = ON;PRAGMA locking_mode = EXCLUSIVE;PRAGMA 
> schema.synchronous = NORMAL; PRAGMA schema.journal_mode = DELETE; PRAGMA 
> busy_timeout = 10"
>};
> 
> 
> -Original Message-
> From: sqlite-users  On Behalf 
> Of Hick Gunter
> Sent: Monday, January 14, 2019 11:28 AM
> To: 'SQLite mailing list' 
> Subject: Re: [sqlite] [EXTERNAL] SQLite error (5): database is locked
> 
> With journal mode, SQLite supports 1 writer OR n readers; with WAL mode, 
> SQLite supports 1 writer AND N readers.
> 
> In any case, connections need to indicate if or how long they are willing to 
> wait for the db file to be unlocked. Default is NO.
> 
> The easiest way is to specify a timeout on the connection. The value needs to 
> be longer than your longest write transaction is expected to run and shorter 
> than the latency required by your application.
> 
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
> Auftrag von Urs Wagner
> Gesendet: Montag, 14. Jänner 2019 10:24
> An: SQLite mailing list 
> Betreff: [EXTERNAL] [sqlite] SQLite error (5): database is locked
> 
> Hallo
> 
> I use several tasks in C# to call Sqlite queries.
> No I get the error SQLite error (5): database is locked.
> Is it not possible to use more than one tasks with Sqlite?
> 
> Regards
> 
> Urs
> ___
> 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 | 
> Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) 
> +43 1 80100 - 0
> 
> May be privileged. May be confidential. Please delete if not the addressee.
> ___
> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Doing math in sqlite

[Barry Smith]

> On 20 Dec 2018, at 4:21 pm, Jungle Boogie  wrote:
> 
> Hi All,
> 
> This is more of a how do I do this in sql question. I apologize in advance
> for a simple question, but I need to learn somehow, so any pointers are 
> appreciated.
> 
> My very simple schema:
> 
> CREATE TABLE mileage (
> date text,
> "car" text,
> "od_reading" integer
> )
> 
> Some sample data:
> 2018/04/15,foo,170644
> 2018/04/15,bar.69625
> 2018/04/22,foo,170821
> 2018/04/22,bar,69914
> 2018/04/29,foo,171006
> 2018/04/29,bar,70123
> 2018/05/06,foo,171129
> 2018/05/06,bar,70223
> 2018/05/09,foo,171178
> 2018/05/20,foo,171304
> 2018/05/20,bar,70517
> 
> I can select the first and last od_readings pertaining to a particular car 
> this
> way:
> select od_reading from mileage where car='foo' limit 1
> select od_reading from mileage where car='bar' order by od_reading desc limit 
> 1
> 
> How do I subtract the last query from the first query to determine how many
> miles were driven?
> 
> Next, is there an elegant way to see how many miles difference there is 
> between
> two readings of the same car?
> 
> For instance, how many miles difference are there between bar on 2018/05/20 
> and
> 2018/05/06?

I wouldn't call my solution elegant, but if you put a unique constraint on the 
"date" column, and want your query to return null if there is not a record 
exactly on that date:

SELECT
   (SELECT od_reading FROM mileage WHERE date = '2018/05/20' AND car='bar')
   -
   (SELECT od_reading FROM mileage WHERE date='2018/05/06' AND car='bar')
As ExactMileage

You can rephrase that as:
SELECT end.od_reading - start.od_reading FROM mileage AS start, mileage AS end 
WHERE end.date='2018/05/20' AND start.date='2018/05/06' AND end.car ='bar' and 
start.car='bar'

Or even you could use CTEs to repeat yourself less:
WITH OnlyBar AS SELECT * FROM mileage WHERE car='bar'
SELECT end.od_reading - start.od_reading FROM OnlyBar AS start, OnlyBar AS end 
WHERE end.date='2018/05/20' AND start.date='2018/05/06

If you might query between two dates that don't necessarily have a record, the 
best you can do is give a range of what the possible driven distance may be:
SELECT
   (SELECT MIN(od_reading) FROM mileage WHERE date >= '2018/05/20' AND 
car='bar')
   -
   (SELECT MAX(od_reading) FROM mileage WHERE date <= '2018/05/06' AND 
car='bar')
AS MaxPossible,
   (SELECT MAX(od_reading) FROM mileage WHERE date <= '2018/05/20' AND 
car='bar')
   -
   (SELECT MIN(od_reading) FROM mileage WHERE date >= '2018/05/06' AND 
car='bar')
AS MinPossible

The second query finds the records before and after the dates in question (or 
uses data on exactly that date) to figure out the minimum and maximum possible 
distances that may have been driven between the dates of interest.

Note that there is a more efficient form of the second query, where one selects 
just od_reading and orders by date, with a limit of 1 instead of using the MIN 
and MAX functions; if your table is going to be large then that is a better 
option (with an index on (car, date), the value can then be computed with an 
index seek instead of a table scan). I used the min/max version because it's 
simpler and easier to read

> 
> Thanks,
> j.b.
> ___
> 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] The "natural" order of the query results.

[Barry Smith]

Without an order by, sqlite can return the rows in any order it pleases. Likely 
whatever consumes the least resources. Although unlikely given your indices, it 
might be possible - for instance if some future micro-optimisation finds that 
it's quicker to read the index in reverse, then sqlite would give things in the 
opposite order. If you leave out a necessary order by you are very much 
exposing yourself to internal changes. So much so that there is a pragma 
reverse_unordered_selects 
(https://www.sqlite.org/pragma.html#pragma_reverse_unordered_selects) you can 
use to specifically find if your app makes invalid assumptions about order.

You might have found a missed optimisation opportunity here (although there's 
always the tradeoff of library size & cycles to optimise vs execution cycles 
saved to consider).

> On 16 Sep 2018, at 6:29 pm, John Found  wrote:
> 
> Is there some relation between the indexes used in the query, the GROUP BY 
> fields used 
> and the order of the result rows, when no "ORDER BY" clause is used?
> 
> I am asking, because I noticed, that on some queries, when I am using "ORDER 
> BY" the query always 
> use temporary b-tree for ordering, but by including the needed fields in the 
> "GROUP BY" clause 
> and removing the ORDER BY clause, the query returns the rows in the proper 
> order without temp b-tree.
> 
> So, is it safe to use this implicit ordering, or this behavior can be changed 
> in the future versions of SQLite?
> 
> Here is an example:
> 
> create table A (
>  id integer primary key autoincrement,
>  o1 integer,
>  o2 integer
> );
> 
> create table B (
>  Aid integer references A(id),
>  data text
> );
> 
> create index idxA on A(o1 desc, o2 desc);
> 
> insert into A(o1, o2) values (1, 100), (2, 50), (3, 200), (5, 300);
> insert into B(Aid, data) values (1, "b"), (1, "a"), (2, "c"), (2, "d"), (2, 
> "e"), (3, "f"), (3, "g");
> 
> -- Always uses temp b-tree for order by
> select 
>  group_concat(B.data), o1, o2
> from 
>  A
> left join 
>  B on A.id = B.Aid
> group by 
>  A.id
> order by 
>  A.o1 desc, A.o2 desc;
> 
> explain query plan:
> idparentnotuseddetail
> 800SCAN TABLE A
> 1900SEARCH TABLE B USING AUTOMATIC COVERING INDEX (Aid=?)
> 5800USE TEMP B-TREE FOR ORDER BY
> 
> 
> -- This one returns the rows in the needed order without ORDER BY
> select 
>  group_concat(B.data), o1, o2
> from 
>  A indexed by idxA
> left join B on A.id = B.Aid
> group by A.id, A.o1, A.o2;
> 
> explain query plan:
> idparentnotuseddetail
> 700SCAN TABLE A USING COVERING INDEX idxA
> 1800SEARCH TABLE B USING AUTOMATIC COVERING INDEX (Aid=?)
> 
> 
> -- But if I add ORDER BY it still begins to use temp b-tree 
> -- regardless that it does not change the order.
> select 
>  group_concat(B.data), o1, o2
> from 
>  A indexed by idxA
> left join B on A.id = B.Aid
> group by A.id, A.o1, A.o2
> order by A.o1 desc, A.o2 desc;
> 
> explain query plan:
> 800SCAN TABLE A
> 1900SEARCH TABLE B USING AUTOMATIC COVERING INDEX (Aid=?)
> 6000USE TEMP B-TREE FOR ORDER BY
> 
> 
> All the above queries, returns the same result rows in the same order:
> 
> group_concat(B.data)  o1   o2
> NULL   5   300
> f,g3   200
> c,d,e  2   50
> a,b1   100
> 
> 
> 
>
> -- 
> John Found 
> ___
> 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] [SQLite3] [Mono] [Linux] "db is locked"

[Barry Smith]

Some ideas:

Sqlite may return that the database is locked immediately if it detects a 
deadlock situation. Something like: a different connection holds a reserved 
lock (waiting for read connections to close so it can promote to exclusive), 
and the current connection tries to promote from a read lock to a reserved or 
exclusive lock. Busy timeout will never resolve this situation so the 
connection attempting to promote just returns that the database is locked.

You can also get guaranteed timeouts if you use multiple connections on the 
same thread, or if your threading logic causes a deadlock. An example might be:

//...
DataReader dataReader = outerCmd.ExecuteReader()
foreach (var row in dataReader)
{
using (SQLiteConnection innerCon = GetNewConnection())
//attempt to write with inner conn
}

The above might happen in a called function to obscure what is happening. Such 
things might be valid on a different dbms that does table or row level locking, 
but can't be used with SQLite's Db level locking (I got bitten by this when I 
thought 'oh yeah, change from sql server to sqlite? I'll just update the SQL 
and change the providers. The logic will translate fine.')

Finally, I have seen inexplicable timeouts if I mix SQLite and 
TransactionScope. In my case the sqlite connections didn't need to participate 
in the transaction so I fixed it by setting enlist=false on the connection 
string and not investigating further.

> On 4 Jul 2018, at 5:14 am, Simon Slavin  wrote:
> 
>> On 3 Jul 2018, at 8:08pm, Phani Rahul Sivalenka 
>>  wrote:
>> 
>> As per our observation, the initial write operations on the sqlite db file
>> throw "db is locked" error. After a certain time (around an hour) write
>> operations start working and we are able to do all the operations as
>> required.  [...]
> 
>> “data
>> source=/var/www/html/ChargerDatabase.db;DateTimeKind=Utc;Version=3;Pooling=True;Synchronous=Off;journal
>> mode=Memory;Busy Timeout=3;Default Timeout=30”
> 
> Test each of your timeouts by removing one, then the other, then both.  See 
> if the removal of one of them changes the described behaviour.
> 
> Simon.
> ___
> 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] LIMIT

[Barry Smith]

Ryan's way works well. Here is a second method which expresses it in a 
different way:

SELECT * FROM T t1 where rowid IN (SELECT rowid FROM T t2 WHERE t1.F2 = t2.F2 
ORDER BY rowid LIMIT 10)

If you have WITHOUT ROWID tables you'd have to replace rowid with your primary 
key.

(The query may still work without the ORDER BY, I didn't test it, but even if 
it does a future query optimiser might break that because without the order by 
the results of the inner select are free to include a different 10 rowids for 
every value in the outer query)

> On 23 Jun 2018, at 9:50 pm, Gert Van Assche  wrote:
> 
> Hi Ryan, thanks for this. This is working if the F1 field is a numeric
> value. With text (especially Asian & Arabic characters) this does not seem
> to work.
> So I created an MD5 hash from the text fields and it works great! Thank you
> so much.
> 
> gert
> 
> Op vr 22 jun. 2018 om 22:52 schreef R Smith :
> 
>> 
>>> On 2018/06/22 10:04 PM, Gert Van Assche wrote:
>>>  All,
>>> 
>>> I'm sure it must be possible, I just don't find how.
>>> I have a table T with 2 fields (F1 and F2). The F1 are unique and the F2
>>> are not unique.
>>> I would like to get only 10 F1 fields for each unique F2.
>> 
>> This is not normally done, and windowing functions in other RDBMSes
>> makes for an easier way, but it can still be done in SQLite with some
>> creative grouping of a self-joined query.
>> In this example, I limited it to 3 F1 items per unique F2 for brevity,
>> but you can easily change the "< 4" to "< 11" or "<= 10" according to
>> preference.
>> 
>>   -- SQLite version 3.20.1  [ Release: 2017-08-24 ]  on SQLitespeed
>> version 2.0.2.4.
>>   --
>> 
>> 
>> 
>> CREATE TABLE T(F1 INTEGER PRIMARY KEY, F2 TEXT);
>> 
>> INSERT INTO T(F1,F2) VALUES
>>  (1,  'John')
>> ,(2,  'John')
>> ,(3,  'Jason')
>> ,(4,  'John')
>> ,(5,  'Jason')
>> ,(6,  'John')
>> ,(7,  'John')
>> ,(8,  'Jason')
>> ,(9,  'Jason')
>> ,(10,  'Joan')
>> ,(11,  'Joan')
>> ,(12,  'Joan')
>> ,(13,  'Jimmy')
>> ;
>> 
>> SELECT A.F2, B.F1
>>   FROM T AS A
>>   JOIN T AS B ON B.F2 = A.F2 AND B.F1 >= A.F1
>>  GROUP BY A.F2, B.F1
>>  HAVING COUNT(*) < 4
>> ;
>> 
>>   -- F2|  F1
>>   -- - | ---
>>   -- Jason |  3
>>   -- Jason |  5
>>   -- Jason |  8
>>   -- Jimmy |  13
>>   -- Joan  |  10
>>   -- Joan  |  11
>>   -- Joan  |  12
>>   -- John  |  1
>>   -- John  |  2
>>   -- John  |  4
>> 
>> 
>> 
>> -- Another option to note, in case the 10 limit is not important and
>> simply aimed
>> -- at saving space, is to use group concatenation, like so:
>> 
>> SELECT F2, group_concat(F1)AS F1
>>   FROM T
>>  GROUP BY F2
>> ;
>> 
>>   --   |
>>   -- F2|F1
>>   -- - | -
>>   -- Jason |  3,5,8,9
>>   -- Jimmy | 13
>>   -- Joan  |  10,11,12
>>   -- John  | 1,2,4,6,7
>> 
>> 
>>   --
>> 
>> 
>> 
>> Cheers!
>> Ryan
>> 
>> 
>> ___
>> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] probably recursive?

[Barry Smith]

Ah my bad, I misunderstood the initial condition. nX is a function of X. My 
statements were only true if nX=X. Well, sorry about the noise.

> On 2 May 2018, at 8:20 am, Roman Fleysher <roman.fleys...@einstein.yu.edu> 
> wrote:
> 
> Dear Barry,
> 
> The statement about the square is not obvious to me. The requirements on 
> counts in x and y are different.
> 
> I also imagine answer could be two or several non-overlapping  "rectangles". 
> "Rectangles" will not be densely filled with dots, they might have empty 
> spots either because the points were never on the list or were eliminated.
> 
> Roman
> 
> 
> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf 
> of Barry Smith [smith.bar...@gmail.com]
> Sent: Tuesday, May 01, 2018 6:12 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] probably recursive?
> 
> Well those constraints simplify your problem.
> 
> In the resultant dataset, the largest X and Y values will be equal, and the 
> largest X will have and entry for every coordinate from (X, 1) to (X, X). 
> Likewise the largest Y will have an entry for every coordinate from (1, Y) to 
> (Y, Y). Basically you'll have two lines from the axes, drawing a square. All 
> points outside that square will be culled, all points on and inside the 
> square will be kept.
> 
> Since you know that, you now have a one dimensional problem to solve. It 
> still seems a little recursive to me, but it should be easier because you 
> only need to find a single number (which you can then plug into a delete 
> statement).
> 
> If my statement about the square is not obvious to prove in your head I can 
> try write a proof for that but I'm not much good at proofs.
> 
>> On 2 May 2018, at 7:27 am, Roman Fleysher <roman.fleys...@einstein.yu.edu> 
>> wrote:
>> 
>> Pairs (x,y) do not repeat.
>> 
>> Actual x and y are positive integers, but I do not see how being positive 
>> can be relevant. Integer is important for sorting/comparison.
>> 
>> 
>> Roman
>> 
>> 
>> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf 
>> of Barry Smith [smith.bar...@gmail.com]
>> Sent: Tuesday, May 01, 2018 5:23 PM
>> To: SQLite mailing list
>> Subject: Re: [sqlite] probably recursive?
>> 
>> Is there a uniqueness constraint on your initial data? Can the same 
>> coordinate be listed multiple times?
>> 
>> Is there a requirement that X > 0 and Y > 0?
>> 
>>>> On 2 May 2018, at 3:35 am, Simon Slavin <slav...@bigfraud.org> wrote:
>>>> 
>>>> On 1 May 2018, at 6:28pm, Simon Slavin <slav...@bigfraud.org> wrote:
>>>> 
>>>> I just realised that
>>> 
>>> That was intended to be personal email.  Apologies, everyone.
>>> 
>>> Simon.
>>> ___
>>> 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
>> ___
>> 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
> ___
> 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] probably recursive?

[Barry Smith]

In you initial email, what is n? Some real number between zero and one?

> On 2 May 2018, at 8:37 am, Abroży Nieprzełoży 
> <abrozynieprzelozy314...@gmail.com> wrote:
> 
> I think Barry mean that you can represent the (x,y) pair as a single
> number like (max(X)-min(X))*(Y-min(Y))+X-min(X) or so, but I don't see
> how it would be helpful.
> 
> 2018-05-02 0:20 GMT+02:00, Roman Fleysher:
>> Dear Barry,
>> 
>> The statement about the square is not obvious to me. The requirements on
>> counts in x and y are different.
>> 
>> I also imagine answer could be two or several non-overlapping  "rectangles".
>> "Rectangles" will not be densely filled with dots, they might have empty
>> spots either because the points were never on the list or were eliminated.
>> 
>> Roman
>> 
>> ____
>> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf
>> of Barry Smith [smith.bar...@gmail.com]
>> Sent: Tuesday, May 01, 2018 6:12 PM
>> To: SQLite mailing list
>> Subject: Re: [sqlite] probably recursive?
>> 
>> Well those constraints simplify your problem.
>> 
>> In the resultant dataset, the largest X and Y values will be equal, and the
>> largest X will have and entry for every coordinate from (X, 1) to (X, X).
>> Likewise the largest Y will have an entry for every coordinate from (1, Y)
>> to (Y, Y). Basically you'll have two lines from the axes, drawing a square.
>> All points outside that square will be culled, all points on and inside the
>> square will be kept.
>> 
>> Since you know that, you now have a one dimensional problem to solve. It
>> still seems a little recursive to me, but it should be easier because you
>> only need to find a single number (which you can then plug into a delete
>> statement).
>> 
>> If my statement about the square is not obvious to prove in your head I can
>> try write a proof for that but I'm not much good at proofs.
>> 
>>> On 2 May 2018, at 7:27 am, Roman Fleysher
>>> wrote:
>>> 
>>> Pairs (x,y) do not repeat.
>>> 
>>> Actual x and y are positive integers, but I do not see how being positive
>>> can be relevant. Integer is important for sorting/comparison.
>>> 
>>> 
>>> Roman
>>> 
>>> 
>>> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on
>>> behalf of Barry Smith [smith.bar...@gmail.com]
>>> Sent: Tuesday, May 01, 2018 5:23 PM
>>> To: SQLite mailing list
>>> Subject: Re: [sqlite] probably recursive?
>>> 
>>> Is there a uniqueness constraint on your initial data? Can the same
>>> coordinate be listed multiple times?
>>> 
>>> Is there a requirement that X > 0 and Y > 0?
>>> 
>>>>> On 2 May 2018, at 3:35 am, Simon Slavin  wrote:
>>>>> 
>>>>> On 1 May 2018, at 6:28pm, Simon Slavin  wrote:
>>>>> 
>>>>> I just realised that
>>>> 
>>>> That was intended to be personal email.  Apologies, everyone.
>>>> 
>>>> Simon.
>>>> ___
>>>> 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
>>> ___
>>> 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
>> ___
>> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] probably recursive?

[Barry Smith]

Well those constraints simplify your problem.

In the resultant dataset, the largest X and Y values will be equal, and the 
largest X will have and entry for every coordinate from (X, 1) to (X, X). 
Likewise the largest Y will have an entry for every coordinate from (1, Y) to 
(Y, Y). Basically you'll have two lines from the axes, drawing a square. All 
points outside that square will be culled, all points on and inside the square 
will be kept.

Since you know that, you now have a one dimensional problem to solve. It still 
seems a little recursive to me, but it should be easier because you only need 
to find a single number (which you can then plug into a delete statement).

If my statement about the square is not obvious to prove in your head I can try 
write a proof for that but I'm not much good at proofs.

> On 2 May 2018, at 7:27 am, Roman Fleysher <roman.fleys...@einstein.yu.edu> 
> wrote:
> 
> Pairs (x,y) do not repeat.
> 
> Actual x and y are positive integers, but I do not see how being positive can 
> be relevant. Integer is important for sorting/comparison.
> 
> 
> Roman
> 
> 
> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf 
> of Barry Smith [smith.bar...@gmail.com]
> Sent: Tuesday, May 01, 2018 5:23 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] probably recursive?
> 
> Is there a uniqueness constraint on your initial data? Can the same 
> coordinate be listed multiple times?
> 
> Is there a requirement that X > 0 and Y > 0?
> 
>>> On 2 May 2018, at 3:35 am, Simon Slavin <slav...@bigfraud.org> wrote:
>>> 
>>> On 1 May 2018, at 6:28pm, Simon Slavin <slav...@bigfraud.org> wrote:
>>> 
>>> I just realised that
>> 
>> That was intended to be personal email.  Apologies, everyone.
>> 
>> Simon.
>> ___
>> 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
> ___
> 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] probably recursive?

[Barry Smith]

Is there a uniqueness constraint on your initial data? Can the same coordinate 
be listed multiple times?

Is there a requirement that X > 0 and Y > 0?

> On 2 May 2018, at 3:35 am, Simon Slavin  wrote:
> 
>> On 1 May 2018, at 6:28pm, Simon Slavin  wrote:
>> 
>> I just realised that
> 
> That was intended to be personal email.  Apologies, everyone.
> 
> Simon.
> ___
> 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] fsync on -wal still happening

[Barry Smith]

I believe the inotifywait does not actually wait for the fsync operation before 
notifying.

Process A can write to a file, the OS can cache it without flushing to disk, 
and a request by process B will be served directly from the cache. Therefore 
the operating system can notify you of the change before it's written to 
persistent storage.

So, in normal synchronous mode, SQLite doesn't cache its writes, it just waits 
before calling fsync and relies on the OS cache.

Perhaps benchmarking before engaging in such an endeavour as you plan would 
indicate whether the OS cache gives you sufficient performance.

> On 29 Dec 2017, at 12:07 pm, Ian Freeman  wrote:
> 
> Thanks, Simon. Indeed I did see that option. But I'm concerned about
> maintaining integrity in the middle of an power-interrupted commit, so
> I was hoping to leverage WAL's inverted behavior of not modifying the
> database file directly until checkpoint time. The only thing left to
> investigate is how WAL handles power loss during the middle of a
> checkpoint. Normally I believe it's able to recover because the next db
> open has the -wal file to look at. But not if I'm storing it in-memory.
> 
> So I'm thinking I will copy the -wal file to disk before the checkpoint
> op for safety, and then load it back onto ramdisk after. I suppose I
> may have to exit WAL mode in order to perform that copy, making
> checkpointing a very expensive operation for my app.
> 
>>> On Fri, 2017-12-29 at 16:48 +, Simon Slavin wrote:
>>> 
>>> On 29 Dec 2017, at 4:34pm, Ian Freeman  wrote:
>>> 
>>> I see, then what I'm seeing is just normal behavior of the writes
>>> being
>>> flushed to disk. I read what I wanted to hear about
>>> synchronous=NORMAL
>>> delaying writes to the -wal file. Instead I'm going to see if I can
>>> move -wal to a ramdisk and see how that will affect db integrity
>>> with
>>> power losses at in-opportune times.
>> 
>> Did you see
>> 
>> PRAGMA journal_mode = MEMORY
>> 
>> ?
>> 
>> Simon.
>> ___
>> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ADO.NET driver upgrade puzzle

[Barry Smith]

Quite strange that Prepare() makes a difference. The system.data.sqlite 
documentation states that Prepare() does nothing, and a code inspection of the 
system.data.sqlite source shows that it does nothing but check that the command 
hasn't been disposed, and that the connection is still valid (which is done at 
the start of almost every method in the class, and therefore again when you 
call ExecuteNonQuery.)

> On 27 Sep 2017, at 5:07 am, Roberts, Barry (FINTL)  
> wrote:
> 
> Hi,
> 
> I am aware of the sqlite FAQ, and especially the comments regarding file 
> systems. We only ever use NTFS file system, and never have any FAT or FAT32 
> file systems. Given that the old 1.0.80.0 driver is totally stable, and the 
> newer one is not, on the same exact hardware, I do not think the issue is 
> hardware related.
> 
> I enforce that the writer to a specific database can only run 1 at a time, 
> this is done via the C# Interlocked mechanism which has always proved to be 
> very robust. There is a separate logger object instance per database, and 
> within each of those the writing is controlled with the interlock. This 
> allows the application to buffer for longer if the writing to disk encounters 
> a short term slow down.
> 
> As per Clemens suggestion I could replace the use of the connection pool and 
> hold a connection open per database. The code can be structured to do that, 
> because object instances are not shared between database logger instances. 
> However I would have preferred to use the connection pool, because that 
> allows me to open late/close early on my connection usage, which is generally 
> better for maintenance and how the production code is currently strutured. I 
> could restructure the code, but that is not really desirable in production 
> code, simply due to an ADO.NET driver update.
> 
> I have been running my test application for days now without issues. This is 
> configured with pooling on, statement preparation off. If I enable statement 
> preparation the system will fail after an hour or two. So it looks like our 
> production code needs to run the same, and remove the IDbCommand.Prepare() 
> calls. The old driver works well but has lots of lock contention, the new one 
> resolves that issue, but is not as stable in some configurations. It is a 
> trade-off, I need the new driver to improve performance, but will trade off 
> the command prepare calls, to enable driver stability.
> 
> Kind Regards,
> Barry Roberts.
> b.robe...@fugro.com | www.fugro.com
> 
> 
> ___
> 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] ADO.NET driver upgrade puzzle

[Barry Smith]

> On 26 Sep 2017, at 12:14 am, Clemens Ladisch  wrote:
> 
> Roberts, Barry (FINTL) wrote:
>> As per my original post, all C# access code is making extensive use of
>> "using" statements. However we do obviously rely on the connection pool
>> being thread safe, because many threads are writing to different
>> databases (connections) concurrently.
>> 
>> There is no direct relationship between threads and databases. However
>> I do enforce that within a process only one thread can be writing to
>> a specific database (because sqlite does not support parallel writing)
>> at a time.
> 
> How exactly are you enforcing that?
> 
>> public IDbConnection CreateConnection()
>> {
>>var connection = new SQLiteConnection
>>{
>>ConnectionString = m_Builder.ConnectionString
>>};
>> 
>>return connection;
>> }
> 
> I would be tempted to replace that ConnectionString variable with the
> actual connection object, and not using the connection pool.  (Assuming
> that the builder objects are not shared, or properly locked.)
> 
> 
> Regards,
> Clemens
> ___
> 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] Seeking advice on improving batch sql update performance on zipvfs

[Barry Smith]

Are your updates sorted by DS? If your queries are sorted then sequential 
queries are more likely to hit the same db pages while searching the index, 
resulting in higher cache usage and fewer decompression operations. This would 
have less benefit if your 100k DS values of the updates are randomly 
distributed through the 30m available, and more of an effect if they're tightly 
clustered in some range.

If you mainly query by DS, is that the same as saying it is your primary key? 
If so, have you tried to declare it so and try the table WITHOUT ROWID, and 
don't bother with the index? It may help since you wouldn't have to decompress 
both the index pages and the table pages.

> On 8 Sep 2017, at 12:33 pm, Dominique Pellé  wrote:
> 
> Yue Wu  wrote:
> 
>> Hello,
>> As mentioned in the subject, our goal is to improve performance regarding
>> to batch sql updates.
>> The update sql as follow,
>> 
>>> UPDATE ITEM SET FIELD4 =? WHERE DS=?
>> 
>> We run 100,000 updates in a single transaction. The zipvfs version takes
>> about 20 min while uncompressed version takes about 7 min.
> 
> Which compression algorithm do you use with zipvfs?
> Try LZ4, it's is times faster than zlib at compression and
> decompression, at the cost of compressing a bit less:
> 
> https://github.com/lz4/lz4
> 
> Or try zstd, which can compress more than zlib and faster (especially
> on 64-bit architectures), but it's not as fast as LZ4:
> 
> http://facebook.github.io/zstd/
> 
> What is also your SQLite page size?
> Reducing the SQLite page size probably helps to speed up
> updates, since zipvfs compresses by pages.
> 
> Dominique
> ___
> 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] confused getting started

[Barry Smith]

A less LMGTFY-like response:
First off, using SQLite does require that you become familiar with SQL, and a 
bunch of database ideas: normalization for good schema design, unit of work for 
good use of transactions, how to use indexes, others I'm sure I don't know 
about. But those ideas are not specific to SQLite, and are good to think about 
anyway. And any part of SQL or db design you learn will be far easier than 
trying to deal with the same problems in a file format you've written yourself.

If you want to use SQLite in a c/c++ app, your easiest option is to simply 
download the amalgamation and include it in your project. That's two files - 
SQLite.c and SQLite.h. The basic procedure is:
Open the file: sqlite3_open(...);
prepare your statement: sqlite3_prepare_v2(...);
Bind your parameters: sqlite3_bind(...);
In a loop {
 execute the statement: sqlite3_step(...);
 Retrieve data if it's a select: sqlite3_column(...);
}
Finalize or reset the statement (depending if you want to use it again): 
sqlite3_finalise / sqlite3_reset
Close the file: sqlite3_close(...)

If you're using dotNet, you can use NuGet to install system.data.sqlite.core, 
then #using system.data.sqlite you'll find a ado.net database interface which 
you should more or less use like any other ado.net db interface (with the 
advantage that if you ever migrate to another dbms will be easier).

I can't comment on Python, I haven't used that.

I never thought SQLite difficult to start using. In fact, possibly what I 
didn't want to accept at first was exactly how easy it was and thought there 
were things I was missing*.

For all but the simplest use cases I think SQLite is far easier than direct 
file access for the fact that it keeps track of the contents of the file for 
you, you have an extensible self documenting file format (in the form of your 
db schema), and it takes care of most of your robustness concerns by making 
everything ACID.

* there were things I was missing - like getting all the config right, and 
making sure to reset or finalize statements, and making sure to always do any 
file operations on the log file(s) too. But even with these mistakes it still 
worked leagues better than anything I could have written myself, with a 
fraction of the work.

> On 5 Mar 2017, at 9:20 PM, Jens Alfke  wrote:
> 
> 
>> On Mar 5, 2017, at 3:03 AM, NTrewartha T-Online  
>> wrote:
>> 
>> Any examples of a C,C++,C# or Python usage for sqllite.?
> 
> Have you tried searching? I entered “sqlite c example” and “sqlite python 
> example” into Google and got some useful-looking links in the top few hits.
> Also, there are quite a few books about SQLite.
> 
>> I would like sqllite on my raspberry pi 3 after I have gained experience 
>> under Windows 10.
> 
> If it’s not installed already in the Raspbian OS, you should just need to run 
> “sudo apt-get sqlite”. (Possibly “sudo apt-get sqlite_dev” since IIRC the 
> development resources like header files are in separate packages under 
> Debian-based distros.)
> 
>> Judging what the replies to questions, the very new beginners are left a bit 
>> out in the cold.
> 
> No offense intended, but SQLite isn’t an especially beginner-friendly tool. 
> It’s a powerful relational database with a ton of configurable options, and a 
> somewhat tricky C API, not to mention a sophisticated query language that you 
> also need to master to make effective use of it. (However, using it from 
> Python should be somewhat easier, since the API is a bit higher level and you 
> don’t have to worry about things like memory management.)
> 
> If your data storage needs aren’t too complex, there are simpler ways to 
> implement it. For example, in the past I’ve just used a simple data 
> serialization library to read and write the entire data set to disk. It works 
> great when the data is small enough that it fits easily in memory and doesn’t 
> take too long to read or write (say, under 100MB.) This is the equivalent of 
> using a regular battery-powered drill to make some holes, instead of learning 
> how to use a router or end mill :)
> 
> (Also, in general if you’re moving from Windows to literally any other 
> platform, you’ll have to re-learn some of your development processes. Windows 
> does things differently from Unix, which is what everything else is based on.)
> 
> —Jens
> ___
> 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] SQLite.Interop.dll

[Barry Smith]

Oh, I do remember having this issue before.

I think the cause is this: Visual studio attempts to trace which dlls are 
required for each project. Then if project A is dependent on project B, visual 
studio will copy what it thinks is all the required dlls for both projects into 
project A's output directory.

Unfortunately visual studio is only so smart and doesn't realise that the 
SQLite.interop.dll is a required dependency. I believe the NuGet package puts 
instructions to move those dlls to the output directory by means of xcopy. So, 
you have this situation:

Project GlobalSQLite, with reference to SQLite.
Project GeneologyControls, with reference to GlobalSQLite.

Tell visual studio to build and it goes and executes the step where the SQLite 
interop DLL is copied to the output of the GlobalSQLite project. But then it 
builds GeneologyControls and doesn't realise it needs to copy the interop DLLs! 
So finally your program executes in the output directory of the 
GeneologyControls project, but the SQLite.interop.dll files are in the output 
of the other project.

You need to ensure that the SQLite interop dlls are in the final output 
directory (and also included when you distribute your application). You can do 
this manually using windows explorer, or you can put in a pre or post build 
event of your final project to copy the x64 and x86 folders (containing the 
SQLite.interop.dll files) into its output directory. Doing it from Windows 
explorer is easier, but you may forget then if you do a clean build a few years 
down the line you'll run into the problem again.

Perhaps there's a cleaner way to do this. Those were my solutions, though...

> On 23 Feb 2017, at 1:58 PM, Clyde Eisenbeis  wrote:
> 
> ---
> About two years ago, I downloaded and installed SQLite.  I don't
> recall the details, but it was a program that installed SQLite.
> 
> I ended up with files such as EntityFramework.dll,
> EntityFramework.SqlServer.dll, System.Data.SQLite.dll, etc.  This
> required "using System.Data.SQLite".
> 
> ---
> I then created a WPF C# genealogy program ... and a GlobalSQLite.dll
> library (as a WPF Custom Control Library).
> 
> The GlobalSQLite.dll library contains commonly used functions.  For example:
> 
>   boCreateFileAndTables(string stPathFilename,
> List liststTableNames,
> List liststFieldDefinitions)
>   {...}
> 
> ---
> I am working to improve / clean up the original code for WPF C#
> genealogy program.
> 
> When I have SQLite installed on my genealogy program and on my
> GlobalSQLite.dll library, it works fine.
> 
> When I don't have SQLite installed on my genealogy program, I get an
> exception "Unable to load DLL 'SQLite.Interop.dll'".  It occurs in my
> GlobalSQLite.dll library program:
> 
>sqliteConn = new System.Data.SQLite.SQLiteConnection("Data
> Source=" + stPathFilename + ";").
> 
> I do find SQLite.Interop.dll under ...
> GlobalsSQLite\packages\System.Data.SQLite.Core.1.0.101.0\build\net46\x86
> ... and under ... \x64.
> 
> Is there a better place to put SQLite.Interop.dll so it works?
> ___
> 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] SQLite Options

[Barry Smith]

I would use system.data.sqlite in that situation.

But I would also say it depends on what you already have written, and what your 
strengths are. I am under the impression from your first email that you already 
have something written using system.data.sqlite. i.e. Using the class 
System.Data.SQLite.SQLiteConnection to create a connection to the db, then 
using the methods of that to manipulate the db or extract data from it. Have I 
assumed wrong?

If I am wrong, and you have yet to start writing anything, I would still 
recommend using system.data.sqlite. Only if you particularly like LINQ over SQL 
and you are prepared to learn the caveats of the entity framework would I 
recommend that.

Note that if you're using system.data.sqlite you will ultimately produce a few 
dlls that must be distributed together:
 - your custom library, which contains the code you've written
 - System.Data.Sqlite.dll, which contains the wrapper to make an interface to 
access SQLite in a more dotNet friendly manner
 - x64\sqlite.interop.dll
 - x86\sqlite.interop.dll
The last two contain the 'raw' SQLite library (for either 32 or 64 bit systems).

You should not need the other libraries for a simple application. If you find 
that visual studio is placing them in your project's output directory, check if 
they are listed as a reference and try to remove them then recompile.

> On 20 Feb 2017, at 1:05 PM, Clyde Eisenbeis <cte...@gmail.com> wrote:
> 
> Thanks for the clarification.  In my case:
> 
> 1) Speed is not an issue.  Size is not an issue.
> 
> 2) This is a personal use database (genealogy).
> 
> 3) Typically I create .dll's that serve as a library (WPF Custom
> Control Library) ... easy to use for different programs.
> 
> 4) For example, I have an Excel .dll library (uses Excel as a
> database).  When the program runs the first time using this .dll
> library, it creates the Excel file along with multiple sheets.
> 
> 5) I'd like to create a similar .dll for an SQLite library.  The
> program that uses this .dll is a simple WPF program that uses the .dll
> class name to access the functions.
> 
> With this info, which option would you recommend?
> 
>> On Sun, Feb 19, 2017 at 9:45 PM, Barry Smith <smith.bar...@gmail.com> wrote:
>> Strange, I replied to this earlier... Perhaps my messages are not getting 
>> through.
>> 
>> You cannot include a .c file for compilation in a c# project. You'd have to 
>> do use a separate DLL and do some pinvoke stuff to get to the raw SQLite 
>> interface, but in my opinion you're better off using the system.data.sqlite 
>> wrapper. If you need the speed and power of the raw interface, you probably 
>> need to drop out of an interpreted and managed language (c#) too...
>> 
>> You don't need the entity framework (EF) to run system.data.sqlite. That is 
>> an object relational mapper (ORM) that uses a lot of fancy reflection to 
>> make data access a little easier* (until you get stung by it) and a lot 
>> slower. EF is developed my Microsoft, although SQLite must provide some 
>> input to make it work with its syntax. You should be able to remove the 
>> entity framework dependencies from your project and still compile with no 
>> issues. Try a complete rebuild / clean compile to try get rid of the 
>> unnecessary dlls.
>> 
>> *whether an ORM actually makes data access easier is debatable, they 
>> basically allow you to write your data access queries in LINQ rather than 
>> SQL, and automatically instansiate c# objects for each line in the results. 
>> I find SQL easier...
>> 
>>> On 19 Feb 2017, at 1:50 PM, Clyde Eisenbeis <cte...@gmail.com> wrote:
>>> 
>>> Sorry for the slow response.
>>> 
>>> My code is in C#.  I don't know if the amalgamation source code in C
>>> can be compiled so it is compatible with C#.
>>> 
>>> If it can, I'd be interested in details.  Thanks!
>>> 
>>>> On Sat, Feb 18, 2017 at 1:29 AM, R Smith <rsm...@rsweb.co.za> wrote:
>>>> 
>>>> 
>>>>>> On 2017/02/18 12:45 AM, Warren Young wrote:
>>>>>> 
>>>>>> On Feb 17, 2017, at 7:32 AM, R Smith <rsm...@rsweb.co.za> wrote:
>>>>>> 
>>>>>> You can even checkout the latest commits via SVN
>>>>> 
>>>>> There’s a Subversion mirror of the official Fossil code repository for
>>>>> SQLite?
>>>> 
>>>> 
>>>> Apologies, force of habit nomenclature. Have fallen to calling any Software
>>>> Versioning system just 'SVN' for short. I did of course mean for it to be
>>>> checked out via Fossil.

Re: [sqlite] SQLite Options

[Barry Smith]

Strange, I replied to this earlier... Perhaps my messages are not getting 
through.

You cannot include a .c file for compilation in a c# project. You'd have to do 
use a separate DLL and do some pinvoke stuff to get to the raw SQLite 
interface, but in my opinion you're better off using the system.data.sqlite 
wrapper. If you need the speed and power of the raw interface, you probably 
need to drop out of an interpreted and managed language (c#) too...

You don't need the entity framework (EF) to run system.data.sqlite. That is an 
object relational mapper (ORM) that uses a lot of fancy reflection to make data 
access a little easier* (until you get stung by it) and a lot slower. EF is 
developed my Microsoft, although SQLite must provide some input to make it work 
with its syntax. You should be able to remove the entity framework dependencies 
from your project and still compile with no issues. Try a complete rebuild / 
clean compile to try get rid of the unnecessary dlls.

*whether an ORM actually makes data access easier is debatable, they basically 
allow you to write your data access queries in LINQ rather than SQL, and 
automatically instansiate c# objects for each line in the results. I find SQL 
easier...

> On 19 Feb 2017, at 1:50 PM, Clyde Eisenbeis  wrote:
> 
> Sorry for the slow response.
> 
> My code is in C#.  I don't know if the amalgamation source code in C
> can be compiled so it is compatible with C#.
> 
> If it can, I'd be interested in details.  Thanks!
> 
>> On Sat, Feb 18, 2017 at 1:29 AM, R Smith  wrote:
>> 
>> 
>>> On 2017/02/18 12:45 AM, Warren Young wrote:
>>> 
 On Feb 17, 2017, at 7:32 AM, R Smith  wrote:
 
 You can even checkout the latest commits via SVN
>>> 
>>> There’s a Subversion mirror of the official Fossil code repository for
>>> SQLite?
>> 
>> 
>> Apologies, force of habit nomenclature. Have fallen to calling any Software
>> Versioning system just 'SVN' for short. I did of course mean for it to be
>> checked out via Fossil.
>> 
>>> https://goo.gl/KzLcV8
>>> 
>>> (Excuse the shortener, it’s a raly long URL.)
>>> 
>>> I could give you that Zip file link, but I suspect it’s purposely not
>>> being published to avoid load on the SQLite repository server caused by bots
>>> repeatedly requesting Zip files and tarballs.
>> 
>> 
>> The bots can read goo links nowadays. ;)
>> 
>>> Using Fossil is far more efficient than downloading Zip archives, but as I
>>> keep getting reminded in my own Fossil-hosted public project, some people
>>> just refuse to install and use anything they don’t absolutely have to.  It’s
>>> six easy steps, but apparently that’s too many for some.
>> 
>> 
>> Agreed, and what is more sad is that Fossil is so much better at actual
>> "Version-Control" (as opposed to making sharing code easiest). If we could
>> get the rest of the World to rather Fossil, everybody wins. (I can already
>> hear Linus clutching his chest and breathing erratically!)
>> 
>> 
>> 
>> ___
>> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Options

[Barry Smith]

System.Data.SQLite is the package you want if you just want a .Net style (i.e. 
Using the standard .net db interfaces) wrapper around SQLite. You can find it 
on NuGet.

The entity framework is a library that maps database entries and relations to 
OOP style objects. Look up object relational mapping (ORM). It's a bit of a 
monster that uses a lot of reflection. It can make some tasks easier, but it's 
also very easy to get stung by it. I would not recommend it for any time you 
need performance, or to deal with even moderate record counts. Although the 
entity framework is compatible with SQLite and system.data.sqlite, it is not 
specific to this dbms - it's a data access layer developed by Microsoft for 
general db access. You do not need it to use SQLite.

Have you tried to remove the reference to the entity framework then performed a 
clean build?

Ps this list strips attachments, so I can't see exactly what you've highlighted.

> On 16 Feb 2017, at 5:40 PM, Clyde Eisenbeis  wrote:
> 
> I started writing SQLite code about two years ago (Visual Studio 2013,
> C#, WPF) ... with a significant delay, since then, because of a
> physical move.
> 
> The code is written for a specific use on my computer ... no other users.
> 
> SQLite was chosen so my sons could eventually install this program on
> their computer ... no database needs to be installed ... no other
> installation required.
> 
> I don't recall the actions taken then, but I do see quite a few
> additional files (EntityFramework.dll, EntityFramework.SqlServer.dll,
> etc.) as references ... see attachment.
> 
> Is there an SQLite version that is comprised of fewer dlls, etc.? ...
> Perhaps SQLite3?
> 
> Clyde
> ___
> 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] Why is this so much more efficient?

[Barry Smith]

Aliases in SQL are not the same as variables in most procedural languages. So 
every time you mention 'totaltime' SQLite is probably recalculating that value 
by adding all the columns together.  See the various discussions regarding no 
deterministic (random) functions last year. Less references to that alias => 
less calculations. Reference it twice and expect it to be slower.

> On 17 Feb 2017, at 12:27 AM, Darko Volaric  wrote:
> 
> You can actually index functions or expression:
> https://www.sqlite.org/expridx.html
> 
> On Thu, Feb 16, 2017 at 9:32 PM, Cecil Westerhof 
> wrote:
> 
>> 2017-02-16 21:10 GMT+01:00 Dominique Pellé :
>> 
>>> Cecil Westerhof  wrote:
>>> 
 I have a table vmstat that I use to store vmstat info. ;-)
 At the moment it has more as 661 thousand records.
 
 In principle the values of usertime, systemtime, idletime, waittime and
 stolentime should add up to 100. I just wanted to check it. Of-course
>>> there
 could be a rounding error, so I wrote the following query:
 SELECT date
 ,  time
 ,  usertime
 ,  systemtime
 ,  idletime
 ,  waittime
 ,  stolentime
 ,  (usertime + systemtime + idletime + waittime + stolentime) AS
 totaltime
 FROM   vmstat
 WHERE  totaltime  < 99 OR totaltime > 101
 
 I did not like that, so I rewrote the WHERE to:
 WHERE  ABS(100 - totaltime) > 1
 
 The funny thing the second WHERE is more efficient as the first, where
>> I
 would have expected it to be the other way around.
 The first takes around 1.050 milliseconds.
 The second takes around  950 milliseconds.
 So the second is around 10% more efficient. Why is this?
 
 In case it is important: I did this in sqlitebrowser 3.7.0, which uses
 SQLite 3.8.10.2.
>>> 
>>> 
>>> I suspect  that when you use "WHERE ABS(100 - totaltime) > 1"
>>> SQLite cannot use an index since it's an expression, so it does a
>>> full table scan, whereas the other solution which does
>>> "WHERE  totaltime  < 99 OR totaltime > 101"
>>> may use an index on totaltime (assuming that there is an index).
>>> 
>>> In general using an index is good.  But if most of the records
>>> satisfy the condition "ABS(100 - totaltime) > 1" then an index
>>> can be more harmful than useful.   And that could explain
>>> why using "WHERE ABS(100 - totaltime) > 1" is a bit faster.
>>> You could try "EXPLAIN QUERY PLAN" on your queries to
>>> see if they use an index or if they do a full table scan.
>> 
>> ​Totaltime is calculated, so it cannot have an index. ;-)
>> Besides from the almost 700.000 records only two satisfy the condition.
>> 
>> I should look into EXPLAIN QUERY PLAN.
>> 
>> --
>> Cecil Westerhof
>> ___
>> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with Backup API please

[Barry Smith]

Hi Brett,

I believe SQLite doesn't use the standard memory allocation routines, but 
instead has its own routines. These (might) use global variables. If each 
module of your application statically links to the SQLite source rather than 
having SQLite in a common DLL, then each module will have its own set of global 
variables. As such, passing raw SQLite pointers across module boundaries can 
result in undefined behaviour. I suspect other issues could also occur with 
other global variables in SQLite.

Note that passing the raw pointer is not the real culprit, but rather having 
different modules attempt to work on the pointer. For example:, the following 
should work fine:
sqlite3* pDb = DLL1GetDb();
DLL1DoSomething(pDb);

But either of the following could fail:
sqlite3* pDb = DLL1GetDb();
DLL2DoSomething(pDb);
// or, if the calling module is not DLL1
sqlite3_exec(pDb,...);

Personally, to enforce this I do not expose (either in arguments or return 
values) raw sqlite objects (dbs, statements, or anything else) in exported 
functions.

If you already have SQLite in a common DLL, or you perform all sqlite3_* calls 
from the same module, I apologise for wasting your time.

Also, SQLite is written in C so it uses error return codes rather than throwing 
exceptions. The exception you are catching would have to be thrown by another 
library that SQLite is calling. This would mean that the output of 
sqlite3_errmsg is likely unrelated to the exception since SQLite would never 
have intercepted it. Perhaps your debugging environment could tell you more 
details of the real proximate cause of the error (unless the you mean that the 
error message of the exception, by coincidence, was the same as a standard 
SQLite error message)

Cheers,

 - Barry 

> On 8 Feb 2017, at 9:30 AM, Brett Goodman  wrote:
> 
> Hello Sqlite users.  I have a problem I can't solve.  I have a C++ DLL 
> project in which I compile the Sqlite amalgamation code. The purpose is to 
> wrap the key Sqlite functions with exported functions I can call from another 
> C++ COM DLL.   I'm using VS2003 for this because its part of a legacy 
> product.  I've been using this project for years, updating the Sqlite source 
> code from time to time with no problems.  I can call functions like 
> sqlite3_open_v2, sqlite3_prepare, sqlite3_step, etc. with no problems.  Now 
> I'm trying to integrate the Backup API.  When I call sqlite3_backup_init it 
> throws this error: _/"library /__/
> /__/routine called out of sequence"/_.  I want to step into the Sqlite code 
> to find out why but for some reason my debugger won't allow me to step into 
> the C code from my C++ code.  So I'm hoping someone can help me.  Below is my 
> C++ implementation which I modeled from the sample on this page: 
> sqlite.org/backup.html.  My calling code is from a VB6 project which simply 
> creates/opens a memory DB and calls the below function (via my COM DLL).  Any 
> help is much appreciated.
> 
> Thanks,
> BrettG
> 
> 
> SQLITE_DLL_API int backup( /*sqlite3*/ void* pdb, const WCHAR *pFilename, 
> BOOL toFile )
> {
>  int rc;
>  sqlite3 *pFileDb;
>  sqlite3_backup *pBackup;
>  sqlite3 *pTo;
>  sqlite3 *pFrom;
> 
>  CTextConverter c(CP_UTF8);
>  //rc = sqlite3_open_v2( c.U2M(pFilename), , SQLITE_OPEN_READWRITE, 
> NULL);
>  rc = sqlite3_open( c.U2M(pFilename), );
> 
>  if( rc==SQLITE_OK )
>  {
>try
>{
>sqlite3 *pMemoryDb = (sqlite3*) pdb;
>pFrom = (toFile ? pMemoryDb : pFileDb);
>pTo   = (toFile ? pFileDb : pMemoryDb);
> 
>// this line throws exception:  "library routine called out of 
> sequence"
> *pBackup = sqlite3_backup_init(pTo, "main", pFrom, "temp");*
>if( pBackup )
>{
>sqlite3_backup_step(pBackup, -1);
>sqlite3_backup_finish(pBackup);
>}
>rc = sqlite3_errcode(pTo);
>sqlite3_close(pFileDb);
>}
>catch(...)
>{
>sqlite3_close(pFileDb);
>const char* err = sqlite3_errmsg( (sqlite3*) pdb );
>throw err;
>}
>  }
>  return rc;
> }
> 
> ___
> 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] Datatype for prices (1,500)

[Barry Smith]

Nice.

Say goodbye to transitive equality though.

> On 3 Dec 2016, at 5:02 AM, Keith Medcalf  wrote:
> 
> 
> feq (and friends) are an extension I wrote that does proper floating point 
> comparisons:
> 
> /*
> ** 2015-11-09
> **
> ** The author disclaims copyright to this source code.  In place of
> ** a legal notice, here is a blessing:
> **
> **May you do good and not evil.
> **May you find forgiveness for yourself and forgive others.
> **May you share freely, never taking more than you give.
> **
> **
> **
> ** This SQLite extension implements functions for comparison of floating point
> ** double precision numbers x and y where x is within a specified number of
> ** ULPs of y.
> **
> ** The default number of ULPs if not specified is 5.
> ** The size of an ULP is based on the scale of x.
> ** If only one value is specified it will be a y value and x will be 0.0
> **
> **   ulp(x) -> ULP precision of x (increment to next representable number)
> **   epsilon(x) -> ULP precision of x (alias of ulp)
> **   ulps(x, y) -> number of ULP of x in (x - y)
> **
> **   flt(x[, y[, u]])   -> x less than y
> **   fle(x[, y[, u]])   -> x less or equal y
> **   feq(x[, y[, u]])   -> x equal y
> **   fge(x[, y[, u]])   -> x greater or equal y
> **   fgt(x[, y[, u]])   -> x greater than y
> **   fne(x[, y[, u]])   -> x not equal y
> **
> */
> #ifdef __cplusplus
> extern "C" {
> #endif
> 
> /*
> ** If the MATH.H is already included, use the math library
> ** otherwise make sure memcpy is defined
> */
> 
> #ifndef _MATH_H_
> #include 
> #endif
> 
> #ifndef SQLITE_PRIVATE
>#define SQLITE_PRIVATE static
> #endif
> 
> #ifdef SQLITE_CORE
>#include "sqlite3.h"
> #else
>#ifdef _HAVE_SQLITE_CONFIG_H
>#include "config.h"
>#endif
>#include "sqlite3ext.h"
>SQLITE_EXTENSION_INIT1
> #endif
> 
> /*
> ** Common Functions
> */
> 
> #ifdef _MATH_H_
> 
> /*
> ** If we have the math library then use it
> */
> 
> static double epsilon(double value)
> {
>int exponent;
>double mantissa = frexp(value, );
>return ldexp(1.0, exponent - 53);
> }
> #define sqlfcmp_fabs fabs
> 
> #else
> 
> /*
> ** If we do not have the math library already, do not force it to be included
> */
> 
> static double epsilon(double value)
> {
>sqlite_int64 a;
>double r = value;
>if (r < 0)
>r = -r;
>memcpy(, , sizeof(a));
>if (a == 0)
>a = (970LL << 52);
>else
>a = ((a >> 52) - 52LL) << 52;
>memcpy(, , sizeof(a));
>return r;
> }
> 
> static double sqlfcmp_fabs(double v)
> {
>if (v < 0)
>return -v;
>return v;
> }
> #endif
> 
> static double distance(double x, double y)
> {
>return (x - y) / epsilon(x);
> }
> 
> /*
> ** Return the distance to the next representable number
> */
> 
> SQLITE_PRIVATE void _ulp(sqlite3_context *context, int argc, sqlite3_value 
> **argv)
> {
>sqlite3_result_double(context, epsilon(sqlite3_value_double(argv[0])));
> }
> 
> /*
> ** Return the number of representable numbers between x and y based on the 
> ULP of x
> ** if only one parameter, it is y and x is 0.0
> */
> 
> SQLITE_PRIVATE void _ulps(sqlite3_context *context, int argc, sqlite3_value 
> **argv)
> {
>double x = 0.0;
>double y = 0.0;
>uintptr_t flag = (uintptr_t)sqlite3_user_data(context);
>if ((argc == 0) || (argc > 2))
>return;
>if (argc == 1)
>y = sqlite3_value_double(argv[0]);
>else
>{
>x = sqlite3_value_double(argv[0]);
>y = sqlite3_value_double(argv[1]);
>}
>sqlite3_result_double(context, distance(x, y));
> }
> 
> /*
> ** Perform Floating Point (Double) Comparisons using ULP of first parameter
> **   first parameter will be 0.0 if only one parameter specified, which will 
> be y
> ** User Context defines operations
> **  flag == 0   x != y
> **  flag & 1x < y
> **  flag & 2x = y
> **  flag & 4x > y
> ** Flag values are additive
> **  flag & 3x <= y
> **  flag & 6x >= y
> ** optional third parameter is ULPs of x to consider equal, defaults to 5
> ** if only one parameter then test if x within 5 ULPs of 0.0
> */
> 
> SQLITE_PRIVATE void _fpc(sqlite3_context *context, int argc, sqlite3_value 
> **argv)
> {
>double x = 0.0;
>double y = 0.0;
>double ulps = 0.0;
>double delta = 5.0;
>uintptr_t flag = (uintptr_t)sqlite3_user_data(context);
>if ((argc == 0) || (argc > 3))
>return;
>if (argc == 1)
>y = sqlite3_value_double(argv[0]);
>else
>{
>x = sqlite3_value_double(argv[0]);
>y = sqlite3_value_double(argv[1]);
>}
>if (argc > 2)
>delta = sqlfcmp_fabs(sqlite3_value_double(argv[2]));
>ulps = distance(x, y);
>if (   ((flag & 2)  && (sqlfcmp_fabs(ulps) <= delta))   /* flag & 2 means 
> test equal */
>|| ((flag & 1)  && (ulps < -delta))   

[sqlite] I don't understand how to use NOT EXISTS

[Barry Smith]

If you have a unique index on name, you could use INSERT OR IGNORE. 
https://www.sqlite.org/lang_conflict.html

INSERT OR IGNORE INTO TAGS (NAME, COUNT) VALUES ('Bleh', 1)

As for your original query: think about just the select clause (you can run it 
independently). This will return ('magnetohydrodynamics', 1) for each row in 
the table. 

If you did not want to use INSERT OR IGNORE, you could put a LIMIT 1 in there, 
or rephrase your query to not use TAGS in the outer select (note: you never 
actually reference anything in the TAGS specified in the outer select)

> On 15 Sep 2015, at 1:06 pm, Nicolas J?ger  
> wrote:
> 
> hi,
> I have a table TAGS with idkey and two colums (NAME, COUNT):
> 
> id|NAME|COUNT
> 53|black metal|3
> 55|occult rock|3
> 61|satanic smurfs|1
> 62|beer|0
> 63|pizza|0
> 
> I want to check if some tag exist by checking if `NAME` is recorded
> in the table or not. If not, I want to add it;
> 
> INSERT INTO TAGS ( NAME, COUNT ) SELECT 'magnetohydrodynamics', 1
> FROM TAGS WHERE NOT EXISTS (SELECT * FROM TAGS WHERE NAME =
> 'magnetohydrodynamics' );
> 
> then if I look up in the table I see:
> 
> id|NAME|COUNT
> 53|black metal|3
> 55|occult rock|3
> 61|satanic smurfs|1
> 62|beer|0
> 63|pizza|0
> 64|magnetohydrodynamics|1
> 65|magnetohydrodynamics|1
> 66|magnetohydrodynamics|1
> 67|magnetohydrodynamics|1
> 68|magnetohydrodynamics|1
> 
> could you tell me please where I did some mess ?
> 
> regards,
> Nicolas J.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] design problem involving trigger

[Barry Smith]

Could this not be achieved by two indexes: one partial and one complete?

CREATE UNIQUE INDEX idx_books1 ON Books(title, author);

CREATE UNIQUE INDEX idx_books2 ON Books(title) WHERE author ISNULL;

To save space and (maybe) time, you could put a 'WHERE author NOTNULL' on the 
first index.

Of course, I'm just talking about how to code it, the issues mentioned by R 
Smith is a different kettle of fish.

Cheers,

Barry


> On 23 Aug 2015, at 3:05 am, "R.Smith"  wrote:
> 
> 
> 
>> On 2015-08-21 11:23 PM, Will Parsons wrote:
>>> On 21 Aug 2015, R.Smith wrote:
>>> 
 On 2015-08-21 04:47 AM, Will Parsons wrote:
 I'm working on a program that involves a catalogue of books.  Part of
 the database schema looks like this:
 
 create table Books(id integer primary key,
 title text collate nocase not null,
 author references Authors(id),
 ...
 unique(title, author));
 
 create table Authors(id integer primary key,
   name text unique not null check(name <> ''));
 
 The idea here is that the title+author of a book must be unique, but a
 book may not necessarily have an author associated with it.  But, the
 schema fragment as I have presented it does not disallow entering the
 same title with a null author multiple times, which is clearly
 undesirable.
 
 In thinking about how to avoid this, one possibility that has occurred
 to me is to add an entry to the Authors table with id=0, name=NULL, and
 to modify the Books table to be:
 
 create table Books(id integer primary key,
 title text collate nocase not null,
 author references Authors(id) not null,
 ...
 unique(title, author));
 
 With this, entries in the Books table that presently have the author
 field set to NUll would instead have author=0.
 
 What I would like to have is a trigger that when an attempt is made to
 enter a new record into the Books table with a NULL author field, is
 to force the author field to 0 instead.  I can't see how to do this
 with a "before insert" trigger, though.  Perhaps I'm approaching this
 the wrong way; in any case I'd appreciate advice.
>>> Nothing wrong with your approach, simply declare the Author to be NOT
>>> NULL and if it is possible to have non-Authors you could do what you
>>> suggest, add a 0-Author to the parent table, or, you could use a partial
>>> Index (available after SQLite 3.7 only I think) which would allow you to
>>> have NULLS but still maintain a strict relation, something like this:
>>> 
>>> create table Books(id integer primary key,
>>> title text collate nocase not null,
>>> author int not null references Authors(id),
>>> ...
>>> );
>>> 
>>> create unique index uBookAuth on Books(title,author) where author is not 
>>> null;
>>> 
>>> create table Authors(id integer primary key,
>>>   name text unique not null check(name <> ''));
>> I guess I don't understand how having a partial index where author is
>> not null prevents adding two entries with the same title and a null
>> author.  How is this different from what I have now with the
>> "unique(title, author)" specification?
> 
> I think I may have misinterpreted slightly...
> 
> To clarify: are the book titles unique or are they not?
> 
> If they are Unique, i.e if no book title can ever appear twice, regardless 
> who authored it, then simply declare a unique index on title.
> If they are NOT Unique, i.e. if a title can appear more than once, then why 
> is it not working for you?
> 
> Essentially, if I interpret correctly, you are asking the DB to NOT limit the 
> number of same-titled books, except when you have supplied an author and the 
> same author has already such a titled book, but then when you don't supply an 
> author, it should know to now also limit the copies of  no-author books?
> 
> I assumed before that you only added NULL for author if you don't know the 
> author yet (which would make sense and can later be updated) but then you 
> can't force the unique constraint, there may be many books with coinciding 
> titles and not-yet-known authors.
> 
> Reading again, I am now thinking that's not the case, you might add NULL 
> authors to books which simply don't have authors (well, all books have 
> authors, but the author might be unknown and accepted to be of unknown status 
> for time to come), in which case, there might be many same-titled 
> unknown-author books.
> 
> If this is the case and you still want to limit unknown author books to just 
> 1 instance, I would suggest to use an explicit author name, maybe something 
> like "(by Unknown)" which would be happily subdued by the Unique constraint 
> if violated. 

[sqlite] Under what circumstances can a table be locked when the database is first opened?

[Barry Smith]

Hi,

Unless you are using shared cache, SQLite does not lock on a per table level - 
only it locks the entire database.

Under what circumstances are you trying to access the database both times? Are 
these multiple connections within the same process or are you shutting down the 
process and then restarting? Do you have shared cached enabled?

Process Explorer is a windows tool that will tell you which process has open 
handles on a particular file. I found it a right pita to use though.

Cheers,

Barry

> On 23 May 2015, at 1:42 pm, "Keith Medcalf"  wrote:
> 
> 
> 1)  Something else has the database open and locked.
> 2)  You are using Shared Cache 
> 3)  Something forgot to finalize a select
> 4)  The database is stored on a non-locally-attached filesystem
> 5)  An issue in the version of SQLite you are using (and you did not say 
> which version you are using)
> 6)  A buggy filesystem driver (you did not say which one you are using)
> 7)  Badly designed antivirus software
> 8)  Badly designed file syncronization software (for example, storing the 
> database in a directory that is being monitored and synced by badly designed 
> software (dropbox for example)).
> 
>> -Original Message-
>> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
>> bounces at mailinglists.sqlite.org] On Behalf Of david at andl.org
>> Sent: Friday, 22 May, 2015 21:13
>> To: 'General Discussion of SQLite Database'
>> Subject: [sqlite] Under what circumstances can a table be locked when the
>> database is first opened?
>> 
>> Question: Under what circumstances can a table be locked when the database
>> is first opened?
>> 
>> My program does:
>> 
>> DROP TABLE IF EXISTS
>> CREATE TABLE
>> INSERT INTO (multiple times)
>> SELECT * (for each row)
>> 
>> Run it once and it works perfectly. Run it twice and the DROP TABLE
>> triggers
>> the error:
>> 
>> SQLITE_LOCKED, database table is locked
>> 
>> Delete the database and run it again and it works. Just once.
>> 
>> Nothing in the documentation tells me how a table can be locked when the
>> database is first opened. I'm using the raw C interface on Windows, so
>> what
>> can I be doing wrong?
>> 
>> The code is actually written in C#, but uses Interop to call the C API
>> directly. The database open code looks like this. No open flags are used.
>> 
>> LastResult = (Result)sqlite3_open(path, out _dbhandle);
>> 
>> Regards
>> David M Bennett FACS
>> 
>> Andl - A New Data Language - andl.org
>> 
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users