{ +-------------------------------------------------------------+ }
{ |                                                             | }
{ |   GM-Software                                               | }
{ |   ===========                                               | }
{ |                                                             | }
{ |   Project: All Projects                                     | }
{ |                                                             | }
{ |   Description: SQLite3 convenience usage.                   | }
{ |                                                             | }
{ |                                                             | }
{ |   Copyright (C) - 2021 - Gerrit Moeller.                    | }
{ |                                                             | }
{ |   Source code dstributed under MIT license.                 | }
{ |                                                             | }
{ |   See: https://www.gm-software.de                           | }
{ |                                                             | }
{ +-------------------------------------------------------------+ }

{$INCLUDE GMCompilerSettings.inc}

unit GMSQLite3;

interface

uses GMStrDef, GMIntf, GMCommon, GMSQLite3Api;


type

  EGMSQLite3DBOption = (dbEnableFKeyConstraints, dbEnableTrigger, dbEnableViews);
  SGMSQLite3DBOptions = set of EGMSQLite3DBOption;

const

  cDfltSQLite3DBOptions = [dbEnableFKeyConstraints, dbEnableTrigger, dbEnableViews];


type

  OGMSQLite3Session = class(TGMRefcountedObj)
   // ..
  end;


  OGMSQLite3DB = class;

  IGMSQLite3DB = interface(IUnknown)
    ['{9C1AFB7A-3B70-41D1-B21C-988C78598BDB}']
    function Obj: OGMSQLite3DB;
  end;

  OGMSQLite3DB = class(TGMRefcountedObj, IGMSQLite3DB)
   protected
    KDB: PSQLite3;

   public
    constructor Create(const AFileName: TGMString;
                       const AOpenFlags: TSQLite3Int = SQLITE_OPEN_READWRITE or SQLITE_OPEN_CREATE;
                       const AOptions: SGMSQLite3DBOptions = cDfltSQLite3DBOptions;
                       const ARefLifeTime: Boolean = True); overload;

    destructor Destroy; override;
    function Obj: OGMSQLite3DB;

    property DB: Psqlite3 read KDB;
  end;


  OGMSQLite3Stmt = class;

  IGMSQLite3Stmt = interface(IUnknown)
    ['{39519D02-11BF-42D6-B336-76894F9C4265}']
    function Obj: OGMSQLite3Stmt;
  end;

  OGMSQLite3Stmt = class(TGMRefcountedObj, IGMSQLite3Stmt)
   protected
    KDB: IGMSQLite3DB;
    KStmt: Psqlite3_stmt;
    KSqlText: TGMString;
    KAffectedRecordCount: Int64;

   public
    constructor Create(const ADB: IGMSQLite3DB; const ASqlText: TGMString; const ARefLifeTime: Boolean = True); overload;
    destructor Destroy; override;
    function Obj: OGMSQLite3Stmt;
    procedure Prepare;
    function NextRow: Boolean;
    function ColumnCount: TSQLite3Int;
    function ColumnDataType(const AColumnIndex: TSQLite3Int): TSQLite3Int;
    function ColumnName(const AColumnIndex: TSQLite3Int): TGMString;
    function ColumnDataTypeName(const AColumnIndex: TSQLite3Int): TGMString;

    function ColumnValueAsInt64(const AColumnIndex: TSQLite3Int): Int64;
    function ColumnValueAsDouble(const AColumnIndex: TSQLite3Int): Double;
    function ColumnValueAsUnicodeString(const AColumnIndex: TSQLite3Int): UnicodeString;
    function ColumnValueAsBlob(const AColumnIndex: TSQLite3Int): RawByteString;

    property DB: IGMSQLite3DB read KDB;
    property Stmt: Psqlite3_stmt read KStmt;
    property SqlText: TGMString read KSqlText;
    property AffectedRecordCount: Int64 read KAffectedRecordCount;
  end;


  XGMSQLiteException = class(EGMException);


  function GMSQLiteCheck(const AErrorCode: TSQLite3Int; const ARoutineName: TGMString; const ACaller: TObject= nil): TSQLite3Int;


implementation

uses GMSql;

resourcestring

  srGeneralErrorFmt = 'SQLite routine "%s" failed with error code (%d)';


{ ------------------------- }
{ ---- Global Routines ---- }
{ ------------------------- }

function SQLiteMsgOfErrorCode(const AErrorCode: TSQLite3Int): TGMString;
var utf8ErrorDesc: AnsiString;
begin
  utf8ErrorDesc := sqlite3_errstr(AErrorCode);
  Result := GMUtf8ToString(utf8ErrorDesc);
end;

function BuildSQLiteErrorMsg(const AErrorCode: TSQLite3Int; const ARoutineName: TGMString; const ACaller: TObject): TGMSTring;
var utf8ErrorDesc: AnsiString; idb: IGMSQLite3DB; istmt: IGMSQLite3Stmt;
begin
  Result := '';
  if AErrorCode = SQLITE_OK then Exit;

  Result := GMStringJoin(GMFormat(srGeneralErrorFmt, [ARoutineName, AErrorCode]), ', ', SQLiteMsgOfErrorCode(AErrorCode));

  GMGetInterface(ACaller, IGMSQLite3DB, idb);
  if (idb = nil) and GMGetInterface(ACaller, IGMSQLite3Stmt, istmt) then idb := istmt.Obj.DB;
  //if idb = nil then Result := '' else
  if idb <> nil then
   begin
    utf8ErrorDesc := sqlite3_errmsg(idb.Obj.DB);
    Result := GMStringJoin(Result, ', ', GMUtf8ToString(utf8ErrorDesc));
   end;
end;

function GMSQLiteCheck(const AErrorCode: TSQLite3Int; const ARoutineName: TGMString; const ACaller: TObject): TSQLite3Int; // ; const ADB: IGMSQLite3DB
begin
  Result := AErrorCode;
  if AErrorCode = SQLITE_OK then Exit;

  raise XGMSQLiteException.ObjError(BuildSQLiteErrorMsg(AErrorCode, ARoutineName, ACaller), ACaller, ARoutineName);
end;

procedure GMSQLiteTrace(const AErrorCode: TSQLite3Int; const ARoutineName: TGMString; const ACaller: TObject = nil); // : TSQLite3Int; // ; const ADB: IGMSQLite3DB
begin
  if AErrorCode <> SQLITE_OK then GMTrace(BuildSQLiteErrorMsg(AErrorCode, ARoutineName, ACaller), tpError);
  //Result := GMSQLiteCheck(AErrorCode, ARoutineName, ACaller);
end;

{ ---------------------- }
{ ---- OGMSQLite3DB ---- }
{ ---------------------- }

constructor OGMSQLite3DB.Create(const AFileName: TGMString; const AOpenFlags: TSQLite3Int; const AOptions: SGMSQLite3DBOptions; const ARefLifeTime: Boolean);
const cBoolArg: Array [Boolean] of TSQLite3Int = (0, 1);
var dbFileNameUtf8: AnsiString; // oldSqlSize: LongInt;// fkin, fkout: TSQLite3Int;
begin
  Create(ARefLifeTime);
  dbFileNameUtf8 := GMStringToUtf8(AFileName);
  //GMSQLiteCheck(sqlite3_open(PAnsiChar(dbFileNameUtf8), KDB), Self, 'sqlite3_open');
  GMSQLiteCheck(sqlite3_open_v2(PAnsiChar(dbFileNameUtf8), KDB, AOpenFlags, nil), 'sqlite3_open_v2', Self);

  //fkin := 1;
  //fkout := 0;
  //GMSQLiteCheck(sqlite3_db_config(KDB, SQLITE_DBCONFIG_ENABLE_FKEY, [fkin, @fkout]), nil, 'sqlite3_db_config');
  //fkout := 0;

  GMSQLiteCheck(sqlite3_db_config(KDB, SQLITE_DBCONFIG_ENABLE_FKEY, [cBoolArg[dbEnableFKeyConstraints in AOptions], nil]), 'sqlite3_db_config(SQLITE_DBCONFIG_ENABLE_FKEY)', self);
  GMSQLiteCheck(sqlite3_db_config(KDB, SQLITE_DBCONFIG_ENABLE_TRIGGER, [cBoolArg[dbEnableTrigger in AOptions], nil]), 'sqlite3_db_config(SQLITE_DBCONFIG_ENABLE_TRIGGER)', self);
  GMSQLiteCheck(sqlite3_db_config(KDB, SQLITE_DBCONFIG_ENABLE_VIEW, [cBoolArg[dbEnableViews in AOptions], nil]), 'sqlite3_db_config(SQLITE_DBCONFIG_ENABLE_VIEW)', self);

  //oldSqlSize := sqlite3_limit(KDB, SQLITE_LIMIT_SQL_LENGTH, 10000000);
end;

destructor OGMSQLite3DB.Destroy;
begin
  if KDB <> nil then begin {$IFDEF DEBUG}GMSQLiteTrace({$ENDIF}sqlite3_close(KDB){$IFDEF DEBUG}, 'sqlite3_close', Self){$ENDIF}; KDB := nil; end;
  inherited Destroy;
end;

function OGMSQLite3DB.Obj: OGMSQLite3DB;
begin
  Result := Self;
end;


{ ------------------------ }
{ ---- OGMSQLite3Stmt ---- }
{ ------------------------ }

constructor OGMSQLite3Stmt.Create(const ADB: IGMSQLite3DB; const ASqlText: TGMString; const ARefLifeTime: Boolean);
begin
  Create(ARefLifeTime);
  KDB := ADB;
  KSqlText := ASqlText;
end;

destructor OGMSQLite3Stmt.Destroy;
begin
  if KStmt <> nil then begin {$IFDEF DEBUG}GMSQLiteTrace({$ENDIF}sqlite3_finalize(KStmt){$IFDEF DEBUG}, 'sqlite3_finalize', Self){$ENDIF}; KStmt := nil; end;
  inherited Destroy;
end;

function OGMSQLite3Stmt.Obj: OGMSQLite3Stmt;
begin
  Result := Self;
end;

procedure OGMSQLite3Stmt.Prepare;
var sqlTextUtf8: AnsiString;
begin
  if DB <> nil then
   begin
    sqlTextUtf8 := GMStringToUtf8(KSqlText);
    GMSQLiteCheck(sqlite3_prepare_v2(Db.Obj.DB, PAnsiChar(sqlTextUtf8), Length(sqlTextUtf8)+1, KStmt, nil), 'sqlite3_prepare_v2', Self);
    //if GMSqlStatmentKind(KSqlText) in [skInsert, skUpdate, skDelete] then KAffectedRecordCount := sqlite3_changes(Db.Obj.DB);
   end;
end;

function OGMSQLite3Stmt.NextRow: Boolean;
var retCode: TSQLite3Int;
begin
  if KStmt = nil then Result := False else
   begin
    retCode := sqlite3_step(KStmt);
    Result := retCode = SQLITE_ROW;
    //if (retCode <> SQLITE_ROW) and (retCode <> SQLITE_DONE) then GMSQLiteCheck(retCode, 'sqlite3_step', Self);
    if not (retCode in [SQLITE_ROW, SQLITE_DONE]) then GMSQLiteCheck(retCode, 'sqlite3_step', Self);

       // and GMSqlStatmentKind(KSqlText) in [skInsert, skUpdate, skDelete]
    if retCode = SQLITE_DONE then KAffectedRecordCount := sqlite3_changes64(Db.Obj.DB);
   end;
end;

function OGMSQLite3Stmt.ColumnCount: TSQLite3Int;
begin
  if KStmt = nil then Result := 0 else Result := sqlite3_column_count(KStmt);
end;

function OGMSQLite3Stmt.ColumnDataType(const AColumnIndex: TSQLite3Int): TSQLite3Int;
begin
  if KStmt = nil then Result := 0 else Result := sqlite3_column_type(KStmt, AColumnIndex);
end;

function OGMSQLite3Stmt.ColumnName(const AColumnIndex: TSQLite3Int): TGMString;
begin
  if KStmt = nil then Result := '' else
   begin
    //Result := PUnicodeChar(sqlite3_column_origin_name16(KStmt, AColumnIndex));
    Result := PUnicodeChar(sqlite3_column_name16(KStmt, AColumnIndex));
    if Length(Result) <= 0 then Result := 'Unknown'+GMIntToStr(AColumnIndex);
   end;
end;

function OGMSQLite3Stmt.ColumnDataTypeName(const AColumnIndex: TSQLite3Int): TGMString;
begin
  if KStmt = nil then Result := '' else Result := PUnicodeChar(sqlite3_column_decltype16(KStmt, AColumnIndex));
end;

function OGMSQLite3Stmt.ColumnValueAsInt64(const AColumnIndex: TSQLite3Int): Int64;
begin
  if KStmt = nil then Result := 0 else Result := sqlite3_column_int64(KStmt, AColumnIndex);
end;

function OGMSQLite3Stmt.ColumnValueAsDouble(const AColumnIndex: TSQLite3Int): Double;
begin
  if KStmt = nil then Result := 0 else Result := sqlite3_column_double(KStmt, AColumnIndex);
end;

function OGMSQLite3Stmt.ColumnValueAsUnicodeString(const AColumnIndex: TSQLite3Int): UnicodeString;
//var pStr: Pointer;
begin
  if KStmt = nil then Result := '' else Result := PUnicodeChar(sqlite3_column_text16(KStmt, AColumnIndex));
   //begin
   // pStr := sqlite3_column_text16(KStmt, AColumnIndex);
   // if pStr = nil then Result := '' else Result := PUnicodeChar(pStr);
   //end;
end;

function OGMSQLite3Stmt.ColumnValueAsBlob(const AColumnIndex: TSQLite3Int): RawByteString;
var sizeInBytes: TSQLite3Int;
begin
  if KStmt = nil then Result := '' else
   begin
    sizeInBytes := sqlite3_column_bytes(KStmt, AColumnIndex);
    SetString(Result, sqlite3_column_blob(KStmt, AColumnIndex), sizeInBytes);
   end;
end;


end.