tripal_core.api.inc 53 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373
  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. if ($ukeys) {
  153. foreach($ukeys as $name => $fields){
  154. foreach($fields as $index => $field){
  155. // build the arrays for performing a select that will check the contraint
  156. array_push($ukselect_cols,$field);
  157. $ukselect_vals[$field] = $insert_values[$field];
  158. }
  159. // now check the constraint
  160. if(tripal_core_chado_select($table,$ukselect_cols,$ukselect_vals)){
  161. watchdog('tripal_core',"tripal_core_chado_insert: Cannot insert duplicate record into $table table: " . print_r($values,1),array(),'WATCHDOG_ERROR');
  162. return false;
  163. }
  164. }
  165. }
  166. // if trying to insert a field that is the primary key, make sure it also is unique
  167. $pkey = $table_desc['primary key'][0];
  168. if($insert_values[$pkey]){
  169. if(tripal_core_chado_select($table,array($pkey),array($pkey => $insert_values[$pkey]))){
  170. watchdog('tripal_core',"tripal_core_chado_insert: Cannot insert duplicate primary key into $table table: " . print_r($values,1),array(),'WATCHDOG_ERROR');
  171. return false;
  172. }
  173. }
  174. // make sure required fields have a value
  175. $fields = $table_desc['fields'];
  176. foreach($fields as $field => $def){
  177. // a field is considered missing if it cannot be null and there is no default
  178. // value for it or it is of type 'serial'
  179. if($def['not null'] == 1 and !array_key_exists($field,$insert_values) and !isset($def['default']) and strcmp($def['type'],serial)!=0){
  180. watchdog('tripal_core',"tripal_core_chado_insert: Field $table.$field cannot be null: " . print_r($values,1),array(),'WATCHDOG_ERROR');
  181. return false;
  182. }
  183. }
  184. // Now build the insert SQL statement
  185. $ifields = array();
  186. $ivalues = array();
  187. $itypes = array();
  188. foreach ($insert_values as $field => $value){
  189. array_push($ifields,$field);
  190. array_push($ivalues,$value);
  191. if(strcmp($fields[$field]['type'],'serial')==0 or
  192. strcmp($fields[$field]['type'],'int')==0){
  193. array_push($itypes,"%d");
  194. } else {
  195. array_push($itypes,"'%s'");
  196. }
  197. }
  198. $sql = "INSERT INTO {$table} (" . implode(", ",$ifields) . ") VALUES (". implode(", ",$itypes) .")";
  199. // finally perform the insert.
  200. $previous_db = tripal_db_set_active('chado'); // use chado database
  201. $result = db_query($sql,$ivalues);
  202. tripal_db_set_active($previous_db); // now use drupal database
  203. if($result){
  204. // add primary keys to values before return
  205. $primary_key = array();
  206. foreach ($table_desc['primary key'] as $field) {
  207. $value = db_last_insert_id($table, $field);
  208. $values[$field] = $value;
  209. }
  210. return $values;
  211. }
  212. else {
  213. watchdog('tripal_core',"tripal_core_chado_insert: Cannot insert record into $table table: " . print_r($values,1),array(),'WATCHDOG_ERROR');
  214. return false;
  215. }
  216. return false;
  217. }
  218. /**
  219. *
  220. */
  221. function tripal_core_chado_delete($table,$match){
  222. $delete_matches = array(); // contains the values for the where clause
  223. // get the table description
  224. $table_desc = module_invoke_all('chado_'.$table.'_schema');
  225. // get the values needed for matching in the SQL statement
  226. foreach ($match as $field => $value){
  227. if(is_array($value)){
  228. $results = tripal_core_chado_get_foreign_key($table_desc,$field,$value);
  229. if (sizeof($results) > 1) {
  230. watchdog('tripal_core', 'tripal_core_chado_delete: When trying to find record to delete, 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);
  231. } elseif (sizeof($results) < 1) {
  232. watchdog('tripal_core', 'tripal_core_chado_delete: When trying to find record to delete, no record matches criteria supplied for !foreign_key foreign key constraint (!criteria)', array('!foreign_key' => $field, '!criteria' => print_r($value,TRUE)), WATCHDOG_ERROR);
  233. } else {
  234. $delete_matches[$field] = $results[0];
  235. }
  236. }
  237. else {
  238. $delete_matches[$field] = $value;
  239. }
  240. }
  241. // now build the SQL statement
  242. $sql = "DELETE FROM {$table} WHERE ";
  243. $dargs = array();
  244. foreach($delete_matches as $field => $value){
  245. if(strcmp($fields[$field]['type'],'serial')==0 or
  246. strcmp($fields[$field]['type'],'int')==0){
  247. $sql .= " $field = %d AND ";
  248. } else {
  249. $sql .= " $field = '%s' AND ";
  250. }
  251. array_push($dargs,$value);
  252. }
  253. $sql = substr($sql,0,-4); // get rid of the trailing 'AND'
  254. // finally perform the delete. If successful, return the updated record
  255. $previous_db = tripal_db_set_active('chado'); // use chado database
  256. $result = db_query($sql,$dargs);
  257. tripal_db_set_active($previous_db); // now use drupal database
  258. if($result){
  259. return true;
  260. }
  261. else {
  262. watchdog('tripal_core',"Cannot delete record in $table table. Match:" . print_r($match,1) . ". Values: ". print_r($values,1),array(),'WATCHDOG_ERROR');
  263. return false;
  264. }
  265. return false;
  266. }
  267. /**
  268. * Provides a generic routine for updating into any Chado table
  269. *
  270. * Use this function to update a record in any Chado table. The first
  271. * argument specifies the table for inserting, the second is an array
  272. * of values to matched for locating the record for updating, and the third
  273. * argument give the values to update. The arrays are mutli-dimensional such
  274. * that foreign key lookup values can be specified.
  275. *
  276. * @param $table
  277. * The name of the chado table for inserting
  278. * @param $match
  279. * An associative array containing the values for locating a record to update.
  280. * @param $values
  281. * An associative array containing the values for updating.
  282. *
  283. * @return
  284. * On success this function returns TRUE. On failure, it returns FALSE.
  285. *
  286. * Example usage:
  287. * @code
  288. $umatch = array(
  289. 'organism_id' => array(
  290. 'genus' => 'Citrus',
  291. 'species' => 'sinensis',
  292. ),
  293. 'uniquename' => 'orange1.1g000034m.g7',
  294. 'type_id' => array (
  295. 'cv_id' => array (
  296. 'name' => 'sequence',
  297. ),
  298. 'name' => 'gene',
  299. 'is_obsolete' => 0
  300. ),
  301. );
  302. $uvalues = array(
  303. 'name' => 'orange1.1g000034m.g',
  304. 'type_id' => array (
  305. 'cv_id' => array (
  306. 'name' => 'sequence',
  307. ),
  308. 'name' => 'mRNA',
  309. 'is_obsolete' => 0
  310. ),
  311. );
  312. * $result = tripal_core_chado_update('feature',$umatch,$uvalues);
  313. * @endcode
  314. * The above code species that a feature with a given uniquename, organism_id,
  315. * and type_id (the unique constraint for the feature table) will be updated.
  316. * The organism_id is specified as a nested array that uses the organism_id
  317. * foreign key constraint to lookup the specified values to find the exact
  318. * organism_id. The same nested struture is also used for specifying the
  319. * values to update. The function will find the record that matches the
  320. * columns specified and update the record with the avlues in the $uvalues array.
  321. *
  322. * @ingroup tripal_api
  323. */
  324. function tripal_core_chado_update($table,$match,$values){
  325. $update_values = array(); // contains the values to be updated
  326. $update_matches = array(); // contains the values for the where clause
  327. // get the table description
  328. $table_desc = module_invoke_all('chado_'.$table.'_schema');
  329. // get the values needed for matching in the SQL statement
  330. foreach ($match as $field => $value){
  331. if(is_array($value)){
  332. $results = tripal_core_chado_get_foreign_key($table_desc,$field,$value);
  333. if (sizeof($results) > 1) {
  334. 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);
  335. } elseif (sizeof($results) < 1) {
  336. 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);
  337. } else {
  338. $update_matches[$field] = $results[0];
  339. }
  340. }
  341. else {
  342. $update_matches[$field] = $value;
  343. }
  344. }
  345. // get the values used for updating
  346. foreach ($values as $field => $value){
  347. if(is_array($value)){
  348. $results = tripal_core_chado_get_foreign_key($table_desc,$field,$value);
  349. if (sizeof($results) > 1) {
  350. 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);
  351. } elseif (sizeof($results) < 1) {
  352. 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);
  353. } else {
  354. $update_values[$field] = $results[0];
  355. }
  356. }
  357. else {
  358. $update_values[$field] = $value;
  359. }
  360. }
  361. // now build the SQL statement
  362. $sql = "UPDATE {$table} SET ";
  363. $fields = $table_desc['fields'];
  364. $uargs = array();
  365. foreach($update_values as $field => $value){
  366. if(strcmp($fields[$field]['type'],'serial')==0 or
  367. strcmp($fields[$field]['type'],'int')==0){
  368. $sql .= " $field = %d, ";
  369. } else {
  370. $sql .= " $field = '%s', ";
  371. }
  372. array_push($uargs,$value);
  373. }
  374. $sql = substr($sql,0,-2); // get rid of the trailing comma & space
  375. $sql .= " WHERE ";
  376. foreach($update_matches as $field => $value){
  377. if(strcmp($fields[$field]['type'],'serial')==0 or
  378. strcmp($fields[$field]['type'],'int')==0){
  379. $sql .= " $field = %d AND ";
  380. } else {
  381. $sql .= " $field = '%s' AND ";
  382. }
  383. array_push($uargs,$value);
  384. }
  385. $sql = substr($sql,0,-4); // get rid of the trailing 'AND'
  386. // finally perform the update. If successful, return the updated record
  387. $previous_db = tripal_db_set_active('chado'); // use chado database
  388. $result = db_query($sql,$uargs);
  389. tripal_db_set_active($previous_db); // now use drupal database
  390. if($result){
  391. return true;
  392. }
  393. else {
  394. watchdog('tripal_core',"Cannot update record in $table table. Match:" . print_r($match,1) . ". Values: ". print_r($values,1),array(),'WATCHDOG_ERROR');
  395. return false;
  396. }
  397. return false;
  398. }
  399. /**
  400. * Provides a generic routine for selecting data from a Chado table
  401. *
  402. * Use this function to perform a simple select from any Chado table.
  403. *
  404. * @param $table
  405. * The name of the chado table for inserting
  406. * @param $columns
  407. * An array of column names
  408. * @param $values
  409. * An associative array containing the values for filtering the results. In the
  410. * case where multiple values for the same time are to be selected an additional
  411. * entry for the field should appear for each value
  412. * @param $options
  413. * An associative array of additional options where the key is the option
  414. * and the value is the value of that option.
  415. *
  416. * Additional Options Include:
  417. * - has_record
  418. * Set this argument to 'true' to have this function return a numeric
  419. * value for the number of recrods rather than the array of records. this
  420. * can be useful in 'if' statements to check the presence of particula records.
  421. * - return_sql
  422. * Set this to 'true' to have this function return an array where the first
  423. * element is the sql that would have been run and the second is an array of
  424. * arguments.
  425. * - case_insensitive_columns
  426. * An array of columns to do a case insensitive search on.
  427. * - regex_columns
  428. * An array of columns where the value passed in should be treated as a regular expression
  429. * - order_by
  430. * An associative array containing the column names of the table as keys
  431. * and the type of sort (i.e. ASC, DESC) as the values. The results in the
  432. * query will be sorted by the key values in the direction listed by the value
  433. *
  434. * @return
  435. * A database query result resource, FALSE if the query was not executed
  436. * correctly, or the number of records in the dataset if $has_record is set.
  437. *
  438. * Example usage:
  439. * @code
  440. * $columns = array('feature_id','name');
  441. * $values = array(
  442. * 'organism_id' => array(
  443. * 'genus' => 'Citrus',
  444. * 'species' => array('sinensis','clementina'),
  445. * ),
  446. * 'uniquename' => 'orange1.1g000034m.g',
  447. * 'type_id' => array (
  448. * 'cv_id' => array (
  449. * 'name' => 'sequence',
  450. * ),
  451. * 'name' => 'gene',
  452. * 'is_obsolete' => 0
  453. * ),
  454. * );
  455. * $result = tripal_core_chado_select('feature',$columns,$values);
  456. * @endcode
  457. * The above code selects a record from the feature table using the three fields
  458. * that uniquely identify a feature. The $columns array simply lists the columns
  459. * to select. The $values array is nested such that the organism is identified by
  460. * way of the organism_id foreign key constraint by specifying the genus and
  461. * species. The cvterm is also specified using its foreign key and the cv_id
  462. * for the cvterm is nested as well. In the example above, two different species
  463. * are allowed to match
  464. *
  465. * @ingroup tripal_api
  466. */
  467. function tripal_core_chado_select($table,$columns,$values,$options = null){
  468. if (!is_array($options)) { $options = array(); }
  469. if (!$options['case_insensitive_columns']) { $options['case_insensitive_columns'] = array(); }
  470. if (!$options['regex_columns']) { $options['regex_columns'] = array(); }
  471. if (!$options['order_by']) { $options['order_by'] = array(); }
  472. if (!is_array($columns)){
  473. watchdog('tripal_feature', 'the $columns argument for tripal_core_chado_select must be an array.');
  474. return false;
  475. }
  476. if (!is_array($values)){
  477. watchdog('tripal_feature', 'the $values argument for tripal_core_chado_select must be an array.');
  478. return false;
  479. }
  480. // get the table description
  481. $table_desc = module_invoke_all('chado_'.$table.'_schema');
  482. $select = '';
  483. $from = '';
  484. $where = '';
  485. $args = array();
  486. foreach($values as $field => $value){
  487. $select[] = $field;
  488. if(is_array($value)){
  489. // if the user has specified multiple values for matching then this we
  490. // want to catch that and save them in our $where array, otherwise
  491. // we'll descend for a foreign key relationship
  492. if(array_values($value) === $value){
  493. $where[$field] = $value;
  494. } else {
  495. // select the value from the foreign key relationship for this value
  496. $foreign_options = array(
  497. 'regex_columns' => $options['regex_columns'],
  498. 'case_insensitive_columns' => $options['case_insensitive_columns']
  499. );
  500. $results = tripal_core_chado_get_foreign_key($table_desc,$field,$value, $foreign_options);
  501. if (sizeof($results) < 1) {
  502. // foreign key records are required
  503. // thus if none matched then return false and alert the admin through watchdog
  504. watchdog('tripal_core',
  505. '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',
  506. array('!table' => $table,
  507. '!columns' => '<pre>' . print_r($columns, TRUE) . '</pre>',
  508. '!values' => '<pre>' . print_r($values, TRUE) . '</pre>',
  509. '!field' => $field,
  510. ),
  511. WATCHDOG_WARNING);
  512. return false;
  513. } else {
  514. $where[$field] = $results;
  515. }
  516. }
  517. }
  518. else {
  519. //need to catch a 0 and make int if integer field
  520. if ($table_desc['fields'][$field]['type'] == 'int') {
  521. $where[$field][] = (int) $value;
  522. } else {
  523. $where[$field][] = $value;
  524. }
  525. }
  526. }
  527. // now build the SQL select statement
  528. if (empty($where)) {
  529. // sometimes want to select everything
  530. $sql = "SELECT " . implode(',',$columns) . " ";
  531. $sql .= "FROM {$table} ";
  532. } else {
  533. $sql = "SELECT " . implode(',',$columns) . " ";
  534. $sql .= "FROM {$table} ";
  535. $sql .= "WHERE ";
  536. foreach($where as $field => $value){
  537. if (count($value) > 1) {
  538. $sql .= "$field IN (".db_placeholders($value,'varchar').") AND ";
  539. foreach ($value as $v) { $args[] = $v; }
  540. } else {
  541. $operator = '=';
  542. if (in_array($field, $options['regex_columns'])) {
  543. $operator = '~*';
  544. }
  545. if (in_array($field, $options['case_insensitive_columns'])) {
  546. $sql .= "lower($field) $operator lower('%s') AND ";
  547. $args[] = $value[0];
  548. } else {
  549. $sql .= "$field $operator '%s' AND ";
  550. $args[] = $value[0];
  551. }
  552. }
  553. }
  554. $sql = substr($sql,0,-4); // get rid of the trailing 'AND '
  555. }
  556. // finally add any ordering of the results to the SQL statement
  557. if(count($options['order_by']) > 0){
  558. $sql .= " ORDER BY ";
  559. foreach($options['order_by'] as $field => $dir){
  560. $sql .= "$field $dir, ";
  561. }
  562. $sql = substr($sql,0,-2); // get rid of the trailing ', '
  563. }
  564. // if the caller has requested the SQL rather than the results...
  565. // which happens in the case of wanting to use the Drupal pager, then do so
  566. if($options['return_sql']){
  567. return array('sql'=> $sql, 'args' => $args);
  568. }
  569. $previous_db = tripal_db_set_active('chado'); // use chado database
  570. $resource = db_query($sql,$args);
  571. tripal_db_set_active($previous_db); // now use drupal database
  572. $results = array();
  573. while ($r = db_fetch_object($resource)) {
  574. $results[] = $r;
  575. }
  576. if(!$options['has_record']){
  577. return $results;
  578. } else{
  579. return count($results);
  580. }
  581. }
  582. /**
  583. * Gets the value of a foreign key relationship
  584. *
  585. * This function is used by tripal_core_chado_select, tripal_core_chado_insert,
  586. * and tripal_core_chado_update to iterate through the associate array of
  587. * values that gets passed to each of those routines. The values array
  588. * is nested where foreign key contraints are used to specify a value that. See
  589. * documentation for any of those functions for further information.
  590. *
  591. * @param $table_desc
  592. * A table description for the table with the foreign key relationship to be identified generated by
  593. * hook_chado_<table name>_schema()
  594. * @param $field
  595. * The field in the table that is the foreign key.
  596. * @param $values
  597. * An associative array containing the values
  598. *
  599. * @return
  600. * A string containg the results of the foreign key lookup, or FALSE if failed.
  601. *
  602. * Example usage:
  603. * @code
  604. *
  605. * $values = array(
  606. * 'genus' => 'Citrus',
  607. * 'species' => 'sinensis',
  608. * );
  609. * $value = tripal_core_chado_get_foreign_key('feature','organism_id',$values);
  610. *
  611. * @endcode
  612. * The above code selects a record from the feature table using the three fields
  613. * that uniquely identify a feature. The $columns array simply lists the columns
  614. * to select. The $values array is nested such that the organism is identified by
  615. * way of the organism_id foreign key constraint by specifying the genus and
  616. * species. The cvterm is also specified using its foreign key and the cv_id
  617. * for the cvterm is nested as well.
  618. *
  619. * @ingroup tripal_api
  620. */
  621. function tripal_core_chado_get_foreign_key($table_desc,$field,$values, $options = null){
  622. if (!is_array($options)) { $options = array(); }
  623. if (!$options['case_insensitive_columns']) { $options['case_insensitive_columns'] = array(); }
  624. if (!$options['regex_columns']) { $options['regex_columns'] = array(); }
  625. // get the list of foreign keys for this table description and
  626. // iterate through those until we find the one we're looking for
  627. $fkeys = $table_desc['foreign keys'];
  628. if($fkeys){
  629. foreach($fkeys as $name => $def){
  630. if (is_array($def['table'])) {
  631. //foreign key was described 2X
  632. $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")).".";
  633. watchdog('tripal_core', $message);
  634. drupal_set_message($message,'error');
  635. continue;
  636. }
  637. $table = $def['table'];
  638. $columns = $def['columns'];
  639. // iterate through the columns of the foreign key relationship
  640. foreach($columns as $left => $right){
  641. // does the left column in the relationship match our field?
  642. if(strcmp($field,$left)==0){
  643. // the column name of the foreign key matches the field we want
  644. // so this is the right relationship. Now we want to select
  645. $select_cols = array($right);
  646. $result = tripal_core_chado_select($table,$select_cols,$values, $options);
  647. $fields = array();
  648. foreach ($result as $obj) {
  649. $fields[] = $obj->$right;
  650. }
  651. return $fields;
  652. }
  653. }
  654. }
  655. }
  656. else {
  657. // TODO: what do we do if we get to this point and we have a fk
  658. // relationship expected but we don't have any definition for one in the
  659. // table schema??
  660. $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.";
  661. watchdog('tripal_core', $message);
  662. drupal_set_message($message,'error');
  663. }
  664. return false;
  665. }
  666. /**
  667. * Generates an object containing the full details of a record(s) in chado.
  668. *
  669. * @todo Add expand_tables arrays
  670. *
  671. * This differs from the objects returned by tripal_core_chado_select in so far as all foreign key
  672. * relationships have been followed meaning you have more complete details. Thus this function
  673. * should be used whenever you need a full variable and tripal_core_chado_select should be used if
  674. * you only case about a few columns.
  675. *
  676. * @param $table
  677. * The name of the base table to generate a variable for
  678. * @param $values
  679. * A select values array that selects the records you want from the base table
  680. * (this has the same form as tripal_core_chado_select)
  681. * @param $base_options
  682. * An array containing options for the base table. For example, an
  683. * option of 'order_by' may be used to sort results in the base table
  684. * if more than one are returned. The options must be compatible with
  685. * the options accepted by the tripal_core_chado_select() function.
  686. * @return
  687. * Either an object (if only one record was selected from the base table)
  688. * or an array of objects (if more than one record was selected from the base table).
  689. *
  690. * Example Usage:
  691. * @code
  692. $values = array(
  693. 'name' => 'Medtr4g030710'
  694. );
  695. $features = tripal_core_generate_chado_var('feature', $values);
  696. * @endcode
  697. * This will return an object if there is only one feature with the name Medtr4g030710 or it will
  698. * return an array of feature objects if more than one feature has that name.
  699. *
  700. * Note to Module Designers: Fields can be excluded by default from these objects by implementing
  701. * one of the following hooks:
  702. * - hook_exclude_field_from_tablename_by_default (where tablename is the name of the table):
  703. * This hook allows you to add fields to be excluded on a per table basis. Simply implement
  704. * this hook to return an array of fields to be excluded. For example:
  705. * @code
  706. mymodule_exclude_field_from_feature_by_default() {
  707. return array('residues' => TRUE);
  708. }
  709. * @endcode
  710. * will ensure that feature.residues is ecluded from a feature object by default.
  711. * - hook_exclude_type_by_default:
  712. * This hook allows you to exclude fields from all tables that are of a given postgresql field
  713. * type. Simply implement this hook to return an array of postgresql types mapped to criteria.
  714. * Then all fields of that type where the criteria supplied returns TRUE will be excluded from
  715. * any table. Tokens available in criteria are &gt;field_value&lt; and &gt;field_name&lt; . For example:
  716. * @code
  717. mymodule_exclude_type_by_default() {
  718. return array('text' => 'length(&gt;field_value&lt; ) > 50');
  719. }
  720. * @endcode
  721. * 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.
  722. *
  723. * @ingroup tripal_api
  724. */
  725. function tripal_core_generate_chado_var($table, $values, $base_options=array()) {
  726. // get description for the current table----------------------------------------------------------
  727. $table_desc = module_invoke_all('chado_'.$table.'_schema');
  728. $table_primary_key = $table_desc['primary key'][0];
  729. $table_columns = array_keys($table_desc['fields']);
  730. // Expandable fields without value needed for criteria--------------------------------------------
  731. $all->expandable_fields = array();
  732. if ($table_desc['referring_tables']) {
  733. $all->expandable_tables = $table_desc['referring_tables'];
  734. } else {
  735. $all->expandable_tables = array();
  736. }
  737. $all->expandable_nodes = array();
  738. // Get fields to be removed by name.................................
  739. $fields_to_remove = module_invoke_all('exclude_field_from_'.$table.'_by_default');
  740. foreach ($fields_to_remove as $field_name => $criteria) {
  741. //replace &gt;field_name&lt; with the current field name &
  742. $criteria = preg_replace('/&gt;field_name&lt; /', $field_name, $criteria);
  743. // if field_value needed we can't deal with this field yet
  744. if (preg_match('/&gt;field_value&lt; /', $criteria)) { break; }
  745. //if criteria then remove from query
  746. $success = drupal_eval('<?php return '.$criteria.'; ?>');
  747. // watchdog('tripal_core',
  748. // 'Evaluating criteria (%criteria) for field %field in tripal_core_generate_chado_var for %table evaluated to %success',
  749. // array('%table' => $table, '%criteria'=>$criteria, '%field' => $field_name, '%success'=>$success),
  750. // WATCHDOG_NOTICE
  751. // );
  752. if ($success) {
  753. unset($table_columns[array_search($field_name, $table_columns)]);
  754. unset($fields_to_remove[$field_name]);
  755. $all->expandable_fields[] = $table . '.' . $field_name;
  756. }
  757. }
  758. //Get fields to be removed by type................................
  759. $types_to_remove = module_invoke_all('exclude_type_by_default');
  760. $field_types = array();
  761. foreach ($table_desc['fields'] as $field_name => $field_array) {
  762. $field_types[$field_array['type']][] = $field_name;
  763. }
  764. foreach ($types_to_remove as $field_type => $criteria) {
  765. // if there are fields of that type to remove
  766. if (is_array($field_types[$field_type])) {
  767. //replace &gt;field_name&lt; with the current field name &
  768. $criteria = preg_replace('/&gt;field_name&lt; /', $field_name, $criteria);
  769. foreach ($field_types[$field_type] as $field_name) {
  770. // if field_value needed we can't deal with this field yet
  771. if (preg_match('/&gt;field_value&lt; /', $criteria)) {
  772. $fields_to_remove[$field_name] = $criteria;
  773. continue;
  774. }
  775. // if field_value needed we can't deal with this field yet
  776. if (preg_match('/&gt;field_value&lt; /', $criteria)) { break; }
  777. //if criteria then remove from query
  778. $success = drupal_eval('<?php return '.$criteria.'; ?>');
  779. // watchdog('tripal_core',
  780. // 'Evaluating criteria (%criteria) for field %field of $type in tripal_core_generate_chado_var for %table evaluated to %success',
  781. // array('%table'=>$table, '%criteria'=>$criteria, '%field'=>$field_name, '%type'=>$field_type, '%success'=>$success),
  782. // WATCHDOG_NOTICE
  783. // );
  784. if ($success) {
  785. unset($table_columns[array_search($field_name, $table_columns)]);
  786. $all->expandable_fields[] = $table . '.' . $field_name;
  787. }
  788. } //end of foreach field of that type
  789. }
  790. } //end of foreach type to be removed
  791. // get the values for the record in the current table---------------------------------------------
  792. $results = tripal_core_chado_select($table, $table_columns, $values,$base_options);
  793. if($results){
  794. foreach ($results as $key => $object) {
  795. // Add empty expandable_x arrays
  796. $object->expandable_fields = $all->expandable_fields;
  797. $object->expandable_tables = $all->expandable_tables;
  798. $object->expandable_nodes = $all->expandable_nodes;
  799. // add curent table
  800. $object->tablename = $table;
  801. // check if the current table maps to a node type-----------------------------------------------
  802. // if this table is connected to a node there will be a chado_tablename table in drupal
  803. if (db_table_exists('chado_'.$table)) {
  804. // that has a foreign key to this one ($table_desc['primary key'][0]
  805. // and to the node table (nid)
  806. $sql = "SELECT %s, nid FROM chado_%s WHERE %s=%d";
  807. $mapping = db_fetch_object(db_query(
  808. $sql,
  809. $table_primary_key,
  810. $table,
  811. $table_primary_key,
  812. $object->{$table_primary_key}
  813. ));
  814. if ($mapping->{$table_primary_key}) {
  815. $object->nid = $mapping->nid;
  816. $object->expandable_nodes[] = $table;
  817. }
  818. }
  819. // remove any fields where criteria need to be evalulated---------------------------------------
  820. foreach ($fields_to_remove as $field_name => $criteria) {
  821. if (!isset($object->{$field_name})) { break; }
  822. $criteria = preg_replace('/&gt;field_value&lt; /', $object->{$field_name}, $criteria);
  823. //if criteria then remove from query
  824. $success = drupal_eval('<?php return '.$criteria.'; ?>');
  825. // watchdog('tripal_core',
  826. // 'Evaluating criteria (%criteria) for field %field in tripal_core_generate_chado_var for %table evaluated to %success',
  827. // array('%table' => $table, '%criteria'=>$criteria, '%field' => $field_name, '%success'=>$success),
  828. // WATCHDOG_NOTICE
  829. // );
  830. if ($success) {
  831. unset($object->{$field_name});
  832. $object->expandable_fields[] = $table . '.' . $field_name;
  833. }
  834. }
  835. // recursively follow foreign key relationships nesting objects as we go------------------------
  836. if ($table_desc['foreign keys']) {
  837. foreach ($table_desc['foreign keys'] as $foreign_key_array) {
  838. $foreign_table = $foreign_key_array['table'];
  839. foreach ($foreign_key_array['columns'] as $foreign_key => $primary_key) {
  840. // Note: Foreign key is the field in the current table whereas primary_key is the field in
  841. // the table referenced by the foreign key
  842. //Dont do anything if the foreign key is empty
  843. if (empty($object->{$foreign_key})) {
  844. break;
  845. }
  846. // get the record from the foreign table
  847. $foreign_values = array($primary_key => $object->{$foreign_key});
  848. $foreign_object = tripal_core_generate_chado_var($foreign_table, $foreign_values);
  849. // add the foreign record to the current object in a nested manner
  850. $object->{$foreign_key} = $foreign_object;
  851. // Flatten expandable_x arrays so only in the bottom object
  852. if (is_array($object->{$foreign_key}->expandable_fields)) {
  853. $object->expandable_fields = array_merge(
  854. $object->expandable_fields,
  855. $object->{$foreign_key}->expandable_fields
  856. );
  857. unset($object->{$foreign_key}->expandable_fields);
  858. }
  859. if (is_array($object->{$foreign_key}->expandable_tables)) {
  860. $object->expandable_tables = array_merge(
  861. $object->expandable_tables,
  862. $object->{$foreign_key}->expandable_tables
  863. );
  864. unset($object->{$foreign_key}->expandable_tables);
  865. }
  866. if (is_array($object->{$foreign_key}->expandable_nodes)) {
  867. $object->expandable_nodes = array_merge(
  868. $object->expandable_nodes,
  869. $object->{$foreign_key}->expandable_nodes
  870. );
  871. unset($object->{$foreign_key}->expandable_nodes);
  872. }
  873. }
  874. }
  875. $results[$key] = $object;
  876. }
  877. }
  878. }
  879. // check only one result returned
  880. if (sizeof($results) == 1) {
  881. // add results to object
  882. return $results[0];
  883. } elseif (!empty($results)) {
  884. return $results;
  885. } else {
  886. // no results returned
  887. }
  888. }
  889. /**
  890. * Retrieves fields/tables/nodes that were excluded by default from a variable and adds them
  891. *
  892. * This function exists to allow tripal_core_generate_chado_var() to excldue some
  893. * fields/tables/nodes from the default form of a variable without making it extremely difficult for
  894. * the tripal admin to get at these variables if he/she wants them.
  895. *
  896. * @param $object
  897. * This must be an object generated using tripal_core_generate_chado_var()
  898. * @param $type
  899. * Must be one of 'field', 'table', 'node'. Indicates what is being expanded.
  900. * @param $to_expand
  901. * The name of the field/table/node to be expanded
  902. * @param $table_options
  903. * An array containing options for the base table. For example, an
  904. * option of 'order_by' may be used to sort results in the base table
  905. * if more than one are returned. The options must be compatible with
  906. * the options accepted by the tripal_core_chado_select() function.
  907. * @return
  908. * A chado object supplemented with the field/table/node requested to be expanded
  909. *
  910. * Example Usage:
  911. * @code
  912. // Get a chado object to be expanded
  913. $values = array(
  914. 'name' => 'Medtr4g030710'
  915. );
  916. $features = tripal_core_generate_chado_var('feature', $values);
  917. // Expand the organism node
  918. $feature = tripal_core_expand_chado_vars($feature, 'node', 'organism');
  919. // Expand the feature.residues field
  920. $feature = tripal_core_expand_chado_vars($feature, 'field', 'feature.residues');
  921. // Expand the feature properties (featureprop table)
  922. $feature = tripal_core_expand_chado_vars($feature, 'table', 'featureprop');
  923. * @endcode
  924. *
  925. * @ingroup tripal_api
  926. */
  927. function tripal_core_expand_chado_vars ($object, $type, $to_expand,$table_options = array()) {
  928. $base_table = $object->tablename;
  929. // check to see if they are expanding an array of objects
  930. if (is_array($object)) {
  931. foreach ($object as $index => $o) {
  932. $object[$index] = tripal_core_expand_chado_vars($o,$type,$to_expand);
  933. }
  934. return $object;
  935. }
  936. switch ($type) {
  937. case "field": //--------------------------------------------------------------------------------
  938. if (preg_match('/(\w+)\.(\w+)/', $to_expand, $matches)) {
  939. $tablename = $matches[1];
  940. $fieldname = $matches[2];
  941. $table_desc = module_invoke_all('chado_'.$tablename.'_schema');
  942. $values = array();
  943. foreach($table_desc['primary key'] as $key) {
  944. $values[$key] = $object->{$key};
  945. }
  946. if ($base_table == $tablename) {
  947. //get the field
  948. $results = tripal_core_chado_select(
  949. $tablename,
  950. array($fieldname),
  951. $values
  952. );
  953. $object->{$fieldname} = $results[0]->{$fieldname};
  954. $object->expanded = $to_expand;
  955. } else {
  956. //We need to recurse -the field is in a nested object
  957. foreach ((array) $object as $field_name => $field_value) {
  958. if (is_object($field_value)) {
  959. $object->{$field_name} = tripal_core_expand_chado_vars(
  960. $field_value,
  961. 'field',
  962. $to_expand
  963. );
  964. }
  965. } //end of for each field in the current object
  966. }
  967. } else {
  968. watchdog(
  969. 'tripal_core',
  970. 'tripal_core_expand_chado_vars: Field (%field) not in the right format. It should be <tablename>.<fieldname>',
  971. WATCHDOG_ERROR
  972. );
  973. }
  974. break;
  975. case "table": //--------------------------------------------------------------------------------
  976. $foreign_table = $to_expand;
  977. $foreign_table_desc = module_invoke_all('chado_'.$foreign_table.'_schema');
  978. // If it's connected to the base table
  979. if ($foreign_table_desc['foreign keys'][$base_table]) {
  980. foreach ($foreign_table_desc['foreign keys'][$base_table]['columns'] as $left => $right) {
  981. if (!$object->{$right}) { break; }
  982. if (is_array($values)) {
  983. $values = array_merge($values, array($left => $object->{$right}) );
  984. } else {
  985. $values = array($left => $object->{$right});
  986. }
  987. $foreign_object = tripal_core_generate_chado_var(
  988. $foreign_table,
  989. array($left => $object->{$right}),
  990. $table_options
  991. );
  992. if ($foreign_object) {
  993. // in the case where the a foreign key relationships exists more
  994. // than once with the same table we want to alter the
  995. // array structure
  996. if(count($foreign_table_desc['foreign keys'][$base_table]['columns']) > 1){
  997. $object->{$foreign_table}->{$left} = $foreign_object;
  998. $object->expanded = $to_expand;
  999. } else {
  1000. $object->{$foreign_table} = $foreign_object;
  1001. $object->expanded = $to_expand;
  1002. }
  1003. }
  1004. }
  1005. } else {
  1006. //We need to recurse -the table has a relationship to one of the nested objects
  1007. foreach ((array) $object as $field_name => $field_value) {
  1008. // if we have a nested object ->expand the table in it
  1009. if (is_object($field_value)) {
  1010. $object->{$field_name} = tripal_core_expand_chado_vars(
  1011. $field_value,
  1012. 'table',
  1013. $foreign_table
  1014. );
  1015. }
  1016. }
  1017. }
  1018. break;
  1019. case "node": //---------------------------------------------------------------------------------
  1020. //if the node to be expanded is for our base table, then just expand it
  1021. if ($object->tablename == $to_expand) {
  1022. $node = node_load($object->nid);
  1023. if ($node) {
  1024. $object->expanded = $to_expand;
  1025. $node->expandable_fields = $object->expandable_fields;
  1026. unset($object->expandable_fields);
  1027. $node->expandable_tables = $object->expandable_tables;
  1028. unset($object->expandable_tables);
  1029. $node->expandable_nodes = $object->expandable_nodes;
  1030. unset($object->expandable_nodes);
  1031. $node->{$base_table} = $object;
  1032. $object = $node;
  1033. } else {
  1034. watchdog(
  1035. 'tripal_core',
  1036. 'tripal_core_expand_chado_vars: No node matches the nid (%nid) supplied.',
  1037. array('%nid'=>$object->nid),
  1038. WATCHDOG_ERROR
  1039. );
  1040. } //end of if node
  1041. } else {
  1042. //We need to recurse -the node to expand is one of the nested objects
  1043. foreach ((array) $object as $field_name => $field_value) {
  1044. if (is_object($field_value)) {
  1045. $object->{$field_name} = tripal_core_expand_chado_vars(
  1046. $field_value,
  1047. 'node',
  1048. $to_expand
  1049. );
  1050. }
  1051. } //end of for each field in the current object
  1052. }
  1053. break;
  1054. default:
  1055. watchdog('tripal_core',
  1056. 'tripal_core_expand_chado_vars: Unrecognized type (%type). Should be one of "field", "table", "node".',
  1057. array('%type'=>$type),
  1058. WATCHDOG_ERROR
  1059. );
  1060. return FALSE;
  1061. }
  1062. //move extended array downwards-------------------------------------------------------------------
  1063. if (!$object->expanded) {
  1064. //if there's no extended field then go hunting for it
  1065. foreach ( (array)$object as $field_name => $field_value) {
  1066. if (is_object($field_value)) {
  1067. if (isset($field_value->expanded)) {
  1068. $object->expanded = $field_value->expanded;
  1069. unset($field_value->expanded);
  1070. }
  1071. }
  1072. }
  1073. }
  1074. //try again becasue now we might have moved it down
  1075. if ($object->expanded) {
  1076. $expandable_name = 'expandable_'.$type.'s';
  1077. if ($object->{$expandable_name}) {
  1078. $key_to_remove = array_search($object->expanded, $object->{$expandable_name});
  1079. unset($object->{$expandable_name}[$key_to_remove]);
  1080. unset($object->expanded);
  1081. } else {
  1082. // if there is an expandable array then we've reached the base object
  1083. // if we get here and don't have anything expanded then something went wrong
  1084. // watchdog(
  1085. // 'tripal_core',
  1086. // 'tripal_core_expand_chado_vars: Unable to expand the %type %to_expand',
  1087. // array('%type'=>$type, '%to_expand'=>$to_expand),
  1088. // WATCHDOG_ERROR
  1089. // );
  1090. } //end of it we've reached the base object
  1091. }
  1092. return $object;
  1093. }
  1094. /**
  1095. * Implements hook_exclude_type_by_default()
  1096. *
  1097. * This hooks allows fields of a specified type that match a specified criteria to be excluded by
  1098. * default from any table when tripal_core_generate_chado_var() is called. Keep in mind that if
  1099. * fields are excluded by default they can always be expanded at a later date using
  1100. * tripal_core_expand_chado_vars().
  1101. *
  1102. * Criteria are php strings that evaluate to either TRUE or FALSE. These strings are evaluated using
  1103. * drupal_eval() which suppresses syntax errors and throws watchdog entries of type php. There are
  1104. * also watchdog entries of type tripal_core stating the exact criteria evaluated. Criteria can
  1105. * contain the following tokens:
  1106. * - &gt;field_name&lt;
  1107. * Replaced by the name of the field to be excluded
  1108. * - &gt;field_value&lt;
  1109. * Replaced by the value of the field in the current record
  1110. * Also keep in mind that if your criteria doesn't contain the &gt;field_value&lt; token then it will be
  1111. * evaluated before the query is executed and if the field is excluded it won't be included in the
  1112. * query.
  1113. *
  1114. * @return
  1115. * An array of type => criteria where the type is excluded if the criteria evaluates to TRUE
  1116. *
  1117. * @ingroup tripal_api
  1118. */
  1119. function tripal_core_exclude_type_by_default() {
  1120. return array('text' => "strlen('&gt;field_value&lt; ') > 100");
  1121. }
  1122. /**
  1123. * Implements hook_exclude_field_from_<tablename>_by_default()
  1124. *
  1125. * This hooks allows fields from a specified table that match a specified criteria to be excluded by
  1126. * default from any table when tripal_core_generate_chado_var() is called. Keep in mind that if
  1127. * fields are excluded by default they can always be expanded at a later date using
  1128. * tripal_core_expand_chado_vars().
  1129. *
  1130. * Criteria are php strings that evaluate to either TRUE or FALSE. These strings are evaluated using
  1131. * drupal_eval() which suppresses syntax errors and throws watchdog entries of type php. There are
  1132. * also watchdog entries of type tripal_core stating the exact criteria evaluated. Criteria can
  1133. * contain the following tokens:
  1134. * - &gt;field_name&lt;
  1135. * Replaced by the name of the field to be excluded
  1136. * - &gt;field_value&lt;
  1137. * Replaced by the value of the field in the current record
  1138. * Also keep in mind that if your criteria doesn't contain the &gt;field_value&lt; token then it will be
  1139. * evaluated before the query is executed and if the field is excluded it won't be included in the
  1140. * query.
  1141. *
  1142. * @return
  1143. * An array of type => criteria where the type is excluded if the criteria evaluates to TRUE
  1144. *
  1145. * @ingroup tripal_api
  1146. */
  1147. function tripal_core_exclude_field_from_feature_by_default() {
  1148. return array();
  1149. }
  1150. /************************************************************************
  1151. * Use this function instead of db_query() to avoid switching databases
  1152. * when making query to the chado database
  1153. */
  1154. function chado_query($sql) {
  1155. $args = func_get_args();
  1156. array_shift($args);
  1157. $sql = db_prefix_tables($sql);
  1158. if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
  1159. $args = $args[0];
  1160. }
  1161. _db_query_callback($args, TRUE);
  1162. $sql = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $sql);
  1163. $previous_db = db_set_active('chado');
  1164. $results = _db_query($sql);
  1165. db_set_active($previous_db);
  1166. return $results;
  1167. }
  1168. /************************************************************************
  1169. * Get chado id for a node. E.g, if you want to get 'analysis_id' from the
  1170. * 'analysis' table for a synced 'chado_analysis' node, use:
  1171. * $analysis_id = chado_get_id_for_node ('analysis', $node)
  1172. * Likewise,
  1173. * $organism_id = chado_get_id_for_node ('organism', $node)
  1174. * $feature_id = chado_get_id_for_node ('feature', $node)
  1175. */
  1176. function chado_get_id_for_node ($table, $node) {
  1177. return db_result(db_query("SELECT $table"."_id FROM {chado_".$table."} WHERE nid = $node->nid"));
  1178. }
  1179. /************************************************************************
  1180. * Get node id for a chado feature/organism/analysis. E.g, if you want to
  1181. * get the node id for an analysis, use:
  1182. * $nid = chado_get_node_id ('analysis', $analysis_id)
  1183. * Likewise,
  1184. * $nid = chado_get_node_id ('organism', $organism_id)
  1185. * $nid = chado_get_node_id ('feature', $feature_id)
  1186. */
  1187. function chado_get_node_id ($table, $id) {
  1188. return db_result(db_query("SELECT nid FROM {chado_".$table."} WHERE $table"."_id = $id"));
  1189. }
  1190. /**
  1191. * Adds a single property to an existing library record.
  1192. *
  1193. * @ingroup tripal_api
  1194. */
  1195. function tripal_core_get_property($basetable, $record_id, $property,$cv_name){
  1196. $table_desc = module_invoke_all('chado_'.$basetable.'prop_schema');
  1197. $fkcol = key($table_desc['foreign keys'][$basetable]['columns']);
  1198. // construct the array of values to be inserted
  1199. $values = array (
  1200. $fkcol => $record_id,
  1201. 'type_id' => array (
  1202. 'cv_id' => array (
  1203. 'name' => $cv_name,
  1204. ),
  1205. 'name' => $property,
  1206. 'is_obsolete' => 0
  1207. ),
  1208. );
  1209. $results = tripal_core_generate_chado_var($basetable.'prop',$values);
  1210. $results = tripal_core_expand_chado_vars($results,'field',$basetable.'prop.value');
  1211. return $results;
  1212. }
  1213. /**
  1214. * Adds a single property to an existing record.
  1215. *
  1216. * @ingroup tripal_core.api
  1217. */
  1218. function tripal_core_insert_property($proptable, $feature_id,$property,$value,$update_if_present = 0){
  1219. // first see if the property already exists, if so we can't insert
  1220. $prop = tripal_feature_get_property($feature_id,$property);
  1221. if(count($prop)>0){
  1222. if($update_if_present){
  1223. return tripal_feature_update_property($feature_id,$property,$value) ;
  1224. } else {
  1225. return FALSE;
  1226. }
  1227. }
  1228. // construct the array of values to be inserted
  1229. $values = array (
  1230. 'feature_id' => $feature_id,
  1231. 'type_id' => array (
  1232. 'cv_id' => array (
  1233. 'name' => 'tripal',
  1234. ),
  1235. 'name' => $property,
  1236. 'is_obsolete' => 0
  1237. ),
  1238. 'value' => $value,
  1239. 'rank' => 0,
  1240. );
  1241. return tripal_core_chado_insert('featureprop',$values);
  1242. }
  1243. /**
  1244. * Adds a single property to an existing feature record.
  1245. *
  1246. * @ingroup tripal_api
  1247. */
  1248. function tripal_core_update_property($proptable, $feature_id,$property,$value,$insert_if_missing = 0){
  1249. // first see if the property is missing (we can't update a missing property
  1250. $prop = tripal_feature_get_property($feature_id,$property);
  1251. if(count($prop)==0){
  1252. if($insert_if_missing){
  1253. return tripal_feature_insert_property($feature_id,$property,$value);
  1254. } else {
  1255. return FALSE;
  1256. }
  1257. }
  1258. // construct the array that will match the exact record to update
  1259. $match = array (
  1260. 'feature_id' => $feature_id,
  1261. 'type_id' => array (
  1262. 'cv_id' => array (
  1263. 'name' => 'tripal',
  1264. ),
  1265. 'name' => $property,
  1266. ),
  1267. );
  1268. // construct the array of values to be updated
  1269. $values = array (
  1270. 'value' => $value,
  1271. );
  1272. return tripal_core_chado_update('featureprop',$match,$values);
  1273. }
  1274. /**
  1275. * Adds a single property to an existing feature record.
  1276. *
  1277. * @ingroup tripal_api
  1278. */
  1279. function tripal_core_delete_property($proptable, $feature_id,$property){
  1280. // construct the array that will match the exact record to update
  1281. $match = array (
  1282. 'feature_id' => $feature_id,
  1283. 'type_id' => array (
  1284. 'cv_id' => array (
  1285. 'name' => 'tripal',
  1286. ),
  1287. 'name' => $property,
  1288. ),
  1289. );
  1290. return tripal_core_chado_delete('featureprop',$match);
  1291. }