/Applications/MobileNotes.app

Notes is the built-in system application to take quick text notes. To backup or restore the data of this application without iTunes, the following information might be useful. Initial data for this analysis comes from an iPhone 3GS with firmware 3.1.3, which was later restored / upgraded to an iPhone 4 and finally to an iPhone 4S with iOS 5.0.1. On lower or higher firmware versions there might be small differences in the data.

In the folder /var/mobile/Library/Notes/ there are two files:

• notes.sqlite
• notes.idx

The second file is probably used for indexing and can be recreated if deleted (?). Looking at the file notes.sqlite, this is an SQLite database with the following tables in it:

Z_ENT	table name	name2
	sqlite_master
1	ZACCOUNT	Account
2	ZNEXTID	NextId
3	ZNOTE	Note
4	ZNOTEBODY	NoteBody
5	ZNOTECHANGE	NoteChange
6	ZPROPERTY	Property
7	ZSTORE	Store
	Z_PRIMARYKEY
	Z_METADATA

The name2 in this list is the value used for the hash in NSStoreModelVersionHashes in the bplist of the field Z_PLIST in the table Z_METADATA. The same name2 is used in the table Z_PRIMARYKEY, field Z_NAME.

The following indexes are defined:

index name	table name	field name
ZACCOUNT_ZDEFAULTSTORE_INDEX	ZACCOUNT	ZDEFAULTSTORE
ZNOTE_ZINTEGERID_INDEX	ZNOTE	ZINTEGERID
ZNOTE_ZSTORE_INDEX	ZNOTE	ZSTORE
ZNOTE_ZBODY_INDEX	ZNOTE	ZBODY
ZNOTEBODY_ZOWNER_INDEX	ZNOTEBODY	ZOWNER
ZNOTECHANGE_ZSTORE_INDEX	ZNOTECHANGE	ZSTORE
ZSTORE_ZACCOUNT_INDEX	ZSTORE	ZACCOUNT

Tables

sqlite_master

This table is contained in every SQLite database and contains general information about the content. It has these fields:

field name	description
type	either 'table' or 'index'
name	name of the table or index
tbl_name	same as name for table, related table name for index
rootpage	integer, internal id where to find the data (?)
sql	creation statement

This is the content of this table:

type	name	tbl_name	rootpage	sql
table	ZACCOUNT	ZACCOUNT	3	CREATE TABLE ZACCOUNT ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZTYPE INTEGER, ZDEFAULTSTORE INTEGER, ZNAME VARCHAR, ZACCOUNTIDENTIFIER VARCHAR, ZCONSTRAINTSPATH VARCHAR )
table	ZNEXTID	ZNEXTID	5	CREATE TABLE ZNEXTID ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZCOUNTER INTEGER )
table	ZNOTE	ZNOTE	6	CREATE TABLE ZNOTE ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZCONTENTTYPE INTEGER, ZDELETEDFLAG INTEGER, ZINTEGERID INTEGER, ZCONTAINSCJK INTEGER, ZEXTERNALSERVERINTID INTEGER, ZEXTERNALFLAGS INTEGER, ZISBOOKKEEPINGENTRY INTEGER, ZSTORE INTEGER, ZBODY INTEGER, ZCREATIONDATE TIMESTAMP, ZMODIFICATIONDATE TIMESTAMP, ZTITLE VARCHAR, ZGUID VARCHAR, ZAUTHOR VARCHAR, ZSERVERID VARCHAR, ZSUMMARY VARCHAR )
table	ZNOTEBODY	ZNOTEBODY	10	CREATE TABLE ZNOTEBODY ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZOWNER INTEGER, ZCONTENT VARCHAR, ZEXTERNALCONTENTREF VARCHAR, ZEXTERNALREPRESENTATION BLOB )
table	ZNOTECHANGE	ZNOTECHANGE	12	CREATE TABLE ZNOTECHANGE ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZCHANGETYPE INTEGER, ZSTORE INTEGER, ZNOTESERVERINTIDS BLOB, ZNOTEINTEGERIDS BLOB, ZNOTESERVERIDS BLOB )
table	ZPROPERTY	ZPROPERTY	14	CREATE TABLE ZPROPERTY ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZPROPERTYKEY VARCHAR, ZPROPERTYVALUE BLOB )
table	ZSTORE	ZSTORE	15	CREATE TABLE ZSTORE ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZACCOUNT INTEGER, ZEXTERNALIDENTIFIER VARCHAR, ZNAME VARCHAR, ZSYNCANCHOR VARCHAR )
table	Z_PRIMARYKEY	Z_PRIMARYKEY	17	CREATE TABLE Z_PRIMARYKEY (Z_ENT INTEGER PRIMARY KEY, Z_NAME VARCHAR, Z_SUPER INTEGER, Z_MAX INTEGER)
table	Z_METADATA	Z_METADATA	18	CREATE TABLE Z_METADATA (Z_VERSION INTEGER PRIMARY KEY, Z_UUID VARCHAR(255), Z_PLIST BLOB)
index	ZACCOUNT_ZDEFAULTSTORE_INDEX	ZACCOUNT	4	CREATE INDEX ZACCOUNT_ZDEFAULTSTORE_INDEX ON ZACCOUNT (ZDEFAULTSTORE)
index	ZNOTE_ZINTEGERID_INDEX	ZNOTE	7	CREATE INDEX ZNOTE_ZINTEGERID_INDEX ON ZNOTE (ZINTEGERID)
index	ZNOTE_ZSTORE_INDEX	ZNOTE	8	CREATE INDEX ZNOTE_ZSTORE_INDEX ON ZNOTE (ZSTORE)
index	ZNOTE_ZBODY_INDEX	ZNOTE	9	CREATE INDEX ZNOTE_ZBODY_INDEX ON ZNOTE (ZBODY)
index	ZNOTEBODY_ZOWNER_INDEX	ZNOTEBODY	11	CREATE INDEX ZNOTEBODY_ZOWNER_INDEX ON ZNOTEBODY (ZOWNER)
index	ZNOTECHANGE_ZSTORE_INDEX	ZNOTECHANGE	13	CREATE INDEX ZNOTECHANGE_ZSTORE_INDEX ON ZNOTECHANGE (ZSTORE)
index	ZSTORE_ZACCOUNT_INDEX	ZSTORE	16	CREATE INDEX ZSTORE_ZACCOUNT_INDEX ON ZSTORE (ZACCOUNT)

