Ok, time for some serious eye candy

After working on a new EntityShape class which renders an entity in a hierarchy, the following results are now possible. It's also possible to rightclick an entity and select a menu option

Needless to say, this opens up a lot of new doors. For now, I'll use it for visualizing a hierarchy and to work on an existing hierarchy (like destroying a hierarchy from a given node) or jump to an entity in a hierarchy directly.

One table, multiple classes (hierarchy per table): For full size, click: http://www.xs4all.nl/~perseus/inheritance1.jpg

One table per concrete entity: For full size, click: http://www.xs4all.nl/~perseus/inheritance2.jpg

All possible due to the excellent foundation of the Netron library: http://netron.sourceforge.net/

All i can say is WOW, thats beautiful

I'm blown away. Simply blown away. Fantastic stuff.

A few points. Love the visual indicators in the tree on the left. The new icon with the green arrow is great, as is the indication of the parent table and field, and use of 'Subtype of ...'. The 'Discriminator value: ...' is great, as is the dimming of the fields in the diagram to show they are part of the parent table. Wonderful, very clear and intuitive.

I'd love it if you'd post the CREATE scripts for the tables in the picture examples you provide, so I can confirm what I'm seeing.

Regarding discriminators. Your picture implies an INT (or NUMERIC) value. What types are you planning to support, and are you then generating ENUMs for those values? I'm an GUID kind of guy myself.

I don't recall, did you say it will try to infer some of these inheritence relationships on a refresh catalog? If a relationship is not found, or needs to be changed, could you show the interface for establishing the relationship, if that interface is created yet?

I can see another feature coming, a properties box on the right so that, when a person clicks onto a field, table, or relationship, more information is displayed. Cool...

One last question, when can I have this?

Thanks guys

swallace wrote:

I'm blown away. Simply blown away. Fantastic stuff. A few points. Love the visual indicators in the tree on the left. The new icon with the green arrow is great, as is the indication of the parent table and field, and use of 'Subtype of ...'. The 'Discriminator value: ...' is great, as is the dimming of the fields in the diagram to show they are part of the parent table. Wonderful, very clear and intuitive.

Thanks Yesterday I toyed a bit with the green arrow, as it looks a bit like a smallish christmas tree, but as the icons are 16x16 pixels, it's hard to get a better looking one, so I left it as is.

I'd love it if you'd post the CREATE scripts for the tables in the picture examples you provide, so I can confirm what I'm seeing.

