In this section we will create 3 scripts to manipulate the publisher database.
The first script createdb.sql contains the SQL commands to create the two ta-bles used by the application: news item and sequence. The second script in-sertdb.sql contains the SQL commands to insert test data into the news item table. The third script cleandb.sql contains the SQL commands to delete all data from the publisher, including the table definitions.
It is possible to run these scripts from the command line interface of the operating system. However, it is also convenient to run them from within Eclipse. For this purpose, we create an ant build script that lets us run these scripts from within Eclipse. Ant is a widely used command-line based build system for Java projects, so it’s useful to become familar with it. We will also use Ant in later chapters to perform other tasks. Ant is built into Eclipse, so there is no need to install anything in order to use it.
Before running these scripts, we need to first create a mysql database called publisher. You should do that now, using the database root account as follows. (Note that the second command is given in multi-line format.)
mysql> create database publisher;
mysql> grant all privileges on publisher.* to publisher@localhost identified by ’publisher’;
The first command given above creates an empty database called pub-lisher. The second command creates a database user called publisher with password publisher (the argument of ”identified by”) and with privileges to perform all possible manipulations of the database.
Inside Eclipse, create a folder in the publisher project called database and within that folder create a file called createdb.sql with the following contents. (If an external window pops up when you try this, then close this window and right click on cleandb.sql and select Open with text editor.)
create table news_item (
id integer primary key, title text not null, url text not null );
create table sequence (
next_value integer );
insert into sequence value (1000);
The first command in the above file is the SQL create table command.
The command creates a table (in the currently selected database) called news item with 3 fields. The first field is called id and it has datatype integer (an 8 byte signed integer) and it is declared as a primary key. A primary key is a field (or composite of several fields) that contains a unique value that can be used to identify the data in a single row in the table.
The database enforces the uniqueness of this field, which means that if you try to insert a new row with a primary key equal to the primary key of an existing row, the insert operation will fail. The publisher application that we will subsequently discuss will follow the convention of making all primary keys integers (8-byte longs in Java) and they will all have the name id in which ever table they appear. The values for the primary keys will have no
”business information.” Such keys are refered to as surrogate primary keys or pseudo primary keys rather than natural primary keys, which do contain business information. After examining the publisher application, we will look at an application that implements a wiki. In the wiki application we will use natural primary keys.
Each row in the the news item table will contain information about a single news item in a news feed. The id field is used to uniquely identify the news item.
In addition to defining a primary key called id, the first command above also creates a field called title, which will contain the titles of news items, and a field called url, which will contain the link of the news article to which the news item refers. Both title and url are declared as text, which means
99
they contain character data with a very large size limitation. These fields are also declared as not null, which means these fields must contain string data and can not be set to null. Note that setting a not null character field to an empty is allowed because this is different from setting the field to null.
The second command in the above file is also a create table command.
In this case, we create a table called sequence that will be used to store a single value: the next value to be used for a new primary key. The third command is an SQL insert command that inserts a row into the sequence table. By inserting the number 1000 in this table, we mean for our primary keys to begin from 1000. Every time we use a primary key, we will increment this value.
Note that MySQL includes an auto increment setting for integer primary keys and that many programmers use this as a way to automatically generate unique primary keys. It has the advantage of eliminating the need for a separate sequence table to store the next primary key value. However, this functionality is not part of the SQL standard and is not provided in all databases. For example, PostgreSQL does not provide an auto incrementing feature. For this reason, we use a separate sequence table, which is supported on all relational databases. The benefit is that we can replace the database with any other SQL compliant database without modifying our code. This type of portability is important when a software system may be used by multiple clients who prefer to use a database of their choise rather than the particular database the application is written for.
Create another script called insertdb.sql with the following contents.
insert into news_item (id, title, url) values (1, ’CNN’, ’http://www.cnn.com/’);
insert into news_item (id, title, url) values (2, ’FOX News’, ’http://www.foxnews.com/’);
The purpose of insertdb.sql is to insert test data into the database. The above script insert two rows into the news time table. As you can see from the syntax, the first part of the insert command specifies in a parenthetical list the name and order of the fields (or columns) to be set, and the second part of the command provides in a parenthetical list the values to be placed in those fields.
Create another script called cleandb.sql with the following contents.
drop table if exists news_item;
drop table if exists sequence;
<?xml version="1.0" encoding="UTF-8"?>
<project name="publisher" default="all" basedir=".">
<property name="mysql.params" value="-u publisher -ppublisher -D publisher" />
<target name="all" depends="cleandb, createdb, insertdb"></target>
<target name="cleandb">
Figure 9.4: Ant Build File
The purpose of the cleandb.sql script is to delete all data in the database including the table definitions.
As we develop the application incrementally, we will need to make fre-quent changes to the structure of the database. Each time we make a change to the structure of the database, we first run cleandb to delete everything, followed by createdb to create the new structure, followed by insertdb to insert new test data.