/lib/utils/ConstraintHandlerTest.php

<?php
// Call ConstraintHandlerTest::main() if this source file is executed directly.
if (!defined("PHPUnit_MAIN_METHOD")) {
    define("PHPUnit_MAIN_METHOD", "ConstraintHandlerTest::main");
}

require_once "PHPUnit/Framework/TestCase.php";
require_once "PHPUnit/Framework/TestSuite.php";

require_once "testConf.php";
require_once ROOT_PATH."/lib/confs/Conf.php";
require_once 'ConstraintHandler.php';

/**
 * Test class for ConstraintHandler.
 * Generated by PHPUnit_Util_Skeleton on 2007-07-25 at 08:52:16.
 */
class ConstraintHandlerTest extends PHPUnit_Framework_TestCase {
    /**
     * Runs the test methods of this class.
     *
     * @access public
     * @static
     */
    public static function main() {
        require_once "PHPUnit/TextUI/TestRunner.php";

        $suite  = new PHPUnit_Framework_TestSuite("ConstraintHandlerTest");
        $result = PHPUnit_TextUI_TestRunner::run($suite);
    }

    /**
     * Sets up the fixture, for example, open a network connection.
     * This method is called before a test is executed.
     *
     * @access protected
     */
    protected function setUp() {

    	$conf = new Conf();
    	$this->connection = mysql_connect($conf->dbhost.":".$conf->dbport, $conf->dbuser, $conf->dbpass);

    	$this->assertTrue($this->connection !== false);
        $this->assertTrue(mysql_select_db($conf->dbname));

		/* Create tables for testing */
		$result = mysql_query("DROP TABLE IF EXISTS test_emp_dependents");
		$this->assertTrue($result !== false, "Mysql Error: " . mysql_error());

		$result = mysql_query("CREATE TABLE test_emp_dependents (" .
				"emp_number int(7) not null default 0," .
				"ed_seqno decimal(2,0) not null default 0," .
				"ed_name varchar(100) default ''," .
				"primary key (emp_number, ed_seqno)" .
				") engine=innodb default charset=utf8");

		$this->assertTrue($result !== false, "Mysql Error: " . mysql_error());

		$result = mysql_query("DROP TABLE IF EXISTS test_users");
		$this->assertTrue($result !== false, "Mysql Error: " . mysql_error());

		$result = mysql_query("CREATE TABLE test_users (" .
				"id varchar(36) not null default ''," .
				"emp_number int(7) default null," .
				"modified_user_id varchar(36) default null," .
				"created_by varchar(36) default null," .
				"primary key (id)" .
				") engine=innodb default charset=utf8");
		$this->assertTrue($result !== false, "Mysql Error: " . mysql_error());

		$result = mysql_query("DROP TABLE IF EXISTS test_employee");
		$this->assertTrue($result !== false, "Mysql Error: " . mysql_error());

		$result = mysql_query("CREATE TABLE test_employee(" .
				"emp_number int(7) not null default 0, " .
				"emp_firstname varchar(100) default '' not null," .
				"job_title_code varchar(13) default null," .
				"work_station int(6) default null," .
				"nation_code varchar(13) default null," .
				"primary key (emp_number)" .
				") engine=innodb default charset=utf8");

		$this->assertTrue($result !== false, "Mysql Error: " . mysql_error());

		$result = mysql_query("DROP TABLE IF EXISTS test_job_title");
		$this->assertTrue($result !== false, "Mysql Error: " . mysql_error());

		$result = mysql_query("CREATE TABLE test_job_title (" .
				"jobtit_code varchar(13) not null default ''," .
				"jobtit_name varchar(50) default null," .
				"jobtit_desc varchar(200) default null," .
				"jobtit_comm varchar(400) default null," .
				"sal_grd_code varchar(13) default null," .
				"primary key(jobtit_code)" .
				") engine=innodb default charset=utf8;");

		$this->assertTrue($result !== false, "Mysql Error: " . mysql_error());

		$result = mysql_query("DROP TABLE IF EXISTS test_nationality");
		$this->assertTrue($result !== false, "Mysql Error: " . mysql_error());

		$result =  mysql_query("create table `test_nationality` (" .
				"  `nat_code` varchar(13) not null default ''," .
				"  `nat_name` varchar(120) default null," .
				"  primary key  (`nat_code`)" .
				") engine=innodb default charset=utf8");

		$this->assertTrue($result !== false, "Mysql Error: " . mysql_error());
    }

