{$IFNDEF CLR}

{$I Dac.inc}

unit DASQLGenerator;
{$ENDIF}

interface

uses
{$IFDEF CLR}
  System.Runtime.InteropServices, System.Text, Variants,
{$ELSE}
  CLRClasses,
{$ENDIF}
  Classes, SysUtils, DAConsts, CRTypes, CRParser, MemData, CRAccess;

type
  _TStatementType = (_stQuery, _stInsert, _stUpdate, _stDelete, _stLock, _stRefresh,
    _stCustom, _stRefreshQuick, _stRefreshCheckDeleted, _stBatchUpdate, _stRecCount);

{ TDAParamInfo }

  _TParamType = (_ptUnknown, _ptInput, _ptOutput, _ptInputOutput, _ptResult);

  TDAParamInfoClass = class of TDAParamInfo;
  TDAParamInfo = class(TCollectionItem)
  protected
    {$IFDEF AUTOREFCOUNT}[weak]{$ENDIF}
    FFieldDesc: TFieldDesc;
    FOld: boolean;
    FParamIndex: integer;
    FParamName: string;
    FParamType: _TParamType;
  public
    property FieldDesc: TFieldDesc read FFieldDesc write FFieldDesc;
    property Old: boolean read FOld write FOld;
    property ParamIndex: integer read FParamIndex write FParamIndex;
    property ParamName: string read FParamName write FParamName;
    property ParamType: _TParamType read FParamType write FParamType;
  end;

  TDAParamsInfo = class(TCollection)
  protected
    function GetItem(Index: Integer): TDAParamInfo;
    procedure SetItem(Index: Integer; Value: TDAParamInfo);
  public
    property Items[Index: Integer]: TDAParamInfo read GetItem write SetItem; default;
  end;

{ TSQLGeneratorService }

  TSQLGeneratorServiceClass = class of TSQLGeneratorService;

  TSQLGeneratorService = class
  public
    constructor Create; virtual;

    procedure RaiseError(const Message: string); virtual;

    function GetOldRecBuf: IntPtr; virtual; abstract;
    function GetNewRecBuf: IntPtr; virtual; abstract;
    function BlobFieldModified(FieldDesc: TCRFieldDesc): boolean; virtual;

    function GetUpdateCommand: TCRCommand; virtual;
    function GetDBKeyList(const TableName, IndexName: string): string; virtual;

    function ParamExists(const ParamName: string): boolean; virtual;

    function BaseSQL: string; virtual; abstract;
    function FinalSQL: string; virtual; abstract;
    function FilterSQL: string; virtual; abstract;
  end;

{ TDASQLGenerator }

  TDASQLGenerator = class
  protected
    FService: TSQLGeneratorService;

    {$IFDEF AUTOREFCOUNT}[weak]{$ENDIF}
    FIRecordSet: TCRRecordSet;
    {$IFDEF AUTOREFCOUNT}[weak]{$ENDIF}
    FICommand: TCRCommand;  // for performance
    {$IFDEF AUTOREFCOUNT}[weak]{$ENDIF}
    FTableInfo: TCRTableInfo;
    FKeySequence: string;

                                    // stInsert                 stUpdate                 stDelete                 stRefresh        stRecCount
    FHeaderSB,                      // INSERT INTO Tbl(         UPDATE Tbl SET           DELETE FROM Tbl WHERE    SELECT           SELECT COUNT(*) FROM (
    FFldSB,                         // f1, f2, f3, ...          f1 = :p1, f2 = :p2, ...                           f1, f2, f3, ...
    FMiddleSB,                      // ) VALUES (               WHERE                                             FROM Tbl WHERE   query
    FFldParamSB,                    // :p1, :p2, :p3, ...
    FCondSB,                        //                          f0 = :p0                 f0 = :p0                 f0 = :p0
    FFooterSB: StringBuilder;       // )                                                                                           )

    FOldRecBuf, FNewRecBuf: IntPtr;
    FDesignMode: boolean;
    FDMLRefresh: boolean;
    FSubstituteParamName: boolean;
    FRefreshSQLFields: string;

    FParamsInfo: TDAParamsInfo;
    function GetParamInfoClass: TDAParamInfoClass; virtual;

    function GetIConnection: TCRConnection;
    procedure SetIRecordSet(Value: TCRRecordSet);
    function SQLInfo: TSQLInfo;
    function GetParserClass: TSQLParserClass;
    function GetFullRefresh: boolean;
    function GetReadOnly: boolean;
    function GetQuoteNames: boolean;

    function QuoteName(const AName: string): string;
    function ScanParams: boolean;
    function CachedUpdates: boolean;
    function InCacheProcessing: boolean;

    function GetOldRecBuf: IntPtr;
    function GetNewRecBuf: IntPtr;

    procedure Clear; virtual;
    function AssembleSB: string;

    function FieldIsNull(FieldDesc: TCRFieldDesc; OldValue: boolean; Data: TData; OldRecBuf, NewRecBuf: IntPtr): boolean; overload; virtual;
    function FieldIsNull(FieldDesc: TCRFieldDesc; OldValue: boolean): boolean; overload; virtual;

    function BlobModified(FieldDesc: TCRFieldDesc; Data: TData; OldRecBuf, NewRecBuf: IntPtr): boolean;
    function FieldModified(FieldDesc: TCRFieldDesc; Data: TData; OldRecBuf, NewRecBuf: IntPtr): boolean; overload; virtual;
    function FieldModified(FieldDesc: TCRFieldDesc): boolean; overload; virtual;

    function GetActualFieldName(FieldDesc: TCRFieldDesc; IsRefresh: boolean): string;

    function GenerateIndexName(const Name: string): string; virtual;
    function MaxIdentLength: integer; virtual;

    function IsSubstituteParamName: boolean; virtual;

    function SQLGetFrom(const SQLText: string): string;
    function SQLGetWhere(const SQLText: string): string;
    function SQLAddWhere(const SQLText, Condition: string): string;
    function SQLSetOrderBy(const SQLText, Fields: string): string;

    procedure AddParam(SB: StringBuilder; FieldDesc: TFieldDesc;
      const StatementType: _TStatementType;
      Index: integer = -1;
      Old: boolean = False); overload;
    procedure AddParam(SB: StringBuilder; FieldDesc: TFieldDesc;
      const StatementType: _TStatementType;
      const ParamType: _TParamType;
      Index: integer = -1;
      Old: boolean = False); overload; virtual;

    procedure AddFieldToInsertSQL(FieldDesc: TCRFieldDesc; const Index: integer = -1); virtual;
    procedure AddFieldToUpdateSQL(FieldDesc: TCRFieldDesc;
      const ModifiedFieldsOnly: boolean;
      const Index: integer = -1); virtual;
    procedure AddFieldToRefreshSQL(FieldDesc: TCRFieldDesc); virtual;
    procedure AddFieldToCondition(SB: StringBuilder; FieldDesc: TCRFieldDesc;
      const StatementType: _TStatementType; const Index: integer = -1); virtual;

    procedure GenerateInsertSQL(
      const KeyAndDataFields: TKeyAndDataFields;
      const ModifiedFieldsOnly: boolean;
      const Index: integer = -1); virtual;
    procedure GenerateUpdateSQL(
      const KeyAndDataFields: TKeyAndDataFields;
      const ModifiedFieldsOnly: boolean;
      const Index: integer = -1); virtual;
    procedure GenerateDeleteSQL(
      const KeyAndDataFields: TKeyAndDataFields;
      const ModifiedFieldsOnly: boolean;
      const Index: integer = -1); virtual;
    procedure GenerateLockSQL(
      const KeyAndDataFields: TKeyAndDataFields;
      const Index: integer = -1); virtual;
    procedure GenerateRefreshSQLSelectPart(const KeyAndDataFields: TKeyAndDataFields); virtual;
    procedure GenerateRefreshSQLFromPart; virtual;
    procedure GenerateRefreshSQL(const KeyAndDataFields: TKeyAndDataFields); virtual;
    procedure GenerateRefreshQuickSQL(const KeyAndDataFields: TKeyAndDataFields); virtual;
    procedure GenerateRefreshCheckDeletedSQL(const KeyAndDataFields: TKeyAndDataFields); virtual;

    procedure GenerateConditions( // Generate WHERE part for UPDATE, DELETE, REFRESH SQLs
      SB: StringBuilder; const StatementType: _TStatementType;
      const KeyAndDataFields: TKeyAndDataFields; const Index: integer = -1); virtual;

    property FullRefresh: boolean read GetFullRefresh;
    property ReadOnly: boolean read GetReadOnly;
    property QuoteNames: boolean read GetQuoteNames;

  public
    constructor Create(ServiceClass: TSQLGeneratorServiceClass); virtual;
    destructor Destroy; override;

    function IndexedPrefix: string;
    function DecodeFieldIndex(const FieldName: string): integer; virtual;

    // Generate insert, update, delete or refresh SQL statements
    function GenerateSQLforUpdTable(UpdatingTableInfo: TCRTableInfo;
      const KeyAndDataFields: TKeyAndDataFields;
      const StatementType: _TStatementType;
      const ModifiedFieldsOnly: boolean;
      const Index: integer = -1): string;

    function GenerateSQL(const StatementType: _TStatementType;
      const ModifiedFieldsOnly: boolean;
      const Index: Integer = -1): string; virtual;

    function GenerateTableSQL(const TableName, OrderFields: string): string; virtual;
    function GenerateRecCountSQL(UseBaseSQL: boolean = False): string; virtual;

    function GenerateSelectValues(const ValuesList: string): string; virtual;

    property KeySequence: string read FKeySequence write FKeySequence;

    property IRecordSet: TCRRecordSet write SetIRecordSet;
    property DesignMode: boolean write FDesignMode;
    property DMLRefresh: boolean write FDMLRefresh;
    property SubstituteParamName: boolean write FSubstituteParamName;

    property ParamsInfo: TDAParamsInfo read FParamsInfo;
    property RefreshSQLFields: string read FRefreshSQLFields;
    property Service: TSQLGeneratorService read FService;
  end;

