Работа с SQLite. Вопросы жизненного цикла

Вопросы программирования и использования среды Lazarus.

Модератор: Модераторы

Работа с SQLite. Вопросы жизненного цикла

Сообщение AlexDanilov » 01.07.2017 03:51:37

Добрый вечер.

Мое приложение сохраняет в БД данные, полученные с сервера каждую минуту, а также может сохранять данные из UI.

Я написал небольшую прослойку для DB. Прошу покритиковать:
Код: Выделить всё
unit dbhelper;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, Dialogs, sqlite3conn, sqldb, db, fpjson, lazlogger,
  models;

type
    TEntity = class(TBaseModel)
      public
        TypeName, JSON: String;
        SaveOnServer: boolean;

        constructor Create;
        constructor Create(TheID: String; TheTypeName: String; TheJSON: String; TheUpdatedAt: TDateTime; TheSaveOnServer: boolean);
    end;

    TEntitiesArray = array of TEntity;
    TOrders = array of TOrder;

    TDb = class(TObject)
      public
        constructor Create;

        function GetOrder(ID: String): TOrder;
        function GetOrders(): TOrders;
        procedure SaveOrder(TheOrder: TOrder; SaveOnServer: boolean);
        procedure UpdateOrder(TheOrder: TOrder; SaveOnServer: boolean);
        function GetEntity(ID, TypeName: String): TEntity;
        function GetEntities(TypeName: String): TEntitiesArray;
        function GetUnsavedEntities(): TEntitiesArray;
        procedure SaveEntity(TheEntity: TEntity; SaveOnServer: boolean);
        procedure UpdateEntity(TheEntity: TEntity; SaveOnServer: boolean);
        procedure Close;

      private
        Conn: TSQLite3Connection;
        Transaction: TSQLTransaction;
    end;

implementation

constructor TEntity.Create;
begin
  SaveOnServer := false;
  UpdatedAt := now();
end;

constructor TEntity.Create(TheID: String; TheTypeName: String; TheJSON: String; TheUpdatedAt: TDateTime; TheSaveOnServer: boolean);
begin
  ID := TheID;
  TypeName := TheTypeName;
  JSON := TheJSON;
  UpdatedAt := TheUpdatedAt;
  SaveOnServer := TheSaveOnServer;
end;

constructor TDb.Create;
var
  DBExists: boolean;
begin
  Conn := TSQLite3Connection.Create(nil);
  Conn.DatabaseName := 'terminal.db';
  DbExists := FileExists(Conn.DatabaseName);

  Conn.Connected := true;
  Transaction := TSQLTransaction.Create(nil);
  Transaction.SQLConnection := Conn;

  if not DBExists then begin
    Conn.Open;
    Transaction.Active := true;

    // Create an entity table
    Conn.ExecuteDirect('CREATE TABLE IF NOT EXISTS entities (' +
       'id TEXT PRIMARY KEY, type TEXT, json TEXT, updated_at TEXT, ' +
       'save_on_server INTEGER)');

    // Creating an indexes
    Conn.ExecuteDirect('CREATE INDEX type_idx ON entities (type);');
    Conn.ExecuteDirect('CREATE INDEX updated_at_idx ON entities (updated_at);');

    Transaction.Commit;
    Transaction.EndTransaction;
  end;
end;

function TDb.GetOrder(ID: String): TOrder;
var entity: TEntity;
  jsonObj: TJSONObject;
begin
  entity := GetEntity(ID, 'order');

  GetOrder := nil;
  if entity <> nil then begin
    try
      jsonObj := TJSONObject(GetJSON(entity.JSON));
    except
      ShowMessage('Error parsing JSON:Entity');
      exit;
    end;

     GetOrder := TOrder.Create(jsonObj);
     GetOrder.UpdatedAt := entity.UpdatedAt;
  end;
end;

function TDb.GetOrders: TOrders;
var
  i, len: Integer;
  TheOrder: TOrder;
  Entities: TEntitiesArray;
  jsonObj: TJSONObject;
begin
  Entities := GetEntities('order');
  len := Length(Entities);
  SetLength(GetOrders, len);

  if len = 0 then exit;

  for i := 0 to len - 1 do begin
    if Entities[i] = nil then continue;

    try
      jsonObj := TJSONObject(GetJSON(Entities[i].JSON));
    except on E: Exception do begin
      ShowMessage('Error parsing JSON:Entity' + pchar(E));
      continue;
      end;
    end;

    TheOrder := TOrder.Create(jsonObj);
    TheOrder.UpdatedAt := Entities[i].UpdatedAt;

    GetOrders[i] := TheOrder;
  end;
end;

procedure TDb.SaveOrder(TheOrder: TOrder; SaveOnServer: boolean);
var entity: TEntity;
begin
  entity := TEntity.Create(TheOrder.ID, 'order', TheOrder.ToJson.FormatJSON(), TheOrder.UpdatedAt, false);
  SaveEntity(entity, SaveOnServer);
end;

procedure TDb.UpdateOrder(TheOrder: TOrder; SaveOnServer: boolean);
var entity: TEntity;
begin
  entity := TEntity.Create(TheOrder.ID, 'order', TheOrder.ToJson.FormatJSON(), TheOrder.UpdatedAt, false);
  UpdateEntity(entity, SaveOnServer);
end;

procedure TDb.UpdateEntity(TheEntity: TEntity; SaveOnServer: boolean);
var existsEntity: TEntity;
begin
  existsEntity := GetEntity(TheEntity.ID, TheEntity.TypeName);

  if existsEntity = nil then begin
    SaveEntity(TheEntity, SaveOnServer);
    exit;
  end;

  if TheEntity.UpdatedAt > existsEntity.UpdatedAt then
    SaveEntity(TheEntity, SaveOnServer);
end;

function TDb.GetEntity(ID, TypeName: String): TEntity;
var Q: TSQLQuery;
begin
  Q := TSQLQuery.Create(nil);
  Q.DataBase := Conn;
  Q.Transaction := Transaction;

  Q.SQL.Text := 'SELECT * FROM entities WHERE type=:type AND id=:id LIMIT 1';
  Q.Params.ParamByName('id').AsString := ID;
  Q.Params.ParamByName('type').AsString := TypeName;
  Q.Open;
  Q.ExecSQL;

  GetEntity := nil;

  if Q.RecordCount > 0 then begin;
    Q.First;

    GetEntity := TEntity.Create;

    GetEntity.ID := ID;
    GetEntity.TypeName := TypeName;
    GetEntity.JSON := Q.FieldByName('json').AsString;
    GetEntity.SaveOnServer := Q.FieldByName('save_on_server').AsBoolean;
    if Q.FieldByName('updated_at').AsString <> '' then
      GetEntity.UpdatedAt := GetEntity.ToDateTime(Q.FieldByName('updated_at').AsString)
    else
      GetEntity.UpdatedAt := 0;
  end;
end;

function TDb.GetEntities(TypeName: String): TEntitiesArray;
var
  i, total: Integer;
  Q: TSQLQuery;
  Entity: TEntity;
begin
  Q := TSQLQuery.Create(nil);
  Q.DataBase := Conn;
  Q.Transaction := Transaction;

  // get records count
  Q.SQL.Text := 'SELECT COUNT(*) total FROM entities WHERE type=:type';
  Q.Params.ParamByName('type').AsString := TypeName;
  Q.Open;
  Q.ExecSQL;
  Q.First;
  total := Q.FieldByName('total').AsInteger;
  Q.Close;

  // get entities
  Q.SQL.Text := 'SELECT * FROM entities WHERE type=:type ORDER BY updated_at';
  Q.Params.ParamByName('type').AsString := TypeName;
  Q.Open;
  Q.ExecSQL;

  SetLength(GetEntities, total);
  if Q.RecordCount > 0 then begin
    i := 0;
    Q.First;

    while not Q.EOF do begin
      Entity := TEntity.Create;

      Entity.ID := Q.FieldByName('id').AsString;
      Entity.TypeName := Q.FieldByName('type').AsString;
      Entity.JSON := Q.FieldByName('json').AsString;
      Entity.SaveOnServer := Q.FieldByName('save_on_server').AsBoolean;
      if Q.FieldByName('updated_at').AsString <> '' then
        Entity.UpdatedAt := Entity.ToDateTime(Q.FieldByName('updated_at').AsString)
      else
        Entity.UpdatedAt := 0;

      GetEntities[i] := Entity;

      inc(i);
      Q.Next;
    end;
  end;

  //Q.Close;
  //Q.Free;
end;

function TDb.GetUnsavedEntities: TEntitiesArray;
var
  i: Integer;
  Q: TSQLQuery;
  Entity: TEntity;
begin
  Q := TSQLQuery.Create(nil);
  Q.DataBase := Conn;
  Q.Transaction := Transaction;

  Q.SQL.Text := 'SELECT * FROM entities WHERE save_on_server = 0';
  Q.Open;
  Q.ExecSQL;

  SetLength(GetUnsavedEntities, Q.RecordCount);
  if Q.RecordCount > 0 then begin
    i := 0;
    Q.First;

    while not Q.EOF do begin
      Entity := TEntity.Create;

      Entity.ID := Q.FieldByName('id').AsString;
      Entity.TypeName := Q.FieldByName('type').AsString;
      Entity.JSON := Q.FieldByName('json').AsString;
      Entity.SaveOnServer := Q.FieldByName('save_on_server').AsBoolean;
      if Q.FieldByName('updated_at').AsString <> '' then
        Entity.UpdatedAt := Entity.ToDateTime(Q.FieldByName('updated_at').AsString)
      else
        Entity.UpdatedAt := 0;

      GetUnsavedEntities[i] := Entity;

      inc(i);
      Q.Next;
    end;
  end;
 
  //Q.Close;
  //Q.Free;
end;

procedure TDb.SaveEntity(TheEntity: TEntity; SaveOnServer: boolean);
var Q: TSQLQuery;
  existsEntity: TEntity;
begin
  Q := TSQLQuery.Create(nil);
  Q.DataBase := Conn;
  Q.Transaction := Transaction;

  //ShowMessage(TheEntity.JSON);

  if not Transaction.Active then Transaction.StartTransaction;

  existsEntity := GetEntity(TheEntity.ID, TheEntity.TypeName);

  if existsEntity = nil then
    Q.SQL.Text := 'INSERT INTO entities (id, type, json, updated_at, save_on_server)' +
    ' VALUES (:id, :type, :json, :updated_at, :saved)'
  else begin
    // in db we have newest version
    if not existsEntity.SaveOnServer and TheEntity.SaveOnServer then exit;

    Q.SQL.Text := 'UPDATE entities SET json=:json, updated_at=:updated_at, ' +
    'save_on_server=:saved WHERE type=:type AND id=:id';
  end;

  Q.Params.ParamByName('id').AsString := TheEntity.ID;
  Q.Params.ParamByName('json').AsString := TheEntity.JSON;
  Q.Params.ParamByName('type').AsString := TheEntity.TypeName;
  Q.Params.ParamByName('saved').asBoolean := SaveOnServer;
  if TheEntity.UpdatedAt <> 0 then
    Q.Params.ParamByName('updated_at').AsString := TheEntity.FromDateTime(TheEntity.UpdatedAt);

  Q.ExecSQL;
  Transaction.Commit;

  //Q.Close;
  //Q.Free;
end;

procedure TDb.Close;
begin
  if Transaction.Active then Transaction.EndTransaction;
  //Transaction.Free;
  Conn.Close(false);
end;

end.


Вопросы:

1. Если раскометировать Q.Close; и Q.Free, то приложение вылетает с SIGSEGV или с SIGIIL ошибками. Разве я не должен после создания и использования TSQLQuery закрыть и прибить?

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

3. Не уверен, что правильно работаю с запросами, открывая соединение, транзакцию, SQLQUery – как-то много кода ради SELECT.
AlexDanilov
новенький
 
Сообщения: 17
Зарегистрирован: 24.10.2016 15:11:46
Откуда: Kiev

Re: Работа с SQLite. Вопросы жизненного цикла

Сообщение Снег Север » 01.07.2017 06:43:40

Извините, но код подробно не рассматривал. Ошибка при Q.Close; и Q.Free может быть связана с тем, что транзакция к этому моменту не закончилась. Аналогично, Transaction.Free даст ошибку, если на нем сидят незакрытые Query.
Нерационально создавать TSQLQuery каждый раз, когда вы делаете запрос. Лучше создать, например, три TSQLQuery - для select, insert и update и менять в них SQL.

В SQLQuery AfterPost запишите:
Код: Выделить всё
     (DataSet as TSQLQuery).ApplyUpdates();
     ((DataSet as TSQLQuery).Transaction as TSQLTransaction).CommitRetaining;
Аватара пользователя
Снег Север
долгожитель
 
Сообщения: 2993
Зарегистрирован: 27.11.2007 16:14:47

Re: Работа с SQLite. Вопросы жизненного цикла

Сообщение olegy123 » 01.07.2017 12:28:25

Снег Север писал(а):Извините, но код подробно не рассматривал. Ошибка при Q.Close; и Q.Free может быть связана с тем, что транзакция к этому моменту не закончилась. Аналогично, Transaction.Free даст ошибку, если на нем сидят незакрытые Query.

Не.. можно много сделать операций и только потом транзакцию закрыть..
Также не обязательно закрывать Query - Query - это набор данных у клиента, будет ли он очищен или нет не так важно для сервера.

Тут вижу две причины:
1) не соблюдены открывание/завершения транзакции. Query может сама открывать и закрывать Transaction.
Тут лучше выключите автоматическое Commint у Query. И пытайтесь разрулить самим.
Transaction.StartTransaction();
Query.ExecSQL();
Query.Open();
Query.Close();
Query.ExecSQL();
Transaction.Commit();

- так должно работать.

2) Вполне возможно инные, глубинные ошибки. От понимания поведения движка Бд.. до ошибок в Dll. Т.е. Query. Transaction. - это всего лишь обобщенный для вас механизм, которое подключается к TSQLite3Connection, также и к другим движкам БД. Те в свою очередь подгружают Dll-ки. В которых могут быть ошибки, которые должны исправить в следующих версиях. Вы можете один из первых кто эту ошибку найдет.

У меня при многопоточном работе с MySQL - мое серверное приложение падало. Оказалось, что нужно при создании потока обязательно сообщить DLL-ке что требуется защита внутренних локальных переменных.
Стандартно в TMySQLConnection такого метода не было.. пришлось писать свой класс (TMySQLMultiThreadConnection)на базе TMySQLConnection с данной функцией.
olegy123
долгожитель
 
Сообщения: 1643
Зарегистрирован: 25.02.2016 12:10:20

Re: Работа с SQLite. Вопросы жизненного цикла

Сообщение AlexDanilov » 01.07.2017 16:02:10

Снег Север писал(а):Ошибка при Q.Close; и Q.Free может быть связана с тем, что транзакция к этому моменту не закончилась. Аналогично, Transaction.Free даст ошибку, если на нем сидят незакрытые Query.

А разве выполнение запроса не блокирующая операция? Я полагал, что пока запрос выполняется происходит блокировка.

Добавлено спустя 1 минуту 13 секунд:
olegy123 писал(а):Тут лучше выключите автоматическое Commint у Query. И пытайтесь разрулить самим.
Transaction.StartTransaction();
Query.ExecSQL();
Query.Open();
Query.Close();
Query.ExecSQL();
Transaction.Commit();


А для чего нужен второй ExecSQL после Close?
AlexDanilov
новенький
 
Сообщения: 17
Зарегистрирован: 24.10.2016 15:11:46
Откуда: Kiev

Re: Работа с SQLite. Вопросы жизненного цикла

Сообщение Снег Север » 01.07.2017 16:13:11

AlexDanilov писал(а):А разве выполнение запроса не блокирующая операция? Я полагал, что пока запрос выполняется происходит блокировка.

Transaction будет блокировать БД от реальных изменений накапливая изменения в некотором стеке. Реальная запись произойдет после 1)Query.Post, 2)Query.ApplyUpdates и 3)Transaction.Commit (Transaction.CommitRetaining - у меня в реальном коде, не помню почему, просто Commit не работал). Но пока вся это цепочка не сработала, вызов Free может привести (и приводит) к AV.
Напомню, что SQLite - это принципиально однопользовательская БД. Для многопользовательских серверных (типа MySQL) всё работает несколько иначе.
Аватара пользователя
Снег Север
долгожитель
 
Сообщения: 2993
Зарегистрирован: 27.11.2007 16:14:47

