tripal_core.api.inc 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310
  1. <?php
  2. /**
  3. * @file
  4. * The Tripal Core API
  5. *
  6. * This file provides the API needed for all other Tripal and Tripal dependent
  7. * modules.
  8. */
  9. require_once "chado_tables.schema.inc";
  10. // just a temporary function for debugging
  11. function tripal_core_chado_insert_test(){
  12. $values = array(
  13. 'organism_id' => array(
  14. 'genus' => 'Citrus',
  15. 'species' => 'sinensis',
  16. ),
  17. 'name' => 'orange1.1g000034m.g',
  18. 'uniquename' => 'orange1.1g000034m.g7',
  19. 'type_id' => array (
  20. 'cv_id' => array (
  21. 'name' => 'sequence',
  22. ),
  23. 'name' => 'gene',
  24. 'is_obsolete' => 0
  25. ),
  26. );
  27. $result = tripal_core_chado_insert('feature',$values);
  28. return "$result->feature_id";
  29. }
  30. /**
  31. * Provides a generic routine for inserting into any Chado table
  32. *
  33. * Use this function to insert a record into any Chado table. The first
  34. * argument specifies the table for inserting and the second is an array
  35. * of values to be inserted. The array is mutli-dimensional such that
  36. * foreign key lookup values can be specified.
  37. *
  38. * @param $table
  39. * The name of the chado table for inserting
  40. * @param $values
  41. * An associative array containing the values for inserting.
  42. *
  43. * @return
  44. * On success this function returns an object containing the serial, or
  45. * incremental fields (such as primary keys) for the record that was just
  46. * inserted. On failure, it returns FALSE.
  47. *
  48. * Example usage:
  49. * @code
  50. * $values = array(
  51. * 'organism_id' => array(
  52. * 'genus' => 'Citrus',
  53. * 'species' => 'sinensis',
  54. * ),
  55. * 'name' => 'orange1.1g000034m.g',
  56. * 'uniquename' => 'orange1.1g000034m.g',
  57. * 'type_id' => array (
  58. * 'cv_id' => array (
  59. * 'name' => 'sequence',
  60. * ),
  61. * 'name' => 'gene',
  62. * 'is_obsolete' => 0
  63. * ),
  64. * );
  65. * $result = tripal_core_chado_insert('feature',$values);
  66. * @endcode
  67. * The above code inserts a record into the feature table. The $values array is
  68. * nested such that the organism is selected by way of the organism_id foreign
  69. * key constraint by specifying the genus and species. The cvterm is also
  70. * specified using its foreign key and the cv_id for the cvterm is nested as
  71. * well.
  72. */
  73. function tripal_core_chado_insert($table,$values){
  74. $insert_values = array();
  75. // get the table description
  76. $chado = tripal_core_get_chado_schema();
  77. $table_desc = $chado[$table];
  78. // iterate through the values array and create a new 'insert_values' array
  79. // that has all the values needed for insert with all foreign relationsihps
  80. // resolved.
  81. foreach($values as $field => $value){
  82. if(is_array($value)){
  83. // select the value from the foreign key relationship for this value
  84. $insert_values[$field] = tripal_core_chado_get_foreign_key($table_desc,$field,$value);
  85. }
  86. else {
  87. $insert_values[$field] = $value;
  88. }
  89. }
  90. // check for violation of any unique constraints
  91. $ukeys = $table_desc['unique keys'];
  92. $ukselect_cols = array();
  93. $ukselect_vals = array();
  94. foreach($ukeys as $name => $fields){
  95. foreach($fields as $index => $field){
  96. // build the arrays for performing a select that will check the contraint
  97. array_push($ukselect_cols,$field);
  98. $ukselect_vals[$field] = $insert_values[$field];
  99. }
  100. // now check the constraint
  101. if(db_fetch_object(tripal_core_chado_select($table,$ukselect_cols,$ukselect_vals))){
  102. watchdog('tripal_core',"Cannot insert duplicate record into $table table: " . print_r($values,1),array(),'WATCHDOG_ERROR');
  103. return false;
  104. }
  105. }
  106. // if trying to insert a field that is the primary key, make sure it also is unique
  107. $pkey = $table_desc['primary key'][0];
  108. if($insert_values[$pkey]){
  109. if(db_fetch_object(tripal_core_chado_select($table,array($pkey),array($pkey => $insert_values[$pkey])))){
  110. watchdog('tripal_core',"Cannot insert duplicate primary key into $table table: " . print_r($values,1),array(),'WATCHDOG_ERROR');
  111. return false;
  112. }
  113. }
  114. // make sure required fields have a value and get any fields that are of type serial
  115. $fields = $table_desc['fields'];
  116. $serials = array();
  117. foreach($fields as $field => $def){
  118. // a field is considered missing if it cannot be null and there is no default
  119. // value for it or it is of type 'serial'
  120. if($def['not null'] == 1 and !$insert_values[$field] and !$def['default'] and strcmp($def['type'],serial)!=0){
  121. watchdog('tripal_core',"Field $field cannot be null: " . print_r($values,1),array(),'WATCHDOG_ERROR');
  122. return false;
  123. }
  124. // get a list of the serial types
  125. if(strcmp($fields[$field]['type'],'serial')==0){
  126. array_push($serials,$field);
  127. }
  128. }
  129. // Now build the insert SQL statement
  130. $ifields = array();
  131. $ivalues = array();
  132. $itypes = array();
  133. foreach ($insert_values as $field => $value){
  134. array_push($ifields,$field);
  135. array_push($ivalues,$value);
  136. if(strcmp($fields[$field]['type'],'serial')==0 or
  137. strcmp($fields[$field]['type'],'int')==0){
  138. array_push($itypes,"%d");
  139. } else {
  140. array_push($itypes,"'%s'");
  141. }
  142. }
  143. $sql = "INSERT INTO $table (" . implode(", ",$ifields) . ") VALUES (". implode(", ",$itypes) .")";
  144. // finally perform the insert. If successful, return an object with the
  145. // primary keys (or all serial types) populated with the values from the
  146. // insert
  147. $object = array();
  148. if(db_query($sql,$ivalues)){
  149. $object = (object) $object; // convert the array to an object
  150. foreach ($serials as $field) {
  151. $object->$field = db_last_insert_id($table, $field);
  152. }
  153. return $object;
  154. }
  155. else {
  156. watchdog('tripal_core',"Cannot insert record into $table table: " . print_r($values,1),array(),'WATCHDOG_ERROR');
  157. return false;
  158. }
  159. }
  160. /**
  161. * Provides a generic routine for selecting data from a Chado table
  162. *
  163. * Use this function to perform a simple select from any Chado table.
  164. *
  165. * @param $table
  166. * The name of the chado table for inserting
  167. * @param $columns
  168. * An array of column names
  169. * @param $values
  170. * An associative array containing the values for filtering the results.
  171. *
  172. * @return
  173. * A database query result resource, or FALSE if the query was not executed
  174. * correctly.
  175. *
  176. * Example usage:
  177. * @code
  178. * $columns = array('feature_id','name');
  179. * $values = array(
  180. * 'organism_id' => array(
  181. * 'genus' => 'Citrus',
  182. * 'species' => 'sinensis',
  183. * ),
  184. * 'uniquename' => 'orange1.1g000034m.g',
  185. * 'type_id' => array (
  186. * 'cv_id' => array (
  187. * 'name' => 'sequence',
  188. * ),
  189. * 'name' => 'gene',
  190. * 'is_obsolete' => 0
  191. * ),
  192. * );
  193. * $result = tripal_core_chado_select('feature',$columns,$values);
  194. * @endcode
  195. * The above code selects a record from the feature table using the three fields
  196. * that uniquely identify a feature. The $columns array simply lists the columns
  197. * to select. The $values array is nested such that the organism is identified by
  198. * way of the organism_id foreign key constraint by specifying the genus and
  199. * species. The cvterm is also specified using its foreign key and the cv_id
  200. * for the cvterm is nested as well.
  201. */
  202. function tripal_core_chado_select($table,$columns,$values){
  203. // get the table description
  204. $chado = tripal_core_get_chado_schema();
  205. $table_desc = $chado[$table];
  206. $select = '';
  207. $from = '';
  208. $where = '';
  209. $args = array();
  210. foreach($values as $field => $value){
  211. $select[] = $field;
  212. if(is_array($value)){
  213. // select the value from the foreign key relationship for this value
  214. $where[$field] = tripal_core_chado_get_foreign_key($table_desc,$field,$value);
  215. }
  216. else {
  217. $where[$field] = $value;
  218. }
  219. }
  220. // now build the SQL select statement
  221. $sql = "SELECT " . implode(',',$columns) . " ";
  222. $sql .= "FROM {$table} ";
  223. $sql .= "WHERE ";
  224. foreach($where as $field => $value){
  225. $sql .= "$field = '%s' AND ";
  226. $args[] = $value;
  227. }
  228. $sql .= " 1=1"; // just add a 1=1 so we don't have trim off the last 'AND'
  229. return db_query($sql,$args);
  230. }
  231. /**
  232. * Gets the value of a foreign key relationship
  233. *
  234. * This function is used by tripal_core_chado_select, tripal_core_chado_insert,
  235. * and tripal_core_chado_update to iterate through the associate array of
  236. * values that gets passed to each of those routines. The values array
  237. * is nested where foreign key contraints are used to specify a value that. See
  238. * documentation for any of those functions for further information.
  239. *
  240. * @param $table_desc
  241. * A table description in Drupal Schema API format for the table with the
  242. * foreign key relationship that needs to be identified.
  243. * @param $field
  244. * The field in the table that is the foreign key.
  245. * @param $values
  246. * An associative array containing the values
  247. *
  248. * @return
  249. * A string containg the results of the foreign key lookup, or FALSE if
  250. * failed.
  251. *
  252. * Example usage:
  253. * @code
  254. *
  255. * $values = array(
  256. * 'genus' => 'Citrus',
  257. * 'species' => 'sinensis',
  258. * );
  259. * $value = tripal_core_chado_get_foreign_key('feature','organism_id',$values);
  260. *
  261. * @endcode
  262. * The above code selects a record from the feature table using the three fields
  263. * that uniquely identify a feature. The $columns array simply lists the columns
  264. * to select. The $values array is nested such that the organism is identified by
  265. * way of the organism_id foreign key constraint by specifying the genus and
  266. * species. The cvterm is also specified using its foreign key and the cv_id
  267. * for the cvterm is nested as well.
  268. */
  269. function tripal_core_chado_get_foreign_key($table_desc,$field,$values){
  270. // get the list of foreign keys for this table description and
  271. // iterate through those until we find the one we're looking for
  272. $fkeys = $table_desc['foreign keys'];
  273. if($fkeys){
  274. foreach($fkeys as $name => $def){
  275. $table = $def['table'];
  276. $columns = $def['columns'];
  277. // iterate through the columns of the foreign key relationship
  278. foreach($columns as $left => $right){
  279. // does the left column in the relationship match our field?
  280. if(strcmp($field,$left)==0){
  281. // the column name of the foreign key matches the field we want
  282. // so this is the right relationship. Now we want to select
  283. $select_cols = array($right);
  284. $result = db_fetch_object(tripal_core_chado_select($table,$select_cols,$values));
  285. return $result->$right;
  286. }
  287. }
  288. }
  289. }
  290. else {
  291. // TODO: what do we do if we get to this point and we have a fk
  292. // relationship expected but we don't have any definition for one in the
  293. // table schema??
  294. }
  295. return false;
  296. }