Programmatically generating SQL(DDL) from M

Monday, March 30, 2009

C# MGrammer Oslo

In this post, i will show how you can generate SQL programmatically from M. Now, so far i have learnt that MGrammer is a contract that converts user’s input into MGraph. Now, Oslo by default comes with MSchema. Through MSchema you can define a type and extend it with MGraph to populate your repository. Here, i will use MSchema to define an entity object , then MGraph and finally run this through a custom SQL generator in C# to get my DDL statement similar to what you can generate by using intellipad that comes with Oslo SDK.

Let’s define a Contacts M

module Contacts {
    type Person {
        Id : Integer32;
        Name: Text;
        Age: Integer32;
    } where identity Id;
    
    People : Person*
    {    
        {Id = 1, Name="Steve",Age=36}, 
        {Id = 2, Name="Kazi",Age=30}
    };
    Employee : Person*
    {    
        {Id = 1, Name="Nike",Age=29} 
    };
}

So, “Person” is a type with ID,Name and Age respectively that is under “Contacts” module with two different MGraph which are “People” and “Employee” that will generate separate create table statements of “Person” type.

Internally , M is represented somewhat like

Parsing the M all starts with Microsoft.M.Parser.SourceParser. In my sample M to SQL project, i used it like

SqlGenerator generator = SqlGenerator.Load(new StringReader(code));
string sql = generator.Generate();

Inside Load…

SourceParser parser = new SourceParser();
syntaxTerm = (CompilationUnit)parser.ParseTerm(reader, "buffer.m");
...
...

/// check if the parse was successful
if (!parser.LastParseSuccessful)
{
    /// do some error processing for invalid schema.
}

Now, once we have a valid grammar, our next step is to get the modules from ISytaxTerm implementation, for each module we have to process the MGraph for the specified type.

foreach (IModuleDeclaration declaration in syntaxTerm.Modules)
{
    builder.AppendSetExtactAbort();
    builder.AppendGo();

    builder.AppendBeginTransaction();
    builder.AppendGo();

    builder.AppendSetANSINulls();
    builder.AppendGo();

    builder.AppendCreateSchema(declaration.Name.Value);
    builder.AppendGo();
    builder.Append(ProcessMembers(declaration.Name.Value, declaration.Members));

    builder.AppendCommitTransaction();
    builder.AppendGo();
}

This shows the basic skeleton of how the output will look like. ProcessMembers is the actual place that generates the entity from type and associates Insert statements. Only, during the MGraph initialization type is realized. Therefore, the table name is equal to the extent name not to the type name.

Inside ProcessMembers …

/// There will be three declaration 
/// -TypeDeclaion
///- ExtentDeclaration
///- ExtnetDeclaration
foreach (IDeclaration declaration in declarations)
{
    if (declaration is TypeDeclaration)
    {
        typeDeclaration = (TypeDeclaration) declaration;
    }
 
    if (declaration is ExtentDeclaration)
    {
       /// use the type declaration from previous step.
        builder.Append(ProcessExtent(name, typeDeclaration, (ExtentDeclaration) declaration));
    }
}

TypeDeclaration is narrowed down to ParameteriedExpression , which has different operations for the declaring type, which is tracked by DeclartionReference.Name (“$Operator$Where”, “$Operator$Identity” , etc). Therefore, to process constraint and data types separately, we need to switch them for operation type. Now, the question is how the MGraph is referred from the type specified. The JSON like string is basically translated to GraphExpression. Like for the “Person” type , we have three properties. GraphExpression.Successors.Count will be equal to numeric value 3 and each of those which are GraphExpression themselves will have only one successor which is a M.Literal. This will actually contain the property name, type and value for parent type ref.

GraphExpression graphExpression = (GraphExpression) extentDeclaration.InitialValue;

if (extentDeclaration.InitialValue != null)
{
foreach (GraphExpression successor in graphExpression.Successors)
{
    int index = 0;

    foreach (var expression in successor.Successors)
    {
        if (expression is GraphExpression)
        {
            GraphExpression node = (GraphExpression) expression;

            Literal literal = (Literal) (node).Successors[0];
           /// process fragment
        }

        index++;
    }
   /// process the final statement
}

That’s all to get started. All the code has been taken from a MSqlProvider that i created while learning M. The technology is itself in bleeding edge so things will change and i will share my updated knowledge forward. You can further enhance MSQLProvider to run the result in configured database along with the mx.exe that comes with Oslo SDK.

You can download the source here to play around and see it live.

Enjoy!!

No Comments