WDLXTV ML SQLITE - Revision history

Recliq at 13:42, 6 January 2012

2012-01-06T13:42:55Z

Recliq: Created page with '

 BEGIN TRANSACTION;  DROP TABLE IF EXISTS video; CREATE TABLE `video`(     `hash` VARCHAR(16) primary key,     `title` VARCHAR(255),     `file` VARCHAR(255),     `path` VARC…'

2012-01-06T13:35:59Z

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…'

New page

<pre>
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;
</pre>

@@ Line 75: / Line 75: @@
 	`id` INTEGER NOT NULL primary key autoincrement,
      `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,
      `episodereleasedate` VARCHAR(12),
@@ Line 92: / Line 92: @@
 DROP TABLE IF EXISTS `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);
@@ Line 120: / Line 120: @@
 DROP TABLE IF EXISTS `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);
@@ Line 171: / Line 171: @@
      `id` int(11) NOT NULL,
      `type` VARCHAR(16) DEFAULT NULL,
-	`url` VARCHAR(255) NOT NULL,
+    `url` VARCHAR(255) NOT NULL,
      `image` BLOB DEFAULT NULL
 );
@@ Line 182: / Line 182: @@
      `id` int(11) 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
 );