Stream audio from SQLite db

Question

I have an SQLite db, and it has audio files in it stored as blobs.

Is it possible in android (or anywhere) to stream media from a db?

Answer 1

I would recommend not storing the audio data in the database. The memory issues mentioned earlier can lead to huge amounts of GC thrashing which can make the system non-responsive for seconds or more at time.

The typical approach involves a handful of steps.

1. Store the audio in a file somewhere in your application's directory.

2. Create two columns in your database. One column (called anything you like) contains a "content://" URL that references the data. Seeing a "content://" URL is a trigger to the system to then look up the contents of the "_data" column in the same row. The contents of that column should be the full path to the file.

3. The system then transparently reads that file, and presents it to whichever code actually requested the content.

I've got some example code for doing this with images -- obviously, it's not quite the same, but I can walk through it here and you should get the gist.

The specific problem I was trying to solve was storing album artwork for a track that's stored off the device. I wanted to be able to show the album artwork in a list, and cache it locally on the device so that repeatedly scrolling through it is fast and does involve repeated network fetches for the same data.

I have an albums database, with various columns that get lazily populated from a remote server. I implement this database using the ContentProvider framework. There's a lot of great information about ContentProviders at http://developer.android.com/guide/topics/providers/content-providers.html, and you should read that first so that the rest of this makes sense.

The files involved are (note: I've linked to specific points in the tree because this is a work in progress and I want the line number references I give you to be stable):

https://github.com/nikclayton/android-squeezer/blob/02c08ace43f775412cc9715bf55aeb83e7b5f2dc/src/com/danga/squeezer/service/AlbumCache.java

This class defines various constants that are used elsewhere, and is pretty idiomatic for anything that's implemented as a ContentProvider.

In this class, COL_ARTWORK_PATH is the column that's going to contain the content:// URL.

https://github.com/nikclayton/android-squeezer/blob/02c08ace43f775412cc9715bf55aeb83e7b5f2dc/src/com/danga/squeezer/service/AlbumCacheProvider.java

This is the implementation of the ContentProvider. Again, this is pretty idiomatic for ContentProviders that are wrapping SQLite databases. Some points of interest:

429: albumListCallback()

This code is called whenever the app receives data about an album from the remote server (that's specific to my app, and not relevant to your problem). By this point the data has been wrapped up as a list of SqueezerAlbums, so this code has to unpack that data and turn it in to rows in the database.

456: Here we call updateAlbumArt with enough data that it can do a remote fetch of the album artwork (and I've just realised looking at this code that I can make this more efficient because it's updating the database more often than it should. But I digress).

475: updateAlbumArt()

This has to fetch the remote image, resize it, store both the original and resized versions in the filesystem (why both? Because I haven't finished this, and there will be code to select the correct cached size later).

This creates a cache directory as necessary, downloads the remote image, resizes it, and saves it to the files.

535: This is the bit you're probably particularly interested in. This creates a content:// URL (using the constants in AlbumCache.java) that references the data, and puts that in COL_ARTWORK_PATH. Then it puts the absolute path to the file in the _data column.

571: openFile()

You must implement this. Users of the ContentProvider will call openFile() when they want to open the file in the database. This implementation uses openFileHelper(), which is the code that looks up the value in the _data column, opens that file, and returns a ParcelFileDescriptor to the caller.

As you may just have realised, your open implementation of openFile() doesn't have to do this -- you could use another column name, or perhaps you have a way of going straight from the URL to the file in the filesystem. This does seem to be a very common idiom though.

Assuming you've done something like that, and now have a ContentProvider for your database, to actually access the image, your application will need to have generated a URI that references a given piece of content by it's ID. The code in the app to open the file looks like this:

Inputstream f = this.getContentResolver().openInputStream(theUri);

which ends up calling your implementation of openFile(), which ends up calling openFileHelper(), which finally gets to the right file in the filesystem. One other advantage of this approach is that openFile() is called in your application's security domain, so it can access the file, and, if implemented correctly, your ContentProvider can be called by completely different applications if you make the URLs that it responds to publicly known.