Tripal Plant PopGen Submit
/home/docs/checkouts/readthedocs.org/user_builds/tpps/checkouts/latest/tpps.install
Go to the documentation of this file.
1 <?php
2 
14 function tpps_install() {
15 
17 
19 
21 
23 
24  chado_create_custom_table('genotype_call', array(
25  'description' => '',
26  'fields' => array(
27  'genotype_call_id' => array(
28  'description' => '',
29  'type' => 'serial',
30  'size' => 'normal',
31  'unsigned' => TRUE,
32  'not null' => TRUE,
33  ),
34  'variant_id' => array(
35  'description' => '',
36  'type' => 'int',
37  'size' => 'big',
38  'not null' => TRUE,
39  ),
40  'marker_id' => array(
41  'description' => '',
42  'type' => 'int',
43  'size' => 'big',
44  'not null' => TRUE,
45  ),
46  'genotype_id' => array(
47  'description' => '',
48  'type' => 'int',
49  'size' => 'big',
50  'not null' => TRUE,
51  ),
52  'project_id' => array(
53  'description' => '',
54  'type' => 'int',
55  'size' => 'big',
56  'not null' => TRUE,
57  ),
58  'stock_id' => array(
59  'description' => '',
60  'type' => 'int',
61  'size' => 'big',
62  'not null' => TRUE,
63  ),
64  'meta_data' => array(
65  'description' => '',
66  'type' => 'json',
67  ),
68  ),
69  'foreign keys' => array(
70  'genotype' => array(
71  'table' => 'genotype',
72  'columns' => array(
73  'genotype_id' => 'genotype_id',
74  ),
75  ),
76  'variant' => array(
77  'table' => 'feature',
78  'columns' => array(
79  'variant_id' => 'feature_id',
80  ),
81  ),
82  'marker' => array(
83  'table' => 'feature',
84  'columns' => array(
85  'marker_id' => 'feature_id',
86  ),
87  ),
88  'project' => array(
89  'table' => 'project',
90  'columns' => array(
91  'project_id' => 'project_id',
92  ),
93  ),
94  'stock' => array(
95  'table' => 'stock',
96  'columns' => array(
97  'stock_id' => 'stock_id',
98  ),
99  ),
100  ),
101  'primary key' => array(
102  'genotype_call_id',
103  ),
104  'referring_tables' => array(
105  'genotype',
106  'feature',
107  'project',
108  'stock',
109  ),
110  ), TRUE, NULL, FALSE);
111 
113 
114  chado_create_custom_table('stock_phenotype', array(
115  'description' => '',
116  'fields' => array(
117  'phenotype_id' => array(
118  'description' => '',
119  'type' => 'int',
120  'size' => 'normal',
121  'not null' => TRUE,
122  ),
123  'stock_id' => array(
124  'description' => '',
125  'type' => 'int',
126  'size' => 'normal',
127  'not null' => TRUE,
128  ),
129  ),
130  'unique keys' => array(
131  'stock_phenotype_c1' => array(
132  'stock_id',
133  'phenotype_id',
134  ),
135  ),
136  'foreign keys' => array(
137  'phenotype' => array(
138  'table' => 'phenotype',
139  'columns' => array(
140  'phenotype_id' => 'phenotype_id',
141  ),
142  ),
143  'stock' => array(
144  'table' => 'stock',
145  'columns' => array(
146  'stock_id' => 'stock_id',
147  ),
148  ),
149  ),
150  'primary key' => array(
151  'phenotype_id',
152  'stock_id',
153  ),
154  'referring_tables' => array(
155  'phenotype',
156  'stock',
157  ),
158  'indexes' => array(
159  'stock_phenotype_idx1' => array(
160  'phenotype_id',
161  ),
162  'stock_phenotype_idx2' => array(
163  'stock_id',
164  ),
165  ),
166  ), TRUE, NULL, FALSE);
167 
168  chado_create_custom_table('project_organism', array(
169  'description' => '',
170  'fields' => array(
171  'project_organism_id' => array(
172  'description' => '',
173  'type' => 'serial',
174  'size' => 'normal',
175  'unsigned' => TRUE,
176  'not null' => TRUE,
177  ),
178  'organism_id' => array(
179  'description' => '',
180  'type' => 'int',
181  'size' => 'big',
182  'not null' => TRUE,
183  ),
184  'project_id' => array(
185  'description' => '',
186  'type' => 'int',
187  'size' => 'big',
188  'not null' => TRUE,
189  ),
190  ),
191  'foreign keys' => array(
192  'organism' => array(
193  'table' => 'organism',
194  'columns' => array(
195  'organism_id' => 'organism_id',
196  ),
197  ),
198  'project' => array(
199  'table' => 'project',
200  'columns' => array(
201  'project_id' => 'project_id',
202  ),
203  ),
204  ),
205  'unique keys' => array(
206  'project_organism_c1' => array(
207  'project_id',
208  'organism_id',
209  ),
210  ),
211  'primary key' => array(
212  'project_organism_id',
213  ),
214  ), TRUE, NULL, FALSE);
215 
216  chado_create_custom_table('pub_organism', array(
217  'description' => '',
218  'fields' => array(
219  'pub_organism_id' => array(
220  'description' => '',
221  'type' => 'serial',
222  'size' => 'normal',
223  'unsigned' => TRUE,
224  'not null' => TRUE,
225  ),
226  'organism_id' => array(
227  'description' => '',
228  'type' => 'int',
229  'size' => 'big',
230  'not null' => TRUE,
231  ),
232  'pub_id' => array(
233  'description' => '',
234  'type' => 'int',
235  'size' => 'big',
236  'not null' => TRUE,
237  ),
238  ),
239  'foreign keys' => array(
240  'organism' => array(
241  'table' => 'organism',
242  'columns' => array(
243  'organism_id' => 'organism_id',
244  ),
245  ),
246  'pub' => array(
247  'table' => 'pub',
248  'columns' => array(
249  'pub_id' => 'pub_id',
250  ),
251  ),
252  ),
253  'unique keys' => array(
254  'pub_organism_c1' => array(
255  'pub_id',
256  'organism_id',
257  ),
258  ),
259  'primary key' => array(
260  'pub_organism_id',
261  ),
262  ), TRUE, NULL, FALSE);
263 
265 
267 
269 
271 
273 
275 
277 
278  tpps_add_dryad();
279 }
280 
287 function tpps_uninstall() {
288 
289  // Remove all incomplete submissions connected to user profiles.
290  db_delete('public.variable')
291  ->condition('name', db_like('tpps_incomplete_') . '%', 'LIKE')
292  ->execute();
293 
294  // Remove all complete submissions connected to user profiles.
295  db_delete('public.variable')
296  ->condition('name', db_like('tpps_complete_') . '%', 'LIKE')
297  ->execute();
298 
299  $tpps_usage = db_select('public.file_usage', 'f')
300  ->fields('f')
301  ->condition('module', 'tpps')
302  ->execute();
303 
304  foreach ($tpps_usage as $row) {
305  file_usage_delete(file_load($row->fid), $row->module, $row->type, $row->id, $row->count);
306  }
307 }
308 
315 function tpps_schema() {
316  $schema = array();
317 
318  if (!db_table_exists('tpps_submission')) {
319  $schema['tpps_submission'] = array(
320  'description' => 'Contains TPPS Submissions',
321  'fields' => array(
322  'tpps_submission_id' => array(
323  'description' => 'The id of the TPPS submission.',
324  'type' => 'serial',
325  'size' => 'normal',
326  'unsigned' => TRUE,
327  'not null' => TRUE,
328  ),
329  'uid' => array(
330  'description' => 'user id of the user who created the submission.',
331  'type' => 'int',
332  'size' => 'big',
333  'not null' => TRUE,
334  ),
335  'status' => array(
336  'description' => 'Status of the submission - Incomplete, Pending Approval, Submission Job Running or Approved.',
337  'type' => 'varchar',
338  'size' => 'normal',
339  'not null' => TRUE,
340  ),
341  'accession' => array(
342  'description' => 'The TGDR### accession number of the submission',
343  'type' => 'varchar',
344  'size' => 'normal',
345  'not null' => TRUE,
346  ),
347  'dbxref_id' => array(
348  'description' => 'The dbxref_id from chado.dbxref.',
349  'type' => 'int',
350  'size' => 'big',
351  'not null' => TRUE,
352  ),
353  'submission_state' => array(
354  'description' => 'The form_state of the submission.',
355  'type' => 'blob',
356  'size' => 'big',
357  'not null' => TRUE,
358  ),
359  ),
360  'foreign keys' => array(
361  'uid' => array(
362  'table' => 'users',
363  'columns' => array(
364  'uid' => 'uid',
365  ),
366  ),
367  'dbxref_id' => array(
368  'table' => 'chado.dbxref',
369  'columns' => array(
370  'dbxref_id' => 'dbxref_id',
371  ),
372  ),
373  ),
374  'primary key' => array(
375  'tpps_submission_id',
376  ),
377  );
378  }
379 
380  if (!db_table_exists('chado.genotype_call')) {
381  $schema['chado.genotype_call'] = array(
382  'description' => '',
383  'fields' => array(
384  'genotype_call_id' => array(
385  'description' => '',
386  'type' => 'serial',
387  'size' => 'normal',
388  'unsigned' => TRUE,
389  'not null' => TRUE,
390  ),
391  'variant_id' => array(
392  'description' => '',
393  'type' => 'int',
394  'size' => 'big',
395  'not null' => TRUE,
396  ),
397  'marker_id' => array(
398  'description' => '',
399  'type' => 'int',
400  'size' => 'big',
401  'not null' => TRUE,
402  ),
403  'genotype_id' => array(
404  'description' => '',
405  'type' => 'int',
406  'size' => 'big',
407  'not null' => TRUE,
408  ),
409  'project_id' => array(
410  'description' => '',
411  'type' => 'int',
412  'size' => 'big',
413  'not null' => TRUE,
414  ),
415  'stock_id' => array(
416  'description' => '',
417  'type' => 'int',
418  'size' => 'big',
419  'not null' => TRUE,
420  ),
421  'meta_data' => array(
422  'description' => '',
423  // Drupal does not support the json data type by default, we will use
424  // a placeholder for meta_data for now, and upon install we will
425  // change the type to json.
426  'type' => 'text',
427  ),
428  ),
429  'foreign keys' => array(
430  'genotype_id' => array(
431  'table' => 'chado.genotype',
432  'columns' => array(
433  'genotype_id' => 'genotype_id',
434  ),
435  ),
436  'variant_id' => array(
437  'table' => 'chado.feature',
438  'columns' => array(
439  'variant_id' => 'feature_id',
440  ),
441  ),
442  'marker_id' => array(
443  'table' => 'chado.feature',
444  'columns' => array(
445  'marker_id' => 'feature_id',
446  ),
447  ),
448  'project_id' => array(
449  'table' => 'chado.project',
450  'columns' => array(
451  'project_id' => 'project_id',
452  ),
453  ),
454  'stock_id' => array(
455  'table' => 'chado.stock',
456  'columns' => array(
457  'stock_id' => 'stock_id',
458  ),
459  ),
460  ),
461  'primary key' => array(
462  'genotype_call_id',
463  ),
464  );
465  }
466 
467  $schema['tpps_submission_dbxref'] = array(
468  'description' => '',
469  'fields' => array(
470  'tpps_submission_dbxref_id' => array(
471  'description' => '',
472  'type' => 'serial',
473  'size' => 'normal',
474  'unsigned' => TRUE,
475  'not null' => TRUE,
476  ),
477  'tpps_submission_id' => array(
478  'description' => '',
479  'type' => 'int',
480  'size' => 'big',
481  'not null' => TRUE,
482  ),
483  'dbxref_id' => array(
484  'description' => '',
485  'type' => 'int',
486  'size' => 'big',
487  'not null' => TRUE,
488  ),
489  ),
490  'foreign keys' => array(
491  'tpps_submission_id' => array(
492  'table' => 'tpps_submission',
493  'columns' => array(
494  'tpps_submission_id' => 'tpps_submission_id',
495  ),
496  ),
497  'dbxref_id' => array(
498  'table' => 'chado.dbxref',
499  'columns' => array(
500  'dbxref_id' => 'dbxref_id',
501  ),
502  ),
503  ),
504  'unique keys' => array(
505  'tpps_submission_dbxref_c1' => array(
506  'tpps_submission_id',
507  'dbxref_id',
508  ),
509  ),
510  'primary key' => array(
511  'tpps_submission_dbxref_id',
512  ),
513  );
514 
515  $schema['tpps_project_file_managed'] = array(
516  'description' => '',
517  'fields' => array(
518  'tpps_project_file_managed_id' => array(
519  'description' => '',
520  'type' => 'serial',
521  'size' => 'normal',
522  'unsigned' => TRUE,
523  'not null' => TRUE,
524  ),
525  'project_id' => array(
526  'description' => '',
527  'type' => 'int',
528  'size' => 'big',
529  'not null' => TRUE,
530  ),
531  'fid' => array(
532  'description' => '',
533  'type' => 'int',
534  'size' => 'big',
535  'not null' => TRUE,
536  ),
537  ),
538  'foreign keys' => array(
539  'project_id' => array(
540  'table' => 'chado.project',
541  'columns' => array(
542  'project_id' => 'project_id',
543  ),
544  ),
545  'fid' => array(
546  'table' => 'file_managed',
547  'columns' => array(
548  'fid' => 'fid',
549  ),
550  ),
551  ),
552  'unique keys' => array(
553  'tpps_project_file_managed_c1' => array(
554  'project_id',
555  'fid',
556  ),
557  ),
558  'primary key' => array(
559  'tpps_project_file_managed_id',
560  ),
561  );
562 
563  $schema['tpps_tag'] = array(
564  'description' => '',
565  'fields' => array(
566  'tpps_tag_id' => array(
567  'description' => '',
568  'type' => 'serial',
569  'size' => 'normal',
570  'unsigned' => TRUE,
571  'not null' => TRUE,
572  ),
573  'name' => array(
574  'description' => '',
575  'type' => 'varchar',
576  'size' => 'normal',
577  'not null' => TRUE,
578  ),
579  'color' => array(
580  'description' => '',
581  'type' => 'varchar',
582  'size' => 'normal',
583  ),
584  'static' => array(
585  'description' => '',
586  'type' => 'int',
587  'size' => 'tiny',
588  'not null' => TRUE,
589  'default' => 0,
590  ),
591  ),
592  'unique keys' => array(
593  'tpps_tag_c1' => array(
594  'name',
595  ),
596  ),
597  'primary key' => array(
598  'tpps_tag_id',
599  ),
600  );
601 
602  $schema['tpps_submission_tag'] = array(
603  'description' => '',
604  'fields' => array(
605  'tpps_submission_tag_id' => array(
606  'description' => '',
607  'type' => 'serial',
608  'size' => 'normal',
609  'unsigned' => TRUE,
610  'not null' => TRUE,
611  ),
612  'tpps_submission_id' => array(
613  'description' => '',
614  'type' => 'int',
615  'size' => 'big',
616  'not null' => TRUE,
617  ),
618  'tpps_tag_id' => array(
619  'description' => '',
620  'type' => 'int',
621  'size' => 'big',
622  'not null' => TRUE,
623  ),
624  ),
625  'foreign keys' => array(
626  'tpps_submission_id' => array(
627  'table' => 'tpps_submission',
628  'columns' => array(
629  'tpps_submission_id' => 'tpps_submission_id',
630  ),
631  ),
632  'tpps_tag_id' => array(
633  'table' => 'tpps_tag',
634  'columns' => array(
635  'tpps_tag_id' => 'tpps_tag_id',
636  ),
637  ),
638  ),
639  'unique keys' => array(
640  'tpps_submission_tag_c1' => array(
641  'tpps_submission_id',
642  'tpps_tag_id',
643  ),
644  ),
645  'primary key' => array(
646  'tpps_submission_tag_id',
647  ),
648  );
649 
650  return $schema;
651 }
652 
660  module_load_include('inc', 'tripal_chado', 'includes/tripal_chado.cv');
661  $transaction = db_transaction();
662 
663  $file_path = DRUPAL_ROOT . '/' . drupal_get_path('module', 'tpps') . '/includes/lib/ppeo.obo';
664 
665  try {
666  $obo_id = db_insert('tripal_cv_obo')
667  ->fields(array(
668  'name' => 'PPEO',
669  'path' => $file_path,
670  ))
671  ->execute();
672  tripal_cv_load_obo($obo_id);
673  }
674  catch (\Exception $e) {
675  $transaction->rollback();
676  throw $e;
677  }
678 }
679 
684  $terms_function = "tpps_get_cvterms_v" . TPPS_MAJOR_VERSION . "_" . TPPS_MINOR_VERSION;
685  $terms = $terms_function();
686 
687  $transaction = db_transaction();
688 
689  try {
690  foreach ($terms as $key => $info) {
691  if (empty($info)) {
692  continue;
693  }
694 
695  if ($key == 'new_terms' and is_array($info)) {
696  foreach ($info as $term_info) {
697  tpps_ols_install_term($term_info);
698  }
699  continue;
700  }
701 
702  $result = tpps_ols_install_term($info);
703  if ($result === 'local') {
705  }
706  }
707  variable_set('tpps_cvterms_version', TPPS_MAJOR_VERSION . '_' . TPPS_MINOR_VERSION);
708  }
709  catch (\Exception $e) {
710  $transaction->rollback();
711  throw $e;
712  }
713 }
714 
719  $results = db_query("SELECT contype "
720  . "FROM pg_catalog.pg_constraint con JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid "
721  . "WHERE rel.relname = 'genotype_call' AND contype like 'f';");
722  if (!$results->rowCount()) {
723  db_query('ALTER TABLE chado.genotype_call ADD CONSTRAINT genotype_call_genotype_id_fkey FOREIGN KEY (genotype_id) REFERENCES chado.genotype(genotype_id) ON DELETE CASCADE;');
724  db_query('ALTER TABLE chado.genotype_call ADD CONSTRAINT genotype_call_marker_id_fkey FOREIGN KEY (marker_id) REFERENCES chado.feature(feature_id) ON DELETE CASCADE;');
725  db_query('ALTER TABLE chado.genotype_call ADD CONSTRAINT genotype_call_variant_id_fkey FOREIGN KEY (variant_id) REFERENCES chado.feature(feature_id) ON DELETE CASCADE;');
726  db_query('ALTER TABLE chado.genotype_call ADD CONSTRAINT genotype_call_project_id_fkey FOREIGN KEY (project_id) REFERENCES chado.project(project_id) ON DELETE CASCADE;');
727  db_query('ALTER TABLE chado.genotype_call ADD CONSTRAINT genotype_call_stock_id_fkey FOREIGN KEY (stock_id) REFERENCES chado.stock(stock_id) ON DELETE CASCADE;');
728  }
729 
730  $and = db_and()
731  ->condition('column_name', 'meta_data')
732  ->condition('table_name', 'genotype_call');
733  $meta_type = db_select('information_schema.columns', 'c')
734  ->fields('c', array('data_type'))
735  ->condition($and)
736  ->execute()->fetchObject()->data_type;
737 
738  if ($meta_type != 'json') {
739  db_query('ALTER TABLE chado.genotype_call ALTER COLUMN meta_data TYPE json USING meta_data::json;');
740  }
741 }
742 
747  $results = db_query("SELECT contype "
748  . "FROM pg_catalog.pg_constraint con JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid "
749  . "WHERE rel.relname = 'tpps_submission' AND contype like 'f';");
750  if (!$results->rowCount()) {
751  // Create foreign key for tpps_submission to reference users table.
752  db_query('ALTER TABLE tpps_submission ADD CONSTRAINT tpps_submission_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid) ON DELETE CASCADE;');
753  db_query('ALTER TABLE tpps_submission ADD CONSTRAINT tpps_submission_dbxref_id_fkey FOREIGN KEY (dbxref_id) REFERENCES chado.dbxref(dbxref_id) ON DELETE CASCADE;');
754  }
755 }
756 
761  $results = db_query("SELECT contype "
762  . "FROM pg_catalog.pg_constraint con JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid "
763  . "WHERE rel.relname = 'tpps_submission_dbxref' AND contype like 'f';");
764  if (!$results->rowCount()) {
765  db_query('ALTER TABLE tpps_submission_dbxref ADD CONSTRAINT tpps_submission_dbxref_tpps_submission_id_fkey FOREIGN KEY (tpps_submission_id) REFERENCES tpps_submission(tpps_submission_id) ON DELETE CASCADE;');
766  db_query('ALTER TABLE tpps_submission_dbxref ADD CONSTRAINT tpps_submission_dbxref_dbxref_id_fkey FOREIGN KEY (dbxref_id) REFERENCES chado.dbxref(dbxref_id) ON DELETE CASCADE;');
767  }
768 }
769 
774  $results = db_query("SELECT contype "
775  . "FROM pg_catalog.pg_constraint con JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid "
776  . "WHERE rel.relname = 'tpps_project_file_managed' AND contype like 'f';");
777  if (!$results->rowCount()) {
778  db_query('ALTER TABLE tpps_project_file_managed ADD CONSTRAINT tpps_project_file_managed_fid_fkey FOREIGN KEY (fid) REFERENCES file_managed(fid) ON DELETE CASCADE;');
779  db_query('ALTER TABLE tpps_project_file_managed ADD CONSTRAINT tpps_project_file_managed_project_id_fkey FOREIGN KEY (project_id) REFERENCES chado.project(project_id) ON DELETE CASCADE');
780  }
781 }
782 
787  $results = db_query("SELECT contype "
788  . "FROM pg_catalog.pg_constraint con JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid "
789  . "WHERE rel.relname = 'tpps_submission_tag' AND contype like 'f';");
790  if (!$results->rowCount()) {
791  db_query('ALTER TABLE tpps_submission_tag ADD CONSTRAINT tpps_submission_tag_tpps_tag_id_fkey FOREIGN KEY (tpps_tag_id) REFERENCES tpps_tag(tpps_tag_id) ON DELETE CASCADE;');
792  db_query('ALTER TABLE tpps_submission_tag ADD CONSTRAINT tpps_submission_tag_tpps_submission_id_fkey FOREIGN KEY (tpps_submission_id) REFERENCES tpps_submission(tpps_submission_id) ON DELETE CASCADE;');
793  }
794 }
795 
800  $local_db = chado_get_db(array('name' => 'local'));
801 
802  $query = db_select('pg_class', 'p')
803  ->fields('p')
804  ->condition('relname', 'plusgeno_view')
805  ->execute();
806  if (!($result = $query->fetchObject())) {
807  // Create plusgeno_view if it does not exist.
808  db_query('create materialized view chado.plusgeno_view as SELECT DISTINCT p.project_id,
809  d.accession,
810  ppub.title,
811  o.species,
812  count(o.stock_id) OVER (PARTITION BY p.project_id) AS tree_count,
813  sum(phen.count) OVER (PARTITION BY p.project_id) AS phen_count,
814  unique_phen.phenotypes_assessed,
815  g.gen_count
816  FROM chado.project_dbxref p
817  JOIN chado.dbxref d ON p.dbxref_id = d.dbxref_id
818  JOIN ( SELECT pp.project_id,
819  pub.title
820  FROM chado.project_pub pp
821  JOIN chado.pub ON pp.pub_id = pub.pub_id) ppub ON p.project_id = ppub.project_id
822  JOIN ( SELECT DISTINCT ps.project_id,
823  (org.genus::text || \' \'::text) || org.species::text AS species,
824  stock.stock_id
825  FROM chado.project_stock ps
826  JOIN chado.stock ON ps.stock_id = stock.stock_id
827  JOIN chado.organism org ON stock.organism_id = org.organism_id) o ON p.project_id = o.project_id
828  LEFT JOIN ( SELECT stock_phenotype.stock_id,
829  count(stock_phenotype.phenotype_id) OVER (PARTITION BY stock_phenotype.stock_id) AS count
830  FROM chado.stock_phenotype) phen ON o.stock_id = phen.stock_id
831  LEFT JOIN ( SELECT DISTINCT pc.project_id,
832  count(pc.name) OVER (PARTITION BY pc.project_id) AS phenotypes_assessed
833  FROM ( SELECT DISTINCT ps.project_id,
834  ph.name
835  FROM chado.phenotype ph
836  JOIN chado.stock_phenotype sp ON ph.phenotype_id = sp.phenotype_id
837  JOIN chado.stock s ON sp.stock_id = s.stock_id
838  JOIN chado.project_stock ps ON s.stock_id = ps.stock_id) pc) unique_phen ON p.project_id = unique_phen.project_id
839  LEFT JOIN ( SELECT DISTINCT genotype_call.project_id,
840  count(genotype_call.genotype_call_id) OVER (PARTITION BY genotype_call.project_id) AS gen_count
841  FROM chado.genotype_call) g ON p.project_id = g.project_id
842  WHERE d.db_id = ' . $local_db->db_id . ';');
843  variable_set('tpps_local_db', $local_db);
844  }
845  else {
846  $matches = array();
847  $definition = db_select('pg_matviews', 'p')
848  ->fields('p', array('definition'))
849  ->condition('matviewname', 'plusgeno_view')
850  ->execute()
851  ->fetchObject()->definition;
852  preg_match('/d\.db_id ?= ?(\d+)\);/', $definition, $matches);
853  variable_set('tpps_local_db', chado_get_db(array('db_id' => (int) $matches[1])));
854  }
855 }
856 
861  $query = db_select('pg_class', 'p')
862  ->fields('p')
863  ->condition('relname', 'tpps_search_genotype_name')
864  ->execute();
865  if (!($result = $query->fetchObject())) {
866  db_query('create materialized view chado.tpps_search_genotype_name as
867  select distinct gc.project_id, g.name
868  from chado.genotype_call gc
869  inner join chado.genotype g
870  on (g.genotype_id = gc.genotype_id);');
871  }
872 
873  $query = db_select('pg_class', 'p')
874  ->fields('p')
875  ->condition('relname', 'tpps_search_genotype_marker')
876  ->execute();
877  if (!($result = $query->fetchObject())) {
878  db_query('create materialized view chado.tpps_search_genotype_marker as
879  select distinct gc.project_id, cvt.name
880  from chado.genotype_call gc
881  inner join chado.genotype g
882  on (g.genotype_id = gc.genotype_id)
883  inner join chado.cvterm cvt
884  on (cvt.cvterm_id = g.type_id);');
885  }
886 }
887 
892  $admin_query = db_select('role', 'r')
893  ->fields('r', array('rid'))
894  ->condition('name', 'administrator')
895  ->execute();
896 
897  $auth_query = db_select('role', 'r')
898  ->fields('r', array('rid'))
899  ->condition('name', 'authenticated user')
900  ->execute();
901 
902  $anon_query = db_select('role', 'r')
903  ->fields('r', array('rid'))
904  ->condition('name', 'anonymous user')
905  ->execute();
906 
907  if (($admin = $admin_query->fetchObject()) and ($auth = $auth_query->fetchObject()) and ($anon = $anon_query->fetchObject())) {
908  $perms = tpps_permission();
909  foreach ($perms as $name => $info) {
910  db_insert('role_permission')
911  ->fields(array(
912  'rid' => $admin->rid,
913  'permission' => $name,
914  'module' => 'tpps',
915  ))
916  ->execute();
917  }
918 
919  db_insert('role_permission')
920  ->fields(array(
921  'rid' => $auth->rid,
922  'permission' => 'access tpps form',
923  'module' => 'tpps',
924  ))
925  ->execute();
926 
927  db_insert('role_permission')
928  ->fields(array(
929  'rid' => $auth->rid,
930  'permission' => 'user tpps submissions',
931  'module' => 'tpps',
932  ))
933  ->execute();
934 
935  db_insert('role_permission')
936  ->fields(array(
937  'rid' => $auth->rid,
938  'permission' => 'tpps delete submission',
939  'module' => 'tpps',
940  ))
941  ->execute();
942 
943  db_insert('role_permission')
944  ->fields(array(
945  'rid' => $auth->rid,
946  'permission' => 'view own tpps submission',
947  'module' => 'tpps',
948  ))
949  ->execute();
950 
951  db_insert('role_permission')
952  ->fields(array(
953  'rid' => $auth->rid,
954  'permission' => 'access tpps details',
955  'module' => 'tpps',
956  ))
957  ->execute();
958 
959  db_insert('role_permission')
960  ->fields(array(
961  'rid' => $anon->rid,
962  'permission' => 'access tpps details',
963  'module' => 'tpps',
964  ))
965  ->execute();
966 
967  drupal_set_message(t('Attention: @t default permissions have been set. Go to @p to manage and customize @t permissions.', array(
968  '@t' => 'TPPS',
969  '@p' => 'admin/people/permissions',
970  )), 'status');
971  }
972 }
973 
977 function tpps_default_tags() {
978  $tags = array(
979  'TPPS' => '',
980  'TPPSc' => 'lightgray',
981  'Genotype' => 'lightgreen',
982  'Phenotype' => 'lightblue',
983  'Environment' => 'lightsalmon',
984  'No Location Information' => 'pink',
985  'Approximate Coordinates' => 'gray',
986  );
987 
988  foreach ($tags as $name => $color) {
989  db_insert('tpps_tag')
990  ->fields(array(
991  'name' => $name,
992  'color' => $color,
993  'static' => 1,
994  ))
995  ->execute();
996  }
997 }
998 
1003  $transaction = db_transaction();
1004  try {
1005  if (empty(tripal_load_bundle_entity(array('label' => 'Genotype')))) {
1007  }
1008 
1009  if (empty(tripal_load_bundle_entity(array('label' => 'Stock')))) {
1011  }
1012  }
1013  catch (Exception $e) {
1014  watchdog_exception('tpps', $e);
1015  $transaction->rollback();
1016  throw $e;
1017  }
1018 
1019 }
1020 
1025  $local_db = variable_get('tpps_local_db');
1026  $local_cv = chado_get_cv(array('name' => 'local'));
1027  $genotype_term = tpps_load_cvterm('genotype', array(
1028  'include_fk' => array(
1029  'dbxref_id' => 1,
1030  ),
1031  ));
1032 
1033  $args = array(
1034  'vocabulary' => $genotype_term->dbxref_id->db_id->name,
1035  'accession' => $genotype_term->dbxref_id->accession,
1036  'term_name' => $genotype_term->name,
1037  'storage_args' => array(
1038  'data_table' => 'genotype',
1039  ),
1040  );
1041 
1042  tripal_create_bundle($args);
1043  $bundle = tripal_load_bundle_entity(array('label' => 'Genotype'));
1044 
1045  $prop_terms = array(
1046  array(
1047  'id' => 'sep:00007',
1048  'name' => 'format',
1049  'cvname' => 'sep',
1050  'field' => 'sep__format',
1051  'label' => 'Genotype Format',
1052  'dbname' => 'sep',
1053  'def' => 'A format is a quality that is a specificiation or arrangement of a specified form. The structure, layout, or the digital manifestation of an entity. [ PSI:GEL ]',
1054  ),
1055  array(
1056  'id' => 'SO:0001686',
1057  'name' => 'quality_value',
1058  'cvname' => 'sequence',
1059  'field' => 'so__quality_value',
1060  'label' => 'Genotype Quality Score',
1061  'dbname' => 'SO',
1062  'def' => 'An experimental feature attribute that defines the quality of the feature in a quantitative way, such as a phred quality score. [ http://www.sequenceontology.org/browser/current_svn/term/SO:ke ]',
1063  ),
1064  array(
1065  'id' => 'operation:3187',
1066  'name' => 'Sequence contamination filtering',
1067  'cvname' => 'operation',
1068  'field' => 'edam__filter',
1069  'label' => 'Genotype Contamination Filter',
1070  'dbname' => 'EDAM_operation',
1071  'def' => 'Identify and filter a (typically large) sequence data set to remove sequences from contaminants in the sample that was sequenced.',
1072  ),
1073  array(
1074  'id' => 'SO:0002119',
1075  'name' => 'allelic_frequency',
1076  'cvname' => 'sequence',
1077  'field' => 'so__allelic_frequency',
1078  'label' => 'Genotype Allelic Frequency',
1079  'dbname' => 'SO',
1080  'def' => 'A physical quality which inheres to the allele by virtue of the number instances of the allele within a population. This is the relative frequency of the allele at a given locus in a population. [ http://www.sequenceontology.org/browser/current_svn/term/SO:ke ]',
1081  ),
1082  array(
1083  'id' => 'NCIT:C155320',
1084  'name' => 'Read Depth',
1085  'cvname' => 'ncit',
1086  'field' => 'ncit__read_depth',
1087  'label' => 'Genotype Read Depth',
1088  'dbname' => 'NCIT',
1089  'def' => 'The number of times a particular locus (site, nucleotide, amplicon, region) was sequenced. [ NCI ]',
1090  ),
1091  array(
1092  'id' => "{$local_db->name}:number_samples",
1093  'name' => 'number_samples',
1094  'cvname' => $local_cv->name,
1095  'field' => "{$local_cv->name}__number_samples",
1096  'label' => 'Genotype Number of Samples',
1097  'dbname' => $local_db->name,
1098  'def' => 'Number of samples',
1099  ),
1100  );
1101 
1102  foreach ($prop_terms as $prop_term) {
1103  $term = chado_insert_cvterm(array(
1104  'id' => $prop_term['id'],
1105  'name' => $prop_term['name'],
1106  'definition' => $prop_term['def'],
1107  'db_name' => $prop_term['dbname'],
1108  'cv_name' => $prop_term['cvname'],
1109  ));
1110 
1111  if ($prop_term['cvname'] != $term->cvname) {
1112  db_update('chado.cvterm')
1113  ->fields(array(
1114  'cv_id' => chado_get_cv(array('name' => $prop_term['cvname']))->cv_id,
1115  ))
1116  ->condition('cvterm_id', $term->cvterm_id)
1117  ->execute();
1118  }
1119  $term->cvname = $prop_term['cvname'];
1120 
1121  $info = field_info_field($prop_term['field']);
1122  if (empty($info['bundles']['TripalEntity'])) {
1123  field_create_field(array(
1124  'field_name' => $prop_term['field'],
1125  'type' => 'chado_linker__prop',
1126  'settings' => array(
1127  'storage' => 'field_chado_storage',
1128  ),
1129  'storage' => array(
1130  'type' => 'field_chado_storage',
1131  'module' => 'tripal_chado',
1132  'active' => 1,
1133  ),
1134  ));
1135  }
1136  elseif (array_search($bundle->name, $info['bundles']['TripalEntity']) !== FALSE) {
1137  continue;
1138  }
1139 
1140  field_create_instance(array(
1141  'field_name' => $prop_term['field'],
1142  'entity_type' => 'TripalEntity',
1143  'label' => $prop_term['label'],
1144  'bundle' => $bundle->name,
1145  'required' => FALSE,
1146  'settings' => array(
1147  'user_register_form' => 0,
1148  'term_vocabulary' => $term->dbname,
1149  'term_name' => $term->name,
1150  'term_accession' => $term->accession,
1151  'base_table' => 'genotype',
1152  'chado_table' => 'genotypeprop',
1153  'chado_column' => 'genotype_id',
1154  'auto_attach' => 1,
1155  'term_fixed' => 0,
1156  ),
1157  ));
1158  }
1159 }
1160 
1165  $stock_term = tpps_load_cvterm('whole plant', array(
1166  'include_fk' => array(
1167  'dbxref_id' => 1,
1168  ),
1169  ));
1170 
1171  chado_associate_semweb_term('stock', 'stock_id', $stock_term);
1172  chado_populate_mview(chado_get_mview_id('db2cv_mview'));
1173 
1174  $args = array(
1175  'vocabulary' => $stock_term->dbxref_id->db_id->name,
1176  'accession' => $stock_term->dbxref_id->accession,
1177  'term_name' => $stock_term->name,
1178  'storage_args' => array(
1179  'data_table' => 'stock',
1180  ),
1181  );
1182  tripal_create_bundle($args);
1183 
1184  db_update('tripal_bundle')
1185  ->fields(array(
1186  'label' => 'Stock',
1187  ))
1188  ->condition('label', 'Whole Plant')
1189  ->execute();
1190  $bundle = tripal_load_bundle_entity(array('label' => 'Stock'));
1191  tripal_save_title_format($bundle, '[data__identifier]');
1192 
1193  $phenotype_term = tpps_load_cvterm('phenotype', array(
1194  'include_fk' => array(
1195  'dbxref_id' => 1,
1196  ),
1197  ));
1198 
1199  $info = field_info_field('sbo__phenotype');
1200  if (empty($info['bundles']['TripalEntity'])) {
1201  field_create_field(array(
1202  'field_name' => 'sbo__phenotype',
1203  'type' => 'sbo__phenotype',
1204  'settings' => array(
1205  'storage' => 'field_chado_storage',
1206  ),
1207  'storage' => array(
1208  'type' => 'field_chado_storage',
1209  'module' => 'tripal_chado',
1210  'active' => 1,
1211  ),
1212  ));
1213  }
1214 
1215  field_create_instance(array(
1216  'field_name' => 'sbo__phenotype',
1217  'entity_type' => 'TripalEntity',
1218  'label' => 'Phenotype',
1219  'bundle' => $bundle->name,
1220  'required' => FALSE,
1221  'settings' => array(
1222  'user_register_form' => 0,
1223  'term_vocabulary' => $phenotype_term->dbxref_id->db_id->name,
1224  'term_name' => $phenotype_term->name,
1225  'term_accession' => $phenotype_term->dbxref_id->accession,
1226  'base_table' => 'stock',
1227  'chado_table' => 'stock_phenotype',
1228  'chado_column' => 'phenotype_id',
1229  'auto_attach' => 1,
1230  'term_fixed' => 0,
1231  ),
1232  ));
1233 
1234  $genotype_term = tpps_load_cvterm('genotype', array(
1235  'include_fk' => array(
1236  'dbxref_id' => 1,
1237  ),
1238  ));
1239 
1240  $info = field_info_field('so__genotype');
1241  if (empty($info['bundles']['TripalEntity'])) {
1242  field_create_field(array(
1243  'field_name' => 'so__genotype',
1244  'type' => 'so__genotype',
1245  'settings' => array(
1246  'storage' => 'field_chado_storage',
1247  ),
1248  'storage' => array(
1249  'type' => 'field_chado_storage',
1250  'module' => 'tripal_chado',
1251  'active' => 1,
1252  ),
1253  ));
1254  }
1255 
1256  field_create_instance(array(
1257  'field_name' => 'so__genotype',
1258  'entity_type' => 'TripalEntity',
1259  'label' => 'Genotype',
1260  'bundle' => $bundle->name,
1261  'required' => FALSE,
1262  'settings' => array(
1263  'user_register_form' => 0,
1264  'term_vocabulary' => $genotype_term->dbxref_id->db_id->name,
1265  'term_name' => $genotype_term->name,
1266  'term_accession' => $genotype_term->dbxref_id->accession,
1267  'base_table' => 'stock',
1268  'chado_table' => 'stock_genotype',
1269  'chado_column' => 'genotype_id',
1270  'auto_attach' => 1,
1271  'term_fixed' => 0,
1272  ),
1273  ));
1274 }
1275 
1279 function tpps_add_dryad() {
1280  chado_insert_db(array(
1281  'name' => 'dryad',
1282  'url' => 'https://datadryad.org/',
1283  'urlprefix' => 'https://datadryad.org/resource/doi:{doi}',
1284  ));
1285 }
1286 
1292 function tpps_update_7100() {
1293 
1294  $transaction = db_transaction();
1295  try {
1296  $submissions = tpps_load_submission_multiple(array(
1297  'status' => 'Approved',
1298  ));
1299 
1300  foreach ($submissions as $state) {
1301  if (!empty($state['saved_values'][TPPS_PAGE_3]['tree-accession']['file']) and empty($state['saved_values'][TPPS_PAGE_3]['tree-accession']['check'])) {
1302  $state['saved_values'][TPPS_PAGE_3]['tree-accession']['species-1'] = $state['saved_values'][TPPS_PAGE_3]['tree-accession'];
1303  unset($state['saved_values'][TPPS_PAGE_3]['tree-accession']['species-1']['check']);
1304  try {
1306  }
1307  catch (Exception $e) {
1308  if ($e->getMessage() == 'Unrecognized file type') {
1309  continue;
1310  }
1311  throw $e;
1312  }
1313  tpps_update_submission($state);
1314  }
1315  }
1316  }
1317  catch (Exception $e) {
1318  $transaction->rollback();
1319  throw $e;
1320  }
1321 }
1322 
1328 function tpps_update_7101() {
1329  $transaction = db_transaction();
1330  try {
1331  $submissions = tpps_load_submission_multiple(array(
1332  'status' => 'Approved',
1333  ));
1334 
1335  foreach ($submissions as $state) {
1336  $firstpage = $state['saved_values'][TPPS_PAGE_1] ?? NULL;
1337  $seconds = $firstpage['publication']['secondaryAuthors'] ?? NULL;
1338  $state['title'] = $firstpage['publication']['title'] ?? "";
1339  $state['abstract'] = $firstpage['publication']['abstract'] ?? "";
1340  $state['pyear'] = $firstpage['publication']['year'] ?? "";
1341  $state['journal'] = $firstpage['publication']['journal'] ?? "";
1342  $state['authors'] = !empty($firstpage['primaryAuthor']) ? array($firstpage['primaryAuthor']) : array();
1343  $state['files'] = array();
1344  if (!empty($seconds['number'])) {
1345  for ($i = 1; $i <= $seconds['number']; $i++) {
1346  $state['authors'][] = $seconds[$i];
1347  }
1348  }
1349 
1350  if (empty($state['tree_info'])) {
1351  $state['tree_info'] = array();
1352  $query = db_select('chado.stock', 's');
1353  $query->join('chado.stockprop', 'lat', 'lat.stock_id = s.stock_id');
1354  $query->join('chado.stockprop', 'lng', 'lng.stock_id = s.stock_id');
1355  $query->fields('s', array('stock_id', 'uniquename', 'organism_id'));
1356  $query->fields('lat', array('value'));
1357  $query->fields('lng', array('value'));
1358  $query->condition('s.uniquename', db_like($state['accession']) . '%', 'LIKE');
1359  $query->condition('lat.type_id', tpps_load_cvterm('gps_latitude', array(), '1_1')->cvterm_id);
1360  $query->condition('lng.type_id', tpps_load_cvterm('gps_longitude', array(), '1_1')->cvterm_id);
1361 
1362  $query = $query->execute();
1363 
1364  while (($tree = $query->fetchObject())) {
1365  preg_match('/^TGDR[0-9]+-(.*)$/', $tree->uniquename, $matches);
1366  $state['tree_info'][$matches[1]] = array(
1367  'stock_id' => $tree->stock_id,
1368  'lat' => $tree->value,
1369  'lng' => $tree->lng_value,
1370  'organism_id' => $tree->organism_id,
1371  );
1372  }
1373  }
1374 
1375  $org_num = $state['stats']['species_count'] ?? 0;
1376  for ($i = 1; $i <= $org_num; $i++) {
1377  $state['files'][] = $state['saved_values'][TPPS_PAGE_3]['tree-accession']["species-$i"]['file'] ?? NULL;
1378  $state['files'][] = $state['saved_values'][TPPS_PAGE_4]["organism-$i"]['phenotype']['iso'] ?? NULL;
1379  $state['files'][] = $state['saved_values'][TPPS_PAGE_4]["organism-$i"]['phenotype']['file'] ?? NULL;
1380  $state['files'][] = $state['saved_values'][TPPS_PAGE_4]["organism-$i"]['phenotype']['metadata'] ?? NULL;
1381  $state['files'][] = $state['saved_values'][TPPS_PAGE_4]["organism-$i"]['genotype']['files']['snps-assay'] ?? NULL;
1382  $state['files'][] = $state['saved_values'][TPPS_PAGE_4]["organism-$i"]['genotype']['files']['assay-design'] ?? NULL;
1383  $state['files'][] = $state['saved_values'][TPPS_PAGE_4]["organism-$i"]['genotype']['files']['ssrs'] ?? NULL;
1384  $state['files'][] = $state['saved_values'][TPPS_PAGE_4]["organism-$i"]['genotype']['files']['other'] ?? NULL;
1385  $state['files'][] = $state['saved_values'][TPPS_PAGE_4]["organism-$i"]['genotype']['files']['vcf'] ?? NULL;
1386  $state['files'][] = $state['saved_values'][TPPS_PAGE_4]["organism-$i"]['genotype']['tripal_fasta']['file']['file_upload'] ?? NULL;
1387  }
1388 
1389  foreach ($state['files'] as $key => $val) {
1390  if (empty($val)) {
1391  unset($state['files'][$key]);
1392  }
1393  }
1394 
1395  tpps_update_submission($state);
1396  }
1397  }
1398  catch (Exception $e) {
1399  $transaction->rollback();
1400  throw $e;
1401  }
1402 }
1403 
1409 function tpps_update_7102() {
1410  tpps_add_dryad();
1411 }
1412 
1418 function tpps_update_7103() {
1419  $transaction = db_transaction();
1420  try {
1421  $submissions = tpps_load_submission_multiple(array(
1422  'status' => 'Approved',
1423  ));
1424  foreach ($submissions as $state) {
1425  if (!empty($state['saved_values'][TPPS_PAGE_1]['doi']) and !empty($state['ids']['project_id'])) {
1426  $dryad_db = chado_get_db(array('name' => 'dryad'));
1427  $dryad_dbxref = chado_insert_dbxref(array(
1428  'db_id' => $dryad_db->db_id,
1429  'accession' => $state['saved_values'][TPPS_PAGE_1]['doi'],
1430  ))->dbxref_id;
1431  tpps_chado_insert_record('project_dbxref', array(
1432  'project_id' => $state['ids']['project_id'],
1433  'dbxref_id' => $dryad_dbxref,
1434  ));
1435  }
1436  }
1437  }
1438  catch (Exception $e) {
1439  $transaction->rollback();
1440  throw $e;
1441  }
1442 }
1443 
1449 function tpps_update_7104() {
1450  $transaction = db_transaction();
1451  try {
1452  $submissions = tpps_load_submission_multiple(array(
1453  'status' => 'Approved',
1454  ));
1455  foreach ($submissions as $state) {
1456  if (!empty($state['ids']['organism_ids']) and !empty($state['ids']['project_id'])) {
1457  $proj_id = $state['ids']['project_id'];
1458  $pub_id = db_select('chado.project_pub', 'p')
1459  ->fields('p', array('pub_id'))
1460  ->condition('project_id', $proj_id)
1461  ->range(0, 1)
1462  ->execute()->fetchObject()->pub_id;
1463  foreach ($state['ids']['organism_ids'] as $org_id) {
1464  tpps_chado_insert_record('pub_organism', array(
1465  'organism_id' => $org_id,
1466  'pub_id' => $pub_id,
1467  ));
1468  }
1469  }
1470  }
1471  }
1472  catch (Exception $e) {
1473  $transaction->rollback();
1474  throw $e;
1475  }
1476 }
1477 
1483 function tpps_update_7105() {
1485 }
1486 
1492 function tpps_update_7106() {
1493  $submissions = tpps_load_submission_multiple();
1494  foreach ($submissions as $state) {
1495  if (empty($state['updated'])) {
1496  tpps_update_submission($state);
1497  }
1498  }
1499 }
1500 
1506 function tpps_update_7107() {
1507  $cv = chado_get_cv(array('name' => 'PPEO'));
1508  if (empty($cv)) {
1510  }
1511 }
1512 
1518 function tpps_update_7108() {
1519  variable_set('tpps_cvterms_version', '1_1');
1520  tpps_migrate_cvterms(1, 1, 1, 2);
1521 }
1522 
1528 function tpps_update_7109() {
1529  $submissions = tpps_load_submission_multiple(array(
1530  'status' => 'Approved',
1531  ));
1532  $or = db_or()
1533  ->condition('type_id', tpps_load_cvterm('file_path')->cvterm_id)
1534  ->condition('type_id', tpps_load_cvterm('file_path', array(), '1_1')->cvterm_id);
1535  foreach ($submissions as $state) {
1536  $files = $state['files'] ?? NULL;
1537  if (!empty($files) and !empty($state['file_info'])) {
1538  $transaction = db_transaction();
1539  try {
1540  // Delete files from projectprop.
1541  $num_files = db_delete('chado.projectprop')
1542  ->condition('project_id', $state['ids']['project_id'])
1543  ->condition($or)
1544  ->execute();
1545  $state['file_rank'] = 0;
1546  $state_files = count($files);
1547  $state_files += count(($state['revised_files'] ?? array()));
1548  if ($num_files != $state_files) {
1549  throw new \Exception("Improper number of files in db vs files in state: $num_files in db and $state_files in state");
1550  }
1551  if (!empty($state['revised_files'])) {
1552  foreach ($state['revised_files'] as $fid) {
1553  $files[] = $fid;
1554  }
1555  }
1556  foreach ($files as $fid) {
1557  if ($fid) {
1558  $file = file_load($fid);
1559  tpps_chado_insert_record('projectprop', array(
1560  'project_id' => $state['ids']['project_id'],
1561  'type_id' => tpps_load_cvterm('file_path')->cvterm_id,
1562  'value' => file_create_url($file->uri),
1563  'rank' => $state['file_rank']++,
1564  ));
1565  }
1566  }
1567  tpps_update_submission($state);
1568  }
1569  catch (\Exception $e) {
1570  drupal_set_message("skipping accession {$state['accession']}: " . $e->getMessage());
1571  $transaction->rollback();
1572  }
1573  unset($transaction);
1574  }
1575  }
1576 }
1577 
1583 function tpps_update_7110() {
1584  $transaction = db_transaction();
1585  try {
1586  $submissions = tpps_load_submission_multiple();
1587  foreach ($submissions as $state) {
1588  $state['alternative_accessions'] = $state['alternative_accessions'] ?? '';
1589  tpps_update_submission($state);
1590  }
1591 
1592  if (!db_table_exists('tpps_submission_dbxref')) {
1593  $schema = tpps_schema();
1594  db_create_table('tpps_submission_dbxref', $schema['tpps_submission_dbxref']);
1596  }
1597  }
1598  catch (\Exception $e) {
1599  $transaction->rollback();
1600  throw $e;
1601  }
1602 }
1603 
1609 function tpps_update_7111() {
1610  $transaction = db_transaction();
1611  try {
1612  db_delete('chado.project_pub')
1613  ->condition('pub_id', db_query('select distinct(pub_id) from chado.pub_organism'), 'NOT IN')
1614  ->execute();
1615  }
1616  catch (\Exception $e) {
1617  $transaction->rollback();
1618  throw $e;
1619  }
1620 }
1621 
1627 function tpps_update_7112() {
1628  $transaction = db_transaction();
1629  try {
1630  $submissions = tpps_load_submission_multiple();
1631  foreach ($submissions as $state) {
1632  $org_num = $state['saved_values'][TPPS_PAGE_1]['organism']['number'];
1633  $update = FALSE;
1634  for ($i = 1; $i <= $org_num; $i++) {
1635  if (gettype($state['saved_values'][TPPS_PAGE_1]['organism'][$i]) != 'array') {
1636  $update = TRUE;
1637  $name = $state['saved_values'][TPPS_PAGE_1]['organism'][$i];
1638  $state['saved_values'][TPPS_PAGE_1]['organism'][$i] = array();
1639  $state['saved_values'][TPPS_PAGE_1]['organism'][$i]['name'] = $name;
1640  $state['saved_values'][TPPS_PAGE_1]['organism'][$i]['is_tree'] = TRUE;
1641  }
1642  }
1643  if ($update) {
1644  tpps_update_submission($state);
1645  }
1646  }
1647  }
1648  catch (\Exception $e) {
1649  $transaction->rollback();
1650  throw $e;
1651  }
1652 }
1653 
1659 function tpps_update_7113() {
1660  $transaction = db_transaction();
1661  $submissions = tpps_load_submission_multiple(array(
1662  'status' => array('Approved', 'Pending Approval', 'Incomplete'),
1663  ));
1664  try {
1665  foreach ($submissions as $state) {
1666  $change = FALSE;
1667  $org_num = 1;
1668  if (!empty($state['file_info'][TPPS_PAGE_3])) {
1669  foreach ($state['file_info'][TPPS_PAGE_3] as $fid => $name) {
1670  if ($name == 'Plant_Accession' or $name == 'Tree_Accession') {
1671  $species = implode('_', explode(' ', $state['saved_values'][TPPS_PAGE_1]['organism'][$org_num]['name']));
1672  $state['file_info'][TPPS_PAGE_3][$fid] = "{$name}_{$species}";
1673  $change = TRUE;
1674  }
1675  $org_num++;
1676  }
1677  }
1678 
1679  if (!empty($state['file_info'][TPPS_PAGE_4])) {
1680  foreach ($state['file_info'][TPPS_PAGE_4] as $fid => $name) {
1681  if (preg_match('/^(.*_)([0-9]+)$/', $name, $matches)) {
1682  $prefix = $matches[1];
1683  $org_num = $matches[2];
1684  $species = implode('_', explode(' ', $state['saved_values'][TPPS_PAGE_1]['organism'][$org_num]['name']));
1685  $state['file_info'][TPPS_PAGE_4][$fid] = "{$prefix}{$species}";
1686  $change = TRUE;
1687  }
1688  }
1689  }
1690 
1691  if ($change) {
1692  tpps_update_submission($state);
1693  if ($state['status'] == 'Approved') {
1694  tpps_submission_rename_files($state['accession']);
1695  }
1696  }
1697  }
1698  }
1699  catch (\Exception $e) {
1700  $transaction->rollback();
1701  throw $e;
1702  }
1703 }
1704 
1710 function tpps_update_7114() {
1711  $transaction = db_transaction();
1712  try {
1713  if (!db_table_exists('tpps_project_file_managed')) {
1714  $schema = tpps_schema();
1715  db_create_table('tpps_project_file_managed', $schema['tpps_project_file_managed']);
1717  }
1718  }
1719  catch (\Exception $e) {
1720  $transaction->rollback();
1721  throw $e;
1722  }
1723 }
1724 
1730 function tpps_update_7115() {
1731  $transaction = db_transaction();
1732  $submissions = tpps_load_submission_multiple(array(
1733  'status' => 'Approved',
1734  ));
1735  try {
1736  foreach ($submissions as $state) {
1737  if (empty($state['ids']['project_id'])) {
1738  continue;
1739  }
1740  $firstpage = $state['saved_values'][TPPS_PAGE_1];
1741  $thirdpage = $state['saved_values'][TPPS_PAGE_3];
1742  $fourthpage = $state['saved_values'][TPPS_PAGE_4];
1743 
1744  $fids = array(
1745  $firstpage['photo'] ?? NULL,
1746  );
1747 
1748  for ($i = 1; $i <= $firstpage['organism']['number']; $i++) {
1749  $fids[] = $thirdpage['tree-accession']["species-$i"]['file'] ?? NULL;
1750  $fids[] = $fourthpage["organism-$i"]['phenotype']['file'] ?? NULL;
1751  $fids[] = $fourthpage["organism-$i"]['phenotype']['metadata'] ?? NULL;
1752  $fids[] = $fourthpage["organism-$i"]['phenotype']['iso'] ?? NULL;
1753  $fids[] = $fourthpage["organism-$i"]['genotype']['tripal_fasta']['file_upload'] ?? NULL;
1754  $fids[] = $fourthpage["organism-$i"]['genotype']['tripal_fasta']['file_upload_existing'] ?? NULL;
1755  $fids[] = $fourthpage["organism-$i"]['genotype']['files']['snps-assay'] ?? NULL;
1756  $fids[] = $fourthpage["organism-$i"]['genotype']['files']['snps-association'] ?? NULL;
1757  $fids[] = $fourthpage["organism-$i"]['genotype']['files']['snps-pop-struct'] ?? NULL;
1758  $fids[] = $fourthpage["organism-$i"]['genotype']['files']['snps-kinship'] ?? NULL;
1759  $fids[] = $fourthpage["organism-$i"]['genotype']['files']['assay-design'] ?? NULL;
1760  $fids[] = $fourthpage["organism-$i"]['genotype']['files']['ssrs'] ?? NULL;
1761  $fids[] = $fourthpage["organism-$i"]['genotype']['files']['ssrs_extra'] ?? NULL;
1762  $fids[] = $fourthpage["organism-$i"]['genotype']['files']['indels'] ?? NULL;
1763  $fids[] = $fourthpage["organism-$i"]['genotype']['files']['other'] ?? NULL;
1764  $fids[] = $fourthpage["organism-$i"]['genotype']['files']['vcf'] ?? NULL;
1765  }
1766 
1767  foreach ($state['saved_values']['summarypage']['analysis'] as $key => $info) {
1768  if (substr($key, -5) == '_file' and file_load($info)) {
1769  $fids[] = $info;
1770  }
1771  }
1772 
1773  foreach ($fids as $fid) {
1774  if (!empty($fid) and file_load($fid)) {
1775  $query = db_select('tpps_project_file_managed', 't')
1776  ->fields('t')
1777  ->condition('fid', $fid)
1778  ->condition('project_id', $state['ids']['project_id'])
1779  ->execute();
1780  $result = $query->fetchObject() ?? NULL;
1781 
1782  if (empty($result)) {
1783  db_insert('tpps_project_file_managed')
1784  ->fields(array(
1785  'project_id' => $state['ids']['project_id'],
1786  'fid' => $fid,
1787  ))
1788  ->execute();
1789  }
1790 
1791  if (!empty($state['revised_files'][$fid]) and ($file = file_load($state['revised_files'][$fid]))) {
1792  $query = db_select('tpps_project_file_managed', 't')
1793  ->fields('t')
1794  ->condition('fid', $state['revised_files'][$fid])
1795  ->condition('project_id', $state['ids']['project_id'])
1796  ->execute();
1797  $result = $query->fetchObject() ?? NULL;
1798 
1799  if (empty($result)) {
1800  db_insert('tpps_project_file_managed')
1801  ->fields(array(
1802  'project_id' => $state['ids']['project_id'],
1803  'fid' => $state['revised_files'][$fid],
1804  ))
1805  ->execute();
1806  }
1807  }
1808  }
1809  }
1810  }
1811  }
1812  catch (\Exception $e) {
1813  $transaction->rollback();
1814  throw $e;
1815  }
1816 }
1817 
1823 function tpps_update_7116() {
1824  $transaction = db_transaction();
1825  try {
1826  if (!db_table_exists('tpps_tag')) {
1827  $schema = tpps_schema();
1828  db_create_table('tpps_tag', $schema['tpps_tag']);
1829  }
1830  if (!db_table_exists('tpps_submission_tag')) {
1831  $schema = tpps_schema();
1832  db_create_table('tpps_submission_tag', $schema['tpps_submission_tag']);
1834  }
1835 
1837 
1838  $submissions = tpps_load_submission_multiple(array(), FALSE);
1839  foreach ($submissions as $record) {
1840  $state = tpps_load_submission($record->accession);
1841  $tags = tpps_submission_get_tags($state['accession']);
1842  if (count($tags) == 0) {
1843  if ((!isset($state['tpps_type']) or $state['tpps_type'] != 'tppsc')) {
1844  tpps_submission_add_tag($state['accession'], 'TPPS');
1845  }
1846  else {
1847  tpps_submission_add_tag($state['accession'], 'TPPSc');
1848  }
1849 
1850  if ($state['status'] == 'Approved') {
1851  $data_type = $state['saved_values'][TPPS_PAGE_2]['data_type'];
1852  $env_phenotypes = FALSE;
1853  if (preg_match('/G/', $data_type)) {
1854  tpps_submission_add_tag($state['accession'], 'Genotype');
1855  }
1856  if (preg_match('/P/', $data_type)) {
1857  tpps_submission_add_tag($state['accession'], 'Phenotype');
1858  $org_num = $state['saved_values'][TPPS_PAGE_1]['organism']['number'];
1859  for ($i = 1; $i <= $org_num; $i++) {
1860  $phenotype_num = $state['saved_values'][TPPS_PAGE_4]["organism-$i"]['phenotype']['phenotypes-meta']['number'];
1861  for ($j = 1; $j <= $phenotype_num; $j++) {
1862  $info = $state['saved_values'][TPPS_PAGE_4]["organism-$i"]['phenotype']['phenotypes-meta'][$j];
1863  if ($info['env-check']) {
1864  $env_phenotypes = TRUE;
1865  break;
1866  }
1867  }
1868  }
1869  }
1870  if (preg_match('/E/', $data_type) or $env_phenotypes) {
1871  tpps_submission_add_tag($state['accession'], 'Environment');
1872  }
1873  }
1874  }
1875  }
1876  }
1877  catch (\Exception $e) {
1878  $transaction->rollback();
1879  throw $e;
1880  }
1881 }
1882 
1888 function tpps_update_7117() {
1889  $transaction = db_transaction();
1890 
1891  try {
1892  $id = tpps_get_tag_id('No Location Information');
1893  if (!$id) {
1894  db_insert('tpps_tag')
1895  ->fields(array(
1896  'name' => 'No Location Information',
1897  'color' => 'pink',
1898  'static' => 1,
1899  ))
1900  ->execute();
1901  }
1902 
1903  $id = tpps_get_tag_id('Approximate Coordinates');
1904  if (!$id) {
1905  db_insert('tpps_tag')
1906  ->fields(array(
1907  'name' => 'Approximate Coordinates',
1908  'color' => 'gray',
1909  'static' => 1,
1910  ))
1911  ->execute();
1912  }
1913 
1914  $submissions = tpps_load_submission_multiple(array(), FALSE);
1915  foreach ($submissions as $record) {
1916  $state = tpps_load_submission($record->accession);
1917 
1918  if (!empty($state['saved_values'][TPPS_PAGE_3]['skip_validation'])) {
1919  tpps_submission_add_tag($state['accession'], 'No Location Information');
1920  }
1921 
1922  $org_num = $state['saved_values'][TPPS_PAGE_1]['organism']['number'];
1923  for ($i = 1; $i <= $org_num; $i++) {
1924  $species_i = $state['saved_values'][TPPS_PAGE_3]['tree-accession']["species-$i"];
1925  if (isset($species_i['exact_coords']) and !$species_i['exact_coords']) {
1926  tpps_submission_add_tag($state['accession'], 'Approximate Coordinates');
1927  break;
1928  }
1929  }
1930  }
1931  }
1932  catch (\Exception $e) {
1933  $transaction->rollback();
1934  throw $e;
1935  }
1936 }
1937 
1943 function tpps_update_7118() {
1944  $transaction = db_transaction();
1945  $submissions = tpps_load_submission_multiple(array(), FALSE);
1946 
1947  try {
1948  foreach ($submissions as $record) {
1949  $state = tpps_load_submission($record->accession);
1950 
1951  $org_num = $state['saved_values'][TPPS_PAGE_1]['organism']['number'];
1952  for ($i = 1; $i <= $org_num; $i++) {
1953  $phenotype = &$state['saved_values'][TPPS_PAGE_4]["organism-$i"]['phenotype'] ?? NULL;
1954  if (!empty($phenotype)) {
1955  // If the submission doesn't have iso/mass spec data, then it must be
1956  // normal phenotype data.
1957  if (empty($phenotype['iso-check']) and !empty($phenotype['file'])) {
1958  $phenotype['normal-check'] = TRUE;
1959  }
1960 
1961  // If the submission does have iso/mass spec data and doesn't have
1962  // the normal-check field, then it must not have normal phenotypes.
1963  if (!empty($phenotype['iso-check']) and !array_key_exists('normal-check', $phenotype)) {
1964  $phenotype['normal-check'] = FALSE;
1965  }
1966  }
1967  };
1968 
1969  tpps_update_submission($state);
1970  }
1971 
1972  }
1973  catch (\Exception $e) {
1974  $transaction->rollback();
1975  throw $e;
1976  }
1977 }
tpps_update_7108()
Definition: tpps.install:1518
tpps_submission_get_tags($accession)
tpps_update_7109()
Definition: tpps.install:1528
tpps_create_stock_entity()
Definition: tpps.install:1164
tpps_install_cvterms()
Definition: tpps.install:683
tpps_update_7110()
Definition: tpps.install:1583
const TPPS_PAGE_1
Definition: tpps.module:12
tpps_update_7103()
Definition: tpps.install:1418
tpps_update_7112()
Definition: tpps.install:1627
tpps_update_7114()
Definition: tpps.install:1710
tpps_update_submission(array $state, array $options=array())
tpps_default_permissions()
Definition: tpps.install:891
tpps_default_tags()
Definition: tpps.install:977
tpps_get_tag_id($name)
tpps_update_7105()
Definition: tpps.install:1483
tpps_update_7107()
Definition: tpps.install:1506
tpps_alter_tpps_submission_dbxref()
Definition: tpps.install:760
tpps_alter_tpps_project_file_managed()
Definition: tpps.install:773
tpps_chado_insert_record($table, $records, array $options=array())
Definition: chado_utils.inc:27
tpps_update_7115()
Definition: tpps.install:1730
tpps_update_7118()
Definition: tpps.install:1943
tpps_submission_add_tag($accession, $tag)
tpps_migrate_cvterms($old_major=NULL, $old_minor=NULL, $new_major=TPPS_MAJOR_VERSION, $new_minor=TPPS_MINOR_VERSION)
tpps_update_7117()
Definition: tpps.install:1888
tpps_create_plusgeno_view()
Definition: tpps.install:799
tpps_local_install_term($key, $version=NULL)
tpps_submission_rename_files($accession)
tpps_update_7102()
Definition: tpps.install:1409
tpps_create_genotype_entity()
Definition: tpps.install:1024
tpps_update_7101()
Definition: tpps.install:1328
tpps_schema()
Definition: tpps.install:315
tpps_alter_tpps_submission_tag()
Definition: tpps.install:786
tpps_load_submission($accession, $state=TRUE)
Definition: submissions.inc:27
tpps_update_7104()
Definition: tpps.install:1449
tpps_add_dryad()
Definition: tpps.install:1279
tpps_submission_update_all_stats(array &$form_state)
tpps_update_7113()
Definition: tpps.install:1659
tpps_alter_tpps_submissions()
Definition: tpps.install:746
tpps_uninstall()
Definition: tpps.install:287
tpps_permission()
Definition: tpps.module:291
tpps_update_7100()
Definition: tpps.install:1292
const TPPS_MAJOR_VERSION
Definition: tpps.module:8
const TPPS_PAGE_4
Definition: tpps.module:15
tpps_load_cvterm($term, array $options=array(), $version=NULL, $refresh_cache=FALSE)
tpps_load_submission_multiple(array $conditions=array(), $state=TRUE)
Definition: submissions.inc:85
tpps_create_genotype_search_views()
Definition: tpps.install:860
tpps_install_miappe_ontology()
Definition: tpps.install:659
tpps_update_7116()
Definition: tpps.install:1823
tpps_create_entities()
Definition: tpps.install:1002
tpps_update_7106()
Definition: tpps.install:1492
tpps_install()
Definition: tpps.install:14
tpps_alter_genotype_call()
Definition: tpps.install:718
const TPPS_PAGE_3
Definition: tpps.module:14
tpps_ols_install_term($info)
const TPPS_PAGE_2
Definition: tpps.module:13
tpps_update_7111()
Definition: tpps.install:1609
const TPPS_MINOR_VERSION
Definition: tpps.module:9