Re: [sqlite] Merging FTS indexes

Why not simply query all databases and merge the results in the application? I would think that would perform equally well, and the merging is likely straightforward to implement… On Tue, Nov 8, 2016 at 1:32 PM Jan Berkel wrote: > > I use sqlite as an application specific format

[sqlite] Fastest way to search json array values?

I'm using the JSON1 extension, and I want to find items by json array contents. This means a query like `*SELECT foo.json FROM foo, json_each(foo.json) WHERE json_each.value = "bar";*`, so basically scanning the entire table. I understand that virtual tables can not be indexed, so what other

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

On Wed, Nov 9, 2016 at 12:20 PM Clemens Ladisch <clem...@ladisch.de> wrote: > Wout Mertens wrote: > > Another approach I thought of is to assume I will only have a certain > > number of array items (let's say 5), generate an "where is not null" > &

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

On Wed, Nov 9, 2016 at 11:42 AM Clemens Ladisch <clem...@ladisch.de> wrote: > Wout Mertens wrote: > > I'm using the JSON1 extension, and I want to find items by json array > > contents. > > > > This means a query like `SELECT foo.json FROM foo, json_each(foo.json)

Re: [sqlite] [OT] suggestion for shell script and variable interpolation

COUNT=$(sqlite3 "$db" "SELECT COUNT(*) FROM foo WHERE baz='$BAZ'") should totally work (I quoted $BAZ as a string, don't do that if it is a number, and you should escape any ' in $BAZ). On Wed, Nov 9, 2016 at 11:40 AM Clemens Ladisch wrote: > Luca Ferrari wrote: > > this

Re: [sqlite] How to get row number of an ID in sorted results

An out-of-the-box question: why do you need to have pages at all? I as a user never cared about the exact page a result was on. Take a look at http://use-the-index-luke.com/no-offset which explains how to do keyset pagination. You can also get the total amount of results until a given row by

Re: [sqlite] SQLite as a Shell Script

well, if you have a sysadmin that has "." in their path, you could write a database called "ls" or common misspellings of commands, and that way execute arbitrary code if the sysadmin visits the system. Wout. On Wed, Nov 16, 2016 at 11:43 AM Rowan Worth wrote: > Interesting but

Re: [sqlite] SQLite as a Shell Script

alled "ls", but: > > a) it will not be executable, and > b) the file will start with "SQLite format 3" rather than the requisite > "#!/bin/busybox ash" > > So the sysadmin would still have to manually execute the "script" with an > appropriate she

Re: [sqlite] calculated-value indexes are not covering?

t 06:48:51PM +, Wout Mertens wrote: > > sqlite> create table t(j json, s string); > > sqlite> create index s on t(s); > > sqlite> create index j on t(json_extract(j, '$.foo')); > > sqlite> create index l on t(length(s)); > > In order for any of thes

Re: [sqlite] Mailinglist question

The one sad thing about this really wonderful mailing list is that its archives seem to rank really low in Google searches. Whenever I search for something, I've not encountered the archives with answers yet. Google groups has all the features requested here. I wonder if it can't be set up as a

Re: [sqlite] calculated-value indexes are not covering?

Williams <n...@cryptonector.com> wrote: > On Wed, Aug 09, 2017 at 06:59:18PM +0000, Wout Mertens wrote: > > but… index s is covering and only includes the field s? I thought a > > covering index was one where all the data needed to satisfy the query is > in > > index? I

Re: [sqlite] calculated-value indexes are not covering?

That clears it up, many thanks! On Fri, Aug 11, 2017 at 2:32 PM Richard Hipp <d...@sqlite.org> wrote: > On 8/11/17, Wout Mertens <wout.mert...@gmail.com> wrote: > > Aha ok, great! > > > > Now, forgive me, but there is still a difference in the byte code,

Re: [sqlite] calculated-value indexes are not covering?

