# Author: Kathy Ahn (kathy@hardcoder.com)
# Filename: db.sql.txt
# example sql script for a dynamically driven web site

create database mysite;
use mysite;

create table usr 	(id INT NOT NULL AUTO_INCREMENT,
				login VARCHAR(50) NOT NULL,
				password VARCHAR(50) NOT NULL,
				email VARCHAR(100) NOT NULL,
				make_public CHAR(1) NOT NULL,
				description TEXT,
				pic VARCHAR(200),
				date_created DATETIME NOT NULL,
				PRIMARY KEY(id),
				INDEX usr_login_index(login, password));					

create table auth	(id INT NOT NULL AUTO_INCREMENT,
				usr_id INT NOT NULL,
				auth_level INT NOT NULL,
				PRIMARY KEY(id),
				FOREIGN KEY(usr_id)
				REFERENCES usr(id));
					
create table category	(id INT NOT NULL AUTO_INCREMENT,
					name VARCHAR(50) NOT NULL,
					type VARCHAR(50) NOT NULL,
					dir_name VARCHAR(50) NOT NULL,
					public CHAR(1) NOT NULL,
					PRIMARY KEY(id));
														
create table content 	(id INT NOT NULL AUTO_INCREMENT,
					cat_id INT NOT NULL,
					title VARCHAR(60) NOT NULL,
					content TEXT NOT NULL,
					usr_id INT,
					date_created DATETIME NOT NULL,
					public CHAR(1),
					reviewed CHAR(1),
					PRIMARY KEY (id),
					FOREIGN KEY(cat_id)
					REFERENCES category(id),
					FOREIGN KEY (usr_id)
					REFERENCES usr(id),
					INDEX content_index(id));


# DATABASE USE PERMISSIONS 																
grant select, insert, update, delete on mysite.* to mysiteuser@localhost identified by 'somepassword';
flush privileges;

# INSERTS - EXAMPLE CATEGORIES
# insert into category (0, name, type, dir_name, public)	
# type is a shorthand name
insert into category values(0, 'My Links', 'Links', 'links', 'Y');
insert into category values(0, 'Developer Resources', 'Resources', 'resources', 'Y');
insert into category values(0, 'Database Examples', 'database', 'db', 'Y');