sqlite insert new value into view

Question

I would like to make sure I did everything in a right way.

There is a 3Gb log file which I want to analysis. In order to execute all queries in ":memory:" to boost the performance, I replace the 10 text columns to integer ids for each row of logs.

create table if not exists app (
    id Integer primary key autoincrement,
    value text unique
);

create table if not exists secret (
    id integer primary key autoincrement,
    value text unique
);

and 10 more tables

create table if not exists raw_log
(
    id Integer primary key autoincrement,
    app_id INTEGER,
    secret_id INTEGER,
    and 10 more _id columns
 );

and create a view for query and a trigger for insert.

create view if not exists log as 
    Select 
        raw_log.id,
        app.value as app,

        secret.value as secret,
        and 10 more ...

        from raw_log, app, secret, ..... x 10
        where raw_log.app_id = app_id.id and raw_log.secret = secret.id and ... x 10


CREATE TRIGGER insert_log 
    INSTEAD OF INSERT ON log 
    FOR EACH ROW BEGIN 
INSERT OR IGNORE INTO app(value) VALUES(NEW.app);
INSERT OR IGNORE INTO secret(value) values(NEW.secret);
... x 10

INSERT INTO raw_log(app_id,secret_id, .... x 10)
select app.id, secret.id, x 10
from app, secret, x 10
where app.value = NEW.app 
and secret.value = NEW.secret 
and ... x 10
END;          

questions:

The insert via trigger looks not work. The entities number in log table is much less than it should be, while entities number in secret and app looks correctly.

I think it is because while a new app and secret appear in log. They can be insert into table without problem. However, since these changes are not committed yet, the following query cannot reference these value successfully.

If so, how can I fix these query?

INSERT INTO raw_log(app_id,secret_id, .... x 10)
        select app.id, secret.id, x 10
            from app, secret, x 10
            where app.value = NEW.app 
                and secret.value = NEW.secret 
                and ... x 10

Answer 1

To get inserted id use last_insert_rowid(), but if i'm not sure do you get id on conflict, so you must use ifnull to get id for insert into raw_log.
You mast save value of last_insert_rowid() before next insert, that's why you must use Variables...

CREATE TEMP TABLE IF NOT EXISTS _Variables (Name TEXT PRIMARY KEY, Value TEXT);
...
INSERT OR IGNORE INTO app(value) VALUES(NEW.app);
INSERT OR REPLACE INTO _Variables(Key, Value) VALUES('app_id', ifnull((SELECT app.id from app where app.value = NEW.app), last_insert_rowid());
...
INSERT INTO raw_log(app_id, secret_id, ... x 10)
values((SELECT Value FROM _Variables WHERE Key = 'app_id')
, (SELECT Value FROM _Variables WHERE Key = 'secret_id'), ... x 10 );
END;