Feature Enhancement: support for postgres enums

[DALDEI]

Current project I am using postgres for the first time. Having used Mysql Enums in the past, and being a fan of typed data -- I ended up with a postgres style enum. Turns out its not quite so easy to use -- in the db, jdbc, sql2o or vok-orm But not impossible --

This is what I did to make it work for me -- there may well be much better ways. First off -- the naive thing to assume is "It just works" -- the error one gets attemping to put a Enum value into a query or insert is not obvious -- it is a side effect of postgress exposing somewhat the fact that enums are user defined types but not enough to use them :) So you get an error about the expression expecting a user defined type but being given a varying. ( google saved the day again )

To work around -- one needs to explicitly add a cast to the SQL. I used a custom SQL (thank you for assuming that one needs to do that sometimes !!!) like this: This is a simple generic function that updates 1 property of a kotlin class into 1 field in a DB table. Called like

    field = MyEnumValue
    update( ::field )  // for normal fields
    update(::field, "::my_user_defined_type" ) // for enums and other convertable user types

  fun update(property : KMutableProperty0<*>,cast:String="" /* for status enum */) = db {
    con.createQuery("UPDATE devices SET ${property.name} = :value${cast} WHERE id = :id")
      .addParameter( "value" , property.getter.call() )
      .addParameter("id", id )
      .executeUpdate()

The relevant bit is the "${cast}" which will insert the "::my_user_defined_type" to create ":value::my_user_defined_type"

I wasn't sure how well vok or sql2o would handle this expressing given that it includes a ":" -- but it did.

To READ the DB with the enum I made a custom converter -- I didnt put the effort into making it fully generic -- the reified types and kotlin enum reflection is a bit subtle and under-implemented. SO I hard coded the single enum "ConversionState" -- a creative author should be able to extend this genericly

   VokOrm.dataSourceConfig.apply {
  ... configuration code ...
... then this call 
   registerEnumConverter( object : EnumConverterFactory {
      override fun <E : Enum<out Enum<*>>?> newConverter(enumClass : Class<E>?) : Converter<E> =
        object : Converter<E> {
          override fun toDatabaseParam(v : E) : Any = (v as ConversionState).name

          override fun convert(v : Any?) : E = ConversionState.valueOf(v.toString()) as E

        }
    })
}

[DALDEI]

Would be a nice kotlin feature to allow TAM's as well as SAM's (Two Abstract Methods) -- then one might be able to avoid the verbose amounts of casting and signatures :)

[mvysny]

Hi, thanks for letting me know! If your entity is plagued by this bug, you may get around this bug by simply overriding that entity's save() and create() methods.

I've tried to create the ENUM type and it works for all databases but Postgres :-( The need for altering the SQL is actually horrible - it should work with String enum constant like with MySQL and H2. It's really infuriating and sloppy of the PostgreSQL driver, since I have to also fix all filters and finders. I'm going to open a bug report for the PostgreSQL driver.

I wonder what I should do. I'm thinking of having an annotation @NativeType added to the enum (so that I know the database enum type name), for example @NativeType("marital_status") enum class MaritalStatus { ... }. The annotation would only be applied for PostgreSQL, and the appropriate cast would be inserted into the UPDATE/INSERT statement.

[mvysny]

Opened a bug report https://github.com/pgjdbc/pgjdbc/issues/1420 . Let us wait what the PostgreSQL guys will think.

[mvysny]

A workaround is to force PostgreSQL driver to guess type, by adding the stringtype=unspecified to your URL:

jdbc:postgresql://localhost:5432/postgres?stringtype=unspecified

Another possible solution would be to properly override Sql2o PostgresQuirks.setParameter() functions, to use the type of java.sql.Types.OTHER. However, stringtype=unspecified may be more than enough. What do you think @DALDEI ?

[DALDEI]

I personally hate adding connection properties to the connection string for jdbc -- it tends to muck up libraries/infrastructure that makes simplifying assumptions (like creating the connection string from host + user + pass). But if setting a connection property works -- that's good enough. FYI: How Spring handles this is with a custom annotation (similar to Jackson Json annotations). Postgres is just weird. I really want to like postgres but - it just pokes me in the eye every time I try. I'd be more 'forgiving' if it was not attempting to 'be a RDBMS' - the standards variance across the industry for SQL and JDBC is bad enough -- for products that actually try to be standards compliant where reasonable -- oh well moan/complain -- what do you expect for 'free' :)

( hint hint paid/commercial postgres providers -- its a hard sell when its free -- maybe you might want to think about some attempt at standards compliance if you want to make a business out of it )