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/mksqldb,v 1.1 2004/01/04 07:20:33 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 	published INT
   99 );
  100 CREATE TABLE set_user (
  101 	user_id VARCHAR(255) NOT NULL,
  102 	set_id VARCHAR(255) NOT NULL,
  103 	psvn INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  104 	set_header TEXT,
  105 	problem_header TEXT,
  106 	open_date INT,
  107 	due_date INT,
  108 	answer_date INT,
  109 	published INT
  110 );
  111 CREATE TABLE problem (
  112 	set_id VARCHAR(255) NOT NULL,
  113 	problem_id VARCHAR(255) NOT NULL,
  114 	source_file TEXT,
  115 	value INT,
  116 	max_attempts INT
  117 );
  118 CREATE TABLE problem_user (
  119 	user_id VARCHAR(255) NOT NULL,
  120 	set_id VARCHAR(255) NOT NULL,
  121 	problem_id VARCHAR(255) NOT NULL,
  122 	source_file TEXT,
  123 	value INT,
  124 	max_attempts INT,
  125 	problem_seed INT,
  126 	status FLOAT,
  127 	attempted INT,
  128 	last_answer TEXT,
  129 	num_correct INT,
  130 	num_incorrect INT
  131 );
  132 EOF
  133 
  134 =head1 BUGS
  135 
  136 Only tested with MySQL.
  137 
  138 =head1 AUTHOR
  139 
  140 Written by Sam Hathaway, hathaway at users.sourceforge.net.
  141 
  142 =cut