tripal_core.api.inc 35 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882
  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.
  353. * @param $has_record
  354. * Set this argument to 'true' to have this function return a numeric
  355. * value for the number of recrods rather than the array of records. this
  356. * can be useful in 'if' statements to check the presence of particula records.
  357. *
  358. * @return
  359. * A database query result resource, FALSE if the query was not executed
  360. * correctly, or the number of records in the dataset if $has_record is set.
  361. *
  362. * Example usage:
  363. * @code
  364. * $columns = array('feature_id','name');
  365. * $values = array(
  366. * 'organism_id' => array(
  367. * 'genus' => 'Citrus',
  368. * 'species' => 'sinensis',
  369. * ),
  370. * 'uniquename' => 'orange1.1g000034m.g',
  371. * 'type_id' => array (
  372. * 'cv_id' => array (
  373. * 'name' => 'sequence',
  374. * ),
  375. * 'name' => 'gene',
  376. * 'is_obsolete' => 0
  377. * ),
  378. * );
  379. * $result = tripal_core_chado_select('feature',$columns,$values);
  380. * @endcode
  381. * The above code selects a record from the feature table using the three fields
  382. * that uniquely identify a feature. The $columns array simply lists the columns
  383. * to select. The $values array is nested such that the organism is identified by
  384. * way of the organism_id foreign key constraint by specifying the genus and
  385. * species. The cvterm is also specified using its foreign key and the cv_id
  386. * for the cvterm is nested as well.
  387. *
  388. * @ingroup tripal_api
  389. */
  390. function tripal_core_chado_select($table,$columns,$values,$has_record = 0){
  391. if (!is_array($columns)){
  392. watchdog('tripal_feature', 'the $columns argument for tripal_core_chado_select must be an array.');
  393. return false;
  394. }
  395. if (!is_array($values)){
  396. watchdog('tripal_feature', 'the $values argument for tripal_core_chado_select must be an array.');
  397. return false;
  398. }
  399. // get the table description
  400. $table_desc = module_invoke_all('chado_'.$table.'_schema');
  401. $select = '';
  402. $from = '';
  403. $where = '';
  404. $args = array();
  405. foreach($values as $field => $value){
  406. $select[] = $field;
  407. if(is_array($value)){
  408. // select the value from the foreign key relationship for this value
  409. $results = tripal_core_chado_get_foreign_key($table_desc,$field,$value);
  410. if (sizeof($results) < 1) {
  411. // foreign key records are required
  412. // thus if none matched then return false and alert the admin through watchdog
  413. watchdog('tripal_core',
  414. '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',
  415. array('!table' => $table,
  416. '!columns' => '<pre>' . print_r($columns, TRUE) . '</pre>',
  417. '!values' => '<pre>' . print_r($values, TRUE) . '</pre>',
  418. '!field' => $field,
  419. ),
  420. WATCHDOG_WARNING);
  421. return false;
  422. } elseif (sizeof($results) == 1) {
  423. $where[$field] = $results[0];
  424. } else {
  425. $where[$field] = $results;
  426. }
  427. }
  428. else {
  429. $where[$field] = $value;
  430. }
  431. }
  432. // now build the SQL select statement
  433. if (empty($where)) {
  434. // sometimes want to select everything
  435. $sql = "SELECT " . implode(',',$columns) . " ";
  436. $sql .= "FROM {$table} ";
  437. } else {
  438. $sql = "SELECT " . implode(',',$columns) . " ";
  439. $sql .= "FROM {$table} ";
  440. $sql .= "WHERE ";
  441. foreach($where as $field => $value){
  442. if (is_array($value)) {
  443. $sql .= "$field IN (".db_placeholders($value,'varchar').") AND ";
  444. foreach ($value as $v) { $args[] = $v; }
  445. } else {
  446. $sql .= "$field = '%s' AND ";
  447. $args[] = $value;
  448. }
  449. }
  450. $sql = substr($sql,0,-4); // get rid of the trailing 'AND'
  451. }
  452. $previous_db = tripal_db_set_active('chado'); // use chado database
  453. $resource = db_query($sql,$args);
  454. tripal_db_set_active($previous_db); // now use drupal database
  455. $results = array();
  456. while ($r = db_fetch_object($resource)) {
  457. $results[] = $r;
  458. }
  459. if(!$has_record){
  460. return $results;
  461. } else{
  462. return count($results);
  463. }
  464. }
  465. /**
  466. * Gets the value of a foreign key relationship
  467. *
  468. * This function is used by tripal_core_chado_select, tripal_core_chado_insert,
  469. * and tripal_core_chado_update to iterate through the associate array of
  470. * values that gets passed to each of those routines. The values array
  471. * is nested where foreign key contraints are used to specify a value that. See
  472. * documentation for any of those functions for further information.
  473. *
  474. * @param $table_desc
  475. * A table description for the table with the foreign key relationship to be identified generated by
  476. * hook_chado_<table name>_schema()
  477. * @param $field
  478. * The field in the table that is the foreign key.
  479. * @param $values
  480. * An associative array containing the values
  481. *
  482. * @return
  483. * A string containg the results of the foreign key lookup, or FALSE if failed.
  484. *
  485. * Example usage:
  486. * @code
  487. *
  488. * $values = array(
  489. * 'genus' => 'Citrus',
  490. * 'species' => 'sinensis',
  491. * );
  492. * $value = tripal_core_chado_get_foreign_key('feature','organism_id',$values);
  493. *
  494. * @endcode
  495. * The above code selects a record from the feature table using the three fields
  496. * that uniquely identify a feature. The $columns array simply lists the columns
  497. * to select. The $values array is nested such that the organism is identified by
  498. * way of the organism_id foreign key constraint by specifying the genus and
  499. * species. The cvterm is also specified using its foreign key and the cv_id
  500. * for the cvterm is nested as well.
  501. *
  502. * @ingroup tripal_api
  503. */
  504. function tripal_core_chado_get_foreign_key($table_desc,$field,$values){
  505. // get the list of foreign keys for this table description and
  506. // iterate through those until we find the one we're looking for
  507. $fkeys = $table_desc['foreign keys'];
  508. if($fkeys){
  509. foreach($fkeys as $name => $def){
  510. if (is_array($def['table'])) {
  511. //foreign key was described 2X
  512. $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")).".";
  513. watchdog('tripal_core', $message);
  514. drupal_set_message($message,'error');
  515. continue;
  516. }
  517. $table = $def['table'];
  518. $columns = $def['columns'];
  519. // iterate through the columns of the foreign key relationship
  520. foreach($columns as $left => $right){
  521. // does the left column in the relationship match our field?
  522. if(strcmp($field,$left)==0){
  523. // the column name of the foreign key matches the field we want
  524. // so this is the right relationship. Now we want to select
  525. $select_cols = array($right);
  526. $result = tripal_core_chado_select($table,$select_cols,$values);
  527. $fields = array();
  528. foreach ($result as $obj) {
  529. $fields[] = $obj->$right;
  530. }
  531. return $fields;
  532. }
  533. }
  534. }
  535. }
  536. else {
  537. // TODO: what do we do if we get to this point and we have a fk
  538. // relationship expected but we don't have any definition for one in the
  539. // table schema??
  540. $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.";
  541. watchdog('tripal_core', $message);
  542. drupal_set_message($message,'error');
  543. }
  544. return false;
  545. }
  546. /**
  547. * Generates an object containing the full details of a record(s) in chado.
  548. *
  549. * @todo Add expand_tables arrays
  550. *
  551. * This differs from the objects returned by tripal_core_chado_select in so far as all foreign key
  552. * relationships have been followed meaning you have more complete details. Thus this function
  553. * should be used whenever you need a full variable and tripal_core_chado_select should be used if
  554. * you only case about a few columns.
  555. *
  556. * @param $table
  557. * The name of the base table to generate a variable for
  558. * @param $values
  559. * A select values array that selects the records you want from the base table
  560. * (this has the same form as tripal_core_chado_select)
  561. * @return
  562. * Either an object (if only one record was selected from the base table)
  563. * or an array of objects (if more than one record was selected from the base table).
  564. *
  565. * Example Usage:
  566. * @code
  567. $values = array(
  568. 'name' => 'Medtr4g030710'
  569. );
  570. $features = tripal_core_generate_chado_var('feature', $values);
  571. * @endcode
  572. * This will return an object if there is only one feature with the name Medtr4g030710 or it will
  573. * return an array of feature objects if more than one feature has that name.
  574. *
  575. * Note to Module Designers: Fields can be excluded by default from these objects by implementing
  576. * one of the following hooks:
  577. * - hook_exclude_field_from_tablename_by_default (where tablename is the name of the table):
  578. * This hook allows you to add fields to be excluded on a per table basis. Simply implement
  579. * this hook to return an array of fields to be excluded. For example:
  580. * @code
  581. mymodule_exclude_field_from_feature_by_default() {
  582. return array('residues' => TRUE);
  583. }
  584. * @endcode
  585. * will ensure that feature.residues is ecluded from a feature object by default.
  586. * - hook_exclude_type_by_default:
  587. * This hook allows you to exclude fields from all tables that are of a given postgresql field
  588. * type. Simply implement this hook to return an array of postgresql types mapped to criteria.
  589. * Then all fields of that type where the criteria supplied returns TRUE will be excluded from
  590. * any table. Tokens available in criteria are <field_value> and <field_name>. For example:
  591. * @code
  592. mymodule_exclude_type_by_default() {
  593. return array('text' => 'length(<field_value>) > 50');
  594. }
  595. * @endcode
  596. * 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.
  597. *
  598. * @ingroup tripal_api
  599. */
  600. function tripal_core_generate_chado_var($table, $values) {
  601. // get description for the current table----------------------------------------------------------
  602. $table_desc = module_invoke_all('chado_'.$table.'_schema');
  603. $table_primary_key = $table_desc['primary key'][0];
  604. $table_columns = array_keys($table_desc['fields']);
  605. // Expandable fields without value needed for criteria--------------------------------------------
  606. $all->expandable_fields = array();
  607. if ($table_desc['referring_tables']) {
  608. $all->expandable_tables = $table_desc['referring_tables'];
  609. } else {
  610. $all->expandable_tables = array();
  611. }
  612. $all->expandable_nodes = array();
  613. // Get fields to be removed by name.................................
  614. $fields_to_remove = module_invoke_all('exclude_field_from_'.$table.'_by_default');
  615. foreach ($fields_to_remove as $field_name => $criteria) {
  616. //replace <field_name> with the current field name &
  617. $criteria = preg_replace('/<field_name>/', $field_name, $criteria);
  618. // if field_value needed we can't deal with this field yet
  619. if (preg_match('/<field_value>/', $criteria)) { break; }
  620. //if criteria then remove from query
  621. $success = drupal_eval('<?php return '.$criteria.'; ?>');
  622. watchdog('tripal_core',
  623. 'Evaluating criteria (%criteria) for field %field in tripal_core_generate_chado_var for %table evaluated to %success',
  624. array('%table' => $table, '%criteria'=>$criteria, '%field' => $field_name, '%success'=>$success),
  625. WATCHDOG_NOTICE
  626. );
  627. if ($success) {
  628. unset($table_columns[array_search($field_name, $table_columns)]);
  629. unset($fields_to_remove[$field_name]);
  630. $all->expandable_fields[] = $table . '.' . $field_name;
  631. }
  632. }
  633. //Get fields to be removed by type................................
  634. $types_to_remove = module_invoke_all('exclude_type_by_default');
  635. $field_types = array();
  636. foreach ($table_desc['fields'] as $field_name => $field_array) {
  637. $field_types[$field_array['type']][] = $field_name;
  638. }
  639. foreach ($types_to_remove as $field_type => $criteria) {
  640. // if there are fields of that type to remove
  641. if (is_array($field_types[$field_type])) {
  642. //replace <field_name> with the current field name &
  643. $criteria = preg_replace('/<field_name>/', $field_name, $criteria);
  644. foreach ($field_types[$field_type] as $field_name) {
  645. // if field_value needed we can't deal with this field yet
  646. if (preg_match('/<field_value>/', $criteria)) {
  647. $fields_to_remove[$field_name] = $criteria;
  648. continue;
  649. }
  650. // if field_value needed we can't deal with this field yet
  651. if (preg_match('/<field_value>/', $criteria)) { break; }
  652. //if criteria then remove from query
  653. $success = drupal_eval('<?php return '.$criteria.'; ?>');
  654. watchdog('tripal_core',
  655. 'Evaluating criteria (%criteria) for field %field of $type in tripal_core_generate_chado_var for %table evaluated to %success',
  656. array('%table'=>$table, '%criteria'=>$criteria, '%field'=>$field_name, '%type'=>$field_type, '%success'=>$success),
  657. WATCHDOG_NOTICE
  658. );
  659. if ($success) {
  660. unset($table_columns[array_search($field_name, $table_columns)]);
  661. $all->expandable_fields[] = $table . '.' . $field_name;
  662. }
  663. } //end of foreach field of that type
  664. }
  665. } //end of foreach type to be removed
  666. // get the values for the record in the current table---------------------------------------------
  667. $results = tripal_core_chado_select($table, $table_columns, $values);
  668. foreach ($results as $key => $object) {
  669. // Add empty expandable_x arrays
  670. $object->expandable_fields = $all->expandable_fields;
  671. $object->expandable_tables = $all->expandable_tables;
  672. $object->expandable_nodes = $all->expandable_nodes;
  673. // check if the current table maps to a node type-----------------------------------------------
  674. // if this table is connected to a node there will be a chado_tablename table in drupal
  675. if (db_table_exists('chado_'.$table)) {
  676. // that has a foreign key to this one ($table_desc['primary key'][0]
  677. // and to the node table (nid)
  678. $sql = "SELECT %s, nid FROM chado_%s WHERE %s=%d";
  679. $mapping = db_fetch_object(db_query(
  680. $sql,
  681. $table_primary_key,
  682. $table,
  683. $table_primary_key,
  684. $object->{$table_primary_key}
  685. ));
  686. if ($mapping->{$table_primary_key}) {
  687. $object->nid = $mapping->nid;
  688. $object->expandable_nodes[] = $table;
  689. }
  690. }
  691. // remove any fields where criteria need to be evalulated---------------------------------------
  692. foreach ($fields_to_remove as $field_name => $criteria) {
  693. if (!isset($object->{$field_name})) { break; }
  694. $criteria = preg_replace('/<field_value>/', $object->{$field_name}, $criteria);
  695. //if criteria then remove from query
  696. $success = drupal_eval('<?php return '.$criteria.'; ?>');
  697. watchdog('tripal_core',
  698. 'Evaluating criteria (%criteria) for field %field in tripal_core_generate_chado_var for %table evaluated to %success',
  699. array('%table' => $table, '%criteria'=>$criteria, '%field' => $field_name, '%success'=>$success),
  700. WATCHDOG_NOTICE
  701. );
  702. if ($success) {
  703. unset($object->{$field_name});
  704. $object->expandable_fields[] = $table . '.' . $field_name;
  705. }
  706. }
  707. // recursively follow foreign key relationships nesting objects as we go------------------------
  708. foreach ($table_desc['foreign keys'] as $foreign_key_array) {
  709. $foreign_table = $foreign_key_array['table'];
  710. foreach ($foreign_key_array['columns'] as $foreign_key => $primary_key) {
  711. // Note: Foreign key is the field in the current table whereas primary_key is the field in
  712. // the table referenced by the foreign key
  713. //Dont do anything if the foreign key is empty
  714. if (empty($object->{$foreign_key})) {
  715. break;
  716. }
  717. // get the record from the foreign table
  718. $foreign_values = array($primary_key => $object->{$foreign_key});
  719. $foreign_object = tripal_core_generate_chado_var($foreign_table, $foreign_values);
  720. // add the foreign record to the current object in a nested manner
  721. $object->{$foreign_key} = $foreign_object;
  722. $object->{$foreign_key}->tablename = $foreign_table;
  723. // Flatten expandable_x arrays so only in the bottom object
  724. if (is_array($object->{$foreign_key}->expandable_fields)) {
  725. $object->expandable_fields = array_merge(
  726. $object->expandable_fields,
  727. $object->{$foreign_key}->expandable_fields
  728. );
  729. unset($object->{$foreign_key}->expandable_fields);
  730. }
  731. if (is_array($object->{$foreign_key}->expandable_tables)) {
  732. $object->expandable_tables = array_merge(
  733. $object->expandable_tables,
  734. $object->{$foreign_key}->expandable_tables
  735. );
  736. unset($object->{$foreign_key}->expandable_tables);
  737. }
  738. if (is_array($object->{$foreign_key}->expandable_nodes)) {
  739. $object->expandable_nodes = array_merge(
  740. $object->expandable_nodes,
  741. $object->{$foreign_key}->expandable_nodes
  742. );
  743. unset($object->{$foreign_key}->expandable_nodes);
  744. }
  745. }
  746. }
  747. $results[$key] = $object;
  748. }
  749. // check only one result returned
  750. if (sizeof($results) == 1) {
  751. // add results to object
  752. return $results[0];
  753. } elseif (!empty($results)) {
  754. return $results;
  755. } else {
  756. // no results returned
  757. watchdog('tripal_core',
  758. 'tripal_core_generate_chado_var for %table: No record matches criteria values:%values',
  759. array('%table'=>$table, '%values'=>print_r($values,TRUE)),
  760. WATCHDOG_ERROR
  761. );
  762. }
  763. }
  764. /**
  765. * Retrieves fields/tables/nodes that were excluded by default from a variable and adds them
  766. *
  767. * This function exists to allow tripal_core_generate_chado_var() to excldue some
  768. * fields/tables/nodes from the default form of a variable without making it extremely difficult for
  769. * the tripal admin to get at these variables if he/she wants them.
  770. *
  771. * @ingroup tripal_api
  772. */
  773. function tripal_core_expand_chado_vars () {
  774. }
  775. /**
  776. * Implements hook_exclude_type_by_default()
  777. *
  778. * This hooks allows fields of a specified type that match a specified criteria to be excluded by
  779. * default from any table when tripal_core_generate_chado_var() is called. Keep in mind that if
  780. * fields are excluded by default they can always be expanded at a later date using
  781. * tripal_core_expand_chado_vars().
  782. *
  783. * Criteria are php strings that evaluate to either TRUE or FALSE. These strings are evaluated using
  784. * drupal_eval() which suppresses syntax errors and throws watchdog entries of type php. There are
  785. * also watchdog entries of type tripal_core stating the exact criteria evaluated. Criteria can
  786. * contain the following tokens:
  787. * - <field_name>
  788. * Replaced by the name of the field to be excluded
  789. * - <field_value>
  790. * Replaced by the value of the field in the current record
  791. * Also keep in mind that if your criteria doesn't contain the <field_value> token then it will be
  792. * evaluated before the query is executed and if the field is excluded it won't be included in the
  793. * query.
  794. *
  795. * @return
  796. * An array of type => criteria where the type is excluded if the criteria evaluates to TRUE
  797. *
  798. * @ingroupt tripal_api
  799. */
  800. function tripal_core_exclude_type_by_default() {
  801. return array('text' => "strlen('<field_value>') > 100");
  802. }
  803. /**
  804. * Implements hook_exclude_field_from_<tablename>_by_default()
  805. *
  806. * This hooks allows fields from a specified table that match a specified criteria to be excluded by
  807. * default from any table when tripal_core_generate_chado_var() is called. Keep in mind that if
  808. * fields are excluded by default they can always be expanded at a later date using
  809. * tripal_core_expand_chado_vars().
  810. *
  811. * Criteria are php strings that evaluate to either TRUE or FALSE. These strings are evaluated using
  812. * drupal_eval() which suppresses syntax errors and throws watchdog entries of type php. There are
  813. * also watchdog entries of type tripal_core stating the exact criteria evaluated. Criteria can
  814. * contain the following tokens:
  815. * - <field_name>
  816. * Replaced by the name of the field to be excluded
  817. * - <field_value>
  818. * Replaced by the value of the field in the current record
  819. * Also keep in mind that if your criteria doesn't contain the <field_value> token then it will be
  820. * evaluated before the query is executed and if the field is excluded it won't be included in the
  821. * query.
  822. *
  823. * @return
  824. * An array of type => criteria where the type is excluded if the criteria evaluates to TRUE
  825. *
  826. * @ingroupt tripal_api
  827. */
  828. function tripal_core_exclude_field_from_feature_by_default() {
  829. return array();
  830. }