plugins/org.eclipse.emf.cdo.tests.db/src/org/eclipse/emf/cdo/tests/db/capabilities/AbstractCapabilityTest.java - cdo/cdo - Git at Google

 /*
  * Copyright (c) 2009-2012, 2015 Eike Stepper (Berlin, Germany) and others.
  * All rights reserved. This program and the accompanying materials
  * are made available under the terms of the Eclipse Public License v1.0
  * which accompanies this distribution, and is available at
  * http://www.eclipse.org/legal/epl-v10.html
  *
  * Contributors:
  *    Eike Stepper - initial API and implementation
  */
 package org.eclipse.emf.cdo.tests.db.capabilities;

 import org.eclipse.net4j.db.DBUtil;
 import org.eclipse.net4j.db.IDBConnectionProvider;

 import java.sql.Connection;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;

 import junit.framework.TestCase;

 /**
  * This is a simple test case that can be used to analyze how a DBMS handles DML in the middle of a transaction.
  *
  * @author Stefan Winkler
  */
 public abstract class AbstractCapabilityTest extends TestCase
 {
   public AbstractCapabilityTest(String name)
   {
     super(name);
   }

   public void testDirtyRead() throws Exception
   {
     msg("TEST " + getClass().getSimpleName() + " - DIRTY READ");
     msg("----------------------------------------------------------");
     msg("Transaction 1 changes a value, transaction 2 will read the value.");
     msg("The value of transaction 2 should be UNCHANGED (else we have a dirty read)");

     Thread t = new Thread()
     {
       @Override
       public void run()
       {
         try
         {
           IDBConnectionProvider provider = getConnectionProvider();
           Connection transaction1 = provider.getConnection();
           transaction1.setAutoCommit(false);

           Statement tx1stmt = transaction1.createStatement();
           tx1stmt.executeUpdate("update status_table set status = 'changed' where trans = 'transaction1'");
           msg("Read value (transaction 1) is "
               + select(transaction1, "select status from status_table where trans = 'transaction1'").toUpperCase());
           sleep(1000);

           transaction1.rollback();
           transaction1.close();
         }
         catch (Exception e)
         {
           throw new Error(e);
         }
       }
     };

     t.start();
     Thread.sleep(300);

     IDBConnectionProvider provider = getConnectionProvider();
     Connection transaction2 = provider.getConnection();

     transaction2.setAutoCommit(false);

     msg("Read value (transaction 2) is "
         + select(transaction2, "select status from status_table where trans = 'transaction1'").toUpperCase());
     msg("----------------------------------------------------------");
     transaction2.rollback();
     transaction2.close();
   }

   public void testRollback() throws Exception
   {
     msg("TEST " + getClass().getSimpleName() + " - ROLLBACK");
     msg("----------------------------------------------------------");
     msg("Transaction changes a value and does a rollback.");
     msg("The value of after rollback should be UNCHANGED.");

     IDBConnectionProvider provider = getConnectionProvider();
     Connection transaction1 = provider.getConnection();
     transaction1.setAutoCommit(false);

     Statement tx1stmt = transaction1.createStatement();

     try
     {
       tx1stmt.executeUpdate("update status_table set status = 'changed' where trans = 'transaction1'");
     }
     finally
     {
       DBUtil.close(tx1stmt);
     }

     msg("Read value before rollback is "
         + select(transaction1, "select status from status_table where trans = 'transaction1'").toUpperCase());

     transaction1.rollback();
     transaction1.close();

     Connection view = provider.getConnection();
     msg("Read value after rollback is "
         + select(view, "select status from status_table where trans = 'transaction1'").toUpperCase());
     view.close();
     msg("----------------------------------------------------------");
   }

   public void testDml() throws Exception
   {
     msg("TEST " + getClass().getSimpleName() + " - DML");
     msg("----------------------------------------------------------");
     msg("Transaction 1 will execute DML, transaction 2 will just change its data.");

     Thread t = new Thread()
     {
       @Override
       public void run()
       {
         try
         {
           IDBConnectionProvider provider = getConnectionProvider();
           Connection transaction2 = provider.getConnection();
           Statement tx2stmt = transaction2.createStatement();
           transaction2.setAutoCommit(false);
           tx2stmt.executeUpdate("update status_table set status = 'changed' where trans = 'transaction2'");
           tx2stmt.executeUpdate("update change_table set status = 'changed' where trans = 'transaction2'");
           tx2stmt.close();
           sleep(1000);
           transaction2.rollback();
           transaction2.close();
         }
         catch (Exception e)
         {
           throw new Error(e);
         }
       }
     };

     t.start();
     Thread.sleep(100);

     IDBConnectionProvider provider = getConnectionProvider();
     Connection transaction1 = provider.getConnection();
     transaction1.setAutoCommit(false);

     Statement tx1stmt = transaction1.createStatement();

     tx1stmt.executeUpdate("update status_table set status = 'changed' where trans = 'transaction1'");
     tx1stmt.executeUpdate("update change_table set status = 'changed' where trans = 'transaction1'");

     tx1stmt.execute("alter table change_table add new_column varchar(255) default 'added column present'");

     tx1stmt.close();

     transaction1.rollback();
     transaction1.close();

     t.join();

     Connection view = provider.getConnection();

     msg("transaction1: unchanged table record is "
         + select(view, "select status from status_table where trans = 'transaction1'").toUpperCase());
     msg("transaction2: unchanged table record is "
         + select(view, "select status from status_table where trans = 'transaction2'").toUpperCase());

     msg("transaction1: changed table record is "
         + select(view, "select status from change_table where trans = 'transaction1'").toUpperCase());
     msg("transaction2: changed table record is "
         + select(view, "select status from change_table where trans = 'transaction2'").toUpperCase());

     String present = "present";
     try
     {
       select(view, "select new_column from change_table where trans = 'transaction2'");
     }
     catch (SQLException e)
     {
       present = "not present";
     }

     msg("Added column is " + present.toUpperCase());
     view.close();

     msg("----------------------------------------------------------");
   }

   @Override
   protected void setUp() throws Exception
   {
     // create table
     Connection conn = getConnectionProvider().getConnection();
     conn.setAutoCommit(false);
     Statement stmt = conn.createStatement();

     // make sure tables don't exist!
     try
     {
       stmt.execute("drop table status_table");
     }
     catch (Exception e)
     {
     }

     try
     {
       stmt.execute("drop table change_table");
     }
     catch (Exception e)
     {
     }

     stmt.execute("create table status_table (trans varchar(255), status varchar(255))");
     stmt.execute("insert into status_table values ('transaction1', 'unchanged')");
     stmt.execute("insert into status_table values ('transaction2', 'unchanged')");

     stmt.execute("create table change_table (trans varchar(255), status varchar(255))");
     stmt.execute("insert into change_table values ('transaction1', 'unchanged')");
     stmt.execute("insert into change_table values ('transaction2', 'unchanged')");

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

   @Override
   protected void tearDown() throws Exception
   {
     Connection conn = getConnectionProvider().getConnection();
     conn.setAutoCommit(true);
     Statement stmt = conn.createStatement();

     stmt.execute("drop table status_table");
     stmt.execute("drop table change_table");

     stmt.close();
     conn.close();
   }

   protected abstract IDBConnectionProvider getConnectionProvider();

   private void msg(String string)
   {
     System.out.println(string);
   }

   private String select(Connection conn, String sql) throws SQLException
   {
     ResultSet rs = null;
     try
     {
       rs = conn.createStatement().executeQuery(sql);
       rs.next();
       return rs.getString(1);
     }
     finally
     {
       if (rs != null)
       {
         try
         {
           rs.close();
         }
         catch (SQLException ex)
         {
           // NOP
         }
       }
     }
   }

   @SuppressWarnings("unused")
   private void sqlDump(Connection conn, String sql)
   {
     ResultSet rs = null;
     try
     {
       System.out.format("Dumping output of %s\n", sql); //$NON-NLS-1$
       rs = conn.createStatement().executeQuery(sql);
       int numCol = rs.getMetaData().getColumnCount();

       StringBuilder row = new StringBuilder(" ");
       for (int c = 1; c <= numCol; c++)
       {
         row.append(String.format("%15s | ", rs.getMetaData().getColumnLabel(c))); //$NON-NLS-1$
       }

       System.out.println(row.toString());

       row = new StringBuilder();
       for (int c = 1; c <= numCol; c++)
       {
         row.append("-----------------+"); //$NON-NLS-1$
       }

       System.out.println(row.toString());

       while (rs.next())
       {
         row = new StringBuilder(" ");
         for (int c = 1; c <= numCol; c++)
         {
           row.append(String.format("%15s | ", rs.getString(c))); //$NON-NLS-1$
         }

         System.out.println(row.toString());
       }

       row = new StringBuilder();
       for (int c = 1; c <= numCol; c++)
       {
         row.append("-----------------+"); //$NON-NLS-1$
       }

       System.out.println(row.toString());
     }
     catch (SQLException ex)
     {
       // NOP
     }
     finally
     {
       if (rs != null)
       {
         try
         {
           rs.close();
         }
         catch (SQLException ex)
         {
           // NOP
         }
       }
     }
   }
 }
	/*
	* Copyright (c) 2009-2012, 2015 Eike Stepper (Berlin, Germany) and others.
	* All rights reserved. This program and the accompanying materials
	* are made available under the terms of the Eclipse Public License v1.0
	* which accompanies this distribution, and is available at
	* http://www.eclipse.org/legal/epl-v10.html
	*
	* Contributors:
	* Eike Stepper - initial API and implementation
	*/
	package org.eclipse.emf.cdo.tests.db.capabilities;

	import org.eclipse.net4j.db.DBUtil;
	import org.eclipse.net4j.db.IDBConnectionProvider;

	import java.sql.Connection;
	import java.sql.ResultSet;
	import java.sql.SQLException;
	import java.sql.Statement;

	import junit.framework.TestCase;

	/**
	* This is a simple test case that can be used to analyze how a DBMS handles DML in the middle of a transaction.
	*
	* @author Stefan Winkler
	*/
	public abstract class AbstractCapabilityTest extends TestCase
	{
	public AbstractCapabilityTest(String name)
	{
	super(name);
	}

	public void testDirtyRead() throws Exception
	{
	msg("TEST " + getClass().getSimpleName() + " - DIRTY READ");
	msg("----------------------------------------------------------");
	msg("Transaction 1 changes a value, transaction 2 will read the value.");
	msg("The value of transaction 2 should be UNCHANGED (else we have a dirty read)");

	Thread t = new Thread()
	{
	@Override
	public void run()
	{
	try
	{
	IDBConnectionProvider provider = getConnectionProvider();
	Connection transaction1 = provider.getConnection();
	transaction1.setAutoCommit(false);

	Statement tx1stmt = transaction1.createStatement();
	tx1stmt.executeUpdate("update status_table set status = 'changed' where trans = 'transaction1'");
	msg("Read value (transaction 1) is "
	+ select(transaction1, "select status from status_table where trans = 'transaction1'").toUpperCase());
	sleep(1000);

	transaction1.rollback();
	transaction1.close();
	}
	catch (Exception e)
	{
	throw new Error(e);
	}
	}
	};

	t.start();
	Thread.sleep(300);

	IDBConnectionProvider provider = getConnectionProvider();
	Connection transaction2 = provider.getConnection();

	transaction2.setAutoCommit(false);

	msg("Read value (transaction 2) is "
	+ select(transaction2, "select status from status_table where trans = 'transaction1'").toUpperCase());
	msg("----------------------------------------------------------");
	transaction2.rollback();
	transaction2.close();
	}

	public void testRollback() throws Exception
	{
	msg("TEST " + getClass().getSimpleName() + " - ROLLBACK");
	msg("----------------------------------------------------------");
	msg("Transaction changes a value and does a rollback.");
	msg("The value of after rollback should be UNCHANGED.");

	IDBConnectionProvider provider = getConnectionProvider();
	Connection transaction1 = provider.getConnection();
	transaction1.setAutoCommit(false);

	Statement tx1stmt = transaction1.createStatement();

	try
	{
	tx1stmt.executeUpdate("update status_table set status = 'changed' where trans = 'transaction1'");
	}
	finally
	{
	DBUtil.close(tx1stmt);
	}

	msg("Read value before rollback is "
	+ select(transaction1, "select status from status_table where trans = 'transaction1'").toUpperCase());

	transaction1.rollback();
	transaction1.close();

	Connection view = provider.getConnection();
	msg("Read value after rollback is "
	+ select(view, "select status from status_table where trans = 'transaction1'").toUpperCase());
	view.close();
	msg("----------------------------------------------------------");
	}

	public void testDml() throws Exception
	{
	msg("TEST " + getClass().getSimpleName() + " - DML");
	msg("----------------------------------------------------------");
	msg("Transaction 1 will execute DML, transaction 2 will just change its data.");

	Thread t = new Thread()
	{
	@Override
	public void run()
	{
	try
	{
	IDBConnectionProvider provider = getConnectionProvider();
	Connection transaction2 = provider.getConnection();
	Statement tx2stmt = transaction2.createStatement();
	transaction2.setAutoCommit(false);
	tx2stmt.executeUpdate("update status_table set status = 'changed' where trans = 'transaction2'");
	tx2stmt.executeUpdate("update change_table set status = 'changed' where trans = 'transaction2'");
	tx2stmt.close();
	sleep(1000);
	transaction2.rollback();
	transaction2.close();
	}
	catch (Exception e)
	{
	throw new Error(e);
	}
	}
	};

	t.start();
	Thread.sleep(100);

	IDBConnectionProvider provider = getConnectionProvider();
	Connection transaction1 = provider.getConnection();
	transaction1.setAutoCommit(false);

	Statement tx1stmt = transaction1.createStatement();

	tx1stmt.executeUpdate("update status_table set status = 'changed' where trans = 'transaction1'");
	tx1stmt.executeUpdate("update change_table set status = 'changed' where trans = 'transaction1'");

	tx1stmt.execute("alter table change_table add new_column varchar(255) default 'added column present'");

	tx1stmt.close();

	transaction1.rollback();
	transaction1.close();

	t.join();

	Connection view = provider.getConnection();

	msg("transaction1: unchanged table record is "
	+ select(view, "select status from status_table where trans = 'transaction1'").toUpperCase());
	msg("transaction2: unchanged table record is "
	+ select(view, "select status from status_table where trans = 'transaction2'").toUpperCase());

	msg("transaction1: changed table record is "
	+ select(view, "select status from change_table where trans = 'transaction1'").toUpperCase());
	msg("transaction2: changed table record is "
	+ select(view, "select status from change_table where trans = 'transaction2'").toUpperCase());

	String present = "present";
	try
	{
	select(view, "select new_column from change_table where trans = 'transaction2'");
	}
	catch (SQLException e)
	{
	present = "not present";
	}

	msg("Added column is " + present.toUpperCase());
	view.close();

	msg("----------------------------------------------------------");
	}

	@Override
	protected void setUp() throws Exception
	{
	// create table
	Connection conn = getConnectionProvider().getConnection();
	conn.setAutoCommit(false);
	Statement stmt = conn.createStatement();

	// make sure tables don't exist!
	try
	{
	stmt.execute("drop table status_table");
	}
	catch (Exception e)
	{
	}

	try
	{
	stmt.execute("drop table change_table");
	}
	catch (Exception e)
	{
	}

	stmt.execute("create table status_table (trans varchar(255), status varchar(255))");
	stmt.execute("insert into status_table values ('transaction1', 'unchanged')");
	stmt.execute("insert into status_table values ('transaction2', 'unchanged')");

	stmt.execute("create table change_table (trans varchar(255), status varchar(255))");
	stmt.execute("insert into change_table values ('transaction1', 'unchanged')");
	stmt.execute("insert into change_table values ('transaction2', 'unchanged')");

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

	@Override
	protected void tearDown() throws Exception
	{
	Connection conn = getConnectionProvider().getConnection();
	conn.setAutoCommit(true);
	Statement stmt = conn.createStatement();

	stmt.execute("drop table status_table");
	stmt.execute("drop table change_table");

	stmt.close();
	conn.close();
	}

	protected abstract IDBConnectionProvider getConnectionProvider();

	private void msg(String string)
	{
	System.out.println(string);
	}

	private String select(Connection conn, String sql) throws SQLException
	{
	ResultSet rs = null;
	try
	{
	rs = conn.createStatement().executeQuery(sql);
	rs.next();
	return rs.getString(1);
	}
	finally
	{
	if (rs != null)
	{
	try
	{
	rs.close();
	}
	catch (SQLException ex)
	{
	// NOP
	}
	}
	}
	}

	@SuppressWarnings("unused")
	private void sqlDump(Connection conn, String sql)
	{
	ResultSet rs = null;
	try
	{
	System.out.format("Dumping output of %s\n", sql); //$NON-NLS-1$
	rs = conn.createStatement().executeQuery(sql);
	int numCol = rs.getMetaData().getColumnCount();

	StringBuilder row = new StringBuilder(" ");
	for (int c = 1; c <= numCol; c++)
	{
	row.append(String.format("%15s \| ", rs.getMetaData().getColumnLabel(c))); //$NON-NLS-1$
	}

	System.out.println(row.toString());

	row = new StringBuilder();
	for (int c = 1; c <= numCol; c++)
	{
	row.append("-----------------+"); //$NON-NLS-1$
	}

	System.out.println(row.toString());

	while (rs.next())
	{
	row = new StringBuilder(" ");
	for (int c = 1; c <= numCol; c++)
	{
	row.append(String.format("%15s \| ", rs.getString(c))); //$NON-NLS-1$
	}

	System.out.println(row.toString());
	}

	row = new StringBuilder();
	for (int c = 1; c <= numCol; c++)
	{
	row.append("-----------------+"); //$NON-NLS-1$
	}

	System.out.println(row.toString());
	}
	catch (SQLException ex)
	{
	// NOP
	}
	finally
	{
	if (rs != null)
	{
	try
	{
	rs.close();
	}
	catch (SQLException ex)
	{
	// NOP
	}
	}
	}
	}
	}