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; }