Tripal v1.0 (6.x-1.0)
|
00001 <?php 00045 function tripal_add_mview($name, $modulename, $mv_table, $mv_specs, $indexed, 00046 $query, $special_index, $comment = NULL, $mv_schema = NULL) { 00047 00048 // get the table name from the schema array 00049 $schema_arr = array(); 00050 if ($mv_schema) { 00051 // if the schema is provided as a string then convert it to an array 00052 if (!is_array($mv_schema)) { 00053 eval("\$schema_arr = $mv_schema;"); 00054 } 00055 // if the schema is provided as an array then create a string 00056 // copy of it for storage in the mview 00057 else { 00058 $schema_arr = $mv_schema; 00059 $mv_schema = var_export($schema_arr, 1); 00060 } 00061 $mv_table = $schema_arr['table']; 00062 } 00063 00064 // Create a new record 00065 $record = new stdClass(); 00066 $record->name = $name; 00067 $record->modulename = $modulename; 00068 $record->mv_table = $mv_table; 00069 $record->mv_specs = $mv_specs; 00070 $record->indexed = $indexed; 00071 $record->query = $query; 00072 $record->special_index = $special_index; 00073 $record->comment = $comment; 00074 $record->mv_schema = $mv_schema; 00075 00076 // add the record to the tripal_mviews table and if successful 00077 // create the new materialized view in the chado schema 00078 if (drupal_write_record('tripal_mviews', $record)) { 00079 00080 // drop the table from chado if it exists 00081 $previous_db = tripal_db_set_active('chado'); // use chado database 00082 if (db_table_exists($mv_table)) { 00083 $sql = "DROP TABLE $mv_table"; 00084 db_query($sql); 00085 } 00086 tripal_db_set_active($previous_db); // now use drupal database 00087 00088 // now construct the indexes 00089 $index = ''; 00090 if ($indexed) { 00091 // add to the array of values 00092 $vals = preg_split("/[\n,]+/", $indexed); 00093 $index = ''; 00094 foreach ($vals as $field) { 00095 $field = trim($field); 00096 $index .= "CREATE INDEX idx_${mv_table}_${field} ON $mv_table ($field);"; 00097 } 00098 } 00099 00100 // create the table differently depending on if it the traditional method 00101 // or the Drupal Schema API method 00102 if ($mv_schema) { 00103 if (!tripal_core_create_custom_table ($ret, $mv_table, $schema_arr, 0)) { 00104 drupal_set_message(t("Could not create the materialized view. Check Drupal error report logs."), 'error'); 00105 } 00106 else { 00107 drupal_set_message(t("View '%name' created", array('%name' => $name))); 00108 } 00109 } 00110 else { 00111 // add the table to the database 00112 $sql = "CREATE TABLE {$mv_table} ($mv_specs); $index"; 00113 $previous_db = tripal_db_set_active('chado'); // use chado database 00114 $results = db_query($sql); 00115 tripal_db_set_active($previous_db); // now use drupal database 00116 if ($results) { 00117 drupal_set_message(t("View '%name' created", array('%name' => $name))); 00118 } 00119 else { 00120 drupal_set_message(t("Failed to create the materialized view table: '%mv_table'", array('%mv_table' => $mv_table)), 'error'); 00121 } 00122 } 00123 } 00124 } 00125 00157 function tripal_edit_mview($mview_id, $name, $modulename, $mv_table, $mv_specs, 00158 $indexed, $query, $special_index, $comment = NULL, $mv_schema = NULL) { 00159 00160 // get the table name from the schema array 00161 $schema_arr = array(); 00162 if ($mv_schema) { 00163 // get the schema from the mv_specs and use it to add the custom table 00164 eval("\$schema_arr = $mv_schema;"); 00165 $mv_table = $schema_arr['table']; 00166 } 00167 00168 $record = new stdClass(); 00169 $record->mview_id = $mview_id; 00170 $record->name = $name; 00171 $record->modulename = $modulename; 00172 $record->query = $query; 00173 $record->last_update = 0; 00174 $record->status = ''; 00175 $record->comment = $comment; 00176 00177 // get the view before we update and check to see if the table structure has 00178 // changed. IF so, then we want to drop and recreate the table. If not, then 00179 // just save the updated SQL. 00180 $create_table = 1; 00181 $sql = "SELECT * FROM {tripal_mviews} WHERE mview_id = %d"; 00182 $mview = db_fetch_object(db_query($sql, $mview_id)); 00183 if($mview->mv_schema == $mv_schema and $mview->mv_table == $mv_table and 00184 $mview->mv_specs == $mv_specs and $mview->indexed == $indexed and 00185 $mview->special_index == $special_index) { 00186 // nothing has changed so simpy update the SQL and other fields 00187 $create_table = 0; 00188 } 00189 else { 00190 // add in the table structure fields 00191 $record->mv_schema = $mv_schema; 00192 $record->mv_table = $mv_table; 00193 $record->mv_specs = $mv_specs; 00194 $record->indexed = $indexed; 00195 $record->query = $query; 00196 $record->special_index = $special_index; 00197 } 00198 00199 // if we are going to create the table then we must first drop it if it exists 00200 if ($create_table) { 00201 $previous_db = tripal_db_set_active('chado'); // use chado database 00202 if (db_table_exists($mview->mv_table)) { 00203 $sql = "DROP TABLE %s"; 00204 db_query($sql, $mview->mv_table); 00205 drupal_set_message(t("View '%name' dropped", array('%name' => $name))); 00206 } 00207 tripal_db_set_active($previous_db); // now use drupal database 00208 } 00209 00210 // update the record to the tripal_mviews table and if successful 00211 // create the new materialized view in the chado schema 00212 if (drupal_write_record('tripal_mviews', $record, 'mview_id')) { 00213 // construct the indexes SQL if needed 00214 $index = ''; 00215 if ($indexed) { 00216 // add to the array of values 00217 $vals = preg_split("/[\n,]+/", $indexed); 00218 $index = ''; 00219 foreach ($vals as $field) { 00220 $field = trim($field); 00221 $index .= "CREATE INDEX idx_${mv_table}_${field} ON $mv_table ($field);"; 00222 } 00223 } 00224 00225 // re-create the table differently depending on if it the traditional method 00226 // or the Drupal Schema API method 00227 if ($create_table and $mv_schema) { 00228 if (!tripal_core_create_custom_table($ret, $mv_table, $schema_arr, 0)) { 00229 drupal_set_message(t("Could not create the materialized view. Check Drupal error report logs.")); 00230 } 00231 else { 00232 drupal_set_message(t("View '%name' created", array('%name' => $name))); 00233 } 00234 } 00235 if ($create_table and !$mv_schema) { 00236 $sql = "CREATE TABLE {$mv_table} ($mv_specs); $index"; 00237 $results = chado_query($sql); 00238 if ($results) { 00239 drupal_set_message(t("View '%name' created. All records cleared. Please re-populate the view.", 00240 array('%name' => $name))); 00241 } 00242 else { 00243 drupal_set_message(t("Failed to create the materialized view table: '%mv_table'", 00244 array('%mv_table' => $mv_table)), 'error'); 00245 } 00246 } 00247 if (!$create_table) { 00248 $message = "View '%name' updated. All records remain. "; 00249 if ($query != $mview->query) { 00250 $message .= "Please repopulate the view to use updated query."; 00251 } 00252 drupal_set_message(t($message, array('%name' => $name))); 00253 } 00254 } 00255 else { 00256 drupal_set_message(t("Failed to update the materialized view: '%mv_table'", 00257 array('%mv_table' => $mv_table)), 'error'); 00258 } 00259 } 00260 00272 function tripal_mviews_get_mview_id($view_name) { 00273 $sql = "SELECT * FROM {tripal_mviews} ". 00274 "WHERE name = '%s'"; 00275 if (db_table_exists('tripal_mviews')) { 00276 $mview = db_fetch_object(db_query($sql, $view_name)); 00277 if ($mview) { 00278 return $mview->mview_id; 00279 } 00280 } 00281 00282 return FALSE; 00283 } 00284 00297 function tripal_mviews_action($op, $mview_id, $redirect = FALSE) { 00298 global $user; 00299 00300 $args = array("$mview_id"); 00301 if (!$mview_id) { 00302 return ''; 00303 } 00304 00305 // get this mview details 00306 $sql = "SELECT * FROM {tripal_mviews} WHERE mview_id = %d"; 00307 $mview = db_fetch_object(db_query($sql, $mview_id)); 00308 00309 // add a job or perform the action based on the given operation 00310 if ($op == 'update') { 00311 tripal_add_job("Populate materialized view '$mview->name'", 'tripal_core', 00312 'tripal_update_mview', $args, $user->uid); 00313 } 00314 if ($op == 'delete') { 00315 // remove the mview from the tripal_mviews table 00316 $sql = "DELETE FROM {tripal_mviews} ". 00317 "WHERE mview_id = $mview_id"; 00318 db_query($sql); 00319 // drop the table from chado if it exists 00320 $previous_db = tripal_db_set_active('chado'); // use chado database 00321 if (db_table_exists($mview->mv_table)) { 00322 $sql = "DROP TABLE $mview->mv_table"; 00323 db_query($sql); 00324 } 00325 tripal_db_set_active($previous_db); // now use drupal database 00326 } 00327 00328 // Redirect the user 00329 if ($redirect) { 00330 drupal_goto("admin/tripal/mviews"); 00331 } 00332 } 00333 00345 function tripal_update_mview($mview_id) { 00346 $sql = "SELECT * FROM {tripal_mviews} WHERE mview_id = %d "; 00347 $mview = db_fetch_object(db_query($sql, $mview_id)); 00348 if ($mview) { 00349 // execute the query inside a transaction so that it doesn't destroy existing data 00350 // that may leave parts of the site unfunctional 00351 tripal_db_start_transaction(); 00352 $previous_db = tripal_db_set_active('chado'); // use chado database 00353 $results = db_query("DELETE FROM {%s}", $mview->mv_table); 00354 $results = db_query("INSERT INTO {%s} ($mview->query)", $mview->mv_table); 00355 tripal_db_set_active($previous_db); // now use drupal database 00356 if ($results) { 00357 // commit the transaction 00358 tripal_db_commit_transaction(); 00359 $sql = "SELECT count(*) as cnt FROM {%s}"; 00360 $previous_db = tripal_db_set_active('chado'); // use chado database 00361 $count = db_fetch_object(db_query($sql, $mview->mv_table)); 00362 tripal_db_set_active($previous_db); // now use drupal database 00363 $record = new stdClass(); 00364 $record->mview_id = $mview_id; 00365 $record->last_update = time(); 00366 $record->status = "Populated with " . number_format($count->cnt) . " rows"; 00367 drupal_write_record('tripal_mviews', $record, 'mview_id'); 00368 return TRUE; 00369 } 00370 else { 00371 // rollback the transaction 00372 tripal_db_rollback_transaction(); 00373 // print and save the error message 00374 $record = new stdClass(); 00375 $record->mview_id = $mview_id; 00376 $record->status = "ERROR populating. See Drupal's recent log entries for details."; 00377 print $record->status . "\n"; 00378 drupal_write_record('tripal_mviews', $record, 'mview_id'); 00379 return FALSE; 00380 } 00381 } 00382 }