Please note that the values for rootpage might differ in every database.

Z_PRIMARYKEY

This is used by the framework and holds an entry for every table. These are the fields in there:

field name	type	description
Z_ENT	INTEGER PRIMARY KEY	The identifier of the table
Z_NAME	VARCHAR	one of these values: 'Account', 'NextId', 'Note', 'NoteBody', 'NoteChange', 'Property', 'Store'
Z_SUPER	INTEGER	always 0
Z_MAX	INTEGER	highest Z_PK in the according table

Z_METADATA

This table is used by the framework and holds configuration data. There is only one record in the table.

field name	type	description
Z_VERSION	INTEGER PRIMARY KEY	value 1
Z_UUID	VARCHAR(255)	value 'D1694631-D07A-4223-9A61-42689DA4C76A' (?)
Z_PLIST	BLOB	binary plist

The bplist holds this data:

• LastIndexTidAlreadySetUp=true
• LocalAccountAndStoreAlreadySetUp=false
• NSPersistenceFrameworkVersion=386
• NSStoreModelVersionHashes=dict:
  • Account=*
  • NextId=*
  • Note=*
  • NoteBody=*
  • NoteChange=*
  • Property=*
  • Store=*
• NSStoreModelVersionHashesVersion=3
• NSStoreModelVersionIdentifiers=array:
  • (one empty string)
• NSStoreType=SQLite

*data, base64 encoded 32-byte hash (calculation?)

ZSTORE

This table has only one record in it.

field name	type	value / description
Z_PK	INTEGER PRIMARY KEY	primary key
Z_ENT	INTEGER	7 (table identifier)
Z_OPT	INTEGER	193 (?)
ZACCOUNT	INTEGER	1 (foreign key to ZACCOUNT table)
ZEXTERNALIDENTIFIER	VARCHAR	'local://local/store'
ZNAME	VARCHAR	'LOCAL_NOTES_STORE'
ZSYNCANCHOR	VARCHAR	NULL

ZACCOUNT

This table has only one record in it. Maybe these entries define different iTunes users. (?)

field name	type	value / description
Z_PK	INTEGER PRIMARY KEY	primary key
Z_ENT	INTEGER	1 (table identifier)
Z_OPT	INTEGER	1 (?)
ZTYPE	INTEGER	0 (?)
ZDEFAULTSTORE	INTEGER	1 (foreign key to ZSTORE table)
ZNAME	VARCHAR	'LOCAL_NOTES_ACCOUNT'
ZACCOUNTIDENTIFIER	VARCHAR	'local://local/account'
ZCONSTRAINTSPATH	VARCHAR	NULL

ZNEXTID

