getting video info out of flipshare.db

WARNING: this shows an implementation detail of FlipShare 5.0, NOT a public interface – it could break with upcoming versions

Goal – get the info directly from flipshare.db.

As has been noted in many places, FlipShare uses a SQLite database for storing its data.  When you add titles (or, well, change them from “Untitled” to something else), it doesn’t modify the names of your video files on disk, it just updates a row in the database.  Same for moving to a new logical folder within FlipShare (and lots other data changes, of course). It’s a great decision on their part, IMHO, even if it makes interacting with your videos in the filesystem a bit more painful 🙂

If you’re trying to interact with the actual videos on disk, you’ll notice there’s no description in them at all – look under Videos\FlipShare Data\Videos and you see VIDnnnnn.mp4 files (where nnnnn appears to be a normal monotonically increasing integer, at least at first glance unrelated to any of the PK id’s in the database).

Since we want to upload our videos with the (arguably, meta)data that’s shown in the FlipShare UI (logical folders, titles, etc), we need to access that flipshare.db sitting in your Videos\FlipShare Data folder.  We could use FlipShare’s export feature, but that would mean taking more disk space than required and we’d have to re-export if we wanted to ‘sync out’ changes to titles, and AFAICT it wouldn’t reflect the logical folders (at least not automatically), just the titles.

Schema investigation

The closest I found to an existing schema explanation was this post which admittedly does give you the 3 tables that hold the pieces of data you need – the title, the video location on disk, and (kinda optional if you just want to upload the video by title) the logical folder it’s in.

The problem for me was figuring out how to join these together.  SQLite supports foreign keys (well, at least 3.x does, not sure if that’s new though), but flipshare.db doesn’t use them, so I started to look at the data in my db’s tables to see if I could just notice it from the data.  The 2 tables I cared the most about were MediaElement and MediaElementSource since they hold the title and video file path, respectively.

  • I looked at the data of each to try and find the join condition, but nothing matched up.
    • so, it’ll be more than a simple 2-table join
  • At this point I wanted to look for a simple mapping table (so, a 3-table join)
    • I picked a particular video in MediaElement based on title,
    • then looked it up in the FlipShare UI to get its length
    • then sorted the VID*.mp4 files by length and looked to find the right video (the one that matches what’s show in the FlipShare UI)
    • then found the MediaElementSource row that matched up
    • Now I had the PK values for the 2 tables I cared about for a single video.
    • Now I looked for data in the database (using .output and .dump in sqlite3.exe) that referenced those 2 values (in either order, of course), hoping there would be a single mapping table involved.  No such luck.
  • At that point, knowing it must be a join of more than 3 tables, I gave up on trying to inspect my way to the join conditions.
  • I did findstr /sim in the Program Files\Flip Video\FlipShare to find which places referenced those tables, which had hits for Core.dll and FlipShare.dll
  • Since Core.dll seemed the more likely place for the DAL I then ran strings.exe on the file, dumped it out to a text file, then started searching for the table names in the output.
  • I found a query that joined MediaElementSourceGraph (which I had already noticed during the manual schema inspection was linked to MediaElementSource by its mediaSourceId – nice, friendly FK name, even if not defined strictly as a foreign key) and MediaElementRendition (which I had similarly noticed was linked to MediaElement via mediaElementId, also clearly an FK name).
  • So, the final 4-table join would link MediaElementSource to MediaElementSourceGraph to MediaElementRendition to MediaElement
  • In retrospect, the schema makes sense, of course – I just couldn’t see the forest for the trees 🙂
  • As a bonus, we could add in the logical folder location for the video, which was much easier to figure out since UserFolderMediaElements is a simple mapping of videos (MediaElement rows) to logical folders (UserFolders rows) as you could tell by its name, so it was just adding those 2 more tables (6 total) to get all the info we’re looking for.

Building the query

Using LinqPad 2.x with the IQ driver (so we can query SQLite) we can use linq’s support for joining (even without foreign keys in place).  It defaults to a couple of common conventions I’ve seen in other OR mappers (linq-to-X and otherwise), making the table names plural (but not the row entity singular if the table is already plural, oddly enough IMHO) and init-cap’ing the property names.  You could certainly turn those off, the query would just need to be slightly different.

I hit one gotcha while constructing the query and adding in the 2 folder-related tables, though:

image

It took me awhile to figure out because I incorrectly took the error message to mean it couldn’t figure out the type of ‘folderElem’ (and adding the explicit type didn’t change the error, of course :), but I eventually figured out it was because of a type mismatch between the members involved in the join condition (elem.Id equals folderElem.MediaElementId).  The UserFolderMediaElements table, oddly enough, doesn’t store the mediaElementId column as an int or similar numeric column – instead, it’s a string.  Not sure why that is (perhaps hysterical raisins), but it is.  Fixing it was simple enough, though – just ToString the elem.Id so it’s comparing 2 strings.  (You can’t use Convert.ToInt32 on the folderElem.MediaElementId since that method isn’t supported by the driver, at least not yet 🙂

One optional where clause (commented out below) is checking the folder’s ParentId – for the FlipShare logical folders (under their ‘Computer’ node), the parent id is 8 (at least in my database 🙂 – i don’t see this in another table, so I’m guessing it’s a defined constant in the code (looks like folder id’s under 1000 are likely that way).  I don’t need this at the moment since I don’t have any videos in other areas (like the ‘Flip Channels’ node), but if you do, you might want that filter.

LINQ Query to get the video info

from source in this.MediaElementSources
    join graph in this.MediaElementSourceGraphs
        on source.Id equals graph.MediaSourceId
    join
rend in this.MediaElementRenditions
        on graph.RenditionId equals rend.Id
    join elem in this.MediaElements
        on rend.MediaElementId equals elem.Id
    join folderElem in this.UserFolderMediaElements
        on elem.Id.ToString() equals folderElem.MediaElementId
    join folder in this.UserFolders
        on folderElem.Id equals folder.Id
//where folder.ParentId == 8
select new
{
    elem.Name,
    elem.PreviewImagePath,
    source.Uri,
    rend.VideoFormat,
    folder.FolderName,
}

NOTE: the VideoFormat isn’t really all that useful to have in the output, I had just included it because I was curious, but feel free to kill it if you don’t need it.  It’s “free” in that you have to join on MediaElementRendition anyway, of course 🙂

SQL Query to get the video info

Since this is LinqPad, I just need to click on the SQL button to get the query that the LINQ query was transformed into, which I include here since it’s more likely to be applicable/usable for people running across this post 🙂

Admittedly, the generated SQL doesn’t have the nicest table alias names, but that’s pretty easy to search-and-replace (or just remove them) if someone wants to 🙂

SELECT t0.[name], t0.[PreviewImagePath], t1.[uri], t2.[videoFormat], t3.[folderName]
FROM [MediaElementSource] AS t1
INNER JOIN [MediaElementSourceGraph] AS t4
  ON (t1.[id] = t4.[mediaSourceId])
INNER JOIN [MediaElementRendition] AS t2
  ON (t4.[renditionId] = t2.[id])
INNER JOIN [MediaElement] AS t0
  ON (t2.[mediaElementId] = t0.[id])
INNER JOIN [UserFolderMediaElements] AS t5
  ON (t0.[id] = t5.[mediaElementId])
INNER JOIN [UserFolders] AS t3
  ON (t5.[id] = t3.[id])

Obligatory Screenshot

Here’s the results from LinqPad, showing it’s the desired output 🙂

image

And, to finish off the post, a repeat of the warning we saw at the beginning, just in case someone skipped down to get the queries 🙂

WARNING: this shows an implementation detail of FlipShare 5.0, NOT a public interface – it could break with upcoming versions

Advertisements

4 thoughts on “getting video info out of flipshare.db

  1. Pingback: James Manning » YouTubeUploader, Google.GData.Client.dll, and the UTF-8 Byte Order Mark

  2. Thanks for this. I’m guessing it took a while? 🙂 I have been wondering why I can’t figure out how to get the files with title and info. Now I know why – no simple task. In your opinion is there a performance reason for this cloak and dagger? Or just a way to make sure we continue to use Flipshare?

    Thanks again.

    • sorry about not getting back to this – i ended up getting iPhone 4’s for myself and my wife, and now I just use some of the existing apps for uploading directly from the phone to various places. It’s a lot simpler than having to sync/copy/move the pictures to a computer and then doing it 🙂

  3. This a great post. It is an excellent starting point for me to migrate all my videos from flipshare to picasa.

Comments are closed.