diff options
| -rw-r--r-- | framework/Data/Common/Mssql/TMssqlMetaData.php | 30 | ||||
| -rw-r--r-- | framework/Data/Common/Mysql/TMysqlMetaData.php | 16 | ||||
| -rw-r--r-- | framework/Data/Common/Oracle/TOracleMetaData.php | 38 | ||||
| -rw-r--r-- | framework/Data/Common/Pgsql/TPgsqlMetaData.php | 28 | ||||
| -rw-r--r-- | framework/Data/Common/Sqlite/TSqliteMetaData.php | 28 | ||||
| -rw-r--r-- | framework/Data/Common/TDbMetaData.php | 10 |
6 files changed, 131 insertions, 19 deletions
diff --git a/framework/Data/Common/Mssql/TMssqlMetaData.php b/framework/Data/Common/Mssql/TMssqlMetaData.php index 43faaa25..21571428 100644 --- a/framework/Data/Common/Mssql/TMssqlMetaData.php +++ b/framework/Data/Common/Mssql/TMssqlMetaData.php @@ -23,7 +23,7 @@ Prado::using('System.Data.Common.Mssql.TMssqlTableInfo'); * @since 3.1 */ class TMssqlMetaData extends TDbMetaData -{ +{ /** * @return string TDbTableInfo class name. */ @@ -258,5 +258,33 @@ EOD; } return false; } + + /** + * Returns all table names in the database. + * @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema. + * If not empty, the returned table names will be prefixed with the schema name. + * @return array all table names in the database. + */ + public function findTableNames($schema='dbo') + { + $condition="TABLE_TYPE='BASE TABLE'"; + $sql=<<<EOD +SELECT TABLE_NAME, TABLE_SCHEMA FROM [INFORMATION_SCHEMA].[TABLES] +WHERE TABLE_SCHEMA=:schema AND $condition +EOD; + $command=$this->getDbConnection()->createCommand($sql); + $command->bindParam(":schema", $schema); + $rows=$command->queryAll(); + $names=array(); + foreach ($rows as $row) + { + if ($schema == self::DEFAULT_SCHEMA) + $names[]=$row['TABLE_NAME']; + else + $names[]=$schema.'.'.$row['TABLE_SCHEMA'].'.'.$row['TABLE_NAME']; + } + + return $names; + } } diff --git a/framework/Data/Common/Mysql/TMysqlMetaData.php b/framework/Data/Common/Mysql/TMysqlMetaData.php index b3ee3d08..71c75501 100644 --- a/framework/Data/Common/Mysql/TMysqlMetaData.php +++ b/framework/Data/Common/Mysql/TMysqlMetaData.php @@ -385,5 +385,21 @@ EOD; } return false; } + + /** + * Returns all table names in the database. + * @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema. + * If not empty, the returned table names will be prefixed with the schema name. + * @return array all table names in the database. + */ + public function findTableNames($schema='') + { + if($schema==='') + return $this->getDbConnection()->createCommand('SHOW TABLES')->queryColumn(); + $names=$this->getDbConnection()->createCommand('SHOW TABLES FROM '.$this->quoteTableName($schema))->queryColumn(); + foreach($names as &$name) + $name=$schema.'.'.$name; + return $names; + } } diff --git a/framework/Data/Common/Oracle/TOracleMetaData.php b/framework/Data/Common/Oracle/TOracleMetaData.php index 6f495aeb..7b5bd195 100644 --- a/framework/Data/Common/Oracle/TOracleMetaData.php +++ b/framework/Data/Common/Oracle/TOracleMetaData.php @@ -334,5 +334,41 @@ EOD; } return false; } -} + + /** + * Returns all table names in the database. + * @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema. + * If not empty, the returned table names will be prefixed with the schema name. + * @return array all table names in the database. + */ + public function findTableNames($schema='') + { + if($schema==='') + { + $sql=<<<EOD +SELECT table_name, '{$schema}' as table_schema FROM user_tables +EOD; + $command=$this->getDbConnection()->createCommand($sql); + } + else + { + $sql=<<<EOD +SELECT object_name as table_name, owner as table_schema FROM all_objects +WHERE object_type = 'TABLE' AND owner=:schema +EOD; + $command=$this->getDbConnection()->createCommand($sql); + $command->bindParam(':schema',$schema); + } + $rows=$command->queryAll(); + $names=array(); + foreach($rows as $row) + { + if($schema===$this->getDefaultSchema() || $schema==='') + $names[]=$row['TABLE_NAME']; + else + $names[]=$row['TABLE_SCHEMA'].'.'.$row['TABLE_NAME']; + } + return $names; + } +} diff --git a/framework/Data/Common/Pgsql/TPgsqlMetaData.php b/framework/Data/Common/Pgsql/TPgsqlMetaData.php index 9b4067a6..595f46e8 100644 --- a/framework/Data/Common/Pgsql/TPgsqlMetaData.php +++ b/framework/Data/Common/Pgsql/TPgsqlMetaData.php @@ -416,5 +416,33 @@ EOD; } return false; } + + /** + * Returns all table names in the database. + * @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema. + * If not empty, the returned table names will be prefixed with the schema name. + * @return array all table names in the database. + */ + public function findTableNames($schema='public') + { + if($schema==='') + $schema=self::DEFAULT_SCHEMA; + $sql=<<<EOD +SELECT table_name, table_schema FROM information_schema.tables +WHERE table_schema=:schema AND table_type='BASE TABLE' +EOD; + $command=$this->getDbConnection()->createCommand($sql); + $command->bindParam(':schema',$schema); + $rows=$command->queryAll(); + $names=array(); + foreach($rows as $row) + { + if($schema===self::DEFAULT_SCHEMA) + $names[]=$row['table_name']; + else + $names[]=$row['table_schema'].'.'.$row['table_name']; + } + return $names; + } } diff --git a/framework/Data/Common/Sqlite/TSqliteMetaData.php b/framework/Data/Common/Sqlite/TSqliteMetaData.php index 6c127f96..9bc345f3 100644 --- a/framework/Data/Common/Sqlite/TSqliteMetaData.php +++ b/framework/Data/Common/Sqlite/TSqliteMetaData.php @@ -188,21 +188,15 @@ class TSqliteMetaData extends TDbMetaData } return false; } + + /** + * Returns all table names in the database. + * @param string $schema the schema of the tables. This is not used for sqlite database. + * @return array all table names in the database. + */ + public function findTableNames($schema='') + { + $sql="SELECT DISTINCT tbl_name FROM sqlite_master WHERE tbl_name<>'sqlite_sequence'"; + return $this->getDbConnection()->createCommand($sql)->queryColumn(); + } } - -/** - -CREATE TABLE foo -( - id INTEGER NOT NULL PRIMARY KEY, - id2 CHAR(2) -); - -CREATE TABLE bar -( - id INTEGER NOT NULL PRIMARY KEY, - foo_id INTEGER - CONSTRAINT fk_foo_id REFERENCES foo(id) ON DELETE CASCADE -); -*/ - diff --git a/framework/Data/Common/TDbMetaData.php b/framework/Data/Common/TDbMetaData.php index fa3dee0b..5b4568cb 100644 --- a/framework/Data/Common/TDbMetaData.php +++ b/framework/Data/Common/TDbMetaData.php @@ -178,5 +178,15 @@ abstract class TDbMetaData extends TComponent return $lft . str_replace(self::$delimiterIdentifier, '', $name) . $rgt; } + + /** + * Returns all table names in the database. + * This method should be overridden by child classes in order to support this feature + * because the default implementation simply throws an exception. + * @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema. + * If not empty, the returned table names will be prefixed with the schema name. + * @return array all table names in the database. + */ + abstract public function findTableNames($schema=''); } |