CREATE TABLE [dbo].[BoardMember] (
    [BoardMemberID] [int] NOT NULL ,
    [CompanyCarID] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Clerk] (
    [ClerkID] [int] NOT NULL ,
    [JobDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CompanyCar] (
    [CarID] [int] NOT NULL ,
    [CarType] [int] NOT NULL ,
    [Brand] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Price] [money] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Employee] (
    [EmployeeID] [int] IDENTITY (1, 1) NOT NULL ,
    [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [StartDate] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Manager] (
    [ManagerID] [int] NOT NULL ,
    [ManagesDepartmentID] [int] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[BoardMember] ADD 
    CONSTRAINT [PK_BoardMember] PRIMARY KEY  CLUSTERED 
    (
        [BoardMemberID]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Clerk] ADD 
    CONSTRAINT [PK_Clerk] PRIMARY KEY  CLUSTERED 
    (
        [ClerkID]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[CompanyCar] ADD 
    CONSTRAINT [PK_CompanyCar] PRIMARY KEY  CLUSTERED 
    (
        [CarID]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Employee] ADD 
    CONSTRAINT [PK_Employee] PRIMARY KEY  CLUSTERED 
    (
        [EmployeeID]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Manager] ADD 
    CONSTRAINT [PK_Manager] PRIMARY KEY  CLUSTERED 
    (
        [ManagerID]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[BoardMember] ADD 
    CONSTRAINT [FK_BoardMember_CompanyCar] FOREIGN KEY 
    (
        [CompanyCarID]
    ) REFERENCES [dbo].[CompanyCar] (
        [CarID]
    ),
    CONSTRAINT [FK_BoardMember_Manager] FOREIGN KEY 
    (
        [BoardMemberID]
    ) REFERENCES [dbo].[Manager] (
        [ManagerID]
    )
GO

ALTER TABLE [dbo].[Clerk] ADD 
    CONSTRAINT [FK_Clerk_Employee] FOREIGN KEY 
    (
        [ClerkID]
    ) REFERENCES [dbo].[Employee] (
        [EmployeeID]
    )
GO

ALTER TABLE [dbo].[Manager] ADD 
    CONSTRAINT [FK_Manager_Employee] FOREIGN KEY 
    (
        [ManagerID]
    ) REFERENCES [dbo].[Employee] (
        [EmployeeID]
    )
GO

As you can see, only boardmember has a relation to company car. This is one thing which is currently not possible, if you cram a hierarchy into a single table. What's also possible (not shown in the picture) is that you can 'hide' the relation boardmember - companycar and create a new relation 'boardmember - familycar' which thus assures there isn't a relation between boardmember and sportscar.

Also (not yet implemented) it will be possible to hide fields in an entity, which of course is mandatory for target-per-entity-hierarchy inheritance. Also (not yet implemented) it will be possible to mark types as 'abstract', which can for example be good for 'employee' and 'companycar'.

Regarding discriminators. Your picture implies an INT (or NUMERIC) value. What types are you planning to support, and are you then generating ENUMs for those values? I'm an GUID kind of guy myself.

I currently support int64/32/16/byte and strings. The main issue is that discriminator values are often defined in the abstract model and GUID's aren't a likely candidate for discriminator values, due to their horrid textual representation.

I don't recall, did you say it will try to infer some of these inheritence relationships on a refresh catalog? If a relationship is not found, or needs to be changed, could you show the interface for establishing the relationship, if that interface is created yet?

If the refresher sees a relation isn't present anymore, it will destroy the hierarchy from the node which was related to its parent via that missing relation. The entities will still be present, the relation just isn't. Same for the absense of the discriminator field. You can then create a new custom relation and tell llblgen pro to find all hierarchies in the entity collection.

I can see another feature coming, a properties box on the right so that, when a person clicks onto a field, table, or relationship, more information is displayed. Cool...

I personally really hate 'property boxes'. They're very tiny often, 'TAB' doesn't work and the give the user the feeling something can be altered there, which isn't the case.

One last question, when can I have this?

I hope to have the beta started somewhere in July

Ok, after pulling apart almost everything, I can now save an entity into the database which is a leaf in a 3-level hierarchy, and also pull all entities using a base entity type, and the collection then fills itself with the proper types (boardmemberentity, managerentity, clerkentity) .

Still a lot of work to do, but it gets shape .

Otis wrote:

Ok, after pulling apart almost everything, I can now save an entity into the database which is a leaf in a 3-level hierarchy, and also pull all entities using a base entity type, and the collection then fills itself with the proper types (boardmemberentity, managerentity, clerkentity) .

Still a lot of work to do, but it gets shape .

Thanks for the update Otis. Its nice to know what's going on and I can't wait to get this

Otis.. I hope you provide a real-life example of using inheritance. I can only imagine how much this will affect the way we design the database BUT I still can't get my mind around visualizing my database tables as related types

omar wrote:

Otis.. I hope you provide a real-life example of using inheritance. I can only imagine how much this will affect the way we design the database BUT I still can't get my mind around visualizing my database tables as related types

I understand what you mean, it's often not that easy to come up with PROPER examples for inheritance in relational models. THe dreaded 'person - customer / person - employee' etc. examples aren't good, as they have problems when the customer is an employee.

One area in which supertype/subtype hierarchies of entities can help is when you want to make a relation only for subgroup of a particular group of entity instances. For example, in my example above, I have employee - manager - boardmember. Boardmember has a relation with companycar, not manager. This way I am able to make sure that the relation between boardmember is only there for boardmembers. Would I have defined it on Employee, I would have to add checks etc, which can fail.

For more info about supertype/subtypes, see for example the object role modeling details at: http://www.orm.net: http://www.orm.net/pdf/springer.pdf (page 8-10)

It's my plan to supply the example database I'm using for testing as it contains both target-per-concrete-entity hierarchies and target-per-entity-hierarchy hierarchies. (other O/R mappers support 4 different ways of inheritance mapping, LLBLGen Pro supports 3, where 2 are actually the same: (2 and 3 in the list below), and as 3) is already supported, it covers 2) (as in: not necessary)

1) target per entity hierarchy: one table with all types, and a discriminator column which signales what type the row represents 2) target per concrete entity: per entity in the hierarchy a table is defined with the fields defined for that entity (all inherited fields are not defined in this table). 3) as 2) but now in the root entity table a discriminator column is present 4) as 2) but now all inherited fields are also present per entity. So the 'Boardmember' table contains all fields of Manager and Employee.

4) isn't supported, as it's bad practise: it contains redundant data and is error prone. 3) is the same as 2) when 2) is supported by the logic, e.g.: the descriminator column isn't necessary.

