[sqlite] SQLite 3.12.2 SCALARS NOT VISIBLE TO CORRELATED SUBQUERY LIMIT CLAUSE

Pasted from comments in one my database project files: - --SQLite 3.12.2 SCALARS NOT VISIBLE TO CORRELATED SUBQUERY LIMIT CLAUSE. --Observe that "s.off" is a scalar constant with respect to subquery "v" 's scope in the following example. with data(v) as (values ('A'),('B'),('C')),

Re: [sqlite] Unexpected 'no such column' with expression in subquery ORDER BY

Keith. You are correct. The online help is the only design document. Furthermore, from my observations about this forum, the intrepid Dr. Hipp will often never weigh in to disclose the full design or thought process of the intended product behavior. You can put the arbitrary outer column

Re: [sqlite] table-naming-expression impact on sqlite3_prepare

t 10 lines changed including the #ifndef TCL_DB_SHELL statements. On Sun, Mar 26, 2017 at 10:36 AM, E.Pasma <pasm...@concepts.nl> wrote: > 26-03-2017 petern : > > > The table-naming-expression, if > > normal expressions are allowed, would obviously

Re: [sqlite] Syntax. table-function-name vs table-naming-function-name

, Richard Hipp <d...@sqlite.org> wrote: > On 3/25/17, petern <peter.nichvolo...@gmail.com> wrote: > > > > Why can't we have a parallel syntax branch for scalar valued > > "table-naming-function-name"? In other words, why not have support for > > simply

Re: [sqlite] How does one block a reader connection?

, 2017 23:44. petern <peter.nichvolo...@gmail.com> > wrote: > > > Can anybody explain the purpose of > > http://sqlite.org/c3ref/busy_handler.html > > ? It seems the only practical use would be to allow the caller to give > > the engine a suggested

[sqlite] table-naming-expression impact on sqlite3_prepare

My thanks to everyone who responded to my read blocking transaction isolation question. Further to my other question/proposal with no responses, what would be the impact on sqlite3_prepare to introduce a new branch called table-naming-expression into the syntax graph at:

[sqlite] Syntax. table-function-name vs table-naming-function-name

Consider the "table-or-subquery" syntax chart linked below. https://www.sqlite.org/syntax/table-or-subquery.html A modest proposal. In the "table-or-subquery" syntax there exists a branch for "table-function-name", aka table valued virtual tables. Why can't we have a parallel syntax branch for

Re: [sqlite] How does one block a reader connection?

ter transaction finally inserts some new commands and closes the transaction for the readers to see the updated command table. On Sat, Mar 25, 2017 at 4:16 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 25 Mar 2017, at 10:52pm, petern <peter.nichvolo...@gmail.com> wrot

[sqlite] How does one block a reader connection?

I would like to construct a SQLite database for one writer and one or more reader connections. The writer will be updating various data tables which occasionally trigger one or more row inserts in a command table named 'cmd'. This command table is being polled by the readers for new commands.

Re: [sqlite] How does one block a reader connection?

committed> is off by default" On Sat, Mar 25, 2017 at 7:56 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 26 Mar 2017, at 2:35am, petern <peter.nichvolo...@gmail.com> wrote: > > > Is there something that can be done by the writer, like holding a BEG

[sqlite] tclsqlite3 db function exports not visible on other connections. Why not?

Taking DRH's remarks about learning tclsqlite for the efficient coding to heart, I discovered a big problem. Here is the simplest example from the docs and DRH presentation: TCLSH % db function myhex {format 0x%X}; % db eval {select myhex(10);} x {parray x}; x(*) = myhex(10) x(myhex(10))

Re: [sqlite] tclsqlite3 db function exports not visible on other connections. Why not?

.tcl files and current tclsqlite3.c source file? On Sun, Mar 19, 2017 at 4:44 AM, Daniel Kamil Kozar <dkk...@gmail.com> wrote: > You can use sqlite3_auto_extension for this. > > On 19 March 2017 at 11:35, R Smith <rsm...@rsweb.co.za> wrote: > > > > On

Re: [sqlite] tclsqlite3 db function exports not visible on other connections. Why not?

