Problems using automap_base with DB2-s SAMPLE DB

[boromil]

Using the official docker form IBM for DB2 10.5.5:

https://hub.docker.com/r/ibmcom/db2express-c/

docker run -it -p 50000:50000 -e DB2INST1_PASSWORD=mypass -e LICENSE=accept ibmcom/db2express-c:latest bash

# create db
su - db2inst1
db2start
db2sampl

python requirements:

sqlalchemy==1.0.17
ibm-db==2.0.5.1
ibm_db_sa==0.3.2

(btw. tried this with the newest versions also - same result)

OS: Ubuntu 14.04 64bit

Doing:

from sqlalchemy.ext.automap import automap_base
from sqlalchemy import create_engine

Base = automap_base()
engine = create_engine("db2://db2inst1:mypass@172.18.0.1:50000/SAMPLE")
Base.prepare(engine, reflect=True)

raises:

Traceback (most recent call last):
  File "db2_automap.py", line 8, in <module>
    Base.prepare(engine, reflect=True)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/ext/automap.py", line 777, in prepare
    generate_relationship)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/ext/automap.py", line 899, in _relationships_for_fks
    nullable = False not in set([fk.parent.nullable for fk in fks])
AttributeError: 'NoneType' object has no attribute 'nullable'

seems that some fk-s have incorrect parent and column attributes

this issue was reported to SQLAlchemy-s team, but this place is more appropriate for it

[zzzeek]

specifically I need the ibm-db-sa folks to help me debug this. I see they are using the modern form of reflection with get_foreign_keys() etc., (https://github.com/ibmdb/python-ibmdbsa/blob/master/ibm_db_sa/ibm_db_sa/base.py#L737), that's good, but now this method is returning some text that is somehow resulting in a ForeignKeyConstraint() that does not have a .parent attribute after reflection. This can only happen if some kind of invalid data is being returned by get_foreign_keys() (though I cannot come up with any combination that does this), or if something else is messing with the Table after the fact. I can't reproduce this condition on my own - .parent is always, definitely, completely set, and if it can't be set, you get an error that it can't find the parent table or column, not that .parent is None. the original reporter of this issue at https://bitbucket.org/zzzeek/sqlalchemy/issues/3949/sqlalchemy-compilerpy-bug was simply unable to produce an adequate test case.

I need a test like this:

from sqlalchemy import *

e = create_engine("sqlite://", echo=True)

m = MetaData()
Table('t1', m, Column('id', Integer, primary_key=True))
Table(
    't2', m, Column('id', Integer, primary_key=True),
    Column('x', ForeignKey('t1.id')))

m.create_all(e)

m2 = MetaData()
m2.reflect(e)

from sqlalchemy.schema import CreateTable

print CreateTable(m2.tables['t2'])

that breaks under DB2. It is one of the tables in DB2's sample database that's doing this. I've tried appending every kind of ForeignKeyConstraint() to a table I can and there is no condition that you can get a ForeignKey element that has None for .parent - the moment ForeignKeyConstraint is attached to Table, the .parent attribute of all ForeignKey objects gets set, and if it cant be set, raises an error immediately. Someone needs to march through all of db2's code and see why this is happening.

[zzzeek]

OK, here is the condition that can produce the error. if reflection.get_foreign_keys() is returning the same combination of columns twice inside the constraint:

from sqlalchemy import *

m = MetaData()

t = Table('t', m, Column('q', Integer))

Table('z', m, Column('q', Integer))

const = ForeignKeyConstraint(['q', 'q'], ['z', 'z'], None, link_to_name=True)

# or:
# const = ForeignKeyConstraint(['q'], ['z.q', 'z.x'], None, link_to_name=True)

# or anything like that

t.append_constraint(const)


# fails
set([fk.parent.nullable for fk in const.elements])

# fails
from sqlalchemy import schema

print schema.CreateTable(t)

this is not a valid FK constraint. SQLAlchemy can be made to work around this but it doesn't make sense if DB2 is sending out the same local/remote column pair twice.

[hemlatabhatt]

@boromil i am not able to reproduce this.Can you try upgrading to the latest ibm_db_sa -0.3.3 and ibm_db=2.0.7 and check if you are still facing the same.

[boromil]

@hemlatabhatt that the first thing I've tried - no luck unfortunately

[asierra01]

I did some research about this, when I have a chance I post here.

here is the long email....
the code is raising an exception on generating ddl on table emp_photo

db2 connect to sample
db2 describe table emp_photo
...db2 will print details on table emp_photo here
db2 describe indexes for emp_photo
...db2 will print details for indexes table emp_photo
db2 describe table employee
...details

official ddl for table EMP_PHOTO

CREATE TABLE "EMP_PHOTO" (
"EMPNO" CHAR(6) NOT NULL ,
"PHOTO_FORMAT" VARCHAR(10) NOT NULL ,
"PICTURE" BLOB(102400) LOGGED NOT COMPACT )
IN "USERSPACE1"

#this alias is confusing the get fk query below
CREATE ALIAS "MAC "."EMP" FOR TABLE "MAC "."EMPLOYEE"

ALTER TABLE "EMP_PHOTO"
ADD CONSTRAINT "PK_EMP_PHOTO" PRIMARY KEY
("EMPNO",
"PHOTO_FORMAT")

ALTER TABLE "EMP_PHOTO"
ADD CONSTRAINT "FK_EMP_PHOTO" FOREIGN KEY
("EMPNO")
REFERENCES "MAC "."EMPLOYEE"
("EMPNO")
ON DELETE RESTRICT
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION

[asierra01]

Is hard to format source code with this editor...hahaha.
so...sqlalchemy rely on ibm_db_sa to produce the FK by using this table mapping below (sys_foreignkeys), the problem is I look at this query carefully and it produced two records.

1 (u'FK_EMP_PHOTO', None, u'MAC', u'EMP_PHOTO', u'EMPNO', u'PK_EMPLOYEE', None, u'MAC', u'EMPLOYEE', u'EMPNO', 7, u'PRIMARY', 1)

2 (u'FK_EMP_PHOTO', None, u'MAC', u'EMP_PHOTO', u'EMPNO', u'PK_EMPLOYEE', None, u'MAC', u'EMP', u'EMPNO', 7, u'PRIMARY', 1)

The EMPLOYEE table is right, but the EMP table is an alias table of employee, I think thats where db2 is providing wrong info on fk.

I created the sample database with user mac, so mac is the schema that 'owns' all these tables
here is a query to get details on table EMPLOYEE and EMP

select * from sysibm.systables
where creator = 'MAC' and name = 'EMPLOYEE'
this print sysibm.systables.type = 'T' ...as Table

'EMP' is an alias table for 'EMPLOYEE'
select * from sysibm.systables
where creator = 'MAC' and name = 'EMP'
this print sysibm.systables.type = 'A' ...as Alias

this below is the table mapping that class DB2Reflector , provide sqlalchemy for the DB2Dialect to get foreignkeys.

sys_foreignkeys = Table("SQLFOREIGNKEYS", ischema,
Column("FK_NAME", CoerceUnicode, key="fkname"),
Column("FKTABLE_SCHEM", CoerceUnicode, key="fktabschema"),
Column("FKTABLE_NAME", CoerceUnicode, key="fktabname"),
Column("FKCOLUMN_NAME", CoerceUnicode, key="fkcolname"),
Column("PK_NAME", CoerceUnicode, key="pkname"),
Column("PKTABLE_SCHEM", CoerceUnicode, key="pktabschema"),
Column("PKTABLE_NAME", CoerceUnicode, key="pktabname"),
Column("PKCOLUMN_NAME", CoerceUnicode, key="pkcolname"),
Column("KEY_SEQ", sa_types.Integer, key="colno"),
schema="SYSIBM")

so I think this query has to check if the table is alias and prune this record before providing the result to sa. I have to experiment with it to see if this will fix this issue.

[mdobrzanski]

In my case

SQLAlchemy==1.2.13
ibm-db==2.0.9
ibm_db_sa==0.3.2

That doesn't happen if I remove all aliases from the database.

I suspect the origin of the problem is creating too many relations between tables.

For start there's a query for foreign keys https://github.com/ibmdb/python-ibmdbsa/blob/master/ibm_db_sa/ibm_db_sa/reflection.py#L301. When it's executed on table with alias for example

SELECT  *
FROM "SYSIBM"."SQLFOREIGNKEYS"
WHERE "SYSIBM"."SQLFOREIGNKEYS"."FKTABLE_SCHEM" = 'DB2INST1'
      AND "SYSIBM"."SQLFOREIGNKEYS"."FKTABLE_NAME" = 'EMPLOYEE'
ORDER BY "SYSIBM"."SQLFOREIGNKEYS"."KEY_SEQ";

two rows are returned for the same relation. One for table DEPARTMENT and one for alias DEPT.

NULL,DB2INST1,DEPARTMENT,DEPTNO,NULL,DB2INST1,EMPLOYEE,WORKDEPT,1,3,2,RED,PK_DEPARTMENT,7,PRIMARY
NULL,DB2INST1,DEPT,DEPTNO,NULL,DB2INST1,EMPLOYEE,WORKDEPT,1,3,2,RED,PK_DEPARTMENT,7,PRIMARY

I'm trying to fix that in my fork https://github.com/mdobrzanski/python-ibmdbsa/blob/reflect-with-aliases/ibm_db_sa/ibm_db_sa/reflection.py#L303 by joining and filtering only for TYPE='T'. But then I get an error on direction.
sqlalchemy.exc.ArgumentError: act.act and back-reference act.act_collection are both of the same direction symbol('ONETOMANY'). Did you mean to set remote_side on the many-to-one side ?

Little Update.
The fix is probably correct. There's something wrong with relationships in SAMPLE database.
The foreign key constraints seems wrong in exported DDL for tables

ACT

ALTER TABLE "DB2INST1"."ACT" 
ADD CONSTRAINT "RPAA" FOREIGN KEY ("ACTNO") REFERENCES "DB2INST1"."ACT ("ACTNO")

PROJACT

ALTER TABLE "DB2INST1"."PROJACT" 
ADD CONSTRAINT "RPAP" FOREIGN KEY ("PROJNO") REFERENCES "DB2INST1"."PROJECT" ("PROJNO")

PROJECT

ALTER TABLE "DB2INST1"."PROJECT"
ADD CONSTRAINT "RPP" FOREIGN KEY ("MAJPROJ") REFERENCES "DB2INST1"."PROJECT" ("PROJNO")

The diagram https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/intro/src/tpc/db2z_sampletablesrelationship.html

Update2:
Yeah, it worked after fixing relationship ACT --< PROJACT in SAMPLE.