apache > db
Apache Derby Fortune Server Tutorial
 
Font size:      

Tables

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

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);