unit Aurelius.Schema.MySQL;

{$I Aurelius.Inc}

interface

uses
  Aurelius.Drivers.Interfaces,
  Aurelius.Schema.AbstractImporter,
  Aurelius.Sql.Metadata;

type
  TMySQLSchemaImporter = class(TAbstractSchemaImporter)
  strict protected
    procedure GetDatabaseMetadata(Connection: IDBConnection; Database: TDatabaseMetadata); override;
  end;

  TMySQLSchemaRetriever = class(TSchemaRetriever)
  strict private
    procedure GetTables;
    procedure GetColumns;
    procedure GetPrimaryKeys;
    procedure GetUniqueKeys;
    procedure GetDBIndexes;
    procedure GetForeignKeys;
    procedure GetFieldDefinition(Column: TColumnMetadata; ADataType: string;
      ASize, APrecision, AScale: integer);
  public
    constructor Create(AConnection: IDBConnection; ADatabase: TDatabaseMetadata); override;
    procedure RetrieveDatabase; override;
  end;

implementation

uses
  SysUtils,
  Aurelius.Schema.Register;

{ TMySQLSchemaImporter }

procedure TMySQLSchemaImporter.GetDatabaseMetadata(Connection: IDBConnection;
  Database: TDatabaseMetadata);
var
  Retriever: TSchemaRetriever;
begin
  Retriever := TMySQLSchemaRetriever.Create(Connection, Database);
  try
    Retriever.RetrieveDatabase;
  finally
    Retriever.Free;
  end;
end;

{ TMySQLSchemaRetriever }

constructor TMySQLSchemaRetriever.Create(AConnection: IDBConnection;
  ADatabase: TDatabaseMetadata);
begin
  inherited Create(AConnection, ADatabase);
end;

procedure TMySQLSchemaRetriever.GetColumns;
begin
  RetrieveColumns(
    'SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, '+
    'NUMERIC_PRECISION, NUMERIC_SCALE, IS_NULLABLE, EXTRA '+
    'FROM INFORMATION_SCHEMA.COLUMNS '+
    'WHERE TABLE_SCHEMA = DATABASE() '+
    'ORDER BY TABLE_NAME, ORDINAL_POSITION',
    procedure (Column: TColumnMetadata; ResultSet: IDBResultSet)
    begin
      Column.NotNull := (AsString(ResultSet.GetFieldValue('IS_NULLABLE')) = 'NO');
      GetFieldDefinition(Column,
        AsString(ResultSet.GetFieldValue('DATA_TYPE')),
        AsInteger(ResultSet.GetFieldValue('CHARACTER_MAXIMUM_LENGTH')),
        AsInteger(ResultSet.GetFieldValue('NUMERIC_PRECISION')),
        AsInteger(ResultSet.GetFieldValue('NUMERIC_SCALE'))
      );
      Column.AutoGenerated := (AsString(ResultSet.GetFieldValue('EXTRA')) = 'auto_increment');
    end
  );
end;

procedure TMySQLSchemaRetriever.GetDBIndexes;
begin
  RetrieveDBIndexes(
    'SELECT S.TABLE_NAME, S.INDEX_NAME, S.COLUMN_NAME '+
    'FROM INFORMATION_SCHEMA.STATISTICS S, INFORMATION_SCHEMA.COLUMNS C '+
    'WHERE S.INDEX_NAME<>''PRIMARY'' AND S.TABLE_NAME=C.TABLE_NAME '+
    'AND S.COLUMN_NAME=C.COLUMN_NAME AND (C.COLUMN_KEY <> ''PRI'') '+
    'AND C.TABLE_SCHEMA = DATABASE() AND S.INDEX_SCHEMA = DATABASE() ' +
    'AND (S.NON_UNIQUE <> 0) OR (S.NON_UNIQUE IS NULL) '+
    'ORDER BY S.TABLE_NAME, S.INDEX_NAME, S.SEQ_IN_INDEX'
  );
end;

procedure TMySQLSchemaRetriever.GetFieldDefinition(Column: TColumnMetadata;
  ADataType: string; ASize, APrecision, AScale: integer);

