source: branches/test1/Client/Forms/UFormImportStructure.pas

unit UFormImportStructure;

{$mode delphi}

interface

uses
  Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls,
  EditBtn, USystem, SpecializedList, USqlDatabase, SpecializedDictionary;

type

  { TSQLTable }

  TSQLTable = class
    Table: TDictionaryStringString;
    Columns: TDbRows;
    function ColumnByName(Name: string): Integer;
    constructor Create;
    destructor Destroy; override;
  end;

  { TImportStructureForm }

  TImportStructureForm = class(TForm)
    Button1: TButton;
    EditSchema: TEdit;
    EditHost: TEdit;
    EditUser: TEdit;
    EditPassword: TEdit;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Memo1: TMemo;
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure FormShow(Sender: TObject);
  private
    function ObjectIdByName(Name: string): Integer;
    { private declarations }
  public
    SQLTables: TListObject; // TListObject<TSQLTable>
  end; 

var
  ImportStructureForm: TImportStructureForm;

implementation

uses
  UCore, UFormMain, UFormMenu;

{ TSQLTable }

function TSQLTable.ColumnByName(Name: string): Integer;
var
  I: Integer;
begin
  I := 0;
  while (I < Columns.Count) and (Columns[I].Values['COLUMN_NAME'] <> Name) do Inc(I);
  if I < Columns.Count then Result := I
    else Result := -1;
end;

constructor TSQLTable.Create;
begin
  Columns := TDbRows.Create;
  Table := TDictionaryStringString.Create;
end;

destructor TSQLTable.Destroy;
begin
  Columns.Free;
  Table.Free;
  inherited Destroy;
end;

{$R *.lfm}

{ TImportStructureForm }

function TImportStructureForm.ObjectIdByName(Name: string): Integer;
var
  I: Integer;
begin
  I := 0;
  while (I < SQLTables.Count) and (TSQLTable(SQLTables[I]).Table.Items[0].Value <> Name) do Inc(I);
  if I < SQLTables.Count then Result := I
    else Result := -1;
end;

procedure TImportStructureForm.FormShow(Sender: TObject);
begin
  EditHost.Text := Core.System.Client.Host;
  EditUser.Text := Core.System.Client.User;
  EditSchema.Text := Core.System.Client.Schema;
end;

procedure TImportStructureForm.Button1Click(Sender: TObject);
var
  Database: TSqlDatabase;
  DbRows: TDbRows;
  DbRows2: TDbRows;
  T, C: Integer;
  TableName: string;
  PropertyName: string;
  PropType: string;
  GroupId: Integer;
  ObjectId: Integer;
  PropertyId: Integer;
  NewTable: TSQLTable;
  RefObjectIndex: Integer;
  RefPropertyId: Integer;
