DatabaseTest.php

<?php
/**
 * @author Rafael Ostertag <rafael.ostertag@math.uzh.ch>
 */

require_once(__DIR__ . '/AbstractDatabaseTest.php');
require_once(__DIR__ . '/../../qfq/Database.php');
require_once(__DIR__ . '/../../qfq/exceptions/DbException.php');

class DatabaseTest extends AbstractDatabaseTest {

    /**
     * @throws \qfq\DbException
     */
    public function testFetchAll() {
        $allRows = $this->db->sql('SELECT * FROM Person ORDER BY id LIMIT 2');

        $this->assertCount(2, $allRows);
        $this->assertEquals(1, $allRows[0]['id']);
        $this->assertEquals(2, $allRows[1]['id']);
    }

    /**
     * @throws \qfq\DbException
     */
    public function testFetchAllEmpty() {
        $allRows = $this->db->sql('SELECT * FROM Person WHERE id=0 ORDER BY id');

        $this->assertEquals(array(), $allRows);
    }

    /**
     * @throws \qfq\DbException
     */
    public function testQuerySimple() {

        $expected = [
            [
                'id' => '1',
                'name' => 'Doe',
                'firstname' => 'John',
                'gender' => 'male',
                'groups' => 'c'
            ],
            [
                'id' => '2',
                'name' => 'Smith',
                'firstname' => 'Jane',
                'gender' => 'female',
                'groups' => 'a,c'
            ],
        ];


        // Check read rows
        $dataArray = $this->db->sql('SELECT * FROM Person ORDER BY id LIMIT 1');
        // Check count
        $this->assertEquals(1, $this->store->getVar(SYSTEM_SQL_COUNT, STORE_SYSTEM));

        // Check read rows
        $dataArray = $this->db->sql('SELECT * FROM Person ORDER BY id LIMIT 2');

        // Check count
        $this->assertEquals(2, $this->store->getVar(SYSTEM_SQL_COUNT, STORE_SYSTEM));
        // Compare rows
        $this->assertEquals($expected, $dataArray);
        // Check rows

        // Same as above, but specify 'ROW_REGULAR'
        $dataArray = $this->db->sql('SELECT * FROM Person ORDER BY id LIMIT 2', ROW_REGULAR);
        $this->assertEquals(2, $this->store->getVar(SYSTEM_SQL_COUNT, STORE_SYSTEM));
        // Check rows
        $this->assertEquals($expected, $dataArray);

        // ROW_EXPECT_0
        $dataArray = $this->db->sql('SELECT * FROM Person WHERE id=0', ROW_EXPECT_0);
        $this->assertEquals(0, $this->store->getVar(SYSTEM_SQL_COUNT, STORE_SYSTEM));
        // Check rows
        $this->assertEquals(array(), $dataArray);

        // ROW_EXPECT_1
        $dataArray = $this->db->sql('SELECT * FROM Person WHERE id=1', ROW_EXPECT_1);
        $this->assertEquals(1, $this->store->getVar(SYSTEM_SQL_COUNT, STORE_SYSTEM));
        // Check rows
        $this->assertEquals($expected[0], $dataArray);

        // ROW_EXPECT_0_1: 1
        $dataArray = $this->db->sql('SELECT * FROM Person WHERE id=1', ROW_EXPECT_0_1);
        $this->assertEquals(1, $this->store->getVar(SYSTEM_SQL_COUNT, STORE_SYSTEM));
        // Check rows
        $this->assertEquals($expected[0], $dataArray);

        // ROW_EXPECT_0_1: 0
        $dataArray = $this->db->sql('SELECT * FROM Person WHERE id=0', ROW_EXPECT_0_1);
        $this->assertEquals(0, $this->store->getVar(SYSTEM_SQL_COUNT, STORE_SYSTEM));
        // Check rows
        $this->assertEquals(array(), $dataArray);

        //ROW_EXPECT_GE_1 - one record
        $dataArray = $this->db->sql('SELECT * FROM Person WHERE id=1', ROW_EXPECT_GE_1);
        $this->assertEquals(1, $this->store->getVar(SYSTEM_SQL_COUNT, STORE_SYSTEM));
        // Check rows
        $this->assertEquals([$expected[0]], $dataArray);

        // ROW_EXPECT_GE_1 - two records
        $dataArray = $this->db->sql('SELECT * FROM Person ORDER BY id LIMIT 2', ROW_EXPECT_GE_1);
        $this->assertEquals(2, $this->store->getVar(SYSTEM_SQL_COUNT, STORE_SYSTEM));
        // Check rows
        $this->assertEquals($expected, $dataArray);

        // Check Implode: 2 records
        $data = $this->db->sql('SELECT * FROM Person ORDER BY id LIMIT 2', ROW_IMPLODE_ALL);
        $this->assertEquals(implode($expected[0]) . implode($expected[1]), $data);

        // Check Implode: 1 record
        $data = $this->db->sql('SELECT * FROM Person ORDER BY id LIMIT 1', ROW_IMPLODE_ALL);
        $this->assertEquals(implode($expected[0]), $data);

        // Check Implode 0 record
        $data = $this->db->sql('SELECT * FROM Person WHERE id=0 ORDER BY id LIMIT 2', ROW_IMPLODE_ALL);
        $this->assertEquals('', $data);

        $data = $this->db->sql('SELECT "a", "b", "c"', ROW_IMPLODE_ALL);
        $this->assertEquals('abc', $data);

        // Multiple columns with same columnname
        $data = $this->db->sql('SELECT "a", "b", "a"', ROW_IMPLODE_ALL);
        $this->assertEquals('aba', $data);

        // Multiple columns with same columnname
        $data = $this->db->sql('SELECT id, name, firstname, name, id FROM Person', ROW_IMPLODE_ALL);
        $this->assertEquals('1DoeJohnDoe12SmithJaneSmith2', $data);

    }

