RealICQClient/DataProvider/HashDBProvider.pas

unit HashDBProvider;

interface

uses
  System.Classes, BaseDataProvider, InterfaceDataProvider, WideStrings;

type
  THashDBProvider = class(TBaseDataProvider, IHashProvider)
  private

  protected
    function GetTableName: string; override;
    function GetCreateTableSQL: string; override;
    function GetSQL(ASQL: string): string; virtual;
  public
    procedure Insert(AKey, AValue: WideString); overload; stdcall;
    procedure Insert(AKey, AValue: WideString; AVersion: Integer); overload; virtual; stdcall;
    procedure Insert(AHashs: TKeyValues); overload; virtual; stdcall;
    procedure Insert(AHash: TKeyValue); overload; stdcall;
    function Find(AKey: WideString): WideString; overload; stdcall;
    function FindKeyValue(AKey: WideString): TKeyValue; overload; virtual; stdcall;
    function FindAll: TWideStrings; stdcall;
    function Find(AKeys: array of WideString): TKeyValues; overload; virtual; stdcall;
    function FindLike(AKey: WideString): TStrings; stdcall;
    procedure Delete(AKey: WideString); stdcall;
    procedure DeleteAll; stdcall;
    procedure DestroyResult(AObject: TStrings); overload;
  end;

implementation

uses
  FireDAC.Comp.Client, BaseDataModule, System.SysUtils, System.VarUtils, System.Variants,
  Data.DB, LoggerImport, MD5_32;

const
  TABLE_NAME: string = 'DefaultHash';
  CREATE_TABLE: string = 'CREATE TABLE %s('+
    'Key Text, ' +
    'Value Text, ' +
    'MD5 Text, ' +
    'Version Integer, ' +
    'CreateTime Double)';
  INSERT_SQL: string = 'INSERT INTO %s(Key, Value, MD5, Version, CreateTime) VALUES(:key, :value, :md5, :version, :createTime)';
  DELETE_SQL: string = 'DELETE FROM %s WHERE Key = :key';
  DELETE_ALL_SQL: string = 'DELETE FROM %s WHERE';
  SELECT_SQL: string = 'SELECT Key, Value, Version FROM %s WHERE Key = :key';
  SELECT_ALL_SQL: string = 'SELECT Key, Value, Version FROM %s';
  SELECT_LIKE_SQL: string = 'SELECT Key, Value, Version FROM %s WHERE Key = %s';
  SELECT_BY_VERSION_SQL: string = 'SELECT Value FROM %s WHERE Key = :key and Version =:version';


{ TAppIconProvider }

procedure THashDBProvider.Delete(AKey: WideString);
var
  AConnection: TFDCustomConnection;
begin
  if (AKey = '') then
    Exit;
  if not Inited and not ReInit then
    Exit;
  AConnection := BaseDataModel.GetConnection;
  try
    AConnection.Connected := True;
    AConnection.ExecSQL(GetSQL(DELETE_SQL), [AKey]);
  finally
    BaseDataModel.GiveBackConnection(AConnection);
  end;
end;


procedure THashDBProvider.DeleteAll;
var
  AConnection: TFDCustomConnection;
begin
  if not Inited and not ReInit then
    Exit;
  AConnection := BaseDataModel.GetConnection;
  try
    AConnection.Connected := True;
    AConnection.ExecSQL(GetSQL(DELETE_ALL_SQL));
  finally
    BaseDataModel.GiveBackConnection(AConnection);
  end;
end;

procedure THashDBProvider.DestroyResult(AObject: TStrings);
begin
  if AObject <> nil then
  begin
    AObject.Clear;
    FreeAndNil(AObject);
  end;
end;


function THashDBProvider.FindKeyValue(AKey: WideString): TKeyValue;
var
  AQuery: TFDQuery;
  ACode: string;
