/**
* Contacts model database schema (file version v8).
*
*
*/
-- CREATE SEQUENCE contacts_id_sequence INITIAL_VALUE=0 INCREMENT=1;
CREATE table contacts
(
cm_id INTEGER NOT NULL, -- see (1): DEFAULT NEXT VALUE OF contacts_id_sequence
cm_type INTEGER NOT NULL,
cm_preftemplaterefid INTEGER NULL,
cm_uidstring VARCHAR(244) NULL,
cm_last_modified TIMESTAMP NULL,
cm_contactcreationdate TIMESTAMP NULL,
cm_attributes INTEGER NOT NULL, -- should be UNSIGNED
cm_replicationcount INTEGER NOT NULL, -- should be UNSIGNED
cm_header LONGBLOB NULL, -- BLOB
cm_textblob LONGBLOB NULL, -- BLOB
cm_searchabletext LONGTEXT NULL, -- CLOB
PRIMARY KEY (cm_id),
FOREIGN KEY (cm_preftemplaterefid) REFERENCES contacts(cm_id)
ON DELETE SET NULL
ON UPDATE CASCADE
);
CREATE table identitytable
(
parent_cmid INTEGER NOT NULL,
cm_firstname VARCHAR(255) NULL,
cm_lastname VARCHAR(255) NULL,
cm_companyname VARCHAR(255) NULL,
cm_type INTEGER NULL,
cm_attributes INTEGER NULL, -- should be UNSIGNED
cm_hintfield BIT(8) NULL, -- binary(1)
cm_exthintfield BIT(16) NULL,-- binary(2)
cm_firstnmprn VARCHAR(255) NULL,
cm_lastnmprn VARCHAR(255) NULL,
cm_companynmprn VARCHAR(255) NULL,
PRIMARY KEY (parent_cmid),
FOREIGN KEY (parent_cmid) REFERENCES contacts(cm_id)
);
-- CREATE SEQUENCE emailtable_fieldid_sequence INITIAL_VALUE=0 INCREMENT=1;
CREATE table emailtable
(
emailparent_cmid INTEGER NOT NULL,
email_fieldid INTEGER NOT NULL, -- see (2) DEFAULT NEXT VALUE OF emailtable_fieldid_sequence
emailaddress VARCHAR(255) NULL,
PRIMARY KEY (email_fieldid, emailparent_cmid),
FOREIGN KEY (emailparent_cmid) REFERENCES contacts(cm_id)
);
CREATE table phone
(
cm_id INTEGER NOT NULL,
cm_phonematching INTEGER NOT NULL,
cm_extendedphonematching INTEGER NOT NULL,
PRIMARY KEY (cm_phonematching, cm_id),
FOREIGN KEY (cm_id) REFERENCES contacts(cm_id)
);
CREATE table groups
(
cm_id INTEGER NOT NULL,
cm_members INTEGER NOT NULL, -- this should really be cm_member, but it is called cm_members in the current database
PRIMARY KEY (cm_members, cm_id),
FOREIGN KEY (cm_id) REFERENCES contacts(cm_id),
FOREIGN KEY (cm_members) REFERENCES contacts(cm_id)
);
CREATE table groups2
(
cm_id INTEGER NOT NULL,
cm_groupmembers LONGBLOB NULL, -- BLOB
PRIMARY KEY (cm_id),
FOREIGN KEY (cm_id) REFERENCES contacts(cm_id)
);
CREATE table sync
(
cm_id INTEGER NOT NULL, -- why is this UNSIGNED in ?
cm_lastsyncdate TIMESTAMP NULL,
PRIMARY KEY (cm_id),
FOREIGN KEY (cm_id) REFERENCES contacts(cm_id) -- does this reference contacts?
);
CREATE table preferences
(
cm_preffileid SMALLINT NULL,
cm_preftemplateid INTEGER NOT NULL,
cm_prefowncardid INTEGER NULL,
cm_prefcardtemplateprefid INTEGER NULL,
cm_preffilever INTEGER NOT NULL,
cm_prefcardtemplateid LONGBLOB NULL, -- BLOB
cm_prefgroupidlist LONGBLOB NULL, -- BLOB
cm_creationdate TIMESTAMP NULL,
cm_machineuid BIGINT NULL,
cm_prefsortorder LONGBLOB NULL, -- BLOB
FOREIGN KEY (cm_preftemplateid) REFERENCES contacts(cm_id),
FOREIGN KEY (cm_prefowncardid) REFERENCES contacts(cm_id),
FOREIGN KEY (cm_prefcardtemplateprefid) REFERENCES contacts(cm_id)
);