    /**
     * @throws \qfq\DbException
     */
    public function testQuerySimpleParameter() {
        $stat = array();
        $dummy = array();

        // Parameter Susbstitution by '?'
        $dataArray = $this->db->sql('SELECT * FROM Person WHERE name LIKE ? ORDER BY id', ROW_REGULAR, ['Smith']);
        // Check count
        $this->assertEquals(1, $this->store->getVar(SYSTEM_SQL_COUNT, STORE_SYSTEM));

        $dataArray = $this->db->sql('UPDATE Person SET groups = ?', ROW_REGULAR, ['a,b,c']);
        $this->assertEquals(2, $this->store->getVar(SYSTEM_SQL_COUNT, STORE_SYSTEM));

        $dataArray = $this->db->sql('INSERT INTO Person (`name`, `firstname`, `groups`) VALUES ( ?, ? ,? )', ROW_REGULAR, ['Meier', 'John', 'a'], 'fake', $dummy, $stat);
        $this->assertEquals(1, $this->store->getVar(SYSTEM_SQL_COUNT, STORE_SYSTEM));
        $this->assertEquals(3, $stat[DB_INSERT_ID]);

        $dataArray = $this->db->sql('INSERT INTO Person (`name`, `firstname`, `groups`) VALUES ( ?, ? ,? )', ROW_REGULAR, ['Meier', 'Jan', 'b'], 'fake', $dummy, $stat);
        $this->assertEquals(1, $this->store->getVar(SYSTEM_SQL_COUNT, STORE_SYSTEM));
        $this->assertEquals(4, $stat[DB_INSERT_ID]);

        $dataArray = $this->db->sql('DELETE FROM Person WHERE name = ?', ROW_REGULAR, ['Meier']);
        $this->assertEquals(2, $this->store->getVar(SYSTEM_SQL_COUNT, STORE_SYSTEM));

    }

    /**
     * @expectedException \qfq\DbException
     */
    public function testSqlExceptionExpect0() {
        $data = $this->db->sql('SELECT * FROM Person ORDER BY id LIMIT 1', ROW_EXPECT_0);
    }

    /**
     * @expectedException \qfq\DbException
     */
    public function testSqlExceptionExpect1_0() {
        $data = $this->db->sql('SELECT * FROM Person ORDER BY id LIMIT 0', ROW_EXPECT_1);
    }

