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