public function deleteOption($key)
{
$stmt = $this->db->prepare("DELETE FROM options WHERE key IS :key");
$stmt->bindParam("key", $key, \PDO::PARAM_STR);
$stmt->execute();
$this->db->commit();
}
/**
* Writes the current session data to the database.
*
* @param FilterResponseEvent $event The event object
*/
public function onKernelResponse(FilterResponseEvent $event)
{
if (!$this->hasUser() || !$this->isContaoMasterRequest($event)) {
return;
}
$user = $this->getUserObject();
$this->connection->prepare('UPDATE ' . $user->getTable() . ' SET session=? WHERE id=?')->execute([serialize($this->getSessionBag()->all()), $user->id]);
}
/**
* @param InputInterface $input
* @param OutputInterface $output
* @throws \Exception
*/
protected function execute(InputInterface $input, OutputInterface $output)
{
$dump = file_get_contents($this->configPath . '/db.sql');
$statement = $this->connection->prepare($dump);
$statement->execute();
if ($statement->errorCode() != 0) {
throw new \Exception('Error while creating Database');
}
$output->writeln('<info>Database created successfully</info>');
}
/**
* Execute a query and map it to your requested types. Returns an array of your rows.
* @param string|array $requestedTypes
* @param string|object|Statement $sql
* @param array $params
* @param string|array $split
* @throws \InvalidArgumentException
* @return array
*/
public function query($requestedTypes, $sql, $params = [], $split = [])
{
if (is_null($requestedTypes) || empty($requestedTypes)) {
throw new \InvalidArgumentException('You must specify at least one type to map your results against.');
}
if (!$sql instanceof Statement) {
$sql = $this->connection->prepare($sql);
}
$sql->execute($params);
$results = $sql->fetchAll();
return $this->mapper->map($requestedTypes, $results, $split);
}
public function deleteVideo(Video $video)
{
$stmt = $this->db->prepare("DELETE FROM videos WHERE id IS :id");
$stmt->bindParam("id", $video->getId());
$stmt->execute();
$this->db->commit();
}
/**
* Executes an SQL INSERT/UPDATE/DELETE query with the given parameters
* and returns the number of affected rows.
*
* This method supports PDO binding types as well as DBAL mapping types.
*
* @param string $query The SQL query.
* @param array $params The query parameters.
* @param array $types The parameter types.
* @return integer The number of affected rows.
* @internal PERF: Directly prepares a driver statement, not a wrapper.
*/
public function executeUpdate($query, array $params = array(), array $types = array())
{
$this->connect();
$logger = $this->_config->getSQLLogger();
if ($logger) {
$logger->startQuery($query, $params, $types);
}
try {
if ($params) {
list($query, $params, $types) = SQLParserUtils::expandListParameters($query, $params, $types);
$stmt = $this->_conn->prepare($query);
if ($types) {
$this->_bindTypedValues($stmt, $params, $types);
$stmt->execute();
} else {
$stmt->execute($params);
}
$result = $stmt->rowCount();
} else {
$result = $this->_conn->exec($query);
}
} catch (\Exception $ex) {
throw DBALException::driverExceptionDuringQuery($ex, $query, $this->resolveParams($params, $types));
}
if ($logger) {
$logger->stopQuery();
}
return $result;
}
/**
* Executes an SQL INSERT/UPDATE/DELETE query with the given parameters
* and returns the number of affected rows.
*
* This method supports PDO binding types as well as DBAL mapping types.
*
* @param string $query The SQL query.
* @param array $params The query parameters.
* @param array $types The parameter types.
* @return integer The number of affected rows.
* @internal PERF: Directly prepares a driver statement, not a wrapper.
*/
public function executeUpdate($query, array $params = array(), array $types = array())
{
$this->connect();
$hasLogger = $this->_config->getSQLLogger() !== null;
if ($hasLogger) {
$this->_config->getSQLLogger()->startQuery($query, $params, $types);
}
if ($params) {
list($query, $params, $types) = SQLParserUtils::expandListParameters($query, $params, $types);
$stmt = $this->_conn->prepare($query);
if ($types) {
$this->_bindTypedValues($stmt, $params, $types);
$stmt->execute();
} else {
$stmt->execute($params);
}
$result = $stmt->rowCount();
} else {
$result = $this->_conn->exec($query);
}
if ($hasLogger) {
$this->_config->getSQLLogger()->stopQuery();
}
return $result;
}
public function __construct(Connection $con, MethodPersister $methodPersister, PropertyPersister $propertyPersister, ConstantPersister $constantPersister)
{
$this->con = $con;
$this->insertStmt = $con->prepare(self::INSERT_STMT);
$this->methodPersister = $methodPersister;
$this->propertyPersister = $propertyPersister;
$this->constantPersister = $constantPersister;
}
private function listenForEcho(Connection $db)
{
$readStatement = $db->prepare(sprintf(self::READ_HEARTBEAT_TOKEN_SQL, $this->heartbeatTable, $this->heartbeatTableColumn));
$readStatement->execute(array($this->heartbeatToken));
if ($readStatement->fetch() === false) {
throw new DBALException("Error during listening for heartbeat echo");
}
}
/**
* {@inheritdoc}
*/
public function rewind()
{
if ($this->statement) {
throw new InvalidMethodCallException('Cannot rewind a PDOStatement');
}
$this->statement = $this->connection->prepare($this->query);
$this->statement->execute($this->parameters);
$this->next();
}
private function prepare($query, $params, $types)
{
$stmt = $this->conn->prepare($query);
foreach ($params as $i => $value) {
$type = $types[$i];
$stmt->bindValue($i, $type->convertToDatabaseValue($value, $this->conn->getDatabasePlatform()), $type->getBindingType());
}
return $stmt;
}
/**
* @return Statement
*/
private function prepareSelectStatement()
{
if ($this->selectStatement === null) {
$sql = <<<EOQ
SELECT event_id, event_name, event_payload, aggregate_version, took_place_at, metadata
FROM {$this->tableName}
WHERE aggregate_id = :aggregate_id
ORDER BY aggregate_version ASC
EOQ;
$this->selectStatement = $this->connection->prepare($sql);
}
return $this->selectStatement;
}
public function patch($version, \DOMDocument $domct, \DOMDocument $domth, Connection $connbas, \unicode $unicode)
{
if ($version == "2.0.1") {
$th = $domth->documentElement;
$ct = $domct->documentElement;
$xp = new DOMXPath($domth);
$te = $xp->query("/thesaurus//te");
for ($i = 0; $i < $te->length; $i++) {
$id = $te->item($i)->getAttribute("id");
$nid = (int) $te->item($i)->getAttribute("nextid");
for ($n = $te->item($i)->firstChild; $n; $n = $n->nextSibling) {
if ($n->nodeName == "sy") {
$n->setAttribute("id", $id . "." . $nid);
$te->item($i)->setAttribute("nextid", ++$nid);
}
}
}
$sql = "UPDATE record SET status=status & ~2";
$stmt = $connbas->prepare($sql);
$stmt->execute();
$stmt->closeCursor();
$sql = "DELETE FROM thit";
$stmt = $connbas->prepare($sql);
$stmt->execute();
$stmt->closeCursor();
$domct = new DOMDocument();
$domct->load(__DIR__ . "/../../../lib/conf.d/blank_cterms.xml");
$ct = $domct->documentElement;
$ct->setAttribute("creation_date", $now = date("YmdHis"));
$ct->setAttribute("modification_date", $now);
$ct->setAttribute("version", $version = "2.0.2");
$th->setAttribute("version", $version = "2.0.2");
$th->setAttribute("modification_date", date("YmdHis"));
$version = "2.0.2";
}
return $version;
}
public function patch($version, \DOMDocument $domct, \DOMDocument $domth, Connection $connbas, \unicode $unicode)
{
if ($version == "2.0.2") {
$th = $domth->documentElement;
$ct = $domct->documentElement;
$sql = "ALTER TABLE `pref` ADD `cterms_moddate` DATETIME";
$stmt = $connbas->prepare($sql);
$stmt->execute();
$stmt->closeCursor();
$sql = "ALTER TABLE `pref` ADD `thesaurus_moddate` DATETIME";
$stmt = $connbas->prepare($sql);
$stmt->execute();
$stmt->closeCursor();
$sql = "UPDATE pref SET thesaurus_moddate = :date1, cterms_moddate = :date2";
$stmt = $connbas->prepare($sql);
$stmt->execute([':date1' => $th->getAttribute("modification_date"), ':date2' => $ct->getAttribute("modification_date")]);
$stmt->closeCursor();
$ct->setAttribute("version", $version = "2.0.3");
$th->setAttribute("version", $version = "2.0.3");
$th->setAttribute("modification_date", date("YmdHis"));
$version = "2.0.3";
}
return $version;
}
/**
* Returns the languages which are currently in use.
*
* @return array The languages array
*/
private function getLanguagesInUse()
{
// Get all languages in use (see #6013)
$query = "\n SELECT language FROM tl_member\n UNION SELECT language FROM tl_user\n UNION SELECT REPLACE(language, '-', '_') FROM tl_page\n WHERE type='root'\n ";
$statement = $this->connection->prepare($query);
$statement->execute();
$languages = [];
while ($language = $statement->fetch(\PDO::FETCH_OBJ)) {
if ('' === $language->language) {
continue;
}
$languages[] = $language->language;
// Also cache "de" if "de-CH" is requested
if (strlen($language->language) > 2) {
$languages[] = substr($language->language, 0, 2);
}
}
return array_unique($languages);
}
/**
* Executes an SQL INSERT/UPDATE/DELETE query with the given parameters
* and returns the number of affected rows.
*
* This method supports PDO binding types as well as DBAL mapping types.
*
* @param string $query The SQL query.
* @param array $params The query parameters.
* @param array $types The parameter types.
* @return integer The number of affected rows.
* @internal PERF: Directly prepares a driver statement, not a wrapper.
*/
public function executeUpdate($query, array $params = array(), array $types = array())
{
$this->connect();
if ($this->_config->getSQLLogger() !== null) {
$this->_config->getSQLLogger()->logSQL($query, $params);
}
if ($params) {
$stmt = $this->_conn->prepare($query);
if ($types) {
$this->_bindTypedValues($stmt, $params, $types);
$stmt->execute();
} else {
$stmt->execute($params);
}
$result = $stmt->rowCount();
} else {
$result = $this->_conn->exec($query);
}
return $result;
}
/**
* Link files to the Mongo product values.
*
* @param string $productTable
*/
protected function migrateMediasOnProductValueMongo($productTable)
{
$db = $this->getMongoDatabase();
$valueCollection = new MongoCollection($db, $productTable);
$productsWithMedia = $valueCollection->find(['values.media' => ['$ne' => null]]);
$stmt = $this->ormConnection->prepare('SELECT fi.id FROM akeneo_file_storage_file_info fi WHERE fi.old_file_key = ?');
foreach ($productsWithMedia as $product) {
foreach ($product['values'] as $index => $value) {
if (isset($value['media'])) {
$stmt->bindValue(1, $value['media']['filename']);
$stmt->execute();
$fileInfo = $stmt->fetch();
/*
db.pim_catalog_product.update(
{ _id: ObjectId("55ee950c48177e12588b5ccb"), "values._id": ObjectId("55ee950c48177e12588b5cd4") },
{ $set: {"values.$.media": NumberLong(666)} }
)
*/
$valueCollection->update(['_id' => new MongoId($product['_id']), 'values._id' => new MongoId($value['_id'])], ['$set' => ['values.$.media' => (int) $fileInfo['id']]]);
}
}
}
}
public function patch($version, \DOMDocument $domct, \DOMDocument $domth, Connection $connbas, \unicode $unicode)
{
if ($version == "2.0.0") {
$th = $domth->documentElement;
$ct = $domct->documentElement;
$xp = new DOMXPath($domth);
$te = $xp->query("/thesaurus//te");
for ($i = 0; $i < $te->length; $i++) {
$id = $te->item($i)->getAttribute("id");
if ($id[0] >= "0" && $id[0] <= "9") {
$te->item($i)->setAttribute("id", "T" . $id);
}
}
$ct->setAttribute("version", $version = "2.0.1");
$th->setAttribute("version", $version = "2.0.1");
$th->setAttribute("modification_date", date("YmdHis"));
$sql = "UPDATE thit SET value=CONCAT('T',value) WHERE LEFT(value,1)>='0' AND LEFT(value,1)<='9'";
$stmt = $connbas->prepare($sql);
$stmt->execute();
$stmt->closeCursor();
$version = "2.0.1";
}
return $version;
}
protected function dropTable(\Doctrine\DBAL\Driver\Connection $conn)
{
$sql = 'DROP TABLE IF EXISTS test_entity;';
$stmt = $conn->prepare($sql);
$stmt->execute();
}
public static function create(Application $app, Connection $connection, \SplFileInfo $data_template)
{
if (!file_exists($data_template->getRealPath())) {
throw new \InvalidArgumentException($data_template->getRealPath() . " does not exist");
}
$sql = 'SELECT sbas_id
FROM sbas
WHERE host = :host AND port = :port AND dbname = :dbname
AND user = :user AND pwd = :password';
$host = $connection->getHost();
$port = $connection->getPort();
$dbname = $connection->getDatabase();
$user = $connection->getUsername();
$password = $connection->getPassword();
$params = [':host' => $host, ':port' => $port, ':dbname' => $dbname, ':user' => $user, ':password' => $password];
$stmt = $app['phraseanet.appbox']->get_connection()->prepare($sql);
$stmt->execute($params);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$stmt->closeCursor();
if ($row) {
return $app['phraseanet.appbox']->get_databox((int) $row['sbas_id']);
}
try {
$sql = 'CREATE DATABASE `' . $dbname . '`
CHARACTER SET utf8 COLLATE utf8_unicode_ci';
$stmt = $connection->prepare($sql);
$stmt->execute();
$stmt->closeCursor();
} catch (\Exception $e) {
}
$sql = 'USE `' . $dbname . '`';
$stmt = $connection->prepare($sql);
$stmt->execute();
$stmt->closeCursor();
$sql = 'SELECT MAX(ord) as ord FROM sbas';
$stmt = $app['phraseanet.appbox']->get_connection()->prepare($sql);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$stmt->closeCursor();
if ($row) {
$ord = $row['ord'] + 1;
}
$params[':ord'] = $ord;
$sql = 'INSERT INTO sbas (sbas_id, ord, host, port, dbname, sqlengine, user, pwd)
VALUES (null, :ord, :host, :port, :dbname, "MYSQL", :user, :password)';
$stmt = $app['phraseanet.appbox']->get_connection()->prepare($sql);
$stmt->execute($params);
$stmt->closeCursor();
$sbas_id = (int) $app['phraseanet.appbox']->get_connection()->lastInsertId();
$app['phraseanet.appbox']->delete_data_from_cache(appbox::CACHE_LIST_BASES);
$databox = $app['phraseanet.appbox']->get_databox($sbas_id);
$databox->insert_datas();
$databox->setNewStructure($data_template, $app['conf']->get(['main', 'storage', 'subdefs']));
return $databox;
}
private function rollbackInstall(Connection $abConn, Connection $dbConn = null)
{
$structure = simplexml_load_file(__DIR__ . "/../../../conf.d/bases_structure.xml");
if (!$structure) {
throw new \RuntimeException('Unable to load schema');
}
$appbox = $structure->appbox;
$databox = $structure->databox;
foreach ($appbox->tables->table as $table) {
try {
$sql = 'DROP TABLE IF EXISTS `' . $table['name'] . '`';
$stmt = $abConn->prepare($sql);
$stmt->execute();
$stmt->closeCursor();
} catch (DBALException $e) {
}
}
if (null !== $dbConn) {
foreach ($databox->tables->table as $table) {
try {
$sql = 'DROP TABLE IF EXISTS `' . $table['name'] . '`';
$stmt = $dbConn->prepare($sql);
$stmt->execute();
$stmt->closeCursor();
} catch (DBALException $e) {
}
}
}
$this->app['configuration.store']->delete();
return;
}
private function doRejectBranch(Connection $connbas, \DOMElement $node)
{
if (strlen($oldid = $node->getAttribute("id")) > 1) {
$node->setAttribute("id", $newid = "R" . substr($oldid, 1));
$thit_oldid = str_replace(".", "d", $oldid) . "d";
$thit_newid = str_replace(".", "d", $newid) . "d";
$sql = "UPDATE thit SET value = :new_value WHERE value = :old_value";
$stmt = $connbas->prepare($sql);
$stmt->execute([':old_value' => $thit_oldid, ':new_value' => $thit_newid]);
$stmt->closeCursor();
}
for ($n = $node->firstChild; $n; $n = $n->nextSibling) {
if ($n->nodeType == XML_ELEMENT_NODE) {
$this->doRejectBranch($connbas, $n);
}
}
}
public function fixIds(Connection $connbas, &$node)
{
if ($node->nodeType != XML_ELEMENT_NODE) {
return;
}
if ($node->parentNode && $node->parentNode->nodeType == XML_ELEMENT_NODE) {
$pid = $node->parentNode->getAttribute("id");
if ($pid != "") {
$id = $node->getAttribute("id");
if (substr($id, 1, strlen($pid)) != substr($pid, "1") . ".") {
$nid = $node->parentNode->getAttribute("nextid");
$node->parentNode->setAttribute("nextid", $nid + 1);
$node->setAttribute("id", $newid = $pid . "." . $nid);
printf("// \tid '%s' (child of '%s') fixed to '%s'\n", $id, $pid, $newid);
$id = str_replace(".", "d", $id) . "d";
$newid = str_replace(".", "d", $newid) . "d";
$sql = "UPDATE thit SET value = :newid WHERE value = :oldid";
$stmt = $connbas->prepare($sql);
$stmt->execute([':newid' => $newid, ':oldid' => $id]);
$stmt->closeCursor();
}
}
}
for ($n = $node->firstChild; $n; $n = $n->nextSibling) {
$this->fixIds($connbas, $n);
}
}
private static function getNewOrder(Connection $conn, $sbas_id)
{
$sql = "SELECT GREATEST(0, MAX(ord)) + 1 AS ord FROM bas WHERE sbas_id = :sbas_id";
$stmt = $conn->prepare($sql);
$stmt->execute([':sbas_id' => $sbas_id]);
$ord = $stmt->fetch(\PDO::FETCH_ASSOC);
$stmt->closeCursor();
return $ord['ord'] ?: 1;
}
