mysqlcommon.pas

通过Tmysql来访问MSQL Server数据库的应用案例.

unit mySQLCommon;

{
  Unit:    TmySQLCommon - Common Data Structures
  Project: TmySQL - http://www.productivity.org/projects/tmysql
  Author:  Justin P. Yunke <[e-mail]>
  Date:    November 1998 - January 2002
  ** Join the TmySQL mailing list at http://www.elists.org **

  Copyrights/Credits:
    Parts Copyright (c) 2001 Justin P. Yunke <[e-mail]>
    Parts Copyright (c) 2001 Ken Kyler
    T.c.X. DataKonsult AB (mySQL software and component bitmap art)
    Bob Silva <[e-mail]> (_libmysql.pas)

  License:
    Open Source.  You must retain copyrights and distribution information
    listed in these files.

  Description:
    This *.pas file contains some internal code structures for the Tmysql
  project, including TmySQLClientThread, TmySQLClientModify, TmySQLClientQuery,
  and TmySQLClientUtility.
}

interface

uses
  Windows, Messages, Classes, StdCtrls, Forms, mysql;

const
  DEFAULT_DLL_LOCATION   = 'libmySQL.DLL';

  DEBUGFILE_COMMON       = 'mysqlcommon.txt';

{For ChainThreadHandler}
  WM_CHAINTASKHANDLER_COMPLETE = WM_USER + 1;
  WM_CHAINTASKHANDLER_ERROR    = WM_USER + 2;

{Last Modification Information}
  TMYSQLCOMMON_VERSION       = '2.1a';
  TMYSQLCOMMON_LAST_MODIFIED = '01.27.2002';
  TMYSQLCOMMON_LAST_AUTHOR   = 'jpy';

  {TmySQLClientModify constants}
  MAX_SQL_LENGTH       = 65536*2;
  MAX_MEMO_LENGTH      = 32768;

  MAX_LENGTH_HOST      = 255;
  MAX_LENGTH_USER      = 16;
  MAX_LENGTH_PASSWD    = 32;
  MAX_LENGTH_DB        = 64;

  MAX_TASK_NAME_LENGTH = 40;

type
  TRefreshOptions = (
    _REFRESH_GRANT,
    _REFRESH_LOG,
    _REFRESH_TABLES,
    _REFRESH_HOSTS,
    _REFRESH_STATUS,
    _REFRESH_THREADS,
    _REFRESH_SLAVE,
    _REFRESH_MASTER
  );
  TSetRefreshOptions = set of TRefreshOptions;

type
  TClientCapabilities = (
    _CLIENT_LONG_PASSWORD,
    _CLIENT_FOUND_ROWS,
    _CLIENT_LONG_FLAG,
    _CLIENT_CONNECT_WITH_DB,
    _CLIENT_NO_SCHEMA,
    _CLIENT_COMPRESS,
    _CLIENT_ODBC,
    _CLIENT_LOCAL_FILES,
    _CLIENT_IGNORE_SPACE,
    _CLIENT_INTERACTIVE,
    _CLIENT_SSL,
    _CLIENT_IGNORE_SIGPIPE,
    _CLIENT_TRANSACTIONS
  );
  TSetClientCapabilities = set of TClientCapabilities;

//----------------------------------------------------------------------------
//----------------------------------------------------------------------------

type
  TModifyType = (
    MODIFY_INSERT, MODIFY_UPDATE, MODIFY_REPLACE_INTO, MODIFY_DELETE,
    MODIFY_CREATE_TABLE, MODIFY_DROP_TABLE
  );
  TQuoteOptions = (
    QUOTE_STRIP_CR,QUOTE_STRIP_LF,
    QUOTE_DONT_INSERT_SINGLEQUOTES,
    QUOTE_DONT_USE_NULL
  );
  TQuoteOptionsSet = set of TQuoteOptions;

