DSL and SQL Server Modeling CTP

• Download source - 42.8 KB

What is a DSL?

There are two kinds of languages to solve problems in business domains, general-purpose languages and domain-specific languages (DSL). General-purpose languages solve problems in many domains whereas domain-specific languages solve problems in a particular domain.

A general-purpose language provides lots of capabilities, abstraction structure, control, view, etc. Programming languages are general-purpose languages, for example in C# there are many facilities that can be used like the language features to implement Object-Orientation, LINQ to write queries, etc. All of this is useful but there is a question “Is it easy to learn and use?”

Domain-specific language isn't a new idea, each business has specialized vocabularies and a domain expert can use them as a language. SQL is a DSL for a database professional whereas it is implemented with a programming language in the background.

A DSL is a specific tool with limited focus on very particular conditions that improves productivity and avoids duplication and it provides an abstraction to make it easier to specify what’s going on in the code. It helps to improve interaction between a domain expert and the customers, they can read the code; validate the code, even write the code without needing a developer to translate the code.

External DSL vs. Internal DSL

When we call a language as External DSL it means the language exists outside the existing languages. In an External DSL, many aspects of the language must be implemented in the language. SQL is an example of External DSL.

An Internal DSL is a language made on top of an existing language or embedded inside a general-purpose language. It doesn't have the original programming language syntax but it has its own and limited syntax. Think of an API, there are some rules to use and users know how to use them but it’s just an API on top of a programming language. HTML and CSS are Internal DSLs.

Graphical DSL vs. Textual DSL

A domain-specific language can be either a visual diagramming language like UML or a textual language. Graphical DSL is great to get a whole view or a big picture of what’s going on, because extra information eliminated from it indeed hides extra information to make it possible to look at the problem from the top. But in Textual DSL, we have a DSL with all details and we can search and compare text so easily and also we have less dependency to mouse.

As you certainly know, Windows Workflow Foundation and BizTalk Orchestration are examples of graphical DSL and Visual Studio DSL Tools is a framework to create graphical DSLs like Class Diagram, Sequence Diagram, Activity Diagram…

Metadata

A common description about metadata is “Data about data”, but in this context we can also say “Data about application”.

With metadata, it’s possible to retrieve some information for example about a picture like resolution without directly accessing the picture main data. Search engines have used metadata widely too.

One of the best implementations of metadata is XAML that can be used to describe application behaviors and as you know, we can create an XAML-Only application.

Day by day, more and more software are moving to metadata programming and they find out the value of metadata. There are three types of metadata:

1. Descriptive metadata to discover resources
2. Structural metadata to show the relationship between different parts of a resource
3. Administrative metadata to manage resources

SQL Server Modeling CTP

SQL Server Modeling CTP is a set of future Microsoft modeling technologies that aim to provide significant productivity gains across the lifecycle of .NET Framework applications by enabling developers, architects, and IT professionals to work together more effectively. Its goal is to significantly increase programming productivity.

The primary goal of this technology is building applications through models and metadata provided by a domain-specific language created by a modeling language mixed with other visual modeling tools.

SQL Server Modeling CTP is composed of three components:

• A metadata repository (SQL Server Modeling Services) to drive application design, development, and management
• A visual tool (code name Quadrant) for building and viewing models visually
• A modeling language (code name M) for building textual domain-specific languages

SQL Server Modeling Services

SQL Server Modeling Services is a service like other services in the next version of SQL Server like Analysis Services and Reporting Services. It’s just a database containing a set of common models that can be imagined like declarative .NET Framework focused on the software application life cycle and it contains application metadata.

Why is it Just a database?

All of capabilities that exist in a database can be used in SQL Server Modeling Services like replication, scale-out, partitioning, backup, report, query, etc.

Base Domain Library (BDL) contains a couple of models like:

• System Runtime
• System Identity
• System Management
• Microsoft .Uml2
• Your models

Along with these models, there is a set of patterns to manage information in an efficient way for multiple users:

• Folder
• Security
• Lifecycle
• Containment
• Referencing
• Relationships
• Views
• Globalization/Localization

Integrate an Application Lifecycle Data

Data is generated throughout the application lifecycle such as business requirements, design artifacts, developer tasks, bugs, and test data is created by a specific tool and coordination between these data is really hard to achieve but in SQL Server Modeling Services we have all the data and can be retrieved with a single query.

System.Runtime contains all data about CLR objects like assemblies, modules, methods, types, …

Microsoft.UML2 contains UML diagrams like use case diagrams, class diagrams, sequence diagrams, activity diagrams …

