MSchema: An Example - Part 3

Url: http://wilderminds.blob.core.windows.net/downloads/StudentSchema.zip

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?