type
  TmySQLClientTaskType = (
    task_mysql_connect,
    task_mysql_close,
    task_mysql_real_connect,

    task_mysql_select_db,
    task_mysql_create_db,
    task_mysql_drop_db,

    task_mysql_query,
    task_mysql_store_result,

    task_mysql_list_db,
    task_mysql_list_tables,
    task_mysql_list_fields,
    task_mysql_list_processes,

    task_mysql_ping,
    task_mysql_shutdown,
    task_mysql_kill,
    task_mysql_options,
    task_mysql_refresh
  );

  TmySQLClientQuery_ExecuteType = (Execute_Stored,Execute_Dynamic);

  TmySQLClientThread_OnComplete = procedure(Sender : TObject; Data : TObject) of object;
  TmySQLClientThread_OnError = procedure(Sender : TObject; Data : TObject; Msg : string) of object;

  TmySQLClientTask_OnComplete = procedure(Sender : TObject) of object;
  TmySQLClientTask_OnError = procedure(Sender : TObject; Msg : string) of object;

  TmySQLClientTask_Status = array[0..4] of string;
  TmySQLClientTask_StatusType = (
    mySQL_Status_NotConnected,
    mySQL_Status_Connecting,
    mySQL_Status_Idle,
    mySQL_Status_Active,
    mySQL_Status_Reconnecting
  );
const
  mySQLClientTask_Status:TmySQLClientTask_Status = ('Not Connected','Connecting','Idle','Active','Reconnecting');

