locationbot.sql 1.0 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243
  1. -- Location Bot
  2. --
  3. -- Douglas Thrift
  4. --
  5. -- locationbot.sql
  6. create schema locationbot;
  7. create type locationbot.granularity as enum ('city', 'best');
  8. create table locationbot.nick (
  9. id serial primary key,
  10. nick text not null unique,
  11. secret char(32) not null,
  12. masks text[],
  13. channels text[],
  14. timezone text,
  15. granularity locationbot.granularity,
  16. location text,
  17. coordinates point,
  18. accuracy float,
  19. speed float,
  20. heading float,
  21. altitude float,
  22. altitude_accuracy float,
  23. updated timestamp with time zone
  24. );
  25. create table locationbot.latitude (
  26. id integer primary key references locationbot.nick on delete cascade,
  27. granularity locationbot.granularity not null,
  28. token text not null,
  29. secret text not null,
  30. authorized boolean not null
  31. );
  32. -- create table locationbot.foursquare (
  33. -- );
  34. create index nick_location_index on locationbot.nick (location);
  35. create index nick_updated_index on locationbot.nick (updated);
  36. create index nick_latitude_index on locationbot.nick (latitude);
  37. create index latitude_authorized on locationbot.latitude (authorized);