Difference between revisions of "WDLXTV ML SQLITE"

From WikiDLXTV
Jump to: navigation, search
(Created page with '<pre> BEGIN TRANSACTION; DROP TABLE IF EXISTS video; CREATE TABLE `video`( `hash` VARCHAR(16) primary key, `title` VARCHAR(255), `file` VARCHAR(255), `path` VARC…')
 
 
Line 75: Line 75:
 
`id` INTEGER NOT NULL primary key autoincrement,
 
`id` INTEGER NOT NULL primary key autoincrement,
 
     `hash` VARCHAR(16),
 
     `hash` VARCHAR(16),
`tvdbid` UNSIGNED INTEGER,
+
    `tvdbid` UNSIGNED INTEGER,
`season` UNSIGNED SMALLINT,
+
    `season` UNSIGNED SMALLINT,
`episode` UNSIGNED SMALLINT,
+
    `episode` UNSIGNED SMALLINT,
`episodes` VARCHAR,
+
    `episodes` VARCHAR,
`episodename` VARCHAR,
+
    `episodename` VARCHAR,
`episodenames` VARCHAR,
+
    `episodenames` VARCHAR,
 
     `episodeplot` VARCHAR,
 
     `episodeplot` VARCHAR,
 
     `episodereleasedate` VARCHAR(12),
 
     `episodereleasedate` VARCHAR(12),
Line 92: Line 92:
 
DROP TABLE IF EXISTS `series`;
 
DROP TABLE IF EXISTS `series`;
 
