
-- -----------------------------------------------------
-- Table `User`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `User` (
  `idUser` INT NOT NULL AUTO_INCREMENT ,
  `email` TINYTEXT NOT NULL ,
  `password` CHAR(40) NOT NULL ,
  `salt` CHAR(23) NOT NULL ,
  `meta` TEXT NOT NULL ,
  `created` DATETIME NOT NULL ,
  `lastLogin` DATETIME NOT NULL ,
  `emailHash` CHAR(40) NOT  NULL
  PRIMARY KEY (`idUser`) )
ENGINE = InnoDB|

SHOW WARNINGS|

-- -----------------------------------------------------
-- Table `Channel`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `Channel` (
  `idChannel` INT NOT NULL AUTO_INCREMENT ,
  `url` VARCHAR(256) CHARACTER SET 'ascii' NOT NULL ,
  `title` TEXT NOT NULL ,  
  `lastChecked` DATETIME NOT NULL ,
  `nextCheck` DATETIME NOT NULL ,
  `meta` TEXT NOT NULL ,
  UNIQUE INDEX `url_UNIQUE` (`url` ASC) ,
  PRIMARY KEY (`idChannel`) )
ENGINE = InnoDB|

SHOW WARNINGS|

-- -----------------------------------------------------
-- Table `ChannelUser`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ChannelUser` (
  `channel` INT NOT NULL ,
  `user` INT NOT NULL ,
  `added` DATETIME NOT NULL ,
  PRIMARY KEY (`channel`, `user`) ,
  INDEX `fk_Channel2User_User1` (`user` ASC) ,
  INDEX `fk_Channel2User_Channel` (`channel` ASC) ,
  CONSTRAINT `fk_Channel2User_Channel`
    FOREIGN KEY (`channel` )
    REFERENCES `Channel` (`idChannel` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Channel2User_User1`
    FOREIGN KEY (`user` )
    REFERENCES `User` (`idUser` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB|

SHOW WARNINGS|

-- -----------------------------------------------------
-- Table `Item`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `Item` (
  `idItem` INT(11) NOT NULL AUTO INCREMENT,
  `channel` INT NOT NULL ,
  `title` TEXT NOT NULL ,
  `description` TEXT NOT NULL ,
  `link` VARCHAR(256) CHARACTER SET 'ascii' NULL ,
  `meta` TEXT NOT NULL ,
  PRIMARY KEY (`idItem`) ,
  INDEX `fk_Item_Channel1` (`channel` ASC) ,
  CONSTRAINT `fk_Item_Channel1`
    FOREIGN KEY (`channel` )
    REFERENCES `Channel` (`idChannel` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB|

SHOW WARNINGS|

-- -----------------------------------------------------
-- Table `ItemUser`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ItemUser` (
  `item` INT NOT NULL ,
  `user` INT NOT NULL ,
  `readed` DATETIME NOT NULL ,
  `meta` TEXT NOT NULL,
  PRIMARY KEY (`item`, `user`) ,
  INDEX `fk_Item2User_User1` (`user` ASC) ,
  INDEX `fk_Item2User_Item1` (`item` ASC) ,
  CONSTRAINT `fk_Item2User_Item1`
    FOREIGN KEY (`item` )
    REFERENCES `Item` (`idItem` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Item2User_User1`
    FOREIGN KEY (`user` )
    REFERENCES `User` (`idUser` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB|

SHOW WARNINGS|

CREATE  TABLE IF NOT EXISTS `Filter` (
  `idFilter` INT(11) NOT NULL ,
  `user` INT(11) NOT NULL ,
  `expression` TEXT NOT NULL ,
  `counter` INT(11) NOT NULL DEFAULT 0 ,
  PRIMARY KEY (`idFilter`) ,
  INDEX `fk_Filter_User1` (`user` ASC) ,
  CONSTRAINT `fk_Filter_User1`
    FOREIGN KEY (`user` )
    REFERENCES `User` (`idUser` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB |