Re: [sqlite] The mailing list is deprecated...... [was: Re: [EXTERNAL] No such column error]
On 3/24/20, Luuk wrote: > "The mailing list is deprecated. You need to go to > https://sqlite.org/forum/ for the sqlite forum." > > Can anyone give the source of this? https://www.sqlite.org/support.html https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg119468.html -- 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] New SQLite Forum requires Javascript?
On 3/13/20, J.B. Nicholson wrote: > > But every link in that table which would (again I assume) point to a page > with that > thread's text instead points to https://sqlite.org/forum/honeypot . Ah. That's the anti-robot defense mechanism. See https://fossil-scm.org/fossil/doc/trunk/www/antibot.wiki for details. Since the Forum does not (yet) have diffs and annotations and tarballs and all the other features of Fossil that cause mindless robots to burn CPU cycles on the server, I suppose I can disable that mechanism for the Forum, which I have now done. Please try again. -- 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] New SQLite Forum established - this mailing list is deprecated
On 3/13/20, Philip Bennefall wrote: > Should I write to you directly, or to the Fossil forum? Either one (or both) will be fine. -- 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] New SQLite Forum established - this mailing list is deprecated
On 3/13/20, Philip Bennefall wrote: > I submitted > a thread a while back offering to work on an audio captcha for Fossil, I don't recall that thread. But if you want to submit code that generates an audio file of some kind that speaks the text of a Fossil Captcha, that would be great. I will build it into the system, assuming it works, does not have onerous external dependencies, and you can get me a signed CLA. -- 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] New SQLite Forum established - this mailing list is deprecated
On 3/13/20, Philip Bennefall wrote: > Is there a solution in the pipeline for the inaccessible captcha in the > forum for visually impaired users? The solution is for you to send me a private email asking for me to create your account for you, as doing that is way, way easier than trying to engineer an audible captcha. -- 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] New SQLite Forum requires Javascript?
On 3/13/20, Warren Young wrote: >> >> Is there a way to use this without running the Javascript? > > It should be, but I lack the time right now to test it. What I can do is > point you to our documentation on how Fossil uses Javascript now: > > https://fossil-scm.org/fossil/doc/js-use-doc/www/javascript.md > > If there’s some problem that prevents you from using the forum without > JavaScript, we’ll certainly consider it a significant issue to be addressed. As far as I know, the forum only uses javascript to scroll to the most recent posting when you load a new thread. So if you don't mind scrolling manually, I think everything else will just work. Did you try it? -- 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] New SQLite Forum established - this mailing list is deprecated
On 3/13/20, Huỳnh Trần Khanh wrote: > [On a mailing nlist] I can > filter the posts, sort them, search through them, archive them, > forward them to a friend, You can do all of that with the SQLite Forum. Remember, all content is still delivered directly to your in-box, just like with a mailing list, so anything you can do with content received from a mailing list can also be done with content from the forum. But there are many things that the forum provides that a mailing list does now. For example, if you want an archive of the forum activity, you can clone the entire history with one command: fossil clone https://sqlite.org/forum sqlite-forum.fossil Then periodically "sync" to keep your private archive up-to-date. Now you have all historical content, neatly packaged in an SQL database. You can extract and search and manage the content in this archive in any way you want. The only thing that you can do with a mailing list that the SQLite Forum does not allow is to submit new postings via email. You must use the web interface in order to post a message. In my experience, this forces people to take a little extra time to think about what they are saying, and to format and arrange their thoughts for clarity, and hence results in a better experience for the readers. There are other important features that the forum provides that mailinglists typically do not: 1. You can format your postings using Markdown 2. You can add hyperlinks to your postings that are consistently displayed and are not dependent on the idiosyncrasies of various email clients. 3. You can edit prior posts to fix typos or mistakes. 4. Your email address is never displayed, even to subscribers. 5. It is much easier to contribute anonymously to a web-based forum than it is to contribute on a mailing list. There is no verification process to go through. You just type in what you want to say and press "Submit". 6. Moderators have much better control over spam and other malicious content. The first point (use of Markdown) is the killer feature for me. There was a recent thread on this mailing list that involved people posting EXPLAIN output. That text gets hopelessly jumbled on most email readers. If those messages had been formatted with Markdown, they would have been much easier to read and understand. I've been using both this mailing list and the Forum on Fossil regularly for two years now. The forum is so much nicer that I have come to dread having to work with the legacy mailing list, at least for complex subjects. It is time for a switch to better technology. -- 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] New SQLite Forum established - this mailing list is deprecated
On 3/12/20, no...@null.net wrote: > I am wondering what (apparently invisible) > anti-spam features are present. I will be happy to discuss that, and any other questions you have, on the Forum. :-) -- 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] New SQLite Forum established - this mailing list is deprecated
I have set up an on-line forum as a replacement for this mailing list: https://sqlite.org/forum https://www.sqlite.org/forum/forumpost/a6a27d79ac Please consider subscribing to the new Forum. The intent is that the forum will eventually replace this mailing list. The Forum is powered by Fossil. It has been in active use in the Fossil community for a couple of years, and has worked well. See the second link above for more information. -- 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] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes
On 3/12/20, Doug wrote: > > Something triggered me when I looked at the generated code: you use the > contents of register 2 for the constant value each time through the loop. > What if the select looks like this, with more than one function call in the > coalesce? Do you handle it properly? > > SELECT coalesce(a, abs(0), abs(1), abs(2)) FROM t1; addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 23000 Start at 23 1 OpenRead 0 2 0 1 00 root=2 iDb=0; t1 2 Explain2 0 0 SCAN TABLE t1 00 3 Rewind 0 22000 4 Column 0 0 100 r[1]=t1.a 5 NotNull1 20000 if r[1]!=NULL goto 20 6 Once 0 9 000 7 Integer0 3 000 r[3]=0 8 Function 1 3 2 abs(1) 00 r[2]=func(r[3]) 9 SCopy 2 1 000 r[1]=r[2] 10 NotNull1 20000 if r[1]!=NULL goto 20 11 Once 0 14000 12 Integer1 5 000 r[5]=1 13 Function 1 5 4 abs(1) 00 r[4]=func(r[5]) 14 SCopy 4 1 000 r[1]=r[4] 15 NotNull1 20000 if r[1]!=NULL goto 20 16 Once 0 19000 17 Integer2 7 000 r[7]=2 18 Function 1 7 6 abs(1) 00 r[6]=func(r[7]) 19 SCopy 6 1 000 r[1]=r[6] 20 ResultRow 1 1 000 output=r[1] 21Next 0 4 001 22Halt 0 0 000 23Transaction0 0 1 0 01 usesStmtJournal=0 24Goto 0 1 0 00 -- 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] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes
On 3/12/20, Doug wrote: > Richard, what does the explain look like with your code change, please. Test case: CREATE TABLE t1(a); explain SELECT coalesce(a, abs(-9223372036854775808)) FROM t1; Before the change: addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 10000 Start at 10 1 OpenRead 0 2 0 1 00 root=2 iDb=0; t1 2 Explain2 0 0 SCAN TABLE t1 00 3 Rewind 0 9 000 4 Column 0 0 100 r[1]=t1.a 5 NotNull1 7 000 if r[1]!=NULL goto 7 6 SCopy 2 1 000 r[1]=r[2] 7 ResultRow 1 1 000 output=r[1] 8 Next 0 4 001 9 Halt 0 0 000 10Transaction0 0 1 0 01 usesStmtJournal=0 11Int64 0 3 0 -9223372036854775808 00 r[3]=-9223372036854775808 12Function 1 3 2 abs(1) 00 r[2]=func(r[3]) 13Goto 0 1 000 Notice that the abs() function is invoked in the "prologue" code. The prologue begins on instruction 10 and continues through the Goto at instruction 13. After the change: addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 13000 Start at 13 1 OpenRead 0 2 0 1 00 root=2 iDb=0; t1 2 Explain2 0 0 SCAN TABLE t1 00 3 Rewind 0 12000 4 Column 0 0 100 r[1]=t1.a 5 NotNull1 10000 if r[1]!=NULL goto 10 6 Once 0 9 000 7 Int64 0 3 0 -9223372036854775808 00 r[3]=-9223372036854775808 8 Function 1 3 2 abs(1) 00 r[2]=func(r[3]) 9 SCopy 2 1 000 r[1]=r[2] 10 ResultRow 1 1 000 output=r[1] 11Next 0 4 001 12Halt 0 0 000 13Transaction0 0 1 0 01 usesStmtJournal=0 14Goto 0 1 000 Now the prologue is just instructions 13 and 14 and omits the abs() function. The abs() function is now computed on instructions 7 and 8, but those instructions only run one time due to the "Once" opcode on instruction 6. -- 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] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes
> 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 > -- 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] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes
On 3/11/20, Hick Gunter wrote: > While ORACLE does state that COALESCE will short circuit, A similar problem was reported on a ticket here: https://www.sqlite.org/src/tktview?name=3c9eadd2a6 (The problem reported on that ticket might not seem to be the same at first glance, but deep down they are both the same issue.) I have a patch to fix the problem on a branch (https://www.sqlite.org/src/timeline?r=do-not-factor-functions) which you can experiment with. More changes and analysis are needed prior to landing on trunk. I cannot guarantee that such a landing will in fact occur, though it seems more likely than not at this point. -- 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] sqlite 3.31.1 crashes in SVN on OpenBSD/sparc64
On 3/11/20, Stefan Sperling wrote: > > Does this help? > Studying the trace output makes me think this is the same problem that came up on the s390 hardware and was fixed here: https://www.sqlite.org/src/info/04885763c4cd00cb Stefan: Can you verify that the patch above fixes the problem? If you want, you can click on the "Tarball" link to get a complete tarball of check-in 04885763c4cd00cb (which, by coincidence was the first check-in *after* the 3.31.1 release), unpack the tarball, then do "./configure && make sqlite3.c" to generate the "sqlitle3.c" and "sqlite3.h" files. Or, you can update the "sqlite3.c" file you are currently using manually entering the 3-line patch shown. Please let us know what you find. -- 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] sqlite 3.31.1 crashes in SVN on OpenBSD/sparc64
On 3/11/20, Stefan Sperling wrote: > > Does this help? > It does help some. But I need a good copy of the database schema, which I don't have. Please bring up the database file in the "sqlite3" command-line shell, then enter the command ".fullschema". Save off the results and send them to me, please. -- 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] sqlite 3.31.1 crashes in SVN on OpenBSD/sparc64
On 3/10/20, Stefan Sperling wrote: > The query being executed is "STMT_INSERT_NODE": > -- STMT_INSERT_NODE > INSERT OR REPLACE INTO nodes ( > wc_id, local_relpath, op_depth, parent_relpath, repos_id, repos_path, > revision, presence, depth, kind, changed_revision, changed_date, > changed_author, checksum, properties, translated_size, last_mod_time, > dav_cache, symlink_target, file_external, moved_to, moved_here, > inherited_props) > VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, > ?15, ?16, ?17, ?18, ?19, ?20, ?21, ?22, ?23) It would be great if you could send us the database schema - or even the whole database if it isn't too big. You can send to my private email if you like. Probably if I have the schema I will be able to repro the problem. Is this only happening on Sparc, and not on x64 or ARM or PPC, etc? Does valgrind give any clues? Something else that might be helpful, if you have time: Set a breakpoint on the sqlite3VdbeExec() function that fires when the statement that is crashing is first executed. (You can determine that it is the correct statement by looking at the value of p->zSql.) Then do: set p->db->flags = p->db->flags | ((0x060)<<32) That will turn on bytecode listing and tracing, and might provide further clues. Please record and send in the trace. -- 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] Is this an SQL parsing / ambiguity bug ?
On 3/10/20, Simon Slavin wrote: > On 9 Mar 2020, at 8:40pm, Vladimir Vysotsky wrote: > >> sqlite> insert into dst(id) select id from src on conflict do nothing; >> Error: near "do": syntax error > > SQLite does not understand "DO NOTHING". You probably want "ON CONFLICT > IGNORE". I think Vlad is trying to do an upsert, which does have a DO NOTHING syntax that I copied from postgres. The parsing ambiguity that Vlad is complaining about (if I understand him correctly) is documented at the bottom of the upsert page: https://www.sqlite.org/lang_UPSERT.html#parseambig -- 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] sqlite3: .width counts bytes, not characters
On 3/9/20, Keith Medcalf wrote: >>I use sqlite3 (sqlite3 --version = "3.11.0 2016-02-15 17:29:24 >>3d862f207e3adc00f78066799ac5a8c282430a5f" on Ubuntu 16.04.6 LTS) for > > Have you tried version more recent than 4 years and 1 month old? > I think some of these issues may have been fixed in the last couple of > years. I think the enhancement is here: https://sqlite.org/src/timeline?c=ed0842c156ab1a78 That would correspond to version 3.20.0. -- 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] Custom VFSes and PENDING_BYTE position
On 3/9/20, Alexey Alyaev wrote: > > I have been working on a custom VFS project since 2017 > > would it not be reasonable to allow setting a different > lock byte offset just in windows/unix VFS, while keeping the lock-byte page > position untouched? > > This would allow VFSes such as ours to move the lock-byte region used by > default OS VFS, outside of the database segment, say into the head space > which we have allocated and reserved for meta. In the default implementation, the location of the PENDING_BYTE is set by a global variable sqlite3PendingByte. If you want to change that global variable, you can. Be warned, however, that changing the location of the pendingByte results in an incompatible file format. You will also run into severe problems (assertion-faults and/or segfaults) if you set the pendingByte to some value that is not a multiple of the page size or that overlaps with page 1 of the database file. Aside: The PENDING_BYTE location can be adjusted in this way for testing purposes. Even release builds are able to move the PENDING_BYTE (using the sqlite3_test_control() interface) since our dogma is "fly what you test and test what you fly." Maybe a better solution for you would be to modify your custom xRead() and xWrite() routines so that the skip over the bytes in the range of 0x4000 through 0x4200. If the offset is greater than or equal to 0x4000 just add 512 to the offset before continuing. Depending on your circumstances, I suppose you would also have to do something similar with xTruncate() and xFileSize(). -- 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] Bug in SQLite version 3.31.1 and 3.32?
On 3/6/20, Xinyue Chen wrote: > -- Buggy query > select t1.textid a, i.intid b > from t t1, > i i > where (((t1.textid = i.intid) and (t1.textid in (12)) and (t1.textid = > i.intid)) or ((t1.textid = null) IS NOT FALSE)) > group by i.intid, t1.textid; (1) The expression "t1.textid=null" is always NULL. (2) The expression "NULL IS NOT FALSE" is always true. (3) The WHERE clause expression "... OR true" is always true. Hence, the query above simplifies to just "SELECT * FROM t, i;". That query should return 4 rows, just as you show. I think it is working correctly. -- 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] Updates to althttpd.c for LetsEncrypt compatibility
TL;DR: If you are using althttpd.c on your website, you will need to get the latest code and recompile before the next time you need to get a cert from LetsEncrypt. There are no (known) vulnerabilities or problems with althttpd.c. This is merely an update for LetsEncrypt compatibility due to recent changes in the LetsEncrypt certbot. Background: The althttpd.c webserver has nothing to do with SQLite, except for the fact that it was created to host the https://sqlite.org/ website, and the source code to althttpd.c is hosted on the SQLite documentation repository. See the althttpd documentation and source code here: https://www.sqlite.org/docsrc/doc/trunk/misc/althttpd.md https://www.sqlite.org/docsrc/doc/trunk/misc/althttpd.c Reason for the change: Today, I was notified by LetsEncrypt that they will be revoking some certs because of a bug in their website validation system. The cert for sqlite.org was among those being revoked. Owners of those certs were advised to get a new cert before tomorrow. But in the meantime, LetsEncrypt has modified their certbot so that it no longer worked with the legacy althttpd. Althttpd takes certain security precautions that are incompatible with the new LetsEncrypt certbot. So, in order to get a new cert, althttpd had to be modified to make an exception to the security precautions for LetsEncrypt. So, if you are one of the handful of people who are using althttpd.c for your own website, you should probably download the new althttpd.c source file and recompile. You will almost certainly need to do this before you get your next cert from LetsEncrypt. And you might need to do that before tomorrow. -- 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] Report bug found in SQLite version 3.31.1
On 3/2/20, Keith Medcalf wrote: > > Perhaps this is the same constant propagation bug that was fixed recently? > So it seems. https://sqlite.org/src/timeline?bid=ya65c8d4e26n3bfa9cc97dn7d8dcfb95cy14d14eb537y109ee07433nabfb043ebbne0c6b8bdb7yc9a8defcef -- 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] VFS xFullPathname cannot access URI parameters
On 2/26/20, Chris Warner wrote: > In 3.31.1, attempting to access URI Parameters via the sqlite3_uri_* > functions inside a VFS xFullPathname function returns junk. This was not an > issue in 3.30.1 or 3.31.0. > SQLite does not promise that sqlite3_uri_parameter() will work for the argument passed into xFullPathname. But it has done so historically, and (apparently) some software has come to depend on that accidental behavior. So I have implemented changes on trunk, and on branch-3.31 to make it work again. -- 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] Trigger name missing
On 2/26/20, Simon Slavin wrote: > > Backward compatibility ? Do you think anyone who used the word AFTER > really wants a BEFORE trigger ? More likely to be a bug they should know > about. We have seen triggers like this in the wild, that work as intended. If we change it to throw an error, the applications that use these kinds of triggers will suddenly start failing. Some of them (no doubt) are unmaintained. The source code has gone missing for some of them, perhaps. That much breakage is not worth it. The issue arises from the forgiving nature of the SQLite parser. The parser is designed so that we can add new keywords to the language (for example: "GENERATED" and "ALWAYS" in the most recent release, in support of generated columns) without breaking legacy schemas that use those keywords as table or column names. Consider what happens in the example Dan provide: CREATE TRIGGER AFTER INSERT ON t1 BEGIN ... END; The parser is bebooping along, parsing tokens one by one. The first token is the keyword CREATE. The second token is the keyword TRIGGER. All good so far. The third token is the keyword AFTER. But the grammar does not recognize the keyword AFTER in that context, and so the parser converts it into an identifier with the value of "AFTER". That does work, and so the parse continues, using "AFTER" as the name of the trigger. That example is a little confusing. But what if, instead, the trigger has been this: CREATE TRIGGER generated INSERT ON t1 BEGIN ... END; With strict enforcement of keywords, this trigger would have worked fine for all versions of SQLite through 3.30.1 and then started failing in version 3.31.0, because it was in that release that GENERATED became a keyword. But with the "fallback" mechanism in SQLite, the trigger continues to work as it always has. That is why the mechanism exists - to prevent unnecessary breakage when new keywords are added to the language. There are literally millions of applications that use SQLite. Some fraction of those are unmaintained. And some additional fraction of those will break, probably to never work again, whenever we add a keyword, except for the keyword fallback mechanism. -- 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] Trigger name missing
On 2/26/20, Jean-Luc Hainaut wrote: > Hi all, > > It seems that SQLite (version 31.1) accepts a trigger declaration in > which the name is missing. When fired, this trigger doesn't crashes but > exhibits a strange behaviour. In particular, while expression > "new." in an "insert" trigger returns the correct value, the > equivalent expression "select from T where Id = new.Id" always > returns null (column "Id" is the PK of table "T"). Similarly, "update T > set = where Id = new.Id" (silently) fails. > What is the text of your trigger? -- 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] sqliteDefaultBusyCallback and HAVE_USLEEP
On 2/25/20, Peter Kolbus wrote: > I noticed that sqliteDefaultBusyCallback() seems to depend directly on the > OS (behave differently based on SQLITE_OS_WIN||HAVE_USLEEP). Since the > underlying primitive, sqlite3OsSleep(), actually uses the VFS to sleep, and > unixSleep() also has a roundup to whole seconds when HAVE_USLEEP is not > defined, any time resolution limitations are already handled there. And when > a custom VFS is configured, that VFS may well be able to sleep in milli or > microseconds using an RTOS-specific function that is not usleep() — for > example FreeRTOS has osDelay(). > > Is there a reason sqliteDefaultBusyCallback() has this dual implementation, > and defining HAVE_USLEEP is correct to get better performance on platforms > that don’t have usleep()? Or could it be simplified? > I don't think I understand the question. It sounds like you are asking why SQLite does not try to sleep for sub-second intervals on systems that do not support usleep()? -- 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] more efficient JSON encoding: idle musing
On 2/21/20, Wout Mertens wrote: > The idea is that upon storing the JSON > data, the JSON1 extension parses it, extracts the layouts recursively, > stores them when they are not known yet, and then only stores the > values in the binary format with the layout identifiers. I experimented with a number of similar ideas for storing JSON when I was first designing the JSON components for SQLite. I was never able to find anything that was as fast or as compact as just storing the original JSON text. But I could have overlooked something. If you have example code for a mechanism that is more space efficient and/or faster, please share it with us. -- 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] Is randomblob(16) a good guid generation across multiple computers?
On 2/21/20, Andy KU7T wrote: > Are you saying the PRNG on Windows is not good enough to use > randomblob(16) in Sqlite? All I need is a reasonable assurance that is are > unique... The default PRNG on Windows is fine for generating globally unique identifiers. The complaint is that the seeding of the PRNG on Windows is such that an attacker could by brute force discover the seed of the PRNG by examining a sequence of generated UUIDs. In that scenario, the attacker might be able to guess the next UUID that your system will be generating. If that is a problem for your application, then fix it by compiling with -DSQLITE_WIN32_USE_UUID=1 and linking against RPCRT4.LIB. -- 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] Is randomblob(16) a good guid generation across multiple computers?
On 2/20/20, Keith Medcalf wrote: > > randomblob(16) does not generate a valid UUID (it does not set the version > and variant flags in the resulting 16-bytes of random data). If you need a UUID in the "standard format", rather than just an ID that its universally unique, you can use the uuid.c extension: https://www.sqlite.org/src/artifact/5bb2264c1b64d163 -- 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] Is randomblob(16) a good guid generation across multiple computers?
On 2/20/20, Andy KU7T wrote: > Hi, > I added a randomblob(16) to each record of a Sqlite table via a trigger with > the goal of global uniqueness. Is that the correct approach or would it be > better to pass Guid from .Net? I am using System.Data.Sqlite. The following > article got me questioning the usage of randomblob: > https://devblogs.microsoft.com/oldnewthing/20120523-00/?p=7553 The author of that article, "Raymond", assumes that the random number generator in the SQL database engine is not cryptographically strong. That assumption is not correct for SQLite, which does you a cryptographically strong PRNG. And the SQLite PRNG is seeded from /dev/random on unix. Now, on Windows systems, the seeding is not quite as strong as it is on unix (unless you compile with -DSQLITE_WIN32_USE_UUID) but it is still sufficient to reduce the changes of a collision between two randomblob(16) calls to practically zero. So, I think randomblob(16) is a fine way to generate a UUID. Though, I tend to use randomblob(20), and I often run it through hex() too, so that it is human-readable. -- 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] Does VACUUM imply REINDEX?
On 2/19/20, Olivier Mascia wrote: > Hello, > > As part of a rare database "maintenance"... > Does VACUUM, in essence, implies whatever actions REINDEX would do? > Would REINDEX before VACUUM make any more sense? > > And what about ANALYZE? Would it be wise or useless, to run it after VACUUM? VACUUM does not imply REINDEX. Those are separate actions. VACUUM repacks all the content in to minimal space, and thus (probably) makes the database file a little smaller. It also reorganizes the pages so that tables are grouped together and are in search order, which might make table scans faster, depending on the underlying filesystem. REINDEX rebuilds an index from scratch based on the original table data. This amounts to a big sort. REINDEX was invented so that if you change the definition of a collating function, you can run REINDEX on all indexes that use that collating function and it will reorder the indexes into the new correct order. REINDEX will also fix any index corruption. But if you have index corruption, that is a symptom of deeper problems which should be addressed. You should not paper-over such problems by running REINDEX. VACUUM is independent of ANALYZE. The information computed by ANALYZE is the same before and after VACUUM. There is no benefit to running ANALYZE after running VACUUM if the ANALYZE data (the content of the sqlite_stat1 table) is still up-to-date. -- 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] last_insert_rowid() returns every growing list
On 2/17/20, The Tick wrote:
> sql eval { insert into test (id, number, data) values( $a, $b, $c ) }
What were you hoping to accomplish here? It seems like you might be
wanting the rowid of the last insert by any database connection into
the "test" table. If so, that is not what last_insert_rowid() does.
The last_insert_rowid() is the rowid of the most recent insert from
the current database connection into *any* table. The SQLite database
file does not keep track of the order of inserts, and so it cannot
determine the last insert on any particular table. It only has that
information for an individual connection. And it only keeps a single
integer which applies to the most recent insert, regardless of what
table was inserted into.
If you need to track the last insert into individual tables, and do so
globally, you can accomplish that using triggers.
To get the last_insert_rowid() from TCL, it is faster to use the "db
last_insert_rowid" TCL command.
--
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] Is it possible to dump a sqlite db that has an associated -wal file?
On 2/16/20, Peng Yu wrote: >> Does it work when you close Firefox? If it works when Firefox is closed >> but >> not when Firefox is open, then the answer is probably no. > > I can check the content when Firefox is closed (the -wal file > disappears after Firefox is closed). > > Why the database can not be read by another sqlite3 session when the > corresponding -wal file exists? Thanks. Because Firefox uses "PRAGMA locking_mode=EXCLUSIVE;" -- 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] WITHOUT ROWID tables
On 2/14/20, Wayne Collins wrote:
> My first posting so I hope I get the etiquette correct.
>
> 1st question How can I determine from a C-program if a table was
> generated with the "WITHOUT ROWID" option? I have an application where
> it is important to know if a table has a rowid or not.
To determine if table XYZ is a WITHOUT ROWID table, run "PRAGMA
index_info('XYZ');". If you get back one or more rows, then XYZ is a
WITHOUT ROWID table. If you get back no rows, then XYZ is a rowid
table.
>
> 2nd Question After a database is defined and the tables created, I have
> an application which automatically generates C++ DAO and DTO objects for
> each table. At run time, I'd like to be certain that the schema and the
> DAO/DTO match. I had thought to use the schema_version pragma but it
> increments when the db is VACUUMED. Is there some hidden cookie or value
> I can use that I have overlooked?
>
There are no magic cookies that change with the schema but not with VACUUM.
Perhaps you could store a strong hash of the sqlite_master.sql column
for each table with your C++ objects, then compare the hash upon
reconnect to see if anything has changed.
--
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] Incorrect join result with duplicate WHERE clause constraint (again)
On 2/13/20, Jim Bosch wrote: > https://gist.github.com/TallJimbo/d819876a77cfd79312ad48508cfdd8a2 Thanks for the very succinct bug report. The problem is now fixed on trunk. https://www.sqlite.org/src/timeline?c=c9a8defcef35a1fe -- 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] Compiling SQLite Encryption Extension for ARM processor
On 2/13/20, Subodh Pathak wrote: > > I am trying to compile SEE for ARM processor. There is a website explain how to compile SQLite for Android here: https://www.sqlite.org/android/doc/trunk/www/index.wiki Please review the instructions on that website and write again if they do not work for you. 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] O_NOFOLLOW issue with /dev/null on Solaris
On 2/13/20, jakub.ku...@oracle.com wrote: > > Recently, O_NOFOLLOW was added to several calls into robust_open(). In > that function, if the fd returned by open() is too low (in the stdio > range 0-2), then it closes it, and opens /dev/null to pad out the fd's > until we reach at least fd#3. Background information: That mechanism was added as a defense again application bugs causing database corruption. See paragraph 1.1 of the "How To Corrupt an SQLite Database" document: https://www.sqlite.org/howtocorrupt.html The fact that you are hitting this problem suggests that there is something wrong with your application. Thanks for the suggested improvements to SQLite. A patch for this will appear in the next release. Or you can use the latest trunk check-in. https://www.sqlite.org/src/timeline?c=0c683c43a62fe25c -- 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] JSON_EACH + recursive query = unexpected performance degradation
On 2/12/20, David Raymond wrote: > Not necessarily related to the question itself, but how did the attachments > actually come through with this mail? Every single other person to try and > attach something to this list has had it stripped off. Sometimes Mailman asks me for approval for messages containing attachments. I think this depends on the size and mimetype of the attachments. In this case, I was asked and I approved. -- 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] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER
On 2/11/20, J. King wrote: > SQLite also has a 1M byte statement > length limit ... The statement length limit is yet another defense against mischief caused by SQL injections. -- 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] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER
On 2/10/20, Digital Dog wrote: > Maybe they should be treated as a > dictionary/hashtable/linked list or similar? > Parameter look-ups are on the critical path. How much performance are you willing to give up in order to have parameters with larger numbers? -- 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] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER
On 2/10/20, Digital Dog wrote: > > Nobody bothered to actually show the downside of increasing this value to > e.g. 10 thousands but everybody immediately proceeded to grumble. > What is the justifiable rationale to not change the default? The maximum number of variables used to be unlimited (or, at least, only limited by the ability of a 32-bit integer to count them). But that was shown to facilitate a DOS attack. Anyone who could inject SQL could do "SELECT ?2147483647;". This would cause SQLite to allocate an array of 2147483648 elements, each of which is 72 bytes in size, in which to store all of the parameters, potentially using up all available memory in the process. Even ?32767 uses a big chunk of heap memory - more than embedded systems people are comfortable giving up - especially if the attacker can trick the system into creating multiple prepared statements with ?32767 in them. The allocated array lives for the life of the prepared statement. -- 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] loading extension csv.c
On 2/7/20, chiahui chen wrote: > > I tried the suggested solution. sqlite3 was > downloaded and compiled. > Then ran : gcc -g -l. -fPIC -dynamiclib ./ext/csv.c -o csv.dylib > the same errors occurred again. > > Is there any suggestion? Just in case if my Mac operating system provides > some info (macOS High Sierra version 10.13.6) > That command works fine for me on my iMac. I have no idea why it is failing for you. -- 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] loading extension csv.c
On 2/6/20, chiahui chen wrote: > Hi, > > After few attempts to load csv extension, (already tried > > gcc -arch x86_64 -g -fPIC -dynamiclib csv.c -o csv.dylib > > and > > gcc -g -fPIC -dynamiclib csv.c -o csv.dylib > ) > Mac terminal returns errors as followings: I don't know what is causing your errors. But I can show you what works for me, on a Mac: (1) Unpack the SQLite source-code tarball for version 3.31.1 (2) Run: ./configure && make (3) Run: gcc -g -I. -fPIC -dynamiclib ext/misc/csv.c -o csv.dylib (4) Run: ./sqlite3 (5) Within the SQLite command-line shell, do: .load ./csv I suspect that the -I. is the option that you need, so that the build will pick up a proper sqlite3ext.h file, and not whatever sqlite3ext.h file that Apple has installed. But that is just my guess. -- 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] Double return Issue in sqlite3_filename_database
On 2/6/20, Damian Wilson wrote: > > Probably benign, but nice if it was cleaned up. > Correct. It is benign - identical machine code is generated with and without the extra statement. And, curiously, the problem was not detected by GCC even with -Wall -Wextra -pedantic-errors. Thanks for pointing it out. -- 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] Check constrain execution timing change? (Now a bug)
On 2/3/20, Dominique Devienne wrote:
> On Sun, Feb 2, 2020 at 12:50 AM Richard Hipp wrote:
>
>> On 2/1/20, Thomas Kurz wrote:
>> >
>> > create table x (x integer check (typeof(x) == 'integer'));
>> > insert into x values ('1');
>> >
>> > --> will pass in future versions???
>>
>> I think that is what it means. yes.
>
> Wow... I haven't caught up on this thread, but that's really really bad
> IMHO,
> and would consider that a serious regression. I've been enforcing
> "strong-typing",
> (or "inflexible-typing" if you prefer Richard) for many schemas, and
> the fact we can
> no longer do that would be a real shame. I wonder where this is coming
> from... --DD
This is the SQL:
CREATE TABLE t1(x INT CHECK(typeof(x)=='integer'));
INSERT INTO t1(x) VALUES('123');
You say that you want to prevent the use of the string literal '123'
for inserting into the integer field x. That will no longer be
possible in SQLite beginning with 3.32.0 (assuming the change
currently on trunk goes through.)
But, why do you want to do that? How do you prevent the use of a
string literal to initialize an integer field in MySQL, PosgreSQL, SQL
Server, and Oracle - all of which accept and run the SQL above
(without the CHECK constraint) with no errors?
If your goal is to prevent an actual string from being stored in the
"x" column, then the legacy CHECK constraint still works for that.
The following insert still fails:
INSERT INTO t1(x) VALUES('xyzzy');
But, you will no longer be allowed to prevent the type coercion that
forces the '123' value into an integer 123, I think. At least, I do
not see a way to do that on trunk right now.
I have put a "Pre-release Snapshot" of the latest code on the Download
page to try to make it easier for people to try out this new change.
--
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] Check constrain execution timing change? (Now a bug)
On 2/2/20, Thomas Kurz wrote:
> And are there any consequences for something like
>
>> create table x (x text check (typeof(x) == 'text'));
>> insert into x values ('1');
>
Not that I know of.
--
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] Check constrain execution timing change? (Now a bug)
On 2/1/20, Thomas Kurz wrote:
> Does this mean there will be no possibility to prevent inserting a string
> into an integer column anymore?
>
> create table x (x integer check (typeof(x) == 'integer'));
> insert into x values ('1');
>
> --> will pass in future versions???
I think that is what it means. yes.
--
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] Check constrain execution timing change? (Now a bug)
On 1/31/20, Keith Medcalf wrote: > > That would elevate this to the status of a bug since it should be impossible > to do this. > It is also not something that is fixable, so the solution will likely be to simply document it. -- 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] Check constrain execution timing change?
CREATE TABLE t1(x INTEGER CHECK(typeof(x)=='text'));
INSERT INTO t1 VALUES('123');
PRAGMA integrity_check;
On 1/31/20, Graham Holden wrote:
> Friday, January 31, 2020, 9:39:07 PM, Simon Slavin
> wrote:
>
>> On 31 Jan 2020, at 9:27pm, Keith Medcalf wrote:
>
>>> You are however correct that this is an "application consistency"
>>> problem more than an SQLite problem and it is a lot of change for
>>> little actual benefit.
>
>> How about this ?
>
>> A program (possibly part of the SQLite precompiled suite, possibly
>> from a third party) goes through any SQLite database as an integrity
>> checker. One of its jobs is to check that column constraints
>> are not violated. This cannot possible be wrong. A SQLite database
>> with violated CHECK clauses is, by definition, corrupt.
>
>> However, because the checks are performed on the values input, not
>> the values stored, some data in the database does violate a CHECK
>> constraint.
>
>> Can this happen, given the behaviour Keith identified ? If so, I
>> would say that something is wrong.
>
> Probably not, at least not with the example he used.
>
> In Keith's example (assuming I understand it correctly), you are
> stopped from inserting/updating something that -- if you did the
> insertion/update WITHOUT the checks -- would be stored in such a
> way that it would later pass "pragma integrity_check" if the checks
> were in place.
>
> So, inserting '1' (a string) into a field with integer affinity, but
> no constraints would be allowed, and result in 1 (an integer) being
> stored.
>
> If that column were to magically gain a "check (typeof(x) == 'integer')"
> constraint, it would pass "pragma integrity_check" (because by now it
> contains an integer, not the string that was originally inserted).
>
> If you now repeated the original insert (of the string '1') with the
> check constraint in place it will now REJECT the insertion, because
> the type-of-the-thing-being-inserted doesn't meet the constraint (it's
> a string).
>
> So it doesn't allow you to create an inconsistent database (as defined
> by check constraints), but does stop some ways of inserting/modifying
> data that would have created valid data if the checks weren't there.
>
> Graham
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
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] Check constrain execution timing change?
On 1/31/20, Keith Medcalf wrote: > > The check should occur AFTER defaults and > column affinity is applied before the data record is stored > Why do you think this? Is it documented somewhere? I ask because your test case gives the same answer (doing the first insert but failing the other two) for every version of SQLite I checked from trunk going back to 3.1.0 (2005-01-21). Hence, for backwards compatibility, even it is documented to do something differently, I should probably change the documentation rather than the behavior. -- 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] Generated columns and COLLATE in the AS parens
On 1/31/20, Markus Winand wrote: > > - Expressions also have a declared type. More elaborate: the result value of > an expression has a type that is statically determined (again the “declared > type”). SQLite is a generalization of "Standard SQL" that allows expressions that can have an arbitrary type. Some (most?) expressions in SQLite will always return the same type, but it is not hard to construct an SQLite expression that returns different types based on its inputs. You can also create application-defined functions including table-valued functions that return different types based on their inputs. Internally, SQLite makes no attempt to track the datatype of an expression. Such tracking could be added, but doing so would just make the library bigger and slower without serving any useful purpose. > > - If a generated column doesn’t have an explicit data type mentioned, the > the data type of the result of the expression is used. Having no explicit data type is a kind of explicit data type in SQLite, since any SQLite column (generated or otherwise) can omit the data type specification and the data type affinity becomes the equivalent of "ANY". From that point of view, SQLite is like PG in that it requires an explicit data type on generated columns, though SQLite extends PG by allowing that explicit data type to be the unnamed "ANY" data type. And since the data type is always implicit in the column definition, there is never an occasion to inherit the data type from the AS expression. Furthermore, SQLite expressions do not have data types, so even if a generated column were said to omit the data type, there would be no way of capturing it from the AS expression. -- 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] Generated columns and COLLATE in the AS parens
On 1/30/20, Markus Winand wrote: > > Unfortunately, the “what would PostgreSQL do” approach doesn’t provide > guidance here. Maybe it does. PostgreSQL doesn't allow typeless columns, but it does allow columns with unspecified collating sequences, does it not? What if you have a normal column X with some collating sequence C and then a generated column Y that as just "AS(X)". If you do comparisons on column Y, which collating sequence does it use - the default or C? Can you run that experiment for us? Or maybe you are thinking the collating sequence of the expression in the AS clause should only be carried through into the generated column if it is explicitly stated, and not implied? What happens if there is a collating sequence specified in the AS clause and also another collating sequence on the column definitions? CREATE TABLE t1(x TEXT, y TEXT AS (x COLLATE nocase) COLLATE rtree); Which collating sequence should be used for "SELECT * FROM t1 WHERE y='xyz';"? Regarding WWPD: If you can convince Tom Lane and/or Bruce Momjian to send me an email that says "An explicit collating sequence at the top-level of a GENERATED ALWAYS AS clause should be carried through as the default collating sequence of the generated column itself", then I'll change it. :-) -- 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] Behavior change around triggers between 3.19 and 3.30
On 1/30/20, Dominique Devienne wrote: > > My first question would be to ask whether there's a pragma or > compile-time option to get back to the old behavior? Did you try "PRAGMA legacy_alter_table=ON;"? > > Second, any idea when this was introduced? > People have been requesting enhanced ALTER TABLE support. In order to provide that, we had to change ALTER TABLE to do a full parse of the entire schema, so that it can find all of the bits and pieces that need altering. This means that ALTER TABLE now also finds latent syntax errors in the schema. -- 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] Generated columns and COLLATE in the AS parens
On 1/29/20, Markus Winand wrote:
> Hi!
>
> I think there might be a glitch in the way SQLite 3.31.x derives the
> collation information from the expression of a generated column.
I think the current behavior is correct.
If you want a column to have a non-standard collating sequence, you
should add a COLLATE constraint to that column definition. The fact
that there is a COLLATE operator on the expression that determines the
value of that column seems irrelevant.
Consider this:
CREATE TABLE t1(a TEXT DEFAULT('xyzzy' COLLATE nocase));
Would you expect the COLLATE operator in the DEFAULT clause to change
the collating sequence associated with column a? Why should a
GENERATED ALWAYS AS constraint work differently from a DEFAULT
constraint?
--
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] IO Short Read Error when inserting a TEXT column
On 1/28/20, rgarnett wrote: > the step function fails > with an IO Error Short Read.The VFS I am using I developed myself from the > demo on the sqLite website. I suspect there may be problems with this code I suspect you are correct. The SQLITE_IOERR_SHORT_READ is an error code that is only generated by the VFS. That means it is your code that is likely generating the error. I suggest you set a breakpoint on the place in your custom VFS where the SQLITE_IOERR_SHORT_READ is being generated and try to figure out what is going on. -- 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] sqlite-3.31.0 segfaults on fuzzcheck on s390x architectures
Please retry using this check-in: https://www.sqlite.org/src/info/b20503aaf5b6595a On 1/28/20, Brüns, Stefan wrote: > On Dienstag, 28. Januar 2020 18:26:05 CET Brüns, Stefan wrote: >> On Dienstag, 28. Januar 2020 16:16:01 CET Richard Hipp wrote: >> > On 1/27/20, Ondrej Dubaj wrote: >> > > Hi, >> > > >> > > I came across a problem during mate test, where fuzzcheck ends with >> > > segfault. >> > > The problem appears to be only on [s390x]. Other architectures are >> > > working fine. >> > >> > Fixed by check-in https://www.sqlite.org/src/info/04885763c4cd00cb >> > >> > Thanks for the temporary SSH login! >> >> We were seeing the problem also on other ppc64BE: >> >> ppc64 (big endian): >> fuzzdata1.db: 0% 10% 20% 30% 40% 50% 60% 70%./fuzzcheck >> /home/abuild/rpmbuild/ BUILD/sqlite-src-3310100/test/fuzzdata1.db >> (sqlid=7726,dbid=1): segfault >> >> The issue is cured with the fix, but we still see 3 failing tests with >> fts4/ >> fts5: >> >> ! fts5matchinfo-15.1 expected: [X'0200'] >> ! fts5matchinfo-15.1 got: [X'0002'] >> ! fts5matchinfo-15.2 expected: [X'0200'] >> ! fts5matchinfo-15.2 got: [X'0002'] >> ! fts4aa-6.10 expected: >> [X'02000E000E000100010001000100' >> ] ! fts4aa-6.10 got: >> [X'0002000E000E0001000100010001' >> ] > > Seems to be an endianess problem, all three affected archs are big endian > (ppc32, ppc64be, s390x): > >> ! fts4aa-6.10 expected: >> [X'0200 0E00 0E00 0100 0100 0100 >> > 0100' >> ] ! fts4aa-6.10 got: >> [X'0002 000E 000E 0001 0001 0001 >> > 0001' >> ] > > This is 3.31.1, btw. > > Kind regards, Stefan > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- 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] Bug report: Potential thread safety issues in sqlite3_initialize
On 1/28/20, Oystein Eftevaag wrote: > in sqlite3MutexInit() sqlite3GlobalConfig.mutex.xMutexAlloc > can be read as being set on a core, while the rest of the initialization > done in sqlite3MutexInit() still is being read as unset. Doesn't the memory barrier at https://www.sqlite.org/src/artifact/bae36f8af32c22ad?ln=247 prevent that? Do you have a suggested patch to make it work? -- 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] New word to replace "serverless"
On 1/28/20, Jan Danielsson wrote: > On 2020-01-28 00:19, Richard Hipp wrote: >> daemon-less? > >This is my favorite, the only problem is that it is culturally more a > Unix-y term. Since suggesting daemon-less, someone else (I'll have to research who, exactly) suggested "server-free", which I think I like more. -- 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] sqlite-3.31.0 segfaults on fuzzcheck on s390x architectures
On 1/27/20, Ondrej Dubaj wrote: > Hi, > > I came across a problem during mate test, where fuzzcheck ends with > segfault. > The problem appears to be only on [s390x]. Other architectures are > working fine. Fixed by check-in https://www.sqlite.org/src/info/04885763c4cd00cb Thanks for the temporary SSH login! -- 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] New word to replace "serverless"
On 1/28/20, Howard Chu wrote: > > Wait, really? AFAICS embedded means in-process, no IPC required to operate. > Things like MySQL-embedded and H2 run a "server" as a thread instead of as a separate process. Clients then use Inter-Thread Communication rather than Inter-Process Communication to send their queries to, and get their results from, the database thread. So this is really the same thing as a server using IPC except that the server runs in the same address space as the client. The point of using the term "serverless" is to indicate that SQLite does not work that way. -- 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] New word to replace "serverless"
daemon-less? -- 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] New word to replace "serverless"
For many years I have described SQLite as being "serverless", as a way to distinguish it from the more traditional client/server design of RDBMSes. "Serverless" seemed like the natural term to use, as it seems to mean "without a server". But more recently, "serverless" has become a popular buzz-word that means "managed by my hosting provider rather than by me." Many readers have internalized this new marketing-driven meaning for "serverless" and are hence confused when they see my claim that "SQLite is serverless". How can I fix this? What alternative word can I use in place of "serverless" to mean "without a server"? Note that "in-process" and "embedded" are not adequate substitutes for "serverless". An RDBMS might be in-process or embedded but still be running a server in a separate thread. In fact, that is how most embedded RDBMSes other than SQLite work, if I am not much mistaken. When I say "serverless" I mean that the application invokes a function, that function performs some task on behalf of the application, then the function returns, *and that is all*. No threads are left over, running in the background to do housekeeping. The function does send messages to some other thread or process. The function does not have an event loop. The function does not have its own stack. The function (with its subfunctions) does all the work itself, using the callers stack, then returns control to the caller. So what do I call this, if I can no longer use the word "serverless" without confusing people? "no-server"? "sans-server"? "stackless"? "non-client/server"? -- 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] sqlite-3.31.0 segfaults on fuzzcheck on s390x architectures
On 1/27/20, Ondrej Dubaj wrote: > The problem appears to be only on this arches. That probably means it is an EBCDIC problem. We have no way of replicating or debugging this problem as we have no access to an s390 machine. Can RedHat perhaps provide one of the SQLite developers with a temporary ssh account into an s390 machine so that we can investigate and fix the problem? -- 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] List of innocuous functions?
On 1/25/20, sky5w...@gmail.com wrote: > SELECT DISTINCT name FROM pragma_function_list > --WHERE (flags & 0x20)!=0 -- no such column: flags > ORDER BY name; > > Works if I drop the WHERE. > Is there a special compile flag that must be used? You need to be using SQLite 3.31.0 or later. -- 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] SSL: cannot connect to host www3.sqlite.org:443 (Connection refused)
On 1/25/20, Richard Hipp wrote: > On 1/25/20, Domingo Alvarez Duarte wrote: >> Hello Richard ! >> >> Since yesterday I'm getting this message when trying to use fossil for >> sqlite3. > > Yeah. That machine went completely bonkers and I had to rebuild it > from scratch, using a new IP address. And because it used a new IP > address, I have to wait 24 hours for the new DNS information to > propagate before LetsEncrypt will give me a new cert. Both https://www3.sqlite.org/ and https://www3.fossil-scm.org/ should be back up now. -- 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] CVE-2019-16168
On 1/24/20, Naumowicz, Ken E wrote: > Hello, > > I need to know if there is a security patch for this CVE on Windows Server > 2012: > > Java SE Vulnerability CVE-2019-16168 Related to JavaFX (SQLite) <<<=== > https://www.symantec.com/security-center/vulnerabilities/writeup/111496 >> NO UPDATE/PATCH FOUND at SQLite - SQLite Homepage > (https://www.sqlite.org/) > I think this CVE must be referring to a bug that allows an attacker to cause a divide-by-zero by modifying the schema and then injecting an SQL query of their own choosing. If so, that bug has been fixed in the latest release. In fact, all known bugs have been fixed in the latest release. On the other hand, I don't know of any mechanism on Windows Server 2012 by which an attacker can modify the schema of an SQLite database and then inject arbitrary SQL. So it is not clear to me that this is really a vulnerability. -- 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] 64 bits version missing
On 1/23/20, Evert van Dijken wrote: > The 64-bits DLL is missing from the download page, Which download page are you looking at? -- 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] SSL: cannot connect to host www3.sqlite.org:443 (Connection refused)
On 1/25/20, Domingo Alvarez Duarte wrote: > Hello Richard ! > > Since yesterday I'm getting this message when trying to use fossil for > sqlite3. Yeah. That machine went completely bonkers and I had to rebuild it from scratch, using a new IP address. And because it used a new IP address, I have to wait 24 hours for the new DNS information to propagate before LetsEncrypt will give me a new cert. The 24 hours have now passed, but I have to be out of the office on some other business. I'll get the new cert installed and running as soon as I a get a chance. Patience, -- 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] List of innocuous functions?
SELECT DISTINCT name FROM pragma_function_list WHERE (flags & 0x20)!=0 ORDER BY name; On 1/24/20, Peter Kolbus wrote: > Is there any documentation showing, or an easy way to generate, the exact > list of SQLite-provided functions that are innocuous? > > I’d like to turn on the new SQLITE_TRUSTED_SCHEMA but support a variety of > applications and am hoping for something to guide analysis. > > Thanks > -Peter > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- 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] Sqlite 3.31.0 breaks firefox and thunderbird
On 1/23/20, Bernhard Rosenkraenzer wrote: > Hi, > after updating sqlite to 3.31.0, both firefox and thunderbird crash on > startup (rebuilding them against the newer sqlite doesn't help). Is this related to https://bugzilla.mozilla.org/show_bug.cgi?id=1607902 > Backtrace: > (gdb) bt > #0 0x71b9fe20 in ?? () from /usr/lib64/firefox-71.0/libxul.so > #1 0x71b993d2 in ?? () from /usr/lib64/firefox-71.0/libxul.so > #2 0x7fffef3afdf5 in pagerOpenWal () from /usr/lib64/libsqlite3.so.0 > #3 0x7fffef398e76 in sqlite3BtreeBeginTrans () from > /usr/lib64/libsqlite3.so.0 > #4 0x7fffef3ed074 in sqlite3InitOne () from /usr/lib64/libsqlite3.so.0 > #5 0x7fffef3f105e in sqlite3Pragma () from /usr/lib64/libsqlite3.so.0 > #6 0x7fffef3b44ea in yy_reduce () from /usr/lib64/libsqlite3.so.0 > #7 0x7fffef397df0 in sqlite3RunParser () from > /usr/lib64/libsqlite3.so.0 > #8 0x7fffef3968a9 in sqlite3Prepare () from /usr/lib64/libsqlite3.so.0 > #9 0x7fffef396171 in sqlite3LockAndPrepare () from > /usr/lib64/libsqlite3.so.0 > #10 0x7fffef3881cf in sqlite3_exec () from /usr/lib64/libsqlite3.so.0 > #11 0x71b93ddc in ?? () from /usr/lib64/firefox-71.0/libxul.so > #12 0x71b934a4 in ?? () from /usr/lib64/firefox-71.0/libxul.so > #13 0x71b938eb in ?? () from /usr/lib64/firefox-71.0/libxul.so > #14 0x71ba2be2 in ?? () from /usr/lib64/firefox-71.0/libxul.so > #15 0x71626619 in ?? () from /usr/lib64/firefox-71.0/libxul.so > #16 0x716347b8 in ?? () from /usr/lib64/firefox-71.0/libxul.so > #17 0x71530f1f in ?? () from /usr/lib64/firefox-71.0/libxul.so > #18 0x71533006 in ?? () from /usr/lib64/firefox-71.0/libxul.so > #19 0x718a5dca in ?? () from /usr/lib64/firefox-71.0/libxul.so > #20 0x718757b8 in ?? () from /usr/lib64/firefox-71.0/libxul.so > #21 0x7152f0b5 in ?? () from /usr/lib64/firefox-71.0/libxul.so > #22 0x777f6979 in ?? () from /lib64/libnspr4.so > #23 0x77f8a031 in start_thread () from /lib64/libpthread.so.0 > #24 0x77b6f4df in clone () from /lib64/libc.so.6 > > The Debian guys have also observed this: > https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=949644 > (and also don't have a fix yet). > > Any ideas? > > Best regards > bero > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- 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] bug on zPath length
On 1/23/20, Ondrej Dubaj wrote: > I discovered an issue found by coverity scan. Thanks for the report. This was previously fixed here: https://www.sqlite.org/src/info/465a15c5c2077011 -- 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] GENERATE_SERIES is not available in the command-line shell
On 1/21/20, Digital Dog wrote: > Hi! > > In the docs here > https://www.sqlite.org/series.html > there's this statement "The generate_series(START,END,STEP) table-valued > function is a loadable extension included in the SQLite source tree, **and > compiled into the command-line shell.**" Unfortunately in version 3.30.1 on > Windows downloaded from SQLite.org it's not available by default. > Is it going to be fixed in 3.31? I'll fix the documentation, so that it doesn't say that any more, if that is what you mean by "fixed". :-) You can use a recursive common table expression in place of generated_series(). Like this: WITH generate_series(value) AS ( VALUES($START) UNION ALL SELECT value + $STEP FROM generate_series WHERE value $ STEP <= $STOP ) SELECT value FROM generate_series; This is more typing, I know. But it is the official SQL standard way of getting an ascending list of integers. -- 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] Reporting documentation issues/unclarity?
On 1/21/20, Merijn Verstraaten wrote:
> Should issues with the documentation (i.e., missing/unclear things) be
> reported to this mailing list too?
>
> Specifically, something that was unclear to me while implementing my own
> aggregate function is what happens if sqlite3_result_error() is called and
> another result functions gets called afterwards. So, suppose we have:
>
> void stepfun(sqlite3_context *ctxt, int nArgs, sqlite3_value **args)
> {
> ... random code here...
> sqlite3_result_error(ctxt, "Something went bad!", -1);
> ...more random code...
> sqlite3_result_int(ctxt, 42);
> return;
> }
>
> Would the overall function still report an error or would the call to
> sqlite3_result_int overwrite the earlier error and have it return
> successfully again?
For details like this, I think it is best to just ask on the mailing
list, and not expect that the answer will be documented.
Writing documentation is a careful balance between brevity and detail.
You don't what to explain what happens in every corner case, as that
will attention away from the main use case, where it belongs.
IIRC, in this case, sqlite3_result_error() persists. The subsequent
sqlite3_result_int() call merely changes the error message to the
number 42. If you want to cancel a prior call to
sqlite3_result_error(), you need to invoke
sqlite3_result_error_code(context, SQLITE_OK).
--
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] 3.31.0 Request TRUSTED_SCHEMA and pragma's
On 1/21/20, Keith Medcalf wrote: > > Richard, > > The TRUSTED_SCHEMA setting works really well but I have noticed one problem > (there may be more, but I haven't run across any yet) with it that is > perhaps easy to address, though it needs to be done properly. That is > perhaps adding an innocuous flag to pragma definitions in mkpragmatab.tcl so > that it can be carried though into the vtable code that handles > pragma_ xConnect method. > > This would permit pragma's such as table_info (for example) to be marked as > innocuous so that pragma_table_info could be used in a view even when the > schema is untrusted. That would potentially leak information about the schemas of other attached database files. It seems like a harmless information leak, but it is a leak nevertheless. If you are setting untrusted schema (as you probably should) but you need to use pragma virtual tables inside of triggers and views, consider putting them inside TEMP triggers and views. TEMP triggers and views, because they must originate in the application itself, are always trusted. -- 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] SQLite 3.31.0 enters pre-release testing
Our intent is that there will be no more changes to SQLite, other than documentation updates and bug fixes, prior to the 3.31.0 release. Consider the "Prerelease Snapshot" at https://sqlite.org/download.html to be a beta-release. Please test that snapshot and report any problems. A terse summary of changes can be seen at https://www.sqlite.org/draft/releaselog/current.html A release checklist has been set up at https://www.sqlite.org/src/ext/checklist/top/index As I write these words, the checklist is all gray. Items will turn green as they are checked off (or other colors if there are issues). The 3.31.0 release will occur when the checklist goes all-green. We hope to achieve this on or before 2020-01-31 - two weeks from today. You can see the full sequence of main-branch check-ins since the previous release at https://www.sqlite.org/src/timeline?from=release=trunk For a diff showing all changes since the previous release, edit the prior URL by replacing "/timeline" with "/vdiff". You can also click on any two nodes (the circles connected by arrows) on the timeline graph to see a diff between the two selected check-ins. Or, manually edit the URL to change the "from" and "to" attributes to a check-in hash prefix, branch name, of tag. -- 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] Next Release? Visual release timeline?
On 1/15/20, Dominique Devienne wrote: > I like Lua's way to graphically visualize releases at > https://www.lua.org/versions.html > > Makes it very easy to get a sense of the frequency. Any chance SQLite > would do that, perhaps with "major" (excluding the leading 3.) > releases on one side, and minor ones on the other? Please send javascript that will generate such a graph, either as SVG or as an HTML Canvas. (1) For improved display on mobile, consider making the graph vertical instead of horizontal. (2) Assume the data is a JSON array of pairs. The first element of each pair is the release name (ex: "3.30.0") and the second element is the time as a fractional year (ex: "2019.7775"). -- 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] Feature request: more robust handling of invalid UTF-16 data
On 1/14/20, Richard Hipp wrote: > I'm having trouble reproducing this. I went back to version 3.30.1 and I was able to reproduce it. So I bisected and found the following: https://sqlite.org/src/timeline?c=51027f08c0478f1b -- 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] Feature request: more robust handling of invalid UTF-16 data
On 1/13/20, Dennis Snell wrote:
> We have a JSON document like this which we store in a table.
>
> {“content”: “\ud83c\udd70\ud83c(null)\udd71”,”tags":[]}
>
>
> The JSON is well-formed but the sequence of UTF-16 code points is invalid.
>
> When sqlite reads this data two types of further corruption
I'm having trouble reproducing this. The following test script (one
of many) illustrates:
CREATE TABLE t1(j TEXT);
INSERT INTO t1(j) VALUES
('{"content": "\ud83c\udd70\ud83c(null)\udd71","tags":[]}');
SELECT length(json_extract(j,'$.content')) FROM t1;
WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<9)
SELECT x, printf('%x',unicode(substr(json_extract(j,'$.content'),x)))
FROM t1, c;
The column T1.J is loaded up with your original JSON with the invalid
code points. Then I run json_extract() to pull out the invalid string,
but SQLite says that the length is 9, which I think is the correct
answer. The second SELECT with the CTE in it loops over each
character and prints out the HEX value for that character. Here is
what I see:
1|1f170
2|fffd
3|28
4|6e
5|75
6|6c
7|6c
8|29
9|fffd
So the initial surrogate pair was rendered correctly as 0x1f170. The
\ud83c without the following high surrogate was converted into 0xfffd
(which is the right thing to do, is it not). Then the 6 ASCII
characters follow. Finally, the last isolated high-surrogate is
(correctly?) converted into 0xfffd.
What behavior were you expecting?
Is there something that I can be doing differently to make it misbehave?
--
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] Query Planner GROUP BY and HAVING clauses optimization ?
On 1/14/20, Keith Medcalf wrote: > > I seem to recall something about "expensive" conditions that will be forced > to be run on only as few surviving candidate rows as possible, but my > recollection is vague (they say the memory is the second thing to go -- > strange I can't remember the first). > > Anyway, Richard may be able to help here. Maybe you are thinking of SQLITE_ENABLE_SORTER_REFERENCES. https://www.sqlite.org/compile.html#enable_sorter_references -- 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] Bug fixes only branch.
On 1/13/20, Syed Ahmad wrote: > We are at 3.14.2 > > Current version = 3.14.2 Date : 2016-09-12 > > https://www.sqlite.org/changes.html > > how can i take latest stable branch which include only bug fixes . no new > features. > > Is there any way? We sometimes do things like that for paid support customers. But maintaining bug-fix branches of historical versions is time-consuming, so we do not do it routinely. It is also risky, as actual releases are better tested and more reliable than backported patches. -- 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] Test failures on GPFS
On 1/11/20, J. King wrote: > > WAL mode does not work over the network, so the test failures are presumably > to be expected. > WAL mode should work on a network filesystem, as long as all of the clients are on the same host computer, and as long as mmap()-ing the *-shm file gives all the clients shared memory. Dunno if GPFS does that or not, though. Maybe not. Or, maybe not reliably. -- 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] Unexplained table bloat
On 1/10/20, Dominique Devienne wrote: > > There's no way at all, to know the length of a text column with embedded > NULLs? > You can find the true length of a string in bytes from C-code using the sqlite3_column_bytes() interface. But I cannot, off-hand, think of a way to do that from SQL. -- 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] Unexplained table bloat
On 1/10/20, Ryan Mack wrote: > OK, I think I've got a better sense now. Hex encoding the column shows > that there's actually a huge amount of data stored in there. For some > reason length() isn't revealing it even if the column type is blob. > Dumping and restoring the table is truncating the data. length() on a BLOB should show the number of bytes in the BLOB. length() on a string should show the number of *characters* (not bytes) in the string up through but not including the first zero-character. It is possible to have additional content after the first zero-character in a string, which length() will not tell you about. -- 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] Unexplained table bloat
On 1/10/20, Ryan Mack wrote: > > I'm trying to understand unexplained table bloat The sqlite3_analyzer command-line utility program (available in the "Precompiled binaries" bundles on the https://sqlite.org/download.html page) is designed to help understand these kinds of problems. Please run that utility on the database and perhaps post the output here. -- 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] how to pass -Dxxx compile option
On 1/9/20, Xingwei Lin wrote: > Hi, > > How can I pass -Dxxx compile option when I build sqlite? Such as, - > DSQLITE_ENABLE_INTERNAL_FUNCTIONS. > Option 1: CFLAGS='-O2 -DSQLITE_ENABLE_INTERNAL_FUNCTIONS' ./configure && make Option 2: ./configure && OPTS='-DSQLITE_ENABLE_INTERNAL_FUNCTIONS' make -e Option 3: ./configure && make OPTS='-DSQLITE_ENABLE_INTERNAL_FUNCTIONS' -- 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] Magellan 2.0 Vulnerabilities
On 1/8/20, Ware, Ryan R wrote: > > We've been following the Magellan 2.0 > (https://blade.tencent.com/magellan2/index_en.html) issues found by Tencent. > Why, oh why, are you doing this? If you are a typical user of SQLite, then there are no vulnerabilities in SQLite that you need to concern yourself with. Now, if you have some application that allows anonymous rogue agents on the internet to run arbitrary, unfiltered SQL statements using SQLite, and if you enable the legacy "FTS3" extension, then the so-called "Magellan 2.0" issues might be of concern to you. But we only know of a single application that fits this description - WebKit. - and that application was patched within hours of the hack becoming known, which was many months ago. Tencent has a amazing marketing organization that is remarkably effective at promoting and amplifying every little trifling bug that their hackers find and make it sound like it will bring an end to civilization. I suggest that you not be drawn into the hype. If Intel has some super-sensitive or especially vulnerable application using SQLite that we don't know about, then you can take out a cost-efficient consulting contract with us and we will work closely and confidentially with you to secure your application against past and future hacks and ensure that you stay up-to-date with all the latest patches. Otherwise, please just ignore Tencent. Excessive focus on Tencent and their vulnerability marketing organization will merely distract you from defending against actual threats. -- 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] TO
On 1/8/20, R Smith wrote: > > Anyone have an idea where the word TO is used in SQL in SQLite? > alter table t1 rename TO t2; rollback TO savepoint1; -- 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] NOTNULL
On 1/8/20, R Smith wrote: > I find the keyword NOTNULL listed among known SQLite keywords - > no. 88 on this page: https://sqlite.org/lang_keywords.html > > But cannot find a single mention of it or place to use it in SQLite, nor > get any hit on the sqlite.org search except in reference to the above list. > > 1. What is it for? > > 2. Does it have/need any documentation? NOTNULL is a postgresql-ism. It is equivalent to IS NOT NULL. -- 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] shell.c: exec_prepared_stmt no return value
Thank you for the bug report.
However, you have provided a fix without showing us the malfunction.
You suggest a change without demonstrating what behavior the change is
designed to fix. The problem you are trying to fix is not obvious,
because when I run test queries that contain errors, I do get an error
message back, even without your fix. So I cannot figure out what
problem your fix is intending to address.
Can you please provide an example input that gives incorrect results
before your proposed fix, and that gives the correct result
afterwards?
On 1/8/20, Hannes Mühleisen wrote:
> Hello SQLite list,
>
> we have noticed that the sqlite shell is unable to report errors that happen
> within exec_prepared_stmt, because that function has no return value and is
> thus unable to bubble issues up. For example, if sqlite3_step should fail
> for some reason, this should be shown to the user, for example in the call
> to exec_prepared_stmt from shell_exec.
>
> We propose to add a return code to exec_prepared_stmt like so:
>
> static int exec_prepared_stmt(
> ShellState *pArg,/* Pointer to ShellState
> */
> sqlite3_stmt *pStmt /* Statment to run */
> ){
> int rc;
> /* ... */
> return rc;
> }
>
> then, in shell_exec, we could say something like
>
> rc = exec_prepared_stmt(pArg, pStmt);
> if (rc != SQLITE_OK) {
> if (pzErrMsg) {
> *pzErrMsg = save_err_msg(db);
> }
> }
>
> This way, if an error occurs during execution, this will be displayed to the
> user.
>
> Best from Amsterdam,
>
> Hannes
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
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] Cascaded delete unexpectedly triggered by upsert
On 1/7/20, Michael Kappert wrote: > > If I understand correctly, the upsert should behave like UPDATE in the > examples above, but it behaves like a DELETE followed by INSERT instead? > REPLACE and UPSERT are different things. See https://www.sqlite.org/lang_conflict.html for information about REPLACE and https://www.sqlite.org/lang_UPSERT.html for information about UPSERT. REPLACE works by first DELETE-ing any rows that would cause a conflict and then doing the INSERT. UPDATE works by converting the INSERT into an UPDATE. -- 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] RTree function information
On 1/7/20, R Smith wrote: > On 2020/01/07 3:46 PM, Richard Hipp wrote: >> On 1/7/20, R Smith wrote: >>> I cannot find any mention of the other two. >> Those other routines are for testing and debugging use only. They >> will (likely) be made inaccessible to normal application code in the >> next release. > > > Thank you kindly. > > May I assume the same of FTS5 functions: Yes. -- 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] RTree function information
On 1/7/20, R Smith wrote: > I see three R*Tree functions in the function list compiled-in with > latest binaries, namely: > rtreecheck(), > rtreedepth(), and > rtreenode(). > > While rtreecheck() is explained ... > > I cannot find any mention of the other two. Those other routines are for testing and debugging use only. They will (likely) be made inaccessible to normal application code in the next release. -- 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] A hang in Sqlite
Yongheng: Please send any subsequent fuzzer finds directly to b...@sqlite.org. Thanks. On 1/6/20, Dominique Devienne wrote: > > The frequency of all these fuzzer related emails has reached a point IMHO > that it's "polluting" a bit this ML. > Especially since most times there's no follow (in the ML at least) to most > of these messages. > > In another recent thread that mentioned another "fuzzer" (Manuel Rigger), I > discovered his reports completely bypassed > the ML and go straight to the official bug tracker, which AFAIK is not > publicly writable, so Mr Rigger's quality work must > have granted him the privilege to report directly via it. > > Thus I'd ask whether Mr Chen (or Mr Yongheng, I'm not sure) could similarly > go off-list and directly to bug tracker? > I have 37 separate threads starting Nov 22nd last year related to his > fuzzer discoveries, for roughly the same number > of threads related to all other topics, which effectively doubles this ML's > traffic (in thread count, not message count). > > Thus my request. FWIW... --DD > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- 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] Performance regression since 3.19.0
How does the performance compare with this: CREATE TABLE t1 (a TEXT, b TEXT); CREATE TABLE t2 (a TEXT, b TEXT); CREATE INDEX t1_a ON t1 (a,b); CREATE INDEX t2_a ON t2 (a,b); SELECT * FROM t1 LEFT JOIN t2 ON (t1.b=t2.b AND t2.a='123') WHERE t1.a='123'; On 1/5/20, Peter Inglesby wrote: > Is there any more information I could provide? Run "ANALYZE;" on a database that contains actual data, then send us the output of ".fullschema" -- 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] Bug? SQLite command-line result is different from Perl DBI::Sqlite result
On 1/5/20, Richard Hipp wrote: > > Note that both MySQL and SQLite do allow you to use parentheses, as > shown in my examples, to define the order of evaluation. PostgreSQL > does not, sadly. > Apparently, in PostgreSQL you have to say: SELECT * FROM (SELECT * FROM a, b) AS x JOIN c USING(id); -- 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] Bug? SQLite command-line result is different from Perl DBI::Sqlite result
On 1/5/20, Keith Medcalf wrote: > > Hrm. Inconsistent/incorrect results. Consider: > > create table a(id integer primary key, a); > insert into a values (1,1), (2,1), (3,1); > create table b(id integer primary key, b); > insert into b values (1,2), (3,2), (4,2); > create table c(id integer primary key, c); > insert into c values (1,3), (4,3), (5,3); > > select * from a, b, c using (id); -- very strange result > > id a id b c > -- -- -- -- -- > 1 1 1 2 3 > 1 1 3 2 3 > 1 1 4 2 3 PostgreSQL and MySQL process the query as follows: SELECT * FROM a, (b JOIN c USING(id)); SQLite processes the query like this: SELECT * FROM (a,b) JOIN c USING (id); I don't know which is correct. Perhaps the result is undefined. Note that both MySQL and SQLite do allow you to use parentheses, as shown in my examples, to define the order of evaluation. PostgreSQL does not, sadly. MS-SQL does not (as far as I can tell) support the USING syntax on a join. -- 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] FW: Questions about your "Performance Matters" talk re SQLite
On 1/3/20, sky5w...@gmail.com wrote: > Is there a query function for these and other config settings? > I see no sqlite3_config_get() in sqlite3.h. There is no query function for the SQLITE_CONFIG_MEMSTATUS setting. -- 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] FW: Questions about your "Performance Matters" talk re SQLite
On 1/2/20, Barry Smith wrote: > 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.” > Looking at the main.c file (https://github.com/plasma-umass/coz/blob/master/benchmarks/sqlite/main.c) it appears that the test creates 64 separate database connections, each with a separate in-memory database. There are two sources of contention here: (1) SQLite keeps track of the total amount of memory it is using on all threads. So for each malloc() and free() it has to take a mutex to increase or decrease the counters. This is probably the primary source of contention. It can be disabled by running: sqlite3_config(SQLITE_CONFIG_MEMSTATUS, 0); early in main(), before any other SQLite interface calls. Make that one change and I suspect that most of the thread contention will go away. (2) SQLite has a single PRNG used by all threads. And so there is a mutex that has to be taken whenever a new random number is generated. But the workload does not appear to be using any random numbers, so I doubt that this is an actual problem in this case. > 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. The task requires X number of CPU cycles *regardless* of the clock frequency. If the clock slows down, then it takes more elapse time to run those X cycles, but it does not increase or decrease the number of cycles required. So in that sense, counting the number of CPU cycles is an excellent measure of effort required to complete the computation. Furthermore, the idea that thread contention will cause the CPU clock to slow down seems silly. Technically, I suppose such a think might actually happen - IF you do all of your work as multiple threads within the same process and they all blocked on the same resource. The point is, you shouldn't do that. Instead of one process with 64 threads, how about 64 processes with one thread each. Since they are all doing different things (serving independent HTTP requests, for example) they might as well each have their own address space. Keeping each job in a separate process provides isolation for added security. And it completely eliminates the need for mutexes and the accompanying thread contention. If SQLite runs faster for you when you make direct calls to pthread_mutex_lock() rather than indirect calls, how much faster would it run if you completely eliminated all calls to pthread_mutex_lock() by putting each task in a separate process? -- 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] New SQL functions available for testing only.
On 1/1/20, Manuel Rigger wrote: > Thanks for adding these functions! My test generator/fuzzer is written in > Java and uses the JDBC interface. In order to call the C function, I would > probably need to modify the driver code or use JNI, which would both be a > bit hacky. Is there a compile-time option that can be set to provide > internal functions by default? -DSQLITE_ENABLE_INTERNAL_FUNCTIONS NB: Do NOT enable this on a production build. The -DSQLITE_ENABLE_INTERNAL_FUNCTIONS compile-time option is for testing purposes only. -- 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] New SQL functions available for testing only.
Fuzzers and testers: I've created some new built-in SQL functions for SQLite that provide direct access to some of the internal code-generator routines that have historically given trouble: expr_compare(A,B) This function returns non-zero if expressions A and B are different. See https://www.sqlite.org/src/artifact/971e442fd5?ln=5049-5075 for details. expr_implies_expr(A,B) Return non-zero if expression A implies expression B. See https://www.sqlite.org/src/artifact/971e442fd5?ln=5277-5299 for details. This routine is used to determine if a partial index is usable. implies_nonnull_row(E,C) C must be the name of a column in one of the tables of the SELECT statement. E is an arbitrary expression. Return non-zero if expression E can only be true if the row of which C is a member has some non-NULL column. This routine is used to help determine if a LEFT JOIN can be simplified into an ordinary JOIN. These new SQL functions are only accessible if you activate "internal functions" using C code like this: sqlite3_test_control(SQLITE_TESTCTRL_INTERNAL_FUNCTIONS, db); Or in the CLI like this: .testctrl internal_functions In other words, these SQL functions are not available to ordinary applications. An application would need to take special steps to activate these routines. And those special steps include making calls to C-code, which means that an attacker who is submitting SQL text and/or database files is unable to exploit any weaknesses that may turn up in these SQL functions. Fuzzer authors: I recommend that you enable these functions in your fuzzers. Doing so might help your fuzzersto find bugs that would otherwise be difficult to reach. Happy hunting. -- 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
