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
