User:Klojum/Testkees/MySQL - Revision history

Klojum: /* Type carefully... */

2018-09-03T07:57:20Z

Type carefully...

← Older revision		Revision as of 07:57, 3 September 2018
Line 112:		Line 112:

	UPDATE movie SET c22 = replace(c22,'smb://192.168.0.1/',' nfs://192.168.2.2/');		UPDATE movie SET c22 = replace(c22,'smb://192.168.0.1/',' nfs://192.168.2.2/');
	UPDATE tvshow set ~~SET~~ = replace(c16,'smb://192.168.0.1/',' nfs://192.168.2.2/');		UPDATE tvshow set c16 = replace(c16,'smb://192.168.0.1/',' nfs://192.168.2.2/');
	UPDATE episode SET c18 = replace(c18,'smb://192.168.0.1/',' nfs://192.168.2.2/');		UPDATE episode SET c18 = replace(c18,'smb://192.168.0.1/',' nfs://192.168.2.2/');
	</pre>		</pre>

Klojum: /* Moving on to the next table */

2018-08-27T08:20:14Z

Moving on to the next table

← Older revision		Revision as of 08:20, 27 August 2018
Line 207:		Line 207:
	Once you've crafted and triple checked your queries, go ahead and execute them just as we did for the path table.		Once you've crafted and triple checked your queries, go ahead and execute them just as we did for the path table.

	~~===~~Example~~===~~		'''Example'''

	You had 2 hard drives with movies on them, and now you are upgrading to 1 much larger drive and combining all of your movies to this single drive. So we're going to change our paths from:		You had 2 hard drives with movies on them, and now you are upgrading to 1 much larger drive and combining all of your movies to this single drive. So we're going to change our paths from:

Klojum: /* Modifying MySQL Paths */

2018-08-27T08:19:36Z

Modifying MySQL Paths

← Older revision		Revision as of 08:19, 27 August 2018
Line 151:		Line 151:
	On the row of tabs just below the toolbar, select the data tab. Make note of the strPATH column. As you can see, the full path to every directory within your source is shown in this column. This is the first thing we need to edit.		On the row of tabs just below the toolbar, select the data tab. Make note of the strPATH column. As you can see, the full path to every directory within your source is shown in this column. This is the first thing we need to edit.

	To do so, select the tab with the blue arrow that says "Query". In the query box you need to input the following:		To do so, select the tab with the blue arrow that says "Query". We need to replace '''OLD/PATH/''' and '''NEW/PATH''' with your paths. In the query box you need to input the following

	<pre>update path set strPath = replace(strPath,'OLD/PATH/','NEW/PATH/');</pre>		<pre>update path set strPath = replace(strPath,'OLD/PATH/','NEW/PATH/');</pre>

	~~We need to replace '''OLD/PATH/''' and '''NEW/PATH''' with your paths.~~

	'''Example'''		'''Example'''

Klojum: /* Modifying MySQL Paths */

2018-08-27T08:19:01Z

Modifying MySQL Paths

← Older revision		Revision as of 08:19, 27 August 2018
Line 158:		Line 158:

	'''Example'''		'''Example'''

	You stored all of your movies on one hard drive, and all of your tv shows on another hard drive. Now you are upgrading to bigger drives, but still keeping movies on one drive and tv shows on another. So we are going to change our source paths from:		You stored all of your movies on one hard drive, and all of your tv shows on another hard drive. Now you are upgrading to bigger drives, but still keeping movies on one drive and tv shows on another. So we are going to change our source paths from:

Klojum: /* Example */

2018-08-27T08:16:24Z

Example

← Older revision		Revision as of 08:16, 27 August 2018
Line 157:		Line 157:
	We need to replace '''OLD/PATH/''' and '''NEW/PATH''' with your paths.		We need to replace '''OLD/PATH/''' and '''NEW/PATH''' with your paths.

	~~===~~Example~~===~~		'''Example'''
	You stored all of your movies on one hard drive, and all of your tv shows on another hard drive. Now you are upgrading to bigger drives, but still keeping movies on one drive and tv shows on another. So we are going to change our source paths from:		You stored all of your movies on one hard drive, and all of your tv shows on another hard drive. Now you are upgrading to bigger drives, but still keeping movies on one drive and tv shows on another. So we are going to change our source paths from:

Klojum: /* Example */

2018-08-27T08:16:03Z

Example

← Older revision		Revision as of 08:16, 27 August 2018
Line 238:		Line 238:
	If they are already correct, then move on. If they are not correct then go back to your query tab and simply edit our previous queries and reuse most of what we've already done.		If they are already correct, then move on. If they are not correct then go back to your query tab and simply edit our previous queries and reuse most of what we've already done.

	~~===~~Example~~===~~		'''Example'''

	So for this table, we would simply take the queries from the previous example and change them to the following:		So for this table, we would simply take the queries from the previous example and change them to the following:

Klojum at 08:15, 27 August 2018

2018-08-27T08:15:24Z

← Older revision		Revision as of 08:15, 27 August 2018
Line 109:		Line 109:
	<pre>		<pre>
	UPDATE art SET url = replace(url,'smb://192.168.0.1/',' nfs://192.168.2.2/');		UPDATE art SET url = replace(url,'smb://192.168.0.1/',' nfs://192.168.2.2/');
	UPDATE ~~episode~~ SET ~~c18~~ = replace(~~c18~~,'smb://192.168.0.1/',' nfs://192.168.2.2/');		UPDATE path SET strPath = replace(strPath,'smb://192.168.0.1/',' nfs://192.168.2.2/');

	UPDATE movie SET c22 = replace(c22,'smb://192.168.0.1/',' nfs://192.168.2.2/');		UPDATE movie SET c22 = replace(c22,'smb://192.168.0.1/',' nfs://192.168.2.2/');
	~~UPDATE path SET strPath = replace(strPath,'smb://192.168.0.1/',' nfs://192.168.2.2/');~~
	UPDATE tvshow set SET = replace(c16,'smb://192.168.0.1/',' nfs://192.168.2.2/');		UPDATE tvshow set SET = replace(c16,'smb://192.168.0.1/',' nfs://192.168.2.2/');
			UPDATE episode SET c18 = replace(c18,'smb://192.168.0.1/',' nfs://192.168.2.2/');
	</pre>		</pre>

Line 202:		Line 203:
	==Moving on to the next table==		==Moving on to the next table==

	Next we need to select our Database: MyVideos** ~~tab again. This time scroll down~~ to ~~the movieview table and double click it~~.		Stuff to do ....

	~~Now select the Data tab again, and scroll to the right until you can see the c19 column~~.

	~~As you can see, this column contains the path to every trailer you have in your library~~. ~~We'll want to change those so your trailers show up properly.~~

	~~Now scroll a little further to the right until you can see the c22 column.~~

	~~As you can see here, this is yet another instance of paths to your root directory for each movie in your library. Obviously, we want to change those.~~

	Once again, scroll to the right just a little more until you can see the strFileName column. Most of these will simply be the movie file names and extensions. However, you will also see all of your multi-part movies (i.e. diehard.cd1.avi, diehard.cd2.avi) with a "stack://" in front. We need to be sure that these stack paths are updated, or else the next time you run Kodi and update your library it will re-scrape all of those movies as new.

	~~To change all of these, go to the Query tab again. Clear out your previous queries and replace them with these:~~

	~~<pre>~~
	~~UPDATE movieview SET strFileName = replace(strFileName,'OLD/PATH','NEW/PATH');~~
	~~UPDATE movieview SET c19 = replace(c19,'OLD/PATH','NEW/PATH');~~
	~~UPDATE movieview SET c22 = replace(c22,'OLD/PATH','NEW/PATH');~~
	~~</pre>~~

	You need to have 1 of each of the above queries for each '''MOVIE''' source that you are changing. So if you are changing 2 movie sources, you would have the above code twice-- once for each path. Do not add anything for TV sources here.

	Once you've crafted and triple checked your queries, go ahead and execute them just as we did for the path table.		Once you've crafted and triple checked your queries, go ahead and execute them just as we did for the path table.
