SafeDispatch/UpdateHelper/CreateMissingPrimaryKeys.sql

276 lines
8.1 KiB
MySQL
Raw Permalink Normal View History

CREATE OR REPLACE FUNCTION CreateMissingPrimaryKeys()
RETURNS void
LANGUAGE 'plpgsql'
AS
$$
BEGIN
--============
--call_patch
--============
if NOT exists (select constraint_name from information_schema.table_constraints where table_name = 'call_patch' and constraint_type = 'PRIMARY KEY') then
ALTER TABLE public.call_patch
ADD CONSTRAINT call_patch_pkey PRIMARY KEY(idx);
RAISE NOTICE '%', 'Created primary key : call_patch_pkey!';
else
RAISE NOTICE '%', 'Primary key already exists for table_name = call_patch!';
end if;
--============
--emailservice ( duplicate row )
--============
if NOT exists (select constraint_name from information_schema.table_constraints where table_name = 'emailservice' and constraint_type = 'PRIMARY KEY') then
ALTER TABLE public.emailservice
ADD CONSTRAINT emailservice_pkey PRIMARY KEY(id);
RAISE NOTICE '%', 'Created primary key : emailservice_pkey !';
else
RAISE NOTICE '%', 'Primary key already exists for table_name = emailservice !';
end if;
--============
--emergdi
--============
if NOT exists (select constraint_name from information_schema.table_constraints where table_name = 'emergdi' and constraint_type = 'PRIMARY KEY') then
ALTER TABLE public.emergdi
ADD CONSTRAINT emergdi_pkey PRIMARY KEY(idx);
RAISE NOTICE '%', 'Created primary key : emergdi_pkey !';
else
RAISE NOTICE '%', 'Primary key already exists for table_name = emergdi !';
end if;
--========
--groups
--========
if NOT exists (select constraint_name from information_schema.table_constraints where table_name = 'groups' and constraint_type = 'PRIMARY KEY') then
ALTER TABLE public.groups
ADD CONSTRAINT groups_pkey PRIMARY KEY(id);
RAISE NOTICE '%', 'Created primary key : groups_pkey !';
else
RAISE NOTICE '%', 'Primary key already exists for table_name = groups !';
end if;
--============
-- icon_theme
--============
if NOT exists (select constraint_name from information_schema.table_constraints where table_name = 'icon_theme' and constraint_type = 'PRIMARY KEY') then
ALTER TABLE public.icon_theme
ADD CONSTRAINT icon_theme_pkey PRIMARY KEY(id);
RAISE NOTICE '%', 'Created primary key : icon_theme_pkey !';
else
RAISE NOTICE '%', 'Primary key already exists for table_name = icon_theme !';
end if;
--============
-- jobtickets
--============
if NOT exists (select constraint_name from information_schema.table_constraints where table_name = 'jobtickets' and constraint_type = 'PRIMARY KEY') then
ALTER TABLE public.jobtickets
ADD CONSTRAINT jobtickets_pkey PRIMARY KEY(ticket_id);
RAISE NOTICE '%', 'Created primary key : jobtickets_pkey !';
else
RAISE NOTICE '%', 'Primary key already exists for table_name = jobtickets !';
end if;
--================
-- jobtickets_log
--================
if NOT exists (select constraint_name from information_schema.table_constraints where table_name = 'jobtickets_log' and constraint_type = 'PRIMARY KEY') then
ALTER TABLE public.jobtickets_log
ADD CONSTRAINT jobtickets_log_pkey PRIMARY KEY(id);
RAISE NOTICE '%', 'Created primary key : jobtickets_log_pkey !';
else
RAISE NOTICE '%', 'Primary key already exists for table_name = jobtickets_log !';
end if;
--================
-- mobile_contacts
--================
if NOT exists (select constraint_name from information_schema.table_constraints where table_name = 'mobile_contacts' and constraint_type = 'PRIMARY KEY') then
ALTER TABLE public.mobile_contacts
ADD CONSTRAINT mobile_contacts_pkey PRIMARY KEY(idx);
RAISE NOTICE '%', 'Created primary key : mobile_contacts_pkey !';
else
RAISE NOTICE '%', 'Primary key already exists for table_name = mobile_contacts !';
end if;
--================
-- placetype
--================
if NOT exists (select constraint_name from information_schema.table_constraints where table_name = 'placetype' and constraint_type = 'PRIMARY KEY') then
ALTER TABLE public.placetype
ADD CONSTRAINT placetype_pkey PRIMARY KEY(type_id);
RAISE NOTICE '%', 'Created primary key : placetype_pkey !';
else
RAISE NOTICE '%', 'Primary key already exists for table_name = placetype !';
end if;
--================
-- radio_mobile
--================
if NOT exists (select constraint_name from information_schema.table_constraints where table_name = 'radio_mobile' and constraint_type = 'PRIMARY KEY') then
ALTER TABLE public.radio_mobile
ADD CONSTRAINT radio_mobile_pkey PRIMARY KEY(idx);
RAISE NOTICE '%', 'Created primary key : radio_mobile_pkey !';
else
RAISE NOTICE '%', 'Primary key already exists for table_name = radio_mobile !';
end if;
--================
-- reports
--================
if NOT exists (select constraint_name from information_schema.table_constraints where table_name = 'reports' and constraint_type = 'PRIMARY KEY') then
ALTER TABLE public.reports
ADD CONSTRAINT reports_pkey PRIMARY KEY(idx);
RAISE NOTICE '%', 'Created primary key : reports_pkey !';
else
RAISE NOTICE '%', 'Primary key already exists for table_name = reports !';
end if;
--================
-- sd_list
--================
if NOT exists (select constraint_name from information_schema.table_constraints where table_name = 'sd_list' and constraint_type = 'PRIMARY KEY') then
ALTER TABLE public.sd_list
ADD CONSTRAINT sd_list_pkey PRIMARY KEY(sd_id);
RAISE NOTICE '%', 'Created primary key : sd_list_pkey !';
else
RAISE NOTICE '%', 'Primary key already exists for table_name = sd_list !';
end if;
--============
-- users_group
--============
if NOT exists (select constraint_name from information_schema.table_constraints where table_name = 'users_group' and constraint_type = 'PRIMARY KEY') then
ALTER TABLE public.users_group
ADD CONSTRAINT users_group_pkey PRIMARY KEY(idx);
RAISE NOTICE '%', 'Created primary key : users_group_pkey !';
else
RAISE NOTICE '%', 'Primary key already exists for table_name = users_group !';
end if;
--============
-- user_radio
--============
if NOT exists (select constraint_name from information_schema.table_constraints where table_name = 'user_radio' and constraint_type = 'PRIMARY KEY') then
ALTER TABLE public.user_radio
ADD CONSTRAINT user_radio_pkey PRIMARY KEY(radio_id, user_id);
RAISE NOTICE '%', 'Created primary key : user_radio_pkey !';
else
RAISE NOTICE '%', 'Primary key already exists for table_name = user_radio !';
end if;
--============
-- vehicle_group
--============
if NOT exists (select constraint_name from information_schema.table_constraints where table_name = 'vehicle_group' and constraint_type = 'PRIMARY KEY') then
ALTER TABLE public.vehicle_group
ADD CONSTRAINT vehicle_group_pkey PRIMARY KEY(idx);
RAISE NOTICE '%', 'Created primary key : vehicle_group_pkey !';
else
RAISE NOTICE '%', 'Primary key already exists for table_name = vehicle_group !';
end if;
--============
-- vehicle_user
--============
if NOT exists (select constraint_name from information_schema.table_constraints where table_name = 'vehicle_user' and constraint_type = 'PRIMARY KEY') then
ALTER TABLE public.vehicle_user
ADD CONSTRAINT vehicle_user_pkey PRIMARY KEY(veh_id, user_id);
RAISE NOTICE '%', 'Created primary key : vehicle_user_pkey !';
else
RAISE NOTICE '%', 'Primary key already exists for table_name = vehicle_user !';
end if;
--============
-- zonenames
--============
if NOT exists (select constraint_name from information_schema.table_constraints where table_name = 'zonename' and constraint_type = 'PRIMARY KEY') then
ALTER TABLE public.zonename
ADD CONSTRAINT zonename_pkey PRIMARY KEY(idx);
RAISE NOTICE '%', 'Created primary key : zonename_pkey !';
else
RAISE NOTICE '%', 'Primary key already exists for table_name = zonename !';
end if;
--============
-- zonepoints
--============
if NOT exists (select constraint_name from information_schema.table_constraints where table_name = 'zonepoints' and constraint_type = 'PRIMARY KEY') then
ALTER TABLE public.zonepoints
ADD CONSTRAINT zonepoints_pkey PRIMARY KEY(idx);
RAISE NOTICE '%', 'Created primary key : zonepoints_pkey!';
else
RAISE NOTICE '%', 'Primary key already exists for table_name = zonepoints !';
end if;
END;
$$;