<?php
use Mammut\DB\Model\ColumnInfo;
use Mammut\DB\Model\TableInfo;
use Mammut\DB\Model\ViewInfo;
use Mammut\DB\Sql\Expression\SQLFunction;
use Mammut\DB\Sql\Select;
use Mammut\DB\Sql\Elements\Condition;
use Mammut\DB\Sql\Expression\Parameter;
use Mammut\DB\Sql\Expression\Calc;
use Mammut\DB\Sql\Expression\Compare;
use Mammut\DB\Sql\Expression\Literal;
use Mammut\DB\Sql\Expression\Between;
use Mammut\DB\Sql\Expression\iExprValue;
use Mammut\IO\File;

/**
 * Base test for all database
 * 
 * @author Stefan Daurer <s.daurer@q-spot.org>
 */
abstract class AbstractDBTestBase extends PHPUnit_Framework_TestCase {
	
	/**
	 *
	 * @var Mammut\DB\DB
	 */
	protected $con;

	protected function tearDown() {
		if(!empty($this->con))
			$this->con->close();
	}

	public function testClasses() {
		// test for valid class files
		new \Mammut\DB\Sql\Elements\Table('test');
		
		new \Mammut\DB\Sql\Expression\PredicateSet();

		new \Mammut\DB\Sql\Expression\Calc('a');
		new \Mammut\DB\Sql\Expression\Compare('a', \Mammut\DB\Sql\Expression\Compare::OP_EQ, '1');
		new \Mammut\DB\Sql\Expression\In('a', [1,2]);
		new \Mammut\DB\Sql\Expression\NotIn('a', [1,2]);
		new \Mammut\DB\Sql\Expression\IsNull('a');
		new \Mammut\DB\Sql\Expression\IsNotNull('a');
		
		new \Mammut\DB\Sql\Elements\Where();
		new \Mammut\DB\Sql\Elements\Having();

 		new \Mammut\DB\Sql\Insert();
		new \Mammut\DB\Sql\Select();
		new \Mammut\DB\Sql\Update();
		new \Mammut\DB\Sql\Delete();
	}

	public function testConnect() {
		$this->assertNotNull($this->con, "No database connection");
		$dialect = $this->con->getDialect();
		$this->assertInstanceOf('\\Mammut\\DB\\Sql\\Dialect\\iDialect', $dialect, "Dialect not a iDialect object");
	}

	/**
	 * @depends testConnect
	 */
	public function testBasics() {
		$this->assertNotEmpty($this->con->getDatabase(), "No database info");
		
		$dia = $this->con->getDialect();
		$this->assertNotEmpty($dia, "No database dialect");
		$this->assertNotEmpty($dia->getDdl(), "DDL is empty");
		
		$c = Calc::newC('a')->add(1)->sub(2);
		$pattern = '#.*a.*\\+.*1.*\\-.*2.*#i';
		$this->assertRegExp($pattern, $dia->getExpressionSQL($c), "Invalid result");

		$c = Condition::newAndSet()->isNull('row')->isNotNull('row2');
		$this->con->select('dummy')->where($c)->getSql($this->con->getDialect());
		$c = Condition::newAndSet()->equalTo('a',1)->notEqualTo('b', 'Test');
		$this->con->select('dummy')->where($c)->getSql($this->con->getDialect());
		$c = Condition::newAndSet()->in('a',[1])->notIn('b', ['Test']);
		$this->con->select('dummy')->where($c)->getSql($this->con->getDialect());
		$c = Condition::newAndSet()->between('a',1,2);  // TODO: ->notBetween('b', 3, 4);
		$this->con->select('dummy')->where($c)->getSql($this->con->getDialect());
		$c = Condition::newAndSet()->nest(Condition::OP_AND)->isNull('a')->unnest();
		$this->con->select('dummy')->where($c)->getSql($this->con->getDialect());
		
		// select without a table
		$sel = $this->con->select()->columns(['c' => SQLFunction::random()]);
		$this->assertTrue(is_numeric($c = $this->con->getObject($sel)->c), 'Result should be a random number, '.$c.' returned');

	}
	
