SQLite. View и триггеры.

[RXL]

Обсуждение статьи: «SQLite. View и триггеры.»

[RXL]

Думаю, что CURRENT_TIMESTAMP стоит заменить на JULIANDAY('now'). Первая конструкция выдает строку, а вторая - дробное число. SQLite хранит данные "как есть" - без общего формата. Т.е. в первом случае - как сроку, во втором - как double. Тогда нужны следующие изменения:

Код: (SQL)

CREATE TABLE jobcards__history
(
    rowid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    jobcard_id INTEGER NOT NULL,
    ctime REAL NOT NULL DEFAULT (JULIANDAY('now')),
    data1 TEXT,
    data2 TEXT
);

CREATE VIEW jobcards AS
    SELECT jci.jobcard_id, DATETIME(jch.ctime) ctime, jch.data1, jch.data2
    FROM jobcards__index jci, jobcards__history jch
    WHERE jch.rowid = jci.last_rowid
        AND jci.deleted = 0;

Тесты показали те же результаты. Интерфейс jobcards не изменился.

Только выборка из jobcards__history показала REAL вместо строки DATETIME.

Вывод теста триггера вставки:

sqlite> SELECT * FROM jobcards__history;
1|1|2455844.17294397|aaa|bbb
2|2|2455844.17301559|ccc|ddd

Вывод теста триггера обновления:

sqlite> SELECT * FROM jobcards__history;
1|1|2455844.17294397|aaa|bbb
2|2|2455844.17301559|ccc|ddd
3|1|2455844.17344936|eee|bbb

[RXL]

Отработало правильно. Обратите внимание на первый и третий оператор — это не мой триггер нашел ошибки, а SQLite сам так решил, на основе предоставленных ему инструкций: изменяемые столбцы перечислены в определении триггера. Второй и четвертый оператор — явная бага (или фича?) SQLite. Значит проверки на изменение запрещенных полей нужны обязательно.

Почитал детальнее и оказалось, что это стандартное поведение. Перечисленные в триггере столбцы - это как бы селекторы. Если в UPDATE не изменяется ни один из перечисленных столбцов, то триггер не срабатывает. Соответственно, проверки на недопустимые изменения нужны обязательно.


И про систему блокировок: есть еще режим журнала WAL (write a head). В этом случае в журнал пишутся не старые замещенные данные, а наоборот новые, для еще не завершенных транзакций. Это позволяет выполнять одновременно чтение из базы и работу транзакций с записью и модификацией данных. Тип журнала переключается прагмой:

PRAGMA dbname.journal_mode = WAL;

Данный тип журнала сохраняется в базе и при открытии базы снова стартует. Но есть (по крайней мере в версии 3.7.0, которой я пользуюсь) один аспект (или баг): после выполнения VACUUM, закрытия базы и последующего ее открытия режим журнала сбрасывается на "DELETE". По этому до VACUUM проверьте режим, после закройте базу, вновь откройте и установите WAL.
Вот лог тестирования:

$ sqlite3 t2.sqlite
SQLite version 3.7.0.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> PRAGMA main.journal_mode;
wal

Видим, что база в режиме WAL.

sqlite> VACUUM;
sqlite> PRAGMA main.journal_mode;
wal

Пусть вас это не обманывает!

sqlite> PRAGMA main.journal_mode = WAL;
wal

А это сейчас не поможет...

sqlite> .quit
$ sqlite3 t2.sqlite
SQLite version 3.7.0.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> PRAGMA main.journal_mode;
delete

Вот так вот...

sqlite> PRAGMA main.journal_mode = WAL;
wal
sqlite> .quit
$ sqlite3 t2.sqlite
SQLite version 3.7.0.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> PRAGMA main.journal_mode;
wal

Теперь все путем.

Кстати, режим WAL был добавлен разработчиками в SQLite как ответ на действия компании Oracle, предлагавшую свою базу данных в этот сегмент рынка и даже (!) поддерживающую API SQLite. С этим режимом у Oracle пропали все преимущества.


Добавлено через 12 часов, 56 минут и 16 секунд:
Проверил  в версии 3.7.8 этой баги нет.