tripal_chado.mviews.api.inc 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442
  1. <?php
  2. /**
  3. * @file
  4. * Provides an application programming interface (API) to manage materialized
  5. * views in Chado.
  6. */
  7. /**
  8. * @defgroup tripal_mviews_api Chado Materalized Views
  9. * @ingroup tripal_chado_api
  10. * @{
  11. * Provides an application programming interface (API) to manage materialized
  12. * views in Chado The Perl-based chado comes with an interface for managing
  13. * materialzed views. This API provides an alternative Drupal-based method.
  14. * @}
  15. */
  16. /**
  17. * Add a materialized view to the chado database.
  18. *
  19. * @param $name
  20. * The name of the materialized view.
  21. * @param $modulename
  22. * The name of the module submitting the materialized view
  23. * (e.g. 'tripal_library').
  24. * @param $mv_schema
  25. * If using the newer Schema API array to define the materialized view then
  26. * this variable should contain the array or a string representation of the
  27. * array.
  28. * @param $query
  29. * The SQL query that loads the materialized view with data.
  30. * @param $comment
  31. * A string containing a description of the materialized view.
  32. * @param $redirect
  33. * Optional (default: TRUE). By default this function redirects back to
  34. * admin pages. However, when called by Drush we don't want to redirect. This
  35. * parameter allows this to be used as a true API function.
  36. *
  37. * @return
  38. * TRUE if the materialized view was successfully added, FALSE otherwise.
  39. *
  40. * @ingroup tripal_mviews_api
  41. */
  42. function chado_add_mview($name, $modulename, $mv_schema, $query, $comment = NULL, $redirect = TRUE) {
  43. if (!array_key_exists('table', $mv_schema)) {
  44. tripal_report_error('tripal_chado', TRIPAL_ERROR,
  45. 'Must have a table name when creating an mview.', array());
  46. return FALSE;
  47. }
  48. $mv_table = $mv_schema['table'];
  49. // See if the mv_table name already exsists.
  50. $mview_id = db_select('tripal_mviews', 'tm')
  51. ->fields('tm', array('mview_id'))
  52. ->condition('name', $name)
  53. ->execute()
  54. ->fetchField();
  55. // Check that the materialized view actually exists and if not,
  56. // remove the entry from tripal_mviews.
  57. if ($mview_id AND !chado_table_exists($name)) {
  58. db_delete('tripal_mviews')
  59. ->condition('mview_id', $mview_id)
  60. ->execute();
  61. $mview_id = FALSE;
  62. }
  63. if(!$mview_id) {
  64. $transaction = db_transaction();
  65. try {
  66. // Create a new record.
  67. $record = new stdClass();
  68. $record->name = $name;
  69. $record->modulename = $modulename;
  70. $record->mv_table = $mv_table;
  71. $record->query = $query;
  72. $record->comment = $comment;
  73. // Convert the schema into a string format.
  74. $str_schema = var_export($mv_schema, TRUE);
  75. $str_schema = preg_replace('/=>\s+\n\s+array/', '=> array', $str_schema);
  76. $record->mv_schema = $str_schema;
  77. // Add the record to the tripal_mviews table and if successful
  78. // create the new materialized view in the chado schema.
  79. if (drupal_write_record('tripal_mviews', $record)) {
  80. // Drop the table from chado if it exists.
  81. if (chado_table_exists($mv_table)) {
  82. $sql = 'DROP TABLE {' . $mv_table . '}';
  83. chado_query($sql);
  84. }
  85. // Create the table.
  86. chado_create_custom_table($mv_table, $mv_schema, 0, $record->mview_id, $redirect);
  87. }
  88. }
  89. catch (Exception $e) {
  90. $transaction->rollback();
  91. watchdog_exception('tripal_chado', $e);
  92. $error = _drupal_decode_exception($e);
  93. tripal_report_error('tripal_chado', TRIPAL_ERROR,
  94. "Could not create the materialized view %table_name: %message.",
  95. array('%table_name' => $name, '%message' => $error['!message']));
  96. return FALSE;
  97. }
  98. drupal_set_message(t("Materialized view '%name' created", array('%name' => $name)));
  99. }
  100. else {
  101. drupal_set_message(t("Materialized view, $name, already exists. Skipping creation.", array('%name' => $name)));
  102. }
  103. return TRUE;
  104. }
  105. /**
  106. * Edits a materialized view to the chado database to help speed data access.
  107. * This function supports the older style where postgres column specifications
  108. * are provided using the $mv_table, $mv_specs and $indexed variables. It also
  109. * supports the newer preferred method where the materialized view is described
  110. * using the Drupal Schema API array.
  111. *
  112. * @param $mview_id
  113. * The mview_id of the materialized view to edit.
  114. * @param $name
  115. * The name of the materialized view.
  116. * @param $modulename
  117. * The name of the module submitting the materialized view
  118. * (e.g. 'tripal_library').
  119. * @param $mv_table
  120. * The name of the table to add to chado. This is the table that can be
  121. * queried.
  122. * @param $mv_specs
  123. * The table definition.
  124. * @param $indexed
  125. * The columns that are to be indexed.
  126. * @param $query
  127. * The SQL query that loads the materialized view with data.
  128. * @param $special_index
  129. * currently not used.
  130. * @param $comment
  131. * A string containing a description of the materialized view.
  132. * @param $mv_schema
  133. * If using the newer Schema API array to define the materialized view then
  134. * this variable should contain the array.
  135. *
  136. * @ingroup tripal_mviews_api
  137. */
  138. function chado_edit_mview($mview_id, $name, $modulename, $mv_table, $mv_specs,
  139. $indexed, $query, $special_index, $comment = NULL, $mv_schema = NULL) {
  140. $transaction = db_transaction();
  141. try {
  142. // get the table name from the schema array
  143. $schema_arr = array();
  144. if ($mv_schema) {
  145. // get the schema from the mv_specs and use it to add the custom table
  146. eval("\$schema_arr = $mv_schema;");
  147. $mv_table = $schema_arr['table'];
  148. }
  149. $record = new stdClass();
  150. $record->mview_id = $mview_id;
  151. $record->name = $name;
  152. $record->modulename = $modulename;
  153. $record->query = $query;
  154. $record->last_update = 0;
  155. $record->status = '';
  156. $record->comment = $comment;
  157. $record->mv_schema = $mv_schema;
  158. $record->mv_table = $mv_table;
  159. // update the record to the tripal_mviews table
  160. drupal_write_record('tripal_mviews', $record, 'mview_id');
  161. // get the view before we update and check to see if the table structure has
  162. // changed. If so, then we want to drop and recreate the table. If not, then
  163. // just save the updated SQL.
  164. $create_table = 1;
  165. $sql = "SELECT * FROM {tripal_mviews} WHERE mview_id = :mview_id";
  166. $results = db_query($sql, array(':mview_id' => $mview_id));
  167. $mview = $results->fetchObject();
  168. if ($mview->mv_schema == $mv_schema and $mview->mv_table == $mv_table) {
  169. chado_create_custom_table($mv_table, $schema_arr, 0, $record->mview_id);
  170. drupal_set_message(t("Materialized view '%name' created", array('%name' => $name)));
  171. }
  172. else {
  173. $message = "View '%name' updated. All records remain. ";
  174. if ($query != $mview->query) {
  175. $message .= "Please repopulate the view to use the updated query.";
  176. }
  177. drupal_set_message(t($message, array('%name' => $name)));
  178. }
  179. // construct the indexes SQL if needed
  180. $index = '';
  181. if ($indexed) {
  182. // add to the array of values
  183. $vals = preg_split("/[\n,]+/", $indexed);
  184. $index = '';
  185. foreach ($vals as $field) {
  186. $field = trim($field);
  187. $index .= "CREATE INDEX idx_${mv_table}_${field} ON $mv_table ($field);";
  188. }
  189. }
  190. }
  191. catch (Exception $e) {
  192. $transaction->rollback();
  193. watchdog_exception('tripal_chado', $e);
  194. $error = _drupal_decode_exception($e);
  195. drupal_set_message(t("Could not update materialized view '%table_name': %message.",
  196. array('%table_name' => $mv_table, '%message' => $error['!message'])), 'error');
  197. return FALSE;
  198. }
  199. }
  200. /**
  201. * Retrieve the materialized view_id given the name.
  202. *
  203. * @param $view_name
  204. * The name of the materialized view.
  205. *
  206. * @return
  207. * The unique identifier for the given view.
  208. *
  209. * @ingroup tripal_mviews_api
  210. */
  211. function chado_get_mview_id($view_name) {
  212. if (db_table_exists('tripal_mviews')) {
  213. $sql = "SELECT * FROM {tripal_mviews} WHERE name = :name";
  214. $results = db_query($sql, array(':name' => $view_name));
  215. $mview = $results->fetchObject();
  216. if ($mview) {
  217. return $mview->mview_id;
  218. }
  219. }
  220. return FALSE;
  221. }
  222. /**
  223. * Retrieves the list of materialized views in this site.
  224. *
  225. * @returns
  226. * An associative array where the key and value pairs are the table names.
  227. *
  228. * @ingroup tripal_mviews_api
  229. */
  230. function chado_get_mview_table_names() {
  231. $sql = "SELECT name FROM {tripal_mviews}";
  232. $resource = db_query($sql);
  233. $tables = array();
  234. foreach ($resource as $r) {
  235. $tables[$r->name] = $r->name;
  236. }
  237. asort($tables);
  238. return $tables;
  239. }
  240. /**
  241. * Populates the specified Materialized View.
  242. *
  243. * @param $mview_id
  244. * The unique ID of the materialized view for the action to be performed on.
  245. *
  246. * @ingroup tripal_mviews_api
  247. */
  248. function chado_refresh_mview($mview_id) {
  249. global $user;
  250. if (!$mview_id) {
  251. tripal_report_error('tripal_chado', TRIPAL_ERROR,
  252. 'Must provide an mview_id when refreshing an mview.', array());
  253. return FALSE;
  254. }
  255. // Get this mview details.
  256. $sql = "SELECT * FROM {tripal_mviews} WHERE mview_id = :mview_id";
  257. $results = db_query($sql, array(':mview_id' => $mview_id));
  258. $mview = $results->fetchObject();
  259. // Add a job to populate the mview.
  260. $args = array("$mview_id");
  261. tripal_add_job("Populate materialized view '$mview->name'", 'tripal_chado',
  262. 'chado_populate_mview', $args, $user->uid);
  263. }
  264. /**
  265. * Retrieves the list of materialized view IDs and their names.
  266. *
  267. * @return
  268. * An array of objects with the following properties: mview_id, name.
  269. *
  270. * @ingroup tripal_mviews_api
  271. *
  272. */
  273. function chado_get_mviews() {
  274. $results = db_select('tripal_mviews', 'tm')
  275. ->fields('tm', array('mview_id', 'name'))
  276. ->execute();
  277. $list = array();
  278. while ($mview = $results->fetchObject()) {
  279. $list[] = $mview;
  280. }
  281. return $list;
  282. }
  283. /**
  284. * Does the specified action for the specified Materialized View.
  285. *
  286. * @param $op
  287. * The action to be taken. One of update or delete.
  288. * @param $mview_id
  289. * The unique ID of the materialized view for the action to be performed on.
  290. *
  291. * @return
  292. * TRUE if the deletion was a success, FALSE on error.
  293. *
  294. * @ingroup tripal_mviews_api
  295. */
  296. function chado_delete_mview($mview_id) {
  297. global $user;
  298. if (!$mview_id) {
  299. tripal_report_error('tripal_chado', TRIPAL_ERROR,
  300. 'Must provide an mview_id when deleting an mview.', array());
  301. return FALSE;
  302. }
  303. try {
  304. $transaction = db_transaction();
  305. // Get this mview details.
  306. $sql = "SELECT * FROM {tripal_mviews} WHERE mview_id = :mview_id";
  307. $results = db_query($sql, array(':mview_id' => $mview_id));
  308. $mview = $results->fetchObject();
  309. // If op is to delete then do so.
  310. // Remove the mview from the tripal_mviews table.
  311. $sql = "DELETE FROM {tripal_mviews} WHERE mview_id = $mview_id";
  312. db_query($sql);
  313. // Does the table already exist?
  314. $mview_exists = chado_table_exists($mview->mv_table);
  315. // Drop the table from chado if it exists.
  316. if ($mview_exists) {
  317. $sql = "DROP TABLE {" . $mview->mv_table . "}";
  318. $success = chado_query($sql);
  319. if ($success) {
  320. //unset the variable
  321. global $databases;
  322. $default_db = $databases['default']['default']['database'];
  323. $chado_schema = chado_get_schema_name('chado');
  324. if (isset($GLOBALS["chado_tables"][$default_db][$chado_schema][$mview->mv_table])){
  325. unset($GLOBALS["chado_tables"][$default_db][$chado_schema][$mview->mv_table]);
  326. }
  327. drupal_set_message(t("Materialized view, %name, deleted.", array('%name' => $mview->name)));
  328. return TRUE;
  329. }
  330. else {
  331. drupal_set_message(t("Problem deleting materialized view, %name.", array('%name' => $mview->name)), 'error');
  332. return FALSE;
  333. }
  334. }
  335. else {
  336. return TRUE;
  337. }
  338. }
  339. catch (Exception $e) {
  340. $transaction->rollback();
  341. watchdog_exception('tripal_chado', $e);
  342. $error = _drupal_decode_exception($e);
  343. tripal_report_error('tripal_chado', TRIPAL_ERROR,
  344. "Could not delete the materialized view %table_name: %message.",
  345. array('%table_name' => $name, '%message' => $error['!message']));
  346. }
  347. return FALSE;
  348. }
  349. /**
  350. * Update a Materialized View.
  351. *
  352. * @param $mview_id
  353. * The unique identifier for the materialized view to be updated.
  354. *
  355. * @return
  356. * True if successful, FALSE otherwise.
  357. *
  358. * @ingroup tripal_mviews_api
  359. */
  360. function chado_populate_mview($mview_id) {
  361. $sql = "SELECT * FROM {tripal_mviews} WHERE mview_id = :mview_id ";
  362. $results = db_query($sql, array(':mview_id' => $mview_id));
  363. $mview = $results->fetchObject();
  364. if ($mview) {
  365. // Execute the query inside a transaction so that it doesn't destroy
  366. // existing data that may leave parts of the site unfunctional.
  367. $transaction = db_transaction();
  368. $previous_db = chado_set_active('chado'); // use chado database
  369. try {
  370. $success = chado_query("DELETE FROM {" . $mview->mv_table . "}");
  371. $success = chado_query("INSERT INTO {" . $mview->mv_table . "} ($mview->query)");
  372. // If success get the number of results and update the table record.
  373. if ($success) {
  374. $sql = "SELECT count(*) as cnt FROM {" . $mview->mv_table . "}";
  375. $results = chado_query($sql);
  376. $count = $results->fetchObject();
  377. $record = new stdClass();
  378. $record->mview_id = $mview_id;
  379. $record->last_update = time();
  380. $record->status = "Populated with " . number_format($count->cnt) . " rows";
  381. drupal_write_record('tripal_mviews', $record, 'mview_id');
  382. }
  383. // If not success then throw an error.
  384. else {
  385. throw new Exception("ERROR populating the materialized view ". $mview->mv_table . ". See Drupal's recent log entries for details.");
  386. }
  387. chado_set_active($previous_db);
  388. }
  389. catch (Exception $e) {
  390. $transaction->rollback();
  391. chado_set_active($previous_db);
  392. // Print and save the error message.
  393. $record = new stdClass();
  394. $record->mview_id = $mview_id;
  395. $record->status = "ERROR populating $mview->mv_table. See Drupal's recent log entries for details.\n";
  396. drupal_write_record('tripal_mviews', $record, 'mview_id');
  397. watchdog_exception('tripal_mviews', $e);
  398. return FALSE;
  399. }
  400. print "Done.\n";
  401. return TRUE;
  402. }
  403. }