/**
* Get table by name
*
* @param string table
* @param string database default NULL if omitted, uses current database
* @return rdbms.DBTable a DBTable object
*/
public function getTable($table, $database = null)
{
$t = new \rdbms\DBTable($table);
$q = $this->conn->query("\n select\n column_name,\n udt_name,\n column_default,\n data_type,\n numeric_precision,\n numeric_scale,\n datetime_precision,\n character_maximum_length,\n is_nullable\n from\n information_schema.columns\n where\n table_schema='public'\n and table_catalog=%s\n and table_name=%s", $database, $table);
while ($record = $q->next()) {
$t->addAttribute(new \rdbms\DBTableAttribute($record['column_name'], $this->map[$record['udt_name']], strpos($record['column_default'], 'nextval(') === 0, $record['is_nullable'] != 'NO', 0, $record['numeric_precision'], $record['numeric_scale']));
}
$q = $this->conn->query("\n select\n t.constraint_name as name,\n k.column_name as column\n from\n information_schema.table_constraints as t JOIN\n information_schema.key_column_usage as k on (k.constraint_name = t.constraint_name)\n where\n 'PRIMARY KEY' = t.constraint_type\n and t.table_catalog = %s\n and t.table_name = %s", $database, $table);
$key = null;
while ($record = $q->next()) {
if ($record['name'] != $key) {
$index = $t->addIndex(new \rdbms\DBIndex($record['name'], []));
$key = $record['name'];
}
$index->unique = true;
$index->primary = true;
$index->keys[] = $record['column'];
}
$q = $this->conn->query("\n select\n i.relname as name,\n a.attname as column,\n ix.indisunique as isunique,\n ix.indisprimary as isprimary\n from\n pg_class t,\n pg_class i,\n pg_index ix,\n pg_attribute a\n where\n t.oid = ix.indrelid\n and i.oid = ix.indexrelid\n and a.attrelid = t.oid\n and a.attnum = ANY(ix.indkey)\n and t.relkind = 'r'\n and t.relname = %s", $table);
$key = null;
while ($record = $q->next()) {
if ($record['name'] != $key) {
$index = $t->addIndex(new \rdbms\DBIndex($record['name'], []));
$key = $record['name'];
}
$index->unique = $record['isunique'];
$index->primary = $record['isprimary'];
$index->keys[] = $record['column'];
}
$q = $this->conn->query("\n select\n t.constraint_name as name,\n t.table_catalog as db,\n t.table_name as tbl,\n k.column_name as col,\n r.unique_constraint_catalog as source_db,\n tt.table_name as source_tbl,\n tk.column_name as source_col\n from\n information_schema.table_constraints as t\n JOIN information_schema.referential_constraints as r on (t.constraint_name = r.constraint_name)\n JOIN information_schema.key_column_usage as k on (k.constraint_name = t.constraint_name)\n JOIN information_schema.table_constraints as tt on (r.unique_constraint_name = tt.constraint_name)\n JOIN information_schema.key_column_usage as tk on (\n r.unique_constraint_name = tk.constraint_name\n and k.ordinal_position = tk.ordinal_position\n )\n where\n t.constraint_type = 'FOREIGN KEY'\n and t.table_catalog = %s\n and t.table_name = %s", $database, $table);
$key = null;
while ($record = $q->next()) {
if ($record['name'] != $key) {
$constraint = new \rdbms\DBForeignKeyConstraint();
$t->addForeignKeyConstraint($constraint);
$key = $record['name'];
}
$constraint->addKey($record['col'], $record['source_col']);
$constraint->setName($record['name']);
$constraint->setSource($record['source_tbl']);
}
return $t;
}
/**
* Get indexes for a given table. Expects a temporary table to exist.
*
* @param string table thee table's name
* @param string database default NULL if omitted, uses current database
* @return rdbms.DBTable
*/
protected function dbTableObjectFor($table, $database = null)
{
$t = new \rdbms\DBTable($table);
// Get the table's attributes
$q = $this->conn->query('
select
c.name,
t.name as type,
c.status,
c.length,
c.prec,
c.scale
from
syscolumns c,
systypes t
where
c.id= object_id(%s)
and t.type = c.type
and t.usertype < 100
and t.name not in ("sysname", "nchar", "nvarchar")
', $this->qualifiedTablename($table, $database));
while ($record = $q->next()) {
// Known bits of status column:
// 0x08 => NULLable
// 0x80 => identity column
$t->addAttribute(new DBTableAttribute($record['name'], $this->map[$record['type']], $record['status'] & 0x80, $record['status'] & 8, $record['length'], $record['prec'], $record['scale']));
}
unset($q);
// This query is taken in part from sp_help (part of core sps from
// SQL Server/11.0.3.3 ESD#6/P-FREE/Linux Intel/Linux 2.2.14
// i686/1/OPT/Fri Mar 17 15:45:30 CET 2000)
$q = $this->conn->query('
declare @i int
declare @id int
declare @last int
declare @keys varchar(200)
declare @key varchar(48)
declare @obj varchar(48)
delete from #indexes
select @obj= %s
select @id= min(indid) from sysindexes where id= object_id(@obj)
while @id is not NULL
begin
set nocount on
select @keys= "", @i= 1
while (@i <= 16) begin
select @key= index_col(@obj, @id, @i)
if @key is NULL begin
goto done
end
if @i > 1 begin
select @keys= @keys + ","
end
select @keys= @keys + @key
select @i= @i + 1
end
done:
set nocount off
insert #indexes select
@keys,
i.name,
v.number,
i.status
from
master.dbo.spt_values v, sysindexes i
where
i.status & v.number = v.number
and v.type = "I"
and i.id = object_id(@obj)
and i.indid = @id
select @last = @id
select @id = min(indid) from sysindexes where id = object_id(@obj) and indid > @last
end
select * from #indexes', $this->qualifiedTablename($table, $database));
$keys = null;
while ($record = $q->next()) {
if ($keys != $record['keys']) {
$index = $t->addIndex(new \rdbms\DBIndex($record['name'], explode(',', $record['keys'])));
$keys = $record['keys'];
}
if (2 == $record['number']) {
$index->unique = true;
}
if ($record['status'] & 2048) {
$index->primary = true;
}
}
// Get foreign key constraints
// in mysql the only way is to parse the creat statement
$sp_helpconstraint = $this->conn->query('sp_helpconstraint %s, detail', $this->qualifiedTablename($table, $database));
while ($db_constraint = $sp_helpconstraint->next()) {
if ('referential constraint' != $db_constraint['type']) {
continue;
}
if (0 !== strpos($db_constraint['definition'], $table . ' ')) {
continue;
}
$t->addForeignKeyConstraint($this->parseForeignKey($db_constraint));
}
return $t;
}
/**
* Get table by name
*
* @param string table
* @param string database default NULL if omitted, uses current database
* @return rdbms.DBTable a DBTable object
*/
public function getTable($table, $database = null)
{
$t = new \rdbms\DBTable($table);
$q = $this->conn->query('describe %c', $this->qualifiedTablename($table, $database));
while ($record = $q->next()) {
$t->addAttribute(self::tableAttributeFrom($record));
}
// Get keys
// +----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+---------+
// | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment |
// +----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+---------+
// | contract | 0 | PRIMARY | 1 | contract_id | A | 6 | NULL | NULL | |
// | contract | 0 | contract_id_2 | 1 | contract_id | A | 6 | NULL | NULL | |
// | contract | 1 | contract_id | 1 | contract_id | A | 6 | NULL | NULL | |
// | contract | 1 | contract_id | 2 | user_id | A | 6 | NULL | NULL | |
// +----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+---------+
$q = $this->conn->query('show keys from %c', $this->qualifiedTablename($table, $database));
$key = null;
while ($record = $q->next()) {
if ($record['Key_name'] != $key) {
$index = $t->addIndex(new \rdbms\DBIndex($record['Key_name'], []));
$key = $record['Key_name'];
}
$index->unique = '0' == $record['Non_unique'];
$index->primary = 'PRIMARY' == $record['Key_name'];
$index->keys[] = $record['Column_name'];
}
// Get foreign key constraints
// in mysql the only way is to parse the creat statement
$createTableString = $this->conn->query('show create table %c', $this->qualifiedTablename($table, $database))->next('Create Table');
for ($i = 0; $i < strlen($createTableString); $i++) {
switch ($createTableString[$i]) {
case '`':
$this->parseQuoteString($createTableString, $i, '`');
break;
case '"':
$this->parseQuoteString($createTableString, $i, '"');
break;
case '(':
$tableConstraints = $this->filterConstraints($this->extractParams($this->parseBracerString($createTableString, $i)));
foreach ($tableConstraints as $tableConstraint) {
if (strstr($tableConstraint, 'FOREIGN KEY') === false) {
continue;
}
$t->addForeignKeyConstraint($this->parseForeignKeyString($tableConstraint));
}
break;
}
}
return $t;
}