protected function store() { $fileTable = IcingaConfigFile::$table; $fileKey = IcingaConfigFile::$keyName; $this->db->beginTransaction(); try { $existingQuery = $this->db->select()->from($fileTable, 'checksum')->where('checksum IN (?)', array_map(array($this, 'dbBin'), $this->getFilesChecksums())); $existing = $this->db->fetchCol($existingQuery); foreach ($existing as $key => $val) { if (is_resource($val)) { $existing[$key] = stream_get_contents($val); } } $missing = array_diff($this->getFilesChecksums(), $existing); /** @var IcingaConfigFile $file */ foreach ($this->files as $name => $file) { $checksum = $file->getChecksum(); if (!in_array($checksum, $missing)) { continue; } $this->db->insert($fileTable, array($fileKey => $this->dbBin($checksum), 'content' => $file->getContent())); } $this->db->insert(self::$table, array('duration' => $this->generationTime, 'last_activity_checksum' => $this->dbBin($this->getLastActivityChecksum()), 'checksum' => $this->dbBin($this->getChecksum()))); /** @var IcingaConfigFile $file */ foreach ($this->files as $name => $file) { $this->db->insert('director_generated_config_file', array('config_checksum' => $this->dbBin($this->getChecksum()), 'file_checksum' => $this->dbBin($file->getChecksum()), 'file_path' => $name)); } $this->db->commit(); } catch (Exception $e) { $this->db->rollBack(); throw $e; var_dump($e->getMessage()); } return $this; }
public function loadGroupByRelationValues($fieldname, $condition, $countValues = false) { if ($condition) { $condition = "WHERE " . $condition; } if ($countValues) { if ($this->model->getVariantMode() == OnlineShop_Framework_IProductList::VARIANT_MODE_INCLUDE_PARENT_OBJECT) { $query = "SELECT dest as `value`, count(DISTINCT src_virtualProductId) as `count` FROM " . $this->model->getCurrentTenantConfig()->getRelationTablename() . " a " . "WHERE fieldname = " . $this->quote($fieldname); } else { $query = "SELECT dest as `value`, count(*) as `count` FROM " . $this->model->getCurrentTenantConfig()->getRelationTablename() . " a " . "WHERE fieldname = " . $this->quote($fieldname); } $subquery = "SELECT a.o_id FROM " . $this->model->getCurrentTenantConfig()->getTablename() . " a " . $this->model->getCurrentTenantConfig()->getJoins() . $condition; $query .= " AND src IN (" . $subquery . ") GROUP BY dest"; OnlineShop_Plugin::getSQLLogger()->log("Query: " . $query, Zend_Log::INFO); $result = $this->db->fetchAssoc($query); OnlineShop_Plugin::getSQLLogger()->log("Query done.", Zend_Log::INFO); return $result; } else { $query = "SELECT dest FROM " . $this->model->getCurrentTenantConfig()->getRelationTablename() . " a " . "WHERE fieldname = " . $this->quote($fieldname); $subquery = "SELECT a.o_id FROM " . $this->model->getCurrentTenantConfig()->getTablename() . " a " . $this->model->getCurrentTenantConfig()->getJoins() . $condition; $query .= " AND src IN (" . $subquery . ") GROUP BY dest"; OnlineShop_Plugin::getSQLLogger()->log("Query: " . $query, Zend_Log::INFO); $result = $this->db->fetchCol($query); OnlineShop_Plugin::getSQLLogger()->log("Query done.", Zend_Log::INFO); return $result; } }
protected function _getSqlDropSequence(Zend_Db_Adapter_Abstract $db, $sequenceName) { $seqList = $db->fetchCol('SELECT sequence_name FROM ALL_SEQUENCES'); if (in_array($sequenceName, $seqList)) { return 'DROP SEQUENCE'; } return null; }
/** * remove all relations for application * * @param string $applicationName * * @return void */ public function removeApplication($applicationName) { $tableName = SQL_TABLE_PREFIX . 'relations'; $select = $this->_db->select()->from($tableName)->columns('rel_id')->where($this->_db->quoteIdentifier('own_model') . ' LIKE ?', $applicationName . '_%'); $relation_ids = $this->_db->fetchCol($select); if (is_array($relation_ids) && count($relation_ids) > 0) { $this->_db->delete($tableName, $this->_db->quoteInto($this->_db->quoteIdentifier('rel_id') . ' IN (?)', $relation_ids)); } }
/** * Test the Adapter's fetchCol() method. */ public function testAdapterFetchCol() { $id = $this->getResultSetKey('id'); $table = $this->getIdentifier(self::TABLE_NAME); $result = $this->_db->fetchCol('SELECT * FROM ' . $this->_db->quoteIdentifier($table) . ' WHERE date_created > ? ORDER BY id', array('2006-01-01')); $this->assertEquals(2, count($result)); // count rows $this->assertEquals(1, $result[0]); $this->assertEquals(2, $result[1]); }
/** * @param $oo_id * @param bool $createMissingChildrenRows * @throws \Zend_Db_Adapter_Exception */ public function doUpdate($oo_id, $createMissingChildrenRows = false) { if (empty($this->fields) && empty($this->relations) && !$createMissingChildrenRows) { return; } $this->idTree = []; $fields = implode("`,`", $this->fields); if (!empty($fields)) { $fields = ", `" . $fields . "`"; } $result = $this->db->fetchRow("SELECT " . $this->idField . " AS id" . $fields . " FROM " . $this->storetable . " WHERE " . $this->idField . " = ?", $oo_id); $o = new \stdClass(); $o->id = $result['id']; $o->values = $result; $o->childs = $this->buildTree($result['id'], $fields); if (!empty($this->fields)) { foreach ($this->fields as $fieldname) { foreach ($o->childs as $c) { $this->getIdsToUpdateForValuefields($c, $fieldname); } $this->updateQueryTable($oo_id, $this->fieldIds[$fieldname], $fieldname); } } if (!empty($this->relations)) { foreach ($this->relations as $fieldname => $fields) { foreach ($o->childs as $c) { $this->getIdsToUpdateForRelationfields($c, $fieldname); } if (is_array($fields)) { foreach ($fields as $f) { $this->updateQueryTable($oo_id, $this->fieldIds[$fieldname], $f); } } else { $this->updateQueryTable($oo_id, $this->fieldIds[$fieldname], $fieldname); } } } // check for missing entries which can occur in object bricks and localized fields // this happens especially in the following case: // parent object has no brick, add child to parent, add brick to parent & click save // without this code there will not be an entry in the query table for the child object if ($createMissingChildrenRows) { $idsToUpdate = $this->extractObjectIdsFromTreeChildren($o->childs); if (!empty($idsToUpdate)) { $idsInTable = $this->db->fetchCol("SELECT " . $this->idField . " FROM " . $this->querytable . " WHERE " . $this->idField . " IN (" . implode(",", $idsToUpdate) . ")"); $diff = array_diff($idsToUpdate, $idsInTable); // create entries for children that don't have an entry yet $originalEntry = $this->db->fetchRow("SELECT * FROM " . $this->querytable . " WHERE " . $this->idField . " = ?", $oo_id); foreach ($diff as $id) { $originalEntry[$this->idField] = $id; $this->db->insert($this->querytable, $originalEntry); } } } }
/** * @param mixed $parentId * @param int $parentLevel * @return $this */ protected function _updateChildLevels($parentId, $parentLevel) { $select = $this->_conn->select()->from($this->_table, $this->_idField)->where($this->_parentField . '=?', $parentId); $ids = $this->_conn->fetchCol($select); if (!empty($ids)) { $this->_conn->update($this->_table, [$this->_levelField => $parentLevel + 1], $this->_conn->quoteInto($this->_idField . ' IN (?)', $ids)); foreach ($ids as $id) { $this->_updateChildLevels($id, $parentLevel + 1); } } return $this; }
/** * remove all relations for application * * @param string $applicationName * * @return void */ public function removeApplication($applicationName) { $tableName = SQL_TABLE_PREFIX . 'relations'; $select = $this->_db->select()->from($tableName, array('rel_id'))->where($this->_db->quoteIdentifier('own_model') . ' LIKE ?', $applicationName . '_%')->limit(10000); do { $relation_ids = $this->_db->fetchCol($select); if (is_array($relation_ids) && count($relation_ids) > 0) { $this->_db->delete($tableName, $this->_db->quoteInto($this->_db->quoteIdentifier('rel_id') . ' IN (?)', $relation_ids)); } else { break; } } while (true); }
protected function _update($type) { $countSelect = $this->_read->select()->from($this->_summaryTable, 'summary_id')->where('type_id=?', $type['type_id'])->having("('" . now() . "' - INTERVAL {$type['period']} {$type['period_type']}) <= MAX(add_date)"); $summaryIds = $this->_read->fetchCol($countSelect); $customerSelect = $this->_read->select()->from($this->_customerTable, 'visitor_id')->where("? - INTERVAL {$type['period']} {$type['period_type']} <= login_at", now())->where("logout_at IS NULL OR logout_at <= ? - INTERVAL {$type['period']} {$type['period_type']}", now()); $customers = $this->_read->fetchCol($customerSelect); $customerCount = count($customers); $customers = $customerCount > 0 ? $customers : 0; $customersCondition = $this->_read->quoteInto('visitor_id NOT IN(?)', $customers); $visitorCount = $this->_read->fetchOne("SELECT COUNT(visitor_id) FROM {$this->_visitorTable} WHERE ('" . now() . "' - INTERVAL {$type['period']} {$type['period_type']}) <= first_visit_at OR (NOW() - INTERVAL {$type['period']} {$type['period_type']}) <= last_visit_at AND {$customersCondition}"); if ($customerCount == 0 && $visitorCount == 0) { return; } $data = array('type_id' => $type['type_id'], 'visitor_count' => $visitorCount, 'customer_count' => $customerCount, 'add_date' => now()); if (count($summaryIds) == 0) { $this->_write->insert($this->_summaryTable, $data); } else { $conditionSql = $this->_write->quoteInto('summary_id in (?)', $summaryIds); $this->_write->update($this->_summaryTable, $data, $conditionSql); } }
/** * Returns a list of the tables in the database. * * @return array */ public function listTables() { $sql = "SELECT tabname " . "FROM systables "; return $this->_adapter->fetchCol($sql); }
/** * Fetch the first column of all rows of the result set as an array * * @param DbQuery $query * * @return array */ public function fetchColumn(DbQuery $query) { return $this->dbAdapter->fetchCol($query->getSelectQuery()); }
/** * Recalculates all token dates, timing and results * and outputs text messages. * * Does not reflect changes to tracks or rounds. * * @param int $sourceId A source identifier * @param int $userId Id of the user who takes the action (for logging) * @return \Gems_Task_TaskRunnerBatch A batch to process the synchronization */ public function synchronizeSources($sourceId = null, $userId = null) { $batch_id = 'source_synch' . ($sourceId ? '_' . $sourceId : ''); $batch = $this->loader->getTaskRunnerBatch($batch_id); if (!$batch->isLoaded()) { if ($sourceId) { $sources = array($sourceId); } else { $select = $this->db->select(); $select->from('gems__sources', array('gso_id_source'))->where('gso_active = 1'); $sources = $this->db->fetchCol($select); } foreach ($sources as $source) { $batch->addTask('Tracker_SourceSyncSurveys', $source, $userId); // Reset cache after basic synch $batch->addTask('CleanCache'); // Reset cache after field synch $batch->addTask('AddTask', 'CleanCache'); $batch->addTask('AddTask', 'Tracker\\UpdateSyncDate', $source, $userId); } } return $batch; }
/** * Get a list of suggested tags to a request entered by the user * * @param Zend_Db_Adapter_Abstract $db * @param string $partialTag //request entered by the user * @param int $limit // count records * @return array */ public static function GetTagSuggestions($db, $partialTag, $limit = 0) { $partialTag = trim($partialTag); if (strlen($partialTag) == 0) { return array(); } // Переведем строку к нижнему регистру $partialTag = mb_convert_case($partialTag, MB_CASE_LOWER, "UTF-8"); $select = $db->select(); $select->distinct(); $select->from(array('t' => 'blog_posts_tags'), 'lower(label)')->joinInner(array('p' => 'blog_posts'), 't.post_id = p.id', array())->where('lower(t.label) like lower(?)', $partialTag . '%')->where('p.status = ?', self::STATUS_LIVE)->order('lower(t.label)'); if ($limit > 0) { $select->limit($limit); } return $db->fetchCol($select); }
/** * Get the tables in the database. * * @param string $prefix Optionally, show only tables with this prefix. * @return array */ public function getTableNames() { $prefix = $this->getPrefix(); $sql = "SHOW TABLES " . ($prefix ? "LIKE '{$prefix}%'" : ''); return $this->_dbAdapter->fetchCol($sql); }
/** * Convert the submitted form-data to a filter to be used for retrieving the data to export * * Now only handles the organization ID and consent codes, but can be extended to * include track info or perform some checks * * @param array $data * @return array */ protected function _getFilter($data) { $filter = array(); if (isset($data['ids'])) { $idStrings = $data['ids']; $idArray = preg_split('/[\\s,;]+/', $idStrings, -1, PREG_SPLIT_NO_EMPTY); if ($idArray) { // Make sure output is OK // $idArray = array_map(array($this->db, 'quote'), $idArray); $filter['respondentid'] = $idArray; } } if ($this->project->hasResponseDatabase()) { $this->_getResponseDatabaseFilter($data, $filter); } if (isset($data['tid'])) { $select = $this->db->select(); $select->from('gems__respondent2track', array('gr2t_id_respondent_track'))->where('gr2t_id_track = ?', $data['tid']); if ($trackArray = $this->db->fetchCol($select)) { $filter['resptrackid'] = $trackArray; } } if (isset($data['oid'])) { $filter['organizationid'] = $data['oid']; } else { //Invalid id so when nothing selected... we get nothing // $filter['organizationid'] = '-1'; } // Consent codes $filter['consentcode'] = array_diff((array) $this->util->getConsentTypes(), (array) $this->util->getConsentRejected()); if (isset($data['rounds']) && !empty($data['rounds'])) { $select = $this->loader->getTracker()->getTokenSelect(array('gto_id_token')); // Only get positive receptioncodes $select->andReceptionCodes(array())->onlySucces(); // Apply track filter if (isset($data['tid']) && !empty($data['tid'])) { $select->forWhere('gto_id_track = ?', (int) $data['tid']); } // Apply survey filter if (isset($data['sid']) && !empty($data['sid'])) { $select->forSurveyId((int) $data['sid']); } // Apply organization filter if (isset($data['oid'])) { $select->forWhere('gto_id_organization in (?)', $data['oid']); } // Apply round description filter if ($data['rounds'] == self::NoRound) { $select->forWhere('gto_round_description IS NULL OR gto_round_description = ""'); } else { $select->forWhere('gto_round_description = ?', $data['rounds']); } $tokens = array(); $result = $select->getSelect()->query(); while ($row = $result->fetch(\Zend_Db::FETCH_NUM)) { $tokens[] = $row[0]; } if (empty($tokens)) { // Add invalid filter $filter['organizationid'] = -1; } $filter['token'] = $tokens; } // \Gems_Tracker::$verbose = true; return $filter; }