mviews.php 14 KB

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