ordering of tables makes some relationships invalid about sqlacodegen

[agronholm]

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

---

Curiously, the order doesn't seem to be a problem if I remove the other foreign key from the PK column of Participant.
Also, a very real bug here seems to be the generation of server_default='NULL'. I have never seen this before.

[agronholm]

Original comment by jonorthwash (Bitbucket: jonorthwash, GitHub: jonorthwash):

---

I've pared down the database a bit while still keeping the error producible, but I didn't make a fully minimal test case. My apologies if this is too much...

Schema of source sqlite3:

#!sqlite3

CREATE TABLE `dups` (
  `id` INTEGER NULL  DEFAULT NULL,
  `person1` INTEGER NULL DEFAULT NULL,
  `person2` INTEGER NULL DEFAULT NULL,
  `confidence` VARCHAR(10) NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
,
	FOREIGN KEY (person1) REFERENCES `individuals` (`id`),
	FOREIGN KEY (person2) REFERENCES `individuals` (`id`)
);
CREATE TABLE `individuals` (
  `id` INTEGER NULL  DEFAULT NULL,
  `fullname` VARCHAR(255) NULL DEFAULT NULL,
  `given_name` VARCHAR(255) NULL DEFAULT NULL,
  `surname` VARCHAR(255) NULL DEFAULT NULL,
  `father` INTEGER NULL DEFAULT NULL,
  `mother` INTEGER NULL DEFAULT NULL,
  `gender` CHAR NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
,
	FOREIGN KEY (father) REFERENCES `individuals` (`id`),
	FOREIGN KEY (mother) REFERENCES `individuals` (`id`)
);
CREATE TABLE `log` (
  `id` INTEGER NULL  DEFAULT NULL,
  `user` VARCHAR(255) NULL DEFAULT NULL,
  `table` VARCHAR(63) NULL DEFAULT NULL,
  `entry_id` INTEGER NULL DEFAULT NULL,
  `field` VARCHAR(63) NULL DEFAULT NULL,
  `old_value` BLOB NULL DEFAULT NULL,
  `new_value` BLOB NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE `participants` (
  `id` INTEGER NULL  DEFAULT NULL,
  `event` INTEGER NULL DEFAULT NULL,
  `role` VARCHAR(255) NULL DEFAULT NULL ,
  PRIMARY KEY (`id`)
,
	FOREIGN KEY (id) REFERENCES `tamgas` (`id`),
	FOREIGN KEY (id) REFERENCES `individuals` (`id`)
);
CREATE TABLE `tamgas` (
  `id` INTEGER NULL  DEFAULT NULL,
  `uri` VARCHAR(255) NULL DEFAULT NULL,
  `name` VARCHAR(20) NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Generated python:

#!python

# coding: utf-8
from sqlalchemy import Column, ForeignKey, Integer, LargeBinary, String
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()
metadata = Base.metadata


class Dup(Base):
    __tablename__ = 'dups'

    id = Column(Integer, primary_key=True)
    person1 = Column(ForeignKey('individuals.id'), server_default='NULL')
    person2 = Column(ForeignKey('individuals.id'), server_default='NULL')
    confidence = Column(String(10), server_default='NULL')

    individual = relationship('Individual', primaryjoin='Dup.person1 == Individual.id')
    individual1 = relationship('Individual', primaryjoin='Dup.person2 == Individual.id')


class Individual(Base):
    __tablename__ = 'individuals'

    id = Column(Integer, primary_key=True)
    fullname = Column(String(255), server_default='NULL')
    given_name = Column(String(255), server_default='NULL')
    surname = Column(String(255), server_default='NULL')
    father = Column(ForeignKey('individuals.id'), server_default='NULL')
    mother = Column(ForeignKey('individuals.id'), server_default='NULL')
    gender = Column(String, server_default='NULL')

    parent = relationship('Individual', remote_side=[id], primaryjoin='Individual.father == Individual.id')
    parent1 = relationship('Individual', remote_side=[id], primaryjoin='Individual.mother == Individual.id')


class Participant(Individual):
    __tablename__ = 'participants'

    id = Column(ForeignKey('individuals.id'), ForeignKey('tamgas.id'), primary_key=True)
    event = Column(Integer, server_default='NULL')
    role = Column(String(255), server_default='NULL')

    tamga = relationship('Tamga', uselist=False)


class Log(Base):
    __tablename__ = 'log'

    id = Column(Integer, primary_key=True)
    user = Column(String(255), server_default='NULL')
    table = Column(String(63), server_default='NULL')
    entry_id = Column(Integer, server_default='NULL')
    field = Column(String(63), server_default='NULL')
    old_value = Column(LargeBinary, server_default='NULL')
    new_value = Column(LargeBinary, server_default='NULL')


class Tamga(Base):
    __tablename__ = 'tamgas'

    id = Column(Integer, primary_key=True)
    uri = Column(String(255), server_default='NULL')
    name = Column(String(20), server_default='NULL')

Error message when python3 runs the generated python file:

#!python backtrace

Traceback (most recent call last):
  File "../2014-05-21.py", line 38, in <module>
    class Participant(Individual):
  File "/usr/local/lib/python3.4/site-packages/sqlalchemy/ext/declarative/api.py", line 53, in __init__
    _as_declarative(cls, classname, cls.__dict__)
  File "/usr/local/lib/python3.4/site-packages/sqlalchemy/ext/declarative/base.py", line 326, in _as_declarative
    mt.map()
  File "/usr/local/lib/python3.4/site-packages/sqlalchemy/ext/declarative/base.py", line 409, in map
    **mapper_args
  File "<string>", line 2, in mapper
  File "/usr/local/lib/python3.4/site-packages/sqlalchemy/orm/mapper.py", line 613, in __init__
    self._configure_inheritance()
  File "/usr/local/lib/python3.4/site-packages/sqlalchemy/orm/mapper.py", line 929, in _configure_inheritance
    consider_as_foreign_keys=fks)
  File "/usr/local/lib/python3.4/site-packages/sqlalchemy/sql/util.py", line 425, in criterion_as_pairs
    visitors.traverse(expression, {}, {'binary': visit_binary})
  File "/usr/local/lib/python3.4/site-packages/sqlalchemy/sql/visitors.py", line 255, in traverse
    return traverse_using(iterate(obj, opts), obj, visitors)
  File "/usr/local/lib/python3.4/site-packages/sqlalchemy/sql/visitors.py", line 246, in traverse_using
    meth(target)
  File "/usr/local/lib/python3.4/site-packages/sqlalchemy/sql/util.py", line 422, in visit_binary
    elif binary.right.references(binary.left):
  File "/usr/local/lib/python3.4/site-packages/sqlalchemy/sql/schema.py", line 1100, in references
    if fk.column.proxy_set.intersection(column.proxy_set):
  File "/usr/local/lib/python3.4/site-packages/sqlalchemy/util/langhelpers.py", line 712, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/usr/local/lib/python3.4/site-packages/sqlalchemy/sql/schema.py", line 1646, in column
    tablekey)
sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'participants.id' could not find table 'tamgas' with which to generate a foreign key to target column 'id'

The behaviour seems a little inconsistent. When I repeatedly regenerate the sqlite database (from a source schema) and then rerun sqlacodegen, sometimes this behaviour appears, and sometimes it doesn't.

[agronholm]

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

---

But all the references are quoted. That is, they are only resolved after all the classes have been defined, when the mapped classes are actually used for the first time.
Can you post an example that reproduces this?