[sqlite] Fastest way to add many simple rows to a table?

2016-05-26 Thread Eric Grange
Hi, I am looking for the fastest way to insert many rows to a simple table. By "simple" I mean a relation table (with just a couple integer/key fields) or even a single-column (temp table used for filtering as an alternative to a "in" literal), but I want to insert hundreds of thousandths of

Re: [sqlite] Fastest way to add many simple rows to a table?

2016-05-27 Thread Eric Grange
rings. So "insert from select" seems to have some avantage, and I was wondering if there are other ways to leverage it? On Fri, May 27, 2016 at 12:29 AM, Dominique Pellé <dominique.pe...@gmail.com > wrote: > Richard Hipp <d...@sqlite.org> wrote: > > > On 5

[sqlite] Appropriate Uses For SQLite

2015-02-19 Thread Eric Grange
> https://www.sqlite.org/whentouse.html : I am currently relying on SQLite as main database engine for blockchain explorers, that's usually between 1 and 2 millions http queries per day, pretty much all of them hitting more than 60 GB of SQLite databases. There is a mix of simple table queries,

[sqlite] Multi-table index ersatz?

2015-03-03 Thread Eric Grange
Hi, I have problem where I need a "multi-table index" ersatz, or maybe a better data structure :-) The problem is as follow: - Table A : some fields plus fields A1 & A2 - Table B : some fields plus fields B1 & B2 Both tables have several dozen millions of rows, and both are accessed

[sqlite] Multi-table index ersatz?

2015-03-03 Thread Eric Grange
Yes A2 & B2 are already indexed (individually and in composite indexes) The problem is that this indexing is not selective enough when taken in isolation. Le 3 mars 2015 12:36, "Simon Davies" a ?crit : > On 3 March 2015 at 11:10, Eric Grange wrote: > > > > Hi, &

[sqlite] Multi-table index ersatz?

2015-03-04 Thread Eric Grange
ot; solution. > It might be possible to write a virtual table module that does the same > as your index on C, but with C being a view. I had a look that way, but AFAICT virtual tables do not support indexing, so I would have to index manually. On Tue, Mar 3, 2015 at 4:57 PM, Dan Kennedy w

[sqlite] SQLite not using primary key index anymore

2015-05-19 Thread Eric Grange
Hi, I recently added a field and index to an existing table, and now SQLite seems to be using that index in place of the primary key, even on simple queries Table declaration: CREATE TABLE vin ( id INTEGER PRIMARY KEY AUTOINCREMENT, tx_id INTEGER NOT NULL, from_vout_id INTEGER,

[sqlite] SQLite not using primary key index anymore

2015-05-19 Thread Eric Grange
this would not be too practical. On Tue, May 19, 2015 at 10:15 AM, Eric Grange wrote: > Hi, > > I recently added a field and index to an existing table, and now SQLite > seems to be using that index in place of the primary key, even on simple > queries > > Table declarat

[sqlite] SQLite not using primary key index anymore

2015-05-19 Thread Eric Grange
While it happened I tested on 3.8.10.1 and 3.8.8.1 The DB is using WAL mode, and there were several connections to it, I suppose this might have interfered with the index or stats? On Tue, May 19, 2015 at 10:50 AM, Clemens Ladisch wrote: > Eric Grange wrote: > > I recently added a

[sqlite] Registering Virtual Tables on the spot (when needed)

2013-11-07 Thread Eric Grange
Hi, I would like to use SQLite in a scripting context where there are thousands of "potential" virtual tables, but of these, only a very very small subset (a couple or a handful at most) would be actually used in any given script, so it wouldn't make sense to expose them all, all the time. I'm

[sqlite] Backing up SQLite DB with unbuffered I/O

2015-10-29 Thread Eric Grange
Is there a way to perform an online backup with unbuffered I/O? I am currently performing backups with the command-line tool and ".backup", but this appears to wreak havoc on the OS disk cache. I am able to mitigate this by disabling buffering on the destination drive, but that cannot be used

[sqlite] json1 not escaping CRLF characters

2016-02-04 Thread Eric Grange
Hello, I have been experimenting with using the json1 extension to generate json sql-side rather than code-side, but I am hitting an issue with the CR & LF characters which are not escaped, which results in json that fails JSON.parse or use as JSONP in browsers. Ideally CR / LF should be encoded

[sqlite] json1 not escaping CRLF characters

2016-02-04 Thread Eric Grange
Thanks! I can make do with replace() for now (as in my particular case, I only have CRLF & TAB to replace) On Thu, Feb 4, 2016 at 11:39 AM, Richard Hipp wrote: > On 2/4/16, Richard Hipp wrote: > > On 2/4/16, Eric Grange wrote: > >> Hello, > >> > >&

[sqlite] Performance comparison between SQLite and SQL Server?

2016-02-16 Thread Eric Grange
IME when SQLite is applicable (mostly the one writer limit), it usually runs circles around to server DBs, provided: - you have comparable CPU - you have enough cache memory, or free RAM for OS disk caching, to be in a comparable memory scenario - you do not have a virtualisation layer, especially

[sqlite] Are BLOB stored inline with the rest of the record fields?

2016-03-02 Thread Eric Grange
Hi, I am wondering if very small BLOBs are stored inline with the record fields (in the same page) or in another page? So far I had not worried about it, but now I have a usage case where I need to store 128 and 256 bit integers (numbers, not IDs or hashes), SQLite does not support such large

[sqlite] Are BLOB stored inline with the rest of the record fields?

2016-03-03 Thread Eric Grange
> All BLOBs are stored inline. The only complication comes when the total row length is longer than a page. Ok thanks! That is besides my original question but what is the complication in that case? Is the blob partially stored in the row, or is some indirection to other pages used, with only

[sqlite] Are BLOB stored inline with the rest of the record fields?

2016-03-04 Thread Eric Grange
inal Message- > > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > > bounces at mailinglists.sqlite.org] On Behalf Of Eric Grange > > Sent: Thursday, March 03, 2016 3:16 AM > > To: SQLite mailing list > > Subject: Re: [sqlite] Are BLOB stored inline wi

