SafeDispatch/UpdateHelper/CreateMissingIndexes.sql

333 lines
12 KiB
MySQL
Raw Permalink Normal View History

CREATE OR REPLACE FUNCTION public.CreateMissingIndexes()
RETURNS void AS
$BODY$
BEGIN
--================
-- VoiceRecordings
--================
if not exists ( SELECT * FROM pg_indexes WHERE tablename = 'VoiceRecordings' and schemaname = 'public' and indexname = 'voicerecordings_time_start_idx' ) then
CREATE INDEX voicerecordings_time_start_idx ON public."VoiceRecordings" USING btree (time_start);
RAISE NOTICE '%', 'Created index : ''voicerecordings_time_start_idx'' !';
else
RAISE NOTICE '%', 'Index ''voicerecordings_time_start_idx'' already exists for table_name = ''VoiceRecordings'' !';
end if;
--================
-- car
--================
if not exists ( SELECT * FROM pg_indexes WHERE tablename = 'car' and schemaname = 'public' and indexname = 'car_name_idx' ) then
CREATE INDEX car_name_idx ON public.car USING btree (name);
RAISE NOTICE '%', 'Created index : ''car_name_idx'' !';
else
RAISE NOTICE '%', 'Index ''car_name_idx'' already exists for table_name = ''car'' !';
end if;
--================
-- geozoneinout
--================
if not exists ( SELECT * FROM pg_indexes WHERE tablename = 'geozoneinout' and schemaname = 'public' and indexname = 'geozoneinout_sc_id_timegmt_idx' ) then
CREATE INDEX geozoneinout_sc_id_timegmt_idx ON public.geozoneinout USING btree (sc_id, timegmt);
RAISE NOTICE '%', 'Created index : ''geozoneinout_sc_id_timegmt_idx'' !';
else
RAISE NOTICE '%', 'Index ''geozoneinout_sc_id_timegmt_idx'' already exists for table_name = ''geozoneinout'' !';
end if;
--================
-- groups
--================
if not exists ( SELECT * FROM pg_indexes WHERE tablename = 'groups' and schemaname = 'public' and indexname = 'groups_name_idx' ) then
CREATE INDEX groups_name_idx ON public.groups USING btree (name);
RAISE NOTICE '%', 'Created index : ''groups_name_idx'' !';
else
RAISE NOTICE '%', 'Index ''groups_name_idx'' already exists for table_name = ''groups'' !';
end if;
--================
-- passenger
--================
if not exists ( SELECT * FROM pg_indexes WHERE tablename = 'passenger' and schemaname = 'public' and indexname = 'passenger_name_idx' ) then
CREATE INDEX passenger_name_idx ON public.passenger USING btree (name);
RAISE NOTICE '%', 'Created index : ''passenger_name_idx'' !';
else
RAISE NOTICE '%', 'Index ''passenger_name_idx'' already exists for table_name = ''passenger'' !';
end if;
--================
-- placetype
--================
if not exists ( SELECT * FROM pg_indexes WHERE tablename = 'placetype' and schemaname = 'public' and indexname = 'placetype_type_name_idx' ) then
CREATE INDEX placetype_type_name_idx ON public.placetype USING btree (type_name);
RAISE NOTICE '%', 'Created index : ''placetype_type_name_idx'' !';
else
RAISE NOTICE '%', 'Index ''placetype_type_name_idx'' already exists for table_name = ''placetype'' !';
end if;
--================
-- radio_gw
--================
if not exists ( SELECT * FROM pg_indexes WHERE tablename = 'radio_gw' and schemaname = 'public' and indexname = 'radio_gw_name_idx' ) then
CREATE INDEX radio_gw_name_idx ON public.radio_gw USING btree (name);
RAISE NOTICE '%', 'Created index : ''radio_gw_name_idx'' !';
else
RAISE NOTICE '%', 'Index ''radio_gw_name_idx'' already exists for table_name = ''radio_gw'' !';
end if;
if not exists ( SELECT * FROM pg_indexes WHERE tablename = 'radio_gw' and schemaname = 'public' and indexname = 'radio_gw_type_idx' ) then
CREATE INDEX radio_gw_type_idx ON public.radio_gw USING btree (gw_type);
RAISE NOTICE '%', 'Created index : ''radio_gw_type_idx'' !';
else
RAISE NOTICE '%', 'Index ''radio_gw_type_idx'' already exists for table_name = ''radio_gw'' !';
end if;
--================
-- sms
--================
if not exists ( SELECT * FROM pg_indexes WHERE tablename = 'sms' and schemaname = 'public' and indexname = 'sms_timegmt_idx' ) then
CREATE INDEX sms_timegmt_idx ON public.sms USING btree (timegmt);
RAISE NOTICE '%', 'Created index : ''sms_timegmt_idx'' !';
else
RAISE NOTICE '%', 'Index ''sms_timegmt_idx'' already exists for table_name = ''sms'' !';
end if;
--================
-- subscriber
--================
if not exists ( SELECT * FROM pg_indexes WHERE tablename = 'subscriber' and schemaname = 'public' and indexname = 'subscriber_imei_idx' ) then
CREATE INDEX subscriber_imei_idx ON public.subscriber USING btree (imei);
RAISE NOTICE '%', 'Created index : ''subscriber_imei_idx'' !';
else
RAISE NOTICE '%', 'Index ''subscriber_imei_idx'' already exists for table_name = ''subscriber'' !';
end if;
--================
-- subscriber_alarm
--================
if not exists ( SELECT * FROM pg_indexes WHERE tablename = 'subscriber_alarm' and schemaname = 'public' and indexname = 'subscriber_alarm_sc_id_idx' ) then
CREATE INDEX subscriber_alarm_sc_id_idx ON public.subscriber_alarm USING btree (sc_id);
RAISE NOTICE '%', 'Created index : ''subscriber_alarm_sc_id_idx'' !';
else
RAISE NOTICE '%', 'Index ''subscriber_alarm_sc_id_idx'' already exists for table_name = ''subscriber_alarm'' !';
end if;
--================
-- subscriber_stun
--================
if not exists ( SELECT * FROM pg_indexes WHERE tablename = 'subscriber_stun' and schemaname = 'public' and indexname = 'subscriber_stun_sc_id_idx' ) then
CREATE INDEX subscriber_stun_sc_id_idx ON public.subscriber_stun USING btree (sc_id);
RAISE NOTICE '%', 'Created index : ''subscriber_stun_sc_id_idx'' !';
else
RAISE NOTICE '%', 'Index ''subscriber_stun_sc_id_idx'' already exists for table_name = ''subscriber_stun'' !';
end if;
--================
-- telemetry
--================
if not exists ( SELECT * FROM pg_indexes WHERE tablename = 'telemetry' and schemaname = 'public' and indexname = 'telemetry_alarm_idx' ) then
CREATE INDEX telemetry_alarm_idx ON public.telemetry USING btree (alarm);
RAISE NOTICE '%', 'Created index : ''telemetry_alarm_idx'' !';
else
RAISE NOTICE '%', 'Index ''telemetry_alarm_idx'' already exists for table_name = ''telemetry'' !';
end if;
if not exists ( SELECT * FROM pg_indexes WHERE tablename = 'telemetry' and schemaname = 'public' and indexname = 'telemetry_name_idx' ) then
CREATE INDEX telemetry_name_idx ON public.telemetry USING btree (name);
RAISE NOTICE '%', 'Created index : ''telemetry_name_idx'' !';
else
RAISE NOTICE '%', 'Index ''telemetry_name_idx'' already exists for table_name = ''telemetry'' !';
end if;
if not exists ( SELECT * FROM pg_indexes WHERE tablename = 'telemetry' and schemaname = 'public' and indexname = 'telemetry_sc_id_idx' ) then
CREATE INDEX telemetry_sc_id_idx ON public.telemetry USING btree (sc_id);
RAISE NOTICE '%', 'Created index : ''telemetry_sc_id_idx'' !';
else
RAISE NOTICE '%', 'Index ''telemetry_sc_id_idx'' already exists for table_name = ''telemetry'' !';
end if;
if not exists ( SELECT * FROM pg_indexes WHERE tablename = 'telemetry' and schemaname = 'public' and indexname = 'telemetry_sc_id__digital_nr_idx' ) then
CREATE INDEX telemetry_sc_id__digital_nr_idx ON public.telemetry USING btree (sc_id, digital_nr);
RAISE NOTICE '%', 'Created index : ''telemetry_sc_id__digital_nr_idx'' !';
else
RAISE NOTICE '%', 'Index ''telemetry_sc_id__digital_nr_idx'' already exists for table_name = ''telemetry'' !';
end if;
--====================
-- telemetry history
--====================
if not exists ( SELECT * FROM pg_indexes WHERE tablename = 'telemetry_history' and schemaname = 'public' and indexname = 'telemetry_history_sc_id_idx' ) then
CREATE INDEX telemetry_history_sc_id_idx ON public.telemetry_history USING btree (sc_id);
RAISE NOTICE '%', 'Created index : ''telemetry_history_sc_id_idx'' !';
else
RAISE NOTICE '%', 'Index ''telemetry_history_sc_id_idx'' already exists for table_name = ''telemetry_history'' !';
end if;
--================
-- user_radio
--================
if not exists ( SELECT * FROM pg_indexes WHERE tablename = 'user_radio' and schemaname = 'public' and indexname = 'user_radio_userid_idx' ) then
CREATE INDEX user_radio_userid_idx ON public.user_radio USING btree (user_id);
RAISE NOTICE '%', 'Created index : ''user_radio_userid_idx'' !';
else
RAISE NOTICE '%', 'Index ''user_radio_userid_idx'' already exists for table_name = ''user_radio'' !';
end if;
if not exists ( SELECT * FROM pg_indexes WHERE tablename = 'user_radio' and schemaname = 'public' and indexname = 'user_radio_radioid_idx' ) then
CREATE INDEX user_radio_radioid_idx ON public.user_radio USING btree (radio_id);
RAISE NOTICE '%', 'Created index : ''user_radio_radioid_idx'' !';
else
RAISE NOTICE '%', 'Index ''user_radio_radioid_idx'' already exists for table_name = ''user_radio'' !';
end if;
--================
-- users
--================
if not exists ( SELECT * FROM pg_indexes WHERE tablename = 'users' and schemaname = 'public' and indexname = 'user_login_idx' ) then
CREATE INDEX user_login_idx ON public.users USING btree (login);
RAISE NOTICE '%', 'Created index : ''user_login_idx'' !';
else
RAISE NOTICE '%', 'Index ''user_login_idx'' already exists for table_name = ''users'' !';
end if;
if not exists ( SELECT * FROM pg_indexes WHERE tablename = 'users' and schemaname = 'public' and indexname = 'user_usertype_idx' ) then
CREATE INDEX user_usertype_idx ON public.users USING btree (user_type);
RAISE NOTICE '%', 'Created index : ''user_usertype_idx'' !';
else
RAISE NOTICE '%', 'Index ''user_usertype_idx'' already exists for table_name = ''users'' !';
end if;
--================
-- userSettings
--================
if not exists ( SELECT * FROM pg_indexes WHERE tablename = 'userSettings' and schemaname = 'public' and indexname = 'usersettings_userid_idx' ) then
CREATE INDEX usersettings_userid_idx ON public."userSettings" USING btree (user_id);
RAISE NOTICE '%', 'Created index : ''usersettings_userid_idx'' !';
else
RAISE NOTICE '%', 'Index ''usersettings_userid_idx'' already exists for table_name = ''userSettings'' !';
end if;
if not exists ( SELECT * FROM pg_indexes WHERE tablename = 'userSettings' and schemaname = 'public' and indexname = 'usersettings_key_idx' ) then
CREATE INDEX usersettings_key_idx ON public."userSettings" USING btree (key);
RAISE NOTICE '%', 'Created index : ''usersettings_key_idx'' !';
else
RAISE NOTICE '%', 'Index ''usersettings_key_idx'' already exists for table_name = ''userSettings'' !';
end if;
if not exists ( SELECT * FROM pg_indexes WHERE tablename = 'userSettings' and schemaname = 'public' and indexname = 'usersettings_userid_key_idx' ) then
CREATE INDEX usersettings_userid_key_idx ON public."userSettings" USING btree (user_id, key);
RAISE NOTICE '%', 'Created index : ''usersettings_userid_key_idx'' !';
else
RAISE NOTICE '%', 'Index ''usersettings_userid_key_idx'' already exists for table_name = ''userSettings'' !';
end if;
--================
-- vehicle_group
--================
if not exists ( SELECT * FROM pg_indexes WHERE tablename = 'vehicle_group' and schemaname = 'public' and indexname = 'vehicle_group_sc_id_grp_ip_idx' ) then
CREATE INDEX vehicle_group_sc_id_grp_ip_idx ON public.vehicle_group USING btree (sc_id, grp_ip);
RAISE NOTICE '%', 'Created index : ''vehicle_group_sc_id_grp_ip_idx'' !';
else
RAISE NOTICE '%', 'Index ''vehicle_group_sc_id_grp_ip_idx'' already exists for table_name = ''vehicle_group'' !';
end if;
--================
-- zonepoints
--================
if not exists ( SELECT * FROM pg_indexes WHERE tablename = 'zonepoints' and schemaname = 'public' and indexname = 'zonepoints_zoneid_idx' ) then
CREATE INDEX zonepoints_zoneid_idx ON public.zonepoints USING btree (zone_id);
RAISE NOTICE '%', 'Created index : ''zonepoints_zoneid_idx'' !';
else
RAISE NOTICE '%', 'Index ''zonepoints_zoneid_idx'' already exists for table_name = ''zonepoints'' !';
end if;
END;
$BODY$
LANGUAGE plpgsql VOLATILE