Error with constraint to other schema enum?

[agronholm]

Originally reported by: YKdvd NA (Bitbucket: YKdvd, GitHub: YKdvd)

---

I was trying to run sqlacodegen on a MySQL database which does FK constraints to a different schema. So the Parent schema has a Users table, which includes a userGroup field which is an ENUM. The Child schema has a Something table which does something like:

CONSTRAINT fk_S_U FOREIGN KEY (user_id) REFERENCES Parent.Users (id) ON UPDATE CASCADE

If I dump the Parent schema with sqlacodegen, there's no problem, and Users dumps fine. I can also dump tables from the Child schema that do FK constraints to other tables (not Users) in the Parent schema. But a Child table that refers to Parent.Users crashes with:

File ".../sqlacodegen/codegen.py", line 469, in init
if isinstance(table.c[colname].type, String):
File "../sqlalchemy/util/_collections.py", line 154, in getitem
return self._data[key]
KeyError: u'users.`userGroup'

I'm not sure why it doesn't like that Parent.Users.userGroup field cross-schema, perhaps something to do with it being an enum? This is sqlacodegen 1.1.1 and SQLAlchemy 0.8.2, against MySQL 5.5/5.1. I'll see if I can dig into the code, or create a minimal example reduction someday when I have free time.

---

• Bitbucket: https://bitbucket.org/agronholm/sqlacodegen/issue/3

[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.

[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):

---

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