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

[Thomas Kurz]

Why can't the forum just forward all new postings to this mailing list and vice 
versa? Then everyone could chose what to use ;)


- Original Message - 
From: Richard Hipp 
To: General Discussion of SQLite Database 
Sent: Thursday, March 12, 2020, 21:17:59
Subject: [sqlite] New SQLite Forum established - this mailing list is deprecated

I have set up an on-line forum as a replacement for this mailing list:

https://sqlite.org/forum
https://www.sqlite.org/forum/forumpost/a6a27d79ac

Please consider subscribing to the new Forum.  The intent is that the
forum will eventually replace this mailing list.

The Forum is powered by Fossil.  It has been in active use in the
Fossil community for a couple of years, and has worked well.  See the
second link above for more information.

-- 
D. Richard Hipp
d...@sqlite.org
___
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

[sqlite] .dump

[Thomas Kurz]

I noticed that the .dump command in the CLI doesn't contain the "user_version" 
and "application_id" fields. I don't know whether this is intentional, but 
would you consider including these values in the output of .dump?

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

[sqlite] alter table

[Thomas Kurz]

I'd just like to kindly ask whether there are any new plans for a full ALTER 
TABLE support?

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

Re: [sqlite] WITHOUT ROWID tables

[Thomas Kurz]

Wouldn't be something like

SELECT sql FROM sqlite_master WHERE tbl_name='?' AND type='table'
contains "WITHOUT ROWID"

be sufficient?

Just being curious.

- Original Message - 
From: sky5w...@gmail.com 
To: SQLite mailing list 
Sent: Saturday, February 15, 2020, 18:06:47
Subject: [sqlite] WITHOUT ROWID tables

Ok, not ideal. Still confusing, but I see the difference.
For my code, I know the schemas. I guess a SQL builder could offer up query
options to the user browsing new databases.

On Sat, Feb 15, 2020 at 11:26 AM Simon Slavin  wrote:

> On 15 Feb 2020, at 3:14pm, sky5w...@gmail.com wrote:

> >> To determine if table XYZ is a WITHOUT ROWID table, run "PRAGMA
> >> index_info('XYZ');".  If you get back one or more rows, then XYZ is a
> >> WITHOUT ROWID table.  If you get back no rows, then XYZ is a rowid
> >> table.
> >>
> > Confused...What if I made an index on a ROWID table?
> > CREATE INDEX "Z" ON "DOC" ("n_id");

> The parameter in index_info() is normally the name of an index.  So if you
> create an index "Z" and ask for index_info("Z") you will get information on
> that index.

> If you create a WITHOUT ROWID table with name 'Y", and ask for
> index_info("Y") you will get information on the primary key of that table.

> If both exist, you get information about the index.

> Simon
> ___
> 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

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

Re: [sqlite] Can I search all tables and columns of SQLite database for a specific text string?

[Thomas Kurz]

I would create an SQL dump ("sqlite3 file.db .dump") and search therein.


- Original Message - 
From: Scott 
To: SQLite Mailing List 
Sent: Thursday, February 13, 2020, 15:01:06
Subject: [sqlite] Can I search all tables and columns of SQLite database for a 
specific text string?

Can I search all tables and columns of SQLite database for a specific text 
string? I'm sure this question has been asked many times, but I'm having 
trouble finding a solid answer.
My problem: My clients SQLite database has 11 tables and multiple columns (some 
designated and others not) and they want to be able to search the entire 
database for a specific text or phrase.
What I have done: I've been searching a couple days and found the Full Text 
search on SQLite home based upon using a virtual table, but I don't think that 
will work. It appears that I may be able to search the sqlite_master but it 
seems it may only contain table and column information only minus the data.
What I'm working in: This is an Android app written in Java using the SQLite
What I hope to do: Find a simple query statement or combination of statements 
that may help to efficiently query for the string across tables and columns 
before I resort to multiple queries and methods for all 11 tables.
I'm looking for any experienced thoughts or suggestions anyone may have 
encountered resolving this kind of issue if available. I'm not expecting anyone 
to solve it for me -- just some guidance would be helpful.
Thanks,
Scott ValleryEcclesiastes 4:9-10
___
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] Check constrain execution timing change? (Now a bug)

[Thomas Kurz]

> 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?

You are right. I apologize for my first excitement. The new behavior is correct 
and consistent to other RDBMs. Sometimes one misses the forest for the trees :-)

___
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)

[Thomas Kurz]

And are there any consequences for something like

> create table x (x text check (typeof(x) == 'text'));
> insert into x values ('1');

?


- Original Message - 
From: Richard Hipp 
To: SQLite mailing list 
Sent: Sunday, February 2, 2020, 00:50:34
Subject: [sqlite] Check constrain execution timing change? (Now a bug)

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.

-- 
D. Richard Hipp
d...@sqlite.org
___
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] Check constrain execution timing change? (Now a bug)

[Thomas Kurz]

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???


- Original Message - 
From: Richard Hipp 
To: SQLite mailing list 
Sent: Saturday, February 1, 2020, 00:09:07
Subject: [sqlite] Check constrain execution timing change? (Now a bug)

On 1/31/20, Keith Medcalf  wrote:

> That would elevate this to the status of a bug since it should be impossible
> to do this.


It is also not something that is fixable, so the solution will likely
be to simply document it.
-- 
D. Richard Hipp
d...@sqlite.org
___
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] New word to replace "serverless"

[Thomas Kurz]

I would not choose a new wording. "Serverless" is correct, and just because 
others start using "serverless" in a wrong manner, I don't see any need for a 
change.

Just my 2 cts.


- Original Message - 
From: Richard Hipp 
To: General Discussion of SQLite Database 
Sent: Monday, January 27, 2020, 23:18:45
Subject: [sqlite] New word to replace "serverless"

For many years I have described SQLite as being "serverless", as a way
to distinguish it from the more traditional client/server design of
RDBMSes.  "Serverless" seemed like the natural term to use, as it
seems to mean "without a server".

But more recently, "serverless" has become a popular buzz-word that
means "managed by my hosting provider rather than by me."  Many
readers have internalized this new marketing-driven meaning for
"serverless" and are hence confused when they see my claim that
"SQLite is serverless".

How can I fix this?  What alternative word can I use in place of
"serverless" to mean "without a server"?

Note that "in-process" and "embedded" are not adequate substitutes for
"serverless".  An RDBMS might be in-process or embedded but still be
running a server in a separate thread. In fact, that is how most
embedded RDBMSes other than SQLite work, if I am not much mistaken.

When I say "serverless" I mean that the application invokes a
function, that function performs some task on behalf of the
application, then the function returns, *and that is all*.  No threads
are left over, running in the background to do housekeeping.  The
function does send messages to some other thread or process.  The
function does not have an event loop.  The function does not have its
own stack. The function (with its subfunctions) does all the work
itself, using the callers stack, then returns control to the caller.

So what do I call this, if I can no longer use the word "serverless"
without confusing people?

"no-server"?
"sans-server"?
"stackless"?
"non-client/server"?


-- 
D. Richard Hipp
d...@sqlite.org
___
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] problem with URI mode=ro

[Thomas Kurz]

> Do the same thing again without the mode=ro
> Do the files get deleted this time ?

No, this shows the same behavior, but in this case, it's actually what I'd 
expect.

> Does the program have enough privs over the database file's folder ?

Yeah, sure, actually the file's on a FAT32 drive.

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

[sqlite] problem with URI mode=ro

[Thomas Kurz]

I have a problem when opening a read-only database, which is a WAL-mode 
database.

When trying to open it in read-only mode, i.e. using file:test.sqlite?mode=ro, 
SHM and WAL file are created. That's unpleasant, but the actual problem is the 
two files don't get deleted when closing the database.

Steps to reproduce (OS=Windows, SQLite=3.30.1):

.open test.sqlite
create table test (a integer primary key autoincrement);
insert into test (a) values (null);
select * from test;
.quit

Now set the read-only attribute to test.sqlite, and continue:

.open file:test.sqlite?mode=ro
select * from test;   <-- creates wal and shm
.quit

Result: SHM and WAL files are kept.

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

Re: [sqlite] DELETE extremely slow (.expert command)

[Thomas Kurz]

> It would/should have told you immediately that you needed those two 
> additional indexes, I should think.

Unfortunately not. Someone told me about ".expert" some time ago and it's 
indeed helpful for me because I never know what indexes to create and why. But 
for this database everything seemed ok:

SQLite version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.
sqlite> pragma foreign_keys=on;
sqlite> pragma foreign_keys;
1
sqlite> .expert
sqlite> delete from dataset;
(no new indexes)

(null)
sqlite>

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

Re: [sqlite] DELETE extremely slow

[Thomas Kurz]

The database schema is not a secret. If it helps, I can post it, that's no 
problem. Is it enough to run ".dump" on a database without data?

- Original Message - 
From: Keith Medcalf 
To: SQLite mailing list 
Sent: Friday, November 1, 2019, 22:57:02
Subject: [sqlite] DELETE extremely slow


On Friday, 1 November, 2019 15:12, Simon Slavin  wrote:

>So the slow-down in the DELETE FROM command is caused by a TRIGGER, but
>there are no TRIGGERs on DELETE ?  I don't understand that.  Can someone
>explain, please ?

The code indicates that they are AFTER DELETE so presumably they are triggers, 
though they are fkey.abort and .abort, so they may be also the result of the 
implementation of ON DELETE conditions, which are internally implemented by 
sub-programs and cannot really be distinguished from triggers without seeing 
the full schema.  You might be able to tell if explain comments were enabled at 
compile time (SQLITE_ENABLE_EXPLAIN_COMMENTS), though I can't really say 
whether the extra comments would make it obvious or not without being able to 
see the actual schema or doing a more detailed analysis of the VDBE code.

Foreign key constraints are processed in-line since the default condition is to 
abort/rollback the entire statement only if the fkey violation count is not 
zero at the end of the statement execution.  If you have ON conditions attached 
to the foreign key constraint, they are implemented by a sub-program called 
after the processing of each row rather than inline.  This means, for example, 
that if you have ON DELETE RESTRICT specified against a foreign key constraint 
that a statement which has no violations will effectively be "half as 
efficient" as one that does not have ON DELETE RESTRICT because extra 
constraint will be implemented as a sub-program that will re-check the 
constraint during statement execution so that an immediate ABORT can be raised 
... or at least I think that is how it is implemented.  The same applies for ON 
... CASCADE or ON ... SET NULL which must be implemented as a sub-program run 
per-row and is not easily distinguishable from a trigger which implements the 
same functionality.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



___
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] DELETE extremely slow

[Thomas Kurz]

Yes, there are triggers. I didn't post them because I didn't see any 
correlation to the DELETE query as they are only INSERT and UPDATE triggers:

CREATE TRIGGER item_inserted AFTER INSERT ON item BEGIN UPDATE trace SET typeid 
= (SELECT id FROM "type" WHERE name = 'modified') WHERE trace.id = new.traceid; 
END
CREATE TRIGGER item_modified AFTER UPDATE OF traceid, freq, value, noiseflag ON 
item BEGIN UPDATE trace SET typeid = (SELECT id FROM "type" WHERE name = 
'modified') WHERE trace.id IN (old.traceid, new.traceid); END
CREATE TRIGGER meta_global_insert INSTEAD OF INSERT ON meta_global FOR EACH ROW 
BEGIN INSERT INTO metadata (parameter, value) VALUES (NEW.parameter, 
NEW.value); END
CREATE TRIGGER meta_global_update INSTEAD OF UPDATE OF parameter, value ON 
meta_global BEGIN UPDATE metadata SET parameter=NEW.parameter, value=NEW.value 
WHERE id=OLD.id AND datasetid IS NULL; END
CREATE TRIGGER meta_dataset_insert INSTEAD OF INSERT ON meta_dataset FOR EACH 
ROW WHEN NEW.datasetid IS NOT NULL BEGIN INSERT INTO metadata (parameter, 
value, datasetid) VALUES (NEW.parameter, NEW.value, NEW.datasetid); END
CREATE TRIGGER meta_dataset_update INSTEAD OF UPDATE OF datasetid, parameter, 
value ON meta_dataset WHEN NEW.datasetid IS NOT NULL BEGIN UPDATE metadata SET 
datasetid=NEW.datasetid, parameter=NEW.parameter, value=NEW.value WHERE 
id=OLD.id AND traceid IS NULL; END
CREATE TRIGGER meta_trace_insert INSTEAD OF INSERT ON meta_trace FOR EACH ROW 
WHEN NEW.traceid IS NOT NULL BEGIN INSERT INTO metadata (parameter, value, 
datasetid, traceid) SELECT NEW.parameter, NEW.value, datasetid, NEW.traceid 
FROM trace WHERE id=NEW.traceid; END
CREATE TRIGGER meta_trace_update INSTEAD OF UPDATE OF traceid, parameter, value 
ON meta_trace WHEN NEW.traceid IS NOT NULL BEGIN UPDATE metadata SET 
datasetid=(SELECT datasetid FROM trace WHERE id=NEW.traceid), 
traceid=NEW.traceid, parameter=NEW.parameter, value=NEW.value WHERE id=OLD.id 
AND itemid IS NULL; END
CREATE TRIGGER meta_item_insert INSTEAD OF INSERT ON meta_item FOR EACH ROW 
WHEN NEW.itemid IS NOT NULL BEGIN INSERT INTO metadata (parameter, value, 
datasetid, traceid, traceid) SELECT NEW.parameter, NEW.value, t.datasetid, 
t.id, NEW.itemid FROM itemid i LEFT JOIN trace t ON i.traceid=t.id WHERE 
id=NEW.itemid; END
CREATE TRIGGER meta_item_update INSTEAD OF UPDATE OF itemid, parameter, value 
ON meta_item WHEN NEW.itemid IS NOT NULL BEGIN UPDATE metadata SET 
datasetid=(SELECT t.datasetid FROM item i LEFT JOIN trace t ON i.traceid=t.id 
WHERE t.id=NEW.itemid), traceid=(SELECT traceid FROM item WHERE id=NEW.itemid), 
itemid=NEW.itemid, parameter=NEW.parameter, value=NEW.value WHERE id=OLD.id; END


- Original Message - 
From: David Raymond 
To: SQLite mailing list 
Sent: Friday, November 1, 2019, 17:27:27
Subject: [sqlite] DELETE extremely slow

Looks like you have triggers going on there. You only gave us the table and 
index definitions. What are the on delete triggers you have?


-Original Message-
From: sqlite-users  On Behalf Of 
Thomas Kurz
Sent: Thursday, October 31, 2019 6:54 PM
To: SQLite mailing list 
Subject: Re: [sqlite] DELETE extremely slow

>  Do you have memory to run this in?  Have you increased the sqlite cache size 
> because that looks (to me) an awful lot like I/O thrashing ...

Sorry to disappoint you, Keith and Simon, but in all cases the database file 
has been located on a ramdisk. It's only about 50 MB in size, btw. 

