Mailing List Archive

As one only partly-informed guess: --we get issues like this when multiple
copies of the story are open (more than one browser session of the same
user -- so it will let more than one edit)

Browser session one opens, saves twice, browser session 2 saves once, but
more recently than browser session 1, smaller version #

Bret usually runs some query to correct version # as you did,

I guess some check against what versions have been saved (including outside
current session) before naming version # might prevent this?

Cheers,

Bryan


On Thu, Nov 8, 2012 at 12:08 PM, Phillip Smith <ps@phillipadsmith.com>wrote:

>
> Had a strange issue with a story in Bricolage today.
>
> Users claimed they couldn't find the story by searching in Bricolage. I
> confirmed that it was not visible in search results, even with the deleted
> option checked.
>
> Looked in the database, the story was there. Couldn't figure it out at
> first.
>
> Then noticed that in the story table the story's current_version was 2001
> and the published_version was 2000. Looked in the story_instance table and
> noticed that there was no version 2001, only 2000.
>
> When I changed the current_version ID to match the published_version ID,
> the story reappeared.
>
> Any thoughts on why this would happen?
>
> Phillip.
>
> --
> Phillip Smith
> http://phillipadsmith.com
> http://twitter.com/phillipadsmith
> http://linkedin.com/in/phillipadsmith
>
> If your email inbox is out of control, check out
> http://sanebox.com/t/s0q7m
>
> Save our in-boxes! http://emailcharter.org
>
>

On Nov 8, 2012, at 9:08 AM, Phillip Smith <ps@phillipadsmith.com> wrote:

> Then noticed that in the story table the story's current_version was 2001 and the published_version was 2000. Looked in the story_instance table and noticed that there was no version 2001, only 2000.
>
> When I changed the current_version ID to match the published_version ID, the story reappeared.
>
> Any thoughts on why this would happen?

Only that we are clearly not using adequate foreign key constraints for versions.

David

On 2012-11-08, at 11:36 AM, David E. Wheeler <david@justatheory.com> wrote:

> On Nov 8, 2012, at 9:08 AM, Phillip Smith <ps@phillipadsmith.com> wrote:
>
>> Then noticed that in the story table the story's current_version was 2001 and the published_version was 2000. Looked in the story_instance table and noticed that there was no version 2001, only 2000.
>>
>> When I changed the current_version ID to match the published_version ID, the story reappeared.
>>
>> Any thoughts on why this would happen?
>
> 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.

--
Phillip Smith
http://phillipadsmith.com
http://twitter.com/phillipadsmith
http://linkedin.com/in/phillipadsmith

If your email inbox is out of control, check out http://sanebox.com/t/s0q7m

Save our in-boxes! http://emailcharter.org

On Nov 9, 2012, at 11:20 AM, Phillip Smith <ps@phillipadsmith.com> 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!

David

On 2012-11-09, at 4:45 PM, David E. Wheeler <david@justatheory.com> wrote:

> On Nov 9, 2012, at 11:20 AM, Phillip Smith <ps@phillipadsmith.com> 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