tripal_core.api.inc 65 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545154615471548154915501551155215531554155515561557155815591560156115621563156415651566156715681569157015711572157315741575157615771578157915801581158215831584158515861587158815891590159115921593159415951596159715981599160016011602160316041605160616071608160916101611161216131614161516161617161816191620162116221623162416251626162716281629163016311632163316341635163616371638163916401641164216431644164516461647164816491650165116521653165416551656165716581659166016611662166316641665166616671668166916701671167216731674167516761677
  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 Tripal
  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 its infancy and some necessary functions might be missing. If you find
  18. * a missing function that you think should be included go to the sourceforge feature request
  19. * page and request it's inclusion in the API. Such feature requests with a working function
  20. * definition will be given priority.
  21. * @}
  22. */
  23. require_once "tripal_core.schema.api.inc";
  24. /**
  25. * @defgroup tripal_chado_api Core Module Chado API
  26. * @{
  27. * Provides an application programming interface (API) to manage data withing the Chado database.
  28. * This includes functions for selecting, inserting, updating and deleting records
  29. * in Chado tables. The functions will ensure proper integrity contraints are met
  30. * for inserts and updates.
  31. *
  32. * Also, a set of functions is provided for creating template variables. First,
  33. * is the tripal_core_generate_chado_vars which is used to select one ore more
  34. * records from a table and return an array with foreign key relationships fully
  35. * populated. For example, if selecting a feature, the organism_id and type_id
  36. * would be present in the returned array as a nested array with their respective
  37. * foreign keys also nested. The only fields that are not included are text
  38. * fields (which may be very large) or many-to-many foreign key relationships.
  39. * However, these fields and relationships can be expanded using the
  40. * tripal_core_expand_chado_vars.
  41. *
  42. * When a row from a chado table is selected using these two functions, it provides
  43. * a way for users who want to cutomize Drupal template files to access all data
  44. * associate with a specific record.
  45. *
  46. * Finally, the property tables in Chado generally follow the same format. Therefore
  47. * there is a set of functions for inserting, updating and deleting properties for
  48. * any table. This provides quick lookup of properties (provided the CV term is
  49. * known).
  50. *
  51. * @}
  52. * @ingroup tripal_api
  53. */
  54. /**
  55. * @defgroup tripal_files_api Core Module Files API
  56. * @{
  57. * Provides an application programming interface (API) for managing files within
  58. * the Tripal data directory structure.
  59. *
  60. * @}
  61. * @ingroup tripal_api
  62. */
  63. /**
  64. * Provides a generic routine for inserting into any Chado table
  65. *
  66. * Use this function to insert a record into any Chado table. The first
  67. * argument specifies the table for inserting and the second is an array
  68. * of values to be inserted. The array is mutli-dimensional such that
  69. * foreign key lookup values can be specified.
  70. *
  71. * @param $table
  72. * The name of the chado table for inserting
  73. * @param $values
  74. * An associative array containing the values for inserting.
  75. *
  76. * @return
  77. * On success this function returns TRUE. On failure, it returns FALSE.
  78. *
  79. * Example usage:
  80. * @code
  81. * $values = array(
  82. * 'organism_id' => array(
  83. * 'genus' => 'Citrus',
  84. * 'species' => 'sinensis',
  85. * ),
  86. * 'name' => 'orange1.1g000034m.g',
  87. * 'uniquename' => 'orange1.1g000034m.g',
  88. * 'type_id' => array (
  89. * 'cv_id' => array (
  90. * 'name' => 'sequence',
  91. * ),
  92. * 'name' => 'gene',
  93. * 'is_obsolete' => 0
  94. * ),
  95. * );
  96. * $result = tripal_core_chado_insert('feature',$values);
  97. * @endcode
  98. * The above code inserts a record into the feature table. The $values array is
  99. * nested such that the organism is selected by way of the organism_id foreign
  100. * key constraint by specifying the genus and species. The cvterm is also
  101. * specified using its foreign key and the cv_id for the cvterm is nested as
  102. * well.
  103. *
  104. * @ingroup tripal_chado_api
  105. */
  106. function tripal_core_chado_insert($table,$values){
  107. $insert_values = array();
  108. // get the table description
  109. $table_desc = module_invoke_all('chado_'.$table.'_schema');
  110. // iterate through the values array and create a new 'insert_values' array
  111. // that has all the values needed for insert with all foreign relationsihps
  112. // resolved.
  113. foreach($values as $field => $value){
  114. if(is_array($value)){
  115. // select the value from the foreign key relationship for this value
  116. $results = tripal_core_chado_get_foreign_key($table_desc,$field,$value);
  117. if (sizeof($results) > 1) {
  118. 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);
  119. } elseif (sizeof($results) < 1) {
  120. //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);
  121. } else {
  122. $insert_values[$field] = $results[0];
  123. }
  124. }
  125. else {
  126. $insert_values[$field] = $value;
  127. }
  128. }
  129. // check for violation of any unique constraints
  130. $ukeys = $table_desc['unique keys'];
  131. $ukselect_cols = array();
  132. $ukselect_vals = array();
  133. if ($ukeys) {
  134. foreach($ukeys as $name => $fields){
  135. foreach($fields as $index => $field){
  136. // build the arrays for performing a select that will check the contraint
  137. array_push($ukselect_cols,$field);
  138. $ukselect_vals[$field] = $insert_values[$field];
  139. }
  140. // now check the constraint
  141. if(tripal_core_chado_select($table,$ukselect_cols,$ukselect_vals)){
  142. watchdog('tripal_core',"tripal_core_chado_insert: Cannot insert duplicate record into $table table: " . print_r($values,1),array(),'WATCHDOG_ERROR');
  143. return false;
  144. }
  145. }
  146. }
  147. // if trying to insert a field that is the primary key, make sure it also is unique
  148. $pkey = $table_desc['primary key'][0];
  149. if($insert_values[$pkey]){
  150. if(tripal_core_chado_select($table,array($pkey),array($pkey => $insert_values[$pkey]))){
  151. watchdog('tripal_core',"tripal_core_chado_insert: Cannot insert duplicate primary key into $table table: " . print_r($values,1),array(),'WATCHDOG_ERROR');
  152. return false;
  153. }
  154. }
  155. // make sure required fields have a value
  156. $fields = $table_desc['fields'];
  157. foreach($fields as $field => $def){
  158. // a field is considered missing if it cannot be null and there is no default
  159. // value for it or it is of type 'serial'
  160. if($def['not null'] == 1 and !array_key_exists($field,$insert_values) and !isset($def['default']) and strcmp($def['type'],serial)!=0){
  161. watchdog('tripal_core',"tripal_core_chado_insert: Field $table.$field cannot be null: " . print_r($values,1),array(),'WATCHDOG_ERROR');
  162. return false;
  163. }
  164. }
  165. // Now build the insert SQL statement
  166. $ifields = array();
  167. $ivalues = array();
  168. $itypes = array();
  169. foreach ($insert_values as $field => $value){
  170. array_push($ifields,$field);
  171. array_push($ivalues,$value);
  172. if(strcmp($value,'__NULL__')==0){
  173. array_push($itypes,"NULL");
  174. }
  175. elseif(strcmp($fields[$field]['type'],'serial')==0 or
  176. strcmp($fields[$field]['type'],'int')==0){
  177. array_push($itypes,"%d");
  178. }
  179. else {
  180. array_push($itypes,"'%s'");
  181. }
  182. }
  183. $sql = "INSERT INTO {$table} (" . implode(", ",$ifields) . ") VALUES (". implode(", ",$itypes) .")";
  184. // finally perform the insert.
  185. $previous_db = tripal_db_set_active('chado'); // use chado database
  186. $result = db_query($sql,$ivalues);
  187. tripal_db_set_active($previous_db); // now use drupal database
  188. if($result){
  189. // add primary keys to values before return
  190. $primary_key = array();
  191. foreach ($table_desc['primary key'] as $field) {
  192. $value = db_last_insert_id($table, $field);
  193. $values[$field] = $value;
  194. }
  195. return $values;
  196. }
  197. else {
  198. watchdog('tripal_core',"tripal_core_chado_insert: Cannot insert record into $table table: " . print_r($values,1),array(),'WATCHDOG_ERROR');
  199. return false;
  200. }
  201. return false;
  202. }
  203. /**
  204. * Provides a generic function for deleting a record(s) from any chado table
  205. *
  206. * Use this function to delete a record(s) in any Chado table. The first
  207. * argument specifies the table to delete from and the second is an array
  208. * of values to match for locating the record(s) to be deleted. The arrays
  209. * are mutli-dimensional such that foreign key lookup values can be specified.
  210. *
  211. * @param $table
  212. * The name of the chado table for inserting
  213. * @param $match
  214. * An associative array containing the values for locating a record to update.
  215. *
  216. * @return
  217. * On success this function returns TRUE. On failure, it returns FALSE.
  218. *
  219. * Example usage:
  220. * @code
  221. $umatch = array(
  222. 'organism_id' => array(
  223. 'genus' => 'Citrus',
  224. 'species' => 'sinensis',
  225. ),
  226. 'uniquename' => 'orange1.1g000034m.g7',
  227. 'type_id' => array (
  228. 'cv_id' => array (
  229. 'name' => 'sequence',
  230. ),
  231. 'name' => 'gene',
  232. 'is_obsolete' => 0
  233. ),
  234. );
  235. $uvalues = array(
  236. 'name' => 'orange1.1g000034m.g',
  237. 'type_id' => array (
  238. 'cv_id' => array (
  239. 'name' => 'sequence',
  240. ),
  241. 'name' => 'mRNA',
  242. 'is_obsolete' => 0
  243. ),
  244. );
  245. * $result = tripal_core_chado_update('feature',$umatch,$uvalues);
  246. * @endcode
  247. * The above code species that a feature with a given uniquename, organism_id,
  248. * and type_id (the unique constraint for the feature table) will be deleted.
  249. * The organism_id is specified as a nested array that uses the organism_id
  250. * foreign key constraint to lookup the specified values to find the exact
  251. * organism_id. The same nested struture is also used for specifying the
  252. * values to update. The function will find all records that match the
  253. * columns specified and delete them.
  254. *
  255. * @ingroup tripal_chado_api
  256. */
  257. function tripal_core_chado_delete($table,$match){
  258. $delete_matches = array(); // contains the values for the where clause
  259. // get the table description
  260. $table_desc = module_invoke_all('chado_'.$table.'_schema');
  261. // get the values needed for matching in the SQL statement
  262. foreach ($match as $field => $value){
  263. if(is_array($value)){
  264. // if the user has specified an array of values to delete rather than
  265. // FK relationships the keep those in our match
  266. if(array_values($value) === $value){
  267. $delete_matches[$field] = $value;
  268. } else {
  269. $results = tripal_core_chado_get_foreign_key($table_desc,$field,$value);
  270. if (sizeof($results) > 1) {
  271. 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);
  272. } elseif (sizeof($results) < 1) {
  273. //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);
  274. } else {
  275. $delete_matches[$field] = $results[0];
  276. }
  277. }
  278. }
  279. else {
  280. $delete_matches[$field] = $value;
  281. }
  282. }
  283. // now build the SQL statement
  284. $sql = "DELETE FROM {$table} WHERE ";
  285. $dargs = array();
  286. foreach($delete_matches as $field => $value){
  287. if (count($value) > 1) {
  288. $sql .= "$field IN (".db_placeholders($value,'varchar').") AND ";
  289. foreach ($value as $v) { $dargs[] = $v; }
  290. } else {
  291. if(strcmp($value,'__NULL__')==0){
  292. $sql .= " $field = NULL AND ";
  293. }
  294. elseif(strcmp($fields[$field]['type'],'serial')==0 or
  295. strcmp($fields[$field]['type'],'int')==0){
  296. $sql .= " $field = %d AND ";
  297. }
  298. else {
  299. $sql .= " $field = '%s' AND ";
  300. }
  301. array_push($dargs,$value);
  302. }
  303. }
  304. $sql = substr($sql,0,-4); // get rid of the trailing 'AND'
  305. // finally perform the delete. If successful, return the updated record
  306. $previous_db = tripal_db_set_active('chado'); // use chado database
  307. $result = db_query($sql,$dargs);
  308. tripal_db_set_active($previous_db); // now use drupal database
  309. if($result){
  310. return true;
  311. }
  312. else {
  313. watchdog('tripal_core',"Cannot delete record in $table table. Match:" . print_r($match,1) . ". Values: ". print_r($values,1),array(),'WATCHDOG_ERROR');
  314. return false;
  315. }
  316. return false;
  317. }
  318. /**
  319. * Provides a generic routine for updating into any Chado table
  320. *
  321. * Use this function to update a record in any Chado table. The first
  322. * argument specifies the table for inserting, the second is an array
  323. * of values to matched for locating the record for updating, and the third
  324. * argument give the values to update. The arrays are mutli-dimensional such
  325. * that foreign key lookup values can be specified.
  326. *
  327. * @param $table
  328. * The name of the chado table for inserting
  329. * @param $match
  330. * An associative array containing the values for locating a record to update.
  331. * @param $values
  332. * An associative array containing the values for updating.
  333. *
  334. * @return
  335. * On success this function returns TRUE. On failure, it returns FALSE.
  336. *
  337. * Example usage:
  338. * @code
  339. $umatch = array(
  340. 'organism_id' => array(
  341. 'genus' => 'Citrus',
  342. 'species' => 'sinensis',
  343. ),
  344. 'uniquename' => 'orange1.1g000034m.g7',
  345. 'type_id' => array (
  346. 'cv_id' => array (
  347. 'name' => 'sequence',
  348. ),
  349. 'name' => 'gene',
  350. 'is_obsolete' => 0
  351. ),
  352. );
  353. $uvalues = array(
  354. 'name' => 'orange1.1g000034m.g',
  355. 'type_id' => array (
  356. 'cv_id' => array (
  357. 'name' => 'sequence',
  358. ),
  359. 'name' => 'mRNA',
  360. 'is_obsolete' => 0
  361. ),
  362. );
  363. * $result = tripal_core_chado_update('feature',$umatch,$uvalues);
  364. * @endcode
  365. * The above code species that a feature with a given uniquename, organism_id,
  366. * and type_id (the unique constraint for the feature table) will be updated.
  367. * The organism_id is specified as a nested array that uses the organism_id
  368. * foreign key constraint to lookup the specified values to find the exact
  369. * organism_id. The same nested struture is also used for specifying the
  370. * values to update. The function will find the record that matches the
  371. * columns specified and update the record with the avlues in the $uvalues array.
  372. *
  373. * @ingroup tripal_chado_api
  374. */
  375. function tripal_core_chado_update($table,$match,$values){
  376. $update_values = array(); // contains the values to be updated
  377. $update_matches = array(); // contains the values for the where clause
  378. // get the table description
  379. $table_desc = module_invoke_all('chado_'.$table.'_schema');
  380. // get the values needed for matching in the SQL statement
  381. foreach ($match as $field => $value){
  382. if(is_array($value)){
  383. $results = tripal_core_chado_get_foreign_key($table_desc,$field,$value);
  384. if (sizeof($results) > 1) {
  385. 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);
  386. } elseif (sizeof($results) < 1) {
  387. //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);
  388. } else {
  389. $update_matches[$field] = $results[0];
  390. }
  391. }
  392. else {
  393. $update_matches[$field] = $value;
  394. }
  395. }
  396. // get the values used for updating
  397. foreach ($values as $field => $value){
  398. if(is_array($value)){
  399. $results = tripal_core_chado_get_foreign_key($table_desc,$field,$value);
  400. if (sizeof($results) > 1) {
  401. 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);
  402. } elseif (sizeof($results) < 1) {
  403. //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);
  404. } else {
  405. $update_values[$field] = $results[0];
  406. }
  407. }
  408. else {
  409. $update_values[$field] = $value;
  410. }
  411. }
  412. // now build the SQL statement
  413. $sql = "UPDATE {$table} SET ";
  414. $fields = $table_desc['fields'];
  415. $uargs = array();
  416. foreach($update_values as $field => $value){
  417. if(strcmp($value,'__NULL__')==0){
  418. $sql .= " $field = NULL, ";
  419. }
  420. elseif(strcmp($fields[$field]['type'],'serial')==0 or
  421. strcmp($fields[$field]['type'],'int')==0){
  422. $sql .= " $field = %d, ";
  423. } else {
  424. $sql .= " $field = '%s', ";
  425. }
  426. array_push($uargs,$value);
  427. }
  428. $sql = substr($sql,0,-2); // get rid of the trailing comma & space
  429. $sql .= " WHERE ";
  430. foreach($update_matches as $field => $value){
  431. if(strcmp($value,'__NULL__')==0){
  432. $sql .= " $field = NULL AND ";
  433. }
  434. elseif(strcmp($fields[$field]['type'],'serial')==0 or
  435. strcmp($fields[$field]['type'],'int')==0){
  436. $sql .= " $field = %d AND ";
  437. }
  438. else {
  439. $sql .= " $field = '%s' AND ";
  440. }
  441. array_push($uargs,$value);
  442. }
  443. $sql = substr($sql,0,-4); // get rid of the trailing 'AND'
  444. // finally perform the update. If successful, return the updated record
  445. $previous_db = tripal_db_set_active('chado'); // use chado database
  446. $result = db_query($sql,$uargs);
  447. tripal_db_set_active($previous_db); // now use drupal database
  448. if($result){
  449. return true;
  450. }
  451. else {
  452. watchdog('tripal_core',"Cannot update record in $table table. Match:" . print_r($match,1) . ". Values: ". print_r($values,1),array(),'WATCHDOG_ERROR');
  453. return false;
  454. }
  455. return false;
  456. }
  457. /**
  458. * Provides a generic routine for selecting data from a Chado table
  459. *
  460. * Use this function to perform a simple select from any Chado table.
  461. *
  462. * @param $table
  463. * The name of the chado table for inserting
  464. * @param $columns
  465. * An array of column names
  466. * @param $values
  467. * An associative array containing the values for filtering the results. In the
  468. * case where multiple values for the same time are to be selected an additional
  469. * entry for the field should appear for each value
  470. * @param $options
  471. * An associative array of additional options where the key is the option
  472. * and the value is the value of that option.
  473. *
  474. * Additional Options Include:
  475. * - has_record
  476. * Set this argument to 'true' to have this function return a numeric
  477. * value for the number of recrods rather than the array of records. this
  478. * can be useful in 'if' statements to check the presence of particula records.
  479. * - return_sql
  480. * Set this to 'true' to have this function return an array where the first
  481. * element is the sql that would have been run and the second is an array of
  482. * arguments.
  483. * - case_insensitive_columns
  484. * An array of columns to do a case insensitive search on.
  485. * - regex_columns
  486. * An array of columns where the value passed in should be treated as a regular expression
  487. * - order_by
  488. * An associative array containing the column names of the table as keys
  489. * and the type of sort (i.e. ASC, DESC) as the values. The results in the
  490. * query will be sorted by the key values in the direction listed by the value
  491. *
  492. * @return
  493. * A database query result resource, FALSE if the query was not executed
  494. * correctly, or the number of records in the dataset if $has_record is set.
  495. *
  496. * Example usage:
  497. * @code
  498. * $columns = array('feature_id','name');
  499. * $values = array(
  500. * 'organism_id' => array(
  501. * 'genus' => 'Citrus',
  502. * 'species' => array('sinensis','clementina'),
  503. * ),
  504. * 'uniquename' => 'orange1.1g000034m.g',
  505. * 'type_id' => array (
  506. * 'cv_id' => array (
  507. * 'name' => 'sequence',
  508. * ),
  509. * 'name' => 'gene',
  510. * 'is_obsolete' => 0
  511. * ),
  512. * );
  513. * $result = tripal_core_chado_select('feature',$columns,$values);
  514. * @endcode
  515. * The above code selects a record from the feature table using the three fields
  516. * that uniquely identify a feature. The $columns array simply lists the columns
  517. * to select. The $values array is nested such that the organism is identified by
  518. * way of the organism_id foreign key constraint by specifying the genus and
  519. * species. The cvterm is also specified using its foreign key and the cv_id
  520. * for the cvterm is nested as well. In the example above, two different species
  521. * are allowed to match
  522. *
  523. * @ingroup tripal_chado_api
  524. */
  525. function tripal_core_chado_select($table,$columns,$values,$options = null){
  526. if (!is_array($options)) { $options = array(); }
  527. if (!$options['case_insensitive_columns']) { $options['case_insensitive_columns'] = array(); }
  528. if (!$options['regex_columns']) { $options['regex_columns'] = array(); }
  529. if (!$options['order_by']) { $options['order_by'] = array(); }
  530. if (!is_array($columns)){
  531. watchdog('tripal_core', 'the $columns argument for tripal_core_chado_select must be an array.');
  532. return false;
  533. }
  534. if (!is_array($values)){
  535. watchdog('tripal_core', 'the $values argument for tripal_core_chado_select must be an array.');
  536. return false;
  537. }
  538. // get the table description
  539. $table_desc = module_invoke_all('chado_'.$table.'_schema');
  540. $select = '';
  541. $from = '';
  542. $where = '';
  543. $args = array();
  544. foreach($values as $field => $value){
  545. $select[] = $field;
  546. if(is_array($value)){
  547. // if the user has specified multiple values for matching then this we
  548. // want to catch that and save them in our $where array, otherwise
  549. // we'll descend for a foreign key relationship
  550. if(array_values($value) === $value){
  551. $where[$field] = $value;
  552. } else {
  553. // select the value from the foreign key relationship for this value
  554. $foreign_options = array(
  555. 'regex_columns' => $options['regex_columns'],
  556. 'case_insensitive_columns' => $options['case_insensitive_columns']
  557. );
  558. $results = tripal_core_chado_get_foreign_key($table_desc,$field,$value, $foreign_options);
  559. if (sizeof($results) < 1) {
  560. // foreign key records are required
  561. // thus if none matched then return false and alert the admin through watchdog
  562. //watchdog('tripal_core',
  563. // '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',
  564. // array('!table' => $table,
  565. // '!columns' => '<pre>' . print_r($columns, TRUE) . '</pre>',
  566. // '!values' => '<pre>' . print_r($values, TRUE) . '</pre>',
  567. // '!field' => $field,
  568. // ),
  569. // WATCHDOG_WARNING);
  570. return false;
  571. } else {
  572. $where[$field] = $results;
  573. }
  574. }
  575. }
  576. else {
  577. //need to catch a 0 and make int if integer field
  578. if ($table_desc['fields'][$field]['type'] == 'int') {
  579. $where[$field][] = (int) $value;
  580. } else {
  581. $where[$field][] = $value;
  582. }
  583. }
  584. }
  585. // now build the SQL select statement
  586. if (empty($where)) {
  587. // sometimes want to select everything
  588. $sql = "SELECT " . implode(',',$columns) . " ";
  589. $sql .= "FROM {$table} ";
  590. } else {
  591. $sql = "SELECT " . implode(',',$columns) . " ";
  592. $sql .= "FROM {$table} ";
  593. $sql .= "WHERE ";
  594. foreach($where as $field => $value){
  595. if (count($value) > 1) {
  596. $sql .= "$field IN (".db_placeholders($value,'varchar').") AND ";
  597. foreach ($value as $v) { $args[] = $v; }
  598. } else {
  599. $operator = '=';
  600. if (in_array($field, $options['regex_columns'])) {
  601. $operator = '~*';
  602. }
  603. if (in_array($field, $options['case_insensitive_columns'])) {
  604. $sql .= "lower($field) $operator lower('%s') AND ";
  605. $args[] = $value[0];
  606. } else {
  607. $sql .= "$field $operator '%s' AND ";
  608. $args[] = $value[0];
  609. }
  610. }
  611. }
  612. $sql = substr($sql,0,-4); // get rid of the trailing 'AND '
  613. }
  614. // finally add any ordering of the results to the SQL statement
  615. if(count($options['order_by']) > 0){
  616. $sql .= " ORDER BY ";
  617. foreach($options['order_by'] as $field => $dir){
  618. $sql .= "$field $dir, ";
  619. }
  620. $sql = substr($sql,0,-2); // get rid of the trailing ', '
  621. }
  622. // if the caller has requested the SQL rather than the results...
  623. // which happens in the case of wanting to use the Drupal pager, then do so
  624. if($options['return_sql']){
  625. return array('sql'=> $sql, 'args' => $args);
  626. }
  627. $previous_db = tripal_db_set_active('chado'); // use chado database
  628. $resource = db_query($sql,$args);
  629. tripal_db_set_active($previous_db); // now use drupal database
  630. $results = array();
  631. while ($r = db_fetch_object($resource)) {
  632. $results[] = $r;
  633. }
  634. if(!$options['has_record']){
  635. return $results;
  636. } else{
  637. return count($results);
  638. }
  639. }
  640. /**
  641. * Gets the value of a foreign key relationship
  642. *
  643. * This function is used by tripal_core_chado_select, tripal_core_chado_insert,
  644. * and tripal_core_chado_update to iterate through the associate array of
  645. * values that gets passed to each of those routines. The values array
  646. * is nested where foreign key contraints are used to specify a value that. See
  647. * documentation for any of those functions for further information.
  648. *
  649. * @param $table_desc
  650. * A table description for the table with the foreign key relationship to be identified generated by
  651. * hook_chado_<table name>_schema()
  652. * @param $field
  653. * The field in the table that is the foreign key.
  654. * @param $values
  655. * An associative array containing the values
  656. * @param $options
  657. * An associative array of additional options where the key is the option
  658. * and the value is the value of that option. These options are passed on to tripal_core_chado_select.
  659. *
  660. * Additional Options Include:
  661. * - case_insensitive_columns
  662. * An array of columns to do a case insensitive search on.
  663. * - regex_columns
  664. * An array of columns where the value passed in should be treated as a regular expression
  665. *
  666. * @return
  667. * A string containg the results of the foreign key lookup, or FALSE if failed.
  668. *
  669. * Example usage:
  670. * @code
  671. *
  672. * $values = array(
  673. * 'genus' => 'Citrus',
  674. * 'species' => 'sinensis',
  675. * );
  676. * $value = tripal_core_chado_get_foreign_key('feature','organism_id',$values);
  677. *
  678. * @endcode
  679. * The above code selects a record from the feature table using the three fields
  680. * that uniquely identify a feature. The $columns array simply lists the columns
  681. * to select. The $values array is nested such that the organism is identified by
  682. * way of the organism_id foreign key constraint by specifying the genus and
  683. * species. The cvterm is also specified using its foreign key and the cv_id
  684. * for the cvterm is nested as well.
  685. *
  686. * @ingroup tripal_chado_api
  687. */
  688. function tripal_core_chado_get_foreign_key($table_desc,$field,$values, $options = null){
  689. if (!is_array($options)) { $options = array(); }
  690. if (!$options['case_insensitive_columns']) { $options['case_insensitive_columns'] = array(); }
  691. if (!$options['regex_columns']) { $options['regex_columns'] = array(); }
  692. // get the list of foreign keys for this table description and
  693. // iterate through those until we find the one we're looking for
  694. $fkeys = $table_desc['foreign keys'];
  695. if($fkeys){
  696. foreach($fkeys as $name => $def){
  697. if (is_array($def['table'])) {
  698. //foreign key was described 2X
  699. $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")).".";
  700. watchdog('tripal_core', $message);
  701. drupal_set_message($message,'error');
  702. continue;
  703. }
  704. $table = $def['table'];
  705. $columns = $def['columns'];
  706. // iterate through the columns of the foreign key relationship
  707. foreach($columns as $left => $right){
  708. // does the left column in the relationship match our field?
  709. if(strcmp($field,$left)==0){
  710. // the column name of the foreign key matches the field we want
  711. // so this is the right relationship. Now we want to select
  712. $select_cols = array($right);
  713. $result = tripal_core_chado_select($table,$select_cols,$values, $options);
  714. $fields = array();
  715. foreach ($result as $obj) {
  716. $fields[] = $obj->$right;
  717. }
  718. return $fields;
  719. }
  720. }
  721. }
  722. }
  723. else {
  724. // TODO: what do we do if we get to this point and we have a fk
  725. // relationship expected but we don't have any definition for one in the
  726. // table schema??
  727. $message = "There is no foreign key relationship defined for ".$field.".
  728. To define a foreign key relationship, determine the table this foreign
  729. key referrs to (<foreign table>) and then implement
  730. hook_chado_<foreign table>_schema(). See
  731. tripal_feature_chado_feature_schema for an example.";
  732. watchdog('tripal_core', $message);
  733. drupal_set_message($message,'error');
  734. }
  735. return false;
  736. }
  737. /**
  738. * Generates an object containing the full details of a record(s) in chado.
  739. *
  740. * This differs from the objects returned by tripal_core_chado_select in so far as all foreign key
  741. * relationships have been followed meaning you have more complete details. Thus this function
  742. * should be used whenever you need a full variable and tripal_core_chado_select should be used if
  743. * you only case about a few columns.
  744. *
  745. * @param $table
  746. * The name of the base table to generate a variable for
  747. * @param $values
  748. * A select values array that selects the records you want from the base table
  749. * (this has the same form as tripal_core_chado_select)
  750. * @param $base_options
  751. * An array containing options for the base table. For example, an
  752. * option of 'order_by' may be used to sort results in the base table
  753. * if more than one are returned. The options must be compatible with
  754. * the options accepted by the tripal_core_chado_select() function.
  755. * @return
  756. * Either an object (if only one record was selected from the base table)
  757. * or an array of objects (if more than one record was selected from the base table).
  758. *
  759. * Example Usage:
  760. * @code
  761. $values = array(
  762. 'name' => 'Medtr4g030710'
  763. );
  764. $features = tripal_core_generate_chado_var('feature', $values);
  765. * @endcode
  766. * This will return an object if there is only one feature with the name Medtr4g030710 or it will
  767. * return an array of feature objects if more than one feature has that name.
  768. *
  769. * Note to Module Designers: Fields can be excluded by default from these objects by implementing
  770. * one of the following hooks:
  771. * - hook_exclude_field_from_tablename_by_default (where tablename is the name of the table):
  772. * This hook allows you to add fields to be excluded on a per table basis. Simply implement
  773. * this hook to return an array of fields to be excluded. For example:
  774. * @code
  775. mymodule_exclude_field_from_feature_by_default() {
  776. return array('residues' => TRUE);
  777. }
  778. * @endcode
  779. * will ensure that feature.residues is ecluded from a feature object by default.
  780. * - hook_exclude_type_by_default:
  781. * This hook allows you to exclude fields from all tables that are of a given postgresql field
  782. * type. Simply implement this hook to return an array of postgresql types mapped to criteria.
  783. * Then all fields of that type where the criteria supplied returns TRUE will be excluded from
  784. * any table. Tokens available in criteria are &gt;field_value&lt; and &gt;field_name&lt; . For example:
  785. * @code
  786. mymodule_exclude_type_by_default() {
  787. return array('text' => 'length(&gt;field_value&lt; ) > 50');
  788. }
  789. * @endcode
  790. * 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.
  791. *
  792. * @ingroup tripal_chado_api
  793. */
  794. function tripal_core_generate_chado_var($table, $values, $base_options=array()) {
  795. // get description for the current table----------------------------------------------------------
  796. $table_desc = module_invoke_all('chado_'.$table.'_schema');
  797. $table_primary_key = $table_desc['primary key'][0];
  798. $table_columns = array_keys($table_desc['fields']);
  799. // Expandable fields without value needed for criteria--------------------------------------------
  800. $all->expandable_fields = array();
  801. if ($table_desc['referring_tables']) {
  802. $all->expandable_tables = $table_desc['referring_tables'];
  803. } else {
  804. $all->expandable_tables = array();
  805. }
  806. $all->expandable_nodes = array();
  807. // Get fields to be removed by name.................................
  808. $fields_to_remove = module_invoke_all('exclude_field_from_'.$table.'_by_default');
  809. foreach ($fields_to_remove as $field_name => $criteria) {
  810. //replace &gt;field_name&lt; with the current field name &
  811. $criteria = preg_replace('/&gt;field_name&lt; /', $field_name, $criteria);
  812. // if field_value needed we can't deal with this field yet
  813. if (preg_match('/&gt;field_value&lt; /', $criteria)) { break; }
  814. //if criteria then remove from query
  815. $success = drupal_eval('<?php return '.$criteria.'; ?>');
  816. // watchdog('tripal_core',
  817. // 'Evaluating criteria (%criteria) for field %field in tripal_core_generate_chado_var for %table evaluated to %success',
  818. // array('%table' => $table, '%criteria'=>$criteria, '%field' => $field_name, '%success'=>$success),
  819. // WATCHDOG_NOTICE
  820. // );
  821. if ($success) {
  822. unset($table_columns[array_search($field_name, $table_columns)]);
  823. unset($fields_to_remove[$field_name]);
  824. $all->expandable_fields[] = $table . '.' . $field_name;
  825. }
  826. }
  827. //Get fields to be removed by type................................
  828. $types_to_remove = module_invoke_all('exclude_type_by_default');
  829. $field_types = array();
  830. foreach ($table_desc['fields'] as $field_name => $field_array) {
  831. $field_types[$field_array['type']][] = $field_name;
  832. }
  833. foreach ($types_to_remove as $field_type => $criteria) {
  834. // if there are fields of that type to remove
  835. if (is_array($field_types[$field_type])) {
  836. //replace &gt;field_name&lt; with the current field name &
  837. $criteria = preg_replace('/&gt;field_name&lt; /', $field_name, $criteria);
  838. foreach ($field_types[$field_type] as $field_name) {
  839. // if field_value needed we can't deal with this field yet
  840. if (preg_match('/&gt;field_value&lt; /', $criteria)) {
  841. $fields_to_remove[$field_name] = $criteria;
  842. continue;
  843. }
  844. // if field_value needed we can't deal with this field yet
  845. if (preg_match('/&gt;field_value&lt; /', $criteria)) { break; }
  846. //if criteria then remove from query
  847. $success = drupal_eval('<?php return '.$criteria.'; ?>');
  848. // watchdog('tripal_core',
  849. // 'Evaluating criteria (%criteria) for field %field of $type in tripal_core_generate_chado_var for %table evaluated to %success',
  850. // array('%table'=>$table, '%criteria'=>$criteria, '%field'=>$field_name, '%type'=>$field_type, '%success'=>$success),
  851. // WATCHDOG_NOTICE
  852. // );
  853. if ($success) {
  854. unset($table_columns[array_search($field_name, $table_columns)]);
  855. $all->expandable_fields[] = $table . '.' . $field_name;
  856. }
  857. } //end of foreach field of that type
  858. }
  859. } //end of foreach type to be removed
  860. // get the values for the record in the current table---------------------------------------------
  861. $results = tripal_core_chado_select($table, $table_columns, $values,$base_options);
  862. if($results){
  863. foreach ($results as $key => $object) {
  864. // Add empty expandable_x arrays
  865. $object->expandable_fields = $all->expandable_fields;
  866. $object->expandable_tables = $all->expandable_tables;
  867. $object->expandable_nodes = $all->expandable_nodes;
  868. // add curent table
  869. $object->tablename = $table;
  870. // check if the current table maps to a node type-----------------------------------------------
  871. // if this table is connected to a node there will be a chado_tablename table in drupal
  872. if (db_table_exists('chado_'.$table)) {
  873. // that has a foreign key to this one ($table_desc['primary key'][0]
  874. // and to the node table (nid)
  875. $sql = "SELECT %s, nid FROM chado_%s WHERE %s=%d";
  876. $mapping = db_fetch_object(db_query(
  877. $sql,
  878. $table_primary_key,
  879. $table,
  880. $table_primary_key,
  881. $object->{$table_primary_key}
  882. ));
  883. if ($mapping->{$table_primary_key}) {
  884. $object->nid = $mapping->nid;
  885. $object->expandable_nodes[] = $table;
  886. }
  887. }
  888. // remove any fields where criteria need to be evalulated---------------------------------------
  889. foreach ($fields_to_remove as $field_name => $criteria) {
  890. if (!isset($object->{$field_name})) { break; }
  891. $criteria = preg_replace('/&gt;field_value&lt; /', $object->{$field_name}, $criteria);
  892. //if criteria then remove from query
  893. $success = drupal_eval('<?php return '.$criteria.'; ?>');
  894. // watchdog('tripal_core',
  895. // 'Evaluating criteria (%criteria) for field %field in tripal_core_generate_chado_var for %table evaluated to %success',
  896. // array('%table' => $table, '%criteria'=>$criteria, '%field' => $field_name, '%success'=>$success),
  897. // WATCHDOG_NOTICE
  898. // );
  899. if ($success) {
  900. unset($object->{$field_name});
  901. $object->expandable_fields[] = $table . '.' . $field_name;
  902. }
  903. }
  904. // recursively follow foreign key relationships nesting objects as we go------------------------
  905. if ($table_desc['foreign keys']) {
  906. foreach ($table_desc['foreign keys'] as $foreign_key_array) {
  907. $foreign_table = $foreign_key_array['table'];
  908. foreach ($foreign_key_array['columns'] as $foreign_key => $primary_key) {
  909. // Note: Foreign key is the field in the current table whereas primary_key is the field in
  910. // the table referenced by the foreign key
  911. //Dont do anything if the foreign key is empty
  912. if (empty($object->{$foreign_key})) {
  913. break;
  914. }
  915. // get the record from the foreign table
  916. $foreign_values = array($primary_key => $object->{$foreign_key});
  917. $foreign_object = tripal_core_generate_chado_var($foreign_table, $foreign_values);
  918. // add the foreign record to the current object in a nested manner
  919. $object->{$foreign_key} = $foreign_object;
  920. // Flatten expandable_x arrays so only in the bottom object
  921. if (is_array($object->{$foreign_key}->expandable_fields)) {
  922. $object->expandable_fields = array_merge(
  923. $object->expandable_fields,
  924. $object->{$foreign_key}->expandable_fields
  925. );
  926. unset($object->{$foreign_key}->expandable_fields);
  927. }
  928. if (is_array($object->{$foreign_key}->expandable_tables)) {
  929. $object->expandable_tables = array_merge(
  930. $object->expandable_tables,
  931. $object->{$foreign_key}->expandable_tables
  932. );
  933. unset($object->{$foreign_key}->expandable_tables);
  934. }
  935. if (is_array($object->{$foreign_key}->expandable_nodes)) {
  936. $object->expandable_nodes = array_merge(
  937. $object->expandable_nodes,
  938. $object->{$foreign_key}->expandable_nodes
  939. );
  940. unset($object->{$foreign_key}->expandable_nodes);
  941. }
  942. }
  943. }
  944. $results[$key] = $object;
  945. }
  946. }
  947. }
  948. // check only one result returned
  949. if (sizeof($results) == 1) {
  950. // add results to object
  951. return $results[0];
  952. } elseif (!empty($results)) {
  953. return $results;
  954. } else {
  955. // no results returned
  956. }
  957. }
  958. /**
  959. * Retrieves fields/tables/nodes that were excluded by default from a variable and adds them
  960. *
  961. * This function exists to allow tripal_core_generate_chado_var() to excldue some
  962. * fields/tables/nodes from the default form of a variable without making it extremely difficult for
  963. * the tripal admin to get at these variables if he/she wants them.
  964. *
  965. * @param $object
  966. * This must be an object generated using tripal_core_generate_chado_var()
  967. * @param $type
  968. * Must be one of 'field', 'table', 'node'. Indicates what is being expanded.
  969. * @param $to_expand
  970. * The name of the field/table/node to be expanded
  971. * @param $table_options
  972. * An array containing options for the base table. For example, an
  973. * option of 'order_by' may be used to sort results in the base table
  974. * if more than one are returned. The options must be compatible with
  975. * the options accepted by the tripal_core_chado_select() function.
  976. * @return
  977. * A chado object supplemented with the field/table/node requested to be expanded
  978. *
  979. * Example Usage:
  980. * @code
  981. // Get a chado object to be expanded
  982. $values = array(
  983. 'name' => 'Medtr4g030710'
  984. );
  985. $features = tripal_core_generate_chado_var('feature', $values);
  986. // Expand the organism node
  987. $feature = tripal_core_expand_chado_vars($feature, 'node', 'organism');
  988. // Expand the feature.residues field
  989. $feature = tripal_core_expand_chado_vars($feature, 'field', 'feature.residues');
  990. // Expand the feature properties (featureprop table)
  991. $feature = tripal_core_expand_chado_vars($feature, 'table', 'featureprop');
  992. * @endcode
  993. *
  994. * @ingroup tripal_chado_api
  995. */
  996. function tripal_core_expand_chado_vars ($object, $type, $to_expand,$table_options = array()) {
  997. $base_table = $object->tablename;
  998. // check to see if they are expanding an array of objects
  999. if (is_array($object)) {
  1000. foreach ($object as $index => $o) {
  1001. $object[$index] = tripal_core_expand_chado_vars($o,$type,$to_expand);
  1002. }
  1003. return $object;
  1004. }
  1005. switch ($type) {
  1006. case "field": //--------------------------------------------------------------------------------
  1007. if (preg_match('/(\w+)\.(\w+)/', $to_expand, $matches)) {
  1008. $tablename = $matches[1];
  1009. $fieldname = $matches[2];
  1010. $table_desc = module_invoke_all('chado_'.$tablename.'_schema');
  1011. $values = array();
  1012. foreach($table_desc['primary key'] as $key) {
  1013. $values[$key] = $object->{$key};
  1014. }
  1015. if ($base_table == $tablename) {
  1016. //get the field
  1017. $results = tripal_core_chado_select(
  1018. $tablename,
  1019. array($fieldname),
  1020. $values
  1021. );
  1022. $object->{$fieldname} = $results[0]->{$fieldname};
  1023. $object->expanded = $to_expand;
  1024. } else {
  1025. //We need to recurse -the field is in a nested object
  1026. foreach ((array) $object as $field_name => $field_value) {
  1027. if (is_object($field_value)) {
  1028. $object->{$field_name} = tripal_core_expand_chado_vars(
  1029. $field_value,
  1030. 'field',
  1031. $to_expand
  1032. );
  1033. }
  1034. } //end of for each field in the current object
  1035. }
  1036. } else {
  1037. watchdog(
  1038. 'tripal_core',
  1039. 'tripal_core_expand_chado_vars: Field (%field) not in the right format. It should be <tablename>.<fieldname>',
  1040. WATCHDOG_ERROR
  1041. );
  1042. }
  1043. break;
  1044. case "table": //--------------------------------------------------------------------------------
  1045. $foreign_table = $to_expand;
  1046. $foreign_table_desc = module_invoke_all('chado_'.$foreign_table.'_schema');
  1047. // If it's connected to the base table
  1048. if ($foreign_table_desc['foreign keys'][$base_table]) {
  1049. foreach ($foreign_table_desc['foreign keys'][$base_table]['columns'] as $left => $right) {
  1050. if (!$object->{$right}) { break; }
  1051. if (is_array($values)) {
  1052. $values = array_merge($values, array($left => $object->{$right}) );
  1053. } else {
  1054. $values = array($left => $object->{$right});
  1055. }
  1056. $foreign_object = tripal_core_generate_chado_var(
  1057. $foreign_table,
  1058. array($left => $object->{$right}),
  1059. $table_options
  1060. );
  1061. if ($foreign_object) {
  1062. // in the case where the a foreign key relationships exists more
  1063. // than once with the same table we want to alter the
  1064. // array structure
  1065. if(count($foreign_table_desc['foreign keys'][$base_table]['columns']) > 1){
  1066. $object->{$foreign_table}->{$left} = $foreign_object;
  1067. $object->expanded = $to_expand;
  1068. } else {
  1069. $object->{$foreign_table} = $foreign_object;
  1070. $object->expanded = $to_expand;
  1071. }
  1072. }
  1073. }
  1074. } else {
  1075. //We need to recurse -the table has a relationship to one of the nested objects
  1076. foreach ((array) $object as $field_name => $field_value) {
  1077. // if we have a nested object ->expand the table in it
  1078. if (is_object($field_value)) {
  1079. $object->{$field_name} = tripal_core_expand_chado_vars(
  1080. $field_value,
  1081. 'table',
  1082. $foreign_table
  1083. );
  1084. }
  1085. }
  1086. }
  1087. break;
  1088. case "node": //---------------------------------------------------------------------------------
  1089. //if the node to be expanded is for our base table, then just expand it
  1090. if ($object->tablename == $to_expand) {
  1091. $node = node_load($object->nid);
  1092. if ($node) {
  1093. $object->expanded = $to_expand;
  1094. $node->expandable_fields = $object->expandable_fields;
  1095. unset($object->expandable_fields);
  1096. $node->expandable_tables = $object->expandable_tables;
  1097. unset($object->expandable_tables);
  1098. $node->expandable_nodes = $object->expandable_nodes;
  1099. unset($object->expandable_nodes);
  1100. $node->{$base_table} = $object;
  1101. $object = $node;
  1102. } else {
  1103. watchdog(
  1104. 'tripal_core',
  1105. 'tripal_core_expand_chado_vars: No node matches the nid (%nid) supplied.',
  1106. array('%nid'=>$object->nid),
  1107. WATCHDOG_ERROR
  1108. );
  1109. } //end of if node
  1110. } else {
  1111. //We need to recurse -the node to expand is one of the nested objects
  1112. foreach ((array) $object as $field_name => $field_value) {
  1113. if (is_object($field_value)) {
  1114. $object->{$field_name} = tripal_core_expand_chado_vars(
  1115. $field_value,
  1116. 'node',
  1117. $to_expand
  1118. );
  1119. }
  1120. } //end of for each field in the current object
  1121. }
  1122. break;
  1123. default:
  1124. watchdog('tripal_core',
  1125. 'tripal_core_expand_chado_vars: Unrecognized type (%type). Should be one of "field", "table", "node".',
  1126. array('%type'=>$type),
  1127. WATCHDOG_ERROR
  1128. );
  1129. return FALSE;
  1130. }
  1131. //move extended array downwards-------------------------------------------------------------------
  1132. if (!$object->expanded) {
  1133. //if there's no extended field then go hunting for it
  1134. foreach ( (array)$object as $field_name => $field_value) {
  1135. if (is_object($field_value)) {
  1136. if (isset($field_value->expanded)) {
  1137. $object->expanded = $field_value->expanded;
  1138. unset($field_value->expanded);
  1139. }
  1140. }
  1141. }
  1142. }
  1143. //try again becasue now we might have moved it down
  1144. if ($object->expanded) {
  1145. $expandable_name = 'expandable_'.$type.'s';
  1146. if ($object->{$expandable_name}) {
  1147. $key_to_remove = array_search($object->expanded, $object->{$expandable_name});
  1148. unset($object->{$expandable_name}[$key_to_remove]);
  1149. unset($object->expanded);
  1150. } else {
  1151. // if there is an expandable array then we've reached the base object
  1152. // if we get here and don't have anything expanded then something went wrong
  1153. // watchdog(
  1154. // 'tripal_core',
  1155. // 'tripal_core_expand_chado_vars: Unable to expand the %type %to_expand',
  1156. // array('%type'=>$type, '%to_expand'=>$to_expand),
  1157. // WATCHDOG_ERROR
  1158. // );
  1159. } //end of it we've reached the base object
  1160. }
  1161. return $object;
  1162. }
  1163. /**
  1164. * Implements hook_exclude_type_by_default()
  1165. *
  1166. * This hooks allows fields of a specified type that match a specified criteria to be excluded by
  1167. * default from any table when tripal_core_generate_chado_var() is called. Keep in mind that if
  1168. * fields are excluded by default they can always be expanded at a later date using
  1169. * tripal_core_expand_chado_vars().
  1170. *
  1171. * Criteria are php strings that evaluate to either TRUE or FALSE. These strings are evaluated using
  1172. * drupal_eval() which suppresses syntax errors and throws watchdog entries of type php. There are
  1173. * also watchdog entries of type tripal_core stating the exact criteria evaluated. Criteria can
  1174. * contain the following tokens:
  1175. * - &gt;field_name&lt;
  1176. * Replaced by the name of the field to be excluded
  1177. * - &gt;field_value&lt;
  1178. * Replaced by the value of the field in the current record
  1179. * Also keep in mind that if your criteria doesn't contain the &gt;field_value&lt; token then it will be
  1180. * evaluated before the query is executed and if the field is excluded it won't be included in the
  1181. * query.
  1182. *
  1183. * @return
  1184. * An array of type => criteria where the type is excluded if the criteria evaluates to TRUE
  1185. *
  1186. * @ingroup tripal_chado_api
  1187. */
  1188. function tripal_core_exclude_type_by_default() {
  1189. return array('text' => "strlen('&gt;field_value&lt; ') > 100");
  1190. }
  1191. /**
  1192. * Implements hook_exclude_field_from_<tablename>_by_default()
  1193. *
  1194. * This hooks allows fields from a specified table that match a specified criteria to be excluded by
  1195. * default from any table when tripal_core_generate_chado_var() is called. Keep in mind that if
  1196. * fields are excluded by default they can always be expanded at a later date using
  1197. * tripal_core_expand_chado_vars().
  1198. *
  1199. * Criteria are php strings that evaluate to either TRUE or FALSE. These strings are evaluated using
  1200. * drupal_eval() which suppresses syntax errors and throws watchdog entries of type php. There are
  1201. * also watchdog entries of type tripal_core stating the exact criteria evaluated. Criteria can
  1202. * contain the following tokens:
  1203. * - &gt;field_name&lt;
  1204. * Replaced by the name of the field to be excluded
  1205. * - &gt;field_value&lt;
  1206. * Replaced by the value of the field in the current record
  1207. * Also keep in mind that if your criteria doesn't contain the &gt;field_value&lt; token then it will be
  1208. * evaluated before the query is executed and if the field is excluded it won't be included in the
  1209. * query.
  1210. *
  1211. * @return
  1212. * An array of type => criteria where the type is excluded if the criteria evaluates to TRUE
  1213. *
  1214. * @ingroup tripal_chado_api
  1215. */
  1216. function tripal_core_exclude_field_from_feature_by_default() {
  1217. return array();
  1218. }
  1219. /**
  1220. * Use this function instead of db_query() to avoid switching databases
  1221. * when making query to the chado database
  1222. */
  1223. function chado_query($sql) {
  1224. $args = func_get_args();
  1225. array_shift($args);
  1226. $sql = db_prefix_tables($sql);
  1227. if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
  1228. $args = $args[0];
  1229. }
  1230. _db_query_callback($args, TRUE);
  1231. $sql = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $sql);
  1232. $previous_db = tripal_db_set_active('chado');
  1233. $results = _db_query($sql);
  1234. tripal_db_set_active($previous_db);
  1235. return $results;
  1236. }
  1237. /**
  1238. * Get chado id for a node. E.g, if you want to get 'analysis_id' from the
  1239. * 'analysis' table for a synced 'chado_analysis' node, use:
  1240. * $analysis_id = chado_get_id_for_node ('analysis', $node)
  1241. * Likewise,
  1242. * $organism_id = chado_get_id_for_node ('organism', $node)
  1243. * $feature_id = chado_get_id_for_node ('feature', $node)
  1244. */
  1245. function chado_get_id_for_node ($table, $node) {
  1246. return db_result(db_query("SELECT $table"."_id FROM {chado_".$table."} WHERE nid = $node->nid"));
  1247. }
  1248. /**
  1249. * Get node id for a chado feature/organism/analysis. E.g, if you want to
  1250. * get the node id for an analysis, use:
  1251. * $nid = chado_get_node_id ('analysis', $analysis_id)
  1252. * Likewise,
  1253. * $nid = chado_get_node_id ('organism', $organism_id)
  1254. * $nid = chado_get_node_id ('feature', $feature_id)
  1255. */
  1256. function chado_get_node_id ($table, $id) {
  1257. return db_result(db_query("SELECT nid FROM {chado_".$table."} WHERE $table"."_id = $id"));
  1258. }
  1259. /**
  1260. * Retrieve a property for a given base table record
  1261. *
  1262. * @param $basetable
  1263. * The base table for which the property should be retrieved. Thus to retrieve a property
  1264. * for a feature the basetable=feature and property is retrieved from featureprop
  1265. * @param $record_id
  1266. * The primary key of the basetable to retrieve properties for. This should be in integer.
  1267. * @param $property
  1268. * The cvterm name describing the type of properties to be retrieved
  1269. * @param $cv_name
  1270. * The name of the cv that the above cvterm is part of
  1271. *
  1272. * @return
  1273. * A chado variable with the specified properties expanded
  1274. *
  1275. * @ingroup tripal_chado_api
  1276. */
  1277. function tripal_core_get_property($basetable, $record_id, $property, $cv_name){
  1278. // get the foreign key for this property table
  1279. $table_desc = module_invoke_all('chado_'.$basetable.'prop_schema');
  1280. $fkcol = key($table_desc['foreign keys'][$basetable]['columns']);
  1281. // construct the array of values to be inserted
  1282. $values = array (
  1283. $fkcol => $record_id,
  1284. 'type_id' => array (
  1285. 'cv_id' => array (
  1286. 'name' => $cv_name,
  1287. ),
  1288. 'name' => $property,
  1289. 'is_obsolete' => 0
  1290. ),
  1291. );
  1292. $results = tripal_core_generate_chado_var($basetable.'prop',$values);
  1293. $results = tripal_core_expand_chado_vars($results,'field',$basetable.'prop.value');
  1294. return $results;
  1295. }
  1296. /**
  1297. * Insert a property for a given basetable record
  1298. *
  1299. * @param $basetable
  1300. * The base table for which the property should be inserted. Thus to insert a property
  1301. * for a feature the basetable=feature and property is inserted into featureprop
  1302. * @param $record_id
  1303. * The primary key of the basetable to insert a property for. This should be in integer.
  1304. * @param $property
  1305. * The cvterm name describing the type of properties to be inserted
  1306. * @param $cv_name
  1307. * The name of the cv that the above cvterm is part of
  1308. * @param $value
  1309. * The value of the property to be inserted (can be empty)
  1310. * @param $update_if_present
  1311. * A boolean indicating whether an existing record should be updated or an error thrown
  1312. *
  1313. * @return
  1314. * Return True on Insert/Update and False otherwise
  1315. *
  1316. * @ingroup tripal_chado_api
  1317. */
  1318. function tripal_core_insert_property($basetable, $record_id, $property,
  1319. $cv_name, $value, $update_if_present = 0)
  1320. {
  1321. // first see if the property already exists, if so we can't insert
  1322. $prop = tripal_core_get_property($basetable,$record_id,$property,$cv_name);
  1323. if(count($prop)>0){
  1324. if($update_if_present){
  1325. return tripal_core_update_property($basetable,$record_id,$property,$cv_name,$value) ;
  1326. } else {
  1327. return FALSE;
  1328. }
  1329. }
  1330. // get the foreign key for this property table
  1331. $table_desc = module_invoke_all('chado_'.$basetable.'prop_schema');
  1332. $fkcol = key($table_desc['foreign keys'][$basetable]['columns']);
  1333. // construct the array of values to be inserted
  1334. $values = array (
  1335. $fkcol => $record_id,
  1336. 'type_id' => array (
  1337. 'cv_id' => array (
  1338. 'name' => $cv_name,
  1339. ),
  1340. 'name' => $property,
  1341. 'is_obsolete' => 0
  1342. ),
  1343. 'value' => $value,
  1344. 'rank' => 0,
  1345. );
  1346. return tripal_core_chado_insert($basetable.'prop',$values);
  1347. }
  1348. /**
  1349. * Update a property for a given basetable record
  1350. *
  1351. * @param $basetable
  1352. * The base table for which the property should be updated. Thus to update a property
  1353. * for a feature the basetable=feature and property is updated in featureprop
  1354. * @param $record_id
  1355. * The primary key of the basetable to update a property for. This should be in integer.
  1356. * @param $property
  1357. * The cvterm name describing the type of property to be updated
  1358. * @param $cv_name
  1359. * The name of the cv that the above cvterm is part of
  1360. * @param $value
  1361. * The value of the property to be inserted (can be empty)
  1362. * @param $insert_if_missing
  1363. * A boolean indicating whether a record should be inserted if one doesn't exist to update
  1364. *
  1365. * Note: The property to be updated is select via theu nique combination of $record_id and
  1366. * $property and then it is updated with the supplied value
  1367. *
  1368. * @return
  1369. * Return True on Update/Insert and False otherwise
  1370. *
  1371. * @ingroup tripal_chado_api
  1372. */
  1373. function tripal_core_update_property($basetable, $record_id,$property,$cv_name,
  1374. $value,$insert_if_missing = 0)
  1375. {
  1376. // first see if the property is missing (we can't update a missing property
  1377. $prop = tripal_core_get_property($basetable,$record_id,$property,$cv_name);
  1378. if(count($prop)==0){
  1379. if($insert_if_missing){
  1380. return tripal_core_insert_property($basetable,$record_id,$property,$cv_name,$value);
  1381. } else {
  1382. return FALSE;
  1383. }
  1384. }
  1385. // get the foreign key for this property table
  1386. $table_desc = module_invoke_all('chado_'.$basetable.'prop_schema');
  1387. $fkcol = key($table_desc['foreign keys'][$basetable]['columns']);
  1388. // construct the array that will match the exact record to update
  1389. $match = array (
  1390. $fkcol => $record_id,
  1391. 'type_id' => array (
  1392. 'cv_id' => array (
  1393. 'name' => $cv_name,
  1394. ),
  1395. 'name' => $property,
  1396. ),
  1397. );
  1398. // construct the array of values to be updated
  1399. $values = array (
  1400. 'value' => $value,
  1401. );
  1402. return tripal_core_chado_update($basetable.'prop',$match,$values);
  1403. }
  1404. /**
  1405. * Deletes a property for a given basetable record
  1406. *
  1407. * @param $basetable
  1408. * The base table for which the property should be deleted. Thus to deleted a property
  1409. * for a feature the basetable=feature and property is deleted from featureprop
  1410. * @param $record_id
  1411. * The primary key of the basetable to delete a property for. This should be in integer.
  1412. * @param $property
  1413. * The cvterm name describing the type of property to be deleted
  1414. * @param $cv_name
  1415. * The name of the cv that the above cvterm is part of
  1416. *
  1417. * Note: The property to be deleted is select via theu nique combination of $record_id and $property
  1418. *
  1419. * @return
  1420. * Return True on Delete and False otherwise
  1421. *
  1422. * @ingroup tripal_chado_api
  1423. */
  1424. function tripal_core_delete_property($basetable, $record_id,$property,$cv_name){
  1425. // get the foreign key for this property table
  1426. $table_desc = module_invoke_all('chado_'.$basetable.'prop_schema');
  1427. $fkcol = key($table_desc['foreign keys'][$basetable]['columns']);
  1428. // construct the array that will match the exact record to update
  1429. $match = array (
  1430. $fkcol => $record_id,
  1431. 'type_id' => array (
  1432. 'cv_id' => array (
  1433. 'name' => $cv_name,
  1434. ),
  1435. 'name' => $property,
  1436. ),
  1437. );
  1438. return tripal_core_chado_delete($basetable.'prop',$match);
  1439. }
  1440. /**
  1441. * This function is typically used in the '.install' file for a Tripal module
  1442. * Each module should call this function during installation to create
  1443. * the module data directory which is sites/default/files/tripal/[module_name]
  1444. * for default Drupal settings. This directory can then be used by the module
  1445. * for storing files.
  1446. *
  1447. * @param $module_name
  1448. * the name of the module being installed.
  1449. *
  1450. * @returns
  1451. * nothing
  1452. *
  1453. * @ingroup tripal_files_api
  1454. */
  1455. function tripal_create_moddir($module_name){
  1456. // make the data directory for this module
  1457. $data_dir = file_directory_path() . "/tripal/$module_name";
  1458. if(!file_check_directory($data_dir,FILE_CREATE_DIRECTORY|FILE_MODIFY_PERMISSIONS)){
  1459. $message = "Cannot create directory $data_dir. This module may not ".
  1460. "behave correctly without this directory. Please create ".
  1461. "the directory manually or fix the problem and reinstall.";
  1462. drupal_set_message($message,'error');
  1463. watchdog('tripal_core',$message,array(),WATCHDOG_ERROR);
  1464. }
  1465. }
  1466. /**
  1467. * Each Tripal module has a unique data directory which was creatd using the
  1468. * tripal_create_moddir function during installation. This function
  1469. * retrieves the directory path.
  1470. *
  1471. * @param $module_name
  1472. * The name of the module
  1473. *
  1474. * @returns
  1475. * The path within the Drupal installation where the data directory resides
  1476. * @ingroup tripal_files_api
  1477. */
  1478. function tripal_get_moddir($module_name){
  1479. $data_dir = file_directory_path() . "/tripal/$module_name";
  1480. return $data_dir;
  1481. }
  1482. /**
  1483. * Set the Tripal Database
  1484. *
  1485. * The tripal_db_set_active function is used to prevent namespace collisions
  1486. * when chado and drupal are installed in the same database but in different
  1487. * schemas. It is also used for backwards compatibility with older versions
  1488. * of tripal or in cases where chado is located outside of the Drupal database.
  1489. *
  1490. * @ingroup tripal_chado_api
  1491. */
  1492. function tripal_db_set_active($dbname){
  1493. global $db_url, $db_type;
  1494. $chado_exists = 0;
  1495. // only postgres can support search paths. So if this is MysQL then
  1496. // just run the normal tripal_db_set_active function.
  1497. if(strcmp($db_type,'pgsql')==0){
  1498. // if the 'chado' database is in the $db_url variable then chado is
  1499. // not in the same Drupal database
  1500. if(is_array($db_url)){
  1501. if(isset($db_url[$dbname])){
  1502. return db_set_active($dbname);
  1503. }
  1504. }
  1505. // check to make sure the chado schema exists
  1506. $sql = "select nspname from pg_catalog.pg_namespace where nspname = 'chado'";
  1507. if(db_fetch_object(db_query($sql))){
  1508. $chado_exists = 1;
  1509. }
  1510. // here we make the assumption that the default database schema is
  1511. // 'public'. This will most likely always be the case but if not,
  1512. // then this code will break
  1513. if($chado_exists && strcmp($dbname,'chado')==0){
  1514. db_query("set search_path to %s",'chado,public');
  1515. return 'public,chado';
  1516. }
  1517. elseif($chado_exists) {
  1518. db_query("set search_path to %s",'public,chado');
  1519. return 'chado,public';
  1520. }
  1521. else {
  1522. return db_set_active($dbname);
  1523. }
  1524. }
  1525. else return db_set_active($dbname);
  1526. }
  1527. /**
  1528. * Purpose: Get max rank for a given set of criteria
  1529. * This function was developed with the many property tables in chado in mind
  1530. *
  1531. * @param $tablename
  1532. * The name of the chado table you want to select the max rank from this table must contain a
  1533. * rank column of type integer
  1534. * @param $where_options
  1535. * where options should include the id and type for that table to correctly
  1536. * group a set of records together where the only difference are the value and rank
  1537. * @code
  1538. * array(
  1539. * <column_name> => array(
  1540. * 'type' => <type of column: INT/STRING>,
  1541. * 'value' => <the value you want to filter on>,
  1542. * 'exact' => <if TRUE use =; if FALSE use ~>,
  1543. * )
  1544. * )
  1545. * @endcode
  1546. * @return the maximum rank
  1547. *
  1548. * @ingroup tripal_chado_api
  1549. */
  1550. function tripal_get_max_chado_rank ($tablename, $where_options) {
  1551. $where= array();
  1552. //generate the where clause from supplied options
  1553. // the key is the column name
  1554. foreach ($where_options as $key => $val_array) {
  1555. if (preg_match('/INT/', $val_array['type'])) {
  1556. $where[] = $key."=".$val_array['value'];
  1557. } else {
  1558. if ($val_array['exact']) { $operator='='; }
  1559. else { $operator='~'; }
  1560. $where[] = $key.$operator."'".$val_array['value']."'";
  1561. }
  1562. }
  1563. $previous_db = tripal_db_set_active('chado');
  1564. $result = db_fetch_object(db_query(
  1565. "SELECT max(rank) as max_rank, count(rank) as count FROM %s WHERE %s",
  1566. $tablename,
  1567. implode(' AND ',$where)
  1568. ));
  1569. tripal_db_set_active($previous_db);
  1570. //drupal_set_message("Max Rank Query=SELECT max(rank) as max_rank, count(rank) as count FROM ".$tablename." WHERE ".implode(' AND ',$where));
  1571. if ($result->count > 0) {
  1572. return $result->max_rank;
  1573. } else {
  1574. return -1;
  1575. }
  1576. }