Update different database versions

BIZ TMS Aurelius
1

Hi Wagner
I have a situation, with database update.
The tools I'm using: Delphi 10.2, firebird, Aurelius (last version), Datamodeler (last version).

I start by generating Schema with Datamodeler "Schema.pas", then I run the update routine,
something like:

procedure UpdateDatabase(vConnection: IDBConnection; aDBVersion: Integer);
var
  DBManager: TDatabaseManager;
  fFDScript: TFDScript;
begin
  if version= 18 then
  begin
    ..
    aFDScript.SQLScripts[0].SQL.Add('ALTER TABLE Ent_Pro ALTER COLUMN Canceled SET NOT NULL;');
    ...
    DBManager := TDatabaseManager.Create(vConnection);
    DBManager.UpdateDatabase;// Update database with Schema.pas (Version 18)????
    ...
    aFDScript.SQLScripts[0].SQL.Add('INSERT INTO SETTING (ID, KEYSECTION, KEYFIELD, KEYVALUE, KEYVALUETYPE, RECVERSION) VALUES (Gen_Id(ID_SETTING,1), ''Section 1'', ''Code'', ''0'', 1, current_timestamp)');
    aFDScript.SQLScripts[0].SQL.Add('ALTER TABLE InvItem ALTER TaxPercent SET DEFAULT 0' );
    ...
  end;

  if version= 19 then
  begin
    ...
    aFDScript.SQLScripts[0].SQL.Add('ALTER TABLE Item ALTER COLUMN Enabled SET NOT NULL;');
    ...
    DBManager := TDatabaseManager.Create(vConnection);
    DBManager.UpdateDatabase;// // Update database with Schema.pas (Version 19)????
    ...
    aFDScript.SQLScripts[0].SQL.Add('ALTER TABLE Item ALTER Enabled SET DEFAULT 0' );
    ...
  end;

  if version= 20 then
  begin
    ...
    DBManager.UpdateDatabase;// Update database with Schema.pas (Version 20)????
    ...
  end;
end;

I need to save "schema.pas" for all versions, so when upgrade from version V17 to V20, can run
"DBManager.UpdateDatabase" for V18, V19, V20 schema. At this point I only have the last schema,
in this case only the V20, any sugestion how I can update several versions?
any idea how to save multiple versions of schema?

Thanks

2

You can use multimodel design, where you generate the Schema marking all classes with a specific model attribute, for example [Model('Schema18')].

Then when you create TDatabaseManager, you can pass a specific mapping explorer to it, using the model name.


DBManager := TDatabaseManager.Create(vConnection, TMappingExplorer.Get('Schema18'));

You will have to keep separate units, Schema18.pas, Schema19.pas, etc..
3
I had thought of that, but how to set model attribute with the datamodele?

4

You can fill in the model name individually in the "advanced" tab (there is a field for Model).

Or you can update automatically from a diagram name (create a diagram named Schema18 e put all tables there), check the end of this chapter: http://www.tmssoftware.biz/business/dmodeler/doc/web/mappings-tab.html

Or use customization script to add a Model attribute from the script. Something like the following (not tested):



procedure OnClassGenerated(Args: TClassGeneratedArgs);
var                                                    
  Attr: TCodeAttributeDeclaration;
