public function DatabaseSchema_sqlsrv::queryColumnInformation in Drupal driver for SQL Server and SQL Azure 7.2

Same name and namespace in other branches

1. 7.3 sqlsrv/schema.inc \DatabaseSchema_sqlsrv::queryColumnInformation()
2. 7 sqlsrv/schema.inc \DatabaseSchema_sqlsrv::queryColumnInformation()

Database introspection: fetch technical information about a table.

Return value

An array with the following structure:

• blobs[]: Array of column names that should be treated as blobs in this table.
• identities[]: Array of column names that are identities in this table.
• identity: The name of the identity column
• columns[]: An array of specification details for the columns
  • name: Column name.
  • max_length: Maximum length.
  • precision: Precision.
  • collation_name: Collation.
  • is_nullable: Is nullable.
  • is_ansi_padded: Is ANSI padded.
  • is_identity: Is identity.
  • definition: If a computed column, the computation formulae.
  • default_value: Default value for the column (if any).

7 calls to DatabaseSchema_sqlsrv::queryColumnInformation()

DatabaseSchema_sqlsrv::calculateClusteredIndexRowSizeBytes in sqlsrv/schema.inc

Estimates the row size of a clustered index.

DatabaseSchema_sqlsrv::compressPrimaryKeyIndex in sqlsrv/schema.inc

Sometimes the size of a table's primary key index needs to be reduced to allow for Primary XML Indexes.

DatabaseSchema_sqlsrv::createIndexSql in sqlsrv/schema.inc

Returns the SQL needed (incomplete) to create and index. Supports XML indexes.

DatabaseSchema_sqlsrv::dropFieldRelatedObjects in sqlsrv/schema.inc

Drop the related objects of a column (indexes, constraints, etc.).

DatabaseSchema_sqlsrv::introspectPrimaryKeyFields in sqlsrv/schema.inc

Get the list of fields participating in the Primary Key

... See full list

File

sqlsrv/schema.inc, line 136

Database schema code for Microsoft SQL Server database servers.

Class

DatabaseSchema_sqlsrv

Code

