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


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

create table users (
    id mediumint not null auto_increment primary key,
    userid varchar (56) binary  not null,
    type   tinyint ,
    index(id)
);

create table filepaths (
	id mediumint not null auto_increment primary key,
	path varchar (254) binary  not null,
	cvsversion VARCHAR(12) BINARY NOT NULL,
	stageversion VARCHAR(12) BINARY,
	wwwversion VARCHAR(12) BINARY,
	locked VARCHAR(12) BINARY DEFAULT NULL,
	watch VARCHAR(254) BINARY DEFAULT NULL,
	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('once','every','cart','push') NOT NULL DEFAULT 'once',
	dest ENUM('stage','www','delete') NOT NULL DEFAULT 'stage',
	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 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,
	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)
);

OK_ALL_DONE