CREATE TABLE `series` (
 
CREATE TABLE `series` (
  `id` INTEGER NOT NULL primary key,  
+
    `id` INTEGER NOT NULL primary key,  
  `Actors` varchar(255) DEFAULT NULL,  
+
    `Actors` varchar(255) DEFAULT NULL,  
  `Airs_DayOfWeek` varchar(16) DEFAULT NULL,  
+
    `Airs_DayOfWeek` varchar(16) DEFAULT NULL,  
  `Airs_Time` varchar(10) DEFAULT NULL,  
+
    `Airs_Time` varchar(10) DEFAULT NULL,  
  `ContentRating` varchar(10) DEFAULT NULL,  
+
    `ContentRating` varchar(10) DEFAULT NULL,  
  `FirstAired` varchar(10) DEFAULT NULL,  
+
    `FirstAired` varchar(10) DEFAULT NULL,  
  `Genre` varchar(255) DEFAULT NULL,  
+
    `Genre` varchar(255) DEFAULT NULL,  
  `IMDB_ID` varchar(15) DEFAULT NULL,  
+
    `IMDB_ID` varchar(15) DEFAULT NULL,  
  `Language` varchar(3) DEFAULT NULL,  
+
    `Language` varchar(3) DEFAULT NULL,  
  `Network` varchar(64) DEFAULT NULL,  
+
    `Network` varchar(64) DEFAULT NULL,  
  `NetworkID` varchar(64) DEFAULT NULL,  
+
    `NetworkID` varchar(64) DEFAULT NULL,  
  `Rating` float(3,1) DEFAULT NULL,  
+
    `Rating` float(3,1) DEFAULT NULL,  
  `Runtime` int(5) DEFAULT NULL,  
+
    `Runtime` int(5) DEFAULT NULL,  
  `SeriesID` int(11) DEFAULT NULL,  
+
    `SeriesID` int(11) DEFAULT NULL,  
  `SeriesName` varchar(64) DEFAULT NULL,  
+
    `SeriesName` varchar(64) DEFAULT NULL,  
  `Status` varchar(32) DEFAULT NULL,  
+
    `Status` varchar(32) DEFAULT NULL,  
  `banner` varchar(255) DEFAULT NULL,  
+
    `banner` varchar(255) DEFAULT NULL,  
  `fanart` varchar(255) DEFAULT NULL,  
+
    `fanart` varchar(255) DEFAULT NULL,  
  `poster` varchar(255) DEFAULT NULL,  
+
    `poster` varchar(255) DEFAULT NULL,  
  `lastupdated` int(11) DEFAULT NULL,  
+
    `lastupdated` int(11) DEFAULT NULL,  
  `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 120: Line 120:
 
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) DEFAULT NULL,
+
    `Combined_episodenumber` varchar(5) DEFAULT NULL,
  `Combined_season` varchar(5) DEFAULT NULL,
+
    `Combined_season` varchar(5) DEFAULT NULL,
  `DVD_chapter` varchar(16) DEFAULT NULL,
+
    `DVD_chapter` varchar(16) DEFAULT NULL,
  `DVD_episodenumber` varchar(16) DEFAULT NULL,
+
    `DVD_episodenumber` varchar(16) DEFAULT NULL,
  `DVD_discid` varchar(16) DEFAULT NULL,
+
    `DVD_discid` varchar(16) DEFAULT NULL,
  `DVD_season` varchar(16) DEFAULT NULL,
+
    `DVD_season` varchar(16) DEFAULT NULL,
  `Director` varchar(64) DEFAULT NULL,
+
    `Director` varchar(64) DEFAULT NULL,
  `EpImgFlag` varchar(16) DEFAULT NULL,
+
    `EpImgFlag` varchar(16) DEFAULT NULL,
  `FirstAired` varchar(10) DEFAULT NULL,
+
    `FirstAired` varchar(10) DEFAULT NULL,
  `GuestStars` varchar(255) DEFAULT NULL,
+
    `GuestStars` varchar(255) DEFAULT NULL,
  `IMDB_ID` varchar(16) DEFAULT NULL,
+
    `IMDB_ID` varchar(16) DEFAULT NULL,
  `Language` varchar(5) DEFAULT NULL,
+
    `Language` varchar(5) DEFAULT NULL,
  `ProductionCode` varchar(16) DEFAULT NULL,
+
    `ProductionCode` varchar(16) DEFAULT NULL,
  `Rating` float(3,1) DEFAULT NULL,
+
    `Rating` float(3,1) DEFAULT NULL,
  `EpisodeName` varchar(255) DEFAULT NULL,
+
    `EpisodeName` varchar(255) DEFAULT NULL,
  `EpisodeNumber` int(5) DEFAULT NULL,
+
    `EpisodeNumber` int(5) DEFAULT NULL,
  `SeasonNumber` int(5) DEFAULT NULL,
+
    `SeasonNumber` int(5) DEFAULT NULL,
  `Writer` varchar(64) DEFAULT NULL,
+
    `Writer` varchar(64) DEFAULT NULL,
  `Filename` varchar(255) DEFAULT NULL,
+
    `Filename` varchar(255) DEFAULT NULL,
  `lastupdated` int(11) DEFAULT NULL,
+
    `lastupdated` int(11) DEFAULT NULL,
  `seasonid` int(11) DEFAULT NULL,
+
    `seasonid` int(11) DEFAULT NULL,
  `seriesid` int(11) DEFAULT NULL,
+
    `seriesid` int(11) DEFAULT NULL,
  `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 171: Line 171:
 
     `id` int(11) NOT NULL,
 
     `id` int(11) NOT NULL,
 
     `type` VARCHAR(16) DEFAULT NULL,
 
     `type` VARCHAR(16) DEFAULT NULL,
`url` VARCHAR(255) NOT NULL,
+
    `url` VARCHAR(255) NOT NULL,
 
     `image` BLOB DEFAULT NULL
 
     `image` BLOB DEFAULT NULL
 
);
 
);
Line 182: Line 182:
 
     `id` int(11) NOT NULL,
 
     `id` int(11) NOT NULL,
 
     `type` VARCHAR(16) NOT NULL,
 
     `type` VARCHAR(16) NOT NULL,
`size` VARCHAR(32) NOT NULL,
+
    `size` VARCHAR(32) NOT NULL,
`width` INT NOT NULL DEFAULT 0,
+
    `width` INT NOT NULL DEFAULT 0,
`height` INT NOT NULL DEFAULT 0,
+
    `height` INT NOT NULL DEFAULT 0,
 
     `url` VARCHAR(255) NOT NULL
 
     `url` VARCHAR(255) NOT NULL
 
);
 
);

Latest revision as of 06:42, 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;