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