begin
  Result[0] := '';
  Result[1] := '';
  Result[2] := '0';
  if not Inited and not ReInit then
    Exit;
  if (AKey = '') then
    Exit;
  ACode := Format('THashDBProvider.Find(%s)', [AKey]);
  try
    AQuery := BaseDataModel.GetQuery;
    AQuery.Connection.Connected := True;
    AQuery.Open(GetSQL(SELECT_SQL), [AKey]);
    AQuery.First;
    if not AQuery.Eof then
    begin
      Result[0] := AKey;
      Result[1] := AQuery.FieldByName('Value').AsString;
      Result[2] := IntToStr(AQuery.FieldByName('Version').AsInteger);
    end;
  except
    on E: Exception do
    begin
      Error(E.Message, ACode);
      BaseDataModel.GiveBackQuery(AQuery);
    end;
  end;
  BaseDataModel.GiveBackQuery(AQuery);
end;

function THashDBProvider.Find(AKey: WideString): WideString;
begin
  Result := FindKeyValue(AKey)[1];
end;

procedure THashDBProvider.Insert(AKey, AValue: WideString);
begin
  Insert(AKey, AValue, 0);
end;

function THashDBProvider.FindAll: TWideStrings;
var
  AQuery: TFDQuery;
begin
  if not Inited and not ReInit then
    Exit;
  try
    AQuery := BaseDataModel.GetQuery;
    AQuery.Connection.Connected := True;
    AQuery.Open(GetSQL(SELECT_ALL_SQL));
    AQuery.First;
    Result := TWideStringList.Create;
    while not AQuery.Eof do
    begin
      Result.Add(AQuery.FieldByName('Value').AsString);
      AQuery.Next;
    end;
  except
    on E: Exception do
    begin
      Error(E.Message, 'THashDBProvider.FindAll');
      DestroyResult(TObject(Result));
      BaseDataModel.GiveBackQuery(AQuery);
    end;
  end;
  BaseDataModel.GiveBackQuery(AQuery);
end;

function THashDBProvider.Find(AKeys: array of WideString): TKeyValues;
var
  AQuery: TFDQuery;
  ALen: Cardinal;
  iLoop,
  jLoop: Integer;
begin
  if not Inited and not ReInit then
    Exit;
  ALen := Length(AKeys);
  if (ALen = 0) then
    Exit;

  try
    AQuery := BaseDataModel.GetQuery;
    AQuery.Connection.Connected := True;
    AQuery.FetchOptions.AutoClose := False;

    for iLoop := 0 to ALen - 1 do
    begin
      AQuery.SQL.Add(GetSql(SELECT_ALL_SQL) + ' WHERE KEY = "' + AKeys[iLoop] + '";');
    end;

    SetLength(Result, ALen);
    jLoop := 0;
    AQuery.Open;
    for iLoop := 0 to ALen - 1 do
    begin
      if AQuery.Eof then
      begin
        AQuery.NextRecordSet;
        continue;
      end;
      Result[jLoop][0] := AQuery.FieldByName('Key').AsString;
      Result[jLoop][1] := AQuery.FieldByName('Value').AsString;
      Result[jLoop][2] := IntToStr(AQuery.FieldByName('Version').AsInteger);
      inc(jLoop);
      AQuery.NextRecordSet;
    end;
    if jLoop < ALen then
      SetLength(Result, jLoop);//Result := Copy(Result, 0, jLoop);
  except
    on E: Exception do
    begin
      Error(E.Message, 'THashDBProvider.Insert');
      BaseDataModel.GiveBackQuery(AQuery);
      SetLength(Result, 0);
    end;
  end;
  BaseDataModel.GiveBackQuery(AQuery);
  Success(Format('查询出%d条数据.', [jLoop]), 'THashDBProvider.Find');
end;

function THashDBProvider.FindLike(AKey: WideString): TStrings;
var
  ASQL: string;
var
  AQuery: TFDQuery;
