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    * @var array
  40    */
  41   private $columns;
  42
  43   /**
  44    * Create a new INSERT query.
  45    *
  46    * @param string $table
  47    *   Table-name and optional alias.
  48    * @return CRM_Utils_SQL_Insert
  49    */
  50   public static function into($table) {
  51     return new self($table);
  52   }
  53
  54   /**
  55    * Insert a record based on a DAO.
  56    *
  57    * @param \CRM_Core_DAO $dao
  58    * @return \CRM_Utils_SQL_Insert
  59    * @throws \CRM_Core_Exception
  60    */
  61   public static function dao(CRM_Core_DAO $dao) {
  62     $table = CRM_Core_DAO::getLocaleTableName($dao->getTableName());
  63     $row = [];
  64     foreach ((array) $dao as $key => $value) {
  65       if ($value === 'null') {
  66         // Blerg!!!
  67         $value = NULL;
  68       }
  69       // Skip '_foobar' and '{\u00}*_options' and 'N'.
  70       if (preg_match('/[a-zA-Z]/', $key{0}) && $key !== 'N') {
  71         $row[$key] = $value;
  72       }
  73     }
  74     return self::into($table)->row($row);
  75   }
  76
  77   /**
  78    * Create a new SELECT query.
  79    *
  80    * @param string $table
  81    *   Table-name and optional alias.
  82    */
  83   public function __construct($table) {
  84     $this->table = $table;
  85     $this->rows = [];
  86   }
  87
  88   /**
  89    * Get columns.
  90    *
  91    * @param array $columns
  92    *
  93    * @return CRM_Utils_SQL_Insert
  94    * @throws \CRM_Core_Exception
  95    */
  96   public function columns($columns) {
  97     if ($this->columns !== NULL) {
  98       throw new CRM_Core_Exception("Column order already specified.");
  99     }
 100     $this->columns = $columns;
 101     return $this;
 102   }
 103
 104   /**
 105    * Get rows.
 106    *
 107    * @param array $rows
 108    *
 109    * @return CRM_Utils_SQL_Insert
 110    */
 111   public function rows($rows) {
 112     foreach ($rows as $row) {
 113       $this->row($row);
 114     }
 115     return $this;
 116   }
 117
 118   /**
 119    * Get row.
 120    *
 121    * @param array $row
 122    *
 123    * @return CRM_Utils_SQL_Insert
 124    * @throws CRM_Core_Exception
 125    */
 126   public function row($row) {
 127     $columns = array_keys($row);
 128
 129     if ($this->columns === NULL) {
 130       sort($columns);
 131       $this->columns = $columns;
 132     }
 133     elseif (array_diff($this->columns, $columns) !== []) {
 134       throw new CRM_Core_Exception("Inconsistent column names");
 135     }
 136
 137     $escapedRow = [];
 138     foreach ($this->columns as $column) {
 139       $escapedRow[$column] = $this->escapeString($row[$column]);
 140     }
 141     $this->rows[] = $escapedRow;
 142
 143     return $this;
 144   }
 145
 146   /**
 147    * Use REPLACE INTO instead of INSERT INTO.
 148    *
 149    * @param bool $asReplace
 150    *
 151    * @return CRM_Utils_SQL_Insert
 152    */
 153   public function usingReplace($asReplace = TRUE) {
 154     $this->verb = $asReplace ? 'REPLACE INTO' : 'INSERT INTO';
 155     return $this;
 156   }
 157
 158   /**
 159    * Escape string.
 160    *
 161    * @param string|NULL $value
 162    *
 163    * @return string
 164    *   SQL expression, e.g. "it\'s great" (with-quotes) or NULL (without-quotes)
 165    */
 166   protected function escapeString($value) {
 167     return $value === NULL ? 'NULL' : '"' . CRM_Core_DAO::escapeString($value) . '"';
 168   }
 169
 170   /**
 171    * Convert to SQL.
 172    *
 173    * @return string
 174    *   SQL statement
 175    */
 176   public function toSQL() {
 177     $columns = "`" . implode('`,`', $this->columns) . "`";
 178     $sql = "{$this->verb} {$this->table} ({$columns}) VALUES";
 179
 180     $nextDelim = '';
 181     foreach ($this->rows as $row) {
 182       $sql .= "{$nextDelim}\n(" . implode(',', $row) . ")";
 183       $nextDelim = ',';
 184     }
 185     $sql .= "\n";
 186
 187     return $sql;
 188   }
 189
 190 }