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