tripal_chado.db.api.inc 19 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609
  1. <?php
  2. /**
  3. * @file
  4. * Provides API functions specificially for managing external database reference
  5. * records in Chado.
  6. */
  7. /**
  8. * @defgroup tripal_chado_database_api Chado DB
  9. * @ingroup tripal_chado_api
  10. * @{
  11. * External databases can be used to indicate the source for a variety of data.
  12. * The most common use is with controlled vocabularies (CV). Chado expects that
  13. * every CV have an external database record, where the database name must be
  14. * the short name of the CV. In other cases, records such as features, stocks,
  15. * libraries, etc., can also be present in remote databases and these
  16. * associations can be made through dbxref linker tables. The API functions
  17. * provided here provide tools to easily work with external databases.
  18. * @}
  19. */
  20. /**
  21. * Retrieves a chado db variable.
  22. *
  23. * Example Usage:
  24. * @code
  25. * $select_values = array(
  26. * 'name' => 'SOFP'
  27. * );
  28. * $db_object = chado_get_db($select_values);
  29. * @endcode
  30. *
  31. * The above code selects the SOFP db and returns the following object:
  32. * @code
  33. * $db_object = stdClass Object (
  34. * [db_id] => 49
  35. * [name] => SOFP
  36. * [description] =>
  37. * [urlprefix] =>
  38. * [url] =>
  39. * );
  40. * @endcode
  41. *
  42. * @param $identifier
  43. * An array with the key stating what the identifier is. Supported keys (only
  44. * on of the following unique keys is required):
  45. * - db_id: the chado db.db_id primary key.
  46. * - name: the chado db.name field (assume unique).
  47. * @param $options
  48. * An array of options. Supported keys include:
  49. * - Any keys supported by chado_generate_var(). See that function
  50. * definition for additional details.
  51. *
  52. * NOTE: the $identifier parameter can really be any array similar to $values
  53. * passed into chado_select_record(). It should fully specify the db record to
  54. * be returned.
  55. *
  56. * @return
  57. * If unique values were passed in as an identifier then an object describing
  58. * the cv will be returned (will be a chado variable from
  59. * chado_generate_var()). Otherwise, an array of objects will be returned.
  60. *
  61. * @ingroup tripal_chado_database_api
  62. */
  63. function chado_get_db($identifiers, $options = array()) {
  64. // Set Defaults.
  65. if (!isset($options['include_fk'])) {
  66. // Tells chado_generate_var not to follow any foreign keys.
  67. $options['include_fk'] = array();
  68. }
  69. // Error Checking of parameters.
  70. if (!is_array($identifiers)) {
  71. tripal_report_error(
  72. 'tripal_chado_database_api',
  73. TRIPAL_ERROR,
  74. "chado_get_db: The identifier passed in is expected to be an array with the key
  75. matching a column name in the db table (ie: db_id or name). You passed in %identifier.",
  76. array(
  77. '%identifier'=> print_r($identifiers, TRUE)
  78. )
  79. );
  80. }
  81. elseif (empty($identifiers)) {
  82. tripal_report_error(
  83. 'tripal_chado_database_api',
  84. TRIPAL_ERROR,
  85. "chado_get_db: You did not pass in anything to identify the db you want. The identifier
  86. is expected to be an array with the key matching a column name in the db table
  87. (ie: db_id or name). You passed in %identifier.",
  88. array(
  89. '%identifier'=> print_r($identifiers, TRUE)
  90. )
  91. );
  92. }
  93. // Try to get the db.
  94. $db = chado_generate_var(
  95. 'db',
  96. $identifiers,
  97. $options
  98. );
  99. // Ensure the db is singular. If it's an array then it is not singular.
  100. if (is_array($db)) {
  101. tripal_report_error(
  102. 'tripal_chado_database_api',
  103. TRIPAL_ERROR,
  104. "chado_get_db: The identifiers you passed in were not unique. You passed in %identifier.",
  105. array(
  106. '%identifier'=> print_r($identifiers, TRUE)
  107. )
  108. );
  109. }
  110. // Report an error if $db is FALSE since then chado_generate_var has failed.
  111. elseif ($db === FALSE) {
  112. tripal_report_error(
  113. 'tripal_chado_database_api',
  114. TRIPAL_ERROR,
  115. "chado_get_db: chado_generate_var() failed to return a db based on the identifiers
  116. you passed in. You should check that your identifiers are correct, as well as, look
  117. for a chado_generate_var error for additional clues. You passed in %identifier.",
  118. array(
  119. '%identifier'=> print_r($identifiers, TRUE)
  120. )
  121. );
  122. }
  123. // Else, as far we know, everything is fine so give them their db :)
  124. else {
  125. return $db;
  126. }
  127. }
  128. /**
  129. * Create an options array to be used in a form element
  130. * which provides a list of all chado dbs.
  131. *
  132. * @return
  133. * An array(db_id => name) for each db in the chado db table.
  134. *
  135. * @ingroup tripal_chado_database_api
  136. */
  137. function chado_get_db_select_options() {
  138. $dbs = chado_query("SELECT db_id, name FROM {db} ORDER BY name");
  139. $options = array();
  140. $options[] = 'Select a Database';
  141. foreach ($dbs as $db) {
  142. $options[$db->db_id] = $db->name;
  143. }
  144. return $options;
  145. }
  146. /**
  147. * Retrieves a chado database reference variable.
  148. *
  149. * Example Usage:
  150. * @code
  151. * $identifiers = array(
  152. * 'accession' => 'synonym',
  153. * 'db_id' => array(
  154. * 'name' => 'SOFP'
  155. * )
  156. * );
  157. * $dbxref_object = chado_get_dbxref($identifiers);
  158. * @endcode
  159. * The above code selects the synonym database reference and returns the
  160. * following object:
  161. * @code
  162. * $dbxref_object = stdClass Object (
  163. * [dbxref_id] => 2581
  164. * [accession] => synonym
  165. * [description] =>
  166. * [version] =>
  167. * [db_db_id] => 49
  168. * [db_name] => SOFP
  169. * [db_description] =>
  170. * [db_urlprefix] =>
  171. * [db_url] =>
  172. * );
  173. * @endcode
  174. *
  175. * @param $identifier
  176. * An array apropriate for use with the chado_generate_var for uniquely
  177. * identifying a dbxref record. Alternatively, there are also some specially
  178. * handled keys. They are:
  179. * - property: An array/object describing the property to select records for.
  180. * It should at least have either a type_name (if unique across cvs) or
  181. * type_id. Other supported keys include: cv_id/cv_name (of the type),
  182. * value and rank.
  183. * @param $options
  184. * An array of options. Supported keys include:
  185. * - Any keys supported by chado_generate_var(). See that function
  186. * definition for additional details.
  187. *
  188. * NOTE: the $identifier parameter can really be any array similar to $values
  189. * passed into chado_select_record(). It should fully specify the dbxref record
  190. * to be returned.
  191. *
  192. * @return
  193. * If unique values were passed in as an identifier then an object describing
  194. * the dbxref will be returned (will be a chado variable from
  195. * chado_generate_var()). Otherwise, FALSE will be returned.
  196. *
  197. * @ingroup tripal_chado_database_api
  198. */
  199. function chado_get_dbxref($identifiers, $options = array()) {
  200. // Set Defaults.
  201. if (!isset($options['include_fk'])) {
  202. // Tells chado_generate_var not only expand the db.
  203. $options['include_fk'] = array('db_id' => TRUE);
  204. }
  205. // Error Checking of parameters.
  206. if (!is_array($identifiers)) {
  207. tripal_report_error('tripal_db_api', TRIPAL_ERROR,
  208. "chado_get_dbxref: The identifier passed in is expected to be an array with the key
  209. matching a column name in the dbxref table (ie: dbxref_id or name). You passed in %identifier.",
  210. array('%identifier'=> print_r($identifiers, TRUE))
  211. );
  212. }
  213. elseif (empty($identifiers)) {
  214. tripal_report_error('tripal_db_api', TRIPAL_ERROR,
  215. "chado_get_dbxref: You did not pass in anything to identify the dbxref you want. The identifier
  216. is expected to be an array with the key matching a column name in the dbxref table
  217. (ie: dbxref_id or name). You passed in %identifier.",
  218. array('%identifier'=> print_r($identifiers, TRUE))
  219. );
  220. }
  221. // If one of the identifiers is property then use chado_get_record_with_property().
  222. if (isset($identifiers['property'])) {
  223. $property = $identifiers['property'];
  224. unset($identifiers['property']);
  225. $dbxref = chado_get_record_with_property(
  226. array('table' => 'dbxref', 'base_records' => $identifiers),
  227. array('type_name' => $property),
  228. $options
  229. );
  230. }
  231. // Else we have a simple case and we can just use chado_generate_var to get
  232. // the analysis.
  233. else {
  234. $dbxref = chado_generate_var('dbxref', $identifiers, $options);
  235. }
  236. // Ensure the dbxref is singular. If it's an array then it is not singular.
  237. if (is_array($dbxref)) {
  238. tripal_report_error('tripal_db_api', TRIPAL_ERROR,
  239. "chado_get_dbxref: The identifiers you passed in were not unique. You passed in %identifier.",
  240. array('%identifier'=> print_r($identifiers, TRUE))
  241. );
  242. }
  243. // Report an error if $dbxref is FALSE since then chado_generate_var has
  244. // failed.
  245. elseif ($dbxref === FALSE) {
  246. tripal_report_error(
  247. 'tripal_db_api',
  248. TRIPAL_ERROR,
  249. "chado_get_dbxref: chado_generate_var() failed to return a dbxref 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 dbxref :)
  258. else {
  259. return $dbxref;
  260. }
  261. }
  262. /**
  263. * Generates a URL for the controlled vocabulary term.
  264. *
  265. * If the URL and URL prefix are provided for the database record of a cvterm
  266. * then a URL can be created for the term. By default, the db.name and
  267. * dbxref.accession are concatenated and appended to the end of the db.urlprefix.
  268. * But Tripal supports the use of {db} and {accession} tokens when if present
  269. * in the db.urlprefix string will be replaced with the db.name and
  270. * dbxref.accession respectively.
  271. *
  272. * @param $dbxref
  273. * A dbxref object as created by the chado_generate_var() function.
  274. *
  275. * @return
  276. * A string containing the URL.
  277. *
  278. * @ingroup tripal_chado_database_api
  279. */
  280. function chado_get_dbxref_url($dbxref) {
  281. $final_url = '';
  282. // Create the URL for the term.
  283. if ($dbxref->db_id->urlprefix) {
  284. $db_count = 0;
  285. $acc_count = 0;
  286. $url = $dbxref->db_id->urlprefix;
  287. // If the URL prefix has replacement tokens then use those.
  288. $url = preg_replace('/\{db\}/', $dbxref->db_id->name, $url, -1, $db_count);
  289. $url = preg_replace('/\{accession\}/', $dbxref->accession, $url, -1, $acc_count);
  290. $final_url = $url;
  291. // If no replacements were made above then tokens weren't used and we can
  292. // default to just appending the db name and accession to the end.
  293. if (!$db_count and !$acc_count) {
  294. $final_url = $dbxref->db_id->urlprefix . $dbxref->db_id->name . ':' . $dbxref->accession;
  295. }
  296. // If the URL prefix is relative then convert it to a full URL.
  297. if (!preg_match('/^(http|https)/', $final_url)) {
  298. $final_url = url($final_url, array('absolute' => TRUE));
  299. }
  300. }
  301. return $final_url;
  302. }
  303. /**
  304. * Adds a new database to the Chado DB table and returns the DB object.
  305. *
  306. * @param $values
  307. * An associative array of the values of the db (those to be inserted):
  308. * - name: The name of the database. This name is usually used as the prefix
  309. * for CV term accessions.
  310. * - description: (Optional) A description of the database. By default no
  311. * description is required.
  312. * - url: (Optional) The URL for the database.
  313. * - urlprefix: (Optional) The URL that is to be used as a prefix when
  314. * constructing a link to a database term.
  315. * @param $options
  316. * Optional. An associative array of options that can include:
  317. * - update_existing: Set this to '1' to force an update of the database if it
  318. * already exists. The default is to not update. If the database exists
  319. * then nothing is added.
  320. *
  321. * @return
  322. * An object populated with fields from the newly added database. If the
  323. * database already exists it returns the values in the current entry.
  324. *
  325. * @ingroup tripal_chado_database_api
  326. */
  327. function chado_insert_db($values, $options = array()) {
  328. // Default Values.
  329. $dbname = $values['name'];
  330. $description = (isset($values['description'])) ? $values['description'] : '';
  331. $url = (isset($values['url'])) ? $values['url'] : '';
  332. $urlprefix = (isset($values['urlprefix'])) ? $values['urlprefix'] : '';
  333. $update = (isset($options['update_existing'])) ? $options['update_existing'] : TRUE;
  334. // Build the values array for inserting/updating.
  335. $ins_values = array(
  336. 'name' => $dbname,
  337. 'description' => $description,
  338. 'url' => $url,
  339. 'urlprefix' => $urlprefix
  340. );
  341. // Get the database record if it already exists.
  342. $sel_values = array('name' => $dbname);
  343. $result = chado_select_record('db', array('*'), $sel_values);
  344. // If it does not exists then add it.
  345. if (count($result) == 0) {
  346. $ins_options = array('statement_name' => 'ins_db_nadeurur');
  347. $success = chado_insert_record('db', $ins_values, $ins_options);
  348. if (!$success) {
  349. tripal_report_error('tripal_chado', TRIPAL_WARNING, "Cannot create db '$dbname'.", NULL);
  350. return 0;
  351. }
  352. $result = chado_select_record('db', array('*'), $sel_values);
  353. }
  354. // If it exists and update is enabled the do the update.
  355. elseif ($update) {
  356. $upd_options = array('statement_name' => 'upd_db_nadeurur');
  357. $success = chado_update_record('db', $sel_values, $ins_values, $upd_options);
  358. if (!$success) {
  359. tripal_report_error('tripal_chado', TRIPAL_WARNING, "Cannot update db '$dbname'.", NULL);
  360. return 0;
  361. }
  362. $result = chado_select_record('db', array('*'), $sel_values);
  363. }
  364. // Return the database object.
  365. return $result[0];
  366. }
  367. /**
  368. * Add a database reference.
  369. *
  370. * @param $values
  371. * An associative array of the values to be inserted including:
  372. * - db_id: the database_id of the database the reference is from.
  373. * - accession: the accession.
  374. * - version: (Optional) The version of the database reference.
  375. * - description: (Optional) A description of the database reference.
  376. *
  377. * @return
  378. * The newly inserted dbxref as an object, similar to that returned by
  379. * the chado_select_record() function.
  380. *
  381. * @ingroup tripal_chado_database_api
  382. */
  383. function chado_insert_dbxref($values) {
  384. $db_id = $values['db_id'];
  385. $accession = $values['accession'];
  386. $version = (isset($values['version'])) ? $values['version'] : '';
  387. $description = (isset($values['description'])) ? $values['description'] : '';
  388. $ins_values = array(
  389. 'db_id' => $db_id,
  390. 'accession' => $accession,
  391. 'version' => $version,
  392. 'description' => $description
  393. );
  394. // Check to see if the dbxref exists.
  395. $sel_values = array(
  396. 'db_id' => $db_id,
  397. 'accession' => $accession,
  398. 'version' => $version
  399. );
  400. $result = chado_select_record('dbxref', array('*'), $sel_values);
  401. // If it doesn't already exist then add it.
  402. if (!$result) {
  403. $success = chado_insert_record('dbxref', $ins_values);
  404. if (!$success) {
  405. tripal_report_error('tripal_chado', TRIPAL_WARNING, "Failed to insert the dbxref record $accession", NULL);
  406. return 0;
  407. }
  408. $result = chado_select_record('dbxref', array('*'), $sel_values);
  409. }
  410. if (isset($result[0])) {
  411. return $result[0];
  412. }
  413. else {
  414. return FALSE;
  415. }
  416. }
  417. /**
  418. * Add a record to a database reference linking table (ie: feature_dbxref).
  419. *
  420. * @param $basetable
  421. * The base table for which the dbxref should be associated. Thus to associate
  422. * a dbxref with a feature the basetable=feature and dbxref_id is added to the
  423. * feature_dbxref table.
  424. * @param $record_id
  425. * The primary key of the basetable to associate the dbxref with. This should
  426. * be in integer.
  427. * @param $dbxref
  428. * An associative array describing the dbxref. Valid keys include:
  429. * 'accession' => the accession for the dbxref, 'db_name' => the name of the
  430. * database the dbxref belongs to.
  431. * 'db_id' => the primary key of the database the dbxref belongs to.
  432. * @param $options
  433. * An associative array of options. Valid keys include:
  434. * - insert_dbxref: Insert the dbxref if it doesn't already exist. TRUE is
  435. * the default.
  436. *
  437. * @ingroup tripal_chado_database_api
  438. */
  439. function chado_associate_dbxref($basetable, $record_id, $dbxref, $options = array()) {
  440. $linking_table = $basetable . '_dbxref';
  441. $foreignkey_name = $basetable . '_id';
  442. // Default Values.
  443. $options['insert_dbxref'] = (isset($options['insert_dbxref'])) ? $options['insert_dbxref'] : TRUE;
  444. // If the dbxref_id is set then we know it already exists.
  445. // Otherwise, select to check.
  446. if (!isset($dbxref['dbxref_id'])) {
  447. $values = array(
  448. 'accession' => $dbxref['accession'],
  449. );
  450. if (isset($dbxref['db_id'])) {
  451. $values['db_id'] = $dbxref['db_id'];
  452. } elseif (isset($dbxref['db_name'])) {
  453. $values['db_id'] = array(
  454. 'name' => $dbxref['db_name']
  455. );
  456. }
  457. else {
  458. tripal_report_error(
  459. 'tripal_chado_database_api',
  460. TRIPAL_WARNING,
  461. "chado_associate_dbxref: The dbxref needs to have either the db_name or db_id
  462. supplied. You were trying to associate a dbxref with the %base %record_id
  463. and supplied the dbxref values: %dbxref.",
  464. array('%base' => $basetable, '%record_id' => $record_id, '%dbxref' => print_r($dbxref,TRUE))
  465. );
  466. return FALSE;
  467. }
  468. $select = chado_select_record('dbxref',array('*'), $values);
  469. if ($select) {
  470. $dbxref['dbxref_id'] = $select[0]->dbxref_id;
  471. }
  472. elseif ($options['insert_dbxref']) {
  473. // Insert the dbxref.
  474. $insert = chado_insert_dbxref($values);
  475. if (isset($insert->dbxref_id)) {
  476. $dbxref['dbxref_id'] = $insert->dbxref_id;
  477. }
  478. else {
  479. tripal_report_error(
  480. 'tripal_chado_database_api',
  481. TRIPAL_WARNING,
  482. "chado_associate_dbxref: Unable to insert the dbxref using the dbxref values: %dbxref.",
  483. array('%dbxref' => print_r($dbxref,TRUE))
  484. );
  485. return FALSE;
  486. }
  487. }
  488. else {
  489. tripal_report_error(
  490. 'tripal_api',
  491. TRIPAL_WARNING,
  492. "chado_associate_dbxref: The dbxref doesn't already exist. You supplied the dbxref values: %dbxref.",
  493. array('%dbxref' => print_r($dbxref,TRUE))
  494. );
  495. return FALSE;
  496. }
  497. }
  498. // Now add the link between the record & dbxref.
  499. if ($dbxref['dbxref_id'] > 0) {
  500. $values = array(
  501. 'dbxref_id' => $dbxref['dbxref_id'],
  502. $foreignkey_name => $record_id
  503. );
  504. $result = chado_select_record($linking_table, array('*'), $values);
  505. // If it doesn't already exist then add it.
  506. if (!$result) {
  507. $success = chado_insert_record($linking_table, $values);
  508. if (!$success) {
  509. tripal_report_error(
  510. 'tripal_api',
  511. TRIPAL_WARNING,
  512. "Failed to insert the %base record %accession",
  513. array('%base' => $linking_table, '%accession' => $dbxref['accession'])
  514. );
  515. return FALSE;
  516. }
  517. $result = chado_select_record($linking_table, array('*'), $values);
  518. }
  519. if (isset($result[0])) {
  520. return $result[0];
  521. }
  522. else {
  523. return FALSE;
  524. }
  525. }
  526. return FALSE;
  527. }
  528. /**
  529. * This function is intended to be used in autocomplete forms
  530. * for searching for accession that begin with the provided string.
  531. *
  532. * @param $db_id
  533. * The DB ID in which to search for the term.
  534. * @param $string
  535. * The string to search for.
  536. *
  537. * @return
  538. * A json array of terms that begin with the provided string.
  539. *
  540. * @ingroup tripal_chado_database_api
  541. */
  542. function chado_autocomplete_dbxref($db_id, $string = '') {
  543. if (!$db_id) {
  544. return drupal_json_output(array());
  545. }
  546. $sql = "
  547. SELECT dbxref_id, accession
  548. FROM {dbxref}
  549. WHERE db_id = :db_id and lower(accession) like lower(:accession)
  550. ORDER by accession
  551. LIMIT 25 OFFSET 0
  552. ";
  553. $results = chado_query($sql, array(':db_id' => $db_id, ':accession' => $string . '%'));
  554. $items = array();
  555. foreach ($results as $ref) {
  556. $items[$ref->accession] = $ref->accession;
  557. }
  558. drupal_json_output($items);
  559. }