Duplicate column name on BuildDatabase or UpdateDatabase

[van_den_Berg_Mark]

Hi,

I have declared some entities, derived from an abstract entity (FYI: the entity hierarchy is Well -> Wellbore -> Operation):

uses
  SysUtils,
  Generics.Collections,
  Aurelius.Mapping.Attributes,
  Aurelius.Types.Blob,
  Aurelius.Types.DynamicProperties,
  Aurelius.Types.Nullable,
  Aurelius.Types.Proxy,
  Entities.WellDoc_Enums;

type
  TBaseEntity = class;
  TOperation = class;
  TWell = class;
  TWellbore = class;

  [AbstractEntity]
  [Table('BaseEntity')]
  [Id('FID', TIdGenerator.Uuid36)]
  TBaseEntity = class
  private
    [Column('ID', [TColumnProp.Required], 36)]
    FID: string;

    [Column('ParentID', [], 36)]
    FParentID: Nullable<string>;

    [Column('Created', [])]
    FCreated: Nullable<TDateTime>;

    [Column('Modified', [])]
    FModified: Nullable<TDateTime>;

    [Column('RowVersion', [TColumnProp.Required])]
    [Version]
    FRowVersion: Integer;

    [Column('QC_Level', [])]
    FQC_Level: TQC_Level;
  public
    property Id: string read FID write FID;
    property ParentID: Nullable<string> read FParentID write FParentID;
    property Created: Nullable<TDateTime> read FCreated write FCreated;
    property Modified: Nullable<TDateTime> read FModified write FModified;
    property RowVersion: Integer read FRowVersion write FRowVersion;
    property QC_Level: TQC_Level read FQC_Level write FQC_Level;
  end;

  [Entity]
  [Table('Operation')]
  TOperation = class(TBaseEntity)
  private
    [Column('OperationType', [TColumnProp.Required], 10)]
    FOperationType: string;

    [Column('OperationName', [TColumnProp.Required], 50)]
    FOperationName: string;

    [Association([TAssociationProp.Lazy, TAssociationProp.Required], CascadeTypeAll - [TCascadeType.Remove])]
    [JoinColumn('ParentID', [TColumnProp.Required], 'ID')]
    FWellbore: Proxy<TWellbore>;
    function GetWellbore: TWellbore;
    procedure SetWellbore(const Value: TWellbore);
  public
    property OperationType: string read FOperationType write FOperationType;
    property OperationName: string read FOperationName write FOperationName;
    property Wellbore: TWellbore read GetWellbore write SetWellbore;
  end;

  [Entity]
  [Table('Well')]
  TWell = class(TBaseEntity)
  private
    [Column('WellName', [TColumnProp.Required], 50)]
    FWellName: string;

    [Column('WellOperator', [TColumnProp.Required], 50)]
    FWellOperator: string;

    [ManyValuedAssociation([TAssociationProp.Lazy, TAssociationProp.Required], [TCascadeType.SaveUpdate,
      TCascadeType.Merge, TCascadeType.Remove], 'FWell')]
    FWellbores: Proxy<TList<TWellbore>>;
    function GetWellbores: TList<TWellbore>;
  public
    constructor Create;
    destructor Destroy; override;
    property WellName: string read FWellName write FWellName;
    property WellOperator: string read FWellOperator write FWellOperator;
    property Wellbores: TList<TWellbore> read GetWellbores;
  end;

  [Entity]
  [Table('Wellbore')]
  TWellbore = class(TBaseEntity)
  private
    [Column('Rank', [TColumnProp.Required])]
    FRank: Integer;

    [Column('WellboreName', [TColumnProp.Required], 50)]
    FWellboreName: string;

    [Column('MD_Start', [TColumnProp.Required])]
    FMD_Start: Double;

    [Association([TAssociationProp.Lazy, TAssociationProp.Required], CascadeTypeAll - [TCascadeType.Remove])]
    [JoinColumn('ParentID', [TColumnProp.Required], 'ID')]
    FWell: Proxy<TWell>;

    [ManyValuedAssociation([TAssociationProp.Lazy, TAssociationProp.Required], [TCascadeType.SaveUpdate,
      TCascadeType.Merge, TCascadeType.Remove], 'FWellbore')]
    FOperations: Proxy<TList<TOperation>>;
    function GetWell: TWell;
    procedure SetWell(const Value: TWell);
    function GetOperations: TList<TOperation>;
  public
    constructor Create;
    destructor Destroy; override;
    property Rank: Integer read FRank write FRank;
    property WellboreName: string read FWellboreName write FWellboreName;
    property MD_Start: Double read FMD_Start write FMD_Start;
    property Well: TWell read GetWell write SetWell;
    property Operations: TList<TOperation> read GetOperations;
  end;

When I run a quick test application and perform a DBSchema.BuildDatabase or UpdateDatabase, I get the following exception:

Project VclTest.exe raised exception class EAureliusOdbcException with message 'Error -1: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column names in each table must be unique. Column name 'ParentID' in table 'Operation' is specified more than once.'.

Checking the SQL executed (in TSQLPerformer.ExecuteSQL), this is indeed the case:

CREATE TABLE Operation (
  ID VARCHAR(36) NOT NULL,

  ParentID VARCHAR(36) NULL,            // <<<<<<<

  Created DATETIME NULL,
  Modified DATETIME NULL,
  RowVersion INT NOT NULL,
  QC_Level INT NULL,
  OperationType VARCHAR(10) NOT NULL,
  OperationName VARCHAR(50) NOT NULL,

  ParentID VARCHAR(36) NOT NULL,         // <<<<<<< (and not null ??)

  CONSTRAINT PK_Operation PRIMARY KEY (ID))

At first I thought it was somehow associated to the use of [AbstractEntity], but changing this to [Entity] had no effect.

I noticed that in several examples, AutoMapping was used. For the sake of trying, I added , AutoMapping to all (base)entity attributes and commented out all [Column] attributes:

 [AbstractEntity, AutoMapping]
  [Table('BaseEntity')]
  [Id('FID', TIdGenerator.Uuid36)]
  TBaseEntity = class
  private
    // [Column('ID', [TColumnProp.Required], 36)]
    FID: string;

    // [Column('ParentID', [], 36)]
    FParentID: Nullable<string>;

    // [Column('Created', [])]
    FCreated: Nullable<TDateTime>;

    // [Column('Modified', [])]
    FModified: Nullable<TDateTime>;

    // [Column('RowVersion', [TColumnProp.Required])]
    [Version]
    FRowVersion: Integer;

    // [Column('QC_Level', [])]
    FQC_Level: TQC_Level;
  public
    property Id: string read FID write FID;
    property ParentID: Nullable<string> read FParentID write FParentID;
    property Created: Nullable<TDateTime> read FCreated write FCreated;
    property Modified: Nullable<TDateTime> read FModified write FModified;
    property RowVersion: Integer read FRowVersion write FRowVersion;
    property QC_Level: TQC_Level read FQC_Level write FQC_Level;
  end;

  [Entity, AutoMapping]
  [Table('Operation')]
  TOperation = class(TBaseEntity)
  private
    // [Column('OperationType', [TColumnProp.Required], 10)]
    FOperationType: string;

    // [Column('OperationName', [TColumnProp.Required], 50)]
    FOperationName: string;

    [Association([TAssociationProp.Lazy, TAssociationProp.Required], CascadeTypeAll - [TCascadeType.Remove])]
    [JoinColumn('ParentID', [TColumnProp.Required], 'ID')]
    FWellbore: Proxy<TWellbore>;
    function GetWellbore: TWellbore;
    procedure SetWellbore(const Value: TWellbore);
  public
    property OperationType: string read FOperationType write FOperationType;
    property OperationName: string read FOperationName write FOperationName;
    property Wellbore: TWellbore read GetWellbore write SetWellbore;
  end;

  [Entity, AutoMapping]
  [Table('Well')]
  TWell = class(TBaseEntity)
  private
    // [Column('WellName', [TColumnProp.Required], 50)]
    FWellName: string;

    // [Column('WellOperator', [TColumnProp.Required], 50)]
    FWellOperator: string;

    [ManyValuedAssociation([TAssociationProp.Lazy, TAssociationProp.Required], [TCascadeType.SaveUpdate,
      TCascadeType.Merge, TCascadeType.Remove], 'FWell')]
    FWellbores: Proxy<TList<TWellbore>>;
    function GetWellbores: TList<TWellbore>;
  public
    constructor Create;
    destructor Destroy; override;
    property WellName: string read FWellName write FWellName;
    property WellOperator: string read FWellOperator write FWellOperator;
    property Wellbores: TList<TWellbore> read GetWellbores;
  end;

  [Entity, AutoMapping]
  [Table('Wellbore')]
  TWellbore = class(TBaseEntity)
  private
    // [Column('Rank', [TColumnProp.Required])]
    FRank: Integer;

    // [Column('WellboreName', [TColumnProp.Required], 50)]
    FWellboreName: string;

    // [Column('MD_Start', [TColumnProp.Required])]
    FMD_Start: Double;

    [Association([TAssociationProp.Lazy, TAssociationProp.Required], CascadeTypeAll - [TCascadeType.Remove])]
    [JoinColumn('ParentID', [TColumnProp.Required], 'ID')]
    FWell: Proxy<TWell>;

    [ManyValuedAssociation([TAssociationProp.Lazy, TAssociationProp.Required], [TCascadeType.SaveUpdate,
      TCascadeType.Merge, TCascadeType.Remove], 'FWellbore')]
    FOperations: Proxy<TList<TOperation>>;
    function GetWell: TWell;
    procedure SetWell(const Value: TWell);
    function GetOperations: TList<TOperation>;
  public
    constructor Create;
    destructor Destroy; override;
    property Rank: Integer read FRank write FRank;
    property WellboreName: string read FWellboreName write FWellboreName;
    property MD_Start: Double read FMD_Start write FMD_Start;
    property Well: TWell read GetWell write SetWell;
    property Operations: TList<TOperation> read GetOperations;
  end;

