locationbot/locationbot.sql

-- Location Bot
--
-- Douglas Thrift
--
-- locationbot.sql

create schema locationbot;

create type locationbot.granularity as enum ('city', 'best');

create table locationbot.nick (
	id serial primary key,
	nick text not null unique,
	secret char(32) not null,
	masks text[],
	channels text[],
	timezone text,
	granularity locationbot.granularity,
	location text,
	coordinates point,
	accuracy float,
	speed float,
	heading float,
	altitude float,
	altitude_accuracy float,
	updated timestamp with time zone
);

create table locationbot.latitude (
	id integer primary key references locationbot.nick on delete cascade,
	granularity locationbot.granularity not null,
	token text not null,
	secret text not null,
	authorized boolean not null
);

-- create table locationbot.foursquare (
-- );

create index nick_location_index on locationbot.nick (location);
create index nick_updated_index on locationbot.nick (updated);
create index nick_latitude_index on locationbot.nick (latitude);
create index latitude_authorized on locationbot.latitude (authorized);