alphagov/transition — pull request #311 by jennyd (2014-06-10 10:26:36+00:00)
Title: Commit the db/structure.sql diff about collation

For some time we have had various bits of diff in this file after
running tests with rake. For the full history, see this commit:

https://github.gds/gds/puppet/commit/310eb6370c893a849a5a0944e4243a031ff93cba

That change was deployed a few weeks ago, and since then the
whitelisted_hosts table has been correctly created with utf8_unicode_ci,
so we think that collation for new tables should be fine in future. The
following tables had been created with utf8_general_ci and were manually
altered to utf8_unicode_ci:

- mappings_batch_entries
- mappings_batches
- organisations_sites
- sessions

Those tables now have the correct table-level collation, but text and
varchar columns in those tables somehow still have the wrong collation
at the column level. A new varchar column created since the table collation
was fixed does have the correct collation (type on mappings_batches).

Collation in itself doesn't make any difference for these four tables:
we don't sort by any of their text columns and even if we did, most of
them store ASCII-only text which would be sorted identically between the
two collations in question anyway.

For consistency we should probably manually alter the affected columns
so that they also have utf8_unicode_ci collation. Since we don't care
about this issue for any other reason, and Rails continues to give us
this diff, let's commit it as it is immediately after replicating data
and running rake tests so that we don't have to work around it all the
time and don't accidentally commit it along with other intended changes
to this file.