iPhone/iPod Touch Notes - fonts and styles

Turns out there is a lot you can do once you've got pscp to grab your notes.db, and a copy of SQLite Browser.

The basic structure of notes.db seems to be:

CREATE TABLE Note 
( creation_date INTEGER
, title TEXT
, summary TEXT)

CREATE TABLE note_bodies 
( note_id INTEGER
, data)

There doesn't appear to be a direct relationship between them, however the [Note] rows are always sorted by [creation_date] ASC, and the [note_bodies].[note_id] ascends in the same 'order'.

[summary] does not appear to be used on the iPhone/iPod Touch (Notes.app puts the first 50 chars of the note data in that column); and [creation_date] is iPhone_Unix_time (seconds since January 1st, 2001 UTC).

Anyway, without further ado, the steps to get this font-test source onto an iPhone/iPod Touch follow (for Windows):

1. Get required software

pscp (part of the PuTTY download) and
SQLite Browser

2. Download your notes.db

Create this batch file somewhere on your PC, along with a Library\Notes folder tree. Run the .BAT (entering the IP address when asked). Assuming the file downloads successfully, MAKE A BACKUP COPY of it somewhere.

DownloadNotes.bat
set IP=
set /p IP=Enter your iPod's IP address (eg: 192.168.1.101): 
pscp -r -pw alpine root@%IP%:/var/root/Library/Notes/* Library/Notes
pause

3. Open notes.db in SQLite Browser

The Database Structure tab will look something like this (additional metadata may be shown)

Go to the Execute SQL tab (like this)

Execute the following four (4) SQL statements, one after the other:

-- BACKUP YOUR notes.db FILE BEFORE YOU START

-- These queries were run using http://sqlitebrowser.sourceforge.net/

-- First, insert the 'header' for the note
-- Get the creation_date from http://i.recipenow.net/i/time/
-- The title appears on the Notes index page; the summary
-- does not appear to be used on the iPhone/Touch (for now)
INSERT INTO Note(creation_date, title, summary) VALUES(216352013, 'Html and Font Test', 'American Typewriter')

-- Now that we've added a new header, 
-- determine the new id and using it:
-- IMPORTANT : *** REPLACE the string MAX_NOTE_ID *** 
--             *** in BOTH the queries below      ***
SELECT MAX(note_id) + 1 FROM note_bodies

-- Then add the new row with that id
INSERT INTO note_bodies (note_id) VALUES ( MAX_NOTE_ID )

-- Finally, update the new row with the note body
UPDATE note_bodies SET data='Html Test<div><br class="webkit-block-placeholder"></div>
<div style="font-family:American Typewriter;">American Typewriter  <b>bold</b> <i>italic</i> <u>underline</u></div>
<div style="font-family:Arial;">Arial  <b>bold</b> <i>italic</i> <u>underline</u></div>
<div style="font-family:Courier New;">Courier New  <b>bold</b> <i>italic</i> <u>underline</u></div>
<div style="font-family:Georgia;">Georgia  <b>bold</b> <i>italic</i> <u>underline</u></div>
<div style="font-family:Helvetica;">Helvetica  <b>bold</b> <i>italic</i> <u>underline</u></div>
<div style="font-family:Marker Felt;">Marker Felt  <b>bold</b> <i>italic</i> <u>underline</u></div>
<div style="font-family:Times New Roman;">Times New Roman  <b>bold</b> <i>italic</i> <u>underline</u></div>
<div style="font-family:Trebuchet MS;">Trebuchet MS  <b>bold</b> <i>italic</i> <u>underline</u></div>
<div style="font-family:Verdana;">Verdana  <b>bold</b> <i>italic</i> <u>underline</u></div>
<div style="font-family:Zapfino;">Zapfino <b>bold</b> <i>italic</i> <u>underline</u></div>
<div><br class="webkit-block-placeholder"></div>
<div style="font-family:Helvetica;"><sup>1</sup>/<sub>2</sub> cup</div>'
WHERE note_id = MAX_NOTE_ID

4. Save notes.db

Save the changes you have just made AND QUIT SQLite Browser. You must quite the program so that the upload will work.

5. Upload your notes.db

As with step 2 (but in reverse)

UploadNotes.bat
set IP=
set /p IP=Enter your iPod's IP address (eg: 192.168.1.101): 
pscp -r -pw alpine Library/Notes/notes.db root@%IP%:/var/root/Library/Notes/
pause

6. Open Notes on your iPhone/iPod Touch

The new note should appear in your Notes application, and display with the complete set of fonts shown above


If you choose to send the note as an email, the formatting stays... so delete the text you don't want and start 'overtyping' in your favourite style for fancy email formatting