Media Browser, SQLite and how to avoid losing cache on new builds.

By | April 20, 2010

I’ve been compiling my own builds of Media Browser ever since a repository was made available to me.  For the majority of that time, the majority of the issues I had to contend with dealt with the expected bugs of untested code.  However, many months ago, the caching system was introduced in order to speed up the loading of our libraries.  This was a very welcomed edition as it tremendously sped up the loading of my collection, which consists of somewhere over 10,000 individual movie files (not to mention the image and metadata files that accompany them).  What this introduced was a new issue, unfortunately.  It doesn’t happen that often, but when it does, it can make upgrading a nightmare when you depend on the watched status that is one of Media Browser’s media management features.

Every once in a while, a change to the underlying code renders all, or most, cached items no longer valid.  This usually is a result in the hashes that are calculated due to a new way cache is handled or, I can only assume, when a tracked value is added or modified.  This normally does not affect playstate and/or the display preferences of the cache, but if you’re like me and use the fairly new SQLite caching functionality, it can be disastrous.  No longer can you just delete the contents of your ImageCache, children, items and providerdata folders – you have a single little cache.db file that secretly holds all of this information.  When those contents become invalidated, you can end up with some real funky results when running Media Browser again.

So, what can we do about this?  Live with it?  Wait until a new system is put into place that protects our precious cache?  Well, those were not answers for me and with the latest build (edits made 4/19), my watched status was lost for, hopefully, the last time.  I am going to show you what to do so that you too can save your watched status and display preferences even after your cache has become invalidated while using the experimental SQLite functionality!

I will outline this step-by-step:

  1. Close Media Center and backup your cache.db file, located in C:\Programdata\MediaBrowser\Cache, to a safe location.  A simple copy and paste to your safe location will do.
  2. Browse into your C:\Programdata\MediaBrowser\ImageCache directory and delete everything.  More than likely, the majority of your ImageCache has become invalidated also, so there’s no sense in keeping it.  Depending on the size of your collection, this could be over a GB of space saved.  CTRL+A then Delete will make quick work of it.
  3. Download SQLite Administrator from here.  Provided for free by Orbmu2k.  It will be a zip file without an installer, so just extract wherever you can easily get to it.
  4. Run the SQLite Administrator application.
  5. Click the Database menu item and choose Open.  Browser to C:\Programdata\MediaBrowser\Cache and choose to Open the cache.db file.
  6. All available Tables and whatnot will be displayed in the left-hand window of the application.
  7. We want to completely drop the tables that have become invalidated so that they will get repopulated with valid data.
  8. Right-click on the children table and choose Delete Table.  Repeat for:
  9. items
  10. provider_data
  11. You will notice that entries under Indexes will also disappear – this is normal.
  12. You should now have only two Tablesdisplay_prefs and play_states.  There will also be additional “folders”, but we won’t concern ourselves with those.
  13. You can now click on Database and choose Exit to close the application.  You’re done!
  14. Open Media Center, start up Media Browser and let it start repopulating your cache!

Please keep in mind, this is not a fix.  This merely provides a way for you to retain the coveted watched status and also your display preferences.  All of your images and metadata will need to be fetched (whether by local or Internet sources), so it may take a while for it to complete.

