get_FKs.php 3.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106
  1. <?php
  2. // This script will add FK relatinsions to an existing schema API array for each
  3. // Chado table. It requires Chado is installed in a 'chado' schema of
  4. // the drupal database. It also requires existing schema hooks for
  5. // version of Chado. The goal is to use the output of this script to
  6. // update the existing schema hooks. Redirect the output of this script to
  7. // a file and then replace the existing schema API include file (e.g.
  8. // tripal_core.schema_v1.2.api.inc). Be sure to check it before replacing
  9. // thie script requires a single argument (-v) which is the Chado version
  10. //
  11. // example usage in drupal directory root:
  12. //
  13. // php ./sites/all/modules/tripal/tripal_core/api/get_FKs.php -v 1.11 > \
  14. // ./sites/all/modules/tripal/tripal_core/apitripal_core.schema_v1.11.api.inc.new
  15. //
  16. // php ./sites/all/modules/tripal/tripal_core/api/get_FKs.php -v 1.2 > \
  17. // ./sites/all/modules/tripal/tripal_core/api/tripal_core.schema_v1.2.api.inc.new
  18. $arguments = getopt("v:");
  19. if (isset($arguments['v'])) {
  20. $drupal_base_url = parse_url('http://www.example.com');
  21. $_SERVER['HTTP_HOST'] = $drupal_base_url['host'];
  22. $_SERVER['REQUEST_URI'] = $_SERVER['SCRIPT_NAME'] = $_SERVER['PHP_SELF'];
  23. $_SERVER['REMOTE_ADDR'] = NULL;
  24. $_SERVER['REQUEST_METHOD'] = NULL;
  25. require_once 'includes/bootstrap.inc';
  26. drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL);
  27. $version = $arguments['v'];
  28. get_chado_fk_relationships($version);
  29. }
  30. /**
  31. *
  32. */
  33. function get_chado_fk_relationships($version){
  34. // convert the version to a form suitable for function names
  35. $v = $version;
  36. $v = preg_replace("/\./","_",$v);
  37. $tables = tripal_core_get_chado_tables();
  38. $sql ="
  39. SELECT
  40. tc.constraint_name, tc.table_name, kcu.column_name,
  41. ccu.table_name AS foreign_table_name,
  42. ccu.column_name AS foreign_column_name
  43. FROM
  44. information_schema.table_constraints AS tc
  45. JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
  46. JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
  47. WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='%s'
  48. ";
  49. // iterate through the tables and get the foreign keys
  50. print "<?php\n";
  51. $referring = array();
  52. foreach ($tables as $table) {
  53. // get the existing table array
  54. $table_arr = tripal_core_get_chado_table_schema($table);
  55. if (empty($table_arr)) {
  56. print "ERROR: empty table definition $table\n";
  57. }
  58. // get the foreign keys and add them to the array
  59. $fks = db_query($sql,$table);
  60. while ($fk = db_fetch_object($fks)) {
  61. $table_arr['foreign keys'][$fk->foreign_table_name]['table'] = $fk->foreign_table_name;
  62. $table_arr['foreign keys'][$fk->foreign_table_name]['columns'][$fk->column_name] = $fk->foreign_column_name;
  63. $reffering[$fk->foreign_table_name] = $table;
  64. }
  65. // reformat the array to be more legible
  66. $arr = var_export($table_arr, 1);
  67. $arr = preg_replace("/\n\s+array/","array", $arr); // move array( to previous line
  68. $arr = preg_replace("/\n/","\n ", $arr); // add indentation
  69. $arr = preg_replace("/true/","TRUE", $arr); // add indentation
  70. $arr = preg_replace("/array \(/","array(", $arr); // add indentation
  71. // print out the new Schema API function for this table
  72. print "/**
  73. * Implements hook_chado_schema_v".$v."_".$table."()
  74. * Purpose: To describe the structure of '$table' to tripal
  75. * @see tripal_core_chado_insert()
  76. * @see tripal_core_chado_update()
  77. * @see tripal_core_chado_select()
  78. *
  79. * @return
  80. * An array describing the '$table' table
  81. *
  82. * @ingroup tripal_chado_v".$version."_schema_api
  83. *
  84. */
  85. function tripal_core_chado_schema_v".$v."_".$table."() {
  86. \$description = $arr;
  87. return \$description;
  88. }
  89. ";
  90. }
  91. }