mviews.php 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447
  1. <?php
  2. //
  3. // Copyright 2009 Clemson University
  4. //
  5. /**
  6. * Add a materialized view to the chado database to help speed data access.
  7. *
  8. * @param $name
  9. * The name of the materialized view.
  10. * @param $modulename
  11. * The name of the module submitting the materialized view (e.g. 'tripal_library')
  12. * @param $mv_table
  13. * The name of the table to add to chado. This is the table that can be queried.
  14. * @param $mv_specs
  15. * The table definition
  16. * @param $indexed
  17. * The columns that are to be indexed
  18. * @param $query
  19. * The SQL query that loads the materialized view with data
  20. * @param $special_index
  21. * function
  22. *
  23. * @ingroup tripal_core_api
  24. */
  25. function tripal_add_mview ($name,$modulename,$mv_table,$mv_specs,$indexed,$query,$special_index){
  26. $record = new stdClass();
  27. $record->name = $name;
  28. $record->modulename = $modulename;
  29. $record->mv_schema = 'DUMMY';
  30. $record->mv_table = $mv_table;
  31. $record->mv_specs = $mv_specs;
  32. $record->indexed = $indexed;
  33. $record->query = $query;
  34. $record->special_index = $special_index;
  35. // add the record to the tripal_mviews table and if successful
  36. // create the new materialized view in the chado schema
  37. if(drupal_write_record('tripal_mviews',$record)){
  38. // drop the table from chado if it exists
  39. $previous_db = tripal_db_set_active('chado'); // use chado database
  40. if (db_table_exists($mv_table)) {
  41. $sql = "DROP TABLE $mv_table";
  42. db_query($sql);
  43. }
  44. tripal_db_set_active($previous_db); // now use drupal database
  45. // now add the table for this view
  46. $index = '';
  47. if($indexed){
  48. $index = ", CONSTRAINT ". $mv_table . "_index UNIQUE ($indexed) ";
  49. }
  50. $sql = "CREATE TABLE {$mv_table} ($mv_specs $index)";
  51. $previous_db = tripal_db_set_active('chado'); // use chado database
  52. $results = db_query($sql);
  53. tripal_db_set_active($previous_db); // now use drupal database
  54. if($results){
  55. drupal_set_message(t("View '$name' created"));
  56. } else {
  57. // if we failed to create the view in chado then
  58. // remove the record from the tripal_jobs table
  59. $sql = "DELETE FROM {tripal_mviews} ".
  60. "WHERE mview_id = $record->mview_id";
  61. db_query($sql);
  62. }
  63. }
  64. }
  65. /**
  66. * Retrieve the materialized view_id given the name
  67. *
  68. * @param $view_name
  69. * The name of the materialized view
  70. *
  71. * @return
  72. * The unique identifier for the given view
  73. *
  74. * @ingroup tripal_core_api
  75. */
  76. function tripal_mviews_get_mview_id ($view_name){
  77. $sql = "SELECT * FROM {tripal_mviews} ".
  78. "WHERE name = '%s'";
  79. if(db_table_exists('tripal_mviews')){
  80. $mview = db_fetch_object(db_query($sql,$view_name));
  81. if($mview){
  82. return $mview->mview_id;
  83. }
  84. }
  85. return 0;
  86. }
  87. /**
  88. *
  89. *
  90. * @ingroup tripal_core
  91. */
  92. function tripal_mviews_action ($op,$mview_id){
  93. global $user;
  94. $args = array("$mview_id");
  95. if(!$mview_id){
  96. return '';
  97. }
  98. // get this mview details
  99. $sql = "SELECT * FROM {tripal_mviews} WHERE mview_id = $mview_id ";
  100. $mview = db_fetch_object(db_query($sql));
  101. // add a job or perform the action based on the given operation
  102. if($op == 'update'){
  103. tripal_add_job("Update materialized view '$mview->name'",'tripal_core',
  104. 'tripal_update_mview',$args,$user->uid);
  105. }
  106. if($op == 'delete'){
  107. // remove the mview from the tripal_mviews table
  108. $sql = "DELETE FROM {tripal_mviews} ".
  109. "WHERE mview_id = $mview_id";
  110. db_query($sql);
  111. // drop the table from chado if it exists
  112. $previous_db = tripal_db_set_active('chado'); // use chado database
  113. if (db_table_exists($mview->mv_table)) {
  114. $sql = "DROP TABLE $mview->mv_table";
  115. db_query($sql);
  116. }
  117. tripal_db_set_active($previous_db); // now use drupal database
  118. }
  119. return '';
  120. }
  121. /**
  122. * Update a Materialized View
  123. *
  124. * @param $mview_id
  125. * The unique identifier for the materialized view to be updated
  126. *
  127. * @return
  128. * True if successful, false otherwise
  129. *
  130. * @ingroup tripal_core_api
  131. */
  132. function tripal_update_mview ($mview_id){
  133. $sql = "SELECT * FROM {tripal_mviews} WHERE mview_id = %d ";
  134. $mview = db_fetch_object(db_query($sql,$mview_id));
  135. if($mview){
  136. $previous_db = tripal_db_set_active('chado'); // use chado database
  137. $results = db_query("DELETE FROM {$mview->mv_table}");
  138. $results = db_query("INSERT INTO $mview->mv_table ($mview->query)");
  139. tripal_db_set_active($previous_db); // now use drupal database
  140. if($results){
  141. $record = new stdClass();
  142. $record->mview_id = $mview_id;
  143. $record->last_update = time();
  144. drupal_write_record('tripal_mviews',$record,'mview_id');
  145. return 1;
  146. } else {
  147. // TODO -- error handling
  148. return 0;
  149. }
  150. }
  151. }
  152. /**
  153. *
  154. *
  155. * @ingroup tripal_core
  156. */
  157. function tripal_mview_report ($mview_id) {
  158. // get this mview details
  159. $sql = "SELECT * FROM {tripal_mviews} WHERE mview_id = $mview_id ";
  160. $mview = db_fetch_object(db_query($sql));
  161. // create a table with each row containig stats for
  162. // an individual job in the results set.
  163. $return_url = url("admin/tripal/tripal_mviews/");
  164. $output .= "<p><a href=\"$return_url\">Return to table of materialized views.</a></p>";
  165. $output .= "<br />";
  166. $output .= "<p>Details for <b>$mview->name</b>:</p>";
  167. $output .= "<br />";
  168. $output .= "<table class=\"border-table\">";
  169. if($mview->name){
  170. $output .= " <tr>".
  171. " <th>View Name</th>".
  172. " <td>$mview->name</td>".
  173. " </tr>";
  174. }
  175. if($mview->modulename){
  176. $output .= " <tr>".
  177. " <th>Module Name</th>".
  178. " <td>$mview->modulename</td>".
  179. " </tr>";
  180. }
  181. if($mview->mv_table){
  182. $output .= " <tr>".
  183. " <th>Table Name</th>".
  184. " <td>$mview->mv_table</td>".
  185. " </tr>";
  186. }
  187. if($mview->mv_specs){
  188. $output .= " <tr>".
  189. " <th>Table Field Definitions</th>".
  190. " <td>$mview->mv_specs</td>".
  191. " </tr>";
  192. }
  193. if($mview->query){
  194. $output .= " <tr>".
  195. " <th>Query</th>".
  196. " <td><pre>$mview->query</pre></td>".
  197. " </tr>";
  198. }
  199. if($mview->indexed){
  200. $output .= " <tr>".
  201. " <th>Indexed Fields</th>".
  202. " <td>$mview->indexed</td>".
  203. " </tr>";
  204. }
  205. if($mview->special_index){
  206. $output .= " <tr>".
  207. " <th>Special Indexed Fields</th>".
  208. " <td>$mview->speical_index</td>".
  209. " </tr>";
  210. }
  211. if($mview->last_update > 0){
  212. $update = format_date($mview->last_update);
  213. } else {
  214. $update = 'Not yet populated';
  215. }
  216. $output .= " <tr>".
  217. " <th>Last Update</th>".
  218. " <td>$update</td>".
  219. " </tr>";
  220. // build the URLs using the url function so we can handle installations where
  221. // clean URLs are or are not used
  222. $update_url = url("admin/tripal/tripal_mviews/action/update/$mview->mview_id");
  223. $delete_url = url("admin/tripal/tripal_mviews/action/delete/$mview->mview_id");
  224. $edit_url = url("admin/tripal/tripal_mviews/edit/$mview->mview_id");
  225. $output .= "<tr><th>Actions</th>".
  226. "<td> <a href='$update_url'>Update</a>, ".
  227. " <a href='$edit_url'>Edit</a>, ".
  228. " <a href='$delete_url'>Delete</a></td></tr>";
  229. $output .= "</table>";
  230. return $output;
  231. }
  232. /**
  233. *
  234. *
  235. * @ingroup tripal_core
  236. */
  237. function tripal_mviews_report () {
  238. $mviews = db_query("SELECT * FROM {tripal_mviews} ORDER BY name");
  239. // create a table with each row containig stats for
  240. // an individual job in the results set.
  241. $output .= "<table class=\"border-table\">".
  242. " <tr>".
  243. " <th nowrap></th>".
  244. " <th>Name</th>".
  245. " <th>Last_Update</th>".
  246. " <th nowrap></th>".
  247. " </tr>";
  248. while($mview = db_fetch_object($mviews)){
  249. if($mview->last_update > 0){
  250. $update = format_date($mview->last_update);
  251. } else {
  252. $update = 'Not yet populated';
  253. }
  254. // build the URLs using the url function so we can handle installations where
  255. // clean URLs are or are not used
  256. $view_url = url("admin/tripal/tripal_mview/$mview->mview_id");
  257. $update_url = url("admin/tripal/tripal_mviews/action/update/$mview->mview_id");
  258. $delete_url = url("admin/tripal/tripal_mviews/action/delete/$mview->mview_id");
  259. // create the row for the table
  260. $output .= " <tr>";
  261. $output .= " <td><a href='$view_url'>View</a>&nbsp".
  262. " <a href='$update_url'>Update</a></td>".
  263. " <td>$mview->name</td>".
  264. " <td>$update</td>".
  265. " <td><a href='$delete_url'>Delete</a></td>".
  266. " </tr>";
  267. }
  268. $new_url = url("admin/tripal/tripal_mviews/new");
  269. $output .= "</table>";
  270. $output .= "<br />";
  271. $output .= "<p><a href=\"$new_url\">Create a new materialized view.</a></p>";
  272. return $output;
  273. }
  274. /**
  275. *
  276. *
  277. * @ingroup tripal_core
  278. */
  279. function tripal_mviews_form(&$form_state = NULL,$mview_id = NULL){
  280. if(!$mview_id){
  281. $action = 'Add';
  282. } else {
  283. $action = 'Update';
  284. }
  285. // get this requested view
  286. if(strcmp($action,'Update')==0){
  287. $sql = "SELECT * FROM {tripal_mviews} WHERE mview_id = $mview_id ";
  288. $mview = db_fetch_object(db_query($sql));
  289. # set the default values. If there is a value set in the
  290. # form_state then let's use that, otherwise, we'll pull
  291. # the values from the database
  292. $default_name = $form_state['values']['name'];
  293. $default_mv_table = $form_state['values']['mv_table'];
  294. $default_mv_specs = $form_state['values']['mv_specs'];
  295. $default_indexed = $form_state['values']['indexed'];
  296. $default_mvquery = $form_state['values']['mvquery'];
  297. $default_special_index = $form_state['values']['special_index'];
  298. if(!$default_name){
  299. $default_name = $mview->name;
  300. }
  301. if(!$default_mv_table){
  302. $default_mv_table = $mview->mv_table;
  303. }
  304. if(!$default_mv_specs){
  305. $default_mv_specs = $mview->mv_specs;
  306. }
  307. if(!$default_indexed){
  308. $default_indexed = $mview->indexed;
  309. }
  310. if(!$default_mvquery){
  311. $default_mvquery = $mview->query;
  312. }
  313. if(!$default_special_index){
  314. $default_special_index = $mview->special_index;
  315. }
  316. }
  317. // Build the form
  318. $form['action'] = array(
  319. '#type' => 'value',
  320. '#value' => $action
  321. );
  322. $form['mview_id'] = array(
  323. '#type' => 'value',
  324. '#value' => $mview_id
  325. );
  326. $form['name']= array(
  327. '#type' => 'textfield',
  328. '#title' => t('View Name'),
  329. '#description' => t('Please enter the name for this materialized view.'),
  330. '#required' => TRUE,
  331. '#default_value' => $default_name,
  332. '#weight' => 1
  333. );
  334. $form['mv_table']= array(
  335. '#type' => 'textfield',
  336. '#title' => t('Table Name'),
  337. '#description' => t('Please enter the Postgres table name that this view will generate in the database. You can use the schema and table name for querying the view'),
  338. '#required' => TRUE,
  339. '#default_value' => $default_mv_table,
  340. '#weight' => 3
  341. );
  342. $form['mv_specs']= array(
  343. '#type' => 'textarea',
  344. '#title' => t('Table Definition'),
  345. '#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.'),
  346. '#required' => TRUE,
  347. '#default_value' => $default_mv_specs,
  348. '#weight' => 4
  349. );
  350. $form['indexed']= array(
  351. '#type' => 'textarea',
  352. '#title' => t('Indexed Fields'),
  353. '#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.'),
  354. '#required' => FALSE,
  355. '#default_value' => $default_indexed,
  356. '#weight' => 5
  357. );
  358. $form['mvquery']= array(
  359. '#type' => 'textarea',
  360. '#title' => t('Query'),
  361. '#description' => t('Please enter the SQL statement used to populate the table.'),
  362. '#required' => TRUE,
  363. '#default_value' => $default_mvquery,
  364. '#weight' => 6
  365. );
  366. /**
  367. $form['special_index']= array(
  368. '#type' => 'textarea',
  369. '#title' => t('View Name'),
  370. '#description' => t('Please enter the name for this materialized view.'),
  371. '#required' => TRUE,
  372. '#default_value' => $default_special_index,
  373. '#weight' => 7
  374. );
  375. */
  376. $form['submit'] = array (
  377. '#type' => 'submit',
  378. '#value' => t($action),
  379. '#weight' => 8,
  380. '#executes_submit_callback' => TRUE,
  381. );
  382. $form['#redirect'] = 'admin/tripal/tripal_mviews';
  383. return $form;
  384. }
  385. /**
  386. *
  387. *
  388. * @ingroup tripal_core
  389. */
  390. function tripal_mviews_form_submit($form, &$form_state){
  391. $action = $form_state['values']['action'];
  392. if(strcmp($action,'Update')==0){
  393. $record = new stdClass();
  394. $record->mview_id = $form_state['values']['mview_id'];
  395. $record->name = $form_state['values']['name'];
  396. $record->mv_table = $form_state['values']['mv_table'];
  397. $record->mv_specs = $form_state['values']['mv_specs'];
  398. $record->indexed = $form_state['values']['indexed'];
  399. $record->query = $form_state['values']['mvquery'];
  400. $record->special_index = $form_state['values']['special_index'];
  401. // add the record to the tripal_mviews table and if successful
  402. // create the new materialized view in the chado schema
  403. if(drupal_write_record('tripal_mviews',$record,'mview_id')){
  404. drupal_set_message('View updated successfullly');
  405. } else {
  406. drupal_set_message('View update failed');
  407. }
  408. }
  409. else if(strcmp($action,'Add')==0){
  410. tripal_add_mview ($form_state['values']['name'], 'tripal_core',
  411. $form_state['values']['mv_table'], $form_state['values']['mv_specs'],
  412. $form_state['values']['indexed'], $form_state['values']['mvquery'],
  413. $form_state['values']['special_index']);
  414. }
  415. else {
  416. drupal_set_message("No action performed.");
  417. }
  418. return '';
  419. }