tripal_core.api.inc 42 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079
  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. $previous_db = tripal_db_set_active('chado'); // use chado database
  199. $result = db_query($sql,$ivalues);
  200. tripal_db_set_active($previous_db); // now use drupal database
  201. if($result){
  202. return true;
  203. }
  204. else {
  205. watchdog('tripal_core',"tripal_core_chado_insert: Cannot insert record into $table table: " . print_r($values,1),array(),'WATCHDOG_ERROR');
  206. return false;
  207. }
  208. return false;
  209. }
  210. /**
  211. * Provides a generic routine for updating into any Chado table
  212. *
  213. * Use this function to update a record in any Chado table. The first
  214. * argument specifies the table for inserting, the second is an array
  215. * of values to matched for locating the record for updating, and the third
  216. * argument give the values to update. The arrays are mutli-dimensional such
  217. * that foreign key lookup values can be specified.
  218. *
  219. * @param $table
  220. * The name of the chado table for inserting
  221. * @param $match
  222. * An associative array containing the values for locating a record to update.
  223. * @param $values
  224. * An associative array containing the values for updating.
  225. *
  226. * @return
  227. * On success this function returns TRUE. On failure, it returns FALSE.
  228. *
  229. * Example usage:
  230. * @code
  231. $umatch = array(
  232. 'organism_id' => array(
  233. 'genus' => 'Citrus',
  234. 'species' => 'sinensis',
  235. ),
  236. 'uniquename' => 'orange1.1g000034m.g7',
  237. 'type_id' => array (
  238. 'cv_id' => array (
  239. 'name' => 'sequence',
  240. ),
  241. 'name' => 'gene',
  242. 'is_obsolete' => 0
  243. ),
  244. );
  245. $uvalues = array(
  246. 'name' => 'orange1.1g000034m.g',
  247. 'type_id' => array (
  248. 'cv_id' => array (
  249. 'name' => 'sequence',
  250. ),
  251. 'name' => 'mRNA',
  252. 'is_obsolete' => 0
  253. ),
  254. );
  255. * $result = tripal_core_chado_update('feature',$umatch,$uvalues);
  256. * @endcode
  257. * The above code species that a feature with a given uniquename, organism_id,
  258. * and type_id (the unique constraint for the feature table) will be updated.
  259. * The organism_id is specified as a nested array that uses the organism_id
  260. * foreign key constraint to lookup the specified values to find the exact
  261. * organism_id. The same nested struture is also used for specifying the
  262. * values to update. The function will find the record that matches the
  263. * columns specified and update the record with the avlues in the $uvalues array.
  264. *
  265. * @ingroup tripal_api
  266. */
  267. function tripal_core_chado_update($table,$match,$values){
  268. $update_values = array(); // contains the values to be updated
  269. $update_matches = array(); // contains the values for the where clause
  270. // get the table description
  271. $table_desc = module_invoke_all('chado_'.$table.'_schema');
  272. // get the values needed for matching in the SQL statement
  273. foreach ($match as $field => $value){
  274. if(is_array($value)){
  275. $results = tripal_core_chado_get_foreign_key($table_desc,$field,$value);
  276. if (sizeof($results) > 1) {
  277. 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);
  278. } elseif (sizeof($results) < 1) {
  279. 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);
  280. } else {
  281. $update_matches[$field] = $results[0];
  282. }
  283. }
  284. else {
  285. $update_matches[$field] = $value;
  286. }
  287. }
  288. // get the values used for updating
  289. foreach ($values as $field => $value){
  290. if(is_array($value)){
  291. $results = tripal_core_chado_get_foreign_key($table_desc,$field,$value);
  292. if (sizeof($results) > 1) {
  293. 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);
  294. } elseif (sizeof($results) < 1) {
  295. 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);
  296. } else {
  297. $update_values[$field] = $results[0];
  298. }
  299. }
  300. else {
  301. $update_values[$field] = $value;
  302. }
  303. }
  304. // now build the SQL statement
  305. $sql = "UPDATE {$table} SET ";
  306. $fields = $table_desc['fields'];
  307. $uargs = array();
  308. foreach($update_values as $field => $value){
  309. if(strcmp($fields[$field]['type'],'serial')==0 or
  310. strcmp($fields[$field]['type'],'int')==0){
  311. $sql .= " $field = %d, ";
  312. } else {
  313. $sql .= " $field = '%s', ";
  314. }
  315. array_push($uargs,$value);
  316. }
  317. $sql = substr($sql,0,-2); // get rid of the trailing comma & space
  318. $sql .= " WHERE ";
  319. foreach($update_matches as $field => $value){
  320. if(strcmp($fields[$field]['type'],'serial')==0 or
  321. strcmp($fields[$field]['type'],'int')==0){
  322. $sql .= " $field = %d AND ";
  323. } else {
  324. $sql .= " $field = '%s' AND ";
  325. }
  326. array_push($uargs,$value);
  327. }
  328. $sql = substr($sql,0,-4); // get rid of the trailing 'AND'
  329. // finally perform the update. If successful, return the updated record
  330. $previous_db = tripal_db_set_active('chado'); // use chado database
  331. $result = db_query($sql,$uargs);
  332. tripal_db_set_active($previous_db); // now use drupal database
  333. if($result){
  334. return true;
  335. }
  336. else {
  337. watchdog('tripal_core',"Cannot update record in $table table. Match:" . print_r($match,1) . ". Values: ". print_r($values,1),array(),'WATCHDOG_ERROR');
  338. return false;
  339. }
  340. return false;
  341. }
  342. /**
  343. * Provides a generic routine for selecting data from a Chado table
  344. *
  345. * Use this function to perform a simple select from any Chado table.
  346. *
  347. * @param $table
  348. * The name of the chado table for inserting
  349. * @param $columns
  350. * An array of column names
  351. * @param $values
  352. * An associative array containing the values for filtering the results. In the
  353. * case where multiple values for the same time are to be selected an additional
  354. * entry for the field should appear for each value
  355. * @param $has_record
  356. * Set this argument to 'true' to have this function return a numeric
  357. * value for the number of recrods rather than the array of records. this
  358. * can be useful in 'if' statements to check the presence of particula records.
  359. *
  360. * @return
  361. * A database query result resource, FALSE if the query was not executed
  362. * correctly, or the number of records in the dataset if $has_record is set.
  363. *
  364. * Example usage:
  365. * @code
  366. * $columns = array('feature_id','name');
  367. * $values = array(
  368. * 'organism_id' => array(
  369. * 'genus' => 'Citrus',
  370. * 'species' => array('sinensis','clementina'),
  371. * ),
  372. * 'uniquename' => 'orange1.1g000034m.g',
  373. * 'type_id' => array (
  374. * 'cv_id' => array (
  375. * 'name' => 'sequence',
  376. * ),
  377. * 'name' => 'gene',
  378. * 'is_obsolete' => 0
  379. * ),
  380. * );
  381. * $result = tripal_core_chado_select('feature',$columns,$values);
  382. * @endcode
  383. * The above code selects a record from the feature table using the three fields
  384. * that uniquely identify a feature. The $columns array simply lists the columns
  385. * to select. The $values array is nested such that the organism is identified by
  386. * way of the organism_id foreign key constraint by specifying the genus and
  387. * species. The cvterm is also specified using its foreign key and the cv_id
  388. * for the cvterm is nested as well. In the example above, two different species
  389. * are allowed to match
  390. *
  391. * @ingroup tripal_api
  392. */
  393. function tripal_core_chado_select($table,$columns,$values,$has_record = 0,$return_sql = 0){
  394. if (!is_array($columns)){
  395. watchdog('tripal_feature', 'the $columns argument for tripal_core_chado_select must be an array.');
  396. return false;
  397. }
  398. if (!is_array($values)){
  399. watchdog('tripal_feature', 'the $values argument for tripal_core_chado_select must be an array.');
  400. return false;
  401. }
  402. // get the table description
  403. $table_desc = module_invoke_all('chado_'.$table.'_schema');
  404. $select = '';
  405. $from = '';
  406. $where = '';
  407. $args = array();
  408. foreach($values as $field => $value){
  409. $select[] = $field;
  410. if(is_array($value)){
  411. // if the user has specified multiple values for matching then this we
  412. // want to catch that and save them in our $where array, otherwise
  413. // we'll descend for a foreign key relationship
  414. if(array_values($value) === $value){
  415. $where[$field] = $value;
  416. } else {
  417. // select the value from the foreign key relationship for this value
  418. $results = tripal_core_chado_get_foreign_key($table_desc,$field,$value);
  419. if (sizeof($results) < 1) {
  420. // foreign key records are required
  421. // thus if none matched then return false and alert the admin through watchdog
  422. watchdog('tripal_core',
  423. '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',
  424. array('!table' => $table,
  425. '!columns' => '<pre>' . print_r($columns, TRUE) . '</pre>',
  426. '!values' => '<pre>' . print_r($values, TRUE) . '</pre>',
  427. '!field' => $field,
  428. ),
  429. WATCHDOG_WARNING);
  430. return false;
  431. } else {
  432. $where[$field] = $results;
  433. }
  434. }
  435. }
  436. else {
  437. $where[$field][] = $value;
  438. }
  439. }
  440. // now build the SQL select statement
  441. if (empty($where)) {
  442. // sometimes want to select everything
  443. $sql = "SELECT " . implode(',',$columns) . " ";
  444. $sql .= "FROM {$table} ";
  445. } else {
  446. $sql = "SELECT " . implode(',',$columns) . " ";
  447. $sql .= "FROM {$table} ";
  448. $sql .= "WHERE ";
  449. foreach($where as $field => $value){
  450. if (count($value) > 1) {
  451. $sql .= "$field IN (".db_placeholders($value,'varchar').") AND ";
  452. foreach ($value as $v) { $args[] = $v; }
  453. } else {
  454. $sql .= "$field = '%s' AND ";
  455. $args[] = $value[0];
  456. }
  457. }
  458. $sql = substr($sql,0,-4); // get rid of the trailing 'AND'
  459. }
  460. // if the caller has requested the SQL rather than the results...
  461. // which happens in the case of wanting to use the Drupal pager, then do so
  462. if($return_sql){
  463. return array('sql'=> $sql, 'args' => $args);
  464. }
  465. $previous_db = tripal_db_set_active('chado'); // use chado database
  466. $resource = db_query($sql,$args);
  467. tripal_db_set_active($previous_db); // now use drupal database
  468. $results = array();
  469. while ($r = db_fetch_object($resource)) {
  470. $results[] = $r;
  471. }
  472. if(!$has_record){
  473. return $results;
  474. } else{
  475. return count($results);
  476. }
  477. }
  478. /**
  479. * Gets the value of a foreign key relationship
  480. *
  481. * This function is used by tripal_core_chado_select, tripal_core_chado_insert,
  482. * and tripal_core_chado_update to iterate through the associate array of
  483. * values that gets passed to each of those routines. The values array
  484. * is nested where foreign key contraints are used to specify a value that. See
  485. * documentation for any of those functions for further information.
  486. *
  487. * @param $table_desc
  488. * A table description for the table with the foreign key relationship to be identified generated by
  489. * hook_chado_<table name>_schema()
  490. * @param $field
  491. * The field in the table that is the foreign key.
  492. * @param $values
  493. * An associative array containing the values
  494. *
  495. * @return
  496. * A string containg the results of the foreign key lookup, or FALSE if failed.
  497. *
  498. * Example usage:
  499. * @code
  500. *
  501. * $values = array(
  502. * 'genus' => 'Citrus',
  503. * 'species' => 'sinensis',
  504. * );
  505. * $value = tripal_core_chado_get_foreign_key('feature','organism_id',$values);
  506. *
  507. * @endcode
  508. * The above code selects a record from the feature table using the three fields
  509. * that uniquely identify a feature. The $columns array simply lists the columns
  510. * to select. The $values array is nested such that the organism is identified by
  511. * way of the organism_id foreign key constraint by specifying the genus and
  512. * species. The cvterm is also specified using its foreign key and the cv_id
  513. * for the cvterm is nested as well.
  514. *
  515. * @ingroup tripal_api
  516. */
  517. function tripal_core_chado_get_foreign_key($table_desc,$field,$values){
  518. // get the list of foreign keys for this table description and
  519. // iterate through those until we find the one we're looking for
  520. $fkeys = $table_desc['foreign keys'];
  521. if($fkeys){
  522. foreach($fkeys as $name => $def){
  523. if (is_array($def['table'])) {
  524. //foreign key was described 2X
  525. $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")).".";
  526. watchdog('tripal_core', $message);
  527. drupal_set_message($message,'error');
  528. continue;
  529. }
  530. $table = $def['table'];
  531. $columns = $def['columns'];
  532. // iterate through the columns of the foreign key relationship
  533. foreach($columns as $left => $right){
  534. // does the left column in the relationship match our field?
  535. if(strcmp($field,$left)==0){
  536. // the column name of the foreign key matches the field we want
  537. // so this is the right relationship. Now we want to select
  538. $select_cols = array($right);
  539. $result = tripal_core_chado_select($table,$select_cols,$values);
  540. $fields = array();
  541. foreach ($result as $obj) {
  542. $fields[] = $obj->$right;
  543. }
  544. return $fields;
  545. }
  546. }
  547. }
  548. }
  549. else {
  550. // TODO: what do we do if we get to this point and we have a fk
  551. // relationship expected but we don't have any definition for one in the
  552. // table schema??
  553. $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.";
  554. watchdog('tripal_core', $message);
  555. drupal_set_message($message,'error');
  556. }
  557. return false;
  558. }
  559. /**
  560. * Generates an object containing the full details of a record(s) in chado.
  561. *
  562. * @todo Add expand_tables arrays
  563. *
  564. * This differs from the objects returned by tripal_core_chado_select in so far as all foreign key
  565. * relationships have been followed meaning you have more complete details. Thus this function
  566. * should be used whenever you need a full variable and tripal_core_chado_select should be used if
  567. * you only case about a few columns.
  568. *
  569. * @param $table
  570. * The name of the base table to generate a variable for
  571. * @param $values
  572. * A select values array that selects the records you want from the base table
  573. * (this has the same form as tripal_core_chado_select)
  574. * @return
  575. * Either an object (if only one record was selected from the base table)
  576. * or an array of objects (if more than one record was selected from the base table).
  577. *
  578. * Example Usage:
  579. * @code
  580. $values = array(
  581. 'name' => 'Medtr4g030710'
  582. );
  583. $features = tripal_core_generate_chado_var('feature', $values);
  584. * @endcode
  585. * This will return an object if there is only one feature with the name Medtr4g030710 or it will
  586. * return an array of feature objects if more than one feature has that name.
  587. *
  588. * Note to Module Designers: Fields can be excluded by default from these objects by implementing
  589. * one of the following hooks:
  590. * - hook_exclude_field_from_tablename_by_default (where tablename is the name of the table):
  591. * This hook allows you to add fields to be excluded on a per table basis. Simply implement
  592. * this hook to return an array of fields to be excluded. For example:
  593. * @code
  594. mymodule_exclude_field_from_feature_by_default() {
  595. return array('residues' => TRUE);
  596. }
  597. * @endcode
  598. * will ensure that feature.residues is ecluded from a feature object by default.
  599. * - hook_exclude_type_by_default:
  600. * This hook allows you to exclude fields from all tables that are of a given postgresql field
  601. * type. Simply implement this hook to return an array of postgresql types mapped to criteria.
  602. * Then all fields of that type where the criteria supplied returns TRUE will be excluded from
  603. * any table. Tokens available in criteria are <field_value> and <field_name>. For example:
  604. * @code
  605. mymodule_exclude_type_by_default() {
  606. return array('text' => 'length(<field_value>) > 50');
  607. }
  608. * @endcode
  609. * will exclude all text fields with a length > 50. Thus if $feature.residues is longer than 50 * it will be excluded, otherwise it will be added.
  610. *
  611. * @ingroup tripal_api
  612. */
  613. function tripal_core_generate_chado_var($table, $values) {
  614. // get description for the current table----------------------------------------------------------
  615. $table_desc = module_invoke_all('chado_'.$table.'_schema');
  616. $table_primary_key = $table_desc['primary key'][0];
  617. $table_columns = array_keys($table_desc['fields']);
  618. // Expandable fields without value needed for criteria--------------------------------------------
  619. $all->expandable_fields = array();
  620. if ($table_desc['referring_tables']) {
  621. $all->expandable_tables = $table_desc['referring_tables'];
  622. } else {
  623. $all->expandable_tables = array();
  624. }
  625. $all->expandable_nodes = array();
  626. // Get fields to be removed by name.................................
  627. $fields_to_remove = module_invoke_all('exclude_field_from_'.$table.'_by_default');
  628. foreach ($fields_to_remove as $field_name => $criteria) {
  629. //replace <field_name> with the current field name &
  630. $criteria = preg_replace('/<field_name>/', $field_name, $criteria);
  631. // if field_value needed we can't deal with this field yet
  632. if (preg_match('/<field_value>/', $criteria)) { break; }
  633. //if criteria then remove from query
  634. $success = drupal_eval('<?php return '.$criteria.'; ?>');
  635. watchdog('tripal_core',
  636. 'Evaluating criteria (%criteria) for field %field in tripal_core_generate_chado_var for %table evaluated to %success',
  637. array('%table' => $table, '%criteria'=>$criteria, '%field' => $field_name, '%success'=>$success),
  638. WATCHDOG_NOTICE
  639. );
  640. if ($success) {
  641. unset($table_columns[array_search($field_name, $table_columns)]);
  642. unset($fields_to_remove[$field_name]);
  643. $all->expandable_fields[] = $table . '.' . $field_name;
  644. }
  645. }
  646. //Get fields to be removed by type................................
  647. $types_to_remove = module_invoke_all('exclude_type_by_default');
  648. $field_types = array();
  649. foreach ($table_desc['fields'] as $field_name => $field_array) {
  650. $field_types[$field_array['type']][] = $field_name;
  651. }
  652. foreach ($types_to_remove as $field_type => $criteria) {
  653. // if there are fields of that type to remove
  654. if (is_array($field_types[$field_type])) {
  655. //replace <field_name> with the current field name &
  656. $criteria = preg_replace('/<field_name>/', $field_name, $criteria);
  657. foreach ($field_types[$field_type] as $field_name) {
  658. // if field_value needed we can't deal with this field yet
  659. if (preg_match('/<field_value>/', $criteria)) {
  660. $fields_to_remove[$field_name] = $criteria;
  661. continue;
  662. }
  663. // if field_value needed we can't deal with this field yet
  664. if (preg_match('/<field_value>/', $criteria)) { break; }
  665. //if criteria then remove from query
  666. $success = drupal_eval('<?php return '.$criteria.'; ?>');
  667. watchdog('tripal_core',
  668. 'Evaluating criteria (%criteria) for field %field of $type in tripal_core_generate_chado_var for %table evaluated to %success',
  669. array('%table'=>$table, '%criteria'=>$criteria, '%field'=>$field_name, '%type'=>$field_type, '%success'=>$success),
  670. WATCHDOG_NOTICE
  671. );
  672. if ($success) {
  673. unset($table_columns[array_search($field_name, $table_columns)]);
  674. $all->expandable_fields[] = $table . '.' . $field_name;
  675. }
  676. } //end of foreach field of that type
  677. }
  678. } //end of foreach type to be removed
  679. // get the values for the record in the current table---------------------------------------------
  680. $results = tripal_core_chado_select($table, $table_columns, $values);
  681. foreach ($results as $key => $object) {
  682. // Add empty expandable_x arrays
  683. $object->expandable_fields = $all->expandable_fields;
  684. $object->expandable_tables = $all->expandable_tables;
  685. $object->expandable_nodes = $all->expandable_nodes;
  686. // add curent table
  687. $object->tablename = $table;
  688. // check if the current table maps to a node type-----------------------------------------------
  689. // if this table is connected to a node there will be a chado_tablename table in drupal
  690. if (db_table_exists('chado_'.$table)) {
  691. // that has a foreign key to this one ($table_desc['primary key'][0]
  692. // and to the node table (nid)
  693. $sql = "SELECT %s, nid FROM chado_%s WHERE %s=%d";
  694. $mapping = db_fetch_object(db_query(
  695. $sql,
  696. $table_primary_key,
  697. $table,
  698. $table_primary_key,
  699. $object->{$table_primary_key}
  700. ));
  701. if ($mapping->{$table_primary_key}) {
  702. $object->nid = $mapping->nid;
  703. $object->expandable_nodes[] = $table;
  704. }
  705. }
  706. // remove any fields where criteria need to be evalulated---------------------------------------
  707. foreach ($fields_to_remove as $field_name => $criteria) {
  708. if (!isset($object->{$field_name})) { break; }
  709. $criteria = preg_replace('/<field_value>/', $object->{$field_name}, $criteria);
  710. //if criteria then remove from query
  711. $success = drupal_eval('<?php return '.$criteria.'; ?>');
  712. watchdog('tripal_core',
  713. 'Evaluating criteria (%criteria) for field %field in tripal_core_generate_chado_var for %table evaluated to %success',
  714. array('%table' => $table, '%criteria'=>$criteria, '%field' => $field_name, '%success'=>$success),
  715. WATCHDOG_NOTICE
  716. );
  717. if ($success) {
  718. unset($object->{$field_name});
  719. $object->expandable_fields[] = $table . '.' . $field_name;
  720. }
  721. }
  722. // recursively follow foreign key relationships nesting objects as we go------------------------
  723. foreach ($table_desc['foreign keys'] as $foreign_key_array) {
  724. $foreign_table = $foreign_key_array['table'];
  725. foreach ($foreign_key_array['columns'] as $foreign_key => $primary_key) {
  726. // Note: Foreign key is the field in the current table whereas primary_key is the field in
  727. // the table referenced by the foreign key
  728. //Dont do anything if the foreign key is empty
  729. if (empty($object->{$foreign_key})) {
  730. break;
  731. }
  732. // get the record from the foreign table
  733. $foreign_values = array($primary_key => $object->{$foreign_key});
  734. $foreign_object = tripal_core_generate_chado_var($foreign_table, $foreign_values);
  735. // add the foreign record to the current object in a nested manner
  736. $object->{$foreign_key} = $foreign_object;
  737. // Flatten expandable_x arrays so only in the bottom object
  738. if (is_array($object->{$foreign_key}->expandable_fields)) {
  739. $object->expandable_fields = array_merge(
  740. $object->expandable_fields,
  741. $object->{$foreign_key}->expandable_fields
  742. );
  743. unset($object->{$foreign_key}->expandable_fields);
  744. }
  745. if (is_array($object->{$foreign_key}->expandable_tables)) {
  746. $object->expandable_tables = array_merge(
  747. $object->expandable_tables,
  748. $object->{$foreign_key}->expandable_tables
  749. );
  750. unset($object->{$foreign_key}->expandable_tables);
  751. }
  752. if (is_array($object->{$foreign_key}->expandable_nodes)) {
  753. $object->expandable_nodes = array_merge(
  754. $object->expandable_nodes,
  755. $object->{$foreign_key}->expandable_nodes
  756. );
  757. unset($object->{$foreign_key}->expandable_nodes);
  758. }
  759. }
  760. }
  761. $results[$key] = $object;
  762. }
  763. // check only one result returned
  764. if (sizeof($results) == 1) {
  765. // add results to object
  766. return $results[0];
  767. } elseif (!empty($results)) {
  768. return $results;
  769. } else {
  770. // no results returned
  771. watchdog('tripal_core',
  772. 'tripal_core_generate_chado_var for %table: No record matches criteria values:%values',
  773. array('%table'=>$table, '%values'=>print_r($values,TRUE)),
  774. WATCHDOG_ERROR
  775. );
  776. }
  777. }
  778. /**
  779. * Retrieves fields/tables/nodes that were excluded by default from a variable and adds them
  780. *
  781. * This function exists to allow tripal_core_generate_chado_var() to excldue some
  782. * fields/tables/nodes from the default form of a variable without making it extremely difficult for
  783. * the tripal admin to get at these variables if he/she wants them.
  784. *
  785. * @param $object
  786. * This must be an object generated using tripal_core_generate_chado_var()
  787. * @param $type
  788. * Must be one of 'field', 'table', 'node'. Indicates what is being expanded.
  789. * @param $to_expand
  790. * The name of the field/table/node to be expanded
  791. *
  792. * @return
  793. * A chado object supplemented with the field/table/node requested to be expanded
  794. *
  795. * Example Usage:
  796. * @code
  797. // Get a chado object to be expanded
  798. $values = array(
  799. 'name' => 'Medtr4g030710'
  800. );
  801. $features = tripal_core_generate_chado_var('feature', $values);
  802. // Expand the organism node
  803. $feature = tripal_core_expand_chado_vars($feature, 'node', 'organism');
  804. // Expand the feature.residues field
  805. $feature = tripal_core_expand_chado_vars($feature, 'field', 'feature.residues');
  806. // Expand the feature properties (featureprop table)
  807. $feature = tripal_core_expand_chado_vars($feature, 'table', 'featureprop');
  808. * @endcode
  809. *
  810. * @ingroup tripal_api
  811. */
  812. function tripal_core_expand_chado_vars ($object, $type, $to_expand) {
  813. $base_table = $object->tablename;
  814. switch ($type) {
  815. case "field": //--------------------------------------------------------------------------------
  816. if (preg_match('/(\w+)\.(\w+)/', $to_expand, $matches)) {
  817. $tablename = $matches[1];
  818. $fieldname = $matches[2];
  819. $table_desc = module_invoke_all('chado_'.$tablename.'_schema');
  820. $values = array();
  821. foreach($table_desc['primary key'] as $key) {
  822. $values[$key] = $object->{$key};
  823. }
  824. if ($base_table == $tablename) {
  825. //get the field
  826. $results = tripal_core_chado_select(
  827. $tablename,
  828. array($fieldname),
  829. $values
  830. );
  831. $object->{$fieldname} = $results[0]->{$fieldname};
  832. $object->expanded = $to_expand;
  833. } else {
  834. //We need to recurse -the field is in a nested object
  835. foreach ((array) $object as $field_name => $field_value) {
  836. if (is_object($field_value)) {
  837. $object->{$field_name} = tripal_core_expand_chado_vars(
  838. $field_value,
  839. 'field',
  840. $to_expand
  841. );
  842. }
  843. } //end of for each field in the current object
  844. }
  845. } else {
  846. watchdog(
  847. 'tripal_core',
  848. 'tripal_core_expand_chado_vars: Field (%field) not in the right format. It should be <tablename>.<fieldname>',
  849. WATCHDOG_ERROR
  850. );
  851. }
  852. break;
  853. case "table": //--------------------------------------------------------------------------------
  854. $foreign_table = $to_expand;
  855. $foreign_table_desc = module_invoke_all('chado_'.$foreign_table.'_schema');
  856. // If it's connected to the base table
  857. if ($foreign_table_desc['foreign keys'][$base_table]) {
  858. foreach ($foreign_table_desc['foreign keys'][$base_table]['columns'] as $left => $right) {
  859. if (!$object->{$right}) { break; }
  860. $foreign_object = tripal_core_generate_chado_var(
  861. $foreign_table,
  862. array($left => $object->{$right})
  863. );
  864. if ($foreign_object) {
  865. $object->{$foreign_table} = $foreign_object;
  866. $object->expanded = $to_expand;
  867. }
  868. }
  869. } else {
  870. //We need to recurse -the table has a relationship to one of the nested objects
  871. foreach ((array) $object as $field_name => $field_value) {
  872. if (is_object($field_value)) {
  873. $object->{$field_name} = tripal_core_expand_chado_vars(
  874. $field_value,
  875. 'table',
  876. $foreign_table
  877. );
  878. }
  879. }
  880. }
  881. break;
  882. case "node": //---------------------------------------------------------------------------------
  883. //if the node to be expanded is for our base table, then just expand it
  884. if ($object->tablename == $to_expand) {
  885. $node = node_load($object->nid);
  886. if ($node) {
  887. $object->expanded = $to_expand;
  888. $node->expandable_fields = $object->expandable_fields;
  889. unset($object->expandable_fields);
  890. $node->expandable_tables = $object->expandable_tables;
  891. unset($object->expandable_tables);
  892. $node->expandable_nodes = $object->expandable_nodes;
  893. unset($object->expandable_nodes);
  894. $node->{$base_table} = $object;
  895. $object = $node;
  896. } else {
  897. watchdog(
  898. 'tripal_core',
  899. 'tripal_core_expand_chado_vars: No node matches the nid (%nid) supplied.',
  900. array('%nid'=>$object->nid),
  901. WATCHDOG_ERROR
  902. );
  903. } //end of if node
  904. } else {
  905. //We need to recurse -the node to expand is one of the nested objects
  906. foreach ((array) $object as $field_name => $field_value) {
  907. if (is_object($field_value)) {
  908. $object->{$field_name} = tripal_core_expand_chado_vars(
  909. $field_value,
  910. 'node',
  911. $to_expand
  912. );
  913. }
  914. } //end of for each field in the current object
  915. }
  916. break;
  917. default:
  918. watchdog('tripal_core',
  919. 'tripal_core_expand_chado_vars: Unrecognized type (%type). Should be one of "field", "table", "node".',
  920. array('%type'=>$type),
  921. WATCHDOG_ERROR
  922. );
  923. return FALSE;
  924. }
  925. //move extended array downwards-------------------------------------------------------------------
  926. if (!$object->expanded) {
  927. //if there's no extended field then go hunting for it
  928. foreach ( (array)$object as $field_name => $field_value) {
  929. if (is_object($field_value)) {
  930. if (isset($field_value->expanded)) {
  931. $object->expanded = $field_value->expanded;
  932. unset($field_value->expanded);
  933. }
  934. }
  935. }
  936. }
  937. //try again becasue now we might have moved it down
  938. if ($object->expanded) {
  939. $expandable_name = 'expandable_'.$type.'s';
  940. if ($object->{$expandable_name}) {
  941. $key_to_remove = array_search($object->expanded, $object->{$expandable_name});
  942. unset($object->{$expandable_name}[$key_to_remove]);
  943. unset($object->expanded);
  944. } else {
  945. // if there is an expandable array then we've reached the base object
  946. // if we get here and don't have anything expanded then something went wrong
  947. watchdog(
  948. 'tripal_core',
  949. 'tripal_core_expand_chado_vars: Unable to expand the %type %to_expand',
  950. array('%type'=>$type, '%to_expand'=>$to_expand),
  951. WATCHDOG_ERROR
  952. );
  953. } //end of it we've reached the base object
  954. }
  955. return $object;
  956. }
  957. /**
  958. * Implements hook_exclude_type_by_default()
  959. *
  960. * This hooks allows fields of a specified type that match a specified criteria to be excluded by
  961. * default from any table when tripal_core_generate_chado_var() is called. Keep in mind that if
  962. * fields are excluded by default they can always be expanded at a later date using
  963. * tripal_core_expand_chado_vars().
  964. *
  965. * Criteria are php strings that evaluate to either TRUE or FALSE. These strings are evaluated using
  966. * drupal_eval() which suppresses syntax errors and throws watchdog entries of type php. There are
  967. * also watchdog entries of type tripal_core stating the exact criteria evaluated. Criteria can
  968. * contain the following tokens:
  969. * - <field_name>
  970. * Replaced by the name of the field to be excluded
  971. * - <field_value>
  972. * Replaced by the value of the field in the current record
  973. * Also keep in mind that if your criteria doesn't contain the <field_value> token then it will be
  974. * evaluated before the query is executed and if the field is excluded it won't be included in the
  975. * query.
  976. *
  977. * @return
  978. * An array of type => criteria where the type is excluded if the criteria evaluates to TRUE
  979. *
  980. * @ingroupt tripal_api
  981. */
  982. function tripal_core_exclude_type_by_default() {
  983. return array('text' => "strlen('<field_value>') > 100");
  984. }
  985. /**
  986. * Implements hook_exclude_field_from_<tablename>_by_default()
  987. *
  988. * This hooks allows fields from a specified table that match a specified criteria to be excluded by
  989. * default from any table when tripal_core_generate_chado_var() is called. Keep in mind that if
  990. * fields are excluded by default they can always be expanded at a later date using
  991. * tripal_core_expand_chado_vars().
  992. *
  993. * Criteria are php strings that evaluate to either TRUE or FALSE. These strings are evaluated using
  994. * drupal_eval() which suppresses syntax errors and throws watchdog entries of type php. There are
  995. * also watchdog entries of type tripal_core stating the exact criteria evaluated. Criteria can
  996. * contain the following tokens:
  997. * - <field_name>
  998. * Replaced by the name of the field to be excluded
  999. * - <field_value>
  1000. * Replaced by the value of the field in the current record
  1001. * Also keep in mind that if your criteria doesn't contain the <field_value> token then it will be
  1002. * evaluated before the query is executed and if the field is excluded it won't be included in the
  1003. * query.
  1004. *
  1005. * @return
  1006. * An array of type => criteria where the type is excluded if the criteria evaluates to TRUE
  1007. *
  1008. * @ingroupt tripal_api
  1009. */
  1010. function tripal_core_exclude_field_from_feature_by_default() {
  1011. return array();
  1012. }