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

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.

- 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.