/* All times are in the form YYYYMMDDHHMMSS */

/* Resource table */
/* A resource is ... */
/* resource_id - unique ID of a resource (machine generated) */
/* name - title of the resource as displayed to users */
/* type_id - match to list of resource types in resource_type table */
/* standard_identifier - unique code for resource specified by external
    authority e.g. ISSN, ISBN */
/* summary - description of the resource displayed to users (paragraph 
    length) */
/* language_code - identifier for the language of the resource */
/* icon_id - match to the small graphics in the icon table */
/* about_url - url of page about resource generally - see also help_url in
    location table */
/* about_url_text - title to display with link to about_url */
/* manager_persid - unique id of the person responsible for the resource
    (e_mail) */
/* ad_last_checked_time - last time the URLs in the table were found to be OK by
    the link checker */
/* ad_creator_persid - unique id of the persion who created the record
    (e_mail) */ 
/* ad_create_time - time when the resource record was created */
/* ad_last_mod_persid - unique id of the persion who most recently edited the
    record */
/* ad_last_mod_time - time when the resource record was most recently edited */

create table resource (
 resource_id integer not null,
 name varchar(100) not null,
 type_id integer,
 standard_identifier varchar(15),
 summary text,
 language_code char(3), /* z39.53 code - see  http://www.oasis_open.org/cover/nisoLang3_1994.html */
 icon_id integer,
 about_url varchar(300),
 about_url_text varchar(40), /* title to display with link to about_url */
 manager_persid varchar(60),
 ad_last_checked_time char(14),
 ad_creator_persid varchar(60),
 ad_create_time char(14),
 ad_last_mod_persid varchar(60),
 ad_last_mod_time char(14)
);

/* Location table */
/* A location is a way to access a resource */
/* location_id - unique id of a location (machine generated) */
/* resource_id - unique id of the resource */
/* name - title of the location - used to group locations together mainly for
    the administrators (to answer questions like "Which resources are on
    Decomate?" */
/* url - where to access the at this location */
/* comment - very short description of location (e.g. to display as ALT text for
    link to location) */
/* access_method_id - match to access_method table to describe how user can see
    resource */
/* supplier_id - match to supplier table */
/* help_url - url of page containing help specific to this location */
/* help_url_text - title to display with link to help_url */
/* media_type - one of list of values describing storage method for resource at
    this location */
/* search_protocol  - one of list of values matching table containing
    information about searching resource at this location */
/* historic_range_start - where location is time specific, when its coverage
    begins (e.g. for database of journals issues from 19920101) */
/* historic_range_end - where location is time specific, when its coverage ends
    (e.g. for database of journals issues from 19920101) */ 
/* content_depth - one of list of values describing how detailed contents of
    resource at location are (e.g. full text) */
/* licence_url - location of licence agreement for the resource at this 
    location */
/* licence_start_date - when licence starts */
/* licence_end_date - when licence ends */
/* available - switch to enable location to be temporarily removed from user
    display (e.g. if it's on a machine which is down) */
/* availability_message - short description of availability of resource always
    displayed to user (e.g. "Down for maintainance 04/01/2000") */
/* manager_persid - as in resource table */
/* ad_last_checked_time - as in resource table */
/* ad_creator_persid - as in resource table */
/* ad_create_time - as in resource table */
/* ad_last_mod_persid - as in resource table */
/* ad_last_mod_time - as in resource table */

create table location (
 location_id integer not null,
 resource_id integer not null,
 name char(20) not null,
 url varchar(300) not null,
 available char(1) not null, /* values: Y/N */
 comment varchar(60),
 access_method_id integer,
 supplier_id integer,
 help_url varchar(300),
 help_url_text varchar(40), /* title to display with link to help_url */
 media_type varchar(8), /* values: cd, www, hardcopy, ... */
 search_protocol varchar(10) not null, /* values: none, z3950, whoispp, sql, www, ... */
 historic_range_start char(8),
 historic_range_end char(8),
 content_depth varchar(13), /* values: full_text, abstract, bibliographic, ... */
 licence_url varchar(300),
 licence_start_date varchar(8),
 licence_end_date varchar(8),
 availability_message varchar(40),
 manager_persid varchar(60),
 ad_last_checked_time char(14),
 ad_creator_persid varchar(60),
 ad_create_time char(14),
 ad_last_mod_persid varchar(60),
 ad_last_mod_time char(12)
);

/* Supplier table */
/* Information about publisher or vendor of a resource_location */
/* Table maintained for administrative purposes, not available to end users */
/* supplier_id - unique identifier of supplier */
/* name - name of the supplier */
/* address - postal address */
/* contact_name - who is responsible for the resource_location at the
    supplier */
