[Opensim-dev] some useful SQL statements (mysql)

[Mic Bowman]

over the last couple months i've accumulated a few useful SQL
statements for finding (and occasionally cleaning up) my MySQL
database. Some of the complexity comes from the various formats of
UUID that are used (binary, ascii, ascii with '-' separators) and
character sets (see the list below).

Find all prims that are outside region boundaries
SELECT * FROM opensim.prims where GroupPositionX<0 or
GroupPositionX>=256 or GroupPositionY<0 or GroupPositionY>=256 or
GroupPositionZ<0;

Finds all the prim inventory items that are contained in prims that no
longer exist
	SELECT * FROM opensim.primitems where replace(primID,'-','') not in
(select UUID from opensim.prims);

Find all the prim shapes entries for which the prims no longer exist
	SELECT * FROM opensim.primshapes where UUID not in (select UUID from
opensim.prims);

Find all the inventory items in trash folders
	SELECT assetID FROM opensim.inventoryitems where parentFolderID in
(select folderID FROM opensim.inventoryfolders where
folderName='Trash');

Find all assets currently in the trash folders
	SELECT hex(id) FROM opensim.assets where hex(id) in (SELECT
replace(assetID,'-','') FROM opensim.inventoryitems where
parentFolderID in (select folderID FROM opensim.inventoryfolders where
folderName='Trash'));

Find all inventory items for which folders no longer exist
	SELECT * from opensim.inventoryitems where parentFolderID not in
(select folderID from opensim.inventoryfolders);

Find prim assets that are not referenced in inventory or prims
	SELECT hex(id) from opensim.assets where invType=6 and hex(id) not in
(select hex(unhex(replace(assetID,'-',''))) from
opensim.inventoryitems union select hex(unhex(UUID)) from
opensim.prims);

Here's a dump of the character sets used in each of the tables:
opensim.agents -- utf8
opensim.assets -- utf8
opensim.inventoryfolders -- utf8
opensim.inventoryitems -- utf8
opensim.land -- latin1
opensim.landaccesslist -- latin1
opensim.logs --
opensim.primitems -- latin1
opensim.prims -- latin1
opensim.primshapes -- latin1
opensim.regions -- utf8
opensim.terrain -- latin1
opensim.userfriends -- utf8
opensim.users -- utf8