-- AREA Database schema to be implemented in 2003
-- Bruno Wolff III
-- January 5, 2003

-- Make sure database is set up properly

-- First connect as postgres superuser

\c - postgres

-- Make sure that not everyone can create schemas or temporary tables

revoke all on database :DBNAME from public;

-- Delete the public schema if it exists

drop schema public cascade;

-- Create the public schema with limited access

begin;
create schema public;
revoke all on schema public from public;
grant usage on schema public to public;
grant all on schema public to bruno;
end;

-- Install the contrib/cube functions. When postgres was installed a
-- make install of contrib/cube must have been done.

\i cube.sql

-- Install the contrib/earthdistance functions. Currently no binary
-- earthdistance stuff is being used, so that a make install of
-- contrib/earthdistance isn't a prerequisite.

\i earthdistance.sql

-- Create the AREA administrator group

drop group area_admin;
create group area_admin;
alter group area_admin add user bruno;

-- bruno now has enough rights that we can use his account to finish up

set session authorization bruno;

-- We should now have a clean database with appropiate extensions and
-- can set up the AREA specific stuff.

-- Privacy
-- This table defines privacy attributes. This allows security to be
-- enforced by which username an application connects with, rather than
-- by having applications include security rules.
-- I anticipate codes to allow contact information:
--   To be publishable (on the web).
--   To be given out in response to special (one off) requests, by AREA admins.
--   To be given out to Tournament Directors.
--   To be given out to AREA participants (those that are ratable).
--   To be used for blind forwarding.
--   To be used for confirmation requests.

-- pcode is the internal code for a privacy type.
-- pid is the external code for a privacy type.
-- descr is the description of the privacy type.

create table privacy (
  pcode serial primary key,
  pid text unique not null constraint bad_pid check (pid ~ '^[A-Z]+$'),
  descr text not null constraint bad_char_in_descr check
    (descr ~ '^[\041-\176]+( [\041-\176]+)*$')
);

grant select, insert, update, delete on privacy to group area_admin;
grant select on privacy to public;

-- user_privacy
-- This table defines what privacy access database users have.
-- Restricted views of data will use the current user name (from the database
-- connection) to control access to contact data.
-- For some things more general access should also be indicated. For example,
-- for locations, web publishing should imply that it is OK to give it to other
-- AREA members. However, things that allow for outside automation, this
-- shouldn't be done. For example, web publishing shouldn't imply blind
-- forwarding for email addresses.

-- username is a database user name (ultimately from pg_shadow).
-- pcode is a type of allowed use of contact information.

create table user_privacy (
  username name,
  pcode int4 references privacy,
  primary key (username, pcode)
);

-- entity_type
-- This table defines valid entity types and indicates which ones can have
-- AREA ratings. Initially I plan to define types for people, games that
-- can beat their players, companies and clubs (gamer groups).

-- etcode is the unique internal code for referencing entity types.
-- etid is the unique external code for referencing entity types.
-- descr is a description of the entity tpye.
-- ratable is true if entities of this type can have AREA Ratings.
-- The primary key definition is used to keep ratable from being
--   changed after entities have been assigned an entity type.

create table entity_type (
  etcode serial primary key,
  etid text unique not null constraint bad_etid check (etid ~ '^[A-Z]+$'),
  descr text not null constraint bad_char_in_descr check
    (descr ~ '^[\041-\176]+( [\041-\176]+)*$'),
  ratable boolean not null,
  unique (etcode, ratable)
);

grant select, insert, update, delete on entity_type to group area_admin;
grant select on entity_type to public;

-- genlabs
-- This table defines valid alternate (not roman numerals) labels for 
-- generation label. Initially Sr -> 1 and Jr -> 2 will be defined.

-- genlab is the label (e.g. Sr or Jr)
-- gen is the generation number

create table genlabs (
  genlab text constraint bad_genlab check
    (genlab ~ '^[\041-\176]+( [\041-\176]+)*$'),
  gen int4 constraint bad_generation check (gen > 0 and gen < 4000),
  primary key (genlab, gen)
);

