The Database Schema

Let's create a database table to store a list of our favourite 80's tunes, including lyrics. Here's an adequate database table for such a collection:

CREATE TABLE myapp_tunes (
    # Collection-specific fields:
    id                      INT NOT NULL AUTO_INCREMENT,
    title                   CHAR(72) NOT NULL,
    artist                  CHAR(72) NOT NULL,
    album                   CHAR(72) NOT NULL,
    style                   CHAR(72) NOT NULL,
    year                    YEAR NOT NULL,
    lyrics                  TEXT NOT NULL,

    # Sitellite-specific fields:
    sitellite_access        CHAR(16) NOT NULL,
    sitellite_status        CHAR(16) NOT NULL,
    sitellite_startdate     DATETIME default NULL,
    sitellite_expirydate    DATETIME default NULL,
    sitellite_owner         CHAR(48) NOT NULL default '',
    sitellite_team          CHAR(48) NOT NULL default '',

    PRIMARY KEY (id),
    INDEX (artist, album, style, year)
);

As a "best practice" rule of thumb, I generally encourage app developers to prefix their database table names with the name of their app, to distinguish them from one another, and to clearly identify which app they belong to. This helps avoid conflicts where five apps all want a table named "category". ;)

The Sitellite-specific fields have the following purposes:

• "sitellite_access" contains the access level of each item in the collection.
• "sitellite_status" contains the status of ecah item in the collection.
• "sitellite_startdate" defines a date and time to approve the item on if its "sitellite_status" value is "queued".
• "sitellite_expirydate" defines a date and time to archive the item on if its "sitellite_status" value is "approved".
• "sitellite_owner" is the original user that created the item.
• "sitellite_team" is the team to which the item is assigned (by default, the same team that the owner belongs to).

Before we continue, to store our style list let's create a simple secondary table. We'll use this table via a Selector widget later on.

CREATE TABLE myapp_styles (
    id                      INT(11) NOT NULL AUTO_INCREMENT,
    name                    CHAR(72) NOT NULL,
    PRIMARY KEY (id),
    INDEX (name)
);

So far, we haven't seen very much that is different from what you would do to create any type of database table/collection. Now, we'll add the table Rex will use to store our revision history for each item in our myapp_tunes table.

CREATE TABLE myapp_tunes_sv (
    # Rex-specific fields:
    sv_autoid               INT(11) NOT NULL AUTO_INCREMENT,
    sv_author               VARCHAR(48) NOT NULL default '',
    sv_action               ENUM('created', 'modified', 'republished', 'replaced', 'restored', 'deleted') NOT NULL default 'created',
    sv_revision             TIMESTAMP(14) NOT NULL,
    sv_changelog            TEXT NOT NULL,
    sv_deleted              ENUM('yes','no') NOT NULL default 'no',
    sv_current              ENUM('yes','no') NOT NULL default 'yes',

    # Collection-specific fields:
    id                      INT NOT NULL,
    title                   CHAR(72) NOT NULL,
    artist                  CHAR(72) NOT NULL,
    album                   CHAR(72) NOT NULL,
    style                   CHAR(72) NOT NULL,
    year                    YEAR NOT NULL,
    lyrics                  TEXT NOT NULL,

    # Sitellite-specific fields:
    sitellite_access        CHAR(16) NOT NULL,
    sitellite_status        CHAR(16) NOT NULL,
    sitellite_startdate     DATETIME default NULL,
    sitellite_expirydate    DATETIME default NULL,
    sitellite_owner         CHAR(48) NOT NULL default '',
    sitellite_team          CHAR(48) NOT NULL default '',

    PRIMARY KEY (sv_autoid),
    INDEX sv_author (sv_author,sv_action,sv_revision,sv_deleted,sv_current),
    INDEX (artist, album, style, year)
);

As you can see, this table is the same as our myapp_tunes table, with the addition of seven new fields used to track changes by Rex. For every change made to a single item in the myapp_tunes table, a new entry is made in myapp_tunes_sv.

Here is a list of additional fields and properties of this table, so that you know what each does:

• The "_sv" table name suffix is used to distinguish the table from the myapp_tunes table. The "_sv" stands for "Sitellite Versioning" although a "_rex" suffix might have been more consistent (hindsight, yadda yadda).
• "sv_autoid" contains a unique numeric identifier for each revision. Revisions are associated with the original field in myapp_tunes via the "id" field, which they have in common.
• "sv_author" contains the user that has made the current change, or revision.
• "sv_action" contains the type of change made. Types include:
  • created - The item was added for the first time.
  • modified - The item was modified.
  • republished - The item was modified, but the change was set to "draft" again, so the change will need to be marked "approved" before it overwrites the copy in the source.
  • replaced - The item, which had been republished, has now been approved, and has overwritten the live copy in the source.
  • restored - The item has been brought back from the dead (ie. it was deleted, then restored).
  • deleted - The item is no more.
• "sv_revision" contains the date and time of the revision.
• "sv_changelog" contains an optional summary from the author of the current revision, usually describing what it is that he/she changed.
• "sv_deleted" tells you whether the revision is currently deleted or not.
• "sv_current" tells you whether the revision is the most current revision or not.

Now, we have done all that is necessary at the database level, and we are ready to create our collection definition file so that Sitellite knows how to handle our collection for us.

Page 1: Creating a Rex Collection
Page 2: The Database Schema
Page 3: The Collection Definition File
Page 4: The Collection Definition File (Continued)
Page 5: Accessing Rex Programmatically
Page 6: Supplimentary Rex APIs

All Tutorials