Safe Haskell | Safe-Inferred |
---|---|
Language | Haskell2010 |
Postgres SQL Rename Identifiers
- Prefix table names with underscores to avoid issues where column names and tables conflict.
This can happen because we give columns and tables the name
root
for some reason, and that can trip uprow_to_json
. See https://github.com/PostgREST/postgrest/issues/993#issuecomment-340377813. An alternative solution would be to not create aTableAlias
with the nameroot
, but that seemed a bit complicated for me to do at the time. - Bypass the Postgres limitation of truncating identifiers to 63 characters long by prepending they identifier's md5 hash when they are longer than 63 characters.
We do both operations in the same traversal for performance reasons, but a simpler
implementation of (1) would be transformBi prefixHash
from the uniplate or the
generic-plate package.
See Postgres docs: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
API
renameIdentifiers :: Select -> Select Source #
Prefix table names with undescores and rename long identifiers.
renameIdentifiersSelectWith :: SelectWithG Select -> SelectWithG Select Source #
prefix table names with undescores and rename long identifiers.
renameIdentifiersSelectWithTopLevelCTE :: SelectWithG TopLevelCTE -> SelectWithG TopLevelCTE Source #
prefix table names with undescores and rename long identifiers.
Prefix long identifiers
We are traversing the query transform Identifier
s in a query that are
longer than 63 characters by prefixing them with their md5 hash.
This works because:
- Database table references and column references also use
Identifier
, but they cannot be more than 63 characters long, so we will never transform those cases. - The md5 hash is a 32 characters long deterministic representation of the identifier, so even when truncated by postgres, it will always be enough to identify the identifiers.
- It is possible in theory for to identifiers to produce the same hash, but extremely unlikely and I don't think we'll ever run into such a case.
Note that we could in theory replace the identifier with a hash, but we prefix the hash instead for our benefit as developers - if we need to read the query at some point we can look at the rest of the identifier ignoring the hash for a readable representation.