Archive

Posts Tagged ‘CFQL’

CFQL Raw methods


CodeFluent Query Language (aka CFQL) allows developers to define platform agnostic methods. For exemple you can write:

LOAD(FirstName) WHERE FirstName STARTSWITH @FirstName 

This CFQL method will be translated to SQL by the persistence producer you decide to use. This is very powerful but when you want to write advanced procedures, you have to write platform specific code.

Read more about CFQL: http://blog.codefluententities.com/2013/07/25/hands-on-cfql/.

Inline SQL code

You can inline SQL code in CFQL by surrounding it with “[“ and “]”:

LOAD(FirstName, int year) WHERE FirstName STARTSWITH @FirstName AND [DATEPART(yy, $Customer::DateOfBirth$) = @year] 

You have to set CheckLevel to None to confirm that you understand what you are doing:

Method Properties

The SQL code generated:

CREATE PROCEDURE [dbo].[Customer_LoadByFirstNameAndYear]
(
 @FirstName [nvarchar] (256),
 @year [int],
 @_orderBy0 [nvarchar] (64) = NULL,
 @_orderByDirection0 [bit] = 0
)
AS
SET NOCOUNT ON
SELECT DISTINCT [Customer].[Customer_Id], [Customer].[Customer_FirstName], [Customer].[Customer_LastName], [Customer].[Customer_DateOfBirth] 
    FROM [Customer]
    WHERE (([Customer].[Customer_FirstName] LIKE (@FirstName + '%')) AND DATEPART(yy, [Customer].[Customer_DateOfBirth]) = @year)

SQL method

Sometimes you need to write a custom method directly in SQL. Set the text to:

  • LOAD(arguments) RAW when the return type correspond to a collection of the current entity
  • LOADONE(arguments) RAW when the return type correspond to one instance of the current entity
  • RAW(arguments) otherwise. You have to specify the return type in the property grid

If you want to target multiple DBMS, you have to write one raw body by DBMS:

CodeFluent Query Language Editor

SQL code generated:

CREATE PROCEDURE [dbo].[Customer_LoadRaw]
(
 @year [int],
 @_orderBy0 [nvarchar] (64) = NULL,
 @_orderByDirection0 [bit] = 0
)
AS
SET NOCOUNT ON
SELECT * FROM Customer 
WHERE DATEPART(yy, [Customer].[Customer_DateOfBirth]) = @year

Use persistent view

Persistent views will be translated to SQL views. Persistent views are a key point since they can be used:

  • To focus, simplify, and customize the perception each user has of the database.
  • To control access to rows and columns of data.
  • To aggregate data for performance.

Persistent views in CodeFluent Entities are attached to the entity concept: an entity can have multiple views. Views can be used for CFQL methods Load, LoadOne, Count, Delete and Search
Views can be auto-generated or you can provide the SQL code.

Create the view:

View Editor

CustomerView

Use the view in a CFQL method:

LoadFromView

The generated SQL code:

CREATE VIEW [dbo].[vCustomerCustomerView]
AS
SELECT 
    [Customer].[Customer_Id], 
	[Customer].[Customer_DateOfBirth], 
	[Customer].[Customer_FirstName] + ' ' + [Customer].[Customer_LastName] AS FullName
FROM Customer

CREATE PROCEDURE [dbo].[Customer_LoadFromView]
(
 @_orderBy0 [nvarchar] (64) = NULL,
 @_orderByDirection0 [bit] = 0
)
AS
SET NOCOUNT ON
SELECT DISTINCT [vCustomerCustomerView].[Customer_Id], [vCustomerCustomerView].[Customer_DateOfBirth], [vCustomerCustomerView].[FullName] 
    FROM [vCustomerCustomerView]
    WHERE ([vCustomerCustomerView].[FullName] LIKE 'J%')

Target Name Transformation (TNT)

Using the name of a column in a RAW method is not safe. Indeed CodeFluent Entities allows to define its own naming convention. So if you write the name of a column in a raw method and then you change the naming convention of your project, your method won’t work anymore.
To handle this case, CodeFluent Entities introduce TNT. In a Raw method you can refers to a column by using for example “$Customer::DateOfBirth$”. This will be replaced by CodeFluent Entities by the name of the column corresponding to the property “DateOfBirth” of the entity “Customer”.

TNT supports the following syntaxes:

  • $[EntityName]$ corresponds to the table name,
  • $[PropertyName]$ corresponds to the property name,
  • $[EntityName]::[PropertyName]$ corresponds to the column name,
  • $[EntityName]:[ViewName]$ corresponds to the view name,
  • $[EntityName]:[ViewName]:[PropertyName]$ corresponds to a column name in the defined view,
  • $[Namespace].[EnumerationName].[EnumerationValue]$ corresponds to the enumeration value of an enumeration declared in the model.

The full documentation is available here: http://www.softfluent.com/documentation/Methods_TargetNameTransformation.html

