tripal_core.chado_general.api.inc 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486
  1. <?php
  2. /**
  3. * @file
  4. * Provides an application programming interface (API) to manage data withing the Chado database.
  5. */
  6. require_once 'tripal_core.schema_v1.2.api.inc';
  7. require_once 'tripal_core.schema_v1.11.api.inc';
  8. /**
  9. * @defgroup tripal_chado_api Chado API
  10. * @ingroup tripal_core_api
  11. * @{
  12. * Provides an application programming interface (API) to manage data withing the Chado database.
  13. *
  14. * This includes functions for selecting, inserting, updating and deleting records
  15. * in Chado tables. The functions will ensure proper integrity contraints are met
  16. * for inserts and updates.
  17. *
  18. * Also, a set of functions is provided for creating template variables. First,
  19. * is the tripal_core_generate_chado_vars which is used to select one ore more
  20. * records from a table and return an array with foreign key relationships fully
  21. * populated. For example, if selecting a feature, the organism_id and type_id
  22. * would be present in the returned array as a nested array with their respective
  23. * foreign keys also nested. The only fields that are not included are text
  24. * fields (which may be very large) or many-to-many foreign key relationships.
  25. * However, these fields and relationships can be expanded using the
  26. * chado_expand_var.
  27. *
  28. * When a row from a chado table is selected using these two functions, it provides
  29. * a way for users who want to cutomize Drupal template files to access all data
  30. * associate with a specific record.
  31. *
  32. * Finally, the property tables in Chado generally follow the same format. Therefore
  33. * there is a set of functions for inserting, updating and deleting properties for
  34. * any table. This provides quick lookup of properties (provided the CV term is
  35. * known).
  36. *
  37. * @}
  38. *
  39. */
  40. /**
  41. * Set the Tripal Database
  42. *
  43. * The chado_set_active function is used to prevent namespace collisions
  44. * when chado and drupal are installed in the same database but in different
  45. * schemas. It is also used for backwards compatibility with older versions
  46. * of tripal or in cases where chado is located outside of the Drupal database.
  47. * or when using Drupal functions such as db_table_exists()
  48. *
  49. * @ingroup tripal_chado_api
  50. */
  51. function chado_set_active($dbname = 'default') {
  52. global $databases, $active_db;
  53. if ($dbname ) {
  54. if ($dbname == 'chado') {
  55. db_query('set search_path to chado,public');
  56. return 'default';
  57. }
  58. else {
  59. db_query('set search_path to public');
  60. return 'chado';
  61. }
  62. }
  63. // if the 'chado' database is in the $db_url variable then chado is
  64. // not in the same Drupal database, so we don't need to set any
  65. // search_path and can just change the database
  66. elseif (array_key_exists($dbname, $databases)) {
  67. return db_set_active($dbname);
  68. }
  69. }
  70. /**
  71. * Get max rank for a given set of criteria
  72. * This function was developed with the many property tables in chado in mind but will
  73. * work for any table with a rank
  74. *
  75. * @params tablename: the name of the chado table you want to select the max rank from
  76. * this table must contain a rank column of type integer
  77. * @params where_options: array(
  78. * <column_name> => array(
  79. * 'type' => <type of column: INT/STRING>,
  80. * 'value' => <the value you want to filter on>,
  81. * 'exact' => <if TRUE use =; if FALSE use ~>,
  82. * )
  83. * )
  84. * where options should include the id and type for that table to correctly
  85. * group a set of records together where the only difference are the value and rank
  86. * @return the maximum rank
  87. *
  88. * @ingroup tripal_chado_api
  89. */
  90. function chado_get_table_max_rank($tablename, $where_options) {
  91. $where_clauses = array();
  92. $where_args = array();
  93. //generate the where clause from supplied options
  94. // the key is the column name
  95. $i = 0;
  96. $sql = "
  97. SELECT max(rank) as max_rank, count(rank) as count
  98. FROM {".$tablename."}
  99. WHERE
  100. ";
  101. foreach ($where_options as $key => $value) {
  102. $where_clauses[] = "$key = :$key";
  103. $where_args[":$key"] = $value;
  104. }
  105. $sql .= implode($where_clauses, ' AND ');
  106. $result = chado_query($sql, $where_args)->fetchObject();
  107. if ($result->count > 0) {
  108. return $result->max_rank;
  109. }
  110. else {
  111. return -1;
  112. }
  113. }
  114. /**
  115. * Retrieve a property for a given base table record
  116. *
  117. * @param $basetable
  118. * The base table for which the property should be retrieved. Thus to retrieve a property
  119. * for a feature the basetable=feature and property is retrieved from featureprop
  120. * @param $record_id
  121. * The foriegn key field of the base table. This should be in integer.
  122. * @param $property
  123. * The cvterm name describing the type of properties to be retrieved
  124. * @param $cv_name
  125. * The name of the cv that the above cvterm is part of
  126. *
  127. * @return
  128. * An array in the same format as that generated by the function
  129. * chado_generate_var(). If only one record is returned it
  130. * is a single object. If more than one record is returned then it is an array
  131. * of objects
  132. *
  133. * @ingroup tripal_chado_api
  134. */
  135. function chado_get_property($basetable, $record_id, $property, $cv_name, $property_id = FALSE) {
  136. // get the foreign key for this property table
  137. $table_desc = chado_get_schema($basetable . 'prop');
  138. $fkcol = key($table_desc['foreign keys'][$basetable]['columns']);
  139. // construct the array of values to be selected
  140. $values = array(
  141. $fkcol => $record_id,
  142. 'type_id' => array(
  143. 'cv_id' => array(
  144. 'name' => $cv_name,
  145. ),
  146. 'name' => $property,
  147. 'is_obsolete' => 0
  148. ),
  149. );
  150. // if we have the unique property_id make sure to add that to the values
  151. if ($property_id) {
  152. $property_pkey = $table_desc['primary key'][0];
  153. $values[$property_pkey] = $property_id;
  154. }
  155. $results = chado_generate_var($basetable . 'prop', $values);
  156. if ($results) {
  157. $results = chado_expand_var($results, 'field', $basetable . 'prop.value');
  158. }
  159. return $results;
  160. }
  161. /**
  162. * Insert a property for a given base table. By default if the property already
  163. * exists a new property is added with the next available rank. If
  164. * $update_if_present argument is specified then the record will be updated if it
  165. * exists rather than adding a new property.
  166. *
  167. * @param $basetable
  168. * The base table for which the property should be inserted. Thus to insert a property
  169. * for a feature the basetable=feature and property is inserted into featureprop
  170. * @param $record_id
  171. * The foriegn key value of the base table. This should be in integer.
  172. * @param $property
  173. * The cvterm name describing the type of properties to be inserted
  174. * @param $cv_name
  175. * The name of the cv that the above cvterm is part of
  176. * @param $value
  177. * The value of the property to be inserted (can be empty)
  178. * @param $update_if_present
  179. * A boolean indicating whether an existing record should be updated. If the
  180. * property already exists and this value is not specified or is zero then
  181. * a new property will be added with the next largest rank.
  182. *
  183. * @return
  184. * Return True on Insert/Update and False otherwise
  185. *
  186. * @ingroup tripal_chado_api
  187. */
  188. function chado_insert_property($basetable, $record_id, $property,
  189. $cv_name, $value, $update_if_present = 0) {
  190. // first see if the property already exists, if the user want's to update
  191. // then we can do that, but otherwise we want to increment the rank and
  192. // insert
  193. $props = chado_get_property($basetable, $record_id, $property, $cv_name);
  194. if (!is_array($props) and $props) {
  195. $props = array($props);
  196. }
  197. $rank = 0;
  198. if (count($props) > 0) {
  199. if ($update_if_present) {
  200. return chado_update_property($basetable, $record_id, $property, $cv_name, $value);
  201. }
  202. else {
  203. // iterate through the properties returned and check to see if the
  204. // property with this value already exists if not, get the largest rank
  205. // and insert the same property but with this new value
  206. foreach ($props as $p) {
  207. if ($p->rank > $rank) {
  208. $rank = $p->rank;
  209. }
  210. if (strcmp($p->value, $value) == 0) {
  211. return TRUE;
  212. }
  213. }
  214. // now add 1 to the rank
  215. $rank++;
  216. }
  217. }
  218. // make sure the cvterm exists. Otherwise we'll get an error with
  219. // prepared statements not matching
  220. $values = array(
  221. 'cv_id' => array(
  222. 'name' => $cv_name,
  223. ),
  224. 'name' => $property,
  225. );
  226. $options = array();
  227. $term = chado_select_record('cvterm', array('cvterm_id'), $values, $options);
  228. if (!$term or count($term) == 0) {
  229. tripal_report_error('tripal_core', TRIPAL_ERROR, "Cannot find property '%prop_name' in vocabulary '%cvname'.",
  230. array('%prop_name' => $property, '%cvname' => $cv_name));
  231. return FALSE;
  232. }
  233. // get the foreign key for this property table
  234. $table_desc = chado_get_schema($basetable . 'prop');
  235. $fkcol = key($table_desc['foreign keys'][$basetable]['columns']);
  236. // construct the array of values to be inserted
  237. $values = array(
  238. $fkcol => $record_id,
  239. 'type_id' => array(
  240. 'cv_id' => array(
  241. 'name' => $cv_name,
  242. ),
  243. 'name' => $property,
  244. ),
  245. 'value' => $value,
  246. 'rank' => $rank,
  247. );
  248. $options = array();
  249. $result = chado_insert_record($basetable . 'prop', $values, $options);
  250. return $result;
  251. }
  252. /**
  253. * Update a property for a given base table record and property name. This
  254. * function should be used only if one record of the property will be present.
  255. * If the property name can have multiple entries (with increasing rank) then
  256. * use the function named chado_update_propertyID
  257. *
  258. * @param $basetable
  259. * The base table for which the property should be updated. The property table
  260. * is constructed using a combination of the base table name and the suffix
  261. * 'prop' (e.g. basetable = feature then property tabie is featureprop).
  262. * @param $record_id
  263. * The foreign key of the basetable to update a property for. This should be in integer.
  264. * For example, if the basetable is 'feature' then the $record_id should be the feature_id
  265. * @param $property
  266. * The cvterm name of property to be updated
  267. * @param $cv_name
  268. * The name of the cv that the above cvterm is part of
  269. * @param $value
  270. * The value of the property to be inserted (can be empty)
  271. * @param $insert_if_missing
  272. * A boolean indicating whether a record should be inserted if one doesn't exist to update
  273. *
  274. * Note: The property to be updated is select via the unique combination of $record_id and
  275. * $property and then it is updated with the supplied value
  276. *
  277. * @return
  278. * Return True on Update/Insert and False otherwise
  279. *
  280. * @ingroup tripal_chado_api
  281. */
  282. function chado_update_property($basetable, $record_id, $property,
  283. $cv_name, $value, $insert_if_missing = FALSE, $property_id = FALSE) {
  284. // first see if the property is missing (we can't update a missing property
  285. $prop = chado_get_property($basetable, $record_id, $property, $cv_name, $property_id);
  286. if (count($prop)==0) {
  287. if ($insert_if_missing) {
  288. return chado_insert_property($basetable, $record_id, $property, $cv_name, $value);
  289. }
  290. else {
  291. return FALSE;
  292. }
  293. }
  294. // get the foreign key for this property table
  295. $table_desc = chado_get_schema($basetable . 'prop');
  296. $fkcol = key($table_desc['foreign keys'][$basetable]['columns']);
  297. // construct the array that will match the exact record to update
  298. $match = array(
  299. $fkcol => $record_id,
  300. 'type_id' => array(
  301. 'cv_id' => array(
  302. 'name' => $cv_name,
  303. ),
  304. 'name' => $property,
  305. ),
  306. );
  307. // If we have the unique property_id, make sure to use it in the match to ensure
  308. // we get the exact record. Doesn't rely on there only being one property of that type
  309. if ($property_id) {
  310. $property_pkey = $table_desc['primary key'][0];
  311. $match = array(
  312. $property_pkey => $property_id
  313. );
  314. }
  315. // construct the array of values to be updated
  316. $values = array(
  317. 'value' => $value,
  318. );
  319. // If we have the unique property_id then we can also update the type
  320. // thus add it to the values to be updated
  321. if ($property_id) {
  322. $values['type_id'] = array(
  323. 'cv_id' => array(
  324. 'name' => $cv_name,
  325. ),
  326. 'name' => $property,
  327. );
  328. }
  329. return chado_update_record($basetable . 'prop', $match, $values);
  330. }
  331. /**
  332. * Deletes a property for a given base table record using the property name
  333. *
  334. * @param $basetable
  335. * The base table for which the property should be deleted. Thus to deleted a property
  336. * for a feature the basetable=feature and property is deleted from featureprop
  337. * @param $record_id
  338. * The primary key of the basetable to delete a property for. This should be in integer.
  339. * @param $property
  340. * The cvterm name describing the type of property to be deleted
  341. * @param $cv_name
  342. * The name of the cv that the above cvterm is part of
  343. *
  344. * Note: The property to be deleted is select via the unique combination of $record_id and $property
  345. *
  346. * @return
  347. * Return True on Delete and False otherwise
  348. *
  349. * @ingroup tripal_chado_api
  350. */
  351. function chado_delete_property($basetable, $record_id, $property, $cv_name) {
  352. // get the foreign key for this property table
  353. $table_desc = chado_get_schema($basetable . 'prop');
  354. $fkcol = key($table_desc['foreign keys'][$basetable]['columns']);
  355. // construct the array that will match the exact record to update
  356. $match = array(
  357. $fkcol => $record_id,
  358. 'type_id' => array(
  359. 'cv_id' => array(
  360. 'name' => $cv_name,
  361. ),
  362. 'name' => $property,
  363. ),
  364. );
  365. return chado_delete_record($basetable . 'prop', $match);
  366. }
  367. /**
  368. * Get all records with a given property.
  369. *
  370. * @param $basetable
  371. * The base table for which variables should be generated.
  372. * @param $property
  373. * An array/object describing the property to select records for. It should at least
  374. * have either a type_name (if unique across cvs) or type_id. Other supported keys
  375. * include: cv_id/cv_name (of the type), value and rank
  376. * @param $record
  377. * An array/object describing the base table record. This can be used to restrict the
  378. * base table records returned to a given type or organism
  379. * @param $options
  380. * An array of options. Supported keys include:
  381. * - Any keys supported by chado_generate_var(). See that function definition for
  382. * additional details.
  383. *
  384. * @return
  385. * An array of chado variables with the given property
  386. *
  387. * @ingroup tripal_chado_api
  388. */
  389. function chado_get_record_with_property($basetable, $property, $record = array(), $options = array()) {
  390. $property_table = $basetable . 'prop';
  391. $foreignkey_name = $basetable . '_id';
  392. // Get paramters in the correct format
  393. if (is_array($property)) {
  394. $property = (object) $property;
  395. }
  396. if (is_object($record)) {
  397. $record = (array) $record;
  398. }
  399. // Check parameters
  400. if (!isset($property->type_name) AND !isset($property->type_id)) {
  401. tripal_report_error('chado_api', TRIPAL_ERROR,
  402. "chado_get_record_with_property: You must identify the type of property you want to
  403. select records for by supplying either a type_name or type_id. You identified the
  404. property as %prop.",
  405. array('%prop'=> print_r($property, TRUE))
  406. );
  407. }
  408. // Build the select values array based on the type
  409. if (isset($property->type_name)) {
  410. $values = array();
  411. $values['type_id'] = array(
  412. 'name' => $property->type_name
  413. );
  414. // If the cv is set, add that too
  415. if (isset($property->cv_name)) {
  416. $values['type_id']['cv_id']['name'] = $property->cv_name;
  417. }
  418. elseif (isset($property->cv_id)) {
  419. $values['type_id']['cv_id'] = $property->cv_id;
  420. }
  421. }
  422. elseif ($property->type_id) {
  423. $values = array('type_id' => $property->type_id);
  424. }
  425. // Add the value and rank to values array if present
  426. if (isset($property->value)) {
  427. $values['value'] = $property->value;
  428. }
  429. if (isset($property->rank)) {
  430. $values['rank'] = $property->rank;
  431. }
  432. // Add the record details to the values array
  433. if (!empty($record)) {
  434. $values[$foreignkey_name] = $record;
  435. }
  436. // Now select the ids of the properties that match
  437. $select = chado_select_record($property_table, array($foreignkey_name), $values);
  438. // For each of these ids, pull out the full base records
  439. $records = array();
  440. foreach ($select as $s) {
  441. $id = $s->{$foreignkey_name};
  442. $values = array($foreignkey_name => $id);
  443. $records[$id] = chado_generate_var($basetable, $values, $options);
  444. }
  445. return $records;
  446. }