/* SQL Scripts to create the tables for PIE for Headline.          */
/* --------------------------------------------------------------- */

/* item - contains the basic information about the PIE's items, the units */
/* into which pages are divided */
/* - itemid - unique identifier for the item; numeric, except for special */
/*     system items */
/* - title - title of the item */
/* - type - identifies what kind of item it is, should match a type in the */
/*     itemconfig table */
/* - parentid - in the hierarchical structure of items, the parent of the */
/*     item (e.g. the list of which a resource is a member) */
/* - ownerid - the owner of the item; generally headliner for system items; */
/*     should match a userid in the userdata table */
/* - expanded - 0 contracted, 1 expanded; determines which HTML to use for */
/*     item */
/* - positionname - determines where in the parent item this item is placed */
/* - positionno - orders items with the same positionname if parent has */
/*     orderby 0 */
/* - location - where to find the item; usually a URL for a resource; items */
/*     in resource database have special URLs begining rdb:// */
/* - description - description of the item */
/* - orderby - determines whether children of the item should be ordered by */
/*     positionno (0) or alphabetically by title (1) */
create table item (
  itemid varchar(25) not null,
  title varchar(100),
  type varchar(20) not null,
  parentid varchar(25),
  ownerid varchar(25),
  expanded varchar(1),
  positionname varchar(20),
  positionno varchar(7),
  location varchar(300),
  description text,
  orderby varchar(1)
);

/* userdata - information about users of the PIE */
/* - userid - the unique identifier which is used to log in to the PIE */
/* - greeting - phrase which appears on user's pages when they log in (e.g. */
/*     "Hello, Simon" */
/* - ip - IP address of user in most recent session */
/* - lastaction - the most recent request made by the user */
/* - lastactiondate - when the most recent request was made - in format */
/*     YYYYMMDD */
/* - lastactiontime - when the most recent request was made (used to */
/*     determine if user session has timed out) - in format HHMMSS */
/* - defaultitem - itemid of item to be displayed to user when first logged */
/*     in (created as copy of item with ID default on first log in) */
/* - mail - email address of user */
/* - lasttext1-3 - text entered in most recent search */
/* - lastfields1-3 - field specifications entered in most recent search */
/* - lastops2-3 - boolean operators entered in most recent search */
/* - lasttype - resource type specification entered in most recent search */
/* - lastage - resource age specification entered in most recent search */
create table userdata (
  userid varchar(25) not null,
  greeting varchar(100),
  ip varchar(15) not null,
  lastaction varchar(100),
  lastactiondate varchar(8),
  lastactiontime varchar(6),
  defaultitem varchar(25),
  mail varchar(50),
  lasttext1 varchar(140),
  lasttext2 varchar(140),
  lasttext3 varchar(140),
  lastfields1 varchar(15),
  lastfields2 varchar(15),
  lastfields3 varchar(15),
  lastops2 varchar(7),
  lastops3 varchar(7),
  lasttype varchar(30),
  lastage varchar(3)
);

/* last search resources - information about which resources were requested */
/* for user's most recent search */
/* - userid - unique identity of user, matches userid in userdata table (can */
/*     be repeated in this table) */
/* - resource - unique identity of resource, matches resource_id from */
/*     resource database */
create table last_search_resources (
  userid varchar(25) not null,
  resource integer
);

/* viewitem - maintains user-specific data about items they have read so that */
/* (for example) they can expand and contract them without affecting the */
/* views of other users */
/* - userid - the unique identifier which is used to log in to the PIE */
/* - itemid - unique identifier for the item; numeric, matches itemid in item */
/*      table */
/* - title - title of the item */
/* - type - identifies what kind of item it is, should match a type in the */
/*      itemconfig table */
/* - parentid - in the hierarchical structure of items, the parent of the */
/*      item (e.g. the list of which a resource is a member) */
/* - expanded - 0 contracted, 1 expanded; determines which HTML to use for */
/*      item */
/* - positionname - determines where in the parent item this item is placed */
/* - positionno - orders items with the same positionname if parent has */
/*     orderby 0 */
/* - location - where to find the item; usually a URL for a resource; items */
/*     in resource database have special URLs begining rdb:// */
/* - description - description of the item */
/* - orderby - determines whether children of the item should be ordered by */
/*     positionno (0) or alphabetically by title (1) */
create table viewitem (
  userid varchar(25) not null,
  itemid varchar(25) not null,
  title varchar(100),
  type varchar(20) not null,
  parentid varchar(25),
  expanded varchar(1),
  positionname varchar(20),
  positionno varchar(7),
  location varchar(300),
  description text,
  orderby varchar(1)
);

