unit Aurelius.Sql.MSSQL;

{$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
  TMSSQLSQLGenerator = class(TAnsiSQLGenerator)
  private
    FUseBoolean: Boolean;
    FWorkaroundInsertTriggers: Boolean;
  protected
    function GetMaxConstraintNameLength: Integer; override;
    procedure DefineColumnType(Column: TColumnMetadata); override;
    function GetSqlLiteral(AValue: Variant; AType: TFieldType): string; override;
    function GetGeneratorName: string; override;
    function GetSqlDialect: string; override;
    function GetSupportedFeatures: TDBFeatures; override;

    function GenerateInsert(Command: TInsertCommand): string; override;
    function GenerateGetLastInsertId(SQLField: TSQLField): string; override;
    function GenerateDropField(Column: TColumnMetadata): string; override;
    function GenerateLimitedSelect(SelectSql: TSelectSql; Command: TSelectCommand): string; override;

    function GetSupportedFieldTypes: TFieldTypeSet; override;
    function GetNullableConstraint(NotNull: boolean): string; override;

    function GetOptimalGuidMode: TSequentialGuidMode; override;

    function OpenQuote: string; override;
    function CloseQuote: string; override;
  public
    constructor Create;
    property UseBoolean: Boolean read FUseBoolean write FUseBoolean;
    property WorkaroundInsertTriggers: Boolean read FWorkaroundInsertTriggers write FWorkaroundInsertTriggers;
  end;

implementation

uses
  Aurelius.Sql.Functions,
  Variants, SysUtils;

{ TMSSQLSQLGenerator }

function TMSSQLSQLGenerator.CloseQuote: string;
begin
  Result := ']';
end;

constructor TMSSQLSQLGenerator.Create;
begin
  inherited Create;
  WorkaroundInsertTriggers := True;
  RegisterFunction('concat', TFormatSQLFunction.Create('(%0:s + %1:s)', TypeInfo(string)));
  RegisterFunction('contains', TFormatSQLFunction.Create('(%0:s) LIKE (''%%'' + (%1:s) + ''%%'')', TypeInfo(boolean)));
  RegisterFunction('startswith', TFormatSQLFunction.Create('(%0:s) LIKE ((%1:s) + ''%%'')', TypeInfo(boolean)));
  RegisterFunction('endswith', TFormatSQLFunction.Create('(%0:s) LIKE (''%%'' + (%1:s))', TypeInfo(boolean)));

  RegisterFunction('year', TFormatSQLFunction.Create('datepart(year, %0:s)', TypeInfo(integer)));
  RegisterFunction('month', TFormatSQLFunction.Create('datepart(month, %0:s)', TypeInfo(integer)));
  RegisterFunction('day', TFormatSQLFunction.Create('datepart(day, %0:s)', TypeInfo(integer)));
  RegisterFunction('hour', TFormatSQLFunction.Create('datepart(hour, %0:s)', TypeInfo(integer)));
  RegisterFunction('minute', TFormatSQLFunction.Create('datepart(minute, %0:s)', TypeInfo(integer)));
  RegisterFunction('second', TFormatSQLFunction.Create('datepart(second, %0:s)', TypeInfo(integer)));

  RegisterFunction('length', TSimpleSQLFunction.Create('len', TypeInfo(integer)));
  RegisterFunction('substring', TSimpleSQLFunction.Create('substring', TypeInfo(string)));
  RegisterFunction('position', TSimpleSQLFunction.Create('charindex', TypeInfo(integer)));
  RegisterFunction('bytelength', TSimpleSQLFunction.Create('datalength', TypeInfo(integer)));
end;

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

  case Column.FieldType of
    ftByte:
      Column.DataType := 'TINYINT';
    ftShortint:
      Column.DataType := 'TINYINT';
    ftLargeint:
      Column.DataType := 'BIGINT';
    ftInteger:
      Column.DataType := 'INT';
    ftLongWord:
      Column.DataType := 'INT';

    ftDate:
      Column.DataType := 'DATETIME';
    ftTime:
      Column.DataType := 'TIME';
    ftDateTime:
      Column.DataType := 'DATETIME';

    ftFloat:
      Column.DataType := 'FLOAT';
    ftSingle:
      Column.DataType := 'REAL';
    ftExtended:
      Column.DataType := 'FLOAT';

    ftMemo:
      Column.DataType := 'VARCHAR(MAX)';
    ftWideMemo:
      Column.DataType := 'NVARCHAR(MAX)';
    ftBlob:
      Column.DataType := 'VARBINARY(MAX)';
    ftGuid:
      Column.DataType := 'UNIQUEIDENTIFIER';
    ftBoolean:
      Column.DataType := 'BIT';
  else
    inherited DefineColumnType(Column);
  end;

  if Column.AutoGenerated then
    Column.DataType := Column.DataType + ' IDENTITY(1,1)';
end;

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

function TMSSQLSQLGenerator.GenerateGetLastInsertId(SQLField: TSQLField): string;
begin
  Result := 'SELECT SCOPE_IDENTITY()';
//  Result := 'SELECT CAST(IDENT_CURRENT('+QuotedStr(SQLField.Table.Name)+') AS INT)';
end;

function TMSSQLSQLGenerator.GenerateInsert(Command: TInsertCommand): string;
const
  OutputTableVar = '@AureliusOutputTbl';
begin
  Result := inherited GenerateInsert(Command);
  if Command.OutputColumn <> '' then
    if WorkaroundInsertTriggers then
    begin
      Insert(
        Format('OUTPUT INSERTED.%s INTO %s' + sqlLineBreak, [Command.OutputColumn, OutputTableVar]),
        Result,
        Pos(' VALUES (', Result)
      );

      Result :=
        'SET NOCOUNT ON; ' + sqlLineBreak
        + Format('DECLARE %s TABLE (ID INT); ', [OutputTableVar]) + sqlLineBreak
        + Result + ';' + sqlLineBreak
        + 'SET NOCOUNT OFF;' + sqlLineBreak
        + Format('SELECT ID From %s;', [OutputTableVar]);
    end
    else
    begin
      Insert(
        Format('OUTPUT INSERTED.%s ' + sqlLineBreak, [Command.OutputColumn]),
        Result,
        Pos('VALUES (', Result)
      );
    end;
end;

function TMSSQLSQLGenerator.GenerateLimitedSelect(SelectSql: TSelectSql;
  Command: TSelectCommand): string;

  function GenerateLimitedSelectWithOffset: string;
  var
    RowNum: string;
    p: integer;
    SubSql: string;
    RowCondition: string;
  begin
    // Build the rownum column
    if SelectSql.OrderClause <> '' then
      RowNum := Format('ROW_NUMBER() OVER(%s) AS rownum, ', [SelectSql.OrderClause])
    else
      RowNum := 'ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) AS rownum, ';;

    // 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);
    SelectSql.OrderClause := '';
    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 GenerateLimitedSelectWithTop: string;
  var
    p: integer;
    TopStr: string;
  begin
    Result := GenerateRegularSelect(SelectSql);
    p := Pos('SELECT', UpperCase(Result));
    Assert(p > 0, 'SELECT word not found in SQL. Cannot generate limited select');
    p := p + Length('SELECT');

    TopStr := TopStr + Format('TOP %d ', [Command.MaxRows]);
    Result := 'SELECT ' + TopStr + Copy(Result, p, MaxInt);
  end;

begin
  if Command.HasFirstRow then
    Result := GenerateLimitedSelectWithOffset
  else
    Result := GenerateLimitedSelectWithTop;
end;

function TMSSQLSQLGenerator.GetSqlDialect: string;
begin
  Result := 'MSSQL';
end;

function TMSSQLSQLGenerator.GetGeneratorName: string;
begin
  Result := 'MSSQL SQL Generator';
end;

function TMSSQLSQLGenerator.GetMaxConstraintNameLength: Integer;
begin
  Result := 128; //MSSQL 2008
end;

function TMSSQLSQLGenerator.GetNullableConstraint(NotNull: boolean): string;
begin
  if NotNull then
    Result := inherited GetNullableConstraint(NotNull)
  else
    Result := ' NULL';
end;

function TMSSQLSQLGenerator.GetOptimalGuidMode: TSequentialGuidMode;
begin
  Result := TSequentialGuidMode.SequentialAtEnd;
end;

function TMSSQLSQLGenerator.GetSqlLiteral(AValue: Variant;
  AType: TFieldType): string;
begin
  Result := inherited GetSqlLiteral(AValue, AType);
  case AType of
    ftDate:
      Result := Format('CAST(''%s'' AS DATETIME)',
        [FormatDateTime('yyyy-mm-dd ', VarToDateTime(AValue))]);
    ftTime:
      Result := Format('CAST(''%s'' AS DATETIME)',
        [FormatDateTime('hh:nn:ss', VarToDateTime(AValue))]);
    ftDateTime,ftTimeStamp:
      Result := Format('CAST(''%s'' AS DATETIME)',
        [FormatDateTime('yyyy-mm-dd hh:nn:ss', VarToDateTime(AValue))]);
  end;
end;

function TMSSQLSQLGenerator.GetSupportedFeatures: TDBFeatures;
begin
  Result := AllDBFeatures - [TDBFeature.Sequences];
end;

function TMSSQLSQLGenerator.GetSupportedFieldTypes: TFieldTypeSet;
begin
  Result := inherited GetSupportedFieldTypes + [ftGuid];
  if UseBoolean then
    Result := Result + [ftBoolean];
end;

function TMSSQLSQLGenerator.OpenQuote: string;
begin
  Result := '[';
end;

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

end.
