Changeset 13 for trunk/DbEngines

Timestamp:

Mar 17, 2015, 12:09:11 AM (10 years ago)

Author:

chronos

Message:

• Modified: Now records are loaded from SQL database using SQL query. The query is parsed also by XML database client to load records from XML file.
• Added: Packages Network and Synapse.

Location:

trunk/DbEngines

Files:

• UEngineMySQL.pas (modified) (4 diffs)
• UEngineXML.pas (modified) (3 diffs)

trunk/DbEngines/UEngineMySQL.pas

@@ -14 +14 @@
   TDatabaseMySQL = class(TDatabaseClient)
   private
+    procedure LoadFields(Table: TTable);
     procedure LoadTables;
   public
     SqlDatabase: TSqlDatabase;
+    procedure Query(DbRows: TDbRows; Text: string); override;
     constructor Create; override;
     destructor Destroy; override;
@@ -27 +29 @@
 { TDatabaseMySQL }
 
+procedure TDatabaseMySQL.LoadFields(Table: TTable);
+var
+  DbRows: TDbRows;
+  NewField: TField;
+  I: Integer;
+  DataType: Integer;
+begin
+  DbRows := TDbRows.Create;
+  try
+    SqlDatabase.Query(DbRows, 'SELECT * FROM `ModelField` WHERE `Model` = ' + IntToStr(Table.Id) + '');
+    for I := 0 to DbRows.Count - 1 do begin
+      NewField := TField.Create;
+      NewField.Table := Table;
+      NewField.Name := TDictionaryStringString(DbRows[I]).Values['Name'];
+      NewField.TextBefore := TDictionaryStringString(DbRows[I]).Values['Title'];
+      DataType := StrToInt(TDictionaryStringString(DbRows[I]).Values['DataType']);
+      NewField.DataType := Table.Database.Engine.DataTypes.FindByType(TFieldType(DataType));
+      if not Assigned(NewField.DataType) then
+        NewField.DataType := Table.Database.Engine.DataTypes.FindByType(ftString);
+      Table.Fields.Add(NewField);
+    end;
+  finally
+    DbRows.Free;
+  end;
+end;
+
 procedure TDatabaseMySQL.LoadTables;
 var
   DbRows: TDbRows;
+  DbRows2: TDbRows;
   NewTable: TTable;
   I: Integer;
@@ -35 +64 @@
   DbRows := TDbRows.Create;
   try
-    SqlDatabase.Query(DbRows, 'SELECT `TABLE_NAME` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` = "' + SqlDatabase.Database + '"');
+    SqlDatabase.Query(DbRows, 'SELECT `Id`,`Name`,`Title` FROM `Model`');
     for I := 0 to DbRows.Count - 1 do begin
       NewTable := TTable.Create;
+      NewTable.Id := StrToInt(TDictionaryStringString(DbRows[I]).Values['Id']);
       NewTable.Database := Database;
-      NewTable.Name := (TDictionaryStringString(DbRows[I])[0]).Value;
-      NewTable.Caption := NewTable.Name;
+      NewTable.Name := TDictionaryStringString(DbRows[I]).Values['Name'];
+      NewTable.Caption := TDictionaryStringString(DbRows[I]).Values['Title'];
+      LoadFields(NewTable);
+      DbRows2 := TDbRows.Create;
+      try
+        SqlDatabase.Query(DbRows2, 'SELECT COUNT(*) FROM `' + NewTable.Name + '`');
+        if DbRows2.Count = 1 then
+          NewTable.RecordsCount := StrToInt(DbRows2[0].Values['COUNT(*)']);
+      finally
+        DbRows2.Free;
+      end;
       Database.Tables.Add(NewTable);
     end;
@@ -46 +85 @@
     DbRows.Free;
   end;
+end;
+
+procedure TDatabaseMySQL.Query(DbRows: TDbRows; Text: string);
+begin
+  SqlDatabase.Query(DbRows, Text);
 end;
 

trunk/DbEngines/UEngineXML.pas

@@ -7 +7 @@
 uses
   Classes, SysUtils, DOM, XMLRead, XMLWrite, UDatabase, UXMLUtils, FileUtil,
-  UHtmlClasses;
+  UHtmlClasses, USqlDatabase, SpecializedDictionary;
 
 type
@@ -29 +29 @@
     procedure LoadFromFile(FileName: string);
     procedure SaveToFile(FileName: string);
+    function GetNextPart(var Text: string): string;
   public
+    procedure Query(DbRows: TDbRows; Text: string); override;
     procedure Load; override;
     procedure Save; override;
@@ -280 +282 @@
 end;
 
+function TDatabaseXML.GetNextPart(var Text: string): string;
+begin
+  if Pos(' ', Text) > 0 then begin
+    Result := Trim(Copy(Text, 1, Pos(' ', Text) - 1));
+    Delete(Text, 1, Pos(' ', Text));
+  end else begin
+    Result := Text;
+    Text := '';
+  end;
+end;
+
+procedure TDatabaseXML.Query(DbRows: TDbRows; Text: string);
+var
+  Command: string;
+  Columns: string;
+  TableName: string;
+  Table: TTable;
+  NewRecord: TDictionaryStringString;
+  I: Integer;
+  F: Integer;
+begin
+  Command := GetNextPart(Text);
+  if Command = 'SELECT' then begin
+    Columns := GetNextPart(Text);
+    Command := GetNextPart(Text);
+    if Command = 'FROM' then begin
+      TableName := GetNextPart(Text);
+    end;
+    Table := Database.Tables.SearchByName(TableName);
+    if Assigned(Table) then begin
+      DbRows.Count := 0;
+      if Columns = '*' then begin
+        for I := 0 to Table.Records.Count - 1 do begin
+          NewRecord := TDictionaryStringString.Create;
+          for F := 0 to Table.Fields.Count - 1 do
+            NewRecord.Add(TField(Table.Fields[F]).Name, TValue(TRecord(Table.Records[I]).Values[I]).GetString);
+          DbRows.Add(NewRecord);
+        end;
+      end else
+      if Columns = 'COUNT(*)' then begin
+        NewRecord := TDictionaryStringString.Create;
+        NewRecord.Add('COUNT(*)', IntToStr(Table.Records.Count));
+        DbRows.Add(NewRecord);
+      end else raise Exception.Create('Unsupported columns ' + Columns + ' specification');
+    end else raise Exception.Create('Table ' + TableName + ' not found.');
+  end else raise Exception.Create('Unsupported SQL command ' + Command);
+end;
+
 procedure TDatabaseXML.Load;
 begin