user_proximity.sql

-- 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 ;