public
Created

  • Download Gist
user_proximity.sql
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66
-- log latitudes and longitudes of users using the mobile API
 
DELIMITER $$
 
DROP PROCEDURE IF EXISTS temp_proc$$
 
CREATE PROCEDURE temp_proc ()
BEGIN
IF 1 = (SELECT COUNT(*) FROM information_schema.tables WHERE table_name = 'user_proximity' AND table_schema = 'fsm') THEN
IF 0 = (SELECT COUNT(*) FROM information_schema.columns WHERE table_name = 'user_proximity' AND table_schema = 'fsm' AND column_name = 'api_method') THEN
CREATE TABLE user_proximity_saved AS SELECT id, user_id, latitude, longitude, radius, NULL, timestamp FROM user_proximity;
ELSE
CREATE TABLE user_proximity_saved AS SELECT id, user_id, latitude, longitude, radius, api_method, timestamp FROM user_proximity;
END IF;
ELSE
CREATE TABLE user_proximity_saved AS SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM dual WHERE 1=0;
END IF;
END$$
 
CALL temp_proc()$$
 
DROP PROCEDURE IF EXISTS temp_proc$$
 
DELIMITER ;
 
DROP TABLE IF EXISTS user_proximity;
 
CREATE TABLE user_proximity (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
user_id SMALLINT(6) NOT NULL,
latitude NUMERIC(7,4) NOT NULL,
longitude NUMERIC(7,4) NOT NULL,
radius DOUBLE PRECISION NULL,
api_method VARCHAR(100) NULL,
timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES fsm_user (id) ON UPDATE CASCADE
);
 
INSERT INTO user_proximity SELECT * FROM user_proximity_saved;
 
DROP TABLE IF EXISTS user_proximity_saved;
 
DROP TRIGGER IF EXISTS check_user_proximity;
 
DELIMITER $$
 
CREATE TRIGGER check_user_proximity BEFORE INSERT ON user_proximity FOR EACH ROW
BEGIN
IF new.latitude < -90 OR new.latitude > 90 THEN
CALL fail('latitude must be between -90 and 90');
END IF;
 
IF new.longitude < -180 OR new.longitude > 180 THEN
CALL fail('longitude must be between -180 and 180');
END IF;
 
IF new.radius < 0 OR new.radius = 0 THEN
CALL fail('radius must be > 0 or NULL');
END IF;
 
IF new.api_method IS NOT NULL AND (LENGTH(new.api_method) = 0 OR new.api_method REGEXP '[^a-zA-Z0-9_]') THEN
CALL fail('invalid api_method, must be identifier or NULL');
END IF;
END$$
 
DELIMITER ;

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.