tripal_core.api.inc 21 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553
  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. /**
  10. * @defgroup tripal_api Tripal API
  11. * @{
  12. * Provides an application programming interface (API) for the tripal package
  13. *
  14. * The Tripal API currently provides generic insert/update/select functions for all chado content as
  15. * well as some module specific functions that insert/update/delete/select specific chado content.
  16. *
  17. * This API is currently in it's infancy so some necessary functions might be missing. If you come
  18. * across a missing function that you think should be included go to the sourceforge feature request * page and request it's inclusion in the API. Such feature requests with a working function * definition will be given priority.
  19. * @}
  20. */
  21. /**
  22. * @defgroup tripal_schema_api Tripal Schema API
  23. * @{
  24. * Provides an application programming interface (API) used to describe chado tables to
  25. * drupal/tripal
  26. * @}
  27. * @ingroup tripal_api
  28. */
  29. require_once "tripal_core.schema.api.inc";
  30. // just a temporary function for debugging
  31. function tripal_core_chado_insert_test(){
  32. $ivalues = array(
  33. 'organism_id' => array(
  34. 'genus' => 'Lens',
  35. 'species' => 'culinaris',
  36. ),
  37. 'name' => 'orange1.1g000034m.g',
  38. 'uniquename' => 'orange1.1g000034m.g9',
  39. 'type_id' => array (
  40. 'cv_id' => array (
  41. 'name' => 'sequence',
  42. ),
  43. 'name' => 'gene',
  44. 'is_obsolete' => 0
  45. ),
  46. );
  47. $umatch = array(
  48. 'organism_id' => array(
  49. 'genus' => 'Lens',
  50. 'species' => 'culinaris',
  51. ),
  52. 'uniquename' => 'orange1.1g000034m.g9',
  53. 'type_id' => array (
  54. 'cv_id' => array (
  55. 'name' => 'sequence',
  56. ),
  57. 'name' => 'gene',
  58. 'is_obsolete' => 0
  59. ),
  60. );
  61. $uvalues = array(
  62. 'name' => 'orange1.1g000034m.g',
  63. 'type_id' => array (
  64. 'cv_id' => array (
  65. 'name' => 'sequence',
  66. ),
  67. 'name' => 'mRNA',
  68. 'is_obsolete' => 0
  69. ),
  70. );
  71. $select_multiple = array(
  72. 'dbxref_id' => array(
  73. 'db_id' => 2,
  74. )
  75. );
  76. //$result = tripal_core_chado_insert('feature',$ivalues);
  77. //return "$result->feature_id";
  78. $result = tripal_core_chado_update('feature',$umatch,$uvalues);
  79. //$result = tripal_core_chado_select('feature',array('type_id', 'uniquename'),$select_multiple);
  80. return $result;
  81. }
  82. /**
  83. * Provides a generic routine for inserting into any Chado table
  84. *
  85. * Use this function to insert a record into any Chado table. The first
  86. * argument specifies the table for inserting and the second is an array
  87. * of values to be inserted. The array is mutli-dimensional such that
  88. * foreign key lookup values can be specified.
  89. *
  90. * @param $table
  91. * The name of the chado table for inserting
  92. * @param $values
  93. * An associative array containing the values for inserting.
  94. *
  95. * @return
  96. * On success this function returns TRUE. On failure, it returns FALSE.
  97. *
  98. * Example usage:
  99. * @code
  100. * $values = array(
  101. * 'organism_id' => array(
  102. * 'genus' => 'Citrus',
  103. * 'species' => 'sinensis',
  104. * ),
  105. * 'name' => 'orange1.1g000034m.g',
  106. * 'uniquename' => 'orange1.1g000034m.g',
  107. * 'type_id' => array (
  108. * 'cv_id' => array (
  109. * 'name' => 'sequence',
  110. * ),
  111. * 'name' => 'gene',
  112. * 'is_obsolete' => 0
  113. * ),
  114. * );
  115. * $result = tripal_core_chado_insert('feature',$values);
  116. * @endcode
  117. * The above code inserts a record into the feature table. The $values array is
  118. * nested such that the organism is selected by way of the organism_id foreign
  119. * key constraint by specifying the genus and species. The cvterm is also
  120. * specified using its foreign key and the cv_id for the cvterm is nested as
  121. * well.
  122. *
  123. * @ingroup tripal_api
  124. */
  125. function tripal_core_chado_insert($table,$values){
  126. $insert_values = array();
  127. // get the table description
  128. $table_desc = module_invoke_all('chado_'.$table.'_schema');
  129. // iterate through the values array and create a new 'insert_values' array
  130. // that has all the values needed for insert with all foreign relationsihps
  131. // resolved.
  132. foreach($values as $field => $value){
  133. if(is_array($value)){
  134. // select the value from the foreign key relationship for this value
  135. $results = tripal_core_chado_get_foreign_key($table_desc,$field,$value);
  136. if (sizeof($results) > 1) {
  137. watchdog('tripal_core', 'tripal_core_chado_insert: Too many records match the criteria supplied for !foreign_key foreign key constraint (!criteria)', array('!foreign_key' => $field, '!criteria' => print_r($value,TRUE)), WATCHDOG_ERROR);
  138. } elseif (sizeof($results) < 1) {
  139. watchdog('tripal_core', 'tripal_core_chado_insert: no record matches criteria supplied for !foreign_key foreign key constraint (!criteria)', array('!foreign_key' => $field, '!criteria' => print_r($value,TRUE)), WATCHDOG_ERROR);
  140. } else {
  141. $insert_values[$field] = $results[0];
  142. }
  143. }
  144. else {
  145. $insert_values[$field] = $value;
  146. }
  147. }
  148. // check for violation of any unique constraints
  149. $ukeys = $table_desc['unique keys'];
  150. $ukselect_cols = array();
  151. $ukselect_vals = array();
  152. foreach($ukeys as $name => $fields){
  153. foreach($fields as $index => $field){
  154. // build the arrays for performing a select that will check the contraint
  155. array_push($ukselect_cols,$field);
  156. $ukselect_vals[$field] = $insert_values[$field];
  157. }
  158. // now check the constraint
  159. if(tripal_core_chado_select($table,$ukselect_cols,$ukselect_vals)){
  160. watchdog('tripal_core',"tripal_core_chado_insert: Cannot insert duplicate record into $table table: " . print_r($values,1),array(),'WATCHDOG_ERROR');
  161. return false;
  162. }
  163. }
  164. // if trying to insert a field that is the primary key, make sure it also is unique
  165. $pkey = $table_desc['primary key'][0];
  166. if($insert_values[$pkey]){
  167. if(tripal_core_chado_select($table,array($pkey),array($pkey => $insert_values[$pkey]))){
  168. watchdog('tripal_core',"tripal_core_chado_insert: Cannot insert duplicate primary key into $table table: " . print_r($values,1),array(),'WATCHDOG_ERROR');
  169. return false;
  170. }
  171. }
  172. // make sure required fields have a value
  173. $fields = $table_desc['fields'];
  174. foreach($fields as $field => $def){
  175. // a field is considered missing if it cannot be null and there is no default
  176. // value for it or it is of type 'serial'
  177. if($def['not null'] == 1 and !array_key_exists($field,$insert_values) and !$def['default'] and strcmp($def['type'],serial)!=0){
  178. watchdog('tripal_core',"tripal_core_chado_insert: Field $table.$field cannot be null: " . print_r($values,1),array(),'WATCHDOG_ERROR');
  179. return false;
  180. }
  181. }
  182. // Now build the insert SQL statement
  183. $ifields = array();
  184. $ivalues = array();
  185. $itypes = array();
  186. foreach ($insert_values as $field => $value){
  187. array_push($ifields,$field);
  188. array_push($ivalues,$value);
  189. if(strcmp($fields[$field]['type'],'serial')==0 or
  190. strcmp($fields[$field]['type'],'int')==0){
  191. array_push($itypes,"%d");
  192. } else {
  193. array_push($itypes,"'%s'");
  194. }
  195. }
  196. $sql = "INSERT INTO {$table} (" . implode(", ",$ifields) . ") VALUES (". implode(", ",$itypes) .")";
  197. // finally perform the insert.
  198. if(db_query($sql,$ivalues)){
  199. return true;
  200. }
  201. else {
  202. watchdog('tripal_core',"tripal_core_chado_insert: Cannot insert record into $table table: " . print_r($values,1),array(),'WATCHDOG_ERROR');
  203. return false;
  204. }
  205. return false;
  206. }
  207. /**
  208. * Provides a generic routine for updating into any Chado table
  209. *
  210. * Use this function to update a record in any Chado table. The first
  211. * argument specifies the table for inserting, the second is an array
  212. * of values to matched for locating the record for updating, and the third
  213. * argument give the values to update. The arrays are mutli-dimensional such
  214. * that foreign key lookup values can be specified.
  215. *
  216. * @param $table
  217. * The name of the chado table for inserting
  218. * @param $match
  219. * An associative array containing the values for locating a record to update.
  220. * @param $values
  221. * An associative array containing the values for updating.
  222. *
  223. * @return
  224. * On success this function returns TRUE. On failure, it returns FALSE.
  225. *
  226. * Example usage:
  227. * @code
  228. $umatch = array(
  229. 'organism_id' => array(
  230. 'genus' => 'Citrus',
  231. 'species' => 'sinensis',
  232. ),
  233. 'uniquename' => 'orange1.1g000034m.g7',
  234. 'type_id' => array (
  235. 'cv_id' => array (
  236. 'name' => 'sequence',
  237. ),
  238. 'name' => 'gene',
  239. 'is_obsolete' => 0
  240. ),
  241. );
  242. $uvalues = array(
  243. 'name' => 'orange1.1g000034m.g',
  244. 'type_id' => array (
  245. 'cv_id' => array (
  246. 'name' => 'sequence',
  247. ),
  248. 'name' => 'mRNA',
  249. 'is_obsolete' => 0
  250. ),
  251. );
  252. * $result = tripal_core_chado_update('feature',$umatch,$uvalues);
  253. * @endcode
  254. * The above code species that a feature with a given uniquename, organism_id,
  255. * and type_id (the unique constraint for the feature table) will be updated.
  256. * The organism_id is specified as a nested array that uses the organism_id
  257. * foreign key constraint to lookup the specified values to find the exact
  258. * organism_id. The same nested struture is also used for specifying the
  259. * values to update. The function will find the record that matches the
  260. * columns specified and update the record with the avlues in the $uvalues array.
  261. *
  262. * @ingroup tripal_api
  263. */
  264. function tripal_core_chado_update($table,$match,$values){
  265. $update_values = array(); // contains the values to be updated
  266. $update_matches = array(); // contains the values for the where clause
  267. // get the table description
  268. $table_desc = module_invoke_all('chado_'.$table.'_schema');
  269. // get the values needed for matching in the SQL statement
  270. foreach ($match as $field => $value){
  271. if(is_array($value)){
  272. $results = tripal_core_chado_get_foreign_key($table_desc,$field,$value);
  273. if (sizeof($results) > 1) {
  274. watchdog('tripal_core', 'tripal_core_chado_update: When trying to find record to update, too many records match the criteria supplied for !foreign_key foreign key constraint (!criteria)', array('!foreign_key' => $field, '!criteria' => print_r($value,TRUE)), WATCHDOG_ERROR);
  275. } elseif (sizeof($results) < 1) {
  276. watchdog('tripal_core', 'tripal_core_chado_update: When trying to find record to update, no record matches criteria supplied for !foreign_key foreign key constraint (!criteria)', array('!foreign_key' => $field, '!criteria' => print_r($value,TRUE)), WATCHDOG_ERROR);
  277. } else {
  278. $update_matches[$field] = $results[0];
  279. }
  280. }
  281. else {
  282. $update_matches[$field] = $value;
  283. }
  284. }
  285. // get the values used for updating
  286. foreach ($values as $field => $value){
  287. if(is_array($value)){
  288. $results = tripal_core_chado_get_foreign_key($table_desc,$field,$value);
  289. if (sizeof($results) > 1) {
  290. watchdog('tripal_core', 'tripal_core_chado_update: When trying to find update values, too many records match the criteria supplied for !foreign_key foreign key constraint (!criteria)', array('!foreign_key' => $field, '!criteria' => print_r($value,TRUE)), WATCHDOG_ERROR);
  291. } elseif (sizeof($results) < 1) {
  292. watchdog('tripal_core', 'tripal_core_chado_update: When trying to find update values, no record matches criteria supplied for !foreign_key foreign key constraint (!criteria)', array('!foreign_key' => $field, '!criteria' => print_r($value,TRUE)), WATCHDOG_ERROR);
  293. } else {
  294. $update_values[$field] = $results[0];
  295. }
  296. }
  297. else {
  298. $update_values[$field] = $value;
  299. }
  300. }
  301. // now build the SQL statement
  302. $sql = "UPDATE {$table} SET ";
  303. $fields = $table_desc['fields'];
  304. $uargs = array();
  305. foreach($update_values as $field => $value){
  306. if(strcmp($fields[$field]['type'],'serial')==0 or
  307. strcmp($fields[$field]['type'],'int')==0){
  308. $sql .= " $field = %d, ";
  309. } else {
  310. $sql .= " $field = '%s', ";
  311. }
  312. array_push($uargs,$value);
  313. }
  314. $sql = substr($sql,0,-2); // get rid of the trailing comma & space
  315. $sql .= " WHERE ";
  316. foreach($update_matches as $field => $value){
  317. if(strcmp($fields[$field]['type'],'serial')==0 or
  318. strcmp($fields[$field]['type'],'int')==0){
  319. $sql .= " $field = %d AND ";
  320. } else {
  321. $sql .= " $field = '%s' AND ";
  322. }
  323. array_push($uargs,$value);
  324. }
  325. $sql = substr($sql,0,-4); // get rid of the trailing 'AND'
  326. // finally perform the update. If successful, return the updated record
  327. if(db_query($sql,$uargs)){
  328. return true;
  329. }
  330. else {
  331. watchdog('tripal_core',"Cannot update record in $table table. Match:" . print_r($match,1) . ". Values: ". print_r($values,1),array(),'WATCHDOG_ERROR');
  332. return false;
  333. }
  334. return false;
  335. }
  336. /**
  337. * Provides a generic routine for selecting data from a Chado table
  338. *
  339. * Use this function to perform a simple select from any Chado table.
  340. *
  341. * @param $table
  342. * The name of the chado table for inserting
  343. * @param $columns
  344. * An array of column names
  345. * @param $values
  346. * An associative array containing the values for filtering the results.
  347. * @param $has_record
  348. * Set this argument to 'true' to have this function return a numeric
  349. * value for the number of recrods rather than the array of records. this
  350. * can be useful in 'if' statements to check the presence of particula records.
  351. *
  352. * @return
  353. * A database query result resource, FALSE if the query was not executed
  354. * correctly, or the number of records in the dataset if $has_record is set.
  355. *
  356. * Example usage:
  357. * @code
  358. * $columns = array('feature_id','name');
  359. * $values = array(
  360. * 'organism_id' => array(
  361. * 'genus' => 'Citrus',
  362. * 'species' => 'sinensis',
  363. * ),
  364. * 'uniquename' => 'orange1.1g000034m.g',
  365. * 'type_id' => array (
  366. * 'cv_id' => array (
  367. * 'name' => 'sequence',
  368. * ),
  369. * 'name' => 'gene',
  370. * 'is_obsolete' => 0
  371. * ),
  372. * );
  373. * $result = tripal_core_chado_select('feature',$columns,$values);
  374. * @endcode
  375. * The above code selects a record from the feature table using the three fields
  376. * that uniquely identify a feature. The $columns array simply lists the columns
  377. * to select. The $values array is nested such that the organism is identified by
  378. * way of the organism_id foreign key constraint by specifying the genus and
  379. * species. The cvterm is also specified using its foreign key and the cv_id
  380. * for the cvterm is nested as well.
  381. *
  382. * @ingroup tripal_api
  383. */
  384. function tripal_core_chado_select($table,$columns,$values,$has_record = 0){
  385. // get the table description
  386. $table_desc = module_invoke_all('chado_'.$table.'_schema');
  387. $select = '';
  388. $from = '';
  389. $where = '';
  390. $args = array();
  391. foreach($values as $field => $value){
  392. $select[] = $field;
  393. if(is_array($value)){
  394. // select the value from the foreign key relationship for this value
  395. $results = tripal_core_chado_get_foreign_key($table_desc,$field,$value);
  396. if (sizeof($results) < 1) {
  397. // foreign key records are required
  398. // thus if none matched then return false and alert the admin through watchdog
  399. watchdog('tripal_core',
  400. 'tripal_core_chado_select: no record in the table referenced by the foreign key (!field) exists. tripal_core_chado_select table=!table, columns=!columns, values=!values',
  401. array('!table' => $table,
  402. '!columns' => '<pre>' . print_r($columns, TRUE) . '</pre>',
  403. '!values' => '<pre>' . print_r($values, TRUE) . '</pre>',
  404. '!field' => $field,
  405. ),
  406. WATCHDOG_WARNING);
  407. return false;
  408. } elseif (sizeof($results) == 1) {
  409. $where[$field] = $results[0];
  410. } else {
  411. $where[$field] = $results;
  412. }
  413. }
  414. else {
  415. $where[$field] = $value;
  416. }
  417. }
  418. // now build the SQL select statement
  419. if (empty($where)) {
  420. // sometimes want to select everything
  421. $sql = "SELECT " . implode(',',$columns) . " ";
  422. $sql .= "FROM {$table} ";
  423. } else {
  424. $sql = "SELECT " . implode(',',$columns) . " ";
  425. $sql .= "FROM {$table} ";
  426. $sql .= "WHERE ";
  427. foreach($where as $field => $value){
  428. if (is_array($value)) {
  429. $sql .= "$field IN (".db_placeholders($value,'varchar').") AND ";
  430. foreach ($value as $v) { $args[] = $v; }
  431. } else {
  432. $sql .= "$field = '%s' AND ";
  433. $args[] = $value;
  434. }
  435. }
  436. $sql = substr($sql,0,-4); // get rid of the trailing 'AND'
  437. }
  438. $resource = db_query($sql,$args);
  439. $results = array();
  440. while ($r = db_fetch_object($resource)) {
  441. $results[] = $r;
  442. }
  443. if(!$has_record){
  444. return $results;
  445. } else{
  446. return count($results);
  447. }
  448. }
  449. /**
  450. * Gets the value of a foreign key relationship
  451. *
  452. * This function is used by tripal_core_chado_select, tripal_core_chado_insert,
  453. * and tripal_core_chado_update to iterate through the associate array of
  454. * values that gets passed to each of those routines. The values array
  455. * is nested where foreign key contraints are used to specify a value that. See
  456. * documentation for any of those functions for further information.
  457. *
  458. * @param $table_desc
  459. * A table description for the table with the foreign key relationship to be identified generated by
  460. * hook_chado_<table name>_schema()
  461. * @param $field
  462. * The field in the table that is the foreign key.
  463. * @param $values
  464. * An associative array containing the values
  465. *
  466. * @return
  467. * A string containg the results of the foreign key lookup, or FALSE if failed.
  468. *
  469. * Example usage:
  470. * @code
  471. *
  472. * $values = array(
  473. * 'genus' => 'Citrus',
  474. * 'species' => 'sinensis',
  475. * );
  476. * $value = tripal_core_chado_get_foreign_key('feature','organism_id',$values);
  477. *
  478. * @endcode
  479. * The above code selects a record from the feature table using the three fields
  480. * that uniquely identify a feature. The $columns array simply lists the columns
  481. * to select. The $values array is nested such that the organism is identified by
  482. * way of the organism_id foreign key constraint by specifying the genus and
  483. * species. The cvterm is also specified using its foreign key and the cv_id
  484. * for the cvterm is nested as well.
  485. *
  486. * @ingroup tripal_api
  487. */
  488. function tripal_core_chado_get_foreign_key($table_desc,$field,$values){
  489. // get the list of foreign keys for this table description and
  490. // iterate through those until we find the one we're looking for
  491. $fkeys = $table_desc['foreign keys'];
  492. if($fkeys){
  493. foreach($fkeys as $name => $def){
  494. if (is_array($def['table'])) {
  495. //foreign key was described 2X
  496. $message = "The foreign key ".$name." was defined twice. Please check modules to determine if hook_chado_".$table_desc['table']."_schema() was implemented and defined this foreign key when it wasn't supposed to. Modules this hook was implemented in: ".implode(', ', module_implements("chado_".$table_desc['table']."_schema")).".";
  497. watchdog('tripal_core', $message);
  498. drupal_set_message($message,'error');
  499. continue;
  500. }
  501. $table = $def['table'];
  502. $columns = $def['columns'];
  503. // iterate through the columns of the foreign key relationship
  504. foreach($columns as $left => $right){
  505. // does the left column in the relationship match our field?
  506. if(strcmp($field,$left)==0){
  507. // the column name of the foreign key matches the field we want
  508. // so this is the right relationship. Now we want to select
  509. $select_cols = array($right);
  510. $result = tripal_core_chado_select($table,$select_cols,$values);
  511. $fields = array();
  512. foreach ($result as $obj) {
  513. $fields[] = $obj->$right;
  514. }
  515. return $fields;
  516. }
  517. }
  518. }
  519. }
  520. else {
  521. // TODO: what do we do if we get to this point and we have a fk
  522. // relationship expected but we don't have any definition for one in the
  523. // table schema??
  524. $message = "There is no foreign key relationship defined for ".$field.". To define a foreign key relationship, determine the table this foreign key referrs to (<foreign table>) and then implement hook_chado_<foreign table>_schema(). See tripal_feature_chado_feature_schema for an example.";
  525. watchdog('tripal_core', $message);
  526. drupal_set_message($message,'error');
  527. }
  528. return false;
  529. }