PostgreSQL sink creates table when entity id contains special characters

[kajal583]

This PR fixes issue#1977 i.e case1 and case2 CASE 1: Encoding is enabled Postgresql sink creates table for all datamodel. But found bug in table name of some data model as shown below:

Data model	Table name	Expected Table name
dm-by-attribute	x002fkajalxffffurnx003angsix002dldxffffx0052oomxfffftemperature	x002fkajalxffffurnx003angsix002dldxffffroomxfffftemperature
dm-by-entity	x002fkajalxffffurnx003angsix002dldxffffx0052oom	x002fkajalxffffurnx003angsix002dldxffffroom
dm-by-entity-database	x002fkajalxffffurnx003angsix002dldxffffx0052oom	x002fkajalxffffurnx003angsix002dldxffffroom
dm-by-fixed-entity-type	x0052oom	room
dm-by-fixed-entity-type-database	x0052oom	room
dm-by-entity-database-schema	x002fkajalxffffurnx003angsix002dldxffffx0052oom	x002fkajalxffffurnx003angsix002dldxffffroom
dm-by-fixed-entity-type-database-schema	x0052oom	room

CASE 2: Encoding is disabled In this case, postgresql sink create table only for below data models:

• dm-by-fixed-entity-type-database-schema
• dm-by-entity-type-database-schema
• dm-by-fixed-entity-type-database
• dm-by-fixed-entity-type
• dm-by-entity-type-database
• dm-by-entity-type
• dm-by-service-path

and for other data models (dm-by-entity,dm-by-attribute,dm-by-entity-database,dm-by-entity-database-schema) it does not create tables.

[fgalan]

Thank you for your contribution!

We hope we can provide feedback on it in a short time

[mapedraza]

@Kajal-NEC Please, could you provide the entity name, entity type, fiware-service and fiware-servicepath header you are using to create the DB for each datamodel?

[kajal583]

Please find below details:

• entity name=urn:ngsi-ld
• entity type: Room
• fiware-service= kajal
• fiware-servicepath=/kajal

[mapedraza]

Organising a bit the information:

Parameters used

entity name=urn:ngsi-ld
entity type: Room
fiware-service= kajal
fiware-servicepath=/kajal

Datamodels analysis:

dm-by-attribute Datamodel name: servicepath_entityname_entitytype_attribute

x002fkajalxffffurnx003angsix002dldxffffx0052oomxfffftemperature x002fkajalxffffurnx003angsix002dldxffffroomxfffftemperature

Differences: R capital (x0052 instead of r)

dm-by-entity Datamodel name: servicepath_entityname_entitytype

x002fkajalxffffurnx003angsix002dldxffffx0052oom x002fkajalxffffurnx003angsix002dldxffffroom

Differences: R capital (x0052 instead of r)

dm-by-entity-database Datamodel name: servicepath_entityname_entitytype

x002fkajalxffffurnx003angsix002dldxffffx0052oom x002fkajalxffffurnx003angsix002dldxffffroom

Differences: R capital (x0052 instead of r)

dm-by-fixed-entity-type Datamodel name: entitytype

x0052oom room

Differences: R capital (x0052 instead of r)

dm-by-fixed-entity-type-database Datamodel name: entitytype

x0052oom room

Differences: R capital (x0052 instead of r)

dm-by-entity-database-schema Datamodel name: servicepath_entityname_entitytype

x002fkajalxffffurnx003angsix002dldxffffx0052oom x002fkajalxffffurnx003angsix002dldxffffroom

Differences: R capital (x0052 instead of r)

dm-by-fixed-entity-type-database-schema Datamodel name: entitytype

x0052oom room

Differences: R capital (x0052 instead of r)

Conclusion

As far as I can see, this PR only fix capitals problems. @Kajal-NEC How this PR approach the issue #1977 regarding the - sign?

[kajal583]

Organising a bit the information:

Parameters used

entity name=urn:ngsi-ld
entity type: Room
fiware-service= kajal
fiware-servicepath=/kajal

Differences: R capital (x0052 instead of r)

Conclusion

As far as I can see, this PR only fix capitals problems. @Kajal-NEC How this PR approach the issue #1977 regarding the - sign?

@mapedraza , Case 2 mentioned above in PR description is regarding issue #1977. When encoding is disabled and entityID containes special character (i.e '-'), postgresql sink does not create table for some data model shown in below table. Parameter used: entity name=urn:ngsi-ld entity type: Room fiware-service= kajal fiware-servicepath=/kajal

DATA MODEL	Table_name before applying this patch	Table name after applying this patch
dm-by-fixed-entity-type-database-schema	room	room
dm-by-entity-type-database-schema	kajal_room	kajal_room
dm-by-fixed-entity-type-database	room	room
dm-by-fixed-entity-type	room	room
dm-by-entity-type-database	kajal_room	kajal_room
dm-by-entity-type	kajal_room	kajal_room
dm-by-service-path	kajal	kajal
dm-by-entity-database-schema	Table not created	kajal_urn_ngsi_ld_room
dm-by-entity-database	Table not created	kajal_urn_ngsi_ld_room
dm-by-entity	Table not created	kajal_urn_ngsi_ld_room
dm-by-attribute	Table not created	kajal_urn_ngsi_ld_room_temperature

