package test;
import junit.framework.*;
import java.sql.*;
import net.ivoa.SkyNode.*;

public class TestColumns extends TestCase {

 public void testColumns(){
      String testTable = "";
      Connection db = null;
      MetaColumn[] cNames = null;


       ncsa.nvo.skynode.Configure config = 
            new ncsa.nvo.skynode.Configure("/Configure.xml");
       String dbDriver = config.getConfigParam("JDBCDriver")[0];
       // Get the db parameters
       String jdbcUrl = 
            config.getConfigParam("JDBCurl")[0];
       String userName = config.getConfigParam("Username")[0];
       String passWord = config.getConfigParam("Password")[0];

       try {
 	       Class.forName(dbDriver);
       } 
           catch (ClassNotFoundException e)
       { 
            System.out.println( "JDBC Driver class not found: " + 
                                        dbDriver + "\n" + 
                                "Check the JDBCDriver tag in Configure.xml" );
           Assert.fail("Driver class not found");
       }
        try {
            db = DriverManager.getConnection (jdbcUrl, userName, passWord);

       } 
           catch (java.sql.SQLException e)
       { 
            System.out.println( "Cannot connect to the database: " + 
                                        jdbcUrl + "\n" + 
                                "Check the JDBCurl, Username and Password tags in Configure.xml" );

           Assert.fail("Can't connect to the database");
       }

       try {
            //Now get columns for a table; try the first one from a database
            // query for tables
		    DatabaseMetaData dbmd = db.getMetaData();

            ResultSet rs = dbmd.getTables(null, null, null, null);	

         boolean existExclude = true;
         boolean existInclude = true;
         String[] excludeTables = config.getConfigParam("ExcludeTable"); 
         String[] includeTables = config.getConfigParam("IncludeTable"); 
         if (excludeTables.length == 1 && excludeTables[0].equals(""))
             existExclude = false;
         if (includeTables.length == 1 && includeTables[0].equals(""))
             existInclude = false;
         if(existInclude) {
             //Find out how many tables we are returning
             // Should match number of includeTables
             int count = 0;
                while(rs.next()) {
                   String temp = "";
                   for (int i=0;i<includeTables.length;i++) {
                       temp = rs.getString(3);
                       if (temp.equals(includeTables[i])) {
                           testTable = temp;
                           break;
                       }
                   }
                }
            } else { // excludeTables >= 0
               test: while (rs.next()) {
                    boolean includeTable = true;
                    String temp = rs.getString(3);
                    for (int i=0;i<excludeTables.length;i++) {
                        if (temp.equals(excludeTables[i])) {
                            includeTable=false;
                        }
                        if(includeTable) {
                            testTable = temp;
                            break test;
                        }
                    }
                } 
            }

            //Now to process this list of columns
            //using the appropriate params from the configuration
            //Assumptions: TableInclude entries but no ColumnExclude entries 
            //implies *only* list the ColumnInclude tables (if they exist)
            //ColumnExclude entries without ColumnInclude entries implies
            //Exclude those from the list, list all others.
            //Both ColumnInclude and ColumnExclude entries -- ignore
            //ColumnExclude
            //and only list those in ColumnInclude
            

            existExclude = true;
            existInclude = true;

            String[] excludeColumns= 
            config.getConfigParam("ExcludeColumn"); 
            String[] includeColumns = 
            config.getConfigParam("IncludeColumn"); 
            if (excludeColumns.length == 1 && excludeColumns[0].equals(""))
                existExclude = false;
            if (includeColumns.length == 1 && includeColumns[0].equals(""))
                existInclude = false;

            //Get Column Descriptions from Config file
            String[] metColID = config.getConfigParam("ColumnID"); 
            String[] metColDesc = config.getConfigParam("ColumnDescription"); 
            String[] metColUCD = config.getConfigParam("ColumnUCD"); 
            String[] metColUnit = config.getConfigParam("ColumnUnit"); 

            if(existInclude) {
                rs = dbmd.getColumns(null, null, testTable, null);	
                //Find out how many columns we are returning
                // Should match number of includeColumns
                int count = 0;
                while(rs.next()) {
                   String temp = "";
                   for (int i=0;i<includeColumns.length;i++) {
                       temp = testTable + "." + rs.getString(4);
                       if (temp.equals(includeColumns[i])) {
                           count++;
                       }
                   }
                }
                if (count != includeColumns.length) {
                    throw new Exception ("Error: IncludeColumns in Configure.xml does not exist in database");
                }
                cNames = new MetaColumn[includeColumns.length];
                for (int i=0;i<includeColumns.length;i++) {
                    String temp = includeColumns[i];
                    MetaColumn column = new MetaColumn();
			        column.setName(temp);
                    for (int j=0;j<metColID.length;j++) {
                        if (metColID[j].equals(temp)) {
                            column.setDescription(metColDesc[j]);
                            column.setUCD(metColUCD[j]);
                            column.setUnit(metColUnit[j]);
                        }
                    }
                    cNames[i] = column;
                }

            } else { // excludeColumns >= 0
                rs = dbmd.getColumns(null, null, testTable, null);	
                //Find out how many columns we are returning
                int count = 0;
                while(rs.next()) {
                    count++;
                }
                int numExcludes = 0;
                for (int i=0;i<excludeColumns.length;i++) {
                    if (excludeColumns[i].startsWith(testTable)) {
                        numExcludes++;
                    }
                }
                if(existExclude) {
                    cNames = new MetaColumn[count - numExcludes];
                } else {
                    cNames =  new MetaColumn[count];
                }
                rs = dbmd.getColumns(null, null, testTable, null);	
                int num = 0;
                while (rs.next()) {
                    boolean includeColumn = true;
                    String temp = testTable + "." + rs.getString(4);
                    for (int i=0;i<excludeColumns.length;i++) {
                        if (temp.equals(excludeColumns[i])) {
                           includeColumn=false;
                        }
                    }
                    if(includeColumn) {
                        String temp2 = rs.getString(4);
                        MetaColumn column = new MetaColumn();
			            column.setName(temp2);
                        for (int j=0;j<metColID.length;j++) {
                            if (metColID[j].equals(temp)) {
                                column.setDescription(metColDesc[j]);
                                column.setUCD(metColUCD[j]);
                                column.setUnit(metColUnit[j]);
                            }
                        }
                        cNames[num] = column;
                        num++;
                    }
                } 
            }

                System.out.println("Successfully found these Columns for Table " + testTable +": " );
                for (int i=0; i<cNames.length; i++) {
                   System.out.println("Name: " + cNames[i].getName() + "\n");
                   System.out.println("Description: " + cNames[i].getDescription() + "\n");
                   System.out.println("UCD: " + cNames[i].getUCD()+ "\n");
                   System.out.println("Unit: " + cNames[i].getUnit()+ "\n");
                }

       } 
           catch (java.sql.SQLException e)
       { 
            System.out.println( "Error in querying database for table information");
            System.out.println( "Although a connection was successful to");
            System.out.println( "the database" + e);
            Assert.fail("Error querying database");
       }
           catch (Exception e)
       { 
            System.out.println( "Error in Configuration:" + e);
            Assert.fail("Error in configuration");
       }
    } 
}