implementation

{ TDAParamsInfo }

function TDAParamsInfo.GetItem(Index: Integer): TDAParamInfo;
begin
  Result := TDAParamInfo(inherited GetItem(Index));
end;

procedure TDAParamsInfo.SetItem(Index: Integer; Value: TDAParamInfo);
begin
  inherited SetItem(Index, Value);
end;

{ TSQLGeneratorService }

constructor TSQLGeneratorService.Create;
begin
  inherited Create;
end;

procedure TSQLGeneratorService.RaiseError(const Message: string);
begin
  raise Exception.Create(Message);
end;

function TSQLGeneratorService.BlobFieldModified(FieldDesc: TCRFieldDesc): boolean;
begin
  Result := False;
end;

function TSQLGeneratorService.GetUpdateCommand: TCRCommand;
begin
  Result := nil;
end;

function TSQLGeneratorService.GetDBKeyList(const TableName, IndexName: string): string;
begin
  Result := '';
end;

function TSQLGeneratorService.ParamExists(const ParamName: string): boolean;
begin
  Result := False;
end;

{ TDASQLGenerator }

constructor TDASQLGenerator.Create(ServiceClass: TSQLGeneratorServiceClass);
begin
  inherited Create;

  FService := ServiceClass.Create;

  FHeaderSB := StringBuilder.Create(100);
  FFldSB := StringBuilder.Create(100);
  FMiddleSB := StringBuilder.Create(100);
  FFldParamSB := StringBuilder.Create(100);
  FCondSB := StringBuilder.Create(100);
  FFooterSB := StringBuilder.Create(100);

  FParamsInfo := TDAParamsInfo.Create(GetParamInfoClass);
  FSubstituteParamName := True;
end;

destructor TDASQLGenerator.Destroy;
begin
  FParamsInfo.Free;

  FHeaderSB.Free;
  FFldSB.Free;
  FMiddleSB.Free;
  FFldParamSB.Free;
  FCondSB.Free;
  FFooterSB.Free;

  FService.Free;

  inherited;
end;

procedure TDASQLGenerator.SetIRecordSet(Value: TCRRecordSet);
begin
  FIRecordSet := Value;
  if FIRecordSet <> nil then
    FICommand := FIRecordSet.GetCommand;
end;

function TDASQLGenerator.GetIConnection: TCRConnection;
begin
  if FICommand <> nil then
    Result := FICommand.GetConnection
  else
    Result := nil;
end;

function TDASQLGenerator.SQLInfo: TSQLInfo;
begin
  if FICommand <> nil then
    Result := FICommand.SQLInfo
  else
    Result := DefaultSQLInfo;
end;

function TDASQLGenerator.GetParserClass: TSQLParserClass;
begin
  if FICommand <> nil then
    Result := FICommand.GetParserClass
  else
    Result := TSQLParser;
end;

function TDASQLGenerator.GetFullRefresh: boolean;
var
  Value: Variant;
begin
  if FIRecordSet <> nil then begin
    FIRecordSet.GetProp(prFullRefresh, Value);
    Result := Value;
  end
  else
    Result := False;
end;

function TDASQLGenerator.GetReadOnly: boolean;
var
  Value: Variant;
begin
  if FIRecordSet <> nil then begin
    FIRecordSet.GetProp(prReadOnly, Value);
    Result := Value;
  end
  else
    Result := False;
end;

function TDASQLGenerator.GetQuoteNames: boolean;
var
  Value: Variant;
begin
  if FICommand <> nil then begin
    FICommand.GetProp(prQuoteNames, Value);
    Result := Value;
  end
  else
    Result := False;
end;

function TDASQLGenerator.QuoteName(const AName: string): string;
begin
  if QuoteNames or SQLInfo.QuotesNeeded(AName) then
    Result := SQLInfo.Quote(AName)
  else
    Result := AName;
end;

function TDASQLGenerator.ScanParams: boolean;
var
  Value: Variant;
begin
  if FICommand <> nil then begin
    FICommand.GetProp(prScanParams, Value);
    Result := Value;
  end
  else
    Result := True;
end;

function TDASQLGenerator.CachedUpdates: boolean;
begin
  if FIRecordSet <> nil then
    Result := FIRecordSet.CachedUpdates
  else
    Result := False;
end;

function TDASQLGenerator.InCacheProcessing: boolean;
begin
  if FIRecordSet <> nil then
    Result := FIRecordSet.InCacheProcessing
  else
    Result := False;
end;

function TDASQLGenerator.GetOldRecBuf: IntPtr;
begin
  if FOldRecBuf = nil then
    FOldRecBuf := FService.GetOldRecBuf;

  Result := FOldRecBuf;
end;

function TDASQLGenerator.GetNewRecBuf: IntPtr;
begin
  if FNewRecBuf = nil then
    FNewRecBuf := FService.GetNewRecBuf;

  Result := FNewRecBuf;
end;

function TDASQLGenerator.GetParamInfoClass: TDAParamInfoClass;
begin
  Result := TDAParamInfo;
end;

procedure TDASQLGenerator.Clear;
begin
  FHeaderSB.Length := 0;
  FFldSB.Length := 0;
  FMiddleSB.Length := 0;
  FFldParamSB.Length := 0;
  FCondSB.Length := 0;
  FFooterSB.Length := 0;
  FRefreshSQLFields := '';

  FOldRecBuf := nil;
  FNewRecBuf := nil;
end;

function TDASQLGenerator.AssembleSB: string;
begin
  Result :=
    FHeaderSB.ToString +
    FFldSB.ToString +
    FMiddleSB.ToString +
    FFldParamSB.ToString +
    FCondSB.ToString +
    FFooterSB.ToString;
end;

function TDASQLGenerator.FieldIsNull(FieldDesc: TCRFieldDesc; OldValue: boolean;
  Data: TData; OldRecBuf, NewRecBuf: IntPtr): boolean;
//This function added for Expand fields support
var
  i: integer;
  ChildFieldDesc: TFieldDesc;
begin
  if FieldDesc.IsObject then begin
    i := FieldDesc.FieldNo;
    Result := False;
    while i < Data.Fields.Count do begin
      ChildFieldDesc := Data.Fields[i];
      Result := (ChildFieldDesc.ParentField = FieldDesc) and FieldIsNull(TCRFieldDesc(ChildFieldDesc), OldValue);
      inc(i);
      if not Result then
        Break;
    end;
  end
  else
    if OldValue then
      Result := Data.GetNull(FieldDesc, OldRecBuf)
    else
      Result := Data.GetNull(FieldDesc, NewRecBuf);
end;

function TDASQLGenerator.FieldIsNull(FieldDesc: TCRFieldDesc; OldValue: boolean): boolean;
begin
  Result := FieldIsNull(FieldDesc, OldValue, FIRecordSet, GetOldRecBuf, GetNewRecBuf);
end;

function TDASQLGenerator.BlobModified(FieldDesc: TCRFieldDesc; Data: TData; OldRecBuf, NewRecBuf: IntPtr): boolean;
begin
  if Data.InCacheProcessing then
    Result := Data.GetObject(FieldDesc, NewRecBuf) <> Data.GetObject(FieldDesc, OldRecBuf)
  else begin
    Result := TBlob(Data.GetObject(FieldDesc, NewRecBuf)).CanRollback or
      FService.BlobFieldModified(FieldDesc);
  end;
end;

function TDASQLGenerator.FieldModified(FieldDesc: TCRFieldDesc; Data: TData; OldRecBuf, NewRecBuf: IntPtr): boolean;
//This function added for Expand fields support
var
  i: integer;
  ChildFieldDesc: TFieldDesc;
begin
  if FieldDesc.IsBlob then
    Result := BlobModified(FieldDesc, Data, OldRecBuf, NewRecBuf)
  else
  if FieldDesc.IsObject then begin
    Result := False;
    i := FieldDesc.FieldNo;
    while i < Data.Fields.Count do begin
      // Child FieldDescs always next to parent FielDescs
      // But child fields can be mixed with their own childs in case of nested objects
      ChildFieldDesc := Data.Fields[i];
      Result := (ChildFieldDesc.ParentField = FieldDesc) and FieldModified(TCRFieldDesc(ChildFieldDesc));
      inc(i);
      if Result then
        break;
    end;
  end
  else
    Result := TMemData(Data).CompareFields(OldRecBuf, NewRecBuf, FieldDesc, [coOrdinalCompare]) <> 0;
end;

function TDASQLGenerator.FieldModified(FieldDesc: TCRFieldDesc): boolean;
begin
  Result := FieldModified(FieldDesc, FIRecordSet, GetOldRecBuf, GetNewRecBuf);
end;

