From f4cb70b89550dd5d8e822daaa9ac750256879a79 Mon Sep 17 00:00:00 2001
From: Bob Igo <bob@stormlogic.com>
Date: Tue, 17 Feb 2009 15:57:23 -0500
Subject: Added Tweaker support scripts to create minimal baseline SQL which
 can then be tweaked.

---
 abs/core-testing/tweaker/bin/SQLtweaker.sh        |  86 +++++++++++
 abs/core-testing/tweaker/bin/create-linhes-sql.sh | 165 ++++++++++++++++++++++
 2 files changed, 251 insertions(+)
 create mode 100755 abs/core-testing/tweaker/bin/SQLtweaker.sh
 create mode 100755 abs/core-testing/tweaker/bin/create-linhes-sql.sh

diff --git a/abs/core-testing/tweaker/bin/SQLtweaker.sh b/abs/core-testing/tweaker/bin/SQLtweaker.sh
new file mode 100755
index 0000000..5760e5d
--- /dev/null
+++ b/abs/core-testing/tweaker/bin/SQLtweaker.sh
@@ -0,0 +1,86 @@
+# This isn't to be run.  Do not chmod it +x and try - it won't do anything.
+
+# This function will change or create entries in various tables within
+# the mythconverg database.
+# If the key value already exists in $SQL_FILENAME, then it is changed.  Otherwise,
+# it will create the necessary SQL to make the entry if it does not exist.
+#
+# For the purposes of this function, a key is defined as any unique string of text
+# after which a comma and a value or values follow.  E.g.:
+# in an entry like this:
+# INSERT INTO `videotypes` VALUES (6,'mpeg','',0,1);
+# we can define the key as "6" if we wanted, but that would be ambiguous.  So we can instead
+# define it as "6,'mpeg'" and we gain the ability to control the values of the fields after
+# the first two.  So this function call
+#
+# ChangeOrMakeEntry "6,'mpeg'" "'mplayer -fs -zoom', 0,1" "videotypes"
+#
+# would change the above SQL entry into this:
+#
+# INSERT INTO `videotypes` VALUES (6,'mpeg','mplayer -fs -zoom', 0,1);
+
+# parameters: name, value, table
+
+# Some tables have simple key, value pairs.  Others have several potential keys and values.
+# The 'settings' table is a simple key, value pair table, plus the name of the system on which
+# the settings hold true, e.g.:
+# INSERT INTO `settings` VALUES ('FooHat','Peas with sauce:drizzle','MythTVhost');
+###
+function ChangeOrMakeEntry {
+    NAME=$1	; shift
+    VALUE=$1	; shift
+    TABLE=$1	; shift
+
+    if [ `grep -c "$NAME" $SQL_FILENAME` == 0 ]; then
+	# There is no setting for $SETTING_NAME, so we need to make it
+	echo "INSERT INTO \`$TABLE\`" VALUES \($NAME,$VALUE\)\; >> $SQL_FILENAME
+    else
+	# There is a setting for $SETTING_NAME, so make sure it's what we want it to be.
+	sed -i "s@$NAME,.*);@$NAME,$VALUE);@" $SQL_FILENAME
+    fi
+}
+
+# shortcut function to ChangeOrMakeEntry for 'settings' table
+function ChangeOrMakeSetting {
+    NAME=$1	; shift
+    VALUE=$1	; shift
+
+    ChangeOrMakeEntry "$NAME" "$VALUE, 'MythTVhost'" "settings"
+}
+
+# shortcut function to ChangeOrMakeEntry for 'settings' table
+function ChangeOrMakeKeybinding {
+    NAME=$1	; shift
+    VALUE=$1	; shift
+
+    ChangeOrMakeEntry "$NAME" "$VALUE, 'MythTVhost'" "keybindings"
+}
+
+# This only works for North America at the moment.
+LocaleCode() { # no arguments.  Obtain a zipcode or locale, either using
+    # SchedulesDirect subscription info or by asking the user.
+
+    mysqldump --tab=/tmp --opt mythconverg videosource
+    # Get the first data direct "Video Source"
+    dd_src=$(awk -F'\t' '$3 == "schedulesdirect1" {print $0; exit 0;}' </tmp/videosource.txt)
+    /bin/rm -f /tmp/videosource.txt
+    if [ -n "$dd_src" ]; then
+	# we have a chance at getting a usable locale from the schedulesdirect data
+	    cd /usr/LH/
+	    
+	    SCHEDULESDIRECT_USERNAME=$(echo "$dd_src" | cut -f4)
+	    SCHEDULESDIRECT_PASSWORD=$(echo "$dd_src" | cut -f7)
+	    
+	    sed -i "s/<userName>.*<\/userName>/<userName>$SCHEDULESDIRECT_USERNAME<\/userName>/" xtvd.xml
+	    sed -i "s/<password>.*<\/password>/<password>$SCHEDULESDIRECT_PASSWORD<\/password>/" xtvd.xml
+	    datadirect-parse.pl &> /dev/null
+	    
+	    SQL_LocaleCode=`grep -i postalCode \`ls -1tr *xml | tail -1\` | perl -e '%zips=""; while(<>) { split(/postalCode=/); @_[1] =~ m/(\d{5})/; if ($1) { $zips{$1}=$1; } } @keys = keys %zips; if ($#keys == 1) { print values %zips,"\n"; }'`
+
+	    if [ "$SQL_LocaleCode" == "" ]; then # zipcode was ambiguous or was not extractable
+		echo -e "\nPlease enter your US zipcode or Canadian Postal Code:"
+		read SQL_LocaleCode
+	    fi
+	    echo $SQL_LocaleCode > /tmp/locale.txt # remember this just long enough to make use of it in PostSQLTweaker.sh
+    fi
+}
diff --git a/abs/core-testing/tweaker/bin/create-linhes-sql.sh b/abs/core-testing/tweaker/bin/create-linhes-sql.sh
new file mode 100755
index 0000000..c4a7848
--- /dev/null
+++ b/abs/core-testing/tweaker/bin/create-linhes-sql.sh
@@ -0,0 +1,165 @@
+#!/bin/bash
+
+# Written by Bob Igo from the MythTV Store at http://MythiC.TV
+# Email: bob@stormlogic.com
+#
+# If you run into problems with this script, please send me email
+
+# PURPOSE:
+# --------------------------
+# This script automates the creation of a new LinHES.sql based
+# on the contents of the current mythconverg database.
+
+if [ `whoami` != "root" ]; then
+    echo "This script must be run as root."
+    exit -1
+fi
+
+# Every file we may create, edit, or delete
+###########################################
+export LINHES_SQL_PROTOTYPE=/data/database/LinHES.sql-new
+export MYTHTV_SQL_OLD=/data/database/LinHES.sql-backup
+export TMPFILE=/tmp/LinHES.sql-tmp
+
+echo ""
+echo " ** ONLY CESMAN SHOULD EVER NEED TO RUN THIS SCRIPT **"
+echo ""
+echo " WARNING: This will archive your mythconverg database to a file,"
+echo " then delete mythconverg from MySQL.  It will then launch mythbackend"
+echo " to repopulate mythconverg, with a goal of creating the smallest-possible"
+echo " mythconverg database that MythTV can load without complanining."
+echo " Every attempt will be made to restore your original database, but"
+echo " ***no guarantee is made***."
+echo ""
+echo " ** ONLY CESMAN SHOULD EVER NEED TO RUN THIS SCRIPT **"
+echo ""
+
+echo -n "Proceed? [y|N] "
+read FEAR
+
+if [ "$FEAR" != "y" ] && [ "$FEAR" != "Y" ]; then
+    echo "***NO OPERATION WILL BE PERFORMED***"
+    exit
+fi
+
+# Archive the current mythconverg table from the MySQL database:
+echo "DROP DATABASE IF EXISTS mythconverg; CREATE DATABASE mythconverg; USE mythconverg;" > $MYTHTV_SQL_OLD
+mysqldump mythconverg >> $MYTHTV_SQL_OLD
+
+# delete the mythconverg database from MySQL
+killall mythbackend
+mysql -e "DROP DATABASE IF EXISTS mythconverg; CREATE DATABASE mythconverg;"
+
+mythtv-setup --geometry 640x480 2>&1 > /dev/null &
+
+echo "*"
+echo "* 1) PICK ANY LANGUAGE WHEN PROMPTED."
+echo "* 2) AGREE TO THE SCHEMA UPGRADE."
+echo "* 3) PRESS ENTER HERE WHEN THE MYTHTV-SETUP MENU LAUNCHES."
+echo "*"
+read KEYPRESS
+
+mysql -e "USE mythconverg; \
+INSERT INTO settings VALUES ('SecurityPin','0000','"`hostname`"'); \
+INSERT INTO settings VALUES ('BackendServerIP','127.0.0.1','"`hostname`"'); \
+INSERT INTO storagegroup VALUES ('\N', 'Default','"`hostname`"','/');"
+
+echo "*"
+echo "* 1) EXIT MYTHTV-SETUP"
+echo "* 2) PRESS ENTER HERE WHEN READY TO CONTINUE."
+echo "*"
+read KEYPRESS
+
+# let mythbackend repopulate mythconverg from scratch
+mythbackend 2>&1 > /dev/null &
+sleep 3
+
+mythfrontend --geometry 640x480 2>&1 > /dev/null &
+echo "*"
+echo "* 1) EXIT MYTHFRONTEND"
+echo "* 2) PRESS ENTER HERE WHEN READY TO CONTINUE."
+echo "*"
+read KEYPRESS
+
+killall mythfrontend
+killall mythbackend
+
+# save off the mostly-pristine MythTV myconverg database
+# (It would be 100% pristine, but mythbackend won't run unless BackendServerIP is given a value,
+# and both mythtv-setup and the mythfrontend won't proceed until you've picked a language.)
+
+# Edit the pristine MythTV mythconverg database so that we can use it to prime
+# the database during a semi-automated LinHES installation.
+
+SQL_FILENAME=$LINHES_SQL_PROTOTYPE
+
+# load our library functions
+. /usr/LH/tweaker/bin/SQLtweaker.sh
+#
+echo "*"
+echo "* Writing $LINHES_SQL_PROTOTYPE..."
+echo "*"
+#
+echo "/*" > $LINHES_SQL_PROTOTYPE
+echo " * MythTV raw SQL creation file." >> $LINHES_SQL_PROTOTYPE
+echo " */" >> $LINHES_SQL_PROTOTYPE
+echo "" >> $LINHES_SQL_PROTOTYPE
+echo "-- #################################################################### --" >> $LINHES_SQL_PROTOTYPE
+echo "-- Drop any initial database:" >> $LINHES_SQL_PROTOTYPE
+echo "DROP DATABASE IF EXISTS mythconverg;" >> $LINHES_SQL_PROTOTYPE
+echo "" >> $LINHES_SQL_PROTOTYPE
+echo "-- #################################################################### --" >> $LINHES_SQL_PROTOTYPE
+echo "-- Create an empty new database:" >> $LINHES_SQL_PROTOTYPE
+echo "CREATE DATABASE mythconverg;" >> $LINHES_SQL_PROTOTYPE
+echo "GRANT ALL ON mythconverg.* TO mythtv@localhost IDENTIFIED BY \"mythtv\";" >> $LINHES_SQL_PROTOTYPE
+echo "FLUSH PRIVILEGES;" >> $LINHES_SQL_PROTOTYPE
+echo "GRANT CREATE TEMPORARY TABLES ON mythconverg.* TO mythtv@localhost IDENTIFIED BY \"mythtv\";" >> $LINHES_SQL_PROTOTYPE
+echo "FLUSH PRIVILEGES;" >> $LINHES_SQL_PROTOTYPE
+echo "USE mythconverg;" >> $LINHES_SQL_PROTOTYPE
+echo "" >> $LINHES_SQL_PROTOTYPE
+echo "-- #################################################################### --" >> $LINHES_SQL_PROTOTYPE
+echo "-- Create all the tables:" >> $LINHES_SQL_PROTOTYPE
+echo "" >> $LINHES_SQL_PROTOTYPE
+
+# Dump the database, removing all unneeded DB inserts - when LinHES launches mythtv-setup and mythfrontend,
+# anything undefined will be given default values by the applications, except for the INSERTs below.
+mysqldump mythconverg | sed -e "s/`hostname`/MythTVhost'/g" -e "s/AUTO_INCREMENT=[0-9]* //g" > $TMPFILE
+grep -v "INSERT INTO" $TMPFILE >> $LINHES_SQL_PROTOTYPE
+sed "s/,(/,\n(/g" $TMPFILE | grep DBSchema | sed "s/\(.*\)NULL),/INSERT INTO settings VALUES \1'MythTVhost');/g" >> $LINHES_SQL_PROTOTYPE
+echo "INSERT INTO settings VALUES ('SecurityPin','0000','MythTVhost');" >> $LINHES_SQL_PROTOTYPE
+echo "INSERT INTO settings VALUES ('BackendServerIP','127.0.0.1','MythTVhost');" >> $LINHES_SQL_PROTOTYPE
+echo "INSERT INTO settings VALUES ('MasterServerIP','127.0.0.1','MythTVhost');" >> $LINHES_SQL_PROTOTYPE
+echo "INSERT INTO settings VALUES ('MasterServerPort','6543','MythTVhost');" >> $LINHES_SQL_PROTOTYPE
+echo "INSERT INTO settings VALUES ('Theme', 'Iulius','MythTVhost');" >> $LINHES_SQL_PROTOTYPE
+echo "INSERT INTO storagegroup VALUES ('\N', 'Default','MythTVhost','/');" >> $LINHES_SQL_PROTOTYPE
+
+rm $TMPFILE
+
+#
+echo "*"
+echo "...DONE"
+echo "*"
+#
+
+echo "*"
+echo "* PRESS ENTER HERE WHEN READY TO RESTORE THE OLD DATABASE"
+echo "* AND RE-LAUNCH MYTHBACKEND"
+echo "*"
+read KEYPRESS
+
+#
+echo "Restoring original database from $MYTHTV_SQL_OLD..."
+#
+# Restore the original mythconverg database
+cat $MYTHTV_SQL_OLD | mysql 2>&1 > /dev/null
+#
+echo "...DONE"
+#
+
+#
+echo "restarting mythbackend..."
+#
+mythbackend &
+#
+echo "...DONE"
+#
-- 
cgit v0.12

