tripal_core.api.inc 18 KB

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