Я бы хотел использовать SQLite FTS3 (FTS4, на самом деле) для индексации таблицы с целочисленные столбцы, концептуально примерно так:
CREATE VIRTUAL TABLE whole (document INTEGER, page INTEGER, content TEXT,
UNIQUE(document, page)) USING fts4();
Я знаю, что FTS3 обрабатывает все столбцы, кроме rowid, как ТЕКСТ, поэтому мне придется использовать две таблицы:
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 требует от меня обеспечения синхронизации идентификаторов строк, но как лучше всего это сделать? Должен ли я использовать FOREIGN KEY или другое ограничение? Будет ли подвыборка лучше, чем присоединение?
Мне нужна вставка для документа и страницы, уже находящихся в базе данных, чтобы перезаписать текстовое содержимое. Возможно ли это программно через SQL, или мне нужно будет проверить, существует ли уже строка в информационной таблице?
Я также хочу УДАЛИТЬ ИЗ обеих таблиц для данного документа - есть ли способ сделать это одним оператором?
Все советы получены с благодарностью, но, поскольку я новичок в SQL, мне особенно понравились образцы кода!
Обновление: Мне совсем не понятно, как я могу создать чужой ключевое ограничение здесь. Если я выберу метаданные
в качестве родительской таблицы (что было бы моим предпочтением, в отсутствие ограничения двунаправленности):
PRAGMA foreign_keys = ON;
CREATE TABLE metadata (document INTEGER, page INTEGER);
CREATE VIRTUAL TABLE data USING fts4(content TEXT, docid REFERENCES metadata);
я получаю Ошибка: сбой конструктора vtable: данные
(неудивительно , потому что 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);
я получаю Ошибка: несоответствие внешнего ключа
.