> SQLite runs on the local machine. While MariaDB is client-server, so the 
> delete effectively runs on the server.

Yes and no. Of curse, I had MariaDB run on the same machine, and its data files 
had been stored on the same ramdisk.

> How much of the 88 minutes is "waiting" time?

I did it again, same file:

SQLite version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.
sqlite> pragma foreign_keys=on;
sqlite> .timer on
sqlite> delete from dataset;
Run Time: real 5249.891 user 2412.812500 sys 2606.531250

> You haven't shown the "explain query plan" Keith asked for

Is the beginning of it enough or do I have to repeat the entire DELETE? Here is 
the output which I canceled after some seconds:

QUERY PLAN
|--SCAN TABLE dataset
|--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_04 (datasetid=?)
|--SEARCH TABLE trace USING COVERING INDEX trace_idx_03 (datasetid=?)
|--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_04 (datasetid=?)
|--SCAN TABLE item
|--SEARCH TABLE trace USING COVERING INDEX trace_idx_03 (datasetid=?)
|--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_03 (traceid=?)
|--SEARCH TABLE item USING COVERING INDEX item_idx_01 (traceid=?)
|--SCAN TABLE item
|--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_03 (traceid=?)
|--SCAN TABLE item
|--SEARCH TABLE item USING COVERING INDEX item_idx_01 (traceid=?)
|--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_06 (itemid=?)
|--

Re: [sqlite] DELETE extremely slow

[Thomas Kurz]

That's it!!! You're a genius! Thank you very very much!

Run Time: real 8.290 user 3.25 sys 1.906250

- Original Message - 
From: Keith Medcalf 
To: SQLite mailing list 
Sent: Friday, November 1, 2019, 18:07:51
Subject: [sqlite] DELETE extremely slow


One of your triggers requires and index on item(nameid) and there is no index 
on item(nameid).  
Hence it is doing a table scan to find the rows matching this trigger.  That is 
why the plan has multiple "SCAN item" in it.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



___
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] DELETE extremely slow

[Thomas Kurz]

>  Do you have memory to run this in?  Have you increased the sqlite cache size 
> because that looks (to me) an awful lot like I/O thrashing ...

Sorry to disappoint you, Keith and Simon, but in all cases the database file 
has been located on a ramdisk. It's only about 50 MB in size, btw. 

> SQLite runs on the local machine. While MariaDB is client-server, so the 
> delete effectively runs on the server.

Yes and no. Of curse, I had MariaDB run on the same machine, and its data files 
had been stored on the same ramdisk.

> How much of the 88 minutes is "waiting" time?

I did it again, same file:

SQLite version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.
sqlite> pragma foreign_keys=on;
sqlite> .timer on
sqlite> delete from dataset;
Run Time: real 5249.891 user 2412.812500 sys 2606.531250

> You haven't shown the "explain query plan" Keith asked for

Is the beginning of it enough or do I have to repeat the entire DELETE? Here is 
the output which I canceled after some seconds:

