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

Jeffrey J. Kosowsky mythtv at kosowsky.org
Wed Jan 13 06:39:53 UTC 2010


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


More information about the mythtv-users mailing list