Useful Queries - Revision history

Ferd Frederix: /* Error Checking */ add grid version of inv check

2022-10-21T02:50:21Z

‎Error Checking: add grid version of inv check

Kyle Brynner: /* Listing things */

2022-07-03T17:50:06Z

‎Listing things

Kyle Brynner: /* Listing things */

2022-07-03T17:30:46Z

‎Listing things

Kyle Brynner: /* Listing things */

2022-07-03T17:17:11Z

‎Listing things

CodyCooper: /* Listing things */

2019-04-26T12:33:38Z

‎Listing things

CodyCooper: /* Listing things */

2019-04-26T12:32:48Z

‎Listing things

CodyCooper at 12:31, 26 April 2019

2019-04-26T12:31:22Z

CodyCooper at 12:30, 26 April 2019

2019-04-26T12:30:53Z

CodyCooper at 21:20, 9 March 2019

2019-03-09T21:20:14Z

CodyCooper at 21:19, 9 March 2019

2019-03-09T21:19:16Z

@@ Line 101: / Line 101: @@
 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
+* Check if there's leftover primshapes without a entry in the prim table and delete them - standalone
   <nowiki>delete from primshapes using primshapes left join prims on prims.uuid=primshapes.uuid where prims.uuid is null; </nowiki>

@@ Line 24: / Line 24: @@
     <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):
-  <nowiki>SELECT regionName as 'Regions' FROM opensim.regions;</nowiki>
+  <nowiki>SELECT regionName as 'Regions' FROM 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.
@@ Line 30: / Line 30: @@
 * 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 assets WHERE id = replace('ef9c5edf-a9f8-4310-8048-97894453a614', '-', '');</nowiki>
 * More complicated version - List inventory entries with a particular name:

@@ Line 22: / Line 22: @@
 * 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' FROM UserAccounts INNER JOIN GridUser ON UserAccounts.PrincipalID = GridUser.UserID WHERE GridUser.Online = 'True';</nowiki>
+    <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):
   <nowiki>SELECT regionName as 'Regions' FROM opensim.regions;</nowiki>

@@ Line 22: / Line 22: @@
 * 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>
+    <nowiki> SELECT concat(FirstName, ' ', LastName) AS 'Online' 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):
   <nowiki>SELECT regionName as 'Regions' FROM opensim.regions;</nowiki>

@@ Line 13: / Line 13: @@
 * 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
+    <nowiki>SELECT friends.PrincipalID, CONCAT(useraccounts.FirstName, " ", useraccounts.LastName) AS "Friend" FROM friends,useraccounts WHERE
-UNION
+   friends.Friend = "054f4add-305d-48f3-a998-c5db4c8e15c9" AND useraccounts.PrincipalID = friends.PrincipalID
-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```
+   UNION

@@ Line 13: / Line 13: @@
 * 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>EXPLAIN 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
+    ```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>
+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):
@@ Line 59: / Line 61: @@
   +----------------------------------------------------+--------------------------------------------------------+
 rows in set (0.00 sec)
 == Counting things ==

← Older revision		Revision as of 12:30, 26 April 2019
Line 11:		Line 11:

	== Listing things ==		== 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>EXPLAIN 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):		* List the users presently logged in (may be inflated due to users not getting logged out properly, whether due to bugs or crashes):