Changeset 23 for trunk/DbEngines

Timestamp:

Mar 26, 2018, 12:40:10 AM (7 years ago)

Author:

chronos

Message:

• Modified: More changes to implement SQL over XML file.

Location:

trunk/DbEngines

Files:

• UEngineMySQL.pas (modified) (2 diffs)
• UEngineXML.pas (modified) (9 diffs)

trunk/DbEngines/UEngineMySQL.pas

@@ -16 +16 @@
     procedure LoadFields(Table: TTable);
   protected
-    procedure LoadTables(Tables: TTables); override;
   public
     SqlDatabase: TSqlDatabase;
@@ -102 +101 @@
 end;
 
-procedure TDatabaseMySQL.LoadTables(Tables: TTables);
-var
-  DbRows: TDbRows;
-  NewTable: TTable;
-  I: Integer;
-begin
-  DbRows := TDbRows.Create;
-  try
-    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.DbClient := Self;
-      NewTable.Name := TDictionaryStringString(DbRows[I]).Values['Name'];
-      NewTable.Caption := TDictionaryStringString(DbRows[I]).Values['Title'];
-      LoadFields(NewTable);
-      Tables.Add(NewTable);
-    end;
-  finally
-    DbRows.Free;
-  end;
-end;
-
 procedure TDatabaseMySQL.Query(Text: string; DbRows: TDbRows = nil);
 begin

trunk/DbEngines/UEngineXML.pas

@@ -33 +33 @@
     function CheckNext(var Source: string; Text: string): Boolean;
     function GetNextPart(var Text: string; Separator: string = ' '): string;
+    function TableCreateIfNotExists(Name: string): TTable;
+    function FieldCreateIfNotExists(TableName, FieldName: string; DataType: TDataType): TField;
   protected
   public
     procedure Query(Text: string; DbRows: TDbRows = nil); override;
-    procedure LoadTables(Tables: TTables); override;
     procedure Load; override;
     procedure Save; override;
@@ -62 +63 @@
 resourcestring
   SWrongFileFormat = 'Wrong file format';
+  STableNotFound = 'Table %s not found';
+  SUnsupportedSqlCommand = 'Unsupported SQL command: %s';
+  SColumnNotFoundInTable = 'Column %s not found in table %s';
 
 { TDbConnectParamsXml }
@@ -336 +340 @@
     Result := Text;
     Text := '';
+  end;
+end;
+
+function TDatabaseXML.TableCreateIfNotExists(Name: string): TTable;
+begin
+  Result := Tables.SearchByName(Name);
+  if not Assigned(Result) then begin
+    Tables.AddNew(Name);
+  end;
+end;
+
+function TDatabaseXML.FieldCreateIfNotExists(TableName, FieldName: string; DataType: TDataType): TField;
+var
+  Table: TTable;
+begin
+  TableCreateIfNotExists(TableName);
+  Table := Tables.SearchByName(TableName);
+  Result := Table.Fields.SearchByName(FieldName);
+  if not Assigned(Result) then begin
+    Table.Fields.AddNew(FieldName, DataType);
   end;
 end;
@@ -359 +383 @@
   Command := GetNextPart(Text);
   if Command = 'SELECT' then begin
+    DbRows.Count := 0;
     Columns := GetNextPart(Text);
     Expect(Text, 'FROM');
     TableName := GetNextPart(Text);
-    Table := Tables.SearchByName(TableName);
-    if Assigned(Table) then begin
-      DbRows.Count := 0;
+    if CheckNext(Text, 'WHERE') then begin
+      WhereName := GetNextPart(Text);
+      Command := GetNextPart(Text);
+      if Command = '=' then begin
+        WhereValue := GetNextPart(Text);
+      end else raise Exception.Create('Expression error');
+    end;
+    if TableName = 'Model' then begin
       if Columns = '*' then begin
-        for I := 0 to Table.Records.Count - 1 do begin
+        for I := 0 to Tables.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);
+          NewRecord.Add('Name', TTable(Tables[I]).Name);
+          NewRecord.Add('Caption', TTable(Tables[I]).Caption);
           DbRows.Add(NewRecord);
         end;
@@ -375 +405 @@
       if Columns = 'COUNT(*)' then begin
         NewRecord := TDictionaryStringString.Create;
-        NewRecord.Add('COUNT(*)', IntToStr(Table.Records.Count));
+        NewRecord.Add('COUNT(*)', IntToStr(Tables.Count));
         DbRows.Add(NewRecord);
       end else raise Exception.Create('Unsupported columns ' + Columns + ' specification');
-    end else raise Exception.Create('Table ' + TableName + ' not found.');
+    end else
+    if TableName = 'ModelField' then begin
+      if WhereName = 'Model' then
+        Table := Tables.SearchByName(WhereValue)
+        else Table := nil;
+      if Assigned(Table) then begin
+        if Columns = '*' then begin
+          for I := 0 to Table.Fields.Count - 1 do begin
+            NewRecord := TDictionaryStringString.Create;
+            NewRecord.Add('Name', TField(Table.Fields[I]).Name);
+            NewRecord.Add('Caption', TField(Table.Fields[I]).TextBefore);
+            NewRecord.Add('Model', Table.Name);
+            NewRecord.Add('DataType', TField(Table.Fields[I]).DataType.Name);
+            DbRows.Add(NewRecord);
+          end;
+        end else
+        if Columns = 'COUNT(*)' then begin
+          NewRecord := TDictionaryStringString.Create;
+          NewRecord.Add('COUNT(*)', IntToStr(Table.Fields.Count));
+          DbRows.Add(NewRecord);
+        end else raise Exception.Create('Unsupported columns ' + Columns + ' specification');
+      end else raise Exception.Create(Format(STableNotFound, [WhereValue]));
+    end else begin
+      Table := Tables.SearchByName(TableName);
+      if Assigned(Table) then begin
+        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(Format(STableNotFound, [TableName]));
+    end;
   end else
   if Command = 'INSERT' then begin
