[sqlite] Fwd: [sqlite-forum] Convert datetime string to second since Epoch with millisecond precision

2020-03-17 Thread Dominique Devienne
Reposting to the ML, maybe I'll have more luck there, than in the forum?
--DD

-- Forwarded message -
From: ddevienne 
Date: Tue, Mar 17, 2020 at 5:09 PM
Subject: [sqlite-forum] Convert datetime string to second since Epoch with
millisecond precision
To: 


Forum post by ddevienne on 2020-03-17 16:09:17
https://sqlite.org/forum/forumpost/0d9c338ff1

Hi. Below does what I want, but surely there's a better way than parsing
the datetime 3 times? Because the below is ugly as hell. Thanks, --DD

```
C:\Users\ddevienne>sqlite3
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select
   ...>cast(strftime('%s', '2016-06-13T09:36:34.123Z') as real) +
   ...> strftime('%f', '2016-06-13T09:36:34.123Z') -
   ...>cast(strftime('%S', '2016-06-13T09:36:34.123Z') as real)
   ...> ;
1465810594.123
sqlite>
```
-- 
Subscription info:
https://sqlite.org/forum/alerts/54F6DD420B31FA7F9F69F5498F1631F5E6D4B48CF97539FDEEF90F71733E90A9
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] SQLite forum posts are about the forum, not SQLite

2020-03-13 Thread Dominique Devienne
I hope that's only a temporary situation... --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

2020-03-13 Thread Dominique Devienne
On Fri, Mar 13, 2020 at 1:05 AM Keith Medcalf  wrote:

> Uck.  That is the most horrible looking thing I have ever seen in my
> life.  Good luck with it.
>

I truly hope Keith you'll continue making your tremendous contributions to
the SQLite community.

Things will inevitably move over to the Forum I'm afraid, but I'd
rather you registered to the forum
(one time setup, with email notifications) and replied on the (deprecated)
ML, rather than having
none your usual insightful answers because you decided to stay exclusively
on the ML (or worse,
if you completely gave up on both). FWIW. --DD

PS: I kinda recall the prospect of switching to a forum wasn't exactly well
received last time it
  was discussed on this ML, and especially so from "heavyweights" of this
ML, it's thus disappointing
  Richard forced that switch on us.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] COALESCE() does not short-circuit sometimes

2020-03-11 Thread Dominique Devienne
On Wed, Mar 11, 2020 at 12:03 PM Justin Ng  wrote:
> -- Query 3
> SELECT
>   COALESCE(
> (SELECT 'hello'),
> ABS(-9223372036854775808)
>   );
> [...]. It should short-circuit and not evaluate ABS()

Interestingly, found this as well:
https://github.com/AnyhowStep/tsql/issues/233
SQLite COALESCE() does not short-circuit sometimes #233
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Status of LSM1 extension

2020-03-05 Thread Dominique Devienne
On Thu, Mar 5, 2020 at 12:35 PM Dan Kennedy  wrote:
> On 5/3/63 16:11, Dominique Devienne wrote:
> > I'm interested in LSM1 [1] as an alternative to SQLite [...]
>
> [...], I don't think it's too bad of an implementation. The
> automated tests are reasonably good - although of course not as good as
> SQLite's though. And the docs are stored in kind of a ridiculous place
> at the moment, but I think they're quite complete.
>
> Not planning to develop this any further unless a big user emerges,
> which is not impossible. I do intend to fix any reported bugs though.

Thanks Dan. I appreciate your candor, and support commitment.
Given the above, I'll start with SQLite, and when/if I'm done with what
I need to do, will try to give LSM1 a try, to compare performance, and
report back.

Thanks again, --DD

PS: I'd still very much appreciate an LSM1 amalgamation
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Status of LSM1 extension

2020-03-05 Thread Dominique Devienne
Hi,

I'm interested in LSM1 [1] as an alternative to SQLite, since in a
particular use-case,
I'm using SQLite mostly as a key-value store, and write performance is
particularly important,
in addition to MVCC. Sounds like it could be an excellent fit here,
and the fact it comes from
the SQLite team is something I value.

That said, the only online doc for LSM1 ([2] and [3]) are from the
defunct SQLite4 web-site,
and the main blog post is starting to look dated [4]. I thus wonder
about the level of quality
and support on LSM1, and lack of doc for it in the main SQLite web-site.

In terms of practicality, there's also no amalgamation for LSM1. And the virtual
table over LSM1 data-files [5], something I was look for, does not
appear to be documented
anywhere. Notably whether using that vtable using the familiar SQLite
API is advisable
instead of using the different and unfamiliar LSM1-specific API.

I'm just looking for clarity and advice around LSM1, as well as
commitments regarding
its level of quality, testing, and support. And whether we can hope to
have more doc and
amalgamation deliverables in the future. It sounds like it's a really
nice piece of code, but
the fact there's very little noise and advertisement about it is
somewhat worrying.

Thanks, --DD

[1] https://www2.sqlite.org/src/dir?name=ext/lsm1
[2] https://sqlite.org/src4/doc/trunk/www/lsmusr.wiki
[3] https://sqlite.org/src4/doc/trunk/www/lsmapi.wiki
[4] https://charlesleifer.com/blog/lsm-key-value-storage-in-sqlite3/
[5] https://www2.sqlite.org/src/finfo?name=ext/lsm1/lsm_vtab.c
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] How to enforce a specific order of group_concat?

2020-03-03 Thread Dominique Devienne
On Mon, Mar 2, 2020 at 6:35 PM Keith Medcalf  wrote:
> Well, in theory an order by in a nested select means that the result of the 
> operation is an ordered projection and not merely a set of rows.
> For this particular case (a nested select with an order by and the outer 
> query with an aggregate) the query will not be flattened (#16)

OK. I was more trying to find out whether such nested "ordered"
projections were a standard-SQL thing or not.

> select x,y from (select x, y from t order by y) order by x;
> will do two order-by sorts to obtain the result even though the query could 
> be (in this particular case) re-written as "select x, y from t order by x, y"

That's assuming the sort is "stable" :)  Stable-sort is typically
slower than non-stable-sort, that's why the STL has std::sort and
std::stable_sort.

> This is why putting an "order by" in a view will usually preclude query 
> flattening because the view is not merely producing a "set of rows" it is 
> producing an "ordered projection" and the ordering must be significant else 
> it would not be there.

I would actually prefer these nested order-by to be ignored, and the
"set of rows" being assumed, forcing the outer query to do its own
ordering.
The very notion of "ordered projection" for nested query sounds more
like an implementation detail, to word-around the lack of window
functions,
than something "official" from the SQL standard or relational theory.

I'm not disputing how SQLite implements things, for historical or
practical reasons, I just want to understand whether such "ordered
projection"
is an official concept from SQL or just an SQLite thing. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] How to enforce a specific order of group_concat?

2020-03-02 Thread Dominique Devienne
On Mon, Mar 2, 2020 at 5:09 PM Keith Medcalf  wrote:
> select group_concat(value) from (select distinct value from test order by 
> value);

But is that guaranteed to be ordered correctly "forever" instead of by
"happenstance"
from current implementation details? My point was that the Window
Function version
is ordered "by design", and not an implementation detail (as I think
the simpler version is).

Your subquery returns rows in a given order too, but "who" says
they'll be processed in that order?
Tables are just "sets of rows" after all, and the relational model is
about set-theory, no? order by
in subquery therefore make little to no sense in nested SQL (in theory...). --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] How to enforce a specific order of group_concat?

2020-03-02 Thread Dominique Devienne
On Sun, Mar 1, 2020 at 10:58 PM mailing lists  wrote:
> Are there any other solutions / possibilities?

I thought someone more knowledgeable than I about Window Functions [1]
would answer,
but since nobody mentioned them so far, I'll do it, as I believe this
is the "SQL native" way
to achieve what you want (modulo DISTINCT perhaps). Notably (from the doc):

Every aggregate window function can also work as a ordinary aggregate function,
simply by omitting the OVER and FILTER clauses. Furthermore, all of
the built-in aggregate
functions of SQLite can be used as an aggregate window function by
adding an appropriate OVER clause

[2] has an example with group_concat() and OVER (ORDER BY ...). I
assume that's what you need,
someone better at Window Functions then me (not difficult!) can
confirm or not that. --DD

[1] https://www.sqlite.org/windowfunctions.html
[2] https://www.sqlite.org/windowfunctions.html#aggwinfunc
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] CSV import using CLI (header, NULL)

2020-02-29 Thread Dominique Devienne
On Sat, Feb 29, 2020 at 1:42 PM Shawn Wagner 
wrote:

> To import a csv file with headers into an existing table, you can use
> .import '| tail -n +2 yourfile.csv' yourtable
> to skip the header line.


On unix. And by shell’ing out to native tools, so not portable.
The cli ought to have something built in, if it doesn’t already.

>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Error/Result code documentation

2020-02-26 Thread Dominique Devienne
On Wed, Feb 26, 2020 at 11:09 AM Dominique Devienne  wrote:
> Hi. In the doc excerpt below from https://www.sqlite.org/rescode.html
> are the $n*Code variables supposed to be expanded with a numeric
> value, and there's a little issue in the doc generation? Thanks, --DD

Thanks for the fix Richard. --DD

To anyone else, here's the fix:
https://www.sqlite.org/docsrc/info/d35032eb7ff2018b
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Error/Result code documentation

2020-02-26 Thread Dominique Devienne
Hi. In the doc excerpt below from https://www.sqlite.org/rescode.html
are the $n*Code variables supposed to be expanded with a numeric
value, and there's a little issue in the doc generation? Thanks, --DD

4. Primary Result Code List
The $nPrimCode result codes ...

5. Extended Result Code List
The $nExtCode extended result codes ...
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

2020-02-13 Thread Dominique Devienne
On Wed, Feb 12, 2020 at 9:02 PM Eric Grange  wrote:
> [...] This is completely safe vs SQL injection, and IME quite efficient. [...]

I disagree that this is efficient enough. I'd much rather have native support in
SQLite for array binding, in the public API, than this. That public
API could wrap
what carray does perhaps, except in a type-safe way (which carray is not IMHO).

e.g., the API could be, for SQL "select rowid from tab where owner = ?
and name_id in (?)":

sqlite3_bind_int(stmt, 1, scalar_int_val);
sqlite3_bind_array_begin(stmt, 2, vector_int_val.size()); // size
hint, to pre-size internal buffers
for (int i : vector_int_val) { // C++11 range-for loop
  sqlite3_bind_int(stmt, 2, i);
}
sqlite3_bind_array_end(stmt, 2);

That syntax is completely made up, but with the equivalent of carray(), SQLite
could efficiently "do the right thing" (perhaps rewriting the SQL into
a join), knows
the cardinatity of the array, so can order the join correctly, etc...

The above approach adds only two APIs, and reuses the existing bind APIs,
to avoid duplicating them all with array-variants. FWIW :). --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Does a foreign key field need a separate index?

2020-02-06 Thread Dominique Devienne
On Thu, Feb 6, 2020 at 11:08 PM Simon Slavin  wrote:
> On 6 Feb 2020, at 9:23pm, Rael Bauer  wrote:
> > Is a foreign key field automatically indexed, or will it benefit from a 
> > separately created index?
> No indexes for either lookup are automatically created.

The shell's .expert command might recommend them. Not sure, haven't tried.
But in general, yes, FKs should be indexed, if are are CASCADE'ing them. --DD

https://sqlite.org/cli.html#expert
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [EXTERNAL] Re: Patch: VTable Column Affinity Question and Change Request

2020-02-06 Thread Dominique Devienne
On Thu, Feb 6, 2020 at 9:32 AM Hick Gunter  wrote:
> >Of course, it may be that the writer of the VTable should know what they are 
> >doing and generate a VTable definition that is consistent with how their 
> >cursor methods return data, however ... this will omit the OP_Affinity if no 
> >>column type was specified when the VTable was defined and most of the 
> >VTable declarations in the existing code that I looked at do not specify 
> >column affinities in the declarations.
>
> Very nice. but detrimental for our use case. Please refrain from adding this 
> tot he distribution by default.
> We are almost exclusively using virtual tables to allow queries against our 
> internal data sources, which are C language structs and thus strictly typed. 
> The column affinities provided by the VTab implementations are used for 
> documentation purposes and the xColumn methods always return the same type 
> (calling the "wrong" sqlite3_result function is considered a programming 
> error). Coercing the returned value to the same type would be just a waste of 
> memory and CPU cycles.

+1. I fear what it would do to our app, also making extensive use of vtables.
Like Gunter mentions, the type is there more for documentation, I'm
unsure "what havoc this could wreak".

Note that our vtables are all read-only, if that matters here. It's
unclear to me if the above applies to writes only,
or also applies to reads. If to writes only, then I don't care much at
the moment, although I might in the future,
and would likely prefer seeing the raw value in my code, than the
result of affinity-coercion. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Check constrain execution timing change? (Now a bug)

2020-02-04 Thread Dominique Devienne
On Tue, Feb 4, 2020 at 5:38 PM Doug  wrote:
> > You're twisting my point here. I obviously want the reverse,
> > I want the database types to "drive" the binding done. 1-to-1.
> > Because even if binding a different type would work, via SQLite's
> > own implicit conversion, I don't want that, because it's hiding a
> > bug in the code most likely instead. --DD

