myldbconverts.pas

一个本地database引擎,支持中文T_Sql查询,兼容DELPHI标准数据库控件

unit MYLDBConverts;

interface

{$I MYLDBVer.inc}

uses SysUtils, {Controls,} DB, Windows,
     MYLDBTypes,
     MYLDBStrUtils,
     MYLDBExcept,
     MYLDBConst;

const
  WildCardMultipleChar = '%';
  WildCardSingleChar = '_';

type
  TSQLFieldType = packed record
    SqlName: String;
    AdvancedFieldType: TMYLDBAdvancedFieldType;
    BaseFieldType: TMYLDBBaseFieldType;
    FieldType: TFieldType;
  end;
const
  SQLFieldTypes: array[1..62] of TSQLFieldType = (

    (SqlName: 'CHAR';           AdvancedFieldType: aftChar;           BaseFieldType: bftChar;         FieldType: ftFixedChar),
    (SqlName: 'FIXEDCHAR';      AdvancedFieldType: aftChar;           BaseFieldType: bftChar;         FieldType: ftFixedChar),
    (SqlName: 'STRING';         AdvancedFieldType: aftString;         BaseFieldType: bftVarchar;      FieldType: ftString),
    (SqlName: 'VARCHAR';        AdvancedFieldType: aftString;         BaseFieldType: bftVarchar;      FieldType: ftString),
    (SqlName: 'VARCHAR2';       AdvancedFieldType: aftString;         BaseFieldType: bftVarchar;      FieldType: ftString),
    (SqlName: 'WIDECHAR';       AdvancedFieldType: aftWideChar;       BaseFieldType: bftWideChar;     FieldType: ftWideString),
    (SqlName: 'FIXEDWIDECHAR';  AdvancedFieldType: aftWideChar;       BaseFieldType: bftWideChar;     FieldType: ftWideString),
    (SqlName: 'WIDESTRING';     AdvancedFieldType: aftWideString;     BaseFieldType: bftWideVarchar;  FieldType: ftWideString),
    (SqlName: 'WIDEVARCHAR';    AdvancedFieldType: aftWideString;     BaseFieldType: bftWideVarchar;  FieldType: ftWideString),

    (SqlName: 'SMALLINT';       AdvancedFieldType: aftSmallint;       BaseFieldType: bftSignedInt16;  FieldType: ftSmallint),
    (SqlName: 'SIGNEDINT16';    AdvancedFieldType: aftSmallint;       BaseFieldType: bftSignedInt16;  FieldType: ftSmallint),
    (SqlName: 'SHORTINT';       AdvancedFieldType: aftShortint;       BaseFieldType: bftSignedInt8;   FieldType: ftSmallint),
    (SqlName: 'SIGNEDINT8';     AdvancedFieldType: aftShortint;       BaseFieldType: bftSignedInt8;   FieldType: ftSmallint),
    (SqlName: 'INTEGER';        AdvancedFieldType: aftInteger;        BaseFieldType: bftSignedInt32;  FieldType: ftInteger),
    (SqlName: 'SIGNEDINT32';    AdvancedFieldType: aftInteger;        BaseFieldType: bftSignedInt32;  FieldType: ftInteger),
    (SqlName: 'LARGEINT';       AdvancedFieldType: aftLargeint;       BaseFieldType: bftSignedInt64;  FieldType: ftLargeint),
    (SqlName: 'INT64';          AdvancedFieldType: aftLargeint;       BaseFieldType: bftSignedInt64;  FieldType: ftLargeint),
    (SqlName: 'SIGNEDINT64';    AdvancedFieldType: aftLargeint;       BaseFieldType: bftSignedInt64;  FieldType: ftLargeint),
    (SqlName: 'WORD';           AdvancedFieldType: aftWord;           BaseFieldType: bftUnsignedInt16;FieldType: ftWord),
    (SqlName: 'UNSIGNEDINT16';  AdvancedFieldType: aftWord;           BaseFieldType: bftUnsignedInt16;FieldType: ftWord),
    (SqlName: 'BYTE';           AdvancedFieldType: aftByte;           BaseFieldType: bftUnsignedInt8; FieldType: ftWord),
    (SqlName: 'UNSIGNEDINT8';   AdvancedFieldType: aftByte;           BaseFieldType: bftUnsignedInt8; FieldType: ftWord),
    (SqlName: 'CARDINAL';       AdvancedFieldType: aftCardinal;       BaseFieldType: bftUnsignedInt32;FieldType: ftLargeint),
    (SqlName: 'UNSIGNEDINT32';  AdvancedFieldType: aftCardinal;       BaseFieldType: bftUnsignedInt32;FieldType: ftLargeint),

    (SqlName: 'AUTOINC';        AdvancedFieldType: aftAutoInc;        BaseFieldType: bftSignedInt32;  FieldType: ftAutoInc),
    (SqlName: 'AUTOINCSHORTINT';AdvancedFieldType: aftAutoIncShortint;BaseFieldType: bftSignedInt8;   FieldType: ftAutoInc),
    (SqlName: 'AUTOINCSMALLINT';AdvancedFieldType: aftAutoIncSmallint;BaseFieldType: bftSignedInt16;  FieldType: ftAutoInc),
    (SqlName: 'AUTOINCINTEGER'; AdvancedFieldType: aftAutoIncInteger; BaseFieldType: bftSignedInt32;  FieldType: ftAutoInc),
    (SqlName: 'AUTOINCLARGEINT';AdvancedFieldType: aftAutoIncLargeint;BaseFieldType: bftSignedInt64;  FieldType: ftLargeint),
    (SqlName: 'AUTOINCBYTE';    AdvancedFieldType: aftAutoIncByte;    BaseFieldType: bftUnsignedInt8; FieldType: ftAutoInc),
    (SqlName: 'AUTOINCWORD';    AdvancedFieldType: aftAutoIncWord;    BaseFieldType: bftUnsignedInt16;FieldType: ftAutoInc),
    (SqlName: 'AUTOINCCARDINAL';AdvancedFieldType: aftAutoIncCardinal;BaseFieldType: bftUnsignedInt32;FieldType: ftAutoInc),

    (SqlName: 'FLOAT';          AdvancedFieldType: aftDouble;         BaseFieldType: bftDouble;       FieldType: ftFloat),
    (SqlName: 'FLOAT';          AdvancedFieldType: aftDouble;         BaseFieldType: bftDouble;       FieldType: ftBCD),
    (SqlName: 'SINGLE';         AdvancedFieldType: aftSingle;         BaseFieldType: bftSingle;       FieldType: ftFloat),
    (SqlName: 'DOUBLE';         AdvancedFieldType: aftDouble;         BaseFieldType: bftDouble;       FieldType: ftFloat),
    (SqlName: 'EXTENDED';       AdvancedFieldType: aftExtended;       BaseFieldType: bftExtended;     FieldType: ftFloat),

    (SqlName: 'LOGICAL';        AdvancedFieldType: aftBoolean;        BaseFieldType: bftLogical;      FieldType: ftBoolean),
    (SqlName: 'BOOLEAN';        AdvancedFieldType: aftBoolean;        BaseFieldType: bftLogical;      FieldType: ftBoolean),
    (SqlName: 'BOOL';           AdvancedFieldType: aftBoolean;        BaseFieldType: bftLogical;      FieldType: ftBoolean),
    (SqlName: 'BIT';            AdvancedFieldType: aftBoolean;        BaseFieldType: bftLogical;      FieldType: ftBoolean),

    (SqlName: 'CURRENCY';        AdvancedFieldType: aftCurrency;      BaseFieldType: bftCurrency;     FieldType: ftCurrency),
    (SqlName: 'MONEY';          AdvancedFieldType: aftCurrency;      BaseFieldType: bftCurrency;     FieldType: ftCurrency),

    (SqlName: 'DATE';            AdvancedFieldType: aftDate;          BaseFieldType: bftDate;         FieldType: ftDate),
    (SqlName: 'TIME';            AdvancedFieldType: aftTime;          BaseFieldType: bftTime;         FieldType: ftTime),
    (SqlName: 'DATETIME';        AdvancedFieldType: aftDateTime;      BaseFieldType: bftDateTime;     FieldType: ftDateTime),
{$IFDEF D6H}
    (SqlName: 'TIMESTAMP';       AdvancedFieldType: aftTimeStamp;     BaseFieldType: bftDateTime;     FieldType: ftTimeStamp),
{$ELSE}
    (SqlName: 'TIMESTAMP';       AdvancedFieldType: aftTimeStamp;     BaseFieldType: bftDateTime;     FieldType: ftUnknown),
{$ENDIF}

    (SqlName: 'BYTES';           AdvancedFieldType: aftBytes;         BaseFieldType: bftBytes;        FieldType: ftBytes),
    (SqlName: 'VARBYTES';        AdvancedFieldType: aftVarBytes;      BaseFieldType: bftVarBytes;     FieldType: ftVarBytes),

    (SqlName: 'BLOB';            AdvancedFieldType: aftBlob;          BaseFieldType: bftBlob;         FieldType: ftBlob),
    (SqlName: 'BLOB';            AdvancedFieldType: aftBlob;          BaseFieldType: bftBlob;         FieldType: ftParadoxOle),
    (SqlName: 'BLOB';            AdvancedFieldType: aftBlob;          BaseFieldType: bftBlob;         FieldType: ftTypedBinary),
    (SqlName: 'BLOB';            AdvancedFieldType: aftBlob;          BaseFieldType: bftBlob;         FieldType: ftDBaseOle),
{$IFDEF D5H}
    (SqlName: 'BLOB';            AdvancedFieldType: aftBlob;          BaseFieldType: bftBlob;         FieldType: ftOraBlob),
    (SqlName: 'BLOB';            AdvancedFieldType: aftBlob;          BaseFieldType: bftBlob;         FieldType: ftOraClob),
{$ELSE}
    (SqlName: 'BLOB';            AdvancedFieldType: aftBlob;          BaseFieldType: bftBlob;         FieldType: ftUnknown),
    (SqlName: 'BLOB';            AdvancedFieldType: aftBlob;          BaseFieldType: bftBlob;         FieldType: ftUnknown),
{$ENDIF}
    (SqlName: 'GRAPHIC';         AdvancedFieldType: aftGraphic;       BaseFieldType: bftBlob;         FieldType: ftGraphic),
    (SqlName: 'MEMO';            AdvancedFieldType: aftMemo;          BaseFieldType: bftClob;         FieldType: ftMemo),
    (SqlName: 'CLOB';            AdvancedFieldType: aftMemo;          BaseFieldType: bftClob;         FieldType: ftMemo),
    (SqlName: 'FORMATTEDMEMO';   AdvancedFieldType: aftFormattedMemo; BaseFieldType: bftClob;         FieldType: ftFmtMemo),
    (SqlName: 'WIDEMEMO';        AdvancedFieldType: aftWideMemo;      BaseFieldType: bftWideClob;     FieldType: ftBlob),
    (SqlName: 'WIDECLOB';        AdvancedFieldType: aftWideMemo;      BaseFieldType: bftWideClob;     FieldType: ftMemo),
{$IFDEF D5H}
    (SqlName: 'GUID';            AdvancedFieldType: aftGuid;          BaseFieldType: bftChar;         FieldType: ftGuid)
{$ELSE}
    (SqlName: 'GUID';            AdvancedFieldType: aftGuid;          BaseFieldType: bftChar;         FieldType: ftUnknown)
{$ENDIF}
  );

  // Convert AdvancedFieldType to BasicFieldType
  function AdvancedFieldTypeToBaseFieldType(AdvancedFieldType: TMYLDBAdvancedFieldType): TMYLDBBaseFieldType;
  // Convert BaseFieldType to AdvancedFieldType
  function BaseFieldTypeToAdvancedFieldType(BaseFieldType: TMYLDBBaseFieldType): TMYLDBAdvancedFieldType;

  // Converts TFieldType to TMYLDBAdvancedFieldType
  function FieldTypeToMYLDBAdvFieldType(FieldType: TFieldType): TMYLDBAdvancedFieldType;
  // Converts TMYLDBAdvancedFieldType to TFieldType
  function MYLDBAdvFieldTypeToFieldType(AdvancedFieldType: TMYLDBAdvancedFieldType): TFieldType;

  // AdvancedFieldType for print
  function AftToStr(AdvancedFieldType: TMYLDBAdvancedFieldType): String;
  // TypeName to TMYLDBAdvancedFieldType
  function StrToAft(TypeName: String): TMYLDBAdvancedFieldType;
  // BaseFieldType for print
  function BftToStr(BaseFieldType: TMYLDBBaseFieldType): String;
  // FieldType for print
  function FtToStr(FieldType: TFieldType): String;


  // Convert MYLDBDate to Date
  function MYLDBDateToDate(MYLDBDate: TMYLDBDate): TDateTime; overload;
  // Convert Date to MYLDBDate
  function DateToMYLDBDate(Date: TDateTime): TMYLDBDate; overload;

  // Convert MYLDBTime to Time
  function MYLDBTimeToTime(MYLDBTime: TMYLDBTime): TDateTime; overload;
  // Convert Time to MYLDBTime
  function TimeToMYLDBTime(Time: TDateTime): TMYLDBTime; overload;

  // Convert MYLDBDateTime to DateTime
  function MYLDBDateTimeToDateTime(MYLDBDateTime: TMYLDBDateTime): TDateTime; overload;
  // Convert DateTime to MYLDBDateTime
  function DateTimeToMYLDBDateTime(DateTime: TDateTime): TMYLDBDateTime; overload;

  // return true if field type is a BLOB field type
  function IsBLOBFieldType(FieldType: TMYLDBBaseFieldType): Boolean; overload;
  function IsBLOBFieldType(FieldType: TMYLDBAdvancedFieldType): Boolean; overload;

  // return true if field type is a string field type, but not wide string
  function IsStringFieldType(FieldType: TMYLDBBaseFieldType): Boolean; overload;
  function IsStringFieldType(FieldType: TMYLDBAdvancedFieldType): Boolean; overload;

  // return true if field type is a wide string field type
  function IsWideStringFieldType(FieldType: TMYLDBBaseFieldType): Boolean; overload;
  function IsWideStringFieldType(FieldType: TMYLDBAdvancedFieldType): Boolean; overload;

  // return true if field type is bytes field type
  function IsBytesFieldType(FieldType: TMYLDBBaseFieldType): Boolean; overload;
  function IsBytesFieldType(FieldType: TMYLDBAdvancedFieldType): Boolean; overload;

  // return true if field type is Autoinc field type
  function IsAutoincFieldType(FieldType: TMYLDBAdvancedFieldType): Boolean; overload;

  // return true if DataType is numeric
  function IsNumericFieldType(FieldType: TMYLDBBaseFieldType): Boolean; overload;
  function IsNumericFieldType(FieldType: TMYLDBAdvancedFieldType): Boolean; overload;

  // return true if DataType is TateTime, Time, Date, TimeStamp
  function IsDateTimeFieldType(FieldType: TMYLDBBaseFieldType): Boolean; overload;
  function IsDateTimeFieldType(FieldType: TMYLDBAdvancedFieldType): Boolean; overload;

  // return true if DataType is String, bytes, etc.
  function IsSizebleFieldType(FieldType: TMYLDBAdvancedFieldType): Boolean;

  // Result = Can cast this type
  function IsConvertableFieldType(FieldType: TMYLDBAdvancedFieldType): boolean;

  // Like '%_' compare for String
  function IsStrMatchPattern(StrPtr: PChar; PatternPtr: PChar; IsPatternEscaped: Boolean; EscapeChar: Char; bIgnoreCase:boolean): Boolean;
  // Like '%_' compare for WideString
  function IsWideStrMatchPattern(StrPtr: PWideChar; PatternPtr: PWideChar; IsPatternEscaped: Boolean; EscapeChar: WideChar; bIgnoreCase:boolean): Boolean;

  // SrcField.Value := DestField.Value
  procedure AssignField(SrcField, DestField: TField);

  // 'YYYY-MM-DD' => TDate
  function ISODateStrToDate(const DateStr: String; var Date: TDateTime): Boolean;
  // 'HH:MM:SS' => TTime
  function ISOTimeStrToTime(const TimeStr: String; var Time: TDateTime): Boolean;
  // 'YYYY-MM-DD HH:MM:SS' => TDateTime
  function ISOTimeStampStrToDateTime(const TimeStampStr: String; var DateTime: TDateTime): Boolean;

implementation

uses
{$IFDEF D6H}DateUtils,{$ELSE}MYLDBD4Routines,{$ENDIF}
Classes;

//------------------------------------------------------------------------------
// Convert AdvancedFieldType to BasicFieldType
//------------------------------------------------------------------------------
function AdvancedFieldTypeToBaseFieldType(AdvancedFieldType: TMYLDBAdvancedFieldType): TMYLDBBaseFieldType;
var i: Integer;
begin
  Result := bftUnknown;
  for i:=Low(SQLFieldTypes) to High(SQLFieldTypes) do
    if SQLFieldTypes[i].AdvancedFieldType = AdvancedFieldType then
      begin
        Result := SQLFieldTypes[i].BaseFieldType;
        break;
      end;
end;//AdvancedFieldTypeToBasicFieldType


//------------------------------------------------------------------------------
// Convert BaseFieldType to AdvancedFieldType
//------------------------------------------------------------------------------
function BaseFieldTypeToAdvancedFieldType(BaseFieldType: TMYLDBBaseFieldType): TMYLDBAdvancedFieldType;
var i: Integer;
begin
  Result := aftUnknown;
  for i:=Low(SQLFieldTypes) to High(SQLFieldTypes) do
    if SQLFieldTypes[i].BaseFieldType = BaseFieldType then
      begin
        Result := SQLFieldTypes[i].AdvancedFieldType;
        break;
      end;
end;//BaseFieldTypeToAdvancedFieldType


//------------------------------------------------------------------------------
// Converts TFieldType to TMYLDBAdvancedFieldType
//------------------------------------------------------------------------------
function FieldTypeToMYLDBAdvFieldType(FieldType: TFieldType): TMYLDBAdvancedFieldType;
var i: Integer;
begin
  Result := aftUnknown;
  for i:=Low(SQLFieldTypes) to High(SQLFieldTypes) do
    if SQLFieldTypes[i].FieldType = FieldType then
      begin
        Result := SQLFieldTypes[i].AdvancedFieldType;
        break;
      end;
  if (Result = aftUnknown) then
    raise EMYLDBException.Create(30289, ErrorGUnknownDataType,
                               [IntToStr(Integer(FieldType))]);
end;//FieldTypeToMYLDBAdvFieldType


//------------------------------------------------------------------------------
// Converts TMYLDBAdvancedFieldType to TFieldType
//------------------------------------------------------------------------------
function MYLDBAdvFieldTypeToFieldType(AdvancedFieldType: TMYLDBAdvancedFieldType): TFieldType;
var i: Integer;
begin
  Result := ftUnknown;
  for i:=Low(SQLFieldTypes) to High(SQLFieldTypes) do
    if SQLFieldTypes[i].AdvancedFieldType = AdvancedFieldType then
      begin
        Result := SQLFieldTypes[i].FieldType;
        break;
      end;
end;//MYLDBAdvFieldTypeToFieldType


//------------------------------------------------------------------------------
// AdvancedFieldType for print
//------------------------------------------------------------------------------
function AftToStr(AdvancedFieldType: TMYLDBAdvancedFieldType): String;
var i: Integer;
begin
  Result := 'Unknown';
  for i:=Low(SQLFieldTypes) to High(SQLFieldTypes) do
    if SQLFieldTypes[i].AdvancedFieldType = AdvancedFieldType then
      begin
        Result := SQLFieldTypes[i].SqlName;
        break;
      end;
end;//AftToStr


//------------------------------------------------------------------------------
// TypeName to TMYLDBAdvancedFieldType
//------------------------------------------------------------------------------
function StrToAft(TypeName: String): TMYLDBAdvancedFieldType;
var
 i: Integer;
 u: String;
begin
  Result := aftUnknown;
  u := UpperCase(TypeName);
  for i:=Low(SQLFieldTypes) to High(SQLFieldTypes) do
   if ( UpperCase(SQLFieldTypes[i].SqlName) = u ) then
    begin
     Result := SQLFieldTypes[i].AdvancedFieldType;