[sqlite] Virtual Tables performance

2008-02-20 Thread Eric Grange
We've begun implementing some virtual tables with latest SQLite build and run into a few questions performance-wise, all these questions revolve around the same issue (join performance). 1) Is there a way to know which fields are going to be potentially requested by xColumn? For instance if I

[sqlite] FTS3 performance rowid vs docid

2009-12-18 Thread Eric Grange
Hello All, Using 3.6.21 and FTS3, I've noticed than when doing search queries, using docid as in select fts.docid from fts where fts.content match "la" the search is much slower than when using rowid as in select fts.rowid from fts where fts.content match "la" Isn't docid supposed to

Re: [sqlite] Fastest way to add many simple rows to a table?

2016-05-31 Thread Eric Grange
> I tried your example and got ~0.45s for json and ~0.37s for my > implementation; I have also tried a custom virtual table, and I also get figures about 20-30% faster than with json. > Interesting: when NOT using "primary key" (I have a flag for this), the > times are ~ 0.26s vs 0.18s vs 0.47s

Re: [sqlite] "Responsive" website revamp at www.sqlite.org

2016-09-06 Thread Eric Grange
Seems to be fine from here, and probably not too critical, as not all websites have a purpose on mobile devices. However, while you are at it, an improvement for the website when browsed from a computer would be to limit the max width of the pages, especially for the documentation pages.

Re: [sqlite] "Responsive" website revamp at www.sqlite.org

2016-09-06 Thread Eric Grange
On Tue, Sep 6, 2016 at 10:19 AM, Stephan Beal <sgb...@googlemail.com> wrote: > On Tue, Sep 6, 2016 at 10:15 AM, Eric Grange <zar...@gmail.com> wrote: > > > However, while you are at it, an improvement for the website when browsed > > from a computer would be

Re: [sqlite] "Responsive" website revamp at www.sqlite.org

2016-09-06 Thread Eric Grange
t; > On 2016/09/06 10:19 AM, Stephan Beal wrote: > >> On Tue, Sep 6, 2016 at 10:15 AM, Eric Grange <zar...@gmail.com> wrote: >> >> However, while you are at it, an improvement for the website when browsed >>> from a computer would be to limit the max width of th

Re: [sqlite] Ideas for optional json fields?

2016-09-26 Thread Eric Grange
Nice solution and works like a charm, thanks Clemens! On Mon, Sep 26, 2016 at 4:22 PM, Clemens Ladisch <clem...@ladisch.de> wrote: > Eric Grange wrote: > > I am generating a json with json_object() function, and I would like to > > omit the null or default fields from the o

[sqlite] Ideas for optional json fields?

