Manually Sync Notes on iPhone or iPod Touch - Part I

Use at your own risk, make sure you backup your notes.db file before you proceed. I am not responsible for any loss of data.

Since the content of the iPhone or iPod Notes application is a sqlite database. You can pull the notes.db file down to your box, modify the notes.db via sqlite and push the file back to your iPhone. Here's how I do this with a mac.

Requirements:
1. Jailbrake iPhone or iPod Touch
2. Install OpenSSH on your iPhone or iPod Touch
3. Install SQLite3 on your Mac (port install sqlite3)

Note: I highly recommend you setup your public keys and modify your sshd_config on your iPhone or iPod Touch to no longer use passwords and only public keys. By now, everybody knows the password to the iPhone or iPod Touch is alpine. (I may setup another page for this later on)

From my mac:
1. Create a subdir called manual_sync_notes and change your directory
mkdir manual_sync_notes
cd manual_sync_notes

2. Pull your notes.db file & make a copy
scp mobile@IP:/private/var/mobile/Library/Notes/notes.db .
cp notes.db notes.db.orig

3. Have a quick look at the notes from your iPhone or iPod touch.
sqlite3 notes.db "select * from note;"

4. Insert a test note into notes.db using the following perl script:

#!/usr/bin/perl


use strict;

my $note_title 	 = $ARGV[0];

my $note_content	= $ARGV[1];

my $note_rownum	 = '';

if ( ! $ARGV[0] ) {

       print "Example: $0 Note_Title Note_Content \n";

       exit 1;

}

use DBI qw(:sql_types);

my $dbh = DBI->connect("dbi:SQLite:notes.db");

notes_addnote_title();

notes_rowid();

notes_addnote_content();

sub notes_addnote_title {

my $sql = qq { insert into note (creation_date, title, summary, contains_cjk) 

       values (strftime('%s','now'),'$note_title','$note_content',0)

     };

my $sth = $dbh->prepare ( $sql );

$sth->execute();

}

sub notes_rowid {

my $sql = qq { select rowid from note where title = '$note_title' };

my $sth = $dbh->prepare ( $sql );

$sth->execute();

my ( $rowid );

$sth->bind_columns( undef, \$rowid );

while ( $sth->fetch() ) {

$note_rownum = "$rowid \n";

chomp $note_rownum;

}

}

sub notes_addnote_content {

my $sql = qq { insert into note_bodies (note_id, data) 

       values ('$note_rownum','$note_content')

     };

my $sth = $dbh->prepare ( $sql );

$sth->execute();

}

user@osx ~/manual_sync_notes $ ./manual_notes_inject.pl NoteTest NoteTestContent

5. Run a quick query to make sure your note has been insert using the following script.

#!/usr/bin/perl


use strict;

my $note_title 	 = $ARGV[0];

my $note_rownum	 = '';

if ( ! $ARGV[0] ) {

       print "Example: $0 Note_Title \n";

       exit 1;

}

use DBI qw(:sql_types);

my $dbh = DBI->connect("dbi:SQLite:notes.db");

notes_rowid();

notes_shownote_content();

sub notes_rowid {

my $sql = qq { select rowid from note where title = '$note_title' };

my $sth = $dbh->prepare ( $sql );

$sth->execute();

my ( $rowid );

$sth->bind_columns( undef, \$rowid );

while ( $sth->fetch() ) {

$note_rownum = "$rowid \n";

chomp $note_rownum;

}

}

sub notes_shownote_content {

        my $sql = qq { select data from note_bodies where note_id = '$note_rownum' };

        my $sth = $dbh->prepare ( $sql );

        $sth->execute();

        my ( $data );

        $sth->bind_columns( undef, \$data );

        while ( $sth->fetch() ) {

print "$data \n"; 

}

}

user@osx ~/manual_sync_notes $ ./manual_notes_query.pl NoteTest
NoteTestContent

6. Push your notes.db file back to the iPhone or iPod Touch
scp notes.db mobile@IP:/private/var/mobile/Library/Notes/.

This is the best solution I could come up with being that we have no SyncServices in the SDK.

Just in case you want to know the permissions of the file for notes.db. The file should be owned by mobile:wheel with the file permissions of -rw-r--r-- aka 644.

I hope to create a Part II where I can use a native Cocoa app on the Mac to handle the modification of the notes.db and automate the sync to the iPhone or iPod Touch.