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

DROP SCHEMA IF EXISTS `packeis2` ;
CREATE SCHEMA IF NOT EXISTS `packeis2` ;

-- -----------------------------------------------------
-- Table `packeis2`.`tree_entry_type`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `packeis2`.`tree_entry_type` ;

CREATE  TABLE IF NOT EXISTS `packeis2`.`tree_entry_type` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `version` INT NULL COMMENT 'For hibernate optimistic lock mechanics' ,
  `name` VARCHAR(45) NULL ,
  `allowChildrens` TINYINT(1) NOT NULL DEFAULT 0 ,
  `editable` TINYINT(1) NOT NULL DEFAULT 0 ,
  `draggable` TINYINT(1) NOT NULL DEFAULT 0 ,
  `dropable` TINYINT(1) NOT NULL DEFAULT 0 ,
  `translatable` TINYINT(1) NOT NULL DEFAULT 0 ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `packeis2`.`tree_entry`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `packeis2`.`tree_entry` ;

CREATE  TABLE IF NOT EXISTS `packeis2`.`tree_entry` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `version` INT NULL COMMENT 'For hibernate optimistic lock mechanics' ,
  `position` INT NOT NULL DEFAULT 0 ,
  `name` VARCHAR(55) NOT NULL COMMENT 'has lucene index' ,
  `description` VARCHAR(255) NOT NULL COMMENT 'has lucene index' ,
  `enabled` TINYINT(1) NOT NULL DEFAULT 1 ,
  `parameter` VARCHAR(255) NULL ,
  `tree_entry_idfk` INT NULL ,
  `tree_entry_type_idfk` INT NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `tree_entryFKtree_entry` (`tree_entry_idfk` ASC) ,
  INDEX `tree_entryFKtree_entry_type` (`tree_entry_type_idfk` ASC) ,
  CONSTRAINT `tree_entryFKtree_entry`
    FOREIGN KEY (`tree_entry_idfk` )
    REFERENCES `packeis2`.`tree_entry` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `tree_entryFKtree_entry_type`
    FOREIGN KEY (`tree_entry_type_idfk` )
    REFERENCES `packeis2`.`tree_entry_type` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = '';


-- -----------------------------------------------------
-- Table `packeis2`.`permission_group`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `packeis2`.`permission_group` ;

CREATE  TABLE IF NOT EXISTS `packeis2`.`permission_group` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `version` INT NULL COMMENT 'For hibernate optimistic lock mechanics' ,
  `name` VARCHAR(45) NOT NULL ,
  `description` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
COMMENT = '';


