Difference between revisions of "WDLXTV ML MYSQL"

Revision as of 06:36, 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);