/* 
 * Copyright (c) 2001, Xiaoping Jia.  
 * All Rights Reserved. 
 */

package db;

import java.sql.*;
import java.io.*;
import java.util.*;

/**
 * This is an application that creates the database used by the Oscar servlet. 
 * It must be run before the Oscar servlet is started for the first time. 
 *
 * @version 1.1 2001/04/29 
 * @since 1.0
 * @author Xiaoping Jia
 * @see db.Oscar
 * @see db.Oscar2
 *
 */ 
public class OscarDB {

  public static final String dbName = "Oscar"; 
  public static final String bestPictureTableName = "BestPictures"; 
  public static final String favoratePictureTableName = "FavoratePictures"; 

  public static void main(String args[]) {

    DBConnector db = DBConnector.getInstance(); 
    try {
      db.startup(dbName); 
    } catch(ClassNotFoundException e) {
      	System.out.println(e);
      	System.exit(1);      
    } catch (SQLException se) { 
    	System.out.println(se);
      	System.exit(1);      
    }

    try { 
      String dropString = "DROP TABLE " + bestPictureTableName; 
      db.executeUpdate(dropString);
      dropString = "DROP TABLE " + favoratePictureTableName; 
      db.executeUpdate(dropString);
    } catch (SQLException se) {} 

    try { 
      String createString =
	"CREATE TABLE " + bestPictureTableName + 
	" (id VARCHAR(32) NOT NULL PRIMARY KEY," +  
	" title VARCHAR(128), count1 INTEGER)";
      db.executeUpdate(createString);

      System.out.println("Created table: " + bestPictureTableName); 

      createString =
	"CREATE TABLE " + favoratePictureTableName + 
	" (id VARCHAR(32) NOT NULL PRIMARY KEY," + 
	" title VARCHAR(128), count1st INTEGER, count2nd INTEGER, count3rd INTEGER)";
      db.executeUpdate(createString);

      System.out.println("Created table: " + favoratePictureTableName); 

      String insertString =
	"INSERT INTO " + bestPictureTableName + 
	" VALUES ('Chocolat', 'Chocolat', 0)";
      db.executeUpdate(insertString);
      insertString =
	"INSERT INTO " + bestPictureTableName + 
	" VALUES ('Dragon', 'Crouching Tiger, Hidden Dragon', 0)";
      db.executeUpdate(insertString);
      insertString =
	"INSERT INTO " + bestPictureTableName + 
	" VALUES ('Brockovich', 'Erin Brockovich', 0)";
      db.executeUpdate(insertString);
      insertString =
	"INSERT INTO " + bestPictureTableName + 
	" VALUES ('Gladiator', 'Gladiator', 0)";
      db.executeUpdate(insertString);
      insertString =
	"INSERT INTO " + bestPictureTableName + 
	" VALUES ('Traffic', 'Traffic', 0)";
      db.executeUpdate(insertString);
    } catch (SQLException se) {
      se.printStackTrace(); 
    }

    db.shutdown(); 
  }

}