; On 3/19/17, petern <peter.nichvolo...@gmail.com> wrote: > > > > In fact, according to the title of that presentation, SQLite is the most > > popular TCL extension in the world! > > > > Furthermore, if the TCL byte code engine is already linked to the SQLite >

Re: [sqlite] If I got many to many relationship data first, how do I insert them to my table?

Qiulang. I am curious about this requirement. Is there an example commerce site in the real world where having a one to one match in a master customer login table to all real customers is vital to the mission? What sort of business would have that? Even banks do not have such surety about

Re: [sqlite] Is it possible to create the Stored Procedure (SP) in Sqlite?

My sense from these replies is that nobody bothers to try using triggers to store their SQLite procedural code within the DB. I was skeptical when I first learned of the technique but the trigger syntax is very computationally permissive. Frankly, I'm still surprised by what one is allowed to do

Re: [sqlite] Is it possible to create the Stored Procedure (SP) in Sqlite?

Yes, please include it in the FAQ along with a description of the SQLite stored procedure pattern syntax which is never disclosed in these replies: CREATE TRIGGER my_sproc INSTEAD OF INSERT on my_sproc_caller_view BEGIN --My procedural code to be prepared and stored in the database. END;

Re: [sqlite] Is it possible to create the Stored Procedure (SP) in Sqlite?

1. Forgot to mention. In cases where the WHEN clause is not convenient for trigger style stored procedure condition branching there is also "SELECT raise(ignore) WHERE ": https://sqlite.org/lang_createtrigger.html#raise 2. Here is a question. It would be helpful to know if TRIGGERs are stored

Re: [sqlite] sqlite3 feature or regression

tclsh, aside from the inconvenience of prefixing every sql statement with "db eval {", looks like a great way to gain the equivalent functionality of scalar output stored procedures compared to the plain vanilla sqlite shell. Is there anything in the works for the tcl bindings to define/export

Re: [sqlite] SQLite Recursive Common Table Expression suggestion

<ddevie...@gmail.com> wrote: > On Wed, Mar 8, 2017 at 3:47 AM, petern <peter.nichvolo...@gmail.com> > wrote: > > > Things have changed somewhat since I wrote those comments. After the > > introduction of row values in 3.15 https://www.sqlite.org/rowvalue.html &

Re: [sqlite] SQLite Recursive Common Table Expression suggestion

Further to sqlite pivot function, matrix functions, or any other result set meta query language feature, I commented about this before with a concrete suggestion. The core problem is the awkward complexity of building a completely general virtual table (vtab) based eval("") or meta("") which

Re: [sqlite] Bug: Nested function call to replace() throws parser stack overflow exception

