apache > db

- Home
- Apache Derby

Font size:

# Exercise

- Create Database
- Load Data
- Update Offensive Fortunes
- Execute Queries
  - Fetch random fortunes
  - Match fortunes to a regular expression
  - Replace value based on a regular expression

## Create Database

Copy FORTUNE_HOME/sql/create_fortunes.sql to a new file. You'll use this to create your database. Notice that the first line in the file creates a database called fortunes:

    connect 'jdbc:derby:fortunes;create=true';

Change "fortunes" to the name of your choice.

Now review the rest of the file. You'll see that it first creates and populates the fortune_meta table, then the offensive table. It creates the fortunes table, but doesn't load data into it yet. You'll do that with a Java program in the Load Data step.

Finally it creates the SQL functions and loads the fortuneServerSql.jar file into Derby.

After you edit that file, run it as shown below (Korn Shell syntax):

    java -Dderby.system.home=$FORTUNE_HOME  org.apache.derby.tools.ij \
         create_fortunes.sql >& create_fortunes.log &

A successful run will show some errors because the script first drops objects before attempting to create them.

## Load Data

The program that loads fortune data into the fortunes table is in FORTUNE_HOME/java/LoadData. You'll see the code itself in the Java section. Right now you'll just use it to load data.

The LoadFortune program loads a cookie file into the fortunes table. Each run does the following:

- Assigns a unique id to the fortunes being loaded. It fetches the max id from the fortunes table to obtain its starting number.
- Opens the cookie file and starts reading it.
- Inserts each fortune into the fortunes table along with a unique id and the name of the cookie file. It preserves the ASCII format of the fortunes it loads by retaining the new lines.

The LoadFortune program looks for cookie files in ../../data. (And later you can easily change it to load cookies from wherever you like.)

The LoadFortune syntax is:

    java -Dderby.system.home=$FORTUNE_HOME LoadFortune database file

The derby_load.sh shell script in that directory loads all the fortunes that are in FORTUNE_HOME/data. Feel free to modify it for your local environment, then load the data. A successful run should show output that looks like this:

    $ java -Dderby.system.home=$FORTUNE_HOME LoadFortune fortunes2 ascii-art
    Loaded ../../data/ascii-art(minId=1 maxId=10)

    $ java -Dderby.system.home=$FORTUNE_HOME LoadFortune fortunes2 art
    Loaded ../../data/art(minId=11 maxId=470)

## Update Offensive Fortunes

The offensive table stores offensive words as regular expressions, and the values in that table get used for updating the offense column in the fortunes table. This is completely tunable based on personal preferences.

With any luck the example below won't offend any readers -- if it does, my sincerest apologies! It adds the word "damn", in any combination of upper and lowercase as an offensive word and specifies "D===" as a suitable alternate:

    insert into offensive
    values ('[Dd][Aa][Mm][Nn]', 'D===', 1);

It's rated a 1 because I personally don't find the word to be terribly offensive, but you can rate it (and other words) however you like.

