TargetPerEntityHierarchy Challenges

Posts   
 
    
Mikhail
User
Posts: 2
Joined: 08-Jun-2017
# Posted on: 08-Jun-2017 23:29:52   

Hi i have a few challenges with TargetPerEntityHierarchy which i have searched around for but have not exactly found.

i have the following schema example:


USE [TestDB]
GO

IF OBJECT_ID('dbo.[AirPlane]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AirPlane]
GO

IF OBJECT_ID('dbo.[Car]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Car]
GO

IF OBJECT_ID('dbo.[Boat]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Boat]
GO

IF OBJECT_ID('dbo.[Vehicle]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Vehicle]
GO

CREATE TABLE [dbo].[Vehicle] (
    [VehicleID] [int] IDENTITY(1, 1) NOT NULL,
    [VehicleTypeID] [int] NOT NULL,
    [Weight] [decimal] NOT NULL,
    CONSTRAINT [PK_Vehicle] PRIMARY KEY CLUSTERED ([VehicleID] ASC) WITH (
        PAD_INDEX = OFF,
        STATISTICS_NORECOMPUTE = OFF,
        IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON
        )
    ON [PRIMARY]
    ) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Airplane] (
    [AirplaneID] [int] NOT NULL,
    [AirplaneTypeID] [int] NOT NULL,
    [MaximumAltitude] [int] NOT NULL,
    [MaximumSpeed] [decimal] NOT NULL,
    CONSTRAINT [PK_Airplane] PRIMARY KEY CLUSTERED ([AirplaneID] ASC) WITH (
        PAD_INDEX = OFF,
        STATISTICS_NORECOMPUTE = OFF,
        IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON
        )
    ON [PRIMARY]
    ) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Airplane]
    WITH CHECK ADD CONSTRAINT [FK_Airplane_Vehicle] FOREIGN KEY ([AirplaneID]) REFERENCES [dbo].[Vehicle]([VehicleID])
GO

ALTER TABLE [dbo].[Airplane] CHECK CONSTRAINT [FK_Airplane_Vehicle]
GO

CREATE TABLE [dbo].[Car] (
    [CarID] [int] NOT NULL,
    [CarTypeID] [int] NOT NULL,
    [AxelCount] [int] NOT NULL,
    [MaximumSpeed] [decimal] NOT NULL,
    CONSTRAINT [PK_Car] PRIMARY KEY CLUSTERED ([CarID] ASC) WITH (
        PAD_INDEX = OFF,
        STATISTICS_NORECOMPUTE = OFF,
        IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON
        )
    ON [PRIMARY]
    ) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Car]
    WITH CHECK ADD CONSTRAINT [FK_Car_Vehicle] FOREIGN KEY ([CarID]) REFERENCES [dbo].[Vehicle]([VehicleID])
GO

ALTER TABLE [dbo].[Car] CHECK CONSTRAINT [FK_Car_Vehicle]
GO

CREATE TABLE [dbo].[Boat] (
    [BoatID] [int] NOT NULL,
    [BoatTypeID] [int] NOT NULL,
    [MaximumNots] [decimal] NOT NULL,
    CONSTRAINT [PK_Boat] PRIMARY KEY CLUSTERED ([BoatID] ASC) WITH (
        PAD_INDEX = OFF,
        STATISTICS_NORECOMPUTE = OFF,
        IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON
        )
    ON [PRIMARY]
    ) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Boat]
    WITH CHECK ADD CONSTRAINT [FK_Boat_Vehicle] FOREIGN KEY ([BoatID]) REFERENCES [dbo].[Vehicle]([VehicleID])
GO

ALTER TABLE [dbo].[Boat] CHECK CONSTRAINT [FK_Boat_Vehicle]
GO

From Designer i have the following (sorry i did not find a way to imbed images in this forum, thus i will use links):

  • In this designer example i am using the latest llblgen trial 5.2. (we currently have licensing for the 4.1 version of llblgen, however, we are looking into getting 5.2 right now based on this experiment). I have reversed engineered the model from the schema above to began the TargetPerEntityHierarchy configuration.

  • https://ibb.co/fwwKTF

  • the next step is the set the subtype as Vehicle.

  • https://ibb.co/cWtcFv

  • the hierarchy is set

  • https://ibb.co/fEg8Na

  • next we need to choose the discriminator field and set the value for each one.

  • https://ibb.co/c2dzTF

  • this is what hierarchy looks like with discriminator value being set. all is well except below we have an error requiring the inheriting entity to be mapped to the target Vehicle.

  • https://ibb.co/j7nF2a

  • we then select the Vehicle as target entity.

  • https://ibb.co/nzrKTF

  • however we have a another problem, the fields that are specific to the inheriting entities are no longer mapped to their own table.

  • https://ibb.co/g6Ev2a

my dilemma is that i want to preserve the database schema the same while letting llblgen manage the abstraction of Vehicle with discriminating type. Instead its forcing me to move all of the fields from the Inheriting types to the Vehicle table. if you noticed i have set each primary key of the inheriting tables to the vehicle primary key. i want to be able to replicate the PK from vehicle to each of the inheriting table while each of the inheriting tables will no require identity seed. i also want to keep each of the inheriting types fields to their own tables.

Am i missing something obvious? i performed at least minimal level of due diligence searching the forums and reading the documentation, i have not exactly found any top to bottom examples of this configuration. i even attempted a model first approach and that did not generate proper results as expected. i have used the following approach so far 1. database first, create schema, 2. reverse engineer the schema to llblgen entities 3. configure the entities to TargetPerEntityHierarchy and preserve the schema.

Thank You in Advance, -Mike

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 09-Jun-2017 08:29:29   

Hi Mike,

As you have a table per type, you have your hierarchy implicit among your tables. So you dont need to use TargetPerEntityHierarchy (which is when you have all types in one physical table differentiated by a discriminator field). Your types are already differentiated by different tables.

In short, you need to use TargetPerEntit, by selecting the Construct Target-per-Entity Hierarchies option in the context menu, when you right-click the Entity Model-, Entities- or any group node in the Project Explore.

More information: http://www.llblgen.com/documentation/5.2/Designer/Concepts/Inheritance.htm

David Elizondo | LLBLGen Support Team
Mikhail
User
Posts: 2
Joined: 08-Jun-2017
# Posted on: 09-Jun-2017 23:08:27   

Thank You for your feedback, i will take that into account and see what results i get back.