tripal_core.api.inc 114 KB

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