tdis_artifacts

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

tdis_identifier

text

2147483647

√

null

element_id

int4

√

null

title

varchar

2147483647

√

null

description

text

2147483647

√

null

purpose

text

2147483647

√

null

element_last_updated

date

√

null

element_created

date

√

null

username

varchar

2147483647

√

null

user_first_name

varchar

2147483647

√

null

user_last_name

varchar

2147483647

√

null

user_id

int4

√

null

collection

varchar

2147483647

√

null

collection_id

int4

√

null

contacts

_int4

√

null

collection_jurisdiction

text

2147483647

√

null

collection_jurisdiction_code

text

2147483647

√

null

collection_jurisdiction_type

varchar

2147483647

√

null

jurisdictions

_text

2147483647

√

null

custody_chain

_varchar

2147483647

√

null

license

varchar

2147483647

√

null

keyword_term

varchar

2147483647

√

null

keyword_vocabulary_source

varchar

2147483647

√

null

metadata_date

date

√

null

status

varchar

2147483647

√

null

endpoint_type

varchar

2147483647

√

null

distribution_method

varchar

2147483647

√

null

update_interval

varchar

2147483647

√

null

publisher_distributor_agency

varchar

2147483647

√

null

publisher_distributor_agency_id

int4

√

null

documentation_filenames

_varchar

2147483647

√

null

creator

varchar

2147483647

√

null

distribution_url

varchar

2147483647

√

null

software

varchar

2147483647

√

null

software_distributor

varchar

2147483647

√

null

software_version

varchar

2147483647

√

null

tdis_collection_identifier

text

2147483647

√

null

time_periods

_daterange

2147483647

√

null

use_constraints

_varchar

2147483647

√

null

accessibility_score

int4

√

null

accessibility_score_description

varchar

2147483647

√

null

data_stage_score

int4

√

null

data_stage_description

varchar

2147483647

√

null

file_format

varchar

2147483647

√

null

process_step_date

date

√

null

process_step_description

text

2147483647

√

null

process_step_source_agency

varchar

2147483647

√

null

most_recent_status_update

timestamptz

35,6

√

null

archive_path

text

2147483647

√

null

record_created_date

timestamptz

35,6

√

null

record_updated_date

timestamptz

35,6

√

null

textsearchable_index_col

tsvector

2147483647

√

null

View Definition