Happy CFQLing,

The R&D team

Breaking Change: Enabling non-Latin character support


A few months ago we added support for non-Latin characters in models. Though nice at first, we noticed that it slowed down method inference noticeably as we had to load non-Latin character sets to parse CFQL when a majority of our users were using Latin characters indeed.

This is why starting with build 646 and upper, we disabled non-Latin character support in CFQL. Nonetheless, you’ll be glad to know that this is configurable, so if you do want to use Cyrillic in your CFQL queries you’ll have to enable it explicitly, and here’s how to do it!

 

Enabling support is done through an appSetting in the configuration file. Here’s a sample CodeFluent.Build.exe.config configuration file which you can place next to CodeFluent.Build.exe (e.g. “%ProgramFiles(x86)%\SoftFluent\CodeFluent\Modeler”):

<?xml version="1.0" encoding="utf-8" ?> 
<configuration> 
    <appSettings> 
    <add key="CfqlAllowsNonLatinCharacters" value="true" /> 
  </appSettings> 
</configuration> 

Please note that you’ll also need to set that appSetting in the devenv.exe.config configuration file to be able to use non-Latin characters in CFQL queries at design time as well.

 

Cheers,

Carl Anderson

What’s CodeFluent Query Language?

March 30, 2012 Leave a comment

CodeFluent Query Language (a.k.a. CFQL) allows you to write platform independent data accessing queries which producers (producer = code generator) will translate into optimized stored procedures.

For instance you can define the following CFQL query in your model:

<cf:method name="LoadByName" body="load(Name) where Name startswith @Name"/> 

The SQL Server Producer will generate the following stored procedure:

CREATE PROCEDURE [dbo].[Test_LoadByName] 
( 
@Name [nvarchar] (256) 
) 
AS 
SET NOCOUNT ON 
SELECT DISTINCT [Test].[Test_Id], [Test].[Test_Name] 
    FROM [Test] 
    WHERE ([Test].[Test_Name] LIKE (@Name + '%'))

RETURN

And the Oracle Database Producer will generate the following equivalent:

PROCEDURE LoadByName(CF_CURSOR OUT "Sample"."CF_#Runtime".CF_CURSOR_TYPE, 
        "#Name" NVARCHAR2) AS 
    V_CF_CURSOR "Sample"."CF_#Runtime".CF_CURSOR_TYPE; 
    BEGIN 
        OPEN V_CF_CURSOR FOR 
        SELECT "Test"."Test_Id", "Test"."Test_Name" 
            FROM "Sample"."Test" 
            WHERE ("Test"."Test_Name" LIKE ("#Name" || N'%')); 
    CF_CURSOR := V_CF_CURSOR; 
    END;

As you can see data accessing queries are not built in-memory dynamically at run time but instead a classic stored procedure is generated at development time.

As a consequence developers can:

  • see what’s going to happen at run time and avoid bad surprises (like this one when using EF for instance),
  • debug it easily if they need to,
  • change the code if the need to.

 

Cheers,
Carl Anderson

CodeFluent Entities: Customizing the Method Body

December 19, 2011 Leave a comment

CodeFluent Entities provides “Methods” which allows developers to define custom data accessing methods (load, loadone, delete, count, search) which persistence producers will translate into stored procedures.

For instance, the following method:

image

Will generate the following stored procedure:

CREATE PROCEDURE [dbo].[Setting_LoadByName]
(
@Name [nvarchar] (256)
)
AS
SET NOCOUNT ON
SELECT DISTINCT [Setting].[Setting_Id], [Setting].[Setting_Name], [Setting].[Setting_Value] FROM [Setting]
    WHERE ([Setting].[Setting_Name] = @Name)

RETURN

You can also create what we call Raw methods which are platform specific code such as T-SQL or PL-SQL, but you can also mix both, that is to say create a CFQL method so upper layers benefit from the out-of-the-box features provided by such methods and just override the actual body of the stored procedure.

For instance say you wanted to create a topped method which the number of returned rows can be passed to the method. The “TOP” statement doesn’t exist in CFQL (see TOP in CFQL for more information) so a way to do this would be to mix CFQL and T-SQL for instance.

To do so, start by defining your CFQL body:

image

And then define the Raw Text property of the body (select your method, and click on the “Bodies” property in the property grid):

image

Once this is done, if you build your model, the stored procedure quoted hereunder will be generated, and in upper layers your method will look just as expected (returning a SettingCollection, mapping returned columns to properties, and taking an int maxCount parameter):

CREATE PROCEDURE [dbo].[Setting_BoundedLoadAll]
(
@maxCount [int]
)
AS
SET NOCOUNT ON
SELECT TOP(@maxCount) * from Setting
RETURN

Many-To-Many Relations in CFQL


