[sqlite] Fwd: [sqlite-forum] Convert datetime string to second since Epoch with millisecond precision
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
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
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
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
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
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?
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?
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?
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)
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
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
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
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?
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
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)
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)
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)
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)
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
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
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
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"
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
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
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?
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?
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 ?
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
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
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
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
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
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
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
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
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
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
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
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
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
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 ?
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
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
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
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?
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
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?
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
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
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
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
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&name=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
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
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
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 ?
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 ?
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 ?
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
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
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
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
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
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
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?
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?
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?
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?
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
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?
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?
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?
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
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
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
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
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
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
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
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
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/
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/
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
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
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
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
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
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
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)
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
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
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
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
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
(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
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
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
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
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
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
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
