How do I implement a View in the Datamodel

BIZ TMS Aurelius
1

I read that it is possible to read a View with Aurelius, but I can't find an example of it.

With the Datamodeller I created two tables:

  THouseData = class;
  THouses = class;
  THouseDataTableDictionary = class;
  THousesTableDictionary = class;

  [Entity]
  [Table('HouseData')]
  [UniqueKey('id')]
  [Id('Fid', TIdGenerator.None)]
  THouseData = class
  private
    [Column('id', [TColumnProp.Required])]
    Fid: integer;

    [Column('House_data', [TColumnProp.Lazy])]
    FHouse_data: TBlob;
  public
    property id: integer read Fid write Fid;
    property House_data: TBlob read FHouse_data write FHouse_data;
  end;

  [Entity]
  [Table('Houses')]
  [UniqueKey('House_level, House_column, House_row')]
  [Id('FHouse_id', TIdGenerator.None)]
  THouses = class
  private
    [Column('House_level', [])]
    FHouse_level: Nullable<integer>;

    [Column('House_column', [])]
    FHouse_column: Nullable<integer>;
   
    [Column('House_row', [])]
    FHouse_row: Nullable<integer>;
   
    [Column('House_id', [TColumnProp.Required])]
    FHouse_id: integer;
  public
    property House_level: Nullable<integer> read FHouse_level write FHouse_level;
    property House_column: Nullable<integer> read FHouse_column write FHouse_column;
    property House_row: Nullable<integer> read FHouse_row write FHouse_row;
    property House_id: integer read FHouse_id write FHouse_id;
  end;

  [Entity]
  [Table('Houses')]
  [UniqueKey('House_level, House_column, House_row')]
  [Id('FHouse_id', TIdGenerator.None)]
  THouses = class
  private
    [Column('House_level', [])]
    FHouse_level: Nullable<integer>;

    [Column('House_column', [])]
    FHouse_column: Nullable<integer>;

    [Column('House_row', [])]
    FHouse_row: Nullable<integer>;

    [Column('House_data', [TColumnProp.Lazy])]
    FHouse_data: TBlob;
  public
    property House_level: Nullable<integer> read FHouse_level write FHouse_level;
    property House_column: Nullable<integer> read FHouse_column write FHouse_column;
    property House_row: Nullable<integer> read FHouse_row write FHouse_row;
    property House_id: integer read FHouse_id write FHouse_id;
  end;

  TDicDictionary = class
  private
    FHouseData: THouseDataTableDictionary;
    FHouses: THousesTableDictionary;
    function GetHouseData: THouseDataTableDictionary;
    function GetHouses: THousesTableDictionary;
  public
    destructor Destroy; override;
    property HouseData: THouseDataTableDictionary read GetHouseData;
    property Houses: THousesTableDictionary read GetHouses;
  end;
 
  THouseDataTableDictionary = class
  private
    Fid: TDictionaryAttribute;
    FHouse_data: TDictionaryAttribute;
  public
    constructor Create;
    property id: TDictionaryAttribute read Fid;
    property House_data: TDictionaryAttribute read FHouse_data;
  end;
 
  THousesTableDictionary = class
  private
    FHouse_level: TDictionaryAttribute;
    FHouse_column: TDictionaryAttribute;
    FHouse_row: TDictionaryAttribute;
    FHouse_id: TDictionaryAttribute;
  public
    constructor Create;
    property House_level: TDictionaryAttribute read FHouse_level;
    property House_column: TDictionaryAttribute read FHouse_column;
    property House_row: TDictionaryAttribute read FHouse_row;
    property House_id: TDictionaryAttribute read FHouse_id;
  end;
 
function Dic: TDicDictionary;

implementation

var
  __Dic: TDicDictionary;

function Dic: TDicDictionary;
begin
  if __Dic = nil then __Dic := TDicDictionary.Create;
  result := __Dic
end;

{ TDicDictionary}

destructor TDicDictionary.Destroy;
begin
  inherited;
  if FHouseData <> nil then FHouseData.Free;
  if FHouses <> nil then FHouses.Free;
end;

function TDicDictionary.GetHouseData: THouseDataTableDictionary;
begin
  if FHouseData = nil then FHouseData := THouseDataTableDictionary.Create;
  result := FHouseData;
end;

function TDicDictionary.GetHouses: THousesTableDictionary;
begin
  if FHouses = nil then FHouses := THousesTableDictionary.Create;
  result := FHouses;
end;

{ THouseDataTableDictionary}

constructor THouseDataTableDictionary.Create;
begin
  Fid := TDictionaryAttribute.Create('id');
  FHouse_data := TDictionaryAttribute.Create('House_data');
end;

{ THousesTableDictionary}

constructor THousesTableDictionary.Create;
begin
  FHouse_level := TDictionaryAttribute.Create('House_level');
  FHouse_column := TDictionaryAttribute.Create('House_column');
  FHouse_row := TDictionaryAttribute.Create('House_row');
  FHouse_id := TDictionaryAttribute.Create('House_id');
end;

I also have this simple view, how do I implement that (I know it will be read-only):

CREATE VIEW HouseView AS SELECT Houses.House_level AS House_level,Houses.House_column AS House_column,Houses.House_row AS House_row,HouseData.House_data AS House_data FROM Houses JOIN HouseData ON HouseData.id = Houses.House_id

2

Create a class THouseView mapped to "table" (view) HouseView:

[Table('HouseView')]
and create the fields/properties mapped to the respective columns in the view.
3

Hi, Wagner

Interesting feature, but how aurelius create a view (with DatabaseManager.UpdateDatabase or DatabaseManager.BuildDatabase). The Attribute "Table" is the same for the Tables and Views.
Thanks
4

Aurelius doesn't create the view, you have to create it yourself.

5

Hi, Wagner
  When the app have multi database engine (firebird, sql server, etc..), the command "create view" differs, how to deal with that?
Thanks

6

You have to deal with that, Aurelius doesn't create views. And actually even if it did, you would have to provide the SQL anyway, and that would be different for each database.