Den 2011-04-01 09:47 skrev Dmitry Yemanov såhär: > 01.04.2011 11:19, Kjell Rilbe wrote: > >> I'm not sure I made much sense... Tell me, does the standard specify >> anything regarding datatype change features? If so, what does it say? > > It allows to alter a string column to a string one of the non-shorter > length. This is how FB works since the beginning. I cannot find any > reference to charset/collation details, unfortunately.
I suppose the "normal way" to convert a columns from one charset to another (and maybe collation as well), would be to create a new temp col with the new datatype, copy the data there to get it transliterated and find all issues with the existing data, then drop the old column and recreate it with the new datatype, and finally copy the data back and drop the temp column. This works of course, but may cause lots of problems if the column is referenced in various constraints. Is it a common enough problem to warrant FB to support a more convenient way to do it? Are there any existing features that can help with the ref/constraint issues? If not, is it possible or desirable to add such features instead of allowing col alterations that require transliteration, validation, index rebuilds? While writing this, I do feel that it would be rather nice if Firebird could automatically do all this: 1. Transliterate/cast existing data to new type, raising erors if data can't be converted. 2. Validate constraints with the new datatype/charset/collation and converted data, raising errors if the alteration results in constraint violations. 3. Rebuild indices, e.g. due to datatype changes (char->int) or collation changes. In some cases a user who issues alter statements will end up with a very long executioin time if the table is large, but data will not be broken and there will be no problems with alterations on columns that are referenced in constraints. One issue would be if a column that's referenced in a foreign key is altered so that the foreign key becomes invalid. If the principle I outline should be taken to its extreme, this should be possible to solve also. Maybe allow columns in keys to be altered as long as the referenced column and the referencing column are "key compatible" (= exact same type?) when the changhes are committed. I.e. up until commit time, "broken" keys are allowed. Good? Bad? Let me know if I'm not making sense... Kjell -- -------------------------------------- Kjell Rilbe DataDIA AB E-post: kj...@datadia.se Telefon: 08-761 06 55 Mobil: 0733-44 24 64 ------------------------------------------------------------------------------ Create and publish websites with WebMatrix Use the most popular FREE web apps or write code yourself; WebMatrix provides all the features you need to develop and publish your website. http://p.sf.net/sfu/ms-webmatrix-sf Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
