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

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