<% #WorldGeeks Home Page require 'cgi' require 'worldgeeks/page' require 'worldgeeks/login' require 'worldgeeks/content_items' front = "
WorldGeeks data model, up to date as of Apr 30 2001:

OVERVIEW (from db)

                   List of relations
              Name               |   Type   |  Owner   
---------------------------------+----------+----------
 bboard_classification_map       | table    | postgres
 bboards                         | table    | postgres
 bboards_bboard_id_seq           | sequence | postgres
 classificatio_classificatio_seq | sequence | postgres
 classification_types            | table    | postgres
 content_classification_map      | table    | postgres
 content_edit__content_edit__seq | sequence | postgres
 content_edit_trail              | table    | postgres
 content_items                   | table    | postgres
 content_items_content_id_seq    | sequence | postgres
 listings_direc_directory_id_seq | sequence | postgres
 listings_directories            | table    | postgres
 listings_directories_map        | table    | postgres
 preferences                     | table    | postgres
 software_helper                 | table    | postgres
 software_view                   | view     | postgres
 tmp                             | table    | postgres
 user_alerts                     | table    | postgres
 user_alerts_alert_id_seq        | sequence | postgres
 user_classifications_map        | table    | postgres
 user_filters                    | table    | postgres
 user_group_id_seq               | sequence | postgres
 users                           | table    | postgres
(23 rows)


INDIVDIUAL TABLES

## users 
# ENTERED INTO WORLDGEEKS DBASE 17.4
# FIELD PERSONAL_STATEMENT ADDED 27.4
create table users (
user_id	                        integer primary key default nextval('user_group_id_seq'),
password                        text not null check(length(trim(password)) > 6),
user_name                       text not null unique,
first_name                      text,
last_name                       text,
organization                    text,
email                           text not null unique,
url                             text,
address_1                       text,
address_2                       text,
address_3                       text,
address_4                       text,
city                            text,
state                           text,
postal_code                     text,
country                         text,
primary_phone                   text,
primary_phone_type              text,  /*home, work, cell, etc*/
secondary_phone                 text,
secondary_phone_type            text,  /*home, work, cell, etc*/
icq_number                      text,
aim                             text,
privacy                         integer not null default '5',  /*show info to
registered users, lower is less private, higher is more private */
#FIELD PERSONAL STATEMENT ADDED 27.4 - to allow people to make short
statement appearing on link to their profiles
personal_statement		text,
status                         text not null, /* lower if we do not
like them, '0' if banned from contributing, higher if a moderator or admin */
registration_date               timestamp default current_date,
second_to_last_visit            timestamp, 
last_visit                      timestamp default current_date 
);

/*ENTERED INTO WORLDGEEKS DB 18 APR 2001
create table user_classifications_map (
      user_id                   integer not null references users,
      classification_id	        integer not null references
      classification_types,
      primary key (user_id, classification_id )
);    

# We need to consider tracking popularity of content items
# maybe with separate table, or as column in this one?
# ENTERED INTO WORLDGEEKS DBASE 17.4
create table content_items (
       content_id		 serial,
       content_type		 text,
       author			 text,
       title			 text,
       summary			 text,
       body			 text, 
       url			 text,
       refers_to		 integer references content_items,
       replies_to		 integer references content_items,
       rating			 integer,
       approval_needed		 bool,
       current_status		 text,
       seen_by			 text, 
       waiting_for		 text,
       date_submitted		 timestamp default current_timestamp,
       primary key(content_id)
 );

/*ADDED TO WORLDGEEKS DB 20 APR 2001
alter table content_items
add
editor_comments text;

/*ADDED TO WORLDGEEKS DB 24 APR 2001
alter table content_items
add
times_rated integer default '0';

/*28 APR 2001 to change column rating to type decimal
alter table content_items
rename column rating to rating_old;

alter table content_items
add
rating decimal default 1.0;

update content_items
set rating = 1.0
where content_type = 'comment';

/*ENTERED INTO WORLDGEEKS DB 18 APR 2001
create view software_view as
       select * from content_items, software_helper
       where content_id = software_id
;

/*ENTERED INTO WORLDGEEKS DB 18 APR 2001
create table software_helper (
       software_id		 integer not null references
       content_items,
       license			 text not null,
       download_url		 text not null,
       home_url			 text not null,
       download_referral_count	 integer,
       home_url_referral_count	 integer,
       system_requirements	 text
);

# ENTERED INTO WORLDGEEKS DBASE 17.4       
create table content_classification_map (
       content_id		integer references content_items,
       classification_id	integer references classification_types,
       primary key ( content_id, classification_id )
);

# ENTERED INTO WORLDGEEKS DBASE 17.4
create table classification_types (
       classification_id	  serial primary key,
       classification		  text,
       parent_id		  integer,
       description		  text
);

/*ENTERED INTO WORLDGEEKS DB 18 APR 2001
/* maintainer?? specify who is vetting it /*
create table bboards (
       bboard_id		  serial,
       bboard_parent_id		  integer references bboards,
       bboard			  text,
       moderated		  bool,
       primary key(bboard_id)
);

/*ENTERED INTO WORLDGEEKS DB 29 APR 2001
create table bboard_classification_map (
       bboard_id                 integer references bboards,
       classification_id         integer references
       classification_types,
       primary key ( bboard_id, classification_id )
);

# Separate from content classification systems
# ENTERED INTO WORLDGEEKS DBASE 17.4
create table listings_directories (
       directory_id		  serial primary key,
       parent_id		  integer,
       directory		text
);
       
# Links are considered a form of content so map
# references content_id and directory_id
# ENTERED INTO WORLDGEEKS DBASE 17.4
create table listings_directories_map (
       content_id			integer references content_items,
       directory_id		integer references
       listings_directories,
       primary key ( link_id, directory_id )
):

/*ENTERED INTO WORLDGEEKS DB 18 APR 2001
create table content_edit_trail (
       content_edit_id		  serial,
       content_id		  integer not null references content_items,
       former_status		  text,
       current_status		  text,
       change_timestamp		  timestamp not null default current_timestamp,
       editor			  integer not null references users,
       note			  text,
       primary key(content_edit_id)
);

/*ADDED TO WORLDGEEKS DB 28 APR 2001
alter table content_edit_trail
add
rating integer;

/*ENTERED INTO WORLDGEEKS DB 18 APR 2001
create table preferences (
      user_id			 integer not null references users,
      comment_threshold		 integer default '3',
      primary_language		 integer references classification_types,
      secondary_language	 integer references classification_types,
      tertiary_language		 integer references classification_types,
      has_filter		 bool,
      has_agent			 bool,
      receive_email_digest	 bool
);

/*ENTERED INTO WORLDGEEKS DB 18 APR 2001
create table user_filters (
       user_id			 integer not null references users,
       classification		 integer not null references classification_types,
       primary key (user_id, classification)
);

/*ENTERED INTO WORLDGEEKS DB 18 APR 2001
create table user_alerts (
       user_id			 integer not null references
       users,
       alert_id			 serial,
       show_on_front_page	 bool,
       email_alert_immed	 bool,
       email_alert_as_digest	 bool,
       separate_from_digest	 bool,
       alert_email_freq		 integer, /*every x days*/
       classification1		 integer references classification_types,       
       classification2		 integer references classification_types,       
       classification3		 integer references classification_types,       
       classification4		 integer references classification_types,       
       classification5		 integer references classification_types,       
       keyword			 text,
       primary key (user_id, alert_id)
);
" title = "Worldgeeks Data Model" frontpage = Page.new(title, "/worldgeeks/datamodel", front, "") print frontpage.render %>