-------------------------------------------------------------------
-- Copyright 2013 Code Strategies                                --
-- This code may be freely used and distributed in any project.  --
-- However, please do not remove this credit if you publish this --
-- code in paper or electronic form, such as on a web site.      --
-------------------------------------------------------------------
DROP DATABASE IF EXISTS tomcat_realm;
CREATE DATABASE tomcat_realm;
USE tomcat_realm;
CREATE TABLE tomcat_users (
	user_name varchar(20) NOT NULL PRIMARY KEY,
	password varchar(32) NOT NULL
);
CREATE TABLE tomcat_roles (
	role_name varchar(20) NOT NULL PRIMARY KEY
);
CREATE TABLE tomcat_users_roles (
	user_name varchar(20) NOT NULL,
	role_name varchar(20) NOT NULL,
	PRIMARY KEY (user_name, role_name),
	CONSTRAINT tomcat_users_roles_foreign_key_1 FOREIGN KEY (user_name) REFERENCES tomcat_users (user_name),
	CONSTRAINT tomcat_users_roles_foreign_key_2 FOREIGN KEY (role_name) REFERENCES tomcat_roles (role_name)
);
INSERT INTO tomcat_users (user_name, password) VALUES ('deron', 'deronpass');
INSERT INTO tomcat_users (user_name, password) VALUES ('larry', 'buythecompetition');
INSERT INTO tomcat_roles (role_name) VALUES ('dude');
INSERT INTO tomcat_roles (role_name) VALUES ('manager');
INSERT INTO tomcat_users_roles (user_name, role_name) VALUES ('deron', 'dude');
INSERT INTO tomcat_users_roles (user_name, role_name) VALUES ('deron', 'manager');
INSERT INTO tomcat_users_roles (user_name, role_name) VALUES ('larry', 'dude');
COMMIT;