CodeModeler Query Language (CMQL) Overview

CodeModeler Query Language is a domain specific language that specifically eases stored procedure creation.

CMQL is platform-independent and is therefore used to declare platform independent queries which will then be translated by producers in platform specific code.

For instance, the Microsoft SQL Server producer translates those queries into T-SQL stored procedures, whereas the MySQL producer translates them into MySQL ones.

Furthermore, those SQL queries are also accessible in upper layers since the Business Object Model producer will generate a corresponding C# method, the Service Object Model SubProducer a corresponding operation, and user interface producers a corresponding output.

Therefore, using CMQL is one of the means provided by SoftFluent CodeModeler to decouple your business logic from technology, which in the end allows you to build more flexible applications.

CMQL is not “another language to learn”. It’s a very simple language that was only designed to save time. There’s no learning curve, no training needed. No book should ever be written about it.

Identifier 'dot' notation

Identifiers support the 'dot' (.) notation, based on property names.

For instance, Customer.Country.Name is a valid CMQL identifier, and depending on the context, may be used for example to create a relational join between the Customer entity, and the Country entity. Name is a property name, declared on the Country entity, and probably has a matching column in the table corresponding to the Country entity:

The 'dot' notation is also supported on collection properties.

For example, Users.Roles.Name is also valid with the 'dot' notation following collection properties. If the relation between the User entity and the Role entity (through the Roles property) is a Many to Many relation, this will create a join between entities, and therefore the User table, the Role table and the association table between User and Role that has been inferred by the CodeModeler engine:

This is the SQL output with implicit joins between 3 tables, which demonstrates the power of CMQL, which can express complex SQL queries with one very simple line of text:

CREATE PROCEDURE [Commerce].[User_LoadByRoleToken]
(
 @token [nvarchar] (256),
 @_orderBy0 [nvarchar] (64) = NULL,
 @_orderByDirection0 [bit] = 0
)
AS
SET NOCOUNT ON
SELECT DISTINCT [Commerce].[User].[User_Id], [Commerce].[User].[User_Email], [Commerce].[User].[User_Password], [Commerce].[User].[_trackLastWriteTime], [Commerce].[User].[_trackCreationTime], [Commerce].[User].[_trackLastWriteUser], [Commerce].[User].[_trackCreationUser], [Commerce].[User].[_rowVersion] 
    FROM [Commerce].[User]
        LEFT OUTER JOIN [Commerce].[Role_Users_User_Roles] ON ([Commerce].[User].[User_Id] = [Commerce].[Role_Users_User_Roles].[User_Id])
                LEFT OUTER JOIN [Commerce].[Role] ON ([Commerce].[Role_Users_User_Roles].[Role_Id] = [Commerce].[Role].[Role_Id]) 
    WHERE ([Commerce].[Role].[Role_Name] LIKE @token)

Identifier Default Notation

If a CMQL identifier is declared as “$default”, it will be replaced by the inferred type default value. With the following model:

$default will be replaced at generation time by the default value of the guid type, which is the empty guid (00000000-0000-0000-0000-000000000000).

Identifier Column Notation

If a CMQL is declared as "$column.typeName", it will be replaced by the CodeModeler-generated TypeName column. This is valid only for derived entities, so the entity type name can be used in queries. With the following method body on a Customer entity deriving from a BaseCustomer entity:

load() where $column.typeName = ‘BaseCustomer'

the method will restrict the load to database rows corresponding to BaseCustomer instances.

Ambient parameters

CMQL identifiers also support the declaration of ambient (also called "static") parameters. Ambient parameter will be created as standard parameters for stored procedures, but as static parameter for calling code (the Business Object Model layer).

If an identifier is prefixed by “$parameter.”, the suffix will be used as an ambient parameter name. With the following method body:

load() where $parameter.MyParam1

the generated method in the BOM will have no parameter, but the code will need the definition of a static stub function called GetMyParam1 to successfully compile.