I am a big fan of Dr. Terry since I saw his ORM episode on (the .NET Show). At one stage I imagined I would conquer the world with ORM (Role Modeling) and with Visio 2003 EA I managed to design several highly complex database. The "Wake up and smell the coffee" call for me was when I wanted to build systems to use these databases (didn't have LLBL at the time). It would be great if I could: 1- design my database using Visio's ORM and then generate the database, build my LLBL designer project, and generate my DAL. 2- generate a class diagram from my ORM diagram to be my “Object-Model” when building my BL and UI and since I am still dreaming, the generated class diagram could interface with the new VS.NET's class diagram so that all database modifications are done in the ORM diagram.

now.. wake up and smell the coffee... back to the Enterprise Manager

omar wrote:

now.. wake up and smell the coffee... back to the Enterprise Manager

No! Back to your text editor. Script.

omar wrote:

I am a big fan of Dr. Terry since I saw his ORM episode on (the .NET Show). At one stage I imagined I would conquer the world with ORM (Role Modeling) and with Visio 2003 EA I managed to design several highly complex database. The "Wake up and smell the coffee" call for me was when I wanted to build systems to use these databases (didn't have LLBL at the time). It would be great if I could: 1- design my database using Visio's ORM and then generate the database, build my LLBL designer project, and generate my DAL. 2- generate a class diagram from my ORM diagram to be my “Object-Model” when building my BL and UI and since I am still dreaming, the generated class diagram could interface with the new VS.NET's class diagram so that all database modifications are done in the ORM diagram.

In fact, that's what LLBLGen Pro does internally: reverse engineer the db to re-create an entity structure, as defined in an ORM model, though not as abstract as ORM would. With the addition of supertype/subtypes, it gets complete

now.. wake up and smell the coffee... back to the Enterprise Manager

QA you mean.

Ok, Inheritance implementation in the runtimes and templates reaches its final stage, where just some porting is to be done (dqe refactorings to other db's) and a typefilter predicate and it 'should work' . Selfservicing and adapter now both load/save/delete entities in hierarchies without problems and today I managed to get the alias-mapping code to work, which was the last complex element in the runtime to get implemented for inheritance.

Imagine this dynamic list test with aliases:

[Test]
public void SingleEntityMultipleTargetDynamicListTestWithRelationWithAlias()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        ResultsetFields fields = new ResultsetFields(2);
        fields.DefineField(BoardMemberFieldIndex.Name, 0, "Name", "BM");
        fields.DefineField(BoardMemberFieldIndex.CompanyCarId, 1, "CompanyCarID", "BM");
        RelationPredicateBucket filter = new RelationPredicateBucket();
        filter.PredicateExpression.Add(PredicateFactory.CompareValue(DepartmentFieldIndex.DepartmentId, ComparisonOperator.Equal, 3, "D"));
        filter.Relations.Add(BoardMemberEntity.Relations.DepartmentEntityUsingManagesDepartmentId, "BM", "D", JoinHint.None);
        DataTable results = new DataTable();
        adapter.FetchTypedList(fields, results, filter);
        Assert.IsTrue( (results.Rows.Count>0));
        foreach(DataRow row in results.Rows)
        {
            Console.WriteLine("Name: {0}. CompanyCarID: {1}", row[0].ToString(), row[1].ToString());
        }
    }
}

This shows the problem because boardmember is aliased as 'BM', but it contains 3 tables, so an alias mapping has to be done. But in such a way, that aliases aren't leaking from deeper scopes (subqueries) upwards. That's now running, so the query produced is:

SELECT  [LPA_B3].[Name] AS [Name],
    [LPA_B2].[CompanyCarID] AS [CompanyCarID] 
FROM 
(
    (
        ( 
            [InheritanceOne].[dbo].[Employee] [LPA_B3]  INNER JOIN [InheritanceOne].[dbo].[Manager] [LPA_B4]  
            ON  [LPA_B3].[EmployeeID]=[LPA_B4].[ManagerID]
        ) INNER JOIN [InheritanceOne].[dbo].[BoardMember] [LPA_B2]  
        ON  [LPA_B4].[ManagerID]=[LPA_B2].[BoardMemberID]
    ) INNER JOIN [InheritanceOne].[dbo].[Department] [LPA_D1]  
    ON  [LPA_D1].[DepartmentID]=[LPA_B4].[ManagesDepartmentID]
) 
WHERE 
( 
    ( 
        [LPA_D1].[DepartmentID] = @DepartmentId1
    )
)

(LPA stands for llblgen pro alias)

Although it's not really necessary, I'm going to add a typefilter predicate as well. You can already filter without it: like:

[Test]
public void FetchAllEmployeesWithTypeFilter()
{
    EntityCollection employees = new EntityCollection(new EmployeeEntityFactory());
    RelationPredicateBucket filter = new RelationPredicateBucket();
    filter.PredicateExpression.Add(PredicateFactory.CompareNull(BoardMemberFieldIndex.Id, true));
    filter.PredicateExpression.AddWithOr(PredicateFactory.CompareNull(ClerkFieldIndex.Id, true));

    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        adapter.FetchEntityCollection(employees, filter);
    }

    Assert.IsTrue((employees.Count > 0));
    foreach(IEntity2 entity in employees)
    {
        Console.WriteLine("ID: {0}. Type: {1}", entity.GetCurrentFieldValue(0), entity.GetType().ToString());
    }
}

but it requires knowledge of the type structure. With targetperentityhierarchy entities (where a discriminator column is used, this is even more weird, as the discriminator column value per entity is not used by the developer (automatic, you don't have to set it), so a type filter predicate is required.