curated_experiment_analysis_files

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

experiment_id

uuid

2147483647

√

null

assembly_id

uuid

2147483647

√

null

assembly.assembly_id

Implied Constraint

R

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 DISTINCT curated_experiment_metadata.experiment_id,
    curated_experiment_metadata.assembly_id,
    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 ((((((curated_experiment_metadata
     LEFT JOIN signal_files ON (((curated_experiment_metadata.experiment_id = signal_files.signal_experiment_id) AND (signal_files.signal_assembly_id = curated_experiment_metadata.assembly_id))))
     LEFT JOIN atac_seq_peak_files ON (((curated_experiment_metadata.experiment_id = atac_seq_peak_files.atac_seq_peak_experiment_id) AND (atac_seq_peak_files.atac_seq_peak_assembly_id = curated_experiment_metadata.assembly_id))))
     LEFT JOIN dnase_seq_peak_files ON (((curated_experiment_metadata.experiment_id = dnase_seq_peak_files.dnase_seq_peak_experiment_id) AND (dnase_seq_peak_files.dnase_seq_peak_assembly_id = curated_experiment_metadata.assembly_id))))
     LEFT JOIN chip_seq_narrow_peak_files ON (((curated_experiment_metadata.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 = curated_experiment_metadata.assembly_id))))
     LEFT JOIN chip_seq_broad_peak_files ON (((curated_experiment_metadata.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 = curated_experiment_metadata.assembly_id))))
     LEFT JOIN chip_seq_gapped_peak_files ON (((curated_experiment_metadata.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 = curated_experiment_metadata.assembly_id))));

curated_experiment_analysis_files

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views