lebyzero.tv.tv-lib.1.27.source-code.schema.sql Maven / Gradle / Ivy

Go to download
DROP VIEW IF EXISTS VPROGRAMS;
DROP VIEW IF EXISTS VPROGRAMS;
DROP VIEW IF EXISTS VPROGRAMS2;
DROP VIEW IF EXISTS VRECORDINGS;
DROP VIEW IF EXISTS VPROGRAMSUMMARIES;
DROP TABLE IF EXISTS CHANNELS;
DROP TABLE IF EXISTS PROGRAMS;
DROP TABLE IF EXISTS KEYWORDS;
DROP TABLE IF EXISTS SEARCHQUERIES;
DROP TABLE IF EXISTS RECSTATUSES;
DROP TABLE IF EXISTS RECORDINGS;

CREATE TABLE CHANNELS (
	id INT,
	name VARCHAR(50)
);

CREATE TABLE PROGRAMS (
	id INT GENERATED BY DEFAULT AS IDENTITY,
	title VARCHAR(100),
	searchTitle VARCHAR(100),
	subtitle VARCHAR(4096),
	description VARCHAR(4096),
	startTime TIMESTAMP,
	duration INT,
	channelid INT,
	episodeNumber INT,
	seasonNumber INT,
	repeat BOOLEAN,
	newSeries BOOLEAN,
	movie BOOLEAN,
	year VARCHAR(4),
	imdbId VARCHAR(10),
	imdbRating DECIMAL,
	imdbSource VARCHAR(15)
);

CREATE TABLE KEYWORDS (
    programid INT,
    keyword VARCHAR(30)
);

CREATE TABLE SEARCHQUERIES (
    id INT GENERATED BY DEFAULT AS IDENTITY,
    episodeNumber varchar(5),
    seasonNumber varchar(5),
    keywords VARCHAR(4096),
    titleSearch varchar(4096),
    isMovie BOOLEAN
);

CREATE TABLE RECORDINGS (
    id INT GENERATED BY DEFAULT AS IDENTITY,
    title VARCHAR(100),
	channelid INT,
    episodeNumber varchar(5),
    seasonNumber varchar(5),
    startTime TIMESTAMP,
    endTime TIMESTAMP,
    recStatus VARCHAR(5)
);

CREATE TABLE RECSTATUSES (
       id INT GENERATED BY DEFAULT AS IDENTITY,
       code VARCHAR(5),
       name VARCHAR(10)
);

CREATE VIEW VRECORDINGS AS
SELECT recordings.id,
       recordings.title,
       recordings.channelid,
       case when channels.name is null then 'UNKNOWN' else channels.name end as channelName,
       recordings.episodeNumber,
       recordings.seasonNumber,
       recordings.startTime,
       recordings.endTime,
       recordings.recStatus,
       case when recStatuses.name is null then 'UNKNOWN' else recStatuses.name end as recStatusName
  FROM recordings
  left outer join channels on recordings.channelid = channels.id
  left outer join recStatuses on recordings.recStatus = recStatuses.code;

CREATE VIEW VPROGRAMS AS
SELECT
       programs.id,
       programs.title,
       programs.searchTitle,
       programs.subtitle,
       programs.description,
       programs.startTime,
       programs.duration,
       programs.channelid,
       programs.episodeNumber,
       programs.seasonNumber,
       programs.repeat,
       programs.newSeries,
       programs.movie,
       programs.year,
       programs.imdbId,
       programs.imdbRating,
       programs.imdbSource,
       case when recordings1.title is null then 0 else 1 end as "recordingsTitleAndChannelMatch",
       case when recordings2.title is null then 0 else 1 end as "recordingsTitleMatch",
       case when recordings3.title is null then 0 else 1 end as "recordingsTimeMatch"
  from PROGRAMS left outer join (select distinct title, channelId from RECORDINGS) recordings1
    on PROGRAMS.searchtitle = RECORDINGS1.title
   and PROGRAMS.channelid = RECORDINGS1.channelid
  left outer join (select distinct title from RECORDINGS) recordings2 on PROGRAMS.searchtitle = RECORDINGS2.title
  left outer join (select distinct title, channelId, startTime from RECORDINGS) recordings3
    on PROGRAMS.searchtitle = RECORDINGS3.title
   and PROGRAMS.channelid = RECORDINGS3.channelid
   and PROGRAMS.startTime = RECORDINGS3.startTime
  join channels on PROGRAMS.channelid = channels.id
 order by title, channels.name, seasonNumber, episodeNumber, startTime;

CREATE VIEW VPROGRAMSUMMARIES AS
select p.id,
       p.title,
       p.searchTitle,
       p.subtitle,
       p.description,
       p.startTime,
       p.duration,
       p.channelid,
       p.episodeNumber,
       p.seasonNumber,
       p.repeat,
       p.newSeries,
       p.movie,
       p.year,
       p.imdbId,
       p.imdbRating,
       p.imdbSource,
       s."recordingsTitleAndChannelMatch",
       s."recordingsTitleMatch",
       s."recordingsTimeMatch"
  from vprograms p,
(select p.title, min(p.startTime) as "startTime", p.channelid, max("recordingsTitleAndChannelMatch") as "recordingsTitleAndChannelMatch", max("recordingsTitleMatch") as "recordingsTitleMatch", max("recordingsTimeMatch") as "recordingsTimeMatch"
   from searchqueries s, vprograms p
  where ((s.titleSearch like '"%"' and p.searchTitle = right(left(upper(s.titleSearch), length(s.titleSearch)-1), length(s.titleSearch)-2))
     or (s.titleSearch not like '"%"' and p.searchTitle like '%' || upper(s.titleSearch) || '%'))
    and ((s.seasonNumber is not null and p.seasonNumber >= s.seasonNumber) or (s.seasonNumber is null))
    and ((s.episodeNumber is not null and p.episodeNumber >= s.episodeNumber) or (s.episodeNumber is null))
    and p.movie = s.isMovie
  group by title, channelid) s
  where p.title = s.title
    and p.startTime = s."startTime"
    and p.channelId = s.channelId
 order by p.startTime, p.title;

CREATE INDEX CHANNELS_ID ON CHANNELS (id);
CREATE INDEX CHANNELS_NAME ON CHANNELS (name);
CREATE INDEX PROGRAMS_TITLE ON PROGRAMS (title);
CREATE INDEX PROGRAMS_SEARCHTITLE ON PROGRAMS (searchTitle);
CREATE INDEX PROGRAMS_CHANNELID ON PROGRAMS (channelid);
CREATE INDEX PROGRAMS_TITLE_CHANNELID ON PROGRAMS (title, channelid);
CREATE INDEX PROGRAMS_TITLE_CHANNELID_STARTTIME ON PROGRAMS (title, channelid, starttime);
CREATE INDEX PROGRAMS_ORDERBYFIELDS ON PROGRAMS (title, channelid, seasonNumber, episodeNumber, starttime);
CREATE INDEX RECORDINGS_TITLE ON RECORDINGS (title);
CREATE INDEX RECORDINGS_TITLE_CHANNELID ON RECORDINGS (title, channelid);
CREATE INDEX RECORDINGS_TITLE_CHANNELID_STARTTIME ON RECORDINGS (title, channelid, starttime);


insert into RECSTATUSES (code, name) values ('0', 'DOWNLOADED');
insert into RECSTATUSES (code, name) values ('1', 'SCHEDULED');
insert into RECSTATUSES (code, name) values ('3', 'NOTSURE');
insert into RECSTATUSES (code, name) values ('5', 'RECORDED');