Open pnorman opened 6 years ago
This is related to #275
I did some more thinking and investigations. I started from a few assumptions
Some background information
:foo
parameters. pgx isn't one of them.geom && NULL
is false, so exits quickly. It's also semantically correct, as the bounding box is either unknown or does not exist.I recommend the following:
On startup do the following per layer
sql := strings.Replace(l.sql, ":bbox", "$1", -1)
sql := strings.Replace(sql, ":zoom", "$2", -1)
// etc for any other tokens in the future
prepare := `PREPARE "tegola_` + strings.Replace(l.name, `"`, `\"`, -1) + `(geometry(polygon, ` + l.srid + `), int) AS`
sql := prepare + "\n" + sql
// SQL instead of PrepareEx to allow for typmods
p.pool.Exec(sql);
Then for type sniffing, if required
// Not sure how to handle nulls in Go - pgtype is probably what's needed.
rows, err := p.pool.Query("tegola_" + strings.Replace(l.name, `"`, `\"`, -1), NullGeom, NullZoom)
if err != nil {
return err
}
Then do magic with FieldDescription
Later, when running the query, run p.pool.Query("tegola_" + strings.Replace(l.name,
",
\", -1), RealBbox, RealZoom)
The following seem relevant for PostGIS types: https://github.com/cridenour/go-postgis https://godoc.org/github.com/mc2soft/pq-types
But I figure others know Go + PostGIS better than I do, so I didn't dig extensively ;)
Tokens are well-established, with Mapnik making use of them too. All the same, they're still a hack that relies on parsing SQL as text and writing SQL carefully. SQL injection isn't a security worry here, since it's intentional the style author can write arbitrary SQL, but the same flaws with manipulating SQL as text lead to bugs when writing queries.
My inclination is towards prepared statements
The server would execute something like
This would be generated by taking the middle line from the style and surrounding it with the
PREPARE
and;
.water
would be something derived from the layer and provider names and be unique.A prepared statement could be made with pgx's func (*Conn) PrepareEx but requires knowing the oid of the geometrytype and might not allow specifying typmods.
func (*Conn) Exec can take a prepared statement name, e.g.
Issues with prepared statements might be
Cached query plans. These are normally good, but a query used over the range of zooms where it goes between sequential and index scans would have problems. It might be necessary to prepare a query for each zoom.
Interactions with pgbouncer and pgpool. These can work with prepared statements, but something like PostGIS data type OIDs might change across them, leading to problems with PrepareEx.