CRM/Utils/SQL/Insert.php

   1 <?php
   2
   3 /**
   4  * Dear God Why Do I Have To Write This (Dumb SQL Builder)
   5  *
   6  * Usage:
   7  * $insert = CRM_Utils_SQL_Insert::into('mytable')
   8  *  ->row(array('col1' => '1', 'col2' => '2' ))
   9  *  ->row(array('col1' => '2b', 'col2' => '1b'));
  10  * echo $insert->toSQL();
  11  *
  12  * Note: In MySQL, numeric values may be escaped. Except for NULL values,
  13  * it's reasonable for us to simply escape all values by default -- without
  14  * any knowledge of the underlying schema.
  15  *
  16  * Design principles:
  17  *  - Portable
  18  *    - No knowledge of the underlying SQL API (except for escaping -- CRM_Core_DAO::escapeString)
  19  *    - No knowledge of the underlying data model
  20  *    - Single file
  21  *  - SQL clauses correspond to PHP functions ($select->where("foo_id=123"))
  22  */
  23 class CRM_Utils_SQL_Insert {
  24
  25   private $verb = 'INSERT INTO';
  26
  27   /**
  28    * @var string
  29    */
  30   private $table;
  31
  32   /**
  33    * @var array
  34    */
  35   private $rows;
  36
  37   /**
  38    * array<string> list of column names
  39    */
  40   private $columns;
  41
  42   /**
  43    * Create a new INSERT query
  44    *
  45    * @param string $table table-name and optional alias
  46    * @return CRM_Utils_SQL_Insert
  47    */
  48   public static function into($table) {
  49     return new self($table);
  50   }
  51
  52   /**
  53    * Create a new SELECT query
  54    *
  55    * @param string $from table-name and optional alias
  56    */
  57   public function __construct($table) {
  58     $this->table = $table;
  59     $this->rows = array();
  60   }
  61
  62   /**
  63    * @param array $rows
  64    * @return CRM_Utils_SQL_Insert
  65    */
  66   public function rows($rows) {
  67     foreach ($rows as $row) {
  68       $this->row($row);
  69     }
  70     return $this;
  71   }
  72
  73   /**
  74    * @param array $row
  75    * @return CRM_Utils_SQL_Insert
  76    * @throws CRM_Core_Exception
  77    */
  78   public function row($row) {
  79     $columns = array_keys($row);
  80     sort($columns);
  81
  82     if ($this->columns === NULL) {
  83       $this->columns = $columns;
  84     }
  85     elseif ($this->columns != $columns) {
  86       throw new CRM_Core_Exception("Inconsistent column names");
  87     }
  88
  89     $escapedRow = array();
  90     foreach ($columns as $column) {
  91       $escapedRow[$column] = $this->escapeString($row[$column]);
  92     }
  93     $this->rows[] = $escapedRow;
  94
  95     return $this;
  96   }
  97
  98   /**
  99    * Use REPLACE INTO instead of INSERT INTO
 100    *
 101    * @param bool $asReplace
 102    * @return CRM_Utils_SQL_Insert
 103    */
 104   public function usingReplace($asReplace = TRUE) {
 105     $this->verb = $asReplace ? 'REPLACE INTO' : 'INSERT INTO';
 106     return $this;
 107   }
 108
 109   /**
 110    * @param string|NULL $value
 111    * @return string SQL expression, e.g. "it\'s great" (with-quotes) or NULL (without-quotes)
 112    */
 113   protected function escapeString($value) {
 114     return $value === NULL ? 'NULL' : '"' . CRM_Core_DAO::escapeString($value) . '"';
 115   }
 116
 117   /**
 118    * @return string SQL statement
 119    */
 120   public function toSQL() {
 121     $columns = "`" . implode('`,`', $this->columns) . "`";
 122     $sql = "{$this->verb} {$this->table} ({$columns}) VALUES";
 123
 124     $nextDelim = '';
 125     foreach ($this->rows as $row) {
 126       $sql .= "{$nextDelim}\n(" . implode(',', $row) . ")";
 127       $nextDelim = ',';
 128     }
 129     $sql .= "\n";
 130
 131     return $sql;
 132   }
 133 }