mengwong (mengwong) wrote,
mengwong
mengwong

Direct access to the Evernote SQL database

On OS X and probably other desktop platforms, one may address the Evernote.sql database directly through an sqlite3 client. SQLite Manager for Firefox is one option; the command line client is another.

The most important table is Zenattributedentity; it is denormalized; multiple object types are flattened into a single table, distinguished by their z_ent entity type column.

Z_ENT = 11: shared notebooks (zsharename)
Z_ENT = 12: note (ztitle)
Z_ENT = 13: notebook (zname)
Z_ENT = 14: attachment (zfilename)
Z_ENT = 15: saved search
Z_ENT = 17: tag


This SQL query finds all excel attachments.

SELECT "open $HOME/Library/Caches/Metadata/com.evernote.Evernote/p" || notes.z_pk || ".evernote" as open, attachments.zfilename, notes.ztitle, notebooks.zname from zenattributedentity attachments join zenattributedentity notes join zenattributedentity notebooks on attachments.znote = notes.z_pk and notes.znotebook1 = notebooks.z_pk where attachments.zfilename like "%xls%";


After using DBIx::Class::Schema::Loader's dbicdump to extract the schema,

dbicdump -o dump_directory=/Users/mengwong/lib/perl/ -o components='["InflateColumn::DateTime"]' Evernote::Schema dbi:SQLite:/Users/mengwong/Library/Application\ Support/Evernote/data/*/Evernote.sql '{ quote_char => "\"" }'

The following DBIx::Class code in Zenattributedentity.pm will create some of the appropriate relationships:

# the has_many through the Z12tag table won't work if the Z12tag table doesn't type its columns.
__PACKAGE__->has_many(tag2note => 'Evernote::Schema::Result::Z12tag', "z_17tags" );
__PACKAGE__->has_many(note2tag => 'Evernote::Schema::Result::Z12tag', "z_12notes" );
__PACKAGE__->has_many(notebook2note => 'Evernote::Schema::Result::Zenattributedentity', "znotebook1");
__PACKAGE__->has_many(attachments => 'Evernote::Schema::Result::Zenattributedentity', "znote");

Other relations like "a note belongs_to a notebook" are left as an exercise for the reader.


A separate table, Z12Tags, records note/tag relationships.

Please don't contact me with requests for assistance; try the Evernote forum or leave comments in this post.
Tags: evernote, sql
Subscribe

  • Post a new comment

    Error

    switch
    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.
    Help
  • 0 comments