unit Aurelius.Sql.MySQL;

{$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
  TMySQLSQLGenerator = 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 GenerateDropUniqueKey(UniqueKey: TUniqueKeyMetadata): string; override;
    function GenerateDropForeignKey(ForeignKey: TForeignKeyMetadata): string; override;
    function GenerateLimitedSelect(SelectSql: TSelectSql; Command: TSelectCommand): string; override;
    function GenerateDropDBIndex(DBIndex: TDBIndexMetadata): string; override;

    function GetSupportedFeatures: TDBFeatures; override;

    function OpenQuote: string; override;
    function CloseQuote: string; override;
  public
    constructor Create;
  end;

implementation

uses
  SysUtils,
  Aurelius.Sql.Functions;

{ TMySQLSQLGenerator }

function TMySQLSQLGenerator.CloseQuote: string;
begin
  Result := '`';
end;

constructor TMySQLSQLGenerator.Create;
begin
  inherited Create;
  RegisterFunction('concat', TSimpleSQLFunction.Create('concat', TypeInfo(string)));
  RegisterFunction('contains', TFormatSQLFunction.Create('(%0:s) LIKE concat(''%%'', %1:s, ''%%'')', TypeInfo(boolean)));
  RegisterFunction('startswith', TFormatSQLFunction.Create('(%0:s) LIKE concat(%1:s, ''%%'')', TypeInfo(boolean)));
  RegisterFunction('endswith', TFormatSQLFunction.Create('(%0:s) LIKE concat(''%%'', %1:s)', TypeInfo(boolean)));
end;

procedure TMySQLSQLGenerator.DefineColumnType(Column: TColumnMetadata);
begin
  DefineNumericColumnType(Column);
  if Column.DataType <> '' then
  begin
    Column.DataType := StringReplace(Column.DataType, 'NUMERIC', 'DECIMAL', [rfIgnoreCase]);
    Exit;
  end;

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

    ftWideString:
      Column.DataType := 'VARCHAR($len)';
    ftFixedWideChar:
      Column.DataType := 'CHAR($len)';

    ftDateTime:
      Column.DataType := 'DATETIME';

    ftSingle:
      Column.DataType := 'FLOAT';

    ftCurrency:
      begin
        Column.DataType := 'DECIMAL($pre, $sca)';
        Column.Precision := 20;
        Column.Scale := 4;
      end;

    ftBoolean:
      Column.DataType := 'BIT';

    ftMemo:
      Column.DataType := 'LONGTEXT';
    ftWideMemo:
      Column.DataType := 'LONGTEXT';
    ftBlob:
      Column.DataType := 'LONGBLOB';
  else
    inherited DefineColumnType(Column);
  end;

  if Column.AutoGenerated then
    Column.DataType := Column.DataType + ' AUTO_INCREMENT';
end;

function TMySQLSQLGenerator.GenerateDropDBIndex(DBIndex: TDBIndexMetadata): string;
begin
  Result := 'DROP INDEX ' + DBIndex.Name + ' ON ' +
    GetFullTablename(DBIndex.Table.Name, DBIndex.Table.Schema);
end;

function TMySQLSQLGenerator.GenerateDropForeignKey(ForeignKey: TForeignKeyMetadata): string;
begin
  Result := 'ALTER TABLE ';
  Result := Result + GetFullTableName(ForeignKey.FromTable.Name, ForeignKey.FromTable.Schema) + sqlLineBreak + '  DROP FOREIGN KEY ' +
    IdName(ForeignKey.Name);
end;

function TMySQLSQLGenerator.GenerateDropUniqueKey(UniqueKey: TUniqueKeyMetadata): string;
begin
  Result := 'ALTER TABLE ';

  Result := Result + GetFullTableName(UniqueKey.Table.Name, UniqueKey.Table.Schema) + sqlLineBreak + '  DROP INDEX ' +
    IdName(UniqueKey.Name) + ';';
end;

function TMySQLSQLGenerator.GenerateGetLastInsertId(SQLField: TSQLField): string;
begin
  Result := 'SELECT LAST_INSERT_ID();';
end;

function TMySQLSQLGenerator.GenerateLimitedSelect(SelectSql: TSelectSql;
  Command: TSelectCommand): string;
var
  MaxRows: integer;
begin
  Result := GenerateRegularSelect(SelectSql) + sqlLineBreak;

  // MaxRows must be present in SQL statement no matter what
  if not Command.HasMaxRows then
    MaxRows := MaxInt
  else
    MaxRows := Command.MaxRows;

  if Command.HasFirstRow then
    Result := Result + Format('LIMIT %d OFFSET %d', [MaxRows, Command.FirstRow])
  else
    Result := Result + Format('LIMIT %d', [MaxRows]);
end;

function TMySQLSQLGenerator.GetSqlDialect: string;
begin
  Result := 'MySQL';
end;

function TMySQLSQLGenerator.GetGeneratorName: string;
begin
  Result := 'MySQL SQL Generator';
end;

function TMySQLSQLGenerator.GetMaxConstraintNameLength: Integer;
begin
  Result := 64;
end;

function TMySQLSQLGenerator.GetSupportedFeatures: TDBFeatures;
begin
  Result := AllDBFeatures - [TDBFeature.Sequences, TDBFeature.RetrieveIdOnInsert, TDBFeature.Schemas];
end;

function TMySQLSQLGenerator.OpenQuote: string;
begin
  Result := '`';
end;

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

end.
