243 lines
6.7 KiB
PL/PgSQL
243 lines
6.7 KiB
PL/PgSQL
DROP SCHEMA public;
|
|
|
|
CREATE SCHEMA public AUTHORIZATION aiber_user;
|
|
|
|
COMMENT ON SCHEMA public IS 'standard public schema';
|
|
|
|
-- DROP SEQUENCE public.casualty_id_seq;
|
|
|
|
CREATE SEQUENCE public.casualty_id_seq
|
|
INCREMENT BY 1
|
|
MINVALUE 1
|
|
MAXVALUE 2147483647
|
|
START 1
|
|
CACHE 1
|
|
NO CYCLE;
|
|
|
|
-- Permissions
|
|
|
|
ALTER SEQUENCE public.casualty_id_seq OWNER TO aiber_user;
|
|
GRANT ALL ON SEQUENCE public.casualty_id_seq TO aiber_user;
|
|
|
|
-- DROP SEQUENCE public.incidentitems_id_seq;
|
|
|
|
CREATE SEQUENCE public.incidentitems_id_seq
|
|
INCREMENT BY 1
|
|
MINVALUE 1
|
|
MAXVALUE 2147483647
|
|
START 1
|
|
CACHE 1
|
|
NO CYCLE;
|
|
|
|
-- Permissions
|
|
|
|
ALTER SEQUENCE public.incidentitems_id_seq OWNER TO aiber_user;
|
|
GRANT ALL ON SEQUENCE public.incidentitems_id_seq TO aiber_user;
|
|
|
|
-- DROP SEQUENCE public.incidentitemsv2_id_seq;
|
|
|
|
CREATE SEQUENCE public.incidentitemsv2_id_seq
|
|
INCREMENT BY 1
|
|
MINVALUE 1
|
|
MAXVALUE 2147483647
|
|
START 1
|
|
CACHE 1
|
|
NO CYCLE;
|
|
|
|
-- Permissions
|
|
|
|
ALTER SEQUENCE public.incidentitemsv2_id_seq OWNER TO aiber_user;
|
|
GRANT ALL ON SEQUENCE public.incidentitemsv2_id_seq TO aiber_user;
|
|
|
|
-- DROP SEQUENCE public.users_id_seq;
|
|
|
|
CREATE SEQUENCE public.users_id_seq
|
|
INCREMENT BY 1
|
|
MINVALUE 1
|
|
MAXVALUE 2147483647
|
|
START 1
|
|
CACHE 1
|
|
NO CYCLE;
|
|
|
|
-- Permissions
|
|
|
|
ALTER SEQUENCE public.users_id_seq OWNER TO aiber_user;
|
|
GRANT ALL ON SEQUENCE public.users_id_seq TO aiber_user;
|
|
-- public.casualty definition
|
|
|
|
-- Drop table
|
|
|
|
-- DROP TABLE public.casualty;
|
|
|
|
CREATE TABLE public.casualty (
|
|
id int4 NOT NULL GENERATED ALWAYS AS IDENTITY,
|
|
incidentid varchar(255) NOT NULL,
|
|
gender varchar(20) NULL DEFAULT ''::character varying,
|
|
dob varchar(12) NULL DEFAULT ''::character varying,
|
|
age int2 NULL,
|
|
alcohol varchar(5) NULL DEFAULT ''::character varying,
|
|
allergies varchar(20) NULL DEFAULT ''::character varying,
|
|
allergydetails text NULL DEFAULT ''::text,
|
|
medicinetaken text NULL DEFAULT ''::text,
|
|
medhist text NULL DEFAULT ''::text,
|
|
flight varchar(100) NULL DEFAULT ''::character varying,
|
|
seat varchar(100) NULL DEFAULT ''::character varying,
|
|
arrivalairport varchar(100) NULL DEFAULT ''::character varying,
|
|
lastmeal text NULL DEFAULT ''::text,
|
|
detailsadded int8 NOT NULL,
|
|
complaint text NULL DEFAULT ''::text,
|
|
history text NULL DEFAULT ''::text,
|
|
deleted int8 NULL DEFAULT 0,
|
|
CONSTRAINT casualty_incidentid_key UNIQUE (incidentid)
|
|
);
|
|
|
|
-- Permissions
|
|
|
|
ALTER TABLE public.casualty OWNER TO aiber_user;
|
|
GRANT ALL ON TABLE public.casualty TO aiber_user;
|
|
|
|
|
|
-- public.incidentitems definition
|
|
|
|
-- Drop table
|
|
|
|
-- DROP TABLE public.incidentitems;
|
|
|
|
CREATE TABLE public.incidentitems (
|
|
id int4 NOT NULL GENERATED ALWAYS AS IDENTITY,
|
|
incidentdate timestamp(0) NOT NULL,
|
|
description text NOT NULL,
|
|
username varchar(50) NOT NULL,
|
|
reportdate timestamp(0) NOT NULL,
|
|
reportlog text NULL,
|
|
incidentid int4 NULL
|
|
);
|
|
|
|
-- Permissions
|
|
|
|
ALTER TABLE public.incidentitems OWNER TO aiber_user;
|
|
GRANT ALL ON TABLE public.incidentitems TO aiber_user;
|
|
|
|
|
|
-- public.incidentitemsv2 definition
|
|
|
|
-- Drop table
|
|
|
|
-- DROP TABLE public.incidentitemsv2;
|
|
|
|
CREATE TABLE public.incidentitemsv2 (
|
|
id int4 NOT NULL GENERATED ALWAYS AS IDENTITY,
|
|
incidentid varchar(255) NOT NULL,
|
|
incidenttime int8 NOT NULL,
|
|
reporttime int8 NOT NULL,
|
|
latitude numeric(12,9) NOT NULL DEFAULT 0,
|
|
longitude numeric(12,9) NOT NULL DEFAULT 0,
|
|
message_type varchar(20) NULL,
|
|
entry_type varchar(20) NULL,
|
|
entry_val1 varchar(255) NULL,
|
|
entry_val2 text NULL,
|
|
ackground int8 NULL DEFAULT 0,
|
|
ackair int8 NULL DEFAULT 0,
|
|
typingtime int8 NOT NULL DEFAULT 0,
|
|
battery int2 NOT NULL DEFAULT 0,
|
|
pluggedin int2 NULL DEFAULT 0,
|
|
username varchar(50) NOT NULL,
|
|
reportlog text NULL,
|
|
ecgtrace varchar(255) NULL
|
|
);
|
|
|
|
-- Permissions
|
|
|
|
ALTER TABLE public.incidentitemsv2 OWNER TO aiber_user;
|
|
GRANT ALL ON TABLE public.incidentitemsv2 TO aiber_user;
|
|
|
|
|
|
-- public.users definition
|
|
|
|
-- Drop table
|
|
|
|
-- DROP TABLE public.users;
|
|
|
|
CREATE TABLE public.users (
|
|
id int4 NOT NULL GENERATED ALWAYS AS IDENTITY,
|
|
username varchar NOT NULL,
|
|
"password" varchar NOT NULL,
|
|
email varchar NOT NULL,
|
|
"_id" varchar NOT NULL
|
|
);
|
|
CREATE INDEX users_id_idx ON public.users USING btree (id);
|
|
CREATE INDEX users_username_idx ON public.users USING btree (username);
|
|
|
|
-- Permissions
|
|
|
|
ALTER TABLE public.users OWNER TO aiber_user;
|
|
GRANT ALL ON TABLE public.users TO aiber_user;
|
|
|
|
|
|
|
|
CREATE OR REPLACE FUNCTION public.upsertcasualty(_incidentid character varying, _gender character varying, _dob character varying, _age smallint, _alcohol character varying, _allergies character varying, _allergydetails text, _medicinetaken text, _medhist text, _flight character varying, _seat character varying, _arrivalairport character varying, _lastmeal text, _detailsadded bigint, _complaint text, _history text, _deleted bigint)
|
|
RETURNS void
|
|
LANGUAGE plpgsql
|
|
AS $function$
|
|
begin
|
|
INSERT INTO public.casualty
|
|
(incidentid, gender, dob, age, alcohol, allergies, allergydetails, medicinetaken, medhist, flight, seat, arrivalairport, lastmeal, detailsadded, complaint, history, deleted)
|
|
VALUES(_incidentid ,
|
|
_gender ::character varying,
|
|
_dob ::character varying,
|
|
_age ,
|
|
_alcohol ::character varying,
|
|
_allergies ::character varying,
|
|
_allergydetails ::text,
|
|
_medicinetaken ::text,
|
|
_medhist ::text,
|
|
_flight ::character varying,
|
|
_seat ::character varying,
|
|
_arrivalairport ::character varying,
|
|
_lastmeal ::text,
|
|
_detailsadded ,
|
|
_complaint ::text,
|
|
_history ::text,
|
|
_deleted)
|
|
|
|
on conflict (incidentid)
|
|
do update set gender = excluded.gender, dob = excluded.dob, age = excluded.age, alcohol = excluded.alcohol,
|
|
allergies = excluded.allergies, allergydetails = excluded.allergydetails, medicinetaken = excluded.medicinetaken, medhist = excluded.medhist,
|
|
flight = excluded.flight, seat = excluded.seat, arrivalairport = excluded.arrivalairport, lastmeal = excluded.lastmeal,
|
|
detailsadded = excluded.detailsadded, complaint = excluded.complaint, history = excluded.history, deleted = excluded.deleted;
|
|
end
|
|
$function$
|
|
;
|
|
|
|
-- Permissions
|
|
|
|
ALTER FUNCTION public.upsertcasualty(varchar,varchar,varchar,int2,varchar,varchar,text,text,text,varchar,varchar,varchar,text,int8,text,text,int8) OWNER TO aiber_user;
|
|
GRANT ALL ON FUNCTION public.upsertcasualty(varchar,varchar,varchar,int2,varchar,varchar,text,text,text,varchar,varchar,varchar,text,int8,text,text,int8) TO aiber_user;
|
|
|
|
CREATE OR REPLACE FUNCTION public.upserttest(_incidentid character varying, _gender character varying)
|
|
RETURNS void
|
|
LANGUAGE plpgsql
|
|
AS $function$
|
|
begin
|
|
INSERT INTO public.casualty
|
|
(incidentid, gender)
|
|
VALUES(_incidentid ,
|
|
_gender ::character varying)
|
|
|
|
on conflict (incidentid)
|
|
do update set gender = excluded.gender;
|
|
end
|
|
$function$
|
|
;
|
|
|
|
-- Permissions
|
|
|
|
ALTER FUNCTION public.upserttest(varchar,varchar) OWNER TO aiber_user;
|
|
GRANT ALL ON FUNCTION public.upserttest(varchar,varchar) TO aiber_user;
|
|
|
|
|
|
-- Permissions
|
|
|
|
GRANT ALL ON SCHEMA public TO aiber_user;
|
|
GRANT ALL ON SCHEMA public TO public;
|