in trying to get all the DB updates and install stuff working right for my efforts to refactor all the issue tracking out of the project.module into a new project_issue.module (http://drupal.org/node/74995), i needed a portable way to test if a table already exists in the DB to avoid a bunch of nasty error messages in certain upgrade/installation paths.

after some research on the web and testing on both MySQL and PgSQL, i've got simple, 1-line versions for each DB to immediately tell you if a table exists, without generating any errors if not. attached patch provides this.

example usage:

  if (!db_table_exists('project_issue_state')) {
    db_query("CREATE TABLE {project_issue_state} ...");
    db_query("INSERT INTO {project_issue_state} VALUES ...");
    ...
  }

sure, MySQL understands CREATE TABLE IF NOT EXISTS, but PgSQL does not. also, the install system might make it easier to keep track of what tables you think you've already installed, but in some cases, that's not good enough. frankly, the above seems a lot easier to use and understand than trying to program with schema revision numbers, etc, etc. plus, the code is *tiny*. i'd much rather see these methods in core than have to duplicate them in a bunch of DB updates and install hooks i've already written and am planning to write...

plus, if they're in core and people start using them, it'll help ensure that folks don't break pgsql by using mysql-specific tricks for this problem.

thanks!
-derek

CommentFileSizeAuthor
db_table_exists.patch1.81 KBdww
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Dries’s picture

Why don't you use %s?

Looks like a useful function.

dww’s picture

re: %s ...

i only did it this way to match the nearby code as closely as possible. for example, a few lines above:

function db_lock_table($table) {
  db_query('LOCK TABLES {'. db_escape_table($table) .'} WRITE');
}

if that's silly, and the above should be written as:

function db_lock_table($table) {
  db_query('LOCK TABLES {%s} WRITE', db_escpage_table($table));
}

let me know, and i'll fix my patch (and generate a new one to clean up db_lock_table() and any others i see like that)...

dww’s picture

(without the typo in "db_escpage_table" of course) ;)

chx’s picture

Status: Needs review » Reviewed & tested by the community
drumm’s picture

Status: Reviewed & tested by the community » Fixed

Looks like it is consistent with what we have and chx might have reviewed it (he didn't actually say anything about his review), so committed to HEAD.

Anonymous’s picture

Status: Fixed » Closed (fixed)