public function getDsn() { $driver = $this->connection->getDriver()->getName(); $user = $this->connection->getUsername(); $pass = $this->connection->getPassword(); $host = $this->connection->getHost(); $db = $this->connection->getDatabase(); return "{$driver}://{$user}:{$pass}@{$host}/{$db}"; }
/** * Returns the system information: * - cpu information * - memory size * - php version * - php accelerator info * - database related info. * * @return array */ public function getSystemInfo() { $info = ezcSystemInfo::getInstance(); $accelerator = false; if ($info->phpAccelerator) { $accelerator = ['name' => $info->phpAccelerator->name, 'url' => $info->phpAccelerator->url, 'enabled' => $info->phpAccelerator->isEnabled, 'versionString' => $info->phpAccelerator->versionString]; } return ['cpuType' => $info->cpuType, 'cpuSpeed' => $info->cpuSpeed, 'cpuCount' => $info->cpuCount, 'memorySize' => $info->memorySize, 'phpVersion' => phpversion(), 'phpAccelerator' => $accelerator, 'database' => ['type' => $this->connection->getDatabasePlatform()->getName(), 'name' => $this->connection->getDatabase(), 'host' => $this->connection->getHost(), 'username' => $this->connection->getUsername()]]; }
/** * @return string */ public function getPanel() { if (empty($this->queries)) { return ''; } return $this->renderStyles() . sprintf('<h1>Queries: %s %s, host: %s</h1>', count($this->queries), $this->totalTime ? ', time: ' . sprintf('%0.3f', $this->totalTime * 1000) . ' ms' : '', sprintf('%s%s/%s', $this->connection->getHost(), ($p = $this->connection->getPort()) ? ':' . $p : '', $this->connection->getDatabase())) . '<div class="nette-inner tracy-inner nette-Doctrine2Panel">' . implode('<br>', array_filter(array($this->renderPanelCacheStatistics(), $this->renderPanelQueries()))) . '</div>'; }
private function createConfigFile(OutputInterface $output) { $template = file_get_contents(__DIR__ . '/config.php.bak'); $result = str_replace(array('{{host}}', '{{database}}', '{{user}}', '{{password}}'), array($this->dbConnection->getHost(), $this->dbName, $this->dbConnection->getUsername(), $this->dbConnection->getPassword()), $template); $filename = __DIR__ . '/../../../../site/config.php'; file_put_contents($filename, $result); chmod($filename, 0644); $output->writeln('Create config file success with chmod 644'); }
/** * @return string */ public function getPanel() { if (empty($this->queries)) { return ""; } $s = ""; foreach ($this->queries as $query) { $s .= $this->processQuery($query); } $host = sprintf('%s%s/%s', $this->connection->getHost(), ($p = $this->connection->getPort()) ? ':' . $p : '', $this->connection->getDatabase()); return $this->renderStyles() . '<h1>Queries: ' . count($this->queries) . ($this->totalTime ? ', time: ' . sprintf('%0.3f', $this->totalTime * 1000) . ' ms' : '') . ', host: ' . $host . '</h1>' . '<div class="nette-inner tracy-inner nette-Doctrine2Panel">' . '<table><tr><th>ms</th><th>SQL Statement</th></tr>' . $s . '</table></div>'; }
/** * @return string */ public function getPanel() { if (empty($this->queries)) { return ''; } $connParams = $this->connection->getParams(); if ($connParams['driver'] === 'pdo_sqlite' && isset($connParams['path'])) { $host = 'path: ' . basename($connParams['path']); } else { $host = sprintf('host: %s%s/%s', $this->connection->getHost(), ($p = $this->connection->getPort()) ? ':' . $p : '', $this->connection->getDatabase()); } return $this->renderStyles() . sprintf('<h1>Queries: %s %s, %s</h1>', count($this->queries), $this->totalTime ? ', time: ' . sprintf('%0.3f', $this->totalTime * 1000) . ' ms' : '', $host) . '<div class="nette-inner tracy-inner nette-Doctrine2Panel">' . implode('<br>', array_filter([$this->renderPanelCacheStatistics(), $this->renderPanelQueries()])) . '</div>'; }
public function getHost() { if (empty($this->usedParams)) { return parent::getHost(); } else { return isset($this->usedParams['host']) ? $this->usedParams['host'] : null; } }
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; } $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([':ord' => $ord, ':host' => $host, ':port' => $port, ':dbname' => $dbname, ':user' => $user, ':password' => $password]); $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', 'default-dir'])); return $databox; }
/** * @todo make the mysql bit dynamic * @param Connection $connection */ private function formatDSN(Connection $connection) { $driver_name = $this->getPropelDriverName($connection->getDriver()); if ($connection->getDriver()->getName() == 'pdo_sqlite') { return sprintf('%s://hack.nl/%s', $driver_name, $connection->getDatabase()); } $params = $connection->getParams(); return sprintf('%s://%s:%s@%s%s/%s?encoding=%s', $driver_name, $connection->getUsername(), $connection->getPassword(), $connection->getHost(), $connection->getPort() ? ':' . $connection->getPort() : '', $connection->getDatabase(), isset($params['charset']) ? $params['charset'] : 'utf8'); }
/** * {@inheritdoc} */ public function getHost() { $params = $this->getParams(); return isset($params['host']) ? $params['host'] : parent::getHost(); }
/** * Collects information about the database eZ Platform is using. * - type * - name * - host * - username * * @return Value\DatabaseSystemInfo */ public function collect() { return new Value\DatabaseSystemInfo(['type' => $this->connection->getDatabasePlatform()->getName(), 'name' => $this->connection->getDatabase(), 'host' => $this->connection->getHost(), 'username' => $this->connection->getUsername()]); }
/** * Fill datatabase with geodata * * @param Connection $connection * * @return boolean */ public function loadGeoData($connection) { $which_output = ''; $which_ret = ''; @exec('which mysql', $which_output, $which_ret); if (is_array($which_output) && isset($which_output[0])) { $mysql_client_command = $which_output[0]; if (!$this->withMysqlAllIsOk($mysql_client_command)) { return false; } $last_dir = getcwd(); $work_dir = __DIR__ . '/../../../../install/Resources/sql'; chdir($work_dir); $db_host = $connection->getHost(); $db_port = $connection->getPort(); $db_user = $connection->getUsername(); $db_pass = $connection->getPassword(); $db_name = $connection->getDatabase(); $access_params = ''; $access_params .= ' -h ' . escapeshellarg($db_host); if (!empty($db_port)) { $access_params .= ' -P ' . escapeshellarg('' . $db_port); } $access_params .= ' -u ' . escapeshellarg($db_user); if (!empty($db_pass)) { $access_params .= ' -p' . escapeshellarg($db_pass); } $access_params .= ' -D ' . escapeshellarg($db_name); $cmd_string = escapeshellcmd($mysql_client_command) . $access_params . ' --local-infile=1 < ' . 'geonames.sql'; $cmd_output = array(); $cmd_retval = 0; exec($cmd_string, $cmd_output, $cmd_retval); chdir($last_dir); if (!empty($cmd_retval)) { return false; } return true; } }
private function getDBConn(InputInterface $input, OutputInterface $output, Connection $abConn, DialogHelper $dialog) { $dbConn = $template = null; if (!$input->getOption('databox')) { do { $retry = false; $dbName = $dialog->ask($output, 'DataBox name, will not be created if empty : ', null); if ($dbName) { try { $dbConn = $this->container['dbal.provider']->get(['host' => $abConn->getHost(), 'port' => $abConn->getPort(), 'user' => $abConn->getUsername(), 'password' => $abConn->getPassword(), 'dbname' => $dbName]); $dbConn->connect(); $output->writeln("\n\t<info>Data-Box : Connection successful !</info>\n"); do { $template = $dialog->ask($output, 'Choose a language template for metadata structure, available are fr (french) and en (english) (en) : ', 'en'); } while (!in_array($template, ['en', 'fr'])); $output->writeln("\n\tLanguage selected is <info>'{$template}'</info>\n"); } catch (\Exception $e) { $retry = true; } } else { $output->writeln("\n\tNo databox will be created\n"); } } while ($retry); } else { $dbConn = $this->container['dbal.provider']->get(['host' => $input->getOption('db-host'), 'port' => $input->getOption('db-port'), 'user' => $input->getOption('db-user'), 'password' => $input->getOption('db-password'), 'dbname' => $input->getOption('databox')]); $dbConn->connect(); $output->writeln("\n\t<info>Data-Box : Connection successful !</info>\n"); $template = $input->getOption('db-template') ?: 'en'; } return [$dbConn, $template]; }
/** * @param string $inputFile Input CSV file with table data. * @param bool $calculateDeltas Set to true to add timeDelta column * @param string $idColumn Name of column with primary key. * @param string $sortColumn Name of column by which data are sorted. * @param string $parentColumn Name of column with parent Id * @param string $outDirectory Directory in which the output file will be stored. * @throws DBALException */ public function process($inputFile, $calculateDeltas, $idColumn, $sortColumn, $parentColumn, $outDirectory) { $dataTypes = [$idColumn => 'VARCHAR(255) NOT NULL DEFAULT \'\'', $parentColumn => 'VARCHAR(255)', $sortColumn => 'VARCHAR(255)']; $csv = new CsvFile($inputFile); $header = $csv->getHeader(); $tableDefinition = $this->getTableDefinition('source', $dataTypes, $idColumn, $header); $this->db->query('DROP TABLE IF EXISTS `source`'); $this->db->query('DROP VIEW IF EXISTS `out.source`'); $this->db->query('DROP TABLE IF EXISTS `out.source`'); $this->db->query('DROP TABLE IF EXISTS `tmp.Fill`'); $this->db->query($tableDefinition); $loadQuery = ' LOAD DATA LOCAL INFILE \'' . str_replace('\\', '/', $inputFile) . '\' INTO TABLE `source` FIELDS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\' ESCAPED BY \'\' IGNORE 1 LINES;'; $this->db->query($loadQuery); // Alter columns $query = 'ALTER TABLE `source` CHANGE `' . $idColumn . '` `id` VARCHAR(255), CHANGE `' . $parentColumn . '` `parent` VARCHAR(255), CHANGE `' . $sortColumn . '` `sort` VARCHAR(255), ADD COLUMN `__root` VARCHAR(255), ADD COLUMN `__depth` INT(11) NOT NULL DEFAULT 0, ADD COLUMN `__tmpRoot` VARCHAR(255) NULL, ADD COLUMN `__position` VARCHAR(2000) NULL, ADD COLUMN `__position_relative` INT(11) NULL, ADD COLUMN `__position_depth` INT(11) NULL;'; $this->db->query($query); if ($calculateDeltas) { $query = ' ALTER TABLE `source` ADD COLUMN `__timestamp` INT(11) NOT NULL DEFAULT 0;'; $this->db->query($query); $query = ' UPDATE `source` SET `__timestamp` = UNIX_TIMESTAMP(`sort`);'; $this->db->query($query); } // Create indexes $query = 'ALTER TABLE `source` ADD KEY(`id`), ADD KEY(`parent`), ADD KEY(`id`, `parent`), ADD KEY(`sort`), ADD KEY(`__depth`), ADD KEY(`__position_depth`), ADD KEY(`__tmpRoot`)'; $this->db->query($query); // Detect Orphans (items with missing parents) and set them to null. $this->db->executeUpdate('UPDATE `source` t1 LEFT JOIN `source` t2 ON t1.`parent` = t2.`id` SET t1.`parent` = NULL WHERE t1.`parent` IS NOT NULL AND t2.`id` IS NULL;'); // Clean self referencing items $this->db->query('UPDATE `source` SET `parent` = NULL WHERE `parent` = `id`;'); // Set roots items where no parent is available $this->db->executeUpdate('UPDATE `source` SET `__root` = `id` WHERE `parent` IS NULL;'); // Set temporary root for all items (their direct parent) - tmpRoot will bubble up to real root. $this->db->query('UPDATE `source` SET `__tmpRoot` = `parent` WHERE `parent` IS NOT NULL;'); // Recursion - while there are any __tmpRoot items increase depth and set tmpRoot a level up $depth = 0; while ($this->db->executeUpdate('UPDATE `source` SET `__root` = `__tmpRoot` WHERE `__tmpRoot` IS NOT NULL;') > 0) { $depth++; $this->db->query('UPDATE `source` t1 JOIN `source` t2 ON t1.`__tmpRoot` = t2.`id` SET t1.`__tmpRoot` = t2.`parent`, t1.`__depth` = t1.`__depth` + 1 ;'); } // Table for creating position $this->db->query('CREATE TABLE `tmp.Fill` ( `id` VARCHAR(255), `__position` VARCHAR(2000), `__position_depth` INT(11), INDEX(`id`) );'); /* Create positions: For each level of depth there will be an increasing number like 00001 - easy to sort alphanumerically E.g. Record with depth level = 0 will get a 01234, a child item of this will be appended to it's parents position number, eg 01234 + 00001 => 0123400001 All lower depths are padded with zeros at the end, so the parent will look like 0123400000. Then this is sorted and inserted in a new table with and will get simple +1 increments. */ for ($i = 0; $i <= $depth; $i++) { // How long is the number of items in source table, used for numeric padding $this->db->query('SELECT @base := LENGTH(COUNT(*)) FROM `source`;'); $this->db->query('SELECT @increment := 0;'); $this->db->query('SELECT @depth := ' . $i . ';'); $this->db->query('TRUNCATE `tmp.Fill`;'); $this->db->query('INSERT INTO `tmp.Fill` SELECT t.`id`, CONCAT( IFNULL(parent.`__position`, \'\'), LPAD(@increment := @increment+1, @base, 0) ) AS `__position`, @increment AS `__position_depth` FROM `source` t LEFT JOIN `source` parent ON t.`parent` = parent.`id` WHERE t.`__depth` = @depth ORDER BY t.`sort` ASC;'); $this->db->query('UPDATE `source` t JOIN `tmp.Fill` f USING(`id`) SET t.`__position` = f.`__position`, t.`__position_depth` = f.`__position_depth` ;'); } $this->db->query('SELECT @increment := 0;'); $this->db->query('TRUNCATE `tmp.Fill`;'); // Flatten the position numbers $this->db->query('INSERT INTO `tmp.Fill` SELECT t.`id`, @increment := @increment+1 AS `__position`, 0 AS `__position_depth` FROM `source` t ORDER BY t.`__position` ASC;'); // And then back to source table $this->db->executeUpdate('UPDATE `source` t JOIN `tmp.Fill` f USING(`id`) SET t.`__position` = f.`__position` ;'); // Relative position $this->db->query('SELECT @increment := 0;'); $this->db->query('TRUNCATE `tmp.Fill`;'); $this->db->query('INSERT INTO `tmp.Fill` SELECT s.`id`, CAST(s.__position - r.__position AS SIGNED) AS `__position`, 0 AS `__position_depth` FROM `source` s LEFT JOIN `source` r ON s.`__root` = r.`id`;'); $this->db->query('UPDATE `source` t JOIN `tmp.Fill` f USING(`id`) SET t.`__position_relative` = f.`__position`;'); if ($calculateDeltas) { $this->db->query('ALTER TABLE source ADD INDEX `root_depth` (`__root`, `__depth`), ADD INDEX `root_relpos` (`__root`, `__position_relative`), ADD INDEX `root_depth_pos` (`__root`, `__depth`, `__position_depth`) ;'); $this->db->query('CREATE VIEW `out.source` AS SELECT source.`id` AS `' . $idColumn . '`, source.`__root` AS `root`, source.`__position` AS `position`, source.`__position_relative` AS `position_relative`, source.`__depth` AS `depth`, IF(source.`__timestamp` - root.`__timestamp` < 0, 0, source.`__timestamp` - root.`__timestamp`) AS `time_delta_runsum`, COALESCE( (source.`__timestamp` - previous.`__timestamp`), (source.`__timestamp` - previous_2.`__timestamp`), (source.`__timestamp` - previous_3.`__timestamp`), 0 ) AS `time_delta` FROM `source` source LEFT JOIN `source` root USE INDEX(`root_depth`) ON source.`__root` = root.`__root` AND root.`__depth` = 0 # Same level, direct previous LEFT JOIN `source` previous USE INDEX(`root_relpos`) ON source.`__root`= previous.`__root` AND previous.`__depth` = source.`__depth` AND previous.`__position_relative` = source.`__position_relative` - 1 # One level up, direct previous LEFT JOIN `source` previous_2 USE INDEX(`root_relpos`) ON source.`__root`= previous_2.`__root` AND previous_2.`__depth` = source.`__depth` - 1 AND previous_2.`__position_relative` = source.`__position_relative` - 1 # No direct previous, finding closest previous on the same level LEFT JOIN `source` previous_3 USE INDEX(`root_depth_pos`) ON source.`__root`= previous_3.`__root` AND previous_3.`__depth` = source.`__depth` AND previous_3.`__position_depth` = source.`__position_depth` - 1 ;'); } else { $this->db->query('CREATE VIEW `out.source` AS SELECT `id` AS `' . $idColumn . '`, `__root` AS `root`, `__position` AS `position`, `__position_relative` AS `position_relative`, `__depth` AS `depth` FROM `source`;'); } // Export Data $outFile = $outDirectory . DIRECTORY_SEPARATOR . 'destination.csv'; $command = 'mysql -u ' . $this->db->getUsername() . ' -p' . $this->db->getPassword() . ' -h ' . $this->db->getHost() . ' ' . $this->db->getDatabase() . ' --default-character-set=UTF8 --batch --execute ' . escapeshellarg('SELECT * FROM `out.source`;') . ' --quick | sed \'s/\\t/,/g\' > ' . $outFile; $process = new Process($command); $process->run(); if ($process->getExitCode() != 0) { $error = $process->getErrorOutput(); if (!$error) { $error = $process->getOutput(); } throw new DBALException('MySQL export error: ' . $error); } }
public function testGetHost() { $this->assertEquals('localhost', $this->_conn->getHost()); }
/** * Create the message we want to throw, if there was a connection error. * * @param Connection $connection The connection. * * @return string The message we want throw if there was a connection error. */ protected function createConnectionErrorMessage($connection) { $message = 'Not connected to database. dsn: ' . $connection->getDriver()->getName() . '://' . '****:****@' . $connection->getHost() . ':' . $connection->getPort() . '/' . $connection->getDatabase(); return $message; }
private function createConfigFile(Connection $abConn, $serverName, $binaryData) { $config = $this->app['configuration.store']->initialize()->getConfig(); $config['main']['database']['host'] = $abConn->getHost(); $config['main']['database']['port'] = $abConn->getPort(); $config['main']['database']['user'] = $abConn->getUsername(); $config['main']['database']['password'] = $abConn->getPassword(); $config['main']['database']['dbname'] = $abConn->getDatabase(); $config['main']['database']['driver'] = 'pdo_mysql'; $config['main']['database']['charset'] = 'UTF8'; $config['main']['binaries'] = $binaryData; $config['servername'] = $serverName; $config['main']['key'] = md5(mt_rand(100000000, 999999999)); $this->app['configuration.store']->setConfig($config); }