I took the BIZ component, expecting to generate and upgrade databases from Data Modeler as well as via Entity.Pas use the same in my projects and use Aurelius.
However, Data Modeler and Aurelius are not compatible in entity names and field types, so it is not possible to check the database and it is impossible to upgrade the database through Aurelius (ValidateDatabase, UpdateDatabase).
Attached are images with examples where DataModeler in Entity.pas does not generate key names that are specified in DataModeler. There are no such names in Entity.pas, but Aurelius generates them again according to its own rules.
This with names would still somehow pass, because Aurelius would generate its key name in the database during UpdateDatabase and ok, I would accept it as such and ok.
The biggest problem is that Aurelius doesn't see that I put 'nvarchar' for the field type (due to national characters), Aurelius only sees 'string' and expects 'varchar', of course UpdateDatabase only sees errors there.
Due to the above, it is impossible to seriously use Data Modeler and Aurelius, due to the check I largely took the BIZ package (of course I expect a lot from xData and other components)
Is there a way to reconcile this, to have Entity.pas generated in Data Modeler serve to validate and upgrade the actual database at the start of my project. To automate this process.
I uploaded pictures with examples
I'm sorry the images escaped before the query and I couldn't connect them to the text, but I think the point is clear
Hello,
Foreign key names: You can tell Data Modeler to use the specified foreign key name in Aurelius by adding a customization script like this: Adding ForeignKey Attribute to Associations
Unique constraint names: For now, you can't provide Aurelius with the unique key name, indeed. Aurelius will create it using its own autogenerated name. This is a feature in our backlog to be implemented in feature.
Ansi/Wide strings: Aurelius consider all mapped string fields/properties as "ANSI" strings (Varchar). To tell Aurelius you want them to be considered "Unicode" (NVarchar), you can define the type of the field/property as Widestring, or you can enable a global config property MapStringToNationalChar thats tell Aurelius to treat all strings as NVarchar. Some references:
Finally, Data Modeler provides the customization script feature which gives you full flexibility to fine tune your Aurelius output and have it automated at the same time so in every new generation your customizations will be applied through the script.
Thank You for the quick response
Foreign key names: I entered the script and that's fine now.
- the problem of Primary key and Unique key names remains
- it is not clear to me why you do not simply generate all the names given in Data modeler in Entity.pas, and Aurelius to simply use those names and not to generate some of its own new names
- There are generally few instructions for the user, do you have more examples or instructions on how to use the scripts
Ansi / Wide strings: I set MapStringToNationalChar: = true and got that all string fields are now 'nvarchar' and only 'nvarchar' can be used in the Data modeler - no more errors with UpdateDatabase and that's fine
There is still the problem that the upgrade of the database cannot be done if there is data in it, as far as I noticed.
If I am wrong I would ask for advice on how to upgrade a database that has data with the generated scripts (DBManager.UpdateDatabase sql script).
UpdateDatabase should also work with data, otherwise it is necessary to write sql scripts for the upgrade.
Attached is one such script, for the database from the example I described. The developer (field length, field compatibility, etc.) should take care of possible problems with the upgrade. This script does this elegantly over temp objects.
Users upgrade with data.zip (766 Bytes)
As explained, primary key and unique key names are not created/modified by Aurelius.
In the Data Modeler documentation there are several script examples for different tasks that you can use as a base for other tasks, and also learn from it.
Also, TMS Aurelius documentation explains the limitations and behavior of the update database when it comes to existing data and destructive commands. Some update operations are not performed indeed.
Here is a key snippet from it:
Since this method performs on a database that has existing object and data, it has some limitations. First, if you are unsure of the effects of schema update, it's strongly recommended that you check schema validation results before updating. Errors might occur when updating the schema, for example, if new schema requires a foreign key creating but existing data doesn't fit into this new constraint. See schema validation for a list of current valid operations and limitations.
Note that UpdateDatabase is a non-destructive method. This means that even if the validation reports that a data-holding object (table or column) needs to be dropped, the SQL statement for it will not be performed.
Hallo,
'As explained, primary key and unique key names are not created / modified by Aurelius.'
- when I create a new database without tables and call Aurelius 'UpdateDatabase', Aurelius creates tables, keys, etc. based on Entuty.pas - after adding a script for foreignkey, foreignkey takes the name from Data Modeler, but the names of primary and unique keys are generated by Aurelius ( does not take names from Data Modeler, there are no names in Entity.pas, database tables do not exist - so only Aurelius could generate those names)
- if there is a key with another name in the database - Aurelius will report that the key does not exist and will offer an 'alter' key with its name
As for the documentation, I apologize, there is enough, never enough for a beginner :)
I read about UpdateDatabase limitations.
UpdateDatabase is quite useless with this non-destructive mode. It reacts to the slightest changes to the database and does nothing, to give us a sql script (where we see problems) which is also useless because it cannot be applied to a database with data.
If UpdateDatabase had a 'destructive' option and generated a script like the one I sent you, it would be possible to do a fully automatic database upgrade with UpdateDatabase. In that case, the developer would have to take care of the limitations in such an upgrade.
During project construction it is often necessary to add a field, change the length of the field, change the number of decimals and the like - these are generally not dangerous actions and it would be possible to perform them at the start of the application - painlessly.
I'm sorry that I'm just criticizing at the moment, I'm new to Delphi and Aureilius, but I've been programming for a long time, so I did and programmed such upgrades myself (other programming languages, other databases), I expected that I wouldn't have to with Data Modeler work from scratch again.
By the way, I am very satisfied with the other TMS products that I use for now, and I am slowly introducing new ones.
Thank you very much for your help, I think we have completed this, we will use the part that suits us, and we will manage for the rest, until possible changes on this issue.
1 Like
We are always improving Aurelius, as you can see from the what's new topic in documentation - five significant releases only this year. That's something we for sure want to improve.
This topic was automatically closed 60 minutes after the last reply. New replies are no longer allowed.