This topic contains the following sections.

DL_Annotation
DL_AnnotationPropertyDescriptions
DL_Announcement
DL_ChatRoom
DL_ChatRoomInvitation
DL_ChatRoomMessage
DL_ChatRoomUser
DL_CollaborationObj
DL_Collection
DL_Contact
DL_DiscussionPost
DL_DiscussionThread
DL_Document
DL_DownloadCount
DL_Email
DL_Event
DL_History
DL_Layer
DL_LibraryObj
DL_Link
DL_Map
DL_MapLayers
DL_News
DL_ObjectCollections
DL_ObjectRelationship
DL_Poll
DL_PollChoice
DL_PollResult
DL_Question
DL_RelationshipType
DL_RemoteObject
DL_Rss
DL_RssContent
DL_SavedSearch
DL_Scenario
DL_SearchLocalResult
DL_SearchRemoteResult
DL_Style
DL_StyleSheet
DL_Survey
DL_SurveyNumericResult
DL_SurveyOptionResult
DL_SurveyOptionType
DL_SurveyQuestion
DL_SurveyTextResult
DL_Task
DL_TaskUser
DL_Thumbnail
DL_UserCatalog
DL_UserCollectionSubscription
DL_Weblog
DL_WeblogEntry
MD_Metadata
MD_RemoteMetadata
RS_OGCLayer
RS_OGCService
RS_PhoenixCategory
RS_PhoenixNode
SAGA_User
WS_Alert
WS_GroupPermissions
WS_Membership
WS_Person
WS_Preferences
WS_RoleGroups
WS_Roles
WS_User
WS_Workspace
WS_WorkspaceRoles

DL_Annotation

This table is reserved for future use.

DL_AnnotationPropertyDescriptions

This table is reserved for future use.

DL_Announcement

Stores the information needed to persist an announcement.

This table is directly associated with the DL_CollaborationObj table.

Figure:An announcement

DL_ChatRoom

Stores the information needed to persist a chat room.

This table is directly associated with the DL_CollaborationObj table.

Figure:Chat room tables

DL_ChatRoomInvitation

A chat room invitation functions as a message sent from one user (in a chat room) to another user (not in the chat room).

See DL_Chatroom.

DL_ChatRoomMessage

Stores the information needed to represent a message (some text a user entered) in the chat room.

See DL_Chatroom.

DL_ChatRoomUser

Associates a user with a chat room and any options he has chosen to use within the chat room.

See DL_Chatroom.

DL_CollaborationObj

The DL_CollaborationObj table is used to describe collaboration objects. It encapsulates all properties common to collaboration objects such as who created it and when, and whether it is shared or deleted.

The tableName column is used to identify what type of collaboration object it is, while the objectID column points to the primary key of the table itself. Since this objectID can point to many different tables, this relationship is not strictly enforced and we cannot use cascaded DELETEs or UPDATES.

The illustration below shows the tables that are associated with the DL_CollaborationObj table.

Figure:DL_CollaborationObj relationships

DL_Collection

Stores the information needed to persist a collection (folder).

Workspaces are comprised of collections, which in turn are comprised of assets (either a library object or a collaboration object) and optionally any nested collections. The DL_Collection table along with the DL_ObjectCollections table maintains the relationship between the asset and what workspace the asset is in.

Currently, collections can only contain one type of object. The tableName column denotes the table that indicates the type of content associated with a collection.

Note:
It wouldn't be a large task to modify the database to support multiple content types. A first step would be to remove the tableName column. Then the various collection-related stored procedures would need to be reviewed. This is left as an exercise for the brave reader.

Figure:Tables associated with DL_Collection

DL_Contact

This table uses the WS_Person table to store information about a person. The association with the DL_CollaborationObj table allows contacts to function just as assets within a workspace.

Important Note:
Do not confuse this table with the WS_User or WS_Person tables.

Figure:Contacts

DL_DiscussionPost

Stores the information needed to represent one message a user has posted to a discussion thread.

See DL_DiscussionThread.

DL_DiscussionThread

Stores the information needed to persist a discussion thread.

This table is directly associated with the DL_CollaborationObj table.

A discussion thread is comprised of 0 or more rows in the DL_DiscussionPost table.

Figure:Discussion tables

DL_Document

Stores the information needed to persist a document in the system.

Figure:Documents

DL_DownloadCount

Associates a counter with a phoenix object, used to keep track of how many times the object was downloaded.

Figure:Download counter

DL_Email

Stores the information needed to persist an email in the system.

This table is directly associated with the DL_CollaborationObj table.

Figure:An email

DL_Event

Stores the information needed to persist an event.

