/Applications/MobileNotes.app

From The iPhone Wiki
(Redirected from Notes)
Jump to: navigation, search

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