Archive:Cleaning Up Database

THIS PAGE IS OUTDATED: This page or section has not been updated in a long time, no longer applies, refers to features that have been replaced/removed, and/or may not be reliable. This page is only kept for historical reasons, or in case someone wants to try updating it.

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.

[VIEW] Recently seen

This tip doesn't clean up the database but might help in keeping an eye on the already seen items. Proceeding the steps below you may add a VIEW to your library that displays the already watched items sorted by date. This view doesn't show up in XBMC at all, except for some skinner implements a special page in his skin. But you can always read this virtual table with an appropriate application. There might be skins that already offer this functionality but I don't know any. And this little hack offers the ability to see the "playlist" from outside of XBMC too. Alternatively there is a ((linux-)shell-)script that simply queries the library without any modifications to it and puts the result into a textfile.

Some words on VIEWS in a database: A VIEW is a special kind of table which is more or less a kind of a saved database-query (in fact, it's not a table, it's a VIEW). Think of it as a shortcut to a long and complicated query. Once it's set up, instead of handling different tables, fields, comparisons or even JOINS or other fuzzy stuff you can just do a SELECT * FROM <VIEWNAME> and you're done. This saves a lot of time! But not only you can shorten an often needed query, you also can just define an alternate listing for the stuff in a single table. And the best of all: once a view is set up, it doesn't change the database at all (OK, many database-eninges offer the ability to make a VIEW updatable, but sqlite doesn't). Last not least, you can use a VIEW in nearly every query you set on the database, you can combine the data with other VIEWS or select a subset of the VIEW based on standard criteria. After all, it is a quite powerful method for working with databases. I love VIEWS! :)

This view offers three simple datafields representing the path, the filename and the date of last playing of the item. The latter one might be emtpy as it is a quite new feature and old entries in the database might not have an appropriate timestamp set. But you can be sure that every item listed by the VIEW is already played (or seen or whatever you may call it), i.e. has a playcount of one or more.

1. Open database "MyVideos34.db"
2. Open SQL-Editor (some editors have a special item for generating a VIEW you should then split the command below and use the right peaces at the right place)
3. execute the following command (if you don't like the name "_myplaylist" you may choose another one but be careful not to conflict with standard xbmc tables. Preceeding with an underscore is a quite good idea to mark own stuff):

CREATE VIEW IF NOT EXISTS _myplaylist AS 
SELECT strPath, strFilename, lastPlayed FROM path, files 
WHERE path.idPath = files.idPath AND playCount NOT NULL ORDER BY lastPlayed DESC;

Now you should be able to query this VIEW like this: select * from _myplaylist; That's it.

Alternately you can use this script from a commandline (bash is recommended) and have the list output to a Textfile. Be sure to check the settings on top of the file and change them to your needs.

#!/bin/bash
#
# XBMC PlayList
# v1
#
# created by BaerMan for XBMC-community
# This script may be used for any purposes.
# You may change, sell, print or even sing it
# but you have to use it at your own risk!
#
# This script is ugly and may under certain circumstances crash your
# computer, kill your cat and/or drink your beer.
# Use it at your own risk!
#
# This script lists the already played items, ordered by playtime

################
### Settings ###
################

### Full path to the video-database ; may be absolute (preceeded by a
### slash "/") or relative form the current directory
DBPATH="/home/xbmc/.xbmc/userdata/Database/MyVideos34.db"

### Filenames for results and intermediate data
### You may change these to any name and place you like but beware not to
### overwrite or delete files you may still need
PREFIX=/home/xbmc/xbmc_
ITEMLIST=${PREFIX}playlist.lst

### Programs used ; either absolute path or command only if path to the
### binary is in variable $PATH ; each command may be extended by optional
### arguments - refer to the specific manpage for details
SQLITECMD="sqlite3" ; RMCMD="rm"

####################
### working code ###
####################
${RMCMD} ${ITEMLIST} 2>/dev/null

${SQLITECMD} -list -separator '' ${DBPATH} \
 "SELECT strPath, strFilename, ' - ', lastPlayed FROM path, files WHERE path.idPath = files.idPath AND playCount NOT NULL ORDER BY lastPlayed DESC;" \
  > ${ITEMLIST}

more (or less) useful stuff regarding recent playlist

One can scratch pretty fine playlists for every month for example:

SELECT * FROM _myplaylist WHERE date(lastPlayed) BETWEEN date('2009-11-01') AND date('2009-11-30');

Or using the predefined VIEW "movieview" selecting the items of the last three months with a rating greater than 6, ordered by rating:

SELECT strFileName, lastPlayed, c05 AS rating FROM movieview WHERE date(lastPlayed) > date('now', '-3 months') AND rating > '6' ORDER BY rating DESC;

It should be obvious where to edit the lines to select another month or rating.