Database:Documentation

From OpenSimulator

(Difference between revisions)
Jump to: navigation, search
(OpenSimulator 0.9.x onwards)
m (OpenSimulator 0.9.x onwards)
(19 intermediate revisions by 3 users not shown)
Line 1: Line 1:
 +
__NOTOC__
 +
{{Portal_Part|Quicklinks and Translations|
 
{{Quicklinks}}
 
{{Quicklinks}}
 +
}}
  
= Introduction =
+
{{Portal_Part|Introduction|
 
+
 
This page documents the databases tables used in OpenSimulator.
 
This page documents the databases tables used in OpenSimulator.
  
Line 14: Line 16:
  
 
Note that some of these tables may be relics, some may be planned for future use, and some may see heavy use but have columns which are relics, not yet properly updated or not yet used at all.
 
Note that some of these tables may be relics, some may be planned for future use, and some may see heavy use but have columns which are relics, not yet properly updated or not yet used at all.
 
+
}}
= Code =
+
{{Portal_Part|Code|
 
There is a common package for database code, OpenSim.Data and each database has its own package (e.g. OpenSim.Data.MySQL).
 
There is a common package for database code, OpenSim.Data and each database has its own package (e.g. OpenSim.Data.MySQL).
  
 
Each database package has to supply implementations for the interfaces in OpenSim.Data.  For instance, IAssetData is the asset interface, used by OpenSimulator to store and retrieve asset data.
 
Each database package has to supply implementations for the interfaces in OpenSim.Data.  For instance, IAssetData is the asset interface, used by OpenSimulator to store and retrieve asset data.
 
+
}}
 
= Organization =
 
= Organization =
 
== OpenSimulator 0.9.x onwards ==
 
== OpenSimulator 0.9.x onwards ==
Line 33: Line 35:
 
* [[Presence]] - Region and session information for currently logged in users.  Used for IM routing amongst other things.
 
* [[Presence]] - Region and session information for currently logged in users.  Used for IM routing amongst other things.
 
* [[UserAccounts]] - User account information, such as name, user level and e-mail. Just local accounts.
 
* [[UserAccounts]] - User account information, such as name, user level and e-mail. Just local accounts.
* [[assets]]</td><td>Grid assets including such things as textures and sounds
+
* [[assets]] - Grid assets including such things as textures and sounds
* [[auth]]</td><td>User authentication information</td>
+
* [[auth]] - User authentication information
* [[inventoryfolders]]</td><td>Inventory folder-related details except for the contents
+
* [[inventoryfolders]] - Inventory folder-related details except for the contents
* [[inventoryitems]] - points back to [[inventoryfolders]]
+
* [[inventoryitems]] - All inventory item details (including folder associations to [[inventoryfolders]]) except contents - contents are referenced in the [[assets]] table
* [[inventoryitems]] - All inventory item details (including folder associations) except contents - contents are referenced in the [[assets]] table
+
 
* [[Regions_(database_table)|regions]] - Regions known to the grid
 
* [[Regions_(database_table)|regions]] - Regions known to the grid
 
* [[tokens]] - unused
 
* [[tokens]] - unused
Line 53: Line 54:
 
* [[Database:Primshapes|primshapes]] - Properties of all rezzed prims
 
* [[Database:Primshapes|primshapes]] - Properties of all rezzed prims
 
* [[regionban]] - Per-Region ban list
 
* [[regionban]] - Per-Region ban list
* [[Database:regionsettings|regionsettings]]</td><td>Per-Region settings
+
* [[Database:regionsettings|regionsettings]] Per-Region settings
 
* [[regionwindlight]] - Windlight region settings
 
* [[regionwindlight]] - Windlight region settings
 
* [[Terrain_(database_table)|terrain]] - Terrain mesh revision history
 
* [[Terrain_(database_table)|terrain]] - Terrain mesh revision history
Line 66: Line 67:
 
* [[estate settings]] - Settings for Estates
 
* [[estate settings]] - Settings for Estates
 
* [[estate users]] - Users allowed on the Estate
 
* [[estate users]] - Users allowed on the Estate
* [[estateban]] - Users banned from the Estate{{Div col end}}
+
* [[estateban]] - Users banned from the Estate
 +
{{Div col end}}
 
}}
 
}}
  
Line 72: Line 74:
 
|}
 
|}
  
== OpenSimulator 0.8.1 onwards ==
 
TODO
 
 
== OpenSimulator 0.7.2 onwards ==
 
