/**
  * Get table information by name
  *
  * @param   string table
  * @param   string database default NULL if omitted, uses current database
  * @return  rdbms.DBTable
  */
 public function getTable($table, $database = null)
 {
     $t = new \rdbms\DBTable($table);
     $primaryKey = [];
     $q = $this->conn->query('pragma table_info(%s)', $table);
     while ($record = $q->next()) {
         $t->addAttribute(new \rdbms\DBTableAttribute($record['name'], self::$map[$this->typeOf($record['type'])], $record['pk'], !$record['notnull'], $this->lengthOf($record['type']), 0, 0));
         if ($record['pk']) {
             $primaryKey[$record['name']] = true;
         }
     }
     $q = $this->conn->query('pragma index_list(%s)', $table);
     while ($index = $q->next()) {
         $dbindex = $t->addIndex(new \rdbms\DBIndex($index['name'], []));
         $dbindex->unique = (bool) $index['unique'];
         $qi = $this->conn->query('pragma index_info(%s)', $index['name']);
         while ($column = $qi->next('name')) {
             $dbindex->keys[] = $column;
         }
         // Find out if this index covers exactly the primary key
         $dbindex->primary = true;
         foreach ($dbindex->keys as $k) {
             if (!isset($primaryKey[$k])) {
                 $dbindex->primary = false;
                 break;
             }
         }
     }
     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("\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 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;
 }
    /**
     * 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;
    }