Re: [sqlite] more efficient JSON encoding: idle musing
If you have example code for a mechanism that is more space efficient and/or faster, please share it with us. "Bencode" is approximately the same space-wise as JSON, but encoding/decoding is potentially faster since it doesn't have to do any escaping for strings: https://en.wikipedia.org/wiki/Bencode On Fri, 21 Feb 2020, Richard Hipp wrote: 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 ___ 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"
The first thing that came to mind was "client-only", though that made me think of an old saying about lawyers, He who represents himself has a fool for a client. ... Otherwise "server-free" has a very nice ring to it. Carl On Tue, 28 Jan 2020, Graham Holden wrote: > Tuesday, January 28, 2020, 4:25:49 PM, Richard Hipp wrote: > >> Since suggesting daemon-less, someone else (I'll have to research who, >> exactly) suggested "server-free", which I think I like more. > > What? A free server with every copy of SQLite? > That sounds like a good deal :-) > > Graham > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Grammar police
Note here "AN H", not "A H", because when saying "H", it starts with a vowel sound Re: Aitch vs. Haitch: https://www.theguardian.com/science/shortcuts/2013/nov/04/letter-h-contentious-alphabet-history-alphabetical-rosen On Fri, 12 Jul 2019, Stephen Chrzanowski wrote: "an historical oversight" feels dirty to me, mostly because it's an incomplete sentence and can be understood in different ways. It's a "point", or answer to a question. In my verbage, "historical" begins with an H (Note here "AN H", not "A H", because when saying "H", it starts with a vowel sound). Its the same as you wouldn't say "I hear an hissing noise", or "I just learned an History Lesson". Words prefixed with the sound "HISS" should be prefixed with "A" in my mind, unless the H is silent, like Honor or Honest. However, that said, it'd also probably depend on what the sentence is going to be describing. "I went to a historical event" leaves a bad taste in my mouth, while "I went to an historical event" feels better, because I think the E in Event carries over to the "AN", or, it could be a past vs present meaning of the sentence. But if I say "I've just learned a historical lesson", the "L" sound in Lesson doesn't carry over correctly to "AN". Let me be clear I'm not saying you're wrong or right, just that, in my head and my syntax when I write sentences, anything that begins with H would end up having an "A" prefix. I don't use the word Historical or History all that often, so I can't say how I've written it out in the past. That said, the point of the sentence is presented whether "AN" or "A" is used, in my opinion. This is going down the lines of (Dare I say) is it ESS-QUE-EL or "SEEK-WIL". In my case, if I were to read that entire paragraph, I probably wouldn't even blink on "... an historical oversight ..." or "... a historical oversight ...". ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Grammar police
Ginger tells me that "a historical" is technically correct, AFAICT, "an historical" is correct iff the "h" in "historical" is silent. Eg, "It's an 'istorical oversight to pronounce the 'h' in 'historical'." On Thu, 11 Jul 2019, Richard Hipp wrote: On 7/11/19, David Raymond wrote: Section 5: "Due to an historical oversight" Due to [a] historical oversight Here in the Southeastern US (specifically in Charlotte, NC) we really do say "an historical oversight". If you said "a historical oversight", people would look at you funny. Ginger tells me that "a historical" is technically correct, but I'm going with what people (here) actually say. :-) All the other corrections, in this email and in other recent mailing list posts, should now have been applied. Thanks, everybody, for sending them in. Please feel free to do so at any time. You can send them directly to me if you don't want to send them to the mailing list. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_MAX_MMAP_SIZE 2GB default
Thanks Jens! On Mon, 22 Apr 2019, Jens Alfke wrote: But yeah, I agree with you that it seems odd to have a compiled-in restriction on the maximum memory-map size. I looked a bit into the history, and it appears the just-under-2GB limit was specifically put there (by drh) so that the value would fit into a signed 32-bit integer: https://www.sqlite.org/src/info/460752b857532016 Maybe this was in order to fit into a ssize_t on 32-bit platforms? (Just a guess as the size_t type is used for mmap.) If that's the reason, would drh & the sqlite team consider changing the default SQLITE_MAX_MMAP_SIZE definition from 0x7fff to SSIZE_MAX (defined in limits.h) ? ... Somewhat of an aside: Most current OSs have a universal buffer cache, wherein filesystem caches and VM pages use the same RAM buffers. A page-fault and a file read will incur similar amounts of work. The big benefit is that the memory-mapped pages can be evicted from RAM when needed for other stuff, whereas a malloc-ed page cache is considered dirty and has to be swapped out before the RAM page can be reused. Right, i think we're agreeing here. I just mean that if a db file is much larger than the available ram, and all of it is used for a query, then whether bytes come in via mmap+memcpy() or regular file read(), the kernel will page-in missing pages into its page cache, and (at its discretion) it will eventually have to evict those pages to make room for others. (I didn't mean to make reference to disk swap or sqlite's user-space pagecache.) The point is that the kernel page loading/evicting part is the same, but the mmap also saves the overhead for the seek/read system calls. And i say this from my personal experience with sqlite -- when doing a long running join on a db that does not fit into ram, with the timer on, i observed that in the regular non-mmap mode, the system time for the query would be about equal to the user time. But when i mmap'ed the whole file, the system time for the query was < 1% of the user time, and the user time was also less than it had been in non-mmap mode. I can only guess the huge difference in the system time was mainly the kernel handling all the seek/read system calls, vs just memcpy when the whole db file is mmap'ed. The bottom line in any case is i saw a substantial speedup for long queries when mmap'ing a large db, even when it did not fit into ram. Thanks..! Carl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please help me fix the SQLite Git mirror
I would like to better understand how rewiring the refs this way constitutes "changing history". The refs/heads entries are all ephemeral - they are constantly changing on their own, and no historical record of their past values is retained. The key bit here is that in git, every commit references its parent commit hash, and thus implies the entire past history for that commit. And local git repos contain that entire history. So when a branch head is updated upstream, git can tell if history has moved forward (ie, the new head commit contains the previous head commit in its history), and if not, it's considered a "forced update", which is to say, history was re-written -- since the last previously know state of history is nolonger part of history. Carl On Mon, 22 Apr 2019, Carl Edquist wrote: Hi Richard, As Jonathan mentioned, in git land, if you have already published a "mistake" commit publicly, the proper way to revert it is to make another commit to reverse/undo the change. By removing a commit from the public history of the published 'master' branch, it forces everyone downstream to manually fix their history. If they do a normal "git pull", git will attempt to merge their master (the mistake commit) with the latest upstream master, which is not actually your intention. But if you make an "revert" commit to undo the change, history will continue forward for the master branch from the downstream perspective. I fixed the recent breakage of the SQLite Git mirror as follows: (1) cd into the refs/heads directory (2) run "cat master >mistake" (3) run "echo a9a5465eb44d0d8f1c3c9d288b7f23f628ddb50b >master" (4) run "git push --mirror https://github.com/sqlite/sqlite.git"; Not that you want to do it this way again if you can avoid it, but the safe git way to do (2),(3) is: 2) git update-ref refs/heads/mistake refs/heads/master 3) git update-ref refs/heads/master a9a5465eb44d0d8f1c3c9d288b7f23f628ddb50b Carl On Mon, 22 Apr 2019, Richard Hipp wrote: Thanks for the help. See additional questions and remarks below On 4/22/19, Jonathan Brandmeyer wrote: ``` # Construct the matching branch name git branch mistake 9b888fc # Push the name alone to the remote git push -u origin mistake # Move the name of master git checkout master && git reset --hard # Push the new name of master git push --force ``` Git reset --hard will move the name of the current working branch to another branch SHA, which is why you need to first check out the branch being moved: Its context sensitive. You are re-writing history, though. I don't understand this part. From the Fossil perspective, moving a check-in from one branch to another is just adding a new tag to that check-in. No history is changed. The DAG of check-ins (the block-chain) is unmodified. Subsequent to your message, I fixed the recent breakage of the SQLite Git mirror as follows: (1) cd into the refs/heads directory (2) run "cat master >mistake" (3) run "echo a9a5465eb44d0d8f1c3c9d288b7f23f628ddb50b >master" (4) run "git push --mirror https://github.com/sqlite/sqlite.git"; This was a one-time fix. I have not yet enhanced the mirroring mechanism to make this happen automatically, but probably I will soon. But before I proceed, I would like to better understand how rewiring the refs this way constitutes "changing history". The refs/heads entries are all ephemeral - they are constantly changing on their own, and no historical record of their past values is retained. So if I modify the refs to synchronize with the canonical Fossil repository, how is that changing history, exactly? Any further explanation is appreciated. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please help me fix the SQLite Git mirror
Hi Richard, As Jonathan mentioned, in git land, if you have already published a "mistake" commit publicly, the proper way to revert it is to make another commit to reverse/undo the change. By removing a commit from the public history of the published 'master' branch, it forces everyone downstream to manually fix their history. If they do a normal "git pull", git will attempt to merge their master (the mistake commit) with the latest upstream master, which is not actually your intention. But if you make an "revert" commit to undo the change, history will continue forward for the master branch from the downstream perspective. I fixed the recent breakage of the SQLite Git mirror as follows: (1) cd into the refs/heads directory (2) run "cat master >mistake" (3) run "echo a9a5465eb44d0d8f1c3c9d288b7f23f628ddb50b >master" (4) run "git push --mirror https://github.com/sqlite/sqlite.git"; Not that you want to do it this way again if you can avoid it, but the safe git way to do (2),(3) is: 2) git update-ref refs/heads/mistake refs/heads/master 3) git update-ref refs/heads/master a9a5465eb44d0d8f1c3c9d288b7f23f628ddb50b Carl On Mon, 22 Apr 2019, Richard Hipp wrote: Thanks for the help. See additional questions and remarks below On 4/22/19, Jonathan Brandmeyer wrote: ``` # Construct the matching branch name git branch mistake 9b888fc # Push the name alone to the remote git push -u origin mistake # Move the name of master git checkout master && git reset --hard # Push the new name of master git push --force ``` Git reset --hard will move the name of the current working branch to another branch SHA, which is why you need to first check out the branch being moved: Its context sensitive. You are re-writing history, though. I don't understand this part. From the Fossil perspective, moving a check-in from one branch to another is just adding a new tag to that check-in. No history is changed. The DAG of check-ins (the block-chain) is unmodified. Subsequent to your message, I fixed the recent breakage of the SQLite Git mirror as follows: (1) cd into the refs/heads directory (2) run "cat master >mistake" (3) run "echo a9a5465eb44d0d8f1c3c9d288b7f23f628ddb50b >master" (4) run "git push --mirror https://github.com/sqlite/sqlite.git"; This was a one-time fix. I have not yet enhanced the mirroring mechanism to make this happen automatically, but probably I will soon. But before I proceed, I would like to better understand how rewiring the refs this way constitutes "changing history". The refs/heads entries are all ephemeral - they are constantly changing on their own, and no historical record of their past values is retained. So if I modify the refs to synchronize with the canonical Fossil repository, how is that changing history, exactly? Any further explanation is appreciated. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_MAX_MMAP_SIZE 2GB default
Hi Jens, thanks for your reply. 2GB still still seems reasonable as a default, since SQLite is not primarily targeted at servers with oodles of RAM. (Remember, "64-bit" includes nearly all current mobile phones/tablets, which still have small amounts of RAM, 2GB or less.) My comment about 32bit vs 64bit wasn't really about the ram available on the system, just about integer sizes and the address space available for an mmap. For instance - if you have a 30GB db file on a 64bit system with <= 2GB ram, you can still mmap the whole file, and benefit from that mmap. If the portion of the db that gets used for a query fits within the available pagecache ram, it's a clear win. (It's not like the whole file automatically gets read from disk into the pagecache for the mmap.) But even if the whole file is used for the query (that is, more than fits into pagecache/ram), it still has the benefit of avoiding the system calls for the file seeks/reads. (Either way the kernel needs to swap disk pages into/out of of the pagecache.) What's more, (if I understand correctly) the default SQLITE_DEFAULT_MMAP_SIZE is zero on all platforms, so mmap_size starts at 0 unless raised with the PRAGMA at runtime. So applications need to explicitly request when they want to do mmaps of a particular size. So my question is, at least on on 64bit platforms, why is the default to *prevent* manually raising mmap_size past 2GB at runtime? I guess I'm curious what the rationale is for this hard limit. You can easily override the default by #defining a higher value yourself, right? Yes, of course, I compile my own now, defining a higher SQLITE_MAX_MMAP_SIZE. But when I want to run an application (eg, the sqlite3 command line util or or the python library) across many systems that I don't control, it would be convenient if I could use the stock versions installed rather than having to compile my own everywhere. Again the question I'm curious about is, why should users have to compile their own versions just to be able to raise PRAGMA mmap_size at runtime? Thanks..! Carl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_MAX_MMAP_SIZE 2GB default
Hi there, My apologies if this has been answered in the past, but is there a reason for the ~2GB default for SQLITE_MAX_MMAP_SIZE ? #ifndef SQLITE_MAX_MMAP_SIZE ... # define SQLITE_MAX_MMAP_SIZE 0x7fff /* 2147418112 */ It makes sense for 32bit platforms, but for 64bit with larger databases (where 2GB ends up being a small fraction of the db file), the default max effectively takes away the benefit of the feature. I can compile my own binary of course, but it would be convenient to have it supported out of the box. Just wondering if there is a reason not to increase the max for 64bit builds (to some large value like 1TB) ? Especially given that that the default mmap size is much lower in any case until it is increased (with PRAGMA mmap_size). Thanks..! Carl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] want to specify DB.TABLE with ".import" and ".mode insert"
Hello,
I observe the following two issues with the command-line sqlite3, both
with version 3.6.20 (on RHEL6) and the latest 3.8.8.3 compiled from
source.
It appears that the '.import FILE TABLE' command doesn't support
specifying the database name (db.table) -- I get a "no such table" error.
(I'd guess that it treats "db.table" as the table name rather than
recognizing the db part, as you can do with sql commands.)
Similarly (but maybe a separate issue), the '.mode insert TABLE' output
mode doesn't work with a database name either -- but this is a little more
clear that it's just single-quoting the whole TABLE string, rather than
recognizing the '.' as the db.table separator.
Hopefully the example output log below demonstrates this adequately.
A side nit-- if the table name *does* need to be quoted for '.mode
insert', it should really be with double-quotes, since it's an identifier,
right? (https://www.sqlite.org/lang_keywords.html)
So I would expect/suggest this output:
> .mode insert sdf.x
> select 'hi';
INSERT INTO sdf.x VALUES('hi');
> .mode insert sdf-x
> select 'hi';
INSERT INTO "sdf-x" VALUES('hi');
> .mode insert sdf-x.asd-y
> select 'hi';
INSERT INTO "sdf-x"."asd-y" VALUES('hi');
(Note that even when double-quotes are necessary, the dot should remain
unquoted!)
But here is what I actually get:
$ ./sqlite3
SQLite version 3.8.8.3 2015-02-25 13:29:11
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> attach ':memory:' as sdf;
sqlite> create table sdf.t1 (x);
sqlite> select * from sdf.t1;
sqlite> .mode list
sqlite> select 'hi';
hi
sqlite> .output t1.dat
sqlite> select 'hi';
sqlite> .output stdout
sqlite> -- here's the first error
sqlite> .import t1.dat sdf.t1
Error: no such table: sdf.t1
sqlite> -- only works with unqualified table name
sqlite> .import t1.dat t1
sqlite> -- but, the table is there
sqlite> select * from sdf.t1;
hi
sqlite> .mode insert sdf.t1
sqlite> select 'bye';
INSERT INTO 'sdf.t1' VALUES('bye');
sqlite> .output t1.sql
sqlite> select 'bye';
sqlite> .output stdout
sqlite> -- the 'db.table' name is broken when quoted
sqlite> .read t1.sql
Error: near line 1: no such table: sdf.t1
sqlite> -- again by hand
sqlite> INSERT INTO 'sdf.t1' VALUES('bye');
Error: no such table: sdf.t1
sqlite> -- works as it should when db.table is unquoted
sqlite> INSERT INTO sdf.t1 VALUES('bye');
sqlite> .mode list
sqlite> select * from sdf.t1;
hi
bye
sqlite>
Is this intentional? If not, would it be easy to fix?
Thanks!
Carl