> Is the code inadvertently putting quotes (') around in integer value [...]?

I'm talking about "real" binding here:
https://www.sqlite.org/c3ref/bind_blob.html
In C/C++, you could mess up your col indexes when binding, or bind incorrectly
for some other reason, and "strong static typing" is more likely to
find those, via
SQL failures, than SQLite's default flexible-typing, that accepts any
value in any typed column,
unless you have these explicit CHECK+typeof constraints. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Check constrain execution timing change? (Now a bug)

2020-02-04 Thread Dominique Devienne
On Mon, Feb 3, 2020 at 6:42 PM James K. Lowden  wrote:
> Do you want to force applications to "pre-convert" values the DBMS can
> convert implicitly?

Yes, that's exactly what I want James.
I want the enforce the bind-value type to be an exact match for the
column value type.

I could before, via CHECK+typeof(), so that's still breaking BC,
even though I accept that now that I was explained things.

> Do you want binding choices in the application to
> drive the datatype in the database, or do you want the database to
> enforce types?

You're twisting my point here. I obviously want the reverse,
I want the database types to "drive" the binding done. 1-to-1.
Because even if binding a different type would work, via SQLite's
own implicit conversion, I don't want that, because it's hiding a bug
in the code most likely instead. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Check constrain execution timing change? (Now a bug)

2020-02-03 Thread Dominique Devienne
On Mon, Feb 3, 2020 at 5:35 PM Richard Hipp  wrote:
> On 2/3/20, Dominique Devienne  wrote:
> > On Sun, Feb 2, 2020 at 12:50 AM Richard Hipp  wrote:
>
> This is the SQL:
>
>   CREATE TABLE t1(x INT CHECK(typeof(x)=='integer'));
>   INSERT INTO t1(x) VALUES('123');
>
> You say that you want to prevent the use of the string literal '123'
> for inserting into the integer field x.  That will no longer be
> possible in SQLite beginning with 3.32.0 (assuming the change
> currently on trunk goes through.)
>
> But, why do you want to do that?  How do you prevent the use of a
> string literal to initialize an integer field in MySQL, PosgreSQL, SQL
> Server, and Oracle - all of which accept and run the SQL above
> (without the CHECK constraint) with no errors?

Right. Implicit conversion also happen in these other DBs (I just
checked Oracle,
but I trust you're way more qualified to assert that me).

> If your goal is to prevent an actual string from being stored in the
> "x" column, then the legacy CHECK constraint still works for that.
> The following insert still fails:
>
>INSERT INTO t1(x) VALUES('xyzzy');

Right again. It fails with "ORA-01722: invalid number" on Oracle.
(no need for a CHECK constraint of course)

> But, you will no longer be allowed to prevent the type coercion that
> forces the '123' value into an integer 123, I think.  At least, I do
> not see a way to do that on trunk right now.

OK. I was more thinking of the '123' staying as text-typed in the DB.
But if it is coerced into the column's type (well, "affinity", not type per se),
then whether the value is bound as a string or a integer should be immaterial.

I still think my code shouldn't be binding values of a type different
than the column's,
and would still greatly prefer "strong *static* typing", which I
emulated with CHECK typeof(),
since it smells like a bug in the code IMHO, but as long as the stored
value is "OK", sure
that makes little differences in the end.

So now that I understand the better, so be it I guess.
I'm sure you have a good reason to make that change, despite the
surprising break in BC for SQLite.

Thanks for taking the time to spell it out for me. --DD

PS: I still wish for a pragma for strong static typing (no need for
CHECK typeof()),
  and now also wish for that to happen even before implicit
conversions. But I've long
  accepted this is unlikely to ever happen :(
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Check constrain execution timing change? (Now a bug)

2020-02-03 Thread Dominique Devienne
On Sun, Feb 2, 2020 at 12:50 AM Richard Hipp  wrote:

> On 2/1/20, Thomas Kurz  wrote:
> > Does this mean there will be no possibility to prevent inserting a string
> > into an integer column anymore?
> >
> > create table x (x integer check (typeof(x) == 'integer'));
> > insert into x values ('1');
> >
> > --> will pass in future versions???
>
> I think that is what it means.  yes.

Wow... I haven't caught up on this thread, but that's really really bad IMHO,
and would consider that a serious regression. I've been enforcing
"strong-typing",
(or "inflexible-typing" if you prefer Richard) for many schemas, and
the fact we can
no longer do that would be a real shame. I wonder where this is coming
from... --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Behavior change around triggers between 3.19 and 3.30

2020-01-30 Thread Dominique Devienne
On Thu, Jan 30, 2020 at 3:38 PM Graham Holden  wrote:
> Thursday, January 30, 2020, 12:24:40 PM, Dominique Devienne 
>  wrote:
> > The strange thing though, is that I can't repro on a small example.
> > Despite using not_there in the trigger, and doing DML and ALTER TABLE,
> > still doesn't fail the same way as in production. What could be the cause? 
> > --DD
>
> I suspect it may only kick-in if you use ALTER TABLE to rename either
> a table or column. As I understand it, the old behaviour was to JUST
> rename the table/column; the new behaviour also scan triggers etc. and
> renames any references to the table/column as well. Since a new column
> cannot (shouldn't?) be referenced by a trigger, there's no real need
> to check them.

Good point. Confirmed below it seems. Thanks Graham. --DD

3.19.3 happily renamed the table, and the trigger is still invalid.
3.30.1 error'd out with: Error: error in trigger t2_on_update_ko: no
such column: OLD.not_there

 Console#1 on 3.19.3 
sqlite> select name, sql from sqlite_master where type = 'trigger';
t2_on_insert_ok|CREATE TRIGGER t2_on_insert_ok after insert on t2
begin insert into t2bis(id,v) values(NEW.id, NEW.v); END
t2_on_update_ko|CREATE TRIGGER t2_on_update_ko after update of
not_there on t2 when OLD.not_there != NEW.not_there begin update t2bis
set v=NEW.v where id=OLD.id; END

sqlite> alter table t2 rename to t2a;

sqlite> select name, sql from sqlite_master where type = 'trigger';
t2_on_insert_ok|CREATE TRIGGER t2_on_insert_ok after insert on "t2a"
begin insert into t2bis(id,v) values(NEW.id, NEW.v); END
t2_on_update_ko|CREATE TRIGGER t2_on_update_ko after update of
not_there on "t2a" when OLD.not_there != NEW.not_there begin update
t2bis set v=NEW.v where id=OLD.id; END
sqlite>

 Console#2 on 3.30.1 
sqlite> select name, sql from sqlite_master where type = 'trigger';
t2_on_insert_ok|CREATE TRIGGER t2_on_insert_ok after insert on "t2a"
begin insert into t2bis(id,v) values(NEW.id, NEW.v); END
t2_on_update_ko|CREATE TRIGGER t2_on_update_ko after update of
not_there on "t2a" when OLD.not_there != NEW.not_there begin update
t2bis set v=NEW.v where id=OLD.id; END

sqlite> alter table t2a rename to t2b;
Error: error in trigger t2_on_update_ko: no such column: OLD.not_there

sqlite> select name, sql from sqlite_master where type = 'trigger';
t2_on_insert_ok|CREATE TRIGGER t2_on_insert_ok after insert on "t2a"
begin insert into t2bis(id,v) values(NEW.id, NEW.v); END
t2_on_update_ko|CREATE TRIGGER t2_on_update_ko after update of
not_there on "t2a" when OLD.not_there != NEW.not_there begin update
t2bis set v=NEW.v where id=OLD.id; END
sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Behavior change around triggers between 3.19 and 3.30

2020-01-30 Thread Dominique Devienne
On Thu, Jan 30, 2020 at 1:09 PM Richard Hipp  wrote:
> On 1/30/20, Dominique Devienne  wrote:
> > My first question would be to ask whether there's a pragma or
> > compile-time option to get back to the old behavior?
>
> Did you try "PRAGMA legacy_alter_table=ON;"?

BINGO!!! Thanks a bunch Richard.

> > Second, any idea when this was introduced?
> People have been requesting enhanced ALTER TABLE support.  In order to
> provide that, we had to change ALTER TABLE to do a full parse of the
> entire schema, so that it can find all of the bits and pieces that
> need altering.  This means that ALTER TABLE now also finds latent
> syntax errors in the schema.

The strange thing though, is that I can't repro on a small example.
Despite using not_there in the trigger, and doing DML and ALTER TABLE,
still doesn't fail the same way as in production. What could be the cause? --DD

 Console #1 
c:\Users\ddevienne\SQLite>sqlite3 with-3.19.3.db
SQLite version 3.19.3 2017-06-08 14:26:16
Enter ".help" for usage hints.
sqlite> create table t1(v);
sqlite> create table t2(id INTEGER PRIMARY KEY, v);
sqlite> create table t2bis(id INTEGER PRIMARY KEY, v);
sqlite> create trigger t2_on_insert_ok after insert on t2 begin insert
into t2bis(id,v) values(NEW.id, NEW.v); END;
sqlite> create trigger t2_on_update_ko after update of not_there on t2
when OLD.not_there != NEW.not_there begin update t2bis set v=NEW.v
where id=OLD.id; END;
sqlite> .exit

c:\Users\ddevienne\SQLite>sqlite3 with-3.19.3.db
SQLite version 3.19.3 2017-06-08 14:26:16
Enter ".help" for usage hints.
sqlite> select name, type from sqlite_master;
t1|table
t2|table
t2bis|table
t2_on_insert_ok|trigger
t2_on_update_ko|trigger
sqlite>
sqlite> insert into t1(v) values (1);
sqlite> insert into t2(v) values (1, 'one');
Error: 2 values for 1 columns
sqlite> insert into t2(id, v) values (1, 'one');
sqlite> update t2 set v = 'uno' where id = 1;
sqlite> alter table t1 add column v2;
sqlite> alter table t2 add column v2;
sqlite> pragma legacy_alter_table;
sqlite>

 Console #2 
C:\Users\ddevienne\SQLite>sqlite3 with-3.19.3.db
SQLite version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.
sqlite> select name, type from sqlite_master;
t1|table
t2|table
t2bis|table
t2_on_insert_ok|trigger
t2_on_update_ko|trigger
sqlite> insert into t1(v) values (2);
sqlite> insert into t2(id, v) values (2, 'two');
sqlite> update t2 set v = 'dos' where id = 2;
sqlite> alter table t1 add column v3;
sqlite> alter table t2 add column v3;
sqlite> pragma legacy_alter_table;
0
sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Behavior change around triggers between 3.19 and 3.30

2020-01-30 Thread Dominique Devienne
BEFORE 3.19.3 2017-06-08 14:26:16
0ee482a1e0eae22e08edc8978c9733a96603d4509645f348ebf55b579e89636b
AFTER 3.30.1 2019-10-10 20:19:45
18db032d058f1436ce3dea84081f4ee5a0f2259ad97301d43c426bc7f3dfalt2

Every 18 to 24 months we upgrade SQLite in a large commercial software suite.
Such a recent upgrade surfaced a major backward-compatibility issue in
the custom
upgrade mechanism of that software suite, when restoring old projects.

The problem was traced back to a bad trigger definition, using a WHEN
clause on an invalid column (that does NOT exist). It's of course
trivial to get rid of that trigger in the latest version of the
schema, but the fact the upgrade fails when processed using 3.30.1 is
a major issue and new.

The same upgrade, processed with 3.19.3, goes through without errors.

The upgrade executes an ordered linear series of +1 version upgrade
scripts (pretty common),
and the first few pure-DDL scripts execute OK, but a subsequent
upgrade script with DML fails, with an error about the non-existing
column referenced by the trigger. That one error of course fails the
whole custom upgrade process.

My first question would be to ask whether there's a pragma or
compile-time option to get back to the old behavior?

Second, any idea when this was introduced?

My guess would be that the first DML "triggers" the parsing of the
schema, which discovers the invalid trigger definition, which starts
failing in 3.30.1, while was "OK" in 3.19.1 (as in no errors
reported).

Is there anything we can do, short of reverting back to 3.19.3?

Note that all the SQL is processed by the official shell, not via custom code.
Also note we build from the amalgamation, mostly with default options,
adding JSON1.

Thanks for any help on the above. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] New word to replace "serverless"

2020-01-28 Thread Dominique Devienne
On Mon, Jan 27, 2020 at 11:19 PM Richard Hipp  wrote:
> How can I fix this?  What alternative word can I use in place of
> "serverless" to mean "without a server"?

Don't. I'm with Warren, Jens, Stephen on this one.

Keep it, but make a new sqlite.org/serverless doc page,
and link to it when you reference that term anywhere in the doc,
to explain the original (and more accurate) meaning of the serverless term.

Regarding the other proposal:
* embedded is accurate, but may make people think this is reserved for
"embedded" micro-controller programming, which it isn't of course.
* server-free is OK
* standalone is also accurate, but too vague.

My $0.02. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] New uuid extension in amalgamation

2020-01-23 Thread Dominique Devienne
Hi. Looks like 3.31 (congrats on the release) does not include that
small extension in the amalgamation. Could it please? Uuids are fairly
common in many schemas, so native support "by default" would
standardize support for them in the SQLite ecosystem. Thanks, --DD

PS: And we'd be able to retire our own functions, which are
inconveniently not available from the SQLite shell unless explicitly
loaded from our custom extension lib.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Find schema of a table in a query

2020-01-19 Thread Dominique Devienne
On Sun, Jan 19, 2020 at 9:47 AM x  wrote:

> Suppose you’re given a query ‘SELECT 1 from tbl’ by a user and you want to
> know the name of the schema that tbl belongs to. What’s the easiest way to
> do this?


Set an authorizer. Requires to write code though, cannot be done in SQL.
https://www.sqlite.org/c3ref/set_authorizer.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Next Release? Visual release timeline?

2020-01-16 Thread Dominique Devienne
On Wed, Jan 15, 2020 at 4:54 PM R Smith  wrote:
> On 2020/01/15 1:24 PM, Richard Hipp wrote:
> >> (2) Assume the data is a JSON array of pairs.  The first element of
> >> each pair is the release name (ex: "3.30.0") and the second element is
> >> the time as a fractional year (ex: "2019.7775").

Note that Richard replied to me private with a JSON array of this form:

chronology = [{"hash":"xx","vers":"3.31.0","date":2020.0398},
{"hash":"18db032d05","vers":"3.30.1","date":2019.7748},
{"hash":"c20a353364","vers":"3.30.0","date":2019.7557},
{"hash":"fc82b73eaa","vers":"3.29.0","date":2019.5202},
{"hash":"884b4b7e50","vers":"3.28.0","date":2019.2875},
{"hash":"bd49a8271d","vers":"3.27.2","date":2019.1506},
...]

So with a little gymnastic to recover the date, and given the hashes,
all the currently "hardcoded"  elements can also be generated
from this JSON array.

Of course, some people disable JavaScript, so "server-side" rendering
might be preferred.

> We'd like to submit this layout as an option:
> https://sqlitespeed.com/sqlite_releases.html
>
> Shown alongside the current list in simple form. Tried a few layouts,
> not all work as well (SQLite releases are much more dense than Lua),
> finally settled on the above, but left some options open.

Interesting, thanks for the submission. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Next Release? Visual release timeline?

2020-01-15 Thread Dominique Devienne
I like Lua's way to graphically visualize releases at
https://www.lua.org/versions.html

Makes it very easy to get a sense of the frequency. Any chance SQLite
would do that, perhaps with "major" (excluding the leading 3.)
releases on one side, and minor ones on the other?

It's been a quarter since the last release, which seems to be longuish
from a cursory glance
at the recent release history. Could it be related to all the
fuzzer-found issues around window functions? Just curious.

Thanks, --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

2020-01-14 Thread Dominique Devienne
On Tue, Jan 14, 2020 at 2:57 PM Jean-Baptiste Gardette
 wrote:
> SELECT * FROM t1 GROUP BY a HAVING b > 1;
>
> Will the GROUP BY clause be supressed and HAVING clause be rewritten in WHERE 
> clause by the optimizer ?

My question would be why you wouldn't write it as a WHERE clause in
the first place :)
Sorry, OT, I know. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Best way to store key,value pairs

2020-01-14 Thread Dominique Devienne
On Tue, Jan 14, 2020 at 9:35 AM Wout Mertens  wrote:
> On Mon, Jan 13, 2020 at 10:45 PM James K. Lowden 
> This is a trade-off between schema simplicity, storage layout and speed of 
> some operations. I'd
> argue that in this particular case, a JSON field is beneficial for 
> simplicity, speed and storage space.

+1. Echoes my own thoughts on this thread.

James is right too of course, in the absolute, but limited
denormalization for efficiency,
and arguably for simplicity too, despite James' opinion, are valuable.
Now it's "just" the
matter of making the right tradeoff based on the particular
circumstances, fully aware
of the pros and cons. And that requires experience and knowledge (and
testing/benchmarking, duh).

Hopefully this particular thread will help people on this recurring topic. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Unexplained table bloat

2020-01-13 Thread Dominique Devienne
On Mon, Jan 13, 2020 at 1:10 PM Keith Medcalf  wrote:
> If the register object contains "text" and you cast it to a blob (remove the 
> text affinity) you are left with just the bag-o-bytes, and length() will 
> return the size of the bag encoded in the register.  If the data in the 
> register is other than type "text" then it must be converted to text first 
> (in the database encoding) and then the cast will remove the text affinity, 
> after which the value returned by the length() function will be the number of 
> bytes in the bag that holds that text representation:
>
> sqlite> pragma encoding='utf-16';
> sqlite> create table x(x);
> sqlite> insert into x values ('text' || char(0) || 'text');
> sqlite> select x, typeof(x), length(x), length(cast(x as blob)) from x;
> text|text|4|18

Please remind me, is the encoding a "client-side" setting, or also a
"server-side" (i.e. stored) setting?

I wasn't sure whether pragma encoding='utf-16' affected the stored
state as well, or whether it was always in UTF-8
and SQLite was doing conversion on the fly for the client requested
encoding. I thought of lengthof() as the size stored
in the value header itself, which I assumed was always in bytes.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Unexplained table bloat

2020-01-13 Thread Dominique Devienne
On Mon, Jan 13, 2020 at 11:07 AM Keith Medcalf  wrote:
> On Monday, 13 January, 2020 02:27, Dominique Devienne  
> wrote:
> >> I'd vote for a lengthof(col) that's always O(1) for both text and blob
>
> So what should lengthof(something) return the number of bytes in the 
> 'database encoding' or something else?

Bytes of course. Of the data stored, i.e. excluding the header byte
and encoded size (if any) from the file-format.
Basically the same as length() *except* for text values, resulting in
O(1) behavior. --DD

PS: I keep forgetting length(text_val) returns the number of
code-points in fact :)
PPS: Surrogate pairs count as one or two code points? That's just
bait, I don't really want to know :)))
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Unexplained table bloat

