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;
}
