1. Brief overview
-
Document generation module based on templates, which
-
can handle templates in different formats and inject parameter lists into them, then generate the finished document.
-
The module is designed to generate output documents (PDF, XLSX, HTML, TXT, …) from various template formats with identifiers (TXT, HTML, object, JRXML, …) by inserting the received parameter(s) into the received template.
-
The solution is based on a microservice architecture.
-
-
Technologies:
-
Components and their versions:
-
Included local Postgres: 14.8-bullseye
-
Included local Oracle: 21.3.0-xe
-
Liquibase: 4.21
-
The latest final PGTools version: 0.10.0
-
-
2. Schemas
2.1. dookug
2.1.1. ERD
2.1.2. Tables
| Field | Type | Default Value | Required? | Description |
|---|---|---|---|---|
x__id |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
Yes |
PK → unique identifier |
|
template_id |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
No |
TEMPLATE identifier |
|
status |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
Yes |
Document status |
|
format |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
Yes |
Document format |
|
filename |
Oracle: VARCHAR2(100 CHAR) Postgres: VARCHAR(100) |
No |
Document filename |
|
error_message |
Oracle: VARCHAR2(512 CHAR) Postgres: VARCHAR(512) |
No |
Error message during process |
|
storage_type |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
Yes |
Storage type |
|
storage_id |
Oracle: VARCHAR2(128 CHAR) Postgres: VARCHAR(128) |
No |
Unique invoice identifier in the calling system |
|
parameters |
Oracle: BLOB Postgres: BYTEA |
No |
Parameter key-value pairs json |
|
parameter_data |
Oracle: BLOB Postgres: BYTEA |
No |
Parameter data structure json |
|
config |
Oracle: CLOB Postgres: TEXT |
No |
Configuration |
|
x__insdate |
TIMESTAMP(6) |
Oracle: sysdate Postgres: now() |
Yes |
Insertion timestamp |
x__insuser |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
'0' |
Yes |
Not used, default value 0 |
x__moddate |
TIMESTAMP(6) |
No |
Modification timestamp, null at insertion |
|
x__moduser |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
'0' |
No |
Not used, default value 0 |
x__version |
Oracle: NUMBER Postgres: INT |
0 |
Yes |
Change versioning |
| Field | Type | Value | Related Field | Referenced Field |
|---|---|---|---|---|
pk_document |
primary key |
x__id |
||
pk_document |
unique index |
x__id |
| Field | Type | Default Value | Required? | Description |
|---|---|---|---|---|
x__id |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
Yes |
Generated primary key (PK) |
|
document_id |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
Yes |
DOCUMENT unique identifier |
|
content |
Oracle: BLOB Postgres: BYTEA |
Yes |
Document content (gzip) |
|
expiry |
Oracle: TIMESTAMP(6) Postgres: TIMESTAMP(6) |
No |
Expiration date |
|
x__insdate |
timestamp(6) |
Oracle: sysdate Postgres: now() |
Yes |
Insertion timestamp |
x__insuser |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
'0' |
Yes |
Not used, default value 0 |
x__moddate |
timestamp(6) |
No |
Modification timestamp, null at insertion |
|
x__moduser |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
'0' |
No |
Not used, default value 0 |
x__version |
Oracle: number Postgres: int |
0 |
Yes |
Change versioning |
| Field | Type | Value | Related Field | Referenced Field |
|---|---|---|---|---|
pk_document_content |
primary key |
x__id |
||
fk_document_content_document |
foreign key constraint |
document_id |
document.x__id |
|
ix_document_content_document_id |
index |
document_id |
||
pk_document_content |
unique index |
x__id |
| Field | Type | Default Value | Required? | Description |
|---|---|---|---|---|
x__id |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
Yes |
Generated primary key (PK) |
|
name |
Oracle: VARCHAR2(255 CHAR) Postgres: VARCHAR(255) |
Yes |
Template name |
|
description |
Oracle: VARCHAR2(1024 CHAR) Postgres: VARCHAR(1024) |
Yes |
Description |
|
template_engine |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
Yes |
Template engine. Possible values: HANDLEBARS, NONE |
|
generator_engine |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
Yes |
Document generation engine. Possible values: PDF_BOX, NONE |
|
language |
Oracle: VARCHAR2(30 CHAR) Postgres: varchar(30) |
No |
Template language |
|
validity_start |
Oracle: TIMESTAMP(6) Postgres: TIMESTAMP(6) |
Oracle: sysdate Postgres: now() |
Yes |
Validity start |
validity_end |
Oracle: TIMESTAMP(6) Postgres: TIMESTAMP(6) |
No |
Validity end |
|
x__insdate |
timestamp(6) |
Oracle: sysdate Postgres: now() |
Yes |
Insertion timestamp |
x__insuser |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
'0' |
Yes |
Not used, default value 0 |
x__moddate |
timestamp(6) |
No |
Modification timestamp, null at insertion |
|
x__moduser |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
'0' |
No |
Not used, default value 0 |
x__version |
Oracle: number Postgres: int |
0 |
Yes |
Change versioning |
| Field | Type | Value | Related Field | Referenced Field |
|---|---|---|---|---|
ck_template_generator_engine |
check constraint |
PDF_BOX, NONE, SAXON |
generator_engine |
|
ck_template_template_engine |
check constraint |
HANDLEBARS, NONE |
template_engine |
|
pk_template |
primary key |
x__id |
||
pk_template |
unique index |
x__id |
| Field | Type | Default Value | Required? | Description |
|---|---|---|---|---|
x__id |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
Yes |
Generated primary key (PK) |
|
key |
Oracle: VARCHAR2(255 CHAR) Postgres: VARCHAR(255) |
Yes |
Template key. For template engine. |
|
description |
Oracle: VARCHAR2(1024 CHAR) Postgres: VARCHAR(1024) |
Yes |
Description |
|
template_engine |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
Yes |
Template engine. Possible values: HANDLEBARS, NONE |
|
generator_engine |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
Yes |
Document generation engine. Possible values: PDF_BOX, NONE |
|
template_part_type |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
Yes |
Template type. Possible values: HEADER, CONTENT, FOOTER, MAIN, OTHER |
|
x__insdate |
timestamp(6) |
Oracle: sysdate Postgres: now() |
Yes |
Insertion timestamp |
x__insuser |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
'0' |
Yes |
Not used, default value 0 |
x__moddate |
timestamp(6) |
No |
Modification timestamp, null at insertion |
|
x__moduser |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
'0' |
No |
Not used, default value 0 |
x__version |
Oracle: number Postgres: int |
0 |
Yes |
Change versioning |
| Field | Type | Value | Related Field | Referenced Field |
|---|---|---|---|---|
ck_template_part_generator_engine |
check constraint |
PDF_BOX, NONE |
generator_engine |
|
ck_template_part_template_engine |
check constraint |
HANDLEBARS, NONE |
template_engine |
|
ck_template_part_template_part_type |
check constraint |
HEADER, CONTENT, FOOTER, MAIN, OTHER |
template_part_type |
|
pk_template_part |
primary key |
x__id |
||
pk_template_part |
unique index |
x__id |
| Field | Type | Default Value | Required? | Description |
|---|---|---|---|---|
x__id |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
Yes |
Generated primary key (PK) |
|
template_part_id |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
Yes |
TEMPLATE unique identifier |
|
content |
Oracle: BLOB Postgres: BYTEA |
Yes |
Template content |
|
compressed |
Oracle: NUMBER Postgres: INT |
Yes |
Is template content compressed |
|
x__insdate |
timestamp(6) |
Oracle: sysdate Postgres: now() |
Yes |
Insertion timestamp |
x__insuser |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
'0' |
Yes |
Not used, default value 0 |
x__moddate |
timestamp(6) |
No |
Modification timestamp, null at insertion |
|
x__moduser |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
'0' |
No |
Not used, default value 0 |
x__version |
Oracle: number Postgres: int |
0 |
Yes |
Change versioning |
| Field | Type | Value | Related Field | Referenced Field |
|---|---|---|---|---|
pk_template_part_content |
primary key |
x__id |
||
uk_template_part_content_template_part_id |
unique index |
template_part_id |
||
fk_template_part_content_template_part |
Foreign key constraint |
template_part_id |
template_part.x__id |
|
pk_template_part_content |
unique index |
x__id |
| Field | Type | Default Value | Required? | Description |
|---|---|---|---|---|
x__id |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
Yes |
Generated primary key (PK) |
|
template_id |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
Yes |
TEMPLATE unique identifier |
|
template_part_id |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
Yes |
TEMPLATE_PART unique identifier |
|
x__insdate |
timestamp(6) |
Oracle: sysdate Postgres: now() |
Yes |
Insertion timestamp |
x__insuser |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
'0' |
Yes |
Not used, default value 0 |
x__moddate |
timestamp(6) |
No |
Modification timestamp, null at insertion |
|
x__moduser |
Oracle: VARCHAR2(30 CHAR) Postgres: VARCHAR(30) |
'0' |
No |
Not used, default value 0 |
x__version |
Oracle: number Postgres: int |
0 |
Yes |
Change versioning |
Field |
Type |
Value |
Related Field |
Referenced Field |
pk_template_template_part |
primary key |
x__id |
||
fk_template_template_part_template |
foreign key constraint |
template_id |
template.x__id |
|
fk_template_template_part_template_part |
foreign key constraint |
template_part_id |
template_part.x__id |
|
ix_template_template_part_template_id |
index |
template_id |
||
ix_template_template_part_template_part_id |
index |
template_part_id |
||
pk_template_template_part |
unique index |
x__id |
2.1.3. Permissions
| Users | Permissions |
|---|---|
dookug |
SELECT, INSERT, UPDATE, DELETE |
dookug_write |
INSERT, UPDATE, DELETE |
dookug_read |
SELECT |
dookug_exec |
dookug_read, dookug_write |
dookug_service |
dookug_exec |
| Users | Permissions |
|---|---|
DOOKUG_WRITE |
INSERT, UPDATE, DELETE |
DOOKUG_READ |
SELECT |
DOOKUG_EXEC |
DOOKUG_READ, DOOKUG_WRITE |
3. Configurations
-
ENV variables:
-
DOCKER_REPOSITORY: Root location of images (default value: DOCKER_REPOSITORY_).
-
DOCKER_LIQUIBASE_DOOKUG: Indicates the image location (schema1 default value: ${DOCKER_REPOSITORY}/liquibase/modules/dookug_db).
-
DBDWH_IMAGE_VERSION: used version of liquibase, postgres, partman images (default value: 0.10.0)
-
VERSION: Project version, this is set automatically.
-
-
Compose variables:
-
LIQUIBASE_BASE_IMAGE: central, corporate base liquibase image with version, used by dockerfile (default value: DOCKER_REPOSITORY/db-base-liquibase:${DBDWH_IMAGE_VERSION}).
-
liquibase-release/LIQUIBASE_INSTALL_DIR: local liquibase directory, used by dockerfile (schema1 default value: ./liquibase/dookug).
-
LIQUIBASE_INSTALL_COMMON_DIR: local liquibase/common directory, used by dockerfile (default value: ./liquibase/common).
-
PG_TOOLS_IMAGE: Postgres partition manager installer path, used by dockerfile (default value: icellmobilsoft/db-base-pg_tools:${DBDWH_IMAGE_VERSION}').
-
-
Before Liquibase variables:
-
S2_SCHEMA_NAME: Step 2 schema name, with default value, can be overridden externally (default value: dookug).
-
INSTALL_PGTOOLS: PG partition manager installer switch in Step 2 (automatically copied in Dockerfile and installed in STEP2), with default value, can be overridden externally (default value: true).
-
INSTALL_USERNAME_ADMIN: The Admin user for the main installs, with default value, can be overridden externally (default value: postgres - system based on DB type).
-
INSTALL_USERNAME_PROJECT: Only for Postgres! The Project user for the project schema installs, with default value, can be overridden externally (default value: S2_SCHEMA_NAME).
-
INSTALL_PASSWORD_ADMIN: The password of Admin user for the main installs, with default value, can be overridden externally (default value: postgres).
-
INSTALL_PASSWORD_PROJECT: The password of Project user for the project schema installs, with default value, can be overridden externally (default value: postgres - developer based on DB type).
-
INSTALL_URL_ADMIN: The URL of Admin DB for the main installs, with default value, can be overridden externally (default value: PostgreSQL: jdbc:postgresql://module-dookug-postgredb:5432/postgres - Oracle: jdbc:oracle:thin:@module-dookug-oracle:1521/xepdb1 based on DB type).
-
INSTALL_URL_PROJECT: The URL of Project DB for the project schema installs, with default value, can be overridden externally (default value: PostgreSQL: jdbc:postgresql://module-dookug-postgredb:5432/dookug_db - Oracle: jdbc:oracle:thin:@module-dookug-oracle:1521/xepdb1 based on DB type).
-
CREATE_DATABASE: PostgreSQL DB. If the current DB needs to be embedded in another DB (under a specific schema), this variable prevents an empty DB from being created. Default value is TRUE in the before-liquibase file, can be overridden externally.
-
INSTALL_STEPS: Before install, you can set the step(s) of the install, which you need to run. You can list multiple steps as well. (default value: 1,2,3,4), can be overridden externally.
-
INSTALL_SCHEMA: Only for Oracle! The project schema to be installed. (default value: dookug), can be overridden externally.
-
-
Properties variables:
-
Properties file: File belonging to the given DB that provides data for local db access and maps the liquibase changelog file to the given step compose file.
-
URL: URL of the DB to be installed, by default local DB access is specified, can be overridden externally.
-
Oracle DB: (default value: jdbc:oracle:thin:@module-dookug-oracle:1521/xepdb1).
-
Postgres DB/step1: (default value: jdbc:postgresql://module-dookug-postgredb:5432/postgres).
-
Postgres DB/step2: (default value: _jdbc:postgresql://module-dookug-postgredb:5432/dookug_db).
-
Postgres DB/step3: (default value: jdbc:postgresql://module-dookug-postgredb:5432/postgres).
-
Postgres DB/step4: (default value: _jdbc:postgresql://module-dookug-postgredb:5432/dookug_db).
-
-
USERNAME: Username for the DB to be installed, by default local DB access is specified, can be overridden externally.
-
Oracle DB/step1: (default value: system).
-
Oracle DB/step2: (schema2 default value: dookug).
-
Oracle DB/step4: (schema4 default value: dookug).
-
Postgres DB: (default value: postgres).
-
-
PASSWORD: Password for the DB to be installed, by default local DB access is specified, can be overridden externally.
-
Oracle DB: (default value: developer).
-
Postgres DB: (default value: postgres).
-
-
CHANGELOGFILE: Name of the liquibase changelog file, by default local changelog file access for the given DB step is specified.
-
step1: DB installation, users, permissions, etc. (default value: liquibase-install-step-01.xml).
-
step2: Liquibase installation, DB objects (default value: liquibase-install-step-02.xml).
-
step3: ONLY PostgreSQL - CRON scheduler entry (default value: liquibase-install-step-03.xml).
-
step4: Optional! Default (test/dev) template loading (default value: liquibase-install-step-04.xml).
-
-
4. Installation, Release, Deployment
Local install (click here)
#=====================
#***Full install***
#INSTALL_STEPS: This is NOT required, the default value: "1,2,3,4".
# The full is 1,2,3,4, or as many as you have, or you can give that step(s) you want!
#INSTALL_PGTOOLS: ONLY in postgresql install!
# This is NOT required, the default value: true.
# In case of locale Postgresql development, this installer automatically installs the PG_TOOLS as well in step2!
# If you turn this parameter on, the 2nd installation step is mandatory in the INSTALL_STEPS env. variable!
# You can turn it off with the value=false
#AUTO_INSTALL: This is REQUIRED!
# at the moment you can use postgresql or oracle
#INSTALL_SCHEMA: ONLY in oracle install!
# This is NOT required, the default value: "dookug".
#=====================
#postgresql default local install:
#this runs all the 4 steps by default
#-------------------------
docker run -it --rm \
--network=dookug-local-network \
-e AUTO_INSTALL=postgresql \
-e INSTALL_USERNAME_PROJECT=dookug_user \
icellmobilsoft/dookug_db:2.0.0-SNAPSHOT
#-------------------------
#If you need only a specific step(s):
#-------------------------
docker run -it --rm \
--network=dookug-local-network \
-e AUTO_INSTALL=postgresql \
-e INSTALL_USERNAME_PROJECT=dookug_user \
-e INSTALL_STEPS=2,4 \
icellmobilsoft/dookug_db:2.0.0-SNAPSHOT
#-------------------------
#oracle local install:
#this runs all the 4 steps by default
#-------------------------
docker run -it --rm \
--network=dookug-local-network \
-e AUTO_INSTALL=oracle \
icellmobilsoft/dookug_db:2.0.0-SNAPSHOT
#-------------------------
#If you need only a specific step(s):
#-------------------------
docker run -it --rm \
--network=dookug-local-network \
-e AUTO_INSTALL=oracle \
-e INSTALL_SCHEMA=schema_name \
-e INSTALL_STEPS=2,4 \
icellmobilsoft/dookug_db:2.0.0-SNAPSHOT-
How "embed" Dookug DB into other "host" DB (install Dookug-db dookug schema into other DB):
Embed DookuG in to other DB (click here)
#---------------------
#Postgres and Linux:
#INSTALL_STEPS: This is NOT required, the default value: "1,2,3,4".
# The full is 1,2,3,4, or as many as you have, or you can give that step(s) you want!
#INSTALL_PGTOOLS: ONLY in postgresql install!
# This is NOT required, the default value: true.
# In case of locale Postgresql development, this installer automatically installs the PG_TOOLS as well in step2!
# If you turn this parameter on, the 2nd installation step is mandatory in the INSTALL_STEPS env. variable!
# You can turn it off with the value=false
#AUTO_INSTALL: This is REQUIRED!
# at the moment you can use postgresql or oracle
#INSTALL_USERNAME_PROJECT: ONLY in postgresql install!
# In case of oracle you have to use the INSTALL_SCHEMA, b/c in oracle, the schema is the user as well.
#INSTALL_SCHEMA: ONLY in oracle install!
# This is NOT required, the default value: "dookug".
#CREATE_DATABASE: When embedding this DookuG installer into another (host) DB, set this to false to avoid creating an empty database.
#in case of server install, you must use the DEFAULT and PROJECT parameters.
# INSTALL_URL_ADMIN
# INSTALL_URL_PROJECT
# INSTALL_USERNAME_ADMIN
# INSTALL_USERNAME_PROJECT
# INSTALL_PASSWORD_ADMIN
# INSTALL_PASSWORD_PROJECT
#---------------------
#postgresql:
#-----------
docker run -it --rm \
--network=host-local-network \
-e AUTO_INSTALL=postgresql \
-e INSTALL_URL_ADMIN=jdbc:postgresql://host_db-postgredb:port_num/postgres \
-e INSTALL_URL_PROJECT=jdbc:postgresql://host_db-postgredb:port_num/host_db \
-e INSTALL_USERNAME_ADMIN=postgres \
-e INSTALL_PASSWORD_ADMIN=passw_from_secret \
-e INSTALL_USERNAME_PROJECT=project_user_name \
-e INSTALL_PASSWORD_PROJECT=passw_from_secret \
-e CREATE_DATABASE=false \
-e INSTALL_STEPS=1,2,3,4 \
-e INSTALL_SCHEMA=dookug_schema_name \
-e INSTALL_PGTOOLS=false \
icellmobilsoft/dookug_db:2.0.0-SNAPSHOT
#-----------
#oracle:
#-----------
docker run -it --rm \
--network=host-local-network \
-e AUTO_INSTALL=oracle \
-e INSTALL_URL_ADMIN=jdbc:oracle:thin:@host_db-oracle:1521/xepdb1 \
-e INSTALL_URL_PROJECT=jdbc:oracle:thin:@host_db-oracle:1521/xepdb1 \
-e INSTALL_USERNAME_ADMIN=system \
-e INSTALL_PASSWORD_ADMIN=passw_from_secret \
-e INSTALL_PASSWORD_PROJECT=passw_from_secret \
-e CREATE_DATABASE=false \
-e INSTALL_STEPS=1,2,3,4 \
-e INSTALL_SCHEMA=dookug_schema_name \
icellmobilsoft/dookug_db:2.0.0-SNAPSHOT#on windows: the "\" needs to be changed to "`"!
-
optional environment variable, only needed during password change:
-e DB_SERVICE_USER_PASSWORD=<service user passw>
5. Release notes
-
0.1.0 Changes:
-
Dookug db install
-
Templates install
-
Documentation
-
-
1.0.0 Changes:
-
DATE type has been replaced with Timestamp(6) in Common.dtd file.
-
DKG-228 - Insert a new flexible template into the boards.
-
DKG-217 - Replacing new date types in the boards.
-
DKG-233 - Hash Fix
-
DKG-231 - Convert documentation
-
DKG-238- Load default templates in Step 4.
-
DKG-245 - Improvement of Dookug Installation Error
-
-
1.0.3 Changes:
-
DKG-246 - Template Content Fix
-
DKG-261 - Repair of Readme adoc.
-
DKG-261 - Repair of Install.adoc.
-
-
1.1.0 Changes:
-
DKG-265 - open source changes
-
DKG-297 - Drop validity_start, validity_end cols from template_part_content, drop template_part_content.template_part_id index, change template_part_content.template_part_id index to unique.
-
-
1.2.0 Changes:
-
Technical release: Backend version sync, no changes compared to 1.1.0
-
-
2.0.0 Changes:
-
DKG-319 - Refactoring the installation process.
-