Allow the designation of a primary key for MySQL views

The feature request is probably too mysql/database-specific for data.module (or schema.module) to directly address in a meaningful way IMHO ...

Using drupal hooks it's relatively easy to hack around this. Can't vouch for the code working in all use cases, as i've just started using data.module and it's just my quick fix without much testing. Hope the attached code helps - YMMV.

Small Caveat 1: if relying on data_ui_views_default_views() to build default views, make sure to clear the caches twice. data_ui_views_default_views can/will be called after the hook_views_data_alter().
Caveat 2: Exposing the database views via data_ui_views_default_views() will confuse the schema-comparison function because it (obviously) can't match the primary key.

I can wrap this into a micro module (e.g. data_views_mysql to eventually handle more mysql and (database) views-specific issues), if the approach is deemed sane and anyone needs it :)

Thanks for the approach. I'm looking at this function:

function serc_custom_views_data_alter(&$views) {
  // name of the database view
  $dbview_name = 'voting_results';
  $pseudo_pk = 'item_id';
  _mysql_views_designate_primarykey($dbview_name, $pseudo_pk, $views);
  // name of the database view
  $dbview_name = '_tmp_voting_AARGH';
  $pseudo_pk = 'item_id';
  _mysql_views_designate_primarykey($dbview_name, $pseudo_pk, $views);
}

"AARGH"?

You copied the "demo" function, you need to replace the values for $dbview_name and $pseudo_pk according to your needs. if you have a (mysql) view named "exampleview" with a primary key "exampleid" then you want to set $dbview_name and $pseudo_pk to these values HTH.

I understand it's a demo function:

1.  // name of the database view
2.  $dbview_name = 'voting_results';
3.  $pseudo_pk = 'item_id';
4.  _mysql_views_designate_primarykey($dbview_name, $pseudo_pk, $views);
5.  // name of the database view
6.  $dbview_name = '_tmp_voting_AARGH';
7.  $pseudo_pk = 'item_id';
8.  _mysql_views_designate_primarykey($dbview_name, $pseudo_pk, $views);

What I'm being dense about is why lines 1-4 and lines 5-8 are duplicates (except for the '_tmp_voting_AARGH') change....

it's only meant to illustrate that you can use the function on multiple views/tables, there's no deeper logic to it :)

I think I've discovered an easier way to do this, which requires only a minor change to DataTable::addPrimaryKey($fields).

Before attempting to actually add the primary key in the DB, check to see if the table is in fact a view, and if so, then just don't add it in the DB (which will fail anyway). But if it is a view, then nonetheless add the PK to the schema, so that everything else that's looking for a PK will get one.

  public function addPrimaryKey($fields) {
    $ret = array();
    $schema = $this->table_schema;
    foreach ($fields as $field) {
      if ($schema['fields'][$field]['type'] == 'text') {
        throw new DataException(t('A text field cannot be made a primary key.'));
      }
    }
//    PATCHED to skip trying to update the database for a view
    if ($schema['description'] != 'VIEW') db_add_primary_key($ret, $this->name, $fields);
    if ($schema['description'] == 'VIEW' || $ret[0]['success']) {
      $schema['primary key'] = $fields;
      $this->update(array('table_schema' => $schema));
      drupal_get_schema($this->name, TRUE);
      if ( $schema['description'] == 'VIEW' ) {
        drupal_set_message(t('This is a database view, and so only a pseudo-primary-key has been created.
          Comparison of the underlying database and the Drupal schema may not match for this table.'));
      }
      return;
    }
    throw new DataException(t('Error creating primary key.'));
  }

This solved problems for me when I was attempting to use views_bulk_operations against a view that I created using webform_mysql_views, where I wanted to mass-eMail people who had given an eMail address on a webform.

Shawn

Incidentally, the same kind of logic would need to be applied to the dropPrimaryKey method as well. I.e.:

  public function dropPrimaryKey() {
    $ret = array();
    // PATCHED to accommodate views and pseudo-primary keys.
    $schema = $this->table_schema;
    if ($schema['description'] != 'VIEW') db_drop_primary_key($ret, $this->name);
    if ($schema['description'] == 'VIEW' || $ret[0]['success']) {
      $schema['primary key'] = array();
      $this->update(array('table_schema' => $schema));
      drupal_get_schema($this->name, TRUE);
      if ( $schema['description'] == 'VIEW' ) {
        drupal_set_message(t('This is a database view, and so only the pseudo-primary-key has been dropped.'));
      }
      return;
    }
    throw new DataException(t('Error dropping primary key.'));
  }

Thanks sdsheridan your snippets were an enormous help to me!

+1 for this feature

I tried using the code in post # 4 but am getting a fatal error that says "Fatal error: Call to undefined function _mysql_views_designate_primarykey() in /example/sites/all/modules/webform_views_fix/webform_views_fix.module on line 7"

Line 7 for my code is:
_mysql_views_designate_primarykey($dbview_name, $pseudo_pk, $views);

And the following is the entire code:

function webform_views_fix_views_data_alter(&$views) {
  // name of the database view
  $dbview_name = 'webform_views_inbound_booking_form__tech_fair';
  $pseudo_pk = 'sid';
  _mysql_views_designate_primarykey($dbview_name, $pseudo_pk, $views);
}

Am I missing something?

Thanks for any help.

Rolled the snippets from 6 and 7 into a patch against 7.x

Hi...

I am using

Webform 7.x-4.0-beta1
Webform MySQL Views 7.x-1.1
Data 7.x-1.0-alpha6+16-dev
Data Admin UI 7.x-1.0-alpha6+16-dev
Schema 7.x-1.2

I had to apply the patch manually. I selected sid as Primary Key, clicked Save, and I got the following error before and after applying the patch.

PDOException: SQLSTATE[HY000]: General error: 1347 'drupal_intranet.webform_views_share_your_core_values_story' is not BASE TABLE: ALTER TABLE {webform_views_share_your_core_values_story} CHANGE `submitted` `submitted` INT NULL DEFAULT NULL; Array ( ) in db_change_field() (line 3020 of /Library/WebServer/Documents/git/intranet-refresh/htdocs/includes/database/database.inc).

When trying to drop my webform table from the Adopted tables list, I get the following error:

PDOException: SQLSTATE[42S02]: Base table or view not found: 1051 Unknown table 'drupal_intranet.webform_views_share_your_core_values_story': DROP TABLE {webform_views_share_your_core_values_story}; Array ( ) in db_drop_table() (line 2819 of /Library/WebServer/Documents/git/intranet-refresh/htdocs/includes/database/database.inc).

i get this error:
PDOException: SQLSTATE[HY000]: General error: 1347 'drupal_intranet.webform_views_share_your_core_values_story' is not BASE TABLE: ALTER TABLE {webform_views_share_your_core_values_story} CHANGE `submitted` `submitted` INT NULL DEFAULT NULL; Array ( ) in db_change_field() (line 3020 of /Library/WebServer/Documents/git/intranet-refresh/htdocs/includes/database/database.inc).
any solutions?

Hi. I'm trying to untangle this function. I put in the patch from #12. No dice. I got the PDO exception too:

PDOException: SQLSTATE[HY000]: General error: 1347 '[server].webform_views_help_desk' is not BASE TABLE: ALTER TABLE {webform_views_help_desk} CHANGE `submitted` `submitted` INT NULL DEFAULT NULL; Array ( ) in db_change_field() (line 3020 of /mnt/www/html/docroot/includes/database/database.inc).

Do I need to apply patch #1 & #12? Or do I need to write/adjust one of the functions to make this work. I'd really like this to work so I can serve up webform results as views.