function TDASQLGenerator.GetActualFieldName(FieldDesc: TCRFieldDesc; IsRefresh: boolean): string;
begin
  Result := FieldDesc.ActualNameQuoted(SQLInfo, QuoteNames);
  if IsRefresh and (FullRefresh or ReadOnly) and
    not FIRecordSet.UpdTableIsArtificial
  then begin // Use original Select part, so we could use Field aliasess
    if FieldDesc.TableInfo <> nil then
      if FieldDesc.TableInfo.TableAlias <> '' then
        Result := SQLInfo.NormalizeName(FieldDesc.TableInfo.TableAlias, QuoteNames) +
          '.' + Result
      else
        Result := SQLInfo.NormalizeName(FieldDesc.TableInfo.TableName, QuoteNames) +
          '.' + Result;
  end;
end;

function TDASQLGenerator.GenerateIndexName(const Name: string): string;
begin
  Result := '_' + Name;
end;

function TDASQLGenerator.DecodeFieldIndex(const FieldName: string): integer;
var
  e: integer;
begin
  Result := -1;
  if (Length(FieldName) >= 2) and (FieldName[1] = '_') then begin
    Val(Copy(FieldName, 2, MaxInt), Result, e);
    if e <> 0 then
      Result := -1;
  end;
end;

function TDASQLGenerator.IndexedPrefix: string;
begin
  Result := 'P_';
end;

function TDASQLGenerator.MaxIdentLength: integer;
begin
  Result := MaxInt;
end;

function TDASQLGenerator.IsSubstituteParamName: boolean;
begin
  Result := FSubstituteParamName;
end;

function TDASQLGenerator.SQLGetFrom(const SQLText: string): string;
begin
  Result := _GetFrom(SQLText, GetParserClass, False, MacroChar);
end;

function TDASQLGenerator.SQLGetWhere(const SQLText: string): string;
begin
  Result := _GetWhere(SQLText, GetParserClass, False, MacroChar);
end;

function TDASQLGenerator.SQLAddWhere(const SQLText, Condition: string): string;
begin
  Result := _AddWhere(SQLText, Condition, GetParserClass, False, MacroChar);
end;

function TDASQLGenerator.SQLSetOrderBy(const SQLText, Fields: string): string;
begin
  Result := _SetOrderBy(SQLText, Fields, GetParserClass);
end;

procedure TDASQLGenerator.AddParam(SB: StringBuilder; FieldDesc: TFieldDesc;
  const StatementType: _TStatementType; Index: integer = -1; Old: boolean = False);
begin
  AddParam(SB, FieldDesc, StatementType, _ptUnknown, Index, Old);
end;

procedure TDASQLGenerator.AddParam(SB: StringBuilder;
  FieldDesc: TFieldDesc;
  const StatementType: _TStatementType;
  const ParamType: _TParamType;
  Index: integer = -1; // only for CachedUpdates
  Old: boolean = False);
var
  ParamName, Prefix: string;
  ParamInfo: TDAParamInfo;
  CheckQuoteNeeded: boolean;
begin
  ParamName := FieldDesc.Name;
  CheckQuoteNeeded := True;

  if Old then
    Prefix := 'Old_';

  if _stRefresh = StatementType then
    if FService.ParamExists(ParamName) then
      Index := 1;

  if Index > - 1 then
    Prefix := IndexedPrefix + IntToStr(Index) + '_' + Prefix;

  if Length(Prefix + ParamName) <= MaxIdentLength then
    ParamName := Prefix + ParamName
  else begin
    ParamName := Prefix + IntToStr(FieldDesc.FieldNo);
    CheckQuoteNeeded := False;
  end;

  if SQLInfo.ParamQuoteAllowed then
    if QuoteNames or (CheckQuoteNeeded and SQLInfo.QuotesNeeded(FieldDesc.Name)) then
      ParamName := SQLInfo.Quote(ParamName);

  if not IsSubstituteParamName then begin
    SB.Append(':');
    SB.Append(ParamName);
  end
  else
    SB.Append('?');

  ParamInfo := TDAParamInfo(FParamsInfo.Add);
  ParamInfo.FieldDesc := FieldDesc;
  ParamInfo.Old := Old;
  ParamInfo.ParamIndex := Index;
  ParamInfo.ParamName := ParamName;
  ParamInfo.ParamType := ParamType;
end;

procedure TDASQLGenerator.AddFieldToCondition(SB: StringBuilder;
  FieldDesc: TCRFieldDesc;
  const StatementType: _TStatementType;
  const Index: integer = -1);
var
  ActualName: string;
begin
  if SB.Length > 0 then
    SB.Append(' AND ');

  Assert(FieldDesc <> nil);
  ActualName := GetActualFieldName(FieldDesc, StatementType = _stRefresh);
  SB.Append(ActualName);

  if not FDesignMode and FieldIsNull(FieldDesc, StatementType <> _stRefresh) then // Refresh generated with current field values
    SB.Append(' IS NULL')
  else begin
    SB.Append(' = ');
    AddParam(SB, FieldDesc, StatementType, _ptInput, Index,
      (StatementType <> _stRefresh) or
      (CachedUpdates and not InCacheProcessing)); // no need to use old value on refresh after update, insert
  end;
