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

require_once(__DIR__ . '/AbstractDatabaseTest.php');
require_once(__DIR__ . '/../../qfq/Database.php');
8
require_once(__DIR__ . '/../../qfq/exceptions/DbException.php');
9
10
11

class DatabaseTest extends AbstractDatabaseTest {

12
    /**
13
     * @throws \qfq\DbException
14
     */
15
    public function testFetchAll() {
16
        $allRows = $this->db->sql('SELECT * FROM Person ORDER BY id LIMIT 2');
17
18
19
20
21
22

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

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

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

32
    /**
33
     * @throws \qfq\DbException
34
     */
35
36
37
38
39
40
41
    public function testQuerySimple() {

        $expected = [
            [
                'id' => '1',
                'name' => 'Doe',
                'firstname' => 'John',
42
43
                'gender' => 'male',
                'groups' => 'c'
44
45
46
47
48
            ],
            [
                'id' => '2',
                'name' => 'Smith',
                'firstname' => 'Jane',
49
50
                'gender' => 'female',
                'groups' => 'a,c'
51
52
53
            ],
        ];

54
55
56
57
58
59

        // 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));

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

63
64
65
        // Check count
        $this->assertEquals(2, $this->store->getVar(SYSTEM_SQL_COUNT, STORE_SYSTEM));
        // Compare rows
66
        $this->assertEquals($expected, $dataArray);
67
68
69
        // Check rows

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

75
76
        // ROW_EXPECT_0
        $dataArray = $this->db->sql('SELECT * FROM Person WHERE id=0', ROW_EXPECT_0);
77
        $this->assertEquals(0, $this->store->getVar(SYSTEM_SQL_COUNT, STORE_SYSTEM));
78
        // Check rows
79
        $this->assertEquals(array(), $dataArray);
80
81
82

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

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

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

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

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

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

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

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

124
    /**
125
     * @throws \qfq\DbException
126
     */
Carsten  Rose's avatar
Carsten Rose committed
127
128
    public function testQuerySimpleParameter() {
        // Parameter Susbstitution by '?'
129
        $dataArray = $this->db->sql('SELECT * FROM Person WHERE name LIKE ? ORDER BY id', ROW_REGULAR, ['Smith']);
Carsten  Rose's avatar
Carsten Rose committed
130
        // Check count
131
        $this->assertEquals(1, $this->store->getVar(SYSTEM_SQL_COUNT, STORE_SYSTEM));
Carsten  Rose's avatar
Carsten Rose committed
132

133
134
        $dataArray = $this->db->sql('UPDATE Person SET groups = ?', ROW_REGULAR, ['a,b,c']);
        $this->assertEquals(2, $this->store->getVar(SYSTEM_SQL_COUNT, STORE_SYSTEM));
Carsten  Rose's avatar
Carsten Rose committed
135

136
137
        $dataArray = $this->db->sql('INSERT INTO Person (`name`, `firstname`, `groups`) VALUES ( ?, ? ,? )', ROW_REGULAR, ['Meier', 'John', 'a']);
        $this->assertEquals(3, $this->store->getVar(SYSTEM_SQL_COUNT, STORE_SYSTEM));
Carsten  Rose's avatar
Carsten Rose committed
138

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

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

145
    }
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181

    /**
     * @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);
    }

182
    /**
183
     * @expectedException \qfq\DbException
184
185
     */
    public function testSanatizeException() {
186
        $this->db->sql('some garbage');
187
188
    }

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

198
199
200
    /**
     *
     */
201
    public function testGetSetValueList() {
202
        $valueList = $this->db->getEnumSetValueList('Person', 'gender');
203
204
205
206
        $expected = [0 => '', 1 => 'male', 2 => 'female'];
        $this->assertEquals($expected, $valueList);

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

211
    /**
212
     * @throws \qfq\DbException
213
     */
214
215
    public function testGetLastInsertId() {

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

218
219
        $this->db->sql($sql);
        $this->assertEquals(3, $this->store->getVar(SYSTEM_SQL_COUNT, STORE_SYSTEM));
220
221
    }

222
223
224
225
226
227
228
229
    /**
     *
     */
    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' => ''],
230
            ['Field' => 'gender', 'Type' => "enum('','male','female')", 'Null' => 'NO', 'Key' => '', 'Default' => 'male', 'Extra' => ''],
231
232
233
            ['Field' => 'groups', 'Type' => "set('','a','b','c')", 'Null' => 'NO', 'Key' => '', 'Default' => '', 'Extra' => ''],
        ];

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

237

238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
    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'
        $dataArray = $this->db->sqlKeys('SELECT id AS "id", name, "0" AS "id" FROM Person ORDER BY id LIMIT 3', $keys);

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

        // Check keys
        $this->assertEquals(['id', 'name', 'id'], $keys);
    }
254
255
256
    /**
     * @throws Exception
     */
257
258
259
260
261
262
    protected function setUp() {
        parent::setUp();

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