Subversion Repositories PHPX

Rev

Rev 68 | Rev 74 | 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
  {
69 PointedEar 161
    /* FIXME: Configuration file path should not be hardcoded */
162
    $config = parse_ini_file('application/.config', true);
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
  /**
382
   * Escapes a database name so that it can be used in a query.
383
   *
384
   * @param string $name
385
   *   The name to be escaped
386
   * @return string
387
   *   The escaped name
388
   */
389
  public function escapeName($name)
390
  {
32 PointedEar 391
    return $this->_leftQuote . $name . $this->_rightQuote;
27 PointedEar 392
  }
41 PointedEar 393
 
27 PointedEar 394
  /**
395
   * Determines if an array is associative (has not all integer keys).
396
   *
397
   * @author
398
   *   Algorithm courtesy of squirrel, <http://stackoverflow.com/a/5969617/855543>.
399
   * @param array $a
400
   * @return boolean
401
   *   <code>true</code> if <var>$a</var> is associative,
402
   *   <code>false</code> otherwise
403
   */
404
  protected function _isAssociativeArray(array $a)
405
  {
406
    for (reset($a); is_int(key($a)); next($a));
407
    return !is_null(key($a));
408
  }
41 PointedEar 409
 
27 PointedEar 410
  /**
411
   * Escapes an associative array so that its string representation can be used
412
   * as list with table or column aliases in a query.
413
   *
414
   * This method does not actually escape anything; it only inserts the
415
   * 'AS' keyword.  It should be overridden by inheriting methods.
416
   *
417
   * NOTE: This method intentionally does not check whether the array actually
418
   * is associative.
419
   *
420
   * @param array &$array
421
   *   The array to be escaped
422
   * @return array
423
   *   The escaped array
424
   */
425
  protected function _escapeAliasArray(array &$array)
426
  {
427
    foreach ($array as $column => &$value)
428
    {
32 PointedEar 429
      $quotedColumn = $column;
430
      if (strpos($column, $this->_leftQuote) === false
431
         && strpos($column, $this->_rightQuote) === false)
432
      {
433
        $quotedColumn = $this->_leftQuote . $column . $this->_rightQuote;
434
      }
41 PointedEar 435
 
32 PointedEar 436
      $value = $value . ' AS ' . $quotedColumn;
27 PointedEar 437
    }
41 PointedEar 438
 
27 PointedEar 439
    return $array;
440
  }
441
 
442
  /**
443
   * @param array $a
444
   * @param string $prefix
445
   */
446
  private static function _expand(array $a, $prefix)
447
  {
448
    $a2 = array();
41 PointedEar 449
 
27 PointedEar 450
    foreach ($a as $key => $value)
451
    {
452
      $a2[] = ':' . $prefix . ($key + 1);
453
    }
41 PointedEar 454
 
27 PointedEar 455
    return $a2;
456
  }
41 PointedEar 457
 
27 PointedEar 458
  /**
459
   * Escapes an associative array so that its string representation can be used
460
   * as value list in a query.
461
   *
462
   * This method should be overridden by inheriting classes to escape
463
   * column names as fitting for the database schema they support.  It is
464
   * strongly recommended that the overriding methods call this method with
465
   * an appropriate <var>$escape</var> parameter, pass all other parameters
466
   * on unchanged, and return its return value.
467
   *
468
   * NOTE: Intentionally does not check whether the array actually is associative!
469
   *
470
   * @param array &$array
471
   *   The array to be escaped
472
   * @param string $suffix
473
   *   The string to be appended to the column name for the value placeholder.
474
   *   The default is the empty string.
475
   * @param array $escape
476
   *   The strings to use left-hand side (index 0) and right-hand side (index 1)
477
   *   of the column name.  The default is the empty string, respectively.
478
   * @return array
479
   *   The escaped array
480
   */
32 PointedEar 481
  protected function _escapeValueArray(array &$array, $suffix = '')
27 PointedEar 482
  {
483
    $result = array();
41 PointedEar 484
 
27 PointedEar 485
    foreach ($array as $column => $value)
486
    {
487
      $op = '=';
488
      $placeholder = ":{$column}";
41 PointedEar 489
 
27 PointedEar 490
      if (is_array($value) && $this->_isAssociativeArray($value))
491
      {
492
        reset($value);
493
        $op = ' ' . key($value) . ' ';
41 PointedEar 494
 
27 PointedEar 495
        $value = $value[key($value)];
496
      }
41 PointedEar 497
 
27 PointedEar 498
      if (is_array($value))
499
      {
33 PointedEar 500
        $placeholder = '(' . implode(', ', self::_expand($value, $column)) . ')';
27 PointedEar 501
      }
41 PointedEar 502
 
32 PointedEar 503
      $result[] = $this->_leftQuote . $column . $this->_rightQuote . "{$op}{$placeholder}{$suffix}";
27 PointedEar 504
    }
41 PointedEar 505
 
27 PointedEar 506
    return $result;
507
  }
41 PointedEar 508
 
27 PointedEar 509
  /**
510
   * Constructs the WHERE part of a query
511
   *
512
   * @param string|array $where
513
   *   Condition
514
   * @param string $suffix
515
   *   The string to be appended to the column name for the value placeholder,
516
   *   passed on to {@link Database::_escapeValueArray()}.  The default is
517
   *   the empty string.
518
   * @return string
519
   * @see Database::_escapeValueArray()
520
   */
521
  protected function _where($where, $suffix = '')
522
  {
523
    if (!is_null($where))
524
    {
525
      if (is_array($where))
526
      {
527
        if (count($where) < 1)
528
        {
529
          return '';
530
        }
41 PointedEar 531
 
27 PointedEar 532
        if ($this->_isAssociativeArray($where))
533
        {
534
          $where = $this->_escapeValueArray($where, $suffix);
535
        }
41 PointedEar 536
 
27 PointedEar 537
        $where = '(' . implode(') AND (', $where) . ')';
538
      }
41 PointedEar 539
 
27 PointedEar 540
      return ' WHERE ' . $where;
541
    }
41 PointedEar 542
 
27 PointedEar 543
    return '';
544
  }
545
 
546
  /**
547
   * Selects data from one or more tables; the resulting records are stored
548
   * in the <code>result</code> property and returned as an associative array,
549
   * where the keys are the column (alias) names.
550
   *
551
   * @param string|array[string] $tables Table(s) to select from
552
   * @param string|array[string] $columns Column(s) to select from (optional)
553
   * @param string|array $where Condition (optional)
554
   * @param string $order Sort order (optional)
555
   *   If provided, MUST start with ORDER BY or GROUP BY
556
   * @param string $limit Limit (optional)
557
   * @param int $fetch_style
558
   *   The mode that should be used for {@link PDOStatement::fetchAll()}.
559
   *   The default is {@link PDO::FETCH_ASSOC}.
560
   * @return array
561
   * @see Database::prepare()
562
   * @see PDOStatement::fetchAll()
563
   */
564
  public function select($tables, $columns = null, $where = null,
51 PointedEar 565
    $order = null, $limit = null, $fetch_style = \PDO::FETCH_ASSOC)
27 PointedEar 566
  {
567
    if (is_null($columns))
568
    {
569
      $columns = array('*');
570
    }
41 PointedEar 571
 
27 PointedEar 572
    if (is_array($columns))
573
    {
574
      if ($this->_isAssociativeArray($columns))
575
      {
576
        $columns = $this->_escapeAliasArray($columns);
577
      }
578
 
33 PointedEar 579
      $columns = implode(', ', $columns);
27 PointedEar 580
    }
581
 
582
    if (is_array($tables))
583
    {
584
      if ($this->_isAssociativeArray($columns))
585
      {
586
        $columns = $this->_escapeAliasArray($columns);
587
      }
588
 
33 PointedEar 589
      $tables = implode(', ', $tables);
27 PointedEar 590
    }
591
 
592
    $query = "SELECT {$columns} FROM {$tables}" . $this->_where($where);
593
 
594
    if (!is_null($order))
595
    {
596
      if (is_array($order))
597
      {
33 PointedEar 598
        $order = 'ORDER BY ' . implode(', ', $order);
27 PointedEar 599
      }
41 PointedEar 600
 
27 PointedEar 601
      $query .= " $order";
602
    }
603
 
604
    if (!is_null($limit))
605
    {
606
      $query .= " LIMIT $limit";
607
    }
41 PointedEar 608
 
27 PointedEar 609
    $stmt = $this->prepare($query);
610
 
611
    $params = array();
41 PointedEar 612
 
27 PointedEar 613
    if (is_array($where) && $this->_isAssociativeArray($where))
614
    {
34 PointedEar 615
      /* FIXME: Export and reuse this */
27 PointedEar 616
      foreach ($where as $column => $condition)
617
      {
34 PointedEar 618
        /* TODO: Also handle function calls as keys */
27 PointedEar 619
        if (is_array($condition) && $this->_isAssociativeArray($condition))
620
        {
621
          reset($condition);
622
          $condition = $condition[key($condition)];
41 PointedEar 623
 
27 PointedEar 624
          if (is_array($condition))
625
          {
626
            foreach (self::_expand($condition, $column) as $param_index => $param_name)
627
            {
628
              $params[$param_name] = $condition[$param_index];
629
            }
630
          }
631
        }
632
        else
633
        {
634
          $params[":{$column}"] = $condition;
635
        }
636
      }
637
    }
638
 
639
    /* DEBUG */
640
    if (defined('DEBUG') && DEBUG > 1)
641
    {
642
      debug(array(
69 PointedEar 643
        'query'  => $query,
644
        'params' => $params
27 PointedEar 645
      ));
646
    }
41 PointedEar 647
 
27 PointedEar 648
    $success =& $this->_lastSuccess;
649
    $success =  $stmt->execute($params);
41 PointedEar 650
 
27 PointedEar 651
    $errorInfo =& $this->_lastError;
652
    $errorInfo =  $stmt->errorInfo();
41 PointedEar 653
 
27 PointedEar 654
    $result =& $this->_lastResult;
655
    $result =  $stmt->fetchAll($fetch_style);
41 PointedEar 656
 
27 PointedEar 657
    if (defined('DEBUG') && DEBUG > 1)
658
    {
659
      debug(array(
660
        '_lastSuccess' => $success,
661
        '_lastError'   => $errorInfo,
662
        '_lastResult'  => $result
663
      ));
664
    }
41 PointedEar 665
 
27 PointedEar 666
    return $result;
667
  }
668
 
669
  /**
670
   * Sets and returns the ID of the last inserted row, or the last value from
671
   * a sequence object, depending on the underlying driver.
672
   *
673
   * @param string $name
674
   *   Name of the sequence object from which the ID should be returned.
675
   * @return string
676
   */
677
  protected function _setLastInsertId($name = null)
678
  {
41 PointedEar 679
    return ($this->_lastInsertId = $this->connection->lastInsertId($name));
27 PointedEar 680
  }
681
 
682
  /**
683
   * Resets the the ID of the last inserted row, or the last value from
684
   * a sequence object, depending on the underlying driver.
685
   *
686
   * @return string
687
   *   The default value
688
   */
689
  protected function _resetLastInsertId()
690
  {
691
    return ($this->_lastInsertId = '');
692
  }
41 PointedEar 693
 
27 PointedEar 694
  /**
695
   * Updates one or more records
696
   *
697
   * @param string|array $tables
698
   *   Table name
48 PointedEar 699
   * @param array $updates
27 PointedEar 700
   *   Associative array of column-value pairs
701
   * @param array|string $where
702
   *   Only the records matching this condition are updated
703
   * @return bool
61 PointedEar 704
   * @see PDOStatement::execute()
27 PointedEar 705
   */
61 PointedEar 706
  public function update ($tables, array $updates, $where = null)
27 PointedEar 707
  {
708
    if (!$tables)
709
    {
710
      throw new InvalidArgumentException('No table specified');
711
    }
41 PointedEar 712
 
27 PointedEar 713
    if (is_array($tables))
714
    {
33 PointedEar 715
      $tables = implode(', ', $tables);
27 PointedEar 716
    }
41 PointedEar 717
 
27 PointedEar 718
    if (!$updates)
719
    {
720
      throw new InvalidArgumentException('No values specified');
721
    }
722
 
723
    $params = array();
41 PointedEar 724
 
27 PointedEar 725
    if ($this->_isAssociativeArray($updates))
726
    {
727
      foreach ($updates as $key => $condition)
728
      {
729
        $params[":{$key}"] = $condition;
730
      }
731
    }
41 PointedEar 732
 
33 PointedEar 733
    $updates = implode(', ', $this->_escapeValueArray($updates));
41 PointedEar 734
 
27 PointedEar 735
    /* TODO: Should escape table names with escapeName(), but what about aliases? */
736
    $query = "UPDATE {$tables} SET {$updates}" . $this->_where($where, '2');
41 PointedEar 737
 
27 PointedEar 738
    $stmt = $this->prepare($query);
41 PointedEar 739
 
27 PointedEar 740
    if (is_array($where) && $this->_isAssociativeArray($where))
741
    {
742
      foreach ($where as $column => $condition)
743
      {
744
        if (is_array($condition) && $this->_isAssociativeArray($condition))
745
        {
746
          reset($condition);
747
          $condition = $condition[key($condition)];
41 PointedEar 748
 
27 PointedEar 749
          if (is_array($condition))
750
          {
751
            foreach (self::_expand($condition, $column) as $param_index => $param_name)
752
            {
753
              $params[$param_name] = $condition[$param_index];
754
            }
755
          }
756
        }
757
        else
758
        {
759
          $params[":{$column}2"] = $condition;
760
        }
761
      }
762
    }
763
 
764
    /* DEBUG */
765
    if (defined('DEBUG') && DEBUG > 1)
766
    {
767
      debug(array(
768
        'query'  => $query,
769
        'params' => $params
770
      ));
771
    }
41 PointedEar 772
 
27 PointedEar 773
    $success =& $this->_lastSuccess;
774
    $success =  $stmt->execute($params);
41 PointedEar 775
 
27 PointedEar 776
    $errorInfo =& $this->_lastError;
777
    $errorInfo =  $stmt->errorInfo();
41 PointedEar 778
 
27 PointedEar 779
    $this->_resetLastInsertId();
41 PointedEar 780
 
27 PointedEar 781
    $result =& $this->_lastResult;
782
    $result =  $stmt->fetchAll();
41 PointedEar 783
 
27 PointedEar 784
    if (defined('DEBUG') && DEBUG > 1)
785
    {
786
      debug(array(
787
        '_lastSuccess' => $success,
788
        '_lastError'    => $errorInfo,
789
        '_lastResult'  => $result
790
      ));
791
    }
41 PointedEar 792
 
27 PointedEar 793
    return $success;
794
  }
41 PointedEar 795
 
27 PointedEar 796
  /**
797
   * Inserts a record into a table.<p>The AUTO_INCREMENT value of the inserted
798
   * row, if any (> 0), is stored in the {@link $lastInsertId} property of
799
   * the <code>Database</code> instance.</p>
800
   *
801
   * @param string $table
802
   *   Table name
803
   * @param array|string $values
804
   *   Associative array of column-value pairs, indexed array,
805
   *   or comma-separated list of values.  If <var>$values</var> is not
806
   *   an associative array, <var>$cols</var> must be passed if the
807
   *   values are not in column order (see below).
808
   * @param array|string $cols
809
   *   Indexed array, or comma-separated list of column names.
810
   *   Needs only be passed if <var>$values</var> is not an associative array
811
   *   and the values are not in column order (default: <code>null</code>);
812
   *   is ignored otherwise.  <strong>You SHOULD NOT rely on column order.</strong>
813
   * @return bool
814
   * @see PDOStatement::execute()
815
   */
61 PointedEar 816
  public function insert ($table, $values, $cols = null)
27 PointedEar 817
  {
818
    if ($cols != null)
819
    {
820
      $cols = ' ('
821
            . (is_array($cols)
33 PointedEar 822
                ? implode(', ', array_map(array($this, 'escapeName'), $cols))
27 PointedEar 823
                : $cols) . ')';
824
    }
825
    else
826
    {
827
      $cols = '';
828
    }
41 PointedEar 829
 
27 PointedEar 830
    /* DEBUG */
831
    if (defined('DEBUG') && DEBUG > 2)
832
    {
833
      debug(array('values' => $values));
834
    }
41 PointedEar 835
 
27 PointedEar 836
    $params = array();
41 PointedEar 837
 
27 PointedEar 838
    if (is_array($values))
839
    {
840
      if ($this->_isAssociativeArray($values))
841
      {
842
        foreach ($values as $key => $condition)
843
        {
844
          $params[":{$key}"] = $condition;
845
        }
41 PointedEar 846
 
27 PointedEar 847
        $values = $this->_escapeValueArray($values);
41 PointedEar 848
 
27 PointedEar 849
        $cols = '';
850
        $values = 'SET ' . implode(', ', $values);
851
      }
852
      else
853
      {
854
        foreach ($values as &$value)
855
        {
856
          if (is_string($value))
857
          {
858
            $value = "'" . $value . "'";
859
          }
860
        }
41 PointedEar 861
 
66 PointedEar 862
        $values = 'VALUES (' . implode(', ', $values) . ')';
27 PointedEar 863
      }
864
    }
41 PointedEar 865
 
27 PointedEar 866
    /* TODO: Should escape table names with escapeName(), but what about aliases? */
867
    $query = "INSERT INTO {$table} {$cols} {$values}";
41 PointedEar 868
 
27 PointedEar 869
    $stmt = $this->prepare($query);
41 PointedEar 870
 
27 PointedEar 871
      /* DEBUG */
872
    if (defined('DEBUG') && DEBUG > 1)
873
    {
874
       debug(array(
875
         'query'  => $query,
876
         'params' => $params
877
       ));
878
    }
41 PointedEar 879
 
27 PointedEar 880
    $success =& $this->_lastSuccess;
881
    $success = $stmt->execute($params);
41 PointedEar 882
 
27 PointedEar 883
    $errorInfo =& $this->_lastError;
884
    $errorInfo =  $stmt->errorInfo();
41 PointedEar 885
 
27 PointedEar 886
    $this->_setLastInsertId();
41 PointedEar 887
 
27 PointedEar 888
    $result =& $this->_lastResult;
889
    $result =  $stmt->fetchAll();
890
 
891
    if (defined('DEBUG') && DEBUG > 1)
892
    {
893
      debug(array(
894
        '_lastSuccess'  => $success,
895
        '_lastError'    => $errorInfo,
896
        '_lastInsertId' => $this->_lastInsertId,
897
        '_lastResult'   => $result
898
      ));
899
    }
41 PointedEar 900
 
27 PointedEar 901
    return $success;
902
  }
41 PointedEar 903
 
27 PointedEar 904
  /**
905
   * Retrieves all rows from a table
906
   *
907
   * @param int[optional] $fetch_style
908
   * @param int[optional] $column_index
909
   * @param array[optional] $ctor_args
910
   * @return array
911
   * @see PDOStatement::fetchAll()
912
   */
913
  public function fetchAll($table, $fetch_style = null, $column_index = null, array $ctor_args = null)
914
  {
915
    /* NOTE: Cannot use table name as statement parameter */
916
    $stmt = $this->prepare("SELECT * FROM $table");
917
    $this->_lastSuccess = $stmt->execute();
41 PointedEar 918
 
27 PointedEar 919
    $this->_lastError = $stmt->errorInfo();
41 PointedEar 920
 
27 PointedEar 921
    $result =& $this->_lastResult;
41 PointedEar 922
 
27 PointedEar 923
    if (is_null($fetch_style))
924
    {
51 PointedEar 925
      $fetch_style = \PDO::FETCH_ASSOC;
27 PointedEar 926
    }
41 PointedEar 927
 
27 PointedEar 928
    if (!is_null($ctor_args))
929
    {
930
      $result = $stmt->fetchAll($fetch_style, $column_index, $ctor_args);
931
    }
932
    else if (!is_null($column_index))
933
    {
934
      $result = $stmt->fetchAll($fetch_style, $column_index);
935
    }
936
    else if (!is_null($fetch_style))
937
    {
938
      $result = $stmt->fetchAll($fetch_style);
939
    }
940
    else
941
    {
942
      $result = $stmt->fetchAll();
943
    }
41 PointedEar 944
 
27 PointedEar 945
    return $result;
946
  }
947
 
948
  /**
949
   * Deletes one or more records
950
   *
951
   * @param string|array $tables
952
   *   Table name(s)
953
   * @param array|string $where
954
   *   Only the records matching this condition are deleted
955
   * @return bool
956
   * @see PDOStatement::execute()
957
   */
958
  public function delete($tables, $where = null)
959
  {
960
    if (!$tables)
961
    {
962
      throw new InvalidArgumentException('No table specified');
963
    }
41 PointedEar 964
 
27 PointedEar 965
    if (is_array($tables))
966
    {
33 PointedEar 967
      $tables = implode(', ', $tables);
27 PointedEar 968
    }
41 PointedEar 969
 
27 PointedEar 970
    $params = array();
41 PointedEar 971
 
27 PointedEar 972
    $query = "DELETE FROM {$tables}" . $this->_where($where);
41 PointedEar 973
 
27 PointedEar 974
    $stmt = $this->prepare($query);
41 PointedEar 975
 
27 PointedEar 976
    if ($this->_isAssociativeArray($where))
977
    {
978
      foreach ($where as $column => $condition)
979
      {
980
        if (is_array($condition) && $this->_isAssociativeArray($condition))
981
        {
982
          reset($condition);
983
          $condition = $condition[key($condition)];
41 PointedEar 984
 
27 PointedEar 985
          if (is_array($condition))
986
          {
987
            foreach (self::_expand($condition, $column) as $param_index => $param_name)
988
            {
989
              $params[$param_name] = $condition[$param_index];
990
            }
991
          }
992
        }
993
        else
994
        {
995
          $params[":{$column}"] = $condition;
996
        }
997
      }
998
    }
999
 
1000
    /* DEBUG */
1001
    if (defined('DEBUG') && DEBUG > 1)
1002
    {
1003
      debug(array(
1004
        'query'  => $query,
1005
        'params' => $params
1006
      ));
1007
    }
41 PointedEar 1008
 
27 PointedEar 1009
    $success =& $this->_lastSuccess;
1010
    $success =  $stmt->execute($params);
41 PointedEar 1011
 
27 PointedEar 1012
    $result =& $this->_lastResult;
1013
    $result =  $stmt->fetchAll();
41 PointedEar 1014
 
27 PointedEar 1015
    $errorInfo =& $this->_lastError;
1016
    $errorInfo =  $stmt->errorInfo();
41 PointedEar 1017
 
27 PointedEar 1018
    if (defined('DEBUG') && DEBUG > 1)
1019
    {
1020
      debug(array(
1021
        '_lastSuccess' => $success,
1022
        '_lastError'   => $errorInfo,
1023
        '_lastResult'  => $result
1024
      ));
1025
    }
41 PointedEar 1026
 
27 PointedEar 1027
    return $success;
1028
  }
1029
}