end;

procedure TDASQLGenerator.GenerateConditions(SB: StringBuilder;
  const StatementType: _TStatementType;
  const KeyAndDataFields: TKeyAndDataFields;
  const Index: integer = -1);
var
  i: integer;
begin
  if Length(KeyAndDataFields.KeyFieldDescs) > 0 then
    for i := 0 to High(KeyAndDataFields.KeyFieldDescs) do
      AddFieldToCondition(SB, KeyAndDataFields.KeyFieldDescs[i], StatementType, Index)
  else begin
    if FIRecordSet.IdentityField <> nil then
      AddFieldToCondition(SB, FIRecordSet.IdentityField, StatementType, Index)
    else begin
      if Length(KeyAndDataFields.DataFieldDescs) = 0 then
        FService.RaiseError(SNoKeyFields);

      for i := 0 to High(KeyAndDataFields.DataFieldDescs) do
        if not KeyAndDataFields.DataFieldDescs[i].IsBlob then
          AddFieldToCondition(SB, KeyAndDataFields.DataFieldDescs[i], StatementType, Index);

      if SB.Length = 0 then
        FService.RaiseError(SNoKeyFields);
    end;
  end;
end;

procedure TDASQLGenerator.AddFieldToInsertSQL(FieldDesc: TCRFieldDesc; const Index: integer = -1);
begin
  if FFldSB.Length > 0 then begin
    FFldSB.Append(', ');
    FFldParamSB.Append(', ');
  end;

  FFldSB.Append(FieldDesc.ActualNameQuoted(SQLInfo, QuoteNames));
  AddParam(FFldParamSB, FieldDesc, _stInsert, _ptInput, Index);
end;

procedure TDASQLGenerator.GenerateInsertSQL(
  const KeyAndDataFields: TKeyAndDataFields;
  const ModifiedFieldsOnly: boolean;
  const Index: integer = -1);
var
  i: integer;
  FieldDesc: TCRFieldDesc;
begin
  for i := 0 to High(KeyAndDataFields.DataFieldDescs) do begin
    FieldDesc := KeyAndDataFields.DataFieldDescs[i];
    // Insert all(!) field values
    if not ModifiedFieldsOnly or not FieldIsNull(FieldDesc, False) then
      AddFieldToInsertSQL(FieldDesc, Index);
  end;

  FHeaderSB.Append('INSERT INTO ');
  FHeaderSB.Append(SQLInfo.NormalizeName(FTableInfo.TableNameFull, QuoteNames));
  FHeaderSB.Append(SLLineSeparator);
  FHeaderSB.Append('  (');
  // Append FFldSB
  FMiddleSB.Append(')');
  FMiddleSB.Append(SLLineSeparator);
  FMiddleSB.Append('VALUES');
  FMiddleSB.Append(SLLineSeparator);
  FMiddleSB.Append('  (');
  // Append FFldParamSB
  FFooterSB.Append(')');
end;

procedure TDASQLGenerator.AddFieldToUpdateSQL(FieldDesc: TCRFieldDesc;
  const ModifiedFieldsOnly: boolean;
  const Index: integer = -1);
begin
  if FFldSB.Length > 0 then
    FFldSB.Append(', ');

  FFldSB.Append(FieldDesc.ActualNameQuoted(SQLInfo, QuoteNames));
  FFldSB.Append(' = ');
  AddParam(FFldSB, FieldDesc, _stUpdate, _ptInput, Index);
end;

procedure TDASQLGenerator.GenerateUpdateSQL(
  const KeyAndDataFields: TKeyAndDataFields;
  const ModifiedFieldsOnly: boolean;
  const Index: integer = -1);
var
  i: integer;
  FieldDesc: TCRFieldDesc;
begin
  for i := 0 to High(KeyAndDataFields.DataFieldDescs) do begin
    FieldDesc := KeyAndDataFields.DataFieldDescs[i];

    if not ModifiedFieldsOnly or FieldModified(FieldDesc) then
      AddFieldToUpdateSQL(FieldDesc, ModifiedFieldsOnly, Index);
  end;

  if FFldSB.Length > 0 then begin
    FHeaderSB.Append('UPDATE ');
    FHeaderSB.Append(SQLInfo.NormalizeName(FTableInfo.TableNameFull, QuoteNames));
    FHeaderSB.Append(SLLineSeparator);
    FHeaderSB.Append('SET');
    FHeaderSB.Append(SLLineSeparator);
    FHeaderSB.Append('  ');
    // Append FFldSB
    FMiddleSB.Append(SLLineSeparator);
    FMiddleSB.Append('WHERE');
    FMiddleSB.Append(SLLineSeparator);
    FMiddleSB.Append('  ');
    // Append FFldParamSB
    GenerateConditions(FCondSB, _stUpdate, KeyAndDataFields, Index);
  end;
