Editing OpenSimulator Internals/Useful Queries


= OpenSimulator Internals/Useful Queries =

== Overview ==

These queries are for grid administrators and developers working directly with MariaDB. Correlated subqueries are used to look up UUIDs by name so you don't have to remember them.

All queries assume osimdev_robust for grid data and osimdev_t1 for region data. Substitute your region database name as needed. Examples use "Test User" as the avatar name -- substitute as required.

----

== Connection ==

 mysql -u opensim -p osimdev_robust
 USE osimdev_t1;

----

== 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';

----

== Region Database (osimdev_t1) ==

=== Objects ===

List all objects with world position (root prims only):

 SELECT Name, UUID, GroupPositionX, GroupPositionY, GroupPositionZ
 FROM prims
 WHERE UUID = SceneGroupID
 ORDER BY Name;

Count prims per object (root prims only, sorted by size):

 SELECT p.Name, p.SceneGroupID,
        p.GroupPositionX, p.GroupPositionY, p.GroupPositionZ,
        COUNT(*) AS prim_count
 FROM prims p
 WHERE p.UUID = p.SceneGroupID
 GROUP BY p.SceneGroupID, p.Name, p.GroupPositionX, p.GroupPositionY, p.GroupPositionZ
 ORDER BY prim_count DESC;

Find an object by name:

 SELECT UUID, GroupPositionX, GroupPositionY, GroupPositionZ
 FROM prims
 WHERE UUID = SceneGroupID
 AND Name LIKE '%house%';

List all prims in a named object:

 SELECT UUID, Name, LinkNumber, PositionX, PositionY, PositionZ
 FROM prims
 WHERE SceneGroupID = (
     SELECT UUID FROM prims
     WHERE UUID = SceneGroupID AND Name LIKE '%Brown House%'
     LIMIT 1
 )
 ORDER BY LinkNumber;

Find objects owned by a specific user:

 SELECT Name, UUID, GroupPositionX, GroupPositionY, GroupPositionZ
 FROM prims
 WHERE UUID = SceneGroupID
 AND OwnerID = (
     SELECT PrincipalID FROM osimdev_robust.UserAccounts
     WHERE FirstName = 'Test' AND LastName = 'User'
 )
 ORDER BY Name;

=== Object Contents (Task Inventory) ===

Show contents of a named object:

 SELECT pi.name, pi.assetType, pi.assetID
 FROM primitems pi
 WHERE pi.primID IN (
     SELECT UUID FROM prims
     WHERE SceneGroupID = (
         SELECT UUID FROM prims
         WHERE UUID = SceneGroupID AND Name LIKE '%Box%'
         LIMIT 1
     )
 );

Count task inventory items by type across all objects:

 SELECT pi.assetType, COUNT(*) AS count
 FROM primitems pi
 GROUP BY pi.assetType
 ORDER BY count DESC;

Show all objects that contain scripts:

 SELECT DISTINCT p.Name, p.UUID, p.GroupPositionX, p.GroupPositionY, p.GroupPositionZ
 FROM prims p
 JOIN primitems pi ON pi.primID = p.UUID
 WHERE p.UUID = p.SceneGroupID
 AND pi.assetType = 10
 ORDER BY p.Name;

=== Prim Shape ===

Show shape for a named object:

 SELECT p.Name, p.LinkNumber,
        ps.ScaleX, ps.ScaleY, ps.ScaleZ,
        ps.PCode, ps.Shape, ps.ProfileCurve, ps.PathCurve
 FROM primshapes ps
 JOIN prims p ON ps.UUID = p.UUID
 WHERE p.SceneGroupID = (
     SELECT UUID FROM prims
     WHERE UUID = SceneGroupID AND Name = 'Object'
     LIMIT 1
 )
 ORDER BY p.LinkNumber;

=== Persistence State ===

Find objects with pending changes (not yet persisted):

 SELECT Name, UUID, GroupPositionX, GroupPositionY, GroupPositionZ
 FROM prims
 WHERE UUID = SceneGroupID
 AND RezzerID IS NOT NULL
 ORDER BY Name;

=== Land ===

Show all parcels:

 SELECT LocalLandID, Name, OwnerUUID, Area, LandStatus,
        UserLocationX, UserLocationY, UserLocationZ
 FROM land;