2020-01-13 Thread Dominique Devienne
On Fri, Jan 10, 2020 at 7:03 PM Richard Hipp  wrote:

> On 1/10/20, Dominique Devienne  wrote:
> > There's no way at all, to know the length of a text column with embedded 
> > NULLs?
>
> You can find the true length of a string in bytes from C-code using
> the sqlite3_column_bytes() interface.  But I cannot, off-hand, think
> of a way to do that from SQL.

That's what I thought. Which implies length(text_val) is O(N), while
length(blob_val) is O(1),
something I never quite realized. And this despite both storing the
length the same way at the
file-format level. That's kind of a gotcha, which might be worth documenting.

I'd vote for a lengthof(col) that's always O(1) for both text and blob
values, although I'm not
holding my breath for it, as I suspect it's unlikely to be added,
given its relative low value.

I don't disagree with Keith that text shouldn't contain embedded
NULLs, and that C-String are
by design and definition that way, I was more thinking of the C++ use
case of having an std::string
with embedded NULLs, which is perfectly OK and common enough, and
using a C++ wrapper for
SQLite (which typically uses overloading for binding for example),
which will insert a text value for that
case, using .c_str() + .length() (or .data() + .size(), doesn't
matter, ends up the same), leading to the
very issue that started this thread (just a guess).

The inability to correctly size a value in SQL (and thus a column, via
a sum() group by)
for text with embedded nulls is quite unfortunate. And the fact
length(text_col) is also O(N)
is similarly unfortunate. Thus the above idea of an O(1)
lengthof(col), as a companion to typeof(col).

Thanks, --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Unexplained table bloat

2020-01-10 Thread Dominique Devienne
On Fri, Jan 10, 2020 at 4:30 PM Richard Hipp  wrote:
> length() on a BLOB should show the number of bytes in the BLOB.
>
> length() on a string should show the number of *characters* (not
> bytes) in the string up through but not including the first
> zero-character.  It is possible to have additional content after the
> first zero-character in a string, which length() will not tell you about.

Hi Richard,

There's no way at all, to know the length of a text column with embedded NULLs?

--DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] A hang in Sqlite

2020-01-06 Thread Dominique Devienne
On Mon, Jan 6, 2020 at 3:49 PM Domingo Alvarez Duarte 
wrote:

> I understand the original point of view of this thread, but I'm glad
> that we have those submissions here because it makes me aware of
> people/tools/technics/patterns that can help in other projects.
>

I completely agree. Yet at the same time, at the cost of doubling an MLs
traffic?

That's why I made this request to SQLite devs for alternate reporting means,
and only after 6 weeks and dozens of reports. We're well aware of
people/tools/technics by now, if not patterns (Windows functions mostly)

"polluting" the ML was an exaggeration, I just couldn't find a better word.
The work of fuzzers is of great value, no dispute here, but not so much in
the ML.

None of us (again, an exaggeration) was aware of Manuel Rigger's reports
for example before recently,
yet they've been going on for months (about 6 months if I recall
correctly). Nor do we have access to these
fuzzers, AFAIK, there's no doc that I'm aware of on them.

Personally I'm happy for the experts only to get these reports (DRH and
Dan), and I'm thankful
to the fuzzers who contribute them, because we all benefit from a better
SQLite as a result, but
I don't care much for them in the ML... --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] A hang in Sqlite

2020-01-06 Thread Dominique Devienne
On Mon, Jan 6, 2020 at 2:36 PM Jose Isaias Cabrera 
wrote:

> Dominique Devienne, on Monday, January 6, 2020 07:51 AM, wrote...
> [...] it's "polluting" a bit this ML.
>
> This is why we are all so different.  I, actually enjoy the "pollution"
> because I try to look at the code and, some times, learn from it. But, yes,
> I am learning, so this is new to me.  Perhaps, if I were to have more SQL
> knowledge, perhaps I would think the same.


Sure. I get that. But learning from fuzzer SQL is probably not the best way
to go about it, IMHO :)
They go into dark corners and even nonsensical SQL to find bugs, so hardly
newbie material.

And you can also look at the bug tracker at
https://www.sqlite.org/src/rptview?rn=1 for at least Mr Rigger's reports,
and possibly soon Yongheng Chen's reports too, if you miss them from the
ML. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] A hang in Sqlite

2020-01-06 Thread Dominique Devienne
On Mon, Jan 6, 2020 at 8:30 AM Dan Kennedy  wrote:

> On 6/1/63 13:44, Yongheng Chen wrote:
> > We found a test case that hangs Sqlite:
>
> Thanks for all the work you've been doing on SQLite!
>

Indeed. But...

The frequency of all these fuzzer related emails has reached a point IMHO
that it's "polluting" a bit this ML.
Especially since most times there's no follow (in the ML at least) to most
of these messages.

In another recent thread that mentioned another "fuzzer" (Manuel Rigger), I
discovered his reports completely bypassed
the ML and go straight to the official bug tracker, which AFAIK is not
publicly writable, so Mr Rigger's quality work must
have granted him the privilege to report directly via it.

Thus I'd ask whether Mr Chen (or Mr Yongheng, I'm not sure) could similarly
go off-list and directly to bug tracker?
I have 37 separate threads starting Nov 22nd last year related to his
fuzzer discoveries, for roughly the same number
of threads related to all other topics, which effectively doubles this ML's
traffic (in thread count, not message count).

Thus my request. FWIW... --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Performance vs. memory trade-off question

2019-12-16 Thread Dominique Devienne
On Sat, Dec 14, 2019 at 2:27 PM Richard Hipp  wrote:

> QUESTION:  Should this feature be default-on or default-off?
>
> What's more important to you?  0.25% fewer CPU cycles or about 72KB
> less heap space used per database connection?
>

Backward compatibility. I.e. if I change nothing in my build, and upgrade
the amalgamation,
then I get the behavior closest to the one before this change. Which if
default-off at compile-time.

Embedded (or many connection) scenarios wanting this can always turn it on
explicitly. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Securing user supplied SQL statements in a single process

2019-12-12 Thread Dominique Devienne
On Thu, Dec 12, 2019 at 1:47 PM test user 
wrote:

> How can I secure user supplied SQL statements in a single process?
>

The one mechanism SQLite has is the authorizer [1].
Whether that's good enough for you, that's for you to determine. --DD

[1] https://www.sqlite.org/c3ref/set_authorizer.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Changes in version 3.28.0 makes sql run slower

2019-12-06 Thread Dominique Devienne
On Fri, Dec 6, 2019 at 2:21 PM Richard Hipp  wrote:

> On 12/6/19, Keith Medcalf  wrote:
> >
> > Perhaps the optimizer could make a "left join" that is not actually an
> outer
> > join into a "cross join" and preserve the nesting order ... ?
>
> It could do that, but that would kind of defeat the whole purpose of
> the optimization, which was to free up the planner to choose a
> different nesting order in cases where the LEFT JOIN didn't really matter.
>
> I suspect that ANALYZE might also solve the OP's problem.
>

Yes it did (see below). But he also mentions that ANALYSE is slow though.
Any chance there could be one day a "fast-analyse" that's less precise but
still good-enough to steer the plan in the right direction? --DD

On Fri, Dec 6, 2019 at 11:06 AM radovan5  wrote:

> Yes it has and I get correct plan. Did not use analyze before [...]

analyze is quite slow also [...]
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Changes in version 3.28.0 makes sql run slower

2019-12-06 Thread Dominique Devienne
On 06.12.2019 10:33, Shawn Wagner wrote:
> Does running an ANALYZE have any impact?
>

On Fri, Dec 6, 2019 at 11:06 AM radovan5  wrote:

> Yes it has and I get correct plan. Did not use analyze before or pragma
> optimize
> but I see I would have to. Thank you for reminding me to this. Just in my
> case
> analyze is quite slow also. So in the end I get same time. It is faster to
> use just
> workaround in sql.


I don't recall the specific, but I believe there are other ways to
influence the planning,
via hints and/or other means. If you are sure one table is always bigger
than the other,
but don't want to run ANALYSE, you can also take a generic sqlite_stat1
entries and
"inject" them "manually" into your DBs. Not sure it's good advice, but I
kinda remember
it being mentioned as well on this list in the past (I think). FWIW. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] sqlite syntax auto suggest

