The (documented) SQL Functions

file functions.hh

Functions

void applyBackReference(void *in_sourceSet_VARCHAR, void *targetSet_VARCHAR, void *in_propertiesTable_VARCHAR, void *in_entitiesTable_VARCHAR, void *in_subQuery_BOOLEAN, void *in_versioned_BOOLEAN)

Process a set of entities, keeping those which are referenced in a specific way.

This procedure filters a source table and writes the result into a target table. If the target table is not given, all non-matching entries are deleted from the source table instead.

Candidate entities are allowed to pass if there is an entity from the entities table which references the candidate as one of the properties in the properties table.

Parameters

sourceSet : table The name of the table which shall be filtered, must have column id.

targetSet : table The name of the result table, must have column id.

propertiesTable : table Use Property - ID references from this table.

entitiesTable : table References by Entities in this table are counted.

subQuery : boolean Create a temporary target table and select as list, instead of using the given targetSet. The versioned parameter has no effect in this case.

versioned : boolean If True, if a reference is versioned (references to specific versions of entities), the target candidate’s version must match. Therefore, the sourceSet and targetSet must have a _iversion column as well (unless sourceSet is the entities table).

void applyIDFilter(void *in_sourceSet_VARCHAR, void *in_targetSet_VARCHAR, void *in_o_CHAR, void *in_EntityID_VARCHAR, void *in_agg_CHAR, void *in_versioned_BOOLEAN)

Filter the sourceSet into targetSet by ID.

This can be done by operator-value tests or by aggregate functions.

The versioned flag currently only has the effect that an _iversion column is also copied to the target.

Parameters

sourceSet : VARCHAR(255) The name of the table from where we start. targetSet : VARCHAR(255) The name of the table where we collect all the “good” ids (or NULL or same as sourceSet). o : CHAR(2) The operator used for filtering, e.g. ‘=’, ‘!=’, ‘>’, … EntityID : VARCHAR(255) An entity id, existing or non-existing, which we use to compare the existing entities to using the operator. agg : CHAR(3) An aggregate function, e.g. ‘max’ or ‘min’. This only makes sense for number-based ids. versioned : BOOLEAN The filter belongs to a version-aware query (e.g. FIND ANY VERSION OF …) and hence the sourceSet and targetSet have an _iversion column.

void applyPOV(void *in_sourceSet_VARCHAR, void *_in_targetSet_VARCHAR, void *_in_propertiesTable_VARCHAR, void *_in_refIdsTable_VARCHAR, void *_in_o_CHAR, void *_in_vText_VARCHAR, void *_in_vInt_INT, void *_in_vDouble_DOUBLE, void *_in_unit_sig_BIGINT, void *_in_vDoubleStdUnit_DOUBLE, void *_in_stdUnit_sig_BIGINT, void *_in_vDateTime_VARCHAR, void *in_vDateTimeDotNotation_VARCHAR, void *in_agg_CHAR, void *_in_pname_VARCHAR, void *_in_versioned_BOOLEAN)

Apply a Property-Operator-Value filter to sourceSet.

The result is written to targetSet (non-matching rows are deleted in sourceSet if no targetSet is given).

Parameters

versioned : boolean If True, sourceSet and targetSet have an _iversion column, otherwise that column will be ignored (or only HEAD will be inserted into targetSet).

void createPOVSelectStatement(void *in_sourceSet_VARCHAR, void *in_targetSet_VARCHAR, void *in_propertiesTable_VARCHAR, void *in_refIdsTable_VARCHAR, void *in_o_CHAR, void *in_vText_VARCHAR, void *in_vInt_INT, void *in_vDouble_DOUBLE, void *in_unit_sig_BIGINT, void *in_vDoubleStdUnit_DOUBLE, void *in_stdUnit_sig_BIGINT, void *in_vDateTime_VARCHAR, void *in_vDateTimeDotNotation_VARCHAR, void *in_agg_CHAR, void *in_pname_VARCHAR, void *in_versioned_BOOLEAN, void *out_statementHasParameter_BOOLEAN, void *out_statement_VARCHAR)

Create the statement that collects potential property rows from the variaous data tables. This is a helper function for applyPOV.

see applyPOV for parameter descriptions

void applyTransactionFilter(void *in_sourceSet_VARCHAR, void *targetSet_VARCHAR, void *in_transaction_VARCHAR, void *in_operator_u_CHAR, void *in_realm_VARCHAR, void *in_userName_VARCHAR, void *in_ilb_BIGINT, void *in_ilb_nanos_INT_UNSIGNED, void *in_eub_BIGINT, void *in_eub_nanos_INT_UNSIGNED, void *in_operator_t_CHAR)

void calcDifference(void *in_resultSetTable_VARCHAR, void *in_diff_VARCHAR, void *in_versioned_BOOLEAN)

