/**
* Collects the table column metadata.
*
* @param TableSchema $table the table metadata
*
* @return boolean whether the table exists in the database
*/
protected function findColumns($table)
{
$sql = "PRAGMA table_info({$table->rawName})";
$columns = $this->connection->createCommand($sql)->queryAll();
if (empty($columns)) {
return false;
}
foreach ($columns as $column) {
$c = $this->createColumn($column);
$table->addColumn($c);
if ($c->isPrimaryKey) {
if ($c->autoIncrement) {
$table->sequenceName = '';
}
if ($table->primaryKey === null) {
$table->primaryKey = $c->name;
} elseif (is_string($table->primaryKey)) {
$table->primaryKey = [$table->primaryKey, $c->name];
} else {
$table->primaryKey[] = $c->name;
}
}
}
if (is_string($table->primaryKey)) {
$cnk = strtolower($table->primaryKey);
if (ColumnSchema::TYPE_INTEGER === $table->columns[$cnk]->type) {
$table->sequenceName = '';
$table->columns[$cnk]->autoIncrement = true;
$table->columns[$cnk]->type = ColumnSchema::TYPE_ID;
}
}
return true;
}
/**
* Collects the table column metadata.
*
* @param TableSchema $table the table metadata
*
* @return boolean whether the table exists in the database
*/
protected function findColumns($table)
{
$sql = <<<EOD
SELECT a.attname, LOWER(format_type(a.atttypid, a.atttypmod)) AS type, d.adsrc, a.attnotnull, a.atthasdef,
\tpg_catalog.col_description(a.attrelid, a.attnum) AS comment
FROM pg_attribute a LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE a.attnum > 0 AND NOT a.attisdropped
\tAND a.attrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname=:table
\t\tAND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = :schema))
ORDER BY a.attnum
EOD;
$command = $this->connection->createCommand($sql);
$command->bindValue(':table', $table->name);
$command->bindValue(':schema', $table->schemaName);
if (($columns = $command->queryAll()) === []) {
return false;
}
foreach ($columns as $column) {
$c = $this->createColumn($column);
$table->addColumn($c);
if (stripos($column['adsrc'], 'nextval') === 0 && preg_match('/nextval\\([^\']*\'([^\']+)\'[^\\)]*\\)/i', $column['adsrc'], $matches)) {
if (strpos($matches[1], '.') !== false || $table->schemaName === self::DEFAULT_SCHEMA) {
$this->sequences[$table->rawName . '.' . $c->name] = $matches[1];
} else {
$this->sequences[$table->rawName . '.' . $c->name] = $table->schemaName . '.' . $matches[1];
}
$c->autoIncrement = true;
}
}
return true;
}
/**
* Collects the table column metadata.
*
* @param TableSchema $table the table metadata
*
* @return boolean whether the table exists in the database
*/
protected function findColumns($table)
{
$sql = <<<SQL
SELECT * FROM sys.syscolumns WHERE creator = '{$table->schemaName}' AND tname = '{$table->name}'
SQL;
try {
$columns = $this->connection->createCommand($sql)->queryAll();
if (empty($columns)) {
return false;
}
} catch (\Exception $e) {
return false;
}
foreach ($columns as $column) {
$c = $this->createColumn($column);
$table->addColumn($c);
if ($c->autoIncrement && $table->sequenceName === null) {
$table->sequenceName = $table->name;
}
}
return true;
}
/**
* Collects the table column metadata.
*
* @param TableSchema $table the table metadata
*
* @return boolean whether the table exists in the database
*/
protected function findColumns($table)
{
$sql = 'SHOW FULL COLUMNS FROM ' . $table->rawName;
try {
$columns = $this->connection->createCommand($sql)->queryAll();
} catch (\Exception $e) {
return false;
}
foreach ($columns as $column) {
$c = $this->createColumn($column);
if ($c->isPrimaryKey) {
if ($table->primaryKey === null) {
$table->primaryKey = $c->name;
} elseif (is_string($table->primaryKey)) {
$table->primaryKey = [$table->primaryKey, $c->name];
} else {
$table->primaryKey[] = $c->name;
}
if ($c->autoIncrement) {
$table->sequenceName = '';
if (ColumnSchema::TYPE_INTEGER === $c->type) {
$c->type = ColumnSchema::TYPE_ID;
}
}
}
$table->addColumn($c);
}
return true;
}
/**
* Collects the table column metadata.
*
* @param TableSchema $table the table metadata
*
* @return boolean whether the table exists in the database
*/
protected function findColumns($table)
{
$schema = !empty($table->schemaName) ? $table->schemaName : $this->getDefaultSchema();
if ($this->isISeries()) {
$sql = <<<SQL
SELECT column_name AS colname,
ordinal_position AS colno,
data_type AS typename,
CAST(column_default AS VARCHAR(254)) AS default,
is_nullable AS nulls,
length AS length,
numeric_scale AS scale,
is_identity AS identity
FROM qsys2.syscolumns
WHERE table_name = :table AND table_schema = :schema
ORDER BY ordinal_position
SQL;
} else {
$sql = <<<SQL
SELECT colname AS colname,
colno,
typename,
CAST(default AS VARCHAR(254)) AS default,
nulls,
length,
scale,
identity
FROM syscat.columns
WHERE syscat.columns.tabname = :table AND syscat.columns.tabschema = :schema
ORDER BY colno
SQL;
}
$command = $this->connection->createCommand($sql);
$command->bindValue(':table', $table->tableName);
$command->bindValue(':schema', $schema);
if (($columns = $command->queryAll()) === []) {
return false;
}
foreach ($columns as $column) {
$c = $this->createColumn($column);
$table->addColumn($c);
}
return count($table->columns) > 0;
}
/**
* Collects the table column metadata.
*
* @param TableSchema $table the table metadata
*
* @return boolean whether the table exists in the database
*/
protected function findColumns($table)
{
$schemaName = $table->schemaName;
$tableName = $table->name;
$sql = <<<EOD
SELECT a.column_name, a.data_type ||
case
when data_precision is not null
then '(' || a.data_precision ||
case when a.data_scale > 0 then ',' || a.data_scale else '' end
|| ')'
when data_type = 'DATE' then ''
when data_type = 'NUMBER' then ''
else '(' || to_char(a.data_length) || ')'
end as data_type,
a.nullable, a.data_default,
( SELECT D.constraint_type
FROM ALL_CONS_COLUMNS C
inner join ALL_constraints D on D.OWNER = C.OWNER and D.constraint_name = C.constraint_name
WHERE C.OWNER = B.OWNER
and C.table_name = B.object_name
and C.column_name = A.column_name
and D.constraint_type = 'P') as Key,
com.comments as column_comment
FROM ALL_TAB_COLUMNS A
inner join ALL_OBJECTS B ON b.owner = a.owner and ltrim(B.OBJECT_NAME) = ltrim(A.TABLE_NAME)
LEFT JOIN user_col_comments com ON (A.table_name = com.table_name AND A.column_name = com.column_name)
WHERE
a.owner = '{$schemaName}'
\tand (b.object_type = 'TABLE' or b.object_type = 'VIEW')
\tand b.object_name = '{$tableName}'
ORDER by a.column_id
EOD;
$command = $this->connection->createCommand($sql);
if (($columns = $command->queryAll()) === []) {
return false;
}
foreach ($columns as $column) {
$c = $this->createColumn($column);
$table->addColumn($c);
if ($c->isPrimaryKey) {
if ($table->primaryKey === null) {
$table->primaryKey = $c->name;
} elseif (is_string($table->primaryKey)) {
$table->primaryKey = [$table->primaryKey, $c->name];
} else {
$table->primaryKey[] = $c->name;
}
// set defaults
$c->autoIncrement = false;
$table->sequenceName = '';
$sql = <<<EOD
SELECT trigger_body FROM ALL_TRIGGERS
WHERE table_owner = '{$schemaName}' and table_name = '{$tableName}'
and triggering_event = 'INSERT' and status = 'ENABLED' and trigger_type = 'BEFORE EACH ROW'
EOD;
$trig = $command = $this->connection->createCommand($sql)->queryScalar();
if (!empty($trig)) {
$c->autoIncrement = true;
$seq = stristr($trig, '.nextval', true);
$seq = substr($seq, strrpos($seq, ' ') + 1);
$table->sequenceName = $seq;
}
}
}
return true;
}
/**
* Collects the table column metadata.
*
* @param TableSchema $table the table metadata
*
* @return boolean whether the table exists in the database
*/
protected function findColumns($table)
{
$sql = "PRAGMA table_info({$table->rawName})";
$columns = $this->connection->createCommand($sql)->queryAll();
if (empty($columns)) {
return false;
}
foreach ($columns as $column) {
$c = $this->createColumn($column);
$table->addColumn($c);
if ($c->isPrimaryKey) {
if ($table->primaryKey === null) {
$table->primaryKey = $c->name;
} elseif (is_string($table->primaryKey)) {
$table->primaryKey = [$table->primaryKey, $c->name];
} else {
$table->primaryKey[] = $c->name;
}
}
}
if (is_string($table->primaryKey) && !strncasecmp($table->columns[$table->primaryKey]->dbType, 'int', 3)) {
$table->sequenceName = '';
$table->columns[$table->primaryKey]->autoIncrement = true;
}
return true;
}
