Database.php 9.5 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
11
<?php
/**
 * Created by PhpStorm.
 * User: crose
 * Date: 1/4/16
 * Time: 7:14 PM
 */

namespace qfq;

use qfq;
12
use qfq\Store;
13
14
15
use qfq\CodeException;
use qfq\UserException;
use qfq\DbException;
16
17
18
19
20
21
22
23


require_once(__DIR__ . '/../qfq/exceptions/UserException.php');
require_once(__DIR__ . '/../qfq/exceptions/CodeException.php');
require_once(__DIR__ . '/../qfq/exceptions/DbException.php');

require_once(__DIR__ . '/../qfq/store/Store.php');

Carsten  Rose's avatar
Carsten Rose committed
24
25
26
27
/**
 * Class Database
 * @package qfq
 */
28
29
30
31
32
33
34
class Database {

    /**
     * @var \PDO
     */
    public $pdo = null;
    /**
35
     * @var Store
36
37
38
39
40
41
42
43
44
45
46
47
     */
    private $store = null;
    private $stmt = null;

    /**
     * Returns current data base handle from Store[System][SYSTEM_DBH].
     * If not exists: open database and store the new dbh in Store[System][SYSTEM_DBH]
     *
     * @throws CodeException
     * @throws UserException
     */
    public function __construct() {
48
        $this->store = Store::getInstance();
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99

        $this->pdo = $this->store->getVar(SYSTEM_DBH, STORE_SYSTEM);

        if ($this->pdo === false) {
            $this->pdo = $this->dbConnect();
            $this->store->setVar(SYSTEM_DBH, $this->pdo, STORE_SYSTEM);
        }
    }

    /**
     * If not
     * @return \PDO
     * @throws UserException
     */
    private function dbConnect() {
        $dbuser = $this->store->getVar(SYSTEM_DBUSER, STORE_SYSTEM);
        $dbserver = $this->store->getVar(SYSTEM_DBSERVER, STORE_SYSTEM);
        $dbpw = $this->store->getVar(SYSTEM_DBPW, STORE_SYSTEM);
        $db = $this->store->getVar(SYSTEM_DB, STORE_SYSTEM);

        try {
            $pdo = new \PDO("mysql:host=" . $dbserver . ";dbname=" . $db, $dbuser, $dbpw, array(\PDO::ATTR_PERSISTENT => true));
        } catch (\Exception $e) {
            throw new UserException ("Error open Database 'mysql:host=" . $dbserver . ";dbname=" . $db . ";dbuser=" . $dbuser . "'': " . $e->getMessage(), ERROR_OPEN_DATABASE);
        }

        return $pdo;
    }

    /**
     * Return the number of rows returned by the last call to execute().
     *
     * If execute() has never been called, returns FALSE.
     *
     * @return mixed Number of rows returned by last call to execute(). If Database::execute()
     *     has never been called prior a call to this method, false is returned.
     */
    public function rowCount() {
        if ($this->stmt == null) {
            return false;
        }

        return $this->stmt->rowCount();
    }

    /**
     * Get the values for a given ENUM or SET column
     *
     * @param string $table name of the table
     * @param string $columnName name of the column
     *
100
     * @throws UserException if the table or column does not exist, or is not of type ENUM or SET
101
102
     * @return array
     */
103
    public function getEnumSetValueList($table, $columnName) {
104
105
106
107
108
109
110
111
112
113
114
115

        $columnDefinition = $this->getFieldDefinitionFromTable($table, $columnName);
        $setEnumDefinition = $columnDefinition["Type"];

        // $setEnumDefinition holds now a string like
        // String:  enum('','red','blue','green')
        $len = mb_strlen($setEnumDefinition);

        # "enum('" = 6, "set('" = 5
        $tokenLength = strpos($setEnumDefinition, "'") + 1;

        // count("enum('") == 6, count("')") == 2
116
        $enumSetString = mb_substr($setEnumDefinition, $tokenLength, $len - (2 + $tokenLength));
117
118
119

        // String: ','red','blue','green

120
        if (($setEnumValueList = explode("','", $enumSetString)) === false) {
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
            return array();
        }

        return $setEnumValueList;
    }

    /**
     * Get database column definition.
     *
     * If the column is not found in the table, an exception is thrown.
     *
     * @param string $table name of the table
     *
     * @param string $columnName name of the column
     *
136
     * @return array the definition of the column as retrieved by Database::getTableDefinition().
137
     *
138
     * @throws UserException
139
140
141
     *
     */
    private function getFieldDefinitionFromTable($table, $columnName) {
142
        $tableDefinition = $this->getTableDefinition($table);
143
144
145
146
147
        foreach ($tableDefinition AS $row) {
            if ($row["Field"] == $columnName) {
                return $row;
            }
        }
148
        throw new DbException("Column name '$columnName' not found in table '$table'.", ERROR_COLUMN_NOT_FOUND_IN_TABLE);
149
150
151
    }

