Changeset 11 for trunk/Forms/UMainForm.pas

Timestamp:

Jun 9, 2011, 12:34:21 PM (13 years ago)

Author:

george

Message:

• Added: Build in SQL structure and initial values initialization.

File:

• trunk/Forms/UMainForm.pas (modified) (9 diffs)

trunk/Forms/UMainForm.pas

@@ -8 +8 @@
   Registry, Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, ComCtrls,
   StdCtrls, ActnList, Menus, ExtCtrls, USqlDatabase, DOM, XMLRead, XMLWrite,
-  UPersistentForm, UTreeState, SpecializedList, URegistry, USystem;
-
-const
-  ConfigFileName = 'Config.xml';
+  UPersistentForm, UTreeState, SpecializedList, SpecializedDictionary,
+  URegistry, USystem;
 
 type
@@ -18 +16 @@
   TMainForm = class(TForm)
     AConnect: TAction;
+    AInitSystemValues: TAction;
     ASettings: TAction;
     AToggleFullscreen: TAction;
@@ -48 +47 @@
     MenuItem16: TMenuItem;
     MenuItem17: TMenuItem;
+    MenuItem18: TMenuItem;
+    MenuItem19: TMenuItem;
     MenuItem2: TMenuItem;
     MenuItem3: TMenuItem;
@@ -65 +66 @@
     procedure AConnectExecute(Sender: TObject);
     procedure AExitExecute(Sender: TObject);
+    procedure AInitSystemValuesExecute(Sender: TObject);
     procedure AItemAddExecute(Sender: TObject);
     procedure AItemEditExecute(Sender: TObject);
@@ -94 +96 @@
     procedure SaveToRegistry;
     procedure InitStructure;
+    procedure InitSystemValues;
   public
     SelectedObjectId: Integer;
@@ -157 +160 @@
 
 procedure TMainForm.InitStructure;
