Difference between revisions of "WDLXTV ML MYSQL"

Latest revision as of 06:40, 6 January 2012

DROP TABLE IF EXISTS `video`;
CREATE TABLE `video`(
    `hash` VARCHAR(16) primary key,
    `title` VARCHAR(255),
    `file` VARCHAR(255),
    `path` VARCHAR(255),
    `size` INT UNSIGNED,
    `mtime` INT,
    `mtime_date` VARCHAR(15),
    `duration` INT UNSIGNED,
    `bitrate` VARCHAR(16),
    `vcodec` VARCHAR(32),
    `height` SMALLINT,
    `width` SMALLINT,
    `fps` FLOAT,
    `type` VARCHAR(20),
    `year` VARCHAR(4),
    `imdb` VARCHAR(32),
    `acodec` VARCHAR(32),
    `channels` VARCHAR(16),
    `abitrate` VARCHAR(16),
    `tvdb` VARCHAR(32),
    `epid` VARCHAR(32),
    `tmdb` INT UNSIGNED,
    `id` INT NOT NULL
);
CREATE INDEX idx_video_title on video(title);
CREATE INDEX idx_video_file on video(file);
CREATE INDEX idx_video_path on video(path);
CREATE INDEX idx_video_mtime on video(mtime);
CREATE INDEX idx_video_date on video(mtime_date);
CREATE INDEX idx_video_imdb on video(imdb);
CREATE INDEX idx_video_tvdb on video(tvdb);
CREATE INDEX idx_video_epid on video(epid);
CREATE INDEX idx_video_tmdb on video(tmdb);
CREATE INDEX idx_video_type on video(type);
CREATE INDEX idx_video_year on video(year);
CREATE INDEX idx_video_id on video(id);

DROP TABLE IF EXISTS  `movies`;
CREATE TABLE `movies` (
    `id` INT UNSIGNED NOT NULL primary key auto_increment,
    `imdb` VARCHAR(32),
    `tmdb` INT UNSIGNED,
    `title` VARCHAR(255),
    `originaltitle` VARCHAR(255),
    `tagline` VARCHAR(255),
    `plot` TEXT,
    `year` SMALLINT UNSIGNED,
    `runtime` SMALLINT UNSIGNED,
    `rating` FLOAT,
    `genre` VARCHAR(255),
    `actor` VARCHAR(255),
    `studio` VARCHAR(255),
    `coutry` VARCHAR(255),
    `releasedate` VARCHAR(12),
    `director` VARCHAR(255),
    `writers` VARCHAR(255),
    `certification` VARCHAR(4),
    `mpaa` VARCHAR(255)
);
CREATE INDEX idx_movies_imdb ON movies(imdb);
CREATE INDEX idx_movies_tmdb ON movies(tmdb);
CREATE INDEX idx_movies_title ON movies(title);
CREATE INDEX idx_movies_year ON movies(year);
CREATE INDEX idx_movies_genre ON movies(genre);
CREATE INDEX idx_movies_studio ON movies(studio);
CREATE INDEX idx_movies_rating ON movies(rating);

DROP TABLE IF EXISTS  `tvinfo`;
CREATE TABLE `tvinfo` (
	`id` INT UNSIGNED NOT NULL primary key auto_increment,
    `hash` VARCHAR(16),
    `tvdbid` INT UNSIGNED,
    `season` SMALLINT UNSIGNED,
    `episode` SMALLINT UNSIGNED,
    `episodes` VARCHAR(255),
    `episodename` VARCHAR(255),
    `episodenames` VARCHAR(255),
    `episodeplot` TEXT,
    `episodereleasedate` VARCHAR(12),
    `gueststars` VARCHAR(255)
);
CREATE INDEX idx_tvinfo_hash ON tvinfo(hash);
CREATE INDEX idx_tvinfo_tvdbid ON tvinfo(tvdbid);
CREATE INDEX idx_tvinfo_season ON tvinfo(season);
CREATE INDEX idx_tvinfo_episode ON tvinfo(episode);

DROP TABLE IF EXISTS `series`;
CREATE TABLE `series` (
    `id` INT UNSIGNED NOT NULL primary key, 
    `Actors` VARCHAR(255), 
    `Airs_DayOfWeek` VARCHAR(16), 
    `Airs_Time` VARCHAR(10), 
    `ContentRating` VARCHAR(10), 
    `FirstAired` VARCHAR(10), 
    `Genre` VARCHAR(255), 
    `IMDB_ID` VARCHAR(15), 
    `Language` VARCHAR(3), 
    `Network` VARCHAR(64), 
    `NetworkID` VARCHAR(64), 
    `Rating` FLOAT(3,1), 
    `Runtime` INT(5), 
    `SeriesID` INT(11), 
    `SeriesName` VARCHAR(64), 
    `Status` VARCHAR(32), 
    `banner` VARCHAR(255), 
    `fanart` VARCHAR(255), 
    `poster` VARCHAR(255), 
    `lastupdated` INT(11), 
    `Overview` TEXT 
);
CREATE INDEX idx_series_IMDB_ID on series(IMDB_ID);
CREATE INDEX idx_series_Rating on series(Rating);
CREATE INDEX idx_series_SeriesID on series(SeriesID);

DROP TABLE IF EXISTS `episodes`;
CREATE TABLE `episodes` (
    `id` INT(11) NOT NULL PRIMARY KEY,
    `Combined_episodenumber` VARCHAR(5),
    `Combined_season` VARCHAR(5),
    `DVD_chapter` VARCHAR(16),
    `DVD_episodenumber` VARCHAR(16),
    `DVD_discid` VARCHAR(16),
    `DVD_season` VARCHAR(16),
    `Director` VARCHAR(64),
    `EpImgFlag` VARCHAR(16),
    `FirstAired` VARCHAR(10),
    `GuestStars` VARCHAR(255),
    `IMDB_ID` VARCHAR(16),
    `Language` VARCHAR(5),
    `ProductionCode` VARCHAR(16),
    `Rating` FLOAT(3,1),
    `EpisodeName` VARCHAR(255),
    `EpisodeNumber` INT(5),
    `SeasonNumber` INT(5),
    `Writer` VARCHAR(64),
    `Filename` VARCHAR(255),
    `lastupdated` INT(11),
    `seasonid` INT(11),
    `seriesid` INT(11),
    `Overview` TEXT
);
CREATE INDEX idx_episodes_IMDB_ID on episodes(IMDB_ID);
CREATE INDEX idx_episodes_seasonid on episodes(seasonid);
CREATE INDEX idx_episodes_seriesid on episodes(seriesid);

DROP TABLE IF EXISTS `series_images`;
CREATE TABLE `series_images` (
    `id` INT UNSIGNED NOT NULL,
    `type` VARCHAR(16),
    `image` LONGBLOB
);
CREATE INDEX idx_series_images_id on series_images(id);
CREATE INDEX idx_series_images_type on series_images(type);

DROP TABLE IF EXISTS `episodes_images`;
CREATE TABLE `episodes_images` (
    `id` INT UNSIGNED NOT NULL,
    `type` VARCHAR(16),
    `image` LONGBLOB
);
CREATE INDEX idx_episodes_images_id on episodes_images(id);
CREATE INDEX idx_episodes_images_type on episodes_images(type);

DROP TABLE IF EXISTS `movies_images`;
CREATE TABLE `movies_images` (
    `id` INT UNSIGNED NOT NULL,
    `type` VARCHAR(16),
    `url` VARCHAR(255),
    `image` LONGBLOB
);
CREATE INDEX idx_movies_images_id on movies_images(id);
CREATE INDEX idx_movies_images_type on movies_images(type);
CREATE INDEX idx_movies_images_url on movies_images(url);

DROP TABLE IF EXISTS `movies_images_links`;
CREATE TABLE `movies_images_links` (
    `id` INT UNSIGNED NOT NULL,
    `type` VARCHAR(16) NOT NULL,
    `size` VARCHAR(32) NOT NULL,
    `width` INT UNSIGNED NOT NULL DEFAULT 0,
    `height` INT UNSIGNED NOT NULL DEFAULT 0,
    `url` VARCHAR(255) NOT NULL
);
CREATE INDEX idx_movies_images_links_id on movies_images_links(id);
CREATE INDEX idx_movies_images_links_type on movies_images_links(type);
CREATE INDEX idx_movies_images_links_size on movies_images_links(size);
CREATE INDEX idx_movies_images_links_width on movies_images_links(width);
CREATE INDEX idx_movies_images_links_height on movies_images_links(height);