tripal_core.api.inc 75 KB

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