tripal_core.chado_query.api.inc

Provides an API for querying of chado including inserting, updating, deleting and selecting from chado.

File

tripal_core/api/tripal_core.chado_query.api.inc
View source
  1. <?php
  2. /**
  3. * @file
  4. * Provides an API for querying of chado including inserting, updating, deleting and
  5. * selecting from chado.
  6. */
  7. /**
  8. * @defgroup tripal_chado_query_api Chado Query API
  9. * @ingroup tripal_chado_api
  10. * @{
  11. * Provides an API for querying of chado including inserting, updating, deleting and
  12. * selecting from specific chado tables. There is also a generic function, chado_query(),
  13. * to execute and SQL statement on chado. It is ideal to use these functions to interact
  14. * with chado in order to keep your module compatible with both local & external chado
  15. * databases. Furthermore, it ensures connection to the chado database is taken care
  16. * of for you.
  17. *
  18. * Generic Queries to a specifc chado table:
  19. *
  20. * chado_select_record( [table name], [columns to select], [specify record to select], [options*] )
  21. * This function allows you to select various columns from the specified chado table. Although
  22. * you can only select from a single table, you can specify the record to select using values
  23. * from related tables through use of a nested array. For example, the following code shows
  24. * you how to select the name and uniquename of a feature based on it's type and source
  25. * organism.
  26. * @code
  27. * $values = array(
  28. * 'organism_id' => array(
  29. * 'genus' => 'Citrus',
  30. * 'species' => 'sinensis',
  31. * ),
  32. * 'type_id' => array (
  33. * 'cv_id' => array (
  34. * 'name' => 'sequence',
  35. * ),
  36. * 'name' => 'gene',
  37. * 'is_obsolete' => 0
  38. * ),
  39. * );
  40. * $result = chado_select_record(
  41. * 'feature', // table to select from
  42. * array('name', 'uniquename'), // columns to select
  43. * $values // record to select (see variable defn. above)
  44. * );
  45. * @endcode
  46. *
  47. * chado_insert_record( [table name], [values to insert], [options*] )
  48. * This function allows you to insert a single record into a specific table. The values to
  49. * insert are specified using an associative array where the keys are the column names to
  50. * insert into and they point to the value to be inserted into that column. If the column
  51. * is a foreign key, the key will point to an array specifying the record in the foreign
  52. * table and then the primary key of that record will be inserted in the column. For example,
  53. * the following code will insert a feature and for the type_id, the cvterm.cvterm_id of
  54. * the cvterm record will be inserted and for the organism_id, the organism.organism_id
  55. * of the organism_record will be inserted.
  56. * @code
  57. * $values = array(
  58. * 'organism_id' => array(
  59. * 'genus' => 'Citrus',
  60. * 'species' => 'sinensis',
  61. * ),
  62. * 'name' => 'orange1.1g000034m.g',
  63. * 'uniquename' => 'orange1.1g000034m.g',
  64. * 'type_id' => array (
  65. * 'cv_id' => array (
  66. * 'name' => 'sequence',
  67. * ),
  68. * 'name' => 'gene',
  69. * 'is_obsolete' => 0
  70. * ),
  71. * );
  72. * $result = chado_insert_record(
  73. * 'feature', // table to insert into
  74. * $values // values to insert
  75. * );
  76. * @endcode
  77. *
  78. * chado_update_record( [table name], [specify record to update], [values to change], [options*] )
  79. * This function allows you to update records in a specific chado table. The record(s)
  80. * you wish to update are specified the same as in the select function above and
  81. * the values to be update are specified the same as the values to be inserted were. For
  82. * example, the following code species that a feature with a given uniquename, organism_id,
  83. * and type_id (the unique constraint for the feature table) will be updated with a new name,
  84. * and the type changed from a gene to an mRNA.
  85. * @code
  86. * $umatch = array(
  87. * 'organism_id' => array(
  88. * 'genus' => 'Citrus',
  89. * 'species' => 'sinensis',
  90. * ),
  91. * 'uniquename' => 'orange1.1g000034m.g7',
  92. * 'type_id' => array (
  93. * 'cv_id' => array (
  94. * 'name' => 'sequence',
  95. * ),
  96. * 'name' => 'gene',
  97. * 'is_obsolete' => 0
  98. * ),
  99. * );
  100. * $uvalues = array(
  101. * 'name' => 'orange1.1g000034m.g',
  102. * 'type_id' => array (
  103. * 'cv_id' => array (
  104. * 'name' => 'sequence',
  105. * ),
  106. * 'name' => 'mRNA',
  107. * 'is_obsolete' => 0
  108. * ),
  109. * );
  110. * $result = chado_update_record('feature',$umatch,$uvalues);
  111. * @endcode
  112. *
  113. * chado_delete_record( [table name], [specify records to delete], [options*] )
  114. * This function allows you to delete records from a specific chado table. The record(s)
  115. * to delete are specified the same as the record to select/update was above. For example,
  116. * the following code will delete all genes from the organism Citrus sinensis.
  117. * @code
  118. * $values = array(
  119. * 'organism_id' => array(
  120. * 'genus' => 'Citrus',
  121. * 'species' => 'sinensis',
  122. * ),
  123. * 'type_id' => array (
  124. * 'cv_id' => array (
  125. * 'name' => 'sequence',
  126. * ),
  127. * 'name' => 'gene',
  128. * 'is_obsolete' => 0
  129. * ),
  130. * );
  131. * $result = chado_select_record(
  132. * 'feature', // table to select from
  133. * $values // records to delete (see variable defn. above)
  134. * );
  135. * @endcode
  136. *
  137. * Generic Queries for any SQL:
  138. *
  139. * Often it is necessary to select from more then one table in chado or to execute
  140. * other complex queries that cannot be handled efficiently by the above functions. It is
  141. * for this reason that the chado_query( [sql string], [arguments to sub-in to the sql] )
  142. * function was created. This function allows you to execute any SQL directly on the
  143. * chado database and should be used with care. If any user input will be used in the query
  144. * make sure to put a placeholder in your SQL string and then define the value in the
  145. * arguments array. This will make sure that the user input is santized and safe through
  146. * type-checking and escaping. The following code shows an example of how to use user input
  147. * resulting from a form and would be called withing the form submit function.
  148. * @code
  149. * $sql = "SELECT F.name, CVT.name as type_name, ORG.common_name
  150. * FROM feature F
  151. * LEFT JOIN cvterm CVT ON F.type_id = CVT.cvterm_id
  152. * LEFT JOIN organism ORG ON F.organism_id = ORG.organism_id
  153. * WHERE
  154. * F.uniquename = :feature_uniquename";
  155. * $args = array( ':feature_uniquename' => $form_state['values']['uniquename'] );
  156. * $result = chado_query( $sql, $args );
  157. * foreach ($result as $r) { [Do something with the records here] }
  158. * @endcode
  159. *
  160. * If you are going to need more then a couple fields, you might want to use the
  161. * Chado Variables API (specifically chado_generate_var()) to select all
  162. * of the common fields needed including following foreign keys.
  163. *
  164. * Loading of Variables from chado data:
  165. *
  166. * These functions, chado_generate_var() and chado_expand_var(), generate objects
  167. * containing the full details of a record(s) in chado. These should be used in all
  168. * theme templates.
  169. *
  170. * This differs from the objects returned by chado_select_record in so far as all foreign key
  171. * relationships have been followed meaning you have more complete details. Thus this function
  172. * should be used whenever you need a full variable and chado_select_record should be used if
  173. * you only case about a few columns.
  174. *
  175. * The initial variable is generated by the
  176. * chado_generate_var([table], [filter criteria], [optional options])
  177. * function. An example of how to use this function is:
  178. * @code
  179. $values = array(
  180. 'name' => 'Medtr4g030710'
  181. );
  182. $features = chado_generate_var('feature', $values);
  183. * @endcode
  184. * This will return an object if there is only one feature with the name Medtr4g030710 or it will
  185. * return an array of feature objects if more than one feature has that name.
  186. *
  187. * Some tables and fields are excluded by default. To have those tables & fields added to
  188. * your variable you can use the
  189. * chado_expand_var([chado variable], [type], [what to expand], [optional options])
  190. * function. An example of how to use this function is:
  191. * @code
  192. // Get a chado object to be expanded
  193. $values = array(
  194. 'name' => 'Medtr4g030710'
  195. );
  196. $features = chado_generate_var('feature', $values);
  197. // Expand the organism node
  198. $feature = chado_expand_var($feature, 'node', 'organism');
  199. // Expand the feature.residues field
  200. $feature = chado_expand_var($feature, 'field', 'feature.residues');
  201. // Expand the feature properties (featureprop table)
  202. $feature = chado_expand_var($feature, 'table', 'featureprop');
  203. * @endcode
  204. */
  205. /**
  206. * Provides a generic routine for inserting into any Chado table
  207. *
  208. * Use this function to insert a record into any Chado table. The first
  209. * argument specifies the table for inserting and the second is an array
  210. * of values to be inserted. The array is mutli-dimensional such that
  211. * foreign key lookup values can be specified.
  212. *
  213. * @param $table
  214. * The name of the chado table for inserting
  215. * @param $values
  216. * An associative array containing the values for inserting.
  217. * @param $options
  218. * An array of options such as:
  219. * - skip_validation: TRUE or FALSE. If TRUE will skip all the validation steps and
  220. * just try to insert as is. This is much faster but results in unhandled
  221. * non user-friendly errors if the insert fails.
  222. * - return_record: by default, the function will return the record but with
  223. * the primary keys added after insertion. To simply return TRUE on success
  224. * set this option to FALSE
  225. *
  226. * @return
  227. * On success this function returns the inserted record with the new primary keys
  228. * added to the returned array. On failure, it returns FALSE.
  229. *
  230. * Example usage:
  231. * @code
  232. * $values = array(
  233. * 'organism_id' => array(
  234. * 'genus' => 'Citrus',
  235. * 'species' => 'sinensis',
  236. * ),
  237. * 'name' => 'orange1.1g000034m.g',
  238. * 'uniquename' => 'orange1.1g000034m.g',
  239. * 'type_id' => array (
  240. * 'cv_id' => array (
  241. * 'name' => 'sequence',
  242. * ),
  243. * 'name' => 'gene',
  244. * 'is_obsolete' => 0
  245. * ),
  246. * );
  247. * $result = chado_insert_record('feature',$values);
  248. * @endcode
  249. * The above code inserts a record into the feature table. The $values array is
  250. * nested such that the organism is selected by way of the organism_id foreign
  251. * key constraint by specifying the genus and species. The cvterm is also
  252. * specified using its foreign key and the cv_id for the cvterm is nested as
  253. * well.
  254. *
  255. * @ingroup tripal_chado_query_api
  256. */
  257. function chado_insert_record($table, $values, $options = array()) {
  258. $print_errors = (isset($options['print_errors'])) ? $options['print_errors'] : FALSE;
  259. if (!is_array($values)) {
  260. tripal_report_error('tripal_core', TRIPAL_ERROR,
  261. 'Cannot pass non array as values for inserting.', array(),
  262. array('print' => $print_errors)
  263. );
  264. return FALSE;
  265. }
  266. if (count($values)==0) {
  267. tripal_report_error('tripal_core', TRIPAL_ERROR,
  268. 'Cannot pass an empty array as values for inserting.',
  269. array(),array('print' => $print_errors)
  270. );
  271. return FALSE;
  272. }
  273. // set defaults for options. If we don't set defaults then
  274. // we get memory leaks when we try to access the elements
  275. if (!is_array($options)) {
  276. $options = array();
  277. }
  278. if (!array_key_exists('skip_validation', $options)) {
  279. $options['skip_validation'] = FALSE;
  280. }
  281. if (!array_key_exists('return_record', $options)) {
  282. $options['return_record'] = TRUE;
  283. }
  284. $insert_values = array();
  285. if (array_key_exists('skip_validation', $options)) {
  286. $validate = !$options['skip_validation'];
  287. }
  288. else {
  289. $validate = TRUE;
  290. }
  291. // get the table description
  292. $table_desc = chado_get_schema($table);
  293. if (!$table_desc) {
  294. tripal_report_error('tripal_core', TRIPAL_WARNING,
  295. 'chado_insert_record; There is no table description for !table_name',
  296. array('!table_name' => $table), array('print' => $print_errors)
  297. );
  298. return FALSE;
  299. }
  300. // iterate through the values array and create a new 'insert_values' array
  301. // that has all the values needed for insert with all foreign relationsihps
  302. // resolved.
  303. foreach ($values as $field => $value) {
  304. // make sure the field is in the table description. If not then return an error
  305. // message
  306. if (!array_key_exists($field, $table_desc['fields'])) {
  307. tripal_report_error('tripal_core', TRIPAL_ERROR,
  308. "chado_insert_record; The field '%field' does not exist " .
  309. "for the table '%table'. Cannot perform insert. Values: %array",
  310. array('%field' => $field, '%table' => $table, '%array' => print_r($values, 1)),
  311. array('print' => $print_errors)
  312. );
  313. return FALSE;
  314. }
  315. if (is_array($value)) {
  316. // select the value from the foreign key relationship for this value
  317. $results = chado_schema_get_foreign_key($table_desc, $field, $value);
  318. if (sizeof($results) > 1) {
  319. tripal_report_error('tripal_core', TRIPAL_ERROR,
  320. 'chado_insert_record: Too many records match the criteria supplied for !foreign_key foreign key constraint (!criteria)',
  321. array('!foreign_key' => $field, '!criteria' => print_r($value, TRUE)),
  322. array('print' => $print_errors)
  323. );
  324. return FALSE;
  325. }
  326. elseif (sizeof($results) < 1) {
  327. tripal_report_error('tripal_core', TRIPAL_DEBUG,
  328. 'chado_insert_record: no record matches criteria supplied for !foreign_key foreign key constraint (!criteria)',
  329. array('!foreign_key' => $field, '!criteria' => print_r($value, TRUE)),
  330. array('print' => $print_errors)
  331. );
  332. return FALSE;
  333. }
  334. else {
  335. $insert_values[$field] = $results[0];
  336. }
  337. }
  338. else {
  339. $insert_values[$field] = $value;
  340. }
  341. }
  342. if ($validate) {
  343. // check for violation of any unique constraints
  344. $ukeys = array();
  345. if (array_key_exists('unique keys', $table_desc)) {
  346. $ukeys = $table_desc['unique keys'];
  347. }
  348. $ukselect_cols = array();
  349. $ukselect_vals = array();
  350. if ($ukeys) {
  351. foreach ($ukeys as $name => $fields) {
  352. foreach ($fields as $index => $field) {
  353. // build the arrays for performing a select that will check the contraint
  354. $ukselect_cols[] = $field;
  355. if (!array_key_exists($field, $insert_values)) {
  356. if (array_key_exists('default', $table_desc['fields'][$field])) {
  357. $ukselect_vals[$field] = $table_desc['fields'][$field]['default'];
  358. }
  359. }
  360. else {
  361. $ukselect_vals[$field] = $insert_values[$field];
  362. }
  363. }
  364. // now check the constraint
  365. if (chado_select_record($table, $ukselect_cols, $ukselect_vals)) {
  366. tripal_report_error('tripal_core', TRIPAL_ERROR,
  367. "chado_insert_record; Cannot insert duplicate record into $table table: !values",
  368. array('!values' => print_r($values, TRUE)), array('print' => $print_errors)
  369. );
  370. return FALSE;
  371. }
  372. }
  373. }
  374. // if trying to insert a field that is the primary key, make sure it also is unique
  375. if (array_key_exists('primary key', $table_desc)) {
  376. $pkey = $table_desc['primary key'][0];
  377. if (array_key_exists($pkey, $insert_values)) {
  378. $coptions = array();
  379. if (chado_select_record($table, array($pkey), array($pkey => $insert_values[$pkey]), $coptions)) {
  380. tripal_report_error('tripal_core', TRIPAL_ERROR,
  381. 'chado_insert_record; Cannot insert duplicate primary key into !table table: !values',
  382. array('!table' => $table, '!values' => print_r($values, TRUE)),
  383. array('print' => $print_errors)
  384. );
  385. return FALSE;
  386. }
  387. }
  388. }
  389. // make sure required fields have a value
  390. if (!is_array($table_desc['fields'])) {
  391. $table_desc['fields'] = array();
  392. tripal_report_error('tripal_core', TRIPAL_WARNING,
  393. "chado_insert_record; %table missing fields: \n %schema",
  394. array('%table' => $table, '%schema' => print_r($table_desc, 1)),
  395. array('print' => $print_errors)
  396. );
  397. }
  398. foreach ($table_desc['fields'] as $field => $def) {
  399. // a field is considered missing if it cannot be NULL and there is no default
  400. // value for it or it is of type 'serial'
  401. if (array_key_exists('NOT NULL', $def) and
  402. !array_key_exists($field, $insert_values) and
  403. !array_key_exists('default', $def) and
  404. strcmp($def['type'], serial) != 0) {
  405. tripal_report_error('tripal_core', TRIPAL_ERROR,
  406. "chado_insert_record; Field %table.%field cannot be NULL: %values",
  407. array('%table' => $table, '%field' => $field, '%values' => print_r($values, 1)),
  408. array('print' => $print_errors)
  409. );
  410. return FALSE;
  411. }
  412. }
  413. } //end of validation
  414. // Now build the insert SQL statement
  415. $ifields = array(); // contains the names of the fields
  416. $itypes = array(); // contains placeholders for the sql query
  417. $ivalues = array(); // contains the values of the fields
  418. $i = 1;
  419. foreach ($insert_values as $field => $value) {
  420. $ifields[] = $field;
  421. $ivalues[":$field"] = $value;
  422. $i++;
  423. if (strcmp($value, '__NULL__')==0) {
  424. $itypes[] = "NULL";
  425. }
  426. else {
  427. $itypes[] = ":$field";
  428. }
  429. }
  430. // create the SQL
  431. $sql = 'INSERT INTO {' . $table . '} (' . implode(", ", $ifields) . ") VALUES (" . implode(", ", $itypes) . ")";
  432. $result = chado_query($sql, $ivalues);
  433. // if we have a result then add primary keys to return array
  434. if ($options['return_record'] == TRUE and $result) {
  435. if (array_key_exists('primary key', $table_desc) and is_array($table_desc['primary key'])) {
  436. foreach ($table_desc['primary key'] as $field) {
  437. $sql = "SELECT CURRVAL('{" . $table . "_" . $field . "_seq}')";
  438. $results = chado_query($sql);
  439. $value = $results->fetchField();
  440. if (!$value) {
  441. tripal_report_error('tripal_core', TRIPAL_ERROR,
  442. "chado_insert_record; not able to retrieve primary key after insert: %sql",
  443. array('%sql' => $sql),
  444. array('print' => $print_errors)
  445. );
  446. return FALSE;
  447. }
  448. $values[$field] = $value;
  449. }
  450. }
  451. return $values;
  452. }
  453. elseif ($options['return_record'] == FALSE and $result) {
  454. return TRUE;
  455. }
  456. else {
  457. tripal_report_error('tripal_core', TRIPAL_ERROR,
  458. 'chado_insert_record; Cannot insert record into "%table": %values',
  459. array('%table' => $table, '%values' => print_r($values, 1)),
  460. array('print' => $print_errors)
  461. );
  462. return FALSE;
  463. }
  464. return FALSE;
  465. }
  466. /**
  467. * Provides a generic routine for updating into any Chado table
  468. *
  469. * Use this function to update a record in any Chado table. The first
  470. * argument specifies the table for inserting, the second is an array
  471. * of values to matched for locating the record for updating, and the third
  472. * argument give the values to update. The arrays are mutli-dimensional such
  473. * that foreign key lookup values can be specified.
  474. *
  475. * @param $table
  476. * The name of the chado table for inserting
  477. * @param $match
  478. * An associative array containing the values for locating a record to update.
  479. * @param $values
  480. * An associative array containing the values for updating.
  481. * @param $options
  482. * An array of options such as:
  483. * - return_record: by default, the function will return the TRUE if the record
  484. * was succesfully updated. However, set this option to TRUE to return the
  485. * record that was updated. The returned record will have the fields provided
  486. * but the primary key (if available for the table) will be added to the record.
  487. * @return
  488. * On success this function returns TRUE. On failure, it returns FALSE.
  489. *
  490. * Example usage:
  491. * @code
  492. $umatch = array(
  493. 'organism_id' => array(
  494. 'genus' => 'Citrus',
  495. 'species' => 'sinensis',
  496. ),
  497. 'uniquename' => 'orange1.1g000034m.g7',
  498. 'type_id' => array (
  499. 'cv_id' => array (
  500. 'name' => 'sequence',
  501. ),
  502. 'name' => 'gene',
  503. 'is_obsolete' => 0
  504. ),
  505. );
  506. $uvalues = array(
  507. 'name' => 'orange1.1g000034m.g',
  508. 'type_id' => array (
  509. 'cv_id' => array (
  510. 'name' => 'sequence',
  511. ),
  512. 'name' => 'mRNA',
  513. 'is_obsolete' => 0
  514. ),
  515. );
  516. * $result = chado_update_record('feature',$umatch,$uvalues);
  517. * @endcode
  518. * The above code species that a feature with a given uniquename, organism_id,
  519. * and type_id (the unique constraint for the feature table) will be updated.
  520. * The organism_id is specified as a nested array that uses the organism_id
  521. * foreign key constraint to lookup the specified values to find the exact
  522. * organism_id. The same nested struture is also used for specifying the
  523. * values to update. The function will find the record that matches the
  524. * columns specified and update the record with the avlues in the $uvalues array.
  525. *
  526. * @TODO: Support Complex filtering as is done in chado_select_record();
  527. *
  528. * @ingroup tripal_chado_query_api
  529. */
  530. function chado_update_record($table, $match, $values, $options = NULL) {
  531. $print_errors = (isset($options['print_errors'])) ? $options['print_errors'] : FALSE;
  532. if (!is_array($values)) {
  533. tripal_report_error('tripal_core', TRIPAL_ERROR,
  534. 'Cannot pass non array as values for updating.',
  535. array(), array('print' => $print_errors)
  536. );
  537. return FALSE;
  538. }
  539. if (count($values)==0) {
  540. tripal_report_error('tripal_core', TRIPAL_ERROR,
  541. 'Cannot pass an empty array as values for updating.',
  542. array(), array('print' => $print_errors)
  543. );
  544. return FALSE;
  545. }
  546. if (!is_array($match)) {
  547. tripal_report_error('tripal_core', TRIPAL_ERROR,
  548. 'Cannot pass non array as values for matching.',
  549. array(), array('print' => $print_errors)
  550. );
  551. return FALSE;
  552. }
  553. if (count($match)==0) {
  554. tripal_report_error('tripal_core', TRIPAL_ERROR,
  555. 'Cannot pass an empty array as values for matching.',
  556. array(), array('print' => $print_errors)
  557. );
  558. return FALSE;
  559. }
  560. // set defaults for options. If we don't set defaults then
  561. // we get memory leaks when we try to access the elements
  562. if (!is_array($options)) {
  563. $options = array();
  564. }
  565. if (!array_key_exists('return_record', $options)) {
  566. $options['return_record'] = FALSE;
  567. }
  568. $update_values = array(); // contains the values to be updated
  569. $update_matches = array(); // contains the values for the where clause
  570. // get the table description
  571. $table_desc = chado_get_schema($table);
  572. if (!$table_desc) {
  573. tripal_report_error('tripal_core', TRIPAL_ERROR,
  574. 'The table name, %table, does not exist.',
  575. array('%table', $table), array('print' => $print_errors)
  576. );
  577. return FALSE;
  578. }
  579. // if the user wants us to return the record then we need to get the
  580. // unique primary key if one exists. That way we can add it to the
  581. // values that get returned at the end of the function
  582. $pkeys = array();
  583. if ($options['return_record'] == TRUE) {
  584. if (array_key_exists('primary key', $table_desc) and is_array($table_desc['primary key'])) {
  585. $columns = array();
  586. $stmt_suffix = '';
  587. foreach ($table_desc['primary key'] as $field) {
  588. $columns[] = $field;
  589. $stmt_suffix .= substr($field, 0, 2);
  590. }
  591. $options2 = array();
  592. $results = chado_select_record($table, $columns, $match, $options2);
  593. if (count($results) > 0) {
  594. foreach ($results as $index => $pkey) {
  595. $pkeys[] = $pkey;
  596. }
  597. }
  598. }
  599. }
  600. // get the values needed for matching in the SQL statement
  601. foreach ($match as $field => $value) {
  602. if (is_array($value)) {
  603. $results = chado_schema_get_foreign_key($table_desc, $field, $value);
  604. if (sizeof($results) > 1) {
  605. tripal_report_error('tripal_core', TRIPAL_ERROR,
  606. 'chado_update_record: When trying to find record to update, too many records match the criteria supplied for !foreign_key foreign key constraint (!criteria)',
  607. array('!foreign_key' => $field, '!criteria' => print_r($value, TRUE)),
  608. array('print' => $print_errors)
  609. );
  610. return FALSE;
  611. }
  612. elseif (sizeof($results) < 1) {
  613. tripal_report_error('tripal_core', TRIPAL_DEBUG,
  614. 'chado_update_record: When trying to find record to update, no record matches criteria supplied for !foreign_key foreign key constraint (!criteria)',
  615. array('!foreign_key' => $field, '!criteria' => print_r($value, TRUE)),
  616. array('print' => $print_errors)
  617. );
  618. return FALSE;
  619. }
  620. else {
  621. $update_matches[$field] = $results[0];
  622. }
  623. }
  624. else {
  625. $update_matches[$field] = $value;
  626. }
  627. }
  628. // get the values used for updating
  629. foreach ($values as $field => &$value) {
  630. if (is_array($value)) {
  631. $foreign_options = array();
  632. // select the value from the foreign key relationship for this value
  633. $results = chado_schema_get_foreign_key($table_desc, $field, $value, $foreign_options);
  634. if (sizeof($results) > 1) {
  635. tripal_report_error('tripal_core', TRIPAL_ERROR,
  636. 'chado_update_record: When trying to find update values, too many records match the criteria supplied for !foreign_key foreign key constraint (!criteria)',
  637. array('!foreign_key' => $field, '!criteria' => print_r($value, TRUE)),
  638. array('print' => $print_errors)
  639. );
  640. return FALSE;
  641. }
  642. elseif (sizeof($results) < 1) {
  643. tripal_report_error('tripal_core', TRIPAL_DEBUG,
  644. 'chado_update_record: When trying to find update values, no record matches criteria supplied for !foreign_key foreign key constraint (!criteria)',
  645. array('!foreign_key' => $field, '!criteria' => print_r($value,TRUE)),
  646. array('print' => $print_errors)
  647. );
  648. return FALSE;
  649. }
  650. else {
  651. $update_values[$field] = $results[0];
  652. }
  653. }
  654. else {
  655. $update_values[$field] = $value;
  656. }
  657. }
  658. // now build the SQL statement
  659. $sql = 'UPDATE {' . $table . '} SET ';
  660. $args = array(); // arguments passed to chado_query
  661. foreach ($update_values as $field => $value) {
  662. if (strcmp($value, '__NULL__') == 0) {
  663. $sql .= " $field = NULL, ";
  664. }
  665. else {
  666. $sql .= " $field = :$field, ";
  667. $args[":$field"] = $value;
  668. }
  669. }
  670. $sql = drupal_substr($sql, 0, -2); // get rid of the trailing comma & space
  671. $sql .= " WHERE ";
  672. foreach ($update_matches as $field => $value) {
  673. if (strcmp($value, '__NULL__')==0) {
  674. $sql .= " $field = NULL AND ";
  675. }
  676. else {
  677. $sql .= " $field = :$field AND ";
  678. $args[":$field"] = $value;
  679. }
  680. }
  681. $sql = drupal_substr($sql, 0, -4); // get rid of the trailing 'AND'
  682. $result = chado_query($sql, $args);
  683. // if we have a result then add primary keys to return array
  684. if ($options['return_record'] == TRUE and $result) {
  685. // only if we have a single result do we want to add the primary keys to the values
  686. // array. If the update matched many records we can't add the pkeys
  687. if (count($pkeys) == 1) {
  688. foreach ($pkeys as $index => $pkey) {
  689. foreach ($pkey as $field => $fvalue) {
  690. $values[$field] = $fvalue;
  691. }
  692. }
  693. }
  694. return $values;
  695. }
  696. elseif ($options['return_record'] == FALSE and $result) {
  697. return TRUE;
  698. }
  699. else {
  700. tripal_report_error('tripal_core', TRIPAL_ERROR,
  701. "chado_update_record: Cannot update record in %table table. \nMatch: %match \nValues: %values",
  702. array('%table' => table, '%match' => print_r($match,TRUE), '%values' => print_r($values, 1)),
  703. array('print' => $print_errors)
  704. );
  705. return FALSE;
  706. }
  707. return FALSE;
  708. }
  709. /**
  710. * Provides a generic function for deleting a record(s) from any chado table
  711. *
  712. * Use this function to delete a record(s) in any Chado table. The first
  713. * argument specifies the table to delete from and the second is an array
  714. * of values to match for locating the record(s) to be deleted. The arrays
  715. * are mutli-dimensional such that foreign key lookup values can be specified.
  716. *
  717. * @param $table
  718. * The name of the chado table for inserting
  719. * @param $match
  720. * An associative array containing the values for locating a record to update.
  721. * @param $options
  722. * Currently there are no options
  723. * @return
  724. * On success this function returns TRUE. On failure, it returns FALSE.
  725. *
  726. * Example usage:
  727. * @code
  728. $umatch = array(
  729. 'organism_id' => array(
  730. 'genus' => 'Citrus',
  731. 'species' => 'sinensis',
  732. ),
  733. 'uniquename' => 'orange1.1g000034m.g7',
  734. 'type_id' => array (
  735. 'cv_id' => array (
  736. 'name' => 'sequence',
  737. ),
  738. 'name' => 'gene',
  739. 'is_obsolete' => 0
  740. ),
  741. );
  742. $uvalues = array(
  743. 'name' => 'orange1.1g000034m.g',
  744. 'type_id' => array (
  745. 'cv_id' => array (
  746. 'name' => 'sequence',
  747. ),
  748. 'name' => 'mRNA',
  749. 'is_obsolete' => 0
  750. ),
  751. );
  752. * $result = chado_update_record('feature', $umatch, $uvalues);
  753. * @endcode
  754. * The above code species that a feature with a given uniquename, organism_id,
  755. * and type_id (the unique constraint for the feature table) will be deleted.
  756. * The organism_id is specified as a nested array that uses the organism_id
  757. * foreign key constraint to lookup the specified values to find the exact
  758. * organism_id. The same nested struture is also used for specifying the
  759. * values to update. The function will find all records that match the
  760. * columns specified and delete them.
  761. *
  762. * @TODO: Support Complex filtering as is done in chado_select_record();
  763. *
  764. * @ingroup tripal_chado_query_api
  765. */
  766. function chado_delete_record($table, $match, $options = NULL) {
  767. $print_errors = (isset($options['print_errors'])) ? $options['print_errors'] : FALSE;
  768. if (!is_array($match)) {
  769. tripal_report_error('tripal_core', TRIPAL_ERROR,
  770. 'Cannot pass non array as values for matching.', array());
  771. return FALSE;
  772. }
  773. if (count($match)==0) {
  774. tripal_report_error('tripal_core', TRIPAL_ERROR,
  775. 'Cannot pass an empty array as values for matching.', array());
  776. return FALSE;
  777. }
  778. // set defaults for options. If we don't set defaults then
  779. // we get memory leaks when we try to access the elements
  780. if (!is_array($options)) {
  781. $options = array();
  782. }
  783. $delete_matches = array(); // contains the values for the where clause
  784. // get the table description
  785. $table_desc = chado_get_schema($table);
  786. $fields = $table_desc['fields'];
  787. if (empty($table_desc)) {
  788. tripal_report_error('tripal_core', TRIPAL_WARNING,
  789. 'chado_delete_record; There is no table description for !table_name',
  790. array('!table_name' => $table), array('print' => $print_errors)
  791. );
  792. }
  793. // get the values needed for matching in the SQL statement
  794. foreach ($match as $field => $value) {
  795. if (is_array($value)) {
  796. // if the user has specified an array of values to delete rather than
  797. // FK relationships the keep those in our match
  798. if (array_values($value) === $value) {
  799. $delete_matches[$field] = $value;
  800. }
  801. else {
  802. $results = chado_schema_get_foreign_key($table_desc, $field, $value);
  803. if (sizeof($results) > 1) {
  804. tripal_report_error('tripal_core', TRIPAL_ERROR,
  805. 'chado_delete_record: When trying to find record to delete, too many records match the criteria supplied for !foreign_key foreign key constraint (!criteria)',
  806. array('!foreign_key' => $field, '!criteria' => print_r($value, TRUE)));
  807. return FALSE;
  808. }
  809. elseif (sizeof($results) < 1) {
  810. //tripal_report_error('tripal_core', TRIPAL_ERROR, 'chado_delete_record: When trying to find record to delete, no record matches criteria supplied for !foreign_key foreign key constraint (!criteria)', array('!foreign_key' => $field, '!criteria' => print_r($value,TRUE)));
  811. }
  812. else {
  813. $delete_matches[$field] = $results[0];
  814. }
  815. }
  816. }
  817. else {
  818. $delete_matches[$field] = $value;
  819. }
  820. }
  821. // now build the SQL statement
  822. $sql = 'DELETE FROM {' . $table . '} WHERE ';
  823. $args = array();
  824. foreach ($delete_matches as $field => $value) {
  825. // if we have an array values then this is an "IN" clasue.
  826. if (count($value) > 1) {
  827. $sql .= "$field IN (";
  828. $index = 0;
  829. foreach ($value as $v) {
  830. $sql .= ":$field" . $index . ", ";
  831. $args[":$field" . $index] = $v;
  832. $index++;
  833. }
  834. $sql = drupal_substr($sql, 0, -2); // get rid of trailing ', '
  835. $sql .= ") AND ";
  836. }
  837. else {
  838. if (strcmp($value, '__NULL__') == 0) {
  839. $sql .= " $field = NULL AND ";
  840. }
  841. else {
  842. $sql .= " $field = :$field AND ";
  843. $args[":$field"] = $value;
  844. }
  845. }
  846. }
  847. $sql = drupal_substr($sql, 0, -4); // get rid of the trailing 'AND'
  848. // finally perform the delete. If successful, return the updated record
  849. $result = chado_query($sql, $args);
  850. if ($result) {
  851. return TRUE;
  852. }
  853. else {
  854. tripal_report_error('tripal_core', TRIPAL_ERROR,
  855. "Cannot delete record in $table table. Match:" . print_r($match, 1) . ". Values: " . print_r($values, 1), array());
  856. return FALSE;
  857. }
  858. return FALSE;
  859. }
  860. /**
  861. * Provides a generic routine for selecting data from a Chado table
  862. *
  863. * Use this function to perform a simple select from any Chado table.
  864. *
  865. * @param $table
  866. * The name of the chado table for inserting
  867. * @param $columns
  868. * An array of column names
  869. * @param $values
  870. * An associative array containing the values for filtering the results. In the
  871. * case where multiple values for the same time are to be selected an additional
  872. * entry for the field should appear for each value. If you need to filter
  873. * results using more complex methods see the 'Complex Filtering' section below.
  874. * @param $options
  875. * An associative array of additional options where the key is the option
  876. * and the value is the value of that option.
  877. *
  878. * Additional Options Include:
  879. * - has_record
  880. * Set this argument to 'TRUE' to have this function return a numeric
  881. * value for the number of records rather than the array of records. this
  882. * can be useful in 'if' statements to check the presence of particula records.
  883. * - return_sql
  884. * Set this to 'TRUE' to have this function return an array where the first
  885. * element is the sql that would have been run and the second is an array of
  886. * arguments.
  887. * - case_insensitive_columns
  888. * An array of columns to do a case insensitive search on.
  889. * - regex_columns
  890. * An array of columns where the value passed in should be treated as a regular expression
  891. * - order_by
  892. * An associative array containing the column names of the table as keys
  893. * and the type of sort (i.e. ASC, DESC) as the values. The results in the
  894. * query will be sorted by the key values in the direction listed by the value
  895. * - is_duplicate: TRUE or FALSE. Checks the values submited to see if
  896. * they violate any of the unique constraints. If so, the record
  897. * is returned, if not, FALSE is returned.
  898. * - pager: Use this option if it is desired to return only a subset of results
  899. * so that they may be shown with in a Drupal-style pager. This should be
  900. * an array with two keys: 'limit' and 'element'. The value of 'limit'
  901. * should specify the number of records to return and 'element' is a
  902. * unique integer to differentiate between pagers when more than one
  903. * appear on a page. The 'element' should start with zero and increment by
  904. * one for each pager.
  905. * -limit: Specifies the number of records to return.
  906. * -offset: Indicates the number of records to skip before returning records.
  907. *
  908. * @return
  909. * An array of results, FALSE if the query was not executed
  910. * correctly, an empty array if no records were matched, or the number of records
  911. * in the dataset if $has_record is set.
  912. * If the option 'is_duplicate' is provided and the record is a duplicate it
  913. * will return the duplicated record. If the 'has_record' option is provided
  914. * a value of TRUE will be returned if a record exists and FALSE will bee
  915. * returned if there are not records.
  916. *
  917. * Example usage:
  918. * @code
  919. * $columns = array('feature_id', 'name');
  920. * $values = array(
  921. * 'organism_id' => array(
  922. * 'genus' => 'Citrus',
  923. * 'species' => array('sinensis', 'clementina'),
  924. * ),
  925. * 'uniquename' => 'orange1.1g000034m.g',
  926. * 'type_id' => array (
  927. * 'cv_id' => array (
  928. * 'name' => 'sequence',
  929. * ),
  930. * 'name' => 'gene',
  931. * 'is_obsolete' => 0
  932. * ),
  933. * );
  934. * $options = array(
  935. * 'order_by' => array(
  936. * 'name' => 'ASC'
  937. * ),
  938. * );
  939. * $result = chado_select_record('feature',$columns,$values,$options);
  940. * @endcode
  941. * The above code selects a record from the feature table using the three fields
  942. * that uniquely identify a feature. The $columns array simply lists the columns
  943. * to select. The $values array is nested such that the organism is identified by
  944. * way of the organism_id foreign key constraint by specifying the genus and
  945. * species. The cvterm is also specified using its foreign key and the cv_id
  946. * for the cvterm is nested as well. In the example above, two different species
  947. * are allowed to match
  948. *
  949. * Complex Filtering:
  950. * All of the documentation above supports filtering based on 'is equal to'
  951. * or 'is NULL'. If your criteria doesn't fall into one of these two categories
  952. * then you need to provide an array with additional details such as the operator
  953. * as well as the value. An example follows and will be discussed in detail.
  954. * @code
  955. $columns = array('feature_id', 'fmin', 'fmax');
  956. // Regular criteria specifying the parent feature to retrieve locations from.
  957. $values = array(
  958. 'srcfeature_id' => array(
  959. 'uniquename' => 'MtChr01'
  960. 'type_id' => array(
  961. 'name' => 'pseudomolecule'
  962. ),
  963. ),
  964. );
  965. // Complex filtering to specify the range to return locations from.
  966. $values['fmin'][] = array(
  967. 'op' => '>',
  968. 'data' => 15
  969. );
  970. $values['fmin'][] = array(
  971. 'op' => '<',
  972. 'data' => 100
  973. );
  974. $results = chado_select_record('featureloc', $columns, $values);
  975. * @endcode
  976. * The above code example will return all of the name, start and end of all
  977. * the features that start within MtChr1:15-100bp. Note that complex filtering
  978. * can be used in conjunction with basic filtering and that multiple criteria,
  979. * even for the same field can be entered.
  980. *
  981. * @ingroup tripal_chado_query_api
  982. */
  983. function chado_select_record($table, $columns, $values, $options = NULL) {
  984. // Set defaults for options. If we don't set defaults then
  985. // we get memory leaks when we try to access the elements.
  986. if (!is_array($options)) {
  987. $options = array();
  988. }
  989. if (!array_key_exists('case_insensitive_columns', $options)) {
  990. $options['case_insensitive_columns'] = array();
  991. }
  992. if (!array_key_exists('regex_columns', $options)) {
  993. $options['regex_columns'] = array();
  994. }
  995. if (!array_key_exists('order_by', $options)) {
  996. $options['order_by'] = array();
  997. }
  998. if (!array_key_exists('return_sql', $options)) {
  999. $options['return_sql'] = FALSE;
  1000. }
  1001. if (!array_key_exists('has_record', $options)) {
  1002. $options['has_record'] = FALSE;
  1003. }
  1004. if (!array_key_exists('is_duplicate', $options)) {
  1005. $options['is_duplicate'] = FALSE;
  1006. }
  1007. $pager = array();
  1008. if (array_key_exists('pager', $options)) {
  1009. $pager = $options['pager'];
  1010. }
  1011. $print_errors = FALSE;
  1012. if (isset($options['print_errors'])) {
  1013. $print_errors = $options['print_errors'];
  1014. }
  1015. // Check that our columns and values arguments are proper arrays.
  1016. if (!is_array($columns)) {
  1017. tripal_report_error('tripal_core', TRIPAL_ERROR,
  1018. 'chado_select_record; the $columns argument must be an array. Columns:%columns',
  1019. array('%columns' => print_r($columns, TRUE)),
  1020. array('print' => $print_errors)
  1021. );
  1022. return FALSE;
  1023. }
  1024. if (!is_array($values)) {
  1025. tripal_report_error('tripal_core', TRIPAL_ERROR,
  1026. 'chado_select_record; the $values argument must be an array. Values:%values',
  1027. array('%values' => print_r($values, TRUE)),
  1028. array('print' => $print_errors)
  1029. );
  1030. return FALSE;
  1031. }
  1032. // Get the table description.
  1033. $table_desc = chado_get_schema($table);
  1034. if (!is_array($table_desc)) {
  1035. tripal_report_error('tripal_chado', TRIPAL_WARNING,
  1036. 'chado_insert_record; There is no table description for !table_name',
  1037. array('!table_name' => $table), array('print' => $print_errors)
  1038. );
  1039. return FALSE;
  1040. }
  1041. $where = array();
  1042. $args = array();
  1043. // if the 'is_duplicate' option is turned on then we want to remove all but unique keys
  1044. if ($options['is_duplicate'] and array_key_exists('unique keys', $table_desc)) {
  1045. $ukeys = $table_desc['unique keys'];
  1046. $has_results = 0;
  1047. // iterate through the unique constraints and reset the values and columns
  1048. // arrays to only include these fields
  1049. foreach ($ukeys as $cname => $fields) {
  1050. if ($has_results) {
  1051. continue;
  1052. }
  1053. $new_values = array();
  1054. $new_columns = array();
  1055. $new_options = array();
  1056. $uq_sname = "uq_" . $table . "_";
  1057. $has_pkey = 0;
  1058. // include the primary key in the results returned
  1059. if (array_key_exists('primary key', $table_desc)) {
  1060. $has_pkey = 1;
  1061. $pkeys = $table_desc['primary key'];
  1062. foreach ($pkeys as $index => $key) {
  1063. array_push($new_columns, $key);
  1064. }
  1065. }
  1066. // recreate the $values and $columns arrays
  1067. foreach ($fields as $field) {
  1068. if (array_key_exists($field, $values)) {
  1069. $new_values[$field] = $values[$field];
  1070. $uq_sname .= substr($field, 0, 2);
  1071. // if there is no primary key then use the unique contraint fields
  1072. if (!$has_pkey) {
  1073. array_push($new_columns, $field);
  1074. }
  1075. }
  1076. // if the field doesn't exist in the values array then
  1077. // substitute any default values
  1078. elseif (array_key_exists('default', $table_desc['fields'][$field])) {
  1079. $new_values[$field] = $table_desc['fields'][$field]['default'];
  1080. $uq_sname .= substr($field, 0, 2);
  1081. if (!$has_pkey) {
  1082. array_push($new_columns, $field);
  1083. }
  1084. }
  1085. // if there is no value (default or otherwise) check if this field is
  1086. // allowed to be null
  1087. elseif (!$table_desc['fields'][$field]['not null']) {
  1088. $new_values[$field] = NULL;
  1089. $uq_sname .= "n" . substr($field, 0, 2);
  1090. if (!$has_pkey) {
  1091. array_push($new_columns, $field);
  1092. }
  1093. }
  1094. // if the array key doesn't exist in the values given by the caller
  1095. // and there is no default value then we cannot check if the record
  1096. // is a duplicate so return FALSE
  1097. else {
  1098. tripal_report_error('tripal_core', TRIPAL_ERROR,
  1099. 'chado_select_record: There is no value for %field thus we cannot ' .
  1100. 'check if this record for table, %table, is unique. %values',
  1101. array('%field' => $field, '%table' => $table, '%values' => print_r($values, TRUE)),
  1102. array('print' => $print_errors));
  1103. return FALSE;
  1104. }
  1105. }
  1106. $results = chado_select_record($table, $new_columns, $new_values, $new_options);
  1107. // if we have a duplicate record then return the results
  1108. if (count($results) > 0) {
  1109. $has_results = 1;
  1110. }
  1111. unset($new_columns);
  1112. unset($new_values);
  1113. unset($new_options);
  1114. }
  1115. if ($options['has_record'] and $has_results) {
  1116. return TRUE;
  1117. }
  1118. else {
  1119. return $results;
  1120. }
  1121. }
  1122. // Process the values array into where clauses and retrieve foreign keys. The
  1123. // $where array should always be an integer-indexed array with each value
  1124. // being an array with a 'field', 'op', and 'data' keys with all foreign keys
  1125. // followed.
  1126. foreach ($values as $field => $value) {
  1127. // Require the field be in the table description.
  1128. if (!array_key_exists($field, $table_desc['fields'])) {
  1129. tripal_report_error('tripal_core', TRIPAL_ERROR,
  1130. 'chado_select_record: The field "%field" does not exist for the table "%table". Cannot perform query. Values: %array',
  1131. array('%field' => $field, '%table' => $table, '%array' => print_r($values, 1)),
  1132. array('print' => $print_errors)
  1133. );
  1134. return array();
  1135. }
  1136. // CASE 1: We have an array for a value.
  1137. if (is_array($value)) {
  1138. // CASE 1a: If there is only one element in the array, treat it the same
  1139. // as a non-array value.
  1140. if (count($value) == 1 AND is_int(key($value))) {
  1141. $value = array_pop($value);
  1142. $op = '=';
  1143. chado_select_record_check_value_type($op, $value, $table_desc['fields'][$field]['type']);
  1144. $where[] = array(
  1145. 'field' => $field,
  1146. 'op' => $op,
  1147. 'data' => $value
  1148. );
  1149. }
  1150. // CASE 1b: If there is a 'data' key in the array then we have the new
  1151. // complex filtering format with a single criteria.
  1152. elseif (isset($value['data']) AND isset($value['op'])) {
  1153. $value['field'] = $field;
  1154. $where[] = $value;
  1155. }
  1156. // CASE 1c: If we have an integer indexed array and the first element is
  1157. // not an array then we have a simple array of values to be used for an IN clause.
  1158. elseif (is_int(key($value)) AND !is_array(current($value))) {
  1159. $where[] = array(
  1160. 'field' => $field,
  1161. 'op' => 'IN',
  1162. 'data' => $value
  1163. );
  1164. }
  1165. // We have a multi-dimensional array: 2 cases...
  1166. else {
  1167. // CASE 1d: If there is a multi-dimensional array with each sub-array
  1168. // containing a data key then we have the new complex filtering format
  1169. // with multiple criteria.
  1170. if (isset($value[0]['data']) AND isset($value[0]['op'])) {
  1171. foreach ($value as $subvalue) {
  1172. $subvalue['field'] = $field;
  1173. $where[] = $subvalue;
  1174. }
  1175. }
  1176. // CASE 1e: We have a multi-dimensional array that doesn't fit any of the
  1177. // above cases then we have a foreign key definition to follow.
  1178. else {
  1179. // Select the value from the foreign key relationship for this value.
  1180. $foreign_options = array(
  1181. 'regex_columns' => $options['regex_columns'],
  1182. );
  1183. $results = chado_schema_get_foreign_key($table_desc, $field, $value, $foreign_options);
  1184. // Ensure that looking up the foreign key didn't fail in an error.
  1185. if ($results === FALSE OR $results === NULL) {
  1186. tripal_report_error('tripal_core', TRIPAL_ERROR,
  1187. 'chado_select_record: could not follow the foreign key definition
  1188. for %field where the definition supplied was %value',
  1189. array('%field' => $field, '%value' => print_r($value, TRUE))
  1190. );
  1191. return array();
  1192. }
  1193. // Ensure that there were results returned.
  1194. elseif (count($results)==0) {
  1195. tripal_report_error('tripal_core', TRIPAL_ERROR,
  1196. 'chado_select_record: the foreign key definition for \'%field\' '.
  1197. 'returned no results where the definition supplied was %value',
  1198. array('%field' => $field, '%value' => print_r($value, TRUE))
  1199. );
  1200. return array();
  1201. }
  1202. // If there was only a single resutlt then add it using an op of =.
  1203. elseif (count($results) == 1) {
  1204. $results = array_pop($results);
  1205. $op = '=';
  1206. chado_select_record_check_value_type($op, $results, $table_desc['fields'][$field]['type']);
  1207. $where[] = array(
  1208. 'field' => $field,
  1209. 'op' => $op,
  1210. 'data' => $results
  1211. );
  1212. }
  1213. // Otherwise multiple results were returned so we want to form an
  1214. // IN (x, y, z) expression.
  1215. else {
  1216. $where[] = array(
  1217. 'field' => $field,
  1218. 'op' => 'IN',
  1219. 'data' => $results
  1220. );
  1221. }
  1222. }
  1223. }
  1224. }
  1225. // CASE 2: We have a single value.
  1226. else {
  1227. $op = '=';
  1228. chado_select_record_check_value_type($op, $value, $table_desc['fields'][$field]['type']);
  1229. $where[] = array(
  1230. 'field' => $field,
  1231. 'op' => $op,
  1232. 'data' => $value
  1233. );
  1234. }
  1235. // Support Deprecated method for regex conditions.
  1236. $current_key = key($where);
  1237. if (in_array($field, $options['regex_columns'])) {
  1238. $where[$current_key]['op'] = '~*';
  1239. }
  1240. }
  1241. // Now build the SQL.
  1242. if (empty($where)) {
  1243. // Sometimes want to select everything.
  1244. $sql = "SELECT " . implode(', ', $columns) . " ";
  1245. $sql .= 'FROM {' . $table . '} ';
  1246. }
  1247. else {
  1248. $sql = "SELECT " . implode(', ', $columns) . " ";
  1249. $sql .= 'FROM {' . $table . '} ';
  1250. // If $values is empty then we want all results so no where clause.
  1251. if (!empty($values)) {
  1252. $sql .= "WHERE ";
  1253. }
  1254. foreach ($where as $clause_num => $value_def) {
  1255. switch ($value_def['op']) {
  1256. // Deal with 'field IN (x, y, z)' where clauses.
  1257. case 'IN':
  1258. $sql .= $value_def['field'] . " IN (";
  1259. $index = 0;
  1260. foreach ($value_def['data'] as $v) {
  1261. $placeholder = ':' . $value_def['field'] . $clause_num .'_' . $index;
  1262. $sql .= $placeholder . ', ';
  1263. $args[$placeholder] = $v;
  1264. $index++;
  1265. }
  1266. $sql = drupal_substr($sql, 0, -2); // remove trailing ', '
  1267. $sql .= ") AND ";
  1268. break;
  1269. // Deal with IS NULL.
  1270. case 'IS NULL':
  1271. $sql .= $value_def['field'] . ' IS NULL AND ';
  1272. break;
  1273. // Default is [field] [op] [data].
  1274. default:
  1275. $placeholder = ':'. $value_def['field'] . $clause_num;
  1276. // Support case insensitive columns.
  1277. if (in_array($value_def['field'], $options['case_insensitive_columns'])) {
  1278. $sql .= 'lower(' . $value_def['field'] .') '. $value_def['op'] .' lower('. $placeholder . ') AND ';
  1279. }
  1280. else {
  1281. $sql .= $value_def['field'] .' '. $value_def['op'] .' '. $placeholder . ' AND ';
  1282. }
  1283. $args[$placeholder] = $value_def['data'];
  1284. }
  1285. } // end foreach item in where clause.
  1286. $sql = drupal_substr($sql, 0, -4); // get rid of the trailing 'AND '
  1287. } // end if (empty($where)){ } else {
  1288. // Add any ordering of the results to the SQL statement.
  1289. if (count($options['order_by']) > 0) {
  1290. $sql .= " ORDER BY ";
  1291. foreach ($options['order_by'] as $field => $dir) {
  1292. $sql .= "$field $dir, ";
  1293. }
  1294. $sql = drupal_substr($sql, 0, -2); // get rid of the trailing ', '
  1295. }
  1296. // Limit the records returned
  1297. if (array_key_exists('limit', $options) and is_numeric($options['limit'])) {
  1298. $sql .= " LIMIT " . $options['limit'];
  1299. if (array_key_exists('offset', $options) and is_numeric($options['offset'])) {
  1300. $sql .= " OFFSET " . $options['offset'];
  1301. }
  1302. }
  1303. // if the caller has requested the SQL rather than the results then do so.
  1304. if ($options['return_sql'] == TRUE) {
  1305. return array('sql' => $sql, 'args' => $args);
  1306. }
  1307. if (array_key_exists('limit', $pager)) {
  1308. $total_records = 0;
  1309. $resource = chado_pager_query($sql, $args, $pager['limit'], $pager['element'], NULL, $total_records);
  1310. }
  1311. else {
  1312. $resource = chado_query($sql, $args);
  1313. }
  1314. // Format results into an array.
  1315. $results = array();
  1316. foreach ($resource as $r) {
  1317. $results[] = $r;
  1318. }
  1319. if ($options['has_record']) {
  1320. return count($results);
  1321. }
  1322. return $results;
  1323. }
  1324. /**
  1325. * Helper Function: check that the value is the correct type.
  1326. *
  1327. * This function is used by chado_select_record() when building the $where
  1328. * clause array to ensure that any single values are the correct type based
  1329. * on the table definition. Furthermore, it ensures that NULL's are caught
  1330. * changing the operator to 'IS NULL'.
  1331. * @code
  1332. $op = '=';
  1333. chado_select_record_check_value_type($op, $value, $table_desc['fields'][$field]['type']);
  1334. $where[] = array(
  1335. 'field' => $field,
  1336. 'op' => $op,
  1337. 'data' => $value
  1338. );
  1339. * @endcode
  1340. *
  1341. * @param $op
  1342. * The operator being used. This is mostly passed in to allow it to be changed
  1343. * if a NULL value is detected.
  1344. * @param $value
  1345. * The value to be checked and adjusted.
  1346. * @param $type
  1347. * The type from the table definition that's used to determine the type of
  1348. * value.
  1349. */
  1350. function chado_select_record_check_value_type(&$op, &$value, $type) {
  1351. if ($value === NULL) {
  1352. $op = 'IS NULL';
  1353. }
  1354. elseif ($type == 'int') {
  1355. $value = (int) $value;
  1356. }
  1357. }
  1358. /**
  1359. * Use this function instead of db_query() to avoid switching databases
  1360. * when making query to the chado database.
  1361. *
  1362. * Will use a chado persistent connection if it already exists.
  1363. *
  1364. * NOTE: When writting SQL queries it is important to enclose the chado table name in
  1365. * curley brackets (ie: {feature}) in order to enusure proper prefixing. Furthermore,
  1366. * if you need to join between tables in separate schemas (ie: chado and drupal) then
  1367. * you should enclose the drupal table in square brackets (ie: [chado_feature]).
  1368. * Keep in mind JOINING BETWEEN SCHEMA'S IS NOT RECOMMENDED since it will break functionality
  1369. * for sites with external chado databases.
  1370. *
  1371. * @param $sql
  1372. * The sql statement to execute
  1373. *
  1374. * @param $args
  1375. * The array of arguments, with the same structure as passed to
  1376. * the db_query() function of Drupal.
  1377. *
  1378. * @return
  1379. * DatabaseStatementInterface A prepared statement object, already executed.
  1380. *
  1381. * Example usage:
  1382. * @code
  1383. * $sql = "SELECT F.name, CVT.name as type_name, ORG.common_name
  1384. * FROM {feature} F
  1385. * LEFT JOIN {cvterm} CVT ON F.type_id = CVT.cvterm_id
  1386. * LEFT JOIN {organism} ORG ON F.organism_id = ORG.organism_id
  1387. * WHERE
  1388. * F.uniquename = :feature_uniquename";
  1389. * $args = array( ':feature_uniquename' => $form_state['values']['uniquename'] );
  1390. * $result = chado_query( $sql, $args );
  1391. * foreach ($result as $r) { [Do something with the records here] }
  1392. * @endcode
  1393. *
  1394. * @ingroup tripal_chado_query_api
  1395. */
  1396. function chado_query($sql, $args = array()) {
  1397. $is_local = isset($GLOBALS["chado_is_local"]) && $GLOBALS["chado_is_local"];
  1398. // Args should be an array
  1399. if (!is_array($args)) {
  1400. tripal_report_error('tripal_core', TRIPAL_ERROR,
  1401. 'chado_query; Need to pass an array to chado_query, "%value" passed instead. Query: %query',
  1402. array('%value' => $args, '%query' => $sql)
  1403. );
  1404. return FALSE;
  1405. }
  1406. // if Chado is local to the database then prefix the Chado table
  1407. // names with 'chado'.
  1408. if ($is_local) {
  1409. // Remove carriage returns from the SQL.
  1410. $sql = preg_replace('/\n/', '', $sql); // remove carriage returns
  1411. // Prefix the tables with their correct schema.
  1412. // Chado tables should be enclosed in curly brackets (ie: {feature} )
  1413. // and Drupal tables should be enclosed in square brackets (ie: [tripal_jobs] ).
  1414. $chado_schema_name = tripal_get_schema_name('chado');
  1415. $drupal_schema_name = tripal_get_schema_name('drupal');
  1416. $sql = preg_replace('/\{(.*?)\}/', $chado_schema_name.'.$1', $sql);
  1417. $sql = preg_replace('/\[(\w+)\]/', $drupal_schema_name.'.$1', $sql);
  1418. // Add an alter hook to allow module developers to change the query right before it's
  1419. // executed. Since all queriying of chado by Tripal eventually goes through this
  1420. // function, we only need to provide an alter hook at this point in order to ensure
  1421. // developers have complete control over the query being executed. For example,
  1422. // a module developer might want to remove schema prefixing from queries and rely
  1423. // on the search path. This alter hook would allow them to do that by implementing
  1424. // mymodule_chado_query_alter($sql, $args) and using a regular expression to remove
  1425. // table prefixing from the query.
  1426. // @see hook_chado_query_alter().
  1427. drupal_alter('chado_query', $sql, $args);
  1428. // The featureloc table has some indexes that use function that call other
  1429. // functions and those calls do not reference a schema, therefore, any
  1430. // tables with featureloc must automaticaly have the chado schema set as
  1431. // active to find.
  1432. if (preg_match('/'.$chado_schema_name.'.featureloc/i', $sql) or preg_match('/'.$chado_schema_name.'.feature/i', $sql)) {
  1433. $previous_db = chado_set_active('chado');
  1434. $results = db_query($sql, $args);
  1435. chado_set_active($previous_db);
  1436. }
  1437. // for all other tables we should have everything in scope so just run the query
  1438. else {
  1439. $results = db_query($sql, $args);
  1440. }
  1441. }
  1442. // Check for any cross schema joins (ie: both drupal and chado tables represented
  1443. // and if present don't execute the query but instead warn the administrator.
  1444. elseif (preg_match('/\[(\w*?)\]/', $sql)) {
  1445. tripal_report_error(
  1446. 'chado_query',
  1447. TRIPAL_ERROR,
  1448. 'The following query does not support external chado databases. Please file an issue
  1449. with the Drupal.org Tripal Project. Query: @query',
  1450. array('@query' => $sql)
  1451. );
  1452. return FALSE;
  1453. }
  1454. // if Chado is not local to the Drupal database then we have to
  1455. // switch to another database
  1456. else {
  1457. $previous_db = chado_set_active('chado') ;
  1458. $results = db_query($sql, $args);
  1459. chado_set_active($previous_db);
  1460. }
  1461. return $results;
  1462. }
  1463. /**
  1464. * This hook provides a way for module developers to alter any/all queries on the chado
  1465. * schema by Tripal.
  1466. *
  1467. * Example: a module developer might want to remove schema prefixing from queries and rely
  1468. * on the search path. This alter hook would allow them to do that by implementing
  1469. * mymodule_chado_query_alter($sql, $args) and using a regular expression to remove
  1470. * table prefixing from the query.
  1471. *
  1472. * @param $sql
  1473. * A string describing the SQL query to be executed by Tripal. All parameters should be
  1474. * indicated by :tokens with values being in the $args array and all tables should
  1475. * be prefixed with the schema name described in tripal_get_schema_name().
  1476. * @param $args
  1477. * An array of arguments where the key is the token used in $sql (for example, :value)
  1478. * and the value is the value you would like substituted in.
  1479. */
  1480. function hook_chado_query_alter(&$sql, &$args) {
  1481. // The following code is an example of how this alter function might be used.
  1482. // Say you would like only a portion of node => feature connections available
  1483. // for a period of time or under a specific condition. To "hide" the other connections
  1484. // you might create a temporary view of the chado_feature table that only includes
  1485. // the connections you would like to be available. In order to ensure this view
  1486. // is used rather than the original chado_feature table you could alter all Tripal
  1487. // queries referring to chado_feature to instead refer to your view.
  1488. if (preg_match('/(\w+)\.chado_feature/', $sql, $matches)) {
  1489. $sql = str_replace(
  1490. $matches[1] . '.chado_feature',
  1491. 'chado_feature_view',
  1492. $sql
  1493. );
  1494. }
  1495. }
  1496. /**
  1497. * Use this function instead of pager_query() when selecting a
  1498. * subset of records from a Chado table.
  1499. *
  1500. * @param $query
  1501. * The SQL statement to execute, this is followed by a variable number of args
  1502. * used as substitution values in the SQL statement.
  1503. * @param $args
  1504. * The array of arguments for the query. They keys are the placeholders
  1505. * @param $limit
  1506. * The number of query results to display per page.
  1507. * @param $element
  1508. * An numeric identifier used to distinguish between multiple pagers on one page.
  1509. * @param $count_query
  1510. * An SQL query used to count matching records.
  1511. *
  1512. * @returns
  1513. * A database query result resource or FALSE if the query was not
  1514. * executed correctly
  1515. *
  1516. * @ingroup tripal_chado_query_api
  1517. */
  1518. function chado_pager_query($query, $args, $limit, $element, $count_query = '') {
  1519. // get the page and offset for the pager
  1520. $page_arg = isset($_GET['page']) ? $_GET['page'] : '0';
  1521. $pages = explode(',', $page_arg);
  1522. $page = 0;
  1523. if (count($pages) >= $element) {
  1524. $page = key_exists($element, $pages) ? $pages[$element] : 0;
  1525. }
  1526. $offset = $limit * $page;
  1527. $q = $_GET['q'];
  1528. // Construct a count query if none was given.
  1529. if (!isset($count_query)) {
  1530. $count_query = preg_replace(array('/SELECT.*?FROM /As', '/ORDER BY .*/'),
  1531. array('SELECT COUNT(*) FROM ', ''), $query);
  1532. }
  1533. // We calculate the total of pages as ceil(items / limit).
  1534. $results = chado_query($count_query, $args);
  1535. if (!$results) {
  1536. tripal_report_error('tripal_core', TRIPAL_ERROR,
  1537. "chado_pager_query(): Query failed: %cq", array('%cq' => $count_query));
  1538. return;
  1539. }
  1540. $total_records = $results->fetchField();
  1541. // set a session variable for storing the total number of records
  1542. $GLOBALS['chado_pager'][$q][$element]['total_records'] = $total_records;
  1543. pager_default_initialize($total_records, $limit, $element);
  1544. $query .= ' LIMIT ' . (int) $limit . ' OFFSET ' . (int) $offset;
  1545. $results = chado_query($query, $args);
  1546. return $results;
  1547. }
  1548. /**
  1549. * A function to retrieve the total number of records for a pager that
  1550. * was generated using the chado_pager_query() function
  1551. *
  1552. * @param $element
  1553. * The $element argument that was passed to the chado_pager_query function
  1554. *
  1555. * @ingroup tripal_chado_query_api
  1556. */
  1557. function chado_pager_get_count($element) {
  1558. $q = $_GET['q'];
  1559. if (array_key_exists($q, $GLOBALS['chado_pager']) and
  1560. array_key_exists($element, $GLOBALS['chado_pager'][$q])) {
  1561. return $GLOBALS['chado_pager'][$q][$element]['total_records'];
  1562. }
  1563. else {
  1564. return 0;
  1565. }
  1566. }
  1567. /**
  1568. * Gets the value of a foreign key relationship
  1569. *
  1570. * This function is used by chado_select_record, chado_insert_record,
  1571. * and chado_update_record to iterate through the associate array of
  1572. * values that gets passed to each of those routines. The values array
  1573. * is nested where foreign key contraints are used to specify a value that. See
  1574. * documentation for any of those functions for further information.
  1575. *
  1576. * @param $table_desc
  1577. * A table description for the table with the foreign key relationship to be identified generated by
  1578. * hook_chado_<table name>_schema()
  1579. * @param $field
  1580. * The field in the table that is the foreign key.
  1581. * @param $values
  1582. * An associative array containing the values
  1583. * @param $options
  1584. * An associative array of additional options where the key is the option
  1585. * and the value is the value of that option. These options are passed on to chado_select_record.
  1586. *
  1587. * Additional Options Include:
  1588. * - case_insensitive_columns
  1589. * An array of columns to do a case insensitive search on.
  1590. * - regex_columns
  1591. * An array of columns where the value passed in should be treated as a regular expression
  1592. *
  1593. * @return
  1594. * A string containg the results of the foreign key lookup, or FALSE if failed.
  1595. *
  1596. * Example usage:
  1597. * @code
  1598. *
  1599. * $values = array(
  1600. * 'genus' => 'Citrus',
  1601. * 'species' => 'sinensis',
  1602. * );
  1603. * $value = chado_schema_get_foreign_key('feature', 'organism_id',$values);
  1604. *
  1605. * @endcode
  1606. * The above code selects a record from the feature table using the three fields
  1607. * that uniquely identify a feature. The $columns array simply lists the columns
  1608. * to select. The $values array is nested such that the organism is identified by
  1609. * way of the organism_id foreign key constraint by specifying the genus and
  1610. * species. The cvterm is also specified using its foreign key and the cv_id
  1611. * for the cvterm is nested as well.
  1612. *
  1613. * @ingroup tripal_core
  1614. */
  1615. function chado_schema_get_foreign_key($table_desc, $field, $values, $options = NULL) {
  1616. // set defaults for options. If we don't set defaults then
  1617. // we get memory leaks when we try to access the elements
  1618. if (!is_array($options)) {
  1619. $options = array();
  1620. }
  1621. if (!array_key_exists('case_insensitive_columns', $options)) {
  1622. $options['case_insensitive_columns'] = array();
  1623. }
  1624. if (!array_key_exists('regex_columns', $options)) {
  1625. $options['regex_columns'] = array();
  1626. }
  1627. // get the list of foreign keys for this table description and
  1628. // iterate through those until we find the one we're looking for
  1629. $fkeys = '';
  1630. if (array_key_exists('foreign keys', $table_desc)) {
  1631. $fkeys = $table_desc['foreign keys'];
  1632. }
  1633. if ($fkeys) {
  1634. foreach ($fkeys as $name => $def) {
  1635. if (is_array($def['table'])) {
  1636. //foreign key was described 2X
  1637. $message = "The foreign key " . $name . " was defined twice. Please check modules "
  1638. . "to determine if hook_chado_schema_<version>_" . $table_desc['table'] . "() was "
  1639. . "implemented and defined this foreign key when it wasn't supposed to. Modules "
  1640. . "this hook was implemented in: " . implode(', ',
  1641. module_implements("chado_" . $table_desc['table'] . "_schema")) . ".";
  1642. tripal_report_error('tripal_core', $message);
  1643. drupal_set_message(check_plain($message), 'error');
  1644. continue;
  1645. }
  1646. $table = $def['table'];
  1647. $columns = $def['columns'];
  1648. // iterate through the columns of the foreign key relationship
  1649. foreach ($columns as $left => $right) {
  1650. // does the left column in the relationship match our field?
  1651. if (strcmp($field, $left) == 0) {
  1652. // the column name of the foreign key matches the field we want
  1653. // so this is the right relationship. Now we want to select
  1654. $select_cols = array($right);
  1655. $result = chado_select_record($table, $select_cols, $values, $options);
  1656. $fields = array();
  1657. if ($result and count($result) > 0) {
  1658. foreach ($result as $obj) {
  1659. $fields[] = $obj->$right;
  1660. }
  1661. return $fields;
  1662. }
  1663. }
  1664. }
  1665. }
  1666. }
  1667. else {
  1668. // @todo: what do we do if we get to this point and we have a fk
  1669. // relationship expected but we don't have any definition for one in the
  1670. // table schema??
  1671. $version = $GLOBALS["chado_version"];
  1672. $message = t("There is no foreign key relationship defined for " . $field . " .
  1673. To define a foreign key relationship, determine the table this foreign
  1674. key referrs to (<foreign table>) and then implement
  1675. hook_chado_chado_schema_v<version>_<foreign table>(). See
  1676. tripal_feature_chado_v1_2_schema_feature for an example. Chado version: $version");
  1677. tripal_report_error('tripal_core', $message);
  1678. drupal_set_message(check_plain($message), 'error');
  1679. }
  1680. return array();
  1681. }
  1682. /**
  1683. * Retrieve the name of the PostgreSQL schema housing Chado or Drupal.
  1684. *
  1685. * @param $schema
  1686. * Wehter you want the schema name for 'chado' or 'drupal'. Chado is the default.
  1687. * @return
  1688. * The name of the PostgreSQL schema housing the $schema specified.
  1689. */
  1690. function tripal_get_schema_name($schema = 'chado') {
  1691. // First we will set our default. This is what will be returned in most cases.
  1692. if ($schema == 'chado') {
  1693. $schema_name = 'chado';
  1694. }
  1695. else {
  1696. $schema_name = 'public';
  1697. }
  1698. // There are cases where modules or admin might need to change the default
  1699. // names for the schema. Thus we provide an alter hook here to allow
  1700. // the names to be changed and ensure that schema names are never hardcoded
  1701. // directly into queries.
  1702. $context = array('schema' => $schema);
  1703. drupal_alter('tripal_get_schema_name', $schema_name, $context);
  1704. return $schema_name;
  1705. }
  1706. /**
  1707. * Alter the name of the schema housing Chado and/or Drupal.
  1708. *
  1709. * This example implementation shows a solution for the case where your chado database
  1710. * was well established in the "public" schema and you added Drupal later in a
  1711. * "drupal" schema. Please note that this has not been tested and while we can ensure
  1712. * that Tripal will work as expected, we have no control over whether Drupal is
  1713. * compatible with not being in the public schema. That's why we recommened the
  1714. * organization we have (ie: Chado in a "chado" schema and Drupal in the "public schema).
  1715. *
  1716. * @param $schema_name
  1717. * The current name of the schema as known by Tripal. This is likely the default
  1718. * set in tripal_get_schema_name() but in the case of multiple alter hooks, it might
  1719. * be different.
  1720. * @param $context
  1721. * This is an array of items to provide context.
  1722. * - schema: this is the schema that was passed to tripal_get_schema_name() and will
  1723. * be either "chado" or "drupal". This should be used to determine you are changing
  1724. * the name of the correct schema.
  1725. */
  1726. function hook_tripal_get_schema_name_alter($schema_name, $context) {
  1727. // First we check which schema was passed to chado_get_schema().
  1728. // Notice that we do not use $schema_name since it may already have
  1729. // been altered by another module.
  1730. if ($context['schema'] == 'chado') {
  1731. $schema_name = 'public';
  1732. }
  1733. // Notice that we use elseif to capture the second case rather than else. This
  1734. // avoids the assumption that there is only one chado and one drupal schema.
  1735. elseif ($context['schema'] == 'drupal') {
  1736. $schema_name = 'drupal';
  1737. }
  1738. }