Sharinf_db.sql

-- MySQL Script generated by MySQL Workbench
-- 12/14/15 20:39:50
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

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,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `Sharinf_db` DEFAULT CHARACTER SET utf8 ;
USE `Sharinf_db` ;

-- -----------------------------------------------------
-- Table `Sharinf_db`.`Users`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Sharinf_db`.`Users` ;

CREATE TABLE IF NOT EXISTS `Sharinf_db`.`Users` (
  `userMail` varchar(50) NOT NULL,
  PRIMARY KEY (`userMail`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Sharinf_db`.`Localization`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Sharinf_db`.`Localization` ;

CREATE TABLE IF NOT EXISTS `Sharinf_db`.`Localization` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) NOT NULL,
  `major` INT NOT NULL,
  `minor` INT NOT NULL,
  `lvirtual` INT NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Directories`
-- -----------------------------------------------------
#DROP TABLE IF EXISTS `Sharinf_db`.`Directories` ;
#CREATE TABLE IF NOT EXISTS `Sharinf_db`.`Directories` (
#  `idDirectories` INT NOT NULL AUTO_INCREMENT,
#  PRIMARY KEY (`idDirectories`))
#ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Session`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Sharinf_db`.`Sessions` ;
CREATE TABLE IF NOT EXISTS `Sharinf_db`.`Sessions` (
  `idSession` INT NOT NULL AUTO_INCREMENT,
  `s_begin` DATETIME NOT NULL,
  `s_end` DATETIME NULL,
  `activeFlag` BOOLEAN NOT NULL,
  `localizationID` INT NOT NULL,
  `s_type` INT NOT NULL,
  `permissions` INT NOT NULL,
  `s_name` VARCHAR(20),
  PRIMARY KEY (`idSession`),
  INDEX `Localization_idx` (`localizationID` ASC),
  CONSTRAINT `localization_ID`
    FOREIGN KEY (`localizationID`)
    REFERENCES `Sharinf_db`.`Localization` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Sharinf_db`.`SessionUserList`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Sharinf_db`.`SessionUserList` ;
CREATE TABLE IF NOT EXISTS `Sharinf_db`.`SessionUserList` (
  `sessionID` INT NOT NULL,
  `user` varchar(50) NOT NULL,
  `adminFlag` TINYINT(1) NOT NULL,
  INDEX `sessionID_idx` (`sessionID` ASC),
  PRIMARY KEY (`sessionID`, `user`),
  CONSTRAINT `userMail`
    FOREIGN KEY (`user`)
    REFERENCES `Sharinf_db`.`Users` (`userMail`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `sessionID`
    FOREIGN KEY (`sessionID`)
    REFERENCES `Sharinf_db`.`Sessions` (`idSession`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Sharinf_db`.`Files`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Sharinf_db`.`Files` ;
CREATE TABLE IF NOT EXISTS `Sharinf_db`.`Files` (
  `Owner` varchar(50) NOT NULL,
  `URL` varchar(150) NOT NULL,
  `Name` varchar(150) NOT NULL,
  PRIMARY KEY (`URL`),
  INDEX `Owner_idx` (`Owner` ASC),
  CONSTRAINT `Owner`
    FOREIGN KEY (`Owner`)
    REFERENCES `Sharinf_db`.`Users` (`userMail`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Sharinf_db`.`DirectoryFileList`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Sharinf_db`.`SessionFileList` ;
CREATE TABLE IF NOT EXISTS `Sharinf_db`.`SessionFileList` (
  `sessionID` INT NOT NULL,
  `fileURL` VARCHAR(150) NOT NULL,
  PRIMARY KEY (`sessionID`, `fileURL`),
  INDEX `fileID_idx` (`fileURL` ASC),
  CONSTRAINT `file_URL`
    FOREIGN KEY (`fileURL`)
    REFERENCES `Sharinf_db`.`Files` (`URL`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `session_ID`
    FOREIGN KEY (`sessionID`)
    REFERENCES `Sharinf_db`.`Sessions` (`idSession`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Sharinf_db`.`LocationUserList`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Sharinf_db`.`LocationUserList` ;
CREATE TABLE IF NOT EXISTS `Sharinf_db`.`LocationUserList` (
  `userMail` varchar(50) NOT NULL,
  `locationId` INT NOT NULL,
  PRIMARY KEY (`userMail`, `locationId`),
  INDEX `location_idx` (`locationId` ASC),
  CONSTRAINT `user`
    FOREIGN KEY (`userMail`)
    REFERENCES `Sharinf_db`.`Users` (`userMail`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `location`
    FOREIGN KEY (`locationId`)
    REFERENCES `Sharinf_db`.`Localization` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

DROP VIEW IF EXISTS LocationSession;
CREATE VIEW LocationSession AS SELECT s.idSession AS SessionID, l.id AS localID, s.activeFlag AS Flag FROM Localization l, Sessions s WHERE l.id = s.localizationID;


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