Line 244:		Line 225:

	<pre>		<pre>
	UPDATE ~~movieview SET strFileName = replace(strFileName,'smb://movies1/share/','smb://big_movie_nas/');~~		UPDATE movie ** blablah
	~~UPDATE movieview SET strFileName = replace(strFileName,'smb://movies2/movies/share/','smb://big_movie_nas/movies/');~~
	~~UPDATE movieview SET c19 = replace(c19,'smb://movies1/share/','smb://big_movie_nas/');~~
	~~UPDATE movieview SET c19 = replace(c19,'smb://movies2/movies/share/','smb://big_movie_nas/movies/');~~
	~~UPDATE movieview SET c22 = replace(c22,'smb://movies1/share/','smb://big_movie_nas/');~~
	~~UPDATE movieview SET c22 = replace(c22,'smb://movies2/movies/share/','smb://big_movie_nas/movies/');~~
	</pre>		</pre>

	~~==Backup again==~~

	~~If all went smoothly, then backup again and name it Step2~~

	==One more table==		==One more table==
Line 262:		Line 234:
	On the Database: myvideos** tab, double click on the movie table.		On the Database: myvideos** tab, double click on the movie table.

	Go to the data tab and scroll right. Take note of columns c19 and c22. They should already reflect the changes made in the previous step, however we need to verify this~~. They should be the same as c19 and c22 in the movieview table~~.		Go to the data tab and scroll right. Take note of columns c19 and c22. They should already reflect the changes made in the previous step, however we need to verify this.

	If they are already correct, then move on. If they are not correct then go back to your query tab and simply edit our previous queries and reuse most of what we've already done.		If they are already correct, then move on. If they are not correct then go back to your query tab and simply edit our previous queries and reuse most of what we've already done.

	~~First, delete the lines that have strFileName in them.~~

	~~With the remaining lines, change all instances of movieview to movie. Now you're ready to execute your queries again.~~

	===Example===		===Example===

Klojum: /* Final notes */

2018-08-27T08:11:33Z

Final notes

← Older revision		Revision as of 08:11, 27 August 2018
Line 297:		Line 297:

	=Final notes=		=Final notes=

	Being that it's been a while since my last migration, I could be mistaken, or this could stem from changes to the DB in Frodo-- (or a bug that I am not aware of). That said, you could try simply using the first portion of the guide and just update the path table. Once that's done, backup your database and fire up Kodi. If everything works properly for you, then you've saved some time. If it doesn't (i.e. if it starts scraping existing content again) you can simply exit Kodi and drop the database, then import your backup and continue on with this guide.

	It cannot be stressed enough-- backup often. If you backed up after each step as instructed, then you could revert to the previous step if you messed up the next step, which could save you a lot of time. If you messed up something major and you're not sure when or where, you have your original to fall back on and start over.		It cannot be stressed enough-- backup often. If you backed up after each step as instructed, then you could revert to the previous step if you messed up the next step, which could save you a lot of time. If you messed up something major and you're not sure when or where, you have your original to fall back on and start over.

	If you didn't backup as instructed, and you messed something up, then you have just landed yourself in a smelly creek with no paddle.		If you didn't backup as instructed, and you messed something up, then you have just landed yourself in a smelly creek with no paddle. Ask in the Kodi forum if you still have questions on the whole data migration in Kodi.

Klojum: /* Thumbnails */

2018-08-27T08:10:05Z

Thumbnails

← Older revision		Revision as of 08:10, 27 August 2018
Line 290:		Line 290:

	==Thumbnails==		==Thumbnails==
	The last remaining obstacle is your thumbnail cache. You will need to disable your MySQL settings in one client (just temporarily remove the settings from your advancedsettings.xml). Then you need to delete your Thumbnail folders from each client~~. Now fire up Kodi on~~ the ~~client that has MySQL disabled and let it scrape all of your sources again~~. ~~It will take quite some time, depending on~~ the ~~size of your library~~.		The last remaining obstacle is your thumbnail cache. You will need to disable your MySQL settings in one client (just temporarily remove the settings from your advancedsettings.xml). Then you need to delete your Thumbnail folders from each client, as well as the local Textures13.db file in the userdata/Database folder.

	Once ~~it's~~ finished and you have all of your thumbnails back the way you like them, ~~shut down~~ Kodi. Copy your thumbnails folder to each of your Kodi clients, and restore your MySQL settings to all clients. Now ~~fire up~~ Kodi again, and everything should be back to normal.		Now restart Kodi on the client that has MySQL disabled and let it scrape all of your sources again. It will take quite some time, depending on the size of your library and the CPU power of that Kodi client computer.

			Once that is finished and you have all of your thumbnails back the way you like them, exit Kodi. Copy your thumbnails folder to each of your Kodi clients and the Texture13.db into the userdata/Database folder, and restore your MySQL settings to all clients. Now start Kodi again, and everything should be back to normal.

	=Final notes=		=Final notes=

