Re: Some help with schema

[Sean Funk]

Here is the sql create script

====

-- MySQL Script generated by MySQL Workbench
-- Mon Jun 30 10:54:32 2014
-- Model: New Model Version: 1.0
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 a0
-- -----------------------------------------------------
-- update 06/14/14
DROP SCHEMA IF EXISTS `a0` ;
CREATE SCHEMA IF NOT EXISTS `a0` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
SHOW WARNINGS;
USE `a0` ;

-- -----------------------------------------------------
-- Table `a0`.`a0`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `a0`.`a0` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `a0`.`a0` (
`postID` INT NOT NULL,
`postLocationLong` TEXT NOT NULL,
`postLocationShort` TINYTEXT NULL,
`postTitle` TEXT NOT NULL,
`postParentID` INT NULL,
`postBody` LONGTEXT NULL,
`postHandle` TEXT NOT NULL,
`postImages` INT NULL,
`postPosPoints` INT NULL,
`postNegPoints` INT NULL,
`postDateTime` DATETIME NOT NULL,
`postLinks` TEXT NULL,
PRIMARY KEY (`postID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8 PARTITION BY HASH() PARTITIONS 101;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `a0`.`b0`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `a0`.`b0` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `a0`.`b0` (
`imageId` INT NOT NULL AUTO_INCREMENT,
`imagehash` CHAR(32) NOT NULL,
`imageblob` BLOB NOT NULL,
`timestamp` DATETIME NOT NULL,
PRIMARY KEY (`imageId`),
CONSTRAINT `imageId`
FOREIGN KEY (`imageId`)
REFERENCES `a0`.`a0` (`postID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

SHOW WARNINGS;
SET SQL_MODE = '';
GRANT USAGE ON *.* TO cldb_serviceuser;
DROP USER serviceuser;
SET SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
SHOW WARNINGS;
CREATE USER 'cldb_serviceuser' IDENTIFIED BY 'Ia902j816skw49JVUGp9tO@AVoxh2*6J';

GRANT SELECT, INSERT, TRIGGER ON TABLE `a0`.* TO 'cldb_serviceuser';
GRANT SELECT ON TABLE `a0`.* TO 'cldb_serviceuser';
SHOW WARNINGS;

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

USE `a0`;

DELIMITER $$

USE `a0`$$
DROP TRIGGER IF EXISTS `a0`.`imageData_BINS` $$
SHOW WARNINGS$$
USE `a0`$$
CREATE TRIGGER `imageData_BINS` BEFORE INSERT ON `imageData` FOR EACH ROW

SET NEW.imagehash = md5(NEW.imageblob);$$

SHOW WARNINGS$$

USE `a0`$$
DROP TRIGGER IF EXISTS `a0`.`imageData_BUPD` $$
SHOW WARNINGS$$
USE `a0`$$
CREATE TRIGGER `imageData_BUPD` BEFORE UPDATE ON `imageData` FOR EACH ROW

SET NEW.imagehash = md5(NEW.imageblob);$$

SHOW WARNINGS$$

DELIMITER ;