· 5 years ago · Jul 03, 2020, 07:12 PM
1
2Skip to content
3Pull requests
4Issues
5Marketplace
6Explore
7@ianmin2
8Learn Git and GitHub without any code!
9
10Using the Hello World guide, you’ll start a branch, write comments, and open a pull request.
11ianmin2 /
12framify
13
143
151
16
17 1
18
19Code
20Issues
21Pull requests
22Actions
23Projects
24Wiki
25Security
26Insights
27
28 Settings
29
30framify/assets/schema/sample.sql
31@ianmin2
32ianmin2 construction Automated project save
33Latest commit e074aed on 13 Oct 2017
34History
351 contributor
36839 lines (728 sloc) 32.9 KB
37-- ORGANIZATIONS --
38DROP TABLE IF EXISTS organizations CASCADE;
39CREATE TABLE IF NOT EXISTS organizations (
40 org_id bigserial PRIMARY KEY,
41 org_name text NOT NULL CONSTRAINT unique_organization_name_required UNIQUE,
42 org_telephone text NOT NULL CONSTRAINT unique_organization_telephone_required UNIQUE,
43 org_email text NOT NULL CONSTRAINT unique_organization_email_required UNIQUE,
44 org_code varchar(25) NOT NULL CONSTRAINT unique_organization_code_required UNIQUE,
45 org_added TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
46 org_active boolean DEFAULT true
47);
48INSERT INTO organizations
49(org_id,org_name,org_telephone,org_email,org_code)
50VALUES
51(1,'Bixbyte Solutions','+254725678447','info@bixbyte.io','pm_bx_001');
52
53
54-- AUD_ORGANIZATIONS --
55DROP TABLE IF EXISTS aud_organizations CASCADE;
56CREATE TABLE IF NOT EXISTS aud_organizations (
57 org_id bigint ,
58 org_name text ,
59 org_telephone text ,
60 org_email text ,
61 org_code varchar(25) ,
62 org_added TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
63 org_active boolean DEFAULT false,
64 func varchar(15)
65);
66
67
68--- ORGANIZATIONS
69CREATE OR REPLACE FUNCTION audit_organizations()
70 RETURNS trigger AS
71$BODY$
72BEGIN
73 IF (TG_OP = 'DELETE') THEN
74 INSERT INTO aud_organizations (org_id,org_name,org_telephone,org_email,org_code,org_added,org_active,func)
75 SELECT OLD.org_id,OLD.org_name,OLD.org_telephone,OLD.org_email,OLD.org_code,OLD.org_added,OLD.org_active,TG_OP;
76 RETURN OLD;
77 END IF;
78 IF (TG_OP = 'INSERT') THEN
79 -- INSERT INTO aud_organizations (org_id,org_name,org_telephone,org_email,org_code,org_added,org_active,func)
80 -- SELECT NEW.org_id,NEW.org_name,NEW.org_telephone,NEW.org_email,NEW.org_code,NEW.org_added,NEW.org_active,TG_OP;
81 RETURN NEW;
82 END IF;
83 IF (TG_OP = 'UPDATE') THEN
84 INSERT INTO aud_organizations (org_id,org_name,org_telephone,org_email,org_code,org_added,org_active,func)
85 SELECT OLD.org_id,OLD.org_name,OLD.org_telephone,OLD.org_email,OLD.org_code,now(),OLD.org_active,TG_OP;
86 RETURN NEW;
87 END IF;
88END;
89$BODY$
90LANGUAGE plpgsql VOLATILE;
91
92
93-- ORGANIZATIONS
94CREATE TRIGGER organizations_audit BEFORE UPDATE OR INSERT OR DELETE
95 ON organizations FOR EACH ROW
96EXECUTE PROCEDURE audit_organizations();
97
98--- ORGANIZATIONS ---
99DROP VIEW IF EXISTS vw_organizations CASCADE;
100CREATE OR REPLACE VIEW vw_organizations AS
101SELECT org_id,org_name,org_telephone,org_email,org_code,org_added,org_active
102FROM organizations;
103
104--==========================================================================================================
105
106
107-- SERVICES --
108DROP TABLE IF EXISTS services CASCADE;
109CREATE TABLE IF NOT EXISTS services (
110 service_id bigserial PRIMARY KEY,
111 service_name text CONSTRAINT unique_service_name_required UNIQUE NOT NULL,
112 service_fee bigint CONSTRAINT service_fee_required NOT NULL,
113 service_code text CONSTRAINT unique_service_coed_required UNIQUE NOT NULL,
114 service_added TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
115 service_active boolean DEFAULT true
116);
117INSERT INTO services
118(service_id,service_name,service_fee,service_code)
119VALUES
120(1,'SMS',0,'BX_SMS');
121
122-- AUD_SERVICES --
123DROP TABLE IF EXISTS aud_services CASCADE;
124CREATE TABLE IF NOT EXISTS aud_services (
125 service_id bigint ,
126 service_name text ,
127 service_fee bigint ,
128 service_code text ,
129 service_added TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
130 service_active boolean DEFAULT true,
131 func varchar(15)
132);
133
134
135--- SERVICES
136CREATE OR REPLACE FUNCTION audit_services()
137 RETURNS trigger AS
138$BODY$
139BEGIN
140 IF (TG_OP = 'DELETE') THEN
141 INSERT INTO aud_services (service_id,service_name,service_fee,service_code,service_added,service_active,func)
142 SELECT OLD.service_id,OLD.service_name,OLD.service_fee,OLD.service_code,OLD.service_added,OLD.service_active,TG_OP;
143 RETURN OLD;
144 END IF;
145 IF (TG_OP = 'INSERT') THEN
146 -- INSERT INTO aud_services (service_id,service_name,service_fee,service_code,service_added,service_active,func)
147 -- SELECT NEW.service_id,NEW.service_name,NEW.service_fee,NEW.service_code,NEW.service_added,NEW.service_active,TG_OP;
148 RETURN NEW;
149 END IF;
150 IF (TG_OP = 'UPDATE') THEN
151 INSERT INTO aud_services (service_id,service_name,service_fee,service_code,service_added,service_active,func)
152 SELECT OLD.service_id,OLD.service_name,OLD.service_fee,OLD.service_code,OLD.service_added,OLD.service_active,TG_OP;
153 RETURN NEW;
154 END IF;
155END;
156$BODY$
157LANGUAGE plpgsql VOLATILE;
158
159-- SERVICES
160CREATE TRIGGER services_audit BEFORE UPDATE OR INSERT OR DELETE
161 ON services FOR EACH ROW
162EXECUTE PROCEDURE audit_services();
163
164
165--- SERVICES ---
166DROP VIEW IF EXISTS vw_services CASCADE;
167CREATE OR REPLACE VIEW vw_services AS
168SELECT service_id,service_name,service_code,service_added,service_active
169FROM services;
170
171
172
173
174--==========================================================================================================
175
176-- SUBSCRIPTIONS --
177DROP TABLE IF EXISTS subscriptions CASCADE;
178CREATE TABLE IF NOT EXISTS subscriptions (
179 sub_id bigserial PRIMARY KEY,
180 sub_org bigint NOT NULL CONSTRAINT organization_id_required REFERENCES organizations( org_id ),
181 sub_service bigint NOT NULL CONSTRAINT service_subscription_required REFERENCES services( service_id ),
182 sub_added TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
183 sub_active boolean DEFAULT true
184);
185INSERT INTO subscriptions
186(sub_id,sub_org,sub_service)
187VALUES
188(1,1,1);
189
190-- AUD_SUBSCRIPTIONS --
191DROP TABLE IF EXISTS aud_subscriptions CASCADE;
192CREATE TABLE IF NOT EXISTS aud_subscriptions (
193 sub_id bigint ,
194 sub_org bigint ,
195 sub_service bigint ,
196 sub_added TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
197 sub_active boolean DEFAULT true,
198 func varchar(15)
199);
200
201
202--- SUBSCRIPTIONS
203CREATE OR REPLACE FUNCTION audit_subscriptions()
204 RETURNS trigger AS
205$BODY$
206BEGIN
207 IF (TG_OP = 'DELETE') THEN
208 INSERT INTO aud_subscriptions (sub_id,sub_org,sub_service,sub_added,sub_active,func)
209 SELECT OLD.sub_id,OLD.sub_org,OLD.sub_service,OLD.sub_added,OLD.sub_active,TG_OP;
210 RETURN OLD;
211 END IF;
212 IF (TG_OP = 'INSERT') THEN
213 -- INSERT INTO aud_subscriptions (sub_id,sub_org,sub_service,sub_added,sub_active,func)
214 -- SELECT NEW.sub_id,NEW.sub_org,NEW.sub_service,NEW.sub_added,NEW.sub_active,TG_OP;
215 RETURN NEW;
216 END IF;
217 IF (TG_OP = 'UPDATE') THEN
218 INSERT INTO aud_subscriptions (sub_id,sub_org,sub_service,sub_added,sub_active,func)
219 SELECT OLD.sub_id,OLD.sub_org,OLD.sub_service,OLD.sub_added,OLD.sub_active,TG_OP;
220 RETURN NEW;
221 END IF;
222END;
223$BODY$
224LANGUAGE plpgsql VOLATILE;
225
226
227-- SUBSCRIPTIONS
228CREATE TRIGGER subscriptions_audit BEFORE UPDATE OR INSERT OR DELETE
229 ON subscriptions FOR EACH ROW
230EXECUTE PROCEDURE audit_subscriptions();
231
232
233--- SUBSCRIPTIONS ---
234DROP VIEW IF EXISTS vw_subscriptions CASCADE;
235CREATE OR REPLACE VIEW vw_subscriptions AS
236SELECT sub_id
237,sub_org,organizations.org_name as sub_org_name,organizations.org_active,organizations.org_email AS sub_org_email, organizations.org_telephone AS sub_org_telephone
238,sub_service,services.service_name as sub_service_name,services.service_active
239,sub_added,sub_active
240FROM subscriptions
241 LEFT JOIN organizations
242 ON subscriptions.sub_org = organizations.org_id
243 LEFT JOIN services
244ON subscriptions.sub_service = services.service_id;
245
246--==========================================================================================================
247
248-- PAYMENT_METHODS --
249DROP TABLE IF EXISTS payment_methods CASCADE;
250CREATE TABLE IF NOT EXISTS payment_methods(
251 pay_method_id bigserial PRIMARY KEY,
252 pay_method_name varchar(60) NOT NULL,
253 pay_method_fee bigint DEFAULT 0,
254 pay_method_added TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
255 pay_method_active boolean DEFAULT true
256);
257INSERT INTO payment_methods
258(pay_method_id,pay_method_name)
259VALUES
260(1,'Card'),(2,'Mpesa'),(3,'Cash'),(4,'Cheque');
261
262
263-- AUD_PAYMENT_METHODS --
264DROP TABLE IF EXISTS aud_payment_methods CASCADE;
265CREATE TABLE IF NOT EXISTS aud_payment_methods(
266 pay_method_id bigint ,
267 pay_method_name varchar(60) ,
268 pay_method_fee bigint DEFAULT 0,
269 pay_method_added TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
270 pay_method_active boolean DEFAULT true,
271 func varchar(15)
272);
273
274--- PAYMENT_METHODS
275CREATE OR REPLACE FUNCTION audit_payment_methods()
276 RETURNS trigger AS
277$BODY$
278BEGIN
279 IF (TG_OP = 'DELETE') THEN
280 INSERT INTO aud_payment_methods (pay_method_id,pay_method_name,pay_method_fee,pay_method_added,pay_method_active,func)
281 SELECT OLD.pay_method_id,OLD.pay_method_name,OLD.pay_method_fee,OLD.pay_method_added,OLD.pay_method_active,TG_OP;
282 RETURN OLD;
283 END IF;
284 IF (TG_OP = 'INSERT') THEN
285 -- INSERT INTO aud_payment_methods (pay_method_id,pay_method_name,pay_method_fee,pay_method_added,pay_method_active,func)
286 -- SELECT NEW.pay_method_id,NEW.pay_method_name,NEW.pay_method_fee,NEW.pay_method_added,NEW.pay_method_active,TG_OP;
287 RETURN NEW;
288 END IF;
289 IF (TG_OP = 'UPDATE') THEN
290 INSERT INTO aud_payment_methods (pay_method_id,pay_method_name,pay_method_fee,pay_method_added,pay_method_active,func)
291 SELECT OLD.pay_method_id,OLD.pay_method_name,OLD.pay_method_fee,OLD.pay_method_added,OLD.pay_method_active,TG_OP;
292 RETURN NEW;
293 END IF;
294END;
295$BODY$
296LANGUAGE plpgsql VOLATILE;
297
298-- PAYMENT_METHODS
299CREATE TRIGGER payment_methods_audit BEFORE UPDATE OR INSERT OR DELETE
300 ON payment_methods FOR EACH ROW
301EXECUTE PROCEDURE audit_payment_methods();
302
303
304--- PAYMENT_METHODS ---
305DROP VIEW IF EXISTS vw_payment_methods CASCADE;
306CREATE OR REPLACE VIEW vw_payment_methods AS
307SELECT pay_method_id,pay_method_name,pay_method_fee,pay_method_added,pay_method_active
308FROM payment_methods;
309
310
311--==========================================================================================================
312
313-- PAYMENTS --
314DROP TABLE IF EXISTS payments CASCADE;
315CREATE TABLE IF NOT EXISTS payments (
316 pay_id bigserial PRIMARY KEY,
317 pay_org bigint CONSTRAINT valid_organization_required REFERENCES organizations( org_id ),
318 pay_amount bigint NOT NULL,
319 pay_method bigint NOT NULL CONSTRAINT valid_pay_method_required REFERENCES payment_methods( pay_method_id ),
320 pay_services jsonb NOT NULL,
321 pay_token text NOT NULL CONSTRAINT token_not_used UNIQUE,
322 pay_message text,
323 pay_added TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
324 pay_active boolean DEFAULT true
325);
326
327
328
329-- AUD_PAYMENTS --
330DROP TABLE IF EXISTS aud_payments CASCADE;
331CREATE TABLE IF NOT EXISTS aud_payments (
332 pay_id bigint ,
333 pay_org bigint ,
334 pay_amount bigint ,
335 pay_method bigint ,
336 pay_services json ,
337 pay_token text,
338 pay_message text,
339 pay_added TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
340 pay_active boolean,
341 func varchar(15)
342);
343
344--- PAYMENTS
345CREATE OR REPLACE FUNCTION audit_payments()
346 RETURNS trigger AS
347$BODY$
348DECLARE
349 _log_balance bigint;
350 _new_balance bigint;
351BEGIN
352 IF (TG_OP = 'DELETE') THEN
353 INSERT INTO aud_payments (pay_id,pay_org,pay_amount,pay_method,pay_services,pay_token,pay_message,pay_added,pay_active,func)
354 SELECT OLD.pay_id,OLD.pay_org,OLD.pay_amount,OLD.pay_method,OLD.pay_services,OLD.pay_token,OLD.pay_message,OLD.pay_added,OLD.pay_active,TG_OP;
355 RETURN OLD;
356 END IF;
357 IF (TG_OP = 'INSERT') THEN
358 SELECT log_balance INTO _log_balance FROM logs WHERE log_organization = NEW.pay_org ORDER BY log_id DESC;
359
360 IF _log_balance IS NULL THEN
361 INSERT INTO logs (log_summary,log_organization,log_balance,log_reference) VALUES ( NEW.pay_services,NEW.pay_org, NEW.pay_amount,NEW.pay_id );
362 ELSE
363 INSERT INTO logs (log_summary,log_organization,log_balance,log_reference) VALUES ( NEW.pay_services,NEW.pay_org, _log_balance+NEW.pay_amount, NEW.pay_id );
364 END IF;
365 -- INSERT INTO aud_payments (pay_id,pay_org,pay_amount,pay_method,pay_services,pay_token,pay_message,pay_added,pay_active,func)
366 -- SELECT NEW.pay_id,NEW.pay_org,NEW.pay_amount,NEW.pay_method,NEW.pay_services,NEW.pay_token,NEW.pay_message,NEW.pay_added,NEW.pay_active,TG_OP;
367 RETURN NEW;
368 END IF;
369 IF (TG_OP = 'UPDATE') THEN
370 INSERT INTO aud_payments (pay_id,pay_org,pay_amount,pay_method,pay_services,pay_token,pay_message,pay_added,pay_active,func)
371 SELECT OLD.pay_id,OLD.pay_org,OLD.pay_amount,OLD.pay_method,OLD.pay_services,OLD.pay_token,OLD.pay_message,OLD.pay_added,OLD.pay_active,TG_OP;
372 RETURN NEW;
373 END IF;
374END;
375$BODY$
376LANGUAGE plpgsql VOLATILE;
377
378-- PAYMENTS
379CREATE TRIGGER payments_audit BEFORE UPDATE OR INSERT OR DELETE
380 ON payments FOR EACH ROW
381EXECUTE PROCEDURE audit_payments();
382
383
384
385--- VW_PAYMENTS ---
386DROP VIEW IF EXISTS vw_payments CASCADE;
387CREATE OR REPLACE VIEW vw_payments AS
388SELECT pay_id
389,pay_org,organizations.org_name as pay_org_name, organizations.org_telephone AS pay_org_telephone, organizations.org_email AS pay_org_email
390,pay_services
391,pay_message,pay_added
392,pay_method,payment_methods.pay_method_name
393,pay_active
394FROM payments
395 LEFT JOIN organizations
396 ON payments.pay_org = organizations.org_id
397 LEFT JOIN payment_methods
398ON payments.pay_method = payment_methods.pay_method_id;
399
400--==========================================================================================================
401
402
403-- MEMBER TYPE ENUMERATOR --
404DROP TYPE IF EXISTS available_roles CASCADE;
405CREATE TYPE available_roles AS ENUM ('audit','client','admin');
406
407-- MEMBERS --
408DROP TABLE IF EXISTS members CASCADE;
409CREATE TABLE IF NOT EXISTS members (
410 member_id bigserial PRIMARY KEY,
411 "name.first" varchar(25) NOT NULL,
412 "name.last" varchar(25),
413 "account.name" varchar(50) UNIQUE NOT NULL,
414 "account.balance" bigint DEFAULT 0,
415 organization bigint CONSTRAINT valid_member_organization_required REFERENCES organizations( org_id ),
416 email varchar(75) UNIQUE NOT NULL,
417 password text NOT NULL,
418 role available_roles NOT NULL,
419 telephone varchar(15) NOT NULL,
420 joined TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
421 active boolean DEFAULT true
422);
423
424INSERT INTO members
425( member_id,"name.first", "name.last","account.name",email, password, role, telephone,organization )
426VALUES
427(1,'User','Administrator','userAdmin','useradmin@bixbyte.io',MD5('ianmin2'),'admin', 0725678447, 1);
428
429-- AUD_MEMBERS --
430DROP TABLE IF EXISTS aud_members CASCADE;
431CREATE TABLE IF NOT EXISTS aud_members (
432 member_id bigint,
433 "name.first" varchar(25) ,
434 "name.last" varchar(25),
435 "account.name" varchar(50),
436 "account.balance" bigint,
437 organization bigint,
438 email varchar(75) ,
439 password text ,
440 role available_roles ,
441 telephone varchar(15) ,
442 joined TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
443 active boolean DEFAULT true,
444 func varchar(15)
445);
446
447
448--- MEMBERS
449CREATE OR REPLACE FUNCTION audit_members()
450 RETURNS trigger AS
451$BODY$
452BEGIN
453 IF (TG_OP = 'DELETE') THEN
454 INSERT INTO aud_members (member_id,"name.first","name.last","account.name","account.balance",organization,email,password,role,telephone,joined,active,func)
455 SELECT OLD.member_id,OLD."name.first",OLD."name.last",OLD."account.name",OLD."account.balance",OLD.organization,OLD.email,OLD.password,OLD.role,OLD.telephone,OLD.joined,OLD.active,TG_OP;
456 RETURN OLD;
457 END IF;
458 IF (TG_OP = 'INSERT') THEN
459 -- INSERT INTO aud_members (member_id,"name.first","name.last","account.name","account.balance",organization,email,password,role,telephone,joined,active,func)
460 -- SELECT NEW.member_id,NEW."name.first",NEW."name.last",NEW."account.name",NEW."account.balance",NEW.organization,NEW.email,NEW.password,NEW.role,NEW.telephone,NEW.joined,NEW.active,TG_OP;
461 RETURN NEW;
462 END IF;
463 IF (TG_OP = 'UPDATE') THEN
464 INSERT INTO aud_members (member_id,"name.first","name.last","account.name","account.balance",organization,email,password,role,telephone,joined,active,func)
465 SELECT OLD.member_id,OLD."name.first",OLD."name.last",OLD."account.name",OLD."account.balance",OLD.organization,OLD.email,OLD.password,OLD.role,OLD.telephone,OLD.joined,OLD.active,TG_OP;
466 RETURN NEW;
467 END IF;
468END;
469$BODY$
470LANGUAGE plpgsql VOLATILE;
471
472
473-- MEMBERS
474CREATE TRIGGER members_audit BEFORE UPDATE OR INSERT OR DELETE
475 ON members FOR EACH ROW
476EXECUTE PROCEDURE audit_members();
477
478--- VW_MEMBERS ---
479DROP VIEW IF EXISTS vw_members;
480CREATE OR REPLACE VIEW vw_members AS
481SELECT member_id,"name.first","name.last","account.name",email,password,role,telephone,joined,active
482,organization,organizations.org_name as organization_name,organizations.org_email AS organization_email,organizations.org_telephone AS organization_telephone
483FROM members
484 LEFT JOIN organizations
485ON members.organization = organizations.org_id;
486
487--- VW_INACTIVE_MEMBERS ---
488DROP VIEW IF EXISTS vw_inactive_members;
489CREATE OR REPLACE VIEW vw_inactive_members AS
490SELECT member_id,"name.first","name.last","account.name",email
491,role,telephone,joined,active
492,organization,organization_name,organization_email,organization_telephone FROM vw_members
493WHERE active = false;
494
495--- VW_MEMBER_INFO ---
496DROP VIEW IF EXISTS vw_member_info CASCADE;
497CREATE OR REPLACE VIEW vw_member_info AS
498SELECT member_id,"name.first","name.last","account.name",email
499,role,telephone,joined,active
500,organization,organization_name,organization_email,organization_telephone
501FROM vw_members;
502
503--==========================================================================================================
504
505--- PASSWORD_RECOVERY --
506DROP TABLE IF EXISTS password_recovery CASCADE;
507CREATE TABLE IF NOT EXISTS password_recovery (
508 password_recovery_id bigserial PRIMARY KEY
509 ,member bigint NOT NULL CONSTRAINT valid_member_required REFERENCES members(member_id)
510 ,recovery_key text
511 ,requested TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
512 ,used boolean DEFAULT false
513 ,used_at TIMESTAMP WITH TIME ZONE
514);
515
516--- AUD_PASSWORD_RECOVERY --
517DROP TABLE IF EXISTS aud_password_recovery CASCADE;
518CREATE TABLE IF NOT EXISTS aud_password_recovery (
519 password_recovery_id bigint
520 ,member bigint
521 ,recovery_key text
522 ,requested TIMESTAMP WITH TIME ZONE
523 ,used boolean
524 ,used_at TIMESTAMP WITH TIME ZONE
525 ,func varchar(15)
526);
527
528--- PASSWORD_RECOVERY TRIGGER FUNCTION
529CREATE OR REPLACE FUNCTION audit_password_recovery()
530 RETURNS trigger AS
531$BODY$
532BEGIN
533 IF (TG_OP = 'DELETE') THEN
534 INSERT INTO aud_password_recovery (password_recovery_id,member,recovery_key,requested,used,used_at,func)
535 SELECT OLD.password_recovery_id,OLD.member,OLD.recovery_key,OLD.requested,OLD.used,OLD.used_at,TG_OP;
536 RETURN OLD;
537 END IF;
538 IF (TG_OP = 'INSERT') THEN
539 -- INSERT INTO aud_password_recovery (password_recovery_id,member,recovery_key,requested,used,used_at,func)
540 -- SELECT NEW.password_recovery_id,NEW.member,NEW.recovery_key,NEW.requested,NEW.used,NEW.used_at,TG_OP;
541 RETURN NEW;
542 END IF;
543 IF (TG_OP = 'UPDATE') THEN
544 INSERT INTO aud_password_recovery (password_recovery_id,member,recovery_key,requested,used,used_at,func)
545 SELECT OLD.password_recovery_id,OLD.member,OLD.recovery_key,OLD.requested,OLD.used,OLD.used_at,TG_OP;
546 NEW.used_at = now();
547 NEW.used = true;
548 RETURN NEW;
549 END IF;
550END;
551$BODY$
552LANGUAGE plpgsql VOLATILE;
553
554
555-- PASSWORD_RECOVERY AUDIT TRIGGER
556CREATE TRIGGER password_recovery_audit BEFORE UPDATE OR INSERT OR DELETE
557 ON password_recovery FOR EACH ROW
558EXECUTE PROCEDURE audit_password_recovery();
559
560-- VW_PASSWORD_RECOVERY
561DROP VIEW IF EXISTS vw_password_recovery CASCADE;
562CREATE OR REPLACE VIEW vw_password_recovery AS
563SELECT password_recovery_id,recovery_key
564,member ,members."name.first" AS member_first_name ,members."name.last" AS member_last_name ,members.telephone AS member_telephone ,members.email AS member_email ,members.role AS member_role
565FROM password_recovery
566 LEFT JOIN members
567 ON password_recovery.member = members.member_id
568WHERE password_recovery.used = false;
569
570
571--==========================================================================================================
572
573-- GROUPS --
574DROP TABLE IF EXISTS groups CASCADE;
575CREATE TABLE IF NOT EXISTS groups (
576 group_id bigserial PRIMARY KEY
577 ,group_name text NOT NULL CONSTRAINT unique_group_name_required UNIQUE
578 ,group_organization bigint NOT NULL CONSTRAINT group_organization_reqired REFERENCES organizations(org_id)
579 ,group_added TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
580 ,group_active boolean DEFAULT true
581);
582INSERT INTO groups
583( group_id,group_name,group_organization)
584VALUES
585(1,'Members',1),(2,'Staff',1),(3,'Board Members',1);
586
587
588-- AUD_GROUPS --
589DROP TABLE IF EXISTS aud_groups CASCADE;
590CREATE TABLE IF NOT EXISTS aud_groups (
591 group_id bigint
592 ,group_name text
593 ,group_organization bigint
594 ,group_added TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
595 ,group_active boolean DEFAULT false
596 ,func varchar(15)
597);
598
599
600--- AUDIT_GROUPS
601CREATE OR REPLACE FUNCTION audit_groups()
602 RETURNS trigger AS
603$BODY$
604BEGIN
605 IF (TG_OP = 'DELETE') THEN
606 INSERT INTO aud_groups (group_id,group_name,group_organization,group_added,group_active,func)
607 SELECT OLD.group_id,OLD.group_name,OLD.group_organization,OLD.group_added,OLD.group_active,TG_OP;
608 RETURN OLD;
609 END IF;
610 IF (TG_OP = 'INSERT') THEN
611 -- INSERT INTO aud_groups (group_id,group_name,group_organization,group_added,group_active,func)
612 -- SELECT NEW.group_id,NEW.group_name,NEW.group_organization,NEW.group_added,NEW.group_active,TG_OP;
613 RETURN NEW;
614 END IF;
615 IF (TG_OP = 'UPDATE') THEN
616 INSERT INTO aud_groups (group_id,group_name,group_organization,group_added,group_active,func)
617 SELECT OLD.group_id,OLD.group_name,OLD.group_organization,OLD.group_added,OLD.group_active,TG_OP;
618 NEW.group_added=now();
619 RETURN NEW;
620 END IF;
621END;
622$BODY$
623LANGUAGE plpgsql VOLATILE;
624
625
626-- GROUPS_AUDIT
627CREATE TRIGGER groups_audit BEFORE UPDATE OR INSERT OR DELETE
628 ON groups FOR EACH ROW
629EXECUTE PROCEDURE audit_groups();
630
631--- VW_GROUPS ---
632DROP VIEW IF EXISTS vw_groups CASCADE;
633CREATE OR REPLACE VIEW vw_groups AS
634SELECT group_id,group_name,group_added,group_active
635,group_organization ,organizations.org_name AS group_organization_name ,organizations.org_telephone AS group_organization_telephone ,organizations.org_email AS group_organization_email, organizations.org_active AS group_organization_active
636FROM groups
637 LEFT JOIN organizations
638 ON groups.group_organization = organizations.org_id;
639
640
641--==========================================================================================================
642
643-- GROUP_MEMBERS --
644DROP TABLE IF EXISTS group_members CASCADE;
645CREATE TABLE IF NOT EXISTS group_members (
646 mem_id bigserial PRIMARY KEY
647 ,mem_name varchar(55)
648 ,mem_user text
649 ,mem_phone text CONSTRAINT group_member_phone_required NOT NULL
650 ,mem_email varchar(50) -- CONSTRAINT group_member_email_required NOT NULL
651 ,mem_group bigint NOT NULL CONSTRAINT mem_group_reqired REFERENCES groups(group_id)
652 ,mem_added TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
653 ,mem_active boolean DEFAULT true
654 -- ,CONSTRAINT unique_user_per_group UNIQUE(mem_user,mem_group)
655 ,CONSTRAINT unique_email_per_group UNIQUE(mem_email,mem_group)
656 ,CONSTRAINT unique_telephone_per_group UNIQUE(mem_phone,mem_group)
657);
658INSERT INTO group_members
659( mem_id,mem_name,mem_group,mem_phone,mem_email,mem_user)
660VALUES
661(1,'Ian Innocent',1,'0725678447','ianmin2@live.com','ianmin2');
662
663
664-- AUD_GROUP_MEMBERS --
665DROP TABLE IF EXISTS aud_group_members CASCADE;
666CREATE TABLE IF NOT EXISTS aud_group_members (
667 mem_id bigint
668 ,mem_name varchar(55)
669 ,mem_user text
670 ,mem_phone text -- CONSTRAINT group_member_phone_required NOT NULL
671 ,mem_email varchar(50) -- CONSTRAINT group_member_email_required NOT NULL
672 ,mem_group bigint
673 ,mem_added TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
674 ,mem_active boolean DEFAULT true
675 ,func varchar(15)
676);
677
678--- AUDIT_GROUP_MEMBERS
679CREATE OR REPLACE FUNCTION audit_group_members()
680 RETURNS trigger AS
681$BODY$
682BEGIN
683 IF (TG_OP = 'DELETE') THEN
684 INSERT INTO aud_group_members (mem_id,mem_name,mem_user,mem_phone,mem_email,mem_group,mem_added,mem_active,func)
685 SELECT OLD.mem_id,OLD.mem_name,OLD.mem_user,OLD.mem_phone,OLD.mem_email,OLD.mem_group,OLD.mem_added,OLD.mem_active,TG_OP;
686 RETURN OLD;
687 END IF;
688 IF (TG_OP = 'INSERT') THEN
689 -- INSERT INTO aud_group_members (mem_id,mem_name,mem_user,mem_phone,mem_email,mem_group,mem_added,mem_active,func)
690 -- SELECT NEW.mem_id,NEW.mem_name,NEW.mem_user,NEW.mem_phone,NEW.mem_email,NEW.mem_group,NEW.mem_added,NEW.mem_active,TG_OP;
691 RETURN NEW;
692 END IF;
693 IF (TG_OP = 'UPDATE') THEN
694 INSERT INTO aud_group_members (mem_id,mem_name,mem_user,mem_phone,mem_email,mem_group,mem_added,mem_active,func)
695 SELECT OLD.mem_id,OLD.mem_name,OLD.mem_user,OLD.mem_phone,OLD.mem_email,OLD.mem_group,OLD.mem_added,OLD.mem_active,TG_OP;
696 NEW.mem_added = now();
697 RETURN NEW;
698 END IF;
699END;
700$BODY$
701LANGUAGE plpgsql VOLATILE;
702
703
704-- GROUP_MEMBERS_AUDIT
705CREATE TRIGGER group_members_audit BEFORE UPDATE OR INSERT OR DELETE
706 ON group_members FOR EACH ROW
707EXECUTE PROCEDURE audit_group_members();
708
709--- VW_GROUP_MEMBERS ---
710DROP VIEW IF EXISTS vw_group_members CASCADE;
711CREATE OR REPLACE VIEW vw_group_members AS
712SELECT mem_id,mem_name,mem_user,mem_phone,mem_email,mem_added,mem_active
713,mem_group ,groups.group_name AS mem_group_name ,groups.group_organization AS mem_organization
714,organizations.org_name AS mem_organization_name ,organizations.org_telephone AS mem_organization_telephone ,organizations.org_email AS mem_organization_email, organizations.org_active AS mem_organization_active
715FROM group_members
716 LEFT JOIN groups
717 ON group_members.mem_group = groups.group_id
718 LEFT JOIN organizations
719 ON groups.group_organization = organizations.org_id;
720
721
722--- VW_GROUPED_MEMBERS ---
723DROP VIEW IF EXISTS vw_grouped_members CASCADE;
724CREATE OR REPLACE VIEW vw_grouped_members AS
725SELECT mem_group_name AS group_name, min(mem_group) AS group, string_agg(mem_phone,',') AS group_members
726FROM vw_group_members
727WHERE mem_active = true
728AND mem_organization_active = true
729GROUP BY mem_group_name;
730
731
732--==========================================================================================================
733
734
735-- TEMPLATES --
736DROP TABLE IF EXISTS templates CASCADE;
737CREATE TABLE IF NOT EXISTS templates (
738 t_id bigserial PRIMARY KEY
739 ,t_name text NOT NULL CONSTRAINT unique_template_name_required UNIQUE
740 ,t_organization bigint NOT NULL CONSTRAINT valid_template_organization_reqired REFERENCES organizations(org_id)
741 ,t_added TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
742 ,t_active boolean DEFAULT true
743);
744
745INSERT INTO templates
746(t_id,t_name,t_organization)
747VALUES
748(0,'The main bulk sms template is this',1)
749,(1,'This is but a sample SMS template. Edit me as you see fit',1);
750
751
752-- AUD_TEMPLATES --
753DROP TABLE IF EXISTS aud_templates CASCADE;
754CREATE TABLE IF NOT EXISTS aud_templates (
755 t_id bigint
756 ,t_name text
757 ,t_organization bigint
758 ,t_added TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
759 ,t_active boolean DEFAULT false
760 ,func varchar(15)
761);
762
763
764--- AUDIT_TEMPLATES
765CREATE OR REPLACE FUNCTION audit_templates()
766 RETURNS trigger AS
767$BODY$
768BEGIN
769 IF (TG_OP = 'DELETE') THEN
770 INSERT INTO aud_templates (t_id,t_name,t_organization,t_added,t_active,func)
771 SELECT OLD.t_id,OLD.t_name,OLD.t_organization,OLD.t_added,OLD.t_active,TG_OP;
772 RETURN OLD;
773 END IF;
774 IF (TG_OP = 'INSERT') THEN
775 -- INSERT INTO aud_templates (t_id,t_name,t_organization,t_added,t_active,func)
776 -- SELECT NEW.t_id,NEW.t_name,NEW.t_organization,NEW.t_added,NEW.t_active,TG_OP;
777 RETURN NEW;
778 END IF;
779 IF (TG_OP = 'UPDATE') THEN
780 INSERT INTO aud_templates (t_id,t_name,t_organization,t_added,t_active,func)
781 SELECT OLD.t_id,OLD.t_name,OLD.t_organization,OLD.t_added,OLD.t_active,TG_OP;
782 NEW.t_added = now();
783 RETURN NEW;
784 END IF;
785END;
786$BODY$
787LANGUAGE plpgsql VOLATILE;
788
789
790-- TEMPLATES_AUDIT
791CREATE TRIGGER templates_audit BEFORE UPDATE OR INSERT OR DELETE
792 ON templates FOR EACH ROW
793EXECUTE PROCEDURE audit_templates();
794
795--- VW_TEMPLATES ---
796DROP VIEW IF EXISTS vw_templates CASCADE;
797CREATE OR REPLACE VIEW vw_templates AS
798SELECT t_id,t_name,t_added,t_active
799,t_organization ,organizations.org_name AS t_organization_name ,organizations.org_telephone AS t_organization_telephone ,organizations.org_email AS organization_email, organizations.org_active AS t_organization_active
800FROM templates
801 LEFT JOIN organizations
802 ON templates.t_organization = organizations.org_id;
803
804
805--==========================================================================================================
806
807-- LOGS --
808DROP TABLE IF EXISTS logs;
809CREATE TABLE IF NOT EXISTS logs (
810 log_id bigserial PRIMARY KEY
811 ,log_summary jsonb
812 ,log_organization bigint CONSTRAINT valid_organization_required REFERENCES organizations(org_id)
813 ,log_reference bigint NOT NULL
814 ,log_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
815 ,log_balance bigint DEFAULT 0
816);
817
818-- AUD_LOGS --
819DROP TABLE IF EXISTS aud_logs;
820CREATE TABLE IF NOT EXISTS aud_logs (
821 log_id bigint
822 ,log_summary jsonb
823 ,log_organization bigint
824 ,log_reference bigint
825 ,log_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
826 ,log_balance bigint DEFAULT 0
827);
828
829--- AUDIT_LOGS
830CREATE OR REPLACE FUNCTION audit_logs()
831 RETURNS trigger AS
832$BODY$
833BEGIN
834 IF (TG_OP = 'DELETE') THEN
835 INSERT INTO aud_logs (log_id,log_summary,log_organization,log_reference,log_time,log_balance,func)
836 SELECT OLD.log_id,OLD.log_summary,OLD.log_organization,OLD.log_reference,OLD.log_time,OLD.log_balance,TG_OP;
837 RETURN OLD;
838 END IF;
839 IF (TG_OP = 'INSERT') THEN
840 -- INSERT INTO aud_logs (log_id,log_summary,log_organization,log_reference,log_time,log_balance,func)
841 -- SELECT NEW.log_id,NEW.log_summary,NEW.log_organization,NEW.log_reference,NEW.log_time,NEW.log_balance,TG_OP;
842 RETURN NEW;
843 END IF;
844 IF (TG_OP = 'UPDATE') THEN
845 INSERT INTO aud_logs (log_id,log_summary,log_organization,log_reference,log_time,log_balance,func)
846 SELECT OLD.log_id,OLD.log_summary,OLD.log_organization,OLD.log_reference,OLD.log_time,OLD.log_balance,TG_OP;
847 NEW.log_time = now();
848 RETURN NEW;
849 END IF;
850END;
851$BODY$
852LANGUAGE plpgsql VOLATILE;
853
854
855-- LOGS_AUDIT
856CREATE TRIGGER logs_audit BEFORE UPDATE OR INSERT OR DELETE
857 ON logs FOR EACH ROW
858EXECUTE PROCEDURE audit_logs();
859
860-- VW_LOGS --
861DROP VIEW IF EXISTS vw_logs CASCADE;
862CREATE OR REPLACE VIEW vw_logs AS
863SELECT log_id,log_summary,log_time,log_balance
864,log_organization ,organizations.org_name AS log_organization_name ,organizations.org_telephone AS log_organization_telephone ,organizations.org_email AS organization_email, organizations.org_active AS log_organization_active
865,log_reference
866FROM logs
867 LEFT JOIN organizations
868 ON logs.log_organization = organizations.org_id
869;
870
871
872INSERT INTO payments
873(pay_id,pay_org,pay_amount,pay_method,pay_services,pay_message,pay_token)
874VALUES
875(1,1,10,1,'{"payments": [{"services":[1]}]}','10 logged Complementary SMS messages','bixbyte');
876
877 © 2020 GitHub, Inc.
878 Terms
879 Privacy
880 Security
881 Status
882 Help
883
884 Contact GitHub
885 Pricing
886 API
887 Training
888 Blog
889 About