SaferOQL
Overview
This is a front end processor for OQL module that offers you safer and more powerful functions.
Although OQL is powerful and useful, it often causes run-time errors because the Mendix modeler cannot check consistency when changing domain-models.
This module solves that problem.
Key features:
- Check the consistency of all SaferOQL queries to domain-models by one-click.
- SaferOQL queries are loaded from text files under the resource folder. so you can compare query files with older versions on Subversion, making query configuration management easier.
- SaferOQL queries developed on the QueryTool-screen can be used for your App without any modification. More details:
- In SaferOQL queries, 'Conditional Block' controlled by parameter that disappear if you didn't specify the parameter at running time. This supports you to move OQL query to outside SaferOQL file.
- In SaferOQL queries, '2Way parameters' offers different values for on Query Tool screen and on actual running time. This supports you to develop and test SaferOQL easily.
- Respective SaferOQL queries should be stored under YourProject/resources/saferoql/ with the file name *.oql. You can see the included sample SaferOQL queries under Your Project/resources/saferoql/.
– XPath query tool was added from ver.2, and it allows you to test XPtah query and check SQLs executed by XPath queries to consider to change them into SaferOQL query.
- PostgreSQL PgStatActivity page was added from ver.2.2, and it allows you to monitor and force-stop wasteful SQL that runs for a long time on PostgreSQL and occupies resources
Since version 2.4.0, by using RetrieveByOQL and RetrieveBySaferOQL, you can retrieve objects which can be used as same as - such as in 'Change'/'Delete' activity- objects retrieved by reguler retrieve activity.
Documentation
Description
This is a front end processor for OQL module that offers you safer and more powerful functions.
Although OQL is powerful and useful, it often causes run-time errors because the Mendix modeler cannot check consistency when changing domain-models. This module solves that problem.
Typical usage scenario
If you are using OQL and are struggling to keep consistency when changing your domain-models, this module will help you.
Features and limitations
Key features:
- Check the consistency of all SaferOQL queries to domain-models by one-click.
- SaferOQL queries are loaded from text files under the resource folder. so you can compare query files with older versions on Subversion, making query configuration management easier.
- SaferOQL queries verified on the Query Tool screen can be used for runtime without modification.
More details:
- In SaferOQL queries, 'Conditional Block' controlled by parameter that disappear if you didn't specify the parameter at running time. This supports you to move OQL query to outside SaferOQL file.
- In SaferOQL queries, '2Way parameters' offers different values for on Query Tool screen and on actual running time. This supports you to develop and test SaferOQL easily.
- Respective SaferOQL queries should be stored under YourProject/resources/saferoql/ with the file name *.oql. You can see the included sample SaferOQL queries under Your Project/resources/saferoql/.
- SaferOQL query
- The sample SaferOQL query looks like this:
/*
* You can write comments! */
/*RETURNS(SaferOQL.SampleReturnEntity)*/
/*
* SaferOQL uses 'RETURNS' directive to know return entity type at consistency test.
* 'RETURNS' directive format is 'RETURNS(ModuleName.EntityName)'.
* If you didn't specify 'RETURNS' directive, SaferOQL doesn't test consistency with return entity
* but only tests oql syntax and domain-model consistency.
* Note: You can't use any space char within all directives.
*/
select
user/Name, user/LastLogin, user/Blocked, user/Active, lang/Code as LanguageCode
from
System.User user inner join user/System.User_Language/System.Language lang
where
true
/*WITH($Name)*/
and user/Name like /*$Name*/'Mx%'
/* ^^^^^^^^^^ This is 2Way parameter that works as $Name at running time. */
/* 2-way parameter can be used even where there is no 'WITH' and 'END' directive */
/*END($Name)*/
/*WITH($Active)*/
and user/Active = /*$Active*/true
/*END($Active)*/
/*WITH($LanguageCode)*/
and lang/Code like /*$LanguageCode*/'%_%'
/*END($LanguageCode)*/
/* SaferOQL uses 'WITH' and 'END' directive to delete Conditional Block.
* 'WITH' directive format is 'WITH($ParameterName)'.
* 'END' directive format is 'END($ParameterName)'.
* If no parameters are specified at running time, the Conditional Block will be deleted.
* It is not essential to use 2Way parameters in the Conditional Block, and it does not matter if the parameters that control the Conditional Block and the 2Way parameters that appear in the Conditional Block are different.
* Mendix's OQL engine internally removes a parameterized where clause if no parameter is specified at run time. However, you can control it more explicitly by using the Conditional Block.
*/
/*WITH($ContainUnionPart)*/
/*It's just an example, so don't worry about it being a strange union statement.*/
union all
select
user/Name, user/LastLogin, user/Blocked, user/Active, lang/Code as LanguageCode
from
System.User user inner join user/System.User_Language/System.Language lang
/*END($ContainUnionPart)*/
order by /*@OrderBy*/LanguageCode /*@AscDesc*/ASC
/* ^^^^^^^^^^ This is Embedded parameter that will be substituted by parameter 'OrderBy' and 'AscDesc' at running time in this case. */
/* Note: This feature removes symbols and parentheses during merging to prevent query injection attacks. */
/* This feature was added in version 1.2.0. */
- SNIP_SaferOQLQueryTool
You can run a test and check the result when developing SaferOQL.
The Query Tool simply removes the SaferOQL directives as comments and executes it.
If you specify SaferOQL as below:
select user/Name, user/LastLogin, user/Blocked, user/Active
from System.User user
where
true
/*WITH($Name)*/
and user/Name like /*$Name*/'Mx%'
/*END($Name)*/
The Query Tool convert it to as below and execute:
select user/Name, user/LastLogin, user/Blocked, user/Active
from System.User user
where
true
and user/Name like 'Mx%'
- SNIP_SaferOQLConsistencyTester
Run all SaferOQL consistency tests and view the results.
As same as the Query Tool, the consistency test is performed by simply removes the SaferOQL directives as comments and then actually executing it.
Those with errors are displayed at the top.
- SNIP_XPathQueryTool
It allows you to to test XPtah query and check SQLs executed by XPath query to consider to change them into SaferOQL query.
- SNIP_PgStatActivity
It allows you to monitor and force-stop wasteful SQL that runs for a long time on PostgreSQL server and occupies resources.
- Add[TypeName]Parameter Action
Corresponds to the Add[TypeName]Parameter of the original OQL module.
Specify the parameters for each Action of CountRowsSaferOQLStatement,
ExecuteSaferOQLStatement, ExportSaferOQLToCSV.
- AddParametersFromEntity Action
Specify the parameters at once from the entity.
The attribute name is mapped to the parameter name.
- AddListParameter Action
Specify the parameters by List for the ‘IN’ operator.
You can only use entity types as below.
- SaferOQL.LongValue
- SaferOQL.DecimalValue
- SaferOQL.DateTimeValue
- SaferOQL.BooleanValue
- SaferOQL.StringValue
Note:This action works on only Mendix 8.18.10 or above because of ticket #126647.
If you specify SaferOQL as below and use AddParameterList action to specify ‘Name’ parameter list that include 3 entries:
select user/Name, user/LastLogin, user/Blocked, user/Active
from System.User user
where
user/Name in ( /*$Name*/'MxAdmin' )
The ExecuteSaferOQLStatement action convert it to as below and execute:
select user/Name, user/LastLogin, user/Blocked, user/Active
from System.User user
where
user/Name in ( $Name_0, $Name_1, $Name_2 )
/* ^^^^^^^^^^^^^^^^^^^^^^^^^^^
It is automatically extended and OQL parameters are specified with same names. */
- CountRowsSaferOQLStatement Action
Corresponds to the CountRowsOQLStatement of the original OQL module.
Convert SaferOQL to OQL and call the CountRowsSaferOQLStatement action of the original OQL module.
- ExecuteSaferOQLStatement Action
Corresponds to the ExecuteOQLStatement of the original OQL module.
Convert SaferOQL to OQL and call the ExecuteOQLStatement action of the original OQL module.
By setting the log level of ‘SaferOQL’ to Debug, you can see the converted OQL.
If you specify SaferOQL as below and pass $Name parameter:
select user/Name, user/LastLogin, user/Blocked, user/Active
from System.User user
where
true
/*WITH($Name)*/
and user/Name like /*$Name*/'Mx%'
/*END($Name)*/
ExecuteSaferOQLStatement action convert it to as below and execute:
select user/Name, user/LastLogin, user/Blocked, user/Active
from System.User user
where
true
and user/Name like $Name
If you don’t pass $Name parameter, ‘and user/Name like $Name’ will be removed.
Please be aware that in Linux environments such as MxCloud, folder names and file names are case sensitive.
- ExportSaferOQLToCSV Action
Corresponds to the ExportOQLToCSV of the original OQL module.
Convert SaferOQL to OQL and call the ExportSaferOQLToCSV action of the original OQL module.
- RetrieveById Action
Retrieve an Entity by its Entity ID.
ID can be obtained by specifying CAST(entityname/ID as LONG) in the select clause of OQL.
Dependencies
- OQL module- CommunityCommons module
Installation
You can easily download and install the module from the App Store on Studio Pro.
Configuration
- Install this module.- Store your SaferOQL query file under YourProject/resources/saferoql/ with the file name *.oql.
Bugs.
There is no known bugs.