/**
* @param string $tableName
* @param array $rows
*/
protected function insertTableRows($tableName, array $rows)
{
foreach ($rows as $rowKey => $values) {
$this->connection->insert($tableName, $this->parser->parse($values));
$this->parser->addReference($rowKey, $this->connection->lastInsertId());
}
}
/**
* Busca as faltas dos tipos $type de todos os alunos atualmente matriculados na turma $schoolClass na data $date.
*
* @param Connection $conn
* @param integer $schoolClass
* @param string $date
* @param integer $type
* @return array
*/
public static function findStudentAttendances(Connection $conn, $schoolClass, $date, $type)
{
$query = $conn->createQueryBuilder();
$sth = $query->select('a.enrollment_id')->from('attendance', 'a')->join('a', 'enrollment', 'e', 'e.enrollment_id = a.enrollment_id')->where('e.enrollment_enddate IS NULL')->andWhere('e.class_id = ?')->andWhere('a.attendance_date = ?')->andWhere('a.attendance_type_id = ?')->setParameters([$schoolClass, $date, $type])->execute();
$list = $sth->fetchAll();
return $list;
}
private function singleImport($config, OutputInterface &$output, $entity_key = null)
{
$this->connection = $this->connectionFactory->createConnection($config['database']);
$this->connection->getConfiguration()->getSQLLogger(null);
if ($entity_key) {
if (!isset($config['maps'][$entity_key])) {
throw new \Exception("Entity alias not found: " . $entity_key);
}
$map = $config['maps'][$entity_key];
if (!$this->container->has($map['old_data']['service_id'])) {
throw new \Exception("Service not exists: " . $map['old_data']['service_id']);
}
$result = $this->importEntity($map);
$output->writeln("<info>Total " . count($result) . " {$entity_key} imported </info>");
} else {
foreach ((array) $config['maps'] as $key => $map) {
if (!$this->container->has($map['old_data']['service_id'])) {
throw new \Exception("Service not exists: " . $map['old_data']['service_id']);
}
$offset = 0;
do {
$result = $this->importEntity($map);
$output->writeln("<info>Total " . count($result) . " {$key} imported </info>");
if (!$result) {
break;
}
$offset++;
$this->setOffset($offset);
} while (true);
}
}
$this->connection->close();
}
public function save(Order $order)
{
$data = $order->jsonSerialize();
unset($data['id']);
$this->connection->insert($this->getTableName(), $data);
$order->setId($this->connection->lastInsertId());
}
public function shutdown()
{
if ($this->connection->isTransactionActive()) {
$this->rollback();
}
$this->connection->close();
}
/**
* @param QueryInterface $query
* @return array
*/
public function fetch(QueryInterface $query)
{
$query->checkReplacements();
$plainQuery = $query->getPlainQuery();
list($parameters, $plainQuery) = $this->modifyParametersFromArrayToScalar($query->getParameters(), $plainQuery);
return $this->connection->fetchAll($plainQuery, $parameters);
}
/**
* @param Connection $connection
* @return Success|Failure
*/
private function checkStandardConnection(Connection $connection)
{
if ($connection->ping()) {
return new Success(get_class($connection));
}
return new Failure(get_class($connection));
}
/**
* Construtor
*
* @param Connection $connection
* @param array $rootAliases
* @param array $fieldMap
*/
public function __construct(Connection $connection, array $rootAliases, array $fieldMap = array())
{
$this->rootAliases = $rootAliases;
$this->fieldMap = $fieldMap;
$this->conn = $connection;
$this->expr = $this->conn->getExpressionBuilder();
}
public function stopQuery()
{
if ($this->explainRunning) {
return;
}
$keys = array_keys($this->queries);
$key = end($keys);
$this->queries[$key][self::TIME] = Debugger::timer('doctrine');
$this->totalTime += $this->queries[$key][self::TIME];
// get EXPLAIN for SELECT queries
if ($this->doExplains) {
if ($this->connection === NULL) {
throw new \Nette\InvalidStateException('You must set a Doctrine\\DBAL\\Connection to get EXPLAIN.');
}
$query = $this->queries[$key][self::SQL];
if (strtoupper(substr(ltrim($query), 0, 6)) !== 'SELECT') {
// only SELECTs are supported
return;
}
// prevent logging explains & infinite recursion
$this->explainRunning = TRUE;
$params = $this->queries[$key][self::PARAMS];
$types = $this->queries[$key][self::TYPES];
$stmt = $this->connection->executeQuery('EXPLAIN ' . $query, $params, $types);
$this->queries[$key][self::EXPLAIN] = $stmt->fetchAll();
$this->explainRunning = FALSE;
}
}
/**
* Initializes the database (once).
*
* @throws \Doctrine\DBAL\DBALException
* @throws \Doctrine\ORM\ORMException
* @throws \Doctrine\ORM\Tools\ToolsException
*/
protected function setUp()
{
if (null === static::$_conn) {
$dbPath = __DIR__ . '/../../../db.sqlite';
if (file_exists($dbPath)) {
unlink($dbPath);
}
$params = ['driver' => 'pdo_sqlite', 'path' => $dbPath];
static::$_conn = DriverManager::getConnection($params);
static::$_conn->getConfiguration()->setSQLLogger(null);
}
if (null === static::$_em) {
$paths = [__DIR__ . '/../../../../../src/Ekyna/Commerce/Bridge/Doctrine/ORM/Resources/mapping'];
$isDevMode = true;
$config = Setup::createXMLMetadataConfiguration($paths, $isDevMode);
$em = EntityManager::create(static::$_conn, $config);
$classes = [];
foreach (static::$_classes as $class) {
array_push($classes, $em->getClassMetadata($class));
}
$schemaTool = new SchemaTool($em);
$schemaTool->dropSchema($classes);
$schemaTool->createSchema($classes);
// Load fixtures
$loader = new Loader();
$loader->loadFromDirectory(__DIR__ . '/../../../../../src/Ekyna/Commerce/Bridge/Doctrine/Fixtures');
$purger = new ORMPurger();
$executor = new ORMExecutor($em, $purger);
$executor->execute($loader->getFixtures());
static::$_em = $em;
}
}
protected function resetSharedConn()
{
if (self::$_sharedConn) {
self::$_sharedConn->close();
self::$_sharedConn = null;
}
}
protected function getRecentFrequent(User $user, array $criteria = null, $limit = 10)
{
$sql = '
SELECT text, MAX(createdAt) AS recency, COUNT(*) AS frequency
FROM stress_log_factors
JOIN stress_logs ON stress_log_id = stress_logs.id
WHERE user_id = ?
';
$params = array($user->getId());
if (is_array($criteria)) {
foreach ($criteria as $key => $val) {
$op = '=';
if (is_array($val)) {
reset($val);
$op = key($val);
$val = current($val);
}
$sql .= 'AND `' . $key . '` ' . $op . ' ? ';
$params[] = $val;
}
}
$sql .= 'GROUP BY text ';
$sql .= 'ORDER BY recency DESC, frequency DESC ';
$sql .= 'LIMIT ' . (int) $limit;
$result = $this->conn->fetchAll($sql, $params);
return array_column($result, 'text');
}
/**
* @param Connection $connection
* @param AppKernel $kernel
*
* @throws \Claroline\MigrationBundle\Migrator\InvalidDirectionException
* @throws \Claroline\MigrationBundle\Migrator\InvalidVersionException
* @throws \Doctrine\DBAL\Migrations\MigrationException
*/
protected function migrateBadgeTables(Connection $connection, AppKernel $kernel)
{
$portfolioBundle = $this->container->get('claroline.persistence.object_manager')->getRepository('ClarolineCoreBundle:Plugin')->findBy(array('vendorName' => 'Icap', 'bundleName' => 'PortfolioBundle'));
$portfolioBundle = count($portfolioBundle) === 1 ? true : false;
if (!$portfolioBundle && $connection->getSchemaManager()->tablesExist(['icap__portfolio_widget_badges'])) {
$this->log('Deleting portfolios badges tables...');
$connection->getSchemaManager()->dropTable('icap__portfolio_widget_badges_badge');
$connection->getSchemaManager()->dropTable('icap__portfolio_widget_badges');
$this->log('Portfolios badges tables deleted.');
}
if ($portfolioBundle && !$connection->getSchemaManager()->tablesExist(['icap__portfolio_widget_badges'])) {
$badgeBundle = $kernel->getBundle('IcapBadgeBundle');
$this->log('Executing migrations for portfolio interaction');
$migrationsDir = "{$badgeBundle->getPath()}/Installation/Migrations";
$migrationsName = "{$badgeBundle->getName()} migration";
$migrationsNamespace = "{$badgeBundle->getNamespace()}\\Installation\\Migrations";
$migrationsTableName = 'doctrine_' . strtolower($badgeBundle->getName()) . '_versions';
$config = new Configuration($connection);
$config->setName($migrationsName);
$config->setMigrationsDirectory($migrationsDir);
$config->setMigrationsNamespace($migrationsNamespace);
$config->setMigrationsTableName($migrationsTableName);
$config->registerMigrationsFromDirectory($migrationsDir);
$migration = new Migration($config);
$executedQueriesNumber = $migration->migrate('20150929141509');
$this->log(sprintf('%d queries executed', $executedQueriesNumber));
}
}
function let(EntityManager $manager, Connection $connection, Statement $statement, ClassMetadata $classMetadata)
{
$connection->prepare(Argument::any())->willReturn($statement);
$manager->getClassMetadata(Argument::any())->willReturn($classMetadata);
$manager->getConnection()->willReturn($connection);
$this->beConstructedWith($manager, 'pim_product_class');
}
public function collectData(IdentifiableInterface $root)
{
$routes = $this->conn->fetchAll('SELECT `routeAscent` ' . 'FROM `' . EdkTables::ROUTE_TBL . '` r ' . 'INNER JOIN `' . CoreTables::AREA_TBL . '` a ON r.`areaId` = a.`id` ' . 'WHERE a.`projectId` = :projectId AND r.`routeType` = 0', [':projectId' => $root->getId()]);
if (sizeof($routes) == 0) {
return false;
}
foreach ($routes as &$row) {
$row['normalized'] = $this->step($row['routeAscent']);
}
$this->data = [];
$min = 10000;
$max = 0;
foreach ($routes as $row) {
if ($row['normalized'] < $min) {
$min = $row['normalized'];
}
if ($row['normalized'] > $max) {
$max = $row['normalized'];
}
}
if ($min >= $max) {
return false;
}
for ($i = $min; $i <= $max; $i++) {
$this->data[$i] = 0;
}
foreach ($routes as $row) {
$this->data[$row['normalized']]++;
}
return true;
}
/**
* @param $userId
* @return null|\stdClass containing userData
*/
public function findById($userId)
{
$stmt = $this->connection->prepare(sprintf("SELECT * FROM %s where id = :user_id", Table::USER));
$stmt->bindValue('user_id', $userId);
$stmt->execute();
return $stmt->fetch();
}
/**
* @param Schema $targetSchema
* @param \Doctrine\DBAL\Connection $connection
* @return \Doctrine\DBAL\Schema\SchemaDiff
*/
protected function getDiff(Schema $targetSchema, \Doctrine\DBAL\Connection $connection)
{
$platform = $connection->getDatabasePlatform();
$platform->registerDoctrineTypeMapping('tinyint unsigned', 'integer');
$platform->registerDoctrineTypeMapping('smallint unsigned', 'integer');
return parent::getDiff($targetSchema, $connection);
}
protected function loadAuthors(Connection $connection)
{
$records = [['Thomas Hardy'], ['Terry Pratchett'], ['Malcolm Gladwell'], ['Charles Dickens']];
foreach ($records as $r) {
$connection->insert('authors', ['name' => $r[0]]);
}
}
protected function buildOptions(ReportParameter $parameter)
{
$options = array();
if ($parameter->getType() == ReportParameter::TYPE_QUERY) {
$stmt = $this->connection->prepare($parameter->getChoices());
$stmt->execute();
$results = $stmt->fetchAll();
$values = array(null => '');
$keySet = array_keys(current($results));
$key = current(array_slice($keySet, 0, 1));
$value = current(array_slice($keySet, 1, 1));
foreach ($results as $result) {
$values[$result[$value]] = $result[$key];
}
$options = array_merge($options, array('choices' => $values));
$parameter->setType(ChoiceType::class);
}
$normalizer = new \Symfony\Component\Serializer\Normalizer\GetSetMethodNormalizer();
$normalizer->setIgnoredAttributes(array('id', 'name', 'type', 'created', 'modified', 'active'));
$serializer = new \Symfony\Component\Serializer\Serializer(array($normalizer));
$options = array_merge($serializer->normalize($parameter), $options);
foreach ($options as $key => $value) {
if (is_null($value)) {
unset($options[$key]);
}
}
$this->handleConstraints($options);
return $options;
}
public function __construct(Connection $con)
{
$this->con = $con;
$this->platform = $con->getDatabasePlatform();
$this->phpType = Type::getType(PhpTypeType::NAME);
$this->insertStmt = $con->prepare(self::INSERT_SQL);
}
/**
* @param LoggerInterface $logger
* @param bool $dryRun
*/
protected function doExecute(LoggerInterface $logger, $dryRun = false)
{
$duplicateEntitiesQuery = 'SELECT
DISTINCT t2.id
FROM
orocrm_campaign_email_stats AS t1
LEFT JOIN orocrm_campaign_email_stats AS t2
ON t1.email_campaign_id = t2.email_campaign_id
AND t1.marketing_list_item_id = t2.marketing_list_item_id
AND t2.id > t1.id
WHERE t2.id IS NOT NULL';
// Done in 2 queries for cross DB support.
$idsToRemove = array_map(function ($item) {
if (is_array($item) && array_key_exists('id', $item)) {
return $item['id'];
}
return null;
}, $this->connection->fetchAll($duplicateEntitiesQuery));
if ($idsToRemove) {
$query = 'DELETE FROM orocrm_campaign_email_stats WHERE id IN (?)';
$logger->notice($query);
if (!$dryRun) {
$this->connection->executeQuery($query, [$idsToRemove], [Connection::PARAM_INT_ARRAY]);
}
}
}
public function _before()
{
$this->connection->exec('DELETE FROM stations');
$loader = new YamlLoader(__DIR__ . '/../../data/fixtures/stations.yml');
$persister = new ConnectionPersister($this->connection);
$persister->persist($loader->load());
}
/**
* Creates a new <tt>Statement</tt> for the given SQL and <tt>Connection</tt>.
*
* @param string $sql The SQL of the statement.
* @param \Doctrine\DBAL\Connection $conn The connection on which the statement should be executed.
*/
public function __construct($sql, Connection $conn)
{
$this->sql = $sql;
$this->stmt = $conn->getWrappedConnection()->prepare($sql);
$this->conn = $conn;
$this->platform = $conn->getDatabasePlatform();
}
/**
* @param Backlog[] $backlogs
*/
private function writeBacklog(array $backlogs)
{
$statement = $this->connection->prepare("\n INSERT IGNORE INTO s_es_backlog (`event`, `payload`, `time`)\n VALUES (:event, :payload, :time);\n ");
foreach ($backlogs as $backlog) {
$statement->execute([':event' => $backlog->getEvent(), ':payload' => json_encode($backlog->getPayload()), ':time' => $backlog->getTime()->format('Y-m-d H:i:s')]);
}
}
protected function execute(InputInterface $input, OutputInterface $output)
{
$file = $input->getArgument('file');
if (!is_file($file)) {
throw new RuntimeException('File does not exists');
}
$verbose = $output->getVerbosity() > OutputInterface::VERBOSITY_NORMAL;
if (!$verbose) {
$this->logger->pushHandler(new NullHandler());
}
try {
$this->connection->beginTransaction();
$result = $this->importService->import(file_get_contents($file));
$this->connection->commit();
$output->writeln('Import successful!');
$output->writeln('The following actions were done:');
$output->writeln('');
foreach ($result as $message) {
$output->writeln('- ' . $message);
}
} catch (\Exception $e) {
$this->connection->rollback();
$output->writeln('An exception occured during import. No changes are applied to the database.');
$output->writeln('');
$output->writeln('Message: ' . $e->getMessage());
$output->writeln('Trace: ' . $e->getTraceAsString());
}
if (!$verbose) {
$this->logger->popHandler();
}
}
/**
* ->processQueueCallback(function (\Dja\Db\Model\Metadata $metadata, \Doctrine\DBAL\Schema\Table $table, array $sql, \Doctrine\DBAL\Connection $db) {})
* @param callable|\Closure $callBack
*/
public function processQueueCallback(\Closure $callBack)
{
$callbackQueue = [];
while (count($this->generateQueue)) {
$modelName = array_shift($this->generateQueue);
try {
/** @var Metadata $metadata */
$metadata = $modelName::metadata();
$tblName = $metadata->getDbTableName();
if ($this->db->getSchemaManager()->tablesExist($tblName)) {
continue;
}
if (isset($this->generated[$tblName])) {
continue;
}
$table = $this->metadataToTable($metadata);
$this->generated[$tblName] = 1;
$sql = $this->dp->getCreateTableSQL($table, AbstractPlatform::CREATE_INDEXES);
array_unshift($callbackQueue, [$metadata, $table, $sql]);
$fks = $table->getForeignKeys();
if (count($fks)) {
$sql = [];
foreach ($fks as $fk) {
$sql[] = $this->dp->getCreateForeignKeySQL($fk, $table);
}
array_push($callbackQueue, [$metadata, $table, $sql]);
}
} catch (\Exception $e) {
pr($e->__toString());
}
}
foreach ($callbackQueue as $args) {
$callBack($args[0], $args[1], $args[2], $this->db);
}
}
public function fetchAll()
{
$stmt = $this->connection->createQueryBuilder()->select('uri, data')->from($this->table)->execute();
foreach ($stmt as $item) {
(yield $item['uri'] => $this->prepareRowForRead($item['uri'], $item['data']));
}
}
/**
* Connects to the database.
*
* @return boolean
*/
public function connect()
{
if ($connected = $this->connection->connect()) {
$this->events->dispatch(Events::postConnect, new Event\ConnectionEvent($this));
}
return $connected;
}
/**
* setup configuration for Doctrine Dbal.
*
* @param array $db_config array config for override the default configuration.
*/
public function setupConfigurationDbal(array $db_config = [])
{
$dbal_config = new Configuration();
if (empty($db_config)) {
//setup connection configuration.
$config = new SystemConfig();
$config->load('db');
$db_params = $config->get('ALL', 'db');
unset($config, $db_params['table_prefix']);
} else {
$db_params = $db_config;
unset($db_params['table_prefix']);
}
$dbal_config->setSQLLogger(new \System\Libraries\Db\Logger());
try {
$this->Conn = DriverManager::getConnection($db_params, $dbal_config);
$this->Conn->connect();
} catch (\Doctrine\DBAL\DBALException $e) {
http_response_code(500);
echo $e->getMessage();
exit;
}
$this->Conn->setFetchMode(\PDO::FETCH_OBJ);
unset($dbal_config, $db_params);
}
/**
* @param string $todoId
* @return \stdClass of todoData
*/
public function findById($todoId)
{
$stmt = $this->connection->prepare(sprintf("SELECT * FROM %s where id = :todo_id", Table::TODO));
$stmt->bindValue('todo_id', $todoId);
$stmt->execute();
return $stmt->fetch();
}
