Subversion Repositories PHPX

Rev

Rev 77 | 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}.
76 PointedEar 577
   * @param bool $fetchAll
578
   *   If <code>true</code> (default), fetches all rows at once.
579
   *   You can set this to <code>false</code> in case of memory problems,
580
   *   in which case this function will return the prepared
581
   *   {@link PDOStatement} instead of the result array.  You can then use
582
   *   {@link PDOStatement::fetch()} to get the returned rows iteratively.
78 PointedEar 583
   *   <var>$fetch_style</var> will be ignored then, so that you can safely
584
   *   pass <code>null</code> for it, for example.
76 PointedEar 585
   * @return array|PDOStatement
27 PointedEar 586
   * @see Database::prepare()
587
   * @see PDOStatement::fetchAll()
588
   */
589
  public function select($tables, $columns = null, $where = null,
76 PointedEar 590
    $order = null, $limit = null, $fetch_style = \PDO::FETCH_ASSOC,
591
    $fetchAll = true)
27 PointedEar 592
  {
593
    if (is_null($columns))
594
    {
595
      $columns = array('*');
596
    }
41 PointedEar 597
 
27 PointedEar 598
    if (is_array($columns))
599
    {
600
      if ($this->_isAssociativeArray($columns))
601
      {
602
        $columns = $this->_escapeAliasArray($columns);
603
      }
604
 
33 PointedEar 605
      $columns = implode(', ', $columns);
27 PointedEar 606
    }
607
 
608
    if (is_array($tables))
609
    {
610
      if ($this->_isAssociativeArray($columns))
611
      {
612
        $columns = $this->_escapeAliasArray($columns);
613
      }
614
 
33 PointedEar 615
      $tables = implode(', ', $tables);
27 PointedEar 616
    }
617
 
618
    $query = "SELECT {$columns} FROM {$tables}" . $this->_where($where);
619
 
620
    if (!is_null($order))
621
    {
622
      if (is_array($order))
623
      {
33 PointedEar 624
        $order = 'ORDER BY ' . implode(', ', $order);
27 PointedEar 625
      }
41 PointedEar 626
 
27 PointedEar 627
      $query .= " $order";
628
    }
629
 
630
    if (!is_null($limit))
631
    {
632
      $query .= " LIMIT $limit";
633
    }
41 PointedEar 634
 
76 PointedEar 635
    $stmt = ($fetchAll
636
      ? $this->prepare($query)
637
      : $this->prepare($query, array(\PDO::ATTR_CURSOR => \PDO::CURSOR_SCROLL)));
27 PointedEar 638
 
639
    $params = array();
41 PointedEar 640
 
27 PointedEar 641
    if (is_array($where) && $this->_isAssociativeArray($where))
642
    {
34 PointedEar 643
      /* FIXME: Export and reuse this */
27 PointedEar 644
      foreach ($where as $column => $condition)
645
      {
34 PointedEar 646
        /* TODO: Also handle function calls as keys */
27 PointedEar 647
        if (is_array($condition) && $this->_isAssociativeArray($condition))
648
        {
649
          reset($condition);
650
          $condition = $condition[key($condition)];
41 PointedEar 651
 
27 PointedEar 652
          if (is_array($condition))
653
          {
654
            foreach (self::_expand($condition, $column) as $param_index => $param_name)
655
            {
656
              $params[$param_name] = $condition[$param_index];
657
            }
658
          }
659
        }
660
        else
661
        {
662
          $params[":{$column}"] = $condition;
663
        }
664
      }
665
    }
666
 
667
    /* DEBUG */
668
    if (defined('DEBUG') && DEBUG > 1)
669
    {
670
      debug(array(
69 PointedEar 671
        'query'  => $query,
672
        'params' => $params
27 PointedEar 673
      ));
674
    }
41 PointedEar 675
 
27 PointedEar 676
    $success =& $this->_lastSuccess;
677
    $success =  $stmt->execute($params);
41 PointedEar 678
 
27 PointedEar 679
    $errorInfo =& $this->_lastError;
680
    $errorInfo =  $stmt->errorInfo();
41 PointedEar 681
 
27 PointedEar 682
    $result =& $this->_lastResult;
41 PointedEar 683
 
77 PointedEar 684
    $result = ($fetchAll
685
      ? $stmt->fetchAll($fetch_style)
686
      : $stmt);
76 PointedEar 687
 
27 PointedEar 688
    if (defined('DEBUG') && DEBUG > 1)
689
    {
690
      debug(array(
691
        '_lastSuccess' => $success,
692
        '_lastError'   => $errorInfo,
693
        '_lastResult'  => $result
694
      ));
695
    }
41 PointedEar 696
 
27 PointedEar 697
    return $result;
698
  }
