452 lines
39 KiB
SQL
452 lines
39 KiB
SQL
---
|
|
-- Below is v4 to v5
|
|
---
|
|
CREATE TABLE public.user_radio (radio_id integer NOT NULL DEFAULT 0,user_id integer NOT NULL DEFAULT 0) WITH (OIDS=FALSE);
|
|
ALTER TABLE public.user_radio OWNER TO postgres;
|
|
CREATE TABLE public.reports(startdate integer NOT NULL DEFAULT 0, stopdate integer NOT NULL DEFAULT 0, unit_ids character varying(4096), geofence_id integer NOT NULL DEFAULT 0, type_stat integer NOT NULL DEFAULT 0, report_id integer NOT NULL DEFAULT 0, email character varying(255),idletime integer, idx serial NOT NULL) WITH (OIDS = FALSE);
|
|
ALTER TABLE public.reports OWNER TO postgres;
|
|
ALTER TABLE zonename ADD COLUMN useridx integer NOT NULL DEFAULT 0;
|
|
ALTER TABLE place ADD COLUMN useridx integer NOT NULL DEFAULT 0;
|
|
ALTER TABLE zonename ADD COLUMN alarmtype integer NOT NULL DEFAULT 3;
|
|
ALTER TABLE zonename ADD COLUMN sentmsg boolean NOT NULL DEFAULT false;
|
|
ALTER TABLE zonename ADD COLUMN msgbody character varying(255) NOT NULL DEFAULT '';
|
|
ALTER TABLE zonename ADD COLUMN unitids character varying(255) NOT NULL DEFAULT '';
|
|
ALTER TABLE zonename ADD COLUMN sentemail boolean NOT NULL DEFAULT false;
|
|
ALTER TABLE zonename ADD COLUMN email character varying(255) NOT NULL DEFAULT '';
|
|
ALTER TABLE zonename ADD COLUMN subj character varying(255) NOT NULL DEFAULT '';
|
|
ALTER TABLE zonename ADD COLUMN body character varying(255) NOT NULL DEFAULT '';
|
|
ALTER TABLE zonename ADD COLUMN imeilist character varying(255) NOT NULL DEFAULT '';
|
|
---
|
|
-- Below is v5 to v6
|
|
---
|
|
ALTER TABLE \"VoiceRecordings\" ADD COLUMN calltype integer NOT NULL DEFAULT 0;
|
|
ALTER TABLE \"VoiceRecordings\" ADD COLUMN group_id integer NOT NULL DEFAULT 0;
|
|
ALTER TABLE zonename ADD COLUMN sentmsg2 boolean NOT NULL DEFAULT false;
|
|
ALTER TABLE zonename ADD COLUMN msgbody2 character varying(255) NOT NULL DEFAULT '';
|
|
ALTER TABLE speedalarm ADD COLUMN lat double precision NOT NULL DEFAULT 0;
|
|
ALTER TABLE speedalarm ADD COLUMN lng double precision NOT NULL DEFAULT 0;
|
|
ALTER TABLE telemetry_history ADD COLUMN lat double precision NOT NULL DEFAULT 0;
|
|
ALTER TABLE telemetry_history ADD COLUMN lng double precision NOT NULL DEFAULT 0;
|
|
ALTER TABLE emergalarm ADD COLUMN lat double precision NOT NULL DEFAULT 0;
|
|
ALTER TABLE emergalarm ADD COLUMN lng double precision NOT NULL DEFAULT 0;
|
|
ALTER TABLE reports ADD COLUMN latlng boolean NOT NULL DEFAULT false;
|
|
UPDATE emergalarm SET address='no address' WHERE address = '';
|
|
UPDATE telemetry_history SET address='no address' WHERE address = '';
|
|
UPDATE speedalarm SET address='no address' WHERE address = '';
|
|
ALTER TABLE vehicle ADD COLUMN gps_poor integer NOT NULL DEFAULT 0;
|
|
ALTER TABLE vehicle ADD COLUMN gps_off integer NOT NULL DEFAULT 0;
|
|
ALTER TABLE vehicle ADD COLUMN made_off integer NOT NULL DEFAULT 0;
|
|
UPDATE vehicle SET gps_poor=\"GPS_reporting_interval\"*2, gps_off=\"GPS_reporting_interval\"*4, made_off=\"GPS_reporting_interval\"*10;
|
|
UPDATE lastpos SET status=0;
|
|
---
|
|
-- Below is v6 with for recordings.
|
|
---
|
|
ALTER TABLE gateways ADD COLUMN peer_id integer NOT NULL DEFAULT 0;
|
|
ALTER TABLE radio_gw DROP CONSTRAINT unique_radio_gw;
|
|
ALTER TABLE sms ADD COLUMN lat double precision NOT NULL DEFAULT 0;
|
|
ALTER TABLE sms ADD COLUMN lng double precision NOT NULL DEFAULT 0;
|
|
ALTER TABLE sms ADD COLUMN address character varying(255) NOT NULL DEFAULT 'no address';
|
|
INSERT INTO emailservice(id, \"user\", pass, enable) VALUES (2,'poll', 'poll', 0);
|
|
ALTER TABLE zonename ADD COLUMN speed integer NOT NULL DEFAULT 0;
|
|
ALTER TABLE emailservice ADD COLUMN pop_server text NOT NULL DEFAULT 'pop.gmail.com';
|
|
ALTER TABLE emailservice ADD COLUMN pop_port integer NOT NULL DEFAULT 995;
|
|
ALTER TABLE emailservice ADD COLUMN smtp_server text NOT NULL DEFAULT 'smtp.gmail.com';
|
|
ALTER TABLE emailservice ADD COLUMN smtp_port integer NOT NULL DEFAULT 587;
|
|
ALTER TABLE emailservice ADD COLUMN is_pop_ssl boolean NOT NULL DEFAULT true;
|
|
ALTER TABLE emailservice ADD COLUMN is_smtp_ssl boolean NOT NULL DEFAULT true;
|
|
ALTER TABLE emailservice ADD COLUMN last_email_time integer NOT NULL DEFAULT 0;
|
|
---
|
|
-- Below is v6 to v7
|
|
---
|
|
ALTER TABLE lastpos ADD COLUMN altitude double precision NOT NULL DEFAULT 0;
|
|
DROP TABLE IF EXISTS jobtickets;
|
|
CREATE TABLE jobtickets (ticket_id serial,name text,job_status integer,sc_id integer,comment text,dispatcher text,priority text,end_time_planned integer,creation_time integer,start_time integer,end_time integer,is_expired boolean DEFAULT false NOT NULL,deleted integer,share_with text,seq_id character varying(40),status integer DEFAULT 0 NOT NULL,sched_timegmt integer DEFAULT 0);
|
|
ALTER TABLE public.jobtickets OWNER TO postgres;
|
|
DROP TABLE IF EXISTS jobtickets_log;
|
|
CREATE TABLE jobtickets_log ( id serial, ticket_id integer, modified_date integer,job_status integer, sc_id integer, comment text) WITH ( OIDS=FALSE);
|
|
ALTER TABLE jobtickets_log OWNER TO postgres;
|
|
DROP TABLE IF EXISTS jobticketstatusesset;
|
|
CREATE TABLE jobticketstatusesset ( status_id serial, status character varying(45), color character varying(45), sound_on integer, sound_file character varying(250), is_first_state integer, is_last_state integer, CONSTRAINT jobticketstatusesset_pkey PRIMARY KEY (status_id))WITH ( OIDS=FALSE);
|
|
ALTER TABLE jobticketstatusesset OWNER TO postgres;
|
|
DROP TABLE IF EXISTS definejobticket;
|
|
CREATE TABLE definejobticket ( name character varying(200) NOT NULL, text character varying(200) NOT NULL, comment character varying(200), priority character varying(200), expire_date integer, idx serial NOT NULL, CONSTRAINT definejobticket_pkey PRIMARY KEY (idx))WITH ( OIDS=FALSE);
|
|
ALTER TABLE definejobticket OWNER TO postgres;
|
|
ALTER TABLE registration ADD COLUMN has_ticketing boolean NOT NULL DEFAULT false;
|
|
DELETE FROM jobticketstatusesset;
|
|
INSERT INTO jobticketstatusesset (status_id, status, color, sound_on, sound_file, is_first_state, is_last_state) VALUES (1, 'Assigned', '3D8584', 0, '', 1, 0);
|
|
INSERT INTO jobticketstatusesset (status_id, status, color, sound_on, sound_file, is_first_state, is_last_state) VALUES (2, 'Accepted', '3D7171', 0, '', 0, 0);
|
|
INSERT INTO jobticketstatusesset (status_id, status, color, sound_on, sound_file, is_first_state, is_last_state) VALUES (3, 'In progress', '3D5B85', 0, '', 0, 0);
|
|
INSERT INTO jobticketstatusesset (status_id, status, color, sound_on, sound_file, is_first_state, is_last_state) VALUES (4, 'Completed', '5E853D', 0, '', 0, 1);
|
|
INSERT INTO jobticketstatusesset (status_id, status, color, sound_on, sound_file, is_first_state, is_last_state) VALUES (5, 'Rejected', 'A93D47', 0, '', 0, 0);
|
|
INSERT INTO jobticketstatusesset (status_id, status, color, sound_on, sound_file, is_first_state, is_last_state) VALUES (6, 'Overdue', 'B29F3D', 0, '', 0, 0);
|
|
ALTER TABLE definejobticket ADD COLUMN handled_by text;
|
|
ALTER TABLE definejobticket ADD COLUMN schedule_date integer;
|
|
ALTER TABLE definejobticket ADD COLUMN share_with text;
|
|
DROP TABLE IF EXISTS sequences;
|
|
CREATE TABLE sequences( name character varying(32) NOT NULL, value integer NOT NULL DEFAULT 0, idx serial NOT NULL, CONSTRAINT sequences_pkey PRIMARY KEY (idx))WITH ( OIDS=FALSE);
|
|
ALTER TABLE sequences OWNER TO postgres;
|
|
INSERT INTO sequences(name, value) VALUES('jobticketing',1);
|
|
DROP TABLE IF EXISTS icon_theme;
|
|
CREATE TABLE icon_theme( id serial NOT NULL, \"name\" text, prefix text) WITH ( OIDS=FALSE);
|
|
ALTER TABLE icon_theme OWNER TO postgres;
|
|
INSERT INTO icon_theme(id, \"name\", prefix)VALUES (1, 'Classic', 'classic'),(2, 'Pin', 'pin');
|
|
DROP TABLE IF EXISTS car;
|
|
CREATE TABLE car(\"name\" character varying(100) NOT NULL, id serial NOT NULL, pattern text, displayed_name text, CONSTRAINT car_pkey PRIMARY KEY (id))WITH ( OIDS=FALSE);
|
|
ALTER TABLE car OWNER TO postgres;
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('minibus', 102, 'minibus_01', 'Van 1');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('Minicar', 103, 'minicar_01', 'MiniCar 1');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('people_08', 35, 'people_08', 'People 8');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('Minicar2', 104, 'minicar_02', 'MiniCar 2');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('police', 105, 'police_01', 'Police 1');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('Police1', 106, 'police_02', 'Police 2');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('schoolbus', 107, 'school_bus_01', 'School Bus 1');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('taxi_02', 131, 'taxi_02', 'Taxi 2');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('schoolbus2', 108, 'school_bus_02', 'School Bus 2');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('moto_01', 132, 'moto_01', 'Moto 1');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('moto_02', 133, 'moto_02', 'Moto 2');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('TowTruckYellow', 110, 'tow_truck_01', 'Tow Truck');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('TractorUnitBlack', 111, 'tractor_unit', 'Tractor Unit');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('moto_03', 134, 'moto_03', 'Moto 3');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('moto_04', 135, 'moto_04', 'Moto 4');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('Classic-Car-66', 92, 'classic_car_01', 'Classic Car 1');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('truck0', 112, 'truck_01', 'Truck 1');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('classycar', 93, 'classic_car_02', 'Classic Car 2');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('peopleblue', 1, 'people_01', 'People 1');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('peoplegreen', 2, 'people_02', 'People 2');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('peoplegrey', 3, 'people_03', 'People 3');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('peoplepink', 4, 'people_04', 'People 4');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('peoplepurple', 5, 'people_05', 'People 5');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('peoplered', 6, 'people_06', 'People 6');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('army', 79, 'army_01', 'Army');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('bigcar0', 80, 'car_01', 'Car 1');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('bigcar1', 81, 'car_02', 'Car 2');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('bigcar2', 82, 'car_03', 'Car 3');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('moto_05', 136, 'moto_05', 'Moto 5');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('bicycle_01', 137, 'bicycle_01', 'Bicycle');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('car0', 87, 'car_04', 'Car 4');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('car1', 88, 'car_05', 'Car 5');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('car2', 89, 'car_06', 'Car 6');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('car3', 90, 'car_07', 'Car 7');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('CarGrey', 91, 'car_08', 'Car 8');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('dodge', 94, 'dodge_01', 'Dodge');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('FireEscape', 95, 'firetruck_01', 'FireTruck 1');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('truck1', 113, 'truck_02', 'Truck 2');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('firefighters2', 96, 'firetruck_02', 'Fire Truck 2');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('ambulance_01', 36, 'ambulance_01', 'Ambulance 1');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('firetruck1', 97, 'firetruck_03', 'Fire Truck 3');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('truck2', 114, 'truck_03', 'Truck 3');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('firetruck2', 98, 'firetruck_04', 'Fire Truck 4');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('Jeep', 99, 'jeep_01', 'Jeep');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('CabrioletRed', 86, 'cabriolet_01', 'Carbriolet');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('truck3', 115, 'truck_04', 'Truck 4');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('Longhaul', 100, 'longhaul_01', 'Long Haul');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('truck4', 116, 'truck_05', 'Truck 5');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('LorryGreen', 101, 'lorry_01', 'Lorry');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('ambulance_02', 78, 'ambulance_02', 'Ambulance 2');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('truck5', 117, 'truck_06', 'Truck 6');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('truck6', 118, 'truck_07', 'Truck 7');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('TruckYellow', 119, 'truck_08', 'Truck 8');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('wagon', 120, 'wagon_01', 'Wagon');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('bus', 83, 'tour_bus_01', 'Tour Bus 1');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('bus0', 84, 'bus_01', 'Bus 1');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('bus2', 85, 'bus_02', 'Bus 2');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('number_01', 121, 'number_01', '1');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('number_02', 122, 'number_02', '2');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('number_04', 124, 'number_04', '4');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('number_03', 123, 'number_03', '3');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('number_05', 125, 'number_05', '5');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('number_06', 126, 'number_06', '6');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('number_07', 127, 'number_07', '7');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('number_08', 128, 'number_08', '8');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('number_09', 129, 'number_09', '9');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('number_10', 130, 'number_10', '10');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('taxi', 109, 'taxi_01', 'Taxi 1');
|
|
INSERT INTO car (name, id, pattern, displayed_name) VALUES ('people_07', 34, 'people_07', 'People 7');
|
|
insert into \"userSettings\" (user_id, key, value) select userid, 'reportTime', '0' from users where userid not in (select user_id from \"userSettings\" where key = 'reportTime') order by userid;
|
|
insert into \"userSettings\" (user_id, key, value) select userid, 'iconTheme', 'pin' from users where userid not in (select user_id from \"userSettings\" where key = 'iconTheme') order by userid;
|
|
ALTER TABLE reports ADD COLUMN userid integer;
|
|
UPDATE reports SET userid = (SELECT MAX(userid) FROM users);
|
|
ALTER TABLE reports ADD COLUMN ticket_status character varying(250);
|
|
ALTER TABLE reports ADD COLUMN ticket_date_type character varying(250);
|
|
ALTER TABLE reports ADD COLUMN type text;
|
|
ALTER TABLE reports ADD COLUMN day text;
|
|
ALTER TABLE reports ADD COLUMN is_activated boolean;
|
|
ALTER TABLE sms ADD COLUMN color character varying(7);
|
|
ALTER TABLE sms ALTER COLUMN color SET STORAGE EXTENDED;
|
|
DROP TABLE IF EXISTS placetype;
|
|
CREATE TABLE placetype ( type_name character varying(100) NOT NULL,type_id integer NOT NULL,icon_name character varying(70));
|
|
ALTER TABLE public.placetype OWNER TO postgres;
|
|
CREATE SEQUENCE placetype_type_id_seq START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;
|
|
ALTER TABLE public.placetype_type_id_seq OWNER TO postgres;
|
|
ALTER SEQUENCE placetype_type_id_seq OWNED BY placetype.type_id;
|
|
SELECT pg_catalog.setval('placetype_type_id_seq', 156, true);
|
|
ALTER TABLE place ADD COLUMN icon_size character varying(20);
|
|
ALTER TABLE place ADD COLUMN color character varying(7);
|
|
ALTER TABLE zonename ADD COLUMN fill_color character varying(7);
|
|
ALTER TABLE zonename ADD COLUMN fill_opacity double precision;
|
|
ALTER TABLE zonename ADD COLUMN border_color character varying(7);
|
|
ALTER TABLE zonename ADD COLUMN border_opacity double precision;
|
|
ALTER TABLE zonename ADD COLUMN border_width integer;
|
|
ALTER TABLE placetype ALTER COLUMN type_id SET DEFAULT nextval('placetype_type_id_seq'::regclass);
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('calculator', 45, 'calculator.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('calendar', 46, 'calendar.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('camera', 47, 'camera.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('cancel', 48, 'cancel.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('factory', 4, 'factory.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('airport', 1, 'airport.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('cd', 49, 'cd.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('hospital', 6, 'hospital.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('bank', 2, 'bank.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('school', 15, 'school.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('supermarket', 17, 'supermarket.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('shopping', 16, 'shopping.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('office', 18, 'office.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('post_office', 13, 'post_office.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('restaurant', 14, 'restaurant.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('home', 9, 'home.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('fire_department', 7, 'fire_department.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('repair_shop', 3, 'repair_shop.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('garage', 5, 'garage.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('hotel', 10, 'hotel.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('gas_station', 8, 'gas_station.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('military_base', 11, 'military_base.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('police_station', 12, 'police_station.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('chair', 50, 'chair.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('check', 51, 'check.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('cinema', 52, 'cinema.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('add', 20, 'add.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('coffee', 53, 'coffee.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('alert', 22, 'alert.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('anchor', 23, 'anchor.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('announce', 24, 'announce.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('antivirus', 25, 'antivirus.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('arrow_down', 26, 'arrow_down.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('compass', 54, 'compass.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('arrow_down_left', 27, 'arrow_down_left.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('computer', 55, 'computer.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('arrow_down_right', 28, 'arrow_down_right.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('arrow_left', 29, 'arrow_left.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('construction', 56, 'construction.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('repair_shop_2', 102, 'repair_shop_2.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('rip', 103, 'rip.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('roundabout', 104, 'roundabout.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('ruby', 105, 'ruby.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('save', 106, 'save.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('arrow_right', 30, 'arrow_right.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('arrow_up', 31, 'arrow_up.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('arrow_up_left', 32, 'arrow_up_left.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('arrow_up_right', 33, 'arrow_up_right.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('baggage', 34, 'baggage.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('banned', 35, 'banned.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('science', 107, 'science.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('search', 108, 'search.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('servers', 109, 'servers.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('settings', 110, 'settings.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('shape_circle', 111, 'shape_circle.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('shape_ellipse', 112, 'shape_ellipse.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('shape_hexagon', 113, 'shape_hexagon.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('shape_kite', 114, 'shape_kite.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('shape_parallelogram', 115, 'shape_parallelogram.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('battery', 36, 'battery.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('bicycle', 37, 'bicycle.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('bluetooth', 38, 'bluetooth.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('book', 39, 'book.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('bookmark', 40, 'bookmark.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('brush', 41, 'brush.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('bug', 42, 'bug.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('bussiness_card', 43, 'bussiness_card.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('cake', 44, 'cake.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('credit_card', 57, 'credit_card.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('cross', 58, 'cross.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('database', 59, 'database.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('directions', 60, 'directions.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('directions_2', 61, 'directions_2.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('doctor', 62, 'doctor.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('drill', 63, 'drill.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('elevator', 64, 'elevator.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('facebook', 65, 'facebook.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('fan', 66, 'fan.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('favorite', 67, 'favorite.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('flag', 68, 'flag.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('food', 69, 'food.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('gameboy', 70, 'gameboy.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('shape_pentagon', 116, 'shape_pentagon.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('shape_rhombus', 117, 'shape_rhombus.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('shape_square', 118, 'shape_square.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('shape_trapezoid', 119, 'shape_trapezoid.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('shape_triangle', 120, 'shape_triangle.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('pool_game', 98, 'pool_game.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('alarm', 21, 'alarm.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('games', 71, 'games.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('gift', 72, 'gift.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('glasses', 73, 'glasses.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('grid', 74, 'grid.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('hairdresser', 75, 'hairdresser.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('hammer', 76, 'hammer.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('hardware', 77, 'hardware.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('headphones', 78, 'headphones.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('hedge', 79, 'hedge.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('help', 80, 'help.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('help_2', 81, 'help_2.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('icecream', 82, 'icecream.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('info', 83, 'info.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('key', 84, 'key.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('map', 85, 'map.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('marker', 86, 'marker.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('microphone', 87, 'microphone.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('moon', 88, 'moon.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('newspaper', 89, 'newspaper.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('padlock_closed', 90, 'padlock_closed.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('padlock_open', 91, 'padlock_open.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('paintroller', 92, 'paintroller.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('park_bench', 93, 'park_bench.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('phone', 94, 'phone.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('pill', 95, 'pill.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('pinetree', 96, 'pinetree.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('pollution', 97, 'pollution.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('print', 99, 'print.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('refreshment', 100, 'refreshment.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('remote_control', 101, 'remote_control.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('stamp', 121, 'stamp.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('star', 122, 'star.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('stop', 123, 'stop.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('stop_2', 124, 'stop_2.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('stopwatch', 125, 'stopwatch.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('store', 126, 'store.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('tag', 127, 'tag.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('tent', 128, 'tent.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('tie', 129, 'tie.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('time', 130, 'time.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('trafficlight', 131, 'trafficlight.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('trafficlight_green', 132, 'trafficlight_green.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('trafficlight_red', 133, 'trafficlight_red.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('trafficlight_yellow', 134, 'trafficlight_yellow.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('trash', 135, 'trash.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('trophy', 136, 'trophy.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('truck', 137, 'truck.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('typewritter', 138, 'typewritter.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('user', 139, 'user.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('users', 140, 'users.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('water', 141, 'water.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('weather_cloud', 142, 'weather_cloud.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('weather_clouds', 144, 'weather_clouds.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('weather_rain', 145, 'weather_rain.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('weather_sun', 146, 'weather_sun.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('weather_snow', 147, 'weather_snow.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('weather_thunder', 148, 'weather_thunder.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('wi_fi', 149, 'wi_fi.png');
|
|
INSERT INTO placetype (type_name, type_id, icon_name) VALUES ('weather_cloud_sun', 143, 'weather_cloud_sun.png');
|
|
ALTER TABLE messages ADD COLUMN altitude double precision;
|
|
---
|
|
-- Below if v7 to LINX
|
|
---
|
|
CREATE TABLE public.newicons (icontype integer NOT NULL DEFAULT 0, id integer NOT NULL DEFAULT 0, imagedata bytea);
|
|
ALTER TABLE groups ADD COLUMN type integer;
|
|
UPDATE groups SET type = 0;
|
|
ALTER TABLE vehicle ADD COLUMN type integer;
|
|
UPDATE vehicle SET type = 0;
|
|
ALTER TABLE vehicle ADD COLUMN active boolean;
|
|
UPDATE vehicle SET active = TRUE;
|
|
ALTER TABLE radio_gw ADD COLUMN name character varying(30);
|
|
UPDATE radio_gw SET name = 'unnamed' || \"ID\";
|
|
ALTER TABLE radio_gw ADD COLUMN gw_type integer;
|
|
UPDATE radio_gw SET gw_type = 0;
|
|
ALTER TABLE groups ADD COLUMN r_gw_id integer;
|
|
UPDATE groups SET r_gw_id = ( CASE WHEN ((select count(*) from radio_gw ) = 0) THEN 0 ELSE (select min(\"ID\") from radio_gw) END);
|
|
update subs_gateway set gateway_id = (CASE WHEN ((select count(*) from radio_gw where \"ID\" = 1 and \"GW_ID\" = 1) = 0) THEN (select min(\"GW_ID\") from radio_gw) ELSE 1 END ), radio_gw_id = (CASE WHEN ((select count(*) from radio_gw where \"ID\" = 1 and \"GW_ID\" = 1) = 0) THEN (select min(\"ID\") from radio_gw) ELSE 1 END) where gateway_id = 1 and radio_gw_id = 1;
|
|
CREATE TABLE subscriber_mobile( sc_id serial NOT NULL, phone_imei character varying(20), first_name character varying(50), last_name character varying(50), company character varying(30), os character varying(20), phone_type character varying(20), sip_pswd character varying(30), CONSTRAINT subscriber_mobile_pkey PRIMARY KEY (sc_id)) WITH ( OIDS=FALSE);
|
|
ALTER TABLE subscriber_mobile OWNER TO postgres;
|
|
CREATE TABLE mobile_contacts(idx serial NOT NULL, sc_id integer NOT NULL, contact_id integer NOT NULL, contact_type character varying(30), contact_type_id integer) WITH ( OIDS=FALSE);
|
|
ALTER TABLE mobile_contacts OWNER TO postgres;
|
|
ALTER TABLE sms ADD COLUMN user_id integer;
|
|
ALTER TABLE sms ALTER COLUMN user_id SET STORAGE PLAIN;
|
|
ALTER TABLE users ADD COLUMN backup_user_id integer default 0;
|
|
ALTER TABLE subscriber_mobile ADD COLUMN has_gps boolean DEFAULT false;
|
|
ALTER TABLE subscriber_mobile ADD COLUMN has_voice boolean DEFAULT false;
|
|
ALTER TABLE subscriber_mobile ADD COLUMN has_text boolean DEFAULT false;
|
|
ALTER TABLE subscriber_mobile ADD COLUMN has_ticketing boolean DEFAULT false;
|
|
ALTER TABLE subscriber_mobile ADD COLUMN email text;
|
|
ALTER TABLE subscriber_mobile ADD COLUMN login text;
|
|
ALTER TABLE users ADD COLUMN sip_id integer;
|
|
ALTER TABLE users ALTER COLUMN sip_id SET STORAGE PLAIN;
|
|
ALTER TABLE subscriber_mobile ADD COLUMN lone_worker boolean DEFAULT false;
|
|
ALTER TABLE subscriber_mobile ADD COLUMN lone_worker_interval integer DEFAULT 0;
|
|
ALTER TABLE emergalarm ADD COLUMN user_id integer;
|
|
ALTER TABLE emergalarm ADD COLUMN timegmt_ack integer;
|
|
ALTER TABLE geozoneinout ADD COLUMN user_id integer;
|
|
ALTER TABLE geozoneinout ADD COLUMN timegmt_ack integer;
|
|
ALTER TABLE speedalarm ADD COLUMN user_id integer;
|
|
ALTER TABLE speedalarm ADD COLUMN timegmt_ack integer;
|
|
ALTER TABLE telemetry_history ADD COLUMN user_id integer;
|
|
ALTER TABLE telemetry_history ADD COLUMN timegmt_ack integer;
|
|
ALTER TABLE emergalarm ADD COLUMN emergency_type integer;
|
|
create table call_patch(idx serial not null, type integer, incoming text, outgoing text, userid integer);
|
|
CREATE INDEX msg_sc_id_idx ON messages USING btree (sc_id);
|
|
CREATE INDEX secv_idx ON messages USING btree (scevtime);
|
|
CREATE INDEX speed_idx ON messages USING btree (speed);
|
|
CREATE INDEX timegmt_idx ON messages USING btree (timegmt);
|
|
CREATE TABLE users_group(idx serial not null, userid integer not null, grp_id integer not null);
|
|
CREATE TABLE sip_manager(sip_id serial not null primary key, id integer not null, type integer not null);
|
|
CREATE UNIQUE INDEX unique_idx ON sip_manager (id, type);
|
|
ALTER SEQUENCE sip_manager_sip_id_seq RESTART WITH 10;
|
|
insert into sip_manager(id, type) select userid, 0 from users where user_type <> 1;
|
|
insert into sip_manager(id, type) select id, 1 from groups where type <> 0;
|
|
insert into sip_manager(id, type) select sc_id, 2 from subscriber;
|
|
insert into sip_manager(id, type) select \"ID\", 4 from radio_gw;
|
|
ALTER TABLE users DROP COLUMN sip_id;
|
|
insert into \"userSettings\" (user_id, key, value) SELECT 0,'lanLinxServer','' where not exists (select * from \"userSettings\" where user_id = 0 and key = 'lanLinxServer');
|
|
insert into \"userSettings\" (user_id, key, value) SELECT 0,'natLinxServer','' where not exists (select * from \"userSettings\" where user_id = 0 and key = 'natLinxServer');
|
|
insert into \"userSettings\" (user_id, key, value) SELECT 0,'lanSipPort','' where not exists (select * from \"userSettings\" where user_id = 0 and key = 'lanSipPort');
|
|
insert into \"userSettings\" (user_id, key, value) SELECT 0,'natSipPort','' where not exists (select * from \"userSettings\" where user_id = 0 and key = 'natSipPort');
|
|
insert into \"userSettings\" (user_id, key, value) SELECT 0,'lanSigPort','41414' where not exists (select * from \"userSettings\" where user_id = 0 and key = 'lanSigPort');
|
|
insert into \"userSettings\" (user_id, key, value) SELECT 0,'natSigPort','' where not exists (select * from \"userSettings\" where user_id = 0 and key = 'natSigPort');
|
|
insert into \"userSettings\" (user_id, key, value) SELECT 0,'rtpStart','' where not exists (select * from \"userSettings\" where user_id = 0 and key = 'rtpStart');
|
|
insert into \"userSettings\" (user_id, key, value) SELECT 0,'rtpEnd','' where not exists (select * from \"userSettings\" where user_id = 0 and key = 'rtpEnd');
|
|
INSERT INTO car(\"name\", id, pattern, displayed_name) VALUES ('linx_01', 138, 'linx_01', 'Linx 1'), ('linx_02', 139, 'linx_02', 'Linx 2');
|
|
ALTER TABLE subscriber_mobile ADD COLUMN has_man_down boolean DEFAULT false;
|
|
ALTER TABLE gateways ADD COLUMN active boolean default false;
|
|
UPDATE gateways SET active = true WHERE \"IP\" IN (SELECT ip FROM reg_gateway);
|
|
DROP TABLE reg_gateway;
|
|
ALTER TABLE subscriber_mobile RENAME lone_worker TO has_lone_worker;
|
|
ALTER TABLE subscriber_mobile ADD COLUMN has_emergency boolean DEFAULT true;
|
|
UPDATE subscriber_mobile SET lone_worker_interval = 1 WHERE lone_worker_interval = 0;
|
|
ALTER TABLE subscriber_mobile ALTER COLUMN lone_worker_interval SET DEFAULT 1;
|
|
ALTER TABLE sms ALTER COLUMN mess TYPE text;
|
|
ALTER TABLE alarm ALTER COLUMN mess TYPE text;
|
|
CREATE SEQUENCE radio_mobile_idx_seq START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;
|
|
CREATE TABLE public.radio_mobile(idx integer NOT NULL DEFAULT nextval('radio_mobile_idx_seq'::regclass), radio_grp_id integer NOT NULL, mobile_grp_id integer NOT NULL);
|
|
insert into \"userSettings\" (user_id, key, value) SELECT 0,'sampleRate','8000' where not exists (select * from \"userSettings\" where user_id = 0 and key = 'sampleRate');
|
|
insert into \"userSettings\" (user_id, key, value) SELECT 0,'bufferMilliseconds','32' where not exists (select * from \"userSettings\" where user_id = 0 and key = 'bufferMilliseconds');
|
|
insert into \"userSettings\" (user_id, key, value) SELECT 0,'bitDepth','16' where not exists (select * from \"userSettings\" where user_id = 0 and key = 'bitDepth');
|
|
alter table definesms add column user_id integer NOT NULL DEFAULT 0;
|
|
CREATE TABLE msg(idx serial, message text NOT NULL, dest_id int NOT NULL, seq_id text, sched_timegmt integer DEFAULT 0, email text NOT NULL, CONSTRAINT msg_pkey PRIMARY KEY (idx));
|
|
CREATE TABLE msg_details(idx serial, msg_id int NOT NULL, sip_id int, timegmt integer, lat double precision NOT NULL DEFAULT 0, lng double precision NOT NULL DEFAULT 0, speed double precision NOT NULL DEFAULT 0, altitude double precision NOT NULL DEFAULT 0, address character varying(255) NOT NULL DEFAULT 'no address'::character varying, is_sent boolean DEFAULT false, is_read boolean DEFAULT false, is_deleted boolean DEFAULT false, CONSTRAINT msg_det_pkey PRIMARY KEY (idx));
|
|
ALTER TABLE msg_details ADD CONSTRAINT msg_fk FOREIGN KEY (msg_id) REFERENCES msg (idx) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE;
|
|
ALTER TABLE msg_details ADD CONSTRAINT sip_manager_msg_det_fk FOREIGN KEY (sip_id) REFERENCES sip_manager (sip_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION;
|
|
ALTER TABLE msg ADD CONSTRAINT sip_manager_msg_fk FOREIGN KEY (dest_id) REFERENCES sip_manager (sip_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION;
|
|
CREATE INDEX msg_sip_id_idx ON msg_details USING btree (sip_id);
|
|
CREATE INDEX msg_timegmt_idx ON msg_details USING btree (timegmt);
|
|
ALTER TABLE msg_details ADD COLUMN msg_type integer not null default 0;
|
|
ALTER TABLE messages ADD COLUMN tallysman_log_id integer;
|
|
ALTER TABLE registration ADD COLUMN has_recordings boolean NOT NULL DEFAULT false;
|
|
ALTER TABLE \"userSettings\" alter column key type text;
|
|
|
|
---
|
|
-- Fix long context menu when to many alerts
|
|
---
|
|
|
|
CREATE INDEX idx_geozone_1 ON public.geozoneinout USING btree (sc_id, preview);
|
|
CREATE INDEX idx_emergalarm ON public.emergalarm USING btree (sc_id, preview);
|
|
CREATE INDEX idx_speedalarm ON public.speedalarm USING btree (sc_id, preview);
|
|
CREATE INDEX idx_telemetryalarm ON public.telemetry_history USING btree (sc_id, ack, alarm);
|
|
|
|
---
|
|
-- Callout for TetraSDR
|
|
---
|
|
|
|
ALTER TABLE zonename ADD COLUMN \"callout\" BOOLEAN DEFAULT FALSE;
|
|
ALTER TABLE zonename ADD COLUMN \"callout_severity\" int DEFAULT 3;
|
|
ALTER TABLE place ADD COLUMN \"callout\" BOOLEAN DEFAULT FALSE;
|
|
ALTER TABLE place ADD COLUMN \"callout_severity\" int DEFAULT 3;
|
|
|