Subversion Repositories PHPX

Rev

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