Java Web Programming with Eclipse contents
Last modified February 15, 2011 05:08 am
In this section, you will build the database needed for the wiki application. However, before starting on this task, you need to first create a new Eclipse project that will hold the database scripts and the other source code that you will build in subsequent sections of this chapter.
In Eclipse, create a new project called wiki using a procedure similar to the one you used to create the website and publisher projects. Pay particular attention to configuring logging. You will be creating java classes with package names that start with wiki. In order that debug-level log messages are written into the log file, the last line in the log4j.properties file should include a reference to wiki rather than publisher, as given in the following line
In this section, you will create a database for the wiki application and set up an ant build file to run database scripts that drop tables, create tables and insert test data. Recall that with these database scripts, it is easy to modify the database as your web application evolves.
Use the MySQL command line client to create a database with name wiki. The following two commands show how to do this.
mysql> create database wiki; mysql> grant all privileges on wiki.* to 'wiki'@'localhost' identified by '1234';
Similar to the publisher project, you should create a folder called database to store database creation scripts and the ant build file to run them. To begin, copy the database folder in the publisher project into the wiki project. In the subsequent steps, we will modify these copied files to work correctly for the wiki application.
In the database folder, modify build.xml so that you operate on the wiki database rather than the publisher database. To do this, you only need to modify the property element that sets the value of the mysql.params attribute. The property element should appear as follows.
<property name="mysql.params" value="-u wiki -p1234 -D wiki" />
Replace the contents of createdb.sql with the contents of the following listing.
create table page ( name varchar(255) primary key, content text not null, published int default 0, published_id varchar(255) );
The name column will hold the name of the wiki page. We use the wiki page name as the primary key rather than using a separate integer id as we did in the publisher application. Using page name as primary key works, because page names need to be unique, which is the main requirement for data to serve as a primary key. The name column has datatype varchar(255), which means that this column will hold variable lengths character strings that can not exceed a length of 255. Varchar columns can be used as primary keys. The content column will contain the character data that comprises the wiki pages; it has datatype text, which allows for the storage of a large number of characters. Text columns can not be used as primary keys. The published column is an integer that will hold a zero or a one, where zero indicates that the page has not been added to the publisher application and one indicates that it has. The published column defaults to 0, meaning that if a value for this column is not specified when a new row is inserted into the wiki page table, then the published column will default to 0. The published_id column holds the id of the news item in the publisher application that references the wiki page represented by this row. If a given page is not published, the state of the published_id column has no meaning to the application.
Modify insertdb.sql, so that you create at least two sample pages. You only need to specify values for the name and content columns because the published column defaults to 0, which represents false, and the value of published_id will not be used when published is false. You should create a row to represent the page whose name is the empty string, because this will be the page with no name and can serve as the start page in the wiki system. Use the contents of the following listing for insert_data.sql.
insert into page (name, content) values ('', '<h1>Nameless Page</h1><a href="hello">hello page</a><br /><a href="nowhere">nowhere page</a>'); insert into page (name, content) values ('hello', '<h1>Hello Page</h1><a href=".">nameless page</a>');
Notice that in the above 2 insert statements we store HTML content directly in the database. Normally in a wiki application users do not enter HTML directly into the application. Instead, they enter character data that is annotated with wiki markup conventions. An example of wiki markup is to use apostrophes to italicize words.
The first of the above insert statements creates a page with name equal to the empty string. We refer to this page as the nameless page, or the root page. Inside the nameless page there are 2 links. The first link goes to a page with name hello, which is a nonempty page created by the second insert command. The second link in the nameless page goes to a page called nowhere, which does not exist in the database. When the user follows the link to the nowhere page, he or she will see a blank page that can be modified.
Finally, modify cleandb.sql with the contents of the following listing.
drop table if exists page;
The above statement is not generic SQL syntax. The if exists [page] part of the statement is supported by MySQL, but is not necessarily support by other database systems. This MySQL extension to the SQL definition is convenient because one can issue a drop table command for a table that may not exist. Using this exension, however, breaks the portability of our application. If we want to use another database system, we would most likely need to modify this script.
Run the all target of the build file to create and populate the page table of the wiki database.