This works and creates the tables as expected! However, I am failing to understand why. Is this a bug in creating the required SQL or am I missing/misunderstanding a certain requirement (e.g. on using AbstractEntity with associations)?

[wlandgraf (Wagner Landgraf)]

You are indeed declaring the ParentId column twice. You declaring it in the abstract entity, through the FParentId field, and then you are declaring it again in TOperation class in this part:

    [Association([TAssociationProp.Lazy, TAssociationProp.Required], CascadeTypeAll - [TCascadeType.Remove])]
    [JoinColumn('ParentID', [TColumnProp.Required], 'ID')]
    FWellbore: Proxy<TWellbore>;
    function GetWellbore: TWellbore;
    procedure SetWellbore(const Value: TWellbore);

[van_den_Berg_Mark]

OK, so what you're saying is that the JoinColumn attribute also declares the ParentID column (i.e. again, as the base entity also declares it too)?

But then why can it work correctly when I use AutoMapping? If JoinColumn does indeed declare the ParentID column, I would still expect this to fail.

PS: I am using the "AutoMapping version" now and so far it all works flawlessly, so this is more about understanding the "why"...

[wlandgraf (Wagner Landgraf)]

@van_den_Berg_Mark

OK, so what you're saying is that the JoinColumn attribute also declares the ParentID column (i.e. again, as the base entity also declares it too)?

Yes.

@van_den_Berg_Mark

But then why can it work correctly when I use AutoMapping? If JoinColumn does indeed declare the ParentID column, I would still expect this to fail.

Because naming convention for automapping is to add underline _ to the camel case names. Thus, in your base entity class, the field FParentID is probably mapped to PARENT_ID column, which is different from the ParentID you declared in JoinColumn attribute.

Check your Operation table, you probably have columns PARENT_ID and ParentID.

By the way, the Table attribute you declared in TBaseEntity makes no sense, there are no tables associated with abstract entities.

[van_den_Berg_Mark]

By the way, the Table attribute you declared in TBaseEntity makes no sense, there are no tables associated with abstract entities.

Clearly. I missed that one when switching between Entity and AbstractEntity.

You're indeed correct about the "extra" field being created:

Does this now mean that I cannot use an AbstractEntity if I need to have associations in the derived classes? Having a duplicate ParentID becomes very confusing and is - to my opinion - not desirable.

I previously had a Data Modeler script that "painted" various classes with common properties (fields). For example:

procedure OnClassGenerated(Args: TClassGeneratedArgs);
var
  ii: Integer;
  Attr: TCodeAttributeDeclaration;
  x: TCodeMemberField;
