SafeDispatch/UpdateHelper/RemoveRecordsWithoutRelatedData.sql

278 lines
8.2 KiB
MySQL
Raw Permalink Normal View History


-- in order to add foreign keys is mandatory to have right values for related data.
-- so we should remove the records without related data
CREATE OR REPLACE FUNCTION RemoveRecordsWithoutRelatedData()
RETURNS void
LANGUAGE 'plpgsql'
AS
$$
BEGIN
if ( (select count(*) from emailservice where id = 2) > 1 ) then
delete from public.emailservice where id = 2;
INSERT INTO public.emailservice(
id, "user", pass, enable, pop_server, pop_port, smtp_server, smtp_port, is_pop_ssl, is_smtp_ssl, last_email_time)
VALUES (2, 'poll', 'poll', 0, 'pop.gmail.com', 995, 'smtp.gmail.com', 587, true, true, 0);
end if;
--=============================================
-- insert a dummy user if it doesn't exist
--=============================================
if not exists ( select * from public.users where userid = 0) then
INSERT INTO users(userid, firstname, lastname, login, password, user_type, ison,backup_user_id) VALUES (0, 'anything', 'anything', 'anything', 'anything', 100, 0, -1 );
end if;
--=============================================
-- insert a dummy suscriber if it doesn't exist
--=============================================
if not exists ( select * from subscriber where sc_id = 0 ) then
insert into subscriber (sc_id, imei) values ( 0, '0');
end if;
--==========================================================
-- replace the invalid user ids to the id of the dummy user
--==========================================================
if exists ( select * from public.sms where user_id < 0) then
update sms set user_id = 0 where user_id = -1;
end if;
--========================================
-- remove the emergalarm records
-- whose subscriber doesn't exists any more
--========================================
delete from public.emergalarm
where sc_id in ( select distinct sc_id from public.emergalarm
except
select sc_id from public.subscriber
)
or user_id in ( select distinct user_id from public.emergalarm
except
select userid from public.users
);
--========================================
-- remove the groups
-- whose radio_gw doesn't exists any more
--========================================
delete from public.groups
where r_gw_id in ( select distinct r_gw_id from public.groups
except
select "ID" from public.radio_gw
);
--========================================
-- remove the passenger
-- whose subscriber doesn't exists any more
--========================================
delete from public.passenger
where sc_id in ( select distinct sc_id from public.passenger
except
select sc_id from public.subscriber
);
--========================================
-- remove the subs_gateway
-- whose subscriber doesn't exists any more
--========================================
delete from public.subs_gateway
where gateway_id in ( select distinct gateway_id from public.subs_gateway
except
select "ID" from public.gateways
)
or radio_gw_id in ( select distinct radio_gw_id from public.subs_gateway
except
select "ID" from public.radio_gw
)
or sc_id in ( select distinct sc_id from public.subs_gateway
except
select sc_id from public.subscriber
);
--========================================
-- remove the speedalarm records
-- whose subscriber doesn't exists any more
--========================================
delete from public.speedalarm
where sc_id in ( select distinct sc_id from public.speedalarm
except
select sc_id from public.subscriber
)
or user_id in ( select distinct user_id from public.speedalarm
except
select userid from public.users
);
--========================================
-- remove the soundcard records
-- whose user doesn't exists any more
--========================================
delete from public.soundcards
where user_id in ( select distinct user_id from public.soundcards
except
select userid from public.users
);
--========================================
-- remove the emergdi records
-- whose subscriber doesn't exists any more
--========================================
delete from public.emergdi
where sc_id in ( select distinct sc_id from public.emergdi
except
select sc_id from public.subscriber
);
--========================================
-- remove the sms records
-- whose subscriber doesn't exists any more
--========================================
delete from public.sms
where sc_id_sour in ( select distinct sc_id_sour from public.sms
except
select sc_id from public.subscriber
)
or sc_id_dest in ( select distinct sc_id_dest from public.sms
except
select sc_id from public.subscriber
);
--========================================
-- remove the messages records
-- whose subscriber doesn't exists any more
--========================================
delete from public.messages
where sc_id in ( select distinct sc_id from public.messages
except
select sc_id from public.subscriber
);
--========================================
-- remove the geozoneinout records
-- whose subscriber doesn't exists any more
--========================================
delete from public.geozoneinout
where sc_id in ( select distinct sc_id from public.geozoneinout
except
select sc_id from public.subscriber
);
--========================================
-- remove the subscriber_history records
-- whose subscriber/ vehicle doesn't exists any more
--========================================
delete from public.subscriber_history
where sc_id in ( select distinct sc_id from public.subscriber_history
except
select sc_id from public.subscriber
)
or veh_id in ( select distinct veh_id from public.subscriber_history
except
select id from public.vehicle
);
--========================================
-- remove the user_radio records
-- whose user_id / radio_id doesn't exists any more
--========================================
delete from public.user_radio
where user_id in ( select distinct user_id from public.user_radio
except
select userid from public.users
)
or radio_id in ( select distinct radio_id from public.user_radio
except
select "ID" from public.radio_gw
);
--========================================
-- remove the user_group records
-- whose user doesn't exists any more
--========================================
delete from public.users_group
where userid in ( select distinct userid from public.users_group
except
select userid from public.users
)
or grp_id in ( select distinct grp_id from public.users_group
except
select id from public.groups
);
--========================================
-- remove the vehicle_group
-- whose group_id doesn't exists any more
--========================================
delete from public.vehicle_group
where grp_ip in ( select distinct grp_ip from public.vehicle_group
except
select id from groups
)
or sc_id in ( select distinct sc_id from public.vehicle_group
except
select sc_id from public.subscriber
);
--========================================
-- remove the vehicle_user
-- whose user_id doesn't exists any more
--========================================
delete from public.vehicle_user
where user_id in ( select distinct user_id from public.vehicle_user
except
select userid from users
)
or veh_id in ( select distinct veh_id from public.vehicle_user
except
select id from public.vehicle
);
--========================================
-- remove the zonepoints records
-- whose zone_id doesn't exists any more
--========================================
delete from public.zonepoints
where zone_id in ( select distinct zone_id from public.zonepoints
except
select idx from public.zonename
);
--========================================
-- remove sc_id=0 from all records
--========================================
delete from public.subscriber_stun
where sc_id = 0;
delete from public.subscriber_alarm
where sc_id = 0;
delete from public.emergdi
where sc_id = 0;
delete from public.messages
where sc_id = 0;
delete from public.subs_gateway
where sc_id = 0;
delete from public.subscriber
where sc_id = 0;
END;
$$