begin
  if not Inited and not ReInit then
    Exit;
  if (AKey = '') then
    Exit;

  ASQL := GetSQL(SELECT_ALL_SQL) + Format(' WHERE Key = %s', [AKey]);
  try
    AQuery := BaseDataModel.GetQuery;
    AQuery.Connection.Connected := True;
    AQuery.Open(ASQL);
    AQuery.First;
    Result := TStringList.Create;
    while not AQuery.Eof do
    begin
      Result.Add(AQuery.FieldByName('Value').AsString);
      AQuery.Next;
    end;
  except
    on E: Exception do
    begin
      Error(E.Message, 'THashDBProvider.FindLike');
      DestroyResult(TObject(Result));
      BaseDataModel.GiveBackQuery(AQuery);
    end;
  end;
  BaseDataModel.GiveBackQuery(AQuery);
end;

function THashDBProvider.GetCreateTableSQL: string;
begin
  Result := GetSQL(CREATE_TABLE);
end;

function THashDBProvider.GetSQL(ASQL: string): string;
begin
  Result := Format(ASQL, [GetTableName]);
end;

function THashDBProvider.GetTableName: string;
begin
  Result := TABLE_NAME;
end;

procedure THashDBProvider.Insert(AKey, AValue: WideString; AVersion: Integer);
var
  AConnection: TFDCustomConnection;
  ALen: Cardinal;
begin

  if not Inited and not ReInit then
    Exit;
  ALen := Length(AValue);
  if (AKey = '') or (ALen = 0) then
    Exit;

  AConnection := BaseDataModel.GetConnection;
  try
    AConnection.Connected := True;
    AConnection.ExecSQL(GetSQL(DELETE_SQL), [AKey]);
    AConnection.ExecSQL(GetSQL(INSERT_SQL), [AKey, AValue, MD5Print(MD5String(AValue)), AVersion, Now]);
  except
    on E: Exception do
    begin
      BaseDataModel.GiveBackConnection(AConnection);
      ERROR(E.Message, 'THashDBProvider.Insert');
    end;
  end;
  BaseDataModel.GiveBackConnection(AConnection);
  Success(Format('AKey:%s', [AKey]), 'THashDBProvider.Insert');
end;

procedure THashDBProvider.Insert(AHash: TKeyValue);
begin
  Insert(AHash[0], AHash[1], StrToIntDef(AHash[2], -1));
end;

procedure THashDBProvider.Insert(AHashs: TKeyValues);
var
  AQuery: TFDQuery;
  ALen: Cardinal;
  iLoop: Integer;
begin
  if not Inited and not ReInit then
    Exit;
  ALen := Length(AHashs);
  if (ALen = 0) then
    Exit;
  if ALen = 1 then
  begin
    Insert(AHashs[0]);
    Exit;
  end;

  try
    AQuery := BaseDataModel.GetQuery;
    AQuery.Connection.Connected := True;
    AQuery.FetchOptions.AutoClose := False;

    AQuery.SQL.Text := GetSQL(INSERT_SQL);
    AQuery.Params.ArraySize := ALen;

    for iLoop := 0 to ALen - 1 do
    begin
      //:key, :value, :md5, :version, :createTime
      AQuery.Params[0].AsStrings[iLoop] := AHashs[iLoop][0];
      AQuery.Params[1].AsStrings[iLoop] := AHashs[iLoop][1];
      AQuery.Params[2].AsStrings[iLoop] := MD5Print(MD5String(AHashs[iLoop][1]));
      AQuery.Params[3].AsIntegers[iLoop] := StrToIntDef(AHashs[iLoop][2], -1);
      AQuery.Params[4].AsFloats[iLoop] := Now;
    end;

    AQuery.Execute(ALen, 0);
    AQuery.NextRecordSet;
  except
    on E: Exception do
    begin
      Error(E.Message, 'THashDBProvider.Insert');
      BaseDataModel.GiveBackQuery(AQuery);
    end;
  end;
  BaseDataModel.GiveBackQuery(AQuery);
  Success(Format('批量插入%d条数据.', [ALen]), 'THashDBProvider.Insert');
end;

end.