db/devghai.com_01_createdb_vroom360.sql (e36c40f)

devghai.com_01_createdb_vroom360.sql

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `devghai_vroom360` DEFAULT CHARACTER SET latin1 ;
USE `devghai_vroom360` ;

-- -----------------------------------------------------
-- Table `devghai_vroom360`.`ages`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `devghai_vroom360`.`ages` ;

CREATE  TABLE IF NOT EXISTS `devghai_vroom360`.`ages` (
  `ages_id` INT NOT NULL AUTO_INCREMENT ,
  `ages_from` INT NULL ,
  `ages_to` INT NULL ,
  PRIMARY KEY (`ages_id`) )
ENGINE = InnoDB,
COMMENT = 'Stores various age ranges available to a user to select from' /* comment truncated */ ;

-- -----------------------------------------------------
-- Table `devghai_vroom360`.`weights`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `devghai_vroom360`.`weights` ;

CREATE  TABLE IF NOT EXISTS `devghai_vroom360`.`weights` (
  `weights_id` INT NOT NULL AUTO_INCREMENT ,
  `weights_from` INT NOT NULL ,
  `weights_to` INT NOT NULL ,
  PRIMARY KEY (`weights_id`) )
ENGINE = InnoDB,
COMMENT = 'Stores various weight ranges available to a user to select f' /* comment truncated */ ;

-- -----------------------------------------------------
-- Table `devghai_vroom360`.`users`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `devghai_vroom360`.`users` ;

