Category Problems

This will destroy your system completely.. not for the faint hearted.

Find out about categories in the categories table but NOT in the nested tree model. This is probably pretty harmless (unless you appear to be mmissing categories all of a sudden) but can cause 3.x versions of FarCry to fail when trying to render the category picker.

SQL to see missing categories

SELECT c.*
FROM categories c
WHERE 
categoryid NOT IN (SELECT objectid FROM nested_tree_objects)

Then decide what you want to do with any category items that appear. You could reattach them to the tree if there is something categoriesd to these category items.

SQL to check for content assigned to dead categories

SELECT * FROM refcategories
WHERE categoryid IN 
	(SELECT categoryid
	FROM categories
	WHERE 
	categoryid NOT IN 
		(SELECT objectid FROM nested_tree_objects))

or just delete them if they sem to be harmless enough.

SQL to see missing categories

DELETE
FROM categories c
WHERE 
categoryid NOT IN (SELECT objectid FROM nested_tree_objects)

However, you may have some orphaned categories in the nested tree model. ie those category references that have lost their parent (maybe through a deletion that didn't finish properly). You can grab a list of orphaned nested tree references using the following:

SQL to check for orphan tree references

SELECT *
FROM nested_tree_objects
WHERE 
parentid NOT IN (SELECT objectid FROM nested_tree_objects)
AND parentid <> ''

Rather than deleting everything you could just find the object references a new parent!

SQL to update orphans with a new parent

UPDATE nested_tree_objects
SET
parentid='--some UUID of valid parent--'
WHERE
objectid IN (select objectid from nested_tree_objects
		where 
		parentid NOT IN (select objectid from nested_tree_objects))
		AND (parentid is not null or parentid <> '')