/*
 * Copyright 2002 Sun Microsystems, Inc. All rights reserved.
 * SUN PROPRIETARY/CONFIDENTIAL. Use is subject to license terms.
 */

/*
 * @(#)dbSchema.java	1.16 02/01/12
 */
package com.sun.cts.tests.jdbc.ee.common;

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

import com.sun.cts.util.*;
import com.sun.cts.harness.*;

import java.rmi.RemoteException;


/**
 * The dbSchema class creates the database and tables using 
 * Sun's J2EE Reference Implementation.
 * @author  
 * @version 1.7, 06/16/99
 */

public class dbSchema extends ServiceEETest  
{
	private	String pTableName = null;
	private	String fTableName = null;
	
	//used only in dropTable() as properties are not passed as a parameter.
	private Properties props = null;
			  
	public void createData(Properties p, Connection conn) throws RemoteException 
	{ 
		TestUtil.logTrace("createData");

		//Get the name of the tables
		pTableName = p.getProperty("ptable","");
		fTableName = p.getProperty("ftable","");
		try
		{
			// Create the database tables
			props=p;
			createTable(p,conn);			
			TestUtil.logMsg("Initialized the tables " + pTableName + " and " + fTableName); 
		} 
		catch (Exception e) 
		{ 
			dbUnConnect(conn);
			throw new RemoteException( e.getMessage() );
		} 
	}
				  
	public void destroyData(Connection conn) throws RemoteException
	{
		TestUtil.logTrace("destroyData");
		try
		{
			dropTables(conn);
			TestUtil.logMsg("Deleted all rows from tables " + pTableName + " and " + fTableName );
		} 
		catch (Exception e) 
		{
			throw new RemoteException( e.getMessage() );
		}
	}

	public void dbUnConnect(Connection conn) throws RemoteException 
	{
		TestUtil.logTrace("dbUnConnect");
		// Close the DB connections
		try
		{
			conn.close();
			TestUtil.logMsg("Closed the database connection");
		} 
		catch (Exception e) 
		{
			TestUtil.logErr("Exception occured while trying to close the DB connection");
			throw new RemoteException( e.getMessage() );
		}
	}
	
	private void createTable(Properties testProps, Connection conn) throws RemoteException 
	{  
		TestUtil.logTrace("createTable");	      

		// Remove all rows from the table
		try
		{
			dropTables(conn);
			TestUtil.logTrace("Deleted all rows from Tables " + pTableName + " and " + fTableName + " dropped");
		} 
		catch (Exception e) 
		{
			TestUtil.logTrace("SQLException encountered while deleting rows for Tables " + pTableName + " and " + fTableName + ":" + e.getMessage());
		}

		// Get the size of the table as int
		String strTabSize = testProps.getProperty("cofSize");
		TestUtil.logTrace("strTabSize: " + strTabSize);

		Integer intTabSize = new Integer(strTabSize);
		TestUtil.logTrace("intTabSize: " + intTabSize.toString() );
		logMsg("intTabSize: " + intTabSize.toString() );

		int tSize = intTabSize.intValue();
		TestUtil.logTrace("tSize: " + tSize);
		
		String strTabTypeSize = testProps.getProperty("cofTypeSize");
		TestUtil.logTrace("strTabTypeSize: " + strTabTypeSize);

		Integer intTabTypeSize = new Integer(strTabTypeSize);
		TestUtil.logTrace("intTabTypeSize: " + intTabTypeSize.toString() );
		logMsg("intTabTypeSize: " + intTabTypeSize.toString() );

		int tTypeSize = intTabTypeSize.intValue();
		TestUtil.logTrace("tTypeSize: " + tTypeSize);
		


		try
		{

			// Add  the prescribed table rows
			TestUtil.logTrace("Adding the " + pTableName + " table rows");
			String updateString1 = testProps.getProperty("Dbschema_Tab2_Insert","");
			PreparedStatement pStmt1 = conn.prepareStatement(updateString1);
			for(int j=1; j<=tTypeSize; j++)
			{
				String sTypeDesc = "Type-" + j;
				int newType = j;
				pStmt1.setInt(1, newType);
				pStmt1.setString(2, sTypeDesc);
				pStmt1.executeUpdate();
			}

			// Add  the prescribed table rows
			TestUtil.logTrace("Adding the " + fTableName + " table rows");
			String updateString = testProps.getProperty("Dbschema_Tab1_Insert","");
			PreparedStatement pStmt = conn.prepareStatement(updateString); 
			for(int i=1; i<=tSize; i++)
			{
				// Perform the insert(s)
				int newKey = i;
				String newName = fTableName + "-" + i;
				float newPrice = i + (float).00;
				int newType = i % 5;
				if(newType == 0)
					newType = 5;
				pStmt.setInt(1, newKey);
				pStmt.setString(2, newName);
				pStmt.setFloat(3, newPrice);
				pStmt.setInt(4, newType);
				pStmt.executeUpdate(); 
			}     
			
			pStmt.close();    
			pStmt1.close();
		} 
		catch (SQLException e) 
		{ 
			TestUtil.logErr("SQLException creating " + fTableName + " or " + pTableName + " table");
			TestUtil.logErr(e.getMessage());
			dropTables(conn);
			throw new RemoteException( e.getMessage() );
		}      
	} 

	private void dropTables(Connection conn) throws RemoteException 
	{   
		TestUtil.logTrace("dropTables");	
		// Delete the fTable
		String removeString = props.getProperty("Dbschema_Tab1_Delete","");
		String removeString1 = props.getProperty("Dbschema_Tab2_Delete","");
		try
		{
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(removeString);
			stmt.close();
		} 
		catch (SQLException e) 
		{ 
			throw new RemoteException( e.getMessage() );
		}      
		finally 
		{
			try
			{
				Statement stmt = conn.createStatement();
				stmt.executeUpdate(removeString1);
				stmt.close();
			} 
			catch (SQLException e) 
			{ 
				throw new RemoteException( e.getMessage() );
			}      
		}

	} 
}