Sloodle 0.3 Database Schema
From SLIS Second Life Wiki
Sloodle Versions: 0.3 (?)
This page is for SLOODLE 0.3 only.
The schema is divided into two parts: the internal schema (the database tables which SLOODLE itself creates), and the external schema (the way SLOODLE uses existing Moodle tables). Note, however, that index information is generally not specified here; in-depth information can be found in the "sloodle/db/install.xml" XMLDB file in the SLOODLE module download.
Internal Schema
SLOODLE directly creates and uses several tables, as follows:
sloodle
Each record defines an instance of a SLOODLE module (such as a Controller or a Distributor module).
| Column Name | Type | Description |
|---|---|---|
| id | integer | Primary table ID |
| course | integer | Identifies the ID of the course this instance belongs to |
| type | string | Gives the short name of this module type, such as "controller" or "distributor" |
| name | string | Name of this module instance |
| intro | string | Description of this module instance |
| timecreated | integer | Timestamp when this module was created |
| timemodified | integer | Timestamp when this module was last modified |
sloodle_controller
Each record represents a Sloodle Controller Module, which is an instance of a SLOODLE module (so each record provides more information for a related record in the sloodle table).
| Column Name | Type | Description |
|---|---|---|
| id | integer | Primary table ID |
| sloodleid | integer | The ID of a record in the sloodle table |
| enabled | integer | Indicates if this Controller is enabled or disabled (zero = disabled, non-zero = enabled) |
| password | string | The Prim Password for this Controller (will be empty if prim passwords are disabled for this Controller) |
sloodle_distributor
Each record represents a Sloodle Distributor Module (used with the Vending Machine object), which is an |instance of a SLOODLE module (so each record provides more information for a related record in the sloodle table).
| Column Name | Type | Description |
|---|---|---|
| id | integer | Primary table ID |
| sloodleid | integer | The ID of a record in the "sloodle" table |
| channel | string | The UUID of the XMLRPC channel opened by the related Vending Machine |
| timeupdated | integer | Timestamp when the Vending Machine last updated its connection to this Distributor module |
sloodle_distributor_entry
Each record represents an item in a Sloodle Distributor Module, which can be distributed by an associated Vending Machine in-world (so each record is linked to a record in the sloodle_distributor table).
| Column Name | Type | Description |
|---|---|---|
| id | integer | Primary table ID |
| distributorid | integer | The ID of a record in the sloodle_distributor table |
| name | string | The name of the item which can be distributed |
sloodle_users
Each record represents an avatar which is registered to a Moodle user (so each record is linked to the Moodle "user" table).
| Column Name | Type | Description |
|---|---|---|
| id | integer | Primary table ID |
| userid | integer | ID of a record in Moodle's "user" table |
| uuid | string | UUID of the avatar |
| avname | string | Full name of the avatar (e.g. "Pedro McMillan") |
| lastactive | integer | Timestamp of the last known SLOODLE activity by this avatar |
sloodle_pending_avatars
Each record represents an avatar which is known by the system, but which is not yet registered to a Moodle user. This is used by systems such as the Registration Booth, where the user has clicked on the panel in the booth, but has not yet logged-in to Moodle to confirm the registration.
| Column Name | Type | Description |
|---|---|---|
| id | integer | Primary table ID |
| uuid | string | UUID of the avatar |
| avname | string | Full name of the avatar (e.g. "Pedro McMillan") |
| lst | string | The "login security token" to authenticate this avatar -- this string is passed in with the URL when the user logs-in to Moodle to confirm the registration. This ensures nobody can 'spoof' an avatar registration (unless they have a ludicrously lucky guess!) |
| timeupdated | integer | Timestamp of the time this avatar's details were last updated. |
sloodle_course
Each record provides additional SLOODLE-related data about a given Moodle course (so they related to the Moodle "course" table). SLOODLE will not automatically create a record here whenever a Moodle course is created -- rather, it creates it the first time it is needed.
| Column Name | Type | Description |
|---|---|---|
| id | integer | Primary table ID |
| course | integer | ID of a course in Moodle's "course" table |
| autoreg | integer | Indicates whether or not auto-registration is enabled for this course (zero = disabled, non-zero = enabled) |
| autoenrol | integer | Indicates whether or not auto-enrolment is enabled for this course (zero = disabled, non-zero = enabled) |
| loginzonepos | string | The position of this course's LoginZone (described as "<x,y,z>") |
| loginzonesize | string | The size of this course's LoginZone (described as "<x,y,z>") |
| loginzoneregion | string | The name of the region in which this LoginZone has been rezzed |
| loginzoneupdated | integer | The timestamp of the last time the LoginZone 'pinged' Moodle (after a period of time with no 'pings', Moodle will warn the user that the LoginZone may not be available, but will continue to allow teleports to it) |
sloodle_active_object
Each record represents an in-world object which has been authorised and configured (using the web configuration system which was new in SLOODLE 0.3) to 'talk to' a particular Controller.
| Column Name | Type | Description |
|---|---|---|
| id | integer | Primary table ID |
| controllerid | integer | ID of an entry in Moodle's "course_modules" table (aka a course module instance ID). For a SLOODLE module, the "instance" field of a course_modules record relates to the ID of a record in the sloodle table. |
| userid | integer | ID of an entry in Moodle's "user" table, identifying the user who authorised this object, if known. (This data is not really used by the system yet, but ideally should determine the object's level of access to courses etc.) |
| uuid | string | The UUID of the object which has been authorised. |
| password | string | A random internal password assigned uniquely to this object (should be a 9 digit number, which allows it to be transferred through an SL object's starting parameter). |
| name | string | Name of the object which has been authorised (this information is arbitrary -- exists solely to help the user identify objects in a list) |
| type | string | The type identifier name for this particular object, such as "chat-1.0" or "glossary-1.0". This relates directly to the names of folders in the SLOODLE modules "mod" folder, e.g. "mod/sloodle/mod/chat-1.0/linker.php". |
| timeupdated | integer | Last time this object was known to exist. Active objects should 'ping' Moodle on a regular basis to update this value. If no 'ping' is received for a period of time (determined in days by the SLOODLE module configuration), then its entry is deleted, so it needs to be re-configured. This prevents the database getting overloaded with redundant objects, and helps security by minimizing redundant passwords. |
sloodle_object_config
Each record stores a single configuration value for a specific object in the sloodle_active_object table. Currently, only active objects can have configurations stored, but in the future, we hope to be able to store 'blueprint' configurations, so that new objects can be configured automatically with default settings (e.g. automatic classroom rezzers).
| Column Name | Type | Description |
|---|---|---|
| id | integer | Primary table ID |
| object | integer | ID of an object in the sloodle_active_object table. |
| name | string | Name of this configuration setting (such as "sloodlecontrollerid"). The names of these settings should conform to variable names in LSL scripts, and (where appropriate) to the names of HTTP parameters passed-back to Moodle by the in-world objects. |
| value | string | Value of this configuration settings, stored as a string. |
sloodle_login_notifications
Each record stores the username and password for an auto-registered user, along with the UUID of the object which auto-registered them. When the Moodle cron job executes, SLOODLE goes through this list, and emails the details to the object in-world, which then IM's them to the avatar. The details are then deleted from this table for security.
| Column Name | Type | Description |
|---|---|---|
| id | integer | Primary table ID |
| destination | string | UUID of the object which auto-registered the avatar, and to which the notification email should therefore be sent. |
| avatar | string | UUID of the avatar who had a Moodle account auto-registered for them. |
| username | string | The auto-registered username. This should typically be the avatar name, lower-case, without any spaces. However, in the event of a conflict with an existing username, a number will be appended. |
| password | string | The auto-registered password. This is a random selection of letters and numbers. |
sloodle_layout
Each record represents a single layout which has been stored using a SLOODLE Set. Layouts exist on a per-course basis.
| Column Name | Type | Description |
|---|---|---|
| id | integer | Primary table ID |
| course | integer | ID of an entry in Moodle's "course" list, identifying the course to which this layout belongs. |
| name | string | The name of this layout. (Layout names must be unique within a course). |
| timeupdated | integer | Timestamp of when this layout was last saved. |
sloodle_layout_entry
Each record is a single object from a layout identified in the sloodle_layout table.
| Column Name | Type | Description |
|---|---|---|
| id | integer | Primary table ID |
| layout | integer | ID of a record in the sloodle_layout table. |
| name | string | Name of the object in the layout. |
| position | string | Position of the object, relative to the SLOODLE Set (stored as "<x,y,z>") |
| rotation | string | Rotation of the object, relative to the SLOODLE Set (stored as a quaternion, "<w,i,j,k>"). |
sloodle_loginzone_allocation
Each record identifies an allocated position within a LoginZone -- whenever a Moodle user without a registered avatar visits a LoginZone page, they are allocated a new position. It exists for up to 15 minutes, during which time they can teleport to that position using a SLurl which the system provides them. Note that LoginZones are one-per-course, so each allocation is on a per-course basis -- a single user cannot have multiple allocations in a single course.
| Column Name | Type | Description |
|---|---|---|
| id | integer | Primary table ID |
| course | integer | ID of an entry in Moodle's "course" table, identifying the course to which this allocation belongs. (Note, this also relates to the "course" field of the sloodle_course table, where additional LoginZone data is held). |
| userid | integer | ID of an entry in Moodle's "user" table, identying the user who requested a LoginZone allocation. |
| position | string | Position of this allocation, relative to the center of the LoginZone (so the LoginZone can be moved without disrupting existing LoginZone allocations). Stored as a vector "<x,y,z>". |
| timecreated | integer | Timestamp when this allocation was created. It will be considered "expired" after 15 minutes, at which point it will be deleted. |
sloodle_user_object
Each record represents an object which has been authorised (or is in the process of being authorised) as a "user object". This form of authorisation was introduced in SLOODLE 0.3 to let students authorise their own SLOODLE Toolbar (this makes it a little quicker for teachers to setup, but more error prone too!). Note that this is purely an avatar-based authorisation -- therefore if a user changes the avatar associated with their Moodle account, then they will need to re-authorise such objects to work with their new avatar. (You can view a list of authorised user objects by visiting a user's SLOODLE Profile, accessible through the SLOODLE Menu Block).
| Column Name | Type | Description |
|---|---|---|
| id | integer | Primary table ID |
| avuuid | string | UUID of the avatar to whom this object is authorised. |
| objuuid | string | UUID of the object being authorised. |
| objname | string | Name of the object being authorised (this information is arbitrary, and exists only to help users identify objects in a list). |
| password | string | Internal password uniquely generated for this object. (For sake of consistency with "active objects", this is a 9-digit number). |
| authorised | integer | Indicates whether or not this object has been authorised yet (the user must login to Moodle in order to authorise the object). |
| timeupdated | integer | Timestamp of when this object last 'pinged' Moodle (if the object is rezzed or attach to an avatar in-world, then it will 'ping' Moodle a few times a day. Several weeks with no pings will cause SLOODLE to delete the entry and disable the password of the object). |
External Schema
SLOODLE uses the following tables in the standard Moodle database:
config
As per the Moodle convention, core module settings are stored in this table:
- sloodle_active_object_lifetime = life time of active objects (in days)
- sloodle_user_object_lifetime = life time of user objects (in days)
- sloodle_allow_autoenrol = indicates if auto-enrolment is available for this site (0 or 1)
- sloodle_allow_autoreg = indicates if auto-registration is available for this site (0 or 1)
Other Moodle Tables
SLOODLE also adds information to the following, but only according to normal Moodle conventions:
- post = adds blog entries posted using the Sloodle Toolbar
- chat_message = adds chat messages logged by the Sloodle WebIntercom
- quiz_attempts = adds attempts made at quiz questions using the Sloodle Quiz Chair
- choice_answers = adds Choice selections made by avatars using the Sloodle Choice
- assignment_submission = adds submission data for objects submitted to the Sloodle PrimDrop
| This page is part of the Sloodle documentation | |||
|---|---|---|---|
| SloodleDocs Home | User Documentation | Administrator Documentation | Developer Documentation | Sloodle Wiki Home | |||
