Skip to end of metadata
Go to start of metadata

SQL repository implementation uses Hibernate 4 as persistence framework. For connection pool management we're using c3p0.

Database layout

Our data objects defined in common-2.xsd schema which are managed by SQL repository are quite complex XML objects. Every XML object type is broken to smaller parts to create indexable and non indexable values (columns for them). Some parts of object are still saved as XML fragments (e.g. PasswordType).

All XML objects (represented by JAXB generated java pojos) are translated to hibernate entity pojos. These pojos are in common package and we're using annotation mapping on getter methods. Basic class hierarchy is shown on picture below. Not all classes are in diagram, only the most interesting part with it's relationships.

Primary, foreign keys

Basicly we have only one object identificator defined in root element (oid attribute). There is also one special case AssignmentType, which is represented by It can occur zero to many times in RoleType as well as in UserType. That's why our hierarchy need to use composite key. Composite key consists from oid (represents parent container identificator - owner) and id (represents container identification, in our case identification of AssignmentType in RoleType or UserType. For "parent" entities like UserType, RoleType, ObjectType and other id is always zero. Since relational database can't represent parent-child relationships like XML (parent-child relationship can be represented in XML by placing child inside parent tags e.g. <parent><child>foo</child></parent>) it's using primary/foreign keys to represent this relationship. And since common schema doesn't define custom identificators for sub elements in XML (and therefore "non" parent hibernate entities) SQL repository had to inherit primary key from parent entity by using @MapsId.

Extension (or xsd:any)

Extensions or xsd:any can be found in:

  • ObjectType and sub classes - parent entity from Hibernate point of view
  • ResourceObjectShadowType attributes - from DB point of view it's the same as other xsd:any.
  • AssignmentType - sub entity from Hibernate point of view

Schema for xsd:any is not defined statically and it can contain elements of different types. SQL repository supports String, Long, Date, Clob. All elements are aggregated to this types (tables).

  • Long, Integer, Short -> Long
  • Date (Gregorian calendar) -> Date
  • String, Double, Float -> String
  • Everything else goes to Clob

Long, Date and String values are indexed, clob is not indexed. Real types for aggregated types are saved as QName values. Aggregation translation for xsd:any values is provided by RAnyConverter.


References in schema are represented by ObjectReferenceType. SQL repository implementation handles it like it's shown on picture.

Database model

Database generated from MySQL DDL script.

Transactions, locking failover

SQL repository uses simple transactions which lasts during single operation (method). When transaction fails because of locking (table lock timeout, record timeout), SQL repository tries to repeat operation for number SqlBaseService LOCKING_MAX_ATTEMPTS times.


SQL repository contains com.evolveum.midpoint.repo.sql.query.QueryInterpreter which can interpret queries defined by query-2 schema. Currently supported query filters are equal, substring, and, or, not. QueryInterpreter uses QueryRegistry to translate queried value name to real hibernate entity property name. QueryRegistry loads entity and attribute definitions during initialization.


Actual configuration used by SQL repository is placed in main configuration file config.xml in midpoint.home folder. Basic configuration is simple. It will use embedded H2 database which will be placed in midpoint.home folder.

For more complex installations you will probably want to use non embedded RDBMS solutions like PostgreSQL, MySQL, etc. Configuration for non embedded database look like this:

Configuration options

Basic JDBC configuration - driver, username, password and JDBC URL.

SQL dialect based on chosen DB. When embedded mode is enabled default value is org.hibernate.dialect.H2Dialect. Supported hibernate dialects

Embedded mode (if true we're using H2). Default true.

This option can be used if we're running in embedded mode. If server mode is turned on H2 runs with TCP server. Other applications/services can connect to H2 server. If false, H2 runs in file mode. Default value is false.

Embedded server mode SSL (if true we're using H2). Default value is false.

Embedded server mode port. Default value is 5437.

Directory where h2 files will be saved if we're running in embedded mode. Default value is ..

Database filename. Name for H2 files if we're running in embedded mode. Default value is midpoint.

Automatically validates or exports schema DDL to the database when the SessionFactory is created. With create-drop, the database schema will be dropped when the SessionFactory is closed explicitly. E.g. validate | update | create | create-drop.

Drops database files if they exist during start. Useful for tests. Default value is false.

Advanced configuration

Advanced configuration can be done in application-context-repository.xml. In this file you can change c3p0 connection pool parameters as well as modify data source and hibernate properties.

As for c3p0, we're now using:

Available c3p0 parameters

Hibernate configuration properties

  • No labels