This table is directly associated with the DL_CollaborationObj table.

Figure:An event

DL_History

DL_History is used to keep a record of important events that have occurred in the system.

The action column indicates the type of action that has occurred. Since this is a plain text field, new actions can be added as neccessary. The most used actions include:

create - the indicated object was created
update - the indicated object was updated
delete - the indicated object was deleted
purge - the indicated object was purged
ownerchg - the owner of the indicated object was changed
download - the indicated object was downloaded. If the user is registered, the username will be stored in message.

The message column is optional and provides additional information regarding the event.

Figure:History table and relationships

DL_Layer

Stores the information needed to persist a layer.

This table is directly associated with the DL_LibraryObj table.

Figure:Layers

DL_LibraryObj

The DL_LibraryObj table is used to describe library objects. It encapsulates all properties common to library objects such as who created it and when, it's published status and numerous flags indicating whether it is deleted, downloadable or a shortcut.

The tableName column is used to identify what type of library object it is, while the objectID column points to the primary key of the table itself. Since this objectID can point to many different tables, this relationship is not strictly enforced and we cannot use cascaded DELETEs or UPDATES.

The illustration below shows the tables that are associated with the DL_LibraryObj table.

Figure:Library objects

DL_Link

Stores the information needed to persist a hyperlink.

This table is directly associated with the DL_CollaborationObj table.

Figure:A link

DL_Map

Stores the information needed to persist a map.

This table is directly associated with the DL_LibraryObj table.

Figure:Maps

DL_MapLayers

Associates a map with its composite layers.

See DL_Map

DL_News

Stores the information needed to persist a news feed.

This table is directly associated with the DL_CollaborationObj table.

Important Note:
Do not confuse this table with the DL_Rss table which is used to generate RSS content to serve out to consumers.

Figure:A news feed

DL_ObjectCollections

See DL_Collections

DL_ObjectRelationship

This table is used to make associations between two different library objects or two different collaboration objects. The type column on the DL_RelationshipType" table indicates the kind of relationship is being described.

This functionality was removed in an earlier of Phoenix, however the scenarios module still uses these tables to persist the components that make up a scenario, using a "hasa" relationship type.

Figure:Relationship tables

DL_Poll

Stores the information needed to persist a poll.

This table is directly associated with the DL_CollaborationObj table.