2016-09-26 Thread Eric Grange
Hi, I am generating a json with json_object() function, and I would like to omit the null or default fields from the output json (for compacity reasons) When there is only one such field, I have used a "case when" like in select case when field2 is null then json_object('field1',

Re: [sqlite] Fastest way to backup a "live" database

2016-10-05 Thread Eric Grange
Eric On Wed, Oct 5, 2016 at 12:50 PM, Clemens Ladisch <clem...@ladisch.de> wrote: > Eric Grange wrote: > > Ran some tests with variations of Clemens's backup exemple, and adding > > > > sqlite3_exec(src, "PRAGMA cache_size=1", NULL, NULL, NULL); >

Re: [sqlite] Fastest way to backup a "live" database

2016-10-05 Thread Eric Grange
Eric On Wed, Oct 5, 2016 at 8:42 AM, Eric Grange <zar...@gmail.com> wrote: > Thanks for the tool Clemens! > > > No, this is what makes the backup restart. With a step size of -1, it > would never restart. > > Hmm, given WAL mode, this brings another question: w

Re: [sqlite] Fastest way to backup a "live" database

2016-10-05 Thread Eric Grange
ant to provide some progress indicator or pace the backup? (ie. pace I/O bandwidth ?) Or in other words, given WAL mode, what do we "lose" when using -1 and to copy everything at once during a backup? On Tue, Oct 4, 2016 at 5:33 PM, Clemens Ladisch <clem...@ladisch.de> wrote:

[sqlite] Fastest way to backup a "live" database

2016-10-04 Thread Eric Grange
Hi, Given a fairly large database (dozens of gigabytes), which uses WAL, and is being accessed continuously (mostly read transactions, but regular write transactions as well), what are the fastest and less disruptive ways to back it up? A basic ".backup" from the CLI can occasionnally take

Re: [sqlite] Fastest way to backup a "live" database

2016-10-04 Thread Eric Grange
On Tue, Oct 4, 2016 at 4:05 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 4 Oct 2016, at 2:53pm, Eric Grange <zar...@gmail.com> wrote: > > > I am going on the assumption that if something fails during backup, the > > backup itself will be toast anyway, but

Re: [sqlite] Ideas for optional json fields?

2016-09-26 Thread Eric Grange
t;:"value","field2":""} I also tried setting the field name to null, but that triggers an error "json_object() labels must be TEXT" On Mon, Sep 26, 2016 at 2:28 PM, R Smith <rsm...@rsweb.co.za> wrote: > > On 2016/09/26 11:15 AM, Eric Grange wrote: &g

Re: [sqlite] Typical suffixes of sqlite database files

2016-10-21 Thread Eric Grange
> What suffixes to sqlite database files do you use or see in the wild? .sql3 .dat On Fri, Oct 21, 2016 at 7:46 AM, Jean-Christophe Deschamps wrote: > At 01:17 21/10/2016, you wrote: > > What suffixes to sqlite database files do you use or see in the wild? >> > > I

Re: [sqlite] SQLite 3.16.0 enters testing

2017-01-01 Thread Eric Grange
> If this is true, then perf is entirely unsuitable for > microoptimization, since microoptimization depends on having > reproducible results. Reproducibility can be misleading however if it is achieved by simulated CPU cache and instruction pipelines, as this could lead to favoring the wrong

Re: [sqlite] RIGHT JOIN! still not supported?

2017-03-22 Thread Eric Grange
For the sake of curiosity, is anyone (as in any human) using RIGHT JOIN? Personally I never had a need for a RIGHT JOIN, not because of theoretical or design considerations, but it just never came into my flow of thought when writing SQL... I guess some automated SQL query generators could use

Re: [sqlite] More built-in functions for basic math

2017-03-09 Thread Eric Grange
A bonus of having them defined in the core is that it avoids the minor inconsistencies that are bound to arise in custom implementations (starting with the name of math functions) Main downside is probably not going to be the size, but that it reserves more names, and may conflict with existing

Re: [sqlite] SQLite 3.18.0 pre-release snapshot

2017-03-07 Thread Eric Grange
Very interesting development, thanks for pushing the boundaries at each new release! Would it be possible to consider some form of deferred optimize? ie. rather than optimize when closing the connection, it would just write the optimize info gathered during the heavy queries, for use in a future

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

2017-08-04 Thread Eric Grange
The main problem with multiple writers would be in conflict resolution, locking and deadlocks. Imagine a simple accounting transactions "update accounts set value = value + 1 where ..." if run at the same time from 2 threads (or close enough), then if you do not have conflict resolution in place

