Database Structure

This page was automatically generated from the database schema dump and may be incomplete or incorrect.

This section documents the database structure of the enaio® system — which tables exist in the database schema, which columns they carry, and how the recurring patterns of the object, stamm and register tables relate to each other. The schema is database-independent — enaio® supports several relational database backends (e.g. Microsoft SQL Server, Oracle). Data types in this documentation are given in SQL Server notation (int, nvarchar, datetime, bigint, smallint, real, image); the Oracle equivalents are NUMBER, NVARCHAR2, DATE, NUMBER(19), NUMBER(5), BINARY_FLOAT and BLOB respectively.

The structures described here are the immediate physical storage form; they are generally not modified directly (except via ado.ExecuteSQL scripts for administrative corrections), but through the DMS, Standard and Workflow engines. This documentation is intended as a reference for administrators, report developers and integration developers.

1. Schema Conventions

The database uses a mix of fixed system tables and type-specific tables per object type. The following conventions are applied throughout:

1.1. Suffix scheme

For every configured DMS object type, several tables exist whose names carry a sequential number X. X corresponds to the main type (haupttyp column) + 1 — object1 thus contains objects with haupttyp = 0, object17 those with haupttyp = 16. The full object-type ID (haupttyp * 65536 + untertyp) is described in Object-type ID.

Name Content

objectX / stammX / registerX

Main object table. Holds one row per object of type X. Contains common system fields (id, timestamp, status, permission IDs, …) plus type-specific index fields.

objectXr / stammXr / registerXr

Rights table. Holds per-object permission entries (complementary to the global security system via ossd). Identical 4-column structure across all object types.

objectXs / stammXs / registerXs

Shadow table for index-data history. Holds the previous index-data state before each change. Only osguid is common (snapshot identifier); after it follow the type-specific index fields.

objectXlistY / stammXlistY / registerXlistY

Table-field data. One table per table-field Y defined on object type X (grid field); holds one row per grid row. Common fields: id (FK to the main object) and line (row number in the grid).

objectXlistYs / stammXlistYs / registerXlistYs

Shadow variant for table fields. Common: osguid and line.

1.2. Column-name convention

Index fields follow a historically grown, type-dependent positional notation (Hungarian style):

  • feld1, feld2, … — text fields (nvarchar)

  • zahl1, zahl2, … — integer fields (int / smallint)

  • datum1, datum2, … — date fields (datetime)

  • real1, real2, … — decimal / floating-point fields (decimal / real)

The mapping "column name → semantic index field" is configured per object type and accessible via the DMS object definition (dms.GetObjDef).

System fields, on the other hand, use German-language descriptive names (anleger, angelegt, archiviert, zeitstempel, medium_doc, …) — a historical legacy.

2. Hierarchical object types

The following three pages describe the pattern — common fields, rights, shadow and table-field variants — and not individual numbered tables.

3. Users and groups

4. Portfolios and document assignments

  • mappe — portfolios.

  • mdrel — portfolio-document assignment.

5. Media and archive

  • medien — storage media, including the WORK and NOTE system media.

  • medrel — media-set relations.

  • archex — archive extensions / server assignment.

6. Locks and links

  • doclock — document locks (checkout).

  • hyplnk — hyperlinks (cross-references between objects).

  • combined — combined documents (parent/child relations).

7. Annotations

8. External systems

9. Audit and history

10. Sessions, devices and locks

11. Permissions and profiles

The workflow organisation objects (osorg*) are documented under Workflows.

12. System events

13. DMS object types, index fields and structures

14. Versions, hashes and signatures

15. Annotations and collaboration

16. Portfolios (OS variant)

17. Remarks and texts

17.1. Storage of notes

Notes on DMS objects come in two kinds, held in different tables:

  • Text note — coloured free text on the object, managed in remarks.

  • Note link — a reference to another DMS object, managed in hyplnk and not in remarks.

Both are possible on folders, registers, documents and portfolios. Their number per object is also kept in the links column of the corresponding index table, which is what drives the note icon in result lists.

The text of a text note is held either in the file system or in the database, depending on server configuration. The switch sits in AS.cfg, section [System], key NoteStorage: 0 = file system, 1 = database. It is not in the server registry and therefore cannot be reached through krn.REGetRegValue; it can be read with adm.GetSystemFile using FileName = as.cfg, and the file comes back as UTF-16LE.

Aspect File system Database

Location of the text

one .txt file per note below the NOTE directory

column ostext in osremarktext, addressed via remarks.textid

remarks.medium

medium ID of the NOTE system medium in medien

NULL

medium in the query output

the same medium ID

0

remarks.textid

NULL

key in osremarktext

<Text id="…"> in the query output

empty

the textid

Length limit

none

8000 characters; beyond that the import fails with -1028 instead of truncating

Searching the note content

not possible

possible

std.GetRemark, std.StoreRemark, std.DeleteRemark

the responsible jobs

not usable, see the warning below

Creating, changing and deleting through XML

dms.XMLUpdate with <RemarkText>

the same
The three std remark jobs require file-system storage. Under database storage std.GetRemark and std.DeleteRemark fail with -1043201745, and std.StoreRemark silently migrates the note back into the file system: the job returns 0, creates a note file, sets remarks.medium to the medium ID and leaves the row in osremarktext orphaned with the old text.

The statements in this section were measured against enaio® server 12.0 (axsvckrn 12.0.799, DMS engine 12.0.769.25242, Microsoft SQL Server 2022).

17.1.1. Location of the note files

The root directory is held in path in the row with flag = 2, one per server group. Below it the files are nested by object type and note ID. All four components are hexadecimal, the directories two digits, the file name eight:

<path>\NOTE\<main type>\<sub type>\<note ID mod 256>\<note ID>.txt

Example for note ID 146610 (0x00023CB2) on an object with main type 4 and sub type 1:

C:\enaio\server\NOTE\04\01\B2\00023CB2.txt

A note file written by the server starts with the UTF-8 BOM and ends with a NUL byte, so eleven characters of text come to 15 bytes. A file handed over through std.StoreRemark is stored verbatim instead.

17.1.2. Switching the storage mode

There is no automatic migration. After switching to database storage, existing text files stay readable unchanged and keep their medium ID in medium.

An individual note moves only when its text is changed, that is on <RemarkText action="UPDATE_TEXT">. It is then given a newly assigned textid, medium is set to NULL and the old .txt file is removed. UPDATE_COLOR does not trigger the move: the colour changes and the note stays in the file system.

An existing long text is not truncated when it moves, because there is no path that would carry it over. UPDATE_TEXT replaces it, and a new text longer than 8000 characters is rejected with -1028.

18. Full-text search

19. Licenses and cryptography

20. Localisation and icons

21. Server cluster and configuration

22. Active editing and temporary state

23. Subscriptions and revisits (OS backing)

24. Workflows

The workflow engine stores models, running processes, history and management data in dedicated tables prefixed oswf. History tables additionally carry the prefix oswfh. The workflow organisation (users, roles, departments, classes and attributes) lives in the osorg* tables and is used both by the workflow engine and by the DMS security system.

Models and definitions

Masks and list UI

Processes and activities (runtime)

Organisation and performers

Workflow-specific performer assignments:

Organisation master data (osorg*) — users, roles, departments, classes, attributes:

Timers

Routing lists

Reports

History (oswfh*)

25. Laboratory (LIMS)

Tables of the laboratory-data integration (Laboratory Master/Transaction Tables):

26. Medicine (HL7)

Tables of the medical-data integration (Medical Master/Transaction Tables):