Сохранение уникального идентификатора в SQLite при обновлении

уважаемые посетители блога, если Вам понравилась, то, пожалуйста, помогите автору с лечением. Подробности тут.

«Отгремел» десятилетний юбилей блога webdelphi. Фанаты гуляли три дня, пили, громили магазины (шутка, конечно). Пришло время снова браться за работу, а именно — за работу с SQLite. Про эту замечательную СУБД я писал в блоге ни один раз и сегодня рассмотрим, на мой взгляд, интересный момент — как сохранить уникальный идентификатор какой-либо записи в таблице SQLite при обновлении этой самой записи? Чтобы было более понятно о чем пойдет речь, рассмотрим пример.

Есть таблица, содержащая информацию, например, по людям:

CREATE TABLE [people](
    [id] INTEGER PRIMARY KEY AUTOINCREMENT, 
    [name] TEXT NOT NULL, 
    [family] TEXT NOT NULL, 
    [age] INTEGER NOT NULL);
 
CREATE UNIQUE INDEX [pUnique]
ON [people](
    [name], 
    [family]);
 
CREATE TABLE [address](
    [aId] INTEGER PRIMARY KEY AUTOINCREMENT, 
    [aPeopleId] INTEGER UNIQUE, 
    [aAddress] TEXT);

Таким образом, в таблице address поле aPeopleId ссылается на поле id в таблице people. Есть довольно удобный способ добавления или обновления записей в SQLite — это использование запроса вида

INSERT OR REPLACE INTO ....

или

INSERT OR IGNORE INTO ....

О подобного рода запросах я писал в этой статье. Как минимум, такие запросы позволяют легко избежать дублирования записей в таблице. Однако вернемся к нашей БД. Тестовое приложение будет таким:

Для работы с SQLite я буду использовать библиотеку FireDAC. Код для добавления новых записей в таблицу напишем такой:

//добавление данных в таблицу people
  FDQuery1.SQL.Text:='INSERT OR REPLACE INTO people (name, family, age) VALUES (:name, :family, :age)';
  FDQuery1.ParamByName('name').AsString:=edName.Text;
  FDQuery1.ParamByName('family').AsString:=edFamily.Text;
  FDQuery1.ParamByName('age').AsInteger:=StrToInt(edAge.Text);
  FDQuery1.ExecSQL;
  //добавление данных в таблицу address
  FDQuery1.SQL.Text:='INSERT OR REPLACE INTO address (aPeopleId, aAddress) VALUES '+
  '((SELECT last_insert_rowid()) , :addr)';
  FDQuery1.ParamByName('addr').Text:=edAddress.Text;
  FDQuery1.ExecSQL;

Здесь мы вначале добавляем запись о человеке. Если новая запись содержит те же имя и фамилию, то происходит обновление записи после чего получается id последней записи (см. SELECT во втором запросе) и заносится запись в таблицу address. На первый взгляд все хорошо. Например, так выглядит запись об Иване Иванове:

который живет на улице Ивановской:

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

В результате мы получим следующее в таблице people:

кроме того, что изменился возраст, изменился также и уникальный идентификатор Id записи, а это, в свою очередь, привело к нарушению в таблице address:

В итоге пользователь может и не заметить, что в базе каждый раз при обновлении копятся лишние данные. Почему так произошло? Дело в том, что, когда используется конструкция вида

INSERT OR REPLACE

то SQLite действует следующим образом — если уникальность не нарушается, то в таблицу просто добавляется новая запись. Если же уникальность нарушена (как во втором нашем случае), то вначале старая запись удаляется и затем вставляется новая с, соответственно, новым уникальным Id. Есть несколько вариантов того, как избежать подобной ошибки и сохранить уникальный идентификатор записи в SQLite при обновлении. Я рассмотрю только два из них.

Способ №1. Сохраняем уникальный идентификатор в SQLite, используя функцию changes()

Перепишем наш запрос следующим образом:

  // пробуем вставить данные в таблицу people
  FDQuery1.SQL.Text := 'INSERT OR IGNORE INTO people (name, family, age) VALUES (:name, :family, :age)';
  FDQuery1.ParamByName('name').AsString := edName.Text;
  FDQuery1.ParamByName('family').AsString := edFamily.Text;
  FDQuery1.ParamByName('age').AsInteger := StrToInt(edAge.Text);
  FDQuery1.ExecSQL;
  // если вставка не удалась, то обновляем запись в таблице
  if FDConnection1.ExecSQLScalar('SELECT changes()') = 0 then
  begin
    FDQuery1.SQL.Text := 'UPDATE people SET name=:name, family=:family, age=:age WHERE name=:name AND family=:family';
    FDQuery1.ParamByName('name').AsString := edName.Text;
    FDQuery1.ParamByName('family').AsString := edFamily.Text;
    FDQuery1.ParamByName('age').AsInteger := StrToInt(edAge.Text);
    FDQuery1.ExecSQL;
  end;
 
  // добавление данных в таблицу address
  FDQuery1.SQL.Text := 'INSERT OR REPLACE INTO address (aPeopleId, aAddress) VALUES ((SELECT id FROM people WHERE name=:name AND family=:family) , :addr)';
  FDQuery1.ParamByName('addr').AsString := edAddress.Text;
  FDQuery1.ParamByName('name').AsString := edName.Text;
  FDQuery1.ParamByName('family').AsString := edFamily.Text;
  FDQuery1.ExecSQL;

Здесь, вместо конструкции

INSERT OR REPLACE

мы использовали

INSERT OR IGNORE

для проверки того, удалась ли вставка новой записи или нет мы использовали функцию SQLite changes(), которая возвращает количество изменений, произошедших в базе. Если же необходимо сохранить конструкцию

INSERT OR REPLACE

то можно предложить второй способ добавления или обновления записи.

Способ №2. Сохраняем уникальный идентификатор в SQLite, используя INSERT OR REPLACE

В том случае, когда необходимо сохранить конструкцию INSERT OR REPLACE и при этом не нарушить целостность данных в SQLite, запрос на добавление/обновление записи можно представить в следующем виде:

// пробуем вставить данные в таблицу people
  FDQuery1.SQL.Text := 'INSERT OR REPLACE INTO people (id, name, family, age) VALUES ((SELECT id FROM people WHERE name=:name AND family=:family), :name, :family, :age)';
  FDQuery1.ParamByName('name').AsString := edName.Text;
  FDQuery1.ParamByName('family').AsString := edFamily.Text;
  FDQuery1.ParamByName('age').AsInteger := StrToInt(edAge.Text);
  FDQuery1.ExecSQL;
  // добавление данных в таблицу address
  FDQuery1.SQL.Text := 'INSERT OR REPLACE INTO address (aPeopleId, aAddress) VALUES ' + '((SELECT id FROM people WHERE name=:name AND family=:family) , :addr)';
  FDQuery1.ParamByName('addr').AsString := edAddress.Text;
  FDQuery1.ParamByName('name').AsString := edName.Text;
  FDQuery1.ParamByName('family').AsString := edFamily.Text;
  FDQuery1.ExecSQL;

Здесь запрос на вставку или перезапись содержит вложенный SELECT, благодаря которому мы можем сохранить уникальный идентификатор записи.

Не знаю, относятся ли эти способы сохранения уникального идентификатора записи в SQLite при обновлении «костылями» или нет, но, в целом, оба эти способа работают.

0 0 голоса

Рейтинг статьи

Сохранение уникального идентификатора записи в SQLite при обновлении