begin
    .......   
    // Add Date Created field to all classes
    x := Args.CodeType.AddField('FCreated', 'DateTime', mvPrivate);
    x.AddAttribute('Column(''Created'', [])');      
    Args.CodeType.AddProperty('Created', 'DateTime','FCreated', 'FCreated', mvPublic);  
    .......    
end;

Whilst this works perfectly fine, it gives far more "clutter" in the descendant classes and is more complex to maintain. The AbstractEntity also makes it far more elegant to set/update common fields (e.g. Created and Modified in a VCL test application). This option is now lost:

procedure TfrmVclClient.SubscribeEvents;
begin
  TMappingExplorer.Default.Events.OnInserting.Subscribe(
    procedure(Args: TInsertingArgs)
    begin
      if Args.Entity is TBaseEntity then
      begin
        (Args.Entity as TBaseEntity).Created := Now;
        (Args.Entity as TBaseEntity).Modified := (Args.Entity as TBaseEntity).Created;
      end;
    end);

  TMappingExplorer.Default.Events.OnUpdating.Subscribe(
    procedure(Args: TUpdatingArgs)
    begin
      if Args.Entity is TBaseEntity then
      begin
        (Args.Entity as TBaseEntity).Modified := Now;
      end;
    end);
end;

It seems a shame not being able to use AbstractEntity with associations in descendants. After all, it's object oriented programming. Is there a way in which this can be achieved or should I stick to the script option and "decorate" all entities with common fields?

PS: I typically like to work with a entities unit I create from Data Modeler ("raw") which I copy to a final unit ("production"). This saves a lot of typing Later I can then add/modify etc and reproduce "raw" (overwrite) and adjust "production" by copy & paste from "raw" (if that makes sense )

[van_den_Berg_Mark]

PS 2:

It becomes even more confusing if - through a simple test application - I add data. As you can see in the screenshot below, the ParentID field (inherited from the AbstractEntity) is used for linking the entity to its parent:

I set the link to the parent record as follows:

procedure TfrmVclClient.adsOperationBeforePost(Dataset: TDataSet);
begin
  if adsOperationWellbore.AsObject = nil then
    adsOperationWellbore.AsObject := adsWellboreSelf.AsObject;
end;

The foreign key for the table (e.g. Operation) is also created on the inherited ParentID field.

So all together it appears that The PARENT_ID field, created by the association, is completely ignored (= NULL).

However, looking at the persistent fields created for the dataset (via a built & installed package), it appears as if PARENT_ID was used:

[wlandgraf (Wagner Landgraf)]

@van_den_Berg_Mark

It seems a shame not being able to use AbstractEntity with associations in descendants. After all, it's object oriented programming.

I don't see the relation at all. Your "object-oriented programming" is doing exactly what Aurelius is also doing: you are declaring a property in the ancestor, and you are declaring a property in the descendant class that has absolutely nothing to do with the ancestor one. Two different, unrelated properties.

@van_den_Berg_Mark

Is there a way in which this can be achieved

What do you want to achieve, exactly? You declared two different properties in two different classes. What is your goal?

@van_den_Berg_Mark

However, looking at the persistent fields created for the dataset (via a built & installed package), it appears as if PARENT_ID was used:

The names of persistent fields are based on the class properties, not the underlying database column names. Different things.

Well, if your goal is to get the parent id of a generic class, even without knowing what and if the class has a parent, then indeed, you should use object-oriented programming, instead of what you are doing.

For example, you can create a virtual method GetParentId in your abstract class, and then implement it in descendants accordingly to what descendants do. And maybe return -1 when the entity doesn't have a parent.

[van_den_Berg_Mark]

I think I now seeing where my thinking was wrong. I was too much along the line in which objects (PODO) are normally associated.

I assumed (wrongly) that I needed ParentID in the ancestor so that descendants could use it to link to the parent. As I now see, that is not required. Simply defining the JoinColumn attribute in the descendant will create the column required to store it (the ID of the parent). I will simply remove the ParentID property from the ancestor and all should be OK.

I hope this makes sense and you see where I went wrong. It's often difficult to express these subtle nuances in writing.

EDIT:

Confirmed : Simply removing the ParentID property from the AbstractEntity was all that was needed. The tables are now created with one ParentID column, which is used for linking the parent entity as required. Apologies for creating confusion!