[sqlite] Performance of writing blobs

I'm surprised about the commit time of SQLite, when writing blobs is involved. Can anybody shed light on this subject? Below's a description of what I do, with the results. I've exporting data into SQLite, spread in several tables. I process only about 240,000 rows, and write around 1GB in 20,000

Re: [sqlite] Performance of writing blobs

On Mon, Jun 11, 2018 at 4:27 PM Clemens Ladisch wrote: > Dominique Devienne wrote: > > My assumption > > was that after the zeroblob(N), there was enough room in the main DBs > > pages, such that the subsequent blob open+write+close did not need to > > generate any

Re: [sqlite] Performance of writing blobs

On Tue, Jun 12, 2018 at 8:03 AM Clemens Ladisch wrote: > Dominique Devienne wrote: > > On Mon, Jun 11, 2018 at 4:27 PM Clemens Ladisch > wrote: > >> It does write to the same pages, but those pages must be copied to the > >> rollback journal so that they can be

Re: [sqlite] Performance of writing blobs

On Tue, Jun 12, 2018 at 12:49 PM Clemens Ladisch wrote: > Dominique Devienne wrote: > > In JOURNAL mode, new data goes to DB file directly, and modified pages > go to the JOURNAL file. > > And since here this is INSERT-only, from empty tables, I assumed pages > copied to

[sqlite] order by column_name collate custom_collation, with virtual table index

From reading this list, I've learned that for an index to have a change to be used to consume an order by, the collation of the query and the index must match. But in many instances, that index is one from a virtual table we implement. So is there a way to tell SQLite that vindex is of a given cus

Re: [sqlite] order by column_name collate custom_collation, with virtual table index

On Thu, Jun 28, 2018 at 10:59 AM Dominique Devienne wrote: > So is there a way to tell SQLite that vindex is of a given custom > collation, > to open the possibility of the index being used? > Note that there's no mention at all of "collation" or "collate" i

Re: [sqlite] [EXTERNAL] order by column_name collate custom_collation, with virtual table index

On Thu, Jun 28, 2018 at 11:18 AM Hick Gunter wrote: > The xBestIndex function needs to call the sqlite_vtab_collation() function > to query the collation name required for each constraint and return the > appropriate index number. > > Subs: yes, yes, see above > Oh, great! Thanks Gunther!!! Ric

Re: [sqlite] [EXTERNAL] order by column_name collate custom_collation, with virtual table index

On Thu, Jun 28, 2018 at 11:48 AM Dominique Devienne wrote: > > On Thu, Jun 28, 2018 at 11:18 AM Hick Gunter wrote: >> >> The xBestIndex function needs to call the sqlite_vtab_collation() function to query the collation name required for each constraint and return the approp

Re: [sqlite] order by column_name collate custom_collation, with virtual table index

On Thu, Jun 28, 2018 at 2:03 PM Richard Hipp wrote: > On 6/28/18, Dominique Devienne wrote: > > From reading this list, I've learned that for an index to have a change > to > > be used to consume an order by, the collation of the query and the index > > must match.

Re: [sqlite] Bug: SQLITE_DEFAULT_LOOKASIDE does not compile without SQLITE_OMIT_COMPILEOPTION_DIAGS

On Mon, Jul 23, 2018 at 12:57 PM Dan Kennedy wrote: > On 07/22/2018 07:48 PM, Victor Costan wrote: > > In a custom SQLite build, SQLITE_DEFAULT_LOOKASIDE results in compilation > > errors, unless used with SQLITE_OMIT_COMPILEOPTION_DIAGS. > > > > This is because src/ctime.c includes the following

Re: [sqlite] Bug: SQLITE_DEFAULT_LOOKASIDE does not compile without SQLITE_OMIT_COMPILEOPTION_DIAGS

On Mon, Jul 23, 2018 at 5:37 PM Dan Kennedy wrote: > On 07/23/2018 06:36 PM, Dominique Devienne wrote: > > On Mon, Jul 23, 2018 at 12:57 PM Dan Kennedy > wrote: > > The diff adds: > > > > #define CTIMEOPT_VAL2_(opt1,opt2) #opt1 "," #opt2 > >

Re: [sqlite] Create VIEW passing despite invalid column name specified

On Fri, Jul 27, 2018 at 1:58 AM Richard Hipp wrote: > On 7/26/18, Tomasz Kot wrote: > > Hello, > > > > Beneath sql shall throw an error on CREATE VIEW statement (as invalid > > column is specified), but it passes (SQLite 3.23.1). > > The error is deferred until you try to use the view. The rea

