Bill Sempf

For a long time, I have used the ‘totally unique’ power of the Guid to design the ‘exactly one of several collections’ problem in my databases. The Zen case is that of the container in an inventory management system.  The container can be on exactly one of the collection of stores, or exactly one of the collection of trucks, or exactly one of the collection of warehouse locations.  I can’t, in any case, be in two locations, or no location.

How I usually implement this is to have a single LocaationId in the Containers table that is of type GUID, and is a foreign key for the TruckId, StoreId or WarehouseLocationId.  Since Guids are globally unique I don’t have the worry about a duplication between tables.  Effectively, the Stores, Trucks and WarehouseLocations tables have an implicit uniqueness constraint, which I can enforce in code if I am getting really squirrely.

I was pleased to see that Entity Framework 4 handles this well.   A “Model from Database” command puts up an entity model that looks strikingly like our domain model diagram.

All I have to do here is alter the navigation properties to show a single location, but that pass back either a store, truck or warehouselocataion, based on maybe a simple Location interface.  Good enough.

Where I was curious is in learning how M will handle this design.  As it turns out, not as well. I ran the “Model from Database” and got this:

module dbo
{
    export WarehouseLocations, Stores, Containers, Trucks;
    WarehouseLocations : {({
        WarehouseLocationID : Guid;
        Aisle : Integer32;
        Shelf : Integer32;
    } where identity WarehouseLocationID)*};
    Stores : {({
        StoreId : Guid;
        StoreNumber : Text where value.Count() <= 16;
    } where identity StoreId)*};
    Containers : {({
        LocationId : {
            StoreId : Guid;
            StoreNumber : Text where value.Count() <= 16;
        } where value in Stores;
        ContainerId : Guid;
        Description : Text where value.Count() <= 128;
    } where identity ContainerId)*};
    Trucks : {({
        TruckId : Guid;
        VehicleCode : Text where value.Count() <= 16;
    } where identity TruckId)*};
}

Yeah, that wasn’t what I was looking for.  Somehow, the members of Store ended up as values of LocationID as an entity …. meh.  It’s CTP software, but it is worthwhile analysis.  Perhaps I’ll dig in and see if I can figure out how M came up with this after the weekend.

Thanks to Mike Wood and others for asking me to give my SQL Modeling talk at CODODN.  Events like CODODN are important, because the bridge the gab between locla events and the larger regionals like CodeMash.  Smaller groups sometimes mean better hallway conversations and the like.  Kudos to all those who participated in getting this together.

Anyway, here is the solution (WarehouseManagementSystem.zip (2.65 mb)) from my talk.  No slides for this talk, just a little talking and a lot of coding.  Get the bits from the SQL Modeling Website, and make sure you have SQL Express 2008 installed.

 Thanks to all who attended; good questions and insight.

Thursday night I had the honor of giving my new talk “Software Modeling with ASCII, and no I’m not kidding” to the Columbus Computer society of the IEEE.  They were very welcoming and enjoyed the talk, and had a number of comments about the technology and its use.

I start the talk with what essentially be the first chapter of Professional Software Modeling.  WE cover the problems with current modeling systems, and the timeline for modeling and object/relational mapping

The bulk of the talk is effectively a demo of deploying a database and entity classes form a simple model and then generating an ASP.NET MVC web site from the model.  It is similar to the hands-on-lab at the PDC, with the Mini Nerd Dinner. 

Right off the bat, a listener pointed out that we HAVE these tools already.  Don’t we have XML?  What’s wrong with that?  Tools like WSDL and EDMX solve these problems, and are human readable! Why do we need something else?

I agreed in principle but I pointed out that not many people think that XML is not human readable any more.  The sample code was, but the 150,000 line long files that end up getting used are NOT.  Especially when there are only about 1,500 important lines in the file.

The XML is still there, I assured everyone, and the model was still in EDMX.  M is just a way to work with the model that is a little more succinct than the XML.  Additionally, I pointed out, there are more semantic pieces to M that we hadn’t gone over.  We had done the nouns, but there are verbs too, if you get my drift.

As we went over how an M model looks in the M file, and how the final database and domain classes look, someone asked the obvious question.  It’s the same question that I asked last fall.

