mviews.php 14 KB

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