	/**
	 * This test checks the query builder and SQL dialect classes
	 * @depends testBasics
	 */
	public function testBuilderAndDialect() {
		// testing query builder
		$this->con->insert()->into('demotable')->values(['name' => 'Blubb'])->getSql($this->con->getDialect());
		$this->con->select()->from('demotable')->where(['id' => new Parameter()])->getSql($this->con->getDialect());
		$this->con->update()->from('demotable')->where(['id' => new Parameter()])->getSql($this->con->getDialect());
		$this->con->delete()->from('demotable')->where(['id' => new Parameter()])->getSql($this->con->getDialect());
		
		// simple select
		$sql = $this->con->select()->from('demotable')->getSql($this->con->getDialect())."\n";
// 		echo $sql."\n";
		$this->assertRegExp('#SELECT.+FROM.+demotable.+#i',$sql,'Invalid SQL returned');
		
		// simple select with alias
		$sql = $this->con->select()->from(['a' => 'demotable'])->getSql($this->con->getDialect())."\n";
// 		echo $sql."\n";
		$this->assertRegExp('#SELECT.+FROM.+demotable.+a.*#i',$sql,'Invalid SQL returned');
		
		$sql = $this->con->select()->from(['a' => ['demotable']])->getSql($this->con->getDialect())."\n";
// 		echo $sql."\n";
		$this->assertRegExp('#SELECT.+FROM.+demotable.+#i',$sql,'Invalid SQL returned');
		
		$sql = $this->con->select()->from(['a' => ['db','demotable']])->getSql($this->con->getDialect())."\n";
// 		echo $sql."\n";
		$this->assertRegExp('#SELECT.+FROM.+demotable.+#i',$sql,'Invalid SQL returned');
		
		$sql = $this->con->select()->from(['a' => ['db','ns','demotable']])->getSql($this->con->getDialect())."\n";
// 		echo $sql."\n";
		$this->assertRegExp('#SELECT.+FROM.+demotable.+#i',$sql,'Invalid SQL returned');

		$sql = $this->con->select()->from('db', NULL,'demotable')->getSql($this->con->getDialect())."\n";
// 		echo $sql."\n";
		$this->assertRegExp('#SELECT.+FROM.+demotable.+#i',$sql,'Invalid SQL returned');
		
		$sql = $this->con->select()->from('db','ns','demotable')->getSql($this->con->getDialect())."\n";
// 		echo $sql."\n";
		$this->assertRegExp('#SELECT.+FROM.+demotable.+#i',$sql,'Invalid SQL returned');
		
		$sql = $this->con->select()->from([
				'a' => $this->con->select()->from('demotable')
		])->getSql($this->con->getDialect())."\n";
// 		echo $sql."\n";
		$this->assertRegExp('#SELECT.+FROM.+demotable.+#i',$sql,'Invalid SQL returned');
		
		// ### Function generator tests
		// MIN
		$sql = $this->con->select('test')->columns(['c' => SQLFunction::min('col')])->getSql($this->con->getDialect())."\n";
// 		echo $sql."\n";
		$this->assertRegExp('#SELECT.+MIN.+col.+FROM.+test.+#i',$sql,'Invalid SQL returned');
		
		$sql = $this->con->select('test')->columns(['c' => SQLFunction::min([['tbl','col']])])->getSql($this->con->getDialect())."\n";
// 		echo $sql."\n";
		$this->assertRegExp('#SELECT.+MIN.+tbl.+col.+FROM.+test.+#i',$sql,'Invalid SQL returned');

		// MAX
		$sql = $this->con->select('test')->columns(['c' => SQLFunction::max('col')])->getSql($this->con->getDialect())."\n";
// 		echo $sql."\n";
		$this->assertRegExp('#SELECT.+MAX.+col.+FROM.+test.+#i',$sql,'Invalid SQL returned');
		
		$sql = $this->con->select('test')->columns(['c' => SQLFunction::max([['tbl','col']])])->getSql($this->con->getDialect())."\n";
// 		echo $sql."\n";
		$this->assertRegExp('#SELECT.+MAX.+tbl.+col.+FROM.+test.+#i',$sql,'Invalid SQL returned');

		// SUM
		$sql = $this->con->select('test')->columns(['c' => SQLFunction::sum('col')])->getSql($this->con->getDialect())."\n";
// 		echo $sql."\n";
		$this->assertRegExp('#SELECT.+SUM.+col.+FROM.+test.+#i',$sql,'Invalid SQL returned');
		
		$sql = $this->con->select('test')->columns(['c' => SQLFunction::sum([['tbl','col']])])->getSql($this->con->getDialect())."\n";
// 		echo $sql."\n";
		$this->assertRegExp('#SELECT.+SUM.+tbl.+col.+FROM.+test.+#i',$sql,'Invalid SQL returned');

		// AVG
		$sql = $this->con->select('test')->columns(['c' => SQLFunction::avg('col')])->getSql($this->con->getDialect())."\n";
// 		echo $sql."\n";
		$this->assertRegExp('#SELECT.+AVG.+col.+FROM.+test.+#i',$sql,'Invalid SQL returned');
		
		$sql = $this->con->select('test')->columns(['c' => SQLFunction::avg([['tbl','col']])])->getSql($this->con->getDialect())."\n";
// 		echo $sql."\n";
		$this->assertRegExp('#SELECT.+AVG.+tbl.+col.+FROM.+test.+#i',$sql,'Invalid SQL returned');
		
		// COUNT
		$sql = $this->con->select('test')->columns(['c' => SQLFunction::count()])->getSql($this->con->getDialect())."\n";
// 		echo $sql."\n";
		$this->assertRegExp('#SELECT.+COUNT.+[*].+FROM.+test.+#i',$sql,'Invalid SQL returned');
		
		$sql = $this->con->select('test')->columns(['c' => SQLFunction::count('col')])->getSql($this->con->getDialect())."\n";
// 		echo $sql."\n";
		$this->assertRegExp('#SELECT.+COUNT.+col.+FROM.+test.+#i',$sql,'Invalid SQL returned');
		
		$sql = $this->con->select('test')->columns(['c' => SQLFunction::count([['tbl','col']])])->getSql($this->con->getDialect())."\n";
// 		echo $sql."\n";
		$this->assertRegExp('#SELECT.+COUNT.+tbl.+col.+FROM.+test.+#i',$sql,'Invalid SQL returned');
		
		// join query tests
		foreach ([
			Select::JOIN_INNER,
			Select::JOIN_LEFT,
			Select::JOIN_RIGHT,
			Select::JOIN_OUTER
		] as $jType) {
			$jCon = new Condition();
			$jCon->equalTo('pid', 'id', iExprValue::TYPE_IDENTIFIER, iExprValue::TYPE_IDENTIFIER);
			$sql = $this->con->select()->from('demotable')->join('table2',$jCon, $jType)->where(['id' => new Parameter()])->getSql($this->con->getDialect());
// 			echo $sql."\n";
			$this->assertRegExp('#SELECT.+FROM.+demotable.*table2.+pid.*=.*id.+WHERE.+id.*=.*#i',$sql,'Invalid SQL returned');
			
			$jCon = new Condition();
			$jCon->equalTo(['a','pid'],['b','id'], iExprValue::TYPE_IDENTIFIER, iExprValue::TYPE_IDENTIFIER);
			$sCon = new Condition();
			$sCon->equalTo(['b','id'],new Parameter());
			$sql = $this->con->select()->from(['a' => 'demotable'])->join(['b' => 'table2'],$jCon, $jType)->where($sCon)->getSql($this->con->getDialect());
// 			echo $sql."\n";
			$this->assertRegExp('#SELECT.+FROM.+demotable.+a.+table2.+b.+pid.*=.*id.+WHERE.+b.*id.#i',$sql,'Invalid SQL returned');
		}
	}

