Editing OpenSimulator Internals/Useful Queries (section)

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