Store birthdates as single columns

[kousu]

The db schema contains (birthyear,birthmonth,birthday) exploded, despite handling created, modified and orientationdate just fine as whole DateTimes. This leads to some awkward code in the views like

  <?php $age = (new DateTime($v['birthyear'] . '-' . $v['birthmonth'] . '-' . $v['birthday']))->diff(new DateTime('
now'))->y; ?>
    <dt>Birthday</dt>
    <dd>
      <?php echo format_bday($v["birthday"], $v["birthmonth"], $v["birthyear"]) ?>

That would be better written like format_bday(new DateTime($v["birth"])).

The volunteers edit view has a jquery date picker for the orientation, but separate fields for birth. We could also edit the view to use a date picker for both.

[kousu]

This might be a feature, not a bug. Knowing a birthday without a birthyear allows the birthday-wishes page to work without revealing age, and conversely knowing a birthyear without birthday might happen if someone mentioned their age but not their birthday; moreover it's a case that shows up in our production database:

mysql> -- birthyear sans date
mysql> select id, created, firstname, lastname, orientationdate, birthyear, birthmonth, birthday from volunteers where isnull(birthyear) and not isnull(birthmonth) and not isnull(birthday) order by orientationdate;
[ censored ]
+------+---------------------+----------------+------------------+-----------------+-----------+------------+----------+
47 rows in set (0.00 sec)

mysql> -- birthdate sans year
mysql> select id, created, firstname, lastname, orientationdate, birthyear, birthmonth, birthday from volunteers where not isnull(birthyear) and isnull(birthmonth) and isnull(birthday) order by orientationdate;
27 rows in set (0.00 sec)

There's also these cases, which are definitely mistakes:

mysql> select id, created, firstname, lastname, orientationdate, birthyear, birthmonth, birthday from volunteers where not isnull(birthyear) and isnull(birthmonth) and not isnull(birthday) order by orientationdate;
| XXXX | XXXXXXXXX | XXXXX    | XXXXX  | XXXXXX      |      1996 |       NULL |       30 |
...
2 rows in set (0.01 sec)

mysql> select id, created, firstname, lastname, orientationdate, birthyear, birthmonth, birthday from volunteers where not isnull(birthyear) and not isnull(birthmonth) and isnull(birthday) order by orientationdate;
| XXXX | XXXXXXXXXX | XXXXXX     | XXXXXX    | XXXXXX      |      1995 |          5 |     NULL |
4 rows in set (0.00 sec)

I think we should still switch to a fully-typed column, and just do some sort of migration on these entries; ~80 weird cases over 6+ years isn't much to worry about.

We could:

• convert isnull(birthyear) or isnull(birthmonth) or isnull(birthday) => birthdate = NULL
• default everyone's birthyear to, say, 1970, and birthmonth and birthday to January and 1, if not given.
• manually flag these cases for review

[kousu]

btw, in https://github.com/santropolroulant/volunteerdb/commit/50913c7c2250a045268eb837686fb75e75a151c0 I did the date-picker thing I suggested, before I realized this might be a feature.

[kousu]

I've been given leave to drop support for partial birthdays, so I'm going to do the convert isnull(birthyear) or isnull(birthmonth) or isnull(birthday) => birthdate = NULL plan.

This is also a good moment to start using migrations; so we should do #10 first before this.

[kousu]

It should be possible merge the data inside a migration; if we're feeling generous, we could even write a rollback function down() in it, since this transformation should be reversible.

[kousu]

Catch: the only cross-SQL way to do year() and month() operations is extract. Luckily, CakePHP supports this. Unfortunately, the CakePHP examples suggest year() which is MySQL-specific.