In Part I of this series, I talked about Oslo in general and about the tool Telerik is building for Oslo. Where we stand today is that I modeled a simple entity (User) and I still have to model some domain entities in MSchema and MGraph. The application I am modeling will allow a user to create a “project” that has the connection strings to the two Oslo repositories they are comparing. Then they will have to in a very Red Gate SQL Compare like fashion compare the entities in the repository and report back a status, including showing the offending M code that is causing a problem side by side with the good M code. Let’s get started modeling my top level domain with M.

As I am thinking now I need a “project” entity. Here is my first stab at a project entity.

//mschema to define a Project type
type Project
{
    ProjectID : Integer64 = AutoNumber();
    ProjectName : Text#25;
    ConectionStringSource : Text;
    ConectionStringDestination : Text;
    DateCompared: DateTime;
    Comment: Text?;
    ProjectOwner: ApplicationUser;
} where identity ProjectID;

You can see that I am making a reference to the ApplicationUser type in my “ProjectOwner” field. Down the line we will have this as a foreign key relationship in SQL Server, but we don’t have to worry about that now, for now we just realize that a ProjectOwner will refer back to the ApplicationUser type we build in Part I.

Here is how the type looks in iPad:

Just like before, I need to see some data before I can really figure out what my type is doing. Call me old school or a “database weenie” but I just connect the dots better when I see some data. So using MGraph, I am showing the data here:

//this will define a SQL foreign key relationship
ProjectCollection : Project* where item.ProjectOwner in ApplicationUserCollection;

ProjectCollection
{
    Project1{
        ProjectName = "My Project 1",
        ConectionStringSource = "Data Source=.;Initial Catalog=MyDB1;Integrated Security=True;",
        ConectionStringDestination = "Data Source=.;Initial Catalog=MyDB2;Integrated Security=True;",
        Comment="Project Comment",
        DateCompared=2009-01-01T00:00:00,
        ProjectOwner=ApplicationUserCollection.Steve //direct ref to steve (FK)
    },
    Project2{
        ProjectName = "My Project 2",
        ConectionStringSource = "Data Source=.;Initial Catalog=MyDB1;Integrated Security=True;",
        ConectionStringDestination = "Data Source=.;Initial Catalog=MyDB2;Integrated Security=True;",
        Comment="Project Comment",
        DateCompared=2009-01-01T00:00:00,
        ProjectOwner=ApplicationUserCollection.Zarko //direct ref to Zarko (FK)
    }
}

Notice that we define a relationship between the ProjectOwner and the ApplicationUserCollection from yesterday. This gives us the ability to use the named instances of the users and even gives us IntelliSense as shown below:

We are now going to model the results of the comparison of the repositories. I envision a grid showing you each object, its status, name, M code, and asking you to take some action. Let’s model the results. First we will need the Stats lookup values:

//Status type
type ComparisonStatus
{
    StatusID:Integer64=AutoNumber();
    StatusDS:Text#25;
} where identity StatusID;

//mgraph to get some data in to the status
StatusCollection:ComparisonStatus*;

StatusCollection
{
    Status1{StatusDS="Exist Only in Source"},
    Status2{StatusDS="Exist Only in Destination"},
    Status3{StatusDS="Exist in Both, Identical Structure"},
    Status4{StatusDS="Exist in Both, Changes"}
}

Next I need to model the results with a results type.

//mschema for the results
type ComparisonResults
{

ProjectRunID: Integer64=AutoNumber();
ProjectRunDate: DateTime;
ProjectID:Project;   //FK to Project
SourceTypeName: Text?;
SourceTypeM: Text?; //is it possible to generate this on the fly? is there value in storing it?
DestinationTypeName: Text?;
DestinationTypeM: Text?; //is it possible to generate this on the fly? is there value in storing it?
StatusID: StatusCollection; //FK

} where identity ProjectRunID;

After I put some data into this type, I immediately realized that the user will run the project multiple times and we will have to have a 1:M relationship between the run of the project’s results and the types. Meaning when you get the results there will be many types associated with each results. So I will spare you the iterations I went through with MGraph, but because of MGraph, I realized that this model was flawed! Here is the refactored version:

    //wow, we need refactoring tools badly in iPad! :)   
    //mschema for the results
    type ComparisonResults
    {
        ProjectRunID: Integer64=AutoNumber();
        ProjectRunDate: DateTime;
        ProjectID:Project;   //FK to Project
    } where identity ProjectRunID;

    //this will define a SQL foreign key relationship
    ResultsCollection : ComparisonResults* where item.ProjectID in ProjectCollection;
    //mgraph for some test data
    ResultsCollection
    {
        Result1{
        ProjectRunDate=2009-01-01T00:00:00,
        ProjectID=ProjectCollection.Project1
        }
    }

Notice how we have some relationships stored back to ProjectCollection.

Now we need to model the details:

//mschema for details
type ComparisonResultDetail
{
    ProjectRunID:ComparisonResults; //FK
    TypeID: Integer64=AutoNumber();
    SourceTypeName: Text?;
    SourceTypeM: Text?; //is it possible to generate this on the fly? is there value in storing it?
    DestinationTypeName: Text?;
    DestinationTypeM: Text?; //is it possible to generate this on the fly? is there value in storing it?
    StatusID: StatusCollection; //FK
} where identity TypeID ;//need a composite PK of ProjectRunID and TypeID

Now we need to add some data via MGraph. Remember it was above in MGraph where I had this breakthrough.

//this will define a SQL foreign key relationship, two FKs actually separated by a comma
ResultsDetailCollection : ComparisonResultDetail* where item.StatusID in StatusCollection,
     item.ProjectRunID in ResultsCollection;

ResultsDetailCollection
    {
        {
        ProjectRunID=ResultsCollection.Result1,
        SourceTypeName="Customers",
        SourceTypeM="m code here",
        DestinationTypeName="Customers",
        DestinationTypeM="m code here",
        StatusID=StatusCollection.Status1
        },
        {
        ProjectRunID=ResultsCollection.Result1,
        SourceTypeName="Orders",
        SourceTypeM="m code here",
        DestinationTypeName="Orders",
        DestinationTypeM="m code here",
        StatusID=StatusCollection.Status2
        } ,
        {
        ProjectRunID=ResultsCollection.Result1,
        SourceTypeName="Order Details",
        SourceTypeM="m code here",
        DestinationTypeName="Order Details",
        DestinationTypeM="m code here",
        StatusID=StatusCollection.Status3
        },
         {
        ProjectRunID=ResultsCollection.Result1,
        SourceTypeName="Products",
        SourceTypeM="m code here",
        DestinationTypeName="Products",
        DestinationTypeM="m code here",
        StatusID=StatusCollection.Status4
        }
    }

So today I modeled some domain entities and learned that when you play around with adding data via MGraph, you will learn and evolve your model much better. I suspect that showing this to the users will help, that is one of the goals of Quadrant. So with this model, I still have not pushed it into the repository yet, I am saving the data on disk in M files. I think that pushing to the repository may be important to do soon (time will tell if this is a best practice or not, remember I am learning!) It is now time to start playing with the MGraph and MSchema transformations to TSQL, that will be the subject of Part III.