Archive:Cleaning Up Database

After some months (or even years) of using XBMC it is very likely that the database is filled with entries that one cannot get rid of easily. Apart from cleaning up the database with XBMC's own menu entry ([Video|Music] - Library - clean library) there seems to be no way to reset some entries without losing valuable information about existing items.

Also there seems to be no way to move files in a comfortable way without making XBMC throw away the old entries and regard the moved files as new ones.

This is going to be a compilation of Tips & Tricks concerning editing the database directly to get rid of old, orphaned or erroneous entries. Additions are welcome! All entries should be generalized in a way that every user may follow the steps; i.e.: no absolute paths, no exact software to use, no self-edited scripts except there is a link to the script included. If a tip presumes a special environment like "Linux Live" or "XBOX" this must be remarked in the title.

Preliminary to all tips

• Make a backup of your databases. Better: make a backup of your whole xbmc-directory.
• If you don't really understand what to do: don't do it, ask first
• if you can't reckon the actual effect of a tip: try it with a copy of the database. Using a copy you can do anything without risk.
• It's advised to use a graphical sqlite-frontend. Editing the database using a commandline is for experienced users only, as queries and updates may fail silently.
• It is presumed that you know about the structure of your installation of xbmc. Especially where to find which file or at least how to search for it.

Change or reset the scraper for a path or a complete directory hierarchy

Change or reset the scraper for a directory (hierarchy) without editing each path's properties within XBMC.

1. Open database "MyVideos34.db"
2. Open table "path"
3. search for columns "strPath" and "strScraper"

The first column contains the path, the latter column contains the used scraper for this path.

Don't delete a complete row! If you do so, you will mangle your database as the table "files" links to the entries in the table "path"! If you want to savely delete a complete path from the database do it within XBMC.

Have a look at the column "strPath" and search for the path(s) you want to modify. Edit or delete the corresponding value in the column "strScraper". Deleting the value results in setting this path back to the value of the next upper level path (i.e. deleting value for myfiles/myvideos/ makes XBMC use the same value as for myfiles/). When editing the value you need to make sure that the new value represents a valid scraper for this mediatype. Either have a look at the other entries or search for the exact scraper's name in the accordingly directory. Follow the exact notation of the scraper's xml-file (no preceeding path!) and use this name (including '.xml') as the new value. Example: "imdb tv.xml"

This should work with the database "MyMusic7.db" too, but I didn't test it. Try at your own risk!

Change the scraper for various paths at once

You want to completely change to a new / alternate scraper but have lots of paths, every one with it's own scraper settings? Here we go:

1. Open database "MyVideos34.db"
2. Open table "path"
3. check for the actual settings in column "strScraper"; this is your <old value>
4. get to know the exact filename of the new scraper; this is your <new value>

Next step depends on the abilities of your sqlite frontend.

A: you can enter SQL commands directly

Open the SQL editor and make sure that you are working with the correct database. Formulate a query like this and only replace the values inside the brackets with your values, everything else is already correct (don't delete the single quotation marks):

UPDATE path SET strScraper = '<new value>' where strScraper = '<old value>';

This one replaces each occurrence of <old value> with <new value>. If you just want to modify a directory <parent path> and all of its subtree, you have to extend the query with another restriction like this one (additional to the above, but delete it's semicolon):

AND strPath LIKE '<parent path>%';

Bear in mind that the LIKE operator is case sensitiv. As a result you should either see a message telling you how many rows were affected (i.e. updated) or you can update your database browser and look at the corresponding rows.

B: you can enter a query using a form

If your sqlite-frontend offers some kind of a form-based, graphical editor, be sure to select the correct database and table. Either change the value for the field "strScraper" in every row seperately or formulate a query and a replacement string so that the values reflect the old and the new scraper's name.

UNTESTED: change the path for moved items

This is still untested. It should work but there is no guarantee. It would be fine if some of the XMBC developers could verify that there are no other dependencies within the database structure concerning the path of a file

You have a directory tree containing media items in several subdirectories. Now you want to move all items into one directory but of course don't want to lose all of the information XBMC already collected. If you'd just move the items and rerun the library update within XBMC, all previous settings would get lost and all the media would be recognized as new. To prevent XBMC from doing that try this one (it is assumed that you either dind't move the files already or at least didn't run XBMC's library update after moving the files):

1. Open database "MyVideos34.db"
2. Open table "path"
3. Open table "files"

In table "path" search for the paths you want to get rid of. For each path note the value of the correpsonding entry in column "idPath". This is your <old value>.

In table "files" search for all entries which have <old value> as entry in column "idPath". Cross-check the results with the content of the actual directory. All entries really should reside in the path you want to delete. Double ckeck the results! Changing the wrong entries would lead to exact that point that you wanted to avoid: newly added items instead of moved ones. When everything looks fine, search for the entry representing the target path. The value of this entry's field "idPath" is your <new value>.

Now open an SQL editor and formulate a query like this one:

UPDATE files SET idPath = '<new value>' where idPath = '<old value>';

If everything was correct, XBMC should use your old files in the new path without rescraping them.