begin
  try
    Database := TSqlDatabase.Create(nil);
    DbRows := TDbRows.Create;
    DbRows2 := TDbRows.Create;
    Database.HostName := EditHost.Text;
    Database.Database := EditSchema.Text;
    Database.UserName := EditUser.Text;
    Database.Password := EditPassword.Text;
    Database.Connect;

    SQLTables.Clear;
    GroupId := Core.System.AddMenu(Database.Database, 0);
    Database.Query(DbRows, 'SHOW TABLES');
    for T := 0 to DbRows.Count - 1 do begin
      NewTable := TSQLTable(SQLTables.AddNew(TSQLTable.Create));
      NewTable.Table.Assign(DbRows[T]);
      TableName := DbRows[T].Items[0].Value;
      Memo1.Lines.Add('Create object "' + TableName + '"');
      NewTable.Table.Add('ObjId', IntToStr(Core.System.AddObject(TableName, TableName, Database.Database)));
    end;

    for T := 0 to SQLTables.Count - 1 do
    with TSQLTable(SQLTables[T]) do begin
      TableName := Table.Items[0].Value;
      Database.Query(Columns, 'SELECT `TCOL`.`DATA_TYPE`, `TCOL`.`COLUMN_NAME`, ' +
      ' `KCU`.`REFERENCED_TABLE_SCHEMA`, `KCU`.`REFERENCED_TABLE_NAME`, `KCU`.`REFERENCED_COLUMN_NAME`, ' +
      ' `TC`.`CONSTRAINT_TYPE` ' +
      ' FROM `information_schema`.`COLUMNS` AS `TCOL`' +
      ' LEFT JOIN `information_schema`.`KEY_COLUMN_USAGE` AS `KCU` ON ' +
      ' (`KCU`.`TABLE_SCHEMA` = `TCOL`.`TABLE_SCHEMA`) AND ' +
      ' (`KCU`.`TABLE_NAME` = `TCOL`.`TABLE_NAME`) AND ' +
      ' (`KCU`.`COLUMN_NAME` = `TCOL`.`COLUMN_NAME`) ' +
      ' LEFT JOIN `information_schema`.`TABLE_CONSTRAINTS` AS `TC` ON' +
      ' (`KCU`.`CONSTRAINT_NAME` = `TC`.`CONSTRAINT_NAME`) AND ' +
      ' (`KCU`.`TABLE_NAME` = `TC`.`TABLE_NAME`) AND ' +
      ' (`KCU`.`CONSTRAINT_SCHEMA` = `TC`.`CONSTRAINT_SCHEMA`)' +
      ' WHERE ' +
      '(`TCOL`.`TABLE_SCHEMA` = "' + Database.Database + '") AND ' +
      '(`TCOL`.`TABLE_NAME` = "' + TableName + '")');
      Memo1.Lines.Add('Add properies for object "' + TableName + '"');
      ObjectId := StrToInt(Table.Values['ObjId']);
      for C := 0 to Columns.Count - 1 do begin
        PropertyName := Columns[C].Values['COLUMN_NAME'];
        PropType := Columns[C].Values['DATA_TYPE'];
        if Columns[C].Values['CONSTRAINT_TYPE'] = 'FOREIGN KEY' then begin
          RefObjectIndex := ObjectIdByName(Columns[C].Values['REFERENCED_TABLE_NAME']);
          PropertyId := Core.System.AddPropertyRelationOne(ObjectId, PropertyName, PropertyName,
            True, StrToInt(TSQLTable(SQLTables[RefObjectIndex]).Table.Values['ObjId']));
        end else
        if PropType = 'int' then
          PropertyId := Core.System.AddPropertyNumber(ObjectId, PropertyName, PropertyName, True);
        if PropType = 'varchar' then
          PropertyId := Core.System.AddPropertyString(ObjectId, PropertyName, PropertyName, True);
        if PropType = 'float' then
          PropertyId := Core.System.AddPropertyFloat(ObjectId, PropertyName, PropertyName, True);
        if PropType = 'datetime' then
          PropertyId := Core.System.AddPropertyDateTime(ObjectId, PropertyName, PropertyName, True);
        Columns[C].Add('Id', IntToStr(PropertyId));
        Memo1.Lines.Add('Create property "' + PropertyName + '" of type ' + PropType + '"');
        //Memo1.Lines.Add(Columns[C].Values['CONSTRAINT_TYPE']);
        //TSQLTable(SQLTables[T]).Columns.Add();
      end;
    end;

    for T := 0 to SQLTables.Count - 1 do
    with TSQLTable(SQLTables[T]) do begin
      TableName := Table.Items[0].Value;
      ObjectId := StrToInt(Table.Values['ObjId']);
      for C := 0 to Columns.Count - 1 do begin
        PropertyName := Columns[C].Values['COLUMN_NAME'];
        PropType := Columns[C].Values['DATA_TYPE'];
        if Columns[C].Values['CONSTRAINT_TYPE'] = 'FOREIGN KEY' then begin
          Memo1.Lines.Add('Add relation 1:n for "' + TableName + '.' + PropertyName + '"');
          RefObjectIndex := ObjectIdByName(Columns[C].Values['REFERENCED_TABLE_NAME']);

          RefPropertyId := TSQLTable(SQLTables[T]).ColumnByName(Columns[C].Values['COLUMN_NAME']);
          RefPropertyId := StrToInt(TSQLTable(SQLTables[T]).Columns[RefPropertyId].Values['Id']);
          PropertyId := Core.System.AddPropertyRelationMany(StrToInt(TSQLTable(SQLTables[RefObjectIndex]).Table.Values['ObjId']), TableName, TableName,
            True, RefPropertyId);
        end;
      end;
    end;
  finally
    DbRows2.Free;
    DbRows.Free;
    Database.Free;
    FormMenu.LoadTree;
  end;
end;

procedure TImportStructureForm.FormCreate(Sender: TObject);
begin
  SQLTables := TListObject.Create;
end;

procedure TImportStructureForm.FormDestroy(Sender: TObject);
begin
  SQLTables.Free;
end;

end.