utilities - database

The database module provides functions for interacting with the database

Functions

Function	Parameters	Return Value	Description
db_select`(query)`	query: String containing a valid HQL select query	A list of rows containing lists of column values	Note that only HQL language is supported! Data will always be returned in the same format: PY `multiple_rows = [[column_a, column_b], [column_a, column_b]]single_row = [[column_a, column_b]]`
db_modify`(query)`	query: String containing a valid HQL modify query	Amount of rows modified	Note that only the HQL language is supported!
sanitize_value`(value)`	value: Object you want to put in a HQL query	Your object sanitized and embedded in quotation marks	Sanitize 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 id	Valid HQL attribute name	Several 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:

Customizing	Attribute Name
uuid	id
id	id_
class	class_
enum	enum_
const	const_

Examples

Selecting all objects where a date attribute is unset

PY

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)

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

PY

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

Reading a pojo attribute safely with convert_python_id_for_hql_query

PY

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')