[sqlite] bug: error in code for geopoly_ccw function

[Graham Hardman]

hi, 

In my posting on 20th November I supplied output from the cli buit from
the snapshot that includes the new geopoly function geopoly_ccw. 

That output showed incorrect results being returned from an attempt to
reverse the direction of a polygon in CW mode after a mirror transform
of a CCW polygon about the X axis. 

There has been no response to that mail, so as I am convinced there is
an error in the program code I decided to resend my report rephrased as
a bug report. 

Looking at the code from the sqlite3.c file I can see that the for loop
"for(ii=2, jj=p->nVertex*2 - 4; ii create virtual table newtab using geopoly(a,b,c);

sqlite> insert into newtab (_shape) values
('[[0,0],[88,0],[88,80],[60,80],[30,40],[0,40],[0,0]]');

sqlite> select geopoly_json(_shape) from newtab;
[[0.0,0.0],[88.0,0.0],[88.0,80.0],[60.0,80.0],[30.0,40.0],[0.0,40.0],[0.0,0.0]]

sqlite> select geopoly_json(geopoly_xform(_shape, 1,0,0,-1,0,0)) from
newtab;
[[0.0,0.0],[88.0,0.0],[88.0,-80.0],[60.0,-80.0],[30.0,-40.0],[0.0,-40.0],[0.0,0.0]]

sqlite> select geopoly_json(geopoly_ccw(geopoly_xform(_shape,
1,0,0,-1,0,0))) from newtab;
[[0.0,0.0],[30.0,-40.0],[60.0,-80.0],[88.0,-80.0],[88.0,0.0],[0.0,-40.0],[0.0,0.0]]


The correct output should have been 

[[0,0][0,-40][30,-40][60,-80][88,-80][88,0][0,0]] 

Please accept my apologies if this matter had already been taken into
consideration. I do understand that the authors are very busy people. 

regards, 

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

[szmate1618]

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

Not much, if I do everything right, I guess. But what if I break the
foreign key integrity?
If it's inside a transaction, I can rollback easily, given that I realize I
broke it.
But if I don't, because foreign key checks are turned off, and I commit
everything before noticing that
something's wrong, that's a lot harder to fix.

I can either take extra care not to break anything, or just do a backup of
the database file before
'risky' transactions, but it would be much easier if I could just use
deferred foreign keys that don't
let me commit if the database is in an inconsistent state.

Máté

Dan Kennedy  ezt írta (időpont: 2018. nov. 23., P,
16:30):

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

Re: [sqlite] Optimizing aggregation queries

[Simon Slavin]

On 23 Nov 2018, at 3:44pm, Wout Mertens  wrote:

> CREATE TABLE log(type TEXT, amount INTEGER)
> SELECT type, SUM(amount), COUNT(*) FROM log GROUP BY type
> 
> What would be good approaches to make the query fast?

Create an index for "log(type, amount)".

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

[Dan Kennedy]

On 11/23/2018 10:47 PM, Thomas Kurz wrote:

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?


Affinity changes are applied before data is written to the database. As 
you say though, implementing ALTER TABLE to change the type would be 
easier if they were not.


Dan.






- 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



___
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

[sqlite] Optimizing aggregation queries

[Wout Mertens]

Given a logging table:

CREATE TABLE log(type TEXT, amount INTEGER)
SELECT type, SUM(amount), COUNT(*) FROM log GROUP BY type

What would be good approaches to make the query fast?

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

[sqlite] PRIMARY KEY not enforcing NOT NULL, WITHOUT ROWID default - suggestions

[Digital Dog]

Hi! Welcome to the list.

I'm reading documentation on the topic:

"In an elegant system, all tables would behave as WITHOUT ROWID tables even
without the WITHOUT ROWID keyword".
then
"However, NOT NULL was not enforced on PRIMARY KEY columns by early
versions of SQLite due to a bug. By the time that this bug was discovered,
so many SQLite databases were already in circulation that the decision was
made not to fix this bug for fear of breaking compatibility."
at https://www.sqlite.org/withoutrowid.html

I suggest a possible solution to make it more elegant and more compliant.
There could be PRAGMA directives to opt-in for compliant behavior:

PRAGMA default_without_rowid = on
To make all tables created while the directive is in use the WITHOUT ROWID
tables.

PRAGMA enforce_not_null_on_primary_key = on
For WITHOUT ROWID tables it would be a no-op, but for rowid tables it would
restore correct behavior.

I think these changes would not add a lot of code to sqlite and will make
life easier for users.

In spirit it would be similar to already existing
PRAGMA foreign_keys = on

(I also wanted to suggest the same to be able to select behaviour of NULL
values in unique index, but am too shy to do it).

Just a thought.

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

[sqlite] Omitted features documentation slightly out of date

[J. King]

The documentation page at  under ALTER 
TABLE mentions that RENAME TABLE and ADD COLUMN are supported, but does 
not mention the new RENAME COLUMN.


--
J. King
___
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

[Dan Kennedy]

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

[szmate1618]

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

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