Migrate-from-m2s

ALTER TABLE location MODIFY lat DECIMAL(10,7) NOT NULL;
ALTER TABLE location MODIFY lon DECIMAL(10,7) NOT NULL;

ALTER TABLE waypoint MODIFY lat DECIMAL(10,7) NOT NULL;
ALTER TABLE waypoint MODIFY lon DECIMAL(10,7) NOT NULL;
ALTER TABLE waypoint MODIFY rad INTEGER NOT NULL;

CREATE INDEX lll ON location (lat, lon);
CREATE INDEX loc_u_d ON location (username, device); -- maybe not if use TID?

ALTER TABLE location ADD tid VARCHAR(2);
UPDATE location SET tid = UCASE(RIGHT(CONVERT(topic USING utf8), 2));
ALTER TABLE location MODIFY tid VARCHAR(2) NOT NULL;
CREATE INDEX loc_tid ON location (tid); -- maybe not if use TID?

ALTER TABLE location ADD t VARCHAR(1);

ALTER TABLE location MODIFY batt DECIMAL(3,1) NOT NULL;
ALTER TABLE location MODIFY acc DECIMAL(6,1) NOT NULL;

ALTER TABLE location DROP username;
ALTER TABLE location DROP device;
ALTER TABLE location DROP weather;
ALTER TABLE location DROP revgeo;
ALTER TABLE location DROP waypoint;
ALTER TABLE location DROP event;
ALTER TABLE location DROP json;
ALTER TABLE location DROP batt;
ALTER TABLE location DROP acc;

ALTER TABLE location ADD vel INTEGER;
ALTER TABLE location ADD cog INTEGER;
ALTER TABLE location ADD trip INTEGER;
ALTER TABLE location ADD dist INTEGER;
ALTER TABLE location ADD t VARCHAR(1) NOT NULL;
ALTER TABLE location ADD ghash VARCHAR(6);
ALTER TABLE location ADD cc VARCHAR(2);
ALTER TABLE location ADD alt INTEGER;

FIXME: add ghash (insert '1' into geo and update location set ghash = '1' where ghash is NULL)
FIXME: drop table geo

UPDATE location SET vel = 0 WHERE vel IS NULL;
UPDATE location SET cog = 0 WHERE cog IS NULL;
UPDATE location SET trip = 0 WHERE trip IS NULL;
UPDATE location SET dist = 0 WHERE dist IS NULL;
UPDATE location SET alt = 0 WHERE alt IS NULL;


ALTER TABLE waypoint ADD tid VARCHAR(2);
ALTER TABLE waypoint DROP username;
ALTER TABLE waypoint DROP device;

-- now run `python lib/dbschema.py'