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