Incremental modification of columns
I have learned the hard way from working with a DDL management tool that there are cases where Informix does not support certain incremental alterations to a column. (The DDL tool abstracts away the various very common attributes about a column that one can modify, such as data type for example.) That is, whereas in other databases I can alter a column's data type, say, without affecting its not null or default constraints, in Informix, the MODIFY clause is all-or-nothing. If I change the data type, the constraints fall away. Why is this? Have people lobbied for this to be changed in the past? Thanks, Best, Laird -- http://about.me/lairdnelson --f46d041827ecc5274404cb4043aa
The reason is historical. Originally Informix treated check constraints, defaults constraints, and not null constraints as attributes of the column type (indeed the NOT NULL is still implemented as a flag in the syscolumns.systype column as well as a full blown constraint) so by MODIFYing a columns type without specifying those "attributes" the new type of the column will not have them. Those three attributes were defined as constraints long after Informix's servers permitted ALTERing a table/column. While the constraint form of the attributes is supported, the semantics of them as attributes survives. Could it be changed? Probably. Should it be changed? Debatable. As to DDL tools, I use Dezign for Databases by Datanomics ( www.datanamic.com/dezign/) and IB that it properly supports Informix style ALTERS when you generate a diff script with it. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Oct 4, 2012 at 2:53 PM, Laird Nelson <ljnelson@gmail.com> wrote: > I have learned the hard way from working with a DDL management tool that > there are cases where Informix does not support certain incremental > alterations to a column. (The DDL tool abstracts away the various very > common attributes about a column that one can modify, such as data type for > example.) > > That is, whereas in other databases I can alter a column's data type, say, > without affecting its not null or default constraints, in Informix, the > MODIFY clause is all-or-nothing. If I change the data type, the > constraints fall away. > > Why is this? Have people lobbied for this to be changed in the past? > > Thanks, > Best, > Laird > > -- > http://about.me/lairdnelson > > --f46d041827ecc5274404cb4043aa > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f502ff886294804cb40fe68
On Thu, Oct 4, 2012 at 12:45 PM, Art Kagel <art.kagel@gmail.com> wrote: > The reason is historical. Originally Informix treated check constraints, > defaults constraints, and not null constraints as attributes of the column > type (indeed the NOT NULL is still implemented as a flag in the > syscolumns.systype column as well as a full blown constraint) so by > MODIFYing a columns type without specifying those "attributes" the new type > of the column will not have them. > Excellent; thank you very much for the information. Concise, clear and thorough. > Could it be changed? Probably. Should it be changed? Debatable. > OK. The tool in question is Liquibase (http://www.liquibase.org). Tracks the running of moderately vendor-independent DDL and SQL commands, abstracting some of the syntax away. Wonderful tool. However, their modifyDataType element ( http://www.liquibase.org/manual/modify_datatype_refactoring) will (for the reasons you state above) drop all attributes about a column when it runs on Informix. It works properly on all other databases. You can see the Java implementation of this command here and why it fails on Informix: https://github.com/liquibase/liquibase/blob/master/liquibase-core/src/main/java/ liquibase/sqlgenerator/core/ModifyDataTypeGenerator.java#L52 If I'm reading between the lines here, there is basically zero chance, right? that the semantics of the MODIFY clause would ever be changed. I don't mean this to be insulting or snarky, I am just trying to cut to the chase. I ask because if there is any real chance that Informix would support incremental alterations like all the other databases that Liquibase supports, then I won't bother with an industrial-strength patch to Liquibase. If, on the other hand, there is basically no chance of the Informix semantics changing in this regard, then I will devote my energy to patching Liquibase. Sounds like as long as I take these three "attributes" into consideration and restore them in my constructed MODIFY clause I should be OK? I strongly urge anyone on the Informix team who slings Java to have a look around this (heavily used) tool (it's not mine :-)) and to contribute patches to the Informix support. As with most things related to Informix in the open source Java world, some well-intentioned guy sort of stabbed around in the dark until Informix support more or less works properly; there are all sorts of edge cases that do NOT work and I'm enough of an Informix newbie to not know how to fix them myself. (For that matter, the Hibernate (you may have heard of them :-)) Informix support also falls into this camp; people have had trouble with it in the past (http://www.iiug.org/forums/development-tools/index.cgi/read/164). You can see the Hibernate Informix support classes here: https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/main/j ava/org/hibernate/dialect/InformixDialect.java ) Thanks very much, again. Best, Laird -- http://about.me/lairdnelson --0016e6d64089e1ee8404cb41725d
IBM actually did contribute some updates for Hibernate to better support Informix. They are in the IIUG Software Repository at: http://www.iiug.org/opensource/files/hibernate-3.3.2_informix.tar.gz Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Oct 4, 2012 at 4:17 PM, Laird Nelson <ljnelson@gmail.com> wrote: > On Thu, Oct 4, 2012 at 12:45 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > The reason is historical. Originally Informix treated check constraints, > > defaults constraints, and not null constraints as attributes of the > column > > type (indeed the NOT NULL is still implemented as a flag in the > > syscolumns.systype column as well as a full blown constraint) so by > > MODIFYing a columns type without specifying those "attributes" the new > type > > of the column will not have them. > > > > Excellent; thank you very much for the information. Concise, clear and > thorough. > > > Could it be changed? Probably. Should it be changed? Debatable. > > > > OK. The tool in question is Liquibase (http://www.liquibase.org). Tracks > the running of moderately vendor-independent DDL and SQL commands, > abstracting some of the syntax away. Wonderful tool. However, their > modifyDataType element ( > http://www.liquibase.org/manual/modify_datatype_refactoring) will (for the > reasons you state above) drop all attributes about a column when it runs on > Informix. It works properly on all other databases. > > You can see the Java implementation of this command here and why it fails > on Informix: > > > https://github.com/liquibase/liquibase/blob/master/liquibase-core/src/main/java/ liquibase/sqlgenerator/core/ModifyDataTypeGenerator.java#L52 > > If I'm reading between the lines here, there is basically zero chance, > right? that the semantics of the MODIFY clause would ever be changed. I > don't mean this to be insulting or snarky, I am just trying to cut to the > chase. > > I ask because if there is any real chance that Informix would support > incremental alterations like all the other databases that Liquibase > supports, then I won't bother with an industrial-strength patch to > Liquibase. If, on the other hand, there is basically no chance of the > Informix semantics changing in this regard, then I will devote my energy to > patching Liquibase. Sounds like as long as I take these three "attributes" > into consideration and restore them in my constructed MODIFY clause I > should be OK? > > I strongly urge anyone on the Informix team who slings Java to have a look > around this (heavily used) tool (it's not mine :-)) and to contribute > patches to the Informix support. As with most things related to Informix > in the open source Java world, some well-intentioned guy sort of stabbed > around in the dark until Informix support more or less works properly; > there are all sorts of edge cases that do NOT work and I'm enough of an > Informix newbie to not know how to fix them myself. > > (For that matter, the Hibernate (you may have heard of them :-)) Informix > support also falls into this camp; people have had trouble with it in the > past (http://www.iiug.org/forums/development-tools/index.cgi/read/164). > You can see the Hibernate Informix support classes here: > > > https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/main/j ava/org/hibernate/dialect/InformixDialect.java > ) > > Thanks very much, again. > > Best, > Laird > > -- > http://about.me/lairdnelson > > --0016e6d64089e1ee8404cb41725d > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3ba95b91cee104cb41f30a
On Thu, Oct 4, 2012 at 1:53 PM, Art Kagel <art.kagel@gmail.com> wrote: > IBM actually did contribute some updates for Hibernate to better support > Informix. They are in the IIUG Software Repository at: > http://www.iiug.org/opensource/files/hibernate-3.3.2_informix.tar.gz But this patch was never applied to the official support, right? (At least there's sections in the patch that do not appear in https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/main/j ava/org/hibernate/dialect/InformixDialect.java. For example, https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/main/j ava/org/hibernate/dialect/InformixDialect.java#L62(line 62) is one of several lines that is deleted by the patch, but as you can see from the Github sources is not present.) I'm by NO means criticizing the fact that someone who was not me :-) took time out of their busy life to do this. I'm just calling attention to the fact that the official support of Informix is a distinct laggard in all Java open source projects that I know of; it would be neat if it were beefed up. Again thanks for the information and your time. Best, Laird -- http://about.me/lairdnelson --f46d0443046e1f0acb04cb4225c4