0 000 13Transaction0 0 3 0 01 usesStmtJournal=0 14Goto 0 1 000 On Fri, Aug 11, 2017 at 1:31 PM Richard Hipp <d...@sqlite.org> wrote: > On 8/11/17, Wout Mertens <wout.mert...@gmail.com> wro

Re: [sqlite] calculated-value indexes are not covering?

So, am I correct in thinking that an index on expressions already has all the required data to answer e.g. a SELECT DISTINCT? If so, that could be an optimization? Can I request this optimization to be made? :) Thanks, Wout. On Thu, Aug 10, 2017, 7:47 AM Wout Mertens <wout.mert...@gmail.

[sqlite] Optimizing searches across several indexes

Hi, I have a table with a bunch of data (in json). I want to search on several values, each one is indexed. However, if I search for COND1 AND COND2, the query plan is simply SEARCH TABLE data USING INDEX cond1Index (cond1=?) Is this normal? I was hoping it could use the indexes somehow for

Re: [sqlite] Optimizing searches across several indexes

eve the query plans for > all of your queries, and then drop all the indices that are never used. > > -Ursprüngliche Nachricht- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > Im Auftrag von Wout Mertens > Gesendet: Mittwoch, 09. August 2017 08

[sqlite] calculated-value indexes are not covering?

Back with more indexing questions :) 12991 $ sqlite3 SQLite version 3.19.3 2017-06-08 14:26:16 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(j json, s string); sqlite> create index s on

Re: [sqlite] Using multi-value indexes for subset queries

Sqlite is just really smart :) Doing a `count(*)` on my table with one constraint of a two-valued index does a table scan and completes in 9ms, and when I force use of the index, it's 100ms. I'll stop trying to second-guess the query optimizer now ;) On Wed, Aug 9, 2017 at 5:11 PM Wout Mertens

Re: [sqlite] Using multi-value indexes for subset queries

se a specific index, and > if you get an error along the lines of "Error: no query solution" then > you'll know it definitely can't be used, either because of a typo while > making it or some other reason. > > > -Original Message- > From: sqlite-users [mailt

[sqlite] Using multi-value indexes for subset queries

Hi all, in experimenting with indexes I found that if you create an index on (a, b) and then SELECT * FROM data WHERE a = 1 AND B = 2, it will use the index, great. However, if you write SELECT * FROM data WHERE a = 1, it won't use the index. If you write SELECT * FROM data WHERE a = 1 AND B !=

Re: [sqlite] Enforcing uniqueness from multiple indexes

Oh wow, I didn't know about ON CONFLICT, thanks! Unfortunately the behavior on UPDATE (actually INSERT OR REPLACE in my case) is not the desired behavior, it removes the row with the same k but different id. However, using a TRIGGER BEFORE INSERT as well as UPDATE, as proposed by Richard, does

Re: [sqlite] dqlite - SQLite replication and failover library

Very interesting! So how does it behave during conflict situations? Raft selects a winning WAL write and any others in flight are aborted? And when not enough nodes are available, writes are hung until consensus? I won't be able to use it due to Go but it's great to know that this is on the

Re: [sqlite] dqlite - SQLite replication and failover library

.io> wrote: > Wout Mertens <wout.mert...@gmail.com> writes: > > > Very interesting! > > > > So how does it behave during conflict situations? Raft selects a winning > > WAL write and any others in flight are aborted? > > Ah yeah this is probably somethi

[sqlite] Enforcing uniqueness from multiple indexes

Hi, I have the following problem: I have data where two independent values need to be unique. I'm using one (id) as the primary key, and the other (let's call it k) should just cause insertion or updating to fail if it already exists in another row with a different id in the table. Furthermore,

Re: [sqlite] Enforcing uniqueness from multiple indexes

Splendid! Many thanks! On Fri, Aug 18, 2017 at 1:47 PM Richard Hipp <d...@sqlite.org> wrote: > On 8/18/17, Wout Mertens <wout.mert...@gmail.com> wrote: > > > > So, bottom line, is there a way to insert or replace a row so that first > > the id constraint is o