2019-11-29 Thread Dominique Devienne
On Fri, Nov 29, 2019 at 2:10 PM Richard Hipp  wrote:

> On 11/29/19, Laurent  wrote:
> >
> > Could you perhaps explain what pos means and what the [isTerminal] =
> 0 rows mean.
>
> New check-in enhances the output to include a comment in the SQL
> before the encoding of each production rule.  This should help make it
> clear what the SQL is trying to represent.
>

Hi. Why not add a column and write it to the SQL insert?

Rather than as a comment in the SQL "dump"? The first thing I'd do with
that new output file,
would be to run it to generate the DB, then look at the DB in a graphical
client, to side and dice
the data. As it stands, those useful comments would be lost in that
scenario. My $0.02. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] built-in printf() not supporting \n or \t ?

2019-11-28 Thread Dominique Devienne
Obviously it's ugly to use concatenation and char() to format a string
literal with tabs and newlines.
Is there a better way? Why doesn't printf() support newlines and tabs like
it's C cousin? --DD

PS: Built-in printf() also doesn't support positional params, to "emulate"
newline with printf( '%1$s1: %2$s%1$s2: %3$s%1$s' , char(10), 'one',
'two'), but that's not too readable either, in any case

sqlite> select printf('\n1: %s\n2: %s\n', 'one', 'two');
\n1: one\n2: two\n
sqlite> select char(10)||printf('1: %s', 'one')||char(10)||printf('2: %s',
'two')||char(10);

1: one
2: two

sqlite> select printf('\t1: %s\t2: %s\t', 'one', 'two');
\t1: one\t2: two\t
sqlite> select char(9)||printf('1: %s', 'one')||char(9)||printf('2: %s',
'two')||char(9);
1: one  2: two
sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Concurrency Question

2019-11-25 Thread Dominique Devienne
On Sat, Nov 23, 2019 at 4:17 PM Dan Kennedy  wrote:

> > [...] Why is thread_B blocked when doing a read just because SQLite is
> writing to another table? [...]
> > Is this the expected behavior or am I doing something stupid in my code.
> And if so, what to check?
>
> This should only happen if you are using shared-cache mode. Don't use
> shared-cache mode.
>

But I'm forced to use shared-cache for multiple connections to an in-memory
database [1].

This is an important use-case IMHO, and the fact in-memory DBs can't use
WAL-mode,
and benefit from the added concurrency in the face of updates, is a real
bummer IMHO.

[1] https://www.sqlite.org/sharedcache.html#inmemsharedcache
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Using application_id

2019-11-19 Thread Dominique Devienne
On Tue, Nov 19, 2019 at 2:00 AM Peter da Silva  wrote:

>
> > If you stick to lower or upper case letters, could encode up to 6 chars
> in the app_id. --DD
>
> The return of RADIX-50.
>
> https://en.wikipedia.org/wiki/DEC_Radix-50


Thanks! I might go with this going forward. --DD

PS: I tend to prefer reusing "old tech" like this, which already has a
"known" name, and Wikipedia page,
rather that inventing my own similar but poorly-documented-by-comparison
scheme. When I'm aware of them that is :)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Using application_id

2019-11-18 Thread Dominique Devienne
On Mon, Nov 18, 2019 at 2:41 PM Clemens Ladisch  wrote:

> Tobias Leupold wrote:
> Apparently, authors or 'private' file formats do not bother to register
> their IDs.
>

Indeed, there's little point, as those are rarely "public".

I tend to chose a 4 letter prefix related to the kind of app/report the
SQLite DB is for,
and use the hexa for each letter to generate the app_id 32-bit integer.

If you stick to lower or upper case letters, could encode up to 6 chars in
the app_id. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Official Github Mirror? Of the Amalgamation?

2019-11-18 Thread Dominique Devienne
On Mon, Nov 18, 2019 at 10:41 AM Shawn Wagner 
wrote:

> There is an official github mirror of the fossil repository:
> https://github.com/sqlite/sqlite


Thanks. I thought there was one, but search below didn't find it:
https://www.google.com/search?q=sqlite+github+official+mirror

It's weird the official repo mirror doesn't rank very high.

Maybe if https://sqlite.org/whynotgit.html linked to it, it might.

At least I would have found it that way, since as the 3rd result from
Google I visited that Why-not-GIT link,
but it's not mentioned there either apparently (not at the top at least) .
Probably should IMHO.


> The amalgamation is two files, though. When you want to upgrade a bundled
> sqlite, to a new version it's trivial to update them. Setting up submodules
> or whatever seems like massive overkill.


OK, after a few more searches, seems like it's not worth it indeed.

Thanks again, --DD

PS: It feels wrong to me, it should be super easy to cherry pick files
(at given tag, hash, date, etc...) from another same-SCM repo IMHO.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Relax "DISTINCT aggregates" error

2019-11-18 Thread Dominique Devienne
On Fri, Nov 15, 2019 at 4:22 PM Jose Isaias Cabrera 
wrote:

> Dominique Devienne, on Friday, November 15, 2019 09:02 AM, wrote...
>
> Have you tried this,
> sqlite> select group_concat(distinct id || ', ') from t;
> 1, ,2, ,4, ,7,
>
> The only problem is that when the list has a non-distinct.  H.
>

That's obviously not the same result (trailing text), and a bit hackish.
Clever, but a no-go IMHO. I'm hoping for a "real" fix taking into account
the constant-ness of trailing arguments, having only the first arg being
row-dependent. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Official Github Mirror? Of the Amalgamation?

2019-11-18 Thread Dominique Devienne
In searches,  https://github.com/mackyle/sqlite comes up first,
but given that Fossil has good/better interop with GIT these days,
why not an official mirror?

Also, mirrors are for the normal repo, while ability to refer to an
amalgamation
in one's project by directly linking to it via a GIT submodule (or
something like
that, I'm no GIT expert) instead of copying it in one's project(s), would
be useful IMHO.

Just my $0.02c. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Relax "DISTINCT aggregates" error

2019-11-15 Thread Dominique Devienne
As can be seen below, the last query fail, despite the one before it
succeeding.
Yet the second argument is constant, thus it would seem "natural" for it to
work as well.
Could the error be "relaxed", when the non-first argument(s) to aggregate
functions are constant?

Thanks, --DD

C:\Users\ddevienne>sqlite3
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t (id int);
sqlite> insert into t values (1), (2), (4), (2), (1), (7);
sqlite> select count(distinct id) from t;
4
sqlite> select group_concat(distinct id) from t;
1,2,4,7
sqlite> select group_concat(distinct id, ', ') from t;
Error: DISTINCT aggregates must have exactly one argument
sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] SQLITE_DETERMINISTIC and custom function optimization

2019-11-14 Thread Dominique Devienne
On Sat, Nov 9, 2019 at 1:20 PM Mario M. Westphal  wrote:

> Thanks to all the friendly people who commented on my question. Much
> appreciated :-)
>
> I was able to solve this with a small trick:
> I created a small 'state' struct with a rowid and the result (float) for
> that row.
>

Sounds like you re-invented https://www.sqlite.org/c3ref/get_auxdata.html but
with global state, no?
I replied to your original thread with that link, before seeing this
message. Using the built-in SQLite
mechanism for function caching is much better, because it's clean, and
properly handles the lifetime
of the cache, tying it to the statement execution lifetime.

Of course, if you want to tie your cache to a longer lifetime, *across*
statement executions,
you can use a global cache independent of SQLite, as it seems you did, but
global state like
this is rarely a good idea in my experience :). YMMV. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] SQLITE_DETERMINISTIC and custom function optimization

2019-11-14 Thread Dominique Devienne
On Fri, Nov 8, 2019 at 9:20 PM Keith Medcalf  wrote:

> [...] The optimizer is prone to calculating things more often than it
> needs to, and is difficult to force to "materialize" things.

Since your expensive function needs to be calculated for every row of the
> table anyway, it would be better to just create a table

that has it calculated once, then compute the updates table, then perform
> the update, then get rid of the extra tables. [...]
>

A better option IMHO is for the function itself to memoize its results, for
the duration of the statement's execution.
That way even if it's called multiple times, you can fetched the cached
result instead of re-performing the expensive computation.

Use https://www.sqlite.org/c3ref/get_auxdata.html for the caching. --DD

PS: Didn't read the whole thread in detail, maybe my answer is a bit off
topic :)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] SQLite with branching

2019-11-05 Thread Dominique Devienne
On Tue, Nov 5, 2019 at 10:01 AM Wout Mertens  wrote:

> On Mon, Nov 4, 2019 at 10:26 PM Jens Alfke  wrote:
>
> > I don't have a practical use for the branching features, though they're
> cool, but I'm salivating at the thought of a 2x speedup.
> > With all the work that's put into eking out small performance increases
> in SQLite, I'd imagine the devs would be interested in
> > something that made that big of a difference...
>
> What I would like to know is how such a performance increase is
> achieved, and why regular SQLite can't do the same?
>

AFAIK, that was one of the goals of SQLite4 [1], to change the backend to
LSM.
We know now SQLite4 is basically abandoned, but LSM was refactored as an
SQLite3 extension [2].
Here's an article that goes into more depth on the subject [3]. Hope this
helps. --DD

[1] https://sqlite.org/src4/doc/trunk/www/index.wiki
[2] https://www.sqlite.org/src/dir?ci=5710845b6314f924=ext/lsm1
[3] https://charlesleifer.com/blog/lsm-key-value-storage-in-sqlite3/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] DELETE extremely slow

2019-10-31 Thread Dominique Devienne
On Thu, Oct 31, 2019 at 4:10 PM Thomas Kurz  wrote:

> Yes, but I'd expect that MariaDB has to do the same, but takes clearly
> less than 1 minute instead of 88 minutes... :confused:
>

Are we comparing apples to oranges here?

SQLite runs on the local machine. While MariaDB is client-server, so the
delete effectively runs on the server.
Is your MariaDB server local to the same machine you're running SQLite on?
Are both using the same local disks? (as opposed to networked and/or
different disks)

Still can't account for 88 minutes though.
You haven't shown the "explain query plan" Keith asked for, which would
help understand what's going on here.
See https://www.sqlite.org/eqp.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] DELETE extremely slow

2019-10-31 Thread Dominique Devienne
On Thu, Oct 31, 2019 at 10:57 AM Thomas Kurz  wrote:

> I'm using a database with 5 hierarchically strcutured tables using foreign
> keys. The largest table contains about 230'000 entries. My problem is that
> deleting in this database is extremely slow:
>
> pragma foreign_keys=on;
> pragma journal_mode=wal;
> .timer on
> delete from dataset;
> --> Run Time: real 197993.218 user 53015.593750 sys 54056.546875
>
> I experimentally imported the same data into a MariaDB database and tried
> the same operation there (without paying attention to creating any indexes,
> etc.). It takes only a few seconds there.
>
> Is there something I can check or do to improve deletion speed?
>

You're not describing the schema enough IMHO.
Is dataset the "top-most" table, containing the "parent" rows all other
tables references (directly or indirectly),
with all FKs having ON DELETE CASCADE?

If that's the case, without some kind of optimization in SQLite, when the
first parent row is deleted,
it triggers a cascade of deletes in "child" tables, looking for rows using
the parent row. So if your FKs
are not indexed for those column(s), that's a full table scan each time...
That's "depth first".

By analyzing the graph of FKs and their ON DELETE CASCADE state, and in the
specific case of
fully deleting the "main parent table", SQLite could decide switch to a
smarter "breadth first" delete,
but I suspect it's not a compelling enough use-case for Richard to invest
time on this.

Try indexing your FKs, and see what happens. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Generated columns in primary keys, indexes, and constraints

2019-10-31 Thread Dominique Devienne
On Wed, Oct 30, 2019 at 11:12 AM Richard Hipp  wrote:

> [...] But using a VIRTUAL generated column as a PRIMARY KEY would be an
> issue.
>

FWIW, I depend on this feature in Oracle, and it works. Both my PRIMARY and
FOREIGN keys
are VIRTUAL columns, which combine two stored columns. Oracle even allows
to ALTER TABLE
to go from a mode where PKs and FKs are scalar and using stored columns,
and another where
those PKs and FKs are switched to (still scalar) constraints but using
these VIRTUAL columns instead.
The big advantage of this is that the upgrade (one way or another)
"theoretically" writes nothing on disk
since the stored columns do not change at all, only VIRTUALs and
CONSTRAINTS are added/removed.

An alternative to this design would be to use composite PKs and FKs, but
our app depends heavily
on efficient handling of SELECT ... WHERE pk_col in (:1),  with :1 being
bound to a collection/array
of PK values, and I could not figure out a way to do the equivalent with a
composite PK. (and that's
also one reason why I've requested several times for a way to bind
collections in SQLite, in a way
that's not dependent on carray() which I'm not fond of since does not
handle lifetime)

So maybe it's not the best reason for VIRTUAL columns being allowed as PKs,
but that's my $0.02. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

2019-10-30 Thread Dominique Devienne
On Wed, Oct 30, 2019 at 11:26 AM Richard Hipp  wrote:

> On 10/29/19, Keith Medcalf  wrote:
> >
> > Before you change anything, I think that is incorrect for the various
> > datetime functions.  I think they SHOULD be permitted in CHECK
> constraints
> > and in generated always columns, whether those are stored or virtual,
> > whether or not parameters are given since they are constant.  They should
> > not be permitted in indexes however unless they are pure (deterministic).
>
> i believe the purpose of a CHECK constraint is declare an eternal
> truth about the database content, not merely something that was true
> at the point in time when the content was first inserted.  Am I wrong?
>

