A Form to Create/Edit a Materialized View

Parameters

$form_state: The current state of the form (Form API)

$mview_id: The unique ID of the Materialized View to Edit or NULL if creating a new materialized view

Return value

A form array (Form API)

File

tripal_chado/includes/tripal_chado.mviews.inc, line 135

Contains functions for viewing and editing of Materialized Views on a Tripal website.

Code

function tripal_mviews_form($form, &$form_state = NULL, $mview_id = NULL) {

  // set the breadcrumb
  $breadcrumb = array();
  $breadcrumb[] = l('Home', '<front>');
  $breadcrumb[] = l('Administration', 'admin');
  $breadcrumb[] = l('Tripal', 'admin/tripal');
  $breadcrumb[] = l('Chado Schema', 'admin/tripal/storage/chado');
  $breadcrumb[] = l('Materialied Views', 'admin/tripal/storage/chado/mviews');
  drupal_set_breadcrumb($breadcrumb);


  if (!$mview_id) {
    $action = 'Add';
  }
  else {
    $action = 'Edit';
  }

  // set defaults for collapsed fieldsets
  $schema_collapsed = 0;
  $traditional_collapsed = 1;

  $default_name = '';
  $default_mv_table = '';
  $default_mv_specs = '';
  $default_indexed = '';
  $default_mvquery = '';
  $default_special_index = '';
  $default_comment = '';
  $default_modulename = '';
  $default_schema = '';

  // if the view is the older style legacy view then this value get's set to 1
  $is_legacy = 0;


  // get this requested view
  if (strcmp($action, 'Edit') == 0) {
    $sql = "SELECT * FROM {tripal_mviews} WHERE mview_id = :mview_id ";
    $mview = db_query($sql, array(':mview_id' => $mview_id))->fetchObject();

    // set the default values.  If there is a value set in the
    // form_state then let's use that, otherwise, we'll pull
    // the values from the database
    if (array_key_exists('values', $form_state)) {
      $default_name = $form_state['values']['name'];
      $default_mv_table = $form_state['values']['mv_table'];
      $default_mv_specs = $form_state['values']['mv_specs'];
      $default_indexed = $form_state['values']['indexed'];
      $default_mvquery = $form_state['values']['mvquery'];
      $default_special_index = $form_state['values']['special_index'];
      $default_comment = $form_state['values']['comment'];
      $default_modulename = $form_state['values']['modulename'];
    }

    if (!$default_name) {
      $default_name = $mview->name;
    }
    if (!$default_mv_table) {
      $default_mv_table = $mview->mv_table;
    }
    if (!$default_mv_specs) {
      $default_mv_specs = $mview->mv_specs;
    }
    if (!$default_indexed) {
      $default_indexed = $mview->indexed;
    }
    if (!$default_mvquery) {
      $default_mvquery = $mview->query;
    }
    if (!$default_special_index) {
      $default_special_index = $mview->special_index;
    }
    if (!$default_comment) {
      $default_comment = $mview->comment;
    }
    if (!$default_schema) {
      $default_schema = $mview->mv_schema;
    }
    if (!$default_modulename) {
      $default_modulename = $mview->modulename ? $mview->modulename : 'tripal_chado';
    }

    if ($mview->mv_specs) {
      $is_legacy = 1;
    }

    // the mv_table column of the tripal_mviews table always has the table
    // name even if it is a custom table. However, for the sake of the form,
    // we do not want this to show up as the mv_table is needed for the
    // traditional style input.  We'll blank it out if we have a custom
    // table and it will get reset in the submit function using the
    // 'table' value from the schema array
    if ($default_schema) {
      $default_mv_table = '';
    }
    // set which fieldset is collapsed
    if (!$default_schema) {
      $schema_collapsed = 1;
      $traditional_collapsed = 0;
    }
  }

  // Build the form
  $form['action'] = array(
    '#type' => 'value',
    '#value' => $action
  );

  $form['is_legacy'] = array(
    '#type' => 'value',
    '#value' => $is_legacy
  );

  $form['mview_id'] = array(
    '#type' => 'value',
    '#value' => $mview_id
  );

  $form['modulename'] = array(
    '#type' => 'value',
    '#value' => $default_modulename,
  );

  $form['instructions'] = array(
    '#type' => 'fieldset',
    '#title' => 'Instructions',
    '#collapsible' => TRUE,
    '#collapsed' => TRUE,
  );
  $form['instructions']['text'] = array(
    '#type' => 'item',
    '#markup' => t('Materialized views are used to help speed data
      querying, particularly for searching.  A materialized view is essentially
      a database table that is pre-populated with the desired data to search on.
      Rows in the materialized view are typically a combination of data from
      multiple tables with indexes on searchable columns. The table structure
      for materialized views is defined using the ' .
      l('Drupal Schema API', 'https://api.drupal.org/api/drupal/includes!database!schema.inc/group/schemaapi/7', 
      array('attributes' => array('target' => '_blank'))) . '. ' . t('Additionally,
      an SQL statement is provided that populates the table with data. ' .
        'Please note that table names should be all lower-case.')
      ),
  );
  $form['instructions']['example_schema'] = array(
    '#type' => 'item',
    '#markup' => "An example Schema API definition for a materialized view: <pre>
array (
  'description' => 'Stores the type and number of features per organism',
  'table' => 'organism_feature_count',
  'fields' => array (
    'organism_id' => array (
      'type' => 'int',
      'not null' => true,
    ),
    'genus' => array (
      'type' => 'varchar',
      'length' => '255',
      'not null' => true,
    ),
    'species' => array (
      'type' => 'varchar',
      'length' => '255',
      'not null' => true,
    ),
    'common_name' => array (
      'type' => 'varchar',
      'length' => '255',
      'not null' => false,
    ),
    'num_features' => array (
      'type' => 'int',
      'not null' => true,
    ),
    'cvterm_id' => array (
      'type' => 'int',
      'not null' => true,
    ),
    'feature_type' => array (
      'type' => 'varchar',
      'length' => '255',
      'not null' => true,
    ),
  ),
  'indexes' => array (
    'organism_id_idx'  => array ('organism_id'),
    'cvterm_id_idx'    => array ('cvterm_id'),
    'feature_type_idx' => array ('feature_type'),
  ),
)
</pre>"
  );
  $form['instructions']['example_sql'] = array(
    '#type' => 'item',
    '#markup' => "An example SQL statement to populate the table: <pre>
SELECT
    O.organism_id, O.genus, O.species, O.common_name,
    count(F.feature_id) as num_features,
    CVT.cvterm_id, CVT.name as feature_type
 FROM organism O
    INNER JOIN feature F  ON O.Organism_id = F.organism_id
    INNER JOIN cvterm CVT ON F.type_id     = CVT.cvterm_id
 GROUP BY
    O.Organism_id, O.genus, O.species, O.common_name, CVT.cvterm_id, CVT.name
    </pre>"
  );


  $form['name'] = array(
    '#type' => 'textfield',
    '#title' => t('View Name'),
    '#description' => t('Please enter the name for this materialized view.'),
    '#required' => TRUE,
    '#default_value' => $default_name,
  );

  $form['comment'] = array(
    '#type' => 'textarea',
    '#title' => t('MView Description'),
    '#description' => t('Optional.  Please provide a description of the purpose for this materialized vieww.'),
    '#required' => FALSE,
    '#default_value' => $default_comment,
  );

  // add a fieldset for the Drupal Schema API
  $form['schema'] = array(
    '#type' => 'fieldset',
    '#title' => 'Table Schema',
    '#description' => t('Use a ' . l("Drupal Schema API", "https://api.drupal.org/api/drupal/includes!database!schema.inc/group/schemaapi/7", array('attributes' => array('target' => '_blank'))) .
      ' array to describe the table. See the bottom of this page for an example.'),
    '#collapsible' => 1,
    '#collapsed' => $schema_collapsed,
  );

  $form['schema']['schema'] = array(
    '#type' => 'textarea',
    '#title' => t('Schema Array'),
    '#description' => t('Please enter the ' . l("Drupal Schema API", "https://api.drupal.org/api/drupal/includes!database!schema.inc/group/schemaapi/7", array('attributes' => array('target' => '_blank'))) .
      ' compatible array that defines the table. There must also be a "table" key with the name of the table as the value. See the example at the bottom of this page.'),
    '#required' => FALSE,
    '#default_value' => $default_schema,
    '#rows' => 25,
    '#attributes' => array(
      'style' => "font-family:Consolas,Monaco,Lucida Console,Liberation Mono,DejaVu Sans Mono,Bitstream Vera Sans Mono,Courier New, monospace;",
    ),
  );

  // only let folks edit legacy MViews, not create new ones
  if ($is_legacy) {
    // add a fieldset for the Original Table Description fields
    $form['traditional'] = array(
      '#type' => 'fieldset',
      '#title' => 'Legacy MViews Setup',
      '#description' => t('<font color="red">Tripal no longer supports editing of legacy style materialized views. </font> This view will continue to function and you can populate it, however, to update you must convert this view to the newer Drupal Schema API format using the "Table Schema" section above.  Unfortunately, after converting this view to the Schema API and saving, the materialized view be recreated and emptied. You will need to re-populate it. Therefore, you may want to schedule update of this or any other legacy materialized during your next site maintenance.'),
      '#collapsible' => 1,
      '#collapsed' => $traditional_collapsed,
    );

    $form['traditional']['mv_table'] = array(
      '#type' => 'textfield',
      '#title' => t('Table Name'),
      '#description' => t('Please enter the table name that this view will generate in the database.  You can use the schema and table name for querying the view'),
      '#required' => FALSE,
      '#default_value' => $default_mv_table,
      '#attributes' => array('disabled' => 'disabled'),
    );

    $form['traditional']['mv_specs'] = array(
      '#type' => 'textarea',
      '#title' => t('Table Definition'),
      '#description' => t('Please enter the field definitions for this view. Each field should be separated by a comma or enter each field definition on each line.'),
      '#required' => FALSE,
      '#default_value' => $default_mv_specs,
      '#attributes' => array('disabled' => 'disabled'),
    );

    $form['traditional']['indexed'] = array(
      '#type' => 'textarea',
      '#title' => t('Indexed Fields'),
      '#description' => t('Please enter the field names (as provided in the table definition above) that will be indexed for this view.  Separate by a comma or enter each field on a new line.'),
      '#required' => FALSE,
      '#default_value' => $default_indexed,
      '#attributes' => array('disabled' => 'disabled'),
    );

    /**
    $form['traditional']['special_index']= array(
      '#type'          => 'textarea',
      '#title'         => t('View Name'),
      '#description'   => t('Please enter the name for this materialized view.'),
      '#required'      => TRUE,
      '#default_value' => $default_special_index,
    );
    */
  }

  $form['mvquery'] = array(
    '#type' => 'textarea',
    '#title' => t('Query'),
    '#description' => t('Please enter the SQL statement used to populate the table.'),
    '#required' => TRUE,
    '#default_value' => $default_mvquery,
    '#rows' => 25,
    '#attributes' => array(
      'style' => "font-family:Consolas,Monaco,Lucida Console,Liberation Mono,DejaVu Sans Mono,Bitstream Vera Sans Mono,Courier New, monospace;",
    ),
  );

  if ($action == 'Edit') {
    $value = 'Save';
  }
  if ($action == 'Add') {
    $value = 'Add';
  }
  $form['submit'] = array(
    '#type' => 'submit',
    '#value' => t($value),
    '#executes_submit_callback' => TRUE,
  );
  $form['cancel'] = array(
    '#type' => 'markup',
    '#markup' => l('Cancel', 'admin/tripal/storage/chado/mviews'),
  );


  return $form;
}