Delete rows from resultSetTable which have a matching ID (an version) in diff.

void calcIntersection(void *in_resultSetTable_VARCHAR, void *in_intersectWith_VARCHAR, void *in_versioned_BOOLEAN)

Drop rows in resultSetTable which do not have a matching ID (and version) in intersectWith.

void calcUnion(void *in_targetSet_VARCHAR, void *in_sourceSet_VARCHAR)

Add the rows from sourceSet to targetSet.

void cleanUpQuery(void*)

Drop temporary tables and warnings, existing warnings are returned in the result set.

void createIDIntersectionStmt(void *sourceSet_VARCHAR, void *targetSet_VARCHAR, void *data_VARCHAR, void *versioned_BOOLEAN)

Create and return an SQL statement string that encodes the following procedure: Reduce the set of IDs in the table sourceSet to the ones present in the table data and store the result in the table targetSet. The result is basically an intersection of the IDs in sourceSet and data.

Parameters

sourceSet : table The table with the entity IDs that shall be filtered. Filtering is done in place when targetSet is NULL.

targetSet : table The table into which the results shall be copied. If NULL, delete all non-matching rows from sourceSet. Must have the columns (id, _iversion) if versioned=TRUE, and only the (id) column otherwise. If this is NULL, create a statement which deletes from sourceSet the rows which have no match in data.

data : table Table whose IDs are a whitelist for which IDs shall be copied from sourceSet to targetSet (or kept in sourceSet if operation is in place). Must have the columns (property_id, entity_id).

versioned : boolean If True and if sourceSet is the entities table, the ID and version are copied directly from data to targetSet, which must not be NULL. Property filtering is done as usual.

void createTmpTable(void *out_newTableName_VARCHAR, void *in_versioned_BOOLEAN)

If not versioned: Creates a temporary table for query results with only a single id column.

If versioned: Creates a temporary table for query results with an id column and an _iversion column.

void deleteEntityProperties(void *in_EntityID_VARCHAR)

void deleteIsa(void *IN_InternalEntityID_INT_UNSIGNED)

void insert_single_child_version(void *in_InternalEntityID_INT_UNSIGNED, void *in_Hash_VARBINARY, void *in_Version_VARBINARY, void *in_Parent_VARBINARY, void *in_Transaction_VARBINARY)

Creates a new version record in the entity_version table.

Parameters

InternalEntityID : INT UNSIGNED The internal ID of the versioned entity. Hash : VARBINARY(255) A hash of the entity. This is currently not implemented properly and only there for future use. Version : VARBINARY(255) The new version ID of the entity, must be produced by the caller. Must be unique for each EntityID. Parent : VARBINARY(255) The version ID of the primary parent (i.e. predecessor). May be NULL; but if given, it must exist. Transaction : VARBINARY(255) The transaction ID which created this entity version (by inserting or updating an entity).

void get_iversion(void *InternalEntityID_INT_UNSIGNED, void *Version_VARBINARY)

Get the internal version id (an integer) of the (API-)version id.

Parameters

InternalEntityID : INT UNSIGNED The entity’s internal id. Version : VARBINARY(255) The (official, externally used) version id.

Returns

The internal version id.

void get_primary_parent_version(void *EntityID_VARCHAR, void *Version_VARBINARY)

Get the version ID of the primary parent (i.e. predecessor) of a version.

Parameters

EntityID : VARCHAR(255) The entity id. Version : VARBINARY(255) The version id.

Returns

The id of the given version’s primary parent version.

void get_version_timestamp(void *EntityID_VARCHAR, void *Version_VARBINARY)

Get the timestamp of a version by retrieving the timestamp of the transaction which created the version.

Parameters

EntityID : VARCHAR(255) The entity id. Version : VARBINARY(255) The version id.

Returns

The versions’ timestamp in <seconds-since-epoch>.<nanoseconds> format. Note that the dot . here is not necessarily a decimal separator.

void get_head_version(void *EntityID_VARCHAR)

Get the version id of the HEAD of an entity.

Parameters

EntityID : VARCHAR(255) The entity id.

Returns

The version id of the HEAD.

void _get_head_iversion(void *InternalEntityID_INT_UNSIGNED)

Get the _iversion number of the HEAD of an entity.

Parameters

InternalEntityID : INT UNSIGNED The entity’s internal id.

Returns

The _iversion of the HEAD.

void get_head_relative(void *EntityID_VARCHAR, void *HeadOffset_INT_UNSIGNED)

Get the id of a version which is specified relative to the HEAD of an entity.

Parameters

EntityID : VARCHAR(255) The entity id. HeadOffset : INT UNSIGNED Distance in the sequence of primary parents of the entity. E.g. 0 is the HEAD itself. 1 is the primary parent of the HEAD. 2 is the primary parent of the primary parent of the HEAD, and so on.