A poll is made up of 1 or more poll choices located in the DL_PollChoice table. When a user casts a vote, a row is added to DL_PollResult which links the user to the choice he selected. In the case of anonymous users (user's with no entry in the DL_User table), no association is made, but the votes counter is increased.

Figure:Polls

DL_PollChoice

Stores each choice associated with a poll. See DL_Poll.

DL_PollResult

Stores the votes that have been cast for each choice in a poll. See DL_Poll.

DL_Question

Stores each question that is part of a survey. See DL_Survey.

DL_RelationshipType

A static table that defines several different types of relationships. See DL_ObjectRelationship.

DL_RemoteObject

Describes an object that exists on a remote node. See DL_SavedSearch.

Figure:Remote objects

DL_Rss

Stores the information needed to define a RSS feed in the system. RSS feeds are associated with both a workspace (the workspaceID column) and an object type (the tableName column). RSS feeds can also be associated with weblogs and in this case, the objectID refers to the entry in the DL_Weblog table.

Generating the text for the RSS feed can be fairly CPU intensive, and in order to reduce stress on the server, the resulting content is cached in the DL_RssContent table. This table also keeps track of how many times the feed was requested and how many times the content was generated.

Important Note:
Do not confuse this table with the DL_News table which is used to bring external RSS feeds into the system.

Figure:RSS Syndication

DL_RssContent

Stores the actual content that is part of the Rss feed. See DL_Rss.

DL_SavedSearch

This table contains the information needed to persist search parameters in the system.

A row in the DL_SavedSearch table is created for every search that is initiated. It contains such information as the time it was initiated (initiated), the search keywords (keyword1 to keyword5), spatial information (westCoord, southCoord, eastCoord, northCoord) and temporal information (lowerDate, upperDate).

The columns are described below.

Column name	Description
userID	The ID of the user that conducted this search. Can be null in the case of anonymous users.
userKey	A unique key that identifies the user that conducted this search. Cannot be null.
createdDate	The time the search was created.
initiated	The time the search was initiated. This time may be different from createdDate due to lag.
lowerDate	The lower bounds of the date range to search within.
upperDate	The upper bounds of the date range to search within.
keyword1 to keyword5	The keywords to search for within the metadata information.
westCoord	The western-most extent of the bounding box to search within.
southCoord	The southern-most extent of the bounding box to search within.
eastCoord	The eastern-most extent of the bounding box to search within.
northCoord	The northern-most extent of the bounding box to search within.
nodeToPoll	The number of remote nodes that a search request will be sent to.
nodesReporting	The number of remote nodes that have responded to a search request.
lastResult	The time that the last remote node responded to a search request. Useful for checking for timeouts.
acknowledged	The time that the search user acknowledged the results. Useful for checking if any results are new or not.

The following diagram shows how the search and search result tables are related.

Figure:An active search with search results

Caution:
The number of rows in DL_SavedSearch will continue to escalate as searches are conducted. It is your responsibility to implement some mechanism to remove old searches and their associated search results.

DL_Scenario

Stores the information needed to persist a scenario. A scenario is simply a collection of other objects in the system that have been associated using a hasa relationship. See DL_ObjectRelationship.

This table is directly associated with the DL_LibraryObj table.

Figure:Scenarios

DL_SearchLocalResult

The results of every search are cached and this table stores snapshots of the local items that matched the associated search (DL_SavedSearch). The main benefit of this approach is that a user can navigate away from the Search page, and the results will still be available for the duration of the user's session.

See DL_SavedSearch.

DL_SearchRemoteResult

The results of every search are cached and this table stores snapshots of the remote items that matched the associated search (DL_SavedSearch). The main benefit of this approach is that a user can navigate away from the Search page, and the results will still be available for the duration of the user's session.

See DL_SavedSearch.

DL_Style

Stores a layer's style information.

See DL_Layer.

DL_StyleSheet

Stores references to all known style sheets.

Figure:Style sheets

DL_Survey

Stores the information needed to persist a survey.

This table is directly associated with the DL_CollaborationObj table.

Figure:Surveys

DL_SurveyNumericResult

Stores user responses to survey questions that require a numeric answer. See DL_Survey.

DL_SurveyOptionResult

Stores user responses to survey questions that require a multiple-choice answer. See DL_Survey.

DL_SurveyOptionType

Stores each choice that makes up a multiple-choice type question in a survey. See DL_Survey.

DL_SurveyQuestion

Stores the information required to form a question that is part of a survey. See DL_Survey.

DL_SurveyTextResult

Stores user responses to survey questions that require a plain text answer. See DL_Survey.

DL_Task

Stores the information needed to persist a task.

This table is directly associated with the DL_CollaborationObj table.

Figure:Tasks

DL_TaskUser

Associates a user with a task to indicate user assignment. See DL_Task.

DL_Thumbnail

Stores the filename of the thumbnail that is associated with a phoenix object.

Figure:Thumbnails

DL_UserCatalog

Maintains a list of referenced objects that are in a user's shopping cart.

Figure:The shopping cart

DL_UserCollectionSubscription

Maintains references between a user and the folders he is subscribed to.

Figure:Subscriptions

DL_Weblog

This table is directly associated with the DL_LibraryObj table and contains no additional properties of its own. A weblog as implemented here, is simply a container that allows associations with rows from DL_WeblogEntry.

Figure:Weblogs

DL_WeblogEntry

Stores a single entry within a weblog. See DL_Weblog.

MD_Metadata

A flat table that stores metadata information for all library objects in the system.

Caution:
The objectID column is not associated with the DL_LibraryObj table but rather, the object table itself. This makes obtaining the library object, if you only have the metadata ID, a little awkward. The following code shows how to include the metadata information when selecting a document. CopySQL SELECT lib.createdDate, ,lib.createdBy ,lib.publishedStatus ,lib.promote ,lib.isDownloadable ,lib.instance ,lib.libraryObjID ,doc.filename ,doc.filetype ,doc.filesize ,md.abstract ,md.title ,md.west_bounding_coordinate ,md.east_bounding_coordinate ,md.north_bounding_coordinate ,md.south_bounding_coordinate FROM dbo.DL_document doc INNER JOIN dbo.DL_LibraryObj lib ON doc.documentID = lib.objectID INNER JOIN dbo.MD_Metadata md ON doc.documentID = md.objectID WHERE (lib.libraryObjID = @libraryObjID)

Caution:

The objectID column is not associated with the DL_LibraryObj table but rather, the object table itself. This makes obtaining the library object, if you only have the metadata ID, a little awkward.

The following code shows how to include the metadata information when selecting a document.

CopySQL

SELECT      lib.createdDate, 
           ,lib.createdBy
           ,lib.publishedStatus
           ,lib.promote
           ,lib.isDownloadable
           ,lib.instance
           ,lib.libraryObjID
           ,doc.filename
           ,doc.filetype
           ,doc.filesize
           ,md.abstract
           ,md.title
           ,md.west_bounding_coordinate
           ,md.east_bounding_coordinate
           ,md.north_bounding_coordinate
           ,md.south_bounding_coordinate

FROM       dbo.DL_document doc
INNER JOIN dbo.DL_LibraryObj lib ON doc.documentID = lib.objectID 
INNER JOIN dbo.MD_Metadata md ON doc.documentID = md.objectID 
WHERE     (lib.libraryObjID = @libraryObjID)

Here are the columns and data types of MD_Metadata.

Figure:Metadata

MD_RemoteMetadata

When associated with a MD_Metadata, indicates that the metadata came from a remote location.

Figure:Metadata from a remote source

RS_OGCLayer

Identifies a layer that has been published on an OGC Web service.

See RS_OGCService

RS_OGCService

Defines an OGC Web service.

Figure:The OGC Services

RS_PhoenixCategory

Provides codes to categorize the purpose of a remote Phoenix node. During node registration, the user can select a category that best indicates the purpose of the node.

categoryID	categoryName
1	RS_Science
2	RS_Government
3	RS_Education
4	RS_Commercial
5	RS_Personal
6	RS_Other

See RS_PhoenixNode.

RS_PhoenixNode

Stores information about other known Phoenix nodes. Normally this table is populated by retreiving a copy of all Phoenix nodes from a centralized location. We call this the Phoenix Node Registry. Nodes in this table can be chosen to be included in remote searches.

Figure:The Phoenix Node Registry

SAGA_User

We have implemented the Saga system by minimizing the impact on other tables. The SAGA_User table simply associates a WS_Person as a Saga member.

Figure:Saga Members

WS_Alert

Stores information needed to persist an alert.

The alertType column indicates the type of alert. This value currently has no meaning at the database level. However, client code can use this value to provide additional information when presenting the alert to the end-user. Below is a table representing the values that the Phoenix class library has documented.

alertType	Description
0	Information
1	Task assignment
2	User joins workspace
3	Discussion needs review
4	News feed removed
5	Informs the admins of a workspace that a member has left the workspace.
6	Informs the admins of the global workspace that a new workspace was created.
7	Informs the admins of the global workspace that a workspace was deleted.
8	Informs a user that a workspace was exported.
9	Informs a workspace admin that an item is to be promoted.
10	Informs a user that an item was created in a folder he/she is subscribed to.
11	Informs a user that someone replied to his discussion post.
12	Informs the admins of the global workspace that a new user account was created.
13	A user has indicated that she wants her account removed.
128	The batch emailer reported an error.

The actionTaken column indicates if the user has taken any action as a result of this alert. Mostly for future expansion, currently there is only 1 action a user can take.

actionTaken	Description
NULL	No action has been taken on this alert.
1	Message is marked as read.

Figure:Alerts

WS_GroupPermissions

Associates a role in WS_Roles with a role group in WS_RoleGroups.

See WS_Membership.

WS_Membership

The WS_Membership table has two purposes:

to identify if a user is a member of a particular workspace.
to indicate what role a user has been assigned in a particular workspace.

The membership system is based on the fact that each workspace can have any number of role groups and each role group is comprised of roles. Each user that is a member of a workspace is assigned to a role group based on the roleGroupID.

The diagram below illustrates how the membership tables relate to each other.

Figure:Workspace membership

WS_Person

Stores information about a person. A person can be either a DL_Contact, a WS_User, or a SAGA_User.

Figure:The Person table

WS_Preferences

Stores various preferences for every user.

Note:
This table will likely be obsolete in the next major release of Phoenix. User options are now mainly stored as an Xml file in the user's content folder.

Figure:User preferences

WS_RoleGroups

Associates permissions in WS_GroupPermissions with a role in WS_WorkspaceRoles.

See WS_Membership for more information.

WS_Roles

This table stores the default roles and is populated at install-time. The default roles are as follows:

Rank	Role name
1	read
2	write
3	edit
4	delete
5	folders
6	publish
7	create
8	admin

See WS_Membership.

WS_User

Stores the information needed to persist a user account.

You could say the WS_User table is the heart of the Phoenix database. Almost every object that is created in the system is associated with a user in some way. This makes it especially difficult to permanently remove a user account as the diagram below shows.

Figure: User table relationships. This diagram illustrates the pervasiveness of the WS_User table.

WS_Workspace

Stores the information required to persist a workspace.

The WS_Workspace table is one of the most important tables in the database.

Figure:Workspaces

WS_WorkspaceRoles

Associates WS_RoleGroups with a WS_Workspace.

See WS_Membership for more information.