4 * Dear God Why Do I Have To Write This (Dumb SQL Builder)
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();
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.
18 * - No knowledge of the underlying SQL API (except for escaping -- CRM_Core_DAO::escapeString)
19 * - No knowledge of the underlying data model
21 * - SQL clauses correspond to PHP functions ($select->where("foo_id=123"))
23 class CRM_Utils_SQL_Insert
{
25 private $verb = 'INSERT INTO';
38 * Array<string> list of column names
44 * Create a new INSERT query.
46 * @param string $table
47 * Table-name and optional alias.
48 * @return CRM_Utils_SQL_Insert
50 public static function into($table) {
51 return new self($table);
55 * Insert a record based on a DAO.
57 * @param \CRM_Core_DAO $dao
58 * @return \CRM_Utils_SQL_Insert
59 * @throws \CRM_Core_Exception
61 public static function dao(CRM_Core_DAO
$dao) {
62 $table = CRM_Core_DAO
::getLocaleTableName($dao->getTableName());
64 foreach ((array) $dao as $key => $value) {
65 if ($value === 'null') {
69 // Skip '_foobar' and '{\u00}*_options' and 'N'.
70 if (preg_match('/[a-zA-Z]/', $key[0]) && $key !== 'N') {
74 return self
::into($table)->row($row);
78 * Create a new SELECT query.
80 * @param string $table
81 * Table-name and optional alias.
83 public function __construct($table) {
84 $this->table
= $table;
91 * @param array $columns
93 * @return CRM_Utils_SQL_Insert
94 * @throws \CRM_Core_Exception
96 public function columns($columns) {
97 if ($this->columns
!== NULL) {
98 throw new CRM_Core_Exception("Column order already specified.");
100 $this->columns
= $columns;
109 * @return CRM_Utils_SQL_Insert
111 public function rows($rows) {
112 foreach ($rows as $row) {
123 * @return CRM_Utils_SQL_Insert
124 * @throws CRM_Core_Exception
126 public function row($row) {
127 $columns = array_keys($row);
129 if ($this->columns
=== NULL) {
131 $this->columns
= $columns;
133 elseif (array_diff($this->columns
, $columns) !== []) {
134 throw new CRM_Core_Exception("Inconsistent column names");
138 foreach ($this->columns
as $column) {
139 $escapedRow[$column] = $this->escapeString($row[$column]);
141 $this->rows
[] = $escapedRow;
147 * Use REPLACE INTO instead of INSERT INTO.
149 * @param bool $asReplace
151 * @return CRM_Utils_SQL_Insert
153 public function usingReplace($asReplace = TRUE) {
154 $this->verb
= $asReplace ?
'REPLACE INTO' : 'INSERT INTO';
161 * @param string|null $value
164 * SQL expression, e.g. "it\'s great" (with-quotes) or NULL (without-quotes)
166 protected function escapeString($value) {
167 return $value === NULL ?
'NULL' : '"' . CRM_Core_DAO
::escapeString($value) . '"';
176 public function toSQL() {
177 $columns = "`" . implode('`,`', $this->columns
) . "`";
178 $sql = "{$this->verb} {$this->table} ({$columns}) VALUES";
181 foreach ($this->rows
as $row) {
182 $sql .= "{$nextDelim}\n(" . implode(',', $row) . ")";