	/**
	 * @depends testConnect
	 */
	public function testCreateDropTable() {
		if($this->con->tableExists('testtable101'))
			$this->con->dropTable('testtable101');
		if($this->con->tableExists('testtable'))
			$this->con->dropTable('testtable');
		
		$info = new TableInfo('testtable');
		$info->addColumn(new ColumnInfo('c1', ColumnInfo::TYPE_INT, -1, array(ColumnInfo::P_AUTONUM)));
		$info->addColumn(new ColumnInfo('c2', ColumnInfo::TYPE_VCHAR, 32));
		$info->addColumn(new ColumnInfo('c3', ColumnInfo::TYPE_DATETIME, -1, array(ColumnInfo::P_ALLOW_NULL)));
		$info->addColumn(new ColumnInfo('c4', ColumnInfo::TYPE_BOOLEAN), -1, array(ColumnInfo::P_ALLOW_NULL));
		$info->addColumn(new ColumnInfo('c5', ColumnInfo::TYPE_VCHAR, 64, array(), $charset = NULL, $collate = NULL, 'DefText'));
		$info->addColumn(new ColumnInfo('c6', ColumnInfo::TYPE_INT, -1));
		$info->setPrimary(array('c1'));
		$info->setIndex('testIdx', ['c2']);
		
		$this->con->createTable($info);
		$this->assertTrue($this->con->tableExists('testtable'), "Table not created");
		$this->con->dropTable('testtable');
		$this->assertFalse($this->con->tableExists($info), "Table should not exist after drop");

		$info2 = new TableInfo('testtable');
		$info2->addColumn(new ColumnInfo('c1', ColumnInfo::TYPE_INT, -1, array(ColumnInfo::P_AUTONUM)));
		$info2->addColumn(new ColumnInfo('c2', ColumnInfo::TYPE_INT, -1));
		$info2->addColumn(new ColumnInfo('c3', ColumnInfo::TYPE_INT, -1));
		$info2->setPrimary(array('c1'));
		$info2->setIndex('testIdx', ['c2']);
		$info2->addCheck('testCheck', Condition::newAndSet()->greaterThan('c2', 'c3',iExprValue::TYPE_IDENTIFIER,iExprValue::TYPE_IDENTIFIER));
		$info2->addCheck('testCheck2', Condition::newAndSet()->greaterThan('c2', 0));
		
		$this->con->createTable($info2);
		$this->assertTrue($this->con->tableExists('testtable'), "Table not created");
		$this->con->dropTable('testtable');
		$this->assertFalse($this->con->tableExists($info2), "Table should not exist after drop");
		
		// recreate table for other tests
		$this->con->createTable($info);
		$this->assertContains('testtable', $this->con->tableList(), "Table not in table list");
		
		$info = new TableInfo('testtable101');
		$info->addColumn(new ColumnInfo('cc1', ColumnInfo::TYPE_INT, -1));
		$this->con->createTable($info);
		
		$this->assertContains('testtable101', $this->con->tableList(), "Table not in table list");
	}

