Re: [sqlite] Write to a View
Hi Marco,
I know we've been discussing this directly, but since you've also mentioned it
here on this list, I thought a response here could also facilitate greater
discussion.
An updatable view is a view that will accept insert, delete and update
commands, propagating changes back to the underlying tables. I believe that
some SQL database engines (not SQLite) facilitate updatable views
automatically, if the view uses just one table or is a simple join of tables,
showing only some of their columns (no calculations). However, any view
(including all views in SQLite) can be explicitly designed to be updatable by
using instead of triggers.
Here's a very simple single table example:
create table Person
( ID integer primary key not null
, Name First text collate nocase
, Name Last text collate nocase
, unique (Name First, Name Last)
)
;
create view Person Sorted
as
select
ID
, Name First
, Name Last
from Person
order by Name Last, Name First
;
If I want to make the Person Sorted view updatable, I need to consider which
of update, insert and delete I want to facilitate, and create an instead of
trigger for each:
create trigger Person Sorted delete
instead of delete
on Person Sorted
begin
delete from Person where ID = old.ID
;
end
;
create trigger Person Sorted insert
instead of insert
on Person Sorted
begin
insert into Person (ID, Name First, Name Last)
select new.ID, new.Name First, new.Name Last
;
end
;
create trigger Person Sorted update
instead of update
on Person Sorted
begin
update Person
set ID = new.ID, Name First = new.Name First, Name Last =
new.Name Last
where ID = old.ID
;
end
;
So, now, if the user opens the view Person Sorted, they can insert, delete or
update directly in that view, and the triggers will propagate those changes to
the underlying table. It appears to behave just like a table.
For instance, they can do any of the following:
insert into Person Sorted (Name First, Name Last) values ('Tom',
'Brodhurst-Hill')
;
delete from Person Sorted where ID = 4
;
delete from Person Sorted where Name Last = 'Brodhurst-Hill'
;
update Person Sorted set Name First = 'Thomas' where Name First = 'Tom'
and Name Last = 'Brodhurst-Hill'
;
Of course, the above example shows a view that is basically a mirror of a
single table, so isn't very useful. It was just for illustration. Let's look at
something more complex. Let's add the following tables so we can associate with
each person a company, a job title and many (or one or none) email addresses.
We can specify whether each email address is for work, home or another purpose:
create table Company
( ID integer primary key not null
, Name text collate nocase not null unique
)
;
create table Person Company
( ID integer primary key not null references Person (ID) on delete
cascade on update cascade
, Company integer not null references Company (ID) on delete cascade
on update cascade
)
;
create table Job Title
( ID integer primary key not null
, Name text collate nocase not null unique
)
;
create table Person Job Title
( ID integer primary key not null references Person (ID) on delete
cascade on update cascade
, Job Title integer not null references Job Title (ID) on delete
cascade on update cascade
)
;
create table Purpose
( ID integer primary key not null
, Name text collate nocase not null unique
)
;
insert into Purpose (Name) select 'Home' union select 'Work' union select
'School'
;
create table Person Email
( ID integer primary key not null
, Person integer not null references Person (ID) on delete cascade on
update cascade
, Email text collate nocase not null
, Purpose integer references Purpose (ID) on delete restrict on
update cascade
, unique (Person, Purpose)
)
;
The above structure is a fairly well normalised store of the data. In this
example, I've restricted the database to allowing just one email address for
each purpose. So each person can have only one work email address.
So, imagine the human resources department has populated these tables with all
the people in our company. They want to distribute a list of all employees,
only showing their name, company and work email address, something like this:
create view Person Work
as
select
Person.ID as ID
, Name First
, Name Last
, Company.Name as Company
, Job Title.Name as Job Title
, ( select Email from Person Email join Purpose on
Purpose.ID = Purpose
where Person = Person.ID and Purpose.Name = 'Work'
) as Email
from Person
left join Person Company on Person Company.ID = Person.ID
left join Company on Company.ID = Company
left join Person Job Title on Person Job Title.ID = Person.ID
left join Job Title on Job Title.ID = Job Title
order by Name Last, Name
Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns
Quoting Igor Tandetnik itandet...@mvps.org: stahl...@dbs.uni-hannover.de wrote: Consider these two tables: CREATE TABLE tab1 (x INTEGER PRIMARY KEY); CREATE TABLE tab2 (x PRIMARY KEY REFERENCES tab1); Assuming they contain the same rows, I expect any query against 'tab1' to return the same rows as against 'tab2'. Why would you expect that? You keep saying this, but I don't understand the basis of your expectations - unless it's just the good old wishful thinking. I base my expectations on two things. First (and probably more importantly): Column 'x' of table 'tab2' is defined as a foreign key referencing the primary key 'x' of table 'tab1'. I expect that a foreign key column is in no way different than the primary key column it points to - unless I explicitly specify it to be different. (This has nothing to do with SQLite, but with the definition of functional dependencies in relational databases: A foreign key *is* a primary key from a foreign table.) So this means the schema definitions of 'tab1' and 'tab2' must effectively be the same and thus my assumption above should hold. As SQLite let me leave out the column type, I thought Great! SQLite is smart enough to infer the column type for FKs! Just like Oracle!. Which leads to my second reason: The Oracle DBMS does what I expect. So yes, I admit that this *is* wishful thinking on my part. I do think, however, that my wishes are reasonable. (I don't know what the SQL Standard has to say about this situation I'm pretty sure the second CREATE TABLE statement is syntactically invalid, per the standard. The column type is mandatory, if I recall correctly. Ah! I think that explains the situation somewhat: SQLite and Oracle expand the standard in the same way by allowing to leave out the column type for FKs. Oracle infers the column type from the PK, but SQLite always uses the default column type BLOB (or 'NONE'..?). (PostgreSQL rejects the definition for 'tab2' as mentioned earlier.) SQLite's behavior makes sense, because *every* column type may be left out. However, I think that in the case of FK-definitions (like the one in 'tab2') assigning the default type is not the right thing to do. but I do think that most users would share my expectation.) I'm not sure what you base this belief on, either. I don't seem to see your argument enjoying widespread support on this thread. Yes, you are obviously right there. I discussed the problem earlier with colleagues (mostly Oracle users), but obviously this list is a different world. :-) However with SQLite there are queries which yield incoherent results: Define incoherent. As far as I can tell, you use this term to mean results you personally dislike. The results SQLite produces are in agreement - in other words, in coherence - with the product documentation. I just meant 'incoherent' wrt. The same query returns different results for the same data. as per my example. You seem to hold this truth to be self-evident, but I honestly don't understand why. Could you explain your reasoning to me? I hope the explanations above make my point at least understandable? Also I don't really care *how* this is fixed As best I can tell, so far there's no agreement that it's broken, so discussing how to fix it seems a bit premature. Agreed there's no agreement. :-) As I said earlier: I'm fine now that I know that I should explicitly specify the column type (even better if that's standard compliant!). I still think that SQLite could be made better by inferring FK column types. But I won't pursue this matter any further if there is no agreement on this. Thank you for your answer! Kind regards, Christian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns
Quoting Simon Slavin slav...@bigfraud.org: On 10 Nov 2012, at 7:21pm, stahl...@dbs.uni-hannover.de wrote: Consider these two tables: CREATE TABLE tab1 (x INTEGER PRIMARY KEY); CREATE TABLE tab2 (x PRIMARY KEY REFERENCES tab1); Assuming they contain the same rows, I expect any query against 'tab1' to return the same rows as against 'tab2'. Sorry, but you are too optimistic. Yeah, I notice that now. :-) I would expect a schema design tool -- the sort where you move rectangles and pointers around -- to warn you when you put a pointer in from tab2 to tab1, that the affinities should match. But in a language where you specifically declare schema using text, I expect the programmer to have to do this him- or herself. Fair enough. Although I would (optimistically) hope that SQL schema definitions (and thus schema design tools) were portable between DBSes. But this can't happen with the impact SQLite's unique concept of 'type affinities' has on schemas. Thanks for your help tough. Christian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns
stahl...@dbs.uni-hannover.de wrote:
Quoting Igor Tandetnik itandet...@mvps.org:
stahl...@dbs.uni-hannover.de wrote:
Consider these two tables:
CREATE TABLE tab1 (x INTEGER PRIMARY KEY);
CREATE TABLE tab2 (x PRIMARY KEY REFERENCES tab1);
Assuming they contain the same rows, I expect any query against 'tab1' to
return the same rows as against 'tab2'.
Why would you expect that? You keep saying this, but I don't
understand the basis of your expectations - unless it's just the
good old wishful thinking.
I base my expectations on two things.
First (and probably more importantly):
Column 'x' of table 'tab2' is defined as a foreign key referencing the primary
key 'x' of table 'tab1'.
I expect that a foreign key column is in no way different than the primary key
column it points to - unless I explicitly specify it to be different.
But you did. I can't help but notice that the two column definitions are indeed
different: one specifies the type and the other doesn't. That effectively means
that the two columns use different collations. Consider this example, which
illustrates the situation more explicitly:
create table a(t text collate nocase unique);
create table b(t text collate binary references a(t));
insert into a values ('X');
insert into b values ('X');
select * from a where t='x'; -- returns one row
select * from b where t='x'; -- returns no rows
(This has nothing to do with SQLite, but with the definition of functional
dependencies in relational databases: A foreign key *is* a primary key from
a foreign table.)
Depends on what the meaning of is is. SQL-92 requres that foreign key columns
match referenced columns according to MATCH predicate:
8.10 match predicate
Function
Specify a test for matching rows.
Format
match predicate ::=
row value constructor MATCH [ UNIQUE ] [ PARTIAL | FULL ]
table subquery
Syntax Rules
1) The row value constructor shall be of the same degree as the
table subquery.
2) The data types of the values of the row value constructor
shall be respectively comparable to those of the corresponding
columns of the table subquery.
3) The collating sequence for each pair of respective values in the
match predicate is determined in the same manner as described
in Subclause 8.2, comparison predicate.
Basically, the columns don't have to be of the same type, and the notion of
matching is the usual comparison, with collations and everything.
So this means the schema definitions of 'tab1' and 'tab2' must effectively be
the same and thus my assumption above should hold.
Which part of which normative document requires this?
As SQLite let me leave out the column type, I thought Great! SQLite is smart
enough to infer the column type for FKs! Just like Oracle!.
This is, of course, a perfect example of wishful thinking.
Which leads to my second reason: The Oracle DBMS does what I expect.
So your definition of correctness is always do what Oracle does, then? In
this case, I suggest you only ever use Oracle, to escape disappointment. By
this definition, it's the only correctly implemented DBMS in the world.
So yes, I admit that this *is* wishful thinking on my part.
I do think, however, that my wishes are reasonable.
You are, naturally, entitled to your optinion, while other people are entitled
to theirs. In this case, the balance of opinion doesn't appear to be in your
favor.
SQLite's behavior makes sense, because *every* column type may be left out.
However, I think that in the case of FK-definitions (like the one in 'tab2')
assigning the default type is not the right thing to do.
Why should one clause in the syntax behave differently depending on the
presence or absence of another, unrelated clause? Sounds like an arbitrary
special case to me.
However with SQLite there are queries which yield incoherent results:
Define incoherent. As far as I can tell, you use this term to mean
results you personally dislike. The results SQLite produces are in
agreement - in other words, in coherence - with the product
documentation.
I just meant 'incoherent' wrt. The same query returns different results for
the same data. as per my example.
As I've shown earlier, this is perfectly normal when two table definitions
differ in a way that results in different collation rules being applied. This
would be true even for a (largely hypothetical) strictly SQL-92 compliant DBMS.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Confusion on 'foreign key mismatch' errors
I'm stuck on some errors related to my use of foreign key constraints
in my application. The following illustrates:
$ sqlite3
SQLite version 3.7.9 --SOURCE-ID--
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite PRAGMA foreign_keys = ON;
sqlite CREATE VIRTUAL TABLE comment USING fts4();
sqlite CREATE TABLE ip (
... id INTEGER PRIMARY KEY, date_added DEFAULT CURRENT_DATE,
... ip VARCHAR(15) NOT NULL UNIQUE, comment INTEGER NOT NULL,
... FOREIGN KEY(comment) REFERENCES comment);
sqlite CREATE INDEX ipcommentindex ON ip(comment);
sqlite INSERT INTO comment VALUES ('this is a comment.');
sqlite SELECT rowid,content FROM comment;
1|this is a comment.
sqlite SELECT last_insert_rowid();
1
sqlite INSERT INTO ip (ip,comment) VALUES ('10.0.1.1',last_insert_rowid());
Error: foreign key mismatch
sqlite INSERT INTO ip (ip,comment) VALUES ('10.0.1.1',1);
Error: foreign key mismatch
Thinking I understand the requirements for foreign key constraints, I
don't know why my INSERT into ip table returns 'foreign key mismatch'
error.
When I leave foreign key constraints off, things work as I would expect:
sqlite CREATE VIRTUAL TABLE comment USING fts4();
sqlite CREATE TABLE ip (
... id INTEGER PRIMARY KEY, date_added DEFAULT CURRENT_DATE,
... ip VARCHAR(15) NOT NULL UNIQUE, comment INTEGER NOT NULL,
... FOREIGN KEY(comment) REFERENCES comment);
sqlite CREATE INDEX ipcommentindex ON ip(comment);
sqlite INSERT INTO comment VALUES ('this is a comment.');
sqlite SELECT rowid,content FROM comment;
1|this is a comment.
sqlite SELECT last_insert_rowid();
1
sqlite INSERT INTO ip (ip,comment) VALUES ('10.0.1.1',last_insert_rowid());
sqlite SELECT * FROM ip;
1|2012-11-12|10.0.1.1|1
I suspect the issue might stem from trying to use the rowid in the
comment table as the parent key in the foreign key on the ip table,
reading:
# http://www.sqlite.org/foreignkeys.html
The parent key is the column or set of columns in the parent table
that the foreign key constraint refers to. This is normally, but not
always, the primary key of the parent table. The parent key must be a
named column or columns in the parent table, not the rowid.
...but then this kind of reads like it's supported:
# http://answers.oreilly.com/topic/1955-how-to-use-full-text-search-in-sqlite/
Unlike traditional tables, the ROWID of an FTS table is stable through
a vacuum (VACUUM in Appendix C), so it can be reliably referenced
through a foreign key.
I'm struggling to find a clear way to achieve a foreign key
constrained relation between these tables if a.) the FTS table can't
define an INTEGER PRIMARY KEY column to function as the parent key for
a child table, or b.) child tables can't reference the rowid on an FTS
parent table as the parent key.
Clue bat appreciated.
--
Darren Spruell
phatbuck...@gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Confusion on 'foreign key mismatch' errors
# http://answers.oreilly.com/topic/1955-how-to-use-full-text-search-in-sqlite/
Unlike traditional tables, the ROWID of an FTS table is stable through
a vacuum (VACUUM in Appendix C), so it can be reliably referenced
through a foreign key.
I'm not sure who wrote that but this page
http://www.sqlite.org/lang_createtable.html states explicitly (at the
very end): The parent key of a foreign key constraint is not allowed
to use the rowid.
Pavel
On Sun, Nov 11, 2012 at 9:49 PM, Darren Spruell phatbuck...@gmail.com wrote:
I'm stuck on some errors related to my use of foreign key constraints
in my application. The following illustrates:
$ sqlite3
SQLite version 3.7.9 --SOURCE-ID--
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite PRAGMA foreign_keys = ON;
sqlite CREATE VIRTUAL TABLE comment USING fts4();
sqlite CREATE TABLE ip (
... id INTEGER PRIMARY KEY, date_added DEFAULT CURRENT_DATE,
... ip VARCHAR(15) NOT NULL UNIQUE, comment INTEGER NOT NULL,
... FOREIGN KEY(comment) REFERENCES comment);
sqlite CREATE INDEX ipcommentindex ON ip(comment);
sqlite INSERT INTO comment VALUES ('this is a comment.');
sqlite SELECT rowid,content FROM comment;
1|this is a comment.
sqlite SELECT last_insert_rowid();
1
sqlite INSERT INTO ip (ip,comment) VALUES ('10.0.1.1',last_insert_rowid());
Error: foreign key mismatch
sqlite INSERT INTO ip (ip,comment) VALUES ('10.0.1.1',1);
Error: foreign key mismatch
Thinking I understand the requirements for foreign key constraints, I
don't know why my INSERT into ip table returns 'foreign key mismatch'
error.
When I leave foreign key constraints off, things work as I would expect:
sqlite CREATE VIRTUAL TABLE comment USING fts4();
sqlite CREATE TABLE ip (
... id INTEGER PRIMARY KEY, date_added DEFAULT CURRENT_DATE,
... ip VARCHAR(15) NOT NULL UNIQUE, comment INTEGER NOT NULL,
... FOREIGN KEY(comment) REFERENCES comment);
sqlite CREATE INDEX ipcommentindex ON ip(comment);
sqlite INSERT INTO comment VALUES ('this is a comment.');
sqlite SELECT rowid,content FROM comment;
1|this is a comment.
sqlite SELECT last_insert_rowid();
1
sqlite INSERT INTO ip (ip,comment) VALUES ('10.0.1.1',last_insert_rowid());
sqlite SELECT * FROM ip;
1|2012-11-12|10.0.1.1|1
I suspect the issue might stem from trying to use the rowid in the
comment table as the parent key in the foreign key on the ip table,
reading:
# http://www.sqlite.org/foreignkeys.html
The parent key is the column or set of columns in the parent table
that the foreign key constraint refers to. This is normally, but not
always, the primary key of the parent table. The parent key must be a
named column or columns in the parent table, not the rowid.
...but then this kind of reads like it's supported:
# http://answers.oreilly.com/topic/1955-how-to-use-full-text-search-in-sqlite/
Unlike traditional tables, the ROWID of an FTS table is stable through
a vacuum (VACUUM in Appendix C), so it can be reliably referenced
through a foreign key.
I'm struggling to find a clear way to achieve a foreign key
constrained relation between these tables if a.) the FTS table can't
define an INTEGER PRIMARY KEY column to function as the parent key for
a child table, or b.) child tables can't reference the rowid on an FTS
parent table as the parent key.
Clue bat appreciated.
--
Darren Spruell
phatbuck...@gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Confusion on 'foreign key mismatch' errors
On 12 Nov 2012, at 5:49am, Darren Spruell phatbuck...@gmail.com wrote:
sqlite CREATE TABLE ip (
... id INTEGER PRIMARY KEY, date_added DEFAULT CURRENT_DATE,
... ip VARCHAR(15) NOT NULL UNIQUE, comment INTEGER NOT NULL,
... FOREIGN KEY(comment) REFERENCES comment);
sqlite CREATE INDEX ipcommentindex ON ip(comment);
sqlite INSERT INTO comment VALUES ('this is a comment.');
sqlite SELECT rowid,content FROM comment;
1|this is a comment.
sqlite SELECT last_insert_rowid();
1
sqlite INSERT INTO ip (ip,comment) VALUES ('10.0.1.1',last_insert_rowid());
Error: foreign key mismatch
You slipped up in the FOREIGN KEY definition. You're relating it to the text
field of the comment table. You should be relating it to the 'rowid' field,
possibly something like.
... FOREIGN KEY(comment) REFERENCES comment(rowid));
However, you cannot relate to rowid, because it's not a properly defined field.
So define an 'id' field for the comment table, then make sure you refer to it
in your FOREIGN KEY definition.
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Confusion on 'foreign key mismatch' errors
On Sun, Nov 11, 2012 at 11:16 PM, Simon Slavin slav...@bigfraud.org wrote:
On 12 Nov 2012, at 5:49am, Darren Spruell phatbuck...@gmail.com wrote:
sqlite CREATE TABLE ip (
... id INTEGER PRIMARY KEY, date_added DEFAULT CURRENT_DATE,
... ip VARCHAR(15) NOT NULL UNIQUE, comment INTEGER NOT NULL,
... FOREIGN KEY(comment) REFERENCES comment);
sqlite CREATE INDEX ipcommentindex ON ip(comment);
sqlite INSERT INTO comment VALUES ('this is a comment.');
sqlite SELECT rowid,content FROM comment;
1|this is a comment.
sqlite SELECT last_insert_rowid();
1
sqlite INSERT INTO ip (ip,comment) VALUES ('10.0.1.1',last_insert_rowid());
Error: foreign key mismatch
You slipped up in the FOREIGN KEY definition. You're relating it to the text
field of the comment table. You should be relating it to the 'rowid' field,
possibly something like.
... FOREIGN KEY(comment) REFERENCES comment(rowid));
However, you cannot relate to rowid, because it's not a properly defined
field. So define an 'id' field for the comment table, then make sure you
refer to it in your FOREIGN KEY definition.
If I'm not mistaken, that's a problem too, as type definitions are
ignored on FTS columns:
# http://www.sqlite.org/fts3.html
If column names are explicitly provided for the FTS table as part of
the CREATE VIRTUAL TABLE statement, then a datatype name may be
optionally specified for each column. This is pure syntactic sugar,
the supplied typenames are not used by FTS or the SQLite core for any
purpose. The same applies to any constraints specified along with an
FTS column name - they are parsed but not used or recorded by the
system in any way.
I would want my 'id' field to behave as an autoincrementing integer
field (i.e. INTEGER PRIMARY KEY) to work properly.
So I think I might be at an impasse with this plan. Maybe I'll drop
the foreign key and restructure around it.
Thx!
--
Darren Spruell
phatbuck...@gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
