/*
    * Replica is published under the terms
    * of the Apache Software License.
    */
   package replica.database.commands.plsql;
   
   import java.sql.Connection;
   import java.sql.ResultSet;
   import java.sql.SQLException;
  import java.sql.Statement;
  
  import org.hsqldb.Server;
  import org.springframework.jdbc.datasource.DriverManagerDataSource;
  
  import replica.database.commands.DatabaseCommandVO;
  
  import junit.framework.TestCase;
  
  /***
   * 
   * @author Pedro Costa
   * @author Helder Silva
   * @since 31/Jan/2004
   */
  public class PlsqlDbCommandTest extends TestCase {
  	
  	PlsqlDbCommand plsqlDbCommand;
  	Server server;
  	DriverManagerDataSource dataSource;
  	
  	String testTableName = "replica_plsql_test";
  
  	/***
  	 * Constructor for PlsqlDbCommandTest.
  	 * @param name
  	 */
  	public PlsqlDbCommandTest(String name) {
  		super(name);
  	}
  
  	/*
  	 * @see TestCase#setUp()
  	 */
  	protected void setUp() throws Exception {
  		super.setUp();
  		
  		server = new Server();
  		server.putPropertiesFromString("database.0=plsqldbcomtest");
  		server.setPort(9002);
  		server.start();
  		
  		try{ Thread.sleep(500); }catch(InterruptedException e){	}
  		
  //		BasicDataSource dataSource = new BasicDataSource();
  		dataSource = new DriverManagerDataSource();
  		dataSource.setDriverClassName("org.hsqldb.jdbcDriver");
  		dataSource.setUrl("jdbc:hsqldb:hsql://localhost:9002");
  		dataSource.setUsername("sa");
  		
  		plsqlDbCommand = new PlsqlDbCommand();
  		plsqlDbCommand.setDataSource(dataSource);
  		
  		try{ dropTestingData(); }catch(Throwable t){ }
  		
  		createTestingData();
  	}
  
  	/*
  	 * @see TestCase#tearDown()
  	 */
  	protected void tearDown() throws Exception {
  		super.tearDown();
  		
  		dropTestingData();
  		
  		server.stop();
  		while( server.getState() != 16 /*ServerConstants.SERVER_STATE_SHUTDOWN*/ ){
  			try{ Thread.sleep(200); }catch(InterruptedException e){}
  		}
  	}
  	
  	void createTestingData(){
  		
  		Connection con = null;
  		try{
  			
  			con = dataSource.getConnection();
  			
  			Statement st = con.createStatement();
  			
  			st.execute("create table " + testTableName + " (id integer primary key, nome varchar);");
  			
  			st.close();
  		}
  		catch(SQLException e){
  			throw new RuntimeException(e);
  		}
  		finally{
  			try{ con.close(); }catch(Throwable t){}
 		}
 	}
 
 	void dropTestingData(){
 		
 		Connection con = null;
 		try{
 			
 			con = dataSource.getConnection();
 			
 			Statement st = con.createStatement();
 			
 			st.execute("drop table " + testTableName + ";");
 			
 			st.close();
 		}
 		catch(SQLException e){
 			throw new RuntimeException(e);
 		}
 		finally{
 			try{ con.close(); }catch(Throwable t){}
 		}
 	}
 	
 	/*
 	 * Test for Object execute(DatabaseCommandVO)
 	 */
 	public void testExecuteDatabaseCommandVO() throws SQLException{
 		
 		String program = "begin insert into " + testTableName + " values ( 1, 'nome' ); commit; end;";
 		
 		DatabaseCommandVO c = new DatabaseCommandVO(dataSource.getConnection(), program);
 		
 		plsqlDbCommand.execute(c);
 		
 		assertEquals(1, getTestingTableCount());
 	}
 
 	int getTestingTableCount(){
 		
 		Connection con = null;
 		try{
 			
 			con = dataSource.getConnection();
 			
 			Statement st = con.createStatement();
 			
 			ResultSet rs = st.executeQuery("select count(*) from " + testTableName + ";");
 			
 			rs.next();
 			int res = rs.getInt(1);
 			
 			st.close();
 			
 			return res;
 		}
 		catch(SQLException e){
 			throw new RuntimeException(e);
 		}
 		finally{
 			try{ con.close(); }catch(Throwable t){}
 		}
 	}
 	
 	public void testInit() {
 		plsqlDbCommand.init();
 		
 		assertNotNull(plsqlDbCommand.mobisnapInterpreter);
 	}
 
 }