The Email Sample Application (OLAP)

[Table of Contents](https://github.com/dell-oss/Doradus/wiki/OLAP Databases: Table-of-Contents) | Previous | Next
OLAP Database Overview: The Email Sample Application

---

To illustrate features, a sample OLAP application called `Email` is used. The `Email` application schema is depicted below:

The tables in the Email application are used as follows:

• Message: Holds one object per sent email. Each object stores values such as Size and SendDate timestamp and links to Participant objects in three different roles: sender, internal recipient, and external recipient.

• Participant: Represents a sender or receiver of the linked Message. Holds the ReceiptDate timestamp for that participant and links to identifying Person and Address objects.

• Address: Stores each participant’s email address, a redundant link to Person, and a link to a Domain object.

• Person: Stores Directory Server properties such as a Name, Department, and Office.

• Domain: Stores unique domain names such as "yahoo.com".

In the diagram, relationships are represented by their link name pairs with arrows pointing to each link’s extent. For example, ↓Sender is a link owned by Message, pointing to Participant, and MessageAsSender↑ is the inverse link of the same relationship. The Manager and DirectReports links form a reflexive relationship within Person, representing an org chart.

Message objects are stored in a shard named YYYY-MM-DD based on their SendDate. The related Participant, Address, Person, and Domain objects are stored in the same shard. This means, for example, that Person and Domain objects are replicated to every shard in which they are referenced.

The schema for the Email application is shown below in XML:

<application name="Email">
	<key>EmailKey</key>
	<options>
		<option name="StorageService">OLAPService</option>
	</options>
	<tables>
		<table name="Message">
			<fields>
				<field name="InReplyTo" type="XLINK" table="Message" inverse="Responses" junction="ThreadID"/>
				<field name="Participants">
					<fields>
						<field name="Sender" type="LINK" table="Participant" inverse="MessageAsSender"/>
						<field name="Recipients">
							<fields>
								<field name="ExternalRecipients" type="LINK" table="Participant" inverse="MessageAsExternalRecipient"/>
								<field name="InternalRecipients" type="LINK" table="Participant" inverse="MessageAsInternalRecipient"/>
							</fields>
						</field>
					</fields>
				</field>
				<field name="Responses" type="XLINK" table="Message" inverse="InReplyTo" junction="_ID"/>
				<field name="SendDate" type="TIMESTAMP"/>
				<field name="Size" type="INTEGER"/>
				<field name="Subject" type="TEXT"/>
				<field name="Tags" collection="true" type="TEXT"/>
				<field name="ThreadID" type="TEXT"/>
			</fields>
			<aliases>
				<alias name="$SalesEmails" expression="Sender.Person.WHERE(Department:Sales)"/>
			</aliases>
		</table>
		<table name="Participant">
			<fields>
				<field name="MessageAddress" type="LINK" table="Address" inverse="Messages"/>
				<field name="MessageAsExternalRecipient" type="LINK" table="Message" inverse="ExternalRecipients"/>
				<field name="MessageAsInternalRecipient" type="LINK" table="Message" inverse="InternalRecipients"/>
				<field name="MessageAsSender" type="LINK" table="Message" inverse="Sender"/>
				<field name="Person" type="LINK" table="Person" inverse="Messages"/>
				<field name="ReceiptDate" type="TIMESTAMP"/>
			</fields>
		</table>
		<table name="Address">
			<fields>
				<field name="Domain" type="LINK" table="Domain" inverse="Addresses"/>
				<field name="Messages" type="LINK" table="Participant" inverse="MessageAddress"/>
				<field name="Name" type="TEXT"/>
				<field name="Person" type="LINK" table="Person" inverse="MessageAddresses"/>
			</fields>
		</table>
		<table name="Person">
			<fields>
				<field name="Location">
					<fields>
						<field name="Department" type="TEXT"/>
						<field name="Office" type="TEXT"/>
					</fields>
				</field>
				<field name="DirectReports" type="LINK" table="Person" inverse="Manager"/>
				<field name="FirstName" type="TEXT"/>
				<field name="LastName" type="TEXT"/>
				<field name="Manager" type="LINK" table="Person" inverse="DirectReports"/>
				<field name="MessageAddresses" type="LINK" table="Address" inverse="Person"/>
				<field name="Messages" type="LINK" table="Participant" inverse="Person"/>
				<field name="Name" type="TEXT"/>
			</fields>
		</table>
		<table name="Domain">
			<fields>
				<field name="Addresses" type="LINK" table="Address" inverse="Domain"/>
				<field name="IsInternal" type="BOOLEAN"/>
				<field name="Name" type="TEXT"/>
			</fields>
		</table>
	</tables>
	<schedules>
		<schedule type="data-aging" value="0 0 3 * *"/>
	</schedules>
</application>
```

The same schema in JSON is shown below:

	{"Email": {
		"key": "EmailKey",
		"options": {"StorageService": "OLAPService"},
		"tables": {
			"Message": {
				"fields": {
					"InReplyTo": {"type": "XLINK", "table": "Message", "inverse": "Responses", "junction": "ThreadID"},
					"Participants": {
						"fields": {
							"Sender": {"type": "LINK", "table": "Participant", "inverse": "MessageAsSender"},
							"Recipients": {
								"fields": {
									"ExternalRecipients": {"type": "LINK", "table": "Participant", "inverse": "MessageAsExternalRecipient"},
									"InternalRecipients": {"type": "LINK", "table": "Participant", "inverse": "MessageAsInternalRecipient"}
								}
							}
						}
					},
					"Responses": {"type": "XLINK", "table": "Message", "inverse": "InReplyTo", "junction": "_ID"},
					"SendDate": {"type": "TIMESTAMP"},
					"Size": {"type": "INTEGER"},
					"Subject": {"type": "TEXT"},
					"Tags": {"collection": "true", "type": "TEXT"},
					"ThreadID": {"type": "TEXT"}
				},
				"aliases": {
					"$SalesEmails": {"expression": "Sender.Person.WHERE(Department:Sales)"}
				}
			},
			"Participant": {
				"fields": {
					"MessageAddress": {"type": "LINK", "table": "Address", "inverse": "Messages"},
					"MessageAsExternalRecipient": {"type": "LINK", "table": "Message", "inverse": "ExternalRecipients"},
					"MessageAsInternalRecipient": {"type": "LINK", "table": "Message", "inverse": "InternalRecipients"},
					"MessageAsSender": {"type": "LINK", "table": "Message", "inverse": "Sender"},
					"Person": {"type": "LINK", "table": "Person", "inverse": "Messages"},
					"ReceiptDate": {"type": "TIMESTAMP"}
				}
			},
			"Address": {
				"fields": {
					"Domain": {"type": "LINK", "table": "Domain", "inverse": "Addresses"},
					"Messages": {"type": "LINK", "table": "Participant", "inverse": "MessageAddress"},
					"Name": {"type": "TEXT"},
					"Person": {"type": "LINK", "table": "Person", "inverse": "MessageAddresses"}
				}
			},
			"Person": {
				"fields": {
					"DirectReports": {"type": "LINK", "table": "Person", "inverse": "Manager"},
					"FirstName": {"type": "TEXT"},
					"LastName": {"type": "TEXT"},
					"Location": {
						"fields": {
							"Department": {"type": "TEXT"},
							"Office": {"type": "TEXT"}
						}
					},
					"Manager": {"type": "LINK", "table": "Person", "inverse": "DirectReports"},
					"MessageAddresses": {"type": "LINK", "table": "Address", "inverse": "Person"},
					"Messages": {"type": "LINK", "table": "Participant", "inverse": "Person"},
					"Name": {"type": "TEXT"}
				}
			},
			"Domain": {
				"fields": {
					"Addresses": {"type": "LINK", "table": "Address", "inverse": "Domain"},
					"IsInternal": {"type": "BOOLEAN"},
					"Name": {"type": "TEXT"}
				}
			}
		},
		"schedules": [
			{"schedule": {"type": "data-aging", "value": "0 0 3 * *"}}
		]												
	}}

Some highlights of this schema:

* The application-level option `StorageService` explicitly assigns the application to the `OLAPService`.

* The `Message` table contains a group field called `Participants`, which contains the link field `Sender` and a second-level group called `Recipients`, which contains the links `InternalRecipients` and `ExternalRecipients`.

* The `Message` table contains a reflexive, cross-shard relationship formed by the xlinks `InReplyTo` and `Responses`.

* The `Message` table defines an alias called `$SalesEmails`, which is assigned the expression `Sender.Person.WHERE(Department:Sales). $SalesEmails` is dynamically expanded when used in DQL queries.

* The application defines a `data-aging` task, assigning its schedule the cron expression "`0 3 * * *`", which means "every day at 03:00".