search

t-rex-tileserver / t-rex

t-rex is a vector tile server specialized on publishing MVT tiles from your own data
https://t-rex.tileserver.ch/
MIT License
545 stars 68 forks source link

Use quoted identifiers in generated SQL #311

Open robert-werner opened 5 months ago

robert-werner commented 5 months ago

I've tried to deploy a T-Rex server from OSM data. I've generated a config with genconfig command with PostgreSQL. I started to get PostgreSQL's syntax errors, so I've investigated through the code, and I discovered that replacing the braces with nothing is creating these errors, so I've decided to delete that code.

Tests are passed.

pka commented 4 months ago

Thanks for your PR! I really like the feature of having readable SQL in the generated configuration. A better solution would probably be checking that no problematic characters are included before removing the quotes.

robert-werner commented 4 months ago

'problematic characters' are the reserved words of Postgre, so the 'better solution' is not the solution at all,

Just try to generate .toml from OSM2PGSQL imported database.

pka commented 4 months ago

Which problematic table or column names does osm2pgsql create?

I'm still in favor of letting basic names unquoted. Rule proposal (subset of https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS): "SQL identifiers must begin with a letter a-z or an underscore (_). Subsequent characters in an identifier can be a-z, underscores or digits (0-9)"

joto commented 4 months ago

Which problematic table or column names does osm2pgsql create?

Of course it depends on the configuration, but typical osm2pgsql configs generate column names such as addr:housenumber from the tags of the same name. Not a great idea, but we are pretty much stuck with that.

robert-werner commented 4 months ago

Which problematic table or column names does osm2pgsql create?

Of course it depends on the configuration, but typical osm2pgsql configs generate column names such as addr:housenumber from the tags of the same name. Not a great idea, but we are pretty much stuck with that.

That's why T-Rex doesn't need the readability instead of functionality. If anyone wants to get readable SQL's, he may be pleased with dedicated option.