private function getTableNames() { $tableNames = []; foreach ($this->connection->fetchAll('SHOW TABLES') as $row) { $tableNames[] = $row['Tables_in_' . $this->connection->getDatabase()]; } return $tableNames; }
/** * 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()]]; }
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}"; }
/** * {@inheritdoc} */ public function shouldBeRun() { $schemaManager = $this->connection->getSchemaManager(); if (!$schemaManager->tablesExist('tl_member')) { return false; } $sql = $this->connection->getDatabasePlatform()->getListTableIndexesSQL('tl_member', $this->connection->getDatabase()); $index = $this->connection->fetchAssoc($sql . " AND INDEX_NAME = 'username'"); return '0' !== $index['Non_Unique']; }
protected function runQueriesFromFile($file) { $queries = array_filter(preg_split('(;\\s*$)m', file_get_contents($file))); if (!$this->output->isQuiet()) { $this->output->writeln(sprintf("Executing %d queries from %s on database %s", count($queries), $file, $this->db->getDatabase())); } foreach ($queries as $query) { $this->db->exec($query); } }
/** * @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>'; }
/** * Gets array database information * * @return array */ public function get_information() { $information = array(); $information['platform'] = $this->conn->getDatabasePlatform()->getName(); $information['database'] = $this->conn->getDatabase(); return $information; }
/** * List the foreign keys for the given table * * @param string $table The name of the table * @return ForeignKeyConstraint[] */ public function listTableForeignKeys($table, $database = null) { if (is_null($database)) { $database = $this->_conn->getDatabase(); } $sql = $this->_platform->getListTableForeignKeysSQL($table, $database); $tableForeignKeys = $this->_conn->fetchAll($sql); return $this->_getPortableTableForeignKeysList($tableForeignKeys); }
/** * Checks if the database is older than version 3.2. * * @return bool True if the database is older than version 3.2 */ public function hasOldDatabase() { if (!$this->hasTable('tl_layout')) { return false; } $sql = $this->connection->getDatabasePlatform()->getListTableColumnsSQL('tl_layout', $this->connection->getDatabase()); $column = $this->connection->fetchAssoc($sql . " AND COLUMN_NAME = 'sections'"); return 'varchar(1022)' !== $column['Type']; }
/** * Get table fields * * Info: $schemaManager->listTableColumns($this->tableName) doesn't work if fields are geometries! * * @throws \Doctrine\DBAL\DBALException * @return array field names */ public function getStoreFields() { $schemaManager = $this->connection->getDriver()->getSchemaManager($this->connection); $columns = array(); $sql = $schemaManager->getDatabasePlatform()->getListTableColumnsSQL($this->tableName, $this->connection->getDatabase()); foreach ($this->connection->fetchAll($sql) as $fieldInfo) { $columns[] = $fieldInfo["field"]; } return $columns; }
/** * @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>'; }
/** * Get meta details about a database table in a format known to ZenCart * * @param string $table table to get meta details for * @return array */ public function metaColumns($table) { $platform = $this->conn->getDatabasePlatform(); $dbName = $this->conn->getDatabase(); $sql = $platform->getListTableColumnsSQL($table, $dbName); $meta = array(); foreach ($this->conn->fetchAll($sql) as $column) { $columnInfo = new QueryFactoryMeta($column['Type']); $meta[$column['Field']] = $columnInfo; } return array_change_key_case($meta, CASE_UPPER); }
/** * @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>'; }
/** * From SQLLogger Doctrine Interface */ public function stopQuery() { $endTime = microtime(true) - $this->start; /** * TODO Improve Connection name * For now, $this->connection->getDatabase() return the name of the Database */ $this->registerQuery($this->query['sql'], $this->query['params'], $endTime, $this->connection->getDatabase()); /** * This call is helpful to register queries in log file when the application is in debug/local mode */ $this->logger->debug($this->query['sql'] . " [Executed in " . $endTime . " secs.] ", array('params' => $this->query['params'], 'types' => $this->query['types'])); }
private function checkDatabase() { try { if (!$this->configuredDatabaseExists()) { $this->output->writeln(sprintf("The configured database '%s' does not exist. Please run 'php app/console doctrine:database:create' to create the database first", $this->db->getDatabase())); exit(self::EXIT_DATABASE_NOT_FOUND_ERROR); } } catch (ConnectionException $e) { $this->output->writeln('An error occured connecting to the database:'); $this->output->writeln($e->getMessage()); $this->output->writeln('Please check the database configuration in parameters.yml'); exit(self::EXIT_GENERAL_DATABASE_ERROR); } }
private function checkDatabase() { try { if (!$this->configuredDatabaseExists()) { $this->output->writeln(sprintf("The configured database '%s' does not exist", $this->db->getDatabase())); exit(self::EXIT_DATABASE_NOT_FOUND_ERROR); } } catch (ConnectionException $e) { $this->output->writeln("An error occured connecting to the database:"); $this->output->writeln($e->getMessage()); $this->output->writeln("Please check the database configuration in parameters.yml"); exit(self::EXIT_GENERAL_DATABASE_ERROR); } }
/** * Returns the current database structure. * * @return array An array of tables and fields */ private function getFromDb() { $tables = $this->connection->fetchAll("SHOW TABLE STATUS LIKE 'tl_%'"); if (empty($tables)) { return []; } $return = []; foreach ($tables as $table) { $sql = $this->connection->getDatabasePlatform()->getListTableColumnsSQL($table['Name'], $this->connection->getDatabase()); $columns = $this->connection->fetchAll($sql); foreach ($columns as $column) { $field = ['name' => $this->quote($column['Field']), 'type' => $column['Type']]; if (isset($column['Collation']) && $column['Collation'] !== $table['Collation']) { $field['collation'] = 'COLLATE ' . $column['Collation']; } $field['null'] = 'YES' === $column['Null'] ? 'NULL' : 'NOT NULL'; if (!empty($column['Extra'])) { $field['extra'] = $column['Extra']; } if (isset($column['Default'])) { $field['default'] = 'default ' . $this->connection->quote($column['Default']); } $return[$table['Name']]['TABLE_FIELDS'][$column['Field']] = trim(implode(' ', $field)); } $sql = $this->connection->getDatabasePlatform()->getListTableIndexesSQL($table['Name'], $this->connection->getDatabase()); $tmp = []; $indexes = $this->connection->fetchAll($sql); foreach ($indexes as $index) { $name = $index['Key_name']; if (isset($tmp[$name])) { $tmp[$name]['columns'][] = $this->quoteColumn($index); continue; } if ('PRIMARY' === $name) { $tmp[$name]['key'] = 'PRIMARY KEY '; } elseif ('0' === $index['Non_Unique']) { $tmp[$name]['key'] = 'UNIQUE KEY ' . $this->quote($name); } else { $tmp[$name]['key'] = 'KEY ' . $this->quote($name); } $tmp[$name]['columns'] = [$this->quoteColumn($index)]; } foreach ($tmp as $name => $conf) { $return[$table['Name']]['TABLE_CREATE_DEFINITIONS'][$name] = $conf['key'] . ' (' . implode(', ', $conf['columns']) . ')'; } } return $return; }
/** * Return all tables as array * * @param string $strDatabase The database name * @param boolean $blnNoCache If true, the cache will be bypassed * * @return array An array of table names */ public function listTables($strDatabase = null, $blnNoCache = false) { if ($blnNoCache || !isset($this->arrCache[$strDatabase])) { $strOldDatabase = $this->resConnection->getDatabase(); // Change the database if ($strDatabase !== null && $strDatabase != $strOldDatabase) { $this->setDatabase($strDatabase); } $this->arrCache[$strDatabase] = $this->resConnection->getSchemaManager()->listTableNames(); // Restore the database if ($strDatabase !== null && $strDatabase != $strOldDatabase) { $this->setDatabase($strOldDatabase); } } return $this->arrCache[$strDatabase]; }
/** * This query works well with small offset, but if want to use it with large offsets please refer to the link on how to implement * http://www.scribd.com/doc/14683263/Efficient-Pagination-Using-MySQL * This will only check permissions on first enity added in the from clause, it will not check permissions * By default the number of rows returned are 10 starting from 0 * * @param Query $query Query * @param QueryBuilder $queryBuilder QueryBuilder * @param UserInterface $user User * * @return string */ private function getPermittedIdsACLSQLForUser(Query $query, QueryBuilder $queryBuilder, UserInterface $user = null) { $database = $this->aclConnection->getDatabase(); $mask = $query->getHint('acl.mask'); $rootEntities = $query->getHint('acl.root.entities'); foreach ($rootEntities as $rootEntity) { $rE[] = '"' . str_replace('\\', '\\\\', $rootEntity) . '"'; // For now ACL will be checked for first root entity, it will not check for all other entities in join etc.., break; } $rootEntities = implode(',', $rE); if (!is_object($user)) { $token = $this->securityContext->getToken(); // for now lets imagine we will have token i.e user is logged in $user = $token->getUser(); } $identifier = "''"; if (is_object($user)) { $identifiers = array(); $userRoles = $user->getRoles(); foreach ($userRoles as $role) { // The reason we ignore this is because by default FOSUserBundle adds ROLE_USER for every user if ($role !== 'ROLE_USER') { $identifiers[] = $role; } } $identifiers[] = str_replace('\\', '\\\\', get_class($user)) . '-' . $user->getUserName(); $identifier = '"' . implode('","', $identifiers) . '"'; } $isNullExpression = $this->aclConnection->getDatabasePlatform()->getIsNullExpression('e.object_identity_id'); $selectQuery = <<<SELECTQUERY SELECT DISTINCT o.object_identifier as id FROM {$database}.acl_object_identities as o INNER JOIN {$database}.acl_classes c ON c.id = o.class_id LEFT JOIN {$database}.acl_entries e ON ( e.class_id = o.class_id AND (e.object_identity_id = o.id OR {$isNullExpression}) ) LEFT JOIN {$database}.acl_security_identities s ON ( s.id = e.security_identity_id ) WHERE c.class_type = {$rootEntities} AND s.identifier IN ({$identifier}) AND e.mask >= {$mask} SELECTQUERY; return $selectQuery; }
private function getTemplatingDataFromSchema(TableSchema $schema) { $columnData = []; $fieldsPack = []; $defaultValuePack = []; $bindingsPack = []; /** @var Column $col */ foreach ($schema->getColumns() as $col) { $columnData[] = ['Field' => $col->getColumnName(), 'FieldCase' => ucfirst(\Parm\Row::columnToCamelCase($col->getColumnName())), 'AllCaps' => $this->fieldToAllCaps($col->getColumnName()), 'typeDate' => $col->isTypeDate(), 'typeDatetime' => $col->isTypeDateTime(), 'typeBoolean' => $col->isBoolean(), 'typeInt' => $col->isInteger(), 'typeNumeric' => $col->isNumeric(), 'typeString' => $col->isString(), 'isPrimaryKey' => $col->isPrimaryKey()]; $fieldsPack[] = ucfirst(\Parm\Row::columnToCamelCase($schema->getTableName())) . "Table::" . $this->fieldToAllCaps($col->getColumnName()) . "_COLUMN"; $defaultValuePack[] = ucfirst(\Parm\Row::columnToCamelCase($schema->getTableName())) . "Table::" . $this->fieldToAllCaps($col->getColumnName()) . "_COLUMN => " . ($col->getDefaultValue() != null ? $col->getDefaultValue() : "null"); $bindingsPack[] = "\tfinal function add" . ucfirst(\Parm\Row::columnToCamelCase($col->getColumnName())) . "TrueBinding() { \$this->addBinding(new \\Parm\\Binding\\TrueBooleanBinding('" . $schema->getTableName() . "." . $col->getColumnName() . "')); }"; $bindingsPack[] = "\tfinal function add" . ucfirst(\Parm\Row::columnToCamelCase($col->getColumnName())) . "FalseBinding() { \$this->addBinding(new \\Parm\\Binding\\FalseBooleanBinding('" . $schema->getTableName() . "." . $col->getColumnName() . "')); }"; $bindingsPack[] = "\tfinal function add" . ucfirst(\Parm\Row::columnToCamelCase($col->getColumnName())) . "NotTrueBinding() { \$this->addBinding(new \\Parm\\Binding\\NotEqualsBinding('" . $schema->getTableName() . "." . $col->getColumnName() . "',1)); }"; $bindingsPack[] = "\tfinal function add" . ucfirst(\Parm\Row::columnToCamelCase($col->getColumnName())) . "NotFalseBinding() { \$this->addBinding(new \\Parm\\Binding\\NotEqualsBinding('" . $schema->getTableName() . "." . $col->getColumnName() . "',0)); }"; $bindingsPack[] = "\n"; } return array('tableName' => $schema->getTableName(), 'variableName' => \Parm\Row::columnToCamelCase($schema->getTableName()), 'className' => ucfirst(\Parm\Row::columnToCamelCase($schema->getTableName())), 'databaseName' => $this->connection->getDatabase(), 'id' => ['exists' => $schema->getIdField() != null, 'columnName' => $schema->getIdField(), 'columnNameAllCaps' => $this->fieldToAllCaps($schema->getIdField()), 'isInteger' => $schema->isIdInteger(), 'isString' => $schema->isIdString(), 'isCalledId' => $schema->isIdCalledId(), 'isAutoIncremented' => $schema->isIdAutoIncremented()], 'namespace' => $this->generateToNamespace, 'autoloaderNamespace' => $this->generateToNamespace, 'namespaceClassSyntax' => $this->generateToNamespace != "" && $this->generateToNamespace != "\\" ? 'namespace ' . $this->generateToNamespace . ';' : '', 'namespaceLength' => strlen($this->generateToNamespace), 'columns' => $columnData, 'defaultValuePack' => implode(", ", $defaultValuePack), 'fieldList' => implode(", ", $fieldsPack), 'bindingsPack' => implode("\n", $bindingsPack)); }
/** * Using the db connection will build the * type composite ready to convert to xml * * @param Doctrine\DBAL\Connection * @param Faker\Components\Engine\XML\Builder\NodeBuilder $builder */ public function analyse(Connection $db, NodeBuilder $builder) { $sm = $db->getSchemaManager(); # add schema element $builder->addSchema($db->getDatabase(), array()); # add writer for the platform $builder->addWriter($db->getDatabasePlatform()->getName(), 'sql'); # iterate over the table $tables = $sm->listTables(); foreach ($tables as $table) { $builder->addTable($table->getName(), array('generate' => 0)); foreach ($table->getColumns() as $column) { $builder->addColumn($column->getName(), array('type' => $column->getType()->getName()))->addType('alphanumeric', array())->setTypeOption('format', 'ccccc')->end()->end(); } $builder->end(); } $builder->end(); return $builder->build(); }
/** * Check the migration of a table on a copy so we can detect errors before messing with the real table * * @param \Doctrine\DBAL\Schema\Table $table * @throws \OC\DB\MigrationException */ protected function checkTableMigrate(Table $table) { $name = $table->getName(); $tmpName = $this->generateTemporaryTableName($name); $this->copyTable($name, $tmpName); //create the migration schema for the temporary table $tmpTable = $this->renameTableSchema($table, $tmpName); $schemaConfig = new SchemaConfig(); $schemaConfig->setName($this->connection->getDatabase()); $schema = new Schema(array($tmpTable), array(), $schemaConfig); try { $this->applySchema($schema); $this->dropTable($tmpName); } catch (DBALException $e) { // pgsql needs to commit it's failed transaction before doing anything else $this->connection->commit(); $this->dropTable($tmpName); throw new MigrationException($table->getName(), $e->getMessage()); } }
/** * Returns table metadata for the provided table * * @param string $table * @return array column => accepts null (bool) */ public function getDbColumns($table) { $key = 'db_columns.' . $table; if ($this->cache->contains($key)) { return $this->cache->fetch($key); } else { $qb = $this->db->createQueryBuilder(); $database = $this->db->getDatabase(); $qb->select('COLUMN_NAME AS ' . $this->db->quoteIdentifier('COLUMN_NAME'), 'IS_NULLABLE AS ' . $this->db->quoteIdentifier('IS_NULLABLE'))->from('information_schema.COLUMNS')->where('TABLE_SCHEMA = ?')->setParameter(0, $database)->andWhere('TABLE_NAME = ?')->setParameter(1, $table); $dbColumnInfo = $qb->execute()->fetchAll(\PDO::FETCH_OBJ); if (empty($dbColumnInfo)) { throw new InvalidArgumentException("The table {$database}.{$table} does not exist"); } $dbColumns = []; foreach ($dbColumnInfo as $column) { $dbColumns[$column->COLUMN_NAME] = $column->IS_NULLABLE == 'YES' ? true : false; } $this->cache->save($key, $dbColumns); return $dbColumns; } }
/** * @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'); }
/** * Returns the schema for the connection. * * @return string */ public function getSchema() { return $this->_conn->getDatabase(); }
/** * The search path for namespaces in the currently connected database. * * The first entry is usually the default namespace in the Schema. All * further namespaces contain tables/sequences which can also be addressed * with a short, not full-qualified name. * * For databases that don't support subschema/namespaces this method * returns the name of the currently connected database. * * @return array */ public function getSchemaSearchPaths() { return array($this->_conn->getDatabase()); }
/** * Returns the name of the main database. * @return string */ public static function get_main_database() { return self::$db->getDatabase(); }
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; }
/** * From SQLLogger Doctrine Interface */ public function stopQuery() { $endTime = microtime(true) - $this->start; $this->registerQuery($this->query['sql'], $this->query['params'], $endTime, $this->connection->getDatabase()); }
private function getSchemaConfig() { $config = new SchemaConfig(); $config->setName($this->connection->getDatabase()); return $config; }