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   /**
  26    * @var string
  27    */
  28   private $table;
  29
  30   /**
  31    * @var array
  32    */
  33   private $rows;
  34
  35   /**
  36    * array<string> list of column names
  37    */
  38   private $columns;
  39
  40   /**
  41    * Create a new INSERT query
  42    *
  43    * @param string $table table-name and optional alias
  44    * @return CRM_Utils_SQL_Insert
  45    */
  46   public static function into($table) {
  47     return new self($table);
  48   }
  49
  50   /**
  51    * Create a new SELECT query
  52    *
  53    * @param string $from table-name and optional alias
  54    */
  55   public function __construct($table) {
  56     $this->table = $table;
  57     $this->rows = array();
  58   }
  59
  60   /**
  61    * @param array $rows
  62    * @return CRM_Utils_SQL_Insert
  63    */
  64   public function rows($rows) {
  65     foreach ($rows as $row) {
  66       $this->row($row);
  67     }
  68     return $this;
  69   }
  70
  71   /**
  72    * @param array $row
  73    * @return CRM_Utils_SQL_Insert
  74    * @throws CRM_Core_Exception
  75    */
  76   public function row($row) {
  77     $columns = array_keys($row);
  78     sort($columns);
  79
  80     if ($this->columns === NULL) {
  81       $this->columns = $columns;
  82     }
  83     elseif ($this->columns != $columns) {
  84       throw new CRM_Core_Exception("Inconsistent column names");
  85     }
  86
  87     $escapedRow = array();
  88     foreach ($columns as $column) {
  89       $escapedRow[$column] = $this->escapeString($row[$column]);
  90     }
  91     $this->rows[] = $escapedRow;
  92
  93     return $this;
  94   }
  95
  96   /**
  97    * @param string|NULL $value
  98    * @return string SQL expression, e.g. "it\'s great" (with-quotes) or NULL (without-quotes)
  99    */
 100   protected function escapeString($value) {
 101     return $value === NULL ? 'NULL' : '"' . CRM_Core_DAO::escapeString($value) . '"';
 102   }
 103
 104   /**
 105    * @return string SQL statement
 106    */
 107   public function toSQL() {
 108     $columns = "`" . implode('`,`', $this->columns) . "`";
 109     $sql = "INSERT INTO {$this->table} ({$columns}) VALUES";
 110
 111     $nextDelim = '';
 112     foreach ($this->rows as $row) {
 113       $sql .= "{$nextDelim}\n(" . implode(',', $row) . ")";
 114       $nextDelim = ',';
 115     }
 116     $sql .= "\n";
 117
 118     return $sql;
 119   }
 120 }