Skip to content

@rkitover /user_proximity.sql
Created

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
-- 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 ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.