CREATE  TABLE IF NOT EXISTS `devghai_vroom360`.`users` (
  `users_loginId` VARCHAR(20) CHARACTER SET 'latin1' COLLATE 'latin1_general_cs' NOT NULL ,
  `users_password` VARCHAR(45) NOT NULL ,
  `users_ageid` INT NULL ,
  `users_gender` ENUM('Male', 'Female') NULL ,
  `users_height` INT NULL ,
  `users_shoesize` FLOAT NULL ,
  `users_weightid` INT NULL ,
  `users_zip` INT NULL ,
  `users_shirtsize` VARCHAR(4) NULL ,
  `users_email` VARCHAR(100) NOT NULL ,
  `user_sittingheight` FLOAT NULL COMMENT 'inches' ,
  `user_upperleglength` FLOAT NULL COMMENT 'inches' ,
  PRIMARY KEY (`users_loginId`) ,
  UNIQUE INDEX `users_loginId_UNIQUE` (`users_loginId` ASC) ,
  INDEX `fk_users_age_ages_id` (`users_ageid` ASC) ,
  INDEX `fk_users_weight_weights_id` (`users_weightid` ASC) ,
  CONSTRAINT `fk_users_age_ages_id`
    FOREIGN KEY (`users_ageid` )
    REFERENCES `devghai_vroom360`.`ages` (`ages_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_users_weight_weights_id`
    FOREIGN KEY (`users_weightid` )
    REFERENCES `devghai_vroom360`.`weights` (`weights_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'Contains all information about the users of the system.' ;

-- -----------------------------------------------------
-- Table `devghai_vroom360`.`questions`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `devghai_vroom360`.`questions` ;

CREATE  TABLE IF NOT EXISTS `devghai_vroom360`.`questions` (
  `questions_id` INT NOT NULL AUTO_INCREMENT ,
  `questions_text` VARCHAR(250) NOT NULL ,
  `questions_ans_value_type` ENUM('Integer', 'Decimal') NOT NULL DEFAULT 'Integer' ,
  `questions_ans_direction` ENUM('LowerIsBetter', 'HigherIsBetter') NOT NULL DEFAULT 'HigherIsBetter' ,
  `questions_location_x` FLOAT NOT NULL ,
  `questions_location_y` FLOAT NOT NULL ,
  `questions_location_z` FLOAT NOT NULL ,
  `questions_active` TINYINT(1)  NOT NULL DEFAULT true ,
  PRIMARY KEY (`questions_id`) )
ENGINE = InnoDB,
COMMENT = 'This table will store all questions that can be given in the' /* comment truncated */ ;

-- -----------------------------------------------------
-- Table `devghai_vroom360`.`vehicle`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `devghai_vroom360`.`vehicle` ;

CREATE  TABLE IF NOT EXISTS `devghai_vroom360`.`vehicle` (
  `vehicle_id` INT NOT NULL AUTO_INCREMENT ,
  `vehicle_make` VARCHAR(45) NOT NULL COMMENT 'Company name to which the vehicle belongs' ,
  `vehicle_model` VARCHAR(45) NOT NULL ,
  `vehicle_year` INT NOT NULL ,
  PRIMARY KEY (`vehicle_id`) )
ENGINE = InnoDB,
COMMENT = 'Master table of vehicles.' ;

-- -----------------------------------------------------
-- Table `devghai_vroom360`.`admins`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `devghai_vroom360`.`admins` ;

CREATE  TABLE IF NOT EXISTS `devghai_vroom360`.`admins` (
  `admins_id` VARCHAR(20) CHARACTER SET 'latin1' COLLATE 'latin1_general_cs' NOT NULL ,
  `admins_password` VARCHAR(45) NULL ,
  `admins_email` VARCHAR(90) NULL ,
  `admins_active` TINYINT(1)  NULL DEFAULT false ,
  `admins_modified_by` VARCHAR(20) NOT NULL ,
  `admins_modification_date` DATETIME NOT NULL DEFAULT '1969-12-31 19:00:00' ,
  PRIMARY KEY (`admins_id`) )
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `devghai_vroom360`.`question_set`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `devghai_vroom360`.`question_set` ;

CREATE  TABLE IF NOT EXISTS `devghai_vroom360`.`question_set` (
  `questionset_id` INT NOT NULL AUTO_INCREMENT ,
  `questionset_setname` VARCHAR(100) NOT NULL ,
  `questionset_active` TINYINT(1)  NOT NULL DEFAULT true COMMENT 'Tells if the survey is active or not.' ,
  `questionset_modified_by` VARCHAR(20) CHARACTER SET 'latin1' COLLATE 'latin1_general_cs' NOT NULL ,
  `questionset_modification_date` DATETIME NOT NULL DEFAULT '1969-12-31 19:00:00' ,
  PRIMARY KEY (`questionset_id`) ,
  UNIQUE INDEX `qs_setname_UNIQUE` (`questionset_setname` ASC) ,
  INDEX `fk_questionset_modified_by_admins_id` (`questionset_modified_by` ASC) ,
  CONSTRAINT `fk_questionset_modified_by_admins_id`
    FOREIGN KEY (`questionset_modified_by` )
    REFERENCES `devghai_vroom360`.`admins` (`admins_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB,
COMMENT = 'This table allows active questions in the question table to ' /* comment truncated */ ;

-- -----------------------------------------------------
-- Table `devghai_vroom360`.`survey`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `devghai_vroom360`.`survey` ;

CREATE  TABLE IF NOT EXISTS `devghai_vroom360`.`survey` (
  `survey_id` INT NOT NULL AUTO_INCREMENT ,
  `survey_vehicleid` INT NOT NULL ,
  `survey_start_date` DATETIME NOT NULL ,
  `survey_end_date` DATETIME NULL COMMENT 'Missing end date will mean that the survey is in progress.' ,
  `survey_user_id` VARCHAR(20) CHARACTER SET 'latin1' COLLATE 'latin1_general_cs' NOT NULL ,
  `survey_question_set` INT NOT NULL ,
  PRIMARY KEY (`survey_id`) ,
  INDEX `fk_survey_vehicle_vehicle_id` (`survey_vehicleid` ASC) ,
  INDEX `fk_survey_question_set_qs_id` (`survey_question_set` ASC) ,
  INDEX `fk_survey_user_id_users_loginId` (`survey_user_id` ASC) ,
  CONSTRAINT `fk_survey_vehicle_vehicle_id`
    FOREIGN KEY (`survey_vehicleid` )
    REFERENCES `devghai_vroom360`.`vehicle` (`vehicle_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_survey_question_set_qs_id`
    FOREIGN KEY (`survey_question_set` )
    REFERENCES `devghai_vroom360`.`question_set` (`questionset_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_survey_user_id_users_loginId`
    FOREIGN KEY (`survey_user_id` )
    REFERENCES `devghai_vroom360`.`users` (`users_loginId` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB,
COMMENT = 'Table that will maintain state of the survey. In case this t' /* comment truncated */ ;

-- -----------------------------------------------------
-- Table `devghai_vroom360`.`answers`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `devghai_vroom360`.`answers` ;

CREATE  TABLE IF NOT EXISTS `devghai_vroom360`.`answers` (
  `answers_id` INT NOT NULL AUTO_INCREMENT ,
  `answers_ques_id` INT NOT NULL ,
  `answers_value` FLOAT NOT NULL ,
  `answers_attachment_path` VARCHAR(150) NULL ,
  `answers_survey_id` INT NOT NULL ,
  `answers_date` DATETIME NOT NULL ,
  `answers_sequence` INT NOT NULL ,
  `answers_comment` VARCHAR(400) NULL ,
  PRIMARY KEY (`answers_id`) ,
  INDEX `fk_answers_ques_id_questions_id` (`answers_ques_id` ASC) ,
  INDEX `fk_answers_survey_id_survey_id` (`answers_survey_id` ASC) ,
  CONSTRAINT `fk_answers_ques_id_questions_id`
    FOREIGN KEY (`answers_ques_id` )
    REFERENCES `devghai_vroom360`.`questions` (`questions_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_answers_survey_id_survey_id`
    FOREIGN KEY (`answers_survey_id` )
    REFERENCES `devghai_vroom360`.`survey` (`survey_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `devghai_vroom360`.`questions_in_set`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `devghai_vroom360`.`questions_in_set` ;

CREATE  TABLE IF NOT EXISTS `devghai_vroom360`.`questions_in_set` (
  `questionsinset_set_id` INT NOT NULL ,
  `questionsinset_question_id` INT NOT NULL ,
  INDEX `fk_qis_set_id_qs_id` (`questionsinset_set_id` ASC) ,
  INDEX `fk_qis_question_id_questions_id` (`questionsinset_question_id` ASC) ,
  CONSTRAINT `fk_qis_set_id_qs_id`
    FOREIGN KEY (`questionsinset_set_id` )
    REFERENCES `devghai_vroom360`.`question_set` (`questionset_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_qis_question_id_questions_id`
    FOREIGN KEY (`questionsinset_question_id` )
    REFERENCES `devghai_vroom360`.`questions` (`questions_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `devghai_vroom360`.`badges`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `devghai_vroom360`.`badges` ;

CREATE  TABLE IF NOT EXISTS `devghai_vroom360`.`badges` (
  `badges_id` INT NOT NULL AUTO_INCREMENT ,
  `badges_description` VARCHAR(100) NOT NULL ,
  `badges_weight` INT NOT NULL ,
  PRIMARY KEY (`badges_id`) )
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `devghai_vroom360`.`user_badges`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `devghai_vroom360`.`user_badges` ;

CREATE  TABLE IF NOT EXISTS `devghai_vroom360`.`user_badges` (
  `userbadges_badgeid` INT NOT NULL ,
  `userbadges_userid` VARCHAR(20) CHARACTER SET 'latin1' COLLATE 'latin1_general_cs' NOT NULL ,
  INDEX `fk_userbadges_badgeid_badges_id` (`userbadges_badgeid` ASC) ,
  INDEX `fk_userbadges_userid_users_loginId` (`userbadges_userid` ASC) ,
  CONSTRAINT `fk_userbadges_badgeid_badges_id`
    FOREIGN KEY (`userbadges_badgeid` )
    REFERENCES `devghai_vroom360`.`badges` (`badges_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_userbadges_userid_users_loginId`
    FOREIGN KEY (`userbadges_userid` )
    REFERENCES `devghai_vroom360`.`users` (`users_loginId` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `devghai_vroom360`.`errorlog`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `devghai_vroom360`.`errorlog` ;

CREATE  TABLE IF NOT EXISTS `devghai_vroom360`.`errorlog` (
  `errorlog_id` INT NOT NULL ,
  `errorlog_datetime` DATETIME NOT NULL ,
  `errorlog_object` VARCHAR(2000) NOT NULL ,
  PRIMARY KEY (`errorlog_id`) )
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- --------------------------------------------------
-- USERS
-- --------------------------------------------------
-- Create user with qualified domain and ip address. Not using wildcards.
CREATE USER 'webuser'@'127.0.0.1' IDENTIFIED BY PASSWORD '*C5DF9C11FDCEA187921EEB5DDDFF0C32FE9CB352';
CREATE USER 'webuser'@'localhost' IDENTIFIED BY PASSWORD '*C5DF9C11FDCEA187921EEB5DDDFF0C32FE9CB352';

-- http://dev.mysql.com/doc/refman/5.6/en/grant.html#grant-privileges
GRANT SELECT, INSERT, UPDATE, EXECUTE ON vroom360.* TO 'webuser'@'127.0.0.1';
GRANT SELECT, INSERT, UPDATE, EXECUTE ON vroom360.* TO 'webuser'@'localhost';
-------------------------------------------

vroom360

initial commit