Description:
When trying to Forward Engineer a database containing a view, the generated SQL file does not contain the appropriate code to do so. It initially creates a 'Placeholder table for view" but then subsequently deletes the table.
How to repeat:
Create two tables, min had a foreign key attaching them. Then create a View that pulls data from both tables. Forward Engineer SQL CREATE Script and then view the SQL file generated. Here is mine.
/////////////////////////////////////////////////////////
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 DATABASE IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 ;
USE `mydb`;
-- -----------------------------------------------------
-- Table `mydb`.`test1`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`test1` (
`test_key` INT NOT NULL ,
`test1_value` VARCHAR(45) NULL ,
`test_table2_idtest_table2` INT NULL ,
PRIMARY KEY (`test_key`) ,
INDEX fk_test1_test_table2 (`test_table2_idtest_table2` ASC) ,
CONSTRAINT `fk_test1_test_table2`
FOREIGN KEY (`test_table2_idtest_table2` )
REFERENCES `mydb`.`test_table2` (`idtest_table2` )
ON DELETE NO ACTION
ON UPDATE NO ACTION);
-- -----------------------------------------------------
-- Table `mydb`.`test_table2`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`test_table2` (
`idtest_table2` INT NOT NULL ,
`test_table2_value` VARCHAR(45) NULL ,
PRIMARY KEY (`idtest_table2`) );
-- -----------------------------------------------------
-- Placeholder table for view `mydb`.`view1`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`view1` (`id` INT);
-- -----------------------------------------------------
-- View `mydb`.`view1`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`view1`;
SELECT t1.test1_value, t2.test_table2_value FROM test1 t1, test_table2 t2, WHERE t1.test_table2_idtest_table2 = t2.idtest_table2;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Description: When trying to Forward Engineer a database containing a view, the generated SQL file does not contain the appropriate code to do so. It initially creates a 'Placeholder table for view" but then subsequently deletes the table. How to repeat: Create two tables, min had a foreign key attaching them. Then create a View that pulls data from both tables. Forward Engineer SQL CREATE Script and then view the SQL file generated. Here is mine. ///////////////////////////////////////////////////////// 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 DATABASE IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 ; USE `mydb`; -- ----------------------------------------------------- -- Table `mydb`.`test1` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`test1` ( `test_key` INT NOT NULL , `test1_value` VARCHAR(45) NULL , `test_table2_idtest_table2` INT NULL , PRIMARY KEY (`test_key`) , INDEX fk_test1_test_table2 (`test_table2_idtest_table2` ASC) , CONSTRAINT `fk_test1_test_table2` FOREIGN KEY (`test_table2_idtest_table2` ) REFERENCES `mydb`.`test_table2` (`idtest_table2` ) ON DELETE NO ACTION ON UPDATE NO ACTION); -- ----------------------------------------------------- -- Table `mydb`.`test_table2` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`test_table2` ( `idtest_table2` INT NOT NULL , `test_table2_value` VARCHAR(45) NULL , PRIMARY KEY (`idtest_table2`) ); -- ----------------------------------------------------- -- Placeholder table for view `mydb`.`view1` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`view1` (`id` INT); -- ----------------------------------------------------- -- View `mydb`.`view1` -- ----------------------------------------------------- DROP TABLE IF EXISTS `mydb`.`view1`; SELECT t1.test1_value, t2.test_table2_value FROM test1 t1, test_table2 t2, WHERE t1.test_table2_idtest_table2 = t2.idtest_table2; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;