/** * 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); }