locked
Issue with SQL Generation RRS feed

  • Question

  • I am posting this here to expand the discussion about some of my comments on my first impressions of Oslo that were posted on my blog (http://wildermuth.com/2008/10/29/First_Impressions_of_Oslo). Chris Sells thought it would be helpful if these comments continued here so you could more effectively track them.

    I don't suggest this is the *right* way to do it in every case, but its a very common scenario.  For example, consider in this very simply inheritance model:

    module VideoGameStore
    {
        type Product
        {
            ProductId : Integer32 = AutoNumber();
            ProductName : Text where value.Count() < 250;
            Description : Text#250;
            ReleaseDate : Date?;
        } where identity ProductId, unique ProductName;
       
        Products : Product*;
       
        type Game : Product
        {
          Genre : Text#100;
          Developer : Text#150;
          Publisher : Text#150;
        };
       
        Games : Game*;
    }

    The Rich SQL is (tables only):

    create table [VideoGameStore].[Products]
    (
      [ProductId] int not null identity,
      [Description] nvarchar(250) not null,
      [ProductName] nvarchar(249) not null,
      [ReleaseDate] date null,
      constraint [PK_Products] primary key clustered ([ProductId]),
      constraint [Unique_Products_ProductName] unique ([ProductName])
    );
    go

    create table [VideoGameStore].[Games]
    (
      [ProductId] int not null identity,
      [Description] nvarchar(250) not null,
      [Developer] nvarchar(150) not null,
      [Genre] nvarchar(100) not null,
      [ProductName] nvarchar(249) not null,
      [Publisher] nvarchar(150) not null,
      [ReleaseDate] date null,
      constraint [PK_Games] primary key clustered ([ProductId]),
      constraint [Unique_Games_ProductName] unique ([ProductName])
    );
    go

    But in many scenarios what you really want is to use decorator tables:

    create table [VideoGameStore].[Games]
    (
      [ProductId] int not null identity,
      [Developer] nvarchar(150) not null,
      [Genre] nvarchar(100) not null,
      [Publisher] nvarchar(150) not null,
      constraint [PK_Games] primary key clustered ([ProductId]),
      constraint [FK_Games_Products] foreign key ([ProductID]) references [VideoGameStore].[Products] ([ProductID])
    );
    go

    But since I couldn't figure out how to tell MSchema to do this, I've taken the assumption it isn't possible...but I've been wrong before (hey, I though WinFS would succeed...so what do I know).

    Ideas?

      
    http://wildermuth.com | http://www.silverlight-tour.com
    Sunday, November 2, 2008 11:30 PM

Answers

  • Shawn - unfortunately the thing that lead you astray is the usage of the term "inheritance". While the M type declaration syntax looks a lot like the C# syntax it is really a very different thing.

    When you say:
        
        type T1 { 
            A;
            B;
        }

        type T2 : T1 {
            C;
            D;
        }

    What you're really saying is that type T2 is the intersection of the set of things defined by the type T1 and the set of things defined by "{ C; D; }". In essence it is simply a convinent way of writing down:

        type T2 {
            A;
            B;
            C;
            D;
        }

    In a structural type system there is no difference between those two definitions of T2.

    How about this modeling of your video game store:

    module VideoGameStore
    {
        type Product
        {
            ProductId : Integer32 = AutoNumber();
            ProductName : Text where value.Count() < 250;
            Description : Text#250;
            ReleaseDate : Date?;
        } where identity ProductId, unique ProductName;
       
        Products : Product*;
       
        type Game
        {
          Product : Product;
          Genre : Text#100;
          Developer : Text#150;
          Publisher : Text#150;
        } where identity Product, Product in Products;
       
        Games : Game*;
    }
    • Marked as answer by Mike Weinhardt Wednesday, November 5, 2008 4:55 PM
    Monday, November 3, 2008 3:38 AM

All replies

  • Shawn - won't this work?
    module VideoGameStore
    {
        type Product
        {
            ProductId : Integer32 = AutoNumber();
            ProductName : ProductName;
            Description : Text#250;
            ReleaseDate : Date?;
        } where identity ProductId, unique ProductName;
        
        ProductName : (
        {
            ProductNameId: Integer32 = AutoNumber();
            ProductNameText : Text where value.Count()<250;
    } where identity ProductNameId)*;
        
        Products : Product*;
        
        type Game : Product
        {
          Genre : Text#100;
          Developer : Text#150;
          Publisher : Text#150;
        };
        
        Games : Game*;
    }

    Or is the issue is that you'd like M to project that into another extent automatically?

    http://www.masteringbiztalk.com/blogs/jon/default.aspx
    Monday, November 3, 2008 1:07 AM
  • Shawn - unfortunately the thing that lead you astray is the usage of the term "inheritance". While the M type declaration syntax looks a lot like the C# syntax it is really a very different thing.

    When you say:
        
        type T1 { 
            A;
            B;
        }

        type T2 : T1 {
            C;
            D;
        }

    What you're really saying is that type T2 is the intersection of the set of things defined by the type T1 and the set of things defined by "{ C; D; }". In essence it is simply a convinent way of writing down:

        type T2 {
            A;
            B;
            C;
            D;
        }

    In a structural type system there is no difference between those two definitions of T2.

    How about this modeling of your video game store:

    module VideoGameStore
    {
        type Product
        {
            ProductId : Integer32 = AutoNumber();
            ProductName : Text where value.Count() < 250;
            Description : Text#250;
            ReleaseDate : Date?;
        } where identity ProductId, unique ProductName;
       
        Products : Product*;
       
        type Game
        {
          Product : Product;
          Genre : Text#100;
          Developer : Text#150;
          Publisher : Text#150;
        } where identity Product, Product in Products;
       
        Games : Game*;
    }
    • Marked as answer by Mike Weinhardt Wednesday, November 5, 2008 4:55 PM
    Monday, November 3, 2008 3:38 AM
  • If M types want to pick up some momentum and if it is to be used to model real world samples, mapping inheritance to "flat" DB is a must.

    I am very disappointed that there seem to be no thought about that in M. There are several ways of mapping an inheritance tree (singe- or vene multiple-inheritance) to a flat RDBMS structure and for M to be successful it should support at least some of them.

    It may be that being object orientated is currently not en vogue (functional style!), but especially in mapping real world objects to program / data it is priceless. Think about the following statements (not to repeat the stupid animal example...) :

    A task is a description of work to be performed by a role/function or person.
    A job is an ordered list of tasks with a name.
    A programming job is a job which has a list of functional requirements.
    A paint job is a job which refers to the object to be painted.

    List all jobs.

    Should work in M and esp. in Quadrant, because that is the way people think. The toolset to find incosnequencies and errors in such a description are what makes Oslo so usefull, so please (pretty please with sugar on top) make this possible.

    P.S.: Could it be that the Ribbon in Quadrant is model driven but does NOT have inheritance? Will MS flatten the UI Controls hierarchy???
    Monday, December 1, 2008 10:43 AM