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