--
--


-- Started on 2020-03-25 15:37:37 PDT

SELECT pg_catalog.set_config('search_path', '', false);

--
-- TOC entry 9 (class 2615 OID 139727)
-- Name: cpu; Type: SCHEMA; Schema: -; Owner: gastil
--

CREATE SCHEMA cpu;


ALTER SCHEMA cpu OWNER TO gastil;

--
-- TOC entry 225 (class 1255 OID 148407)
-- Name: f_nopredicates(text); Type: FUNCTION; Schema: cpu; Owner: gastil
--

CREATE FUNCTION cpu.f_nopredicates(text) RETURNS text
    LANGUAGE sql
    AS $_$select regexp_replace(regexp_replace(regexp_replace($1,'\[','','g'),'\]','','g'),'[[:digit:]]','','g');$_$;


ALTER FUNCTION cpu.f_nopredicates(text) OWNER TO gastil;


--
-- TOC entry 203 (class 1259 OID 139742)
-- Name: pkg_date_created; Type: TABLE; Schema: cpu; Owner: gastil
--

CREATE TABLE cpu.pkg_date_created (
    pkg_id character varying(32) NOT NULL,
    date_created timestamp without time zone
);


ALTER TABLE cpu.pkg_date_created OWNER TO gastil;

--
-- TOC entry 200 (class 1259 OID 139728)
-- Name: site_pkg_tag_count; Type: TABLE; Schema: cpu; Owner: gastil
--

CREATE TABLE cpu.site_pkg_tag_count (
    site character(3) NOT NULL,
    pkg_id character varying(32) NOT NULL,
    tag character varying(128) NOT NULL,
    count integer
);


ALTER TABLE cpu.site_pkg_tag_count OWNER TO gastil;

--
-- TOC entry 205 (class 1259 OID 141451)
-- Name: tag_nsites_alldates; Type: TABLE; Schema: cpu; Owner: gastil
--

CREATE TABLE cpu.tag_nsites_alldates (
    tag character varying(128) NOT NULL,
    nsites integer
);


ALTER TABLE cpu.tag_nsites_alldates OWNER TO gastil;

--
-- TOC entry 201 (class 1259 OID 139734)
-- Name: vw_count_pkgs_per_site; Type: VIEW; Schema: cpu; Owner: gastil
--

CREATE VIEW cpu.vw_count_pkgs_per_site AS
SELECT count(DISTINCT site_pkg_tag_count.pkg_id) AS n_pkgs, site_pkg_tag_count.site FROM cpu.site_pkg_tag_count GROUP BY site_pkg_tag_count.site ORDER BY site_pkg_tag_count.site;


ALTER TABLE cpu.vw_count_pkgs_per_site OWNER TO gastil;

--
-- TOC entry 202 (class 1259 OID 139738)
-- Name: vw_pkg_id; Type: VIEW; Schema: cpu; Owner: gastil
--

CREATE VIEW cpu.vw_pkg_id AS
SELECT site_pkg_tag_count.pkg_id FROM cpu.site_pkg_tag_count GROUP BY site_pkg_tag_count.pkg_id ORDER BY site_pkg_tag_count.pkg_id;


ALTER TABLE cpu.vw_pkg_id OWNER TO gastil;

--
-- TOC entry 204 (class 1259 OID 141446)
-- Name: vw_wide_tag_count_by_site_sincejan2018; Type: VIEW; Schema: cpu; Owner: gastil
--

