从 SQLite 数据库流式传输音频
我有一个 SQLite 数据库，其中有存储为 blob 的音频文件。
在android（或任何地方）中是否可以从数据库流媒体？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我建议不要将音频数据存储在数据库中。前面提到的内存问题可能会导致大量的 GC 抖动，从而导致系统有时几秒或更长时间无响应。
典型的方法涉及几个步骤。
将音频存储在应用程序目录中某处的文件中。
在数据库中创建两列。其中一列（任意命名）包含引用数据的“content://”URL。看到“content://”URL 会触发系统查找同一行中“_data”列的内容。该列的内容应该是文件的完整路径。
系统然后透明地读取该文件，并将其呈现给实际请求内容的任何代码。
我有一些用于对图像执行此操作的示例代码 - 显然，它并不完全相同，但我可以在这里浏览它，您应该明白要点。
我试图解决的具体问题是存储存储在设备外的曲目的专辑插图。我希望能够在列表中显示专辑插图，并将其缓存在设备本地，以便反复滚动它速度很快，并且确实涉及对相同数据的重复网络获取。
我有一个相册数据库，其中包含从远程服务器延迟填充的各种列。我使用 ContentProvider 框架实现此数据库。 http://developer.android.com/guide/topics 有很多有关 ContentProvider 的重要信息/providers/content-providers.html，您应该首先阅读该内容，以便其余部分有意义。
因为这是一项正在进行的工作，我希望我给您的行号引用保持稳定）：
涉及的文件是（注意：我已链接到树中的特定点， com/nikclayton/android-squeezer/blob/02c08ace43f775412cc9715bf55aeb83e7b5f2dc/src/com/danga/squeezer/service/AlbumCache.java">https://github.com/nikclayton/android-squeezer/blob/02c08ace43f775412cc9715bf55 aeb83e7b5f2dc/src/com/danga /squeezer/service/AlbumCache.java
这个类定义了在其他地方使用的各种常量，对于作为 ContentProvider 实现的任何内容来说都是相当惯用的。
在此类中，COL_ARTWORK_PATH 是将包含 content:// URL 的列。
https://github.com/nikclayton/android-挤压/blob/02c08ace43f775412cc9715bf55aeb83e7b5f2dc/src/com/danga/squeezer/service/AlbumCacheProvider.java
这是ContentProvider的实现。同样，这对于包装 SQLite 数据库的 ContentProvider 来说非常惯用。一些兴趣点：
429：albumListCallback()
每当应用程序从远程服务器接收有关相册的数据时就会调用此代码（这是特定于我的应用程序的，与您的问题无关）。此时，数据已包装为 SqueezerAlbums 列表，因此此代码必须解压该数据并将其转换为数据库中的行。
456：这里我们用足够的数据调用 updateAlbumArt，它可以远程获取专辑插图（我刚刚意识到查看这段代码，我可以提高效率，因为它更新数据库的频率比应有的要高。但是我离题了）。
475: updateAlbumArt()
这必须获取远程图像，调整其大小，将原始版本和调整大小的版本都存储在文件系统中（为什么两者都存储？因为我还没有完成这个，稍后会有代码来选择正确的缓存大小）。
这将根据需要创建一个缓存目录，下载远程图像，调整其大小，并将其保存到文件中。
535：这是您可能特别感兴趣的部分。这会创建一个引用数据的 content:// URL（使用 AlbumCache.java 中的常量），并将其放入 COL_ARTWORK_PATH 中。然后它将文件的绝对路径放入 _data 列中。
571: openFile()
你必须实现这个。 ContentProvider 的用户在想要打开数据库中的文件时将调用 openFile()。此实现使用 openFileHelper()，该代码查找 _data 列中的值、打开该文件并向调用者返回 ParcelFileDescriptor。
正如您可能刚刚意识到的那样，您的 openFile() 的开放实现不必执行此操作 — 您可以使用其他列名，或者也许您有一种方法可以直接从 URL 转到文件系统中的文件。不过，这似乎是一个非常常见的习语。
假设您已经完成了类似的操作，并且现在数据库有了一个 ContentProvider，为了实际访问图像，您的应用程序需要生成一个 URI，通过其 ID 引用给定的内容片段。应用程序中用于打开文件的代码如下所示：
最终调用 openFile() 的实现，最终调用 openFileHelper()，最终到达文件系统中的正确文件。这种方法的另一个优点是 openFile() 在应用程序的安全域中调用，因此它可以访问该文件，并且如果实现正确，如果您创建了它响应的 URL，则您的 ContentProvider 可以由完全不同的应用程序调用为公众所知。
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.
Store the audio in a file somewhere in your application's directory.
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.
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:
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.
如果您以 Android 音频系统可以本地解释的格式（即 mp3、3gpp、ogg）将实际音频数据存储在数据库中，那么您可以采取的一种方法是在服务中实现 Web 服务器，并拥有该服务器服务打开 SQLite 数据库，使用 Cursor.getBlob 获取 Blob 然后通过使用 ByteArrayInputStream 包装字节数组，通过 Web 服务器将该 Blob 提供给 MediaPlayer 实例。我见过这样的实现（在这些情况下，它来自文件，而不是数据库，但适用相同的原则）。
或者，您可以使用 AudioTrack，翻译音频（如果不是 PCM 格式），然后播放它并自行处理音频管理：可能需要更多工作，但效率更高。
请注意，这将非常占用内存，并且可能会表现不佳：对于 5mb MP3，您基本上必须将整个内容保存在内存中，因为 Android 的 SQLite 接口似乎没有为您提供 blob 的流接口。如果您正在加载多个媒体文件，那么......就会发生不好的事情。
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.