Useful Queries

From OpenSimulator

(Difference between revisions)
Jump to: navigation, search
(New page: 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...)
 
(Listing things)
(12 intermediate revisions by 5 users not shown)
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:
+
__NOTOC__
 +
{{Quicklinks}}
 +
<br />
  
==Listing things==
+
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:
* List the users presently logged in (may be inflated due to users not getting logged out properly, whether due to bugs or crashes):
+
  
<nowiki>SELECT concat(username, ' ', lastname) AS 'Online Users' FROM opensim.users
+
'''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).'''
INNER JOIN opensim.agents ON opensim.users.UUID = opensim.agents.UUID WHERE opensim.agents.agentonline = '1';</nowiki>
+
 
 +
'''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, '-', <nowiki>''</nowiki>) 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.
 +
  <nowiki>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</nowiki>
 +
 
 +
 
 +
* List the users presently logged in (may be inflated due to users not getting logged out properly, whether due to bugs or crashes):
  
 +
  <nowiki> SELECT concat(FirstName, ' ', LastName) AS 'Online Users' FROM useraccounts INNER JOIN griduser ON useraccounts.PrincipalID = griduser.UserID WHERE griduser.Online = 'True';</nowiki>
 
* List the Regions in your database (only works for grid mode):
 
* List the Regions in your database (only works for grid mode):
 
  <nowiki>SELECT regionName as 'Regions' FROM opensim.regions;</nowiki>
 
  <nowiki>SELECT regionName as 'Regions' FROM opensim.regions;</nowiki>
  
* 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.
+
* 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.
 
  <nowiki>SELECT inventoryName, InventoryID, assetID FROM inventoryitems WHERE inventoryName = 'My Inventory Item Name Here';</nowiki>
 
  <nowiki>SELECT inventoryName, InventoryID, assetID FROM inventoryitems WHERE inventoryName = 'My Inventory Item Name Here';</nowiki>
  
Line 31: Line 47:
 
  3 rows in set (0.00 sec)
 
  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)
+
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)
  
  
Line 48: Line 64:
 
  2 rows in set (0.00 sec)
 
  2 rows in set (0.00 sec)
  
 
+
== Counting things ==
 
+
==Counting things==
+
  
 
* Count the total number of users:
 
* Count the total number of users:
Line 60: Line 74:
 
  <nowiki>SELECT count(UUID) AS 'Users Online' FROM opensim.agents WHERE agentonline = '1';</nowiki>
 
  <nowiki>SELECT count(UUID) AS 'Users Online' FROM opensim.agents WHERE agentonline = '1';</nowiki>
  
* 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 number of Regions. There are different versions, depending on whether you run in standalone or grid mode, because standalone mode lacks the regions table.
  
===Standalone mode===
+
* Count the regions that have parcels:
 +
<nowiki>SELECT count(DISTINCT regionUUID) FROM opensim.land;</nowiki>
 +
 
 +
* Count the total number of parcels:
 +
<nowiki>SELECT count(UUID) AS 'Parcels' FROM opensim.land;</nowiki>
 +
 
 +
=== Standalone mode ===
 
This may be slow if you have a lot of old terraforming revisions lying around your database:
 
This may be slow if you have a lot of old terraforming revisions lying around your database:
 
  <nowiki>SELECT count(DISTINCT regionUUID) AS 'Regions' FROM opensim.terrain;</nowiki>
 
  <nowiki>SELECT count(DISTINCT regionUUID) AS 'Regions' FROM opensim.terrain;</nowiki>
  
===Grid Mode===
+
=== Grid Mode ===
  
 
Version 1 counts unique Region names:
 
Version 1 counts unique Region names:
Line 74: Line 94:
 
  <nowiki>SELECT count(UUID) AS 'Regions' FROM opensim.regions;</nowiki>
 
  <nowiki>SELECT count(UUID) AS 'Regions' FROM opensim.regions;</nowiki>
  
* Count the regions that have parcels:
 
<nowiki>SELECT count(DISTINCT regionUUID) FROM opensim.land;</nowiki>
 
  
* Count the total number of parcels:
 
<nowiki>SELECT count(UUID) AS 'Parcels' FROM opensim.land;</nowiki>
 
  
 
+
== 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 replace(assetID, '-', '')  
 
NOT IN (SELECT hex(id) FROM opensim.assets);</nowiki>
 
NOT IN (SELECT hex(id) FROM opensim.assets);</nowiki>
 +
 +
* Check if there's leftover primshapes without a entry in the prim table and delete them
 +
 +
<nowiki>delete from primshapes using primshapes left join prims on prims.uuid=primshapes.uuid where prims.uuid is null; </nowiki>

Revision as of 05:33, 26 April 2019


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 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 hex(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
delete from primshapes using primshapes left join prims on prims.uuid=primshapes.uuid where prims.uuid is null; 
Personal tools
General
About This Wiki