Re: [sqlite] Version 3.20.0 coming soon...

2017-07-14 Thread Eric Grange
> Could I suggest that the STMT virtual table be renamed as something like sqlite_statements ? > Or anything else that starts with "sqlite_" ? Seconded, especially as more and more virtual tables become available (a good thing), there is a greater risk of name collision, so an "sqlite_" namespace

Re: [sqlite] [OT] UTF8-BOM and text encoding detection (was: UTF8-BOM not disregarded in CSV import)

2017-06-27 Thread Eric Grange
> ASCII / ANSI is a 7-bit format. ASCII is a 7 bit encoding, but uses 8 bits in just about any implementation out there. I do not think there is any 7 bit implementation still alive outside of legacy mode for low-level wire protocols (RS232 etc.). I personally have never encountered a 7 bit ASCII

Re: [sqlite] UTF8-BOM and text encoding detection (was: UTF8-BOM not disregarded in CSV import)

2017-06-29 Thread Eric Grange
der to figure out down the road where the garbage in your database originated from (incorrect input? bug in the heuristics? etc.) On Wed, Jun 28, 2017 at 10:40 PM, Tim Streater <t...@clothears.org.uk> wrote: > On 28 Jun 2017 at 14:20, Rowan Worth <row...@dug.com> wrote: > > > On

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

2017-06-09 Thread Eric Grange
> Isn't it all just obfuscation? Not really, the encryption protects the file, wherever it is, as long as the attacker does not have access to the application keys or application memory. > If the adversary is another process on the same host, encrypting the db > just adds obfuscation, which is

Re: [sqlite] UTF8-BOM not disregarded in CSV import

2017-06-26 Thread Eric Grange
Alas, there is no end in sight to the pain for the Unicode decision to not make the BOM compulsory for UTF-8. Making it optional or non-necessary basically made every single text file ambiguous, with non-trivial heuristics and implicit conventions required instead, resulting in character

Re: [sqlite] UTF8-BOM not disregarded in CSV import

2017-06-26 Thread Eric Grange
>Easily solved by never including a superflous BOM in UTF-8 text And that easy option has worked beautifully for 20 years... not. Yes, BOM is a misnommer, yes it "wastes" 3 bytes, but in the real world "text files" have a variety of encodings. No BOM = you have to fire a whole suite of

Re: [sqlite] [OT] UTF8-BOM and text encoding detection (was: UTF8-BOM not disregarded in CSV import)

2017-06-27 Thread Eric Grange
> In case 7 we have little choice but to invoke heuristics or defer to the > user, yes? Yes in theory, but "no" in the real world, or rather "not in any way that matters" In the real world, text files are heavily skewed towards 8 bit formats, meaning just three cases dominate the debate: - ASCII

Re: [sqlite] Proof that a line has been modified

