Difference between revisions of "WDLXTV ML SQLITE"

Revision as of 05:35, 6 January 2012

BEGIN TRANSACTION;

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` INTEGER NOT NULL primary key autoincrement,
    `imdb` VARCHAR(32),
    `tmdb` UNSIGNED INTEGER,
    `title` VARCHAR,
    `originaltitle` VARCHAR,
    `tagline` VARCHAR,
    `plot` VARCHAR,
    `year` UNSIGNED SMALLINT,
    `runtime` UNSIGNED SMALLINT,
    `rating` float,
    `genre` VARCHAR,
    `actor` VARCHAR,
    `studio` VARCHAR,
    `coutry` VARCHAR,
    `releasedate` VARCHAR(12),
    `director` VARCHAR,
    `writers` VARCHAR,
    `certification` VARCHAR(4),
    `mpaa` VARCHAR
);
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` INTEGER NOT NULL primary key autoincrement,
    `hash` VARCHAR(16),
	`tvdbid` UNSIGNED INTEGER,
	`season` UNSIGNED SMALLINT,
	`episode` UNSIGNED SMALLINT,
	`episodes` VARCHAR,
	`episodename` VARCHAR,
	`episodenames` VARCHAR,
    `episodeplot` VARCHAR,
    `episodereleasedate` VARCHAR(12),
    `gueststars` VARCHAR
);
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` INTEGER NOT NULL primary key, 
  `Actors` varchar(255) DEFAULT NULL, 
  `Airs_DayOfWeek` varchar(16) DEFAULT NULL, 
  `Airs_Time` varchar(10) DEFAULT NULL, 
  `ContentRating` varchar(10) DEFAULT NULL, 
  `FirstAired` varchar(10) DEFAULT NULL, 
  `Genre` varchar(255) DEFAULT NULL, 
  `IMDB_ID` varchar(15) DEFAULT NULL, 
  `Language` varchar(3) DEFAULT NULL, 
  `Network` varchar(64) DEFAULT NULL, 
  `NetworkID` varchar(64) DEFAULT NULL, 
  `Rating` float(3,1) DEFAULT NULL, 
  `Runtime` int(5) DEFAULT NULL, 
  `SeriesID` int(11) DEFAULT NULL, 
  `SeriesName` varchar(64) DEFAULT NULL, 
  `Status` varchar(32) DEFAULT NULL, 
  `banner` varchar(255) DEFAULT NULL, 
  `fanart` varchar(255) DEFAULT NULL, 
  `poster` varchar(255) DEFAULT NULL, 
  `lastupdated` int(11) DEFAULT NULL, 
  `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) DEFAULT NULL,
  `Combined_season` varchar(5) DEFAULT NULL,
  `DVD_chapter` varchar(16) DEFAULT NULL,
  `DVD_episodenumber` varchar(16) DEFAULT NULL,
  `DVD_discid` varchar(16) DEFAULT NULL,
  `DVD_season` varchar(16) DEFAULT NULL,
  `Director` varchar(64) DEFAULT NULL,
  `EpImgFlag` varchar(16) DEFAULT NULL,
  `FirstAired` varchar(10) DEFAULT NULL,
  `GuestStars` varchar(255) DEFAULT NULL,
  `IMDB_ID` varchar(16) DEFAULT NULL,
  `Language` varchar(5) DEFAULT NULL,
  `ProductionCode` varchar(16) DEFAULT NULL,
  `Rating` float(3,1) DEFAULT NULL,
  `EpisodeName` varchar(255) DEFAULT NULL,
  `EpisodeNumber` int(5) DEFAULT NULL,
  `SeasonNumber` int(5) DEFAULT NULL,
  `Writer` varchar(64) DEFAULT NULL,
  `Filename` varchar(255) DEFAULT NULL,
  `lastupdated` int(11) DEFAULT NULL,
  `seasonid` int(11) DEFAULT NULL,
  `seriesid` int(11) DEFAULT NULL,
  `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(11) NOT NULL,
    `type` VARCHAR(16) DEFAULT NULL,
    `image` BLOB DEFAULT NULL
);
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(11) NOT NULL,
    `type` VARCHAR(16) DEFAULT NULL,
    `image` BLOB DEFAULT NULL
);
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(11) NOT NULL,
    `type` VARCHAR(16) DEFAULT NULL,
	`url` VARCHAR(255) NOT NULL,
    `image` BLOB DEFAULT NULL
);
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(11) NOT NULL,
    `type` VARCHAR(16) NOT NULL,
	`size` VARCHAR(32) NOT NULL,
	`width` INT NOT NULL DEFAULT 0,
	`height` INT 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);

COMMIT;
VACUUM;