The insert sequence does not work in all cases. This fixes one particular
problem of duplicate or changed linkage to parent in Alpha:Charlie ...
create trigger trg_summary_insert
instead of insert on summary
for each row
begin
insert or ignore into Alpha (a_attribute) values (new.a_attribute);
insert or ignore into Beta (b_attribute) values (new.b_attribute);
select raise(ABORT,'c_attribute has different a_attribute parent')
from Charlie
where c_attribute == new.c_attribute
and a_id != (select id from Alpha where a_attribute == new.a_attribute);
insert or ignore into Charlie (c_attribute, a_id) values (new.c_attribute,
(select id from Alpha where a_attribute == new.a_attribute));
insert or abort into AlphaBeta values ((select id from Alpha where a_attribute
== new.a_attribute),
(select id from Beta where b_attribute
== new.b_attribute));
end;
That is, you need to throw an error if c_attribute is already linked to a
different a_attribute, otherwise you can insert or ignore into Charlie.
---
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 Keith Medcalf
>Sent: Wednesday, 20 February, 2019 09:44
>To: SQLite Users (sqlite-users@mailinglists.sqlite.org)
>Subject: Re: [sqlite] What is the recommended way to write to views?
>
>
>Or as a trigger of course:
>
>create table Alpha
>(
> id integer primary key,
> a_attribute text not null collate nocase unique
>);
>
>create table Beta
>(
> id integer primary key,
> b_attribute text not null collate nocase unique
>);
>
>create table Charlie
>(
> id integer primary key,
> c_attribute text not null collate nocase unique,
> a_id integer not null references Alpha
>);
>create index Charlie_a_id on Charlie (a_id);
>
>create table AlphaBeta
>(
> a_id integer not null references Alpha,
> b_id integer not null references Beta,
> unique(a_id, b_id),
> unique(b_id, a_id)
>);
>
>create view Summary (a_attribute, b_attribute, c_attribute)
>as select a_attribute,
> b_attribute,
> c_attribute
> from Alpha, Beta, Charlie, AlphaBeta
> where Alpha.id == Charlie.a_id
> and Alpha.id == AlphaBeta.a_id
> and Beta.id == AlphaBeta.b_id
>;
>
>create trigger trg_summary_insert
>instead of insert on summary
>for each row
>begin
>insert or ignore into Alpha (a_attribute) values (new.a_attribute);
>insert or ignore into Beta (b_attribute) values (new.b_attribute);
>insert into Charlie (c_attribute, a_id) values (new.c_attribute,
>(select id from Alpha where a_attribute == new.a_attribute));
>insert into AlphaBeta values ((select id from Alpha where a_attribute
>== new.a_attribute),
> (select id from Beta where b_attribute
>== new.b_attribute));
>end;
>
>Note that I fixed the subselects when I originally wrote them I
>followed the rule that "columns containing the same data should have
>the same name" yet did not follow that rule when creating the schema.
>Sorry bout that.
>
>Nonetheless, you probably want to use source programming to maintain
>the database properly rather than relying on triggers, unless you
>plan to write a "whole set of them" (update, delete) so you can
>"pretend" that a complicated relational data structure is "just a
>table".
>
>However the instead of trigger does have the advantage of being one
>compiled as a single plan, and does get around the daftness of some
>database interfaces.
>
>---
>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: Keith Medcalf [mailto:kmedc...@dessus.com]
>>Sent: Wednesday, 20 February, 2019 09:24
>>To: 'SQLite mailing list'
>>Subject: RE: [sqlite] What is the recommended way to write to views?
>>
>>
>>Your constraints are logically inconsistent and incompletely
>>specified.
>>
>>>Alpha has-many Beta,and
>>>Beta has-many Alpha
>>>Alpha has-many Charlie, while
>>>Charlie has one Alpha
>>
>>Implies that:
>>
>>Alpha:Beta is N:M and Beta:Alpha is N:M. Fine.
>>but you did not say whether the instant relationships are unique.
>>(That is, whether one specific Alpha may have only one Beta as a
>>child, or it may have multiple instances of the same Beta as a
>>child). We will assume that the set is unique and enforce that.
>>
>>Alpha:Charlie is 1:N and Charlie:Alpha is N:1. Fine.
>>
>>You also did not specify how to handle NULL attributes. Since how
>>you do this makes significant changes to the processing and
>integrity
>>of the database, we will assume that you simply forgot about the
>>possibility of null values and thus assume that attributes will not
>>be null. Further we assume that the attribute comprises an entire
>>candidate key and therefore is unique in each set.
>>
>>(where do you get such silly column names from for the foreign key
>>linkage fields, it looks like some idiotic "lets put a bunch o'junk
>>as prefixes on a variable name to embed the context into the name,
>>just cuz we can and that is our (albeit foolish) standard. Mutatis
>>mutandis the N:M join table. And why are you using AUTOINCREMENT
>...
>>are you overloading some meaning onto the id's rather than just
>using
>>it for linkage?)
>>
>>
>>create table Alpha
>>(
>> id integer primary key,
>> a_attribute text not null collate nocase unique
>>);
>>
>>create table Beta
>>(
>> id integer primary key,
>> b_attribute text not null collate nocase unique
>>);
>>
>>create table Charlie
>>(
>> id integer primary key,
>> c_attribute text not null collate nocase unique,
>> a_id integer not null references Alpha
>>);
>>create index Charlie_a_id on Charlie (a_id);
>>
>>create table AlphaBeta
>>(
>> a_id integer not null references Alpha,
>> b_id integer not null references Beta,
>> unique(a_id, b_id),
>> unique(b_id, a_id)
>>);
>>
>>create view Summary (a_attribute, b_attribute, c_attribute)
>>as select a_attribute,
>> b_attribute,
>> c_attribute
>> from Alpha, Beta, Charlie, AlphaBeta
>> where Alpha.id == Charlie.a_id
>> and Alpha.id == AlphaBeta.a_id
>> and Beta.id == AlphaBeta.b_id
>>;
>>
>>To add a record you need to do the following in your programming
>>language:
>>
>>begin immediate;
>>insert or ignore into Alpha (a_attribute) values (?); -- where
>you
>>bind a_attribute
>>select id from Alpha where a_attribute == ?; -- where
>you
>>bind a_attribute and save the a_id returned
>>insert or ignore into Beta (b_attribute) values (?); -- where
>you
>>bind b_attribute
>>select id from Beta where b_attribute == ?; -- where
>you
>>bind b_attribute and save the b_id returned
>>insert into Charlie (c_attribute, a_id) values (?, ?); -- where
>you
>>bind c_attribute and the saved a_id
>>insert into AlphaBeta values (a_id, b_id) values (?, ?); -- where
>you
>>bind the a_id and b_id
>>commit;
>>
>>If an error is thrown at any step (anywhere in the sequence) then
>>your insert data is inconsistent and you must rollback the
>>transaction.
>>
>>
>>Alternatively, you may do something like the following:
>>
>>begin immediate;
>>insert or ignore into Alpha (a_attribute) values (:a_attribute);
>>insert or ignore into Beta (b_attribute) values (:b_attribute);
>>insert into Charlie (c_attribute, a_id) values (:c_attribute,
>(select
>>a_id from Alpha where a_attribute == :a_attribute));
>>insert into AlphaBeta values ((select a_id from Alpha where
>>a_attribute == :a_attribute),
>> (select b_id from Beta where
>>b_attribute == :b_attribute));
>>commit;
>>
>>depending on the capabilities of your source language interface. I
>>would use the second because my source language interface knows how
>>to run "batches" of statements and how to bind from a named
>>dictionary (plus it only requires caching 4 statement plans as
>>opposed to 6), though in the grand scheme of things the performance
>>is about the same.
>>
>>If you were inserting a bunch of data (as in from parsing a CSV
>file,
>>for example) then you might want to only begin one transaction for
>>the whole mess and use savepoints around each row, then commit the
>>single transaction as a whole. For performance you know, since
>>spinning rust is limited in how many transactions it can perform per
>>second and the I/O rate.
>>
>>---
>>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 Rocky Ji
>>>Sent: Wednesday, 20 February, 2019 04:14
>>>To: SQLite mailing list
>>>Subject: [sqlite] What is the recommended way to write to views?
>>>
>>>Hi everyone,
>>>
>>>I know the simple answer to be `instead of insert ...`. But
>consider
>>>a
>>>situation where:
>>>
>>>Alpha has-many Beta,and
>>>Beta has-many Alpha
>>>Alpha has-many Charlie, while
>>>Charlie has one Alpha
>>>
>>>So if my SQL looks like:
>>>-- PRAGMA fk ON;
>>>create table Alpha (
>>> a_id integer primary key autoincrement,
>>> a_attribute text
>>>);
>>>
>>>create table Beta (
>>> b_id integer primary key autoincrement,
>>> b_attribute real
>>>);
>>>
>>>create table Charlie (
>>> c_id integer primary key autoincrement,
>>> c_attribute text,
>>> FK_C_a_id integer,
>>> constraint abc foreign key (FK_C_a_id) references Alpha (a_id)
>>>);
>>>
>>>create table m2mAlphaBeta (
>>> _id integer primary key autoincrement,
>>> FK_a_id integer,
>>> FK_b_id integer,
>>> constraint def foreign key (FK_a_id) references Alpha (a_id),
>>> constraint ghi foreign key (FK_b_id) references Beta (b_id)
>>>);
>>>
>>>
>>>create view summary
>>>as
>>> select
>>> a.a_attribute,
>>> b.b_attribute,
>>> c.c_attribute
>>> from
>>> m2mAlphaBeta m
>>> inner join Alpha a on a.a_id = m.FK_a_id
>>> inner join Beta b on b.b_id = m.FK_b_id
>>> inner join Charlie c on c.FK_C_a_id = a.a_id
>>>;
>>>
>>>
>>>And assuming all incoming data (say from CSV read) is correct. How
>>do
>>>I
>>>write a record to `summary`?
>>>
>>>Like how do I get ROWID of the "just inserted" record in A to
>insert
>>>into C
>>>properly. I am thinking "should I make transactions, but we don't
>>>have
>>>variables"... and going round-and-round without solution.
>>>
>>>Please recommend the canonical way to write to such views.
>>>
>>>Good day.
>>>_______________________________________________
>>>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