== OpenSimulator 0.7.2 onwards ==
  
=== Common ===
+
{{Portal_Part|Code|
 
Migrations supports automatic upgrade to new database formats.
 
Migrations supports automatic upgrade to new database formats.
  
=== Services ===
+
}}
<table>
+
{| width="100%" cellpadding=10 margin: 10px 10px 10px 10px;
<tr>
+
|-
  <td>[[Avatars]]</td><td>Persistence of the visual appearance of avatars between logins and attached objects</td>
+
|valign="top" |
</tr>
+
{{Portal_Part|Robust|
<tr>
+
{{Div col|colwidth=12em|rules=yes|gap=1em}}
  <td>[[Friends]]</td><td>A list of an avatars friends, friendship requests, and permissions granted to friends</td>
+
* [[Avatars]] Persistence of the visual appearance of avatars between logins and attached objects
</tr>
+
* [[Friends]] A list of an avatars friends, friendship requests, and permissions granted to friends
<tr>
+
* [[GridUser]] Information about a user in this particular grid, for everyone who uses the grid, local and foreign. This includes their home region, last region, online status and last login/logout.  This is distinct from auth information
  <td>[[GridUser]]</td><td>Information about a user in this particular grid, for everyone who uses the grid, local and foreign. This includes their home region, last region, online status and last login/logout.  This is distinct from auth information</td>
+
* [[Presence]] - Region and session information for currently logged in users.  Used for IM routing amongst other things.
</tr>
+
* [[UserAccounts]] User account information, such as name, user level and e-mail. Just local accounts.
<tr>
+
* [[assets]] Grid assets including such things as textures and sounds
  <td>[[Presence]]</td>
+
* [[auth]] User authentication information
  <td>Region and session information for currently logged in users.  Used for IM routing amongst other things.</td>
+
* [[inventoryfolders]] Inventory folder-related details *except contents*
</tr>
+
* [[inventoryitems]] holds actual content and points back to * [[inventoryfolders]]
<tr>
+
* [[inventoryitems]] All inventory item details (including folder associations) except contents - contents are referenced in the * [[assets]] table*
  <td>[[UserAccounts]]</td><td>User account information, such as name, user level and e-mail. Just local accounts.</td>
+
* [[Regions_(database_table)|regions]] Regions known to the grid
</tr>
+
tokens table is unused
<tr>
+
{{Div col end}}
  <td>[[assets]]</td><td>Grid assets including such things as textures and sounds</td>
+
}}
</tr>
+
<tr>
+
  <td>[[auth]]</td><td>User authentication information</td>
+
</tr>
+
<tr>
+
  <td>[[inventoryfolders]]</td><td>Inventory folder-related details *except contents*</td>
+
</tr>
+
<tr>
+
  <td>&nbsp;</td><td>Contents are in the [[inventoryitems]] table and point back to [[inventoryfolders]]</td>
+
</tr>
+
<tr>
+
  <td>[[inventoryitems]]</td><td>All inventory item details (including folder associations) except contents - contents are referenced in the [[assets]] table</td>
+
</tr>
+
<tr>
+
  <td>[[Regions_(database_table)|regions]]<br /></td><td>Regions known to the grid</td>
+
</tr>
+
<tr>
+
  <td>tokens</td><td>Unused.</td>
+
</tr>
+
</table>
+
  
=== Simulator ===
+
|valign="top" width="50%"|
<table>
+
<tr>
+
<td>[[estate groups]]</td><td>Narrowly purposed at present. Appears to associate UUIDs under an Estate ID</td>
+
</tr>
+
<tr>
+
<td>[[estate managers]]</td><td>Associates avatar UUIDs with Estate ID</td>
+
</tr>
+
<tr>
+
<td>[[estate map]]<br /><td>Associates Regions with Estates By Estate ID</td>
+
</tr>
+
<tr>
+
<td>[[estate settings]]</td><td>Settings for Estates</td>
+
</tr>
+
<tr>
+
<td>[[estate users]]</td><td>Users allowed on the Estate</td>
+
</tr>
+
<tr>
+
<td>[[estateban]]</td><td>Users banned from the Estate</td>
+
</tr>
+
<tr>
+
<td>[[Land_(database_table)|land]]</td><td>Parcel Properties</td>
+
</tr>
+
<tr>
+
<td>[[landaccesslist]]</td><td>Parcel Access List</td>
+
</tr>
+
<tr>
+
<td>[[primitems]]</td><td>Contains the prim's inventory, which is not at all unlike the avatars'<br />inventory with the exception of folders</td>
+
</tr>
+
<tr>
+
<td>[[Prims_(database_table)|prims]]</td><td>All rezzed prim details *except shape and contents*</td>
+
</tr>
+
<tr>
+
<td>[[Database:Primshapes|primshapes]]</td><td>Properties of all rezzed prims</td>
+
</tr>
+
<tr>
+
<td>regionban</td><td>Per-Region ban list</td>
+
</tr>
+
<tr>
+
<td>[[Database:regionsettings|regionsettings]]</td><td>Per-Region settings</td>
+
</tr>
+
<tr>
+
<td>regionwindlight</td><td>Windlight region settings</td>
+
</tr>
+
<tr>
+
<td>[[Terrain_(database_table)|terrain]]</td><td>Terrain mesh revision history</td>
+
</tr>
+
</table>
+
  
== Older information ==
+
{{Portal_Part|Simulator|
 +
{{Div col|colwidth=12em|rules=yes|gap=1em}}
 +
* [[Land_(database_table)|land]] Parcel Properties
 +
* [[landaccesslist]] Parcel Access List
 +
* [[primitems]] Contains the prim's inventory, which is not at all unlike the avatars'<br />inventory with the exception of folders
 +
* [[Prims_(database_table)|prims]] All rezzed prim details *except shape and contents*
 +
* [[Database:Primshapes|primshapes]] Properties of all rezzed prims
 +
* regionban Per-Region ban list
 +
* [[Database:regionsettings|regionsettings]] Per-Region settings
 +
* regionwindlight - Windlight region settings
 +
* [[Terrain_(database_table)|terrain]] Terrain mesh revision history
  
This mainly pertains to OpenSimulator 0.6.9.
+
{{Div col end}}
 +
}}
  
<table>
+
{{Portal_Part|Estates|
<tr>
+
{{Div col|colwidth=12em|rules=yes|gap=1em}}
<td colspan="2">'''Services:'''</td>
+
* [[estate groups]] Narrowly purposed at present. Appears to associate UUIDs under an Estate ID
<tr>
+
* [[estate managers]] Associates avatar UUIDs with Estate ID
<td colspan="2">&nbsp;</td>
+
* [[estate map]] Associates Regions with Estates By Estate ID
</tr>
+
* [[estate settings]] Settings for Estates
<tr>
+
* [[estate users]] Users allowed on the Estate
<td>[[agents]]</td><td>Records agent (Avatar/User) login details</td>
+
* [[estateban]] Users banned from the Estate
<tr>
+
}}
<td>[[assets]]</td><td>Comprehensive grid assets including textures and avatar inventories</td>
+
</tr>
+
<tr>
+
<td>[[avatarappearance]]</td><td>Persistence of the visual appearance of avatars between logins</td>
+
</tr>
+
<tr>
+
<td>[[avatarattachments]]</td><td>Items attached to avatars</td>
+
</tr>
+
<tr>
+
<td>[[inventoryfolders]]</td><td>Inventory folder-related details *except contents*</td>
+
</tr>
+
<tr>
+
<td>&nbsp;</td><td>Contents are in the [[inventoryitems]] table and point back to [[inventoryfolders]]</td>
+
</tr>
+
<tr>
+
<td>[[inventoryitems]]</td><td>All inventory item details (including folder associations) except contents</td>
+
</tr>
+
<tr>
+
<td>&nbsp;</td><td>Contents are referenced in the [[assets]] table</td>
+
</tr>
+
<tr>
+
<td>[[migrations]]</td><td>Supports 'automagic' migration to new database formats</td>
+
</tr>
+
<tr>
+
<td>[[Regions_(database_table)|regions]]</td><td>Regions known to the grid</td>
+
</tr>
+
<tr>
+
<td>[[userfriends]]</td><td>Friends list registration</td>
+
</tr>
+
<tr>
+
<td>[[users]]</td><td>Stored user profile</td>
+
</tr>
+
</table>
+
<br />
+
<br />
+
  
<table>
+
|}
<tr>
+
 
