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 | ||
53 | /** | |
fe482240 | 54 | * Create a new SELECT query. |
3e4ef323 | 55 | * |
59f4c9ee | 56 | * @param string $table |
77855840 | 57 | * Table-name and optional alias. |
3e4ef323 TO |
58 | */ |
59 | public function __construct($table) { | |
60 | $this->table = $table; | |
61 | $this->rows = array(); | |
62 | } | |
63 | ||
70599df6 | 64 | /** |
65 | * Get columns. | |
66 | * | |
67 | * @param array $columns | |
68 | * | |
69 | * @return $this | |
70 | * @throws \CRM_Core_Exception | |
71 | */ | |
c5458931 TO |
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 | ||
3e4ef323 | 80 | /** |
70599df6 | 81 | * Get rows. |
82 | * | |
3e4ef323 | 83 | * @param array $rows |
70599df6 | 84 | * |
3e4ef323 TO |
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 | /** | |
70599df6 | 95 | * Get row. |
96 | * | |
3e4ef323 | 97 | * @param array $row |
70599df6 | 98 | * |
3e4ef323 TO |
99 | * @return CRM_Utils_SQL_Insert |
100 | * @throws CRM_Core_Exception | |
101 | */ | |
102 | public function row($row) { | |
103 | $columns = array_keys($row); | |
3e4ef323 TO |
104 | |
105 | if ($this->columns === NULL) { | |
c5458931 | 106 | sort($columns); |
3e4ef323 TO |
107 | $this->columns = $columns; |
108 | } | |
c5458931 | 109 | elseif (array_diff($this->columns, $columns) !== array()) { |
3e4ef323 TO |
110 | throw new CRM_Core_Exception("Inconsistent column names"); |
111 | } | |
112 | ||
113 | $escapedRow = array(); | |
c5458931 | 114 | foreach ($this->columns as $column) { |
3e4ef323 TO |
115 | $escapedRow[$column] = $this->escapeString($row[$column]); |
116 | } | |
117 | $this->rows[] = $escapedRow; | |
118 | ||
119 | return $this; | |
120 | } | |
121 | ||
09095684 | 122 | /** |
fe482240 | 123 | * Use REPLACE INTO instead of INSERT INTO. |
09095684 TO |
124 | * |
125 | * @param bool $asReplace | |
70599df6 | 126 | * |
09095684 TO |
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 | ||
3e4ef323 | 134 | /** |
70599df6 | 135 | * Escape string. |
136 | * | |
3e4ef323 | 137 | * @param string|NULL $value |
70599df6 | 138 | * |
a6c01b45 CW |
139 | * @return string |
140 | * SQL expression, e.g. "it\'s great" (with-quotes) or NULL (without-quotes) | |
3e4ef323 TO |
141 | */ |
142 | protected function escapeString($value) { | |
143 | return $value === NULL ? 'NULL' : '"' . CRM_Core_DAO::escapeString($value) . '"'; | |
144 | } | |
145 | ||
146 | /** | |
70599df6 | 147 | * Convert to SQL. |
148 | * | |
a6c01b45 CW |
149 | * @return string |
150 | * SQL statement | |
3e4ef323 TO |
151 | */ |
152 | public function toSQL() { | |
153 | $columns = "`" . implode('`,`', $this->columns) . "`"; | |
09095684 | 154 | $sql = "{$this->verb} {$this->table} ({$columns}) VALUES"; |
3e4ef323 TO |
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 | } | |
96025800 | 165 | |
3e4ef323 | 166 | } |