Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

On Fri, Mar 13, 2020 at 1:15 AM Jens Alfke wrote: > > > On Mar 12, 2020, at 1:17 PM, Richard Hipp wrote: > > > > I have set up an on-line forum as a replacement for this mailing list: > > Oh crap. > > > The Forum is powered by Fossil. > > I appreciate that you like to 'eat your own dog food'.

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

The nice thing about web browsers, you can apply your own styling. There's extensions that help with that, like StyleBot. Wout. On Fri, Mar 13, 2020 at 1:05 AM Keith Medcalf wrote: > > > Uck. That is the most horrible looking thing I have ever seen in my life. > Good luck with it. > > > -- >

Re: [sqlite] New SQLite Forum requires Javascript?

Hi, I normally wouldn't do this on the internet, but this mailing list goes deep. Not sure how that will go now that we have to use the forum, but here goes. JavaScript makes websites much nicer to work with, by a wide margin, especially those with lots of interaction like forums. Furthermore,

Re: [sqlite] Setting auto_vacuum=2 doesn't work after setting journal_mode=WAL

I do the exact same pragmas as Jens, and also in this order. I was even convinced that it was working, so I'll need to double check. The documentation might be correct if you know what to look for, but from current experience it's not obvious. Wout. On Sat., Feb. 22, 2020, 4:02 a.m. Keith

Re: [sqlite] more efficient JSON encoding: idle musing

On Fri, Feb 21, 2020 at 3:03 PM Richard Hipp wrote: > If you > have example code for a mechanism that is more space efficient and/or > faster, please share it with us. I'll see if I can prototype something in JS - I'd be keeping the layouts in a helper table, and I wouldn't be storing the values

Re: [sqlite] more efficient JSON encoding: idle musing

On Fri, Feb 21, 2020 at 2:37 PM Warren Young wrote: > > On Feb 21, 2020, at 5:20 AM, Wout Mertens wrote: > > Queries can go faster, because a query like `where json_extract(json, > > '$.foo') = 'bar'` can first check the layouts to see which ones apply, > > SQLite’s JSON

[sqlite] more efficient JSON encoding: idle musing

Hi, I use SQLite as a MaybeSQL store, mixing fixed columns with schemaless JSON columns. It's really great. In JavaScript, objects are key-value collections with unique keys, where the order of the keys is important. Most JSVMs store them as a pointer to a layout and then the values. The layout

Re: [sqlite] Optimizer limitation with partial indexes

Does moving the expr3 work? SELECT * FROM Table WHERE ((expr1 > val1 AND AND expr3) OR (expr2 > val2 AND expr3)) Wout. On Wed, Feb 12, 2020 at 12:09 AM Jens Alfke wrote: > I'm running into a problem with partial indexes; apparently the query > optimizer isn't smart enough. > > I currently

Re: [sqlite] Best way to store key,value pairs

On Mon, Jan 13, 2020 at 10:45 PM James K. Lowden wrote: > You're making an efficiency argument here, or maybe > ease-of-implementation assertion. For me, inserting one header row or > 20 is the same coding effort (still need a loop). I think transaction > throughput would be about the same if

Re: [sqlite] Things you shouldn't assume when you store names

Fascinating discussion, and threads like this are why this is the only mailing list that always triggers my "important" flag :) My problem with names isn't the number of fields needed to present them (I liberally use JSON1), but the operations that are possible on them, and the UI needed to enter

Re: [sqlite] SQLite with branching

On Mon, Nov 4, 2019 at 10:26 PM Jens Alfke wrote: > I don't have a practical use for the branching features, though they're cool, > but I'm salivating at the thought of a 2x speedup. > With all the work that's put into eking out small performance increases in > SQLite, I'd imagine the devs

Re: [sqlite] Theoretical write performance for low-throughput devices

On Mon, Oct 21, 2019 at 5:28 PM Jonathan Brandmeyer < jbrandme...@planetiq.com> wrote: > I'm working on an embedded system that uses a log-structured > filesystem on raw NAND flash. This is not your typical workstation's > managed flash (SATA/NVMe), or portable managed flash (SD/USB). It's a >

[sqlite] odd fts5 create bug

Hi, I don't know how to reproduce this, but I do know how I did it: I created an FTS5 table with the option "tokenizer" instead of "tokenize", and it created the table but not the support tables, and it returned an option error. The command was create VIRTUAL table "{sdb} houses-fts-en" using

Re: [sqlite] Fastest way to SELECT on a set of keys?

On Fri, Sep 13, 2019 at 6:38 PM Jens Alfke wrote: > (b) Execute "SELECT key, … FROM table WHERE key IN (…)", including all of > the key strings. > > If I do (b), SQLite has less setup work to do, and it could potentially > optimize the b-tree lookup. On the downside, I have to prepare a

Re: [sqlite] Proposal: SQLite on DNA

Surely brings new meaning to SQL injection attacks... Wout. On Thu., Jul. 25, 2019, 9:48 p.m. Stephen Chrzanowski wrote: > Do we really want to start going down this path? Like... we already have > parents who won't allow their kids to get flu shots. Will we need to give > our PCs the same

Re: [sqlite] Ionic SQLite get error: "Cannot read property 'executeSql' of undefined"

Attachments get removed, but this error means that the object you're calling executeSql on is not defined. Maybe you're not loading some file? Wout. On Thu., Jul. 25, 2019, 3:05 p.m. Sebastien Capdeville < capdeville.sebast...@gmail.com> wrote: > Hello, > I have an error with 'executeSql' on a

Re: [sqlite] How to set access permissions to protect a database file?

You are preventing Group users from eXecuting your script by removing the x. I think you wanted chmod 755 reading_room.tcl Same for the db file where users of the same Group are not allowed to Write. Finally, I think locking may be a problem: users will run the script, which will create journal

[sqlite] RFE: allow parameters in PRAGMA statements

Hi, I am using the user_version pragma for implementing an event-handling database. I'd like to prepare the statement to update it, e.g. `PRAGMA user_version = ?`. However, sqlite3 won't let me do that, so I just run the text query every time with the number embedded. Not a huge problem, more

Re: [sqlite] SQLITE_OPEN_WAL

On Thu, Jun 6, 2019 at 10:17 AM Simon Slavin wrote: > On 6 Jun 2019, at 9:11am, Wout Mertens wrote: > > > I'd like to create a database in WAL mode if it doesn't exist > > Create it without the flag then execute > > PRAGMA journal_mode=WAL > Ah no, you see, the

Re: [sqlite] Please help me fix the SQLite Git mirror

