This function processes the datatypes supported by the DB,
as specified in Types->getColumns() and either returns an array
(useful for quickly checking if a datatype is supported)
or an HTML snippet that creates a drop-down list.
/**
* Composes the query necessary to create a routine from an HTTP request.
*
* @return string The CREATE [ROUTINE | PROCEDURE] query.
*/
function PMA_RTN_getQueryFromRequest()
{
global $_REQUEST, $errors, $param_sqldataaccess, $param_directions, $PMA_Types;
$_REQUEST['item_type'] = isset($_REQUEST['item_type']) ? $_REQUEST['item_type'] : '';
$query = 'CREATE ';
if (!empty($_REQUEST['item_definer'])) {
if (mb_strpos($_REQUEST['item_definer'], '@') !== false) {
$arr = explode('@', $_REQUEST['item_definer']);
$do_backquote = true;
if (substr($arr[0], 0, 1) === "`" && substr($arr[0], -1) === "`") {
$do_backquote = false;
}
$query .= 'DEFINER=' . PMA\libraries\Util::backquote($arr[0], $do_backquote);
$do_backquote = true;
if (substr($arr[1], 0, 1) === "`" && substr($arr[1], -1) === "`") {
$do_backquote = false;
}
$query .= '@' . PMA\libraries\Util::backquote($arr[1], $do_backquote) . ' ';
} else {
$errors[] = __('The definer must be in the "username@hostname" format!');
}
}
if ($_REQUEST['item_type'] == 'FUNCTION' || $_REQUEST['item_type'] == 'PROCEDURE') {
$query .= $_REQUEST['item_type'] . ' ';
} else {
$errors[] = sprintf(__('Invalid routine type: "%s"'), htmlspecialchars($_REQUEST['item_type']));
}
if (!empty($_REQUEST['item_name'])) {
$query .= PMA\libraries\Util::backquote($_REQUEST['item_name']);
} else {
$errors[] = __('You must provide a routine name!');
}
$params = '';
$warned_about_dir = false;
$warned_about_length = false;
if (!empty($_REQUEST['item_param_name']) && !empty($_REQUEST['item_param_type']) && !empty($_REQUEST['item_param_length']) && is_array($_REQUEST['item_param_name']) && is_array($_REQUEST['item_param_type']) && is_array($_REQUEST['item_param_length'])) {
$item_param_name = $_REQUEST['item_param_name'];
$item_param_type = $_REQUEST['item_param_type'];
$item_param_length = $_REQUEST['item_param_length'];
for ($i = 0, $nb = count($item_param_name); $i < $nb; $i++) {
if (!empty($item_param_name[$i]) && !empty($item_param_type[$i])) {
if ($_REQUEST['item_type'] == 'PROCEDURE' && !empty($_REQUEST['item_param_dir'][$i]) && in_array($_REQUEST['item_param_dir'][$i], $param_directions)) {
$params .= $_REQUEST['item_param_dir'][$i] . " " . PMA\libraries\Util::backquote($item_param_name[$i]) . " " . $item_param_type[$i];
} else {
if ($_REQUEST['item_type'] == 'FUNCTION') {
$params .= PMA\libraries\Util::backquote($item_param_name[$i]) . " " . $item_param_type[$i];
} else {
if (!$warned_about_dir) {
$warned_about_dir = true;
$errors[] = sprintf(__('Invalid direction "%s" given for parameter.'), htmlspecialchars($_REQUEST['item_param_dir'][$i]));
}
}
}
if ($item_param_length[$i] != '' && !preg_match('@^(DATE|DATETIME|TIME|TINYBLOB|TINYTEXT|BLOB|TEXT|' . 'MEDIUMBLOB|MEDIUMTEXT|LONGBLOB|LONGTEXT|' . 'SERIAL|BOOLEAN)$@i', $item_param_type[$i])) {
$params .= "(" . $item_param_length[$i] . ")";
} else {
if ($item_param_length[$i] == '' && preg_match('@^(ENUM|SET|VARCHAR|VARBINARY)$@i', $item_param_type[$i])) {
if (!$warned_about_length) {
$warned_about_length = true;
$errors[] = __('You must provide length/values for routine parameters' . ' of type ENUM, SET, VARCHAR and VARBINARY.');
}
}
}
if (!empty($_REQUEST['item_param_opts_text'][$i])) {
if ($PMA_Types->getTypeClass($item_param_type[$i]) == 'CHAR') {
$params .= ' CHARSET ' . mb_strtolower($_REQUEST['item_param_opts_text'][$i]);
}
}
if (!empty($_REQUEST['item_param_opts_num'][$i])) {
if ($PMA_Types->getTypeClass($item_param_type[$i]) == 'NUMBER') {
$params .= ' ' . mb_strtoupper($_REQUEST['item_param_opts_num'][$i]);
}
}
if ($i != count($item_param_name) - 1) {
$params .= ", ";
}
} else {
$errors[] = __('You must provide a name and a type for each routine parameter.');
break;
}
}
}
$query .= "(" . $params . ") ";
if ($_REQUEST['item_type'] == 'FUNCTION') {
$item_returntype = isset($_REQUEST['item_returntype']) ? $_REQUEST['item_returntype'] : null;
if (!empty($item_returntype) && in_array($item_returntype, PMA\libraries\Util::getSupportedDatatypes())) {
$query .= "RETURNS " . $item_returntype;
} else {
$errors[] = __('You must provide a valid return type for the routine.');
}
if (!empty($_REQUEST['item_returnlength']) && !preg_match('@^(DATE|DATETIME|TIME|TINYBLOB|TINYTEXT|BLOB|TEXT|' . 'MEDIUMBLOB|MEDIUMTEXT|LONGBLOB|LONGTEXT|SERIAL|BOOLEAN)$@i', $item_returntype)) {
$query .= "(" . $_REQUEST['item_returnlength'] . ")";
} else {
if (empty($_REQUEST['item_returnlength']) && preg_match('@^(ENUM|SET|VARCHAR|VARBINARY)$@i', $item_returntype)) {
if (!$warned_about_length) {
$errors[] = __('You must provide length/values for routine parameters' . ' of type ENUM, SET, VARCHAR and VARBINARY.');
}
}
}
if (!empty($_REQUEST['item_returnopts_text'])) {
if ($PMA_Types->getTypeClass($item_returntype) == 'CHAR') {
$query .= ' CHARSET ' . mb_strtolower($_REQUEST['item_returnopts_text']);
}
}
if (!empty($_REQUEST['item_returnopts_num'])) {
if ($PMA_Types->getTypeClass($item_returntype) == 'NUMBER') {
$query .= ' ' . mb_strtoupper($_REQUEST['item_returnopts_num']);
}
}
$query .= ' ';
}
if (!empty($_REQUEST['item_comment'])) {
$query .= "COMMENT '" . $GLOBALS['dbi']->escapeString($_REQUEST['item_comment']) . "' ";
}
if (isset($_REQUEST['item_isdeterministic'])) {
$query .= 'DETERMINISTIC ';
} else {
$query .= 'NOT DETERMINISTIC ';
}
if (!empty($_REQUEST['item_sqldataaccess']) && in_array($_REQUEST['item_sqldataaccess'], $param_sqldataaccess)) {
$query .= $_REQUEST['item_sqldataaccess'] . ' ';
}
if (!empty($_REQUEST['item_securitytype'])) {
if ($_REQUEST['item_securitytype'] == 'DEFINER' || $_REQUEST['item_securitytype'] == 'INVOKER') {
$query .= 'SQL SECURITY ' . $_REQUEST['item_securitytype'] . ' ';
}
}
if (!empty($_REQUEST['item_definition'])) {
$query .= $_REQUEST['item_definition'];
} else {
$errors[] = __('You must provide a routine definition.');
}
return $query;
}
