View of /trunk/webwork-modperl/doc/new-DB-sql

    1 CREATE DATABASE webwork;
    2 
    3 CREATE TABLE user (
    4 	id		VARCHAR(255) NOT NULL PRIMARY KEY,
    5 	first_name	TEXT,
    6 	last_name	TEXT,
    7 	email_address	TEXT,
    8 	student_id	TEXT,
    9 	status		TEXT,
   10 	section		TEXT,
   11 	recitation	TEXT,
   12 	comment		TEXT
   13 );
   14 
   15 CREATE TABLE password (
   16 	user_id		VARCHAR(255) NOT NULL PRIMARY KEY,
   17 	password	TEXT
   18 );
   19 
   20 CREATE TABLE permission (
   21 	user_id		VARCHAR(255) NOT NULL PRIMARY KEY,
   22 	permission	INT
   23 );
   24 
   25 CREATE TABLE key (
   26 	user_id		VARCHAR(255) NOT NULL PRIMARY KEY,
   27 	key		TEXT
   28 );
   29 
   30 CREATE TABLE set (
   31 	id		VARCHAR(255) NOT NULL PRIMARY KEY,
   32 	set_header	TEXT,
   33 	problem_header	TEXT,
   34 	open_date	DATETIME,
   35 	due_date	DATETIME,
   36 	answer_date	DATETIME
   37 );
   38 
   39 CREATE TABLE set_user (
   40 	user_id		VARCHAR(255) NOT NULL,
   41 	set_id		VARCHAR(255) NOT NULL,
   42 	psvn		INT,
   43 	set_header	TEXT,
   44 	problem_header	TEXT,
   45 	open_date	DATETIME,
   46 	due_date	DATETIME,
   47 	answer_date	DATETIME
   48 );
   49 
   50 CREATE TABLE problem (
   51 	id		VARCHAR(255) NOT NULL PRIMARY KEY,
   52 	set_id		VARCHAR(255) NOT NULL,
   53 	source_file	TEXT,
   54 	value		INT,
   55 	max_attempts	INT
   56 );
   57 
   58 CREATE TABLE problem_user (
   59 	user_id		VARCHAR(255) NOT NULL,
   60 	set_id		VARCHAR(255) NOT NULL,
   61 	problem_id	VARCHAR(255) NOT NULL,
   62 	source_file	TEXT,
   63 	value		INT,
   64 	max_attempts	INT,
   65 	problem_seed	INT,
   66 	status		FLOAT,
   67 	attempted	INT,
   68 	last_answer	TEXT,
   69 	num_correct	INT,
   70 	num_incorrect	INT,
   71 );