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 the 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]]
|
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 a input for usage 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 a 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:
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)
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)
# ...
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')
. 