@@ -401 +470 @@
     Expect(Text, ')');
     if TableName = 'Model' then begin
-      Table := TTable.Create;
-      Table.Name := InsertValues.Values['Name'];
+      Table := Tables.AddNew(InsertValues.Values['Name']);
       Table.Caption := InsertValues.Values['Caption'];
-      Table.DbClient := Self;
-      Tables.Add(Table);
+    end else
+    if TableName = 'ModelField' then begin
+      Table := Tables.SearchByName(InsertValues.Values['Model']);
+      if Assigned(Table) then begin
+        Field := Table.Fields.AddNew(InsertValues.Values['Name'], DbManager.DataTypes.SearchByType(ftString));
+        Field.TextBefore := InsertValues.Values['Caption'];
+        Field.DataType := DbManager.DataTypes.SearchByName(InsertValues.Values['DataType']);
+      end else raise Exception.Create(Format(STableNotFound, [InsertValues.Values['Model']]));
     end else begin
       Table := Tables.SearchByName(TableName);
       if Assigned(Table) then begin
-        Row := TRecord.Create;
-        Row.Parent := Table;
-        Row.InitValues;
+        Row := Table.Records.AddNew;
         for ValueIndex := 0 to InsertValues.Count - 1 do begin
           Field := Table.Fields.SearchByName(InsertValues.Names[ValueIndex]);
-          FieldIndex := Table.Fields.IndexOf(Field);
-          TValue(Row.Values[FieldIndex]).SetString(InsertValues.ValueFromIndex[ValueIndex]);
+          if Assigned(Field) then begin
+            FieldIndex := Table.Fields.IndexOf(Field);
+            TValue(Row.Values[FieldIndex]).SetString(InsertValues.ValueFromIndex[ValueIndex]);
+          end else raise Exception.Create(Format(SColumnNotFoundInTable,
+            [InsertValues.Names[ValueIndex], TableName]));
         end;
-        Table.Records.Add(Row);
-      end else raise Exception.Create(Format('Table %s not found', [TableName]));
+      end else raise Exception.Create(Format(STableNotFound, [TableName]));
     end;
     InsertValues.Free;
@@ -436 +510 @@
       if Assigned(Table) then begin
         Tables.Remove(Table);
-      end else raise Exception.Create(Format('Table %s not found', [whereValue]));
+      end else raise Exception.Create(Format(STableNotFound, [whereValue]));
+    end else
+    if TableName = 'ModelField' then begin
+      Field := Table.Fields.SearchByName(WhereName);
+      Table.Fields.Remove(Field);
     end else begin
       Table := Tables.SearchByName(TableName);
@@ -444 +522 @@
           Table.Records.Remove(Row);
         end else raise Exception.Create('Row not found');
-      end else raise Exception.Create(Format('Table %s not found', [TableName]));
+      end else raise Exception.Create(Format(STableNotFound, [TableName]));
     end;
   end else
-    raise Exception.Create('Unsupported SQL command ' + Command);
-end;
-
-procedure TDatabaseXML.LoadTables(Tables: TTables);
-begin
-  Tables.Assign(Self.Tables);
+    raise Exception.Create(Format(SUnsupportedSqlCommand, [Command]));
 end;
 
@@ -461 +534 @@
   if FileExists(FileName) then
     LoadFromFile(FileName);
+  (*
+  TableCreateIfNotExists('Model');
+  TableCreateIfNotExists('ModelField');
+  TableCreateIfNotExists('DataType');
+  TableCreateIfNotExists('Module');
+
+  FieldCreateIfNotExists('ModelField', 'Name', DbManager.DataTypes.SearchByType(ftString));
+  FieldCreateIfNotExists('ModelField', 'Caption', DbManager.DataTypes.SearchByType(ftString));
+  FieldCreateIfNotExists('ModelField', 'Model', DbManager.DataTypes.SearchByType(ftString));
+  FieldCreateIfNotExists('ModelField', 'DataType', DbManager.DataTypes.SearchByType(ftString));
+  FieldCreateIfNotExists('Model', 'Name', DbManager.DataTypes.SearchByType(ftString));
+  FieldCreateIfNotExists('Model', 'Caption', DbManager.DataTypes.SearchByType(ftString));
+  FieldCreateIfNotExists('Model', 'Module', DbManager.DataTypes.SearchByType(ftString));
+  FieldCreateIfNotExists('Model', 'System', DbManager.DataTypes.SearchByType(ftBoolean));
+  FieldCreateIfNotExists('DataType', 'Name', DbManager.DataTypes.SearchByType(ftString));
+  FieldCreateIfNotExists('DataType', 'Caption', DbManager.DataTypes.SearchByType(ftString));
+  FieldCreateIfNotExists('DataType', 'FieldType', DbManager.DataTypes.SearchByType(ftInteger));
+  FieldCreateIfNotExists('Module', 'Name', DbManager.DataTypes.SearchByType(ftString));
+  FieldCreateIfNotExists('Module', 'Caption', DbManager.DataTypes.SearchByType(ftString));
+  *)
 end;