tripal_db.api.inc 11 KB

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