This page is to track the proposed schema changes and notes for changes to the file tracking, seektable, etc. information stored in the database.

##############################################################################
# This file describes the schema updates to accomplish several tasks related
# to MythTV and MythVideo.
#
# 1) Create a new 'videofile' table which is used to store common info about
#    MythTV recording files and MythVideo files
# 2) Replace the exising (unused) 'recordedfile' table with a new table which
#    joins the recorded table with the new videofile table.  This will allow
#    recordings to have multiple files associated with them, such as the
#    original recording, a transcoded low-def version, and a .flv version for
#    streaming via MythWeb.
# 3) Create a new 'videofilemetadata' table to replace the existing
#    videometadata table used by MythVideo.  This table joins with videofile
#    to get the file info for the video.  Later, drop videometadata.  We
#    could instead just alter the current videometadata, but this schema
#    shows the table as it would be defined if new rather than showing what
#    fields would be dropped if we modified the existing table.
# 4) Merge MythTV's recordedseek and MythVideo's filemarkup seektable into
#    a new videoseek table that uses a videofile.id as the key.
# 5) Merge MythTV's recordedmarkup and MythVideo's filemarkup markups into
#    a new videomarkup table that uses a videofile.id as they key.

# TODO:
# 1) sanity check this whole thing.
# 2) add a couple tables to support playlists of recordings and videos.
#    this is different than than the 'play this file after that' functionality
#    in MythVideo, this is for saving real playlists.

##############################################################################
# Notes about existing recorded table
#
# recorded.basename is used to store the default filename from videofile
#
# Two versions after migration, delete the following fields which would be
#     unused at that time:
# - starttime
# - endtime
# - storagegroup
# - hostname
# - playgroup
# - watched
# - bookmark
# - cutlist
# - commflagged
# - transcoded
# - editing
# - filesize
# - stars
# - originalairdate
#
# Add metadataid, which refers to a record in metadatacommon.
# 
# The former recorded.starttime/endtime values, which correspond to recording
#    start/end times, will be in recordedfile.recstart and recordedfile.recend.
# The former recorded.stars value will be in metadatacommon.stars.
# The former recorded.originalairdate value will be in
#    metadatacommon.releasedate.
# The recorded.progstart/progend columns will be moved to the location where
#    recorded.starttime/endtime were.

##############################################################################
# Table for storing MythTV recording files, joins to recorded and videofile.
# Recordings can have multiple files, so we need this table to join the files
#     to the recordings, rather than just being able to add a fileid to the
#     recorded table.
DROP TABLE IF EXISTS new_recordedfile;
CREATE TABLE new_recordedfile (
	fileid        INT UNSIGNED NOT NULL DEFAULT 0,

	chanid        INT UNSIGNED NOT NULL DEFAULT 0,
	progstart     DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',

# Recording start and end times.  The (new) recorded table's start/end times
# will reference only values from listings data--allowing us to link multiple
# parts of the same recording to a single row in recorded.
        recstart      DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
        recend      DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',

	defaultfile   TINYINT(1) NOT NULL DEFAULT 0,

	PRIMARY KEY (chanid, progstart, fileid),
	INDEX (fileid)
	);

##############################################################################
# Table for storing info about a MythVideo video, joins to videofile.
#  - videometadata.userrating will be in metadatacommon.stars
#  - videometadata.rating will be in metadatacommon.certification
#  - videometadta.year will be combined with/replaced by releasedate
#
DROP TABLE IF EXISTS videofilemetadata;
CREATE TABLE videofilemetadata (
	videoid       INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

	title         VARCHAR(128) NOT NULL DEFAULT '',
	subtitle      VARCHAR(128) NOT NULL DEFAULT '',
        tagline       VARCHAR(255) NOT NULL DEFAULT '',
	plot          VARCHAR(8000) NOT NULL DEFAULT '',
	length        SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0,
        insertdate    DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
	showlevel     SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0,
	browse        TINYINT(1) NOT NULL DEFAULT 0,
	category      INT UNSIGNED NOT NULL DEFAULT 0,
        metadataid    INT UNSIGNED NOT NULL DEFAULT 0,

	INDEX (videoid)
	);

##############################################################################
# Table for common metadata for recordings and MythVideo videos.
#  - metadatacommon.stars replaces videometadata.userrating and recorded.stars
#  - metadatacommon.certification replaces videometadata.rating
#
DROP TABLE IF EXISTS metadatacommon;
CREATE TABLE metadatacommon (
        metadataid    INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

	director      VARCHAR(128) NOT NULL DEFAULT '',
        studio        VARCHAR(128) NOT NULL DEFAULT '',
	certification VARCHAR(128) NOT NULL DEFAULT '',
	inetref       VARCHAR(255) NOT NULL DEFAULT '',
        homepage      VARCHAR(8000) NOT NULL DEFAULT '',
	season        SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0,
	episode       SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0,
        releasedate   DATE NOT NULL DEFAULT '0000-00-00',
	stars         FLOAT NOT NULL DEFAULT 0,
        processed     TINYINT(1) NOT NULL DEFAULT 0,
	trailer       INT UNSIGNED NOT NULL DEFAULT 0,
	coverfile     INT UNSIGNED NOT NULL DEFAULT 0,
	screenshot    INT UNSIGNED NOT NULL DEFAULT 0,
	banner        INT UNSIGNED NOT NULL DEFAULT 0,
	fanart        INT UNSIGNED NOT NULL DEFAULT 0,

	INDEX (metadataid)
	);

