--
--

SET escape_string_warning = off;

--
-- Name: drugref2; Type: DATABASE; Schema: -; Owner: postgres
--

CREATE DATABASE drugref2 WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_GB.UTF-8' LC_CTYPE = 'en_GB.UTF-8';


ALTER DATABASE drugref2 OWNER TO postgres;

\connect drugref2

SET escape_string_warning = off;

--
-- Name: holbrook; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA holbrook;


ALTER SCHEMA holbrook OWNER TO postgres;

--
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE PROCEDURAL LANGUAGE plpgsql;


ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;




--
-- Name: interactions; Type: TABLE; Schema: holbrook; Owner: postgres; Tablespace: 
--

CREATE TABLE interactions (
    id integer NOT NULL,
    affectingatc character varying(7),
    affectedatc character varying(7),
    effect character(1),
    significance character(1),
    evidence character(1),
    comment text,
    affectingdrug text,
    affecteddrug text
);


ALTER TABLE holbrook.interactions OWNER TO postgres;

--
-- Name: interactions_id_seq; Type: SEQUENCE; Schema: holbrook; Owner: postgres
--

CREATE SEQUENCE interactions_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE holbrook.interactions_id_seq OWNER TO postgres;

--
-- Name: interactions_id_seq; Type: SEQUENCE OWNED BY; Schema: holbrook; Owner: postgres
--

ALTER SEQUENCE interactions_id_seq OWNED BY interactions.id;



--
-- Name: cd2_companies; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE cd2_companies (
    mfr_code character varying(5),
    company_code integer,
    company_name character varying(80),
    company_type character varying(40),
    suite_number character varying(20),
    street_name character varying(80),
    city_name character varying(60),
    province character varying(40),
    country character varying(40),
    postal_code character varying(20),
    post_office_box character varying(15)
);


ALTER TABLE public.cd2_companies OWNER TO postgres;

--
-- Name: cd_active_ingredients; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE cd_active_ingredients (
    drug_code integer,
    active_ingredient_code integer,
    ingredient character varying(240) DEFAULT NULL::character varying,
    ingredient_supplied_ind character(1) DEFAULT NULL::bpchar,
    strength character varying(20) DEFAULT NULL::character varying,
    strength_unit character varying(40) DEFAULT NULL::character varying,
    strength_type character varying(40) DEFAULT NULL::character varying,
    dosage_value character varying(20) DEFAULT NULL::character varying,
    base character(1) DEFAULT NULL::bpchar,
    dosage_unit character varying(40) DEFAULT NULL::character varying,
    notes text
);


ALTER TABLE public.cd_active_ingredients OWNER TO postgres;

--
-- Name: cd_companies; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE cd_companies (
    drug_code integer,
    mfr_code character varying(5) DEFAULT NULL::character varying,
    company_code integer,
    company_name character varying(80) DEFAULT NULL::character varying,
    company_type character varying(40) DEFAULT NULL::character varying,
    address_mailing_flag character(1) DEFAULT NULL::bpchar,
    address_billing_flag character(1) DEFAULT NULL::bpchar,
    address_notification_flag character(1) DEFAULT NULL::bpchar,
    address_other character varying(20) DEFAULT NULL::character varying,
    suite_number character varying(20) DEFAULT NULL::character varying,
    street_name character varying(80) DEFAULT NULL::character varying,
    city_name character varying(60) DEFAULT NULL::character varying,
    province character varying(40) DEFAULT NULL::character varying,
    country character varying(40) DEFAULT NULL::character varying,
    postal_code character varying(20) DEFAULT NULL::character varying,
    post_office_box character varying(15) DEFAULT NULL::character varying
);


ALTER TABLE public.cd_companies OWNER TO postgres;

--
-- Name: cd_drug_product; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE cd_drug_product (
    drug_code integer,
    product_categorization character varying(80) DEFAULT NULL::character varying,
    class character varying(40) DEFAULT NULL::character varying,
    drug_identification_number character varying(8) DEFAULT NULL::character varying,
    brand_name character varying(200) DEFAULT NULL::character varying,
    gp_flag character(1) DEFAULT NULL::bpchar,
    accession_number character varying(5) DEFAULT NULL::character varying,
    number_of_ais character varying(10) DEFAULT NULL::character varying,
    last_update_date date,
    ai_group_no character varying(10) DEFAULT NULL::character varying,
    company_code integer
);


ALTER TABLE public.cd_drug_product OWNER TO postgres;

--
-- Name: cd_drug_search; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE cd_drug_search (
    id integer NOT NULL,
    drug_code character varying(30),
    category integer,
    name text
);


