Stream audio from SQLite db

Question

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

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

Answer 1

I would recommend not storing audio data in a database. The memory problems mentioned earlier can lead to a huge number of GC crashes that can make the system inactive for several seconds or more.

A typical approach involves several steps.

• Save the audio file to a file somewhere in your application directory.

• Create two columns in your database. One column (called anything) contains the URL "content: //", which refers to the data. Viewing the URL "content: //" is a trigger for the system to then view the contents of the column "_data" on the same line. The contents of this column must be the full path to the file.

• Then the system transparently reads this file and presents it depending on what code actually requested the content.

I have an example code for this with images - obviously this is not quite the same, but I can go through it here and you should get the gist.

The specific problem I was trying to solve was to store album art for a track stored on the device. I would like to show the album art in the list and cache it locally on the device, to scroll it many times quickly and require repeated network seizures for the same data.

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

Files used (note: I am associated with specific points in the tree, because this is incomplete work, and I want the links to line numbers I let you be stable):

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

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

In this class, COL_ARTWORK_PATH is the column that will contain the content: // URL.

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

This is an implementation of ContentProvider. Again, this is pretty idiomatic for ContentProviders who wrap SQLite databases. Some sights:

429: albumListCallback ()

This code is called whenever the application receives album data from a remote server (as for my application and does not apply to your problem). At this point, the data was wrapped as a SqueezerAlbums list, so this code should unzip this data and include it in the rows in the database.

456: Here we will call updateAlbumArt enough data so that it can retrieve the album cover remotely (and I just realized by looking at this code that I can make it more efficient because it updates the database more often but I got distracted).

475: updateAlbumArt ()

This is necessary in order to get the remote image, resize it, save both the original and the modified versions in the file system (for some reason, and another? Because I have not finished it yet, and a code will appear to select the correct cached size later).

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

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

571: openFile ()

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

As you may have understood, your open implementation of openFile () should not do this - you could use a different column name, or perhaps you have a way to go directly from the URL to the file in the file system. This seems to be a very common idiom, though.

Assuming you did something similar, and now you have a ContentProvider for your database, in order to actually access the image, your application will need to create a URI that refers to this piece of content by its identifier. The code in the application to open the file is as follows:

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

which ultimately calls your implementation of openFile (), which ends with a call to openFileHelper (), which finally ends up in the desired file on the file system. Another advantage of this approach is that openFile () is called in your application security domain, so it can access the file and, if implemented correctly, your ContentProvider can be called by completely different applications, if you make URLs, on which he answers is well known.

Answer 2

If you save the actual audio data in a database in a format that the Android audio system can use (for example, mp3, 3gpp, ogg), then one way to do this is to implement a web server in the service, and for the Service to open the SQLite database, extract blob with Cursor.getBlob , and then load that Blob into the MediaPlayer instance through the web server byte wrapping ByteArrayInputStream. I saw implementations like this (in those cases it was from a file, not db, but the same principles apply).

Alternatively, you can use AudioTrack, translate the audio if it is not in PCM format, and then play it and handle the audio controls yourself: perhaps a lot more work, but more efficiently.

Please note that this will be extremely memory intensive and will probably work poorly: for a 5 MB MP3 you will basically have to store everything in memory, as it does not seem that the Android SQLite interface gives you a bunch stream interface. If you upload multiple media files, then ... bad things happen.