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 | /** | |
38 | * array<string> list of column names | |
39 | */ | |
40 | private $columns; | |
41 | ||
42 | /** | |
43 | * Create a new INSERT query | |
44 | * | |
45 | * @param string $table table-name and optional alias | |
46 | * @return CRM_Utils_SQL_Insert | |
47 | */ | |
48 | public static function into($table) { | |
49 | return new self($table); | |
50 | } | |
51 | ||
52 | /** | |
53 | * Create a new SELECT query | |
54 | * | |
55 | * @param string $from table-name and optional alias | |
56 | */ | |
57 | public function __construct($table) { | |
58 | $this->table = $table; | |
59 | $this->rows = array(); | |
60 | } | |
61 | ||
62 | /** | |
63 | * @param array $rows | |
64 | * @return CRM_Utils_SQL_Insert | |
65 | */ | |
66 | public function rows($rows) { | |
67 | foreach ($rows as $row) { | |
68 | $this->row($row); | |
69 | } | |
70 | return $this; | |
71 | } | |
72 | ||
73 | /** | |
74 | * @param array $row | |
75 | * @return CRM_Utils_SQL_Insert | |
76 | * @throws CRM_Core_Exception | |
77 | */ | |
78 | public function row($row) { | |
79 | $columns = array_keys($row); | |
80 | sort($columns); | |
81 | ||
82 | if ($this->columns === NULL) { | |
83 | $this->columns = $columns; | |
84 | } | |
85 | elseif ($this->columns != $columns) { | |
86 | throw new CRM_Core_Exception("Inconsistent column names"); | |
87 | } | |
88 | ||
89 | $escapedRow = array(); | |
90 | foreach ($columns as $column) { | |
91 | $escapedRow[$column] = $this->escapeString($row[$column]); | |
92 | } | |
93 | $this->rows[] = $escapedRow; | |
94 | ||
95 | return $this; | |
96 | } | |
97 | ||
09095684 TO |
98 | /** |
99 | * Use REPLACE INTO instead of INSERT INTO | |
100 | * | |
101 | * @param bool $asReplace | |
102 | * @return CRM_Utils_SQL_Insert | |
103 | */ | |
104 | public function usingReplace($asReplace = TRUE) { | |
105 | $this->verb = $asReplace ? 'REPLACE INTO' : 'INSERT INTO'; | |
106 | return $this; | |
107 | } | |
108 | ||
3e4ef323 TO |
109 | /** |
110 | * @param string|NULL $value | |
111 | * @return string SQL expression, e.g. "it\'s great" (with-quotes) or NULL (without-quotes) | |
112 | */ | |
113 | protected function escapeString($value) { | |
114 | return $value === NULL ? 'NULL' : '"' . CRM_Core_DAO::escapeString($value) . '"'; | |
115 | } | |
116 | ||
117 | /** | |
118 | * @return string SQL statement | |
119 | */ | |
120 | public function toSQL() { | |
121 | $columns = "`" . implode('`,`', $this->columns) . "`"; | |
09095684 | 122 | $sql = "{$this->verb} {$this->table} ({$columns}) VALUES"; |
3e4ef323 TO |
123 | |
124 | $nextDelim = ''; | |
125 | foreach ($this->rows as $row) { | |
126 | $sql .= "{$nextDelim}\n(" . implode(',', $row) . ")"; | |
127 | $nextDelim = ','; | |
128 | } | |
129 | $sql .= "\n"; | |
130 | ||
131 | return $sql; | |
132 | } | |
133 | } |