Re: [sqlite] GUI INfo

> On Sep 10, 2014, at 1:56 PM, Maxine Nietz wrote: > > What I want to know is where do I find info on creating a graphical user > interface such as menus, forms and reports. What additional programs are > required to do this? SQLite doesn't have anything related to a

[sqlite] R-tree query regression, in SQLite library on latest Mac OS X beta

I've run into an R-tree query regression in the latest beta of Mac OS X 10.10. Looks like Apple upgraded the built-in SQLite library to version 3.8.5. (I'm not sure what version was in the previous beta, but the current released Mac OS X, 10.9.4, has version 3.7.13.) I looked at the list of

Re: [sqlite] R-tree query regression, in SQLite library on latest Mac OS X beta

> On Sep 10, 2014, at 4:06 PM, Richard Hipp wrote: > > The problem was fixed in SQLite version 3.8.6 released on 2014-08-15. Thanks for the confirmation! I hadn't caught the notice that the bug fix is in 3.8.6; I must not have read far enough down the release notes. I filed

Re: [sqlite] R-tree query regression, in SQLite library on latest Mac OS X beta

> On Sep 10, 2014, at 4:28 PM, Simon Slavin wrote: > > Please post a followup to your bugreport mentioning that /the SQLIte > developers/ say that the bug is fixed in build 3.8.6. Apple's report readers > will notice it and note the easy fix. I've just done so. It's

Re: [sqlite] R-tree query regression, in SQLite library on latest Mac OS X beta

> On Sep 10, 2014, at 4:50 PM, Simon Slavin wrote: > > Don't worry too much about this. In the last count of an OS X version I > counted seven different copies of SQLite being used in various places, > integrated into programs, or as libraries, or as the shell tool.

Re: [sqlite] R-tree query regression, in SQLite library on latest Mac OS X beta

> On Sep 11, 2014, at 1:44 AM, Tim Streater wrote: > > In OS X 10.9.4, the SQLite version in PHP is 3.7.7.1, for example. Yes, as I already said, there are some open-source components not developed by Apple that embed their own copies of sqlite. But the really important

[sqlite] Doc issue: No explanation of record counts for EXPLAIN QUERY PLAN

In the documentation of EXPLAIN QUERY PLAN[1], I found the following: > sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; > 0|0|0|SCAN TABLE t1 > The example above shows SQLite estimating that the full-table scan will visit > approximately 100,000 records. There’s no

Re: [sqlite] Replicate SQLite and keep sync (every half hour) from PostgreSQL table

> On Feb 2, 2017, at 1:15 AM, Michael Nielsen wrote: > > I would like to replicate the PostgreSQL table (including a WHERE clause) > to a in-memory SQLite database, which will sync/update every 30 minutes (or > so). There isn’t any built-in or standard way to do this

Re: [sqlite] Bitten by lack of isolation between SELECT and UPDATE on the same connection

> On Jan 31, 2017, at 9:39 AM, James K. Lowden wrote: > > According the SQL standard, every SQL statement is atomic. SELECT has > no beginning and no end: the results it returns reflect the state of > the database as of the moment the statement was executed. If you

[sqlite] Request: Per-connection equivalent to SQLITE_CONFIG_LOG

Our library is registering a SQLITE_CONFIG_LOG callback* to log error messages. This can provide useful information about errors returned by SQLite. However, we’ve found a side effect that, since this callback is global, it gets called due to activity from other clients using SQLite in the same

Re: [sqlite] WAL mode

> On Feb 8, 2017, at 9:46 AM, Simon Slavin wrote: > > Does your program execute sqlite3_shutdown() and check to see whether it > returns an error code ? Never noticed that function before … the docs say it’s "designed to aid in process initialization and shutdown on

Re: [sqlite] WAL mode

> On Feb 8, 2017, at 9:21 AM, Niti Agarwal wrote: > > According to the SQLite documentation, the shm and wal files are supposed > to be deleted upon completion of the program. Are you explicitly closing the database before your program exits? (In other words, I think

Re: [sqlite] Transactions

> On Feb 6, 2017, at 11:08 AM, James K. Lowden wrote: > > It's fascinating, and emblematic of our times, that > something like iTunes had (or has) DBMS interaction amidst low-level > operations like capture and playback. Oh, it didn’t use a database! It was just

Re: [sqlite] Patch Etiquette

> On Feb 5, 2017, at 12:14 PM, Ziemowit Laski wrote: > > HOWEVER, one thing bothers me: You did not acknowledge my authorship of it. > AFAICT, you NEVER seem to acknowledge third party contributions. Clearly, > I'm not user 'drh’. Like with other open-source projects, I

Re: [sqlite] SQLite3 Pros / Cons

> On Feb 5, 2017, at 5:27 AM, Clyde Eisenbeis wrote: > > I posted "[sqlite] Retrieve INTEGER PRIMARY KEY" a few days ago. The > only solution proposed appears to use sqlite3. I think you’re confusing sqlite3 the library with its C API. You’re _already_ using the sqlite3

Re: [sqlite] Transactions

> On Feb 6, 2017, at 12:07 AM, Hick Gunter wrote: > > The optimal number of inserts/transaction depends on your hardware setup and > who else needs access to CPU and I/O resources. Too many transactions can definitely be a problem! It depends on the OS, but the filesystem

Re: [sqlite] Does SQLite use field definitions?

> On Feb 1, 2017, at 7:41 AM, Clyde Eisenbeis wrote: > > However, I don't see any complaints by SQLite when I use MEMO. SQLite actually ignores the column data types completely in a table spec. You can store any type of data in any column of any table. (In other words,

Re: [sqlite] SQLite - SELECT query slowing down while fetching from higher offset.

> On Feb 1, 2017, at 7:18 AM, Richard Hipp wrote: > > See also https://www.sqlite.org/rowvalue.html#scrolling_window_queries > This approach comes with a major caveat that’s not mentioned in the text: the data

Re: [sqlite] JPA

> On Feb 1, 2017, at 6:19 AM, Cecil Westerhof wrote: > > Can SQLite be used with JPA? Sure, I’ve been SQLite for years ;-) —Jens Peter Alfke ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Help with custom collation

Perhaps off-topic, but: UTF-16 is generally not recommended, unless you need to work with legacy APIs that require it. It has the same difficulties as UTF-8 (having to handle characters broken into multi-element sequences) but uses more RAM and isn’t downward compatible with ASCII. Also, since

Re: [sqlite] SQLite Query truncating String column at 255 chars long

> On Jan 27, 2017, at 11:09 AM, John McKown > wrote: > > ​I'm guessing this is a problem with SQLite.NET , or .NET > itself, which is > not supported here. …or with Visual BASIC. (It looks like the code the OP posted is BASIC, judging by the

[sqlite] Bitten by lack of isolation between SELECT and UPDATE on the same connection

I’ve just run headlong in to the issues described in "No Isolation Between Operations On The Same Database Connection”. Specifically, I’ve discovered (after some debugging) that if I iterate over the the rows in a table using sqlite3_step, and update each row after it’s returned, Bad Stuff

Re: [sqlite] Bitten by lack of isolation between SELECT and UPDATE on the same connection

> On Jan 30, 2017, at 8:03 PM, Rowan Worth wrote: > > If the iterator isn't exhausted, how do you know when to dispose the > sqlite3_stmt? The iterator (which is an Objective-C NSEnumerator object) will be deleted shortly after it exits scope. Some of the refcounting is

Re: [sqlite] Bitten by lack of isolation between SELECT and UPDATE on the same connection

> On Jan 30, 2017, at 9:10 PM, Simon Slavin wrote: > > Nope. Cannot do that. Any number of things might happen between the first > _step() and the _finalize(). For all you know someone might delete the > object the iterator is currently on instead of just updating it.

Re: [sqlite] Thread safety of serialized mode

> On Feb 17, 2017, at 3:48 AM, Simon Slavin wrote: > > It is insane that a CPU would allow two threads to interfere with each-other > in such a way as to 'break' an INC instruction. It may be insane to you, but it’s simply how multiprocessor computer architectures work,

Re: [sqlite] Thread safety of serialized mode

> On Feb 17, 2017, at 9:18 AM, James K. Lowden wrote: > > It's the OS. A thread is an OS abstraction, not a machine feature. You don’t need an OS to create threads. All you need is setjmp/longjmp or the equivalent. “Green” threads are more awkward to use than

Re: [sqlite] Thread safety of serialized mode

> On Feb 17, 2017, at 11:11 AM, Dominique Devienne wrote: > > No they are not. They may be scheduled on threads, but they are not threads. We're disagreeing on terminology, but I believe I’m correct. Threads don’t have to be implemented at the OS level. Threads

Re: [sqlite] Thread safety of serialized mode

> On Feb 16, 2017, at 11:49 AM, Warren Young wrote: > > A software developer who refuses to learn about his processor’s assembly > language is like trying to become an electrical engineer without learning > anything about physics. In this case what you need to read is

Re: [sqlite] Deprecated OSX functioon

> On Feb 16, 2017, at 1:02 PM, James Walker wrote: > > For what it's worth, the OS header recommends replacing it with the function > atomic_compare_exchange_strong, which I assume refers to the C++11 template > function. It’s also available in C as a macro defined

Re: [sqlite] Thread safety of serialized mode

> On Feb 16, 2017, at 6:26 PM, James K. Lowden wrote: > > It doesn't change the fact that the OS has subverted the > guarantees your language would otherwise provide, such as the atomicity > of ++i noted elsewhere in this thread. It’s not the OS, it’s the

Re: [sqlite] Patch for consideration: auto_vacuum slack.

> On Feb 13, 2017, at 12:13 PM, Richard Hipp wrote: > > Does anybody really care anymore that a > database file might have a few dozen pages on its freelist? Or if > they do care, does anybody lack the temp space sufficient to run a > real VACUUM? The issue of vacuuming has

Re: [sqlite] thousand separator for printing large numbers

> On Feb 10, 2017, at 5:59 AM, Dominique Devienne wrote: > > 2) ask DRH to consider enhancing SQLite's built-in printf() to support it > out-of-the-box. I disagree; this is feature bloat. SQLite is an embedded database, and the host app can do whatever it wants with the

Re: [sqlite] thousand separator for printing large numbers

> On Feb 10, 2017, at 2:45 AM, Dominique Devienne wrote: > > Honestly Clemens? There wouldn't be a built-in printf() and substr() etc... > if that was the case. Not really. Those aren’t necessarily intended to format data for display, and I’ve never used them for that.

Re: [sqlite] Thread safety of serialized mode

> On Feb 14, 2017, at 4:46 PM, Richard Hipp wrote: > > This is yet another reason why I say "threads are evil”. I agree, and it’s a pretty widely held opinion these days, going back at least to Edward Lee’s 2006 paper “The Problem With Threads”.[1] Actually the problem

Re: [sqlite] Thread safety of serialized mode

> On Feb 14, 2017, at 3:51 PM, Bob Friesenhahn > wrote: > > Due to timing constraints, it performs all read queries in one thread and > creates a temporary POSIX thread for each update query (this is the > developer's reasoning). To me that seems kind of

Re: [sqlite] Thread safety of serialized mode

> On Feb 14, 2017, at 5:15 PM, Richard Hipp wrote: > > Nor is there anything wrong with goto, pointers, and assert(), in > principle. And yet they are despised while threads are adored, in > spite of the fact that goto/pointer/assert() errors are orders of > magnitude easier

Re: [sqlite] Difference between min and max time, especially for sys

> On Feb 15, 2017, at 11:47 AM, Cecil Westerhof wrote: > > But the difference between sys can be almost a factor twenty. What seems very > big to me. Yup. There is a huge speed difference between the kernel going to the disk/SSD to read from a file, vs. the kernel

Re: [sqlite] 3.17.0 does not read updated DB

> On Feb 15, 2017, at 9:51 PM, Roman Fleysher > wrote: > > My mistake: I do not update DB. I rename (unix mv) the DB. I wouldn’t recommend doing that to an open database! (a) I don’t think it’s formally specified when SQLite opens or closes the database file.

Re: [sqlite] Thread safety of serialized mode

> On Feb 15, 2017, at 3:44 AM, Cecil Westerhof wrote: > > ​As I said before: I did not work much with threads. Mostly for GUI > performance. Do you (or anyone else) have any resources about those > concurrency models​? Theory: https://en.wikipedia.org/wiki/Actor_model

Re: [sqlite] Thread safety of serialized mode

> On Feb 14, 2017, at 11:58 PM, Clemens Ladisch wrote: > > But "go parallel" does not necessarily imply threads. There are many > ways to allow code running on different CPUs(/cores) to communicate > with each other (e.g., files, sockets, message queues, pipes, shared >

Re: [sqlite] Beginning of release testing for version 3.17.0

> On Feb 7, 2017, at 7:39 AM, no...@null.net wrote: > > Nice to see a sha1 extension included with SQLite now. … Just in time for SHA-1 to be declared officially broken: there’s now an effective mechanism to generate collisions (it only takes 100 GPU-years), and at least two colliding files

Re: [sqlite] SQLite.NET Class Library Documentation Feedback: Limitations

> On Oct 5, 2016, at 5:49 AM, Santiago Bayeta wrote: > > But I also read from SQLite docs that now SQLite supports unlimited Reading > operations while writing. It does if the databases uses a WAL (Write-Ahead Log). This is a persistent per-database setting that can be

Re: [sqlite] multiple processes working on one database file

> On Oct 5, 2016, at 3:25 AM, Simon Slavin wrote: > > Yes. SQLite incorporates multi-process/multi-thread locking, exponential > backoff, and such things. This all works fine unless you have intentionally > turned off those features. I did find that SQLite’s own

Re: [sqlite] Protecting databases

> On Oct 7, 2016, at 10:46 PM, Damien Sykes-Lindley > wrote: > > I cannot see any means of password protecting the database without either > buying a commercial extension to do this, or recompiling SQLite with the > authentication extension. I’m surprised no one

Re: [sqlite] Parallel access to read only in memory database

> On Oct 7, 2016, at 1:45 PM, Joe Mistachkin wrote: > > Have you tried using the URI "file::memory:?cache=shared” Shared cache will definitely help when using multiple read-only connections, but I have a feeling memory-mapping would help even more since it would

[sqlite] Backward compatibility of indexes with "WHERE function()"

What’s the compatibility of the new partial indexes* with deterministic functions in their WHERE clauses? I.e. if I create such an index, and then later the database is opened by an older version of SQLite, what happens? (Let’s say the function used in the WHERE clause is still properly

Re: [sqlite] Backward compatibility of indexes with "WHERE function()"

> On Oct 8, 2016, at 12:56 PM, Richard Hipp wrote: > > Databases that use functions in the WHERE clause of a partial index > will be unreadable by any version of SQLite prior to 3.15.0. Yikes! This would be good to call out in the docs. > Why can't you statically link against

Re: [sqlite] Backward compatibility of indexes with "WHERE function()"

> On Oct 8, 2016, at 1:39 PM, Richard Hipp wrote: > > See http://sqlite.org/graphs/size-20161009.jpg > for a graph of > compiled-binary size using gcc 4.8.4 and -Os on x64 Linux. Less than > 0.5 MB, though to be fair this is

Re: [sqlite] Parallel access to read only in memory database

> On Oct 9, 2016, at 8:15 AM, Howard Chu wrote: > > Use SQLightning, it's designed specifically for write once read many > workloads. "SQLite3 ported to use LMDB instead of its original Btree code” — sounds great, and the performance figures quoted in the readme are

Re: [sqlite] Partial indexes on JSON properties?

> On Oct 3, 2016, at 11:29 AM, Richard Hipp wrote: > > Deterministic SQL functions are now allowed in partial index WHERE > clauses, as of a few minutes ago. The current "Prerelease Snapshot" > (https://www.sqlite.org/download.html ) >

Re: [sqlite] Partial indexes on JSON properties?

> On Oct 2, 2016, at 6:20 AM, Clemens Ladisch wrote: > > Changing the function in any way (including not registering the > function) would essentially corrupt the index. Well, the same can be said of using a custom collation function, which has been supported since 3.0; or

[sqlite] Partial indexes on JSON properties?

I’m experimenting with querying databases of JSON documents. These data-sets are schemaless and there’s no guarantee that they all have a common set of properties; in fact it’s common for them to have the equivalent of multiple ‘tables’ in the same data-set, i.e. groups of documents with

Re: [sqlite] Parallel access to read only in memory database

> On Oct 9, 2016, at 10:41 AM, Howard Chu wrote: > > As for code freshness, I've seen no compelling new features from 3.8.x onward > that would improve performance so there's been no reason to update further. Perhaps, but there’s important new functionality in newer versions,

Re: [sqlite] Data loss during the disk full condition

> On Oct 29, 2016, at 10:44 AM, Simon Slavin wrote: > > To minimise problems like the one you reported it needs to quit the program > (preferably with a non-zero exit code) when it gets any unexpected result. That may be true for a server or a command-line tool, but it's

Re: [sqlite] Data loss during the disk full condition

> On Oct 29, 2016, at 11:34 AM, Simon Slavin wrote: > > Really ? An interactive program (or any program) gets a result code it wan't > expecting and you don't want it to shut down ? Really. Apps aren’t supposed to crash. How would you feel if Photoshop or Word or Logic

Re: [sqlite] Fastest way to search json array values?

> On Nov 9, 2016, at 3:04 AM, Wout Mertens wrote: > >> Indexes (even expression indexes) require a 'real' table. >> You'd have to put the results of json_each() into a separate table that >> can then be indexed. > > I assume I can use some sort of trigger setup to

Re: [sqlite] Import 5000 xml files in a sqlite database file

There might be a GUI SQLite client app that can do this; I don’t know (the one I use, SQLiteManager, can only import SQL files.) But it’s really easy to do with a short program in a scripting language like Python/Ruby/PHP. I just searched for [sqlite import xml] and the top hit is the

Re: [sqlite] A small technical question about SQLite

I’m guessing it’s probably a phone-sex line. No one would actually expect strangers to call them to answer their homework problems. —Jens > On Oct 24, 2016, at 7:20 PM, Scott Robison wrote: > > Don't everyone dial at once! > > On Mon, Oct 24, 2016 at 2:36 PM, LIAT

Re: [sqlite] sqlite3 db is encrypted how to decrypt

> On Oct 21, 2016, at 12:16 AM, ravi.shan...@cellworksgroup.com wrote: > >I have a sqlite3 db which is encrypted using lib.so file how to > decrypt the db. If i access the db it print db is encrypted or not a > database. I don't have any idea about lib.so file and encryption used

Re: [sqlite] Drop Existing Table Results in 'no such table'

> On Oct 20, 2016, at 11:40 PM, Clemens Ladisch wrote: > > sanhua.zh wrote: >> I am trying to rename a table by editing the sqlite_master directly. > > Don't do this. Patient: “Doctor, my arm hurts when I do this!” Doctor: “Then don’t do that.” —Jens

Re: [sqlite] Using IGNORE for complete record match

> On Oct 21, 2016, at 2:46 PM, Rick Kohrs wrote: > > INSERT or IGNORE does not seem to be working as expected. The “or IGNORE” part describes what happens if there’s a conflict that would otherwise cause the INSERT to fail. Your schema doesn’t declare any column or

Re: [sqlite] How does the pager know whether its memory cache is still valid?

> On Oct 24, 2016, at 1:31 AM, Rowan Worth wrote: > > OK, so the entire cache is invalidated when another process updates the DB, > which is what I feared. In this case I'm looking at too many concurrent > updates for caching to add much value. I’m no expert on the internals,

Re: [sqlite] Encryption

> On Nov 14, 2016, at 7:23 AM, Ulrich Telle wrote: > > The RSA encryption offered by System.Data.SQLite should not be used, if > security is a concern for you. You should prefer an AES encryption scheme. I’m surprised anything encrypts databases with RSA, as that

Re: [sqlite] When does SQLite open/create files?

> On Nov 23, 2016, at 1:31 PM, Richard Hipp wrote: > > Have you enabled the error and warning log? > (https://www.sqlite.org/errlog.html ) > IIRC, the error log will give > you the name of the file that could not be opened. No! I wasn’t

[sqlite] Warning message about "database schema has changed", although sqlite3_step succeeds

Now that I’ve enabled the SQLite error log, I’m seeing some unexpected messages, like this one during sqlite3_step: statement aborts at 28: [CREATE TABLE IF NOT EXISTS …] database schema has changed {at errorLogCallback:116} The DB schema must have changed since the statement

[sqlite] Minor error in FTS3/4 docs

In section 3 of the FTS3/4 documentation page, : Normally, a token or token prefix query is matched against the FTS table column specified as the right-hand side of the MATCH operator. That should read "*left*-hand

Re: [sqlite] creating a table

> On Nov 21, 2016, at 10:08 AM, John R. Sowden > wrote: > > Thank you all for your answers and direction for further information. > Hopefully, I will not bring these subjects up again. :) Some of what you’re asking applies to any SQL database. The SQLite docs do

[sqlite] When does SQLite open/create files?

Does SQLite ever open or create files while a database connection is already open? Or does that only happen while creating the connection? (I'm using WAL mode, if it makes a difference.) --Jens [via iPhone] ___ sqlite-users mailing list

Re: [sqlite] When does SQLite open/create files?

> On Nov 21, 2016, at 12:39 PM, Richard Hipp <d...@sqlite.org> wrote: > > On 11/21/16, Jens Alfke <j...@mooseyard.com> wrote: >> Does SQLite ever open or create files while a database connection is already >> open? > > (1) When you run ATTACH. > >

Re: [sqlite] When does SQLite open/create files?

> On Nov 23, 2016, at 12:45 PM, Simon Slavin wrote: > > Are you checking the value returned by each call to the SQLite API to make > sure it's SQLITE_OK ? Yes. As I said, the error is returned from sqlite3_step(), but it doesn’t specify _what_ file couldn’t be opened.

Re: [sqlite] When does SQLite open/create files?

> On Nov 22, 2016, at 2:10 PM, Richard Hipp wrote: > > No. Did you run "PRAGMA temp_store=MEMORY;"? This might be a > statement journal opening. No. (The string “temp_store” does not appear anywhere in the source code.) Any way I can get more info from SQLite about exactly

Re: [sqlite] Changing ID's to UUID

> On Nov 15, 2016, at 3:02 AM, Simon Slavin wrote: > > SQLite has a randomblob function which can be used to select part of the > UUID, but you need to pick a UUID scheme suitable for your purposes to know > how much of it can be random. In some use cases it’s important

[sqlite] Database corruption, and PRAGMA fullfsync on macOS

I’m seeing conflicting information about SQLite’s use of F_FULLFSYNC on macOS when committing transactions. This is making me nervous about durability and the possibility of database corruption. The SQLite docs for PRAGMA fullfsync (https://www.sqlite.org/pragma.html#pragma_fullfsync

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

> On Nov 15, 2016, at 10:57 AM, Simon Slavin wrote: > > sqlite> PRAGMA checkpoint_fullfsync; > 1 I wasn’t aware of that pragma. Just tried it on my Mac (10.12.1), and its value is 1 even if I don’t first set pragma fullfsync; i.e. it defaults to 1. (Contradicting the

Re: [sqlite] Changing ID's to UUID

> On Nov 15, 2016, at 11:35 AM, Quan Yong Zhai wrote: > > Create a custom function MD5 , If you’re going to go to this trouble, at least use SHA256! MD5 is broken. These days no one should be using it for anything, except when needed for compatibility with legacy

Re: [sqlite] Changing ID's to UUID

> On Nov 16, 2016, at 5:59 AM, Keith Medcalf wrote: > > What I do not understand is why one would use a UUID (randomly generated > bunch of bytes) as a key in a database. It is long, every use must be > checked for collisions, and inherently far less efficient than the

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

> On Nov 15, 2016, at 1:46 PM, Simon Slavin wrote: > > Do these people admit they're letting their phones run out of power ? There’s nothing wrong with letting your phone run out of power, and software should be resilient to it. I don’t think that’s the problem,

Re: [sqlite] Changing ID's to UUID

> On Nov 15, 2016, at 1:11 PM, Scott Robison wrote: > > Completely depends on your needs. If your needs are not cryptographic, then > there is no problem. But there’s little reason to use MD5 instead of SHA-1; they’re roughly the same speed, but SHA-1 is considerably

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

> On Nov 15, 2016, at 10:57 AM, Simon Slavin wrote: > > My understanding is that F_FULLFSYNC still works the way you describe on a > Mac and SQLite still uses it the way the documentation says. But I'm not in > touch with either development group. This seems like a

[sqlite] Are identical calls to deterministic functions coalesced?

Does the query optimizer coalesce identical calls to deterministic functions? I’m thinking in particular of a statement like: CREATE INDEX byFoo ON jtable (json_extract(body, ‘$.foo’)) WHERE json_extract(body, ‘$.foo’) IS NOT NULL where it would improve performance if the JSON were not

[sqlite] Thread-safety of user-defined functions

Do I need to worry about concurrent calls to custom functions (or virtual tables) that I register with SQLite? They’re associated with only a single connection, but with Serialized mode, that connection could be used from multiple threads. And what if I use `pragma threads` to enable helper

Re: [sqlite] Using nested SELECT with json_each()

Thanks much! (I’d forgotten about EXPLAIN QUERY PLAN — I had tried regular EXPLAIN, but the long list of virtual-machine instructions was too much for my brain.) —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

[sqlite] Using nested SELECT with json_each()

I’m writing code to generate SQL queries that use json_each to test the contents of a JSON array. The docs give this as an example: SELECT DISTINCT user.name FROM user, json_each(user.phone) WHERE json_each.value LIKE '704-%'; I’m not happy with this, as it requires the

Re: [sqlite] Backward cursor support?

> On Oct 12, 2016, at 9:49 AM, Igor Korot wrote: > > Forward cursors are supported by means of prepare()/step(), but what about > backward cursors? No, SQLite cursors only go in one direction, i.e. there is no step_back function. You could simulate this feature by copying

Re: [sqlite] Why takes the second SELECT three times as much time?

> On Oct 16, 2016, at 4:49 AM, Luuk wrote: > > Because your second query has to build the complete view before it can decide > if a result is between the selected dates? I didn’t think a view had a physical manifestation that had to be built; I thought it was just a

Re: [sqlite] Regarding Hebrew data in Sqlite db

> On Oct 15, 2016, at 9:26 AM, Vaibhav Shah wrote: > > I am using sqlite3.exe for bulk insertion in C#. I am facing issue when > insert Hebrew data. As it contains double qoute(") as character and it does > not support in insertion. It’s almost always a bad idea to put

Re: [sqlite] A possible double bug?

I’d say the mistake here is converting a double to a string before inserting it into the database. Any time you convert between floating point and decimal (or vice versa) you can lose accuracy, and are not guaranteed round-trip fidelity. (0.1, 0.01, 0.001, etc. do not have finite-length exact

Re: [sqlite] A possible double bug?

> On Oct 17, 2016, at 2:12 AM, Quan Yong Zhai wrote: > > Or after prepare “ INSERT INTO test VALUES(?) “ > Bind_text “62.027393” > Bind_double 62.027393 > > In all the four situation, the value insert into foo field is binary > identical, it’s a 8-bytes REAL value.

Re: [sqlite] Any performance penalty for SELECTing more columns? (C API)

> On Oct 15, 2016, at 11:10 AM, Clemens Ladisch wrote: > > In practice, this does not matter unless you have large strings/blobs > that must be read from overflow pages. I do, actually, which is why I asked. One of the columns is a blob holding a JSON document that can be

Re: [sqlite] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

> On Oct 15, 2016, at 11:12 AM, Keith Medcalf wrote: > >> Is there a way to do this automagically (like a specialized INSERT >> command?) in Sqlite? > > Unfortunately no, there is no way to do this on *ANY* database that uses the > relational database model. There’s no

Re: [sqlite] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

> On Oct 15, 2016, at 7:42 AM, Luca Olivetti wrote: > > Instead of an autoincrement you could increment it manually in, say, 1000 > increments. > Then, when you have to insert something between 1000 and 2000 you just use > 1500. Unfortunately this breaks down after log2(1000)

[sqlite] Any performance penalty for SELECTing more columns? (C API)

In a simple SELECT query of a single table, using the C API, is there any difference in performance for requesting more or fewer columns of the table in the result? Or is the performance penalty only incurred when actually reading the column values? For example, lets say a table has 26

Re: [sqlite] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

> On Oct 15, 2016, at 11:38 AM, Luca Olivetti wrote: > > I'm aware of the limitation but for my application[*] it is perfectly fine, > maybe it is also ok for the OP, maybe it isn't. OK, but if you’re proposing a solution that you know has major limitations, I think it’s a

Re: [sqlite] How do I check if the DB is open?

> On Dec 13, 2016, at 5:33 PM, Simon Slavin wrote: > > The only thing you should do if sqlite3_close() doesn’t work is to print an > error message which includes the value returned. Because if you can’t close > the database what are you going to do instead ? Igor is

Re: [sqlite] How do I check if the DB is open?

> On Dec 13, 2016, at 8:01 PM, Igor Korot wrote: > > Yes, you are correct. > Do you know how I can write such a code? Just move the code that looks for statements into the " if( res != SQLITE_OK )” block. —Jens ___ sqlite-users

Re: [sqlite] Updating multiple records in C#

> On Dec 6, 2016, at 5:40 PM, Richard Andersen wrote: > > cmd.CommandText = @"UPDATE pdata SET FileName = @fileName WHERE FileName = > 'filename.zip'"; You can add other columns to set by adding more “name = value” expressions in between SET and WHERE. Here’s the

Re: [sqlite] System.Data.SQLite -> ChangePassword 2nd time has opposite behavior

> On Dec 12, 2016, at 5:00 AM, Baugher, Melody wrote: > > string passwordPragma = "PRAGMA key='" + password + "';"; Not quoting the password string leaves you open to SQL injection attack. This probably isn’t an issue in an internal development tool, but if there’s

Re: [sqlite] LIKE and the like and SIMilarity

> On Jan 11, 2017, at 9:55 AM, Anony Mous wrote: > > Textual data has case. Sometimes that matters. Sometimes it doesn't. A > database engine should be able to cleanly deal with that without forcing > the programmer to write custom code. +1. And while we’re at it, I’d like

Re: [sqlite] INSERT is corrupting a database

> On Jan 12, 2017, at 3:52 PM, Kevin O'Gorman wrote: > > My opinion is that no user bug whatever should cause DB integrity problems > without raising an exception. That is a totally reasonable attitude … for programs running in a “safe” environment like an

[sqlite] Bug report: Incorrect error information if db fails to open due to bad flags

I’ve found a case where incorrect error information gets reported to client C code trying to open a SQLite database. (This is with SQLite 3.14 on mac OS 10.12.) After the following C code runs (the path here is irrelevant): sqlite3 *db; int ret = sqlite3_open_v2("/tmp/foo", ,

  1   2   3   4   5   6   >