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 | /** | |
43 | * Create a new INSERT query | |
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 | /** | |
54 | * Create a new SELECT query | |
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 | ||
64 | /** | |
65 | * @param array $rows | |
66 | * @return CRM_Utils_SQL_Insert | |
67 | */ | |
68 | public function rows($rows) { | |
69 | foreach ($rows as $row) { | |
70 | $this->row($row); | |
71 | } | |
72 | return $this; | |
73 | } | |
74 | ||
75 | /** | |
76 | * @param array $row | |
77 | * @return CRM_Utils_SQL_Insert | |
78 | * @throws CRM_Core_Exception | |
79 | */ | |
80 | public function row($row) { | |
81 | $columns = array_keys($row); | |
82 | sort($columns); | |
83 | ||
84 | if ($this->columns === NULL) { | |
85 | $this->columns = $columns; | |
86 | } | |
87 | elseif ($this->columns != $columns) { | |
88 | throw new CRM_Core_Exception("Inconsistent column names"); | |
89 | } | |
90 | ||
91 | $escapedRow = array(); | |
92 | foreach ($columns as $column) { | |
93 | $escapedRow[$column] = $this->escapeString($row[$column]); | |
94 | } | |
95 | $this->rows[] = $escapedRow; | |
96 | ||
97 | return $this; | |
98 | } | |
99 | ||
09095684 TO |
100 | /** |
101 | * Use REPLACE INTO instead of INSERT INTO | |
102 | * | |
103 | * @param bool $asReplace | |
104 | * @return CRM_Utils_SQL_Insert | |
105 | */ | |
106 | public function usingReplace($asReplace = TRUE) { | |
107 | $this->verb = $asReplace ? 'REPLACE INTO' : 'INSERT INTO'; | |
108 | return $this; | |
109 | } | |
110 | ||
3e4ef323 TO |
111 | /** |
112 | * @param string|NULL $value | |
a6c01b45 CW |
113 | * @return string |
114 | * SQL expression, e.g. "it\'s great" (with-quotes) or NULL (without-quotes) | |
3e4ef323 TO |
115 | */ |
116 | protected function escapeString($value) { | |
117 | return $value === NULL ? 'NULL' : '"' . CRM_Core_DAO::escapeString($value) . '"'; | |
118 | } | |
119 | ||
120 | /** | |
a6c01b45 CW |
121 | * @return string |
122 | * SQL statement | |
3e4ef323 TO |
123 | */ |
124 | public function toSQL() { | |
125 | $columns = "`" . implode('`,`', $this->columns) . "`"; | |
09095684 | 126 | $sql = "{$this->verb} {$this->table} ({$columns}) VALUES"; |
3e4ef323 TO |
127 | |
128 | $nextDelim = ''; | |
129 | foreach ($this->rows as $row) { | |
130 | $sql .= "{$nextDelim}\n(" . implode(',', $row) . ")"; | |
131 | $nextDelim = ','; | |
132 | } | |
133 | $sql .= "\n"; | |
134 | ||
135 | return $sql; | |
136 | } | |
96025800 | 137 | |
3e4ef323 | 138 | } |