select * from (values ('a'), ('B'), ('Z'), ('x'), ('Č'), ('@'), ('_'), ('%'), ('^'), ('5'), ('47'), ('持')) as t(text) order by text -- collation "C"
These are based on RepositoryService interface and there should be no difference compared to the legacy repository. Each point contains design/implementation notes.
Repository stores midPoint objects (subtypes of
ObjectType), assigning their OID if missing. For this
fullObject(BYTEA) columns would be enough. However, this is not acceptable for
searchObjectsperformance see note there.
Repository assigns any missing container IDs (CID) for object’s containers. These IDs are unique inside a single object and assigned loosely sequentially. For this
m_object.cid_seqis stored in the database and it contains the next available CID. IDs are assigned only to multi-valued containers, e.g. object’s metadata does not need any CID.
Repository allows for searching for objects and selected containers with reasonable performance. This is why objects stored as documents (
fullObject) is not enough. Various types are stored in specific sub-tables instead with values extracted to separate columns. There are other related tables, typically for containers and other multi-value properties. Not all values are by default searchable/indexable in this way and it must be possible to specify what other properties are indexed. These can be properties from the default schema but also from schema extensions.
Each object is a separate "aggregate" and any inter-object references are soft, not constraints on the database level. For example, multi-value container
roleMembershipRefis stored in
m_ref_role_membershiptable which has a foreign key to the
Depending on the chosen collation ordering of text fields can be case-insensitive or not. To see it quickly, one can use select like this:
collation "x" one can see various results for other collations.
en_US.UTF-8 is used during database creation (recommended by midPoint docs) the ordering
of the select above should be case-insensitive.
Collation used by default for each database can be determined like so:
select datname, datcollate from pg_database; -- returns among others: midpoint,en_US.UTF-8
Funny enough, this default collation name may not be a valid name for
clause but for
en_US.UTF-8 it seems to act the same as
collate "en_US.utf8" (or
The list of valid collation names can be obtained from
select * from pg_collation;
As explained in the collation documentation,
there is a default collation (
collprovider = 'd'), libc based collations (
d) and ICU collations
i) if compiled with it (PG 13 on Ubuntu contains these too).
|ICU collations can’t be used as database defaults.|
It is possible to create tables or single columns with non-default collation.
To list columns with different collation one can use this select (with or without
filter, select based on this answer):
select table_schema, table_name, column_name, collation_name from information_schema.columns where collation_name is not null and table_schema = 'public' order by table_schema, table_name, ordinal_position;
If query is used with custom
If case-sensitive locale is used when case-insensitive behavior is expected it may result in
The old repository uses