Мое приложение сохраняет в БД данные, полученные с сервера каждую минуту, а также может сохранять данные из 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.