The deeply nested string replace pattern comes up frequently. A suggestion. Why not a built in aggregate replace function? As such, the example here would be more readable and not limited by stack depth as the expression: SELECT replace(Transcript,column1,column2) FROM (VALUES

Re: [sqlite] sequencer

Neat. For production, you might want to check the type on function arguments before using them. Is it working fairly fast on inserts? That is, I presume, if this is intended for bypassing restrictions on the DEFAULT clause (expr) of column-constraint in a CREATE TABLE statement:

Re: [sqlite] sequencer

Sylvain, are you happy with the performance? Maybe you are using it differently but, from my tests, the DEFAULT clause is ignored for PRIMARY KEY columns. I had to use an ordinary column with UNIQUE constraint to test your extension. Below is a tester for 1 million rows which completes in

Re: [sqlite] group_concat() reverses order given where clause?

quote from https://sqlite.org/lang_aggfunc.html The group_concat() function returns a string which is the concatenation of all non-NULL values of X. If parameter Y is present then it is used as the separator between instances of X. A comma (",") is used as the separator if Y is omitted. The

[sqlite] Is option -interactive (force interactive i/o) working correctly?

This test case is peformed using two bash terminal sessions. (1) At the first terminal: $ #Interactively run sqlite3 from named pipe. $ mkfifo /tmp/slsh_in $ ./sqlite3 -interactive /tmp/slsh_in $ Results at first terminal after echo line is sent from second terminal: $ #Interactively run

Re: [sqlite] Is option -interactive (force interactive i/o) working correctly?

.@ladisch.de> wrote: > petern wrote: > > $ echo "SELECT ('Shouldn''t SQLite shell continue interactively after > > processing this statement?')msg;" >/tmp/slsh_in > > $ > > > > Results at first terminal after echo line is sent from second terminal: >

Re: [sqlite] Disable trigger?

If runtime disablement of one or more whole triggers is desired, their execution can be gated by adding a conditioned WHEN clause (or logical conjuction with existing WHEN clause) to the offending trigger declaration. https://sqlite.org/syntax/create-trigger-stmt.html For example: CREATE

Re: [sqlite] Draft docs typo

Speaking of type string lifetime, what about pointer lifetime management? I think you've overlooked the pointer lifetime problem for sqlite3_result_pointer(C,P,T). This form, unlike the blob form, lacks the destructor callback for cleanup when SQLite determines the scope of the last statement

[sqlite] VALUES clause quirk or bug?

Why does the choice of data value quotation mark influence the output column name of the inline VALUES clause? [This quirk was the origin of a recent bug in a current project.] sqlite> .version SQLite 3.19.3 2017-06-08 14:26:16 0ee482a1e0eae22e08edc8978c9733a96603d4509645f348ebf55b579e89636b

Re: [sqlite] VALUES clause quirk or bug?

Thanks Ryan. Thank you very much for the detailed analysis on how the column names are arrived at. Presumably the column names "",":1",":2",... will be stable in future. I use them frequently in the following pattern. SELECT custom_aggregate("",":1") FROM (VALUES (1,2),(3,4)); Or, with

Re: [sqlite] VALUES clause quirk or bug?

ear "AS": syntax error The desired output in both cases would of course be: c1,c2 1,2 3,4 On Sat, Jul 8, 2017 at 2:20 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > > On 8 Jul 2017, at 8:36pm, petern <peter.nichvolo...@gmail.com> wrote: > > > W

Re: [sqlite] VALUES clause quirk or bug?

A lot of replies. To be clear, this is not a quirk of the shell and I'm not just monkeying around looking for haphazard guesses about why I'm "trying to change the SQL standard". The following query sent through sqlite3_exec() produces the following output: SELECT max([],[:1]) FROM (VALUES

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

Regarding the new pointer value/result pseudo-null API: void *sqlite3_value_pointer(sqlite3_value*); void sqlite3_result_pointer(sqlite3_context*, void*); Assuming sqlite3_value_type() returns SQLITE_NULL for these pseudo-nulls, and thinking ahead, an additional pointer type API pair would be

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

Missing documentation or wrong extension source? https://www.sqlite.org/src/file/ext/misc/remember.c line 51: -> ptr = sqlite3_value_pointer(argv[1], "carray"); This signature, 'void* sqlite3_value_pointer(V,T)' , is not documented here: https://sqlite.org/draft/c3ref/value_blob.html Nor is

Re: [sqlite] PRAGMA table_info and not nullable rowid alias

That's interesting. Apparentely PRAGMA table_info() reports the declared column type not the operational column type. sqlite> CREATE TABLE test (id INTEGER PRIMARY KEY NOT NULL); Run Time: real 0.454 user 0.00 sys 0.00 sqlite> PRAGMA table_info(test); cid,name,type,notnull,dflt_value,pk

Re: [sqlite] SQLite 3.20.0 postponed

Yes. Good point. Before seeing Richard's last commits for pseudo-null pointer passing with static string type name, I proposed that the pseudo-null pointer API be extended with function signature forms which pass by value a user defined integer field for type information. After further

Re: [sqlite] SQLite 3.20.0 postponed

Indeed. Or Type(T) of long, or sqlite_int64, or whatever is most universal where porting other architectures is concerned. It would be a shame if the API had to be changed later because the integer type chosen couldn't hold a pointer. From there, in a closed compiled host C/C++ setting with the

Re: [sqlite] SQLite 3.20.0 postponed

the purpose wrong, then I really don't understand why there > needs to be a separate value type for pointers at all ... > > > >-Original Message- > >From: sqlite-users [mailto:sqlite-users- > >boun...@mailinglists.sqlite.org] On Behalf Of petern > >Sent: Friday

Re: [sqlite] New draft document on the new pointer-passing interfaces

! So please, BEFORE RESPONDING TO THIS POST, DO READ THE PROPOSAL WHERE THIS IS CLEARLY STATED! On Tue, Jul 25, 2017 at 6:11 AM, Peter Da Silva < peter.dasi...@flightaware.com> wrote: > On 7/24/17, 7:20 PM, "sqlite-users on behalf of petern" < > sqlite-users-boun...@mailingl

Re: [sqlite] New draft document on the new pointer-passing interfaces

AM, Gwendal Roué <gwendal.r...@gmail.com> wrote: > > > Le 24 juil. 2017 à 19:02, petern <peter.nichvolo...@gmail.com> a écrit : > > > > Great. But, if this is an ultimate replacement for BLOB'ed pointers, > these > > new pseudo-null pointers must suppo

Re: [sqlite] Doc bug. sqlite3_value_subtype() sqlite3_result_subtype() missing from obtaining/setting pages

rd isn't updating the > file format just to spite me for rudely arguing the need for subtypes a > couple of years ago. That or he just values the stability of the file > format. > > > > On Jul 24, 2017, at 2:40 AM, petern <peter.nichvolo...@gmail.com>

Re: [sqlite] New draft document on the new pointer-passing interfaces

access protocol with subtypes. In fact, subtypes could afford greater security at runtime if the programmer rotates or otherwise randomizes the type id's. On Mon, Jul 24, 2017 at 10:05 AM, Richard Hipp <d...@sqlite.org> wrote: > On 7/24/17, petern <peter.nichvolo...@gmai

[sqlite] Doc bug. sqlite3_value_subtype() sqlite3_result_subtype() missing from obtaining/setting pages

1. Why are these functions missing from the obtaining/setting pages? Are they not recommended for some reason? I'd like to know if they are on the way out because, obviously, these solve the BLOB typing problem for functions directly and I'm now using them.

Re: [sqlite] New draft document on the new pointer-passing interfaces

Great. But, if this is an ultimate replacement for BLOB'ed pointers, these new pseudo-null pointers must support SQLITE_STATIC and destructor function pointer lifetime disposition for those migrating their code. Why can't the producer destructor disposition be preserved within a chain of

Re: [sqlite] New draft document on the new pointer-passing interfaces

. :-) On Mon, Jul 24, 2017 at 1:52 PM, Peter Da Silva < peter.dasi...@flightaware.com> wrote: > On 7/24/17, 3:50 PM, "sqlite-users on behalf of petern" < > sqlite-users-boun...@mailinglists.sqlite.org on behalf of > peter.nichvolo...@gmail.com> wrote: > > BTW, if

Re: [sqlite] New draft document on the new pointer-passing interfaces

where strcmp() loads a register to one of the pointer "keys" you've insisted be conveniently published for hackers in the data segment? On Tue, Jul 25, 2017 at 10:43 AM, Richard Hipp <d...@sqlite.org> wrote: > On 7/24/17, petern <peter.nichvolo...@gmail

Re: [sqlite] SQLite 3.20.0 postponed

Richard. Here is food for thought. Below is a contrived but realistic example of an embedded application wire format which demonstrates some expected extension function pointer allocations and another recent topic. SELECT DoTrackingRecalibrate(column1,CommandPacket(column2,column3)) FROM

Re: [sqlite] Trigger firing order

> a programming pattern that is missing in SQLite trigger body: "if (new.C1 <> old.C1) then ". It can be simulated for updates ("update ... where ... and new.C1 <> old.C1") but not for inserts ("insert" has no "where" clause). Maybe so. But, INSERT can accept data rows from a SELECT statement

[sqlite] Documentation bug. Detecting NULL extension arguments.

Is this the recommended way for any argument type? zCol = (const char*)sqlite3_value_text(argv[i]); if( 0==zCol ) return; Docs say protected_sqlite3_value can represent NULL but how is not explained anywhere. https://www.sqlite.org/c3ref/value_blob.html

Re: [sqlite] Documentation bug. Detecting NULL extension arguments.

sqlite3_value_type() definitely deserves at least one document sentence within https://sqlite.org/c3ref/value_blob.html containing the link to https://sqlite.org/c3ref/c_blob.html On Fri, Jun 30, 2017 at 10:26 AM, Richard Hipp <d...@sqlite.org> wrote: > On 6/30/17, petern <pe

Re: [sqlite] Problem with the new pointer-passing interface

Dominique. strcmp alone won't care if zPType is code segment or data segment. As is, this release won't need the dynamic linker to perfectly consolidate constants. Much ado. [You are also free to clone three very tiny API functions substituting integer/etc PType if you're in the mood to ship

Re: [sqlite] New draft document on the new pointer-passing interfaces

ironic how the new pseudo-null API leans even more heavily on a waning shield of address space randomization than the subtype API did. On Thu, Jul 27, 2017 at 11:41 AM, Jens Alfke <j...@mooseyard.com> wrote: > > > On Jul 27, 2017, at 10:02 AM, petern <peter.nichvolo...@gmail.c

Re: [sqlite] New draft document on the new pointer-passing interfaces

>...the attacker is already able to read the process’s address space. The rest of us here are saying that, in that case, as far as we’re concerned the attacker has already won. I understand that. What I'm saying is your standard is not nuanced. Applying a security standard that amounts to best

Re: [sqlite] Bug: Nested function call to replace() throws parser stack overflow exception

Hey Brian. I had some time this morning to look at it. Try whacking this new concat_replace into your SQLite build in either func.c or sqlite3.c amalgam. Also remember to hook it up with a new entry after say, group_concat, as follows. AGGREGATE(group_replace, 3, 0, 0,

Re: [sqlite] sequencer

, Sylvain Pointeau <sylvain.point...@gmail.com > wrote: > On Fri, Aug 4, 2017 at 9:21 AM, Sylvain Pointeau < > sylvain.point...@gmail.com > > wrote: > > > On Fri, Aug 4, 2017 at 7:41 AM, petern <peter.nichvolo...@gmail.com> > > wrote: > > > >> Syl

[sqlite] Generalized SQLite stored procedure style pivot table exhibit.

Here I revisit the pivot table problem using the SQLite stored procedure pattern and pure unmodified SQLite. I am seeking feedback for improving the brevity of the stored procedure presented here. This time around I went with generating pivot views instead of pivot tables since that was the more

Re: [sqlite] need help to implement a VTAB on a already sorted table

Richard. Further to your reply on Mar 31 2017, were those newly worked code exemplars demonstrating non-trivial sqlite3_index_info from xBestIndex interaction for a SQLite table backed vtable published? I am sure there is still great interest for this information considering the lack of even one

Re: [sqlite] The cli truncates values containing null chars

Some output modes of shell.c already automatically hex print BLOB's: static void output_hex_blob(FILE *out, const void *pBlob, int nBlob){ int i; char *zBlob = (char *)pBlob; raw_printf(out,"X'"); for(i=0; i

Re: [sqlite] SQLite in memory

From the SQLite shell (CLI), have you tried dot commands ".backup" to file and ".restore" to a new :memory: DB? That assumes a few things like access to the filesystem and sufficient user memory quota to hold the disk version of the DB. Does that work? The shell dot commands and their syntax is

Re: [sqlite] Generalized SQLite stored procedure style pivot table exhibit.

ERT INTO generate_pivot1_view VALUES('quarterly_sales','year','fruit','tons','avg'); END; On Tue, May 16, 2017 at 4:32 PM, E.Pasma <pasm...@concepts.nl> wrote: > 15 mei 2017, 07:34 petern: > > Here I revisit the pivot table problem using the SQLite stored procedure >> pattern and pure unmodifi

[sqlite] Is sqlite3_auto_extension() same compilation unit ruled out?

I have a situation where it would be convenient to locate externally loadable SQLite extension code in the same compilation unit as the server code. Is there a way for server main() to load those extensions located within its own compilation unit? Does the necessity of #including both sqlite3.h

Re: [sqlite] sqlite3_exec statement count including create/drop?

only negative SQLITE_OMIT_TRACE: https://sqlite.org/compile.html I don't see anything called TRACE in bld/Makefile. On Mon, Jun 19, 2017 at 1:05 AM, Clemens Ladisch <clem...@ladisch.de> wrote: > petern wrote: > > Is there a C API way to get a total count or notification as each

Re: [sqlite] sqlite3_exec statement count including create/drop?

, Jun 19, 2017 at 1:30 PM, Clemens Ladisch <clem...@ladisch.de> wrote: > petern wrote: > > linker says -> "undefined reference to `sqlite3_trace_v2'" > > Then your distribution's package appears to be compiled with > SQLITE_OMIT_TRACE. > > Just a

Re: [sqlite] vtable and usable constraint

Gunter. Thank you very much for the insightful and detailed answer. May I ask the obvious and possibly oversimplified question? This is directed to anyone especially those privy to the worked VTab examples DRH mentioned. Suppose the virtual table is merely a light wrapper over backing_table.

Re: [sqlite] Generalized SQLite stored procedure style pivot table exhibit.

iew synchronizing trigger could be more precisely applied on a normalized fruit_type table.] On Tue, May 16, 2017 at 4:32 PM, E.Pasma <pasm...@concepts.nl> wrote: > 15 mei 2017, 07:34 petern: > > Here I revisit the pivot table problem using the SQLite stored procedure &

[sqlite] 3.19.3 README.md Doc bug

Was there a version in the past where the compile instructions made sense? tar xzf sqlite.tar.gz;# Unpack the source tree into "sqlite" mkdir bld;# Build will occur in a sibling directory cd bld ;# Change to the build directory

Re: [sqlite] 3.19.3 README.md Doc bug

14, 2017 at 10:40 AM, petern <peter.nichvolo...@gmail.com> > wrote: > > > Was there a version in the past where the compile instructions made > sense? > > > > tar xzf sqlite.tar.gz;# Unpack the source tree into "sqlite" > >

[sqlite] sqlite3_exec statement count including create/drop?

Is there a C API way to get a total count or notification as each statement is prepared by sqlite_prepare_v2 within sqlite3_exec? The sqlite3_exec callback interface is silent with respect to CREATE and DROP statements. Presumably I could copy and paste the entire body of sqlite3_exec into my

Re: [sqlite] Is sqlite3_auto_extension() same compilation unit ruled out?

(p->db, "writefile", 2, SQLITE_UTF8, 0, writefileFunc, 0, 0); } } --- On Mon, Jun 12, 2017 at 5:57 PM, David Burgess <dburges...@gmail.com> wrote: > Have a look at the way readfile() and writefile() is implemented in the > sqlite interpret

