Subversion Repositories PHPX

Rev

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