699
 
700
  /**
701
   * Sets and returns the ID of the last inserted row, or the last value from
702
   * a sequence object, depending on the underlying driver.
703
   *
704
   * @param string $name
705
   *   Name of the sequence object from which the ID should be returned.
706
   * @return string
707
   */
708
  protected function _setLastInsertId($name = null)
709
  {
41 PointedEar 710
    return ($this->_lastInsertId = $this->connection->lastInsertId($name));
27 PointedEar 711
  }
712
 
713
  /**
714
   * Resets the the ID of the last inserted row, or the last value from
715
   * a sequence object, depending on the underlying driver.
716
   *
717
   * @return string
718
   *   The default value
719
   */
720
  protected function _resetLastInsertId()
721
  {
722
    return ($this->_lastInsertId = '');
723
  }
41 PointedEar 724
 
27 PointedEar 725
  /**
726
   * Updates one or more records
727
   *
728
   * @param string|array $tables
729
   *   Table name
48 PointedEar 730
   * @param array $updates
27 PointedEar 731
   *   Associative array of column-value pairs
732
   * @param array|string $where
733
   *   Only the records matching this condition are updated
734
   * @return bool
61 PointedEar 735
   * @see PDOStatement::execute()
27 PointedEar 736
   */
61 PointedEar 737
  public function update ($tables, array $updates, $where = null)
27 PointedEar 738
  {
739
    if (!$tables)
740
    {
741
      throw new InvalidArgumentException('No table specified');
742
    }
41 PointedEar 743
 
27 PointedEar 744
    if (is_array($tables))
745
    {
33 PointedEar 746
      $tables = implode(', ', $tables);
27 PointedEar 747
    }
41 PointedEar 748
 
27 PointedEar 749
    if (!$updates)
750
    {
751
      throw new InvalidArgumentException('No values specified');
752
    }
753
 
754
    $params = array();
41 PointedEar 755
 
27 PointedEar 756
    if ($this->_isAssociativeArray($updates))
757
    {
758
      foreach ($updates as $key => $condition)
759
      {
760
        $params[":{$key}"] = $condition;
761
      }
762
    }
41 PointedEar 763
 
33 PointedEar 764
    $updates = implode(', ', $this->_escapeValueArray($updates));
41 PointedEar 765
 
27 PointedEar 766
    /* TODO: Should escape table names with escapeName(), but what about aliases? */
767
    $query = "UPDATE {$tables} SET {$updates}" . $this->_where($where, '2');
41 PointedEar 768
 
27 PointedEar 769
    $stmt = $this->prepare($query);
41 PointedEar 770
 
27 PointedEar 771
    if (is_array($where) && $this->_isAssociativeArray($where))
772
    {
773
      foreach ($where as $column => $condition)
774
      {
775
        if (is_array($condition) && $this->_isAssociativeArray($condition))
776
        {
777
          reset($condition);
778
          $condition = $condition[key($condition)];
41 PointedEar 779
 
27 PointedEar 780
          if (is_array($condition))
781
          {
782
            foreach (self::_expand($condition, $column) as $param_index => $param_name)
783
            {
784
              $params[$param_name] = $condition[$param_index];
785
            }
786
          }
787
        }
788
        else
789
        {
790
          $params[":{$column}2"] = $condition;
791
        }
792
      }
793
    }
794
 
795
    /* DEBUG */
796
    if (defined('DEBUG') && DEBUG > 1)
797
    {
798
      debug(array(
799
        'query'  => $query,
800
        'params' => $params
801
      ));
802
    }
41 PointedEar 803
 
27 PointedEar 804
    $success =& $this->_lastSuccess;
805
    $success =  $stmt->execute($params);
41 PointedEar 806
 
27 PointedEar 807
    $errorInfo =& $this->_lastError;
808
    $errorInfo =  $stmt->errorInfo();
41 PointedEar 809
 
27 PointedEar 810
    $this->_resetLastInsertId();
41 PointedEar 811
 
27 PointedEar 812
    $result =& $this->_lastResult;
813
    $result =  $stmt->fetchAll();
41 PointedEar 814
 
27 PointedEar 815
    if (defined('DEBUG') && DEBUG > 1)
816
    {
817
      debug(array(
818
        '_lastSuccess' => $success,
819
        '_lastError'    => $errorInfo,
820
        '_lastResult'  => $result
821
      ));
822
    }
41 PointedEar 823
 
27 PointedEar 824
    return $success;
825
  }