Re: [sqlite] Thread notification for new record in a table.

FYI. I proposed a portable solution for a responsive interprocess work queue within SQLite without using native IPC less than 2 weeks ago on this very board. [See Dori the forgetful fish.] https://www.mail-archive.com/sqlite-users@mailinglists. sqlite.org/msg102741.html DB reader(s) block/poll

Re: [sqlite] Proposition: introduce a new SQLITE_READ_TABLE Authorizer Action Code

Gwendal. I understand all that. It's also good that you've confirmed how SQLITE_READ is actually queried by the authorizer callback interface. I was wondering about that. Reading your earlier post, one might get the impression that the SQLITE_READ authorizer action was not queried by the

Re: [sqlite] Proposition: introduce a new SQLITE_READ_TABLE Authorizer Action Code

Gwendal. Your proposal last month for adding column names to the callback parameters seemed more sensible. The first question that comes to mind when new callback modes are to being proposed is what else would be missing if the same standard were applied to every possible operation? My thought.

Re: [sqlite] Table-valued functions as PIVOT

Richard is right. Unless you're discarding transformed rowsets, your pivots will become stale. If you need a live pivot, I recently disclosed a SQLite native language "stored procedure" for computing live pivot views on this mailing list:

Re: [sqlite] CTE question...

Try running your subquery by itself and see if the first row's code_key is what you want for every column of your recursive outer query: SELECT DISTINCT code_key FROM _misc_log WHERE code_key != (SELECT