CFQL actually provides built-in support for Many-To-Many relations, which often seems unnatural to developers. As a consequence, a common mistake is to write raw SQL to access a value contained in a Many-To-Many relation property, when in fact it can be done without any extra work.

For instance, in the following example a User can have several Roles, and one Role can be assigned to several Users. Nevertheless, if you want to load all users with a role name containing a specific token, instead of writing a raw SQL query, you could do it in a single line CFQL query, such as:

<User>
<Id/>
<Email typeName="Email" collectionKey="true"/>
<Password />
<Roles typeName="RoleCollection"/>
<cf:method name="LoadByRoleToken" body="load(string token) where Roles.Name contains @token" />
</User>

<Role>
<Id />
<Name collectionKey="true" />
<Users typeName="UserCollection" />
</Role>

As you can see in the example above, you can access entity properties using dots even on collection properties :)

Use Entities Rather Than Their Keys in CFQL


CFQL is made to use entities directly, you don’t have to use their keys explicitly (even though you can). The key point in CFQL is to be decoupled from the database’s physical definition, so keep it as simple as possible using entities for maximum flexibility. Here’s an example illustrating this point. One could be tempted to write such a CFQL query:

<cf:method name="DeleteByRoleId" body="delete(int RoleId) where Role.Id=@RoleId " />

Of course, this method is valid, however writing so implies that the identifier of the Role entity is of the integer type, so if you change its type, or change its key into a composite key, you’ll have to write this method over. However, using entities directly instead of their keys solves this issue:

<cf:method name="DeleteByRole" body="delete(Role) where Role=@Role" />

Both CFQL queries will actually produce the same SQL statement, however the second version is way more flexible.

Table-Valued Parameters

April 28, 2011 Leave a comment

Microsoft, starting with SQL Server 2008, added support for a new parameter type in SQL Server: Table-Valued parameters. Using them, you can send multiple rows of data to a T-SQL stored procedure, without creating a temporary table for instance.

Starting with the build 50204.579, the SQL Server producer now provides built-in support for those types. Thanks to this new feature you can now design CFQL queries such as:

<cf:method name="LoadCategories" body="load(int[] ints) where Id in (@ints)"/>

Which will generate the following code:

CREATE TYPE [dbo].[cf_type_Category_LoadCategories_0] AS TABLE(
[Item] [INT] NULL)
GO
CREATE PROCEDURE [dbo].[Category_LoadCategories]
(
@ints [dbo].[cf_type_Category_LoadCategories_0] READONLY,
@_orderBy0 [nvarchar] (64) = NULL,
@_orderByDirection0 [BIT] = 0
)
AS
SET NOCOUNT ON
DECLARE @_c_ints INT; SELECT @_c_ints= COUNT(*) FROM @ints
SELECT DISTINCT [Category].[Category_Id], [Category].[Category_Name], [Category].[_rowVersion]
FROM [Category]
WHERE [Category].[Category_Id] IN ((SELECT * FROM @ints))
RETURN
GO

The feature is actually broader than that, you can also:

  • In CFQL, use the ‘=’ operator on table-valued types, e.g. count(int[] ints) where @ints = Id,
  • Use .NET CLR types, e.g. raw(System.Uri[] uris),
  • Use already existing table-valued types in database,
  • Map table-valued types to .NET types such as DataTable, IDataReader and IEnumerable<SqlDataRecord>,
  • Map them to generated collection types you created in the model.

Want to know more about it? It’s all detailed in our product documentation, in the Array Parameters article.

Categories: SQL Server Tags: ,

CFQL Methods

February 9, 2011 Leave a comment

Using CFQL (CodeFluent Query Language) you can define platform independent persistent methods in your model which will then be translated into actual stored procedures by persistence producers such as SQL Server producer and the Oracle Database producer.

Moreover, upper layers producers will also generate the corresponding code mapped onto the generated stored procedure.

Example:

  • In the following model:

 

<Employee>
   <Id />
   <Name />
   <Address typeName="Address" />
</Employee>

<Address>
   <Id />
   <Line1 />
   <Line2 />
   <Zip />
   <City />
   <Country />
</Address>

 

  • Adding the following CFQL method to the Employee entity:
<cf:method name="LoadByCity" 
body="load(string cityName) where Address.City Like @cityName"  />
  • Will generate the following T-SQL stored procedure using the SQL Server producer:

 

CREATE PROCEDURE [dbo].[Employee_LoadByCity] (@cityName [nvarchar] (256), ...)
AS
SELECT DISTINCT [Employee].[Employee_Id], [Employee].[Employee_Name], [Employee].[Employee_Age], (...)
   FROM [Employee] LEFT OUTER JOIN [Address] ON ([Employee].[Employee_Address_Id] = [Address].[Address_Id])
       WHERE ([Address].[Address_City] LIKE @cityName)

 

Here’s how to do it in your application using the Modeler:

Follow

Get every new post delivered to your Inbox.

Join 49 other followers