/**
reads csv file and creates MySQL db on the local machine.
flags:  -f filename
        -d dimensions
        -s|-i all data treated as strings or integers
        

output:  db table filled with csv data (attrs and measure)
         for count diamonds the measure column is initialized to zero
         
written by Hazel Webb
February 5, 2009        
**/


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


public class DBsqlPreprocessor{
    
    private static int d;
    //number of data dims
    private static int MEASURE_INDEX;
    //location of the measure value (final field in each line of data)
    private static boolean v = false;
    //noisy output
    private static String file;
    //store input file name
    private static boolean integers = false;
    //all data will be treated as integers (except measure it will always be double)
    private static boolean strings = false;
    //all data will be treated as strings (except measure it will always be double)
    private static boolean indexes = false;
    //default:no indexes will be created,  if true every column will get an index

    private static Connection con;
    private static Statement stmt;
    private static Statement countStmt;
    static ResultSet rs;
    //use stack to track attrs to be deleted
    static Stack<Integer> toDelete;
    static Stack<String> dateToDelete;
    static String table;
    private static String type;
    private final static int MAXCHARS = 30;//for size of varchar
    private static String tablename;
    private static String connectString = "jdbc:mysql://localhost/YOUR-DATABASE-NAME";
    private static String user = "XXX";//use your user name
    private static String pwd = "XXX";//your password
    //default connection  can be overridden  by cmd line connection string

    public static void main(String[] args){
	
	DBsqlPreprocessor proc = new DBsqlPreprocessor();
	Hashtable<String,Integer> hashFlags = new Hashtable<String,Integer>();
	//hardcode maps from flag strings to integers
	hashFlags.put("-filename", new Integer(0));
	hashFlags.put("-f", new Integer(0));
	hashFlags.put("-d", new Integer(1));
	hashFlags.put("-i", new Integer(2));
	hashFlags.put("-s", new Integer(3));
	hashFlags.put("-h", new Integer(4));
	hashFlags.put("-v", new Integer(5));
	hashFlags.put("-c", new Integer(6));
	hashFlags.put("-u", new Integer(7));
	hashFlags.put("-p", new Integer(8));
	hashFlags.put("-index", new Integer(9));
	if(args.length < 5){
	    System.out.println("Usage: DBsqlPreprocessor -f filename -d dims -i OR -s [-v verbose -c connectString -u user -p password -index]");
	    System.exit(1);
	}
	int iarg;
	for(int a=0;a<args.length;a++){
	    iarg=4;
	    //map this argument to its int
	    try{
		iarg=(int)hashFlags.get(args[a]);
	    }catch(Exception e){System.err.println("Problem with argument: "+args[a]);e.printStackTrace();System.exit(1);}
	    
	    // run that int through a switch
	    switch(iarg){
	    case 0:
		a++;
		file = args[a];
		break;
	    case 1:
		a++;
		d = Integer.parseInt(args[a]);
		//how many dimensions?
		MEASURE_INDEX = d;
		break;
	    case 2:
		integers = true;
		type = "integer";
		break;
	    case 3:
		strings = true;
		type = "varchar("+MAXCHARS+")";
		break;
	    case 4:
		System.out.println("Usage: DBsqlPreprocessor -f filename -d dims -i (all integer data) OR -s (all string data) [-v verbose]");
		System.exit(0);
		break;
	      case 5:
		  v = true;
		  break;
	    case 6:
		a++;
		connectString = args[a];
		break;
	    case 7:
		a++;
		user = args[a];
		break;
	    case 8:
		a++;
		pwd = args[a];
		break;
	    case 9:
		indexes = true;
	    }
	}

	if(proc.createTable())
	    System.out.println("Table created successfully");
	if(proc.loadTable())
	    System.out.println("Data loaded successfully");
    }



    /**
       createTable()
       connects to diamond database on localhost and creates a table for the csv file
       also creates a second table in which to store a copy of the data - diamond building is destructive
       All attributes are either integers or strings the measure is of type double
    **/
    public static boolean createTable(){
	boolean success = false;
	tablename = file;

	//remove .csv extension from the table name
	if(file.contains("csv"))
	    tablename = file.substring(0,file.length()-4);

	//only use the file name not entire path to file as the table name
	if(file.contains("/"))
	   tablename = tablename.substring(tablename.lastIndexOf('/')+1);
	  if(v) System.out.println(tablename);

	   //connect to db
	try {
	    Class.forName("com.mysql.jdbc.Driver");
	    con = DriverManager.getConnection(connectString,user,pwd);
	    stmt = con.createStatement();
	    
	    //trial run to create a table
	    
	    String dropTable = "drop table if exists "+tablename;
	    String dropTable2 = "drop table if exists " + tablename + "2";
	    String createTable ="create table "+ tablename + "(";
	    String createTable2 = "create table " + tablename+"2(";
	    if(indexes){
		for(int i = 0; i < d; ++i){
		    createTable = createTable +"attr"+i+" " + type +", index(attr"+i+"),  ";
		    createTable2 =  createTable2 +"attr"+i+" " + type +", index(attr"+i+"),  ";
		}
	    }else{
		for(int i = 0; i < d; ++i){
                    createTable = createTable +"attr"+i+" " + type + ",  ";
                    createTable2 =  createTable2 +"attr"+i+" " + type + ", ";
                }
	    }

	    createTable = createTable + "measure double default 0)DATA  DIRECTORY='INSERT YOUR INFORMATION HERE' INDEX DIRECTORY='INSERT YOUR INFORMATION HERE'";//add the measure column
	    createTable2 = createTable2 + "measure double default 0)DATA  DIRECTORY='INSERT YOUR INFORMATION HERE' INDEX DIRECTORY='INSERT YOUR INFORMATION HERE'";//add the measure column

	    //checking sql statement System.out.println(createTable);
	    stmt.executeUpdate(dropTable);
	    stmt.executeUpdate(dropTable2);
	    stmt.executeUpdate(createTable);
	    stmt.executeUpdate(createTable2);
	    //if we made it to the next statement everything is good
	    success = true;
	    con.close();
	    stmt.close();
	}
	catch(ClassNotFoundException e){
	    System.out.println("Couldn't connect");
	    e.printStackTrace();
	}
	catch( SQLException sqle){
	    System.out.println(sqle.getMessage());
	}
	catch(Exception oe){
	    System.out.println("other things went wrong");
	    oe.printStackTrace();
	}
	
	return success;
    }

 /**
       loadTable()
       connects to diamond database on localhost and loads the newly minted table with all the data in
       the csv file.  If there is no measure the value defaults to zero.
    **/
    public static boolean loadTable(){
	boolean success = false;

	//connect to db
	try {
	    Class.forName("com.mysql.jdbc.Driver");
	    con = DriverManager.getConnection("jdbc:mysql://localhost:3306/XXX?user=XXX&password=XXX");
	    stmt = con.createStatement();
	    
	    //trial run to create a table
	    
	    String loadTable = "load data local infile '"+file+"' into table "+tablename+"  fields terminated by \',\'";

	    //checking sql statement 
	    System.out.println(loadTable);

	    stmt.executeUpdate(loadTable);
	    //if we made it to the next statement everything is good
	    success = true;
	    stmt.close();
	    con.close();
	}
	catch(ClassNotFoundException e){
	    System.out.println("Couldn't connect");
	    e.printStackTrace();
	}
	catch( SQLException sqle){
	    System.out.println(sqle.getMessage());
	}
	catch(Exception oe){
	    System.out.println("other things went wrong");
	    oe.printStackTrace();
	}
       
	return success;
    }
}