Использование SQLite FTS3 со столбцами INTEGER

Я бы хотел использовать SQLite FTS3 (фактически FTS4) для индексации таблицы с целыми столбцами, концептуально что-то вроде этого:

CREATE VIRTUAL TABLE whole (document INTEGER, page INTEGER, content TEXT, 
    UNIQUE(document, page)) USING fts4();

Я знаю, что FTS3 рассматривает все столбцы, отличные от rowid, как TEXT, поэтому мне придется использовать две таблицы:

CREATE VIRTUAL TABLE data USING fts4();
CREATE TABLE metadata(document INTEGER, page INTEGER, UNIQUE(document, page));

Я хочу иметь возможность запрашивать документы или страницы в документе:

SELECT DISTINCT document FROM metadata NATURAL JOIN data WHERE content MATCH 'foo';
SELECT page FROM metadata NATURAL JOIN data 
    WHERE document = 123 AND content MATCH 'foo';

Я думаю, что NATURAL JOIN требует от меня убедиться, что rowids синхронизированы, но что лучший способ сделать это? Должен ли я использовать FOREIGN KEY или другое ограничение? Будет ли суб-выбор лучше, чем соединение?

Мне нужна вставка для документа и страницы уже в базе данных, чтобы перезаписать текстовое содержимое. Возможно ли это программно с помощью SQL или мне нужно проверить, существует ли строка в информационной таблице?

Я тоже захочу УДАЛИТЬ ИЗ обеих таблиц для данного документа - есть ли способ сделать это в одном заявлении?

Все советы с благодарностью получены, но поскольку я новичок в SQL, примеры кода особенно ценятся!

Обновление: Это совсем не ясно, как я могу создать ограничение внешнего ключа здесь. Если я выберу metadata в качестве родительской таблицы (это было бы моим предпочтением, если бы не было двунаправленного ограничения):

PRAGMA foreign_keys = ON;
CREATE TABLE metadata (document INTEGER, page INTEGER);
CREATE VIRTUAL TABLE data USING fts4(content TEXT, docid REFERENCES metadata);

Я получаю Error: vtable constructor failed: data (неудивительно, потому что docid является псевдонимом для rowid, но, конечно, я не могу использовать другой столбец, потому что все столбцы, кроме rowid, должны быть TEXT).

Если я попробую наоборот:

PRAGMA foreign_keys = ON;
CREATE VIRTUAL TABLE data USING fts4();
CREATE TABLE metadata (document INTEGER, page INTEGER, docid REFERENCES data);

построение таблицы преуспевает, но если я попробую:

INSERT INTO data (docid, content) VALUES (123, 'testing');
INSERT INTO metadata (docid, document, page) VALUES (123, 12, 23);

Я получаю Error: foreign key mismatch.

Ответ 1

Короче говоря, довольно сложно обеспечить согласованность, в которой участвует FTS3.

Виртуальные таблицы SQLite не допускают триггеры, а таблицы FTS3 игнорируют ограничения и сродства.

Лучшее, что я смог сделать до сих пор, заключается в следующем:

CREATE TABLE metadata (document INTEGER, page INTEGER, UNIQUE(document, page));
CREATE VIRTUAL TABLE data USING fts4();

CREATE VIEW whole AS SELECT metadata.rowid AS rowid, document, page, content 
    FROM metadata JOIN data ON metadata.rowid = data.rowid;

CREATE TRIGGER whole_insert INSTEAD OF INSERT ON whole
BEGIN
  INSERT INTO metadata (document, page) VALUES (NEW.document, NEW.page);
  INSERT INTO data (rowid, content) VALUES (last_insert_rowid(), NEW.content);
END;

CREATE TRIGGER whole_delete INSTEAD OF DELETE ON whole
BEGIN
  DELETE FROM metadata WHERE rowid = OLD.rowid;
  DELETE FROM data WHERE rowid = OLD.rowid;
END;

Чтобы обеспечить согласованность, я мог (с помощью PRAGMA recursive_triggers = NO) создавать триггеры для создания исключений для прямых операций в таблицах metadata и data, но это, вероятно, слишком сложно для моих целей (также мне не нужно UPDATE для таблицы whole).

Ответ 2

Я думаю, что большинство администраторов баз данных согласятся с тем, что если вы используете SQL, вы должны воспользоваться всеми возможностями, которые он предлагает.

Внешние ключи - это общий маршрут, который я бы рекомендовал, и они документированы здесь.

В целом с базами данных SQL вы никогда не должны вручную обеспечивать согласованность. В частности, в случаях, когда они подходят друг другу с помощью внешнего ключа.

Для случая DELETE FROM SQLite не поддерживает ключевое слово "каскад", например, MS SQL, но имеет триггеры, которые позволяют вам иметь такое поведение в любом случае. Документацию для триггеров SQLite можно найти здесь.

Наконец, я пропустил бы естественное соединение.