Unit SQLite3IniFileDb;
{$I SQLite.inc}
Interface

Uses
  SysUtils, Classes, SQLite3Lib, Sqlite3Types;

Const
  DEFAULT_LIST_ITEM_NAME = 'Item';

Type

  ESQLite3IniFileDb = Class(Exception);

  TSQLite3IniFileDb = Class
  Private
    FDb: TSQLiteDB;
    FPassWord, FOldPassWord: WideString;
    FStmtDeleteKey: TSQLiteStmt;
    FStmtEraseSection: TSQLiteStmt;
    FStmtInsertKey: TSQLiteStmt;
    FStmtInsertSection: TSQLiteStmt;
    FStmtReadSection: TSQLiteStmt;
    FStmtReadSections: TSQLiteStmt;
    FStmtReadSectionValues: TSQLiteStmt;
    FStmtSelectKeyID: TSQLiteStmt;
    FStmtSelectValue: TSQLiteStmt;
    FStmtSelectSectionID: TSQLiteStmt;
    FStmtUpdateKey: TSQLiteStmt;
    FUpdateCount: Cardinal;
  Protected
    Function Check(Const AErrorCode: Integer): Integer;
    Procedure Execute(Const ASql8: Utf8String);
    Function Prepare(Const ASql8: Utf8String): TSQLiteStmt;
    Procedure RaiseException(Const AErrorCode: Integer);
  Public

    Constructor Create(
      Const AFileName: WideString; Const APassWord: WideString = '');

    Destructor Destroy; Override;

    Procedure BeginUpdate;

    Procedure Clear;

    Procedure DeleteKey(
      Const ASection, AKey: WideString);

    Procedure EndUpdate;

    Procedure EraseSection(
      Const ASection: WideString);

    Function InsertSection(
      Const ASection: WideString): Int64;

    Function ReadInteger(
      Const ASection, AKey: WideString;
      Const ADefault: Integer = 0): Integer;

    Procedure ReadSection(
      Const ASection: WideString; AStrings: TStrings);

    Procedure ReadSections(
      Const AStrings: TStrings);

    Procedure ReadSectionValues(
      Const ASection: WideString; AStrings: TStrings);

    Function ReadString(
      Const ASection, AKey: WideString;
      Const ADefault: WideString = ''): WideString;

    Function ReadStringList(
      Const ASection: WideString;
      Const AStringList: TStrings;
      Const AClearFirst: Boolean = True;
      Const AItemName: WideString = DEFAULT_LIST_ITEM_NAME): Integer;

    Function SectionExists(
      Const ASection: WideString): Boolean;

    Function SelectKeyID(
      Const ASectionID: Int64;
      Const AKey: WideString): Int64;

    Function SelectSectionID(
      Const ASection: WideString): Int64;

    Function KeyExists(
      Const ASection, AKey: WideString): Boolean;

    Procedure WriteInteger(
      Const ASection, AKey: WideString;
      Const AValue: Integer);

    Procedure WriteString(
      Const ASection, AKey, AValue: WideString);

    Procedure WriteStringList(
      Const ASection: WideString;
      Const AStringList: TStrings;
      Const AItemName: WideString = DEFAULT_LIST_ITEM_NAME);

    Property UpdateCount: Cardinal Read FUpdateCount;
  Published
    Property PassWord: WideString Read FPassWord Write FPassWord;
  End;

Implementation

Const
  LIST_COUNT_SUFFIX = 'Count';
  Sql_Insert_Key = 'INSERT INTO Keys(SectionID,KeyName,KeyValue)VALUES(?,?,?);';
  Sql_Select_Value = 'SELECT KeyValue FROM Keys NATURAL JOIN Sections WHERE SectionName=? AND KeyName=?;';
  Sql_Update_Key = 'UPDATE Keys SET KeyValue=? WHERE KeyID=?;';

Resourcestring
  SSQLite3IniFileDbError = 'SQLite3IniFileDb Error %d: %s';

Constructor TSQLite3IniFileDb.Create(Const AFileName: WideString; Const APassWord: WideString = '');
Begin
  SQLite3LoadLibrary;
  If Not SQLite3_DllHandle = 0 Then
    ESQLite3IniFileDb.Create('Load SQLite3 error.');

  Check(SQLite3_Open(PAnsiChar(UTF8Encode(AFileName)), FDb));

  If APassWord <> '' Then
  Begin
    FPassWord := APassWord;
    FOldPassWord := FPassWord;
    {$IFDEF UseSQLiteCrypt}
    If Assigned(SQLite3_Key) Then
      SQLite3_Key(FDb, PAnsiChar(UTF8Encode(APassWord)), Length(APassWord) * 2);
    {$ENDIF}
  End;

  Execute('PRAGMA synchronous=off;');
  Execute('PRAGMA legacy_file_format=off;');

  Execute(
    'CREATE TABLE IF NOT EXISTS Sections(' +
    'SectionID INTEGER PRIMARY KEY,' +
    'SectionName TEXT UNIQUE COLLATE NoCase);');

  Execute(
    'CREATE TABLE IF NOT EXISTS Keys(' +
    'KeyID INTEGER PRIMARY KEY,' +
    'SectionID INTEGER,' +
    'KeyName TEXT COLLATE NoCase,' +
    'KeyValue TEXT,' +
    'UNIQUE(SectionID,KeyName));');

  Execute(
    'CREATE INDEX IF NOT EXISTS iKeySectionID ON Keys (SectionID);');

  Execute(
    'CREATE TRIGGER IF NOT EXISTS sd AFTER DELETE ON Sections ' +
    'BEGIN ' +
    'DELETE FROM Keys WHERE SectionID=old.SectionID;' +
    'END;');

  Execute(
    'CREATE TRIGGER IF NOT EXISTS su AFTER UPDATE ON Sections ' +
    'BEGIN ' +
    'UPDATE Keys SET SectionID=new.SectionID WHERE SectionID=old.SectionID;' +
    'END;');

  Execute(
    'CREATE TRIGGER IF NOT EXISTS k AFTER DELETE ON Keys ' +
    'WHEN NOT EXISTS (SELECT 0 FROM Keys WHERE SectionID=old.SectionID)' +
    'BEGIN ' +
    'DELETE FROM Sections WHERE SectionID = OLD.SectionID;' +
    'END;');
End;

Destructor TSQLite3IniFileDb.Destroy;
Begin
  SQLite3_Finalize(FStmtDeleteKey);
  SQLite3_Finalize(FStmtEraseSection);
  SQLite3_Finalize(FStmtInsertKey);
  SQLite3_Finalize(FStmtInsertSection);
  SQLite3_Finalize(FStmtReadSection);
  SQLite3_Finalize(FStmtReadSections);
  SQLite3_Finalize(FStmtReadSectionValues);
  SQLite3_Finalize(FStmtSelectValue);
  SQLite3_Finalize(FStmtSelectKeyID);
  SQLite3_Finalize(FStmtSelectSectionID);
  SQLite3_Finalize(FStmtUpdateKey);

  If WideCompareStr(FPassWord, FOldPassWord) <> 0 Then
  Begin
    {$IFDEF UseSQLiteCrypt}
    If Assigned(SqLite3_Rekey) Then
      SqLite3_Rekey(FDb, PAnsiChar(UTF8Encode(FPassWord)), Length(FPassWord) * 2);
    {$ENDIF}
  End;

  Check(SQLite3_Close(FDb));
  SQLite3FreeLibrary;
  Inherited;
End;

Procedure TSQLite3IniFileDb.BeginUpdate;
Begin
  If FUpdateCount = 0 Then
    Execute('BEGIN TRANSACTION;');
  Inc(FUpdateCount);
End;

Function TSQLite3IniFileDb.Check(Const AErrorCode: Integer): Integer;
Begin
  Result := AErrorCode;
  Case Result And $FF Of
    SQLITE_OK, SQLITE_ROW, SQLITE_DONE:
      ;
  Else
    RaiseException(AErrorCode);
  End;
End;

Procedure TSQLite3IniFileDb.Clear;
Begin
  Execute('DELETE FROM Sections;');
  Execute('DELETE FROM Keys;');
End;

Procedure TSQLite3IniFileDb.DeleteKey(Const ASection, AKey: WideString);
Const
  SQL = 'DELETE FROM Keys WHERE SectionID=(SELECT SectionID FROM Sections WHERE SectionName=?)AND KeyName =?';
Begin
  If Not Assigned(FStmtDeleteKey) Then
    FStmtDeleteKey := Prepare(SQL);
  Try
    Check(SQLite3_Bind_Str16(FStmtDeleteKey, 1, ASection));
    Check(SQLite3_Bind_Str16(FStmtDeleteKey, 2, AKey));
    Check(Sqlite3_Step(FStmtDeleteKey));
  Finally
    SQLite3_Reset(FStmtDeleteKey);
  End;
End;

Procedure TSQLite3IniFileDb.EndUpdate;
Begin
  If FUpdateCount > 0 Then
  Begin
    Dec(FUpdateCount);
    If FUpdateCount = 0 Then
      Execute('COMMIT TRANSACTION;');
  End;
End;

Procedure TSQLite3IniFileDb.EraseSection(Const ASection: WideString);
Const
  SQL = 'DELETE FROM Sections WHERE SectionName=?;';
Begin
  If Not Assigned(FStmtEraseSection) Then
    FStmtEraseSection := Prepare(SQL);
  Try
    Check(SQLite3_Bind_Str16(FStmtEraseSection, 1, ASection));
    Check(Sqlite3_Step(FStmtEraseSection));
  Finally
    SQLite3_Reset(FStmtEraseSection);
  End;
End;

Procedure TSQLite3IniFileDb.Execute(Const ASql8: Utf8String);
Var
  Stmt: TSQLiteStmt;
Begin
  Stmt := Prepare(ASql8);
  Try
    Check(Sqlite3_Step(Stmt));
  Finally
    SQLite3_Finalize(Stmt);
  End;
End;

Function TSQLite3IniFileDb.InsertSection(Const ASection: WideString): Int64;
Const
  Sql_Insert_Section = 'INSERT INTO Sections(SectionName)VALUES(?);';