    /**
152
153
154
155
156
157
158
159
160
     * Get all column definitions for a table. Return Assoc Array:
     *
     * Field      Type                      Null    Key    Default    Extra
     * --------------------------------------------------------------------------
     * id         bigint(20)                 NO     PRI    NULL    auto_increment
     * name       varchar(128)               YES           NULL
     * firstname  varchar(128)               YES           NULL
     * gender     enum('','male','female')   NO            male
     * groups     set('','a','b','c')        NO            a
161
162
163
164
165
     *
     * @param string $table table to retrieve column definition from
     *
     * @return array column definition of table as returned by SHOW FIELDS FROM as associative array.
     */
166
    public function getTableDefinition($table) {
167
        return $this->sql("SHOW FIELDS FROM `$table`");
168
169
170
171
172
173
174
175
176
177
178
179
180
    }

    /**
     * Fires query $sql and fetches result als assoc array.
     * $mode
     *  ROW_REGULAR      return result set as assoc array. No check about number of rows.
     *  ROW_IMPLODE_ALL  return result set as one string: implode all rows and columns.  No check about number of rows.
     *  ROW_EXACT_1      return result set as assoc array. It has to be exact one row selected. If not: throw Exception,
     *  ROW_EMPTY_IS_OK  return result set as assoc array. It has to be 0 or one row selected. If not: throw Exception,
     *
     * @param $sql
     * @param $mode
     */
181
    public function sql($sql, $mode = ROW_REGULAR, array $parameterArray = array(), $specificMessage='') {
182
        // for error reporting in exception
183
184
        if($specificMessage)
            $specificMessage .= " ";
185

Carsten  Rose's avatar
Carsten Rose committed
186
        $count = $this->prepareExecute($sql, $parameterArray);
187
        if ($count === false) {
188
            throw new DbException($specificMessage . "No idea why this error happens - please take some time and check this: $sql", ERROR_DB_GENERIC_CHECK);
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
        }

        if ($mode === ROW_IMPLODE_ALL) {
            return $this->fetchAll(ROW_IMPLODE_ALL);
        }

        if ($mode === ROW_REGULAR) {
            return $this->fetchAll();
        }

        if ($count < 1) {
            if ($mode === ROW_EMPTY_IS_OK) {
                return array();
            }

204
            throw new DbException($specificMessage . "Expected one row, got nothing: $sql", ERROR_DB_TOO_FEW_ROWS);
205
206
207
        }

        if ($count > 1) {
208
            throw new DbException($specificMessage ."Expected one row, got $count rows: $sql", ERROR_DB_TOO_MANY_ROWS);
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
        }

        return $this->fetchOne();

    }

    /**
     * Execute a prepared SQL statement.
     *
     * Execute the given SQL statement as prepared statement. It requires a parameter array which can be empty.
     *
     * The result of the query can be retrieved by using one of the fetch methods.
     *
     * Subsequent calls to this method will overwrite the result of previous calls.
     *
     * @param string $sql SQL statement with prepared statement variable.
     * @param array $parameterArray parameter array for prepared statement execution.
     * @return mixed
     */
Carsten  Rose's avatar
Carsten Rose committed
228
    public function prepareExecute($sql, array $parameterArray = array()) {
229
230

        $this->store->setVar(SYSTEM_SQL_FINAL, $sql, STORE_SYSTEM);
Carsten  Rose's avatar
Carsten Rose committed
231
        $this->store->setVar(SYSTEM_SQL_PARAM_ARRAY, $parameterArray, STORE_SYSTEM);
232
233
234

        $this->stmt = $this->pdo->prepare($sql);
        if (false === $this->stmt->execute($parameterArray)) {
235
            throw new DbException($this->stmt->errorInfo()[2], ERROR_DB_EXECUTE);
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
        }

        $count = $this->stmt->rowCount();
        $this->store->setVar(SYSTEM_SQL_COUNT, $count, STORE_SYSTEM);

        return $count;
    }

    /**
     * Fetch all rows of the result as associative array.
     *
     * @return mixed false in case of error.
     *              Empty string is returned if the query didn't yield any rows.
     *              All rows as Multi Assoc array if $mode!=IMPLODE_ALL.
     *              All rows and all columns imploded to one string if $mode=IMPLODE_ALL
     *
     */
    public function fetchAll($mode = '') {
        if ($this->stmt == null) {
            return false;
        }

        if ($this->stmt->rowCount() === 0) {
            return ($mode === ROW_IMPLODE_ALL) ? "" : array();
        }

        $str = "";
        if ($mode === ROW_IMPLODE_ALL) {
            foreach ($this->stmt->fetchAll(\PDO::FETCH_ASSOC) as $row) {
                $str .= implode($row);
            }
            return $str;
        } else {
            return $this->stmt->fetchAll(\PDO::FETCH_ASSOC);
        }
    }

    /**
     * Fetch one row of the result as associative array.
     *
     * @return null|mixed the first row of the result of Database::execute() as associative array.
     *  If Database::execute() has never been called prior a call to this method, false is returned.
     *  Empty string is returned if the query didn't yield any rows.
     */
    public function fetchOne() {
        if ($this->stmt == null) {
            return false;
        }

        if ($this->stmt->rowCount() === 0) {
            return array();
        }

        return $this->stmt->fetch(\PDO::FETCH_ASSOC);
    }
Carsten  Rose's avatar
Carsten Rose committed
291

292
293
294
295
296
297
298
    /**
     * @return mixed
     */
    public function getLastInsertId() {
        return $this->pdo->lastInsertId();
    }

299
}