I decided to take a brief look at the upcoming modeling framework Oslo, more specifically "M", a language intended for defining data models and domain-specific languages.

To get started, you can download the January 2009 CTP (Community Technology Preview) here.

Apparently, you can use "M" to concisely define a data model for your application, and using the Intellipad tool (included with the Oslo CTP), generate working T-SQL that could be used to create your database schema in SQL Server.

My goal was to create a model with a many-to-many relationship and also define some initial entities in that model.

I wanted to create two types, Users and Roles, where a User has an id, a username, and a password, and a role has an id and a name. I wanted the relationship between users to be many-to-many, such that a user could have 0 or more roles, and roles were not restricted to a single user.

I fired up Intellipad and set it to "M Mode" and after playing around for a while and some googling, came up with this model (module?).

module MyModel
{
  type User 
  { 
      Id : Integer32 = AutoNumber(); //Id should be generated automatically if not specified..
      Username : Text where value.Count<=50; //max length of 50
      Password : Text; 
      EmailAddress : Text;
  } where identity(Id); //Id is the key	
  
  type Role
  {
      Id : Integer32 = AutoNumber();//Id should be generated automatically if not specified..
      Name : Text where value.Count<=20;    //max length of 20
  } where identity(Id);    //Id is the key
    
  type UserRole
  {
      User : User where value in Users;//Like a foreign key.. The user must exist in the Users extent
      Role : Role where value in Roles;        
    }    
    
  Users : User* //This is an extent – will be mapped to a SQL table
  {    
      User1:User{Username = "Johnny5",
           Password = "Alive", 
           EmailAddress = "johnny5@fakeemail.com"}//Initial User value – map to SQL Insert
  }
  
  Roles : Role*
  {
     Role1:Role{Name="Admin"}//Initial Role value – will map to SQL Insert
  };    
  
  UserRoles : UserRole*
  {
     {User=Users.User1, Role=Roles.Role1} //Initial UserRoles value – map to SQL Insert
  };   
}

This model allowed me to define the User and Role types concisely and also their relationship with the UserRole type.

One thing that I should mention is the way that I defined the initial values for Users and Roles.

I defined the initial user as:

User1:User{Username = "Johnny5", Password = "Alive",  EmailAddress = "johnny5@fakeemail.com"}

All of the posts and examples I saw on the net showed initial values in the format:

{Username = "Johnny5", Password = "Alive",  EmailAddress = "johnny5@fakeemail.com"}

The additional name and type qualifiers were left out.

By adding these to the definition, I was able to reference them in UserRoles extent and also define initial values for a relationship.

To generate the T-SQL for this model, I selected Generic T-SQL Preview from the M Mode menu.

This generated the following SQL:

set xact_abort on;
go

begin transaction;
go

set ansi_nulls on;
go

create schema [MyModel];
go

create table [MyModel].[Users]
(
  [Id] int not null identity,
  [EmailAddress] nvarchar(max) not null,
  [Password] nvarchar(max) not null,
  [Username] nvarchar(50) not null,
  constraint [PK_Users] primary key clustered ([Id])
);
go

create table [MyModel].[Roles]
(
  [Id] int not null identity,
  [Name] nvarchar(20) not null,
  constraint [PK_Roles] primary key clustered ([Id])
);
go

create table [MyModel].[UserRoles]
(
  [Role] int not null,
  [User] int not null,
  constraint [FK_UserRoles_User_MyModel_Users] foreign key ([User]) references [MyModel].[Users] ([Id]),
  constraint [FK_UserRoles_Role_MyModel_Roles] foreign key ([Role]) references [MyModel].[Roles] ([Id])
);
go

insert into [MyModel].[Users] ([Username], [Password], [EmailAddress])
 values (N'Johnny5', N'Alive', N'johnny5@fakeemail.com');
declare @MyModel_Users_Id0 bigint = @@identity;

insert into [MyModel].[Roles] ([Name])
 values (N'Admin');

This is pretty cool. We've defined a model in a much more concise way than all that SQL, and let the tools do the dirty work.

It's also pretty cool what it did with the Inserts in the generated SQL. After creating the initial Users and Roles values, the script stores their identity values in local variables to be used in the upcoming insert for the UserRoles table.

It figured out that it would need those values later, and generated a working script to create a SQL database matching our model along with insert statements to populate the tables with the desired initial values.

I did find that the script is intended for SQL Server 2008 though and had to make very slight modifications to make it work in SQL Server 2005. SQL Server 2005 doesn’t allow setting the value of local variables in the declaration, so each declare statement had to be split into a declare and a set.

I've only scratched the surface of what Oslo is supposed to offer so far, but it does seem promising… as modeling is becoming a bigger trend in software development.