Returns

The version id of the HEAD.

void _get_version(void *InternalEntityID_INT_UNSIGNED, void *IVersion_INT_UNSIGNED)

The inverse function of get_iversion. Resolves the version id.

Parameters

InternalEntityID : INT UNSIGNED The entity’s internal id. IVersion Internal version id (integer).

Returns

The (external) version id.

void get_version_history(void *in_EntityID_VARCHAR)

Select the complete transaction history of an entity (Who created which version and when).

Parameters

EntityID : VARCHAR(255) The entity id.

Selects

Tuples (child, parent, child_seconds, child_nanos, child_username, child_realm). child and parent are version IDs.

void retrieveQueryTemplateDef(void *in_EntityID_VARCHAR, void *in_Version_VARBINARY)

Select a query string from the templates tables.

Parameters

EntityID : VARCHAR(255) The QueryTemplate’s id. Version : VARBINARY(255) The QueryTemplate’s version’s id.

Returns

Tuple (definition). A CQL query string (-fragment) which defines this QueryTemplate.

void getDependentEntities(void *in_EntityID_VARCHAR)

void getIdByName(void *in_Name_VARCHAR, void *in_Role_VARCHAR, void *in_Lmt_INT_UNSIGNED)

void initBackReference(void *in_PropertyID_VARCHAR, void *in_PropertyName_VARCHAR, void *in_EntityID_VARCHAR, void *in_EntityName_VARCHAR)

void initEntity(void *in_eid_VARCHAR, void *in_ename_VARCHAR, void *in_enameLike_VARCHAR, void *in_enameRegexp_VARCHAR, void *in_resultset_VARCHAR, void *in_versioned_BOOLEAN)

Insert the specified (by ename, enameLike, enameRegexp or eid) Entities into resultset.

If versioned is TRUE, also add archived entities (for example if the name was changed in the past).

void initPOVPropertiesTable(void *in_PropertyID_VARCHAR, void *in_PropertyName_VARCHAR, void *in_sourceSet_VARCHAR, void *in_includeReplacements_BOOLEAN)

void initPOVRefidsTable(void *in_PropertyID_VARCHAR, void *in_PropertyName_VARCHAR)

void initSubEntity(void *in_EntityID_VARCHAR, void *in_ename_VARCHAR, void *in_tableName_VARCHAR)

void insertEntity(void *in_EntityID_VARCHAR, void *in_EntityName_VARCHAR, void *in_EntityDesc_TEXT, void *in_EntityRole_VARCHAR, void *in_ACL_VARBINARY)

void insertEntityDataType(void *in_PropertyID_VARCHAR, void *in_DataTypeID_VARCHAR)

void insertEntityCollection(void *in_PropertyID_VARCHAR, void *in_Collection_VARCHAR)

void insertEntityProperty(void *in_DomainID_VARCHAR, void *in_EntityID_VARCHAR, void *in_PropertyID_VARCHAR, void *in_Datatable_VARCHAR, void *in_PropertyValue_TEXT, void *in_PropertyUnitSig_BIGINT, void *in_PropertyStatus_VARCHAR, void *in_DatatypeOverride_VARCHAR, void *in_Collection_VARCHAR, void *in_PropertyIndex_INT_UNSIGNED, void *in_RealPropertyID_VARCHAR)

void insertIsa(void *IN_ChildID_VARCHAR, void *IN_ParentID_VARCHAR)

Insert an “is a” relation

This procedure fills the isa_cache table. All passed entities must be existing in entities.id.

Parameters

ChildID : VARCHAR(255) The child entity.

ParentID : VARCHAR(255) The parent entity.

void isSubtype(void *in_ChildID_VARCHAR, void *in_ParentID_VARCHAR)

void overrideType(void *in_InternalDomainID_INT_UNSIGNED, void *in_InternalEntityID_INT_UNSIGNED, void *in_InternalPropertyID_INT_UNSIGNED, void *in_InternalDataTypeID_INT_UNSIGNED)

void registerReplacementIds(void *in_amount_INT_UNSIGNED)

void retrieveOverrides(void *in_DomainID_VARCHAR, void *in_EntityID_VARCHAR, void *in_Version_VARBINARY)

void retrieveEntityParents(void *in_EntityID_VARCHAR, void *in_Version_VARBINARY)

void updateEntity(void *in_EntityID_VARCHAR, void *in_EntityName_VARCHAR, void *in_EntityDescription_TEXT, void *in_EntityRole_VARCHAR, void *in_DatatypeID_VARCHAR, void *in_Collection_VARCHAR, void *in_ACL_VARBINARY)

dir _generated

page This autogenerated documentation from SQL.

Global functions