Geek asks and answers

Sqlite insert new value into view

I would like to make sure that I did everything right.

There is a 3Gb log file that I want to parse. To execute all the queries in ": memory:" to improve performance, I replace 10 columns of text with integer identifiers for each line 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 the request and a trigger for insertion.

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:

An insert through a trigger appears to be inoperative. The number of entities in the log table is much smaller than it should be, while the number of objects in secret and the application look right.

, , . . , , .

, ?

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
+5
1

id, last_insert_rowid(), , , ifnull id raw_log.
last_insert_rowid() , ...

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;
+1

More articles:


All Articles