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:
# - storagegroup
# - hostname
# - playgroup
# - watched
# - bookmark
# - cutlist
# - commflagged
# - transcoded
# - editing
# - filesize

##############################################################################
# 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,
	starttime     DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
	defaultfile   TINYINT(1) NOT NULL DEFAULT 0,

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

##############################################################################
# Table for storing info about a MythVideo video, joins to videofile.
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 '',
	plot          VARCHAR(8000) NOT NULL DEFAULT '',
	director      VARCHAR(128) NOT NULL DEFAULT '',
	rating        VARCHAR(128) NOT NULL DEFAULT '',
	inetref       VARCHAR(255) NOT NULL DEFAULT '',
	year          SMALLINT(5) NOT NULL DEFAULT 0,
	userrating    FLOAT NOT NULL DEFAULT 0,
	length        SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0,
	season        SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0,
	episode       SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0,
	showlevel     SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0,
	browse        TINYINT(1) NOT NULL DEFAULT 0,
	category      INT UNSIGNED NOT NULL DEFAULT 0,
	coverfile     INT UNSIGNED NOT NULL DEFAULT 0,
	trailer       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 (videoid)
	);

##############################################################################
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,

# 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,
        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),
	aspect          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,

	PRIMARY KEY (streamid, sampleframe)
	);

##############################################################################
DROP TABLE IF EXISTS videoseek;
CREATE TABLE videoseek (
	fileid        INT UNSIGNED NOT NULL DEFAULT 0,
	mark          MEDIUMINT(8) NOT NULL DEFAULT 0,
	type          TINYINT(4) NOT NULL DEFAULT 0,
	offset        BIGINT(20) 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) NOT NULL DEFAULT 0,
	type          TINYINT(4) NOT NULL DEFAULT 0,
	data          INT 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;