Stream audio from SQLite db

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?

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.

If you are storing the actual audio data in the database in a format that the Android audio system can natively interpret (i.e. mp3, 3gpp, ogg) then one way you could do it is to implement a web server in a Service, and have that Service open the SQLite database, fetch the blob using Cursor.getBlob and then feeding that Blob out to a MediaPlayer instance through the web server by wrapping the byte array with a ByteArrayInputStream. I've seen implementations like this done (in those cases it was from file, not db but the same principles apply).

Alternatively you could use AudioTrack, translate the audio if its not in PCM format and then play it and handle the audio management yourself: probably a lot more work but more efficient.

Note that this will be EXTREMELY memory-intensive and will probably perform poorly: for a 5mb MP3 you'd basically have to hold the entire thing in memory since it doesn't appear that Android's SQLite interface gives you a stream interface to blobs. If you are loading multiple media files, then...bad things happen.