CREATE VIEW cpu.vw_wide_tag_count_by_site_sincejan2018 AS
SELECT x.tag, x.n AS "ALL", hja.n AS "AND", arc.n AS "ARC", bes.n AS "BES", ble.n AS "BLE", bnz.n AS "BNZ", cap.n AS "CAP", cce.n AS "CCE", cdr.n AS "CDR", cwt.n AS "CWT", edi.n AS "EDI", fce.n AS "FCE", gce.n AS "GCE", hbr.n AS "HBR", hfr.n AS "HFR", jrn.n AS "JRN", kbs.n AS "KBS", knz.n AS "KNZ", luq.n AS "LUQ", mcm.n AS "MCM", mcr.n AS "MCR", nes.n AS "NES", nin.n AS "NIN", ntl.n AS "NTL", nwk.n AS "NWK", nwt.n AS "NWT", pal.n AS "PAL", pie.n AS "PIE", sbc.n AS "SBC", sev.n AS "SEV", sgs.n AS "SGS", vcr.n AS "VCR" FROM ((((((((((((((((((((((((((((((((SELECT t.tag, sum(t.count) AS n FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) GROUP BY t.tag) x LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'and'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) hja ON (((x.tag)::text = (hja.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'arc'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) arc ON (((x.tag)::text = (arc.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'bes'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) bes ON (((x.tag)::text = (bes.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'ble'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) ble ON (((x.tag)::text = (ble.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'bnz'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) bnz ON (((x.tag)::text = (bnz.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'cap'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) cap ON (((x.tag)::text = (cap.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'cce'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) cce ON (((x.tag)::text = (cce.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'cdr'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) cdr ON (((x.tag)::text = (cdr.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'cwt'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) cwt ON (((x.tag)::text = (cwt.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'edi'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) edi ON (((x.tag)::text = (edi.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'fce'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) fce ON (((x.tag)::text = (fce.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'gce'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) gce ON (((x.tag)::text = (gce.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'hbr'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) hbr ON (((x.tag)::text = (hbr.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'hfr'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) hfr ON (((x.tag)::text = (hfr.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'jrn'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) jrn ON (((x.tag)::text = (jrn.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'kbs'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) kbs ON (((x.tag)::text = (kbs.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'knz'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) knz ON (((x.tag)::text = (knz.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'luq'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) luq ON (((x.tag)::text = (luq.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'mcm'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) mcm ON (((x.tag)::text = (mcm.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'mcr'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) mcr ON (((x.tag)::text = (mcr.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'nes'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) nes ON (((x.tag)::text = (nes.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'nin'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) nin ON (((x.tag)::text = (nin.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'ntl'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) ntl ON (((x.tag)::text = (ntl.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'nwk'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) nwk ON (((x.tag)::text = (nwk.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'nwt'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) nwt ON (((x.tag)::text = (nwt.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'pal'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) pal ON (((x.tag)::text = (pal.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'pie'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) pie ON (((x.tag)::text = (pie.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'sbc'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) sbc ON (((x.tag)::text = (sbc.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'sev'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) sev ON (((x.tag)::text = (sev.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'sgs'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) sgs ON (((x.tag)::text = (sgs.tag)::text))) LEFT JOIN (SELECT sum(t.count) AS n, t.tag FROM (cpu.site_pkg_tag_count t JOIN cpu.pkg_date_created d ON (((t.pkg_id)::text = (d.pkg_id)::text))) WHERE ((t.site ~~ 'vcr'::text) AND (d.date_created > '2018-01-01 00:00:00'::timestamp without time zone)) GROUP BY t.tag) vcr ON (((x.tag)::text = (vcr.tag)::text))) ORDER BY x.n DESC, x.tag;


ALTER TABLE cpu.vw_wide_tag_count_by_site_sincejan2018 OWNER TO gastil;

--
-- TOC entry 206 (class 1259 OID 143759)
-- Name: xpath_scope_count; Type: TABLE; Schema: cpu; Owner: gastil
--

CREATE TABLE cpu.xpath_scope_count (
    xpath text NOT NULL,
    scope character varying(12) NOT NULL,
    n_count integer
);


ALTER TABLE cpu.xpath_scope_count OWNER TO gastil;

--
-- TOC entry 2946 (class 0 OID 0)
-- Dependencies: 206
-- Name: TABLE xpath_scope_count; Type: COMMENT; Schema: cpu; Owner: gastil
--

COMMENT ON TABLE cpu.xpath_scope_count IS 'xpath data from john porter using his python fetchEmlXpaths from June 25, 2019 basis (the 6838 pkgs from pasta).';


--
-- TOC entry 2947 (class 0 OID 0)
-- Dependencies: 206
-- Name: COLUMN xpath_scope_count.scope; Type: COMMENT; Schema: cpu; Owner: gastil
--

COMMENT ON COLUMN cpu.xpath_scope_count.scope IS 'as in knb-lter-xyz';


--
-- TOC entry 2948 (class 0 OID 0)
-- Dependencies: 206
-- Name: COLUMN xpath_scope_count.n_count; Type: COMMENT; Schema: cpu; Owner: gastil
--

COMMENT ON COLUMN cpu.xpath_scope_count.n_count IS 'number of occurances of that xpath in eml docs from that scope as calculated by jp script. Name of col is n_count because count is a reserved sql word.';


--
-- TOC entry 2819 (class 2606 OID 139732)
-- Name: site_pkg_tag_count cpu_site_pkg_tag_count_pk_site_pkg_tag; Type: CONSTRAINT; Schema: cpu; Owner: gastil
--

ALTER TABLE ONLY cpu.site_pkg_tag_count
    ADD CONSTRAINT cpu_site_pkg_tag_count_pk_site_pkg_tag PRIMARY KEY (site, pkg_id, tag);


--
-- TOC entry 2823 (class 2606 OID 141455)
-- Name: tag_nsites_alldates cpu_tag_nsites_pk; Type: CONSTRAINT; Schema: cpu; Owner: gastil
--

ALTER TABLE ONLY cpu.tag_nsites_alldates
    ADD CONSTRAINT cpu_tag_nsites_pk PRIMARY KEY (tag);


--
-- TOC entry 2825 (class 2606 OID 143766)
-- Name: xpath_scope_count pk_xpath_scope_count; Type: CONSTRAINT; Schema: cpu; Owner: gastil
--

ALTER TABLE ONLY cpu.xpath_scope_count
    ADD CONSTRAINT pk_xpath_scope_count PRIMARY KEY (xpath, scope);


--
-- TOC entry 2821 (class 2606 OID 139746)
-- Name: pkg_date_created pkg_date_created_pkey; Type: CONSTRAINT; Schema: cpu; Owner: gastil
--

ALTER TABLE ONLY cpu.pkg_date_created
    ADD CONSTRAINT pkg_date_created_pkey PRIMARY KEY (pkg_id);


-- Completed on 2020-03-25 15:37:43 PDT

--
--

