View of /trunk/webwork2/doc/devel/new-DB-sql

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