[sqlite] Releasing a read (SHARED) lock

2015-03-12 Thread Barry
on disk? If this is not the intended behaviour - is there a way I can find out which statements are causing the lock to be held open? Or can I force SQLite to discard its cache? Any help would be appreciated. Regards, Barry Smith

[sqlite] Releasing a read (SHARED) lock

2015-03-13 Thread Barry
Hi Dinu, Am I correct in my understanding then that it is not enough to call sqlite3_reset, I must call sqlite3_finalize after I am done with a statement? Cheers, - Barry > On Mar 12, 2015 11:40 AM, "R.Smith" wrote: > > > > > > > On 2015-03-12 04:38 AM, Barr

[sqlite] Releasing a read (SHARED) lock

2015-03-13 Thread Barry
irst step() > actually) > > > On 13.03.2015 02:44, Simon Slavin wrote: > >> On 13 Mar 2015, at 12:17am, Barry wrote: >> >> On 13 March 2015 at 01:21, Dinu Marina wrote: >>> >>> You should be also aware of a more common pitfall: unclosed result sets. &

[sqlite] BEGINNER - Transactions in shell script

2015-09-07 Thread Barry
SQL commands do not need to be on multiple lines (they only need a semicolon after each command). But dot commands do. Have you tried the following? (ECHO .bail on ECHO %multiple commands%) | sqlite3.exe %dbname% But, much more importantly (particularly since you included BEGINNER in big letters

[sqlite] Avoid duplicate sets with join table

2016-04-20 Thread Barry
Would not the following snippet be useful to return the record ID if the feature set already exists? SELECT rec.id FROM records rec WHERE 1 = (SELECT SUM(id_f IN (21, 22, 23)) = 3 AND COUNT(*) = 3 FROM records_features rf WHERE rf.id_r = rec.id) You would have to have some logic in your calling

Re: [sqlite] Help with Backup API please

2017-02-08 Thread Barry
to free(...)) was the fact that Brett is encountering an exception rather than an invalid return code. On 8 February 2017 at 14:16, Richard Hipp <d...@sqlite.org> wrote: > On 2/8/17, Barry Smith <smith.bar...@gmail.com> wrote: > > > > I believe SQLite doesn't use the

Re: [sqlite] SQLite Options

2017-02-21 Thread Barry
On 21 February 2017 at 13:48, Clyde Eisenbeis <cte...@gmail.com> wrote: > Thanks for the good info! > > I can't find SQLite.Interop.dll ... is referenced at > https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki, > but don't see a download. > > On Mon, F

Re: [sqlite] SQLite3 on Windows

2017-10-10 Thread Barry
Perhaps I misunderestimate your question, but I think a certain amount of explanation of how SQLite works may be in order. What are you trying to do? The SQLite shell tool does not depend on the DLL (the SQLite library is instead included in the executable file). You can put sqlite3.exe wherever

[sqlite] PRAGMA writable_schema and schema_version: changes are ignored on the same connection

2018-06-04 Thread Barry
This started off as a bug report about the writable schema method recommended in https://www.sqlite.org/lang_altertable.html, but I realised the problem is a bit broader. It seems SQLite is ignoring manual changes to sqlite_master when combined with increments to schema_version. The alter table

Re: [sqlite] LIMIT

2018-06-23 Thread Barry
had returned all possible values, then stalled for a long time as the DB engine processed all rows that could never return anything. Cheers, - Barry [*I suspected that most of the time with the rowid query was taken in writing to the console, so I SELECT'd COUNT(*) instead of * and it returned

Re: [sqlite] SELECT becomes very slow when converted to UPDATE

2018-06-23 Thread Barry
Do I correctly understand the intention of the UPDATE is that for each my_id in meta_table, it will store the count of all the hashes that are associated only with my_id and no other id's? In that case, have you tried: UPDATE meta_table SET distinct_hashes = ( SELECT

[sqlite] Fwd: PRAGMA writable_schema and schema_version: changes are ignored on the same connection

2018-06-20 Thread Barry
Trying again since this didn't go through the first time... This started off as a bug report about the writable schema method recommended in https://www.sqlite.org/lang_altertable.html, but I realised the problem is a bit broader. It seems SQLite is ignoring manual changes to sqlite_master when

Re: [sqlite] An issue with System.Data.SQLite

2017-12-22 Thread Barry
Do you allow the entity framework to create your database, or do you do it yourself? You need text affinity on the columns in question. Then any numbers will be stored as text, not numbers. See: https://sqlite.org/datatype3.html Particularly: 3.1. Determination Of Column Affinity The affinity

Re: [sqlite] Connection open to nonexistent database succeeds! WTF?

2018-03-31 Thread Barry
It looks like you're using C# and System.Data.SQLite. If that's the case, use the connection string parameter FailIfMissing=True to throw an exception if the file does not exist. On 1 April 2018 at 02:34, Jens Alfke wrote: > > > On Mar 31, 2018, at 8:17 AM, Mike Clark

Re: [sqlite] Doing math in sqlite

2018-12-20 Thread Barry
Thu 20 Dec 2018 6:26 PM, Barry Smith wrote: > > > > > > > On 20 Dec 2018, at 4:21 pm, Jungle Boogie > wrote: > > > > > > Hi All, > > > > > > Some sample data: > > > 2018/04/15,foo,170644 > > > 2018/04/15,bar.69625 > &

Re: [sqlite] Support for System.Data.SQLite: Different API type for int/integer columns

2019-01-28 Thread Barry
I use Convert.ToInt32 (or whatever else is appropriate) instead of direct casts when reading from System.Data.SQLite... I use a very similar schema in an SQLServer and SQLite database, and Convert seems to handle many of the differences without requiring different code for each database engine.

Re: [sqlite] Is this a bug?

2019-06-18 Thread Barry
Your nested select statement wants to return many rows. Technically I think this is illegal SQL (?), but it looks like SQLite is being lax about this as usual and doing what it normally does in this sort of situation: picks a row at random from the inner select. Your statement is more or less

Re: [sqlite] Talos Security Advisory for Sqlite3 (TALOS-2019-0777)

2019-05-08 Thread Barry
Dr. Hipp, you're sending your replies to the mailing list as well as your intended recipient. Not sure if this is intended? On Wed, 8 May 2019 at 08:02, Richard Hipp wrote: > On 5/8/19, Richard Hipp wrote: > > On 5/8/19, Regina Wilson (regiwils) wrote: > >> > >> Here’s a copy of the report. >

Re: [sqlite] Safe saving of in-memory db to disk file

2019-07-15 Thread Barry
If you use the SQLite backup api to 'backup' (save) the in-memory database to the disk database, an atomic transaction is used to write the data to the destination database. This means that all of SQLites usual guarantees apply: If the operation completes, the disk database will contain a

Re: [sqlite] System.Data.SQLite.EF6 feature request: Timespan support

2019-08-19 Thread Barry
be doing the TimeSpan <-> (Number or string or however you want to store it in the database) conversion. Cheers, - Barry On Mon, 19 Aug 2019 at 16:40, Simon Slavin wrote: > On 20 Aug 2019, at 12:19am, Barry wrote: > > > Would it be possible for a future version of SQLite t

[sqlite] System.Data.SQLite.EF6 feature request: Timespan support

2019-08-19 Thread Barry
that schema. Would it be possible for a future version of SQLite to support TimeSpan mapping in the entity framework? Thanks in advance, - Barry Smith ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin

Re: [sqlite] [EXTERNAL] sqlite3_exec without ubiqitous text conversions

2019-07-30 Thread Barry
Why do it this way? Why not write your own custom_sqlite3_exec(...) that uses the standard, stable, documented interfaces? custom_sqlite3_exec(...) could call prepare / step / finalize, and use the standard sqlite3_column_* interfaces to fill a result array. This would be very little work and

Re: [sqlite] Is WAL mode serializable?

2019-07-09 Thread Barry
Serialization isolation requires 'The databases ends up in the state it would appear if every transaction were run sequentially'. It doesn't actually require that all transactions occur sequentially, just that the database ends up in a state as though they had. Why do you think that SQLite's

Re: [sqlite] wal mode

2019-12-08 Thread Barry
Even in rollback journal mode, it is not universally safe to use normal file operations on a SQLite database. See section 1.3 and 1.4 of https://www.sqlite.org/howtocorrupt.html If you want to use normal file system operations (or any type of manipulation not using the SQLite library) on an

[sqlite] design problem involving trigger

2015-08-23 Thread Barry Smith
. 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 wr

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

2015-05-23 Thread Barry Smith
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: > > >

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

2015-09-16 Thread 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

[sqlite] database permanently locked

2006-12-02 Thread Max Barry
My database is permanently locked, and I've spent two fruitless days trying to unlock it. The problem: $ sqlite trac.db SQLite version 3.3.6 Enter ".help" for instructions sqlite> .databases Error: database is locked I believe this was caused by something bad happening in Trac 0.9.6. More than

Re: [sqlite] database permanently locked

2006-12-03 Thread Max Barry
Christian Smith wrote: > Max Barry uttered: > > My database is permanently locked, and I've spent two fruitless days > trying to unlock it. > > You haven't said what sort of box this is. I guess a generic Unix. If Linux, > you'll probably have fuser install

[sqlite] SqLite db ODBC

2008-05-29 Thread Barry Trutor
I am trying to locate an ODBC driver that I can use in the Windows Data Link. Is this doable? Does one exist? Thanks, Barry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

RE: [sqlite] Threads and locking

2005-12-16 Thread Barry Paul
t of using a lock to synchronise access to the > databaseso that only one thread at a time could change the > database although both could read simultaneously? > JS > > Barry Paul wrote: > > > > Hi, I am having some unexpected locking issues with SQLite. > > &

Re: [sqlite] Help with Backup API please

2017-02-08 Thread Barry Smith
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, a

Re: [sqlite] SQLite Options

2017-02-20 Thread Barry Smith
ple 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 me

Re: [sqlite] SQLite Options

2017-02-17 Thread 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

Re: [sqlite] Why is this so much more efficient?

2017-02-16 Thread 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

Re: [sqlite] SQLite.Interop.dll

2017-02-23 Thread 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

Re: [sqlite] SQLite Options

2017-02-19 Thread 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

Re: [sqlite] Datatype for prices (1,500)

2016-12-03 Thread 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

Re: [sqlite] confused getting started

2017-03-05 Thread 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

Re: [sqlite] Seeking advice on improving batch sql update performance on zipvfs

2017-09-08 Thread 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

Re: [sqlite] ADO.NET driver upgrade puzzle

2017-09-27 Thread Barry Smith
(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) <b.robe...@fugro.com> > wrote: > > Hi, > > I am aware of the sqlite FAQ, and especially the comments r

Re: [sqlite] ADO.NET driver upgrade puzzle

2017-09-26 Thread Barry Smith
> On 26 Sep 2017, at 12:14 am, Clemens Ladisch <clem...@ladisch.de> 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 po

Re: [sqlite] LIMIT

2018-06-23 Thread 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

Re: [sqlite] probably recursive?

2018-05-01 Thread Barry Smith
__ > 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 in

Re: [sqlite] probably recursive?

2018-05-01 Thread 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-m

Re: [sqlite] probably recursive?

2018-05-01 Thread 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

Re: [sqlite] probably recursive?

2018-05-01 Thread 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 t

Re: [sqlite] [SQLite3] [Mono] [Linux] "db is locked"

2018-07-03 Thread 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

Re: [sqlite] fsync on -wal still happening

2017-12-29 Thread 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

Re: [sqlite] Doing math in sqlite

2018-12-20 Thread 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 ( >

Re: [sqlite] [EXTERNAL] SQLite error (5): database is locked

2019-01-14 Thread 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

Re: [sqlite] The "natural" order of the query results.

2018-09-16 Thread 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

Re: [sqlite] Row locking sqlite3

2019-03-22 Thread 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

Re: [sqlite] Is it possible to use sqlite online-backup system in an ongoing manner?

2019-03-13 Thread 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

Re: [sqlite] Odd exception when creating a connection object

2019-05-15 Thread Roberts, Barry
many connection objects will get instantiated very close to each other time wise. Unfortunately the problem seems to be very difficult to replicate reliably. Generally we are seeing around about a 5% failure rate at application start. Any further info, ask away. Hope that helps, Barry Roberts

[sqlite] [Re] Odd exception when creating a connection object

2019-05-14 Thread Roberts, Barry
Regards, Barry Roberts. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Odd exception when creating a connection object

2019-05-01 Thread Roberts, Barry
. at System.Data.SQLite.SQLiteLog.Initialize(String className) at System.Data.SQLite.SQLiteConnection..ctor(String connectionString, Boolean parseViaFramework) We have parse via framework set to true. Kind Regards, Barry Roberts. ___ sqlite-users mailing list

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

2020-02-15 Thread 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

Re: [sqlite] Unexplained table bloat

2020-01-13 Thread 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

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

2020-01-02 Thread 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

Re: [sqlite] AVG Function HowTo

2019-12-28 Thread 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

Re: [sqlite] SQL help

2019-12-28 Thread 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

Re: [sqlite] SQL help

2019-12-28 Thread Barry Smith
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

Re: [sqlite] ADO.NET driver upgrade puzzle

2017-09-25 Thread Roberts, Barry (FINTL)
} [NotNull] public IDbCommand CreateCommand([NotNull] IDbConnection connection) { IDbCommand command = new SQLiteCommand(); command.Connection = connection.NotNull("connection");

Re: [sqlite] When is db size an issue?

2017-10-02 Thread Roberts, Barry (FINTL)
, Barry Roberts. b.robe...@fugro.com<mailto: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] pragma integrity_check throwing exception

2017-10-03 Thread Roberts, Barry (FINTL)
et the above working in the later drivers? Kind Regards, Fugro Intersite. Barry Roberts. b.robe...@fugro.com<mailto:b.robe...@fugro.com> | www.fugro.com ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-b

[sqlite] ADO.NET driver upgrade puzzle

2017-09-25 Thread Roberts, Barry (FINTL)
se journal mode "persist". Should we not use prepare calls when running connection pooling, or do you have any other recommendations we should take note of. Kind Regards, Barry Roberts. b.robe...@fugro.com<mailto:b.robe...@fugro.com> | www.fugro.com

Re: [sqlite] ADO.NET driver upgrade puzzle

2017-09-27 Thread Roberts, Barry (FINTL)
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<mailto:b.robe...@fugro.com> | www.fugro.com ___ sqlite-users mailing list sqlite

Re: [sqlite] C# pragma integrity_check call throwing exception

2017-10-26 Thread Roberts, Barry (FINTL)
s now. This helps me justify altering our code base, to work with the revised behaviour. Kind Regards, Fugro Intersite. Barry Roberts. b.robe...@fugro.com<mailto: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] C# pragma integrity_check call throwing exception

2017-10-24 Thread Roberts, Barry (FINTL)
works and returns ok, so access to the file is ok, just some internal indexes are messed up. Is there any way to get the above working in the later drivers, or can someone tell me which driver update changed this behaviour? Kind Regards, Fugro Intersite. Barry Roberts. b.robe...@fugro.com<mai