Folders

Data in the database can be organized into folders hierarchy like file system, we can copy, move, delete, apply security, etc.

Modeling Tools

We can add our models into Modeling Services using modeling tools.

M Language

M is a text based domain-specific modeling language. A key advantage of modeling in text is ease with which both computers and humans can store and process text.

M acts as both a schema language that can validate that textual input conforms to a given language as well as a transformation language that projects textual input into data structures that are amenable to further processing or storage.

Using M to Create Model

M helps me to design our model and then we can have it in SQL Server Modeling Services. The Intellipad is a tool to assist write M code.

This is a sample of implicit type creation mapping between M and the generated SQL.

" M Language "

module Sales
{
    Customer:
    {
        (
            {
                CustomerID:Integer32 => AutoNumber;
                FirstName:Text(255);  
                LastName:Text(255);
                
            }
        )*
    }where identity CustomerID;
    
    Order:
    {
        (
            {
                OrderID:Integer32 => AutoNumber;
                OrderNumber:Text(255);  
                OrderDate:Date; 
                Customer:Customer;    
            }
        )*
    }where identity OrderID;
}

" SQL "

execute [sp_executesql] N'create schema [Sales]';
go

create table [Sales].[Customer]
(
    [CustomerID] int not null identity,
    [FirstName] nvarchar(255) not null,
    [LastName] nvarchar(255) not null,
    constraint [PK_Customer] primary key clustered ([CustomerID])
);
go

create table [Sales].[Order]
(
    [OrderID] int not null identity,
    [OrderNumber] nvarchar(255) not null,
    [OrderDate] date not null,
    [Customer] int not null,
    constraint [PK_Order] primary key clustered ([OrderID]),
    constraint [FK_Order_Customer_Sales_Customer] foreign key _
	([Customer]) references [Sales].[Customer] ([CustomerID])
);
go

This is the way you can create types and create tables in the database; the code is pretty easy.

M as EDM in Entity Framework

As you certainly know, the EDM is the bridge between your application and your data and is the component that allows you to work with your data conceptually rather than going directly against your database and trying to figure out the back-end schema.

Now we can use M language to create our entity model instead of using EDM.

" C# "

Sales.SalesContainer context = new Sales.SalesContainer
("Data Source=.;Initial Catalog=MyOsloTestDB;Integrated Security=True");

var customer = Sales.Customer.CreateCustomer(0, "Joe", "Brown");
var order = Sales.Order.CreateOrder(0, "Test", DateTime.Now);
order.Customer = customer;
context.AddToOrders(order);
context.SaveChanges();

GridView1.DataSource = context.Orders.Select(o=>o.Customer).ToList();
GridView1.DataBind();

M Grammar

The “M” language itself is an example of an external DSL, since it is a free-standing language optimized for modeling.

M Grammar provides an extensive tool set that supports language creation, testing, and deployment.

We can compile a grammar file into an image form and running the image file against input text written in the DSL.

When you run the compiled image file against an input text stream, the parser generated by MGrammar creates an output data structure called an MGraph. MGrammar provides an API that enables you to manipulate the output data structure, transforming it as your application requires.

Create a Language

This sample shows how to create a custom language named SalesLanguage using M and using language definition features in M:

Language definition features:

1. Token rules: The lowest level structure in the language like letters and words, we can use Regex to specify the rule.
2. Syntax rules: Defines the grammar in the language and they are a combination of tokens
3. Interleave rules: Exclude tokens like whitespaces
4. Projections: Shape of output

This is the sample grammar you can write in the Intellipad:

" M Grammar "

module Sales
{
    language SalesLanguage
    {
        syntax Main = p:Product* => Product {valuesof(p)};
        syntax Product = ProductStart NumberAttribute 
		Equals num:Number NameAttribute Equals n:Name 
                                    => { Number => num, Name => n };
        syntax Name = t:Text => t;
        
        token ProductStart = "product";
        token NumberAttribute = "Number";
        token NameAttribute = "Name";
        token Text = '"' t:TextWithWhitespace '"' => t;        
        token AlphaNumerical ='a'..'z' | 'A'..'Z' | '0'..'9';
        token Number = '0'..'9';
        token Equals = '=';
        token TextWithWhitespace = (AlphaNumerical | Whitespace)+;
        token Whitespace = '\t' | ' ';
        
        interleave IgnoreChars = '\r' | '\n' | Whitespace;
    }
}

Main syntax is required in your custom language and it is the entry point in M language in Grammar mode. Product* means Product collection and p: is alias name for Product collection. This is a projection in LINQ:

var productNumbers = Products.Select(p => Number = p.Number, Name = p.Name);

As you can see, you use Projection in M in the same way you used it in LINQ:

=> { Number => num, Name => n }

Tokens in the sample grammar are words and patterns of accepted words and alphabets.

token AlphaNumerical ='a'..'z' | 'A'..'Z' | '0'..'9';

After you created your MGrammar document, you should compile it with M Compiler (M.exe).

After that, the compiler creates a compiled grammar file named Product.mx. The next step is combining a text file and the grammar file to infer the model file. This is the sample input text file:

" DSL output "

product Number=1 Name="Monitor" 

product Number=2 Name="Mouse" 

product Number=3 Name="Keyboard"

You can use MGrammar Executor (MGX.exe) to infer a model file.

You can see that it’s a text file and a compiled grammar file we used as its input and inferred Product.m as output.

You can even specify an output type like XAML for MGrammar Executor:

This is the sample output model file generated from input and grammar:

module Sales
{
    Product : 
    {
        {
            ID : Integer32 => AutoNumber;
            Name : Text;
            Number : Integer32 ?;
        }*
    } where identity ID;
    
    Product
    {
      {
        Number => 1,
        Name => "Monitor"
      },
      {
        Number => 2,
        Name => "Mouse"
      },
      {
        Number => 3,
        Name => "Keyboard"
      }
    }
}

First part is constraint part for Product collection fields:

ID : Integer32 => AutoNumber;

This line is a constraint on ID field that should be Integer32 type.

The code is equivalent of this SQL script:

[ID] int not null identity
where identity ID;

This line is equivalent of this SQL script:

constraint [PK_Product] primary key clustered ([ID])

This is the sample generated T-SQL Script:

"Generated T-SQL"

set xact_abort on;
go

begin transaction;
go

set ansi_nulls on;
go

if not exists 
(
    select *
    from [sys].[schemas]
    where [name] = N'Sales'
)
    execute [sp_executesql] N'create schema [Sales]';
go

if not exists 
(
    select *
    from [sys].[schemas]
    where [name] = N'$MRuntime.Sales'
)
    execute [sp_executesql] N'create schema [$MRuntime.Sales]';
go

create table [Sales].[Product]
(
    [ID] int not null identity,
    [Name] nvarchar(max) not null,
    [Number] int null,
    constraint [PK_Product] primary key clustered ([ID])
);
go

create table [$MRuntime.Sales].[Product_Labels]
(
    [Label] nvarchar(444) not null,
    [Value] int not null,
    constraint [PK_Product_Labels] primary key clustered ([Label]),
    constraint [FK_Product_Labels_Value_Sales_Product] foreign key _
	([Value]) references [Sales].[Product] ([ID]) on delete cascade
);
go

create index [IR_Value] on [$MRuntime.Sales].[Product_Labels] ([Value]);
go

create function [$MRuntime.Sales].[LookupInProduct_Labels]
(
    @name as nvarchar(max)
)
returns table
as
return
        select top (1)
            [t3].[ID] as [ID],
            [t3].[Name] as [Name],
            [t3].[Number] as [Number]
        from [$MRuntime.Sales].[Product_Labels] as [p]
        cross apply 
        (
            select [$Product2].[ID] as [ID],
                [$Product2].[Name] as [Name],
                [$Product2].[Number] as [Number]
            from [Sales].[Product] as [$Product2]
            where [$Product2].[ID] = [p].[Value]
        ) as [t3]
        where [p].[Label] = @name;
go

declare @seed_Sales_Product bigint = 0;

declare @increment_Sales_Product bigint = ident_incr(N'[Sales].[Product]');

declare @reseed_Sales_Product bigint = coalesce(@seed_Sales_Product + _
	@increment_Sales_Product * 3, 0);

dbcc checkident(N'[Sales].[Product]', reseed, @reseed_Sales_Product);

set identity_insert [Sales].[Product] on;

insert into [Sales].[Product] ([ID], [Number], [Name])
    values (@seed_Sales_Product + @increment_Sales_Product * 1, 1, N'Monitor'),
        (@seed_Sales_Product + @increment_Sales_Product * 2, 2, N'Mouse'),
        (@seed_Sales_Product + @increment_Sales_Product * 3, 3, N'Keyboard');

set identity_insert [Sales].[Product] off;
go

commit transaction;
go

You don’t have to generate SQL Script from M file, you can use M command-line Utility to install the model file on your existing database or create a new database:

History

• 21^st September, 2010: Initial version