/** * {@inheritDoc} */ protected function doSetAll(array $values) { $this->runSQL('DELETE FROM `' . $this->tableName . '`'); $rows = array(); foreach ($values as $key => $value) { $this->runSQL('INSERT INTO ' . $this->tableName . ' (`key`, `value`) VALUES (' . $this->connection->quote($key) . ',' . $this->connection->quote(json_encode($value)) . ');'); } }
public function postUpdate() { $em = $this->container->get('doctrine.orm.entity_manager'); $process = false; if (in_array('claro_forum_subject_temp', $this->conn->getSchemaManager()->listTableNames())) { $columns = $this->conn->getSchemaManager()->listTableColumns('claro_forum_subject_temp'); foreach ($columns as $column) { if ($column->getName() === 'forum_id') { $process = true; break; } } } if ($process) { $this->log('restoring the subjects...'); $forums = $em->getRepository('ClarolineForumBundle:Forum')->findAll(); $sql = 'SELECT * FROM claro_forum_subject_temp WHERE forum_id = :forumId'; $stmt = $this->conn->prepare($sql); foreach ($forums as $forum) { $category = new Category(); $category->setName($forum->getResourceNode()->getName()); $category->setForum($forum); $em->persist($category); $em->flush(); $stmt->bindValue('forumId', $forum->getId()); $stmt->execute(); foreach ($stmt->fetchAll() as $rowsSubject) { $this->conn->query("INSERT INTO claro_forum_subject VALUES (\n {$rowsSubject['id']},\n {$category->getId()},\n {$rowsSubject['user_id']},\n {$this->conn->quote($rowsSubject['title'])},\n '{$rowsSubject['created']}',\n '{$rowsSubject['updated']}',\n false\n )"); } } $this->log('restoring the messages...'); $this->conn->query('INSERT IGNORE INTO claro_forum_message SELECT * FROM claro_forum_message_temp'); $this->conn->query('DROP TABLE claro_forum_message_temp'); $this->conn->query('DROP TABLE claro_forum_subject_temp'); $this->conn->query('DROP TABLE claro_forum_options'); } else { $this->log('categories already added'); } $widget = $em->getRepository('ClarolineCoreBundle:Widget\\Widget')->findBy(array('name' => 'claroline_forum_widget')); if (!$widget) { $this->log('adding the forum widget...'); $plugin = $em->getRepository('ClarolineCoreBundle:Plugin')->findOneBy(array('vendorName' => 'Claroline', 'bundleName' => 'ForumBundle')); $widget = new Widget(); $widget->setName('claroline_forum_widget'); $widget->setDisplayableInDesktop(true); $widget->setDisplayableInWorkspace(true); $widget->setConfigurable(false); $widget->setExportable(false); $widget->setIcon('none'); $widget->setPlugin($plugin); $em->persist($widget); $plugin->setHasOptions(true); $em->persist($widget); $em->flush(); } else { $this->log('forum widget already added'); } }
public function generateSql($routeName, $type, array $state, array $title, array $description, array $alias, array $path, $parentRouteName = null, $template = null) { $sql = []; $currentDate = (new \DateTime())->format('y-m-d H:i:s'); $sql[] = sprintf("SELECT @parentId:=id FROM `%s` WHERE `route_name` = '%s' LIMIT 1;", self::STRUCTURE_TABLE_NAME, $this->connection->quote($parentRouteName)); $sql[] = sprintf("INSERT INTO `%s`\n (\n `parent_id`,\n `created_at`,\n `updated_at`,\n `title`,\n `description`,\n `type`,\n `alias`,\n `path`,\n `state`,\n `template`,\n `route_name`\n )\n VALUES (\n @parentId,\n '%s',\n '%s',\n '%s',\n '%s',\n '%s',\n '%s',\n '%s',\n '%d',\n '%s',\n '%s'\n )\n ON DUPLICATE KEY UPDATE\n `parent_id`=VALUES(`parent_id`),\n `updated_at`=VALUES(`updated_at`),\n `title`=VALUES(`title`),\n `description`=VALUES(`description`),\n `type`=VALUES(`type`),\n `alias`=VALUES(`alias`),\n `path`=VALUES(`path`),\n `state`=VALUES(`state`),\n `template`=VALUES(`template`)\n ;", self::STRUCTURE_TABLE_NAME, $currentDate, $currentDate, $this->connection->quote($title[$this->defaultLocale]), $this->connection->quote($description[$this->defaultLocale]), $type, $this->connection->quote($alias[$this->defaultLocale]), $this->connection->quote($path[$this->defaultLocale]), (bool) $state[$this->defaultLocale] ? 1 : 0, $this->connection->quote($template), $this->connection->quote($routeName)); $sql[] = sprintf("SELECT @structureId := id FROM `%s` WHERE route_name = '%s';", self::STRUCTURE_TABLE_NAME, $this->connection->quote($routeName)); return implode("\n", $sql); }
/** * Changes a value to a specific type. * * The original implementation of this method * had some vestiges of support for type options * (like a regular expression pattern for the regexp * type) . This seems to have long been unsupported * so now all bits of the type after <code>:</code> * are squashed. * * @param mixed $value value to bind * @param string $type type of value * @return mixed modified value */ public function getBindVarValue($value, $type) { if (false !== strpos($type, ':')) { $type = strstr($type, ':', true); } switch ($type) { case 'csv': case 'passthru': return $value; break; case 'floatval': return is_numeric($value) ? $value : 0; break; case 'integer': return (int) $value; break; case 'currency': case 'date': case 'string': return $this->conn->quote($value); break; case 'noquotestring': return $this->prepareInput($value); case 'regexp': return $this->prepareInput(preg_quote($value)); } throw new RuntimeException(sprintf('Type %s does not exist', $type)); }
/** * Returns the SQL to check if a value is one in a set of * given values.. * * in() accepts an arbitrary number of parameters. The first parameter * must always specify the value that should be matched against. Successive * parameters must contain a logical expression or an array with logical * expressions. These expressions will be matched against the first * parameter. * * Example: * <code> * $q->select( '*' )->from( 'table' ) * ->where( $q->expr->in( 'id', 1, 2, 3 ) ); * </code> * * @throws \eZ\Publish\Core\Persistence\Database\QueryException if called with less than two * parameters. * @throws \eZ\Publish\Core\Persistence\Database\QueryException if the 2nd parameter is an * empty array. * * @param string $column the value that should be matched against * @param string|array(string) $... values that will be matched against $column * * @return string logical expression */ public function in($column) { $args = func_get_args(); if (count($args) < 2) { throw new QueryException('Expected two or more parameters to in()'); } if (is_array($args[1])) { $values = array_values($args[1]); } else { $values = array_slice($args, 1); } // Special handling of sub selects to avoid double braces if (count($values) === 1 && $values[0] instanceof SubselectDoctrineQuery) { return "{$column} IN " . $values[0]->getQuery(); } if (count($values) == 0) { throw new QueryException('At least one element is required as value.'); } foreach ($values as $key => $value) { switch (true) { case $value instanceof SubselectDoctrineQuery: $values[$key] = $value->getQuery(); break; case is_int($value): case is_float($value): $values[$key] = (string) $value; break; default: $values[$key] = $this->connection->quote($value); } } return "{$column} IN ( " . implode(', ', $values) . ' )'; }
/** * Generates a single query builder from the provided keywords array. * * @param Keyword[] $keywords * @param $tables * @return QueryBuilder */ private function buildQueryFromKeywords($keywords, $tables) { $keywordSelection = []; foreach ($keywords as $match) { $keywordSelection[] = 'SELECT ' . $match->getRelevance() . ' as relevance, ' . $this->connection->quote($match->getTerm()) . ' as term, ' . $match->getId() . ' as keywordID'; } $keywordSelection = implode("\n UNION ALL ", $keywordSelection); $tablesSql = []; foreach ($tables as $table) { $query = $this->connection->createQueryBuilder(); $alias = 'st' . $table['tableID']; $query->select(['MAX(sf.relevance * sm.relevance) as relevance', 'sm.keywordID']); $query->from('(' . $keywordSelection . ')', 'sm'); $query->innerJoin('sm', 's_search_index', 'si', 'sm.keywordID = si.keywordID'); $query->innerJoin('si', 's_search_fields', 'sf', 'si.fieldID = sf.id AND sf.relevance != 0 AND sf.tableID = ' . $table['tableID']); $query->groupBy('articleID')->addGroupBy('sm.term')->addGroupBy('sf.id'); if (!empty($table['referenz_table'])) { $query->addSelect($alias . '.articleID as articleID'); $query->innerJoin('si', $table['referenz_table'], $alias, 'si.elementID = ' . $alias . '.' . $table['foreign_key']); } elseif (!empty($table['foreign_key'])) { $query->addSelect($alias . '.id as articleID'); $query->innerJoin('si', 's_articles', $alias, 'si.elementID = ' . $alias . '.' . $table['foreign_key']); } else { $query->addSelect('si.elementID as articleID'); } $tablesSql[] = $query->getSQL(); } $tablesSql = "\n" . implode("\n UNION ALL\n", $tablesSql); $subQuery = $this->connection->createQueryBuilder(); $subQuery->select(['srd.articleID', 'SUM(srd.relevance) as relevance']); $subQuery->from("(" . $tablesSql . ')', 'srd')->groupBy('srd.articleID')->setMaxResults(5000); $query = $this->connection->createQueryBuilder(); $query->from("(" . $subQuery->getSQL() . ')', 'sr')->innerJoin('sr', 's_articles', 'a', 'a.id = sr.articleID'); return $query; }
/** * Revert data to the previous version. */ public function sqlDownData() { // Map columns to custom fields. $colmap = array(); foreach ($this->fieldmap as $field => $col) { $colmap[$col] = $field; } // Test that the v2 columns actually exist. $existingCols = $this->conn->getSchemaManager()->listTableColumns($this->usersTable); $existingColnames = array_map(function ($col) { return $col->getName(); }, $existingCols); foreach ($this->fieldmap as $col) { if (!in_array($col, $existingColnames)) { throw new \RuntimeException('Cannot migrate down because current schema is not v2. (Missing column "' . $this->usersTable . '.' . $col . '").'); } } // Get user columns to revert back to custom fields. $userData = $this->conn->fetchAll('SELECT id AS user_id, ' . implode(', ', $this->fieldmap) . ' FROM ' . $this->conn->quoteIdentifier($this->usersTable)); $queries = array(); foreach ($userData as $row) { foreach ($this->fieldmap as $col) { if ($row[$col] !== null) { $queries[] = 'INSERT INTO ' . $this->conn->quoteIdentifier($this->userCustomFieldsTable) . ' (user_id, attribute, value) VALUES' . ' (' . $this->conn->quote($row['user_id'], Type::INTEGER) . ', ' . $this->conn->quote($colmap[$col], Type::STRING) . ', ' . $this->conn->quote($row[$col], Type::STRING) . ')'; } } } return $queries; }
/** * @param mixed $value * @param Type $type * * @return string */ protected function quoteValue($value, Type $type) { // Don't quote numbers as some don't follow standards for casting if (is_scalar($value) && ctype_digit((string) $value)) { return (string) $value; } return $this->connection->quote($value, $type->getBindingType()); }
public function getRawSql() { $sql = $this->getSql(); foreach ($this->qb->getParameters() as $key => $value) { $sql = str_replace(':' . $key, $this->connection->quote($value), $sql); } return $sql; }
/** * Update object * * @param StorableInterface $obj * @param string $type * * @return void */ protected function update(StorableInterface $obj, $type) { $qb = $this->con->createQueryBuilder()->update($this->tables[$type])->where('id = :id')->setParameter('id', $obj->getId(), \PDO::PARAM_INT); foreach ($obj->getStorableData() as $name => $value) { $qb->set($name, $this->con->quote($value)); } return $qb->execute(); }
public function lookupRange(&$escapedField, &$rawValue, &$negate) { if (!is_array($rawValue) || count($rawValue) !== 2) { throw new \InvalidArgumentException("value for RANGE lookup must me array with two elements"); } $rawValue = Expr($this->db->quote($rawValue[0]) . ' AND ' . $this->db->quote($rawValue[1])); return 'BETWEEN %s'; }
/** * Creates a left join list for translations * on used query components * * @todo: make it cleaner * @return string */ private function prepareTranslatedComponents() { $q = $this->getQuery(); $locale = $q->getHint(TranslatableListener::HINT_TRANSLATABLE_LOCALE); if (!$locale) { // use from listener $locale = $this->listener->getListenerLocale(); } $defaultLocale = $this->listener->getDefaultLocale(); if ($locale === $defaultLocale && !$this->listener->getPersistDefaultLocaleTranslation()) { // Skip preparation as there's no need to translate anything return; } $em = $this->getEntityManager(); $ea = new TranslatableEventAdapter(); $ea->setEntityManager($em); $joinStrategy = $q->getHint(TranslatableListener::HINT_INNER_JOIN) ? 'INNER' : 'LEFT'; foreach ($this->translatedComponents as $dqlAlias => $comp) { $meta = $comp['metadata']; $config = $this->listener->getConfiguration($em, $meta->name); $transClass = $this->listener->getTranslationClass($ea, $meta->name); $transMeta = $em->getClassMetadata($transClass); $transTable = $transMeta->getQuotedTableName($this->platform); foreach ($config['fields'] as $field) { $compTblAlias = $this->walkIdentificationVariable($dqlAlias, $field); $tblAlias = $this->getSQLTableAlias('trans' . $compTblAlias . $field); $sql = " {$joinStrategy} JOIN " . $transTable . ' ' . $tblAlias; $sql .= ' ON ' . $tblAlias . '.' . $transMeta->getQuotedColumnName('locale', $this->platform) . ' = ' . $this->conn->quote($locale); $sql .= ' AND ' . $tblAlias . '.' . $transMeta->getQuotedColumnName('field', $this->platform) . ' = ' . $this->conn->quote($field); $identifier = $meta->getSingleIdentifierFieldName(); $idColName = $meta->getQuotedColumnName($identifier, $this->platform); if ($ea->usesPersonalTranslation($transClass)) { $sql .= ' AND ' . $tblAlias . '.' . $transMeta->getSingleAssociationJoinColumnName('object') . ' = ' . $compTblAlias . '.' . $idColName; } else { $sql .= ' AND ' . $tblAlias . '.' . $transMeta->getQuotedColumnName('objectClass', $this->platform) . ' = ' . $this->conn->quote($config['useObjectClass']); $mappingFK = $transMeta->getFieldMapping('foreignKey'); $mappingPK = $meta->getFieldMapping($identifier); $fkColName = $this->getCastedForeignKey($compTblAlias . '.' . $idColName, $mappingFK['type'], $mappingPK['type']); $sql .= ' AND ' . $tblAlias . '.' . $transMeta->getQuotedColumnName('foreignKey', $this->platform) . ' = ' . $fkColName; } isset($this->components[$dqlAlias]) ? $this->components[$dqlAlias] .= $sql : ($this->components[$dqlAlias] = $sql); $originalField = $compTblAlias . '.' . $meta->getQuotedColumnName($field, $this->platform); $substituteField = $tblAlias . '.' . $transMeta->getQuotedColumnName('content', $this->platform); // Treat translation as original field type $fieldMapping = $meta->getFieldMapping($field); if ($this->platform instanceof MySqlPlatform && in_array($fieldMapping["type"], array("decimal")) || !$this->platform instanceof MySqlPlatform && !in_array($fieldMapping["type"], array("datetime", "datetimetz", "date", "time"))) { $type = Type::getType($fieldMapping["type"]); $substituteField = 'CAST(' . $substituteField . ' AS ' . $type->getSQLDeclaration($fieldMapping, $this->platform) . ')'; } // Fallback to original if was asked for if ($this->needsFallback() && (!isset($config['fallback'][$field]) || $config['fallback'][$field]) || !$this->needsFallback() && isset($config['fallback'][$field]) && $config['fallback'][$field]) { $substituteField = 'COALESCE(' . $substituteField . ', ' . $originalField . ')'; } $this->replacements[$originalField] = $substituteField; } } }
/** * Constructs the SQL for retrieving the primary key of the given object * identity. * * @param ObjectIdentityInterface $oid * * @return string */ protected function getSelectObjectIdentityIdSql(ObjectIdentityInterface $oid) { $query = <<<QUERY SELECT o.id FROM %s o INNER JOIN %s c ON c.id = o.class_id WHERE o.object_identifier = %s AND c.class_type = %s QUERY; return sprintf($query, $this->options['oid_table_name'], $this->options['class_table_name'], $this->connection->quote((string) $oid->getIdentifier()), $this->connection->quote((string) $oid->getType())); }
/** * {@inheritdoc} */ public function startQuery($sql, array $params = null, array $types = null) { if ($params) { list($sql, $params, $types) = SQLParserUtils::expandListParameters($sql, $params, $types); $query = vsprintf(str_replace('?', "%s", $sql), call_user_func(function () use($params, $types) { $quotedParams = array(); foreach ($params as $typeIndex => $value) { $quotedParams[] = $this->connection->quote($value, $types[$typeIndex]); } return $quotedParams; })); } else { $query = $sql; } $this->lastQuery = $query; if ($this->outputQuery) { $this->output($query); } }
/** * {@inheritdoc} */ public function quote($value) { if (ConnectionDecoratorChain::isDecorate()) { return (new ConnectionDecoratorChain($this, $this->decorators))->quote($value); } try { return $this->conn->quote($value); } catch (\Exception $e) { throw $this->convertException($e); } }
/** * Makes sure database is created and ready to use. * * @param Fiddle $fiddle * @return string */ protected function prepareDatabase(Fiddle $fiddle) { $directory = $this->getDirectory($fiddle); $fiddleEscaped = $this->db->quote($fiddle->getId(), \PDO::PARAM_STR); $fiddleId = $this->db->quoteIdentifier($fiddle->getId()); if (!$this->debug && file_exists($directory . '/home/sandbox/propel.yml')) { return false; } $dbName = 'fiddle_' . $fiddle->getId(); $userName = $fiddle->getId(); $password = substr(md5(microtime() * 10000 + mt_rand()), 0, 14); $dbNameIdentifier = $this->db->quoteIdentifier($dbName); //create new db credentials $row = $this->db->fetchArray("SHOW DATABASES LIKE ?", [$dbName]); if (false === $row) { // database does not exist yet $this->db->executeQuery(sprintf("CREATE DATABASE %s", $dbNameIdentifier)); } $user = $this->db->fetchArray("SELECT User FROM mysql.user WHERE User = ?", [$fiddle->getId()]); // we don't save the password in our database so we have to remove the user first if ($user) { $this->db->executeQuery(sprintf("DROP USER %s@'localhost'", $fiddleEscaped)); } $this->db->executeQuery(sprintf("CREATE USER %s@'localhost' IDENTIFIED BY %s", $fiddleEscaped, $this->db->quote($password, \PDO::PARAM_STR))); $this->needConfigConvertCall = true; $this->db->executeQuery(sprintf("GRANT USAGE, ALTER, CREATE, DELETE, DROP, INDEX, INSERT, SELECT, UPDATE ON %s.* TO %s@'localhost'", $dbNameIdentifier, $fiddleEscaped)); $propelConfig = <<<EOF propel: database: connections: default: adapter: mysql classname: Propel\\Runtime\\Connection\\DebugPDO dsn: mysql:host=127.0.0.1;dbname={$dbName} user: {$userName} password: {$password} attributes: runtime: log: defaultLogger: type: stream path: ./propel_log.txt level: 100 defaultConnection: default connections: - default generator: defaultConnection: default connections: - default EOF; file_put_contents($directory . '/home/sandbox/propel.yml', $propelConfig); return $propelConfig; }
/** * Escapes a string to insert into the database as text * @param string The string to escape * @return string The escaped string */ public static function escape_string($string) { /* The pdo::quote function adds a "'" character we need to remove that ' because in Chamilo, developers builds a query like this: $sql = "SELECT * FROM $table WHERE id = 'Database::escape_string($id)'"; otherwise we will have an error because the query will be: SELECT * FROM user WHERE id = ''1'' instead of SELECT * FROM user WHERE id = '1' */ // $string = '_@_'.self::$db->quote($string).'_@_'; $string = self::$db->quote($string); return trim($string, "'"); return $string; }
/** * Selects data from the database not managed by Phabric. * Used to select a copy of the data before update in order to allow * roll back. * * @param string $tableName Name of table to query * @param string $phName Name of the Phabric entity * @param array $data Data from the Gherkin * * @return array */ protected function selectPreloadedData($tableName, $phName, $data) { $builder = $this->connection->createQueryBuilder(); $nValue = $this->connection->quote($data[$phName]); $builder->select('*')->from($tableName, 'a')->where("a.`{$phName}` = {$nValue}"); $sql = $builder->getSQL(); $result = $builder->execute(); $initalData = $result->fetchAll(\PDO::FETCH_ASSOC); if (count($initalData) > 1) { throw new \RuntimeException(' More than one row returned when trying to manage unmanaged (preloaded) data. Value in the name column (set in config) must be unique.'); } return reset($initalData); }
/** * Returns the current database structure. * * @return array An array of tables and fields */ private function getFromDb() { $tables = $this->connection->fetchAll("SHOW TABLE STATUS LIKE 'tl_%'"); if (empty($tables)) { return []; } $return = []; foreach ($tables as $table) { $sql = $this->connection->getDatabasePlatform()->getListTableColumnsSQL($table['Name'], $this->connection->getDatabase()); $columns = $this->connection->fetchAll($sql); foreach ($columns as $column) { $field = ['name' => $this->quote($column['Field']), 'type' => $column['Type']]; if (isset($column['Collation']) && $column['Collation'] !== $table['Collation']) { $field['collation'] = 'COLLATE ' . $column['Collation']; } $field['null'] = 'YES' === $column['Null'] ? 'NULL' : 'NOT NULL'; if (!empty($column['Extra'])) { $field['extra'] = $column['Extra']; } if (isset($column['Default'])) { $field['default'] = 'default ' . $this->connection->quote($column['Default']); } $return[$table['Name']]['TABLE_FIELDS'][$column['Field']] = trim(implode(' ', $field)); } $sql = $this->connection->getDatabasePlatform()->getListTableIndexesSQL($table['Name'], $this->connection->getDatabase()); $tmp = []; $indexes = $this->connection->fetchAll($sql); foreach ($indexes as $index) { $name = $index['Key_name']; if (isset($tmp[$name])) { $tmp[$name]['columns'][] = $this->quoteColumn($index); continue; } if ('PRIMARY' === $name) { $tmp[$name]['key'] = 'PRIMARY KEY '; } elseif ('0' === $index['Non_Unique']) { $tmp[$name]['key'] = 'UNIQUE KEY ' . $this->quote($name); } else { $tmp[$name]['key'] = 'KEY ' . $this->quote($name); } $tmp[$name]['columns'] = [$this->quoteColumn($index)]; } foreach ($tmp as $name => $conf) { $return[$table['Name']]['TABLE_CREATE_DEFINITIONS'][$name] = $conf['key'] . ' (' . implode(', ', $conf['columns']) . ')'; } } return $return; }
private function getQuery($identifier, $mask, $type) { $sql = "SELECT\n o.object_identifier\n , c.class_type\n FROM {$this->options['sid_table_name']} s\n LEFT JOIN {$this->options['entry_table_name']} e\n ON (\n (e.security_identity_id = s.id)\n OR {$this->connection->getDatabasePlatform()->getIsNullExpression('e.security_identity_id')}\n )\n LEFT JOIN {$this->options['oid_table_name']} o\n ON (o.id = e.object_identity_id)\n LEFT JOIN {$this->options['class_table_name']} c\n ON (c.id = o.class_id)"; if (is_array($identifier)) { $connection = $this->connection; $identifiers = array_map(function ($elem) use($connection) { return $connection->quote($elem); }, $identifier); $sql .= 'WHERE s.identifier IN (' . implode(', ', $identifiers) . ')'; } else { $sql .= ' WHERE s.identifier = ' . $this->connection->quote($identifier); } $sql .= ' AND e.mask >= ' . $mask; if ($type) { $sql .= ' AND c.class_type = ' . $this->connection->quote($type); } return $sql; }
/** * Find a row by fields value * * @param array $fields * @param int $limit Limit results, 1 by default * * @return array */ public function findByFields($fields, $limit = 1) { $where = " WHERE "; foreach ($fields as $field) { if (!empty($field['name']) && isset($field['value'])) { $operator = empty($field['operator']) ? '=' : $field['name']; $where .= $field['name'] . $operator . $this->db->quote($field['value']) . ' AND '; } } if (substr($where, strlen($where) - 5, 5) == ' AND ') { $where = substr($where, 0, strlen($where) - 4); } if (is_int($limit) && $limit >= 1) { $limit = ' LIMIT ' . $limit; } else { $limit = ''; } return $this->db->fetchAssoc(sprintf('SELECT * FROM %s' . ($where !== " WHERE " ? $where : '') . $limit, $this->getTableName())); }
protected function getAncestorLookupSql(array $batch) { $sql = <<<SELECTCLAUSE SELECT a.ancestor_id FROM {$this->options['oid_table_name']} o INNER JOIN {$this->options['class_table_name']} c ON c.id = o.class_id INNER JOIN {$this->options['oid_ancestors_table_name']} a ON a.object_identity_id = o.id WHERE ( SELECTCLAUSE; $types = array(); $count = count($batch); for ($i = 0; $i < $count; $i++) { if (!isset($types[$batch[$i]->getType()])) { $types[$batch[$i]->getType()] = true; // if there is more than one type we can safely break out of the // loop, because it is the differentiator factor on whether to // query for only one or more class types if (count($types) > 1) { break; } } } if (1 === count($types)) { $ids = array(); for ($i = 0; $i < $count; $i++) { $identifier = (string) $batch[$i]->getIdentifier(); $ids[] = $this->connection->quote($identifier); } $sql .= sprintf('(o.object_identifier IN (%s) AND c.class_type = %s)', implode(',', $ids), $this->connection->quote($batch[0]->getType())); } else { $where = '(o.object_identifier = %s AND c.class_type = %s)'; for ($i = 0; $i < $count; $i++) { $sql .= sprintf($where, $this->connection->quote($batch[$i]->getIdentifier()), $this->connection->quote($batch[$i]->getType())); if ($i + 1 < $count) { $sql .= ' OR '; } } } $sql .= ')'; return $sql; }
/** * @param QOM\OperandInterface $operand * * @return string * * @throws InvalidQueryException */ public function walkOperand(QOM\OperandInterface $operand) { if ($operand instanceof QOM\NodeNameInterface) { $selectorName = $operand->getSelectorName(); $alias = $this->getTableAlias($selectorName); return $this->platform->getConcatExpression("{$alias}.namespace", "(CASE {$alias}.namespace WHEN '' THEN '' ELSE ':' END)", "{$alias}.local_name"); } if ($operand instanceof QOM\NodeLocalNameInterface) { $selectorName = $operand->getSelectorName(); $alias = $this->getTableAlias($selectorName); return "{$alias}.local_name"; } if ($operand instanceof QOM\LowerCaseInterface) { return $this->platform->getLowerExpression($this->walkOperand($operand->getOperand())); } if ($operand instanceof QOM\UpperCaseInterface) { return $this->platform->getUpperExpression($this->walkOperand($operand->getOperand())); } if ($operand instanceof QOM\LiteralInterface) { return $this->conn->quote($this->getLiteralValue($operand)); } if ($operand instanceof QOM\PropertyValueInterface) { $alias = $this->getTableAlias($operand->getSelectorName() . '.' . $operand->getPropertyName()); $property = $operand->getPropertyName(); if ($property == "jcr:path") { return $alias . ".path"; } if ($property == "jcr:uuid") { return $alias . ".identifier"; } return $this->sqlXpathExtractValue($alias, $property); } if ($operand instanceof QOM\LengthInterface) { $alias = $this->getTableAlias($operand->getPropertyValue()->getSelectorName()); $property = $operand->getPropertyValue()->getPropertyName(); return $this->sqlXpathExtractValueAttribute($alias, $property, 'length'); } throw new InvalidQueryException("Dynamic operand " . get_class($operand) . " not yet supported."); }
/** * Escape the values and serialize objects and arrays * * @param array $arrValues The values array * * @return array The array with the escaped values */ protected function escapeParams($arrValues) { foreach ($arrValues as $k => $v) { switch (gettype($v)) { case 'string': $arrValues[$k] = $this->resConnection->quote($v); break; case 'boolean': $arrValues[$k] = $v === true ? 1 : 0; break; case 'object': $arrValues[$k] = $this->resConnection->quote(serialize($v)); break; case 'array': $arrValues[$k] = $this->resConnection->quote(serialize($v)); break; default: $arrValues[$k] = $v === null ? 'NULL' : $v; break; } } return $arrValues; }
public function pumpToDb(Connection $db, Parser $parser) { $tableName = strtolower(trim($parser->getHeader('tbl'))); $attrDefs = $parser->getAttributeDefs(); $recordsPumped = 0; $sql = "INSERT INTO " . strtolower($tableName) . " (\n"; $isFirst = true; foreach ($attrDefs as $attrName => $attrDef) { if ($isFirst) { $isFirst = false; } else { $sql .= ",\n"; } $sql .= " " . strtolower(trim($attrName)); } $sql .= "\n) VALUES (\n%s\n);\n"; foreach ($parser as $record) { $values = array(); foreach ($attrDefs as $attrName => $attrDef) { if (trim($record[$attrName]) === '') { $values[] = 'NULL'; } else { if ($attrDef['type'] == 'char') { $values[] = $db->quote(iconv($this->sourceCharset, $this->targetCharset, trim($record[$attrName]))); } else { $values[] = trim($record[$attrName]); } } } $valuesStr = " " . implode(",\n ", $values); $recordsPumped++; $insertSql = sprintf($sql, $valuesStr); // echo $insertSql; $db->exec($insertSql); } return $recordsPumped; }
/** * {@inheritdoc} */ public function walkStringPrimary($stringPrimary) { return is_string($stringPrimary) ? $this->conn->quote($stringPrimary) : $stringPrimary->dispatch($this); }
/** * Return the next autoincrement ID of a table * * @param string $strTable The table name * * @return integer The autoincrement ID */ public function getNextId($strTable) { $statement = $this->resConnection->executeQuery('SHOW TABLE STATUS LIKE ' . $this->resConnection->quote($strTable)); $status = $statement->fetch(\PDO::FETCH_ASSOC); return $status['Auto_increment']; }
/** * Convenience access to PDO::quote. * * @author Jerome Bogaerts, <*****@*****.**> * @param string $parameter The parameter to quote. * @param int $parameter_type A PDO PARAM_XX constant. * @return string The quoted string. */ public function quote($parameter, $parameter_type = PDO::PARAM_STR) { return $this->connection->quote($parameter, $parameter_type); }
/** * Rebuilds the search index for the shopware default search query builder. */ public function build() { @ini_set("memory_limit", "512M"); @set_time_limit(0); $this->setNextUpdateTimestamp(); // Truncate search index table $this->connection->executeUpdate('TRUNCATE TABLE `s_search_index`'); // Get a list of all tables and columns in this tables that should be processed by search /** * Example return: * tableID | table | where | referenz_table | fieldIDs | fields | foreign_key * 1 | s_articles | NULL | NULL | 3,4 | name, keywords | NULL * 2 | s_categories | NULL | s_articles_categories | 1,2 | metakeywords, description | categoryID */ $tables = $this->getSearchTables(); if (!empty($tables)) { foreach ($tables as $table) { // Set primary key $table['elementID'] = empty($table['foreign_key']) && $table['table'] != 's_articles' ? 'articleID' : 'id'; // Build sql query to fetch values from this table $sql = 'SELECT ' . $table['elementID'] . ' as id, ' . $table['fields'] . ' FROM ' . $table['table']; // If any where condition is set, add to query if (!empty($table['where'])) { $sql .= ' WHERE ' . $table['where']; } // Get all fields & values from current table $getTableKeywords = $this->connection->fetchAll($sql); // If no result, return if (empty($getTableKeywords)) { continue; } // Build array from columns fieldIDs and fields $fields = array_combine(explode(', ', $table["fieldIDs"]), explode(', ', $table["fields"])); $keywords = []; $sqlIndex = []; // Go through every row of result foreach ($getTableKeywords as $currentRow => $row) { // Go through every column of result foreach ($fields as $fieldID => $field) { // Split string from column into keywords $field_keywords = $this->termHelper->splitTerm($row[$field]); if (empty($field_keywords)) { continue; } foreach ($field_keywords as &$keyword) { $keyword = $this->connection->quote($keyword); $keywords[] = $keyword; } // SQL-queries to fill s_search_index $sqlIndex[] = 'SELECT sk.id as keywordID, ' . $row['id'] . ' as elementID, ' . $fieldID . ' as fieldID ' . 'FROM s_search_keywords sk ' . 'WHERE sk.keyword IN (' . implode(', ', $field_keywords) . ')'; } // If no new keywords were found, proceed with next table if (empty($keywords)) { continue; } // If last row or more then 5000 keywords fetched, write results to index if ($currentRow == count($getTableKeywords) - 1 || count($keywords) > 5000) { $keywords = array_unique($keywords); // Remove duplicates $sql_keywords = 'INSERT IGNORE INTO `s_search_keywords` (`keyword`) VALUES'; $sql_keywords .= ' (' . implode('), (', $keywords) . ')'; // Insert Keywords $this->connection->executeUpdate($sql_keywords); $keywords = []; // Update index $sqlIndex = implode("\n\nUNION ALL\n\n", $sqlIndex); $sqlIndex = "INSERT IGNORE INTO s_search_index (keywordID, elementID, fieldID)\n\n" . $sqlIndex; $this->connection->executeUpdate($sqlIndex); $sqlIndex = []; } } } } $this->cleanupIndex(); $this->cleanupKeywords(); }
/** * Splits Federation at a given distribution value. * * @param mixed $splitDistributionValue * * @return void */ public function splitFederation($splitDistributionValue) { $type = Type::getType($this->distributionType); $sql = "ALTER FEDERATION " . $this->getFederationName() . " " . "SPLIT AT (" . $this->getDistributionKey() . " = " . $this->conn->quote($splitDistributionValue, $type->getBindingType()) . ")"; $this->conn->exec($sql); }