/* pagesnotviewed - for each user, lists the pages which they want to have */
/* filtered out of the listing of pages on the left hand side navigation bar */
/* WARNING: This table lists the pages *not* viewed! */
/* - userid - unique identity of user, matches userid in userdata table */
/* - itemid - unique identifier of saved search - matches itemid in item */
/*     table */
create table pagesnotviewed (
  userid varchar(25) not null,
  itemid varchar(12) not null
);

/* readgroups - determines which groups of users can read particular PIE */
/* items. If there is no entry for an item, it is assumed to inherit the same */
/* set of groups as its parent */
/* - itemid - unique identifier of the item, matching itemid in item table */
/* - groupid - unique identifier of the group, matching groupid in groups */
/*     table */
create table readgroups (
  itemid varchar(25) not null,
  groupid varchar(50)
);

/* editgroups - determines which groups of users can edit particular PIE */
/* items. If there is no entry for an item, it is assumed to inherit the same */
/* set of groups as its parent */
/* - itemid - unique identifier of the item, matching itemid in item table */
/* - groupid - unique identifier of the group, matching groupid in groups */
/*     table */
create table editgroups (
  itemid varchar(25) not null,
  groupid varchar(50)
);

/* search - contains details of saved searches */
/* itemid - unique identifier of saved search - matches itemid in item table */
/* - text1-3 - text entered in saved search */
/* - fields1-3 - field specifications entered in saved search */
/* - ops2-3 - boolean operators entered in saved search */
/* - type - resource type specification entered in saved search */
/* - age - resource age specification entered in saved search */
/* - frequency - how often the search should be re-run - daily, weekly or */
/*     monthly */
/* - last_run - date search last run - in format YYYYMMDD */
create table search (
  itemid varchar(25) not null,
  text1 varchar(140),
  text2 varchar(140),
  text3 varchar(140),
  fields1 varchar(15),
  fields2 varchar(15),
  fields3 varchar(15),
  ops2 varchar(7),
  ops3 varchar(7),
  type varchar(30),
  age varchar(3),
  frequency varchar(7),
  last_run char(8)
);

/* search resources - information about which resources were requested */
/* for saved search */
/* - itemid - unique identifier of saved search - matches itemid in item */
/*     table */
/* - resource - unique identity of resource, matches resource_id from */
/*     resource database */
create table search_resources (
  itemid varchar(25) not null,
  resource integer
);

/* itemconfig - contains the HTML for each type of item */
/* - type - unique identifier of item type, matches type in item table */
/* - htmlexpanded - pseudo-HTML to display for expanded items of this type */
/* - htmlcontracted - pseudo-HTML to display for contracted items of this */
/*     type */
create table itemconfig (
  type varchar(20) not null,
  htmlexpanded text,
  htmlcontracted text
);

/* groups - matches users and groups */
/* Note 1: each user is put into a group identified by their email address */
/* Note 2: groups are added whenever a user logs in but not deleted */
/* - mail - user email, matches mail in userdata table, can be repeated */
/* - groupid - unique identifier for group */
create table groups (
  mail varchar(50) not null,
  groupid varchar(50)
);

/* help - information for displaying as help messages */
/* - topic - unique identifier for help text */
/* - title - title of help text */
/* - helptext - body of help text */
/* - orderby - used to order list of help topics */
create table help (
  topic varchar(20) not null,
  title varchar(50),
  helptext text,
  orderby integer
);

create index viewitemindex1 on viewitem (itemid,parentid,userid);