41 PointedEar 826
 
27 PointedEar 827
  /**
828
   * Inserts a record into a table.<p>The AUTO_INCREMENT value of the inserted
829
   * row, if any (> 0), is stored in the {@link $lastInsertId} property of
830
   * the <code>Database</code> instance.</p>
831
   *
832
   * @param string $table
833
   *   Table name
834
   * @param array|string $values
835
   *   Associative array of column-value pairs, indexed array,
836
   *   or comma-separated list of values.  If <var>$values</var> is not
837
   *   an associative array, <var>$cols</var> must be passed if the
838
   *   values are not in column order (see below).
839
   * @param array|string $cols
840
   *   Indexed array, or comma-separated list of column names.
841
   *   Needs only be passed if <var>$values</var> is not an associative array
842
   *   and the values are not in column order (default: <code>null</code>);
843
   *   is ignored otherwise.  <strong>You SHOULD NOT rely on column order.</strong>
844
   * @return bool
845
   * @see PDOStatement::execute()
846
   */
61 PointedEar 847
  public function insert ($table, $values, $cols = null)
27 PointedEar 848
  {
849
    if ($cols != null)
850
    {
851
      $cols = ' ('
852
            . (is_array($cols)
33 PointedEar 853
                ? implode(', ', array_map(array($this, 'escapeName'), $cols))
27 PointedEar 854
                : $cols) . ')';
855
    }
856
    else
857
    {
858
      $cols = '';
859
    }
41 PointedEar 860
 
27 PointedEar 861
    /* DEBUG */
862
    if (defined('DEBUG') && DEBUG > 2)
863
    {
864
      debug(array('values' => $values));
865
    }
41 PointedEar 866
 
27 PointedEar 867
    $params = array();
41 PointedEar 868
 
27 PointedEar 869
    if (is_array($values))
870
    {
871
      if ($this->_isAssociativeArray($values))
872
      {
873
        foreach ($values as $key => $condition)
874
        {
875
          $params[":{$key}"] = $condition;
876
        }
41 PointedEar 877
 
27 PointedEar 878
        $values = $this->_escapeValueArray($values);
41 PointedEar 879
 
27 PointedEar 880
        $cols = '';
881
        $values = 'SET ' . implode(', ', $values);
882
      }
883
      else
884
      {
885
        foreach ($values as &$value)
886
        {
887
          if (is_string($value))
888
          {
889
            $value = "'" . $value . "'";
890
          }
891
        }
41 PointedEar 892
 
66 PointedEar 893
        $values = 'VALUES (' . implode(', ', $values) . ')';
27 PointedEar 894
      }
895
    }
41 PointedEar 896
 
27 PointedEar 897
    /* TODO: Should escape table names with escapeName(), but what about aliases? */
898
    $query = "INSERT INTO {$table} {$cols} {$values}";
41 PointedEar 899
 
27 PointedEar 900
    $stmt = $this->prepare($query);
41 PointedEar 901
 
27 PointedEar 902
      /* DEBUG */
903
    if (defined('DEBUG') && DEBUG > 1)
904
    {
905
       debug(array(
906
         'query'  => $query,
907
         'params' => $params
908
       ));
909
    }
41 PointedEar 910
 
27 PointedEar 911
    $success =& $this->_lastSuccess;
912
    $success = $stmt->execute($params);
41 PointedEar 913
 
27 PointedEar 914
    $errorInfo =& $this->_lastError;
915
    $errorInfo =  $stmt->errorInfo();
41 PointedEar 916
 
27 PointedEar 917
    $this->_setLastInsertId();
41 PointedEar 918
 
27 PointedEar 919
    $result =& $this->_lastResult;
920
    $result =  $stmt->fetchAll();
921
 
922
    if (defined('DEBUG') && DEBUG > 1)
923
    {
924
      debug(array(
925
        '_lastSuccess'  => $success,
926
        '_lastError'    => $errorInfo,
927
        '_lastInsertId' => $this->_lastInsertId,
928
        '_lastResult'   => $result
929
      ));
930
    }
41 PointedEar 931
 
27 PointedEar 932
    return $success;
933
  }
41 PointedEar 934
 
