Subversion Repositories PHPX

Rev

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