If the parameter name is surrounded by [ and ], the name will be parsed as a snippet, and the parameter name will be extracted starting from the last '.' character in the string, if any. With the following method body:

load() where Name = $parameter.[System.Environment.UserDomainName + "\\" + System.Environment.UserName]

the Test method generated in the BOM will have no parameter, and will pass the current authenticated user domain and name as a parameter to the corresponding generated stored procedure. The parameter name of the procedure will be inferred as “username”.

If you have multiple ambient parameters with the same final name, but still want to use the snippet notation, you must prefix the snippet with the final parameter name, like this:

load(int id1, int id2) where Name = $parameter.[Id1=Application.Id]=@id1 and $parameter.[Id2=Tenant.Id]=@id2

In this example, Application and Tenant both have a property named Id, but we want to distinguish them as Id1 and Id2.

Note: This ambient parameter feature is typically used in multi-tenant scenarios.

TOP Equivalence

CMQL does not provide a TOP operator or statement letting you restrict the number of lines retrieved from the query. The concept is available at the method level, through the “Maximum Count” (Advanced Properties tab) attribute:

Enumeration Support

CMQL fully supports enumerations declared in your model: you can use them naturally in your CMQL queries:

load() where Status=OrderStatus.Shipped

where OrderStatus is an enumeration declared in the model and Status is a property of this type.

View Support

Using CMQL you can also use views as the data source (note usage of the from keyword):

load from SampleView

Persistence Nullable Support

It's possible to indicate an identifier is nullable with regards to database producers by suffixing the parameter with a '?'. As a consequence, conditions applying to the parameter will be taken into account only if non-null:

load(int value?) where Value = @value

This method will generate this kind of SQL:

CREATE PROCEDURE [dbo].[Test_LoadByValue]
(
 @value [int] = NULL,
 @_orderBy0 [nvarchar] (64) = NULL,
 @_orderByDirection0 [bit] = 0
)
AS
SET NOCOUNT ON
IF(@value IS NULL)
BEGIN
    SELECT DISTINCT [Test].[Test_Id], [Test].[Test_Name], [Test].[Test_Value], [Test].[_trackLastWriteTime], [Test].[_trackCreationTime], [Test].[_trackLastWriteUser], [Test].[_trackCreationUser], [Test].[_rowVersion] 
        FROM [Test]
END
ELSE
BEGIN
    SELECT DISTINCT [Test].[Test_Id], [Test].[Test_Name], [Test].[Test_Value], [Test].[_trackLastWriteTime], [Test].[_trackCreationTime], [Test].[_trackLastWriteUser], [Test].[_trackCreationUser], [Test].[_rowVersion] 
        FROM [Test]
        WHERE ([Test].[Test_Value] = @value)
END

Model Nullable Support

On the other hand, prefixing the type with a '?' indicates CodeModeler that the parameter can be null in the object model (and not in the persistence layer) as the previous example. So the following method body:

load(?int value) where Value = @value

Will generate the following code in C#:

public static Cmql.TestCollection LoadByValue(System.Nullable<int> value)
{
    Cmql.TestCollection ret = Cmql.TestCollection.PageLoadByValue(int.MinValue, int.MaxValue, null, value);
    return ret;
}

And the following code in SQL:

CREATE PROCEDURE [dbo].[Test_LoadByValue]
(
 @value [int],
 @_orderBy0 [nvarchar] (64) = NULL,
 @_orderByDirection0 [bit] = 0
)
AS
SET NOCOUNT ON
SELECT DISTINCT [Test].[Test_Id], [Test].[Test_Name], [Test].[Test_Value], [Test].[_trackLastWriteTime], [Test].[_trackCreationTime], [Test].[_trackLastWriteUser], [Test].[_trackCreationUser], [Test].[_rowVersion] 
    FROM [Test]
    WHERE ([Test].[Test_Value] = @value)