##############################################################################
DROP TABLE IF EXISTS videopart;
CREATE TABLE videopart (
	fileid        INT UNSIGNED NOT NULL DEFAULT 0,
	videoid       INT UNSIGNED NOT NULL DEFAULT 0,

# playback order if multiple files for a video (ie, part1, part2, etc.)
	order         SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0,

	PRIMARY KEY (fileid, videoid),
	INDEX (fileid)
	);

##############################################################################
DROP TABLE IF EXISTS mediafile;
CREATE TABLE mediafile (

# Fields in no particular order, just ones with notes first at the top.

# file ID
	fileid          INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

# who owns this file
	hostname        VARCHAR(64) NOT NULL,

# Storage Group
	storagegroup    VARCHAR(32) NOT NULL DEFAULT 'Default',

# filename relative to the Storage Group dir
	filename        VARCHAR(255) NOT NULL,

# ID# of SG directory where file was last found (used/updated by GetPlaybackURL())
	dirid           INT UNSIGNED NOT NULL DEFAULT 0,

# type ('Video', 'Image', etc., as defined by an enum in MythTV)
	type            INT UNSIGNED NOT NULL DEFAULT 0,

# subtype ('Trailer', 'Preview', 'Fanart', etc., as defined by an enum in MythTV)
	subtype         INT UNSIGNED NOT NULL DEFAULT 0,

# comment ('Original Recording', 'Transcoded Copy', 'iPod format', etc.)
#             for use by MythTV when asking the user which of multiple files
#             for a recording do they want to play.
	comment         VARCHAR(64) NOT NULL DEFAULT '',

	INDEX (filename)
	);

##############################################################################
DROP TABLE IF EXISTS videofile;
CREATE TABLE videofile (

# Fields in no particular order, just ones with notes first at the top.

# file ID
	fileid          INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

# this file in use by MythTV as a recording and has a recordedfile entry 
	isRecording     TINYINT(1) NOT NULL DEFAULT 0,

# this file in use by MythVideo and has a video(file)metadata entry
	isVideo         TINYINT(1) NOT NULL DEFAULT 0,

	filesize        BIGINT(20) NOT NULL DEFAULT 0,
	filehash        VARCHAR(32) NOT NULL DEFAULT '',
	playgroup       VARCHAR(32) NOT NULL DEFAULT 'Default',
	watched         TINYINT(1) NOT NULL DEFAULT 0,
	bookmark        TINYINT(1) NOT NULL DEFAULT 0,
	cutlist         TINYINT(1) NOT NULL DEFAULT 0,
	commflagged     TINYINT(1) NOT NULL DEFAULT 0,
	transcoded      TINYINT(1) NOT NULL DEFAULT 0,
	editing         TINYINT(1) NOT NULL DEFAULT 0,
	);

##############################################################################
# Stream information
DROP TABLE IF EXISTS streaminfo;
CREATE TABLE streaminfo (
        id              INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
	fileid          INT UNSIGNED NOT NULL DEFAULT 0,

	type            INT UNSIGNED NOT NULL DEFAULT 0,
# length of video in seconds (used for display of total duration information to user)
        length          BIGINT(20) NOT NULL DEFAULT 0,

        primarystream   TINYINT(1) NOT NULL DEFAULT 0,        
        description     VARCHAR(255) NOT NULL DEFAULT ''
	);

##############################################################################
# Sample video attributes taken at a particular frame number
DROP TABLE IF EXISTS videoinfo;
CREATE TABLE videoinfo (
        streamid        INT UNSIGNED NOT NULL DEFAULT 0,
	sampleframe     INT UNSIGNED NOT NULL DEFAULT 0,

	width           SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0,
	height          SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0,
	fps             FLOAT(6,3),
	displayaspect   FLOAT(8,3),
        pixelaspect     FLOAT(8,3),

	PRIMARY KEY (streamid, sampleframe)
	);

##############################################################################
# Sample audio attributes taken at a particular (video) frame number
DROP TABLE IF EXISTS audioinfo;
CREATE TABLE audioinfo (
        streamid        INT UNSIGNED NOT NULL DEFAULT 0,
	sampleframe     INT UNSIGNED NOT NULL DEFAULT 0,

	rate            SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0,
	bits            SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0,
	channels        TINYINT(3) NOT NULL DEFAULT 0,
        language        VARCHAR(10) NOT NULL DEFAULT '',

	PRIMARY KEY (streamid, sampleframe)
	);

##############################################################################
DROP TABLE IF EXISTS videoseek;
CREATE TABLE videoseek (
	fileid        INT UNSIGNED NOT NULL DEFAULT 0,
	mark          MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT 0,
	type          TINYINT(4) NOT NULL DEFAULT 0,
	offset        BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
	PRIMARY KEY (fileid, mark, type)
	);

##############################################################################
DROP TABLE IF EXISTS videomarkup;
CREATE TABLE videomarkup (
	fileid        INT UNSIGNED NOT NULL DEFAULT 0,
	mark          MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT 0,
	type          TINYINT(4) NOT NULL DEFAULT 0,
	data          INT UNSIGNED NOT NULL DEFAULT 0,
	PRIMARY KEY (fileid, mark, type)
	);

##############################################################################
# inuseprograms modification to allow tracking all videofiles in use
#
# ALTER TABLE inuseprograms ADD fileid INT NOT NULL DEFAULT 0;