Changeset 23


Ignore:
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
Files:
8 edited

Legend:

Unmodified
Added
Removed

  • trunk/DbEngines/UEngineMySQL.pas

    r21 r23  
    1616    procedure LoadFields(Table: TTable);
    1717  protected
    18     procedure LoadTables(Tables: TTables); override;
    1918  public
    2019    SqlDatabase: TSqlDatabase;
    … …  
    102101end;
    103102
    104 procedure TDatabaseMySQL.LoadTables(Tables: TTables);
    105 var
    106   DbRows: TDbRows;
    107   NewTable: TTable;
    108   I: Integer;
    109 begin
    110   DbRows := TDbRows.Create;
    111   try
    112     SqlDatabase.Query(DbRows, 'SELECT `Id`,`Name`,`Title` FROM `Model`');
    113     for I := 0 to DbRows.Count - 1 do begin
    114       NewTable := TTable.Create;
    115       NewTable.Id := StrToInt(TDictionaryStringString(DbRows[I]).Values['Id']);
    116       NewTable.DbClient := Self;
    117       NewTable.Name := TDictionaryStringString(DbRows[I]).Values['Name'];
    118       NewTable.Caption := TDictionaryStringString(DbRows[I]).Values['Title'];
    119       LoadFields(NewTable);
    120       Tables.Add(NewTable);
    121     end;
    122   finally
    123     DbRows.Free;
    124   end;
    125 end;
    126 
    127103procedure TDatabaseMySQL.Query(Text: string; DbRows: TDbRows = nil);
    128104begin

  • trunk/DbEngines/UEngineXML.pas

    r22 r23  
    3333    function CheckNext(var Source: string; Text: string): Boolean;
    3434    function GetNextPart(var Text: string; Separator: string = ' '): string;
     35    function TableCreateIfNotExists(Name: string): TTable;
     36    function FieldCreateIfNotExists(TableName, FieldName: string; DataType: TDataType): TField;
    3537  protected
    3638  public
    3739    procedure Query(Text: string; DbRows: TDbRows = nil); override;
    38     procedure LoadTables(Tables: TTables); override;
    3940    procedure Load; override;
    4041    procedure Save; override;
    … …  
    6263resourcestring
    6364  SWrongFileFormat = 'Wrong file format';
     65  STableNotFound = 'Table %s not found';
     66  SUnsupportedSqlCommand = 'Unsupported SQL command: %s';
     67  SColumnNotFoundInTable = 'Column %s not found in table %s';
    6468
    6569{ TDbConnectParamsXml }
    … …  
    336340    Result := Text;
    337341    Text := '';
     342  end;
     343end;
     344
     345function TDatabaseXML.TableCreateIfNotExists(Name: string): TTable;
     346begin
     347  Result := Tables.SearchByName(Name);
     348  if not Assigned(Result) then begin
     349    Tables.AddNew(Name);
     350  end;
     351end;
     352
     353function TDatabaseXML.FieldCreateIfNotExists(TableName, FieldName: string; DataType: TDataType): TField;
     354var
     355  Table: TTable;
     356begin
     357  TableCreateIfNotExists(TableName);
     358  Table := Tables.SearchByName(TableName);
     359  Result := Table.Fields.SearchByName(FieldName);
     360  if not Assigned(Result) then begin
     361    Table.Fields.AddNew(FieldName, DataType);
    338362  end;
    339363end;
    … …  
    359383  Command := GetNextPart(Text);
    360384  if Command = 'SELECT' then begin
     385    DbRows.Count := 0;
    361386    Columns := GetNextPart(Text);
    362387    Expect(Text, 'FROM');
    363388    TableName := GetNextPart(Text);
    364     Table := Tables.SearchByName(TableName);
    365     if Assigned(Table) then begin
    366       DbRows.Count := 0;
     389    if CheckNext(Text, 'WHERE') then begin
     390      WhereName := GetNextPart(Text);
     391      Command := GetNextPart(Text);
     392      if Command = '=' then begin
     393        WhereValue := GetNextPart(Text);
     394      end else raise Exception.Create('Expression error');
     395    end;
     396    if TableName = 'Model' then begin
    367397      if Columns = '*' then begin
    368         for I := 0 to Table.Records.Count - 1 do begin
     398        for I := 0 to Tables.Count - 1 do begin
    369399          NewRecord := TDictionaryStringString.Create;
    370           for F := 0 to Table.Fields.Count - 1 do
    371             NewRecord.Add(TField(Table.Fields[F]).Name, TValue(TRecord(Table.Records[I]).Values[I]).GetString);
     400          NewRecord.Add('Name', TTable(Tables[I]).Name);
     401          NewRecord.Add('Caption', TTable(Tables[I]).Caption);
    372402          DbRows.Add(NewRecord);
    373403        end;
    … …  
    375405      if Columns = 'COUNT(*)' then begin
    376406        NewRecord := TDictionaryStringString.Create;
    377         NewRecord.Add('COUNT(*)', IntToStr(Table.Records.Count));
     407        NewRecord.Add('COUNT(*)', IntToStr(Tables.Count));
    378408        DbRows.Add(NewRecord);
    379409      end else raise Exception.Create('Unsupported columns ' + Columns + ' specification');
    380     end else raise Exception.Create('Table ' + TableName + ' not found.');
     410    end else
     411    if TableName = 'ModelField' then begin
     412      if WhereName = 'Model' then
     413        Table := Tables.SearchByName(WhereValue)
     414        else Table := nil;
     415      if Assigned(Table) then begin
     416        if Columns = '*' then begin
     417          for I := 0 to Table.Fields.Count - 1 do begin
     418            NewRecord := TDictionaryStringString.Create;
     419            NewRecord.Add('Name', TField(Table.Fields[I]).Name);
     420            NewRecord.Add('Caption', TField(Table.Fields[I]).TextBefore);
     421            NewRecord.Add('Model', Table.Name);
     422            NewRecord.Add('DataType', TField(Table.Fields[I]).DataType.Name);
     423            DbRows.Add(NewRecord);
     424          end;
     425        end else
     426        if Columns = 'COUNT(*)' then begin
     427          NewRecord := TDictionaryStringString.Create;
     428          NewRecord.Add('COUNT(*)', IntToStr(Table.Fields.Count));
     429          DbRows.Add(NewRecord);
     430        end else raise Exception.Create('Unsupported columns ' + Columns + ' specification');
     431      end else raise Exception.Create(Format(STableNotFound, [WhereValue]));
     432    end else begin
     433      Table := Tables.SearchByName(TableName);
     434      if Assigned(Table) then begin
     435        if Columns = '*' then begin
     436          for I := 0 to Table.Records.Count - 1 do begin
     437            NewRecord := TDictionaryStringString.Create;
     438            for F := 0 to Table.Fields.Count - 1 do
     439              NewRecord.Add(TField(Table.Fields[F]).Name, TValue(TRecord(Table.Records[I]).Values[I]).GetString);
     440            DbRows.Add(NewRecord);
     441          end;
     442        end else
     443        if Columns = 'COUNT(*)' then begin
     444          NewRecord := TDictionaryStringString.Create;
     445          NewRecord.Add('COUNT(*)', IntToStr(Table.Records.Count));
     446          DbRows.Add(NewRecord);
     447        end else raise Exception.Create('Unsupported columns ' + Columns + ' specification');
     448      end else raise Exception.Create(Format(STableNotFound, [TableName]));
     449    end;
    381450  end else
    382451  if Command = 'INSERT' then begin
    … …  
    401470    Expect(Text, ')');
    402471    if TableName = 'Model' then begin
    403       Table := TTable.Create;
    404       Table.Name := InsertValues.Values['Name'];
     472      Table := Tables.AddNew(InsertValues.Values['Name']);
    405473      Table.Caption := InsertValues.Values['Caption'];
    406       Table.DbClient := Self;
    407       Tables.Add(Table);
     474    end else
     475    if TableName = 'ModelField' then begin
     476      Table := Tables.SearchByName(InsertValues.Values['Model']);
     477      if Assigned(Table) then begin
     478        Field := Table.Fields.AddNew(InsertValues.Values['Name'], DbManager.DataTypes.SearchByType(ftString));
     479        Field.TextBefore := InsertValues.Values['Caption'];
     480        Field.DataType := DbManager.DataTypes.SearchByName(InsertValues.Values['DataType']);
     481      end else raise Exception.Create(Format(STableNotFound, [InsertValues.Values['Model']]));
    408482    end else begin
    409483      Table := Tables.SearchByName(TableName);
    410484      if Assigned(Table) then begin
    411         Row := TRecord.Create;
    412         Row.Parent := Table;
    413         Row.InitValues;
     485        Row := Table.Records.AddNew;
    414486        for ValueIndex := 0 to InsertValues.Count - 1 do begin
    415487          Field := Table.Fields.SearchByName(InsertValues.Names[ValueIndex]);
    416           FieldIndex := Table.Fields.IndexOf(Field);
    417           TValue(Row.Values[FieldIndex]).SetString(InsertValues.ValueFromIndex[ValueIndex]);
     488          if Assigned(Field) then begin
     489            FieldIndex := Table.Fields.IndexOf(Field);
     490            TValue(Row.Values[FieldIndex]).SetString(InsertValues.ValueFromIndex[ValueIndex]);
     491          end else raise Exception.Create(Format(SColumnNotFoundInTable,
     492            [InsertValues.Names[ValueIndex], TableName]));
    418493        end;
    419         Table.Records.Add(Row);
    420       end else raise Exception.Create(Format('Table %s not found', [TableName]));
     494      end else raise Exception.Create(Format(STableNotFound, [TableName]));
    421495    end;
    422496    InsertValues.Free;
    … …  
    436510      if Assigned(Table) then begin
    437511        Tables.Remove(Table);
    438       end else raise Exception.Create(Format('Table %s not found', [whereValue]));
     512      end else raise Exception.Create(Format(STableNotFound, [whereValue]));
     513    end else
     514    if TableName = 'ModelField' then begin
     515      Field := Table.Fields.SearchByName(WhereName);
     516      Table.Fields.Remove(Field);
    439517    end else begin
    440518      Table := Tables.SearchByName(TableName);
    … …  
    444522          Table.Records.Remove(Row);
    445523        end else raise Exception.Create('Row not found');
    446       end else raise Exception.Create(Format('Table %s not found', [TableName]));
     524      end else raise Exception.Create(Format(STableNotFound, [TableName]));
    447525    end;
    448526  end else
    449     raise Exception.Create('Unsupported SQL command ' + Command);
    450 end;
    451 
    452 procedure TDatabaseXML.LoadTables(Tables: TTables);
    453 begin
    454   Tables.Assign(Self.Tables);
     527    raise Exception.Create(Format(SUnsupportedSqlCommand, [Command]));
    455528end;
    456529
    … …  
    461534  if FileExists(FileName) then
    462535    LoadFromFile(FileName);
     536  (*
     537  TableCreateIfNotExists('Model');
     538  TableCreateIfNotExists('ModelField');
     539  TableCreateIfNotExists('DataType');
     540  TableCreateIfNotExists('Module');
     541
     542  FieldCreateIfNotExists('ModelField', 'Name', DbManager.DataTypes.SearchByType(ftString));
     543  FieldCreateIfNotExists('ModelField', 'Caption', DbManager.DataTypes.SearchByType(ftString));
     544  FieldCreateIfNotExists('ModelField', 'Model', DbManager.DataTypes.SearchByType(ftString));
     545  FieldCreateIfNotExists('ModelField', 'DataType', DbManager.DataTypes.SearchByType(ftString));
     546  FieldCreateIfNotExists('Model', 'Name', DbManager.DataTypes.SearchByType(ftString));
     547  FieldCreateIfNotExists('Model', 'Caption', DbManager.DataTypes.SearchByType(ftString));
     548  FieldCreateIfNotExists('Model', 'Module', DbManager.DataTypes.SearchByType(ftString));
     549  FieldCreateIfNotExists('Model', 'System', DbManager.DataTypes.SearchByType(ftBoolean));
     550  FieldCreateIfNotExists('DataType', 'Name', DbManager.DataTypes.SearchByType(ftString));
     551  FieldCreateIfNotExists('DataType', 'Caption', DbManager.DataTypes.SearchByType(ftString));
     552  FieldCreateIfNotExists('DataType', 'FieldType', DbManager.DataTypes.SearchByType(ftInteger));
     553  FieldCreateIfNotExists('Module', 'Name', DbManager.DataTypes.SearchByType(ftString));
     554  FieldCreateIfNotExists('Module', 'Caption', DbManager.DataTypes.SearchByType(ftString));
     555  *)
    463556end;
    464557

  • trunk/Forms/UFormFields.pas

    r22 r23  
    9191  if FormField.ShowModal = mrOk then begin
    9292    FormField.Save(NewField);
    93     Fields.Table.DbClient.Query('INSERT INTO ModelField ( Name , TextBefore) VALUES ( ' +
    94       NewField.Name + ' , ' + NewField.TextBefore + ' )');
     93    Fields.Table.DbClient.Query('INSERT INTO ModelField ( Name , Caption , Model , DataType ) VALUES ( ' +
     94      NewField.Name + ' , ' + NewField.TextBefore + ' , ' + TableName + ' , ' + NewField.DataType.Name + ' )');
    9595    ReloadList;
    9696  end else NewField.Free;
    … …  
    160160  I: Integer;
    161161begin
     162  Fields.Clear;
    162163  DbRows := TDbRows.Create;
    163164  Fields.Table.DbClient.Query('SELECT * FROM ModelField WHERE Model = ' + Fields.Table.Name, DbRows);
    164165  for I := 0 to DbRows.Count - 1 do begin
    165     NewField := TField.Create;
    166     NewField.Table := Fields.Table;
    167     Fields.Add(NewField);
     166    NewField := Fields.AddNew(DbRows[I].Values['Name'], Fields.Table.DbClient.DbManager.DataTypes.SearchByName(DbRows[I].Values['DataType']));
     167    NewField.TextBefore := DbRows[I].Values['Caption'];
    168168  end;
    169169  DbRows.Free;

  • trunk/Forms/UFormTables.pas

    r22 r23  
    77uses
    88  Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, ComCtrls,
    9   ActnList, Menus, UDatabase;
     9  ActnList, Menus, UDatabase, SpecializedDictionary;
    1010
    1111type
    … …  
    212212procedure TFormTables.FormCreate(Sender: TObject);
    213213begin
    214   FTables := TTables.Create(False);
     214  FTables := TTables.Create;
    215215end;
    216216
    … …  
    232232procedure TFormTables.ReloadList;
    233233var
     234  DbRows: TDbRows;
     235  NewTable: TTable;
    234236  I: Integer;
    235237begin
    236   if Assigned(DbClient) then DbClient.LoadTables(Tables)
    237     else Tables.Clear;
     238  Tables.Clear;
     239  if Assigned(DbClient) then begin
     240    DbRows := TDbRows.Create;
     241    DbClient.Query('SELECT * FROM Model', DbRows);
     242    for I := 0 to DbRows.Count - 1 do begin
     243      NewTable := Tables.AddNew(TDictionaryStringString(DbRows[I]).Values['Name']);
     244      NewTable.Caption := TDictionaryStringString(DbRows[I]).Values['Caption'];
     245    end;
     246    DbRows.Free;
     247  end;
     248
    238249  for I := 0 to Tables.Count - 1 do
    239250    TTable(Tables[I]).LoadRecordsCount;

  • trunk/Languages/MyData.cs.po

    r22 r23  
    99"MIME-Version: 1.0\n"
    1010"Content-Transfer-Encoding: 8bit\n"
    11 "X-Generator: Poedit 2.0.6\n"
     11"X-Generator: Poedit 2.0.4\n"
    1212"Language: cs\n"
    1313
    … …  
    6565#: tformconnect.buttonxmlbrowse.caption
    6666msgid "Browse"
    67 msgstr ""
     67msgstr "Procházet"
    6868
    6969#: tformconnect.caption
    7070msgid "Connection parameters:"
    71 msgstr ""
     71msgstr "Parametry spojení:"
    7272
    7373#: tformconnect.label1.caption
    … …  
    7979#: tformconnect.label2.caption
    8080msgid "Database engine:"
    81 msgstr ""
     81msgstr "Databázový systém:"
    8282
    8383#: tformconnect.label3.caption
    8484msgid "Xml file:"
    85 msgstr ""
     85msgstr "Xml soubor:"
    8686
    8787#: tformconnect.label4.caption
    8888msgid "Host:"
    89 msgstr ""
     89msgstr "Hostitel:"
    9090
    9191#: tformconnect.label5.caption
    9292msgid "Port:"
    93 msgstr ""
     93msgstr "Port:"
    9494
    9595#: tformconnect.tabsheetregistry.caption
    … …  
    170170#: tformfield.label2.caption
    171171msgid "Text before:"
    172 msgstr "Text za:"
     172msgstr "Text před:"
    173173
    174174#: tformfield.label3.caption
    175175msgid "Text after:"
    176 msgstr "Text před:"
     176msgstr "Text po:"
    177177
    178178#: tformfield.label4.caption
    … …  
    500500msgstr "Čas"
    501501
     502#: uenginexml.scolumnnotfoundintable
     503msgid "Column %s not found in table %s"
     504msgstr "Sloupec %d nenalezen v tabulce %s"
     505
     506#: uenginexml.stablenotfound
     507msgid "Table %s not found"
     508msgstr "Tabulka %s nenalezena"
     509
     510#: uenginexml.sunsupportedsqlcommand
     511msgid "Unsupported SQL command: %s"
     512msgstr "Nepodporovaný SQL příkaz: %s"
     513
    502514#: uenginexml.swrongfileformat
    503515msgid "Wrong file format"
    … …  
    559571msgid "Database query error: \"%s\""
    560572msgstr "Chyba požadavku databáze: \"%s\""
    561 

  • trunk/Languages/MyData.po

    r22 r23  
    475475msgstr ""
    476476
     477#: uenginexml.scolumnnotfoundintable
     478msgid "Column %s not found in table %s"
     479msgstr ""
     480
     481#: uenginexml.stablenotfound
     482msgid "Table %s not found"
     483msgstr ""
     484
     485#: uenginexml.sunsupportedsqlcommand
     486msgid "Unsupported SQL command: %s"
     487msgstr ""
     488
    477489#: uenginexml.swrongfileformat
    478490msgid "Wrong file format"

  • trunk/MyData.lpr

    r20 r23  
    1111  UFormTable, UFormRecords, UFormRecord, UFormFields, UFormField,
    1212  TemplateGenerics, UFormMain, SysUtils,
    13   UFormConnect, UFormDatabases, UFormPreferences, UDbClientRegistry;
     13  UFormConnect, UFormDatabases, UFormPreferences;
    1414
    1515{$R *.res}

  • trunk/UDatabase.pas

    r22 r23  
    7777    function SearchByName(Name: string): TField;
    7878    procedure Assign(Source: TFields);
     79    function AddNew(Name: string; DataType: TDataType): TField;
    7980  end;
    8081
    … …  
    9697    procedure Assign(Source: TRecords);
    9798    function SearchByValue(Name, Value: string): TRecord;
     99    function AddNew: TRecord;
    98100  end;
    99101
    … …  
    120122    DbClient: TDbClient;
    121123    function SearchByName(Name: string): TTable;
     124    function AddNew(Name: string): TTable;
    122125  end;
    123126
    … …  
    145148    Name: string;
    146149    Params: TDbConnectParams;
     150    DbManager: TDbManager;
    147151    destructor Destroy; override;
    148152    function GetClient: TDbClient;
    … …  
    189193    procedure SetConnectProfile(AValue: TDbConnectProfile); virtual;
    190194  public
     195    DbManager: TDbManager;
    191196    procedure Query(Text: string; DbRows: TDbRows = nil); virtual;
    192     procedure LoadTables(Tables: TTables); virtual;
    193197    constructor Create; virtual;
    194198    procedure Load; virtual;
    … …  
    350354
    351355      ConnectProfile := TDbConnectProfile.Create;
     356      ConnectProfile.DbManager := DbManager;
    352357      ConnectProfile.ClientType := ClientType;
    353358      ConnectProfile.Name := GetValue('Name', '');
    … …  
    414419  Result := ClientType.DatabaseClientClass.Create;
    415420  Result.ConnectProfile := Self;
     421  Result.DbManager := DbManager;
    416422end;
    417423
    … …  
    428434end;
    429435
     436function TTables.AddNew(Name: string): TTable;
     437begin
     438  Result := TTable.Create;
     439  Result.DbClient := DbClient;
     440  Result.Name := Name;
     441  Add(Result);
     442end;
     443
    430444{ TDbClient }
    431445
    … …  
    443457procedure TDbClient.Query(Text: string; DbRows: TDbRows = nil);
    444458begin
    445 end;
    446 
    447 procedure TDbClient.LoadTables(Tables: TTables);
    448 begin
    449   Tables.Clear;
    450459end;
    451460
    … …  
    598607end;
    599608
     609function TRecords.AddNew: TRecord;
     610begin
     611  Result := TRecord.Create;
     612  Result.Parent := Parent;
     613  Result.InitValues;
     614  Add(Result);
     615end;
     616
    600617{ TFields }
    601618
    … …  
    622639    TField(Items[I]).Assign(TField(Source.Items[I]));
    623640  end;
     641end;
     642
     643function TFields.AddNew(Name: string; DataType: TDataType): TField;
     644begin
     645  Result := TField.Create;
     646  Result.Table := Table;
     647  Result.Name := Name;
     648  Result.DataType := DataType;
     649  Add(Result);
    624650end;
    625651
Note: See TracChangeset for help on using the changeset viewer.