Load methods

By default, SoftFluent CodeModeler builds an in-memory model (using SQLDom) of all C.R.U.D (Create, Read, Update and Delete) procedures for each entity. It also generates all procedures for relations between entities. However, it is unlikely that this will be enough to create a complete application. This the reason why the CMQL language exists.

CMQL is a simple yet powerful language that allows a developer to write a procedure (signature and body) that will load one or many instances of the same entity type. It is a mix between traditional SQL and OOP syntaxes.

CMQL has been designed to facilitate most common stored procedures creation. It is not a replacement for SQL ("Structured Query Language"). There are SQL constructs (standard or specific to the target database, like SQL Server's T-SQL or MySQL) that cannot be expressed in CMQL. For all these constructs, a developer can still use the model driven approach, using the raw keyword (see below).

More information such as available operators, examples or advanced methods, can be found in the Architect Guide, and more especially in the Methods chapter, Load and Load One articles.

Load definition

CMQL uses a different syntax for defining a procedure that will return one instance, and a procedure that will return more than one instance (a set, or a collection). This influences for example the Object Model producer which will attach the corresponding method to the entity class or to the entity collection class.

The general syntax is:

<loadType>([argumentType1] argument1, [argumentType2] argument2, ... , [argumentTypeN] argumentN) [From <source>] [<where> <order by>] | [raw]

• <LoadType> can be either load or loadone for loading a collection or one instance, respectively.

• <where> is a where clause (see below)

• <source> can be an Entity or a View name

• <order by> is an order by clause (see below)

raw means the procedure body is not CMQL but direct persistence code in a language that must be understood by configured target persistence producers. Although it is perfectly feasible, and easy to implement, to directly write custom code using .NET and relation SQL, the benefits of defining raw load methods in the model is to have all producers create their target artifacts automatically, regardless of where and how the load method is actually implemented. For example, the Web Service producer will by default automatically create one Service operation for each custom load method defined in the model. It is therefore quite easy to implement a Service Oriented Architecture, Cloud, or Micro Service on top of CodeModeler-generated persistence methods.

Arguments

Arguments are composed of an optional type name and a name. If the type name is not specified, CodeModeler will try to find a property in the entity that matches the specified name. Otherwise type names are resolved using CodeModeler's type system resolution algorithm.

Arguments are referenced in the method body using the '@' character.

For example, the following LoadByCardType method declares an argument named* CreditCard *with an implicitly defined CreditCardType type:

Note: CMQL does not support a variable number of arguments.

From clause

The from syntax resembles traditional SQL syntax:

from <source>

The source can be either the declaring entity name (the default) or a view name for this entity.

In general, the from clause is not used, as by default, CMQL uses the declaring entity as the source.

Where clause

The where syntax resembles traditional SQL syntax:

where <expression1> [and | or] <expression2> [and | or] ... <expressionN>

Supported operators for expressions are:

• unary operators: not, exists

• binary operators: and, or, equals, contains, freetext, like, &, |, ***, /, +, -, =, <>, >=, <=, >, <

• set operator: in (expression1, expression2, ... , expressionM)

Expressions also support the usage of literals:

• parameters (for example @MyArg)

• integer (for example 2403)

• floats (for example 1966.2403)

• string (for example "Kilroy was here")

• boolean (true or false)

• hexadecimal (for example 0xDEADBEEF)

• date/time (for example #1966/24/03 08:00:00#)

• guid (for example “EAE89191-76D4-4f67-9B96-1CB8CC8D27BC”)

 And finally, expressions can contain 'EPath' expressions.

'EPath' syntax

The difference between SQL and CMQL resides mostly in the syntax used for defining columns used in the query. Using CMQL, we do not speak about columns, we speak about EPath expression. EPath expressions use object oriented 'dot' type syntax to navigate between entities in the persistence layer.

An EPath expression can be an entity property, or a path of entity properties separated by the '.' character. EPath expression are transformed into a relational syntax tree by the CodeModeler model-to-persistence engine. Ultimately this syntax tree will be produced as a stored procedure.

EPath expressions also support enumerations project types.

For example, lets analyze the following model:

In this model,

• We define a LoadVistaCustomers custom method on the Customer entity

• The method loads a collection of customers (load prefix)

• The method has one argument of enumeration type CreditCartType (defined after)

• The method loads all Customers that have a PaymentInfo with a Visa CreditCartType.

Order By clause

The order by syntax resembles traditional SQL syntax:

order by <identifier1> [asc | desc], <identifier2> [asc | desc], ... , <identifierN> [asc | desc]

• <identifier> is an EPath expression (described above)

• asc  sorts by <identifier> in ascending order, which is the default if nothing is specified

• desc sorts by <identifier> in descending order