tripal_chado.cv.api.inc 54 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405140614071408140914101411141214131414141514161417141814191420142114221423142414251426142714281429143014311432143314341435143614371438143914401441144214431444144514461447144814491450145114521453145414551456145714581459146014611462146314641465146614671468146914701471147214731474147514761477147814791480148114821483148414851486148714881489149014911492149314941495149614971498149915001501150215031504150515061507150815091510151115121513151415151516151715181519152015211522152315241525152615271528152915301531153215331534153515361537153815391540154115421543154415451546154715481549155015511552155315541555155615571558155915601561156215631564156515661567156815691570157115721573157415751576157715781579158015811582158315841585158615871588158915901591159215931594159515961597159815991600160116021603160416051606160716081609161016111612161316141615161616171618161916201621162216231624162516261627162816291630163116321633163416351636163716381639164016411642164316441645164616471648164916501651
  1. <?php
  2. /**
  3. * @file
  4. * This module provides a set of functions to simplify working with
  5. * controlled vocabularies.
  6. */
  7. /**
  8. * @defgroup tripal_chado_api Controlled Vocabulary API
  9. * @ingroup tripal_api
  10. * This module provides a set of functions to simplify working with
  11. * controlled vocabularies. Most of the API functions deal with retrieving
  12. * terms or their parent vocabularies.
  13. *
  14. * However, the API also supports
  15. * generation of trees for browsing a vocabulary as well as generation of
  16. * pie graphs for display of hierarchical counts of terms. Version 0.3b of
  17. * Tripal provides a feature browser and a feature summary chart uses
  18. * the API functions provided here. But in general charts and trees can be
  19. * created for any controlled vocabulary.
  20. *
  21. */
  22. /**
  23. * Retrieves a chado controlled vocabulary variable
  24. *
  25. * @param $identifier
  26. * An array with the key stating what the identifier is. Supported keys (only on of the
  27. * following unique keys is required):
  28. * - cv_id: the chado cv.cv_id primary key
  29. * - name: the chado cv.name field (assume unique)
  30. * @param $options
  31. * An array of options. Supported keys include:
  32. * - Any keys supported by chado_generate_var(). See that function definition for
  33. * additional details.
  34. *
  35. * NOTE: the $identifier parameter can really be any array similar to $values passed into
  36. * chado_select_record(). It should fully specify the cv record to be returned.
  37. *
  38. * @return
  39. * If unique values were passed in as an identifier then an object describing the cv
  40. * will be returned (will be a chado variable from chado_generate_var()). Otherwise,
  41. * FALSE will be returned.
  42. *
  43. * @ingroup tripal_chado_api
  44. */
  45. function tripal_get_cv($identifiers, $options = array()) {
  46. // Set Defaults
  47. if (!isset($options['include_fk'])) {
  48. // Tells chado_generate_var not to follow any foreign keys
  49. $options['include_fk'] = array();
  50. }
  51. // Error Checking of parameters
  52. if (!is_array($identifiers)) {
  53. tripal_report_error(
  54. 'tripal_chado_api',
  55. TRIPAL_ERROR,
  56. "tripal_get_cv: The identifier passed in is expected to be an array with the key
  57. matching a column name in the cv table (ie: cv_id or name). You passed in %identifier.",
  58. array(
  59. '%identifier'=> print_r($identifiers, TRUE)
  60. )
  61. );
  62. }
  63. elseif (empty($identifiers)) {
  64. tripal_report_error(
  65. 'tripal_chado_api',
  66. TRIPAL_ERROR,
  67. "tripal_get_cv: You did not pass in anything to identify the cv you want. The identifier
  68. is expected to be an array with the key matching a column name in the cv table
  69. (ie: cv_id or name). You passed in %identifier.",
  70. array(
  71. '%identifier'=> print_r($identifiers, TRUE)
  72. )
  73. );
  74. }
  75. // Try to get the cv
  76. $cv = chado_generate_var(
  77. 'cv',
  78. $identifiers,
  79. $options
  80. );
  81. // Ensure the cv is singular. If it's an array then it is not singular
  82. if (is_array($cv)) {
  83. tripal_report_error(
  84. 'tripal_chado_api',
  85. TRIPAL_ERROR,
  86. "tripal_get_cv: The identifiers you passed in were not unique. You passed in %identifier.",
  87. array(
  88. '%identifier'=> print_r($identifiers, TRUE)
  89. )
  90. );
  91. }
  92. // Report an error if $cv is FALSE since then chado_generate_var has failed
  93. elseif ($cv === FALSE) {
  94. tripal_report_error(
  95. 'tripal_chado_api',
  96. TRIPAL_ERROR,
  97. "tripal_get_cv: chado_generate_var() failed to return a cv based on the identifiers
  98. you passed in. You should check that your identifiers are correct, as well as, look
  99. for a chado_generate_var error for additional clues. You passed in %identifier.",
  100. array(
  101. '%identifier'=> print_r($identifiers, TRUE)
  102. )
  103. );
  104. }
  105. // Else, as far we know, everything is fine so give them their cv :)
  106. else {
  107. return $cv;
  108. }
  109. }
  110. /**
  111. * Create an options array to be used in a form element which provides a
  112. * list of all chado cvs
  113. *
  114. * @return
  115. * An array(cv_id => name) for each cv in the chado cv table
  116. *
  117. * @ingroup tripal_chado_api
  118. */
  119. function tripal_get_cv_select_options() {
  120. $results = chado_select_record('cv', array('cv_id', 'name'), array(), array('order_by' => array('name' => 'ASC')));
  121. $options = array();
  122. $options[] = 'Select a Vocabulary';
  123. foreach ($results as $r) {
  124. $options[$r->cv_id] = $r->name;
  125. }
  126. return $options;
  127. }
  128. /**
  129. * Retrieves a chado controlled vocabulary term variable
  130. *
  131. * @param $identifier
  132. * An array apropriate for use with the chado_generate_var for uniquely
  133. * identifying a cvterm record. Alternativley, there are also some specially
  134. * handled keys. They are:
  135. * - id: an ID for the term of the for [dbname]:[accession], where [dbname]
  136. * is the short name of the vocabulary and accession is the unique ID.
  137. * - cv_id: an integer indicating the cv_id or an array with 'name' => the
  138. * name of the cv.
  139. * - synonym: an array with 'name' => the name of the synonym of the cvterm
  140. * you want returned; 'cv_id' => the cv_id of the synonym; 'cv_name' =>
  141. * the name of the cv of the synonym
  142. * - property: An array/object describing the property to select records
  143. * for. It should at least have either a type_name (if unique across cvs)
  144. * or type_id. Other supported keys include: cv_id/cv_name (of the type),
  145. * value and rank
  146. * @param $options
  147. * An array of options. Supported keys include:
  148. * - Any keys supported by chado_generate_var(). See that function
  149. * definition for additional details.
  150. *
  151. * NOTE: the $identifier parameter can really be any array similar to $values
  152. * passed into chado_select_record(). It should fully specify the cvterm
  153. * record to be returned.
  154. *
  155. * @return
  156. * If unique values were passed in as an identifier then an object describing
  157. * the cvterm will be returned (will be a chado variable from
  158. * chado_generate_var()). Otherwise, FALSE will be returned.
  159. *
  160. * @ingroup tripal_cv_api
  161. */
  162. function tripal_get_cvterm($identifiers, $options = array()) {
  163. // Set Defaults
  164. if (!isset($options['include_fk'])) {
  165. // Tells chado_generate_var to only get the cv
  166. $options['include_fk'] = array('cv_id' => TRUE);
  167. }
  168. // Error Checking of parameters
  169. if (!is_array($identifiers)) {
  170. tripal_report_error('tripal_cv_api', TRIPAL_ERROR,
  171. "tripal_get_cvterm: The identifier passed in is expected to be an array with the key
  172. matching a column name in the cvterm table (ie: cvterm_id or name). You passed in %identifier.",
  173. array('%identifier'=> print_r($identifiers, TRUE))
  174. );
  175. }
  176. elseif (empty($identifiers)) {
  177. tripal_report_error('tripal_cv_api', TRIPAL_ERROR,
  178. "tripal_get_cvterm: You did not pass in anything to identify the cvterm you want. The identifier
  179. is expected to be an array with the key matching a column name in the cvterm table
  180. (ie: cvterm_id or name). You passed in %identifier.",
  181. array('%identifier'=> print_r($identifiers, TRUE))
  182. );
  183. }
  184. // If synonym was passed in, then process this first before calling chado_generate_var()
  185. if (isset($identifiers['synonym'])) {
  186. $synonym = $identifiers['synonym']['name'];
  187. $values = array('synonym' => $synonym);
  188. if (isset($identifiers['synonym']['cv_id'])) {
  189. $values['cvterm_id'] = array('cv_id' => $identifiers['synonym']['cv_id']);
  190. }
  191. if (isset($identifiers['synonym']['cv_name'])) {
  192. $values['cvterm_id'] = array('cv_id' => array('name' => $identifiers['synonym']['cv_name']));
  193. }
  194. $options = array(
  195. 'case_insensitive_columns' => array('name')
  196. );
  197. $result = chado_select_record('cvtermsynonym', array('cvterm_id'), $values, $options);
  198. // if the synonym doens't exist or more than one record is returned then return false
  199. if (count($result) == 0) {
  200. return FALSE;
  201. }
  202. if (count($result) > 1) {
  203. return FALSE;
  204. }
  205. $identifiers = array('cvterm_id' => $result[0]->cvterm_id);
  206. }
  207. // If one of the identifiers is property then use chado_get_record_with_property()
  208. if (isset($identifiers['property'])) {
  209. $property = $identifiers['property'];
  210. unset($identifiers['property']);
  211. $cvterm = chado_get_record_with_property(
  212. array('table' => 'cvterm', 'base_records' => $identifiers),
  213. array('type_name' => $property),
  214. $options
  215. );
  216. }
  217. if (isset($identifiers['id'])) {
  218. list($db_name, $accession) = preg_split('/:/', $identifiers['id']);
  219. $cvterm = chado_generate_var('cvterm',array(
  220. 'dbxref_id' => array(
  221. 'db_id' => array(
  222. 'name' => $db_name,
  223. ),
  224. 'accession' => $accession,
  225. )
  226. ));
  227. }
  228. // Else we have a simple case and we can just use chado_generate_var to get the cvterm
  229. else {
  230. // Try to get the cvterm
  231. $cvterm = chado_generate_var('cvterm', $identifiers, $options);
  232. }
  233. // Ensure the cvterm is singular. If it's an array then it is not singular
  234. if (is_array($cvterm)) {
  235. tripal_report_error(
  236. 'tripal_cv_api',
  237. TRIPAL_ERROR,
  238. "tripal_get_cvterm: The identifiers you passed in were not unique. You passed in %identifier.",
  239. array(
  240. '%identifier'=> print_r($identifiers, TRUE)
  241. )
  242. );
  243. }
  244. // Report an error if $cvterm is FALSE since then chado_generate_var has failed
  245. elseif ($cvterm === FALSE) {
  246. tripal_report_error(
  247. 'tripal_cv_api',
  248. TRIPAL_ERROR,
  249. "tripal_get_cvterm: chado_generate_var() failed to return a cvterm based on the identifiers
  250. you passed in. You should check that your identifiers are correct, as well as, look
  251. for a chado_generate_var error for additional clues. You passed in %identifier.",
  252. array(
  253. '%identifier'=> print_r($identifiers, TRUE)
  254. )
  255. );
  256. }
  257. // Else, as far we know, everything is fine so give them their cvterm :)
  258. else {
  259. return $cvterm;
  260. }
  261. }
  262. /**
  263. * Create an options array to be used in a form element
  264. * which provides a list of all chado cvterms
  265. *
  266. * @param $cv_id
  267. * The chado cv_id; only cvterms with the supplied cv_id will be returned
  268. * @param $rel_type
  269. * Set to TRUE if the terms returned should only be relationship types in
  270. * the vocabulary. This is useful for creating drop-downs of terms
  271. * used for relationship linker tables.
  272. *
  273. * @return
  274. * An associative array with the cvterm_id's as keys. The first
  275. * element in the array has a key of '0' and a value of 'Select a Type'
  276. *
  277. * @ingroup tripal_chado_api
  278. */
  279. function tripal_get_cvterm_select_options($cv_id, $rel_type = FALSE) {
  280. $columns = array('cvterm_id', 'name');
  281. $values = array('cv_id' => $cv_id);
  282. if ($rel_type) {
  283. $values['is_relationshiptype'] = 1;
  284. }
  285. $s_options = array('order_by' => array('name' => 'ASC'));
  286. $cvterms = chado_select_record('cvterm', $columns, $values, $s_options);
  287. $options = array();
  288. $options[0] = 'Select a Type';
  289. foreach ($cvterms as $cvterm) {
  290. $options[$cvterm->cvterm_id] = $cvterm->name;
  291. }
  292. return $options;
  293. }
  294. /**
  295. * Updates the cvtermpath table of Chado for the specified CV.
  296. *
  297. * @param $cv_id
  298. * The chado cv_id;
  299. * @param $job_id
  300. * This function is intended to be used with the Tripal Jobs API.
  301. * When this function is called as a job the $job_id is automatically
  302. * passed to this function.
  303. * @return
  304. * TRUE on success FALSE on failure
  305. *
  306. * @ingroup tripal_chado_api
  307. */
  308. function tripal_update_cvtermpath_old($cv_id, $job_id = NULL) {
  309. // TODO: need better error checking in this function
  310. // first get the controlled vocabulary name:
  311. $sql = "SELECT * FROM {cv} WHERE cv_id = :cv_id";
  312. $cv = chado_query($sql, array(':cv_id' => $cv_id))->fetchObject();
  313. print "\nUpdating cvtermpath for $cv->name...\n";
  314. // We need to set the chado schema as active because some of the
  315. // functions call other functions which would not be in scope.
  316. $previous = chado_set_active('chado');
  317. try {
  318. $sql = "SELECT * FROM fill_cvtermpath(:name)";
  319. db_query($sql, array(':name' => $cv->name));
  320. chado_set_active($previous);
  321. }
  322. catch (Exception $e) {
  323. chado_set_active($previous);
  324. $error = $e->getMessage();
  325. tripal_report_error('tripal_chado', TRIPAL_ERROR, "Could not fill cvtermpath table: @error", array('@error' => $error));
  326. return FALSE;
  327. }
  328. return TRUE;
  329. }
  330. /**
  331. * Duplicate of fill_cvtermpath() stored procedure in Chado.
  332. *
  333. * Identifies all of the root terms of the controlled vocabulary. These
  334. * root terms are then processed by calling the
  335. * tripal_update_cvtermpath_root_loop() function on each one.
  336. *
  337. * @param $cvid
  338. * The controlled vocabulary ID from the cv table of Chado (i.e. cv.cv_id).
  339. * @param $job_id
  340. */
  341. function tripal_update_cvtermpath($cv_id, $job_id = NULL){
  342. // TODO: there's a function to determine the current Chado instance.
  343. // we should use that.
  344. $prev_db = chado_set_active('chado');
  345. try {
  346. $result = db_query('
  347. SELECT DISTINCT t.*
  348. FROM cvterm t
  349. LEFT JOIN cvterm_relationship r ON (t.cvterm_id = r.subject_id)
  350. INNER JOIN cvterm_relationship r2 ON (t.cvterm_id = r2.object_id)
  351. WHERE t.cv_id = :cvid AND r.subject_id is null',
  352. array(':cvid' => $cv_id)
  353. );
  354. // Iterate through each root level term.
  355. $record = $result->fetchAll();
  356. $roots = array();
  357. foreach ($record as $item){
  358. tripal_update_cvtermpath_root_loop($item->cvterm_id, $item->cv_id, $roots);
  359. }
  360. }
  361. catch (Exception $e) {
  362. // If there's an exception we have to set the database back. So, do that
  363. // and then rethrow the error.
  364. chado_set_active($prev_db);
  365. throw $e;
  366. }
  367. chado_set_active($prev_db);
  368. }
  369. /**
  370. * Duplicate of _fill_cvtermpath4root() stored procedure in Chado.
  371. *
  372. * This function process a "branch" of the ontology. Initially, the
  373. * "root" starts at the top of the tree. But, as the cvtermpath is populated
  374. * the "root" becomes terms deeper in the tree.
  375. *
  376. * @param $rootid
  377. * The term ID from the cvterm table of Chado (i.e. cvterm.cvterm_id).
  378. * @param $cvid
  379. * The controlled vocabulary ID from the cv table of Chado (i.e. cv.cv_id).
  380. */
  381. function tripal_update_cvtermpath_root_loop($rootid, $cvid, &$roots){
  382. // Get's the cvterm record for this "root".
  383. $ttype = db_select('cvterm', 'cv')
  384. ->fields('cv', array('cvterm_id'));
  385. $db_or = db_or();
  386. $db_or->condition('cv.name', "isa", '=');
  387. $db_or->condition('cv.name', "is_a", '=');
  388. $ttype->condition($db_or);
  389. $result = $ttype->execute()->fetchObject();
  390. $term_id = $rootid . '|' . $rootid . '|' . $cvid . '|' . $result->cvterm_id;
  391. // If the child_id matches any other id in the array then we've hit a loop.
  392. foreach ($roots as $element_id) {
  393. if ($element_id == $term_id) {
  394. return;
  395. }
  396. }
  397. // Then add that new entry to the $tree_path.
  398. $roots[] = $term_id;
  399. // Descends through the branch starting at this "root" term.
  400. $visited = array();
  401. $depth = 0;
  402. tripal_update_cvtermpath_loop($rootid, $rootid, $cvid, $result->cvterm_id, $depth, 0, $visited);
  403. // Get's the children terms of this "root" term and then recursively calls
  404. // this function making each child root.
  405. $cterm = db_query(
  406. 'SELECT *
  407. FROM cvterm_relationship
  408. WHERE object_id = :rootid
  409. ',
  410. array(':rootid' => $rootid)
  411. );
  412. while($cterm_result = $cterm->fetchAssoc()) {
  413. tripal_update_cvtermpath_root_loop($cterm_result['subject_id'], $cvid, $roots);
  414. }
  415. }
  416. /**
  417. *
  418. * @param $origin
  419. * The root terms cvterm_id.
  420. * @param $child_id
  421. * The cvterm_id of the current child term. The child term is a descendent
  422. * of the origin.
  423. * @param $cv_id
  424. * The controlled vocabulary ID from the cv table of Chado (i.e. cv.cv_id).
  425. * @param $type_id
  426. * The relationship type between the origin term and the child.
  427. * @param $depth
  428. * The depth of the recursion.
  429. * @param $increment_of_depth.
  430. * An integer ??
  431. * @param $tree_path.
  432. * The array of every term between the current child and the origin. Each
  433. * element in the array is an associative array with the keys:
  434. * -build_id: an string identifier for the child that combines the origin,
  435. * child cvterm_id,cv_id, and the type_id.
  436. * -depth: the depth that a child was inserted into the cvtermpath table.
  437. * @return multitype:
  438. */
  439. $loop_data;
  440. function tripal_update_cvtermpath_loop($origin, $child_id, $cv_id, $type_id, $depth,
  441. $increment_of_depth, $tree_path){
  442. // An array of
  443. global $loop_data;
  444. // Check to see if a row with these values already exists.
  445. chado_set_active('chado');
  446. $count = db_query(
  447. 'SELECT *
  448. FROM cvtermpath
  449. WHERE cv_id = :cvid
  450. AND object_id = :origin
  451. AND subject_id = :child_id
  452. AND pathdistance = :depth
  453. ',
  454. array(':cvid' => $cv_id, ':origin' => $origin, ':child_id' => $child_id, ':depth' => $depth)
  455. );
  456. $count_total = $count->rowCount();
  457. // If we've already seen this term then just return, we don't want
  458. // to insert it again.
  459. if ($count_total > 0) {
  460. return $loop_data;
  461. }
  462. // Build the ID.
  463. $term_id = $origin . '|' . $child_id . '|' . $cv_id . '|' . $type_id;
  464. // Now check if the most recent entry already exists in the array.
  465. if ($increment_of_depth != 0 && empty($loop_data)) {
  466. // Search the $tree_path for the new $child_id in the build_id column.
  467. foreach ($tree_path as $parent) {
  468. // If this child is the same as a parent term that has already been
  469. // processed then we have a potential loop.
  470. if ($parent['build_id'] == $term_id) {
  471. // The loop checker function below.
  472. $result_of_loop_checker = tripal_update_cvtermpath_loop_checker($origin,
  473. $child_id, $cv_id, $type_id, $depth, $increment_of_depth, 0,
  474. $parent, array(), $depth);
  475. if (!empty($result_of_loop_checker)) {
  476. $loop_data = $result_of_loop_checker;
  477. //Find the depth of the loop start by finding it in the array_of_children
  478. foreach($tree_path as $children => $child){
  479. if($child['build_id'] == $loop_data['build_id']){
  480. $loop_location = $child['depth'];
  481. }
  482. }
  483. $array_loop_data = (array)$loop_data;
  484. $array_loop_data['depth'] = $loop_location;
  485. $loop_data = $array_loop_data;
  486. break;
  487. }
  488. }
  489. if (!empty($loop_data)) {
  490. return $loop_data;
  491. }
  492. }
  493. }
  494. // We have not detected a loop, so it's safe to insert the term.
  495. $query = db_insert('cvtermpath')
  496. ->fields([
  497. 'object_id' => $origin,
  498. 'subject_id' => $child_id,
  499. 'cv_id' => $cv_id,
  500. 'type_id' => $type_id,
  501. 'pathdistance' => $depth,
  502. ]);
  503. $rows = $query->execute();
  504. // Then add that new entry to the $tree_path.
  505. $tree_path[$increment_of_depth] = [
  506. 'build_id' => $term_id,
  507. 'depth' => $depth
  508. ];
  509. // Get all of the relationships of this child term, and recursively
  510. // call the tripal_update_cvtermpath_loop() function to continue
  511. // descending down the tree.
  512. $query = db_select('cvterm_relationship', 'cvtr')
  513. ->fields('cvtr')
  514. ->condition('cvtr.object_id', $child_id, '=')
  515. ->execute();
  516. $cterm_relationships = $query->fetchAll();
  517. foreach ($cterm_relationships as $item) {
  518. if (!empty($loop_data)) {
  519. if ($loop_data['depth'] < $depth) {
  520. break;
  521. }
  522. elseif ($loop_data['depth'] > $depth) {
  523. $loop_data = NULL;
  524. break;
  525. }
  526. }
  527. else {
  528. $increment_of_depth++;
  529. tripal_update_cvtermpath_loop($origin, $item->subject_id, $cv_id,
  530. $item->type_id, $depth + 1, $increment_of_depth, $tree_path);
  531. }
  532. }
  533. }
  534. /**
  535. * @param $origin
  536. * @param $child_id
  537. * @param $cv_id
  538. * @param $type_id
  539. * @param $depth
  540. * @param $increment_of_depth
  541. * @param $distance_between_parent_child
  542. * @param $possible_start_of_loop
  543. * @param $array_of_possible_loop
  544. * @param $depth_at_start_of_loop
  545. *
  546. * @return bool
  547. */
  548. function tripal_update_cvtermpath_loop_checker($origin, $child_id, $cv_id, $type_id,
  549. $depth, $increment_of_depth, $distance_between_parent_child, $possible_start_of_loop,
  550. $array_of_possible_loop, $depth_at_start_of_loop){
  551. // Find the child terms of the current term via the relationship taboe.
  552. chado_set_active('chado');
  553. $query = db_select('cvterm_relationship', 'cvtr')
  554. ->fields('cvtr')
  555. ->condition('cvtr.object_id', $child_id, '=')
  556. ->execute();
  557. $cterm_relationships = $query->fetchAll();
  558. // Iterate through the child terms via the relationships.
  559. foreach ($cterm_relationships as $item){
  560. // Search the $tree_path for the new $child_id in the build_id column.
  561. foreach ($array_of_possible_loop as $parent) {
  562. if ($parent['build_id'] === $possible_start_of_loop['build_id']) {
  563. // If the search returns something check for a possible loop.
  564. if (!empty($parent)) {
  565. $result = tripal_update_cvtermpath_loop_checker_traverse($origin, $child_id,
  566. $cv_id, $type_id, $depth, $increment_of_depth, $possible_start_of_loop,
  567. $array_of_possible_loop, array(), 0);
  568. if(!empty($result)){
  569. break 2;
  570. }
  571. }
  572. }
  573. }
  574. $increment_of_depth++;
  575. $distance_between_parent_child++;
  576. $child_id = $origin . '|' . $item->subject_id . '|' . $cv_id . '|' . $item->type_id;
  577. $array_of_possible_loop[$distance_between_parent_child] = ['build_id' => $child_id];
  578. $result = tripal_update_cvtermpath_loop_checker($origin, $item->subject_id, $cv_id, $item->type_id, $depth + 1, $increment_of_depth, $distance_between_parent_child, $possible_start_of_loop, $array_of_possible_loop, $depth_at_start_of_loop);
  579. if($result !== FALSE){
  580. return $result;
  581. }
  582. }
  583. if (!empty($result)) {
  584. return $result;
  585. }
  586. else {
  587. return FALSE;
  588. }
  589. }
  590. function tripal_update_cvtermpath_loop_checker_traverse($origin, $child_id, $cv_id,
  591. $type_id, $depth, $increment_of_depth, $possible_start_of_loop, $array_of_possible_loop,
  592. $traverse_of_loop, $increment) {
  593. chado_set_active('chado');
  594. $query = db_select('cvterm_relationship', 'cvtr')
  595. ->fields('cvtr')
  596. ->condition('cvtr.object_id', $child_id, '=')
  597. ->execute();
  598. $cterm = $query->fetchAll();
  599. foreach ($cterm as $item) {
  600. if ($array_of_possible_loop === $traverse_of_loop) {
  601. //Report the loop.
  602. $loop_found = end($array_of_possible_loop);
  603. break;
  604. }
  605. elseif ($array_of_possible_loop != $traverse_of_loop) {
  606. $increment_of_depth++;
  607. $increment++;
  608. $child_id = $origin . '|' . $item->subject_id . '|' . $cv_id . '|' . $item->type_id;
  609. $traverse_of_loop[$increment] = ['build_id' => $child_id];
  610. $result = tripal_update_cvtermpath_loop_checker_traverse($origin,
  611. $item->subject_id, $cv_id, $item->type_id, $depth + 1,
  612. $increment_of_depth, $possible_start_of_loop, $array_of_possible_loop,
  613. $traverse_of_loop, $increment);
  614. if ($result !== FALSE) {
  615. return $result;
  616. }
  617. }
  618. }
  619. if ($loop_found) {
  620. return $loop_found;
  621. }
  622. else {
  623. return FALSE;
  624. }
  625. }
  626. /*
  627. *
  628. * @param $origin
  629. * @param $subject_id
  630. * @param $cv_idxkcd
  631. * @param $type_id
  632. * @param $depth
  633. * @return multitype:
  634. function tripal_update_cvtermpath_loop($origin, $child_id, $cv_id, $type_id, $depth){
  635. // Variables and arrays needed for loop checking.
  636. $tree_path;
  637. $array_of_possible_loop;
  638. $possible_start_of_loop;
  639. $distance_between_parent_child;
  640. $increment_of_depth;
  641. chado_set_active('chado');
  642. $count = db_query(
  643. 'SELECT *
  644. FROM cvtermpath
  645. WHERE cv_id = :cvid
  646. AND object_id = :origin
  647. AND subject_id = :child_id
  648. AND pathdistance = :depth
  649. ',
  650. array(':cvid' => $cv_id, ':origin' => $origin, ':child_id' => $child_id, ':depth' => $depth)
  651. );
  652. $count_total = $count->rowCount();
  653. //Loop check
  654. chado_set_active('chado');
  655. $loop = db_query(
  656. 'SELECT *
  657. FROM cvtermpath
  658. WHERE cv_id = :cvid
  659. AND object_id = :origin
  660. AND subject_id = :child_id
  661. AND type_id = :type_id
  662. ',
  663. array(':cvid' => $cv_id, ':origin' => $origin, ':child_id' => $child_id, ':type_id' => $type_id,)
  664. );
  665. $loop_check = $loop->rowCount();
  666. //watchdog('debug', '<pre>tripal_ds_preprocess_TripalEntity $rows ' . print_r($rows, TRUE) . '</pre>');
  667. /*if(!empty($rows)){
  668. foreach($rows as $row){
  669. tripal_update_cvtermpath_loop_check($origin, $child_id, $cv_id, $type_id, $depth, $row->cvtermpath_id, 0);
  670. }
  671. }
  672. else {*/
  673. //If no loop proceed.
  674. /*try{
  675. if($count_total == 0) {
  676. chado_set_active('chado');
  677. $query = db_insert('cvtermpath')
  678. ->fields(array(
  679. 'object_id' => $origin,
  680. 'subject_id' => $child_id,
  681. 'cv_id' => $cv_id,
  682. 'type_id' => $type_id,
  683. 'pathdistance' => $depth,
  684. ));
  685. $rows = $query->execute();
  686. }
  687. if ($loop_check == 0) {
  688. chado_set_active('chado');
  689. $query = db_select('cvterm_relationship', 'cvtr')
  690. ->fields('cvtr')
  691. ->condition('cvtr.object_id', $child_id, '=')
  692. ->execute();
  693. $cterm = $query->fetchAll();
  694. foreach ($cterm as $item) {
  695. //watchdog('debug', '<pre>tripal_ds_preprocess_TripalEntity $item ' . print_r($item, TRUE) . '</pre>');
  696. tripal_update_cvtermpath_loop($origin, $item->subject_id, $cv_id, $item->type_id, $depth + 1);
  697. };
  698. //}
  699. }
  700. }
  701. catch(Exception $e){
  702. watchdog_exception('tripal_ds', $e);
  703. return FALSE;
  704. }
  705. return 1;
  706. }
  707. */
  708. /**
  709. *
  710. * @param $origin
  711. * @param $subject_id
  712. * @param $cv_id
  713. * @param $type_id
  714. * @param $depth
  715. * @return multitype:
  716. function tripal_update_cvtermpath_loop_check($origin, $child_id, $cv_id, $type_id, $depth, $cvtermpath_id, $loop_count, $loop_check, $object_id){
  717. //Store the
  718. //Check if the passed parameters match any of the items in the loop_check array.
  719. if(!empty($loop_check)){
  720. foreach($loop_check as $item){
  721. if ($item['type_id'] = $type_id){
  722. if($item['subject_id'] = $child_id){
  723. if($item['object_id'] = $object_id){
  724. //Loop found, roll back all rows until $cvtermpath_id-1 (last correct entry)
  725. // and step into the next loop
  726. }
  727. }
  728. }
  729. }
  730. }
  731. $loop_count + 1;
  732. chado_set_active('chado');
  733. $count = db_query(
  734. 'SELECT *
  735. FROM cvtermpath
  736. WHERE cv_id = :cvid AND object_id = :origin
  737. AND subject_id = :child_id
  738. AND pathdistance = :depth
  739. ',
  740. array(':cvid' => $cv_id, ':origin' => $origin, ':child_id' => $child_id, ':depth' => $depth)
  741. );
  742. $count_total = $count->rowCount();
  743. if ($count_total == 0) {
  744. chado_set_active('chado');
  745. $query = db_insert('cvtermpath')
  746. ->fields(array(
  747. 'object_id' => $origin,
  748. 'subject_id' => $child_id,
  749. 'cv_id' => $cv_id,
  750. 'type_id' => $type_id,
  751. 'pathdistance' => $depth,
  752. ));
  753. $rows = $query->execute();
  754. $cterm = array();
  755. $query = db_select('cvterm_relationship', 'cvtr')
  756. ->fields('cvtr')
  757. ->condition('cvtr.object_id', $child_id, '=' )
  758. ->execute();
  759. $cterm = $query->fetchAll();
  760. foreach ($cterm as $item) {
  761. $loop_check[$loop_count]= $item;
  762. tripal_update_cvtermpath_loop_check($origin, $item->subject_id, $cv_id, $item->type_id, $depth + 1, $loop_count, $loop_check, $item->object_id);
  763. };
  764. }
  765. return 1;
  766. }
  767. */
  768. /**
  769. * Adds a controlled vocabular to the CV table of Chado.
  770. *
  771. * @param $name
  772. * The name of the controlled vocabulary. These are typically all lower case
  773. * with no special characters other than an undrescore (for spaces).
  774. * @param $comment
  775. * A description or definition of the vocabulary.
  776. *
  777. * @return
  778. * An object populated with fields from the newly added database.
  779. *
  780. * @ingroup tripal_chado_api
  781. */
  782. function tripal_insert_cv($name, $definition) {
  783. // insert/update values
  784. $ins_values = array(
  785. 'name' => $name,
  786. 'definition' => $definition
  787. );
  788. // see if the CV default exists already in the database
  789. $sel_values = array('name' => $name);
  790. $results = chado_select_record('cv', array('*'), $sel_values);
  791. // if it does not exists then add it
  792. if (count($results) == 0) {
  793. $success = chado_insert_record('cv', $ins_values);
  794. if (!$success) {
  795. tripal_report_error('tripal_chado', TRIPAL_WARNING, "Failed to create the CV record", NULL);
  796. return FALSE;
  797. }
  798. $results = chado_select_record('cv', array('*'), $sel_values);
  799. }
  800. // if it already exists then do an update
  801. else {
  802. $success = chado_update_record('cv', $sel_values, $ins_values);
  803. if (!$success) {
  804. tripal_report_error('tripal_chado', TRIPAL_WARNING, "Failed to update the CV record", NULL);
  805. return FALSE;
  806. }
  807. $results = chado_select_record('cv', array('*'), $sel_values);
  808. }
  809. // return the cv object
  810. return $results[0];
  811. }
  812. /**
  813. * Add's a controlled vocabulary term to Chado.
  814. *
  815. * This function will add a cvterm record (and a dbxref record if appropriate
  816. * values are provided). If the parent vocabulary does not exist then
  817. * that also is added to the cv table. If the cvterm is a relationship term
  818. * then the 'is_relationship' value should be set. All
  819. * terms must also have a corresponding database. This is specified in the
  820. * term's ID just before the colon (e.g. GO:003824). If the database does not
  821. * exist in the DB table then it will be added automatically. The accession
  822. * (the value just after the colon in the term's ID) will be added to the
  823. * dbxref table. If the CVterm already exists and $update is set (default)
  824. * then the cvterm is updated. If the CVTerm already exists and $update is
  825. * not set, then no changes are made and the CVTerm object is returned.
  826. *
  827. * @param $term
  828. * An associative array with the following keys:
  829. * - id: the term accession. must be of the form <DB>:<ACCESSION>, where
  830. * <DB> is the name of the database to which the cvterm belongs and the
  831. * <ACCESSION> is the term's accession number in the database.
  832. * - name: the name of the term. usually meant to be human-readable.
  833. * - is_obsolete: is present and set to 1 if the term is defunct
  834. * - definition: the definition of the term
  835. * - cv_name: The CV name to which the term belongs. If this arugment is
  836. * null or not provided then the function tries to find a record in the
  837. * CV table with the same name provided in the $term[namespace]. If
  838. * this field is provided then it overrides what the value in
  839. * $term[namespace]
  840. * - is_relationship: If this term is a relationship term then this value
  841. * should be 1.
  842. * - db_name: In some cases the database name will not be part of the
  843. * $term['id'] and it needs to be explicitly set. Use this argument
  844. * only if the database name cannot be specififed in the term ID
  845. * (e.g. <DB>:<ACCESSION>).
  846. * @param $options
  847. * An associative array with the following keys:
  848. * - update_existing: By default this is TRUE. If the term exists it is
  849. * automatically updated.
  850. *
  851. * @return
  852. * A cvterm object
  853. *
  854. * @ingroup tripal_chado_api
  855. */
  856. function tripal_insert_cvterm($term, $options = array()) {
  857. // get the term properties
  858. $id = (isset($term['id'])) ? $term['id'] : '';
  859. $name = '';
  860. $cvname = '';
  861. $definition = '';
  862. $is_obsolete = 0;
  863. $accession = '';
  864. // Set Defaults
  865. if (isset($term['cv_name'])) {
  866. $cvname = $term['cv_name'];
  867. }
  868. else {
  869. $cvname = 'local';
  870. }
  871. // Namespace is deprecated but must be supported for backwards
  872. // compatability
  873. if (array_key_exists('namespace', $term)) {
  874. $cvname = $term['namespace'];
  875. }
  876. if (isset($term['is_relationship'])) {
  877. $is_relationship = $term['is_relationship'];
  878. }
  879. else {
  880. $is_relationship = 0;
  881. }
  882. if (isset($term['db_name'])) {
  883. $dbname = $term['db_name'];
  884. }
  885. else {
  886. $dbname = 'internal';
  887. }
  888. if (isset($options['update_existing'])) {
  889. $update = $options['update_existing'];
  890. }
  891. else {
  892. $update = 1;
  893. }
  894. if (array_key_exists('name', $term)) {
  895. $name = $term['name'];
  896. }
  897. else {
  898. $name = $id;
  899. }
  900. if (array_key_exists('definition', $term)) {
  901. $definition = preg_replace('/^\"(.*)\"/', '\1', $term['definition']);
  902. }
  903. else {
  904. $definition = '';
  905. }
  906. if (array_key_exists('is_obsolete', $term)) {
  907. $is_obsolete = $term['is_obsolete'];
  908. if (strcmp($is_obsolete, 'true') == 0) {
  909. $is_obsolete = 1;
  910. }
  911. }
  912. if (!$name and !$id) {
  913. tripal_report_error('tripal_cv', TRIPAL_WARNING, "Cannot find cvterm without 'id' or 'name'", NULL);
  914. return 0;
  915. }
  916. if (!$id) {
  917. $id = $name;
  918. }
  919. // Get the accession and the database from the cvterm id.
  920. if ($dbname) {
  921. $accession = $id;
  922. }
  923. if (preg_match('/^.+?:.*$/', $id)) {
  924. $accession = preg_replace('/^.+?:(.*)$/', '\1', $id);
  925. $dbname = preg_replace('/^(.+?):.*$/', '\1', $id);
  926. }
  927. // Check that we have a database name, give a different message if it's a
  928. // relationship.
  929. if ($is_relationship and !$dbname) {
  930. tripal_report_error('tripal_cv', TRIPAL_WARNING, "A database name is not provided for this relationship term: $id", NULL);
  931. return 0;
  932. }
  933. if (!$is_relationship and !$dbname) {
  934. tripal_report_error('tripal_cv', TRIPAL_WARNING, "A database identifier is missing from the term: $id", NULL);
  935. return 0;
  936. }
  937. // Make sure the CV name exists
  938. $cv = tripal_get_cv(array('name' => $cvname));
  939. if (!$cv) {
  940. $cv = tripal_insert_cv($cvname, '');
  941. }
  942. if (!$cv) {
  943. tripal_report_error('tripal_cv', TRIPAL_WARNING, "Cannot find namespace '$cvname' when adding/updating $id", NULL);
  944. return 0;
  945. }
  946. // This SQL statement will be used a lot to find a cvterm so just set it
  947. // here for easy reference below. Because CV terms can change their names
  948. // but accessions don't change, the following SQL finds cvterms based on
  949. // their accession rather than the name.
  950. $cvtermsql = "
  951. SELECT CVT.name, CVT.cvterm_id, CV.cv_id, CV.name as cvname,
  952. DB.name as dbname, DB.db_id, DBX.accession
  953. FROM {cvterm} CVT
  954. INNER JOIN {dbxref} DBX on CVT.dbxref_id = DBX.dbxref_id
  955. INNER JOIN {db} DB on DBX.db_id = DB.db_id
  956. INNER JOIN {cv} CV on CV.cv_id = CVT.cv_id
  957. WHERE DBX.accession = :accession and DB.name = :name
  958. ";
  959. // Add the database. The function will just return the DB object if the
  960. // database already exists.
  961. $db = tripal_get_db(array('name' => $dbname));
  962. if (!$db) {
  963. $db = tripal_insert_db(array('name' => $dbname));
  964. }
  965. if (!$db) {
  966. tripal_report_error('tripal_cv', TRIPAL_WARNING, "Cannot find database '$dbname' in Chado.", NULL);
  967. return 0;
  968. }
  969. // The cvterm table has two unique dependencies. We need to check both.
  970. // first check the (name, cv_id, is_obsolete) constraint.
  971. $values = array(
  972. 'name' => $name,
  973. 'is_obsolete' => $is_obsolete,
  974. 'cv_id' => array(
  975. 'name' => $cvname,
  976. ),
  977. );
  978. $result = chado_select_record('cvterm', array('*'), $values);
  979. if (count($result) == 1) {
  980. $cvterm = $result[0];
  981. // Get the dbxref record.
  982. $values = array('dbxref_id' => $cvterm->dbxref_id);
  983. $result = chado_select_record('dbxref', array('*'), $values);
  984. $dbxref = $result[0];
  985. if (!$dbxref) {
  986. tripal_report_error('tripal_cv', TRIPAL_ERROR,
  987. 'Unable to access the dbxref record for the :term cvterm. Term Record: !record',
  988. array(':term' => $name, '!record' => print_r($cvterm, TRUE))
  989. );
  990. return FALSE;
  991. }
  992. // Get the db.
  993. $values = array('db_id' => $dbxref->db_id);
  994. $result = chado_select_record('db', array('*'), $values);
  995. $db_check = $result[0];
  996. // // The database name for this existing term does not match that of the
  997. // // one provided to this function. The CV name matches otherwise we
  998. // // wouldn't have made it this far. So, let's swap the database for
  999. // // this term.
  1000. // if ($db_check->name != $db->name) {
  1001. // // Look to see if the correct dbxref record already exists for this
  1002. // // database.
  1003. // $values = array(
  1004. // 'db_id' => $db->db_id,
  1005. // 'accession' => $accession,
  1006. // );
  1007. // $result = chado_select_record('dbxref', array('*'), $values);
  1008. // // If we already have a good dbxref then we want to update our cvterm
  1009. // // to use this dbxref.
  1010. // if (count($result) > 0) {
  1011. // $dbxref = $result[0];
  1012. // $match = array('cvterm_id' => $cvterm->cvterm_id);
  1013. // $values = array('dbxref_id' => $dbxref->dbxref_id);
  1014. // $success = chado_update_record('cvterm', $match, $values);
  1015. // if (!$success) {
  1016. // tripal_report_error('tripal_cv', TRIPAL_WARNING, "Failed to correct the dbxref id for the cvterm " .
  1017. // "'$name' (id: $accession), for database $dbname", NULL);
  1018. // return 0;
  1019. // }
  1020. // }
  1021. // // If we don't have the dbxref then we want to delete our cvterm and let
  1022. // // the code below recreate it with the correct info.
  1023. // else {
  1024. // $match = array('cvterm_id' => $cvterm->cvterm_id);
  1025. // chado_delete_record('cvterm', $match);
  1026. // }
  1027. // }
  1028. // Check that the accession matches. Sometimes an OBO can define a term
  1029. // multiple times but with different accessions. If this is the case we
  1030. // can't do an insert or it will violate the constraint in the cvterm table.
  1031. // So we'll need to add the record to the cvterm_dbxref table instead.
  1032. if ($dbxref->accession != $accession) {
  1033. // Get/add the dbxref for his term.
  1034. $dbxref_new = tripal_insert_dbxref(array(
  1035. 'db_id' => $db->db_id,
  1036. 'accession' => $accession
  1037. ));
  1038. if (!$dbxref_new) {
  1039. tripal_report_error('tripal_cv', TRIPAL_WARNING, "Failed to find or insert the dbxref record for cvterm, " .
  1040. "$name (id: $accession), for database $dbname", NULL);
  1041. return 0;
  1042. }
  1043. // Check to see if the cvterm_dbxref record already exists.
  1044. $values = array(
  1045. 'cvterm_id' => $cvterm->cvterm_id,
  1046. 'dbxref_id' => $dbxref_new->dbxref_id,
  1047. 'is_for_definition' => 1,
  1048. );
  1049. $result = chado_select_record('cvterm_dbxref', array('*'), $values);
  1050. // if the cvterm_dbxref record does not exists then add it
  1051. if (count($result)==0) {
  1052. $options = array(
  1053. 'return_record' => FALSE,
  1054. );
  1055. $success = chado_insert_record('cvterm_dbxref', $values, $options);
  1056. if (!$success) {
  1057. tripal_report_error('tripal_cv', TRIPAL_WARNING, "Failed to find or insert the cvterm_dbxref record for a " .
  1058. "duplicated cvterm: $name (id: $accession), for database $dbname", NULL);
  1059. return 0;
  1060. }
  1061. }
  1062. // get the original cvterm with the same name and return that.
  1063. $result = chado_query($cvtermsql, array(':accession' => $dbxref->accession, ':name' => $dbname));
  1064. $cvterm = $result->fetchObject();
  1065. return $cvterm;
  1066. }
  1067. // Continue on, we've fixed the record if the db_id did not match.
  1068. // We can now perform and updated if we need to.
  1069. }
  1070. // get the CVterm record
  1071. $result = chado_query($cvtermsql, array(':accession' => $accession, ':name' => $dbname));
  1072. $cvterm = $result->fetchObject();
  1073. if (!$cvterm) {
  1074. // Check to see if the dbxref exists if not, add it.
  1075. $dbxref = tripal_insert_dbxref(array(
  1076. 'db_id' => $db->db_id,
  1077. 'accession' => $accession
  1078. ));
  1079. if (!$dbxref) {
  1080. tripal_report_error('tripal_cv', TRIPAL_WARNING, "Failed to find or insert the dbxref record for cvterm, " .
  1081. "$name (id: $accession), for database $dbname", NULL);
  1082. return 0;
  1083. }
  1084. // Check to see if the dbxref already has an entry in the cvterm table.
  1085. $values = array('dbxref_id' => $dbxref->dbxref_id);
  1086. $check = chado_select_record('cvterm', array('cvterm_id'), $values);
  1087. if (count($check) == 0) {
  1088. // now add the cvterm
  1089. $ins_values = array(
  1090. 'cv_id' => $cv->cv_id,
  1091. 'name' => $name,
  1092. 'definition' => $definition,
  1093. 'dbxref_id' => $dbxref->dbxref_id,
  1094. 'is_obsolete' => $is_obsolete,
  1095. 'is_relationshiptype' => $is_relationship,
  1096. );
  1097. $success = chado_insert_record('cvterm', $ins_values);
  1098. if (!$success) {
  1099. if (!$is_relationship) {
  1100. tripal_report_error('tripal_cv', TRIPAL_WARNING, "Failed to insert the term: $name ($dbname)", NULL);
  1101. return 0;
  1102. }
  1103. else {
  1104. tripal_report_error('tripal_cv', TRIPAL_WARNING, "Failed to insert the relationship term: $name (cv: " . $cvname . " db: $dbname)", NULL);
  1105. return 0;
  1106. }
  1107. }
  1108. }
  1109. // This dbxref already exists in the cvterm table.
  1110. else {
  1111. tripal_report_error('tripal_cv', TRIPAL_WARNING, "The dbxref already exists for another cvterm record: $name (cv: " . $cvname . " db: $dbname)", NULL);
  1112. return 0;
  1113. }
  1114. $result = chado_query($cvtermsql, array(':accession' => $accession, ':name' => $dbname));
  1115. $cvterm = $result->fetchObject();
  1116. }
  1117. // Update the cvterm.
  1118. elseif ($update) {
  1119. // First, basic update of the term.
  1120. $match = array('cvterm_id' => $cvterm->cvterm_id);
  1121. $upd_values = array(
  1122. 'name' => $name,
  1123. 'definition' => $definition,
  1124. 'is_obsolete' => $is_obsolete,
  1125. 'is_relationshiptype' => $is_relationship,
  1126. );
  1127. $success = chado_update_record('cvterm', $match, $upd_values);
  1128. if (!$success) {
  1129. tripal_report_error('tripal_cv', TRIPAL_WARNING, "Failed to update the term: $name", NULL);
  1130. return 0;
  1131. }
  1132. // Second, check that the dbxref has not changed and if it has then update it.
  1133. $checksql = "
  1134. SELECT cvterm_id
  1135. FROM {cvterm} CVT
  1136. INNER JOIN {dbxref} DBX on CVT.dbxref_id = DBX.dbxref_id
  1137. INNER JOIN {db} DB on DBX.db_id = DB.db_id
  1138. INNER JOIN {cv} CV on CV.cv_id = CVT.cv_id
  1139. WHERE DBX.accession = :accession and DB.name = :dbname and CVT.name = :term and CV.name = :cvname
  1140. ";
  1141. $check = chado_query($checksql, array(':accession' => $accession, ':dbname' => $dbname, ':term' => $name, ':cvname' => $cvname))->fetchObject();
  1142. if (!$check) {
  1143. // check to see if the dbxref exists if not, add it.
  1144. $dbxref = tripal_insert_dbxref(array(
  1145. 'db_id' => $db->db_id,
  1146. 'accession' => $accession
  1147. ));
  1148. if (!$dbxref) {
  1149. tripal_report_error('tripal_chado', TRIPAL_WARNING, "Failed to find or insert the dbxref record for cvterm, " .
  1150. "$name (id: $accession), for database $dbname", NULL);
  1151. return 0;
  1152. }
  1153. $match = array('cvterm_id' => $cvterm->cvterm_id);
  1154. $upd_values = array(
  1155. 'dbxref_id' => $dbxref->dbxref_id,
  1156. );
  1157. $success = chado_update_record('cvterm', $match, $upd_values);
  1158. if (!$success) {
  1159. tripal_report_error('tripal_chado', TRIPAL_WARNING, "Failed to update the term $name with new accession $db:$accession", NULL);
  1160. return 0;
  1161. }
  1162. }
  1163. // Finally grab the updated details.
  1164. $result = chado_query($cvtermsql, array(':accession' => $accession, ':name' => $dbname));
  1165. $cvterm = $result->fetchObject();
  1166. }
  1167. else {
  1168. // do nothing, we have the cvterm but we don't want to update
  1169. }
  1170. // return the cvterm
  1171. return $cvterm;
  1172. }
  1173. /**
  1174. * TODO: deprecate this function
  1175. *
  1176. * This function allows other modules to programatically
  1177. * submit an ontology for loading into Chado.
  1178. *
  1179. * This function will add a job to the Jobs subsystem for parsing the ontology.
  1180. * You can either pass a known OBO ID to the function or the URL
  1181. * or full path the the ontology file. If a URL or file name is
  1182. * passed then the $obo_name argument must also be provided. If
  1183. * this is the first time the ontology has been provided to Tripal
  1184. * then it will be added to the database and will be assigned a
  1185. * unique OBO ID.
  1186. *
  1187. * @param $obo_id
  1188. * If the ontology is already loaded into the Tripal tables then
  1189. * use this argument to specify the unique ID for the ontology
  1190. * that will be loaded
  1191. * @param $obo_name
  1192. * If the OBO has not been added before then use this argument
  1193. * to specify the human readable name of the ontology.
  1194. * @param $obo_url
  1195. * If the OBO to be loaded is located on a remote server then
  1196. * use this argument to provide the URL.
  1197. * @param $obo_file
  1198. * If the OBO is housed on the local file system of the server then
  1199. * use this argument to specify the full path.
  1200. *
  1201. * @return
  1202. * returns the job_id of the submitted job or FALSE if the job was not added
  1203. *
  1204. * @ingroup tripal_chado_api
  1205. */
  1206. function tripal_submit_obo_job($obo) {
  1207. global $user;
  1208. // Set Defaults
  1209. $obo['obo_id'] = (isset($obo['obo_id'])) ? $obo['obo_id'] : NULL;
  1210. $obo['name'] = (isset($obo['name'])) ? $obo['name'] : NULL;
  1211. $obo['url'] = (isset($obo['url'])) ? $obo['url'] : NULL;
  1212. $obo['file'] = (isset($obo['file'])) ? $obo['file'] : NULL;
  1213. $includes = array(
  1214. drupal_get_path('module', 'tripal_chado') . '/includes/tripal_chado.cv.inc',
  1215. );
  1216. if ($obo['obo_id']) {
  1217. $sql = "SELECT * FROM {tripal_cv_obo} WHERE obo_id = :obo_id";
  1218. $result = db_query($sql, array(':obo_id' => $obo['obo_id']))->fetchObject();
  1219. $args = array($result->obo_id);
  1220. return tripal_add_job("Load OBO " . $result->name, 'tripal_chado',
  1221. "tripal_cv_load_obo", $args, $user->uid, 10, $includes);
  1222. }
  1223. else {
  1224. if ($obo['url']) {
  1225. $sql = "SELECT * FROM {tripal_cv_obo} WHERE path = :url";
  1226. $result = db_query($sql, array(':url' => $obo['url']))->fetchObject();
  1227. $args = array($result->obo_id);
  1228. return tripal_add_job("Load OBO " . $result->name, 'tripal_chado',
  1229. "tripal_cv_load_obo", $args, $user->uid, 10, $includes);
  1230. }
  1231. elseif ($obo['file']) {
  1232. $sql = "SELECT * FROM {tripal_cv_obo} WHERE path = :file";
  1233. $result = db_query($sql, array(':url' => $obo['file']))->fetchObject();
  1234. $args = array($result->obo_id);
  1235. return tripal_add_job("Load OBO " . $result->name, 'tripal_chado',
  1236. "tripal_cv_load_obo", $args, $user->uid, 10, $includes);
  1237. }
  1238. }
  1239. return FALSE;
  1240. }
  1241. /**
  1242. * Add the OBO to the tripal_cv_obo table in the Drupal database.
  1243. *
  1244. * If the OBO name already exists in the table then the path is updated.
  1245. *
  1246. * @param $name
  1247. * The human readable name of this ontology
  1248. * @param $path
  1249. * The file path or URL of the ontology
  1250. *
  1251. * @return
  1252. * Returns the ontology ID
  1253. *
  1254. * @ingroup tripal_chado_api
  1255. */
  1256. function tripal_insert_obo($name, $path) {
  1257. // make sure an OBO with the same name doesn't already exist
  1258. $obo_id = db_select('tripal_cv_obo', 'tco')
  1259. ->fields('tco', array('obo_id'))
  1260. ->condition('name', $name)
  1261. ->execute()
  1262. ->fetchField();
  1263. if ($obo_id) {
  1264. db_update('tripal_cv_obo')
  1265. ->fields(array(
  1266. 'path' => $path,
  1267. ))
  1268. ->condition('name', $name)
  1269. ->execute();
  1270. return $obo_id;
  1271. }
  1272. else {
  1273. $obo_id = db_insert('tripal_cv_obo')
  1274. ->fields(array(
  1275. 'name' => $name,
  1276. 'path' => $path,
  1277. ))
  1278. ->execute();
  1279. return $obo_id;
  1280. }
  1281. }
  1282. /**
  1283. * Retrieves an OBO record.
  1284. *
  1285. * @param $values
  1286. * An associate array with the following allowed keys: obo_id, name
  1287. *
  1288. * @return
  1289. * An instance of an OBO record object.
  1290. */
  1291. function tripal_get_obo($values) {
  1292. $query = db_select('tripal_cv_obo', 'tco')
  1293. ->fields('tco');
  1294. if (array_key_exists('name', $values)) {
  1295. $query->condition('tco.name', $values['name']);
  1296. }
  1297. if (array_key_exists('obo_id', $values)) {
  1298. $query->condition('tco.obo_id', $values['obo_id']);
  1299. }
  1300. return $query->execute()->fetchObject();
  1301. }
  1302. /**
  1303. * This function is intended to be used in autocomplete forms
  1304. * for searching for CV terms that begin with the provided string
  1305. *
  1306. * @param $cv_id
  1307. * The CV ID in which to search for the term
  1308. * @param $string
  1309. * The string to search for
  1310. *
  1311. * @return
  1312. * A json array of terms that begin with the provided string
  1313. *
  1314. * @ingroup tripal_chado_api
  1315. */
  1316. function tripal_autocomplete_cvterm($cv_id, $string = '') {
  1317. if ($cv_id) {
  1318. $sql = "
  1319. SELECT CVT.cvterm_id, CVT.name
  1320. FROM {cvterm} CVT
  1321. WHERE CVT.cv_id = :cv_id and lower(CVT.name) like lower(:name)
  1322. UNION
  1323. SELECT CVT2.cvterm_id, CVTS.synonym as name
  1324. FROM {cvterm} CVT2
  1325. INNER JOIN {cvtermsynonym} CVTS ON CVTS.cvterm_id = CVT2.cvterm_id
  1326. WHERE CVT2.cv_id = :cv_id and lower(CVTS.synonym) like lower(:name)
  1327. ORDER by name
  1328. LIMIT 25 OFFSET 0
  1329. ";
  1330. $results = chado_query($sql, array(':cv_id' => $cv_id, ':name' => $string . '%'));
  1331. $items = array();
  1332. foreach ($results as $term) {
  1333. $items[$term->name] = $term->name;
  1334. }
  1335. }
  1336. // If a CV wasn't provided then search all of them, and include the cv
  1337. // in the results.
  1338. else {
  1339. $sql = "
  1340. SELECT CVT.cvterm_id, CVT.name, CV.name as cvname, CVT.cv_id
  1341. FROM {cvterm} CVT
  1342. INNER JOIN {cv} CV on CVT.cv_id = CV.cv_id
  1343. WHERE lower(CVT.name) like lower(:name)
  1344. UNION
  1345. SELECT CVT2.cvterm_id, CVTS.synonym as name, CV2.name as cvname, CVT2.cv_id
  1346. FROM {cvterm} CVT2
  1347. INNER JOIN {cv} CV2 on CVT2.cv_id = CV2.cv_id
  1348. INNER JOIN {cvtermsynonym} CVTS ON CVTS.cvterm_id = CVT2.cvterm_id
  1349. WHERE lower(CVTS.synonym) like lower(:name)
  1350. ORDER by name
  1351. LIMIT 25 OFFSET 0
  1352. ";
  1353. $results = chado_query($sql, array(':name' => $string . '%'));
  1354. $items = array();
  1355. foreach ($results as $term) {
  1356. $items[$term->name] = $term->name;
  1357. }
  1358. }
  1359. drupal_json_output($items);
  1360. }
  1361. /**
  1362. * Add a record to a cvterm linking table (ie: feature_cvterm)
  1363. *
  1364. * @param $basetable
  1365. * The base table to which the cvterm should be linked/associated. Thus to associate a
  1366. * cvterm to a feature the basetable=feature and cvterm_id is added to the feature_cvterm table.
  1367. * @param $record_id
  1368. * The primary key of the basetable to associate the cvterm with. This should be in integer.
  1369. * @param $cvterm
  1370. * An associative array describing the cvterm. Valid keys include:
  1371. * - name: the name for the cvterm,
  1372. * - cv_name: the name of the cv the cvterm belongs to.
  1373. * - cv_id: the primary key of the cv the cvterm belongs to.
  1374. * @param $options
  1375. * An associative array of options. Valid keys include:
  1376. * - insert_cvterm: Insert the cvterm if it doesn't already exist. FALSE is the default
  1377. *
  1378. * @ingroup tripal_chado_api
  1379. */
  1380. function tripal_associate_cvterm($basetable, $record_id, $cvterm, $options = array()) {
  1381. $linking_table = $basetable . '_cvterm';
  1382. $foreignkey_name = $basetable . '_id';
  1383. // Default Values
  1384. $options['insert_cvterm'] = (isset($options['insert_cvterm'])) ? $options['insert_cvterm'] : FALSE;
  1385. // If the cvterm_id is not set then find the cvterm record using the name and cv_id
  1386. if (!isset($cvterm['cvterm_id'])) {
  1387. $values = array(
  1388. 'name' => $cvterm['name'],
  1389. );
  1390. if (isset($cvterm['cv_id'])) {
  1391. $values['cv_id'] = $cvterm['cv_id'];
  1392. }
  1393. elseif (isset($cvterm['cv_name'])) {
  1394. $values['cv_id'] = array(
  1395. 'name' => $cvterm['cv_name']
  1396. );
  1397. }
  1398. else {
  1399. tripal_report_error('tripal_chado_api', TRIPAL_WARNING,
  1400. "tripal_associate_cvterm: The cvterm needs to have either the cv_name or cv_id
  1401. supplied. You were trying to associate a cvterm with the %base %record_id
  1402. and supplied the cvterm values: %cvterm.",
  1403. array('%base' => $basetable, '%record_id' => $record_id, '%cvterm' => print_r($cvterm,TRUE))
  1404. );
  1405. return FALSE;
  1406. }
  1407. // Get the cvterm. If it doesn't exist then add it if the option
  1408. // 'insert_cvterm' is set.
  1409. $select = chado_select_record('cvterm', array('*'), $values);
  1410. if ($select) {
  1411. $cvterm['cvterm_id'] = $select[0]->cvterm_id;
  1412. }
  1413. elseif ($options['insert_cvterm']) {
  1414. // Insert the cvterm
  1415. $insert = tripal_insert_cvterm($values);
  1416. if (isset($insert->cvterm_id)) {
  1417. $cvterm['cvterm_id'] = $insert->cvterm_id;
  1418. }
  1419. else {
  1420. tripal_report_error('tripal_chado_api', TRIPAL_WARNING,
  1421. "tripal_associate_cvterm: Unable to insert the cvterm using the cvterm values: %cvterm.",
  1422. array('%cvterm' => print_r($cvterm,TRUE))
  1423. );
  1424. return FALSE;
  1425. }
  1426. }
  1427. else {
  1428. tripal_report_error('tripal_api', TRIPAL_WARNING,
  1429. "tripal_associate_cvterm: The cvterm doesn't already exist. You supplied the cvterm values: %cvterm.",
  1430. array('%cvterm' => print_r($cvterm,TRUE))
  1431. );
  1432. return FALSE;
  1433. }
  1434. }
  1435. // Now add the link between the record & cvterm
  1436. if ($cvterm['cvterm_id'] > 0) {
  1437. $values = array(
  1438. 'cvterm_id' => $cvterm['cvterm_id'],
  1439. $foreignkey_name => $record_id,
  1440. 'pub_id' => 1,
  1441. );
  1442. // Check if the cvterm is already associated. If so, don't re-add it.
  1443. $result = chado_select_record($linking_table, array('*'), $values);
  1444. if (!$result) {
  1445. $success = chado_insert_record($linking_table, $values);
  1446. if (!$success) {
  1447. tripal_report_error('tripal_api', TRIPAL_WARNING,
  1448. "Failed to insert the %base record %term",
  1449. array('%base' => $linking_table, '%term' => $cvterm['name'])
  1450. );
  1451. return FALSE;
  1452. }
  1453. $result = chado_select_record($linking_table, array('*'), $values);
  1454. }
  1455. if (isset($result[0])) {
  1456. return $result[0];
  1457. }
  1458. else {
  1459. return FALSE;
  1460. }
  1461. }
  1462. return FALSE;
  1463. }
  1464. /**
  1465. * This function sets the default vocabulary for a given table and field.
  1466. *
  1467. * @param $table
  1468. * The name of the table that contains a field with a foreign key
  1469. * relationship to the cvterm table
  1470. * @param $field
  1471. * The table field name that has the foreign key relationship to the
  1472. * cvterm table for which the default vocabulary will be set
  1473. * @param $cv_name
  1474. * The name of the vocabulary
  1475. *
  1476. * @return
  1477. * TRUE if set, FALSE if an error occured
  1478. */
  1479. function tripal_set_default_cv($table, $field, $cv_name, $cv_id = FALSE) {
  1480. // Get the CV object
  1481. if ($cv_id) {
  1482. $cv = tripal_get_cv(array('cv_id' => $cv_id));
  1483. }
  1484. else {
  1485. $cv = tripal_get_cv(array('name' => $cv_name));
  1486. }
  1487. if ($cv) {
  1488. // first delete any entries for this table and field
  1489. $num_deleted = db_delete('tripal_cv_defaults')
  1490. ->condition('table_name', $table)
  1491. ->condition('field_name', $field)
  1492. ->execute();
  1493. // now add the default value
  1494. $cv_default_id = db_insert('tripal_cv_defaults')
  1495. ->fields(array(
  1496. 'table_name' => $table,
  1497. 'field_name' => $field,
  1498. 'cv_id' => $cv->cv_id,
  1499. ))
  1500. ->execute();
  1501. if (!$cv_default_id) {
  1502. tripal_report_error('tripal_chado', TRIPAL_WARNING,
  1503. "Cannot set default vocabulary for %table.%field. Check the error logs.",
  1504. array('%table' => $table, '%field' => $field));
  1505. return FALSE;
  1506. }
  1507. }
  1508. else {
  1509. tripal_report_error('tripal_chado', TRIPAL_WARNING,
  1510. "Cannot set default vocabulary for %table.%field. The vocabulary name, '%cvname', doesn't exist.",
  1511. array('%table' => $table, '%field' => $field, '%cvname' => $cv_name));
  1512. return FALSE;
  1513. }
  1514. }