Introduction
Derby Fortune Server Overview
The Apache Derby Fortune Server, shortened to just "Fortune Server" throughout most of this tutorial, outputs fortunes like the UNIX fortune command does. If you aren't familiar with the fortune command, it outputs an entry from a file that might be a quote, a short story, a poem, a joke, ASCII art -- or pretty much anything at all these days! You can read more about it in the UNIX "fortune" command section below.
Instead of storing fortunes in flat files like the UNIX fortune command does, the Fortune Server stores them in a Derby database and fetches them back using standard SQL statements. The Fortune Server lets you fetch a fortune at random or based on a regular expression. It also lets you restrict a search to one category of data and lets you set your tolerance for offensive fortunes.
Figure 1 shows a web application included in the Fortune Server distribution that specifies a regular expression search for "eagle" or "crow". The word boundary tag (\b) excludes matches within a word to avoid words such as "microwave" or "beagle". The example restricts the search to just the "miscellaneous" category -- the default is "None", which includes all categories. Finally, it specifies an offense level of "<= 1" (the default is "= 0").
When you click on the Fetch Fortune button, it fetches any fortune(s) that match the query criteria and displays them at the bottom of the web page. The Fortune Server web application highlights any text that matched the query criteria. In addition to displaying search results, it also displays the SQL query that was executed behind the scenes as shown in Figure 1.
Figure 1: Fortune Server Web Application
If you're already familiar with SQL (and your eye sight is good enough that you can actually read the query in Figure 1[1]) you might spot two SQL functions in the query that you don't recognize:
-
tutMatch returns 1 if a string in the first argument matches the regular expression in the second.
-
tutReplace replaces any matched text with the third argument. The Fortune Server web application uses it to highlight matched text with a <span class=hi-lite>[2] tag.
The tutMatch and tutReplace functions aren't part of standard SQL. The Fortune Server creates them by embedding Jakarta Regexp in Derby. The Fortune Server adds another SQL function that Figure 1 doesn't show, tutRand, which uses the Jakarta Mathematics Library to generate a random number. These new SQL functions allow the Fortune Server to fetch a fortune at random or fetch fortunes based on a regular expression.
UNIX "fortune" command
If a UNIX machine has the fortune program installed, typing man fortune shows the full syntax. This section highlights just a few features.
When run with no arguments, fortune outputs a random entry, as shown below:
$ fortune You have the capacity to learn from mistakes. You'll learn a lot today.
When run with the "-m" option, fortunes that match a regular expression get output. For example, the command below looks for all fortunes that have "Zebra" or "zebra" somewhere in the text:
$ fortune -m "[Zz]ebra" (computers) % A biologist, a statistician, a mathematician and a computer scientist are on a photo-safari in Africa. As they're driving along the savannah in their jeep, they stop and scout the horizon with their binoculars. The biologist: "Look! A herd of zebras! And there's a white zebra! Fantastic! We'll be famous!" The statistician: "Hey, calm down, it's not significant. We only know there's one white zebra." The mathematician: "Actually, we only know there exists a zebra, which is white on one side." The computer scientist : "Oh, no! A special case!" % (science) % FORTUNE'S FUN FACTS TO KNOW AND TELL: #44 Zebras are colored with dark stripes on a light background. %
The fortune command also lets you specify which cookie file to use. The next command restricts the regular expression search to just the science file:
$ fortune -m "[Zz]ebra" science (science) % FORTUNE'S FUN FACTS TO KNOW AND TELL: #44 Zebras are colored with dark stripes on a light background. %
The fortune command also supports options that include (-a) or that specify (-o) offensive fortunes.
Fortune Server functionality
The Fortune Server supports many fortune command features, including:
- Output a fortune selected at random.
- Output fortunes that match a regular expression.
- Fetch from just a subset of the data, such as "science".
- Suppress or specifically enable the output of offensive fortunes.
The Fortune Server does not support the ability to:
- Name multiple categories (other than the default, which includes all).
- Specify percentage probabilities for selection from a specific category.
These two features are left as an exercise for the motivated reader.
Fortune Server Dataset
The tutorial dataset was built on a Gentoo Linux machine. A JDBC program, which is included in the Fortune Server distribution, reads files in /usr/share/fortune and loads them into a Derby table. You'll see the source code for this program in the section on Java and JDBC.
Only freely available fortunes with a license allowing redistribution are included in the Fortune Server software distribution; specifically, the following packages were loaded from http://www.gentoo.org:
- fortune-mod (BSD license)
- fortune-mod-kernelcookies (GPL-2)
- fortune-mod-osfortune (GPL-1)
- fortune-mod-starwars (GPL-2)
- fortune-mod-tao (GPL-1)
Tutorial Goals
This tutorial won't teach you everything there is to know about Derby, SQL, Java, JDBC, or building Web applications. It focuses on several features in the context of one application that uses Derby in its embedded mode.
The tutorial database focuses on a subset of SQL features, including:
- How to create tables with primary and foreign keys and check constraints.
- How to compose SQL SELECT statements
The tutorial database focuses on a subset of Java and JDBC capabilities, including:
- How to implement a JDBC application that inserts data into a Derby database and how to fetch data back from a Derby database.
- How to create SQL functions implemented in Java that embed another technology into Derby, such as the Jakarta Mathematics Library and Jakarta Regexp.
- How to embed Derby in other technologies, such as Jakarta Tomcat.
End Notes
This section fills in picky details that would otherwise clutter up the main discussion.
[1]: Fortune Server SQL Query
If your eye sight isn't good enough to read that query in Figure 1, here it is:
select src as Category, tutReplace(fortune, '\b(eagle|crow)\b', '<span class=hi-lite>$0</span>') as Fortune from fortunes where tutMatch(fortune, '\b(eagle|crow)\b')=1 and src='miscellaneous' and offense <= 1
[2]: Highlighting Results
The SQL statement shown in Figure 1 has a <span class=hi-lite> tag that highlights results. That tag is a local style defined for the page as shown below:
<style> .hi-lite {background-color: #CCCCFF} </style>