analysis_file_experiment_info

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

species_name

text

2147483647

√

null

species_id

uuid

2147483647

√

null

species.species_id

Implied Constraint

description

varchar

2147483647

√

null

epigenome_group_id

uuid

2147483647

√

null

epigenome_group.epigenome_group_id

Implied Constraint

epigenome_accession

text

2147483647

√

null

epigenome_name

text

2147483647

√

null

epigenome_description

text

2147483647

√

null

epigenome_id

uuid

2147483647

√

null

epigenome.epigenome_id

Implied Constraint

experiment_name

text

2147483647

√

null

experiment_id

uuid

2147483647

√

null

experiment_type

2147483647

√

null

experiment_target_type

text

2147483647

√

null

experiment_target

text

2147483647

√

null

signal_replicate_set_accession

text

2147483647

√

null

signal_replicate_set_id

uuid

2147483647

√

null

replicate_set.replicate_set_id

Implied Constraint

control_replicate_set_accession

text

2147483647

√

null

control_replicate_set_id

uuid

2147483647

√

null

replicate_set.replicate_set_id

Implied Constraint

ensembl_accession

varchar

2147483647

√

null

open_chromatin_peak

text

2147483647

√

null

chip_seq_narrow_peak_key

text

2147483647

√

null

chip_seq_broad_peak_key

text

2147483647

√

null

chip_seq_gapped_peak_key

text

2147483647

√

null

signal_key

text

2147483647

√

null

Relationships

Close relationships within degrees of separation

View Definition

WITH signal_files AS (
         SELECT analysis_file.analysis_file_id AS signal_analysis_file_id,
            analysis_file.experiment_id AS signal_experiment_id,
            assembly_1.assembly_id AS signal_assembly_id,
            s3_object.key AS signal_key
           FROM (((analysis_file
             JOIN assembly assembly_1 ON ((analysis_file.assembly_id = assembly_1.assembly_id)))
             JOIN file ON ((analysis_file.file_id = file.file_id)))
             JOIN s3_object ON ((file.file_id = s3_object.file_id)))
          WHERE (analysis_file.analysis_type = 'signal'::analysis_type)
        ), atac_seq_peak_files AS (
         SELECT analysis_file.analysis_file_id AS atac_seq_peak_analysis_file_id,
            analysis_file.experiment_id AS atac_seq_peak_experiment_id,
            assembly_1.assembly_id AS atac_seq_peak_assembly_id,
            s3_object.key AS atac_seq_peak_key
           FROM (((analysis_file
             JOIN assembly assembly_1 ON ((analysis_file.assembly_id = assembly_1.assembly_id)))
             JOIN file ON ((analysis_file.file_id = file.file_id)))
             JOIN s3_object ON ((file.file_id = s3_object.file_id)))
          WHERE (analysis_file.analysis_type = 'atac_seq_peaks'::analysis_type)
        ), dnase_seq_peak_files AS (
         SELECT analysis_file.analysis_file_id AS dnase_seq_peak_analysis_file_id,
            analysis_file.experiment_id AS dnase_seq_peak_experiment_id,
            assembly_1.assembly_id AS dnase_seq_peak_assembly_id,
            s3_object.key AS dnase_seq_peak_key
           FROM (((analysis_file
             JOIN assembly assembly_1 ON ((analysis_file.assembly_id = assembly_1.assembly_id)))
             JOIN file ON ((analysis_file.file_id = file.file_id)))
             JOIN s3_object ON ((file.file_id = s3_object.file_id)))
          WHERE (analysis_file.analysis_type = 'dnase_seq_peaks'::analysis_type)
        ), chip_seq_narrow_peak_files AS (
         SELECT analysis_file.analysis_file_id AS chip_seq_narrow_peak_analysis_file_id,
            analysis_file.experiment_id AS chip_seq_narrow_peak_experiment_id,
            assembly_1.assembly_id AS chip_seq_narrow_peak_assembly_id,
            s3_object.key AS chip_seq_narrow_peak_key
           FROM (((analysis_file
             JOIN assembly assembly_1 ON ((analysis_file.assembly_id = assembly_1.assembly_id)))
             JOIN file ON ((analysis_file.file_id = file.file_id)))
             JOIN s3_object ON ((file.file_id = s3_object.file_id)))
          WHERE (analysis_file.analysis_type = 'chip_seq_narrow_peaks'::analysis_type)
        ), chip_seq_broad_peak_files AS (
         SELECT analysis_file.analysis_file_id AS chip_seq_broad_peak_analysis_file_id,
            analysis_file.experiment_id AS chip_seq_broad_peak_experiment_id,
            assembly_1.assembly_id AS chip_seq_broad_peak_assembly_id,
            s3_object.key AS chip_seq_broad_peak_key
           FROM (((analysis_file
             JOIN assembly assembly_1 ON ((analysis_file.assembly_id = assembly_1.assembly_id)))
             JOIN file ON ((analysis_file.file_id = file.file_id)))
             JOIN s3_object ON ((file.file_id = s3_object.file_id)))
          WHERE (analysis_file.analysis_type = 'chip_seq_broad_peaks'::analysis_type)
        ), chip_seq_gapped_peak_files AS (
         SELECT analysis_file.analysis_file_id AS chip_seq_gapped_peak_analysis_file_id,
            analysis_file.experiment_id AS chip_seq_gapped_peak_experiment_id,
            assembly_1.assembly_id AS chip_seq_gapped_peak_assembly_id,
            s3_object.key AS chip_seq_gapped_peak_key
           FROM (((analysis_file
             JOIN assembly assembly_1 ON ((analysis_file.assembly_id = assembly_1.assembly_id)))
             JOIN file ON ((analysis_file.file_id = file.file_id)))
             JOIN s3_object ON ((file.file_id = s3_object.file_id)))
          WHERE (analysis_file.analysis_type = 'chip_seq_gapped_peaks'::analysis_type)
        )
 SELECT experiment_signal_and_control_data_source.species_name,
    experiment_signal_and_control_data_source.species_id,
    experiment_signal_and_control_data_source.description,
    experiment_signal_and_control_data_source.epigenome_group_id,
    experiment_signal_and_control_data_source.epigenome_accession,
    experiment_signal_and_control_data_source.epigenome_name,
    experiment_signal_and_control_data_source.epigenome_description,
    experiment_signal_and_control_data_source.epigenome_id,
    experiment_signal_and_control_data_source.experiment_name,
    experiment_signal_and_control_data_source.experiment_id,
    experiment_signal_and_control_data_source.experiment_type,
    experiment_signal_and_control_data_source.experiment_target_type,
    experiment_signal_and_control_data_source.experiment_target,
    experiment_signal_and_control_data_source.signal_replicate_set_accession,
    experiment_signal_and_control_data_source.signal_replicate_set_id,
    experiment_signal_and_control_data_source.control_replicate_set_accession,
    experiment_signal_and_control_data_source.control_replicate_set_id,
    assembly.ensembl_accession,
    COALESCE(atac_seq_peak_files.atac_seq_peak_key, dnase_seq_peak_files.dnase_seq_peak_key) AS open_chromatin_peak,
    chip_seq_narrow_peak_files.chip_seq_narrow_peak_key,
    chip_seq_broad_peak_files.chip_seq_broad_peak_key,
    chip_seq_gapped_peak_files.chip_seq_gapped_peak_key,
    signal_files.signal_key
   FROM (((((((experiment_signal_and_control_data_source
     JOIN assembly ON ((experiment_signal_and_control_data_source.species_id = assembly.species_id)))
     LEFT JOIN signal_files ON (((experiment_signal_and_control_data_source.experiment_id = signal_files.signal_experiment_id) AND (signal_files.signal_assembly_id = assembly.assembly_id))))
     LEFT JOIN atac_seq_peak_files ON (((experiment_signal_and_control_data_source.experiment_id = atac_seq_peak_files.atac_seq_peak_experiment_id) AND (atac_seq_peak_files.atac_seq_peak_assembly_id = assembly.assembly_id))))
     LEFT JOIN dnase_seq_peak_files ON (((experiment_signal_and_control_data_source.experiment_id = dnase_seq_peak_files.dnase_seq_peak_experiment_id) AND (dnase_seq_peak_files.dnase_seq_peak_assembly_id = assembly.assembly_id))))
     LEFT JOIN chip_seq_narrow_peak_files ON (((experiment_signal_and_control_data_source.experiment_id = chip_seq_narrow_peak_files.chip_seq_narrow_peak_experiment_id) AND (chip_seq_narrow_peak_files.chip_seq_narrow_peak_assembly_id = assembly.assembly_id))))
     LEFT JOIN chip_seq_broad_peak_files ON (((experiment_signal_and_control_data_source.experiment_id = chip_seq_broad_peak_files.chip_seq_broad_peak_experiment_id) AND (chip_seq_broad_peak_files.chip_seq_broad_peak_assembly_id = assembly.assembly_id))))
     LEFT JOIN chip_seq_gapped_peak_files ON (((experiment_signal_and_control_data_source.experiment_id = chip_seq_gapped_peak_files.chip_seq_gapped_peak_experiment_id) AND (chip_seq_gapped_peak_files.chip_seq_gapped_peak_assembly_id = assembly.assembly_id))));

analysis_file_experiment_info

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views