begin                                             
    Attr := Args.CodeType.AddAttribute('Model');
    Attr.AddRawArgument('''' + 'Schema18' + '''');
  end;
end;

5

"You can fill in the model name individually in the "advanced" tab (there is a field for Model)."
R: not a option, 300 Tables

"Or you can update automatically from a diagram name (create a diagram named Schema18 e put all tables there), ..."
R: not a option, 300 Tables. And same schema have different diagrams, for instance, Invoice Diagram, Customer Diagram, ....

"Or use customization script to add a Model attribute from the script. ..."
R: The attribute is add OK, but... i just don't understand Wagner, but i add schema18.pas, schema19.pas and schema20.pas
   with same classes to the project?

Sorry...

Thanks

6

Adding all tables to a diagram is a single-click. The same table can be present in more than one diagram.

Yes.
7

Hi, Wagner
 Consider:    
//Old Schema
unit DB.Schema;
...  
  [Entity]
  [Table('Tabel1')]
  [Id('FId', TIdGenerator.None)]
  TTabel1 = class
  private
    [Column('Id', [TColumnProp.Required])]
    FId: Integer;
    
    [Column('Field1', [], 50)]
    FField1: Nullable<string>;
  public
    property Id: Integer read FId write FId;
    property Field1: Nullable<string> read FField1 write FField1;
  end;

//New Schema
unit DB.Schema20;
... 
  [Entity]
  [Table('Tabel1')]
  [Id('FId', TIdGenerator.None)]
  [Model('Schema20')]
  TTabel1 = class
  private
    [Column('Id', [TColumnProp.Required])]
    FId: Integer;
   
    [Column('Field1', [], 50)]
    FField1: Nullable<string>;

    [Column('Field1', [], 50)]
    FNewFieldSchema20: Nullable<string>;

  public
    property Id: Integer read FId write FId;
    property Field1: Nullable<string> read FField1 write FField1;
  end;

  ...
  var
    Schema20: TArray<TClass>;
  ...
    Schema20 = TMappingExplorer.Get('Schema20');
    "DBManager := TDatabaseManager.Create(vConnection, Schema20);"
  ...  

  what Schema is returned, DBSchema.pas or DBSchema20.pas? in this case Schema20 is empty!
  i think Get('Schema20') is "looking" in wrong unit, Schema.pas instead of Schema20.pas
  what i'm missing?

Many thanks
 
 

8

TMappingExplorer.Get doesn't return a TArray<TClass>. That code should not even compile, so how are you being able to run it?

You need to make sure all classes are registered with RegisterEntity otherwise if they are not used anywhere, linker will remove it from final executable.
9
Sorry,
 Setup20 := TMappingSetup.Create;
  Setup20.MappedClasses.RegisterClasses(TMappedClasses.GetModelClasses('Schema20'));
  DBManager := TDatabaseManager.Create(vConnection, TMappingExplorer.Get('Schema20'));

as I said, TMappedClasses.GetModelClasses('Schema20'), result empty

10

You need to make sure all classes are registered with RegisterEntity otherwise if they are not used anywhere, linker will remove it from final executable.

Wagner R. Landgraf2018-11-12 21:02:40

11
Hi, Wagner
  Is there a sample? i can not make this work.
thanks
12

Can you please try to reduce your existing code to a compilable project with SQLite and send to us? We will then send the project back to you, working.

13

Hi Wagner
  You could alter AureliusGettingStarted sample:

Add Units to the project:

unit EntitiesV2;
interface
uses Aurelius.Mapping.Attributes ;

type
  [Entity]
  [Automapping]
  [Model('EntitiesV2')]
  TPerson = class
  private
    FId: integer;
    FLastName: string;
    FFirstName: string;
    FEmail: string;
    FNewFieldV2: string;
    function GetFullName: string;
  public
    property Id: integer read FId;
    property LastName: string read FLastName write FLastName;
    property FirstName: string read FFirstName write FFirstName;
    property Email: string read FEmail write FEmail;
    property NewFieldV2: string read FNewFieldV2 write FNewFieldV2;
  public
    property FullName: string read GetFullName;
  end;

implementation
{ TPerson }
function TPerson.GetFullName: string;
begin
  Result := FirstName + ' ' + LastName;
end;

initialization
//  RegisterEntity(TPerson);
end.

unit EntitiesV3;
interface
uses Aurelius.Mapping.Attributes;

type
  [Entity]
  [Automapping]
  [Model('EntitiesV3')]
  TPerson = class
  private
    FId: integer;
    FLastName: string;
    FFirstName: string;
    FEmail: string;
    FNewFieldV2: string;
    FNewFieldV3: string;
    function GetFullName: string;
  public
    property Id: integer read FId;
    property LastName: string read FLastName write FLastName;
    property FirstName: string read FFirstName write FFirstName;
    property Email: string read FEmail write FEmail;
    property NewFieldV2: string read FNewFieldV2 write FNewFieldV2;
    property NewFieldV3: string read FNewFieldV3 write FNewFieldV3;
  public
    property FullName: string read GetFullName;
  end;

implementation
{ TPerson }
function TPerson.GetFullName: string;
begin
  Result := FirstName + ' ' + LastName;
end;

initialization
//  RegisterEntity(TPerson);
end.


then alter:

procedure TForm1.btUpdateSchemaClick(Sender: TObject);
var
  Setup: TMappingSetup;
  fMappedClasses: TArray<TClass>;
  fClass: TClass;
begin
  //Update/Create Version 1*************
  fMappedClasses := TMappedClasses.GetModelClasses('Default');
  Setup := TMappingSetup.Create;
  try
    Setup.MappedClasses.RegisterClasses(TMappedClasses.GetModelClasses('Default'));
//    TMappingExplorer.ReplaceDefaultInstance(TMappingExplorer.Create(Setup));
  finally
    Setup.Free;
  end;
  TDatabaseManager.Update(Connection);
  ShowMessage('Database updated V1.');

  //Update Version 2*************
  fMappedClasses := TMappedClasses.GetModelClasses('EntitiesV2');
  for fClass in fMappedClasses do
  begin
    fClass.ClassName;  //fMappedClasses is empty!
  end;
  Setup := TMappingSetup.Create;
  try
    Setup.MappedClasses.RegisterClasses(TMappedClasses.GetModelClasses('EntitiesV2'));
    TMappingExplorer.ReplaceDefaultInstance(TMappingExplorer.Create(Setup));//needed?
  finally
    Setup.Free;
  end;
  TDatabaseManager.Update(Connection);
  ShowMessage('Database updated V2.');

  //Update Version 2*************
  fMappedClasses := TMappedClasses.GetModelClasses('EntitiesV3');
  for fClass in fMappedClasses do
  begin
    fClass.ClassName;  //fMappedClasses is empty!
  end;
  Setup := TMappingSetup.Create;
  try
    Setup.MappedClasses.RegisterClasses(TMappedClasses.GetModelClasses('EntitiesV3'));
    TMappingExplorer.ReplaceDefaultInstance(TMappingExplorer.Create(Setup));//needed?
  finally
    Setup.Free;
  end;
  TDatabaseManager.Update(Connection);
  ShowMessage('Database updated V3.');

end;

Thanks

14

As I have mentioned before: "You need to make sure all classes are registered with RegisterEntity otherwise if they are not used anywhere, linker will remove it from final executable.".


Why does your comments exactly the RegisterEntity call, like this:

initialization
//  RegisterEntity(TPerson);
end.
15
Hi, Wagner
it's not supposed,

Setup.MappedClasses.RegisterClasses(TMappedClasses.GetModelClasses('Default'));
Setup.MappedClasses.RegisterClasses(TMappedClasses.GetModelClasses('EntitiesV2'));
Setup.MappedClasses.RegisterClasses(TMappedClasses.GetModelClasses('EntitiesV3'));

Register all entitys in Models?
16

If a class is not used anywhere, linker will remove it from final executable. GetModelClasses can't return a class that doesn't exist in executable.
Wagner R. Landgraf2018-11-19 19:13:24

17
That's what i'm try to say :)

