123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505 |
- <?php
- function tripal_mview_report($mview_id) {
-
- $sql = "SELECT * FROM {tripal_mviews} WHERE mview_id = :mview_id";
- $results = db_query($sql, array(':mview_id' => $mview_id));
- $mview = $results->fetchObject();
-
- $rows = array();
-
-
- $output = "<p>" . l("Return to table of materialized views", "admin/tripal/schema/mviews/") . "</p>";
- $output .= "<p>Details for <b>$mview->name</b>:</p>";
-
-
-
- $update_url = url("admin/tripal/schema/mviews/action/update/$mview->mview_id");
- $delete_url = url("admin/tripal/schema/mviews/action/delete/$mview->mview_id");
- $edit_url = url("admin/tripal/schema/mviews/edit/$mview->mview_id");
- $rows[] = array('Actions', "<a href='$update_url'>Populate</a>, <a href='$edit_url'>Edit</a>, <a href='$delete_url'>Delete</a>");
-
- if ($mview->last_update > 0) {
- $update = format_date($mview->last_update);
- }
- else {
- $update = 'Not yet populated';
- }
- $rows[] = array('Last Update', $update);
- if ($mview->name) {
- $rows[] = array('View Name', $mview->name);
- }
- if ($mview->modulename) {
- $rows[] = array('Module Name', $mview->modulename);
- }
- if ($mview->mv_table) {
- $rows[] = array('Table Name', $mview->mv_table);
- }
- if ($mview->mv_specs) {
- $rows[] = array('Table Field Definitions', $mview->mv_specs);
- }
- if ($mview->query) {
- $rows[] = array('Query', "<textarea rows=\"15\" cols=\"120\" style=\"font-family:Consolas,Monaco,Lucida Console,Liberation Mono,DejaVu Sans Mono,Bitstream Vera Sans Mono,Courier New, monospace;\">" . $mview->query . "</textarea>");
- }
- if ($mview->indexed) {
- $rows[] = array('Indexed Fields', $mview->indexed);
- }
- if ($mview->special_index) {
- $rows[] = array('Special Indexed Fields', $mview->special_index);
- }
- if ($mview->mv_schema) {
- $rows[] = array('Drupal Schema API Definition', "<textarea rows=\"20\" cols=\"120\" style=\"font-family:Consolas,Monaco,Lucida Console,Liberation Mono,DejaVu Sans Mono,Bitstream Vera Sans Mono,Courier New, monospace;\">" . $mview->mv_schema . "</textarea>");
- }
-
- $header = array('Detail', 'Value');
- $table = array(
- 'header' => $header,
- 'rows' => $rows,
- 'attributes' => array(),
- 'sticky' => FALSE,
- 'caption' => '',
- 'colgroups' => array(),
- 'empty' => 'There are no materialized views',
- );
- $table = theme_table($table);
- $output .= $table;
- return $output;
- }
- function tripal_mviews_report() {
- $header = array('', 'MView Name', 'Last Update', 'Status', 'Description', '');
- $rows = array();
- $mviews = db_query("SELECT * FROM {tripal_mviews} ORDER BY name");
- foreach ($mviews as $mview) {
- if ($mview->last_update > 0) {
- $update = format_date($mview->last_update);
- }
- else {
- $update = 'Not yet populated';
- }
- $rows[] = array(
- l(t('View'), "admin/tripal/schema/mviews/report/$mview->mview_id") . " | " .
- l(t('Edit'), "admin/tripal/schema/mviews/edit/$mview->mview_id") . " | " .
- l(t('Populate'), "admin/tripal/schema/mviews/action/update/$mview->mview_id"),
- $mview->name,
- $update,
- $mview->status,
- $mview->comment,
- l(t('Delete'), "admin/tripal/schema/mviews/action/delete/$mview->mview_id"),
- );
- }
- $rows[] = array(
- 'data' => array(
- array('data' => l(t('Create a new materialized view.'), "admin/tripal/schema/mviews/new"),
- 'colspan' => 6),
- )
- );
- $page = '</p>' . t("Materialized Views (MViews) are custom tables populated with a defined SQL statement.
- Because Chado is highly normalized and highly constrained it serves as a wonderful
- data storage platform, but unfortunately some queries may be slow. MViews alleviate slowness by aggregating data
- into tables that are more easy to query. Use MViews to create tables for custom search pages or custom Tripal
- module development.") . '</p>';
- $page .= '<p><b>' . t("MViews behaves in the following way:") . '</b><ul>' .
- '<li>' . t("The SQL statement defined for an MVIEW will be used to populate the table") . '</li>' .
- '<li>' . t("Altering the table structure of an MView will cause the MView table to be dropped and recreated. All records in the MView will be lost.") . '</li>' .
- '<li>' . t("Altering the query of an existing view will not change the MView table. No records will be lost. ") . '</li>' .
- '<li>' . t("Repopulating an MView that is already populated will result in replacement of all records.") . '</li>' .
- '<li>' . t("A database transaction will be used when populating MViews. Therefore replacement of records does not occur until the query completes. Any search forms or pages dependent on the MView will continue to function.") . '</li>' .
- '</ul></p>';
- $page .= '<b>' . t("Existing MViews") . '</b>';
- $table = array(
- 'header' => $header,
- 'rows' => $rows,
- 'attributes' => array(),
- 'sticky' => FALSE,
- 'caption' => '',
- 'colgroups' => array(),
- 'empty' => 'There are no materialized views',
- );
- $page .= theme_table($table);
- return $page;
- }
- function tripal_mviews_form($form, &$form_state = NULL, $mview_id = NULL) {
- if (!$mview_id) {
- $action = 'Add';
- }
- else {
- $action = 'Edit';
- }
-
- $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 = '';
-
-
- $is_legacy = 0;
-
-
-
- if (strcmp($action, 'Edit') == 0 ) {
- $sql = "SELECT * FROM {tripal_mviews} WHERE mview_id = :mview_id ";
- $results = db_query($sql, array(':mview_id' => $mview_id));
- $mview = $results->fetchObject();
-
-
-
- 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_core';
- }
-
- if ($mview->mv_specs) {
- $is_legacy = 1;
- }
-
-
-
-
-
-
- if ($default_schema) {
- $default_mv_table = '';
- }
-
- if (!$default_schema) {
- $schema_collapsed = 1;
- $traditional_collapsed = 0;
- }
- }
-
- $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['return_link'] = array(
- '#type' => 'item',
- '#description' => l("Return to table of materialized views", "admin/tripal/schema/mviews/"),
- );
-
- $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,
- );
-
- $form['schema'] = array(
- '#type' => 'fieldset',
- '#title' => 'Drupal Schema API Setup',
- '#description' => t('Use the Drupal Schema API array to describe a table. The benefit is that it ' .
- 'can be fully integrated with Tripal Views. Tripal supports an extended ' .
- 'array format to allow for descriptoin of foreign key relationships.'),
- '#collapsible' => 1,
- '#collapsed' => $schema_collapsed ,
- );
- $form['schema']['schema']= array(
- '#type' => 'textarea',
- '#title' => t('Schema Array'),
- '#description' => t('Please enter the Drupal Schema API compatible array that defines the table.'),
- '#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;",
- ),
- );
-
- if ($is_legacy) {
-
- $form['traditional'] = array(
- '#type' => 'fieldset',
- '#title' => 'Legacy MViews Setup',
- '#description' => t('Traditionally MViews were created by specifying PostgreSQL style ' .
- 'column types. This method can be used but is deprecated in favor of the ' .
- 'newer Drupal schema API method provided above. In rare cases where the Drupal Schema API ' .
- 'does not support a desired data type the Legacy Mviews should be used'),
- '#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,
- );
-
- $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,
- );
-
- $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,
- );
-
-
- }
- $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),
- '#weight' => 9,
- '#executes_submit_callback' => TRUE,
- );
- $form['#redirect'] = 'admin/tripal/schema/mviews';
- return $form;
- }
- function tripal_mviews_form_validate($form, &$form_state) {
- $action = $form_state['values']['action'];
- $mview_id = $form_state['values']['mview_id'];
- $name = $form_state['values']['name'];
- $is_legacy = $form_state['values']['is_legacy'];
- $query = $form_state['values']['mvquery'];
- if ($is_legacy) {
- $mv_table = $form_state['values']['mv_table'];
- $mv_specs = $form_state['values']['mv_specs'];
- $indexed = $form_state['values']['indexed'];
- $special_index = '';
- }
- else {
- $mv_table = '';
- $mv_specs = '';
- $indexed = '';
- $special_index = '';
- }
- $comment = $form_state['values']['comment'];
- $schema = $form_state['values']['schema'];
- if ($schema and ($mv_table or $mv_specs or $indexed or $special_index)) {
- form_set_error($form_state['values']['schema'],
- t('You can create an MView using the Drupal Schema API method or the ' .
- 'traditional method but not both.'));
- }
- if (!$schema) {
- if (!$mv_specs) {
- form_set_error($form_state['values']['mv_specs'],
- t('The Table Definition field is required.'));
- }
- if (!$mv_table) {
- form_set_error($form_state['values']['mv_table'],
- t('The Table Name field is required.'));
- }
- }
-
- if ($schema) {
- $success = eval("\$schema_array = $schema;");
- if ($success === FALSE) {
- $error = error_get_last();
- form_set_error($form_state['values']['schema'],
- t("The schema array is improperly formatted. Parse Error : " . $error["message"]));
- }
- if (!array_key_exists('table', $schema_array)) {
- form_set_error($form_state['values']['schema'],
- t("The schema array must have key named 'table'"));
- }
-
- }
- }
- function tripal_mviews_form_submit($form, &$form_state) {
- $ret = array();
- $action = $form_state['values']['action'];
- $mview_id = $form_state['values']['mview_id'];
- $name = $form_state['values']['name'];
- $is_legacy = $form_state['values']['is_legacy'];
- $query = $form_state['values']['mvquery'];
- if ($is_legacy) {
- $mv_table = $form_state['values']['mv_table'];
- $mv_specs = $form_state['values']['mv_specs'];
- $indexed = $form_state['values']['indexed'];
- $special_index = '';
- }
- else {
- $mv_table = '';
- $mv_specs = '';
- $indexed = '';
- $special_index = '';
- }
- $comment = $form_state['values']['comment'];
- $schema = $form_state['values']['schema'];
- $modulename = $form_state['values']['modulename'];
- if (!$modulename) {
- $modulename = 'tripal_core';
- }
- if (strcmp($action, 'Edit') == 0) {
- tripal_edit_mview($mview_id, $name, $modulename, $mv_table, $mv_specs,
- $indexed, $query, $special_index, $comment, $schema);
- }
- elseif (strcmp($action, 'Add') == 0) {
- tripal_add_mview($name, $modulename, $mv_table, $mv_specs,
- $indexed, $query, $special_index, $comment, $schema);
- }
- else {
- drupal_set_message(t("No action performed."));
- }
- return '';
- }
|