function set_relationship($table, $relate_values, $check_duplicates = true, $do_update = false, $data_values = null)
{
$where = '';
// make sure there is a date modified
$date_modified = $this->db->convert("'" . $GLOBALS['timedate']->nowDb() . "'", 'datetime');
$row = null;
if ($check_duplicates) {
$query = "SELECT * FROM {$table} ";
$where = "WHERE deleted = '0' ";
foreach ($relate_values as $name => $value) {
$where .= " AND {$name} = '{$value}' ";
}
$query .= $where;
$result = $this->db->query($query, false, "Looking For Duplicate Relationship:" . $query);
$row = $this->db->fetchByAssoc($result);
}
if (!$check_duplicates || empty($row)) {
unset($relate_values['id']);
if (isset($data_values)) {
$relate_values = array_merge($relate_values, $data_values);
}
$query = "INSERT INTO {$table} (id, " . implode(',', array_keys($relate_values)) . ", date_modified) VALUES ('" . create_guid() . "', " . "'" . implode("', '", $relate_values) . "', " . $date_modified . ")";
$this->db->query($query, false, "Creating Relationship:" . $query);
} else {
if ($do_update) {
$conds = array();
foreach ($data_values as $key => $value) {
array_push($conds, $key . "='" . $this->db->quote($value) . "'");
}
$query = "UPDATE {$table} SET " . implode(',', $conds) . ",date_modified=" . $date_modified . " " . $where;
$this->db->query($query, false, "Updating Relationship:" . $query);
}
}
}
public function vardefProvider()
{
$GLOBALS['log']->info('DBManagerTest.vardefProvider: _db = ' . print_r($this->_db));
$this->setUp();
// Just in case the DB driver is not created yet.
$emptydate = $this->_db->emptyValue("date");
$emptytime = $this->_db->emptyValue("time");
$emptydatetime = $this->_db->emptyValue("datetime");
return array(array("testid", array('id' => array('name' => 'id', 'type' => 'varchar', 'required' => true)), array("id" => "test123"), array("id" => "'test123'")), array("testtext", array('text1' => array('name' => 'text1', 'type' => 'varchar', 'required' => true), 'text2' => array('name' => 'text2', 'type' => 'varchar')), array(), array("text1" => "''"), array()), array("testtext2", array('text1' => array('name' => 'text1', 'type' => 'varchar', 'required' => true), 'text2' => array('name' => 'text2', 'type' => 'varchar')), array('text1' => 'foo', 'text2' => 'bar'), array("text1" => "'foo'", 'text2' => "'bar'")), array("testreq", array('id' => array('name' => 'id', 'type' => 'varchar', 'required' => true), 'intval' => array('name' => 'intval', 'type' => 'int', 'required' => true), 'floatval' => array('name' => 'floatval', 'type' => 'decimal', 'required' => true), 'money' => array('name' => 'money', 'type' => 'currency', 'required' => true), 'test_dtm' => array('name' => 'test_dtm', 'type' => 'datetime', 'required' => true), 'test_dtm2' => array('name' => 'test_dtm2', 'type' => 'datetimecombo', 'required' => true), 'test_dt' => array('name' => 'test_dt', 'type' => 'date', 'required' => true), 'test_tm' => array('name' => 'test_tm', 'type' => 'time', 'required' => true)), array("id" => "test123", 'intval' => 42, 'floatval' => 42.24, 'money' => 56.78, 'test_dtm' => '2002-01-02 12:34:56', 'test_dtm2' => '2011-10-08 01:02:03', 'test_dt' => '1998-10-04', 'test_tm' => '03:04:05'), array("id" => "'test123'", 'intval' => 42, 'floatval' => 42.24, 'money' => 56.78, 'test_dtm' => $this->_db->convert('\'2002-01-02 12:34:56\'', "datetime"), 'test_dtm2' => $this->_db->convert('\'2011-10-08 01:02:03\'', 'datetime'), 'test_dt' => $this->_db->convert('\'1998-10-04\'', 'date'), 'test_tm' => $this->_db->convert('\'03:04:05\'', 'time'))), array("testreqnull", array('id' => array('name' => 'id', 'type' => 'varchar', 'required' => true), 'intval' => array('name' => 'intval', 'type' => 'int', 'required' => true), 'floatval' => array('name' => 'floatval', 'type' => 'decimal', 'required' => true), 'money' => array('name' => 'money', 'type' => 'currency', 'required' => true), 'test_dtm' => array('name' => 'test_dtm', 'type' => 'datetime', 'required' => true), 'test_dtm2' => array('name' => 'test_dtm2', 'type' => 'datetimecombo', 'required' => true), 'test_dt' => array('name' => 'test_dt', 'type' => 'date', 'required' => true), 'test_tm' => array('name' => 'test_tm', 'type' => 'time', 'required' => true)), array(), array("id" => "''", 'intval' => 0, 'floatval' => 0, 'money' => 0, 'test_dtm' => "{$emptydatetime}", 'test_dtm2' => "{$emptydatetime}", 'test_dt' => "{$emptydate}", 'test_tm' => "{$emptytime}"), array()), array("testnull", array('id' => array('name' => 'id', 'type' => 'varchar'), 'intval' => array('name' => 'intval', 'type' => 'int'), 'floatval' => array('name' => 'floatval', 'type' => 'decimal'), 'money' => array('name' => 'money', 'type' => 'currency'), 'test_dtm' => array('name' => 'test_dtm', 'type' => 'datetime'), 'test_dtm2' => array('name' => 'test_dtm2', 'type' => 'datetimecombo'), 'test_dt' => array('name' => 'test_dt', 'type' => 'date'), 'test_tm' => array('name' => 'test_tm', 'type' => 'time')), array("id" => 123), array("id" => "'123'"), array()), array("testempty", array('id' => array('name' => 'id', 'type' => 'varchar'), 'intval' => array('name' => 'intval', 'type' => 'int'), 'floatval' => array('name' => 'floatval', 'type' => 'decimal'), 'money' => array('name' => 'money', 'type' => 'currency'), 'test_dtm' => array('name' => 'test_dtm', 'type' => 'datetime'), 'test_dtm2' => array('name' => 'test_dtm2', 'type' => 'datetimecombo'), 'test_dt' => array('name' => 'test_dt', 'type' => 'date'), 'test_tm' => array('name' => 'test_tm', 'type' => 'time'), 'text_txt' => array('name' => 'test_txt', 'type' => 'varchar')), array("id" => "", 'intval' => '', 'floatval' => '', 'money' => '', 'test_dtm' => '', 'test_dtm2' => '', 'test_dt' => '', 'test_tm' => '', 'text_txt' => null), array("id" => "''", 'intval' => 0, 'floatval' => 0, 'money' => 0, 'test_dtm' => "NULL", 'test_dtm2' => "NULL", 'test_dt' => "NULL", 'test_tm' => 'NULL'), array('intval' => 'NULL', 'floatval' => 'NULL', 'money' => 'NULL', 'test_dtm' => 'NULL', 'test_dtm2' => 'NULL', 'test_dt' => 'NULL', 'test_tm' => 'NULL')));
}
/**
* Remove old jobs that still are marked as running
* @return bool true if no failed job discovered, false if some job were failed
*/
public function cleanup()
{
// fail jobs that are too old
$ret = true;
$date = $this->db->convert($this->db->quoted($GLOBALS['timedate']->getNow()->modify("+{$this->timeout} seconds")->asDb()), 'datetime');
$res = $this->db->query("SELECT id FROM {$this->job_queue_table} WHERE status='" . SchedulersJob::JOB_STATUS_RUNNING . "' AND date_modified <= {$date}");
while ($row = $this->db->fetchByAssoc($res)) {
$this->resolveJob($row["id"], SchedulersJob::JOB_FAILURE, translate('ERR_TIMEOUT', 'SchedulersJobs'));
$ret = false;
}
// TODO: soft-delete old done jobs?
return $ret;
}
/**
* Returns db-formatted 30-days-ago date
*
* @param $days
* @return string
*/
protected function getLastXDays($days)
{
$days = (int) $days;
$timedate = TimeDate::getInstance();
return $this->db->convert($this->db->quoted($timedate->getNow()->modify("-{$days} days")->asDb(false)), 'datetime');
}
protected function wrapIfNull($field)
{
$has_space = strrpos($field, " ");
// Check if the field has space - i.e. it's "table.field alias"
if ($has_space && !stristr("' '", $field)) {
$aggregate_func = strtolower(substr($field, 0, 4));
if ($aggregate_func == 'max(' || $aggregate_func == 'min(' || $aggregate_func == 'avg(' || $aggregate_func == 'sum(') {
return $field;
}
if (strtolower(substr($field, 0, 6)) == 'count(') {
return $field;
}
// This is field name as table.field
$field_name = substr($field, 0, $has_space);
$field_data = explode(".", $field_name);
if (!isset($field_data[1]) || !isset($this->focus->field_name_map[$field_data[1]]['type'])) {
// Not a field or unknown field type - don't touch it
return $field;
}
$db = DBManagerFactory::getInstance();
$field_type = $db->getFieldType($this->focus->field_name_map[$field_data[1]]);
if (!in_array($field_type, array('currency', 'double', 'float', 'decimal', 'int', 'date', 'datetime'))) {
if ($field_type === 'bool') {
$default = '0';
} else {
$default = "''";
}
// add IFNULL to the field and then re-add alias back
return $this->db->convert($field_name, "IFNULL", array($default)) . " " . substr($field, $has_space + 1) . "\n";
}
}
return $field;
}