const
  vNoSizeTypes : array[0..21] of string =
     ( 'bigint', 'bit', 'blob', 'date',  'datetime', 'double precision', 'float', 'int', 'longblob',
       'longtext', 'mediumblob', 'mediumint', 'mediumtext', 'real', 'smallint', 'text', 'time',
       'timestamp', 'tinyblob', 'tinyint', 'tinytext', 'year' );

  function NeedSize: Boolean;
  var
    I: Integer;
  begin
    for i := 0 to high(vNoSizeTypes) do
      if vNoSizeTypes[I] = ADataType then
        Exit(false);
    Result := true;
  end;

begin
  ADataType := Trim(LowerCase(ADataType));
  if ADataType =  'double' then
  begin
    Column.DataType := 'DOUBLE PRECISION';
    Exit;
  end;

  Column.DataType := ADataType;
  if NeedSize then
  begin
    if (ADataType = 'binary') or (ADataType = 'char') or (ADataType = 'varbinary') or (ADataType = 'varchar') then
      Column.Length := ASize
    else
    begin
      Column.Precision := APrecision;
      Column.Scale := AScale;
    end;
  end;
end;

procedure TMySQLSchemaRetriever.GetForeignKeys;
begin
  RetrieveForeignKeys(
    'SELECT R.CONSTRAINT_NAME, R.TABLE_NAME AS FK_TABLE_NAME, R.REFERENCED_TABLE_NAME AS PK_TABLE_NAME, '+
    'KC.COLUMN_NAME AS FK_COLUMN_NAME, KC.REFERENCED_COLUMN_NAME AS PK_COLUMN_NAME '+
    'FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KC, INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R '+
    'WHERE R.CONSTRAINT_NAME = KC.CONSTRAINT_NAME AND R.TABLE_NAME = KC.TABLE_NAME '+
    'AND KC.CONSTRAINT_SCHEMA = DATABASE() AND R.CONSTRAINT_SCHEMA = DATABASE() '+
    'ORDER BY R.CONSTRAINT_NAME, KC.POSITION_IN_UNIQUE_CONSTRAINT'
  );
end;

procedure TMySQLSchemaRetriever.GetPrimaryKeys;
begin
  RetrievePrimaryKeys(
    'SELECT C.CONSTRAINT_NAME, C.TABLE_NAME, K.COLUMN_NAME, K.ORDINAL_POSITION '+
    'FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS C, INFORMATION_SCHEMA.KEY_COLUMN_USAGE K '+
    'WHERE C.CONSTRAINT_TYPE=''PRIMARY KEY'' '+
    'AND C.TABLE_NAME = K.TABLE_NAME AND C.CONSTRAINT_NAME = K.CONSTRAINT_NAME '+
    'AND C.CONSTRAINT_SCHEMA = DATABASE() AND K.CONSTRAINT_SCHEMA = DATABASE() '+
    'ORDER BY C.TABLE_NAME, C.CONSTRAINT_NAME, K.ORDINAL_POSITION'
  );
end;

procedure TMySQLSchemaRetriever.GetTables;
begin
  RetrieveTables(
    'SELECT TABLE_NAME '+
    'FROM INFORMATION_SCHEMA.TABLES '+
    'WHERE TABLE_TYPE=''BASE TABLE'' '+
    'AND TABLE_SCHEMA = DATABASE() '+
    'ORDER BY TABLE_NAME'
  );
end;

procedure TMySQLSchemaRetriever.GetUniqueKeys;
begin
  RetrieveUniqueKeys(
    'SELECT C.CONSTRAINT_NAME, C.TABLE_NAME, K.COLUMN_NAME, K.ORDINAL_POSITION '+
    'FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS C, INFORMATION_SCHEMA.KEY_COLUMN_USAGE K '+
    'WHERE C.CONSTRAINT_TYPE=''UNIQUE'' '+
    'AND C.TABLE_NAME = K.TABLE_NAME AND C.CONSTRAINT_NAME = K.CONSTRAINT_NAME '+
    'AND C.CONSTRAINT_SCHEMA = DATABASE() AND K.CONSTRAINT_SCHEMA = DATABASE() '+
    'ORDER BY C.TABLE_NAME, C.CONSTRAINT_NAME, K.ORDINAL_POSITION'
  );
end;

procedure TMySQLSchemaRetriever.RetrieveDatabase;
begin
  Database.Clear;
  GetTables;
  GetColumns;
  GetPrimaryKeys;
  GetUniqueKeys;
  GetDBIndexes;
  GetForeignKeys;
end;

initialization
  TSchemaImporterRegister.GetInstance.RegisterImporter('MySQL', TMySQLSchemaImporter.Create);

end.

