TMS Aurelius & reserved words in Firebird 4.0.X

BIZ TMS Aurelius
1

Hello folks,

I bought Holger Flick's book on Modern Software Development with Delphi and cloned his GitHub repository. As a first step I wanted to setup the FDConnection to Firebird 4.0.5. I dropped a TFDPhysFBDriverLink into the DataManager datamodule and saved my connection settings as

[Database]
Database=FlixAccounting
Protocol=TCPIP
Server=localhost
User_Name=sysdba
Password=masterkey
DriverID=FB

(e.g. I use a database alias in databases.conf for the database location).

I know that I have to be cautious regarding the TAureliusConnection.SQLDialect. I left that empty as I only have a SQLDialect of Firebird but not Firebird3 in my drop-down list for that property.

When the database tables are created by Aurelius I got an error on the following SQL command generated by Aurelius:

CREATE TABLE QUICK_ITEM (
CATEGORY VARCHAR(255) NOT NULL,
Name VARCHAR(255),
ID INTEGER NOT NULL,
QUANTITY DOUBLE PRECISION NOT NULL,
VALUE DOUBLE PRECISION NOT NULL,
DESCRIPTION VARCHAR(255) NOT NULL,
CONSTRAINT PK_QUICK_ITEM PRIMARY KEY (ID),
CONSTRAINT UK_5B1FFB01_QUICK_ITEM UNIQUE (Name))

I specified column attributes in the model for TQuickItem.Value & TInvoiceItem.Value for the reserved word VALUE in Firebird:

[Column('"VALUE"')]
FValue: Double;

.. and the database structure was created in Firebird.

Is this the official path of using reserved words as properties in a model class? I looked at the TAnsiSQLGenerator.GenerateFieldDefinition and it doesn't seem to use the TAnsiSQLGenerator.QuoteIdentifiers property.

Is there a better way to make Aurelius aware of reserved words in the different database servers it supports?

Thanks for a short answer in advance.

Salut, Mathias

2

I don't think this property attribute is the correct solution. Whilst the database is now created correctly, when I restart the application the column Value on table Invoice_Item is not found (e.g. in TDatabaseManager.GetUpdateDatabaseCommands - CompareTables) and the column is marked as removed. As a result the DDL command

ALTER TABLE INVOICE_ITEM
  ADD "VALUE" DOUBLE PRECISION

is executed, which obviously isn't going to work:

ALTER TABLE INVOICE_ITEM failed
violation of PRIMARY or UNIQUE KEY 
constraint "RDB$INDEX_15" on table "RDB$RELATION_FIELDS"
Problematic key value is 
("RDB$FIELD_NAME" = 'VALUE', 
 "RDB$RELATION_NAME" = 'INVOICE_ITEM')

So may I ask what is the correct method for reserved words?

Salut, Mathias

3

Hi @Mathias_Burbach,

The best alternative is to use OnGetIdName event, this way:

4

Hello Wagner,

Thanks for your reply. I defined a private method called TDataManager.SupportReservedWordsForFirebird and used your technique of defining a OnGetIdName event handler and it works. :slightly_smiling_face:

But since there is already a TFirebirdSQLGenerator in the Aurelius framework, wouldn't that be the place to aim supporting Firebird as much as possible by making it reserved-word-aware? All reserved words for Firebird are listed here.

Just curious.

Salut,
Mathias

5

We didn't want to make it too strict.

First, we would have to do it for all databases supported by Aurelius.

Second, the reserved words might vary from version to version - and the same generator might be used for several different versions.

So we let this to be less strict and if users struggle with it they can easily solve it the way you did.