Subversion Repositories PHPX

Rev

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