The kind of CHECK constraint Keith mentioned would run afoul of
https://www.sqlite.org/pragma.html#pragma_integrity_check so you're
probably right
that allowing these non-deterministic function is not a good idea (on 2nd
thought...) --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

2019-10-30 Thread Dominique Devienne
On Wed, Oct 30, 2019 at 11:26 AM Richard Hipp  wrote:

> On 10/29/19, Keith Medcalf  wrote:
> >
> > Before you change anything, I think that is incorrect for the various
> > datetime functions.  I think they SHOULD be permitted in CHECK
> constraints
> > and in generated always columns, whether those are stored or virtual,
> > whether or not parameters are given since they are constant.  They should
> > not be permitted in indexes however unless they are pure (deterministic).
>
> i believe the purpose of a CHECK constraint is declare an eternal
> truth about the database content, not merely something that was true
> at the point in time when the content was first inserted.  Am I wrong?
>

That makes sense too, indeed. Basically an "invariant" as we often say.

Yet I see Keith's use-cases as compelling enough to leave that decision to
the user,
rather than the implementation making it for us. Or perhaps you think
triggers should
be used instead, for those use cases? --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

2019-10-30 Thread Dominique Devienne
On Wed, Oct 30, 2019 at 8:32 AM Keith Medcalf  wrote:

> On Tuesday, 29 October, 2019 23:05, Simon Slavin 
> wrote:
> >On 30 Oct 2019, at 3:58am, Keith Medcalf  wrote:
>
> >> Before you change anything, I think that is incorrect for the various
> >> datetime functions.  I think they SHOULD be permitted in CHECK
> >> constraints and in generated always columns, whether those are stored or
> >> virtual, whether or not parameters are given since they are constant.
> >> They should not be permitted in indexes however unless they are pure
> >> (deterministic).
>
> > But can SQLite tell the difference at that stage ?  For instance,
>
> Personally, I do not see a problem with permitting them to be used
> anywhere EXCEPT in an index expression.
>

+1. I completely agree with Keith, and the use-cases he outlined are great
examples of enforcing business logic using CHECK constraints.

I especially like the fact one cannot alter these stored generated values,
again adding a level of integrity to the DB. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Backward compatibility

2019-10-29 Thread Dominique Devienne
On Tue, Oct 29, 2019 at 8:38 AM Thomas Kurz  wrote:

> We recently had a discussion about date/time support, but also other
> suggestions, which sooner or later end up at the point "cannot be done,
> would break backward compatibility". (See also: "Backward compatibility vs.
> new features (was: Re: dates, times and R)")
>
> I'm always curious and monitoring trunk development, and now I have read
> in the draft release notes for 3.31:
>
> > If an earlier version of SQLite attempts to read a database file that
> contains a generated column in its schema, then that earlier version will
> perceive the generated column syntax as an error and will report that the
> database schema is corrupt.
>
> ...which leads me to some confusion. So there actually *are* features
> breaking compability? Wouldn't that be a chance for full date/time support
> as well?
>

To complement Graham's answer, I see 3 different levels of backward
compatibility:

1) file format: https://www.sqlite.org/fileformat.html
2) SQLite C API: https://www.sqlite.org/capi3ref.html
3) SQL language: https://www.sqlite.org/lang.html (including the built-in
SQL functions)

Anything that breaks #1 or #2 is dead on arrival basically.
SQLite4 was a brief attempt at #1, but it fizzled out.

OTOH, #3 keeps adding new features over time, because as long as you don't
use those new features, older versions of SQLite can read/process the DB
files just fine.
This is more "forward-compatibility" in a sense, when older SQLite versions
can or cannot read DB files written using newer versions of SQLite.
While "backward-compatibility" is the latest version of SQLite reading
older SQLite's DB files (mostly about #1 and #2).

Your DB files are as forward-compatible as you choose them to be, depending
on the new SQL features you use or not.
I didn't follow the date/time thread closely enough to see where the
proposed changes fall in the above categorization.
I hope this helps. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Conflict between snapshots and checkpoints

2019-09-30 Thread Dominique Devienne
On Mon, Sep 30, 2019 at 2:07 PM Keith Medcalf  wrote:

> On Monday, 30 September, 2019 02:06, Dominique Devienne <
> ddevie...@gmail.com> wrote:
> >On Sun, Sep 29, 2019 at 2:13 PM Keith Medcalf 
> wrote:
> >> On Sunday, 29 September, 2019 01:28, Gwendal Roué <
> gwendal.r...@gmail.com> wrote:
>
> >> >Those N reader connections allow concurrent database reads. Those
> >> "reads" are generally wrapped in a deferred transaction which provides
> >> snapshot isolation.
>
> >> No, it provides REPEATABLE-READ isolation.  There is no actual
> >> "snapshot" taken and no snapshot exists.
>
> > You are merely not seeing data written to the WAL transaction log at a
> > point-in-time subsequent to the point in time at which you commenced
> > the "repeatable-read".
>
> >I don't see where you are going with this Keith.
> >Repeatable-reads "in the past" *are* snapshots IMHO.
>
> Generally no.  "Snapshot Isolation" does not really exist, though some
> RDBMS have created it to permit more opportunistic updates.  Reads are at
> the Repeatable-Read isolation level (both for WAL and DELETE journal
> modes), and writes are Serialized.  Snapshot Isolation is an invention of
> the MVCC folks to theoretically permit greater update concurrency at the
> expense of serializable isolation and introduces anomalies into the
> database read and write processing that cannot occur when the updates are
> serialized, and which generally requires the application programmer to take
> extra steps to ensure database consistency.
>
> >The WAL file *does* contain enough information combined with the main
> >database file pages, to logically *and* physically represent a "snapshot"
> >of the DB at that point-in-time.
>
> For the purposes of reading only yes, I suppose you could call it a
> "snapshot", except that it isn't.  It is just a point-in-time
> repeatable-read.  You can only upgrade a transaction from read to write if
> you are holding the "top" snapshot (that is, you must be seeing the entire
> database, not a point-in-time version of it).
>
> >So not calling it a "snapshot" is a stretch at the very least. What is a
> "snapshot"
> >according to you, if that's not it?
>
> Snapshot Isolation is implemented by a bunch of different databases that
> do not conform to the SQL Standard and it introduces anomalies into the
> update process that cannot be introduced when using Serializable
> Isolation.
>
> https://en.wikipedia.org/wiki/Isolation_(database_systems)
> https://en.wikipedia.org/wiki/Snapshot_isolation
>
> >And also why do you think Richard, who knows a thing or two about
> >databases, called these API *snaphot* then?
>
> Because it is a convenient descriptor, perhaps?  They may in fact be
> considered to be a snapshot of the database as it existed at some point in
> the past (without full view of all committed transactions) however only the
> "top" snapshot, the one that has a view of all committed transactions is
> permitted to update/write to the database.
>
> >I'm genuinely curious here. I think I disagree with you, but most time I
> >do, I'm wrong, so I'd like to understand, really. --DD
>
> The isolation is either repeatable-read for read transactions, or
> serializable for writes.  It is not Snapshot Isolation.
> So although one may consider that what you are looking at is a "snapshot"
> of the database that existed at a particular point-in-time, it should not
> be confused with "snapshot isolation" which is an entirely different beast
> altogether.
>

So I guess our main difference here, is that I have no qualms at all with
point-in-time *read-only* repeatable-read transaction being called a
"Snapshot".
MVCC (which to me is synonymous with snapshots) is more about
read-consistency across statements (i.e. a read transaction) that does
*not* prevent writes.
Using snapshots, I can parallelize access to several tables across
connections (and threads), ensuring read-consistency in several separate
transactions and connections.
I used to do that in Oracle, and I'm glad that I can try to do it in SQLite
too now. There are caveats of course, like controlling checkpointing, but I
can live with that. --DD

PS: Note that I never used "Snapshot *Isolation*" myself. For me, Snapshot
= point-in-time read-consistency.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Conflict between snapshots and checkpoints

2019-09-30 Thread Dominique Devienne
On Sun, Sep 29, 2019 at 2:13 PM Keith Medcalf  wrote:

> On Sunday, 29 September, 2019 01:28, Gwendal Roué 
> wrote:
> >Those N reader connections allow concurrent database reads. Those "reads"
> are
> >generally wrapped in a deferred transaction which provides snapshot
> >isolation.
>
> No, it provides REPEATABLE-READ isolation.  There is no actual "snapshot"
> taken and no snapshot exists.

You are merely not seeing data written to the WAL transaction log at a
> point-in-time subsequent to the point in time at which you commenced the
> "repeatable-read".
>

I don't see where you are going with this Keith.
Repeatable-reads "in the past" *are* snapshots IMHO.

The WAL file *does* contain enough information combined with the main
database file pages,
to logically *and* physically represent a "snapshot" of the DB at that
point-in-time. So not calling
it a "snapshot" is a stretch at the very least. What is a "snapshot"
according to you, if that's not it?

And also why do you think Richard, who knows a thing or two about
databases, called these API *snaphot* then?

I'm genuinely curious here. I think I disagree with you, but most time I
do, I'm wrong, so I'd like to understand, really. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not work as expected

2019-09-23 Thread Dominique Devienne
On Sat, Sep 21, 2019 at 10:17 PM Fredrik Larsen  wrote:

> [...] But fixing issues in less than a day of reporting? [...]
>

That's not unusual at all for SQLite. Either it gets "fixed" quickly, or it
doesn't.

The hard part is making the case with Richard (and Dan) about the merit of
the
change, especially if it's not a bug-fix like here, but a missed
optimization. They
care a lot about performance, and do try to respond to community input, even
though they participate very little in this ML in such request-for-changes
threads.

If we named SQLite optimizations, that new one would be the Fredrik's :).
--DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not work as expected

2019-09-20 Thread Dominique Devienne
On Fri, Sep 20, 2019 at 12:33 PM Hick Gunter  wrote:

> The dialogue from the stackoverflow discussion shows this quite clearly.
>

Shows what clearly Gunter? I'm not sure to follow. I've read the SO post,
and I don't get your point.

We can observe GROUP BY works ASCending only as of now. Why it can't work
DESCending to avoid ordering,
that's a different question. From https://www.sqlite.org/lang_select.html we
can observe that GROUP BY takes an
expr on the RHS, while ORDER BY takes an expr followed by optional COLLATE
and ASC/DESC terms.

So given an GROUP BY expr followed by an ORDER BY with the same expr,
"pushing up" the ordering's
COLLATE and ASC/DESC terms on the GROUP BY itself, eliminates the need for
the separate ordering step.
That's a clear win in runtime performance. Hopefully SQLite can do that
sooner rather than later. Thanks, --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not work as expected

2019-09-20 Thread Dominique Devienne
On Thu, Sep 19, 2019 at 6:15 PM Hick Gunter  wrote:

> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Fredrik Larsen
> Gesendet: Donnerstag, 19. September 2019 17:29
> An: SQLite mailing list 
> Betreff: Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not work
> as expected
> ...
> Hick; ORDER BY x DESC >is< covered by index. Btree-indexes allows
> traversal both ways. You can see this if you remove GROUP_BY.
> ...
> True and nothing new, but not the point.
>
> After doing GROUP BY x over the covering index, the result rows would be
> returned by x ASC.

There is no index on the rowset returned by the GROUP BY, as the rows only
> exist one at a time.

Therefore, the only way to get them into ORDER BY X DESC is to sort them.
>

But who says the GROUP BY must return rows in ASCending order?

A lot of us "oldies" of this ML well know the order is arbitrary and
subject to change w/o an explicit ORDER BY.
So the GROUP BY is allowed, AFAIK, to return rows in DESCending order just
the same. And to do so efficiently
as Fredrik points out, since indexes (or indices, I never know) work
equally well in both directions. In fact, it could
return rows in arbitrary / random order too!

The query-planner does see the ORDER BY that follows the GROUP BY after
all, so it could well decide
to group in DESCending order, thus avoiding the ordering completely, like
it already does for ASCending.
This would be a great optimisation, and from 30,000ft, it does indeed seem
like a "simple" one compared
to all the advanced optimisations already implements, as Fredrik mentioned.

I might even say that it looks like a "low-hanging-fruit", if I dared :).
Dunno, perhaps GROUP BY has some
requirement an ordering, or GROUP BY impls somehow can't easily work "in
reverse", I'm no expert of the
code. I wish the experts would chime in. Too often we never hear any
rational for doing or not doing things.
This is a "users" list and there's no "dev" list. I wish more was shared
about the internal structures, impls,
etc... explaining why something is harder to implement that it sounds. Oh
well... --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Undo sqlite3_snapshot_open?

2019-09-19 Thread Dominique Devienne
On Thu, Sep 19, 2019 at 1:13 PM Gwendal Roué  wrote:

> I am looking at the snapshot experimental APIs
>

How long do experimental APIs remain experimental?

Snapshot is over 3.75 years old now. Will it ever graduate to a fully
supported API?

As far as I understood the doc, a snapshot remains valid only as long as
the WAL file is not checkpointed.
Which imply checkpoint is allowed to invalidate any snapshots, while I can
see where the reverse would
be preferred, i.e. active snapshots prevent the full checkpointing of the
WAL file. Is there any way to know
about snapshots, so the connection in charge of checkpointing does not
clobber them? Thanks, --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] The best way to check if a file is a sqlite3 DB file?

