/** * Load config from db-adapter * * @param \Zend\Db\Adapter\Adapter $db * @return array */ protected function loadConfig() { if (empty($this->dbAdapter)) { return $this->config; } $platform = $this->dbAdapter->getPlatform(); $driver = $this->dbAdapter->getDriver(); $query = $this->dbAdapter->query(' SELECT ' . $platform->quoteIdentifier('value') . ' FROM ' . $platform->quoteIdentifier('settings') . ' WHERE ' . $platform->quoteIdentifier('key') . ' = ' . $platform->quoteValue('ini-cache') . ' AND ' . $platform->quoteIdentifier('type') . ' = ' . $platform->quoteValue('ini-cache') . ' LIMIT 1 '); $this->config = array(); $result = $query->execute(); if ($result->getAffectedRows() > 0) { foreach ($result as $cache) { $this->config = ArrayUtils::merge($this->config, (array) unserialize($cache['value'])); } } else { $query = $this->dbAdapter->query(' SELECT ' . $platform->quoteIdentifier('key') . ', ' . $platform->quoteIdentifier('value') . ' FROM ' . $platform->quoteIdentifier('settings') . ' WHERE ' . $platform->quoteIdentifier('type') . ' = ' . $platform->quoteValue('ini') . ' '); foreach ($query->execute() as $pair) { $key = (string) $pair['key']; $value = (string) $pair['value']; $entry = array(); $curr =& $entry; foreach (explode('.', $key) as $sub) { $curr[$sub] = null; $curr =& $curr[$sub]; } $curr = $value; $this->config = ArrayUtils::merge($this->config, $entry); } $query = $this->dbAdapter->query(' INSERT INTO ' . $platform->quoteIdentifier('settings') . ' ( ' . $platform->quoteIdentifier('key') . ', ' . $platform->quoteIdentifier('value') . ', ' . $platform->quoteIdentifier('type') . ' ) VALUES ( ' . $driver->formatParameterName('key') . ', ' . $driver->formatParameterName('value') . ', ' . $driver->formatParameterName('type') . ' ) '); $query->execute(array('key' => 'ini-cache', 'value' => serialize($this->config), 'type' => 'ini-cache')); } $this->dbAdapter = null; return $this->config; }
/** * Add table prefixed columns, will automatically * quote table parts identifiers found in the column name. * It provides an alternative for defining columns from multiple/joined * table in one go. * * <code> * $select->from(array('p' =>'product') * ->prefixedColumns(array('product_title' => 'p.title')); * </code> * Possible valid states: * array(value, ...) * value can be strings or Expression objects * * array(string => value, ...) * key string will be use as alias, * value can be string or Expression objects * * @throws Exception\InvalidArgumentException when usage not correct * @param array $columns * @return Select */ public function prefixedColumns(array $columns) { $pf = $this->adapter->getPlatform(); $identifierSeparator = $pf->getIdentifierSeparator(); $names = array(); $cols = array(); foreach ($columns as $alias => $column) { if (is_string($column)) { if (strpos($column, self::SQL_STAR) !== false) { $msg = __METHOD__ . " Invalid argument, prefixedColumn() does not accept sql * column specification"; throw new Exception\InvalidArgumentException($msg); } $parts = explode($identifierSeparator, $column); if (count($parts) > 1) { $quotedParts = array(); foreach ($parts as $part) { $quotedParts[] = $pf->quoteIdentifier($part); } // to remove PHPAnalyzer warnings //var_dump($quotedParts[count($quotedParts)-1]); //die(); $last_part = $parts[count($parts) - 1]; if (!is_string($alias)) { $alias = $last_part; } if (in_array($alias, $names)) { $msg = __METHOD__ . ": Invalid argument, multiple columns have the same alias ({$alias})"; throw new Exception\InvalidArgumentException($msg); } $names[] = $alias; $cols[$alias] = new Expression(join($identifierSeparator, $quotedParts)); } else { if (in_array($alias, $names)) { $msg = __METHOD__ . ": Invalid argument, multiple columns have the same alias ({$alias})"; throw new Exception\InvalidArgumentException($msg); } $cols[$alias] = $column; $names[] = $alias; } } else { if (in_array($alias, $names)) { $msg = __METHOD__ . ": Invalid argument, multiple columns have the same alias ({$alias})"; throw new Exception\InvalidArgumentException($msg); } $cols[$alias] = $column; $names[] = $alias; } } $this->columns($cols); return $this; }
/** * Load constraint references * * @param string $schema * @param string $database * @return type */ protected function loadConstraintReferences($schema, $database) { /** @var $platform \Zend\Db\Adapter\PlatformInterface */ $platform = $this->adapter->getPlatform(); $quoteIdentifierForWalk = function (&$c) use($platform) { $c = $platform->quoteIdentifierInFragment($c); }; $quoteSelectList = function (array $identifierList) use($platform, $quoteIdentifierForWalk) { array_walk($identifierList, $quoteIdentifierForWalk); return implode(', ', $identifierList); }; // target: CONSTRAINT_SCHEMA, CONSTRAINT_NAME, UPDATE_RULE, DELETE_RULE, REFERENCE_CONSTRAINT_NAME if ($platform->getName() == 'MySQL') { $sql = 'SELECT ' . $quoteSelectList(array('RC.CONSTRAINT_NAME', 'RC.UPDATE_RULE', 'RC.DELETE_RULE', 'RC.TABLE_NAME', 'CK.REFERENCED_TABLE_NAME', 'CK.REFERENCED_COLUMN_NAME')) . ' FROM ' . $platform->quoteIdentifierInFragment('INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC') . ' INNER JOIN ' . $platform->quoteIdentifierInFragment('INFORMATION_SCHEMA.KEY_COLUMN_USAGE CK') . ' ON ' . $platform->quoteIdentifierInFragment('RC.CONSTRAINT_NAME') . ' = ' . $platform->quoteIdentifierInFragment('CK.CONSTRAINT_NAME'); } else { $sql = 'SELECT ' . $quoteSelectList(array('RC.CONSTRAINT_NAME', 'RC.UPDATE_RULE', 'RC.DELETE_RULE', 'TC1.TABLE_NAME', 'CK.TABLE_NAME AS REFERENCED_TABLE_NAME', 'CK.COLUMN_NAME AS REFERENCED_COLUMN_NAME')) . ' FROM ' . $platform->quoteIdentifierInFragment('INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC') . ' INNER JOIN ' . $platform->quoteIdentifierInFragment('INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC1') . ' ON ' . $platform->quoteIdentifierInFragment('RC.CONSTRAINT_NAME') . ' = ' . $platform->quoteIdentifierInFragment('TC1.CONSTRAINT_NAME') . ' INNER JOIN ' . $platform->quoteIdentifierInFragment('INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC2') . ' ON ' . $platform->quoteIdentifierInFragment('RC.UNIQUE_CONSTRAINT_NAME') . ' = ' . $platform->quoteIdentifierInFragment('TC2.CONSTRAINT_NAME') . ' INNER JOIN ' . $platform->quoteIdentifierInFragment('INFORMATION_SCHEMA.KEY_COLUMN_USAGE CK') . ' ON ' . $platform->quoteIdentifierInFragment('TC2.CONSTRAINT_NAME') . ' = ' . $platform->quoteIdentifierInFragment('CK.CONSTRAINT_NAME'); } if ($schema != '__DEFAULT_SCHEMA__') { $sql .= ' AND ' . $platform->quoteIdentifierInFragment('RC.CONSTRAINT_SCHEMA') . ' = ' . $platform->quoteValue($schema); } $results = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE); $constraintRefData = array(); foreach ($results->toArray() as $row) { $constraintRefData[] = array_change_key_case($row, CASE_LOWER); } $this->prepareDataHeirarchy($database, $schema, array('constraints', 'keys')); $this->data[$database][$schema]['constraints']['references'] = $constraintRefData; }
/** * Drops a stream table * * Use this function with caution. All your events will be lost! But it can be useful in migration scenarios. * * @param StreamName $streamName * @param bool $returnSql * @return string|null Whether $returnSql is true or not function will return generated sql or execute it directly */ public function dropSchemaFor(StreamName $streamName, $returnSql = false) { $dropTable = new DropTable($this->getTable($streamName)); if ($returnSql) { return $dropTable->getSqlString($this->dbAdapter->getPlatform()); } $this->dbAdapter->getDriver()->getConnection()->execute($dropTable->getSqlString($this->dbAdapter->getPlatform())); }
/** * Create source from adapter * * @param Adapter $adapter * @return Source\InformationSchemaMetadata */ protected function createSourceFromAdapter(Adapter $adapter) { switch ($adapter->getPlatform()->getName()) { case 'MySQL': case 'SQLServer': return new Source\InformationSchemaMetadata($adapter); case 'SQLite': return new Source\SqliteMetadata($adapter); } throw new \Exception('cannot create source from adapter'); }
/** * (non-PHPdoc) * * @see \VisioCrudModeler\Descriptor\AbstractDataSourceDescriptor::getName() */ public function getName() { if (is_null($this->name)) { if ($this->adapter->getPlatform() instanceof \Zend\Db\Adapter\Platform\Mysql) { $this->name = $this->currentDatabaseMysql(); } else { throw new \RuntimeException('Automatic database name resolving is not supported for platform: ' . get_class($this->adapter->getPlatform())); } } return $this->name; }
/** * _authenticateCreateSelect() - This method creates a Zend\Db\Sql\Select object that * is completely configured to be queried against the database. * * @return DbSelect */ protected function _authenticateCreateSelect() { // build credential expression if (empty($this->credentialTreatment) || strpos($this->credentialTreatment, '?') === false) { $this->credentialTreatment = '?'; } $credentialExpression = new Expression('(CASE WHEN ' . $this->zendDb->getPlatform()->quoteIdentifier($this->credentialColumn) . ' = ' . $this->credentialTreatment . ' THEN 1 ELSE 0 END) AS ' . $this->zendDb->getPlatform()->quoteIdentifier('zend_auth_credential_match')); // get select $dbSelect = clone $this->getDbSelect(); $dbSelect->from($this->tableName)->columns(array('*', $credentialExpression))->where($this->zendDb->getPlatform()->quoteIdentifier($this->identityColumn) . ' = ?'); return $dbSelect; }
/** * Sets the DB adapter. * * @param Adapter $dbAdapter */ public function setAdapter(Adapter $dbAdapter) { $this->dbAdapter = $dbAdapter; /* * temp fix */ $driver = $this->dbAdapter->getDriver(); $driver->getConnection()->connect(); $this->dbAdapter->getPlatform()->setDriver($driver); /* --- */ $this->setSqlFromAdapter($dbAdapter); }
/** * Automatically create source from adapter * * @throws Exception\UnsupportedDriverException * @param \Zend\Db\Adapter\Adapter $adapter * @param string $schema database schema to use or null to current schema defined by the adapter * @return \Soluble\Db\Metadata\Source\AbstractSource */ protected function createSourceFromAdapter(Adapter $adapter, $schema = null) { $adapter_name = strtolower($adapter->getPlatform()->getName()); switch ($adapter_name) { case 'mysql': $source = new Source\Mysql\InformationSchema($adapter, $schema); break; default: throw new Exception\UnsupportedDriverException("Currently only MySQL is supported, driver set '{$adapter_name}'"); } return $source; }
public function __construct(Adapter $adapter) { $this->adapter = $adapter; $platform = $adapter->getPlatform(); switch (strtolower($platform->getName())) { case 'sqlserver': $platform = new SqlServer\SqlServer(); $this->decorators = $platform->decorators; break; default: } }
/** * Create db adapter service * * @param ServiceLocatorInterface $serviceLocator * @return Adapter */ public function createService(ServiceLocatorInterface $serviceLocator) { $config = $serviceLocator->get('Config'); $result = new Adapter($config['db']); if (defined('SCPPER_DEBUG')) { $profiler = new \Zend\Db\Adapter\Profiler\Profiler(); $result->setProfiler($profiler); if ($result->getPlatform()->getName() === 'MySQL') { $result->query('SET SESSION query_cache_type=0;', Adapter::QUERY_MODE_EXECUTE); } } return $result; }
/** * Set the db adapter options * @param array $options */ protected function setOptions(array $options) { if (!array_key_exists('adapter', $options) || !array_key_exists('table', $options) || !array_key_exists('column_key', $options) || !array_key_exists('column_value', $options)) { throw new Exception\InvalidArgumentException('Db adapter options must be defined "adapter", "table", "column_key" and "column_value" keys.'); } if (!$options['adapter'] instanceof Adapter) { throw new Exception\InvalidArgumentException('Db adapter must be an instance of Zend\\Db\\Adapter\\Adapter.'); } $this->adapter = $options['adapter']; $options['table'] = $this->adapter->getPlatform()->quoteIdentifier($options['table']); $options['column_key'] = $this->adapter->getPlatform()->quoteIdentifier($options['column_key']); $options['column_value'] = $this->adapter->getPlatform()->quoteIdentifier($options['column_value']); $this->options = $options; return $this; }
protected function createSourceFromAdapter(Adapter $adapter) { switch ($adapter->getPlatform()->getName()) { case 'MySQL': return new Source\MysqlMetadata($adapter); case 'SQLServer': return new Source\SqlServerMetadata($adapter); case 'SQLite': return new Source\SqliteMetadata($adapter); case 'PostgreSQL': return new Source\PostgresqlMetadata($adapter); case 'Oracle': return new Source\OracleMetadata($adapter); } throw new \Exception('cannot create source from adapter'); }
/** * Setup services which depends on the db * * @param \Zend\Db\Adapter\Adapter $db * @return \Zend\Db\Adapter\Adapter */ public function configure(DbAdapter $db) { $sm = $this->getServiceLocator(); $platform = $db->getPlatform(); $driver = $db->getDriver(); $domain = strtolower($this->getDomain()); $query = $db->query(' SELECT * FROM ' . $platform->quoteIdentifier('_central') . '.' . $platform->quoteIdentifier('fulldomain') . ' WHERE LOWER( ' . $platform->quoteIdentifier('fulldomain') . ' ) = LOWER( ' . $driver->formatParameterName('fulldomain') . ' ) '); $result = $query->execute(array('fulldomain' => $domain)); if ($result->getAffectedRows() > 0) { foreach ($result as $data) { $info = new SiteInfo(array_merge($data, array('scheme' => $this->getScheme(), 'port' => $this->getPort()))); $sm->setService('SiteInfo', $info); $driver->getConnection()->setCurrentSchema($info->getSchema()); return $db; } } else { $parts = explode('.', $domain); $subParts = array(); $mainDomain = false; while (count($parts) > 2) { $subParts[] = array_shift($parts); $mainDomain = implode('.', $parts); $result = $query->execute(array('fulldomain' => $mainDomain)); if ($result->getAffectedRows() > 0) { break; } $mainDomain = false; } if ($mainDomain) { $sm->setService('RedirectToDomain', new RedirectionService($this->getScheme(), $mainDomain, $this->getPort(), sprintf('sub-domain "%s" not found', implode('.', $subParts)), true)); } else { $config = $driver->getConnection()->getConnectionParameters(); if (empty($config['defaultDomain'])) { throw new Exception\InvalidArgumentException('Domain not found, and default domain not set'); } else { $sm->setService('RedirectToDomain', new RedirectionService($this->getScheme(), $config['defaultDomain'], $this->getPort(), 'domain not found', false)); } } } return $db; }
/** * Create source from adapter * * @param Adapter $adapter * @return MetadataInterface * @throws InvalidArgumentException If adapter platform name not recognized. */ public static function createSourceFromAdapter(Adapter $adapter) { $platformName = $adapter->getPlatform()->getName(); switch ($platformName) { case 'MySQL': return new MysqlMetadata($adapter); case 'SQLServer': return new SqlServerMetadata($adapter); case 'SQLite': return new SqliteMetadata($adapter); case 'PostgreSQL': return new PostgresqlMetadata($adapter); case 'Oracle': return new OracleMetadata($adapter); default: throw new InvalidArgumentException("Unknown adapter platform '{$platformName}'"); } }
/** * Prepare statement * * @param \Zend\Db\Adapter\Adapter $adapter * @param \Zend\Db\Adapter\Driver\StatementInterface $statement * @return void */ public function prepareStatement(Adapter $adapter, StatementInterface $statement) { $driver = $adapter->getDriver(); $platform = $adapter->getPlatform(); $parameterContainer = $statement->getParameterContainer(); $prepareType = $driver->getPrepareType(); $parts = parent::getWhereParts(); $wherePart = ''; $whereParamIndex = 1; foreach ($parts as $part) { if (is_string($part)) { $wherePart .= $part; } elseif (is_array($part)) { $values = $part[1]; $types = isset($part[2]) ? $part[2] : array(); foreach ($values as $vIndex => $value) { if (isset($types[$vIndex]) && $types[$vIndex] == self::TYPE_IDENTIFIER) { $values[$vIndex] = $platform->quoteIdentifierInFragment($value); } elseif (isset($types[$vIndex]) && $types[$vIndex] == self::TYPE_VALUE) { if ($prepareType == 'positional') { $parameterContainer->offsetSet(null, $value); $values[$vIndex] = $driver->formatParameterName(null); } elseif ($prepareType == 'named') { $name = 'where' . $whereParamIndex++; $values[$vIndex] = $driver->formatParameterName($name); $parameterContainer->offsetSet($name, $value); } } } $wherePart .= vsprintf($part[0], $values); } } $sql = $statement->getSql(); $sql .= sprintf($this->specification, $wherePart); $statement->setSql($sql); }
protected function setUp() { parent::setUp(); $driverConfig = ['driver' => getenv('db_type'), 'database' => str_replace('%BASE_DIR%', __DIR__ . '/../../../', getenv('db_name')), 'username' => getenv('db_username'), 'password' => getenv('db_password'), 'hostname' => getenv('db_host'), 'port' => getenv('db_port'), 'options' => ['buffer_results' => true]]; $config = ['dir' => __DIR__ . '/../data/ApplyMigration', 'namespace' => 'ApplyMigration']; $this->adapter = $adapter = new Adapter($driverConfig); $metadata = new Metadata($adapter); $tableNames = $metadata->getTableNames(); $drop_if_exists = ['test', MigrationVersion::TABLE_NAME]; foreach ($drop_if_exists as $table) { if (in_array($table, $tableNames)) { // ensure db is in expected state $drop = new DropTable($table); $adapter->query($drop->getSqlString($adapter->getPlatform())); } } /** @var ArrayObject $version */ $version = new MigrationVersion(); $resultSetPrototype = new ResultSet(); $resultSetPrototype->setArrayObjectPrototype($version); $gateway = new TableGateway(MigrationVersion::TABLE_NAME, $adapter, null, $resultSetPrototype); $table = new MigrationVersionTable($gateway); $this->migration = new Migration($adapter, $config, $table); }
/** * Prepare statement * * @param \Zend\Db\Adapter\Adapter $adapter * @param \Zend\Db\Adapter\Driver\StatementInterface $statement * @return void */ public function prepareStatement(Adapter $adapter, StatementInterface $statement) { $driver = $adapter->getDriver(); $platform = $adapter->getPlatform(); $parameterContainer = $statement->getParameterContainer(); if (!$parameterContainer instanceof ParameterContainer) { $parameterContainer = new ParameterContainer(); $statement->setParameterContainer($parameterContainer); } $prepareType = $driver->getPrepareType(); $table = $platform->quoteIdentifier($this->table); // if ($this->schema != '') { // $table = $platform->quoteIdentifier($this->schema) // . $platform->getIdentifierSeparator() // . $table; // } $set = $this->set; if (is_array($set)) { $setSql = array(); $values = array(); foreach ($set as $column => $value) { $parameterContainer->offsetSet($column, $value); $setSql[] = $platform->quoteIdentifier($column) . ' = ' . $driver->formatParameterName($column); } $set = implode(', ', $setSql); } $sql = sprintf($this->specifications[self::SPECIFICATION_UPDATE], $table, $set); // process where if ($this->where->count() > 0) { $whereParts = $this->processExpression($this->where, $platform, $adapter->getDriver(), 'where'); if (count($whereParts['parameters']) > 0) { $parameterContainer->merge($whereParts['parameters']); } $sql .= ' ' . sprintf($this->specifications[self::SPECIFICATION_WHERE], $whereParts['sql']); } $statement->setSql($sql); }
/** * Drop temporary db & restore params */ public function tearDown() { parent::tearDown(); $sm = $this->getApplication()->getServiceManager(); \Zork\ServiceManager\ServiceManager::unregisterServices($sm); $sm = null; $config = $this->getApplicationConfig(); $this->reset(); $this->gc(); if (!empty($config['db']) && $this->originalDbName) { $config['db']['dbname'] = $this->originalDbName; $db = new DbAdapter($config['db']); $platform = $db->getPlatform(); $db->query(sprintf('DROP DATABASE %s', $platform->quoteIdentifier($this->temporaryDbName)), DbAdapter::QUERY_MODE_EXECUTE); $platform = null; $db = null; $this->gc(); } chdir($this->orginalCwd); }
/** * {@inheritdoc} */ public function quoteValue($value) { return $this->zendAdapter->getPlatform()->quoteValue($value); }
/** * Prepare statement * * @param \Zend\Db\Adapter\Adapter $adapter * @param \Zend\Db\Adapter\Driver\StatementInterface $statementContainer * @return void */ public function prepareStatement(Adapter $adapter, StatementContainerInterface $statementContainer) { $driver = $adapter->getDriver(); $platform = $adapter->getPlatform(); $parameterContainer = $statementContainer->getParameterContainer(); if (!$parameterContainer instanceof ParameterContainer) { $parameterContainer = new ParameterContainer(); $statementContainer->setParameterContainer($parameterContainer); } $table = $platform->quoteIdentifier($this->table); $set = $this->set; if (is_array($set)) { $setSql = array(); foreach ($set as $column => $value) { if ($value instanceof Expression) { $exprData = $this->processExpression($value, $platform, $adapter); $setSql[] = $platform->quoteIdentifier($column) . ' = ' . $exprData->getSql(); $parameterContainer->merge($exprData->getParameterContainer()); } else { $setSql[] = $platform->quoteIdentifier($column) . ' = ' . $driver->formatParameterName($column); $parameterContainer->offsetSet($column, $value); } } $set = implode(', ', $setSql); } $sql = sprintf($this->specifications[self::SPECIFICATION_UPDATE], $table, $set); // process where if ($this->where->count() > 0) { $whereParts = $this->processExpression($this->where, $platform, $adapter, 'where'); $parameterContainer->merge($whereParts->getParameterContainer()); $sql .= ' ' . sprintf($this->specifications[self::SPECIFICATION_WHERE], $whereParts->getSql()); } $statementContainer->setSql($sql); }
/** * @testdox unit test: Test getPlatform() returns platform object * @covers Zend\Db\Adapter\Adapter::getPlatform */ public function testGetPlatform() { $this->assertSame($this->mockPlatform, $this->adapter->getPlatform()); }
/** * Prepare the delete statement * * @param Adapter $adapter * @param StatementInterface $statement * @return void */ public function prepareStatement(Adapter $adapter, StatementInterface $statement) { $platform = $adapter->getPlatform(); $parameterContainer = $statement->getParameterContainer(); if (!$parameterContainer instanceof ParameterContainer) { $parameterContainer = new ParameterContainer(); $statement->setParameterContainer($parameterContainer); } $table = $platform->quoteIdentifier($this->table); $sql = sprintf($this->specifications[self::SPECIFICATION_DELETE], $table); // process where if ($this->where->count() > 0) { $whereParts = $this->processExpression($this->where, $platform, $adapter->getDriver(), 'where'); if (count($whereParts['parameters']) > 0) { $parameterContainer->merge($whereParts['parameters']); } $sql .= ' ' . sprintf($this->specifications[self::SPECIFICATION_WHERE], $whereParts['sql']); } $statement->setSql($sql); }
/** * Prepare the delete statement * * @param Adapter $adapter * @param StatementInterface $statement * @return void */ public function prepareStatement(Adapter $adapter, StatementInterface $statement) { $driver = $adapter->getDriver(); $platform = $adapter->getPlatform(); $table = $platform->quoteIdentifier($this->table); if ($this->databaseOrSchema != '') { $table = $platform->quoteIdentifier($this->databaseOrSchema) . $platform->getIdentifierSeparator() . $table; } $sql = sprintf($this->specification, $table); $statement->setSql($sql); $this->where->prepareStatement($adapter, $statement); }
/** * Return object (table/schema) configuration * * @throws Exception\ErrorException * * @param string $table * @param boolean $include_options * @return array */ protected function getObjectConfig($table = null, $include_options = false) { $schema = $this->schema; $qSchema = $this->adapter->getPlatform()->quoteValue($schema); if ($table !== null) { $qTable = $this->adapter->getPlatform()->quoteValue($table); $table_clause = "and (t.TABLE_NAME = {$qTable} or (kcu.referenced_table_name = {$qTable} and kcu.constraint_name = 'FOREIGN KEY'))"; $table_join_condition = "(t.table_name = kcu.table_name or kcu.referenced_table_name = t.table_name)"; } else { $table_join_condition = "t.table_name = kcu.table_name"; $table_clause = ''; } $query = "\n\n SELECT\n t.table_name,\n c.column_name,\n c.data_type,\n c.column_type,\n\n c.extra,\n\n tc.constraint_type,\n kcu.constraint_name,\n kcu.referenced_table_name,\n kcu.referenced_column_name,\n\n c.column_default,\n c.is_nullable,\n c.numeric_precision,\n c.numeric_scale,\n c.character_octet_length,\n c.character_maximum_length,\n c.ordinal_position,\n\n c.column_key, -- UNI/MUL/PRI\n c.character_set_name,\n\n\n c.collation_name,\n\n c.column_comment,\n\n t.table_type,\n t.engine,\n t.table_comment,\n t.table_collation\n\n FROM `INFORMATION_SCHEMA`.`COLUMNS` c\n INNER JOIN `INFORMATION_SCHEMA`.`TABLES` t on c.TABLE_NAME = t.TABLE_NAME\n LEFT JOIN `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` kcu\n on (\n {$table_join_condition}\n and kcu.table_schema = t.table_schema\n and kcu.column_name = c.column_name\n )\n LEFT JOIN\n `INFORMATION_SCHEMA`.`TABLE_CONSTRAINTS` tc\n on (\n t.table_name = tc.table_name\n and tc.table_schema = t.table_schema\n and tc.constraint_name = kcu.constraint_name\n )\n\n\n where c.TABLE_SCHEMA = {$qSchema}\n and t.TABLE_SCHEMA = {$qSchema}\n {$table_clause}\n and (kcu.table_schema = {$qSchema} or kcu.table_schema is null)\n\n and (kcu.column_name = c.column_name or kcu.column_name is null)\n order by t.table_name, c.ordinal_position\n "; $this->disableInnoDbStats(); try { $results = $this->adapter->query($query, Adapter::QUERY_MODE_EXECUTE); } catch (\Exception $e) { //@codeCoverageIgnoreStart $this->restoreInnoDbStats(); throw new Exception\ErrorException(__METHOD__ . ": " . $e->getMessage()); //@codeCoverageIgnoreEnd } $this->restoreInnoDbStats(); $references = array(); $config = new Config(array('tables' => array()), true); $tables = $config->offsetGet('tables'); foreach ($results as $r) { // Setting table information $table_name = $r['table_name']; if (!$tables->offsetExists($table_name)) { $table_def = array('name' => $table_name, 'columns' => array(), 'primary_keys' => array(), 'unique_keys' => array(), 'foreign_keys' => array(), 'references' => array(), 'indexes' => array()); if ($include_options) { $table_def['options'] = array('comment' => $r['table_comment'], 'collation' => $r['table_collation'], 'type' => $r['table_type'], 'engine' => $r['engine']); } $tables->offsetSet($table_name, $table_def); } $table = $tables->offsetGet($table_name); $columns = $table->columns; $column_name = $r['column_name']; $data_type = strtolower($r['data_type']); $col_def = array('type' => $data_type, 'primary' => $r['constraint_type'] == 'PRIMARY KEY', 'nullable' => $r['is_nullable'] == 'YES', 'default' => $r['column_default']); if ($r['constraint_type'] == 'PRIMARY KEY') { $col_def['primary'] = true; $col_def['autoincrement'] = $r['extra'] == 'auto_increment'; } $has_charset = false; if (in_array($data_type, array('int', 'tinyint', 'mediumint', 'bigint', 'int', 'smallint', 'year'))) { $col_def['unsigned'] = (bool) preg_match('/unsigned/', strtolower($r['column_type'])); $col_def['precision'] = $r['numeric_precision']; } elseif (in_array($data_type, array('real', 'double precision', 'decimal', 'numeric', 'float', 'dec', 'fixed'))) { $col_def['precision'] = $r['numeric_precision']; $col_def['scale'] = $r['numeric_scale']; } elseif (in_array($data_type, array('timestamp', 'date', 'time', 'datetime'))) { // nothing yet } elseif (in_array($data_type, array('char', 'varchar', 'binary', 'varbinary', 'text', 'tinytext', 'mediumtext', 'longtext'))) { $col_def['octet_length'] = $r['character_octet_length']; $col_def['length'] = $r['character_maximum_length']; $has_charset = true; } elseif (in_array($data_type, array('blob', 'tinyblob', 'mediumblob', 'longblob'))) { $col_def['octet_length'] = $r['character_octet_length']; $col_def['length'] = $r['character_maximum_length']; } elseif (in_array($data_type, array('enum', 'set'))) { $col_def['octet_length'] = $r['character_octet_length']; $col_def['length'] = $r['character_maximum_length']; $def = $r['column_type']; preg_match_all("/'([^']+)'/", $def, $matches); if (is_array($matches[1]) && count($matches) > 0) { $col_def['values'] = $matches[1]; } } if ($include_options) { $col_def['options'] = array('comment' => $r['column_comment'], 'definition' => $r['column_type'], 'column_key' => $r['column_key'], 'ordinal_position' => $r['ordinal_position'], 'constraint_type' => $r['constraint_type']); if ($has_charset) { $col_def['options']['charset'] = $r['character_set_name']; $col_def['options']['collation'] = $r['collation_name']; } } $columns[$column_name] = $col_def; $foreign_keys = $table->foreign_keys; $unique_keys = $table->unique_keys; $constraint_name = $r['constraint_name']; $referenced_table_name = $r['referenced_table_name']; $referenced_column_name = $r['referenced_column_name']; switch ($r['constraint_type']) { case 'PRIMARY KEY': $table->primary_keys = array_merge($table->primary_keys->toArray(), (array) $column_name); break; case 'UNIQUE': if (!$unique_keys->offsetExists($constraint_name)) { $unique_keys[$constraint_name] = array(); } $unique_keys[$constraint_name] = array_merge($unique_keys[$constraint_name]->toArray(), (array) $column_name); break; case 'FOREIGN KEY': /* if (!$foreign_keys->offsetExists($constraint_name)) { $foreign_keys[$constraint_name] = array(); } * */ $fk = array('referenced_table' => $referenced_table_name, 'referenced_column' => $referenced_column_name, 'constraint_name' => $constraint_name); $foreign_keys[$column_name] = $fk; //$table->references[$referenced_table_name] = array($column_name => $r['referenced_column_name']); if (!array_key_exists($referenced_table_name, $references)) { $references[$referenced_table_name] = array(); } $references[$referenced_table_name][] = array('column' => $column_name, 'referenced_column' => $referenced_column_name, 'constraint_name' => $constraint_name); break; } } foreach ($references as $referenced_table_name => $refs) { if ($tables->offsetExists($referenced_table_name)) { $table = $tables[$referenced_table_name]; $references = $table->references; $references[$referenced_table_name] = $refs; } } $array = $config->toArray(); unset($config); return $array; }
/** * Setup services which depends on the db * * @param \Zend\Db\Adapter\Adapter $db * @return \Zend\Db\Adapter\Adapter */ public function configure(DbAdapter $db) { $sm = $this->getServiceLocator(); $platform = $db->getPlatform(); $driver = $db->getDriver(); $matches = array(); $fulldomain = strtolower($this->getDomain()); $config = $driver->getConnection()->getConnectionParameters(); /// TODO: remove this if (isset($_SERVER['GRIDGUYZ_DOMAIN'])) { $domain = $_SERVER['GRIDGUYZ_DOMAIN']; $subdomain = isset($_SERVER['GRIDGUYZ_SUBDOMAIN']) ? $_SERVER['GRIDGUYZ_SUBDOMAIN'] : ''; } elseif (preg_match('/^[\\da-fA-F:]+/', $fulldomain) || preg_match('/^\\d+(\\.\\d+){3}$/', $fulldomain)) { $subdomain = ''; $domain = $fulldomain; } else { $fullLength = strlen($fulldomain); if (empty($config['validDomains'])) { $validDomains = array('localhost'); } else { $validDomains = array_map('strtolower', (array) $config['validDomains']); } if (!empty($config['defaultDomain'])) { $defaultDomain = strtolower($config['defaultDomain']); if (!in_array($defaultDomain, $validDomains)) { array_unshift($validDomains, $defaultDomain); } } foreach ($validDomains as $validDomain) { if ($fulldomain == $validDomain) { $subdomain = ''; $domain = $fulldomain; break; } $length = $fullLength - strlen($validDomain) - 1; if ($length > 0 && '.' . $validDomain == substr($fulldomain, $length)) { $subdomain = substr($fulldomain, 0, $length); $domain = $validDomain; break; } } } if (!isset($subdomain) || !isset($domain)) { if (preg_match('/^(.+)\\.([a-z0-9-]+\\.[a-z]+)$/', $fulldomain, $matches)) { $subdomain = $matches[1]; $domain = $matches[2]; } else { $subdomain = ''; $domain = $fulldomain; } } $query = $db->query(' SELECT * FROM ' . $platform->quoteIdentifier('subdomain') . ' WHERE LOWER( ' . $platform->quoteIdentifier('subdomain') . ' ) = LOWER( ' . $driver->formatParameterName('subdomain') . ' ) '); $result = $query->execute(array('subdomain' => $subdomain)); if ($result->getAffectedRows() > 0) { $schema = $db->getCurrentSchema(); foreach ($result as $data) { $info = new SiteInfo(array('schema' => $schema, 'domain' => $domain, 'subdomain' => $subdomain, 'subdomainId' => $data['id'], 'fulldomain' => $fulldomain, 'scheme' => $this->getScheme(), 'port' => $this->getPort())); $sm->setService('SiteInfo', $info); return $db; } } else { if ($domain) { $sm->setService('RedirectToDomain', new RedirectionService($this->getScheme(), $domain, $this->getPort(), 'sub-domain not found', false)); } else { if (empty($config['defaultDomain'])) { throw new Exception\InvalidArgumentException('Domain not found, and default domain not set'); } else { $sm->setService('RedirectToDomain', new RedirectionService($this->getScheme(), $config['defaultDomain'], $this->getPort(), 'sub-domain not found', false)); } } } return $db; }
/** * Prepare statement * * @param \Zend\Db\Adapter\Adapter $adapter * @param \Zend\Db\Adapter\Driver\StatementInterface $statementContainer * @return void */ public function prepareStatement(Adapter $adapter, StatementContainerInterface $statementContainer) { // ensure statement has a ParameterContainer $parameterContainer = $statementContainer->getParameterContainer(); if (!$parameterContainer instanceof ParameterContainer) { $parameterContainer = new ParameterContainer(); $statementContainer->setParameterContainer($parameterContainer); } $sqls = array(); $parameters = array(); $platform = $adapter->getPlatform(); foreach ($this->specifications as $name => $specification) { $parameters[$name] = $this->{'process' . $name}($platform, $adapter, $parameterContainer, $sqls, $parameters); if ($specification && is_array($parameters[$name])) { $sqls[$name] = $this->createSqlFromSpecificationAndParameters($specification, $parameters[$name]); } } $sql = implode(' ', $sqls); $statementContainer->setSql($sql); return; }
/** * @param string $identifier * @return string */ public function quoteIdentifier($identifier) { return $this->adapter->getPlatform()->quoteIdentifier($identifier); }
/** * Prepare statement * * @param Adapter $adapter * @param StatementContainerInterface $statementContainer * @return void */ public function prepareStatement(Adapter $adapter, StatementContainerInterface $statementContainer) { $driver = $adapter->getDriver(); $platform = $adapter->getPlatform(); $parameterContainer = $statementContainer->getParameterContainer(); if (!$parameterContainer instanceof ParameterContainer) { $parameterContainer = new ParameterContainer(); $statementContainer->setParameterContainer($parameterContainer); } $table = $platform->quoteIdentifier($this->table); $columns = array(); $values = array(); foreach ($this->columns as $cIndex => $column) { $columns[$cIndex] = $platform->quoteIdentifier($column); if ($this->values[$cIndex] instanceof Expression) { $exprData = $this->processExpression($this->values[$cIndex], $platform, $adapter); $values[$cIndex] = $exprData->getSql(); $parameterContainer->merge($exprData->getParameterContainer()); } else { $values[$cIndex] = $driver->formatParameterName($column); $parameterContainer->offsetSet($column, $this->values[$cIndex]); } } $sql = sprintf($this->specifications[self::SPECIFICATION_INSERT], $table, implode(', ', $columns), implode(', ', $values)); $statementContainer->setSql($sql); }
/** * Prepare statement * * @param \Zend\Db\Adapter\Adapter $adapter * @param \Zend\Db\Adapter\Driver\StatementInterface $statement * @return void */ public function prepareStatement(Adapter $adapter, StatementInterface $statement) { $platform = $adapter->getPlatform(); $separator = $platform->getIdentifierSeparator(); $columns = array(); foreach ($this->columns as $columnKey => $column) { $columns[] = $platform->quoteIdentifierInFragment($column); /* if (is_int($columnKey)) { $columns = $platform->quoteIdentifierInFragment($column); } else { $columns = $platform->quoteIdentifierInFragment($column); } */ } $table = $platform->quoteIdentifier($this->table); if ($this->databaseOrSchema != '') { $dbSchema = $platform->quoteIdentifier($this->databaseOrSchema) . $platform->getIdentifierSeparator(); $table = $dbSchema . $table; } else { $dbSchema = ''; } if ($this->joins) { $jArgs = array(); foreach ($this->joins as $j => $join) { $jArgs[$j] = array(); $jArgs[$j][] = strtoupper($join[3]); // type $jArgs[$j][] = $platform->quoteIdentifier($join[0]); // table $jArgs[$j][] = $platform->quoteIdentifierInFragment($join[1], array('=', 'AND', 'OR', '(', ')')); // on foreach ($join[2] as $jColumn) { $columns[] = $jArgs[$j][1] . $separator . $platform->quoteIdentifierInFragment($jColumn); } } } $columns = implode(', ', $columns); $sql = sprintf($this->specification1, $columns, $table); if (isset($jArgs)) { foreach ($jArgs as $jArg) { $sql .= ' ' . vsprintf($this->specification2, $jArg); } } if ($this->where->count() > 0) { $statement->setSql($sql); $this->where->prepareStatement($adapter, $statement); $sql = $statement->getSql(); } $order = null; // @todo $limit = null; // @todo $sql .= isset($order) ? sprintf($this->specification3, $order) : ''; $sql .= isset($limit) ? sprintf($this->specification4, $limit) : ''; $statement->setSql($sql); }