Skip to content

Instantly share code, notes, and snippets.

@jpmens

jpmens/schema.md

Created March 24, 2019 13:45
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save jpmens/a62244e5a10bb36ca450f921c423ddd3 to your computer and use it in GitHub Desktop.
Save jpmens/a62244e5a10bb36ca450f921c423ddd3 to your computer and use it in GitHub Desktop.
Download ZIP
Sierra AddressBook schema
Raw
schema.md 
echo '.schema' | sqlite3 ~/Library/Application\ Support/AddressBook/AddressBook-v22.abcddb
CREATE TABLE ZABCDCUSTOMPROPERTY ( Z_ENT INTEGER, Z_PK INTEGER PRIMARY KEY, Z_OPT INTEGER, ZVALUETYPE INTEGER, ZRECORDTYPE VARCHAR, ZPROPERTYNAME VARCHAR );
CREATE TABLE ZABCDDELETEDRECORDLOG ( Z_ENT INTEGER, Z_PK INTEGER PRIMARY KEY, Z_OPT INTEGER, ZADDRESSBOOKSOURCE INTEGER, ZDELETEDRECORDUNIQUEID VARCHAR, ZUNIQUEID VARCHAR );
CREATE INDEX ZABCDDELETEDRECORDLOG_ZADDRESSBOOKSOURCE_INDEX ON ZABCDDELETEDRECORDLOG (ZADDRESSBOOKSOURCE);
CREATE TABLE ZABCDSERVICE ( Z_ENT INTEGER, Z_PK INTEGER PRIMARY KEY, Z_OPT INTEGER, ZSERVICENAME VARCHAR );
CREATE TABLE ZABCDSHARINGACCESSCONTROLENTRY ( Z_ENT INTEGER, Z_PK INTEGER PRIMARY KEY, Z_OPT INTEGER, ZORDERINGINDEX INTEGER, ZISPRIVATE INTEGER, ZREADACLSOURCE INTEGER, ZOWNER INTEGER, ZREADWRITEACLSOURCE INTEGER, ZISPRIMARY INTEGER, ZLABEL VARCHAR, ZUNIQUEID VARCHAR, ZACCOUNTNAME VARCHAR );
CREATE INDEX ZABCDSHARINGACCESSCONTROLENTRY_ZREADACLSOURCE_INDEX ON ZABCDSHARINGACCESSCONTROLENTRY (ZREADACLSOURCE);
CREATE INDEX ZABCDSHARINGACCESSCONTROLENTRY_ZOWNER_INDEX ON ZABCDSHARINGACCESSCONTROLENTRY (ZOWNER);
CREATE INDEX ZABCDSHARINGACCESSCONTROLENTRY_ZREADWRITEACLSOURCE_INDEX ON ZABCDSHARINGACCESSCONTROLENTRY (ZREADWRITEACLSOURCE);
CREATE TABLE Z_METADATA (Z_VERSION INTEGER PRIMARY KEY, Z_UUID VARCHAR(255), Z_PLIST BLOB);
CREATE TABLE Z_MODELCACHE (Z_CONTENT BLOB);
CREATE TABLE ZABCDRECORD ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZCREATIONDATEYEAR INTEGER, ZDISPLAYFLAGS INTEGER, ZMODIFICATIONDATEYEAR INTEGER, ZADDRESSBOOKSOURCE INTEGER, ZISALL INTEGER, ZINFO INTEGER, ZME INTEGER, Z22_ME INTEGER, ZBIRTHDAYYEAR INTEGER, ZPREFERREDFORLINKNAME INTEGER, ZPREFERREDFORLINKPHOTO INTEGER, ZPRIVACYFLAGS INTEGER, ZADDRESSBOOKSOURCE1 INTEGER, ZCONTACTINDEX INTEGER, ZLUNARBIRTHDAYCOMPONENTS INTEGER, ZNOTE INTEGER, ZSOURCEWHERECONTACTISME INTEGER, ZASSISTANTSYNCANCHOR INTEGER, ZSHARECOUNT INTEGER, ZSYNCCOUNT INTEGER, ZVERSION INTEGER, ZADDRESSBOOKSOURCE2 INTEGER, ZCREATIONDATE TIMESTAMP, ZCREATIONDATEYEARLESS FLOAT, ZMODIFICATIONDATE TIMESTAMP, ZMODIFICATIONDATEYEARLESS FLOAT, ZBIRTHDAY TIMESTAMP, ZBIRTHDAYYEARLESS FLOAT, ZUNIQUEID VARCHAR, ZNAME VARCHAR, ZNAMENORMALIZED VARCHAR, ZTMPREMOTELOCATION VARCHAR, ZNAME1 VARCHAR, ZREMOTELOCATION VARCHAR, ZSERIALNUMBER VARCHAR, ZDEPARTMENT VARCHAR, ZFIRSTNAME VARCHAR, ZIDENTITYUNIQUEID VARCHAR, ZIMAGEREFERENCE VARCHAR, ZJOBTITLE VARCHAR, ZLASTNAME VARCHAR, ZLINKID VARCHAR, ZMAIDENNAME VARCHAR, ZMIDDLENAME VARCHAR, ZNICKNAME VARCHAR, ZORGANIZATION VARCHAR, ZPHONEMEDATA VARCHAR, ZPHONETICFIRSTNAME VARCHAR, ZPHONETICLASTNAME VARCHAR, ZPHONETICMIDDLENAME VARCHAR, ZPHONETICORGANIZATION VARCHAR, ZPREFERREDAPPLEPERSONAIDENTIFIER VARCHAR, ZPREFERREDLIKENESSSOURCE VARCHAR, ZSORTINGFIRSTNAME VARCHAR, ZSORTINGLASTNAME VARCHAR, ZSUFFIX VARCHAR, ZTITLE VARCHAR, ZTMPHOMEPAGE VARCHAR, ZASSISTANTVALIDITY VARCHAR, ZCREATEDVERSION VARCHAR, ZLASTDOTMACACCOUNT VARCHAR, ZLASTSAVEDVERSION VARCHAR, ZSYNCANCHOR VARCHAR, ZMODIFIEDUNIQUEIDSDATA BLOB, ZSEARCHELEMENTDATA BLOB );
CREATE TABLE Z_17PARENTGROUPS ( Z_17CHILDGROUPS INTEGER, Z_18PARENTGROUPS INTEGER, PRIMARY KEY (Z_17CHILDGROUPS, Z_18PARENTGROUPS) );
CREATE TABLE Z_22PARENTGROUPS ( Z_22CONTACTS INTEGER, Z_18PARENTGROUPS1 INTEGER, PRIMARY KEY (Z_22CONTACTS, Z_18PARENTGROUPS1) );
CREATE TABLE ZABCDDATECOMPONENTS ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZDAY INTEGER, ZERA INTEGER, ZISLEAPMONTH INTEGER, ZMONTH INTEGER, ZYEAR INTEGER, ZCONTACT INTEGER, Z22_CONTACT INTEGER, ZCALENDARIDENTIFIER VARCHAR, ZUNIQUEID VARCHAR );
CREATE TABLE ZABCDLIKENESS ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZISPRIMARY INTEGER, ZISPRIVATE INTEGER, ZKIND INTEGER, ZORDERINGINDEX INTEGER, ZOWNER INTEGER, Z22_OWNER INTEGER, ZLABEL VARCHAR, ZUNIQUEID VARCHAR, ZVERSION VARCHAR, ZDATA BLOB );
CREATE TABLE ZABCDALERTTONE ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZOWNER INTEGER, Z22_OWNER INTEGER, ZTONEDATA VARCHAR, ZTYPE VARCHAR, ZUNIQUEID VARCHAR );
CREATE TABLE ZABCDUNKNOWNPROPERTY ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZOWNER INTEGER, Z16_OWNER INTEGER, ZPROPERTYNAME VARCHAR, ZORIGINALLINE BLOB );
CREATE TABLE ZABCDSOCIALPROFILE ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZISPRIMARY INTEGER, ZISPRIVATE INTEGER, ZORDERINGINDEX INTEGER, ZOWNER INTEGER, Z22_OWNER INTEGER, ZBUNDLEIDENTIFIERSSTRING VARCHAR, ZDISPLAYNAME VARCHAR, ZLABEL VARCHAR, ZSERVICENAME VARCHAR, ZTEAMIDENTIFIER VARCHAR, ZUNIQUEID VARCHAR, ZURLSTRING VARCHAR, ZUSERIDENTIFIER VARCHAR, ZUSERNAME VARCHAR, ZCUSTOMVALUESDATA BLOB );
CREATE TABLE ZABCDCALENDARURI ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZISPRIMARY INTEGER, ZISPRIVATE INTEGER, ZORDERINGINDEX INTEGER, ZOWNER INTEGER, Z22_OWNER INTEGER, ZLABEL VARCHAR, ZUNIQUEID VARCHAR, ZURL VARCHAR );
CREATE TABLE ZABCDCONTACTINDEX ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZCONTACT INTEGER, Z22_CONTACT INTEGER, ZSTRINGFORINDEXING VARCHAR );
CREATE TABLE ZABCDEMAILADDRESS ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZISPRIMARY INTEGER, ZISPRIVATE INTEGER, ZORDERINGINDEX INTEGER, ZOWNER INTEGER, Z22_OWNER INTEGER, ZADDRESS VARCHAR, ZADDRESSNORMALIZED VARCHAR, ZLABEL VARCHAR, ZUNIQUEID VARCHAR );
CREATE TABLE ZABCDPHONENUMBER ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZISPRIMARY INTEGER, ZISPRIVATE INTEGER, ZORDERINGINDEX INTEGER, ZOWNER INTEGER, Z22_OWNER INTEGER, ZAREACODE VARCHAR, ZCOUNTRYCODE VARCHAR, ZEXTENSION VARCHAR, ZFULLNUMBER VARCHAR, ZLABEL VARCHAR, ZLASTFOURDIGITS VARCHAR, ZLOCALNUMBER VARCHAR, ZUNIQUEID VARCHAR );
CREATE TABLE ZABCDNOTE ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZCONTACT INTEGER, Z22_CONTACT INTEGER, ZTEXT VARCHAR, ZRICHTEXTDATA BLOB );
CREATE TABLE ZABCDURLADDRESS ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZISPRIMARY INTEGER, ZISPRIVATE INTEGER, ZORDERINGINDEX INTEGER, ZOWNER INTEGER, Z22_OWNER INTEGER, ZLABEL VARCHAR, ZUNIQUEID VARCHAR, ZURL VARCHAR );
CREATE TABLE ZABCDDISTRIBUTIONLISTCONFIG ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZADDRESS INTEGER, ZCONTACT INTEGER, Z22_CONTACT INTEGER, ZEMAIL INTEGER, ZGROUP INTEGER, Z18_GROUP INTEGER, ZPHONE INTEGER, ZPROPERTYNAME VARCHAR );
CREATE TABLE ZABCDREMOTELOCATION ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZISPRIMARY INTEGER, ZISPRIVATE INTEGER, ZORDERINGINDEX INTEGER, ZOWNER INTEGER, Z16_OWNER INTEGER, ZLABEL VARCHAR, ZUNIQUEID VARCHAR, ZURL VARCHAR );
CREATE TABLE ZABCDCONTACTDATE ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZDATEYEAR INTEGER, ZISPRIMARY INTEGER, ZISPRIVATE INTEGER, ZORDERINGINDEX INTEGER, ZOWNER INTEGER, Z22_OWNER INTEGER, ZDATE TIMESTAMP, ZDATEYEARLESS FLOAT, ZLABEL VARCHAR, ZUNIQUEID VARCHAR );
CREATE TABLE ZABCDCUSTOMPROPERTYVALUE ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZDATEVALUEYEAR INTEGER, ZISPRIMARY INTEGER, ZISPRIVATE INTEGER, ZORDERINGINDEX INTEGER, ZCUSTOMPROPERTY INTEGER, ZOWNER INTEGER, Z16_OWNER INTEGER, ZDATEVALUE TIMESTAMP, ZDATEVALUEYEARLESS FLOAT, ZNUMBERVALUE FLOAT, ZLABEL VARCHAR, ZSTRINGVALUE VARCHAR, ZUNIQUEID VARCHAR, ZDATAVALUE BLOB );
CREATE TABLE ZABCDRELATEDNAME ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZISPRIMARY INTEGER, ZISPRIVATE INTEGER, ZORDERINGINDEX INTEGER, ZOWNER INTEGER, Z22_OWNER INTEGER, ZLABEL VARCHAR, ZNAME VARCHAR, ZUNIQUEID VARCHAR );
CREATE INDEX ZABCDALERTTONE_ZOWNER_INDEX ON ZABCDALERTTONE (ZOWNER);
CREATE INDEX ZABCDCALENDARURI_ZOWNER_INDEX ON ZABCDCALENDARURI (ZOWNER);
CREATE INDEX ZABCDCONTACTDATE_ZOWNER_INDEX ON ZABCDCONTACTDATE (ZOWNER);
CREATE INDEX ZABCDCONTACTINDEX_ZCONTACT_INDEX ON ZABCDCONTACTINDEX (ZCONTACT);
CREATE INDEX ZABCDCUSTOMPROPERTYVALUE_ZCUSTOMPROPERTY_INDEX ON ZABCDCUSTOMPROPERTYVALUE (ZCUSTOMPROPERTY);
CREATE INDEX ZABCDCUSTOMPROPERTYVALUE_ZOWNER_INDEX ON ZABCDCUSTOMPROPERTYVALUE (ZOWNER);
CREATE INDEX ZABCDDATECOMPONENTS_ZCONTACT_INDEX ON ZABCDDATECOMPONENTS (ZCONTACT);
CREATE INDEX ZABCDDISTRIBUTIONLISTCONFIG_ZADDRESS_INDEX ON ZABCDDISTRIBUTIONLISTCONFIG (ZADDRESS);
CREATE INDEX ZABCDDISTRIBUTIONLISTCONFIG_ZCONTACT_INDEX ON ZABCDDISTRIBUTIONLISTCONFIG (ZCONTACT);
CREATE INDEX ZABCDDISTRIBUTIONLISTCONFIG_ZEMAIL_INDEX ON ZABCDDISTRIBUTIONLISTCONFIG (ZEMAIL);
CREATE INDEX ZABCDDISTRIBUTIONLISTCONFIG_ZGROUP_INDEX ON ZABCDDISTRIBUTIONLISTCONFIG (ZGROUP);
CREATE INDEX ZABCDDISTRIBUTIONLISTCONFIG_ZPHONE_INDEX ON ZABCDDISTRIBUTIONLISTCONFIG (ZPHONE);
CREATE INDEX ZABCDEMAILADDRESS_ZADDRESS_INDEX ON ZABCDEMAILADDRESS (ZADDRESS);
CREATE INDEX ZABCDEMAILADDRESS_ZADDRESSNORMALIZED_INDEX ON ZABCDEMAILADDRESS (ZADDRESSNORMALIZED);
CREATE INDEX ZABCDEMAILADDRESS_ZOWNER_INDEX ON ZABCDEMAILADDRESS (ZOWNER);
CREATE INDEX ZABCDLIKENESS_ZOWNER_INDEX ON ZABCDLIKENESS (ZOWNER);
CREATE INDEX ZABCDNOTE_ZCONTACT_INDEX ON ZABCDNOTE (ZCONTACT);
CREATE INDEX ZABCDPHONENUMBER_ZLASTFOURDIGITS_INDEX ON ZABCDPHONENUMBER (ZLASTFOURDIGITS);
CREATE INDEX ZABCDPHONENUMBER_ZOWNER_INDEX ON ZABCDPHONENUMBER (ZOWNER);
CREATE INDEX ZABCDRECORD_ZUNIQUEID_INDEX ON ZABCDRECORD (ZUNIQUEID);
CREATE INDEX ZABCDRECORD_ZNAMENORMALIZED_INDEX ON ZABCDRECORD (ZNAMENORMALIZED);
CREATE INDEX ZABCDRECORD_ZADDRESSBOOKSOURCE_INDEX ON ZABCDRECORD (ZADDRESSBOOKSOURCE);
CREATE INDEX ZABCDRECORD_ZINFO_INDEX ON ZABCDRECORD (ZINFO);
CREATE INDEX ZABCDRECORD_ZME_INDEX ON ZABCDRECORD (ZME);
CREATE INDEX ZABCDRECORD_ZIDENTITYUNIQUEID_INDEX ON ZABCDRECORD (ZIDENTITYUNIQUEID);
CREATE INDEX ZABCDRECORD_ZLINKID_INDEX ON ZABCDRECORD (ZLINKID);
CREATE INDEX ZABCDRECORD_ZSORTINGFIRSTNAME_INDEX ON ZABCDRECORD (ZSORTINGFIRSTNAME);
CREATE INDEX ZABCDRECORD_ZSORTINGLASTNAME_INDEX ON ZABCDRECORD (ZSORTINGLASTNAME);
CREATE INDEX ZABCDRECORD_ZADDRESSBOOKSOURCE1_INDEX ON ZABCDRECORD (ZADDRESSBOOKSOURCE1);
CREATE INDEX ZABCDRECORD_ZCONTACTINDEX_INDEX ON ZABCDRECORD (ZCONTACTINDEX);
CREATE INDEX ZABCDRECORD_ZLUNARBIRTHDAYCOMPONENTS_INDEX ON ZABCDRECORD (ZLUNARBIRTHDAYCOMPONENTS);
CREATE INDEX ZABCDRECORD_ZNOTE_INDEX ON ZABCDRECORD (ZNOTE);
CREATE INDEX ZABCDRECORD_ZSOURCEWHERECONTACTISME_INDEX ON ZABCDRECORD (ZSOURCEWHERECONTACTISME);
CREATE INDEX ZABCDRECORD_ZADDRESSBOOKSOURCE2_INDEX ON ZABCDRECORD (ZADDRESSBOOKSOURCE2);
CREATE INDEX ZABCDRECORD_Z_ENT_INDEX ON ZABCDRECORD (Z_ENT);
CREATE INDEX Z_17PARENTGROUPS_Z_18PARENTGROUPS_INDEX ON Z_17PARENTGROUPS (Z_18PARENTGROUPS, Z_17CHILDGROUPS);
CREATE INDEX Z_22PARENTGROUPS_Z_18PARENTGROUPS1_INDEX ON Z_22PARENTGROUPS (Z_18PARENTGROUPS1, Z_22CONTACTS);
CREATE INDEX ZABCDRELATEDNAME_ZOWNER_INDEX ON ZABCDRELATEDNAME (ZOWNER);
CREATE INDEX ZABCDREMOTELOCATION_ZOWNER_INDEX ON ZABCDREMOTELOCATION (ZOWNER);
CREATE INDEX ZABCDSOCIALPROFILE_ZOWNER_INDEX ON ZABCDSOCIALPROFILE (ZOWNER);
CREATE INDEX ZABCDUNKNOWNPROPERTY_ZOWNER_INDEX ON ZABCDUNKNOWNPROPERTY (ZOWNER);
CREATE INDEX ZABCDURLADDRESS_ZOWNER_INDEX ON ZABCDURLADDRESS (ZOWNER);
CREATE TABLE Z_PRIMARYKEY (Z_ENT INTEGER PRIMARY KEY, Z_NAME VARCHAR, Z_SUPER INTEGER, Z_MAX INTEGER);
CREATE TABLE ZABCDMESSAGINGADDRESS ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZISPRIMARY INTEGER, ZISPRIVATE INTEGER, ZORDERINGINDEX INTEGER, ZOWNER INTEGER, Z22_OWNER INTEGER, ZSERVICE INTEGER, ZADDRESS VARCHAR, ZBUNDLEIDENTIFIERSSTRING VARCHAR, ZLABEL VARCHAR, ZTEAMIDENTIFIER VARCHAR, ZUNIQUEID VARCHAR, ZUSERIDENTIFIER VARCHAR );
CREATE INDEX ZABCDMESSAGINGADDRESS_ZADDRESS_INDEX ON ZABCDMESSAGINGADDRESS (ZADDRESS);
CREATE INDEX ZABCDMESSAGINGADDRESS_ZOWNER_INDEX ON ZABCDMESSAGINGADDRESS (ZOWNER);
CREATE INDEX ZABCDMESSAGINGADDRESS_ZSERVICE_INDEX ON ZABCDMESSAGINGADDRESS (ZSERVICE);
CREATE TABLE ZABCDPOSTALADDRESS ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZISPRIMARY INTEGER, ZISPRIVATE INTEGER, ZORDERINGINDEX INTEGER, ZOWNER INTEGER, Z22_OWNER INTEGER, ZCITY VARCHAR, ZCOUNTRYCODE VARCHAR, ZCOUNTRYNAME VARCHAR, ZLABEL VARCHAR, ZREGION VARCHAR, ZSAMA VARCHAR, ZSTATE VARCHAR, ZSTREET VARCHAR, ZSUBLOCALITY VARCHAR, ZUNIQUEID VARCHAR, ZZIPCODE VARCHAR, ZCUSTOMVALUESDICTIONARY BLOB );
CREATE INDEX ZABCDPOSTALADDRESS_ZOWNER_INDEX ON ZABCDPOSTALADDRESS (ZOWNER);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment