-
-
Notifications
You must be signed in to change notification settings - Fork 812
/
Insert.php
209 lines (188 loc) · 4.61 KB
/
Insert.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
<?php
/**
* Object-oriented SQL builder for INSERT queries.
*
* Usage:
* $insert = CRM_Utils_SQL_Insert::into('mytable')
* ->row(array('col1' => '1', 'col2' => '2' ))
* ->row(array('col1' => '2b', 'col2' => '1b'));
* echo $insert->toSQL();
*
* Note: In MySQL, numeric values may be escaped. Except for NULL values,
* it's reasonable for us to simply escape all values by default -- without
* any knowledge of the underlying schema.
*
* Design principles:
* - Portable
* - No knowledge of the underlying SQL API (except for escaping -- CRM_Core_DAO::escapeString)
* - No knowledge of the underlying data model
* - Single file
* - SQL clauses correspond to PHP functions ($select->where("foo_id=123"))
*/
class CRM_Utils_SQL_Insert {
/**
* @var string
* Ex: 'INSERT INTO', 'REPLACE INTO'
*/
private $verb;
/**
* @var string
*/
private $table;
/**
* @var array
*/
private $rows;
/**
* Array<string> list of column names
* @var array
*/
private $columns;
/**
* Create a new INSERT query.
*
* @param string $table
* Table-name and optional alias.
* @param string $verb
* Ex: 'INSERT INTO', 'REPLACE INTO'
* @return CRM_Utils_SQL_Insert
*/
public static function into($table, string $verb = 'INSERT INTO') {
return new self($table, $verb);
}
/**
* Insert a record based on a DAO.
*
* @param \CRM_Core_DAO $dao
* @return \CRM_Utils_SQL_Insert
* @throws \CRM_Core_Exception
*/
public static function dao(CRM_Core_DAO $dao) {
$table = $dao::getLocaleTableName();
$row = [];
foreach ((array) $dao as $key => $value) {
if ($value === 'null') {
// Blerg!!!
$value = NULL;
}
// Skip '_foobar' and '{\u00}*_options' and 'N'.
if (preg_match('/[a-zA-Z]/', $key[0]) && $key !== 'N') {
$row[$key] = $value;
}
}
return self::into($table)->row($row);
}
/**
* Create a new SELECT query.
*
* @param string $table
* Table-name and optional alias.
* @param string $verb
* Ex: 'INSERT INTO', 'REPLACE INTO'
*/
public function __construct($table, string $verb = 'INSERT INTO') {
$this->table = $table;
$this->verb = $verb;
$this->rows = [];
}
/**
* Get columns.
*
* @param array $columns
*
* @return CRM_Utils_SQL_Insert
* @throws \CRM_Core_Exception
*/
public function columns($columns) {
if ($this->columns !== NULL) {
throw new CRM_Core_Exception("Column order already specified.");
}
$this->columns = $columns;
return $this;
}
/**
* Get rows.
*
* @param array $rows
*
* @return CRM_Utils_SQL_Insert
*/
public function rows($rows) {
foreach ($rows as $row) {
$this->row($row);
}
return $this;
}
/**
* Get row.
*
* @param array $row
*
* @return CRM_Utils_SQL_Insert
* @throws CRM_Core_Exception
*/
public function row($row) {
$columns = array_keys($row);
if ($this->columns === NULL) {
sort($columns);
$this->columns = $columns;
}
elseif (array_diff($this->columns, $columns) !== []) {
throw new CRM_Core_Exception("Inconsistent column names");
}
$escapedRow = [];
foreach ($this->columns as $column) {
if (is_bool($row[$column])) {
$escapedRow[$column] = (int) $row[$column];
}
else {
$escapedRow[$column] = $this->escapeString($row[$column]);
}
}
$this->rows[] = $escapedRow;
return $this;
}
/**
* Use REPLACE INTO instead of INSERT INTO.
*
* @param bool $asReplace
*
* @return CRM_Utils_SQL_Insert
*/
public function usingReplace($asReplace = TRUE) {
$this->verb = $asReplace ? 'REPLACE INTO' : 'INSERT INTO';
return $this;
}
use CRM_Utils_SQL_EscapeStringTrait;
/**
* Convert to SQL.
*
* @return string
* SQL statement
*/
public function toSQL() {
$columns = "`" . implode('`,`', $this->columns) . "`";
$sql = "{$this->verb} {$this->table} ({$columns}) VALUES";
$nextDelim = '';
foreach ($this->rows as $row) {
$sql .= "{$nextDelim}\n(" . implode(',', $row) . ")";
$nextDelim = ',';
}
$sql .= "\n";
return $sql;
}
/**
* Execute the query.
*
* @param bool $i18nRewrite
* If the system has multilingual features, should the field/table
* names be rewritten?
* @return CRM_Core_DAO
* @see CRM_Core_DAO::executeQuery
* @see CRM_Core_I18n_Schema::rewriteQuery
*/
public function execute($i18nRewrite = TRUE) {
return CRM_Core_DAO::executeQuery($this->toSQL(), [], TRUE, NULL,
FALSE, $i18nRewrite);
}
}