Please provide support for MySQL ENUM type

[WIERZBICKI_Stephane1 (WIERZBICKI Stéphane)]

Hello,

I'm trying to import an existing MySQL database.
Several tables do use enum type, like hereunder :

CREATE TABLE `frames` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `client_id` bigint(20) unsigned NOT NULL,
  `limit` bigint(20) unsigned NOT NULL,
  `evaluated_at` date NOT NULL,
  `expires_at` date NOT NULL,
  `invoicing_frequency` enum('MONTHLY','QUARTERLY','DAILY','INDIVIDUAL','YEARLY','YEARLY_RUNNING') NOT NULL,
  `invoicing_amount` enum('UPFRONT','PERIODICAL','PERIODICAL_EXCEPT_FIXED','PERIODICAL_YEARLY','UPFRONT_SPLIT') NOT NULL,
  `collateral_guarantors` enum('NO_SURETY','JOINTLY','SEVERALLY','SURETY') NOT NULL,
  `collateral_lien` tinyint(1) NOT NULL,
  `collateral_financial` tinyint(1) NOT NULL,
  `collateral_ownership` tinyint(1) NOT NULL,
  `collateral_letter` tinyint(1) NOT NULL,
  `collateral_cash` tinyint(1) NOT NULL,
  `collateral_subsidiaries` tinyint(1) NOT NULL,
  `collateral_note` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `frames_client_id_foreign` (`client_id`),
  CONSTRAINT `frames_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2767 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Generating Aurelius entity create class with variant type fields/property.

 [Entity]
  [Table('frames')]
  [Id('Fid', TIdGenerator.IdentityOrSequence)]
  Tframes = class
  private
    [Column('id', [TColumnProp.Required, TColumnProp.NoInsert, TColumnProp.NoUpdate])]
    Fid: Int64;
    
    [Column('limit', [TColumnProp.Required])]
    Flimit: Int64;
    
    [Column('evaluated_at', [TColumnProp.Required])]
    Fevaluated_at: TDateTime;
    
    [Column('expires_at', [TColumnProp.Required])]
    Fexpires_at: TDateTime;
    
    [Column('invoicing_frequency', [TColumnProp.Required, TColumnProp.NoInsert, TColumnProp.NoUpdate])]
    Finvoicing_frequency: Variant;
    
    [Column('invoicing_amount', [TColumnProp.Required, TColumnProp.NoInsert, TColumnProp.NoUpdate])]
    Finvoicing_amount: Variant;
    
    [Column('collateral_guarantors', [TColumnProp.Required, TColumnProp.NoInsert, TColumnProp.NoUpdate])]
    Fcollateral_guarantors: Variant;
    
    [Column('collateral_lien', [TColumnProp.Required])]
    Fcollateral_lien: Integer;
    
    [Column('collateral_financial', [TColumnProp.Required])]
    Fcollateral_financial: Integer;
    
    [Column('collateral_ownership', [TColumnProp.Required])]
    Fcollateral_ownership: Integer;
    
    [Column('collateral_letter', [TColumnProp.Required])]
    Fcollateral_letter: Integer;
    
    [Column('collateral_cash', [TColumnProp.Required])]
    Fcollateral_cash: Integer;
    
    [Column('collateral_subsidiaries', [TColumnProp.Required])]
    Fcollateral_subsidiaries: Integer;
    
    [Column('collateral_note', [TColumnProp.Lazy])]
    [DBTypeMemo]
    Fcollateral_note: TBlob;
    
    [Column('created_at', [])]
    Fcreated_at: Nullable<TDateTime>;
    
    [Column('updated_at', [])]
    Fupdated_at: Nullable<TDateTime>;
    
    [Association([TAssociationProp.Lazy, TAssociationProp.Required], CascadeTypeAll - [TCascadeType.Remove])]
    [JoinColumn('client_id', [TColumnProp.Required], 'id')]
    Fclient_id: Proxy<Tclients>;
    function Getclient_id: Tclients;
    procedure Setclient_id(const Value: Tclients);
  public
    property id: Int64 read Fid write Fid;
    property limit: Int64 read Flimit write Flimit;
    property evaluated_at: TDateTime read Fevaluated_at write Fevaluated_at;
    property expires_at: TDateTime read Fexpires_at write Fexpires_at;
    property invoicing_frequency: Variant read Finvoicing_frequency;
    property invoicing_amount: Variant read Finvoicing_amount;
    property collateral_guarantors: Variant read Fcollateral_guarantors;
    property collateral_lien: Integer read Fcollateral_lien write Fcollateral_lien;
    property collateral_financial: Integer read Fcollateral_financial write Fcollateral_financial;
    property collateral_ownership: Integer read Fcollateral_ownership write Fcollateral_ownership;
    property collateral_letter: Integer read Fcollateral_letter write Fcollateral_letter;
    property collateral_cash: Integer read Fcollateral_cash write Fcollateral_cash;
    property collateral_subsidiaries: Integer read Fcollateral_subsidiaries write Fcollateral_subsidiaries;
    property collateral_note: TBlob read Fcollateral_note write Fcollateral_note;
    property created_at: Nullable<TDateTime> read Fcreated_at write Fcreated_at;
    property updated_at: Nullable<TDateTime> read Fupdated_at write Fupdated_at;
    property client_id: Tclients read Getclient_id write Setclient_id;
  end;

I'm then getting an error message when using a TAureliusDataset and selecting the TFrame entity (load field definition)

Database has multiple tables using enums types (I'm not the owner of the database and cannot change it structure)

Can Data modeler be amended to handle these kind of fields differently ? Maybe using a string type and having a setter and getter that will only allows values from a record type (also generated by Data modeler) ? Any other solution is also welcomed.

Kind regards
Stéphane

[wlandgraf (Wagner Landgraf)]

I believe you can solve this using customization scripts, by inspecting the value of computed field, parsing it and then generating the property enum.

[WIERZBICKI_Stephane1 (WIERZBICKI Stéphane)]

Thanks Wagner for this quick answer.

I'm not really confident doing this with script. Would you perhaps mind updating TMS Aurelius documentation and provide such a sample (guess that existing customers will benefit from this as well).

Kind regards

[wlandgraf (Wagner Landgraf)]

Here is a small script that modifies the data type to string when computed fields have their expression starting with enum.

procedure OnColumnGenerated(Args: TColumnGeneratedArgs);
var
  Expr: string;
begin                                
  Expr := Args.DBField.Expression;
  if LowerCase(Copy(Expr, 1, 5)) = 'enum(' then
  begin
    Args.Field.FieldType.BaseType := 'string';
    Args.Prop.PropertyType.Basetype := 'string';    
  end;    
end;