tripal_chado.install.inc 29 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828
  1. <?php
  2. /**
  3. * @file
  4. * Functions to install chado schema through Drupal
  5. */
  6. /**
  7. * Load Chado Schema Form
  8. *
  9. * @ingroup tripal_chado
  10. */
  11. function tripal_chado_load_form($form, $form_state) {
  12. // we want to force the version of Chado to be set properly
  13. $real_version = chado_get_version(TRUE);
  14. // get the effective version. Pass true as second argument
  15. // to warn the user if the current version is not compatible
  16. $version = chado_get_version(FALSE, TRUE);
  17. if (array_key_exists('values', $form_state)) {
  18. if ($form_state['values']['action_to_do'] == "Upgrade Chado v1.2 to v1.3") {
  19. $tables_list = implode(', ', [
  20. 'analysis_cvterm',
  21. 'analysis_dbxref',
  22. 'analysis_pub',
  23. 'analysis_relationship',
  24. 'contactprop',
  25. 'dbprop',
  26. 'feature_contact',
  27. 'featuremap_contact',
  28. 'featuremap_dbxref',
  29. 'featuremap_organism',
  30. 'featuremapprop',
  31. 'featureposprop',
  32. 'library_contact',
  33. 'library_expression',
  34. 'library_expressionprop',
  35. 'library_featureprop',
  36. 'library_relationship',
  37. 'library_relationship_pub',
  38. 'nd_experiment_analysis',
  39. 'organism_cvterm',
  40. 'organism_cvtermprop',
  41. 'organism_pub',
  42. 'organism_relationship',
  43. 'organismprop_pub',
  44. 'phenotypeprop',
  45. 'phylotreeprop',
  46. 'project_analysis',
  47. 'project_dbxref',
  48. 'project_feature',
  49. 'project_stock',
  50. 'pubauthor_contact',
  51. 'stock_feature',
  52. 'stock_featuremap',
  53. 'stock_library',
  54. 'stockcollection_db',
  55. ]);
  56. $items = [
  57. 'PostgreSQL version 9.5 is required to perform this upgrade. If your Tripal
  58. site uses an older version please upgrade before proceeding.',
  59. 'A major change between Chado v1.2 and v1.3 is that primary and foreign
  60. keys were upgraded from integers to big integers. If your site has custom
  61. materialized views that will hold data derived from fields changed to
  62. big integers then you may need to alter the views to change the fields
  63. from integers to big integers and repopulate those views. If you have not
  64. added any materialized views you can ignore this issue.',
  65. 'Custom PL/pgSQL functions that expect primary and
  66. foreign key fields to be integers will not work after the upgrade.
  67. Those functions will need to be altered to accept big integers. If you
  68. do not have any custom PL/pgSQL functions you can ignore this issue.',
  69. 'PostgreSQL Views that use fields that are converted to big
  70. integers will cause this upgrade to fail. You must first remove
  71. those views, perform the upgrade and then recreate them with the
  72. appropriate fields change to big integers. If you do not have custom
  73. PostgreSQL Views you can ignore this issue.',
  74. 'Several new tables were added to Chado v1.3. However, some groups have
  75. added these tables to their Chado v1.2 installation. The Tripal upgrader
  76. will alter the primary and foreign keys of those tables to be "bigints"
  77. if they already exist but will otherwise leave them the same. You should
  78. verify that any tables with Chado v1.3 names correctly match the v1.3 schema.
  79. Otherwise you may have problems using Tripal. If you have not added any
  80. Chado v1.3 tables to your Chado v1.2 database you can ignore this issue.
  81. These are the newly added tables: ' .
  82. $tables_list . '.',
  83. ];
  84. $list = theme_item_list([
  85. 'items' => $items,
  86. 'title' => '',
  87. 'type' => 'ul',
  88. 'attributes' => [],
  89. ]);
  90. drupal_set_message('Please note: the upgrade of Chado from v1.2 to v1.3 may
  91. require several fixes to your database. Please review the following
  92. list to ensure a safe upgrade. The Tripal upgrader is
  93. not able to fix these problems automatically: ' . $list, 'warning');
  94. }
  95. if ($form_state['values']['action_to_do'] == "Install Chado v1.3" or
  96. $form_state['values']['action_to_do'] == "Install Chado v1.2" or
  97. $form_state['values']['action_to_do'] == "Install Chado v1.11") {
  98. drupal_set_message('Please note: if Chado is already installed it will
  99. be removed and recreated and all data will be lost. If this is
  100. desired or if this is the first time Chado has been installed
  101. you can ignore this issue.', 'warning');
  102. }
  103. }
  104. $form['current_version'] = [
  105. '#type' => 'item',
  106. '#title' => t("Current installed version of Chado:"),
  107. '#description' => $real_version,
  108. ];
  109. $form['action_to_do'] = [
  110. '#type' => 'select',
  111. '#title' => 'Installation/Upgrade Action',
  112. '#options' => [
  113. 'Install Chado v1.3' => t('New Install of Chado v1.3 (erases all existing Chado data if Chado already exists)'),
  114. 'Upgrade Chado v1.2 to v1.3' => t('Upgrade existing Chado v1.2 to v1.3 (no data is lost)'),
  115. 'Install Chado v1.2' => t('New Install of Chado v1.2 (erases all existing Chado data if Chado already exists)'),
  116. 'Upgrade Chado v1.11 to v1.2' => t('Upgrade existing Chado v1.11 to v1.2 (no data is lost)'),
  117. 'Install Chado v1.11' => t('New Install of Chado v1.11 (erases all existing Chado data if Chado already exists)'),
  118. ],
  119. '#description' => t('Select an action to perform.'),
  120. '#required' => TRUE,
  121. '#ajax' => [
  122. 'callback' => "tripal_chado_load_form_ajax_callback",
  123. 'wrapper' => 'tripal_chado_load_form',
  124. 'effect' => 'fade',
  125. 'method' => 'replace',
  126. ],
  127. ];
  128. $form['button'] = [
  129. '#type' => 'submit',
  130. '#value' => t('Install/Upgrade Chado'),
  131. ];
  132. $form['#prefix'] = '<div id="tripal_chado_load_form">';
  133. $form['#suffix'] = '</div>';
  134. return $form;
  135. }
  136. /**
  137. * Ajax callback function for the gensas_job_view_panel_form.
  138. *
  139. * @param $form
  140. * @param $form_state
  141. */
  142. function tripal_chado_load_form_ajax_callback($form, $form_state) {
  143. return $form;
  144. }
  145. function tripal_chado_load_form_validate($form, &$form_state) {
  146. // We do not want to allow re-installation of Chado if other
  147. // Tripal modules are installed. This is because the install files
  148. // of those modules may add content to Chado and reinstalling Chado
  149. // removes that content which may break the modules.
  150. if ($form_state['values']['action_to_do'] == "Install Chado v1.3" or
  151. $form_state['values']['action_to_do'] == "Install Chado v1.2" or
  152. $form_state['values']['action_to_do'] == "Install Chado v1.11") {
  153. $modules = system_get_info('module');
  154. // The tripal_chado_views module should not be included as it's a required
  155. // dependency of tripal_chado
  156. unset($modules['tripal_chado_views']);
  157. $list = [];
  158. foreach ($modules as $mname => $module) {
  159. if (array_key_exists('dependencies', $module) and in_array('tripal_chado', $module['dependencies'])) {
  160. $list[] = $module['name'] . " ($mname)";
  161. }
  162. }
  163. if (count($list) > 0) {
  164. form_set_error("action_to_do", "Chado cannot be installed while other Tripal modules
  165. are enabled. You must fully uninstall the following modules if you
  166. would like to install or re-install chado.<br>" .
  167. implode("<br>", $list));
  168. }
  169. }
  170. if ($form_state['values']['action_to_do'] == "Upgrade Chado v1.11 to v1.2") {
  171. // Make sure we are already not at v1.2
  172. $real_version = chado_get_version(TRUE);
  173. if ($real_version == "1.2") {
  174. form_set_error("action_to_do", "You are already at v1.2. There is no need to upgrade.");
  175. }
  176. }
  177. if ($form_state['values']['action_to_do'] == "Upgrade Chado v1.2 to v1.3") {
  178. // Make sure we are already not at v1.3
  179. $real_version = chado_get_version(TRUE);
  180. if ($real_version == "1.3") {
  181. form_set_error("action_to_do", "You are already at v1.3. There is no need to upgrade.");
  182. }
  183. }
  184. }
  185. /**
  186. * Submit Load Chado Schema Form
  187. *
  188. * @ingroup tripal_chado
  189. */
  190. function tripal_chado_load_form_submit($form, &$form_state) {
  191. global $user;
  192. $action_to_do = trim($form_state['values']['action_to_do']);
  193. $args = [$action_to_do];
  194. $includes = [module_load_include('inc', 'tripal_chado', 'includes/tripal_chado.install')];
  195. tripal_add_job($action_to_do, 'tripal_chado',
  196. 'tripal_chado_install_chado', $args, $user->uid, 10, $includes);
  197. }
  198. /**
  199. * Submit Load Chado Schema Form
  200. *
  201. * @ingroup tripal_chado
  202. */
  203. function tripal_chado_load_drush_submit($version) {
  204. $args = [$version];
  205. $includes = [module_load_include('inc', 'tripal_chado', 'includes/tripal_chado.install')];
  206. tripal_add_job($version, 'tripal_chado',
  207. 'tripal_chado_install_chado', $args, 1, 10, $includes);
  208. }
  209. /**
  210. * Install Chado Schema
  211. *
  212. * @ingroup tripal_chado
  213. */
  214. function tripal_chado_install_chado($action, TripalJob $job = NULL) {
  215. $vsql = "
  216. INSERT INTO {chadoprop} (type_id, value)
  217. VALUES (
  218. (SELECT cvterm_id
  219. FROM {cvterm} CVT
  220. INNER JOIN {cv} CV on CVT.cv_id = CV.cv_id
  221. WHERE CV.name = 'chado_properties' AND CVT.name = 'version'),
  222. :version)
  223. ";
  224. $vusql = "
  225. UPDATE {chadoprop}
  226. SET value = :version
  227. WHERE type_id = (SELECT cvterm_id
  228. FROM {cvterm} CVT
  229. INNER JOIN {cv} CV on CVT.cv_id = CV.cv_id
  230. WHERE CV.name = 'chado_properties' AND CVT.name = 'version')
  231. ";
  232. $transaction = db_transaction();
  233. try {
  234. if ($action == 'Install Chado v1.3') {
  235. tripal_chado_install_chado_1_3();
  236. chado_query($vsql, [':version' => '1.3']);
  237. }
  238. elseif ($action == 'Upgrade Chado v1.2 to v1.3') {
  239. tripal_chado_upgrade_chado_1_2_to_1_3();
  240. chado_query($vusql, [':version' => '1.3']);
  241. }
  242. elseif ($action == 'Install Chado v1.2') {
  243. tripal_chado_install_chado_1_2();
  244. chado_query($vsql, [':version' => '1.2']);
  245. }
  246. elseif ($action == 'Upgrade Chado v1.11 to v1.2') {
  247. tripal_chado_upgrade_chado_1_11_to_1_2();
  248. chado_query($vsql, [':version' => '1.2']);
  249. }
  250. elseif ($action == 'Install Chado v1.11') {
  251. tripal_chado_install_chado_1_11();
  252. }
  253. } catch (Exception $e) {
  254. $transaction->rollback();
  255. tripal_chado_install_done();
  256. tripal_report_error('tripal_chado', TRIPAL_ERROR, $e->getMessage(), ['print' => TRUE]);
  257. return FALSE;
  258. }
  259. return TRUE;
  260. }
  261. /**
  262. * Installs Chado v1.3.
  263. */
  264. function tripal_chado_install_chado_1_3(TripalJob $job = NULL) {
  265. // Get the path to the schema and init SQL files.
  266. $schema_file = drupal_get_path('module', 'tripal_chado') .
  267. '/chado_schema/default_schema-1.3.sql';
  268. $init_file = drupal_get_path('module', 'tripal_chado') .
  269. '/chado_schema/initialize-1.3.sql';
  270. // Erase the Chado schema if it exists and perform the install.
  271. if (tripal_chado_reset_chado_schema()) {
  272. $success = tripal_chado_install_sql($schema_file);
  273. if ($success) {
  274. print "Install of Chado v1.3 (Step 1 of 2) Successful!\n";
  275. }
  276. else {
  277. throw new Exception("Installation (Step 1 of 2) Problems! Please check output above for errors.");
  278. }
  279. $success = tripal_chado_install_sql($init_file);
  280. if ($success) {
  281. print "Install of Chado v1.3 (Step 2 of 2) Successful.\nInstallation Complete\n";
  282. }
  283. else {
  284. throw new Exception("Installation (Step 2 of 2) Problems! Please check output above for errors.");
  285. }
  286. }
  287. else {
  288. throw new Exception("ERROR: cannot install chado. Please check database permissions");
  289. }
  290. }
  291. /**
  292. * Installs Chado v1.2.
  293. */
  294. function tripal_chado_install_chado_1_2() {
  295. // Get the path to the schema and init SQL files.
  296. $schema_file = drupal_get_path('module', 'tripal_chado') .
  297. '/chado_schema/default_schema-1.2.sql';
  298. $init_file = drupal_get_path('module', 'tripal_chado') .
  299. '/chado_schema/initialize-1.2.sql';
  300. // Erase the Chado schema if it exists and perform the install.
  301. if (tripal_chado_reset_chado_schema()) {
  302. $success = tripal_chado_install_sql($schema_file);
  303. if ($success) {
  304. print "Install of Chado v1.2 (Step 1 of 2) Successful!\n";
  305. }
  306. else {
  307. throw new Exception("Installation (Step 1 of 2) Problems! Please check output above for errors.");
  308. }
  309. $success = tripal_chado_install_sql($init_file);
  310. if ($success) {
  311. print "Install of Chado v1.2 (Step 2 of 2) Successful.\nInstallation Complete\n";
  312. }
  313. else {
  314. throw new Exception("Installation (Step 2 of 2) Problems! Please check output above for errors.");
  315. }
  316. }
  317. else {
  318. throw new Exception("ERROR: cannot install chado. Please check database permissions");
  319. }
  320. }
  321. /**
  322. *
  323. */
  324. function tripal_chado_install_chado_1_11() {
  325. // Get the path to the schema and init SQL files.
  326. $schema_file = drupal_get_path('module', 'tripal_chado') .
  327. '/chado_schema/default_schema-1.11.sql';
  328. $init_file = drupal_get_path('module', 'tripal_chado') .
  329. '/chado_schema/initialize-1.11.sql';
  330. // Erase the Chado schema if it exists and perform the install.
  331. if (tripal_chado_reset_chado_schema()) {
  332. $success = tripal_chado_install_sql($schema_file);
  333. if ($success) {
  334. print "Install of Chado v1.11 (Step 1 of 2) Successful!\n";
  335. }
  336. else {
  337. throw new Exception("Installation (Step 1 of 2) Problems! Please check output above for errors.");
  338. }
  339. $success = tripal_chado_install_sql($init_file);
  340. if ($success) {
  341. print "Install of Chado v1.11 (Step 2 of 2) Successful.\nInstallation Complete!\n";
  342. }
  343. else {
  344. throw new Exception("Installation (Step 2 of 2) Problems! Please check output above for errors.");
  345. }
  346. }
  347. else {
  348. throw new Exception("ERROR: cannot install chado. Please check database permissions");
  349. }
  350. }
  351. /**
  352. * Upgrades Chado from v1.2 to v1.3
  353. */
  354. function tripal_chado_upgrade_chado_1_2_to_1_3() {
  355. // Upgrade some of the custom tables that Tripal created that are now in
  356. // Chado v1.3. We'll do this ahead of time because the upgrade script won't
  357. // upgrade tables if they already exist.
  358. print "Checking for existing v1.3 tables in v1.2 and fixing bigints...\n";
  359. tripal_chado_upgrade_chado_1_2_to_1_3_pre_alter();
  360. // Get the path to the diff schema and upgrade SQL files.
  361. print "Incorporating additional changes...\n";
  362. $diff_file = drupal_get_path('module', 'tripal_chado') .
  363. '/chado_schema/default_schema-1.2-1.3-diff.sql';
  364. $success = tripal_chado_install_sql($diff_file);
  365. if ($success) {
  366. print "Upgrade from v1.2 to v1.3 Successful!\n";
  367. }
  368. else {
  369. throw new Exception("Upgrade problems! Please check output above for errors.");
  370. }
  371. }
  372. /**
  373. * Upgrade custom tables that may match the tables now in Chado v1.3.
  374. *
  375. * There were many new tables that were added to Chado v1.3 that were
  376. * suggested by the Chado user community. Some of those were Tripal users.
  377. * Therefore, to help these Tripal users upgrade more seemlessly this function
  378. * checks if those custom tables already exists, and if so updates them as
  379. * best it can to match. At a minimum it will create the table if it doesn't
  380. * exist and if it does it will change the primary keys and foreign keys to
  381. * be big ints.
  382. */
  383. function tripal_chado_upgrade_chado_1_2_to_1_3_pre_alter() {
  384. // Include the Chado v1.3 schema definitions.
  385. module_load_include('inc', 'tripal_chado', '/api/tripal_chado.schema_v1.3.api');
  386. // The list of new tables in Chado v1.3
  387. $new_tables = [
  388. 'analysis_cvterm',
  389. 'analysis_dbxref',
  390. 'analysis_pub',
  391. 'analysis_relationship',
  392. 'contactprop',
  393. 'dbprop',
  394. 'feature_contact',
  395. 'featuremap_contact',
  396. 'featuremap_dbxref',
  397. 'featuremap_organism',
  398. 'featuremapprop',
  399. 'featureposprop',
  400. 'library_contact',
  401. 'library_expression',
  402. 'library_expressionprop',
  403. 'library_featureprop',
  404. 'library_relationship',
  405. 'library_relationship_pub',
  406. 'nd_experiment_analysis',
  407. 'organism_cvterm',
  408. 'organism_cvtermprop',
  409. 'organism_pub',
  410. 'organism_relationship',
  411. 'organismprop_pub',
  412. 'phenotypeprop',
  413. 'phylotreeprop',
  414. 'project_analysis',
  415. 'project_dbxref',
  416. 'project_feature',
  417. 'project_stock',
  418. 'pubauthor_contact',
  419. 'stock_feature',
  420. 'stock_featuremap',
  421. 'stock_library',
  422. 'stockcollection_db',
  423. ];
  424. // Get the name of the chado schema.
  425. $chado_schema = chado_get_schema_name('chado');
  426. // Iterate through the new Chado tables and create them or if they already
  427. // exist then update them.
  428. foreach ($new_tables as $table) {
  429. // Get the schema for this table.
  430. $function = 'tripal_chado_chado_schema_v1_3_' . $table;
  431. $schema = $function();
  432. // If the table exists then fix the pkeys and fkeys.
  433. if (chado_table_exists($table)) {
  434. // Update the primary key fields to be bigints.
  435. $fields = $schema['fields'];
  436. foreach ($fields as $field_name => $field) {
  437. if ($field['type'] == 'serial') {
  438. if (chado_column_exists($table, $field_name)) {
  439. $sql = 'ALTER TABLE {' . $table . '} ALTER COLUMN ' . $field_name . ' TYPE bigint';
  440. chado_query($sql);
  441. }
  442. else {
  443. throw new Exception('Could not alter primary key to bigint: ' . $table . '.' . $field_name);
  444. }
  445. }
  446. }
  447. // Update the foreign key fields to be bigints.
  448. $fkeys = $schema['foreign keys'];
  449. foreach ($fkeys as $fktable => $details) {
  450. foreach ($details['columns'] as $leftkey => $rightkey) {
  451. if (chado_column_exists($table, $leftkey)) {
  452. $sql = 'ALTER TABLE {' . $table . '} ALTER COLUMN ' . $leftkey . ' TYPE bigint';
  453. chado_query($sql);
  454. }
  455. else {
  456. throw new Exception('Could not alter foreign key to bigint: ' . $table . '.' . $leftkey);
  457. }
  458. }
  459. }
  460. }
  461. }
  462. }
  463. /**
  464. * Upgrades Chado from v1.11 to v1.2
  465. */
  466. function tripal_chado_upgrade_chado_1_11_to_1_2() {
  467. // Get the path to the schema diff and upgarde SQL files.
  468. $schema_file = drupal_get_path('module', 'tripal_chado') . '/chado_schema/default_schema-1.11-1.2-diff.sql';
  469. $init_file = drupal_get_path('module', 'tripal_chado') . '/chado_schema/upgrade-1.11-1.2.sql';
  470. $success = tripal_chado_install_sql($schema_file);
  471. if ($success) {
  472. print "Upgrade from v1.11 to v1.2 (Step 1 of 2) Successful!\n";
  473. }
  474. else {
  475. throw new Exception("Upgrade (Step 1 of 2) problems! Please check output above for errors.");
  476. }
  477. $success = tripal_chado_install_sql($init_file);
  478. if ($success) {
  479. print "Upgrade from v1.11 to v1.2 (Step 2 of 2) Successful.\nUpgrade Complete!\n";
  480. }
  481. else {
  482. throw new Exception("Upgrade (Step 2 of 2) problems! Please check output above for errors.");
  483. }
  484. }
  485. /**
  486. * Reset the Chado Schema
  487. * This drops the current chado and chado-related schema and re-creates it
  488. *
  489. * @ingroup tripal_chado
  490. */
  491. function tripal_chado_reset_chado_schema() {
  492. // determine the schema name.
  493. $chado_schema = chado_get_schema_name('chado');
  494. $chado_dot = $chado_schema . '.';
  495. // drop current chado and chado-related schema
  496. if (chado_dbschema_exists('genetic_code')) {
  497. print "Dropping existing 'genetic_code' schema\n";
  498. db_query("drop schema genetic_code cascade");
  499. }
  500. if (chado_dbschema_exists('so')) {
  501. print "Dropping existing 'so' schema\n";
  502. db_query("drop schema so cascade");
  503. }
  504. if (chado_dbschema_exists('frange')) {
  505. print "Dropping existing 'frange' schema\n";
  506. db_query("drop schema frange cascade");
  507. }
  508. if (chado_dbschema_exists($chado_schema)) {
  509. if ($chado_schema != 'chado') {
  510. print "Dropping existing Chado ('$chado_schema') schema\n";
  511. }
  512. else {
  513. print "Dropping existing 'chado' schema\n";
  514. }
  515. db_query("drop schema $chado_schema cascade");
  516. }
  517. // create the new chado schema
  518. print "Creating 'chado' schema\n";
  519. db_query("create schema $chado_schema");
  520. if (chado_dbschema_exists('chado')) {
  521. // before creating the plpgsql language let's check to make sure
  522. // it doesn't already exists
  523. $sql = "SELECT COUNT(*) FROM pg_language WHERE lanname = 'plpgsql'";
  524. $results = db_query($sql);
  525. $count = $results->fetchObject();
  526. if (!$count or $count->count == 0) {
  527. db_query("create language plpgsql");
  528. }
  529. return TRUE;
  530. }
  531. return FALSE;
  532. }
  533. /**
  534. * Execute the provided SQL
  535. *
  536. * @param $sql_file
  537. * Contains SQL statements to be executed
  538. *
  539. * @ingroup tripal_chado
  540. */
  541. function tripal_chado_install_sql($sql_file) {
  542. $chado_local = chado_dbschema_exists('chado');
  543. // determine the schema name.
  544. $chado_schema = chado_get_schema_name('chado');
  545. $chado_dot = $chado_schema . '.';
  546. if ($chado_local) {
  547. db_query("set search_path to $chado_schema");
  548. }
  549. print "Loading $sql_file...\n";
  550. $lines = file($sql_file, FILE_SKIP_EMPTY_LINES);
  551. if (!$lines) {
  552. return 'Cannot open $schema_file';
  553. }
  554. $stack = [];
  555. $in_string = 0;
  556. $in_function = FALSE;
  557. $query = '';
  558. $i = 0;
  559. $success = 1;
  560. foreach ($lines as $line_num => $line) {
  561. $i++;
  562. $type = '';
  563. // find and remove comments except when inside of strings
  564. if (preg_match('/--/', $line) and !$in_string and !preg_match("/'.*?--.*?'/", $line)) {
  565. $line = preg_replace('/--.*$/', '', $line); // remove comments
  566. }
  567. if (preg_match('/\/\*.*?\*\//', $line)) {
  568. $line = preg_replace('/\/\*.*?\*\//', '', $line); // remove comments
  569. }
  570. // skip empty lines
  571. if (preg_match('/^\s*$/', $line) or strcmp($line, '') == 0) {
  572. continue;
  573. }
  574. // Find SQL for new objects
  575. if (preg_match('/^\s*CREATE\s+TABLE/i', $line) and !$in_string and !$in_function) {
  576. $stack[] = 'table';
  577. $line = preg_replace("/public\./", $chado_dot, $line);
  578. }
  579. if (preg_match('/^\s*ALTER\s+TABLE\s+/i', $line) and !$in_string and !$in_function) {
  580. $stack[] = 'alter_table';
  581. $line = preg_replace("/public\./", $chado_dot, $line);
  582. }
  583. if (preg_match('/^\s*SET/i', $line) and !$in_string and !$in_function) {
  584. $stack[] = 'set';
  585. }
  586. if (preg_match('/^\s*CREATE\s+SCHEMA/i', $line) and !$in_string and !$in_function) {
  587. $stack[] = 'schema';
  588. }
  589. if (preg_match('/^\s*CREATE\s+SEQUENCE/i', $line) and !$in_string and !$in_function) {
  590. $stack[] = 'sequence';
  591. $line = preg_replace("/public\./", $chado_dot, $line);
  592. }
  593. if (preg_match('/^\s*CREATE\s+(?:OR\s+REPLACE\s+)*VIEW/i', $line) and !$in_string and !$in_function) {
  594. $stack[] = 'view';
  595. $line = preg_replace("/public\./", $chado_dot, $line);
  596. }
  597. if (preg_match('/^\s*COMMENT/i', $line) and !$in_string and sizeof($stack) == 0 and !$in_function) {
  598. $stack[] = 'comment';
  599. $line = preg_replace("/public\./", $chado_dot, $line);
  600. }
  601. if (preg_match('/^\s*CREATE\s+(?:OR\s+REPLACE\s+)*FUNCTION/i', $line) and !$in_string and !$in_function) {
  602. $in_function = TRUE;
  603. $stack[] = 'function';
  604. $line = preg_replace("/public\./", $chado_dot, $line);
  605. }
  606. if (preg_match('/^\s*CREATE\s+INDEX/i', $line) and !$in_string and !$in_function) {
  607. $stack[] = 'index';
  608. }
  609. if (preg_match('/^\s*INSERT\s+INTO/i', $line) and !$in_string and !$in_function) {
  610. $stack[] = 'insert';
  611. $line = preg_replace("/public\./", $chado_dot, $line);
  612. }
  613. if (preg_match('/^\s*CREATE\s+TYPE/i', $line) and !$in_string and !$in_function) {
  614. $stack[] = 'type';
  615. }
  616. if (preg_match('/^\s*GRANT/i', $line) and !$in_string and !$in_function) {
  617. $stack[] = 'grant';
  618. }
  619. if (preg_match('/^\s*CREATE\s+AGGREGATE/i', $line) and !$in_string and !$in_function) {
  620. $stack[] = 'aggregate';
  621. }
  622. if (preg_match('/^\s*DROP\s+FUNCTION/i', $line) and !$in_string and !$in_function) {
  623. $stack[] = 'drop_function';
  624. }
  625. if (preg_match('/^\s*DROP\s+VIEW/i', $line) and !$in_string and !$in_function) {
  626. $stack[] = 'drop_view';
  627. }
  628. if (preg_match('/^\s*DROP\s+INDEX/i', $line) and !$in_string and !$in_function) {
  629. $stack[] = 'drop_index';
  630. }
  631. if (preg_match('/^\s*DROP\s+SEQUENCE/i', $line) and !$in_string and !$in_function) {
  632. $stack[] = 'drop_seq';
  633. }
  634. if (preg_match('/^\s*ALTER\s+TYPE\s+/i', $line) and !$in_string and !$in_function) {
  635. $stack[] = 'alter_type';
  636. }
  637. if (preg_match('/^\s*ALTER\s+SEQUENCE\s+/i', $line) and !$in_string and !$in_function) {
  638. $stack[] = 'alter_seq';
  639. }
  640. // determine if we are in a string that spans a line
  641. $matches = preg_match_all("/[']/i", $line, $temp);
  642. $in_string = $in_string - ($matches % 2);
  643. $in_string = abs($in_string);
  644. // if we've reached the end of an object then pop the stack
  645. if (strcmp($stack[sizeof($stack) - 1], 'table') == 0 and preg_match('/\);\s*$/', $line)) {
  646. $type = array_pop($stack);
  647. }
  648. elseif (strcmp($stack[sizeof($stack) - 1], 'alter_table') == 0 and preg_match('/;\s*$/', $line)) {
  649. $type = array_pop($stack);
  650. }
  651. elseif (strcmp($stack[sizeof($stack) - 1], 'set') == 0 and preg_match('/;\s*$/', $line) and !$in_string) {
  652. $type = array_pop($stack);
  653. }
  654. elseif (strcmp($stack[sizeof($stack) - 1], 'schema') == 0 and preg_match('/;\s*$/', $line) and !$in_string) {
  655. $type = array_pop($stack);
  656. }
  657. elseif (strcmp($stack[sizeof($stack) - 1], 'sequence') == 0 and preg_match('/;\s*$/', $line) and !$in_string) {
  658. $type = array_pop($stack);
  659. }
  660. elseif (strcmp($stack[sizeof($stack) - 1], 'view') == 0 and preg_match('/;\s*$/', $line) and !$in_string) {
  661. $type = array_pop($stack);
  662. }
  663. elseif (strcmp($stack[sizeof($stack) - 1], 'comment') == 0 and preg_match('/;\s*$/', $line) and !$in_string) {
  664. $type = array_pop($stack);
  665. }
  666. elseif (strcmp($stack[sizeof($stack) - 1], 'function') == 0) {
  667. if (preg_match('/LANGUAGE.*?;\s*$/i', $line)) {
  668. $type = array_pop($stack);
  669. $in_function = FALSE;
  670. //print "FUNCTION DONE ($i): $line";
  671. }
  672. else {
  673. if (preg_match('/\$_\$;\s*$/i', $line)) {
  674. $type = array_pop($stack);
  675. $in_function = FALSE;
  676. //print "FUNCTION DONE ($i): $line";
  677. }
  678. else {
  679. if (preg_match('/\$\$;\s*$/i', $line)) {
  680. $type = array_pop($stack);
  681. $in_function = FALSE;
  682. // print "FUNCTION DONE ($i): $line";
  683. }
  684. else {
  685. // print "FUNCTION ($i): $line";
  686. }
  687. }
  688. }
  689. }
  690. elseif (strcmp($stack[sizeof($stack) - 1], 'index') == 0 and preg_match('/;\s*$/', $line) and !$in_string) {
  691. $type = array_pop($stack);
  692. }
  693. elseif (strcmp($stack[sizeof($stack) - 1], 'insert') == 0 and preg_match('/\);\s*$/', $line)) {
  694. $type = array_pop($stack);
  695. }
  696. elseif (strcmp($stack[sizeof($stack) - 1], 'type') == 0 and preg_match('/\);\s*$/', $line)) {
  697. $type = array_pop($stack);
  698. }
  699. elseif (strcmp($stack[sizeof($stack) - 1], 'grant') == 0 and preg_match('/;\s*$/', $line) and !$in_string) {
  700. $type = array_pop($stack);
  701. }
  702. elseif (strcmp($stack[sizeof($stack) - 1], 'aggregate') == 0 and preg_match('/\);\s*$/', $line)) {
  703. $type = array_pop($stack);
  704. }
  705. elseif (strcmp($stack[sizeof($stack) - 1], 'drop_function') == 0 and preg_match('/;\s*$/i', $line)) {
  706. $type = array_pop($stack);
  707. }
  708. elseif (strcmp($stack[sizeof($stack) - 1], 'drop_view') == 0 and preg_match('/;\s*$/i', $line)) {
  709. $type = array_pop($stack);
  710. }
  711. elseif (strcmp($stack[sizeof($stack) - 1], 'drop_index') == 0 and preg_match("/;\s*$/i", $line)) {
  712. $type = array_pop($stack);
  713. }
  714. elseif (strcmp($stack[sizeof($stack) - 1], 'drop_seq') == 0 and preg_match("/;\s*$/i", $line)) {
  715. $type = array_pop($stack);
  716. }
  717. elseif (strcmp($stack[sizeof($stack) - 1], 'alter_type') == 0 and preg_match('/;\s*$/i', $line)) {
  718. $type = array_pop($stack);
  719. }
  720. elseif (strcmp($stack[sizeof($stack) - 1], 'alter_seq') == 0 and preg_match('/;\s*$/i', $line)) {
  721. $type = array_pop($stack);
  722. }
  723. // if we're in a recognized SQL statement then let's keep track of lines
  724. if ($type or sizeof($stack) > 0) {
  725. $query .= "$line";
  726. }
  727. else {
  728. throw new Exception("UNHANDLED $i, $in_string: $line");
  729. }
  730. if (preg_match_all("/\n/", $query, $temp) > 1000) {
  731. throw new Exception("SQL query is too long. Terminating:\n$query\n");
  732. }
  733. if ($type and sizeof($stack) == 0) {
  734. //print "Adding $type: line $i\n";
  735. // rewrite the set search_path to make 'public' be 'chado', but only if the
  736. // chado schema exists
  737. if (strcmp($type, 'set') == 0 and $chado_local) {
  738. $query = preg_replace("/public/m", $chado_schema, $query);
  739. }
  740. // execute the statement
  741. try {
  742. $result = db_query($query);
  743. } catch (Exception $e) {
  744. $error = $e->getMessage();
  745. throw new Exception("FAILED. Line $i, $in_string\n$error:\n$query\n\n");
  746. }
  747. if (!$result) {
  748. $error = pg_last_error();
  749. throw new Exception("FAILED. Line $i, $in_string\n$error:\n$query\n\n");
  750. }
  751. $query = '';
  752. }
  753. }
  754. tripal_chado_install_done();
  755. return $success;
  756. }
  757. /**
  758. * Finish the Chado Schema Installation
  759. *
  760. * @ingroup tripal_chado
  761. */
  762. function tripal_chado_install_done() {
  763. $drupal_schema = chado_get_schema_name('drupal');
  764. db_query("set search_path to $drupal_schema");
  765. }