tripal_chado.custom_tables.inc 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427
  1. <?php
  2. /**
  3. * @file
  4. * Contains functions for creating, editing and deleting custom tables
  5. * on the Tripal website.
  6. *
  7. */
  8. /**
  9. * Provides a landing page for administrating custom tables.
  10. */
  11. function tripal_custom_table_admin_view() {
  12. $output = '';
  13. // set the breadcrumb
  14. $breadcrumb = [];
  15. $breadcrumb[] = l('Home', '<front>');
  16. $breadcrumb[] = l('Administration', 'admin');
  17. $breadcrumb[] = l('Tripal', 'admin/tripal');
  18. $breadcrumb[] = l('Chado Schema', 'admin/tripal/storage/chado');
  19. $breadcrumb[] = l('Custom Tables', 'admin/tripal/storage/chado/custom_tables');
  20. drupal_set_breadcrumb($breadcrumb);
  21. // Add the view
  22. $view = views_embed_view('tripal_admin_custom_table', 'default');
  23. if (isset($view)) {
  24. $output .= $view;
  25. }
  26. else {
  27. $output .= '<p>The Tripal Custom Table management system uses primarily views to provide an '
  28. . 'administrative interface. Currently one or more views needed for this '
  29. . 'administrative interface are disabled. <strong>Click each of the following links to '
  30. . 'enable the pertinent views</strong>:</p>';
  31. $output .= '<ul>';
  32. $output .= '<li>' . l('Custom Tables View', 'admin/tripal/storage/chado/custom_tables/views/tables/enable') . '</li>';
  33. $output .= '</ul>';
  34. }
  35. return $output;
  36. }
  37. /**
  38. * Renders the tripal_custom_tables_form.
  39. *
  40. */
  41. function tripal_custom_table_new_page() {
  42. $form = drupal_get_form('tripal_custom_tables_form');
  43. return drupal_render($form);
  44. }
  45. /**
  46. * A template function which returns markup to display details for the custom
  47. * table
  48. *
  49. * @param $table_id
  50. * The unique ID of the custom table
  51. *
  52. */
  53. function tripal_custom_table_view($table_id) {
  54. // get this custom_table details
  55. $sql = "SELECT * FROM {tripal_custom_tables} WHERE table_id = :table_id";
  56. $results = db_query($sql, [':table_id' => $table_id]);
  57. $custom_table = $results->fetchObject();
  58. // create a table with each row containig stats for
  59. // an individual job in the results set.
  60. $return_url = url("admin/tripal/storage/chado/custom_tables");
  61. $output = "<p><a href=\"$return_url\">" . t("Return to list of custom tables") . "</a></p>";
  62. $output .= "<br />";
  63. $output .= "<p>Details for <b>$custom_table->table_name</b>:</p>";
  64. $output .= "<br />";
  65. $output .= "<table class=\"border-table\">";
  66. if ($custom_table->table_name) {
  67. $output .= " <tr>" .
  68. " <th>Table Name</th>" .
  69. " <td>$custom_table->table_name</td>" .
  70. " </tr>";
  71. }
  72. if ($custom_table->schema) {
  73. $output .= " <tr>" .
  74. " <th>Table Field Definitions</th>" .
  75. " <td><pre>" . var_export(unserialize($custom_table->schema), 1) . "</pre></td>" .
  76. " </tr>";
  77. }
  78. // build the URLs using the url function so we can handle installations where
  79. // clean URLs are or are not used
  80. $delete_url = url("admin/tripal/storage/chado/custom_tables/delete/$custom_table->table_id");
  81. $edit_url = url("admin/tripal/storage/chado/custom_tables/edit/$custom_table->table_id");
  82. $output .= "<tr><th>Actions</th>" .
  83. "<td>" .
  84. " <a href='$edit_url'>Edit</a>, " .
  85. " <a href='$delete_url'>Delete</a></td></tr>";
  86. $output .= "</table>";
  87. return $output;
  88. }
  89. /**
  90. * A Form to Create/Edit a Custom table.
  91. *
  92. * @param $form_state
  93. * The current state of the form (Form API)
  94. * @param $table_id
  95. * The unique ID of the Custom table to Edit or NULL if creating a new table
  96. *
  97. * @return
  98. * A form array (Form API)
  99. *
  100. */
  101. function tripal_custom_tables_form($form, &$form_state = NULL, $table_id = NULL) {
  102. // set the breadcrumb
  103. $breadcrumb = [];
  104. $breadcrumb[] = l('Home', '<front>');
  105. $breadcrumb[] = l('Administration', 'admin');
  106. $breadcrumb[] = l('Tripal', 'admin/tripal');
  107. $breadcrumb[] = l('Chado Schema', 'admin/tripal/storage/chado');
  108. $breadcrumb[] = l('Custom Tables', 'admin/tripal/storage/chado/custom_tables');
  109. drupal_set_breadcrumb($breadcrumb);
  110. if (!$table_id) {
  111. $action = 'Add';
  112. }
  113. else {
  114. $action = 'Edit';
  115. }
  116. // get this requested table
  117. $default_schema = '';
  118. $default_force_drop = 0;
  119. if (strcmp($action, 'Edit') == 0) {
  120. $sql = "SELECT * FROM {tripal_custom_tables} WHERE table_id = :table_id ";
  121. $results = db_query($sql, [':table_id' => $table_id]);
  122. $custom_table = $results->fetchObject();
  123. // if this is a materialized view then don't allow editing with this function
  124. if (property_exists($custom_table, 'mview_id') and $custom_table->mview_id) {
  125. drupal_set_message("This custom table is a materialized view. Please use the " . l('Materialized View', 'admin/tripal/storage/chado/mviews') . " interface to edit it.", 'error');
  126. drupal_goto("admin/tripal/storage/chado/custom_tables");
  127. return [];
  128. }
  129. // set the default values. If there is a value set in the
  130. // form_state then let's use that, otherwise, we'll pull
  131. // the values from the database
  132. if (array_key_exists('values', $form_state)) {
  133. $default_schema = $form_state['values']['schema'];
  134. $default_force_drop = $form_state['values']['force_drop'];
  135. }
  136. if (!$default_schema) {
  137. $default_schema = var_export(unserialize($custom_table->schema), 1);
  138. $default_schema = preg_replace('/=>\s+\n\s+array/', '=> array', $default_schema);
  139. }
  140. }
  141. // Build the form
  142. $form['action'] = [
  143. '#type' => 'value',
  144. '#value' => $action,
  145. ];
  146. $form['table_id'] = [
  147. '#type' => 'value',
  148. '#value' => $table_id,
  149. ];
  150. $form['instructions'] = [
  151. '#type' => 'fieldset',
  152. '#title' => 'Instructions',
  153. '#collapsible' => TRUE,
  154. '#collapsed' => TRUE,
  155. ];
  156. $form['instructions']['text'] = [
  157. '#type' => 'item',
  158. '#markup' => '<p>' . t('At times it is necessary to add a custom table
  159. to the Chado schema. These are not offically sanctioned tables but may
  160. be necessary for local data requirements. Avoid creating custom tables
  161. when possible as other GMOD tools may not recognize these tables nor
  162. the data in them. Linker tables or property tables are often a good
  163. candidate for a custom table. For example a table to link stocks and
  164. libraries (e.g. library_stock). Try to model linker or propery tables
  165. after existing tables. If the table already exists it will not be
  166. modified. To force dropping and recreation of the table
  167. click the checkbox below. Tables are defined using the ' .
  168. l('Drupal Schema API', 'https://api.drupal.org/api/drupal/includes!database!schema.inc/group/schemaapi/7',
  169. ['attributes' => ['target' => '_blank']]) . '</p>' .
  170. '<p>Please note that table names should be all lower-case.</p>'
  171. ),
  172. ];
  173. $form['instructions']['example'] = [
  174. '#type' => 'item',
  175. '#markup' => "Example library_stock table: <pre>
  176. array (
  177. 'table' => 'library_stock',
  178. 'fields' => array (
  179. 'library_stock_id' => array(
  180. 'type' => 'serial',
  181. 'not null' => TRUE,
  182. ),
  183. 'library_id' => array(
  184. 'type' => 'int',
  185. 'not null' => TRUE,
  186. ),
  187. 'stock_id' => array(
  188. 'type' => 'int',
  189. 'not null' => TRUE,
  190. ),
  191. ),
  192. 'primary key' => array(
  193. 'library_stock_id'
  194. ),
  195. 'unique keys' => array(
  196. 'library_stock_c1' => array(
  197. 'library_id',
  198. 'stock_id'
  199. ),
  200. ),
  201. 'foreign keys' => array(
  202. 'library' => array(
  203. 'table' => 'library',
  204. 'columns' => array(
  205. 'library_id' => 'library_id',
  206. ),
  207. ),
  208. 'stock' => array(
  209. 'table' => 'stock',
  210. 'columns' => array(
  211. 'stock_id' => 'stock_id',
  212. ),
  213. ),
  214. ),
  215. )
  216. </pre>",
  217. ];
  218. $form['force_drop'] = [
  219. '#type' => 'checkbox',
  220. '#title' => t('Re-create table'),
  221. '#description' => t('Check this box if your table already exists and you would like to drop it and recreate it.'),
  222. '#default_value' => $default_force_drop,
  223. ];
  224. $form['schema'] = [
  225. '#type' => 'textarea',
  226. '#title' => t('Schema Array'),
  227. '#description' => t('Please enter the ' . l('Drupal Schema API', 'https://api.drupal.org/api/drupal/includes!database!schema.inc/group/schemaapi/7', ['attributes' => ['target' => '_blank']]) . ' compatible array that defines the table.'),
  228. '#required' => FALSE,
  229. '#default_value' => $default_schema,
  230. '#rows' => 25,
  231. ];
  232. if ($action == 'Edit') {
  233. $value = 'Save';
  234. }
  235. if ($action == 'Add') {
  236. $value = 'Add';
  237. }
  238. $form['submit'] = [
  239. '#type' => 'submit',
  240. '#value' => t($value),
  241. '#executes_submit_callback' => TRUE,
  242. ];
  243. $form['cancel'] = [
  244. '#type' => 'markup',
  245. '#markup' => l('Cancel', 'admin/tripal/storage/chado/custom_tables'),
  246. ];
  247. return $form;
  248. }
  249. /**
  250. * Implements hook_validate().
  251. * Validate the Create/Edit custom table form.
  252. *
  253. */
  254. function tripal_custom_tables_form_validate($form, &$form_state) {
  255. $action = $form_state['values']['action'];
  256. $table_id = $form_state['values']['table_id'];
  257. $schema = $form_state['values']['schema'];
  258. $force_drop = $form_state['values']['force_drop'];
  259. if (!$schema) {
  260. form_set_error($form_state['values']['schema'], t('Schema array field is required.'));
  261. }
  262. // make sure the array is valid
  263. $schema_array = [];
  264. if ($schema) {
  265. $success = preg_match('/^\s*array/', $schema);
  266. if (!$success) {
  267. form_set_error($form_state['values']['schema'],
  268. t("The schema array should begin with the word 'array'."));
  269. }
  270. else {
  271. $success = eval("\$schema_array = $schema;");
  272. if ($success === FALSE) {
  273. $error = error_get_last();
  274. form_set_error('schema', t("The schema array is improperly formatted. Parse Error : " . $error["message"]));
  275. }
  276. if (is_array($schema_array) and !array_key_exists('table', $schema_array)) {
  277. form_set_error('schema', t("The schema array must have key named 'table'"));
  278. }
  279. // validate the contents of the array
  280. $error = chado_validate_custom_table_schema($schema_array);
  281. if ($error) {
  282. form_set_error('schema', $error);
  283. }
  284. if ($action == 'Edit') {
  285. // see if the table name has changed. If so, then check to make sure
  286. // it doesn't already exists. We don't want to drop a table we didn't mean to
  287. $sql = "SELECT * FROM {tripal_custom_tables} WHERE table_id = :table_id";
  288. $results = db_query($sql, [':table_id' => $table_id]);
  289. $ct = $results->fetchObject();
  290. if ($ct->table_name != $schema_array['table']) {
  291. $exists = chado_table_exists($schema_array['table']);
  292. if ($exists) {
  293. form_set_error($form_state['values']['schema'],
  294. t("The table name already exists, please choose a different name."));
  295. }
  296. }
  297. }
  298. }
  299. }
  300. }
  301. /**
  302. * Submit the Create/Edit Custom table form
  303. * Implements hook_form_submit().
  304. *
  305. */
  306. function tripal_custom_tables_form_submit($form, &$form_state) {
  307. $ret = [];
  308. $action = $form_state['values']['action'];
  309. $table_id = $form_state['values']['table_id'];
  310. $schema = $form_state['values']['schema'];
  311. $force_drop = $form_state['values']['force_drop'];
  312. $skip_creation = 1;
  313. if ($force_drop) {
  314. $skip_creation = 0;
  315. }
  316. // convert the schema into a PHP array
  317. $schema_arr = [];
  318. eval("\$schema_arr = $schema;");
  319. if (strcmp($action, 'Edit') == 0) {
  320. chado_edit_custom_table($table_id, $schema_arr['table'], $schema_arr, $skip_creation);
  321. }
  322. elseif (strcmp($action, 'Add') == 0) {
  323. chado_create_custom_table($schema_arr['table'], $schema_arr, $skip_creation);
  324. }
  325. else {
  326. drupal_set_message(t("No action performed."));
  327. }
  328. drupal_goto("admin/tripal/storage/chado/custom_tables");
  329. }
  330. /**
  331. * Just a simple form for confirming deletion of a custom table
  332. *
  333. */
  334. function tripal_custom_tables_delete_form($form, &$form_state, $table_id) {
  335. // get details about this table entry
  336. $sql = "SELECT * FROM {tripal_custom_tables} WHERE table_id = :table_id";
  337. $results = db_query($sql, [':table_id' => $table_id]);
  338. $entry = $results->fetchObject();
  339. // if this is a materialized view then don't allow editing with this function
  340. if ($entry->mview_id) {
  341. drupal_set_message("This custom table is a materialized view. Please use the " . l('Materialized View', 'admin/tripal/storage/chado/mviews') . " interface to delete it.", 'error');
  342. drupal_goto("admin/tripal/storage/chado/custom_tables");
  343. return [];
  344. }
  345. $form = [];
  346. $form['table_id'] = [
  347. '#type' => 'value',
  348. '#value' => $table_id,
  349. ];
  350. $form['sure'] = [
  351. '#type' => 'markup',
  352. '#markup' => '<p>Are you sure you want to delete the "' . $entry->table_name . '" custom table?</p>',
  353. ];
  354. $form['submit'] = [
  355. '#type' => 'submit',
  356. '#value' => 'Delete',
  357. ];
  358. $form['cancel'] = [
  359. '#type' => 'submit',
  360. '#value' => 'Cancel',
  361. ];
  362. return $form;
  363. }
  364. /**
  365. * form submit hook for the tripal_custom_tables_delete_form form.
  366. *
  367. * @param $form
  368. * @param $form_state
  369. */
  370. function tripal_custom_tables_delete_form_submit($form, &$form_state) {
  371. $action = $form_state['clicked_button']['#value'];
  372. $table_id = $form_state['values']['table_id'];
  373. if (strcmp($action, 'Delete') == 0) {
  374. chado_delete_custom_table($table_id);
  375. }
  376. else {
  377. drupal_set_message(t("No action performed."));
  378. }
  379. drupal_goto("admin/tripal/storage/chado/custom_tables");
  380. }