Provide COLLATE clause, if required, to perform case sensitive comparisons
for queries on information_schema.
public static getCollateForIS ( ) : string | ||
return | string | COLLATE clause if needed or empty string. |
/** * This function will generate the values that are required to complete * the "Edit routine" form given the name of a routine. * * @param string $name The name of the routine. * @param string $type Type of routine (ROUTINE|PROCEDURE) * @param bool $all Whether to return all data or just * the info about parameters. * @param bool $control Where to use Controllink to query for * routine information * * @return array Data necessary to create the routine editor. */ function PMA_RTN_getDataFromName($name, $type, $all = true, $control = false) { global $db; $link = null; if ($control) { $link = $GLOBALS['controllink']; } $retval = array(); // Build and execute the query $fields = "SPECIFIC_NAME, ROUTINE_TYPE, DTD_IDENTIFIER, " . "ROUTINE_DEFINITION, IS_DETERMINISTIC, SQL_DATA_ACCESS, " . "ROUTINE_COMMENT, SECURITY_TYPE"; $where = "ROUTINE_SCHEMA " . PMA\libraries\Util::getCollateForIS() . "=" . "'" . $GLOBALS['dbi']->escapeString($db) . "' " . "AND SPECIFIC_NAME='" . $GLOBALS['dbi']->escapeString($name) . "'" . "AND ROUTINE_TYPE='" . $GLOBALS['dbi']->escapeString($type) . "'"; $query = "SELECT {$fields} FROM INFORMATION_SCHEMA.ROUTINES WHERE {$where};"; $routine = $GLOBALS['dbi']->fetchSingleRow($query, 'ASSOC', $link); if (!$routine) { return false; } // Get required data $retval['item_name'] = $routine['SPECIFIC_NAME']; $retval['item_type'] = $routine['ROUTINE_TYPE']; $definition = $GLOBALS['dbi']->getDefinition($db, $routine['ROUTINE_TYPE'], $routine['SPECIFIC_NAME'], $link); if ($definition == NULL) { return false; } $parser = new SqlParser\Parser($definition); /** * @var CreateStatement $stmt */ $stmt = $parser->statements[0]; $params = SqlParser\Utils\Routine::getParameters($stmt); $retval['item_num_params'] = $params['num']; $retval['item_param_dir'] = $params['dir']; $retval['item_param_name'] = $params['name']; $retval['item_param_type'] = $params['type']; $retval['item_param_length'] = $params['length']; $retval['item_param_length_arr'] = $params['length_arr']; $retval['item_param_opts_num'] = $params['opts']; $retval['item_param_opts_text'] = $params['opts']; // Get extra data if (!$all) { return $retval; } if ($retval['item_type'] == 'FUNCTION') { $retval['item_type_toggle'] = 'PROCEDURE'; } else { $retval['item_type_toggle'] = 'FUNCTION'; } $retval['item_returntype'] = ''; $retval['item_returnlength'] = ''; $retval['item_returnopts_num'] = ''; $retval['item_returnopts_text'] = ''; if (!empty($routine['DTD_IDENTIFIER'])) { $options = array(); foreach ($stmt->return->options->options as $opt) { $options[] = is_string($opt) ? $opt : $opt['value']; } $retval['item_returntype'] = $stmt->return->name; $retval['item_returnlength'] = implode(',', $stmt->return->parameters); $retval['item_returnopts_num'] = implode(' ', $options); $retval['item_returnopts_text'] = implode(' ', $options); } $retval['item_definer'] = $stmt->options->has('DEFINER'); $retval['item_definition'] = $routine['ROUTINE_DEFINITION']; $retval['item_isdeterministic'] = ''; if ($routine['IS_DETERMINISTIC'] == 'YES') { $retval['item_isdeterministic'] = " checked='checked'"; } $retval['item_securitytype_definer'] = ''; $retval['item_securitytype_invoker'] = ''; if ($routine['SECURITY_TYPE'] == 'DEFINER') { $retval['item_securitytype_definer'] = " selected='selected'"; } else { if ($routine['SECURITY_TYPE'] == 'INVOKER') { $retval['item_securitytype_invoker'] = " selected='selected'"; } } $retval['item_sqldataaccess'] = $routine['SQL_DATA_ACCESS']; $retval['item_comment'] = $routine['ROUTINE_COMMENT']; return $retval; }
/** * Returns the list of events inside this database * * @param int $pos The offset of the list within the results * @param string $searchClause A string used to filter the results of the query * * @return array */ private function _getEvents($pos, $searchClause) { $maxItems = $GLOBALS['cfg']['MaxNavigationItems']; $retval = array(); $db = $this->real_name; if (!$GLOBALS['cfg']['Server']['DisableIS']) { $escdDb = Util::sqlAddSlashes($db); $query = "SELECT `EVENT_NAME` AS `name` "; $query .= "FROM `INFORMATION_SCHEMA`.`EVENTS` "; $query .= "WHERE `EVENT_SCHEMA` " . Util::getCollateForIS() . "='{$escdDb}' "; if (!empty($searchClause)) { $query .= "AND `EVENT_NAME` LIKE '%"; $query .= Util::sqlAddSlashes($searchClause, true); $query .= "%'"; } $query .= "ORDER BY `EVENT_NAME` ASC "; $query .= "LIMIT " . intval($pos) . ", {$maxItems}"; $retval = $GLOBALS['dbi']->fetchResult($query); } else { $escdDb = Util::backquote($db); $query = "SHOW EVENTS FROM {$escdDb} "; if (!empty($searchClause)) { $query .= "WHERE `Name` LIKE '%"; $query .= Util::sqlAddSlashes($searchClause, true); $query .= "%'"; } $handle = $GLOBALS['dbi']->tryQuery($query); if ($handle !== false) { $count = 0; if ($GLOBALS['dbi']->dataSeek($handle, $pos)) { while ($arr = $GLOBALS['dbi']->fetchArray($handle)) { if ($count < $maxItems) { $retval[] = $arr['Name']; $count++; } else { break; } } } } } return $retval; }
/** * returns details about the TRIGGERs for a specific table or database * * @param string $db db name * @param string $table table name * @param string $delimiter the delimiter to use (may be empty) * * @return array information about triggers (may be empty) */ public function getTriggers($db, $table = '', $delimiter = '//') { $result = array(); if (!$GLOBALS['cfg']['Server']['DisableIS']) { $query = 'SELECT TRIGGER_SCHEMA, TRIGGER_NAME, EVENT_MANIPULATION' . ', EVENT_OBJECT_TABLE, ACTION_TIMING, ACTION_STATEMENT' . ', EVENT_OBJECT_SCHEMA, EVENT_OBJECT_TABLE, DEFINER' . ' FROM information_schema.TRIGGERS' . ' WHERE EVENT_OBJECT_SCHEMA ' . Util::getCollateForIS() . '=' . ' \'' . Util::sqlAddSlashes($db) . '\''; if (!empty($table)) { $query .= " AND EVENT_OBJECT_TABLE " . Util::getCollateForIS() . " = '" . Util::sqlAddSlashes($table) . "';"; } } else { $query = "SHOW TRIGGERS FROM " . Util::backquote($db); if (!empty($table)) { $query .= " LIKE '" . Util::sqlAddSlashes($table, true) . "';"; } } if ($triggers = $this->fetchResult($query)) { foreach ($triggers as $trigger) { if ($GLOBALS['cfg']['Server']['DisableIS']) { $trigger['TRIGGER_NAME'] = $trigger['Trigger']; $trigger['ACTION_TIMING'] = $trigger['Timing']; $trigger['EVENT_MANIPULATION'] = $trigger['Event']; $trigger['EVENT_OBJECT_TABLE'] = $trigger['Table']; $trigger['ACTION_STATEMENT'] = $trigger['Statement']; $trigger['DEFINER'] = $trigger['Definer']; } $one_result = array(); $one_result['name'] = $trigger['TRIGGER_NAME']; $one_result['table'] = $trigger['EVENT_OBJECT_TABLE']; $one_result['action_timing'] = $trigger['ACTION_TIMING']; $one_result['event_manipulation'] = $trigger['EVENT_MANIPULATION']; $one_result['definition'] = $trigger['ACTION_STATEMENT']; $one_result['definer'] = $trigger['DEFINER']; // do not prepend the schema name; this way, importing the // definition into another schema will work $one_result['full_trigger_name'] = Util::backquote($trigger['TRIGGER_NAME']); $one_result['drop'] = 'DROP TRIGGER IF EXISTS ' . $one_result['full_trigger_name']; $one_result['create'] = 'CREATE TRIGGER ' . $one_result['full_trigger_name'] . ' ' . $trigger['ACTION_TIMING'] . ' ' . $trigger['EVENT_MANIPULATION'] . ' ON ' . Util::backquote($trigger['EVENT_OBJECT_TABLE']) . "\n" . ' FOR EACH ROW ' . $trigger['ACTION_STATEMENT'] . "\n" . $delimiter . "\n"; $result[] = $one_result; } } // Sort results by name $name = array(); foreach ($result as $value) { $name[] = $value['name']; } array_multisort($name, SORT_ASC, $result); return $result; }
/** * Returns the names of children of type $type present inside this container * This method is overridden by the PMA\libraries\navigation\nodes\NodeDatabase * and PMA\libraries\navigation\nodes\NodeTable classes * * @param string $type The type of item we are looking for * ('tables', 'views', etc) * @param int $pos The offset of the list within the results * @param string $searchClause A string used to filter the results of the query * * @return array */ public function getData($type, $pos, $searchClause = '') { $maxItems = $GLOBALS['cfg']['MaxNavigationItems']; $retval = array(); $db = $this->realParent()->real_name; $table = $this->real_name; switch ($type) { case 'columns': if (!$GLOBALS['cfg']['Server']['DisableIS']) { $db = Util::sqlAddSlashes($db); $table = Util::sqlAddSlashes($table); $query = "SELECT `COLUMN_NAME` AS `name` "; $query .= "FROM `INFORMATION_SCHEMA`.`COLUMNS` "; $query .= "WHERE `TABLE_NAME`='{$table}' "; $query .= "AND `TABLE_SCHEMA`='{$db}' "; $query .= "ORDER BY `COLUMN_NAME` ASC "; $query .= "LIMIT " . intval($pos) . ", {$maxItems}"; $retval = $GLOBALS['dbi']->fetchResult($query); break; } $db = Util::backquote($db); $table = Util::backquote($table); $query = "SHOW COLUMNS FROM {$table} FROM {$db}"; $handle = $GLOBALS['dbi']->tryQuery($query); if ($handle === false) { break; } $count = 0; if ($GLOBALS['dbi']->dataSeek($handle, $pos)) { while ($arr = $GLOBALS['dbi']->fetchArray($handle)) { if ($count < $maxItems) { $retval[] = $arr['Field']; $count++; } else { break; } } } break; case 'indexes': $db = Util::backquote($db); $table = Util::backquote($table); $query = "SHOW INDEXES FROM {$table} FROM {$db}"; $handle = $GLOBALS['dbi']->tryQuery($query); if ($handle === false) { break; } $count = 0; while ($arr = $GLOBALS['dbi']->fetchArray($handle)) { if (in_array($arr['Key_name'], $retval)) { continue; } if ($pos <= 0 && $count < $maxItems) { $retval[] = $arr['Key_name']; $count++; } $pos--; } break; case 'triggers': if (!$GLOBALS['cfg']['Server']['DisableIS']) { $db = Util::sqlAddSlashes($db); $table = Util::sqlAddSlashes($table); $query = "SELECT `TRIGGER_NAME` AS `name` "; $query .= "FROM `INFORMATION_SCHEMA`.`TRIGGERS` "; $query .= "WHERE `EVENT_OBJECT_SCHEMA` " . Util::getCollateForIS() . "='{$db}' "; $query .= "AND `EVENT_OBJECT_TABLE` " . Util::getCollateForIS() . "='{$table}' "; $query .= "ORDER BY `TRIGGER_NAME` ASC "; $query .= "LIMIT " . intval($pos) . ", {$maxItems}"; $retval = $GLOBALS['dbi']->fetchResult($query); break; } $db = Util::backquote($db); $table = Util::sqlAddSlashes($table); $query = "SHOW TRIGGERS FROM {$db} WHERE `Table` = '{$table}'"; $handle = $GLOBALS['dbi']->tryQuery($query); if ($handle === false) { break; } $count = 0; if ($GLOBALS['dbi']->dataSeek($handle, $pos)) { while ($arr = $GLOBALS['dbi']->fetchArray($handle)) { if ($count < $maxItems) { $retval[] = $arr['Trigger']; $count++; } else { break; } } } break; default: break; } return $retval; }