GMSQLite3 pascal source code


{ +-------------------------------------------------------------+ }
{ |                                                             | }
{ |   GM-Software                                               | }
{ |   ===========                                               | }
{ |                                                             | }
{ |   Project: All Projects                                     | }
{ |                                                             | }
{ |   Description: SQLite3 convenience usage.                   | }
{ |                                                             | }
{ |                                                             | }
{ |   Copyright (C) - 2021 - Gerrit Moeller.                    | }
{ |                                                             | }
{ |   Source code distributed 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];
  cSqLiteExecUninitialized = -1;
  cUnknownSQLiteType = 0;


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;
    KStepRetCode: TSQLite3Int;

   public
    constructor Create(const ADB: IGMSQLite3DB; const ASqlText: TGMString; const ARefLifeTime: Boolean = True); overload;
    destructor Destroy; override;
    function Obj: OGMSQLite3Stmt;
    procedure Prepare;
    procedure Reset;
    function NextRowAndAffectedRowCount: Boolean;
    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 ParameterCount: TSQLite3Int;

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

    function CloumnValueBlobDataPtr(const AColumnIndex: TSQLite3Int): Pointer;
    function CloumnValueBlobDataSize(const AColumnIndex: TSQLite3Int): LongInt;

    function CloumnValueUtf8DataPtr(const AColumnIndex: TSQLite3Int): PAnsiChar;

    function CloumnValueUtf16DataPtr(const AColumnIndex: TSQLite3Int): PWideChar;
    function CloumnValueUtf16DataSize(const AColumnIndex: TSQLite3Int): LongInt;

    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; const ADBFileName: TGMString = ''): TSQLite3Int;


implementation

uses GMSql;

resourcestring

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


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

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

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

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

  GMGetInterface(ACaller, IGMSQLite3DB, idb);
  if (idb = nil) and GMGetInterface(ACaller, IGMSQLite3Stmt, istmt) then idb := istmt.Obj.DB;

  if idb <> nil then
   begin
    utf8ErrorDesc := sqlite3_errmsg(idb.Obj.DB);
    if not GMSameText(GMStrip(utf8ErrorDesc), GMStrip(errMsg)) then Result := GMStringJoin(Result, ', ', utf8ErrorDesc);
   end;

  if (AErrorCode = SQLITE_CANTOPEN) and (Length(ADBFileName) > 0) then
     Result := GMStringJoin(Result, ' ', GMQuote(ADBFileName));
end;

function GMSQLiteCheck(const AErrorCode: TSQLite3Int; const ARoutineName: TGMString; const ACaller: TObject; const ADBFileName: TGMString = ''): TSQLite3Int; // ; const ADB: IGMSQLite3DB
begin
  Result := AErrorCode;
  if AErrorCode <> SQLITE_OK then
    raise XGMSQLiteException.ObjError(BuildSQLiteErrorMsg(AErrorCode, ARoutineName, ACaller, ADBFileName), ACaller, ARoutineName);
end;

{$IFDEF DEBUG}
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;
{$ENDIF}