<td colspan="2">'''Simulator:'''</td>
+
== Pre 0.6.9 ==
<tr>
+
{{Portal_Part|Code|This mainly pertains to OpenSimulator 0.6.9.}}
<td colspan="2">&nbsp;</td>
+
{| width="100%" cellpadding=10 margin: 10px 10px 10px 10px;
</tr>
+
|-
<tr>
+
|valign="top" |
<td>[[estate groups]]</td><td>Narrowly purposed at present. Appears to associate UUIDs under an Estate ID</td>
+
{{Portal_Part|Robust|
</tr>
+
{{Div col|colwidth=12em|rules=yes|gap=1em}}
<tr>
+
*[[agents]] Records agent (Avatar/User) login details
<td>[[estate managers]]</td><td>Associates UUIDs with Estate ID</td>
+
*[[assets]] Comprehensive grid assets including textures and avatar inventories
</tr>
+
*[[avatarappearance]] Persistence of the visual appearance of avatars between logins
<tr>
+
*[[avatarattachments]] Items attached to avatars
<td>[[estate map]]</td><td>Associates Regions with Estates By Estate ID</td>
+
*[[inventoryfolders]] Inventory folder-related details *except contents*, Contents are in the [[inventoryitems]] table and point back to
</tr>
+
*[[inventoryitems]] All inventory item details (including folder associations) except contents are referenced in the [[assets]] table
<tr>
+
*[[migrations]] Supports 'automagic' migration to new database formats
<td>[[estate settings]]</td><td>Settings for Estates</td>
+
*[[Regions_(database_table)|regions]] Regions known to the grid
</tr>
+
*[[userfriends]] Friends list registration
<tr>
+
*[[users]] Stored user profiles
<td>[[estate users]]</td><td>Users allowed on the Estate</td>
+
{{Div col end}}
</tr>
+
}}
<tr>
+
 
<td>[[estateban]]</td><td>Users banned from the Estate</td>
+
|valign="top" width="50%"|
</tr>
+
 
<tr>
+
{{Portal_Part|Simulator|
<td>[[land]]</td><td>Parcel Properties</td>
+
{{Div col|colwidth=12em|rules=yes|gap=1em}}
</tr>
+
* [[land]] Parcel Properties
<tr>
+
* [[landaccesslist]] Parcel Access List
<td>[[landaccesslist]]</td><td>Parcel Access List</td>
+
* [[migrations]] Supports 'automagic' upgrade to new database formats
</tr>
+
* [[primitems]] Contains the prim's inventory, which is not at all unlike the avatars inventory with the exception of folders
<tr>
+
* [[Prims_(database_table)|prims]] All rezzed prim details *except shape and contents*
<td>[[migrations]]</td><td>Supports 'automagic' upgrade to new database formats</td>
+
* [[Database:Primshapes|primshapes]] Properties of all rezzed prims
</tr>
+
* [[regionban]] Per-Region ban list
<tr>
+
* [[regionsettings]] Per-Region settings
<td>[[primitems]]</td><td>Contains the prim's inventory, which is not at all unlike the avatars</td>
+
* [[terrain]] Terrain mesh revision history
</tr>
+
{{Div col end}}
<tr>
+
}}
<td>&nbsp;</td><td>inventory with the exception of folders</td>
+
 
</tr>
+
{{Portal_Part|Estates|
<tr>
+
{{Div col|colwidth=12em|rules=yes|gap=1em}}
<td>[[Prims_(database_table)|prims]]</td><td>All rezzed prim details *except shape and contents*</td>
+
* [[estate groups]] Narrowly purposed at present. Appears to associate UUIDs under an Estate ID
</tr>
+
* [[estate managers]] Associates UUIDs with Estate ID
<tr>
+
* [[estate map]] Associates Regions with Estates By Estate ID
<td>[[Database:Primshapes|primshapes]]</td><td>Properties of all rezzed prims</td>
+
* [[estate settings]] Settings for Estates
</tr>
+
* [[estate users]] Users allowed on the Estate
<tr>
+
* [[estateban]] Users banned from the Estate
<td>[[regionban]]</td><td>Per-Region ban list</td>
+
}}
</tr>
+
 
<tr>
+
|}
<td>[[regionsettings]]</td><td>Per-Region settings</td>
+
</tr>
+
<tr>
+
<td>[[terrain]]</td><td>Terrain mesh revision history</td>
+
</tr>
+
</table>
+
  
 
[[Category:Database]]
 
