Data Model - Basics - Part 11 - The HuagatiRes data model converted to “M”
Happy over Microsoft’s new commitment to data modelling, I have been playing around a bit with the “Oslo” CTP. It is time they come up with a replacement for Visio 2003 EA. The latest thing I tried was a conversion of the HuagatiRes datamodel into an M script. There’s not much to say about it - “M” and “Oslo” are still CTPs so some things can be defined in M models (e.g. table defs, foreign keys, PKs) while others can not be described in M (e.g. indexes, alias types, precision/scale on numeric/decimal types etc). I also haven’t figured out how to declare both nullability and length on members of type “Text”.  “:Text where value.Count <=100” works fine while “:Text? where value.Count <=100” will throw an error, but that might just be me not fully understanding the M syntax.
The M model for HuagatiRes can be downloaded from here:
http://blog.huagati.com/downloads/HuagatiResMmodel.zip
Huagati MGen / “Aker Brygge”
Now, I wasn’t stupid enough to actually type all of that into Intellipad. Instead, I did something even more stupid: I wrote a code snippet that extracts schema, type, table, column, foreign key, and index defs and generates M files.
If you’re interested in taking the schema->M tool (”Huagati MGen” a.k.a. “Aker Brygge”) for a test-spin, you can download it from here:
http://blog.huagati.com/downloads/HuagatiMGen.zip
It requires .net 3.5 SP1 and works against SQL Server 2005 and SQL Server 2008 databases only. Give it a connect string and an output folder and it will generate one M file per schema in the database you point it at, complete with code comments (based on object names and extended property descriptions) and all. Even [commented out] index definitions - prepared for the day when M gets support for index definitions…
Â
In the guts of MGen
The first version of MGen was a single exe, but I have since divided it up into a DLL containing a simple object model (MCodeProvider) for generating M code, along with a class that will connect to a SQL Server to extract schema definition and generate M using the MCodeProvider.
The exe file adds the simple user interface shown above, but if you prefer you can use the MCodeProvider or the DBSchemaMGenerator from your code instead. Note that this is just an experimental piece of sample code - it does not support everything that can be done in M, and likewise the MCodeProvider and related classes also support [db-side] features that don’t exist in the CTP version of M. The generated M code will reflect unsupported things such as indexes, extended properties etc as code comments.
Usage example 1 - generate M from an existing database:
string connectString = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True"; string targetFolder = "c:\temp";
//generate M from an existing db
Huagati.MGenCore.DBSchemaMGenerator mGenerator
   = new Huagati.MGenCore.DBSchemaMGenerator();
mGenerator.SchemaToM(connectString, targetFolder);
Â
Usage example 2 - generate M code using the MCodeProvider:
//create a new module Â
MCodeProvider code = new MCodeProvider();
MModule module
   = new MModule
   {
       Name = "TestModule",
       DescriptionComment = "Test Module"
   };
code.Modules.Add(module);
//define a simple type for identifying codes, string less than or equal to 10 characters in length Â
MSimpleType idCodeType
   = new MSimpleType
   {
       Name = "CodeType",
       Inherits = new MTextType(),
       DescriptionComment = "Simple type used for storing identifying codes."
   };
idCodeType.Constraints.Add(
   new MMemberConstraint
   {
       MemberMember = ConstraintMemberEnum.Length,
       Operator = ConstraintMemberOperator.LessThanOrEqual,
       ConstraintValue
           = new MIntLiteral
           {
               Value = 10
           }
   });
module.Members.Add(idCodeType);
//define a simple type for names, string less than or equal to 255 characters in length Â
MSimpleType nameType
   = new MSimpleType
   {
       Name = "NameType",
       Inherits = new MTextType(),
       DescriptionComment = "Simple type used for storing names."
   };
nameType.Constraints.Add(
   new MMemberConstraint
   {
       MemberMember = ConstraintMemberEnum.Length,
       Operator = ConstraintMemberOperator.LessThanOrEqual,
       ConstraintValue
           = new MIntLiteral
           {
               Value = 255
           }
   });
module.Members.Add(nameType);
//define a base type for all ref tables Â
MComplexType refTableType
   = new MComplexType
   {
       Name = "RefTableType",
       DescriptionComment = "Reference table base type."
   };
MMember idCode
   = new MMember
   {
       Name = "Code",
       Inherits = idCodeType
   };
refTableType.Members.Add(idCode);
refTableType.Members.Add(
   new MMember
   {
       Name = "Name",
       Inherits = nameType
   });
refTableType.Members.Add(
   new MMember
   {
       Name = "SortSeq",
       Inherits = new MInt32Type()
   });
refTableType.Members.Add(
   new MMember
   {
       Name = "Active",
       Inherits = new MLogicalType()
   });
refTableType.IdentityMembers.Add(idCode);
module.Members.Add(refTableType);
//define a test table, inheriting from the ref table type Â
MExtent testTable
   = new MExtent
   {
       Name = "Test",
       DescriptionComment = "Test reference table.",
       Inherits = refTableType
   };
module.Members.Add(testTable);
//add an index to the test table Â
MIndex testTableIndex1 =
   new MIndex
   {
       Name = "ix_TestTable_SomeOtherField"
   };
testTableIndex1.Members.Add(refTableType.GetMember("Name"));
testTable.Indexes.Add(testTableIndex1);
//save all modules Â
code.SaveCode("c:\\temp");Â Â
The output from the code above will be an M file containing the following:
//Test Module
module TestModule
{
   //Simple type used for storing identifying codes.
   type CodeType : Text where value.Count<=10;
   //Simple type used for storing names.    type NameType : Text where value.Count<=255;
   //Reference table base type.
   type RefTableType
   {
       Code : CodeType;
       Name : NameType;
       SortSeq : Integer32;
       Active : Logical;
   } where identity (Code);
   //Test reference table.
   //[ExtendedProperty("MS_Description", "Test reference table.")]
   Test : RefTableType*;
   // index ix_TestTable_SomeOtherField(Name);
}