On Tue, Apr 23, 2019 at 1:22 PM Richard Hipp wrote: > The inability to see the entire DAG on a single screen in GitHub is a > persistent source of annoyance > to users like me who are accustomed to Fossil. > Note that many git clients (https://git-scm.com/downloads/guis/) do allow you to see

Re: [sqlite] compressed sqlite3 database file?

On Wed, Apr 10, 2019 at 9:51 PM Peng Yu wrote: > What do you recommend for Mac? Thanks. > Nothing. Apple doesn't want you to have compression, because then you would take longer to buy a new Mac. The afsctool compression is a laughable hack that only works on read-only data. Writing to the file

Re: [sqlite] compressed sqlite3 database file?

As I said in my previous email, I have a 13GB database that transparently compresses to 800MB. Not sure if it got through, didn't get replies to my last two emails. Wout. On Wed., Apr. 10, 2019, 5:04 p.m. Warren Young wrote: > On Apr 9, 2019, at 11:39 PM, Peng Yu wrote: > > > > Is there a way

Re: [sqlite] compressed sqlite3 database file?

I know of two options: The proprietary https://sqlite.org/zipvfs/doc/trunk/www/readme.wiki and this extension that you have to call on strings yourself: https://github.com/siara-cc/Shox96_Sqlite_UDF Furthermore, some filesystems allow transparent compression, like ntfs, bcachefs, zfs and btrfs. I

Re: [sqlite] How to use ORDER BY on FTS5 table ?

You need to create an index on both columns at once or the indexes can't be used. Try "EXPLAIN QUERY PLAN SELECT ...your query here" to see if indexes are being used. Wout. On Sun., Apr. 7, 2019, 9:41 a.m. Nik Jain wrote: > Have a fts5 table with 2 indexed columns. Where the idea is to match

[sqlite] Optimization corner case with IS?

I noticed this, IS is not treated like = for optimization: SQLITE> CREATE TABLE t(f INTEGER); SQLITE> CREATE INDEX t_f ON t(f) WHERE f IS NOT NULL; SQLITE> EXPLAIN QUERY PLAN SELECT * FROM t WHERE f = 1; QUERY PLAN `--SEARCH TABLE t USING COVERING INDEX t_f (f=?) SQLITE> EXPLAIN QUERY PLAN SELECT

Re: [sqlite] JSON1: queries on object keys

Mar 28, 2019, at 4:15 AM, Wout Mertens wrote: > > > > - I don't see how json_type can help > > I don’t see “json_type” in this thread at all, other than this message. > > > - Schemaless data is really nice to work with > > Sure, but it has a cost. Unless yo

Re: [sqlite] JSON1: queries on object keys

To answer all emails in this thread: - I don't see how json_type can help, I want to query the keys of objects - Schemaless data is really nice to work with, the wrapper I use does allow putting parts of the JSON object into real columns but changing the production db schema all the

Re: [sqlite] Row locking sqlite3

See also http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2017-August/074060.html - this branch has been around for a while and I think it was mentioned elsewhere that it definitely is being considered for mainline inclusion at some not immediate point. Wout. On Tue, Mar 26,

[sqlite] JSON1: queries on object keys

Hi amazing list, what would be the best way to answer these, given `CREATE TABLE foo(id TEXT, json JSON);` and json is always a json object: - all rows with a given key bar - SELECT * FROM foo WHERE json_extract(json, '$.bar') IS NOT NULL; - all rows where there are only any of the

Re: [sqlite] Recursive CTE on tree with doubly linked items

On Mon, Mar 18, 2019 at 10:21 AM Keith Medcalf wrote: > requires a "gentlemen's agreement" to only put positive values in the > position column (meaning the database cannot enforce this, you need to do > it at the application level) > Can't this be done with a before insert trigger? sqlite>

Re: [sqlite] mysql to sqlite

SQLite doesn't enforce types, but it does accept them. They're documented at https://www.sqlite.org/datatype3.html Basically, you want INTEGER and TEXT. There's no date type. I recommend storing those as epoch integer, or in ISO text format, so that they're easy to parse and sort correctly.

[sqlite] sqlite.org has expired ssl cert

it expired in January… Looks like the Let's Encrypt client isn't auto-updating the certificates. Wout. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Maximum result set size

Don't listen to me, Simon's answer is way better :) Wout. On Mon, Mar 11, 2019 at 9:22 PM Wout Mertens wrote: > There is no fixed limit, and the sqlite API just walks through the > results, so any memory overrun that happens is due to application level > code. > > Wout. >

Re: [sqlite] Maximum result set size

There is no fixed limit, and the sqlite API just walks through the results, so any memory overrun that happens is due to application level code. Wout. On Mon, Mar 11, 2019 at 8:30 PM Tim Streater wrote: > What is the maximum size in bytes that a result set may be? And what > happens if that

Re: [sqlite] (Info) Shox96 Compression as SQLite UDF

Wonderful! Things I wonder: * would it be possible to set up columns in such a way that the compression is transparent, that is, existing queries remain unchanged? * How does it fare on JSON strings? I notice that double-quotes are short, but array and object delimiters are 11/12 bits? It seems

Re: [sqlite] Database backup with writers present?

I meant reflinks not extents. I should go to bed ;) Wout. On Thu, Feb 28, 2019 at 11:57 PM Wout Mertens wrote: > One option, if you are on a filesystem supporting extents (macOS's apfs > and Linux's btrfs only, currently, with xfs and bcachefs support on the > horizon):

Re: [sqlite] Database backup with writers present?

One option, if you are on a filesystem supporting extents (macOS's apfs and Linux's btrfs only, currently, with xfs and bcachefs support on the horizon): do a copy with reflinks (cp -c on macOS, cp --reflink=auto elsewhere). That should be super fast since all it does is point to existing data and

Re: [sqlite] SIMD based JSON parsing for speeding up JSON extension

It's only a fair comparison if the simdjson code runs on the same system. It might reach 10GB/s or 200MB/s… Another possible concern is whether the SQLite JSON code is 100% compliant (I don't know if this is the case). There are some hairy edge cases in JSON (Unicode handling) that might slow

[sqlite] Index on expression optimization

Hi, I wonder if the following optimization would be easy to do: sqlite> create table t(a,b); sqlite> create index a on t(a); sqlite> explain query plan select a from t where a is not null; QUERY PLAN `--SCAN TABLE t USING COVERING INDEX a sqlite> explain query plan select a from t where (a is

Re: [sqlite] SQLite slow when lots of tables

Ah yes very true, it's easy to forget ones biases - I make single threaded web services with mostly-read access. This is a great use case for sqlite (provided you solve the data distribution problem). Wout. On Wed, Jan 30, 2019, 2:06 AM Keith Medcalf On Tuesday, 29 January, 2019 16:28, Wout

Re: [sqlite] SQLite slow when lots of tables

I always have to explain to people that there's no magic sauce that "real databases" add versus SQLite. SQLite uses the same techniques all databases use, and thanks to the absence of a network later, you avoid a lot of latency, so it can actually be faster. (I do believe that SQLite optimizes

Re: [sqlite] WAL mode for in-memory databases?

AFAIK, your best bet is to put a file db on a ramdisk (tmpfs). The ":memory:" DB is per connection only. Wout. On Mon, Jan 14, 2019 at 11:37 AM Dominique Devienne wrote: > According to [1] WAL mode does not apply to in-memory databases. > But that's an old post, and not quite authoritative

Re: [sqlite] Question about floating point

Ah, the luxuries of not programming in JavaScript ;) Anyway, using int64 would not have been sufficient to represent, say, tax numbers for the country, especially if you worked with cents. Whereas 53 bits of precision gets you a very long way and can even handle deflation The Wout. On Sun,

Re: [sqlite] Question about floating point

On Sat, Dec 15, 2018 at 7:13 PM Keith Medcalf wrote: > > >And yet ... here we are. The post which started this thread summed > >currency amounts and reached a total of 211496.252 . > > >Yes, you can say 'that would have been rounded before it was > >printed'. But then you're into the

Re: [sqlite] sqlite3_bind_text() and WHERE x IN (?)

On Fri, Nov 30, 2018 at 3:15 PM Dominique Devienne wrote: > sqlite> .header on > sqlite> create table t (c, n); > sqlite> insert into t values (1, 'one'), (2, 'two'), (3, 'three'); > sqlite> select n from t where c in (select value from json_each('[1, 3]')); > n > one > three Very nice! Now I

Re: [sqlite] sqlite_btreeinfo

| > index|TZ_ZoneOffset|TZ_ZoneData|20|CREATE INDEX TZ_ZoneOffset on > TZ_ZoneData (Zone_ID, StartTime + Offset, Offset)|0|35903|161|2| > sqlite> > > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volum

[sqlite] sqlite_btreeinfo

I can't figure out how to get access to the sqlite_btreeinfo vtable that was added in 3.22 :-( The only documentation is the C file and there doesn't seem to be a compile flag for it. I went and downloaded the file from https://sqlite.org/src/artifact/4f0ebf278f46e68e, then compiled it on on

Re: [sqlite] Index with calculated value not covering?

ways Wout. On Wed, Dec 12, 2018 at 4:54 PM Richard Hipp wrote: > On 12/12/18, Wout Mertens wrote: > > sqlite> CREATE TABLE "history"("v" INTEGER PRIMARY KEY AUTOINCREMENT, > > "type" TEXT, "data" JSON); > > sqlite> CREATE IN

Re: [sqlite] [EXTERNAL] Re: SQLITE gives incorrect results for 'NOT IN' query if partial index exists

Well since this thread is very off topic anyway: I think that would be wildly specific spam, I think she genuinely wanted to unsubscribe. Also, my message to Luuk was supposed to be unicast. I even forwarded the mail and typed his address manually but somehow gmail thought it opportune to keep

[sqlite] Index with calculated value not covering?

sqlite> CREATE TABLE "history"("v" INTEGER PRIMARY KEY AUTOINCREMENT, "type" TEXT, "data" JSON); sqlite> CREATE INDEX "type_size" on history(type, length(data)); sqlite> explain query plan select type from history group by type; QUERY PLAN `--SCAN TABLE history USING COVERING INDEX type_size

Re: [sqlite] SQLITE gives incorrect results for 'NOT IN' query if partial index exists

Hi Luuk, Not sure if you realize this, but your email comes over as very aggressive, and if there's one person on this mailing list that doesn't deserve that, it's dr Hipp. In particular, the quotes around forgot seem to imply that it was forgotten on purpose. Personally, I would have worded it

Re: [sqlite] Possible bug in Alter Table

> > > You have something mucking about and "helping you" to be cutie-pie. If > you turn that crap off, your problems will go away... > If it's on a mac, this terrible misfeature can be turned off in system preferences - keyboard - text - smart quotes. I lost a couple hours this way too, I

[sqlite] Optimizing aggregation queries

Given a logging table: CREATE TABLE log(type TEXT, amount INTEGER) SELECT type, SUM(amount), COUNT(*) FROM log GROUP BY type What would be good approaches to make the query fast? Wout. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Regarding CoC

See, this is where I miss being able to non-intrusively add a heart emoji to your post. Here it is anyway: ❤ Wout. On Thu, Oct 25, 2018, 12:11 AM Richard Hipp wrote: > On 10/24/18, Michael Falconer wrote: > > > > it's all gone...while my devout atheism is generally pleased my > > somewhat

Re: [sqlite] Regarding CoC

On Wed, Oct 24, 2018 at 1:55 PM Jan Danielsson wrote: > > Lets not pretend the rules are from English origin please. --DD > >I don't think that was what Wout meant. Read "Ye Olde English" as > "Aesthetically 'old'", not "use the original". Point was merely to give > some visual clues to

Re: [sqlite] Regarding CoC

I think a lot of confusion could have been avoided by putting the text of the CoC in a separate box, and for extra effect use a parchment paper background, something like the Papyrus font and write the rules in Ye Olde English. Right now it looks as if the rules were written specifically for

Re: [sqlite] Regarding CoC

On Oct 19, 2018 11:00 PM, "Roger Schlueter" wrote: There's no atheists/freethinkers at SQLite? There's at least one (me), but they can simply ignore all the God-related rules, easy. It gets a bit harder for Hindus though, they have to mentally add "(your favorite for this use case)" in front

Re: [sqlite] Regarding CoC

Well in the preface it does say that full compliance is near impossible and not required. I wouldn't call it antagonistic, it has many rules that are very inclusive of anyone. I must say it's a bit messy though, there are quite a few rules and not all of them are nicely orthogonal. Most of them

Re: [sqlite] Compression for ft5

This is really cool, thanks for sharing! I wonder though, is the compression done per field? I read the source but I couldn't figure it out quickly (not really used to the sqlite codebase). What are the compression ratios you achieve? Wout. On Mon, Sep 24, 2018 at 3:58 PM Domingo Alvarez

Re: [sqlite] Get data in one query

By the way, why not store the time as epoch? Date and time in one... On Wed, Aug 29, 2018, 8:46 PM Cecil Westerhof wrote: > 2018-08-29 18:58 GMT+02:00 Cecil Westerhof : > > > 2018-08-29 18:06 GMT+02:00 R Smith : > > > >> > >> SELECT SUM(Tot) AS Tot, SUM(Late) AS Late > >> FROM (SELECT 1 AS

Re: [sqlite] Unsigned

On Sun, Aug 26, 2018, 2:21 AM D Burgess wrote: > 2. Mixed 64/32 bit system that has integers that use the full 64 bits. > Numbers are sourced by realtime hardware. > Absence of 64 bit unsigned means addition of few functions to handle > inserts and display representation(s), numbers stored as

Re: [sqlite] Unsigned

I don't understand, can you not just use blobs as primary keys? $ sqlite3 SQLite version 3.24.0 2018-06-04 19:24:41 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table t(id blob primary key);

Re: [sqlite] Unsigned

You can just store binary blobs and interpret then in the client, no? Or do you need to do arithmetic on them? On Tue, Aug 21, 2018, 6:55 PM Randall Smith wrote: > >>> Date: Tue, 21 Aug 2018 16:46:48 +1000 > >>> From: D Burgess > >>> > >>> Is there a historical

Re: [sqlite] Possible NULL DEREFERENCES and DEAD STORES found by static analysis tools

I was curious so I looked it up, the 2015 one is here http://sqlite.1065341.n5.nabble.com/Security-issues-in-SQLite-td81339.html but the 2014 one didn't get any replies. The gist of it is that these static analysis tools generate a lot of false positives, so unless you can come up with a test

Re: [sqlite] I Heed Help

The problems you are describing are very specific yet don't involve large amounts of data. Typically, these types of problems are handled with a spreadsheet or an online form like Google forms. On Sat, Aug 18, 2018, 10:15 PM am...@juno.com wrote: > August 18, 2018 I am stuck on the dilemmas

Re: [sqlite] The upcoming alter table rename column

You can, since 3.16, get most pragma results as table-valued functions. I think what you want is something like SELECT * FROM pragma_table_info("tableName"); On Wed, Aug 15, 2018 at 8:04 AM Domingo Alvarez Duarte wrote: > Hello Richard ! > > I'm following the changes you are making to a add

Re: [sqlite] Safe sqlite over remote filesystem?

On Tue, Aug 14, 2018 at 4:10 PM Clemens Ladisch wrote: > So as long as all > programs that access the database cooperate, they can switch to a different > locking implementation, such as the unix-dotfile VFS: > > https://www.sqlite.org/vfs.html#standard_unix_vfses > > Note: this makes all

Re: [sqlite] Safe sqlite over remote filesystem?

On Tue, Aug 14, 2018 at 6:28 PM Rowan Worth wrote: > FWIW in the building I work in we have 20-30 users hitting around a dozen > SQLite DBs 8 hours a day 5 days a week, with all DBs served over nfs. Multiple writers? I presume you use WAL mode? > Erm, I got a bit carried away. My point is,

Re: [sqlite] Safe sqlite over remote filesystem?

On Tue, Aug 14, 2018 at 6:13 PM Alek Paunov wrote: > I am curious, Did you considered adapting writing in your use-case to > Bedrock? AFAIK, you can read from Bedrock instance DBs safely without > further adaptation. > Right, Bedrock is amazing, but in this particular use-case the only

Re: [sqlite] Safe sqlite over remote filesystem?

that changed in the last 7 years. Using the per-host-file-messaging as a communication channel to a single master that also exports the NFS doesn't seem that outlandish any more. On Tue, Aug 14, 2018 at 3:07 PM Wout Mertens wrote: > Idle musing again, I'm pretty bad at dropping thoug

[sqlite] Safe sqlite over remote filesystem?

Idle musing again, I'm pretty bad at dropping thoughts that are not immediately applicable to me, sorry. I know that multi-writer sqlite and NFS don't play well with each other. However, I wonder if some constraints could be added that would make this situation safe. My problem space is that of

Re: [sqlite] column types and constraints

cluded a bunch-o-magic which > either is not documented, or that you did not read and therefore you > assumed the issue you saw was SQLite3 when in fact it was the third-party > interface wrapper. > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a

Re: [sqlite] column types and constraints

story time: I was storing rows with text id's and never bothered setting a type, it was "id PRIMARY KEY" and I always assumed that it gave me back what I stored in it via the nodejs binding. One day I was storing a string of numbers which happened to fit in a 64 bit int, and so sqlite stored

Re: [sqlite] Check if the new table has been created

you can query the table with https://www.sqlite.org/pragma.html#pragma_table_info On Tue, Jun 19, 2018, 8:26 PM Igor Korot wrote: > Hi, > Is there a C API which checks if the new table has been created? > > Thank you. > ___ > sqlite-users mailing

Re: [sqlite] Issue with node-sqlite3

Another datapoint: while the sqlite3 module gets only minimal attention, it does work fine and we do use it in production for years now. On Wed, Jun 13, 2018 at 4:26 PM Peter Johnson wrote: > Hi Omer, > > Unfortunately what you are trying to do it not possible. > > You are trying to run the npm

Re: [sqlite] Selecting multiple similar columnname.

So the satellites don't get leapsecond updates? I don't understand why it can't just be an epoch… On Sat, Jun 9, 2018, 8:39 AM Stephan Buchert wrote: > To take care of the leap seconds every ~1.5 years or so, you need a day > segmented time stamp and a three column primary key: > > CREATE TABLE

[sqlite] JSON: check for subset of array

Hi all, Optimization question: If I have an array in JSON, what would be the best way to check that a given array contains a subset of the stored array? Right now I have the WHERE clause EXISTS(SELECT 1 FROM json_each(tbl.json, "$.foo") j WHERE j.value = ?) AND EXISTS(SELECT 1 FROM

Re: [sqlite] "cursored" queries and total rows

One more thing: On Sun, Jun 3, 2018 at 2:16 PM R Smith wrote: > > - If it is an Index, /and/ the Key repeats magnificently much (Imagine > adding an "Age" column to a phone-book and then filtering on Age, there > will be thousands of people who are all 34, for instance) then you are > better

Re: [sqlite] "cursored" queries and total rows

esn't matter of course. On Mon, Jun 4, 2018 at 6:00 PM heribert wrote: > I'm using also paged queries. I'm adding an OFFSET to the select-limit > query. > Works for me. > > Am 03.06.2018 um 14:16 schrieb R Smith: > > > > On 2018/06/03 1:13 PM, Wout Mertens wrote:

[sqlite] "cursored" queries and total rows

Hi all, To do paged queries on a query like SELECT colVal FROM t WHERE b=? LIMIT 10 I keep track of column values and construct a query that will get the next item in a query by augmenting the query like SELECT colVal FROM t WHERE b=? AND colVal > ? LIMIT 10 To know how many rows

Re: [sqlite] [EXTERNAL] Window functions

I implemented cursors by simply sending an encoded set of row values that indicate the absolute sorting position of your current query. Given that set of values, you can continue the query from that position, even if values were added or removed before the position. See

Re: [sqlite] In memory only WAL file

ah ok, now I understand. So you would like some setting like "limit writes to x/s", keeping the intermediate writes in memory. Here is a great writeup on how to tune sqlite for writes: https://stackoverflow.com/q/1711631/124416 But depending on your app you might just group a ton of writes in a

Re: [sqlite] In memory only WAL file

Serious question: what prompts you to consider these things? Is sqlite being too slow for you? On Thu, Apr 5, 2018 at 10:00 AM Pavel Cernohorsky < pavel.cernohor...@appeartv.com> wrote: > Hello Dan, thank you very much for clearing this up, because that was my > important misunderstanding. > >

Re: [sqlite] MIN() and MAX() of set of row values

As a sidenote, I believe row values were added because of keyset pagination https://use-the-index-luke.com/no-offset. I found them to not be actually useful, so I thought I'd explain here. (copied from my comments on that page (now no longer visible), slightly edited) I ended up implementing

[sqlite] .dump command and user_version

I noticed that `.dump` does not output the user_version pragma. It seems to me that that is part of the database data? I don't actually use it, but it might be interesting to add this for completeness? sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; COMMIT; sqlite> pragma

[sqlite] non-returned column aliases for repeating expressions?

Hi list, I often have (autogenerated) queries like SELECT "id" AS _1,"json" AS _2 FROM "testing" WHERE json_extract(json, '$.foo') < 50 ORDER BY json_extract(json, '$.foo') DESC,"id" where the json_extract(json, '$.foo') is indexed I wonder if it would be more efficient to write instead

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

1 very important one. I use it to insert events into a queue with a version and the version has to monotonously increase. However, if I had to maintain the known max manually, that wouldn't really be a problem. On Mon, Mar 19, 2018, 3:52 AM Rowan Worth, wrote: > 5/10 > 1/11 >

Re: [sqlite] [EXTERNAL] Indexing multiple values per row

True, I mean in case there were multiple dimensions. I should write down hidden thinking processes more. On Fri, Mar 16, 2018 at 9:58 AM Simon Slavin <slav...@bigfraud.org> wrote: > On 16 Mar 2018, at 8:38am, Wout Mertens <wout.mert...@gmail.com> wrote: > > > It seems

Re: [sqlite] [EXTERNAL] Indexing multiple values per row

I have the same problem but luckily O(n) performance is fast enough for me right now. It seems to me that the only real option is to maintain a derived table, the table could even be R*TREE to allow range queries. Triggers seem to be the accepted way to derive tables, but I'm a little afraid of

Re: [sqlite] Help with json1 query?

Can you elaborate on the metadata? Are the keys always the same, in which case you could store them as columns? There's also the https://sqlite.org/rtree.html extension which lets you efficiently query multidimensional range data. If there is truly no schema, what you propose is the only way

Re: [sqlite] Need some tips on using FTS5 with SQLite

I too am interested in this answer, I still have to start using fts5. What would be interesting is to see the `EXPLAIN QUERY PLAN [query]` for each of your queries, so as to see what causes the slowness. On Thu, Feb 8, 2018, 7:14 PM John Found, wrote: > > I am using FTS5

Re: [sqlite] Article on AUTOINC vs. UUIDs

Thanks, very insightful! On Thu, Nov 30, 2017 at 5:27 PM J Decker wrote: > I would also like to make a note, that many criticisms are 'there's so many > bytes to have to compare', however, because of the highly random nature of > good UUIDs failure occurs quickly, usually

Re: [sqlite] Article on AUTOINC vs. UUIDs

For userIds, I generate a unique id from their email address. If they later change the email address, they keep the id anyway. I really like natural keys. Of course, if you want to use that id in URLs, it would be good to use a second unique id that is not used as a foreign key, so that people

Re: [sqlite] Article on AUTOINC vs. UUIDs

The article is a bit muddled, the only real argument I could find is that auto-inc makes next keys easy to guess, which is information leakage, which means that is a potential security problem if that information were somehow useful. Seems to me that problem can be resolved by having an auto-inc

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

The one thing that saddens me is that the clock is not the full first part of the UUID, so it's not a proxy for sorting by creation date. I often wonder why they did that, they must have done it on purpose. On 11/25/17, Peter Da Silva wrote: >>> What about time

Re: [sqlite] Many ML emails going to GMail's SPAM

22, 2017 at 3:36 PM Dominique Devienne <ddevie...@gmail.com> wrote: > On Wed, Nov 22, 2017 at 3:08 PM, Wout Mertens <wout.mert...@gmail.com> > wrote: > > > One more reason for some forum vs a mailing list: You can "like" a post > > without spammi

Re: [sqlite] Many ML emails going to GMail's SPAM

One more reason for some forum vs a mailing list: You can "like" a post without spamming everyone, thus showing your appreciation to the poster and surfacing interesting content for summarization algorithms. On Wed, Nov 22, 2017 at 2:13 PM Peter Da Silva < peter.dasi...@flightaware.com> wrote: >

Re: [sqlite] how into insert row into middle of table with integer primary key

On Tue, Nov 21, 2017, 11:10 PM Jens Alfke, wrote: > > It’s a lot better to use strings, and just increase the length of the > string as necessary. So to insert in between “A” and “C” you add “B”, then > to insert between “A” and “B” you add “AM”, etc. > Except that you can't

Re: [sqlite] Many ML emails going to GMail's SPAM

Discourse has a mailing-list mode you can enable, which will send you all posts (I presume, I never tried it) The default setup sends you interesting new topics at an interval of your choosing. What I like very much about Discourse: - great engagement - easy following of only those topics

Re: [sqlite] Most efficient way to detect on-disk change

Thank you all! I really have to do a thorough read of all the pragmas, there are so many useful things in there! The user version sounds exactly like what I should be using for storing the db version, and presumably the data_version is a little faster still than reading the user version. @Keith,

  1   2   >