	/**
	 * @depends testCreateDropTable
	 */
	public function testCreateDropViews() {
		$view = new ViewInfo('testview');
		$view->setBase('testtable101');
		$view->addColumn('testtable101', 'cc1');
// 		$view->addColumn('testtable101', 'cc1','cc2');
		
		$this->con->createView($view);
// 		$this->assertTrue($this->con->tableExists('testview'), 'View not found by db class');
		$this->con->dropView($view);
	}
	
	/**
	 * @depends testCreateDropTable
	 */
	public function testGetTableInfo() {
		$info = $this->con->table('testtable')->getInfo();
		$this->assertNotNull($info, "Info is null");
		$this->assertEquals(6, $info->getColumnCount(), "Invalid column count info");
		
		$cinfo = $info->getColumnInfo('c2');
		$this->assertNotNull($cinfo, "ColumnInfo is null");
		$this->assertEquals(ColumnInfo::TYPE_VCHAR, $cinfo->getType(), "Invalid column type info");
		$this->assertFalse($cinfo->isNullAllowed(), "Invalid column null info");
		
		$cinfo = $info->getColumnInfo('c3');
		$this->assertNotNull($cinfo, "ColumnInfo is null");
		$this->assertEquals(ColumnInfo::TYPE_DATETIME, $cinfo->getType(), "Invalid column type info");
		$this->assertTrue($cinfo->isNullAllowed(), "Invalid column null info");
		
		$info = $this->con->table('testtable')->getIndexList();
		$this->assertNotNull($info, "Info is null");
		
		$info = $this->con->table('testtable')->getPKeyFields();
		$this->assertNotNull($info, "Info is null");
		$this->assertContains('c1', $info, "Invalid primary key info");
		
		$info = $this->con->table('testtable')->getFKeyList();
		$this->assertNotNull($info, "Info is null");
		
		$idxName = $this->con->getDialect()->isConstraintNameGlobal() ?	'testtable_testIdx' : 'testIdx';
		
		$info = $this->con->table('testtable')->getIndexList();
		$this->assertNotNull($info, "Info is null");
		$this->assertContains($idxName, $info, "Invalid index info");
		$this->assertTrue($this->con->table('testtable')->isIndexDefined($idxName),'Index could not be detected with method');
		
		$info = $this->con->table('testtable')->getIndexFields($idxName);
		$this->assertNotNull($info, "Info is null");
		$this->assertContains('c2', $info, "Invalid index column info");
	}

