unit Aurelius.Sql.DB2;

{$I Aurelius.inc}

interface

uses
  DB,
  Aurelius.Sql.AnsiSqlGenerator,
  Aurelius.Sql.BaseTypes,
  Aurelius.Sql.Commands,
  Aurelius.Sql.Interfaces,
  Aurelius.Sql.Metadata,
  Aurelius.Sql.Register;

type
  TDB2SQLGenerator = class(TAnsiSQLGenerator)
  protected
    function GetMaxConstraintNameLength: Integer; override;
    procedure DefineColumnType(Column: TColumnMetadata); override;

    function GetGeneratorName: string; override;
    function GetSqlDialect: string; override;

    function GenerateGetLastInsertId(SQLField: TSQLField): string; override;

    function GenerateGetNextSequenceValue(Command: TGetNextSequenceValueCommand): string; override;
    function GenerateCreateSequence(Sequence: TSequenceMetadata): string; override;
    function GenerateDropSequence(Sequence: TSequenceMetadata): string; override;
    function GenerateDropField(Column: TColumnMetadata): string; override;
    function GenerateLimitedSelect(SelectSql: TSelectSql; Command: TSelectCommand): string; override;

    function GetSupportedFeatures: TDBFeatures; override;
  public
    constructor Create;
  end;

implementation

uses
  Variants, SysUtils,
  Aurelius.Sql.Functions;

{ TDB2SQLGenerator }

constructor TDB2SQLGenerator.Create;
begin
  inherited Create;
//  RegisterFunction('length', TSimpleSQLFunction.Create('length', TypeInfo(integer)));
  RegisterFunction('length', TFormatSQLFunction.Create('character_length(%0:s, codeunits32)', TypeInfo(integer)));
  RegisterFunction('substring', TSimpleSQLFunction.Create('substr', TypeInfo(string)));
  RegisterFunction('position', TSimpleSQLFunction.Create('locate', TypeInfo(integer)));
end;

procedure TDB2SQLGenerator.DefineColumnType(Column: TColumnMetadata);
begin
  DefineNumericColumnType(Column);
  if Column.DataType <> '' then
    Exit;

  case Column.FieldType of
    ftWideString:
      Column.DataType := 'VARGRAPHIC($len)';
    ftFixedWideChar:
      Column.DataType := 'GRAPHIC($len)';
    ftLargeint:
      Column.DataType := 'BIGINT';
    ftMemo:
      Column.DataType := 'CLOB(1G)';
    ftWideMemo:
      Column.DataType := 'DBCLOB(1G)';
    ftBlob:
      Column.DataType := 'BLOB(1G)';
  else
    inherited DefineColumnType(Column);
  end;

  if Column.AutoGenerated then
    Column.DataType := Column.DataType + ' GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1)';
end;

function TDB2SQLGenerator.GenerateCreateSequence(Sequence: TSequenceMetadata): string;
begin
  Result := Format('CREATE SEQUENCE %s AS INTEGER START WITH %s INCREMENT BY %s;',
    [IdName(Sequence.Name), IntToStr(Sequence.InitialValue), IntToStr(Sequence.Increment)]);
end;

function TDB2SQLGenerator.GenerateDropField(Column: TColumnMetadata): string;
begin
  result := InternalGenerateDropField(Column, True);
end;

function TDB2SQLGenerator.GenerateDropSequence(Sequence: TSequenceMetadata): string;
begin
  Result := Format('DROP SEQUENCE %s;', [IdName(Sequence.Name)]);
end;

function TDB2SQLGenerator.GenerateGetLastInsertId(SQLField: TSQLField): string;
begin
  Result := 'SELECT INTEGER(IDENTITY_VAL_LOCAL()) FROM SYSIBM.SYSDUMMY1;';
//  Result := 'SELECT CAST(IDENT_CURRENT('+QuotedStr(SQLTable.Name)+') AS INT)';
end;

function TDB2SQLGenerator.GenerateGetNextSequenceValue(Command: TGetNextSequenceValueCommand): string;
begin
  Result := Format('SELECT INTEGER(NEXT VALUE FOR %s) FROM SYSIBM.SYSDUMMY1;', [IdName(Command.SequenceName)]);
end;

function TDB2SQLGenerator.GenerateLimitedSelect(SelectSql: TSelectSql;
  Command: TSelectCommand): string;
var
  RowNum: string;
  p: integer;
  SubSql: string;
  RowCondition: string;
begin
  // Build the rownum column
  RowNum := Format('ROW_NUMBER() OVER (%s) AS rownum, ', [SelectSql.OrderClause]);

  // Insert the rownum column in the select statement
  p := Pos('SELECT', UpperCase(SelectSql.SelectClause));
  Assert(p > 0, 'SELECT word not found in SQL. Cannot generate limited select');
  p := p + Length('SELECT');

  // Build the regular sql with the new added rownum column and without order clause
  SelectSql.SelectClause := 'SELECT ' + RowNum + Copy(SelectSql.SelectClause, p, MaxInt);
  SubSql := GenerateRegularSelect(SelectSql);

  // Build the filter condition for rows to be retrieved
  if Command.HasFirstRow and Command.HasMaxRows then
    RowCondition := Format('rownum BETWEEN %d AND %d', [Command.FirstRow + 1, Command.LastRow + 1])
  else
  if Command.HasFirstRow then
    RowCondition := Format('rownum >= %d', [Command.FirstRow + 1])
  else
    RowCondition := Format('rownum <= %d', [Command.LastRow + 1]);

  Result := 'SELECT * FROM ( ' +
    SubSql + ') as tempset ' +
    'WHERE ' + RowCondition;
end;

function TDB2SQLGenerator.GetSqlDialect: string;
begin
  Result := 'DB2';
end;

function TDB2SQLGenerator.GetGeneratorName: string;
begin
  Result := 'DB2 SQL Generator';
end;

function TDB2SQLGenerator.GetMaxConstraintNameLength: Integer;
begin
  Result := 128;
end;

function TDB2SQLGenerator.GetSupportedFeatures: TDBFeatures;
begin
  Result := AllDBFeatures - [TDBFeature.RetrieveIdOnInsert];
end;

initialization
  TSQLGeneratorRegister.GetInstance.RegisterGenerator(TDB2SQLGenerator.Create);

end.
