--
--

-- Started on 2016-03-09 13:46:01 CET

SET escape_string_warning = off;

--
-- TOC entry 311 (class 2612 OID 16606)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE PROCEDURAL LANGUAGE plpgsql;


ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;


--
-- TOC entry 20 (class 1255 OID 16625)
-- Dependencies: 3
-- Name: levenshtein(text, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION levenshtein(text, text) RETURNS integer
    AS '$libdir/fuzzystrmatch', 'levenshtein'
    LANGUAGE c IMMUTABLE STRICT;


ALTER FUNCTION public.levenshtein(text, text) OWNER TO postgres;

--
-- TOC entry 21 (class 1255 OID 16626)
-- Dependencies: 3
-- Name: metaphone(text, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION metaphone(text, integer) RETURNS text
    AS '$libdir/fuzzystrmatch', 'metaphone'
    LANGUAGE c IMMUTABLE STRICT;


ALTER FUNCTION public.metaphone(text, integer) OWNER TO postgres;

--
-- TOC entry 22 (class 1255 OID 16627)
-- Dependencies: 3
-- Name: soundex(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION soundex(text) RETURNS text
    AS '$libdir/fuzzystrmatch', 'soundex'
    LANGUAGE c IMMUTABLE STRICT;


ALTER FUNCTION public.soundex(text) OWNER TO postgres;

--
-- TOC entry 23 (class 1255 OID 16628)
-- Dependencies: 3
-- Name: text_soundex(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION text_soundex(text) RETURNS text
    AS '$libdir/fuzzystrmatch', 'soundex'
    LANGUAGE c;


ALTER FUNCTION public.text_soundex(text) OWNER TO postgres;



--
-- TOC entry 1484 (class 1259 OID 16613)
-- Dependencies: 3
-- Name: country; Type: TABLE; Schema: public; Owner: stgsamplelogin01; Tablespace: 
--

CREATE TABLE country (
    id integer NOT NULL,
    country_code integer NOT NULL,
    name character varying NOT NULL,
    postpaid_tariff integer NOT NULL,
    prepaid_tariff integer NOT NULL
);


ALTER TABLE public.country OWNER TO stgsamplelogin01;

--
-- TOC entry 1485 (class 1259 OID 16619)
-- Dependencies: 3
-- Name: country_alias; Type: TABLE; Schema: public; Owner: stgsamplelogin01; Tablespace: 
--

CREATE TABLE country_alias (
    alias_id integer NOT NULL,
    country_id integer NOT NULL,
    alias_name character varying
);


ALTER TABLE public.country_alias OWNER TO stgsamplelogin01;

--
-- TOC entry 1487 (class 1259 OID 16635)
-- Dependencies: 3
-- Name: log; Type: TABLE; Schema: public; Owner: stgsamplelogin01; Tablespace: 
--

CREATE TABLE log (
    request_date timestamp without time zone NOT NULL,
    user_msisdn character varying NOT NULL,
    msc_address character varying NOT NULL,
    sms_country character varying,
    is_postpaid boolean,
    result_code integer NOT NULL,
    matched_country integer
);


ALTER TABLE public.log OWNER TO stgsamplelogin01;

--
-- TOC entry 1486 (class 1259 OID 16629)
-- Dependencies: 3
-- Name: result; Type: TABLE; Schema: public; Owner: stgsamplelogin01; Tablespace: 
--

CREATE TABLE result (
    code integer NOT NULL,
    description character varying NOT NULL
);


ALTER TABLE public.result OWNER TO stgsamplelogin01;

--
-- TOC entry 1483 (class 1259 OID 16607)
-- Dependencies: 3
-- Name: tariff; Type: TABLE; Schema: public; Owner: stgsamplelogin01; Tablespace: 
--

CREATE TABLE tariff (
    id integer NOT NULL,
    description character varying NOT NULL,
    is_postpaid boolean NOT NULL,
    text character varying NOT NULL
);


ALTER TABLE public.tariff OWNER TO stgsamplelogin01;

--
-- TOC entry 1488 (class 1259 OID 504683713)
-- Dependencies: 3
-- Name: tariff_bk20140630; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE tariff_bk20140630 (
    id integer,
    description character varying,
    is_postpaid boolean,
    text character varying
);


ALTER TABLE public.tariff_bk20140630 OWNER TO postgres;

--
-- TOC entry 1762 (class 2606 OID 16658)
-- Dependencies: 1485 1485 1485
-- Name: country_alias_pkey; Type: CONSTRAINT; Schema: public; Owner: stgsamplelogin01; Tablespace: 
--

ALTER TABLE ONLY country_alias
    ADD CONSTRAINT country_alias_pkey PRIMARY KEY (alias_id, country_id);


--
-- TOC entry 1758 (class 2606 OID 16646)
-- Dependencies: 1484 1484
-- Name: country_country_code_key; Type: CONSTRAINT; Schema: public; Owner: stgsamplelogin01; Tablespace: 
--

ALTER TABLE ONLY country
    ADD CONSTRAINT country_country_code_key UNIQUE (country_code);


--
-- TOC entry 1760 (class 2606 OID 16644)
-- Dependencies: 1484 1484
-- Name: country_pkey; Type: CONSTRAINT; Schema: public; Owner: stgsamplelogin01; Tablespace: 
--

ALTER TABLE ONLY country
    ADD CONSTRAINT country_pkey PRIMARY KEY (id);


--
-- TOC entry 1766 (class 2606 OID 16667)
-- Dependencies: 1487 1487 1487
-- Name: log_pkey; Type: CONSTRAINT; Schema: public; Owner: stgsamplelogin01; Tablespace: 
--

ALTER TABLE ONLY log
    ADD CONSTRAINT log_pkey PRIMARY KEY (request_date, user_msisdn);


--
-- TOC entry 1764 (class 2606 OID 16665)
-- Dependencies: 1486 1486
-- Name: result_pkey; Type: CONSTRAINT; Schema: public; Owner: stgsamplelogin01; Tablespace: 
--

ALTER TABLE ONLY result
    ADD CONSTRAINT result_pkey PRIMARY KEY (code);


--
-- TOC entry 1756 (class 2606 OID 16642)
-- Dependencies: 1483 1483
-- Name: tariff_pkey; Type: CONSTRAINT; Schema: public; Owner: stgsamplelogin01; Tablespace: 
--

ALTER TABLE ONLY tariff
    ADD CONSTRAINT tariff_pkey PRIMARY KEY (id);


--
-- TOC entry 1767 (class 2606 OID 16647)
-- Dependencies: 1484 1483 1755
-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: sample
--

ALTER TABLE ONLY country
    ADD CONSTRAINT "$1" FOREIGN KEY (postpaid_tariff) REFERENCES tariff(id);


--
-- TOC entry 1769 (class 2606 OID 16659)
-- Dependencies: 1485 1484 1759
-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: sample
--

ALTER TABLE ONLY country_alias
    ADD CONSTRAINT "$1" FOREIGN KEY (country_id) REFERENCES country(id);


--
-- TOC entry 1770 (class 2606 OID 16668)
-- Dependencies: 1486 1763 1487
-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: sample
--

ALTER TABLE ONLY log
    ADD CONSTRAINT "$1" FOREIGN KEY (result_code) REFERENCES result(code);


--
-- TOC entry 1768 (class 2606 OID 16652)
-- Dependencies: 1484 1483 1755
-- Name: $2; Type: FK CONSTRAINT; Schema: public; Owner: sample
--

ALTER TABLE ONLY country
    ADD CONSTRAINT "$2" FOREIGN KEY (prepaid_tariff) REFERENCES tariff(id);


--
-- TOC entry 1771 (class 2606 OID 16673)
-- Dependencies: 1484 1759 1487
-- Name: $2; Type: FK CONSTRAINT; Schema: public; Owner: sample
--

ALTER TABLE ONLY log
    ADD CONSTRAINT "$2" FOREIGN KEY (matched_country) REFERENCES country(id);


--
-- TOC entry 1776 (class 0 OID 0)
-- Dependencies: 3
-- 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;


--
-- TOC entry 1777 (class 0 OID 0)
-- Dependencies: 1484
-- Name: country; Type: ACL; Schema: public; Owner: sample
--

REVOKE ALL ON TABLE country FROM PUBLIC;
REVOKE ALL ON TABLE country FROM stgsamplelogin01;
GRANT ALL ON TABLE country TO stgsamplelogin01;
GRANT SELECT ON TABLE country TO sample_read_role;
GRANT ALL ON TABLE country TO sample_write_role;


--
-- TOC entry 1778 (class 0 OID 0)
-- Dependencies: 1485
-- Name: country_alias; Type: ACL; Schema: public; Owner: sample
--

REVOKE ALL ON TABLE country_alias FROM PUBLIC;
REVOKE ALL ON TABLE country_alias FROM stgsamplelogin01;
GRANT ALL ON TABLE country_alias TO stgsamplelogin01;
GRANT SELECT ON TABLE country_alias TO sample_read_role;
GRANT ALL ON TABLE country_alias TO sample_write_role;


--
-- TOC entry 1779 (class 0 OID 0)
-- Dependencies: 1487
-- Name: log; Type: ACL; Schema: public; Owner: sample
--

REVOKE ALL ON TABLE log FROM PUBLIC;
REVOKE ALL ON TABLE log FROM stgsamplelogin01;
GRANT ALL ON TABLE log TO stgsamplelogin01;
GRANT SELECT ON TABLE log TO sample_read_role;
GRANT ALL ON TABLE log TO sample_write_role;


--
-- TOC entry 1780 (class 0 OID 0)
-- Dependencies: 1486
-- Name: result; Type: ACL; Schema: public; Owner: sample
--

REVOKE ALL ON TABLE result FROM PUBLIC;
REVOKE ALL ON TABLE result FROM stgsamplelogin01;
GRANT ALL ON TABLE result TO stgsamplelogin01;
GRANT SELECT ON TABLE result TO sample_read_role;
GRANT ALL ON TABLE result TO sample_write_role;


--
-- TOC entry 1781 (class 0 OID 0)
-- Dependencies: 1483
-- Name: tariff; Type: ACL; Schema: public; Owner: sample
--

REVOKE ALL ON TABLE tariff FROM PUBLIC;
REVOKE ALL ON TABLE tariff FROM stgsamplelogin01;
GRANT ALL ON TABLE tariff TO stgsamplelogin01;
GRANT SELECT ON TABLE tariff TO sample_read_role;
GRANT ALL ON TABLE tariff TO sample_write_role;


-- Completed on 2016-03-09 13:46:01 CET

--
--