    /**
     * @expectedException \qfq\DbException
     */
    public function testSqlExceptionExpect1_2() {
        $data = $this->db->sql('SELECT * FROM Person ORDER BY id ', ROW_EXPECT_1);
    }

    /**
     * @expectedException \qfq\DbException
     */
    public function testSqlExceptionExpect01() {
        $data = $this->db->sql('SELECT * FROM Person ORDER BY id ', ROW_EXPECT_0_1);
    }

    /**
     * @expectedException \qfq\DbException
     */
    public function testSqlExceptionExpectGE1() {
        $data = $this->db->sql('SELECT * FROM Person ORDER BY id LIMIT 0', ROW_EXPECT_GE_1);
    }

    /**
     * @expectedException \qfq\DbException
     */
    public function testSanitizeException() {
        $this->db->sql('some garbage');
    }

    /**
     * @throws \qfq\DbException
     */
    public function testParameters() {
        //TODO: ParameterName
//        $this->db->prepareExecute('SELECT * FROM Person WHERE id = :valueOfA', [':valueOfA' => 2]);
//        $this->assertEquals(1, $this->db->getRowCount());
    }

    /**
     *
     */
    public function testGetSetValueList() {
        $valueList = $this->db->getEnumSetValueList('Person', 'gender');
        $expected = [0 => '', 1 => 'male', 2 => 'female'];
        $this->assertEquals($expected, $valueList);

        $expected = [0 => '', 1 => 'a', 2 => 'b', 3 => 'c'];
        $valueList = $this->db->getEnumSetValueList('Person', 'groups');
        $this->assertEquals($expected, $valueList);
    }

    /**
     * @throws \qfq\DbException
     */
    public function testSelectReturn() {
        $dummy = array();
        $stat = array();

        $sql = "SELECT name FROM Person ORDER BY id";

        $rc = $this->db->sql($sql, ROW_REGULAR, $dummy, 'fake', $dummy, $stat);
        $this->assertEquals([0 => ['name' => 'Doe'], 1 => ['name' => 'Smith']], $rc);

        // DB_NUM_ROWS | DB_INSERT_ID | DB_AFFECTED_ROWS
        $this->assertEquals(2, $stat[DB_NUM_ROWS]);
    }

    /**
     * @throws \qfq\DbException
     */
    public function testShowReturn() {
        $dummy = array();
        $stat = array();

        $sql = "SHOW TABLES";

        $rc = $this->db->sql($sql, ROW_REGULAR, $dummy, 'fake', $dummy, $stat);

        // DB_NUM_ROWS | DB_INSERT_ID | DB_AFFECTED_ROWS
        $this->assertEquals(6, $stat[DB_NUM_ROWS]);
    }

    /**
     * @throws \qfq\DbException
     */
    public function testExplainReturn() {
        $dummy = array();
        $stat = array();

        $sql = "EXPLAIN Person";

        $rc = $this->db->sql($sql, ROW_REGULAR, $dummy, 'fake', $dummy, $stat);

        // DB_NUM_ROWS | DB_INSERT_ID | DB_AFFECTED_ROWS
        $this->assertEquals(5, $stat[DB_NUM_ROWS]);
    }

    /**
     * @throws \qfq\DbException
     */
    public function testInsertReturn() {
        $dummy = array();
        $stat = array();

        $sql = "INSERT INTO Person (id, name, firstname, gender, groups) VALUES (NULL, 'Doe', 'Jonni', 'male','')";

        $rc = $this->db->sql($sql, ROW_REGULAR, $dummy, 'fake', $dummy, $stat);
        // DB_NUM_ROWS | DB_INSERT_ID | DB_AFFECTED_ROWS
        $this->assertEquals(3, $stat[DB_INSERT_ID]);
        $this->assertEquals(1, $stat[DB_AFFECTED_ROWS]);

        $this->assertEquals(3, $rc);
    }

