table_change_log_script.tmpl

{{$ChangeLogTableName := (printf "%s%s_change_log" .Prefix .TableName)}}
{{$GlobalChangeLogTableName := (printf "%s_change_log_global" .Prefix)}}

CREATE TABLE IF NOT EXISTS {{$ChangeLogTableName}} (
    id   INTEGER PRIMARY KEY AUTOINCREMENT,
{{range $index, $col := .Columns}}
    val_{{$col.Name}} {{$col.Type}},
{{end}}
    type TEXT,
    created_at INTEGER,
    state INTEGER
);

CREATE INDEX IF NOT EXISTS {{$ChangeLogTableName}}_state_index ON {{$ChangeLogTableName}} (state);

{{range $trigger, $read_target := .Triggers}}
DROP TRIGGER IF EXISTS {{$ChangeLogTableName}}_on_{{$trigger}};
CREATE TRIGGER IF NOT EXISTS {{$ChangeLogTableName}}_on_{{$trigger}}
AFTER {{$trigger}} ON {{$.TableName}}
WHEN (SELECT COUNT(*) FROM pragma_function_list WHERE name='marmot_version') < 1
BEGIN

    INSERT INTO {{$ChangeLogTableName}}(
        {{range $col := $.Columns}}
            val_{{$col.Name}},
        {{end}}
        type,
        created_at,
        state
    ) VALUES(
        {{range $col := $.Columns}}
            {{$read_target}}.{{$col.Name}},
        {{end}}
        '{{$trigger}}',
        CAST((strftime('%s','now') || substr(strftime('%f','now'),4)) as INT),
        0 -- Pending
    );

    INSERT INTO {{$GlobalChangeLogTableName}} (change_table_id, table_name)
    VALUES (
        last_insert_rowid(),
        '{{$.TableName}}'
    );

END;
{{end}}