Microsoft Works Calendar

The Works Calendar stores all its data into %ALLUSERSPROFILE%\Application Data\Microsoft\Works\. This means all calendars are shared with all users on the computer! The calendar does not include any access control settings, and the files are quite easy to play with.

The most important file is mswkscal.wcd, whose file extension probably means Works Calendar Database. It is a Jet database, with the very recognizable Standard Jet DB signature. Jet is the engine behind Microsoft Office Access, and you can indeed open the file with Access to read it. With Access 2003, I have to go through three different security alerts telling me to block untrusted macros on that database, but it does open correctly.

There are three tables and two queries. Their properties say they were created in 1997/1998. Here’s an approximative representation of the database as SQL:

CREATE TABLE Events (
    ID BIGINT NOT NULL PRIMARY KEY AUTOINCREMENT,
    lDuration BIGINT,
    szSubject TEXT CHECK (szSubject <> ''),
    szBody TEXT CHECK (szBody <> ''),
    szLocation VARCHAR(255) CHECK (szLocation <> ''),
    rgRecurOptions BLOB NOT NULL,
    fRecurring BIGINT DEFAULT FALSE,
    lCountryCode BIGINT DEFAULT 0,
    lCodePage BIGINT DEFAULT 0,
    lCharSet BIGINT DEFAULT 0,
    MasterId BIGINT DEFAULT 0,
    dtModify TIMESTAMP,
    rgCats BLOB, -- An array of category IDs
    -- Unsigned. ID of the client application with whom this record is associated
    ulClientID BIGINT, 
    -- Unsigned. ID of the record in the context of the client application
    ulClientRecordID BIGINT,
    -- Type of the event depending on the client application.
    -- For the Works Address Book, this indicates Birthday or Anniversary.
    ulClientValueType BIGINT,
    -- Introduced in Works 9
    szClientRecStrId VARCHAR(255)
);
CREATE INDEX lCountryCode ON Events (lCountryCode);
CREATE INDEX MasterId ON Events (MasterId);
CREATE INDEX ulClientID ON Events (ulClientID);
CREATE INDEX ulClientRecordID ON Events (ulClientRecordID);
-- This is a duplicate index, just like in the original DB.
CREATE UNIQUE INDEX ID ON Events (ID);

CREATE TABLE EventInstance (
    dtStart TIMESTAMP,
    dtEnd TIMESTAMP,
    EventID BIGINT NOT NULL REFERENCES Events (ID) ON UPDATE CASCADE ON DELETE CASCADE,
    -- Indicates if this is the master record
    fRecurRec BIGINT DEFAULT FALSE,
    -- Indiciates if this is a deleted recurring record
    fDeleted BIGINT DEFAULT FALSE,
    -- When a reminder should be shown to the user
    dtRemind TIMESTAMP,
    -- Number of seconds before the event when a reminder should be shown
    lRemind BIGINT DEFAULT 0,
    -- Used to distinguish recurring event records
    -- The original DB defines this as auto-increment, but without it being a primary key,
    -- which is not supported in SQLite.
    SequenceId BIGINT NOT NULL
);

-- Duplicate of Events.rgCats, but a more normal way of representing arrays
CREATE TABLE CatToEvents (
    CategoryId BIGINT NOT NULL,
    EventId BIGINT NOT NULL REFERENCES Events (ID) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE VIEW EventQuery AS
SELECT ID, dtStart, dtEnd, dtRemind, lDuration, lRemind, szSubject, szBody, szLocation
FROM Events
INNER JOIN EventInstance ON Events.ID = EventInstance.EventID;

There are two queries stored in that database. One of them is defined as a view above, but the other takes parameters, which means an SQLite equivalent will require more than what SQLite can provide! Here is an attempt using the define extension of SQLean:

-- Helper function to split a string into a table of integers
CREATE VIRTUAL TABLE parse_integer_list USING define((
    WITH RECURSIVE splitter (value, str) AS (
            SELECT NULL, :str 
        UNION
            SELECT
                CASE
                    WHEN INSTR(str, :sep) > 0
                    THEN SUBSTR(str, 1, INSTR(str, :sep) - 1)
                    ELSE str
                END,
                SUBSTR(str, INSTR(str, :sep) + 1)
            FROM splitter
    )
    SELECT CAST(value AS INTEGER)
    FROM splitter
    WHERE value IS NOT NULL
));

CREATE VIRTUAL TABLE QEventToCat USING define((
    SELECT Events.*, *
    FROM Events INNER JOIN CatToEvents ON Events.ID = CatToEvents.EventId
    WHERE CategoryId in (SELECT parse_integer_list(:szCatList))
    AND dtStart > :QdtEnd AND dtEnd < :QdtStart;
));

That last query retrieves all events, filtered by a list of category IDs and in a defined interval. Events in multiple categories will be duplicated, once per category, with a different CategoryId returned.

That database is a mess. I rewrote some of the comments for the fields. The original comments mention WAB, which is usually the intialism for the Windows Address Book. In this case, this instead refers to the Works Address Book, a feature removed in Works 5.0.

The Works Address Book was introduced roughly at the same time as the Windows Address Book, though the Windows Address Book was part of Internet Explorer 3, so not necessarily available for everyone. It was a special kind of Works Database that would automatically add reminders in your calendar for your contact’s birthdays, hence the whole “client application” thing in the calendar’s database. All versions of Works starting from 5.0 instead provide an Address Book Converter, which takes the Works Database of the old address book and imports it into the Windows Address Book. There are a few text files lying around in the installation directory related to this, trying to map field names in the database to Windows Address Book fields, as those in the database are localized.

Look at the CalMRU.dat, logins.ini and wkcalcat.dat files

~lucidiot's wiki