/**
* {@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);
}