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

Jeffrey J. Kosowsky mythtv at kosowsky.org
Mon Jan 11 03:57:29 UTC 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:
--------------------------------------------------------------------------
# !/bin/bash
#mythfrontendprofiledup.bash
#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 [ -z "$deleteonly" ] ; then
	echo "** Copying frontend settings from '$OLDHOST' to '$NEWHOST'"
else
	NEWHOST=$OLDHOST
 	echo "** Deleting frontend settings for '$OLDHOST'"
fi
#########################################################################
# Inject mysql queries
mysql -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
set @oldhostexists := (select count(distinct hostname) from displayprofilegroups where hostname = '<OLDHOSTNAME>');

-- Only do deletions and insertions if:
--        @doit = @numotherhosts * @oldhostexists > 0
set @doit :=  @oldhostexists * @numotherhosts;

-- 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
-- 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
)
###########################################################################


More information about the mythtv-users mailing list