Error with constraint to other schema enum? about sqlacodegen

[agronholm]

Original comment by Alex Grönholm (Bitbucket: agronholm, GitHub: agronholm):

---

Fixed check constraint handling with alternate schemas (fixes #3)

[agronholm]

Original comment by YKdvd NA (Bitbucket: YKdvd, GitHub: YKdvd):

---

It doesn't seem to be the cross-schema bit as such, but something about (at least) an ENUM column in the FK target, even though that target is okay by itself. Here's a test case (the CREATE SCHEMA commands aren't complete):

#!SQL

CREATE SCHEMA IF NOT EXISTS TParent...
CREATE SCHEMA IF NOT EXISTS TChild...

CREATE TABLE `TParent`.`BadTarget` (
	`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	`userGroup` enum('Guest','Administrator','Modeler','LeadModeler','Layout','LeadLayout','Layout / Animator','Layout / LeadAnimator','Animator','LeadAnimator','Lighter','LeadLighter','LeadLighter / Compositor','LeadLighter / Compositor / Director','Compositor','Lighter / Compositor','LeadCompositor','LeadCompositor / Layout / Animator','Director','TechnicalDirector','TechnicalDirector / Lighter','TechnicalDirector / Lighter / Compositor') NOT NULL DEFAULT 'Guest',
	PRIMARY KEY (`id`)
) ENGINE=`InnoDB` AUTO_INCREMENT=1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT=COMPACT COMMENT='' CHECKSUM=0 DELAY_KEY_WRITE=0;

CREATE TABLE `TParent`.`GoodTarget` (
	`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	`dummy` varchar(255) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=`InnoDB` AUTO_INCREMENT=1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT=COMPACT COMMENT='' CHECKSUM=0 DELAY_KEY_WRITE=0;

CREATE TABLE `TChild`.`BadSource` (
	`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	`fkSource` int(11) UNSIGNED DEFAULT NULL,
	PRIMARY KEY (`id`),
	CONSTRAINT `fk_BadSource_BadTarget` FOREIGN KEY (`fkSource`) REFERENCES `TParent`.`BadTarget` (`id`)  ON UPDATE CASCADE,
	INDEX `idx_BadSource_fkSource` USING BTREE (fkSource)
) ENGINE=`InnoDB` AUTO_INCREMENT=1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT=COMPACT COMMENT='' CHECKSUM=0 DELAY_KEY_WRITE=0;

CREATE TABLE `TChild`.`GoodSource` (
	`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	`fkSource` int(10) UNSIGNED DEFAULT NULL,
	PRIMARY KEY (`id`),
	CONSTRAINT `fk_GoodSource_Goodarget` FOREIGN KEY (`fkSource`) REFERENCES `TParent`.`GoodTarget` (`id`)  ON UPDATE CASCADE,
	INDEX `idx_GoodSource_fkSource` USING BTREE (fkSource)
) ENGINE=`InnoDB` AUTO_INCREMENT=1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT=COMPACT COMMENT='' CHECKSUM=0 DELAY_KEY_WRITE=0;

The first run below works okay, and dumps out the Parent tables. The second one works, constraining a TChild table to a TParent table (that has no ENUM). The third one fails, doing the same sort of constraint but to a target table that does have an ENUM. I don't know if it is the length of the ENUM, or just its existence, but it may be a simple thing; I won't have time to dig into your code for awhile.

#!bash

sqlacodegen mysql://dbUser:dbPassword@dbHost/TParent
sqlacodegen  --tables GoodSource mysql://dbUser:dbPassword@dbHost/TChild
sqlacodegen  --tables BadSource mysql://dbUser:dbPassword@dbHost/TChild

[agronholm]

Original comment by Alex Grönholm (Bitbucket: agronholm, GitHub: agronholm):

---

SQLACodegen does not currently handle alternate schemas very well. I'll try to improve on this in the next release.

The test case would still be appreciated.