At any rate, objectionable words are entered into the offensive table as a regular expression. Replacement text is also stored in the offensive table for implementing applications that go ahead and output offensive fortunes by substituting alternate text. However, the replacement text model is flawed (that's a warning). Can anybody say how?

The fortunes table is then updated based on values in the offensive table with the query shown below:

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

However, there's a caveat! Since a given fortune might have multiple expletives with different levels assigned, you would want the offense entry for that fortune to be set to the most objectionable entry. So the FORTUNE_HOME/sql/update_offensive.sql script distributed with the Fortune Server updates in order of offense, starting with entries at level 1 and ending with level 5.

Copy FORTUNE_HOME/sql/update_offensive.sql to a new file and edit it to put in your database name. Then run it as shown below:

    -Dderby.system.home=$FORTUNE_HOME  org.apache.derby.tools.ij \
              update_offensive.sql >& update_offensive.log &

## Execute Queries

### Fetch random fortunes

The tutRand() SQL function lets us select fortunes at random.

The query below retrieves the minimum id and maximum id in the fortunes table. Counting the records verifies that there aren't any gaps in id numbers:

    ij> select min(id), max(id), count(*) from fortunes;
    1          |2          |3
    -----------------------------------
    |13685      |13685

This next query selects an inoffensive fortune at random, supplying the minimum and maximum id numbers as the lower and upper bounds. Soon you'll see how the fortunes.offense value gets set. If the randomly selected fortune were offensive, this query would return no rows found.

    ij> select id, fortune from fortunes
    where  id=tutRand(1, 13685)
    and    offense = 0;
    ID         |FORTUNE
    ------------------------------------------------------
    8659       |To refuse praise is to seek praise twice.

The next example performs the same query as the previous, but it dynamically determines the minimum and maximum bounds with select expressions:

    ij> select id, fortune from fortunes
    where  id=tutRand(
           (select min(id) from fortunes),
           (select max(id) from fortunes))
    and    offense = 0;
    ID         |FORTUNE
    -------------------------------------------------------------------
    2931       |Expert, n.:
             Someone who comes from out of town and shows slides.

    1 row selected

This next query fetches a fortune at random from the pets category:

    select id, fortune from fortunes
    where id=tutRand(
         (select min(id) from fortunes where src='pets'),
         (select max(id) from fortunes where src='pets'))
    nd   offense = 0;
    D         |FORTUNE
    ------------------------------------------------------------------
    900       |With a rubber duck, one's never alone.
                   -- "The Hitchhiker's Guide to the Galaxy"

### Match fortunes to a regular expression

The tutMatch() SQL function lets us select fortunes that match a regular expression. The first query below retrieves all fortunes that mention 'eagle' or 'crow', but it only matches on word boundaries, so we won't get matches for 'microwave', 'crowd', or 'beagle'.

    ij> select src, fortune from fortunes
    where tutMatch(fortune, '\b(eagle|crow)\b')=1;
    SRC                     |FORTUNE
    ----------------------------------------------------------------------------------
    definitions             |Hippogriff, n.:
            An animal (now extinct) which was half horse and half griffin.
            The griffin was itself a compound creature, ha&
    ethnic                  |The American nation in the sixth ward is a fine
    people; they love the eagle -- on the back of a dollar.
                    -- Finlay Peter Dunne
    miscellaneous           |       A crow perched himself on a telephone wire.
    He was going to make a long-distance caw.
    miscellaneous           |I'd be a poorer man if I'd never seen an eagle fly.
                    -- John Denver

    [I saw an eagle fly once.  Fortunately, I had my eagle fl&
    miscellaneous           |The eagle may soar, but the weasel never gets
    sucked into a jet engine.
    people                  |Even a hawk is an eagle among crows.
    songs-poems             |Oh, I have slipped the surly bonds of earth,
    And danced the skies on laughter silvered wings;
    Sunward I've climbed and joined t&
    wisdom                  |It is said that the lonely eagle flies to the
    mountain peaks while the lowly
         ant crawls the ground, but cannot the soul of the &

    8 rows selected

Notice the ampersands (&) at the end of the some of the entries in the output for the previous query. It means that output was truncated. MaximumDisplayWidth lets you increase the ij display width.

Also notice the odd line breaks. The Java program that loads the fortunes retains the line feeds to keep the original ASCII format, which is important for the ascii-art and poem cookie files. For example, picture what would happen with the example below if it did not keep the original line feeds:

    ij> maximumDisplayWidth 300;
    ij> select fortune from fortunes where id=1;
    FORTUNE
    --------------------------------------------------------
                     (  /\__________/\  )
                      \(^ @___..___@ ^)/
                       /\ (\/\/\/\/) /\
                      /  \(/\/\/\/\)/  \
                    -(    """"""""""    )
                      \      _____      /
                      (     /(   )\     )
                      _)   (_V) (V_)   (_
                     (V)(V)(V)   (V)(V)(V)

    1 row selected

Keeping the original ASCII format isn't optimal for ij. The nice part about this is you'll be able to modify the code to load and format cookie files however you like. :-)

### Replace value based on a regular expression

The tutReplace() function adds a replacement text argument. So, if a value matches the regular expression, that value gets replaced by the third argument.

This example fetches fortunes in the linuxcookie category that mention bug (in any combination of upper and lowercase characters) and adds asterisks to highlight the matches:

    ij> select id,
               tutReplace(fortune, '[Bb][Uu][Gg]', '  ***$0***')
    from fortunes
    where tutMatch(fortune, '[Bb][Uu][Gg]')=1
    and src = 'linuxcookie'
    and offense=0;
    ID         |2
    -------------------------------------------------------------------------------
    5699       |And 1.1.81 is officially ***Bug***Free(tm), so if you receive
    any ***bug***-reports on it, you know they are just evil lies."
    (&
    5730       |"I'm an idiot.. At least this one [***bug***] took about
    5 minutes to find.." (Linus Torvalds in response to a ***bug*** report&
    5731       |I've run DOOM more in the last few days than I have the last
    few months.  I just love de***bug***ging ;-)
    (Linus Torvalds)
    5754       |"Never make any mistaeks."
    (Anonymous, in a mail discussion about to a kernel ***bug*** report.)
    5792       |Who wants to remember that escape-x-alt-control-left shift-b
    puts you into super-edit-de***bug***-compile mode?
    (Discussion in &