grant select, insert, update, delete on genlabs to group area_admin;
grant select on genlabs to public;

-- This stuff is pretty constant so load it at database creation

copy genlabs from stdin using delimiters ',';
Sr,1
Jr,2
\.

-- entity
-- This table has an entry for anything that might get an AREA rating
-- or have contact information. This includes people, games that can
-- win, companies and clubs.

-- entity is a unique internal code for referencing an entity.
-- current_areaid is the current AREA ID in use by this person.
-- current_name is the code for the entity's current name.
-- etcode is the code for what kind of entity they are.
-- rateable is whether this entity can be rated.
-- admin_lock is used to restrict changes to the entities data to just admins.
--   Typically after some kind of abuse or the entity's death. Changes can
--   still be requested through automated interfaces, but they can only be
--   applied after admin approval.
-- created is when the entity record was first created. This won't be too
--   accurate for old people as we haven't tracked that information previously.
-- The foreign key (entity, current_areaid) makes sure that there is an
--   AREA ID - entity pair for this entity and that it points back to it.
--   This constraint is added after entity_areaid is created.
-- The foreign key (current_name, entity) makes sure there is a name entry
--   for this entity and that it points back to it.
-- The foreign key (etcode, ratable) makes sure that the ratablility of
--   an entity type can't change while entities are using it.

create table entity (
  entity serial primary key,
  current_areaid text unique not null,
  current_name int4 unique not null,
  etcode int4 not null,
  ratable boolean not null,
  admin_lock boolean not null,
  created timestamp default current_timestamp not null
    constraint future check(created <= current_timestamp),
  foreign key (etcode, ratable) references entity_type (etcode, ratable)
);

grant select, insert, update, delete on entity to group area_admin;

-- entity_areaid
-- This table tracks all AREA IDs ever (officially) used by an entity.
-- The constraints don't prevent someone from reusing an old AREA ID,
-- but applications should require an AREA admin not to reuse an old ID
-- as this is likely to cause confusion. Exactly one AREA ID must
-- be current for each entity.

-- entity is the entity code for the entity that is or was using the AREA ID.
-- areaid is the AREA ID. Until all current AREA IDs use the new format, the
--   format checking will be liberal enough to allow all previously assigned
--   AREA IDs. Afterwards a check involving 'ended' can make sure current ones
--   follow the new standard. Until then the new standard will be enforced in
--   applications.
-- started is when the AREA ID was assigned to the entity.
-- ended is when the entity was no longer using this AREA ID.

create table entity_areaid (
  entity int4 not null references entity,
  areaid text not null constraint bad_areaid check
    (areaid ~ '^[A-Z0-9]+(\\.[0-9]{2,3})?$')
    constraint trailing_all_zeros check (areaid !~ '\\.0+?$')
    constraint missing_lead_zeros check (areaid !~ '^[0-9]{1,4}(\\.[0-9]+)?$'),
  started timestamp default current_timestamp not null
    constraint future check(started <= current_timestamp),
  ended timestamp constraint ea_bad_range check (ended > started)
    constraint future check(ended <= current_timestamp),
  primary key (entity, areaid)
);

grant select, insert, update, delete on entity_areaid to group area_admin;

-- In case we need to look up which entities used an AREA ID we want an
-- index on areaid.

create index entity_areaid_areaid on entity_areaid (areaid);

-- Because entity and entity_areaid refer to each other, one constraint
-- needs to be added later and needs to be deferrable.

alter table entity add
  foreign key (entity, current_areaid) references entity_areaid (entity, areaid)
  deferrable;

-- entity_name
-- This table contains the various names that have been used by an entity.
-- This generally shouldn't include typos. Typos should just get fixed.
-- Adding or changing an alternate name should also generally not result in
-- a new entry. New entries should mainly be made for legal name changes. This
-- way we won't forget about old names when looking at historical data.
-- Exactly one name must be current for a given entity.
-- Names are currently restricted to the English alphabet plus some limited
-- use of special characters. No accented characters are available.
-- I don't think indexes for names will be very useful. They won't be used
-- often, they will be accessed through a privacy view, they may be accessd
-- lower cased or with partial matches. There currently aren't enough names
-- for searching to be a big deal. If that changes a functional, partial
-- index can be created (and tested to make sure it really gets used).
-- This table should not be directly accessible except to AREA admins.

-- entity is the entity to which this name applies.
-- encode is a unique code for this entity that is useful for making sure
--   only one name is current and allowing typos to be more easily fixed.
-- lname is a person's last (or only) name(s).
-- alname is an altenate last name. (Typically a maiden name.)
-- fmname is a person's first and middle names if any. In some cases this
--   might just be initials.
-- aname is an alternate name that the person likes to be called. It might
--   be a nickname or a short version of their first name.
-- gen is their generation number. For the normal case, people should have
--   no generation rather than a generation of 1.
-- genlab is an alternate label to be used for displaying a person's
--   generation. Typically Sr or Jr. If there is no genlab label, then
--   roman numerals are used.
-- started is when AREA became aware of the entity using this name.
-- ended is when AREA became aware that this name was not to be used.
-- descr is for admin use. Typically it would indicate why an old name changed.

create table entity_name (
  entity int4 not null references entity,
  encode serial primary key,
  lname text not null constraint bad_last_name check
    (lname ~ '^[A-Za-z]+(( |\'|-)[A-Za-z]+)*$'),
  alname text constraint bad_alt_last_name check
    (lname ~ '^[A-Za-z]+(( |\'|-)[A-Za-z]+)*$'),
  fmname text constraint bad_first_or_mid_name check
    (fmname ~ '^[A-Za-z]+(( |\'|-)[A-Za-z]+)*$'),
  aname text constraint bad_alt_name check
    (aname ~ '^[A-Za-z]+(( |\'|-)[A-Za-z]+)*$'),
  gen int4 constraint bad_generation check (gen > 0 and gen < 4000),
  genlab text constraint missing_gen check (genlab is null or gen is not null),
  started timestamp default current_timestamp not null
    constraint future check(started <= current_timestamp),
  ended timestamp constraint en_bad_range check (ended > started)
    constraint future check(ended <= current_timestamp),
  descr text constraint bad_char_in_descr check
    (descr ~ '^[\041-\176]+( [\041-\176]+)*$'),
  unique (entity, encode),
  foreign key (genlab, gen) references genlabs
);

grant select, insert, update, delete on entity_name to group area_admin;

-- Because entity and entity_name refer to each other, one constraint
-- needs to be added later and needs to be deferrable.

alter table entity add
  foreign key (entity, current_name) references entity_name (entity, encode)
  deferrable;

-- entity_name_privacy
-- This table specifies what kind of access is allowed for a name.
-- The anticipated default for names is all uses are allowed.

-- encode is the entity_name code.
-- pcode is the privacy code.

create table entity_name_privacy (
  encode int4 references entity_name,
  pcode int4 references privacy,
  primary key (encode, pcode)
);

grant select, insert, update, delete on entity_name_privacy
  to group area_admin;

-- entity_phone
-- Telephone number for an entity.
-- The same phone number can be provided multiple times for the same entity,
-- as there may be different comments provided with different privacy
-- restrictions.

-- entity is the entity code.
-- epcode is key for this phone number.
-- cc is the country code. For the US (or any country covered by the NANP)
--    this is 1.
-- area is the area or city code. This is optional for some countries but should
--   be three digits if the country code is 1.
-- local is the local number. This should be seven digits if the country code
--   is 1.
-- ext is an extension.
-- descr is a comment about using the phone number.
-- touched is when this record was last updated.

create table entity_phone (
  entity int4 not null references entity,
  epcode serial primary key,
  cc text not null constraint bad_cc check (cc ~ '^[0-9]+$'),
  area text constraint bad_area check (area ~ '^[0-9]+$'),
  local text not null constraint bad_local check (local ~ '^[0-9]+$'),
  ext text constraint bad_ext check (ext ~ '^[0-9]+$'),
  descr text constraint bad_char_in_descr check
    (descr ~ '^[\041-\176]+( [\041-\176]+)*$'),
  touched timestamp not null
    constraint future check(touched <= current_timestamp),
  constraint no_900 check (cc <> '1' or (area <> '900' and area <> '976')),
  constraint nanp check (cc <> '1' or (area is not null and
    length(area) = 3 and length(local) = 7))
);

grant select, insert, update, delete on entity_phone to group area_admin;

-- entity_phone_privacy
-- This table defines what kind of access is allowed for a phone number.
-- The anticipated default is that it may not be given out.

-- epcode is the entity phone code.
-- pcode is the privacy code.

create table entity_phone_privacy (
  epcode int4 references entity_phone,
  pcode int4 references privacy,
  primary key (epcode, pcode)
);

grant select, insert, update, delete on entity_phone_privacy
  to group area_admin;

-- entity_email
-- Email address for an entity.
-- The same email address can be provided multiple times for the same entity,
-- as there may be different comments provided with different privacy
-- restrictions.
-- The applications should probably warn people about oddball, but legal
-- email addresses.
-- We aren't going to accept domain literals as those are only to be used
-- in emergencies to work around something that is broken.

-- entity is the entity code.
-- eecode is key for this email address.
-- local is the local part of the email address (not RFC 821 encoded).
-- domain is the lowercased domain name.
-- descr is a comment about using the email address.
-- touched is when this record was last updated.

create table entity_email (
  entity int4 not null references entity,
  eecode serial primary key,
  local text not null constraint bad_local check (local ~ '^[\040-\176]+$'),
  domain text not null constraint bad_domain check
    (domain ~ '^[a-z0-9][-a-z0-9]*[a-z0-9](\.[a-z0-9][-a-z0-9]*[a-z0-9])+$'),
  descr text constraint bad_char_in_descr check
    (descr ~ '^[\041-\176]+( [\041-\176]+)*$'),
  touched timestamp not null
    constraint future check(touched <= current_timestamp)
);

grant select, insert, update, delete on entity_email to group area_admin;

-- Just in case we want to look people up by email address or domain (which
-- has been normalized) create an index.

create index entity_email_address on entity_email (domain, local);

-- entity_email_privacy
-- This table defines what kind of access is allowed for an email address.
-- The anticipated default is that it may be used to confirm requests.

-- eecode is the entity email address.
-- pcode is the privacy code.

create table entity_email_privacy (
  eecode int4 references entity_email,
  pcode int4 references privacy,
  primary key (eecode, pcode)
);

grant select, insert, update, delete on entity_email_privacy
  to group area_admin;

-- entity_url
-- URL for an entity.
-- The same URL can be provided multiple times for the same entity,
-- as there may be different comments provided with different privacy
-- restrictions.
-- Only http or https URLs are allowed. No usernames or passwords are allowed.

-- entity is the entity code.
-- eucode is key for this URL.
-- url is the urlencoded URL. Even though browsers may encode stuff (e.g. ~) as
--   necessary we will force anything not allowed to be encoded. We can't do
--   the encoding ourselves because we need context to do that.
-- descr is a comment about using the URL.
-- touched is when this record was last updated.

create table entity_url (
  entity int4 not null references entity,
  eucode serial primary key,
  url text not null constraint bad_url check (url ~
    '^[hH][tT][tT][pP][sS]?://[a-zA-Z0-9][-a-zA-Z0-9]*[a-zA-Z0-9](\\.[a-zA-Z0-9][-a-zA-Z0-9]*[a-zA-Z0-9])+(:[0-9]+)?(/([a-zA-Z0-9$_.+!*\'(),/;:@&=]|%[0-9a-fA-F][0-9a-fA-F])*(\\?([a-zA-Z0-9$_.+!*\'(),/;:@&=]|%[0-9a-fA-F][0-9a-fA-F])*)?)?$'),
  descr text constraint bad_char_in_descr check
    (descr ~ '^[\041-\176]+( [\041-\176]+)*$'),
  touched timestamp not null
    constraint future check(touched <= current_timestamp)
);

