Subversion Repositories PHPX

Rev

Rev 66 | Rev 69 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
27 PointedEar 1
<?php
2
 
51 PointedEar 3
namespace PointedEars\PHPX\Db;
4
 
29 PointedEar 5
require_once __DIR__ . '/../global.inc';
27 PointedEar 6
 
7
/**
8
 * Generic database model class using PDO (PHP Data Objects)
9
 *
41 PointedEar 10
 * @property-read PDO $connection
11
 *   Database connection.  Established on read access to this
12
 *   property if not yet established.
27 PointedEar 13
 * @property-read array $lastError
14
 *   Last error information of the database operation.
15
 *   See {@link PDOStatement::errorInfo()}.
16
 * @property-read string $lastInsertId
17
 *   ID of the last inserted row, or the last value from a sequence object,
18
 *   depending on the underlying driver. May not be supported by all databases.
19
 * @property-read array $lastResult
20
 *   Last result of the database operation
21
 * @property-read boolean $lastSuccess
22
 *   Last success value of the database operation
23
 * @author Thomas Lahn
24
 */
51 PointedEar 25
class Database extends \PointedEars\PHPX\AbstractModel
27 PointedEar 26
{
32 PointedEar 27
  /* Access properties */
41 PointedEar 28
 
27 PointedEar 29
  /**
30
   * DSN of the database
31
   * @var string
32
   */
33
  protected $_dsn = '';
41 PointedEar 34
 
27 PointedEar 35
  /**
36
   * Username to access the database
37
   * @var string
38
   */
39
  protected $_username;
41 PointedEar 40
 
27 PointedEar 41
  /**
42
   * Password to access the database
43
   * @var string
44
   */
45
  protected $_password;
41 PointedEar 46
 
27 PointedEar 47
  /**
48
   * PDO driver-specific options
49
   * @var array
50
   */
51
  protected $_options = array();
32 PointedEar 52
 
53
  /**
54
   * Database-specific string to use for quoting a name or value
55
   * left-hand side (for security reasons and to prevent a name
56
   * from being parsed as a keyword).
57
   * @var string
58
   */
59
  protected $_leftQuote = '';
41 PointedEar 60
 
27 PointedEar 61
  /**
32 PointedEar 62
   * Database-specific string to use for quoting a name or value
63
   * left-hand side (for security reasons and to prevent a name
64
   * from being parsed as a keyword).
65
   * @var string
66
   */
67
  protected $_rightQuote = '';
41 PointedEar 68
 
32 PointedEar 69
  /* Status properties */
41 PointedEar 70
 
32 PointedEar 71
  /**
27 PointedEar 72
   * Database connection
73
   * @var PDO
74
   */
75
  protected $_connection;
41 PointedEar 76
 
27 PointedEar 77
  /**
78
   * Last success value of the database operation
79
   * @var boolean
80
   */
81
  protected $_lastSuccess;
82
 
83
  /**
84
   * Last error information of the database operation
85
   * @var array
86
   */
87
  protected $_lastError;
41 PointedEar 88
 
27 PointedEar 89
  /**
90
   * Last result of the database operation
91
   * @var array
92
   */
93
  protected $_lastResult;
94
 
95
  /**
96
  * ID of the last inserted row, or the last value from a sequence object,
97
  * depending on the underlying driver. May not be supported by all databases.
98
  * @var string
99
  */
100
  protected $_lastInsertId = '';
41 PointedEar 101
 
34 PointedEar 102
  /**
103
   * Creates a new <code>Database</code> instance.
104
   *
105
   * Each of the parameters is optional and can also be given
106
   * by a protected property where the parameter name is preceded
107
   * by <code>_</code>.  Parameter values overwrite the default
108
   * property values.  It is recommended to use default property
109
   * values of inheriting classes except for small applications
110
   * and testing purposes.
111
   *
112
   * @param string $dsn
113
   * @param string $username
114
   * @param string $password
44 PointedEar 115
   * @param array $options
116
   * @see PDO::__construct()
34 PointedEar 117
   */
44 PointedEar 118
  public function __construct ($dsn = '', $username = null,
34 PointedEar 119
    $password = null, array $options = array())
27 PointedEar 120
  {
34 PointedEar 121
    if ($dsn !== '')
122
    {
123
      $this->_dsn = $dsn;
124
    }
41 PointedEar 125
 
34 PointedEar 126
    if ($username !== null)
127
    {
128
      $this->_username = $username;
129
    }
41 PointedEar 130
 
34 PointedEar 131
    if ($password !== null)
132
    {
133
      $this->_password = $password;
134
    }
41 PointedEar 135
 
34 PointedEar 136
    if ($options)
137
    {
138
      $this->_options = $options;
139
    }
27 PointedEar 140
  }
41 PointedEar 141
 
27 PointedEar 142
  /**
47 PointedEar 143
   * Reads the connection configuration for this database
144
   * from the configuration file, application/.config
145
   *
146
   * There must be an INI section named "database:" followed
147
   * by the value of the <code>$_dbname</code> property
148
   * containing keys and values for the properties of the
149
   * <code>Database</code> instance.  Except for the key
150
   * <code>dbname</code>, which allows for aliases, all
151
   * keys are ignored if the corresponding properties
152
   * were set.  That is, definitions in the class file
153
   * override those in the configuration file.
154
   *
68 PointedEar 155
   * @return array|boolean
156
   *   The configuration array if the configuration
157
   *   file could be read, <code>false</code> otherwise.
47 PointedEar 158
   */
159
  public function readConfig ()
160
  {
161
        $config = parse_ini_file('application/.config', true);
162
        if ($config !== false)
163
        {
164
                $section = 'database:' . $this->_dbname;
165
                if (isset($config[$section]))
166
                {
167
                        $dbconfig = $config[$section];
68 PointedEar 168
                        $options = array(
169
                    'host', 'port', 'dbname', 'username', 'password', 'charset'
170
                        );
171
 
172
                        foreach ($options as $key)
47 PointedEar 173
                        {
174
                                $property = "_$key";
175
                                if (isset($dbconfig[$key])
176
                                     && $key == 'dbname'
177
                                                 || (property_exists($this, $property)
178
                                                                  && $this->$property === null))
179
                                {
180
                                        $this->$property = $dbconfig[$key];
181
                                }
182
                        }
68 PointedEar 183
 
184
                        return $config[$section];
47 PointedEar 185
                }
186
        }
187
 
188
        return $config;
189
  }
190
 
191
  /**
41 PointedEar 192
   * @return PDO
193
   */
194
  public function getConnection ()
195
  {
196
    if ($this->_connection === null)
197
    {
198
      $this->_connection =
51 PointedEar 199
        new \PDO($this->_dsn, $this->_username, $this->_password, $this->_options);
41 PointedEar 200
    }
201
 
202
    return $this->_connection;
203
  }
204
 
205
  /**
44 PointedEar 206
   * Creates a database according to the specified parameters
207
   *
208
   * Should be overwritten and called by inheriting classes.
209
   *
210
   * @param string $dsn
211
   *   Connection DSN (required; must not include the database
212
   *   name).
213
   * @param string $username = null
214
   *   Connection username.  The default is specified by the
215
   *   <code>$_username</code> property.  Note that creating
216
   *   the database usually requires a user with more privileges
217
   *   than the one accessing the database or its tables.
218
   * @param string $password = null
219
   *   Connection password.  The default is specified by the
220
   *   <code>$_password</code> property.
221
   * @param array? $options = null
222
   *   Connection options.  The default is specified by the
223
   *   <code>$_options</code> property.
224
   * @param string $spec = null
225
   *   Additional database specifications, like character encoding
226
   *   and collation.
227
   * @param boolean $force = false
228
   *   If a true-value, the database will be attempted to be
229
   *   created even if there is a database of the name specified
230
   *   by the <code>$_dbname</code> property.
231
   * @return int
232
   *   The number of rows affected by the CREATE DATABASE statement.
233
   * @see PDO::__construct()
234
   * @see PDO::exec()
235
   */
236
  public function create ($dsn, $username = null, $password = null,
237
    array $options = null, $dbspec = null, $force = false)
238
  {
58 PointedEar 239
    $connection = new \PDO($dsn,
44 PointedEar 240
      $username !== null ? $username : $this->_username,
241
      $password !== null ? $password : $this->_password,
242
      $options !== null ? $options : $this->_options);
243
 
244
    $query = 'CREATE DATABASE'
245
           . (!$force ? ' IF NOT EXISTS' : '')
246
           . ' ' . $this->escapeName($this->_dbname)
247
           . ($dbspec ? ' ' . $dbspec : '');
248
 
249
    return $connection->exec($query);
250
  }
251
 
252
  /**
58 PointedEar 253
   * Maps column meta-information to a column definition.
254
   *
255
   * Should be overwritten and called by inheriting classes.
256
   *
257
   * @todo
258
   * @param array $value
259
   * @param string $column_name
260
   * @return string
261
   */
262
  protected function _columnDef (array $data, $column_name)
263
  {
264
        $def = (isset($data['unsigned']) && $data['unsigned'] ? 'UNSIGNED ' : '')
265
                         . $data['type']
266
                         . (isset($data['not_null']) && $data['not_null'] ? ' NOT NULL'                     : ' NULL')
267
                         . (isset($data['default'])  && $data['default']  ? " DEFAULT {$data['default']}"   : '')
268
                         . (isset($data['auto_inc']) && $data['auto_inc'] ? ' AUTO_INCREMENT'               : '')
269
                         . (isset($data['unique'])   && $data['unique']   ? ' UNIQUE KEY'                   : '')
270
                         . (isset($data['primary'])  && $data['primary']  ? ' PRIMARY KEY'                  : '')
271
                         . (isset($data['comment'])  && $data['comment']  ? " COMMENT '{$data['comment']}'" : '');
272
 
273
        return $this->escapeName($column_name) . ' ' . $def;
274
  }
275
 
276
  /**
277
   * Creates a database table according to the specified parameters
278
   *
279
   * @todo
280
   * @param string $name
281
   * @param array $columns
282
   * @param array $options = null
283
   * @return bool
284
   * @see PDOStatement::execute()
285
   */
286
  public function createTable ($name, array $columns, array $options = null)
287
  {
288
        $class = \get_class($this);
289
        $query = 'CREATE TABLE '
290
          . $this->escapeName($name)
291
          . '('
292
          . array_map(array($this, '_columnDef'), $columns, array_keys($columns)) . ')';
293
 
294
        $stmt = $this->prepare($query);
295
 
296
        /* DEBUG */
297
    if (defined('DEBUG') && DEBUG > 1)
298
    {
299
      debug(array(
300
        'query'  => $query,
301
      ));
302
    }
303
 
304
    $success =& $this->_lastSuccess;
305
    $success =  $stmt->execute();
306
 
307
    $errorInfo =& $this->_lastError;
308
    $errorInfo =  $stmt->errorInfo();
309
 
310
    $this->_resetLastInsertId();
311
 
312
    $result =& $this->_lastResult;
313
    $result =  $stmt->fetchAll();
314
 
315
    if (defined('DEBUG') && DEBUG > 1)
316
    {
317
      debug(array(
318
        '_lastSuccess' => $success,
319
        '_lastError'    => $errorInfo,
320
        '_lastResult'  => $result
321
      ));
322
    }
323
 
324
    return $success;
325
  }
326
 
327
  /**
27 PointedEar 328
   * Initiates a transaction
329
   *
330
   * @return bool
331
   * @see PDO::beginTransaction()
332
   */
333
  public function beginTransaction()
334
  {
41 PointedEar 335
    return $this->connection->beginTransaction();
27 PointedEar 336
  }
41 PointedEar 337
 
27 PointedEar 338
  /**
339
   * Rolls back a transaction
340
   *
341
   * @return bool
342
   * @see PDO::rollBack()
343
   */
344
  public function rollBack()
345
  {
41 PointedEar 346
    return $this->connection->rollBack();
27 PointedEar 347
  }
41 PointedEar 348
 
27 PointedEar 349
  /**
350
   * Commits a transaction
351
   *
352
   * @return bool
353
   * @see PDO::commit()
354
   */
355
  public function commit()
356
  {
41 PointedEar 357
    return $this->connection->commit();
27 PointedEar 358
  }
41 PointedEar 359
 
27 PointedEar 360
  /**
361
   * Prepares a statement for execution with the database
362
   * @param string $query
363
   */
364
  public function prepare($query, array $driver_options = array())
365
  {
41 PointedEar 366
    return $this->connection->prepare($query, $driver_options);
27 PointedEar 367
  }
41 PointedEar 368
 
27 PointedEar 369
  /**
370
   * Returns the ID of the last inserted row, or the last value from
371
   * a sequence object, depending on the underlying driver.
372
   *
373
   * @return int
374
   */
375
  public function getLastInsertId()
376
  {
377
    return $this->_lastInsertId;
378
  }
41 PointedEar 379
 
27 PointedEar 380
  /**
381
   * Escapes a database name so that it can be used in a query.
382
   *
383
   * @param string $name
384
   *   The name to be escaped
385
   * @return string
386
   *   The escaped name
387
   */
388
  public function escapeName($name)
389
  {
32 PointedEar 390
    return $this->_leftQuote . $name . $this->_rightQuote;
27 PointedEar 391
  }
41 PointedEar 392
 
27 PointedEar 393
  /**
394
   * Determines if an array is associative (has not all integer keys).
395
   *
396
   * @author
397
   *   Algorithm courtesy of squirrel, <http://stackoverflow.com/a/5969617/855543>.
398
   * @param array $a
399
   * @return boolean
400
   *   <code>true</code> if <var>$a</var> is associative,
401
   *   <code>false</code> otherwise
402
   */
403
  protected function _isAssociativeArray(array $a)
404
  {
405
    for (reset($a); is_int(key($a)); next($a));
406
    return !is_null(key($a));
407
  }
41 PointedEar 408
 
27 PointedEar 409
  /**
410
   * Escapes an associative array so that its string representation can be used
411
   * as list with table or column aliases in a query.
412
   *
413
   * This method does not actually escape anything; it only inserts the
414
   * 'AS' keyword.  It should be overridden by inheriting methods.
415
   *
416
   * NOTE: This method intentionally does not check whether the array actually
417
   * is associative.
418
   *
419
   * @param array &$array
420
   *   The array to be escaped
421
   * @return array
422
   *   The escaped array
423
   */
424
  protected function _escapeAliasArray(array &$array)
425
  {
426
    foreach ($array as $column => &$value)
427
    {
32 PointedEar 428
      $quotedColumn = $column;
429
      if (strpos($column, $this->_leftQuote) === false
430
         && strpos($column, $this->_rightQuote) === false)
431
      {
432
        $quotedColumn = $this->_leftQuote . $column . $this->_rightQuote;
433
      }
41 PointedEar 434
 
32 PointedEar 435
      $value = $value . ' AS ' . $quotedColumn;
27 PointedEar 436
    }
41 PointedEar 437
 
27 PointedEar 438
    return $array;
439
  }
440
 
441
  /**
442
   * @param array $a
443
   * @param string $prefix
444
   */
445
  private static function _expand(array $a, $prefix)
446
  {
447
    $a2 = array();
41 PointedEar 448
 
27 PointedEar 449
    foreach ($a as $key => $value)
450
    {
451
      $a2[] = ':' . $prefix . ($key + 1);
452
    }
41 PointedEar 453
 
27 PointedEar 454
    return $a2;
455
  }
41 PointedEar 456
 
27 PointedEar 457
  /**
458
   * Escapes an associative array so that its string representation can be used
459
   * as value list in a query.
460
   *
461
   * This method should be overridden by inheriting classes to escape
462
   * column names as fitting for the database schema they support.  It is
463
   * strongly recommended that the overriding methods call this method with
464
   * an appropriate <var>$escape</var> parameter, pass all other parameters
465
   * on unchanged, and return its return value.
466
   *
467
   * NOTE: Intentionally does not check whether the array actually is associative!
468
   *
469
   * @param array &$array
470
   *   The array to be escaped
471
   * @param string $suffix
472
   *   The string to be appended to the column name for the value placeholder.
473
   *   The default is the empty string.
474
   * @param array $escape
475
   *   The strings to use left-hand side (index 0) and right-hand side (index 1)
476
   *   of the column name.  The default is the empty string, respectively.
477
   * @return array
478
   *   The escaped array
479
   */
32 PointedEar 480
  protected function _escapeValueArray(array &$array, $suffix = '')
27 PointedEar 481
  {
482
    $result = array();
41 PointedEar 483
 
27 PointedEar 484
    foreach ($array as $column => $value)
485
    {
486
      $op = '=';
487
      $placeholder = ":{$column}";
41 PointedEar 488
 
27 PointedEar 489
      if (is_array($value) && $this->_isAssociativeArray($value))
490
      {
491
        reset($value);
492
        $op = ' ' . key($value) . ' ';
41 PointedEar 493
 
27 PointedEar 494
        $value = $value[key($value)];
495
      }
41 PointedEar 496
 
27 PointedEar 497
      if (is_array($value))
498
      {
33 PointedEar 499
        $placeholder = '(' . implode(', ', self::_expand($value, $column)) . ')';
27 PointedEar 500
      }
41 PointedEar 501
 
32 PointedEar 502
      $result[] = $this->_leftQuote . $column . $this->_rightQuote . "{$op}{$placeholder}{$suffix}";
27 PointedEar 503
    }
41 PointedEar 504
 
27 PointedEar 505
    return $result;
506
  }
41 PointedEar 507
 
27 PointedEar 508
  /**
509
   * Constructs the WHERE part of a query
510
   *
511
   * @param string|array $where
512
   *   Condition
513
   * @param string $suffix
514
   *   The string to be appended to the column name for the value placeholder,
515
   *   passed on to {@link Database::_escapeValueArray()}.  The default is
516
   *   the empty string.
517
   * @return string
518
   * @see Database::_escapeValueArray()
519
   */
520
  protected function _where($where, $suffix = '')
521
  {
522
    if (!is_null($where))
523
    {
524
      if (is_array($where))
525
      {
526
        if (count($where) < 1)
527
        {
528
          return '';
529
        }
41 PointedEar 530
 
27 PointedEar 531
        if ($this->_isAssociativeArray($where))
532
        {
533
          $where = $this->_escapeValueArray($where, $suffix);
534
        }
41 PointedEar 535
 
27 PointedEar 536
        $where = '(' . implode(') AND (', $where) . ')';
537
      }
41 PointedEar 538
 
27 PointedEar 539
      return ' WHERE ' . $where;
540
    }
41 PointedEar 541
 
27 PointedEar 542
    return '';
543
  }
544
 
545
  /**
546
   * Selects data from one or more tables; the resulting records are stored
547
   * in the <code>result</code> property and returned as an associative array,
548
   * where the keys are the column (alias) names.
549
   *
550
   * @param string|array[string] $tables Table(s) to select from
551
   * @param string|array[string] $columns Column(s) to select from (optional)
552
   * @param string|array $where Condition (optional)
553
   * @param string $order Sort order (optional)
554
   *   If provided, MUST start with ORDER BY or GROUP BY
555
   * @param string $limit Limit (optional)
556
   * @param int $fetch_style
557
   *   The mode that should be used for {@link PDOStatement::fetchAll()}.
558
   *   The default is {@link PDO::FETCH_ASSOC}.
559
   * @return array
560
   * @see Database::prepare()
561
   * @see PDOStatement::fetchAll()
562
   */
563
  public function select($tables, $columns = null, $where = null,
51 PointedEar 564
    $order = null, $limit = null, $fetch_style = \PDO::FETCH_ASSOC)
27 PointedEar 565
  {
566
    if (is_null($columns))
567
    {
568
      $columns = array('*');
569
    }
41 PointedEar 570
 
27 PointedEar 571
    if (is_array($columns))
572
    {
573
      if ($this->_isAssociativeArray($columns))
574
      {
575
        $columns = $this->_escapeAliasArray($columns);
576
      }
577
 
33 PointedEar 578
      $columns = implode(', ', $columns);
27 PointedEar 579
    }
580
 
581
    if (is_array($tables))
582
    {
583
      if ($this->_isAssociativeArray($columns))
584
      {
585
        $columns = $this->_escapeAliasArray($columns);
586
      }
587
 
33 PointedEar 588
      $tables = implode(', ', $tables);
27 PointedEar 589
    }
590
 
591
    $query = "SELECT {$columns} FROM {$tables}" . $this->_where($where);
592
 
593
    if (!is_null($order))
594
    {
595
      if (is_array($order))
596
      {
33 PointedEar 597
        $order = 'ORDER BY ' . implode(', ', $order);
27 PointedEar 598
      }
41 PointedEar 599
 
27 PointedEar 600
      $query .= " $order";
601
    }
602
 
603
    if (!is_null($limit))
604
    {
605
      $query .= " LIMIT $limit";
606
    }
41 PointedEar 607
 
27 PointedEar 608
    $stmt = $this->prepare($query);
609
 
610
    $params = array();
41 PointedEar 611
 
27 PointedEar 612
    if (is_array($where) && $this->_isAssociativeArray($where))
613
    {
34 PointedEar 614
      /* FIXME: Export and reuse this */
27 PointedEar 615
      foreach ($where as $column => $condition)
616
      {
34 PointedEar 617
        /* TODO: Also handle function calls as keys */
27 PointedEar 618
        if (is_array($condition) && $this->_isAssociativeArray($condition))
619
        {
620
          reset($condition);
621
          $condition = $condition[key($condition)];
41 PointedEar 622
 
27 PointedEar 623
          if (is_array($condition))
624
          {
625
            foreach (self::_expand($condition, $column) as $param_index => $param_name)
626
            {
627
              $params[$param_name] = $condition[$param_index];
628
            }
629
          }
630
        }
631
        else
632
        {
633
          $params[":{$column}"] = $condition;
634
        }
635
      }
636
    }
637
 
638
    /* DEBUG */
639
    if (defined('DEBUG') && DEBUG > 1)
640
    {
641
      debug(array(
642
        'query'  => $query,
643
        'params' => $params
644
      ));
645
    }
41 PointedEar 646
 
27 PointedEar 647
    $success =& $this->_lastSuccess;
648
    $success =  $stmt->execute($params);
41 PointedEar 649
 
27 PointedEar 650
    $errorInfo =& $this->_lastError;
651
    $errorInfo =  $stmt->errorInfo();
41 PointedEar 652
 
27 PointedEar 653
    $result =& $this->_lastResult;
654
    $result =  $stmt->fetchAll($fetch_style);
41 PointedEar 655
 
27 PointedEar 656
    if (defined('DEBUG') && DEBUG > 1)
657
    {
658
      debug(array(
659
        '_lastSuccess' => $success,
660
        '_lastError'   => $errorInfo,
661
        '_lastResult'  => $result
662
      ));
663
    }
41 PointedEar 664
 
27 PointedEar 665
    return $result;
666
  }
667
 
668
  /**
669
   * Sets and returns the ID of the last inserted row, or the last value from
670
   * a sequence object, depending on the underlying driver.
671
   *
672
   * @param string $name
673
   *   Name of the sequence object from which the ID should be returned.
674
   * @return string
675
   */
676
  protected function _setLastInsertId($name = null)
677
  {
41 PointedEar 678
    return ($this->_lastInsertId = $this->connection->lastInsertId($name));
27 PointedEar 679
  }
680
 
681
  /**
682
   * Resets the the ID of the last inserted row, or the last value from
683
   * a sequence object, depending on the underlying driver.
684
   *
685
   * @return string
686
   *   The default value
687
   */
688
  protected function _resetLastInsertId()
689
  {
690
    return ($this->_lastInsertId = '');
691
  }
41 PointedEar 692
 
27 PointedEar 693
  /**
694
   * Updates one or more records
695
   *
696
   * @param string|array $tables
697
   *   Table name
48 PointedEar 698
   * @param array $updates
27 PointedEar 699
   *   Associative array of column-value pairs
700
   * @param array|string $where
701
   *   Only the records matching this condition are updated
702
   * @return bool
61 PointedEar 703
   * @see PDOStatement::execute()
27 PointedEar 704
   */
61 PointedEar 705
  public function update ($tables, array $updates, $where = null)
27 PointedEar 706
  {
707
    if (!$tables)
708
    {
709
      throw new InvalidArgumentException('No table specified');
710
    }
41 PointedEar 711
 
27 PointedEar 712
    if (is_array($tables))
713
    {
33 PointedEar 714
      $tables = implode(', ', $tables);
27 PointedEar 715
    }
41 PointedEar 716
 
27 PointedEar 717
    if (!$updates)
718
    {
719
      throw new InvalidArgumentException('No values specified');
720
    }
721
 
722
    $params = array();
41 PointedEar 723
 
27 PointedEar 724
    if ($this->_isAssociativeArray($updates))
725
    {
726
      foreach ($updates as $key => $condition)
727
      {
728
        $params[":{$key}"] = $condition;
729
      }
730
    }
41 PointedEar 731
 
33 PointedEar 732
    $updates = implode(', ', $this->_escapeValueArray($updates));
41 PointedEar 733
 
27 PointedEar 734
    /* TODO: Should escape table names with escapeName(), but what about aliases? */
735
    $query = "UPDATE {$tables} SET {$updates}" . $this->_where($where, '2');
41 PointedEar 736
 
27 PointedEar 737
    $stmt = $this->prepare($query);
41 PointedEar 738
 
27 PointedEar 739
    if (is_array($where) && $this->_isAssociativeArray($where))
740
    {
741
      foreach ($where as $column => $condition)
742
      {
743
        if (is_array($condition) && $this->_isAssociativeArray($condition))
744
        {
745
          reset($condition);
746
          $condition = $condition[key($condition)];
41 PointedEar 747
 
27 PointedEar 748
          if (is_array($condition))
749
          {
750
            foreach (self::_expand($condition, $column) as $param_index => $param_name)
751
            {
752
              $params[$param_name] = $condition[$param_index];
753
            }
754
          }
755
        }
756
        else
757
        {
758
          $params[":{$column}2"] = $condition;
759
        }
760
      }
761
    }
762
 
763
    /* DEBUG */
764
    if (defined('DEBUG') && DEBUG > 1)
765
    {
766
      debug(array(
767
        'query'  => $query,
768
        'params' => $params
769
      ));
770
    }
41 PointedEar 771
 
27 PointedEar 772
    $success =& $this->_lastSuccess;
773
    $success =  $stmt->execute($params);
41 PointedEar 774
 
27 PointedEar 775
    $errorInfo =& $this->_lastError;
776
    $errorInfo =  $stmt->errorInfo();
41 PointedEar 777
 
27 PointedEar 778
    $this->_resetLastInsertId();
41 PointedEar 779
 
27 PointedEar 780
    $result =& $this->_lastResult;
781
    $result =  $stmt->fetchAll();
41 PointedEar 782
 
27 PointedEar 783
    if (defined('DEBUG') && DEBUG > 1)
784
    {
785
      debug(array(
786
        '_lastSuccess' => $success,
787
        '_lastError'    => $errorInfo,
788
        '_lastResult'  => $result
789
      ));
790
    }
41 PointedEar 791
 
27 PointedEar 792
    return $success;
793
  }
41 PointedEar 794
 
27 PointedEar 795
  /**
796
   * Inserts a record into a table.<p>The AUTO_INCREMENT value of the inserted
797
   * row, if any (> 0), is stored in the {@link $lastInsertId} property of
798
   * the <code>Database</code> instance.</p>
799
   *
800
   * @param string $table
801
   *   Table name
802
   * @param array|string $values
803
   *   Associative array of column-value pairs, indexed array,
804
   *   or comma-separated list of values.  If <var>$values</var> is not
805
   *   an associative array, <var>$cols</var> must be passed if the
806
   *   values are not in column order (see below).
807
   * @param array|string $cols
808
   *   Indexed array, or comma-separated list of column names.
809
   *   Needs only be passed if <var>$values</var> is not an associative array
810
   *   and the values are not in column order (default: <code>null</code>);
811
   *   is ignored otherwise.  <strong>You SHOULD NOT rely on column order.</strong>
812
   * @return bool
813
   * @see PDOStatement::execute()
814
   */
61 PointedEar 815
  public function insert ($table, $values, $cols = null)
27 PointedEar 816
  {
817
    if ($cols != null)
818
    {
819
      $cols = ' ('
820
            . (is_array($cols)
33 PointedEar 821
                ? implode(', ', array_map(array($this, 'escapeName'), $cols))
27 PointedEar 822
                : $cols) . ')';
823
    }
824
    else
825
    {
826
      $cols = '';
827
    }
41 PointedEar 828
 
27 PointedEar 829
    /* DEBUG */
830
    if (defined('DEBUG') && DEBUG > 2)
831
    {
832
      debug(array('values' => $values));
833
    }
41 PointedEar 834
 
27 PointedEar 835
    $params = array();
41 PointedEar 836
 
27 PointedEar 837
    if (is_array($values))
838
    {
839
      if ($this->_isAssociativeArray($values))
840
      {
841
        foreach ($values as $key => $condition)
842
        {
843
          $params[":{$key}"] = $condition;
844
        }
41 PointedEar 845
 
27 PointedEar 846
        $values = $this->_escapeValueArray($values);
41 PointedEar 847
 
27 PointedEar 848
        $cols = '';
849
        $values = 'SET ' . implode(', ', $values);
850
      }
851
      else
852
      {
853
        foreach ($values as &$value)
854
        {
855
          if (is_string($value))
856
          {
857
            $value = "'" . $value . "'";
858
          }
859
        }
41 PointedEar 860
 
66 PointedEar 861
        $values = 'VALUES (' . implode(', ', $values) . ')';
27 PointedEar 862
      }
863
    }
41 PointedEar 864
 
27 PointedEar 865
    /* TODO: Should escape table names with escapeName(), but what about aliases? */
866
    $query = "INSERT INTO {$table} {$cols} {$values}";
41 PointedEar 867
 
27 PointedEar 868
    $stmt = $this->prepare($query);
41 PointedEar 869
 
27 PointedEar 870
      /* DEBUG */
871
    if (defined('DEBUG') && DEBUG > 1)
872
    {
873
       debug(array(
874
         'query'  => $query,
875
         'params' => $params
876
       ));
877
    }
41 PointedEar 878
 
27 PointedEar 879
    $success =& $this->_lastSuccess;
880
    $success = $stmt->execute($params);
41 PointedEar 881
 
27 PointedEar 882
    $errorInfo =& $this->_lastError;
883
    $errorInfo =  $stmt->errorInfo();
41 PointedEar 884
 
27 PointedEar 885
    $this->_setLastInsertId();
41 PointedEar 886
 
27 PointedEar 887
    $result =& $this->_lastResult;
888
    $result =  $stmt->fetchAll();
889
 
890
    if (defined('DEBUG') && DEBUG > 1)
891
    {
892
      debug(array(
893
        '_lastSuccess'  => $success,
894
        '_lastError'    => $errorInfo,
895
        '_lastInsertId' => $this->_lastInsertId,
896
        '_lastResult'   => $result
897
      ));
898
    }
41 PointedEar 899
 
27 PointedEar 900
    return $success;
901
  }
41 PointedEar 902
 
27 PointedEar 903
  /**
904
   * Retrieves all rows from a table
905
   *
906
   * @param int[optional] $fetch_style
907
   * @param int[optional] $column_index
908
   * @param array[optional] $ctor_args
909
   * @return array
910
   * @see PDOStatement::fetchAll()
911
   */
912
  public function fetchAll($table, $fetch_style = null, $column_index = null, array $ctor_args = null)
913
  {
914
    /* NOTE: Cannot use table name as statement parameter */
915
    $stmt = $this->prepare("SELECT * FROM $table");
916
    $this->_lastSuccess = $stmt->execute();
41 PointedEar 917
 
27 PointedEar 918
    $this->_lastError = $stmt->errorInfo();
41 PointedEar 919
 
27 PointedEar 920
    $result =& $this->_lastResult;
41 PointedEar 921
 
27 PointedEar 922
    if (is_null($fetch_style))
923
    {
51 PointedEar 924
      $fetch_style = \PDO::FETCH_ASSOC;
27 PointedEar 925
    }
41 PointedEar 926
 
27 PointedEar 927
    if (!is_null($ctor_args))
928
    {
929
      $result = $stmt->fetchAll($fetch_style, $column_index, $ctor_args);
930
    }
931
    else if (!is_null($column_index))
932
    {
933
      $result = $stmt->fetchAll($fetch_style, $column_index);
934
    }
935
    else if (!is_null($fetch_style))
936
    {
937
      $result = $stmt->fetchAll($fetch_style);
938
    }
939
    else
940
    {
941
      $result = $stmt->fetchAll();
942
    }
41 PointedEar 943
 
27 PointedEar 944
    return $result;
945
  }
946
 
947
  /**
948
   * Deletes one or more records
949
   *
950
   * @param string|array $tables
951
   *   Table name(s)
952
   * @param array|string $where
953
   *   Only the records matching this condition are deleted
954
   * @return bool
955
   * @see PDOStatement::execute()
956
   */
957
  public function delete($tables, $where = null)
958
  {
959
    if (!$tables)
960
    {
961
      throw new InvalidArgumentException('No table specified');
962
    }
41 PointedEar 963
 
27 PointedEar 964
    if (is_array($tables))
965
    {
33 PointedEar 966
      $tables = implode(', ', $tables);
27 PointedEar 967
    }
41 PointedEar 968
 
27 PointedEar 969
    $params = array();
41 PointedEar 970
 
27 PointedEar 971
    $query = "DELETE FROM {$tables}" . $this->_where($where);
41 PointedEar 972
 
27 PointedEar 973
    $stmt = $this->prepare($query);
41 PointedEar 974
 
27 PointedEar 975
    if ($this->_isAssociativeArray($where))
976
    {
977
      foreach ($where as $column => $condition)
978
      {
979
        if (is_array($condition) && $this->_isAssociativeArray($condition))
980
        {
981
          reset($condition);
982
          $condition = $condition[key($condition)];
41 PointedEar 983
 
27 PointedEar 984
          if (is_array($condition))
985
          {
986
            foreach (self::_expand($condition, $column) as $param_index => $param_name)
987
            {
988
              $params[$param_name] = $condition[$param_index];
989
            }
990
          }
991
        }
992
        else
993
        {
994
          $params[":{$column}"] = $condition;
995
        }
996
      }
997
    }
998
 
999
    /* DEBUG */
1000
    if (defined('DEBUG') && DEBUG > 1)
1001
    {
1002
      debug(array(
1003
        'query'  => $query,
1004
        'params' => $params
1005
      ));
1006
    }
41 PointedEar 1007
 
27 PointedEar 1008
    $success =& $this->_lastSuccess;
1009
    $success =  $stmt->execute($params);
41 PointedEar 1010
 
27 PointedEar 1011
    $result =& $this->_lastResult;
1012
    $result =  $stmt->fetchAll();
41 PointedEar 1013
 
27 PointedEar 1014
    $errorInfo =& $this->_lastError;
1015
    $errorInfo =  $stmt->errorInfo();
41 PointedEar 1016
 
27 PointedEar 1017
    if (defined('DEBUG') && DEBUG > 1)
1018
    {
1019
      debug(array(
1020
        '_lastSuccess' => $success,
1021
        '_lastError'   => $errorInfo,
1022
        '_lastResult'  => $result
1023
      ));
1024
    }
41 PointedEar 1025
 
27 PointedEar 1026
    return $success;
1027
  }
1028
}