#!/bin/sh
#
  MYSQL="/usr/local/mysql/bin/mysql -u root " 


$MYSQL << OK_ALL_DONE
drop database push20 ;
create database push20 ;
use push20;


create table releases_history (
	id mediumint not null auto_increment primary key,
	release_id MEDIUMINT(9) NOT NULL,
	history BLOB DEFAULT NULL,
	index(id)
);

create table projects_history (
	id mediumint not null auto_increment primary key,
	project_id MEDIUMINT(9) NOT NULL,
	history BLOB DEFAULT NULL,
	index(id)
);

create table beta_push_history (
	id mediumint not null auto_increment primary key,
	project_id MEDIUMINT(9),
	dir_names MEDIUMINT(9) NOT NULL,
	filepaths MEDIUMINT(9) NOT NULL,
	user MEDIUMINT(9) NOT NULL,
	index(id)
);

create table static_push_history (
	id mediumint not null auto_increment primary key,
	release_id MEDIUMINT(9) NOT NULL,
	project_id MEDIUMINT(9),
	bug_id MEDIUMINT(9),
	dir_names MEDIUMINT(9) NOT NULL,
	filepaths MEDIUMINT(9) NOT NULL,
	user MEDIUMINT(9) NOT NULL,
	index(id)
);

create table request_push_history (
	id mediumint not null auto_increment primary key,
	release_id MEDIUMINT(9) NOT NULL,
	project_id MEDIUMINT(9),
	bug_id MEDIUMINT(9),
	dir_names MEDIUMINT(9) NOT NULL,
	filepaths MEDIUMINT(9) NOT NULL,
	user MEDIUMINT(9) NOT NULL,
	area ENUM('trunk','qa_branch','prod_branch') NOT NULL DEFAULT 'qa_branch',
	index(id)
);

create table dir_names (
	id mediumint not null auto_increment primary key,
	name varchar (254) binary  not null,
	perms varchar (254) binary default 'all',
	area ENUM('www','sandbox','images','src','static','env') NOT NULL DEFAULT 'www',
	index(id)
);

create table users (
    id mediumint not null auto_increment primary key,
    userid varchar (56) binary  not null,
    push tinyint default '0',
    restart tinyint default '0',
    db_push tinyint default '0',
    db_machine tinyint default '0',
    db_cvs tinyint default '0',
    releases tinyint default '0',
    projects tinyint default '0',
    qa tinyint default '0',
    stress_test tinyint default '0',
    users tinyint default '0',
    index(id)
);

create table filepaths (
	id mediumint not null auto_increment primary key,
	dir_name MEDIUMINT(9) NOT NULL,
	path varchar (254) binary  not null,
	trunk_version VARCHAR(12) BINARY,
	qa_branch_version VARCHAR(12) BINARY,
	prod_branch_version VARCHAR(12) BINARY,
	beta_branch_version VARCHAR(12) BINARY,
	beta_version VARCHAR(12) BINARY,
	index(id),
	index(path)
);

create table queue (
	id MEDIUMINT(9) NOT NULL PRIMARY KEY AUTO_INCREMENT DEFAULT 0,
	filename MEDIUMINT(9) NOT NULL,
	username MEDIUMINT(9) NOT NULL,
	version VARCHAR(12) BINARY NOT NULL,
	type ENUM('Beta','Static','Request') NOT NULL DEFAULT 'Request',
dest ENUM('trunk','qa_branch','prod_branch','beta','static') NOT NULL DEFAULT 'trunk',
	status ENUM('cart','in_qa') NOT NULL DEFAULT 'cart',
	selfqa MEDIUMINT(9) NOT NULL,
	peerqa MEDIUMINT(9) NOT NULL,
	index(id),
	index(type)
);

create table pushcomments (
	id MEDIUMINT(9) NOT NULL PRIMARY KEY AUTO_INCREMENT DEFAULT 0,
	comment BLOB DEFAULT NULL,
	index(id)
);

create table pushhistory (
	id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	when DATETIME NOT NULL DEFAULT '0000-00-00',
	user MEDIUMINT(9) NOT NULL DEFAULT 0,
	file MEDIUMINT(9) NOT NULL DEFAULT 0,
	version VARCHAR(12) BINARY NOT NULL,
	comment MEDIUMINT(9) NOT NULL DEFAULT 0,
	destination ENUM('stage','www','delete') NOT NULL DEFAULT 'stage',
	deleted MEDIUMINT(9) DEFAULT NULL,
	index(id)
);

create table dal_machines (
	id MEDIUMINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	machineid VARCHAR(30) BINARY,
	location VARCHAR(30) BINARY,
	index(id)
);