“Great!  What do I do with an EXISTING application?”

I don’t have a good answer for that.  Would I like to be able to take an existing application’s database and look at the representative M file?  Yes.  Can I?  I am not sure I can.  That is an open question.

Honestly, I haven’t taken the time to look into the story for existing applications.  Fact is, most application development is adding features to existing applications and I don’t know how M fits into that.  If it is going to be a good modeling tool for existing application there needs to be a reverse-engineering story, and I hope there is.  I mean, you can always make a model from an existing database, but I am not sure that is enough.

The last discussion we had was about potential.  Specifically – what is Microsoft doing to support hte wide adoption of this product?  How about multiple datasources?  What if I need to model an identity system, where there is an Active Directory, an LDS and a database with identity information?  Can I model that in M?

No, I answered.  Right now M is shackled by EDMX, which really only has a provider for SQL Server and Oracle.  It is theoretically possible to enhance the M modeling superstructure to handle a multi-source database, but it isn’t done yet.

In general, everyone loved the talk, and I am looking forward to cleaning it up a bit and giving it a few more times.  Thanks to Jack Freund and the IEEE Computer Society for allowing me to speak!  Hope to see you all again sometime!

In M you can describe values and use MGraph to infer a data model.  for instance, I can tell it a little data, like this:

module TheStates
{
  States  => { "Ohio", "Indiana", "Kentucky" };
}

… and get back the SQL code for my inferred table:

set xact_abort on;
go

begin transaction;
go

set ansi_nulls on;
go

if not exists 
(
    select *
    from [sys].[schemas]
    where [name] = N'TheStates'
)
    execute [sp_executesql] N'create schema [TheStates]';
go

create table [TheStates].[States]
(
    [Item] nvarchar(max) not null
);
go

insert into [TheStates].[States] ([Item])
    values (N'Ohio'),
        (N'Indiana'),
        (N'Kentucky');
go

commit transaction;
go

The problem is with a real world model, say a collection of states that represents a route.  if you try to model that with an extent, like this:

module Proto
{
  Representative =>
  {
    Id => "4";
    Name => "Bill";
    States  => { "Ohio", "Indiana", "Kentucky" };
  } where identity Id;
}

Well, at least it is a Not Yet Implemented error.

untitled3  4,3-8,22    Error   M3999   Not yet implemented: Initializing a collection with an expression that yields a collection (Microsoft.M.SemanticGraph.FromExpressionSymbol)

Still, without something that straightforward implemented, it is tough to call this a modeling language for data.  Hopefully it is in the works soon.

Well, if you went to PDC and did the thing I always do, which is go to every talk that lists Don Box as a speaker, then you probably heard about SQ!L Modeling.  I don’t care about the SQL part – SQL is a database as far as I am concerned – it is an implementation detail.  As a software architect, though, I am very interested in Modeling.

So I get the bits and install them.  Great.  I have Quadrant as a Start Menu item, but Quadrant is Access.  Not interested (for this anyway).  I want to tell Visual Studio how I need my software built.  I need M.

The New Project Dialog has a Oslo Library that might be interesting.  I thought they had nixed Oslo though?  What’s up with that?  Anyway, the description says “A project for creating Oslo Flavored CSharp Library” whatever that means.  I just want to design software, people.  I don’t make ice cream.

I’ll go ahead and model SHARP, my event system, because I know that system well and have been modeling it for years.  To start, I name the Oslo Library SharpLibrary.  The resultant code is unusual, but it is a new language after all.

module SharpLibrary
{
    type Model
    {
        Id : Integer32 => AutoNumber();
        
        Field : Integer32;
        
    }
    
    Modelsamples : {Model*} where identity Id;
    
}

Don’t let the ‘module’ statement fool you – this isn’t a RAD application development language that will remain nameless.  M is a language that will model your middle tier.  Let’s model.  The goal is to build the simple SHARP model, which I used in VB for Dummies and the C# All In One.

I will start with the Conference entity, because it is sorta the middle of the system.  ID is as the example, but I want to add a Title and Description.  First thing is that M needs intellisense.  What’s a string again? String?  Varchar?  Oh, no of course it is Text.  Of course.

More later!