/**
* Parse SQL with types from sql in the form of "INSERT INTO testPreparedStatement(id, name) VALUES(?int, ?varchar)"
* @param string $sql
* @param array $lobs names of clob and blob fields from query
* @return boolean
*/
protected function parseSQL($sql, array $lobs = array())
{
if (empty($this->DBM)) {
$this->log->error("Prepare failed: Database object missing");
return false;
}
if (empty($sql)) {
$this->log->error("Prepare failed: empty SQL statement");
return false;
}
$this->sqlText = $sql;
$this->log->info("Parse Query: {$sql}");
// Build fieldDefs array and replace ?SugarDataType placeholders with a single ?placeholder
$cleanedSql = "";
$nextParam = strpos($sql, "?");
if ($nextParam == 0) {
$cleanedSql = $sql;
} else {
// parse the sql string looking for params
$row = 0;
while ($nextParam > 0) {
$cleanedSql .= substr($sql, 0, $nextParam + 1);
// we want the ?
$sql = substr($sql, $nextParam + 1);
// strip leading chars
// scan for termination of SugarDataType
$sugarDataType = "";
for ($i = 0; $i < strlen($sql) and strpos(",) ", substr($sql, $i, 1)) === false; $i++) {
if (strpos(",) ", substr($sql, $i, 1)) == false) {
$sugarDataType .= substr($sql, $i, 1);
}
}
if ($sugarDataType === "") {
// no type, default to varchar
$sugarDataType = 'varchar';
}
// insert the fieldDef
$this->fieldDefs[$row]['type'] = $sugarDataType;
if ($this->DBM->isTextType($sugarDataType)) {
if (empty($lobs)) {
$this->log->fatal('Name of lob field is not specified: ' . $this->sqlText);
return false;
}
$this->lobFields[$row] = array_shift($lobs);
}
$sql = substr($sql, $i);
// strip off the SugarDataType
$nextParam = strpos($sql, "?");
// look for another param
$row++;
}
// add the remaining sql
$cleanedSql .= $sql;
}
$this->parsedSQL = $cleanedSql;
return true;
}
/**
* Prefixes column names with this bean's table name.
*
* @param string $order_by Order by clause to be processed
* @param SugarBean $submodule name of the module this order by clause is for
* @param boolean $suppress_table_name Whether table name should be suppressed
* @return string Processed order by clause
*
* Internal function, do not override.
*/
public function process_order_by($order_by, $submodule = null, $suppress_table_name = false)
{
if (empty($order_by)) {
return $order_by;
}
//submodule is empty,this is for list object in focus
if (empty($submodule)) {
$bean_queried = $this;
} else {
//submodule is set, so this is for subpanel, use submodule
$bean_queried = $submodule;
}
$raw_elements = explode(',', $order_by);
$valid_elements = array();
foreach ($raw_elements as $key => $value) {
$is_valid = false;
//value might have ascending and descending decorations
$list_column = preg_split('/\\s/', trim($value), 2);
$list_column = array_map('trim', $list_column);
$list_column_name = $list_column[0];
if (isset($bean_queried->field_defs[$list_column_name])) {
$field_defs = $bean_queried->field_defs[$list_column_name];
$source = isset($field_defs['source']) ? $field_defs['source'] : 'db';
if (empty($field_defs['table']) && !$suppress_table_name) {
if ($source == 'db') {
$list_column[0] = $bean_queried->table_name . '.' . $list_column[0];
} elseif ($source == 'custom_fields') {
$list_column[0] = $bean_queried->table_name . '_cstm.' . $list_column[0];
}
}
// Bug 38803 - Use CONVERT() function when doing an order by on ntext, text, and image fields
if ($source != 'non-db' && $this->db->isTextType($this->db->getFieldType($bean_queried->field_defs[$list_column_name]))) {
// array(10000) is for db2 only. It tells db2manager to cast 'clob' to varchar(10000) for this 'sort by' column
$list_column[0] = $this->db->convert($list_column[0], "text2char", array(10000));
}
$is_valid = true;
if (isset($list_column[1])) {
switch (strtolower($list_column[1])) {
case 'asc':
case 'desc':
break;
default:
$GLOBALS['log']->debug("process_order_by: ({$list_column['1']}) is not a valid order.");
unset($list_column[1]);
break;
}
}
} else {
$GLOBALS['log']->debug("process_order_by: ({$list_column['0']}) does not have a vardef entry.");
}
if ($is_valid) {
$valid_elements[$key] = implode(' ', $list_column);
}
}
return implode(', ', $valid_elements);
}
/**
* Prefixes column names with this bean's table name.
*
* @param string $order_by Order by clause to be processed
* @param SugarBean $submodule name of the module this order by clause is for
* @return string Processed order by clause
*
* Internal function, do not override.
*/
function process_order_by($order_by, $submodule = null)
{
if (empty($order_by)) {
return $order_by;
}
//submodule is empty,this is for list object in focus
if (empty($submodule)) {
$bean_queried = $this;
} else {
//submodule is set, so this is for subpanel, use submodule
$bean_queried = $submodule;
}
$elements = explode(',', $order_by);
foreach ($elements as $key => $value) {
if (strchr($value, '.') === false) {
//value might have ascending and descending decorations
$list_column = explode(' ', trim($value));
if (isset($list_column[0])) {
$list_column_name = trim($list_column[0]);
if (isset($bean_queried->field_defs[$list_column_name])) {
$source = isset($bean_queried->field_defs[$list_column_name]['source']) ? $bean_queried->field_defs[$list_column_name]['source'] : 'db';
if (empty($bean_queried->field_defs[$list_column_name]['table']) && $source == 'db') {
$list_column[0] = $bean_queried->table_name . "." . $list_column[0];
}
if (empty($bean_queried->field_defs[$list_column_name]['table']) && $source == 'custom_fields') {
$list_column[0] = $bean_queried->table_name . "_cstm." . $list_column[0];
}
// Bug 38803 - Use CONVERT() function when doing an order by on ntext, text, and image fields
if ($source != 'non-db' && $this->db->isTextType($this->db->getFieldType($bean_queried->field_defs[$list_column_name]))) {
$list_column[0] = $this->db->convert($list_column[0], "text2char");
}
$value = implode(' ', $list_column);
} else {
$GLOBALS['log']->debug("process_order_by: ({$list_column['0']}) does not have a vardef entry.");
}
}
}
$elements[$key] = $value;
}
return implode(',', $elements);
}
/**
* Prefixes column names with this bean's table name.
*
* @param string $order_by Order by clause to be processed
* @param SugarBean $submodule name of the module this order by clause is for
* @param boolean $suppress_table_name Whether table name should be suppressed
* @param array $field_map Map of bean fields to query columns
* @return string Processed order by clause
*
* Internal function, do not override.
* @deprecated Use SugarQuery & $this->fetchFromQuery() instead
*/
public function process_order_by($order_by, $submodule = null, $suppress_table_name = false, $field_map = array())
{
if (empty($order_by)) {
return $order_by;
}
//submodule is empty,this is for list object in focus
if (empty($submodule)) {
$bean_queried = $this;
} else {
//submodule is set, so this is for subpanel, use submodule
$bean_queried = $submodule;
}
$raw_elements = explode(',', $order_by);
$valid_elements = array();
foreach ($raw_elements as $key => $value) {
$is_valid = false;
//value might have ascending and descending decorations
$list_column = preg_split('/\\s/', trim($value), 2);
$list_column = array_map('trim', $list_column);
$list_column_name = $list_column[0];
// check if it contains table name, eg tasks.name
if (($pos = strpos($list_column_name, ".")) !== false) {
$list_column_name = substr($list_column_name, $pos + 1);
}
if (isset($bean_queried->field_defs[$list_column_name])) {
$field_defs = $bean_queried->field_defs[$list_column_name];
$source = isset($field_defs['source']) ? $field_defs['source'] : 'db';
if (empty($field_defs['table']) && !$suppress_table_name) {
if ($source == 'db') {
$list_column[0] = $bean_queried->table_name . '.' . $list_column_name;
} elseif ($source == 'custom_fields') {
$list_column[0] = $bean_queried->table_name . '_cstm.' . $list_column_name;
}
}
// Bug 38803 - Use CONVERT() function when doing an order by on ntext, text, and image fields
if ($source != 'non-db' && $this->db->isTextType($this->db->getFieldType($bean_queried->field_defs[$list_column_name]))) {
$list_column[0] = $this->db->convert($list_column[0], "text2char");
}
$is_valid = true;
if (isset($list_column[1])) {
switch (strtolower($list_column[1])) {
case 'asc':
case 'desc':
break;
default:
$GLOBALS['log']->debug("process_order_by: ({$list_column['1']}) is not a valid order.");
unset($list_column[1]);
break;
}
}
} else {
$GLOBALS['log']->debug("process_order_by: ({$list_column['0']}) does not have a vardef entry.");
}
if ($is_valid) {
// Determine order by direction. Will be the same for multiple columns.
$order = isset($list_column[1]) ? $list_column[1] : '';
if (isset($field_map[$list_column_name])) {
foreach ($field_map[$list_column_name] as $field) {
$valid_elements[$field] = $field . ' ' . $order;
}
} else {
$valid_elements[$list_column[0]] = implode(' ', $list_column);
}
// Apply `ORDER BY` stability if not implied by db backend
if (!$this->db->supports('order_stability')) {
if ($suppress_table_name) {
$stableCol = 'id';
} else {
$stableCol = $bean_queried->getTableName() . '.id';
}
$valid_elements[$stableCol] = "{$stableCol} {$order}";
}
}
}
return implode(', ', $valid_elements);
}