25 thoughts on “Media Browser, SQLite and how to avoid losing cache on new builds.

  1. tylor

    Excellent post Jon, I have been looking for something to save the playstate in the new cache for a while.

    Works great

  2. Sinjen

    Hmm…what about an article showing a commandline version of some tool with an example of how to export and import a table?? :)

    You may already have thought of this, but if we had a commandline tool available, we could use it to schedule synchronizations of the Playstate table! Yes, we could just sync the entire cache.db on a schedule in the middle of the night. But, I’d rather just sync the playstate and I’m more than willing to deal with the, once a night sync, that this solution would provide. It’s not ideal, but until there’s a better solution this would be acceptable to me. I’d finally be able to actually use SQL lite.

  3. Sinjen

    In my previous comment, I failed to be clear in what I want to do. I want to add shared playstate support when using SQL lite to all my machines by synchronizing the playstate table at a scheduled time.

    1. Jon Post author

      I knew what you meant :)
      I haven’t looked into it much, but the possibility did enter my mind when playing around with this. The SQLite shell (sqlite3.exe) can handle passing commands to do this, but I don’t know how it will stand up. Oddly enough, I’ve already seen my movies watched statuses completely disappear…even more odd is that my TV series watched statuses are still intact.
      Anyway, I’ll take a look at the shell this week and see what I can cook up if you, or someone else, doesn’t do it first.

  4. tylor

    I would also be very interested in this Sinjen… I have been missing that option for sometime while using sqlite

  5. Jon Post author

    OK, so I have created a series of scripts that this has worked on so far, but I have not done any “live” testing on whether or not Media Browser works after doing the SQL commands. I’m at work and while I do have Media Center here, I haven’t created any kind of dummy environment. Hopefully, I’ll have some results tomorrow and I’ll give you what I came up with.

    1. Sinjen

      Email them to me and I’ll run through some tests if you’d like.

      1. Jon Post author

        OK, I sent it out. It’s a pretty simple script and there should be no problems as long as shares are setup well. Let me know if there are any glitches…I’ve only tested as far as exporting and importing the table goes.

    1. Jon Post author

      I’ll send to you too if he Sinjen says it works well for him. It’s a little clunky, but very simple, so setting as a scheduled service or running via a standby/wake utility it should work fairly well.

      1. Sinjen

        I haven’t been using SQL lite (except on a test machine), so tonight I’m getting a couple machines setup for it. I’ll let you know how it goes, hopefully, tomorrow. Thanks Jon!


  6. Sinjen

    Btw, I don’t want this to turn into a huge project or anything, but if you happen to have a moment of clarity on a possible method to merge two separate SQL lite playstate tables…I’d be REALLY interested in that :)


    1. Jon Post author

      You can send me both cahe files and I can take a look. Not hard to merge, but I don’t know what or if it will break stuff.

      1. Sinjen

        After thinking about this, a simple merge wouldn’t do it. We’d need a comparison merge based on the Last Played date for watched state. And then for watched position you would also need to compare the position ticks. It gets complicated really quickly :(

        1. Jon Post author

          Oh, I think I see what you mean. Sounds like you hop around too many rooms to finish one show :)

          1. Jon Post author

            I’m about to get my third under 5…I feel a lot more of your pain lol

  7. Sinjen

    Ok, I got Jon’s scripts up and working. Sheesh I hate windows file sharing in Vista…

    At any rate, this works. What I did:

    I configured two machines to use SQL lite and then let them re-build their cache completely. I then opened Media Browser and marked a bunch of episodes as watched and then picked one and started playing it, fast forwarding several minutes into the ep. I then exited Media Browser on both machines and ran Jon’s scripts from the machine I used to mark the eps as watched. The script completed normally. I added a pause for testing just to make sure I could see what happened on the first run.

    I then opened MB on the target system and checked the watched status of the eps I marked in the other room. Bam, they were there. I then checked the episode I started playing and it started playing exactly where I left off in the other room. Very cool.

    To be clear…all we’ve done so far is clone the watched/play state from one machine to another. I see this only being used to share the playstate of say, your main HTPC to a bedroom or something. I’m not diminishing how cool it is to at least regain SOME shared playstate capability. I just didn’t want anyone reading this to think this was the total answer to shared playstate.


    1. Jon Post author

      Definitely not. In fact, if it’s for only two identical systems that are never on at the same time, using mklink and sharing a common network cache.db works pretty well. It’s what I’m doing now, but ensuring the database has no chance of becoming locked is the key…if that happens, corruption is pretty much inevitable from what I saw in the past in doing that.

      Even though the script was configured for one source and one target, it may be better suited for multiple target systems.

  8. binaryronin

    Hi Jon,

    Thanks for giving such strong support to the mediabrowser project!

    From some of your previous posts, I am hoping that you will be able to answer my questions. They all pertain to the SQL feature of Media Browser.

    Forgive me for having to ask these questions, I have only been using MB for a month or two now, and I have been leery of setting up the SQL in MB due to the ‘experimental’ status, although while in my search for these answers, I can see that the ‘experimental’ status is not quite so, and I am likely to set my MB up to take advantage of it. Especially since my 14TBs of movies\shows takes forever to process into MB when it starts.

    1) How does the sql database get its data? I am using Media Center Master to fetch metadata, and it stores the relevant data in mymovies.xml files and images in the movie file folders. Does it poll these files on a scheduled basis while MB is running, or just when Media Browser starts a session?

    2) I am working on a setting up a website that will (in its first iteration) poll the mymovies.xml files to populate pages with the meta-data. My primary goal is to simply display all data so that I can have a web based movie catalog for friends to view. My secondary goal is to set it up in such a way that I can click an edit button and then edit incorrect\missing meta-data and have it saved to the mymovies.XML files. I am also considering setting the website up (in its second iteration) as a MySQL DB that updates from the xml files. I haven’t decided because I also want to use the XML data to create a printable (i.e. PDF) index of my movies using the XML-FO syntax. Though, I can simply export the XML on the fly.

    So here’s the question: do you know if the MB SQL DB contains “everything” in the mymovies.xml file? I’m thinking that if I can simply copy tables from the cache.db file, then it will save me some processing time on the backend. I also like the idea of being able to include my “watched” status in the website.

    3) I’m thinking that I may be able to set things up in such a way that when I edit meta-data on the website, I just inject directly into the MB DB. What do you think?

    4) FYI, I am also toying with the idea of setting up the website as a one stop shop for all my movie needs. What I mean here is that in addition to simply displaying my movie collection as it is, I am thinking of trying to include the relevant data (torrent file, date added, date completed, share ratio, etc.) from uTorrent for the one or two movies I get from that source, as well as an auto-search feature to auto-magically download meta-data and torrents for movies in a wish list. Although to be quite honest, this is probably far too ambitious for me right now, for starters, I have no idea how to handle the BEencoded data such as uTorrent uses.

    Good God, that was a long post, sorry!


    1. Jon Post author

      1) The database is just another means of storing cached metadata…that’s all it is. Instead of storing it in the usual Cache directory subfolders as hash files, it’s just populating tables in the database. Database access is faster than flat file access, so that’s where the advantage comes from. Images are still maintained in the ImageCache directory. Playstate and display are no longer stored in the database.
      2) I am pretty certain “everything’ that’s in a mymovies.xml file will NOT be found in the database. Media Browser ignores data that it is not specifically designed to cache/use.
      3) I don’t see why not. The data is not in plain text, however, so you will have to figure out the hashing translation. It’s not encrypted, though.
      4) Got me there :)

      No worries…hope I was able to shed a little light on things for you.

  9. binaryronin

    Thanks for the reply Jon,

    That really does help, for the most part it looks like I will have to continue using data from the mymovies.xml files, the cache.db probably isn’t what I was hoping. It would have been great if the DB was a full DB that stored everything about the collection, but I guess for the developers to do that would probably mean two distinct development threads. Oh well. At least I will still be able to use my own DB for the website.

    Thanks again!


Leave a Reply

Your email address will not be published. Required fields are marked *