public function Scheme::TableDetailsGet in Drupal driver for SQL Server and SQL Azure 8.2

Database introspection: fetch technical information about a table.

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).

Parameters

string $table:

Return value

array

3 calls to Scheme::TableDetailsGet()

Scheme::calculateClusteredIndexRowSizeBytes in drivers/lib/Drupal/Driver/Database/sqlsrv/Scheme.php

Estimates the row size of a clustered index.

Scheme::ColumnDetailsGet in drivers/lib/Drupal/Driver/Database/sqlsrv/Scheme.php

Retrieve an array of field specs from an array of field names.

Scheme::TableHasXmlIndex in drivers/lib/Drupal/Driver/Database/sqlsrv/Scheme.php

Check if a table already has an XML index.

File

drivers/lib/Drupal/Driver/Database/sqlsrv/Scheme.php, line 414

Class

Scheme

Namespace

Drupal\Driver\Database\sqlsrv

Code

public function TableDetailsGet($table) {
  if ($cache = $this->cnn
    ->Cache('sqlsrv-tabledetails')
    ->Get($table)) {

    // The correctness of this data is so important for the database layer
    // to work, that we double check that it is - at least - valid.
    if (isset($cache->data['columns']) && !empty($cache->data['columns'])) {
      return $cache->data;
    }
  }

  // We could adapt the current code to support temporary table introspection, but
  // for now this is not supported.
  if ($table[0] == '#') {
    throw new \Exception('Temporary table introspection is not supported.');
  }
  $schema = $this
    ->GetDefaultSchema();

  // Initialize the information array.
  $info = [
    'identity' => null,
    'identities' => [],
    'columns' => [],
    'columns_clean' => [],
  ];

  // Don't use {} around information_schema.columns table.
  $result = $this->cnn
    ->query_execute("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' => $schema . '.' . $table,
  ));
  foreach ($result as $column) {
    if ($column->type == 'varbinary') {
      $info['blobs'][$column->name] = true;
    }

    // Add the complete SQL Server type with length
    $column->sqlsrv_type = $column->type;
    if ($this
      ->IsVariableLengthType($column->type)) {
      if ($column->max_length == -1) {
        $column->sqlsrv_type .= "(max)";
      }
      else {
        $column->sqlsrv_type .= "({$column->max_length})";
      }
    }
    $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 ($column->is_identity) {
      $info['identities'][$column->name] = $column->name;
      $info['identity'] = $column->name;
    }
  }

  // We should have some column data here, otherwise there is a
  // chance that the table does not exist.
  if (empty($info['columns']) && !$this
    ->TableExists($table)) {
    throw new SchemaObjectDoesNotExistException("Table {$table} does not exist.", 25663);
  }

  // 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);
  }

  // Now introspect information about indexes
  $result = $this->cnn
    ->query_execute("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' => $schema . '.' . $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;
    }
  }
  $this->cnn
    ->Cache('sqlsrv-tabledetails')
    ->Set($table, $info);
  return $info;
}