/**
* Get all beans from link
* @see Link2::query()
*/
public function query($params)
{
unset($params['return_as_array']);
$query = $this->getQuery($params);
$result = $this->db->query($query);
$rows = array();
while ($row = $this->db->fetchByAssoc($result, false)) {
$rows[$row['id']] = $row;
}
return array("rows" => $rows);
}
/**
* @param string $table_name
* @param array $join_key_values
*
* @return bool
*/
function relationship_exists($table_name, $join_key_values)
{
// find the key values for the table.
$dup_keys = $this->_get_alternate_key_fields($table_name);
if (empty($dup_keys)) {
Log::debug("No alternate key define, skipping duplicate check..");
return false;
}
$delimiter = '';
$this->_duplicate_where = ' WHERE ';
foreach ($dup_keys as $field) {
//look for key in $join_key_values, if found add to filter criteria else abort duplicate checking.
if (isset($join_key_values[$field])) {
$this->_duplicate_where .= $delimiter . ' ' . $field . "='" . $join_key_values[$field] . "'";
$delimiter = 'AND';
} else {
Log::error('Duplicate checking aborted, Please supply a value for this column ' . $field);
return false;
}
}
//add deleted check.
$this->_duplicate_where .= $delimiter . ' deleted=0';
$query = 'SELECT id FROM ' . $table_name . $this->_duplicate_where;
Log::debug("relationship_exists query(" . $query . ')');
$result = $this->_db->query($query, true);
$row = $this->_db->fetchByAssoc($result);
if ($row == null) {
return false;
} else {
$this->_duplicate_key = $row['id'];
return true;
}
}
/**
* Fetch the next job in the queue and mark it running
* @param string $clientID ID of the client requesting the job
* @return SugarJob
*/
public function nextJob($clientID)
{
$now = $this->db->now();
$queued = SchedulersJob::JOB_STATUS_QUEUED;
$try = $this->jobTries;
while ($try--) {
// TODO: tranaction start?
$id = $this->db->getOne("SELECT id FROM {$this->job_queue_table} WHERE execute_time <= {$now} AND status = '{$queued}' ORDER BY date_entered ASC");
if (empty($id)) {
return null;
}
$job = new SchedulersJob();
$job->retrieve($id);
if (empty($job->id)) {
return null;
}
$job->status = SchedulersJob::JOB_STATUS_RUNNING;
$job->client = $clientID;
$client = $this->db->quote($clientID);
// using direct query here to be able to fetch affected count
// if count is 0 this means somebody changed the job status and we have to try again
$res = $this->db->query("UPDATE {$this->job_queue_table} SET status='{$job->status}', date_modified={$now}, client='{$client}' WHERE id='{$job->id}' AND status='{$queued}'");
if ($this->db->getAffectedRowCount($res) == 0) {
// somebody stole our job, try again
continue;
} else {
// to update dates & possible hooks
$job->save();
break;
}
// TODO: commit/check?
}
return $job;
}
/**
* Returns report schedule properties
*
* @param string $id Report schedule ID
*
* @return array
*/
public function getInfo($id)
{
$query = "SELECT report_id, next_run, time_interval\n FROM {$this->table_name}\n WHERE id = " . $this->db->quoted($id);
$result = $this->db->query($query);
$row = $this->db->fetchByAssoc($result);
$row = $this->fromConvertReportScheduleDBRow($row);
return $row;
}
/**
* Remove list of ids from fts_queue table
* @param array $deleteIDs
*/
private function delFtsIDs($deleteIDs)
{
$tableName = self::QUEUE_TABLE;
$inClause = implode("','", $deleteIDs);
$query = "DELETE FROM {$tableName} WHERE id in ('{$inClause}')";
$GLOBALS['log']->debug("DELETE BEAN QUERY IS: {$query}");
$this->db->query($query);
}
/**
* Run the query and return the db result object
* @return db result object
*/
protected function runQuery()
{
$sql = $this->compileSql();
if ($this->usePreparedStatements) {
return $this->db->preparedQuery($sql, $this->data);
} else {
return $this->db->query($sql);
}
}
/**
* doUpdateUsDollarRate
*
* execute the standard sql query for updating rates.
* to use a specific query, override doCustomUpdateUsDollarRate()
* in your extended class and make your own.
*
* @access protected
* @param string $tableName
* @param string $usDollarColumn
* @param string $amountColumn
* @param string $currencyId
* @return boolean true on success
*/
protected function doUpdateUsDollarRate($tableName, $usDollarColumn, $amountColumn, $currencyId)
{
// setup SQL statement
$query = sprintf("UPDATE %s SET %s = %s / base_rate where currency_id = '%s'", $tableName, $usDollarColumn, $amountColumn, $currencyId);
// execute
$result = $this->db->query($query, true, string_format($GLOBALS['app_strings']['ERR_DB_QUERY'], array('CurrencyRateUpdate', $query)));
if (empty($result)) {
return false;
}
return true;
}
/**
* @ticket 34892
*/
public function test_Bug34892_MssqlNotClearingErrorResults()
{
// execute a bad query
$this->_db->query("select dsdsdsdsdsdsdsdsdsd", false, "test_Bug34892_MssqlNotClearingErrorResults", true);
// assert it found an error
$this->assertNotEmpty($this->_db->lastError(), "lastError should return true as a result of the previous illegal query");
// now, execute a good query
$this->_db->query("select * from config");
// and make no error messages are asserted
$this->assertEmpty($this->_db->lastError(), "lastError should have cleared the previous error and return false of the last legal query");
}
function retrieve_relationships($table, $values, $select_id)
{
$query = "SELECT {$select_id} FROM {$table} WHERE deleted = 0 ";
foreach ($values as $name => $value) {
$query .= " AND {$name} = '{$value}' ";
}
$query .= " ORDER BY {$select_id} ";
$result = $this->db->query($query, false, "Retrieving Relationship:" . $query);
$ids = array();
while ($row = $this->db->fetchByAssoc($result)) {
$ids[] = $row;
}
return $ids;
}
public static function storeCrashReport($email, $crash_report)
{
try {
$crashTable = DBManager::$mysql_crash_table;
$sql = "INSERT INTO {$crashTable} (`EMAIL`, `CRASH_REPORT`) VALUES ('{$email}','{$crash_report}') ON DUPLICATE KEY UPDATE EMAIL = VALUES(EMAIL) , CRASH_REPORT = VALUES (CRASH_REPORT)";
$result = DBManager::query($sql);
if (DBManager::$local_debug) {
echo $result;
}
} catch (Exception $e) {
if (DBManager::$local_debug) {
echo "Got exception";
}
}
}
/**
* @depends testAddPrimaryKey
*/
public function testRemovePrimaryKey()
{
$tablename = 'testConstraints';
$this->created[$tablename] = true;
$sql = $this->_db->add_drop_constraint($tablename, array('name' => 'testConstraints_pk', 'type' => 'primary', 'fields' => array('id')), true);
$result = $this->_db->query($sql);
$indices = $this->_db->get_indices($tablename);
// find if any are primary
$found = false;
foreach ($indices as $index) {
if ($index['type'] == "primary") {
$found = true;
break;
}
}
$this->assertFalse($found, 'Primary Key Found On Table');
}
/**
* DEPRECATED.
*/
public function retrieve()
{
if (!isset($this->bean)) {
$GLOBALS['log']->fatal('DynamicField retrieve, bean not instantiated');
return false;
}
if (!$this->bean->hasCustomFields()) {
return false;
}
$query = 'SELECT * FROM ' . $this->bean->table_name . "_cstm WHERE id_c='" . $this->bean->id . "'";
$result = $this->db->query($query);
$row = $this->db->fetchByAssoc($result);
if ($row) {
foreach ($row as $name => $value) {
// originally in pre-r30895 we checked if this field was in avail_fields i.e., in fields_meta_data and not deleted
// with the removal of avail_fields post-r30895 we have simplified this - we now retrieve every custom field even if previously deleted
// this is considered harmless as the value although set in the bean will not otherwise be used (nothing else works off the list of fields in the bean)
$this->bean->{$name} = $value;
}
}
return true;
}
$messages = array();
while($row = $dbManager->fetch_assoc($result)) {
array_push($messages, $row);
}
echo json_encode($messages);
});
$app->get('/classes/:className/where/jsonString', function ( $className ) {
$dbManager = new DBManager();
$result = $dbManager->query("SELECT * FROM ".$className);
$messages = array();
while($row = $dbManager->fetch_assoc($result)) {
array_push($messages, $row);
}
echo json_encode($messages);
});
$app->run();
/**
* @param string $lhs_module
* @param string $rhs_module
* @param DBManager $db
* @param string $type
*
* @return array|null
*/
function retrieve_by_modules($lhs_module, $rhs_module, &$db, $type = '')
{
//give it the relationship_name and base module
//it will return the module name on the other side of the relationship
$query = "\tSELECT * FROM relationships\n\t\t\t\t\tWHERE deleted=0\n\t\t\t\t\tAND (\n\t\t\t\t\t(lhs_module = '" . $lhs_module . "' AND rhs_module = '" . $rhs_module . "')\n\t\t\t\t\tOR\n\t\t\t\t\t(lhs_module = '" . $rhs_module . "' AND rhs_module = '" . $lhs_module . "')\n\t\t\t\t\t)\n\t\t\t\t\t";
if (!empty($type)) {
$query .= " AND relationship_type='{$type}'";
}
$result = $db->query($query, true, " Error searching relationships table..");
$row = $db->fetchByAssoc($result);
if ($row != null) {
return $row['relationship_name'];
}
return null;
}
/**
* @param $metadata
* @param $module
* @return bool|resource
*/
public function updateDashboardsMetadata($metadata, $module)
{
return $this->dbManager->query(sprintf("\n UPDATE dashboards SET\n metadata = '%s'\n WHERE\n deleted = 0\n AND name = 'LBL_DEFAULT_DASHBOARD_TITLE'\n AND dashboard_module = '{$module}'\n AND view_name = 'record'\n AND dashboard_type = 'dashboard'\n ", $this->dbManager->quote(json_encode($metadata))));
}
/**
* Initialize Sugar environment
*/
protected function initSugar()
{
if ($this->sugar_initialized) {
return;
}
// BR-385 - This fixes the issues around SugarThemeRegistry fatals. The cache needs rebuild on stage-post init of sugar
if ($this->current_stage == 'post') {
$this->cleanFileCache();
}
if (!defined('sugarEntry')) {
define('sugarEntry', true);
}
$this->log("Initializing SugarCRM environment");
global $beanFiles, $beanList, $objectList, $timedate, $moduleList, $modInvisList, $sugar_config, $locale, $sugar_version, $sugar_flavor, $sugar_build, $sugar_db_version, $sugar_timestamp, $db, $locale, $installing, $bwcModules, $app_list_strings, $modules_exempt_from_availability_check;
$installing = true;
include 'include/entryPoint.php';
$installing = false;
$GLOBALS['current_language'] = $this->config['default_language'];
if (empty($GLOBALS['current_language'])) {
$GLOBALS['current_language'] = 'en_us';
}
$GLOBALS['log'] = LoggerManager::getLogger('SugarCRM');
$this->db = $GLOBALS['db'] = DBManagerFactory::getInstance();
//Once we have a DB, we can do a full cache clear
if ($this->current_stage == 'post') {
$this->cleanCaches();
}
SugarApplication::preLoadLanguages();
$timedate = TimeDate::getInstance();
if (empty($locale)) {
if (method_exists('Localization', 'getObject')) {
$locale = Localization::getObject();
} else {
$locale = new Localization();
}
}
if (!isset($_SERVER['REQUEST_URI'])) {
$_SERVER['REQUEST_URI'] = '';
}
// Load user
$GLOBALS['current_user'] = $this->getUser();
// Prepare DB
if ($this->config['dbconfig']['db_type'] == 'mysql') {
//Change the db wait_timeout for this session
$now_timeout = $this->db->getOne("select @@wait_timeout");
$this->db->query("set wait_timeout=28800");
$now_timeout = $this->db->getOne("select @@wait_timeout");
$this->log("DB timeout set to {$now_timeout}");
}
// stop trackers
$trackerManager = TrackerManager::getInstance(true);
$trackerManager->pause();
$trackerManager->unsetMonitors();
$this->sugar_initialized = true;
$this->loadStrings();
$GLOBALS['app_list_strings'] = return_app_list_strings_language($GLOBALS['current_language']);
$this->log("Done initializing SugarCRM environment");
}
$db = new DBManager($DB_MANAGER->DB_ORACLE8);
$db->parseURL("db.oci8://reward:reward2005@flexim");
$db->connect();
$SQLCmd = "select * from watch";
*/
$db = new DBManager($DB_MANAGER->DB_POSTGRES);
$db->parseURL("db.postgres://*****:*****@127.0.0.1:5432/flextor");
$db->connect();
$SQLCmd = "SELECT * FROM ipm_sc.modules";
if (!$db->isConnect) {
echo "not connected<br>";
echo $db->getHost() . "<br>";
echo $db->getPort() . "<br>";
echo $db->getUser() . "<br>";
echo $db->getPassword() . "<br>";
}
/* Querying Database */
//$resultsets = new ResultSet();
$resultsets = new ResultSet();
$resultsets = $db->query($SQLCmd);
echo "Result Count = " . $resultsets->size() . " column = " . $resultsets->columnSize() . "<br>";
$counter = 0;
while ($resultsets->next()) {
$counter++;
echo $counter . " ";
echo $resultsets->get(0) . " ";
echo $resultsets->get(1) . " ";
echo $resultsets->get(2) . "<br>";
}
/* DISCONNECT */
$db->disconnect();
/**
* Return the module name on the other side of the relationship
* @param string $lhs_module Left side module
* @param string $rhs_module Right side module
* @param DBManager $db
* @return string|null
*/
public function retrieve_by_sides($lhs_module, $rhs_module, $db)
{
//give it the relationship_name and base module
//it will return the module name on the other side of the relationship
$query = "SELECT * FROM relationships WHERE deleted=0 AND lhs_module = '" . $lhs_module . "' AND rhs_module = '" . $rhs_module . "'";
$result = $db->query($query, true, " Error searching relationships table.");
$row = $db->fetchByAssoc($result);
if ($row != null) {
return $this->convertRow($row);
}
return null;
}
/**
* Soft Delete all the Revenue Line Item Reports, this should only be called when switching from Opps w/ RLIs to
* Opps w/o RLIs
*/
public function deleteAllRevenueLineItemReports()
{
$sql = "UPDATE saved_reports set deleted = 1 WHERE module = 'RevenueLineItems' AND deleted = 0";
$this->db->query($sql);
}
/**
* If auditing is enabled, create the audit table.
*
* Function is used by the install scripts and a repair utility in the admin panel.
*
* Internal function, do not override.
*/
function create_audit_table()
{
global $dictionary;
$table_name = $this->get_audit_table_name();
require 'metadata/audit_templateMetaData.php';
$fieldDefs = $dictionary['audit']['fields'];
$sql = $this->dbManager->helper->createTableSQLParams($table_name, $fieldDefs, array());
$msg = "Error creating table: " . $table_name . ":";
$this->dbManager->query($sql, true, $msg);
}
/**
* If auditing is enabled, create the audit table.
*
* Function is used by the install scripts and a repair utility in the admin panel.
*
* Internal function, do not override.
*/
function create_audit_table()
{
global $dictionary;
$table_name = $this->get_audit_table_name();
require 'metadata/audit_templateMetaData.php';
$fieldDefs = $dictionary['audit']['fields'];
$indices = $dictionary['audit']['indices'];
// '0' stands for the first index for all the audit tables
$indices[0]['name'] = 'idx_' . strtolower($this->getTableName()) . '_' . $indices[0]['name'];
$indices[1]['name'] = 'idx_' . strtolower($this->getTableName()) . '_' . $indices[1]['name'];
$engine = null;
if (isset($dictionary['audit']['engine'])) {
$engine = $dictionary['audit']['engine'];
} else {
if (isset($dictionary[$this->getObjectName()]['engine'])) {
$engine = $dictionary[$this->getObjectName()]['engine'];
}
}
$sql = $this->dbManager->helper->createTableSQLParams($table_name, $fieldDefs, $indices, $engine);
$msg = "Error creating table: " . $table_name . ":";
$this->dbManager->query($sql, true, $msg);
}
function execute_query($query_name = 'query', $result_name = 'result', $row_count_name = 'row_count', $row_start_name = 'row_start', $row_end_name = 'row_end', $limit = false)
{
// FIXME: needs DB-independent code here
if ($limit) {
$start_offset = $this->report_offset;
if (!$this->db->supports('select_rows')) {
if ($start_offset > 0) {
$start_offset++;
}
}
$this->{$result_name} = $this->db->limitQuery($this->{$query_name}, $start_offset, $this->report_max, true, "Error executing query ");
} else {
$this->{$result_name} = $this->db->query($this->{$query_name}, true, "Error executing query ");
}
if (!empty($row_count_name) && empty($this->{$row_count_name})) {
$this->{$row_count_name} = $this->report_offset;
$this->{$row_end_name} = $this->report_max;
if ($limit && $this->total_count < $this->{$row_end_name} + $this->{$row_count_name}) {
$this->{$row_end_name} = $this->total_count - $this->{$row_count_name};
}
if ($this->{$row_count_name} > 0) {
$this->{$row_start_name} = 1;
}
}
}