18

Back to the first post,...

19

Could you please uncomment the calls to RegisterEntity? That's probably the cause of the problems.

20

Hi, Wagner
  Since Version 4.4:
    ...
    Fixed : It's now possible to have two Aurelius entity class with same name in the same model (e.g, TCity in Unit1 and TCity in Unit2).
    ...
  Reopen this post :)

  Considere the same sample, with uncomment "initialization RegisterEntity(TPerson)", and adding some lines to

procedure TForm1.btSavePersonClick(Sender: TObject);
var
  Person: TPerson;
  fManager: TObjectManager;
  fSetup: TMappingSetup;
  fMappingExplorer: TMappingExplorer;
  fEntities: String;
begin
  fEntities := 'Entities';
  fSetup := TMappingSetup.create;
  try
    fSetup.MappedClasses.RegisterClasses(TMappedClasses.GetModelClasses(fEntities));
    fMappingExplorer := TMappingExplorer.Create(fSetup);
  finally
    fSetup.Free;
  end;
  //Creating a manager to MappingExplorer "Entities"
  fManager := TObjectManager.Create(Connection, fMappingExplorer);
   
  Person := fManager.Find<TPerson>(1);
  ...
end;

  Exception
  "raised exception class EClassNotRegistered with message 'Class TPERSON is not registered. Register it in the MappedEntities.'."

  if remove Entities2 and Entities3, from the project, works as aspected. 
  if fEntities = 'EntitiesV3', works as aspected. 

What is wrong?
Thanks