SQL-Datenmodell

Overview

Table Details

All timestamps are stored as BIGINT as milliseconds since Unix epoch, are mapped to Java java.time.Instant.

Table dds_user

Column	Database Type	Java Type	Remarks
id	BIGINT NOT NULL PRIMARY KEY	Long	The primary key
create_ts	BIGINT NOT NULL	Instant
domain	VARCHAR(255) NOT NULL	String
user_id	VARCHAR(255)	String
given_name	VARCHAR(255)	String
sur_name	VARCHAR(255)	String
email	VARCHAR(255)	String

Table dds_user_group

Column	Database Type	Java Type	Remarks
id	BIGINT NOT NULL PRIMARY KEY	Long	The primary key
ad_group_id	VARCHAR(255) NOT NULL UNIQUE	String
dds_name	VARCHAR(255) NOT NULL UNIQUE	String

Table dds_user_group_permission

Column	Database Type	Java Type	Remarks
id	BIGINT NOT NULL PRIMARY KEY	Long	The primary key
group_id	BIGINT NOT NULL REFERENCES dds_user_group(id)	Long
right_id	BIGINT NOT NULL REFERENCES dds_user_right(id)	Long
data_source_id	BIGINT REFERENCES dds_datasource(id)	Long	null means all datasources
max_confidentiality	VARCHAR(255)	String	null means all confidentialies

Table dds_user_right

Column	Database Type	Java Type	Remarks
id	BIGINT NOT NULL PRIMARY KEY	Long	The primary key
act	VARCHAR(255)	String
technical_key	VARCHAR(255)	String

Table dds_schema

Column	Database Type	Java Type	Remarks
id	BIGINT NOT NULL PRIMARY KEY	Long	The primary key
description	VARCHAR(255) NOT NULL	String
long_name_pattern	VARCHAR(255) NOT NULL	String
long_name_annotations_pattern	VARCHAR(255) NOT NULL	String
short_name_pattern	VARCHAR(255) NOT NULL	String

Table dds_schema_prop

Column	Database Type	Java Type	Remarks
id	BIGINT NOT NULL PRIMARY KEY	Long	The primary key
type	VARCHAR(255) NOT NULL	String
json_name	VARCHAR(255) NOT NULL UNIQUE	String
importance	VARCHAR(255) NOT NULL	String
label	VARCHAR(255) NOT NULL UNIQUE	String
description	VARCHAR(4096)	String
true_name	VARCHAR(255)	String
false_name	VARCHAR(255)	String
editable	BOOLEAN NOT NULL	Boolean
searchable	BOOLEAN NOT NULL	Boolean
priority	INTEGER NOT NULL	Integer
entity_type	VARCHAR(32) NOT NULL	String
other_range_bound	BIGINT REFERENCES dds_schema_prop(id)	Long
range_start	BOOLEAN	Boolean
required	BOOLEAN NOT NULL	Boolean
for_export	BOOLEAN NOT NULL	Boolean	Whether this property will be included in the export metadata

Table dds_schema_schema_prop

Column	Database Type	Java Type	Remarks
schema_prop_id	BIGINT NOT NULL PRIMARY KEY REFERENCES dds_schema_prop(id)	Long
schema_id	BIGINT NOT NULL PRIMARY KEY REFERENCES dds_schema(id)	Long

Table dds_enum_value

Column	Database Type	Java Type	Remarks
id	BIGINT NOT NULL PRIMARY KEY	Long	The primary key
schema_prop_id	BIGINT NOT NULL REFERENCES dds_schema_prop(id)	Long
value	VARCHAR(255) NOT NULL	String
description	VARCHAR(1024)	String

Table dds_datasource

