| Development Kit Table of Contents | |
File names and directory names | Bold (startup.htm) |
Command lines, output samples, option names | Typewriter font (kill 17016) |
Key terms | Italics (server world) |
OverviewArchitectureWorldServer supports the registration, authentication, and communication needs for 3D chat clients. Typically the client is intended to view a 3D multi-user community where users can choose avatars (3D representations of themselves) and navigate through a 3D environment, while chatting with other users. The WorldServer suite consists of two primary types of server processes: the UserServer and the RoomServer.
The above diagram depicts an example Worlds application consisting of 2 clients, a WorldServer (containing 2 RoomServers and a UserServer), and an Oracle/SQL*Plus database server. This example application will be used later to illustrate WorldServer operation. First we describe how responsibilities are divided between RoomServers and UserServers. The RoomServerThe RoomServer can operate in stand-alone mode to handle an entire world when no user authentication is required. For larger worlds, or when user authentication and registration are required, multiple RoomServers can be used in conjunction with a UserServer. If shared state (shared objects) is desired, the RoomServer must run in conjunction with an Oracle database that will store persistent room data. The RoomServer performs the following tasks:
The UserServerThe UserServer is used for larger worlds that require more than one RoomServer, or when user registration and authentication are required. When user registration and authentication are not required, the UserServer is used in anonymous mode, and can handle large worlds with multiple RoomServers. When user registration and authentication are needed, the UserServer maintains a user database and is configured to provide the necessary user services. The UserServer performs the following tasks:
Possible ConfigurationsPossible WorldServer configurations include the following: Single RoomServerA RoomServer may be utilized in stand-alone mode to provide anonymous multi-user capabilities for a small virtual reality space. Users do not have to register or be authenticated, they only have to present a unique user name (one not currently in use on the RoomServer) at the time they initialize a session. Single RoomServers run in anonymous mode are useful for demos and small worlds but cannot scale to more than 1000-2000 users and cannot keep track of persistent data such as users' permanent names, email addresses, or usage. Note: This configuration hasn't been extensively tested. Multiple RoomServers - single anonymous UserServerMultiple RoomServers may be utilized with a UserServer configured in anonymous mode to provide scalable multi-user capabilities for a virtual reality space. When the UserServer is configured to run in anonymous mode, users only have to present a unique user name (one not currently in use by a client actively logged on to the WorldServer) at the time they initialize a session. The UserServer provides redirection services and message forwarding as necessary between the RoomServers. Note: This configuration hasn't been extensively tested. Multiple RoomServers - single UserServer (Currently used configuration)One or more RoomServers may be utilized with a UserServer configured to provide registration and authentication services. In this case, users must register, and must authenticate each time they log into the system. In addition to providing redirection and command forwarding, the UserServer handles user registration and authentication, and maintains a database of information on users, their usage, and other per user information. The UserServer can also be configured to allow guest logins, where a client is assigned a guest name and has access to a limited area only. Guest logins do not require authentication. Single UserServerA UserServer may be utilized without any RoomServers for the sole purpose of delivering a text message to the client, giving the status of the WorldServer. The UserServer may either be configured to require authentication, or to operate in anonymous mode. The server message file will contain the message to be delivered to all clients that connect, and all redirection will indicate that the desired rooms do not exist, causing the client to terminate the connection after displaying the text message. Note: This configuration hasn't been extensively tested. Redundant WorldServersTwo WorldServers, each with their own UserServer and RoomServers, can be combined to provide redundancy and additional scalability. The UserServers in the two WorldServers share information concerning which users are currently logged in. Clients are configured to try to connect through both UserServers and the first one to respond is used. If one WorldServer fails, clients connected to the failed WorldServer will reconnect to the working WorldServer automatically. Users cannot see or chat with users which are in rooms served by a different WorldServer, but they can whisper to such users and can teleport to the user's room on the other WorldServer. Buddylist notifications also work across WorldServer boundaries. Note: This option is no longer supported. Single WorldServer OperationThe following diagrams and associated narratives describe how clients, a WorldServer, and a database server interact during normal operations. The diagrams show two clients and a WorldServer configuration consisting of two RoomServers and a single UserServer. Although this is not typical, it is sufficient to illustrate the majority of WorldServer operation. What follows should not be used as a basis for understanding the details of the underlying protocols. Several aspects of the communication protocol have been simplified or ignored. For example, positive and negative acknowledgments are not generally presented, and there is no mention of room subscriptions. The aspects not presented were not considered essential to understand overall operation in sufficient detail to administer a WorldServer installation.
If a UserServer is to be used, it is started first. On startup it reads its configuration file. If it is configured to work with a user database, it connects to the database server, using the username and password provided in the UserServer's configuration file, and retrieves all user properties stored in the database. User properties include registration serial numbers, usernames, and passwords. The UserServer is now ready to receive connections from clients. Once the UserServer (if any) is ready, each RoomServer is started. On startup it reads its configuration file and allocates any rooms listed in its configuration file. These rooms are called statically allocated rooms. If it is configured to work with a room database, it connects to the database server, using the username and password provided in the RoomServer's configuration file, and retrieves the room properties for all rooms that it serves. It also adds any rooms that are listed in its configuration file which are not yet in the database. Note that use of statically allocated rooms is deprecated in favor of dynamic assignment and dynamic allocation discussed below. If the RoomServer is configured to work with a UserServer, it connects to the UserServer using the password provided in the RoomServer's configuration file. The RoomServer then identifies itself to the UserServer with a unique name and indicates which rooms it is willing to serve. The UserServer stores this information for quick retrieval when redirecting clients and routing messages. The RoomServer can indicate to the UserServer that it will serve rooms in particular worlds. When one of these rooms is first referenced by the client, the UserServer will dynamically assign them to such a "volunteering" RoomServer. If there are no volunteers, the UserServer will dynamically assign the room (and all future rooms in the same world) to the RoomServer which is currently serving the smallest number of worlds. The room will then be dynamically allocated by the RoomServer and the room properties will be retrieved from the database server. The RoomServer configuration file specifies which worlds the RoomServer volunteers to serve.
If the WorldServer includes a UserServer, clients send the user's name, password, and optionally the number of nearby avatars for which the client would like to receive updates. If the UserServer determines that the username/password combination is authentic, it responds with the number of nearby avatars for which updates will be sent. This will be the same as the number requested by the client, or a default if they client did not specify. The default number of avatars is set in the UserServer configuration file. For guest logins (if permitted), no username or password is sent by the client. Instead, the UserServer assigns a username and sends it and a time limit to the client. The client is responsible for enforcing the time limit by logging the user off after the specified time has elapsed. The time limit, the prefix for assigned guest usernames, and the number of guests allowed are all specified in the UserServer configuration file. If the UserServer is configured to work with a database (i.e. it is not anonymous) and username and password are not authentic, the UserServer requests a registration serial number from the client which queries the user. If the provided registration serial number is valid, the username, and password are added to the user database. If the UserServer is anonymous, any username which is not being used will be considered valid. If the WorldServer does not include a UserServer, the client authenticates directly with the RoomServer. As with an anonymous UserServer configuration, any username which is not being used will be considered valid by the RoomServer.
If the WorldServer includes a UserServer, the initial client request to change rooms is sent to the UserServer. The UserServer determines which RoomServer is serving the requested room and responds by redirecting the client to that RoomServer. The client then connects to the RoomServer using the user's name and password, and requests to change rooms. The RoomServer requests that the UserServer authenticate the username/password combination. If the user is authentic, the UserServer sends the user's properties to the requesting RoomServer. The RoomServer, now knowing that the user is valid, allows the user to change rooms and sends the properties of the new room back to the client.
Once a user is in a room, almost all communication is between RoomServers and clients. Each client communicates with the RoomServer serving the room where the user is currently located. The client informs the RoomServer of any changes in the user's location and any messages the user wishes to send to other users. The RoomServer informs the client of any changes in the location of nearby users and any messages from other users.
If a RoomServer receives a message for a user in a room that it does not serve, it forwards the message to the UserServer. The UserServer determines which RoomServer is currently serving the user and forwards the message to the recipient's RoomServer. The recipient's RoomServer then forwards the message to the recipient's client. This process is used for whispers, broadcasts, and normal chat across room boundaries. The UserServer forwards broadcasts to all RoomServers.
Redirects are the transfer of a user from one RoomServer to another RoomServer. This might happen if the user walks between rooms, or if the user teleports, uses bookmarks or first connects past the UserServer. The UserServer acts as intermediary for all redirect requests, because only the UserServer knows which rooms are being served by which RoomServers. When a RoomServer receives a client request to move to a room served by a different RoomServer, it queries the UserServer to determine which RoomServer serves the requested room and then redirects the client to the new RoomServer. The client then connects to the new RoomServer using the user's name and password, and requests to change rooms. The RoomServer requests that the UserServer authenticate the username/password combination. If the user is authentic, the UserServer sends the user's properties to the requesting RoomServer. The RoomServer, knowing that the user is valid, allows the user to change rooms and sends the properties of the new room back to the client. Because this involves a number of steps, redirects
occasionally cause a short hitch on the client side. A well-designed world
should keep the number of redirects to a minimum, by placing divisions
between RoomServers at natural "choke points" between physical portions of
a virtual space, such as between worlds. The dynamic room assignment
scheme meets this requirement because all rooms within a world are served
by the same RoomServer. (Note that a single RoomServer can serve
multiple worlds.)
When a user wishes to logout, the client informs the current RoomServer, and disconnects. The RoomServer then notifies the UserServer, and the UserServer updates the database with any changes to the user's properties.
If the UserServer or a RoomServer were to fail, any changes
to user or room properties which have not been sent to the database server
would be lost. To minimize the loss, the UserServer and RoomServer
periodically send these changes to the database server. The
frequency of these updates is set in the configuration files. Advanced TopicsClient vs. Server Worlds and ChannelsAs mentioned in the introduction, a WorldServer application allows users to navigate a virtual space consisting of many worlds which, in turn, consists of many rooms. However, the meaning of the terms virtual space, world, and room depends on whether the context is what the user perceives or how the WorldServer works. In this section, we will attempt to better define these terms in each of these contexts. We call the virtual space that the user perceives the user virtual space. It consists of multiple interconnected user worlds. A user world generally corresponds to a ".world" file on the client machine. This file describes the connectivity of user rooms and their visual characteristics (e.g. locations of walls, and textures). From the WorldServer's perspective, what we call the server virtual space consists of multiple channels. Each channel consists of multiple server worlds and handles a separate, disjoint set of users. Each server world consists of a collection of server rooms. Each server room handles most of the message traffic from the users in the containing channel whose avatar is in the corresponding user room. Each RoomServer can serve several server worlds (in the same or different channels) by dynamically allocating server rooms within each server world as needed. One (or two redundant) WorldServer, each with one or more RoomServers, can be used to serve the entire server virtual space. Note that the use of channels means that multiple server worlds can correspond to the same user world and multiple server rooms can correspond to the same user room. By limiting the number of users per channel, it is possible to reduce the load on each RoomServer and better balance the load between RoomServers. The recommended maximum number of users per channel is set in the configuration files. When a user logs in, the WorldServer searches for a channel with less than this maximum population and places the user in that channel. If all channels are full, a new channel is created. Using multiple channels can improves performance in two ways. First, it results in fewer users in each server room. This means that there are less users to search to find the nearest avatars and results in a lighter load. Second, in configurations with a UserServer, load balancing is also improved. With or without channels the UserServer dynamically assigns new server worlds to the RoomServer serving the fewest server worlds. More channels result in more server worlds and finer-grained balancing. Channels are strictly a performance optimization, and as such, they are designed to be mostly invisible to the user. The WorldServer treats messaging between users in server worlds in different channels no differently than messaging between users in different server worlds on the same channel. Whispers, teleports, and buddy list notifications can cross channel and server world boundaries, but normal chat and position updates do not. Note that users can teleport into full channels so the per channel population limit is not strictly enforced. The fact that normal chat and position updates do not cross channels can lead to a perceivable artifact in the users experience. It means that 2 users can be standing next to each other in the user world, and show up on each others buddy lists, but they may not be able to see each other or chat with each other because they are in two different server worlds in two different channels. They can however, whisper to each other and one of them can teleport to the other's location. This will put them in the same server world and they will be able to see and chat with each other. Other than the existence of this artifact, users are completely unaware of the existence of multiple channels. Redundant WorldServer OperationA certain amount of redundancy is available by simply running two WorldServers which are connected to the same (if any) database. Clients can be configured to attempt to connect to two WorldServers at the same time. Each client keeps the first connection which is established and discards the other. This generally results in clients connecting to the closest WorldServer and ensures that if one WorldServer is unavailable, users will still be able to connect. This kind of redundancy is valuable, but it results in separate server virtual spaces which correspond to the same user virtual space. Users connected to one WorldServer cannot see, chat with, whisper to, or teleport to users connected to the other WorldServer. In order for the two WorldServers to serve the same server virtual space, one of the two UserServers must be configured to act as a backup server and the other as a backup client. Whenever the backup client UserServer is running, it periodically attempts to connect to the backup server UserServer until a connection is established. Once a connection is established all other communication is symmetric (i.e. peer-to-peer). Upon establishing the link, each UserServer sends to the other the list of users which are currently logged in. This allows buddy lists to initially include users connected to either WorldServer. As users log in and out of either UserServer, they notify each other so that the buddy lists are always kept up to date. Broadcasts, and whispers are also forwarded to the other UserServer. When a user on one WorldServer attempts to teleport to a server room served by the other WorldServer, the following rather complex communication occurs.
|
Selecting a WorldServer ConfigurationRemember that the server executables have hard-coded maximum user limitations. This makes it fairly important to make accurate estimates of the size of your intended user base. Any WorldServer configuration that uses a UserServer can only use at most two UserServers, so make sure you choose UserServers that have a large enough upper limit to cover expansion. RoomServers also have maximum user limits, but because you can scale up your world by adding multiple RoomServers, the initial user estimate won't be as critical. If the entire WorldServer suite will be running on one single-processor architecture, there is a slight performance degradation in running more than one RoomServer process through the single processor (because of the extra overhead in the form of context switches). However, configuring multiple RoomServers on a single-processor machine offers some minor advantages for long term user tracking, only because each RoomServer generates its own log file. If the WorldServer will be running on multiple single-processor machines, configure one RoomServer per machine. Run the UserServer on a machine of its own, or the most powerful machine if a separate machine is not available. If all machines are identical, start the last RoomServer on the machine running the UserServer. The last RoomServer started will be the last to have server worlds dynamically assigned to it. If the WorldServer will be running on a multi-processor architecture, configure one RoomServer per processor, plus one UserServer. If possible, for systems which use an Oracle database, run the UserServer on the same machine as the Oracle server. This is not a requirement, but it will reduce network bandwidth usage and may reduce database client-server configuration problems. If possible, the UserServer and the Oracle server should be running on the same local network. The potential down side of running on a single machine is the performance of the machine which supports both the UserServer and the database, particularly if the database is also used by other applications. The degree to which this will be a problem depends mainly on the database. The UserServer will probably never be the majority CPU-cycle user on any machine. Our experience is that 1000 users logging in and out randomly on an SparcStation 1000 UserServer only accounted for 3 percent of maximum CPU load. Another possible reason for distributing RoomServers on multiple machines (or even for splitting up a single machine into several RoomServer processes) is that if an individual RoomServer goes down it won't bring down the whole system. When a RoomServer is brought down, the UserServer will not be able to provide redirection for the rooms it was serving. Clients will reconnect to the UserServer and rooms will be dynamically reassigned to the remaining RoomServers. This allows for real-time correction of problems without bringing down your entire world, and if necessary the configuration file of the non-operating RoomServer could be used to start a new process on another machine without any long-term visible difference to the client. The guidelines for determining the optimal number of RoomServers vary between configurations, processors and specific load situations. The hardware requirements give some idea of how many users could be supported with several configurations, but there are several variables that will affect your true user capacity. Some of these variables are intangibles or design considerations for specific worlds: for instance how "chatty" the population of your intended world will prove to be, the percentage of whispered messages vs. standard chat, number of undeliverable messages, etc. Other variables are controllable, for instance the update cycles keyword in the RoomServers' configuration files. The following list of questions will help you identify your needs and determine the optimal RoomServer organization for your WorldServer:
Hardware RequirementsOur tests indicate that between 4.5 and 10 active users can
be supported per MHz of CPU speed. The tests simulated users which
moved constantly and sent a short chat message once every 10
seconds. Servers were configured with the default settings which
result in chat and movement being sent to the 6 nearest avatars once every
second. The hardware we tested and the expected number of users
supported are described in the table below. Note that, due to
testing conditions, some numbers are extrapolated:
Note that this does not take into account HTTP or SMTP server load. Database requirementsIf user authentication is required, an Oracle database server must be running. The actual Oracle database server may be run on the same machine, or on a separate machine. In the case of running Oracle on a separate machine, an Oracle SQL*Plus client needs to be on the same machine as the UserServer process. Oracle's system requirements need to be considered in addition to WorldServer's requirements. If full logging is enabled, allow about 1 GB of disk space on the Oracle machine. Also consider that there are varying levels of logging possible, both by Oracle and by the WorldServers, and some logging options may require automated or real-time maintenance and intervention to keep the log files from getting unwieldy. Bandwidth RequirementsWide area bandwidth per user is approximately 1 Kbps from server to client and approximately 1 Kbps from client to server. This is based on internal testing of a 3 RoomServer configuration serving 3000 simulated users behaving as described above. |
Server (server name)
Users (port)
ClientUpdates (maximum avatars) (update interval microseconds)
UserServer (IP address) (port) (name) (password)
UnicodeNames
World (server world name)
MothFile (file name)
Connections (maximum ordinary connections) (maximum priority connections)
MaxChannelPopulation (maximum users per channel)
Logging
# (comment)
Server (server name)
Users (port) (delay minutes)
RoomServers (port) (password)
UserDatabase (username) (password) (servicename) (update interval minutes)
Guests (maximum guests) (prefix) (duration)
UnicodeNames
MothFile (file name)
Connections (maximum connections) (maximum active users)
MaxChannelPopulation (maximum users per channel before redirection)
BackupServer (port) (password)
BackupClient (host) (port) (password)
ChannelSuffix (suffix)
Logging
Detailed documentation for SQL and SQLPlus can be found on a variety of sources, including Oracle's web site. For quick reference, here are a few of the most common SQL commands:
SELECT: Select clause tells Oracle which column you want.
FROM: From clause tells Oracle the name of the table or tables those columns are in.
WHERE: Where clause tells Oracle what qualifiers you'd like to put on the information it is selecting for you.
ORDER BY: Order by clause tells Oracle what column(s) you want the
returned information sorted by.
The UserServer maintains two databases - the user database (three tables), and the serial number database (one table). Each of these tables is set up to allow for relevant queries at runtime. The key to the user database tables is the username field, but queries can be formed based on other fields as well.
The SerialNumbers table contains a record for every valid serial number. It
is initialized with a set of serial numbers by a WorldServer administrator. A
user will register by providing a valid serial number that matches an unused
table entry. The serial number must be distributed with the client software or
in some other way, because it will be required for all registrations from the
client. The serial number record contains the following information:
serialNumber | VARCHAR2(20)NOTNULL,UNIQUE | The user's serial number |
userName | VARCHAR2(50)NOTNULL | The user name with case intact |
serialStatus | VARCHAR2(50)NOTNULL | One of {SERIAL_FREE, SERIAL_USED} |
The values defined for serialStatus are:
0 = SERIAL_FREE
1 = SERIAL_USED
The SerialNumbers table will be initialized with a set of serial numbers by a WorldServer administrator. The serialStatus column should be initialized to SERIAL_FREE at this time (this will be done for you when you create serial numbers). A user will then register via the client by providing a valid serial number. The UserServer will set serialStatus to SERIAL_USED upon successful user registration with a given serial number, and enter their username in the userName field.
The included program SerialGen can generate a list of serial numbers based on a seed and tagged by prefix. The program runs in the C-shell and produces three output files: an SQL script that you can use to directly modify the SerialNumbers table, a master list as a text table for administration purposes, and a separate text table for use in production of the serial numbers to be given to client end users. See Generating Serial Numbers.
The UserRegistration table contains a record for every registered user. The
record holds the following information:
userNameLower | VARCHAR2(50) NOT NULL | The user name in all lower case. |
userName | VARCHAR2(50) NOT NULL | The user name with case intact. |
serialNumber | VARCHAR2(20) NOT NULL | The user's serial number. |
password | VARCHAR2(20) NOT NULL | The user's password. |
clientVersion | VARCHAR2(20) NOT NULL | The user's client software version. |
accountStatus | NUMBER(1) NOT NULL, | One of {ACCOUNT_ACTIVE, ACCOUNT_INACTIVE}. |
registrationDate | DATE NOT NULL | The date and time the user registered. |
timesOn | NUMBER(10) NOT NULL | The number of times the user has logged on since registration. |
totalMinutes | NUMBER(10) NOT NULL | The number of minutes the user has been logged on since registration. |
userPrivileges | NUMBER(3) NOT NULL | The system privileges assigned to this user. |
AccountStatus allowed values:
The values defined for accountStatus include:
0 = ACCOUNT_INACTIVE 1 = ACCOUNT_ACTIVE
The WorldServer will set accountStatus to ACCOUNT_ACTIVE upon successful user registration. The WorldServer administrator may deactivate an account by setting accountStatus to ACCOUNT_INACTIVE.
userPrivileges allowed values:
The values defined for userPrivileges include:
0 = No privileges 1 = PRIV_BUILD - the user may dynamically register rooms 2 = PRIV_BROADCAST - the user may broadcast text 4 = PRIV_PROPERTY - the user may retrieve and set all properties of any object 3 = PRIV_BUILD and PRIV_BROADCAST 5 = PRIV_BUILD and PRIV_PROPERTY 6 = PRIV_BROADCAST and PRIV_PROPERTY 7 = PRIV_BUILD and PRIV_BROADCAST and PRIV_PROPERTY
The WorldServer will set an authenticated user's privilege level to the value given in this field when the user logs on. This privilege level is communicated to all RoomServers the user connects to. To take effect, the value must be changed while the user is logged off, otherwise it will only become effective at the next login.
The UserProperties table is used to store persistent user properties. These
are accessed every time a user logs in, and they may also be used to form the
reply for a "finger" operation. The UserProperties table contains the following
columns:
userName | VARCHAR2(50) NOT NULL | The user name with case intact |
propertyId | NUMBER(3) NOT NULL | The property identifier. |
propertyFlags | NUMBER(3) NOT NULL | Each property has a PropertyFlags field that defines certain aspects of the property. |
propertyAccess | NUMBER(3) NOT NULL | Defines access restrictions on the property. |
propertyStringValue | VARCHAR2(255) NOT NULL | The value of the property when it is a string. |
propertyBinaryValue | RAW(255) | The value of the property when it is binary data. |
The setting of the PropertyFlag determines which column the value of the property is stored in. When the value of the property is a string and is stored in propertyStringValue, the propertyBinaryValue will be NULL. When the value of the property is binary data and is stored in propertyBinaryValue, the propertyStringValue will be NULL. Properties stored in propertyStringValue will be readable using the Select command in SQLplus. Properties stored in propertyBinaryValue will appear encoded in hexadecimal when selected using SQLplus.
The values in the propertyFlags and propertyAccess as seen when doing a select on these columns are as follows:
propertyFlags
128 = Store in DB, no auto-update, not a finger property, stored in propertyStringValue. 144 = Store in DB, no auto-update, not a finger property, stored in propertyBinaryValue. 160 = Store in DB, no auto-update, finger property, stored in propertyStringValue. 176 = Store in DB, no auto-update, finger property, stored in propertyBinaryValue. 192 = Store in DB, auto-update, not a finger property, stored in propertyStringValue. 208 = Store in DB, auto-update, not a finger property, stored in propertyBinaryValue. 224 = Store in DB, auto-update, finger property, stored in propertyStringValue. 240 = Store in DB, auto-update, finger property, stored in propertyBinaryValue.
propertyAccess
0 = Public write, public read. 1 = Possessor write, public read. 2 = Public write, owner read. 3 = Possessor write, owner read.
UserProperties can be used to store persistent user data from session to session, including potentially shared-state properties related to users. Properties and their Id's need to be coordinated between the server and the client (or the client's underlying language). Properties are generally meaningless to the server, except for the reserved properties for session tracking etc. It is up to the client to interpret the property values correctly.
In Gamma, properties are exchanged with the client by using attributes. A full discussion of properties, attributes and using shared objects in Gamma will be included in a future document.
PropertyId:
Each property has a PropertyId field that is one byte. Up to 255 PropertyIds may be defined. Zero is reserved and is not a valid PropertyId. Some of these properties are shared between objects (especially those relating to session control), but others may be defined on a per-object basis. Currently defined propertyIds include:
Session Properties:
1 = Application Name (string)
2 = User Name (string)
3 = Protocol Number (string)
4 = Error Number (string)
6 = Password (string)
8 = Update Interval (string)
9 = Client Version (string)
10 = Serial Number (string)
12 = Logon/Logoff Flag (string)
13 = Permitted Session Duration (string)
14 = Guest User Flag (string)
15 = Server Type (string)
User Properties:
5 = Avatar Bitmap (string)
7 = Avatar Updates Requested (string)
9 = Client Version (string)
11 = Email Address (string)
The client software can require users to register with their email addresses as an optional field. For example, in Worlds Chat, the client sends the email address as VAR_EMAIL. VAR_EMAIL in turn is understood by the server to be a property that is stored in the Properties database, as Property ID 11, with the email itself stored in PropertyStringValue as a string. Since this table is also keyed to the username, you can correlate tables of email addresses as needed using SQL.
To extract an email address for a given user:
select propertyStringValue from UserProperties where userName='John Doe' and propertyId=11;
To extract a list of all recorded email addresses:
select userName, propertyStringValue from UserProperties
where propertyId=11
order by userName;
You should note however that many database table queries, particularly in the RoomProperties table, might give you data that is stored mostly in binary form, and that cannot be properly interpreted by standard SQL. What you'll get in this case is often meaningless hexadecimal, which could be unicode text or just raw data.