View of /trunk/webwork2/bin/mksqldb

    1 #!/usr/bin/env perl
    2 ################################################################################
    3 # WeBWorK Online Homework Delivery System
    4 # Copyright © 2000-2003 The WeBWorK Project, http://openwebwork.sf.net/
    5 # $CVSHeader: webwork-modperl/bin/addcourse,v 1.5 2004/01/03 20:07:02 sh002i Exp $
    6 # 
    7 # This program is free software; you can redistribute it and/or modify it under
    8 # the terms of either: (a) the GNU General Public License as published by the
    9 # Free Software Foundation; either version 2, or (at your option) any later
   10 # version, or (b) the "Artistic License" which comes with this package.
   11 # 
   12 # This program is distributed in the hope that it will be useful, but WITHOUT
   13 # ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
   14 # FOR A PARTICULAR PURPOSE.  See either the GNU General Public License or the
   15 # Artistic License for more details.
   16 ################################################################################
   17 
   18 =head1 NAME
   19 
   20 mksqldb - generate SQL statments defining a course database
   21 
   22 =head1 SYNOPSIS
   23 
   24  mksqldb COURSEID | mysql -u root -p
   25 
   26 =head1 DESCRIPTION
   27 
   28 Generates and prints SQL statments defining a course database. These statements
   29 can then be piped to the SQL console of your choice.
   30 
   31 In addition to executing the statements generated by this utility, you must
   32 also grant the WeBWorK system permission to access your database server. Since
   33 this is a database-specific process, it is beyond the scope of this utility.
   34 
   35 =head1 OPTIONS
   36 
   37 =over
   38 
   39 =item I<COURSEID>
   40 
   41 The name of the course for which to generate statements.
   42 
   43 =back
   44 
   45 =cut
   46 
   47 use strict;
   48 use warnings;
   49 
   50 sub usage {
   51 	print STDERR "usage: $0 COURSEID\n";
   52 	exit;
   53 }
   54 
   55 my $courseID = shift;
   56 
   57 unless ($courseID) {
   58 	print STDERR "$0: must specify COURSEID.\n";
   59 	usage();
   60 };
   61 
   62 my $database = "webwork_$courseID";
   63 
   64 print <<EOF;
   65 CREATE DATABASE $database;
   66 USE $database;
   67 CREATE TABLE user (
   68 	user_id VARCHAR(255) NOT NULL PRIMARY KEY,
   69 	first_name TEXT,
   70 	last_name TEXT,
   71 	email_address TEXT,
   72 	student_id TEXT,
   73 	status TEXT,
   74 	section TEXT,
   75 	recitation TEXT,
   76 	comment TEXT
   77 );
   78 CREATE TABLE password (
   79 	user_id VARCHAR(255) NOT NULL PRIMARY KEY,
   80 	password TEXT
   81 );
   82 CREATE TABLE permission (
   83 	user_id VARCHAR(255) NOT NULL PRIMARY KEY,
   84 	permission INT
   85 );
   86 CREATE TABLE key_not_a_keyword (
   87 	user_id VARCHAR(255) NOT NULL PRIMARY KEY,
   88 	key_not_a_keyword TEXT,
   89 	timestamp INT
   90 );
   91 CREATE TABLE set_not_a_keyword (
   92 	set_id VARCHAR(255) NOT NULL PRIMARY KEY,
   93 	set_header TEXT,
   94 	problem_header TEXT,
   95 	open_date INT,
   96 	due_date INT,
   97 	answer_date INT
   98 );
   99 CREATE TABLE set_user (
  100 	user_id VARCHAR(255) NOT NULL,
  101 	set_id VARCHAR(255) NOT NULL,
  102 	psvn INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  103 	set_header TEXT,
  104 	problem_header TEXT,
  105 	open_date INT,
  106 	due_date INT,
  107 	answer_date INT
  108 );
  109 CREATE TABLE problem (
  110 	set_id VARCHAR(255) NOT NULL,
  111 	problem_id VARCHAR(255) NOT NULL,
  112 	source_file TEXT,
  113 	value INT,
  114 	max_attempts INT
  115 );
  116 CREATE TABLE problem_user (
  117 	user_id VARCHAR(255) NOT NULL,
  118 	set_id VARCHAR(255) NOT NULL,
  119 	problem_id VARCHAR(255) NOT NULL,
  120 	source_file TEXT,
  121 	value INT,
  122 	max_attempts INT,
  123 	problem_seed INT,
  124 	status FLOAT,
  125 	attempted INT,
  126 	last_answer TEXT,
  127 	num_correct INT,
  128 	num_incorrect INT
  129 );
  130 EOF
  131 
  132 =head1 BUGS
  133 
  134 Only tested with MySQL.
  135 
  136 =head1 AUTHOR
  137 
  138 Written by Sam Hathaway, hathaway at users.sourceforge.net.
  139 
  140 =cut