Re: Работа с SQLite. Вопросы жизненного цикла

Сообщение serbod » 01.07.2017 20:53:01

Схема такая. Если это запрос таблицы, то:
Код: Выделить всё
  Q.SQL.Text := 'SELECT * FROM entities WHERE type=:type AND id=:id LIMIT 1';
  Q.Params.ParamByName('id').AsString := ID;
  Q.Params.ParamByName('type').AsString := TypeName;
  Q.Open(); // открываем запрос, он выполняется и становится на первую строку
  while not Q.Eof do // пока не конец таблицы
  begin
    Entity.ID := Q.FieldByName('id').AsString;
    Entity.TypeName := Q.FieldByName('type').AsString;
    Entity.JSON := Q.FieldByName('json').AsString;
    Q.Next(); // следующая строка
  end;
  Q.Close(); // закрываем таблицу


Если это не запрос, а действие без чтения результата, то вместо Open() используем ExecSQL();
Код: Выделить всё
Q.SQL.Text := 'INSERT INTO entities (id, type, json, updated_at, save_on_server)' +
    ' VALUES (:id, :type, :json, :updated_at, :saved)';
Q.Params.ParamByName('id').AsString := TheEntity.ID;
Q.Params.ParamByName('json').AsString := TheEntity.JSON;
Q.ExecSQL(); // выполняем запрос


При создании объектов внутри процедуры необходимо использовать конструкцию:
Код: Выделить всё
  Q := TSQLQuery.Create(nil);
  try
    //...
  finally
    Q.Free();
  end;


Для курсовой работы это не обязательно, а для реального кода это должно войти в привычку.
Также должно войти в привычку, если в одной процедуре создаете объекты и назначаете их друг другу, то сразу в соседней процедуре необходимо написать назначение nil в свойства и удаление объектов в обратном порядке. То есть создание и удаление объектов должны быть не в разных концах программы, а максимально близко друг к другу.
Аватара пользователя
serbod
постоялец
 
Сообщения: 449
Зарегистрирован: 16.09.2016 11:03:02
Откуда: Минск

Re: Работа с SQLite. Вопросы жизненного цикла

Сообщение olegy123 » 02.07.2017 07:12:25

AlexDanilov писал(а):А для чего нужен второй ExecSQL после Close?

для примера, что внтури транзакции могут быть разные операции (выборка,вставка,удаление,обновление) и только потом подтверждение общей транзакцией.
olegy123
долгожитель
 
Сообщения: 1643
Зарегистрирован: 25.02.2016 12:10:20

Re: Работа с SQLite. Вопросы жизненного цикла

Сообщение Снег Север » 02.07.2017 08:13:39

serbod писал(а):Также должно войти в привычку, если в одной процедуре создаете объекты и назначаете их друг другу, то сразу в соседней процедуре необходимо написать назначение nil в свойства и удаление объектов в обратном порядке. То есть создание и удаление объектов должны быть не в разных концах программы, а максимально близко друг к другу.

В данном случае это сомнительный совет. SQL компоненты предпочтительнее создавать при создании формы и удалять только при ее уничтожении.

И нельзя забывать про ApplyUpdates.
Аватара пользователя
Снег Север
долгожитель
 
Сообщения: 2993
Зарегистрирован: 27.11.2007 16:14:47

Re: Работа с SQLite. Вопросы жизненного цикла

Сообщение serbod » 02.07.2017 19:22:12

Снег Север писал(а):SQL компоненты предпочтительнее создавать при создании формы и удалять только при ее уничтожении.

Согласен. Это другой правильный подход, привязка компонента к родителю/владельцу. При освобождении владельца автоматически освобождаются все подчиненные объекты.

Добавлено спустя 10 часов 29 минут 6 секунд:
Снег Север писал(а):И нельзя забывать про ApplyUpdates.

Это если открытый датасет редактировался методами самого датасета, а не SQL-командами.
Аватара пользователя
serbod
постоялец
 
Сообщения: 449
Зарегистрирован: 16.09.2016 11:03:02
Откуда: Минск


Вернуться в Lazarus

Кто сейчас на конференции

Сейчас этот форум просматривают: нет зарегистрированных пользователей и гости: 36

Рейтинг@Mail.ru