Physical Model
Last updated
Last updated
In SanteDB, the physical model is used to express the concrete tables which are created in a SQL based RDBMS to store data on a physical media (disk). Implementers of new SQL based persistence services should try to re-use these mappings in their expression to the physical model.
SanteDB uses the ORM Lite data mapping technology, and the SanteDB.Persistence.Data.Ado
assembly contains the ORM lite physical model classes.
Version 2.5 of SanteDB is undergoing a major code refactor. The physical model classes in the 2.5.x series of SanteDB will reside in the SanteDB.Persistence.Data
assembly.
SanteDB includes an implementation of a persistence layer which includes support for generic ADO based databases. This provider uses the ADO.NET wrapper to invoke necessary functions and select data from tables. In order to leverage the ADO.NET persistence service, the ADO.NET provider must support:
SQL92 syntax
Limit and offset queries in the form of SELECT FOO FROM BAR OFFSET x LIMIT y
Invoke of Stored Procedures and Functions or equivalent C# logic which implements the functions
Pooled and thread-safe access to the underlying disk structure.
Currently SanteDB iCDR works with the following OrmLite data providers:
Regardless of the RDBMS system feeding the ADO provider, it must follow the schema specified in this section.
To implement a new ADO.NET based data provider, implementers will need to:
Write an ORM Lite IDbProvider
instance (see an example of the FirebirdSQL provider)
Write an ORM Lite IDbConfigurationProvider
instance (see an example of the FirebirdSQL Configuration provider)
The DDL and DML classes for the most recent SanteDB physical model (see an example of the FirebirdSQL DDL files)
You should include these as embedded resources in your plugin
You should include all updates , translated to your SQL dialect as well
You should include your own provider's invariant in the header
Implementations of any supported extended functions (such as SOUNDEX, LEVENSHTEIN, etc.) as IDbFilterFunction
implementations (see an example FirebirdSQL String Matching Functions)
See: Database Patching article for information on patching SQL structures.
The physical schema of the ADO provider is designed such that many different ADO providers can support the data structures. Because of the limitations on some RDBMS systems, the ADO schema uses a short-hand for table names.
The following suffixes are placed on entities in the schema:
Most tables in the SanteDB physical model carry common abbreviations of names. Because of the varied contributors some tables break these rules, however the general principal applies:
Names less than four characters should be represented in full (example: NAME
or TAG
)
Names which represent a classified object carry their classification, for example:
OBS_TBL
=> Observation Table
SUB_ADM_TBL
=> Substance Administration Table
Association tables which exist only to link together two other tables should have ASSOC
in their name
Longer table names are usually acronyms:
CD_TBL
=> Concept Definition Table
CS_TBL
=> Code System Table
Table and columns should remove vowels where the removal of vowels do no interfere with understanding the meaning of the column or table:
ENT_TBL
=> Entity Table
CLS_CD_ID
=> Class Concept Definition ID
Tables which represent an inheritance are created as discrete tables. Each class in the hierarchy maps to one table in the schema and contain only the attributes of the class.
As an example:
In the database schema this inheritance structure is represented as:
On a versioned database each class down the hierarchy should point to the version identification of the superclass rather than the identifier of the superclass - this is to track versioned changes to first class properties.
On persistence services which provide versioning (such as those in the iCDR) the tables can be split into two categories:
Versioned Tables -> Acts, Entities and Concepts which track changes to data over time.
Non-Versioned Tables -> Any other class which does not track changes over time.
The legacy dCDR SQLite provider is un-versioned. This means all of its tables are not versioned.
Because version data is required for interfaces in the iCDR the structures are broken into a primary table and a versioning table, where:
Primary Table: Contains the unchanging attributes of the object
Version Table: Contains the attributes which may be changed on the object. This table contains:
Sequence Identification (used for tracking the order of versions)
Creation time and provenance (who created the version)
Obsolete time and provenance (who rendered this version obsolete)
Replaced version id (the version that this version replaces)
For example, ENT_TBL
and ENT_VRSN_TBL
are related in this manner:
Tables which are associated with a versioned entity should be linked to the primary key of the non-versioned portion of the data, and carry an Effective Version Seq
and an Obsolete Version Seq.
For example, identifiers for an entity: