Handle db_index_exists('a_table', 'PRIMARY') in DatabaseSchema::indexExists() [#1062122]

The above command works just fine for MySQL but doesn't for PostgreSQL.

I noticed this while backporting db_index_exists() to Privatemsg (what else) to clean up the mess I have with my indexes there.

Here is the function I ended up using in D6, I can provide a real patch later.

/**
 * Checks if an index exists in the given table.
 *
 * @param $table
 *   The name of the table in drupal (no prefixing).
 * @param $name
 *   The name of the index in drupal (no prefixing).
 *
 * @return
 *   TRUE if the given index exists, otherwise FALSE.
 */
function privatemsg_db_index_exists($table, $name) {
  global $db_type;

  if ($db_type == 'pgsql') {
    if ($name == 'PRIMARY') {
      $pkey = '{' . $table . '}_pkey';
      return (bool) db_result(db_query("SELECT 1 FROM pg_constraint WHERE conname = '$pkey' AND contype = 'p'"));
    }
    else {
      $index_name = '{' . $table . '}_' . $name . '_idx';
      return (bool) db_result(db_query("SELECT 1 FROM pg_indexes WHERE indexname = '$index_name'"));
    }
  }
  else {
    return (bool)db_fetch_array(db_query('SHOW INDEX FROM {' . $table . "} WHERE key_name = '$name'"));
  }
}

Of course, a existsPrimary() function would be better, but I don't think we can get that into D7.

Comment	File	Size	Author
#8	primary_2.patch	1.97 KB	berdir
#3	primary.patch	1.92 KB	berdir

Comments

Comment #1

josh waihi commented 15 February 2011 at 11:03

What if the user specifies the primary keys actual name instead of PRIMARY? does that work in MySQL, you code wouldn't work in PostgreSQL.

Comment #2

berdir

German

Switzerland

commented 15 February 2011 at 11:30

'PRIMARY' is the actual name in MySQL, there is absoletely zero special handling for that in DatabaseSchema_mysql. Index names must only be unique per table in MySQL not like PostgreSQL, where they need to be prefixed with the table name.

Example:

SHOW INDEX FROM pm_message;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| pm_message |          0 | PRIMARY  |            1 | mid         | A         |         445 |     NULL | NULL   |      | BTREE      |         |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Comment #3

berdir

German

Switzerland

commented 16 February 2011 at 08:01

Status:

Active

» Needs review

Status	File	Size
new	primary.patch	1.92 KB

Here is a patch.

Also added a simple test but didn't find a table that is installed by default and doesn't have a primary key. forum_index would be an example without one..

Comment #4

lars toomre commented 10 April 2011 at 19:03

This came up for me today in Drupal patch bingo. I do not use PostgreSQL so I have no ability to test this patch.

However, in reading through the code, I wonder whether it would not be better to replace:

    if ($name == 'PRIMARY') {

with

    if (strtolower($name) == 'primary') {

This change would remove the dependency on capitalization or a parameter potentially passed in mixed format. I ask because I normally think of specifying text parameters all in lower case as a general rule. I would not normally think of using a keyword case in all capital letters. This is not an important change, but I thought I would share it to prevent a WTF for a future programmer.

Comment #5

berdir

German

Switzerland

commented 10 April 2011 at 19:44

It must be PRIMARY because that is what MySQL is using. There is no point in allowing 'primary' if that would require yet another special case in the MySQL driver.