public function queryColumnInformation($table, $refresh = FALSE) {

  // No worry for the tableExists() check, results
  // are cached.
  // This fails with drupal > v7.78

  //if (empty($table) || !$this->tableExists($table)) {
  if (!$this
    ->tableExists($table)) {
    throw new DatabaseSchemaObjectDoesNotExistException("The table '{$table}' does not exist.");
  }
  $table_info = $this
    ->getPrefixInfo($table);

  // We could adapt the current code to support temporary table introspection, but
  // for now this is not supported.
  if ($table_info['table'][0] == '#') {
    throw new Exception('Temporary table introspection is not supported.');
  }
  if ($cache = fastcache::cache_get('queryColumnInformation:' . $table, 'schema_queryColumnInformation')) {
    return $cache->data;
  }
  $info = array();

  // Don't use {} around information_schema.columns table.
  $result = $this->connection
    ->query_direct("SELECT sysc.name, sysc.max_length, sysc.precision, sysc.collation_name,\n                    sysc.is_nullable, sysc.is_ansi_padded, sysc.is_identity, sysc.is_computed, TYPE_NAME(sysc.user_type_id) as type,\n                    syscc.definition,\n                    sm.[text] as default_value\n                    FROM sys.columns AS sysc\n                    INNER JOIN sys.syscolumns AS sysc2 ON sysc.object_id = sysc2.id and sysc.name = sysc2.name\n                    LEFT JOIN sys.computed_columns AS syscc ON sysc.object_id = syscc.object_id AND sysc.name = syscc.name\n                    LEFT JOIN sys.syscomments sm ON sm.id = sysc2.cdefault\n                    WHERE sysc.object_id = OBJECT_ID(:table)\n                    ", array(
    ':table' => $table_info['schema'] . '.' . $table_info['table'],
  ));
  foreach ($result as $column) {
    if ($column->type == 'varbinary') {
      $info['blobs'][$column->name] = TRUE;
    }
    $info['columns'][$column->name] = (array) $column;

    // Provide a clean list of columns that excludes the ones internally created by the
    // database driver.
    if (!(isset($column->name[1]) && substr($column->name, 0, 2) == "__")) {
      $info['columns_clean'][$column->name] = (array) $column;
    }
  }

  // If we have computed columns, it is important to know what other columns they depend on!
  $column_names = array_keys($info['columns']);
  $column_regex = implode('|', $column_names);
  foreach ($info['columns'] as &$column) {
    $dependencies = array();
    if (!empty($column['definition'])) {
      $matches = array();
      if (preg_match_all("/\\[[{$column_regex}\\]]*\\]/", $column['definition'], $matches) > 0) {
        $dependencies = array_map(function ($m) {
          return trim($m, "[]");
        }, array_shift($matches));
      }
    }
    $column['dependencies'] = array_flip($dependencies);
  }

  // Don't use {} around system tables.
  $result = $this->connection
    ->query_direct('SELECT name FROM sys.identity_columns WHERE object_id = OBJECT_ID(:table)', array(
    ':table' => $table_info['schema'] . '.' . $table_info['table'],
  ));
  unset($column);
  $info['identities'] = array();
  $info['identity'] = NULL;
  foreach ($result as $column) {
    $info['identities'][$column->name] = $column->name;
    $info['identity'] = $column->name;
  }

  // Now introspect information about indexes
  $result = $this->connection
    ->query_direct("select tab.[name]  as [table_name],\n         idx.[name]  as [index_name],\n         allc.[name] as [column_name],\n         idx.[type_desc],\n         idx.[is_unique],\n         idx.[data_space_id],\n         idx.[ignore_dup_key],\n         idx.[is_primary_key],\n         idx.[is_unique_constraint],\n         idx.[fill_factor],\n         idx.[is_padded],\n         idx.[is_disabled],\n         idx.[is_hypothetical],\n         idx.[allow_row_locks],\n         idx.[allow_page_locks],\n         idxc.[is_descending_key],\n         idxc.[is_included_column],\n         idxc.[index_column_id],\n         idxc.[key_ordinal]\n    FROM sys.[tables] as tab\n    INNER join sys.[indexes]       idx  ON tab.[object_id] =  idx.[object_id]\n    INNER join sys.[index_columns] idxc ON idx.[object_id] = idxc.[object_id] and  idx.[index_id]  = idxc.[index_id]\n    INNER join sys.[all_columns]   allc ON tab.[object_id] = allc.[object_id] and idxc.[column_id] = allc.[column_id]\n    WHERE tab.object_id = OBJECT_ID(:table)\n    ORDER BY tab.[name], idx.[index_id], idxc.[index_column_id]\n                    ", array(
    ':table' => $table_info['schema'] . '.' . $table_info['table'],
  ));
  foreach ($result as $index_column) {
    if (!isset($info['indexes'][$index_column->index_name])) {
      $ic = clone $index_column;

      // Only retain index specific details.
      unset($ic->column_name);
      unset($ic->index_column_id);
      unset($ic->is_descending_key);
      unset($ic->table_name);
      unset($ic->key_ordinal);
      $info['indexes'][$index_column->index_name] = (array) $ic;
      if ($index_column->is_primary_key) {
        $info['primary_key_index'] = $ic->index_name;
      }
    }
    $index =& $info['indexes'][$index_column->index_name];
    $index['columns'][$index_column->key_ordinal] = array(
      'name' => $index_column->column_name,
      'is_descending_key' => $index_column->is_descending_key,
      'key_ordinal' => $index_column->key_ordinal,
    );

    // Every columns keeps track of what indexes it is part of.
    $info['columns'][$index_column->column_name]['indexes'][] = $index_column->index_name;
    if (isset($info['columns_clean'][$index_column->column_name])) {
      $info['columns_clean'][$index_column->column_name]['indexes'][] = $index_column->index_name;
    }
  }
  fastcache::cache_set('queryColumnInformation:' . $table, $info, 'schema_queryColumnInformation');
  return $info;
}