The database module provides functions for interacting with the database

Functions

FunctionParametersReturn ValueDescription
db_select(query)query: String containing a valid HQL select queryA list of rows containing lists of column values

Note that only HQL language is supported!

Data will always be returned in the same format:

multiple_rows = [[column_a, column_b], [column_a, column_b]] single_row = [[column_a, column_b]]
PY
db_modify(query)query: String containing a valid HQL modify queryAmount of rows modifiedNote that only the HQL language is supported!
sanitize_value(value)value: Object you want to put in a HQL queryYour object sanitized and embedded in quotation marksSanitize an input for use in a HQL query. This should always be used when formatting data into a query! The following objects are put into the correct format for an HQL query: uuid.UUID, java.util.UUID, java.util.Date
convert_python_id_for_hql_query(python_id)python_id: String containing a python idValid HQL attribute nameSeveral words are reserved in Java and overlap with python ids that were used in customizing. To refer to them in a HQL query or when interacting with a pojo object you must specify the correct attribute name. This function returns the valid attribute name for a given python id.

Reserved words changed by convert_python_id_for_hql_query:

CustomizingAttribute Name
uuidid
idid_
classclass_
enumenum_
constconst_

Examples

Selecting all objects where a date attribute is unset

from customizing import utilities
# Select all resource assignments that don't have an actual end set
query = """SELECT ras.id 
           FROM ResourceAssignment ras
           WHERE ras.actual_end != '01.01.1970'"""

utilities.db_select(query)
PY

Using sanitize value to use a java.util.Date object in a query

from customizing import utilities

# Data event that is run when attendance is modified
def write_attendance_to_period(event, entity):
    # Read data from pojo
    resource = entity.resource  # String
    date = entity.date  # java.util.Date

    query = """select
                 a.duration,
                 a.type
               from
                 Attendance a
               where
                 a.resource = {resource}
               and
                 a.date = {date}""".format(resource=utilities.sanitize_value(resource), 
                                           date=utilities.sanitize_value(date))

    result = utilities.db_select(query)
    # ...
PY

Reading a pojo attribute safely with convert_python_id_for_hql_query

from customizing import utilities

def read_pojo_attribute(pojo, python_id):
    attribute_name = utilities.convert_python_id_for_hql_query(python_id)
    return getattr(pojo, attribute_name)

pojo = utilities.create_entity('Project')
# Querying pojo.uuid raises an exception, the correct attribute is pojo.id!
uuid = read_pojo_attribute(pojo, python_id='uuid')
PY