Changeset 20 for trunk/Forms/UImportStructureForm.pas

Timestamp:

Jun 15, 2011, 11:11:59 AM (13 years ago)

Author:

george

Message:

• Added: Import relation one and many from existed database.

File:

• trunk/Forms/UImportStructureForm.pas (modified) (7 diffs)

trunk/Forms/UImportStructureForm.pas

@@ -7 +7 @@
 uses
   Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls,
-  USystem;
+  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 }
@@ -25 +35 @@
     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
-    { public declarations }
+    SQLTables: TListObject; // TListObject<TSQLTable>
   end; 
 
@@ -38 +51 @@
 
 uses
-  UCore, USqlDatabase, UMainForm;
+  UCore, UMainForm;
+
+{ 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);
@@ -63 +111 @@
   ObjectId: Integer;
   PropertyId: Integer;
+  NewTable: TSQLTable;
+  RefObjectIndex: Integer;
+  RefPropertyId: Integer;
 begin
   try
@@ -73 +124 @@
     Database.Password := EditPassword.Text;
     Database.Connect;
+
+    SQLTables.Clear;
     GroupId := Core.System.AddGroup(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 + '"');
-      ObjectId := Core.System.AddObject(TableName, TableName, Database.Database, GroupId);
-      Database.Query(DbRows2, 'SHOW COLUMNS FROM `' + TableName + '`');
-      for C := 0 to DbRows2.Count - 1 do begin
-        PropertyName := DbRows2[C].Values['Field'];
-        PropType := DbRows2[C].Values['Type'];
-        PropType := Copy(PropType, 1, Pos('(', PropType) - 1);
+      NewTable.Table.Add('ObjId', IntToStr(Core.System.AddObject(TableName, TableName, Database.Database, GroupId)));
+    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
+          PropertyId := Core.System.AddPropertyRelationOne(ObjectId, PropertyName, PropertyName,
+            ObjectIdByName(Columns[C].Values['REFERENCED_TABLE_NAME']));
+        end else
         if PropType = 'int' then
           PropertyId := Core.System.AddPropertyNumber(ObjectId, PropertyName, PropertyName);
@@ -90 +169 @@
         if PropType = 'float' then
           PropertyId := Core.System.AddPropertyFloat(ObjectId, PropertyName, PropertyName);
+        if PropType = 'datetime' then
+          PropertyId := Core.System.AddPropertyDateTime(ObjectId, PropertyName, PropertyName);
+        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,
+            RefPropertyId);
+        end;
       end;
     end;
@@ -101 +204 @@
 end;
 
+procedure TImportStructureForm.FormCreate(Sender: TObject);
+begin
+  SQLTables := TListObject.Create;
+end;
+
+procedure TImportStructureForm.FormDestroy(Sender: TObject);
+begin
+  SQLTables.Free;
+end;
+
 end.