/* contact_no - telephone number to contact supplier */
/* help_no - telephone number for support from supplier */
/* help_email - email address for support from supplier */
/* url - WWW page of supplier */
/* ad_creator_persid  - as in resource table */
/* ad_create_time  - as in resource table */
/* ad_last_mod_persid  - as in resource table */
/* ad_last_mod_time  - as in resource table */
create table supplier (
 supplier_id integer not null,
 name varchar(50) not null,
 address text,
 contact_name varchar(30),
 contact_no varchar(20),
 help_no varchar(20),
 help_email varchar(60),
 url varchar(300),
 ad_creator_persid varchar(60),
 ad_create_time char(14),
 ad_last_mod_persid varchar(60),
 ad_last_mod_time char(14)
);

/* Group_right table */
/* Matches resource_locations against user group names to determine levels of
access to the resource */
/* location_id - unique id of the location involved */
/* group_id - unique id of the group involved (determined by the auth broker) */
/* access_right - level of access allowed to members of the group  - ordered
    list */
create table group_right (
 location_id integer not null,
 group_id varchar(10) not null,
 access_right varchar(10) not null /* values: read, access, search, ... */
);

/* Location ip_range table */
/* Join table to link locations with IP ranges to determine when a user is
allowed to access a location */
create table location_ip_range (
 location_id integer not null,
 ip_range_id integer not null
);

/* IP_range table */
/* Table to specify ip ranges */
/* ip_range_id - unique ID for range */
/* name - nickname for range (e.g. LSE campus) for easy identification */
/* ip - basic IP address */
/* mask - subnet mask */
create table ip_range (
 ip_range_id integer not null,
 name varchar(15) not null,
 ip char(15) not null,
 mask char(15) not null
);

/* Training session table */
/* Stores information about training available for a particular resource */
/* resource_id - link to resource table */
/* session_time - when the next session is to be held */
/* summary - information about the training available */
/* info_url - link to more detailed information about the training (if other
    fields are empty, can be used to link to timetable */
/* manager_persid unique id of the person responsible for the training
    (e_mail) */
/* place_url - link to map showing location of training session */
create table training_session (
 resource_id integer not null,
 session_time char(14),
 summary text,
 info_url char(300),
 manager_persid varchar(60),
 place_url char(300)  
);

/* Resource type table */
/* Classifies resources by function (not medium) */
/* type_id - unique identifier */
/* name - nickname for the resource type */
create table resource_type (
 type_id integer not null,
 name varchar(30) not null /* values: journal, website, bibliographic database, statistical database, ... */
);

/* Icon table */
/* Contains information about the icon to display with a resource (e.g. logo) */
/* icon_id - unique identifier */
/* url - location of the icon graphic */
/* alt_text - text to display in the ALT attribute for the IMG tag accessing the
    icon graphic */
create table icon (
 icon_id integer not null,
 url varchar(300) not null,
 alt_text varchar(30)
);

/* Resource_subject */
/* Join table linking resources and subjects */
create table resource_subject (
 resource_id integer not null,
 subject_id integer not null
);

/* Subject table */
/* Used to classify resources by a controlled list of subject terms (e.g. LBS
scheme */
/* subject_id - local unique identifier for this subject term */
/* term - term taken from the external schema */
/* origin_schema - name of the schema being used */
/* origin_identifier - unique key of the term in the schema definition, if it
    has them */
/* relevance - how much the resource is relevant to the subject */
create table subject (
 subject_id integer not null,
 term varchar(30) not null,
 origin_schema varchar(10),
 origin_identifier varchar(20),
 relevance integer
);

/* Resource_keyword table */
/* Join table linking resources and keywords */
create table resource_keyword (
 resource_id integer not null,
 keyword_id integer not null
);

/* Keyword table */
/* Keywords are suggested by the RDB administrator, who will be able to select
an existing word or add a new one (hence it is a less controlled list than the
subjects */
/* keyword_id - unique id for the keyword */
/* keyword - text of the keyword */
create table keyword (
 keyword_id integer not null,
 keyword varchar(30) not null
);

/* Access method table */
/* Information needed to provide direct access to a resource - details to be
filled in later */
create table access_method (
 access_method_id integer not null
);

/* Location_Z3950 table */
/* Search parameters for locations with search_protocol z3950 - details to be
filled in later */
create table location_z3950 (
 location_id integer not null
);

/* Location_Whois++ table */
/* Search parameters for locations with search_protocol whois++ - details to be
filled in later */
create table location_whoispp (
 location_id integer not null
);

/* Location_SQL table */
/* Search parameters for locations with search_protocol sql - details to be
filled in later */
create table location_sql (
 location_id integer not null
);

/* Location_WWW table */
/* Search parameters for locations with search_protocol www - details to be
filled in later */
create table location_www (
 location_id integer not null
);