Column	Database Type	Java Type	Remarks
id	BIGINT NOT NULL PRIMARY KEY	Long	The primary key
data_point_schema_id	BIGINT NOT NULL REFERENCES dds_schema(id)	Long
data_variant_schema_id	BIGINT NOT NULL REFERENCES dds_schema(id)	Long
short_name	VARCHAR(255) NOT NULL UNIQUE	String
long_name	VARCHAR(1024) NOT NULL	String
long_description	VARCHAR(4096)	String
database_type	VARCHAR(255)	String
database_connection	VARCHAR(255)	String
table_name	VARCHAR(1024)	String
user_read_permission	VARCHAR(255)	String
schemas	VARCHAR(255)	String
time_description	VARCHAR(255)	String
analysis_time_window	VARCHAR(255)	String
null_interpretation	VARCHAR(255)	String
preview_limit	BIGINT	Long
export_block_size	INTEGER	Integer
further_information_link	VARCHAR(255)	String
inbound_outbound	VARCHAR(255)	String
privacy_information	VARCHAR(4096)	String
deputy_email	VARCHAR(80) NOT NULL	String
datapoint_creation_method	VARCHAR(16)	String
props	JSONB NOT NULL	at.iteg.dds.api.dto.DdsDatasourceProps	Using json here would not have been my first choice but it seems to lead to the best outcome. Evaluated alternatives: - postgresql table inheritance: not chosen because indices, unique indices, and primary keys do not carry over across different sub-tables which would prevent us from having a foreign key to the datasource. - inheritance by using a central id and sub-tables having a foreign key to the central table: Not chosen because the amount of code to map this structure to java classes would be quite large and would probably have to be repeated for every datasource type that we decide to add. - Simply having every property on dds_datasource: Zero separation of concerns, Makes it hard to attribute properties to datasource types which might lead to datasource types using properties that should not actually be defined for them, Unclear nullability. In the end using json allows to define the properties that are allowed for a datasource type in code and make it all type-safe.
export_column_description	VARCHAR(4096)	String

Table dds_dirty_flags

Column	Database Type	Java Type	Remarks
id	BIGINT NOT NULL PRIMARY KEY	Long	id is dbType SERIAL by migration
create_ts	BIGINT NOT NULL	Instant	create_ts is dbType timestamp by migration
dirty_flag	VARCHAR(10) NOT NULL	String
context	VARCHAR(255) NOT NULL	String

Table dds_data_point

Column	Database Type	Java Type	Remarks
id	BIGINT NOT NULL PRIMARY KEY	Long	The primary key
import_props	JSONB NOT NULL	java.util.Map<String, Object>	JSON object containing all attributes imported from the foreign database system.
user_props	JSONB	java.util.Map<String, java.util.Optional<Object>>	JSON object containing all attributes entered by the user. null values mean that the property is explicitly empty
import_props_hash	BYTEA NOT NULL	byte[]
created	BIGINT NOT NULL	Instant
created_by	BIGINT NOT NULL REFERENCES dds_user(id)	Long
updated	BIGINT	Instant
updated_by	BIGINT REFERENCES dds_user(id)	Long
invalidated	BIGINT	Instant
invalidated_by	BIGINT REFERENCES dds_user(id)	Long
datasource_id	BIGINT NOT NULL REFERENCES dds_datasource(id)	Long
primary_prop	VARCHAR(255) NOT NULL	String	The identifier(s) in the foreign database. Is unique in combination with the datasource_id
confidentiality	VARCHAR(2) NOT NULL	String
merged_props	JSONB NOT NULL	java.util.Map<String, Object>	The generated combination of import_props and user_props. Entries in user_props override entries in imports_props.
fulltext_key	VARCHAR(255) NOT NULL	String	Commented out because we cannot do anything with this column because of its type.

Table dds_data_variant

Column	Database Type	Java Type	Remarks
id	BIGINT NOT NULL PRIMARY KEY	Long	The primary key
created	BIGINT NOT NULL	Instant	The timestamp when this entity was created.
created_by	BIGINT NOT NULL REFERENCES dds_user(id)	Long	The user this entity was created by.
updated	BIGINT	Instant	The timestamp when this entity was last update.
updated_by	BIGINT REFERENCES dds_user(id)	Long	The user this entity was last updated by.
invalidated	BIGINT	Instant	The timestamp of this entities deletion (Soft-delete)
invalidated_by	BIGINT REFERENCES dds_user(id)	Long	The user this entity was deleted by.
import_props	JSONB NOT NULL	java.util.Map<String, Object>	JSON object containing all attributes imported from the foreign database system.
user_props	JSONB	java.util.Map<String, java.util.Optional<Object>>	JSON object containing all attributes entered by the user. null values mean that the property is explicitly empty
import_props_hash	BYTEA NOT NULL	byte[]	A cryptographic hash of the import_props. Calculated using a procedure that ignores the order of the props. Used for change detection
data_point_id	BIGINT NOT NULL REFERENCES dds_data_point(id)	Long
primary_prop	VARCHAR(255) NOT NULL	String	The identifier(s) in the foreign database. Is unique in combination with the datasource_id of the corresponding datapoint.
merged_props	JSONB NOT NULL	java.util.Map<String, Object>	The generated combination of import_props and user_props. Entries in user_props override entries in imports_props.

