tripal_cv.api.inc 38 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129
  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_cv_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_cv_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_cv_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_cv_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_cv_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_cv_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_cv_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. foreach ($results as $r) {
  123. $options[$r->cv_id] = $r->name;
  124. }
  125. return $options;
  126. }
  127. /**
  128. * Retrieves a chado controlled vocabulary term variable
  129. *
  130. * @param $identifier
  131. * An array with the key stating what the identifier is. Supported keys (only one of the
  132. * following unique keys are required):
  133. * - cvterm_id: the chado cv.cvterm_id primary key
  134. * - name: the chado cvterm.name field (assume unique)
  135. * There are also some specially handled keys. They are:
  136. * - cv_id: an integer indicating the cv_id or an array with 'name' => the name of the cv.
  137. * - synonym: an array with 'name' => the name of the synonym of the cvterm you want
  138. * returned; 'cv_id' => the cv_id of the synonym; 'cv_name' => the name of the cv
  139. * of the synonym
  140. * - property: An array/object describing the property to select records for. It
  141. * should at least have either a type_name (if unique across cvs) or type_id. Other
  142. * supported keys include: cv_id/cv_name (of the type), value and rank
  143. * @param $options
  144. * An array of options. Supported keys include:
  145. * - Any keys supported by chado_generate_var(). See that function definition for
  146. * additional details.
  147. *
  148. * NOTE: the $identifier parameter can really be any array similar to $values passed into
  149. * chado_select_record(). It should fully specify the cvterm record to be returned.
  150. *
  151. * @return
  152. * If unique values were passed in as an identifier then an object describing the cvterm
  153. * will be returned (will be a chado variable from chado_generate_var()). Otherwise,
  154. * FALSE will be returned.
  155. *
  156. * @ingroup tripal_cv_api
  157. */
  158. function tripal_get_cvterm($identifiers, $options = array()) {
  159. // Set Defaults
  160. if (!isset($options['include_fk'])) {
  161. // Tells chado_generate_var to only get the cv
  162. $options['include_fk'] = array('cv_id' => TRUE);
  163. }
  164. // Error Checking of parameters
  165. if (!is_array($identifiers)) {
  166. tripal_report_error('tripal_cv_api', TRIPAL_ERROR,
  167. "tripal_get_cvterm: The identifier passed in is expected to be an array with the key
  168. matching a column name in the cvterm table (ie: cvterm_id or name). You passed in %identifier.",
  169. array('%identifier'=> print_r($identifiers, TRUE))
  170. );
  171. }
  172. elseif (empty($identifiers)) {
  173. tripal_report_error('tripal_cv_api', TRIPAL_ERROR,
  174. "tripal_get_cvterm: You did not pass in anything to identify the cvterm you want. The identifier
  175. is expected to be an array with the key matching a column name in the cvterm table
  176. (ie: cvterm_id or name). You passed in %identifier.",
  177. array('%identifier'=> print_r($identifiers, TRUE))
  178. );
  179. }
  180. // If synonym was passed in, then process this first before calling chado_generate_var()
  181. if (isset($identifiers['synonym'])) {
  182. $synonym = $identifiers['synonym']['name'];
  183. $values = array('synonym' => $synonym);
  184. if (isset($identifiers['synonym']['cv_id'])) {
  185. $values['cvterm_id'] = array('cv_id' => $identifiers['synonym']['cv_id']);
  186. }
  187. if (isset($identifiers['synonym']['cv_name'])) {
  188. $values['cvterm_id'] = array('cv_id' => array('name' => $identifiers['synonym']['cv_name']));
  189. }
  190. $options = array(
  191. 'case_insensitive_columns' => array('name')
  192. );
  193. $result = chado_select_record('cvtermsynonym', array('cvterm_id'), $values, $options);
  194. // if the synonym doens't exist or more than one record is returned then return false
  195. if (count($result) == 0) {
  196. return FALSE;
  197. }
  198. if (count($result) > 1) {
  199. return FALSE;
  200. }
  201. $identifiers = array('cvterm_id' => $result[0]->cvterm_id);
  202. }
  203. // If one of the identifiers is property then use chado_get_record_with_property()
  204. if (isset($identifiers['property'])) {
  205. $property = $identifiers['property'];
  206. unset($identifiers['property']);
  207. $cvterm = chado_get_record_with_property(
  208. array('table' => 'cvterm', 'base_records' => $identifiers),
  209. array('type_name' => $property),
  210. $options
  211. );
  212. }
  213. // Else we have a simple case and we can just use chado_generate_var to get the cvterm
  214. else {
  215. // Try to get the cvterm
  216. $cvterm = chado_generate_var('cvterm', $identifiers, $options);
  217. }
  218. // Ensure the cvterm is singular. If it's an array then it is not singular
  219. if (is_array($cvterm)) {
  220. tripal_report_error(
  221. 'tripal_cv_api',
  222. TRIPAL_ERROR,
  223. "tripal_get_cvterm: The identifiers you passed in were not unique. You passed in %identifier.",
  224. array(
  225. '%identifier'=> print_r($identifiers, TRUE)
  226. )
  227. );
  228. }
  229. // Report an error if $cvterm is FALSE since then chado_generate_var has failed
  230. elseif ($cvterm === FALSE) {
  231. tripal_report_error(
  232. 'tripal_cv_api',
  233. TRIPAL_ERROR,
  234. "tripal_get_cvterm: chado_generate_var() failed to return a cvterm based on the identifiers
  235. you passed in. You should check that your identifiers are correct, as well as, look
  236. for a chado_generate_var error for additional clues. You passed in %identifier.",
  237. array(
  238. '%identifier'=> print_r($identifiers, TRUE)
  239. )
  240. );
  241. }
  242. // Else, as far we know, everything is fine so give them their cvterm :)
  243. else {
  244. return $cvterm;
  245. }
  246. }
  247. /**
  248. * Create an options array to be used in a form element
  249. * which provides a list of all chado cvterms
  250. *
  251. * @param $cv_id
  252. * The chado cv_id; only cvterms with the supplied cv_id will be returned
  253. *
  254. * @return
  255. * An associative array with the cvterm_id's as keys. The first
  256. * element in the array has a key of '0' and a value of 'Select a Type'
  257. *
  258. * @ingroup tripal_cv_api
  259. */
  260. function tripal_get_cvterm_select_options($cv_id) {
  261. $columns = array('cvterm_id', 'name');
  262. $values = array('cv_id' => $cv_id);
  263. $s_options = array('order_by' => array('name' => 'ASC'));
  264. $cvterms = chado_select_record('cvterm', $columns, $values, $s_options);
  265. $options = array();
  266. $options[0] = 'Select a Type';
  267. foreach ($cvterms as $cvterm) {
  268. $options[$cvterm->cvterm_id] = $cvterm->name;
  269. }
  270. return $options;
  271. }
  272. /**
  273. * Updates the cvtermpath table of Chado for the specified CV.
  274. *
  275. * @param $cv_id
  276. * The chado cv_id;
  277. * @param $job_id
  278. * This function is intended to be used with the Tripal Jobs API.
  279. * When this function is called as a job the $job_id is automatically
  280. * passed to this function.
  281. * @return
  282. * TRUE on success FALSE on failure
  283. *
  284. * @ingroup tripal_cv_api
  285. */
  286. function tripal_update_cvtermpath($cv_id, $job_id = NULL) {
  287. // TODO: need better error checking in this function
  288. // first get the controlled vocabulary name:
  289. $sql = "SELECT * FROM {cv} WHERE cv_id = :cv_id";
  290. $cv = chado_query($sql, array(':cv_id' => $cv_id))->fetchObject();
  291. print "\nUpdating cvtermpath for $cv->name...\n";
  292. $previous = chado_set_active('chado');
  293. try {
  294. $sql = "SELECT * FROM fill_cvtermpath(:name)";
  295. db_query($sql, array(':name' => $cv->name));
  296. chado_set_active($previous);
  297. }
  298. catch (Exception $e) {
  299. chado_set_active($previous);
  300. $error = $e->getMessage();
  301. tripal_report_error('tripal_cv', TRIPAL_ERROR, "Could not fill cvtermpath table: @error", array('@error' => $error));
  302. return FALSE;
  303. }
  304. return TRUE;
  305. }
  306. /**
  307. * Adds a controlled vocabular to the CV table of Chado.
  308. *
  309. * @param $name
  310. * The name of the controlled vocabulary. These are typically all lower case
  311. * with no special characters other than an undrescore (for spaces).
  312. * @param $comment
  313. * A description or definition of the vocabulary.
  314. *
  315. * @return
  316. * An object populated with fields from the newly added database.
  317. *
  318. * @ingroup tripal_cv_api
  319. */
  320. function tripal_insert_cv($name, $definition) {
  321. // insert/update values
  322. $ins_values = array(
  323. 'name' => $name,
  324. 'definition' => $definition
  325. );
  326. // see if the CV (default-namespace) exists already in the database
  327. $sel_values = array('name' => $name);
  328. $sel_options = array('statement_name' => 'sel_cv_na');
  329. $results = chado_select_record('cv', array('*'), $sel_values, $sel_options);
  330. // if it does not exists then add it
  331. if (count($results) == 0) {
  332. $ins_options = array('statement_name' => 'ins_cv_nade');
  333. $success = chado_insert_record('cv', $ins_values, $ins_options);
  334. if (!$success) {
  335. tripal_report_error('tripal_cv', TRIPAL_WARNING, "Failed to create the CV record", NULL);
  336. return FALSE;
  337. }
  338. $results = chado_select_record('cv', array('*'), $sel_values, $sel_options);
  339. }
  340. // if it already exists then do an update
  341. else {
  342. $upd_options = array('statement_name' => 'upd_cv_nade');
  343. $success = chado_update_record('cv', $sel_values, $ins_values, $upd_options);
  344. if (!$success) {
  345. tripal_report_error('tripal_cv', TRIPAL_WARNING, "Failed to update the CV record", NULL);
  346. return FALSE;
  347. }
  348. $results = chado_select_record('cv', array('*'), $sel_values, $sel_options);
  349. }
  350. // return the cv object
  351. return $results[0];
  352. }
  353. /**
  354. * Add's a controlled vocabulary term to the cvterm table.
  355. *
  356. * If the parent CV does not exist then
  357. * that too is added to the CV table. If the cvterm is a relationship term
  358. * then the $is_relationship argument should be set. The function will try
  359. * to first find the relationship in the relationship ontology for updating and
  360. * if it can't be found will add the relationship to the __global CV. All terms
  361. * must also have a corresponding database. This is specified in the term's
  362. * ID just before the colon (e.g. GO:003824). If the database does not exist
  363. * in the DB table then it will be added automatically. The accession (the
  364. * value just after the colon in the term's ID) will be added to the dbxref
  365. * table. If the CVterm already exists and $update is set (default) then the
  366. * cvterm is updated. If the CVTerm already exists and $update is not set, then
  367. * no changes are made and the CVTerm object is returned.
  368. *
  369. * @param $term
  370. * An associative array with the following keys:
  371. * - id: the term accession. must be of the form <DB>:<ACCESSION>, where <DB> is the
  372. * name of the database to which the cvterm belongs and the <ACCESSION> is the
  373. * term's accession number in the database.
  374. * - name: the name of the term. usually meant to be human-readable.
  375. * - namespace: the CV name for the term. DEPRECATED. Please use cv_name instead.
  376. * - is_obsolete: is present and set to 1 if the term is defunct
  377. * - definition: the definition of the term
  378. * - cv_name: The CV name to which the term belongs. If this arugment is null or not
  379. * provided then the function tries to find a record in the CV table with the same
  380. * name provided in the $term[namespace]. If this field is provided then it
  381. * overrides what the value in $term[namespace]
  382. * - is_relationship: If this term is a relationship term then this value should be 1.
  383. * - db_name: In some cases the database name will not be part of the $term['id'] and it
  384. * needs to be explicitly set. Use this argument only if the database name
  385. * cannot be specififed in the term ID (e.g. <DB>:<ACCESSION>).
  386. * @param $options
  387. * An associative array with the following keys:
  388. * - update_existing: By default this is TRUE. If the term exists it is automatically updated.
  389. *
  390. * @return
  391. * A CVTerm object
  392. *
  393. * @ingroup tripal_cv_api
  394. */
  395. function tripal_insert_cvterm($term, $options = array()) {
  396. // Set Defaults
  397. if (isset($term['cv_name'])) {
  398. $defaultcv = $term['cv_name'];
  399. }
  400. else {
  401. $defaultcv = '_global';
  402. }
  403. if (isset($term['is_relationship'])) {
  404. $is_relationship = $term['is_relationship'];
  405. }
  406. else {
  407. $is_relationship = 0;
  408. }
  409. if (isset($term['db_name'])) {
  410. $dbname = $term['db_name'];
  411. }
  412. else {
  413. $dbname = 'internal';
  414. }
  415. if (isset($options['update_existing'])) {
  416. $update = $options['update_existing'];
  417. }
  418. else {
  419. $update = 1;
  420. }
  421. // get the term properties
  422. $id = (isset($term['id'])) ? $term['id'] : '';
  423. $name = '';
  424. $cvname = '';
  425. $definition = '';
  426. $is_obsolete = 0;
  427. $accession = '';
  428. if (array_key_exists('name', $term)) {
  429. $name = $term['name'];
  430. }
  431. else {
  432. $name = $id;
  433. }
  434. if (array_key_exists('namespace', $term)) {
  435. $cvname = $term['namespace'];
  436. }
  437. else {
  438. $cvname = $defaultcv;
  439. }
  440. if (array_key_exists('definition', $term)) {
  441. $definition = preg_replace('/^\"(.*)\"/', '\1', $term['definition']);
  442. }
  443. else {
  444. $definition = '';
  445. }
  446. if (array_key_exists('is_obsolete', $term)) {
  447. $is_obsolete = $term['is_obsolete'];
  448. if (strcmp($is_obsolete, 'true') == 0) {
  449. $is_obsolete = 1;
  450. }
  451. }
  452. if (!$name and !$id) {
  453. tripal_report_error('tripal_cv', TRIPAL_WARNING, "Cannot find cvterm without 'id' or 'name'", NULL);
  454. return 0;
  455. }
  456. if (!$id) {
  457. $id = $name;
  458. }
  459. // get the accession and the database from the cvterm id
  460. if ($dbname) {
  461. $accession = $id;
  462. }
  463. if (preg_match('/^.+?:.*$/', $id)) {
  464. $accession = preg_replace('/^.+?:(.*)$/', '\1', $id);
  465. $dbname = preg_replace('/^(.+?):.*$/', '\1', $id);
  466. }
  467. // check that we have a database name, give a different message if it's a relationship
  468. if ($is_relationship and !$dbname) {
  469. tripal_report_error('tripal_cv', TRIPAL_WARNING, "A database name is not provided for this relationship term: $id", NULL);
  470. return 0;
  471. }
  472. if (!$is_relationship and !$dbname) {
  473. tripal_report_error('tripal_cv', TRIPAL_WARNING, "A database identifier is missing from the term: $id", NULL);
  474. return 0;
  475. }
  476. // make sure the CV name exists
  477. $cv = tripal_get_cv(array('name' => $cvname));
  478. if (!$cv) {
  479. $cv = tripal_insert_cv($cvname, '');
  480. }
  481. if (!$cv) {
  482. tripal_report_error('tripal_cv', TRIPAL_WARNING, "Cannot find namespace '$cvname' when adding/updating $id", NULL);
  483. return 0;
  484. }
  485. // this SQL statement will be used a lot to find a cvterm so just set it
  486. // here for easy reference below. Because CV terms can change their names
  487. // but accessions don't change, the following SQL finds cvterms based on
  488. // their accession rather than the name
  489. $cvtermsql = "
  490. SELECT CVT.name, CVT.cvterm_id, CV.cv_id, CV.name as cvname,
  491. DB.name as dbname, DB.db_id, DBX.accession
  492. FROM {cvterm} CVT
  493. INNER JOIN {dbxref} DBX on CVT.dbxref_id = DBX.dbxref_id
  494. INNER JOIN {db} DB on DBX.db_id = DB.db_id
  495. INNER JOIN {cv} CV on CV.cv_id = CVT.cv_id
  496. WHERE DBX.accession = :accession and DB.name = :name
  497. ";
  498. // add the database. The function will just return the DB object if the
  499. // database already exists.
  500. $db = tripal_get_db(array('name' => $dbname));
  501. if (!$db) {
  502. $db = tripal_insert_db(array('name' => $dbname));
  503. }
  504. if (!$db) {
  505. tripal_report_error('tripal_cv', TRIPAL_WARNING, "Cannot find database '$dbname' in Chado.", NULL);
  506. return 0;
  507. }
  508. // the cvterm table has two unique dependencies. We need to check both.
  509. // first check the (name, cv_id, is_obsolete) constraint
  510. $values = array(
  511. 'name' => $name,
  512. 'is_obsolete' => $is_obsolete,
  513. 'cv_id' => array(
  514. 'name' => $cvname,
  515. ),
  516. );
  517. $options = array('statement_name' => 'sel_cvterm_c1');
  518. $result = chado_select_record('cvterm', array('*'), $values, $options);
  519. // if the constraint is met then let's check it to see if
  520. // the database name matches the one we have been provided
  521. if (count($result) == 1) {
  522. $cvterm = $result[0];
  523. // get the dbxref record
  524. $values = array('dbxref_id' => $cvterm->dbxref_id);
  525. $options = array('statement_name' => 'sel_dbxref_id');
  526. $result = chado_select_record('dbxref', array('*'), $values, $options);
  527. $dbxref = $result[0];
  528. // get the db
  529. $values = array('db_id' => $dbxref->db_id);
  530. $options = array('statement_name' => 'sel_db_id');
  531. $result = chado_select_record('db', array('*'), $values, $options);
  532. $db_check = $result[0];
  533. // the database name for this existing term does not match that of the
  534. // one provided to this function. The CV name matches otherwise we
  535. // wouldn't have made it this far. So, let's swap the database for
  536. // this term
  537. if ($db_check->name != $db->name) {
  538. // look to see if the correct dbxref record already exists for this database
  539. $values = array(
  540. 'db_id' => $db->db_id,
  541. 'accession' => $accession,
  542. );
  543. $options = array('statement_name' => 'sel_dbxref_idac');
  544. $result = chado_select_record('dbxref', array('*'), $values, $options);
  545. // if we already have a good dbxref then we want to update our cvterm
  546. // to use this dbxref
  547. if (count($result) > 0) {
  548. $dbxref = $result[0];
  549. $match = array('cvterm_id' => $cvterm->cvterm_id);
  550. $values = array('dbxref_id' => $dbxref->dbxref_id);
  551. $options = array('statement_name' => 'upd_cvterm_db');
  552. $success = chado_update_record('cvterm', $match, $values, $options);
  553. if (!$success) {
  554. tripal_report_error('tripal_cv', TRIPAL_WARNING, "Failed to correct the dbxref id for the cvterm " .
  555. "'$name' (id: $accession), for database $dbname", NULL);
  556. return 0;
  557. }
  558. }
  559. // if we don't have the record then we want to delete our cvterm and let the code
  560. // below recreate it with the correct info
  561. else {
  562. $match = array('cvterm_id' => $cvterm->cvterm_id);
  563. $options = array('statement_name' => 'del_cvterm_cv');
  564. chado_delete_record('cvterm', $match, $options);
  565. }
  566. }
  567. // check that the accession matches. Sometimes an OBO can define the same term
  568. // multiple times but with different accessions. If this is the case we
  569. // can't do an insert or it will violate the constraint in the cvterm table.
  570. // so we'll need to add the record to the cvterm_dbxref table instead
  571. if ($dbxref->accession != $accession) {
  572. // get/add the dbxref fort his term
  573. $dbxref_new = tripal_insert_dbxref(array(
  574. 'db_id' => $db->db_id,
  575. 'accession' => $accession
  576. ));
  577. if (!$dbxref_new) {
  578. tripal_report_error('tripal_cv', TRIPAL_WARNING, "Failed to find or insert the dbxref record for cvterm, " .
  579. "$name (id: $accession), for database $dbname", NULL);
  580. return 0;
  581. }
  582. // check to see if the cvterm_dbxref record already exists
  583. $values = array(
  584. 'cvterm_id' => $cvterm->cvterm_id,
  585. 'dbxref_id' => $dbxref_new->dbxref_id,
  586. 'is_for_definition' => 1,
  587. );
  588. $options = array('statement_name' => 'sel_cvtermdbxref_cvdbis');
  589. $result = chado_select_record('cvterm_dbxref', array('*'), $values, $options);
  590. // if the cvterm_dbxref record does not exists then add it
  591. if (count($result)==0) {
  592. $options = array(
  593. 'statement_name' => 'ins_cvtermdbxref_cvdbis',
  594. 'return_record' => FALSE,
  595. );
  596. $success = chado_insert_record('cvterm_dbxref', $values, $options);
  597. if (!$success) {
  598. tripal_report_error('tripal_cv', TRIPAL_WARNING, "Failed to find or insert the cvterm_dbxref record for a " .
  599. "duplicated cvterm: $name (id: $accession), for database $dbname", NULL);
  600. return 0;
  601. }
  602. }
  603. // get the original cvterm with the same name and return that.
  604. $result = chado_query($cvtermsql, array(':accession' => $dbxref->accession, ':name' => $dbname));
  605. $cvterm = $result->fetchObject();
  606. return $cvterm;
  607. }
  608. // continue on, we've fixed the record if the db_id did not match,
  609. // we can now perform and updated if we need to.
  610. }
  611. // get the CVterm record
  612. $result = chado_query($cvtermsql, array(':accession' => $accession, ':name' => $dbname));
  613. $cvterm = $result->fetchObject();
  614. if (!$cvterm) {
  615. // check to see if the dbxref exists if not, add it
  616. $dbxref = tripal_insert_dbxref(array(
  617. 'db_id' => $db->db_id,
  618. 'accession' => $accession
  619. ));
  620. if (!$dbxref) {
  621. tripal_report_error('tripal_cv', TRIPAL_WARNING, "Failed to find or insert the dbxref record for cvterm, " .
  622. "$name (id: $accession), for database $dbname", NULL);
  623. return 0;
  624. }
  625. // check to see if the dbxref already has an entry in the cvterm table
  626. // this is the second constraint in the cvterm table
  627. $values = array('dbxref_id' => $dbxref->dbxref_id);
  628. $options = array('statement_name' => 'sel_cvterm_db');
  629. $check = chado_select_record('cvterm', array('cvterm_id'), $values, $options);
  630. if (count($check) == 0) {
  631. // now add the cvterm
  632. $ins_values = array(
  633. 'cv_id' => $cv->cv_id,
  634. 'name' => $name,
  635. 'definition' => $definition,
  636. 'dbxref_id' => $dbxref->dbxref_id,
  637. 'is_obsolete' => $is_obsolete,
  638. 'is_relationshiptype' => $is_relationship,
  639. );
  640. $ins_options = array('statement_name' => 'ins_cvterm_all');
  641. $success = chado_insert_record('cvterm', $ins_values, $ins_options);
  642. if (!$success) {
  643. if (!$is_relationship) {
  644. tripal_report_error('tripal_cv', TRIPAL_WARNING, "Failed to insert the term: $name ($dbname)", NULL);
  645. return 0;
  646. }
  647. else {
  648. tripal_report_error('tripal_cv', TRIPAL_WARNING, "Failed to insert the relationship term: $name (cv: " . $cvname . " db: $dbname)", NULL);
  649. return 0;
  650. }
  651. }
  652. }
  653. // this dbxref already exists in the cvterm table
  654. else {
  655. tripal_report_error('tripal_cv', TRIPAL_WARNING, "The dbxref already exists for another cvterm record: $name (cv: " . $cvname . " db: $dbname)", NULL);
  656. return 0;
  657. }
  658. $result = chado_query($cvtermsql, array(':accession' => $accession, ':name' => $dbname));
  659. $cvterm = $result->fetchObject();
  660. }
  661. // upate the cvterm
  662. elseif ($update) {
  663. $match = array('cvterm_id' => $cvterm->cvterm_id);
  664. $upd_values = array(
  665. 'name' => $name,
  666. 'definition' => $definition,
  667. 'is_obsolete' => $is_obsolete,
  668. 'is_relationshiptype' => $is_relationship,
  669. );
  670. $upd_options = array('statement_name' => 'upd_cvterm_nadeisis');
  671. $success = chado_update_record('cvterm', $match, $upd_values, $upd_options);
  672. if (!$success) {
  673. tripal_report_error('tripal_cv', TRIPAL_WARNING, "Failed to update the term: $name", NULL);
  674. return 0;
  675. }
  676. $result = chado_query($cvtermsql, array(':accession' => $accession, ':name' => $dbname));
  677. $cvterm = $result->fetchObject();
  678. }
  679. else {
  680. // do nothing, we have the cvterm but we don't want to update
  681. }
  682. // return the cvterm
  683. return $cvterm;
  684. }
  685. /**
  686. * This function allows other modules to programatically
  687. * submit an ontology for loading into Chado.
  688. *
  689. * This function will add a job to the Jobs subsystem for parsing the ontology.
  690. * You can either pass a known OBO ID to the function or the URL
  691. * or full path the the ontology file. If a URL or file name is
  692. * passed then the $obo_name argument must also be provided. If
  693. * this is the first time the ontology has been provided to Tripal
  694. * then it will be added to the database and will be assigned a
  695. * unique OBO ID.
  696. *
  697. * @param $obo_id
  698. * If the ontology is already loaded into the Tripal tables then
  699. * use this argument to specify the unique ID for the ontology
  700. * that will be loaded
  701. * @param $obo_name
  702. * If the OBO has not been added before then use this argument
  703. * to specify the human readable name of the ontology.
  704. * @param $obo_url
  705. * If the OBO to be loaded is located on a remote server then
  706. * use this argument to provide the URL.
  707. * @param $obo_file
  708. * If the OBO is housed on the local file system of the server then
  709. * use this argument to specify the full path.
  710. *
  711. * @return
  712. * returns the job_id of the submitted job or FALSE if the job was not added
  713. *
  714. * @ingroup tripal_cv_api
  715. */
  716. function tripal_submit_obo_job($obo) {
  717. global $user;
  718. // Set Defaults
  719. $obo['obo_id'] = (isset($obo['obo_id'])) ? $obo['obo_id'] : NULL;
  720. $obo['name'] = (isset($obo['name'])) ? $obo['name'] : NULL;
  721. $obo['url'] = (isset($obo['url'])) ? $obo['url'] : NULL;
  722. $obo['file'] = (isset($obo['file'])) ? $obo['file'] : NULL;
  723. if ($obo['obo_id']) {
  724. $sql = "SELECT * FROM {tripal_cv_obo} WHERE obo_id = :obo_id";
  725. $result = db_query($sql, array(':obo_id' => $obo['obo_id']))->fetchObject();
  726. $args = array($result->obo_id);
  727. return tripal_add_job("Load OBO " . $result->name, 'tripal_cv',
  728. "tripal_cv_load_obo_v1_2_id", $args, $user->uid);
  729. }
  730. else {
  731. if ($obo['url']) {
  732. $args = array($obo['name'], $obo['url']);
  733. return tripal_add_job("Load OBO " . $obo['name'], 'tripal_cv',
  734. "tripal_cv_load_obo_v1_2_url", $args, $user->uid);
  735. }
  736. elseif ($obo['file']) {
  737. $args = array($obo['name'], $obo['file']);
  738. return tripal_add_job("Load OBO " . $obo['name'], 'tripal_cv',
  739. "tripal_cv_load_obo_v1_2_file", $args, $user->uid);
  740. }
  741. }
  742. return FALSE;
  743. }
  744. /**
  745. * Add the OBO to the tripal_cv_obo table in the Drupal database.
  746. *
  747. * If the OBO name already exists in the table then the path is updated.
  748. *
  749. * @param $name
  750. * The human readable name of this ontology
  751. * @param $path
  752. * The file path or URL of the ontology
  753. *
  754. * @return
  755. * Returns the ontology ID
  756. *
  757. * @ingroup tripal_cv_api
  758. */
  759. function tripal_insert_obo($name, $path) {
  760. // make sure an OBO with the same name doesn't already exist
  761. $obo_id = db_select('tripal_cv_obo', 'tco')
  762. ->fields('tco', array('obo_id'))
  763. ->condition('name', $name)
  764. ->execute()
  765. ->fetchField();
  766. if ($obo_id) {
  767. db_update('tripal_cv_obo')
  768. ->fields(array(
  769. 'path' => $path,
  770. ))
  771. ->condition('name', $name)
  772. ->execute();
  773. return $obo_id;
  774. }
  775. else {
  776. $record = new stdClass;
  777. $record->name = $name;
  778. $record->path = $path;
  779. drupal_write_record('tripal_cv_obo', $record);
  780. return $record->obo_id;
  781. }
  782. }
  783. /**
  784. * This function is intended to be used in autocomplete forms
  785. * for searching for CV terms that begin with the provided string
  786. *
  787. * @param $cv_id
  788. * The CV ID in which to search for the term
  789. * @param $string
  790. * The string to search for
  791. *
  792. * @return
  793. * A json array of terms that begin with the provided string
  794. *
  795. * @ingroup tripal_cv_api
  796. */
  797. function tripal_autocomplete_cvterm($cv_id, $string = '') {
  798. $sql = "
  799. SELECT cvterm_id, name
  800. FROM {cvterm}
  801. WHERE cv_id = :cv_id and lower(name) like lower(:name)
  802. ORDER by name
  803. LIMIT 25 OFFSET 0
  804. ";
  805. $results = chado_query($sql, array(':cv_id' => $cv_id, ':name' => $string . '%'));
  806. $items = array();
  807. foreach ($results as $term) {
  808. $items[$term->name] = $term->name;
  809. }
  810. drupal_json_output($items);
  811. }
  812. /**
  813. * Add a record to a cvterm linking table (ie: feature_cvterm)
  814. *
  815. * @param $basetable
  816. * The base table to which the cvterm should be linked/associated. Thus to associate a
  817. * cvterm to a feature the basetable=feature and cvterm_id is added to the feature_cvterm table.
  818. * @param $record_id
  819. * The primary key of the basetable to associate the cvterm with. This should be in integer.
  820. * @param $cvterm
  821. * An associative array describing the cvterm. Valid keys include:
  822. * - name: the name for the cvterm,
  823. * - cv_name: the name of the cv the cvterm belongs to.
  824. * - cv_id: the primary key of the cv the cvterm belongs to.
  825. * @param $options
  826. * An associative array of options. Valid keys include:
  827. * - insert_cvterm: Insert the cvterm if it doesn't already exist. FALSE is the default
  828. *
  829. * @ingroup tripal_db_api
  830. */
  831. function tripal_associate_cvterm($basetable, $record_id, $cvterm, $options = array()) {
  832. $linking_table = $basetable . '_cvterm';
  833. $foreignkey_name = $basetable . '_id';
  834. // Default Values
  835. $options['insert_cvterm'] = (isset($options['insert_cvterm'])) ? $options['insert_cvterm'] : FALSE;
  836. // If the cvterm_id is not set then find the cvterm record using the name and cv_id
  837. if (!isset($cvterm['cvterm_id'])) {
  838. $values = array(
  839. 'name' => $cvterm['name'],
  840. );
  841. if (isset($cvterm['cv_id'])) {
  842. $values['cv_id'] = $cvterm['cv_id'];
  843. }
  844. elseif (isset($cvterm['cv_name'])) {
  845. $values['cv_id'] = array(
  846. 'name' => $cvterm['cv_name']
  847. );
  848. }
  849. else {
  850. tripal_report_error('tripal_cv_api', TRIPAL_WARNING,
  851. "tripal_associate_cvterm: The cvterm needs to have either the cv_name or cv_id
  852. supplied. You were trying to associate a cvterm with the %base %record_id
  853. and supplied the cvterm values: %cvterm.",
  854. array('%base' => $basetable, '%record_id' => $record_id, '%cvterm' => print_r($cvterm,TRUE))
  855. );
  856. return FALSE;
  857. }
  858. // Get the cvterm. If it doesn't exist then add it if the option
  859. // 'insert_cvterm' is set.
  860. $select = chado_select_record('cvterm', array('*'), $values);
  861. if ($select) {
  862. $cvterm['cvterm_id'] = $select[0]->cvterm_id;
  863. }
  864. elseif ($options['insert_cvterm']) {
  865. // Insert the cvterm
  866. $insert = tripal_insert_cvterm($values);
  867. if (isset($insert->cvterm_id)) {
  868. $cvterm['cvterm_id'] = $insert->cvterm_id;
  869. }
  870. else {
  871. tripal_report_error('tripal_cv_api', TRIPAL_WARNING,
  872. "tripal_associate_cvterm: Unable to insert the cvterm using the cvterm values: %cvterm.",
  873. array('%cvterm' => print_r($cvterm,TRUE))
  874. );
  875. return FALSE;
  876. }
  877. }
  878. else {
  879. tripal_report_error('tripal_api', TRIPAL_WARNING,
  880. "tripal_associate_cvterm: The cvterm doesn't already exist. You supplied the cvterm values: %cvterm.",
  881. array('%cvterm' => print_r($cvterm,TRUE))
  882. );
  883. return FALSE;
  884. }
  885. }
  886. // Now add the link between the record & cvterm
  887. if ($cvterm['cvterm_id'] > 0) {
  888. $values = array(
  889. 'cvterm_id' => $cvterm['cvterm_id'],
  890. $foreignkey_name => $record_id,
  891. 'pub_id' => 1,
  892. );
  893. // Check if the cvterm is already associated. If so, don't re-add it.
  894. $result = chado_select_record($linking_table, array('*'), $values);
  895. if (!$result) {
  896. $success = chado_insert_record($linking_table, $values);
  897. if (!$success) {
  898. tripal_report_error('tripal_api', TRIPAL_WARNING,
  899. "Failed to insert the %base record %term",
  900. array('%base' => $linking_table, '%term' => $cvterm['name'])
  901. );
  902. return FALSE;
  903. }
  904. $result = chado_select_record($linking_table, array('*'), $values);
  905. }
  906. if (isset($result[0])) {
  907. return $result[0];
  908. }
  909. else {
  910. return FALSE;
  911. }
  912. }
  913. return FALSE;
  914. }
  915. /**
  916. * This function sets the default vocabulary for a given table and field.
  917. *
  918. * @param $table
  919. * The name of the table that contains a field with a foreign key
  920. * relationship to the cvterm table
  921. * @param $field
  922. * The table field name that has the foreign key relationship to the
  923. * cvterm table for which the default vocabulary will be set
  924. * @param $cv_name
  925. * The name of the vocabulary
  926. *
  927. * @return
  928. * TRUE if set, FALSE if an error occured
  929. */
  930. function tripal_set_default_cv($table, $field, $cv_name, $cv_id = FALSE) {
  931. // Get the CV object
  932. if ($cv_id) {
  933. $cv = tripal_get_cv(array('cv_id' => $cv_id));
  934. }
  935. else {
  936. $cv = tripal_get_cv(array('name' => $cv_name));
  937. }
  938. if ($cv) {
  939. // first delete any entries for this table and field
  940. $num_deleted = db_delete('tripal_cv_defaults')
  941. ->condition('table_name', $table)
  942. ->condition('field_name', $field)
  943. ->execute();
  944. // now add the default value
  945. $cv_default_id = db_insert('tripal_cv_defaults')
  946. ->fields(array(
  947. 'table_name' => $table,
  948. 'field_name' => $field,
  949. 'cv_id' => $cv->cv_id,
  950. ))
  951. ->execute();
  952. if (!$cv_default_id) {
  953. tripal_report_error('tripal_cv', TRIPAL_WARNING,
  954. "Cannot set default vocabulary for %table.%field. Check the error logs.",
  955. array('%table' => $table, '%field' => $field));
  956. return FALSE;
  957. }
  958. }
  959. else {
  960. tripal_report_error('tripal_cv', TRIPAL_WARNING,
  961. "Cannot set default vocabulary for %table.%field. The vocabulary name, '%cvname', doesn't exist.",
  962. array('%table' => $table, '%field' => $field, '%cvname' => $cv_name));
  963. return FALSE;
  964. }
  965. }
  966. /**
  967. * Retreives the default vocabulary for a given table and field
  968. * @param $table
  969. * The name of the table that contains a field with a foreign key
  970. * relationship to the cvterm table
  971. * @param $field
  972. * The table field name that has the foreign key relationship to the
  973. * cvterm table for which the default vocabulary will be set
  974. *
  975. * @return
  976. * The cv object of the default vocabulary or an empty array if not
  977. * available.
  978. */
  979. function tripal_get_default_cv($table, $field) {
  980. $sql = "
  981. SELECT cv_id
  982. FROM {tripal_cv_defaults}
  983. WHERE table_name = :table and field_name = :field
  984. ";
  985. $cv_id = db_query($sql, array(':table' => $table, ':field' => $field))->fetchField();
  986. return tripal_get_cv(array('cv_id' => $cv_id));
  987. }
  988. /**
  989. * Create an options array to be used in a form element
  990. * which provides a list of all chado cvterms. Unlike the
  991. * tripal_get_cvterm_select_option, this function retreives the cvterms using
  992. * the default vocabulary set for a given table and field. It will also
  993. * notify the administrative user if a default vocabulary is missing for the
  994. * field and if the vocabulary is empty.
  995. *
  996. * @param $table
  997. * The name of the table that contains the field with a foreign key
  998. * relationship to the cvterm table
  999. * @param $field
  1000. * The table field name that has the foreign key relationship to the
  1001. * cvterm table for which the default vocabulary will be set
  1002. * @param $field_desc
  1003. * A human readable descriptive name for the field
  1004. *
  1005. * @return
  1006. * An array(cvterm_id => name)
  1007. * for each cvterm in the chado cvterm table where cv_id=that supplied
  1008. */
  1009. function tripal_get_cvterm_default_select_options($table, $field, $field_desc) {
  1010. $default_cv = tripal_get_default_cv($table, $field);
  1011. $options = array();
  1012. if ($default_cv) {
  1013. $options = tripal_get_cvterm_select_options($default_cv->cv_id);
  1014. if (count($options) == 0) {
  1015. tripal_set_message('There are no ' . $field_desc . '. Please ' .
  1016. l('add terms',
  1017. 'admin/tripal/chado/tripal_cv/cv/' .$default_cv->cv_id. '/cvterm/add',
  1018. array('attributes' => array('target' => '_blank'))) . ' to the ' .
  1019. $default_cv->name .' vocabulary.',
  1020. TRIPAL_WARNING);
  1021. }
  1022. }
  1023. else {
  1024. tripal_set_message('There is not a default vocabulary set for ' . $field_desc . '. '.
  1025. 'Please set one using the ' .
  1026. l('vocabulary defaults configuration page',
  1027. 'admin/tripal/chado/tripal_cv/defaults',
  1028. array('attributes' => array('target' => '_blank'))) . '.',
  1029. TRIPAL_WARNING);
  1030. }
  1031. return $options;
  1032. }