How this Site was Built

While some parts of this site existed, namely home page and resume page, it was a traditionally built static web-site with no dynamic capabilities. EDIT 6100 gave me a prime opportunity to turn the site into a dynamically built and interactive site with some database tables driving the show.

I wanted to give users a way of leaving comments on the site as well as offering up their own definitions for Task #1 and this required creating a user login interface as well as constructing forms for accepting data input. This gave me a chance to learn a lot more about the python scripting language, especially in the critical area of processing data forms and validating user input (rather than blindly accepting that they will enter good data). It is not fool proof, so please do report any weird errors or quirks you do encounter so I can continue to make the site better!

All technology used is 100% open source software that is freely available to anyone. To learn more about open source, visit some of these links:

Schema

For those of you really interested in the details, here's the data structure set up to support the dynamics of the site.

CREATE SEQUENCE id_seq;

CREATE TABLE users (
  userid            integer       not null constraint users_pk primary key,
  email             varchar(150)  not null constraint users_email_un unique,
  password          varchar(15)   not null,
  firstname         varchar(50)   not null,
  lastname          varchar(50)   not null,
  screenname        varchar(100)  not null constraint users_screenname_un unique,
  privname          integer       not null default 0,
  privemail         integer       not null default 5,
  emailverified     boolean       not null default 'f',
  emailbouncing     boolean       not null default 'f',
  noalertsuntil     timestamp,
  lastvisit         timestamp,
  nexttolastvisit   timestamp,
  n_sessions        integer       not null default 1,
  datecreated       timestamp     not null default now(),
  lastupdate        timestamp     not null default now()
);

CREATE TABLE sessions (
  sessionid        integer       constraint sessions_pk primary key,
  userid           integer,      -- no constraint because we don't always know user (i.e. anon users)
  pageviews        integer       not null default 1,
  sessiondate      date          not null default now(), -- no time part!
  created          datetime      not null default now(),
  lastupdated      datetime      not null default now(),
  url              varchar(512),
  referer          varchar(512)
);


CREATE INDEX users_email_idx on users(email);

CREATE TABLE userprefs (
   userid           integer         constraint userprefs_userid_fk references users(userid) on delete cascade
                                      constraint userprefs_pk primary key,
   textonlyspam     boolean         default 'f',
   languagepref     char(2)         default 'en',
   dontspamme       boolean         default 'f'
);


CREATE TABLE urls (
   urlid             integer         not null constraint urls_urlid_pk primary key,
   url               varchar(400)    not null
);

CREATE TABLE comments (
   commentid         integer         not null constraint comments_id_pk primary key,
   urlid             integer         not null constraint comments_urlid_fk 
                                                references urls(urlid) on delete cascade,
   userid            integer         not null constraint comments_userid_fk 
                                                references users(userid) on delete cascade,
   comment           varchar(4097)   not null,
   visible           boolean         not null default 'f',
   datecreated       timestamp       not null default now()
);

CREATE TABLE edit6100_defs (
   defid             integer         not null constraint edit6100_defs_pk primary key,
   userid            integer         not null constraint edit6100_defs_userid_fk 
                                                references users(userid) on delete cascade,
   title             varchar(200)    not null,
   definition        varchar(4096)   not null,
   source            varchar(250)    not null,
   link              varchar(250)    not null,
   visible           boolean         not null default 'f',            
   datecreated       timestamp       not null default now()
);

CREATE TABLE it_cats (
   catid             integer         not null constraint it_cats_pk primary key,
   userid            integer         not null constraint it_cats_userid_fk 
                                                references users(userid) on delete cascade,
   catname           varchar(200)    not null
);

CREATE TABLE it_defs (
   defid             integer         not null constraint it_defs_pk primary key,
   userid            integer         not null constraint it_defs_userid_fk 
                                                references users(userid) on delete cascade,
   catid             integer         not null constraint it_defs_catid_fk 
                                                references it_cats(catid) on delete cascade,
   defname           varchar(200)    not null,
   definition        varchar(4096)   not null,
   source            varchar(250)    not null,
   link              varchar(250)    not null,
   visible           boolean         not null default 'f',            
   datecreated       timestamp       not null default now()
);

Reader Comments

Comments Welcome

All valid comments concerning this page (i.e. non-flameable material, smears, profanity, slandering, or other similiar commentary) will be posted here for others to read) whether critical or supportive in nature. In other words, differing opinions are not grounds for censorship.

Add a Comment

Want to leave a comment? Just log into the web-site and then you will see an entry form here for entering comments.