HOW-TO:Update SQL databases when files move: Difference between revisions

From Official Kodi Wiki
Jump to navigation Jump to search
>Verboze
No edit summary
 
(30 intermediate revisions by 12 users not shown)
Line 1: Line 1:
{{XBMC wiki toc Inline}}
<section begin="intro" />
__NOEDITSECTION__
Kodi has functions to fully manage its database files. Some advanced operations can be performed by editing the database tables directly on your PC. ''Be forewarned that database tables are relational (inter-related) - do not change any entries unless you are sure of the implications!'' Make a backup first, and worst-case, you just have to delete the corrupt database file and rescan your music, videos, or programs from within Kodi.<section end="intro" />
__NOTOC__
 
XBMC has functions to fully manage its database files. Some advanced operations can be performed by editing the database tables directly on your PC. ''Be forewarned that database tables are relational (inter-related) - do not change any entries unless you are sure of the implications!'' Make a backup first, and worst-case, you just have to delete the corrupt database file and rescan your music, videos, or programs from within XBMC.
If you are using a shared mySQL Database on a NAS, please follow this guide instead: [[HOW-TO:Update_Paths_In_MySQL]]


'''Required Software'''
'''Required Software'''


Two popular SQLite interfaces for Windows are:
Two popular SQLite interfaces for Windows are:
  * [http://www.singular.gr/sqlite/ SQLite3Explorer]
* [http://sqlitebrowser.org/ DB Browser for SQLite (DB4S), previously known as "SQLite Database Browser" ]
  * [http://www.yunqa.de/delphi/sqlitespy/index.htm SQLite Spy]
* [http://www.yunqa.de/delphi/doku.php/products/sqlitespy/index SQLite Spy]
* ''[http://www.singular.gr/sqlite/ SQLite3Explorer (note: last updated 2010)]''


A capable but unmaintained SQLite client for OSX can be found here:
A capable but unmaintained SQLite client for OSX can be found here:
  * [http://saxmike.com/MySoftware/MySoftware.asp?Menu=MYSOFTWARE Mike T's SQLite Database Administrator Tool]
* [http://saxmike.com/MySoftware/MySoftware.asp?Menu=MYSOFTWARE Mike T's SQLite Database Administrator Tool]
 
A client for Linux (works on other *nix as well):
* [http://sqliteman.com/ Sqliteman]
 
== Assumptions ==


== How-To: Change the root paths for your scanned media ==
Let's say you've got all your music stored on shared folder from a PC or NAS (network attached storage), and you've scanned it into the MyMusic##.db database. You then changed a drive location, a computer name, etc.:


Let's say you've got all your music stored on shared folder from a PC or NAS (network attached storage), and you've scanned it into the MyMusic6.db database. You then changed a drive location, a computer name, etc.:
Copy UserData\Database\MyMusic##.db over to your PC, run SQLite3Explorer, and examine the tables. You'll see that the paths in the table name 'paths', in a field named strPath.


Copy UserData\MyMusic6.db over to your PC, run SQLite3Explorer, and examine the tables. You'll see that all the path info is kept in a single table name 'paths', in a field named strPath.
original location: '''smb://my_nas/old_share/Music/Library/...rest of path/filenames'''


''original location: smb://nas/disk-3/Music/Library/...rest of path/filenames''
new location: '''smb://my_nas/new_share/Music/Library/...rest of path/filenames'''


''new location: smb://nas/disk-1/Music/Library/...rest of path/filenames''
== Understanding SQLite text replacement ==
Here are two alternative ways to update paths in your database; they're presented here for your consideration. You can just generally just follow the instructions in the sections below, however.


Executing an UPDATE query with the SUBSTR function will allow a 'search-and-replace' to occur:
====Using the REPLACE function:====
It's important to realize that REPLACE() will replace ''every'' occurrence of the search string, so it's recommended that you use include the beginning of the URI (like smb://...)


'''''update path set strPath = substr(strPath,1,10) || 'disk-1' || substr(strPath,17,length(strPath)-16);'''''
'''UPDATE path SET strPath = REPLACE(strPath,'smb://my_nas/old_share', 'smb://my_nas/new_share');'''
 
====Alternately, using the SUBSTR function:====
If you want to have guaranteed control over which characters get replaced, you can use the SUBSTR() function.
 
'''UPDATE path SET strPath = SUBSTR(strPath,1,13) || 'new_share' || SUBSTR(strPath,23);'''


You can see that the table named 'path' was updated to change the values in the field named 'strPath':
You can see that the table named 'path' was updated to change the values in the field named 'strPath':
   * we took the first 10 characters of the original string "smb://nas/"
   * we took the first 13 characters of the original string "smb://my_nas/"
   * appended the string "disk-1"
   * appended the string "new_share"
   * appended the 17th-onwards part of the original string to the end: "/Music/Library/..."
   * appended the 20th-onwards part of the original string to the end: "/Music/Library/..."


So, the SUBSTR function returns a substring from an original string, taking these parameters:
So, the SUBSTR function returns a substring from an original string, taking these parameters:
Line 39: Line 52:
And the LENGTH function determines the number of characters in the original string; you can see that in this example how many characters remain after discarding the first 16.
And the LENGTH function determines the number of characters in the original string; you can see that in this example how many characters remain after discarding the first 16.


There's lots of potential here, but you may need to play around a bit to get it right.
== Updating your Music library ==
Open the latest [[Databases/MyMusic|MyMusic*.db]] file in your sqlite program, and run the following command (substituting your paths as appropriate):
  UPDATE path SET strPath = REPLACE(strPath,'smb://my_nas/old_share', 'smb://my_nas/new_share');
 
== Updating your Video library ==
Open the latest [[Databases/MyVideos|MyVideo*.db]] file in your sqlite program, and run the following command (substituting your paths as appropriate):
  UPDATE path SET strPath = REPLACE(strPath,'smb://my_nas/old_share', 'smb://my_nas/new_share');
  UPDATE movie SET c22 = REPLACE(c22,'smb://my_nas/old_share', 'smb://my_nas/new_share');
  UPDATE episode SET c18 = REPLACE(c18,'smb://my_nas/old_share', 'smb://my_nas/new_share');
  UPDATE art SET url = REPLACE(url,'smb://my_nas/old_share', 'smb://my_nas/new_share');
  UPDATE tvshow SET c16 = REPLACE(c16,'smb://my_nas/old_share', 'smb://my_nas/new_share');
  UPDATE files SET strFilename = REPLACE(strFilename,'smb://my_nas/old_share', 'smb://my_nas/new_share');
 
Open the latest Textures*.db file in your sqlite program, and run the following command (substituting your paths as appropriate):
  UPDATE path SET url = REPLACE(url,'smb://my_nas/old_share', 'smb://my_nas/new_share');
  UPDATE path SET texture = REPLACE(texture,'smb://my_nas/old_share', 'smb://my_nas/new_share');


Also, remember to update the paths in UserData\sources.xml; otherwise, you may get an error when you try to access the source via XBMC.
Additionally, if you're updating from a local Windows path to a smb share, you will need to replace backslashes with forward slashes in each of the tables in both databases in order to prevent a future library update from treating the discovered files as different from the existing. For example:
  UPDATE path SET strPath = REPLACE(strPath,'\', '/');


[[category:How To|Database]]
== Updating sources.xml ==
[[category:Inner Workings]]
Edit [[Sources.xml|sources.xml]] in a text editor and do a global search-and-replace for '''smb://my_nas/old_share''' to '''smb://my_nas/new_share'''
[[category:Development]]
[[Category:How-to]]
[[category:Library]]
[[Category:Advanced topics]]
[[category:Video Library]]
[[category:Music Library]]

Latest revision as of 07:40, 4 May 2021

Kodi has functions to fully manage its database files. Some advanced operations can be performed by editing the database tables directly on your PC. Be forewarned that database tables are relational (inter-related) - do not change any entries unless you are sure of the implications! Make a backup first, and worst-case, you just have to delete the corrupt database file and rescan your music, videos, or programs from within Kodi.

If you are using a shared mySQL Database on a NAS, please follow this guide instead: HOW-TO:Update_Paths_In_MySQL

Required Software

Two popular SQLite interfaces for Windows are:

A capable but unmaintained SQLite client for OSX can be found here:

A client for Linux (works on other *nix as well):

Assumptions

Let's say you've got all your music stored on shared folder from a PC or NAS (network attached storage), and you've scanned it into the MyMusic##.db database. You then changed a drive location, a computer name, etc.:

Copy UserData\Database\MyMusic##.db over to your PC, run SQLite3Explorer, and examine the tables. You'll see that the paths in the table name 'paths', in a field named strPath.

original location: smb://my_nas/old_share/Music/Library/...rest of path/filenames

new location: smb://my_nas/new_share/Music/Library/...rest of path/filenames

Understanding SQLite text replacement

Here are two alternative ways to update paths in your database; they're presented here for your consideration. You can just generally just follow the instructions in the sections below, however.

Using the REPLACE function:

It's important to realize that REPLACE() will replace every occurrence of the search string, so it's recommended that you use include the beginning of the URI (like smb://...)

UPDATE path SET strPath = REPLACE(strPath,'smb://my_nas/old_share', 'smb://my_nas/new_share');

Alternately, using the SUBSTR function:

If you want to have guaranteed control over which characters get replaced, you can use the SUBSTR() function.

UPDATE path SET strPath = SUBSTR(strPath,1,13) || 'new_share' || SUBSTR(strPath,23);

You can see that the table named 'path' was updated to change the values in the field named 'strPath':

  * we took the first 13 characters of the original string "smb://my_nas/"
  * appended the string "new_share"
  * appended the 20th-onwards part of the original string to the end: "/Music/Library/..."

So, the SUBSTR function returns a substring from an original string, taking these parameters:

  * original string to operate on
  * position of first character to extract
  * total number of characters to extract

And the LENGTH function determines the number of characters in the original string; you can see that in this example how many characters remain after discarding the first 16.

Updating your Music library

Open the latest MyMusic*.db file in your sqlite program, and run the following command (substituting your paths as appropriate):

  UPDATE path SET strPath = REPLACE(strPath,'smb://my_nas/old_share', 'smb://my_nas/new_share');

Updating your Video library

Open the latest MyVideo*.db file in your sqlite program, and run the following command (substituting your paths as appropriate):

  UPDATE path SET strPath = REPLACE(strPath,'smb://my_nas/old_share', 'smb://my_nas/new_share');
  UPDATE movie SET c22 = REPLACE(c22,'smb://my_nas/old_share', 'smb://my_nas/new_share');
  UPDATE episode SET c18 = REPLACE(c18,'smb://my_nas/old_share', 'smb://my_nas/new_share');
  UPDATE art SET url = REPLACE(url,'smb://my_nas/old_share', 'smb://my_nas/new_share');
  UPDATE tvshow SET c16 = REPLACE(c16,'smb://my_nas/old_share', 'smb://my_nas/new_share');
  UPDATE files SET strFilename = REPLACE(strFilename,'smb://my_nas/old_share', 'smb://my_nas/new_share');

Open the latest Textures*.db file in your sqlite program, and run the following command (substituting your paths as appropriate):

  UPDATE path SET url = REPLACE(url,'smb://my_nas/old_share', 'smb://my_nas/new_share');
  UPDATE path SET texture = REPLACE(texture,'smb://my_nas/old_share', 'smb://my_nas/new_share');

Additionally, if you're updating from a local Windows path to a smb share, you will need to replace backslashes with forward slashes in each of the tables in both databases in order to prevent a future library update from treating the discovered files as different from the existing. For example:

  UPDATE path SET strPath = REPLACE(strPath,'\', '/');

Updating sources.xml

Edit sources.xml in a text editor and do a global search-and-replace for smb://my_nas/old_share to smb://my_nas/new_share