[[Category:Database]]

Revision as of 16:10, 6 May 2022

Quicklinks and Translations
Introduction

This page documents the databases tables used in OpenSimulator.

Tables are split up into Common, Services and Simulator.

On a grid, the grid services will contain the Services and Common tables, whilst each simulator instance will access the Simulator tables.

A standalone will contain Common, Services and Simulator tables, with the exception of the regions table, which is not currently persisted for standalones.

SQLite is generally not considered appropriate for this use case and will not be covered here; however the table structures are very similar and most of what is here will be useful in that context as well.

Note that some of these tables may be relics, some may be planned for future use, and some may see heavy use but have columns which are relics, not yet properly updated or not yet used at all.

Code

There is a common package for database code, OpenSim.Data and each database has its own package (e.g. OpenSim.Data.MySQL).

Each database package has to supply implementations for the interfaces in OpenSim.Data. For instance, IAssetData is the asset interface, used by OpenSimulator to store and retrieve asset data.

Organization

OpenSimulator 0.9.x onwards

Robust
  • Avatars - Persistence of the visual appearance of avatars between logins and attached objects
  • Friends - A list of an avatars friends, friendship requests, and permissions granted to friends
  • GridUser - Information about a user in this particular grid, for everyone who uses the grid, local and foreign. This includes their home region, last region, online status and last login/logout. This is distinct from auth information
  • Presence - Region and session information for currently logged in users. Used for IM routing amongst other things.
  • UserAccounts - User account information, such as name, user level and e-mail. Just local accounts.
  • assets - Grid assets including such things as textures and sounds
  • auth - User authentication information
  • inventoryfolders - Inventory folder-related details except for the contents
  • inventoryitems - All inventory item details (including folder associations to inventoryfolders) except contents - contents are referenced in the assets table
  • regions - Regions known to the grid
  • tokens - unused
