DatabaseTest.php 7 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
<?php
/**
 * @author Rafael Ostertag <rafael.ostertag@math.uzh.ch>
 */

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

class DatabaseTest extends AbstractDatabaseTest {

11
12
13
    /**
     * @throws \qfq\exceptions\DbException
     */
14
15
    public function testExecute() {

16
        $this->db->prepareExecute('SELECT * FROM Person');
17

18
        $this->assertEquals(2, $this->db->rowCount());
19
20
    }

21
22
23
    /**
     * @throws \qfq\exceptions\DbException
     */
24
    public function testFetchOneRowAssocEmptyResult() {
25
        $this->db->prepareExecute('SELECT * FROM Person WHERE id=0', []);
26

27
        $this->assertEquals(array(), $this->db->fetchOne());
28
29
    }

30
31
32
    /**
     * @throws \qfq\exceptions\DbException
     */
33
    public function testFetchOne() {
34
        $this->db->prepareExecute('SELECT * FROM Person');
35

36
        $oneRow = $this->db->fetchOne();
37
38
39
40
41
42
43
44
45

        $this->assertArrayHasKey('name', $oneRow);
        $this->assertArrayHasKey('firstname', $oneRow);
        $this->assertArrayHasKey('gender', $oneRow);

        $this->assertEquals('Doe', $oneRow['name']);
        $this->assertEquals('John', $oneRow['firstname']);
    }

46
47
48
    /**
     * @throws \qfq\exceptions\DbException
     */
49
    public function testFetchAll() {
50
        $this->db->prepareExecute('SELECT * FROM Person ORDER BY id LIMIT 2');
51

52
        $allRows = $this->db->fetchAll();
53
54
55
56
57
58

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

59
60
61
    /**
     * @throws \qfq\exceptions\DbException
     */
62
    public function testFetchAllEmpty() {
63
        $this->db->prepareExecute('SELECT * FROM Person WHERE id=0 ORDER BY id');
64

65
        $this->assertEquals(array(), $this->db->fetchAll());
66
67
    }

68
69
70
    /**
     * @throws \qfq\exceptions\DbException
     */
71
72
73
74
75
76
77
    public function testQuerySimple() {

        $expected = [
            [
                'id' => '1',
                'name' => 'Doe',
                'firstname' => 'John',
78
79
                'gender' => 'male',
                'groups' => 'c'
80
81
82
83
84
            ],
            [
                'id' => '2',
                'name' => 'Smith',
                'firstname' => 'Jane',
85
86
                'gender' => 'female',
                'groups' => 'a,c'
87
88
89
90
            ],
        ];

        // Check read rows
91
        $dataArray = $this->db->sql('SELECT * FROM Person ORDER BY id LIMIT 2');
92
        // Check count
93
        $this->assertEquals(2, $this->db->rowCount());
94
95
96
97
98
99
        $this->assertEquals(2, $this->store->getVar(SYSTEM_SQL_COUNT, STORE_SYSTEM));
        // Compare rows
        $this->assertSame($expected, $dataArray);
        // Check rows

        // Same as above, but specify 'ROW_REGULAR'
100
101
        $dataArray = $this->db->sql('SELECT * FROM Person ORDER BY id LIMIT 2', ROW_REGULAR);
        $this->assertEquals(2, $this->db->rowCount());
102
103
104
105
        // Check rows
        $this->assertSame($expected, $dataArray);

        // ROW_EXACT_1
106
107
        $dataArray = $this->db->sql('SELECT * FROM Person WHERE id=1', ROW_EXACT_1);
        $this->assertEquals(1, $this->db->rowCount());
108
109
110
111
        // Check rows
        $this->assertSame($expected[0], $dataArray);

        // ROW_EMPTY_IS_OK - one record
112
113
        $dataArray = $this->db->sql('SELECT * FROM Person WHERE id=1', ROW_EMPTY_IS_OK);
        $this->assertEquals(1, $this->db->rowCount());
114
115
116
117
        // Check rows
        $this->assertSame($expected[0], $dataArray);

        // ROW_EMPTY_IS_OK - no record
118
119
        $dataArray = $this->db->sql('SELECT * FROM Person WHERE id=0', ROW_EMPTY_IS_OK);
        $this->assertEquals(0, $this->db->rowCount());
120
121
122
123
        // Check rows
        $this->assertSame(array(), $dataArray);

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

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

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

136
137
138
    /**
     * @throws \qfq\exceptions\DbException
     */
Carsten  Rose's avatar
Carsten Rose committed
139
140
    public function testQuerySimpleParameter() {
        // Parameter Susbstitution by '?'
141
        $dataArray = $this->db->sql('SELECT * FROM Person WHERE name LIKE ? ORDER BY id', ROW_REGULAR, ['Smith']);
Carsten  Rose's avatar
Carsten Rose committed
142
        // Check count
143
        $this->assertEquals(1, $this->db->rowCount());
Carsten  Rose's avatar
Carsten Rose committed
144
145
146


        // Parameter Susbstitution by name
147
        $dataArray = $this->db->sql('SELECT * FROM Person WHERE name LIKE :valueOfName ORDER BY id ', ROW_REGULAR, [':valueOfName' => 'Smith']);
Carsten  Rose's avatar
Carsten Rose committed
148
        // Check count
149
        $this->assertEquals(1, $this->db->rowCount());
Carsten  Rose's avatar
Carsten Rose committed
150
151
152

    }

153
154
155
156
157
    /**
     * @expectedException \qfq\exceptions\DbException
     *
     */
    public function testSanatizeException() {
158
        $this->db->sql('some garbage');
159
160
    }

161
162
163
    /**
     * @throws \qfq\exceptions\DbException
     */
164
    public function testParameters() {
165
        $this->db->prepareExecute('SELECT * FROM Person WHERE id = :valueOfA', [':valueOfA' => 2]);
166

167
        $this->assertEquals(1, $this->db->rowCount());
168
169
    }

170
171
172
    /**
     *
     */
173
    public function testGetSetValueList() {
174
        $valueList = $this->db->getEnumSetValueList('Person', 'gender');
175
176
177
178
        $expected = [0 => '', 1 => 'male', 2 => 'female'];
        $this->assertEquals($expected, $valueList);

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

183
184
185
    /**
     * @throws \qfq\exceptions\DbException
     */
186
187
    public function testGetLastInsertId() {

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

190
191
        $this->db->prepareExecute($sql);
        $this->assertEquals(3, $this->db->getLastInsertId());
192
193
    }

194
195
196
197
198
199
200
201
    /**
     *
     */
    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' => ''],
202
            ['Field' => 'gender', 'Type' => "enum('','male','female')", 'Null' => 'NO', 'Key' => '', 'Default' => 'male', 'Extra' => ''],
203
204
205
            ['Field' => 'groups', 'Type' => "set('','a','b','c')", 'Null' => 'NO', 'Key' => '', 'Default' => '', 'Extra' => ''],
        ];

206
        $this->assertEquals($expected, $this->db->getTableDefinition('Person'));
207
208
209
210
211
    }

    /**
     * @throws Exception
     */
212
213
214
215
216
217
218
219
    protected function setUp() {
        parent::setUp();

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


}