27 PointedEar 935
  /**
936
   * Retrieves all rows from a table
937
   *
938
   * @param int[optional] $fetch_style
939
   * @param int[optional] $column_index
940
   * @param array[optional] $ctor_args
941
   * @return array
942
   * @see PDOStatement::fetchAll()
943
   */
944
  public function fetchAll($table, $fetch_style = null, $column_index = null, array $ctor_args = null)
945
  {
946
    /* NOTE: Cannot use table name as statement parameter */
947
    $stmt = $this->prepare("SELECT * FROM $table");
948
    $this->_lastSuccess = $stmt->execute();
41 PointedEar 949
 
27 PointedEar 950
    $this->_lastError = $stmt->errorInfo();
41 PointedEar 951
 
27 PointedEar 952
    $result =& $this->_lastResult;
41 PointedEar 953
 
27 PointedEar 954
    if (is_null($fetch_style))
955
    {
51 PointedEar 956
      $fetch_style = \PDO::FETCH_ASSOC;
27 PointedEar 957
    }
41 PointedEar 958
 
27 PointedEar 959
    if (!is_null($ctor_args))
960
    {
961
      $result = $stmt->fetchAll($fetch_style, $column_index, $ctor_args);
962
    }
963
    else if (!is_null($column_index))
964
    {
965
      $result = $stmt->fetchAll($fetch_style, $column_index);
966
    }
967
    else if (!is_null($fetch_style))
968
    {
969
      $result = $stmt->fetchAll($fetch_style);
970
    }
971
    else
972
    {
973
      $result = $stmt->fetchAll();
974
    }
41 PointedEar 975
 
27 PointedEar 976
    return $result;
977
  }
978
 
979
  /**
980
   * Deletes one or more records
981
   *
982
   * @param string|array $tables
983
   *   Table name(s)
984
   * @param array|string $where
985
   *   Only the records matching this condition are deleted
986
   * @return bool
987
   * @see PDOStatement::execute()
988
   */
989
  public function delete($tables, $where = null)
990
  {
991
    if (!$tables)
992
    {
993
      throw new InvalidArgumentException('No table specified');
994
    }
41 PointedEar 995
 
27 PointedEar 996
    if (is_array($tables))
997
    {
33 PointedEar 998
      $tables = implode(', ', $tables);
27 PointedEar 999
    }
41 PointedEar 1000
 
27 PointedEar 1001
    $params = array();
41 PointedEar 1002
 
27 PointedEar 1003
    $query = "DELETE FROM {$tables}" . $this->_where($where);
41 PointedEar 1004
 
27 PointedEar 1005
    $stmt = $this->prepare($query);
41 PointedEar 1006
 
27 PointedEar 1007
    if ($this->_isAssociativeArray($where))
1008
    {
1009
      foreach ($where as $column => $condition)
1010
      {
1011
        if (is_array($condition) && $this->_isAssociativeArray($condition))
1012
        {
1013
          reset($condition);
1014
          $condition = $condition[key($condition)];
41 PointedEar 1015
 
27 PointedEar 1016
          if (is_array($condition))
1017
          {
1018
            foreach (self::_expand($condition, $column) as $param_index => $param_name)
1019
            {
1020
              $params[$param_name] = $condition[$param_index];
1021
            }
1022
          }
1023
        }
1024
        else
1025
        {
1026
          $params[":{$column}"] = $condition;
1027
        }
1028
      }
1029
    }
1030
 
1031
    /* DEBUG */
1032
    if (defined('DEBUG') && DEBUG > 1)
1033
    {
1034
      debug(array(
1035
        'query'  => $query,
1036
        'params' => $params
1037
      ));
1038
    }
41 PointedEar 1039
 
27 PointedEar 1040
    $success =& $this->_lastSuccess;
1041
    $success =  $stmt->execute($params);
41 PointedEar 1042
 
27 PointedEar 1043
    $result =& $this->_lastResult;
1044
    $result =  $stmt->fetchAll();
41 PointedEar 1045
 
27 PointedEar 1046
    $errorInfo =& $this->_lastError;
1047
    $errorInfo =  $stmt->errorInfo();
41 PointedEar 1048
 
27 PointedEar 1049
    if (defined('DEBUG') && DEBUG > 1)
1050
    {
1051
      debug(array(
1052
        '_lastSuccess' => $success,
1053
        '_lastError'   => $errorInfo,
1054
        '_lastResult'  => $result
1055
      ));
1056
    }
41 PointedEar 1057
 
27 PointedEar 1058
    return $success;
1059
  }
76 PointedEar 1060
}