apache > db

- Home
- Apache Derby

Font size:

# Tables

- Table fortune_meta
- Table offensive
- Table fortunes

The Fortune Server schema is composed of the three tables depicted in Figure 10. The fortune_meta table stores meta-data about the fortune, the offensive table stores a list of offensive words, and the fortunes table stores the actual fortune.

Figure 10: Fortune Server Schema

## Table fortune_meta

The fortune_meta table tracks information about the data sets that are loaded into the database, including from where the data set was obtained and what license it is associated with. Each column is described below:

fortune_meta
Column Name | Description
--- | ---
name | The name of the original cookie file; for example, art or computers. This column is the primary key, which uniquely identifies an entry. Derby automatically creates an index on the primary key.
package | Package to which the name belongs. For example, the Gentoo fortune-mod package contains many cookie files.
source | Where the package/cookie files were obtained. All packages/files distributed with the Derby Tutorial Fortune Server came from www.gentoo.org.
license | License governing cookie file. So far, BSD, GPL-1, GPL-2.
url | URL for the data set, if one exists and is valid.

Below is the SQL statement that creates the fortune_meta table:

    create table fortune_meta
    (name        varchar(24) not null constraint name_pk primary key,
     package     varchar(32) not null,
     source      varchar(64) not null,
     license     varchar(8)  not null,
     url         varchar(64)
    );

## Table offensive

The offensive table stores a list of words that might cause offense. Each column is described below:

offensive
Column Name | Description
--- | ---
word | The offensive word as a regular expression. For example, if "golf" were offensive we might enter it as "[Gg][Oo][Ll][Ff]" so we would catch every combination of upper and lower case letters. This column is the primary key, so it automatically gets a backing index.
alternate | An inoffensive replacement for the word; for example, "G===".
level | Degree of offense. 0 means the it is not offensive (it's safe), so this column has a check constraint that requires values > 0.

The columns in the offensive table can be used for regular expression match and replace. Later you'll see how it gets used to set the offense level in the fortunes table.

Below is the SQL statement that creates the offensive table:

    create table offensive
    (word      varchar(20) not null constraint word_pk primary key,
     alternate varchar(20),
     level     smallint not null constraint level_ck check (level > 0)
    );

## Table fortunes

The fortunes table stores each fortune. The columns are summarized in the table below:

fortunes
Column Name | Description
--- | ---
id | A unique integer value. The program that loads data into the fortunes table sets this value and it gets used with the tutRand SQL function to select a fortune at random. This column is the primary key, so it gets a backing index.
src | The source cookie file. This column is a foreign key to fortune_meta.name, so a value may only be inserted into this table if the fortune_meta.name entry already exists for it. Derby automatically creates an index on foreign key columns.
offense | The level of offense assigned to this fortune. It has a check constraint that makes sure that the value is >= 0 and the default is 0 (i.e., it's safe).
fortune | The text of the fortune.

Below is the SQL statement that creates the fortunes table:

    create table fortunes
    (id          int         not null constraint id_pk primary key,
     src         varchar(24) not null,
     offense     smallint    default 0
                             constraint off_ck check (offense >= 0),
     fortune     varchar(3000),
     foreign key(src) references fortune_meta(name)
    );

After data is loaded into the fortunes table, the offense value for each fortune gets updated by matching offensive words to the fortune using the tutMatch SQL function:

    update fortunes
    set    offense=N
    where  id in
           (select f.id
            from fortunes f, offensive o
     where o.level=N
     and tutMatch(f.fortune, o.word)=N);