Commit | Line | Data |
---|---|---|
3e4ef323 TO |
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 | ||
09095684 TO |
25 | private $verb = 'INSERT INTO'; |
26 | ||
3e4ef323 TO |
27 | /** |
28 | * @var string | |
29 | */ | |
30 | private $table; | |
31 | ||
32 | /** | |
33 | * @var array | |
34 | */ | |
35 | private $rows; | |
36 | ||
37 | /** | |
100fef9d | 38 | * Array<string> list of column names |
6714d8d2 | 39 | * @var array |
3e4ef323 TO |
40 | */ |
41 | private $columns; | |
42 | ||
43 | /** | |
fe482240 | 44 | * Create a new INSERT query. |
3e4ef323 | 45 | * |
77855840 TO |
46 | * @param string $table |
47 | * Table-name and optional alias. | |
3e4ef323 TO |
48 | * @return CRM_Utils_SQL_Insert |
49 | */ | |
50 | public static function into($table) { | |
51 | return new self($table); | |
52 | } | |
53 | ||
867a532b TO |
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()); | |
be2fb01f | 63 | $row = []; |
867a532b TO |
64 | foreach ((array) $dao as $key => $value) { |
65 | if ($value === 'null') { | |
6714d8d2 SL |
66 | // Blerg!!! |
67 | $value = NULL; | |
867a532b TO |
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 | ||
3e4ef323 | 77 | /** |
fe482240 | 78 | * Create a new SELECT query. |
3e4ef323 | 79 | * |
59f4c9ee | 80 | * @param string $table |
77855840 | 81 | * Table-name and optional alias. |
3e4ef323 TO |
82 | */ |
83 | public function __construct($table) { | |
84 | $this->table = $table; | |
be2fb01f | 85 | $this->rows = []; |
3e4ef323 TO |
86 | } |
87 | ||
70599df6 | 88 | /** |
89 | * Get columns. | |
90 | * | |
91 | * @param array $columns | |
92 | * | |
14069c56 | 93 | * @return CRM_Utils_SQL_Insert |
70599df6 | 94 | * @throws \CRM_Core_Exception |
95 | */ | |
c5458931 TO |
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 | ||
3e4ef323 | 104 | /** |
70599df6 | 105 | * Get rows. |
106 | * | |
3e4ef323 | 107 | * @param array $rows |
70599df6 | 108 | * |
3e4ef323 TO |
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 | /** | |
70599df6 | 119 | * Get row. |
120 | * | |
3e4ef323 | 121 | * @param array $row |
70599df6 | 122 | * |
3e4ef323 TO |
123 | * @return CRM_Utils_SQL_Insert |
124 | * @throws CRM_Core_Exception | |
125 | */ | |
126 | public function row($row) { | |
127 | $columns = array_keys($row); | |
3e4ef323 TO |
128 | |
129 | if ($this->columns === NULL) { | |
c5458931 | 130 | sort($columns); |
3e4ef323 TO |
131 | $this->columns = $columns; |
132 | } | |
be2fb01f | 133 | elseif (array_diff($this->columns, $columns) !== []) { |
3e4ef323 TO |
134 | throw new CRM_Core_Exception("Inconsistent column names"); |
135 | } | |
136 | ||
be2fb01f | 137 | $escapedRow = []; |
c5458931 | 138 | foreach ($this->columns as $column) { |
3e4ef323 TO |
139 | $escapedRow[$column] = $this->escapeString($row[$column]); |
140 | } | |
141 | $this->rows[] = $escapedRow; | |
142 | ||
143 | return $this; | |
144 | } | |
145 | ||
09095684 | 146 | /** |
fe482240 | 147 | * Use REPLACE INTO instead of INSERT INTO. |
09095684 TO |
148 | * |
149 | * @param bool $asReplace | |
70599df6 | 150 | * |
09095684 TO |
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 | ||
3e4ef323 | 158 | /** |
70599df6 | 159 | * Escape string. |
160 | * | |
3e4ef323 | 161 | * @param string|NULL $value |
70599df6 | 162 | * |
a6c01b45 CW |
163 | * @return string |
164 | * SQL expression, e.g. "it\'s great" (with-quotes) or NULL (without-quotes) | |
3e4ef323 TO |
165 | */ |
166 | protected function escapeString($value) { | |
167 | return $value === NULL ? 'NULL' : '"' . CRM_Core_DAO::escapeString($value) . '"'; | |
168 | } | |
169 | ||
170 | /** | |
70599df6 | 171 | * Convert to SQL. |
172 | * | |
a6c01b45 CW |
173 | * @return string |
174 | * SQL statement | |
3e4ef323 TO |
175 | */ |
176 | public function toSQL() { | |
177 | $columns = "`" . implode('`,`', $this->columns) . "`"; | |
09095684 | 178 | $sql = "{$this->verb} {$this->table} ({$columns}) VALUES"; |
3e4ef323 TO |
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 | } | |
96025800 | 189 | |
3e4ef323 | 190 | } |