What does the player's internal database structure look like?
- Keywords:
- database structure
The OpenSplash player creates a local SQLite database in the program folder (cache/cache.db) which is used to store media asset and playlist information retrieved from a content management server. The player maintains and reads the records from the database to determine what to play and when. It also logs events and content item plays, which can be sent back to the server.
Playlist Database structure
-------
Playlist related tables:
CoreDesigns - each record contains details of a single content item, or media asset available to play (eg bitmap images or movie files). These are referenced in the CoreZoneLoopSpots table
CoreFaceLayout - each record defines details of a logical Face, or canvas area, which displays the player output
CoreZoneLayout - each record defines a Zone, or screen area, which is displayed on a Face. There can be multiple zones for each face, and zones can be stacked as well as adjacent to each other. Each zone can run a playlist. Each zone record references a Face in the CoreFaceLayout table
CoreZoneLoops - each record contains a logical definition for a playlist which can be applied to a Zone. Each ZoneLoop record references a Zone in the CoreZoneLayout table
CoreZoneLoopSpots - each record specifies a content item from the CoreDesigns table as a playlist element, which can be assigned to a ZoneLoop playlist definition. Each ZoneLoopSpot record references a ZoneLoop in the CoreZoneLoops table
Table Structures
CoreDesigns
--------------
DesignID - GUID for the content item
BundleID - GUID allowing designs to be logically grouped for a single ZoneLoopSpot. You can have one or more designs in a 'bundle' and that bundle is referenced in the ZoneLoopSpot.
BundleOrder - Allows the server to specify a fixed order for content items in a single bundle when played as referenced by a ZoneLoopSpot record
URN - File name of the design, or content item. The content items are stored locally on the player system in the ‘local’ sub-folder
DurationMs - Duration in milliseconds. A duration of 0 signifies that the item is a still image, otherwise it is treated as a movie file.
Checksum - MD5 hash of the content item file, in hex. The player checks this value at startup for all records in the table, and if it doesn’t match the file, the file is assumed to be incorrect or corrupt, and is deleted and scheduled to be downloaded again from the server.
CoreFaceLayout
------------------
FaceID - GUID for this Face
PlayerID - GUID reference to the CorePlayer table
Height - height of the player Face canvas area in pixels
Width - width of the player Face canvas area in pixels
X - horizontal position of the Face top left corner in pixels, 0 is at the left of frame
Y - vertical position of the Face top left corner in pixels, 0 is at the top of frame
CoreZoneLayout
------------------
ZoneID - GUID for this Zone
FaceID - GUID reference to the CoreFaceLayout table, for the Face on which this Zone is situated
Height - height of the Zone area in pixels
Width - width of the Zone area in pixels
X - horizontal position of the Zone top left corner in pixels, 0 is on the left of frame
Y - vertical position of the Zone top left corner in pixels, 0 is at the top of frame
CoreZoneLoops
-----------------
ZoneLoopID - GUID for this ZoneLoop
ZoneID - GUID reference to the CoreZoneLayout table, for the Zone in which this ZoneLoop is scheduled
Start - start date/time for the ZoneLoop interpreted as the player's local time
End - end date/time for the ZoneLoop interpreted as the player's local time
LengthS - the length of the loop in seconds
FixedSpots - boolean value, whether the length of the spots inside the ZoneLoop is fixed
Overbookable - boolean value, whether a ZoneLoop can be overbooked (i.e. can we schedule more content than should fit into the loop?)
ZoneLoopSpot
----------------
ZoneLoopSpotID - GUID for the ZoneLoopSpot
ZoneLoopID - GUID reference to the CoreZoneLoop table, for the ZoneLoop to which this playlist element belongs
BookingID - GUID which can be used to tie the ZoneLoopSpot back to a specific booking, for example for proof of play. This field is not referenced in the other playlist tables, but is included in each entry in the CorePlayerLog reporting table
BundleID - GUID reference to the CoreDesigns table, representing the content bundle for this 'booking' (which may match one or more content items from the CoreDesigns table)
LengthS - the requested length of this playlist element in seconds
Start - start date/time on which this ZoneLoopSpot becomes valid to play, interpreted as the player's local time
End - end date/time on which this ZoneLoopSpot ceases to be valid to play, interpreted as the player's local time
StartTimeS - relative start time on which this ZoneLoopSpot becomes valid to play, relative to the start of the day (in seconds from midnight)
EndTimeS - relative end time on which this ZoneLoopSpot becomes valid to play, relative to the start of the day (in seconds from midnight)
WeekDays - days of the week on which this ZoneLoopSpot is valid to play, integer bit field expressed in decimal for each weekday, with all days = 127 (Monday = 1, Tuesday = 2, Wednesday = 4, etc)
PlayOrder - allows the server to specify a particular order for ZoneLoopSpots. If PlayOrder is set to the same for all records, playout is in record order in the table.
Notes
-------
Start/End specifies the date/time range that this ZoneLoopSpot is valid in (the duration of the playlist). Note that date/times are stored as a UNIX timestamp (ie seconds since the epoch). For example, a value of 1325376000 would be Sun, 01 Jan 2012 00:00:00 GMT.
StartTimeS/EndTimeS exist to support a scenario where the ZoneLoopSpot only plays within a certain period of the day. For example, 0 - 86400 would be 'all day'. 32400 - 36000 would be 9am - 10am. Note that the playlist generation included in OpenSplash does not currently support this scenario.
WeekDays exists to support a scenario where the ZoneLoopSpot only plays on certain days of the week. Again, the playlist generation included in OpenSplash does not currently support this scenario. For usage, see the OpenSplash.