Klojum: /* Type carefully... */

2018-08-27T08:04:11Z

Type carefully...

← Older revision		Revision as of 08:04, 27 August 2018
Line 108:		Line 108:
	Below are the tables that need to be altered in the MyVideos107 database for Kodi 17:		Below are the tables that need to be altered in the MyVideos107 database for Kodi 17:
	<pre>		<pre>
	~~UPDATE path SET strPath = replace(strPath,'smb://192.168.0.1/',' nfs://192.168.2.2/');~~
	~~UPDATE movieview SET c22 = replace(c22,'smb://192.168.0.1/',' nfs://192.168.2.2/');~~
	UPDATE art SET url = replace(url,'smb://192.168.0.1/',' nfs://192.168.2.2/');		UPDATE art SET url = replace(url,'smb://192.168.0.1/',' nfs://192.168.2.2/');
	UPDATE episode SET c18 = replace(c18,'smb://192.168.0.1/',' nfs://192.168.2.2/');		UPDATE episode SET c18 = replace(c18,'smb://192.168.0.1/',' nfs://192.168.2.2/');
	UPDATE ~~episodeview~~ SET ~~c18~~ = replace(~~c18~~,'smb://192.168.0.1/',' nfs://192.168.2.2/');		UPDATE movie SET c22 = replace(c22,'smb://192.168.0.1/',' nfs://192.168.2.2/');
	UPDATE ~~episodeview~~ SET ~~strShowPath~~ = replace(~~strShowPath~~,'smb://192.168.0.1/',' nfs://192.168.2.2/');		UPDATE path SET strPath = replace(strPath,'smb://192.168.0.1/',' nfs://192.168.2.2/');
	UPDATE tvshow set SET = replace(c16,'smb://192.168.0.1/',' nfs://192.168.2.2/');		UPDATE tvshow set SET = replace(c16,'smb://192.168.0.1/',' nfs://192.168.2.2/');
	</pre>		</pre>
Line 121:		Line 119:


	The music database tables can have another trick up their ~~sleeve~~, as URLs are stored in different formats. Compare the two URLs in this example from the 'art' table:		The music database tables can have another trick up their sleeves, as URLs are stored in different formats. Compare the two URLs in this example from the 'art' table:
	nfs://192.168.1.141/MUSIC/ARTISTS/Gipsy Kings/fanart.jpg		nfs://192.168.1.141/MUSIC/ARTISTS/Gipsy Kings/fanart.jpg
	image://music@nfs%3a%2f%2f192.168.1.141%2fMUSIC%2fARTISTS%2fGipsy%20Kings%2fGipsy%20Kings%2fGipsy%20Kings%20-%20Bamboleo.mp3/		image://music@nfs%3a%2f%2f192.168.1.141%2fMUSIC%2fARTISTS%2fGipsy%20Kings%2fGipsy%20Kings%2fGipsy%20Kings%20-%20Bamboleo.mp3/
Line 129:		Line 127:
	UPDATE art SET url = replace(url,'smb://192.168.0.1/',' nfs://192.168.2.2/');		UPDATE art SET url = replace(url,'smb://192.168.0.1/',' nfs://192.168.2.2/');
	UPDATE art SET url = replace(url,'@smb%3a%2f%2f192.168.0.1%2f',' @nfs%3a%2f%2f192.168.2.2%2f');		UPDATE art SET url = replace(url,'@smb%3a%2f%2f192.168.0.1%2f',' @nfs%3a%2f%2f192.168.2.2%2f');

	UPDATE path SET strPath = replace(strPath,'smb://192.168.0.1/',' nfs://192.168.2.2/');		UPDATE path SET strPath = replace(strPath,'smb://192.168.0.1/',' nfs://192.168.2.2/');
	</pre>		</pre>