Re: [sqlite] JSON1 extension & speed-up by using indices etc.?

On Tue, Jun 20, 2017 at 2:43 AM Simon Slavin wrote: > On 20 Jun 2017, at 1:34am, Jens Alfke wrote: > > > You can create indexes to support JSON1 queries by using the same > json_xx function calls in a CREATE INDEX statement. > > That’s a great idea. I

[sqlite] C API: which calls have the biggest chance of latency?

I am liking the simplicity of the better-sqlite3 Nodejs library, but it is synchronous (for some good reasons), so it will hang the main thread until sqlite is done. I would like to make it partially asynchronous, still doing most of the work on the main thread, but waiting in a helper thread. I

Re: [sqlite] C API: which calls have the biggest chance of latency?

On Fri, May 26, 2017 at 7:33 AM Simon Slavin <slav...@bigfraud.org> wrote: > > On 26 May 2017, at 6:00am, Wout Mertens <wout.mert...@gmail.com> wrote: > > > Ideally there'd be some way to know if a _step() call will be served from > > buffer… > > There are

Re: [sqlite] SQLite in memory

Note that, as I understand it, if you use only a single connection for the CherryPi server, all the threads on the server will be running the queries sequentially. Try using a database connection per thread? On Thu, May 18, 2017, 8:47 PM Gabriele Lanaro wrote: >

Re: [sqlite] Questions about SQLite Encryption Extension (SEE)

Just musing: is an encrypted disk not more reliable? You have to store the key somewhere… On Thu, Jun 8, 2017, 7:07 PM Richard Hipp wrote: > On 6/8/17, Karl Sanders wrote: > > I would like to know if an encrypted database allows hot backups and > >

Re: [sqlite] Questions about SQLite Encryption Extension (SEE)

Richard Hipp <d...@sqlite.org> wrote: > On 6/8/17, Wout Mertens <wout.mert...@gmail.com> wrote: > > Just musing: is an encrypted disk not more reliable? You have to store > the > > key somewhere… > > Maybe. I guess it depends on your threat model. > > Enc

Re: [sqlite] Using key/value idea to store data & creating normal table VIEW?

Could you not combine the data on the app side? On Sat, 17 Jun 2017, 9:15 AM J Decker, wrote: > Probably need to use some CTE expressions to tackle that. > > https://sqlite.org/lang_with.html > > On Fri, Jun 16, 2017 at 10:59 PM, Robert M. Münch < >

Re: [sqlite] Using key/value idea to store data & creating normal table VIEW?

And another option of course is to store all those extra columns as JSON, which you can query with the JSON1 extension. You can even index on the extracted values. On Sat, 17 Jun 2017, 9:53 AM Wout Mertens, <wout.mert...@gmail.com> wrote: > Could you not combine the data on the

[sqlite] INTEGER PRIMARY KEY AUTOINCREMENT

Is there a way to specify the starting rowid when using autoincrement? Or should I insert and then remove a row with the id set to one less than the desired id? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Adding array parameter support?

wrote: > I agree that being able to bind Array values is very useful, but this > shouldn't > need any new syntax in the SQL, the same ? should be acceptable; this > shouldn't > be any different than the distinction between binding a number vs text > etc. -- > Darren Duncan >

[sqlite] Adding array parameter support?

Hi, I am wondering if the sqlite API could be changed to accommodate array parameters, for example when using the literal `@?`. This would be handy when specifying `INSERT INTO t(@?) VALUES(@?)` or `SELECT * FROM t WHERE v IN (@?)`. Apart from the ease of use benefit, I think this will help

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

Isn't that what cross join is for? Do a select on a virtual table to calculate the value and then use that value in the real where clause? On Wed, Sep 13, 2017, 9:10 AM Hick Gunter wrote: > Try fl_value(...) IN () > > -Ursprüngliche Nachricht- > Von: sqlite-users