-begin
-
+var
+  DbRows: TDbRows;
+  DbRows2: TDbRows;
+  StructureVersion: string;
+  Data: TDictionaryStringString;
+  ObjectId: Integer;
+begin
+  with System do
+  try
+    DbRows := TDbRows.Create;
+    Data := TDictionaryStringString.Create;
+
+    Database.Query(DbRows, 'SHOW TABLES LIKE "' + InformationTable + '"');
+    if DbRows.Count = 0 then begin
+      Database.Query(DbRows, 'CREATE TABLE IF NOT EXISTS `' + InformationTable + '` ( ' +
+  '`Version` varchar(255) NOT NULL,' +
+  '`LastUpdateTime` datetime NOT NULL' +
+') ENGINE=InnoDB DEFAULT CHARSET=utf8;');
+      Database.Query(DbRows, 'INSERT INTO `' + InformationTable + '` (`Version`, `LastUpdateTime`) VALUES ' +
+  '("0.1", "0000-00-00 00:00:00");');
+    end;
+    Database.Select(DbRows, InformationTable);
+    StructureVersion := DbRows[0].Values['Version'];
+
+    Database.Query(DbRows, 'SHOW TABLES LIKE "' + ObjectTable + '"');
+    if DbRows.Count = 0 then begin
+      Database.Query(DbRows, 'CREATE TABLE IF NOT EXISTS `' + ObjectTable + '` ( ' +
+        '`Id` int(11) NOT NULL AUTO_INCREMENT,' +
+        '`Name` varchar(255) NOT NULL,' +
+        '`Schema` varchar(255) NOT NULL,' +
+        '`Table` varchar(255) NOT NULL,' +
+        '`PrimaryKey` varchar(255) NOT NULL DEFAULT "Id", ' +
+        '`Sequence` int(11) NOT NULL,' +
+        '`Group` int(11) NOT NULL,' +
+        'PRIMARY KEY (`Id`),' +
+        'KEY `Group` (`Group`)' +
+      ') ENGINE=InnoDB  DEFAULT CHARSET=utf8');
+
+    end;
+
+    Database.Query(DbRows, 'SHOW TABLES LIKE "' + ObjectGroupTable + '"');
+    if DbRows.Count = 0 then begin
+      Database.Query(DbRows, 'CREATE TABLE IF NOT EXISTS `' + ObjectGroupTable + '` ( ' +
+        '`Id` int(11) NOT NULL AUTO_INCREMENT,' +
+        '`Name` varchar(255) NOT NULL,' +
+        '`Parent` int(11) NOT NULL,' +
+        '`Sequence` int(11) NOT NULL,' +
+        'PRIMARY KEY (`Id`)' +
+      ') ENGINE=InnoDB  DEFAULT CHARSET=utf8');
+    end;
+
+    Database.Query(DbRows, 'SHOW TABLES LIKE "' + PropertyTable + '"');
+    if DbRows.Count = 0 then begin
+      Database.Query(DbRows, 'CREATE TABLE IF NOT EXISTS `' + PropertyTable + '` ( ' +
+        '`Id` int(11) NOT NULL AUTO_INCREMENT,' +
+      '`Name` varchar(255) NOT NULL,' +
+      '`Object` int(11) NOT NULL,' +
+      '`PropertyGroup` int(11) NOT NULL,' +
+      '`Type` int(11) NOT NULL,' +
+      '`ColumnName` varchar(255) NOT NULL,' +
+      'KEY `Object` (`Object`),' +
+      'KEY `PropertyGroup` (`PropertyGroup`),' +
+      'KEY `Type` (`Type`),' +
+      'PRIMARY KEY (`Id`)' +
+      ') ENGINE=InnoDB  DEFAULT CHARSET=utf8');
+    end;
+    Database.Query(DbRows, 'SHOW TABLES LIKE "' + PropertyTypeTable + '"');
+    if DbRows.Count = 0 then begin
+      Database.Query(DbRows, 'CREATE TABLE IF NOT EXISTS `' + PropertyTypeTable + '` ( ' +
+        '`Id` int(11) NOT NULL AUTO_INCREMENT,' +
+      '`Name` varchar(255) NOT NULL,' +
+      '`DbType` varchar(255) NOT NULL,' +
+      '`TypeIndex` int(11) NOT NULL,' +
+      '`Parent` int(11) NOT NULL,' +
+      '`ParameterTable` varchar(255) NOT NULL,' +
+      'PRIMARY KEY (`Id`)' +
+      ') ENGINE=InnoDB  DEFAULT CHARSET=utf8');
+    end;
+    Database.Query(DbRows, 'SHOW TABLES LIKE "' + PropertyGroupTable + '"');
+    if DbRows.Count = 0 then begin
+      Database.Query(DbRows, 'CREATE TABLE IF NOT EXISTS `' + PropertyGroupTable + '` ( ' +
+        '`Id` int(11) NOT NULL AUTO_INCREMENT,' +
+      '`Name` varchar(255) NOT NULL,' +
+      '`Object` int(11) NOT NULL,' +
+      'KEY `Object` (`Object`),' +
+      'PRIMARY KEY (`Id`)' +
+      ') ENGINE=InnoDB  DEFAULT CHARSET=utf8');
+    end;
+
+  finally
+    Data.Free;
+    DbRows.Free;
+  end;
+end;
+
+procedure TMainForm.InitSystemValues;
+var
+  ObjectId: Integer;
+  TypeNumber: Integer;
+  TypeString: Integer;
+  GroupId: Integer;
+begin
+  with System do begin
+  TypeNumber := AddType('Number', 'INT', vtInteger);
+  TypeString := AddType('String', 'VAR(255)', vtString);
+  AddType('Text', 'TEXT', vtText);
+  AddType('Date and time', 'DATETIME', vtDateTime);
+  AddType('Floating number', 'FLOAT', vtFloat);
+  AddType('Image', 'BLOB', vtImage);
+  AddType('Boolean', 'BOOL', vtBoolean);
+  AddType('IPv4', 'BINARY(4)', vtIPv4);
+  AddType('IPv6', 'BINARY(16)', vtIPv6);
+  AddType('MAC address', 'BINARY(6)', vtMAC);
+  AddType('File', '', vtFile);
+  AddType('GPS', '', vtGPS);
+  AddType('Currency', 'FLOAT', vtCurrency);
+  AddType('Enumeration', '', vtEnumeration);
+  AddType('Time', 'TIME', vtTime);
+  AddType('Date', 'DATE', vtDate);
+  AddType('Color', 'INT', vtColor);
+
+  GroupId := AddObjectGroup('System');
+
+  ObjectId := AddObject('Object groups', 'ObjectGroup', System.Database.Database, GroupId);
+    AddProperty(ObjectId, 'Name', 'Name', TypeString);
+  ObjectId := AddObject('Objects', 'Object', System.Database.Database, GroupId);
+    AddProperty(ObjectId, 'Name', 'Name', TypeString);
+    AddProperty(ObjectId, 'Group', 'Group', TypeNumber);
+    AddProperty(ObjectId, 'Schema', 'Schema', TypeString);
+    AddProperty(ObjectId, 'Table', 'Table', TypeString);
+    AddProperty(ObjectId, 'Primary key', 'PrimaryKey', TypeString);
+    AddProperty(ObjectId, 'Sequence', 'Sequence', TypeNumber);
+  ObjectId := AddObject('Property types', 'Type', System.Database.Database, GroupId);
+    AddProperty(ObjectId, 'Name', 'Name', TypeString);
+    AddProperty(ObjectId, 'Type', 'DbType', TypeString);
+    AddProperty(ObjectId, 'Type index', 'TypeIndex', TypeNumber);
+    AddProperty(ObjectId, 'Parent', 'Parent', TypeNumber);
+  ObjectId := AddObject('Property groups', 'PropertyGroup', System.Database.Database, GroupId);
+  ObjectId := AddObject('Properties', 'Property', System.Database.Database, GroupId);
+    AddProperty(ObjectId, 'Name', 'Name', TypeString);
+    AddProperty(ObjectId, 'Object', 'Object', TypeNumber);
+    AddProperty(ObjectId, 'PropertyGroup', 'PropertyGroup', TypeNumber);
+    AddProperty(ObjectId, 'Type', 'Type', TypeNumber);
+    AddProperty(ObjectId, 'ColumnName', 'ColumnName', TypeString);
+  end;
 end;
 
@@ -271 +417 @@
 end;
 
+procedure TMainForm.AInitSystemValuesExecute(Sender: TObject);
+begin
+  InitSystemValues;
+  LoadTree;
+end;
+
 procedure TMainForm.AConnectExecute(Sender: TObject);
 begin
@@ -360 +512 @@
     try
       DbRows := TDbRows.Create;
-      System.Database.Query(DbRows, 'SELECT * FROM `ObjectGroup`');
+      System.Database.Query(DbRows, 'SELECT * FROM `' + ObjectGroupTable + '`');
       for I := 0 to DbRows.Count - 1 do begin
         NewNode := AddChild(TopItem, DbRows[I].Values['Name']);
@@ -368 +520 @@
         try
           ObjectDbRows := TDbRows.Create;
-          System.Database.Query(ObjectDbRows, 'SELECT * FROM `Object` WHERE `Group`=' + DbRows[I].Values['Id']);
+          System.Database.Query(ObjectDbRows, 'SELECT * FROM `' + ObjectTable + '` WHERE `Group`=' + DbRows[I].Values['Id']);
           for O := 0 to ObjectDbRows.Count - 1 do begin
             NewObjectNode := AddChild(NewNode, ObjectDbRows[O].Values['Name']);