Simulator
  • land - Parcel Properties
  • landaccesslist - Parcel Access List
  • primitems - Contains the prim's inventory, which is not at all unlike the avatars'
    inventory with the exception of folders
  • prims - All rezzed prim details *except shape and contents*
  • primshapes - Properties of all rezzed prims
  • regionban - Per-Region ban list
  • regionsettings Per-Region settings
  • regionwindlight - Windlight region settings
  • terrain - Terrain mesh revision history
Estates


OpenSimulator 0.7.2 onwards

Code

Migrations supports automatic upgrade to new database formats.

Robust
  • Avatars Persistence of the visual appearance of avatars between logins and attached objects
  • Friends A list of an avatars friends, friendship requests, and permissions granted to friends
  • GridUser Information about a user in this particular grid, for everyone who uses the grid, local and foreign. This includes their home region, last region, online status and last login/logout. This is distinct from auth information
  • Presence - Region and session information for currently logged in users. Used for IM routing amongst other things.
  • UserAccounts User account information, such as name, user level and e-mail. Just local accounts.
  • assets Grid assets including such things as textures and sounds
  • auth User authentication information
  • inventoryfolders Inventory folder-related details *except contents*
  • inventoryitems holds actual content and points back to * inventoryfolders
  • inventoryitems All inventory item details (including folder associations) except contents - contents are referenced in the * assets table*
  • regions Regions known to the grid
  • tokens table is unused
Simulator
  • land Parcel Properties
  • landaccesslist Parcel Access List
  • primitems Contains the prim's inventory, which is not at all unlike the avatars'
    inventory with the exception of folders
  • prims All rezzed prim details *except shape and contents*
  • primshapes Properties of all rezzed prims
  • regionban Per-Region ban list
  • regionsettings Per-Region settings
  • regionwindlight - Windlight region settings
  • terrain Terrain mesh revision history
Estates

Pre 0.6.9

Code
This mainly pertains to OpenSimulator 0.6.9.
Robust
  • agents Records agent (Avatar/User) login details
  • assets Comprehensive grid assets including textures and avatar inventories
  • avatarappearance Persistence of the visual appearance of avatars between logins
  • avatarattachments Items attached to avatars
  • inventoryfolders Inventory folder-related details *except contents*, Contents are in the inventoryitems table and point back to
  • inventoryitems All inventory item details (including folder associations) except contents are referenced in the assets table
  • migrations Supports 'automagic' migration to new database formats
  • regions Regions known to the grid
  • userfriends Friends list registration
  • users Stored user profiles
Simulator
  • land Parcel Properties
  • landaccesslist Parcel Access List
  • migrations Supports 'automagic' upgrade to new database formats
  • primitems Contains the prim's inventory, which is not at all unlike the avatars inventory with the exception of folders
  • prims All rezzed prim details *except shape and contents*
  • primshapes Properties of all rezzed prims
  • regionban Per-Region ban list
  • regionsettings Per-Region settings
  • terrain Terrain mesh revision history
Estates
Personal tools
General
For Administrators
For Developers
For Creators
For Grid Users
About This Wiki
Tools