Thanks for the suggestion on `UNIQUE`, I should have done it. Based on this
new schema I wrote (pseudoSQL code):
create table dummy (d text); -- empty table to let us use
case...when...then syntax
create table variables(
variable_id integer primary key autoincrement,
name text not null,
value integer not null
);
create trigger summary_insert
instead of insert on summary
begin
select *,
case when (select a_id from Alpha where a_attribute =
NEW.a_attribute) not null
then
(insert into variables (name, value) values ("AlphaPK", (select
a_id from Alpha where a_attribute = NEW.a_attribute)))
else
-- insert into Alpha
-- get last_insert_rowid
/*
nested case...when...then till all pks are attained
then insert into appropriate tables using something like
`select max(value) from variables where name = AlphaPK`
*/
from dummy;
end;
But I guess this is too ugly, and difficult to maintain. I'll use DAL to do
all this work and insert in tables directly.
On Wed, Feb 20, 2019 at 5:53 PM Simon Slavin <slav...@bigfraud.org> wrote:
> On 20 Feb 2019, at 11:14am, Rocky Ji <rockyji3...@gmail.com> wrote:
>
> > 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`?
>
> First, congratulations on getting "integer primary key autoincrement"
> right for keys which are FOREIGN KEY keys. Common mistake. However, I
> suspect you've missed a bet. Consider
>
> > create table Alpha (
> > a_id integer primary key autoincrement,
> > a_attribute text
> > );
>
> Would "a_attribute TEXT UNIQUE" be better ? This would guard against two
> entries in Alpha having the same attribute, which I think you wouldn't
> don't want. You can do the same thing for the other _attribute columns.
>
> In answer to the question, "summary" is a VIEW. You don't have to write
> anything to it. It looks like it already does what you want.
>
> > 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.
>
> Ah, I see. Okay, I'm going to ignore Charlie for clarity. You have a CSV
> file containing two columns: a.a_attribute and b.b_attribute, and you're
> wondering how to get the data into Alpha, Beta and m2mAlphaBeta. The
> answer is ... not in one command. It can't be done.
>
> Reading two values from your CSV file, theAAttrib and theBAttrib,
>
> First, make sure that Alpha and Beta have the rows they need.
>
> INSERT OR IGNORE INTO Alpha (a_attribute) VALUES (theAAttrib);
> INSERT OR IGNORE INTO Beta (b_attribute) VALUES (theBAttrib);
>
> But you don't know whether those created new rows or not, so you have to
> find the rows you're going to want to use:
>
> newA_id = result of "SELECT a_id FROM Alpha WHERE a_attribute = '<
> theAAttrib>'"
> newB_id = result of "SELECT b_id FROM BETA WHERE b_attribute = <
> theBAttrib>"
>
> Then you have the values you need for "INSERT INTO Charlie".
>
> Unforunately, you cannot use last_insert_rowid() because you do not know
> whether your "INSERT OR IGNORE" inserted a new row or not.
>
> Fortunately, your "UNIQUE" constraints on the attribute columns means that
> you have indexes on them. This means that the "SELECT" commands will
> execute very quickly.
>
> Extend the above for Charlie.
>
> Simon.
> _______________________________________________
> 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
