just like we've got db_table_exists(), a handy function (especially useful in .install files) to see if a given table already exists in the DB, how about adding support for a sister function to tell you if a certain column exists in a given table? i needed this in signup.install for something, so i figured out how to do it on both MySQL and PgSQL. i figured i'd share. ;) it's a 1-line function in both cases, so it's not going to bloat core that much, and i think i'd be nice to have. if you want to easily test this function on both DB backends, apply the attached patch, then create a php-filtered node on your test site with the following:

$columns = array(
  'users' => 'uid',
  'userz' => 'uid',
  'node' => 'nidz',
);

print '<ul>';
foreach ($columns as $table => $column) {
  if (db_table_exists($table)) {
    if (db_column_exists($table, $column)) {
      print '<li>'. t('Found %column in {@table}', array('%column' => $column, '@table' => $table)) .'</li>';
    }
    else {
      print '<li>'. t('Table {@table} exists, but does not contain %column', array('%column' => $column, '@table' => $table)) .'</li>';
    }
  }
  else {
    print '<li>'. t('Table {@table} does not exist.', array('@table' => $table)) .'</li>';
  }
}
print '</ul>';

i get the following on both pgsql and mysql on a fresh HEAD install:

  • Found uid in {users}
  • Table {userz} does not exist.
  • Table {node} exists, but does not contain nidz

let me know if this needs any changes before it's RTBC.

thanks,
-derek

p.s. no, i didn't bloat db_column_exists() to ensure the table already exists, i figured that's the caller's problem. on pgsql, the attached version just returns false if you give it a bogus table. on mysql you get an error. i *could* wrap the mysql implementation in a db_table_exists() if we really wanted, but that seems wrong.

CommentFileSizeAuthor
db_column_exists.patch.txt2.68 KBdww
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

dww’s picture

oh, i forgot to mention: thanks to Br3nda from IRC for initial pointers on getting this right in PgSQL.

Shiny’s picture

You're welcome.

chx’s picture

Are you sure this needs database specific, introspection methods? What about:

$result = db_fetch_array(db_query_range("SELECT * FROM $table", 0, 1));
return isset($result[$column]);
chx’s picture

Problem with my approach: empty tables.

Dries’s picture

Status: Needs review » Reviewed & tested by the community

Works for me! :)

Dries’s picture

Status: Reviewed & tested by the community » Fixed

Committed to CVS HEAD. Thanks dww.

dww’s picture

added a mention to the upgrade docs: http://drupal.org/node/114774#db-column-exists
thanks for committing the change. ;)

Anonymous’s picture

Status: Fixed » Closed (fixed)