This table has only one record in it.

field name	type	value / description
Z_PK	INTEGER PRIMARY KEY	primary key
Z_ENT	INTEGER	2 (table identifier)
Z_OPT	INTEGER	31 (?)
ZCOUNTER	INTEGER	320 (?)

ZPROPERTY

This table contains three rows.

field name	type	value / description
Z_PK	INTEGER PRIMARY KEY	primary key
Z_ENT	INTEGER	always 6 (table identifier)
Z_OPT	INTEGER	unknown (?), values 186 / 27 / 27
ZPROPERTYKEY	VARCHAR	values 'LastTransactionID' / 'DeviceSyncAnchorKey' / 'ComputerSyncAnchorKey'
ZPROPERTYVALUE	BLOB	NULL for the two AnchorKeys, but LastTransactionID contains a value - beginning with BLOB from other table.

ZNOTE

This table contains general infos about each note. There is one record for every note.

field name	type	value / description
Z_PK	INTEGER PRIMARY KEY	primary key
Z_ENT	INTEGER	always 3 (table identifier)
Z_OPT	INTEGER	unknown (?), the values 1,2,5,9,12,31,32 are being used here
ZCONTENTTYPE	INTEGER	always 0
ZDELETEDFLAG	INTEGER	always 0
ZINTEGERID	INTEGER	always the value of Z_PK multiplied by 10
ZCONTAINSCJK	INTEGER	always 0
ZEXTERNALSERVERINTID	INTEGER	always -4294967296 (?)
ZEXTERNALFLAGS	INTEGER	always 0
ZISBOOKKEEPINGENTRY	INTEGER	always 0
ZSTORE	INTEGER	always 1 (foreign key to ZSTORE table)
ZBODY	INTEGER	foreign key to ZNOTEBODY table, value matches Z_PK of this table
ZCREATIONDATE	TIMESTAMP	creation date and time of the note
ZMODIFICATIONDATE	TIMESTAMP	last modification date and time of the note
ZTITLE	VARCHAR	title for overview list
ZGUID	VARCHAR	always NULL
ZAUTHOR	VARCHAR	always NULL
ZSERVERID	VARCHAR	always NULL
ZSUMMARY	VARCHAR	short text

For the two date / time fields in this table: This is a floating point number and indicates the number of seconds since 1 January 2001.

ZNOTEBODY

This table contains general infos about each note. There is one record for every note.

field name	type	value / description
Z_PK	INTEGER PRIMARY KEY	primary key
Z_ENT	INTEGER	always 4 (table identifier)
Z_OPT	INTEGER	unknown (?), the values 1,2,3,5,9,10,12,31,32 are being used here
ZOWNER	INTEGER	always matches Z_PK
ZCONTENT	VARCHAR	This is the actual content text (HTML format).
ZEXTERNALCONTENTREF	VARCHAR	always NULL
ZEXTERNALREPRESENTATION	BLOB	always NULL

ZNOTECHANGE

The purpose of this table is unknown. It is probably related to changes and synchronization.

field name	type	value / description
Z_PK	INTEGER PRIMARY KEY	primary key
Z_ENT	INTEGER	always 5 (table identifier)
ZCHANGETYPE	INTEGER	unknown (?), values of 0,1,2 exist
ZSTORE	INTEGER	always 1 (foreign key to ZSTORE)
ZNOTESERVERINTIDS	BLOB	big blob, content unknown (?)
ZNOTEINTEGERIDS	BLOB	big blob, content unknown (?)
ZNOTESERVERIDS	BLOB	big blob, content unknown (?)

Convert

A script for converting a notes.sqlite file into a usable series of text files:

#!/bin/bash
set -e

function adjust() {
    date -j -f '%Y-%m-%d %H:%M:%S' "$1" +'%m/%d/%Y %H:%M:%S'
}

rm -rf notes.d
mkdir -p notes.d

sqlite3 notes.sqlite 'select
    z_pk as id,
    datetime(zcreationdate + 978307200, "unixepoch", "localtime") as created,
    datetime(zmodificationdate + 978307200, "unixepoch", "localtime") as modified,
    ztitle as title from znote
' | while IFS='|' read -r id created modified title; do
    file=notes.d/"$id".html
    sqlite3 notes.sqlite 'select zcontent from znotebody where zowner = '"$id" >"$file"
    SetFile -d "$(adjust "$created")" -m "$(adjust "$modified")" "$file"
    title=${title//[\/:.]/_}
    mv "$file" notes.d/"$id - ${title:0:$((260-${#file}))}".html
done

Parents

(root)/Applications