Table dds_data_fetch_statistic

Column	Database Type	Java Type	Remarks
id	BIGINT NOT NULL PRIMARY KEY	Long	The primary key
fetch_period_ns	BIGINT	Long	TODO Nullable + Index anschauen; Reihenfolge im Index wichtig!
fetch_from	BIGINT	Instant
fetch_to	BIGINT	Instant
created	BIGINT NOT NULL	Instant
data_variant_id	BIGINT NOT NULL REFERENCES dds_data_variant(id)	Long
start_up_time_ns	BIGINT NOT NULL	Long
fetch_time_ns	BIGINT NOT NULL	Long
job_id	BIGINT	Long	no foreignKey to keep statistics when deleting job
job_run_id	BIGINT	Long	no foreignKey to keep statistics when deleting jobRun
template_id	BIGINT	Long	no foreignKey to keep statistics when deleting template
result_limit	BIGINT	Long
result_from	BIGINT	Instant
result_to	BIGINT	Instant
result_count	BIGINT	Long

Table dds_unit

Column	Database Type	Java Type	Remarks
id	BIGINT NOT NULL PRIMARY KEY	Long	The primary key
unit	VARCHAR(64) NOT NULL	String
description	VARCHAR(1024)	String
dimension	VARCHAR(255) NOT NULL	String
is_standard	BOOLEAN NOT NULL	Boolean	True = Standardeinheit (wie in Referenzdatenbank); False = Einheit aus Quelldatenbank (wenn Importer es erlaubt, Importer können Quelldatenbank-Einheiten auf nicht vorhanden abbilden, oder durchlassen; an der GUI (Admin/Einheiten) "Importiert"

Table dds_unit_mapping

Column	Database Type	Java Type	Remarks
id	BIGINT NOT NULL PRIMARY KEY	Long	The primary key
key	VARCHAR(255) NOT NULL	String
value	VARCHAR(255) NOT NULL	String
convertion_formula	VARCHAR(255)	String
datasource_id	BIGINT REFERENCES dds_datasource(id)	Long

Table dds_ksv

Column	Database Type	Java Type	Remarks
id	BIGINT NOT NULL PRIMARY KEY	Long	The primary key
deleted	BOOLEAN NOT NULL	Boolean
released	BOOLEAN NOT NULL	Boolean
parent_id	BIGINT REFERENCES dds_ksv(id)	Long	If parent_id is null this ksv node is at the root deferred during the ksv import
code	VARCHAR(80) NOT NULL UNIQUE	String
name	VARCHAR(255) NOT NULL	String
fulltext_key	VARCHAR(255) NOT NULL	String
full_path	BIGINT[] NOT NULL	Long[]	The entire list of ancestors including ourselves. [root,level1,level2,...,self]
leaf	BOOLEAN NOT NULL	Boolean	true if this node doesn't have any children

Table dds_tag

Column	Database Type	Java Type	Remarks
id	BIGINT NOT NULL PRIMARY KEY	Long	The primary key
label	VARCHAR(255) NOT NULL UNIQUE	String
description	VARCHAR(255) NOT NULL	String
created	BIGINT NOT NULL	Instant
created_by	BIGINT NOT NULL REFERENCES dds_user(id)	Long
updated	BIGINT	Instant
updated_by	BIGINT REFERENCES dds_user(id)	Long

Table dds_template_module_to_datasource

datasource filter

Column	Database Type	Java Type	Remarks
datasource_id	BIGINT NOT NULL PRIMARY KEY REFERENCES dds_datasource(id)	Long
template_module_id	BIGINT NOT NULL PRIMARY KEY REFERENCES dds_template_module(id)	Long

Table dds_template_module

Column	Database Type	Java Type	Remarks
id	BIGINT NOT NULL PRIMARY KEY	Long	The primary key
name	VARCHAR(255) NOT NULL UNIQUE	String
description	VARCHAR(1024)	String
is_private	BOOLEAN NOT NULL	Boolean
search_term	VARCHAR(255)	String
activity_filter	VARCHAR(32)	String
chart_configuration	JSONB	at.iteg.dds.api.dto.chartconfig.DbChartConfiguration
betriebs_berichte_enabled	BOOLEAN NOT NULL	Boolean
betriebs_berichte_ksv_filter	VARCHAR(255)	String	The betriebsberichte ksv filter and the search ksv filter are treated as an entirely different thing on the backend The fontend assumes and ensures that they are both the same thing This is a bit weird, but it simplifies many things
betriebs_berichte_description_filter	VARCHAR(255)	String
betriebs_berichte_reason_filter	TEXT[]	String[]
include_annotations	BOOLEAN NOT NULL	Boolean
created	BIGINT NOT NULL	Instant
created_by	BIGINT NOT NULL REFERENCES dds_user(id)	Long
updated	BIGINT	Instant
updated_by	BIGINT REFERENCES dds_user(id)	Long

Table dds_search_filter

One of the filters in a template module.

Column	Database Type	Java Type	Remarks
id	BIGINT NOT NULL PRIMARY KEY	Long	The primary key
template_module_id	BIGINT NOT NULL REFERENCES dds_template_module(id)	Long
entity_type	VARCHAR(32) NOT NULL	String	The type of entity this filter acts on. Can be data_point or data_variant
schema_prop_id	BIGINT NOT NULL REFERENCES dds_schema_prop(id)	Long	The property that this filter filters.
type	VARCHAR(32) NOT NULL	String	The filter operator Can be EQUAL, LESS_THAN, LESS_OR_EQUAL, GREATER_THAN, GREATER_OR_EQUAL, STARTS_WITH, ENDS_WITH, CONTAINS, IN, UNDEF, or LIKE
value	JSONB	Object	The operator value The meaning of this attribute changes depending on the operator type

Table dds_template

Column	Database Type	Java Type	Remarks
id	BIGINT NOT NULL PRIMARY KEY	Long	The primary key
name	VARCHAR(255) NOT NULL UNIQUE	String
description	VARCHAR(1024)	String
notes	VARCHAR(4096)	String
visibility	VARCHAR(32)	String
default_period	VARCHAR(16) NOT NULL	String	Default value of defaultPeriod is 'daily'
created	BIGINT NOT NULL	Instant
created_by	BIGINT NOT NULL REFERENCES dds_user(id)	Long
updated	BIGINT	Instant
updated_by	BIGINT REFERENCES dds_user(id)	Long

Table dds_template_module_to_template

Column	Database Type	Java Type	Remarks
template_id	BIGINT NOT NULL PRIMARY KEY REFERENCES dds_template(id)	Long
template_module_id	BIGINT NOT NULL PRIMARY KEY REFERENCES dds_template_module(id)	Long

Table dds_tag_to_template

Column	Database Type	Java Type	Remarks
template_id	BIGINT NOT NULL PRIMARY KEY REFERENCES dds_template(id)	Long
tag_id	BIGINT NOT NULL PRIMARY KEY REFERENCES dds_tag(id)	Long

Table dds_template_module_to_data_variant

selected variants

Column	Database Type	Java Type	Remarks
id	BIGINT NOT NULL PRIMARY KEY	Long	The primary key
data_variant_id	BIGINT NOT NULL REFERENCES dds_data_variant(id)	Long
template_module_id	BIGINT NOT NULL REFERENCES dds_template_module(id)	Long
description	VARCHAR(256)	String

Table dds_template_job

Column	Database Type	Java Type	Remarks
id	BIGINT NOT NULL PRIMARY KEY	Long	The primary key
created	BIGINT NOT NULL	Instant	The timestamp when this entity was created.
created_by	BIGINT NOT NULL REFERENCES dds_user(id)	Long	The user this entity was created by.
updated	BIGINT	Instant	The timestamp when this entity was last update.
updated_by	BIGINT REFERENCES dds_user(id)	Long	The user this entity was last updated by.
invalidated	BIGINT	Instant	The timestamp of this entities deletion (Soft-delete)
invalidated_by	BIGINT REFERENCES dds_user(id)	Long	The user this entity was deleted by.
name	VARCHAR(255) NOT NULL UNIQUE	String
template_id	BIGINT NOT NULL REFERENCES dds_template(id)	Long
description	VARCHAR(4096)	String
is_draft	BOOLEAN NOT NULL	Boolean
is_in_health_check	BOOLEAN NOT NULL	Boolean
export_target_id	BIGINT NOT NULL REFERENCES dds_export_target(id)	Long
notification_emails	VARCHAR(1023)	String
activated	BIGINT	Instant	The user that activated this job. Used for the authorization of export variants using dds_template_job_activation_group
activated_by	BIGINT REFERENCES dds_user(id)	Long
start_boundary_timestamp	BIGINT NOT NULL	Instant	In UTC
start_boundary_timezone	VARCHAR(32) NOT NULL	String
integrity_duration	BIGINT NOT NULL	Instant
period	VARCHAR(32) NOT NULL	String
retry_delay	INTEGER	Integer	seconds
retry_timeout	BIGINT	Long	seconds
retry_max_count	INTEGER	Integer

Table dds_template_job_activation_group

The groups of the activatedBy user at the point in time where they activated it. Storing these prevents us from needing to fetch the groups of a user that is not logged in and prevents the export job from failing if the activating user priviledges are reduced by, for example, them leaving the company.

Column	Database Type	Java Type	Remarks
template_job_id	BIGINT NOT NULL PRIMARY KEY REFERENCES dds_template_job(id)	Long
user_group_id	BIGINT NOT NULL PRIMARY KEY REFERENCES dds_user_group(id)	Long

Table dds_job_run

Column	Database Type	Java Type	Remarks
id	BIGINT NOT NULL PRIMARY KEY	Long	The primary key
job_id	BIGINT NOT NULL REFERENCES dds_template_job(id)	Long	The ID of the Template/ExportJob
run_ts	BIGINT NOT NULL	Instant	When this job run SHOULD run, e.g. now or at x o'clock on y date, depending on start
uuid	VARCHAR(255)	String	The unique schedule UUID, i.e. the ID it is registered in the OneTimeScheduler
run_started_ts	BIGINT	Instant	What time the (last) attempt was started (transition time for state RUNNING)
run_finished_ts	BIGINT	Instant	The last attempt's (final/error) state transition time
from_ts	BIGINT	Instant	DateTime from where data is fetched
to_ts	BIGINT	Instant	DateTime to where data is fetched
iteration	INTEGER	Integer	Unused; idea was to store the auto-schedule iteration. By that one knows whether this was an automatic scheduled job run or adhoc. Currently, it is never set.
attempt	INTEGER NOT NULL	Integer	If there was an error, which attempt is scheduled?
status	VARCHAR(16) NOT NULL	String	Statemachine status
result_count	BIGINT	Long	If SUCCESS, how many elements were fetched (in sum)
is_periodically	BOOLEAN NOT NULL	Boolean	Whether this job run was started as periodically scheduled (false: called adhoc)
started_by	BIGINT	Long	The numeric ID of the user who scheduled this job

Table dds_export_target

Column	Database Type	Java Type	Remarks
id	BIGINT NOT NULL PRIMARY KEY	Long	The primary key
name	VARCHAR(255) NOT NULL	String	There is an XOR constraint on schemaName and filePath (only and exactly one may exist) (dds_export_target_schema_name_check_xor_file_path) There is a UNIQUE constraint on name (dds_export_target_unique_name) There is a ENUM constraint on confidentiality (dds_export_target_check_confidentiality)
schema_name	VARCHAR(40)	String
file_path	VARCHAR(255)	String
confidentiality	VARCHAR(2) NOT NULL	String
default_csv_format	BIGINT REFERENCES dds_csv_export_format(id)	Long
export_db	VARCHAR(32)	String

Table dds_csv_export_format

The csv export options.

Column	Database Type	Java Type	Remarks
id	BIGINT NOT NULL PRIMARY KEY	Long	The primary key
media_type	VARCHAR(255)	String	The media type, always text/csv;charset=utf-8
delimiter	VARCHAR(1)	String	The csv options according to commons-csv CSVFormat
record_separator	VARCHAR(255)	String
quote	VARCHAR(1)	String
escape_character	VARCHAR(1)	String
decimal_separator	VARCHAR(1)	String
date_time_format	VARCHAR(255)	String	The datetime format string according to DateTimeFormatter.
timezone	VARCHAR(255)	String	The iana timezone id that will be used for all output dates. Offset specifications strings like those used by ZoneOffset shouldn't be used because they lack good interoperability between datetime libraries
datavariant_layout	VARCHAR(255)	String	vertical or horizontal

Table dds_tag_to_datapoint

Column	Database Type	Java Type	Remarks
id	BIGINT NOT NULL PRIMARY KEY	Long	The primary key
tag_id	BIGINT NOT NULL REFERENCES dds_tag(id)	Long
data_point_id	BIGINT NOT NULL REFERENCES dds_data_point(id)	Long