2017-09-08 Thread Eric Grange
For complete tamper-proofness, you need "something" external to the system, ie. not in the database, not in the code, and not on the server: - if you use signatures, then those signatures should be made by a secure signing service or device - if you use hashes (be it a merkle tree, a blockchain,

Re: [sqlite] Seeking advice on improving batch sql update performance on zipvfs

2017-09-11 Thread Eric Grange
> I've also attached results of "EXPLAIN UPDATE ITEM SET FIELD4 = 10 WHERE DS = 15;". Not zipvfs specific, but that kind of update can be quite inefficient if the record is large (in terms of bytes, or records per database page) as your table declaration hints to. This will be especially

[sqlite] Performance issue with left join in 3.24 compared with 3.22

2018-06-26 Thread Eric Grange
Hi, I am experiencing a massive performance issue on a query with a left join in 3.24, now taking 40 seconds, whereas in 3.22 it was just a few milliseconds. The problematic query looks like select d.key_field, count(*) nb from low_volume_table b join mid_volume_table c on

Re: [sqlite] Performance issue with left join in 3.24 compared with 3.22

2018-06-26 Thread Eric Grange
results in the same table scan of high_volume_table first, just without the index. - using the unary "+" on the d table filters has no effect on the query plan (as these are not indexed in the first place I guess) Using unlikely() on the d table filters seems to be the only option that works. On Tue, Jun

Re: [sqlite] Performance issue with left join in 3.24 compared with 3.22

2018-06-26 Thread Eric Grange
is dynamically generated from user options and filters (which can indeed lead to SQL that is not really "optimal"). Is having a cross join somewhere among the joins enough to "disable" the left join strength reduction for other joins? On Tue, Jun 26, 2018 at 5:58 PM, Richard Hipp wrote:

Re: [sqlite] How to Handle BigInt

2018-05-03 Thread Eric Grange
As quick insight I gleaned from this list sometime ago: if you only need to be able to search and sort, the blobs can be used. If you can live with a fixed size (f.i. 256bits), then just use the fixed-size big-endian representation as blob content. For variable-length big integers, encode them

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

2018-01-09 Thread Eric Grange
<slav...@bigfraud.org> wrote: > On 9 Jan 2018, at 9:50am, Eric Grange <zar...@gmail.com> wrote: > > > then I fill that table with something like > > > > INSERT INTO RANKED > > SELECT key, value > > FROM ...something rather

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

2018-01-09 Thread Eric Grange
Tue, Jan 9, 2018 at 11:44 AM, Dominique Devienne <ddevie...@gmail.com> wrote: > On Tue, Jan 9, 2018 at 11:26 AM, Eric Grange <egra...@glscene.org> wrote: > > > So the order by is used to control the insertion order, so that the RANK > > autoinc primary key ends up with na

[sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

2018-01-09 Thread Eric Grange
Hi, I have a problem where I have a large set of (key, value) which I want to sort by value, and then store in a table having (rank, key, value) fields, so that for a given key I can quickly find the rank, or for a given rank range, I can quickly list the keys & values. Since there is no

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

2018-01-12 Thread Eric Grange
t case ? On Tue, Jan 9, 2018 at 6:21 PM, Simon Slavin <slav...@bigfraud.org> wrote: > On 9 Jan 2018, at 11:35am, Eric Grange <egra...@glscene.org> wrote: > > > In both cases, since things are constantly in flux, the absolute rank and > > neighbor do not really matte

Re: [sqlite] Reducing index size

2018-07-31 Thread Eric Grange
r using a database is to automate the creation > and use of efficient search algorithms with the tradeoff of more > expenditure on disk space. > > I hope you find an acceptable solution, >Donald > > > > > > On Mon, Jul 30, 2018 at 4:32 AM Eric Grange wrote:

[sqlite] Quick way to figure SQLite database block size per table & indexes

2018-07-30 Thread Eric Grange
Hi, Is there a quick way (as in speed) to obtain the number of database blocks (or kilobytes) allocated for each table and each index ? I have been using various manual approaches so far (from using length() to vacuum and drops), but none of them are really fast or can practical to automate.

Re: [sqlite] Quick way to figure SQLite database block size per table & indexes

2018-07-30 Thread Eric Grange
download sqlite3_analyzer Thanks, the text output is interesting with built-in documentation. On Mon, Jul 30, 2018 at 9:06 AM, D Burgess wrote: > download sqlite3_analyzer > from > http://www2.sqlite.org/2018/sqlite-tools-linux-x86-324.zip > > > > On Mon, Jul 30, 2018 at 4:46

[sqlite] Reducing index size

2018-07-30 Thread Eric Grange
Hi, Is there a way to reduce the size of an index on strings/blobs ? I have tables which are a key + value, the key is an "integer primary key autoincrement", which is used for references in all other tables of the schema. The values are cryptographic GUIDs (so 256 to 512 bits in size) with a

Re: [sqlite] Reducing index size

2018-07-30 Thread Eric Grange
Jul 30, 2018 at 11:16 AM, Paul Sanderson < sandersonforens...@gmail.com> wrote: > If I understand correctly then changing from a base64 index to a blob > containing the raw bytes would save 25% > > Paul > www.sandersonforensics.com > SQLite Forensics Book <https://www.amazo

Re: [sqlite] Quick way to figure SQLite database block size per table & indexes

2018-07-30 Thread Eric Grange
30, 2018 at 10:11 AM Eric Grange > wrote: > > > >> @David Yip > >> > If you have the dbstat module enabled (-DSQLITE_ENABLE_DBSTAT_VTAB), > you > >> > can query the dbstat virtual table for the number of pages used per > >> table and index, e

Re: [sqlite] Reducing index size

2018-07-30 Thread Eric Grange
@Dominique Devienne > SQLite supports function-based indexes, but unfortunately if does not support "function-based columns". Far fetched maybe, but could a virtual table or table-valued functions be used to provide that? ie. use the virtual table to pass data directly to an index, and then

Re: [sqlite] Reducing index size

2018-07-30 Thread Eric Grange
12:05 PM, Rowan Worth wrote: > On 30 July 2018 at 17:53, Eric Grange wrote: > > > @Rowan Worth > > > What if you could create a "lite" index, which stores just the rowids > in > > a particular order and > > > refers back to the table for the rest

[sqlite] sqlite3_column_decltype and affinity

2018-03-30 Thread Eric Grange
Hi, Is there a way to have sqlite3_column_decltype return the affinity for an expression ? https://www.sqlite.org/datatype3.html#affinity_of_expressions states that a CAST can be used to specify the affinity of a column, however sqlite3_column_decltype does not report that affinity, as is

Re: [sqlite] sqlite3_column_decltype and affinity

2018-04-01 Thread Eric Grange
to be able to recover that info whe it is provide through a cast. On Fri, Mar 30, 2018 at 6:04 PM, Simon Slavin <slav...@bigfraud.org> wrote: > On 30 Mar 2018, at 11:22am, Eric Grange <zar...@gmail.com> wrote: > > > Is there a way to have sqlite3_column_decltype return the affini

Re: [sqlite] sqlite3_column_decltype and affinity

2018-04-02 Thread Eric Grange
ed type of "type". >> so the sql engine already has to keep track of the affinity specified through "cast()" if I understood the above correctly On Sun, Apr 1, 2018 at 3:19 PM, Simon Slavin <slav...@bigfraud.org> wrote: > On 1 Apr 2018, at 8:19am, Eric Grange <

Re: [sqlite] The upcoming 3.23.0 release

2018-03-23 Thread Eric Grange
> Add the LEFT JOIN strength reduction optimization that converts a LEFT JOIN into an ordinary JOIN A question on this one: I have been using LEFT JOIN for many years (and not juste in SQLIte) in cases where a JOIN could have been used as a way to "hint" query optimizers which tables to scan

[sqlite] SQLite pre-compiled DLL for Windows x64

2018-09-27 Thread Eric Grange
Hi, For version 3.25.2, the precompiled x64 DLL appears missing, there are several vsix downloads, but when extracting the bundled DLLs (in the Redist/Retail directories), they appear different (or non-standard ones that need some form of post-processing ?), for instance the x86 dll is larger

[sqlite] upsert from select

2018-11-30 Thread Eric Grange
Hi, I am running into a little trouble when trying to use and "upsert" from a select clause. Starting from the "vocabulary" exemple at https://sqlite.org/lang_UPSERT.html this works INSERT INTO vocabulary(word, count) SELECT 'jovial', 1 ON CONFLICT(word) DO UPDATE SET count=count+1 but as

Re: [sqlite] upsert from select

2018-11-30 Thread Eric Grange
ocabulary(word) > SELECT w FROM A WHERE 1 > ON CONFLICT(word) DO UPDATE SET wcount=wcount+1 > ; > > > SELECT * FROM vocabulary > >-- word |wcount >-- -- | >-- jovial | 2 > > > PS: I used "wcount" rather because &

Re: [sqlite] [EXTERNAL] json_group_array() and sorting

2019-01-08 Thread Eric Grange
der, with an order by clause in a > subselect if necessary. > > If you have an order by clause which is already fulfilled by the > visitation order, SQLite will not sort again. > > -Ursprüngliche Nachricht- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlit

[sqlite] json_group_array() and sorting

2019-01-08 Thread Eric Grange
Hi, Is json_group_array() supposed to honor a sorting clause or not ? (and concatenation aggregates in general) I have a query like select json_group_array(json_object( 'id', st.id, 'num', st.numeric_field, ...bunch of fields here... )) from some_table st ...bunch of joins here...

Re: [sqlite] [EXTERNAL] json_group_array() and sorting

2019-01-08 Thread Eric Grange
could end up wrecking existing queries where the subtype currently survives. Eric Le mar. 8 janv. 2019 à 11:41, Dominique Devienne a écrit : > On Tue, Jan 8, 2019 at 11:04 AM Dominique Devienne > wrote: > > > > On Tue, Jan 8, 2019 at 10:50 AM Eric Grange wrote: > >&g

[sqlite] Location of error in SQL statements ?

2019-05-07 Thread Eric Grange
Hi, Is there are way to get more information about an SQL syntax error message ? For example on a largish SQL query I got the following error message near "on": syntax error but as the query is basically a long list of joins, this is not too helpful ;) Having a character offset, a line