Oasis Contacts Database Format

The database used by the Oasis software on the iPaq 310 to store Contacts details is, in fact, a SQLite3 database file kept in \ResidentFlash\IPAQ\OS\sqdb. This means that you can copy the file out to your Windows or Linux desktop and manipulate the file using the normal SQlite3 tools. Below is the database schema.

CREATE TABLE APPOINTMENTS(
        ID INTEGER NOT NULL UNIQUE,
        ALLDAYEVENT INTEGER,
        RECURRING INTEGER,
        REMINDERSET INTEGER,
        STARTTIME FLOAT,
        ENDTIME FLOAT,
        SUBJECT TEXT,
        REMARK TEXT,
        LOCATION TEXT,
        CATEGORY TEXT,
        CREATOR BLOB,
        ATTENDEES BLOB,
        ATTENDEECOUNT INTEGER,
        RECURRENCEPATTERN BLOB,
        REMINDERBEFORE INTEGER,
        ISEXCEPTION INTEGER,
        EXCEPTION BLOB,
        LOCATIONFROM TEXT,
        DRIVETIMESEC INTEGER,
        TRAVELREMINDER INTEGER,
        CONSTRAINT APPOINTMENTS_PK PRIMARY KEY (ID)
);

CREATE TABLE CONTACTS( ID INTEGER NOT NULL UNIQUE,
        ANNIVERSARY REAL,
        BIRTHDAY REAL,
        BUSINESSFAXNUMBER TEXT,
        COMPANYNAME TEXT,
        DEPARTMENT TEXT,
        EMAIL1ADDRESS TEXT,
        MOBILETELEPHONENUMBER TEXT,
        OFFICELOCATION TEXT,
        PAGERNUMBER TEXT,
        BUSINESSTELEPHONENUMBER TEXT,
        JOBTITLE TEXT,
        HOMETELEPHONENUMBER TEXT,
        EMAIL2ADDRESS TEXT,
        SPOUSE TEXT,
        EMAIL3ADDRESS TEXT,
        HOME2TELEPHONENUMBER TEXT,
        HOMEFAXNUMBER TEXT,
        CARTELEPHONENUMBER TEXT,
        ASSISTANTNAME TEXT,
        ASSISTANTTELEPHONENUMBER TEXT,
        CHILDREN TEXT,
        CATEGORIES TEXT,
        WEBPAGE TEXT,
        BUSINESS2TELEPHONENUMBER TEXT,
        RADIOTELEPHONENUMBER TEXT,
        FILEAS TEXT,
        YOMICOMPANYNAME TEXT,
        YOMIFIRSTNAME TEXT,
        YOMILASTNAME TEXT,
        TITLE TEXT,
        FIRSTNAME TEXT,
        MIDDLENAME TEXT,
        LASTNAME TEXT,
        SUFFIX TEXT,
        HOMEADDRESSSTREET TEXT,
        HOMEADDRESSCITY TEXT,
        HOMEADDRESSSTATE TEXT,
        HOMEADDRESSPOSTALCODE TEXT,
        HOMEADDRESSCOUNTRY TEXT,
        OTHERADDRESSSTREET TEXT,
        OTHERADDRESSCITY TEXT,
        OTHERADDRESSSTATE TEXT,
        OTHERADDRESSPOSTALCODE TEXT,
        OTHERADDRESSCOUNTRY TEXT,
        BUSINESSADDRESSSTREET TEXT,
        BUSINESSADDRESSCITY TEXT,
        BUSINESSADDRESSSTATE TEXT,
        BUSINESSADDRESSPOSTALCODE TEXT,
        BUSINESSADDRESSCOUNTRY TEXT,
        HOMEADDRESSVALID INTEGER,
        BUSINESSADDRESSVALID INTEGER,
        OTHERADDRESSVALID INTEGER,
        HOMEADDRESSGPH INTEGER,
        HOMEADDRESSGPV INTEGER,
        BUSINESSADDRESSGPH INTEGER,
        BUSINESSADDRESSGPV INTEGER,
        OTHERADDRESSGPH INTEGER,
        OTHERADDRESSGPV INTEGER,
        PICTUREDATA BLOB,
        CONSTRAINT CONTACTS_PK PRIMARY KEY (ID)
);

CREATE TABLE Reminders(
        ID INTEGER NOT NULL,
        PARENTID INTEGER,
        OCCURENCE INTEGER,
        TIME FLOAT,
        SNOOZE INTEGER,
        ALARMED INTEGER,
        TYPE INTEGER,
        DISSMISSED INTEGER,
        CONSTRAINT Reminders_PK PRIMARY KEY (ID)
);

CREATE TABLE TASKS(
        ID INTEGER NOT NULL UNIQUE,
        SUBJECT TEXT,
        CATEGORY TEXT,
        STARTDATE FLOAT,
        DUEDATE FLOAT,
        DATECOMPLETED FLOAT,
        REMINDERTIME FLOAT,
        OCCURENCEDATE FLOAT,
        IMPORTANCE INTEGER,
        SENSITIVITY INTEGER,
        RECURRENCEPATTERN BLOB,
        REMINDEROPT INTEGER,
        COMPLETE INTEGER,
        RECURRING INTEGER,
        REMINDERSET INTEGER,
        ISEXCEPTION INTEGER,
        EXCEPTION BLOB,
        CONSTRAINT TASKS_PK PRIMARY KEY (ID)
);

CREATE INDEX APPOINTMENTSid ON APPOINTMENTS (ID ASC);
CREATE INDEX CONTACTSid ON CONTACTS (ID ASC);
CREATE INDEX Remindersid ON Reminders (ID ASC);
CREATE INDEX TASKSid ON TASKS (ID ASC);

Back to: Ipaq310

-- WarrenToomey - 23 Jul 2008

Topic revision: r1 - 2008-07-23 - WarrenToomey
 
This site is powered by the TWiki collaboration platform Powered by PerlCopyright © 2008-2018 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback