Difference between revisions of "WDLXTV ML MYSQL"

From WikiDLXTV
Jump to: navigation, search
(Created page with '<pre> DROP TABLE IF EXISTS `video`; CREATE TABLE `video`( `hash` VARCHAR(16) primary key, `title` VARCHAR(255), `file` VARCHAR(255), `path` VARCHAR(255), `siz…')
 
 
Line 73: Line 73:
 
`id` INT UNSIGNED NOT NULL primary key auto_increment,
 
`id` INT UNSIGNED NOT NULL primary key auto_increment,
 
     `hash` VARCHAR(16),
 
     `hash` VARCHAR(16),
`tvdbid` INT UNSIGNED,
+
    `tvdbid` INT UNSIGNED,
`season` SMALLINT UNSIGNED,
+
    `season` SMALLINT UNSIGNED,
`episode` SMALLINT UNSIGNED,
+
    `episode` SMALLINT UNSIGNED,
`episodes` VARCHAR(255),
+
    `episodes` VARCHAR(255),
`episodename` VARCHAR(255),
+
    `episodename` VARCHAR(255),
`episodenames` VARCHAR(255),
+
    `episodenames` VARCHAR(255),
 
     `episodeplot` TEXT,
 
     `episodeplot` TEXT,
 
     `episodereleasedate` VARCHAR(12),
 
     `episodereleasedate` VARCHAR(12),
Line 90: Line 90:
 
DROP TABLE IF EXISTS `series`;
 
DROP TABLE IF EXISTS `series`;
 
CREATE TABLE `series` (
 
CREATE TABLE `series` (
  `id` INT UNSIGNED NOT NULL primary key,  
+
    `id` INT UNSIGNED NOT NULL primary key,  
  `Actors` VARCHAR(255),  
+
    `Actors` VARCHAR(255),  
  `Airs_DayOfWeek` VARCHAR(16),  
+
    `Airs_DayOfWeek` VARCHAR(16),  
  `Airs_Time` VARCHAR(10),  
+
    `Airs_Time` VARCHAR(10),  
  `ContentRating` VARCHAR(10),  
+
    `ContentRating` VARCHAR(10),  
  `FirstAired` VARCHAR(10),  
+
    `FirstAired` VARCHAR(10),  
  `Genre` VARCHAR(255),  
+
    `Genre` VARCHAR(255),  
  `IMDB_ID` VARCHAR(15),  
+
    `IMDB_ID` VARCHAR(15),  
  `Language` VARCHAR(3),  
+
    `Language` VARCHAR(3),  
  `Network` VARCHAR(64),  
+
    `Network` VARCHAR(64),  
  `NetworkID` VARCHAR(64),  
+
    `NetworkID` VARCHAR(64),  
  `Rating` FLOAT(3,1),  
+
    `Rating` FLOAT(3,1),  
  `Runtime` INT(5),  
+
    `Runtime` INT(5),  
  `SeriesID` INT(11),  
+
    `SeriesID` INT(11),  
  `SeriesName` VARCHAR(64),  
+
    `SeriesName` VARCHAR(64),  
  `Status` VARCHAR(32),  
+
    `Status` VARCHAR(32),  
  `banner` VARCHAR(255),  
+
    `banner` VARCHAR(255),  
  `fanart` VARCHAR(255),  
+
    `fanart` VARCHAR(255),  
  `poster` VARCHAR(255),  
+
    `poster` VARCHAR(255),  
  `lastupdated` INT(11),  
+
    `lastupdated` INT(11),  
  `Overview` TEXT  
+
    `Overview` TEXT  
 
);
 
);
 
CREATE INDEX idx_series_IMDB_ID on series(IMDB_ID);
 
CREATE INDEX idx_series_IMDB_ID on series(IMDB_ID);
Line 118: Line 118:
 
DROP TABLE IF EXISTS `episodes`;
 
DROP TABLE IF EXISTS `episodes`;
 
CREATE TABLE `episodes` (
 
CREATE TABLE `episodes` (
  `id` INT(11) NOT NULL PRIMARY KEY,
+
    `id` INT(11) NOT NULL PRIMARY KEY,
  `Combined_episodenumber` VARCHAR(5),
+
    `Combined_episodenumber` VARCHAR(5),
  `Combined_season` VARCHAR(5),
+
    `Combined_season` VARCHAR(5),
  `DVD_chapter` VARCHAR(16),
+
    `DVD_chapter` VARCHAR(16),
  `DVD_episodenumber` VARCHAR(16),
+
    `DVD_episodenumber` VARCHAR(16),
  `DVD_discid` VARCHAR(16),
+
    `DVD_discid` VARCHAR(16),
  `DVD_season` VARCHAR(16),
+
    `DVD_season` VARCHAR(16),
  `Director` VARCHAR(64),
+
    `Director` VARCHAR(64),
  `EpImgFlag` VARCHAR(16),
+
    `EpImgFlag` VARCHAR(16),
  `FirstAired` VARCHAR(10),
+
    `FirstAired` VARCHAR(10),
  `GuestStars` VARCHAR(255),
+
    `GuestStars` VARCHAR(255),
  `IMDB_ID` VARCHAR(16),
+
    `IMDB_ID` VARCHAR(16),
  `Language` VARCHAR(5),
+
    `Language` VARCHAR(5),
  `ProductionCode` VARCHAR(16),
+
    `ProductionCode` VARCHAR(16),
  `Rating` FLOAT(3,1),
+
    `Rating` FLOAT(3,1),
  `EpisodeName` VARCHAR(255),
+
    `EpisodeName` VARCHAR(255),
  `EpisodeNumber` INT(5),
+
    `EpisodeNumber` INT(5),
  `SeasonNumber` INT(5),
+
    `SeasonNumber` INT(5),
  `Writer` VARCHAR(64),
+
    `Writer` VARCHAR(64),
  `Filename` VARCHAR(255),
+
    `Filename` VARCHAR(255),
  `lastupdated` INT(11),
+
    `lastupdated` INT(11),
  `seasonid` INT(11),
+
    `seasonid` INT(11),
  `seriesid` INT(11),
+
    `seriesid` INT(11),
  `Overview` TEXT
+
    `Overview` TEXT
 
);
 
);
 
CREATE INDEX idx_episodes_IMDB_ID on episodes(IMDB_ID);
 
CREATE INDEX idx_episodes_IMDB_ID on episodes(IMDB_ID);
Line 169: Line 169:
 
     `id` INT UNSIGNED NOT NULL,
 
     `id` INT UNSIGNED NOT NULL,
 
     `type` VARCHAR(16),
 
     `type` VARCHAR(16),
`url` VARCHAR(255),
+
    `url` VARCHAR(255),
 
     `image` LONGBLOB
 
     `image` LONGBLOB
 
);
 
);
Line 180: Line 180:
 
     `id` INT UNSIGNED NOT NULL,
 
     `id` INT UNSIGNED NOT NULL,
 
     `type` VARCHAR(16) NOT NULL,
 
     `type` VARCHAR(16) NOT NULL,
`size` VARCHAR(32) NOT NULL,
+
    `size` VARCHAR(32) NOT NULL,
`width` INT UNSIGNED NOT NULL DEFAULT 0,
+
    `width` INT UNSIGNED NOT NULL DEFAULT 0,
`height` INT UNSIGNED NOT NULL DEFAULT 0,
+
    `height` INT UNSIGNED NOT NULL DEFAULT 0,
 
     `url` VARCHAR(255) NOT NULL
 
     `url` VARCHAR(255) NOT NULL
 
);
 
);

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);