Managing logical operators in search conditions

🌐 This document is available in both English and Ukrainian. Use the language toggle in the top right corner to switch between versions.

1. General description

This article covers implementation of a possibility for a modeler to manage logical operators, OR or AND, for combining the search parameters, and the order of their defining.

2. Actors and user roles

  • Registry developer

3. General provisions

  • The behavior and the contract of existing search criteria do not change.

  • Reverse compatibility of search criteria configuration is maintained.

4. Functional scenarios

  • Setting up search criteria

  • Generation of search criteria services

5. Current implementation

In the search criteria, there is a possibility to set the fields for searching using the searchType attribute of the ext:column tag.

For example, for a search criterion defined as follows:

 <changeSet author="registry owner" id="create SC">
    <ext:createSearchCondition name="dictionary">
        <ext:table name="dict" alias="d">
            <ext:column name="name" searchType="startsWith" />
            <ext:column name="live" searchType="equal" />
            <ext:column name="id" searchType="equal" />
            <ext:column name="description"/>
        </ext:table>
    </ext:createSearchCondition>
</changeSet>

when calling, provided that all parameters have been passed, the following search string is generated that combines all the parameters using the `AND' operator.

name like '$1%' AND live = $2 AND id = $3

According to the API search criteria contract, the search parameters are optional. Therefore, if a parameter is not passed, it is not included into the search string. For example, if the id parameter is not passed, then the search string is generated without it.

name like '$1%' AND live = $2

In any case, all parameters are combined by the `AND' operator, and the regulation modeler cannot change this.

6. Target design

6.1. Schema and Liquibase tag extension module

In the scheme of extended tags, the ext:logicOperator element of new tableLogicOperatorType type is added to the allowed child elements of the ext:table tag with the tableSearchConditionType type (the one used for tables, not the tag having the same name used in the CTE and having a different type). It has one mandatory type attribute, the type of or or and logical operator , and can contain ext:column and ext:logicOperator elements, i.e. itself.

Changes to the Liquibase extended tag scheme
<xsd:complexType name="tableLogicOperatorType">
    <xsd:sequence>
        <xsd:choice minOccurs="1" maxOccurs="unbounded">
            <xsd:element name="column" type="columnSearchConditionType" maxOccurs="unbounded"/>
            <xsd:element name="logicOperator" type="tableLogicOperatorType" maxOccurs="unbounded"/>
        </xsd:choice>
    </xsd:sequence>
    <xsd:attribute name="type" type="logicOperatorType" use="required"/>
</xsd:complexType>
....
<xsd:complexType name="tableSearchConditionType">
    <xsd:sequence>
        <xsd:choice minOccurs="1" maxOccurs="unbounded">
            <xsd:element name="column" type="columnSearchConditionType" maxOccurs="unbounded"/>
            <xsd:element name="function" type="functionType" maxOccurs="unbounded"/>
            <xsd:element name="logicOperator" type="tableLogicOperatorType" maxOccurs="unbounded"/>
        </xsd:choice>
    </xsd:sequence>
    <xsd:attribute name="name" type="xsd:string" use="required"/>
    <xsd:attribute name="alias" type="xsd:string" use="optional"/>
</xsd:complexType>

The possibility to write all the information about ext:logicOperator tags required for service generation in the ddm_liquibase_metadata metadata table when processing the createSearchCondition tag is added to the Liquibase tag extension module.

Placing the ext:column tags without the searchType attribute inside the ext:logicOperator tag is prohibited.

6.2. Service generator

The ext:logicOperator tag controls which column conditions are bracketed and which operator is used to combine these bracketed conditions. That is, opening a tag is equivalent to the opening bracket, and closing a tag is equivalent to the closing bracket in the resulting request to the database, and the type attribute indicates which logical operator to use.

When implementing the algorithm, the following rules shall be kept in mind:

  • First-level ext:table search conditions are combined using the and operator, just like before the changes.

  • Search conditions between different tables are combined using the and operator.

  • If no conditions are passed for any of the columns inside the ext:logicOperator element during a call, then this element is not processed.

For example, when calling for the following search criterion