    /**
     * @throws \qfq\DbException
     */
    public function testUpdateReturn() {
        $dummy = array();
        $stat = array();

        $sql = "UPDATE Person SET groups = 'a'";

        $rc = $this->db->sql($sql, ROW_REGULAR, $dummy, 'fake', $dummy, $stat);

        $this->assertEquals(2, $rc);

        // DB_NUM_ROWS | DB_INSERT_ID | DB_AFFECTED_ROWS
        $this->assertEquals(2, $stat[DB_AFFECTED_ROWS]);
    }

    /**
     * @throws \qfq\DbException
     */
    public function testDeleteReturn() {
        $dummy = array();
        $stat = array();

        $this->executeSQLFile(__DIR__ . '/fixtures/Generic.sql', true);

        $sql = "DELETE FROM Person WHERE id=2";

        $rc = $this->db->sql($sql, ROW_REGULAR, $dummy, 'fake', $dummy, $stat);

        $this->assertEquals(1, $rc);

        // DB_NUM_ROWS | DB_INSERT_ID | DB_AFFECTED_ROWS
        $this->assertEquals(1, $stat[DB_AFFECTED_ROWS]);

    }


    /**
     * @throws \qfq\DbException
     */
    public function testReplaceReturn() {
        $dummy = array();
        $stat = array();

        $this->executeSQLFile(__DIR__ . '/fixtures/Generic.sql', true);

        $sql = "REPLACE INTO Person (id, name, firstname, gender, groups) VALUES (1, 'Doe', 'John', 'male','')";

        $rc = $this->db->sql($sql, ROW_REGULAR, $dummy, 'fake', $dummy, $stat);
        // DB_NUM_ROWS | DB_INSERT_ID | DB_AFFECTED_ROWS
        $this->assertEquals(1, $stat[DB_INSERT_ID]);
        $this->assertEquals(2, $stat[DB_AFFECTED_ROWS]);

        $this->assertEquals(1, $rc);


        $sql = "REPLACE INTO Person (id, name, firstname, gender, groups) VALUES (100, 'Lincoln', 'Abraham', 'male','')";

        $rc = $this->db->sql($sql, ROW_REGULAR, $dummy, 'fake', $dummy, $stat);
        // DB_NUM_ROWS | DB_INSERT_ID | DB_AFFECTED_ROWS
        $this->assertEquals(100, $stat[DB_INSERT_ID]);
        $this->assertEquals(1, $stat[DB_AFFECTED_ROWS]);

        $this->assertEquals(100, $rc);
    }

    /**
     *
     */
    public function testGetTableDefinition() {
        $expected = [
            ['Field' => 'id', 'Type' => 'bigint(20)', 'Null' => 'NO', 'Key' => 'PRI', 'Default' => '', 'Extra' => 'auto_increment'],
            ['Field' => 'name', 'Type' => 'varchar(128)', 'Null' => 'YES', 'Key' => '', 'Default' => '', 'Extra' => ''],
            ['Field' => 'firstname', 'Type' => 'varchar(128)', 'Null' => 'YES', 'Key' => '', 'Default' => '', 'Extra' => ''],
            ['Field' => 'gender', 'Type' => "enum('','male','female')", 'Null' => 'NO', 'Key' => '', 'Default' => 'male', 'Extra' => ''],
            ['Field' => 'groups', 'Type' => "set('','a','b','c')", 'Null' => 'NO', 'Key' => '', 'Default' => '', 'Extra' => ''],
        ];

        $this->assertEquals($expected, $this->db->getTableDefinition('Person'));
    }


    public function testSqlKeys() {
        $keys = array();

        $expected = [
            ['0' => '1', '1' => 'Doe', '2' => '0'],
            ['0' => '2', '1' => 'Smith', '2' => '0'],
        ];
        // Same as above, but specify 'ROW_REGULAR'
        $stat = array();
        $dataArray = $this->db->sqlKeys('SELECT id AS "id", name, "0" AS "id" FROM Person ORDER BY id LIMIT 3', $keys, $stat);

        // Check rows
        $this->assertEquals($expected, $dataArray);

        // Check keys
        $this->assertEquals(['id', 'name', 'id'], $keys);
    }

    /**
     * @throws Exception
     */
    protected function setUp() {
        parent::setUp();

        $this->executeSQLFile(__DIR__ . '/fixtures/Generic.sql', true);
    }
}