Useful Queries

From OpenSimulator

(Difference between revisions)
Jump to: navigation, search
m (rearrange for less ambiguity)
(Note about database migration)
Line 1: Line 1:
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:
+
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, the UUID fields migrated to a consistent format (the dashed form, like 00000000-0000-0000-0000-000000000000).'''
 +
 
 +
'''This means that in earlier revision databases, you will need to use the hex(id) or replace(id, '-', <nowiki>''</nowiki>) functions to get the id's into a consistent format.  hex(id) should be used for the assets table, and replace(id, '-', <nowiki>''</nowiki>) should be used on any uuid field that is in dashed form.  UUID literals inserted into queries should not have dashes.'''
 +
 
 +
'''On more recent databases, these functions are no longer necessary, and the id fields can be used directly.'''
  
 
==Listing things==
 
==Listing things==
Line 14: Line 20:
  
 
* Verify a particular asset exists, given the UUID (usually you want to replace the UUID here with an assetID from the previous query):
 
* Verify a particular asset exists, given the UUID (usually you want to replace the UUID here with an assetID from the previous query):
  <nowiki>SELECT name FROM opensim.assets WHERE hex(id) = replace('ef9c5edf-a9f8-4310-8048-97894453a614', '-', '');</nowiki>
+
  <nowiki>SELECT name FROM opensim.assets WHERE id = 'ef9c5edf-a9f8-4310-8048-97894453a614';</nowiki>
  
 
* More complicated version - List inventory entries with a particular name:
 
* More complicated version - List inventory entries with a particular name:
  <nowiki>SELECT concat(inventoryName, ' - ',  replace(inventoryID, '-', '')) AS 'Inventory',  
+
  <nowiki>SELECT concat(inventoryName, ' - ',  inventoryID) AS 'Inventory',  
concat(assets.name, ' - ', hex(assets.id)) AS 'Asset' FROM inventoryitems LEFT JOIN assets  
+
concat(assets.name, ' - ', assets.id) AS 'Asset' FROM inventoryitems LEFT JOIN assets  
ON replace(assetID, '-', '')=hex(assets.id) WHERE inventoryName = 'My Inventory Item Here';</nowiki>
+
ON assetID=assets.id WHERE inventoryName = 'My Inventory Item Here';</nowiki>
  
 
Given 'I can haz asset' as a name, and assuming several users had something called that in their inventories, this might return:
 
Given 'I can haz asset' as a name, and assuming several users had something called that in their inventories, this might return:
  +----------------------------------------------------+--------------------------------------------------------+
+
  +--------------------------------------------------------+------------------------------------------------------------+
  | Inventory                                         | Asset                                                 |
+
  | Inventory                                             | Asset                                                     |
  +----------------------------------------------------+--------------------------------------------------------+
+
  +--------------------------------------------------------+------------------------------------------------------------+
  | I can haz asset - 17b6ca75ca8444a1813718705c591be6 | Cheeseburger        - 12345678123456781234567812345678 |
+
  | I can haz asset - 17b6ca75-ca84-44a1-8137-18705c591be6 | Cheeseburger        - 12345678-1234-5678-1234-567812345678 |
  | I can haz asset - 94ac2e6a26f74e03bedeff0e5747819a | lolcat in ur assets - 13371337133713371337133713371337 |  
+
  | I can haz asset - 94ac2e6a-26f7-4e03-bede-ff0e5747819a | lolcat in ur assets - 13371337-1337-1337-1337-133713371337 |  
  | I can haz asset - 96a6dd77c3ec4ebf91ab7e182ebb7b41 | lolcat in ur assets - 13371337133713371337133713371337 |
+
  | I can haz asset - 96a6dd77-c3ec-4ebf-91ab-7e182ebb7b41 | lolcat in ur assets - 13371337-1337-1337-1337-133713371337 |
  +----------------------------------------------------+--------------------------------------------------------+
+
  +--------------------------------------------------------+------------------------------------------------------------+
 
  3 rows in set (0.00 sec)
 
  3 rows in set (0.00 sec)
  
Line 35: Line 41:
  
 
Or, alternately, list all inventory items by asset name:
 
Or, alternately, list all inventory items by asset name:
  <nowiki>SELECT concat(inventoryName, ' - ',  replace(inventoryID, '-', '')) AS 'Inventory',  
+
  <nowiki>SELECT concat(inventoryName, ' - ',  inventoryID) AS 'Inventory',  