-- -----------------------------------------------------
-- Table `packeis2`.`user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `packeis2`.`user` ;

CREATE  TABLE IF NOT EXISTS `packeis2`.`user` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `version` INT NULL COMMENT 'For hibernate optimistic lock mechanics' ,
  `login` VARCHAR(45) NOT NULL ,
  `passwd` VARCHAR(255) NOT NULL ,
  `firstname` VARCHAR(45) NOT NULL ,
  `lastname` VARCHAR(45) NOT NULL ,
  `email` VARCHAR(45) NOT NULL ,
  `active` TINYINT(1) NOT NULL DEFAULT 0 ,
  `registered` TINYINT(1) NOT NULL DEFAULT 0 ,
  `homepage` VARCHAR(45) NULL ,
  `jabber` VARCHAR(45) NULL ,
  `skype` VARCHAR(45) NULL ,
  `icq` VARCHAR(45) NULL ,
  `pager` VARCHAR(45) NULL ,
  `comment` TEXT NULL ,
  `permission_group_idfk` INT NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `userFKpermission_group` (`permission_group_idfk` ASC) ,
  CONSTRAINT `userFKpermission_group`
    FOREIGN KEY (`permission_group_idfk` )
    REFERENCES `packeis2`.`permission_group` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = '';


-- -----------------------------------------------------
-- Table `packeis2`.`package_info`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `packeis2`.`package_info` ;

CREATE  TABLE IF NOT EXISTS `packeis2`.`package_info` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `version` INT NULL COMMENT 'For hibernate optimistic lock mechanics' ,
  `name` VARCHAR(45) NOT NULL ,
  `short_description` VARCHAR(60) NOT NULL COMMENT 'has lucene index' ,
  `author` VARCHAR(45) NOT NULL COMMENT 'has lucene index' ,
  `package_version` VARCHAR(45) NOT NULL COMMENT 'has lucene index' ,
  `date` DATE NOT NULL COMMENT 'has lucene index' ,
  `state` ENUM('testing','stable') NOT NULL COMMENT 'has lucene index' ,
  `sha1sum` VARCHAR(45) NULL ,
  `required_space` VARCHAR(45) NULL ,
  `description` TEXT NULL ,
  `filesize` INT NOT NULL ,
  `official` TINYINT(1) NOT NULL DEFAULT 0 ,
  `active` TINYINT(1) NOT NULL DEFAULT 0 ,
  `marked_as_deleted` TINYINT(1) NOT NULL DEFAULT 0 ,
  `on_cd` TINYINT(1) NOT NULL DEFAULT 0 ,
  `packeis_state` VARCHAR(45) NOT NULL DEFAULT '' ,
  `tree_entry_idfk` INT NOT NULL COMMENT 'Section ID corresponds to a tree entry' ,
  `owner_user_idfk` INT NOT NULL ,
  `checker_user_idfk` INT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `packageFKtree_entry` (`tree_entry_idfk` ASC) ,
  INDEX `packageFKowner_user` (`owner_user_idfk` ASC) ,
  INDEX `packageFKchecker_user` (`checker_user_idfk` ASC) ,
  CONSTRAINT `packageFKtree_entry`
    FOREIGN KEY (`tree_entry_idfk` )
    REFERENCES `packeis2`.`tree_entry` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `packageFKowner_user`
    FOREIGN KEY (`owner_user_idfk` )
    REFERENCES `packeis2`.`user` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `packageFKchecker_user`
    FOREIGN KEY (`checker_user_idfk` )
    REFERENCES `packeis2`.`user` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = '';


-- -----------------------------------------------------
-- Table `packeis2`.`system_tag`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `packeis2`.`system_tag` ;

CREATE  TABLE IF NOT EXISTS `packeis2`.`system_tag` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `version` INT NULL COMMENT 'For hibernate optimistic lock mechanics' ,
  `systemtag` VARCHAR(20) NOT NULL ,
  `description` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
COMMENT = '';


-- -----------------------------------------------------
-- Table `packeis2`.`system_tag_package_info_link`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `packeis2`.`system_tag_package_info_link` ;

CREATE  TABLE IF NOT EXISTS `packeis2`.`system_tag_package_info_link` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `version` INT NULL COMMENT 'For hibernate optimistic lock mechanics' ,
  `package_info_idfk` INT NOT NULL ,
  `systemtag_idfk` INT NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `system_tag_package_info_linkFKpackage_info` (`package_info_idfk` ASC) ,
  INDEX `system_tag_package_info_linkFKsystem_tag` (`systemtag_idfk` ASC) ,
  CONSTRAINT `system_tag_package_info_linkFKpackage_info`
    FOREIGN KEY (`package_info_idfk` )
    REFERENCES `packeis2`.`package_info` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `system_tag_package_info_linkFKsystem_tag`
    FOREIGN KEY (`systemtag_idfk` )
    REFERENCES `packeis2`.`system_tag` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = '';


-- -----------------------------------------------------
-- Table `packeis2`.`file`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `packeis2`.`file` ;

CREATE  TABLE IF NOT EXISTS `packeis2`.`file` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `version` INT NULL COMMENT 'For hibernate optimistic lock mechanics' ,
  `type` ENUM('-','d','l') NOT NULL COMMENT '\"-\" File\n\"d\" Directory\n\"l\" Link' ,
  `permission` INT(4) NOT NULL ,
  `owner` VARCHAR(42) NOT NULL ,
  `group` VARCHAR(42) NOT NULL ,
  `directory` VARCHAR(255) NOT NULL COMMENT 'Directory where this file is located' ,
  `name` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
COMMENT = '';


-- -----------------------------------------------------
-- Table `packeis2`.`package_info_dependencies`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `packeis2`.`package_info_dependencies` ;

CREATE  TABLE IF NOT EXISTS `packeis2`.`package_info_dependencies` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `version` INT NULL COMMENT 'For hibernate optimistic lock mechanics' ,
  `required_version_operator` ENUM('<','<=','=','>=','>') NOT NULL COMMENT 'Should the required version be lower, lower equal, equal, greater equal or greater than the version of the referenced dependency' ,
  `required_version` VARCHAR(45) NOT NULL ,
  `required_package_name` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
COMMENT = '';


-- -----------------------------------------------------
-- Table `packeis2`.`package_info_file_link`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `packeis2`.`package_info_file_link` ;

CREATE  TABLE IF NOT EXISTS `packeis2`.`package_info_file_link` (
  `id` INT NOT NULL ,
  `version` INT NULL COMMENT 'For hibernate optimistic lock mechanics' ,
  `package_info_idfk` INT NOT NULL ,
  `file_idfk` INT NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `package_info_file_linkFKfile` (`file_idfk` ASC) ,
  INDEX `package_info_file_linkFKpackage_info` (`package_info_idfk` ASC) ,
  CONSTRAINT `package_info_file_linkFKfile`
    FOREIGN KEY (`file_idfk` )
    REFERENCES `packeis2`.`file` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `package_info_file_linkFKpackage_info`
    FOREIGN KEY (`package_info_idfk` )
    REFERENCES `packeis2`.`package_info` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = '';


-- -----------------------------------------------------
-- Table `packeis2`.`eislist`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `packeis2`.`eislist` ;

CREATE  TABLE IF NOT EXISTS `packeis2`.`eislist` (
  `id` INT NOT NULL ,
  `version` INT NULL COMMENT 'For hibernate optimistic lock mechanics' ,
  `url` VARCHAR(45) NOT NULL ,
  `comment` VARCHAR(255) NULL ,
  `user_idfk` INT NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `eislistFKuser` (`user_idfk` ASC) ,
  CONSTRAINT `eislistFKuser`
    FOREIGN KEY (`user_idfk` )
    REFERENCES `packeis2`.`user` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = '';


-- -----------------------------------------------------
-- Table `packeis2`.`statistic`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `packeis2`.`statistic` ;

CREATE  TABLE IF NOT EXISTS `packeis2`.`statistic` (
  `id` INT NOT NULL ,
  `version` INT NULL COMMENT 'For hibernate optimistic lock mechanics' ,
  `year` YEAR NOT NULL ,
  `month` ENUM('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') NOT NULL ,
  `downloads` INT NOT NULL ,
  `package_info_idfk` INT NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `statisticFKpackage_info` (`package_info_idfk` ASC) ,
  CONSTRAINT `statisticFKpackage_info`
    FOREIGN KEY (`package_info_idfk` )
    REFERENCES `packeis2`.`package_info` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = '';


-- -----------------------------------------------------
-- Table `packeis2`.`tree_entry_type_permission_group_link`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `packeis2`.`tree_entry_type_permission_group_link` ;

CREATE  TABLE IF NOT EXISTS `packeis2`.`tree_entry_type_permission_group_link` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `version` INT NULL ,
  `tree_entry_type_idfk` INT NOT NULL ,
  `permission_group_idfk` INT NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `tree_entry_type_permission_group_linkFKtree_entry_type` (`tree_entry_type_idfk` ASC) ,
  INDEX `tree_entry_type_permission_group_linkFKpermission_group` (`permission_group_idfk` ASC) ,
  CONSTRAINT `tree_entry_type_permission_group_linkFKtree_entry_type`
    FOREIGN KEY (`tree_entry_type_idfk` )
    REFERENCES `packeis2`.`tree_entry_type` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `tree_entry_type_permission_group_linkFKpermission_group`
    FOREIGN KEY (`permission_group_idfk` )
    REFERENCES `packeis2`.`permission_group` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;



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