ps at phillipadsmith
Nov 11, 2012, 7:16 PM
Post #6 of 6
(467 views)
Permalink
|
On 2012-11-09, at 4:45 PM, David E. Wheeler <david [at] justatheory> wrote:
> On Nov 9, 2012, at 11:20 AM, Phillip Smith <ps [at] phillipadsmith> wrote:
>
>>> Only that we are clearly not using adequate foreign key constraints for versions.
>>
>> I'm willing to take a look / try to fix, if you have some guidance on where to start.
>
> Currently the story table has a version column, rather than a story_instance__id column. This is because there can be two instances with the same version at one time: The currently-checked in version, and the currently checked-out version. When the currently checked-out version is checked in, its version is incremented. This is…unfortunate. (The same applies to the media and template tables, of course).
>
> The reason it's this way is so that, when selecting stories from the database, we want a join that returns just the latest instance. Most of the time, that's the last checked-in instance. But when you're editing a story, we want to give you the checked-out instance, which is the one you're working on. So the query is something like this:
>
> SELECT s.*, si.*
> FROM story s
> JOIN story_instance si ON s.id = si.story__id
> WHERE si.checked_out = ?;
>
> So if you have a story checked out to edit, we pass true for the parameter. In all other cases we pass 0, so you get the latest checked-in version.
>
> If we were to switch to a proper FK reference, we'd have to add two columns to story, I think: checked_in_story_instance_id and checked_out_story_instance_id. Most of the time, the latter would be NULL. When a story is first created, before its checked in, the former would be NULL. If a story has previously been checked in and us currently checked out, neither would be NULL. If you wanted the latest checked-out version, you would join from checked_out_story_instance_id to story_instance.id. If you wanted the latest checked-in version, you would join from checked_in_story_instance_id to story_instance.id. The code that does this joining, BTW, is in Bric::Util::DBI.
>
> In addition, we would also want a CHECK constraint on the table, to make sure that one or the other is not null. Something like
>
> ALTER TABLE story ADD CONSTRAINT ck_story_instances CHECK(
> COALESCE(checked_out_story_instance_id, checked_in_story_instance_id) IS NOT NULL
> );
>
> So, the plan would be, for story, media, and template:
>
> • Add the two FK constraint columns
> • Add the check constraint
> • Drop the version column
> • Update the queries that join the two tables
> • Make all the tests pass
>
> Have fun!
Thanks! :-P
--
Phillip Smith
http://phillipadsmith.com
|
