tripal_core.api.inc 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443
  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. $ivalues = 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. $umatch = array(
  28. 'organism_id' => array(
  29. 'genus' => 'Citrus',
  30. 'species' => 'sinensis',
  31. ),
  32. 'uniquename' => 'orange1.1g000034m.g7',
  33. 'type_id' => array (
  34. 'cv_id' => array (
  35. 'name' => 'sequence',
  36. ),
  37. 'name' => 'gene',
  38. 'is_obsolete' => 0
  39. ),
  40. );
  41. $uvalues = array(
  42. 'name' => 'orange1.1g000034m.g',
  43. 'type_id' => array (
  44. 'cv_id' => array (
  45. 'name' => 'sequence',
  46. ),
  47. 'name' => 'mRNA',
  48. 'is_obsolete' => 0
  49. ),
  50. );
  51. // $result = tripal_core_chado_insert('feature',$ivalues);
  52. // return "$result->feature_id";
  53. $result = tripal_core_chado_update('feature',$umatch,$uvalues);
  54. return $result;
  55. }
  56. /**
  57. * Provides a generic routine for inserting into any Chado table
  58. *
  59. * Use this function to insert a record into any Chado table. The first
  60. * argument specifies the table for inserting and the second is an array
  61. * of values to be inserted. The array is mutli-dimensional such that
  62. * foreign key lookup values can be specified.
  63. *
  64. * @param $table
  65. * The name of the chado table for inserting
  66. * @param $values
  67. * An associative array containing the values for inserting.
  68. *
  69. * @return
  70. * On success this function returns TRUE. On failure, it returns FALSE.
  71. *
  72. * Example usage:
  73. * @code
  74. * $values = array(
  75. * 'organism_id' => array(
  76. * 'genus' => 'Citrus',
  77. * 'species' => 'sinensis',
  78. * ),
  79. * 'name' => 'orange1.1g000034m.g',
  80. * 'uniquename' => 'orange1.1g000034m.g',
  81. * 'type_id' => array (
  82. * 'cv_id' => array (
  83. * 'name' => 'sequence',
  84. * ),
  85. * 'name' => 'gene',
  86. * 'is_obsolete' => 0
  87. * ),
  88. * );
  89. * $result = tripal_core_chado_insert('feature',$values);
  90. * @endcode
  91. * The above code inserts a record into the feature table. The $values array is
  92. * nested such that the organism is selected by way of the organism_id foreign
  93. * key constraint by specifying the genus and species. The cvterm is also
  94. * specified using its foreign key and the cv_id for the cvterm is nested as
  95. * well.
  96. */
  97. function tripal_core_chado_insert($table,$values){
  98. $insert_values = array();
  99. // get the table description
  100. $chado = tripal_core_get_chado_schema();
  101. $table_desc = $chado[$table];
  102. // iterate through the values array and create a new 'insert_values' array
  103. // that has all the values needed for insert with all foreign relationsihps
  104. // resolved.
  105. foreach($values as $field => $value){
  106. if(is_array($value)){
  107. // select the value from the foreign key relationship for this value
  108. $insert_values[$field] = tripal_core_chado_get_foreign_key($table_desc,$field,$value);
  109. }
  110. else {
  111. $insert_values[$field] = $value;
  112. }
  113. }
  114. // check for violation of any unique constraints
  115. $ukeys = $table_desc['unique keys'];
  116. $ukselect_cols = array();
  117. $ukselect_vals = array();
  118. foreach($ukeys as $name => $fields){
  119. foreach($fields as $index => $field){
  120. // build the arrays for performing a select that will check the contraint
  121. array_push($ukselect_cols,$field);
  122. $ukselect_vals[$field] = $insert_values[$field];
  123. }
  124. // now check the constraint
  125. if(db_fetch_object(tripal_core_chado_select($table,$ukselect_cols,$ukselect_vals))){
  126. watchdog('tripal_core',"Cannot insert duplicate record into $table table: " . print_r($values,1),array(),'WATCHDOG_ERROR');
  127. return false;
  128. }
  129. }
  130. // if trying to insert a field that is the primary key, make sure it also is unique
  131. $pkey = $table_desc['primary key'][0];
  132. if($insert_values[$pkey]){
  133. if(db_fetch_object(tripal_core_chado_select($table,array($pkey),array($pkey => $insert_values[$pkey])))){
  134. watchdog('tripal_core',"Cannot insert duplicate primary key into $table table: " . print_r($values,1),array(),'WATCHDOG_ERROR');
  135. return false;
  136. }
  137. }
  138. // make sure required fields have a value
  139. $fields = $table_desc['fields'];
  140. foreach($fields as $field => $def){
  141. // a field is considered missing if it cannot be null and there is no default
  142. // value for it or it is of type 'serial'
  143. if($def['not null'] == 1 and !$insert_values[$field] and !$def['default'] and strcmp($def['type'],serial)!=0){
  144. watchdog('tripal_core',"Field $field cannot be null: " . print_r($values,1),array(),'WATCHDOG_ERROR');
  145. return false;
  146. }
  147. }
  148. // Now build the insert SQL statement
  149. $ifields = array();
  150. $ivalues = array();
  151. $itypes = array();
  152. foreach ($insert_values as $field => $value){
  153. array_push($ifields,$field);
  154. array_push($ivalues,$value);
  155. if(strcmp($fields[$field]['type'],'serial')==0 or
  156. strcmp($fields[$field]['type'],'int')==0){
  157. array_push($itypes,"%d");
  158. } else {
  159. array_push($itypes,"'%s'");
  160. }
  161. }
  162. $sql = "INSERT INTO {$table} (" . implode(", ",$ifields) . ") VALUES (". implode(", ",$itypes) .")";
  163. // finally perform the insert.
  164. if(db_query($sql,$ivalues)){
  165. return true;
  166. }
  167. else {
  168. watchdog('tripal_core',"Cannot insert record into $table table: " . print_r($values,1),array(),'WATCHDOG_ERROR');
  169. return false;
  170. }
  171. return false;
  172. }
  173. /**
  174. * Provides a generic routine for updating into any Chado table
  175. *
  176. * Use this function to update a record in any Chado table. The first
  177. * argument specifies the table for inserting, the second is an array
  178. * of values to matched for locating the record for updating, and the third
  179. * argument give the values to update. The arrays are mutli-dimensional such
  180. * that foreign key lookup values can be specified.
  181. *
  182. * @param $table
  183. * The name of the chado table for inserting
  184. * @param $match
  185. * An associative array containing the values for locating a record to update.
  186. * @param $values
  187. * An associative array containing the values for updating.
  188. *
  189. * @return
  190. * On success this function returns TRUE. On failure, it returns FALSE.
  191. *
  192. * Example usage:
  193. * @code
  194. $umatch = array(
  195. 'organism_id' => array(
  196. 'genus' => 'Citrus',
  197. 'species' => 'sinensis',
  198. ),
  199. 'uniquename' => 'orange1.1g000034m.g7',
  200. 'type_id' => array (
  201. 'cv_id' => array (
  202. 'name' => 'sequence',
  203. ),
  204. 'name' => 'gene',
  205. 'is_obsolete' => 0
  206. ),
  207. );
  208. $uvalues = array(
  209. 'name' => 'orange1.1g000034m.g',
  210. 'type_id' => array (
  211. 'cv_id' => array (
  212. 'name' => 'sequence',
  213. ),
  214. 'name' => 'mRNA',
  215. 'is_obsolete' => 0
  216. ),
  217. );
  218. * $result = tripal_core_chado_update('feature',$umatch,$uvalues);
  219. * @endcode
  220. * The above code species that a feature with a given uniquename, organism_id,
  221. * and type_id (the unique constraint for the feature table) will be updated.
  222. * The organism_id is specified as a nested array that uses the organism_id
  223. * foreign key constraint to lookup the specified values to find the exact
  224. * organism_id. The same nested struture is also used for specifying the
  225. * values to update. The function will find the record that matches the
  226. * columns specified and update the record with the avlues in the $uvalues array.
  227. */
  228. function tripal_core_chado_update($table,$match,$values){
  229. $update_values = array(); // contains the values to be updated
  230. $update_matches = array(); // contains the values for the where clause
  231. // get the table description
  232. $chado = tripal_core_get_chado_schema();
  233. $table_desc = $chado[$table];
  234. // get the values needed for matching in the SQL statement
  235. foreach ($match as $field => $value){
  236. if(is_array($value)){
  237. $update_matches[$field] = tripal_core_chado_get_foreign_key($table_desc,$field,$value);
  238. }
  239. else {
  240. $update_matches[$field] = $value;
  241. }
  242. }
  243. // get the values used for updating
  244. foreach ($values as $field => $value){
  245. if(is_array($value)){
  246. $update_values[$field] = tripal_core_chado_get_foreign_key($table_desc,$field,$value);
  247. }
  248. else {
  249. $update_values[$field] = $value;
  250. }
  251. }
  252. // now build the SQL statement
  253. $sql = "UPDATE {$table} SET ";
  254. $fields = $table_desc['fields'];
  255. $uargs = array();
  256. foreach($update_values as $field => $value){
  257. if(strcmp($fields[$field]['type'],'serial')==0 or
  258. strcmp($fields[$field]['type'],'int')==0){
  259. $sql .= " $field = %d, ";
  260. } else {
  261. $sql .= " $field = '%s', ";
  262. }
  263. array_push($uargs,$value);
  264. }
  265. $sql = substr($sql,0,-2); // get rid of the trailing comma & space
  266. $sql .= " WHERE ";
  267. foreach($update_matches as $field => $value){
  268. if(strcmp($fields[$field]['type'],'serial')==0 or
  269. strcmp($fields[$field]['type'],'int')==0){
  270. $sql .= " $field = %d AND ";
  271. } else {
  272. $sql .= " $field = '%s' AND ";
  273. }
  274. array_push($uargs,$value);
  275. }
  276. $sql = substr($sql,0,-4); // get rid of the trailing 'AND'
  277. // finally perform the update. If successful, return the updated record
  278. if(db_query($sql,$uargs)){
  279. return true;
  280. }
  281. else {
  282. watchdog('tripal_core',"Cannot update record in $table table. Match:" . print_r($match,1) . ". Values: ". print_r($values,1),array(),'WATCHDOG_ERROR');
  283. return false;
  284. }
  285. return false;
  286. }
  287. /**
  288. * Provides a generic routine for selecting data from a Chado table
  289. *
  290. * Use this function to perform a simple select from any Chado table.
  291. *
  292. * @param $table
  293. * The name of the chado table for inserting
  294. * @param $columns
  295. * An array of column names
  296. * @param $values
  297. * An associative array containing the values for filtering the results.
  298. *
  299. * @return
  300. * A database query result resource, or FALSE if the query was not executed
  301. * correctly.
  302. *
  303. * Example usage:
  304. * @code
  305. * $columns = array('feature_id','name');
  306. * $values = array(
  307. * 'organism_id' => array(
  308. * 'genus' => 'Citrus',
  309. * 'species' => 'sinensis',
  310. * ),
  311. * 'uniquename' => 'orange1.1g000034m.g',
  312. * 'type_id' => array (
  313. * 'cv_id' => array (
  314. * 'name' => 'sequence',
  315. * ),
  316. * 'name' => 'gene',
  317. * 'is_obsolete' => 0
  318. * ),
  319. * );
  320. * $result = tripal_core_chado_select('feature',$columns,$values);
  321. * @endcode
  322. * The above code selects a record from the feature table using the three fields
  323. * that uniquely identify a feature. The $columns array simply lists the columns
  324. * to select. The $values array is nested such that the organism is identified by
  325. * way of the organism_id foreign key constraint by specifying the genus and
  326. * species. The cvterm is also specified using its foreign key and the cv_id
  327. * for the cvterm is nested as well.
  328. */
  329. function tripal_core_chado_select($table,$columns,$values){
  330. // get the table description
  331. $chado = tripal_core_get_chado_schema();
  332. $table_desc = $chado[$table];
  333. $select = '';
  334. $from = '';
  335. $where = '';
  336. $args = array();
  337. foreach($values as $field => $value){
  338. $select[] = $field;
  339. if(is_array($value)){
  340. // select the value from the foreign key relationship for this value
  341. $where[$field] = tripal_core_chado_get_foreign_key($table_desc,$field,$value);
  342. }
  343. else {
  344. $where[$field] = $value;
  345. }
  346. }
  347. // now build the SQL select statement
  348. $sql = "SELECT " . implode(',',$columns) . " ";
  349. $sql .= "FROM {$table} ";
  350. $sql .= "WHERE ";
  351. foreach($where as $field => $value){
  352. $sql .= "$field = '%s' AND ";
  353. $args[] = $value;
  354. }
  355. $sql = substr($sql,0,-4); // get rid of the trailing 'AND'
  356. return db_query($sql,$args);
  357. }
  358. /**
  359. * Gets the value of a foreign key relationship
  360. *
  361. * This function is used by tripal_core_chado_select, tripal_core_chado_insert,
  362. * and tripal_core_chado_update to iterate through the associate array of
  363. * values that gets passed to each of those routines. The values array
  364. * is nested where foreign key contraints are used to specify a value that. See
  365. * documentation for any of those functions for further information.
  366. *
  367. * @param $table_desc
  368. * A table description in Drupal Schema API format for the table with the
  369. * foreign key relationship that needs to be identified.
  370. * @param $field
  371. * The field in the table that is the foreign key.
  372. * @param $values
  373. * An associative array containing the values
  374. *
  375. * @return
  376. * A string containg the results of the foreign key lookup, or FALSE if
  377. * failed.
  378. *
  379. * Example usage:
  380. * @code
  381. *
  382. * $values = array(
  383. * 'genus' => 'Citrus',
  384. * 'species' => 'sinensis',
  385. * );
  386. * $value = tripal_core_chado_get_foreign_key('feature','organism_id',$values);
  387. *
  388. * @endcode
  389. * The above code selects a record from the feature table using the three fields
  390. * that uniquely identify a feature. The $columns array simply lists the columns
  391. * to select. The $values array is nested such that the organism is identified by
  392. * way of the organism_id foreign key constraint by specifying the genus and
  393. * species. The cvterm is also specified using its foreign key and the cv_id
  394. * for the cvterm is nested as well.
  395. */
  396. function tripal_core_chado_get_foreign_key($table_desc,$field,$values){
  397. // get the list of foreign keys for this table description and
  398. // iterate through those until we find the one we're looking for
  399. $fkeys = $table_desc['foreign keys'];
  400. if($fkeys){
  401. foreach($fkeys as $name => $def){
  402. $table = $def['table'];
  403. $columns = $def['columns'];
  404. // iterate through the columns of the foreign key relationship
  405. foreach($columns as $left => $right){
  406. // does the left column in the relationship match our field?
  407. if(strcmp($field,$left)==0){
  408. // the column name of the foreign key matches the field we want
  409. // so this is the right relationship. Now we want to select
  410. $select_cols = array($right);
  411. $result = db_fetch_object(tripal_core_chado_select($table,$select_cols,$values));
  412. return $result->$right;
  413. }
  414. }
  415. }
  416. }
  417. else {
  418. // TODO: what do we do if we get to this point and we have a fk
  419. // relationship expected but we don't have any definition for one in the
  420. // table schema??
  421. }
  422. return false;
  423. }