[sqlite] Why does eval.c lack a row separator parameter?

Is there a practical reason why eval.c was designed with only two parameters? https://www.sqlite.org/src/artifact/f971962e92ebb8b0 Why eval(X,Y) instead of eval(X,Y,Z)? The second form with both an optional column separator Y and an optional row separator Z is far more useful. I develop an

[sqlite] Error: cannot create trigger on system table

What are the drawbacks to allowing triggers on system tables? Is this an arbitrary restriction? In the "big picture" overview, what would it take to get system table triggers working after bypassing the error check in trigger.c below? /* Do not create a trigger on a system table */ if(

Re: [sqlite] Error: cannot create trigger on system table

extension released with SQLite? I'd be happy to provide my changes. Let me know if you want them. On Wed, Sep 20, 2017 at 3:20 AM, Richard Hipp <d...@sqlite.org> wrote: > On 9/20/17, petern <peter.nichvolo...@gmail.com> wrote: > > What are the drawbacks to allowing trig

Re: [sqlite] Error: cannot create trigger on system table

many live integration uses. On Wed, Sep 20, 2017 at 11:25 AM, Simon Slavin <slav...@bigfraud.org> wrote: > > > On 20 Sep 2017, at 6:55pm, petern <peter.nichvolo...@gmail.com> wrote: > > > OK. If system table triggers are generally not maintained, what is > >

Re: [sqlite] Support for named foreign keys?

Definitely what Clemens said: https://www.sqlite.org/syntax/table-constraint.html But you probably want to see all the FK's, not merely named ones. I suggest parsing for the 'REFERENCES' keyword of the table schemas stored in sqlite_master (or .schema report of the shell tool). You can fix the

Re: [sqlite] Ordering a GROUP BY, or other concatenating tricks?

You could also write it even more clearly as: WITH IndexedLines AS (SELECT LineText FROM DocLines WHERE DocID = 10 ORDER BY LineIndex) SELECT group_concat(LineText, char(10)) FROM IndexedLines; That code will actually work. As it is not C, SQLite will not recognize the '\n' C escaped line feed

Re: [sqlite] Simple SQL question?

It is often helpful to study the syntax diagrams to see what is possible and intended by the language: https://sqlite.org/lang_update.html Take a look at the WHERE clause. The WHERE clause determines which rows are UPDATEd. One weakness in the documentation (although it may otherwise generally

Re: [sqlite] sqlite3_get_auxdata() defect

e same value? Take another look at the example. On Mon, Nov 27, 2017 at 1:15 AM, Clemens Ladisch <clem...@ladisch.de> wrote: > petern wrote: > > So, at the very least, the documentation at > > https://sqlite.org/c3ref/get_auxdata.html is woefully incomplete > because: >

Re: [sqlite] sqlite3_get_auxdata() defect

auxint, 0, 0); if (SQLITE_OK == rc) sqlite3_create_function(db, "auxint", 2, SQLITE_UTF8, 0, auxint, 0, 0); return rc; } -- On Mon, Nov 27, 2017 at 1:15 AM, Clemens Ladisch <clem...@ladisch.de> wrote: > petern wrote: > > So, at the very least, the documentation at > > https:

Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

