SQLite: export BLOB to file; ToastMaster SpeechTimerz: export audio records; Apple CAF to MP3

UPD: Apparently the sqlite BLOB export stuff is a popular request, and my article on how to do it here with Java program source code is just too complex for people that aren’t familiar with Java. So I’m going to do a better one soon, with more complete Java program, precompiled for anyone to use. Stay tuned.
Done! Check this post.


UPD2: It seems JailBreak is not really necessary to get the files from SpeechTimerz – you can use tools like DiskAid or iExplorer to get files from your iPhone apps, JailBreak is not necessary for that (which is good, because it seems iOS5 on iPhone4 isn’t properly jailbreakable still, and for 4s and iPad2 there’s even no “beta” jailbreak).


Intro
There is an iPhone app called SpeechTimerz that (among others) I use for ToastMasters public speaking club meetings to perform “timekeeper” role. Besides helping to track/indicate time this app also records audio during speech time, so technically by the end of the meeting all speeches are recorded and can be listened to from the iPhone. But there is no audio export feature in the app, so there is no way to get recordings anywhere.

Having a jailbroken iPhone I have access to all files on it, so I decided to dig in this apps files to see where does the audio go and check wether I could get it out. And I succeeded.

What I found out was that audio (in Apple Core Audio Format) is stored in SQLite database as BLOB (Binary Large OBject) field. So once I’ve got my hands on the file the task was to extract BLOB values from it. It turned out to be a bit harder than I thought.

The final touch was to batch-convert Apple CAF (Core Audio Format) to MP3, which also took me a bit of digging.

Everything was done on MacOS 10.6, though most of it (except CAF converting) is more/less same for Windows.

Getting files from SpeechTimerz
To get direct access to filesystem on your iPhone it must be jailbroken, and also you need to install/configure ssh. I won’t focus on this part (there are a lot of articles on the subject, if you need help with this just go google-up “iPhone ssh”), just mention that I used Fugu as SFTP client on mac.

Directory where SpeechTimerz held it’s documents in my case was (and I’m pretty sure in all cases is):
/var/mobile/Applications/5EA04716-A238-49E5-90F8-9C213B397BB7/Documents

In it there are two files that we’re interested in:
CurrentSpeech.caf
oosharks_universal_speechtimerz.sqlite
First is the recording of latest speech in CAF format, second is SQLite DB with all speech recordings (including this latest one BTW).

Getting BLOB values out of SQLite DB – using Java
I thought that would be easy to do – just download some nice free SQLite client and export them. Not sure if it was just bad luck, or are most SQLite clients really limited in that part, but I couldn’t find one that could do it properly (CSV/TXT export is NOT ok). I even tried tedious way of having SQLite as datasource in Eclipse, but could only do “text” export, and resulting files were not in proper CAF format.

I figured out it would be faster if I’d write a little program for this myself, and since I’m a Java guy choice of programming language was out of the question. I’ve downloaded Zentus SQLite JDBC Driver and in 5 minutes I had few lines of ugly but working Java code that got me the CAF files. The (ugly) code:

import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class SQLiteBlobExport {
	// Path to file is hardcoded - change to yours!
	public static void main(String args[]) throws Exception {
		Class.forName("org.sqlite.JDBC");
		Driver jdbcDriver = DriverManager.getDriver("jdbc:sqlite:/Users/uzer/oosharks_universal_speechtimerz.sqlite");
		Connection connection = jdbcDriver.connect("jdbc:sqlite:/Users/uzer/oosharks_universal_speechtimerz.sqlite", new Properties());
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt.executeQuery("select zaudiorecording from zspeechtime");
		while(!rs.isAfterLast()) {
			Object obj = rs.getObject(1);
			FileOutputStream fos = new FileOutputStream("/Users/uzer/tmspeech"+rs.getRow());
			fos.write((byte[])obj);
			fos.flush();
			fos.close();
			rs.next();
		}
		rs.close();
		connection.close();
	}
}

As you (hopefully) can tell from the code, program assumes that path to SQLite DB file is /Users/uzer/oosharks_universal_speechtimerz.sqlite (so change it to yours) and produces files tmspeech1, tmspeech2 etc in /Users/uzer/

For non-Java guys, to run all this:
0. Install JDK (Java Development Kit) if you don’t have it
1. Save code pasted above to SQLiteBlobExport.java file in some folder XYZ.
2. In command line go to that folder
3. Compile SQLiteBlobExport.java by running this command: javac SQLiteBlobExport.java
(if you get error that “javac” isn’t known use complete path to javac, like this: "/usr/bin/javac SQLiteBlobExport.java")
4. Download sqlitejdbc-v056.jar and put it in same XYZ folder
5. In command line in XYZ folder run command: "java -cp ./sqlitejdbc-v056.jar:./ SQLiteBlobExport"
(If you’re on Windows use backslashes in paths, y’know).

Converting CAF to MP3
Now we’ve got our CAF files, can play them with QuickTime player or preview, but we want to share them with our Windows friends or something like this. In short, we want MP3. Nothing easier than this.
In terminal we can run "afconvert -f 'WAVE' -d I16@44100 <filename here>" to convert CAF to WAV. WAVs can be converted using iTunes to anything else iTunes supports. But to have it all somewhat quicker, I’ve installed lame mp3 encoder (lame is installed via macports, was few clicks for me with Porticus), and now I:
– convert all CAFs in current folder to WAVs by running: find . -iname "*.caf" -exec afconvert -f 'WAVE' -d I16@44100 {} \;
– convert all WAVs in current folder to MP3 by running: find . -iname "*.wav" -exec lame -b 192 {} {}.mp3 \;

Odd fact: iTunes doesn’t want to play CAFs, but Cog for example does it easily.

Outro
That’s all, comrades. Hope you’ll find this post useful, and hope guys from OOSharks won’t mind this little digging in SpeechTimerz.

Advertisements

15 thoughts on “SQLite: export BLOB to file; ToastMaster SpeechTimerz: export audio records; Apple CAF to MP3

  1. Update: the app GUID changed to E8AAA195-38D8-4F15-A205-8C6B7D66180D after update or phone restore. Not sure why this happened and will the GUID remain same for all users, so my hint to find the App folder is to login via ssh to your device, go to /var/mobile/Applications/ (with cd command ofcourse) and do find -iname currentspeech.caf

  2. shit that sounds complicated. I’ll try to do it though..I’m on the same mission. I found the file on my iphone from heytell but its an sqlite file. Mfckers. So now I will attempt to learn Java to figure this out.

  3. Exception in thread “main” java.sql.SQLException: no such table: zspeechtime
    at org.sqlite.DB.throwex(DB.java:288)
    at org.sqlite.NativeDB.prepare(Native Method)
    at org.sqlite.DB.prepare(DB.java:114)
    at org.sqlite.Stmt.executeQuery(Stmt.java:89)
    at SQLiteBlobExport.main(SQLiteBlobExport.java:16)
    TomMacBook:desktop t******$

    do i need to change ‘zspeechtime’ to something different?

  4. FYI regarding above comment, im trying to get audio from a heytell sqlite file which ive renamed to your filenaming, with no success

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s