tripal_core.custom_tables.api.inc

Provides an API to manage custom tables in Chado.

File

tripal_core/api/tripal_core.custom_tables.api.inc
View source
  1. <?php
  2. /**
  3. * @file
  4. * Provides an API to manage custom tables in Chado.
  5. */
  6. /**
  7. * @defgroup tripal_custom_tables_api Tripal Custom Tables API
  8. * @ingroup tripal_core_api
  9. * @{
  10. * Provides an API to manage custom tables in Chado.
  11. * @}
  12. */
  13. /**
  14. * Edits a custom table in the chado database. It supports
  15. * using the Drupal Schema API array.
  16. *
  17. * @param $table_id
  18. * The table_id of the table to edit
  19. * @param $table_name
  20. * The name of the custom table
  21. * @param $schema
  22. * Use the Schema API array to define the custom table.
  23. * @param $skip_if_exists
  24. * Set as TRUE to skip dropping and re-creation of the table. This is
  25. * useful if the table was already created through another means and you
  26. * simply want to make Tripal aware of the table schema.
  27. *
  28. * @ingroup tripal_custom_tables_api
  29. */
  30. function chado_edit_custom_table($table_id, $table_name, $schema, $skip_if_exists = 1) {
  31. $transaction = db_transaction();
  32. try {
  33. // Create a new record
  34. $record = new stdClass();
  35. $record->table_id = $table_id;
  36. $record->table_name = $table_name;
  37. $record->schema = serialize($schema);
  38. // get the current custom table record
  39. $sql = "SELECT * FROM {tripal_custom_tables} WHERE table_id = :table_id";
  40. $results = db_query($sql, array(':table_id' => $table_id));
  41. $custom_table = $results->fetchObject();
  42. // if this is a materialized view then don't allow editing with this function
  43. if ($custom_table->mview_id) {
  44. tripal_report_error('tripal_core', TRIPAL_ERROR, "Please use the tripal_edit_mview() function to edit this custom table as it is a materialized view.", array());
  45. drupal_set_message("This custom table is a materialized view. Please use the " . l('Materialized View', 'admin/tripal/schema/mviews') . " interface to edit it.", 'error');
  46. return FALSE;
  47. }
  48. // if the user changed the table name, we want to drop the old one and force
  49. // creation of the new one.
  50. if ($custom_table->table_name != $table_name) {
  51. chado_query("DROP TABLE %s", $custom_table->table_name);
  52. $skip_if_exists = 0; // we want to create the table
  53. }
  54. // if skip creation is not set, then drop the table from chado if it exists
  55. if (!$skip_if_exists) {
  56. if (db_table_exists($custom_table->table_name)) {
  57. chado_query("DROP TABLE %s", $custom_table->table_name);
  58. drupal_set_message(t("Custom Table " . $custom_table->table_name . " dropped"));
  59. }
  60. }
  61. // update the custom table record and run the create custom table function
  62. drupal_write_record('tripal_custom_tables', $record, 'table_id');
  63. $success = chado_create_custom_table ($table_name, $schema, $skip_if_exists);
  64. }
  65. catch (Exception $e) {
  66. $transaction->rollback();
  67. watchdog_exception('tripal_core', $e);
  68. $error = _drupal_decode_exception($e);
  69. drupal_set_message(t("Could not update custom table '%table_name': %message.",
  70. array('%table_name' => $table, '%message' => $error['!message'])), 'error');
  71. return FALSE;
  72. }
  73. return TRUE;
  74. }
  75. /**
  76. * Add a new table to the Chado schema. This function is simply a wrapper for
  77. * the db_create_table() function of Drupal, but ensures the table is created
  78. * inside the Chado schema rather than the Drupal schema. If the table already
  79. * exists then it will be dropped and recreated using the schema provided.
  80. * However, it will only drop a table if it exsits in the tripal_custom_tables
  81. * table. This way the function cannot be used to accidentally alter existing
  82. * non custom tables. If $skip_if_exists is set then the table is simply
  83. * added to the tripal_custom_tables and no table is created in Chado.
  84. *
  85. * If you are creating a materialized view do not use this function, but rather
  86. * use the tripal_add_mview(). A materialized view is also considered a custom table
  87. * and an entry for it will be added to both the tripal_mviews and
  88. * tripal_custom_tables tables, but only if the tripal_add_mview() function is
  89. * used. The optional $mview_id parameters in this function is intended
  90. * for use by the tripal_add_mview() function when it calls this function
  91. * to create the table.
  92. *
  93. * @param $table
  94. * The name of the table to create.
  95. * @param $schema
  96. * A Drupal-style Schema API definition of the table
  97. * @param $skip_if_exists
  98. * Set as TRUE to skip dropping and re-creation of the table if it already
  99. * exists. This is useful if the table was already created through another
  100. * means and you simply want to make Tripal aware of the table schema. If the
  101. * table does not exist it will be created.
  102. * @param $mview_id
  103. * Optional. If this custom table is also a materialized view then provide
  104. * it's mview_id. This paramter is intended only when this function
  105. * is called by the tripal_add_mview() function. When creating a custom
  106. * table you shouldn't need to use this parameter.
  107. * @return
  108. * TRUE on success, FALSE on failure
  109. *
  110. * @ingroup tripal_custom_tables_api
  111. */
  112. function chado_create_custom_table($table, $schema, $skip_if_exists = 1, $mview_id = NULL) {
  113. global $databases;
  114. $created = 0;
  115. $recreated = 0;
  116. $chado_schema = tripal_get_schema_name('chado');
  117. $chado_dot = $chado_schema . '.';
  118. $transaction = db_transaction();
  119. try {
  120. // see if the table entry already exists in the tripal_custom_tables table.
  121. $sql = "SELECT * FROM {tripal_custom_tables} WHERE table_name = :table_name";
  122. $results = db_query($sql, array(':table_name' => $table));
  123. $centry = $results->fetchObject();
  124. // check to see if the table already exists in the chado schema
  125. $exists = chado_table_exists($table);
  126. // if the table does not exist then create it
  127. if (!$exists) {
  128. $ret = db_create_table($chado_dot . $table, $schema);
  129. $created = 1;
  130. }
  131. // if the table exists in Chado and in our custom table and
  132. // skip creation is turned off then drop and re-create the table
  133. if ($exists and is_object($centry) and !$skip_if_exists) {
  134. // drop the table we'll recreate it with the new schema
  135. chado_query('DROP TABLE {' . $table . '}');
  136. // remove any 'referring_tables' from the array as the db_create_table doesn't use that
  137. $new_schema = $schema;
  138. if (array_key_exists('referring_tables', $new_schema)) {
  139. unset($new_schema['referring_tables']);
  140. }
  141. db_create_table($chado_dot . $table, $new_schema);
  142. $recreated = 1;
  143. }
  144. // add an entry in the tripal_custom_table
  145. $record = new stdClass();
  146. $record->table_name = $table;
  147. $record->schema = serialize($schema);
  148. if ($mview_id) {
  149. $record->mview_id = $mview_id;
  150. }
  151. // if an entry already exists then remove it
  152. if ($centry) {
  153. $sql = "DELETE FROM {tripal_custom_tables} WHERE table_name = :table_name";
  154. db_query($sql, array(':table_name' => $table));
  155. }
  156. $success = drupal_write_record('tripal_custom_tables', $record);
  157. // now add any foreign key constraints
  158. if (($created or !$skip_if_exists) and array_key_exists('foreign keys', $schema)) {
  159. // iterate through the foreign keys and add each one
  160. $fkeys = $schema['foreign keys'];
  161. foreach ($fkeys as $fktable => $fkdetails) {
  162. $relations = $fkdetails['columns'];
  163. foreach ($relations as $left => $right) {
  164. $sql = '
  165. ALTER TABLE {' . $table . '}
  166. ADD CONSTRAINT ' . $table . '_' . $left . '_fkey FOREIGN KEY (' . $left . ')
  167. REFERENCES {' . $fktable . '} (' . $right . ')
  168. ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
  169. ';
  170. chado_query($sql);
  171. }
  172. }
  173. }
  174. }
  175. catch (Exception $e) {
  176. $transaction->rollback();
  177. watchdog_exception('tripal_core', $e);
  178. $error = _drupal_decode_exception($e);
  179. drupal_set_message(t("Could not add custom table '%table_name': %message.",
  180. array('%table_name' => $table, '%message' => $error['!message'])), 'error');
  181. return FALSE;
  182. }
  183. if ($created) {
  184. drupal_set_message("Custom table, '" . $table . "' , created successfully.", 'status');
  185. }
  186. elseif ($recreated) {
  187. drupal_set_message("Custom table, '" . $table . "' , re-created successfully.", 'status');
  188. }
  189. else {
  190. drupal_set_message("Custom table, '" . $table . "' , already exists. Table structure not changed, but definition array has been saved.", 'status');
  191. }
  192. return TRUE;
  193. }
  194. /**
  195. * This function is used to validate a Drupal Schema API array prior to
  196. * passing it ot the chado_create_custom_table_schema(). This function
  197. * can be used in a form validate function or whenver a schema is provided by
  198. * a user and needs validation.
  199. *
  200. * @param $schema_array
  201. * the Drupal Schema API compatible array
  202. *
  203. * @return
  204. * An empty string for success or a message string for failure
  205. *
  206. * @ingroup tripal_custom_tables_api
  207. */
  208. function chado_validate_custom_table_schema($schema_array) {
  209. if (is_array($schema_array) and !array_key_exists('table', $schema_array)) {
  210. return "The schema array must have key named 'table'";
  211. }
  212. if (preg_match('/[ABCDEFGHIJKLMNOPQRSTUVWXYZ]/', $schema_array['table'])) {
  213. return "Postgres will automatically change the table name to lower-case. To prevent unwanted side-effects, please rename the table with all lower-case characters.";
  214. }
  215. // check index length
  216. if (array_key_exists('indexes', $schema_array)) {
  217. foreach ($schema_array['indexes'] as $index_name => $details) {
  218. if (strlen($schema_array['table'] . '_' . $index_name) > 60) {
  219. return "One ore more index names appear to be too long. For example: '" . $schema_array['table'] . '_' . $index_name .
  220. ".' Index names are created by concatenating the table name with the index name provided " .
  221. "in the 'indexes' array of the schema. Please alter any indexes that when combined with the table name are " .
  222. "longer than 60 characters.";
  223. }
  224. }
  225. }
  226. // check unique key length
  227. if (array_key_exists('unique keys', $schema_array)) {
  228. foreach ($schema_array['unique keys'] as $index_name => $details) {
  229. if (strlen($schema_array['table'] . '_' . $index_name) > 60) {
  230. return "One ore more unique key names appear to be too long. For example: '" . $schema_array['table'] . '_' . $index_name .
  231. ".' Unique key names are created by concatenating the table name with the key name provided " .
  232. "in the 'unique keys' array of the schema. Please alter any unique keys that when combined with the table name are " .
  233. "longer than 60 characters.";
  234. }
  235. }
  236. }
  237. }
  238. /**
  239. * Retrieve the custom table id given the name
  240. *
  241. * @param $table_name
  242. * The name of the custom table
  243. *
  244. * @return
  245. * The unique identifier for the given table
  246. *
  247. * @ingroup tripal_custom_tables_api
  248. */
  249. function chado_get_custom_table_id($table_name) {
  250. if (db_table_exists('tripal_custom_tables')) {
  251. $sql = "SELECT * FROM {tripal_custom_tables} WHERE table_name = :table_name";
  252. $results = db_query($sql, array(':table_name' => $table_name));
  253. $custom_table = $results->fetchObject();
  254. if ($custom_table) {
  255. return $custom_table->table_id;
  256. }
  257. }
  258. return FALSE;
  259. }
  260. /**
  261. * Retrieves the list of custom tables in this site.
  262. *
  263. * @returns
  264. * An associative array where the key and value pairs are the table names.
  265. *
  266. * @ingroup tripal_custom_tables_api
  267. */
  268. function chado_get_custom_table_names($include_mview = TRUE) {
  269. $sql = "SELECT table_name FROM {tripal_custom_tables}";
  270. if (!$include_mview) {
  271. $sql .= " WHERE mview_id IS NULL";
  272. }
  273. $resource = db_query($sql);
  274. foreach ($resource as $r) {
  275. $tables[$r->table_name] = $r->table_name;
  276. }
  277. asort($tables);
  278. return $tables;
  279. }
  280. /**
  281. * Deletes the specified custom table
  282. *
  283. * @param $table_id
  284. * The unique ID of the custom table for the action to be performed on
  285. *
  286. * @ingroup tripal_custom_tables_api
  287. */
  288. function chado_delete_custom_table($table_id) {
  289. global $user;
  290. $args = array("$table_id");
  291. if (!$table_id) {
  292. return '';
  293. }
  294. // get this table details
  295. $sql = "SELECT * FROM {tripal_custom_tables} WHERE table_id = :table_id";
  296. $results = db_query($sql, array(':table_id' => $table_id));
  297. $custom_table = $results->fetchObject();
  298. // if this is a materialized view then don't allow deletion with this function
  299. if ($custom_table->mview_id) {
  300. tripal_report_error('tripal_core', TRIPAL_ERROR, "Please use the tripal_delete_mview() function to delete this custom table as it is a materialized view. Table not deleted.", array());
  301. drupal_set_message("This custom table is a materialized view. Please use the " . l('Materialized View', 'admin/tripal/schema/mviews') . " interface to delete it.", 'error');
  302. return FALSE;
  303. }
  304. // remove the entry from the tripal_custom tables table
  305. $sql = "DELETE FROM {tripal_custom_tables} WHERE table_id = $table_id";
  306. $success = db_query($sql);
  307. if ($success) {
  308. drupal_set_message(t("Custom Table '%name' removed", array('%name' => $custom_table->table_name)));
  309. }
  310. // drop the table from chado if it exists
  311. if (chado_table_exists($custom_table->table_name)) {
  312. $success = chado_query("DROP TABLE {" . $custom_table->table_name . "}");
  313. if ($success) {
  314. drupal_set_message(t("Custom Table '%name' dropped", array('%name' => $custom_table->table_name)));
  315. }
  316. else {
  317. tripal_report_error('tripal_core', TRIPAL_ERROR, "Cannot drop the custom table: %name", array('%name' => $custom_table->table_name));
  318. drupal_set_message(t("Cannot drop the custom table: '%name'", array('%name' => $custom_table->table_name)));
  319. }
  320. }
  321. }