tripal_db.api.inc 9.6 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373
  1. <?php
  2. /**
  3. * @file
  4. * Provides an application programming interface (API) to manage references to
  5. * external databases
  6. */
  7. /**
  8. * @defgroup tripal_db_api Database Reference API
  9. * @ingroup tripal_api
  10. * @{
  11. * Provides an application programming interface (API) to manage references to external databases
  12. * @}
  13. */
  14. /**
  15. * To retrieve a chado database object
  16. *
  17. * @param $select_values
  18. * An array meant to uniquely select a given database
  19. *
  20. * @return
  21. * Chado database object
  22. *
  23. * The database is selected using tripal_core_chado select and as such the
  24. * $select_values array parameter meant to uniquely identify the database to be
  25. * returned follows the same form as when using tripal_core_chado_select directly.
  26. *
  27. * Example Usage:
  28. * @code
  29. $select_values = array(
  30. 'name' => 'SOFP'
  31. );
  32. $db_object = tripal_db_get_db($select_values);
  33. * @endcode
  34. * The above code selects the SOFP db and returns the following object:
  35. * @code
  36. $db_object = stdClass Object (
  37. [db_id] => 49
  38. [name] => SOFP
  39. [description] =>
  40. [urlprefix] =>
  41. [url] =>
  42. );
  43. * @endcode
  44. *
  45. * @ingroup tripal_db_api
  46. */
  47. function tripal_db_get_db($select_values) {
  48. $columns = array(
  49. 'db_id',
  50. 'name',
  51. 'description',
  52. 'urlprefix',
  53. 'url'
  54. );
  55. $results = chado_select_record('db', $columns, $select_values);
  56. if (sizeof($results) == 1) {
  57. return $results[0];
  58. }
  59. elseif (empty($results)) {
  60. tripal_report_error('tripal_db', TRIPAL_WARNING,
  61. 'tripal_db_get_db: No db matches criteria values:%values',
  62. array('%values' => print_r($select_values, TRUE)));
  63. return FALSE;
  64. }
  65. else {
  66. tripal_report_error('tripal_db', TRIPAL_WARNING,
  67. 'tripal_db_get_db: 2+ dbs match criteria values:%values',
  68. array('%values' => print_r($select_values, TRUE)));
  69. }
  70. }
  71. /**
  72. * To retrieve a chado db object
  73. *
  74. * @param $db_id
  75. * db.db_id
  76. * @return
  77. * Chado db object with all fields from the chado db table
  78. *
  79. * @ingroup tripal_db_api
  80. */
  81. function tripal_db_get_db_by_db_id($db_id) {
  82. $r = chado_query("SELECT * FROM {db} WHERE db_id = :db_id", array(':db_id' => $db_id ));
  83. return $r->fetchObject();
  84. }
  85. /**
  86. * To retrieve a chado db object
  87. *
  88. * @param $name
  89. * db.name
  90. * @return
  91. * chado db object with all fields from the chado db table
  92. *
  93. * @ingroup tripal_db_api
  94. */
  95. function tripal_db_get_db_by_name($name) {
  96. $values = array('name' => $name);
  97. $options = array('statement_name' => 'sel_db_na');
  98. $db = chado_select_record('db', array('*'), $values, $options);
  99. if (count($db) == 1) {
  100. return $db[0];
  101. }
  102. if (count($db) == 0) {
  103. return FALSE;
  104. }
  105. if (count($db) > 1) {
  106. return FALSE;
  107. }
  108. }
  109. /**
  110. * Create an options array to be used in a form element
  111. * which provides a list of all chado dbs
  112. *
  113. * @return
  114. * An array(db_id => name) for each db in the chado db table
  115. *
  116. * @ingroup tripal_db_api
  117. */
  118. function tripal_db_get_db_options() {
  119. $result = chado_query("SELECT db_id, name FROM {db}");
  120. $options = array();
  121. foreach ($result as $r) {
  122. $options[$r->db_id] = $r->name;
  123. }
  124. return $options;
  125. }
  126. /**
  127. * To retrieve a chado database reference object
  128. *
  129. * @param $select_values
  130. * An array meant to uniquely select a given database reference
  131. *
  132. * @return
  133. * Chado database reference object
  134. *
  135. * The database reference is selected using tripal_core_chado select and as such the
  136. * $select_values array parameter meant to uniquely identify the database reference to be
  137. * returned follows the same form as when using tripal_core_chado_select directly.
  138. *
  139. * Example Usage:
  140. * @code
  141. $select_values = array(
  142. 'accession' => 'synonym',
  143. 'db_id' => array(
  144. 'name' => 'SOFP'
  145. )
  146. );
  147. $dbxref_object = tripal_db_get_dbxref($select_values);
  148. * @endcode
  149. * The above code selects the synonym database reference and returns the following object:
  150. * @code
  151. $dbxref_object = stdClass Object (
  152. [dbxref_id] => 2581
  153. [accession] => synonym
  154. [description] =>
  155. [version] =>
  156. [db_db_id] => 49
  157. [db_name] => SOFP
  158. [db_description] =>
  159. [db_urlprefix] =>
  160. [db_url] =>
  161. );
  162. * @endcode
  163. *
  164. * @ingroup tripal_db_api
  165. */
  166. function tripal_db_get_dbxref($select_values) {
  167. $columns = array(
  168. 'dbxref_id',
  169. 'db_id',
  170. 'accession',
  171. 'description',
  172. 'version'
  173. );
  174. $results = chado_select_record('dbxref', $columns, $select_values);
  175. if (sizeof($results) == 1) {
  176. $dbxref = tripal_db_add_db_to_object(array('db_id' => $results[0]->db_id), $results[0], array());
  177. unset($dbxref->db_id);
  178. return $dbxref;
  179. }
  180. elseif (empty($results)) {
  181. tripal_report_error('tripal_db', TRIPAL_WARNING,
  182. 'tripal_db_get_dbxref: No dbxref matches criteria values:%values',
  183. array('%values' => print_r($select_values, TRUE)));
  184. return FALSE;
  185. }
  186. else {
  187. tripal_report_error('tripal_db', TRIPAL_WARNING,
  188. 'tripal_db_get_dbxref: 2+ dbxrefs match criteria values:%values',
  189. array('%values' => print_r($select_values, TRUE)));
  190. }
  191. }
  192. /**
  193. * Purpose: To retrieve a chado dbxref object with a given accession
  194. *
  195. * @param $accession
  196. * dbxref.accession
  197. * @param $db_id
  198. * dbxref.db_id
  199. * @return
  200. * chado dbxref object with all fields from the chado dbxref table
  201. *
  202. * @ingroup tripal_db_api
  203. */
  204. function tripal_db_get_dbxref_by_accession($accession, $db_id=0) {
  205. if (!empty($db_id)) {
  206. $sql = "SELECT * FROM {dbxref} WHERE accession = :accession AND db_id = :db_id";
  207. $r = chado_query($sql, array(':accession' => $accession, ':db_id' => $db_id));
  208. }
  209. else {
  210. $sql = "SELECT * FROM {dbxref} WHERE accession = :accession";
  211. $r = chado_query($sql, array(':accession' => $accession));
  212. }
  213. return $r->fetchObject();
  214. }
  215. /**
  216. * Adds a new database to the Chado DB table and returns the DB object.
  217. *
  218. * @param $dbname
  219. * The name of the database. This name is usually used as the prefix for
  220. * CV term accessions
  221. * @param $description
  222. * Optional. A description of the database. By default no description is required.
  223. * @param $url
  224. * Optional. The URL for the database
  225. * @param $urlprefix
  226. * Optional. The URL that is to be used as a prefix when constructing a link to
  227. * a database term
  228. * @param $update
  229. * Optional. Set this to '1' to force an update of the database if it
  230. * already exists. The default is to not update. If the database exists
  231. * then nothing is added.
  232. *
  233. * @return
  234. * An object populated with fields from the newly added database. If the
  235. * database already exists it returns the values in the current entry.
  236. *
  237. * @ingroup tripal_db_api
  238. */
  239. function tripal_db_add_db($dbname, $description = '', $url = '',
  240. $urlprefix = '', $update = 0) {
  241. // build the values array for inserting/updating
  242. $ins_values = array(
  243. 'name' => $dbname,
  244. 'description' => $description,
  245. 'url' => $url,
  246. 'urlprefix' => $urlprefix
  247. );
  248. // get the database record if it already exists
  249. $sel_values = array('name' => $dbname);
  250. $sel_options = array('statement_name' => 'sel_db_na');
  251. $result = chado_select_record('db', array('*'), $sel_values, $sel_options);
  252. // if it does not exists then add it
  253. if (count($result) == 0) {
  254. $ins_options = array('statement_name' => 'ins_db_nadeurur');
  255. $success = chado_insert_record('db', $ins_values, $ins_options);
  256. if (!$success) {
  257. tripal_report_error('tripal_db', TRIPAL_WARNING, "Cannot create db '$dbname'.", NULL);
  258. return 0;
  259. }
  260. $result = chado_select_record('db', array('*'), $sel_values, $sel_options);
  261. }
  262. // if it exists and update is enabled the do the update
  263. elseif ($update) {
  264. $upd_options = array('statement_name' => 'upd_db_nadeurur');
  265. $success = chado_update_record('db', $sel_values, $ins_values, $upd_options);
  266. if (!$success) {
  267. tripal_report_error('tripal_db', TRIPAL_WARNING, "Cannot update db '$dbname'.", NULL);
  268. return 0;
  269. }
  270. $result = chado_select_record('db', array('*'), $sel_values, $sel_options);
  271. }
  272. // return the database object
  273. return $result[0];
  274. }
  275. /**
  276. * Add a database reference
  277. *
  278. * @ingroup tripal_db_api
  279. */
  280. function tripal_db_add_dbxref($db_id, $accession, $version = '', $description = '') {
  281. $ins_values = array(
  282. 'db_id' => $db_id,
  283. 'accession' => $accession,
  284. 'version' => $version,
  285. 'description' => $description
  286. );
  287. // check to see if the dbxref exists
  288. $sel_values = array(
  289. 'db_id' => $db_id,
  290. 'accession' => $accession,
  291. 'version' => $version
  292. );
  293. $result = chado_select_record('dbxref', array('*'), $sel_values);
  294. // if it doesn't already exist then add it
  295. if (!$result) {
  296. $success = chado_insert_record('dbxref', $ins_values);
  297. if (!$success) {
  298. tripal_report_error('tripal_cv', TRIPAL_WARNING, "Failed to insert the dbxref record $accession", NULL);
  299. return 0;
  300. }
  301. $result = chado_select_record('dbxref', array('*'), $sel_values);
  302. }
  303. if (isset($result[0])) {
  304. return $result[0];
  305. }
  306. else {
  307. return FALSE;
  308. }
  309. }
  310. /**
  311. * Add a record to a database reference linking table (ie: feature_dbxref)
  312. *
  313. * @ingroup tripal_db_api
  314. */
  315. function tripal_db_add_dbxref_link($linking_table, $dbxref_id, $foreignkey_name, $foreignkey_id) {
  316. $values = array(
  317. 'dbxref_id' => $dbxref_id,
  318. $foreignkey_name => $foreignkey_id
  319. );
  320. $result = chado_select_record($linking_table, array('*'), $values);
  321. // if it doesn't already exist then add it
  322. if (!$result) {
  323. $success = chado_insert_record($linking_table, $values);
  324. if (!$success) {
  325. tripal_report_error('tripal_db', TRIPAL_WARNING, "Failed to insert the $linking_table record $accession", NULL);
  326. return 0;
  327. }
  328. $result = chado_select_record($linking_table, array('*'), $values);
  329. }
  330. if (isset($result[0])) {
  331. return $result[0];
  332. }
  333. else {
  334. return FALSE;
  335. }
  336. }