[fgalan]

We wonder how this PR would affect to existing deployments (i.e. backward compatibility). Current databases using table names like x0052oom would be potentially impacted if this PR is applied.

In order to know the scope of the change, are your modifications changing always the table names? Or only when enable_encoding is enabled? Could you add a enable_encoding column to your tables (considering both cases false and true)?

Thanks!

[kajal583]

We wonder how this PR would affect to existing deployments (i.e. backward compatibility). Current databases using table names like x0052oom would be potentially impacted if this PR is applied.

In order to know the scope of the change, are your modifications changing always the table names? Or only when enable_encoding is enabled? Could you add a enable_encoding column to your tables (considering both cases false and true)?

Thanks!

CASE 1 from PR discription: When enable_encoding=true, postgresql sink creates table. But when there is CAPITAL letter in tableName then it gets replaced by some code like x0041, x0042, x0043, x0044 etc as shown in below table: enable_encoding=true Parameter used: entity name= urn:ngsi-ld entity type= Room fiware-service= kajal fiware-servicepath= /kajal

Datamodel name	Expected table name as per Documentation	Table Name before applying PR	Table Name after applying PR	Differences
dm-by-attribute	x002f{svcPath}xffff{entityId}xffff{entityType}xffff{attribute}	x002fkajalxffffurnx003angsix002dldxffffx0052oomxfffftemperature	x002fkajalxffffurnx003angsix002dldxffffroomxfffftemperature	R capital (x0052 instead of r)
dm-by-entity	x002f{svcPath}xffff{entityId}xffff{entityType}	x002fkajalxffffurnx003angsix002dldxffffx0052oom	x002fkajalxffffurnx003angsix002dldxffffroom	R capital (x0052 instead of r)
dm-by-entity-database	x002f{svcPath}xffff{entityId}xffff{entityType}	x002fkajalxffffurnx003angsix002dldxffffx0052oom	x002fkajalxffffurnx003angsix002dldxffffroom	R capital (x0052 instead of r)
dm-by-fixed-entity-type	{entityType}	x0052oom	room	**R capital (x0052 instead of r)
dm-by-fixed-entity-type-database	{entityType}	x0052oom	room	R capital (x0052 instead of r)
dm-by-entity-database-schema	x002f{svcPath}xffff{entityId}xffff{entityType}	x002fkajalxffffurnx003angsix002dldxffffx0052oom	x002fkajalxffffurnx003angsix002dldxffffroom	R capital (x0052 instead of r)
dm-by-fixed-entity-type-database-schema	{entityType}	x0052oom	room	R capital (x0052 instead of r)
dm-by-entity-type-database-schema	x002f{svcPath}xffff{entityType}	x002fkajalxffffroom	x002fkajalxffffroom	No change
dm-by-entity-type-database	x002f{svcPath}xffff{entityType}	x002fkajalxffffroom	x002fkajalxffffroom	No change
dm-by-entity-type	x002f{svcPath}xffff{entityType}	x002fkajalxffffroom	x002fkajalxffffroom	No change
dm-by-service-path	x002f{svcPath}	x002fkajal	x002fkajal	No change

Case 2 From PR discription: Case 2 mentioned in PR description is fix for #1977. Issue #1977 occurs when enable_encoding=false Describe issue #1977: When enable_encoding=false and entityID containes special character (i.e '-'), postgresql sink does not create table for some data model shown in below table. enable_encoding=false Parameter used: entity name= urn:ngsi-ld entity type= Room fiware-service= kajal fiware-servicepath= /kajal

DATA MODEL	Expected table name as per Documentation	Table_name before applying this patch	Table name after applying this patch
dm-by-fixed-entity-type-database-schema	{entityType}	room	room
dm-by-entity-type-database-schema	{servicepath_entitytype}	kajal_room	kajal_room
dm-by-fixed-entity-type-database	{entitytype}	room	room
dm-by-fixed-entity-type	{entitytype}	room	room
dm-by-entity-type-database	{servicepath_entitytype}	kajal_room	kajal_room
dm-by-entity-type	{servicepath_entitytype}	kajal_room	kajal_room
dm-by-service-path	{entitytype}	kajal	kajal
dm-by-entity-database-schema	{servicepath_entityname_entitytype}	Table not created	kajal_urn_ngsi_ld_room
dm-by-entity-database	{servicepath_entityname_entitytype}	Table not created	kajal_urn_ngsi_ld_room
dm-by-entity	{servicepath_entityname_entitytype}	Table not created	kajal_urn_ngsi_ld_room
dm-by-attribute	{servicepath_entityname_entitytype_attribute}	Table not created	kajal_urn_ngsi_ld_room_temperature