mythtv at kosowsky
Jan 12, 2010, 10:39 PM
Post #2 of 8
(889 views)
Permalink
|
|
Re: UPDATED: Bash/MySQL script for copying and/or deleting mythfrontend profiles
[In reply to]
|
|
Matt Beadon wrote at about 13:14:02 -0800 on Tuesday, January 12, 2010:
> >
> > BTW it works like a charm and now I can see my myth recordings on my
> > XP laptop -- woohooo....
> >
> > Here is an updated version of the code with some minor usability
> > enhancements:
> >
>
> Great timing (for me)! I just got my new laptop running last night and
> fired up myth FE only to realize that I'd have to spend ages getting the
> settings cloned from my existing FE before I'd be happy using it. :( Then I
> found this thread, thanks! Haven't run it yet but I will when I get home.
>
> I'm interested in the delete hostname function since I happen to have a few
> leftover invalid entries in my DB from changing my hostname without doing it
> the "mythtv way".
>
> I'm not very familiar with mysql but I'd like to make the following
> suggestions that I think would go really nicely with the current functions:
> 1) list all hostnames that have some configuration info in the DB. This
> will let people like me clean up after their mistakes more easily using your
> delete hostname command. :)
Here is an updated version adding your #1:
--------------------------------------------------------------------
# !/bin/bash
#mythfrontendprofiledup
# Version 0.2, January 2010
#Jeff Kosowsky (with MUCH help from Ronald Frazier)
#Script to automatically copy and/or delete mythfrontend profiles.
#
# Note the program operates on the following tables:
# settings, keybindings, jumppoints, displayprofiles, displayprofilegroups
##############################################################################
#Default values
MYTHDB=mythconverg
USER=mythtv
PASSWD= #Blank means it will query
#############################################################################
function usage () {
cat <<EOF
Copy (or delete) a frontend from the mythtv database
Usage: ${0##*/} [options] OLDHOSTNAME NEWHOSTNAME
Copy over frontend settings from OLDHOSTNAME to NEWHOSTNAME
(note existing entries for NEWHOSTNAME are deleted first)
${0##*/} -d|--deleteonly [options] HOSTNAME
Delete HOSTNAME frontend from database
${0##*/} -f|--frontends
Show all frontend hosts listed in database
${0##*/} -h|--help
Display this usage message
where options are:
--mythdb|-m MYTHDB Use MYTHDB database [default is to use 'mythconverg']
--nobackup|-n Don't backup database to MYTHDB.<date>.sql
before editing database [default is to backup]
--passwd|-p PASSWD Use password PASSWD [Default is to query for password]
--user|-u USER Use user USER [default is 'mythtv']
Note: program will not delete the last mythfrontend profile nor will it make
any changes to NEWHOSTNAME unless OLDHOSTNAME exists.
EOF
}
ALLHOSTS="((select hostname from settings)
union (select hostname from keybindings)
union (select hostname from jumppoints)
union (select hostname from displayprofilegroups)) as HOSTS"
function display_hostnames ()
{
mysql --skip-column-names -u $USER -p$PASSWD $MYTHDB < <(cat <<EOF
select * from $ALLHOSTS where hostname != "NULL";
EOF
)
}
############################################################################
SHORT_OPTIONS="d,f,h,m:,n,p:,u:"
LONG_OPTIONS="deleteonly,frontends,help,mythdb:,nobackup,passwd:,user:"
PARSED_OPTIONS=$(getopt -n "${0##*/}" -o $SHORT_OPTIONS -l $LONG_OPTIONS -- "$@")
OPTIONS_RET=$?
eval set -- "$PARSED_OPTIONS"
# Parsing error or no flags
if [ $OPTIONS_RET -ne 0 ] || [ $# -le 0 ]; then
usage
exit 1
fi
while [ $# -ge 1 ]; do
case $1 in
--deleteonly | -d) deleteonly=1 ;;
--frontends | -f) shift; SHOWFRONTENDS=1;;
--help | -h) usage; exit 0 ;;
--mythdb | -m) shift; MYTHDB="$1" ;;
--nobackup | -n) nobackup=1 ;;
--passwd | -p) shift; PASSWD="$1" ;;
--user | -u) shift; USER="$1" ;;
-- ) shift; break;;
* ) echo "ERROR: unknown flag $1"; usage; exit 1;;
esac
shift
done
OLDHOST=$1
NEWHOST=$2
if [ -n "$SHOWFRONTENDS" ] ; then
if [ $# -eq 0 ] ; then
display_hostnames
exit 0
else
usage
exit 1
fi
fi
if [ -n "$deleteonly" -a $# -ne 1 ] || [ -z "$deleteonly" -a $# -ne 2 ] ; then
usage
exit 1
fi
if [ "$OLDHOST" = "$NEWHOST" ] ; then
echo "Error: NEWHOSTNAME can't equal OLDHOSTNAME"
exit 2
fi
if [ -z "$nobackup" ] ; then
backup=$MYTHDB-`date "+%m%d%y.%H%M%S"`.sql
echo "** Backing up myth database to: $backup"
mysqldump --order-by-primary -u $USER -p$PASSWD $MYTHDB > $backup
fi
if [ -n "$deleteonly" ] ; then
NEWHOST=$OLDHOST
fi
#########################################################################
# Inject mysql queries
mysql --skip-column-names -u $USER -p$PASSWD $MYTHDB < <( sed "s/<OLDHOSTNAME>/$OLDHOST/g;s/<NEWHOSTNAME>/$NEWHOST/g" <<EOF
-- DELETE:
-- Calculate number of hosts left other than NEWHOSTNAME (the one changing)
set @numotherhosts := (select count(distinct hostname) from
displayprofilegroups where hostname != '<NEWHOSTNAME>');
-- Check if OLDHOSTNAME exists in any of the relevant tables
set @oldhostexists :=
(select count(*) from $ALLHOSTS where hostname = '<OLDHOSTNAME>');
-- Only do deletions and insertions if:
-- @doit = @numotherhosts * @oldhostexists > 0
set @doit := @oldhostexists * @numotherhosts;
select if(@oldhostexists > 0, if(@numotherhosts > 0,
"** Deleting any frontend settings for '<NEWHOSTNAME>'...",
"Error: No other hosts left. Aborting deletions..."),
"Error: '<OLDHOSTNAME>' doesn't exist. Aborting all database changes...");
-- First delete entries for <NEWHOSTNAME> from the following tables:
-- settings, keybindings, jumppoints
DELETE FROM settings WHERE hostname ='<NEWHOSTNAME>' AND @doit > 0;
DELETE FROM keybindings WHERE hostname ='<NEWHOSTNAME>' AND @doit > 0;
DELETE FROM jumppoints WHERE hostname ='<NEWHOSTNAME>' AND @doit > 0;
-- Second Delete any existing entries and profiles corresponding to
-- NEWHOSTNAME from displayprofiles and displayprofilegroups tables
-- NOTE: We assume that the profilegroupids are unique to each hostname
-- (and they will be if you use this script to copy frontend settings)
DELETE FROM displayprofiles WHERE profilegroupid IN (SELECT profilegroupid
FROM displayprofilegroups WHERE hostname = '<NEWHOSTNAME>') AND @doit > 0;
DELETE FROM displayprofilegroups WHERE hostname = '<NEWHOSTNAME>' AND @doit > 0;
-- Third, reset AUTO_INCREMENT values for the profile tables
-- Note you need the PREPARE/EXECUTE because you cannot assign a variable to
-- AUTO_INCREMENT directly
SET @lastgroupid := (select max(profilegroupid) FROM displayprofilegroups);
SET @s = CONCAT("ALTER TABLE displayprofilegroups AUTO_INCREMENT=", @lastgroupid+1);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @lastprofileid := (select max(profileid) FROM displayprofiles);
SET @s = CONCAT("ALTER TABLE displayprofiles AUTO_INCREMENT=", @lastprofileid+1);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
EOF
[ -z "$deleteonly" ] && sed "s/<OLDHOSTNAME>/$OLDHOST/g;s/<NEWHOSTNAME>/$NEWHOST/g" <<EOF
-- COPY OLDHOSTNAME SETTINGS TO NEWHOSTNAME
select if(@doit,
"** Copying over frontend profile from '<OLDHOSTNAME>' to '<NEWHOSTNAME>'...",
"");
-- First copy over settings for tables: settings, keybindings, jumppoints
INSERT INTO settings(value, data, hostname) SELECT value, data,
'<NEWHOSTNAME>' FROM settings WHERE hostname = '<OLDHOSTNAME>' AND @doit > 0;
INSERT INTO keybindings(context, action, description, keylist,
hostname) SELECT context, action, description, keylist,
'<NEWHOSTNAME>' FROM keybindings WHERE hostname = '<OLDHOSTNAME>' AND @doit > 0;
INSERT INTO jumppoints(destination, description, keylist, hostname)
SELECT destination, description, keylist, '<NEWHOSTNAME>' FROM
jumppoints WHERE hostname = '<OLDHOSTNAME>' AND @doit > 0;
-- Second copy over settings for profiles.
-- Create temporary Tables GroupIDMap and ProfileIDMap to map profilegroup's
-- and profileid's from displayprofilegroup and displayprofiles tables
-- so as to avoid conflicts and minimize wasted key space
CREATE TEMPORARY TABLE GroupIDMap (oldid INT UNSIGNED , newid INT
UNSIGNED);
INSERT INTO GroupIDMap(oldid, newid) SELECT DISTINCT profilegroupid,
@lastgroupid := @lastgroupid+1 FROM displayprofilegroups WHERE
hostname='<OLDHOSTNAME>' AND @doit > 0;
CREATE TEMPORARY TABLE ProfileIDMap (oldid INT UNSIGNED, newid INT
UNSIGNED);
INSERT into ProfileIDMap(oldid) SELECT DISTINCT profileid FROM
displayprofiles WHERE profilegroupid IN (SELECT oldid FROM
GroupIDMap) AND @doit > 0;
UPDATE ProfileIDMap set newid=(@lastprofileid := @lastprofileid+1)
where @doit > 0;
-- Third, use the temporary tables to copy the profiles
INSERT INTO displayprofilegroups (name, hostname, profilegroupid)
SELECT name, '<NEWHOSTNAME>', newid FROM displayprofilegroups INNER
JOIN GroupIDMap ON profilegroupid=oldid where @doit > 0;
INSERT INTO displayprofiles(profilegroupid, profileid, value, data)
SELECT G.newid, P.newid, value, data
FROM displayprofiles INNER JOIN GroupIDMap G ON profilegroupid=G.oldid
INNER JOIN ProfileIDMap P ON profileid=P.oldid where @doit > 0;
EOF
)
###########################################################################
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
|
