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);
}