end;

procedure TDASQLGenerator.GenerateDeleteSQL(
  const KeyAndDataFields: TKeyAndDataFields;
  const ModifiedFieldsOnly: boolean;
  const Index: integer = -1);
begin
  FHeaderSB.Append('DELETE FROM ');
  FHeaderSB.Append(SQLInfo.NormalizeName(FTableInfo.TableNameFull, QuoteNames));
  FHeaderSB.Append(SLLineSeparator);
  FHeaderSB.Append('WHERE');
  FHeaderSB.Append(SLLineSeparator);
  FHeaderSB.Append('  ');
  GenerateConditions(FCondSB, _stDelete, KeyAndDataFields, Index);
end;

procedure TDASQLGenerator.GenerateLockSQL(
  const KeyAndDataFields: TKeyAndDataFields;
  const Index: integer = -1);
begin

end;

procedure TDASQLGenerator.AddFieldToRefreshSQL(FieldDesc: TCRFieldDesc);
var
  IndexName: string;
begin
  if FFldSB.Length > 0 then
    FFldSB.Append(', ');

  FFldSB.Append(FieldDesc.ActualNameQuoted(SQLInfo, QuoteNames));

  if not FDesignMode then begin
    IndexName := GenerateIndexName(IntToStr(FIRecordSet.Fields.IndexOf(FieldDesc)));
    FFldSB.Append(' AS ' + IndexName);
    if Assigned(FieldDesc.Encryptor) then
      FRefreshSQLFields := FRefreshSQLFields + ';' + IndexName;
  end;
end;

procedure TDASQLGenerator.GenerateRefreshSQLSelectPart(const KeyAndDataFields: TKeyAndDataFields);
var
  i: integer;
  FieldArrHigh: integer;
  UseDataFields: boolean;
  FieldDesc: TCRFieldDesc;
begin
  FHeaderSB.Append('SELECT ');

  UseDataFields := Length(KeyAndDataFields.DataFieldDescs) > 0;
  if UseDataFields then
    FieldArrHigh := Length(KeyAndDataFields.DataFieldDescs) - 1
  else
    FieldArrHigh := High(KeyAndDataFields.KeyFieldDescs);

  // SELECT ... FROM .... {WITH NOLOCK}
  // Add SELECT section
  for i := 0 to FieldArrHigh do begin
    if UseDataFields then
      FieldDesc := KeyAndDataFields.DataFieldDescs[i]
    else
      FieldDesc := KeyAndDataFields.KeyFieldDescs[i];

    AddFieldToRefreshSQL(FieldDesc);
  end;
end;

procedure TDASQLGenerator.GenerateRefreshSQLFromPart;
begin
  FMiddleSB.Append(SQLInfo.NormalizeName(FTableInfo.TableNameFull, QuoteNames));
  if FTableInfo.TableAlias <> '' then begin
    FMiddleSB.Append(' ');
    FMiddleSB.Append(SQLInfo.NormalizeName(FTableInfo.TableAlias, QuoteNames));
  end;
end;

procedure TDASQLGenerator.GenerateRefreshSQL(const KeyAndDataFields: TKeyAndDataFields);
var
  SelectSQL: string;
begin
  GenerateConditions(FCondSB, _stRefresh, KeyAndDataFields);
  if FullRefresh or ReadOnly then begin
    if FCondSB.Length = 0 then begin
      SelectSQL := FService.FinalSQL;
      FHeaderSB.Append(SelectSQL);
    end
    else begin
      SelectSQL := FService.BaseSQL;
      FHeaderSB.Append(SQLAddWhere(SelectSQL, SLLineSeparator + '  ' + FCondSB.ToString));
      FCondSB.Length := 0; // WHERE clause already added to FHeaderSB
    end;
  end
  else begin
    GenerateRefreshSQLSelectPart(KeyAndDataFields);
    FMiddleSB.Append(' FROM ');
    GenerateRefreshSQLFromPart;
    FMiddleSB.Append(SLLineSeparator);
    FMiddleSB.Append('WHERE');
    FMiddleSB.Append(SLLineSeparator);
    FMiddleSB.Append('  ');
  end;
end;

procedure TDASQLGenerator.GenerateRefreshQuickSQL(const KeyAndDataFields: TKeyAndDataFields);
begin
  GenerateConditions(FCondSB, _stRefreshQuick, KeyAndDataFields);
  FHeaderSB.Append(SQLAddWhere(FService.FinalSQL, FCondSB.ToString));
  FCondSB.Length := 0; // WHERE clause already added to FHeaderSB
end;

