Is it possible to change a column's data type using DDL statements in the VDB after it's been loaded natively?

[mdfspiff]

Is it possible to change a column's data type using DDL statements in the VDB after it's been loaded natively by the translator?

We have a column that is being created as a string by a custom translator.  We'd like
to make it into a clob using DDL statements in the VDB. 

Something like the following:

<metadata type="NATIVE,DDL">

                <![CDATA[

                ALTER FOREIGN TABLE "points" ALTER COLUMN "timestamps" OPTIONS(SET DataType 'clob');

                ]]>

        </metadata>

The translator's metadata processor creates the "timestamps" column as a string, but it's not big enough.  We need it to be a clob.  How do we override the datatype selected by the translator?

Thanks!

[rareddy]

Technically it should be possible to implement as the metadata is being manipulated at the deploy time. However, I do not see that has been implemented. We created a bigger bucket of JIRA for these [TEIID-2578] add/remove schema elements - JBoss Issue Tracker

You can log an enhancement request.

[shawkins]

You also have the option of fully specifying the Teiid DDL for the points table and excluding it from the import.

And we may need to address the type mapping as we should always choose a type that is wide enough for the data.  What are the particulars here - what is the source and what is the column type?

[tom9729]

This is for a WFS translator. It's parsing metadata from a response like this.

http://giswebservices.massgis.state.ma.us/geoserver/wfs?request=DescribeFeatureType&service=wfs&version=1.1.0&typeName=massgis:GISDATA.MINLL10_ARC

Note that there is no length information in the XSD so xsd:string = string, but if the data can be more than 4000(?) chars then it needs to map to clob or Teiid appears to truncate it. It's possible to specify length information for a type in the XSD, but I'm not sure if this is something GeoServer/GeoTools knows how to use.

Being able to alter column types (similiar to how things like SEARCHABLE can be toggled) seems useful. I think this is a bit of a fringe case because generally the long columns are GML types instead of string.

Thanks,

Tom

[shawkins]

Logged the general issue: [TEIID-3349] Allow column type to be altered - JBoss Issue Tracker