[sqlite] FW: [sqlite-announce] Version 3.29.0
And there was great rejoicing: "1. Added the SQLITE_DBCONFIG_DQS_DML and SQLITE_DBCONFIG_DQS_DDL actions to sqlite3_db_config() for activating and deactivating the double-quoted string literal misfeature. Both default to "on" for legacy compatibility, but developers are encouraged to turn them "off", perhaps using the -DSQLITE_DQS=0 compile-time option." Also, I don't think I'd ever seen the quirks page (https://sqlite.org/quirks.html) before. Is that new-ish? I don't see it anywhere on https://sqlite.org/docs.html , maybe add it to the "Overview Documents" section? -Original Message- From: sqlite-announce On Behalf Of Richard Hipp Sent: Wednesday, July 10, 2019 4:20 PM To: sqlite-announce Subject: [sqlite-announce] Version 3.29.0 SQLite version 3.29.0 is now available on the SQLite website: https://sqlite.org/ https://sqlite.org/releaselog/3_29_0.html Version 3.29.0 is a routine maintenance release with some small performance enhancements and fixes for various obscure bugs. See the release notes above for details. If you have any problems, please report them to the sqlite-users@mailinglists.sqlite.org mailing list or directly to me. -- D. Richard Hipp d...@sqlite.org ___ sqlite-announce mailing list sqlite-annou...@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-announce ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FW: [sqlite-announce] Version 3.29.0
On 7/11/19, David Raymond wrote: > I don't think I'd ever seen the quirks page > (https://sqlite.org/quirks.html) before. Is that new-ish? It's been around for a little more than a year. See https://www.sqlite.org/docsrc/finfo?ss=c&name=pages%2Fquirks.in -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Quirks of SQLite. Was: Version 3.29.0
On 7/11/19, David Raymond wrote: > I don't see [quirks.html] > anywhere on https://sqlite.org/docs.html , maybe add it to the "Overview > Documents" section? The quirks.html document is now linked in the Overview Documents section. https://www.sqlite.org/quirks.html EVERYONE: If you have personally experienced some unusual or unexpected feature of SQLite that you think should be added to "quirks.html", please follow-up to this thread, or send me private email, so that I can consider adding it. Thanks. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Quirks of SQLite. Was: Version 3.29.0
On 11 Jul 2019, at 3:21pm, Richard Hipp wrote: > EVERYONE: If you have personally experienced some unusual or > unexpected feature of SQLite that you think should be added to > "quirks.html", please follow-up to this thread, or send me private > email, so that I can consider adding it. A) (perhaps add to the BOOLEAN) SQLite has no UNSIGNED affinity. The Quirks document so far is all about SQLite's interpretation of SQL, but I am thinking about other things which might belong in it. B) SQLite doesn't open a database when you call _open() . And it doesn't return an error message if you try to open a non-existent database in read-only mode. C) SQLite doesn't close a database when you call _close(). And if there's a pending statement which would precent closure, it won't return an error code. D) SQLite (may ?) require read/write access to a database's folder even if you open the database in read-only mode. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Grammar police
" An application interact with the database engine using function calls, not be sending messages to a separate process or thread." "An applications [interacts] ..., [not by]... ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Grammar police
Sorry. This was in the Quirks, Caveats page, #2. On Thu, Jul 11, 2019 at 9:57 AM Don V Nielsen wrote: > " An application interact with the database engine using function calls, > not be sending messages to a separate process or thread." > > "An applications [interacts] ..., [not by]... > > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Quirks of SQLite. Was: Version 3.29.0
On Thu, Jul 11, 2019 at 6:47 AM Simon Slavin wrote: > > On 11 Jul 2019, at 3:21pm, Richard Hipp wrote: > > > EVERYONE: If you have personally experienced some unusual or > > unexpected feature of SQLite that you think should be added to > > "quirks.html", please follow-up to this thread, or send me private > > email, so that I can consider adding it. > > A) (perhaps add to the BOOLEAN) SQLite has no UNSIGNED affinity. > > The Quirks document so far is all about SQLite's interpretation of SQL, but I > am thinking about other things which might belong in it. > > B) SQLite doesn't open a database when you call _open() . And it doesn't > return an error message if you try to open a non-existent database in > read-only mode. > > C) SQLite doesn't close a database when you call _close(). And if there's a > pending statement which would precent closure, it won't return an error code. > > D) SQLite (may ?) require read/write access to a database's folder even if > you open the database in read-only mode. Yes please to things like this going in there. Britton ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Quirks of SQLite. Was: Version 3.29.0
Typos \ suggested amendments to quirks.html Section 2 "When ever comparing SQLite to other SQL database engines" When ever should be one word. "Whenever comparing SQLite to other SQL database engines" "An application interact with the database engine" should be, "An application *interacts* with the database engine" "using function calls, not be sending messages to a separate process" should be, "using function calls, not *by* sending messages to a separate process" Section 3.2 "SQLite as no DATETIME datatype" should be, "SQLite *has* no DATETIME datatype" Section 4 "there where already countless millions of databases" should be, "there *were* already countless millions of databases" Thanks, Chris On Thu, Jul 11, 2019 at 3:22 PM Richard Hipp wrote: > On 7/11/19, David Raymond wrote: > > I don't see [quirks.html] > > anywhere on https://sqlite.org/docs.html , maybe add it to the "Overview > > Documents" section? > > The quirks.html document is now linked in the Overview Documents section. > > https://www.sqlite.org/quirks.html > > EVERYONE: If you have personally experienced some unusual or > unexpected feature of SQLite that you think should be added to > "quirks.html", please follow-up to this thread, or send me private > email, so that I can consider adding it. Thanks. > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] Grammar police
Other small ones from the Quirks page: Section 2: "to realize the SQLite is not intended as" to realize [that] SQLite is not intended as Section 3.2: "SQLite as no DATETIME datatype." SQLite [has] no DATETIME datatype Section 5: "Due to an historical oversight" Due to [a] historical oversight Section 6: "each output row might be composed from two more more rows" each output row might be composed from two [or] more rows "then the one of the rows is chosen arbitrarily" then one of the rows is chosen arbitrarily Section 8: "into bad habit of" into [the] bad habit of or into bad habit[s] of (I always feel a little weird when pointing out typos as the meaning is usually perfectly fine the way it is, it feels like I'm being overly critical, and I worry my "corrections" are also not quite right) -Original Message- From: sqlite-users On Behalf Of Don V Nielsen Sent: Thursday, July 11, 2019 10:58 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Grammar police Sorry. This was in the Quirks, Caveats page, #2. On Thu, Jul 11, 2019 at 9:57 AM Don V Nielsen wrote: > " An application interact with the database engine using function calls, > not be sending messages to a separate process or thread." > > "An applications [interacts] ..., [not by]... > > > ___ 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] sqlite3_close() drops wal and shm files despite of other processes holding DB open
I'm using SQLITE3 (V3.29.0) on an arm embedded linux (2.6.39) on an ext3 filesystem. Several processes hold the DB open and the "-wal" and "-shm" files exist. if I use 'lsof | fgrep ' I can see all processes having all three files open. At least one of the processes uses threads, but every process has just one single DB connection active which is shared among all threads. The compilation of sqlite3 is done with multithreading in mind: sqlite> pragma compile_options; COMPILER=gcc-6.2.0 ENABLE_DBSTAT_VTAB ENABLE_FTS4 ENABLE_JSON1 ENABLE_RTREE ENABLE_STAT3 ENABLE_STMTVTAB ENABLE_UNKNOWN_SQL_FUNCTION ENABLE_UPDATE_DELETE_LIMIT HAVE_ISNAN THREADSAFE=1 I can check, that the database is threadsafe (mode == 1) and is switched to WAL-mode. So far I never noticed any problems dealing with concurrent updates or so. The only thing (tested in depth with V3.15.2 and V3.29.0) is when one process stops and closes the database using sqlite3_close(). This may even be the sqlite3 CLI. That process closes DB (lsof shows that this process has closed its filedescriptors and is not in the listing anymore). Right at the next write access to the DB in the still running process (at least I think that this is exactly the point) the "-wal" and "-shm" files are removed. The sqlite3_exec() function still returns SQLITE3_OK on all following actions, but 'lsof' reports, that this process has opened the "-wal" and "-shm" files, but marked as "deleted". And they are really deleted and none of the upcoming DB changes will ever reach the real DB. What is wrong? I already checked, that my kernel supports POSIX file locking (CONFIG_FILE_LOCKING=yes). What else can I check? Two or more sqlite3 CLI processes started in parallel don't exhibit this behavior. Thanks Andreas -- Mit freundlichen Grüßen Andreas Kretzer ETB Electronic Team Beratungs- und Vertriebs GmbH Berliner Straße 8a 15537 Erkner FON +49 3362 889349-12 FAX +49 3362 889349-23 email: a.kret...@etb-electronic.de AG Potsdam HRB 16532; Sitz der Gesellschaft: Am Mellensee Geschäftsführer: Marco Runge, Jürgen Gentzsch ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Quirks of SQLite. Was: Version 3.29.0
On Thu, 11 Jul 2019 10:21:10 -0400 Richard Hipp wrote: > If you have personally experienced some unusual or unexpected feature > of SQLite that you think should be added to "quirks.html", please > follow-up to this thread Thank you for publishing this page. I would suggest these additions: 1. Integer division by zero is not an error. It results in NULL. 2. Update is not atomic. Each row is written one at a time, and "intermediate" updates that (temporarily) violate UNIQUE constraints cause the update to fail, even if the the constraint would be satisfied were the update carried to completion. 3. A table of isolation in SQL terms (repeatable read, etc.). Isolation is affected by WAL and Begin Transaction. SQLite differs in that way quite sharply from other DBMSs. Of these, #2 is the most significant, because it's an unambiguous violation of the SQL standard. I'm unaware of any other SQL implementation that enforces UPDATE constraints at a level invisible to the user. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Grammar police
On 7/11/19, David Raymond wrote: > Section 5: > "Due to an historical oversight" > Due to [a] historical oversight > Here in the Southeastern US (specifically in Charlotte, NC) we really do say "an historical oversight". If you said "a historical oversight", people would look at you funny. Ginger tells me that "a historical" is technically correct, but I'm going with what people (here) actually say. :-) All the other corrections, in this email and in other recent mailing list posts, should now have been applied. Thanks, everybody, for sending them in. Please feel free to do so at any time. You can send them directly to me if you don't want to send them to the mailing list. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Grammar police
On 7/11/19, David Raymond wrote: > Section 5: > "Due to an historical oversight" > Due to [a] historical oversight > Here in the Southeastern US (specifically in Charlotte, NC) we really do say "an historical oversight". If you said "a historical oversight", people would look at you funny. Ginger tells me that "a historical" is technically correct, but I'm going with what people (here) actually say. :-) All the other corrections, in this email and in other recent mailing list posts, should now have been applied. Thanks, everybody, for sending them in. Please feel free to do so at any time. You can send them directly to me if you don't want to send them to the mailing list. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Grammar police
On Jul 11, 2019, at 10:41 AM, Richard Hipp wrote: > > Here in the Southeastern US (specifically in Charlotte, NC) we really > do say "an historical oversight". If you said "a historical > oversight", people would look at you funny. … :) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Grammar police
Ginger tells me that "a historical" is technically correct, AFAICT, "an historical" is correct iff the "h" in "historical" is silent. Eg, "It's an 'istorical oversight to pronounce the 'h' in 'historical'." On Thu, 11 Jul 2019, Richard Hipp wrote: On 7/11/19, David Raymond wrote: Section 5: "Due to an historical oversight" Due to [a] historical oversight Here in the Southeastern US (specifically in Charlotte, NC) we really do say "an historical oversight". If you said "a historical oversight", people would look at you funny. Ginger tells me that "a historical" is technically correct, but I'm going with what people (here) actually say. :-) All the other corrections, in this email and in other recent mailing list posts, should now have been applied. Thanks, everybody, for sending them in. Please feel free to do so at any time. You can send them directly to me if you don't want to send them to the mailing list. -- D. Richard Hipp d...@sqlite.org ___ 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] Grammar police
On 2019-07-11 2:31 PM, Carl Edquist wrote: Ginger tells me that "a historical" is technically correct, AFAICT, "an historical" is correct iff the "h" in "historical" is silent. Eg, "It's an 'istorical oversight to pronounce the 'h' in 'historical'." From the New Oxford American Dictionary entry for "an" -- "usage: Is it ’a historical document’ or ’an historical document’? ‘A hotel’ or ‘an hotel’? There is still some divergence of opinion over which form of the indefinite article should be used before words that begin with h- and have an unstressed first syllable. In the 18th and 19th centuries, people often did not pronounce the initial h for these words, and so an was commonly used. Today the h is pronounced, and so it is logical to use a rather than an. However, the indefinite article an is still encountered before the h in both British and American English, particularly with historical: in the Oxford English Corpus around a quarter of examples of historical are preceded with an rather than a." An is fading in this usage but certainly still acceptable. -- Ned ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SPAM?] Re: Grammar police
On 7/11/19 3:45 PM, Ned Fleming wrote: > On 2019-07-11 2:31 PM, Carl Edquist wrote: >>> Ginger tells me that "a historical" is technically correct, >> >> AFAICT, "an historical" is correct iff the "h" in "historical" is >> silent. >> >> Eg, "It's an 'istorical oversight to pronounce the 'h' in 'historical'." >> > > From the New Oxford American Dictionary entry for "an" -- > > "usage: Is it ’a historical document’ or ’an historical document’? ‘A > hotel’ or ‘an hotel’? There is still some divergence of opinion over > which form of the indefinite article should be used before words that > begin with h- and have an unstressed first syllable. In the 18th and > 19th centuries, people often did not pronounce the initial h for these > words, and so an was commonly used. Today the h is pronounced, and so > it is logical to use a rather than an. However, the indefinite article > an is still encountered before the h in both British and American > English, particularly with historical: in the Oxford English Corpus > around a quarter of examples of historical are preceded with an rather > than a." > > An is fading in this usage but certainly still acceptable. > Yes, the a / an distinction is largely based on the following sound, 'an' if it is a vowel, 'a' if it is not (the n sound breaks up the double vowel). words that begin with an initial unstressed h-vowel might not have the h really vocalized, so the following sound is vowelish, so it takes 'an' (there isn't enough h to break up the vowel cluster). This can largely be affected by the accent one talks with (which can be related but different than the dialect). -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users