<changeSet author="registry owner" id="create SC registration_equal_laboratory_id_solution">
    <ext:createSearchCondition name="registration_equal_laboratory_id_solution">
        <ext:table name="registration" alias="r">
            <ext:column name="registration_id" />
            <ext:column name="registration_no" searchType="equal"/>
            <ext:column name="created_date" />
            <ext:logicOperator type="or">
                <ext:column name="solution_date" searchType="equal" />
                <ext:column name="laboratory_id" searchType="equal" />
                <ext:logicOperator type="and">
                    <ext:column name="name" searchType="equal" />
                    <ext:column name="surname" searchType="equal" />
                </ext:logicOperator>
            </ext:logicOperator>
        </ext:table>
        <ext:table name="solution_type" alias="s">
            <ext:logicOperator type="or">
                <ext:column name="name" alias="solution_name" searchType="equal" />
                <ext:column name="constant_code" alias="solution_code" searchType="equal" />
            </ext:logicOperator>
        </ext:table>
        <ext:join type="inner">
            <ext:left alias="r">
                <ext:column name="solution_type_id" />
            </ext:left>
            <ext:right alias="s">
                <ext:column name="solution_type_id" />
            </ext:right>
        </ext:join>
    </ext:createSearchCondition>
</changeSet>

the following search string should be generated, provided that all parameters are passed:

                        -- <ext:table name="registration" alias="r">
                        --     <ext:column name="registration_id" />
                        --     <ext:column name="created_date" />
registration_no=$0      --     <ext:column name="registration_no" searchType="equal"/>
AND
(                       --     <ext:logicOperator type="or">
    solution_date=$1    --         <ext:column name="solution_date" searchType="equal" />
    OR
    laboratory_id=$2    --         <ext:column name="laboratory_id" searchType="equal" />
    OR
    (                   --         <ext:logicOperator type="and">
        firstname=$3    --             <ext:column name="firstname" searchType="equal" />
        AND
        surname=$4      --             <ext:column name="surname" searchType="equal" />
    )                   --         </ext:logicOperator>
)                       --     </ext:logicOperator>
                        -- </ext:table>
AND
                        -- <ext:table name="solution_type" alias="s">
(                       --     <ext:logicOperator type="or">
    name=$5             --         <ext:column name="name" alias="solution_name" searchType="equal" />
    OR
    constant_code=$6    --         <ext:column name="constant_code" alias="solution_code" searchType="equal" />
)                       --     </ext:logicOperator>
                        -- </ext:table>

6.3. System components and their designation in the solution design

This section gives a list of system components that are engaged or need to be changed/created in the framework of the implementation of functional requirements according to the technical design of the solution.

Component

Service name

Designation / Changes

Service Generator

service-generation-utility

Generation of Java projects for services

Liquibase extended tagging scheme

liquibase-ext-schema

Scheme validation

Liquibase tag extension module

liquibase-ddm-ext

Processing of extended tags at the regulations deployment stage

7. Registry regulations modeling

7.1. Modeling search conditions

The regulations administrator gets a possibility to control which logical operator, OR or AND, to use for combining the search parameters and in which order they are determined.

Registry regulations structure
Figure 1. Registry regulations structure
Configuration example
 <changeSet author="registry owner" id="create or/and SC">
    <ext:createSearchCondition name="dictionary">
        <ext:table name="dict" alias="d">
            <ext:logicOperator type="or">
                <ext:logicOperator type="and">
                    <ext:column name="name" searchType="startsWith" />
                    <ext:column name="live" searchType="equal" />
                </ext:logicOperator>
                <ext:column name="id" searchType="equal" />
            </ext:logicOperator>
            <ext:column name="description"/>
        </ext:table>
    </ext:createSearchCondition>
</changeSet>

7.2. Validation of registry regulations

As part of the implementation of the solution, the xml scheme of extended liquibase tags used for validation will be extended.

8. High-level development plan

8.1. Technical assessments

  • BE

8.2. Development plan

  • Extension of the Liquibase extended tags scheme.

  • Liquibase tag extension module extension.

  • Extension of service generator.

  • Development of instructions for the regulations developer and reference examples.