type
  TmySQLClientTask_OnStatus = procedure(Sender : TObject; Status : TmySQLClientTask_StatusType; TaskName : string; TasksLeft : integer) of object;

  TmySQLClientTaskData = class(TObject)
  public
    FName              : string[MAX_TASK_NAME_LENGTH];

    FTaskType          : TmySQLClientTaskType;
    FPrepared          : boolean;

    F_Wild             : pchar;
    F_ListData         : TStringList;
    F_IntegerData      : integer;

    F_table            : pchar;
    F_modifytype       : TModifyType;
    F_condition        : pchar;

    F_query            : pchar;
    F_host, F_user,
    F_passwd, F_db     : pchar;
    F_port             : Cardinal;
    F_unix_socket      : PChar;
    F_clientflag       : Cardinal;

    F_Session          : pmysql;
    F_presults         : pmysql_res;
    F_ret_code         : integer;
    F_pid              : integer;
    F_options          : mysql_option;
    F_refresh_options  : cardinal;
    F_arg              : pchar;

    F_keyfield         : longword;
    F_executetype      : TmySQLClientQuery_ExecuteType;

    FThreadOnComplete  : TmySQLClientThread_OnComplete;
    FThreadOnError     : TmySQLClientThread_OnError;

    FTaskOnComplete    : TmySQLClientTask_OnComplete;
    FTaskOnError       : TmySQLClientTask_OnError;

    constructor Create;
    destructor  Destroy; override;
  end;

  TmySQLClientThread = class(TThread)
  private
    FHandlerWndHandle : HWND;

    FSession : pmysql;

    FTaskList : TList;

    FThreaded : boolean;

    FOnStatus : TmySQLClientTask_OnStatus;
    FOnFinished : TmySQLClientTask_OnComplete;

    procedure   Run(T : TmySQLClientTaskData);
    procedure   Finish(T : TmySQLClientTaskData);

    procedure   PostComplete(T : TmySQLClientTaskData);
    procedure   PostError(T : TmySQLClientTaskData);

    procedure   ResumeNonThread;

    function    GetTaskCount : integer;
  public
    constructor Create(CreateSuspended : boolean);
    destructor  Destroy; override;

    property    Session : pmysql read FSession write FSession;

    property    TaskCount : integer read GetTaskCount;

    property    OnStatus : TmySQLClientTask_OnStatus read FOnStatus write FOnStatus;
    property    OnFinished : TmySQLClientTask_OnComplete read FOnFinished write FOnFinished;

    procedure   Prepare_mysql_real_connect(
      const host, user, passwd, db: pchar;
      port: Cardinal; unix_socket: PChar; clientflag: Cardinal;

      const TaskName : string;

      OnComplete     : TmySQLClientThread_OnComplete;
      OnError        : TmySQLClientThread_OnError;

      OnTaskComplete : TmySQLClientTask_OnComplete;
      OnTaskError    : TmySQLClientTask_OnError
    );

    procedure   Prepare_mysql_close(
      const TaskName : string;

      OnComplete     : TmySQLClientThread_OnComplete;
      OnError        : TmySQLClientThread_OnError;

      OnTaskComplete : TmySQLClientTask_OnComplete;
      OnTaskError    : TmySQLClientTask_OnError
    );

    procedure   Prepare_mysql_db(
      tasktype : TmySQLClientTaskType;

      db : pchar;
      const TaskName : string;

      OnComplete     : TmySQLClientThread_OnComplete;
      OnError        : TmySQLClientThread_OnError;

      OnTaskComplete : TmySQLClientTask_OnComplete;
      OnTaskError    : TmySQLClientTask_OnError
    );

    procedure   Prepare_mysql_query(
      tasktype : TmySQLClientTaskType;

      _query : pchar;

      const TaskName : string;

      OnComplete     : TmySQLClientThread_OnComplete;
      OnError        : TmySQLClientThread_OnError;

      OnTaskComplete : TmySQLClientTask_OnComplete;
      OnTaskError    : TmySQLClientTask_OnError
    );

    procedure   Prepare_mysql_list(
      const TaskType : TmySQLClientTaskType;

      Wild : pchar;

      const TaskName : string;

      OnComplete     : TmySQLClientThread_OnComplete;
      OnError        : TmySQLClientThread_OnError;

      OnTaskComplete : TmySQLClientTask_OnComplete;
      OnTaskError    : TmySQLClientTask_OnError
    );

    procedure   Prepare_mysql_list_fields(
      TableName : pchar;
      Wild : pchar;

      const TaskName : string;

      OnComplete     : TmySQLClientThread_OnComplete;
      OnError        : TmySQLClientThread_OnError;

      OnTaskComplete : TmySQLClientTask_OnComplete;
      OnTaskError    : TmySQLClientTask_OnError
    );

    procedure   Prepare_mysql_list_processes(
      const TaskName : string;

      OnComplete     : TmySQLClientThread_OnComplete;
      OnError        : TmySQLClientThread_OnError;

      OnTaskComplete : TmySQLClientTask_OnComplete;
      OnTaskError    : TmySQLClientTask_OnError
    );

    procedure   Prepare_mysql_ping_shutdown(
      const TaskType : TmySQLClientTaskType;
      const TaskName : string;

      OnComplete     : TmySQLClientThread_OnComplete;
      OnError        : TmySQLClientThread_OnError;

      OnTaskComplete : TmySQLClientTask_OnComplete;
      OnTaskError    : TmySQLClientTask_OnError
    );

    procedure   Prepare_mysql_kill(
      const pid : integer;
      const TaskName : string;

      OnComplete     : TmySQLClientThread_OnComplete;
      OnError        : TmySQLClientThread_OnError;

      OnTaskComplete : TmySQLClientTask_OnComplete;
      OnTaskError    : TmySQLClientTask_OnError
    );

    procedure   Prepare_mysql_options(
      const opt: mysql_option; arg: pchar;
      const TaskName : string;

      OnComplete     : TmySQLClientThread_OnComplete;
      OnError        : TmySQLClientThread_OnError;

      OnTaskComplete : TmySQLClientTask_OnComplete;
      OnTaskError    : TmySQLClientTask_OnError
    );

    procedure   Prepare_mysql_refresh(
      const RefreshOptions: cardinal;
      const TaskName : string;

      OnComplete     : TmySQLClientThread_OnComplete;
      OnError        : TmySQLClientThread_OnError;

      OnTaskComplete : TmySQLClientTask_OnComplete;
      OnTaskError    : TmySQLClientTask_OnError
    );

    procedure   Execute; override;
  end;

  TmySQLClientTaskHandler = class(TObject)
  private
    FWndHandle         : HWND;

    FThreaded          : boolean;
    FThread            : TmySQLClientThread;

    function    GetSession : pmysql;
    procedure   SetSession(Session : pmysql);

    function    GetThreaded : boolean;
    procedure   SetThreaded(B : boolean);
  protected
    procedure   WndProc(var Mesg : TMessage); virtual;
  public
    constructor Create(const BThreaded : boolean); virtual;
    destructor  Destroy; override;

    property    Session : pmysql read GetSession write SetSession;

    property    Threaded : boolean read GetThreaded write SetThreaded;
    property    Thread : TmySQLClientThread read FThread write FThread;

    procedure   StartTasks;

    procedure   DumpTasks;
  end;

