Useful Queries
From OpenSimulator
 |
Languages: |
|
This page lists various SQL queries one can perform from a MySql console to check the database for various bits of information. They have not been tested in other SQL variants, and may or may not work as intended:
NOTE: In SVN revision 5115 through 5123, some UUID fields began migrating to a consistent format (the dashed form, like 00000000-0000-0000-0000-000000000000).
This means that in these later revision databases, you will no longer need to use the hex(id) function on the assets table. On the other hand, until all UUIDs are in the same format, replace(id, '-', '') will still be necessary on dashed UUID fields to get the id's into a consistent format for joins and such. UUID literals inserted into queries should not have dashes. The queries on this page have not yet been revised to take this into consideration.
Listing things
- List All Friends of a Certain User, this checks one column and then checks the other, it will not Show Flags, so even if it is still just "Offered" then it will show as a friend.
SELECT friends.PrincipalID, CONCAT(useraccounts.FirstName, " ", useraccounts.LastName) AS "Friend" FROM friends,useraccounts WHERE friends.Friend = "054f4add-305d-48f3-a998-c5db4c8e15c9" AND useraccounts.PrincipalID = friends.PrincipalID UNION SELECT friends.Friend, CONCAT(useraccounts.FirstName, " ", useraccounts.LastName) AS "Friend" FROM friends, useraccounts WHERE friends.PrincipalID = "054f4add-305d-48f3-a998-c5db4c8e15c9" AND useraccounts.PrincipalID = friends.Friend
- List the users presently logged in (may be inflated due to users not getting logged out properly, whether due to bugs or crashes):
SELECT concat(FirstName, ' ', LastName) AS 'Online Users' FROM UserAccounts INNER JOIN GridUser ON UserAccounts.PrincipalID = GridUser.UserID WHERE GridUser.Online = 'True';
- List the Regions in your database (only works for grid mode):
SELECT regionName as 'Regions' FROM regions;
- Find the asset UUID of a given inventory Item (replace the name with the one you want). This may return multiple rows if more than one inventory item exists, and some entries may point to a single asset, while others point at different assets.
SELECT inventoryName, InventoryID, assetID FROM inventoryitems WHERE inventoryName = 'My Inventory Item Name Here';
- Verify a particular asset exists, given the UUID (usually you want to replace the UUID here with an assetID from the previous query):
SELECT name FROM assets WHERE id = replace('ef9c5edf-a9f8-4310-8048-97894453a614', '-', '');
- More complicated version - List inventory entries with a particular name:
SELECT concat(inventoryName, ' - ', replace(inventoryID, '-', '')) AS 'Inventory', concat(assets.name, ' - ', hex(assets.id)) AS 'Asset' FROM inventoryitems LEFT JOIN assets ON replace(assetID, '-', '')=hex(assets.id) WHERE inventoryName = 'My Inventory Item Here';
Given 'I can haz asset' as a name, and assuming several users had something called that in their inventories, this might return:
+----------------------------------------------------+--------------------------------------------------------+ | Inventory | Asset | +----------------------------------------------------+--------------------------------------------------------+ | I can haz asset - 17b6ca75ca8444a1813718705c591be6 | Cheeseburger - 12345678123456781234567812345678 | | I can haz asset - 94ac2e6a26f74e03bedeff0e5747819a | lolcat in ur assets - 13371337133713371337133713371337 | | I can haz asset - 96a6dd77c3ec4ebf91ab7e182ebb7b41 | lolcat in ur assets - 13371337133713371337133713371337 | +----------------------------------------------------+--------------------------------------------------------+ 3 rows in set (0.00 sec)
The last two entries are the same asset, but two different inventory entries. If you get a NULL result in the Asset column, that means the asset was not found (see the error checking section)
Or, alternately, list all inventory items by asset name:
SELECT concat(inventoryName, ' - ', replace(inventoryID, '-', '')) AS 'Inventory', concat(assets.name, ' - ', hex(assets.id)) AS 'Asset' FROM opensim.inventoryitems INNER JOIN opensim.assets ON replace(assetID, '-', '')=hex(opensim.assets.id) WHERE opensim.assets.name = 'My Asset Name Here';
Which (given 'lolcat in ur assets' as the name) might return:
+----------------------------------------------------+--------------------------------------------------------+ | Inventory | Asset | +----------------------------------------------------+--------------------------------------------------------+ | Pretty Pikshurs - 7962c8de232042ce845c40653ca31a0a | lolcat in ur assets - 13371337133713371337133713371337 | | I can haz asset - 96a6dd77c3ec4ebf91ab7e182ebb7b41 | lolcat in ur assets - 13371337133713371337133713371337 | +----------------------------------------------------+--------------------------------------------------------+ 2 rows in set (0.00 sec)
Counting things
- Count the total number of users:
SELECT count(uuid) AS 'Users' FROM opensim.users;
- Count the number of users presently logged in (same issues as listing online users, above):
SELECT count(UUID) AS 'Users Online' FROM opensim.agents WHERE agentonline = '1';
- Count the number of Regions. There are different versions, depending on whether you run in standalone or grid mode, because standalone mode lacks the regions table.
- Count the regions that have parcels:
SELECT count(DISTINCT regionUUID) FROM opensim.land;
- Count the total number of parcels:
SELECT count(UUID) AS 'Parcels' FROM opensim.land;
Standalone mode
This may be slow if you have a lot of old terraforming revisions lying around your database:
SELECT count(DISTINCT regionUUID) AS 'Regions' FROM opensim.terrain;
Grid Mode
Version 1 counts unique Region names:
SELECT count(DISTINCT regionName) AS 'Regions' FROM opensim.regions;
Version 2 counts regionIDs, so if you have multiple entries for regions, this will catch them.
SELECT count(UUID) AS 'Regions' FROM opensim.regions;
Error Checking
- List all inventory entries that point to assets that do not exist (this should never happen, but if there are items in your inventory that you can't seem to use something and/or the asset server complains about an asset missing, see if it turns up with):
SELECT inventoryname, inventoryID, assetID FROM opensim.inventoryitems WHERE replace(assetID, '-', '') NOT IN (SELECT hex(id) FROM opensim.assets);
- Check if there's leftover primshapes without a entry in the prim table and delete them - standalone
delete from primshapes using primshapes left join prims on prims.uuid=primshapes.uuid where prims.uuid is null;
- Check if there's leftover primshapes without a entry in the prim table and delete them - grid
select * from primshapes left join prims on prims.uuid=primshapes.uuid where prims.uuid is null
