package jdbc;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.SQLException;
import java.util.Properties;

public class CreateDatabase
{
    /**
     *  Main entry point
     *  @param args command line arguments:
     *  <br>
     *  The name of a file containing the names of tables
     *  to be created, one table name per line.
     *  Each table name must have an associated <tablename>.dat
     *  file to define the fields and data for the table.
     */
    public static void main(String[] args)
    {
        Connection con = null;
        Statement stmt = null;
        try
        {
            con = getConnection();
            stmt = con.createStatement();
            
            String tableList = "";
            if (args.length > 0)
                tableList = args[0];
            else
            {
                System.out.println(
                       "Usage: CreateDatabase <table-list-file>");
                System.exit(0);
            }
            
            // Create the tables listed in the tableList file
            createTables(tableList, stmt);
        }
        catch (SQLException ex)
        {
            dumpSQLException(ex);
        }
        catch (IOException ex)
        {
            dumpIOException(ex);
        }
        finally
        {
            try
            {
                if (stmt != null)
                    stmt.close();
                if (con != null)
                    con.close();
            }
            catch (SQLException ex)
            {   /* Do nothing */ }
        }
    }
    
    /**
     *  Gets a connection to the appropriate database, based
     *  on the contents of a properties file.
     */
    public static Connection getConnection()
        throws SQLException, IOException
    {
        Properties props = new Properties();
        File propsFile = new File("jdbc", "CreateDatabase.properties");
        props.load( new FileInputStream(propsFile) );
        
        String drivers = props.getProperty("jdbc.drivers");
        if (drivers != null)
        {
            // Set the System property "jdbc.drivers"
            System.getProperties().put("jdbc.drivers", drivers);
        }
        String url = props.getProperty("jdbc.url");
        String username = props.getProperty("jdbc.username");
        String password = props.getProperty("jdbc.password");
        
        return DriverManager.getConnection(url, username, password);
    }
    
    /**
     *  Creates the tables listed in the tableList file.
     *  @param tableList the filename containing the table list
     *  @param stmt the Statement to use
     */
    public static void createTables(String tableList,
                                    Statement stmt)
        throws IOException                                   
    {
        BufferedReader in = null;
        try
        {
            in = new BufferedReader(
                       new FileReader(
                            new File("jdbc", tableList) ) );

            // Each line contains the name of a table to create
            String tableName = null;
            while (true)
            {
                tableName = in.readLine();
                if (tableName == null)
                    break;
                tableName = tableName.trim();
                try
                {
                    createTable(tableName, stmt);
                    showTable(tableName, stmt);
                }
                catch (Exception ex)
                {
                    ex.printStackTrace();
                    // Ignore any exceptions, so we can continue
                    // on to the next table
                }
            }
        }
        finally
        {
            if (in != null)
                in.close();
        }
    }
    
    /**
     *  Creates the specified table.
     *  @param tableName the name of the table to create.
     *  (There must be a file with the name tableName.dat
     *  that supplies information about the columns and data.)
     *  @param stmt the Statement to use.
     */
    public static void createTable(String tableName,
                                   Statement stmt)
        throws SQLException, IOException
    {
        BufferedReader in = null;
        try
        {
            in = new BufferedReader(
                       new FileReader(
                            new File("jdbc", tableName + ".dat") ) );

            // First, drop the table in case it already exists
            String command = "DROP TABLE " + tableName;
            System.out.println(
                 "Attempting to drop table " + tableName + "...");
            try
            {
                stmt.executeUpdate(command);
            }
            catch (SQLException ex)
            {
                // Ignore; presumably the table doesn't exist
            }
            
            String line = in.readLine();
            
            command = "CREATE TABLE " + tableName +
                            "(" + line + ")";
        
            System.out.println("Creating table " + tableName + "...");
            System.out.println("Statement:\n" + command);
            
            stmt.executeUpdate(command);
            
            System.out.println("Populating table " + tableName + "...");
            while ( (line = in.readLine()) != null)
            {
                command = "INSERT INTO " + tableName +
                          " VALUES(" + line + ")";
                stmt.executeUpdate(command);
            }
        }
        catch (SQLException ex)
        {
            dumpSQLException(ex);
        }
            
        finally
        {
            if (in != null)
                in.close();
        }
    }
    
    /**
     *  Displays the contents of the table.
     *  @param tableName the name of the table
     *  @param stmt the Statement to use
     */
    public static void showTable(String tableName, 
                                 Statement stmt)
        throws SQLException
    {
        String query = "SELECT * FROM " + tableName;
        ResultSet rs = null;
        try
        {
            rs = stmt.executeQuery(query);
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            while (rs.next())
            {
                for (int i = 1; i <= columnCount; i++)
                {
                    if (i > 1)
                        System.out.print(", ");
                    System.out.print(rs.getString(i));
                }
                System.out.println();
            }
        }
        finally
        {
            if (rs != null)
                rs.close();
        }
    }

    private static void dumpSQLException(SQLException ex)
    {
        System.out.println("SQLException:");
        while (ex != null)
        {
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("Message:  " + ex.getMessage());
            System.out.println("Vendor:   " + ex.getErrorCode());
            ex = ex.getNextException();
            System.out.println();
        }
    }
    
    private static void dumpIOException(IOException ex)
    {
        System.out.println("IOException: " + ex);
        ex.printStackTrace();
    }
}