embox

1
CREATE TABLE jobcards__history
2
(
3
    rowid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
4
    jobcard_id INTEGER NOT NULL,
5
    ctime REAL NOT NULL DEFAULT CURRENT_TIMESTAMP,
6
    data1 TEXT,
7
    data2 TEXT
8
);
9

10
CREATE TABLE jobcards__index
11
(
12
    jobcard_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
13
    deleted INTEGER(1) NOT NULL DEFAULT 0,
14
    first_rowid INTEGER NOT NULL DEFAULT 0,
15
    last_rowid INTEGER NOT NULL DEFAULT 0
16
);
17

18
CREATE VIEW jobcards AS
19
    SELECT jci.jobcard_id, jch.ctime, jch.data1, jch.data2
20
    FROM jobcards__index jci, jobcards__history jch
21
    WHERE jch.rowid = jci.last_rowid
22
        AND jci.deleted = 0;
23

24

25
CREATE TRIGGER jobcards__ii_update_index INSTEAD OF INSERT ON jobcards
26
BEGIN
27
    SELECT
28
            CASE
29
                WHEN NEW.jobcard_id IS NOT NULL AND MIN(rowid) IS NOT NULL
30
                    THEN RAISE(ABORT, 'duplicate key')
31
            END
32
        FROM jobcards__index
33
        WHERE jobcard_id = NEW.jobcard_id;
34
    INSERT INTO jobcards__index (jobcard_id) VALUES (NEW.jobcard_id);
35
    INSERT INTO jobcards__history
36
        (jobcard_id, data1, data2)
37
        VALUES (
38
            IFNULL(NEW.jobcard_id, last_insert_rowid()),
39
            NEW.data1,
40
            NEW.data2
41
        );
42
    UPDATE jobcards__index
43
        SET first_rowid = last_insert_rowid(),
44
            last_rowid = last_insert_rowid()
45
        WHERE jobcard_id = (
46
                SELECT jobcard_id
47
                FROM jobcards__history
48
                WHERE rowid = last_insert_rowid()
49
            );
50
END;
51

52