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
