I'll answer my own rant for future reference on the subject. I ended up doing it this way:
Got the majority of the schema migration queries from Navicat (a gui for MySQL). Got a more complete list of collation and charset changes by using this little script I found and modified a bit: http://bogdan.org.ua/2008/02/08/convert-mysql-database-from-one-encodingcollation-into-another.html I now had an sql-file that would put everything into an application default charset and collaton. I still had to manually check and enter the collation and charset data for fields and tables that for one reason of another had to use a different setting than the "application default". It was a bit of a mess and not a lot of fun, but it worked. /Martin On Oct 7, 11:09 am, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > Hi, > I am about to go grey-haired from trying to migrate our database > schema. How on earth can this be done without screwing it all up > royally? A part from tedious, manual creation of many many alter > statements, what do you use? How do you do it? > That is the short question... > > ...here is the longer explaining rant: > > The problem is character sets and collations. They can be ignored for > smaller, english applications but are a necessity for larger and > especially non-english language applications. > > Typical affected parts of my data: > Any character-field used as a "keyword" or as a unique index. Things > of that sort MUST bave the correct collation to work. > Any list of names that should be sorted. Names of files, folders, > people... whatever. Without the correct collation a portion of the > data will be sorted the wrong way. > Any texts that should be searchable. The wrong collation means the > search feature will find too many or too few results in some cases. > > So what's the problem? > Cake's schema support ignores these thing completely. (which is better > than partial support IMHO) > mysqldiff.org and most reasonably priced software I have tried for > creating diffs or migrations have only partial support. Ignoring field- > level settings while altering table-level defaults... but keeping all > settings when creating new tables. > MySQL (at least v5) applies the current default table-level settings > to any field without its own specification. Which makes the support of > the software I have tried even more treacherous to use. They will > implement a new table default but that will have no effect on any > existing field. > > All this makes for a high-risk situation where a migration is almost > more likely to cause problems than not. > > Leaving character set and collation out will write the current > defaults (of a higher "level") to the created table or field. That > means that the only level where a migration tool absolutely must > support this is on the fields. Any change to anything else will have > no effect at all on existing fields or tables... I have found no > software that support this. :) > > My problem is finding a way of modifying my schema this once so that > the next time I can use an "automated" diff or migration. > > Martin - feeling screwier than sql --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~----------~----~----~----~------~----~------~--~---
