/****************** To override in each EntitySqlDao template file *****************************/
tableName() ::= ""
/** Leave out id, account_record_id and tenant_record_id **/
tableFields(prefix) ::= ""
tableValues() ::= ""
historyTableName() ::= ""
historyTableFields(prefix) ::= <<
<targetRecordIdField(prefix)>
, <changeTypeField(prefix)>
, <tableFields(prefix)>
>>
historyTableValues() ::= <<
<targetRecordIdValue()>
, <changeTypeValue()>
, <tableValues()>
>>
/** Used for entities that can be soft deleted to make we exclude those entries in base calls getById(), get() **/
andCheckSoftDeletionWithComma(prefix) ::= ""
/** Add extra fields for SELECT queries **/
extraTableFieldsWithComma(prefix) ::= ""
defaultOrderBy(prefix) ::= <<
order by <recordIdField(prefix)> ASC
>>
/****************** To override in each EntitySqlDao template file <end> *****************************/
idField(prefix) ::= <<
<prefix>id
>>
idValue() ::= ":id"
recordIdField(prefix) ::= <<
<prefix>record_id
>>
recordIdValue() ::= ":recordId"
changeTypeField(prefix) ::= <<
<prefix>change_type
>>
changeTypeValue() ::= ":changeType"
targetRecordIdField(prefix) ::= <<
<prefix>target_record_id
>>
targetRecordIdValue() ::= ":targetRecordId"
/** Override this if the Entity isn't tied to an account **/
accountRecordIdField(prefix) ::= <<
<prefix>account_record_id
>>
accountRecordIdFieldWithComma(prefix) ::= <<
, <accountRecordIdField(prefix)>
>>
accountRecordIdValue() ::= ":accountRecordId"
accountRecordIdValueWithComma() ::= <<
, <accountRecordIdValue()>
>>
tenantRecordIdField(prefix) ::= <<
<prefix>tenant_record_id
>>
tenantRecordIdFieldWithComma(prefix) ::= <<
, <tenantRecordIdField(prefix)>
>>
tenantRecordIdValue() ::= ":tenantRecordId"
tenantRecordIdValueWithComma() ::= <<
, <tenantRecordIdValue()>
>>
allTableFields(prefix) ::= <<
<recordIdField(prefix)>
, <idField(prefix)>
, <tableFields(prefix)>
<extraTableFieldsWithComma(prefix)>
<accountRecordIdFieldWithComma(prefix)>
<tenantRecordIdFieldWithComma(prefix)>
>>
allTableValues() ::= <<
<recordIdValue()>
, <idValue()>
, <tableValues()>
<accountRecordIdValueWithComma()>
<tenantRecordIdValueWithComma()>
>>
allHistoryTableFields(prefix) ::= <<
<recordIdField(prefix)>
, <idField(prefix)>
, <targetRecordIdField(prefix)>
, <historyTableFields(prefix)>
<accountRecordIdFieldWithComma(prefix)>
<tenantRecordIdFieldWithComma(prefix)>
>>
allHistoryTableValues() ::= <<
<recordIdValue()>
, <idValue()>
, <targetRecordIdValue()>
, <historyTableValues()>
<accountRecordIdValueWithComma()>
<tenantRecordIdValueWithComma()>
>>
/** Macros used for multi-tenancy (almost any query should use them!) */
CHECK_TENANT(prefix) ::= "<prefix>tenant_record_id = :tenantRecordId"
AND_CHECK_TENANT(prefix) ::= "and <CHECK_TENANT(prefix)>"
getAll() ::= <<
select
<allTableFields("t.")>
from <tableName()> t
where <CHECK_TENANT("t.")>
<andCheckSoftDeletionWithComma("t.")>
<defaultOrderBy("t.")>
;
>>
get(offset, rowCount, orderBy, ordering) ::= <<
select
<allTableFields("t.")>
from <tableName()> t
join (
select <recordIdField("")>
from <tableName()>
where <CHECK_TENANT("")>
<andCheckSoftDeletionWithComma("")>
order by <orderBy> <ordering>
limit :rowCount offset :offset
) optimization on <recordIdField("optimization.")> = <recordIdField("t.")>
order by t.<orderBy> <ordering>
;
>>
getCount() ::= <<
select
count(1) as count
from <tableName()> t
where <CHECK_TENANT("t.")>
<andCheckSoftDeletionWithComma("t.")>
;
>>
getById(id) ::= <<
select
<allTableFields("t.")>
from <tableName()> t
where <idField("t.")> = :id
<andCheckSoftDeletionWithComma("t.")>
<AND_CHECK_TENANT("t.")>
;
>>
getByRecordId(recordId) ::= <<
select
<allTableFields("t.")>
from <tableName()> t
where <recordIdField("t.")> = :recordId
<andCheckSoftDeletionWithComma("t.")>
<AND_CHECK_TENANT("t.")>
;
>>
/** Note: account_record_id can be NULL **/
getByAccountRecordId(accountRecordId) ::= <<
select
<allTableFields("t.")>
from <tableName()> t
where (<accountRecordIdField("t.")> = :accountRecordId or (<accountRecordIdField("t.")> is null and :accountRecordId is null))
<andCheckSoftDeletionWithComma("t.")>
<AND_CHECK_TENANT("t.")>
<defaultOrderBy("t.")>
;
>>
getByAccountRecordIdIncludedDeleted(accountRecordId) ::= <<
select
<allTableFields("t.")>
from <tableName()> t
where (<accountRecordIdField("t.")> = :accountRecordId or (<accountRecordIdField("t.")> is null and :accountRecordId is null))
<AND_CHECK_TENANT("t.")>
<defaultOrderBy("t.")>
;
>>
getHistoryTargetRecordId(recordId) ::= <<
select
<targetRecordIdField("t.")>
from <historyTableName()> t
where <recordIdField("t.")> = :recordId
<andCheckSoftDeletionWithComma("t.")>
<AND_CHECK_TENANT("t.")>
;
>>
getRecordId(id) ::= <<
select
<recordIdField("t.")>
from <tableName()> t
where <idField("t.")> = :id
<andCheckSoftDeletionWithComma("t.")>
<AND_CHECK_TENANT("t.")>
;
>>
getRecordIdForTable(tableName) ::= <<
select
<recordIdField("t.")>
from <tableName> t
where <idField("t.")> = :id
<andCheckSoftDeletionWithComma("t.")>
<AND_CHECK_TENANT("t.")>
;
>>
getHistoryRecordId(targetRecordId) ::= <<
select
max(<recordIdField("t.")>)
from <historyTableName()> t
where <targetRecordIdField("t.")> = :targetRecordId
<andCheckSoftDeletionWithComma("t.")>
<AND_CHECK_TENANT("t.")>
;
>>
getHistoryRecordIdsForTable(historyTableName) ::= <<
select
<recordIdField("t.")>
from <historyTableName> t
where <targetRecordIdField("t.")> = :targetRecordId
<andCheckSoftDeletionWithComma("t.")>
<AND_CHECK_TENANT("t.")>
;
>>
searchQuery(prefix) ::= <<
1 = 1
>>
search(ordering) ::= <<
select
<allTableFields("t.")>
from <tableName()> t
where (<searchQuery("t.")>)
<andCheckSoftDeletionWithComma("t.")>
<AND_CHECK_TENANT("t.")>
order by <recordIdField("t.")> <ordering>
limit :rowCount offset :offset
;
>>
getSearchCount() ::= <<
select
count(1) as count
from <tableName()> t
where (<searchQuery("t.")>)
<andCheckSoftDeletionWithComma("t.")>
<AND_CHECK_TENANT("t.")>
;
>>
create() ::= <<
insert into <tableName()> (
<idField("")>
, <tableFields("")>
<accountRecordIdFieldWithComma("")>
<tenantRecordIdFieldWithComma("")>
)
values (
<idValue()>
, <tableValues()>
<accountRecordIdValueWithComma()>
<tenantRecordIdValueWithComma()>
)
>>
/** Audits, History **/
auditTableName() ::= "audit_log"
auditTableFields(prefix) ::= <<
<prefix>id
, <prefix>table_name
, <prefix>target_record_id
, <prefix>change_type
, <prefix>created_by
, <prefix>reason_code
, <prefix>comments
, <prefix>user_token
, <prefix>created_date
<if(accountRecordIdField(prefix))>, <accountRecordIdField(prefix)><endif>
<if(tenantRecordIdField(prefix))>, <tenantRecordIdField(prefix)><endif>
>>
auditTableValues() ::= <<
:id
, :tableName
, :targetRecordId
, :changeType
, :createdBy
, :reasonCode
, :comments
, :userToken
, :createdDate
<if(accountRecordIdField(""))>, <accountRecordIdValue()><endif>
<if(tenantRecordIdField(""))>, <tenantRecordIdValue()><endif>
>>
getHistoryForTargetRecordId() ::= <<
select
<idField("")>
, <historyTableFields("t.")>
<accountRecordIdFieldWithComma("t.")>
<tenantRecordIdFieldWithComma("t.")>
from <historyTableName()> t
where <targetRecordIdField("t.")> = :targetRecordId
<AND_CHECK_TENANT("t.")>
order by <recordIdField("t.")> ASC
;
>>
addHistoryFromTransaction() ::= <<
insert into <historyTableName()> (
<idField("")>
, <historyTableFields("")>
<accountRecordIdFieldWithComma("")>
<tenantRecordIdFieldWithComma("")>
)
values (
<idValue()>
, <historyTableValues()>
<accountRecordIdValueWithComma()>
<tenantRecordIdValueWithComma()>
)
;
>>
insertAuditFromTransaction() ::= <<
insert into <auditTableName()> (
<auditTableFields("")>
)
values (
<auditTableValues()>
)
;
>>
getAuditLogsForAccountRecordId() ::= <<
select
<auditTableFields("t.")>
from <auditTableName()> t
where <accountRecordIdField("t.")> = :accountRecordId
<andCheckSoftDeletionWithComma("t.")>
<AND_CHECK_TENANT("t.")>
order by t.table_name, <recordIdField("t.")> ASC
;
>>
getAuditLogsForTableNameAndAccountRecordId() ::= <<
select
<auditTableFields("t.")>
from <auditTableName()> t
where <accountRecordIdField("t.")> = :accountRecordId
and t.table_name = :tableName
<andCheckSoftDeletionWithComma("t.")>
<AND_CHECK_TENANT("t.")>
<defaultOrderBy("t.")>
;
>>
getAuditLogsForTargetRecordId() ::= <<
select
<auditTableFields("t.")>
from <auditTableName()> t
where t.target_record_id = :targetRecordId
and t.table_name = :tableName
<andCheckSoftDeletionWithComma("t.")>
<AND_CHECK_TENANT("t.")>
<defaultOrderBy("t.")>
;
>>
getAuditLogsViaHistoryForTargetRecordId(historyTableName) ::= <<
select
<auditTableFields("t.")>
from <auditTableName()> t
join (
select
<recordIdField("h.")> record_id
from <historyTableName> h
where <targetRecordIdField("h.")> = :targetRecordId
<andCheckSoftDeletionWithComma("t.")>
<AND_CHECK_TENANT("h.")>
) history_record_ids on t.target_record_id = history_record_ids.record_id
where t.table_name = :tableName
<AND_CHECK_TENANT("t.")>
<defaultOrderBy("t.")>
;
>>
test() ::= <<
select
<allTableFields("t.")>
from <tableName()> t
where <CHECK_TENANT("t.")>
<andCheckSoftDeletionWithComma("t.")>
limit 1
;
>>