278 lines
8.2 KiB
MySQL
278 lines
8.2 KiB
MySQL
|
|
|||
|
|
-- 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;
|
|||
|
|
$$
|
|||
|
|
|