Finishing the Database

At the end of the Elaboration phase, the database was fairly stable. We added a couple of new fields during the Construction phase and had to make changes to allow single quote characters in the fields. In the following sections we describe how we addressed each of these situations.

Adding New Fields ”Updating the Database Schema

You can add new fields by changing the schema. There is no effect on the user if the user creates a new database after receiving the new schema. But what happens to existing databases that contain valuable data? You don't want to manually copy the data from the old database to the new one. Nor do you want to have to choose between using a new release of the software that supports the new database schema or staying with an older release that supports your existing database. You want the software to update the database when necessary with minimal intervention on your part.

We realized that PSP Tools users would face database update problems as new versions became available. Our early adopters, Gary and Russell, were already using the software. Although we could insist that they use the database utilities to update their databases, we knew we would have to address the database upgrade problem eventually. Gary developed a simple yet effective solution after studying the Cloudscape SQL documentation and prototyping a couple of ideas.

The solution to the problem requires that the database schema has a version. We added the version to the DBInfo table. Every time we change the database, we also do the following: we increase the database version number and we write a database updater class. The database updater class implements the IPSPDatabaseUpdater interface. The interface, shown in Listing 9.9, requires two methods , initialize() and doUpdateDabase() . The initialize() method associates an open database with the updater. The doUpdateDatabase() method executes the appropriate SQL commands to update the database schema.

Listing 9.9 Interface for database version updaters

  public interface  IPSPDatabaseUpdater {      /**       * Update the database and return true if everything went       * okay. Return false otherwise.       */  public boolean  doUpdateDatabase();      /**       * Initialize the updater with the database.       */  public void  initialize(PSPDatabase db); } 

Now let's look at an updater class. Listing 9.10 shows the updater class for updating a database from version 1 to version 2. The required change is to add a timeID field to the table containing detailed time entries. The initialize() method is trivial. We can add the timeID field with the SQL statement:

 ALTER TABLE C16A ADD COLUMN timeID INTEGER DEFAULT AUTOINCREMENT INITIAL 1 INCREMENT 1 

The alter command adds the column and inserts appropriate values into the added fields for all records in the database. Using this technique, we avoid having to read and update each record after altering the schema. The only thing the doUpdateDatabase() method must do is execute the SQL command.

Listing 9.10 Sample database updater

  public void  initialize(PSPDatabase db) {      myDatabase = db; }  public boolean  doUpdateDatabase() {      String sqlUpdate =             "ALTER TABLE C16A ADD COLUMN timeID INTEGER" +             " DEFAULT AUTOINCREMENT INITIAL 1 INCREMENT 1";      Statement stmt =  null  ;  try  {             stmt = myDatabase.getNewStatement();  int  rows = myDatabase.doUpdate(stmt, sqlUpdate);             stmt.close();      }  catch  (PSPDatabaseException e) {             JOptionPane.showMessageDialog(  null  ,             "Unable to update the database.\n" +             "SQL Error:\n    " +             e.getSQLExceptionMessage(),             "Database Update Error",             JOptionPane.ERROR_MESSAGE);  return false  ;      }  catch  (SQLException e) {             JOptionPane.showMessageDialog(  null  ,               "Warning: problem closing the statement\n" +               e.getMessage(),               "Database Update Error",               JOptionPane.WARNING_MESSAGE);  return false  ;      }  return true  ; } 

Each database updater is responsible for updating a database from one version to the next version. If, for some reason, your database is more than one version behind the current version, PSP Tools invokes a series of database updaters, each updating the database one version.

We check for an out-of-date database when we initialize a database in the PSPDatabase.java module after the connection is made, as shown in Listing 9.11.

