diff options
Diffstat (limited to 'scripts')
| -rwxr-xr-x | scripts/epgd-conflictsof | 31 | ||||
| -rwxr-xr-x | scripts/epgd-dropall | 156 | ||||
| -rwxr-xr-x | scripts/epgd-import-epglv | 10 | ||||
| -rwxr-xr-x | scripts/epgd-ls-channelids | 39 | ||||
| -rwxr-xr-x | scripts/epgd-sendmail | 6 | ||||
| -rwxr-xr-x | scripts/epgd-showdones | 5 | ||||
| -rwxr-xr-x | scripts/epgd-showmerge | 32 | ||||
| -rwxr-xr-x | scripts/epgd-showtimer | 18 | ||||
| -rwxr-xr-x | scripts/epgd-showtimerat | 14 | ||||
| -rwxr-xr-x | scripts/epgd-tool | 315 | ||||
| -rwxr-xr-x | scripts/epgh-login | 43 | ||||
| -rwxr-xr-x | scripts/epgh-request | 28 | ||||
| -rwxr-xr-x | scripts/epgsearchdone.pl | 140 |
13 files changed, 837 insertions, 0 deletions
diff --git a/scripts/epgd-conflictsof b/scripts/epgd-conflictsof new file mode 100755 index 0000000..e753b30 --- /dev/null +++ b/scripts/epgd-conflictsof @@ -0,0 +1,31 @@ + +export MYSQL_PWD=epg + +if [ -z $1 ]; then + exit +fi + +ID=$1 + +mysql -u epg2vdr -Depg2vdr -e " \ +select t.id, SUBSTRING_INDEX(t.channelid, '-', 3) as transponder, v.name as vdr, t.source, t.state, \ + t.eventid, from_unixtime(t.day, '%d.%m.%Y') as day, \ + concat_ws(':', right(concat("00", t.starttime DIV 100), 2), right(concat("00", t.starttime%100), 2)) as start, \ + concat_ws(':', right(concat("00", t.endtime DIV 100), 2), right(concat("00", t.endtime%100), 2)) as end, \ + e.title, e.shorttext, t.file \ + from \ + timers t left outer join events e on (t.eventid = e.masterid), vdrs v, \ + (select day, starttime, endtime, vdruuid from timers where id = $ID) ti \ + where \ + v.uuid = t.vdruuid \ + and t.state in ('P','R') \ + and t.active = 1 \ + and \ + ( (t.day + t.starttime div 100 * 60 * 60 + t.starttime % 100 * 60 >= ti.day + ti.starttime div 100 * 60 * 60 + ti.starttime % 100 * 60 \ + and t.day + t.starttime div 100 * 60 * 60 + t.starttime % 100 * 60 <= ti.day + ti.endtime div 100 * 60 * 60 + ti.endtime % 100 * 60) \ + or \ + (t.day + t.endtime div 100 * 60 * 60 + t.endtime % 100 * 60 >= ti.day + ti.starttime div 100 * 60 * 60 + ti.starttime % 100 * 60 \ + and t.day + t.endtime div 100 * 60 * 60 + t.endtime % 100 * 60 <= ti.day + ti.endtime div 100 * 60 * 60 + ti.endtime % 100 * 60) \ + ) \ + and t.vdruuid = ti.vdruuid \ + order by t.day, start;" diff --git a/scripts/epgd-dropall b/scripts/epgd-dropall new file mode 100755 index 0000000..2c93522 --- /dev/null +++ b/scripts/epgd-dropall @@ -0,0 +1,156 @@ +#!/bin/bash + +export MYSQL_PWD=epg + +echo "------------------------" +echo "-- Drop normal tables --" +echo "------------------------" +echo "This tables contain data which is restored automatically " +echo " by downloading from internet and via the dvb stream and from your vdrs" +echo -n "Are you sure? [y/N] " + +read -n 1 c +echo "" + +if [ "${c}" = "y" ]; then + + mysql -u epg2vdr -Depg2vdr -e 'drop table if exists events;' + mysql -u epg2vdr -Depg2vdr -e 'drop table if exists useevents;' + mysql -u epg2vdr -Depg2vdr -e 'drop table if exists fileref;' + mysql -u epg2vdr -Depg2vdr -e 'drop table if exists imagerefs;' + mysql -u epg2vdr -Depg2vdr -e 'drop table if exists channelmap;' + mysql -u epg2vdr -Depg2vdr -e 'drop table if exists episodes;' + mysql -u epg2vdr -Depg2vdr -e 'drop view if exists eventsview;' + mysql -u epg2vdr -Depg2vdr -e 'drop view if exists eventsviewplain;' + mysql -u epg2vdr -Depg2vdr -e 'drop table if exists components;' + mysql -u epg2vdr -Depg2vdr -e 'drop table if exists recordingdirs;' + mysql -u epg2vdr -Depg2vdr -e 'drop table if exists recordinglist;' + mysql -u epg2vdr -Depg2vdr -e 'drop table if exists vdrs;' + + mysql -u epg2vdr -Depg2vdr -e 'drop table if exists analyse;' + mysql -u epg2vdr -Depg2vdr -e 'drop table if exists snapshot;' + + echo "Now you have two choices for timers and timersdone:" + echo " 1. Automatic (without dropping the timers table):" + echo " All created timers will be marked for delete action to force VDRs to remove them" + echo " 2. Drop table timers - you have to cleanup the timers.conf of your VDRs by hand" + echo " " + echo "In both cases all still pending timers will removed from timersdone to force epgd to create it again!" + echo " " + echo "Proceed with 1 (otherwise 2 will done)? [y/N] " + + read -n 1 c + echo "" + + if [ "${c}" = "y" ]; then + mysql -u epg2vdr -Depg2vdr -e "update timers set action = 'D' where state in ('P', 'R');" + else + mysql -u epg2vdr -Depg2vdr -e 'drop table if exists timers;' + fi + + mysql -u epg2vdr -Depg2vdr -e "delete from timersdone where state in ('C', 'Q');" + + echo "" + echo "Tables dropped!" +fi + +echo "---------------------" +echo "-- Drop procedures --" +echo "---------------------" +echo -n "Are you sure? [y/N] " + +read -n 1 c +echo "" + +if [ "${c}" = "y" ]; then + mysql -u epg2vdr -Depg2vdr -e 'drop procedure if exists reverseepg;' + mysql -u epg2vdr -Depg2vdr -e 'drop procedure if exists mergeepg;' + + mysql -u epg2vdr -Depg2vdr -e 'drop function if exists getupdflg;' + mysql -u epg2vdr -Depg2vdr -e 'drop function if exists getcrosslvr;' + mysql -u epg2vdr -Depg2vdr -e 'drop function if exists getlvrmin;' + + echo "" + echo "Procedures and Functions dropped!" +fi + +echo "-------------------" +echo "-- Drop images --" +echo "-------------------" +echo "In most cases you do not need to drop the image table." +echo -n "Drop image tables? [y/N] " + +read -n 1 i +echo "" + +if [ "${i}" = "y" ]; then + mysql -u epg2vdr -Depg2vdr -e 'drop table if exists images;' + + echo "" + echo "Table images dropped!" +fi + +echo "----------------------" +echo "-- Scraper tables --" +echo "-----------------------" +echo "In most cases you don't need to drop the scarper tables!" +echo -n "Drop scraper tables? [y/N] " + +read -n 1 i +echo "" + +if [ "${i}" = "y" ]; then + mysql -u epg2vdr -Depg2vdr -e 'drop table if exists series;' + mysql -u epg2vdr -Depg2vdr -e 'drop table if exists series_episode;' + mysql -u epg2vdr -Depg2vdr -e 'drop table if exists series_media;' + mysql -u epg2vdr -Depg2vdr -e 'drop table if exists series_actor;' + mysql -u epg2vdr -Depg2vdr -e 'drop table if exists movie;' + mysql -u epg2vdr -Depg2vdr -e 'drop table if exists movie_actor;' + mysql -u epg2vdr -Depg2vdr -e 'drop table if exists movie_actors;' + mysql -u epg2vdr -Depg2vdr -e 'drop table if exists movie_media;' + mysql -u epg2vdr -Depg2vdr -e 'drop view if exists thetvdbview;' + + echo "" + echo "Scraper tables dropped!" +fi + + +echo "-----------------------------" +echo "-- Tables with User Data --" +echo "-----------------------------" +echo "In most cases you DON'T LIKE to drop tables with the USER data!" +echo "If you proceed in the next step you will to backup it" +echo -n "Drop tables with user data? [y/N] " + +read -n 1 i +echo "" + +if [ "${i}" = "y" ]; then + + echo -n "Backup User Data Tables now? [y/N] " + + read -n 1 i + echo "" + + if [ "${i}" = "y" ]; then + mysqldump --opt -u epg2vdr epg2vdr parameters | gzip > ./parameters-dump.sql.gz + echo "Table parameters dumped to ./parameters-dump.sql.gz" + + mysqldump --opt -u epg2vdr epg2vdr users | gzip > ./users-dump.sql.gz + echo "Table parameters dumped to ./users-dump.sql.gz" + + mysqldump --opt -u epg2vdr epg2vdr timersdone | gzip > ./timersdone-sump.sql.gz + echo "Table parameters dumped to ./timersdone-dump.sql.gz" + + mysqldump --opt -u epg2vdr epg2vdr searchtimers | gzip > ./searchtimers-sump.sql.gz + echo "Table parameters dumped to ./searchtimers-dump.sql.gz" + fi + + mysql -u epg2vdr -Depg2vdr -e 'drop table if exists parameters;' + mysql -u epg2vdr -Depg2vdr -e 'drop table if exists searchtimers;' + mysql -u epg2vdr -Depg2vdr -e 'drop table if exists timersdone;' + mysql -u epg2vdr -Depg2vdr -e 'drop table if exists users;' + + echo "" + echo "User Data tables dropped!" +fi diff --git a/scripts/epgd-import-epglv b/scripts/epgd-import-epglv new file mode 100755 index 0000000..2d9df0c --- /dev/null +++ b/scripts/epgd-import-epglv @@ -0,0 +1,10 @@ +#!/bin/bash + +if [ -z MYSQL_PWD ]; then + export MYSQL_PWD=epg +fi + +mysql -u root -e "DROP FUNCTION IF EXISTS epglv;" +mysql -u root -e "DROP FUNCTION IF EXISTS epglvr;" +mysql -u root -e "CREATE FUNCTION epglv RETURNS INT SONAME 'mysqlepglv.so';" +mysql -u root -e "CREATE FUNCTION epglvr RETURNS INT SONAME 'mysqlepglv.so';" diff --git a/scripts/epgd-ls-channelids b/scripts/epgd-ls-channelids new file mode 100755 index 0000000..e4f6f3e --- /dev/null +++ b/scripts/epgd-ls-channelids @@ -0,0 +1,39 @@ +#!/usr/bin/perl + +use strict; +use Getopt::Long; + + +if (($#ARGV!=0) || (! $ARGV[0])) +{ + print "Usage: $0 <channels.conf>\n"; + exit 1; +} + +open CHANNELS, $ARGV[0] + or die $!; + +my(@lines)=<CHANNELS>; + +foreach my $line (@lines) +{ + my @column=split(/:/, $line); + my @name=split(/;/, $column[0]); + + # These are the names and field numbers from channels.conf: + # + # Name: $column[0], Freq: $column[1], Param: $column[2], Src: $column[3], Symb: $column[4], + # VPID: $column[5], APID: $column[6], TPID: $column[7], CAID: $column[8], SID: $column[9], + # NID: $column[10], TID: $column[11], RID: $column[12] + + # make sure to print the "data lines" only: + + if (($column[3] =~ /^S/) || ($column[3] =~ /C/) || ($column[3] =~ /I/)) + { + print "$column[3]-$column[10]-$column[11]-$column[9] \/\/ $name[0]\n"; + } + + +} + +close(CHANNELS); diff --git a/scripts/epgd-sendmail b/scripts/epgd-sendmail new file mode 100755 index 0000000..64c90b9 --- /dev/null +++ b/scripts/epgd-sendmail @@ -0,0 +1,6 @@ +#!/bin/bash + +# ----------------------------------------- +# require: mail of 'GNU Mailutils' package + +echo "$2" | mail -s "$1" -a "Content-Type: $3; charset=UTF-8" $4 diff --git a/scripts/epgd-showdones b/scripts/epgd-showdones new file mode 100755 index 0000000..13dad7d --- /dev/null +++ b/scripts/epgd-showdones @@ -0,0 +1,5 @@ + +export MYSQL_PWD=epg + +mysql -u epg2vdr -Depg2vdr -e " \ + select from_unixtime(updsp, '%d.%m %H:%m') as upd, id, state as s, substr(title,1,35), shorttext, episodecompname, episodecomppartname, expression from timersdone where state != 'D';" diff --git a/scripts/epgd-showmerge b/scripts/epgd-showmerge new file mode 100755 index 0000000..1dc022a --- /dev/null +++ b/scripts/epgd-showmerge @@ -0,0 +1,32 @@ +export MYSQL_PWD=epg + +if [ "$1" == "-h" ]; then + + echo " Usage: $0 [<channelid>] [<to-date>]" + echo " (without channelid the merge state will shown)" + echo "" + echo " Example: $0 S19.2E-1-1010-11150 \"2013-10-01 16:00\"" + + exit 0 +fi + +mysql -u epg2vdr -Depg2vdr -e "select from_unixtime(lastmerge) as 'las merge was at' from vdrs where uuid = 'epgd';" +mysql -u epg2vdr -Depg2vdr -e "select count(1) as 'events pending for merge' from events where source = 'vdr' and updsp > (select lastmerge from vdrs where uuid = 'epgd');" +mysql -u epg2vdr -Depg2vdr -e "select count(source),source from events group by source;" + +if [ -z "$1" ]; then + + mysql -u epg2vdr -Depg2vdr -e "select count(*),source,updflg from events where starttime+duration >= unix_timestamp() group by source,updflg;" + +else + + if [ -z "$2" ]; then + to=`date +%Y-%m-%d -d "+ 1 day"` + else + to=$2 + fi + + echo "channel $1" + mysql -u epg2vdr -Depg2vdr -e "select source,masterid,useid,updflg,delflg,from_unixtime(starttime) starttime,round(duration/60,0) duration,substr(title,1,45) title,from_unixtime(updsp) updsp from events where channelid = '$1' and starttime+duration >= unix_timestamp() order by starttime,source desc limit 50;" + +fi diff --git a/scripts/epgd-showtimer b/scripts/epgd-showtimer new file mode 100755 index 0000000..be18de6 --- /dev/null +++ b/scripts/epgd-showtimer @@ -0,0 +1,18 @@ +export MYSQL_PWD=epg + +if [ -z $1 ]; then + STATE="(t.state != 'D' or t.state is null)" +else + STATE="t.state like '$1'" +fi + +mysql -u epg2vdr -Depg2vdr -e " \ +select from_unixtime(t.inssp, '%d.%m %H:%i') as ins, v.name, substr(t.source, 25) as source, t.id, t.autotimerid as aid, t.state as S, t.info, t.action as A, \ + t.eventid, concat(from_unixtime(day, '%d.%m '), concat_ws(':', right(concat("00", t.starttime DIV 100), 2), right(concat("00", t.starttime%100), 2))) as start, \ + concat_ws(':', right(concat("00", t.endtime DIV 100), 2), right(concat("00", t.endtime%100), 2)) as end, \ + e.title, e.shorttext, t.file \ + from \ + timers t left outer join events e on (t.eventid = e.masterid), vdrs v \ + where \ + v.uuid = t.vdruuid and $STATE \ + order by t.day, start;" diff --git a/scripts/epgd-showtimerat b/scripts/epgd-showtimerat new file mode 100755 index 0000000..de1d8f3 --- /dev/null +++ b/scripts/epgd-showtimerat @@ -0,0 +1,14 @@ + +export MYSQL_PWD=epg + +mysql -u epg2vdr -Depg2vdr -e " \ +select t.id, SUBSTRING_INDEX(t.channelid, '-', 3) as transponder, v.name, t.source, t.channelid, t.state as S, \ + from_unixtime(t.day + t.starttime div 100 * 60 * 60 + t.starttime % 100 * 60) as start, \ + from_unixtime(t.day + t.endtime div 100 * 60 * 60 + t.endtime % 100 * 60) as end \ + from \ + timers t left outer join events e on (t.eventid = e.masterid), vdrs v \ + where \ + v.uuid = t.vdruuid and t.active = 1 and t.state in ('P','R') \ + and ('$1' between from_unixtime(t.day + t.starttime div 100 * 60 * 60 + t.starttime % 100 * 60) and from_unixtime(t.day + t.endtime div 100 * 60 * 60 + t.endtime % 100 * 60)) \ + and v.name = '$2' \ + order by t.day, start;" diff --git a/scripts/epgd-tool b/scripts/epgd-tool new file mode 100755 index 0000000..11987a9 --- /dev/null +++ b/scripts/epgd-tool @@ -0,0 +1,315 @@ +#!/bin/bash + + +# MySQL helper script by 3PO +# Update 26.02.2017 + + +PASSWORD="" +HOST="" +MY_CNF="/etc/mysql/my.cnf" +MY_DB_SV="mysqld" +TMPFILE="/tmp/.epgtool" + +DISTRI="$(grep "^NAME=" /etc/os-release |cut -d "=" -f2 | sed -e "s/\"//g")" +RELEASE="$(grep "^VERSION_ID=" /etc/os-release |cut -d "=" -f2 | sed -e "s/\"//g")" + +if [ "$DISTRI" == Fedora ] ; then + MY_CNF="/etc/my.cnf" + if [ "$RELEASE" -gt 21 ] ; then + MY_DB_SV="mariadb" + fi +fi + +[ "$HOST" != "" ] && HOST="-h $HOST" + +rc_start () +{ + MYSQL_START="/etc/init.d/mysql start" + MYSQL_RESTART="/etc/init.d/mysql restart" + EPGD_START="/etc/init.d/epgd start" + EPGD_STOP="/etc/init.d/epgd stop" + EPGD_RESTART="/etc/init.d/epgd restart" +} + +systemd_start () +{ + MYSQL_START="systemctl start "$MY_DB_SV"" + MYSQL_RESTART="systemctl restart "$MY_DB_SV"" + EPGD_START="systemctl epgd start" + EPGD_STOP="systemctl epgd stop" + EPGD_RESTART="systemctl epgd restart" +} + +upstart_start () +{ + MYSQL_START="/sbin/start mysql" + MYSQL_RESTART="/sbin/restart mysql" + EPGD_START="/sbin/start epgd" + EPGD_STOP="/sbin/stop epgd" + EPGD_RESTART="/sbin/restart epgd" +} + +chkRootPwd () +{ +[ -f ~/.ssh/mysqlpasswd ] && . ~/.ssh/mysqlpasswd +if [ ! $PASSWORD ] ; then + echo "root Password for MySQL Database is not set!" + echo "Exit" + exit +else + . ~/.ssh/mysqlpasswd + SQL_ROOT="mysql -u root -e " +fi + +if [ "$PASSWORD" != "" ] ; then + export MYSQL_PWD="$PASSWORD" + SQL_ROOT="mysql -u root -e " +fi +} + +if [ "$(ps -p 1 -o comm=)" == 'systemd' ] ; then + systemd_start + elif [ -d /usr/lib/upstart ] ; then + upstart_start +elif which rc > /dev/null 2>&1 ; then + rc_start + fi + +PID_DB="$(pidof mysqld | wc -l)" +PID_EPGD="$(pidof epgd | wc -l)" +[ "$(pidof epgd)" != "" ] && EPGD_RUN="1" +[ "$(pidof mysqld)" != "" ] && MYSQL_RUN="1" +SQL_EPG2VDR="mysql $HOST -u epg2vdr -D epg2vdr -e " + +EPGD_EXIST="$(which epgd |wc -l)" +[ "$EPGD_EXIST" -eq 0 ] && PID_EPGD="0" && EPGD_RUN="0" + + + +DEL_DB () +{ +[ "$PID_DB" -lt 1 ] && $MYSQL_START +[ "$PID_EPGD" -ne 0 ] && $EPGD_STOP +chkRootPwd +$SQL_ROOT "DROP DATABASE epg2vdr;" +} + +DEL_U () +{ +[ "$PID_DB" -lt 1 ] && $MYSQL_START +[ "$PID_EPGD" -ne 0 ] && $EPGD_STOP +chkRootPwd +$SQL_ROOT "DROP USER epg2vdr;" +} + +DROP_PICS () +{ +export MYSQL_PWD=epg +$SQL_EPG2VDR "DROP TABLE images;" +} + +DEL_ALL () +{ +[ "$PID_DB" -lt 1 ] && $MYSQL_START +[ "$PID_EPGD" -ne 0 ] && $EPGD_STOP +chkRootPwd +$SQL_ROOT "DROP DATABASE epg2vdr;" +$SQL_ROOT "DROP USER epg2vdr;" +} + +DROPVIEW () +{ +[ "$PID_DB" -lt 1 ] && $MYSQL_START +[ "$PID_EPGD" -ne 0 ] && $EPGD_STOP +export MYSQL_PWD=epg +$SQL_EPG2VDR "DROP VIEW eventsview;" +[ "$EPGD_RUN" == 1 ] && $EPGD_START +} + +DROP_ALL () +{ +[ "$PID_DB" -lt 1 ] && $MYSQL_START +[ "$PID_EPGD" -ne 0 ] && $EPGD_STOP + +export MYSQL_PWD=epg +$SQL_EPG2VDR "SHOW TABLES" |grep -v images > $TMPFILE + +while read TABLE; do + $SQL_EPG2VDR "DROP TABLE IF EXISTS $TABLE;" +done < "$TMPFILE" + +rm -f $TMPFILE + +[ "$EPGD_RUN" == 1 ] && $EPGD_START +CASE="DROP_PICS" +echo -e "\nDelete all EPG Pictures from epg2vdr Database?\n" +YES_NO +} + +YES_NO () +{ +LOOP=0 +while [ $LOOP -eq 0 ] +do + echo -en 'Are You sure? [yes/no]: ' + read CHOICE + echo -en "\n" + case $CHOICE in + + [yY][eE][sS]|[yY]) + $CASE + LOOP=1 ;; + + [nN][oO]|[nN]) + echo -e "\nScript aborted by User\n" + exit ;; + + *) echo "Please enter \"yes\" or \"no\"." + LOOP=0;; + + esac +done +} + + +case $1 in + + -new-db|new-db) + [ "$PID_DB" -lt 1 ] && $MYSQL_START + chkRootPwd + $SQL_ROOT "CREATE DATABASE epg2vdr charset utf8;" + $SQL_ROOT "DROP FUNCTION IF EXISTS epglv;" + $SQL_ROOT "DROP FUNCTION IF EXISTS epglvr;" + $SQL_ROOT "CREATE FUNCTION epglv RETURNS INT SONAME 'mysqlepglv.so';" + $SQL_ROOT "CREATE FUNCTION epglvr RETURNS INT SONAME 'mysqlepglv.so';" + exit + ;; + + -new-u|new-u) + [ "$PID_DB" -lt 1 ] && $MYSQL_START + chkRootPwd + $SQL_ROOT "CREATE USER 'epg2vdr'@'%' IDENTIFIED BY 'epg';" + $SQL_ROOT "GRANT ALL PRIVILEGES ON epg2vdr.* TO 'epg2vdr'@'%' IDENTIFIED BY 'epg';" + $SQL_ROOT "GRANT ALL PRIVILEGES ON epg2vdr.* TO 'epg2vdr'@'localhost' IDENTIFIED BY 'epg';" + $SQL_ROOT "FLUSH PRIVILEGES;" + exit + ;; + + -del-db|del-db) + echo -e "\nDelete the complete epg2vdr Database?\n" + CASE="DEL_DB" + YES_NO + ;; + + -del-u|del-u) + echo -e "\nDelete the User epg2vdr?\n" + CASE="DEL_U" + YES_NO + ;; + + -del-all|del-all) + echo -e "\nDelete the complete epg2vdr Database and User epg2vdr?\n" + CASE="DEL_ALL" + YES_NO + ;; + + -show|show) + [ "$PID_DB" -lt 1 ] && $MYSQL_START + chkRootPwd + $SQL_ROOT "SELECT User,Host,Db FROM mysql.db;" + $SQL_ROOT "SHOW GRANTS FOR epg2vdr;" + $SQL_ROOT "SHOW DATABASES;" + export MYSQL_PWD=epg + $SQL_EPG2VDR "SHOW TABLES;" + exit + ;; + + -fix-cnf|fix-cnf) + if [ ! -f $MY_CNF ] ; then + echo "$MY_CNF does not exist" + echo "Exit" + exit + else + chkRootPwd + $SQL_ROOT "Flush LOGS;" + $SQL_ROOT "RESET MASTER;" + sed -i $MY_CNF -e "s/^bind-address/# bind-address/" + sed -i $MY_CNF -e "s/^log-bin/# log-bin/" + [ "$MYSQL_RUN" == "1" ] && $MYSQL_RESTART + [ "$EPGD_RUN" == "1" ] && $EPGD_RESTART + fi + exit + ;; + + -dropview|dropview) + echo -e "\nDrop all Views form database?\n" + CASE="DROPVIEW" + YES_NO + ;; + + -drop-all|drop-all) + echo -e "\nClean the complete epg2vdr Database?\n" + CASE="DROP_ALL" + YES_NO + ;; + + -check-events|check-events) + export MYSQL_PWD=epg + $SQL_EPG2VDR "SELECT count(1) AS Events, source FROM events group by source;" + exit + ;; + + -check-pic|check-pic) + export MYSQL_PWD=epg + $SQL_EPG2VDR "SELECT count(1) AS Pictures FROM images WHERE image IS NOT NULL;" + exit + ;; + + -show-stats|show-stats) + export MYSQL_PWD=epg + $SQL_EPG2VDR "SELECT version, master, ip, state, FROM_UNIXTIME(updsp,'%D %M %Y% %H:%i:%s') AS 'last touch', FROM_UNIXTIME(lastupd, '%D %M %Y% %H:%i:%s') AS 'last download', FROM_UNIXTIME(nextupd,'%D %M %Y% %H:%i:%s') AS 'next download' FROM vdrs ;" + exit + ;; + + -show-size|show-size) + export MYSQL_PWD=epg + $SQL_EPG2VDR "SELECT table_schema AS 'Database', ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size in MB' FROM information_schema.TABLES GROUP BY table_schema;" + echo "" + $SQL_EPG2VDR "SELECT table_name AS 'Tables', ROUND(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema = \"epg2vdr\" ORDER BY (data_length + index_length) DESC;" + exit + ;; + + -drop-pics|drop-pics) + echo -e "\nDelete all EPG Pictures from epg2vdr Database?\n" + CASE="DROP_PICS" + YES_NO + ;; + + *) + echo "" + echo " MySQL helper script by 3PO" + echo "" + echo " usage: [-new-db] [-new-u] [-del-db] [-del-u] [ -del-all] [-show] [-fix-cnf] [-dropview] [-drop-all] [-drop-pics] [-check-events] [-check-pic] [-show-stats] [-show-size]" + echo "" + echo " -new-db Create new Database for epg2vdr" + echo " -new-u Create new User for epg2vdr" + echo " -del-db Delete epg2vdr Database" + echo " -del-u Delete User epg2vdr" + echo " -del-all Delete epg2vdr Database and User epg2vdr" + echo " -show Shows GRANTS for epg2vdr and list Databases and Users" + echo " -fix-cnf Disable \"bind-address\" in $MY_CNF" + echo " -dropview Drop Table \"eventsview\" in Database" + echo " -drop-all Drop all Tables in epg2vdr Database" + echo " -drop-pics Delete all Pictures from epg2vdr Database" + echo " -check-events Shows quantity of Events in Database imported per source" + echo " -check-pic Shows quantity of EPG Pictures stored in Database" + echo " -show-stats Shows the Stats and Versions of EPGd and the connected VDRs" + echo " -show-size Shows the Size of the Tables in the epg2vdr Database" + echo "" + exit + ;; + +esac + + diff --git a/scripts/epgh-login b/scripts/epgh-login new file mode 100755 index 0000000..b0a8061 --- /dev/null +++ b/scripts/epgh-login @@ -0,0 +1,43 @@ + +if [ -z "$1" ]; then + _USER=nologin +else + _USER="$1" +fi + +if [ -z "$2" ]; then + _PASSWD=nologin +else + _PASSWD="$2" +fi + +if [ -z "$EPGD_HOST" ]; then + EPGD_HOST=localhost +fi + +if [ -z "$EPGD_PORT" ]; then + EPGD_PORT=9999 +fi + +if [ "$_USER" == "logout" ]; then + wget -q -O - $EPGD_HOST:$EPGD_PORT/sid$EPG_HTTPD_SESSION/data/login --post-data="{ \"key\" : \"\" }" > /dev/null + # echo "logout succeeded" + exit +fi + +KEY=`echo -n $_USER$_PASSWD | md5sum | sed s/" .*$"/""/g` +RESULT=`wget -q -O - $EPGD_HOST:$EPGD_PORT/data/login --post-data="{ \"key\" : \"$KEY\" }"` + +if [ ! -z "$RESULT" ]; then + EPG_HTTPD_SESSION=`echo $RESULT | json_pp | grep session | sed s/".*: ."/""/ | sed s/"\".*"/""/` +fi + +IS_JSON=`expr index "$RESULT" "{"` + +if [ ! -z $EPG_HTTPD_SESSION ] && [ $IS_JSON -ne 0 ]; then + export EPGD_PORT EPGD_HOST + export EPG_HTTPD_SESSION + echo "login succeeded, session is \"$EPG_HTTPD_SESSION\"" +else + echo "Error: Login at $EPGD_HOST/$EPGD_PORT failed!" +fi diff --git a/scripts/epgh-request b/scripts/epgh-request new file mode 100755 index 0000000..ad6a72f --- /dev/null +++ b/scripts/epgh-request @@ -0,0 +1,28 @@ + +if [ -z "$1" ]; then + echo "Usage: epgh-request <request> [ <user> <passwd> ]" + exit +fi + +#--------- +# login + +. epgh-login "$2" "$3" + +#--------- +# work + +# RES=$(wget -q -O - "$EPGD_HOST:$EPGD_PORT/sid$EPG_HTTPD_SESSION/data/$1") + +RES=$(curl -s "$EPGD_HOST:$EPGD_PORT/sid$EPG_HTTPD_SESSION/data/$1") + +echo $RES | json_pp + +if [ $? -ne 0 ]; then + echo "Got: $RES" +fi + +#--------- +# logout + +epgh-login logout diff --git a/scripts/epgsearchdone.pl b/scripts/epgsearchdone.pl new file mode 100755 index 0000000..2e1d52a --- /dev/null +++ b/scripts/epgsearchdone.pl @@ -0,0 +1,140 @@ +#!/usr/bin/perl -w + +if (not defined $ARGV[0] or not defined $ARGV[1]) {die "No parameter given!\n"}; + +use DBI; + +$db_user = "epg2vdr"; +$db_password = "epg"; + +my $database_name = "epg2vdr"; +my $location = "localhost"; +my $port_num = "3306"; +my $database = "DBI:mysql:$database_name:$location:$port_num"; + +# Einloggen: +$dbh = DBI->connect($database,$db_user,$db_password, { mysql_enable_utf8 => 1 }); + +if ( $ARGV[1] eq 'loaddone') { + +$sql = "INSERT INTO timersdone (inssp,updsp,source,state,channelid,starttime,duration,title,comptitle,shorttext,compshorttext,longdescription,complongdescription,channelname,expression,aux) VALUES (UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), 'epgsearch', 'R', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; + +$sth = $dbh->prepare($sql); +die "Error: $DBI::errstr\n" if $DBI::err; + +$channelid = undef; +$starttime = undef; +$duration = undef; +$title = undef; +$comptitle = undef; +$shorttext = undef; +$compshorttext = undef; +$complongdescription = undef; +$longdescription = undef; +$channelname = undef; +$expression = undef; +$aux = undef; + +open (FILE, "<$ARGV[0]") or die "Can't open file\n"; +while (<FILE>) { + $line = $_; + $line =~ s/\n//; + + if ($line =~ /^R/ ) { + @fields = split(' ', $line); + $starttime = $fields[1]; + $duration = $fields[2]; + } + + if ($line =~ /^C/ ) { $channelid = substr($line,2); } + if ($line =~ /^T/ ) { $title = substr($line,2); $comptitle = uc($title); $comptitle =~ tr/äöü/ÄÖÜ/; $comptitle =~ s/[^0-9A-ZÄÖÜß]//ug; } + if ($line =~ /^S/ ) { $shorttext = substr($line,2); $compshorttext = uc($shorttext) ; $compshorttext =~ tr/äöüéá/ÄÖÜÉÁ/; $compshorttext =~ s/[^0-9A-ZÄÖÜß]//ug; } + if ($line =~ /^D/ ) { $longdescription = substr($line,2); $complongdescription = uc($longdescription); $complongdescription =~ tr/äöü/ÄÖÜ/; $complongdescription =~ s/[^0-9A-ZÄÖÜß]//ug; } + if ($line =~ /^@/ ) { + $aux = substr($line,2); + $aux =~ /<[C|c]hannel>[0-9]* - (.*)<\/[C|c]hannel>.*timer>(.*)<\/[S|s]earch/; + $channelname = $1; + $expression = $2; + } + + if ($line =~ /^r/ ) { + + $sth->execute($channelid,$starttime,$duration,$title,$comptitle,$shorttext,$compshorttext,$longdescription,$complongdescription,$channelname,$expression,$aux); + + $channelid = undef; + $starttime = undef; + $duration = undef; + $title = undef; + $comptitle = undef; + $shorttext = undef; + $compshorttext = undef; + $longdescription = undef; + $complongdescription = undef; + $channelname = undef; + $expression = undef; + $aux = undef; + } + +} +close(FILE) or die "Can't close file\n"; + +} + +elsif ( $ARGV[1] eq 'loaddump') { + +$sql = "INSERT INTO timersdone (id, inssp, updsp, source, state ,timerid, autotimerid, title, comptitle, shorttext, compshorttext, longdescription, complongdescription, episodecompname, episodecompshortname, episodecomppartname, episodelang, episodeseason, episodepart, channelid, channelname, expression, starttime, duration, aux) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; + +$sth = $dbh->prepare($sql); +die "Error: $DBI::errstr\n" if $DBI::err; + +open (FILE, "<$ARGV[0]") or die "Can't open file\n"; +while (<FILE>) { + $line = $_; + $line =~ s/\n//; + @fields = split('\t', $line); + + + for ($i=0; $i<=24; $i++) { if ($fields[$i] eq '') { $fields[$i] = undef; } } + + $sth->execute($fields[0],$fields[1],$fields[2],$fields[3],$fields[4],$fields[5],$fields[6],$fields[7],$fields[8],$fields[9],$fields[10],$fields[11],$fields[12],$fields[13],$fields[14],$fields[15],$fields[16],$fields[17],$fields[18],$fields[19],$fields[20],$fields[21],$fields[22],$fields[23],$fields[24]); + +} +close(FILE) or die "Can't close file\n"; + +} + + +elsif ( $ARGV[1] eq 'writedump') { + +use open ':utf8'; + +open FILE, ">$ARGV[0]" or die "Can't open file!\n"; + +# select finished recordings +$sql = "SELECT id, inssp, updsp, source, state, timerid, autotimerid, title, comptitle, shorttext, compshorttext, longdescription, complongdescription, episodecompname, episodecompshortname, episodecomppartname, episodelang, episodeseason, episodepart, channelid, channelname, expression, starttime, duration, aux FROM timersdone where state = 'R' order by comptitle,compshorttext"; + +$sth = $dbh->prepare($sql); +die "Error: $DBI::errstr\n" if $DBI::err; +$sth->execute(); +die "Error: $DBI::errstr\n" if $DBI::err; + +$sth->rows; + +$arrayref = $sth->fetchall_arrayref; +for (@$arrayref) { + @fields = @$_; + + foreach $field (@fields) { if (not defined $field) { $field = '';} } + + print FILE join("\t",@fields), "\n"; + +} + + +$sth->finish(); +close(FILE) or die "Can't close file\n"; + +} + +#Ausloggen +$dbh->disconnect; |