tripal_core.mviews.api.inc

Provides an application programming interface (API) to manage materialized views in Chado.

File

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