2019-09-19 Thread Dominique Devienne
On Thu, Sep 19, 2019 at 10:20 AM Rowan Worth  wrote:

> On Thu, 19 Sep 2019 at 16:03, Dominique Devienne 
> > On Wed, Sep 18, 2019 at 6:43 PM Clemens Ladisch 
> > > Peng Yu wrote:
> > > > Is there a better way to just return an exit status of 0 for
> > > > a sqlite3 DB file and 1 otherwise?
>
> > >   dd bs=16 count=1 < some.db > sqlite3-signature
> > >   cmp --bytes=16 sqlite3-signature /tmp/tmp.erZ5aS6PUX.sqa > /dev/null
> > >   [ $? = 0 ] && echo SQLite DB
> >
> >
> > I'm actually surprised sqlite3[.exe] itself doesn't have a more to do
> that.
>
> As usual, sqlite doesn't touch the DB file until it is asked to. Try
> "sqlite3 FILENAME 'pragma schema_version'" on some random file and you'll
> get "Error: file is encrypted or is not a database". But note that trying
> the same on a non-existent file will succeed, and additionally create an
> empty file.
>

Thanks. Good tip. Need double not single quotes on Windows. And error code
of 26 if a bit weird,
but this works well otherwise:

D:\>sqlite3 TypedEntity.h 'pragma schema_version'
Error: unrecognized token: "'pragma"

D:\>sqlite3 TypedEntity.h "pragma schema_version"
Error: file is not a database

D:\>echo %ERRORLEVEL%
26

D:\>sqlite3 SOME.db "pragma schema_version"
58

D:\>echo %ERRORLEVEL%
0


> > You'd think sqlite3[.exe] is the best suited to figure out if a file is a
> > valie SQLite database or not,
>
> It still is: sqlite3 FILENAME 'pragma integrity_check'
>
> Parsing the header doesn't tell you whether the DB is valid, but if that's
> all you want to do I suggest the ubiquitous file(1) command which reports
> "SQLite 3.x database" for a [non-empty] sqlite db file.
>

I'm well aware of that. We were discussing an alternative to the *nix file
command,
and integrity_check goes way beyond (and is way slower) than checkinga file
type.
You might as well through FK checks with foreign_key_check if you are going
there :).

pragma quick_check would be more appropriate, if one wants to go the extra
mile, w/o slowing things down too much.

But again, thanks for this tip. Good one. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] The best way to check if a file is a sqlite3 DB file?

2019-09-19 Thread Dominique Devienne
On Wed, Sep 18, 2019 at 6:43 PM Clemens Ladisch  wrote:

> Peng Yu wrote:
> > Is there a better way to just return an exit status of 0 for
> > a sqlite3 DB file and 1 otherwise?
>
> Extract the magic header string from a known DB file:
>
>   dd bs=16 count=1 < some.db > sqlite3-signature
>
> Then you can compare it against the beginning of the file:
>
>   cmp --bytes=16 sqlite3-signature /tmp/tmp.erZ5aS6PUX.sqa > /dev/null
>   [ $? = 0 ] && echo SQLite DB


I'm actually surprised sqlite3[.exe] itself doesn't have a more to do that.
I tried using it to open a non-DB file, and it opens in interactive mode,
with
no error or warning, wether I use -bail or not. I was expecting a hard
error.

You'd think sqlite3[.exe] is the best suited to figure out if a file is a
valie SQLite database or not,
and exit with a non-zero status if it's not (possibly using a given CLI arg
for that behavior). That
would be a useful addition, if it's not already possible in a way I can't
find. Parsing the 100 bytes
header would do and be super fast. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [EXTERNAL] Re: insert: how to force application to provide value for int primary key?

2019-09-10 Thread Dominique Devienne
On Tue, Sep 10, 2019 at 5:07 PM Hick Gunter  wrote:

> So it really depends on the order of adding records more than the presence
> or absence of a rowid.
>

True. I'm making the conjecture that w/ rowid tables tend to be ordered
(via implicit or explicit integer auto-increment rowids), while w/o rowid
tables
tend to be on the random load side. Thanks for your insights. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Why aren't there date/time parsing built-in functions in SQLite

2019-09-10 Thread Dominique Devienne
On Tue, Sep 10, 2019 at 4:35 PM Igor Tandetnik  wrote:

> On 9/10/2019 7:05 AM, Keith Medcalf wrote:
> > select value,
> > round((julianday(value) - julianday('00:00:00')) * 86400.0, 3)
> >from test;
>
> Another possibility: strftime('%s', '1970-01-01 ' || value)
>

I ended up needing %f, an implicit cast via 0.0+, and using Keith's trick
of time(..., 'unixepoch') to get the correct result. Thank you both. --DD

PS: Still... Dealing with time/date/duration shouldn't be a puzzle/quiz
like this IMHO.

D:\pdgm>sqlite3 time-elapsed-dxo.db
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
sqlite> create table dur(id number primary key, elapsed text not null);
sqlite> .mode csv
sqlite> .import time-elapsed-dxo.txt dur
sqlite> .mode col
sqlite> .header on
sqlite> select count(*) from dur;
count(*)
--
158
sqlite> select * from dur limit 5;
id  elapsed
--  ---
1   00:00:02.68
14  00:00:00.78
12  00:00:02.31
4   00:00:06.36
5   00:00:08.01
sqlite> select *, strftime('%f', '1970-01-01 ' || elapsed) from dur limit 5;
id  elapsed  strftime('%f', '1970-01-01 ' || elapsed)
--  ---  
1   00:00:02.68  02.680
14  00:00:00.78  00.780
12  00:00:02.31  02.310
4   00:00:06.36  06.360
5   00:00:08.01  08.010
sqlite> select *, 0.0+strftime('%f', '1970-01-01 ' || elapsed) from dur
limit 1;
id  elapsed  0.0+strftime('%f', '1970-01-01 ' || elapsed)
--  ---  
1   00:00:02.68  2.68
sqlite> select *, typeof(0.0+strftime('%f', '1970-01-01 ' || elapsed)) from
dur limit 1;
id  elapsed  typeof(0.0+strftime('%f', '1970-01-01 ' ||
elapsed))
--  ---
 
1   00:00:02.68  real
sqlite> select sum(0.0+strftime('%f', '1970-01-01 ' || elapsed)) from dur;
sum(0.0+strftime('%f', '1970-01-01 ' || elapsed))
-
4038.85
sqlite> select time(sum(0.0+strftime('%f', '1970-01-01 ' || elapsed))) from
dur;
time(sum(0.0+strftime('%f', '1970-01-01 ' || elapsed)))
---
08:24:00
sqlite> select time(sum(0.0+strftime('%f', '1970-01-01 ' || elapsed)),
'unixepoch') from dur;
time(sum(0.0+strftime('%f', '1970-01-01 ' || elapsed)), 'unixepoch')

01:07:18
sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] insert: how to force application to provide value for int primary key?

2019-09-10 Thread Dominique Devienne
On Tue, Sep 10, 2019 at 4:32 PM R Smith  wrote:

> > So "fat" tables, with large rows, and lost of inserts/updates, is
> basically
> > the worse case
> > scenario for such WITHOUT ROWID tables. It works, no issue there, and as
> > typical of
> > SQLite is often fast enough for most DB sizes, but it *can* matter. Just
> be
> > aware of it.
>
> That is interesting - could you elaborate on how exactly lots of inserts
> would be worse in WITHOUT_ROWID tables than in normal tables?*
>

WITHOUT ROWID tables have a "real" natural key, which as such is much
more likely to have a random distribution, resulting in splicing new rows
all
over the place. While regular tables typically have an auto-incrementing
ROWID,
acting as the B-Tree key, which means new rows are mostly inserted "at the
end",
in the last page, yielding fewer non-leaf page rewrites I'm guessing.

You're probably right to challenge what I wrote. It's mostly intuition, not
hard-facts,
so I could well be completely off-base. I may also have read the above when
I looked
into Oracle IOTs (Index Organized Tables), which are similar I think
(again, perhaps
I'm wrong).

I guess one would need to run experiments with a shim VFS to track IO to
verify
my claims above :). For now, just take it with a grain of salt or just
plain assume
it was talking out of my a..! --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] insert: how to force application to provide value for int primary key?

2019-09-10 Thread Dominique Devienne
On Tue, Sep 10, 2019 at 2:09 PM Marek Wieckowski  wrote:

> Yes, indeed works. Great, thank you!
>

Note though that it has performance implications perhaps.

This changes to physical structure of the table, to be stored as an index
basically.

So if you do lots of insertions "in the middle", you could have "write
amplifications".
WITHOUT ROWID tables are ideal for read-mostly tables, and function as-if
the
table is an index that covers all columns. So there's no need for separate
IO for the
index (to lookup a PK) then to its table (to lookup the actual row), it's
just one IO.

It's no different from updating an index, except indexes are typically
"skinnier" (have
fewer and smaller columns) than their corresponding tables, limiting the
impact.

So "fat" tables, with large rows, and lost of inserts/updates, is basically
the worse case
scenario for such WITHOUT ROWID tables. It works, no issue there, and as
typical of
SQLite is often fast enough for most DB sizes, but it *can* matter. Just be
aware of it.

My $0.02. --DD

https://www.sqlite.org/withoutrowid.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] insert: how to force application to provide value for int primary key?

2019-09-10 Thread Dominique Devienne
On Tue, Sep 10, 2019 at 2:20 PM Jose Isaias Cabrera 
wrote:

> Marek Wieckowski, on Tuesday, September 10, 2019 08:08 AM, wrote...
> > > Make it a WITHOUT ROWID table:
> > >
> > >   CREATE TABLE example_table   (
> > >id INTEGER PRIMARY KEY,
> > >description TEXT NOT NULL
> > >) WITHOUT ROWID;
>
> Will someone point me to a spot where I can understand this piece of SQL?
> Or, a simple explanation would do.  Thanks.
>

https://www.sqlite.org/withoutrowid.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] FW: Why aren't there date/time parsing built-in functions in SQLite

2019-09-10 Thread Dominique Devienne
On Tue, Sep 10, 2019 at 1:27 PM Keith Medcalf  wrote:

> Also, note that you have to use the 'unixepoch' modifier with the time
> function so that it knows the value is seconds,

not days, since floats are by default days and integers are by default
> seconds. [...]


In my quick reading of the doc [1], I didn't pickup any such mention. Is it
even there?

The 'unixepoch' modifier tells the internal datetime functions that the
> provided value is relative to the unix epoch in seconds, rather than the
> julian epoch in days.

I don't think there is a modifier to force the days from the julian epoch
> interpretation.
>

See above. Not super-clear from the doc.

As DRH mentioned recently about a different piece of doc, I suspect that
doc hasn't been updated in years,
and could use some attention IMHO. It's not specified what the various
functions return in terms of types for
example. It reads more like a terse user manual than reference
documentation. Note sure how to make it more
approachable exactly, but it seems hard to grasp exactly what's going on,
at least to me. FWIW. --DD

[1] https://www.sqlite.org/lang_datefunc.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Why aren't there date/time parsing built-in functions in SQLite

2019-09-10 Thread Dominique Devienne
On Tue, Sep 10, 2019 at 1:05 PM Keith Medcalf  wrote:

> insert into test values ('00:00:07.86');
> select value,
>round((julianday(value) - julianday('00:00:00')) * 86400.0, 3)
>   from test;
>

Thanks! As I guessed, I was indeed missing something.
But IMHO that something is definitely not obvious or straightforward though.

I still think a strptime()-like function to parse according to a format a
text date/time would be much more obvious.
With modifiers specifying the output units, no need to subtract and
multiply. But that's just me I guess.

You are limited to "value" between 00:00:00.000 and 23:59:59.999 since the
> internal datetime only stores julian milliseconds.  Note that the default
> date if you do not provide that part is 2000-01-01
>

Indeed, this works here, but if you have a duration with a number of hours
that exceeds 24h, or one measured in M:SS:FF (F for fractional seconds)
where M exceeds 60min, etc...
what do you do them? Back to extracting components via text manipulations
and doing your own math?
Seems to me one needs to be a super expert like you Keith to do these
things, when it ought to be simpler, no? --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Enhancement request: scanf built-in function

2019-09-10 Thread Dominique Devienne
In the same vein as my previous post about lack of function acting as the
"opposite" of  strftime(),
I'm wondering if it's not time to have a scanf() function as the opposite
of printf().

In the olden days, SQLite didn't have table-valued eponymous-vtable
"functions", so there
was a technical reaon for having no scanf(). But nowadays, nothing prevents
it, and it would
be quite useful. Unless such vtable-functions need to have a fixed number
of returned columns?

To come back on that same text duration example, I thinking of something
like this:

create table vs (id number, elapsed text);
select vs.id, vs.elapsed, f."$1"*(60*60)+f."$2"*60+f."$3"+(f."$4"/100.0) as
sec
  from vs, scanf('%d:%d:%d.%d', elapsed) as f

or perhaps if one could "name" the returned "tuple"

create table vs (id number, elapsed text);
select vs.id, vs.elapsed, f.hh*(60*60)+f.mm*60+f.ss+(f.cs/100.0) as sec
  from vs, scanf('%d:%d:%d.%d', elapsed, 'hh', 'mm', 'ss', 'cs') as f