grant select, insert, update, delete on entity_url to group area_admin;

-- entity_url_privacy
-- This table defines what kind of access is allowed for a URL.
-- The anticipated default is that it may be used for any purpose.

-- eucode is the entity URL.
-- pcode is the privacy code.

create table entity_url_privacy (
  eucode int4 references entity_url,
  pcode int4 references privacy,
  primary key (eucode, pcode)
);

grant select, insert, update, delete on entity_url_privacy
  to group area_admin;

-- entity_postal
-- Postal address for an entity.
-- The same postal address can be provided multiple times for the same entity,
-- as there may be different comments provided with different privacy
-- restrictions.
-- This will be treated as opaque. It is up to the members to make sure
-- their address is usable. We should remind them to include postal code
-- and country. They shouldn't include their name in the address. If they
-- have special instructions for using the address (like using someone
-- else's name instead of theirs) that should go in the description.

-- entity is the entity code.
-- emcode is key for this postal address.
-- address is the postal address. It may (and normally will) have multiple
--   lines.
-- descr is a comment about using the URL.
-- touched is when this record was last updated.

create table entity_postal (
  entity int4 not null references entity,
  emcode serial primary key,
  address text not null constraint bad_address check (address ~
    '^[\041-\176]+( [\041-\176]+)*(\n[\041-\176]+( [\041-\176]+)*)*$'),
  descr text constraint bad_char_in_descr check
    (descr ~ '^[\041-\176]+( [\041-\176]+)*$'),
  touched timestamp not null
    constraint future check(touched <= current_timestamp)
);

grant select, insert, update, delete on entity_postal to group area_admin;

-- entity_postal_privacy
-- This table defines what kind of access is allowed for a postal address.
-- The anticipated default is that it may be used by members and tournament
-- directors.

-- epcode is the entity postal address.
-- pcode is the privacy code.

create table entity_postal_privacy (
  emcode int4 references entity_postal,
  pcode int4 references privacy,
  primary key (emcode, pcode)
);

grant select, insert, update, delete on entity_postal_privacy
  to group area_admin;

-- entity_location
-- The location of an entity on the surface of the Earth.
-- The same location can be provided multiple times for the same
-- entity, as there may be different comments provided with different privacy
-- restrictions.
-- If someone doesn't want to provide exact coordinates for their location,
-- then it is suggested that they either give them for a nearby landmark or
-- round them off and note that in the description. That way searches for
-- nearby players will still work. People will normally give location in
-- latitude and longitude in fractional degrees. Internally this will be
-- converted to a retangular cooridate data type (cube).
-- Until check constraints are supported in domains, we need to have them here.

-- entity is the entity code.
-- elcode is key for this location.
-- location specifies the location on the surface of the earth
-- descr is a comment about using the location.
-- touched is when this record was last updated.

create table entity_location (
  entity int4 not null references entity,
  elcode serial primary key,
  location earth not null
    constraint not_point check(is_point(location))
    constraint not_3d check(cube_dim(location) <= 3)
    constraint on_surface check(abs(cube_distance(location,'(0)'::cube) /
    earth() - 1) < '10e-12'::float8);
  descr text constraint bad_char_in_descr check
    (descr ~ '^[\041-\176]+( [\041-\176]+)*$'),
  touched timestamp not null
    constraint future check(touched <= current_timestamp)
);

grant select, insert, update, delete on entity_location
  to group area_admin;

-- This index should allow for efficient checks of containment in cubes
-- A great circle distance can be easily changed into a secant distance
-- used for a bounding box, though the secant distance should be checked
-- on the returned locations.

create index entity_cube on entity_location using gist (location);

-- entity_location_privacy
-- This table defines what kind of access is allowed for a location.
-- The anticipated default is that it may be used by members and tournament
-- directors.

-- elcode is the entity location.
-- pcode is the privacy code.

create table entity_location_privacy (
  elcode int4 references entity_location,
  pcode int4 references privacy,
  primary key (elcode, pcode)
);

grant select, insert, update, delete on entity_location_privacy
  to group area_admin;

-- entity_admin_note
-- This table has notes about the entity added by AREA admins. They might
-- be used for such things as noting abuse incidents or why an administrative
-- lock has been applied to an entity. These are always restricted to AREA
-- admins.

-- entity is the entity code
-- eacode is the code for this note.
-- descr is the note
-- started is when the note was created.
-- ended is when the note was superceded or ceased to be applicable.

create table entity_admin_note (
  entity int4 not null references entity,
  eacode serial primary key,
  descr text not null constraint bad_char_in_descr check
    (descr ~ '^[\041-\176]+( [\041-\176]+)*$'),
  started timestamp not null
    constraint future check(started <= current_timestamp),
  ended timestamp constraint en_bad_range check (ended > started)
    constraint future check(ended <= current_timestamp)
);

grant select, insert, update, delete on entity_admin_note
  to group area_admin;

-- entity_view
-- Privacy restricted view of entity.

-- entity is the unique entity code
-- current_areaid is the current AREA ID
-- lname is the last name if available
-- alname is an alternate last name
-- fmname is the first and middle names if available
-- aname is an alternate name if available
-- gen is the generation number if available
-- genlab is the generation label if available
-- etcode is the entity type code for this entity
-- ratable is whether or not this entity can have game ratings

create view entity_view (entity, current_areaid, lname, alname, fmname, aname,
  gen, genlab, etcode, ratable)
  as select entity.entity, current_areaid, lname, alname, fmname, aname, gen,
    genlab, etcode, ratable
    from entity left join
      (select lname, alname, fmname, aname, gen, genlab, encode from entity_name
        where exists
          (select 1 from user_privacy join entity_name_privacy using (pcode)
            where user_privacy.username = current_user and
            entity_name_privacy.encode = entity_name.encode)) as name
      on (entity.current_name = name.encode);

grant select on entity_view to public;

-- entity_name_view
-- Privacy restricted view of entity_name

-- entity is the unique entity code
-- lname is the last name
-- alname is an alternate last name
-- fmname is the first and middle names
-- aname is an alternate name
-- gen is the generation number
-- genlab is the generation label
-- current is whether or not this name is current

create view entity_name_view
  as select entity.entity, lname, alname, fmname, aname, gen, genlab,
    entity.current_name = entity_name.entity as current
    from entity_name, entity
    where
      entity.entity = entity_name.entity and
      exists
        (select 1 from user_privacy join entity_name_privacy using (pcode)
        where user_privacy.username = current_user and
        entity_name_privacy.encode = entity_name.encode);

grant select on entity_name_view to public;

-- entity_phone_view
-- Privacy restricted view of entity_phone

-- entity is the unique entity code
-- cc is the country code
-- area is the area code
-- local is the local number
-- ext is the extension
-- descr has a usage comment

create view entity_phone_view
  as select entity, cc, area, local, ext, descr from entity_phone
    where exists
      (select 1 from user_privacy join entity_phone_privacy using (pcode)
        where user_privacy.username = current_user and
        entity_phone_privacy.epcode = entity_phone.epcode);

grant select on entity_phone_view to public;

-- entity_email_view
-- Privacy restricted view of entity_email

-- entity is the unique entity code
-- local is the local part of the email address
-- domain is the domain part of the email address
-- descr has a usage comment

create view entity_email_view
  as select entity, local, domain, descr from entity_email
    where exists
      (select 1 from user_privacy join entity_email_privacy using (pcode)
        where user_privacy.username = current_user and
        entity_email_privacy.eecode = entity_email.eecode);

grant select on entity_email_view to public;

-- entity_url_view
-- Privacy restricted view of entity_url

-- entity is the unique entity code
-- url is the URL
-- descr has a usage comment

create view entity_url_view
  as select entity, url, descr from entity_url
    where exists
      (select 1 from user_privacy join entity_url_privacy using (pcode)
        where user_privacy.username = current_user and
        entity_url_privacy.eucode = entity_url.eucode);

grant select on entity_url_view to public;

-- entity_postal_view
-- Privacy restricted view of entity_postal

-- entity is the unique entity code
-- address is the postal address
-- descr has a usage comment

create view entity_postal_view
  as select entity, address, descr from entity_postal
    where exists
      (select 1 from user_privacy join entity_postal_privacy using (pcode)
        where user_privacy.username = current_user and
        entity_postal_privacy.emcode = entity_postal.emcode);

grant select on entity_postal_view to public;

-- entity_location_view
-- Privacy restricted view of entity_location

-- entity is the unique entity code
-- location specifies the location using rectangular coordinates
-- descr has a usage comment

create view entity_location_view
  as select entity, location, descr from entity_location
    where exists
      (select 1 from user_privacy join entity_location_privacy using (pcode)
        where user_privacy.username = current_user and
        entity_location_privacy.elcode = entity_location.elcode);

grant select on entity_location_view to public;

-- Old Schema definitions being converted follow.

-- Publisher table
-- This is used to provide consistant versions of publisher names and
-- a single place to keep track of a URL for the publisher.

-- publishers  Table name
-- pubid       Internal code for a publisher
-- pubname     The name of the publisher
-- puburl      An optional URL for the publisher

create table publishers (
  pubid text primary key constraint bad_char_in_id check
    (pubid ~ '^[A-Z0-9]+$'),
  pubname text not null constraint bad_char_in_name check
    (pubname ~ '^[\041-\176]+( [\041-\176]+)*$'),
  puburl text constraint bad_char_in_url check
    (puburl ~ '^[\041-\176]+$')
);

-- Allow access to it from the web server account

grant select on publishers to public;

-- Rating type table
-- This table defines valid rating type codes and provides a text description

-- rtype    Rating type code (e.g. WTA, TEAM, RACE, TWO, etc.)
-- descr    A description of the rating type

create table rtypes (
  rtype text primary key constraint bad_char_in_rtype check
    (rtype ~ '^[A-Z0-9]+$'),
  descr text not null constraint bad_char_in_descr check
    (descr ~ '^[\041-\176]+( [\041-\176]+)*$')
);

-- Allow access to it from the web server account

grant select on rtypes to public;

-- Contacts table
-- This table defines valid contact addresses (mostly for reporting results)
-- These are all public AREA email addresses

-- contact   Contact code
-- email     Email address

create table contacts (
  contact text primary key constraint bad_char_in_contact check
    (contact ~ '^[A-Z0-9]+$'),
  email text not null constraint bad_char_in_email check
    (email ~ '^[-_.a-zA-Z0-9]+@[-_.a-zA-Z0-9]+$')
);

-- Allow access to it from the web server account

grant select on contacts to public;

-- Game definition table
-- This is the primary definition for games.
-- Titles may only contain printable US ASCII characters and
-- imbedded spaces. Sorting will be done using the US ASCII colating
-- sequence with uppercase letters treated as lowercase.

-- games    Table name
-- gameid   Initially will be excel sheet name
-- title    The game title
-- rtype    Rating type (e.g. WTA, RACE, TEAM or TWO)
-- contact  Where to send results (by email)
-- touched  Last time gameid, or title (not ratings) changed

create table games (
  gameid text primary key constraint bad_char_in_id check
    (gameid ~ '^[A-Z0-9]+$'),
  title text not null constraint bad_char_in_title check
    (title ~ '^[\041-\176]+( [\041-\176]+)*$'),
  rtype text not null constraint bad_rtype references rtypes,
  contact text not null constraint bad_contact references contacts,
  touched timestamp default 'now' not null
);

-- This probably won't help searching, but should help displaying the
-- sorted title index.

create index title_idx on games (lower(title));

-- Allow access to it from the web server account

grant select on games to public;

-- Table of publishers for a game

-- gamepubs   Table name
-- gameid    Game being published
-- pubid     One of the game's publishers

create table gamepubs (
  gameid text constraint bad_gameid references games,
  pubid text constraint bad_pubid references publishers,
  primary key (gameid, pubid)
);

-- Allow access to it from the web server account

grant select on gamepubs to public;

-- Table of wbc events (from any year, not just current)

-- wbc     Table name
-- code    A WBC code
-- event   The event title (may not always be known)
-- url     URL for the event page if any

create table wbc (
  code text primary key
    constraint bad_char_in_code check (code ~ '^[\041-\176]+$')
    constraint no_slash_in_code check (code !~ '/'),
  event text
    constraint bad_char_in_event check
      (event ~ '^[\041-\176]+( [\041-\176]+)*$'),
  url text
    constraint bad_char_in_url check (url ~ '^[\041-\176]+$')
);

-- For matching searches wbc codes are case insensitive and will be done
-- using lower case.

create unique index wbc_lower on wbc (lower(code));

-- Allow access to it from the web server account

grant select on wbc to public;

-- Table of WBC codes for games
-- There might be muliple entries for a single code or for a single game
-- because WBC events don't map 1 to 1 to games.
-- We can't have slashses (/) in the WBC code since we use the WBC code
-- in generated URLs. Unescaped slashes break relative references and
-- escaped ones are detected as not found before rewriting can catch
-- the request. Setting the base in generated html documents is one
-- possible work around if the WBC uses any in their codes.

-- wbcgames  Table name
-- code      WBC code from wbc table
-- gameid    gameid from games table

create table wbcgames (
  code text constraint bad_wbc_code references wbc,
  gameid text constraint bad_gameid references games,
  primary key (code, gameid)
);

-- Allow access to it from the web server account

grant select on wbcgames to public;

-- Current AREA ratings
-- Note this record should only be created for person - game pairs where
-- either the person has at least one recorded rated game or has specifically
-- notified AREA that they have an interest in the game.
-- This table should be used to retrive data even though it isn't the
-- ultimate source for data. It is too expensive to rederive this information
-- from the base data. If something bad happens, this table should be 
-- rebuildable from a transaction table that includes actions for the
-- results of rated games, expressing interest in a game, or making hand
-- corrections to fix problems.

-- crate    Table name
-- areaid   From the cname table
-- gameid   From the games table
-- rate     Current AREA rating
-- frq      Number of times payed rated games of this game
--          If frq is 0 the player has expressed interest in the game.
-- opp      Total number of different opponents played
-- rmp      Total number of rated games played remotely (PBM, PBEM, VASL, etc.)
-- trn      Total number of tournaments played in (with rated games)
-- rp       String indicating type of remote play
-- gm       String indciating when last gm'd
-- touched  Timestamp from when information in this record was changed
--          Typically this should be the last time a match was entered
--          for this person.

create table crate (
  areaid text constraint bad_areaid references cname,
  gameid text constraint bad_gameid references games,
  rate int4 default 5000 not null constraint rate_nonnegative check (rate >= 0),
  frq int4 default 0 not null constraint frq_nonnegative check (frq >= 0),
  opp int4 default 0 not null constraint opp_nonnegative check (opp >= 0),
  rmp int4 default 0 not null constraint rmp_nonnegative check (rmp >= 0),
  trn int4 default 0 not null constraint trn_nonnegative check (trn >= 0),
  rp text,
  gm text,
  touched timestamp default 'now' not null,
  primary key (areaid, gameid)
);

-- Allow for fast access when searching by game.

create index crate_game on crate (gameid, areaid);

-- Allow access to it from the web server account

grant select on crate to public;

-- Related URL table
-- This is used to have links to other web sites related to a game displayed.

-- gameurls  Table name
-- gameid    Game the link applies to
-- url       The url for the link
-- comment   Text describing the relation
-- expires   When to stop displaying the link

create table gameurls (
  gameid text constraint bad_gameid references games,
  url text constraint bad_char_in_url check
    (url ~ '^[\041-\176]+$'),
  comment text not null constraint bad_char_in_comment check
    (comment ~ '^[\041-\176]+( [\041-\176]+)*$'),
  expires timestamp default 'infinity' not null,
  primary key (gameid, url)
);

-- Allow access to it from the web server account

grant select on gameurls to public;