Ich verwende EPGd und EPG2VDR um den VDR mit EPG-Infos zu füllen.
Was mir allerdings fehlt sind die Angaben zur Sendungslänge, so wie sie im DVB-EPG meistens an die Berschreibung angehänt ist.
Meine eventsview.sql:
- 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,
- case when sub_longdescription is Null then
- cnt_longdescription
- else
- sub_longdescription
- end longdescription,
- 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 mergesource,
- 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('||','Moderation: ',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('|','Produktion: ',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('|','Folgentitel: ',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('|','Episode: ',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('||','EPG: ',upper(replace(cnt_source,'vdr','dvb')),'/',upper(sub_source)) else concat('||','EPG: ',upper(replace(cnt_source,'vdr','dvb'))) end
- )
- )
- ,'|', '
- ') as description
- from
- useevents;
Display More
Leider kenn ich mch mit dem sql-kram nicht aus.
Ich hätte gerne die Sendungslänge (ohne Werbung) an die Beschreibung angehängt (54 Min.)
Die eventsview-plain:
- 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');
Display More
Das müsste doch machbar sein?