    /**
     * Tears down the fixture, for example, close a network connection.
     * This method is called after a test is executed.
     *
     * @access protected
     */
    protected function tearDown() {
		$this->assertTrue(mysql_query("DROP TABLE IF EXISTS test_emp_dependents"), "Error: " . mysql_error());
		$this->assertTrue($result = mysql_query("DROP TABLE IF EXISTS test_users"), "Error: " . mysql_error());
		$this->assertTrue(mysql_query("DROP TABLE IF EXISTS test_employee"), "Error: " . mysql_error());
		$this->assertTrue(mysql_query("DROP TABLE IF EXISTS test_job_title"), "Error: " . mysql_error());
		$this->assertTrue(mysql_query("DROP TABLE IF EXISTS test_nationality"), "Error: " . mysql_error());
    }

    /**
     * Test method applyConstraints() with simple on delete set null constraint
     */
    public function testSimpleSetNullConstraint() {

    	$fkConstraints = array(array("test_employee", array("nation_code"), "test_nationality", array("nat_code"), "null"));

		$this->assertTrue(mysql_query("INSERT INTO test_nationality(nat_code, nat_name) VALUES(1, 'Sri Lankan')"));
		//$this->assertTrue(mysql_query("INSERT INTO test_nationality(nat_code, nat_name) VALUES(2, 'Indian')"));

		$this->assertTrue(mysql_query("INSERT INTO test_employee(emp_number,emp_firstname, nation_code) VALUES(1, 'John', 2), " .
				"(2, 'Ruwan', 1), (3, 'Kamal', 2)"));

    	$constraintHandler = new ConstraintHandler();
    	$failed = $constraintHandler->applyConstraints($fkConstraints);
		$this->assertEquals(0, count($failed));

    	$this->assertEquals(3, $this->_countRows("test_employee"), "Shouldn't delete employees");
    	$this->assertEquals(1, $this->_countRows("test_nationality"), "Shouldn't delete nationality");

    	/* Verify that nation_code is null for two employees */
    	$this->assertEquals(2, $this->_countRows("test_employee", "nation_code IS NULL"));
    	$employees = $this->_selectWhere("test_employee", "nation_code IS NULL");
    	$this->assertEquals(2, count($employees));

		/* Test that the correct employees had nation_code set to null */
		foreach ($employees as $employee) {
			$empNumber = intval($employee['emp_number']);
			if (($empNumber != 1) && ($empNumber != 3)) {
				$this->fail("nation_code set null for invalid employee: $empNumber");
			}
		}

		/* Check the foreign key constraints have been set */
		$this->assertEquals(0, count($constraintHandler->getMissingConstraints($fkConstraints)));
    }

    /**
     * Test method applyConstraints() with simple on delete cascade constraint
     */
    public function testSimpleCascadeConstraint() {

		$this->assertTrue(mysql_query("INSERT INTO test_employee(emp_number,emp_firstname) VALUES(1, 'John'), " .
				"(2, 'Ruwan'), (3, 'Kamal')"), "Mysql Error: ". mysql_error());

		$this->assertTrue(mysql_query("INSERT INTO test_emp_dependents(emp_number, ed_seqno, ed_name) " .
				"VALUES(1, 1, 'Ruwan'), (1, 2, 'Kamani'), (3, 1, 'Sena'), (4, 1, 'Ravi'), (4, 2, 'Janesh'), " .
				"(5, 1, 'Wimal')"), "Mysql Error: ". mysql_error());

		$fkConstraints = array(array("test_emp_dependents", array("emp_number"), "test_employee", array("emp_number"), "cascade"));
    	$constraintHandler = new ConstraintHandler();
    	$failed = $constraintHandler->applyConstraints($fkConstraints);

		$this->assertEquals(0, count($failed));

    	$this->assertEquals(3, $this->_countRows("test_employee"), "Shouldn't delete employees");
    	$this->assertEquals(3, $this->_countRows("test_emp_dependents"));
    	$this->assertEquals(0, $this->_countRows("test_emp_dependents", "emp_number = 4"));
    	$this->assertEquals(0, $this->_countRows("test_emp_dependents", "emp_number = 5"));
    	$this->assertEquals(2, $this->_countRows("test_emp_dependents", "emp_number = 1"));
    	$this->assertEquals(1, $this->_countRows("test_emp_dependents", "emp_number = 3"));

		/* Check the foreign key constraints have been set */
		$this->assertEquals(0, count($constraintHandler->getMissingConstraints($fkConstraints)));
    }

    /**
     * Test method applyConstraints() with simple on delete set null constraint
     */
    public function testSimpleSetNullWithNoParents() {

    	$fkConstraints = array(array("test_employee", array("nation_code"), "test_nationality", array("nat_code"), "null"));

		$this->assertTrue(mysql_query("INSERT INTO test_employee(emp_number,emp_firstname, nation_code) VALUES(1, 'John', 2), " .
				"(2, 'Ruwan', 1), (3, 'Kamal', 2)"));

    	$constraintHandler = new ConstraintHandler();
    	$failed = $constraintHandler->applyConstraints($fkConstraints);
		$this->assertEquals(0, count($failed));

    	$this->assertEquals(3, $this->_countRows("test_employee"), "Shouldn't delete employees");
    	$this->assertEquals(0, $this->_countRows("test_nationality"), "Shouldn't change nationality");

    	/* Verify that nation_code is null for all employees */
    	$this->assertEquals(3, $this->_countRows("test_employee", "nation_code IS NULL"));

		/* Check the foreign key constraints have been set */
		$this->assertEquals(0, count($constraintHandler->getMissingConstraints($fkConstraints)));
    }

    /**
     * Test method applyConstraints() with simple on delete cascade constraint with no entries in
     * the parent table.
     */
    public function testSimpleCascadeConstraintWithNoParents() {

		$this->assertTrue(mysql_query("INSERT INTO test_emp_dependents(emp_number, ed_seqno, ed_name) " .
				"VALUES(1, 1, 'Ruwan'), (1, 2, 'Kamani'), (3, 1, 'Sena'), (4, 1, 'Ravi'), (4, 2, 'Janesh'), " .
				"(5, 1, 'Wimal')"), "Mysql Error: ". mysql_error());

		$fkConstraints = array(array("test_emp_dependents", array("emp_number"), "test_employee", array("emp_number"), "cascade"));
    	$constraintHandler = new ConstraintHandler();
    	$failed = $constraintHandler->applyConstraints($fkConstraints);

		$this->assertEquals(0, count($failed));

    	$this->assertEquals(0, $this->_countRows("test_employee"), "Shouldn't delete employees");
    	$this->assertEquals(0, $this->_countRows("test_emp_dependents"));

		/* Check the foreign key constraints have been set */
		$this->assertEquals(0, count($constraintHandler->getMissingConstraints($fkConstraints)));
    }

	/**
	 * Test getConstraintSQL method.
	 */
	public function testGetConstraintSQL() {

		$constraintHandler = new ConstraintHandler();

		$expected = "ALTER TABLE hs_hr_emp_dependents ADD CONSTRAINT FOREIGN KEY (emp_number) " .
					"REFERENCES hs_hr_employee(emp_number) ON DELETE CASCADE";
		$constraint = array("hs_hr_emp_dependents", array("emp_number"), "hs_hr_employee", array("emp_number"),
							"cascade");

		$this->assertEquals($expected, $constraintHandler->getConstraintSQL($constraint));

		$expected = "ALTER TABLE hs_hr_leave ADD CONSTRAINT FOREIGN KEY (leave_request_id,leave_type_id,employee_id)" .
				    " REFERENCES hs_hr_leave_requests(leave_request_id,leave_type_id,employee_id) ON DELETE CASCADE";
		$constraint = array("hs_hr_leave", array("leave_request_id","leave_type_id","employee_id"), "hs_hr_leave_requests",
					  array("leave_request_id","leave_type_id","employee_id"), "cascade");

		$constraintHandler = new ConstraintHandler();
		$this->assertEquals($expected, $constraintHandler->getConstraintSQL($constraint));

	}

	/**
	 * Checks that all constraints in the database are set.
	 */
	public function testAllConstraintsSet() {

		$constraintHandler = new ConstraintHandler();
		require ROOT_PATH.'/dbscript/constraints.php';

		$failed = $constraintHandler->getMissingConstraints($fkConstraints);

		$numFailed = count($failed);
		if ($numFailed > 0) {
			foreach($failed as $constraint) {
				$failedList[] = $constraintHandler->getConstraintSQL($constraint);
			}

			$message = "Following $numFailed constraint(s) were missing: " . implode("\n,",$failedList);
			$this->fail($message);
		}
	}

	/**
	 * Tests the method getMissingConstraints()
	 */
	public function testCheckConstraintsSet() {
		$constraintHandler = new ConstraintHandler();

		$fkConstraints = array(array("test_emp_dependents", array("emp_number"), "test_employee", array("emp_number"), "cascade"));
		$this->assertEquals(1, count($constraintHandler->getMissingConstraints($fkConstraints)));

		$this->assertTrue(mysql_query("ALTER TABLE test_emp_dependents add constraint foreign key (emp_number) " .
                          "references test_employee(emp_number) on delete cascade"));

		$this->assertEquals(0, count($constraintHandler->getMissingConstraints($fkConstraints)));
	}

	/**
	 * Tests the regexp used in getMissingConstraints().
	 */
	public function testCheckRegExp() {
		$string = "  CONSTRAINT `test_emp_dependents_ibfk_1` FOREIGN KEY (`emp_number`) REFERENCES `test_employee` (`emp_number`) ON DELETE CASCADE";
		$regexp = "/\w*CONSTRAINT\b.*\bFOREIGN\s+KEY[\s(`]+emp_number[\s)`]+REFERENCES[\s`)]+test_employee[\s`(]+emp_number[\s`)]+ON\s+DELETE.*/";
		$count = preg_match($regexp, $string);
		$this->assertEquals(1, $count);
	}

	/**
	 * Convenience method that does a select from a table with given where
	 * condition.
	 *
	 * @param string $table Table name
	 * @param string $where Where clause
	 *
	 * @return array results as an array (MYSQL_BOTH) from the database
	 */
    private function _selectWhere($table, $where) {

    	$sql = "SELECT * FROM $table WHERE $where";

    	$result = mysql_query($sql);
		$this->assertTrue($result !== false, "Query failed: $sql");

		$data = array();
		while ($row = mysql_fetch_array($result)) {
			$data[] = $row;
		}

    	return $data;
    }

    /**
     * Counts rows in table admins (with optional condition)
     *
     * @param  string $table table name
     * @param  string $where where clause
     * @return int number of rows
     */
    private function _countRows($table, $where = null) {

        $sql = "SELECT COUNT(*) FROM $table";
        if (!empty($where)) {
                $sql .= " WHERE " . $where;
        }
        $result = mysql_query($sql);
		$this->assertTrue($result !== false, "Query failed: $sql");
        $row = mysql_fetch_array($result, MYSQL_NUM);
    	$this->assertTrue(is_array($row));
        $count = $row[0];
        return $count;
    }

}

// Call ConstraintHandlerTest::main() if this source file is executed directly.
if (PHPUnit_MAIN_METHOD == "ConstraintHandlerTest::main") {
    ConstraintHandlerTest::main();
}
?>