Re: [sqlite] CTE question...

a >quick listing of the unique values in the code_key field, alongside >of a >proper row number. (The native result of rowid from the table is not >useful, since they are typically hundreds of rows apart.) > >Sample values: > >code_key >20170914-1200 >20170914-1822 >

[sqlite] sqlite CLI scripts using ".echo on" with ".once" or ".output"

I imagine this is expected behavior but wanted to be sure. In an sqlite commandline utility (CLI) script, if one enables .echo on for debugging, and redirects output either by .once filename or .output filename then the sql commands themselves are included in the output file. I've taken

Re: [sqlite] CTE question...

purpose is produce a > quick listing of the unique values in the code_key field, alongside of a > proper row number. (The native result of rowid from the table is not > useful, since they are typically hundreds of rows apart.) > > Sample values: > > code_key > 20170914-1200 > 20170

[sqlite] CTE question...

values in the code_key field, alongside of a proper row number. (The native result of rowid from the table is not useful, since they are typically hundreds of rows apart.) Sample values: code_key 20170914-1200 20170914-1822 20170915-0855 20170915-1718 I can get the recursion to work properly

Re: [sqlite] 'database disk image is malformed' only on the mac

On 15 Sep 2017, at 4:38am, Fahad wrote: > How can I prevent this from happening? Like I said, using a unit test I was > able to reproduce this 1 out of 20 tries but when using the real app and > plugin at the same time, I am able to reproduce every 5th try (it seems the > app

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

On Thursday, 14 September, 2017 19:05, Jens Alfke , wrote: >> On Sep 14, 2017, at 1:23 PM, Keith Medcalf >wrote: >> You merely need to ONCE it either for each input row or for each >result row. So for example: >> select slow(a.x), slow(a.x)*slow(b.y),

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

Nico Williams wrote: > I would much prefer to be able to specify which CTEs must be materialized, > and which may be left as internal views. That would give the user a great > deal of control. WITH x AS () MATERIALIZED ... . "Materialized" is the wrong word; you want to prevent only subquery

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

On Fri Sep 15, 2017 at 09:55:40AM +0200, Dominique Devienne wrote: > On Thu, Sep 14, 2017 at 11:43 PM, Nico Williams > wrote: > > > [...] I would much prefer to be able to specify which CTEs must be > > materialized, > > and which may be left as internal views. That would

Re: [sqlite] Figuring out the Cause of SQLite Error 11: Corrupt Database

On 9/14/17, Esplin, Justin wrote: > > We are seeing a couple worrisome (and not very informative) messages > repeatedly in our logs: > > SQLite error (11): database corruption at line 55472 of > [cf538e2783] > > SQLite error (11): database corruption at line 55514

Re: [sqlite] 'database disk image is malformed' only on the mac

Hi Simon I tried it with sqlite3_shutdown(), didn't help. I was able to reproduce it again using the main app and the safari share plugin. Main App: Finished writing to db, just opened a new connection to read (after opening a connection I set PRAGMA query_only=1; PRAGMA read_uncommitted=1; and

[sqlite] Figuring out the Cause of SQLite Error 11: Corrupt Database

Howdy, We are seeing a couple worrisome (and not very informative) messages repeatedly in our logs: SQLite error (11): database corruption at line 55472 of [cf538e2783] SQLite error (11): database corruption at line 55514 of [cf538e2783] I have been looking around the web for a

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

On Thu, Sep 14, 2017 at 11:43 PM, Nico Williams wrote: > On Thu, Sep 14, 2017 at 1:10 PM Simon Slavin wrote: > > Can you not do it with WITH ? I don’t really understand how WITH works > > but it would seem to evaluate its terms just once for each

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] [EXTERNAL] Re: Table-valued functions as PIVOT

Should not the result (for a simple pivot) be more like Field | Alice | Bob | Eve - age | 42| 27 | 16 with one row for each column of the original table? Or maybe even: Using pivot (,,); -Ursprüngliche Nachricht- Von: sqlite-users