Support Postgres custom type

[zhenwenc]

Recently have a requirement on using custom Enum type on Postgres database, but when I trying to insert test data with DbSetup, the following exception thrown:

Cause: org.postgresql.util.PSQLException: ERROR: column "animal_type" is of type currency but expression is of type character varying

The reason is that, to insert data with JDBC prepared statement, the query needs to be in form: INSERT INTO pet (pet_id, pet_type, name) VALUES (?, ?::animal_type, ?);

Please refer this site for more details: http://www.gotoquiz.com/web-coding/programming/java-programming/convert-between-java-enums-and-postgresql-enums/

By digging into the source code, the only way I found to solve this problem is hacking the Insert#execute method (https://github.com/Ninja-Squad/DbSetup/blob/f9f9fd5fbf8c98c50d37dde7d0ac793a3176b4ec/src/main/java/com/ninja_squad/dbsetup/operation/Insert.java#L257)

I am wondering, is there any other convenient way to achieve this?

Thanks in advance!

[zhenwenc]

Thanks for the nice design, I found a workaround for this issue. Here is the solution in case anyone facing the same problem:

import java.sql.{ParameterMetaData, SQLException, Types}
import com.ninja_squad.dbsetup.bind.{Binder, Binders, DefaultBinderConfiguration}

object CustomTypeBinder {
  private[db] val OtherType = Types.OTHER
  private[db] val CustomTypeRE = "(.*?)::(.*?)".r
}

class MyBinderConfiguration extends DefaultBinderConfiguration {
  import CustomTypeBinder._

  override def getBinder(metadata: ParameterMetaData, param: Int) =
    Option(metadata).map(m ⇒ m.getParameterType(param)) match {
      case Some(`OtherType`) ⇒ new CustomBinder
      case Some(_)           ⇒ super.getBinder(metadata, param)
      case None              ⇒ Binders.defaultBinder
    }
}

class CustomBinder extends Binder {
  import CustomTypeBinder._

  @throws(classOf[SQLException])
  override def bind(stmt: java.sql.PreparedStatement, param: Int, value: AnyRef) =
    value match {
      case x: String ⇒ x match {
        case CustomTypeRE(v, t) ⇒ stmt.setObject(param, newPGobject(v, t))
        case _                  ⇒ // fallback to default use binder
      }
      case _ ⇒ // fallback to default use binder
    }

  private def newPGobject(value: String, valueType: String) = {
    val obj = new PGobject
    obj.setType(valueType)
    obj.setValue(value)
    obj
  }
}

Sample usage:

val initTable = sequenceOf(
    sql("DROP TABLE IF EXISTS T CASCADE"),
    sql("CREATE TABLE T (id UUID)"),
    Insert.into("T")
      .columns("id")
      .values("bfb9ef37-1caf-4d51-9d58-1e30f19c3b8c::uuid")
      .build
    )

The code is written in Scala.

[panser]

@zhenwenc , can you explain what to do further with this code? how to include/replace original dbsetup code. And it would be great if you add java version for it.

[zhenwenc]

Hi @panser , the value of initTable variable in my sample above is an instance of Operation (a composite operation in this case), where you could pass in a BinderConfiguration instance while executing the operation. You could either use the Operation#execute method, or like what I am doing (see the sample here):

DbSetup dbSetup = new DbSetup(new DataSourceDestination(dataSource), operation, new MyBinderConfiguration());
dbSetup.launch();

Let me know if you need more help.

[slowteetoe]

I also ran into this situation, with a jsonb field... Here's roughly the java equivalent of what @zhenwenc did - https://github.com/slowteetoe/DbSetup/commit/bf8cd6c38123d9eaa98b6be69bdc822404a91168 (I didn't create a PR because it adds a dependency on postgres, don't know how you feel about that)

[zhenwenc]

@slowteetoe Thanks very much for the Java version. A suggestion, I would like to recommend making a wrapper for DBSetup to use with this custom binder, rather than adding it to the DBSetup library as this binder is very Postgres specific.

Just an example, here is what my code looks like:

"support json column value" in {
 val initTable = sequenceOf(
    sql("DROP TABLE IF EXISTS T CASCADE"),
    sql("CREATE TABLE T (id INT, da JSON)"),
    Insert.into("T")
      .columns("id", "da")
      .values("1", """{ "d": "John", "a": { "desc": "milk", "quality": 4 } }::json""")
      .build
  )
  // get a db connection, configure DBSetup instance, execute the operation
  dbSetupManager.execute(initTable)

  val expected = DbTable(
    "T",
    ("id", "da"),
    ("1", """{ "d": "John", "a": { "desc": "milk", "quality": 4 } }""")
  )
  // verify the data actually persisted in the database
  dbSetupManager.verifyPersisted(expected)
}

[mnafshin]

A Java sample code (thanks to @zhenwenc): let's suppose you have an enum "ServiceType" and by using following function in your writer class, and adding the following classes to the project, you can write ServiceType enum in the Postgres DB.

void customExecuteOperations(final Destination destination,
    final Operation... setupOperations) {

    final Operation operationSequence;
    if (setupOperations.length > 1) {
      operationSequence = sequenceOf(setupOperations);
    } else {
      operationSequence = sequenceOf(setupOperations[0]);
    }
    new DbSetup(destination, operationSequence, CustomBinderConfiguration.INSTANCE).launch();
  }

class CustomBinderConfiguration extends DefaultBinderConfiguration {

  public static final CustomBinderConfiguration INSTANCE = new CustomBinderConfiguration();

  private static final Binder serviceTypeBinder = new ServiceTypeBinder();

  @Override
  public Binder getBinder(ParameterMetaData metadata, int param) throws SQLException {
    if (param == 5) { //in my case 5th parameter was of type ServiceType enum
      return serviceTypeBinder;
    }
    return super.getBinder(metadata, param);
  }
}

class ServiceTypeBinder implements Binder {

  @Override
  public void bind(PreparedStatement statement, int param, Object value) throws SQLException {
    if (param == 5) { //in my case 5th parameter was of type ServiceType enum
      var serviceType = ServiceType.valueOf(value.toString());
      statement.setObject(param, serviceType, Types.OTHER);
    } else {
      statement.setObject(param, value);
    }
  }
}

[wamalalawrence]

Thanks @mnafshin, will give it a try!

[esfomeado]

Any plans to support this?

[jnizet]

@esfomeado I don't plan to add binders that are specific to custom PostgreSQL types, if that's what you're asking. But you can specify a custom binder for specific columns of an insert (see https://dbsetup.ninja-squad.com/apidoc/2.1.0/com/ninja_squad/dbsetup/operation/Insert.Builder.html#withBinder-com.ninja_squad.dbsetup.bind.Binder-java.lang.String...-), or even a custom BinderConfiguration that would use the parameter metadata to always use a custom binder for a given column type, or name (see https://dbsetup.ninja-squad.com/apidoc/2.1.0/com/ninja_squad/dbsetup/bind/BinderConfiguration.html and https://dbsetup.ninja-squad.com/apidoc/2.1.0/com/ninja_squad/dbsetup/DbSetup.html#DbSetup-com.ninja_squad.dbsetup.destination.Destination-com.ninja_squad.dbsetup.operation.Operation-com.ninja_squad.dbsetup.bind.BinderConfiguration-) This custom BinderCOnfiguration can simply delegate to the default binder configuration (https://dbsetup.ninja-squad.com/apidoc/2.1.0/com/ninja_squad/dbsetup/bind/DefaultBinderConfiguration.html#INSTANCE) for all the types/columns, except the ones you want to handle with your custom binder.