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

public class TestTables extends TestCase {

    Connection db;
	Statement st;	// Our statement to run queries with

    MetaTable[] tNames;

    public void testTables() {
       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("JDBC Driver 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 database.");

       }

           //List tables
      try {
		 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;

         //Get Table description parameters from Config file
         String[] metTabName = config.getConfigParam("TableName"); 
         String[] metTabDesc = config.getConfigParam("TableDescription"); 
            
         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])) {
                           count++;
                       }
                   }
                }
                if (count != includeTables.length) {
                    throw new Exception ("Error: IncludeTable in Configure.xml does not exist in database");
                }
                tNames = new MetaTable[includeTables.length];
                for (int i=0;i<includeTables.length;i++) {
                    MetaTable table = new MetaTable();
                    table.setName(includeTables[i]);
                    // Find row count
                    int rowCount = 0;
                    st = db.createStatement
                             (ResultSet.TYPE_SCROLL_SENSITIVE,
                                  ResultSet.CONCUR_READ_ONLY);
                    String sql2 = "SELECT COUNT(*) FROM " + includeTables[i];  
                    ResultSet rs2 = st.executeQuery(sql2);
		            if (rs2 != null) {
                       rs2.next();
                       rowCount = rs2.getInt(1);
                    } 
                    table.setRows(rowCount);
                    for (int j=0;j<metTabName.length;j++) {
                        if (metTabName[j].equals(includeTables[i])) {
                            table.setDescription(metTabDesc[j]);
                         }
                    }
                        tNames[i] = table;
                }

            } else { // excludeTables >= 0
                //Find out how many tables we are returning
                int count = 0;
                while(rs.next()) {
                    count++;
                }
                if(existExclude) {
                    tNames = new MetaTable[count - excludeTables.length];
                } else {
                    tNames =  new MetaTable[count];
                }
                rs = dbmd.getTables(null, "public", null, null);	
                int num = 0;
                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) {
                        MetaTable table = new MetaTable();
                        table.setName(temp);
                        // Find row count
                        int rowCount = 0;
                        st = db.createStatement
                              (ResultSet.TYPE_SCROLL_SENSITIVE,
                                  ResultSet.CONCUR_READ_ONLY);
                        String sql2 = "SELECT COUNT(*) FROM " + temp;  
                        ResultSet rs2 = st.executeQuery(sql2);
		                if (rs2 != null) {
                           rs2.next();
                           rowCount = rs2.getInt(1);
                        } 
                        table.setRows(rowCount);

                        for (int j=0;j<metTabName.length;j++) {
                            if (metTabName[j].equals(temp)) {
                                table.setDescription(metTabDesc[j]);
                             }
                        }
                        tNames[num] = table;
                        num++;
                    }
                } 
            }
            System.out.println("Successfully found these Tables: " );
            for (int i=0; i<tNames.length; i++) {
                System.out.println("Name: " + tNames[i].getName() + "\n");
                System.out.println("Description: " + tNames[i].getDescription() + "\n");
                System.out.println("Number of Rows: " + tNames[i].getRows() + "\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.");
       }

    }
}