type
  TmySQLClientModifyData = class(TObject)
  private
    pData : pchar;
  public
    property Data : pchar read pData write pData;
    destructor Destroy; override;
  end;

  TmySQLClientModify = class(TObject)
  private
    FTaskHandler        : TmySQLClientTaskHandler;

    FData               : TStringList;

    FCurrentTaskData    : TmySQLClientTaskData;

    FDefault_OnComplete : TmySQLClientTask_OnComplete;
    FDefault_OnError    : TmySQLClientTask_OnError;

    procedure   Combine(T : TmySQLClientTaskData);

    procedure   _OnComplete(Sender: TObject; Data: TObject);
    procedure   _OnError(Sender: TObject; Data: TObject; Msg: string);
  protected
  public
    constructor Create;
    destructor  Destroy; override;

    property    TaskHandler : TmySQLClientTaskHandler read FTaskHandler write FTaskHandler;

    property    CurrentTaskData : TmySQLClientTaskData read FCurrentTaskData write FCurrentTaskData;

    property    Default_OnComplete : TmySQLClientTask_OnComplete read FDefault_OnComplete write FDefault_OnComplete;
    property    Default_OnError    : TmySQLClientTask_OnError    read FDefault_OnError    write FDefault_OnError;

    procedure   Add(const Column : string; Data : pchar);
    procedure   AddString(const Column : string; const Data : string);
    procedure   AddInteger(const Column : string; const Data : integer);
    procedure   AddMemo(const Column : string; Memo : TMemo);

    procedure   PrepareCreateTable(Table : pchar; const TaskName : string);
    procedure   PrepareCreateTableTask(
      Table : pchar;
      const TaskName : string;
      OnComplete : TmySQLClientTask_OnComplete;
      OnError    : TmySQLClientTask_OnError
    );

    procedure   PrepareDropTable(Table : pchar; const TaskName : string);
    procedure   PrepareDropTableTask(
      Table : pchar;
      const TaskName : string;
      OnComplete : TmySQLClientTask_OnComplete;
      OnError    : TmySQLClientTask_OnError
    );

    procedure   PrepareInsert(Table : pchar; const TaskName : string);
    procedure   PrepareInsertTask(
      Table : pchar;
      const TaskName : string;
      OnComplete : TmySQLClientTask_OnComplete;
      OnError    : TmySQLClientTask_OnError
    );

    procedure   PrepareUpdate(Table : pchar; Condition : pchar; const TaskName : string);
    procedure   PrepareUpdateTask(
      Table, Condition : pchar;
      const TaskName : string;
      OnComplete : TmySQLClientTask_OnComplete;
      OnError    : TmySQLClientTask_OnError
    );

    procedure   PrepareUpdateString(Table : pchar; const Condition : string; const TaskName : string);
    procedure   PrepareUpdateStringTask(
      Table : pchar; const Condition : string;
      const TaskName : string;
      OnComplete : TmySQLClientTask_OnComplete;
      OnError    : TmySQLClientTask_OnError
    );

    procedure   PrepareReplaceInto(Table : pchar; Condition : pchar; const TaskName : string);
    procedure   PrepareReplaceIntoTask(
      Table, Condition : pchar;
      const TaskName : string;
      OnComplete : TmySQLClientTask_OnComplete;
      OnError    : TmySQLClientTask_OnError
    );

    procedure   PrepareReplaceIntoString(Table : pchar; const Condition : string; const TaskName : string);
    procedure   PrepareReplaceIntoStringTask(
      Table : pchar; const Condition : string;
      const TaskName : string;
      OnComplete : TmySQLClientTask_OnComplete;
      OnError    : TmySQLClientTask_OnError
    );

    procedure   PrepareDelete(Table, Condition : pchar; const TaskName : string);
    procedure   PrepareDeleteTask(
      Table, Condition : pchar;
      const TaskName : string;
      OnComplete : TmySQLClientTask_OnComplete;
      OnError    : TmySQLClientTask_OnError
    );

    procedure   PrepareDeleteString(Table : pchar; const Condition : string; const TaskName : string);
    procedure   PrepareDeleteStringTask(
      Table : pchar; const Condition : string;
      const TaskName : string;
      OnComplete : TmySQLClientTask_OnComplete;
      OnError    : TmySQLClientTask_OnError
    );

    procedure   Reset;
  end;

type
  TmySQLClientQuery = class(TObject)
  private
    FTaskHandler : TmySQLClientTaskHandler;

    FData         : TStringList;
    FFields       : TStringList;
    FFieldsLoaded : boolean;

    FCurrentTaskData : TmySQLClientTaskData;

    FDefault_OnComplete : TmySQLClientTask_OnComplete;
    FDefault_OnError    : TmySQLClientTask_OnError;

    procedure   GetFields;
    function    GetFieldCount : integer;
    function    GetField(const i : longint) : pmysql_field;