Subversion Repositories PHPX

Rev

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