{ ---------------------- }
{ ---- 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: Utf8String; // oldSqlSize: LongInt;// fkin, fkout: TSQLite3Int;
begin
  Create(ARefLifeTime);
  dbFileNameUtf8 := AFileName;
  //GMSQLiteCheck(sqlite3_open(PAnsiChar(dbFileNameUtf8), KDB), Self, 'sqlite3_open');
  GMSQLiteCheck(sqlite3_open_v2(PAnsiChar(dbFileNameUtf8), KDB, AOpenFlags, nil), 'sqlite3_open_v2', Self, AFileName);

  //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;
  KStepRetCode := cSqLiteExecUninitialized;
  //KAffectedRecordCount := -1; 0
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;

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

procedure OGMSQLite3Stmt.Prepare;
{.$IFNDEF UNICODE}var sqlTextUtf8: Utf8String; {.$ENDIF}
begin
  if DB <> nil then
   begin
    {.$IFDEF UNICODE}
    // Length: The number of bytes in the input string including the nul-terminator!
    //GMSQLiteCheck(sqlite3_prepare16_v2(Db.Obj.DB, PWideChar(KSqlText), (Length(KSqlText)+1) * SizeOf(WideChar), KStmt, nil), 'sqlite3_prepare16_v2', Self);
    {.$ELSE}
    sqlTextUtf8 := KSqlText; // GMStringToUtf8(KSqlText);
    // Length: The number of bytes in the input string INCLUDING the nul-terminator!
    GMSQLiteCheck(sqlite3_prepare_v2(Db.Obj.DB, PAnsiChar(sqlTextUtf8), Length(sqlTextUtf8)+1, KStmt, nil), 'sqlite3_prepare_v2', Self);
    {.$ENDIF}
    //if GMSqlStatmentKind(KSqlText) in [skInsert, skUpdate, skDelete] then KAffectedRecordCount := sqlite3_changes(Db.Obj.DB);
   end;
end;

procedure OGMSQLite3Stmt.Reset;
begin
  if (KStmt <> nil) and (KStepRetCode in [SQLITE_ROW, SQLITE_DONE]) then
   begin
    GMSQLiteCheck(sqlite3_reset(KStmt), 'sqlite3_reset', Self);
    KStepRetCode := cSqLiteExecUninitialized;
	 end;
end;

function OGMSQLite3Stmt.NextRow: Boolean;
begin
  if (KStmt = nil) or (KStepRetCode = SQLITE_DONE) then Result := False else
   begin
    KStepRetCode := sqlite3_step(KStmt);
    Result := KStepRetCode = SQLITE_ROW;
    if not (KStepRetCode in [SQLITE_ROW, SQLITE_DONE]) then GMSQLiteCheck(KStepRetCode, 'sqlite3_step', Self);
   end;
end;

function OGMSQLite3Stmt.NextRowAndAffectedRowCount: Boolean;
begin
  Result := NextRow;
  KAffectedRecordCount := sqlite3_changes64(Db.Obj.DB);
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
   {$IFDEF UNICODE}
    Result := PUnicodeChar(sqlite3_column_decltype16(KStmt, AColumnIndex));
   {$ELSE}
    Result := PAnsiChar(sqlite3_column_decltype(KStmt, AColumnIndex));
   {$ENDIF}
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;
begin
  if KStmt = nil then Result := '' else
    //Result := PUnicodeChar(sqlite3_column_text16(KStmt, AColumnIndex));
     SetString(Result, sqlite3_column_text16(KStmt, AColumnIndex), sqlite3_column_bytes16(KStmt, AColumnIndex) div SizeOf(WideChar));
end;

function OGMSQLite3Stmt.ColumnValueAsUtf8String(const AColumnIndex: TSQLite3Int): Utf8String;
//var utf8Str: Utf8String;
begin
  if KStmt = nil then Result := '' else
     SetString(Result, sqlite3_column_text(KStmt, AColumnIndex), sqlite3_column_bytes(KStmt, AColumnIndex));

  //if KStmt = nil then Result := '' else
  // begin
  //  SetString(utf8Str, sqlite3_column_text(KStmt, AColumnIndex), sqlite3_column_bytes(KStmt, AColumnIndex));
  //  Result := utf8Str;
  // 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;

function OGMSQLite3Stmt.CloumnValueBlobDataSize(const AColumnIndex: TSQLite3Int): LongInt;
begin
  Result := sqlite3_column_bytes(KStmt, AColumnIndex);
end;

function OGMSQLite3Stmt.CloumnValueBlobDataPtr(const AColumnIndex: TSQLite3Int): Pointer;
begin
  Result := sqlite3_column_blob(KStmt, AColumnIndex);
end;

function OGMSQLite3Stmt.CloumnValueUtf8DataPtr(const AColumnIndex: TSQLite3Int): PAnsiChar;
begin
  Result := sqlite3_column_text(KStmt, AColumnIndex);
end;

function OGMSQLite3Stmt.CloumnValueUtf16DataSize(const AColumnIndex: TSQLite3Int): LongInt;
begin
  Result := sqlite3_column_bytes16(KStmt, AColumnIndex);
end;

function OGMSQLite3Stmt.CloumnValueUtf16DataPtr(const AColumnIndex: TSQLite3Int): PWideChar;
begin
  Result := sqlite3_column_text16(KStmt, AColumnIndex);
end;



end.