ALTER TABLE public.cd_drug_search OWNER TO postgres;

--
-- Name: cd_drug_search_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE cd_drug_search_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.cd_drug_search_id_seq OWNER TO postgres;

--
-- Name: cd_drug_search_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE cd_drug_search_id_seq OWNED BY cd_drug_search.id;


--
-- Name: cd_drug_status; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE cd_drug_status (
    drug_code integer,
    current_status_flag character(1) DEFAULT NULL::bpchar,
    status character varying(40) DEFAULT NULL::character varying,
    history_date date
);


ALTER TABLE public.cd_drug_status OWNER TO postgres;

--
-- Name: cd_form; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE cd_form (
    drug_code integer,
    pharm_cd_form_code integer,
    pharmaceutical_cd_form character varying(40) DEFAULT NULL::character varying
);


ALTER TABLE public.cd_form OWNER TO postgres;

--
-- Name: cd_inactive_products; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE cd_inactive_products (
    drug_code integer,
    drug_identification_number character varying(8) DEFAULT NULL::character varying,
    brand_name character varying(200) DEFAULT NULL::character varying,
    history_date date
);


ALTER TABLE public.cd_inactive_products OWNER TO postgres;

--
-- Name: cd_packaging; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE cd_packaging (
    drug_code integer,
    upc character varying(12) DEFAULT NULL::character varying,
    package_size_unit character varying(40) DEFAULT NULL::character varying,
    package_type character varying(40) DEFAULT NULL::character varying,
    package_size character varying(5) DEFAULT NULL::character varying,
    product_inforation character varying(80) DEFAULT NULL::character varying
);


ALTER TABLE public.cd_packaging OWNER TO postgres;

--
-- Name: cd_pharmaceutical_std; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE cd_pharmaceutical_std (
    drug_code integer,
    pharmaceutical_std character varying(40) DEFAULT NULL::character varying
);


ALTER TABLE public.cd_pharmaceutical_std OWNER TO postgres;

--
-- Name: cd_route; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE cd_route (
    drug_code integer,
    route_of_administration_code integer,
    route_of_administration character varying(40) DEFAULT NULL::character varying
);


ALTER TABLE public.cd_route OWNER TO postgres;

--
-- Name: cd_schedule; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE cd_schedule (
    drug_code integer,
    schedule character varying(40) DEFAULT NULL::character varying
);


ALTER TABLE public.cd_schedule OWNER TO postgres;

--
-- Name: cd_therapeutic_class; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE cd_therapeutic_class (
    drug_code integer,
    tc_atc_number character varying(8) DEFAULT NULL::character varying,
    tc_atc character varying(120) DEFAULT NULL::character varying,
    tc_ahfs_number character varying(20) DEFAULT NULL::character varying,
    tc_ahfs character varying(80) DEFAULT NULL::character varying
);


ALTER TABLE public.cd_therapeutic_class OWNER TO postgres;

--
-- Name: cd_veterinary_species; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE cd_veterinary_species (
    drug_code integer,
    vet_species character varying(80) DEFAULT NULL::character varying,
    vet_sub_species character varying(80) DEFAULT NULL::character varying
);


ALTER TABLE public.cd_veterinary_species OWNER TO postgres;

--
-- Name: link_generic_brand; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE link_generic_brand (
    id integer,
    drug_code character varying(30)
);


ALTER TABLE public.link_generic_brand OWNER TO postgres;


--
-- Name: id; Type: DEFAULT; Schema: holbrook; Owner: postgres
--

ALTER TABLE interactions ALTER COLUMN id SET DEFAULT nextval('interactions_id_seq'::regclass);



--
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE cd_drug_search ALTER COLUMN id SET DEFAULT nextval('cd_drug_search_id_seq'::regclass);



--
-- Name: interactions_affectingatc_key; Type: CONSTRAINT; Schema: holbrook; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY interactions
    ADD CONSTRAINT interactions_affectingatc_key UNIQUE (affectingatc, affectedatc, effect);


--
-- Name: interactions_pkey; Type: CONSTRAINT; Schema: holbrook; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY interactions
    ADD CONSTRAINT interactions_pkey PRIMARY KEY (id);



--
-- Name: cd_drug_search_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY cd_drug_search
    ADD CONSTRAINT cd_drug_search_pkey PRIMARY KEY (id);


--
-- Name: cd_company_drug_code_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX cd_company_drug_code_idx ON cd_companies USING btree (drug_code);


--
-- Name: cd_drug_code_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX cd_drug_code_idx ON cd_drug_product USING btree (drug_code);


--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
--