=== Terrain ===

Check terrain revision and blob size:

 SELECT RegionUUID, Revision, LENGTH(Heightfield) AS bytes
 FROM terrain;

=== Estate ===

All estate tables are in the region database (osimdev_t1), not the grid database.

Show estate settings:

 SELECT EstateID, EstateName, EstateOwner,
        PublicAccess, AllowVoice, DenyMinors,
        AllowDirectTeleport, AllowSetHome, AllowLandmark,
        AllowParcelChanges, AllowEnviromentOverride,
        FixedSun, SunPosition, UseGlobalTime,
        DenyAnonymous, DenyIdentified, DenyTransacted
 FROM estate_settings;

Show which region belongs to which estate:

 SELECT r.regionName, m.EstateID, e.EstateName
 FROM estate_map m
 JOIN estate_settings e ON e.EstateID = m.EstateID
 JOIN osimdev_robust.regions r ON r.uuid = m.RegionID;

List estate managers by name:

 SELECT e.EstateName, em.uuid AS manager_uuid,
        ua.FirstName, ua.LastName
 FROM estate_managers em
 JOIN estate_settings e ON e.EstateID = em.EstateID
 LEFT JOIN osimdev_robust.UserAccounts ua ON ua.PrincipalID = em.uuid
 ORDER BY e.EstateName, ua.LastName;

List allowed groups:

 SELECT e.EstateName, eg.uuid AS group_uuid
 FROM estate_groups eg
 JOIN estate_settings e ON e.EstateID = eg.EstateID
 ORDER BY e.EstateName;

List allowed users:

 SELECT e.EstateName, eu.uuid AS user_uuid,
        ua.FirstName, ua.LastName
 FROM estate_users eu
 JOIN estate_settings e ON e.EstateID = eu.EstateID
 LEFT JOIN osimdev_robust.UserAccounts ua ON ua.PrincipalID = eu.uuid
 ORDER BY e.EstateName, ua.LastName;

List ban entries:

 SELECT e.EstateName,
        eb.bannedUUID, eb.bannedIp, eb.bannedIpHostMask,
        eb.banningUUID, FROM_UNIXTIME(eb.banTime) AS banned_at
 FROM estateban eb
 JOIN estate_settings e ON e.EstateID = eb.EstateID
 ORDER BY e.EstateName, eb.banTime DESC;

== Cross-Database Queries ==

Objects owned by a named user (cross-database):

 SELECT p.Name, p.UUID, p.GroupPositionX, p.GroupPositionY, p.GroupPositionZ
 FROM osimdev_t1.prims p
 WHERE p.UUID = p.SceneGroupID
 AND p.OwnerID = (
     SELECT PrincipalID FROM osimdev_robust.UserAccounts
     WHERE FirstName = 'Test' AND LastName = 'User'
 )
 ORDER BY p.Name;

Inventory items for an object currently in the region:

 SELECT pi.name, pi.assetType, a.name AS asset_name
 FROM osimdev_t1.primitems pi
 JOIN osimdev_robust.assets a ON a.id = pi.assetID
 WHERE pi.primID = (
     SELECT UUID FROM osimdev_t1.prims
     WHERE UUID = SceneGroupID AND Name LIKE '%Box%'
     LIMIT 1
 );

----

== Conversions ==

Unix timestamp to datetime:

 SELECT FROM_UNIXTIME(1782133981);

Region metres to region coordinates:

 SELECT locX/256 AS coord_x, locY/256 AS coord_y FROM regions;

Asset type numbers:

 0  = texture
 2  = sound
 3  = landmark
 5  = clothing
 6  = object
 7  = notecard
 10 = script (LSL)
 13 = body part
 20 = animation
 24 = link (inventory link)
 25 = mesh

----

== See Also ==

* [[OpenSimulator Internals/Databases]]
* [[OpenSimulator Internals/Walkthroughs/Avatar Rezzes Box]]
* [[OpenSimulator Internals/Walkthroughs/Avatar Derezzes Object]]
* [[OpenSimulator Internals/Walkthroughs/Avatar Acquires Inventory]]
* [[OpenSimulator Internals/Data Dictionaries]]