Subversion Repositories PHPX

Rev

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