procedure TDASQLGenerator.GenerateRefreshCheckDeletedSQL(const KeyAndDataFields: TKeyAndDataFields);
var
  i: integer;
  FieldDesc: TCRFieldDesc;
  ActualFieldName: string;
  Condition, FromClause: string;
  FinalSQL: string;
begin
  FHeaderSB.Append('SELECT ');

  for i := 0 to Length(KeyAndDataFields.KeyFieldDescs) - 1 do begin
    FieldDesc := KeyAndDataFields.KeyFieldDescs[i];
    if i > 0 then begin
      FFldSB.Append(', ');
      FFooterSB.Append(', ');
    end;
    ActualFieldName := GetActualFieldName(FieldDesc, True);
    FFldSB.Append(ActualFieldName);
    FFooterSB.Append(ActualFieldName);
    if not SameText(FieldDesc.ActualName, FieldDesc.Name) then
      FFldSB.Append(' AS ' + QuoteName(FieldDesc.Name));
  end;

  FinalSQL := FService.FinalSQL;
  FromClause := SQLGetFrom(FinalSQL);
  FMiddleSB.Append(' FROM ' + FromClause);
  Condition := SQLGetWhere(FinalSQL);
  if Condition <> '' then
    FMiddleSB.Append(' WHERE ' + Condition);
  FMiddleSB.Append(' ORDER BY ');
end;

function TDASQLGenerator.GenerateSQLforUpdTable(UpdatingTableInfo: TCRTableInfo;
  const KeyAndDataFields: TKeyAndDataFields;
  const StatementType: _TStatementType;
  const ModifiedFieldsOnly: boolean;
  const Index: integer = -1): string;
begin
  if UpdatingTableInfo.TableName = '' then
    FService.RaiseError(SBadTableInfoName);

  Clear;
  FParamsInfo.Clear;
  FTableInfo := UpdatingTableInfo;

  case StatementType of
    _stInsert:
      GenerateInsertSQL(KeyAndDataFields, ModifiedFieldsOnly, Index);
    _stUpdate:
      GenerateUpdateSQL(KeyAndDataFields, ModifiedFieldsOnly, Index);
    _stDelete:
      GenerateDeleteSQL(KeyAndDataFields, ModifiedFieldsOnly, Index);
    _stLock:
      GenerateLockSQL(KeyAndDataFields, Index);
    _stRefresh:
      GenerateRefreshSQL(KeyAndDataFields);
    _stRefreshQuick:
      GenerateRefreshQuickSQL(KeyAndDataFields);
    _stRefreshCheckDeleted:
      GenerateRefreshCheckDeletedSQL(KeyAndDataFields);
    _stRecCount:
      GenerateRecCountSQL(True);
  else
    FService.RaiseError(SBadStatementType);
  end;

  Result := AssembleSB();
end;

function TDASQLGenerator.GenerateSQL(const StatementType: _TStatementType;
  const ModifiedFieldsOnly: boolean;
  const Index: Integer = -1): string;
var
  KeyAndDataFields: TKeyAndDataFields;
  ForceUseAllFields: boolean;
begin
  Assert(FIRecordSet <> nil);
  if FIRecordSet.UpdatingTableInfoIdx = -1 then
    Result := ''
  else begin
    ForceUseAllFields := (StatementType = _stRefresh) and FullRefresh and
      (FIRecordSet.TablesInfo.Count <> 1);
    FIRecordSet.GetKeyAndDataFields(KeyAndDataFields, ForceUseAllFields);
    Result := GenerateSQLforUpdTable(FIRecordSet.UpdatingTableInfo,
      KeyAndDataFields, StatementType, ModifiedFieldsOnly, Index);
  end;
end;

function TDASQLGenerator.GenerateTableSQL(const TableName, OrderFields: string): string;
var
  AOrderFields: string;
begin
  Result := 'SELECT * FROM ' + SQLInfo.NormalizeName(TableName, QuoteNames) + SLLineSeparator;

  AOrderFields := Trim(OrderFields);
  if AOrderFields <> '' then
    Result := Result + 'ORDER BY ' + AOrderFields + SLLineSeparator;
end;

function TDASQLGenerator.GenerateRecCountSQL(UseBaseSQL: boolean = False): string;
var
  SelectSQL: string;
begin
  if UseBaseSQL then
    SelectSQL := FService.BaseSQL // for SQLGeneratorFrame
  else
    SelectSQL := FService.FinalSQL;
  if SelectSQL = '' then
    FService.RaiseError(SEmptySQLStatement);

  Clear;

  FHeaderSB.Append('SELECT COUNT(*) FROM (');
  FMiddleSB.Append(SelectSQL);
  FFooterSB.Append(')');

  Result := AssembleSB();
end;

function TDASQLGenerator.GenerateSelectValues(const ValuesList: string): string;
begin
  Result := 'SELECT ' + ValuesList;
end;

end.
