Editing OpenSimulator Internals/Useful Queries (section)

== Grid Database (osimdev_robust) ==

=== Users ===

Find a user by first name:

 SELECT PrincipalID, FirstName, LastName, Email, UserLevel,
        FROM_UNIXTIME(Created) AS created
 FROM UserAccounts
 WHERE FirstName LIKE 'Test%';

Show login history by name:

 SELECT FROM_UNIXTIME(Login) AS last_login,
        FROM_UNIXTIME(Logout) AS last_logout,
        Online
 FROM GridUser
 WHERE UserID = (
     SELECT PrincipalID FROM UserAccounts
     WHERE FirstName = 'Test' AND LastName = 'User'
 );

Show home and last location by name:

 SELECT HomePosition, HomeLookAt, LastPosition, LastLookAt,
        HomeRegionID, LastRegionID
 FROM GridUser
 WHERE UserID = (
     SELECT PrincipalID FROM UserAccounts
     WHERE FirstName = 'Test' AND LastName = 'User'
 );

Who is currently online:

 SELECT UserID, RegionID, LastSeen FROM Presence;

=== Avatar Appearance ===

Show all appearance data for a user by name:

 SELECT Name, Value
 FROM Avatars
 WHERE PrincipalID = (
     SELECT PrincipalID FROM UserAccounts
     WHERE FirstName = 'Test' AND LastName = 'User'
 )
 ORDER BY Name;

Show worn wearables only:

 SELECT Name, Value
 FROM Avatars
 WHERE PrincipalID = (
     SELECT PrincipalID FROM UserAccounts
     WHERE FirstName = 'Test' AND LastName = 'User'
 )
 AND Name LIKE 'Wearable%';

Show attachment points only:

 SELECT Name, Value
 FROM Avatars
 WHERE PrincipalID = (
     SELECT PrincipalID FROM UserAccounts
     WHERE FirstName = 'Test' AND LastName = 'User'
 )
 AND Name LIKE '_ap_%';

=== Inventory ===

Count inventory items by asset type for a user:

 SELECT assetType, COUNT(*) AS count
 FROM inventoryitems
 WHERE avatarID = (
     SELECT PrincipalID FROM UserAccounts
     WHERE FirstName = 'Test' AND LastName = 'User'
 )
 GROUP BY assetType
 ORDER BY count DESC;

Find an inventory item by name:

 SELECT inventoryName, assetType, assetID, parentFolderID
 FROM inventoryitems
 WHERE avatarID = (
     SELECT PrincipalID FROM UserAccounts
     WHERE FirstName = 'Test' AND LastName = 'User'
 )
 AND inventoryName LIKE '%house%';

List top-level inventory folders for a user:

 SELECT folderName, type, folderID
 FROM inventoryfolders
 WHERE agentID = (
     SELECT PrincipalID FROM UserAccounts
     WHERE FirstName = 'Test' AND LastName = 'User'
 )
 AND parentFolderID = (
     SELECT folderID FROM inventoryfolders
     WHERE agentID = (
         SELECT PrincipalID FROM UserAccounts
         WHERE FirstName = 'Test' AND LastName = 'User'
     )
     AND type = 8
 )
 ORDER BY folderName;

=== Assets ===

Count all assets by type:

 SELECT assetType, COUNT(*) AS count
 FROM assets
 GROUP BY assetType
 ORDER BY count DESC;

Find assets created by a user:

 SELECT id, name, assetType, FROM_UNIXTIME(create_time) AS created
 FROM assets
 WHERE CreatorID = (
     SELECT PrincipalID FROM UserAccounts
     WHERE FirstName = 'Test' AND LastName = 'User'
 )
 ORDER BY create_time DESC;

Find an asset by name:

 SELECT id, name, assetType, FROM_UNIXTIME(create_time) AS created
 FROM assets
 WHERE name LIKE '%note%';

=== Friends ===

Show friends for a user:

 SELECT Friend, Flags
 FROM Friends
 WHERE PrincipalID = (
     SELECT PrincipalID FROM UserAccounts
     WHERE FirstName = 'Test' AND LastName = 'User'
 );

=== Tokens ===

The <code>tokens</code> table exists in the schema but is not used as of 0.9.x.
Do not rely on its contents.

=== Regions ===

'''Note:''' In standalone deployments the <code>regions</code> table is not
written. Region data exists only in memory for the duration of the process.
These queries return no rows on a standalone.


List all registered regions:

 SELECT regionName,
        locX/256 AS coord_x, locY/256 AS coord_y,
        sizeX, sizeY, flags,
        FROM_UNIXTIME(last_seen) AS last_seen
 FROM regions;

Check if a region is online:

 SELECT regionName, flags & 4 AS online
 FROM regions
 WHERE regionName = 'T1';

----