Persisting the Configuration (Updated)


[Updated and expanded on March 5]

HVP-Config-DB For this week’s release of the Silverlight HyperVideo Platform we are committed to having configuration information retrieved from a database. To accomplish this we need (want?)

Smooth-streaming videos that we can use to illustrate the relationship among Items, Topics and Links and

A Database in which to store the configuration information

Step 1 was accomplished by

  • Creating the videos
  • Creating image files
  • Uploading the videos and images to MSN where the videos were encoded for smooth streaming and URLs were returned for the image and for the video
  • Watching the videos and noting where we want to mark topics and links
  • Adding that information to the database

Step 2 was accomplished by creating a Database with four tables

  • Listables
  • Items
  • Topics
  • Links
The Listables table’s relationship with the other three is isomorphic to the relationship between the Listable base class and the derived classes Item, Topic and Link. All of this is captured in the database diagram shown above.

Foreign Keys

[sql]SELECT dbo.Items.ID AS ItemsID,
dbo.Listables.ID AS ListablesID,
dbo.Listables ON
dbo.Items.MyListableID = dbo.Listables.ID[/sql]
The Sets, Items, Topics and Links tables each represent part of an object, with the remaining part held in Listables. This “normalization” of the database reduces the duplication of data, and thus reduces the likelihood of database corruption.

The Sets, Items, Links and Topics tables each have a column titled MyLinkableId which is a foreign key linked to the key ID field in the Linkables table. This lets us create views such as this definition of the ItemsView

As shown in the image, the column MyListableID in Items and the column ID in Listables are used to join the two tables together into a complete Item.

An Item’s Links and Topics

The second set of connections from each of Links and Topics to the Listables (colored blue) represent the has a ItemsViewrelationship from Items to Topics and from Items to Links.

Thus, the database has captured all the object relationships, but has translated it into relational terms.

The next step is to use the Entity Frameworks to translate back to objects, and WCF RIA Services to create those objects both on the database server and on the Silverlight client.

Adding Data to the Tables

For what follows, assume I have the following videos, topics and links:

Assume I have a set of videos in this relationship

Set – 0 Items:

  • Item – Hypervideo Overview (video)
  • Item- Items and Links (video)

Topics for HyperVideo Overview:

  • Topics and Links
  • Multiple Items
  • Clicking a Link

Links for HyperVideo Overview:

  • Silverlight: Get started
  • Sets
  • MVVM

We will have course have authoring tools for adding this information to the database, but for now let’s add it by hand.

Step 1 – Make an entry for the Set
[sql]– set
insert into Listables (TextToDisplay, ImageSource) values ( ‘ ‘, ‘ ‘)[/sql]
Step 2 – Man entries for the two items

Each entry is in two parts: the “base class” table, Listables, and then the “derived class” Items:
[sql]insert into Listables (TextToDisplay, ImageSource) values (
‘HyperVideo Overview’,
insert into Items (MyListableID, ItemUri) values
Step 3 – Enter the Topics for the first item
[sql]insert into Listables (TextToDisplay,ImageSource)
values ( ‘Topics and Links’, ‘ ‘)
insert into Topics(MyListableID,MyItem,Offset)
values (14,13,25)

insert into Listables ( TextToDisplay, ImageSource)
values ( ‘MultipleItems’, ‘ ‘)
insert into Topics(MyListableID,MyItem,Offset)
values (15,13,52)

insert into Listables ( TextToDisplay, ImageSource)
values ( ‘Clicking A Link’, ‘ ‘)
insert into Topics(MyListableID,MyItem,Offset)
values (16,13,85)[/sql]
Note that each of the three topics is entered in two parts, the Listable and the Topic.

Step 4 – Enter the Links for the first item
[sql]insert into Listables ( TextToDisplay, ImageSource)
values ( ‘Silverlight: Get Started’, ‘ ‘)
insert into Links(MyListableID, MyItem, Offset)
values (17,13,37)

insert into Listables ( TextToDisplay, ImageSource)
values ( ‘Sets’, ‘ ‘)
insert into Links(MyListableID, MyItem, Offset)
values (18,13,49)

insert into Listables ( TextToDisplay, ImageSource)
values ( ‘MVVM ‘, ‘ ‘)
insert into Links(MyListableID, MyItem, Offset)
values (19,13,90)[/sql]
We would then enter the topics and links for the first item as well.

Examining the raw data and the relationshiops

When we’re done, we can do some queries to make sure that the data makes sense. First, we’ll examine the Listables table; ListablesResultswe should see the “base” data for the set, for each item and for all the topics and links:

That is, in fact, exactly what we see. Each entry has a unique ID (in this case an integer, in a future release a GUID). Entry 1 is the Set (which has no text or image). Entry 2 is the first Item we entered.

Entries 3-7 are the Listable parts of the Topics for the first item and entries 8-13 are the Listable parts of the Links for the first item.

Note that Listable ID 13 represeents the second item, and that is followed by the Listable parts for the topics and links of the second item.

To see these relationships, we can query the TopicsView to see that the database has captured the Text to display, the offset (in seconds) of where to jump to if a topic is chosen, and the relationship between the Topic entry, its own Listable entry, and the Listable entry for the Item that the topic relates to.
[sql]select TopicsID, MyListableID, TextToDisplay, MyItem, Offset
from TopicsView[/sql]
The result of this query displays 5 columns,

TopicsViewTopicsID is the unique ID for each Topic entry

MyListableID is the ID of the entry in Listable that completes this topic (i.e., the base part of this Topic)

TextToDisplay is the text that will be displayed in the Topics window

MyItem is the ListableID of the item that these topics are for (e.e., Listables 2 and 13 shown above)

Finally, Offset is the number of seconds into the video that clicking on the topic will scrub to.

This work is licensed under a Creative Commons license.

About Jesse Liberty

Jesse Liberty has three decades of experience writing and delivering software projects and is the author of 2 dozen books and a couple dozen Pluralsight & LinkedIn Learning courses. He was a Senior Technical Evangelist for Microsoft, a Distinguished Software Engineer for AT&T, a VP for Information Services for Citibank and a Software Architect for PBS. He is a Xamarin Certified Mobile Developer and a Xamarin MVP and a Microsoft MVP.
This entry was posted in Mini-Tutorial and tagged . Bookmark the permalink.

One Response to Persisting the Configuration (Updated)

Comments are closed.