tripal_core_chado.api.inc 110 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793179417951796179717981799180018011802180318041805180618071808180918101811181218131814181518161817181818191820182118221823182418251826182718281829183018311832183318341835183618371838183918401841184218431844184518461847184818491850185118521853185418551856185718581859186018611862186318641865186618671868186918701871187218731874187518761877187818791880188118821883188418851886188718881889189018911892189318941895189618971898189919001901190219031904190519061907190819091910191119121913191419151916191719181919192019211922192319241925192619271928192919301931193219331934193519361937193819391940194119421943194419451946194719481949195019511952195319541955195619571958195919601961196219631964196519661967196819691970197119721973197419751976197719781979198019811982198319841985198619871988198919901991199219931994199519961997199819992000200120022003200420052006200720082009201020112012201320142015201620172018201920202021202220232024202520262027202820292030203120322033203420352036203720382039204020412042204320442045204620472048204920502051205220532054205520562057205820592060206120622063206420652066206720682069207020712072207320742075207620772078207920802081208220832084208520862087208820892090209120922093209420952096209720982099210021012102210321042105210621072108210921102111211221132114211521162117211821192120212121222123212421252126212721282129213021312132213321342135213621372138213921402141214221432144214521462147214821492150215121522153215421552156215721582159216021612162216321642165216621672168216921702171217221732174217521762177217821792180218121822183218421852186218721882189219021912192219321942195219621972198219922002201220222032204220522062207220822092210221122122213221422152216221722182219222022212222222322242225222622272228222922302231223222332234223522362237223822392240224122422243224422452246224722482249225022512252225322542255225622572258225922602261226222632264226522662267226822692270227122722273227422752276227722782279228022812282228322842285228622872288228922902291229222932294229522962297229822992300230123022303230423052306230723082309231023112312231323142315231623172318231923202321232223232324232523262327232823292330233123322333233423352336233723382339234023412342234323442345234623472348234923502351235223532354235523562357235823592360236123622363236423652366236723682369237023712372237323742375237623772378237923802381238223832384238523862387238823892390239123922393239423952396239723982399240024012402240324042405240624072408240924102411241224132414241524162417241824192420242124222423242424252426242724282429243024312432243324342435243624372438243924402441244224432444244524462447244824492450245124522453245424552456245724582459246024612462246324642465246624672468246924702471247224732474247524762477247824792480248124822483248424852486248724882489249024912492249324942495249624972498249925002501250225032504250525062507250825092510251125122513251425152516251725182519252025212522252325242525252625272528252925302531253225332534253525362537253825392540254125422543254425452546254725482549255025512552255325542555255625572558255925602561256225632564256525662567256825692570257125722573257425752576257725782579258025812582258325842585258625872588258925902591259225932594259525962597259825992600260126022603260426052606260726082609261026112612261326142615261626172618261926202621262226232624262526262627262826292630263126322633263426352636263726382639264026412642264326442645264626472648264926502651265226532654265526562657265826592660266126622663266426652666266726682669267026712672267326742675267626772678267926802681268226832684268526862687268826892690269126922693269426952696269726982699270027012702270327042705270627072708270927102711271227132714271527162717271827192720272127222723272427252726272727282729273027312732273327342735273627372738273927402741274227432744274527462747274827492750275127522753275427552756275727582759276027612762276327642765276627672768276927702771277227732774277527762777277827792780278127822783278427852786278727882789279027912792279327942795279627972798279928002801280228032804280528062807280828092810281128122813281428152816281728182819282028212822282328242825282628272828282928302831283228332834283528362837283828392840284128422843284428452846284728482849285028512852285328542855285628572858285928602861286228632864286528662867286828692870287128722873287428752876287728782879288028812882288328842885288628872888288928902891289228932894289528962897289828992900290129022903290429052906290729082909291029112912291329142915291629172918291929202921292229232924292529262927292829292930293129322933293429352936293729382939294029412942294329442945294629472948294929502951295229532954295529562957295829592960296129622963296429652966296729682969297029712972297329742975297629772978297929802981298229832984298529862987298829892990299129922993299429952996299729982999300030013002300330043005300630073008300930103011301230133014301530163017301830193020302130223023302430253026302730283029303030313032303330343035303630373038303930403041304230433044304530463047304830493050305130523053305430553056305730583059306030613062306330643065306630673068306930703071307230733074307530763077307830793080308130823083308430853086308730883089309030913092309330943095309630973098309931003101310231033104
  1. <?php
  2. require_once "tripal_core.schema_v1.2.api.inc";
  3. require_once "tripal_core.schema_v1.11.api.inc";
  4. // Globals used by Tripals Error catching functions
  5. // Should match those defined by watchdog
  6. define('TRIPAL_CRITICAL',2);
  7. define('TRIPAL_ERROR',3);
  8. define('TRIPAL_WARNING',4);
  9. define('TRIPAL_NOTICE',5);
  10. define('TRIPAL_INFO',6);
  11. define('TRIPAL_DEBUG',7);
  12. /**
  13. * Provide better error notice for Tripal
  14. * @param $type
  15. * The catagory to which this message belongs. Can be any string, but the general
  16. * practice is to use the name of the module.
  17. * @param $message
  18. * The message to store in the log. Keep $message translatable by not concatenating
  19. * dynamic values into it! Variables in the message should be added by using placeholder
  20. * strings alongside the variables argument to declare the value of the placeholders.
  21. * See t() for documentation on how $message and $variables interact.
  22. * @param $variables
  23. * Array of variables to replace in the message on display or NULL if message is
  24. * already translated or not possible to translate.
  25. * @param $severity
  26. * The severity of the message; one of the following values:
  27. * - TRIPAL_CRITICAL: Critical conditions.
  28. * - TRIPAL_ERROR: Error conditions.
  29. * - TRIPAL_WARNING: Warning conditions.
  30. * - TRIPAL_NOTICE: (default) Normal but significant conditions.
  31. * - TRIPAL_INFO: Informational messages.
  32. * - TRIPAL_DEBUG: Debug-level messages.
  33. * @param $options
  34. * An array of options. Some available options include:
  35. * - print: prints the error message to the screen. Useful when display is the command-line
  36. */
  37. function tripal_core_report_error($type, $severity, $message, $variables = array(), $options = array()) {
  38. // Get human-readable severity string
  39. $severity_string = '';
  40. switch ($severity) {
  41. case TRIPAL_CRITICAL:
  42. $severity_string = 'CRITICAL';
  43. break;
  44. case TRIPAL_ERROR:
  45. $severity_string = 'ERROR';
  46. break;
  47. case TRIPAL_WARNING:
  48. $severity_string = 'WARNING';
  49. break;
  50. case TRIPAL_NOTICE:
  51. $severity_string = 'NOTICE';
  52. break;
  53. case TRIPAL_INFO:
  54. $severity_string = 'INFO';
  55. break;
  56. case TRIPAL_DEBUG:
  57. $severity_string = 'DEBUG';
  58. break;
  59. }
  60. // Send to watchdog
  61. try {
  62. watchdog($type, $message, $variables, $severity);
  63. }
  64. catch (Exception $e) {
  65. print "CRITICAL (TRIPAL_CORE): Unable to register error message with watchdog";
  66. $options['print'] = TRUE;
  67. }
  68. // If print option supplied then print directly to the screen
  69. if (isset($options['print'])) {
  70. if (sizeof($variables) > 0) {
  71. $message = str_replace(array_keys($variables), $variables, $message);
  72. }
  73. print $severity_string . ' (' . strtoupper($type) . '):' . $message . "\n";
  74. }
  75. }
  76. /**
  77. * @file
  78. * The Tripal Core API
  79. *
  80. * This file provides the API needed for all other Tripal and Tripal dependent
  81. * modules.
  82. *
  83. *
  84. * @defgroup tripal_chado_api Chado API
  85. * @ingroup tripal_core_api
  86. * @{
  87. * Provides an application programming interface (API) to manage data withing the Chado database.
  88. * This includes functions for selecting, inserting, updating and deleting records
  89. * in Chado tables. The functions will ensure proper integrity contraints are met
  90. * for inserts and updates.
  91. *
  92. * Also, a set of functions is provided for creating template variables. First,
  93. * is the tripal_core_generate_chado_vars which is used to select one ore more
  94. * records from a table and return an array with foreign key relationships fully
  95. * populated. For example, if selecting a feature, the organism_id and type_id
  96. * would be present in the returned array as a nested array with their respective
  97. * foreign keys also nested. The only fields that are not included are text
  98. * fields (which may be very large) or many-to-many foreign key relationships.
  99. * However, these fields and relationships can be expanded using the
  100. * tripal_core_expand_chado_vars.
  101. *
  102. * When a row from a chado table is selected using these two functions, it provides
  103. * a way for users who want to cutomize Drupal template files to access all data
  104. * associate with a specific record.
  105. *
  106. * Finally, the property tables in Chado generally follow the same format. Therefore
  107. * there is a set of functions for inserting, updating and deleting properties for
  108. * any table. This provides quick lookup of properties (provided the CV term is
  109. * known).
  110. *
  111. * @}
  112. *
  113. */
  114. /**
  115. * Provides a generic routine for inserting into any Chado table
  116. *
  117. * Use this function to insert a record into any Chado table. The first
  118. * argument specifies the table for inserting and the second is an array
  119. * of values to be inserted. The array is mutli-dimensional such that
  120. * foreign key lookup values can be specified.
  121. *
  122. * @param $table
  123. * The name of the chado table for inserting
  124. * @param $values
  125. * An associative array containing the values for inserting.
  126. * @param $options
  127. * An array of options such as:
  128. * - skip_validation: TRUE or FALSE. If TRUE will skip all the validation steps and
  129. * just try to insert as is. This is much faster but results in unhandled
  130. * non user-friendly errors if the insert fails.
  131. * - return_record: by default, the function will return the record but with
  132. * the primary keys added after insertion. To simply return TRUE on success
  133. * set this option to FALSE
  134. *
  135. * @return
  136. * On success this function returns the inserted record with the new primary keys
  137. * added to the returned array. On failure, it returns FALSE.
  138. *
  139. * Example usage:
  140. * @code
  141. * $values = array(
  142. * 'organism_id' => array(
  143. * 'genus' => 'Citrus',
  144. * 'species' => 'sinensis',
  145. * ),
  146. * 'name' => 'orange1.1g000034m.g',
  147. * 'uniquename' => 'orange1.1g000034m.g',
  148. * 'type_id' => array (
  149. * 'cv_id' => array (
  150. * 'name' => 'sequence',
  151. * ),
  152. * 'name' => 'gene',
  153. * 'is_obsolete' => 0
  154. * ),
  155. * );
  156. * $result = tripal_core_chado_insert('feature',$values);
  157. * @endcode
  158. * The above code inserts a record into the feature table. The $values array is
  159. * nested such that the organism is selected by way of the organism_id foreign
  160. * key constraint by specifying the genus and species. The cvterm is also
  161. * specified using its foreign key and the cv_id for the cvterm is nested as
  162. * well.
  163. *
  164. * @ingroup tripal_chado_api
  165. */
  166. function tripal_core_chado_insert($table, $values, $options = array()) {
  167. $print_errors = (isset($options['print_errors'])) ? $options['print_errors'] : FALSE;
  168. if (!is_array($values)) {
  169. tripal_core_report_error(
  170. 'tripal_core',
  171. TRIPAL_ERROR,
  172. 'Cannot pass non array as values for inserting.',
  173. array(),
  174. array('print' => $print_errors)
  175. );
  176. return FALSE;
  177. }
  178. if (count($values)==0) {
  179. tripal_core_report_error(
  180. 'tripal_core',
  181. TRIPAL_ERROR,
  182. 'Cannot pass an empty array as values for inserting.',
  183. array(),
  184. array('print' => $print_errors)
  185. );
  186. return FALSE;
  187. }
  188. // set defaults for options. If we don't set defaults then
  189. // we get memory leaks when we try to access the elements
  190. if (!is_array($options)) {
  191. $options = array();
  192. }
  193. if (!array_key_exists('skip_validation', $options)) {
  194. $options['skip_validation'] = FALSE;
  195. }
  196. if (!array_key_exists('return_record', $options)) {
  197. $options['return_record'] = TRUE;
  198. }
  199. $insert_values = array();
  200. if (array_key_exists('skip_validation', $options)) {
  201. $validate = !$options['skip_validation'];
  202. }
  203. else {
  204. $validate = TRUE;
  205. }
  206. // get the table description
  207. $table_desc = tripal_core_get_chado_table_schema($table);
  208. if (empty($table_desc)) {
  209. tripal_core_report_error('tripal_core', TRIPAL_WARNING,
  210. 'tripal_core_chado_insert; There is no table description for !table_name',
  211. array('!table_name' => $table), array('print' => $print_errors)
  212. );
  213. }
  214. // iterate through the values array and create a new 'insert_values' array
  215. // that has all the values needed for insert with all foreign relationsihps
  216. // resolved.
  217. foreach ($values as $field => $value) {
  218. // make sure the field is in the table description. If not then return an error
  219. // message
  220. if (!array_key_exists($field, $table_desc['fields'])) {
  221. tripal_core_report_error(
  222. 'tripal_core',
  223. TRIPAL_ERROR,
  224. "tripal_core_chado_insert; The field '%field' does not exist " .
  225. "for the table '%table'. Cannot perform insert. Values: %array",
  226. array('%field' => $field, '%table' => $table, '%array' => print_r($values, 1)),
  227. array('print' => $print_errors)
  228. );
  229. return FALSE;
  230. }
  231. if (is_array($value)) {
  232. // select the value from the foreign key relationship for this value
  233. $results = tripal_core_chado_get_foreign_key($table_desc, $field, $value);
  234. if (sizeof($results) > 1) {
  235. tripal_core_report_error(
  236. 'tripal_core',
  237. TRIPAL_ERROR,
  238. 'tripal_core_chado_insert: Too many records match the criteria supplied for !foreign_key foreign key constraint (!criteria)',
  239. array('!foreign_key' => $field, '!criteria' => print_r($value, TRUE)),
  240. array('print' => $print_errors)
  241. );
  242. }
  243. elseif (sizeof($results) < 1) {
  244. tripal_core_report_error(
  245. 'tripal_core',
  246. TRIPAL_DEBUG,
  247. 'tripal_core_chado_insert: no record matches criteria supplied for !foreign_key foreign key constraint (!criteria)',
  248. array('!foreign_key' => $field, '!criteria' => print_r($value, TRUE)),
  249. array('print' => $print_errors)
  250. );
  251. }
  252. else {
  253. $insert_values[$field] = $results[0];
  254. }
  255. }
  256. else {
  257. $insert_values[$field] = $value;
  258. }
  259. }
  260. if ($validate) {
  261. // check for violation of any unique constraints
  262. $ukeys = array();
  263. if (array_key_exists('unique keys', $table_desc)) {
  264. $ukeys = $table_desc['unique keys'];
  265. }
  266. $ukselect_cols = array();
  267. $ukselect_vals = array();
  268. if ($ukeys) {
  269. foreach ($ukeys as $name => $fields) {
  270. foreach ($fields as $index => $field) {
  271. // build the arrays for performing a select that will check the contraint
  272. $ukselect_cols[] = $field;
  273. if (!array_key_exists($field, $insert_values)) {
  274. if (array_key_exists('default', $table_desc['fields'][$field])) {
  275. $ukselect_vals[$field] = $table_desc['fields'][$field]['default'];
  276. }
  277. }
  278. else {
  279. $ukselect_vals[$field] = $insert_values[$field];
  280. }
  281. }
  282. // now check the constraint
  283. if (tripal_core_chado_select($table, $ukselect_cols, $ukselect_vals)) {
  284. tripal_core_report_error(
  285. 'tripal_core',
  286. TRIPAL_ERROR,
  287. "tripal_core_chado_insert; Cannot insert duplicate record into $table table: !values",
  288. array('!values' => print_r($values, TRUE)),
  289. array('print' => $print_errors)
  290. );
  291. return FALSE;
  292. }
  293. }
  294. }
  295. // if trying to insert a field that is the primary key, make sure it also is unique
  296. if (array_key_exists('primary key', $table_desc)) {
  297. $pkey = $table_desc['primary key'][0];
  298. if (array_key_exists($pkey, $insert_values)) {
  299. $coptions = array('statement_name' => 'pqsel_' . $table . '_' . $pkey);
  300. if (tripal_core_chado_select($table, array($pkey), array($pkey => $insert_values[$pkey]), $coptions)) {
  301. tripal_core_report_error(
  302. 'tripal_core',
  303. TRIPAL_ERROR,
  304. 'tripal_core_chado_insert; Cannot insert duplicate primary key into !table table: !values',
  305. array('!table' => $table, '!values' => print_r($values, TRUE)),
  306. array('print' => $print_errors)
  307. );
  308. return FALSE;
  309. }
  310. }
  311. }
  312. // make sure required fields have a value
  313. if (!is_array($table_desc['fields'])) {
  314. $table_desc['fields'] = array();
  315. tripal_core_report_error(
  316. 'tripal_core',
  317. TRIPAL_WARNING,
  318. "tripal_core_chado_insert; %table missing fields: \n %schema",
  319. array('%table' => $table, '%schema' => print_r($table_desc, 1)),
  320. array('print' => $print_errors)
  321. );
  322. }
  323. foreach ($table_desc['fields'] as $field => $def) {
  324. // a field is considered missing if it cannot be NULL and there is no default
  325. // value for it or it is of type 'serial'
  326. if (array_key_exists('NOT NULL', $def) and
  327. !array_key_exists($field, $insert_values) and
  328. !array_key_exists('default', $def) and
  329. strcmp($def['type'], serial) != 0) {
  330. tripal_core_report_error(
  331. 'tripal_core',
  332. TRIPAL_ERROR,
  333. "tripal_core_chado_insert; Field %table.%field cannot be NULL: %values",
  334. array('%table' => $table, '%field' => $field, '%values' => print_r($values, 1)),
  335. array('print' => $print_errors)
  336. );
  337. return FALSE;
  338. }
  339. }
  340. } //end of validation
  341. // Now build the insert SQL statement
  342. $ifields = array(); // contains the names of the fields
  343. $itypes = array(); // contains placeholders for the sql query
  344. $ivalues = array(); // contains the values of the fields
  345. $i = 1;
  346. foreach ($insert_values as $field => $value) {
  347. $ifields[] = $field;
  348. $ivalues[":$field"] = $value;
  349. $i++;
  350. if (strcmp($value, '__NULL__')==0) {
  351. $itypes[] = "NULL";
  352. }
  353. else {
  354. $itypes[] = ":$field";
  355. }
  356. }
  357. // create the SQL
  358. $sql = 'INSERT INTO {' . $table . '} (' . implode(", ", $ifields) . ") VALUES (" . implode(", ", $itypes) . ")";
  359. $result = chado_query($sql, $ivalues);
  360. // if we have a result then add primary keys to return array
  361. if ($options['return_record'] == TRUE and $result) {
  362. if (array_key_exists('primary key', $table_desc) and is_array($table_desc['primary key'])) {
  363. foreach ($table_desc['primary key'] as $field) {
  364. $sql = "SELECT CURRVAL('{" . $table . "_" . $field . "_seq}')";
  365. $results = chado_query($sql);
  366. $value = $results->fetchField();
  367. if (!$value) {
  368. tripal_core_report_error(
  369. 'tripal_core',
  370. TRIPAL_ERROR,
  371. "tripal_core_chado_insert; not able to retrieve primary key after insert: %sql",
  372. array('%sql' => $sql),
  373. array('print' => $print_errors)
  374. );
  375. return FALSE;
  376. }
  377. $values[$field] = $value;
  378. }
  379. }
  380. return $values;
  381. }
  382. elseif ($options['return_record'] == FALSE and $result) {
  383. return TRUE;
  384. }
  385. else {
  386. tripal_core_report_error(
  387. 'tripal_core',
  388. TRIPAL_ERROR,
  389. 'tripal_core_chado_insert; Cannot insert record into "%table": %values',
  390. array('%table' => $table, '%values' => print_r($values, 1)),
  391. array('print' => $print_errors)
  392. );
  393. return FALSE;
  394. }
  395. return FALSE;
  396. }
  397. /**
  398. * Provides a generic routine for updating into any Chado table
  399. *
  400. * Use this function to update a record in any Chado table. The first
  401. * argument specifies the table for inserting, the second is an array
  402. * of values to matched for locating the record for updating, and the third
  403. * argument give the values to update. The arrays are mutli-dimensional such
  404. * that foreign key lookup values can be specified.
  405. *
  406. * @param $table
  407. * The name of the chado table for inserting
  408. * @param $match
  409. * An associative array containing the values for locating a record to update.
  410. * @param $values
  411. * An associative array containing the values for updating.
  412. * @param $options
  413. * An array of options such as:
  414. * - return_record: by default, the function will return the TRUE if the record
  415. * was succesfully updated. However, set this option to TRUE to return the
  416. * record that was updated. The returned record will have the fields provided
  417. * but the primary key (if available for the table) will be added to the record.
  418. * @return
  419. * On success this function returns TRUE. On failure, it returns FALSE.
  420. *
  421. * Example usage:
  422. * @code
  423. $umatch = array(
  424. 'organism_id' => array(
  425. 'genus' => 'Citrus',
  426. 'species' => 'sinensis',
  427. ),
  428. 'uniquename' => 'orange1.1g000034m.g7',
  429. 'type_id' => array (
  430. 'cv_id' => array (
  431. 'name' => 'sequence',
  432. ),
  433. 'name' => 'gene',
  434. 'is_obsolete' => 0
  435. ),
  436. );
  437. $uvalues = array(
  438. 'name' => 'orange1.1g000034m.g',
  439. 'type_id' => array (
  440. 'cv_id' => array (
  441. 'name' => 'sequence',
  442. ),
  443. 'name' => 'mRNA',
  444. 'is_obsolete' => 0
  445. ),
  446. );
  447. * $result = tripal_core_chado_update('feature',$umatch,$uvalues);
  448. * @endcode
  449. * The above code species that a feature with a given uniquename, organism_id,
  450. * and type_id (the unique constraint for the feature table) will be updated.
  451. * The organism_id is specified as a nested array that uses the organism_id
  452. * foreign key constraint to lookup the specified values to find the exact
  453. * organism_id. The same nested struture is also used for specifying the
  454. * values to update. The function will find the record that matches the
  455. * columns specified and update the record with the avlues in the $uvalues array.
  456. *
  457. * @ingroup tripal_chado_api
  458. */
  459. function tripal_core_chado_update($table, $match, $values, $options = NULL) {
  460. $print_errors = (isset($options['print_errors'])) ? $options['print_errors'] : FALSE;
  461. if (!is_array($values)) {
  462. tripal_core_report_error(
  463. 'tripal_core',
  464. TRIPAL_ERROR,
  465. 'Cannot pass non array as values for updating.',
  466. array(),
  467. array('print' => $print_errors)
  468. );
  469. return FALSE;
  470. }
  471. if (count($values)==0) {
  472. tripal_core_report_error(
  473. 'tripal_core',
  474. TRIPAL_ERROR,
  475. 'Cannot pass an empty array as values for updating.',
  476. array(),
  477. array('print' => $print_errors)
  478. );
  479. return FALSE;
  480. }
  481. if (!is_array($match)) {
  482. tripal_core_report_error(
  483. 'tripal_core',
  484. TRIPAL_ERROR,
  485. 'Cannot pass non array as values for matching.',
  486. array(),
  487. array('print' => $print_errors)
  488. );
  489. return FALSE;
  490. }
  491. if (count($match)==0) {
  492. tripal_core_report_error(
  493. 'tripal_core',
  494. TRIPAL_ERROR,
  495. 'Cannot pass an empty array as values for matching.',
  496. array(),
  497. array('print' => $print_errors)
  498. );
  499. return FALSE;
  500. }
  501. // set defaults for options. If we don't set defaults then
  502. // we get memory leaks when we try to access the elements
  503. if (!is_array($options)) {
  504. $options = array();
  505. }
  506. if (!array_key_exists('return_record', $options)) {
  507. $options['return_record'] = FALSE;
  508. }
  509. $update_values = array(); // contains the values to be updated
  510. $update_matches = array(); // contains the values for the where clause
  511. // get the table description
  512. $table_desc = tripal_core_get_chado_table_schema($table);
  513. // if the user wants us to return the record then we need to get the
  514. // unique primary key if one exists. That way we can add it to the
  515. // values that get returned at the end of the function
  516. $pkeys = array();
  517. if ($options['return_record'] == TRUE) {
  518. if (array_key_exists('primary key', $table_desc) and is_array($table_desc['primary key'])) {
  519. $columns = array();
  520. $stmt_suffix = '';
  521. foreach ($table_desc['primary key'] as $field) {
  522. $columns[] = $field;
  523. $stmt_suffix .= substr($field, 0, 2);
  524. }
  525. $options2 = array('statement_name' => 'sel_' . $table . '_' . $stmt_suffix);
  526. $results = tripal_core_chado_select($table, $columns, $match, $options2);
  527. if (count($results) > 0) {
  528. foreach ($results as $index => $pkey) {
  529. $pkeys[] = $pkey;
  530. }
  531. }
  532. }
  533. }
  534. // get the values needed for matching in the SQL statement
  535. foreach ($match as $field => $value) {
  536. if (is_array($value)) {
  537. $results = tripal_core_chado_get_foreign_key($table_desc, $field, $value);
  538. if (sizeof($results) > 1) {
  539. tripal_core_report_error(
  540. 'tripal_core',
  541. TRIPAL_ERROR,
  542. 'tripal_core_chado_update: When trying to find record to update, too many records match the criteria supplied for !foreign_key foreign key constraint (!criteria)',
  543. array('!foreign_key' => $field, '!criteria' => print_r($value, TRUE)),
  544. array('print' => $print_errors)
  545. );
  546. }
  547. elseif (sizeof($results) < 1) {
  548. tripal_core_report_error(
  549. 'tripal_core',
  550. TRIPAL_DEBUG,
  551. 'tripal_core_chado_update: When trying to find record to update, no record matches criteria supplied for !foreign_key foreign key constraint (!criteria)',
  552. array('!foreign_key' => $field, '!criteria' => print_r($value, TRUE)),
  553. array('print' => $print_errors)
  554. );
  555. }
  556. else {
  557. $update_matches[$field] = $results[0];
  558. }
  559. }
  560. else {
  561. $update_matches[$field] = $value;
  562. }
  563. }
  564. // get the values used for updating
  565. foreach ($values as $field => $value) {
  566. if (is_array($value)) {
  567. $foreign_options = array();
  568. // select the value from the foreign key relationship for this value
  569. $results = tripal_core_chado_get_foreign_key($table_desc, $field, $value, $foreign_options);
  570. if (sizeof($results) > 1) {
  571. tripal_core_report_error(
  572. 'tripal_core',
  573. TRIPAL_ERROR,
  574. 'tripal_core_chado_update: When trying to find update values, too many records match the criteria supplied for !foreign_key foreign key constraint (!criteria)',
  575. array('!foreign_key' => $field, '!criteria' => print_r($value, TRUE)),
  576. array('print' => $print_errors)
  577. );
  578. }
  579. elseif (sizeof($results) < 1) {
  580. tripal_core_report_error(
  581. 'tripal_core',
  582. TRIPAL_DEBUG,
  583. 'tripal_core_chado_update: When trying to find update values, no record matches criteria supplied for !foreign_key foreign key constraint (!criteria)',
  584. array('!foreign_key' => $field, '!criteria' => print_r($value,TRUE)),
  585. array('print' => $print_errors)
  586. );
  587. }
  588. else {
  589. $update_values[$field] = $results[0];
  590. }
  591. }
  592. else {
  593. $update_values[$field] = $value;
  594. }
  595. }
  596. // now build the SQL statement
  597. $sql = 'UPDATE {' . $table . '} SET ';
  598. $args = array(); // arguments passed to chado_query
  599. foreach ($update_values as $field => $value) {
  600. if (strcmp($value, '__NULL__') == 0) {
  601. $sql .= " $field = NULL, ";
  602. }
  603. else {
  604. $sql .= " $field = :$field, ";
  605. $args[":$field"] = $value;
  606. }
  607. }
  608. $sql = drupal_substr($sql, 0, -2); // get rid of the trailing comma & space
  609. $sql .= " WHERE ";
  610. foreach ($update_matches as $field => $value) {
  611. if (strcmp($value, '__NULL__')==0) {
  612. $sql .= " $field = NULL AND ";
  613. }
  614. else {
  615. $sql .= " $field = :$field AND ";
  616. $args[":$field"] = $value;
  617. }
  618. }
  619. $sql = drupal_substr($sql, 0, -4); // get rid of the trailing 'AND'
  620. $result = chado_query($sql, $args);
  621. // if we have a result then add primary keys to return array
  622. if ($options['return_record'] == TRUE and $result) {
  623. // only if we have a single result do we want to add the primary keys to the values
  624. // array. If the update matched many records we can't add the pkeys
  625. if (count($pkeys) == 1) {
  626. foreach ($pkeys as $index => $pkey) {
  627. foreach ($pkey as $field => $fvalue) {
  628. $values[$field] = $fvalue;
  629. }
  630. }
  631. }
  632. return $values;
  633. }
  634. elseif ($options['return_record'] == FALSE and $result) {
  635. return TRUE;
  636. }
  637. else {
  638. tripal_core_report_error(
  639. 'tripal_core',
  640. TRIPAL_ERROR,
  641. "tripal_core_chado_update: Cannot update record in %table table. \nMatch: %match \nValues: %values",
  642. array('%table' => table, '%match' => print_r($match,TRUE), '%values' => print_r($values, 1)),
  643. array('print' => $print_errors)
  644. );
  645. return FALSE;
  646. }
  647. return FALSE;
  648. }
  649. /**
  650. * Provides a generic function for deleting a record(s) from any chado table
  651. *
  652. * Use this function to delete a record(s) in any Chado table. The first
  653. * argument specifies the table to delete from and the second is an array
  654. * of values to match for locating the record(s) to be deleted. The arrays
  655. * are mutli-dimensional such that foreign key lookup values can be specified.
  656. *
  657. * @param $table
  658. * The name of the chado table for inserting
  659. * @param $match
  660. * An associative array containing the values for locating a record to update.
  661. * @param $options
  662. * Currently there are no options
  663. * @return
  664. * On success this function returns TRUE. On failure, it returns FALSE.
  665. *
  666. * Example usage:
  667. * @code
  668. $umatch = array(
  669. 'organism_id' => array(
  670. 'genus' => 'Citrus',
  671. 'species' => 'sinensis',
  672. ),
  673. 'uniquename' => 'orange1.1g000034m.g7',
  674. 'type_id' => array (
  675. 'cv_id' => array (
  676. 'name' => 'sequence',
  677. ),
  678. 'name' => 'gene',
  679. 'is_obsolete' => 0
  680. ),
  681. );
  682. $uvalues = array(
  683. 'name' => 'orange1.1g000034m.g',
  684. 'type_id' => array (
  685. 'cv_id' => array (
  686. 'name' => 'sequence',
  687. ),
  688. 'name' => 'mRNA',
  689. 'is_obsolete' => 0
  690. ),
  691. );
  692. * $result = tripal_core_chado_update('feature', $umatch, $uvalues);
  693. * @endcode
  694. * The above code species that a feature with a given uniquename, organism_id,
  695. * and type_id (the unique constraint for the feature table) will be deleted.
  696. * The organism_id is specified as a nested array that uses the organism_id
  697. * foreign key constraint to lookup the specified values to find the exact
  698. * organism_id. The same nested struture is also used for specifying the
  699. * values to update. The function will find all records that match the
  700. * columns specified and delete them.
  701. *
  702. * @ingroup tripal_chado_api
  703. */
  704. function tripal_core_chado_delete($table, $match, $options = NULL) {
  705. if (!is_array($match)) {
  706. watchdog('tripal_core', 'Cannot pass non array as values for matching.', array(),
  707. WATCHDOG_ERROR);
  708. return FALSE;
  709. }
  710. if (count($match)==0) {
  711. watchdog('tripal_core', 'Cannot pass an empty array as values for matching.', array(),
  712. WATCHDOG_ERROR);
  713. return FALSE;
  714. }
  715. // set defaults for options. If we don't set defaults then
  716. // we get memory leaks when we try to access the elements
  717. if (!is_array($options)) {
  718. $options = array();
  719. }
  720. $delete_matches = array(); // contains the values for the where clause
  721. // get the table description
  722. $table_desc = tripal_core_get_chado_table_schema($table);
  723. $fields = $table_desc['fields'];
  724. // get the values needed for matching in the SQL statement
  725. foreach ($match as $field => $value) {
  726. if (is_array($value)) {
  727. // if the user has specified an array of values to delete rather than
  728. // FK relationships the keep those in our match
  729. if (array_values($value) === $value) {
  730. $delete_matches[$field] = $value;
  731. }
  732. else {
  733. $results = tripal_core_chado_get_foreign_key($table_desc, $field, $value);
  734. if (sizeof($results) > 1) {
  735. watchdog('tripal_core', 'tripal_core_chado_delete: When trying to find record to delete, too many records match the criteria supplied for !foreign_key foreign key constraint (!criteria)', array('!foreign_key' => $field, '!criteria' => print_r($value, TRUE)), WATCHDOG_ERROR);
  736. }
  737. elseif (sizeof($results) < 1) {
  738. //watchdog('tripal_core', 'tripal_core_chado_delete: 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)), WATCHDOG_ERROR);
  739. }
  740. else {
  741. $delete_matches[$field] = $results[0];
  742. }
  743. }
  744. }
  745. else {
  746. $delete_matches[$field] = $value;
  747. }
  748. }
  749. // now build the SQL statement
  750. $sql = 'DELETE FROM {' . $table . '} WHERE ';
  751. $args = array();
  752. foreach ($delete_matches as $field => $value) {
  753. // if we have an array values then this is an "IN" clasue.
  754. // we cannot use prepared statements with these
  755. if (count($value) > 1) {
  756. $sql .= "$field IN (";
  757. $index = 0;
  758. foreach ($value as $v) {
  759. $sql .= ":$field" . $index . ", ";
  760. $args[":$field" . $index] = $v;
  761. $index++;
  762. }
  763. $sql = drupal_substr($sql, 0, -2); // get rid of trailing ', '
  764. $sql .= ") AND ";
  765. }
  766. else {
  767. if (strcmp($value, '__NULL__') == 0) {
  768. $sql .= " $field = NULL AND ";
  769. }
  770. else {
  771. $sql .= " $field = :$field AND ";
  772. $args[":$field"] = $value;
  773. }
  774. }
  775. }
  776. $sql = drupal_substr($sql, 0, -4); // get rid of the trailing 'AND'
  777. // finally perform the delete. If successful, return the updated record
  778. $result = chado_query($sql, $args);
  779. if ($result) {
  780. return TRUE;
  781. }
  782. else {
  783. watchdog('tripal_core', "Cannot delete record in $table table. Match:" . print_r($match, 1) . ". Values: " . print_r($values, 1), array(), 'WATCHDOG_ERROR');
  784. return FALSE;
  785. }
  786. return FALSE;
  787. }
  788. /**
  789. * Provides a generic routine for selecting data from a Chado table
  790. *
  791. * Use this function to perform a simple select from any Chado table.
  792. *
  793. * @param $table
  794. * The name of the chado table for inserting
  795. * @param $columns
  796. * An array of column names
  797. * @param $values
  798. * An associative array containing the values for filtering the results. In the
  799. * case where multiple values for the same time are to be selected an additional
  800. * entry for the field should appear for each value
  801. * @param $options
  802. * An associative array of additional options where the key is the option
  803. * and the value is the value of that option.
  804. *
  805. * Additional Options Include:
  806. * - has_record
  807. * Set this argument to 'TRUE' to have this function return a numeric
  808. * value for the number of recrods rather than the array of records. this
  809. * can be useful in 'if' statements to check the presence of particula records.
  810. * - return_sql
  811. * Set this to 'TRUE' to have this function return an array where the first
  812. * element is the sql that would have been run and the second is an array of
  813. * arguments.
  814. * - case_insensitive_columns
  815. * An array of columns to do a case insensitive search on.
  816. * - regex_columns
  817. * An array of columns where the value passed in should be treated as a regular expression
  818. * - order_by
  819. * An associative array containing the column names of the table as keys
  820. * and the type of sort (i.e. ASC, DESC) as the values. The results in the
  821. * query will be sorted by the key values in the direction listed by the value
  822. * - statement_name: the name of the prepared statement to use. If the statement
  823. * has not yet been prepared it will be prepared automatically. On subsequent
  824. * calls with the same statement_name only an execute on the previously
  825. * prepared statement will occur.
  826. * - is_prepared: TRUE or FALSE. Whether or not the statement is prepared. By
  827. * default if the statement is not prepared it will be automatically.
  828. * However to avoid this check, which requires a database query you can
  829. * set this value to true and the check will not be performed.
  830. * - is_duplicate: TRUE or FALSE. Checks the values submited to see if
  831. * they violate any of the unique constraints. If so, the record
  832. * is returned, if not, FALSE is returned.
  833. * - pager: Use this option if it is desired to return only a subset of results
  834. * so that they may be shown with in a Drupal-style pager. This should be
  835. * an array with two keys: 'limit' and 'element'. The value of 'limit'
  836. * should specify the number of records to return and 'element' is a
  837. * unique integer to differentiate between pagers when more than one
  838. * appear on a page. The 'element' should start with zero and increment by
  839. * one for each pager. The pager currently does not work with prepared queries
  840. * (when using the -statement_name option).
  841. *
  842. * @return
  843. * An array of results, FALSE if the query was not executed
  844. * correctly, an empty array if no records were matched, or the number of records
  845. * in the dataset if $has_record is set.
  846. * If the option 'is_duplicate' is provided and the record is a duplicate it
  847. * will return the duplicated record. If the 'has_record' option is provided
  848. * a value of TRUE will be returned if a record exists and FALSE will bee
  849. * returned if there are not records.
  850. *
  851. * Example usage:
  852. * @code
  853. * $columns = array('feature_id', 'name');
  854. * $values = array(
  855. * 'organism_id' => array(
  856. * 'genus' => 'Citrus',
  857. * 'species' => array('sinensis', 'clementina'),
  858. * ),
  859. * 'uniquename' => 'orange1.1g000034m.g',
  860. * 'type_id' => array (
  861. * 'cv_id' => array (
  862. * 'name' => 'sequence',
  863. * ),
  864. * 'name' => 'gene',
  865. * 'is_obsolete' => 0
  866. * ),
  867. * );
  868. * $options = array(
  869. * 'statement_name' => 'sel_feature_genus_species_cvname'
  870. * 'order_by' => array(
  871. * 'name' => 'ASC'
  872. * ),
  873. * );
  874. * $result = tripal_core_chado_select('feature',$columns,$values,$options);
  875. * @endcode
  876. * The above code selects a record from the feature table using the three fields
  877. * that uniquely identify a feature. The $columns array simply lists the columns
  878. * to select. The $values array is nested such that the organism is identified by
  879. * way of the organism_id foreign key constraint by specifying the genus and
  880. * species. The cvterm is also specified using its foreign key and the cv_id
  881. * for the cvterm is nested as well. In the example above, two different species
  882. * are allowed to match
  883. *
  884. * @ingroup tripal_chado_api
  885. */
  886. function tripal_core_chado_select($table, $columns, $values, $options = NULL) {
  887. $print_errors = (isset($options['print_errors'])) ? $options['print_errors'] : FALSE;
  888. if (!is_array($values)) {
  889. tripal_core_report_error(
  890. 'tripal_core',
  891. TRIPAL_ERROR,
  892. 'Cannot pass non array as values for selecting.',
  893. array(),
  894. array('print' => $print_errors)
  895. );
  896. return FALSE;
  897. }
  898. if (!is_array($columns)) {
  899. tripal_core_report_error(
  900. 'tripal_core',
  901. TRIPAL_ERROR,
  902. 'Cannot pass non array as columns for selecting.',
  903. array(),
  904. array('print' => $print_errors)
  905. );
  906. return FALSE;
  907. }
  908. if (count($columns)==0) {
  909. tripal_core_report_error(
  910. 'tripal_core',
  911. TRIPAL_ERROR,
  912. 'Cannot pass an empty array as columns for selecting.',
  913. array(),
  914. array('print' => $print_errors)
  915. );
  916. return FALSE;
  917. }
  918. // set defaults for options. If we don't set defaults then
  919. // we get memory leaks when we try to access the elements
  920. if (!is_array($options)) {
  921. $options = array();
  922. }
  923. if (!array_key_exists('case_insensitive_columns', $options)) {
  924. $options['case_insensitive_columns'] = array();
  925. }
  926. if (!array_key_exists('regex_columns', $options)) {
  927. $options['regex_columns'] = array();
  928. }
  929. if (!array_key_exists('order_by', $options)) {
  930. $options['order_by'] = array();
  931. }
  932. if (!array_key_exists('return_sql', $options)) {
  933. $options['return_sql'] = FALSE;
  934. }
  935. if (!array_key_exists('has_record', $options)) {
  936. $options['has_record'] = FALSE;
  937. }
  938. if (!array_key_exists('is_duplicate', $options)) {
  939. $options['is_duplicate'] = FALSE;
  940. }
  941. $pager = array();
  942. if (array_key_exists('pager', $options)) {
  943. $pager = $options['pager'];
  944. }
  945. // check that our columns and values arguments are proper arrays
  946. if (!is_array($columns)) {
  947. tripal_core_report_error(
  948. 'tripal_core',
  949. TRIPAL_ERROR,
  950. 'tripal_core_chado_select; the $columns argument must be an array. Columns:%columns',
  951. array('%columns' => print_r($columns, TRUE)),
  952. array('print' => $print_errors)
  953. );
  954. return FALSE;
  955. }
  956. if (!is_array($values)) {
  957. tripal_core_report_error(
  958. 'tripal_core',
  959. TRIPAL_ERROR,
  960. 'tripal_core_chado_select; the $values argument must be an array. Values:%values',
  961. array('%values' => print_r($values, TRUE)),
  962. array('print' => $print_errors)
  963. );
  964. return FALSE;
  965. }
  966. // get the table description
  967. $table_desc = tripal_core_get_chado_table_schema($table);
  968. $select = '';
  969. $from = '';
  970. $where = array();
  971. $args = array();
  972. // if the 'use_unique' option is turned on then we want
  973. // to remove all but unique keys
  974. if ($options['is_duplicate'] and array_key_exists('unique keys', $table_desc)) {
  975. $ukeys = $table_desc['unique keys'];
  976. $has_results = 0;
  977. // iterate through the unique constraints and reset the values and columns
  978. // arrays to only include these fields
  979. foreach ($ukeys as $cname => $fields) {
  980. if ($has_results) {
  981. continue;
  982. }
  983. $new_values = array();
  984. $new_columns = array();
  985. $new_options = array();
  986. $uq_sname = "uq_" . $table . "_";
  987. $has_pkey = 0;
  988. // include the primary key in the results returned
  989. if (array_key_exists('primary key', $table_desc)) {
  990. $has_pkey = 1;
  991. $pkeys = $table_desc['primary key'];
  992. foreach ($pkeys as $index => $key) {
  993. array_push($new_columns, $key);
  994. }
  995. }
  996. // recreate the $values and $columns arrays
  997. foreach ($fields as $field) {
  998. if (array_key_exists($field, $values)) {
  999. $new_values[$field] = $values[$field];
  1000. $uq_sname .= substr($field, 0, 2);
  1001. // if there is no primary key then use the unique contraint fields
  1002. if (!$has_pkey) {
  1003. array_push($new_columns, $field);
  1004. }
  1005. }
  1006. // if the field doesn't exist in the values array then
  1007. // substitute any default values
  1008. elseif (array_key_exists('default', $table_desc['fields'][$field])) {
  1009. $new_values[$field] = $table_desc['fields'][$field]['default'];
  1010. $uq_sname .= substr($field, 0, 2);
  1011. if (!$has_pkey) {
  1012. array_push($new_columns, $field);
  1013. }
  1014. }
  1015. // if there is no value (default or otherwise) check if this field is
  1016. // allowed to be null
  1017. elseif (!$table_desc['fields'][$field]['not null']) {
  1018. $new_values[$field] = NULL;
  1019. $uq_sname .= "n" . substr($field, 0, 2);
  1020. if (!$has_pkey) {
  1021. array_push($new_columns, $field);
  1022. }
  1023. }
  1024. // if the array key doesn't exist in the values given by the caller
  1025. // and there is no default value then we cannot check if the record
  1026. // is a duplicate so return FALSE
  1027. else {
  1028. tripal_core_report_error(
  1029. 'tripal_core',
  1030. TRIPAL_ERROR,
  1031. 'tripal_core_chado_select: There is no value for %field thus we cannot check if this record is unique',
  1032. array('%field' => $field),
  1033. array('print' => $print_errors)
  1034. );
  1035. return FALSE;
  1036. }
  1037. }
  1038. $new_options['statement_name'] = $uq_sname;
  1039. $results = tripal_core_chado_select($table, $new_columns, $new_values, $new_options);
  1040. // if we have a duplicate record then return the results
  1041. if (count($results) > 0) {
  1042. $has_results = 1;
  1043. }
  1044. unset($new_columns);
  1045. unset($new_values);
  1046. unset($new_options);
  1047. }
  1048. if ($options['has_record'] and $has_results) {
  1049. return TRUE;
  1050. }
  1051. else {
  1052. return $results;
  1053. }
  1054. }
  1055. foreach ($values as $field => $value) {
  1056. // make sure the field is in the table description. If not then return an error
  1057. // message
  1058. if (!array_key_exists($field, $table_desc['fields'])) {
  1059. tripal_core_report_error('tripal_core', TRIPAL_ERROR,
  1060. 'tripal_core_chado_select: The field "%field" does not exist for the table "%table". Cannot perform query. Values: %array',
  1061. array('%field' => $field, '%table' => $table, '%array' => print_r($values, 1)),
  1062. array('print' => $print_errors)
  1063. );
  1064. return array();
  1065. }
  1066. $select[] = $field;
  1067. if (is_array($value)) {
  1068. // if the user has specified multiple values for matching then this we
  1069. // want to catch that and save them in our $where array, otherwise
  1070. // we'll descend for a foreign key relationship
  1071. if (array_values($value) === $value) {
  1072. $where[$field] = $value;
  1073. }
  1074. else {
  1075. // select the value from the foreign key relationship for this value
  1076. $foreign_options = array(
  1077. 'regex_columns' => $options['regex_columns'],
  1078. );
  1079. $results = tripal_core_chado_get_foreign_key($table_desc, $field, $value, $foreign_options);
  1080. if (!$results or count($results)==0) {
  1081. return array();
  1082. }
  1083. else {
  1084. $where[$field] = $results;
  1085. }
  1086. }
  1087. }
  1088. else {
  1089. // need to catch a 0 and make int if integer field
  1090. // but we don't want to catch a NULL
  1091. if ($value === NULL) {
  1092. $where[$field] = NULL;
  1093. }
  1094. elseif ($table_desc['fields'][$field]['type'] == 'int') {
  1095. $where[$field][] = (int) $value;
  1096. }
  1097. else {
  1098. $where[$field][] = $value;
  1099. }
  1100. }
  1101. }
  1102. // now build the SQL and prepared SQL statements. We may not use
  1103. // the prepared statement if it wasn't requested in the options or if the
  1104. // argument in a where statement has multiple values.
  1105. if (empty($where)) {
  1106. // sometimes want to select everything
  1107. $sql = "SELECT " . implode(', ', $columns) . " ";
  1108. $sql .= 'FROM {' . $table . '} ';
  1109. // we don't prepare a statement if there is no where clause
  1110. $prepared = FALSE;
  1111. }
  1112. else {
  1113. $sql = "SELECT " . implode(', ', $columns) . " ";
  1114. $sql .= 'FROM {' . $table . '} ';
  1115. // if $values is empty then we want all results so no where clause
  1116. if (!empty($values)) {
  1117. $sql .= "WHERE ";
  1118. }
  1119. foreach ($where as $field => $value) {
  1120. // if we have multiple values returned then we need an 'IN' statement
  1121. // in our where statement
  1122. if (count($value) > 1) {
  1123. $sql .= "$field IN (";
  1124. $index = 0;
  1125. foreach ($value as $v) {
  1126. $sql .= ":$field" . $index . ', ';
  1127. $args[":$field" . $index] = $v;
  1128. $index++;
  1129. }
  1130. $sql = drupal_substr($sql, 0, -2); // remove trailing ', '
  1131. $sql .= ") AND ";
  1132. }
  1133. // if we have a null value then we need an IS NULL in our where statement
  1134. elseif ($value === NULL) {
  1135. $sql .= "$field IS NULL AND ";
  1136. // Need to remove one from the argument count b/c nulls don't add an argument
  1137. }
  1138. // if we have a single value then we need an = in our where statement
  1139. else {
  1140. $operator = '=';
  1141. if (in_array($field, $options['regex_columns'])) {
  1142. $operator = '~*';
  1143. }
  1144. if (in_array($field, $options['case_insensitive_columns'])) {
  1145. $sql .= "lower($field) $operator lower(:$field) AND ";
  1146. $args[":$field"] = $value[0];
  1147. }
  1148. else {
  1149. $sql .= "$field $operator :$field AND ";
  1150. $args[":$field"] = $value[0];
  1151. }
  1152. }
  1153. } // end foreach item in where clause
  1154. $sql = drupal_substr($sql, 0, -4); // get rid of the trailing 'AND '
  1155. } // end if (empty($where)){ } else {
  1156. // finally add any ordering of the results to the SQL statement
  1157. if (count($options['order_by']) > 0) {
  1158. $sql .= " ORDER BY ";
  1159. foreach ($options['order_by'] as $field => $dir) {
  1160. $sql .= "$field $dir, ";
  1161. }
  1162. $sql = drupal_substr($sql, 0, -2); // get rid of the trailing ', '
  1163. }
  1164. // if the caller has requested the SQL rather than the results...
  1165. // which happens in the case of wanting to use the Drupal pager, then do so
  1166. if ($options['return_sql'] == TRUE) {
  1167. return array('sql' => $sql, 'args' => $args);
  1168. }
  1169. if (array_key_exists('limit', $pager)) {
  1170. $resource = chado_pager_query($sql, $pager['limit'], $pager['element'], NULL, $args);
  1171. }
  1172. else {
  1173. $resource = chado_query($sql, $args);
  1174. }
  1175. // format results into an array
  1176. $results = array();
  1177. foreach ($resource as $r) {
  1178. $results[] = $r;
  1179. }
  1180. if ($options['has_record']) {
  1181. return count($results);
  1182. }
  1183. return $results;
  1184. }
  1185. /**
  1186. * Gets the value of a foreign key relationship
  1187. *
  1188. * This function is used by tripal_core_chado_select, tripal_core_chado_insert,
  1189. * and tripal_core_chado_update to iterate through the associate array of
  1190. * values that gets passed to each of those routines. The values array
  1191. * is nested where foreign key contraints are used to specify a value that. See
  1192. * documentation for any of those functions for further information.
  1193. *
  1194. * @param $table_desc
  1195. * A table description for the table with the foreign key relationship to be identified generated by
  1196. * hook_chado_<table name>_schema()
  1197. * @param $field
  1198. * The field in the table that is the foreign key.
  1199. * @param $values
  1200. * An associative array containing the values
  1201. * @param $options
  1202. * An associative array of additional options where the key is the option
  1203. * and the value is the value of that option. These options are passed on to tripal_core_chado_select.
  1204. *
  1205. * Additional Options Include:
  1206. * - case_insensitive_columns
  1207. * An array of columns to do a case insensitive search on.
  1208. * - regex_columns
  1209. * An array of columns where the value passed in should be treated as a regular expression
  1210. *
  1211. * @return
  1212. * A string containg the results of the foreign key lookup, or FALSE if failed.
  1213. *
  1214. * Example usage:
  1215. * @code
  1216. *
  1217. * $values = array(
  1218. * 'genus' => 'Citrus',
  1219. * 'species' => 'sinensis',
  1220. * );
  1221. * $value = tripal_core_chado_get_foreign_key('feature', 'organism_id',$values);
  1222. *
  1223. * @endcode
  1224. * The above code selects a record from the feature table using the three fields
  1225. * that uniquely identify a feature. The $columns array simply lists the columns
  1226. * to select. The $values array is nested such that the organism is identified by
  1227. * way of the organism_id foreign key constraint by specifying the genus and
  1228. * species. The cvterm is also specified using its foreign key and the cv_id
  1229. * for the cvterm is nested as well.
  1230. *
  1231. * @ingroup tripal_chado_api
  1232. */
  1233. function tripal_core_chado_get_foreign_key($table_desc, $field, $values, $options = NULL) {
  1234. // set defaults for options. If we don't set defaults then
  1235. // we get memory leaks when we try to access the elements
  1236. if (!is_array($options)) {
  1237. $options = array();
  1238. }
  1239. if (!array_key_exists('case_insensitive_columns', $options)) {
  1240. $options['case_insensitive_columns'] = array();
  1241. }
  1242. if (!array_key_exists('regex_columns', $options)) {
  1243. $options['regex_columns'] = array();
  1244. }
  1245. // get the list of foreign keys for this table description and
  1246. // iterate through those until we find the one we're looking for
  1247. $fkeys = '';
  1248. if (array_key_exists('foreign keys', $table_desc)) {
  1249. $fkeys = $table_desc['foreign keys'];
  1250. }
  1251. if ($fkeys) {
  1252. foreach ($fkeys as $name => $def) {
  1253. if (is_array($def['table'])) {
  1254. //foreign key was described 2X
  1255. $message = "The foreign key " . $name . " was defined twice. Please check modules "
  1256. . "to determine if hook_chado_schema_<version>_" . $table_desc['table'] . "() was "
  1257. . "implemented and defined this foreign key when it wasn't supposed to. Modules "
  1258. . "this hook was implemented in: " . implode(', ',
  1259. module_implements("chado_" . $table_desc['table'] . "_schema")) . ".";
  1260. watchdog('tripal_core', $message);
  1261. drupal_set_message(check_plain($message), 'error');
  1262. continue;
  1263. }
  1264. $table = $def['table'];
  1265. $columns = $def['columns'];
  1266. // iterate through the columns of the foreign key relationship
  1267. foreach ($columns as $left => $right) {
  1268. // does the left column in the relationship match our field?
  1269. if (strcmp($field, $left) == 0) {
  1270. // the column name of the foreign key matches the field we want
  1271. // so this is the right relationship. Now we want to select
  1272. $select_cols = array($right);
  1273. $result = tripal_core_chado_select($table, $select_cols, $values, $options);
  1274. $fields = array();
  1275. if ($result and count($result) > 0) {
  1276. foreach ($result as $obj) {
  1277. $fields[] = $obj->$right;
  1278. }
  1279. return $fields;
  1280. }
  1281. }
  1282. }
  1283. }
  1284. }
  1285. else {
  1286. // TODO: what do we do if we get to this point and we have a fk
  1287. // relationship expected but we don't have any definition for one in the
  1288. // table schema??
  1289. $version = tripal_core_get_chado_version(TRUE);
  1290. $message = t("There is no foreign key relationship defined for " . $field . " .
  1291. To define a foreign key relationship, determine the table this foreign
  1292. key referrs to (<foreign table>) and then implement
  1293. hook_chado_chado_schema_v<version>_<foreign table>(). See
  1294. tripal_feature_chado_v1_2_schema_feature for an example. Chado version: $version");
  1295. watchdog('tripal_core', $message);
  1296. drupal_set_message(check_plain($message), 'error');
  1297. }
  1298. return array();
  1299. }
  1300. /**
  1301. * Generates an object containing the full details of a record(s) in chado.
  1302. *
  1303. * This differs from the objects returned by tripal_core_chado_select in so far as all foreign key
  1304. * relationships have been followed meaning you have more complete details. Thus this function
  1305. * should be used whenever you need a full variable and tripal_core_chado_select should be used if
  1306. * you only case about a few columns.
  1307. *
  1308. * @param $table
  1309. * The name of the base table to generate a variable for
  1310. * @param $values
  1311. * A select values array that selects the records you want from the base table
  1312. * (this has the same form as tripal_core_chado_select)
  1313. * @param $base_options
  1314. * An array containing options for the base table. For example, an
  1315. * option of 'order_by' may be used to sort results in the base table
  1316. * if more than one are returned. The options must be compatible with
  1317. * the options accepted by the tripal_core_chado_select() function.
  1318. * Additionally, These options are available for this function:
  1319. * -return_array:
  1320. * can be provided to force the function to always return an array. Default
  1321. * behavior is to return a single record if only one record exists or to return
  1322. * an array if multiple records exist.
  1323. * - include_fk:
  1324. * an array of FK relationships to follow. By default, the
  1325. * tripal_core_chado_select function will follow all FK relationships but this
  1326. * may generate more queries then is desired slowing down this function call when
  1327. * there are lots of FK relationships to follow. Provide an array specifying the
  1328. * fields to include. For example, if expanding a property table (e.g. featureprop)
  1329. * and you want the CV and accession but do not want the DB the following
  1330. * array would work:
  1331. *
  1332. * $table_options = array(
  1333. * 'include_fk' => array(
  1334. * 'type_id' => array(
  1335. * 'cv_id' => 1,
  1336. * 'dbxref_id' => 1,
  1337. * )
  1338. * )
  1339. * );
  1340. *
  1341. * The above array will expand the 'type_id' of the property table but only
  1342. * further expand the cv_id and the dbxref_id and will go no further.
  1343. * - pager:
  1344. * Use this option if it is desired to return only a subset of results
  1345. * so that they may be shown within a Drupal-style pager. This should be
  1346. * an array with two keys: 'limit' and 'element'. The value of 'limit'
  1347. * should specify the number of records to return and 'element' is a
  1348. * unique integer to differentiate between pagers when more than one
  1349. * appear on a page. The 'element' should start with zero and increment by
  1350. * one for each pager. This only works when type is a 'table'.
  1351. * @return
  1352. * Either an object (if only one record was selected from the base table)
  1353. * or an array of objects (if more than one record was selected from the base table).
  1354. * If the option 'return_array' is provided the function always returns an array.
  1355. *
  1356. * Example Usage:
  1357. * @code
  1358. $values = array(
  1359. 'name' => 'Medtr4g030710'
  1360. );
  1361. $features = tripal_core_generate_chado_var('feature', $values);
  1362. * @endcode
  1363. * This will return an object if there is only one feature with the name Medtr4g030710 or it will
  1364. * return an array of feature objects if more than one feature has that name.
  1365. *
  1366. * Note to Module Designers: Fields can be excluded by default from these objects by implementing
  1367. * one of the following hooks:
  1368. * - hook_exclude_field_from_tablename_by_default (where tablename is the name of the table):
  1369. * This hook allows you to add fields to be excluded on a per table basis. Simply implement
  1370. * this hook to return an array of fields to be excluded. For example:
  1371. * @code
  1372. mymodule_exclude_field_from_feature_by_default() {
  1373. return array('residues' => TRUE);
  1374. }
  1375. * @endcode
  1376. * will ensure that feature.residues is ecluded from a feature object by default.
  1377. * - hook_exclude_type_by_default:
  1378. * This hook allows you to exclude fields from all tables that are of a given postgresql field
  1379. * type. Simply implement this hook to return an array of postgresql types mapped to criteria.
  1380. * Then all fields of that type where the criteria supplied returns TRUE will be excluded from
  1381. * any table. Tokens available in criteria are &gt;field_value&lt; and &gt;field_name&lt; . For example:
  1382. * @code
  1383. mymodule_exclude_type_by_default() {
  1384. return array('text' => 'length(&gt;field_value&lt; ) > 50');
  1385. }
  1386. * @endcode
  1387. * will exclude all text fields with a length > 50. Thus if $feature.residues is longer than 50 * it will be excluded, otherwise it will be added.
  1388. *
  1389. * @ingroup tripal_chado_api
  1390. */
  1391. function tripal_core_generate_chado_var($table, $values, $base_options = array()) {
  1392. $all = new stdClass();
  1393. $return_array = 0;
  1394. if (array_key_exists('return_array', $base_options)) {
  1395. $return_array = 1;
  1396. }
  1397. $include_fk = 0;
  1398. if (array_key_exists('include_fk', $base_options)) {
  1399. $include_fk = $base_options['include_fk'];
  1400. }
  1401. $pager = array();
  1402. if (array_key_exists('pager', $base_options)) {
  1403. $pager = $base_options['pager'];
  1404. }
  1405. // get description for the current table----------------------------------------------------------
  1406. $table_desc = tripal_core_get_chado_table_schema($table);
  1407. if (!$table_desc or count($table_desc) == 0) {
  1408. watchdog('tripal_core', "tripal_core_generate_chado_var: The table '%table' has not been defined. " .
  1409. "and cannot be expanded. If this is a custom table, please add it using the Tripal " .
  1410. "custom table interface.", array('%table' => $table), WATCHDOG_ERROR);
  1411. if ($return_array) {
  1412. return array();
  1413. }
  1414. return FALSE;
  1415. }
  1416. $table_primary_key = $table_desc['primary key'][0];
  1417. $table_columns = array_keys($table_desc['fields']);
  1418. // Expandable fields without value needed for criteria--------------------------------------------
  1419. $all->expandable_fields = array();
  1420. if ($table_desc['referring_tables']) {
  1421. $all->expandable_tables = $table_desc['referring_tables'];
  1422. }
  1423. else {
  1424. $all->expandable_tables = array();
  1425. }
  1426. $all->expandable_nodes = array();
  1427. /*
  1428. // Get fields to be removed by name.................................
  1429. $fields_to_remove = module_invoke_all('exclude_field_from_' . $table . '_by_default');
  1430. foreach ($fields_to_remove as $field_name => $criteria) {
  1431. //replace &gt;field_name&lt; with the current field name &
  1432. $criteria = preg_replace('/&gt;field_name&lt; /', addslashes($field_name), $criteria);
  1433. // if field_value needed we can't deal with this field yet
  1434. if (preg_match('/&gt;field_value&lt; /', $criteria)) {
  1435. break;
  1436. }
  1437. //if criteria then remove from query
  1438. // @coder-ignore: only module designers can populate $criteria -not security risk
  1439. $success = php_eval('<?php return ' . $criteria . '; ?>');
  1440. if ($success) {
  1441. unset($table_columns[array_search($field_name, $table_columns)]);
  1442. unset($fields_to_remove[$field_name]);
  1443. $all->expandable_fields[] = $table . '.' . $field_name;
  1444. }
  1445. }
  1446. //Get fields to be removed by type................................
  1447. $types_to_remove = module_invoke_all('exclude_type_by_default');
  1448. $field_types = array();
  1449. foreach ($table_desc['fields'] as $field_name => $field_array) {
  1450. $field_types[$field_array['type']][] = $field_name;
  1451. }
  1452. foreach ($types_to_remove as $field_type => $criteria) {
  1453. // if there are fields of that type to remove
  1454. if (is_array($field_types[$field_type])) {
  1455. //replace &gt;field_name&lt; with the current field name &
  1456. $criteria = preg_replace('/&gt;field_name&lt; /', addslashes($field_name), $criteria);
  1457. foreach ($field_types[$field_type] as $field_name) {
  1458. // if field_value needed we can't deal with this field yet
  1459. if (preg_match('/&gt;field_value&lt; /', $criteria)) {
  1460. $fields_to_remove[$field_name] = $criteria;
  1461. continue;
  1462. }
  1463. // if field_value needed we can't deal with this field yet
  1464. if (preg_match('/&gt;field_value&lt; /', $criteria)) {
  1465. break;
  1466. }
  1467. //if criteria then remove from query
  1468. // @coder-ignore: only module designers can populate $criteria -not security risk
  1469. $success = php_eval('<?php return ' . $criteria . '; ?>');
  1470. if ($success) {
  1471. unset($table_columns[array_search($field_name, $table_columns)]);
  1472. $all->expandable_fields[] = $table . '.' . $field_name;
  1473. }
  1474. } //end of foreach field of that type
  1475. }
  1476. } //end of foreach type to be removed
  1477. */
  1478. // get the values for the record in the current table---------------------------------------------
  1479. $results = tripal_core_chado_select($table, $table_columns, $values, $base_options);
  1480. if ($results) {
  1481. foreach ($results as $key => $object) {
  1482. // Add empty expandable_x arrays
  1483. $object->expandable_fields = $all->expandable_fields;
  1484. $object->expandable_tables = $all->expandable_tables;
  1485. $object->expandable_nodes = $all->expandable_nodes;
  1486. // add curent table
  1487. $object->tablename = $table;
  1488. // check if the current table maps to a node type-----------------------------------------------
  1489. // if this table is connected to a node there will be a chado_tablename table in drupal
  1490. if (db_table_exists('chado_' . $table)) {
  1491. // that has a foreign key to this one ($table_desc['primary key'][0]
  1492. // and to the node table (nid)
  1493. $sql = "
  1494. SELECT $table_primary_key, nid
  1495. FROM {chado_$table}
  1496. WHERE $table_primary_key = :$table_primary_key";
  1497. $mapping = db_query($sql, array(":$table_primary_key" => $object->{$table_primary_key}))->fetchObject();
  1498. if ($mapping->{$table_primary_key}) {
  1499. $object->nid = $mapping->nid;
  1500. $object->expandable_nodes[] = $table;
  1501. }
  1502. }
  1503. // remove any fields where criteria needs to be evalulated---------------------------------------
  1504. /* foreach ($fields_to_remove as $field_name => $criteria) {
  1505. if (!isset($object->{$field_name})) {
  1506. break;
  1507. }
  1508. $criteria = preg_replace('/&gt;field_value&lt; /', addslashes($object->{$field_name}), $criteria);
  1509. $success = php_eval('<?php return ' . $criteria . '; ?>');
  1510. if ($success) {
  1511. unset($object->{$field_name});
  1512. $object->expandable_fields[] = $table . '.' . $field_name;
  1513. }
  1514. }
  1515. */
  1516. // recursively follow foreign key relationships nesting objects as we go------------------------
  1517. if ($table_desc['foreign keys']) {
  1518. foreach ($table_desc['foreign keys'] as $foreign_key_array) {
  1519. $foreign_table = $foreign_key_array['table'];
  1520. foreach ($foreign_key_array['columns'] as $foreign_key => $primary_key) {
  1521. // Note: Foreign key is the field in the current table whereas primary_key is the field in
  1522. // the table referenced by the foreign key
  1523. //Dont do anything if the foreign key is empty
  1524. if (empty($object->{$foreign_key})) {
  1525. continue;
  1526. }
  1527. if ($include_fk) {
  1528. // don't recurse if the callee has supplied an $fk_include list and this
  1529. // FK table is not in the list.
  1530. if (is_array($include_fk) and !array_key_exists($foreign_key, $include_fk)) {
  1531. continue;
  1532. }
  1533. // if we have the option but it is not an array then we don't recurse any furutehr
  1534. if (!is_array($include_fk)) {
  1535. continue;
  1536. }
  1537. }
  1538. // get the record from the foreign table
  1539. $foreign_values = array($primary_key => $object->{$foreign_key});
  1540. $options = array();
  1541. if (is_array($include_fk)) {
  1542. $options['include_fk'] = $include_fk[$foreign_key];
  1543. }
  1544. $foreign_object = tripal_core_generate_chado_var($foreign_table, $foreign_values, $options);
  1545. // add the foreign record to the current object in a nested manner
  1546. $object->{$foreign_key} = $foreign_object;
  1547. // Flatten expandable_x arrays so only in the bottom object
  1548. if (is_array($object->{$foreign_key}->expandable_fields)) {
  1549. $object->expandable_fields = array_merge(
  1550. $object->expandable_fields,
  1551. $object->{$foreign_key}->expandable_fields
  1552. );
  1553. unset($object->{$foreign_key}->expandable_fields);
  1554. }
  1555. if (is_array($object->{$foreign_key}->expandable_tables)) {
  1556. $object->expandable_tables = array_merge(
  1557. $object->expandable_tables,
  1558. $object->{$foreign_key}->expandable_tables
  1559. );
  1560. unset($object->{$foreign_key}->expandable_tables);
  1561. }
  1562. if (is_array($object->{$foreign_key}->expandable_nodes)) {
  1563. $object->expandable_nodes = array_merge(
  1564. $object->expandable_nodes,
  1565. $object->{$foreign_key}->expandable_nodes
  1566. );
  1567. unset($object->{$foreign_key}->expandable_nodes);
  1568. }
  1569. }
  1570. }
  1571. $results[$key] = $object;
  1572. }
  1573. }
  1574. }
  1575. // convert the results into an array
  1576. $results_arr = array();
  1577. foreach ($results as $record) {
  1578. $results_arr[] = $record;
  1579. }
  1580. // check only one result returned
  1581. if (!$return_array) {
  1582. if (sizeof($results_arr) == 1) {
  1583. // add results to object
  1584. return $results_arr[0];
  1585. }
  1586. elseif (!empty($results_arr)) {
  1587. return $results_arr;
  1588. }
  1589. else {
  1590. // no results returned
  1591. }
  1592. }
  1593. // the caller has requested results are always returned as
  1594. // an array
  1595. else {
  1596. if (!$results_arr) {
  1597. return array();
  1598. }
  1599. else {
  1600. return $results_arr;
  1601. }
  1602. }
  1603. }
  1604. /**
  1605. * Retrieves fields/tables/nodes that were excluded by default from a variable and adds them
  1606. *
  1607. * This function exists to allow tripal_core_generate_chado_var() to excldue some
  1608. * fields/tables/nodes from the default form of a variable without making it extremely difficult for
  1609. * the tripal admin to get at these variables if he/she wants them.
  1610. *
  1611. * @param $object
  1612. * This must be an object generated using tripal_core_generate_chado_var()
  1613. * @param $type
  1614. * Must be one of 'field', 'table', 'node'. Indicates what is being expanded.
  1615. * @param $to_expand
  1616. * The name of the field/table/node to be expanded
  1617. * @param $table_options
  1618. * - order_by:
  1619. * An array containing options for the base table. For example, an
  1620. * option of 'order_by' may be used to sort results in the base table
  1621. * if more than one are returned. The options must be compatible with
  1622. * the options accepted by the tripal_core_chado_select() function.
  1623. * - return_array:
  1624. * Additionally, The option 'return_array' can be provided to force
  1625. * the function to expand tables as an array. Default behavior is to expand
  1626. * a table as single record if only one record exists or to expand as an array if
  1627. * multiple records exist.
  1628. * - include_fk:
  1629. * an array of FK relationships to follow. By default, the
  1630. * tripal_core_chado_select function will follow all FK relationships but this
  1631. * may generate more queries then is desired slowing down this function call when
  1632. * there are lots of FK relationships to follow. Provide an array specifying the
  1633. * fields to include. For example, if expanding a property table (e.g. featureprop)
  1634. * and you want the CV and accession but do not want the DB the following
  1635. * array would work:
  1636. * $table_options = array(
  1637. * 'include_fk' => array(
  1638. * 'type_id' => array(
  1639. * 'cv_id' => 1,
  1640. * 'dbxref_id' => 1,
  1641. * )
  1642. * )
  1643. * );
  1644. *
  1645. * The above array will expand the 'type_id' of the property table but only
  1646. * further expand the cv_id and the dbxref_id and will go no further.
  1647. * - pager:
  1648. * Use this option if it is desired to return only a subset of results
  1649. * so that they may be shown within a Drupal-style pager. This should be
  1650. * an array with two keys: 'limit' and 'element'. The value of 'limit'
  1651. * should specify the number of records to return and 'element' is a
  1652. * unique integer to differentiate between pagers when more than one
  1653. * appear on a page. The 'element' should start with zero and increment by
  1654. * one for each pager. This only works when type is a 'table'.
  1655. * @return
  1656. * A chado object supplemented with the field/table/node requested to be expanded.
  1657. * If the type is a table and it has already been expanded no changes is made to the
  1658. * returned object
  1659. *
  1660. * Example Usage:
  1661. * @code
  1662. // Get a chado object to be expanded
  1663. $values = array(
  1664. 'name' => 'Medtr4g030710'
  1665. );
  1666. $features = tripal_core_generate_chado_var('feature', $values);
  1667. // Expand the organism node
  1668. $feature = tripal_core_expand_chado_vars($feature, 'node', 'organism');
  1669. // Expand the feature.residues field
  1670. $feature = tripal_core_expand_chado_vars($feature, 'field', 'feature.residues');
  1671. // Expand the feature properties (featureprop table)
  1672. $feature = tripal_core_expand_chado_vars($feature, 'table', 'featureprop');
  1673. * @endcode
  1674. *
  1675. * @ingroup tripal_chado_api
  1676. */
  1677. function tripal_core_expand_chado_vars($object, $type, $to_expand, $table_options = array()) {
  1678. // make sure we have a value
  1679. if (!$object) {
  1680. watchdog('tripal_core', 'Cannot pass non array as argument, $object, to tripal_core_expand_chado_vars function.', array(), WATCHDOG_ERROR);
  1681. return $object;
  1682. }
  1683. // check to see if we are expanding an array of objects
  1684. if (is_array($object)) {
  1685. foreach ($object as $index => $o) {
  1686. $object[$index] = tripal_core_expand_chado_vars($o, $type, $to_expand);
  1687. }
  1688. return $object;
  1689. }
  1690. // get the base table name
  1691. $base_table = $object->tablename;
  1692. switch ($type) {
  1693. case "field": //--------------------------------------------------------------------------------
  1694. if (preg_match('/(\w+)\.(\w+)/', $to_expand, $matches)) {
  1695. $tablename = $matches[1];
  1696. $fieldname = $matches[2];
  1697. $table_desc = tripal_core_get_chado_table_schema($tablename);
  1698. $values = array();
  1699. foreach ($table_desc['primary key'] as $key) {
  1700. $values[$key] = $object->{$key};
  1701. }
  1702. if ($base_table == $tablename) {
  1703. //get the field
  1704. $results = tripal_core_chado_select($tablename, array($fieldname), $values);
  1705. $object->{$fieldname} = $results[0]->{$fieldname};
  1706. $object->expanded = $to_expand;
  1707. }
  1708. else {
  1709. //We need to recurse -the field is in a nested object
  1710. foreach ((array) $object as $field_name => $field_value) {
  1711. if (is_object($field_value)) {
  1712. $object->{$field_name} = tripal_core_expand_chado_vars(
  1713. $field_value,
  1714. 'field',
  1715. $to_expand
  1716. );
  1717. }
  1718. } //end of for each field in the current object
  1719. }
  1720. }
  1721. else {
  1722. watchdog('tripal_core', 'tripal_core_expand_chado_vars: Field (%field) not in the right format. " .
  1723. "It should be <tablename>.<fieldname>', WATCHDOG_ERROR);
  1724. }
  1725. break;
  1726. case "table": //--------------------------------------------------------------------------------
  1727. $foreign_table = $to_expand;
  1728. // don't expand the table it already is expanded
  1729. if (array_key_exists($foreign_table, $object)) {
  1730. return $object;
  1731. }
  1732. $foreign_table_desc = tripal_core_get_chado_table_schema($foreign_table);
  1733. // If it's connected to the base table via a FK constraint
  1734. if ($foreign_table_desc['foreign keys'][$base_table]) {
  1735. foreach ($foreign_table_desc['foreign keys'][$base_table]['columns'] as $left => $right) {
  1736. // if the FK value in the base table is not there then we can't expand it, so just skip it.
  1737. if (!$object->{$right}) {
  1738. continue;
  1739. }
  1740. // generate a new object for this table using the FK values in the base table.
  1741. // if a prepared statement is provided generate a new statement_name so that
  1742. // we don't conflict when we recurse.
  1743. $new_options = $table_options;
  1744. /*
  1745. if (array_key_exists('statement_name', $table_options)) {
  1746. $new_options['statement_name'] = "exp_" . $foreign_table . "_" . substr($left, 0, 2) . substr($right, 0, 2);
  1747. }
  1748. */
  1749. $foreign_object = tripal_core_generate_chado_var($foreign_table, array($left => $object->{$right}), $new_options);
  1750. // if the generation of the object was successful, update the base object to include it.
  1751. if ($foreign_object) {
  1752. // in the case where the foreign key relationships exists more
  1753. // than once with the same table we want to alter the array structure. rather than
  1754. // add the object with a key of the table name, we will add the FK field name in between
  1755. if (count($foreign_table_desc['foreign keys'][$base_table]['columns']) > 1) {
  1756. if (!is_object($object->{$foreign_table})) {
  1757. $object->{$foreign_table} = new stdClass();
  1758. }
  1759. $object->{$foreign_table}->{$left} = $foreign_object;
  1760. $object->expanded = $to_expand;
  1761. }
  1762. else {
  1763. if (!property_exists($object, $foreign_table) or !is_object($object->{$foreign_table})) {
  1764. $object->{$foreign_table} = new stdClass();
  1765. }
  1766. $object->{$foreign_table} = $foreign_object;
  1767. $object->expanded = $to_expand;
  1768. }
  1769. }
  1770. // if the object returned is NULL then handle that
  1771. else {
  1772. if (count($foreign_table_desc['foreign keys'][$base_table]['columns']) > 1) {
  1773. if (!is_object($object->{$foreign_table})) {
  1774. $object->{$foreign_table} = new stdClass();
  1775. }
  1776. $object->{$foreign_table}->{$left} = NULL;
  1777. }
  1778. else {
  1779. $object->{$foreign_table} = NULL;
  1780. }
  1781. }
  1782. }
  1783. }
  1784. // if the foreign table is not connected to the base table through a FK constraint
  1785. else {
  1786. // We need to recurse -the table has a relationship to one of the nested objects
  1787. $did_expansion = 0;
  1788. foreach ((array) $object as $field_name => $field_value) {
  1789. // if we have a nested object ->expand the table in it
  1790. if (is_object($field_value)) {
  1791. $did_expansion = 1;
  1792. $object->{$field_name} = tripal_core_expand_chado_vars($field_value, 'table', $foreign_table);
  1793. }
  1794. }
  1795. // if we did not expand this table we should return a message that the foreign table
  1796. // could not be expanded
  1797. if (!$did_expansion) {
  1798. watchdog('tripal_core', 'tripal_core_expand_chado_vars: Could not expand table, %table. It is ' .
  1799. 'not in a foreign key relationship with the base object nor with any other expanded table. ' .
  1800. 'Check the table definition to ensure that a proper foreign key relationship is present.',
  1801. array('%table' => $foreign_table), WATCHDOG_ERROR);
  1802. }
  1803. }
  1804. break;
  1805. case "node": //---------------------------------------------------------------------------------
  1806. //if the node to be expanded is for our base table, then just expand it
  1807. if ($object->tablename == $to_expand) {
  1808. $node = node_load($object->nid);
  1809. if ($node) {
  1810. $object->expanded = $to_expand;
  1811. $node->expandable_fields = $object->expandable_fields;
  1812. unset($object->expandable_fields);
  1813. $node->expandable_tables = $object->expandable_tables;
  1814. unset($object->expandable_tables);
  1815. $node->expandable_nodes = $object->expandable_nodes;
  1816. unset($object->expandable_nodes);
  1817. $node->{$base_table} = $object;
  1818. $object = $node;
  1819. }
  1820. else {
  1821. watchdog('tripal_core', 'tripal_core_expand_chado_vars: No node matches the nid (%nid) supplied.',
  1822. array('%nid' => $object->nid), WATCHDOG_ERROR);
  1823. } //end of if node
  1824. }
  1825. else {
  1826. //We need to recurse -the node to expand is one of the nested objects
  1827. foreach ((array) $object as $field_name => $field_value) {
  1828. if (is_object($field_value)) {
  1829. $object->{$field_name} = tripal_core_expand_chado_vars(
  1830. $field_value,
  1831. 'node',
  1832. $to_expand
  1833. );
  1834. }
  1835. } //end of for each field in the current object
  1836. }
  1837. break;
  1838. default:
  1839. watchdog('tripal_core', 'tripal_core_expand_chado_vars: Unrecognized type (%type). Should be one of "field", "table", "node".',
  1840. array('%type' => $type), WATCHDOG_ERROR);
  1841. return FALSE;
  1842. }
  1843. //move extended array downwards-------------------------------------------------------------------
  1844. if (!$object->expanded) {
  1845. //if there's no extended field then go hunting for it
  1846. foreach ( (array)$object as $field_name => $field_value) {
  1847. if (is_object($field_value)) {
  1848. if (isset($field_value->expanded)) {
  1849. $object->expanded = $field_value->expanded;
  1850. unset($field_value->expanded);
  1851. }
  1852. }
  1853. }
  1854. }
  1855. //try again becasue now we might have moved it down
  1856. if ($object->expanded) {
  1857. $expandable_name = 'expandable_' . $type . 's';
  1858. if ($object->{$expandable_name}) {
  1859. $key_to_remove = array_search($object->expanded, $object->{$expandable_name});
  1860. unset($object->{$expandable_name}[$key_to_remove]);
  1861. unset($object->expanded);
  1862. }
  1863. else {
  1864. // if there is an expandable array then we've reached the base object
  1865. // if we get here and don't have anything expanded then something went wrong
  1866. // watchdog(
  1867. // 'tripal_core',
  1868. // 'tripal_core_expand_chado_vars: Unable to expand the %type %to_expand',
  1869. // array('%type'=>$type, '%to_expand'=>$to_expand),
  1870. // WATCHDOG_ERROR
  1871. // );
  1872. } //end of it we've reached the base object
  1873. }
  1874. return $object;
  1875. }
  1876. /**
  1877. * Implements hook_exclude_type_by_default()
  1878. *
  1879. * This hooks allows fields of a specified type that match a specified criteria to be excluded by
  1880. * default from any table when tripal_core_generate_chado_var() is called. Keep in mind that if
  1881. * fields are excluded by default they can always be expanded at a later date using
  1882. * tripal_core_expand_chado_vars().
  1883. *
  1884. * Criteria are php strings that evaluate to either TRUE or FALSE. These strings are evaluated using
  1885. * drupal_eval() which suppresses syntax errors and throws watchdog entries of type php. There are
  1886. * also watchdog entries of type tripal_core stating the exact criteria evaluated. Criteria can
  1887. * contain the following tokens:
  1888. * - &gt;field_name&lt;
  1889. * Replaced by the name of the field to be excluded
  1890. * - &gt;field_value&lt;
  1891. * Replaced by the value of the field in the current record
  1892. * Also keep in mind that if your criteria doesn't contain the &gt;field_value&lt; token then it will be
  1893. * evaluated before the query is executed and if the field is excluded it won't be included in the
  1894. * query.
  1895. *
  1896. * @return
  1897. * An array of type => criteria where the type is excluded if the criteria evaluates to TRUE
  1898. *
  1899. * @ingroup tripal_chado_api
  1900. */
  1901. function tripal_core_exclude_type_by_default() {
  1902. return array('text' => 'strlen("&gt;field_value&lt; ") > 100');
  1903. }
  1904. /**
  1905. * Implements hook_exclude_field_from_<tablename>_by_default()
  1906. *
  1907. * This hooks allows fields from a specified table that match a specified criteria to be excluded by
  1908. * default from any table when tripal_core_generate_chado_var() is called. Keep in mind that if
  1909. * fields are excluded by default they can always be expanded at a later date using
  1910. * tripal_core_expand_chado_vars().
  1911. *
  1912. * Criteria are php strings that evaluate to either TRUE or FALSE. These strings are evaluated using
  1913. * drupal_eval() which suppresses syntax errors and throws watchdog entries of type php. There are
  1914. * also watchdog entries of type tripal_core stating the exact criteria evaluated. Criteria can
  1915. * contain the following tokens:
  1916. * - &gt;field_name&lt;
  1917. * Replaced by the name of the field to be excluded
  1918. * - &gt;field_value&lt;
  1919. * Replaced by the value of the field in the current record
  1920. * Also keep in mind that if your criteria doesn't contain the &gt;field_value&lt; token then it will be
  1921. * evaluated before the query is executed and if the field is excluded it won't be included in the
  1922. * query.
  1923. *
  1924. * @return
  1925. * An array of type => criteria where the type is excluded if the criteria evaluates to TRUE
  1926. *
  1927. * @ingroup tripal_chado_api
  1928. */
  1929. function tripal_core_exclude_field_from_feature_by_default() {
  1930. return array();
  1931. }
  1932. /**
  1933. * Use this function instead of pager_query() when selecting a
  1934. * subset of records from a Chado table.
  1935. *
  1936. * @param $query
  1937. * The SQL statement to execute, this is followed by a variable number of args
  1938. * used as substitution values in the SQL statement.
  1939. * @param $limit
  1940. * The number of query results to display per page.
  1941. * @param $element
  1942. * An optional integer to distinguish between multiple pagers on one page.
  1943. * @param $count_query
  1944. * An SQL query used to count matching records.
  1945. *
  1946. * @returns
  1947. * A database query result resource or FALSE if the query was not
  1948. * executed correctly
  1949. *
  1950. * @ingroup tripal_chado_api
  1951. */
  1952. function chado_pager_query($query, $limit, $element, $count_query) {
  1953. // The following code is almost an exact duplicate of the
  1954. // Drupal pager_query function. However, substitions have
  1955. // been made to call chado_query rather than db_query
  1956. global $pager_page_array, $pager_total, $pager_total_items;
  1957. $page = isset($_GET['page']) ? $_GET['page'] : '';
  1958. // get the SQL query arguments that get substituted into modifiers later.
  1959. $args = func_get_args();
  1960. $args = array_slice($args, 4);
  1961. // Alternative syntax for '...'
  1962. if (isset($args[0]) && is_array($args[0])) {
  1963. $args = $args[0];
  1964. }
  1965. // Construct a count query if none was given.
  1966. if (!isset($count_query)) {
  1967. $count_query = preg_replace(array('/SELECT.*?FROM /As', '/ORDER BY .*/'), array('SELECT COUNT(*) FROM ', ''), $query);
  1968. }
  1969. // Convert comma-separated $page to an array, used by other functions.
  1970. $pager_page_array = explode(',', $page);
  1971. // We calculate the total of pages as ceil(items / limit).
  1972. $pager_total_items[$element] = db_result(chado_query($count_query, $args));
  1973. $pager_total[$element] = ceil($pager_total_items[$element] / $limit);
  1974. $pager_page_array[$element] = max(0, min((int) $pager_page_array[$element], ((int) $pager_total[$element]) - 1));
  1975. return chado_query_range($query, $args, $pager_page_array[$element] * $limit, $limit);
  1976. }
  1977. /**
  1978. * Use this function instead of db_query_range().
  1979. *
  1980. * @param $sql
  1981. * The SQL statement to execute, this is followed by a variable number of args
  1982. * used as substitution values in the SQL statement.
  1983. * @param $args
  1984. * The SQL arguments
  1985. * @param $from
  1986. * The first result row to return..
  1987. * @param $count
  1988. * The maximum number of result rows to return.
  1989. *
  1990. * @returns
  1991. * A database query result resource or FALSE if the query was not
  1992. * executed correctly
  1993. *
  1994. * @ingroup tripal_chado_api
  1995. */
  1996. function chado_query_range($query, $args, $from, $count) {
  1997. $query .= ' LIMIT ' . (int) $count . ' OFFSET ' . (int) $from;
  1998. return chado_query($query, $args);
  1999. }
  2000. /**
  2001. * Use this function instead of db_query() to avoid switching databases
  2002. * when making query to the chado database
  2003. *
  2004. * Will use a chado persistent connection if it already exists
  2005. *
  2006. * @param $sql
  2007. * The sql statement to execute
  2008. *
  2009. * @param $args
  2010. * The array of arguments, with the same structure as passed to
  2011. * the db_query() function of Drupal.
  2012. *
  2013. * @return
  2014. * DatabaseStatementInterface A prepared statement object, already executed.
  2015. *
  2016. * @ingroup tripal_chado_api
  2017. */
  2018. function chado_query($sql, $args = array()) {
  2019. $is_local = tripal_core_is_chado_local();
  2020. // Args should be an array
  2021. if (!is_array($args)) {
  2022. tripal_core_report_error('tripal_core', TRIPAL_ERROR,
  2023. 'chado_query; Need to pass an array to chado_query, "%value" passed instead. Query: %query',
  2024. array('%value' => $args, '%query' => $sql)
  2025. );
  2026. $args = array($args);
  2027. }
  2028. // if Chado is local to the database then prefix the Chado table
  2029. // names with 'chado'.
  2030. if ($is_local) {
  2031. $sql = preg_replace('/\n/', '', $sql); // remove carriage returns
  2032. $sql = preg_replace('/\{(.*?)\}/', 'chado.$1', $sql);
  2033. // the featureloc table has some indexes that use function that call other functions
  2034. // and those calls do not reference a schema, therefore, any tables with featureloc
  2035. // must automaticaly have the chado schema set as active to find
  2036. if(preg_match('/chado.featureloc/i', $sql)) {
  2037. $previous_db = tripal_db_set_active('chado') ;
  2038. $results = db_query($sql, $args);
  2039. tripal_db_set_active($previous_db);
  2040. }
  2041. // for all other tables we should have everything in scope so just run the query
  2042. else {
  2043. $results = db_query($sql, $args);
  2044. }
  2045. }
  2046. // if Chado is not local to the Drupal database then we have to
  2047. // switch to another database
  2048. else {
  2049. $previous_db = tripal_db_set_active('chado') ;
  2050. $results = db_query($sql);
  2051. tripal_db_set_active($previous_db);
  2052. }
  2053. return $results;
  2054. }
  2055. /**
  2056. * Get chado id for a node. E.g, if you want to get 'analysis_id' from the
  2057. * 'analysis' table for a synced 'chado_analysis' node, (the same for
  2058. * organisms and features):
  2059. * $analysis_id = chado_get_id_for_node ('analysis', $node->nid)
  2060. * $organism_id = chado_get_id_for_node ('organism', $node->nid)
  2061. * $feature_id = chado_get_id_for_node ('feature', $node->nid)
  2062. *
  2063. * @param $table
  2064. * @param $nid
  2065. *
  2066. * @ingroup tripal_chado_api
  2067. */
  2068. function chado_get_id_for_node($table, $nid) {
  2069. $sql = "SELECT " . $table . "_id as id FROM {chado_$table} WHERE nid = :nid";
  2070. return db_query($sql, array(':nid' => $nid))->fetchField();
  2071. }
  2072. /**
  2073. * Get node id for a chado feature/organism/analysis. E.g, if you want to
  2074. * get the node id for an analysis, use:
  2075. * $nid = chado_get_node_id ('analysis', $analysis_id)
  2076. * Likewise,
  2077. * $nid = chado_get_node_id ('organism', $organism_id)
  2078. * $nid = chado_get_node_id ('feature', $feature_id)
  2079. *
  2080. * @ingroup tripal_chado_api
  2081. */
  2082. function chado_get_node_id($table, $id) {
  2083. $sql = "SELECT nid FROM {chado_$table} WHERE " . $table . "_id = :" . $table . "_id";
  2084. return db_query($sql, array(":" . $table . "_id" => $id))->fetchField();
  2085. }
  2086. /**
  2087. * Retrieve a property for a given base table record
  2088. *
  2089. * @param $basetable
  2090. * The base table for which the property should be retrieved. Thus to retrieve a property
  2091. * for a feature the basetable=feature and property is retrieved from featureprop
  2092. * @param $record_id
  2093. * The foriegn key field of the base table. This should be in integer.
  2094. * @param $property
  2095. * The cvterm name describing the type of properties to be retrieved
  2096. * @param $cv_name
  2097. * The name of the cv that the above cvterm is part of
  2098. *
  2099. * @return
  2100. * An array in the same format as that generated by the function
  2101. * tripal_core_generate_chado_var(). If only one record is returned it
  2102. * is a single object. If more than one record is returned then it is an array
  2103. * of objects
  2104. *
  2105. * @ingroup tripal_chado_api
  2106. */
  2107. function tripal_core_get_property($basetable, $record_id, $property, $cv_name) {
  2108. // get the foreign key for this property table
  2109. $table_desc = tripal_core_get_chado_table_schema($basetable . 'prop');
  2110. $fkcol = key($table_desc['foreign keys'][$basetable]['columns']);
  2111. // construct the array of values to be selected
  2112. $values = array(
  2113. $fkcol => $record_id,
  2114. 'type_id' => array(
  2115. 'cv_id' => array(
  2116. 'name' => $cv_name,
  2117. ),
  2118. 'name' => $property,
  2119. 'is_obsolete' => 0
  2120. ),
  2121. );
  2122. $results = tripal_core_generate_chado_var($basetable . 'prop', $values);
  2123. if ($results) {
  2124. $results = tripal_core_expand_chado_vars($results, 'field', $basetable . 'prop.value');
  2125. }
  2126. return $results;
  2127. }
  2128. /**
  2129. * Insert a property for a given base table. By default if the property already
  2130. * exists a new property is added with the next available rank. If
  2131. * $update_if_present argument is specified then the record will be updated if it
  2132. * exists rather than adding a new property.
  2133. *
  2134. * @param $basetable
  2135. * The base table for which the property should be inserted. Thus to insert a property
  2136. * for a feature the basetable=feature and property is inserted into featureprop
  2137. * @param $record_id
  2138. * The foriegn key value of the base table. This should be in integer.
  2139. * @param $property
  2140. * The cvterm name describing the type of properties to be inserted
  2141. * @param $cv_name
  2142. * The name of the cv that the above cvterm is part of
  2143. * @param $value
  2144. * The value of the property to be inserted (can be empty)
  2145. * @param $update_if_present
  2146. * A boolean indicating whether an existing record should be updated. If the
  2147. * property already exists and this value is not specified or is zero then
  2148. * a new property will be added with the next largest rank.
  2149. *
  2150. * @return
  2151. * Return True on Insert/Update and False otherwise
  2152. *
  2153. * @ingroup tripal_chado_api
  2154. */
  2155. function tripal_core_insert_property($basetable, $record_id, $property,
  2156. $cv_name, $value, $update_if_present = 0) {
  2157. // first see if the property already exists, if the user want's to update
  2158. // then we can do that, but otherwise we want to increment the rank and
  2159. // insert
  2160. $props = tripal_core_get_property($basetable, $record_id, $property, $cv_name);
  2161. if (!is_array($props) and $props) {
  2162. $props = array($props);
  2163. }
  2164. $rank = 0;
  2165. if (count($props) > 0) {
  2166. if ($update_if_present) {
  2167. return tripal_core_update_property($basetable, $record_id, $property, $cv_name, $value);
  2168. }
  2169. else {
  2170. // iterate through the properties returned and check to see if the
  2171. // property with this value already exists if not, get the largest rank
  2172. // and insert the same property but with this new value
  2173. foreach ($props as $p) {
  2174. if ($p->rank > $rank) {
  2175. $rank = $p->rank;
  2176. }
  2177. if (strcmp($p->value, $value) == 0) {
  2178. return TRUE;
  2179. }
  2180. }
  2181. // now add 1 to the rank
  2182. $rank++;
  2183. }
  2184. }
  2185. // make sure the cvterm exists. Otherwise we'll get an error with
  2186. // prepared statements not matching
  2187. $values = array(
  2188. 'cv_id' => array(
  2189. 'name' => $cv_name,
  2190. ),
  2191. 'name' => $property,
  2192. );
  2193. $options = array('statement_name' => 'sel_cvterm_cv');
  2194. $term = tripal_core_chado_select('cvterm', array('cvterm_id'), $values, $options);
  2195. if (!$term or count($term) == 0) {
  2196. watchdog('tripal_core', "Cannot find property '%prop_name'.",
  2197. array('%prop_name' => $property), WATCHDOG_ERROR);
  2198. return FALSE;
  2199. }
  2200. // get the foreign key for this property table
  2201. $table_desc = tripal_core_get_chado_table_schema($basetable . 'prop');
  2202. $fkcol = key($table_desc['foreign keys'][$basetable]['columns']);
  2203. // construct the array of values to be inserted
  2204. $values = array(
  2205. $fkcol => $record_id,
  2206. 'type_id' => array(
  2207. 'cv_id' => array(
  2208. 'name' => $cv_name,
  2209. ),
  2210. 'name' => $property,
  2211. ),
  2212. 'value' => $value,
  2213. 'rank' => $rank,
  2214. );
  2215. $options = array('statement_name' => 'ins_' . $basetable . 'prop_' . substr($fkcol, 0, 2) . 'tyvara');
  2216. $result = tripal_core_chado_insert($basetable . 'prop', $values, $options);
  2217. return $result;
  2218. }
  2219. /**
  2220. * Update a property for a given base table record and property name. This
  2221. * function should be used only if one record of the property will be present.
  2222. * If the property name can have multiple entries (with increasing rank) then
  2223. * use the function named tripal_core_update_property_by_id
  2224. *
  2225. * @param $basetable
  2226. * The base table for which the property should be updated. The property table
  2227. * is constructed using a combination of the base table name and the suffix
  2228. * 'prop' (e.g. basetable = feature then property tabie is featureprop).
  2229. * @param $record_id
  2230. * The foreign key of the basetable to update a property for. This should be in integer.
  2231. * For example, if the basetable is 'feature' then the $record_id should be the feature_id
  2232. * @param $property
  2233. * The cvterm name of property to be updated
  2234. * @param $cv_name
  2235. * The name of the cv that the above cvterm is part of
  2236. * @param $value
  2237. * The value of the property to be inserted (can be empty)
  2238. * @param $insert_if_missing
  2239. * A boolean indicating whether a record should be inserted if one doesn't exist to update
  2240. *
  2241. * Note: The property to be updated is select via the unique combination of $record_id and
  2242. * $property and then it is updated with the supplied value
  2243. *
  2244. * @return
  2245. * Return True on Update/Insert and False otherwise
  2246. *
  2247. * @ingroup tripal_chado_api
  2248. */
  2249. function tripal_core_update_property($basetable, $record_id, $property,
  2250. $cv_name, $value, $insert_if_missing = 0) {
  2251. // first see if the property is missing (we can't update a missing property
  2252. $prop = tripal_core_get_property($basetable, $record_id, $property, $cv_name);
  2253. if (count($prop)==0) {
  2254. if ($insert_if_missing) {
  2255. return tripal_core_insert_property($basetable, $record_id, $property, $cv_name, $value);
  2256. }
  2257. else {
  2258. return FALSE;
  2259. }
  2260. }
  2261. // get the foreign key for this property table
  2262. $table_desc = tripal_core_get_chado_table_schema($basetable . 'prop');
  2263. $fkcol = key($table_desc['foreign keys'][$basetable]['columns']);
  2264. // construct the array that will match the exact record to update
  2265. $match = array(
  2266. $fkcol => $record_id,
  2267. 'type_id' => array(
  2268. 'cv_id' => array(
  2269. 'name' => $cv_name,
  2270. ),
  2271. 'name' => $property,
  2272. ),
  2273. );
  2274. // construct the array of values to be updated
  2275. $values = array(
  2276. 'value' => $value,
  2277. );
  2278. return tripal_core_chado_update($basetable . 'prop', $match, $values);
  2279. }
  2280. /**
  2281. * Update a property for a given base table record. This function should be
  2282. * used if multiple records of the same property will be present. Also, use this
  2283. * function to change the property name of an existing property.
  2284. *
  2285. * @param $basetable
  2286. * The base table for which the property should be updated. The property table
  2287. * is constructed using a combination of the base table name and the suffix
  2288. * 'prop' (e.g. basetable = feature then property tabie is featureprop).
  2289. * @param $record_id
  2290. * The primary key of the base table. This should be in integer.
  2291. * For example, if the basetable is 'feature' then the $record_id should be the featureprop_id
  2292. * @param $property
  2293. * The cvterm name of property to be updated
  2294. * @param $cv_name
  2295. * The name of the cv that the above cvterm is part of
  2296. * @param $value
  2297. * The value of the property to be inserted (can be empty)
  2298. *
  2299. * @return
  2300. * Return True on Update/Insert and False otherwise
  2301. *
  2302. * @ingroup tripal_chado_api
  2303. */
  2304. function tripal_core_update_property_by_id($basetable, $record_id, $property,
  2305. $cv_name, $value) {
  2306. // get the primary key for this property table
  2307. $table_desc = tripal_core_get_chado_table_schema($basetable . 'prop');
  2308. $pkcol = $table_desc['primary key'][0];
  2309. // construct the array that will match the exact record to update
  2310. $match = array(
  2311. $pkcol => $record_id,
  2312. );
  2313. // construct the array of values to be updated
  2314. $values = array(
  2315. 'type_id' => array(
  2316. 'cv_id' => array(
  2317. 'name' => $cv_name,
  2318. ),
  2319. 'name' => $property,
  2320. ),
  2321. 'value' => $value,
  2322. );
  2323. return tripal_core_chado_update($basetable . 'prop', $match, $values);
  2324. }
  2325. /**
  2326. * Deletes a property for a given base table record using the property name
  2327. *
  2328. * @param $basetable
  2329. * The base table for which the property should be deleted. Thus to deleted a property
  2330. * for a feature the basetable=feature and property is deleted from featureprop
  2331. * @param $record_id
  2332. * The primary key of the basetable to delete a property for. This should be in integer.
  2333. * @param $property
  2334. * The cvterm name describing the type of property to be deleted
  2335. * @param $cv_name
  2336. * The name of the cv that the above cvterm is part of
  2337. *
  2338. * Note: The property to be deleted is select via the unique combination of $record_id and $property
  2339. *
  2340. * @return
  2341. * Return True on Delete and False otherwise
  2342. *
  2343. * @ingroup tripal_chado_api
  2344. */
  2345. function tripal_core_delete_property($basetable, $record_id, $property, $cv_name) {
  2346. // get the foreign key for this property table
  2347. $table_desc = tripal_core_get_chado_table_schema($basetable . 'prop');
  2348. $fkcol = key($table_desc['foreign keys'][$basetable]['columns']);
  2349. // construct the array that will match the exact record to update
  2350. $match = array(
  2351. $fkcol => $record_id,
  2352. 'type_id' => array(
  2353. 'cv_id' => array(
  2354. 'name' => $cv_name,
  2355. ),
  2356. 'name' => $property,
  2357. ),
  2358. );
  2359. return tripal_core_chado_delete($basetable . 'prop', $match);
  2360. }
  2361. /**
  2362. * Deletes a property using the property ID
  2363. *
  2364. * @param $basetable
  2365. * The base table for which the property should be deleted. Thus to deleted a property
  2366. * for a feature the basetable=feature and property is deleted from featureprop
  2367. * @param $record_id
  2368. * The primary key of the basetable to delete a property for. This should be in integer.
  2369. *
  2370. * @return
  2371. * Return True on Delete and False otherwise
  2372. *
  2373. * @ingroup tripal_chado_api
  2374. */
  2375. function tripal_core_delete_property_by_id($basetable, $record_id) {
  2376. // get the foreign key for this property table
  2377. $table_desc = tripal_core_get_chado_table_schema($basetable . 'prop');
  2378. $pkcol = $table_desc['primary key'][0];
  2379. // construct the array that will match the exact record to update
  2380. $match = array(
  2381. $pkcol => $record_id,
  2382. );
  2383. return tripal_core_chado_delete($basetable . 'prop', $match);
  2384. }
  2385. /**
  2386. * Start a transaction block. Ensures the use of a persistent chado connection
  2387. */
  2388. function tripal_db_start_transaction() {
  2389. $transaction = db_transaction();
  2390. return $transaction;
  2391. }
  2392. /**
  2393. * Set a savepoint to roll the current transaction back to if an error is encountered
  2394. */
  2395. function tripal_db_set_savepoint_transaction($savepoint, $release = FALSE) {
  2396. // Postgresql requires a savepoint of the same name to be unset before re-use
  2397. if ($release) {
  2398. chado_query("RELEASE SAVEPOINT :savepoint", array(':savepoint' => $savepoint));
  2399. }
  2400. chado_query("SAVEPOINT :savepoint", array(':savepoint' => $savepoint));
  2401. }
  2402. /**
  2403. * Rollback changes.
  2404. *
  2405. * If $savepoint is NULL then rollback to the beginning of the transaction,
  2406. * Otherwise, rollback to the point at which the named $savepoint was created
  2407. *
  2408. * @param $savepoint
  2409. * The name of the saved point in the transaction to rollback to
  2410. *
  2411. * @param $commit
  2412. * The transcation will only be committed if this value is TRUE. The
  2413. * default is TRUE.
  2414. *
  2415. * @return nothing
  2416. */
  2417. function tripal_db_rollback_transaction($savepoint = NULL, $commit = TRUE) {
  2418. if ($savepoint) {
  2419. chado_query("ROLLBACK TO SAVEPOINT :savepoint", array(':savepoint' => $savepoint));
  2420. }
  2421. else {
  2422. chado_query("ROLLBACK");
  2423. }
  2424. if ($commit) {
  2425. tripal_db_commit_transaction();
  2426. }
  2427. }
  2428. /**
  2429. * Retrieves the schema in an array for the specified custom table.
  2430. *
  2431. * @param $table
  2432. * The name of the table to create.
  2433. *
  2434. * @return
  2435. * A Drupal-style Schema API array definition of the table. Returns
  2436. * FALSE on failure.
  2437. *
  2438. * @ingroup tripal_core_api
  2439. */
  2440. function tripal_get_chado_custom_schema($table) {
  2441. $sql = "SELECT schema FROM {tripal_custom_tables} WHERE table_name = :table_name";
  2442. $results = db_query($sql, array(':table_name' => $table));
  2443. $custom = $results->fetchObject();
  2444. if (!$custom) {
  2445. return FALSE;
  2446. }
  2447. else {
  2448. return unserialize($custom->schema);
  2449. }
  2450. }
  2451. /**
  2452. * Check that any given Chado table exists. This function
  2453. * is necessary because Drupa's db_table_exists function
  2454. * hardcodes the 'public'
  2455. *
  2456. * @return
  2457. * TRUE/FALSE depending upon whether it exists
  2458. */
  2459. function chado_table_exists($table) {
  2460. global $databases;
  2461. $default_db = $databases['default']['default']['database'];
  2462. $sql = "
  2463. SELECT 1
  2464. FROM information_schema.tables
  2465. WHERE
  2466. table_name = :table_name AND
  2467. table_schema = 'chado' AND
  2468. table_catalog = '$default_db'
  2469. ";
  2470. $results = db_query($sql, array(':table_name' => $table));
  2471. $exists = $results->fetchObject();
  2472. if (!$exists) {
  2473. return FALSE;
  2474. }
  2475. return TRUE;
  2476. }
  2477. /**
  2478. * Check that the Chado schema exists within the local database
  2479. *
  2480. * @return
  2481. * TRUE/FALSE depending upon whether it exists
  2482. */
  2483. function tripal_core_chado_schema_exists() {
  2484. // This is postgresql-specific code to check the existence of the chado schema
  2485. // @coder-ignore: acting on pg_catalog schema rather then drupal schema therefore, table prefixing does not apply
  2486. $sql = "
  2487. SELECT nspname
  2488. FROM pg_namespace
  2489. WHERE
  2490. has_schema_privilege(nspname, 'USAGE') AND
  2491. nspname = 'chado'
  2492. ";
  2493. $results = db_query($sql);
  2494. $name = $results->fetchObject();
  2495. if ($name) {
  2496. variable_set('chado_schema_exists', FALSE);
  2497. return TRUE;
  2498. }
  2499. else {
  2500. variable_set('chado_schema_exists', TRUE);
  2501. return FALSE;
  2502. }
  2503. }
  2504. /**
  2505. * Check that any given schema exists
  2506. *
  2507. * @param $schema
  2508. * The name of the schema to check the existence of
  2509. *
  2510. * @return
  2511. * TRUE/FALSE depending upon whether or not the schema exists
  2512. *
  2513. * @ingroup tripal_chado_api
  2514. */
  2515. function tripal_core_schema_exists($schema) {
  2516. // check that the chado schema now exists
  2517. $sql = "
  2518. SELECT nspname
  2519. FROM pg_namespace
  2520. WHERE
  2521. has_schema_privilege(nspname, 'USAGE') AND
  2522. nspname = :nspname
  2523. ORDER BY nspname
  2524. ";
  2525. $results = db_query($sql, array(':nspname' => $schema));
  2526. $name = $results->fetchObject();
  2527. if (strcmp($name->nspname, $schema) != 0) {
  2528. return FALSE;
  2529. }
  2530. return TRUE;
  2531. }
  2532. /**
  2533. * Retrieves the list tables in the Chado schema. By default it only retursn
  2534. * the default Chado tables, but may also return custom tables added to the
  2535. * Chado schema as well.
  2536. *
  2537. * @param $include_custom
  2538. * Optional. Set as TRUE to include any custom tables created in the
  2539. * Chado schema. Custom tables are added to Chado using the
  2540. * tripal_core_chado_create_table() function.
  2541. *
  2542. * @returns
  2543. * An associative array where the key and value pairs are the Chado table names.
  2544. *
  2545. * @ingroup tripal_core_api
  2546. */
  2547. function tripal_core_get_chado_tables($include_custom = NULL) {
  2548. // first get the chado version that is installed
  2549. $v = tripal_core_get_chado_version();
  2550. $tables = array();
  2551. if ($v == '1.2') {
  2552. $tables_v1_2 = tripal_core_chado_get_v1_2_tables();
  2553. foreach ($tables_v1_2 as $table) {
  2554. $tables[$table] = $table;
  2555. }
  2556. }
  2557. if ($v == '1.11' or $v == '1.11 or older') {
  2558. $tables_v1_11 = tripal_core_chado_get_v1_11_tables();
  2559. foreach ($tables_v1_11 as $table) {
  2560. $tables[$table] = $table;
  2561. }
  2562. }
  2563. // now add in the custom tables too if requested
  2564. if ($include_custom) {
  2565. $sql = "SELECT table_name FROM {tripal_custom_tables}";
  2566. $resource = db_query($sql);
  2567. foreach ($resource as $r) {
  2568. $tables[$r->table_name] = $r->table_name;
  2569. }
  2570. }
  2571. asort($tables);
  2572. return $tables;
  2573. }
  2574. /**
  2575. * Sets a Drupal variable with the current version of Chado. This variable
  2576. * can then be queried later using the tripal_core_get_chado_Version
  2577. *
  2578. * @returns
  2579. * The version of Chado
  2580. *
  2581. * @ingroup tripal_core_api
  2582. */
  2583. function tripal_core_set_chado_version() {
  2584. global $databases;
  2585. $is_local = 0;
  2586. // check that Chado is installed if not return 'uninstalled as the version'
  2587. $chado_exists = tripal_core_chado_schema_exists();
  2588. if (!$chado_exists) {
  2589. // if it's not in the drupal database check to see if it's specified in the $db_url
  2590. // in the settings.php
  2591. if (!array_key_exists('chado', $databases)) {
  2592. // if it's not in the drupal database or specified in the $db_url then
  2593. // return uninstalled as the version
  2594. return 'not installed';
  2595. }
  2596. $previous_db = tripal_db_set_active('chado');
  2597. $prop_exists = db_table_exists('chadoprop');
  2598. tripal_db_set_active($previous_db);
  2599. }
  2600. else {
  2601. $is_local = 1;
  2602. $prop_exists = db_table_exists('chado.chadoprop');
  2603. }
  2604. // if the table doesn't exist then we don't know what version but we know
  2605. // it must be 1.11 or older.
  2606. if (!$prop_exists) {
  2607. return "1.11 or older";
  2608. }
  2609. $sql = "
  2610. SELECT value
  2611. FROM {chadoprop} CP
  2612. INNER JOIN {cvterm} CVT on CVT.cvterm_id = CP.type_id
  2613. INNER JOIN {cv} CV on CVT.cv_id = CV.cv_id
  2614. WHERE CV.name = 'chado_properties' and CVT.name = 'version'
  2615. ";
  2616. if (!$is_local) {
  2617. $previous_db = tripal_db_set_active('chado');
  2618. $results = db_query($sql);
  2619. tripal_db_set_active($previous_db);
  2620. }
  2621. else {
  2622. $results = chado_query($sql);
  2623. }
  2624. $v = $results->fetchObject();
  2625. // if we don't have a version in the chadoprop table then it must be
  2626. // v1.11 or older
  2627. if (!$v) {
  2628. variable_set('chado_version', "1.11 or older");
  2629. return "1.11 or older";
  2630. }
  2631. variable_set('chado_version', $v->value);
  2632. return $v->value;
  2633. }
  2634. /**
  2635. * Returns the version number of the currently installed Chado instance.
  2636. * It can return the real or effective version.
  2637. *
  2638. * @param $exact
  2639. * Set this argument to 1 to retrieve the exact version that is installed.
  2640. * Otherwise, this function will set the version to the nearest 'tenth'.
  2641. * Chado versioning numbers in the hundreds represent changes to the
  2642. * software and not the schema. Changes in the tenth's represent changes
  2643. * in the schema.
  2644. *
  2645. * @param $warn_if_unsupported
  2646. * If the currently installed version of Chado is not supported by Tripal
  2647. * the generatea a Drupal warning.
  2648. *
  2649. * @returns
  2650. * The version of Chado
  2651. *
  2652. * @ingroup tripal_core_api
  2653. */
  2654. function tripal_core_get_chado_version($exact = FALSE, $warn_if_unsupported = FALSE) {
  2655. // first get the chado version that is installed
  2656. // $exact_version = variable_get('chado_version', '');
  2657. // if (!$exact_version) {
  2658. $exact_version = tripal_core_set_chado_version();
  2659. // }
  2660. // Tripal only supports v1.11 or newer.. really this is the same as v1.1
  2661. // but at the time the v1.11 schema API was written we didn't know that so
  2662. // we'll return the version 1.11 so the schema API will work.
  2663. if (strcmp($exact_version, '1.11 or older') == 0) {
  2664. $exact_version = "1.11";
  2665. if ($warn_if_unsupported) {
  2666. drupal_set_message(t("WARNING: Tripal does not fully support Chado version less than v1.11. If you are certain this is v1.11
  2667. or if Chado was installed using an earlier version of Tripal then all is well. If not please upgrade to v1.11 or later"),
  2668. 'warning');
  2669. }
  2670. }
  2671. // if not returing an exact version, return the version to the nearest 10th.
  2672. // return 1.2 for all versions of 1.2x
  2673. $effective_version = $exact_version;
  2674. if (preg_match('/^1\.2\d+$/', $effective_version)) {
  2675. $effective_version = "1.2";
  2676. }
  2677. if ($warn_if_unsupported and ($effective_version != 1.11 and $effective_version != 1.2 and $effective_version != 'not installed')) {
  2678. drupal_set_message(t("WARNING: The currently installed version of Chado, v$exact_version, is not fully compatible with Tripal."), 'warning');
  2679. }
  2680. // if the callee has requested the exact version then return it
  2681. if ($exact) {
  2682. return $exact_version;
  2683. }
  2684. return $effective_version;
  2685. }
  2686. /**
  2687. * Retrieves the chado tables Schema API array.
  2688. *
  2689. * @param $table
  2690. * The name of the table to retrieve. The function will use the appopriate
  2691. * Tripal chado schema API hooks (e.g. v1.11 or v1.2).
  2692. *
  2693. * @returns
  2694. * A Drupal Schema API array defining the table.
  2695. *
  2696. * @ingroup tripal_core_api
  2697. */
  2698. function tripal_core_get_chado_table_schema($table) {
  2699. // first get the chado version that is installed
  2700. $v = tripal_core_get_chado_version();
  2701. // get the table array from the proper chado schema
  2702. $v = preg_replace("/\./", "_", $v); // reformat version for hook name
  2703. $table_arr = module_invoke_all("chado_schema_v" . $v . "_" . $table);
  2704. // if the table_arr is empty then maybe this is a custom table
  2705. if (!is_array($table_arr) or count($table_arr) == 0) {
  2706. $table_arr = tripal_get_chado_custom_schema($table);
  2707. }
  2708. return $table_arr;
  2709. }
  2710. /**
  2711. * This function will delete Drupal nodes for any sync'ed table (e.g.
  2712. * feature, organism, analysis, stock, library) if the chado record has been
  2713. * deleted or the entry in the chado_[table] table has been removed.
  2714. *
  2715. * @param $table
  2716. * The name of the table that corresonds to the node type we want to clean up.
  2717. * @param $job_id
  2718. * This should be the job id from the Tripal jobs system. This function
  2719. * will update the job status using the provided job ID.
  2720. *
  2721. * @ingroup tripal_core_api
  2722. */
  2723. function tripal_core_clean_orphaned_nodes($table, $job_id) {
  2724. $count = 0;
  2725. // build the SQL statments needed to check if nodes point to valid analyses
  2726. $dsql = "SELECT * FROM {node} WHERE type = 'chado_" . $table . "' order by nid";
  2727. $nsql = "SELECT * FROM {node} WHERE nid = :nid";
  2728. $csql = "SELECT * FROM {chado_" . $table . "} WHERE nid = :nid ";
  2729. $clsql= "SELECT * FROM {chado_" . $table . "}";
  2730. $lsql = "SELECT * FROM {" . $table . "} where " . $table . "_id = :" . $table . "_id ";
  2731. // load into nodes array
  2732. print "Getting nodes\n";
  2733. $nodes = array();
  2734. $res = db_query($dsql);
  2735. foreach ($res as $node) {
  2736. $nodes[$count] = $node;
  2737. $count++;
  2738. }
  2739. // load the chado_$table into an array
  2740. print "Getting chado_$table\n";
  2741. $cnodes = array();
  2742. $res = db_query($clsql);
  2743. foreach ($res as $node) {
  2744. $cnodes[$count] = $node;
  2745. $count++;
  2746. }
  2747. $interval = intval($count * 0.01);
  2748. if ($interval < 1) {
  2749. $interval = 1;
  2750. }
  2751. // iterate through all of the chado_$table entries and remove those
  2752. // that don't have a node or don't have a $table record in chado.libary
  2753. print "Verifying all chado_$table Entries\n";
  2754. $deleted = 0;
  2755. foreach ($cnodes as $nid) {
  2756. // update the job status every 1% analyses
  2757. if ($job_id and $i % $interval == 0) {
  2758. tripal_job_set_progress($job_id, intval(($i / $count) * 100));
  2759. }
  2760. // see if the node exits, if not remove the entry from the chado_$table table
  2761. $results = db_query($nsql, array(':nid' => $nid->nid));
  2762. $node = $results->fetchObject();
  2763. if (!$node) {
  2764. $deleted++;
  2765. db_query("DELETE FROM {chado_" . $table . "} WHERE nid = :nid", array(':nid' => $nid->nid));
  2766. $message = "chado_$table missing node.... DELETING: $nid->nid";
  2767. watchdog('tripal_core', $message, array(), WATCHDOG_WARNING);
  2768. }
  2769. // see if the record in chado exist, if not remove the entry from the chado_$table
  2770. $table_id = $table . "_id";
  2771. $results = chado_query($lsql, array(":" . $table . "_id" => $nid->$table_id));
  2772. $record = $results->fetchObject();
  2773. if (!$record) {
  2774. $deleted++;
  2775. $sql = "DELETE FROM {chado_" . $table . "} WHERE " . $table . "_id = :" . $table . "_id";
  2776. db_query($sql, array(":" . $table . "_id" => $nid->$table_id));
  2777. $message = "chado_$table missing $table.... DELETING entry.";
  2778. watchdog('tripal_core', $message, array(), WATCHDOG_WARNING);
  2779. }
  2780. $i++;
  2781. }
  2782. print "\t$deleted chado_$table entries missing either a node or chado entry.\n";
  2783. // iterate through all of the nodes and delete those that don't
  2784. // have a corresponding entry in chado_$table
  2785. $deleted = 0;
  2786. foreach ($nodes as $node) {
  2787. // update the job status every 1% libraries
  2788. if ($job_id and $i % $interval == 0) {
  2789. tripal_job_set_progress($job_id, intval(($i / $count) * 100));
  2790. }
  2791. // check to see if the node has a corresponding entry
  2792. // in the chado_$table table. If not then delete the node.
  2793. $results = db_query($csql, array(":nid" => $node->nid));
  2794. $link = $results->fetchObject();
  2795. if (!$link) {
  2796. if (node_access('delete', $node)) {
  2797. $deleted++;
  2798. $message = "Node missing in chado_$table table.... DELETING node $node->nid";
  2799. watchdog("tripal_core", $message, array(), WATCHDOG_WARNING);
  2800. node_delete($node->nid);
  2801. }
  2802. else {
  2803. $message = "Node missing in chado_$table table.... but cannot delete due to improper permissions (node $node->nid)";
  2804. watchdog("tripal_core", $message, array(), WATCHDOG_WARNING);
  2805. }
  2806. }
  2807. $i++;
  2808. }
  2809. print "\t$deleted nodes did not have corresponding chado_$table entries.\n";
  2810. return '';
  2811. }
  2812. /**
  2813. * Check whether chado is installed (either in the same or a different database)
  2814. *
  2815. * @return
  2816. * TRUE/FALSE depending upon whether chado is installed.
  2817. *
  2818. * @ingroup tripal_chado_api
  2819. */
  2820. function tripal_core_is_chado_installed() {
  2821. global $databases;
  2822. // first check if chado is in the $databases variable of the settings.php file
  2823. if (array_key_exists('chado', $databases)) {
  2824. return TRUE;
  2825. }
  2826. // check to make sure the chado schema exists
  2827. return tripal_core_chado_schema_exists();
  2828. }
  2829. /**
  2830. * Check whether chado is installed local to the Drupal database
  2831. * in its own Chado schema.
  2832. *
  2833. * @return
  2834. * TRUE/FALSE depending upon whether chado is local.
  2835. *
  2836. * @ingroup tripal_chado_api
  2837. */
  2838. function tripal_core_is_chado_local() {
  2839. global $db_url, $db_type;
  2840. $is_installed = tripal_core_is_chado_installed();
  2841. if ($is_installed) {
  2842. if (is_array($db_url)) {
  2843. if (isset($db_url['chado'])) {
  2844. return FALSE;
  2845. }
  2846. }
  2847. return TRUE;
  2848. }
  2849. return FALSE;
  2850. }
  2851. /**
  2852. * Determine whether a given chado table is directly linked to a node
  2853. *
  2854. * @param $chado_table
  2855. * The name of a chado table to check (ie: feature)
  2856. * @return
  2857. * TRUE if it is linked to a node and FALSE otherwise
  2858. */
  2859. function tripal_core_is_tripal_node_type($chado_table) {
  2860. $linking_table = 'chado_' . $chado_table;
  2861. if (db_table_exists($linking_table)) {
  2862. return TRUE;
  2863. }
  2864. else {
  2865. return FALSE;
  2866. }
  2867. }
  2868. /**
  2869. * Set the Tripal Database
  2870. *
  2871. * The tripal_db_set_active function is used to prevent namespace collisions
  2872. * when chado and drupal are installed in the same database but in different
  2873. * schemas. It is also used for backwards compatibility with older versions
  2874. * of tripal or in cases where chado is located outside of the Drupal database.
  2875. * or when using Drupal functions such as db_table_exists()
  2876. *
  2877. * @ingroup tripal_chado_api
  2878. */
  2879. function tripal_db_set_active($dbname = 'default') {
  2880. global $databases, $active_db;
  2881. $chado_exists = variable_get('chado_schema_exists', FALSE);
  2882. if ($dbname ) {
  2883. if ($dbname == 'chado') {
  2884. db_query('set search_path to chado,public');
  2885. return 'default';
  2886. }
  2887. else {
  2888. db_query('set search_path to public');
  2889. return 'chado';
  2890. }
  2891. }
  2892. // if the 'chado' database is in the $db_url variable then chado is
  2893. // not in the same Drupal database, so we don't need to set any
  2894. // search_path and can just change the database
  2895. elseif (array_key_exists($dbname, $databases)) {
  2896. return db_set_active($dbname);
  2897. }
  2898. }