Hey Peter. Good on you. Lobbying for sensible fixes to the public API does pay off sometimes. There's a new branch in the timeline. [Watch for a merge here: https://www.sqlite.org/src/timeline?n=50 ] https://www.sqlite.org/src/info/2494132a2b1221a4 ** PRAGMA table_ipk() ** ** If has

Re: [sqlite] How to use sqlite3_table_column_metadata?

I pasted that SQL into a SQLite shell and tried it out. First off, Affinity (aka pragma table_info.type) column is case collated, so the LIKE operator should be used: select O.* from SysColumns as O where ObjectType == 'table' and IsPrimaryKey == 1 and Affinity LIKE 'INTEGER' and

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

Well, first you imagine you're back at the office in Santa Clara or Redmond in the early 1990's. Then take a belt of whisky, cross your eyes, and paste from doc to clipboard a few edits and voila: #include "sqlite3ext.h" #include SQLITE_EXTENSION_INIT1 static struct metadata { char const

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

Apparently you would query pragma_table_info for "INTEGER" PK columns and then ask through a trivial extension function about the other column meta-data: https://www.sqlite.org/c3ref/table_column_metadata.html The basic plot is illustrated below: sqlite> .load column-meta-data.so sqlite> SELECT

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

Shane. Below is a simple benchmark you can play with to decide if that trigger is fast enough for your application. On the time scale of human thinking and reaction time, I've found SQLite code quite responsive and magnitudes easier to maintain than the equivalent application code. FYI, that

