// Program til at slette og genoprette tabeller på database-serveren

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

public class UtilDB {
  public static void main(String[] args) throws SQLException {
    Connection   db;            // The connection to the database
    Statement    st;            // A statement to run queries with
    
    String url = "jdbc:postgresql://ellemose.dina.kvl.dk/gp";
    String usr = "gp";
    String pwd = "e2000";
    
    try { Class.forName("postgresql.Driver"); } 
    catch (ClassNotFoundException e) 
      { System.out.println("Cannot find the Postgresql driver"); }
    
    // Connect to database
    db = DriverManager.getConnection(url, usr, pwd); 
    st = db.createStatement();

    dropTables(st);
    createTables(st);
    insertData(st);

    st.close();
    db.close();
  }

  private static void insertData(Statement st) {
    String[] insert = {
      "INSERT INTO student VALUES ('Olesen', 'Peter', 'L2143');",
      "INSERT INTO student VALUES ('Hansen', 'Erika', 'Jø0007');",
      "INSERT INTO student VALUES ('Funder', 'Ulrik', 'Hg0014');",

      "INSERT INTO kursus VALUES (10181, 'Databehandling');",
      "INSERT INTO kursus VALUES (45621, 'Landbrugszoologi');",
      "INSERT INTO kursus VALUES (15351, 'Miljømodeller');",
      "INSERT INTO kursus VALUES (15311, 'Matematisk Grundkursus');",

      "INSERT INTO eksamen VALUES ('L2143', 010181, 8);",
      "INSERT INTO eksamen VALUES ('L2143', 045621, 9);",
      "INSERT INTO eksamen VALUES ('Jø0007', 010181, 11);",
      "INSERT INTO eksamen VALUES ('Jø0007', 015311, 8);",
      "INSERT INTO eksamen VALUES ('L2143', 015311, 10);",
      "INSERT INTO eksamen VALUES ('Hg0014', 015311, 7);"};
    execUpdates(st, insert);
  }

  private static void createTables(Statement st) {
    String[] create = {"CREATE TABLE student (efternavn TEXT, fornavn TEXT, studienummer TEXT);",
		       "CREATE TABLE kursus (kursusnummer INT, kursusnavn TEXT);",
		       "CREATE TABLE eksamen (studienummer TEXT, kursusnummer INT, karakter INT);"};
    execUpdates(st, create);
  }

  private static void dropTables(Statement st) {
    String[] drop = {"DROP TABLE student;",
		     "DROP TABLE kursus;",
		     "DROP TABLE eksamen;"};
    execUpdates(st, drop);
  }

  private static void execUpdates(Statement st, String[] sqls) {
    try {
      for(int i=0; i<sqls.length; i=i+1) {
	boolean resultset = st.execute(sqls[i]);
      }
    } catch (SQLException e) 
      { System.out.println("Database error: " + e.toString()); }
  }
}