create table www_machines (
	id MEDIUMINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	machineid VARCHAR(30) BINARY,
	location VARCHAR(30) BINARY,
	index(id)
);

create table machines (
	id MEDIUMINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	machineid VARCHAR(30) BINARY,
	dal_server ENUM('true','false') NOT NULL DEFAULT 'false',
	www_server ENUM('true','false') NOT NULL DEFAULT 'false',
	location ENUM('dev','stage','www') NOT NULL,
	index(id)
);


create table dirpermissions (
    user  mediumint not null,
    dir   varchar (255) binary not null ,
    recursive  tinyint ,
    index (user)
);

create table features (
	id mediumint not null auto_increment primary key,
	username MEDIUMINT(9) NOT NULL,
	tag varchar (56) binary  not null,
	released MEDIUMINT(9) default NULL,
	comment BLOB DEFAULT NULL,
	release_id MEDIUMINT(9) NOT NULL,
	pm_id MEDIUMINT(9) NOT NULL,
	depends varchar (56) binary  not null,
	modules ENUM('webdev_www','webdev','dal','all') NOT NULL DEFAULT 'webdev_www',
	index(id),
	index(username),
	index(pm_id)
);

create table projects (
	id mediumint(9) not null auto_increment primary key,
	name varchar (144) binary not null,
	pm MEDIUMINT(9),
	tech_lead MEDIUMINT(9) NOT NULL,
	first_eng MEDIUMINT(9),
	second_eng MEDIUMINT(9),
	server_eng MEDIUMINT(9),
	db_eng MEDIUMINT(9),
	summary BLOB DEFAULT NULL,
	comment BLOB DEFAULT NULL,
	depends BLOB DEFAULT NULL,
	notes BLOB DEFAULT NULL,
	testing_url varchar (96) binary DEFAULT NULL,
	spec varchar (96) binary DEFAULT NULL,
	spec_due_date varchar (56) binary DEFAULT NULL,
	spec_complete ENUM('TRUE','FALSE') NOT NULL DEFAULT 'FALSE',
	tech_spec varchar (96) binary DEFAULT NULL,
	tech_spec_due_date varchar (56) binary DEFAULT NULL,
	tech_spec_complete ENUM('TRUE','FALSE') NOT NULL DEFAULT 'FALSE',
	mockup varchar (96) binary DEFAULT NULL,
	mockup_due_date varchar (56) binary DEFAULT NULL,
	mockup_complete ENUM('TRUE','FALSE') NOT NULL DEFAULT 'FALSE',
	testing_plan varchar (96) binary DEFAULT NULL,
	testing_plan_due_date varchar (56) binary DEFAULT NULL,
	testing_plan_complete ENUM('TRUE','FALSE') NOT NULL DEFAULT 'FALSE',
	bugs varchar (96) binary DEFAULT NULL,
	cc_list varchar (96) binary DEFAULT NULL,
	release_id MEDIUMINT(9),
	type MEDIUMINT(9) NOT NULL,
	phase MEDIUMINT(9) NOT NULL,
	priority MEDIUMINT(9) NOT NULL,
	effort_tech_lead MEDIUMINT(9) NOT NULL default '0',
	effort_first_eng MEDIUMINT(9) NOT NULL default '0',
	effort_second_eng MEDIUMINT(9) NOT NULL default '0',
	effort_server_eng MEDIUMINT(9) NOT NULL default '0',
	effort_db_eng MEDIUMINT(9) NOT NULL default '0',
	code_review MEDIUMINT(9) DEFAULT NULL,
	signoff_eng MEDIUMINT(9) NOT NULL default 'NA',
	signoff_servereng MEDIUMINT(9) NOT NULL default 'NA',
	signoff_dbeng MEDIUMINT(9) NOT NULL default 'NA',
	signoff_cc MEDIUMINT(9) NOT NULL default 'NA',
	signoff_report MEDIUMINT(9) NOT NULL default 'NA',
	signoff_legal MEDIUMINT(9) NOT NULL default 'NA',
	signoff_market MEDIUMINT(9) NOT NULL default 'NA',
	signoff_qa MEDIUMINT(9) NOT NULL default 'NA',
	creation_date varchar (56) binary DEFAULT NULL,
        start_date varchar (56) binary DEFAULT NULL,
	last_modified_date varchar (56) binary DEFAULT NULL,
	target_date varchar (56) binary DEFAULT NULL,
	release_date varchar (56) binary DEFAULT NULL,
	completion_date varchar (56) binary DEFAULT NULL,
	status MEDIUMINT(9) NOT NULL default '1',
	index(id),
	index(tech_lead)
);

create table signoff_eng (
	id mediumint not null auto_increment primary key,
	name varchar (56) binary  not null,
);

