[wp-hackers] Restructuring Category Hierarchy

[Michael E. Hancock]

Jamie Talbot wrote:
> Michael E. Hancock wrote:
> > But with the Nested List, what happens in a delete case,
> > where the server *coughs* just after the deletion step,
> > but before the left and right pointers get reset?
>
> It is a concern - I don't use MyISAM at all outside of
> WordPress for this reason.  However, there are still
> multiple stages in adding or deleting in the current
> system - we write to wp_post2cat as well as
> wp_categories and wp_posts - if it borks halfway
> through any of that you wouldn't be happy.  We could
> always use InnoDB for the new tables, which provide
> transaction support (as well as referential constraints).
> And there's no problem mixing the two types in the same
> database...  But as is pointed out in another thread,
> it takes more space and is potentially slower.

Thanks for the reply Jamie.

Today, you write a post, wp_posts gets updated, the server borks and
wp_post2cat doesn't get updated.  You notice the post has no category, but
that's easy to fix--just add that category to the post again.

In the Nested Set, following the example [1] of Adding New Nodes,  when the
server *trips* while shifting the other nodes (e.g. adding 2 to the lft and
rgt columns), it would seem any subsequent query of the tree is going to be
inaccurate, and any additions or deletions will not 'fix' the problem.
There is no recovering.

>From that standpoint, it would seem Nested Sets absolutely need InnoDB (i.e.
transactions).

Are the 'commit' developers willing to use InnoDB?

Finally, what are the implications of the LOCK TABLE in the Adding New Nodes
example?

Regards.

Michael E. Hancock


[1] http://dev.mysql.com/tech-resources/articles/hierarchical-data.html