Как получить поведение вставки или обновления без использования CONFLICT_REPLACE?

Мое приложение для Android использует таблицу SQLite FTS3 для обеспечения полнотекстового поиска. Я использую insertWithOnConflict с CONFLICT_REPLACE для обновления моей базы данных, вставляя новую строку, если это необходимо, или обновляя существующую строку, если она присутствует.

Я был очень удивлен, обнаружив, что моя таблица содержит повторяющиеся строки, но похоже, что это задокументированная «функция» модулей SQLite FTS:

На странице Расширения SQLite FTS3 и FTS4:

Типы данных и ограничения столбцов указываются вместе с каждым столбцом. Они полностью игнорируются FTS и SQLite.

Довольно легко воспроизвести дублирование из командной строки:

sqlite> CREATE VIRTUAL TABLE test_duplicates USING FTS3
   ...> (id INTEGER PRIMARY KEY, name TEXT);
sqlite> INSERT INTO test_duplicates (id, name) VALUES (1, "George");
sqlite> INSERT INTO test_duplicates (id, name) VALUES (1, "George");
sqlite> INSERT OR REPLACE INTO test_duplicates (id, name) VALUES (1, "George");
sqlite> SELECT * FROM test_duplicates;
1|George
1|George
1|George
sqlite>

Мой вопрос: какой лучший (самый простой, самый надежный) способ воспроизвести поведение CONFLICT_REPLACE?

Мои идеи на данный момент состоят в том, чтобы (A) выполнить SELECT, затем UPDATE или INSERT на основе результата или (B) вслепую попытаться УДАЛИТЬ существующую строку (которая может или не может присутствовать), а затем ВСТАВИТЬ.


android android-sqlite
person George    schedule 09.11.2012    source источник

Ответы (4)


arrow_upward
3
arrow_downward

ссылаясь на документ fts, я нашел этот абзац:

... каждая таблица FTS имеет столбец "rowid". Идентификатор строки таблицы FTS ведет себя так же, как столбец идентификатора строки обычной таблицы SQLite, за исключением того, что значения, хранящиеся в столбце идентификатора строки таблицы FTS, остаются неизменными, если база данных перестраивается с помощью команды VACUUM. Для таблиц FTS разрешен псевдоним "docid" наряду с обычными идентификаторами "rowid", "oid" и "oid". Попытка вставить или обновить строку со значением docid, которое уже существует в таблице, является ошибкой, как и в случае с обычной таблицей SQLite.

Это означает, что вы можете использовать встроенный столбец docid в качестве первичного ключа и позволить таблице fts применить к нему ограничение.

person kdehairy    schedule 12.11.2012

arrow_upward
1
arrow_downward

У меня такая же проблема. Я обнаружил, что когда мы CREATE VIRTUAL TABLE test_duplicates USING FTS3, он создаст столбец с именем rowid и его первичный ключ этой таблицы, так что нам просто нужно использовать rowid вместо id, который будет работать правильно.

Если вы измените: INSERT INTO test_duplicates (id, name) VALUES (1, "George");

to:

INSERT INTO test_duplicates (rowid, id, name) VALUES (1, 1, "George");
INSERT INTO test_duplicates (rowid, id, name) VALUES (2, 2, "George");
person Khoa Bui    schedule 28.04.2016

arrow_upward
0
arrow_downward

я не возьму на себя ответственность за это. но я не могу найти оригинальную ссылку, откуда я это взял. вы делаете запрос, затем:

if (cursor.moveToFirst()) {
        // record exists
    } else {
        // record not found
person mango    schedule 09.11.2012

arrow_upward
0
arrow_downward

Я использую следующий запрос для INSERT OR REPLACE

INSERT OR REPLACE INTO test_duplicates (`rowid`, `name`) VALUES 
((SELECT `rowid` FROM test_duplicates WHERE `name` = "George" LIMIT 1), "George")

И это работает. Но в этом случае вы не можете указать rowid. rowid будет обрабатываться самой базой данных.

person Sourav Bagchi    schedule 16.04.2019