Bug #33816 Forward Engineer SQL CREATE Script does not generate views.
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;
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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;