TripalChadoMViewsAPITest.php 9.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280
  1. <?php
  2. namespace Tests\tripal_chado\api;
  3. use StatonLab\TripalTestSuite\DBTransaction;
  4. use StatonLab\TripalTestSuite\TripalTestCase;
  5. class TripalChadoMViewsAPITest extends TripalTestCase {
  6. // Use a transaction to roll back changes after every test.
  7. use DBTransaction;
  8. // This variable holds example materialized views that can be used
  9. // by the unit tests below.
  10. private $example_mviews = [
  11. 'analysis_organism_test' => [
  12. 'schema' => [
  13. 'table' => 'analysis_organism_test',
  14. 'description' => 'This view is for associating an organism (via it\'s associated features) to an analysis.',
  15. 'fields' => [
  16. 'analysis_id' => [
  17. 'size' => 'big',
  18. 'type' => 'int',
  19. 'not null' => true,
  20. ],
  21. 'organism_id' => [
  22. 'size' => 'big',
  23. 'type' => 'int',
  24. 'not null' => true,
  25. ],
  26. ],
  27. 'indexes' => [
  28. 'networkmod_qtl_indx0' => [
  29. 0 => 'analysis_id',
  30. ],
  31. 'networkmod_qtl_indx1' => [
  32. 0 => 'organism_id',
  33. ],
  34. ],
  35. 'foreign keys' => [
  36. 'analysis' => [
  37. 'table' => 'analysis',
  38. 'columns' => [
  39. 'analysis_id' => 'analysis_id',
  40. ],
  41. ],
  42. 'organism' => [
  43. 'table' => 'organism',
  44. 'columns' => [
  45. 'organism_id' => 'organism_id',
  46. ],
  47. ],
  48. ],
  49. ],
  50. 'sql' => "
  51. SELECT DISTINCT A.analysis_id, O.organism_id
  52. FROM analysis A
  53. INNER JOIN analysisfeature AF ON A.analysis_id = AF.analysis_id
  54. INNER JOIN feature F ON AF.feature_id = F.feature_id
  55. INNER JOIN organism O ON O.organism_id = F.organism_id
  56. ",
  57. 'comment' => 'This view is for associating an organism (via it\'s associated features) to an analysis.',
  58. 'module' => 'tripal_chado',
  59. ],
  60. ];
  61. /**
  62. * Test creation of a new materialized view.
  63. *
  64. * @group api
  65. */
  66. public function test_chado_add_mview() {
  67. // Add the analysis_organism mview.
  68. $mview_name = 'analysis_organism_test';
  69. $mview_module = $this->example_mviews[$mview_name]['module'];
  70. $mview_sql = $this->example_mviews[$mview_name]['sql'];
  71. $mview_schema = $this->example_mviews[$mview_name]['schema'];
  72. $mview_comment = $this->example_mviews[$mview_name]['comment'];
  73. $success = chado_add_mview($mview_name, $mview_module, $mview_schema, $mview_sql, $mview_comment, FALSE);
  74. $this->assertTrue($success, "Failed to create materialized view: $mview_name");
  75. // Make sure that the entry is now there.
  76. $mview = db_select('tripal_mviews', 'tm')
  77. ->fields('tm')
  78. ->condition('name', $mview_name)
  79. ->execute()
  80. ->fetchObject();
  81. $this->assertTrue(is_object($mview),
  82. "Failed to find the materialized view, $mview_name, in the tripal_mviews table");
  83. // Make sure that all of the fields exist and were properly added.
  84. $this->assertTrue($mview->modulename == $mview_module,
  85. "Failed to create a proper materialized the modulename field is incorrect: '$mview_module' != '$mview->modulename'");
  86. $this->assertTrue($mview->mv_table == $mview_name,
  87. "Failed to create a proper materialized the mv_table field does not match input.");
  88. $this->assertTrue($mview->query == $mview_sql,
  89. "Failed to create a proper materialized the query field does not match input.");
  90. $this->assertTrue($mview->comment == $mview_comment,
  91. "Failed to create a proper materialized the comment field does not match input.");
  92. $this->assertNULL($mview->status,
  93. "Failed to create a proper materialized the status field should be NULL.");
  94. $this->assertNULL($mview->last_update,
  95. "Failed to create a proper materialized the last_update field should be NULL.");
  96. // Make sure the table exists.
  97. $this->assertTrue(chado_table_exists($mview_name),
  98. "Materialized view, $mview_name, was added to the tripal_mviews table but the table was not created.");
  99. $this->reset_tables();
  100. }
  101. /**
  102. * Test deletion of a materialized view.
  103. *
  104. * @group api
  105. */
  106. public function test_chado_delete_mview() {
  107. // Make sure the mview is present.
  108. $mview_name = 'analysis_organism_test';
  109. $mview_module = $this->example_mviews[$mview_name]['module'];
  110. $mview_sql = $this->example_mviews[$mview_name]['sql'];
  111. $mview_schema = $this->example_mviews[$mview_name]['schema'];
  112. $mview_comment = $this->example_mviews[$mview_name]['comment'];
  113. $success = chado_add_mview($mview_name, $mview_module, $mview_schema, $mview_sql, $mview_comment, FALSE);
  114. $this->assertTrue($success, "Failed to create materialized view: $mview_name");
  115. // Get the mview_id.
  116. $mview = db_select('tripal_mviews', 'tm')
  117. ->fields('tm')
  118. ->condition('name', $mview_name)
  119. ->execute()
  120. ->fetchObject();
  121. $this->assertTrue(is_object($mview),
  122. "Failed to find the materialized view, $mview_name, in the tripal_mviews table");
  123. $mview_id = $mview->mview_id;
  124. // Now run the API function to delete it.
  125. $success = chado_delete_mview($mview_id);
  126. $this->assertTrue($success,
  127. "Materialized view, $mview_name, could not be deleted.");
  128. $this->reset_tables();
  129. // Make sure the table is gone.
  130. $this->assertFalse(chado_table_exists($mview_name),
  131. "Materialized view, $mview_name, table failed to be removed after deletion.");
  132. }
  133. /**
  134. * Test modifications to a materialized view
  135. *
  136. * @group api
  137. */
  138. public function test_chado_edit_mview() {
  139. // TODO: this is currently a stub for a test function that neds
  140. // implementation. For now it returns true to get past unit testing.
  141. $this->assertTrue(true);
  142. $this->reset_tables();
  143. }
  144. /**
  145. * Test adding a Tripal Job to re-populate a materialized view
  146. *
  147. * @group api
  148. */
  149. public function test_chado_refresh_mview() {
  150. // TODO: this is currently a stub for a test function that neds
  151. // implementation. For now it returns true to get past unit testing.
  152. $this->assertTrue(true);
  153. $this->reset_tables();
  154. }
  155. /**
  156. * Test re-populating a materialized view.
  157. *
  158. * @group api
  159. */
  160. public function test_chado_populate_mview() {
  161. // TODO: this is currently a stub for a test function that neds
  162. // implementation. For now it returns true to get past unit testing.
  163. $this->assertTrue(true);
  164. $this->reset_tables();
  165. }
  166. /**
  167. * Test modifications to a materialized view
  168. *
  169. * @group api
  170. */
  171. public function test_chado_get_mview_id() {
  172. // TODO: this is currently a stub for a test function that neds
  173. // implementation. For now it returns true to get past unit testing.
  174. $this->assertTrue(true);
  175. $this->reset_tables();
  176. }
  177. /**
  178. * Test retrieving names of the materialized views.
  179. *
  180. * @group api
  181. */
  182. public function test_chado_get_mview_table_names() {
  183. $this->reset_tables();
  184. // TODO: this is currently a stub for a test function that neds
  185. // implementation. For now it returns true to get past unit testing.
  186. $this->assertTrue(true);
  187. }
  188. /**
  189. * Test retrieving all materialized view objects.
  190. *
  191. * @group api
  192. */
  193. public function test_chado_get_mviews() {
  194. // TODO: this is currently a stub for a test function that neds
  195. // implementation. For now it returns true to get past unit testing.
  196. $this->assertTrue(true);
  197. $this->reset_tables();
  198. }
  199. /**
  200. * Issue 322 reported the problem of re-adding a materialized view after
  201. * the actual table had been manually removed outside of Tripal. The
  202. * function reported errors.
  203. *
  204. * @ticket 322
  205. */
  206. public function test_re_adding_deleted_mview_issue_322() {
  207. // Add the analysis_organism mview.
  208. $mview_name = 'analysis_organism_test';
  209. $mview_module = $this->example_mviews[$mview_name]['module'];
  210. $mview_sql = $this->example_mviews[$mview_name]['sql'];
  211. $mview_schema = $this->example_mviews[$mview_name]['schema'];
  212. $mview_comment = $this->example_mviews[$mview_name]['comment'];
  213. $success = chado_add_mview($mview_name, $mview_module, $mview_schema, $mview_sql, $mview_comment, FALSE);
  214. $this->assertTrue($success, "Failed to create materialized view: $mview_name");
  215. // Now simulate manual deletion of the table outside of the API.
  216. chado_query('DROP TABLE {' . $mview_name . '}');
  217. // Make sure the table no longer exists.
  218. $this->assertFalse(chado_table_exists($mview_name),
  219. "Failed to manually remove the materialized view, cannot complete the test.");
  220. // Now try to read the mview. Previously, the behavior was the the mview
  221. // table would not be created because Tripal thinks it's already there.
  222. $success = chado_add_mview($mview_name, $mview_module, $mview_schema, $mview_sql, $mview_comment, FALSE);
  223. $this->assertTrue($success, "Failed to re-create materialized view: $mview_name");
  224. // Now make sure the mview table exists.
  225. $this->reset_tables();
  226. $this->assertTrue(chado_table_exists($mview_name),
  227. "Manually removing a materialized views throws off the chado_add_mview function when the mview is re-added. See Issue #322");
  228. $this->reset_tables();
  229. }
  230. /**
  231. * The chado_table_exists() function mantains a global variable to keep track
  232. * of which tables exist. If the table exists then it stores that info in
  233. * the global variable. This is because lots of queries perform a check to
  234. * see if the tables exist and that can have a major performance hit.
  235. *
  236. * Because we are creating and dropping Chado tables in our tests it throws
  237. * off the array and we need to reset it. Normally this isn't a problem
  238. * because this would get reset on every page load anyone. For testing it
  239. * doesn't.
  240. */
  241. public function reset_tables() {
  242. $GLOBALS["chado_tables"] = [];
  243. }
  244. }