QUERY PLAN
|--SCAN TABLE dataset
|--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_04 (datasetid=?)
|--SEARCH TABLE trace USING COVERING INDEX trace_idx_03 (datasetid=?)
|--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_04 (datasetid=?)
|--SCAN TABLE item
|--SEARCH TABLE trace USING COVERING INDEX trace_idx_03 (datasetid=?)
|--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_03 (traceid=?)
|--SEARCH TABLE item USING COVERING INDEX item_idx_01 (traceid=?)
|--SCAN TABLE item
|--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_03 (traceid=?)
|--SCAN TABLE item
|--SEARCH TABLE item USING COVERING INDEX item_idx_01 (traceid=?)
|--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_06 (itemid=?)
|--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_06 (itemid=?)
`--SCAN TABLE item
addr  opcode p1p2p3p4 p5  comment  
  -        -  --  -
0 Init   0 30000  Start at 30  
1 Null   0 1 000  r[1]=NULL
2 OpenRead   0 7 0 0  00  root=7 iDb=0; dataset
3 Rewind 0 7 000   
4   Rowid  0 2 000  r[2]=rowid   
5   RowSetAdd  1 2 000  rowset(1)=r[2]
6 Next   0 4 001   
7 OpenWrite  0 7 0 3  00  root=7 iDb=0; dataset
8   RowSetRead 1 29200  r[2]=rowset(1)
9   NotExists  0 282 1  00  intkey=r[2]  
10  Copy   2 3 000  r[3]=r[2]
11  OpenRead   2 170 k(3,,,)02  root=17 iDb=0; 
metadata_idx_04
12  IsNull 3 18000  if r[3]==NULL goto 
18
13  Affinity   3 1 0 C  00  affinity(r[3])
14  SeekGE 2 183 1  00  key=r[3] 
15IdxGT  2 183 1  00  key=r[3] 
16FkCounter  0 1 000  fkctr[0]+=1  
17  Next   2 15000   
18  OpenRead   4 13869  0 k(2,,) 02  root=13869 iDb=0; 
trace_idx_03
19  IsNull 3 25000  if r[3]==NULL goto 
25
20  Affinity   3 1 0 C  00  affinity(r[3])
21  SeekGE 4 253 1  00  key=r[3] 
22IdxGT  4 253 1  00  key=r[3] 
23FkCounter  0 1 000  fkctr[0]+=1  
24  Next   4 22100   
25  Delete 0 1 0 dataset00   
26  Program3 0 9 program00  Call: fkey.abort
27  Program3 0 10program00  Call: fkey.abort
28Goto   0 8 000   
29Halt   0 0 000   
30Transaction0 1 400  01  usesStmtJournal=0
31Goto   0 1 000   
0 Init   0 1 000  Start at 1; Start: 
.abort (AFTER DELETE ON dataset)
1 Null   0 1 000  r[1]=NULL
2 OpenRead   11170 k(3,,,)02  root=17 iDb=0; 
metadata_idx_04
3 Param  0 2 000  r[2]=old.rowid
4 IsNull 2 11000  if r[2]==NULL goto 11
5 Affinity   2 1 0 C  00  affinity(r[2])
6 SeekGE 11112 1  00  key=r[2] 
7 

Re: [sqlite] DELETE extremely slow

[Thomas Kurz]

> According to the MariaDB reference manual, it does not "do anything" with 
> references clauses on columns.

Thanks for that hint, I will try again tomorrow because I cannot say for sure 
now whether it worked correctly or not. (And I don't have that data available 
anymore.)

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

Re: [sqlite] DELETE extremely slow

[Thomas Kurz]

> Something is wrong.  If you did multiple commands like
>
> DELETE FROM MyTable;
>
> to your child tables, they should be fast.  Have you run an integrity check ?

I created a new database now, added the missing index "trace(datasetid)" as 
suggested by Keith.

The result of "DELETE FROM dataset" is now 88 minutes, which of course is 
better than before where it took hours, but not nearly as quick as I'd expect...

@Warren:
> Is that command representative of actual use, or are you deleting all rows 
> just for the purpose of benchmarking?

Usually I want to delete only several datasets, but not all. I left out the 
where-clause for simplification now.

@Keith:
> and in the face of enforced foreign key constraints will always delete the 
> rows one by each even if dependent (child) tables have no rows.

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

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

Re: [sqlite] DELETE extremely slow

[Thomas Kurz]

> Keith found the answer: you don't have the indexes required to make your 
> FOREIGN KEYs run quickly.

Thanks, I will try that.

> If you DELETE FROM the child tables first, do you get fast or slow times ?

Yes, I already tried deleting from each table individually. It's slow 
everywhere.

> Thee way you're doing it involves a lot of steps as SQlite works its way 
> through the parent table, deletes one row from that, and cascades through the 
> other tables, looking for and deleting related rows from those.

Ok, I might have errors in my declarations, but SQLite seems to have problems 
as well, because MariaDB (without any explicit index defintion!) handles the 
same deletion within seconds...

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

Re: [sqlite] DELETE extremely slow

[Thomas Kurz]

Yes, please apologize, I indeed forgot to attach the table definitions:

CREATE TABLE dataset (
id INTEGER  PRIMARY KEY AUTOINCREMENT
UNIQUE
NOT NULL,
name   STRING   DEFAULT NULL
COLLATE NOCASE,
is_latest  BOOLEAN  NOT NULL
DEFAULT 1,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE trace (
id INTEGER  PRIMARY KEY AUTOINCREMENT
UNIQUE
NOT NULL,
name   STRING   DEFAULT NULL
COLLATE NOCASE,
datasetid  INTEGER  REFERENCES dataset (id) 
NOT NULL,
quantityid INTEGER  REFERENCES quantity (id) 
NOT NULL,
stored DATETIME DEFAULT NULL,
created_at DATETIME NOT NULL
DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX trace_idx_01 ON trace (
quantityid
);

CREATE INDEX trace_idx_01 ON trace (
quantityid
);

CREATE TABLE item (
idINTEGER PRIMARY KEY AUTOINCREMENT
  UNIQUE
  NOT NULL,
traceid   INTEGER REFERENCES trace (id) 
  NOT NULL,
freq  BIGINT  NOT NULL,
value REALNOT NULL,
noiseflag BOOLEAN DEFAULT NULL
);

CREATE INDEX item_idx_01 ON item (
traceid
);

CREATE TABLE metadata (
idINTEGER PRIMARY KEY AUTOINCREMENT
  UNIQUE
  NOT NULL,
parameter STRING  NOT NULL
  COLLATE NOCASE,
value STRING  NOT NULL
  COLLATE NOCASE,
datasetid INTEGER DEFAULT NULL
  REFERENCES dataset (id),
traceid   INTEGER DEFAULT NULL
  REFERENCES trace (id),
itemidINTEGER DEFAULT NULL
  REFERENCES item (id) 
);

CREATE INDEX metadata_idx_01 ON metadata (
parameter,
value,
datasetid,
traceid,
itemid
);

CREATE INDEX metadata_idx_02 ON metadata (
datasetid,
traceid
);

CREATE INDEX metadata_idx_03 ON metadata (
traceid
);

CREATE INDEX metadata_idx_04 ON metadata (
datasetid,
itemid
);

CREATE INDEX metadata_idx_05 ON metadata (
traceid,
itemid
);

CREATE INDEX metadata_idx_06 ON metadata (
itemid
);

CREATE INDEX metadata_idx_07 ON metadata (
datasetid,
parameter
);

CREATE INDEX metadata_idx_08 ON metadata (
traceid,
parameter
);

CREATE INDEX metadata_idx_09 ON metadata (
parameter,
traceid
);

CREATE INDEX metadata_idx_10 ON metadata (
parameter,
datasetid,
traceid,
itemid
);

CREATE TABLE quantity (
id INTEGER PRIMARY KEY AUTOINCREMENT
   UNIQUE
   NOT NULL,
name   STRING  NOT NULL,
unit   STRING  NOT NULL,
sumrule[SMALLINT UNSIGNED] DEFAULT NULL,
created_at DATETIMEDEFAULT CURRENT_TIMESTAMP,
UNIQUE (
name,
unit,
sumrule
)
ON CONFLICT IGNORE
);





- Original Message - 
From: Dominique Devienne 
To: SQLite mailing list 
Sent: Thursday, October 31, 2019, 11:06:07
Subject: [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 mailin

[sqlite] DELETE extremely slow

[Thomas Kurz]

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?

Kind regards,
Thomas

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

[sqlite] Backward compatibility

[Thomas Kurz]

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?

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

Re: [sqlite] Single or double quotes when defining alias?

[Thomas Kurz]

Ok, thanks for everone's answer.


- Original Message - 
From: James K. Lowden 
To: sqlite-users@mailinglists.sqlite.org 
Sent: Monday, October 28, 2019, 18:27:06
Subject: [sqlite] Single or double quotes when defining alias?

On Fri, 25 Oct 2019 23:55:20 +0200
Thomas Kurz  wrote:

> SELECT column1 AS 'c'
> --or--
> SELECT column2 AS "d"

> On the one hand, the name refers to a column or table identifier. 

The SQL-92 standard refers to that kind of name as a
"correlation name", and its BNF grammar designates a correlation name
as a kind of indentifier.  Therefore, syntactically, "d" is correct
because double-quotes are used to quote identifiers.  

Which was news to me.  I've always used single-quotes for
correlation names (on creation, never reference).  Not because they need
quoting. I never choose a correlation name that needs to be quoted;
normally they're just 3 lower-case letters, at most.  I would quote
them only to make them stand out for the syntax highlighter.  And who
doesn't like pretty SQL?  

--jkl


___
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] Roadmap?

[Thomas Kurz]

> SQLite has had geospatial support for years via the r-tree extension, and 
> more recently GeoJSON.

But not compatible to standards from Open Geospatial Consortium, as far as I 
know. Which requires additional conversions, and considering that geodata 
usually have sizes of a GB or more, this is not an option at all.

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

Re: [sqlite] Roadmap?

[Thomas Kurz]

> What do you mean by "SQL-basics"?

I forgot to mention that at least some basic math would be very helpful as 
well. I don't want to suggest a complete math support, that would really be far 
away from liteness, but the discussion standard deviation has shown that at 
least STDEV and POWER would be very helpful if they part of SQLite core.

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

Re: [sqlite] Roadmap?

[Thomas Kurz]

> Omitting RIGHT JOIN is good, that's a misfeature and LEFT JOIN does 
> everything 
useful it does.

With all dear respect, but I don't think that it is up to you to define what a 
"feature" and a "misfeature" is. iirc, RIGHT JOIN is declared in SQL92, it is 
part of the SQL standard, and therefore it is one of the "SQL basics" I 
mentioned. And it's not a big thing either.

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

Re: [sqlite] Roadmap?

[Thomas Kurz]

> I suspect you are used to database servers, and haven’t used SQLite as an 
> embedded library inside an app

Yes and no ;-)

I have used database servers, and I am currently (for about 2 years) using (and 
appreciating!) SQLite library.

> Full text search is very common

Yes, of course. I didn't mean to deny that. I was just wondering why it's got 
priority over SQL-standards (because, as far as I know, but I might be wrong 
here, FTS is not part of one the SQL-standards).

> You didn’t mention geo-queries

Geospatial support would be one of the features I would *LOVE* to see in SQLite 
:-)

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

Re: [sqlite] Roadmap?

[Thomas Kurz]

> Feel free to make suggestions. Which missing feature or features causes 
you the most bother?

Thanks, Dan.

To me, the most puzzling thing is the lack of full ALTER TABLE support (DROP 
COLUMN, MODIFY COLUMN, ADD CONSTRAINT, DROP CONSTRAINT). Modifying tables is 
some kind of science in SQLite, and thus, very error-prone. I'd be willing to 
donate for that but as a private user I cannot affort 85 k$ ;-)

If you are collecting suggestions, here's some ideas:

- RIGHT JOIN
- Time periods, temporal referential integrity, temporal predicates from 
SQL:2011
- native geospatial support (storage using well-known binary representation 
from Open Geospatial Consortium); I know there's Spatialite, but there are 
massive bugs in Spatialite that imho arise only due to the lack of basic native 
geo support

Some non-standard but very useful behaviors from other RDBMs:

- ON UPDATE CURRENT_TIMESTAMP (from MySQL)
- SHOW TABLES, SHOW COLUMNS, etc. (from MySQL)
- RETURNING (from Postgres)

(I have left out some things I know about that they have already been discussed 
recently, like DATETIME.)

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

Re: [sqlite] Roadmap?

[Thomas Kurz]

> The features you name don't take away from the "liteness", they are all quite 
small and useful.

Yes of course they are useful, I wouldn't deny that. But they are prioritized 
over SQL-basics, that's what I'm confused about.


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

[sqlite] Single or double quotes when defining alias?

[Thomas Kurz]

Dear all,

this might be a stupid question, but do I have to use single or double quotes 
when defining an alias?

SELECT column1 AS 'c'
--or--
SELECT column2 AS "d"

On the one hand, the name refers to a column or table identifier. On the other 
hand, at the time of using this statement, the identifier does not exist yet. 
At that moment, it is a string literal telling that an identifier with that 
name should be created.

So which one is correct?

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

[sqlite] Roadmap?

[Thomas Kurz]

I'd kindly ask whether there is some sort of roadmap for SQLite development?

Someone recently pointed out how much he loves the "lite" and well-thought 
features. I cannot see that: I observe that many "playground" gadgets keep 
being implemented (like virtual columns, virtual tables, FTS3/4/5, ...), where 
one might wonder about their relationship to "Liteness", whereas other 
features, essential basics of the SQL standards, are still missing and there is 
no indication they are to be added.

Without wanting to offend someone, I cannot see the logic in development, so: 
Is there some kind of roadmap?

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

Re: [sqlite] SQLite plus the works (was Re: Opposite of SQLite)

[Thomas Kurz]

> It has LEFT JOIN and does not have RIGHT JOIN. Why? Because RIGHT can be made 
> out of LEFT by swapping order of tables.

The paradigma of SQL is to let the user describe what he wants to do, not to 
think about how to describe the problem so that the database system does 
understand. The lack of RIGHT JOIN, FULL INNER/OUTER JOIN, (full) ALTER TABLE, 
missing geospatial functions, etc. is a huge disadvantage, not an advantage!

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

Re: [sqlite] Date time input

[Thomas Kurz]

> I hope you will experience such joy as well. 

Well, I don't ;-) The lack of full ALTER TABLE support frustrates me every 
time, even though I greatly appreciate most other parts of SQLite and the 
developers' work. But a more complete SQL statement support would be very, very 
helpful.

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

Re: [sqlite] Determining column collating functions

[Thomas Kurz]

Would you consider implementing this not as a pragma, but as a real statement, 
like MySQL's SHOW COLUMNS 
(https://dev.mysql.com/doc/refman/5.5/en/show-columns.html)? Would be easier to 
memorize.

- Original Message - 
From: Keith Medcalf 
To: SQLite mailing list 
Sent: Wednesday, August 14, 2019, 00:36:07
Subject: [sqlite] Determining column collating functions


On Tuesday, 13 August, 2019 15:59, Manuel Rigger  
wrote:

>Is there a simple way to determine the collating function of a
>column?

Presently, there is not.

>PRAGMA table_info does not seem to provide this information. The
>information could be extracted from sqlite_master, which contains the
>SQL statements used to create the table or view. While parsing the
>SQL string is rather straightforward for tables, it would involve 
>more effort to determine the collating functions for views, which 
>can again reference other views or tables.

The internal schema representation for the column (from which the table_info 
and table_xinfo draw their information) does contain the name of the collation 
being used if it is not the default (and also the actual affinity of the 
column), however the current table_info/table_xinfo does not return that 
information, although modifications to do so would be rather trivial.

Richard, would you like a patch for this (and a database_info pragma, which 
would allow all the introspection pragma tables to work across all attached 
databases more easily)?

As for views, they are a sort of dynamic thing so the collating sequence (nor 
the affinity) of a view column is not known until the statement using the view 
is prepared (a view is merely the storage of a definition and nothing is really 
known about it until it is used).

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.





___
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] Backward compatibility vs. new features (was: Re: dates, times and R)

[Thomas Kurz]

Another reason: because PostgreSQL supports it as well (including timezone) ;-)

- Original Message - 
From: Peter da Silva 
To: SQLite mailing list 
Sent: Tuesday, August 13, 2019, 23:18:29
Subject: [sqlite] Backward compatibility vs. new features (was: Re: dates, 
times and R)

If the datr/time is stored internally as utc iso8601 text then it will
remain compatible with old versions and can implement whatever new behavior
is needed on new versions. The bigger question is 'what new behavior'? The
only nee behavior seems to be 'let this third party package see it as a
date', which it should be able to figure out by looking at the schema.
___
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] Backward compatibility vs. new features (was: Re: dates, times and R)

[Thomas Kurz]

> And now you have a file which can't be edited with old versions of the CLI.  
> However you cut it, you have compatibility problems.

One shouldn't do it at all. It's like trying to a edit a DOCX with Word95. It's 
not *backward* compatibility. It's not the case you mentioned before. And 
finally, when using the CLI there's no reason not to update the CLI.

___
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)

[Thomas Kurz]

> A programmer uses a copy of the SQLite CLI to correct errors in a database 
> made and maintained by a production program.  This involves making a new 
> table, copying some data from the old data to the new table, deleting the old 
> table, then renaming the new table.  When the programmer is finished making 
> changes, they replace the old version of the database with the corrected 
> version.

But this ain't a problem because the default would be a compatible version.

Maybe I should explain my idea a bit more explicit, to avoid a 
misunderstanding. Let's assume preparations with 3.31 and the first real change 
(e.g. DATE) in 3.34. For simplicity, we create a table TEST without any 
declaration. Application itself uses 3.16.

a) Creating table with any version <3.31 results in: CREATE TABLE TEST

b) Creating table with 3.31+ without any explicit declaration: 
CREATE TABLE TEST --> will be stored as CREATE TABLE TEST VERSION=3.31
This should be no problem for any library below 3.31 as it should ignore the 
VERSION declaration.

Everything that is done with this table will always operate in 3.31 
compatibility mode, regardless of what library version is actually being used, 
be it an old one or a new one.

c) To enable the new DATE interpreation, we do:
CREATE TABLE TEST VERSION=3.34 --> will be stored the same way

Now we have to distinguish three cases:

i) Library versions 3.34+ can use the modified DATE datatype. Reasonably, it 
should internally be encoded as INT or FLOAT, depending on what timestamp is 
internally being used. This will be a compatibility-fallback for case iii. 
Alternatively, one could use BLOB to tell case iii not to handle this data at 
all.

ii) Versions 3.31, 3.32 and 3.33 should reject modifying such kind of table as 
(when being honest) they do not know about the feature.

iii) Versions up to and including 3.30 should basically also reject 
modifications, but they cannot do it as they don't now about the VERSION flag. 
For this case, we have stored the data either as INT/FLOAT which can be 
handled, or as BLOB which wouldn't be touched.

The important point is that we would have introduced a mechanism which should 
be future-proof, but breaking compatibility as few as possible. And a 
programmer would always have to actively enable a "feature level". If he didn't 
do that, the library would operate in compatibility mode as it is the case 
right now. Modifying tables would not be a problem either.

___
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)

[Thomas Kurz]

> This would break backward compatibility.  It is necessary to be sure that 
> database files made with current versions of SQLite can be opened with old 
> versions back to 2013.

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? Apart from database browsers I cannot 
think of many situations where an application has to read an arbitrary database 
created by an arbitrary application. It wouldn't know how to interpret data 
anyhow?

> > Alternatively, one could introduce a pragma statement, say PRAGMA 
> > emulation=ver, that could default to 3.30 (or whatever) now and which 
> > doesn't change at all

> This would break backward compatibility.  It is necessary to be sure that 
> database files made with current versions of SQLite can be opened with old 
> versions back to 2013.

I don't think it would break compatibility. If there's no space to include 
version information in the database file directly, it could e.g. be added to 
the table definition: CREATE TABLE whatsoever VERSION=3.30, just like the 
ENGINE keyword of MariabDB. Reading tables without a VERSION keyword are 
automatically interpreted in the version where this feature was introduced 
first.

___
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)

[Thomas Kurz]

> Since date/time is stored as an offset in some units from an epoch of some 
> type, the "datatype" declaration is nothing more than an annotation of an 
> already existing double or integer type -- and you can already annotate your 
> select column names and table attribute type declarations just fine.

The problem is not only about storing an arbitrary integer or float number. A 
date is much more, it has timezone information with it, and I would like to see 
a DATE column handle this in a proper and well-defined way, just as a calendar 
(CalDAV) does handle it, so it would allow me to convert between e.g. EST and 
CEST or calculating time differences (e.g. working time in a company). I know 
many of this is somehow possible now as well, but as far as I know, not really 
compliant with other RDMSs.

It's not only the date type itself, I miss some other useful aids as well. 
MariaDB/MySQL for example have the "DEFAULT CURRENT_TIMESTAMP ON UPDATE 
CURRENT_TIMESTAMP" feature, useful for having a "last modified" column to track 
changes. SQLite requires me to define a trigger which is quite complex as one 
has to declare each column except for the "last modified" one to avoid a 
recursive trigger.

Beyond data/time, a great improvement would be support for geodata. SQLite has 
become quite popular for GIS applications (e.g. QGIS, but also ArcGIS) using 
Spatialite and/or GeoPackage. Currently, there is no GEOMETRY data type so both 
store geometries in BLOBs ensuring proper functionality with a whole bunch of 
triggers. This leads to many problems, for example when renaming tables or 
columns. I often had inconsistencies and needed to manually adjust the 
"geometry_columns" table (which also would be obsolete then, leading to much 
cleaner database layout) to make things working again.

Please apologize: I do not want to offend anyone, it's just my opinion. I 
appreciate SQLite being a great thing and I don't know a better embedded 
database (one can forget about Firebird, etc.). But in my everyday work I 
always find some issues that could make a great thing perfect if they were 
implemented :-)

___
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)

[Thomas Kurz]

> The issue for something like a data-time field is how would you indicate
that a field is a data-time field. Due to backwards compatibility it
can't use the term data or time to trigger that use, as existing
applications use that and expect a different result, based on published
and promised rules.

I don't see a huge problem here. Does the database file have a version number 
indicating which version the file has been created with? If so, newer SQLite 
libraries could easily emulate the old-style behavior by just checking this 
version number.

Alternatively, one could introduce a pragma statement, say PRAGMA 
emulation=ver, that could default to 3.30 (or whatever) now and which doesn't 
change at all (or will at least always be some years behind the most current 
version) and which tells the library to behave exactly as in version . 
This would allow changing modes without breaking existing applications. One 
step more could be storing  in the database as well, using the version 
number enabled upon database creation. This way, one would have a mechanism 
that could eliminate "cannot be done due to backward compatibility" once and 
forever :)

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

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

[Thomas Kurz]

> I do understand the value of having date/time types in SQLite, but it is not 
> easy to do while retaining backward compatibility.  It'll have to wait for 
> SQLite4 or something.

Actually I do not really understand the point about backward compatibility. 
Many very useful suggestions are rejected by just citing "backward 
comatibility".

From my point of view, this is not actually a knock-out-criterium, because:

a) Existing applications would always continue to work, even if using newer 
versions of sqlite.dll as it should be no problem for any later version that 
intruduced feature X to continue using any database regardless of whether or 
not this database actually contains feature X. (This is actual *backward* 
compatibility.)

b) New applications could decide whether or not to make use of any new feature.

c) Of course, an existing application doesn't know how to handle database 
structures with feature X when using an sqlite.dll from the time before this 
feature has been introduced. (I would, however, call this *forward* 
compatibility.) This is true, but on the other hand, one might ask why an 
arbitrary application actually might want to do this? I have often gotten the 
response that it is up to the app how to handle data when reading from a 
database (IIRC, DATE as a matter of fact was the topic of the discussion). So 
one could as well argue that it is the app's responsibility to use up-to-date 
libraries when accessing databases. (Note that this applies *only* to an app 
dealing with *foreign* databases where one anyhow needs to know how to 
interpret data, so this is no knock-out-problem.)

Someone recently posted about SQLite support for the next 31 (or so) years. 
Actually I hope this doesn't mean we will have to wait for three decades until 
new features could be implemented...?!

Maybe a new subpage could be added to the website, named "proposed features" or 
similar, just listing what has been proposed including some short description. 
There have been many great ideas and it would be a pity if they got lost in the 
depths of the mailing list ;)

Just my 2cts
Thomas

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

Re: [sqlite] Feature request: import MySQL dumps in CLI

[Thomas Kurz]

> I highly doubt the SQLite team will undertake this task. They
> Surely have the skill to do so, but their priority is the one
> software product you desire to use, undoubtedly due to its
> high utility.  I doubt that utility would exist if they were
> to wander off tacking the conversion challenge for the other
> popular database systems.
 
Well, that's why I asked for an *import* support. It's widely spread practice 
to offer at least import capabilities from other software. The other way round 
would be up to MySql/MariaDB.

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

Re: [sqlite] Feature request: import MySQL dumps in CLI

[Thomas Kurz]

t null references geodb_locations
,  coord_type   integer not null check (coord_type=20010)
,  lat  double precision
,  lon  double precision
,  coord_subtypeinteger
,  valid_since  date
,  date_type_since  integer
,  valid_until  date not null
create table geodb_textdata (
  loc_id   integer not null references geodb_locations
,  text_typeinteger not null
,  text_val varchar(255) not null,  /* 
varchar(2000)? */
,  text_locale  varchar(5),  /* ISO 639-1 */
,  is_native_lang   smallint(1)
,  is_default_name  smallint(1)
,  valid_since  date
,  date_type_since  integer
,  valid_until  date not null
,  date_type_until  integer not null
,check (
,  (
,(
,  (text_type = 50010 or text_type = 50014 or
,   text_type = 50012 or text_type = 50070 or
,   text_type = 50071 or text_type = 50080 or
,   text_type = 50080 or text_type = 50090
,  ) and
,  text_locale like '__%' and
,  is_native_lang is not null and
,  is_default_name is not null
,) or
,(
,  (text_type = 50011 or text_type = 50013 or
,   text_type = 50030 or text_type = 50050 or
,   text_type = 50060
,  ) and
,  text_locale is null and
,  is_native_lang is null and
,  is_default_name is null
,)
,  ) and
,(
,  (valid_since is null and date_type_since is null) or
,  (valid_since is not null and date_type_since is not null)
,)
create table geodb_intdata (
  loc_id   integer not null references geodb_locations
,  int_type integer not null
,  int_val  bigint not null
,  valid_since  date
,  date_type_since  integer
,  valid_until  date not null
create table geodb_floatdata (
  loc_id   integer not null references geodb_locations
,  float_type   integer not null
,  float_valdouble precision not null,/* double / float??? */
,  valid_since  date
,  date_type_since  integer
,  valid_until  date not null
create table geodb_changelog (
  id   integer not null primary key
,  datumdate not null
,  beschreibung text not null
,  autorvarchar(50) not null
END TRANSACTION; 


- Original Message - 
From: Simon Slavin 
To: SQLite mailing list 
Sent: Wednesday, August 7, 2019, 18:25:45
Subject: [sqlite] Feature request: import MySQL dumps in CLI

On 7 Aug 2019, at 5:13pm, Thomas Kurz  wrote:

> So my suggestion would be to add an import feature to the CLI that allows to 
> directly import MySQL/MariaDB dumps into an SQLite database keeping as many 
> information as possible. As SQLite already has a complete SQL parser I expect 
> much better results than with existing converters.

MySQL has a tool which dumps the database as SQL commands.  SQLite has a tool 
which reads SQL commands and makes a database from them.

However, there are occasional compatibility problems with using the two 
together because of differing rules on text quoting, entity names, etc..  If 
you're running into one of these give us some details, and what OS you're 
using, and we'll see if we can figure out a script which works around them.
___
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

[sqlite] Feature request: import MySQL dumps in CLI

[Thomas Kurz]

Dear SQLite team,

I suppose I am not the only one having to convert between MySQL/MariaDB and 
SQLite databases every now and then. I know there are converters for MySQL 
dumps but none of any I have ever tried did work nearly reliable.

So my suggestion would be to add an import feature to the CLI that allows to 
directly import MySQL/MariaDB dumps into an SQLite database keeping as many 
information as possible. As SQLite already has a complete SQL parser I expect 
much better results than with existing converters.

Kind regards,
Thomas

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

Re: [sqlite] Hidden data

[Thomas Kurz]

Have you tried dumping the database ("sqlite3 places.sqlite .dump") and then 
searching for some known data in the resulting SQL file?


- Original Message - 
From: bitwyse 
To: sqlite-users@mailinglists.sqlite.org 
Sent: Sunday, August 4, 2019, 18:33:29
Subject: [sqlite] Hidden data

Hello

Many of you may already know that FireFox (and other Mozilla family
navigators like SeaMonkey) store their bookmarks in an Sqlite file
("places.sqlite").
Bookmarks may include
 - the name of the page
 - the URL
 - chosen labels
 - keywords
 - the description

The last 2 are extracted from the page header.

You _can_ edit the description - some programmers just use the title and
others the first line(s) of the text (I've even seen about half of the
page!) - but it's not much use - you can't search in it.
So I don't regret that it is no longer visible nor editable in the
latest versions of the bookmark manager. However the data REMAINS in the
file, although it isn't displayed in a basic viewer (the FireFox Sqlite
manager extension or DB sqlite viewer).
There is a field "bookmarkProperties/description" in the
"moz_anno_attributes" table but I can't find the corresponding data.

Could it be hidden in a Blob?
What program (command?) would diplay it and allow deleting it?
(VACUUMing doesn't remove it.)

(I have got rid of it by exporting the bookmarks to an HTML file,
deleting all the  tag lines and then re-importing it - but that's
laborious and I would like to be able to do it in one operation.)

Regards
Christophe

-- 
bitwyse   [PGP KeyID 0x18EB38C4]
Les conseils - c'est ce qu'on demande
quand on connaît déjà la réponse
mais aurait préféré ne pas la savoir.

___
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] [SPAM?] Re: Explicit "read transaction" with journal_mode=WAL.

[Thomas Kurz]

Would it be possible for you to give some feedback (just an estimation) whether 
or not a suggestion might be considered?


- Original Message - 
From: Richard Hipp 
To: SQLite mailing list 
Sent: Wednesday, July 31, 2019, 16:10:13
Subject: [sqlite] [SPAM?] Re: Explicit "read transaction" with journal_mode=WAL.

On 7/31/19, Simon Slavin  wrote:
> On 31 Jul 2019, at 12:57pm, test user  wrote:

>> Is there a standard place where people can request features to be added to
>> SQLite?

> Here.  You've already done it.  The developers of SQLite read this list and
> will consider the things you wanted to do and whether it's worth providing a
> better way to do them.

Just to confirm: Simon is exactly correct.

-- 
D. Richard Hipp
d...@sqlite.org
___
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] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...

[Thomas Kurz]

Imho it would be helpful (especially for newbies that don't know the full 
history of SQLite) to have a "PRAGMA strict_mode" or similar, to disable all 
kinds of historical bugs. They might be relevant for existing applications but 
in no way for newly created ones. Among the things to consider should be:

- PRIMARY KEY = UNIQUE NOT NULL (what I have learned now *g*)
- enable FOREIGN KEY constraints (I know there is already a pragma, but imho it 
should be included)
- strict type enforcement
- disable the use of double quotes for strings
- default to WITHOUT ROWID

...and probably many more I don't know about ;-)


- Original Message - 
From: Dominique Devienne 
To: SQLite mailing list 
Sent: Friday, July 19, 2019, 10:25:17
Subject: [sqlite] I can insert multiple rows with the same primary key when one 
of the value of the PK is NULL ...

On Thu, Jul 18, 2019 at 9:11 PM Keith Medcalf  wrote:

> Except in SQLite where as a documented behavioural anomaly maintained for
> backwards compatibility it simply means "UNIQUE" (for ROWID tables).  And
> UNIQUE indexes may have NULL components.  This is because despite your
> wishing that your primary key is the primary key, it is not the primary key.

> However when WITHOUT ROWID tables were introduced there was no backwards
> compatibility issues (they were new after all) then PRIMARY KEY could be
> implemented as UNIQUE NOT NULL ...

> https://sqlite.org/nulls.html
> https://sqlite.org/rowidtable.html
> https://sqlite.org/withoutrowid.html

> See especially 2 sub 4 in the latter.


This whole thread is good material for the new(ish) quirks page IMHO.
As Keith points out, most of the material exists in the doc already, but
IMHO the quirks page should be the one-stop page to learn about all
those historical or by-design particularities of SQLite, with a short
explanation
and pointers to other doc places with more details. Just my $0.02 of course
:)

--DD
___
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] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...

[Thomas Kurz]

> You might prefer adding an explicit NOT NULL on both "client" and "salesman" 
> columns.
> There is an historical reason why SQLite accepts NULL for primary key 
> column(s).

Ok, thanks for the hint, I didn't know that either. But it is a very odd 
behavior, because PRIMARY KEY per definition doesn't mean anything else than 
UNIQUE NOT NULL.

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

[sqlite] modify table (again)

[Thomas Kurz]

Dear all,

I really followed the 12-step ALTER TABLE schema and stumbled upon the 
following problem:

PRAGMA foreign_keys=1;
CREATE TABLE A (id INTEGER PRIMARY KEY, v1 TEXT, v2 INTEGER);
CREATE TABLE B (id INTEGER PRIMARY KEY, ref REFERENCES A(id));
INSERT INTO A (v1, v2) VALUES ('test7', 7);
INSERT INTO A (v1, v2) VALUES ('test123', 123);
INSERT INTO B (ref) VALUES (2);
CREATE VIEW v AS SELECT B.id, A.v1, A.v2 FROM B LEFT JOIN A ON B.ref=A.id;
-- modify table A now
PRAGMA foreign_keys=0; -- step 1
BEGIN TRANSACTION; -- step 2
-- skip step 3, no indexes and triggers
CREATE TABLE new_a (id INTEGER PRIMARY KEY, v1 TEXT, v2 INTEGER, v3 TEXT); -- 
step 4
INSERT INTO new_a SELECT id, v1, v2, 'new' FROM A; -- step 5
DROP TABLE A; -- step 6
ALTER TABLE new_a RENAME TO A; -- step 7
-- skip step 8, no indexes and triggers
-- skip step 9, the view should not be affected (referenced columns stay the 
same)
PRAGMA foreign_key_check; -- step 10
COMMIT; -- step 11
PRAGMA foreign_keys=1; -- step 12

Result with version 3.28.0:
Error: near line 15: error in view v: no such table: main.A 
(i.e. step 7 fails)

(Note that this is only an example, I know that I could use ADD COLUMN if I 
wanted to add a column. Indeed, I want to change a column constraint.)

However, this works (but does not correspond to the schema from 
https://www.sqlite.org/lang_altertable.html):
BEGIN TRANSACTION;
CREATE TABLE tmp AS SELECT * FROM A;
DROP TABLE A;
CREATE TABLE A (id INTEGER PRIMARY KEY, v1 TEXT, v2 INTEGER, v3 TEXT);
INSERT INTO A SELECT id, v1, v2, 'new' FROM tmp;
DROP TABLE tmp;
PRAGMA foreign_key_check;
COMMIT;
PRAGMA foreign_keys=1;

I consider this a bug and would like to renew my request for full ALTER TABLE 
support. Imho it is unacceptable to have to go thru 12 steps only for adding a 
CHECK constraint to a column.

Kind regards,
Thomas

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

Re: [sqlite] wal

[Thomas Kurz]

> A WAL file left behind is a sign of a problem in the app which should be 
> corrected.

I have exactly this problem and don't like the SHM and WAL files being left 
behind. I have even tried "pragma wal_checkpoint(full)" before closing the 
connection, but there are still situations where the WAL file remains.

Is there anything else I can do ensure that these files are removed?

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

Re: [sqlite] Please explain multi-access behaviour to me

[Thomas Kurz]

This has been a very informative and helpful discussion. Thank you.

So have I understood correctly, that in an application, this kind of 
SQLITE_BUSY handling is sufficient:

BEGIN
UPDATE #1
SELECT #2
UPDATE #3
COMMIT <- check for busy here and retry only the commit on failure

And second, what is the best place for busy-handling when only reading? Examle:

BEGIN < sufficient here?
SELECT #1
SELECT #2
SELECT #3
ROLLBACK

Can I assume that if the "begin" succeeds, I have the right to perform all 
following selects without having to fear getting an SQLITE_BUSY? Or do I have 
to repeat the whole block?

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

Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

[Thomas Kurz]

> I'll be happy to eat my words if someone can produce a mathematical 
paper that argued for the inclusion of -0.0 in IEEE754 to serve a 
mathematical concept. It's a fault, not a feature.

There are indeed very few use cases. The most common one is dealing with water 
temperature. You can have water at 0 C and ice at 0 C, both states differ only 
by latent heat. It's one of the rare cases where you could 0 and -0 to 
distinguish between phases.

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

Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?

[Thomas Kurz]

> It would also be a file format change, rendering about 1e12 existing
database files obsolete.

Maybe, but maybe there could be some clever implementation which doesn't break 
compatibility. I don't know about the exact internals of how SQlite stores 
values in the file. But I think there must be some identifier that tells 
whether a value is binary, integer, or float. Wouldn't it be possible to store 
both values, binary float and decimal float, in such a way that older versions 
would just read the binary float and ignore the additional data? Then, newer 
versions could read either, according on whether PRAGMA DECIMAL_MATH=TRUE is 
set or not.

Just an idea, don't know whether this would be feasible or not.

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

Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?

[Thomas Kurz]

> For an SQL engine, the next-best-thing to strict binary IEEE754 is not
sloppy binary IEEE754, its probably strict decimal IEEE754.

That would be a *really great* improvement!

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

Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?

[Thomas Kurz]

In the historical documents of Sqlite4, there has been a note about 
"distinguish whether a number is exact or approximate" (or similar). Imho this 
information would be more useful than distinguishing between +/- 0.0.

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

Re: [sqlite] round function inconsistent

[Thomas Kurz]

>  INSERT INTO t1(a,b) VALUES(2,3.254893418589635);

But this is a different scenario. The value is already stored in the database 
as 3.255.


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

Re: [sqlite] round function inconsistent

[Thomas Kurz]

Sorry, I was too fast with sending.

With the three values mentioned before:

a) 3.255
b) 3.254999
c) 3.254893418589635

Both SQLite and MySQL (however, I used MariaDB) return these values on a simple 
SELECT b:

a) 3.255
b) 3.254999
c) 3.255


And ROUND(b,2) returns:

a) 3.26 for MariaDB, 3.25 for SQLite
b) 3.25 for both
c) 3.26 for MariaDB, 3.25 for SQLite

For MariaDB, this is consistent, for SQlite, it ain't.


- Original Message - 
From: Richard Hipp 
To: SQLite mailing list 
Sent: Friday, May 24, 2019, 19:06:00
Subject: [sqlite] round function inconsistent

On 5/24/19, Jose Isaias Cabrera  wrote:


> FWIW, I went to sqlfiddle [1] and these are the answers for this SQL
> command:

> SELECT round(3.255,2), round(3.2548,2);

I also went to sqlfiddle and did a slightly more realistic scenario:

  CREATE TABLE t1(a INT, b DOUBLE PRECISION);
  INSERT INTO t1(a,b) VALUES(1,3.255);
  INSERT INTO t1(a,b) VALUES(2,3.254893418589635);
  SELECT a, b FROM t1;

In other words, I made the value actually pass through the database.

For MySQL I got:

1 3.26
2 3.26

For SQL Server I got:

1 3.25
2 3.25

The query does not work on PostgreSQL, because PG wisely prohibits
using the two-argument around() function on binary floating-point
values, perhaps to prevent discussions such as this one.  In order to
get this to work on PG I had to modify the query as follows:

   SELECT a, round(CAST(b AS NUMERIC),2) from t1

And the result is then:

1 3.26
2 3.26

-- 
D. Richard Hipp
d...@sqlite.org
___
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] round function inconsistent

[Thomas Kurz]

Sorry, but even Excel (which usually isn't very good at decimal math) gives 
correct results:

ROUND(3.255;2) --> 3.26
ROUND(3.254999;2) --> 3.25

Yours is clearly incorrect.


- Original Message - 
From: Richard Hipp 
To: SQLite mailing list 
Sent: Friday, May 24, 2019, 14:44:52
Subject: [sqlite] round function inconsistent

On 5/24/19, Jose Isaias Cabrera  wrote:

> Dr. Hipp, how many more scenarios, where round gives the wrong answer,
> exist?  Thanks.


Consider these two queries:

   SELECT round(3.255,2);
   SELECT round(3.2548,2);

Do you expect them to give different answers?

If so, do you realize that 3.255 and 3.48 are in fact the
exact same floating point number?  That number in (unambiguous) hex
notation is 0x1.a0a3d70a3d70ap+1. So how is it that you would expect
the round() function to return different answers for two cases where
it is given bit-for-bit identical inputs?  How does it know which
answer to give?

-- 
D. Richard Hipp
d...@sqlite.org
___
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] SQL Features That SQLite Does Not Implement

[Thomas Kurz]

This doesn't solve the problem. The database must work regardless of whether it 
is used within my own app or any other database viewer that might not have the 
extension functions available. In the first case, medians, standard deviations, 
etc. are included in the view, in the second case the values should be just 
NULL.


- Original Message - 
From: Simon Slavin 
To: SQLite mailing list 
Sent: Thursday, May 23, 2019, 12:34:39
Subject: [sqlite] SQL Features That SQLite Does Not Implement

On 23 May 2019, at 7:57am, Thomas Kurz  wrote:

> CREATE VIEW foo AS SELECT {if has stddev then stddev(...) else null} FROM ...

'if' in SQL language is CASE.

Near the beginning of your code, try to execute a function that contains 
stddev().  Make a note of whether it compiles without errors or not.  If it 
compiles, then the function is available.  Store this boolean somewhere and use 
it later on in your code.

What you propose doing: creating a library call which works differently 
depending on whether stddev() is available just leads to infinite regress.  If 
you create such a library call then you'll want to create another library to 
tell whether /your/ library call is available.
___
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] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

[Thomas Kurz]

This ain't quite helpful. Sqlite also supports FTS3/4/5 which I would 
personally consider as bloat. So what is the actual "bloat" in supporting a 
function that can just check whether a function exists or not. That would 
probably add only some bytes to the library.


- Original Message - 
From: R Smith 
To: sqlite-users@mailinglists.sqlite.org 
Sent: Thursday, May 23, 2019, 12:46:52
Subject: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

Ok, but you can't have it both ways. Either you want to write generic 
use-everywhere SQL, in which case you need to stick to the generic 
included-everywhere functions,

OR

You want to write specialized specific queries that use either your own 
or other UDF's, in which case you must be able to load your own (or 
other's) add-on libraries.

If you think a construct like "SELECT COALESCE(myFunc(xx), 0);" which 
returns the function result, unless it errors out, in which case it 
returns Zero, is good programming, then you are making a first-principle 
mistake. Because now you add a layer of complexity to the end-system. 
The result is Zero - Is this an error, or is this actually the result? 
Extra checks needed.

Errors should cause errors so that everyone know it's an error. 
Silencing errors is a very Microsoftian Gooey (to quote Keith) way of 
thinking and a mistake often made by novice GUI programmers who value 
user-experience over truth.

Else what you are asking for is to have your bread buttered on both 
sides, which isn't feasible, and which isn't done in any RDBMS engine 
(though admittedly the big ones do include most general math functions 
as standard, but then they don't need to sometimes run on embedded devices).

This is SQLite. Perhaps some of us could collaborate on a fork called 
SQLbloat and put out standard libs/code/precompileds for versions of 
sqlite with everything - bbq sauce and all, for when you don't need Lite 
- then you can specify that the queries are for SQLbloat and they will 
run out the box directly and correctly.  Linux distro wars coming to an 
SQLite DB near you soon!


Cheers,
Ryan


On 2019/05/23 12:19 PM, Thomas Kurz wrote:
> That doesn't make any difference. Then I could use the extensions-functions.c 
> loadable module as well. My database has to work equally well no matter what 
> dll and/or extension is used or not.

> Regardless of my application, the problem stays the same when you open the 
> database in any other viewer that doesn't have the extension.




> - Original Message -
> From: Hick Gunter 
> To: 'SQLite mailing list' 
> Sent: Thursday, May 23, 2019, 11:58:10
> Subject: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

> Put your function into a loadable extension and load it during application 
> startup so that it is always available to your code. This does not require 
> checking the sqlite3.dll

> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
> Auftrag von Thomas Kurz
> Gesendet: Donnerstag, 23. Mai 2019 11:41
> An: SQLite mailing list 
> Betreff: Re: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not 
> Implement

> I want to define VIEWs that work equally well regardless of whether a default 
> sqlite3.dll or a custom build with built-in extension-functions.c is used. 
> Defining my own function as an extension module which checks that case would 
> additionally require checking whether a default sqlite3.dll or a custom build 
> is used? That doesn't make any sense to me?!?


> - Original Message -
> From: Hick Gunter 
> To: 'SQLite mailing list' 
> Sent: Thursday, May 23, 2019, 10:02:30
> Subject: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

> Just write a function that takes a function name, a default value and an 
> unspecified number of arguments.

> function_present(,[,])

> The implementation will then check if the named function is available; If so, 
> prepare, execute and return the result of "SELECT ()"
> If not, just return .

> This is probably going to make your queries run blindingly slow...

> BTW, what is your use case?

> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
> Auftrag von Thomas Kurz
> Gesendet: Donnerstag, 23. Mai 2019 08:58
> An: SQLite mailing list 
> Betreff: [EXTERNAL] Re: [sqlite] SQL Features That SQLite Does Not Implement

> Ok, thank you for that hint. But it is still very unconvenient. How can I 
> define a view based on your suggestion? I want to have something like

> CREATE VIEW foo AS SELECT {if has stddev then stddev(...) else null} FROM ...

> - Original Message -
> From: Keith Medcalf 
> To: SQLite mailing list 
> Sent: Thursday, May 23, 2019, 07:1

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

[Thomas Kurz]

That doesn't make any difference. Then I could use the extensions-functions.c 
loadable module as well. My database has to work equally well no matter what 
dll and/or extension is used or not.

Regardless of my application, the problem stays the same when you open the 
database in any other viewer that doesn't have the extension.




- Original Message - 
From: Hick Gunter 
To: 'SQLite mailing list' 
Sent: Thursday, May 23, 2019, 11:58:10
Subject: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

Put your function into a loadable extension and load it during application 
startup so that it is always available to your code. This does not require 
checking the sqlite3.dll

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Thomas Kurz
Gesendet: Donnerstag, 23. Mai 2019 11:41
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

I want to define VIEWs that work equally well regardless of whether a default 
sqlite3.dll or a custom build with built-in extension-functions.c is used. 
Defining my own function as an extension module which checks that case would 
additionally require checking whether a default sqlite3.dll or a custom build 
is used? That doesn't make any sense to me?!?


- Original Message -
From: Hick Gunter 
To: 'SQLite mailing list' 
Sent: Thursday, May 23, 2019, 10:02:30
Subject: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

Just write a function that takes a function name, a default value and an 
unspecified number of arguments.

function_present(,[,])

The implementation will then check if the named function is available; If so, 
prepare, execute and return the result of "SELECT ()"
If not, just return .

This is probably going to make your queries run blindingly slow...

BTW, what is your use case?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Thomas Kurz
Gesendet: Donnerstag, 23. Mai 2019 08:58
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] SQL Features That SQLite Does Not Implement

Ok, thank you for that hint. But it is still very unconvenient. How can I 
define a view based on your suggestion? I want to have something like

CREATE VIEW foo AS SELECT {if has stddev then stddev(...) else null} FROM ...

- Original Message -
From: Keith Medcalf 
To: SQLite mailing list 
Sent: Thursday, May 23, 2019, 07:18:45
Subject: [sqlite] SQL Features That SQLite Does Not Implement


select name from pragma_function_list where name == 'M_Pi' collate nocase;

returns the name of the function if it exists.  See pragma function_list

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Thomas Kurz
>Sent: Wednesday, 22 May, 2019 22:19
>To: SQLite mailing list
>Subject: Re: [sqlite] SQL Features That SQLite Does Not Implement

>I agree in that not every math function can be included by default.
>My problem, however, is that I cannot know whether a user uses my
>self-compiled version with built-in extension-functions.c, or a
>downloaded version from sqlite.org.

>It would be very, very helpful (especially regarding views!) to have
>some kind of a "check function" so that one could write

>SELECT CASE WHEN defined('stddev') THEN stddev(foo) ELSE NULL END FROM
>bar


>- Original Message -
>From: Keith Medcalf 
>To: SQLite mailing list 
>Sent: Wednesday, May 22, 2019, 22:20:11
>Subject: [sqlite] SQL Features That SQLite Does Not Implement


>On Wednesday, 22 May, 2019 11:51, sky5w...@gmail.com wrote:

>>Please add a note to the omitted page that many basic math functions
>>are NOT supported. (sqrt,mod,power,stdev,etc.)

>Traditionally "math library" functions provided by the various language
>runtimes were not included becase this would introduce dependancies on
>a "math library".  While this is available on *most* platforms, it is
>not available on *all* platforms which SQLite3 can be compiled for out
>of the box.  Furthermore the implementation of some transcendentals may
>be intrinsic on some CPU's and require huge amounts of library code on
>others.  Statistical functions are not included because, well, they
>require complex implementations to get right.

>Moreover, even the builtin functions are "lite" (the round function
>does not round properly for instance (it does grade-school 4/5 rounding
>rather than half-even rounding), the average function is rather simple
>in implementation and suffers from trivally triggered sources of
>computational error (it uses sum/count rather than

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

[Thomas Kurz]

I want to define VIEWs that work equally well regardless of whether a default 
sqlite3.dll or a custom build with built-in extension-functions.c is used. 
Defining my own function as an extension module which checks that case would 
additionally require checking whether a default sqlite3.dll or a custom build 
is used? That doesn't make any sense to me?!?


- Original Message - 
From: Hick Gunter 
To: 'SQLite mailing list' 
Sent: Thursday, May 23, 2019, 10:02:30
Subject: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

Just write a function that takes a function name, a default value and an 
unspecified number of arguments.

function_present(,[,])

The implementation will then check if the named function is available;
If so, prepare, execute and return the result of "SELECT ()"
If not, just return .

This is probably going to make your queries run blindingly slow...

BTW, what is your use case?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Thomas Kurz
Gesendet: Donnerstag, 23. Mai 2019 08:58
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] SQL Features That SQLite Does Not Implement

Ok, thank you for that hint. But it is still very unconvenient. How can I 
define a view based on your suggestion? I want to have something like

CREATE VIEW foo AS SELECT {if has stddev then stddev(...) else null} FROM ...

- Original Message -
From: Keith Medcalf 
To: SQLite mailing list 
Sent: Thursday, May 23, 2019, 07:18:45
Subject: [sqlite] SQL Features That SQLite Does Not Implement


select name from pragma_function_list where name == 'M_Pi' collate nocase;

returns the name of the function if it exists.  See pragma function_list

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Thomas Kurz
>Sent: Wednesday, 22 May, 2019 22:19
>To: SQLite mailing list
>Subject: Re: [sqlite] SQL Features That SQLite Does Not Implement

>I agree in that not every math function can be included by default.
>My problem, however, is that I cannot know whether a user uses my
>self-compiled version with built-in extension-functions.c, or a
>downloaded version from sqlite.org.

>It would be very, very helpful (especially regarding views!) to have
>some kind of a "check function" so that one could write

>SELECT CASE WHEN defined('stddev') THEN stddev(foo) ELSE NULL END
>FROM bar


>- Original Message -
>From: Keith Medcalf 
>To: SQLite mailing list 
>Sent: Wednesday, May 22, 2019, 22:20:11
>Subject: [sqlite] SQL Features That SQLite Does Not Implement


>On Wednesday, 22 May, 2019 11:51, sky5w...@gmail.com wrote:

>>Please add a note to the omitted page that many basic math functions
>>are NOT supported. (sqrt,mod,power,stdev,etc.)

>Traditionally "math library" functions provided by the various
>language runtimes were not included becase this would introduce
>dependancies on a "math library".  While this is available on *most*
>platforms, it is not available on *all* platforms which SQLite3 can
>be compiled for out of the box.  Furthermore the implementation of
>some transcendentals may be intrinsic on some CPU's and require huge
>amounts of library code on others.  Statistical functions are not
>included because, well, they require complex implementations to get
>right.

>Moreover, even the builtin functions are "lite" (the round function
>does not round properly for instance (it does grade-school 4/5
>rounding rather than half-even rounding), the average function is
>rather simple in implementation and suffers from trivally triggered
>sources of computational error (it uses sum/count rather than
>successive approximation to the mean), and many other limitations
>exist in the builtin implementations of many functions).

>All of these issues can be "fixed" however, all you need to do is add
>the necessary code via the extension mechanism to add whatever
>functionality you require using whatever numerical methods you
>determine are suitable for your needs.  For example, I have added
>default support via the extension mechanism (and the EXTRA_INIT hook)
>to make all the distributed extensions available on every connection,
>to add all the standard platform math functions, to add a bunch of
>statistical functions, several platform APIs (Windows in this case),
>and to "fix" the builtin round, datetime (to include proper support
>for instant times and timezone manipulation using the standard IANA
>timezone database), and added support for basic Unicode nocase and
>noaccent collations without using the who

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

[Thomas Kurz]

Ok, thank you for that hint. But it is still very unconvenient. How can I 
define a view based on your suggestion? I want to have something like

CREATE VIEW foo AS SELECT {if has stddev then stddev(...) else null} FROM ...

- Original Message - 
From: Keith Medcalf 
To: SQLite mailing list 
Sent: Thursday, May 23, 2019, 07:18:45
Subject: [sqlite] SQL Features That SQLite Does Not Implement


select name from pragma_function_list where name == 'M_Pi' collate nocase;

returns the name of the function if it exists.  See pragma function_list

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Thomas Kurz
>Sent: Wednesday, 22 May, 2019 22:19
>To: SQLite mailing list
>Subject: Re: [sqlite] SQL Features That SQLite Does Not Implement

>I agree in that not every math function can be included by default.
>My problem, however, is that I cannot know whether a user uses my
>self-compiled version with built-in extension-functions.c, or a
>downloaded version from sqlite.org.

>It would be very, very helpful (especially regarding views!) to have
>some kind of a "check function" so that one could write

>SELECT CASE WHEN defined('stddev') THEN stddev(foo) ELSE NULL END
>FROM bar


>- Original Message -
>From: Keith Medcalf 
>To: SQLite mailing list 
>Sent: Wednesday, May 22, 2019, 22:20:11
>Subject: [sqlite] SQL Features That SQLite Does Not Implement


>On Wednesday, 22 May, 2019 11:51, sky5w...@gmail.com wrote:

>>Please add a note to the omitted page that many basic math functions
>>are NOT supported. (sqrt,mod,power,stdev,etc.)

>Traditionally "math library" functions provided by the various
>language runtimes were not included becase this would introduce
>dependancies on a "math library".  While this is available on *most*
>platforms, it is not available on *all* platforms which SQLite3 can
>be compiled for out of the box.  Furthermore the implementation of
>some transcendentals may be intrinsic on some CPU's and require huge
>amounts of library code on others.  Statistical functions are not
>included because, well, they require complex implementations to get
>right.

>Moreover, even the builtin functions are "lite" (the round function
>does not round properly for instance (it does grade-school 4/5
>rounding rather than half-even rounding), the average function is
>rather simple in implementation and suffers from trivally triggered
>sources of computational error (it uses sum/count rather than
>successive approximation to the mean), and many other limitations
>exist in the builtin implementations of many functions).

>All of these issues can be "fixed" however, all you need to do is add
>the necessary code via the extension mechanism to add whatever
>functionality you require using whatever numerical methods you
>determine are suitable for your needs.  For example, I have added
>default support via the extension mechanism (and the EXTRA_INIT hook)
>to make all the distributed extensions available on every connection,
>to add all the standard platform math functions, to add a bunch of
>statistical functions, several platform APIs (Windows in this case),
>and to "fix" the builtin round, datetime (to include proper support
>for instant times and timezone manipulation using the standard IANA
>timezone database), and added support for basic Unicode nocase and
>noaccent collations without using the whole ICU library.

>The downside of this is that the implementation of all these
>"goodies" quadruples the size of the base engine code (sqlite3.obj)
>and it is no longer "Lite".  There are other drawbacks as well.  For
>example, it is difficult to make many advanced numerical calculation
>methods (aggregates) compatible with window functions as currently
>implemented.

>About the only thing that is missing from SQLite3 is the ability to
>declare and implement "user defined types" in a fully integrated way
>(such as was added to DB2 back in the late 80's early 90's, and which
>I do not think anyone else has implemented as nicely anywhere else).

>Really, the issue is that SQLite3 is an SQL based relational storage
>manager, and it implements this function very well.  It does not
>provide a huge array of accoutrements that you may see with other
>more ex$pen$ive RDMS systems, but does provide the ability to add
>(most of) those accoutrements if you wish.

>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.





>__

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

[Thomas Kurz]

> exact numeric representations.  

+1 for that as had already been in consideration for version 4

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

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

[Thomas Kurz]

I agree in that not every math function can be included by default. My problem, 
however, is that I cannot know whether a user uses my self-compiled version 
with built-in extension-functions.c, or a downloaded version from sqlite.org.

It would be very, very helpful (especially regarding views!) to have some kind 
of a "check function" so that one could write

SELECT CASE WHEN defined('stddev') THEN stddev(foo) ELSE NULL END FROM bar


- Original Message - 
From: Keith Medcalf 
To: SQLite mailing list 
Sent: Wednesday, May 22, 2019, 22:20:11
Subject: [sqlite] SQL Features That SQLite Does Not Implement


On Wednesday, 22 May, 2019 11:51, sky5w...@gmail.com wrote:

>Please add a note to the omitted page that many basic math functions
>are NOT supported. (sqrt,mod,power,stdev,etc.)

Traditionally "math library" functions provided by the various language 
runtimes were not included becase this would introduce dependancies on a "math 
library".  While this is available on *most* platforms, it is not available on 
*all* platforms which SQLite3 can be compiled for out of the box.  Furthermore 
the implementation of some transcendentals may be intrinsic on some CPU's and 
require huge amounts of library code on others.  Statistical functions are not 
included because, well, they require complex implementations to get right.

Moreover, even the builtin functions are "lite" (the round function does not 
round properly for instance (it does grade-school 4/5 rounding rather than 
half-even rounding), the average function is rather simple in implementation 
and suffers from trivally triggered sources of computational error (it uses 
sum/count rather than successive approximation to the mean), and many other 
limitations exist in the builtin implementations of many functions).

All of these issues can be "fixed" however, all you need to do is add the 
necessary code via the extension mechanism to add whatever functionality you 
require using whatever numerical methods you determine are suitable for your 
needs.  For example, I have added default support via the extension mechanism 
(and the EXTRA_INIT hook) to make all the distributed extensions available on 
every connection, to add all the standard platform math functions, to add a 
bunch of statistical functions, several platform APIs (Windows in this case), 
and to "fix" the builtin round, datetime (to include proper support for instant 
times and timezone manipulation using the standard IANA timezone database), and 
added support for basic Unicode nocase and noaccent collations without using 
the whole ICU library.

The downside of this is that the implementation of all these "goodies" 
quadruples the size of the base engine code (sqlite3.obj) and it is no longer 
"Lite".  There are other drawbacks as well.  For example, it is difficult to 
make many advanced numerical calculation methods (aggregates) compatible with 
window functions as currently implemented.

About the only thing that is missing from SQLite3 is the ability to declare and 
implement "user defined types" in a fully integrated way (such as was added to 
DB2 back in the late 80's early 90's, and which I do not think anyone else has 
implemented as nicely anywhere else).

Really, the issue is that SQLite3 is an SQL based relational storage manager, 
and it implements this function very well.  It does not provide a huge array of 
accoutrements that you may see with other more ex$pen$ive RDMS systems, but 
does provide the ability to add (most of) those accoutrements if you wish.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.





___
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] SQL Features That SQLite Does Not Implement

[Thomas Kurz]

Before starting to support SQL2016 features, I would suggest support for 
missing features of older SQL standard versions first ;)

- Original Message - 
From: sky5w...@gmail.com 
To: SQLite mailing list 
Sent: Wednesday, May 22, 2019, 21:29:40
Subject: [sqlite] SQL Features That SQLite Does Not Implement

Yes, I expected some pushback. However, my post was to save the next person
having to search for answers. I did not expect to requote the top line of
the page I listed?
"SQLite implements most of the common features of SQL. Rather than try to
list all the features of SQL that SQLite does support, *it is much easier
to list those that it does not.* Unsupported features of SQL are shown
below."

I am asking for a followup to the missing features list.
Similar to this: https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016

Yes, I am aware of the extension capabilities and will augment them with my
own.
Expect more questions in that effort. ;)
___
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] SQLite with single writer on Windows network share

[Thomas Kurz]

> How about you give up on the idea of using Windows shares to distribute a 
> SQLite DB and use a tool meant for the job, such as BedrockDB?

BedrockDB is recommended here now and then, and it sounds interesting indeed. 
However, it's not available for Windows. (This should always be noted when 
recommending BedrockDB, imho)

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

Re: [sqlite] Getting the week of the month from strftime or date functions

[Thomas Kurz]

> What about just sticking with the ISO week definition?
>
> https://en.wikipedia.org/wiki/ISO_week_date

From the document you cited:

"The ISO standard does not define any association of weeks to months."

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

Re: [sqlite] Getting the week of the month from strftime or date functions

[Thomas Kurz]

I think "week of the month" is not a standard value. As with week of the year, 
is week #1 the week in which the month starts, the first complete week within 
the month, or the first week with at least 4 days?


- Original Message - 
From: Jose Isaias Cabrera 
To: sqlite-users@mailinglists.sqlite.org 
Sent: Thursday, May 2, 2019, 21:44:44
Subject: [sqlite] Getting the week of the month from strftime or date functions


Greetings.

To break Manuel's constant bug finding emails, :-), I want to get the week of 
the month from either date or strftime functions.  I know I can get the week of 
the year by doing,

 SELECT strftime('%W','2019-03-07');

but I need to get the week of that month based on the date.  I can write a 
quick function to do it, but I thought there was an option for it, but I 
couldn't find it in the help site[1].  Is there such a choice?

Thanks.

[1] https://www.sqlite.org/lang_datefunc.html

SQLite Query Language: Date And Time 
Functions
The only reasons for providing functions other than strftime() is for 
convenience and for efficiency. Time Strings. A time string can be in any of 
the following formats:
www.sqlite.org


___
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] ANN: SQLite3 Decimal Extension

[Thomas Kurz]

I appreciate your effort towards this extension. In my opinion, however, this 
is (along with bigint-support) a feature that belongs into core (for that 
reason alone to get math operations, comparisons, aggregates, etc. working in 
an intuitive way).

Years ago, for SQLite4, there seem to have been plans for "decimal math". The 
concept looked very useful and I'd suggest taking that idea up again, but this 
time for SQLite3 ;-)




- Original Message - 
From: Lifepillar 
To: sqlite-users@mailinglists.sqlite.org 
Sent: Wednesday, April 3, 2019, 14:30:52
Subject: [sqlite] ANN: SQLite3 Decimal Extension

[I hope that this kind of announcement is not off-topic here]

SQLite3 Decimal is an extension implementing exact decimal arithmetic
for SQLite3. It is currently unfinished and under development.  At this
point anything, including the user interface and the internals, is
subject to change. I am publishing it early because I am seeking to get
as much feedback as possible to "get it right".

The extension is currently using the excellent decNumber library, but it
does not aim to become an IEEE 754 conforming implementation. It is also
totally unrelated to SQL decimal/numeric types.

Decimals are stored as blobs and the storage format is a minor variant
of decimalInfinite (https://arxiv.org/abs/1506.01598). That is a totally
ordered encoding, so decimals can be compared directly (memcmp()) and
also indexed (does SQLite support indexes on blobs?).

[Here, I must thank Dr. Hipp, with whom I had a brief email exchange
severals moons ago, who convinced me that the IEEE 754 encoding was not
an ideal storage format for databases]

By default, the precision is limited to 39 digits and exponents must be
in the range [-99,999,999,+99,999,999] (for some mathematical
operations, the exponent must not exceed 99,999 in absolute value). Such
parameters may be configured at compile time and also changed at
runtime. Any integer or fractional number satisfying such requirements
can be manipulated. Note that the on-disk representation is *not*
subject to such limits and can accommodate arbitrarily small or
arbitrarily large decimals. With the defaults just mentioned, a decimal
occupies between 1 and 24 bytes on disk (plus any overhead that blobs
may add).

As I have said, I welcome any feedback, from the super-technical to the
end-user oriented. There is no manual so far, but the code is mostly
documented. You may find a sample session in the repository's home page.

Also, this is my first public Fossil repository: if you have any
suggestions on how I should improve its configuration, let me know.

So, here for the adventurous ones:

Official repository:

https://chiselapp.com/user/lifepillar/repository/sqlite3decimal

Git mirror (which exists only for testing `fossil git export`...):

https://github.com/lifepillar/sqlite3decimal-mirror

Enjoy,
Life.


___
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] Row locking sqlite3

[Thomas Kurz]

I apologize that I am currently unable to reproduce the problem. The files I 
have just tested at home (same schema as the one I had in the office this week) 
behave as expected (i.e. no difference with or without transaction). I will try 
again in the office next week. If I can find the database with which I observed 
this issue, I will send it to you.


- Original Message - 
From: Dan Kennedy 
To: sqlite-users@mailinglists.sqlite.org 
Sent: Friday, March 29, 2019, 19:33:51
Subject: [sqlite] Row locking sqlite3


On 28/3/62 01:04, Thomas Kurz wrote:
>> I wonder whether SQLite is treating each DELETE as a single transaction.  
>> Could you try wrapping the main delete in BEGIN ... END and see whether that 
>> speeds up the cascaded DELETE ?  Would you be able to find timings (either 
>> in your code or in the command-line tool) and tell us whether it's the 
>> DELETE or the END which takes the time ?
> Ok, well very interesting and I'd never have had this idea, but indeed it 
> works: within a transaction, it takes only a few seconds. This is very 
> surprising as to me, a single DELETE statement is nothing more than that: a 
> single atomic operation which should automatically be treated as a 
> transaction (auto-commit-mode).

> *confused*


Me too. For the BEGIN/COMMIT version, you're counting the time spent in 
COMMIT as well, correct?

If this is repeatable, we'd be very interesting in figuring out what is 
going on.

Dan.



___
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] UPSERT with multiple constraints

[Thomas Kurz]

You are right. This is indeed a situation that I didn't have in mind.

I will rethink the data design. Thank you very much for this comment and also 
to all others which gave me valuable ideas on how to handle my conflict.

- Original Message - 
From: James K. Lowden 
To: sqlite-users@mailinglists.sqlite.org 
Sent: Friday, March 29, 2019, 17:03:09
Subject: [sqlite] UPSERT with multiple constraints

On Wed, 27 Mar 2019 23:59:47 +0100
Thomas Kurz  wrote:

> Sure. I have a table of items. Each item has a type, a name, and
> properties A, B, C (and some more, but they're not relevant here).

> I want to enforce ...  UNIQUE (type, name). 
... 
> Furthermore, items of a certain type that have identical properties
> A, B, C are also considered equal, regardless of their name: UNIQUE
> (type, A, B, C).
... 
> Now when inserting an item that already exists (according to the
> uniqueness definition above), the existing item should be updated
> with the new name and A, B, C properties.

IIUC, by "upsert" you mean that for a new row matching an existing row
on {type, A, B, C}, instead of inserting the new row, you want to update
the existing row with the new row's name.  Unless, that is, the new row
would then conflict with (i.e., match) a different row on {type, name},
in which case the update fails.  If no row matches either criteria, you
want to insert the row.  

So why not use SQL to do that as designed, instead of relying on the
strange upsert?  

insert into T values ( 'type', 'name', 'a', 'b', 'c' )
where not exists ( select 1 from T
where type = 'type' and name = 'name' 
or   A = 'a' and B = 'b' and C = 'c'
);

update T set name = 'name'
where A = 'a' and B = 'b' and C = 'c';


For efficiency you can check that the first insert affected zero rows
before updating, but that's not strictly necessary.  

If there's a possibility of other processes updating the database
between the two statements, wrap them in a transaction.  

--jkl
___
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] UPSERT with multiple constraints

[Thomas Kurz]

> Can I ask what it is that you're trying to do ?  This smacks of trying to add 
> 1 to an existing value or something like that.

Sure. I have a table of items. Each item has a type, a name, and properties A, 
B, C (and some more, but they're not relevant here).

I want to enforce that items of a certain type and name are unique: UNIQUE 
(type, name). But there can be multiple items with the same name as long as 
they are of different types.

Furthermore, items of a certain type that have identical properties A, B, C are 
also considered equal, regardless of their name: UNIQUE (type, A, B, C).

I cannot use UNIQUE (type, name, A, B, C), as this would mean that there can be 
two items with the same A, B, C (and type, of course), but different name. On 
the other hand, there could be two items with the same same (and type, of 
course) but different A, B, C.

Now when inserting an item that already exists (according to the uniqueness 
definition above), the existing item should be updated with the new name and A, 
B, C properties.

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

Re: [sqlite] Row locking sqlite3

[Thomas Kurz]

Integrity check is ok. I'm deleting using primary keys only, so it shouldn't be 
an index problem either.

- Original Message - 
From: Simon Slavin 
To: SQLite mailing list 
Sent: Wednesday, March 27, 2019, 19:25:17
Subject: [sqlite] Row locking sqlite3

On 27 Mar 2019, at 6:04pm, Thomas Kurz  wrote:

> Ok, well very interesting and I'd never have had this idea, but indeed it 
> works: within a transaction, it takes only a few seconds. This is very 
> surprising as to me, a single DELETE statement is nothing more than that: a 
> single atomic operation which should automatically be treated as a 
> transaction (auto-commit-mode).

I don't think this is the problem.  I think it's more likely the other problem 
I mentioned: difficulty in finding which cascade rows to delete.  Did you run 
your integrity check and make sure you had an appropriate UNIQUE index (or that 
you were keying on your primary key) ?

Simon.
___
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] Row locking sqlite3

[Thomas Kurz]

> I wonder whether SQLite is treating each DELETE as a single transaction.  
> Could you try wrapping the main delete in BEGIN ... END and see whether that 
> speeds up the cascaded DELETE ?  Would you be able to find timings (either in 
> your code or in the command-line tool) and tell us whether it's the DELETE or 
> the END which takes the time ?

Ok, well very interesting and I'd never have had this idea, but indeed it 
works: within a transaction, it takes only a few seconds. This is very 
surprising as to me, a single DELETE statement is nothing more than that: a 
single atomic operation which should automatically be treated as a transaction 
(auto-commit-mode).

*confused*

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

[sqlite] UPSERT with multiple constraints

[Thomas Kurz]

Dear all,

I have a table with multiple (in this case 2) UNIQUE constraints:

UNIQUE (col1, col2)
UNIQUE (col1, col3, col4, col5)

Is it possible to use UPSERT twice? I have already tried some statements, but 
neither of these were successful. This is what I want to achieve:

INSERT INTO ... ON CONFLICT DO UPDATE SET ...

So the update should occur no matter which UNIQUE-constraint would be violated 
by the insert.

I've also tried ON CONFLICT (col1, col2, col3, col4, col5) which is rejected 
("does not match any UNIQUE constraint"). The error message is perfectly 
correct, but doesn't solve my problem ;-)

So what I'm looking for is some kind of "ON CONFLICT (col1, col2) OR CONFLICT 
(col1, col3, col4, col5) DO UPDATE".

Any hints for me?

Kind regards,
Thomas

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

Re: [sqlite] Row locking sqlite3

[Thomas Kurz]

Imho quite simple: There are operations that take a long time. I observe this 
behavior especially with DELETE in combination with ON CASCADE DELETE. Can take 
half an hour, and meanwhile the database remains locked.


- Original Message - 
From: Simon Slavin 
To: SQLite mailing list 
Sent: Wednesday, March 27, 2019, 12:14:15
Subject: [sqlite] Row locking sqlite3

The other aspect of this is to wonder why OP wants row locking.  They might 
think that it's faster than locking the whole database, or that it will allow 
their program to work without any pauses.  Neither of these are necessarily 
true.
___
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] read-only database in WAL mode and temporary files

[Thomas Kurz]

But it's of zero size, so there cannot be anything inside. As far as I have 
understood, it's just a journal containing the changes since the last 
checkpoint. If I don't change anything, why should there be the need of this 
journal? I would expect the journal to be created when the first write 
operation (INSERT, DELETE, whatsoever) occurs, but not for a read operation.

And moreover, why is read-only-mode allowed to create a journal (this is 
contradictory to the read-only-idea), but not delete it upon closing the 
connection?

Actually, this leads to more problems: If the database itself has the 
read-only-attribute set (by filesystem or ACL), SQLite would start journaling 
changes which can be never be checkpointed. In commonsense, a write operation 
should immediately terminate and return an error in this case.

Finally, I have noticed the immutable mode, but I have a bad feeling about it. 
If I understand correctly, a second connection could still open the same 
database in write-mode. I would instead expect that a read-only-connection 
locked the database (using file locking mechanism) and a parallel 
write-connection to be blocked until the locked is released. (No temporary 
files should be required until this moment.)


- Original Message - 
From: Shawn Wagner 
To: SQLite mailing list 
Sent: Sunday, March 24, 2019, 11:29:58
Subject: [sqlite] read-only database in WAL mode and temporary files

Even a read only database needs to create the wal journal if it doesn't
exist at the moment for a database that uses that mode:
https://www.sqlite.org/wal.html#read_only_databases

You might look into the immutable option mentioned there and see if it's
appropriate for your needs.


On Sun, Mar 24, 2019, 2:33 AM Thomas Kurz  wrote:

> When I open a database in read-only mode (?mode=ro), I observe that the
> WAL and SHM temporary files are created anyway. Is there any possibility to
> prevent the creation of these files? Aren't they useless?

> Even worse (using sqlite3.exe version 3.27.1):

> .open test.db
> pragma journal_mode=wal;
> create table a (b integer);
> insert into a (1);
> .quit

> This creates database with WAL and SHM files; those files are correctly
> removed when exiting.

> .open --readonly test.db
> select * from a;   <--- creates WAL and SHM -- why?
> .quit

> Now, WAL and SHM files aren't deleted anymore.

> Proposal: Neither WAL nor SHM should be created at all when opening a
> WAL-mode database in read-only mode.

> ___
> 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

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

[sqlite] read-only database in WAL mode and temporary files

[Thomas Kurz]

When I open a database in read-only mode (?mode=ro), I observe that the WAL and 
SHM temporary files are created anyway. Is there any possibility to prevent the 
creation of these files? Aren't they useless?

Even worse (using sqlite3.exe version 3.27.1):

.open test.db
pragma journal_mode=wal;
create table a (b integer);
insert into a (1);
.quit

This creates database with WAL and SHM files; those files are correctly removed 
when exiting.

.open --readonly test.db
select * from a;   <--- creates WAL and SHM -- why?
.quit

Now, WAL and SHM files aren't deleted anymore.

Proposal: Neither WAL nor SHM should be created at all when opening a WAL-mode 
database in read-only mode.

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

Re: [sqlite] Row locking sqlite3

[Thomas Kurz]

Ah, great, thank you very much for this information.

- Original Message - 
From: Barry Smith 
To: SQLite mailing list 
Sent: Friday, March 22, 2019, 21:38:10
Subject: [sqlite] Row locking sqlite3

You might be interested in the BEGIN CONCURRENT branch. It does page level 
locking (not quite as granular as row level).

https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md

> On 22 Mar 2019, at 11:48 am, Thomas Kurz  wrote:

> This sounds interesting. I have some questions about:

>> Row lock information is shared with processes. If a process finished 
>> unexpectedly, unnecessary lock information might be stayed. In order to 
>> unlock them, please use sqlumdash_cleaner.exe which clears all record 
>> information. If there is a process which is in a transaction, 
>> sqlumdash_cleaner.exe should be called after end the transaction.

> - Where is row lock information stored? In database file, in journal file, in 
> WAL file, or in memory?
> - Why should the cleaner be called after the end of a transaction?
> - I don't like the idea of calling an external exe in case of problems (and 
> more than that, after every transaction??). Couldn't you introduce a PRAGMA 
> for unlocking rows?



> - Original Message - 
> From: Peng Yu 
> To: SQLite mailing list 
> Sent: Friday, March 22, 2019, 15:25:24
> Subject: [sqlite] Row locking sqlite3

> Hi,

> I see that sqlite3 still does not support row locking. This package
> tries to resolve this problem. But it does not have a standard build
> process for Linux.

> https://github.com/sqlumdash/sqlumdash/

> Are there other packages similar to sqlite3 but support row locking? 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
___
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] Row locking sqlite3

[Thomas Kurz]

This sounds interesting. I have some questions about:

> Row lock information is shared with processes. If a process finished 
> unexpectedly, unnecessary lock information might be stayed. In order to 
> unlock them, please use sqlumdash_cleaner.exe which clears all record 
> information. If there is a process which is in a transaction, 
> sqlumdash_cleaner.exe should be called after end the transaction.

- Where is row lock information stored? In database file, in journal file, in 
WAL file, or in memory?
- Why should the cleaner be called after the end of a transaction?
- I don't like the idea of calling an external exe in case of problems (and 
more than that, after every transaction??). Couldn't you introduce a PRAGMA for 
unlocking rows?



- Original Message - 
From: Peng Yu 
To: SQLite mailing list 
Sent: Friday, March 22, 2019, 15:25:24
Subject: [sqlite] Row locking sqlite3

Hi,

I see that sqlite3 still does not support row locking. This package
tries to resolve this problem. But it does not have a standard build
process for Linux.

https://github.com/sqlumdash/sqlumdash/

Are there other packages similar to sqlite3 but support row locking? 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] Feature suggestion / requesst

[Thomas Kurz]

May I ask whether this suggestion has been considered being added to SQlite?


- Original Message - 
From: Clemens Ladisch 
To: sqlite-users@mailinglists.sqlite.org 
Sent: Friday, June 8, 2018, 08:25:25
Subject: [sqlite] Feature suggestion / requesst

Hick Gunter wrote:
>> I've encountered a feature that I think would be awesome:
>> https://www.postgresql.org/docs/9.3/static/dml-returning.html

>> Example: INSERT INTO blah (this, that, another) VALUES (x, y, z) RETURNING 
>> id;

> What does this do if the INSERT creates multiple rows?

It returns multiple rows.  (Also useful with UPDATE, DELETE.)

> What about inserts generated from trigger programs?

The same as a SELECT in a trigger program.


Regards,
Clemens
___
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] Problems loading extensions on Windows 10

[Thomas Kurz]

Are both of the same architecture, either 32bit or 64bit?

- Original Message - 
From: Kyle 
To: sqlite-users@mailinglists.sqlite.org 
Sent: Tuesday, March 5, 2019, 23:30:35
Subject: [sqlite] Problems loading extensions on Windows 10

I am having problems loading sqlite3 extensions on Windows 10.

I have downloaded both sqlite3 and mod_spatialite.dll (the extension) and
copied them to C:\Windows\System32.

When I run
SELECT load_extension('mod_spatialite')
sqlite returns "Error: the specified module could not be found"

I have also tried
SELECT load_extension('C:\Windows\System32\mod_spatialite.dll')
which returns the same error.

These commands work perfectly fine in Ubuntu and the extension loads with
no problems. What do I need to do differently on Windows?
___
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] inconsistent behavior when creating INTEGER NOT NULL column without DEFAULT?

[Thomas Kurz]

> I guess a missing DEFAULT automatically implies DEFAULT NULL, so the behavior 
> of ALTER should be correct whilst CREATE seems to forget to reject the 
> statement.

Sorry, I was wrong about this. The CREATE shows the correct behavior whereas 
ALTER incorrecty rejects the statement. According to 
https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html default values 
for columns without explicit DEFAULT are chosen by type (section "Handling of 
Implicit Defaults").

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

Re: [sqlite] inconsistent behavior when creating INTEGER NOT NULL column without DEFAULT?

[Thomas Kurz]

> This is a limitation of SQLite's current ALTER TABLE implementation. Columns 
> can only be added with a default value of NULL, therefore NOT NULL columns 
> are forbidden. 

I don't think so because this works (shortened here; it also works with 
REFERENCES...):

ALTER TABLE test ADD COLUMN groupid2 INTEGER NOT NULL DEFAULT 1;

I guess a missing DEFAULT automatically implies DEFAULT NULL, so the behavior 
of ALTER should be correct whilst CREATE seems to forget to reject the 
statement.

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

[sqlite] inconsistent behavior when creating INTEGER NOT NULL column without DEFAULT?

[Thomas Kurz]

I just stumbled upon the following issue (tested with 3.27.1):

I can do this:
CREATE TABLE test (groupid INTEGER NOT NULL REFERENCES mygroup (id) ON UPDATE 
CASCADE ON DELETE CASCADE);

But this fails:
ALTER TABLE test ADD COLUMN groupid2 INTEGER NOT NULL REFERENCES mygroup (id) 
ON UPDATE CASCADE ON DELETE CASCADE;

--> Error: Cannot add a NOT NULL column with default value NULL

I think both variants should behave consistently. But I don't know which 
behavior is the correct one according to the SQL standard.


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

Re: [sqlite] Tips for index creation

[Thomas Kurz]

Ok, thank you very much for everybody's help.


- Original Message - 
From: Shawn Wagner 
To: SQLite mailing list 
Sent: Wednesday, February 13, 2019, 13:58:19
Subject: [sqlite] Tips for index creation

Some useful reading:

https://use-the-index-luke.com/

https://www.sqlite.org/queryplanner.html (and the pages it links to)

There's also the .expert command in the sqlite shell:

sqlite> .expert
sqlite> SELECT ... FROM ...;

will suggest indexes that will benefit a particular query.


On Wed, Feb 13, 2019, 4:39 AM Thomas Kurz  Hello,

> I apologize right at the beginning, because this is a real noob question.
> But I don't have much experience with performance optimization and indexes,
> so I'm hoping for some useful hints what indexes to create.

> I have queries like this:

> SELECT parameter, value FROM metadata WHERE id1=a AND id2 IS NULL and id3
> IS NULL
> -or-
> SELECT parameter, value FROM metadata WHERE id1=a AND id2=b and id3 IS NULL
> -or-
> SELECT parameter, value FROM metadata WHERE id1=a AND id2=b and id3=c

> Do I have to create three indexes, one for each column id1, id2, id3? Or
> is it better or even necessary to create only one index covering all three
> id columns?

> Do I need indexes for the parameter and value columns as well, and under
> which circumstances? (Only if I want to use a SELECT ... WHERE parameter =
> xy?)

> Kind regards,
> Thomas

> ___
> 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

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

[sqlite] Tips for index creation

[Thomas Kurz]

Hello,

I apologize right at the beginning, because this is a real noob question. But I 
don't have much experience with performance optimization and indexes, so I'm 
hoping for some useful hints what indexes to create.

I have queries like this:

SELECT parameter, value FROM metadata WHERE id1=a AND id2 IS NULL and id3 IS 
NULL
-or-
SELECT parameter, value FROM metadata WHERE id1=a AND id2=b and id3 IS NULL
-or-
SELECT parameter, value FROM metadata WHERE id1=a AND id2=b and id3=c

Do I have to create three indexes, one for each column id1, id2, id3? Or is it 
better or even necessary to create only one index covering all three id columns?

Do I need indexes for the parameter and value columns as well, and under which 
circumstances? (Only if I want to use a SELECT ... WHERE parameter = xy?)

Kind regards,
Thomas

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

Re: [sqlite] Database locking problems

[Thomas Kurz]

Just for curiosity: how do other DBMS (MySQL, etc.) solve this issue? I guess 
the keypoint is that no matter where the query comes from, the database files 
are always under control of the same process which then can take care of the 
correct order in which to read and write data.

But the problem that the same database page cannot be written to from 2 
different statements (or being read while another statement is writing into it) 
should arise there as well, shouldn't it? However, I have never seen MySQL 
return some kind of "busy" (whatsoever it may be named there).

As I said, just for curiosity - no offense against SQlite because I can well 
understand the problem that SQlite has to deal with :-)


- Original Message - 
From: Richard Damon 
To: sqlite-users@mailinglists.sqlite.org 
Sent: Monday, January 21, 2019, 00:21:48
Subject: [sqlite] Database locking problems

On 1/20/19 4:51 PM, andrew.g...@l3t.com wrote:
> James K. Lowden wrote:
>> On Sat, 19 Jan 2019 08:07:42 -0500 Richard Hipp  wrote:
>>> The busy timeout is not working because you start out your transaction
>>> using a read operation - the first SELECT statement - which gets a read
>>> lock.  Later when you go to COMMIT, this has to elevate to a write
>>> lock.  But SQLite sees that some other process has already updated the
>>> database since you started your read.
>> Another solution is to rely on atomicity in SQL:

>> insert into t
>> select :pid, nrows, N
>> from (select 1 as N union select 2 union select 3) as cardinals
>> cross join (select :pid, count(*) as nrows from t) as how_many;

>> By using a single SQL statement, you avoid a user-defined transaction
>> and any proprietary transaction qualifiers.
> Thank you for the suggestion, but I don't believe this is an option in my 
> application.  There's too much back-and-forth between the database and my 
> logic to put it all into a single statement.  Thus, transactions are 
> necessary.  Transactions exist to allow multiple statements to become an 
> atomic unit, so eschewing them is basically the same thing as admitting they 
> don't work.  There are two possibilities:

> 1. Transactions do work, but I'm misusing them and must learn how to be more 
> careful.  In this case, I will update documentation to properly explain their 
> use to others.

> 2. Transactions don't work, at least not for my task.  In this case, I will 
> do my best to investigate the problem and suggest a correction.

> Either way, the right thing for me to do is continue digging in.

> Going from BEGIN to BEGIN IMMEDIATE indeed fixed the test program I wrote, 
> but my application is still broken, and I don't know what's different about 
> it.  I'm working on instrumenting the fcntl() calls to log the sequence of 
> operations.

Transactions work, but the way you have described your use of them has a
predictable issue. The problem being if you begin with a shared lock,
try to promote to an exclusive lock to write, then you need to wait for
all the other shared locks to clear, and if another of them also tries
to promote to an exclusive lock, which cause a deadlock, which sounds to
be your issue.

Starting with a BEGIN IMMEDIATE breaks this deadlock situation, so it
can fix your problem, at the cost that you get less concurrency.

The experimental concurrency option might also help, in that a write
operation doesn't need an exclusive lock for the whole database, but
does introduce more points where a transaction might fail and need to be
rolled back.


-- 
Richard DamonTh

___
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] WAL mode for in-memory databases?

[Thomas Kurz]

It would also be very helpful if more control about in-memory-databases was 
available. As far as I have understood, an in-memory database is deleted when 
the last connection closes. This requires me to always hold a connection to an 
in-memory database even if don't need it right now.

Maybe one could introduce a pragma "persistent_inmemory" that allows to keep an 
in-memory-database until explicitely deleted. (DROP DATABASE IF EXISTS ...)

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

Re: [sqlite] SQLite error (5): database is locked

[Thomas Kurz]

> pragma_busy_timeout

Does setting the busy_timeout retry periodically (e.g. every x milliseconds), 
or is there some automatism that ensures that the requested operation is done 
just-in-time as soon as the previous/blocking operation is finished?

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

Re: [sqlite] Question about floating point

[Thomas Kurz]

Ok, as there seem to be some experts about floating-point numbers here, there 
is one aspect that I never understood:

floats are stored as a fractional part, which is binary encoded, and an 
integer-type exponent. The first leads to the famous rounding errors as there 
is no exact representation of most fractions.

Can someone explain to me why it has been defined this way? Having 1 bit sign, 
11 bit exponent, and 52 bit mantissa, I would have stored the (in the meantime 
well known) number 211496.26 as 21149626E-2, i.e. I would have stored a 52 bit 
integer number and appropriate exponent. This way there should be no rounding 
errors and one would always have a guaranteed precision of ~15 significant 
digits.


- Original Message - 
From: Keith Medcalf 
To: SQLite mailing list 
Sent: Monday, December 17, 2018, 20:12:29
Subject: [sqlite] Question about floating point


The "nearest" representation of 211496.26 is 211496.260931323.  The two 
representable IEEE-754 double precision floating point numbers bounding 
211496.26 are:

211496.260931323
211496.25802094

The difference between 211496.252 (which is itself a truncated 
representation -- the actual stored value should presumably be 
211496.252200173) and 211496.260931323 is 8.73114913702011e-11, 
or 3 ULP.

Applying half-even rounding at the second "decimal place" to 211496.252 
(which when multiplied by 100 is 21149625.25494194 which half-even 
rounds to 21149626.0 which when divided by 100 is 211496.260931323 or 
exactly the "nearest representable IEEE-754 double precision floating point 
number" to 211496.26

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
>Sent: Monday, 17 December, 2018 10:36
>To: SQLite mailing list
>Subject: Re: [sqlite] Question about floating point

>On 17 Dec 2018, at 5:16pm, James K. Lowden 
>wrote:

>> IEEE
>> double-precision floating point is accurate to within 15 decimal
>> digits.

>First, the problem is not storage it's calculation.

>Second, the thread was started because a floating point calculation
>in SQLite, exactly as it is run today, led to the following value:

>211496.252

>which is typical of such problems.  Please don't consider that
>there's no problem until you have solved that problem.

>Simon.
>___
>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

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

Re: [sqlite] Question about floating point

[Thomas Kurz]

> Good way to overflow your integers.
> With floating point, that's not a problem.

With int64, it shouldn't be a problem either.

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

Re: [sqlite] ALTER TABLE, modifying columns

[Thomas Kurz]

> I never would have allowed the recent
> enhancements to ALTER TABLE that broke it.

The enhancements made have been way overdue. Personally, I appreciate them very 
much and they are worth the "trouble". And I hope that the small problem does 
not prevent you from taking MODIFY COLUMN and DROP COLUMN into account. I think 
it should solve most problems as the quite complex procedure (which obviously 
seems to be improperly implemented quite often, not only from me *g*) would 
then be obsolete. And, in addition, SQLite could possibly even use a more 
efficient way for the modifications than just copying all the data. (In fact, a 
13th step "VACUUM" may be required in many cases, as dropping the table in step 
6 can lead to a large "hole" in the database.)

As far as the example is concerned, I will try to reproduce tomorrow.

Kind regards,
Thomas

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

Re: [sqlite] ALTER TABLE, modifying columns

[Thomas Kurz]

This doesn't work either. The error now occurs in the "ALTER TABLE" line, which 
is correct as the table "x" being refered to doesn't exist that moment. Tested 
with both 3.25.2 and 3.26.

Btw, has the "correct vs. incorrect" table that you've cited already been there 
before release 3.25?


- Original Message - 
From: Shawn Wagner 
To: SQLite mailing list 
Sent: Wednesday, December 12, 2018, 18:02:54
Subject: [sqlite] ALTER TABLE, modifying columns

You're using a workflow that https://www.sqlite.org/lang_altertable.html
explicitly calls out as incorrect and error prone...

Try to create a new table, copy data over, drop the original and then
rename the new one to see if that fixes the issue.

On Wed, Dec 12, 2018, 8:54 AM Thomas Kurz  Dear all,

> I don't know whether the behavior is intentional or a bug, so let me
> describe it (occurs since 3.25):

> Due to the lack of ALTER TABLE MODIFY COLUMN, I use the following
> construction:

> PRAGMA foreign_keys=0
> BEGIN TRANSACTION
> ALTER TABLE x RENAME TO x_old
> CREATE TABLE IF NOT EXISTS x (... new declaration ...)
> INSERT INTO x (...) SELECT ... FROM x_old
> DROP TABLE x_old
>  more to do here ...
> COMMIT
> PRAGMA foreign_keys=1

> Usually, this works fine, but now I have a VIEW that references table x,
> which leads to an error "error in view ...: no such table: main.x_old".

> Of course, this happens because renaming x to x_old also changes the
> view's reference from x to x_old which is not intended in this case.

> As a workaround, I have now added "PRAGMA legacy_alter_table" before and
> after the transaction.

> The behavior makes modifying columns even more complicated, so I'd like to
> beg for an ALTER TABLE MODIFY COLUMN statement once more ;-))

> Kind regards,
> Thomas

> ___
> 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

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

[sqlite] ALTER TABLE, modifying columns

[Thomas Kurz]

Dear all,

I don't know whether the behavior is intentional or a bug, so let me describe 
it (occurs since 3.25):

Due to the lack of ALTER TABLE MODIFY COLUMN, I use the following construction:

PRAGMA foreign_keys=0
BEGIN TRANSACTION
ALTER TABLE x RENAME TO x_old
CREATE TABLE IF NOT EXISTS x (... new declaration ...)
INSERT INTO x (...) SELECT ... FROM x_old
DROP TABLE x_old
 more to do here ...
COMMIT
PRAGMA foreign_keys=1

Usually, this works fine, but now I have a VIEW that references table x, which 
leads to an error "error in view ...: no such table: main.x_old".

Of course, this happens because renaming x to x_old also changes the view's 
reference from x to x_old which is not intended in this case.

As a workaround, I have now added "PRAGMA legacy_alter_table" before and after 
the transaction.

The behavior makes modifying columns even more complicated, so I'd like to beg 
for an ALTER TABLE MODIFY COLUMN statement once more ;-))

Kind regards,
Thomas

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

Re: [sqlite] function named geopolyCosine is a misnomer

[Thomas Kurz]

Could it be that the one angle is north-based, the other one east-based?


- Original Message - 
From: Graham Hardman 
To: SQLite mailing list 
Sent: Thursday, November 29, 2018, 12:46:05
Subject: [sqlite] function named geopolyCosine is a misnomer

Hi, 

I was very interested in the numerical approximation used in the
function named geopolyCosine and after a bit of on-line research decided
to test it's accuracy myself. What I discovered was that the function in
fact returns the sin value rather than the cosine value. 

This is quickly noticed by checking the return value when r = 0. The
value returned is 0 rather than the expected 1.0 

The function is only employed by the geopoly_regular function which I
found does actually return the expected shape (taking into account the
approximation being used). 

On checking the geopoly_regular program code I saw that the coordinate
calculation formulae cancel out the incorrect value returned from
geopolyCosine by essentially  reversing the normal understanding of sin
and cosine. 

I hope it is understood that I do not wish to offend. I would, in fact
appreciate a link to the site where this approximation was discovered. 

regards, 

Graham
___
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] Bug?: unexpected behaviour - alter parent table in a deferred foreign key relationship

[Thomas Kurz]

To what I've learned so far, SQlite stores all data "as is" into any column 
regardless of the column declaration. The affinity only matters upon reading, 
am I correct? If so, would it be a big deal implementing ALTER TABLE ALTER 
COLUMN?


- Original Message - 
From: Dan Kennedy 
To: sqlite-users@mailinglists.sqlite.org 
Sent: Friday, November 23, 2018, 16:30:12
Subject: [sqlite] Bug?: unexpected behaviour - alter parent table in a deferred 
foreign key relationship

On 11/23/2018 09:54 PM, szmate1618 wrote:
> Dear list members,

> I have the following problem, with which I'd like to request your aid:

> Currently, at version 3.25.2, SQLite only has a limited support for alter
> table. E.g. you cannot change the datatype (type affinity) of a column, or
> drop a column.

> The usual workaround is to create a new table with the desired schema, fill
> it with data from the original table, drop the original table, and rename
> the new one. But what if the original table is a parent table in a foreign
> key relationship?

> The official solution
>  is turning foreign
> keys off, making the changes you want, then turning foreign keys on. But
> I'm slightly annoyed this cannot be done in a transaction (because these 
> PRAGMA
> foreign_keys =s don't take effect inside of transactions, so they need to
> be issued before and after).


"PRAGMA foreign_keys = ?" is a property of the connection only, not the 
database file. So what advantage would there be in including the PRAGMA 
statements in the body of a transaction?

Dan.




> I'd like to use deferred foreign keys instead. I have 3 queries, one of
> them seems to work, the two others do not. My questions are the following:

>- Does the seemingly working query work by design? Or it's just a
>fortunate(?) constellation of multiple factors, and depending on other
>tables or new data in the database it might break in the future? Somewhat
>like undefined behavior in C++?
>- Why do the other ones not work? How are they different from the first
>one?

> Setup

> PRAGMA foreign_keys = OFF;
> DROP TABLE IF EXISTS Parent;CREATE TABLE Parent(A TEXT UNIQUE,
> COLUMN_TO_DROP FLOAT);INSERT INTO Parent VALUES('whatever', 0.0);
> DROP TABLE IF EXISTS Child;CREATE TABLE Child(C TEXT REFERENCES
> Parent(A) DEFERRABLE INITIALLY DEFERRED);INSERT INTO Child
> VALUES('whatever');

> PRAGMA foreign_keys = ON;

> Query1 - seems to be working as intended

> BEGIN TRANSACTION;CREATE TABLE Temp AS SELECT A FROM Parent;DROP TABLE
> Parent;CREATE TABLE Parent (A TEXT UNIQUE);INSERT INTO Parent SELECT *
> FROM Temp;DROP TABLE Temp;COMMIT;

> Query2 - create [...] as select [...] fails

> BEGIN TRANSACTION;CREATE TABLE Temp AS SELECT A FROM Parent;DROP TABLE
> Parent;CREATE TABLE Parent AS SELECT * FROM Temp; -- different
> from Query1CREATE UNIQUE INDEX ParentIndex on Parent(A);  -- different
> from Query1DROP TABLE Temp;COMMIT;

> Result:

> sqlite> PRAGMA foreign_key_check;
> sqlite> .schemaCREATE TABLE Child(C TEXT REFERENCES Parent(A)
> DEFERRABLE INITIALLY DEFERRED);CREATE TABLE Parent(A TEXT);CREATE
> UNIQUE INDEX ParentIndex on Parent(A);
> sqlite> SELECT * FROM Parent;
> whatever

> Query3 - insert into [...] fails

> BEGIN TRANSACTION;CREATE TABLE Temp (A TEXT UNIQUE);  -- different
> from Query1INSERT INTO Temp SELECT A FROM Parent;  -- different from
> Query1DROP TABLE Parent;CREATE TABLE Parent (A TEXT UNIQUE);INSERT
> INTO Parent SELECT * FROM Temp;DROP TABLE Temp;COMMIT;

> Result:

> sqlite> PRAGMA foreign_key_check;
> sqlite> .schemaCREATE TABLE Child(C TEXT REFERENCES Parent(A)
> DEFERRABLE INITIALLY DEFERRED);CREATE TABLE Parent (A TEXT UNIQUE);
> sqlite> SELECT * FROM Parent;
> whatever

> Note that PRAGMA foreign_key_check does not report any problem in any of
> the cases.


> I posted an identical question on StackOverflow, but no one was able to
> provide any information so far. Thanks in advance!

> Máté Szabó
> ___
> 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

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

Re: [sqlite] SQLite iOS timestamp type mapping settings must be set to float to get correct data

[Thomas Kurz]

> (Does SQL itself have a numeric timestamp type, or explicitly endorse the 
> POSIX epoch for numeric timestamps?)

SQL has an explicit TIMESTAMP type since SQL-92, one thing that I'm heavily 
missing in SQlite ;-)

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

Re: [sqlite] geopoly - rules re data entry

[Thomas Kurz]

> I discovered that
>many legacy GeoJSON files do not follow the rules and put polygon
>vertexes in CW order.

As far as I know, the Open Geospatial Consortium defines polygons with CCW 
order (and iCW inner rings) as "seen from top", and an iCW exterior ring (with 
CCW inner rings) as "seen from bottom".

That might explain the difference (if it's not just wrong by mistake).

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