tripal_core.api.inc 44 KB

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