create table signoff_servereng (
	id mediumint not null auto_increment primary key,
	name varchar (56) binary  not null,
);

create table signoff_dbeng (
	id mediumint not null auto_increment primary key,
	name varchar (56) binary  not null,
);

create table signoff_cc (
	id mediumint not null auto_increment primary key,
	name varchar (56) binary  not null,
);

create table signoff_report (
	id mediumint not null auto_increment primary key,
	name varchar (56) binary  not null,
);

create table signoff_legal (
	id mediumint not null auto_increment primary key,
	name varchar (56) binary  not null,
);

create table signoff_market (
	id mediumint not null auto_increment primary key,
	name varchar (56) binary  not null,
);

create table signoff_qa (
	id mediumint not null auto_increment primary key,
	name varchar (56) binary  not null,
);

create table project_type (
	id mediumint not null auto_increment primary key,
	name varchar (56) binary  not null,
	index(id)
);

create table project_status (
	id mediumint not null auto_increment primary key,
	name varchar (56) binary  not null,
	index(id)
);

create table project_phase (
	id mediumint not null auto_increment primary key,
	name varchar (56) binary  not null,
	index(id)
);

create table project_priority (
	id mediumint not null auto_increment primary key,
	name varchar (56) binary  not null,
	index(id)
);

create table tags (
	id mediumint not null auto_increment primary key,
	tag varchar (56) binary not null,
	user_name varchar (56) binary NOT NULL default 'none',
	status ENUM('stage','www','archived') NOT NULL DEFAULT 'stage',
	module ENUM('full','webdev_www','dal','webdev_src') NOT NULL DEFAULT 'webdev_www',
	index(id)
);

create table releases (
	id mediumint not null auto_increment primary key,
	release_name varchar (56) binary not null,
	tag_id MEDIUMINT(9) default NULL,
	target_date varchar (56) binary not null,
	release_date varchar (56) binary default null,
	locked ENUM('locked','unlocked') NOT NULL DEFAULT 'unlocked',
	index(id)
);

create table qa_history (
	id mediumint not null auto_increment primary key,
	release_id MEDIUMINT(9) NOT NULL,
	date varchar (56) binary not null,
	features_passed varchar (56) binary not null,
	features_failed varchar (56) binary not null,
	index(id),
	index(release_id)
);

create table stress_test (
	id mediumint not null auto_increment primary key,
	release_id MEDIUMINT(9) NOT NULL,
	date varchar (56) binary not null,
	test_type ENUM('rad_logger','logplayer','php_timing') NOT NULL, 
	log varchar (56) binary not null,
	server_set ENUM('stage','www') NOT NULL DEFAULT 'stage', 
	dal_server ENUM('stagedal1','stagedal2','NA') NOT NULL DEFAULT 'NA', 
	num_clients MEDIUMINT(9) NOT NULL, 
	num_requests MEDIUMINT(9) NOT NULL, 
	new_data ENUM('true','false') NOT NULL DEFAULT 'false',
	clear_dal_cache ENUM('true','false') NOT NULL DEFAULT 'true',
	clear_web_cache ENUM('true','false') NOT NULL DEFAULT 'true',
	notes BLOB DEFAULT NULL,
	index(id),
	index(release_id)
);

create table entity_times (
	id MEDIUMINT(9) NOT NULL,
	stress_test_id MEDIUMINT(9) NOT NULL,
	total_calls MEDIUMINT(9),
	total_sec DECIMAL(9,6),
	avg_sec DECIMAL(9,6),
	max_sec DECIMAL(9,6),
	min_sec DECIMAL(9,6),
	first_sec DECIMAL(9,6),
	avg_edb_size MEDIUMINT(9),
	min_edb_size MEDIUMINT(9),
	max_edb_size MEDIUMINT(9),
	avg_xml_size MEDIUMINT(9),
	min_xml_size MEDIUMINT(9),
	max_xml_size MEDIUMINT(9),
	edb_total_time DECIMAL(9,6),
	edb_first_time DECIMAL(9,6),
	edb_avg_time DECIMAL(9,6),
	edb_total_calls DECIMAL(9,6),
	index(id),
	index (stress_test_id)
);

create table entity_name (
	id mediumint not null auto_increment primary key,
	name varchar (56) binary not null,
	index(id)
);

create table months (
	id mediumint not null auto_increment primary key,
	name varchar (56) binary not null,
	max_days MEDIUMINT(9),
	index(id)
);

create table days (
	id mediumint not null auto_increment primary key,
	name varchar (56) binary not null,
	index(id)
);

create table years (
	id mediumint not null auto_increment primary key,
	name varchar (56) binary not null,
	index(id)
);

OK_ALL_DONE