Rants Tagged with “MSchema”
1 (Total Pages: 1/Total Results: 7)
If you haven't been following the new Specification for the M Language that the Oslo team has been cooking up, they have been making changes in response to feedback about the language. These changes will come to light in the next CTP. These changes are;
'=>' as label binding operator
The label binding operator has changed from '=' to '=>'.
@[] as escape for verbatim identifier names
Identifier names that need to be escaped use '@[]' instead of '[]'
'about' operator
about' is an operator that returns data for M identifiers (e.g., extents, type declarations, computed value declarations)
'item' removed
'item' is no longer a keyword used to iterate over a collection of collection of values. Instead, use 'value' to iterate over a collection, and 'value' plus parenthesis to iterate over a collection of collection.
'into' removed
'into' has been removed as a keyword with the 'join' and 'group by' query clauses.
'this' removed
'this' has been removed as a keyword that refers to all of the members of an entity type declaration. Instead, constraints now must be written after the member declarations and use 'value' to reference members.
Decimal without preceding 0
Decimals of the form '0.xxx' no longer require the preceding 0.
DateTimeOffset type and literal value added
A new data type, DateTimeOffset, has been added. In addition, a new literal kind, DateTimeOffset, has been added to write literals of the type DateTimeOffset
# undef removed
'#undef' has been removed from the supported set of preprocessor directives
Preprocessor scope changes
'#define' must occur before any conditional operator '#define' is scoped to a compilation unit, not a source file
Text patterns defined
Text value patterns are now defining including literals, character classes/ranges, repitition operators, 'any', 'empty' and 'error' keywords, and text pattern operators difference, intersect and inverse.
Productions defined
Productions are defining including pattern and token declarations, constructors, precedence rules, and constructor operators.
Rules defined
Rules are defining including token rules, syntax rules, interleave rules, and parameterized rules
Language defined
Language is defined
Binary literal syntax change
The literal syntax for binary has changed from \xNNNN to 0xNNNN.
binary shift operators removed
The binary shift operators (<< and >>) have been removed from the language.
Entity type computed value declarations removed
Entity types can no longer have computed value declarations
where/select become Where/Select
Where and Select query expression clauses now have the first character capitalized
Identity selector supports multiple fields
The identity selector now supports using more than 1 field as the identity to select an instance
extern
Extern is now supported for computed values and extent declarations
name overloaded changes
Now, within a module, computed values, extents and types must not have same names, where as before only computed values and extents could not have same names.
'.' leading dot identifier
A leading dot identifier '.' can be used to scope the remaining dotted identifiers to the current module rather than the current lexical scope
'::' scope identifier
The scope identifier '::' constructs a fully qualified identifier name with the module name to the left of the operator, and the module's member name to the right.
Attributes defined
Attributes as used by Mgrammar declarations is defined
Catalog definitions
The M catalog written in M is defined
SQL mapping
The M-to-SQL mapping is defined
I've been playing a lot with M to do my database and test data loads for some internal projects for AgiliTrain and I've noticed an interesting representation for how relationships in MSchema are generating their SQL. In MSchema you have a couple of ways to represent a relationship between tables. The relationship syntax is one feature I really like in MSchema. For example, let's take the Northwind database's Customers and Orders tables. In MSchema I can define them in a traditional way where the Order has a reference to the Customer table:
module Northwind
{
type Customer
{
CustomerID : Text#5;
CompanyName : Text#40;
ContactName : (Text#30)?;
ContactTitle : (Text#30)?;
Address : (Text#60)?;
City : (Text#15)?;
Region : (Text#15)?;
PostalCode : (Text#10)?;
Country : (Text#15)?;
Phone : (Text#24)?;
Fax : (Text#24)?;
} where identity CustomerID;
type Order
{
OrderID : Integer32 = AutoNumber();
OrderDate : (DateTime)?;
RequiredDate : (DateTime)?;
ShippedDate : (DateTime)?;
Freight : (Decimal9)?;
ShipName : (Text#40)?;
ShipAddress : (Text#60)?;
ShipCity : (Text#15)?;
ShipRegion : (Text#15)?;
ShipPostalCode : (Text#10)?;
ShipCountry : (Text#15)?;
CustomerID : Customer? where value in Customers;
} where identity OrderID;
Customers : Customer*;
Orders : Order*;
}
If you notice the CustomerID in the Order type, you can see that we are defining relationship in the type so that the database construction is very predictable. For example, an ERD of the database created looks like so:
My problem with this strategy is that when you define MGraph representations of the data, you have to define your objects separately. MGraph lets you assign names to do this so its pretty easy:
Customers
{
BobsCrabShack {
CustomerID = "BOBSH",
CompanyName = "Bob's Crab Shack",
}
};
Orders
{
{
OrderDate = 2009-01-01,
Freight = 199.00,
CustomerID = Customers.BobsCrabShack
}
}
Notice that the MGraph uses the alias set up in the customer to assign it to the Order. This way that MGraph (or testing data in my case) does not need to ever know about generated ID's. The SQL generation in the M Executor (mx.exe) takes care of that for us. There is a fundamental problem with this though.
For several situations (MGrammar's AST to MGraph for example), i'd like to nest the objects to imply that owner ship. For example, I'd like to craft my MGraph like this:
Customers
{
BobsCrabShack {
CustomerID = "BOBSH",
CompanyName = "Bob's Crab Shack",
Orders =
{
{
OrderDate = 2009-01-01,
Freight = 199.00
}
}
}
};
You should notice that the orders for Bob's Crab Shack are nested inside the Customer. When you're dealing with a tree of information, constructing the MGraph would be easier in this way. But this requires we look at the relationship differently in MSchema:
module Northwind
{
type Customer
{
CustomerID : Text#5;
CompanyName : Text#40;
ContactName : (Text#30)?;
ContactTitle : (Text#30)?;
Address : (Text#60)?;
City : (Text#15)?;
Region : (Text#15)?;
PostalCode : (Text#10)?;
Country : (Text#15)?;
Phone : (Text#24)?;
Fax : (Text#24)?;
Orders : Order*;
} where identity CustomerID;
type Order
{
OrderID : Integer32 = AutoNumber();
OrderDate : (DateTime)?;
RequiredDate : (DateTime)?;
ShippedDate : (DateTime)?;
Freight : (Decimal9)?;
ShipName : (Text#40)?;
ShipAddress : (Text#60)?;
ShipCity : (Text#15)?;
ShipRegion : (Text#15)?;
ShipPostalCode : (Text#10)?;
ShipCountry : (Text#15)?;
} where identity OrderID;
Customers : Customer* where item.Orders <= Orders;
Orders : Order*;
}
Two things to notice in the MSchema example, the relationship is defined in the parent not the child. The Customer type now has a property called Orders that is of type "Order*". So that the Customer is expected to have a list of orders. In the Customers extent, we create the constraint to say that each Order in the Orders property is in the Orders extent. (A bit confused by the identical naming but I think you can make sense of it).
So that is all good, right? Well, it is almost fine. The schema generation of this pattern is decidedly different:
Note that the relationship is now represented by a mapping table between the Orders and Customers. At first I thought this was just a mistake, but in fact its the right thing to do. Why? Because the MSchema language has no way of us telling it that the Orders are a type that are naturally 'owned' but Customers. In other words, Orders will have one and only one parent: Customers. Since MSchema can't describe that, it does the right thing and use a mapping table to establish the relationship so that other objects that contain Orders can establish their own relationship.
I'd love to have a way to have my cake and eat it too. If there was a way to specify that we were the one and only owner of a particular relationship, then it could create the SQL in a smarter fashion, but that feels like its trying to be too smart and might cause confusion.
If you want to play with the two styles, I've included a .zip file (linked above) with the two "M" files.
What do you think?
I've spent the better part of six weeks building the new AgiliTrain website and its been quite a lot of fun. Of course if you have been reading this blog for long you know that I usually take a personal project like this as an opportunity to learning something new. In this case I did three things I haven't done on a personal project before:
- Use ASP.NET MVC (Beta)
- Use Oslo's MSchema
- Use a Designer
I've been itching to use ASP.NET MVC on a project but until the beta arrived I didn't want spend too much time with it. Of course as you probably know, ASP.NET MVC released a RC yesterday so its pretty close to being done. I am still on the Beta until they RTW (release to web) ASP.NET MVC. Don't want to retrofit it twice.
I've been thinking a lot about how I would characterize my experience with ASP.NET MVC. I recently was at a talk from Paul Lockwood (at the Atlanta .NET User Group) and he thought that it was two weeks of a steep learning curve then bliss. That's pretty close to my experience. I thrashed for a couple of weeks wondering whether going back to WebForms would be faster...but I was patient.
I talk to some people about their fears of ASP.NET MVC and the big one I hear is lost of ViewState. I was never a fan of ViewState and was always perplexed that most projects used WebControls and not nearly enough HtmlControls. HTTP is stateless so in many many situations remaining stateless makes more sense. Since the HTML controls were lighter weight I tended to use them much more often. That certainly makes my move to MVC easier as I am not bogged down by the loss of ViewState.
Another contention I hear is that ASP.NET MVC allows you to write code more like the web. This means doing a lot of AJAX and using jQuery (or Dojo, or whatever you want) as your UI layer. While that is interesting in a wide variety of scenarios...I don't think PostBacks are as evil as we make them out to be. One of the problems with WebForms was that since it was tied to post-backs and sending back ViewState...those post-backs were always big. For AgiliTrain I didn't feel the need for a lot of wiz-bang. I am just presenting materials and taking data (for registration and such). Very simple and doing that in simple HTML still appeals to me. In fact, the argument against simple post-backs is lost without ViewState. My Postbacks to the server are tiny because they simply contain mostly just the data users are entering. I think the real evil was when post-backs were used as a replacement for event handling situations...the post-backs themselves weren't the problem...the programming model was.
I did use jQuery in a couple of places where I needed rich behavior in the UI but that was the exception rather than the rule. I built a website not a web application. If you're building a web application, this is likely to be reversed. Building lots of interactivity and paging and such makes sense in that case...just not mine.
But why did I use ASP.NET MVC at all? Was it because I wanted to learn a new technology or was it the best tool for the job? In my case both are true. I needed a site that is very SEO friendly and to do the kind of routing I wanted (like I did in my Wildermuth.com site) was harder than it should have been. I wanted a better experience. I also thought that since I was presenting information that is changing somewhat rapidly that I could develop it faster with ASP.NET MVC. Not faster in the initial implementation (because I had to learn MVC) but faster to add new functionality as it required it. I am not positive this is true but in the last few days I've been able to add new pieces of functionality to the site pretty easily. It feels a lot more modular than my other sites.
There were two big ah-ha moments for me in using ASP.NET MVC:
- When I was told to create models that contained the data for the view (instead of using my data model).
- When I integrated the designer's wireframes into the project.
The first of these was big because I was using ViewData for everything and it felt like Session state again. A big property bag of loosely typed data that I was hoping would work.
The second of these was a huge surprise as the MVC code was mostly clean HTML (no magic ClientID's or controls) so that integrating was simple.
In the next couple of parts of this series I will delve deeper into how I built up the site using ASP.NET MVC and why I think its a great approach for my future projects.
In Part 1 of this series I showed you how to create database schema with MSchema. Then in Part 2 I showed you how to use MGraph to create data to store the in the database along side database schema. In this last part, I will show you how to use the M tools to put the schema and data in the database. I use these tools to build my database during a build script.
The first thing we need to do is to compile the schema. You can do this with the m.exe tool that ships with the Oslo SDK. While many of the demo's I've seen have pushed the data into the Repository, I want to push it into a generalized database. (My site isn't ready to be build on top of Oslo since its going to be released quite soon so I am just using the MSchema stack to help me build the database). When we compile the schema we can tell the tool type of target we're going to aim for. In our case we will target TSQL instead of the repository. So the first call in my database build script is to build the schema like so:
m.exe students.m /p:image -t:TSQL10
This tells the m.exe compiler to build an image (in this case an .mx file) based on our schema (the students.m file). This stop only builds the database, we now want to push it into the database. We do this with the mx.exe tool:
mx.exe /i:students.mx /db:Training /ig /f /verbose
This part of the script uses the compiled version of the schema (the student.mx file created by our last step and insert it into a database called Training. We could specify a server and security credentials but since I am building it with the local database and integrated security, we didn't need to specify these. The /ig tells the database to ignore missing dependencies and try to build the database from the schema from scratch. The /f flag says force the schema (including deleting existing elements with the same name). Now we have the schema in the database. But what about the data we created?
To insert the data, our studentsdata.m file doesn't know about the schema so we need to hint it to the compiler:
m.exe studentsdata.m /p:image -r:students.mx -nologo -t:TSQL10
This call looks the same as the first except we reference our compiled schema file with the -r flag. Like before this creates a compiled version of the data we're putting in the database. So you probably have already guessed that we use the mx.exe tool to insert the data into the database:
mx /i:studentsdata.mx /db:Training /ig /f /verbose
This looks just like our schema database insertion, but we're going to use the compiled data file instead. Because we compiled it with the reference to the compiled schema file, the new .mx file has everything it needs to insert itself into the database as rows in the database.
Hopefully this series has helped you understand how the M languages and the tools can work together to manage schema creation for projects. There are a lot of other features that we haven't discussed (like versioning) that are supported but i'll leave that for another day.
Do you think you'll use MSchema on any upcoming project?
If you have read Part 1 of this series, you have seen how to create types, collections and constraints using MSchema. Along with most database schemas, I find the need to create some amount of data to go along with those schemas. For my current project (our new web site), I wanted to have some small set of data that included some basic workshops and locations.
To create the data we can use the MGraph language. MGraph is related to MSchema but is a way of describing concrete instances of data. To create an instance:
module Training
{
Workshop
{
{
Name = "Silverlight Workshop",
Description = "A three day workshop to teach Silverlight 2.",
Length = 3,
MaxAttendees = 16,
DefaultPrice = 1800.00
},
{
Name = "Advanced Silverlight",
Description = "Two-day course teaching advanced Silverlight topics.",
Length = 2,
MaxAttendees = 16,
DefaultPrice = 1200.00
}
}
}
The syntax looks similar to a mix between the C# object initialization syntax and JSON. The module matches the same module that the schema was defined in. Then it is the extent name (Workshop in this case) then an array of objects (notice the types defined inside curly braces). This can be used to generate insert statements. That's easy to do with actual insert statements, so why learn this format at all?
Foreign keys are what make this fun. If we wanted to add an event (who has a reference to a Workshop) we would need to be able to tell the MGraph language how to make the reference. I innocently thought of this approach:
Event
{
{
WorkshopId = 1,
EventDate = 2009-02-04
}
}
I assumed that I could set the id's manually then use them...to my surprise MGraph handles that better that I guessed. how so? The creation of the instances can be named. So if we change the workshop creation to use names like so:
Workshop
{
SilverlightWorkshop {
Name = "Silverlight Workshop",
Description = "A three day workshop to teach Silverlight 3.",
Length = 3,
MaxAttendees = 16,
DefaultPrice = 1800.00
},
AdvancedSilverlight {
Name = "Advanced Silverlight",
Description = "Two-day course teaching advanced Silverlight topics.",
Length = 2,
MaxAttendees = 16,
DefaultPrice = 1200.00
}
}
Now that the workshops are named, we can define our events with the names:
Event
{
{
WorkshopId = Workshop.SilverlightWorkshop,
EventDate = 2009-02-04
}
}
Now when we create the event, we can use the workshop (notice the Workshop is prefixed) to say that the workshop for this event is the one we created above. M takes care of the rest.
In the next part, we will show the command-line tools you can use to store the schemas and data in the database.
As most of you know, I run a small training company. We are in the midst of a re-write of our main website to allow for more cohesive registration and information about our classes. For this project, I am completely re-creating my database schema (as the old one was a bit 'off the cuff'). What a great opportunity to try out MSchema to build the new schema.
I am going to show you some features in three parts. In this first part I will show you how to define some interesting data types in MSchema. In the second part I will show you how to define your static or test data in MGraph that can be inserted into the database along with your schema. Then finally in the third part I will show you how to use the command-line tools to build your schema from the M files. Onto the first part!
I'll admit, I hate visual editors. In fact, in his PDC talk, it was as if Doug Purdy was talking to me when he said we have an emotional connection to our text editors. I have spent some time with emacs.NET IntelliPad and so far I am impressed. It is taking some getting used to but the extensibility is pretty impressive.
I like the way that MSchema forces me to create a type then push it into an extent (e.g. to make it a table). For example, I started with a Workshop type that defined a training topic:
module Training
{
type WorkshopDef
{
WorkshopId : Integer32 = AutoNumber();
Name : Text#100;
Description : Text?;
Markup : Text?;
Length : Integer32;
MaxAttendees : Integer8;
DefaultPrice : Decimal19;
} where identity WorkshopId;
}
I start with a module which just creates something like a namespace for our types and extents. This module will become the Schema in SQL Server for our tables and views. Next, I created a type with the fields I wanted. You notice that I post-pended "Def" on the type. This is mostly just a convention I made up so that I could name the collections singular (we'll see more about that later when we see the generated schema). You can see the fields for my type and the constraints I am specifying (e.g. Text#100 is the same as saying Text where value.length <= 100). Like in C#, the question mark says that the field is nullable. Finally, I specify that the type has an identity using the field(s) that make up my primary key.
At this point all I have is a type, no tables or view or anything really. I need to extend this into a collection:
Workshop : WorkshopDef*;
This extent simply says create some place to hold things that look like WorkshopDef and call it Workshop. The asterisk at the end of WorkshopDef implies zero or more (like in regular expressions). This set of schema would yield a small amount of SQL:
create schema [Training];
go
create table [Training].[Workshop]
(
[WorkshopId] int not null identity,
[DefaultPrice] decimal(19,6) not null,
[Description] nvarchar(max) null,
[Length] int not null,
[Markup] nvarchar(max) null,
[MaxAttendees] int not null,
[Name] nvarchar(100) not null,
constraint [PK_Workshop] primary key clustered ([WorkshopId])
);
go
You can see the schema created closely matches our type definitions. That's the cool factor for me. Let's do something more interesting. For each Workshop I have a list of Topics (and eventually sub-topics). These are bullet point items that show what is covered in a workshop. So I can add a new type like so:
type TopicDef
{
TopicId : Integer32 = AutoNumber();
WorkshopId : WorkshopDef;
Name : Text#100;
} where identity TopicId;
Note that when I define that I want a WorkshopId to be a reference to another type, I specify it as the type I am referring to. This means in our extent for Topics we can create the foreign key like so:
Topic : TopicDef*
where item.WorkshopId in Workshop;
This essentially says that we want topics, but be sure that WorkshopId are in the Workshop table (the foreign key). The next thing to show is the real power of constraints. If you saw the Doug Purdy talk you probably saw that you could create a constraint for simple data like so:
type AttendeeDef
{
AttendeeId : Integer32 = AutoNumber();
FirstName : Text#50;
LastName : Text#50;
MiddleName : (Text#50)?;
DateRegistered : Date;
HasCancelled : Logical = false;
Phone : (Text#50)?;
Email : (Text#100)?;
TShirtSize : Integer32 where value <= 5;
} where identity AttendeeId;
In the TShirtSize, notice how I am constraining the value to a range of numbers from zero to five. This may be a magic way of translating from small to 3x. The SQL actually works as it creates a check constraint:
create table [Training].[Attendee]
(
[AttendeeId] int not null identity,
[DateRegistered] date not null,
[Email] nvarchar(100) null,
[FirstName] nvarchar(50) not null,
[HasCancelled] bit not null default 0,
[LastName] nvarchar(50) not null,
[MiddleName] nvarchar(50) null,
[Phone] nvarchar(50) null,
[TShirtSize] int not null,
constraint [PK_Attendee] primary key clustered ([AttendeeId]),
constraint [Check_Attendee] check ([Training].[Check_Attendee_Func](
[AttendeeId],
[DateRegistered],
[Email],
[FirstName],
[HasCancelled],
[LastName],
[MiddleName],
[Phone],
[TShirtSize]) = 1));
go
The Check_Attendee_Func is a generated function to test for constraints (including the size of TShirtSize. But that is making TShirtSize a magic number. I don't like magic numbers. Let's create something like an enumeration instead:
type TShirtSizeEnum
{
"Small",
"Medium",
"Large",
"X-Large",
"2X",
"3X"
};
type AttendeeDef
{
AttendeeId : Integer32 = AutoNumber();
FirstName : Text#50;
LastName : Text#50;
MiddleName : (Text#50)?;
DateRegistered : Date;
HasCancelled : Logical = false;
Phone : (Text#50)?;
Email : (Text#100)?;
TShirtSize : TShirtSizeEnum;
} where identity AttendeeId;
The new TShirtSizeEnum type can be used to constrain the value of the TShirtSize field. How do we do that? We simply tell MSchema that we are going to store a TShirtSizeEnum in that field and it creates it in the generated SQL.
When I first grabbed the Oslo SDK, I wanted to first dive into MSchema. MSchema is a language for defining your data store and relationships between data that Oslo uses to define how to handle storage. My first attempt was to try and replicate the store model of my VideoGameStore data that i've been using to show off Silverlight and ADO.NET Data Services. My original attempt was:
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*;
}
This resulted in the non-decorator table solution of:
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
The reason this didn't work is that (curly braces aside), MSchema isn't talking inheritance (as I saw it). In my model I specify that the type "Game" is 'derived' from "Product" which feels like inheritance but is actually extended the type, which is why the resulting SQL results in two tables with overlapping data.
What I should have done was:
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*;
}
In this case I am creating a type that encompasses the Product type so in this case by including the Product in the type, I am creating that extension that wanted. By making this change I get a different database schema generated:
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]
(
[Product] int not null,
[Developer] nvarchar(150) not null,
[Genre] nvarchar(100) not null,
[Publisher] nvarchar(150) not null,
constraint [PK_Games] primary key clustered ([Product]),
constraint [FK_Games_Product_VideoGameStore_Products]
foreign key ([Product])
references [VideoGameStore].[Products] ([ProductId])
);
go
So my new Games table truly extends the Product table (as a decorator table). I have a reference to the Product in the new table (represented by the Product value which is a foreign key to the Product table).
My problem is that MSchema (and likely with the rest of the M family of languages) is that they feel like curly-braced languages (e.g. C#, C++, C, Java) so that when I am writing M I get caught up in trying to apply concepts from that world to this very different world.
At least the guys on the Oslo forum helped sort me out...
