[mythtv-users] UPDATED: Bash/MySQL script for copying and/or deleting mythfrontend profiles

[Jeffrey J. Kosowsky]

Jeffrey J. Kosowsky wrote at about 22:57:29 -0500 on Sunday, January 10, 2010:
 > OK. I spent way too much time today learning mysql on the fly :)
 > But here is a combined Bash/MySQL script that I came up with to
 > robustly either copy or delete mythfrontend profiles. I owe much
 > gratitude to Ronald Frazier for coming up with the core SQL queries --
 > I just added some robustness and extensions...
 > 
 > The script does some reasonable error checking to make sure that you
 > are not deleting the last frontend profile and that the profile you
 > are copying actually exists.
 > 
 > The routine also by default (and in the interest of healthy paranoia)
 > dumps a copy of your existing database before doing anything...
 > 
 > Use the '-h' flag for usage information to figure out how to use the
 > routine.
 > 
 > Of course, I am the most newbie of newbies to mysql, so check through
 > the code before using, don't turn off the default backups, and test it
 > first, etc....
 > 
 > Here is the code:

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:

-----------------------------------------------------------------------------
# !/bin/bash
#mythfrontendprofiledup
#Jeff Kosowsky (with MUCH help from Ronald Frazier)
#Script to automatically copy and/or delete mythfrontend profiles.

##############################################################################
#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##*/} -h
             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
}
############################################################################

SHORT_OPTIONS="d,h,m:,n,p:,u:"
LONG_OPTIONS="deleteonly,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 ;;
	--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 "$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
	unset 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
-- NOTE in the deletion case "<OLDHOSTNAME>" = "", so we always try to delete
set @oldhostexists := if("<OLDHOSTNAME>" = "", 1, 
	(select count(distinct hostname) from displayprofilegroups 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
)
###########################################################################