If as I fear, vtables need a "fixed" structure (columns and names),
the only solution would be to return an array-type (a-la PostgreSQL),
and/or have a small "protocol" to unpack elements from a tuple-like value.
That's possible and efficient thanks to type/subtype and/or pointer types.

Thoughts? --DD

PS: Of course, in my use case, a more specialized strptime() [1] would be
better IMHO.
It's just that it made me think about printf/scanf, thus this post.

[1] https://pubs.opengroup.org/onlinepubs/009695399/functions/strptime.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Why aren't there date/time parsing built-in functions in SQLite

2019-09-10 Thread Dominique Devienne
Hi,

There are functions to format numbers as text times and/or dates,
but I can't find equivalent ones to parse those text times/dates back to
numbers.

I wanted to sum durations expressed as HH:MM:SS.SS strings, and I was
expecting
a function parsing such a string into a number of seconds for example, but
couldn't
find one. Sure you can take apart the string with substr() and cast then do
the math
easily via verbose SQL, but why?

I ended up massaging those text durations as CSV in VIM to decompose them
and
then did what's below, but my question is really why the "reverse" of
strftime()
is not part of SQLite itself? Seems to me so "natural" it should be, I
wonder if I'm
not missing some obvious way to do this more easily with SQLite?

Thanks, --DD

sqlite> create table vs (id number primary key, hh, mm, ss, cs);
sqlite> .mode csv
sqlite> .import time-elapsed.txt vs
sqlite> .mode col
sqlite> .header on
sqlite> select * from vs limit 10;
id  hh  mm  ss  cs
--  --  --  --  --
1   00  00  02  68
14  00  00  00  78
12  00  00  02  31
4   00  00  06  36
5   00  00  08  01
8   00  00  09  36
9   00  00  09  79
11  00  00  13  62
10  00  00  17  50
33  00  00  07  86
sqlite> select id, hh||':'||mm||':'||ss||'.'||cs as "elapsed",
cast(hh*(60*60)+mm*(60)+ss as number)+cs/100.0 from vs limit 10;
id  elapsed  cast(hh*(60*60)+mm*(60)+ss as number)+cs/100.0
--  ---  --
1   00:00:02.68  2.68
14  00:00:00.78  0.78
12  00:00:02.31  2.31
4   00:00:06.36  6.36
5   00:00:08.01  8.01
8   00:00:09.36  9.36
9   00:00:09.79  9.79
11  00:00:13.62  13.62
10  00:00:17.50  17.5
33  00:00:07.86  7.86
sqlite> select id, hh||':'||mm||':'||ss||'.'||cs as "elapsed",
hh*(60*60)+mm*(60)+ss+cs/100.0 from vs limit 10;
id  elapsed  hh*(60*60)+mm*(60)+ss+cs/100.0
--  ---  --
1   00:00:02.68  2.68
14  00:00:00.78  0.78
12  00:00:02.31  2.31
4   00:00:06.36  6.36
5   00:00:08.01  8.01
8   00:00:09.36  9.36
9   00:00:09.79  9.79
11  00:00:13.62  13.62
10  00:00:17.50  17.5
33  00:00:07.86  7.86
sqlite> select sum(hh*(60*60)+mm*(60)+ss+cs/100.0) as total from vs;
total
--
7338.85
sqlite> select time(sum(hh*(60*60)+mm*(60)+ss+cs/100.0)) as total from vs;
total
--
08:24:00
sqlite> select max(hh*(60*60)+mm*(60)+ss+cs/100.0) as total from vs;
total
--
211.95
sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] INSERT vs BEGIN

2019-09-03 Thread Dominique Devienne
On Tue, Sep 3, 2019 at 12:03 PM Rob Richardson 
wrote:

> I didn't know it is possible to insert multiple rows into a table using a
> command like this.


Added over 7 years ago: See
https://www.sqlite.org/changes.html#version_3_7_11 #1


> Is this just an SQLite feature, or is this part of the SQL standard?


I suspect it's non-standard, since Oracle does not support it. But it's
just a guess on my part. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] INSERT vs BEGIN

2019-09-02 Thread Dominique Devienne
On Mon, Sep 2, 2019 at 12:52 PM Simon Slavin  wrote:

> > One must know that I am obliged to use "BEGIN DEFERRED TRANSACTION"
> because others threads needs to access to tables.
> SQLite copes very well when you have one connection writing to the
> database and other connections reading.  The problems come when you have
> two connections writing to the database at once.
>

In WAL mode only! Otherwise readers are blocked when the writer is active,
and readers prevent the writer from proceeding. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] INSERT vs BEGIN

2019-09-02 Thread Dominique Devienne
On Mon, Sep 2, 2019 at 12:04 PM Grincheux <51...@protonmail.ch> wrote:

> What is the best ?
>
> INSERT INTO artists (name) VALUES
> ("Gene Vincent") ...
> ("Moi _ Me");
>

You're missing commas.
And you should not use double-quotes but single-quotes for string-literals.


> I want to insert 1 000 000 records.
> The other manner tot do is creating a transaction with one insert command
> by line.
>

In all cases, you should have as few transactions as possible IMHO,
typically a single one.

Whether to have one statement per-row or one-statement for multiple-rows is
separate from transactions.
The multi-row insert statement might hit the parser limit if too large.
I've converted in the past a very large
insert-per-row SQL file, to one-insert-per-table (for all that table's row)
and ran into that limit. So I gave up,
since hard to know how many rows to put per statement.

And as Gunter wrote, SQLite will need to parse each statement in full in
memory, so the larger the statement
the more memory used.

If you're doing massive inserts from text files, maybe from a CSV rather
than SQL file might be faster.
You're trading one parser (SQL) for another (CSV), but since DRH wrote
both, and CSV is significantly
simpler than SQL (for parsing), it's possible CSV might have an edge. But
that remains to be seen.


> My question is what is the best thing to do ?
> Or having a transaction for the first sample?
>
> One must know that I am obliged to use "BEGIN DEFERRED TRANSACTION" because
> others threads needs to access to tables.
>

Unless you're using WAL, other threads will be blocked during inserts,
whether you use a
single transactions, or not. They might be able to "sneak-in" betweeb
inserts TX if using multiple TXs,
but then you might block the "inserter".


> Please help me.
>

Programmatically, prepare() and bind() as Gunter wrote again. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [EXTERNAL] char(0) with SQLite

2019-09-02 Thread Dominique Devienne
On Mon, Sep 2, 2019 at 12:12 PM Hick Gunter  wrote:

> Dimensions are ignored by SQLite. A field defined CHAR(0) can hold any
> length (up to the internal limit) of string. SQlite will only store the
> actual length of the string plus its contents, no space  is wasted.


And Gunter wrote "string" above, but SQLite will also hold an integer, or
real, or blob value in that row/col (i.e. cell) just as well :) --DD

PS: Note that if you use the text type for your password, note that the
text is expected to be UTF-8.
If you users use accented characters in an 8-bit encoding, and you do not
properly convert that
into UTF-8, this could perhaps create issues (although if you take that
UTF-8, and manipulate it
assuming the same 8-bit encoding, SQLite should give you back the same
bytes, it doesn't do
UTF-8 validation AFAIR). Otherwise use a blob and conventions/assumptions
in your app.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [EXTERNAL] Re: http://roaringbitmap.org/

2019-09-02 Thread Dominique Devienne
On Mon, Sep 2, 2019 at 12:08 PM Hick Gunter  wrote:

> Back in 2011 I implemented a virtual table using the "fastbit" library by
> John Wu of the Lawrence Berekely National Laboratory. This allowed selects
> of the form
>
> SELECT ... FROM  WHERE rowid IN (SELECT rowid FROM
>  WHERE );
>

Did it work well? Did you get any speedup compared to a normal BTree index?
Available anywhere?
How low the cardinality of indexed columns value-space needs to be to
benefit from a bitmap index?


> provided that the data had been inserted before by running
>
> INSERT INTO  SELECT rowid,;


Custom (user-defined) indexes is an area that I'd welcome in SQLite. You
can work around it
as you did above, but that implies the index maintenance rests on the
user's shoulders. While
it would be relatively easy I suspect for SQLite core to notify a custom
index of table changes.

Conversely, you can't use SQLite (sole for now) BTree indexes with a
virtual table, AFAIK,
(I have a doubt all of a sudden...), the vtable must do all the indexing
itself.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] http://roaringbitmap.org/

2019-09-02 Thread Dominique Devienne
On Mon, Sep 2, 2019 at 8:06 AM Robert M. Münch 
wrote:

> Hi, I think that SQLite use some bitmap indexes


Not that I know of, but I don't know the full source code. Maybe FTS[345]
do/es,
but SQLite itself only uses BTree-indexes AFAIK.


> and this here might be of interest if not already used/known:
> http://roaringbitmap.org/ I think it’s from the same guy how did SIMDJSON.
>

Thanks for sharing. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Tracking item history using SQLite

2019-09-02 Thread Dominique Devienne
On Sat, Aug 31, 2019 at 12:24 PM Rob Willett 
wrote:

> 5. SQLite seems to be able to do anything we want it to. [...]
> Other people seem worried about the 'lack' of some datatypes, we do
> masses of data and date conversations as needed and it's never been a
> speed issue or any issue.


 (since I'm often one of those "other people", I feel compelled to reply to
that one)

As Keith wrote above in this thread, it's all about "integrity", and why
I'd want more datatypes in SQLite.

An integer column (e.g. number of seconds since Epoc, or gregorian days, or
else) or a
text column (e.g. RFC XYZ datetime, local-TZ or not) says nothing about
that column, and certainly
does not enforce anything by itself. Rare are the people actually adding
CHECK constraints to enforce those.
So having more specialized datatypes provides more semantic information in
the schema itself,
and that a good thing, a very good thing indeed.

Of course you can do anything with SQLite despite that, or the "flexible
typing" dear to DRH,
just like you can code anything in a duck-typing scripting language like
you can in a statically
typed language. But more typing does help in the long run IMHO, and is very
valuable. FWIW... --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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

2019-08-29 Thread Dominique Devienne
On Thu, Aug 29, 2019 at 2:35 PM Jose Isaias Cabrera 
wrote:

> Free Ekanayaka, on Thursday, August 29, 2019 06:40 AM, wrote...
> > See https://dqlite.io for more details.
>
> Can dsqlite be installed on Windows?  I went to the site, read the
> README.md file, and could not find any reference of it.  I can see the
>
> $ sudo add-apt-repository -y ppa:dqlite/v1 && sudo apt install dqlite
>
> which lets me know that it linux/unix based.  But, is Windows an option
> also?  Thanks.
>

Ditto. Wasn't clear is Windows was supported.
But I'd add whether dqlite could replicate across platforms as well.
SQLite DB files are portable across platforms and architectures.
So can dqlite replicate across Windows, Linux, OSX, etc???

Also, does it come in easy-to-digest amalgamation?
Especially since it requires a patch to SQLite, a portable
C-amalgamation source distribution form would maximize
changes of it being tried out.

Perhaps OT, but would the hooks added to the WAL-journaling
ever be up-streamed by Richard, under a build flag for example?
Without it, that's basically "forking" SQLite, which is not ideal... --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] strip off file metadata in sqlar

2019-08-28 Thread Dominique Devienne
On Tue, Aug 27, 2019 at 11:46 PM Peng Yu  wrote:

> I just need the return status of sqldiff (as `cmp -s`). Is there an
> option to suppress all the screen output? Thanks.
>

https://www.sqlite.org/cgi/src/artifact/7b9b7238284f0213

Doesn't look like it does. But redirecting to /dev/null or NUL would do.
Only problem is there are only two returns in main(), both returning 0;

But I guess the output of sqldiff is empty (or deterministic?) is the same,
so that output can be processed by something.

Adding a mode to sqldiff to return 0 on same or 1 on different should not
be difficult though. Adding short-circuiting to make it fast might be a
little harder. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] database like file archive

2019-08-27 Thread Dominique Devienne
https://sqlite.org/sqlar/doc/trunk/README.md

On Tue, Aug 27, 2019 at 3:57 PM Peng Yu  wrote:

> Hi,
>
> I haven't found an archive format that allows in-place delete (I know
> that .zip, .7z and .tar don't). This means that whenever delete is
> needed, the original archive must be copied first. This can be
> problematic when the archive is large and the file to delete is small.
>
> Something along the line of the ability of sqlite3 to perform in-place
> delete might be a useful feature for archives. But I haven't found any
> such archive format. Does anybody know one? Thanks.
>
> --
> Regards,
> Peng
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Query for Many to Many

2019-08-27 Thread Dominique Devienne
On Tue, Aug 27, 2019 at 4:00 PM Dominique Devienne 
wrote:

> select author.*, books.*
>   from author_books
>   join author on author.author_id  = author_books.author_id
>   join books  on books.book_isbn   = author_books.book_isbn
>

Which can also be written:

select author.*, books.*
  from author_books
  join author using author_id
  join books  using book_isbn

Or even:

select author.*, books.*
  from author_books
  natural join author
  natural join books

