MySQL script for making re-installation of TV-Server a cakewalk (1 Viewer)

[level20peon]

Hey guys,

I rarely re-install the TV-Server very often for update purposes. Well I think that the main reason for this is that it is not always possible to backup the configuration, since sometimes something is changed in the TV-Server application itself or maybe the TV-providers change something in their channel frequencies.

So I wrote this SQL script and would like to share it. Maybe some of you have similar issues and can increase efficiency of their work with this.

Main purpose of this script is to dynamically restore your old settings without backing up your old database which may lead to incompatibility issues. So the script adapts all new database entries like channel ID's and just replaces the relevant entries (or columns).

NOTE: ADAPTION OF THIS SCRIPT MAY TAKE QUITE SOME WORK, BUT IT WILL SAVE MUCH TIME AFTERWARDS

[COLOR="Gray"]/* Enter all of the channels that you want to keep from all the
channels appearing after a channel scan here. Just replace the SciFi,
Sports and News channel as you like (you may enter as many
channels as you like). All channels not listed here will be deleted. */[/COLOR]
DELETE channel c, channelmap h, tuningdetail t FROM channel c, channelmap h, tuningdetail t WHERE c.name NOT IN ("SciFi Channel", "Sports Channel", "News Channel", "Radio Channel 1", "Radio Channel 2") AND c.idChannel = h.idChannel AND c.idChannel = t.idChannel;

[COLOR="Gray"]/*Chose a sort order for your channels, no more nasty sorting after
each scan. (TV-Channels start with "0", Radio-Channels also start
with "0" seperately */[/COLOR]
UPDATE channel SET sortOrder = REPLACE(name, 'SciFi Channel', '0') WHERE name = 'SciFi Channel';
UPDATE channel SET sortOrder = REPLACE(name, 'Sports Channel', '1') WHERE name = 'Sports Channel';
UPDATE channel SET sortOrder = REPLACE(name, 'News Channel', '2') WHERE name = 'News Channel';

UPDATE channel SET sortOrder = REPLACE(name, 'Radio Channel 1', '0') WHERE name = 'Radio Channel 1';
UPDATE channel SET sortOrder = REPLACE(name, 'Radio Channel 2', '1') WHERE name = 'Radio Channel 2';

[COLOR="Gray"]/*Check every setting you want to change which differs from
the default setting. Use the following pattern for your consideration. */[/COLOR]
UPDATE card SET recordingFolder = 'D:\\TV\\RECORDINGS';
UPDATE card SET timeshiftingFolder = 'Y:\\';
UPDATE card SET recordingFormat = '0';
UPDATE card SET decryptLimit = '3';

UPDATE server SET hostName = '192.168.0.11';

UPDATE setting SET value = REPLACE(tag, 'epgStoreOnlySelected', 'yes') WHERE tag = 'epgStoreOnlySelected';
UPDATE setting SET value = REPLACE(tag, 'timeshiftingEpgGrabberEnabled', 'no') WHERE tag = 'timeshiftingEpgGrabberEnabled';
UPDATE setting SET value = REPLACE(tag, 'xmlTv', 'C:\\Programme\\Team Mediaportal\\MediaPortal\\WebEPG') WHERE tag = 'xmlTv';


/* INSERT recording schedules */
DELETE FROM schedule;
INSERT INTO `schedule` VALUES ('3', '222', '3', 'nano', '2008-05-02 04:35:00', '2008-05-02 05:00:00', '5', '1', 'D:\\TV\\RECORDINGS', '0', '3', '2000-01-01 00:00:00', '5', '5', '2000-01-01 00:00:00', '1');

/* since Channel-ID's can differ after a while with a new scan I update them this way... using a placeholder to insert the schedule into the db and replace it with the correct value after a scan */
UPDATE schedule SET idChannel = REPLACE(idChannel, '222', (SELECT idChannel FROM channel WHERE name = 'ZDF')) WHERE idChannel = '333';


[COLOR="Gray"]/*This section only applies for german Users who use TV-Movie
Clickfinder. Just follow the pattern of this section to automatically
assign your TV-Channels to the Clickfinders Channels. The
[I]tvmoviemapping[/I] table is first truncated and afterwards there are
entered some default values (you should take your current
[I]tvmoviemapping[/I] table, export it and then use it in the script. The
fields need to be filled before the next step) */[/COLOR]

DELETE FROM tvmoviemapping;

INSERT INTO `tvmoviemapping` VALUES ('1', '536', 'SciFi Channel', '00:00', '00:00');
INSERT INTO `tvmoviemapping` VALUES ('2', '566', 'Sports Channel', '00:00', '00:00');
INSERT INTO `tvmoviemapping` VALUES ('3', '564', 'News Channel', '00:00', '00:00');


[COLOR="Gray"]/*Assign Clickfinder with the current channel-ID's of your current
channel-scan dynamically */[/COLOR]
UPDATE tvmoviemapping SET idChannel = REPLACE(stationName, 'SciFi Channel([COLOR="Red"]wie es im Clickfinder benannt ist[/COLOR])', (SELECT idChannel FROM channel WHERE name = 'SciFi Channel([COLOR="Red"]wie es in den channels benannt ist[/COLOR])')) WHERE stationName = 'SciFi Channel([COLOR="Red"]wie es im Clickfinder benannt ist[/COLOR])';
UPDATE tvmoviemapping SET idChannel = REPLACE(stationName, 'Sports Channel([COLOR="Red"]wie es im Clickfinder benannt ist[/COLOR])', (SELECT idChannel FROM channel WHERE name = 'Sports Channel([COLOR="Red"]wie es in den channels benannt ist[/COLOR])')) WHERE stationName = 'Sports Channel([COLOR="Red"]wie es im Clickfinder benannt ist[/COLOR])';
UPDATE tvmoviemapping SET idChannel = REPLACE(stationName, 'News Channel([COLOR="Red"]wie es im Clickfinder benannt ist[/COLOR])', (SELECT idChannel FROM channel WHERE name = 'News Channel([COLOR="Red"]wie es in den channels benannt ist[/COLOR])')) WHERE stationName = 'News Channel([COLOR="Red"]wie es im Clickfinder benannt ist[/COLOR])';

Save the code as "something.sql" and run it by entering

"c:\<mysqlinstalldir>\bin\mysql.exe" -u <yourmysqlusername> -p<yourmysqlpassword> <dbname> < "c:\something.sql"

in the command prompt.

NOTE: It is important to use a space character here "-u <yourmysqlusername>" and NO space character here "-p<yourmysqlpassword>"... this is no typing error.


After the script is ready to launch, you basically can delete the tvlibrary database (or do an install of the TV-Server from scratch). Then:
1. Do a full channelscan
2. stop the TV-Service and exit the configuration
3. run the script
4. start the TV-Service, voila, all of your configuration is restored


I am attaching my complete script for you to see what I was up to this evening. Maybe it helps understanding if the above descriptions are too crappy


-level20peon