mirror of
https://github.com/librenms/librenms.git
synced 2024-10-07 16:52:45 +00:00
3e35ee0e7d
- Fix location so it is a regular database relation (this allows multiple devices to be accurately linked to one location and saves api calls) - Parse coordinates from the location more consistently - Add settings to webui - ~~Used [PHP Geocoder](http://geocoder-php.org/), which has lots of backends and is well tested. (also includes reverse and geoip)~~ - Google Maps, Bing, Mapquest, and OpenStreetMap supported initially. - Default to OpenStreetMap, which doesn't require a key. They will liberally hand out bans if you exceed 1 query per second though. - All other Geocoding APIs require an API key. (Google requires a credit card on file, but seems to be the most accurate) - Update all (I think) sql queries to handle the new structure - Remove final vestiges of override_sysLocation as a device attribute - Update existing device groups and rules in DB - Tested all APIs with good/bad location, no/bad/good key, and no connection. - Cannot fix advanced queries that use location This blocks #8868 DO NOT DELETE THIS TEXT #### Please note > Please read this information carefully. You can run `./scripts/pre-commit.php` to check your code before submitting. - [x] Have you followed our [code guidelines?](http://docs.librenms.org/Developing/Code-Guidelines/) #### Testers If you would like to test this pull request then please run: `./scripts/github-apply <pr_id>`, i.e `./scripts/github-apply 5926` After you are done testing, you can remove the changes with `./scripts/github-remove`. If there are schema changes, you can ask on discord how to revert.
18 lines
1.4 KiB
SQL
18 lines
1.4 KiB
SQL
ALTER TABLE devices ADD location_id int NULL AFTER location;
|
|
ALTER TABLE locations MODIFY lat double(10,6);
|
|
ALTER TABLE locations MODIFY lng double(10,6);
|
|
ALTER TABLE locations MODIFY location varchar(255) NOT NULL;
|
|
|
|
INSERT INTO locations (location, timestamp) SELECT devices.location, NOW() FROM devices WHERE devices.location IS NOT NULL AND NOT EXISTS (SELECT location FROM locations WHERE location = devices.location);
|
|
DELETE t1 FROM locations t1 INNER JOIN locations t2 WHERE t1.id < t2.id AND t1.location = t2.location;
|
|
CREATE UNIQUE INDEX locations_location_uindex ON locations (location);
|
|
|
|
UPDATE devices INNER JOIN locations ON devices.location = locations.location SET devices.location_id = locations.id;
|
|
ALTER TABLE devices DROP location;
|
|
|
|
UPDATE alert_rules SET builder=REPLACE(builder, 'devices.location', 'locations.location');
|
|
UPDATE device_groups SET pattern=REPLACE(pattern, 'devices.location', 'locations.location');
|
|
|
|
INSERT INTO config (config_name,config_value,config_default,config_descr,config_group,config_group_order,config_sub_group,config_sub_group_order,config_hidden,config_disabled) values ('geoloc.engine','','','Geocoding Engine','external',0,'location',0,'0','0');
|
|
INSERT INTO config (config_name,config_value,config_default,config_descr,config_group,config_group_order,config_sub_group,config_sub_group_order,config_hidden,config_disabled) values ('geoloc.api_key','','','Geocoding API Key (Required to function)','external',0,'location',0,'0','0');
|