	/**
	 * @depends testGetTableInfo
	 */
	public function testXMLDef() {
		$file = new File('./testdata/tabledef.xml');
		$file->open();
		$infos = TableInfo::fromXML($file);
		$file->close();
		
		foreach ($infos as $info) {
			if ($this->con->tableExists($info))
				$this->con->dropTable($info);
			$this->con->createTable($info);
		}
		
		try {
			$info = $this->con->table('demotable1')->getInfo();
			$this->assertEquals(5, $info->getColumnCount(),'Invalid column count');
			$this->assertEquals('col1', $info->getColumnInfo(0)->getName(),'Invalid column name');
		}
		finally {
			$infos = array_reverse($infos);
			foreach ($infos as $info)
				$this->con->dropTable($info);
		}
	}
	
	/**
	 * @depends testCreateDropTable
	 */
	public function testRenameTable() {
		$this->con->renameTable('testtable', 'testtable2');
		$this->assertTrue($this->con->tableExists('testtable2'), "Table not renamed");
		$this->assertFalse($this->con->tableExists('testtable'), "Table not renamed");
		
		$this->con->renameTable('testtable2', 'testtable');
		$this->assertTrue($this->con->tableExists('testtable'), "Table not renamed");
		$this->assertFalse($this->con->tableExists('testtable2'), "Table not renamed");
	}
	
	/**
	 * @depends testCreateDropTable
	 */
	public function testAlterTable() {
		$t = $this->con->table('testtable');
		$t->addColumn(new ColumnInfo('ctemp', ColumnInfo::TYPE_VCHAR, 8, array(ColumnInfo::P_ALLOW_NULL)));

		$info = $t->getInfo();
		$this->assertNotNull($info, "Info is null");
		$this->assertEquals(7, $info->getColumnCount(), "Invalid column count");
		
		$t->setIndex('Ictemp', ['ctemp']);
		$idx = $t->getIndexList();
		$this->assertContains('Ictemp', $idx);
		
		$t->removeIndex('Ictemp');
		$idx = $t->getIndexList();
		$this->assertNotContains('Ictemp', $idx);
		
		$t->removeColumn('ctemp');
		
		$info = $t->getInfo();
		$this->assertNotNull($info, "Info is null");
		$this->assertEquals(6, $info->getColumnCount(), "Invalid column count");
		
		$t2 = $this->con->table('testtable101');
		$t2->addFKey('testfk', ['cc1'], 'testtable', ['c1']);
		$t2->removeFKey('testfk');
	}
	
	/**
	 * @depends testAlterTable
	 */
	public function testModifyPKey() {
		$t = $this->con->table('testtable');
		
		$t->alterColumn('c1', new ColumnInfo('c1', ColumnInfo::TYPE_INT, -1, array()));
		$t->removePKey();
		$t->setPKey(['c1']);
		// SQLServer does not support adding of identities after column creation
		if (!($this->con->getDialect() instanceof \Mammut\DB\Sql\Dialect\SqlServer))
			$t->alterColumn('c1', new ColumnInfo('c1', ColumnInfo::TYPE_INT, -1, array(ColumnInfo::P_AUTONUM)));
	}
	
