#179 PostgreSQLDataTypeFactory and OID Data Type

[mfrechePgest]

Hi,

Not sure I'm in the right place to post this, but here I am.

I've encountered some problems using Dbunit 2.4.8 on a PostgreSQL database, with some "oid" columns, where Dbunit apparently don't know how to store bytes.

I managed to get it working with a few modifications.
I added a PostgreSQLOidDataType (see attachment) and a new condition in PostgresqlDataTypeFactory.createDataType :
if (sqlType == Types.BIGINT && "oid".equalsIgnoreCase(sqlTypeName)) {
return new PostgreSQLOidDataType();
}

I'm not really confident in my code, as you can see in my "ugly" comments on autocommit management.
But it works.

Could anyone include this kind of feature in a next dbunit version ?

Thanks

[Hou Tianze]

Thanks, this helped me in migrating from PostgreSQL to MySQL.

[Jeff Jensen]

Thanks for the comment. How did a PostgreSQL change help your migration in MySQL? Did you adapt this for MySQL or what was the change?

[Hou Tianze]

That was some fast reply :)

The scenario is like this:
I was trying to migrate the database from PostgreSQL to MySQL using some tools/scripts that come with the proudct (it's actually just calling dbunit from ant). However, after migration, the product won't work properly with the migrated MySQL database. After some investigation, I found that the original PostgreSQL DDL is using OID for some table columns, and a default export will generate BigInt numbers as the contents for these columns, so after importing, these values are wrong in the detination database. After some googling, I adopted the changes from the OP (and made some minor modifications) and was able to export the desired data from PostgreSQL. (The actual data is actually a bit more complicated - These OIDs map to either Text or Blob, so in the end, I had to decide which type of data (String or byte[]) to return from getSqlValue() by checking the table/column names. So in my case, it was very dirty hack).

[Jeff Jensen]

Thank you for the detailed info, helpful to understand and glad it worked!

Do I understand this correctly - the OP's code interprets the OID as BIGINT, and your situation interpreted the OID as text or blob? If so, then the OP's code should be "PostgreSQLOidBigIntDataType' and we can then have "PostgreSQLOidTextDataType" and "PostgreSQLOidBlobDataType", and adjust PostgresqlDataTypeFactory accordingly (?).

It seems this is a useful feature for dbUnit. In order to add this feature, we need a test(s) to prove it works. Do you have time to make tests for it based on your usage and improve it?

Did you find the autocommit setting was needed? I wonder why the OP needed it.

Thanks again for your help.

[Hou Tianze]

You're welcome and thanks for following up this issue.

I'm afraid your second paragraph isn't totally accurate :) - Let me clarify: My situation is a very special case (I'd say the bizzare problem was introduced by the DDL creator), so for dbunit's sake, we can totally forget about it (the Text type). I think the OP's naming of the file (PostgreSQLOidDataType.java) is correct - PostgreSQL's OID should be mapped to bytea/blob (byte[] in Java) only, and we only need this one file to handle it. The BigInt registration part is due to the fact that, using JDBC, OID data will be read as with type BigInt (the java enum (-5?)) and type name "OID".

The autocommit setting part is needed (otherwise, Postgres SQL exception will be thrown), in fact, we need more - not only the write part (setSqlValue), but also the read part (getSqlValue, surprisingly, if not changing autocommit to false, exeception will also arise).

I will try to wrap my modifications and upload them (is there any better way like a pull request instead of uploading attachments here?) hopefully tomorrow or before end of this week, to give you more information about what I mean. For the test cases, let me look into it how to write one first.

One possible pitfall is that with this source file added, dbunit will need PostgreSQL JDBC jar to compile, but since we already need Oracle's JDBC jar to compile, this may or may not be a big issue (your call).

This link maybe useful to understand PostgreSQL OID (btw, I read somewhere that the use of OID might be generally discouraged):
https://www.postgresql.org/docs/9.4/static/lo-funcs.html

[Jeff Jensen]

your second paragraph isn't totally accurate
autocommit setting part is needed

Thanks for clarifying everything! Much appreciated.

I will try to wrap my modifications and upload them (is there any better way like a pull request instead of uploading attachments here?)

Great! Yes, see the "Merge Request" SF feature. Fork dbUnit, make your changes in your fork, adjust commits (messages, squashes, etc.), then use the MR option.

The correct package for the new class is org.dbunit.ext.postgresql. Use the Oracle ones as examples too.

test cases, let me look into it how to write one first.

For this "type" class, rather simple - see the PostgresqlDataTypeFactoryTest for some examples.
See the Oracle ones for ideas too. Don't hesitate to ask questions...
Really appreciate you doing this!

dbunit will need PostgreSQL JDBC jar to compile

No problem, it's even easier than Oracle because it's available in Maven Central.

[Hou Tianze]

Thanks for the pointers, I made a merge request here (with comments on things to note about this change):
https://sourceforge.net/p/dbunit/code.git/merge-requests/31/

[Jeff Jensen]

Wonderful! Thank you very much. I will look at it in the next few days.

[Jeff Jensen]

Just ensuring you saw my questions and comments on the merge request...

[Hou Tianze]

Thanks for notifying. Somehow (maybe due to my account settings), I never receive any email notification after your made your comments in the pull-requests... I will reply your comments (and probably ask questions) there and make some changes neccessary. I'm a bit busy in the next two days, but probably should get back to you before end of this weekend.

[Hou Tianze]

I just post a reply with some questions in the merge-request, just in case you don't receive notification there.

[Jeff Jensen]

Thanks, I have notifications on. And letting you know I replied, in case you don't!

[Jeff Jensen]

Merged, thank you for help and merge request!

[Hou Tianze]

Glad if it's of any help 😀