4. Subscription database

4.1 Thoughts

The subscription database contains the information which users are subscribed to which feed/s. The user's requests given on the subscription web form go into this database. The database is part of the Action Applications SQL database.

In order to implement the concept of "slice owners", a new table slice_owners is created.

4.2 Specification detail

Table subscriptions

This is a new table.

It contains one entry for each subscription. A subscription in this sense is a single combination of slice owner, content type and category. content_type and category can be empty which means all. Fields:

uid             CHAR(40) NOT NULL,      # user id of the user receiving the feed
category        VARCHAR(16),            # category id, may be NULL for all
content_type    VARCHAR(20),            # points into slice.type
slice_owner     VARCHAR(16) NOT NULL,   # id of slice owner in table 
                                        # slice_owners
frequency       SMALLINT NOT NULL,      # a flag indicating the frequency of
                                        # email posts. -1 means weekly,
                                        # -2 means daily.
last_post       DATETIME DEFAULT '1980-01-01 00:00:00' NOT NULL,
                                        # last time an email was posted
INDEX uid(uid)
INDEX frequency(frequency)

Table slice_owners

This is a new table.

It contains slice owners.

id              VARCHAR(16) NOT NULL,   # id of slice owner
name            CHAR(80),               # clear text name of slice owner
PRIMARY KEY(id)

Table email_auto_users

This is a new table.

It contains information about automatically created user accounts for email subscription services. Because this information can't be stored easily in an ldap storage, we separate it from the regular user database. It can be used for automatic cleanup of email subscription account. Whenever a user account is automatically created, it generated an entry here. Note that we store the password in clear text so we can email it to the user on request.

uid             VARCHAR(40) NOT NULL,   # uid of user as found in users
                                        # database
creation_time   DATETIME NOT NULL,      # creation time
last_change     DATETIME NOT NULL,      # last subscription change
clear_pw        VARCHAR(40),            # clear text password
confirmed       SMALLINT(5) NOT NULL,   # flag indicating if this account's
                                        # email address has been confirmed
confirm_key     VARCHAR(16),            # key for confirmation sent by email
PRIMARY KEY(uid)

Table categories

The existing table is to be modified. A field email_sub_usable should be added:

email_sub_usable SMALLINT(5) DEFAULT '0'

The new field indicates if the category is usable for email subscription. 0 means No, 1 means Yes.

Table slices

The existing table gets two more fields:

owner           VARCHAR(16) NOT NULL,   # id of slice owner in table
                                        # slice_owners
email_sub_enable SMALLINT(5) DEFAULT '1'

The flag indicates that a slice is open for email subscription feeds (1) or not(0). The default of 1 is intended.

Table modifications would need to be done in the data structure file which is included in the doc/ directory as well as in live installations as ALTER TABLE and CREATE TABLE commands. In order to update existing installations, the execution of such a database modifying script would have to be documented.