java.sql.Types NVARCHAR support?

General Discussion
1

I was curious if there were plans to support the java.sql.Types NVARCHAR value in Liquibase.

I am trying to create database independent change logs (as much as possible).  

We extensively use nvarchar for SQL server, but there is no nvarchar in Oracle.

Java SE 6 has added NVARCHAR.  Will Liquibase support NVARCHAR types?

Also, I am trying to use Maven, and just found out that I cannot pass in the databaseClass with a custom one so that I can do the conversion myself (or at least, I haven’t found out how to so far). 

2

I think we should support nvarchar.  Would you expect it to map to varchar in oracle, or what type?

Nathan

3

NVARCHAR should map to NVARCHAR2 for Oracle, which I believe has been available since Oracle 8 (but someone would need to verify that).

However, there are really two different instances, a Unicode Database and a database using a National Character Set, so how do you know whether it should be converted to a VARCHAR2(precision = 3x) or an NVARCHAR2(precision)?

I don’t know.  Perhaps there is some additional piece of meta-data or configuration option available?  So perhaps, by default, it goes to NVARCHAR2 and there is an additional paramter passed in like databaseIsUnicode?

4

Oracle will support running a Unicode Datatype solution (involved NVARCHAR2) on top of a Unicode Database (where the DB character set is configured to one of the Unicode options) - so I think it makes sense that you map the java.sql.Types.NVARCHAR type to NVARCHAR2 in Oracle - as it will then work in whatever the database character set is assigned to. The only difference I can see would be the max lengths of the precision, which would be either 2000 or 4000 depending on the database character set. This shouldn’t matter to Liquibase though - as it becomes an implementation issue.

See here for details of the differences in Oracle:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch6unicode.htm#i1007077

This change would certainly help tidy up my 400 changeSets which currently apply a modifySql tag to every operation replacing NVARCHARs with NVARCHAR2s.

Ben

5

Amen Ben, I am with you.

I haven’t looked across all of the data types yet, I just know that we have conversion issues for Character columns across dbms types.

6

I created an issue for this: http://liquibase.jira.com/browse/CORE-649

Nathan

7

I committed a fix to support nvarchar type (which changes to nvarchar2 for oracle).  Let me know if needs changes.

Nathan