ILIAS  release_6 Revision v6.24-5-g0c8bfefb3b8
•All Data Structures Namespaces Files Functions Variables Modules Pages
Public Member Functions
ilMySQLQueryUtils Class Reference

Class ilMySQLQueryUtils. More...

+ Inheritance diagram for ilMySQLQueryUtils:
+ Collaboration diagram for ilMySQLQueryUtils:

Public Member Functions

 in ($field, $values, $negate=false, $type="")
 
 quote ($value, $type=null)
 
 concat (array $values, $allow_null=true)
 
 locate ($a_needle, $a_string, $a_start_pos=1)
 
 free (ilPDOStatement $statement)
 
 quoteIdentifier ($identifier)
 
 createTable ($name, $fields, $options=array())
 
 like ($column, $type, $value="?", $case_insensitive=true)
 
 now ()
 
 lock (array $tables)
 
 unlock ()
 
 createDatabase ($a_name, $a_charset="utf8", $a_collation="")
 
 groupConcat ($a_field_name, $a_seperator=",", $a_order=null)
 
 cast ($a_field_name, $a_dest_type)
 
Parameters
string$a_field_name
mixed$a_dest_type
Returns
string
More...
 
- Public Member Functions inherited from ilQueryUtils
 __construct (ilDBInterface $ilDBInterface)
 ilMySQLQueryUtils constructor. More...
 
 in ($field, $values, $negate=false, $type="")
 
 quote ($value, $type=null)
 
 concat (array $values, $allow_null=true)
 
 locate ($a_needle, $a_string, $a_start_pos=1)
 
 free (ilPDOStatement $statement)
 
 quoteIdentifier ($identifier)
 
 createTable ($name, $fields, $options=array())
 
 like ($column, $type, $value="?", $case_insensitive=true)
 
 now ()
 
 lock (array $tables)
 
 unlock ()
 
 createDatabase ($a_name, $a_charset="utf8", $a_collation="")
 
 groupConcat ($a_field_name, $a_seperator=",", $a_order=null)
 
Parameters
string$a_field_name
string$a_seperator
string$a_order
Returns
string
More...
 
 cast ($a_field_name, $a_dest_type)
 
Parameters
string$a_field_name
mixed$a_dest_type
Returns
string
More...
 

Additional Inherited Members

- Protected Attributes inherited from ilQueryUtils
 $db_instance
 

Detailed Description

Class ilMySQLQueryUtils.

Author
Fabian Schmid fs@st.nosp@m.uder.nosp@m.-raim.nosp@m.ann..nosp@m.ch

Definition at line 8 of file class.ilMySQLQueryUtils.php.

Member Function Documentation

◆ cast()

ilMySQLQueryUtils::cast (   $a_field_name,
  $a_dest_type 
)

Parameters
string$a_field_name
mixed$a_dest_type
Returns
string

Implements ilQueryUtilsInterface.

Definition at line 324 of file class.ilMySQLQueryUtils.php.

325  {
326  return $a_field_name;
327  }

◆ concat()

ilMySQLQueryUtils::concat ( array  $values,
  $allow_null = true 
)
Parameters
array$values
bool$allow_null
Returns
string

Implements ilQueryUtilsInterface.

Definition at line 58 of file class.ilMySQLQueryUtils.php.

59  {
60  if (!count($values)) {
61  return ' ';
62  }
63 
64  $concat = ' CONCAT(';
65  $first = true;
66  foreach ($values as $field_info) {
67  $val = $field_info[0];
68 
69  if (!$first) {
70  $concat .= ',';
71  }
72 
73  if ($allow_null) {
74  $concat .= 'COALESCE(';
75  }
76  $concat .= $val;
77 
78  if ($allow_null) {
79  $concat .= ",''";
80  $concat .= ')';
81  }
82 
83  $first = false;
84  }
85  $concat .= ') ';
86 
87  return $concat;
88  }

◆ createDatabase()

ilMySQLQueryUtils::createDatabase (   $a_name,
  $a_charset = "utf8",
  $a_collation = "" 
)
Parameters
$a_name
string$a_charset
string$a_collation
Returns
mixed

Implements ilQueryUtilsInterface.

Definition at line 291 of file class.ilMySQLQueryUtils.php.

292  {
293  if ($a_collation != "") {
294  $sql = "CREATE DATABASE `" . $a_name . "` CHARACTER SET " . $a_charset . " COLLATE " . $a_collation;
295  } else {
296  $sql = "CREATE DATABASE `" . $a_name . "` CHARACTER SET " . $a_charset;
297  }
298 
299  return $sql;
300  }

◆ createTable()

ilMySQLQueryUtils::createTable (   $name,
  $fields,
  $options = array() 
)
Parameters
$name
$fields
array$options
Returns
string
Exceptions

Implements ilQueryUtilsInterface.

Definition at line 140 of file class.ilMySQLQueryUtils.php.

