asgsqlite3.pas

连接sqlite数据库控件

                        - changed all showmessages to raise event types
                        - added readonly property
              feb 21, 2005 Release 2005.02.A Albert Drent (c) 2003 .. 2005 Aducom Software
                        - some bugfixes, code optimization
                        - first implementation of IProvider support (thanks to
                          Rocco Barbaresco)
                        - changed default SDB extension to SQB
                        - support for multi sql statements in one string
                        - added master-detail functionality on insert of
                          new child record (key-data is copied)
                        - added compatibility with SQLite3.dll version 3.1.2
              feb 28, 2005 Release 2005.02.B Albert Drent (c) 2003 .. 2005 Aducom Software
                        - bugfix for filter on master-detail. Setting filtered will
                          not automatical open table any more. Causes error on closed
                          tables in master-detail otherwise.
              mar 7, 2005 Release 2005.03.A Albert Drent (c) 2003 .. 2005 Aducom Software
                        - bugfix for memofields causing crlf to be handled wrongly
                        - added TableExists function ('// DI  Ralf http://www.yunqa.de/delphi/)
                        - some code optimizations ('// DI Ralf http://www.yunqa.de/delphi/)
                        - solved decimalpoint bug ('// DI Ralf http://www.yunqa.de/delphi/)
              mar 10, 2005 Release 2005.03.B Albert Drent (c) 2003 .. 2005 Aducom Software
                        - more code optimizations
                        - removed Forms from uses list to decrease codesize of console app's
                        - changed cursorstyle handling
                        - changed exception handling
              mar 29, 2005 Release 2005.03.C Albert Drent (c) 2003 .. 2005 Aducom Software
                        - removed default pragma's for tables
                        - implemented another solution for tableheader duplicates (mirko)
                        - implemented user version
                        - solved minor bug in GetTableInfo
              april 5, 2005 Release 2005.04.A Albert Drent (c) 2003 .. 2005 Aducom Software
                        - fixed bug conceirning in-memory database (Bert Verhees)
                        - fixed precompiler settings for Delhpi 7 (Dak)
              mai 31, 2005 Release 2005.05.A Albert Drent (c) 2003 .. 2005 Aducom Software
                        - UTF8 support (thanks to Bilgehan KUYUCU)
                        - Statical linking (thanks to Gianpaolo Avallone (GPA)
                        - Some bugfixes and enhancements by Albert, Gianpaolo and others.
              august 1, 2005 Release 2005.08.A
                        - UTF8 support to be set by property, solving a 'locale' problem.
                        - Bugfix for null strings
                        - Fix for static linking
                        - Solved bug in updatesql, fieldlist is now setup correctly
                        - solved bug in updatesql, inserting null values and constants.
              august 2, 2005 Release 2005.08.B
                        - first steps towards support utf16 (by Kevin Zhang)
                        - implemented another locate routine (by jbannon)
                        - changed treatment of datatype text. It is considered 'longtext' now.
                          added 'shorttext' datatype which replaces the original 'text' datatype.
                          The datatype text is now compatible with sqlite3, mysql and perhaps
                          others.
              sept 1, 2005 Release 2005.09.A
                        - code optimizations by Kevin Lu, Aducom
              sept 1, 2005 Release 2005.09.B
                        - candidate fix for decimal point problem by Jordi March
              sept 20, 2005 Release 2005.09.C
                        - implementation of unidirectional dataset. This improves
                          performance on large resultsets to the optimum. However
                          the dataset is readonly by default, using inserts/deletes and
                          updates will result in a 'database table is locked' error.
              sept 21, 2005 Release 2005.09.D
                        - bugfix for decimal point routine by Kevin Lu
              sept 29, 2005 Release 2005.09.E
                        - bugfix for unidirectional dataset.
                        - bugfix for split of prepare and execute. Data was not updated
                          correctly
              nov 1, 2005 Release 2005.11.A
                        - Code improvements
                        - First (alpha) implementation of Lookup field (By John Lito)
              dec 14, 2005 Release 2005.12.A
                        - Check for null values
              jan 5, 2006 Release 2006.01.A
                        - fixed unidirectional bug (I)
              jan 9, 2006 Release 2006.01.B
                        - fixed unidirectional bug (II)
                        - automatically will close table if tablename property is changed
                          while a table is still open.
                        - automatically close query if sql property is changed while a table
                          is still open.
                        - fixes for compilation under D4
                        - changes for installation under Delphi 2005 and 2006
                        - changes of copyright notice, the components are now under
                          open-source bsd licence.

*_* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * }


interface

uses
  DB,
  DBCommon,
  Dialogs,
  Classes,
  Windows,
  SysUtils,
{$IFDEF ASQLITE_D6PLUS}
  Variants,
{$ENDIF}
  ASGRout3;
const
  SQLiteVersion     = 'ASGSQLite V2006.01.B stable';

  MaxBuf            = 30000;            // max stringbuffer for record (length) (excluding blob's)
  SQLITE_OK         = 0;                // Successful result */
  SQLITE_ERROR      = 1;                // SQL error or missing database */
  SQLITE_INTERNAL   = 2;                // An internal logic error in SQLite */
  SQLITE_PERM       = 3;                // Access permission denied */
  SQLITE_ABORT      = 4;                // Callback routine requested an abort */
  SQLITE_BUSY       = 5;                // The database file is locked */
  SQLITE_LOCKED     = 6;                // A table in the database is locked */
  SQLITE_NOMEM      = 7;                // A malloc() failed */
  SQLITE_READONLY   = 8;                // Attempt to write a readonly database */
  SQLITE_INTERRUPT  = 9;                // Operation terminated by sqlite_interrupt() */
  SQLITE_IOERR      = 10;               // Some kind of disk I/O error occurred */
  SQLITE_CORRUPT    = 11;               // The database disk image is malformed */
  SQLITE_NOTFOUND   = 12;               // (Internal Only) Table or record not found */
  SQLITE_FULL       = 13;               // Insertion failed because database is full */
  SQLITE_CANTOPEN   = 14;               // Unable to open the database file */
  SQLITE_PROTOCOL   = 15;               // Database lock protocol error */
  SQLITE_EMPTY      = 16;               // (Internal Only) Database table is empty */
  SQLITE_SCHEMA     = 17;               // The database schema changed */
  SQLITE_TOOBIG     = 18;               // Too much data for one row of a table */
  SQLITE_CONSTRAINT = 19;               // Abort due to contraint violation */
  SQLITE_MISMATCH   = 20;               // Data type mismatch */
  SQLITE_MISUSE     = 21;               // Library used incorrectly */
  SQLITE_NOLFS      = 22;               // Uses OS features not supported on host */
  SQLITE_AUTH       = 23;               // Authorization denied */
  SQLITE_ROW        = 100;              // sqlite_step() has another row ready */
  SQLITE_DONE       = 101;              // sqlite_step() has finished executing */

  SQLITE_CREATE_INDEX = 1;              // Index Name      Table Name      */
  SQLITE_CREATE_TABLE = 2;              // Table Name      NULL            */
  SQLITE_CREATE_TEMP_INDEX = 3;         // Index Name      Table Name      */
  SQLITE_CREATE_TEMP_TABLE = 4;         // Table Name      NULL            */
  SQLITE_CREATE_TEMP_TRIGGER = 5;       // Trigger Name    Table Name      */
  SQLITE_CREATE_TEMP_VIEW = 6;          // View Name       NULL            */
  SQLITE_CREATE_TRIGGER = 7;            // Trigger Name    Table Name      */
  SQLITE_CREATE_VIEW = 8;               // View Name       NULL            */
  SQLITE_DELETE     = 9;                // Table Name      NULL            */
  SQLITE_DROP_INDEX = 10;               // Index Name      Table Name      */
  SQLITE_DROP_TABLE = 11;               // Table Name      NULL            */
  SQLITE_DROP_TEMP_INDEX = 12;          // Index Name      Table Name      */
  SQLITE_DROP_TEMP_TABLE = 13;          // Table Name      NULL            */
  SQLITE_DROP_TEMP_TRIGGER = 14;        // Trigger Name    Table Name      */
  SQLITE_DROP_TEMP_VIEW = 15;           // View Name       NULL            */
  SQLITE_DROP_TRIGGER = 16;             // Trigger Name    Table Name      */
  SQLITE_DROP_VIEW  = 17;               // View Name       NULL            */
  SQLITE_INSERT     = 18;               // Table Name      NULL            */
  SQLITE_PRAGMA     = 19;               // Pragma Name     1st arg or NULL */
  SQLITE_READ       = 20;               // Table Name      Column Name     */
  SQLITE_SELECT     = 21;               // NULL            NULL            */
  SQLITE_TRANSACTION = 22;              // NULL            NULL            */
  SQLITE_UPDATE     = 23;               // Table Name      Column Name     */
  SQLITE_ATTACH     = 24;               // Filename        NULL            */
  SQLITE_DETACH     = 25;               // Database Name   NULL            */

  SQLITE_DENY       = 1;                // Abort the SQL statement with an error */
  SQLITE_IGNORE     = 2;                // Don't allow access, but don't generate an error */

  Crlf              : string = #13#10;
  Q                 = '''';

type
  pInteger = ^integer;
  pPointer = ^Pointer;
  pSmallInt = ^smallint;
  pFloat = ^extended;
  pBoolean = ^boolean;

  TConvertBuffer = array[1..255] of char;


  TSQLite3_Callback = function(UserData: Pointer; ColumnCount: Integer; ColumnValues, ColumnNames: PPointer): Integer; cdecl;
//  TSQLiteExecCallback = function(Sender: TObject; Columns: integer; ColumnValues: Pointer; ColumnNames: Pointer): integer of object; cdecl;
  TSQLiteBusyCallback = function(Sender: TObject; ObjectName: PAnsiChar; BusyCount: integer): integer of object; cdecl;
  TOnData = procedure(Sender: TObject; Columns: integer; ColumnNames, ColumnValues: string) of object;
  TOnBusy = procedure(Sender: TObject; ObjectName: string; BusyCount: integer; var Cancel: boolean) of object;
  TOnQueryComplete = procedure(Sender: TObject) of object;
  TASQLite3NotifyEvent = procedure(Sender: TObject) of object;

  // structure for holding field information. It is used by GetTableInfo

  TASQLite3Field = class
  public
    FieldNumber: integer;
    FieldName: string;
    FieldType: string;
    FieldNN: integer;                   // 1 if notnull
    FieldDefault: string;
    FieldPK: integer;                   // 1 if primary key
  end;

  // object to 'play' with SQLite's default settings

  TASQLite3Pragma = class(TComponent)
  private
    FTempCacheSize: integer;
    FDefaultCacheSize: integer;
    FDefaultSynchronous: string;
    FDefaultTempStore: string;
    FTempStore: string;
    FSynchronous: string;
  protected
    function GetTempCacheSize: string;
    function GetDefaultCacheSize: string;
    function GetDefaultSynchronous: string;
    function GetDefaultTempStore: string;
    function GetTempStore: string;
    function GetSynchronous: string;
  published
    { Published declarations }
    property TempCacheSize: integer read FTempCacheSize write FTempCacheSize;
    property DefaultCacheSize: integer read FDefaultCacheSize write FDefaultCacheSize;
    property DefaultSynchronous: string read FDefaultSynchronous
      write FDefaultSynchronous;
    property DefaultTempStore: string read FDefaultTempStore write FDefaultTempStore;
    property TempStore: string read FTempStore write FTempStore;
    property Synchronous: string read FSynchronous write FSynchronous;
  end;

  // component to log messages
  // it's for debugging purpose and may be obsolete due
  // to the event implementation. not sure yet...

  TASQLite3Log = class(TComponent)
  private
    FLogFile: string;
    FLogDebugOut: boolean;
    FAppend: boolean;
    FLogSQL: boolean;
    FLogInt: boolean;
  protected
  public
    procedure Display(Msg: string);
  published
    { Published declarations }
    property LogFile: string read FLogFile write FLogFile;
    property LogDebugOut: boolean read FLogDebugOut write FLogDebugOut; // 20040225
    property Append: boolean read FAppend write FAppend;
    property LogSQL: boolean read FLogSQL write FLogSQL;
    property LogInternals: boolean read FLogInt write FLogInt;
  end;

// This component can be used to store sql outside the pascal source.
// It is useful for automatically creating tables on open of a temporary database
// (i.e. in-memory database)

  TASQLite3InlineSQL = class(TComponent)
  private
    FSQL: TStrings;
    procedure SetSQL(const Value: TStrings);
    function GetSQL: TStrings;
  public
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;
  published
    property SQL: TStrings read GetSQL write SetSQL;
  end;

  { Basic Database component }

  TASQLite3DB = class(TComponent)
  private
    { Private declarations }
    FAfterConnect: TASQLite3NotifyEvent;
    FBeforeConnect: TASQLite3NotifyEvent;
    FAfterDisconnect: TASQLite3NotifyEvent;
    FBeforeDisconnect: TASQLite3NotifyEvent;
    FDatabases: string;
    function FGetDefaultExt: string;
    function FGetDriverDLL: string;
  protected
    { Protected declarations }
    FInlineSQL: TASQLite3InlineSQL;