home
products
contribute
download
documentation
forum
Home
Forums
New posts
Search forums
What's new
New posts
All posts
Latest activity
Members
Registered members
Current visitors
Donate
Log in
Register
What's new
Search
Search
Search titles only
By:
New posts
Search forums
Search titles only
By:
Menu
Log in
Register
Navigation
Install the app
Install
More options
Contact us
Close Menu
Forums
MediaPortal 1
Development
General Development (no feature request here!)
MySQL script for making re-installation of TV-Server a cakewalk
Contact us
RSS
JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser
.
Reply to thread
Message
<blockquote data-quote="level20peon" data-source="post: 188632" data-attributes="member: 28613"><p>Hey guys,</p><p></p><p>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.</p><p></p><p>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.</p><p></p><p>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).</p><p></p><p><em>NOTE: ADAPTION OF THIS SCRIPT MAY TAKE QUITE SOME WORK, BUT IT WILL SAVE MUCH TIME AFTERWARDS <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite2" alt=";)" title="Wink ;)" loading="lazy" data-shortname=";)" /></em></p><p></p><p>[CODE]</p><p>[COLOR="Gray"]/* Enter all of the channels that you want to keep from all the</p><p>channels appearing after a channel scan here. Just replace the SciFi,</p><p>Sports and News channel as you like (you may enter as many</p><p>channels as you like). All channels not listed here will be deleted. */[/COLOR]</p><p>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;</p><p></p><p>[COLOR="Gray"]/*Chose a sort order for your channels, no more nasty sorting after</p><p>each scan. (TV-Channels start with "0", Radio-Channels also start</p><p>with "0" seperately */[/COLOR]</p><p>UPDATE channel SET sortOrder = REPLACE(name, 'SciFi Channel', '0') WHERE name = 'SciFi Channel';</p><p>UPDATE channel SET sortOrder = REPLACE(name, 'Sports Channel', '1') WHERE name = 'Sports Channel';</p><p>UPDATE channel SET sortOrder = REPLACE(name, 'News Channel', '2') WHERE name = 'News Channel';</p><p></p><p>UPDATE channel SET sortOrder = REPLACE(name, 'Radio Channel 1', '0') WHERE name = 'Radio Channel 1';</p><p>UPDATE channel SET sortOrder = REPLACE(name, 'Radio Channel 2', '1') WHERE name = 'Radio Channel 2';</p><p></p><p>[COLOR="Gray"]/*Check every setting you want to change which differs from</p><p>the default setting. Use the following pattern for your consideration. */[/COLOR]</p><p>UPDATE card SET recordingFolder = 'D:\\TV\\RECORDINGS';</p><p>UPDATE card SET timeshiftingFolder = 'Y:\\';</p><p>UPDATE card SET recordingFormat = '0';</p><p>UPDATE card SET decryptLimit = '3';</p><p></p><p>UPDATE server SET hostName = '192.168.0.11';</p><p></p><p>UPDATE setting SET value = REPLACE(tag, 'epgStoreOnlySelected', 'yes') WHERE tag = 'epgStoreOnlySelected';</p><p>UPDATE setting SET value = REPLACE(tag, 'timeshiftingEpgGrabberEnabled', 'no') WHERE tag = 'timeshiftingEpgGrabberEnabled';</p><p>UPDATE setting SET value = REPLACE(tag, 'xmlTv', 'C:\\Programme\\Team Mediaportal\\MediaPortal\\WebEPG') WHERE tag = 'xmlTv';</p><p></p><p></p><p>/* INSERT recording schedules */</p><p>DELETE FROM schedule;</p><p>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');</p><p></p><p>/* 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 */</p><p>UPDATE schedule SET idChannel = REPLACE(idChannel, '222', (SELECT idChannel FROM channel WHERE name = 'ZDF')) WHERE idChannel = '333';</p><p></p><p></p><p>[COLOR="Gray"]/*This section only applies for german Users who use TV-Movie</p><p>Clickfinder. Just follow the pattern of this section to automatically</p><p>assign your TV-Channels to the Clickfinders Channels. The</p><p>[I]tvmoviemapping[/I] table is first truncated and afterwards there are</p><p>entered some default values (you should take your current</p><p>[I]tvmoviemapping[/I] table, export it and then use it in the script. The</p><p>fields need to be filled before the next step) */[/COLOR]</p><p></p><p>DELETE FROM tvmoviemapping;</p><p></p><p>INSERT INTO `tvmoviemapping` VALUES ('1', '536', 'SciFi Channel', '00:00', '00:00');</p><p>INSERT INTO `tvmoviemapping` VALUES ('2', '566', 'Sports Channel', '00:00', '00:00');</p><p>INSERT INTO `tvmoviemapping` VALUES ('3', '564', 'News Channel', '00:00', '00:00');</p><p></p><p></p><p>[COLOR="Gray"]/*Assign Clickfinder with the current channel-ID's of your current</p><p>channel-scan dynamically */[/COLOR]</p><p>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])';</p><p>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])';</p><p>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])';[/CODE]</p><p></p><p>Save the code as "<em>something.sql</em>" and run it by entering</p><p>[CODE]</p><p>"c:\<mysqlinstalldir>\bin\mysql.exe" -u <yourmysqlusername> -p<yourmysqlpassword> <dbname> < "c:\something.sql"</p><p>[/CODE]</p><p>in the command prompt.</p><p></p><p><span style="color: Red">NOTE: It is important to use a space character here "<em>-u <yourmysqlusername></em>" and NO space character here "<em>-p<yourmysqlpassword></em>"... this is no typing error.</span></p><p></p><p></p><p>After the script is ready to launch, you basically can delete the <em>tvlibrary</em> database (or do an install of the TV-Server from scratch). Then:</p><p><span style="color: Red">1. </span>Do a full channelscan</p><p><span style="color: Red">2. </span>stop the TV-Service and exit the configuration</p><p><span style="color: Red">3. </span>run the script</p><p><span style="color: Red">4. </span>start the TV-Service, voila, all of your configuration is restored</p><p></p><p></p><p>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 <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite2" alt=";)" title="Wink ;)" loading="lazy" data-shortname=";)" /></p><p></p><p></p><p>-level20peon</p></blockquote><p></p>
[QUOTE="level20peon, post: 188632, member: 28613"] 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). [I]NOTE: ADAPTION OF THIS SCRIPT MAY TAKE QUITE SOME WORK, BUT IT WILL SAVE MUCH TIME AFTERWARDS ;)[/I] [CODE] [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])';[/CODE] Save the code as "[I]something.sql[/I]" and run it by entering [CODE] "c:\<mysqlinstalldir>\bin\mysql.exe" -u <yourmysqlusername> -p<yourmysqlpassword> <dbname> < "c:\something.sql" [/CODE] in the command prompt. [COLOR="Red"]NOTE: It is important to use a space character here "[I]-u <yourmysqlusername>[/I]" and NO space character here "[I]-p<yourmysqlpassword>[/I]"... this is no typing error.[/COLOR] After the script is ready to launch, you basically can delete the [I]tvlibrary[/I] database (or do an install of the TV-Server from scratch). Then: [COLOR="Red"]1. [/COLOR]Do a full channelscan [COLOR="Red"]2. [/COLOR]stop the TV-Service and exit the configuration [COLOR="Red"]3. [/COLOR]run the script [COLOR="Red"]4. [/COLOR]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 [/QUOTE]
Insert quotes…
Verification
Post reply
Forums
MediaPortal 1
Development
General Development (no feature request here!)
MySQL script for making re-installation of TV-Server a cakewalk
Contact us
RSS
Top
Bottom
