Database.php 9.36 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
16
17
18
19
20
21
22
23
use qfq\exceptions\CodeException;
use qfq\exceptions\UserException;
use qfq\exceptions\DbException;


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
100
101
102

        $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
     *
     * @throws exceptions\UserException if the table or column does not exist, or is not of type ENUM or SET
     * @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
139
140
141
     *
     * @throws exceptions\UserException
     *
     */
    private function getFieldDefinitionFromTable($table, $columnName) {
142
        $tableDefinition = $this->getTableDefinition($table);
143
144
145
146
147
148
149
150
151
        foreach ($tableDefinition AS $row) {
            if ($row["Field"] == $columnName) {
                return $row;
            }
        }
        throw new DbException("Column name '$columnName' not found in table '$table'.");
    }

    /**
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
     */
Carsten  Rose's avatar
Carsten Rose committed
181
    public function sql($sql, $mode = ROW_REGULAR, array $parameterArray = array()) {
182
183
        // for error reporting in exception

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

        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();
            }

            throw new DbException("Expected one row, got nothing: $sql", ERROR_DB_TOO_FEW_ROWS);
        }

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

        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
226
    public function prepareExecute($sql, array $parameterArray = array()) {
227
228

        $this->store->setVar(SYSTEM_SQL_FINAL, $sql, STORE_SYSTEM);
Carsten  Rose's avatar
Carsten Rose committed
229
        $this->store->setVar(SYSTEM_SQL_PARAM_ARRAY, $parameterArray, STORE_SYSTEM);
230
231
232
233
234
235
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

        $this->stmt = $this->pdo->prepare($sql);
        if (false === $this->stmt->execute($parameterArray)) {
            throw new DbException($this->stmt->errorInfo()[2]);
        }

        $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
289

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

297
}