Delimiting style depends on the underlying database platform that is being used.
NOTE: Just because you CAN use quoted identifiers does not mean
you SHOULD use them. In general, they end up causing way more
problems than they solve.
| public quoteIdentifier ( string $str ) : string | ||
| $str | string | The name to be quoted. |
| return | string | The quoted name. |
/**
* @param $data
* @param $entity
* @param $primaryId
* @return QueryBuilder
*/
public function getQueryBuilderForEntity($data, $entity, $primaryId)
{
$metaData = $this->modelManager->getClassMetadata($entity);
$table = $metaData->table['name'];
$builder = $this->getQueryBuilder();
if ($primaryId) {
$id = $builder->createNamedParameter($primaryId, \PDO::PARAM_INT);
$builder->update($table);
//update article id in case we don't have any field for update
$builder->set('id', $id);
$builder->where('id = ' . $id);
} else {
$builder->insert($table);
}
foreach ($data as $field => $value) {
if (!array_key_exists($field, $metaData->fieldMappings)) {
continue;
}
$key = $this->connection->quoteIdentifier($metaData->fieldMappings[$field]['columnName']);
$value = $this->getNamedParameter($value, $field, $metaData, $builder);
if ($primaryId) {
$builder->set($key, $value);
} else {
$builder->setValue($key, $value);
}
}
return $builder;
}
/**
* @return array
*/
private function getQuotedFields()
{
$fields = [];
foreach (self::$columnNames as $field) {
$fields[] = $this->connection->quoteIdentifier($field);
}
return $fields;
}
public function __construct(Connection $conn, StorageKeyGeneratorInterface $storageKeyGenerator)
{
$this->conn = $conn;
$this->storageKeyGenerator = $storageKeyGenerator;
$this->schemaManager = $this->conn->getSchemaManager();
$this->keyColumn = $this->conn->quoteIdentifier(self::KEY_COLUMN);
$this->valueColumn = $this->conn->quoteIdentifier(self::VALUE_COLUMN);
}
/**
* Fetch all
*
* @return array
*/
public function fetchAll()
{
$tableName = $this->connection->quoteIdentifier($this->tableName);
$sql = "SELECT version FROM {$tableName} ORDER BY version ASC";
$all = $this->connection->fetchAll($sql);
return array_map(function ($v) {
return $v['version'];
}, $all);
}
/**
* @param array $rows
* @return array
*/
protected function quoteIdentifiers(array $rows)
{
$quotedRows = array_map(function ($row) {
$quoted = [];
foreach ($row as $identifier => $value) {
$quoted[$this->connection->quoteIdentifier($identifier)] = $value;
}
return $quoted;
}, $rows);
return $quotedRows;
}
/**
* Find User instances that match the given criteria.
*
* @param array $criteria
* @param array $options An array of the following options (all optional):<pre>
* limit (int|array) The maximum number of results to return, or an array of (offset, limit).
* order_by (string|array) The name of the column to order by, or an array of column name and direction, ex. array(time_created, DESC)
* </pre>
* @return User[] An array of matching User instances, or an empty array if no matching users were found.
*/
public function findBy(array $criteria = array(), array $options = array())
{
// Check the identity map first.
if (array_key_exists('id', $criteria) && array_key_exists($criteria['id'], $this->identityMap)) {
return array($this->identityMap[$criteria['id']]);
}
list($common_sql, $params) = $this->createCommonFindSql($criteria);
$sql = 'SELECT * ' . $common_sql;
if (array_key_exists('order_by', $options)) {
list($order_by, $order_dir) = is_array($options['order_by']) ? $options['order_by'] : array($options['order_by']);
$sql .= 'ORDER BY ' . $this->conn->quoteIdentifier($order_by) . ' ' . ($order_dir == 'DESC' ? 'DESC' : 'ASC') . ' ';
}
if (array_key_exists('limit', $options)) {
list($offset, $limit) = is_array($options['limit']) ? $options['limit'] : array(0, $options['limit']);
$sql .= 'LIMIT ' . (int) $offset . ', ' . (int) $limit . ' ';
}
$data = $this->conn->fetchAll($sql, $params);
$users = array();
foreach ($data as $userData) {
if (array_key_exists($userData['id'], $this->identityMap)) {
$user = $this->identityMap[$userData['id']];
} else {
$user = $this->hydrateUser($userData);
$this->identityMap[$user->getId()] = $user;
}
$users[] = $user;
}
return $users;
}
/**
* {@inheritdoc}
*/
public function updateJob(Enlight_Components_Cron_Job $job)
{
$data = [];
$data['action'] = $job->getAction();
$data[$this->connection->quoteIdentifier('interval')] = $job->getInterval();
$data['data'] = serialize($job->getData());
$data['active'] = $job->getActive() ? '1' : '0';
$data['next'] = $job->getNext() ? $job->getNext()->toString('YYYY-MM-dd HH:mm:ss') : null;
$data['start'] = $job->getStart() ? $job->getStart()->toString('YYYY-MM-dd HH:mm:ss') : null;
$data['end'] = $job->getEnd() ? $job->getEnd()->toString('YYYY-MM-dd HH:mm:ss') : null;
if (is_null($job->getId())) {
$this->connection->insert($this->tableName, $data);
} else {
$this->connection->update($this->tableName, $data, ['id' => $job->getId()]);
}
}
/**
* Convert the tables in the current database to use given character set and collation.
*
* @param string $characterSet Character set to convert to
* @param string $collation Collation to set, must be compatible with the character set
* @param string $outputPathAndFilename
* @param boolean $verbose
* @throws ConnectionException
* @throws DBALException
*/
protected function convertToCharacterSetAndCollation($characterSet = 'utf8', $collation = 'utf8_unicode_ci', $outputPathAndFilename = null, $verbose = false)
{
$statements = ['SET foreign_key_checks = 0'];
$statements[] = 'ALTER DATABASE ' . $this->connection->quoteIdentifier($this->persistenceSettings['backendOptions']['dbname']) . ' CHARACTER SET ' . $characterSet . ' COLLATE ' . $collation;
$tableNames = $this->connection->getSchemaManager()->listTableNames();
foreach ($tableNames as $tableName) {
$statements[] = 'ALTER TABLE ' . $this->connection->quoteIdentifier($tableName) . ' DEFAULT CHARACTER SET ' . $characterSet . ' COLLATE ' . $collation;
$statements[] = 'ALTER TABLE ' . $this->connection->quoteIdentifier($tableName) . ' CONVERT TO CHARACTER SET ' . $characterSet . ' COLLATE ' . $collation;
}
$statements[] = 'SET foreign_key_checks = 1';
if ($outputPathAndFilename === null) {
try {
$this->connection->beginTransaction();
foreach ($statements as $statement) {
if ($verbose) {
$this->outputLine($statement);
}
$this->connection->exec($statement);
}
$this->connection->commit();
} catch (\Exception $exception) {
$this->connection->rollBack();
$this->outputLine($exception->getMessage());
$this->outputLine('[ERROR] The transaction was rolled back.');
}
} else {
file_put_contents($outputPathAndFilename, implode(';' . PHP_EOL, $statements) . ';');
}
}
/**
* Lock one or more tables
*
* @param array $arrTables An array of table names to be locked
*/
public function lockTables($arrTables)
{
$arrLocks = array();
foreach ($arrTables as $table => $mode) {
$arrLocks[] = $this->resConnection->quoteIdentifier($table) . ' ' . $mode;
}
$this->resConnection->exec('LOCK TABLES ' . implode(', ', $arrLocks) . ';');
}
/**
* @param array $where column => value pairs
* @return array `column` => value pairs
*/
protected function quoteIdentifiers(array $where)
{
$columns = array_map(function ($column) {
return $this->db->quoteIdentifier($column);
}, array_keys($where));
$identifier = array_combine($columns, array_values($where));
return $identifier;
}
/**
* Delete a User from the database.
*
* @param User $user
*/
public function delete(User $user)
{
$this->dispatcher->dispatch(UserEvents::BEFORE_DELETE, new UserEvent($user));
$this->clearIdentityMap($user);
$this->conn->executeUpdate('DELETE FROM ' . $this->conn->quoteIdentifier($this->userTableName) . ' WHERE ' . $this->getUserColumns('id') . ' = ?', array($user->getId()));
$this->conn->executeUpdate('DELETE FROM ' . $this->conn->quoteIdentifier($this->userCustomFieldsTableName) . ' WHERE ' . $this->getUserColumns('user_id') . ' = ?', array($user->getId()));
$this->dispatcher->dispatch(UserEvents::AFTER_DELETE, new UserEvent($user));
}
/**
* Quote the column names.
* @param array $data
* @return array
*/
private function quoteColumns(array $data)
{
$safeData = [];
foreach ($data as $key => $value) {
$key = $this->connection->quoteIdentifier($key);
$safeData[$key] = $value;
}
return $safeData;
}
/**
* Tries to autodetect, if identifier has to be quoted and quotes it.
*
* @param string $expression
* @return string
*/
public function quoteIdentifier($expression)
{
$expression = trim($expression);
if ($expression[0] === $this->getDatabasePlatform()->getIdentifierQuoteCharacter()) {
return $expression;
// already quoted
}
return parent::quoteIdentifier($expression);
}
/**
* {@inheritdoc}
*/
protected function write(array $record)
{
if ($this->columnMap === null) {
$dataToInsert = $record;
} else {
$dataToInsert = array();
foreach ($this->columnMap as $columnName => $fieldKey) {
if (isset($record[$fieldKey])) {
$dataToInsert[$this->conn->quoteIdentifier($columnName)] = $record[$fieldKey];
}
}
}
array_walk_recursive($dataToInsert, function (&$value) {
// Convert DateTime instances to ISO-8601 Strings
if ($value instanceof \DateTime) {
$value = $value->format(\DateTime::ISO8601);
}
});
$this->conn->insert($this->table, $dataToInsert);
}
/**
* {@inheritdoc}
*/
public function quoteIdentifier($identifier)
{
if (ConnectionDecoratorChain::isDecorate()) {
return (new ConnectionDecoratorChain($this, $this->decorators))->quoteIdentifier($identifier);
}
try {
return $this->conn->quoteIdentifier($identifier);
} catch (\Exception $e) {
throw $this->convertException($e);
}
}
/**
* Constructor
*
* @param Librarian $librarian The librarian interface responsible for the query
* @param DBAL\Connection $db A Doctrine Connection
* @param int $limit The maximum number of results to return
* @param array $orderBy An assoc. array of indexName => direction
* @param array $indexes An assoc. array of indexName => Index
*/
public function __construct(LibrarianInterface $librarian, DBAL\Connection $db, $limit, array $orderBy, array $indexes)
{
$this->librarian = $librarian;
$this->indexes = $indexes;
$this->db = $db;
$this->limit = $limit;
$this->queryBuilder = $this->db->createQueryBuilder();
$this->queryBuilder->setMaxResults($limit);
foreach ($this->indexes as $index) {
$index->setQuery($this);
$index->setQueryBuilder($this->queryBuilder);
}
// TODO: make this explicitly defined?
$this->mainIndex = current($this->indexes);
foreach (array_slice($this->indexes, 1) as $name => $index) {
$this->queryBuilder->leftJoin($this->mainIndex->getName(), $index->getTableName(), $index->getName($quote = true), $this->mainIndex->getName() . '.id = ' . $this->db->quoteIdentifier($name) . '.id');
}
$this->queryBuilder->select('distinct ' . $this->mainIndex->getName() . '.id')->from($this->mainIndex->getTableName(), $this->mainIndex->getName($quote = true));
foreach ($orderBy as $name => $direction) {
$this->indexes[$name]->orderBy($direction);
}
}
protected function extractDbInformation(Fiddle $fiddle)
{
$identifier = $this->db->quoteIdentifier('fiddle_' . $fiddle->getId());
$result = array();
$tables = $this->db->fetchAll(sprintf('SHOW TABLES FROM %s', $identifier));
foreach ($tables as $table) {
$table = current($table);
$tableIdentifier = $identifier . '.' . $this->db->quoteIdentifier($table);
$columns = $this->db->fetchAll(sprintf('SHOW COLUMNS FROM %s', $tableIdentifier));
$items = $this->db->fetchAll(sprintf('SELECT * FROM %s LIMIT 1000', $tableIdentifier));
$result[] = ['name' => $table, 'count' => count($items), 'columns' => $columns, 'items' => $items ?: false];
}
$fiddle->setDatabaseInformation($result);
}
/**
* Imports a template.
*
* @param string $template The template path
* @param bool $preserveData True to preserve the existing data
*/
public function importTemplate($template, $preserveData = false)
{
if (!$preserveData) {
$tables = $this->connection->getSchemaManager()->listTableNames();
foreach ($tables as $table) {
if (0 === strncmp($table, 'tl_', 3)) {
$this->connection->query('TRUNCATE TABLE ' . $this->connection->quoteIdentifier($table));
}
}
}
$data = file($this->rootDir . '/../templates/' . $template);
foreach (preg_grep('/^INSERT /', $data) as $query) {
$this->connection->query($query);
}
}
/**
* Get query builder prepared to select from the source table
*
* @param array $fields
* @return QueryBuilder
*/
public function getSelectQueryBuilder(array $fields = array())
{
$connection = $this->getConnection();
$qb = $connection->createQueryBuilder();
$fields = array_merge($this->getFields(), $fields);
$fields = array_merge(array($this->getUniqueId()), $fields);
foreach ($fields as &$field) {
if (is_array($field)) {
$keyName = current(array_keys($field));
$expression = current(array_values($field));
$field = "{$expression} AS " . $this->connection->quoteIdentifier($keyName);
}
}
$queryBuilder = $qb->select($fields)->from($this->getTableName(), 't');
return $queryBuilder;
}
/**
* Build parameters for insert or update
* @param DataObjectInterface $object
* @return array
*/
protected function buildQueryParams(DataObjectInterface $object)
{
$queryParams = [];
$dbColumns = $this->queryHelper->getDbColumns($object->getTableName());
$data = $object->getData();
foreach ($dbColumns as $column => $acceptNull) {
if ($column == 'id') {
continue;
}
if (isset($data[$column])) {
$queryParams[$this->db->quoteIdentifier($column)] = $data[$column];
} else {
if ($acceptNull) {
$queryParams[$this->db->quoteIdentifier($column)] = null;
}
}
}
return $queryParams;
}
protected function sqlDownSchemaSqlite()
{
$queries = array();
$newTable = $this->usersTable . '_' . uniqid();
$queries[] = 'CREATE TABLE ' . $this->conn->quoteIdentifier($newTable) . ' (
id INTEGER PRIMARY KEY,
email VARCHAR(100) NOT NULL DEFAULT "" UNIQUE,
password VARCHAR(255) DEFAULT NULL,
salt VARCHAR(255) NOT NULL DEFAULT "",
roles VARCHAR(255) NOT NULL DEFAULT "",
name VARCHAR(100) NOT NULL DEFAULT "",
time_created INT NOT NULL DEFAULT 0
)';
$queries[] = 'INSERT INTO ' . $this->conn->quoteIdentifier($newTable) . '
SELECT id, email, password, salt, roles, name, time_created FROM ' . $this->conn->quoteIdentifier($this->usersTable);
$queries[] = 'DROP TABLE ' . $this->conn->quoteIdentifier($this->usersTable);
$queries[] = 'ALTER TABLE ' . $this->conn->quoteIdentifier($newTable) . ' RENAME TO ' . $this->conn->quoteIdentifier($this->usersTable);
return $queries;
}
/**
* Builds the ORDER BY part
* @param QueryBuilder $qb
* @param array $order_by
* @return self
*/
protected function buildOrderBy(QueryBuilder $qb, array $order_by = [])
{
foreach ($order_by as $order) {
$column = null;
$direction = 'ASC';
if (is_string($order)) {
$column = $order;
} elseif (is_array($order) && count($order) === 2) {
list($column, $direction) = $order;
}
if ($column === null || $this->getColumn($column) === null) {
throw Schema\SchemaException::columnDoesNotExist($column, $this->table_name);
}
if (!in_array($direction, ['ASC', 'DESC'])) {
throw QueryBuilderException::orderByDirectionDoesNotExist($direction);
}
$qb->addOrderBy($this->conn->quoteIdentifier($column), $direction);
}
return $this;
}
public function testSaveManyToMany()
{
$otherObjects = [];
$otherObject = new OtherDataObject();
$otherObject->setName('Other object many-to-many 1-1');
$otherObject2 = new OtherDataObject();
$otherObject2->setName('Other object many-to-many 1-2');
$otherObject3 = new OtherDataObject();
$otherObject3->setName('Other object many-to-many 2-1');
$otherObject4 = new OtherDataObject();
$otherObject4->setName('Other object many-to-many 2-2');
$otherObjectToDelete = new OtherDataObject();
$otherObjects[] = $otherObjectToDelete->setName('Other object many-to-many 1-3');
$otherObjectToDelete2 = new OtherDataObject();
$otherObjects[] = $otherObjectToDelete2->setName('Other object many-to-many 2-3');
$objects = [];
$object = new ExtendedDataObject();
$object2 = new ExtendedDataObject();
$objects[] = $object->setMyColumn('Save many-to-many 1')->setOtherDataObjects([$otherObject, $otherObject2]);
$objects[] = $object2->setMyColumn('Save many-to-many 2')->setOtherDataObjects([$otherObject3, $otherObject4]);
$this->repository->saveAll($objects);
$this->objectMapper->saveAll($otherObjects);
$queryHelper = $this->objectMapper->getQueryHelper();
$queryHelper->massInsert('extended_other_rel', [['extendedDataObjectId' => $object->getId(), 'otherDataObjectId' => $otherObjectToDelete->getId()], ['extendedDataObjectId' => $object2->getId(), 'otherDataObjectId' => $otherObjectToDelete2->getId()]]);
$relationshipSaver = $this->objectMapper->getRelationshipSaver();
$relationshipSaver->saveManyToMany($objects, OtherDataObject::class, 'extended_other_rel');
$this->assertGreaterThan(0, $otherObject->getId());
$this->assertGreaterThan(0, $otherObject2->getId());
$this->assertGreaterThan(0, $otherObject3->getId());
$this->assertGreaterThan(0, $otherObject4->getId());
$objectLinks = $queryHelper->buildSelectQuery('extended_other_rel', self::$connection->quoteIdentifier('otherDataObjectId'), ['extendedDataObjectId' => $object->getId()])->execute()->fetchAll(\PDO::FETCH_COLUMN);
$this->assertCount(2, $objectLinks);
$this->assertEquals($otherObject->getId(), $objectLinks[0]);
$this->assertEquals($otherObject2->getId(), $objectLinks[1]);
$object2Links = $queryHelper->buildSelectQuery('extended_other_rel', self::$connection->quoteIdentifier('otherDataObjectId'), ['extendedDataObjectId' => $object2->getId()])->execute()->fetchAll(\PDO::FETCH_COLUMN);
$this->assertCount(2, $object2Links);
$this->assertEquals($otherObject3->getId(), $object2Links[0]);
$this->assertEquals($otherObject4->getId(), $object2Links[1]);
}
/**
* Constructor.
*
* @param Connection $connection The connection to use for query execution.
* @param string $table The name of the table to insert rows into.
* @param string[] $columns The names of the columns to insert values into.
* Can be left empty to allow arbitrary row inserts based on the table's column order.
*/
public function __construct(Connection $connection, string $table, array $columns = [])
{
$this->connection = $connection;
$this->table = $connection->quoteIdentifier($table);
$this->columns = $columns;
}
/**
* @param \Doctrine\DBAL\Connection $connection
*/
function it_deletes_file($connection)
{
$connection->quoteIdentifier(Argument::any())->will(function ($argument) {
return sprintf('"%s"', $argument[0]);
});
$connection->delete('someTableName', array('"key"' => 'filename'))->shouldBeCalled()->willReturn(1);
$this->delete('filename')->shouldReturn(true);
}
/**
* Escape identifier
*
* @param string $identifier
* @return string
*/
public function escapeIdentifier($identifier)
{
return $this->connection->quoteIdentifier($identifier);
}
/**
* @return void
*/
public function flush()
{
$this->connection->exec("DROP TABLE IF EXISTS {$this->connection->quoteIdentifier($this->tableName)}");
$this->setUp();
}
/**
* Quotes an identifier.
*
* @param mixed $str The identifier
*
* @return mixed The quoted identifier
*/
private function quote($str)
{
return $this->connection->quoteIdentifier($str);
}
/**
* @param string $name
*/
protected function dropTable($name)
{
$this->connection->exec('DROP TABLE ' . $this->connection->quoteIdentifier($name));
}
/**
* @param \Doctrine\DBAL\Connection $connection
*/
function it_should_delete_file($connection)
{
$connection->quoteIdentifier(ANY_ARGUMENT)->willReturnUsing(function ($argument) {
return sprintf('"%s"', $argument);
});
$connection->delete('someTableName', array('"key"' => 'filename'))->shouldBeCalled()->willReturn(1);
$this->delete('filename')->shouldReturn(true);
}