	/**
	 * @depends testCreateDropTable
	 */
	public function testInsertAndCountData() {
		$this->con->setAutocommit(true);
		$data = array('c2' => 'Testdata1','c3' => new DateTime('2012-10-24'), 'c4' => true, 'c6' => 30);
		$this->con->table('testtable')->insert($data);
		
		$data = array('c2' => 'Testdata2','c3' => new DateTime('2012-10-25'), 'c4' => true, 'c6' => 30);
		$this->con->table('testtable')->insert($data);
		
		$this->assertEquals(2, $this->con->table('testtable')->size(), "Invalid row count");
		
		$data = new stdClass();
		$data->c2 = 'Testdata3';
		$data->c3 = new DateTime('2012-10-26');
		$data->c4 = false;
		$data->c6 = 40;
		$this->con->table('testtable')->insert($data);
		
		$this->assertEquals(3, $this->con->table('testtable')->size(), "Invalid row count");
		
		if($this->con->isTransactionSupported()) {
			$this->con->setAutocommit(false);
			$this->con->startTransaction();
			
			$data = new stdClass();
			$data->c2 = 'Testdata4';
			$data->c3 = new DateTime('2012-10-27');
			$data->c4 = true;
			$data->c6 = 40;
			$this->con->table('testtable')->insert($data);
			
			$this->con->rollback();
			
			$this->assertEquals(3, $this->con->table('testtable')->size(), "Invalid row count");
			$this->con->setAutocommit(true);
		}
	}

	/**
	 * @depends testInsertAndCountData
	 */
	public function testSelectData() {
		$res = $this->con->query("SELECT * FROM testtable ORDER BY c2");
		$this->assertNotNull($res, "No result");
		$this->assertEquals(3, $res->getRowCount(), "Invalid result size");
		
		$row = $res->fetchObject();
		$this->assertTrue(is_object($row), "Invalid result type");
		$this->assertEquals('Testdata1', $row->c2, "Invalid result data");
		$this->assertTrue($row->c3 instanceof DateTime, "Invalid result data type ".gettype($row->c3).' - instance of DateTime expected');
		$this->assertEquals('DefText', $row->c5, "Invalid result data (not default value)");
		
		$row = $res->fetchRow();
		$this->assertTrue(is_array($row), "Invalid result type");
		$this->assertTrue($row[2] instanceof DateTime, "Invalid result data type ".gettype($row[2]).' - instance of DateTime expected');
		 		
		$res->close();
 		 	
		$res = $this->con->table('testtable')->select(array('c2' => 'Testdata2'));
		$this->assertNotNull($res, "No result");
		$this->assertEquals(1, $res->getRowCount(), "Invalid result size");
		
		$row = $res->fetchArray();
		$this->assertEquals('Testdata2', $row['c2'], "Invalid result data");
		$this->assertTrue($row['c3'] instanceof DateTime, "Invalid result data type ".gettype($row['c3']).' - instance of DateTime expected');
		
		$res->close();
	}

	/**
	 * @depends testInsertAndCountData
	 */
	public function testSelectDataWithQueryBuilder() {
		$query = $this->con->select()->from('testtable')->where(array('c2' => 'Testdata2'));
		$res = $this->con->query($query);
		$this->assertNotNull($res, "No result");
		$this->assertEquals(1, $res->getRowCount(), "Invalid result size");
		
		$row = $res->fetchArray();
		$this->assertEquals('Testdata2', $row['c2'], "Invalid result data");
		$this->assertTrue($row['c3'] instanceof DateTime, "Invalid result data type");
		
		$res->close();
		
		$query = $this->con->select()->from('testtable')->where(['c2' => 'Testdata2', 'c4' => true]);
		$query->order(['c1','!c2']);
		$res = $this->con->query($query);
		$this->assertNotNull($res, "No result");
		$this->assertEquals(1, $res->getRowCount(), "Invalid result size");
		
		$row = $res->fetchArray();
		$this->assertEquals('Testdata2', $row['c2'], "Invalid result data");
		$this->assertTrue($row['c3'] instanceof DateTime, "Invalid result data type");
		
		$res->close();
		
		$query2 = $this->con->select()->from('testtable');
		$query = $this->con->select()->from(['q2' => $query2]);
		$res = $this->con->query($query);
		$res->close();
		
		$res = $this->con->getRowList($query);
		$this->assertEquals(3, count($res), "Different row count");
		$res = $this->con->getArrayList($query);
		$this->assertEquals(3, count($res), "Different row count");
		$res = $this->con->getObjectList($query);
		$this->assertEquals(3, count($res), "Different row count");
	}

	/**
	 * @depends testInsertAndCountData
	 */
	public function testPreparedStatement() {
		$stmt = $this->con->prepareStatement("SELECT * FROM testtable WHERE c2=?");
		$stmt->execute(array('Testdata3'));
		$this->assertEquals(1, $stmt->getRowCount(), "Invalid result size");
		
		$row = $stmt->fetchObject();
		$this->assertTrue(is_object($row), "Invalid result type");
		$this->assertEquals('Testdata3', $row->c2, "Invalid result data");
		$this->assertEquals('DefText', $row->c5, "Invalid result data (not default value)");
		$this->assertTrue($row->c3 instanceof DateTime,  "Invalid result data type ".gettype($row->c3).' - instance of DateTime expected');
		
		$stmt->close();
		
		$stmt = $this->con->prepareStatement("SELECT * FROM testtable WHERE c3=?");
		$stmt->execute(array(new DateTime('2012-10-24')));
		$this->assertEquals(1, $stmt->getRowCount(), "Invalid result size");
		$stmt->close();
	}

	/**
	 * @depends testInsertAndCountData
	 */
	public function testPreparedStatementWithQueryBuilder() {
		$query = $this->con->select()->from('testtable')->where(array('c2' => new Parameter()));
		$stmt = $this->con->prepareStatement($query);
		$stmt->execute(array('Testdata3'));
		$this->assertEquals(1, $stmt->getRowCount(), "Invalid result size");
		
		$row = $stmt->fetchObject();
		$this->assertTrue(is_object($row), "Invalid result type");
		$this->assertEquals('Testdata3', $row->c2, "Invalid result data");
		$this->assertEquals('DefText', $row->c5, "Invalid result data (not default value)");
		$this->assertTrue($row->c3 instanceof DateTime, "Invalid result data type");
		
		$stmt->close();
	}

	/**
	 * @depends testSelectData
	 */
	public function testUpdateData() {
		$res = $this->con->table('testtable')->update(array('c2' => 'Testdata21'), array('c2' => 'Testdata2'));
		$this->assertEquals(1, $res, "Invalid result size");
		
		$res = $this->con->table('testtable')->select(array('c3' => new DateTime('2012-10-25')));
		$this->assertNotNull($res, "No result");
		$this->assertEquals(1, $res->getRowCount(), "Invalid result size");
		
		$row = $res->fetchArray();
		$this->assertEquals('Testdata21', $row['c2'], "Invalid result data");
		$this->assertTrue($row['c3'] instanceof DateTime, "Invalid result data type");
		
		$res->close();
	}
	
	/**
	 * @depends testSelectData
	 */
	public function testUpdateDataWithQueryBuilder() {
		$update = $this->con->update();
		$update->from('testtable')->set(['c6' => 20])->where(['c6' => 30]);
		$res = $this->con->query($update);
 		$this->assertEquals(2, $this->con->getAffectedRowCount(), "Invalid update size");
		
		$update = $this->con->update();
		$update->from('testtable')->set(['c6' => Calc::newC('c6')->add(5)])->where(['c6' => 40]);
		$res = $this->con->query($update);
		$this->assertEquals(1, $res, "Invalid update size");
	}
	
	/**
	 * @depends testUpdateData
	 */
	public function testDeleteData() {
		$res = $this->con->table('testtable')->delete(array('c2' => 'Testdata2'));
		$this->assertEquals(0, $res, "Invalid result size");
		
		if($this->con->isTransactionSupported()) {
			$this->con->setAutocommit(false);
			$this->con->startTransaction();
			$res = $this->con->table('testtable')->delete(array('c2' => 'Testdata21'));
			$this->con->rollback();
			
			$this->assertEquals(3, $this->con->table('testtable')->size(), "Invalid row count");
			$this->con->setAutocommit(true);
		}
		
		$res = $this->con->table('testtable')->delete(array('c2' => 'Testdata21'));
		$this->assertEquals(2, $this->con->table('testtable')->size(), "Invalid row count");
	}

	/**
	 * @depends testUpdateData
	 */
	public function testDeleteDataWithQueryBuilder() {
		$query = $this->con->delete()->from('testtable')->where(array('c2' => 'Testdata1'));
		$this->con->query($query);
		$this->assertEquals(1, $this->con->table('testtable')->size(), "Invalid row count");
	}
	
}