java/testing/org/apache/derbyTesting/junit/CleanDatabaseTestSetup.java - derby - Git at Google

 /*
  *
  * Derby - Class org.apache.derbyTesting.functionTests.util.CleanDatabase
  *
  * Licensed to the Apache Software Foundation (ASF) under one or more
  * contributor license agreements.  See the NOTICE file distributed with
  * this work for additional information regarding copyright ownership.
  * The ASF licenses this file to You under the Apache License, Version 2.0
  * (the "License"); you may not use this file except in compliance with
  * the License.  You may obtain a copy of the License at
  *
  *    http://www.apache.org/licenses/LICENSE-2.0
  *
  * Unless required by applicable law or agreed to in writing,
  * software distributed under the License is distributed on an
  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
  * either express or implied. See the License for the specific
  * language governing permissions and limitations under the License.
  */
 package org.apache.derbyTesting.junit;

 import java.sql.*;
 import java.util.ArrayList;
 import java.util.List;

 import junit.framework.Test;

 /**
  * Test decorator that cleans a database on setUp and
  * tearDown to provide a test with a consistent empty
  * database as a starting point. The obejcts are cleaned
  * (dropped) on tearDown to ensure any filures dropping
  * the objects can easily be associated with the test
  * fixtures that created them.
  * <P>
  * Tests can extend to provide a decorator that defines
  * some schema items and then have CleanDatabaseTestSetup
  * automatically clean them up by implementing the decorateSQL method..
  * As an example:
  * <code>
         return new CleanDatabaseTestSetup(suite) {
             protected void decorateSQL(Statement s) throws SQLException {

                 s.execute("CREATE TABLE T (I INT)");
                 s.execute("CREATE INDEX TI ON T(I)")

             }
         };
  * </code>
  *
  */
 public class CleanDatabaseTestSetup extends BaseJDBCTestSetup {

     /**
      * Decorator this test with the cleaner
      */
     public CleanDatabaseTestSetup(Test test) {
         super(test);
     }
     /**
      * Constructor to use when running in a client / server
      * with the server already started on a given host
      * and port.
      */
     /*
      * Currently only used in o.a.dT.ft.tests.replicationTests.StandardTests
      * for running existing JUnit tests on a client server configuration.
      * To avoid duplicating the code inside decorateSQL() methods
      * public static decorate() methods have been factored out
      * for reuse in test methods in StandardTests: e.g. as AnsiTrimTest.decorate(s);
      */
     public CleanDatabaseTestSetup(Test test,
             boolean useNetworkClient,
             String hostName,
             int portNo) {
         super(test);
         if ( useNetworkClient )
         {
             this.jdbcClient = JDBCClient.DERBYNETCLIENT;
         }
         this.hostName = hostName;
         this.portNo = portNo;
     }
     private JDBCClient jdbcClient = null;
     private String hostName = null;
     private int portNo = -1;

     /**
      * Clean the default database using the default connection
      * and calls the decorateSQL to allow sub-classes to
      * initialize their schema requirments.
      */
     protected void setUp() throws Exception {
         if (jdbcClient != null )
         { // We have network client (useNetworkClient) on a given host and port.
             TestConfiguration current = TestConfiguration.getCurrent();
             TestConfiguration modified = new TestConfiguration(current,
                     jdbcClient,
                     hostName,
                     portNo);
             TestConfiguration.setCurrent(modified);
         }
         Connection conn = getConnection();
         conn.setAutoCommit(false);

         // compress as well to allow the fixtures wrapped in
         // this decorator to start with a clean database.
         CleanDatabaseTestSetup.cleanDatabase(conn, true);

         Statement s = conn.createStatement();
         try {
             decorateSQL(s);
             s.close();
             conn.commit();
         } finally {
             // Make sure we release any locks held by the connection at this
             // point. Not doing so may cause subsequent tests to fail.
             try {
                 clearConnection();
             } catch (SQLException sqle) {
                 // Ignore, but print details in debug mode.
                 if (getTestConfiguration().isVerbose()) {
                     println("clearing connection failed: " + sqle.getMessage());
                     sqle.printStackTrace(System.err);
                 }
             }
         }
     }

     /**
      * Sub-classes can override this method to execute
      * SQL statements executed at setUp time once the
      * database has been cleaned.
      * Once this method returns the statement will be closed,
      * commit called and the connection closed. The connection
      * returned by s.getConnection() is the default connection
      * and is in auto-commit false mode.
      * <BR>
      * This implementation does nothing. Sub-classes need not call it.
      * @throws SQLException
      */
     protected void decorateSQL(Statement s) throws SQLException
     {
         // nothing in the default case.
     }

     /**
      * Clean the default database using the default connection.
      */
     protected void tearDown() throws Exception {
         Connection conn = getConnection();
         // See DERBY-5686 - perhaps there's a test that leaves a
         // connection in read-only state - let's check here and
         // if there's a conn that's read-only, unset it, and make
         // the test fail so we find it.
         conn.setAutoCommit(false);
         boolean ok=true;
         if (conn.isReadOnly())
         {
             conn.setReadOnly(false);
             ok=false;
         }

         // Clean the database, ensures that any failure dropping
         // objects can easily be linked to test fixtures that
         // created them
         //
         // No need to compress, any test requiring such a clean
         // setup should not assume it is following another test
         // with this decorator, it should wrap itself in a CleanDatabaseTestSetup.
         // Compress is a somewhat expensive operation so avoid it if possible.
         CleanDatabaseTestSetup.cleanDatabase(conn, false);
         super.tearDown();
         if (!ok)
             fail("the test that was just run left the conn read-only");
     }

     /**
      * Clean a complete database
      * @param conn Connection to be used, must not be in auto-commit mode.
      * @param compress True if selected system tables are to be compressed
      * to avoid potential ordering differences in test output.
      * @throws SQLException database error
      */
      public static void cleanDatabase(Connection conn, boolean compress) throws SQLException {
          clearProperties(conn);
          removeObjects(conn);
          if (compress)
              compressObjects(conn);
          removeRoles(conn);
          removeUsers( conn );
      }

      /**
       * Set of database properties that will be set to NULL (unset)
       * as part of cleaning a database.
       */
      private static final String[] CLEAR_DB_PROPERTIES =
      {
          "derby.database.classpath",
      };

      /**
       * Clear all database properties.
       */
      private static void clearProperties(Connection conn) throws SQLException {

          PreparedStatement ps = conn.prepareCall(
            "CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?, NULL)");

          for (int i = 0; i < CLEAR_DB_PROPERTIES.length; i++)
          {
              ps.setString(1, CLEAR_DB_PROPERTIES[i]);
              ps.executeUpdate();
          }
          ps.close();
          conn.commit();
      }


      /**
       * Remove all objects in all schemas from the database.
       */
      private static void removeObjects(Connection conn) throws SQLException {

         DatabaseMetaData dmd = conn.getMetaData();

         SQLException sqle = null;
         // Loop a number of arbitary times to catch cases
         // where objects are dependent on objects in
         // different schemas.
         for (int count = 0; count < 5; count++) {
             // Fetch all the user schemas into a list
             List<String> schemas = new ArrayList<String>();
             ResultSet rs = dmd.getSchemas();
             while (rs.next()) {

                 String schema = rs.getString("TABLE_SCHEM");
                 if (schema.startsWith("SYS"))
                     continue;
                 if (schema.equals("SQLJ"))
                     continue;
                 if (schema.equals("NULLID"))
                     continue;

                 schemas.add(schema);
             }
             rs.close();

             // DROP all the user schemas.
             sqle = null;
             for (String schema : schemas) {
                 try {
                     JDBC.dropSchema(dmd, schema);
                 } catch (SQLException e) {
                     sqle = e;
                 }
             }
             // No errors means all the schemas we wanted to
             // drop were dropped, so nothing more to do.
             if (sqle == null)
                 return;
         }
         throw sqle;
     }

     private static void removeRoles(Connection conn) throws SQLException {
         // No metadata for roles, so do a query against SYSROLES
         Statement stm = conn.createStatement();
         Statement dropStm = conn.createStatement();

         // cast to overcome territory differences in some cases:
         ResultSet rs = stm.executeQuery(
             "select roleid from sys.sysroles where " +
             "cast(isdef as char(1)) = 'Y'");

         while (rs.next()) {
             dropStm.executeUpdate("DROP ROLE " + JDBC.escape(rs.getString(1)));
         }

         stm.close();
         dropStm.close();
         conn.commit();
     }

     /** Drop all credentials stored in SYSUSERS */
     private static void removeUsers(Connection conn) throws SQLException
     {
         // Get the users
         Statement stm = conn.createStatement();
         ResultSet rs = stm.executeQuery( "select username from sys.sysusers" );
         ArrayList<String> users = new ArrayList<String>();

         while ( rs.next() ) { users.add( rs.getString( 1 ) ); }
         rs.close();
         stm.close();

         // Now delete them
         PreparedStatement   ps = conn.prepareStatement( "call syscs_util.syscs_drop_user( ? )" );

         for ( int i = 0; i < users.size(); i++ )
         {
             ps.setString( 1, (String) users.get( i ) );

             // you can't drop the DBO's credentials. sorry.
             try {
                 ps.executeUpdate();
             }
             catch (SQLException se)
             {
                 if ( "4251F".equals( se.getSQLState() ) ) { continue; }
                 else { throw se; }
             }
         }

         ps.close();
         conn.commit();
     }

      /**
       * Set of objects that will be compressed as part of cleaning a database.
       */
      private static final String[] COMPRESS_DB_OBJECTS =
      {
          "SYS.SYSDEPENDS",
      };

      /**
       * Compress the objects in the database.
       *
       * @param conn the db connection
       * @throws SQLException database error
       */
      private static void compressObjects(Connection conn) throws SQLException {

     	 CallableStatement cs = conn.prepareCall
     	     ("CALL SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE(?, ?, 1, 1, 1)");

     	 for (int i = 0; i < COMPRESS_DB_OBJECTS.length; i++)
     	 {
     		 int delim = COMPRESS_DB_OBJECTS[i].indexOf(".");
              cs.setString(1, COMPRESS_DB_OBJECTS[i].substring(0, delim) );
              cs.setString(2, COMPRESS_DB_OBJECTS[i].substring(delim+1) );
              cs.execute();
     	 }

     	 cs.close();
     	 conn.commit();
      }

 }
	/*
	*
	* Derby - Class org.apache.derbyTesting.functionTests.util.CleanDatabase
	*
	* Licensed to the Apache Software Foundation (ASF) under one or more
	* contributor license agreements. See the NOTICE file distributed with
	* this work for additional information regarding copyright ownership.
	* The ASF licenses this file to You under the Apache License, Version 2.0
	* (the "License"); you may not use this file except in compliance with
	* the License. You may obtain a copy of the License at
	*
	* http://www.apache.org/licenses/LICENSE-2.0
	*
	* Unless required by applicable law or agreed to in writing,
	* software distributed under the License is distributed on an
	* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
	* either express or implied. See the License for the specific
	* language governing permissions and limitations under the License.
	*/
	package org.apache.derbyTesting.junit;

	import java.sql.*;
	import java.util.ArrayList;
	import java.util.List;

	import junit.framework.Test;

	/**
	* Test decorator that cleans a database on setUp and
	* tearDown to provide a test with a consistent empty
	* database as a starting point. The obejcts are cleaned
	* (dropped) on tearDown to ensure any filures dropping
	* the objects can easily be associated with the test
	* fixtures that created them.
	* <P>
	* Tests can extend to provide a decorator that defines
	* some schema items and then have CleanDatabaseTestSetup
	* automatically clean them up by implementing the decorateSQL method..
	* As an example:
	* <code>
	return new CleanDatabaseTestSetup(suite) {
	protected void decorateSQL(Statement s) throws SQLException {

	s.execute("CREATE TABLE T (I INT)");
	s.execute("CREATE INDEX TI ON T(I)")

	}
	};
	* </code>
	*
	*/
	public class CleanDatabaseTestSetup extends BaseJDBCTestSetup {

	/**
	* Decorator this test with the cleaner
	*/
	public CleanDatabaseTestSetup(Test test) {
	super(test);
	}
	/**
	* Constructor to use when running in a client / server
	* with the server already started on a given host
	* and port.
	*/
	/*
	* Currently only used in o.a.dT.ft.tests.replicationTests.StandardTests
	* for running existing JUnit tests on a client server configuration.
	* To avoid duplicating the code inside decorateSQL() methods
	* public static decorate() methods have been factored out
	* for reuse in test methods in StandardTests: e.g. as AnsiTrimTest.decorate(s);
	*/
	public CleanDatabaseTestSetup(Test test,
	boolean useNetworkClient,
	String hostName,
	int portNo) {
	super(test);
	if ( useNetworkClient )
	{
	this.jdbcClient = JDBCClient.DERBYNETCLIENT;
	}
	this.hostName = hostName;
	this.portNo = portNo;
	}
	private JDBCClient jdbcClient = null;
	private String hostName = null;
	private int portNo = -1;

	/**
	* Clean the default database using the default connection
	* and calls the decorateSQL to allow sub-classes to
	* initialize their schema requirments.
	*/
	protected void setUp() throws Exception {
	if (jdbcClient != null )
	{ // We have network client (useNetworkClient) on a given host and port.
	TestConfiguration current = TestConfiguration.getCurrent();
	TestConfiguration modified = new TestConfiguration(current,
	jdbcClient,
	hostName,
	portNo);
	TestConfiguration.setCurrent(modified);
	}
	Connection conn = getConnection();
	conn.setAutoCommit(false);

	// compress as well to allow the fixtures wrapped in
	// this decorator to start with a clean database.
	CleanDatabaseTestSetup.cleanDatabase(conn, true);

	Statement s = conn.createStatement();
	try {
	decorateSQL(s);
	s.close();
	conn.commit();
	} finally {
	// Make sure we release any locks held by the connection at this
	// point. Not doing so may cause subsequent tests to fail.
	try {
	clearConnection();
	} catch (SQLException sqle) {
	// Ignore, but print details in debug mode.
	if (getTestConfiguration().isVerbose()) {
	println("clearing connection failed: " + sqle.getMessage());
	sqle.printStackTrace(System.err);
	}
	}
	}
	}

	/**
	* Sub-classes can override this method to execute
	* SQL statements executed at setUp time once the
	* database has been cleaned.
	* Once this method returns the statement will be closed,
	* commit called and the connection closed. The connection
	* returned by s.getConnection() is the default connection
	* and is in auto-commit false mode.
	* <BR>
	* This implementation does nothing. Sub-classes need not call it.
	* @throws SQLException
	*/
	protected void decorateSQL(Statement s) throws SQLException
	{
	// nothing in the default case.
	}

	/**
	* Clean the default database using the default connection.
	*/
	protected void tearDown() throws Exception {
	Connection conn = getConnection();
	// See DERBY-5686 - perhaps there's a test that leaves a
	// connection in read-only state - let's check here and
	// if there's a conn that's read-only, unset it, and make
	// the test fail so we find it.
	conn.setAutoCommit(false);
	boolean ok=true;
	if (conn.isReadOnly())
	{
	conn.setReadOnly(false);
	ok=false;
	}

	// Clean the database, ensures that any failure dropping
	// objects can easily be linked to test fixtures that
	// created them
	//
	// No need to compress, any test requiring such a clean
	// setup should not assume it is following another test
	// with this decorator, it should wrap itself in a CleanDatabaseTestSetup.
	// Compress is a somewhat expensive operation so avoid it if possible.
	CleanDatabaseTestSetup.cleanDatabase(conn, false);
	super.tearDown();
	if (!ok)
	fail("the test that was just run left the conn read-only");
	}

	/**
	* Clean a complete database
	* @param conn Connection to be used, must not be in auto-commit mode.
	* @param compress True if selected system tables are to be compressed
	* to avoid potential ordering differences in test output.
	* @throws SQLException database error
	*/
	public static void cleanDatabase(Connection conn, boolean compress) throws SQLException {
	clearProperties(conn);
	removeObjects(conn);
	if (compress)
	compressObjects(conn);
	removeRoles(conn);
	removeUsers( conn );
	}

	/**
	* Set of database properties that will be set to NULL (unset)
	* as part of cleaning a database.
	*/
	private static final String[] CLEAR_DB_PROPERTIES =
	{
	"derby.database.classpath",
	};

	/**
	* Clear all database properties.
	*/
	private static void clearProperties(Connection conn) throws SQLException {

	PreparedStatement ps = conn.prepareCall(
	"CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?, NULL)");

	for (int i = 0; i < CLEAR_DB_PROPERTIES.length; i++)
	{
	ps.setString(1, CLEAR_DB_PROPERTIES[i]);
	ps.executeUpdate();
	}
	ps.close();
	conn.commit();
	}


	/**
	* Remove all objects in all schemas from the database.
	*/
	private static void removeObjects(Connection conn) throws SQLException {

	DatabaseMetaData dmd = conn.getMetaData();

	SQLException sqle = null;
	// Loop a number of arbitary times to catch cases
	// where objects are dependent on objects in
	// different schemas.
	for (int count = 0; count < 5; count++) {
	// Fetch all the user schemas into a list
	List<String> schemas = new ArrayList<String>();
	ResultSet rs = dmd.getSchemas();
	while (rs.next()) {

	String schema = rs.getString("TABLE_SCHEM");
	if (schema.startsWith("SYS"))
	continue;
	if (schema.equals("SQLJ"))
	continue;
	if (schema.equals("NULLID"))
	continue;

	schemas.add(schema);
	}
	rs.close();

	// DROP all the user schemas.
	sqle = null;
	for (String schema : schemas) {
	try {
	JDBC.dropSchema(dmd, schema);
	} catch (SQLException e) {
	sqle = e;
	}
	}
	// No errors means all the schemas we wanted to
	// drop were dropped, so nothing more to do.
	if (sqle == null)
	return;
	}
	throw sqle;
	}

	private static void removeRoles(Connection conn) throws SQLException {
	// No metadata for roles, so do a query against SYSROLES
	Statement stm = conn.createStatement();
	Statement dropStm = conn.createStatement();

	// cast to overcome territory differences in some cases:
	ResultSet rs = stm.executeQuery(
	"select roleid from sys.sysroles where " +
	"cast(isdef as char(1)) = 'Y'");

	while (rs.next()) {
	dropStm.executeUpdate("DROP ROLE " + JDBC.escape(rs.getString(1)));
	}

	stm.close();
	dropStm.close();
	conn.commit();
	}

	/** Drop all credentials stored in SYSUSERS */
	private static void removeUsers(Connection conn) throws SQLException
	{
	// Get the users
	Statement stm = conn.createStatement();
	ResultSet rs = stm.executeQuery( "select username from sys.sysusers" );
	ArrayList<String> users = new ArrayList<String>();

	while ( rs.next() ) { users.add( rs.getString( 1 ) ); }
	rs.close();
	stm.close();

	// Now delete them
	PreparedStatement ps = conn.prepareStatement( "call syscs_util.syscs_drop_user( ? )" );

	for ( int i = 0; i < users.size(); i++ )
	{
	ps.setString( 1, (String) users.get( i ) );

	// you can't drop the DBO's credentials. sorry.
	try {
	ps.executeUpdate();
	}
	catch (SQLException se)
	{
	if ( "4251F".equals( se.getSQLState() ) ) { continue; }
	else { throw se; }
	}
	}

	ps.close();
	conn.commit();
	}

	/**
	* Set of objects that will be compressed as part of cleaning a database.
	*/
	private static final String[] COMPRESS_DB_OBJECTS =
	{
	"SYS.SYSDEPENDS",
	};

	/**
	* Compress the objects in the database.
	*
	* @param conn the db connection
	* @throws SQLException database error
	*/
	private static void compressObjects(Connection conn) throws SQLException {

	CallableStatement cs = conn.prepareCall
	("CALL SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE(?, ?, 1, 1, 1)");

	for (int i = 0; i < COMPRESS_DB_OBJECTS.length; i++)
	{
	int delim = COMPRESS_DB_OBJECTS[i].indexOf(".");
	cs.setString(1, COMPRESS_DB_OBJECTS[i].substring(0, delim) );
	cs.setString(2, COMPRESS_DB_OBJECTS[i].substring(delim+1) );
	cs.execute();
	}

	cs.close();
	conn.commit();
	}

	}