SELECT concat(e.element_type, '-', e.guid) AS tdis_identifier,
    e.id AS element_id,
    e.title,
    e.description,
    e.purpose,
    mem.element_last_updated,
    mem.element_created,
    u.username,
    u.first_name AS user_first_name,
    u.last_name AS user_last_name,
    u.contact_id AS user_id,
    col_e.title AS collection,
    col.element_id AS collection_id,
    COALESCE(array_agg(DISTINCT ARRAY[ec.contact_id, ec.role_id]) FILTER (WHERE (ec.contact_id IS NOT NULL)), '{}'::integer[]) AS contacts,
    col_j.name AS collection_jurisdiction,
    col_j.code AS collection_jurisdiction_code,
    col_jt.name AS collection_jurisdiction_type,
    COALESCE(array_agg(DISTINCT ARRAY[j.name, (jt.name)::text]), ('{}'::character varying[])::text[]) AS jurisdictions,
    e.custody_chain,
    e.license,
    e.keyword_term,
    cv.name AS keyword_vocabulary_source,
    e.metadata_date,
    s.name AS status,
    ept.name AS endpoint_type,
    dm.name AS distribution_method,
    ui.name AS update_interval,
    pda.name AS publisher_distributor_agency,
    pda.id AS publisher_distributor_agency_id,
    mem.documentation_filenames,
    mem.creator,
    mem.distribution_url,
    sw.name AS software,
    sw.distributor AS software_distributor,
    mem.software_version,
    concat(col_e.element_type, '-', col_e.guid) AS tdis_collection_identifier,
    mem.time_periods,
    COALESCE(array_agg(DISTINCT con.name) FILTER (WHERE (con.name IS NOT NULL)), '{}'::character varying[]) AS use_constraints,
    acc.score AS accessibility_score,
    acc.description AS accessibility_score_description,
    ds.score AS data_stage_score,
    ds.description AS data_stage_description,
    ff.name AS file_format,
    a.process_date AS process_step_date,
    a.process_description AS process_step_description,
    pss.name AS process_step_source_agency,
    lsu.most_recent_update AS most_recent_status_update,
    string_agg(DISTINCT (tu.path)::text, ';'::text) AS archive_path,
    e.created AS record_created_date,
    e.updated AS record_updated_date,
    e.textsearchable_index_col
   FROM ((((((((((((((((((((((((((tdis_element e
     JOIN tdis_member mem ON ((e.id = mem.element_id)))
     JOIN tdis_artifact a ON ((mem.element_id = a.element_id)))
     JOIN tdis_users u ON ((e.user_id = u.contact_id)))
     JOIN tdis_collection col ON ((mem.collection_id = col.element_id)))
     JOIN tdis_element col_e ON ((col.element_id = col_e.id)))
     JOIN tdis_status s ON ((mem.status_id = s.id)))
     JOIN tdis_jurisdiction col_j ON ((col.jurisdiction_id = col_j.id)))
     JOIN tdis_jurisdiction_type col_jt ON ((col_j.jurisdiction_type_id = col_jt.id)))
     LEFT JOIN tdis_member_jurisdiction mj ON ((mj.member_id = mem.element_id)))
     LEFT JOIN tdis_jurisdiction j ON ((mj.jurisdiction_id = j.id)))
     LEFT JOIN tdis_jurisdiction_type jt ON ((j.jurisdiction_type_id = jt.id)))
     LEFT JOIN tdis_element_contact ec ON ((e.id = ec.element_id)))
     LEFT JOIN tdis_endpoint_type ept ON ((mem.end_point_type_id = ept.id)))
     LEFT JOIN tdis_distribution_method dm ON ((mem.distribution_method_id = dm.id)))
     LEFT JOIN tdis_update_interval ui ON ((mem.update_interval_id = ui.id)))
     LEFT JOIN tdis_software sw ON ((mem.software_id = sw.id)))
     LEFT JOIN tdis_agency pda ON ((mem.publisher_distributor_agency_id = pda.id)))
     LEFT JOIN tdis_controlled_vocabulary cv ON ((e.controlled_vocabulary_id = cv.id)))
     LEFT JOIN tdis_member_constraint mc ON ((mem.element_id = mc.member_id)))
     LEFT JOIN tdis_constraint con ON ((mc.constraint_id = con.id)))
     LEFT JOIN tdis_accessibility acc ON ((mem.accessibility_id = acc.id)))
     LEFT JOIN tdis_data_stage ds ON ((mem.data_stage_id = ds.id)))
     LEFT JOIN tdis_file_format ff ON ((a.file_format_id = ff.id)))
     LEFT JOIN tdis_agency pss ON ((a.process_source_id = pss.id)))
     LEFT JOIN tdis_latest_status_updates lsu ON ((mem.element_id = lsu.member_id)))
     LEFT JOIN tdis_upload tu ON (((e.id = tu.element_id) AND (tu.type_id = 3))))
  WHERE ((e.deleted = false) AND ((e.element_type)::text = 'TA'::text))
  GROUP BY (concat(e.element_type, '-', e.guid)), e.id, u.username, u.first_name, u.last_name, u.contact_id, cv.name, col.element_id, col_e.title, col_j.name, col_j.code, col_jt.name, e.created, e.updated, e.title, e.description, e.purpose, mem.element_last_updated, mem.element_created, mem.geom, e.custody_chain, e.license, e.keyword_term, e.metadata_date, s.name, ept.name, dm.name, ui.name, mem.documentation_filenames, pda.name, pda.id, mem.creator, mem.distribution_url, sw.name, sw.distributor, mem.software_version, (concat(col_e.element_type, '-', col_e.guid)), mem.time_periods, lsu.most_recent_update, e.textsearchable_index_col, acc.score, acc.description, ds.score, ds.description, ff.name, a.process_date, a.process_description, pss.name;

Columns

View Definition

Possibly Referenced Tables/Views