ILIAS  trunk Revision v11.0_alpha-3011-gc6b235a2e85
class.ilMySQLQueryUtils.php
Go to the documentation of this file.
1<?php
2
19declare(strict_types=1);
20
26{
30 public function in(string $field, array $values, bool $negate = false, string $type = ""): string
31 {
32 if (!is_array($values) || $values === []) {
33 // BEGIN fixed mantis #0014191:
34 //return " 1=2 "; // return a false statement on empty array
35 return $negate ? ' 1=1 ' : ' 1=2 ';
36 // END fixed mantis #0014191:
37 }
38 if ($type === "") { // untyped: used ? for prepare/execute
39 $str = $field . (($negate) ? " NOT" : "") . " IN (?" . str_repeat(",?", count($values) - 1) . ")";
40 } else { // typed, use values for query/manipulate
41 $str = $field . (($negate) ? " NOT" : "") . " IN (";
42 $sep = "";
43 foreach ($values as $v) {
44 $str .= $sep . $this->quote($v, $type);
45 $sep = ",";
46 }
47 $str .= ")";
48 }
49
50 return $str;
51 }
52
56 public function quote($value, ?string $type = null): string
57 {
58 return $this->db_instance->quote($value, $type);
59 }
60
61 public function concat(array $values, bool $allow_null = true): string
62 {
63 if ($values === []) {
64 return ' ';
65 }
66
67 $concat = ' CONCAT(';
68 $first = true;
69 foreach ($values as $field_info) {
70 $val = $field_info[0];
71
72 if (!$first) {
73 $concat .= ',';
74 }
75
76 if ($allow_null) {
77 $concat .= 'COALESCE(';
78 }
79 $concat .= $val;
80
81 if ($allow_null) {
82 $concat .= ",''";
83 $concat .= ')';
84 }
85
86 $first = false;
87 }
88
89 return $concat . ') ';
90 }
91
92 public function locate(string $a_needle, string $a_string, int $a_start_pos = 1): string
93 {
94 $locate = ' LOCATE( ';
95 $locate .= $a_needle;
96 $locate .= ',';
97 $locate .= $a_string;
98 $locate .= ',';
99 $locate .= $a_start_pos;
100 $locate .= ') ';
101
102 return $locate;
103 }
104
105 public function free(ilPDOStatement $statement): bool
106 {
107 $statement->closeCursor();
108
109 return true;
110 }
111
112 public function quoteIdentifier(string $identifier): string
113 {
114 return $this->db_instance->quoteIdentifier($identifier);
115 }
116
120 public function createTable(string $name, array $fields, array $options = []): string
121 {
122 if ($name === '') {
123 throw new ilDatabaseException('no valid table name specified');
124 }
125 if (empty($fields)) {
126 throw new ilDatabaseException('no fields specified for table "' . $name . '"');
127 }
128 $query_fields_array = [];
129 $fd = $this->db_instance->getFieldDefinition();
130 if ($fd !== null) {
131 foreach ($fields as $field_name => $field) {
132 $query_fields_array[] = $fd->getDeclaration(
133 $field['type'],
134 $field_name,
135 $field
136 );
137 }
138 }
139
140 $query_fields = implode(', ', $query_fields_array);
141
142 if (!empty($options['primary'])) {
143 $query_fields .= ', PRIMARY KEY (' . implode(', ', array_keys($options['primary'])) . ')';
144 }
145
146 $query = "CREATE TABLE $name ($query_fields)";
147
148 $options_strings = [];
149
150 if (!empty($options['comment'])) {
151 $options_strings['comment'] = 'COMMENT = ' . $this->quote($options['comment'], 'text');
152 }
153
154 if (!empty($options['charset'])) {
155 $options_strings['charset'] = 'DEFAULT CHARACTER SET ' . $options['charset'];
156 if (!empty($options['collate'])) {
157 $options_strings['charset'] .= ' COLLATE ' . $options['collate'];
158 }
159 }
160
161 $type = false;
162 if (!empty($options['type'])) {
163 $type = $options['type'];
164 }
165 if ($type) {
166 $options_strings[] = "ENGINE = $type";
167 }
168
169 if (!empty($options_strings)) {
170 $query .= ' ' . implode(' ', $options_strings);
171 }
172
173 return $query;
174 }
175
179 public function like(string $column, string $type, string $value = "?", bool $case_insensitive = true): string
180 {
181 if (!in_array($type, [
184 "blob",
185 ], true)
186 ) {
187 throw new ilDatabaseException("Like: Invalid column type '" . $type . "'.");
188 }
189 if ($value === "?") {
190 if ($case_insensitive) {
191 return "UPPER(" . $column . ") LIKE(UPPER(?))";
192 }
193
194 return $column . " LIKE(?)";
195 }
196
197 if ($case_insensitive) {
198 // Always quote as text
199 return " UPPER(" . $column . ") LIKE(UPPER(" . $this->quote($value, 'text') . "))";
200 }
201
202 // Always quote as text
203 return " " . $column . " LIKE(" . $this->quote($value, 'text') . ")";
204 }
205
206 public function now(): string
207 {
208 return "NOW()";
209 }
210
211 public function lock(array $tables): string
212 {
213 $lock = 'LOCK TABLES ';
214
215 $counter = 0;
216 foreach ($tables as $table) {
217 if ($counter++ !== 0) {
218 $lock .= ', ';
219 }
220
221 if (isset($table['sequence']) && $table['sequence']) {
222 $table_name = $this->db_instance->getSequenceName($table['name']);
223 } else {
224 $table_name = $table['name'];
225 }
226
227 $lock .= ($table_name . ' ');
228
229 if ($table['alias'] ?? null) {
230 $lock .= ($table['alias'] . ' ');
231 }
232
233 switch ($table['type']) {
235 $lock .= ' READ ';
236 break;
237
239 $lock .= ' WRITE ';
240 break;
241 }
242 }
243
244 return $lock;
245 }
246
247 public function unlock(): string
248 {
249 return 'UNLOCK TABLES';
250 }
251
252 public function createDatabase(string $name, string $charset = "utf8", string $collation = ""): string
253 {
254 if ($collation !== "") {
255 return "CREATE DATABASE `" . $name . "` CHARACTER SET " . $charset . " COLLATE " . $collation;
256 }
257
258 return "CREATE DATABASE `" . $name . "` CHARACTER SET " . $charset;
259 }
260
261 public function groupConcat(string $field_name, string $seperator = ",", ?string $order = null): string
262 {
263 if ($order === null) {
264 return "GROUP_CONCAT(" . $field_name . " SEPARATOR " . $this->quote($seperator, "text") . ")";
265 }
266 return "GROUP_CONCAT(" . $field_name . " ORDER BY " . $order . " SEPARATOR " . $this->quote(
267 $seperator,
268 "text"
269 ) . ")";
270 }
271
275 public function cast(string $a_field_name, $a_dest_type): string
276 {
277 return $a_field_name;
278 }
279}
Class ilDatabaseException.
Class ilMySQLQueryUtils.
free(ilPDOStatement $statement)
cast(string $a_field_name, $a_dest_type)
groupConcat(string $field_name, string $seperator=",", ?string $order=null)
createTable(string $name, array $fields, array $options=[])
concat(array $values, bool $allow_null=true)
quote($value, ?string $type=null)
like(string $column, string $type, string $value="?", bool $case_insensitive=true)
locate(string $a_needle, string $a_string, int $a_start_pos=1)
createDatabase(string $name, string $charset="utf8", string $collation="")
in(string $field, array $values, bool $negate=false, string $type="")
quoteIdentifier(string $identifier)
Class ilPDOStatement is a Wrapper Class for PDOStatement.
closeCursor()
Pdo allows for a manual closing of the cursor.
Class ilQueryUtils.
$counter