[sqlite] Segfault on query related to json_each in where clause

Crashes on 3.19, 3.20, but not on 3.15: $ sqlite3 SQLite version 3.20.0 2017-08-01 13:24:15 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(json JSON); sqlite> select * from t

Re: [sqlite] Possible bug with strftime('%s') < strftime('%s')

Thank you, very interesting! The leap second behavior is slightly worrying, basically anything time-based (animations etc) will take a second longer? What if you want an engine burn to last 2 seconds, set a trigger for 2 seconds from now, and then it's burning 50% longer? On Thu, Oct 12, 2017,

Re: [sqlite] Possible bug with strftime('%s') < strftime('%s')

NOTONIC or CLOCK_REALTIME as the first argument > to clock_gettime(). > > But any API you might use to set a trigger for 2 seconds into the future is > probably already based on a monotonic clock. Polling a realtime clock would > make for a pretty convoluted implementation! > > -Rowan

[sqlite] Segfault on query related to json_each in where clause

Oh interesting, I am seeing it with 3.19.3 contrary to the bug report… Mind you this is High Sierra so Apple may have messed with the build… /usr/bin/sqlite3 --version 3.19.3 2017-06-27 16:48:08 2b0954060fe10d6de6d479287dd88890f1bef6cc1beca11bc6cdb79f72e2377b > Thanks for the bug report. This

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] 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

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] 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

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

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] 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] [EXTERNAL] sqlite_master structure

Don't forget about this handy pragma: PRAGMA schema.table_info(table-name); This pragma returns one row for each column in the named table. Columns in the result set include the column name, data type, whether or not the column can be NULL, and the default value for the column. The "pk" column

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,

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

I'm working with a db that's only written to in transations, and each transaction increases a db-global version counter. This means that I can cache all reads, unless the version changed. What would be the most efficient way to make sure I *never* serve stale data? Right now everything's a

Re: [sqlite] optimizing min/max with calculated index?

Works great for me! On Thu, Oct 26, 2017 at 10:13 PM Richard Hipp wrote: > Y'all please try the lastest trunk version of SQLite for me and let me > know if it works better for you. Thanks. > > -- > D. Richard Hipp > d...@sqlite.org >

Re: [sqlite] calculation with the result of two select results

> WHERE STRFTIME('%Y-%m-%d %H:%M', timestamp) = STRFTIME('%Y-%m-%d %H:%M', 'now', 'localtime', '-1 minute'); Won't this run strftime on all rows? Unless you have a calculated index on that strftime function, I think you should convert the 'now' to a timestamp… Unless of course your table is 5

Re: [sqlite] calculation with the result of two select results

t; 35|Function|15|14|8|strftime(-1)|04| > 36|String8|0|11|0|%Y-%m-%d %H:%M|00| > 37|String8|0|18|0|%Y-%m-%d %H:%M|00| > 38|String8|0|19|0|now|00| > 39|String8|0|20|0|localtime|00| > 40|String8|0|21|0|-1 days|00| > 41|String8|0|22|0|-1 minute|00| > 42|Function|31|18|13|strftime(

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] 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] 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] 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] 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] "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] 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] 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] BedrockDB interview on Floss Weekly

I had never heard of you guys, this looks amazing! How can you only have 222 github stars?! Paxos, so needs at least 3 nodes? How do you use it from an application that normally uses sqlite? Is it a drop-in replacement? I use Node.JS… Interesting that you emulate mysql, given that sqlite tries

[sqlite] optimizing min/max with calculated index?

I have a table with an indexed json field, and I want to know the maximum value of that field. create table events(id integer primary key, json JSON); create index t on events(json_extract(json, '$.ts)); If I do select max(json_extract(json, '$.ts')) from events; it does a table

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] 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?

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 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] 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?

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] 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] 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] 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] 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] 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] [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] 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] 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] 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] 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

[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

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] 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] 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] 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

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 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

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] 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] 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

[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] [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] 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] 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

  1   2   >