tripal_chado.query.api.inc 74 KB

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