All of the above untested of course :).
Not even sure SQLite supports natural join or not (I'd guess it does). --DD

https://stackoverflow.com/questions/45531762/sql-join-using-vs-join-on
https://stackoverflow.com/questions/8696383/difference-between-natural-join-and-inner-join
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Query for Many to Many

2019-08-27 Thread Dominique Devienne
On Tue, Aug 27, 2019 at 3:38 PM dboland9  wrote:

> I need some help writing some queries for a MTM relationship.  The example
> tables are:
>
> author table books table author_books table
> author_id PKbook_isbn PKa_b_id PK
> author_fnamebook_title  author_id FK
> author_lnamebook_pub_date   book_isbn FK
> author_minit
>
>
> Listings desired:
> book_isbn   book_title  book_pub_date   author
> --++--+---
>
> author book_isbnBook_title
> +-+
>
> Would appreciate the query (inner join - that I do know), and why so I can
> learn something from them.  Please keep them simple (no alias or other
> shortcuts) so I can easily follow what you are doing.  Thanks in advance.
>

Well, that's all you need, inner join, just two of them. Nothing difficult
here IMHO. Or I'm missing something. --DD


> I assume the query will be something like:
>   SELECT
> books.book_isbn, books.book_title, books.book_pub_date,
> author.author_fname, author.author_minit,
> author.author_lname
>   FROM books
>   JOIN
> author_books ON (something )


select author.*, books.*
  from author_books
  join author on author.author_id  = author_books.author_id
  join books  on books.book_isbn   = author_books.book_isbn
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)

2019-08-13 Thread Dominique Devienne
On Tue, Aug 13, 2019 at 10:58 AM Ling, Andy 
wrote:

> > This is what I would call "forward compatibility": You expect an old
> application
> > to be able to read file formats of a future version. Do you have an
> example
> > where there is really required?
>
> I have an Android app that lets you share the database between users. The
> app will run on a variety of versions of Android with a similar variety
> of versions of sqlite. Currently any version can read the database from
> any other
> version regardless of whether it is old reading new or vice versa.
>

Then if you want to retain that, just don't use new features that might
break fwd-compatibility.
That's already the case now, e.g. without-rowid tables, if used, break
older versions of SQLite.
Or defining a view or a trigger using window-functions. Or zillions other
things that can make
a DB incompatible with old versions of SQLite. That can happen now!

And to gracefully handle cases of incompatibilities "by choice", your app
should use
https://www.sqlite.org/pragma.html#pragma_user_version to detect such
cases, and/or
have a user setting to save in "compatibility mode", foregoing better/new
features, or not.
Of course, the version of the runtime SQLite library can also be used.

The fact the SQLite file-format has little to no wiggle room for changes,
does *NOT* mean
SQLite won't or can't grow new features. It's growing them all the time.

The "for the next 35-years" comment was made by DRH himself, and do not
mean there
won't be new things in SQLite for that time, but instead that DRH and team
commit (pun intended :))
to supporting all existing and future features until 2050 at the least
(modulo the BUS FACTOR of course...),
and that commitment is backed by SQLite's outstanding 100% line and branch
coverage testing, and is
one of the primary reason DRH is always reluctant to add new requested
features (quite a testing burden).

But don't despair, FKs, Function-based Indexes, CTEs, Window-Functions,
etc... were asked
literally for years, and made it eventually. When you have a long view like
Richard does,
you're not in a rush to do anything I guess :). --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Enhance CLI with ability to assert version and compile option of sqlite3.so/.dll

2019-08-07 Thread Dominique Devienne
Hi Richard, and others,

A neat little program with added in the context of Fossil SCM,
which asserts the version of the SQLite library used, and which
compile time option were used to compile it (statically or not, for that
matter).

See https://fossil-scm.org/fossil/info/350c627a52908458

I think both would make fine enhancements to the CLI, are command line
options or dot-commands (or both). Checking options would be a text-to-int
converter for compile-time options of course.

If the CLI is linked statically, asserts itself.
If linked dynamically, asserts the shared-lib it's using.

The CLI is often used directly, e.g. forked from a different program,
and the ability to assert a minimum version and/or compile time options
would avoid weird errors, in favor of clear requirement failure ones.

My $0.02. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Doc about sqlite_master

2019-08-05 Thread Dominique Devienne
On Mon, Aug 5, 2019 at 2:03 PM Simon Slavin  wrote:

> On 5 Aug 2019, at 11:54am, Dominique Devienne  wrote:
>
> > What other sqlite_* tables are you talking about?
> > sqlite_master is AFAIK the only table with a "fixed-name", that's part
> of the "public API" as previously discussed.
>
> I would argue that sqlite_sequence and sqlite_stat?, as documented in
>
> <https://www.sqlite.org/fileformat2.html#the_sqlite_sequence_table>
>
> and later sections, are just as well documented as sqlite_master, and
> therefore just as arguably part of the public API.
>

Right. As long as the page includes "master" in its title, as in
"sqlite_master and other built-in SQLite tables", makes sense.
Just an exhaustive list of such tables, with links to other places in the
doc about them, would be an improvement compare
to the current situation IMHO. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Doc about sqlite_master

2019-08-05 Thread Dominique Devienne
On Mon, Aug 5, 2019 at 11:37 AM Simon Slavin  wrote:

> On 5 Aug 2019, at 10:34am, Dominique Devienne  wrote:
>
> > Thus my suggestion that an explicit page of its own is
> warranted, properly indexed then.
> Given that there are other sqlite_* tables which are documented, perhaps
> rather than having a page on just sqlite_master, there should be a page on
> all documented sqlite_* tables.


What other sqlite_* tables are you talking about?

sqlite_master is AFAIK the only table with a "fixed-name", that's part of
the "public API" as previously discussed.
Aren't all these other sqlite_* tables implementation details of some sort?
As such, they are completely separate IMHO. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Doc about sqlite_master

2019-08-05 Thread Dominique Devienne
On Mon, Aug 5, 2019 at 10:37 AM Simon Slavin  wrote:

> On 5 Aug 2019, at 9:25am, Dominique Devienne  wrote:
> <
> https://www.sqlite.org/fileformat2.html#storage_of_the_sql_database_schema
> >
> <https://sqlite.org/faq.html#q7>
> I think the first of those could be said to be the place to go for
> information about it.  It's just not listed like that in any index.
>

"go to" is precisely the problem I'm raising. Google found the FAQ
reference, but that still doesn't get you the possible type values.
And the FileFormat page is much harder to find/discover IMHO. Thus my
suggestion that an explicit page of its own is warranted,
properly indexed then. My $0.02. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Information Schema enhancement request

2019-08-05 Thread Dominique Devienne
(Not sure it's strictly information schema related, but here goes anyway).

SQLite has grown in the recent months better support for extracting
an information schema, thanks to new pragmas and especially their
eponymous vtable versions, allowing them to be mixed in queries with
sqlite_master.

But one thing that's lacking IMHO, is the ability to get dependencies
between objects, to know e.g. that view depends on those table(s)/view(s).
Or that trigger depends on those other view(s)/table(s) it's selecting from
or inserting into (or deleting from).

There's the authorizer API, but it's not accessible from the CLI or queries
in general.
And even if it was, since triggers are "merged in" to SQL using them,
there'd be no
way to distinguish their own dependencies, from the SQL they'd be part of.

I'd therefore would like a new pragma that can list dependencies of
sqlite_master
entries, in terms of other sqlite_master entries. For both tables and
indexes, it's trivial.
The info is already right there in sqlite_master. BUT for views and
triggers, not so at all.

Such a new pragma could reuse the authorizer machinery maybe, and allow an
official way to get dependencies, w/o abusing the authorizer API for that
purpose.

Thank you for considering this proposed enhancement. Thanks, --DD

PS: For triggers, knowing what DML / CRUD the trigger does against the
dependency would be very valuable information too. Obviously for views,
that's SELECT only, while triggers can have all 4 SELECT, INSERT, UPDATE,
DELETE against any of their dependencies.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Doc about sqlite_master

2019-08-05 Thread Dominique Devienne
First, is sqlite_master part of the "public API" of SQLite?
I would think so, since many example refer to it.

I was thus looking for a Doc page about it,
but a quick Google search didn't turn up a "direct" result.

For example, the values sqlite_master.type can take, I found only outside
sqlite.org.
Also in https://www.sqlite.org/sitemap.html#pindex, there's no master entry.

I'm sure there's plenty of tidbits scattered in the doc, but could there be
a
central place to read and learn about sqlite_master? TIA, --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [EXTERNAL] storing blobs in a separate table

2019-08-01 Thread Dominique Devienne
On Thu, Aug 1, 2019 at 5:44 PM Jens Alfke  wrote:

> > On Jul 31, 2019, at 5:02 AM, Hick Gunter  wrote:
> > SQLite stores rows in a compressed format that requires decoding. To
> access the nth field, all the fields that come before it need to be decoded.
>
> My understanding is that it’s just a matter of a byte-count before each
> field. So getting to the n’th field just requires n-1 memory reads and
> pointer additions, a handful of machine instructions. If so, that won’t
> measurably affect performance.
>

[DD] I think this assumes the row fits inside a page. Which with (inline)
blobs, may not be the case at all.
[DD] And Pages form a "linked-list", so reading past the blob may mean
reading ("paging") all those blob pages to follow that chain of page-ids,
to "get-past" a large blob.
[DD] There's a special mode where SQLite keeps extra pages to keep track of
pages, and thus can potentially avoid that "page-chain", but it's not often
used I believe.
[DD] But I'm no expert here :). --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Large database backup

2019-08-01 Thread Dominique Devienne
On Thu, Aug 1, 2019 at 5:02 PM Olivier Mascia  wrote:

> > Le 1 août 2019 à 14:49, Tammisalo Toni  a
> écrit :
> > I have an application which is using sqlite database in WAL mode. There
> is a need for
> > periodic backups to a remote site without obstructing the normal
> operation. Both read
> > and write access is required during the backup. At the moment I have
> system which
> > first blocks checkpoints as otherwise backup was restarted too often.
> Backup is done
> > to remote database implemented with sqlite vfs layer.
> > ...
> > Also, I'm open to other suggestions.
>
> You are using WAL mode.
> Have you tried coding your backup as a single step (passing -1 for the
> number of pages to step)?
>
> int status = sqlite3_backup_step(bck, -1);
>
> Or if you really want to call sqlite3_backup_step() incrementally (X pages
> at a time), then do BEGIN [DEFERRED] [TRANSACTION] first.
>
> Your backup copy should then be allowed to proceed from start to finish
> without impacting readers and writers, nor being impacted by them.

You shouldn't see any restart.  Your backup will be a representation of the
> database as it was when the single (or first) sqlite3_backup_step() call
> started.
>

I've little practical experience here on this, but unless I'm mistaken, it
does mean the WAL file cannot be check-pointed,
while readers (including the backup) are still operating (AFAIK). Not a
problem per-se, just something to be aware of. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Double CTRL-C in shell get you completely out

2019-08-01 Thread Dominique Devienne
On Wed, Jul 31, 2019 at 8:37 PM Tony Papadimitriou  wrote:

> Recently CTRL-C was improved to abort the query and stay in the CLI.  This
> is very good.
>

FWIW, SQliteSpy uses the escape key (ESC) to abort a run-away query (Hello
incorrect recursive CTE!)
I'd prefer CTRL-C to keep killing the current process, and ESC to abort
queries. My $0.02... --DD

PS: Avoids overloading the meaning of CTRL-C/D/Z with non-standard behavior.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [EXTERNAL] Re: Estimated Costs and Memory DBs

2019-07-24 Thread Dominique Devienne
On Wed, Jul 24, 2019 at 3:09 PM Hick Gunter  wrote:

> With the current interface, the xBestIndex function has the possibility of
> returning "effort" and "result set size" separately, instead of just an
> aggregate "effort" (which was at the time documented to assume "result set
> size").
>

Thanks Gunter for the reminder. Indeed my vtables have been implemented a
long time ago, and only use estimatedCost.
I need to investigate estimatedRows and the other newer xBestIndex
features, include (re)reading several times the doc. --DD

From https://www.sqlite.org/c3ref/index_info.html

  double estimatedCost;   /* Estimated cost of using this index */
  /* Fields below are only available in SQLite 3.8.2 and later */
  sqlite3_int64 estimatedRows;/* Estimated number of rows returned */
  /* Fields below are only available in SQLite 3.9.0 and later */
  int idxFlags;  /* Mask of SQLITE_INDEX_SCAN_* flags */
  /* Fields below are only available in SQLite 3.10.0 and later */
  sqlite3_uint64 colUsed;/* Input: Mask of columns used by statement */
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [EXTERNAL] Re: Estimated Costs and Memory DBs

2019-07-24 Thread Dominique Devienne
On Wed, Jul 24, 2019 at 10:45 AM Hick Gunter  wrote:

> The speed of a virtual table depends on the backing store and software
> used to implement it.
>

[DD] Sure. virtual-tables can also access the disk and do expensive things.
[DD] I did say "example given" for my fast-pure-memory-no-decoding case.


> We have virtual tables that reference CTree files as well as virtual
> tables that reference memory sections here.

The advantage is that the VT implementation can adjust it's answers in the
> xBestIndex function.


[DD] I'm not sure I see your point. My point (and Justin's if I understand
him right), is that the relative
[DD] costs from tables vs virtual-tables is hard to figure out, which could
skew results of the planner
[DD] toward sub-optimal plans.

[DD] Most of my queries involve only my own virtual tables, so I use
arbitrary relative costs, like
[DD] 1 if returning a single row via a (virtual) unique index or PK, 2 if
returning a range of rows, and 4 for a full table scan.
[DD] But these "relative for my vtable costs" are probably completely wrong
when mixed with "real" tables,
[DD] disk-based or in-memory. There must be some meaningful correlations
between all costs for an optimal plan.
[DD] Or am I missing something? --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


  1   2   3   4   5   6   7   8   >