Sordie.co.uk

libsassy/libSassy.Database.pas

Raw

{(
 )) libSassy.Database
((    SQLite database library
 ))
((  Copyright  Sordie Aranka Solomon-Smith 2015-2016
 ))
((  This work is made available under the terms of the Creative Commons
 )) Attribution-NonCommercial-ShareAlike 3.0 Unported license
((  http://creativecommons.org/licenses/by-nc-sa/3.0/
 )}

unit libSassy.Database;

interface

uses
  libSassy.Interfaces,
  libSassy.Log,
  libSassy.Strings;

{$REGION 'SQLite API'}
type
  TSQLiteDB    = Pointer;
  TSQLiteQuery = Pointer;

const
  SQLITE_OK   = 0;
  SQLITE_ROW  = 100;
  SQLITE_DONE = 101;

  SQLITE_INTEGER = 1;
  SQLITE_TEXT    = 3;
  SQLITE_NULL    = 5;

{$IFDEF CPUX64}
  sqlite3 = 'SQLite3-64.dll';
{$ELSE}
  sqlite3 = 'SQLite3-32.dll';
{$ENDIF}

function sqlite3_open16(FileName: PChar; var DB: TSQLiteDB): Integer; cdecl; external sqlite3;
function sqlite3_close(DB: TSQLiteDB): Integer; cdecl; external sqlite3;
function sqlite3_prepare16(DB: TSQLiteDB; QueryStr: PChar; QueryLen: Integer; var Query: TSQLiteQuery; var NextQuery: PChar): Integer; cdecl; external sqlite3;
function sqlite3_step(Query: TSQLiteQuery): Integer; cdecl; external sqlite3;
function sqlite3_finalize(Query: TSQLiteQuery): Integer; cdecl; external sqlite3;
function sqlite3_column_count(Query: TSQLiteQuery): Integer; cdecl; external sqlite3;
function sqlite3_column_name16(Query: TSQLiteQuery; i: Integer): PChar; cdecl; external sqlite3;
function sqlite3_column_type(Query: TSQLiteQuery; i: Integer): Integer; cdecl; external sqlite3;
function sqlite3_column_int(Query: TSQLiteQuery; i: Integer): Integer; cdecl; external sqlite3;
function sqlite3_column_text16(Query: TSQLiteQuery; i: Integer): PChar; cdecl; external sqlite3;
{$ENDREGION}

type
  TTable = class;

{$REGION 'TDatabase'}
  TDatabase = class(TInterface)
  private
    fDB:       TSQLiteDB;
    fFileName: String;

    fLogObject: TLog;
    fLogPrefix: String;

    procedure SetFileName(AFileName: String);
  public
    constructor Create(const AFileName: String = '');
    destructor  Destroy; override;

    procedure Close; inline;

    function  Prepare(const SQL: String): TSQLiteQuery;
    procedure Finalize(var Query: TSQLiteQuery);

    function Query     (const SQL: String): Boolean;
    function QueryRaw  (const SQL: String): Boolean;
    function QueryTable(const SQL: String): TTable;

    function TableExists(const Name: String): Boolean;

    property FileName: String read fFileName write SetFileName;

    property LogObject: TLog   read fLogObject write fLogObject;
    property LogPrefix: String read fLogPrefix write fLogPrefix;

    procedure Log(Any: array of const);
  end;
{$ENDREGION}

{$REGION 'TTable'}
  TTable = class(TInterface)
  private
    fDatabase: TDatabase;
    fSQL:      String;

    fColumnNames: array of String;
    fTable:       array of array of String;

    function GetRows:    Integer; inline;
    function GetColumns: Integer; inline;
  public
    constructor Create(const ADatabase: TDatabase; const ASQL: String);

    function Empty: Boolean; inline;

    function Column(const Name: String): Integer;
    function Value(const Name: String; const Row: Integer = 0; const Default: String = ''): String;

    function GetColumnName(const Index: Integer): String;

    property Database: TDatabase read fDatabase;
    property SQL:      String    read fSQL;

    property Rows:    Integer read GetRows;
    property Columns: Integer read GetColumns;
  public
    property ColumnName[const Index: Integer]: String read GetColumnName;
  end;
{$ENDREGION}

implementation

{$REGION 'TDatabase'}
procedure TDatabase.SetFileName;
begin
  if fFileName = AFileName then Exit;

  if fDB <> nil then
    sqlite3_close(fDB);

  fDB       := nil;
  fFileName := AFileName;

  if not AFileName.Empty then
  begin
    Log(['Opening ', fFileName, '...']);
    if sqlite3_open16(fFileName.Ptr, fDB) <> SQLITE_OK then
    begin
      fDB       := nil;
      fFileName := '';

      Log(['Failed top open database']);
    end;
  end
  else
    Log(['Closed']);
end;

constructor TDatabase.Create;
begin
  inherited Create;

  fDB := nil;

  if AFileName.Empty then
    fFileName := ''
  else
    FileName := AFileName;

  fLogPrefix := ClassName;
  fLogObject := nil;
end;

destructor TDatabase.Destroy;
begin
  Close;

  inherited;
end;

procedure TDatabase.Close;
begin
  FileName := '';
end;

function TDatabase.Prepare;
var
  NextQuery: PChar;
begin
  if fDB = nil then exit(nil);

  Log([SQL]);

  if sqlite3_prepare16(fDB, SQL.Ptr, -1, Result, NextQuery) <> SQLITE_OK then
    if Result <> nil then
      Finalize(Result);
end;

procedure TDatabase.Finalize;
begin
  if Query = nil then exit;

  sqlite3_finalize(Query);
  Query := nil;
end;

function TDatabase.Query;
var
  SQLTemp: String;
  Command: String;
begin
  SQLTemp := SQL;

  repeat
    Command := SQLTemp.Split(';', True, True);
    if Command.Empty then break;

    if Command.LastChar <> ';' then
      Command := Command + ';';

    if not QueryRaw(Command) then Exit(False);
  until SQLTemp.Empty;

  Result := True;
end;

function TDatabase.QueryRaw;
var
  Query: TSQLiteQuery;
begin
  //TMonitor.Enter(Self);
  try
    Query := Prepare(SQL);
    if Query = nil then exit(False);

    Result := sqlite3_step(Query) = SQLITE_DONE;
  finally
    Finalize(Query);

    //TMonitor.Exit(Self);
  end;
end;

function TDatabase.QueryTable;
begin
  Result := TTable.Create(Self, SQL);
end;

function TDatabase.TableExists;
begin
  with QueryTable('SELECT `sql` FROM `sqlite_master` WHERE `type` = "table" AND `name` = "' + Name + '";') do try
    Result := not Empty;
  finally
    Free;
  end;
end;

procedure TDatabase.Log;
begin
  if fLogObject = nil then Exit;
  fLogObject.Log(Any, fLogPrefix);
end;
{$ENDREGION}

{$REGION 'TTable'}
function TTable.GetRows;
begin
  Result := Length(fTable);
end;

function TTable.GetColumns;
begin
  Result := Length(fColumnNames);
end;

constructor TTable.Create;
var
  Query: TSQLiteQuery;
  i:     Integer;
begin
  inherited Create;

  fDatabase := ADatabase;
  fSQL      := ASQL;

  Query := fDatabase.Prepare(fSQL);
  if Query = nil then exit;

  try
    while sqlite3_step(Query) = SQLITE_ROW do
    begin
      if Length(fTable) = 0 then
      begin
        SetLength(fColumnNames, sqlite3_column_count(Query));

        for i := 0 to length(fColumnNames) - 1 do
          fColumnNames[i] := String(sqlite3_column_name16(Query, i)).Lowercase;
      end;

      SetLength(fTable, Length(fTable) + 1);
      SetLength(fTable[High(fTable)], Length(fColumnNames));

      for i := 0 to Length(fColumnNames) - 1 do
        case sqlite3_column_type(Query, i) of
          SQLITE_INTEGER: fTable[High(fTable)][i] := String.Int(sqlite3_column_int(Query, i));
          SQLITE_TEXT:    fTable[High(fTable)][i] := sqlite3_column_text16(Query, i);
        else
          fTable[High(fTable)][i] := '';
        end;
    end;
  finally
    fDatabase.Finalize(Query);
  end;
end;

function TTable.Empty;
begin
  Result := Length(fTable) = 0;
end;

function TTable.Column;
var
  i: Integer;
  n: String;
begin
  n := Name.Lowercase;

  for i := 0 to length(fColumnNames) - 1 do
    if n = fColumnNames[i] then exit(i);

  Result := -1;
end;

function TTable.Value;
var
  C: Integer;
begin
  if (Row < 0) or (Row >= Length(fTable)) then exit(Default);

  C := Column(Name);
  if C = -1 then exit(Default);

  Result := fTable[Row][C];
end;

function TTable.GetColumnName;
begin
  if (Index < 0) or (Index >= GetColumns) then
    Result := ''
  else
    Result := fColumnNames[Index];
end;
{$ENDREGION}

end.