· 5 years ago · Mar 04, 2020, 03:42 PM
1--
2-- PostgreSQL database cluster dump
3--
4
5SET default_transaction_read_only = off;
6
7SET client_encoding = 'UTF8';
8SET standard_conforming_strings = on;
9
10--
11-- Roles
12--
13
14CREATE ROLE postgres;
15ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'md52a29a4f7eb0a98abca0992ca3fb555b6';
16
17
18
19
20
21
22--
23-- Databases
24--
25
26--
27-- Database "template1" dump
28--
29
30\connect template1
31
32--
33-- PostgreSQL database dump
34--
35
36-- Dumped from database version 12.1
37-- Dumped by pg_dump version 12.1
38
39SET statement_timeout = 0;
40SET lock_timeout = 0;
41SET idle_in_transaction_session_timeout = 0;
42SET client_encoding = 'UTF8';
43SET standard_conforming_strings = on;
44SELECT pg_catalog.set_config('search_path', '', false);
45SET check_function_bodies = false;
46SET xmloption = content;
47SET client_min_messages = warning;
48SET row_security = off;
49
50--
51-- PostgreSQL database dump complete
52--
53
54--
55-- Database "dcb" dump
56--
57
58--
59-- PostgreSQL database dump
60--
61
62-- Dumped from database version 12.1
63-- Dumped by pg_dump version 12.1
64
65SET statement_timeout = 0;
66SET lock_timeout = 0;
67SET idle_in_transaction_session_timeout = 0;
68SET client_encoding = 'UTF8';
69SET standard_conforming_strings = on;
70SELECT pg_catalog.set_config('search_path', '', false);
71SET check_function_bodies = false;
72SET xmloption = content;
73SET client_min_messages = warning;
74SET row_security = off;
75
76--
77-- Name: dcb; Type: DATABASE; Schema: -; Owner: postgres
78--
79
80CREATE DATABASE dcb WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'English_United States.1252' LC_CTYPE = 'English_United States.1252';
81
82
83ALTER DATABASE dcb OWNER TO postgres;
84
85\connect dcb
86
87SET statement_timeout = 0;
88SET lock_timeout = 0;
89SET idle_in_transaction_session_timeout = 0;
90SET client_encoding = 'UTF8';
91SET standard_conforming_strings = on;
92SELECT pg_catalog.set_config('search_path', '', false);
93SET check_function_bodies = false;
94SET xmloption = content;
95SET client_min_messages = warning;
96SET row_security = off;
97
98--
99-- Name: uuid-ossp; Type: EXTENSION; Schema: -; Owner: -
100--
101
102CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;
103
104
105--
106-- Name: EXTENSION "uuid-ossp"; Type: COMMENT; Schema: -; Owner:
107--
108
109COMMENT ON EXTENSION "uuid-ossp" IS 'generate universally unique identifiers (UUIDs)';
110
111
112--
113-- Name: already_subscribed(); Type: FUNCTION; Schema: public; Owner: postgres
114--
115
116CREATE FUNCTION public.already_subscribed() RETURNS trigger
117 LANGUAGE plpgsql
118 AS $$
119BEGIN
120IF (select count(*) from subscription where
121 operator_id = NEW.operator_id and
122 service_id = NEW.service_id and
123 pricepoint_id = NEW.pricepoint_id and
124 msisdn = NEW.msisdn) > 0 THEN
125NEW = NULL;
126END IF;
127RETURN NEW;
128END;
129$$;
130
131
132ALTER FUNCTION public.already_subscribed() OWNER TO postgres;
133
134--
135-- Name: check_if_exists(integer, integer, uuid, text); Type: FUNCTION; Schema: public; Owner: postgres
136--
137
138CREATE FUNCTION public.check_if_exists(_service_id integer, _operator_id integer, _pricepoint_id uuid, _msisdn text) RETURNS boolean
139 LANGUAGE plpgsql
140 AS $$
141
142 BEGIN
143
144 if(
145 select exists (select true from subscription WHERE service_id = _service_id
146 AND operator_id = _operator_id
147 AND pricepoint_id = _pricepoint_id
148 AND msisdn = _msisdn) = true)
149
150 then return true;
151 else
152 return false;
153 END IF;
154
155
156
157
158
159
160 END;
161$$;
162
163
164ALTER FUNCTION public.check_if_exists(_service_id integer, _operator_id integer, _pricepoint_id uuid, _msisdn text) OWNER TO postgres;
165
166--
167-- Name: event_handler(); Type: FUNCTION; Schema: public; Owner: postgres
168--
169
170CREATE FUNCTION public.event_handler() RETURNS trigger
171 LANGUAGE plpgsql
172 AS $$
173DECLARE
174_free_trial INTEGER;
175_period INTEGER;
176_send_notification boolean;
177_is_required boolean;
178_provider_id int;
179BEGIN
180 -- check if service provider expects a notification
181 _provider_id := (select provider_id from service where _id = OLD.service_id);
182 _send_notification := (select is_notified from provider where _id = _provider_id);
183 IF ( NEW.status = 'subscribed') THEN
184
185 _free_trial := (select free_trial from pricepoint where _id = OLD.pricepoint_id);
186 UPDATE subscription set billing_date = now() + (_free_trial::INTEGER || ' hours')::INTERVAL
187 where _id = OLD._id;
188 -- check if welcome sms required by operator
189 _is_required := (select required_sms->>'welcome' from operator where _id = OLD.operator_id);
190 IF (_is_required) THEN
191 INSERT INTO public.event(subscription_id, event_name, sms_key) VALUES (NEW._id, 'success_subscribed', 'welcome');
192 END IF;
193
194 IF (_send_notification) THEN
195 INSERT INTO public.notification(service_provider_id, subscription_id, event_name) VALUES
196 (_provider_id, OLD._id, 'welcome');
197 END IF;
198 ELSIF (NEW.status = 'unsubscribed' AND OLD.status is distinct from 'unsubscribed') THEN
199 -- check if goodbye sms required by operator
200 _is_required := (select required_sms->>'goodbye' from operator where _id = OLD.operator_id);
201 IF (_is_required) THEN
202 INSERT INTO public.event(subscription_id, event_name, sms_key) VALUES (NEW._id, 'unsubscribed', 'goodbye');
203 END IF;
204
205 IF (_send_notification) THEN
206 INSERT INTO public.notification(service_provider_id, subscription_id, event_name) VALUES
207 (_provider_id, OLD._id, 'goodbye');
208 END IF;
209 END IF;
210
211 IF (NEW.last_success_billing is distinct from OLD.last_success_billing) THEN
212 -- check if billing sms required by operator
213 _is_required := (select required_sms->>'billing' from operator where _id = OLD.operator_id);
214 _period := (select period from pricepoint where _id = OLD.pricepoint_id);
215 IF (_is_required) THEN
216 INSERT INTO public.event(subscription_id, event_name, sms_key) VALUES (NEW._id, 'success_billed', 'billing');
217 END IF;
218 IF (_send_notification) THEN
219 INSERT INTO public.notification(service_provider_id, subscription_id, event_name) VALUES
220 (_provider_id, OLD._id, 'billing');
221 END IF;
222 UPDATE subscription set billing_date = now() + (_period::INTEGER || ' hours')::INTERVAL
223 where _id = OLD._id;
224 END IF;
225
226 IF (OLD.active is distinct from NEW.active) THEN
227 IF (_send_notification) THEN
228 INSERT INTO public.notification(service_provider_id, subscription_id, event_name) VALUES
229 (_provider_id, OLD._id, 'status');
230 END IF;
231 END IF;
232
233RETURN NEW;
234END;
235$$;
236
237
238ALTER FUNCTION public.event_handler() OWNER TO postgres;
239
240--
241-- Name: get_by_policy_unsubscribers(); Type: FUNCTION; Schema: public; Owner: postgres
242--
243
244CREATE FUNCTION public.get_by_policy_unsubscribers() RETURNS TABLE(id uuid, msisdn text, operator_id integer, parking_period integer, last_success_billing timestamp without time zone)
245 LANGUAGE plpgsql
246 AS $$
247BEGIN
248DROP TABLE IF EXISTS by_policy_unsubscribers;
249CREATE TEMP TABLE by_policy_unsubscribers
250 (
251 subscription_id uuid,
252 msisdn text,
253 operator_id integer,
254 parking_period integer,
255 last_success_billing timestamp without time zone
256 );
257
258INSERT INTO by_policy_unsubscribers
259 SELECT SUB._id as id, SUB.msisdn,SUB.operator_id, OP.parking_period, SUB.last_success_billing
260 FROM subscription SUB
261 LEFT JOIN operator OP ON SUB.operator_id = OP._id
262 WHERE SUB.status = 'subscribed'
263 AND (
264 (SUB.last_success_billing IS NULL AND NOW() >= SUB.created_date + (OP.parking_period::INTEGER || ' hours')::INTERVAL)
265 OR
266 (SUB.last_success_billing IS NOT NULL AND NOW() >= SUB.last_success_billing + (OP.parking_period::INTEGER || ' hours')::INTERVAL)
267 );
268
269-- Deactivate and set status to unsubscribed
270UPDATE subscription SUB
271SET status = 'unsubscribed', active = false
272FROM by_policy_unsubscribers TMP
273WHERE SUB._id = TMP.subscription_id;
274
275return query select * from by_policy_unsubscribers;
276END;
277$$;
278
279
280ALTER FUNCTION public.get_by_policy_unsubscribers() OWNER TO postgres;
281
282--
283-- Name: get_debt_due_subscribers(); Type: FUNCTION; Schema: public; Owner: postgres
284--
285
286CREATE FUNCTION public.get_debt_due_subscribers() RETURNS TABLE(msisdn text, operator_id integer, subscription_id uuid, billing_date timestamp without time zone, retrial_interval integer, amount numeric, billing_parameters json)
287 LANGUAGE plpgsql
288 AS $$
289BEGIN
290DROP TABLE IF EXISTS debt_due_subscribers;
291CREATE TEMP TABLE debt_due_subscribers
292 (
293 msisdn text,
294 operator_id integer,
295 subscription_id uuid,
296 billing_date timestamp without time zone,
297 retrial_interval integer,
298 amount numeric(7,3),
299 billing_parameters json
300 );
301
302-- Subscribers to be billed: reached billing date, and within parking period
303INSERT INTO debt_due_subscribers
304 SELECT SUB.msisdn, SUB.operator_id, SUB._id, SUB.billing_date, OP.retrial_interval, PP.amount, PP.billing_parameters
305 FROM subscription SUB
306 LEFT JOIN operator OP ON SUB.operator_id = OP._id
307 LEFT JOIN pricepoint PP ON PP.operator_id = OP._id
308 WHERE NOW() >= SUB.billing_date
309 AND SUB.status = 'subscribed'
310 AND (
311 (SUB.last_success_billing IS NULL AND NOW() < SUB.created_date + (OP.parking_period::INTEGER || ' hours')::INTERVAL)
312 OR
313 (SUB.last_success_billing IS NOT NULL AND NOW() < SUB.last_success_billing + (OP.parking_period::INTEGER || ' hours')::INTERVAL)
314 );
315
316-- Set user as in active and set the next billing attempt time in case payment failed
317UPDATE subscription SUB
318SET billing_date = SUB.billing_date + (TMP.retrial_interval::INTEGER || ' hours')::INTERVAL,
319 active = false
320FROM debt_due_subscribers TMP
321WHERE SUB._id = TMP.subscription_id;
322
323return query select * from debt_due_subscribers;
324END;
325$$;
326
327
328ALTER FUNCTION public.get_debt_due_subscribers() OWNER TO postgres;
329
330--
331-- Name: get_event_driven_messages(); Type: FUNCTION; Schema: public; Owner: postgres
332--
333
334CREATE FUNCTION public.get_event_driven_messages() RETURNS TABLE(_id uuid, subscription_id uuid, msisdn text, event_name text, sms_key text, operator_id integer, service_name text, message text, amount numeric, currency character, billing_date timestamp without time zone)
335 LANGUAGE plpgsql
336 AS $$
337
338BEGIN
339DROP TABLE IF EXISTS event_driven_messages;
340CREATE TEMP TABLE event_driven_messages
341 (
342 _id uuid,
343subscription_id uuid,
344 msisdn text,
345 event_name text,
346 sms_key text,
347 operator_id integer,
348 service_name text,
349 message text,
350 amount numeric(7,3),
351 currency character(3),
352 billing_date timestamp without time zone
353 );
354
355INSERT INTO event_driven_messages
356 SELECT EVT._id, EVT.subscription_id, SUB.msisdn, EVT.event_name,EVT.sms_key,SUB.operator_id,
357 SRV.name, MSG.text, PPT.amount, PPT.currency, SUB.billing_date
358 FROM event EVT
359 LEFT JOIN subscription SUB ON EVT.subscription_id = SUB._id
360 LEFT JOIN service SRV ON SUB.service_id = SRV._id
361 LEFT JOIN message MSG ON EVT.sms_key = MSG.key
362 LEFT JOIN pricepoint PPT ON SUB.pricepoint_id = PPT._id
363
364 WHERE
365 SUB.language_id = MSG.language_id AND
366 EVT.is_processed = false
367 limit 50;
368
369-- mark as processed
370UPDATE event EVT
371SET is_processed = true, processed_date = now()
372FROM event_driven_messages TMP
373WHERE EVT._id = TMP._id;
374
375return query select * from event_driven_messages;
376END;
377$$;
378
379
380ALTER FUNCTION public.get_event_driven_messages() OWNER TO postgres;
381
382--
383-- Name: get_notifications(); Type: FUNCTION; Schema: public; Owner: postgres
384--
385
386CREATE FUNCTION public.get_notifications() RETURNS TABLE(_id integer, iseligible boolean, event text, subscription_id uuid, currency character, amount numeric, billing_date timestamp without time zone)
387 LANGUAGE plpgsql
388 AS $$
389
390BEGIN
391DROP TABLE IF EXISTS pending_notifications;
392CREATE TEMP TABLE pending_notifications
393 (
394 _id int,
395 isEligible boolean,
396 event text,
397 subscription_id uuid,
398 currency character(3),
399 amount numeric(7,3),
400 billing_date timestamp without time zone
401 );
402
403INSERT INTO pending_notifications
404 SELECT
405 NOTF._id,
406 SUB.active as isEligible,
407 NOTF.event_name as event,
408 SUB._id as user,
409 PP.currency,
410 PP.amount,
411 SUB.billing_date
412 FROM notification NOTF
413 LEFT JOIN subscription SUB ON NOTF.subscription_id = SUB._id
414 LEFT JOIN pricepoint PP ON SUB.pricepoint_id = PP._id
415 WHERE NOTF.is_processed = 'false'
416 LIMIT 250;
417-- set is_processed = true to mark as processed
418UPDATE notification NOTF
419SET is_processed = 'true', processed_date = now()
420FROM pending_notifications TMP
421WHERE NOTF._id= TMP._id;
422
423return query select * from pending_notifications;
424END;
425$$;
426
427
428ALTER FUNCTION public.get_notifications() OWNER TO postgres;
429
430--
431-- Name: get_pending_subscribers(); Type: FUNCTION; Schema: public; Owner: postgres
432--
433
434CREATE FUNCTION public.get_pending_subscribers() RETURNS TABLE(id uuid, msisdn text, operator_id integer, service_name text, message text, extra_params json, sms_key text)
435 LANGUAGE plpgsql
436 AS $$
437
438BEGIN
439DROP TABLE IF EXISTS pending_subscribers;
440CREATE TEMP TABLE pending_subscribers
441 (
442 id uuid,
443 msisdn text,
444 operator_id integer,
445 service_name text,
446 message text,
447 extra_params json,
448 sms_key text
449 );
450
451-- Get new subscribers with status pending and increment verification attempts
452-- to indicate that pin has been sent
453INSERT INTO pending_subscribers
454 SELECT SUB._id as id, SUB.msisdn,SUB.operator_id, SRV.name, MSG.text, SUB.extra_params, MSG.key
455 FROM subscription SUB
456 LEFT JOIN pricepoint PP ON SUB.pricepoint_id = PP._id
457 LEFT JOIN service SRV ON SUB.service_id = SRV._id
458 LEFT JOIN message MSG ON SUB.service_id = MSG.service_id
459 LEFT JOIN operator OP ON SUB.operator_id = OP._id
460 WHERE
461 SUB.status = 'pending' AND
462 SUB.last_success_billing IS NULL AND
463 SUB.verification_attempts < 5 AND
464 MSG.key = 'pin' AND
465 OP.required_sms->>'pin' = 'true' AND
466 PP.flow_type = 2 limit 50;
467
468-- set status to awaiting_verification to mark as processed
469UPDATE subscription SUB
470SET status = 'awaiting_verification'
471
472FROM pending_subscribers TMP
473WHERE SUB._id = TMP.id;
474
475return query select * from pending_subscribers;
476END;
477$$;
478
479
480ALTER FUNCTION public.get_pending_subscribers() OWNER TO postgres;
481
482--
483-- Name: language_fallback(); Type: FUNCTION; Schema: public; Owner: postgres
484--
485
486CREATE FUNCTION public.language_fallback() RETURNS trigger
487 LANGUAGE plpgsql
488 AS $$
489BEGIN
490IF NEW.language_id = 0 THEN
491 NEW.language_id = (SELECT default_language_id FROM operator where _id = NEW.operator_id);
492END IF;
493RETURN NEW;
494END;
495$$;
496
497
498ALTER FUNCTION public.language_fallback() OWNER TO postgres;
499
500--
501-- Name: sms_event_handler(); Type: FUNCTION; Schema: public; Owner: postgres
502--
503
504CREATE FUNCTION public.sms_event_handler() RETURNS trigger
505 LANGUAGE plpgsql
506 AS $$
507DECLARE
508_operator_id integer;
509_service_id integer;
510_action text;
511_subscription_id uuid;
512BEGIN
513 _service_id := (select service_id from pricepoint where _id = OLD.pricepoint_id);
514 IF (_action = 'sub') THEN
515 IF (select count(*) from subscription where
516 operator_id = NEW.operator_id and
517 service_id = NEW.service_id and
518 pricepoint_id = NEW.pricepoint_id and
519 msisdn = NEW.msisdn) = 0 THEN
520 INSERT INTO public.subscription(service_id, operator_id, pricepoint_id, language_id, msisdn, channel)
521 VALUES ( _service_id, OLD.operator_id, OLD.pricepoint_id, OLD.language_id, OLD.msisdn, 'sms');
522 ELSE
523 INSERT INTO public.event(subscription_id, event_name, sms_key)
524 VALUES (NEW._id, 'already_subscribed', 'fallback');
525
526 END IF;
527 ELSE IF (_action = 'unsub') THEN
528 IF (select count(*) from subscription where
529 operator_id = NEW.operator_id and
530 service_id = NEW.service_id and
531 pricepoint_id = NEW.pricepoint_id and
532 msisdn = NEW.msisdn) = 0 THEN
533 INSERT INTO public.event(subscription_id, event_name, sms_key)
534 VALUES (NEW._id, 'not_subscribed', 'fallback');
535 ELSE
536 _subscription_id := (select _id from pricepoint where
537 operator_id = NEW.operator_id and
538 service_id = NEW.service_id and
539 pricepoint_id = NEW.pricepoint_id and
540 msisdn = NEW.msisdn);
541 UPDATE public.subscription set staus = 'unsubscribed' WHERE
542 _id = _subscription_id;
543 END IF;
544 ELSE
545 INSERT INTO public.event(subscription_id, event_name, sms_key)
546 VALUES (NEW._id, 'fallback', 'fallback');
547 END IF;
548
549 END IF;
550
551RETURN NEW;
552END;
553$$;
554
555
556ALTER FUNCTION public.sms_event_handler() OWNER TO postgres;
557
558--
559-- Name: sms_subscription(integer, integer, uuid, text, jsonb, text); Type: FUNCTION; Schema: public; Owner: postgres
560--
561
562CREATE FUNCTION public.sms_subscription(serviceid integer, operatorid integer, pricepointid uuid, _msisdn text, extraparams jsonb, _status text) RETURNS integer
563 LANGUAGE plpgsql
564 AS $$
565 DECLARE
566 _exists integer := (select count(*) from subscription where
567 pricepoint_id = pricepointId AND
568 service_id = serviceId AND
569 operator_id = operatorId AND
570 msisdn = _msisdn);
571 BEGIN
572 IF _exists = 0 THEN
573 RETURN 401;
574
575 ELSE
576 INSERT INTO public.subscription(service_id, operator_id, pricepoint_id, billing_date, status, extra_params, msisdn, channel)
577 VALUES (serviceId, operatorId, pricepointId, now(), _status, extraParams, _msisdn, 'sms');
578 RETURN 200;
579 END IF;
580 END;
581$$;
582
583
584ALTER FUNCTION public.sms_subscription(serviceid integer, operatorid integer, pricepointid uuid, _msisdn text, extraparams jsonb, _status text) OWNER TO postgres;
585
586--
587-- Name: sms_subscription(integer, integer, uuid, text, text, text); Type: FUNCTION; Schema: public; Owner: postgres
588--
589
590CREATE FUNCTION public.sms_subscription(serviceid integer, operatorid integer, pricepointid uuid, _msisdn text, extraparams text, _status text) RETURNS integer
591 LANGUAGE plpgsql
592 AS $$
593 DECLARE
594 _exists integer := (select count(*) from subscription where
595 pricepoint_id = pricepointId AND
596 service_id = serviceId AND
597 operator_id = operatorId AND
598 msisdn = _msisdn);
599 BEGIN
600 IF _exists > 0 THEN
601 RETURN 401;
602
603 ELSE
604 INSERT INTO public.subscription(service_id, operator_id, pricepoint_id, billing_date, status, extra_params, msisdn, channel)
605 VALUES (serviceId, operatorId, pricepointId, now(), _status, extraParams::json, _msisdn, 'sms');
606 RETURN 200;
607 END IF;
608 END;
609$$;
610
611
612ALTER FUNCTION public.sms_subscription(serviceid integer, operatorid integer, pricepointid uuid, _msisdn text, extraparams text, _status text) OWNER TO postgres;
613
614--
615-- Name: validate_subscription_params(); Type: FUNCTION; Schema: public; Owner: postgres
616--
617
618CREATE FUNCTION public.validate_subscription_params() RETURNS trigger
619 LANGUAGE plpgsql
620 AS $$
621DECLARE
622
623 serviceId_valid boolean := (select exists(select 1 from service where _id= NEW.service_id));
624 operatorId_valid boolean := (select exists(select 1 from operator where _id=NEW.operator_id));
625 pricepointId_valid boolean := (select exists(select 1 from pricepoint where _id=NEW.pricepoint_id));
626BEGIN
627
628IF serviceId_valid = false OR operatorId_valid = false OR pricepointId_valid = false
629
630THEN
631 RETURN NULL;
632END IF;
633RETURN NEW;
634END;
635$$;
636
637
638ALTER FUNCTION public.validate_subscription_params() OWNER TO postgres;
639
640--
641-- Name: verify_pin(text, uuid); Type: FUNCTION; Schema: public; Owner: postgres
642--
643
644CREATE FUNCTION public.verify_pin(pin_to_check text, subscription_id uuid) RETURNS integer
645 LANGUAGE plpgsql
646 AS $$
647 DECLARE
648 validation json := (select extra_params->>'validation' from "subscription" where _id = subscription_id);
649 status text := (select status from subscription where _id = subscription_id);
650 pin text;
651 _verification_attempts integer := (select verification_attempts from subscription where _id = subscription_id);
652 valid_until timestamp;
653 BEGIN
654 -- max verification attempts reached
655 IF _verification_attempts = 5 THEN
656 RETURN 403;
657
658 ELSE
659
660 valid_until := (select validation->>'valid_until');
661 pin := (select validation->>'pin');
662 -- Already verified earlier
663 IF status = 'subscribed' THEN
664 RETURN 402;
665 -- Expired pin
666 ELSEIF NOW()> valid_until THEN
667 RETURN 401;
668 -- Success
669 ELSIF pin_to_check = pin THEN
670 UPDATE subscription set status = 'subscribed', active = true,
671 modified_date = now()
672 where _id = subscription_id;
673 RETURN 200;
674
675 ELSE
676 -- Incorrect pin
677 UPDATE subscription set verification_attempts = verification_attempts +1,
678 modified_date = now()
679 where _id = subscription_id;
680 RETURN 400;
681 END IF;
682 END IF;
683 END;
684$$;
685
686
687ALTER FUNCTION public.verify_pin(pin_to_check text, subscription_id uuid) OWNER TO postgres;
688
689SET default_tablespace = '';
690
691SET default_table_access_method = heap;
692
693--
694-- Name: category; Type: TABLE; Schema: public; Owner: postgres
695--
696
697CREATE TABLE public.category (
698 _id integer NOT NULL,
699 name character(20) NOT NULL
700);
701
702
703ALTER TABLE public.category OWNER TO postgres;
704
705--
706-- Name: category__id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
707--
708
709ALTER TABLE public.category ALTER COLUMN _id ADD GENERATED BY DEFAULT AS IDENTITY (
710 SEQUENCE NAME public.category__id_seq
711 START WITH 1
712 INCREMENT BY 1
713 NO MINVALUE
714 NO MAXVALUE
715 CACHE 1
716);
717
718
719--
720-- Name: country; Type: TABLE; Schema: public; Owner: postgres
721--
722
723CREATE TABLE public.country (
724 _id integer NOT NULL,
725 name character(20) NOT NULL,
726 abbreviation character(2) NOT NULL
727);
728
729
730ALTER TABLE public.country OWNER TO postgres;
731
732--
733-- Name: country__id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
734--
735
736CREATE SEQUENCE public.country__id_seq
737 AS integer
738 START WITH 1
739 INCREMENT BY 1
740 NO MINVALUE
741 NO MAXVALUE
742 CACHE 1;
743
744
745ALTER TABLE public.country__id_seq OWNER TO postgres;
746
747--
748-- Name: country__id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
749--
750
751ALTER SEQUENCE public.country__id_seq OWNED BY public.country._id;
752
753
754--
755-- Name: event; Type: TABLE; Schema: public; Owner: postgres
756--
757
758CREATE TABLE public.event (
759 _id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
760 subscription_id uuid NOT NULL,
761 event_name text NOT NULL,
762 sms_key text,
763 is_processed boolean DEFAULT false,
764 processed_date timestamp without time zone,
765 CONSTRAINT allowed_event_name CHECK ((event_name = ANY (ARRAY[('success_subscribed'::bpchar)::text, ('success_billed'::bpchar)::text, ('unsubscribed'::bpchar)::text])))
766);
767
768
769ALTER TABLE public.event OWNER TO postgres;
770
771--
772-- Name: flow_type; Type: TABLE; Schema: public; Owner: postgres
773--
774
775CREATE TABLE public.flow_type (
776 _id integer NOT NULL,
777 name character(20) NOT NULL
778);
779
780
781ALTER TABLE public.flow_type OWNER TO postgres;
782
783--
784-- Name: flow_type__id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
785--
786
787CREATE SEQUENCE public.flow_type__id_seq
788 AS integer
789 START WITH 1
790 INCREMENT BY 1
791 NO MINVALUE
792 NO MAXVALUE
793 CACHE 1;
794
795
796ALTER TABLE public.flow_type__id_seq OWNER TO postgres;
797
798--
799-- Name: flow_type__id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
800--
801
802ALTER SEQUENCE public.flow_type__id_seq OWNED BY public.flow_type._id;
803
804
805--
806-- Name: language; Type: TABLE; Schema: public; Owner: postgres
807--
808
809CREATE TABLE public.language (
810 _id integer NOT NULL,
811 name character(20) NOT NULL,
812 abbreviation character(2) NOT NULL
813);
814
815
816ALTER TABLE public.language OWNER TO postgres;
817
818--
819-- Name: language__id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
820--
821
822CREATE SEQUENCE public.language__id_seq
823 AS integer
824 START WITH 1
825 INCREMENT BY 1
826 NO MINVALUE
827 NO MAXVALUE
828 CACHE 1;
829
830
831ALTER TABLE public.language__id_seq OWNER TO postgres;
832
833--
834-- Name: language__id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
835--
836
837ALTER SEQUENCE public.language__id_seq OWNED BY public.language._id;
838
839
840--
841-- Name: message; Type: TABLE; Schema: public; Owner: postgres
842--
843
844CREATE TABLE public.message (
845 _id integer NOT NULL,
846 language_id smallint,
847 text text,
848 key text,
849 service_id integer,
850 pricepoint_id uuid,
851 operator_id integer
852);
853
854
855ALTER TABLE public.message OWNER TO postgres;
856
857--
858-- Name: message__id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
859--
860
861CREATE SEQUENCE public.message__id_seq
862 AS integer
863 START WITH 1
864 INCREMENT BY 1
865 NO MINVALUE
866 NO MAXVALUE
867 CACHE 1;
868
869
870ALTER TABLE public.message__id_seq OWNER TO postgres;
871
872--
873-- Name: message__id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
874--
875
876ALTER SEQUENCE public.message__id_seq OWNED BY public.message._id;
877
878
879--
880-- Name: notification; Type: TABLE; Schema: public; Owner: postgres
881--
882
883CREATE TABLE public.notification (
884 _id integer NOT NULL,
885 service_provider_id integer NOT NULL,
886 subscription_id uuid NOT NULL,
887 event_name text NOT NULL,
888 event_date timestamp without time zone DEFAULT now(),
889 is_processed boolean DEFAULT false,
890 processed_date timestamp without time zone
891);
892
893
894ALTER TABLE public.notification OWNER TO postgres;
895
896--
897-- Name: notification__id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
898--
899
900CREATE SEQUENCE public.notification__id_seq
901 AS integer
902 START WITH 1
903 INCREMENT BY 1
904 NO MINVALUE
905 NO MAXVALUE
906 CACHE 1;
907
908
909ALTER TABLE public.notification__id_seq OWNER TO postgres;
910
911--
912-- Name: notification__id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
913--
914
915ALTER SEQUENCE public.notification__id_seq OWNED BY public.notification._id;
916
917
918--
919-- Name: operator; Type: TABLE; Schema: public; Owner: postgres
920--
921
922CREATE TABLE public.operator (
923 _id integer NOT NULL,
924 name character(20) NOT NULL,
925 country_id integer NOT NULL,
926 parking_period integer NOT NULL,
927 grace_period integer NOT NULL,
928 dnd_start time without time zone DEFAULT '00:00:00'::time without time zone NOT NULL,
929 dnd_end time without time zone DEFAULT '23:59:59'::time without time zone NOT NULL,
930 retrial_interval integer DEFAULT 6 NOT NULL,
931 default_language_id integer NOT NULL,
932 required_sms json DEFAULT '{"welcome": true,"billing": true,"pin": true,"goodbye": true}'::json NOT NULL
933);
934
935
936ALTER TABLE public.operator OWNER TO postgres;
937
938--
939-- Name: operator__id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
940--
941
942CREATE SEQUENCE public.operator__id_seq
943 AS integer
944 START WITH 1
945 INCREMENT BY 1
946 NO MINVALUE
947 NO MAXVALUE
948 CACHE 1;
949
950
951ALTER TABLE public.operator__id_seq OWNER TO postgres;
952
953--
954-- Name: operator__id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
955--
956
957ALTER SEQUENCE public.operator__id_seq OWNED BY public.operator._id;
958
959
960--
961-- Name: pricepoint; Type: TABLE; Schema: public; Owner: postgres
962--
963
964CREATE TABLE public.pricepoint (
965 _id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
966 service_id integer NOT NULL,
967 operator_id integer NOT NULL,
968 flow_type integer NOT NULL,
969 free_trial integer DEFAULT 0,
970 period integer NOT NULL,
971 amount numeric(7,3) NOT NULL,
972 currency character(3) NOT NULL,
973 parent_id uuid,
974 billing_parameters json,
975 created_date timestamp without time zone DEFAULT now(),
976 modified_date timestamp without time zone DEFAULT now(),
977 active boolean DEFAULT true
978);
979
980
981ALTER TABLE public.pricepoint OWNER TO postgres;
982
983--
984-- Name: provider; Type: TABLE; Schema: public; Owner: postgres
985--
986
987CREATE TABLE public.provider (
988 _id integer NOT NULL,
989 name character(20) NOT NULL,
990 email character(30) NOT NULL,
991 phone character(20) NOT NULL,
992 portal_username character(20) NOT NULL,
993 portal_password character(20) NOT NULL,
994 auth_username character(20) NOT NULL,
995 auth_password character(20) NOT NULL,
996 created_date timestamp without time zone DEFAULT now(),
997 modified_date timestamp without time zone DEFAULT now(),
998 active boolean DEFAULT true,
999 is_notified boolean DEFAULT false
1000);
1001
1002
1003ALTER TABLE public.provider OWNER TO postgres;
1004
1005--
1006-- Name: provider__id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
1007--
1008
1009CREATE SEQUENCE public.provider__id_seq
1010 AS integer
1011 START WITH 1
1012 INCREMENT BY 1
1013 NO MINVALUE
1014 NO MAXVALUE
1015 CACHE 1;
1016
1017
1018ALTER TABLE public.provider__id_seq OWNER TO postgres;
1019
1020--
1021-- Name: provider__id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
1022--
1023
1024ALTER SEQUENCE public.provider__id_seq OWNED BY public.provider._id;
1025
1026
1027--
1028-- Name: service; Type: TABLE; Schema: public; Owner: postgres
1029--
1030
1031CREATE TABLE public.service (
1032 _id integer NOT NULL,
1033 name character(20) NOT NULL,
1034 category_id integer NOT NULL,
1035 provider_id integer
1036);
1037
1038
1039ALTER TABLE public.service OWNER TO postgres;
1040
1041--
1042-- Name: service__id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
1043--
1044
1045CREATE SEQUENCE public.service__id_seq
1046 AS integer
1047 START WITH 1
1048 INCREMENT BY 1
1049 NO MINVALUE
1050 NO MAXVALUE
1051 CACHE 1;
1052
1053
1054ALTER TABLE public.service__id_seq OWNER TO postgres;
1055
1056--
1057-- Name: service__id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
1058--
1059
1060ALTER SEQUENCE public.service__id_seq OWNED BY public.service._id;
1061
1062
1063--
1064-- Name: sms_event; Type: TABLE; Schema: public; Owner: postgres
1065--
1066
1067CREATE TABLE public.sms_event (
1068 _id bigint NOT NULL,
1069 sms_id bigint NOT NULL,
1070 msisdn text NOT NULL,
1071 operator_id integer NOT NULL,
1072 pricepoint_id uuid NOT NULL,
1073 language_id smallint,
1074 action text NOT NULL,
1075 message text
1076);
1077
1078
1079ALTER TABLE public.sms_event OWNER TO postgres;
1080
1081--
1082-- Name: sms_event__id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
1083--
1084
1085CREATE SEQUENCE public.sms_event__id_seq
1086 START WITH 1
1087 INCREMENT BY 1
1088 NO MINVALUE
1089 NO MAXVALUE
1090 CACHE 1;
1091
1092
1093ALTER TABLE public.sms_event__id_seq OWNER TO postgres;
1094
1095--
1096-- Name: sms_event__id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
1097--
1098
1099ALTER SEQUENCE public.sms_event__id_seq OWNED BY public.sms_event._id;
1100
1101
1102--
1103-- Name: string; Type: TABLE; Schema: public; Owner: postgres
1104--
1105
1106CREATE TABLE public.string (
1107 _id integer NOT NULL
1108);
1109
1110
1111ALTER TABLE public.string OWNER TO postgres;
1112
1113--
1114-- Name: subscription; Type: TABLE; Schema: public; Owner: postgres
1115--
1116
1117CREATE TABLE public.subscription (
1118 _id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
1119 service_id integer NOT NULL,
1120 operator_id integer NOT NULL,
1121 pricepoint_id uuid NOT NULL,
1122 language_id integer,
1123 created_date timestamp without time zone DEFAULT now(),
1124 modified_date timestamp without time zone DEFAULT now(),
1125 billing_date timestamp without time zone,
1126 status text DEFAULT 'pending'::text NOT NULL,
1127 verification_attempts integer DEFAULT 0 NOT NULL,
1128 active boolean DEFAULT false,
1129 last_success_billing timestamp without time zone,
1130 extra_params jsonb DEFAULT '{}'::json,
1131 msisdn text NOT NULL,
1132 channel text DEFAULT 'web'::text,
1133 CONSTRAINT allowed_channel CHECK ((channel = ANY (ARRAY[('sms'::bpchar)::text, ('web'::bpchar)::text]))),
1134 CONSTRAINT allowed_status CHECK ((status = ANY (ARRAY[('pending'::bpchar)::text, ('awaiting_verification'::bpchar)::text, ('subscribed'::bpchar)::text, ('unsubscribed'::bpchar)::text])))
1135);
1136
1137
1138ALTER TABLE public.subscription OWNER TO postgres;
1139
1140--
1141-- Name: COLUMN subscription.verification_attempts; Type: COMMENT; Schema: public; Owner: postgres
1142--
1143
1144COMMENT ON COLUMN public.subscription.verification_attempts IS 'The number at which the end user received a verification pin to verify his subscription.';
1145
1146
1147--
1148-- Name: COLUMN subscription.last_success_billing; Type: COMMENT; Schema: public; Owner: postgres
1149--
1150
1151COMMENT ON COLUMN public.subscription.last_success_billing IS 'latest successful billing attempt';
1152
1153
1154--
1155-- Name: country _id; Type: DEFAULT; Schema: public; Owner: postgres
1156--
1157
1158ALTER TABLE ONLY public.country ALTER COLUMN _id SET DEFAULT nextval('public.country__id_seq'::regclass);
1159
1160
1161--
1162-- Name: flow_type _id; Type: DEFAULT; Schema: public; Owner: postgres
1163--
1164
1165ALTER TABLE ONLY public.flow_type ALTER COLUMN _id SET DEFAULT nextval('public.flow_type__id_seq'::regclass);
1166
1167
1168--
1169-- Name: language _id; Type: DEFAULT; Schema: public; Owner: postgres
1170--
1171
1172ALTER TABLE ONLY public.language ALTER COLUMN _id SET DEFAULT nextval('public.language__id_seq'::regclass);
1173
1174
1175--
1176-- Name: message _id; Type: DEFAULT; Schema: public; Owner: postgres
1177--
1178
1179ALTER TABLE ONLY public.message ALTER COLUMN _id SET DEFAULT nextval('public.message__id_seq'::regclass);
1180
1181
1182--
1183-- Name: notification _id; Type: DEFAULT; Schema: public; Owner: postgres
1184--
1185
1186ALTER TABLE ONLY public.notification ALTER COLUMN _id SET DEFAULT nextval('public.notification__id_seq'::regclass);
1187
1188
1189--
1190-- Name: operator _id; Type: DEFAULT; Schema: public; Owner: postgres
1191--
1192
1193ALTER TABLE ONLY public.operator ALTER COLUMN _id SET DEFAULT nextval('public.operator__id_seq'::regclass);
1194
1195
1196--
1197-- Name: provider _id; Type: DEFAULT; Schema: public; Owner: postgres
1198--
1199
1200ALTER TABLE ONLY public.provider ALTER COLUMN _id SET DEFAULT nextval('public.provider__id_seq'::regclass);
1201
1202
1203--
1204-- Name: service _id; Type: DEFAULT; Schema: public; Owner: postgres
1205--
1206
1207ALTER TABLE ONLY public.service ALTER COLUMN _id SET DEFAULT nextval('public.service__id_seq'::regclass);
1208
1209
1210--
1211-- Name: sms_event _id; Type: DEFAULT; Schema: public; Owner: postgres
1212--
1213
1214ALTER TABLE ONLY public.sms_event ALTER COLUMN _id SET DEFAULT nextval('public.sms_event__id_seq'::regclass);
1215
1216
1217--
1218-- Name: category category_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
1219--
1220
1221ALTER TABLE ONLY public.category
1222 ADD CONSTRAINT category_pkey PRIMARY KEY (_id);
1223
1224
1225--
1226-- Name: country country_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
1227--
1228
1229ALTER TABLE ONLY public.country
1230 ADD CONSTRAINT country_pkey PRIMARY KEY (_id);
1231
1232
1233--
1234-- Name: flow_type flow_type_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
1235--
1236
1237ALTER TABLE ONLY public.flow_type
1238 ADD CONSTRAINT flow_type_pkey PRIMARY KEY (_id);
1239
1240
1241--
1242-- Name: language language_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
1243--
1244
1245ALTER TABLE ONLY public.language
1246 ADD CONSTRAINT language_pkey PRIMARY KEY (_id);
1247
1248
1249--
1250-- Name: message message_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
1251--
1252
1253ALTER TABLE ONLY public.message
1254 ADD CONSTRAINT message_pkey PRIMARY KEY (_id);
1255
1256
1257--
1258-- Name: operator operator_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
1259--
1260
1261ALTER TABLE ONLY public.operator
1262 ADD CONSTRAINT operator_pkey PRIMARY KEY (_id);
1263
1264
1265--
1266-- Name: pricepoint pricepoint_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
1267--
1268
1269ALTER TABLE ONLY public.pricepoint
1270 ADD CONSTRAINT pricepoint_pkey PRIMARY KEY (_id);
1271
1272
1273--
1274-- Name: provider provider_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
1275--
1276
1277ALTER TABLE ONLY public.provider
1278 ADD CONSTRAINT provider_pkey PRIMARY KEY (_id);
1279
1280
1281--
1282-- Name: service service_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
1283--
1284
1285ALTER TABLE ONLY public.service
1286 ADD CONSTRAINT service_pkey PRIMARY KEY (_id);
1287
1288
1289--
1290-- Name: sms_event sms_event_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
1291--
1292
1293ALTER TABLE ONLY public.sms_event
1294 ADD CONSTRAINT sms_event_pkey PRIMARY KEY (_id);
1295
1296
1297--
1298-- Name: string string_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
1299--
1300
1301ALTER TABLE ONLY public.string
1302 ADD CONSTRAINT string_pkey PRIMARY KEY (_id);
1303
1304
1305--
1306-- Name: subscription subscription_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
1307--
1308
1309ALTER TABLE ONLY public.subscription
1310 ADD CONSTRAINT subscription_pkey PRIMARY KEY (_id);
1311
1312
1313--
1314-- Name: subscription trg_already_subscribed; Type: TRIGGER; Schema: public; Owner: postgres
1315--
1316
1317CREATE TRIGGER trg_already_subscribed BEFORE INSERT ON public.subscription FOR EACH ROW EXECUTE FUNCTION public.already_subscribed();
1318
1319
1320--
1321-- Name: subscription trg_event_handler; Type: TRIGGER; Schema: public; Owner: postgres
1322--
1323
1324CREATE TRIGGER trg_event_handler AFTER UPDATE ON public.subscription FOR EACH ROW EXECUTE FUNCTION public.event_handler();
1325
1326
1327--
1328-- Name: subscription trg_language_fallback; Type: TRIGGER; Schema: public; Owner: postgres
1329--
1330
1331CREATE TRIGGER trg_language_fallback BEFORE INSERT ON public.subscription FOR EACH ROW EXECUTE FUNCTION public.language_fallback();
1332
1333
1334--
1335-- Name: subscription trg_validate_subscription_params; Type: TRIGGER; Schema: public; Owner: postgres
1336--
1337
1338CREATE TRIGGER trg_validate_subscription_params BEFORE INSERT OR UPDATE ON public.subscription FOR EACH ROW EXECUTE FUNCTION public.validate_subscription_params();
1339
1340
1341--
1342-- PostgreSQL database dump complete
1343--
1344
1345--
1346-- Database "postgres" dump
1347--
1348
1349\connect postgres
1350
1351--
1352-- PostgreSQL database dump
1353--
1354
1355-- Dumped from database version 12.1
1356-- Dumped by pg_dump version 12.1
1357
1358SET statement_timeout = 0;
1359SET lock_timeout = 0;
1360SET idle_in_transaction_session_timeout = 0;
1361SET client_encoding = 'UTF8';
1362SET standard_conforming_strings = on;
1363SELECT pg_catalog.set_config('search_path', '', false);
1364SET check_function_bodies = false;
1365SET xmloption = content;
1366SET client_min_messages = warning;
1367SET row_security = off;
1368
1369--
1370-- Name: adminpack; Type: EXTENSION; Schema: -; Owner: -
1371--
1372
1373CREATE EXTENSION IF NOT EXISTS adminpack WITH SCHEMA pg_catalog;
1374
1375
1376--
1377-- Name: EXTENSION adminpack; Type: COMMENT; Schema: -; Owner:
1378--
1379
1380COMMENT ON EXTENSION adminpack IS 'administrative functions for PostgreSQL';
1381
1382
1383--
1384-- PostgreSQL database dump complete
1385--
1386
1387--
1388-- Database "sms" dump
1389--
1390
1391--
1392-- PostgreSQL database dump
1393--
1394
1395-- Dumped from database version 12.1
1396-- Dumped by pg_dump version 12.1
1397
1398SET statement_timeout = 0;
1399SET lock_timeout = 0;
1400SET idle_in_transaction_session_timeout = 0;
1401SET client_encoding = 'UTF8';
1402SET standard_conforming_strings = on;
1403SELECT pg_catalog.set_config('search_path', '', false);
1404SET check_function_bodies = false;
1405SET xmloption = content;
1406SET client_min_messages = warning;
1407SET row_security = off;
1408
1409--
1410-- Name: sms; Type: DATABASE; Schema: -; Owner: postgres
1411--
1412
1413CREATE DATABASE sms WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'English_United States.1252' LC_CTYPE = 'English_United States.1252';
1414
1415
1416ALTER DATABASE sms OWNER TO postgres;
1417
1418\connect sms
1419
1420SET statement_timeout = 0;
1421SET lock_timeout = 0;
1422SET idle_in_transaction_session_timeout = 0;
1423SET client_encoding = 'UTF8';
1424SET standard_conforming_strings = on;
1425SELECT pg_catalog.set_config('search_path', '', false);
1426SET check_function_bodies = false;
1427SET xmloption = content;
1428SET client_min_messages = warning;
1429SET row_security = off;
1430
1431--
1432-- Name: get_incoming_sms(); Type: FUNCTION; Schema: public; Owner: postgres
1433--
1434
1435CREATE FUNCTION public.get_incoming_sms() RETURNS TABLE(_id bigint, correlation_id text, msisdn text, sender_id text, operator_id integer, text text, encoding text, pricepoint_id uuid, service_id integer, action text)
1436 LANGUAGE plpgsql
1437 AS $$
1438
1439BEGIN
1440DROP TABLE IF EXISTS incoming_sms;
1441CREATE TEMP TABLE incoming_sms
1442 (
1443 _id bigint,
1444 correlation_id text,
1445 msisdn text,
1446 sender_id text,
1447 operator_id integer,
1448 text text,
1449 encoding text,
1450 pricepoint_id uuid,
1451 service_id integer,
1452 action text
1453 );
1454
1455INSERT INTO incoming_sms
1456SELECT
1457 SMS._id ,
1458 SMS.correlation_id ,
1459 SMS.msisdn ,
1460 SMS.sender_id ,
1461 SMS.operator_id ,
1462 SMS.text ,
1463 SMS.encoding,
1464 KW.pricepoint_id,
1465 KW.service_id,
1466 CASE WHEN KW.action is NULL THEN 'fallback' ELSE KW.action END AS action
1467 FROM sms_in SMS
1468 LEFT JOIN keywords KW ON SMS.text = KW.text
1469 WHERE SMS.is_processed = false
1470 LIMIT 250;
1471-- set is_processed = true to mark as processed
1472UPDATE sms_in
1473SET is_processed = 'true', processed_date = now()
1474FROM incoming_sms TMP
1475WHERE sms_in._id= TMP._id;
1476
1477return query select * from incoming_sms;
1478END;
1479$$;
1480
1481
1482ALTER FUNCTION public.get_incoming_sms() OWNER TO postgres;
1483
1484--
1485-- Name: sms_in_handler(); Type: FUNCTION; Schema: public; Owner: postgres
1486--
1487
1488CREATE FUNCTION public.sms_in_handler() RETURNS trigger
1489 LANGUAGE plpgsql
1490 AS $$
1491DECLARE
1492_operator_id INTEGER;
1493_sender_id INTEGER;
1494_text text;
1495_pricepoint uuid;
1496_action text;
1497BEGIN
1498
1499 _pricepoint := (select pricepoint from keywords where
1500 NEW.operator_id = _operator_id AND
1501 NEW.sender_id = _sender_id AND
1502 NEW.text = _text);
1503
1504 IF (_pricepoint is distinct from null) THEN
1505 _action := (select keywords.action from keywords where
1506 NEW.operator_id = _operator_id AND
1507 NEW.sender_id = _sender_id AND
1508 NEW.text = _text);
1509 ELSE
1510 _action := 'fallback';
1511 END IF;
1512
1513 INSERT INTO public.event(
1514 sms_id, msisdn, operator_id, pricepoint_id, action)
1515 VALUES (NEW._id, NEW.msisdn, NEW.operator_id, NEW.pricepoint_id, _action);
1516
1517RETURN NEW;
1518END;
1519$$;
1520
1521
1522ALTER FUNCTION public.sms_in_handler() OWNER TO postgres;
1523
1524SET default_tablespace = '';
1525
1526SET default_table_access_method = heap;
1527
1528--
1529-- Name: event; Type: TABLE; Schema: public; Owner: postgres
1530--
1531
1532CREATE TABLE public.event (
1533 _id bigint NOT NULL,
1534 sms_id bigint NOT NULL,
1535 msisdn text NOT NULL,
1536 operator_id integer NOT NULL,
1537 pricepoint_id uuid NOT NULL,
1538 created_date timestamp without time zone DEFAULT now(),
1539 is_processed boolean DEFAULT false,
1540 processed_date timestamp without time zone,
1541 action text NOT NULL
1542);
1543
1544
1545ALTER TABLE public.event OWNER TO postgres;
1546
1547--
1548-- Name: event__id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
1549--
1550
1551CREATE SEQUENCE public.event__id_seq
1552 START WITH 1
1553 INCREMENT BY 1
1554 NO MINVALUE
1555 NO MAXVALUE
1556 CACHE 1;
1557
1558
1559ALTER TABLE public.event__id_seq OWNER TO postgres;
1560
1561--
1562-- Name: event__id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
1563--
1564
1565ALTER SEQUENCE public.event__id_seq OWNED BY public.event._id;
1566
1567
1568--
1569-- Name: event_sms_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
1570--
1571
1572CREATE SEQUENCE public.event_sms_id_seq
1573 START WITH 1
1574 INCREMENT BY 1
1575 NO MINVALUE
1576 NO MAXVALUE
1577 CACHE 1;
1578
1579
1580ALTER TABLE public.event_sms_id_seq OWNER TO postgres;
1581
1582--
1583-- Name: event_sms_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
1584--
1585
1586ALTER SEQUENCE public.event_sms_id_seq OWNED BY public.event.sms_id;
1587
1588
1589--
1590-- Name: keywords; Type: TABLE; Schema: public; Owner: postgres
1591--
1592
1593CREATE TABLE public.keywords (
1594 _id integer NOT NULL,
1595 text text NOT NULL,
1596 encoding text,
1597 operator_id integer NOT NULL,
1598 sender_id text NOT NULL,
1599 pricepoint_id uuid NOT NULL,
1600 action text,
1601 service_id integer,
1602 CONSTRAINT allowed_action CHECK ((action = ANY (ARRAY[('sub'::bpchar)::text, ('unsub'::bpchar)::text, ('fallback'::bpchar)::text])))
1603);
1604
1605
1606ALTER TABLE public.keywords OWNER TO postgres;
1607
1608--
1609-- Name: keywords__id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
1610--
1611
1612CREATE SEQUENCE public.keywords__id_seq
1613 AS integer
1614 START WITH 1
1615 INCREMENT BY 1
1616 NO MINVALUE
1617 NO MAXVALUE
1618 CACHE 1;
1619
1620
1621ALTER TABLE public.keywords__id_seq OWNER TO postgres;
1622
1623--
1624-- Name: keywords__id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
1625--
1626
1627ALTER SEQUENCE public.keywords__id_seq OWNED BY public.keywords._id;
1628
1629
1630--
1631-- Name: sms_in; Type: TABLE; Schema: public; Owner: postgres
1632--
1633
1634CREATE TABLE public.sms_in (
1635 _id bigint NOT NULL,
1636 correlation_id text,
1637 msisdn text,
1638 sender_id text,
1639 text text,
1640 encoding text,
1641 created_date timestamp without time zone DEFAULT now(),
1642 is_processed boolean DEFAULT false,
1643 processed_date timestamp without time zone,
1644 operator_id integer
1645);
1646
1647
1648ALTER TABLE public.sms_in OWNER TO postgres;
1649
1650--
1651-- Name: sms_in__id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
1652--
1653
1654CREATE SEQUENCE public.sms_in__id_seq
1655 START WITH 1
1656 INCREMENT BY 1
1657 NO MINVALUE
1658 NO MAXVALUE
1659 CACHE 1;
1660
1661
1662ALTER TABLE public.sms_in__id_seq OWNER TO postgres;
1663
1664--
1665-- Name: sms_in__id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
1666--
1667
1668ALTER SEQUENCE public.sms_in__id_seq OWNED BY public.sms_in._id;
1669
1670
1671--
1672-- Name: sms_out; Type: TABLE; Schema: public; Owner: postgres
1673--
1674
1675CREATE TABLE public.sms_out (
1676 _id integer NOT NULL,
1677 correlation_id text,
1678 msisdn text,
1679 sender_id text,
1680 text text,
1681 encoding text,
1682 created_date timestamp without time zone DEFAULT now(),
1683 is_processed boolean DEFAULT false,
1684 processed_date timestamp without time zone
1685);
1686
1687
1688ALTER TABLE public.sms_out OWNER TO postgres;
1689
1690--
1691-- Name: sms_out__id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
1692--
1693
1694CREATE SEQUENCE public.sms_out__id_seq
1695 AS integer
1696 START WITH 1
1697 INCREMENT BY 1
1698 NO MINVALUE
1699 NO MAXVALUE
1700 CACHE 1;
1701
1702
1703ALTER TABLE public.sms_out__id_seq OWNER TO postgres;
1704
1705--
1706-- Name: sms_out__id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
1707--
1708
1709ALTER SEQUENCE public.sms_out__id_seq OWNED BY public.sms_out._id;
1710
1711
1712--
1713-- Name: event _id; Type: DEFAULT; Schema: public; Owner: postgres
1714--
1715
1716ALTER TABLE ONLY public.event ALTER COLUMN _id SET DEFAULT nextval('public.event__id_seq'::regclass);
1717
1718
1719--
1720-- Name: event sms_id; Type: DEFAULT; Schema: public; Owner: postgres
1721--
1722
1723ALTER TABLE ONLY public.event ALTER COLUMN sms_id SET DEFAULT nextval('public.event_sms_id_seq'::regclass);
1724
1725
1726--
1727-- Name: keywords _id; Type: DEFAULT; Schema: public; Owner: postgres
1728--
1729
1730ALTER TABLE ONLY public.keywords ALTER COLUMN _id SET DEFAULT nextval('public.keywords__id_seq'::regclass);
1731
1732
1733--
1734-- Name: sms_in _id; Type: DEFAULT; Schema: public; Owner: postgres
1735--
1736
1737ALTER TABLE ONLY public.sms_in ALTER COLUMN _id SET DEFAULT nextval('public.sms_in__id_seq'::regclass);
1738
1739
1740--
1741-- Name: sms_out _id; Type: DEFAULT; Schema: public; Owner: postgres
1742--
1743
1744ALTER TABLE ONLY public.sms_out ALTER COLUMN _id SET DEFAULT nextval('public.sms_out__id_seq'::regclass);
1745
1746
1747--
1748-- Name: event event_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
1749--
1750
1751ALTER TABLE ONLY public.event
1752 ADD CONSTRAINT event_pkey PRIMARY KEY (_id);
1753
1754
1755--
1756-- Name: sms_in sms_in_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
1757--
1758
1759ALTER TABLE ONLY public.sms_in
1760 ADD CONSTRAINT sms_in_pkey PRIMARY KEY (_id);
1761
1762
1763--
1764-- Name: sms_out sms_out_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
1765--
1766
1767ALTER TABLE ONLY public.sms_out
1768 ADD CONSTRAINT sms_out_pkey PRIMARY KEY (_id);
1769
1770
1771--
1772-- Name: keywords unq_keyword; Type: CONSTRAINT; Schema: public; Owner: postgres
1773--
1774
1775ALTER TABLE ONLY public.keywords
1776 ADD CONSTRAINT unq_keyword UNIQUE (operator_id, pricepoint_id, sender_id, text);
1777
1778
1779--
1780-- PostgreSQL database dump complete
1781--
1782
1783--
1784-- PostgreSQL database cluster dump complete
1785--