Subversion Repositories PHPX

Rev

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