Listing 9.11 Checking database version during initialization

  public void  initializeDB()  throws  PSPDatabaseException {  try  {          // Get the database information          dbInfo = dbAccessor.getDBInfo();  if  (checkUpgrade() ==  false  ) {               close();  throw new  PSPDatabaseException(                   "Cannot work on the database until an "  +  "upgrade is performed");           }           // Get the phases (done for side effects only)           phaseAccessor.getPhases();      }  catch  (PSPDatabaseException e) {  throw  e;      } } 

The checkUpgrade() method determines if the database is at the current version and, if not, updates it. Before updating the database, the user is asked to confirm that the update should proceed. This provides an opportunity to back up the database before upgrading it. The checkUpgrade() method is shown in Listing 9.12.

Listing 9.12 Method to determine whether the database needs upgrading

  private boolean  checkUpgrade() {  if  (dbInfo.getDBVersion() == DB_VERSION) {  return true  ;      }      PSPDBUpdateManager updateMgr =  new  PSPDBUpdateManager(  this  , dbInfo.getDBVersion(),                DB_VERSION);  boolean  result = updateMgr.doUpdateDatabase();  if  (!result) {  return false  ;      }      // Update the dbInfo record.  try  {           dbAccessor.updateDBInfo("dbVersion", "" +                DB_VERSION);      }  catch  (PSPDatabaseException e) {           JOptionPane.showMessageDialog(  null  ,                "Unable to update the database info " +                "after updating.\n" +                "SQL Error:\n    " +                e.getSQLExceptionMessage(),                "Database Update Error",                JOptionPane.ERROR_MESSAGE);  return false  ;      }  return true  ; } 

After we determine that an update is required, we create an instance of a PSPDBUpdateManager object. The PSPDBUpdateManager performs the individual updates in the proper sequence. When the object is created, we call the doUpdateDatabase() method. After having the user confirm that the update should proceed, the code in Listing 9.13 executes. ^[4]

^[4] We replaced some error-handling code with comments in this code example to make it easier to understand the core logic in the loop.

Listing 9.13 Database updating code in PSPDBUpdateManager.doUpdateDatabase()

 // Now, iterate through as many updaters as needed.  int  i = oldVersion; Class updaterClass =  null  ;  while  (i < newVersion) {  int  j = i+1;      // Get the appropriate updater class      String className =           "com.skunkworks.psp.database.PSPDBUpdate" +           i + "to" + j;  try  {           updaterClass = Class.forName(className);      }  catch  (Exception e) {           JOptionPane.showMessageDialog(  null  ,           "Unable to instantiate the class:\n" +           className +           "\nCannot update the database.\n\n" +           "Error Message:\n    " + e.getMessage(),           "Database Update Error",           JOptionPane.ERROR_MESSAGE);  return false  ;      }      // Now create an instance of it      IPSPDatabaseUpdater updater =  null  ;  try  {           updater =      (IPSPDatabaseUpdater)updaterClass.newInstance();      }  catch  (Exception e) {           JOptionPane.showMessageDialog(  null  ,           "Unable to create an instance of:\n" +           className +           "\nCannot update the database.\n\n" +           "Error Message:\n    " + e.getMessage(),           "Database Update Error",           JOptionPane.ERROR_MESSAGE);  return false  ;      }      // Now do the update      updater.initialize(myDatabase);  if  (!updater.doUpdateDatabase()) {  return false  ;      }      i++; }  return true;  

The main loop executes once for each update. Given the class name, it uses a Java feature to dynamically create an instance of a class. We create the class name based on the from-version and the to-version. For example, to update the database from version 1 to version 2, the variable className is assigned the value com.skunkworks.psp.database.PSPDBUpdate1to2 . Similarly, to upgrade the database from version 1 to version 3, we call the doUpdateDatabase() method in a PSPDBUpdate1to2 object first, then in a PSPDBUpdate2to3 object.

Allowing Single Quotes in Fields

The SQL language uses single quotes to enclose strings. Some of the fields in the database are free-form and can include single quotes. For example, the database has a description. The first time we implemented the code to enter the database description, Gary typed Gary's database . SQL interpreted the apostrophe as the string terminator and deemed the SQL statement to be malformed .

The solution is to ensure that every embedded single quote is replaced by two single quotes (not the double quote character), forcing the single quote value into the field. We implemented the solution by creating a utility class, PSPSQLUtilities.java . This class contains two methods, as shown in Listing 9.14. One method converts every single quote character in the argument while the other method only converts embedded quotes. The appropriate method is called whenever we are about to perform an SQL operation. This is shown in Listing 9.15. Notice how we call the static method PSPSQLUtilities.prepareSQLString() when we insert the value of the login name into the user selection statement. This code is taken from PSPUserAccessor.java .

Listing 9.14 Utility methods to prepare SQL strings

 /**  * Prepare a data string for inclusion in an SQL statement.  * This routine replaces any single quote character by  * two single quote characters.  */  public static  String prepareSQLString(String s) {      StringBuffer sb =  new  StringBuffer();  for  (  int  i = 0; i < s.length(); i++) {  char  c = s.charAt(i);  if  (c == '\'') {                sb.append(c);     // append the single quote           }           sb.append(c);      }  return  sb.toString(); } 

Listing 9.15 Using the SQL utility methods

  public  PSPUser getUser(String loginName)  throws  PSPDatabaseException      {           String sqlGetUser =                "SELECT * FROM Users WHERE loginName = '" +                PSPSQLUtilities.prepareSQLString(loginName) + "'";  try  {                Statement stmt = myDatabase.getNewStatement();                ResultSet rs = myDatabase.doQuery(stmt, sqlGetUser);  if  (!rs.next()) {  return null  ;     // not found                }  return  userFromDB(rs);           }  catch  (PSPDatabaseException e) {  throw  e;           }  catch  (SQLException se) {  throw new  PSPDatabaseException(                      "SQL error getting a task", se);           }      }