get_FKs.php 3.3 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394
  1. <?php
  2. // This script will generate an updated 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/get_FKs.php -v 1.2 > \
  14. // ./sites/all/modules/tripal/tripal_core/tripal_core.schema_v1.2.api.inc.new
  15. $arguments = getopt("v:");
  16. if (isset($arguments['v'])) {
  17. $drupal_base_url = parse_url('http://www.example.com');
  18. $_SERVER['HTTP_HOST'] = $drupal_base_url['host'];
  19. $_SERVER['REQUEST_URI'] = $_SERVER['SCRIPT_NAME'] = $_SERVER['PHP_SELF'];
  20. $_SERVER['REMOTE_ADDR'] = NULL;
  21. $_SERVER['REQUEST_METHOD'] = NULL;
  22. require_once 'includes/bootstrap.inc';
  23. drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL);
  24. $version = $arguments['v'];
  25. get_chado_fk_relationships($version);
  26. }
  27. /**
  28. *
  29. */
  30. function get_chado_fk_relationships($version){
  31. // convert the version to a form suitable for function names
  32. $v = $version;
  33. $v = preg_replace("/\./","_",$v);
  34. $tables = tripal_core_get_chado_tables();
  35. $sql ="
  36. SELECT
  37. tc.constraint_name, tc.table_name, kcu.column_name,
  38. ccu.table_name AS foreign_table_name,
  39. ccu.column_name AS foreign_column_name
  40. FROM
  41. information_schema.table_constraints AS tc
  42. JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
  43. JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
  44. WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='%s'
  45. ";
  46. // iterate through the tables and get the foreign keys
  47. print "<?php\n";
  48. foreach ($tables as $table){
  49. // get the existing table array
  50. $table_arr = tripal_core_get_chado_table_schema($table);
  51. // get the foreign keys and add them to the array
  52. $fks = db_query($sql,$table);
  53. while($fk = db_fetch_object($fks)){
  54. $table_arr['foreign keys'][$fk->foreign_table_name]['table'] = $fk->foreign_table_name;
  55. $table_arr['foreign keys'][$fk->foreign_table_name]['columns'][$fk->column_name] = $fk->foreign_column_name;
  56. }
  57. // reformat the array to be more legible
  58. $arr = var_export($table_arr,1);
  59. $arr = preg_replace("/\n\s+array/","array",$arr); // move array( to previous line
  60. $arr = preg_replace("/\n/","\n ",$arr); // add indentation
  61. // print out the new Schema API function for this table
  62. print "/**
  63. * Implements hook_chado_schema_v".$v."_".$table."()
  64. * Purpose: To describe the structure of '$table' to tripal
  65. * @see tripal_core_chado_insert()
  66. * @see tripal_core_chado_update()
  67. * @see tripal_core_chado_select()
  68. *
  69. * @return
  70. * An array describing the '$table' table
  71. *
  72. * @ingroup tripal_chado_v".$version."_schema_api
  73. *
  74. */
  75. function tripal_core_chado_schema_v".$v."_".$table."() {
  76. \$description = $arr;
  77. return \$description;
  78. }
  79. ";
  80. }
  81. }