integration of the geometry types seems to be incomplete #14

Open
cartazio opened this Issue Mar 18, 2012 · 8 comments

Projects

None yet

2 participants

@cartazio
  1. what is needed to expose those as having their own types for the purposes of using them as query parameter types and result types?

  2. I would be very happy to do the work, I just need someone to point me to what needs to be done to do the work

  3. thanks!
    -Carter

@lpsmith
Owner
lpsmith commented Mar 18, 2012

Are you looking to use PostgreSQL's geometry types, or PostGIS's?

Yeah, there is no specific support for either variety at this point. However you don't have to modify postgresql-simple to add support for them. You can create a suitable Haskell type to represent the geometry types, and then define Param and Result instances for your Haskell type. If you are using PostgreSQL's built in types, you can determine the type of a result using the OID and the BuiltinTypes module. If you are using PostGIS, then the types don't have a stable OID, so you should instead use the typename to determine the result type.

That said, I am interested in supporting geometry types "out of box" in postgresql-simple.

Unfortunately there is no support for binary formats at this point, which would be particularly helpful for PostGIS geometries. Unfortunately this would require deeper changes to postgresql-simple, though those issues do need to be dealt with eventually.

@cartazio

the basic geometry types would be a good enough start for now (for my purposes), I'm just starting out on a project which needs geometry queries, gis and a good db. (though I'll quickly probably really want to use postgis )

It looks like postgres makes it pretty clear which textual representations it will write each of the geometry types to, so it should be straight forward to just add those. I'd be up for having a go at that, though my two questions are then
1)I assume the newtypes should be added to the Types module? and the associated rendering / parsing defined in the Param and Result modules?
2) Since I might have an interest in using these features (and helping add other things), if you like I can have a go sometime over the next day or several adding these things.

so generally, the way things are currently setup, as long as I can formulate a query to get the typename and also the result(s) of a query, I can on my side do all the render/parse jiggery as is and it should work? awesome!

Every other db lib on hackage seems to leave even that much flexibility on the wayside.

@lpsmith
Owner
lpsmith commented Mar 18, 2012

Well, yes, I do make use of user defined types, so this sort of flexibility is essential to me (and in fact a major reason for abandoning HDBC). The Param and Result typeclasses were a major step in the right direction, and they were taken from mysql-simple. There is a further complication though, that at a protocol layer only type OIDs (integers) are passed, and not strings, and that user-defined types don't have stable type oids. Unfortunately Chris Done's pgsql-simple did not deal with this correctly, though I did incorporate some ideas from his code on this topic. So what I do is store the type name for non-builtin types in a per-connection cache; every time I see an unknown OID I consult the cache, and if it's not there then I query the metatables.

Well, like I said, you can define your geometry types in your own projects; for the purposes of including it as base functionality in postgresql-simple, I'd suggest putting it in a separate module, say something like Database.PostgreSQL.Simple.Geometry

You may want to take a look at the 0.1 branch; I've renamed the Result typeclass to FromField, and changed the result type as I wasn't satisfied with the (lack of) Alternative instances for Either, and didn't want to include an orphaned instance for a typeclass and type that both come from the base package. Also, I now keep track of multiple errors.

@lpsmith
Owner
lpsmith commented Mar 18, 2012

Oh, and I forgot to mention; it's important that your Result instances not retain a reference to the ByteString that convert is passed, as that will cause the entire LibPQ.Result to be retained. I really should update the documentation.

So for example, the convert method for ByteStrings calls Data.ByteString.copy. It's also not safe to retain a reference to a substring of the parameter without copying, due to the way ByteStrings work. I doubt this will be an issue for geometry types, but you never know.

@cartazio
cartazio commented Jun 2, 2012

sorry for disappearing, been a bit buried, but I'll get back to you on this stuff soon!
-Carter

@lpsmith
Owner
lpsmith commented Jun 2, 2012

Oh, no problem :)

@cartazio

@lpsmith i'm doing some new postgres hacking that involves needing to add new data types to postgres, in particular some simple enums.

Am I correct in understand that for user added types, I can check that I've the correct type on the postgres side via selecting the typename field from the Field record data type?
(its not super clearly documented anywhere aside from this languishing issue I think :) )

@lpsmith
Owner
lpsmith commented Mar 18, 2013

You are correct, that's the recommended way of type-checking postgresql types that do not have stable type OIDs, as describes all user-added types.

And no, it's not exactly a languishing issue; the documentation has already been improved on the 0.3 branch, but the 0.3 branch has been languishiing. I'm hoping to get it out this week though.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment