Rev 27 | Rev 32 | Go to most recent revision | Only display areas with differences | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed
Rev 27 | Rev 29 | ||
---|---|---|---|
1 | <?php
|
1 | <?php
|
2 | 2 | ||
3 | require_once 'lib/global.inc'; |
3 | require_once __DIR__ . '/../global.inc'; |
4 | 4 | ||
5 | require_once 'lib/AbstractModel.php'; |
5 | require_once __DIR__ . '/../AbstractModel.php'; |
6 | 6 | ||
7 | /**
|
7 | /**
|
8 | * Generic database model class using PDO (PHP Data Objects)
|
8 | * Generic database model class using PDO (PHP Data Objects)
|
9 | *
|
9 | *
|
10 | * @property-read array $lastError
|
10 | * @property-read array $lastError
|
11 | * Last error information of the database operation.
|
11 | * Last error information of the database operation.
|
12 | * See {@link PDOStatement::errorInfo()}.
|
12 | * See {@link PDOStatement::errorInfo()}.
|
13 | * @property-read string $lastInsertId
|
13 | * @property-read string $lastInsertId
|
14 | * ID of the last inserted row, or the last value from a sequence object,
|
14 | * ID of the last inserted row, or the last value from a sequence object,
|
15 | * depending on the underlying driver. May not be supported by all databases.
|
15 | * depending on the underlying driver. May not be supported by all databases.
|
16 | * @property-read array $lastResult
|
16 | * @property-read array $lastResult
|
17 | * Last result of the database operation
|
17 | * Last result of the database operation
|
18 | * @property-read boolean $lastSuccess
|
18 | * @property-read boolean $lastSuccess
|
19 | * Last success value of the database operation
|
19 | * Last success value of the database operation
|
20 | * @author Thomas Lahn
|
20 | * @author Thomas Lahn
|
21 | */
|
21 | */
|
22 | class Database extends AbstractModel |
22 | class Database extends AbstractModel |
23 | {
|
23 | {
|
24 | /**
|
24 | /**
|
25 | * DSN of the database
|
25 | * DSN of the database
|
26 | * @var string
|
26 | * @var string
|
27 | */
|
27 | */
|
28 | protected $_dsn = ''; |
28 | protected $_dsn = ''; |
29 | 29 | ||
30 | /**
|
30 | /**
|
31 | * Username to access the database
|
31 | * Username to access the database
|
32 | * @var string
|
32 | * @var string
|
33 | */
|
33 | */
|
34 | protected $_username; |
34 | protected $_username; |
35 | 35 | ||
36 | /**
|
36 | /**
|
37 | * Password to access the database
|
37 | * Password to access the database
|
38 | * @var string
|
38 | * @var string
|
39 | */
|
39 | */
|
40 | protected $_password; |
40 | protected $_password; |
41 | 41 | ||
42 | /**
|
42 | /**
|
43 | * PDO driver-specific options
|
43 | * PDO driver-specific options
|
44 | * @var array
|
44 | * @var array
|
45 | */
|
45 | */
|
46 | protected $_options = array(); |
46 | protected $_options = array(); |
47 | 47 | ||
48 | /**
|
48 | /**
|
49 | * Database connection
|
49 | * Database connection
|
50 | * @var PDO
|
50 | * @var PDO
|
51 | */
|
51 | */
|
52 | protected $_connection; |
52 | protected $_connection; |
53 | 53 | ||
54 | /**
|
54 | /**
|
55 | * Last success value of the database operation
|
55 | * Last success value of the database operation
|
56 | * @var boolean
|
56 | * @var boolean
|
57 | */
|
57 | */
|
58 | protected $_lastSuccess; |
58 | protected $_lastSuccess; |
59 | 59 | ||
60 | /**
|
60 | /**
|
61 | * Last error information of the database operation
|
61 | * Last error information of the database operation
|
62 | * @var array
|
62 | * @var array
|
63 | */
|
63 | */
|
64 | protected $_lastError; |
64 | protected $_lastError; |
65 | 65 | ||
66 | /**
|
66 | /**
|
67 | * Last result of the database operation
|
67 | * Last result of the database operation
|
68 | * @var array
|
68 | * @var array
|
69 | */
|
69 | */
|
70 | protected $_lastResult; |
70 | protected $_lastResult; |
71 | 71 | ||
72 | /**
|
72 | /**
|
73 | * ID of the last inserted row, or the last value from a sequence object,
|
73 | * ID of the last inserted row, or the last value from a sequence object,
|
74 | * depending on the underlying driver. May not be supported by all databases.
|
74 | * depending on the underlying driver. May not be supported by all databases.
|
75 | * @var string
|
75 | * @var string
|
76 | */
|
76 | */
|
77 | protected $_lastInsertId = ''; |
77 | protected $_lastInsertId = ''; |
78 | 78 | ||
79 | public function __construct() |
79 | public function __construct() |
80 | {
|
80 | {
|
81 | $this->_connection = |
81 | $this->_connection = |
82 | new PDO($this->_dsn, $this->_username, $this->_password, $this->_options); |
82 | new PDO($this->_dsn, $this->_username, $this->_password, $this->_options); |
83 | }
|
83 | }
|
84 | 84 | ||
85 | /**
|
85 | /**
|
86 | * Initiates a transaction
|
86 | * Initiates a transaction
|
87 | *
|
87 | *
|
88 | * @return bool
|
88 | * @return bool
|
89 | * @see PDO::beginTransaction()
|
89 | * @see PDO::beginTransaction()
|
90 | */
|
90 | */
|
91 | public function beginTransaction() |
91 | public function beginTransaction() |
92 | {
|
92 | {
|
93 | return $this->_connection->beginTransaction(); |
93 | return $this->_connection->beginTransaction(); |
94 | }
|
94 | }
|
95 | 95 | ||
96 | /**
|
96 | /**
|
97 | * Rolls back a transaction
|
97 | * Rolls back a transaction
|
98 | *
|
98 | *
|
99 | * @return bool
|
99 | * @return bool
|
100 | * @see PDO::rollBack()
|
100 | * @see PDO::rollBack()
|
101 | */
|
101 | */
|
102 | public function rollBack() |
102 | public function rollBack() |
103 | {
|
103 | {
|
104 | return $this->_connection->rollBack(); |
104 | return $this->_connection->rollBack(); |
105 | }
|
105 | }
|
106 | 106 | ||
107 | /**
|
107 | /**
|
108 | * Commits a transaction
|
108 | * Commits a transaction
|
109 | *
|
109 | *
|
110 | * @return bool
|
110 | * @return bool
|
111 | * @see PDO::commit()
|
111 | * @see PDO::commit()
|
112 | */
|
112 | */
|
113 | public function commit() |
113 | public function commit() |
114 | {
|
114 | {
|
115 | return $this->_connection->commit(); |
115 | return $this->_connection->commit(); |
116 | }
|
116 | }
|
117 | 117 | ||
118 | /**
|
118 | /**
|
119 | * Prepares a statement for execution with the database
|
119 | * Prepares a statement for execution with the database
|
120 | * @param string $query
|
120 | * @param string $query
|
121 | */
|
121 | */
|
122 | public function prepare($query, array $driver_options = array()) |
122 | public function prepare($query, array $driver_options = array()) |
123 | {
|
123 | {
|
124 | return $this->_connection->prepare($query, $driver_options); |
124 | return $this->_connection->prepare($query, $driver_options); |
125 | }
|
125 | }
|
126 | 126 | ||
127 | /**
|
127 | /**
|
128 | * Returns the ID of the last inserted row, or the last value from
|
128 | * Returns the ID of the last inserted row, or the last value from
|
129 | * a sequence object, depending on the underlying driver.
|
129 | * a sequence object, depending on the underlying driver.
|
130 | *
|
130 | *
|
131 | * @return int
|
131 | * @return int
|
132 | */
|
132 | */
|
133 | public function getLastInsertId() |
133 | public function getLastInsertId() |
134 | {
|
134 | {
|
135 | return $this->_lastInsertId; |
135 | return $this->_lastInsertId; |
136 | }
|
136 | }
|
137 | 137 | ||
138 | /**
|
138 | /**
|
139 | * Escapes a database name so that it can be used in a query.
|
139 | * Escapes a database name so that it can be used in a query.
|
140 | *
|
140 | *
|
141 | * @param string $name
|
141 | * @param string $name
|
142 | * The name to be escaped
|
142 | * The name to be escaped
|
143 | * @return string
|
143 | * @return string
|
144 | * The escaped name
|
144 | * The escaped name
|
145 | */
|
145 | */
|
146 | public function escapeName($name) |
146 | public function escapeName($name) |
147 | {
|
147 | {
|
148 | return $name; |
148 | return $name; |
149 | }
|
149 | }
|
150 | 150 | ||
151 | /**
|
151 | /**
|
152 | * Determines if an array is associative (has not all integer keys).
|
152 | * Determines if an array is associative (has not all integer keys).
|
153 | *
|
153 | *
|
154 | * @author
|
154 | * @author
|
155 | * Algorithm courtesy of squirrel, <http://stackoverflow.com/a/5969617/855543>.
|
155 | * Algorithm courtesy of squirrel, <http://stackoverflow.com/a/5969617/855543>.
|
156 | * @param array $a
|
156 | * @param array $a
|
157 | * @return boolean
|
157 | * @return boolean
|
158 | * <code>true</code> if <var>$a</var> is associative,
|
158 | * <code>true</code> if <var>$a</var> is associative,
|
159 | * <code>false</code> otherwise
|
159 | * <code>false</code> otherwise
|
160 | */
|
160 | */
|
161 | protected function _isAssociativeArray(array $a) |
161 | protected function _isAssociativeArray(array $a) |
162 | {
|
162 | {
|
163 | for (reset($a); is_int(key($a)); next($a)); |
163 | for (reset($a); is_int(key($a)); next($a)); |
164 | return !is_null(key($a)); |
164 | return !is_null(key($a)); |
165 | }
|
165 | }
|
166 | 166 | ||
167 | /**
|
167 | /**
|
168 | * Escapes an associative array so that its string representation can be used
|
168 | * Escapes an associative array so that its string representation can be used
|
169 | * as list with table or column aliases in a query.
|
169 | * as list with table or column aliases in a query.
|
170 | *
|
170 | *
|
171 | * This method does not actually escape anything; it only inserts the
|
171 | * This method does not actually escape anything; it only inserts the
|
172 | * 'AS' keyword. It should be overridden by inheriting methods.
|
172 | * 'AS' keyword. It should be overridden by inheriting methods.
|
173 | *
|
173 | *
|
174 | * NOTE: This method intentionally does not check whether the array actually
|
174 | * NOTE: This method intentionally does not check whether the array actually
|
175 | * is associative.
|
175 | * is associative.
|
176 | *
|
176 | *
|
177 | * @param array &$array
|
177 | * @param array &$array
|
178 | * The array to be escaped
|
178 | * The array to be escaped
|
179 | * @return array
|
179 | * @return array
|
180 | * The escaped array
|
180 | * The escaped array
|
181 | */
|
181 | */
|
182 | protected function _escapeAliasArray(array &$array) |
182 | protected function _escapeAliasArray(array &$array) |
183 | {
|
183 | {
|
184 | foreach ($array as $column => &$value) |
184 | foreach ($array as $column => &$value) |
185 | {
|
185 | {
|
186 | $value = $value . ' AS ' . $column; |
186 | $value = $value . ' AS ' . $column; |
187 | }
|
187 | }
|
188 | 188 | ||
189 | return $array; |
189 | return $array; |
190 | }
|
190 | }
|
191 | 191 | ||
192 | /**
|
192 | /**
|
193 | * @param array $a
|
193 | * @param array $a
|
194 | * @param string $prefix
|
194 | * @param string $prefix
|
195 | */
|
195 | */
|
196 | private static function _expand(array $a, $prefix) |
196 | private static function _expand(array $a, $prefix) |
197 | {
|
197 | {
|
198 | $a2 = array(); |
198 | $a2 = array(); |
199 | 199 | ||
200 | foreach ($a as $key => $value) |
200 | foreach ($a as $key => $value) |
201 | {
|
201 | {
|
202 | $a2[] = ':' . $prefix . ($key + 1); |
202 | $a2[] = ':' . $prefix . ($key + 1); |
203 | }
|
203 | }
|
204 | 204 | ||
205 | return $a2; |
205 | return $a2; |
206 | }
|
206 | }
|
207 | 207 | ||
208 | /**
|
208 | /**
|
209 | * Escapes an associative array so that its string representation can be used
|
209 | * Escapes an associative array so that its string representation can be used
|
210 | * as value list in a query.
|
210 | * as value list in a query.
|
211 | *
|
211 | *
|
212 | * This method should be overridden by inheriting classes to escape
|
212 | * This method should be overridden by inheriting classes to escape
|
213 | * column names as fitting for the database schema they support. It is
|
213 | * column names as fitting for the database schema they support. It is
|
214 | * strongly recommended that the overriding methods call this method with
|
214 | * strongly recommended that the overriding methods call this method with
|
215 | * an appropriate <var>$escape</var> parameter, pass all other parameters
|
215 | * an appropriate <var>$escape</var> parameter, pass all other parameters
|
216 | * on unchanged, and return its return value.
|
216 | * on unchanged, and return its return value.
|
217 | *
|
217 | *
|
218 | * NOTE: Intentionally does not check whether the array actually is associative!
|
218 | * NOTE: Intentionally does not check whether the array actually is associative!
|
219 | *
|
219 | *
|
220 | * @param array &$array
|
220 | * @param array &$array
|
221 | * The array to be escaped
|
221 | * The array to be escaped
|
222 | * @param string $suffix
|
222 | * @param string $suffix
|
223 | * The string to be appended to the column name for the value placeholder.
|
223 | * The string to be appended to the column name for the value placeholder.
|
224 | * The default is the empty string.
|
224 | * The default is the empty string.
|
225 | * @param array $escape
|
225 | * @param array $escape
|
226 | * The strings to use left-hand side (index 0) and right-hand side (index 1)
|
226 | * The strings to use left-hand side (index 0) and right-hand side (index 1)
|
227 | * of the column name. The default is the empty string, respectively.
|
227 | * of the column name. The default is the empty string, respectively.
|
228 | * @return array
|
228 | * @return array
|
229 | * The escaped array
|
229 | * The escaped array
|
230 | */
|
230 | */
|
231 | protected function _escapeValueArray(array &$array, $suffix = '', array &$escape = array('', '')) |
231 | protected function _escapeValueArray(array &$array, $suffix = '', array &$escape = array('', '')) |
232 | {
|
232 | {
|
233 | $result = array(); |
233 | $result = array(); |
234 | 234 | ||
235 | foreach ($array as $column => $value) |
235 | foreach ($array as $column => $value) |
236 | {
|
236 | {
|
237 | $op = '='; |
237 | $op = '='; |
238 | $placeholder = ":{$column}"; |
238 | $placeholder = ":{$column}"; |
239 | 239 | ||
240 | if (is_array($value) && $this->_isAssociativeArray($value)) |
240 | if (is_array($value) && $this->_isAssociativeArray($value)) |
241 | {
|
241 | {
|
242 | reset($value); |
242 | reset($value); |
243 | $op = ' ' . key($value) . ' '; |
243 | $op = ' ' . key($value) . ' '; |
244 | 244 | ||
245 | $value = $value[key($value)]; |
245 | $value = $value[key($value)]; |
246 | }
|
246 | }
|
247 | 247 | ||
248 | if (is_array($value)) |
248 | if (is_array($value)) |
249 | {
|
249 | {
|
250 | $placeholder = '(' . implode(',', self::_expand($value, $column)) . ')'; |
250 | $placeholder = '(' . implode(',', self::_expand($value, $column)) . ')'; |
251 | }
|
251 | }
|
252 | 252 | ||
253 | $result[] = $escape[0] . $column . $escape[1] . "{$op}{$placeholder}{$suffix}"; |
253 | $result[] = $escape[0] . $column . $escape[1] . "{$op}{$placeholder}{$suffix}"; |
254 | }
|
254 | }
|
255 | 255 | ||
256 | return $result; |
256 | return $result; |
257 | }
|
257 | }
|
258 | 258 | ||
259 | /**
|
259 | /**
|
260 | * Constructs the WHERE part of a query
|
260 | * Constructs the WHERE part of a query
|
261 | *
|
261 | *
|
262 | * @param string|array $where
|
262 | * @param string|array $where
|
263 | * Condition
|
263 | * Condition
|
264 | * @param string $suffix
|
264 | * @param string $suffix
|
265 | * The string to be appended to the column name for the value placeholder,
|
265 | * The string to be appended to the column name for the value placeholder,
|
266 | * passed on to {@link Database::_escapeValueArray()}. The default is
|
266 | * passed on to {@link Database::_escapeValueArray()}. The default is
|
267 | * the empty string.
|
267 | * the empty string.
|
268 | * @return string
|
268 | * @return string
|
269 | * @see Database::_escapeValueArray()
|
269 | * @see Database::_escapeValueArray()
|
270 | */
|
270 | */
|
271 | protected function _where($where, $suffix = '') |
271 | protected function _where($where, $suffix = '') |
272 | {
|
272 | {
|
273 | if (!is_null($where)) |
273 | if (!is_null($where)) |
274 | {
|
274 | {
|
275 | if (is_array($where)) |
275 | if (is_array($where)) |
276 | {
|
276 | {
|
277 | if (count($where) < 1) |
277 | if (count($where) < 1) |
278 | {
|
278 | {
|
279 | return ''; |
279 | return ''; |
280 | }
|
280 | }
|
281 | 281 | ||
282 | if ($this->_isAssociativeArray($where)) |
282 | if ($this->_isAssociativeArray($where)) |
283 | {
|
283 | {
|
284 | $where = $this->_escapeValueArray($where, $suffix); |
284 | $where = $this->_escapeValueArray($where, $suffix); |
285 | }
|
285 | }
|
286 | 286 | ||
287 | $where = '(' . implode(') AND (', $where) . ')'; |
287 | $where = '(' . implode(') AND (', $where) . ')'; |
288 | }
|
288 | }
|
289 | 289 | ||
290 | return ' WHERE ' . $where; |
290 | return ' WHERE ' . $where; |
291 | }
|
291 | }
|
292 | 292 | ||
293 | return ''; |
293 | return ''; |
294 | }
|
294 | }
|
295 | 295 | ||
296 | /**
|
296 | /**
|
297 | * Selects data from one or more tables; the resulting records are stored
|
297 | * Selects data from one or more tables; the resulting records are stored
|
298 | * in the <code>result</code> property and returned as an associative array,
|
298 | * in the <code>result</code> property and returned as an associative array,
|
299 | * where the keys are the column (alias) names.
|
299 | * where the keys are the column (alias) names.
|
300 | *
|
300 | *
|
301 | * @param string|array[string] $tables Table(s) to select from
|
301 | * @param string|array[string] $tables Table(s) to select from
|
302 | * @param string|array[string] $columns Column(s) to select from (optional)
|
302 | * @param string|array[string] $columns Column(s) to select from (optional)
|
303 | * @param string|array $where Condition (optional)
|
303 | * @param string|array $where Condition (optional)
|
304 | * @param string $order Sort order (optional)
|
304 | * @param string $order Sort order (optional)
|
305 | * If provided, MUST start with ORDER BY or GROUP BY
|
305 | * If provided, MUST start with ORDER BY or GROUP BY
|
306 | * @param string $limit Limit (optional)
|
306 | * @param string $limit Limit (optional)
|
307 | * @param int $fetch_style
|
307 | * @param int $fetch_style
|
308 | * The mode that should be used for {@link PDOStatement::fetchAll()}.
|
308 | * The mode that should be used for {@link PDOStatement::fetchAll()}.
|
309 | * The default is {@link PDO::FETCH_ASSOC}.
|
309 | * The default is {@link PDO::FETCH_ASSOC}.
|
310 | * @return array
|
310 | * @return array
|
311 | * @see Database::prepare()
|
311 | * @see Database::prepare()
|
312 | * @see PDOStatement::fetchAll()
|
312 | * @see PDOStatement::fetchAll()
|
313 | */
|
313 | */
|
314 | public function select($tables, $columns = null, $where = null, |
314 | public function select($tables, $columns = null, $where = null, |
315 | $order = null, $limit = null, $fetch_style = PDO::FETCH_ASSOC) |
315 | $order = null, $limit = null, $fetch_style = PDO::FETCH_ASSOC) |
316 | {
|
316 | {
|
317 | if (is_null($columns)) |
317 | if (is_null($columns)) |
318 | {
|
318 | {
|
319 | $columns = array('*'); |
319 | $columns = array('*'); |
320 | }
|
320 | }
|
321 | 321 | ||
322 | if (is_array($columns)) |
322 | if (is_array($columns)) |
323 | {
|
323 | {
|
324 | if ($this->_isAssociativeArray($columns)) |
324 | if ($this->_isAssociativeArray($columns)) |
325 | {
|
325 | {
|
326 | $columns = $this->_escapeAliasArray($columns); |
326 | $columns = $this->_escapeAliasArray($columns); |
327 | }
|
327 | }
|
328 | 328 | ||
329 | $columns = implode(',', $columns); |
329 | $columns = implode(',', $columns); |
330 | }
|
330 | }
|
331 | 331 | ||
332 | if (is_array($tables)) |
332 | if (is_array($tables)) |
333 | {
|
333 | {
|
334 | if ($this->_isAssociativeArray($columns)) |
334 | if ($this->_isAssociativeArray($columns)) |
335 | {
|
335 | {
|
336 | $columns = $this->_escapeAliasArray($columns); |
336 | $columns = $this->_escapeAliasArray($columns); |
337 | }
|
337 | }
|
338 | 338 | ||
339 | $tables = implode(',', $tables); |
339 | $tables = implode(',', $tables); |
340 | }
|
340 | }
|
341 | 341 | ||
342 | $query = "SELECT {$columns} FROM {$tables}" . $this->_where($where); |
342 | $query = "SELECT {$columns} FROM {$tables}" . $this->_where($where); |
343 | 343 | ||
344 | if (!is_null($order)) |
344 | if (!is_null($order)) |
345 | {
|
345 | {
|
346 | if (is_array($order)) |
346 | if (is_array($order)) |
347 | {
|
347 | {
|
348 | $order = 'ORDER BY ' . implode(',', $order); |
348 | $order = 'ORDER BY ' . implode(',', $order); |
349 | }
|
349 | }
|
350 | 350 | ||
351 | $query .= " $order"; |
351 | $query .= " $order"; |
352 | }
|
352 | }
|
353 | 353 | ||
354 | if (!is_null($limit)) |
354 | if (!is_null($limit)) |
355 | {
|
355 | {
|
356 | $query .= " LIMIT $limit"; |
356 | $query .= " LIMIT $limit"; |
357 | }
|
357 | }
|
358 | 358 | ||
359 | $stmt = $this->prepare($query); |
359 | $stmt = $this->prepare($query); |
360 | 360 | ||
361 | $params = array(); |
361 | $params = array(); |
362 | 362 | ||
363 | if (is_array($where) && $this->_isAssociativeArray($where)) |
363 | if (is_array($where) && $this->_isAssociativeArray($where)) |
364 | {
|
364 | {
|
365 | foreach ($where as $column => $condition) |
365 | foreach ($where as $column => $condition) |
366 | {
|
366 | {
|
367 | if (is_array($condition) && $this->_isAssociativeArray($condition)) |
367 | if (is_array($condition) && $this->_isAssociativeArray($condition)) |
368 | {
|
368 | {
|
369 | reset($condition); |
369 | reset($condition); |
370 | $condition = $condition[key($condition)]; |
370 | $condition = $condition[key($condition)]; |
371 | 371 | ||
372 | if (is_array($condition)) |
372 | if (is_array($condition)) |
373 | {
|
373 | {
|
374 | foreach (self::_expand($condition, $column) as $param_index => $param_name) |
374 | foreach (self::_expand($condition, $column) as $param_index => $param_name) |
375 | {
|
375 | {
|
376 | $params[$param_name] = $condition[$param_index]; |
376 | $params[$param_name] = $condition[$param_index]; |
377 | }
|
377 | }
|
378 | }
|
378 | }
|
379 | }
|
379 | }
|
380 | else
|
380 | else
|
381 | {
|
381 | {
|
382 | $params[":{$column}"] = $condition; |
382 | $params[":{$column}"] = $condition; |
383 | }
|
383 | }
|
384 | }
|
384 | }
|
385 | }
|
385 | }
|
386 | 386 | ||
387 | /* DEBUG */
|
387 | /* DEBUG */
|
388 | if (defined('DEBUG') && DEBUG > 1) |
388 | if (defined('DEBUG') && DEBUG > 1) |
389 | {
|
389 | {
|
390 | debug(array( |
390 | debug(array( |
391 | 'query' => $query, |
391 | 'query' => $query, |
392 | 'params' => $params |
392 | 'params' => $params |
393 | )); |
393 | )); |
394 | }
|
394 | }
|
395 | 395 | ||
396 | $success =& $this->_lastSuccess; |
396 | $success =& $this->_lastSuccess; |
397 | $success = $stmt->execute($params); |
397 | $success = $stmt->execute($params); |
398 | 398 | ||
399 | $errorInfo =& $this->_lastError; |
399 | $errorInfo =& $this->_lastError; |
400 | $errorInfo = $stmt->errorInfo(); |
400 | $errorInfo = $stmt->errorInfo(); |
401 | 401 | ||
402 | $result =& $this->_lastResult; |
402 | $result =& $this->_lastResult; |
403 | $result = $stmt->fetchAll($fetch_style); |
403 | $result = $stmt->fetchAll($fetch_style); |
404 | 404 | ||
405 | if (defined('DEBUG') && DEBUG > 1) |
405 | if (defined('DEBUG') && DEBUG > 1) |
406 | {
|
406 | {
|
407 | debug(array( |
407 | debug(array( |
408 | '_lastSuccess' => $success, |
408 | '_lastSuccess' => $success, |
409 | '_lastError' => $errorInfo, |
409 | '_lastError' => $errorInfo, |
410 | '_lastResult' => $result |
410 | '_lastResult' => $result |
411 | )); |
411 | )); |
412 | }
|
412 | }
|
413 | 413 | ||
414 | return $result; |
414 | return $result; |
415 | }
|
415 | }
|
416 | 416 | ||
417 | /**
|
417 | /**
|
418 | * Sets and returns the ID of the last inserted row, or the last value from
|
418 | * Sets and returns the ID of the last inserted row, or the last value from
|
419 | * a sequence object, depending on the underlying driver.
|
419 | * a sequence object, depending on the underlying driver.
|
420 | *
|
420 | *
|
421 | * @param string $name
|
421 | * @param string $name
|
422 | * Name of the sequence object from which the ID should be returned.
|
422 | * Name of the sequence object from which the ID should be returned.
|
423 | * @return string
|
423 | * @return string
|
424 | */
|
424 | */
|
425 | protected function _setLastInsertId($name = null) |
425 | protected function _setLastInsertId($name = null) |
426 | {
|
426 | {
|
427 | return ($this->_lastInsertId = $this->_connection->lastInsertId($name)); |
427 | return ($this->_lastInsertId = $this->_connection->lastInsertId($name)); |
428 | }
|
428 | }
|
429 | 429 | ||
430 | /**
|
430 | /**
|
431 | * Resets the the ID of the last inserted row, or the last value from
|
431 | * Resets the the ID of the last inserted row, or the last value from
|
432 | * a sequence object, depending on the underlying driver.
|
432 | * a sequence object, depending on the underlying driver.
|
433 | *
|
433 | *
|
434 | * @return string
|
434 | * @return string
|
435 | * The default value
|
435 | * The default value
|
436 | */
|
436 | */
|
437 | protected function _resetLastInsertId() |
437 | protected function _resetLastInsertId() |
438 | {
|
438 | {
|
439 | return ($this->_lastInsertId = ''); |
439 | return ($this->_lastInsertId = ''); |
440 | }
|
440 | }
|
441 | 441 | ||
442 | /**
|
442 | /**
|
443 | * Updates one or more records
|
443 | * Updates one or more records
|
444 | *
|
444 | *
|
445 | * @param string|array $tables
|
445 | * @param string|array $tables
|
446 | * Table name
|
446 | * Table name
|
447 | * @param array $values
|
447 | * @param array $values
|
448 | * Associative array of column-value pairs
|
448 | * Associative array of column-value pairs
|
449 | * @param array|string $where
|
449 | * @param array|string $where
|
450 | * Only the records matching this condition are updated
|
450 | * Only the records matching this condition are updated
|
451 | * @return bool
|
451 | * @return bool
|
452 | */
|
452 | */
|
453 | public function update($tables, $updates, $where = null) |
453 | public function update($tables, $updates, $where = null) |
454 | {
|
454 | {
|
455 | if (!$tables) |
455 | if (!$tables) |
456 | {
|
456 | {
|
457 | throw new InvalidArgumentException('No table specified'); |
457 | throw new InvalidArgumentException('No table specified'); |
458 | }
|
458 | }
|
459 | 459 | ||
460 | if (is_array($tables)) |
460 | if (is_array($tables)) |
461 | {
|
461 | {
|
462 | $tables = implode(',', $tables); |
462 | $tables = implode(',', $tables); |
463 | }
|
463 | }
|
464 | 464 | ||
465 | if (!$updates) |
465 | if (!$updates) |
466 | {
|
466 | {
|
467 | throw new InvalidArgumentException('No values specified'); |
467 | throw new InvalidArgumentException('No values specified'); |
468 | }
|
468 | }
|
469 | 469 | ||
470 | $params = array(); |
470 | $params = array(); |
471 | 471 | ||
472 | if ($this->_isAssociativeArray($updates)) |
472 | if ($this->_isAssociativeArray($updates)) |
473 | {
|
473 | {
|
474 | foreach ($updates as $key => $condition) |
474 | foreach ($updates as $key => $condition) |
475 | {
|
475 | {
|
476 | $params[":{$key}"] = $condition; |
476 | $params[":{$key}"] = $condition; |
477 | }
|
477 | }
|
478 | }
|
478 | }
|
479 | 479 | ||
480 | $updates = implode(',', $this->_escapeValueArray($updates)); |
480 | $updates = implode(',', $this->_escapeValueArray($updates)); |
481 | 481 | ||
482 | /* TODO: Should escape table names with escapeName(), but what about aliases? */
|
482 | /* TODO: Should escape table names with escapeName(), but what about aliases? */
|
483 | $query = "UPDATE {$tables} SET {$updates}" . $this->_where($where, '2'); |
483 | $query = "UPDATE {$tables} SET {$updates}" . $this->_where($where, '2'); |
484 | 484 | ||
485 | $stmt = $this->prepare($query); |
485 | $stmt = $this->prepare($query); |
486 | 486 | ||
487 | if (is_array($where) && $this->_isAssociativeArray($where)) |
487 | if (is_array($where) && $this->_isAssociativeArray($where)) |
488 | {
|
488 | {
|
489 | foreach ($where as $column => $condition) |
489 | foreach ($where as $column => $condition) |
490 | {
|
490 | {
|
491 | if (is_array($condition) && $this->_isAssociativeArray($condition)) |
491 | if (is_array($condition) && $this->_isAssociativeArray($condition)) |
492 | {
|
492 | {
|
493 | reset($condition); |
493 | reset($condition); |
494 | $condition = $condition[key($condition)]; |
494 | $condition = $condition[key($condition)]; |
495 | 495 | ||
496 | if (is_array($condition)) |
496 | if (is_array($condition)) |
497 | {
|
497 | {
|
498 | foreach (self::_expand($condition, $column) as $param_index => $param_name) |
498 | foreach (self::_expand($condition, $column) as $param_index => $param_name) |
499 | {
|
499 | {
|
500 | $params[$param_name] = $condition[$param_index]; |
500 | $params[$param_name] = $condition[$param_index]; |
501 | }
|
501 | }
|
502 | }
|
502 | }
|
503 | }
|
503 | }
|
504 | else
|
504 | else
|
505 | {
|
505 | {
|
506 | $params[":{$column}2"] = $condition; |
506 | $params[":{$column}2"] = $condition; |
507 | }
|
507 | }
|
508 | }
|
508 | }
|
509 | }
|
509 | }
|
510 | 510 | ||
511 | /* DEBUG */
|
511 | /* DEBUG */
|
512 | if (defined('DEBUG') && DEBUG > 1) |
512 | if (defined('DEBUG') && DEBUG > 1) |
513 | {
|
513 | {
|
514 | debug(array( |
514 | debug(array( |
515 | 'query' => $query, |
515 | 'query' => $query, |
516 | 'params' => $params |
516 | 'params' => $params |
517 | )); |
517 | )); |
518 | }
|
518 | }
|
519 | 519 | ||
520 | $success =& $this->_lastSuccess; |
520 | $success =& $this->_lastSuccess; |
521 | $success = $stmt->execute($params); |
521 | $success = $stmt->execute($params); |
522 | 522 | ||
523 | $errorInfo =& $this->_lastError; |
523 | $errorInfo =& $this->_lastError; |
524 | $errorInfo = $stmt->errorInfo(); |
524 | $errorInfo = $stmt->errorInfo(); |
525 | 525 | ||
526 | $this->_resetLastInsertId(); |
526 | $this->_resetLastInsertId(); |
527 | 527 | ||
528 | $result =& $this->_lastResult; |
528 | $result =& $this->_lastResult; |
529 | $result = $stmt->fetchAll(); |
529 | $result = $stmt->fetchAll(); |
530 | 530 | ||
531 | if (defined('DEBUG') && DEBUG > 1) |
531 | if (defined('DEBUG') && DEBUG > 1) |
532 | {
|
532 | {
|
533 | debug(array( |
533 | debug(array( |
534 | '_lastSuccess' => $success, |
534 | '_lastSuccess' => $success, |
535 | '_lastError' => $errorInfo, |
535 | '_lastError' => $errorInfo, |
536 | '_lastResult' => $result |
536 | '_lastResult' => $result |
537 | )); |
537 | )); |
538 | }
|
538 | }
|
539 | 539 | ||
540 | return $success; |
540 | return $success; |
541 | }
|
541 | }
|
542 | 542 | ||
543 | /**
|
543 | /**
|
544 | * Inserts a record into a table.<p>The AUTO_INCREMENT value of the inserted
|
544 | * Inserts a record into a table.<p>The AUTO_INCREMENT value of the inserted
|
545 | * row, if any (> 0), is stored in the {@link $lastInsertId} property of
|
545 | * row, if any (> 0), is stored in the {@link $lastInsertId} property of
|
546 | * the <code>Database</code> instance.</p>
|
546 | * the <code>Database</code> instance.</p>
|
547 | *
|
547 | *
|
548 | * @param string $table
|
548 | * @param string $table
|
549 | * Table name
|
549 | * Table name
|
550 | * @param array|string $values
|
550 | * @param array|string $values
|
551 | * Associative array of column-value pairs, indexed array,
|
551 | * Associative array of column-value pairs, indexed array,
|
552 | * or comma-separated list of values. If <var>$values</var> is not
|
552 | * or comma-separated list of values. If <var>$values</var> is not
|
553 | * an associative array, <var>$cols</var> must be passed if the
|
553 | * an associative array, <var>$cols</var> must be passed if the
|
554 | * values are not in column order (see below).
|
554 | * values are not in column order (see below).
|
555 | * @param array|string $cols
|
555 | * @param array|string $cols
|
556 | * Indexed array, or comma-separated list of column names.
|
556 | * Indexed array, or comma-separated list of column names.
|
557 | * Needs only be passed if <var>$values</var> is not an associative array
|
557 | * Needs only be passed if <var>$values</var> is not an associative array
|
558 | * and the values are not in column order (default: <code>null</code>);
|
558 | * and the values are not in column order (default: <code>null</code>);
|
559 | * is ignored otherwise. <strong>You SHOULD NOT rely on column order.</strong>
|
559 | * is ignored otherwise. <strong>You SHOULD NOT rely on column order.</strong>
|
560 | * @return bool
|
560 | * @return bool
|
561 | * <code>true</code> if successful, <code>false</code> otherwise
|
561 | * <code>true</code> if successful, <code>false</code> otherwise
|
562 | * @see PDOStatement::execute()
|
562 | * @see PDOStatement::execute()
|
563 | */
|
563 | */
|
564 | public function insert($table, $values, $cols = null) |
564 | public function insert($table, $values, $cols = null) |
565 | {
|
565 | {
|
566 | if ($cols != null) |
566 | if ($cols != null) |
567 | {
|
567 | {
|
568 | $cols = ' (' |
568 | $cols = ' (' |
569 | . (is_array($cols) |
569 | . (is_array($cols) |
570 | ? implode(',', array_map(create_function('$s', 'return "`$s`";'), $cols)) |
570 | ? implode(',', array_map(create_function('$s', 'return "`$s`";'), $cols)) |
571 | : $cols) . ')'; |
571 | : $cols) . ')'; |
572 | }
|
572 | }
|
573 | else
|
573 | else
|
574 | {
|
574 | {
|
575 | $cols = ''; |
575 | $cols = ''; |
576 | }
|
576 | }
|
577 | 577 | ||
578 | /* DEBUG */
|
578 | /* DEBUG */
|
579 | if (defined('DEBUG') && DEBUG > 2) |
579 | if (defined('DEBUG') && DEBUG > 2) |
580 | {
|
580 | {
|
581 | debug(array('values' => $values)); |
581 | debug(array('values' => $values)); |
582 | }
|
582 | }
|
583 | 583 | ||
584 | $params = array(); |
584 | $params = array(); |
585 | 585 | ||
586 | if (is_array($values)) |
586 | if (is_array($values)) |
587 | {
|
587 | {
|
588 | if ($this->_isAssociativeArray($values)) |
588 | if ($this->_isAssociativeArray($values)) |
589 | {
|
589 | {
|
590 | foreach ($values as $key => $condition) |
590 | foreach ($values as $key => $condition) |
591 | {
|
591 | {
|
592 | $params[":{$key}"] = $condition; |
592 | $params[":{$key}"] = $condition; |
593 | }
|
593 | }
|
594 | 594 | ||
595 | $values = $this->_escapeValueArray($values); |
595 | $values = $this->_escapeValueArray($values); |
596 | 596 | ||
597 | $cols = ''; |
597 | $cols = ''; |
598 | $values = 'SET ' . implode(', ', $values); |
598 | $values = 'SET ' . implode(', ', $values); |
599 | }
|
599 | }
|
600 | else
|
600 | else
|
601 | {
|
601 | {
|
602 | foreach ($values as &$value) |
602 | foreach ($values as &$value) |
603 | {
|
603 | {
|
604 | if (is_string($value)) |
604 | if (is_string($value)) |
605 | {
|
605 | {
|
606 | $value = "'" . $value . "'"; |
606 | $value = "'" . $value . "'"; |
607 | }
|
607 | }
|
608 | }
|
608 | }
|
609 | 609 | ||
610 | $values = ' VALUES (' . implode(', ', $values) . ')'; |
610 | $values = ' VALUES (' . implode(', ', $values) . ')'; |
611 | }
|
611 | }
|
612 | }
|
612 | }
|
613 | 613 | ||
614 | /* TODO: Should escape table names with escapeName(), but what about aliases? */
|
614 | /* TODO: Should escape table names with escapeName(), but what about aliases? */
|
615 | $query = "INSERT INTO {$table} {$cols} {$values}"; |
615 | $query = "INSERT INTO {$table} {$cols} {$values}"; |
616 | 616 | ||
617 | $stmt = $this->prepare($query); |
617 | $stmt = $this->prepare($query); |
618 | 618 | ||
619 | /* DEBUG */
|
619 | /* DEBUG */
|
620 | if (defined('DEBUG') && DEBUG > 1) |
620 | if (defined('DEBUG') && DEBUG > 1) |
621 | {
|
621 | {
|
622 | debug(array( |
622 | debug(array( |
623 | 'query' => $query, |
623 | 'query' => $query, |
624 | 'params' => $params |
624 | 'params' => $params |
625 | )); |
625 | )); |
626 | }
|
626 | }
|
627 | 627 | ||
628 | $success =& $this->_lastSuccess; |
628 | $success =& $this->_lastSuccess; |
629 | $success = $stmt->execute($params); |
629 | $success = $stmt->execute($params); |
630 | 630 | ||
631 | $errorInfo =& $this->_lastError; |
631 | $errorInfo =& $this->_lastError; |
632 | $errorInfo = $stmt->errorInfo(); |
632 | $errorInfo = $stmt->errorInfo(); |
633 | 633 | ||
634 | $this->_setLastInsertId(); |
634 | $this->_setLastInsertId(); |
635 | 635 | ||
636 | $result =& $this->_lastResult; |
636 | $result =& $this->_lastResult; |
637 | $result = $stmt->fetchAll(); |
637 | $result = $stmt->fetchAll(); |
638 | 638 | ||
639 | if (defined('DEBUG') && DEBUG > 1) |
639 | if (defined('DEBUG') && DEBUG > 1) |
640 | {
|
640 | {
|
641 | debug(array( |
641 | debug(array( |
642 | '_lastSuccess' => $success, |
642 | '_lastSuccess' => $success, |
643 | '_lastError' => $errorInfo, |
643 | '_lastError' => $errorInfo, |
644 | '_lastInsertId' => $this->_lastInsertId, |
644 | '_lastInsertId' => $this->_lastInsertId, |
645 | '_lastResult' => $result |
645 | '_lastResult' => $result |
646 | )); |
646 | )); |
647 | }
|
647 | }
|
648 | 648 | ||
649 | return $success; |
649 | return $success; |
650 | }
|
650 | }
|
651 | 651 | ||
652 | /**
|
652 | /**
|
653 | * Retrieves all rows from a table
|
653 | * Retrieves all rows from a table
|
654 | *
|
654 | *
|
655 | * @param int[optional] $fetch_style
|
655 | * @param int[optional] $fetch_style
|
656 | * @param int[optional] $column_index
|
656 | * @param int[optional] $column_index
|
657 | * @param array[optional] $ctor_args
|
657 | * @param array[optional] $ctor_args
|
658 | * @return array
|
658 | * @return array
|
659 | * @see PDOStatement::fetchAll()
|
659 | * @see PDOStatement::fetchAll()
|
660 | */
|
660 | */
|
661 | public function fetchAll($table, $fetch_style = null, $column_index = null, array $ctor_args = null) |
661 | public function fetchAll($table, $fetch_style = null, $column_index = null, array $ctor_args = null) |
662 | {
|
662 | {
|
663 | /* NOTE: Cannot use table name as statement parameter */
|
663 | /* NOTE: Cannot use table name as statement parameter */
|
664 | $stmt = $this->prepare("SELECT * FROM $table"); |
664 | $stmt = $this->prepare("SELECT * FROM $table"); |
665 | $this->_lastSuccess = $stmt->execute(); |
665 | $this->_lastSuccess = $stmt->execute(); |
666 | 666 | ||
667 | $this->_lastError = $stmt->errorInfo(); |
667 | $this->_lastError = $stmt->errorInfo(); |
668 | 668 | ||
669 | $result =& $this->_lastResult; |
669 | $result =& $this->_lastResult; |
670 | 670 | ||
671 | if (is_null($fetch_style)) |
671 | if (is_null($fetch_style)) |
672 | {
|
672 | {
|
673 | $fetch_style = PDO::FETCH_ASSOC; |
673 | $fetch_style = PDO::FETCH_ASSOC; |
674 | }
|
674 | }
|
675 | 675 | ||
676 | if (!is_null($ctor_args)) |
676 | if (!is_null($ctor_args)) |
677 | {
|
677 | {
|
678 | $result = $stmt->fetchAll($fetch_style, $column_index, $ctor_args); |
678 | $result = $stmt->fetchAll($fetch_style, $column_index, $ctor_args); |
679 | }
|
679 | }
|
680 | else if (!is_null($column_index)) |
680 | else if (!is_null($column_index)) |
681 | {
|
681 | {
|
682 | $result = $stmt->fetchAll($fetch_style, $column_index); |
682 | $result = $stmt->fetchAll($fetch_style, $column_index); |
683 | }
|
683 | }
|
684 | else if (!is_null($fetch_style)) |
684 | else if (!is_null($fetch_style)) |
685 | {
|
685 | {
|
686 | $result = $stmt->fetchAll($fetch_style); |
686 | $result = $stmt->fetchAll($fetch_style); |
687 | }
|
687 | }
|
688 | else
|
688 | else
|
689 | {
|
689 | {
|
690 | $result = $stmt->fetchAll(); |
690 | $result = $stmt->fetchAll(); |
691 | }
|
691 | }
|
692 | 692 | ||
693 | return $result; |
693 | return $result; |
694 | }
|
694 | }
|
695 | 695 | ||
696 | /**
|
696 | /**
|
697 | * Deletes one or more records
|
697 | * Deletes one or more records
|
698 | *
|
698 | *
|
699 | * @param string|array $tables
|
699 | * @param string|array $tables
|
700 | * Table name(s)
|
700 | * Table name(s)
|
701 | * @param array|string $where
|
701 | * @param array|string $where
|
702 | * Only the records matching this condition are deleted
|
702 | * Only the records matching this condition are deleted
|
703 | * @return bool
|
703 | * @return bool
|
704 | * @see PDOStatement::execute()
|
704 | * @see PDOStatement::execute()
|
705 | */
|
705 | */
|
706 | public function delete($tables, $where = null) |
706 | public function delete($tables, $where = null) |
707 | {
|
707 | {
|
708 | if (!$tables) |
708 | if (!$tables) |
709 | {
|
709 | {
|
710 | throw new InvalidArgumentException('No table specified'); |
710 | throw new InvalidArgumentException('No table specified'); |
711 | }
|
711 | }
|
712 | 712 | ||
713 | if (is_array($tables)) |
713 | if (is_array($tables)) |
714 | {
|
714 | {
|
715 | $tables = implode(',', $tables); |
715 | $tables = implode(',', $tables); |
716 | }
|
716 | }
|
717 | 717 | ||
718 | $params = array(); |
718 | $params = array(); |
719 | 719 | ||
720 | $query = "DELETE FROM {$tables}" . $this->_where($where); |
720 | $query = "DELETE FROM {$tables}" . $this->_where($where); |
721 | 721 | ||
722 | $stmt = $this->prepare($query); |
722 | $stmt = $this->prepare($query); |
723 | 723 | ||
724 | if ($this->_isAssociativeArray($where)) |
724 | if ($this->_isAssociativeArray($where)) |
725 | {
|
725 | {
|
726 | foreach ($where as $column => $condition) |
726 | foreach ($where as $column => $condition) |
727 | {
|
727 | {
|
728 | if (is_array($condition) && $this->_isAssociativeArray($condition)) |
728 | if (is_array($condition) && $this->_isAssociativeArray($condition)) |
729 | {
|
729 | {
|
730 | reset($condition); |
730 | reset($condition); |
731 | $condition = $condition[key($condition)]; |
731 | $condition = $condition[key($condition)]; |
732 | 732 | ||
733 | if (is_array($condition)) |
733 | if (is_array($condition)) |
734 | {
|
734 | {
|
735 | foreach (self::_expand($condition, $column) as $param_index => $param_name) |
735 | foreach (self::_expand($condition, $column) as $param_index => $param_name) |
736 | {
|
736 | {
|
737 | $params[$param_name] = $condition[$param_index]; |
737 | $params[$param_name] = $condition[$param_index]; |
738 | }
|
738 | }
|
739 | }
|
739 | }
|
740 | }
|
740 | }
|
741 | else
|
741 | else
|
742 | {
|
742 | {
|
743 | $params[":{$column}"] = $condition; |
743 | $params[":{$column}"] = $condition; |
744 | }
|
744 | }
|
745 | }
|
745 | }
|
746 | }
|
746 | }
|
747 | 747 | ||
748 | /* DEBUG */
|
748 | /* DEBUG */
|
749 | if (defined('DEBUG') && DEBUG > 1) |
749 | if (defined('DEBUG') && DEBUG > 1) |
750 | {
|
750 | {
|
751 | debug(array( |
751 | debug(array( |
752 | 'query' => $query, |
752 | 'query' => $query, |
753 | 'params' => $params |
753 | 'params' => $params |
754 | )); |
754 | )); |
755 | }
|
755 | }
|
756 | 756 | ||
757 | $success =& $this->_lastSuccess; |
757 | $success =& $this->_lastSuccess; |
758 | $success = $stmt->execute($params); |
758 | $success = $stmt->execute($params); |
759 | 759 | ||
760 | $result =& $this->_lastResult; |
760 | $result =& $this->_lastResult; |
761 | $result = $stmt->fetchAll(); |
761 | $result = $stmt->fetchAll(); |
762 | 762 | ||
763 | $errorInfo =& $this->_lastError; |
763 | $errorInfo =& $this->_lastError; |
764 | $errorInfo = $stmt->errorInfo(); |
764 | $errorInfo = $stmt->errorInfo(); |
765 | 765 | ||
766 | if (defined('DEBUG') && DEBUG > 1) |
766 | if (defined('DEBUG') && DEBUG > 1) |
767 | {
|
767 | {
|
768 | debug(array( |
768 | debug(array( |
769 | '_lastSuccess' => $success, |
769 | '_lastSuccess' => $success, |
770 | '_lastError' => $errorInfo, |
770 | '_lastError' => $errorInfo, |
771 | '_lastResult' => $result |
771 | '_lastResult' => $result |
772 | )); |
772 | )); |
773 | }
|
773 | }
|
774 | 774 | ||
775 | return $success; |
775 | return $success; |
776 | }
|
776 | }
|
777 | }
|
777 | }
|