References $name, $query, $type, and quote().

141  {
142  if (!$name) {
143  throw new ilDatabaseException('no valid table name specified');
144  }
145  if (empty($fields)) {
146  throw new ilDatabaseException('no fields specified for table "' . $name . '"');
147  }
148  $query_fields_array = array();
149  foreach ($fields as $field_name => $field) {
150  $query_fields_array[] = $this->db_instance->getFieldDefinition()->getDeclaration($field['type'], $field_name, $field);
151  }
152 
153  $query_fields = implode(', ', $query_fields_array);
154 
155  if (!empty($options['primary'])) {
156  $query_fields .= ', PRIMARY KEY (' . implode(', ', array_keys($options['primary'])) . ')';
157  }
158 
159  $query = "CREATE TABLE $name ($query_fields)";
160 
161  $options_strings = array();
162 
163  if (!empty($options['comment'])) {
164  $options_strings['comment'] = 'COMMENT = ' . $this->quote($options['comment'], 'text');
165  }
166 
167  if (!empty($options['charset'])) {
168  $options_strings['charset'] = 'DEFAULT CHARACTER SET ' . $options['charset'];
169  if (!empty($options['collate'])) {
170  $options_strings['charset'] .= ' COLLATE ' . $options['collate'];
171  }
172  }
173 
174  $type = false;
175  if (!empty($options['type'])) {
176  $type = $options['type'];
177  }
178  if ($type) {
179  $options_strings[] = "ENGINE = $type";
180  }
181 
182  if (!empty($options_strings)) {
183  $query .= ' ' . implode(' ', $options_strings);
184  }
185 
186  return $query;
187  }
ilMySQLQueryUtils\quote
quote($value, $type=null)
Definition: class.ilMySQLQueryUtils.php:47
$type
$type
Definition: proxy_ylocal.php:10
ilDatabaseException
Class ilDatabaseException.
Definition: exception.ilDatabaseException.php:9
$name
if($format !==null) $name
Definition: metadata.php:230
$query
$query
Definition: proxy_ylocal.php:13
+ Here is the call graph for this function:

◆ free()

ilMySQLQueryUtils::free ( ilPDOStatement  $statement)
Parameters
\ilPDOStatement$statement
Returns
bool

Implements ilQueryUtilsInterface.

Definition at line 115 of file class.ilMySQLQueryUtils.php.

References ilPDOStatement\closeCursor().

116  {
117  $statement->closeCursor();
118 
119  return true;
120  }
ilPDOStatement\closeCursor
closeCursor()
Pdo allows for a manual closing of the cursor.
Definition: class.ilPDOStatement.php:57
+ Here is the call graph for this function:

◆ groupConcat()

ilMySQLQueryUtils::groupConcat (   $a_field_name,
  $a_seperator = ",",
  $a_order = null 
)
Parameters
string$a_field_name
string$a_seperator
string$a_order
Returns
string

Implements ilQueryUtilsInterface.

Definition at line 310 of file class.ilMySQLQueryUtils.php.

References quote().

311  {
312  if ($a_order === null) {
313  $sql = "GROUP_CONCAT(" . $a_field_name . " SEPARATOR " . $this->quote($a_seperator, "text") . ")";
314  } else {
315  $sql = "GROUP_CONCAT(" . $a_field_name . " ORDER BY " . $a_order . " SEPARATOR " . $this->quote($a_seperator, "text") . ")";
316  }
317  return $sql;
318  }
ilMySQLQueryUtils\quote
quote($value, $type=null)
Definition: class.ilMySQLQueryUtils.php:47
+ Here is the call graph for this function:

◆ in()

ilMySQLQueryUtils::in (   $field,
  $values,
  $negate = false,
  $type = "" 
)
Parameters
string$field
string[]$values
bool$negate
string$type
Returns
string

Implements ilQueryUtilsInterface.

Definition at line 18 of file class.ilMySQLQueryUtils.php.

References $type, and quote().

19  {
20  if (!is_array($values) || count($values) == 0) {
21  // BEGIN fixed mantis #0014191:
22  //return " 1=2 "; // return a false statement on empty array
23  return $negate ? ' 1=1 ' : ' 1=2 ';
24  // END fixed mantis #0014191:
25  }
26  if ($type == "") { // untyped: used ? for prepare/execute
27  $str = $field . (($negate) ? " NOT" : "") . " IN (?" . str_repeat(",?", count($values) - 1) . ")";
28  } else { // typed, use values for query/manipulate
29  $str = $field . (($negate) ? " NOT" : "") . " IN (";
30  $sep = "";
31  foreach ($values as $v) {
32  $str .= $sep . $this->quote($v, $type);
33  $sep = ",";
34  }
35  $str .= ")";
36  }
37 
38  return $str;
39  }
ilMySQLQueryUtils\quote
quote($value, $type=null)
Definition: class.ilMySQLQueryUtils.php:47
$type
$type
Definition: proxy_ylocal.php:10
+ Here is the call graph for this function:

◆ like()

ilMySQLQueryUtils::like (   $column,
  $type,
  $value = "?",
  $case_insensitive = true 
)
Parameters
$column
$type
string$value
bool$case_insensitive
Returns
string
Exceptions

Implements ilQueryUtilsInterface.

Definition at line 198 of file class.ilMySQLQueryUtils.php.

References $type, quote(), ilDBConstants\T_CLOB, and ilDBConstants\T_TEXT.

199  {
200  if (!in_array($type, array(
201  ilDBConstants::T_TEXT,
202  ilDBConstants::T_CLOB,
203  "blob",
204  ))
205  ) {
206  throw new ilDatabaseException("Like: Invalid column type '" . $type . "'.");
207  }
208  if ($value == "?") {
209  if ($case_insensitive) {
210  return "UPPER(" . $column . ") LIKE(UPPER(?))";
211  } else {
212  return $column . " LIKE(?)";
213  }
214  } else {
215  if ($case_insensitive) {
216  // Always quote as text
217  return " UPPER(" . $column . ") LIKE(UPPER(" . $this->quote($value, 'text') . "))";
218  } else {
219  // Always quote as text
220  return " " . $column . " LIKE(" . $this->quote($value, 'text') . ")";
221  }
222  }
223  }
ilMySQLQueryUtils\quote
quote($value, $type=null)
Definition: class.ilMySQLQueryUtils.php:47
$type
$type
Definition: proxy_ylocal.php:10
ilDatabaseException
Class ilDatabaseException.
Definition: exception.ilDatabaseException.php:9
+ Here is the call graph for this function:

◆ locate()

ilMySQLQueryUtils::locate (   $a_needle,
  $a_string,
  $a_start_pos = 1 
)
Parameters
$a_needle
$a_string
int$a_start_pos
Returns
string

Implements ilQueryUtilsInterface.

Definition at line 97 of file class.ilMySQLQueryUtils.php.

98  {
99  $locate = ' LOCATE( ';
100  $locate .= $a_needle;
101  $locate .= ',';
102  $locate .= $a_string;
103  $locate .= ',';
104  $locate .= $a_start_pos;
105  $locate .= ') ';
106 
107  return $locate;
108  }

◆ lock()

ilMySQLQueryUtils::lock ( array  $tables)
Parameters
array$tables
Returns
string

Implements ilQueryUtilsInterface.

Definition at line 239 of file class.ilMySQLQueryUtils.php.

References ilDBConstants\LOCK_READ, and ilDBConstants\LOCK_WRITE.

240  {
241  $lock = 'LOCK TABLES ';
242 
243  $counter = 0;
244  foreach ($tables as $table) {
245  if ($counter++) {
246  $lock .= ', ';
247  }
248 
249  if (isset($table['sequence']) && $table['sequence']) {
250  $table_name = $this->db_instance->getSequenceName($table['name']);
251  } else {
252  $table_name = $table['name'];
253  }
254 
255  $lock .= ($table_name . ' ');
256 
257  if ($table['alias'] ?? null) {
258  $lock .= ($table['alias'] . ' ');
259  }
260 
261  switch ($table['type']) {
262  case ilDBConstants::LOCK_READ:
263  $lock .= ' READ ';
264  break;
265 
266  case ilDBConstants::LOCK_WRITE:
267  $lock .= ' WRITE ';
268  break;
269  }
270  }
271 
272  return $lock;
273  }

◆ now()

ilMySQLQueryUtils::now ( )
Returns
string

Implements ilQueryUtilsInterface.

Definition at line 229 of file class.ilMySQLQueryUtils.php.

230  {
231  return "NOW()";
232  }

◆ quote()

ilMySQLQueryUtils::quote (   $value,
  $type = null 
)
Parameters
mixed$value
null$type
Returns
string

Implements ilQueryUtilsInterface.

Definition at line 47 of file class.ilMySQLQueryUtils.php.

References $type.

Referenced by createTable(), groupConcat(), in(), and like().

48  {
49  return $this->db_instance->quote($value, $type);
50  }
$type
$type
Definition: proxy_ylocal.php:10
+ Here is the caller graph for this function:

◆ quoteIdentifier()

ilMySQLQueryUtils::quoteIdentifier (   $identifier)
Parameters
$identifier
Returns
string

Implements ilQueryUtilsInterface.

Definition at line 127 of file class.ilMySQLQueryUtils.php.

128  {
129  return $this->db_instance->quoteIdentifier($identifier);
130  }

◆ unlock()

ilMySQLQueryUtils::unlock ( )
Returns
string

Implements ilQueryUtilsInterface.

Definition at line 279 of file class.ilMySQLQueryUtils.php.

280  {
281  return 'UNLOCK TABLES';
282  }
The documentation for this class was generated from the following file: