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

From Official Kodi Wiki
Jump to navigation Jump to search
>NedBot
>NedBot
m (Bot: Removing from Category:Inner Workings)
Line 44: Line 44:


[[Category:How-to|Database]]
[[Category:How-to|Database]]
[[Category:Inner Workings]]
[[Category:Development]]
[[Category:Development]]
[[Category:Library]]
[[Category:Library]]
[[Category:Video Library]]
[[Category:Video Library]]
[[Category:Music Library]]
[[Category:Music Library]]

Revision as of 22:37, 26 November 2011

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.

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):

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 MyMusic6.db database. You then changed a drive location, a computer name, etc.:

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://nas/disk-3/Music/Library/...rest of path/filenames

new location: smb://nas/disk-1/Music/Library/...rest of path/filenames

Executing an UPDATE query with the SUBSTR function will allow a 'search-and-replace' to occur:

update path set strPath = substr(strPath,1,10) || 'disk-1' || substr(strPath,17,length(strPath)-16);

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/"
  * appended the string "disk-1"
  * appended the 17th-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.

There's lots of potential here, but you may need to play around a bit to get it right.

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.