concat(assets.name, ' - ', hex(assets.id)) AS 'Asset' FROM opensim.inventoryitems INNER JOIN opensim.assets  
+
concat(assets.name, ' - ', 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';</nowiki>
+
ON assetID=opensim.assets.id WHERE opensim.assets.name = 'My Asset Name Here';</nowiki>
  
 
Which (given 'lolcat in ur assets' as the name) might return:
 
Which (given 'lolcat in ur assets' as the name) might return:
  +----------------------------------------------------+--------------------------------------------------------+
+
  +--------------------------------------------------------+------------------------------------------------------------+
  | Inventory                                         | Asset                                                 |
+
  | Inventory                                             | Asset                                                     |
  +----------------------------------------------------+--------------------------------------------------------+
+
  +--------------------------------------------------------+------------------------------------------------------------+
  | Pretty Pikshurs - 7962c8de232042ce845c40653ca31a0a | lolcat in ur assets - 13371337133713371337133713371337 |  
+
  | Pretty Pikshurs - 7962c8de-2320-42ce-845c-40653ca31a0a | lolcat in ur assets - 13371337-1337-1337-1337-133713371337 |  
  | I can haz asset - 96a6dd77c3ec4ebf91ab7e182ebb7b41 | lolcat in ur assets - 13371337133713371337133713371337 |  
+
  | I can haz asset - 96a6dd77-c3ec-4ebf-91ab-7e182ebb7b41 | lolcat in ur assets - 13371337-1337-1337-1337-133713371337 |  
  +----------------------------------------------------+--------------------------------------------------------+
+
  +--------------------------------------------------------+------------------------------------------------------------+
 
  2 rows in set (0.00 sec)
 
  2 rows in set (0.00 sec)
  
Line 84: Line 90:
 
==Error Checking==
 
==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):
 
* 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):
  <nowiki>SELECT inventoryname, inventoryID, assetID FROM opensim.inventoryitems WHERE replace(assetID, '-', '')
+
  <nowiki>SELECT inventoryname, inventoryID, assetID FROM opensim.inventoryitems WHERE assetID  
NOT IN (SELECT hex(id) FROM opensim.assets);</nowiki>
+
NOT IN (SELECT id FROM opensim.assets);</nowiki>

Revision as of 15:20, 17 June 2008

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, the UUID fields migrated to a consistent format (the dashed form, like 00000000-0000-0000-0000-000000000000).

This means that in earlier revision databases, you will need to use the hex(id) or replace(id, '-', '') functions to get the id's into a consistent format. hex(id) should be used for the assets table, and replace(id, '-', '') should be used on any uuid field that is in dashed form. UUID literals inserted into queries should not have dashes.

On more recent databases, these functions are no longer necessary, and the id fields can be used directly.

Contents

Listing things

  • 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(username, ' ', lastname) AS 'Online Users' FROM opensim.users 
INNER JOIN opensim.agents ON opensim.users.UUID = opensim.agents.UUID WHERE opensim.agents.agentonline = '1';
  • List the Regions in your database (only works for grid mode):
SELECT regionName as 'Regions' FROM opensim.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 opensim.assets WHERE id = 'ef9c5edf-a9f8-4310-8048-97894453a614';
  • More complicated version - List inventory entries with a particular name:
SELECT concat(inventoryName, ' - ',  inventoryID) AS 'Inventory', 
concat(assets.name, ' - ', assets.id) AS 'Asset' FROM inventoryitems LEFT JOIN assets 
ON assetID=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 - 17b6ca75-ca84-44a1-8137-18705c591be6 | Cheeseburger        - 12345678-1234-5678-1234-567812345678 |
| I can haz asset - 94ac2e6a-26f7-4e03-bede-ff0e5747819a | lolcat in ur assets - 13371337-1337-1337-1337-133713371337 | 
| I can haz asset - 96a6dd77-c3ec-4ebf-91ab-7e182ebb7b41 | lolcat in ur assets - 13371337-1337-1337-1337-133713371337 |
+--------------------------------------------------------+------------------------------------------------------------+
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, ' - ',  inventoryID) AS 'Inventory', 
concat(assets.name, ' - ', assets.id) AS 'Asset' FROM opensim.inventoryitems INNER JOIN opensim.assets 
ON assetID=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 - 7962c8de-2320-42ce-845c-40653ca31a0a | lolcat in ur assets - 13371337-1337-1337-1337-133713371337 | 
| I can haz asset - 96a6dd77-c3ec-4ebf-91ab-7e182ebb7b41 | lolcat in ur assets - 13371337-1337-1337-1337-133713371337 | 
+--------------------------------------------------------+------------------------------------------------------------+
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 assetID 
NOT IN (SELECT id FROM opensim.assets);
Personal tools
General
About This Wiki