Customisation of jdbc->sql type mapping

[Thomas Fischer <tf...@apache.org>]

Is there any support planned for customisation the mapping from jdbc types 
to sql types ? For example, users might want to use nvarchar2 instead of 
varchar for some reason.

In torque, users could do this using the db.props file for the different 
databases up to Torque 3.1.1. In Torque 3.2, the db.props file is gone and 
it is quite difficult for users to define their own mappings. This needs 
to be changed in Torque. However, it would make sense to do this in a 
manner that ddlutils might support in the future.

My current plan for Torque is to use the "domain" attribute in the torque 
schema file. A domain (I'm not finding this name intuitive, maybe there's 
a better one) is a kind of "custom column type" the user can define. For 
example, the user can define a type "SHORTVARCHAR" which corresponds to a 
varchar column with size 20. The user then can use the type "shortvarchar" 
as column type in the schema, and does not need to specify the size of 20 
everywhere.

The plan would be to give the domain an additional attribute "sqlType" 
where also the sql type can be changed. So to use NVARCHAR columns, the 
user would be creating a domain called NVARCHAR2, and set its type to 
VARCHAR (i.e. it behaves like a varchar column for the java part) and its 
sqlType to NVARCHAR2.

As far as I know, ddlutils does not support the domain stuff at the 
moment, but there is a request in jira (ddlutils-84) that it should do so. 
Do you plan to implement something of the sort, or would you think that 
another way of dealing with custom mappings is more appropriate ?

      Thomas

Re: Customisation of jdbc->sql type mapping

[Thomas Fischer <tf...@apache.org>]

On Mon, 7 Aug 2006, Thomas Dudziak wrote:

> On 8/5/06, Thomas Fischer <tf...@apache.org> wrote:
>
>> Is there any support planned for customisation the mapping from jdbc types
>> to sql types ? For example, users might want to use nvarchar2 instead of
>> varchar for some reason.
>
> yep, that is one of the core features planned for the 1.1 release.
> Basically, there are planned to be three levels of customization:
>
> - for the Ant task invocation, so that the mapping applies to all
> schema files handled by this task run
>
> - for a schema file
>
> - for a specific column
>
> the general idea is to have declarative (XML) and programmatic control
> to e.g. map every occurrence of VARCHAR (JDBC type) to NVARCHAR2
> (Oracle type) regardless of length, or also for specific lengths.

ok, as it is in 1.1. you probably do not have an idea what the xml (you 
mean schema.xml here, I assume ? ) part would look like :-(

>> My current plan for Torque is to use the "domain" attribute in the torque
>> schema file. A domain (I'm not finding this name intuitive, maybe there's
>> a better one) is a kind of "custom column type" the user can define. For
>> example, the user can define a type "SHORTVARCHAR" which corresponds to a
>> varchar column with size 20. The user then can use the type "shortvarchar"
>> as column type in the schema, and does not need to specify the size of 20
>> everywhere.
>
> hmm, this is somewhat different to the above as it basically means
> that a filter is applied between reading the schema from XML and
> applying the model (e.g. writing it to the DB).
> This can surely be done but might perhaps make more sense in a Torque
> derived XML schema ? I'm not too sure yet, but since it would not be
> difficult to implement, we can just as well have it in DdlUtils.
> This would however be different from support DOMAINs in the database
> as these apply to the native type instead.

Probably you are right, on should not misuse domains for that, if they 
exist in databases and they cannot support this.

    Thomas

Re: Customisation of jdbc->sql type mapping

[Thomas Dudziak <to...@gmail.com>]

On 8/5/06, Thomas Fischer <tf...@apache.org> wrote:

> Is there any support planned for customisation the mapping from jdbc types
> to sql types ? For example, users might want to use nvarchar2 instead of
> varchar for some reason.

yep, that is one of the core features planned for the 1.1 release.
Basically, there are planned to be three levels of customization:

- for the Ant task invocation, so that the mapping applies to all
schema files handled by this task run

- for a schema file

- for a specific column

the general idea is to have declarative (XML) and programmatic control
to e.g. map every occurrence of VARCHAR (JDBC type) to NVARCHAR2
(Oracle type) regardless of length, or also for specific lengths.

> In torque, users could do this using the db.props file for the different
> databases up to Torque 3.1.1. In Torque 3.2, the db.props file is gone and
> it is quite difficult for users to define their own mappings. This needs
> to be changed in Torque. However, it would make sense to do this in a
> manner that ddlutils might support in the future.
>
> My current plan for Torque is to use the "domain" attribute in the torque
> schema file. A domain (I'm not finding this name intuitive, maybe there's
> a better one) is a kind of "custom column type" the user can define. For
> example, the user can define a type "SHORTVARCHAR" which corresponds to a
> varchar column with size 20. The user then can use the type "shortvarchar"
> as column type in the schema, and does not need to specify the size of 20
> everywhere.

hmm, this is somewhat different to the above as it basically means
that a filter is applied between reading the schema from XML and
applying the model (e.g. writing it to the DB).
This can surely be done but might perhaps make more sense in a Torque
derived XML schema ? I'm not too sure yet, but since it would not be
difficult to implement, we can just as well have it in DdlUtils.
This would however be different from support DOMAINs in the database
as these apply to the native type instead.

Tom