Re: [sqlite] Create VIEW passing despite invalid column name specified

On Fri, Jul 27, 2018 at 11:33 AM R Smith wrote: > On 2018/07/27 10:40 AM, Dominique Devienne wrote: > > On Fri, Jul 27, 2018 at 1:58 AM Richard Hipp wrote: > >> On 7/26/18, Tomasz Kot wrote: > >>> Beneath sql shall throw an error on CREATE VIEW statement (as inv

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

On Mon, Jul 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.g. > > Thanks, I did not know about that module, however it is

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

Oops, sent too early... On Mon, Jul 30, 2018 at 10:29 AM Dominique Devienne wrote: > On Mon, Jul 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

Re: [sqlite] Reducing index size

On Mon, Jul 30, 2018 at 10:42 AM Simon Slavin wrote: > On 30 Jul 2018, at 9:32am, Eric Grange wrote: > > > As these are cryptographic GUIDs, the first few bytes of a values are in > > practice unique, so in theory I can index just the first few bytes (using > > substr()), > > this indeed reduces

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

On Mon, Jul 30, 2018 at 11:42 AM Eric Grange wrote: > PtrMap pages may be too much overhead in my case, I have occasionally run > vacuum on same databases to see the effect, and it was not very > significant. > > This is likely because the databases are heavily skewed towards inserting > (and ind

Re: [sqlite] [EXTERNAL] UNION ALL bug in Multi-threading

On Wed, Aug 1, 2018 at 10:39 AM Hick Gunter wrote: > Are you sure it is not the special case documented in the ATTACH command? > (see https://sqlite.org/lang_attach.html) > Good point. OP will tell us if it applies to his/her case. > " Transactions involving multiple attached databases are ato

Re: [sqlite] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all

On Thu, Aug 2, 2018 at 12:57 AM Yuri wrote: > The attached testcase injects the foreign key violation into a long > transaction. This makes the remainder of the transaction much slower, > even though the foreign key is deferred, and should only be checked in > the end of the transaction. > Right

Re: [sqlite] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all

On Thu, Aug 2, 2018 at 9:35 AM Keith Medcalf wrote: > You observe no violation when VIOLATION is 0 because there is no > referential integrity violation to report ... > Really Keith? Parent IDs are in range [0, NROWS) Child/FK IDs inserted are in range [NROWS, 2*NROWS) How's that not an FK viol

Re: [sqlite] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all

On Thu, Aug 2, 2018 at 10:34 AM Keith Medcalf wrote: > > Yes. Look at the CREATE TABLE for table A (completely ignore table B as > it serves no purpose whatsoever) > Rah, silly me... I assumed A.aid referenced B.id. Why have a B table at all then. > Also, if you create an index on the chi

[sqlite] SQLite name/identifier length limit?

https://www.sqlite.org/limits.html doesn't say explicitly. Unlimited? SQLITE_MAX_LENGTH ? PostgreSQL is limited to 63 chars. Oracle is limited to 30 and 128 chars (latter > 12.2). Other DBs? (just curious). --DD ___ sqlite-users mailing list sqlite-users

Re: [sqlite] SQLite name/identifier length limit?

On Wed, Aug 29, 2018 at 12:09 PM Richard Hipp wrote: > On 8/29/18, Dominique Devienne wrote: > > https://www.sqlite.org/limits.html doesn't say explicitly. > > Unlimited? SQLITE_MAX_LENGTH ? > > > > PostgreSQL is limited to 63 chars. > > Oracle is limi

Re: [sqlite] DB To DB Transfer Time

On Tue, Sep 25, 2018 at 8:41 PM dmp wrote: > The result for the 50K file db test of SQLite was 370.184 > seconds. Is this a reasonable transfer speed, given the conditions noted? > As Stephen already wrote, impossible to say. Start by contrasting this DB-copy you wrote to other methods: 1) files

Re: [sqlite] DB To DB Transfer Time

On Tue, Oct 2, 2018 at 6:56 PM dmp wrote: > >> On Sep 25, 2018, at 11:14 AM, dmp > wrote: > The timing given takes place at the beginning of the transfer > process and stops when completed, threads join. > Why aren't you measuring the time spent only in the SQLite writing thread? That would el

Re: [sqlite] Persistent snapshots and rollbacks

On Fri, Oct 5, 2018 at 5:55 PM Chris Brody wrote: > Savepoints ()? Savepoints are still part of a transaction, so not visible to readers until the COMMIT. Daniel's use case wants each transaction to become visible to readers, so savepoint do not apply

[sqlite] Find key,value duplicates but with differing values

I can find duplicates fine: select xmd.partId, parts.title, xmd.name, count(*) "#dupplicates", group_concat(xmd.value) "values", group_concat(xmd.idx) "indexes" from extra_meta_data xmd join parts on parts.id = xmd.partId group by partId, name having "#dupplicates" > 1;

Re: [sqlite] [EXTERNAL] Find key, value duplicates but with differing values

On Thu, Oct 11, 2018 at 6:21 PM Hick Gunter wrote: > Two nested selects > The inner select groups by partId, name, value > The outer select groups by partId, name > Thank you who replied, Gunter, Ryan, Roman, David. This was simpler than I thought. I should have reflected a bit more myself :) --

Re: [sqlite] R*Tree performance

On Mon, Oct 22, 2018 at 2:03 PM Richard Hipp wrote: > On 10/22/18, Zoltan Demeter wrote: > > I have a rather poor performance of 50 k inserts per second. The data > > to be inserted is precalculated and passed to the loop. I am using the > > same logic as above, so the loop is wrapped in a trans

Re: [sqlite] Regarding CoC

On Wed, Oct 24, 2018 at 1:10 PM Wout Mertens wrote: > [...] write the rules in Ye Olde English. [..] > He was "Italian", and more likely to write in Latin, not English, old or new. The SQLite doc is English only because that's DRH native tong (I assume). Lets not pretend the rules are from Engli

Re: [sqlite] curious discovery about geopoly module

On Wed, Oct 31, 2018 at 12:51 PM Graham Holden wrote: > > There are, of course, multiple apps on my system that use sqlite3.dll - > including the Bricscad app that I am running my vba code from.Speculating > somewhat: Have you tried updating the copy Briscad is using? If one version > is already

Re: [sqlite] Displaying row count

On Wed, Oct 31, 2018 at 3:55 PM Clemens Ladisch wrote: > David Fletcher wrote:> Hi all, > > Is there a mode in the sqlite shell, or some fancy extension, that will > display a row > > number when outputting results? > > No. You'd have to modify the shell, or add the row_number() window > functio

Re: [sqlite] [EXTERNAL] found a glitch in ALTER TABLE RENAME (3.25.x)

On Fri, Nov 9, 2018 at 8:26 AM Simon Slavin wrote: > On 9 Nov 2018, at 7:11am, Hick Gunter wrote: > > Foreign keys are ignored by default and need to be explicitly enabled. I > would expect this to include everything that relates to foreign keys. > > I've casually discovered that the behavior of

Re: [sqlite] Suitability for Macintosh OS 9.2?

On Mon, Nov 19, 2018 at 4:34 PM Simon Slavin wrote: > On 19 Nov 2018, at 1:58pm, Charles Hudson wrote: > > > I come from an ANSI SQL client / server background (Oracle, MS SQL) but > am interested in finding a SQL database to install on an old Macintosh G3 > Power PC that is running OS 9.2. > OS

Re: [sqlite] Creating and dropping tables with multiple connections

On Tue, Nov 20, 2018 at 5:28 PM Simon Slavin wrote: > On 20 Nov 2018, at 3:34pm, Albert Banaszkiewicz < > albert.banaszkiew...@tomtom.com> wrote: > > > ExecuteInTransaction(writeDb1, KCreateTable); > > I can't answer your question but the above line shows a misunderstanding > of SQL. Transaction

Re: [sqlite] Boosting insert and indexing performance for 10 billion rows (?)

On Wed, Nov 28, 2018 at 6:03 PM AJ M wrote: > [...] The data comes out to 10 billion rows of an 8 byte signed integer (~200-300 gb pre-index), and while insertion takes ~6 hours, indexing takes > 8 hours by > itself. [...] query speed is fine as-is. [...] > Hi AJ. Your message is quite intrigui

Re: [sqlite] Detecting erroneous multi-row subqueries

On Fri, Nov 30, 2018 at 3:00 AM J. King wrote: > On 2018-11-29 20:56:13, "Richard Hipp" wrote: > >On 11/29/18, J. King wrote: > >> Is it possible to make SQLite fail like PostgreSQL does? > > > >That is possible in theory, but how many of the millions of existing > >applications would that br

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

On Fri, Nov 30, 2018 at 2:10 PM Simon Walter wrote: > How does one use WHERE x IN (?) with a prepared statement? What is the > correct way to do this? > You cannot do it. Must use WHERE x IN (?, ?, ?), i.e. an explicit and known in advance number of bind placeholders. Or not use binding at all,

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

On Fri, Nov 30, 2018 at 2:44 PM Richard Hipp wrote: > On 11/30/18, Simon Walter wrote: > > Thanks Dominique, > > > > Much appreciated. I can now stop pulling out my hair. I will do > > something with sprintf. > > See https://www.sqlite.org/carray.html Right. Any table-valued function would do

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

On Fri, Nov 30, 2018 at 3:03 PM Dominique Devienne wrote: > On Fri, Nov 30, 2018 at 2:44 PM Richard Hipp wrote: > >> On 11/30/18, Simon Walter wrote: >> > Thanks Dominique, >> > >> > Much appreciated. I can now stop pulling out my hair. I will do >&

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

On Fri, Nov 30, 2018 at 3:26 PM Simon Walter wrote: > On 11/30/2018 11:20 PM, Dominique Devienne wrote: > To be honest, I am using apr_dbd as I would like to support more than > just SQLite. So I will need to play around with MySQL and PostgreSQL at > least and maybe branch if SQLi

[sqlite] Support function_list in pre-built binaries from SQLite download page

sqlite> pragma function_list; sqlite> Nothing returned, so obviously the -DSQLITE_INTROSPECTION_PRAGMAS compile-time option is not used [1]. Could it be ON by default please? TIA, --DD [1] https://www.sqlite.org/pragma.html#pragma_function_list ___ sqli

[sqlite] Misleading error message on missing function

sqlite> select json_each('[1, 3, 5]'); Error: no such function: json_each sqlite> select * from json_each('[1,2]'); 0|1|integer|1|1||$[0]|$ 1|2|integer|2|2||$[1]|$ Misusing the json_each() table-values function as a regular function yields a misleading (IMHO) "no such function" message, despite th

Re: [sqlite] Support function_list in pre-built binaries from SQLite download page

On Fri, Nov 30, 2018 at 4:11 PM Dominique Devienne wrote: > Nothing returned, so obviously the -DSQLITE_INTROSPECTION_PRAGMAS > compile-time option is not used [1]. Could it be ON by default please? > TIA, --DD > ping. ___ sqlite-users

Re: [sqlite] Misleading error message on missing function

On Fri, Nov 30, 2018 at 4:16 PM Dominique Devienne wrote: > sqlite> select json_each('[1, 3, 5]'); > Error: no such function: json_each > [...] > Any chance we might get a more user-friendly error message? > Like perhaps "Error: table-valued function not

Re: [sqlite] Support function_list in pre-built binaries from SQLite download page

On Tue, Dec 4, 2018 at 10:08 AM Dominique Devienne wrote: > On Fri, Nov 30, 2018 at 4:11 PM Dominique Devienne > wrote: > >> Nothing returned, so obviously the -DSQLITE_INTROSPECTION_PRAGMAS >> compile-time option is not used [1]. Could it be ON by default please? >&

Re: [sqlite] Support function_list in pre-built binaries from SQLite download page

On Tue, Dec 4, 2018 at 3:30 PM Richard Hipp wrote: > On 12/4/18, Dominique Devienne wrote: > > Wrong pragma never give errors AFAIK, > > That is how pragmas are designed to work, yes. Unknown pragmas are > silently ignored. Thanks for confirming my "AFAIK" Ri

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

On Thu, Dec 6, 2018 at 8:49 AM Hick Gunter wrote: > ... which is what SQLite does internally if you provide a list of literal > values inside the parentheses. > Which is IMHO a pity that there's no API to bind such an ephemeral table and 'bind" it. Heck, given how the carray() eponymous vtable

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

On Thu, Dec 6, 2018 at 11:10 AM Hick Gunter wrote: > Maybe someone can come up with a CTE that works for this... > How so? I'm not following you. Ryan already provided a CTE to transform a string into values, but that involves string parsing, and is kinda ugly (no offence Ryan :) ), and unlikely

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

On Thu, Dec 6, 2018 at 12:12 PM Hick Gunter wrote: > The cost of the first query plan is therefore O(a * b * (m + log n)) or > O(a *b *m) + O(a * b * log n) > The cost of the second query plan is only O(log n + m * (log a + log b)) > or O((log a*b) * m) + O(log n) > > This makes the second query

[sqlite] Claimed vulnerability in SQLite: Info or Intox?

https://blade.tencent.com/magellan/index_en.html Sounds to me it more related to a "remote callable" program like Chrome, than SQLite proper, but I'd like an official stance on SQLite itself please. Thanks, --DD ___ sqlite-users mailing list sqlite-user

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

On Tue, Dec 18, 2018 at 11:13 PM Richard Hipp wrote: > On 12/18/18, Dominique Devienne wrote: > > https://blade.tencent.com/magellan/index_en.html > > > > Sounds to me it's more related to a "remote callable" program like > Chrome, > > than SQLi

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

On Wed, Dec 19, 2018 at 11:14 AM Richard Hipp wrote: > > Could there be a way to make shadow tables off-limit to arbitrary SQL? > > That is one of the things that the new SQLITE_DBCONFIG_DEFENSIVE > option does - it makes shadow tables read-only so that they cannot be > corrupted by SQL. > May I

Re: [sqlite] Using sqlite3_interrupt with a timeout

On Mon, Dec 31, 2018 at 10:31 PM Keith Medcalf wrote: > >I don't think the interrupt call will actually terminate a step that > >is actually being processed, but only mark that no more steps should > >happen. In other words, I don't think SQLite is spending time > >checking a flag to stop in the

Re: [sqlite] A Minor Issue Report: Extra const Keyword in PragmaName zName

On Wed, Jan 2, 2019 at 1:47 PM Richard Damon wrote: > On 12/30/18 6:10 PM, Richard Green wrote: > const char *const zName; // Note extra space > > Then that is declaring that zName is an immutable pointer to a immutable > string/character, which is actually likely true, as the code shouldn't > be

Re: [sqlite] Support function_list in pre-built binaries from SQLite download page

On Tue, Dec 4, 2018 at 6:16 PM Keith Medcalf wrote: > > My introspection pragma's work, and always have. Then again, I compile with them turned on. Perhaps if they are available the option should appear in the compile_options output, at least? Indeed, that too Keith. But from the looks of it, t

[sqlite] Concatenating text literals with NULL yields NULL

I was just surprised by this behavior, see below. Googling it, seems like SQL Server has a setting the change the behavior in that case. Is this standard SQL behavior, as implemented in SQLite? Not complaining, just asking whether I can depend on it, or not. Thanks, --DD C:\Users\ddevienne>sqlite

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

On Tue, Jan 8, 2019 at 10:50 AM Eric Grange wrote: > Thanks. > > I think I may have encountered a "real" bug while ordering in a subquery. > I have simplified it in the following exemples: > > select json_group_array(o) from ( >select json_object( > 'id', sb.id >) o >from ( >

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

On Tue, Jan 8, 2019 at 11:04 AM Dominique Devienne wrote: > > On Tue, Jan 8, 2019 at 10:50 AM Eric Grange wrote: >> >> Can someone confirm whether this is a bug ? > > > My guess is that it works as "designed", even if this is surprising... > > I believ

[sqlite] WAL mode for in-memory databases?

According to [1] WAL mode does not apply to in-memory databases. But that's an old post, and not quite authoritative when not from the official SQLite docs. I'd like to benefit from the MVCC of WAL mode, but for an in-memory database, with different threads, each with its own connection, accessing

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

On Mon, Jan 14, 2019 at 2:23 PM Wout Mertens wrote: > AFAIK, your best bet is to put a file db on a ramdisk (tmpfs). That's not a very portable solution, and a work-around at best. I don't see anything technical that would prevent WAL to work for ":memory:". "Shared-memory" "in-process" is jus

Re: [sqlite] Bug due to left join strength reduction optimization?

On Tue, Feb 5, 2019 at 7:47 AM Keith Medcalf wrote: > sqlite> select ... from tab left join tab as tab2 on 0 ... > Can someone please educate me on this {{ on 0 }} join "condition" ? I'm not following what the intended meaning is... Must have one, since OP "expects 1 row with one column contain

Re: [sqlite] sqlite 3.37.1: void function returns value

On Mon, Feb 11, 2019 at 11:16 AM Jonas Bülow wrote: > > Just tried to update my sqlite version from 3.24 to 3.27.1 and the > > compiler complained about a void function returning a value. I don't know > > about C, but in C++ this is undefined behaviour and the clang compiler > > sometimes generat

Re: [sqlite] sqlite 3.37.1: void function returns value

On Mon, Feb 11, 2019 at 11:31 AM Jonas Bülow wrote: > Sorry, I missed some information. It is the MSVC v15.5 compiler that > complains: > > sqlite3.c(58167): error C2220: warning treated as error - no 'object' file > generated [c:\work\sqlite-amalgamation-3270100\sqlite3.vcxproj] > sqlite3.c(5816

Re: [sqlite] sqlite 3.37.1: void function returns value

On Mon, Feb 11, 2019 at 1:11 PM Clemens Ladisch wrote: > Peter da Silva wrote: > > I am pretty sure that the code is not legal C > > Indeed; C99 and C11 say in 6.3.2.2: > | The (nonexistent) value of a void expression (an expression that has > | type void) shall not be used in any way [...] > and

Re: [sqlite] Reading a table from inside a scalar function

On Mon, Feb 18, 2019 at 9:58 AM Arun - Siara Logics (cc) wrote: > [...]. Is it possible to read specific row(s) from a table within same > db (using row ids) from inside a User defined Deterministic scalar function > (C API)? > Yes it is possible, but then your UDF is unlikely to be *Determini

Re: [sqlite] Reading a table from inside a scalar function

On Mon, Feb 18, 2019 at 11:48 AM R Smith wrote: > Note that in a transaction without WAL mode ... your query may or may not > "see" data that is older, > Hmmm, I don't think so. Journal mode, WAL or not, doesn't matter. If you are inside a transaction, you are inside it. And will see the current

Re: [sqlite] Reading a table from inside a scalar function

On Mon, Feb 18, 2019 at 12:25 PM Richard Hipp wrote: > On 2/18/19, Arun - Siara Logics (cc) wrote: > > If known, kindly point me to an existing open source implementation. > > https://www.sqlite.org/src/file/ext/misc/eval.c Looks like the code was updated to use sqlite3_realloc64() despite wha

Re: [sqlite] Reading a table from inside a scalar function

On Mon, Feb 18, 2019 at 1:05 PM R Smith wrote: > If not, I apologize, and is very interested in where then > "read_uncommitted" becomes useful? > read_uncommitted is about changes in *other* transactions than your own. You can always see your own changes, in *your* current transaction. read_uncom

Re: [sqlite] Reading a table from inside a scalar function

On Mon, Feb 18, 2019 at 2:16 PM Arun - Siara Logics (cc) wrote: > Thank you, for the detailed advice, info and the pointer. Is there a > faster way to query the table using row id, that is, skip the query parsing > and planner? I still need the page cache feature and allow for concurrent > modi

Re: [sqlite] ON CONFLICT with partial indexes

On Mon, Feb 18, 2019 at 4:38 PM Richard Damon wrote: > Remember the query plan is determined when that statement is compiled, > which is BEFORE you do the binding of the parameters, so the plan can not > depend on the value of parameters. There is no later attempt to optimize > once the values ar

[sqlite] About server-process-edition branch

Hi, I've reread [1] about the "server mode" of SQLite, but noticed that a) it's not been touched in almost a year [2], and b) it's not mentioned in https://www.sqlite.org/serverless.html It's also limited to same-process clients and synchronous=off, making it "not safe" for production use I guess

Re: [sqlite] Getting data from two JOIN tables

On Wed, Feb 27, 2019 at 2:18 PM Jose Isaias Cabrera wrote: > Thanks. This is exactly what I needed. So, there is really no JOIN here, > or is the "from t outer_t, z outer_z" a JOIN like statement? Where can I > read more about this? And yes, your assessment of t(a, idate) and z(f, > idate) be

Re: [sqlite] Getting data from two JOIN tables

On Wed, Feb 27, 2019 at 3:42 PM Jose Isaias Cabrera wrote: > Thanks, David. I actually like the comma (,) than the words (JOIN, > etc). Less wordy and, to me, more logically flow-y. > Just the reverse as myself. I much prefer explicit join-on, to separate filtering from join-conditions in the

Re: [sqlite] Getting data from two JOIN tables

On Wed, Feb 27, 2019 at 9:20 PM Keith Medcalf wrote: > [...] As such, except in OUTER joins, you do not even have to have the ON > expression related to the table(s) which have been seen so far or even > those in the join expression ... because ON is merely a syntactic substitute for WHERE and m

Re: [sqlite] sqlite3_mprintf not handling positional referencing / conversion

On Thu, Feb 28, 2019 at 12:14 AM Richard Hipp wrote: > On 2/27/19, lnksz wrote: > > 2) If not, a mention in the docs under disadvantages would be a > nice information > > Docs have now been updated. Hi. I don't see it in https://www.sqlite.org/src/timeline. Did I miss it? Or is that doc in som

Re: [sqlite] sqlite3_mprintf not handling positional referencing / conversion

On Thu, Feb 28, 2019 at 10:16 AM Richard Hipp wrote: > On 2/28/19, Dominique Devienne wrote: > > On Thu, Feb 28, 2019 at 12:14 AM Richard Hipp wrote: > >> Docs have now been updated. > > > > Hi. I don't see it in https://www.sqlite.org/src/timeline. > &

Re: [sqlite] proposal: make the archive option -Au to update only newer files. /Patch included/

On Wed, Mar 20, 2019 at 5:13 PM Захар Малиновский < zakhar.malinovs...@gmail.com> wrote: > Here I include patch file with the changes: fossil diff --from trunk .\src\ > shell.c.in > patch-updateOnlyNewer.patch > (see patch file patch-updateOnlyNewer.patch) > Copy/paste it inline to your message.

Re: [sqlite] sqlar: makeDirectory called with permission bits of file

On Mon, Mar 25, 2019 at 4:07 PM Richard Hipp wrote: > Please try the latest trunk check-in and let use know whether or not it > fixes your issue. > For reference: https://www.sqlite.org/src/info/f11c89595dc65f89 ___ sqlite-users mailing list sqlite-use

Re: [sqlite] proposal: make the archive option -Au to update only newer files. /Patch included/

On Mon, Mar 25, 2019 at 8:35 AM Захар Малиновский < zakhar.malinovs...@gmail.com> wrote: > I would like to know is there a way to see if this proposal rejected or > something? Is this a write ML to propose with patch? > Looks like Richard implemented what you need? https://www.sqlite.org/src/info

Re: [sqlite] JSON1: queries on object keys

On Tue, Mar 26, 2019 at 3:35 PM Wout Mertens wrote: > 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, '$.b

Re: [sqlite] Row locking sqlite3

On Wed, Mar 27, 2019 at 1:02 PM Simon Slavin wrote: > On 27 Mar 2019, at 11:48am, Thomas Kurz wrote: > Locking by rows is a slow operation. You first have to lock the entire > database, then lock the row, then release the database. And each of those > locks is a test-and-lock operation. Also,

Re: [sqlite] Feature request, sqlite3_stmt_action

On Thu 28 Mar 2019 at 08:07, Olivier Mascia wrote: > > > Le 27 mars 2019 à 18:04, siscia a écrit : > > > > I would like to propose a function (named `sqlite3_stmt_action` for the > sake > > of discussion) that allow to understand if a specific statement is > either a > > SELECT, UPDATE, DELETE o

Re: [sqlite] Feature request, sqlite3_stmt_action

On Thu, Mar 28, 2019 at 10:59 AM R Smith wrote: > Maybe even, if possible, This query updates these tables: x1, x2, x3... > etc. (some of which might hide behind an FK relation or Trigger) but I > know this is pushing my luck. :) > What I ended-up doing is introspecting the VDBE program of the

Re: [sqlite] [EXTERNAL] Re: Feature request, sqlite3_stmt_action

On Thu, Mar 28, 2019 at 1:35 PM Hick Gunter wrote: > IMHO the sqlite3_set_authorizer() interface already does a pretty decent > job of providing the requested information: > True, but only if you are fully in control, because authorizer do not "stack". There's only one, you can't get to restore

Re: [sqlite] Clear sqlite3 terminal enhancement

On Thu, Mar 28, 2019 at 1:16 PM Clemens Ladisch wrote: > Jeffrey Walton wrote: > > When working in the Linux terminal we can clear the scrollback with > > the 'clear' command; and we can delete all history and scrollback with > > the 'reset' command. I am not able to do the same within the sqlite

Re: [sqlite] Is it possible to transpose a table using SQL?

On Mon, Apr 1, 2019 at 7:15 AM Shane Dev wrote: > [...]. By "dynamically changing table", I meant the number of columns and > rows could could change > after the dependant view was created. it appears this is impossible using > only SQL > It's possible using a virtual table, which years ago a co

Re: [sqlite] ANN: SQLite3 Decimal Extension

On Wed, Apr 3, 2019 at 2:31 PM Lifepillar wrote: > [I hope that this kind of announcement is not off-topic here] > Not at all, IMHO. > SQLite3 Decimal is an extension implementing exact decimal arithmetic > for SQLite3. It is currently unfinished and under development. I'm curious, what was

[sqlite] Full Outer Join of 3 or more tables

Up to now, we were doing 2-tables full-outer-join using the classic emulation, since SQLite lacks support for that join. But now we are doing it with 3 tables, and it gets ugly fast IMHO. https://stackoverflow.com/questions/12759087/full-outer-join-in-sqlite-on-4-tables I "think" the reason that

Re: [sqlite] Error in docs

On Mon, Apr 8, 2019 at 7:58 PM Jim Dossey wrote: > I think I found an error in the documentation here: > https://www.sqlite.org/datatype3.html#column_affinity_for_views_and_subqueries > < > https://www.sqlite.org/datatype3.html#column_affinity_for_views_and_subqueries > > > > It defines the table

Re: [sqlite] Option to control implicit casting

On Tue, Apr 9, 2019 at 5:08 AM Joshua Thomas Wise < joshuathomasw...@gmail.com> wrote: > SQLite3 uses manifest typing, which is great and provides a ton of > flexibility. However, due to implicit casting rules, many operations can > accidentally result in a different value than what was desired. I

Re: [sqlite] Error in docs

On Tue, Apr 9, 2019 at 9:41 AM Richard Hipp wrote: > On 4/9/19, Dominique Devienne wrote: > >> > >> It defines the table and view: > >> CREATE TABLE t1(a INT, b TEXT, c REAL); > >> CREATE VIEW v1(x,y,z) AS SELECT b, a+c, 42 FROM t1 WHERE b!=11; > >&g

Re: [sqlite] Help with sqlite3_value_text

On Fri, Apr 12, 2019 at 4:51 PM x wrote: > I’m still confused by utf strings. [... I want to scan the string to > count the number of occurrences of a certain character. [...] > How do I do the same thing if the string param is a utf-8 or utf-16 string > and the SearchChar is a Unicode character

Re: [sqlite] Json paths

On Mon, Apr 15, 2019 at 6:34 AM Charles Leifer wrote: > I was wondering if there were any plans to support wildcard paths? > The main issue here IMHO is that there's no official standard, AFAIK. > Postgres v12 release looks like it has a pretty sophisticated jsonpath > type. SQLite does ofte

Re: [sqlite] Database corruption check.

On Mon, Apr 15, 2019 at 11:37 AM Lullaby Dayal wrote: > [...]. But I fail to run the .selftest command from my sqlite3 prompt. I > got the error: > unknown command or invalid arguments error. > That code dates back to July 2017. So you must have a very old version. > I am a newbie in SQLite. I

Re: [sqlite] Custom collation of blobs

On Fri, Apr 26, 2019 at 7:36 PM Jens Alfke wrote: > We are using SQLite blobs to store some structured values, and need > control over how they are collated in queries, i.e. memcmp is not the > correct ordering. We’ve registered a custom collating function, but > unfortunately it doesn’t get call

Re: [sqlite] Go & SQLite asserts

On Mon, Apr 29, 2019 at 9:49 PM Russ Cox wrote: > On Mon, Apr 29, 2019 at 3:28 PM Richard Hipp wrote: > For what it's worth, it was not clear to me until just now that the article > existed to push back on a general "asserts considered harmful" notion. I > was reading it as primarily documenting

Re: [sqlite] SQLite with single writer on Windows network share

On Fri, May 10, 2019 at 4:40 AM Warren Young wrote: > Bedrock is based in part on the Paxos algorithm, another major advance in > distributed computing, and another of Leslie Lamport’s brainchildren: > > https://en.wikipedia.org/wiki/Paxos_(computer_science) In the same vein, there's also R

Re: [sqlite] CSV import does not handle fields with a comma surrounded by double

On Tue, May 21, 2019 at 5:36 PM Shawn Wagner wrote: > I have a handy script that can handle that sort of input with extra spaces > (With the --strip option), and other stuff that csv .import doesn't always > deal well with: > > https://github.com/shawnw/useful_sqlite_extensions/tree/master/tools

Re: [sqlite] SQL Features That SQLite Does Not Implement

On Thu, May 23, 2019 at 7:39 AM Keith Medcalf wrote: > You can check if what you need is available on a connection and either > load it if needed or just abort: > > sqlite> select * from pragma_function_list order by 1, 2; > name builtin > -

  1   2   3   4   5   6   7   8   >