unit Aurelius.Schema.ElevateDB;

{$I Aurelius.Inc}

interface

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

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

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

implementation

uses
  SysUtils,
  Aurelius.Schema.Register;

{ TElevateDBSchemaImporter }

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

{ TElevateDBSchemaRetriever }

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

procedure TElevateDBSchemaRetriever.GetColumns;
begin
  RetrieveColumns(
      'SELECT TableName as TABLE_NAME, Name as COLUMN_NAME, "Type", "Length", '+
      'Precision, Scale, Nullable, Generated, Identity '+
      'FROM Information.TableColumns '+
      'ORDER BY TableName, OrdinalPos',
    procedure (Column: TColumnMetadata; ResultSet: IDBResultSet)
    begin
      Column.NotNull := not AsBoolean(ResultSet.GetFieldValue('Nullable'));
      GetFieldDefinition(Column,
        AsString(ResultSet.GetFieldValue('Type')),
        AsInteger(ResultSet.GetFieldValue('Length')),
        AsInteger(ResultSet.GetFieldValue('Precision')),
        AsInteger(ResultSet.GetFieldValue('Scale'))
      );
      Column.AutoGenerated := AsBoolean(ResultSet.GetFieldValue('Generated')) and AsBoolean(ResultSet.GetFieldValue('Identity'));
    end
  );
end;

procedure TElevateDBSchemaRetriever.GetDBIndexes;
begin
  RetrieveDBIndexes(
    'SELECT I.TableName as TABLE_NAME, I.Name as INDEX_NAME, IC.ColumnName as COLUMN_NAME '+
    'FROM Information.Indexes I, Information.IndexColumns IC '+
    'WHERE I.TableName = IC.TableName AND I.Name = IC.IndexName '+
    'AND (I.Type = ''Index'') '+
    'ORDER BY I.TableName, I.Name, IC.OrdinalPos'
  );
end;

procedure TElevateDBSchemaRetriever.GetFieldDefinition(Column: TColumnMetadata;
  ADataType: string; ALength, APrecision, AScale: integer);
begin
  Column.DataType := ADataType;
  ADataType := LowerCase(ADataType);

  if (ADataType = 'byte') or (ADataType = 'char') or (ADataType = 'varbyte') or (ADataType = 'varchar') then
    Column.Length := ALength
  else
  if (ADataType = 'decimal') or (ADataType = 'numeric') then
  begin
    Column.DataType := 'NUMERIC';
    Column.Precision := APrecision;
    Column.Scale := AScale;
  end;
end;

procedure TElevateDBSchemaRetriever.GetForeignKeys;
begin
  RetrieveForeignKeys(
    'SELECT C.TableName as FK_TABLE_NAME, C.Name as CONSTRAINT_NAME, C.TargetTable as PK_TABLE_NAME, '+
    'CC.ColumnName AS FK_COLUMN_NAME, CP.ColumnName AS PK_COLUMN_NAME '+
    'FROM Information.Constraints C INNER JOIN Information.ConstraintColumns CC '+
    '   ON C.TableName = CC.TableName AND C.Name = CC.ConstraintName '+
    ' INNER JOIN Information.ConstraintColumns CP '+
    '   ON C.TargetTable = CP.TableName AND C.TargetTableConstraint = CP.ConstraintName '+
    'AND CC.OrdinalPos = CP.OrdinalPos '+
    'WHERE C.Type = ''Foreign Key'' '+
    'ORDER BY C.TableName, C.Name, CC.OrdinalPos, CP.OrdinalPos'
  );
end;

procedure TElevateDBSchemaRetriever.GetPrimaryKeys;
begin
  RetrievePrimaryKeys(
    'SELECT I.TableName as TABLE_NAME, C.ColumnName as COLUMN_NAME '+
    'FROM Information.Indexes I, Information.IndexColumns C '+
    'WHERE I.TableName = C.TableName '+
    'AND I.Name = C.IndexName '+
    'AND I.Type = ''Primary Key'' '+
    'ORDER BY I.TableName, I.Name, C.OrdinalPos'
  );
end;

procedure TElevateDBSchemaRetriever.GetTables;
begin
  RetrieveTables(
    'SELECT Name as TABLE_NAME FROM Information.Tables ORDER BY Name'
  );
end;

procedure TElevateDBSchemaRetriever.GetUniqueKeys;
begin
  RetrieveUniqueKeys(
    'SELECT C.TableName as TABLE_NAME, C.Name as CONSTRAINT_NAME, CC.ColumnName as COLUMN_NAME '+
    'FROM Information.Constraints C INNER JOIN Information.ConstraintColumns CC '+
    'ON C.TableName = CC.TableName AND C.Name = CC.ConstraintName '+
    'WHERE (C.Type = ''Unique'') '+
    'ORDER BY C.TableName, C.Name, CC.OrdinalPos'
  );
end;

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

initialization
  TSchemaImporterRegister.GetInstance.RegisterImporter('ElevateDB', TElevateDBSchemaImporter.Create);

end.

