CRM-17060 fix for post code groups with IN not working
[civicrm-core.git] / 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 * @param array $rows
66 * @return CRM_Utils_SQL_Insert
67 */
68 public function rows($rows) {
69 foreach ($rows as $row) {
70 $this->row($row);
71 }
72 return $this;
73 }
74
75 /**
76 * @param array $row
77 * @return CRM_Utils_SQL_Insert
78 * @throws CRM_Core_Exception
79 */
80 public function row($row) {
81 $columns = array_keys($row);
82 sort($columns);
83
84 if ($this->columns === NULL) {
85 $this->columns = $columns;
86 }
87 elseif ($this->columns != $columns) {
88 throw new CRM_Core_Exception("Inconsistent column names");
89 }
90
91 $escapedRow = array();
92 foreach ($columns as $column) {
93 $escapedRow[$column] = $this->escapeString($row[$column]);
94 }
95 $this->rows[] = $escapedRow;
96
97 return $this;
98 }
99
100 /**
101 * Use REPLACE INTO instead of INSERT INTO.
102 *
103 * @param bool $asReplace
104 * @return CRM_Utils_SQL_Insert
105 */
106 public function usingReplace($asReplace = TRUE) {
107 $this->verb = $asReplace ? 'REPLACE INTO' : 'INSERT INTO';
108 return $this;
109 }
110
111 /**
112 * @param string|NULL $value
113 * @return string
114 * SQL expression, e.g. "it\'s great" (with-quotes) or NULL (without-quotes)
115 */
116 protected function escapeString($value) {
117 return $value === NULL ? 'NULL' : '"' . CRM_Core_DAO::escapeString($value) . '"';
118 }
119
120 /**
121 * @return string
122 * SQL statement
123 */
124 public function toSQL() {
125 $columns = "`" . implode('`,`', $this->columns) . "`";
126 $sql = "{$this->verb} {$this->table} ({$columns}) VALUES";
127
128 $nextDelim = '';
129 foreach ($this->rows as $row) {
130 $sql .= "{$nextDelim}\n(" . implode(',', $row) . ")";
131 $nextDelim = ',';
132 }
133 $sql .= "\n";
134
135 return $sql;
136 }
137
138 }