Rants Tagged with “MGraph”
1 (Total Pages: 1/Total Results: 3)
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?
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?
