Provide documentation on mapping between postgres objects and Egeria objects & possible errors?

[planetf1]

I imported a 'flight booking' database from https://postgrespro.com/education/demodb (medium) This has a table called 'bookings'. If I inspect this with Egeria 'REX' I get:

Entity : boarding_passes
Type : RelationalColumn
Version : 1
Status : ACTIVE
Properties :
additionalProperties :
character_maximum_length :4
character_octet_length :16
column_name :seat_no
data_type :character varying
dtd_identifier :4
identity_cycle :NO
is_generated :NEVER
is_identity :NO
is_nullable :NO
is_self_referencing :NO
is_updatable :YES
ordinal_position :4
table_catalog :postgres
table_name :boarding_passes
table_schema :bookings
udt_catalog :postgres
udt_name :varchar
udt_schema :pg_catalog
allowsDuplicateValues :
displayName :boarding_passes
isDeprecated :
isNullable :
length :0
maxCardinality :0
minCardinality :0
minimumLength :0
orderedValues :
position :0
qualifiedName :postgres::bookings::boarding_passes::seat_no
significantDigits :0
sortOrder :Ignore
Classifications :
Anchors :
anchorGUID :465f06c7-9f13-4a17-8475-ebab01081638
TypeEmbeddedAttribute :
dataType :character varying
isDeprecated :
qualifiedName :postgres::bookings::boarding_passes::seat_no:ColumnType
schemaTypeName :PrimitiveSchemaType
GUID : b6d03634-4323-4f35-9b71-8d7a1f0ef268
Home Repository :
metadataCollectionName : PostgresHostServer
metadataCollectionId : 3ea6b7ab-7008-4333-a879-cb42afb8c723

Whilst some attributes are more obvious than others, anyone using this connector (and Egeria) really needs to understand not just what they mean from an Egeria perspective, but also the translation from postgres.

(As this is very technical a clear mapping/config file with annotations used by the code would be clear too)

Some of the questions/points that follow may be resolved by docs (many in fact), but there may be a few technical glitches too

[planetf1]

Some questions also arise

• There is no 'boarding_passes' column defined in the postgres database, but there is a TABLE of that name
• What are 'character_maximum_length :4 & character_octet_length :16' and where do they come from? Is this unicode related? << UPDATE - now I see as it's a column, this is the varchar length, so answered
• This column (seat_no) is VARCHAR(4) so I understand the datatype of 'character varying'. I see dtd_identifier is '4' - is this meant to represent the length? << UPDATE - when looking at other colums it seems this is set the same as the ordinal, so we don't have the length?

• The display name is set to 'boarding passes' but as with the Entity name, I think it should be 'seat_no'

  Also if we have multiple postgres endpoints we are cataloguing (through multiple connections), will we not get an issue with the qualified name as it will no longer be unique - this is also a likely case?

[planetf1]

If I follow the link (in REX) to the table, this is also called 'boarding_passes' (but in this case it's correct).

• Why is the table's qualified name of the form qualifiedName :postgres.bookings.BASE.boarding_passes - what does BASE signify? And if this is a convention is there a reason we don't do similar for columns? (qualifiedName :postgres::bookings::boarding_passes::seat_no << is this in order to support both VIEWs and regular tables (BASE). That makes sense

[planetf1]

There is some variance in naming of entities. For example whilst tables (and in theory columns) use a simple name, DeployedDatabaseSchema uses a qualified name like postgres::postgres::bookings as does RelationalDBSchemaType

Some of this may relate to the Data Manager OMAS vs the connector?

[planetf1]

I notice the qualifiedName of the 'Database' Entity is simpl postgres - dup issue?

 'Entity : postgres
Type : Database
Version : 1
Status : ACTIVE
Properties :
additionalProperties :
collate :en_US.UTF-8
ctype :en_US.UTF-8
encoding :6
name :postgres
version :PostgreSQL 11.12 on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
databaseVersion :PostgreSQL 11.12 on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
deployedImplementationType :postgres
name :postgres
qualifiedName :postgres

[planetf1]

Column name is fixed by #84 Will re-review other mappings

[planetf1]

• As per https://egeria.odpi.org/open-metadata-implementation/access-services/data-manager/docs/relational-database-open-metadata-types.png#pagewidth should we create a 'Software Server' for the postgres database, or is this done elsewhere?
• Documentation is needed to understand the mapping
• A few minor questions about whether the qualified names used are correct and could be made more consistent
• all else looks good

[wbittles]

@planetf1 There hasn't been a mapping yet, those are information schema name value pairs.

[mandy-chessell]

The SoftwareServer is created through the IT Infrastructure OMAS (when written). This is if IT Infrastructure cataloguing is required. In this case, the SoftwareServerCapability (DatabaseManager) is typically also created through IT Infrastructure OMAS and linked to the software server.

The Database Integrator OMIS will automatically create the DatabaseManager SoftwareServerCapability if not already created. It has minimal properties (just the qualifiedName) and no attempt is made to connect it to a SoftwareServer. The IT Infrastructure OMAS can then be used to fill out the SoftwareServerCapability (and link it to a SoftwareServer) at a later date if needed.

[wbittles]

this is a question on the initial post

Egeria thinks that you have created a schema called bookings in the default postgres database. In that schema is a table called boarding_passes, with a column called seat_no

and it's this column that your actually looking at in REX , so we need to work out where it's going wrong, I assume your using the public schema ?

[wbittles]

https://github.com/odpi/egeria-database-connectors/issues/79#issuecomment-863961126

This is a symptom of the lack of mapping, it's a postgres database , imported from a postgres database, which is called postgres. This will be cleared up when mapping is resolved.

[wbittles]

https://github.com/odpi/egeria-database-connectors/issues/79#issuecomment-863949025 It was added to help debug an problem when querying for tables and views. It's actually the value that postgres uses to differentiate the two

[planetf1]

I don't think there's anything wrong now -- the initial issue was with the names of columns being incorrect - but this is now fixed in the connector. The rest looked good in REX. Also agree with the point about software server.

The two outstanding points I see are a) The qualified names assigned for (for example) Database is a short name ie 'postgres'. This seems very short -- in most places the qualified names are longer. Just wanted to check whether that was intended and whether we have any challenges with uniqueness especially as more databases & database servers are imported. b) Whether you feel it useful to add any further docs clarifying the mappings. If we think what we have is sufficient for now, that's fine we can close this sub-issue.

[github-actions[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed in 20 days if no further activity occurs. Thank you for your contributions.

[github-actions[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed in 20 days if no further activity occurs. Thank you for your contributions.

[github-actions[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed in 20 days if no further activity occurs. Thank you for your contributions.