Re: [sqlite] Can I recursively concatenate strings?

Shane. printf() will pad spaces you can replace with 'x' or whatever. WITH lengths(id,l) AS (VALUES (1,4),(2,1),(3,9)) SELECT id,l,replace(printf('%'||l||'s'),' ','x')mask FROM lengths; id,l,mask 1,4, 2,1,x 3,9,x If printf() weren't available, it would be worth the effort to add

Re: [sqlite] Callback for sqlite3_finalize()

Ben. Your post was in spam. Is your extension function an aggregate? From https://www.sqlite.org/c3ref/get_auxdata.html "These functions may be used by (non-aggregate) SQL functions..." The trace hook API has some statement level modes you might hook for to manage your cached data:

Re: [sqlite] sqlite3_get_auxdata() defect

limitations would only add one or two sentences but would save people a lot of time. Peter On Mon, Nov 27, 2017 at 1:27 PM, Clemens Ladisch <clem...@ladisch.de> wrote: > petern wrote: > > Evidently the sqlite3_get_auxdata() API is very buggy. > > The documentation say

[sqlite] sqlite3_get_auxdata() defect

Consider the simplest possible pair of argument metadata test functions as follows. -- /* Further to https://sqlite.org/c3ref/get_auxdata.html UDF auxint(X,I) - stores I plus the function argument X's current metadata INT and returns the new value. UDF auxint(X) - returns the current value

Re: [sqlite] How to use sqlite3_table_column_metadata?

Smissaert <bart.smissa...@gmail.com > > > wrote: > > > > My advise to the VB guy would be to load the extension instead of > trying > > to > > get VB to marshal pointers from the C API. > > > > ?? I do load the extension although it is compiled std_

Re: [sqlite] Using computed column once in SELECT

CTE's can give you the dose of syntactic sugar you're craving: [No, you can reference other column definitions within the same SELECT statement.] WITH w_age_col AS (SELECT *,CAST((julianday('now')-julianday(dob))/365.25 AS INT)age FROM the_table) SELECT *, age,87-age life_expectancy FROM

Re: [sqlite] Odd question

Re: Nelson "odd". This will make the desired (?) side effect happen: .load eval.so SELECT coalesce(eval('INSERT INTO table1 VALUES(a, b, c)'), 1) AS value; If INSERT references columns from an outer scope then use printf() inside the eval(). From a program, use

Re: [sqlite] API, sqlite3_value_bytes and sqlite3_value_blob...

Dave. The documentation contains many such catch-all statements which do not reflect a full decision tree. The usual cover story will either be (I paraphrase) : 1. "that's an implementation detail" or 2. "it might change later, so the documentation can only make a short blanket statement". It is

[sqlite] SQLite running sum side effect

If you want a controlled side effect, like a running sum, add your own stateful extension functions. Example: static double g_sum; static void FloatSumReset(sqlite3_context *context, int argc, sqlite3_value **argv) { g_sum = sqlite3_value_double(argv[0]); } static void

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

Shane. If you're dead set on paying the cost for brute force mid table id insertion, take a look at INSTEAD OF triggers: https://sqlite.org/lang_createtrigger.html Your example would look like this: CREATE VIEW fruit_ins AS SELECT * FROM fruit; CREATE TRIGGER fruit_ins INSTEAD OF INSERT ON

Re: [sqlite] Energy consumption of SQLite queries

Energy measurement can be carried out with a recording electric power meter provided the power otherwise consumed by the operating system and other programs can be controlled for. Total energy used by the computer during one trial is computed by the integral of the recorded power reading over

Re: [sqlite] Retrieving constraint name

Igor/Cezary, It is remarkable how 'struct Parse' already contains the constraint name as Cezary pointed out. -> Token constraintName;/* Name of the constraint currently being parsed */ But is not included in the 'struct FKey' linked list node that is reeled in to produce columns in the PRAGMA

  1   2   >