mirror of
https://gitlab.silvrtree.co.uk/martind2000/obrand-admin-server.git
synced 2025-01-10 22:05:07 +00:00
669 lines
16 KiB
PL/PgSQL
669 lines
16 KiB
PL/PgSQL
-- Database: "oBrand"
|
|
|
|
DROP DATABASE "oBrand";
|
|
|
|
-- Role: obrand
|
|
|
|
DROP ROLE obrand;
|
|
|
|
CREATE ROLE obrand LOGIN
|
|
ENCRYPTED PASSWORD 'md51e16472d06fb312d14e3001f44e9460e'
|
|
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
|
|
|
|
CREATE DATABASE "oBrand"
|
|
WITH OWNER = postgres
|
|
ENCODING = 'UTF8'
|
|
TABLESPACE = pg_default
|
|
LC_COLLATE = 'C'
|
|
LC_CTYPE = 'C'
|
|
CONNECTION LIMIT = -1;
|
|
GRANT ALL ON DATABASE "oBrand" TO postgres;
|
|
|
|
|
|
GRANT CONNECT ON DATABASE "oBrand" TO obrand;
|
|
REVOKE ALL ON DATABASE "oBrand" FROM public;
|
|
|
|
ALTER DEFAULT PRIVILEGES
|
|
GRANT INSERT, SELECT, UPDATE ON TABLES
|
|
TO public;
|
|
|
|
ALTER DEFAULT PRIVILEGES
|
|
GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES
|
|
TO postgres;
|
|
|
|
|
|
|
|
--
|
|
|
|
|
|
|
|
|
|
ALTER TABLE "profile" DROP CONSTRAINT IF EXISTS "profile_fk0";
|
|
|
|
ALTER TABLE "profile" DROP CONSTRAINT IF EXISTS "profile_fk1";
|
|
|
|
ALTER TABLE "venue" DROP CONSTRAINT IF EXISTS "venue_fk0";
|
|
|
|
ALTER TABLE "billing" DROP CONSTRAINT IF EXISTS "billing_fk0";
|
|
|
|
DROP TABLE IF EXISTS "logins";
|
|
|
|
DROP TABLE IF EXISTS "profile";
|
|
|
|
DROP TABLE IF EXISTS "company";
|
|
|
|
DROP TABLE IF EXISTS "venue";
|
|
|
|
DROP TABLE IF EXISTS "billing";
|
|
|
|
DROP TABLE IF EXISTS "master_beacons";
|
|
|
|
DROP TABLE IF EXISTS "pages";
|
|
|
|
|
|
-- SEQUENCES
|
|
|
|
|
|
-- Sequence: public.billing_id_seq
|
|
|
|
-- DROP SEQUENCE public.billing_id_seq;
|
|
|
|
CREATE SEQUENCE public.billing_id_seq
|
|
INCREMENT 1
|
|
MINVALUE 1
|
|
MAXVALUE 9223372036854775807
|
|
START 1
|
|
CACHE 1;
|
|
ALTER TABLE public.billing_id_seq
|
|
OWNER TO postgres;
|
|
|
|
|
|
|
|
-- Sequence: public.company_id_seq
|
|
|
|
-- DROP SEQUENCE public.company_id_seq;
|
|
|
|
CREATE SEQUENCE public.company_id_seq
|
|
INCREMENT 1
|
|
MINVALUE 1
|
|
MAXVALUE 9223372036854775807
|
|
START 2
|
|
CACHE 1;
|
|
ALTER TABLE public.company_id_seq
|
|
OWNER TO postgres;
|
|
GRANT ALL ON SEQUENCE public.company_id_seq TO postgres;
|
|
GRANT SELECT, UPDATE ON SEQUENCE public.company_id_seq TO obrand;
|
|
|
|
|
|
|
|
|
|
-- Sequence: public.logins_id_seq
|
|
|
|
-- DROP SEQUENCE public.logins_id_seq;
|
|
|
|
CREATE SEQUENCE public.logins_id_seq
|
|
INCREMENT 1
|
|
MINVALUE 1
|
|
MAXVALUE 9223372036854775807
|
|
START 4
|
|
CACHE 1;
|
|
ALTER TABLE public.logins_id_seq
|
|
OWNER TO postgres;
|
|
GRANT ALL ON SEQUENCE public.logins_id_seq TO postgres;
|
|
GRANT SELECT, UPDATE ON SEQUENCE public.logins_id_seq TO obrand;
|
|
|
|
|
|
|
|
|
|
-- Sequence: public.pages_id_seq
|
|
|
|
-- DROP SEQUENCE public.pages_id_seq;
|
|
|
|
CREATE SEQUENCE public.pages_id_seq
|
|
INCREMENT 1
|
|
MINVALUE 1
|
|
MAXVALUE 9223372036854775807
|
|
START 35
|
|
CACHE 1;
|
|
ALTER TABLE public.pages_id_seq
|
|
OWNER TO postgres;
|
|
GRANT ALL ON SEQUENCE public.pages_id_seq TO postgres;
|
|
GRANT SELECT, UPDATE ON SEQUENCE public.pages_id_seq TO obrand;
|
|
|
|
|
|
|
|
|
|
-- Sequence: public.profile_id_seq
|
|
|
|
-- DROP SEQUENCE public.profile_id_seq;
|
|
|
|
CREATE SEQUENCE public.profile_id_seq
|
|
INCREMENT 1
|
|
MINVALUE 1
|
|
MAXVALUE 9223372036854775807
|
|
START 2
|
|
CACHE 1;
|
|
ALTER TABLE public.profile_id_seq
|
|
OWNER TO postgres;
|
|
GRANT ALL ON SEQUENCE public.profile_id_seq TO postgres;
|
|
GRANT SELECT, UPDATE ON SEQUENCE public.profile_id_seq TO obrand;
|
|
|
|
|
|
|
|
|
|
|
|
-- Sequence: public.venue_id_seq
|
|
|
|
-- DROP SEQUENCE public.venue_id_seq;
|
|
|
|
CREATE SEQUENCE public.venue_id_seq
|
|
INCREMENT 1
|
|
MINVALUE 1
|
|
MAXVALUE 9223372036854775807
|
|
START 1
|
|
CACHE 1;
|
|
ALTER TABLE public.venue_id_seq
|
|
OWNER TO postgres;
|
|
|
|
|
|
|
|
|
|
-- ----
|
|
|
|
|
|
|
|
|
|
|
|
-- Table: public.company
|
|
|
|
-- DROP TABLE public.company;
|
|
|
|
CREATE TABLE public.company
|
|
(
|
|
id integer NOT NULL DEFAULT nextval('company_id_seq'::regclass),
|
|
cid character varying(22) NOT NULL,
|
|
company_name character varying(100) NOT NULL,
|
|
address1 character varying(150) NOT NULL,
|
|
address2 character varying(150),
|
|
address3 character varying(150),
|
|
town character varying(150) NOT NULL,
|
|
county character varying(150),
|
|
postcode character varying(12) NOT NULL,
|
|
country integer,
|
|
pcontact character varying(20) NOT NULL,
|
|
ocontact character varying(20),
|
|
mobile character varying(20),
|
|
email character varying(150) NOT NULL,
|
|
CONSTRAINT company_pk PRIMARY KEY (id)
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE public.company
|
|
OWNER TO postgres;
|
|
GRANT SELECT, UPDATE, INSERT ON TABLE public.company TO public;
|
|
GRANT ALL ON TABLE public.company TO postgres;
|
|
|
|
|
|
|
|
|
|
|
|
-- Table: public.billing
|
|
|
|
-- DROP TABLE public.billing;
|
|
|
|
CREATE TABLE public.billing
|
|
(
|
|
id integer NOT NULL DEFAULT nextval('billing_id_seq'::regclass),
|
|
company_id integer NOT NULL,
|
|
CONSTRAINT billing_pk PRIMARY KEY (id),
|
|
CONSTRAINT billing_fk0 FOREIGN KEY (company_id)
|
|
REFERENCES public.company (id) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE public.billing
|
|
OWNER TO postgres;
|
|
GRANT SELECT, UPDATE, INSERT ON TABLE public.billing TO public;
|
|
GRANT ALL ON TABLE public.billing TO postgres;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-- Table: public.logins
|
|
|
|
-- DROP TABLE public.logins;
|
|
|
|
CREATE TABLE public.logins
|
|
(
|
|
id integer NOT NULL DEFAULT nextval('logins_id_seq'::regclass),
|
|
username character varying(100) NOT NULL,
|
|
email character varying(150) NOT NULL,
|
|
password_hash character varying(78) NOT NULL,
|
|
password_reset_token character varying(128),
|
|
uid character varying(22),
|
|
CONSTRAINT logins_pk PRIMARY KEY (id),
|
|
CONSTRAINT logins_email_key UNIQUE (email),
|
|
CONSTRAINT logins_password_reset_token_key UNIQUE (password_reset_token),
|
|
CONSTRAINT logins_uid_key UNIQUE (uid),
|
|
CONSTRAINT logins_username_key UNIQUE (username)
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE public.logins
|
|
OWNER TO postgres;
|
|
GRANT SELECT, UPDATE, INSERT ON TABLE public.logins TO public;
|
|
GRANT ALL ON TABLE public.logins TO postgres;
|
|
GRANT SELECT, UPDATE, INSERT, DELETE, TRIGGER ON TABLE public.logins TO obrand;
|
|
|
|
|
|
|
|
|
|
-- Table: public.master_beacons
|
|
|
|
-- DROP TABLE public.master_beacons;
|
|
|
|
CREATE TABLE public.master_beacons
|
|
(
|
|
id bigint NOT NULL,
|
|
uid uuid NOT NULL,
|
|
CONSTRAINT master_beacons_pk PRIMARY KEY (id)
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE public.master_beacons
|
|
OWNER TO postgres;
|
|
GRANT SELECT, UPDATE, INSERT ON TABLE public.master_beacons TO public;
|
|
GRANT ALL ON TABLE public.master_beacons TO postgres;
|
|
|
|
|
|
|
|
|
|
|
|
-- Table: public.pages
|
|
|
|
-- DROP TABLE public.pages;
|
|
|
|
CREATE TABLE public.pages
|
|
(
|
|
id integer NOT NULL DEFAULT nextval('pages_id_seq'::regclass),
|
|
cid character varying(22),
|
|
vid character varying(22),
|
|
pid character varying(22),
|
|
content integer,
|
|
title character varying(100) NOT NULL,
|
|
data json NOT NULL,
|
|
CONSTRAINT pages_pk PRIMARY KEY (id)
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE public.pages
|
|
OWNER TO postgres;
|
|
GRANT SELECT, UPDATE, INSERT ON TABLE public.pages TO public;
|
|
GRANT ALL ON TABLE public.pages TO postgres;
|
|
|
|
|
|
|
|
|
|
-- Table: public.profile
|
|
|
|
-- DROP TABLE public.profile;
|
|
|
|
CREATE TABLE public.profile
|
|
(
|
|
id integer NOT NULL DEFAULT nextval('profile_id_seq'::regclass),
|
|
uid character varying(22) NOT NULL,
|
|
forename character varying(75) NOT NULL,
|
|
surname character varying(75) NOT NULL,
|
|
gender integer NOT NULL,
|
|
dob date NOT NULL,
|
|
bio text NOT NULL,
|
|
member_of character varying(22),
|
|
CONSTRAINT profile_pk PRIMARY KEY (id),
|
|
CONSTRAINT profile_fk0 FOREIGN KEY (uid)
|
|
REFERENCES public.logins (uid) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE public.profile
|
|
OWNER TO postgres;
|
|
GRANT SELECT, UPDATE, INSERT ON TABLE public.profile TO public;
|
|
GRANT ALL ON TABLE public.profile TO postgres;
|
|
|
|
|
|
|
|
|
|
--
|
|
|
|
|
|
|
|
-- Table: public.venue
|
|
|
|
-- DROP TABLE public.venue;
|
|
|
|
CREATE TABLE public.venue
|
|
(
|
|
id integer NOT NULL DEFAULT nextval('venue_id_seq'::regclass),
|
|
venue_name character varying(100) NOT NULL,
|
|
address1 character varying(150) NOT NULL,
|
|
address2 character varying(150),
|
|
address3 character varying(150),
|
|
town character varying(150) NOT NULL,
|
|
county character varying(150),
|
|
postcode character varying(12) NOT NULL,
|
|
country integer,
|
|
pcontact character varying(20) NOT NULL,
|
|
ocontact character varying(20),
|
|
mobile character varying(20),
|
|
email character varying(150) NOT NULL,
|
|
company_id integer NOT NULL,
|
|
CONSTRAINT venue_pk PRIMARY KEY (id),
|
|
CONSTRAINT venue_fk0 FOREIGN KEY (company_id)
|
|
REFERENCES public.company (id) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE public.venue
|
|
OWNER TO postgres;
|
|
GRANT SELECT, UPDATE, INSERT ON TABLE public.venue TO public;
|
|
GRANT ALL ON TABLE public.venue TO postgres;
|
|
|
|
|
|
|
|
|
|
--
|
|
|
|
|
|
|
|
|
|
--ALTER TABLE "profile" ADD CONSTRAINT "profile_fk0" FOREIGN KEY ("uid") REFERENCES "logins"("uid");
|
|
--ALTER TABLE "profile" ADD CONSTRAINT "profile_fk1" FOREIGN KEY ("member_of") REFERENCES "company"("cid");
|
|
|
|
--ALTER TABLE "venue" ADD CONSTRAINT "venue_fk0" FOREIGN KEY ("company_id") REFERENCES "company"("id");
|
|
|
|
--ALTER TABLE "billing" ADD CONSTRAINT "billing_fk0" FOREIGN KEY ("company_id") REFERENCES "company"("id");
|
|
|
|
grant connect on database "oBrand" to obrand;
|
|
|
|
GRANT SELECT, UPDATE, INSERT, DELETE, TRIGGER ON TABLE public.logins TO obrand;
|
|
|
|
grant select, update on logins_id_seq to obrand;
|
|
grant select, update on profile_id_seq to obrand;
|
|
grant select, update on company_id_seq to obrand;
|
|
grant select, update on pages_pk to obrand;
|
|
|
|
|
|
|
|
|
|
|
|
-- functions
|
|
|
|
|
|
-- Function: public.getaccountdetails(character varying)
|
|
|
|
-- DROP FUNCTION public.getaccountdetails(character varying);
|
|
|
|
CREATE OR REPLACE FUNCTION public.getaccountdetails(IN _uid character varying)
|
|
RETURNS TABLE(id integer, email character varying, uid character varying, forename character varying, surname character varying, member_of character varying) AS
|
|
$BODY$
|
|
|
|
BEGIN
|
|
|
|
|
|
RETURN QUERY
|
|
SELECT
|
|
|
|
logins.id,
|
|
logins.email,
|
|
logins.uid,
|
|
profile.forename,
|
|
profile.surname,
|
|
profile.member_of
|
|
FROM logins
|
|
LEFT JOIN profile ON profile.uid = logins.uid
|
|
WHERE logins.uid = _uid;
|
|
|
|
|
|
END;
|
|
$BODY$
|
|
LANGUAGE plpgsql VOLATILE
|
|
COST 100
|
|
ROWS 1000;
|
|
ALTER FUNCTION public.getaccountdetails(character varying)
|
|
OWNER TO postgres;
|
|
|
|
|
|
--
|
|
|
|
|
|
-- Function: public.insert_user(character varying, character varying, character varying, character varying)
|
|
|
|
-- DROP FUNCTION public.insert_user(character varying, character varying, character varying, character varying);
|
|
|
|
CREATE OR REPLACE FUNCTION public.insert_user(
|
|
_username character varying,
|
|
_email character varying,
|
|
_passwordhash character varying,
|
|
_uid character varying)
|
|
RETURNS void AS
|
|
$BODY$
|
|
|
|
BEGIN
|
|
INSERT into logins(username, email, password_hash, uid) Values(_username,_email,_passwordHash,_uid);
|
|
END;
|
|
$BODY$
|
|
LANGUAGE plpgsql VOLATILE
|
|
COST 100;
|
|
ALTER FUNCTION public.insert_user(character varying, character varying, character varying, character varying)
|
|
OWNER TO postgres;
|
|
|
|
|
|
|
|
--
|
|
|
|
-- Function: public.makememberof(character varying, character varying)
|
|
|
|
-- DROP FUNCTION public.makememberof(character varying, character varying);
|
|
|
|
CREATE OR REPLACE FUNCTION public.makememberof(
|
|
_uid character varying,
|
|
_cid character varying)
|
|
RETURNS void AS
|
|
$BODY$
|
|
|
|
BEGIN
|
|
UPDATE profile SET member_of = _cid WHERE uid = _uid;
|
|
|
|
|
|
END;
|
|
$BODY$
|
|
LANGUAGE plpgsql VOLATILE
|
|
COST 100;
|
|
ALTER FUNCTION public.makememberof(character varying, character varying)
|
|
OWNER TO postgres;
|
|
|
|
|
|
--
|
|
|
|
|
|
|
|
|
|
|
|
-- Function: public.upsert_company(character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, integer, character varying, character varying, character varying, character varying)
|
|
|
|
-- DROP FUNCTION public.upsert_company(character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, integer, character varying, character varying, character varying, character varying);
|
|
|
|
CREATE OR REPLACE FUNCTION public.upsert_company(
|
|
_cid character varying,
|
|
_company_name character varying,
|
|
_address1 character varying,
|
|
_address2 character varying,
|
|
_address3 character varying,
|
|
_town character varying,
|
|
_county character varying,
|
|
_postcode character varying,
|
|
_country integer,
|
|
_pcontact character varying,
|
|
_ocontact character varying,
|
|
_mobile character varying,
|
|
_email character varying)
|
|
RETURNS void AS
|
|
$BODY$
|
|
|
|
BEGIN
|
|
WITH upsert AS (UPDATE company
|
|
SET cid = _cid,
|
|
company_name = _company_name,
|
|
address1 = _address1,
|
|
address2 = _address2,
|
|
address3 = _address3,
|
|
town = _town,
|
|
county = _county,
|
|
postcode = _postcode,
|
|
country = _country,
|
|
pcontact = _pcontact,
|
|
ocontact = _ocontact,
|
|
mobile = _mobile,
|
|
email = _email
|
|
|
|
WHERE cid = _cid
|
|
RETURNING *)
|
|
INSERT INTO company (
|
|
cid,
|
|
company_name,
|
|
address1,
|
|
address2,
|
|
address3,
|
|
town,
|
|
county,
|
|
postcode,
|
|
country,
|
|
pcontact,
|
|
ocontact,
|
|
mobile,
|
|
email
|
|
) SELECT
|
|
_cid,
|
|
_company_name,
|
|
_address1,
|
|
_address2,
|
|
_address3,
|
|
_town,
|
|
_county,
|
|
_postcode,
|
|
_country,
|
|
_pcontact,
|
|
_ocontact,
|
|
_mobile,
|
|
_email
|
|
WHERE NOT EXISTS(SELECT *
|
|
FROM upsert);
|
|
|
|
|
|
END;
|
|
$BODY$
|
|
LANGUAGE plpgsql VOLATILE
|
|
COST 100;
|
|
ALTER FUNCTION public.upsert_company(character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, integer, character varying, character varying, character varying, character varying)
|
|
OWNER TO postgres;
|
|
|
|
|
|
|
|
|
|
--
|
|
|
|
|
|
|
|
-- Function: public.upsert_page(character varying, character varying, character varying, integer, character varying, json)
|
|
|
|
-- DROP FUNCTION public.upsert_page(character varying, character varying, character varying, integer, character varying, json);
|
|
|
|
CREATE OR REPLACE FUNCTION public.upsert_page(
|
|
_cid character varying,
|
|
_vid character varying,
|
|
_pid character varying,
|
|
_content integer,
|
|
_title character varying,
|
|
_data json)
|
|
RETURNS void AS
|
|
$BODY$
|
|
|
|
BEGIN
|
|
WITH upsert AS (UPDATE pages
|
|
SET cid = _cid, vid = _vid, content = _content, title = _title, data = _data
|
|
WHERE pid = _pid
|
|
RETURNING *)
|
|
INSERT INTO pages (cid, vid, pid, content, title, data) SELECT
|
|
_cid,
|
|
_vid,
|
|
_pid,
|
|
_content,
|
|
_title,
|
|
_data
|
|
WHERE NOT EXISTS(SELECT *
|
|
FROM upsert);
|
|
|
|
|
|
END;
|
|
$BODY$
|
|
LANGUAGE plpgsql VOLATILE
|
|
COST 100;
|
|
ALTER FUNCTION public.upsert_page(character varying, character varying, character varying, integer, character varying, json)
|
|
OWNER TO postgres;
|
|
|
|
|
|
|
|
|
|
--
|
|
|
|
|
|
-- Function: public.upsert_profile(character varying, character varying, character varying, integer, date, text)
|
|
|
|
-- DROP FUNCTION public.upsert_profile(character varying, character varying, character varying, integer, date, text);
|
|
|
|
CREATE OR REPLACE FUNCTION public.upsert_profile(
|
|
_uid character varying,
|
|
_forename character varying,
|
|
_surname character varying,
|
|
_gender integer,
|
|
_dob date,
|
|
_bio text)
|
|
RETURNS void AS
|
|
$BODY$
|
|
|
|
BEGIN
|
|
WITH upsert AS (UPDATE profile
|
|
SET forename = _forename, surname = _surname, gender = _gender, dob = _dob, bio = _bio
|
|
WHERE uid = _uid
|
|
RETURNING *)
|
|
INSERT INTO profile (uid, forename, surname, gender, dob, bio) SELECT
|
|
_uid,
|
|
_forename,
|
|
_surname,
|
|
_gender,
|
|
_dob,
|
|
_bio
|
|
WHERE NOT EXISTS(SELECT *
|
|
FROM upsert);
|
|
|
|
|
|
END;
|
|
$BODY$
|
|
LANGUAGE plpgsql VOLATILE
|
|
COST 100;
|
|
ALTER FUNCTION public.upsert_profile(character varying, character varying, character varying, integer, date, text)
|
|
OWNER TO postgres;
|