summaryrefslogtreecommitdiff
path: root/configs
diff options
context:
space:
mode:
authorhorchi <vdr@jwendel.de>2017-03-05 16:39:28 +0100
committerhorchi <vdr@jwendel.de>2017-03-05 16:39:28 +0100
commite2a48d8701f91b8e24fbe9e99e91eb72a87bb749 (patch)
tree726f70554b4ca985a09ef6e30a7fdc8df089993c /configs
downloadvdr-epg-daemon-e2a48d8701f91b8e24fbe9e99e91eb72a87bb749.tar.gz
vdr-epg-daemon-e2a48d8701f91b8e24fbe9e99e91eb72a87bb749.tar.bz2
git init1.1.103
Diffstat (limited to 'configs')
-rw-r--r--configs/channelmap.conf49
-rw-r--r--configs/epg.dat981
-rw-r--r--configs/epgd.conf49
-rw-r--r--configs/eventsview-3po.sql75
-rw-r--r--configs/eventsview-ck.sql67
-rw-r--r--configs/eventsview-horchi.sql67
-rw-r--r--configs/eventsview-uti.sql68
-rw-r--r--configs/eventsview.sql66
-rw-r--r--configs/eventsviewplain-3po.sql99
-rw-r--r--configs/eventsviewplain.sql78
-rw-r--r--configs/getcrosslvr.sql34
-rw-r--r--configs/getlvrmin.sql51
-rw-r--r--configs/getupdflg.sql33
-rw-r--r--configs/mergeepg.sql401
-rw-r--r--configs/recording.py155
-rw-r--r--configs/reverseepg.sql42
-rw-r--r--configs/thetvdbview.sql33
17 files changed, 2348 insertions, 0 deletions
diff --git a/configs/channelmap.conf b/configs/channelmap.conf
new file mode 100644
index 0000000..beed1fa
--- /dev/null
+++ b/configs/channelmap.conf
@@ -0,0 +1,49 @@
+//
+// ChannelMap for EPG Daemon
+// --------------------------
+//
+// Format:
+// <source>:<extid>[:<merge>[:<vps>]] = <cid1>[,<cid2>[,<cid3>]]
+//
+//
+// <source> 'vdr' or the source name provided by a plugin
+// <extid> 000 for vdr or the channle id used by the external EPG provider
+// <merge> Merge DVB and externel EPG for this channel
+// { 0, 1, 2 } (default 1, the default for source 'vdr' is 0)
+// <vps> { y, Y, n, N, 0, 1 } (default 0)
+// <cid> VDR ChannelID (Src-NID-TID-SID)
+
+//
+// channels not listed here will not touched by the plugin
+// if 'blacklist' in plugin options set to 'yes' the plugin will block all
+// event data of the DVB stream since the channel is not listed here
+
+//
+// get this channels from DVB stream
+
+vdr:000:0:0 = S19.2E-1-1066-28656 // VH1
+vdr:000:0:0 = S19.2E-133-12-105 // Sky Sport HD Extra
+vdr:000:0:0 = S19.2E-133-2-262 // Sky Bundesliga 1
+vdr:000:0:0 = S19.2E-133-3-272 // Spieldaten
+vdr:000:0:0 = S19.2E-133-3-282 // Sky Bundesliga 3
+vdr:000:0:0 = S19.2E-133-3-292 // Sky Bundesliga 4
+vdr:000:0:0 = S19.2E-133-3-302 // Sky Bundesliga 5
+vdr:000:0:0 = S19.2E-133-17-312 // FRA - AUE
+vdr:000:0:0 = S19.2E-133-17-322 // 2. Liga Konf.
+vdr:000:0:0 = S19.2E-133-17-332 // MSV - AACH
+vdr:000:0:0 = S19.2E-133-1-342 // Sky Bundesliga 9
+vdr:000:0:0 = S19.2E-133-1-352 // Sky Bundesliga 10
+vdr:000:0:0 = S19.2E-133-2-252 // Sky Bundesliga 11
+vdr:000:0:0 = S19.2E-133-2-253 // LIVE Wimbledon 3
+vdr:000:0:0 = S19.2E-133-17-333 // Sky Racer
+vdr:000:0:0 = S19.2E-133-17-323 // Sky Pitlane
+vdr:000:0:0 = S19.2E-133-17-313 // Sky Sport 6
+vdr:000:0:0 = S19.2E-0-111719-303 // Sky Sport 7
+vdr:000:0:0 = S19.2E-0-111719-293 // Sky Sport 8
+vdr:000:0:0 = S19.2E-0-111719-283 // Sky Sport 9
+vdr:000:0:0 = S19.2E-0-111797-263 // Sky Sport 10
+vdr:000:0:0 = S19.2E-0-111719-273 // Sky Sport 11
+vdr:000:0:0 = S19.2E-133-1-363 // Sky Sport 12
+vdr:000:0:0 = S19.2E-133-1-373 // Sky Sport 13
+vdr:000:0:0 = S19.2E-53-1097-2042 // Hustler TV
+vdr:000:0:0 = S19.2E-53-1097-2045 // Dorcel TV
diff --git a/configs/epg.dat b/configs/epg.dat
new file mode 100644
index 0000000..6e07ea3
--- /dev/null
+++ b/configs/epg.dat
@@ -0,0 +1,981 @@
+// --------------------------------------------------------------------------
+//
+// Table Dictionary for EPG Daemon and related Plugins
+//
+// --------------------------------------------------------------------------
+// See the README file for copyright information and how to reach the author
+// --------------------------------------------------------------------------
+
+// ----------------------------------------------------------------
+// Table Events
+// ----------------------------------------------------------------
+
+Table events
+{
+ EVENTID "" eventid UBigInt 0 Primary,
+ CHANNELID "" channelid Ascii 50 Primary,
+
+ MASTERID "" masterid UInt 0 Autoinc,
+ USEID "" useid UInt 0 Data,
+
+ SOURCE "" source Ascii 10 Meta,
+ FILEREF "" fileref Ascii 100 Meta,
+ INSSP "" inssp Int 10 Meta,
+ UPDSP "" updsp Int 10 Meta,
+ UPDFLG "" updflg Ascii 1 Meta,
+ DELFLG "" delflg Ascii 1 Meta,
+
+ TABLEID "" tableid Int 2 Data,
+ VERSION "" version Int 3 Data,
+ TITLE "" title Ascii 200 Data,
+ COMPTITLE "" comptitle Ascii 200 Data,
+ SHORTTEXT "" shorttext Ascii 300 Data,
+ COMPSHORTTEXT "" compshorttext Ascii 300 Data,
+ LONGDESCRIPTION "" longdescription MText 25000 Data,
+ COMPLONGDESCRIPTION "" complongdescription MText 25000 Data filter epgd|httpd|epg2vdr,
+ STARTTIME "" starttime Int 10 Data,
+ DURATION "" duration Int 5 Data,
+ PARENTALRATING "" parentalrating Int 2 Data,
+ VPS "" vps Int 10 Data,
+ CONTENTS "Genre code like table 28 of ETSI EN 300 468" contents ASCII 100 Data filter epgd|httpd|epg2vdr,
+ SHORTDESCRIPTION "" shortdescription MText 3000 Data,
+ ACTOR "" actor MText 5000 Data,
+ AUDIO "" audio Ascii 50 Data,
+ CATEGORY "" category Ascii 50 Data,
+ COUNTRY "" country Ascii 50 Data,
+ DIRECTOR "" director Text 1000 Data,
+ COMMENTATOR "" commentator Ascii 200 Data,
+ FLAGS "" flags Ascii 100 Data,
+ GENRE "" genre Ascii 100 Data,
+ MUSIC "" music Ascii 250 Data,
+ PRODUCER "" producer Text 1000 Data,
+ SCREENPLAY "" screenplay Ascii 500 Data,
+ SHORTREVIEW "" shortreview Ascii 500 Data,
+ TIPP "" tipp Ascii 250 Data,
+ TOPIC "" topic Ascii 1000 Data,
+ YEAR "" year Ascii 10 Data,
+ RATING "" rating Ascii 250 Data,
+ NUMRATING "" numrating Int 2 Data filter epgd|httpd|epg2vdr,
+ TXTRATING "" txtrating Ascii 100 Data,
+ MOVIEID "" movieid Ascii 20 Data,
+ MODERATOR "" moderator Ascii 250 Data,
+ OTHER "" other Text 2000 Data,
+ GUEST "" guest Text 1000 Data,
+ CAMERA "" camera Text 1000 Data,
+ EXTEPNUM "" extepnum Int 4 Data,
+ IMAGECOUNT "" imagecount Int 2 Data,
+
+ EPISODECOMPNAME "" episodecompname Ascii 100 Data filter epgd|httpd|epg2vdr,
+ EPISODECOMPSHORTNAME "" episodecompshortname Ascii 100 Data filter epgd|httpd|epg2vdr,
+ EPISODECOMPPARTNAME "" episodecomppartname Ascii 200 Data filter epgd|httpd|epg2vdr,
+ EPISODELANG "" episodelang Ascii 10 Data filter epgd|httpd|epg2vdr,
+
+ SCRSERIESID "" scrseriesid Int 0 Data,
+ SCRSERIESEPISODE "" scrseriesepisode Int 0 Data,
+ SCRMOVIEID "" scrmovieid Int 0 Data,
+ SCRSP "" scrsp Int 0 Data,
+}
+
+// ----------------------------------------------------------------
+// Indices for Events
+// ----------------------------------------------------------------
+
+Index events
+{
+ comptitle "" COMPTITLE,
+ source "" SOURCE,
+ filerefsource "" FILEREF SOURCE,
+ channelid "" CHANNELID,
+ useid "" USEID,
+ useidchannelid "" USEID CHANNELID,
+ updflgupdsp "" UPDFLG UPDSP,
+ sourcechannelid "" SOURCE CHANNELID,
+ scrsp "" SCRSP,
+ sourceupdsp "" SOURCE UPDSP,
+ scrseriesid "" SCRSERIESID,
+ channelidstarttime "" CHANNELID STARTTIME,
+}
+
+// ----------------------------------------------------------------
+// Table Components
+// ----------------------------------------------------------------
+
+Table components
+{
+ EVENTID "" eventid UBigInt 0 Primary,
+ CHANNELID "" channelid Ascii 50 Primary,
+ STREAM "" stream Int 3 Primary,
+ TYPE "" type Int 3 Primary,
+ LANG "" lang Ascii 8 Primary,
+ DESCRIPTION "" description Ascii 100 Primary,
+ INSSP "" inssp Int 0 Meta,
+ UPDSP "" updsp Int 0 Meta,
+}
+
+// ----------------------------------------------------------------
+// Table FileRef
+// ----------------------------------------------------------------
+
+Table fileref
+{
+ NAME "" name Ascii 100 Primary,
+ SOURCE "" source Ascii 10 Primary,
+ INSSP "" inssp Int 0 Meta,
+ UPDSP "" updsp Int 0 Meta,
+ EXTERNALID "" extid Ascii 10 Data,
+ FILEREF "" fileref Ascii 100 Data,
+ TAG "" tag Ascii 100 Data,
+}
+
+// ----------------------------------------------------------------
+// Indices for FileRefs
+// ----------------------------------------------------------------
+
+Index filerefs
+{
+ SourceFileref "" SOURCE FILEREF,
+ Fileref "" FILEREF,
+}
+
+// ----------------------------------------------------------------
+// Table ImageRefs
+// ----------------------------------------------------------------
+
+Table imagerefs
+{
+ EVENTID "" eventid UBigInt 0 Primary,
+ LFN "" lfn Int 0 Primary,
+ INSSP "" inssp Int 0 Meta,
+ UPDSP "" updsp Int 0 Meta,
+ SOURCE "" source Ascii 10 Meta,
+ FILEREF "" fileref Ascii 100 Data,
+ IMGNAME "" imagename Ascii 100 Data,
+}
+
+// ----------------------------------------------------------------
+// Indices for ImageRefs
+// ----------------------------------------------------------------
+
+Index imagerefs
+{
+ lfn "" LFN,
+ name "" IMGNAME,
+}
+
+// ----------------------------------------------------------------
+// Table Images
+// ----------------------------------------------------------------
+
+Table images
+{
+ IMGNAME "" imagename Ascii 100 Primary,
+ INSSP "" inssp Int 0 Meta,
+ UPDSP "" updsp Int 0 Meta,
+ IMAGE "" image Mlob 512000 Data,
+}
+
+// ----------------------------------------------------------------
+// Table Episodes
+// ----------------------------------------------------------------
+
+Table episodes
+{
+ COMPNAME "" compname Ascii 100 Primary,
+ COMPPARTNAME "" comppartname Ascii 200 Primary,
+ LANG "" lang Ascii 10 Primary,
+
+ INSSP "" inssp Int 0 Meta,
+ UPDSP "" updsp Int 0 Meta,
+
+ LINK "" link Int 0 Data,
+ SHORTNAME "" shortname Ascii 100 Data,
+ COMPSHORTNAME "" compshortname Ascii 100 Data,
+ EPISODENAME "" episodename Ascii 100 Data,
+ PARTNAME "" partname Ascii 300 Data,
+ SEASON "" season Int 0 Data,
+ PART "" part Int 0 Data,
+ PARTS "" parts Int 0 Data,
+ NUMBER "" number Int 0 Data,
+ EXTRACOL1 "" extracol1 Ascii 250 Data,
+ EXTRACOL2 "" extracol2 Ascii 250 Data,
+ EXTRACOL3 "" extracol3 Ascii 250 Data,
+ COMMENT "" comment Ascii 250 Data,
+}
+
+// ----------------------------------------------------------------
+// Indices for Episodes
+// ----------------------------------------------------------------
+
+Index episodes
+{
+ updsp "" UPDSP,
+}
+
+// ----------------------------------------------------------------
+// Table ChannelMap
+// ----------------------------------------------------------------
+
+Table channelmap
+{
+ EXTERNALID "" extid Ascii 10 Primary,
+ CHANNELID "" channelid Ascii 50 Primary,
+ SOURCE "" source Ascii 20 Primary,
+
+ ORDER "" ord Int 0 Data,
+ VISIBLE "" visible Int 0 Data,
+ CHANNELNAME "" channelname Ascii 100 Data,
+ VPS "" vps Int 0 Data,
+ FORMAT "" format Ascii 50 Data,
+ UNKNOWNATVDR "" unknownatvdr UInt 1 Data,
+ MERGE "" merge Int 0 Data,
+ MERGESP "" mergesp Int 0 Data,
+ INSSP "" inssp Int 0 Meta,
+ UPDSP "" updsp Int 0 Meta,
+ UPDFLG "" updflg Ascii 1 Meta,
+}
+
+// ----------------------------------------------------------------
+// Indices for ChannelMap
+// ----------------------------------------------------------------
+
+Index channelmap
+{
+ sourceExtid "" SOURCE EXTERNALID,
+ source "" SOURCE,
+ updflg "" UPDFLG,
+ sourcechannelid "" SOURCE CHANNELID,
+ mergesp "" MERGESP,
+ channelid "" CHANNELID,
+}
+
+// ----------------------------------------------------------------
+// Table Vdrs
+// ----------------------------------------------------------------
+
+Table vdrs
+{
+ UUID "" uuid Ascii 40 Primary,
+
+ INSSP "" inssp Int 0 Meta,
+ UPDSP "" updsp Int 0 Meta,
+
+ NAME "" name Ascii 100 Data,
+ VERSION "" version Ascii 100 Data,
+ DBAPI "" dbapi UInt 0 Data,
+ LASTUPDATE "" lastupd Int 0 Data,
+ NEXTUPDATE "" nextupd Int 0 Data,
+ LASTMERGE "" lastmerge Int 0 Data,
+ STATE "" state Ascii 20 Data,
+ MASTER "" master Ascii 1 Data,
+ IP "" ip Ascii 20 Data,
+ MAC "" mac Ascii 18 Data,
+ PID "" pid UInt 0 Data filter epgd|httpd|epg2vdr,
+ SVDRP "" svdrp UInt 0 Data filter epgd|httpd|epg2vdr,
+ TUNERCOUNT "" tunercount UInt 0 Data filter epgd|httpd|epg2vdr,
+
+ SHAREINWEB "" shareinweb UInt 1 Data,
+ USECOMMONRECFOLDER "" usecommonrecfolder UInt 1 Data,
+
+ VIDEODIR "" videodir Ascii 300 Data,
+ VIDEOTOTAL "" videototal UInt 0 Data,
+ VIDEOFREE "" videofree UInt 0 Data,
+}
+
+// ----------------------------------------------------------------
+// Indices for Vdrs
+// ----------------------------------------------------------------
+
+Index vdrs
+{
+ state "" STATE,
+}
+
+// ----------------------------------------------------------------
+// Table Users
+// ----------------------------------------------------------------
+
+Table users
+{
+ USER "" user Ascii 40 Primary,
+
+ INSSP "" inssp Int 0 Meta,
+ UPDSP "" updsp Int 0 Meta,
+
+ PASSWD "" passwd Ascii 100 Data,
+ ACTIVE "" active Int 1 Data,
+ RIGHTS "" rights UInt 0 Data,
+}
+
+// ----------------------------------------------------------------
+// Table Parameters
+// ----------------------------------------------------------------
+
+Table parameters
+{
+ OWNER "" owner Ascii 40 Primary,
+ NAME "" name Ascii 40 Primary,
+ INSSP "" inssp Int 0 Meta,
+ UPDSP "" updsp Int 0 Meta,
+ VALUE "" value Ascii 500 Data,
+}
+
+// ----------------------------------------------------------------
+// Table Analyse
+// ----------------------------------------------------------------
+
+Table analyse
+{
+ CHANNELID "" channelid Ascii 50 Primary,
+ VDRMASTERID "" vdr_masterid UInt 0 Data,
+ VDREVENTID "" vdr_eventid UBigInt 0 Primary,
+ VDRSTARTTIME "" vdr_starttime Int 10 Data,
+ VDRDURATION "" vdr_duration Int 5 Data,
+ VDRTITLE "" vdr_title Ascii 200 Data,
+ VDRSHORTTEXT "" vdr_shorttext Ascii 300 Data,
+ EXTMASTERID "" ext_masterid UInt 0 Data,
+ EXTEVENTID "" ext_eventid UBigInt 0 Data,
+ EXTSTARTTIME "" ext_starttime Int 10 Data,
+ EXTDURATION "" ext_duration Int 5 Data,
+ EXTTITLE "" ext_title Ascii 200 Data,
+ EXTSHORTTEXT "" ext_shorttext Ascii 300 Data,
+ EXTEPISODE "" ext_episode Ascii 1 Data,
+ EXTMERGE "" ext_merge Int 11 Data,
+ EXIIMAGES "" ext_images Ascii 1 Data,
+ LVMIN "" lvmin Int 3 Data,
+ RANK "" rank Int 5 Data,
+}
+
+// ----------------------------------------------------------------
+// Indices for Analyse
+// ----------------------------------------------------------------
+
+Index analyse
+{
+ vdr_masterid "" VDRMASTERID,
+}
+
+// ----------------------------------------------------------------
+// Table Snapshot
+// ----------------------------------------------------------------
+
+Table snapshot
+{
+ CHANNELID "" channelid Ascii 50 Data,
+ SOURCE "" source Ascii 10 Data,
+ VDRMASTERID "" masterid UInt 0 Data,
+ EVENTID "" eventid UBigInt 0 Data,
+ USEID "" useid UInt 0 Data,
+ STARTTIME "" starttime Int 10 Data,
+ DURATION "" duration Int 5 Data,
+ TITLE "" title Ascii 200 Data,
+ COMPTITLE "" comptitle Ascii 200 Data,
+ SHORTTEXT "" shorttext Ascii 300 Data,
+ COMPSHORTTEXT "" compshorttext Ascii 300 Data,
+ UPDSP "" updsp Int 10 Data,
+ EPISODE "" episode Ascii 1 Data,
+ MERGE "" merge Int 0 Data,
+ IMAGES "" images Ascii 1 Data,
+}
+
+// ----------------------------------------------------------------
+// Indices for Snapshot
+// ----------------------------------------------------------------
+
+Index snapshot
+{
+ channelid "" CHANNELID,
+ starttimeSource "" STARTTIME SOURCE,
+}
+
+// ----------------------------------------------------------------
+// Table UseEvents
+// ----------------------------------------------------------------
+
+Table useevents
+{
+ CNTSOURCE "" cnt_source Ascii 10 Primary,
+ CHANNELID "" cnt_channelid Ascii 50 Primary,
+ CNTEVENTID "" cnt_eventid UBigInt 0 Primary|Meta,
+
+ MASTERID "" cnt_masterid UInt 0 Data|Meta,
+ USEID "" cnt_useid UInt 0 Data,
+
+ SUBSOURCE "" sub_source Ascii 10 Data|Meta,
+ SUBEVENTID "" sub_eventid UBigInt 0 Data|Meta,
+ UPDSP "" all_updsp Int 0 Data,
+ UPDFLG "" cnt_updflg Ascii 1 Data|Meta,
+ DELFLG "" cnt_delflg Ascii 1 Data,
+ FILEREF "" cnt_fileref Ascii 100 Data,
+ TABLEID "" cnt_tableid Int 2 Data,
+ VERSION "" cnt_version Int 3 Data,
+ TITLE "" sub_title Ascii 200 Data,
+ SHORTTEXT "" sub_shorttext Ascii 300 Data,
+ COMPTITLE "" sub_comptitle Ascii 200 Data,
+ COMPSHORTTEXT "" sub_compshorttext Ascii 300 Data,
+ GENRE "" sub_genre Ascii 100 Data,
+ COUNTRY "" sub_country Ascii 50 Data,
+ YEAR "" sub_year Ascii 10 Data,
+ STARTTIME "" cnt_starttime Int 10 Data,
+ DURATION "" cnt_duration Int 5 Data,
+ PARENTALRATING "" cnt_parentalrating Int 2 Data,
+ VPS "" cnt_vps Int 10 Data,
+ CONTENTS "Genre code like table 28 of ETSI EN 300 468" cnt_contents ASCII 100 Data,
+ CATEGORY "" sub_category Ascii 50 Data,
+ SHORTDESCRIPTION "" sub_shortdescription MText 3000 Data,
+ SHORTREVIEW "" sub_shortreview Ascii 500 Data,
+ TIPP "" sub_tipp Ascii 250 Data,
+ RATING "" sub_rating Ascii 250 Data,
+ NUMRATING "" sub_numrating Int 2 Data,
+ TXTRATING "" sub_txtrating Ascii 100 Data,
+ TOPIC "" sub_topic Ascii 1000 Data,
+ LONGDESCRIPTION "" sub_longdescription MText 25000 Data,
+ COMPLONGDESCRIPTION "" sub_complongdescription MText 25000 Data,
+ CNTLONGDESCRIPTION "" cnt_longdescription MText 25000 Data,
+ MODERATOR "" sub_moderator Ascii 250 Data,
+ GUEST "" sub_guest Text 1000 Data,
+ ACTOR "" sub_actor MText 5000 Data,
+ PRODUCER "" sub_producer Text 1000 Data,
+ OTHER "" sub_other Text 2000 Data,
+ DIRECTOR "" sub_director Text 1000 Data,
+ COMMENTATOR "" sub_commentator Ascii 200 Data,
+ SCREENPLAY "" sub_screenplay Ascii 500 Data,
+ CAMERA "" sub_camera Text 1000 Data,
+ MUSIC "" sub_music Ascii 250 Data,
+ AUDIO "" sub_audio Ascii 50 Data,
+ FLAGS "" sub_flags Ascii 100 Data,
+ IMAGECOUNT "" sub_imagecount Int 2 Data,
+
+ SCRSERIESID "" sub_scrseriesid Int 0 Data,
+ SCRSERIESEPISODE "" sub_scrseriesepisode Int 0 Data,
+ SCRMOVIEID "" sub_scrmovieid Int 0 Data,
+ SCRSP "" sub_scrsp Int 0 Data|Meta,
+
+ EPISODECOMPNAME "" sub_episodecompname Ascii 100 Data,
+ EPISODECOMPSHORTNAME "" sub_episodecompshortname Ascii 100 Data,
+ EPISODECOMPPARTNAME "" sub_episodecomppartname Ascii 200 Data,
+
+ EPISODENAME "" epi_episodename Ascii 100 Data,
+ EPISODESHORTNAME "" epi_shortname Ascii 100 Data,
+ EPISODEPARTNAME "" epi_partname Ascii 300 Data,
+ EPISODELANG "" epi_lang Ascii 10 Data,
+ EPISODEEXTRACOL1 "" epi_extracol1 Ascii 250 Data,
+ EPISODEEXTRACOL2 "" epi_extracol2 Ascii 250 Data,
+ EPISODEEXTRACOL3 "" epi_extracol3 Ascii 250 Data,
+ EPISODESEASON "" epi_season Int 0 Data,
+ EPISODEPART "" epi_part Int 0 Data,
+ EPISODEPARTS "" epi_parts Int 0 Data,
+ EPISODENUMBER "" epi_number Int 0 Data,
+}
+
+// ----------------------------------------------------------------
+// Indices for UseEvents
+// ----------------------------------------------------------------
+
+Index useevents
+{
+ channelidstarttime "" CHANNELID STARTTIME,
+ useid "" USEID,
+ channelidupdflgupdsp "" CHANNELID UPDFLG UPDSP,
+ channelid "" CHANNELID,
+ updflgstarttimeduration "" UPDSP STARTTIME DURATION,
+}
+
+// ----------------------------------------------------------------
+// Table Recording List
+// ----------------------------------------------------------------
+
+Table recordinglist
+{
+ MD5PATH "" md5path Ascii 40 Primary,
+ STARTTIME "" starttime UInt 0 Primary,
+ OWNER "uuid of vdr" owner Ascii 40 Primary,
+
+ INSSP "" inssp Int 10 Meta,
+ UPDSP "" updsp Int 10 Meta,
+ LASTIFOUPD "" lastifoupd Int 10 Meta,
+
+ VDRUUID "" vdruuid Ascii 40 Data,
+ PATH "" path Ascii 1000 Data,
+ NAME "" name Ascii 1000 Data,
+ FOLDER "" folder Ascii 1000 Data,
+ TITLE "" title Ascii 200 Data,
+ SHORTTEXT "" shorttext Ascii 300 Data,
+ LONGDESCRIPTION "" longdescription MText 25000 Data,
+ DURATION "" duration UInt 0 Data,
+ FSK "" fsk UInt 1 Data,
+
+ EVENTID "useid" eventid UInt 0 Data,
+ CHANNELID "" channelid Ascii 50 Data,
+ CHANNELNAME "just a copy" channelname Ascii 100 Data,
+
+ STATE "" state Ascii 1 Data,
+ INUSE "" inuse UInt 1 Data,
+ JOB "" job Ascii 1 Data,
+
+ // enriched by 'external' data of events
+
+ ACTOR "" actor MText 5000 Data,
+ AUDIO "" audio Ascii 50 Data,
+ CATEGORY "" category Ascii 50 Data,
+ COUNTRY "" country Ascii 50 Data,
+ DIRECTOR "" director Text 1000 Data,
+ FLAGS "" flags Ascii 100 Data,
+ GENRE "" genre Ascii 100 Data,
+ MUSIC "" music Ascii 250 Data,
+ PRODUCER "" producer Text 1000 Data,
+ SCREENPLAY "" screenplay Ascii 500 Data,
+ SHORTREVIEW "" shortreview Ascii 500 Data,
+ TIPP "" tipp Ascii 250 Data,
+ TOPIC "" topic Ascii 1000 Data,
+ YEAR "" year Ascii 10 Data,
+ RATING "" rating Ascii 250 Data,
+ NUMRATING "" numrating Int 2 Data,
+ TXTRATING "" txtrating Ascii 100 Data,
+ MODERATOR "" moderator Ascii 250 Data,
+ OTHER "" other Text 2000 Data,
+ GUEST "" guest Text 1000 Data,
+ CAMERA "" camera Text 1000 Data,
+
+ // episode reference
+
+ EPISODECOMPNAME "" episodecompname Ascii 100 Data filter epgd|httpd|epg2vdr,
+ EPISODECOMPSHORTNAME "" episodecompshortname Ascii 100 Data filter epgd|httpd|epg2vdr,
+ EPISODECOMPPARTNAME "" episodecomppartname Ascii 200 Data filter epgd|httpd|epg2vdr,
+ EPISODELANG "" episodelang Ascii 10 Data filter epgd|httpd|epg2vdr,
+
+ // scraper fields
+ // IDs found while scraping (reference to scraper tables), managed by epgd
+
+ SCRSERIESID "" scrseriesid UInt 0 Data,
+ SCRSERIESEPISODE "" scrseriesepisode UInt 0 Data,
+ SCRMOVIEID "" scrmovieid UInt 0 Data,
+
+ // this fields are written by the scraper plugin,
+ // the id fields hold the user hint for scraping
+
+ SCRINFOMOVIEID "" scrinfomovieid UInt 0 Data,
+ SCRINFOSERIESID "" scrinfoseriesid UInt 0 Data,
+ SCRINFOEPISODEID "" scrinfoepisodeid UInt 0 Data,
+
+ SCRNEW "" scrnew UInt 0 Data,
+ SCRSP "" scrsp Int 0 Data,
+}
+
+// ----------------------------------------------------------------
+// Table RecordingDirs
+// ----------------------------------------------------------------
+
+Table recordingdirs
+{
+ VDRUUID "" vdruuid Ascii 40 Primary,
+ DIRECTORY "" directory Ascii 255 Primary,
+
+ INSSP "" inssp Int 0 Meta,
+ UPDSP "" updsp Int 0 Meta,
+}
+
+// ----------------------------------------------------------------
+// Table Timers
+// ----------------------------------------------------------------
+
+Table timers
+{
+ ID "" id UInt 0 Primary|Autoinc,
+ VDRUUID "" vdruuid Ascii 40 Primary,
+
+ INSSP "" inssp Int 0 Meta,
+ UPDSP "" updsp Int 0 Meta,
+
+ EVENTID "useid" eventid UInt 0 Data,
+ CHANNELID "" channelid Ascii 50 Data,
+ _STARTTIME "pre filled start timer for trigger" _starttime Int 10 Data,
+
+ SOURCE "like osd, webif, epgd" source Ascii 40 Data,
+ TYPE "'R'ecord, 'V'iew (umschalt)" type Ascii 1 Data,
+ STATE "'D'eleted, 'R'unning, 'F'inished" state Ascii 1 Data default u,
+ INFO "error reason if state is failed" info Ascii 255 Data,
+ ACTION "" action Ascii 1 Data default a,
+ TCCMAILCNT "" tccmailcnt UInt 0 Data,
+ WRNCOUNT "" wrncount UInt 0 Data,
+ RETRYS "" retrys UInt 0 Data,
+
+ NAMINGMODE "" namingmode Int 0 Data,
+ TEMPLATE "" template Ascii 100 Data,
+ ACTIVE "" active UInt 0 Data,
+ DAY "" day Int 10 Data,
+ WEEKDAYS "" weekdays Int 10 Data,
+ STARTTIME "" starttime Int 10 Data,
+ ENDTIME "" endtime Int 10 Data,
+ FILE "" file Ascii 512 Data,
+ DIRECTORY "" directory Ascii 512 Data,
+
+ PRIORITY "" priority Int 0 Data,
+ LIFETIME "" lifetime Int 0 Data,
+ VPS "" vps Int 0 Data,
+ CHILDLOCK "" childlock Int 0 Data,
+ AUX "" aux Ascii 1000 Data,
+
+ AUTOTIMERNAME "Bezeichung des Suchtimers" autotimername Ascii 100 Data,
+ AUTOTIMERID "id of autotimer" autotimerid UInt 0 Data,
+ AUTOTIMERINSSP "" autotimerinssp Int 0 Data,
+
+ // just to update the done state by the plugin !!
+
+ DONEID "id of done entry" doneid UInt 0 Data,
+ EXPRESSION "" expression Ascii 200 Data,
+}
+
+// ----------------------------------------------------------------
+// Indices for Timers
+// ----------------------------------------------------------------
+
+Index timers
+{
+ eventidchannelidvdruuid "" EVENTID CHANNELID VDRUUID,
+ vdruuidstate "" VDRUUID STATE
+}
+
+// ----------------------------------------------------------------
+// Table
+// ----------------------------------------------------------------
+
+Table searchtimers
+{
+ ID "" id UInt 0 Primary|Autoinc,
+
+ INSSP "" inssp Int 0 Meta,
+ UPDSP "" updsp Int 0 Meta,
+
+ CHANNELIDS "comma separated list of channleids or empty" channelids Ascii 500 Data,
+ CHEXCLUDE "" chexclude UInt 0 Data,
+ CHFORMAT "HD,SD" chformat Ascii 50 Data,
+
+ NAME "Bezeichung des Suchtimers" name Ascii 100 Data,
+ EXPRESSION "" expression Ascii 200 Data,
+ EXPRESSION1 "" expression1 Ascii 200 Data,
+ SEARCHMODE "1 exact, 2 regexp, 3 like, 4 enthalten, .." searchmode UInt 0 Data,
+ SEARCHFIELDS "Bitmaske: 1 title, 2 shorttext, 4 - desc, .." searchfields UInt 0 Data,
+ SEARCHFIELDS1 "Bitmaske: 0 off, 1 title, 2 shorttext, 4 - desc, .." searchfields1 UInt 0 Data,
+
+ CASESENSITIV "0,1" casesensitiv UInt 0 Data,
+
+ REPEATFIELDS "Bitmaske: 1 title, 2 shorttext, 4 - desc, .." repeatfields UInt 0 Data,
+
+ // optional EPG Detail options
+
+ EPISODENAME "" episodename Ascii 100 Data,
+ SEASON "e.g. '1-7'" season Ascii 10 Data,
+ SEASONPART "e.g. '20-'" seasonpart Ascii 10 Data,
+ CATEGORY "e.g. 'Spielfilm','Serie'" category Ascii 150 Data,
+ GENRE "e.g. 'Krimi','Action'" genre Ascii 150 Data,
+ YEAR "e.g. '2010-2015'" year Ascii 10 Data,
+ TIPP "" tipp Ascii 250 Data,
+ NOEPGMATCH "" noepgmatch Int 0 Data,
+
+ // steering
+
+ TYPE "'R'ecord, 'V'iew, 'S'earch" type Ascii 1 Data,
+ STATE "D - Deleted" state Ascii 1 Data,
+ NAMINGMODE "" namingmode Int 0 Data,
+ TEMPLATE "" template Ascii 100 Data,
+
+ ACTIVE "0,1" active UInt 0 Data,
+ SOURCE "webif ,osd, ..." source Ascii 40 Data,
+ HITS "stastic counter, just for info" hits UInt 0 Data,
+ MODSP "last user modification time" modsp Int 0 Data,
+ LASTRUN "last execution of search timer" lastrun Int 0 Data,
+ VDRUUID "empty or uuid of vdr" vdruuid Ascii 40 Data,
+
+ // timer details
+
+ WEEKDAYS "bitmask like timers, 0 foy all" weekdays Int 0 Data,
+ NEXTDAYS "" nextdays UInt 0 Data,
+ STARTTIME "null for unlimited search" starttime Int 0 Data,
+ ENDTIME "null for unlimited search" endtime Int 0 Data,
+ DIRECTORY "" directory Ascii 512 Data,
+ PRIORITY "" priority Int 0 Data,
+ LIFETIME "" lifetime Int 0 Data,
+ VPS "" vps Int 0 Data,
+ CHILDLOCK "" childlock Int 0 Data,
+}
+
+// ----------------------------------------------------------------
+// Table TimersDone
+// ----------------------------------------------------------------
+
+Table timersdone
+{
+ ID "" id UInt 0 Primary|Autoinc,
+
+ INSSP "" inssp Int 0 Meta,
+ UPDSP "" updsp Int 0 Meta,
+
+ SOURCE "" source Ascii 40 Data,
+ STATE "Q requested, C created, R recorded, F Failed" state Ascii 1 Data,
+
+ TIMERID "not filled completely in all cases yet" timerid UInt 0 Data,
+ AUTOTIMERID "" autotimerid UInt 0 Data,
+ AUTOTIMERNAME "Bezeichung des Suchtimers" autotimername Ascii 100 Data,
+
+ // to compare
+
+ TITLE "" title Ascii 200 Data,
+ COMPTITLE "" comptitle Ascii 200 Data,
+ SHORTTEXT "" shorttext Ascii 300 Data,
+ COMPSHORTTEXT "" compshorttext Ascii 300 Data,
+ LONGDESCRIPTION "" longdescription MText 25000 Data,
+ COMPLONGDESCRIPTION "" complongdescription MText 25000 Data,
+
+ EPISODECOMPNAME "" episodecompname Ascii 100 Data filter epgd|httpd|epg2vdr,
+ EPISODECOMPSHORTNAME "" episodecompshortname Ascii 100 Data filter epgd|httpd|epg2vdr,
+ EPISODECOMPPARTNAME "" episodecomppartname Ascii 200 Data filter epgd|httpd|epg2vdr,
+ EPISODELANG "" episodelang Ascii 10 Data filter epgd|httpd|epg2vdr,
+ EPISODESEASON "" episodeseason Int 0 Data,
+ EPISODEPART "" episodepart Int 0 Data,
+
+ // just for info
+
+ CHANNELID "" channelid Ascii 50 Data,
+ CHANNELNAME "" channelname Ascii 100 Data;
+ EXPRESSION "expression of autotimer" expression Ascii 200 Data,
+ STARTTIME "" starttime Int 10 Data,
+ DURATION "" duration Int 5 Data,
+ AUX "" aux Ascii 512 Data,
+}
+
+// ----------------------------------------------------------------
+// Indices for TimersDone
+// ----------------------------------------------------------------
+
+Index TimersDone
+{
+ checkdoubles "" COMPTITLE, COMPSHORTTEXT
+}
+
+// ----------------------------------------------------------------
+// Table Messages / Notifications
+// ----------------------------------------------------------------
+
+Table messages
+{
+ ID "" id UInt 0 Primary|Autoinc,
+
+ INSSP "" inssp Int 0 Meta,
+ UPDSP "" updsp Int 0 Meta,
+
+ TYPE "Warning, Info, Error, Fatal" type Ascii 1 Data,
+ TITLE "" title Ascii 200 Data,
+ STATE "Read, New, Deleted" state Ascii 1 Data,
+ TEXT "" text MText 20000 Data,
+}
+
+// ----------------------------------------------------------------
+// SCRAPER stuff
+// ----------------------------------------------------------------
+// ----------------------------------------------------------------
+// Table Series
+// ----------------------------------------------------------------
+
+Table series
+{
+ SERIESID "" series_id UInt 0 Primary,
+ SERIESNAME "" series_name Ascii 200 Data,
+ SERIESLASTSCRAPED "" series_last_scraped UInt 0 Data,
+ SERIESLASTUPDATED "" series_last_updated UInt 0 Data,
+ SERIESOVERVIEW "" series_overview Text 10000 Data,
+ SERIESFIRSTAIRED "" series_firstaired Ascii 50 Data,
+ SERIESNETWORK "" series_network Ascii 100 Data,
+ SERIESIMDBID "" series_imdb_id Ascii 20 Data,
+ SERIESGENRE "" series_genre Ascii 100 Data,
+ SERIESRATING "" series_rating Float 31 Data,
+ SERIESSTATUS "" series_status Ascii 50 Data,
+}
+
+// ----------------------------------------------------------------
+// Indices for Series
+// ----------------------------------------------------------------
+
+Index series
+{
+ seriesname "" SERIESNAME,
+}
+
+// ----------------------------------------------------------------
+// Table SeriesEpisode
+// ----------------------------------------------------------------
+
+Table series_episode
+{
+ EPISODEID "" episode_id UInt 0 Primary,
+ EPISODENUMBER "" episode_number UInt 0 Data,
+ SEASONNUMBER "" season_number UInt 0 Data,
+ EPISODENAME "" episode_name Ascii 300 Data,
+ EPISODEOVERVIEW "" episode_overview Text 10000 Data,
+ EPISODEFIRSTAIRED "" episode_firstaired Ascii 20 Data,
+ EPISODEGUESTSTARS "" episode_gueststars Ascii 1000 Data,
+ EPISODERATING "" episode_rating Float 31 Data,
+ EPISODELASTUPDATED "" episode_last_updated UInt 0 Data,
+ SERIESID "" series_id UInt 0 Data,
+}
+
+// ----------------------------------------------------------------
+// Indices for SeriesEpisode
+// ----------------------------------------------------------------
+
+Index series_episode
+{
+ series_id "" SERIESID,
+}
+
+// ----------------------------------------------------------------
+// Table SeriesMedia
+// ----------------------------------------------------------------
+
+Table series_media
+{
+ SERIESID "" series_id UInt 0 Primary,
+ SEASONNUMBER "" season_number UInt 0 Primary,
+ EPISODEID "" episode_id UInt 0 Primary,
+ ACTORID "" actor_id UInt 0 Primary,
+ MEDIATYPE "" media_type UInt 0 Primary,
+
+ INSSP "" inssp Int 0 Meta filter epgd|httpd|epg2vdr,
+ UPDSP "" updsp Int 0 Meta filter epgd|httpd|epg2vdr,
+
+ MEDIAURL "" media_url Ascii 100 Data,
+ MEDIAWIDTH "" media_width UInt 0 Data,
+ MEDIAHEIGHT "" media_height UInt 0 Data,
+ MEDIARATING "" media_rating Float 31 Data,
+ MEDIACONTENT "" media_content Mlob 1000000 Data,
+}
+
+// ----------------------------------------------------------------
+// Indices for SeriesMedia
+// ----------------------------------------------------------------
+
+Index series_media
+{
+ series_id "" SERIESID,
+ season_number "" SEASONNUMBER,
+ episode_id "" EPISODEID,
+ actor_id "" ACTORID,
+}
+
+// ----------------------------------------------------------------
+// Table SeriesActor
+// ----------------------------------------------------------------
+
+Table series_actor
+{
+ ACTORID "" actor_id UInt 0 Primary,
+ ACTORNAME "" actor_name Ascii 100 Data,
+ ACTORROLE "" actor_role Ascii 500 Data,
+ SORTORDER "" actor_sortorder UInt 0 Data,
+}
+
+// ----------------------------------------------------------------
+// Indices for SeriesActor
+// ----------------------------------------------------------------
+
+Index series_actor
+{
+}
+
+// ----------------------------------------------------------------
+// Table Movie
+// ----------------------------------------------------------------
+
+Table movie
+{
+ MOVIEID "" movie_id UInt 0 Primary,
+ TITLE "" movie_title Ascii 300 Data,
+ ORIGINALTITLE "" movie_original_title Ascii 300 Data,
+ TAGLINE "" movie_tagline Ascii 1000 Data,
+ OVERVIEW "" movie_overview Text 5000 Data,
+ ISADULT "" movie_adult UInt 0 Data,
+ COLLECTIONID "" movie_collection_id UInt 0 Data,
+ COLLECTIONNAME "" movie_collection_name Ascii 300 Data,
+ BUDGET "" movie_budget UInt 0 Data,
+ REVENUE "" movie_revenue UInt 0 Data,
+ GENRES "" movie_genres Ascii 500 Data,
+ HOMEPAGE "" movie_homepage Ascii 300 Data,
+ RELEAASEDATE "" movie_release_date Ascii 20 Data,
+ RUNTIME "" movie_runtime UInt 0 Data,
+ POPULARITY "" movie_popularity Float 31 Data,
+ VOTEAVERAGE "" movie_vote_average Float 31 Data,
+}
+
+// ----------------------------------------------------------------
+// Indices for Movies
+// ----------------------------------------------------------------
+
+Index movie
+{
+ movie_id "" MOVIEID,
+ movietitle "" TITLE,
+}
+
+// ----------------------------------------------------------------
+// Table MovieActor
+// ----------------------------------------------------------------
+
+Table movie_actor
+{
+ ACTORID "" actor_id UInt 0 Primary,
+ ACTORNAME "" actor_name Ascii 300 Data,
+}
+
+// ----------------------------------------------------------------
+// Indices for MovieActor
+// ----------------------------------------------------------------
+
+Index movie_actor
+{
+ actor_id "" ACTORID,
+}
+
+// ----------------------------------------------------------------
+// Table MovieActors
+// ----------------------------------------------------------------
+
+Table movie_actors
+{
+ MOVIEID "" movie_id UInt 0 Primary,
+ ACTORID "" actor_id UInt 0 Primary,
+ ROLE "" actor_role Ascii 300 Data,
+}
+
+// ----------------------------------------------------------------
+// Indices for MovieActors
+// ----------------------------------------------------------------
+
+Index movie_actors
+{
+ movie_id "" MOVIEID,
+ actor_id "" ACTORID,
+}
+
+// ----------------------------------------------------------------
+// Table MovieMedia
+// ----------------------------------------------------------------
+
+Table movie_media
+{
+ MOVIEID "" movie_id UInt 0 Primary,
+ ACTORID "" actor_id UInt 0 Primary,
+ MEDIATYPE "" media_type UInt 0 Primary,
+ MEDIAURL "" media_url Ascii 100 Data,
+ MEDIAWIDTH "" media_width UInt 0 Data,
+ MEDIAHEIGHT "" media_height UInt 0 Data,
+ MEDIACONTENT "" media_content Mlob 1000000 Data,
+}
+
+// ----------------------------------------------------------------
+// Indices for MovieMedia
+// ----------------------------------------------------------------
+
+Index movie_media
+{
+ movie_id "" MOVIEID,
+ actor_id "" ACTORID,
+}
diff --git a/configs/epgd.conf b/configs/epgd.conf
new file mode 100644
index 0000000..fbcb03d
--- /dev/null
+++ b/configs/epgd.conf
@@ -0,0 +1,49 @@
+# ------------------------------------
+# Configuration of EPG Daemon
+# ------------------------------------
+
+# EpgImageSize
+# 0 = 174x130
+# 1 = 329x245
+# 2 = 525x400
+
+DbHost = localhost
+DbPort = 3306
+DbName = epg2vdr
+DbUser = epg2vdr
+DbPass = epg
+
+EpgView = eventsview.sql
+EpgViewWeb = eventsviewplain.sql
+TheTvDBView = thetvdbview.sql
+
+CheckInitial = 1
+DaysInAdvance = 8
+DaysToUpdate = 4
+UpdateTime = 12
+UpdateThreshold = 500
+
+XmlStoreToFs = 0
+
+GetEPGImages = 1
+EpgImageSize = 2
+MaxImagesPerEvent = 1
+
+SeriesEnabled = 1
+SeriesPort = 2006
+SeriesStoreToFs = 0
+SeriesUrl = eplists.constabel.net
+
+ScrapEpg = 1
+ScrapRecordings = 1
+
+# NetDevice = eth0
+
+# CachePath = /var/cache/epgd/
+# HttpPort = 9999
+
+# HttpProxy = 127.0.0.1:8000
+# UserName =
+# Password =
+
+LogLevel = 1
diff --git a/configs/eventsview-3po.sql b/configs/eventsview-3po.sql
new file mode 100644
index 0000000..3f9d167
--- /dev/null
+++ b/configs/eventsview-3po.sql
@@ -0,0 +1,75 @@
+CREATE VIEW eventsview as select cnt_useid useid, cnt_eventid eventid, cnt_channelid channelid, cnt_source source, all_updsp updsp, cnt_updflg updflg, cnt_delflg delflg, cnt_fileref fileref, cnt_tableid tableid, cnt_version version, sub_title title,
+case when sub_shorttext is null then
+ concat(
+ case when length(ifnull(sub_genre,'')) > 0 then sub_genre else '' end,
+ case when length(ifnull(sub_genre,'')) > 0 and length(ifnull(sub_country,'')) + length(ifnull(sub_year,'')) > 0 then ' (' else '' end,
+ case when length(ifnull(sub_country,'')) > 0 then sub_country else '' end,
+ case when length(ifnull(sub_country,'')) > 0 and length(ifnull(sub_year,'')) > 0 then ' ' else '' end,
+ case when length(ifnull(sub_year,'')) > 0 then sub_year else '' end,
+ case when length(ifnull(sub_genre,'')) > 0 and length(ifnull(sub_country,'')) + length(ifnull(sub_year,'')) > 0 then ')' else '' end
+ )
+else
+ concat(
+ sub_shorttext,
+ case when length(ifnull(sub_genre,'')) + length(ifnull(sub_country,'')) + length(ifnull(sub_year,'')) > 0 then ' (' else '' end,
+ case when length(ifnull(sub_genre,'')) > 0 then sub_genre else '' end,
+ case when length(ifnull(sub_country,'')) or length(ifnull(sub_year,'')) > 0 then ', ' else '' end,
+ case when length(ifnull(sub_country,'')) > 0 then sub_country else '' end,
+ case when length(ifnull(sub_country,'')) > 0 and length(ifnull(sub_year,'')) > 0 then ' ' else '' end,
+ case when length(ifnull(sub_year,'')) > 0 then sub_year else '' end,
+ case when length(ifnull(sub_genre,'')) + length(ifnull(sub_country,'')) + length(ifnull(sub_year,'')) > 0 then ')' else '' end
+ )
+end shorttext,
+cnt_starttime starttime, cnt_duration duration, cnt_parentalrating parentalrating, cnt_vps vps, cnt_contents contents, replace(
+concat(
+ TRIM(LEADING '|' FROM
+ concat(
+ case when sub_genre is Null then '' else concat('|','Genre: ',sub_genre) end,
+ case when sub_category is Null then '' else concat('|','Kategorie: ',sub_category) end,
+ case when sub_country is Null then '' else concat('|','Land: ',sub_country) end,
+ case when sub_year is Null then '' else concat('|','Jahr: ',substring(sub_year,1,4)) end,
+ case when sub_flags is Null then '' else concat('|',sub_flags) end,
+ case when sub_flags is Null or sub_audio is Null then '' else ' ' end,
+ case when sub_audio is Null then '' else concat('[', replace(sub_audio, ' ', '] ['), ']') end,
+ case when sub_flags is Null and sub_audio is Null then '|' else '' end,
+ case when sub_flags is Null or sub_audio is Null then '' else ' ' end,
+ case when cnt_parentalrating is Null then '' else concat('[FSK ',cnt_parentalrating,']') end,
+ case when cnt_source <> sub_source then concat(' [EPG: ',upper(replace(cnt_source,'vdr','dvb')),'/',upper(sub_source),']') else concat(' [EPG: ',upper(replace(cnt_source,'vdr','dvb')),']') end
+ )
+ ),
+ case when sub_source <> 'epgdata' and sub_tipp = 'TagesTipp' then '||Tagestipp' else '' end,
+ concat(
+ case when sub_txtrating is Null and sub_shortreview is null then '' else '||Bewertung: ' end,
+ case when sub_txtrating is null then '' else sub_txtrating end,
+ case when sub_txtrating is Null or sub_shortreview is null then '' else ', ' end,
+ case when sub_shortreview is Null then '' else sub_shortreview end,
+ case when sub_topic is Null then '' else concat('||','Thema: ',sub_topic) end,
+ case when sub_longdescription is Null then '' else concat('||',sub_longdescription) end,
+ case when sub_moderator is Null then '' else concat('||','Moderator: ',sub_moderator) end,
+ case when sub_commentator is Null then '' else concat('||','Kommentar: ',sub_commentator) end,
+ case when sub_producer is Null then '' else concat('|','Produzent: ',sub_producer) end,
+ case when sub_guest is Null then '' else concat('|','Gäste: ',sub_guest) end,
+ case when sub_other is Null then '' else concat('|','Sonstige: ',sub_other) end,
+ case when sub_actor is Null then '' else concat('|',replace(sub_actor,'), ', ')|')) end,
+ case when sub_director is Null and sub_screenplay is Null and sub_camera is Null and sub_music is Null then '' else '|' end,
+ case when sub_director is Null then '' else concat('|','Regie: ',sub_director) end,
+ case when sub_screenplay is Null then '' else concat('|','Drehbuch: ',sub_screenplay) end,
+ case when sub_camera is Null then '' else concat('|','Kamera: ',sub_camera) end,
+ case when sub_music is Null then '' else concat('|','Musik: ',sub_music) end,
+ case when sub_rating is Null then '' else concat('|',replace(replace(sub_rating,' / ', '|'),' ',': ')) end,
+ case when epi_episodename is Null then '' else concat('||','Serie: ',epi_episodename) end,
+ case when epi_shortname is Null then '' else concat('|','Kurzname: ',epi_shortname) end,
+ case when epi_partname is Null then '' else concat('|','Episode: ',epi_partname) end,
+ case when epi_extracol1 is Null then '' else concat('|',epi_extracol1) end,
+ case when epi_extracol2 is Null then '' else concat('|',epi_extracol2) end,
+ case when epi_extracol3 is Null then '' else concat('|',epi_extracol3) end,
+ case when epi_season is Null then '' else concat('|','Staffel: ',cast(epi_season as char)) end,
+ case when epi_part is Null then '' else concat('|','Staffelfolge: ',cast(epi_part as char)) end,
+ case when epi_parts is Null then '' else concat('|','Staffelfolgen: ',cast(epi_parts as char)) end,
+ case when epi_number is Null then '' else concat('|','Folge: ',cast(epi_number as char)) end
+ )
+)
+,'|', '
+') as description
+from
+ useevents;
diff --git a/configs/eventsview-ck.sql b/configs/eventsview-ck.sql
new file mode 100644
index 0000000..62e54e8
--- /dev/null
+++ b/configs/eventsview-ck.sql
@@ -0,0 +1,67 @@
+CREATE VIEW eventsview as select cnt_useid useid, cnt_eventid eventid, cnt_channelid channelid, cnt_source source, all_updsp updsp, cnt_updflg updflg, cnt_delflg delflg, cnt_fileref fileref, cnt_tableid tableid, cnt_version version, sub_title title,
+case when sub_shorttext is null then
+ concat(
+ case when length(ifnull(sub_genre,'')) > 0 then sub_genre else '' end,
+ case when length(ifnull(sub_genre,'')) > 0 and length(ifnull(sub_country,'')) + length(ifnull(sub_year,'')) > 0 then ' (' else '' end,
+ case when length(ifnull(sub_country,'')) > 0 then sub_country else '' end,
+ case when length(ifnull(sub_country,'')) > 0 and length(ifnull(sub_year,'')) > 0 then ' ' else '' end,
+ case when length(ifnull(sub_year,'')) > 0 then sub_year else '' end,
+ case when length(ifnull(sub_genre,'')) > 0 and length(ifnull(sub_country,'')) + length(ifnull(sub_year,'')) > 0 then ')' else '' end
+ )
+else
+ sub_shorttext
+end shorttext,
+cnt_starttime starttime, cnt_duration duration, cnt_parentalrating parentalrating, cnt_vps vps, cnt_contents contents, replace(
+concat(
+ TRIM(LEADING '|' FROM
+ concat(
+ case when sub_genre is Null then '' else concat('|','Genre: ',sub_genre) end,
+ case when sub_category is Null then '' else concat('|','Kategorie: ',sub_category) end,
+ case when sub_country is Null then '' else concat('|','Land: ',sub_country) end,
+ case when sub_year is Null then '' else concat('|','Jahr: ',substring(sub_year,1,4)) end
+ )
+ ),
+ concat(
+ case when sub_shortdescription is Null then '' else concat('||',sub_shortdescription) end,
+ case when sub_txtrating is Null and sub_shortreview is null then '' else '||' end,
+ case when sub_txtrating is null then '' else sub_txtrating end,
+ case when sub_txtrating is Null or sub_shortreview is null then '' else ', ' end,
+ case when sub_shortreview is Null then '' else sub_shortreview end,
+ case when sub_tipp is Null and sub_rating is Null then '' else '||' end,
+ case when sub_tipp is Null then '' else concat('|[',sub_tipp,']') end,
+ case when sub_rating is Null then '' else concat('|',sub_rating) end,
+ case when sub_topic is Null then '' else concat('||','Thema: ',sub_topic) end,
+ case when sub_longdescription is Null then '' else concat('||',sub_longdescription) end,
+ case when sub_moderator is Null then '' else concat('||','Moderator: ',sub_moderator) end,
+ case when sub_commentator is Null then '' else concat('||','Kommentar: ',sub_commentator) end,
+ case when sub_guest is Null then '' else concat('|','Gäste: ',sub_guest) end,
+ case when cnt_parentalrating is Null or cnt_parentalrating = 0 then '' else concat('||','Altersempfehlung: ab ',cnt_parentalrating) end,
+ case when sub_actor is Null and sub_producer is Null and sub_other is Null then '' else '|' end,
+ case when sub_actor is Null then '' else concat('|','Darsteller: ',sub_actor) end,
+ case when sub_producer is Null then '' else concat('|','Produzent: ',sub_producer) end,
+ case when sub_other is Null then '' else concat('|','Sonstige: ',sub_other) end,
+ case when sub_director is Null and sub_screenplay is Null and sub_camera is Null and sub_music is Null and sub_audio is Null and sub_flags is Null then '' else '|' end,
+ case when sub_director is Null then '' else concat('|','Regie: ',sub_director) end,
+ case when sub_screenplay is Null then '' else concat('|','Drehbuch: ',sub_screenplay) end,
+ case when sub_camera is Null then '' else concat('|','Kamera: ',sub_camera) end,
+ case when sub_music is Null then '' else concat('|','Musik: ',sub_music) end,
+ case when sub_audio is Null then '' else concat('|','Audio: ',sub_audio) end,
+ case when sub_flags is Null then '' else concat('|','Flags: ',sub_flags) end,
+ case when epi_episodename is Null then '' else concat('||','Serie: ',epi_episodename) end,
+ case when epi_shortname is Null then '' else concat('|','Kurzname: ',epi_shortname) end,
+ case when epi_partname is Null then '' else concat('|','Episode: ',epi_partname) end,
+ case when epi_extracol1 is Null then '' else concat('|',epi_extracol1) end,
+ case when epi_extracol2 is Null then '' else concat('|',epi_extracol2) end,
+ case when epi_extracol3 is Null then '' else concat('|',epi_extracol3) end,
+ case when epi_season is Null then '' else concat('|','Staffel: ',cast(epi_season as char)) end,
+ case when epi_part is Null then '' else concat('|','Staffelfolge: ',cast(epi_part as char)) end,
+ case when epi_parts is Null then '' else concat('|','Staffelfolgen: ',cast(epi_parts as char)) end,
+ case when epi_number is Null then '' else concat('|','Folge: ',cast(epi_number as char)) end,
+ case when cnt_source = sub_source or (sub_category in('Serie','Spielfilm') and length(cnt_longdescription) / length(sub_longdescription) <= 5) or cnt_longdescription is Null then '' else concat('||','DVB EPG:|',cnt_longdescription) end,
+ case when cnt_source <> sub_source then concat('||','Quelle: ',upper(replace(cnt_source,'vdr','dvb')),'/',upper(sub_source)) else concat('||','Quelle: ',upper(replace(cnt_source,'vdr','dvb'))) end
+ )
+)
+,'|', '
+') as description
+from
+ useevents;
diff --git a/configs/eventsview-horchi.sql b/configs/eventsview-horchi.sql
new file mode 100644
index 0000000..3e9debf
--- /dev/null
+++ b/configs/eventsview-horchi.sql
@@ -0,0 +1,67 @@
+CREATE VIEW eventsview as select cnt_useid useid, cnt_eventid eventid, cnt_channelid channelid, cnt_source source, all_updsp updsp, cnt_updflg updflg, cnt_delflg delflg, cnt_fileref fileref, cnt_tableid tableid, cnt_version version, sub_title title,
+case when sub_shorttext is null then
+ concat(
+ case when length(ifnull(sub_genre,'')) > 0 then sub_genre else '' end,
+ case when length(ifnull(sub_genre,'')) > 0 and length(ifnull(sub_country,'')) + length(ifnull(sub_year,'')) > 0 then ' (' else '' end,
+ case when length(ifnull(sub_country,'')) > 0 then sub_country else '' end,
+ case when length(ifnull(sub_country,'')) > 0 and length(ifnull(sub_year,'')) > 0 then ' ' else '' end,
+ case when length(ifnull(sub_year,'')) > 0 then sub_year else '' end,
+ case when length(ifnull(sub_genre,'')) > 0 and length(ifnull(sub_country,'')) + length(ifnull(sub_year,'')) > 0 then ')' else '' end
+ )
+else
+ sub_shorttext
+end shorttext,
+cnt_starttime starttime, cnt_duration duration, cnt_parentalrating parentalrating, cnt_vps vps, cnt_contents contents, replace(
+concat(
+ TRIM(LEADING '|' FROM
+ concat(
+ case when sub_genre is Null then '' else concat('|','Genre: ',sub_genre) end,
+ case when sub_category is Null then '' else concat('|','Kategorie: ',sub_category) end,
+ case when sub_country is Null then '' else concat('|','Land: ',sub_country) end,
+ case when sub_year is Null then '' else concat('|','Jahr: ',substring(sub_year,1,4)) end,
+ case when epi_episodename is Null then '' else concat('|Serie: ',epi_episodename,' - Staffel: ',epi_season,' / Folge: ',epi_part) end,
+ case when sub_flags is Null then '' else concat('|',sub_flags) end,
+ case when sub_flags is Null or sub_audio is Null then '' else ' ' end,
+ case when sub_audio is Null then '' else concat('[', replace(sub_audio, ' ', '] ['), ']') end,
+ case when sub_flags is Null and sub_audio is Null then '|' else '' end,
+ case when sub_flags is Null or sub_audio is Null then '' else ' ' end,
+ case when cnt_parentalrating is Null then '' else concat('[FSK ',cnt_parentalrating,']') end,
+ case when cnt_source <> sub_source then concat(' [EPG: ',upper(replace(cnt_source,'vdr','dvb')),'/',upper(sub_source),']') else concat(' [EPG: ',upper(replace(cnt_source,'vdr','dvb')),']') end
+ )
+ ),
+ case when sub_tipp is Null then '' else concat('||',sub_tipp) end,
+ concat(
+ case when sub_txtrating is Null and sub_shortreview is null then '' else '||Bewertung: ' end,
+ case when sub_txtrating is null then '' else sub_txtrating end,
+ case when sub_txtrating is Null or sub_shortreview is null then '' else ', ' end,
+ case when sub_shortreview is Null then '' else sub_shortreview end,
+ case when sub_topic is Null then '' else concat('||','Thema: ',sub_topic) end,
+ case when sub_longdescription is Null then '' else concat('||',sub_longdescription) end,
+ case when sub_moderator is Null then '' else concat('||','Moderator: ',sub_moderator) end,
+ case when sub_commentator is Null then '' else concat('||','Kommentar: ',sub_commentator) end,
+ case when sub_producer is Null then '' else concat('|','Produzent: ',sub_producer) end,
+ case when sub_guest is Null then '' else concat('|','Gäste: ',sub_guest) end,
+ case when sub_other is Null then '' else concat('|','Sonstige: ',sub_other) end,
+ case when sub_actor is Null then '' else concat('|',replace(sub_actor,'), ', ')|')) end,
+ case when sub_director is Null and sub_screenplay is Null and sub_camera is Null and sub_music is Null then '' else '|' end,
+ case when sub_director is Null then '' else concat('|','Regie: ',sub_director) end,
+ case when sub_screenplay is Null then '' else concat('|','Drehbuch: ',sub_screenplay) end,
+ case when sub_camera is Null then '' else concat('|','Kamera: ',sub_camera) end,
+ case when sub_music is Null then '' else concat('|','Musik: ',sub_music) end,
+ case when sub_rating is Null then '' else concat('|',replace(replace(sub_rating,' / ', '|'),' ',': ')) end,
+ case when epi_episodename is Null then '' else concat('||','Serie: ',epi_episodename) end,
+ case when epi_shortname is Null then '' else concat('|','Kurzname: ',epi_shortname) end,
+ case when epi_partname is Null then '' else concat('|','Episode: ',epi_partname) end,
+ case when epi_extracol1 is Null then '' else concat('|',epi_extracol1) end,
+ case when epi_extracol2 is Null then '' else concat('|',epi_extracol2) end,
+ case when epi_extracol3 is Null then '' else concat('|',epi_extracol3) end,
+ case when epi_season is Null then '' else concat('|','Staffel: ',cast(epi_season as char)) end,
+ case when epi_part is Null then '' else concat('|','Staffelfolge: ',cast(epi_part as char)) end,
+ case when epi_parts is Null then '' else concat('|','Staffelfolgen: ',cast(epi_parts as char)) end,
+ case when epi_number is Null then '' else concat('|','Folge: ',cast(epi_number as char)) end
+ )
+)
+,'|', '
+') as description
+from
+ useevents;
diff --git a/configs/eventsview-uti.sql b/configs/eventsview-uti.sql
new file mode 100644
index 0000000..2984707
--- /dev/null
+++ b/configs/eventsview-uti.sql
@@ -0,0 +1,68 @@
+CREATE VIEW eventsview as select cnt_useid useid, cnt_eventid eventid, cnt_channelid channelid, cnt_source source, all_updsp updsp, cnt_updflg updflg, cnt_delflg delflg, cnt_fileref fileref, cnt_tableid tableid, cnt_version version, sub_title title,
+case when sub_shorttext is null then
+ concat(
+ case when length(ifnull(sub_genre,'')) > 0 then sub_genre else '' end,
+ case when length(ifnull(sub_genre,'')) > 0 and length(ifnull(sub_country,'')) + length(ifnull(sub_year,'')) > 0 then ' (' else '' end,
+ case when length(ifnull(sub_country,'')) > 0 then sub_country else '' end,
+ case when length(ifnull(sub_country,'')) > 0 and length(ifnull(sub_year,'')) > 0 then ' ' else '' end,
+ case when length(ifnull(sub_year,'')) > 0 then sub_year else '' end,
+ case when length(ifnull(sub_genre,'')) > 0 and length(ifnull(sub_country,'')) + length(ifnull(sub_year,'')) > 0 then ')' else '' end
+ )
+else
+ sub_shorttext
+end shorttext,
+cnt_starttime starttime, cnt_duration duration, cnt_parentalrating parentalrating, cnt_vps vps, cnt_contents contents, replace(
+concat(
+ TRIM(LEADING '|' FROM
+ concat(
+ case when sub_genre is Null then '' else concat('|','Genre: ',sub_genre) end,
+ case when sub_category is Null then '' else concat('|','Kategorie: ',sub_category) end,
+ case when sub_country is Null then '' else concat('|','Land: ',sub_country) end,
+ case when sub_year is Null then '' else concat('|','Jahr: ',substring(sub_year,1,4)) end,
+ case when sub_flags is Null then '' else concat('|',sub_flags) end,
+ case when sub_flags is Null or sub_audio is Null then '' else ' ' end,
+ case when sub_audio is Null then '' else concat('[', replace(sub_audio, ' ', '] ['), ']') end,
+ case when sub_flags is Null and sub_audio is Null then '|' else '' end,
+ case when sub_flags is Null or sub_audio is Null then '' else ' ' end,
+ case when cnt_parentalrating is Null then '' else concat('[FSK ',cnt_parentalrating,']') end
+ )
+ ),
+ case when sub_shortdescription is Null then '' else concat('||',sub_shortdescription) end,
+ case when sub_source <> 'epgdata' and sub_tipp = '[TagesTipp]' then '||Tagestipp' else '' end,
+ concat(
+ case when sub_txtrating is Null and sub_shortreview is null then '' else '||Bewertung: ' end,
+ case when sub_txtrating is null then '' else sub_txtrating end,
+ case when sub_txtrating is Null or sub_shortreview is null then '' else ', ' end,
+ case when sub_shortreview is Null then '' else sub_shortreview end,
+ case when sub_topic is Null then '' else concat('||','Thema: ',sub_topic) end,
+ case when sub_longdescription is Null then '' else concat('||',sub_longdescription) end,
+ case when sub_moderator is Null then '' else concat('||','Moderator: ',sub_moderator) end,
+ case when sub_commentator is Null then '' else concat('||','Kommentar: ',sub_commentator) end,
+ case when sub_producer is Null then '' else concat('|','Produzent: ',sub_producer) end,
+ case when sub_guest is Null then '' else concat('|','Gäste: ',sub_guest) end,
+ case when sub_other is Null then '' else concat('|','Sonstige: ',sub_other) end,
+ case when sub_actor is Null then '' else concat('|',replace(sub_actor,'), ', ')|')) end,
+ case when sub_director is Null and sub_screenplay is Null and sub_camera is Null and sub_music is Null then '' else '|' end,
+ case when sub_director is Null then '' else concat('|','Regie: ',sub_director) end,
+ case when sub_screenplay is Null then '' else concat('|','Drehbuch: ',sub_screenplay) end,
+ case when sub_camera is Null then '' else concat('|','Kamera: ',sub_camera) end,
+ case when sub_music is Null then '' else concat('|','Musik: ',sub_music) end,
+ case when sub_rating is Null then '' else concat('|',replace(replace(sub_rating,' / ', '|'),' ',': ')) end,
+ case when epi_episodename is Null then '' else concat('||','Serie: ',epi_episodename) end,
+ case when epi_shortname is Null then '' else concat('|','Kurzname: ',epi_shortname) end,
+ case when epi_partname is Null then '' else concat('|','Episode: ',epi_partname) end,
+ case when epi_extracol1 is Null then '' else concat('|',epi_extracol1) end,
+ case when epi_extracol2 is Null then '' else concat('|',epi_extracol2) end,
+ case when epi_extracol3 is Null then '' else concat('|',epi_extracol3) end,
+ case when epi_season is Null then '' else concat('|','Staffel: ',cast(epi_season as char)) end,
+ case when epi_part is Null then '' else concat('|','Staffelfolge: ',cast(epi_part as char)) end,
+ case when epi_part is Null then '' else concat('|','Staffelfolgen: ',cast(epi_parts as char)) end,
+ case when epi_number is Null then '' else concat('|','Folge: ',cast(epi_number as char)) end,
+ case when cnt_source = sub_source or (sub_category in('Serie','Spielfilm') and length(cnt_longdescription) / length(sub_longdescription) <= 5) or cnt_longdescription is Null then '' else concat('||','DVB EPG:|',cnt_longdescription) end,
+ case when cnt_source <> sub_source then concat('||','Quelle: ',upper(replace(cnt_source,'vdr','dvb')),'/',upper(sub_source)) else concat('||','Quelle: ',upper(replace(cnt_source,'vdr','dvb'))) end
+ )
+)
+,'|', '
+') as description
+from
+ useevents;
diff --git a/configs/eventsview.sql b/configs/eventsview.sql
new file mode 100644
index 0000000..6cee8af
--- /dev/null
+++ b/configs/eventsview.sql
@@ -0,0 +1,66 @@
+CREATE VIEW eventsview as select cnt_useid useid, cnt_eventid eventid, cnt_channelid channelid, cnt_source source, all_updsp updsp, cnt_updflg updflg, cnt_delflg delflg, cnt_fileref fileref, cnt_tableid tableid, cnt_version version, sub_title title,
+case when sub_shorttext is null then
+ concat(
+ case when length(ifnull(sub_genre,'')) > 0 then sub_genre else '' end,
+ case when length(ifnull(sub_genre,'')) > 0 and length(ifnull(sub_country,'')) + length(ifnull(sub_year,'')) > 0 then ' (' else '' end,
+ case when length(ifnull(sub_country,'')) > 0 then sub_country else '' end,
+ case when length(ifnull(sub_country,'')) > 0 and length(ifnull(sub_year,'')) > 0 then ' ' else '' end,
+ case when length(ifnull(sub_year,'')) > 0 then sub_year else '' end,
+ case when length(ifnull(sub_genre,'')) > 0 and length(ifnull(sub_country,'')) + length(ifnull(sub_year,'')) > 0 then ')' else '' end
+ )
+else
+ sub_shorttext
+end shorttext,
+cnt_starttime starttime, cnt_duration duration, cnt_parentalrating parentalrating, cnt_vps vps, cnt_contents contents, replace(
+concat(
+ TRIM(LEADING '|' FROM
+ concat(
+ case when sub_genre is Null then '' else concat('|','Genre: ',sub_genre) end,
+ case when sub_category is Null then '' else concat('|','Kategorie: ',sub_category) end,
+ case when sub_country is Null then '' else concat('|','Land: ',sub_country) end,
+ case when sub_year is Null then '' else concat('|','Jahr: ',substring(sub_year,1,4)) end
+ )
+ ),
+ concat(
+ case when sub_shortdescription is Null then '' else concat('||',sub_shortdescription) end,
+ case when sub_txtrating is Null and sub_shortreview is null then '' else '||' end,
+ case when sub_txtrating is null then '' else sub_txtrating end,
+ case when sub_txtrating is Null or sub_shortreview is null then '' else ', ' end,
+ case when sub_shortreview is Null then '' else sub_shortreview end,
+ case when sub_tipp is Null and sub_rating is Null then '' else '||' end,
+ case when sub_tipp is Null then '' else concat('|',sub_tipp) end,
+ case when sub_rating is Null then '' else concat('|',sub_rating) end,
+ case when sub_topic is Null then '' else concat('||','Thema: ',sub_topic) end,
+ case when sub_longdescription is Null then '' else concat('||',sub_longdescription) end,
+ case when sub_moderator is Null then '' else concat('||','Moderator: ',sub_moderator) end,
+ case when sub_commentator is Null then '' else concat('||','Kommentar: ',sub_commentator) end,
+ case when sub_guest is Null then '' else concat('|','Gäste: ',sub_guest) end,
+ case when cnt_parentalrating is Null or cnt_parentalrating = 0 then '' else concat('||','Altersempfehlung: ab ',cnt_parentalrating) end,
+ case when sub_actor is Null and sub_producer is Null and sub_other is Null then '' else '|' end,
+ case when sub_actor is Null then '' else concat('|','Darsteller: ',sub_actor) end,
+ case when sub_producer is Null then '' else concat('|','Produzent: ',sub_producer) end,
+ case when sub_other is Null then '' else concat('|','Sonstige: ',sub_other) end,
+ case when sub_director is Null and sub_screenplay is Null and sub_camera is Null and sub_music is Null and sub_audio is Null and sub_flags is Null then '' else '|' end,
+ case when sub_director is Null then '' else concat('|','Regie: ',sub_director) end,
+ case when sub_screenplay is Null then '' else concat('|','Drehbuch: ',sub_screenplay) end,
+ case when sub_camera is Null then '' else concat('|','Kamera: ',sub_camera) end,
+ case when sub_music is Null then '' else concat('|','Musik: ',sub_music) end,
+ case when sub_audio is Null then '' else concat('|','Audio: ',sub_audio) end,
+ case when sub_flags is Null then '' else concat('|','Flags: ',sub_flags) end,
+ case when epi_episodename is Null then '' else concat('||','Serie: ',epi_episodename) end,
+ case when epi_shortname is Null then '' else concat('|','Kurzname: ',epi_shortname) end,
+ case when epi_partname is Null then '' else concat('|','Episode: ',epi_partname) end,
+ case when epi_extracol1 is Null then '' else concat('|',epi_extracol1) end,
+ case when epi_extracol2 is Null then '' else concat('|',epi_extracol2) end,
+ case when epi_extracol3 is Null then '' else concat('|',epi_extracol3) end,
+ case when epi_season is Null then '' else concat('|','Staffel: ',cast(epi_season as char)) end,
+ case when epi_part is Null then '' else concat('|','Staffelfolge: ',cast(epi_part as char)) end,
+ case when epi_parts is Null then '' else concat('|','Staffelfolgen: ',cast(epi_parts as char)) end,
+ case when epi_number is Null then '' else concat('|','Folge: ',cast(epi_number as char)) end,
+ case when cnt_source <> sub_source then concat('||','Quelle: ',upper(replace(cnt_source,'vdr','dvb')),'/',upper(sub_source)) else concat('||','Quelle: ',upper(replace(cnt_source,'vdr','dvb'))) end
+ )
+)
+,'|', '
+') as description
+from
+ useevents;
diff --git a/configs/eventsviewplain-3po.sql b/configs/eventsviewplain-3po.sql
new file mode 100644
index 0000000..1b2c511
--- /dev/null
+++ b/configs/eventsviewplain-3po.sql
@@ -0,0 +1,99 @@
+CREATE VIEW eventsviewplain
+as
+select
+ cnt_useid,
+ cnt_eventid,
+ cnt_channelid,
+ sub_eventid as imageid,
+ cnt_source,
+ all_updsp,
+ cnt_updflg as updflg,
+ cnt_delflg,
+ cnt_fileref,
+ cnt_tableid,
+ cnt_version,
+ sub_title,
+ sub_comptitle,
+ case
+ when ifnull(sub_compshorttext,'') = '' or sub_comptitle=sub_compshorttext or ( substring(sub_compshorttext,1,1)='S' and substring(sub_compshorttext,2,1) REGEXP ('[0-9]') ) then ''
+ else sub_compshorttext
+ end sub_compshorttext,
+ sub_complongdescription,
+ case
+ when ifnull(sub_shorttext,'') = '' or sub_comptitle=sub_compshorttext or ( substring(sub_shorttext,1,1)='S' and substring(sub_shorttext,2,1) REGEXP ('[0-9]') ) then ''
+ else sub_shorttext
+ end sub_shorttext,
+ cnt_starttime,
+ cnt_duration,
+ cnt_parentalrating,
+ cnt_vps,
+ cnt_contents,
+ sub_imagecount,
+ sub_genre,
+ sub_category,
+ sub_country,
+ sub_year,
+ sub_shortdescription,
+ concat(
+ case
+ when cnt_source = 'epgdata' then
+ case when sub_rating like '%Allgemein *****%' then concat('|','Bewertung: Sehr empfehlenswert')
+ when sub_rating like '%Allgemein ****%' then concat('|','Bewertung: Empfehlenswert')
+ when sub_rating like '%Allgemein ***%' then concat('|','Bewertung: Eher durchschnittlich')
+ when sub_rating like '%Allgemein **%' then concat('|','Bewertung: Eher nicht empfehlenswert')
+ when sub_rating like '%Allgemein *%' then concat('|','Bewertung: Eher uninteressant')
+ else ''
+ end
+ else
+ case when sub_numrating = 5 then 'Bewertung: Einer der besten Filme aller Zeiten'
+ when sub_numrating = 4 then 'Bewertung: Sehr empfehlenswert'
+ when sub_numrating = 3 then 'Bewertung: Empfehlenswert'
+ when sub_numrating = 2 then 'Bewertung: Eher durchschnittlich'
+ when sub_numrating = 1 then 'Bewertung: Eher uninteressant'
+ else ''
+ end
+ end,
+ case when ((cnt_source = 'epgdata' and sub_rating is null) or sub_numrating is Null) or sub_shortreview is null then '' else ', ' end,
+ case when sub_shortreview is Null then '' else sub_shortreview end
+) as sub_shortreview,
+ sub_tipp,
+ sub_rating,
+ sub_numrating,
+ sub_txtrating,
+ sub_topic,
+ sub_longdescription,
+ case when cnt_source != sub_source and sub_category not in('Serie','Spielfilm') then cnt_longdescription end cnt_longdescription,
+ sub_moderator,
+ sub_guest,
+ sub_actor,
+ sub_producer,
+ sub_other,
+ sub_director,
+ sub_commentator,
+ sub_screenplay,
+ sub_camera,
+ sub_music,
+ sub_audio,
+ sub_flags,
+ sub_episodecompname,
+ sub_episodecompshortname,
+ sub_episodecomppartname,
+ sub_scrseriesid,
+ sub_scrseriesepisode,
+ sub_scrmovieid,
+ epi_episodename,
+ epi_shortname,
+ epi_partname,
+ epi_lang,
+ epi_extracol1,
+ epi_extracol2,
+ epi_extracol3,
+ epi_season,
+ epi_part,
+ epi_parts,
+ epi_number,
+ case when cnt_source <> sub_source then concat(upper(replace(cnt_source,'vdr','dvb')),'/',upper(sub_source)) else upper(replace(cnt_source,'vdr','dvb')) end merge
+from
+ useevents
+where
+ cnt_updflg in('A','L','P');
diff --git a/configs/eventsviewplain.sql b/configs/eventsviewplain.sql
new file mode 100644
index 0000000..51b9a71
--- /dev/null
+++ b/configs/eventsviewplain.sql
@@ -0,0 +1,78 @@
+CREATE VIEW eventsviewplain
+as
+select
+ cnt_useid,
+ cnt_eventid,
+ cnt_channelid,
+ sub_eventid as imageid,
+ cnt_source,
+ all_updsp,
+ cnt_updflg as updflg,
+ cnt_delflg,
+ cnt_fileref,
+ cnt_tableid,
+ cnt_version,
+ sub_title,
+ sub_comptitle,
+ case
+ when ifnull(sub_compshorttext,'') = '' or sub_comptitle=sub_compshorttext or ( substring(sub_compshorttext,1,1)='S' and substring(sub_compshorttext,2,1) REGEXP ('[0-9]') ) then ''
+ else sub_compshorttext
+ end sub_compshorttext,
+ sub_complongdescription,
+ case
+ when ifnull(sub_shorttext,'') = '' or sub_comptitle=sub_compshorttext or ( substring(sub_shorttext,1,1)='S' and substring(sub_shorttext,2,1) REGEXP ('[0-9]') ) then ''
+ else sub_shorttext
+ end sub_shorttext,
+ cnt_starttime,
+ cnt_duration,
+ cnt_parentalrating,
+ cnt_vps,
+ cnt_contents,
+ sub_imagecount,
+ sub_genre,
+ sub_category,
+ sub_country,
+ sub_year,
+ sub_shortdescription,
+ sub_shortreview,
+ sub_tipp,
+ sub_rating,
+ sub_numrating,
+ sub_txtrating,
+ sub_topic,
+ sub_longdescription,
+ case when cnt_source != sub_source and sub_category not in('Serie','Spielfilm') then cnt_longdescription end cnt_longdescription,
+ sub_moderator,
+ sub_guest,
+ sub_actor,
+ sub_producer,
+ sub_other,
+ sub_director,
+ sub_commentator,
+ sub_screenplay,
+ sub_camera,
+ sub_music,
+ sub_audio,
+ sub_flags,
+ sub_episodecompname,
+ sub_episodecompshortname,
+ sub_episodecomppartname,
+ sub_scrseriesid,
+ sub_scrseriesepisode,
+ sub_scrmovieid,
+ epi_episodename,
+ epi_shortname,
+ epi_partname,
+ epi_lang,
+ epi_extracol1,
+ epi_extracol2,
+ epi_extracol3,
+ epi_season,
+ epi_part,
+ epi_parts,
+ epi_number,
+ case when cnt_source <> sub_source then concat(upper(replace(cnt_source,'vdr','dvb')),'/',upper(sub_source)) else upper(replace(cnt_source,'vdr','dvb')) end merge
+from
+ useevents
+where
+ cnt_updflg in('A','L','P');
diff --git a/configs/getcrosslvr.sql b/configs/getcrosslvr.sql
new file mode 100644
index 0000000..bc6f943
--- /dev/null
+++ b/configs/getcrosslvr.sql
@@ -0,0 +1,34 @@
+CREATE FUNCTION getcrosslvr ( comp1 varchar(300), comp2 varchar(300) )
+RETURNS int
+NOT DETERMINISTIC
+BEGIN
+DECLARE ratio DECIMAL(7,2);
+DECLARE shorter varchar(300);
+DECLARE longer varchar(300);
+DECLARE complenght int;
+DECLARE crosslv int;
+DECLARE crosslvt int;
+IF char_length(comp1) = 0 then
+ set ratio = 999;
+ set shorter = comp1; set longer = comp2; set complenght = char_length(comp2);
+ELSEIF char_length(comp2) = 0 then
+ set ratio = 999;
+ set shorter = comp2; set longer = comp1; set complenght = char_length(comp1);
+ELSEIF char_length(comp1) >= char_length(comp2) then
+ set ratio = round(char_length(comp1) / char_length(comp2),2);
+ set shorter = comp2; set longer = comp1; set complenght = char_length(comp1);
+else set ratio = round(char_length(comp2) / char_length(comp1),2);
+ set shorter = comp1; set longer = comp2; set complenght = char_length(comp2);
+END IF;
+IF comp1 is null or comp2 is null then set crosslv = 99;
+ELSEIF comp1 = comp2 then set crosslv = 0;
+ELSEIF comp1 is Null or comp2 is Null or char_length(comp1) = 0 or char_length(comp2) = 0 then set crosslv = 99;
+ELSEIF complenght >= 10 and ratio >= 3.2 then set crosslv = 99;
+ELSEIF ratio < 1.5 then set crosslv = epglvr(shorter,longer);
+ELSE set crosslvt = epglvr(shorter,substr(longer,1,complenght/2));
+ IF crosslvt < 60 then set crosslv = crosslvt;
+ ELSE set crosslv = epglvr(substr(longer from complenght/2+1),shorter);
+ END IF;
+END IF;
+RETURN crosslv;
+END
diff --git a/configs/getlvrmin.sql b/configs/getlvrmin.sql
new file mode 100644
index 0000000..d075dfd
--- /dev/null
+++ b/configs/getlvrmin.sql
@@ -0,0 +1,51 @@
+CREATE FUNCTION getlvrmin ( vdr_starttime int(10), ext_starttime int(10), vdr_duration int(5), ext_duration int(5), lv1 int(3), lv2 int(3), lv3 int(3), lv4 int(3) )
+RETURNS int
+NOT DETERMINISTIC
+BEGIN
+DECLARE delta_st int;
+DECLARE delta_du int;
+DECLARE lv_time int default 0;
+DECLARE lv_min int;
+DECLARE lv_dur int;
+DECLARE lv_sum int;
+
+IF vdr_duration = ext_duration then set delta_du = 100;
+ELSEIF vdr_duration > ext_duration then set delta_du = round(vdr_duration/ext_duration*100,0);
+ELSE set delta_du = round(ext_duration/vdr_duration*100,0);
+END IF;
+
+IF delta_du > 184 then set lv_dur = 50;
+ELSEIF delta_du > 168 then set lv_dur = 40;
+ELSEIF delta_du > 152 then set lv_dur = 30;
+ELSEIF delta_du > 136 then set lv_dur = 20;
+ELSEIF delta_du > 120 then set lv_dur = 10;
+ELSE set lv_dur = 0;
+END IF;
+
+IF vdr_starttime = ext_starttime then set delta_st = 100;
+ELSEIF vdr_starttime > ext_starttime then set delta_st = vdr_starttime-ext_starttime;
+ELSE set delta_st = ext_starttime-vdr_starttime;
+END IF;
+
+IF delta_st <= 600 then
+ IF delta_st > 540 then set lv_time = 50;
+ ELSEIF delta_st > 480 then set lv_time = 40;
+ ELSEIF delta_st > 420 then set lv_time = 30;
+ ELSEIF delta_st > 360 then set lv_time = 20;
+ ELSEIF delta_st > 300 then set lv_time = 10;
+ ELSE set lv_time = 0;
+ END IF;
+
+ set lv_min = LEAST(lv1,lv2,lv3,lv4);
+
+ set lv_sum = lv_time + lv_dur + lv_min;
+
+ELSEIF lv_dur + lv1 + lv4 <= 60 then set lv_sum = 60;
+ELSEIF lv1 <= 30 and vdr_starttime = ext_starttime then set lv_sum = 60;
+ELSEIF lv1 <= 30 and vdr_duration/abs(vdr_starttime-ext_starttime) >= 2.5 then set lv_sum = 60;
+ELSE set lv_sum = 99;
+END IF;
+
+RETURN lv_sum;
+
+END
diff --git a/configs/getupdflg.sql b/configs/getupdflg.sql
new file mode 100644
index 0000000..dd63734
--- /dev/null
+++ b/configs/getupdflg.sql
@@ -0,0 +1,33 @@
+CREATE FUNCTION getupdflg ( master_id INT(11) )
+RETURNS CHAR
+NOT DETERMINISTIC
+BEGIN
+DECLARE upd_flg CHAR;
+ SET upd_flg = (
+ select case
+ when ev.source != 'vdr' and ev.starttime <= cm.mergesp and ev.masterid != ev.useid then 'C'
+ when ev.source != 'vdr' and ev.starttime <= cm.mergesp and jo.masterid is not Null then 'T'
+ when ev.delflg = 'Y' then 'D'
+ when ev.source != 'vdr' and ev.starttime <= cm.mergesp and jo.masterid is Null then 'R'
+ when ev.source != 'vdr' and ev.starttime > cm.mergesp then 'A'
+ when ev.source = 'vdr' and cm.mergesp is Null then 'P'
+ when ev.source = 'vdr' and ev.starttime > cm.mergesp then 'I'
+ when ev.source = 'vdr' and ev.starttime <= cm.mergesp and ev.masterid = ev.useid then 'A'
+ when ev.source = 'vdr' and ev.starttime <= cm.mergesp and ev.masterid != ev.useid then 'L'
+ end
+from
+ events ev
+left join
+ events jo
+on
+ ev.masterid = jo.useid and jo.useid != 0 and jo.updflg = 'L'
+left join
+ (select distinct mergesp,channelid from channelmap where channelmap.source != 'vdr') cm
+on
+ ev.channelid = cm.channelid
+where
+ ev.masterid = master_id
+ limit 1
+);
+RETURN upd_flg;
+END
diff --git a/configs/mergeepg.sql b/configs/mergeepg.sql
new file mode 100644
index 0000000..bde021a
--- /dev/null
+++ b/configs/mergeepg.sql
@@ -0,0 +1,401 @@
+CREATE PROCEDURE mergeepg ()
+BEGIN
+/*
+* declare variables
+*/
+DECLARE startrun INT;
+DECLARE lastrun INT;
+/*
+* configure merge
+*/
+set @epi ='a';
+set @img ='b';
+set @sht ='';
+/*
+* fix useid = 0
+*/
+update events set useid = masterid where useid = 0;
+/*
+* cleanup deleted Links
+*/
+update
+ events e
+set
+ e.useid = e.masterid,
+ e.updsp = unix_timestamp()
+where
+ e.source = 'vdr' and
+ e.updflg in ('D','R') and
+ e.useid != e.masterid and
+ e.starttime + e.duration >= unix_timestamp();
+/*
+* cleanup broken Links
+*/
+update
+ events t
+ inner join
+(
+ select
+ v.masterid,
+ e.starttime,
+ e.comptitle,
+ e.compshorttext,
+ e.updflg
+ from
+ events v
+ left join
+ events e
+ on e.masterid = v.useid and v.masterid != e.masterid and e.updflg in ('T','C') and e.delflg is Null
+ where
+ v.updflg in('L') and
+ v.starttime + v.duration >= unix_timestamp()
+) s
+on t.masterid=s.masterid
+set
+ t.useid = t.masterid,
+ t.updflg = case when t.delflg = 'Y' then 'D' else 'A' end,
+ t.updsp = unix_timestamp()
+where
+ s.updflg is Null or
+ abs(s.starttime - t.starttime) > 21600 or
+ (abs(s.starttime - t.starttime) > 3600 and
+ getlvrmin(0,0,0,0,
+ getcrosslvr(s.comptitle,t.comptitle),
+ getcrosslvr(s.comptitle,t.compshorttext),
+ getcrosslvr(s.compshorttext,t.comptitle),
+ getcrosslvr(s.compshorttext,t.compshorttext)) >= 60);
+/*
+* cleanup broken Targets
+*/
+update
+ events t
+ inner join
+(
+ select
+ e.masterid,
+ v.updflg
+ from
+ events e
+ left join
+ events v
+ on e.masterid = v.useid and v.masterid != e.masterid and v.updflg = 'L'
+ where
+ e.updflg in('C','T') and
+ e.starttime + e.duration >= unix_timestamp()
+) s
+on t.masterid=s.masterid
+set
+ t.useid = t.masterid,
+ t.updflg = case when t.delflg = 'Y' then 'D' else 'R' end,
+ t.updsp = unix_timestamp()
+where
+ s.updflg is Null;
+/*
+* reset tables
+*/
+truncate snapshot;
+truncate analyse;
+/*
+* prepare snapshot
+*/
+insert into snapshot
+select
+ distinct ev.channelid, ev.source, masterid, ev.eventid, useid, starttime, duration, title, comptitle, shorttext, compshorttext, ev.updsp,
+ case when episodecompname is Null then Null else 'X' end episodecompname,
+ case when ev.source = 'vdr' then Null else cm.merge end merge,
+ case when ev.imagecount >0 then 'X' else Null end images
+from
+ events ev
+ inner join channelmap cm on ( ev.channelid = cm.channelid )
+where
+ ( ev.source = cm.source or ev.source = 'vdr' )
+ AND
+ ev.updflg in ('I','A','R','S')
+ AND
+ ev.starttime between unix_timestamp() - ev.duration and unix_timestamp() + 259200
+ AND
+ cm.merge in(1,2);
+/*
+* do the magic
+*/
+set @pk1 ='';
+set @rn1 =10000;
+set @lvmin ='';
+insert ignore into analyse
+SELECT channelid, vdr_masterid, vdr_eventid, vdr_starttime, vdr_duration, vdr_title, vdr_shorttext, ext_masterid, ext_eventid, ext_starttime, ext_duration, ext_title, ext_shorttext, ext_episodecompname, ext_merge, ext_images, lvmin,
+ epi+img+sht + ranklv as rank
+FROM
+(
+ SELECT channelid, vdr_masterid, vdr_eventid, vdr_starttime, vdr_duration, vdr_title, vdr_shorttext, ext_masterid, ext_eventid, ext_starttime, ext_duration, ext_title, ext_shorttext, ext_episodecompname, ext_merge, ext_images, lvmin,
+ case when ext_episodecompname is not Null then 0 else (case @epi when 'a' then 1000 when 'b' then 100 when 'c' then 10 else 0 end) end epi,
+ case when ext_images is not Null then 0 else (case @img when 'a' then 1000 when 'b' then 100 when 'c' then 10 else 0 end) end img,
+ case when ext_shorttext is not Null then 0 else (case @sht when 'a' then 1000 when 'b' then 100 when 'c' then 10 else 0 end) end sht,
+ @rn1 := if(@pk1=concat(channelid,vdr_eventid,ext_source), if(@lv=lvmin, @rn1, @rn1+10000),10000+ext_merge) as ranklv,
+ @pk1 := concat(channelid,vdr_eventid,ext_source),
+ @lv := lvmin
+ FROM
+ (
+ select
+ vdr.channelid,
+ vdr.masterid vdr_masterid, vdr.eventid vdr_eventid, vdr.starttime vdr_starttime, vdr.duration vdr_duration, vdr.title vdr_title, vdr.shorttext vdr_shorttext,
+ ext.masterid ext_masterid, ext.eventid ext_eventid, ext.starttime ext_starttime, ext.duration ext_duration, ext.title ext_title, ext.shorttext ext_shorttext,
+ ext.source ext_source,ext.episode ext_episodecompname, ext.merge ext_merge, ext.images ext_images,
+ getlvrmin(vdr.starttime,ext.starttime,vdr.duration,ext.duration,
+ getcrosslvr(vdr.comptitle,ext.comptitle),
+ getcrosslvr(vdr.comptitle,ext.compshorttext),
+ getcrosslvr(vdr.compshorttext,ext.comptitle),
+ getcrosslvr(vdr.compshorttext,ext.compshorttext)
+ ) lvmin
+ from
+ snapshot vdr
+ inner join
+ snapshot ext
+ on vdr.channelid = ext.channelid and
+ vdr.starttime - ext.starttime between -1200 and +1200 and
+ (vdr.duration+1) / (ext.duration+1) * 100 between 50 and 200
+ where
+ vdr.source = 'vdr'
+ AND
+ vdr.source <> ext.source
+ ORDER BY channelid,vdr_eventid,ext_merge desc,lvmin
+ ) A
+ where lvmin <= 60
+) B
+order by channelid,vdr_eventid,rank;
+/*
+* update mergesp
+*/
+update
+ channelmap cm,
+ (select channelid,max(vdr_starttime) merge_sp from analyse group by channelid) an
+set
+ cm.mergesp = an.merge_sp
+where
+ an.channelid = cm.channelid and
+ an.merge_sp > cm.mergesp;
+/*
+* update useid on vdr events
+*/
+update
+ events ev,
+ analyse an
+set
+ ev.useid = an.ext_masterid
+where
+ ev.masterid = vdr_masterid and
+ ev.channelid = an.channelid;
+/*
+* update useid on ext events
+*/
+update
+ events ext,
+ events vdr
+set
+ ext.useid = case when ext.updflg in('I','R') then vdr.masterid else ext.useid end,
+ ext.updflg = case when ext.updflg in('I','R') then 'C' else 'T' end,
+ ext.updsp = case when ext.updflg in('I','R') then unix_timestamp() else ext.updsp end
+where
+ ext.masterid = vdr.useid and
+ ext.channelid = vdr.channelid and
+ ext.updflg in('I','A','R','S') and
+ ext.source <> vdr.source and
+ vdr.source = 'vdr';
+/*
+* update all other relevant updflg and updsp
+*/
+update
+ events ev,
+ (select distinct channelid,mergesp from channelmap where source != 'vdr') cm
+set
+ ev.updflg = case when ev.source = 'vdr' and ev.masterid != ev.useid then 'L'
+ when ev.source = 'vdr' and ev.masterid = ev.useid then 'A'
+ when ev.source != 'vdr' and ev.updflg = 'S' then 'I'
+ when ev.source != 'vdr' then 'R'
+ end,
+ ev.updsp = case when source = 'vdr' and ev.masterid = ev.useid and ev.updflg = 'A' then ev.updsp
+ else unix_timestamp()
+ end
+where
+ ev.channelid = cm.channelid and
+ ev.starttime < cm.mergesp + ev.duration/10 and
+ ( ev.source = 'vdr' and ev.updflg in ('I','A') or
+ ev.source != 'vdr' and ev.updflg in ('A','S') );
+/*
+* reinitialize prior removed dvb events
+*/
+update
+ events ev,
+ (select distinct channelid,mergesp from channelmap where source != 'vdr') cm
+set
+ ev.updflg = 'A',
+ ev.useid = ev.masterid,
+ ev.updsp = unix_timestamp()
+where
+ ev.channelid = cm.channelid and
+ ev.starttime < cm.mergesp + ev.duration/10 and
+ ev.source = 'vdr' and
+ ev.updflg in ('R');
+/*
+* get startrun / lastrun
+*/
+SET startrun = (select unix_timestamp());
+SET lastrun = (select value from parameters where owner = 'epgd' and name = 'mergeStart');
+/*
+* truncate useevents if needed
+*/
+IF lastrun = 0 then truncate table useevents;
+END IF;
+/*
+* update useevents
+*/
+insert into
+ useevents( cnt_source, cnt_channelid, cnt_eventid, cnt_masterid, cnt_useid, sub_source, sub_eventid, all_updsp, cnt_updflg, cnt_delflg, cnt_fileref, cnt_tableid, cnt_version, sub_title, sub_shorttext, sub_comptitle, sub_compshorttext, sub_genre, sub_country, sub_year, cnt_starttime, cnt_duration, cnt_parentalrating, cnt_vps, cnt_contents, sub_category, sub_shortdescription, sub_shortreview, sub_tipp, sub_rating, sub_numrating, sub_txtrating, sub_topic, sub_longdescription, sub_complongdescription, cnt_longdescription, sub_moderator, sub_guest, sub_actor, sub_producer, sub_other, sub_director, sub_commentator, sub_screenplay, sub_camera, sub_music, sub_audio, sub_flags, sub_imagecount, sub_scrseriesid, sub_scrseriesepisode, sub_scrmovieid, sub_scrsp, sub_episodecompname, sub_episodecompshortname, sub_episodecomppartname, epi_episodename, epi_shortname, epi_partname, epi_lang, epi_extracol1, epi_extracol2, epi_extracol3, epi_season, epi_part, epi_parts, epi_number )
+select
+ cnt.source,
+ cnt.channelid,
+ cnt.eventid,
+ cnt.masterid,
+ cnt.useid,
+ sub.source,
+ sub.eventid,
+ GREATEST(cnt.updsp,sub.updsp,IFNULL(epi.updsp,0)),
+ cnt.updflg,
+ cnt.delflg,
+ cnt.fileref,
+ cnt.tableid,
+ cnt.version,
+ sub.title,
+ sub.shorttext,
+ sub.comptitle,
+ sub.compshorttext,
+ sub.genre,
+ sub.country,
+ sub.year,
+ cnt.starttime,
+ cnt.duration,
+ cnt.parentalrating,
+ cnt.vps,
+ cnt.contents,
+ sub.category,
+ sub.shortdescription,
+ sub.shortreview,
+ sub.tipp,
+ sub.rating,
+ sub.numrating,
+ sub.txtrating,
+ sub.topic,
+ sub.longdescription,
+ sub.complongdescription,
+ cnt.longdescription,
+ sub.moderator,
+ sub.guest,
+ sub.actor,
+ sub.producer,
+ sub.other,
+ sub.director,
+ sub.commentator,
+ sub.screenplay,
+ sub.camera,
+ sub.music,
+ sub.audio,
+ sub.flags,
+ sub.imagecount,
+ sub.scrseriesid,
+ sub.scrseriesepisode,
+ sub.scrmovieid,
+ sub.scrsp,
+ sub.episodecompname,
+ sub.episodecompshortname,
+ sub.episodecomppartname,
+ epi.episodename,
+ epi.shortname,
+ epi.partname,
+ epi.lang,
+ epi.extracol1,
+ epi.extracol2,
+ epi.extracol3,
+ epi.season,
+ epi.part,
+ epi.parts,
+ epi.number
+from
+ events cnt
+ inner join events sub on (case when cnt.useid = 0 then cnt.masterid else cnt.useid end = sub.masterid)
+ left outer join episodes epi on (sub.episodecompname = epi.compname and sub.episodecomppartname = epi.comppartname and sub.episodelang = epi.lang)
+where
+ cnt.updflg in('A','L','P','C','R','D','X') and
+ GREATEST(cnt.updsp,sub.updsp,IFNULL(epi.updsp,0)) >= lastrun - 5
+ON DUPLICATE KEY UPDATE
+ cnt_masterid = cnt.masterid,
+ cnt_useid = cnt.useid,
+ sub_source = sub.source,
+ sub_eventid = sub.eventid,
+ all_updsp = GREATEST(cnt.updsp,sub.updsp,IFNULL(epi.updsp,0)),
+ cnt_updflg = cnt.updflg,
+ cnt_delflg = cnt.delflg,
+ cnt_fileref = cnt.fileref,
+ cnt_tableid = cnt.tableid,
+ cnt_version = cnt.version,
+ sub_title = sub.title,
+ sub_shorttext = sub.shorttext,
+ sub_comptitle = sub.comptitle,
+ sub_compshorttext = sub.compshorttext,
+ sub_genre = sub.genre,
+ sub_country = sub.country,
+ sub_year = sub.year,
+ cnt_starttime = cnt.starttime,
+ cnt_duration = cnt.duration,
+ cnt_parentalrating = cnt.parentalrating,
+ cnt_vps = cnt.vps,
+ cnt_contents = cnt.contents,
+ sub_category = sub.category,
+ sub_shortdescription = sub.shortdescription,
+ sub_shortreview = sub.shortreview,
+ sub_tipp = sub.tipp,
+ sub_rating = sub.rating,
+ sub_numrating = sub.numrating,
+ sub_txtrating = sub.txtrating,
+ sub_topic = sub.topic,
+ sub_longdescription = sub.longdescription,
+ sub_complongdescription = sub.complongdescription,
+ cnt_longdescription = cnt.longdescription,
+ sub_moderator = sub.moderator,
+ sub_guest = sub.guest,
+ sub_actor = sub.actor,
+ sub_producer = sub.producer,
+ sub_other = sub.other,
+ sub_director = sub.director,
+ sub_commentator = sub.commentator,
+ sub_screenplay = sub.screenplay,
+ sub_camera = sub.camera,
+ sub_music = sub.music,
+ sub_audio = sub.audio,
+ sub_flags = sub.flags,
+ sub_imagecount = sub.imagecount,
+ sub_scrseriesid = sub.scrseriesid,
+ sub_scrseriesepisode = sub.scrseriesepisode,
+ sub_scrmovieid = sub.scrmovieid,
+ sub_scrsp = sub.scrsp,
+ sub_episodecompname = sub.episodecompname,
+ sub_episodecompshortname = sub.episodecompshortname,
+ sub_episodecomppartname = sub.episodecomppartname,
+ epi_episodename = epi.episodename,
+ epi_shortname = epi.shortname,
+ epi_partname = epi.partname,
+ epi_lang = epi.lang,
+ epi_extracol1 = epi.extracol1,
+ epi_extracol2 = epi.extracol2,
+ epi_extracol3 = epi.extracol3,
+ epi_season = epi.season,
+ epi_part = epi.part,
+ epi_parts = epi.parts,
+ epi_number = epi.number;
+/*
+* remove hidden events
+*/
+delete from useevents where (cnt_source,cnt_channelid,cnt_eventid) in( select source,channelid,eventid from events where updflg in ('T','S','I') );
+/*
+* update lastrun
+*/
+update parameters set updsp = unix_timestamp(), value = startrun where owner = 'epgd' and name = 'mergeStart';
+END
diff --git a/configs/recording.py b/configs/recording.py
new file mode 100644
index 0000000..fedebb3
--- /dev/null
+++ b/configs/recording.py
@@ -0,0 +1,155 @@
+# ------------------------------------------------------------------------------------------# ----------------------------------------------------
+# - after importing 'event' the event object is available
+# with the following methods:
+#
+# title
+# shorttext
+# starttime
+# year
+# category
+# episodname
+# shortname
+# partname
+# season
+# part
+# number
+# extracol1
+# extracol2
+# extracol3
+#
+# namingmode -> timers naming mode { 1,2,3,4,5 } - see epgservice.h
+# tmplExpression
+#
+# - implement at least the function 'name()' witch have to return the name of the recording
+# if the result was empty VDRs typical recording name will be used
+# ------------------------------------------------------------------------------------------
+
+import event
+
+def name():
+
+ import locale
+ locale.setlocale(locale.LC_ALL, 'de_DE.UTF-8')
+ import time
+
+ if event.namingmode() == 1: # Auto
+
+ if event.episodname():
+ if event.shortname():
+ title = event.shortname()
+ else:
+ title = event.episodname()
+
+ part = "%02i" % int(event.part())
+ number = "%03i" % int(event.number())
+
+ if event.title() == 'Tatort':
+ if event.extracol1():
+ ermittler = event.extracol1()[11:]
+ else:
+ ermittler = ''
+
+ if event.extracol2():
+ ort = event.extracol2()[5:]
+ else:
+ ort = ''
+
+ season = str(int(event.season())+1969)
+ return "Tatort~" + ort + "~" + ermittler + "~" + season + "x" + part + " - " + number + ". " + event.shorttext()
+
+ else:
+ if event.title() == 'Polizeiruf 110':
+ season = str(int(event.season())+1971)
+ else:
+ season = "%02i" % int(event.season())
+
+ return title + "~" + season + "x" + part + " - " + number + ". " + event.shorttext()
+
+ elif event.category() == 'Serie':
+ if event.shorttext() and event.title() != event.shorttext():
+ return event.title() + "~" + event.shorttext()
+ else:
+ return event.title() + "~" + time.strftime("%a %d.%m.%Y %H:%M", time.localtime(event.starttime()))
+ else:
+ return event.title()
+
+
+ elif event.namingmode() == 2: # Constable
+
+ if event.episodname():
+ if event.shortname():
+ title = event.shortname()
+ else:
+ title = event.episodname()
+
+ part = "%02i" % int(event.part())
+ number = "%03i" % int(event.number())
+
+ if event.title() == 'Tatort':
+ if event.extracol1:
+ ermittler = event.extracol1()[11:]
+ else:
+ ermittler = ''
+
+ if event.extracol2():
+ ort = event.extracol2()[5:]
+ else:
+ ort = ''
+
+ season = str(int(event.season())+1969)
+ return "Tatort~" + ort + "~" + ermittler + "~" + season + "x" + part + " - " + number + ". " + event.shorttext()
+
+ else:
+ if event.title() == 'Polizeiruf 110':
+ season = str(int(event.season())+1971)
+ else:
+ season = "%02i" % int(event.season())
+
+ return title + "~" + season + "x" + part + " - " + number + ". " + event.shorttext()
+
+ elif event.shorttext() and event.title() != event.shorttext():
+ return event.title() + "~?x? - ?. " + event.shorttext()
+ else:
+ return event.title() + "~" + time.strftime("%a %d.%m.%Y %H:%M", time.localtime(event.starttime()))
+
+ elif event.namingmode() == 3: # Serie
+ if event.shorttext() and event.title() != event.shorttext():
+ return event.title() + "~" + event.shorttext()
+ else:
+ return event.title()
+
+ elif event.namingmode() == 4: # Kategorisiert
+ if event.category():
+ return event.category() + "~" + event.title()
+ else:
+ return event.title()
+
+ elif event.namingmode() == 5: # User (has to be defined/configured by User
+ return event.title()
+
+ elif event.namingmode() == 6:
+ if event.tmplExpression() == "":
+ return event.title()
+
+ recording = event.tmplExpression()
+ recording = recording.replace("%title%", event.title())
+ recording = recording.replace("%shorttext%", event.shorttext())
+ recording = recording.replace("%starttime%", time.strftime("%a %d.%m.%Y %H:%M", time.localtime(event.starttime())))
+ recording = recording.replace("%year%", event.year())
+ recording = recording.replace("%category%", event.category())
+ recording = recording.replace("%episodname%", event.episodname())
+ recording = recording.replace("%shortname%", event.shortname())
+ recording = recording.replace("%partname%", event.partname())
+ recording = recording.replace("%season%", "%02i" % int(event.season()))
+ recording = recording.replace("%part%", "%02i" % int(event.part()))
+ recording = recording.replace("%number%", "%02i" % int(event.number()))
+ recording = recording.replace("%extracol1%", event.extracol1())
+ recording = recording.replace("%extracol2%", event.extracol2())
+ recording = recording.replace("%extracol3%", event.extracol3())
+
+ recording = recording.replace("/", "~")
+
+ return recording
+
+ else:
+ return event.title()
diff --git a/configs/reverseepg.sql b/configs/reverseepg.sql
new file mode 100644
index 0000000..01653ff
--- /dev/null
+++ b/configs/reverseepg.sql
@@ -0,0 +1,42 @@
+CREATE PROCEDURE reverseepg ()
+BEGIN
+/*
+* channelmap zurücksetzen
+*/
+update channelmap set mergesp = 0;
+/*
+* events zurücksetzen Teil 1
+*/
+update
+ events ev,
+ (select distinct channelid from channelmap where source <> 'vdr' ) cm
+set
+ useid = masterid,
+ updflg =
+ case
+ when ev.delflg = 'Y' then 'D'
+ else 'I'
+ end
+where
+ ev.source = 'vdr' and
+ ev.channelid = cm.channelid;
+/*
+* events zurücksetzen Teil 2
+*/
+update
+ events ev,
+ (select distinct mergesp,merge,channelid,source from channelmap) cm
+set
+ useid = masterid,
+ updflg =
+ case
+ when ev.delflg = 'Y' then 'D'
+ when ev.source = 'vdr' then 'P'
+ when ev.source != 'vdr' and merge > 1 then 'S'
+ else 'A'
+ end
+where
+ ev.source = cm.source and
+ ev.channelid = cm.channelid;
+END
+
diff --git a/configs/thetvdbview.sql b/configs/thetvdbview.sql
new file mode 100644
index 0000000..d6b06af
--- /dev/null
+++ b/configs/thetvdbview.sql
@@ -0,0 +1,33 @@
+CREATE VIEW thetvdbview as
+select
+ ev.eventid,
+ case when ep.compname = 'TATORT' then concat('Tatort',replace(substring(ep.extracol1,11),' und ',' & ')) else ev.title end title,
+ ev.scrsp,
+ case when ep.compname = 'TATORT' then Null else ep.season end season,
+ case when ep.compname = 'TATORT' then Null else ep.part end part,
+ case when ep.compname = 'TATORT' then Null else ep.number end number,
+ ep.partname shorttext
+from
+ events ev, episodes ep
+where
+ ev.episodecompname = ep.compname and
+ ev.episodecomppartname = ep.comppartname and
+ ev.episodelang = ep.lang and
+ ev.scrsp is null and
+ updflg in('A','T','C','P')
+union
+select
+ eventid,
+ title,
+ scrsp,
+ case when substring(shorttext,1,1)='S' and substring(shorttext,2,1) REGEXP ('[0-9]') then TRIM(LEADING '0' from replace(SUBSTRING_INDEX(shorttext, 'E', 1),'S','')) end season,
+ case when substring(shorttext,1,1)='S' and substring(shorttext,2,1) REGEXP ('[0-9]') then TRIM(LEADING '0' from SUBSTRING_INDEX(shorttext, 'E', -1)) end part,
+ null number,
+ case when substring(shorttext,1,1)='S' and substring(shorttext,2,1) REGEXP ('[0-9]') then Null else shorttext end shorttext
+from
+ events
+where
+ episodecompname is Null
+ and scrsp is null
+ and updflg in('A','T','C','P')
+ and category = 'Serie';
generated by cgit v1.2.3 (git 2.25.1) at 2025-01-31 02:37:18 +0000