#!/bin/sh
#
# mysql-setup
#
# Build or rebuild example database in MySQL
#
# Andrew Findlay
# May 2010

mysql -u root -pexample <<EOF_MYSQL
drop database if exists classroom;
create database classroom;
grant all on classroom.* to student identified by 'example';
grant all on classroom.* to student@localhost identified by 'example';

use classroom;

create table person (
	pid int(8) unsigned auto_increment not null,
	username varchar(16),
	firstname varchar(16),
	lastname varchar(16),
	phone varchar(16),
	primary key (pid)
);
load data infile '$HOME/files/person-table.csv'
	into table person
	fields terminated by ','
	ignore 1 lines;

create table phone (
	username varchar(16),
	phone varchar(16),
	rownum int(8) unsigned auto_increment not null,
	primary key (rownum)
);
load data infile '$HOME/files/phone-table.csv'
	into table phone
	fields terminated by ','
	ignore 1 lines;

EOF_MYSQL


