search

telefonicaid / fiware-cygnus

A connector in charge of persisting context data sources into other third-party databases and storage systems, creating a historical view of the context
https://fiware-cygnus.rtfd.io/
GNU Affero General Public License v3.0
64 stars 104 forks source link

PostgreSQL sink does not create table when entity id contains special characters #1977

Open jlsiewert opened 4 years ago

jlsiewert commented 4 years ago

It appears that cygnus-ngsi and its PosgreSQL sink can't create a postgres table when the entity has an id that contains the - chracter, e.g. urn:ngsi-ld:Motion:001 as recommended in the Fiware tutorials.

I setup a simple stack with Orion, an IoT-Agent as well as Cygnus-NGSI and a PostgreSQL database.

However, PostgreSQL reports the error

2020-11-10 08:39:33.404 UTC [66] ERROR: syntax error at or near "-" at character 44
2020-11-10 08:39:33.404 UTC [66] STATEMENT: CREATE TABLE IF NOT EXISTS openiot.urn_ngsi-ld_motion_001_thing (recvTimeTs text,recvTime text,fiwareServicePath text,entityId text,entityType text,attrName text,attrType text,attrValue text,attrMd text)

The error does not occur when a different entity id is used. Data is then persisted as expected.

Cygnus should replace non-alphanumeric characters in entity ids with _, however, that does not appear to be happening. Is this just a bug or do I need some sort of name mapping to solve this?

fgalan commented 3 years ago

Related question in SOF: https://stackoverflow.com/questions/64767280/fiware-cygnus-postgresql-sink-does-not-create-table-when-entity-id-contains-spec

fgalan commented 3 years ago

The limitation should be at least documented. Maybe every sink should have a "Syntax restrictions" section (or a better name :) to describe this kind of things, starting with Postgresqt sink based on this issue.

jlsiewert commented 3 years ago

This limitation should probably be documented in this project as well as in the getting started guide.

Looking at the current encoding this leaves the dash in a weird space, especially because even Unicode seems to be supported in entities id. Considering that it is recommended to append the literal urn:ngsi-ld: in front of each id it feels like this sink should handle dashes and colons as well.

fgalan commented 3 years ago

Considering that it is recommended to append the literal urn:ngsi-ld: in front of each id it feels like this sink should handle dashes and colons as well.

Maybe the authors of that document should change the recommendation to urn:ngsild.