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

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

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

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] SQL help

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] SQL help

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] AVG Function HowTo

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] Row locking sqlite3

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?

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] [EXTERNAL] SQLite error (5): database is locked

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] Doing math in sqlite

> 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] The "natural" order of the query results.

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] [SQLite3] [Mono] [Linux] "db is locked"

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] LIMIT

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?

ecause 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 >

Re: [sqlite] probably recursive?

t; ____ >> 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 recur

Re: [sqlite] probably recursive?

__ > 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?

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] fsync on -wal still happening

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] ADO.NET driver upgrade puzzle

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

Re: [sqlite] ADO.NET driver upgrade puzzle

> 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

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

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] confused getting started

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] SQLite.Interop.dll

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

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

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] SQLite Options

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?

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] Help with Backup API please

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

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

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

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

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] design problem involving trigger

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

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

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