diff options
| -rw-r--r-- | CHANGES | 10 | ||||
| -rw-r--r-- | doc/build/reference/dialects/sqlite.rst | 59 | ||||
| -rw-r--r-- | lib/sqlalchemy/databases/access.py | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/databases/firebird.py | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/databases/informix.py | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/databases/maxdb.py | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/databases/mssql.py | 4 | ||||
| -rw-r--r-- | lib/sqlalchemy/databases/mysql.py | 16 | ||||
| -rw-r--r-- | lib/sqlalchemy/databases/oracle.py | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/databases/postgres.py | 11 | ||||
| -rw-r--r-- | lib/sqlalchemy/databases/sqlite.py | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/databases/sybase.py | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/schema.py | 83 | ||||
| -rw-r--r-- | test/engine/reflection.py | 68 |
14 files changed, 135 insertions, 130 deletions
@@ -168,6 +168,16 @@ CHANGES also would be a little misleading compared to values(). + - Reflected foreign keys will properly locate + their referenced column, even if the column + was given a "key" attribute different from + the reflected name. This is achieved via a + new flag on ForeignKey/ForeignKeyConstraint + called "link_to_name", if True means the given + name is the referred-to column's name, not its + assigned key. + [ticket:650] + - select() can accept a ClauseList as a column in the same way as a Table or other selectable and the interior expressions will be used as diff --git a/doc/build/reference/dialects/sqlite.rst b/doc/build/reference/dialects/sqlite.rst index ada2521a8..118c239b1 100644 --- a/doc/build/reference/dialects/sqlite.rst +++ b/doc/build/reference/dialects/sqlite.rst @@ -3,62 +3,3 @@ SQLite .. automodule:: sqlalchemy.databases.sqlite -SQLite Column Types ------------------- - -.. autoclass:: SLBinary - :members: __init__ - :show-inheritance: - -.. autoclass:: SLBoolean - :members: __init__ - :show-inheritance: - -.. autoclass:: SLChar - :members: __init__ - :show-inheritance: - -.. autoclass:: SLDateTime - :members: __init__ - :show-inheritance: - -.. autoclass:: SLDate - :members: __init__ - :show-inheritance: - -.. autoclass:: SLFloat - :members: __init__ - :show-inheritance: - -.. autoclass:: SLNumeric - :members: __init__ - :show-inheritance: - -.. autoclass:: SLInteger - :members: __init__ - :show-inheritance: - -.. autoclass:: SLSmallInteger - :members: __init__ - :show-inheritance: - -.. autoclass:: SLString - :members: __init__ - :show-inheritance: - -.. autoclass:: SLText - :members: __init__ - :show-inheritance: - -.. autoclass:: SLTime - :members: __init__ - :show-inheritance: - -.. autoclass:: DateTimeMixin - :members: - :show-inheritance: - -.. autoclass:: SLUnicodeMixin - :members: - :show-inheritance: - diff --git a/lib/sqlalchemy/databases/access.py b/lib/sqlalchemy/databases/access.py index b5adc2015..7edf68de8 100644 --- a/lib/sqlalchemy/databases/access.py +++ b/lib/sqlalchemy/databases/access.py @@ -312,7 +312,7 @@ class AccessDialect(default.DefaultDialect): continue scols = [c.ForeignName for c in fk.Fields] rcols = ['%s.%s' % (fk.Table, c.Name) for c in fk.Fields] - table.append_constraint(schema.ForeignKeyConstraint(scols, rcols)) + table.append_constraint(schema.ForeignKeyConstraint(scols, rcols, link_to_name=True)) finally: dtbs.Close() diff --git a/lib/sqlalchemy/databases/firebird.py b/lib/sqlalchemy/databases/firebird.py index c42a2c7f5..2fa8ec190 100644 --- a/lib/sqlalchemy/databases/firebird.py +++ b/lib/sqlalchemy/databases/firebird.py @@ -539,7 +539,7 @@ class FBDialect(default.DefaultDialect): fk[1].append(refspec) for name, value in fks.iteritems(): - table.append_constraint(schema.ForeignKeyConstraint(value[0], value[1], name=name)) + table.append_constraint(schema.ForeignKeyConstraint(value[0], value[1], name=name, link_to_name=True)) def do_execute(self, cursor, statement, parameters, **kwargs): # kinterbase does not accept a None, but wants an empty list diff --git a/lib/sqlalchemy/databases/informix.py b/lib/sqlalchemy/databases/informix.py index 2a9327cf5..fa13d0d34 100644 --- a/lib/sqlalchemy/databases/informix.py +++ b/lib/sqlalchemy/databases/informix.py @@ -338,7 +338,7 @@ class InfoDialect(default.DefaultDialect): fk[1].append(refspec) for name, value in fks.iteritems(): - table.append_constraint(schema.ForeignKeyConstraint(value[0], value[1] , None )) + table.append_constraint(schema.ForeignKeyConstraint(value[0], value[1] , None, link_to_name=True )) # PK c = connection.execute("""select t1.constrname as cons_name , t1.constrtype as cons_type , diff --git a/lib/sqlalchemy/databases/maxdb.py b/lib/sqlalchemy/databases/maxdb.py index dbe04f591..64b81482e 100644 --- a/lib/sqlalchemy/databases/maxdb.py +++ b/lib/sqlalchemy/databases/maxdb.py @@ -701,7 +701,7 @@ class MaxDBDialect(default.DefaultDialect): autoload=True, autoload_with=connection, **table_kw) - constraint = schema.ForeignKeyConstraint(columns, referants, + constraint = schema.ForeignKeyConstraint(columns, referants, link_to_name=True, **constraint_kw) table.append_constraint(constraint) diff --git a/lib/sqlalchemy/databases/mssql.py b/lib/sqlalchemy/databases/mssql.py index ecf8b2462..5bf7f28af 100644 --- a/lib/sqlalchemy/databases/mssql.py +++ b/lib/sqlalchemy/databases/mssql.py @@ -1145,7 +1145,7 @@ class MSSQLDialect(default.DefaultDialect): schema.Table(rtbl, table.metadata, schema=rschema, autoload=True, autoload_with=connection) if rfknm != fknm: if fknm: - table.append_constraint(schema.ForeignKeyConstraint(scols, [_gen_fkref(table, s, t, c) for s, t, c in rcols], fknm)) + table.append_constraint(schema.ForeignKeyConstraint(scols, [_gen_fkref(table, s, t, c) for s, t, c in rcols], fknm, link_to_name=True)) fknm, scols, rcols = (rfknm, [], []) if not scol in scols: scols.append(scol) @@ -1153,7 +1153,7 @@ class MSSQLDialect(default.DefaultDialect): rcols.append((rschema, rtbl, rcol)) if fknm and scols: - table.append_constraint(schema.ForeignKeyConstraint(scols, [_gen_fkref(table, s, t, c) for s, t, c in rcols], fknm)) + table.append_constraint(schema.ForeignKeyConstraint(scols, [_gen_fkref(table, s, t, c) for s, t, c in rcols], fknm, link_to_name=True)) class MSSQLDialect_pymssql(MSSQLDialect): diff --git a/lib/sqlalchemy/databases/mysql.py b/lib/sqlalchemy/databases/mysql.py index e22f242ea..86fb7b247 100644 --- a/lib/sqlalchemy/databases/mysql.py +++ b/lib/sqlalchemy/databases/mysql.py @@ -2326,23 +2326,19 @@ class MySQLSchemaReflector(object): autoload=True, autoload_with=connection) ref_names = spec['foreign'] - if not set(ref_names).issubset( - set(c.name for c in ref_table.c)): - raise exc.InvalidRequestError( - "Foreign key columns (%s) are not present on " - "foreign table %s" % - (', '.join(ref_names), ref_table.fullname)) - ref_columns = [ref_table.c[name] for name in ref_names] + + if ref_schema: + refspec = [".".join([ref_schema, ref_name, column]) for column in ref_names] + else: + refspec = [".".join([ref_name, column]) for column in ref_names] con_kw = {} for opt in ('name', 'onupdate', 'ondelete'): if spec.get(opt, False): con_kw[opt] = spec[opt] - key = schema.ForeignKeyConstraint([], [], **con_kw) + key = schema.ForeignKeyConstraint(loc_names, refspec, link_to_name=True, **con_kw) table.append_constraint(key) - for pair in zip(loc_names, ref_columns): - key.append_element(*pair) def _set_options(self, table, line): """Apply safe reflected table options to a ``Table``. diff --git a/lib/sqlalchemy/databases/oracle.py b/lib/sqlalchemy/databases/oracle.py index 66e83ec2f..aed070f2e 100644 --- a/lib/sqlalchemy/databases/oracle.py +++ b/lib/sqlalchemy/databases/oracle.py @@ -680,7 +680,7 @@ class OracleDialect(default.DefaultDialect): fk[1].append(refspec) for name, value in fks.iteritems(): - table.append_constraint(schema.ForeignKeyConstraint(value[0], value[1], name=name)) + table.append_constraint(schema.ForeignKeyConstraint(value[0], value[1], name=name, link_to_name=True)) class _OuterJoinColumn(sql.ClauseElement): diff --git a/lib/sqlalchemy/databases/postgres.py b/lib/sqlalchemy/databases/postgres.py index 0a6c12f9b..273f5859e 100644 --- a/lib/sqlalchemy/databases/postgres.py +++ b/lib/sqlalchemy/databases/postgres.py @@ -577,10 +577,11 @@ class PGDialect(default.DefaultDialect): c = connection.execute(t, table=table_oid) for row in c.fetchall(): pk = row[0] - col = table.c[pk] - table.primary_key.add(col) - if col.default is None: - col.autoincrement = False + if pk in table.c: + col = table.c[pk] + table.primary_key.add(col) + if col.default is None: + col.autoincrement = False # Foreign keys FK_SQL = """ @@ -617,7 +618,7 @@ class PGDialect(default.DefaultDialect): for column in referred_columns: refspec.append(".".join([referred_table, column])) - table.append_constraint(schema.ForeignKeyConstraint(constrained_columns, refspec, conname)) + table.append_constraint(schema.ForeignKeyConstraint(constrained_columns, refspec, conname, link_to_name=True)) # Indexes IDX_SQL = """ diff --git a/lib/sqlalchemy/databases/sqlite.py b/lib/sqlalchemy/databases/sqlite.py index 6eabca1a9..270e067f4 100644 --- a/lib/sqlalchemy/databases/sqlite.py +++ b/lib/sqlalchemy/databases/sqlite.py @@ -522,7 +522,7 @@ class SQLiteDialect(default.DefaultDialect): if refspec not in fk[1]: fk[1].append(refspec) for name, value in fks.iteritems(): - table.append_constraint(schema.ForeignKeyConstraint(value[0], value[1])) + table.append_constraint(schema.ForeignKeyConstraint(value[0], value[1], link_to_name=True)) # check for UNIQUE indexes c = connection.execute("%sindex_list(%s)" % (pragma, qtable)) unique_indexes = [] diff --git a/lib/sqlalchemy/databases/sybase.py b/lib/sqlalchemy/databases/sybase.py index 75b208056..652f6d8a7 100644 --- a/lib/sqlalchemy/databases/sybase.py +++ b/lib/sqlalchemy/databases/sybase.py @@ -619,7 +619,7 @@ class SybaseSQLDialect(default.DefaultDialect): foreignKeys[primary_table][1].append('%s.%s'%(primary_table, primary_column)) for primary_table in foreignKeys.keys(): #table.append_constraint(schema.ForeignKeyConstraint(['%s.%s'%(foreign_table, foreign_column)], ['%s.%s'%(primary_table,primary_column)])) - table.append_constraint(schema.ForeignKeyConstraint(foreignKeys[primary_table][0], foreignKeys[primary_table][1])) + table.append_constraint(schema.ForeignKeyConstraint(foreignKeys[primary_table][0], foreignKeys[primary_table][1], link_to_name=True)) if not found_table: raise exc.NoSuchTableError(table.name) diff --git a/lib/sqlalchemy/schema.py b/lib/sqlalchemy/schema.py index 5fa84063f..32ea2b5ee 100644 --- a/lib/sqlalchemy/schema.py +++ b/lib/sqlalchemy/schema.py @@ -769,13 +769,15 @@ class ForeignKey(SchemaItem): __visit_name__ = 'foreign_key' - def __init__(self, column, constraint=None, use_alter=False, name=None, onupdate=None, ondelete=None, deferrable=None, initially=None): + def __init__(self, column, constraint=None, use_alter=False, name=None, onupdate=None, ondelete=None, deferrable=None, initially=None, link_to_name=False): """ Construct a column-level FOREIGN KEY. :param column: A single target column for the key relationship. A :class:`Column` - object or a column name as a string: ``tablename.columnname`` or - ``schema.tablename.columnname``. + object or a column name as a string: ``tablename.columnkey`` or + ``schema.tablename.columnkey``. ``columnkey`` is the ``key`` which has been assigned + to the column (defaults to the column name itself), unless ``link_to_name`` is ``True`` + in which case the rendered name of the column is used. :param constraint: Optional. A parent :class:`ForeignKeyConstraint` object. If not supplied, a :class:`ForeignKeyConstraint` will be automatically created @@ -797,7 +799,10 @@ class ForeignKey(SchemaItem): :param initially: Optional string. If set, emit INITIALLY <value> when issuing DDL for this constraint. - + + :param link_to_name: if True, the string name given in ``column`` is the rendered + name of the referenced column, not its locally assigned ``key``. + :param use_alter: If True, do not emit this key as part of the CREATE TABLE definition. Instead, use ALTER TABLE after table creation to add the key. Useful for circular dependencies. @@ -812,6 +817,7 @@ class ForeignKey(SchemaItem): self.ondelete = ondelete self.deferrable = deferrable self.initially = initially + self.link_to_name = link_to_name def __repr__(self): return "ForeignKey(%r)" % self._get_colspec() @@ -877,21 +883,29 @@ class ForeignKey(SchemaItem): "foreign key" % tname) table = Table(tname, parenttable.metadata, mustexist=True, schema=schema) - try: - if colname is None: - # colname is None in the case that ForeignKey argument - # was specified as table name only, in which case we - # match the column name to the same column on the - # parent. - key = self.parent - _column = table.c[self.parent.key] - else: - _column = table.c[colname] - except KeyError, e: + + _column = None + if colname is None: + # colname is None in the case that ForeignKey argument + # was specified as table name only, in which case we + # match the column name to the same column on the + # parent. + key = self.parent + _column = table.c.get(self.parent.key, None) + elif self.link_to_name: + key = colname + for c in table.c: + if c.name == colname: + _column = c + else: + key = colname + _column = table.c.get(colname, None) + + if not _column: raise exc.NoReferencedColumnError( "Could not create ForeignKey '%s' on table '%s': " "table '%s' has no column named '%s'" % ( - self._colspec, parenttable.name, table.name, str(e))) + self._colspec, parenttable.name, table.name, key)) elif hasattr(self._colspec, '__clause_element__'): _column = self._colspec.__clause_element__() @@ -1191,50 +1205,47 @@ class ForeignKeyConstraint(Constraint): """ __visit_name__ = 'foreign_key_constraint' - def __init__(self, columns, refcolumns, name=None, onupdate=None, ondelete=None, use_alter=False, deferrable=None, initially=None): + def __init__(self, columns, refcolumns, name=None, onupdate=None, ondelete=None, use_alter=False, deferrable=None, initially=None, link_to_name=False): """Construct a composite-capable FOREIGN KEY. - columns - A sequence of local column names. The named columns must be defined - and present in the parent Table. + :param columns: A sequence of local column names. The named columns must be defined + and present in the parent Table. The names should match the ``key`` given + to each column (defaults to the name) unless ``link_to_name`` is True. - refcolumns - A sequence of foreign column names or Column objects. The columns + :param refcolumns: A sequence of foreign column names or Column objects. The columns must all be located within the same Table. - name - Optional, the in-database name of the key. + :param name: Optional, the in-database name of the key. - onupdate - Optional string. If set, emit ON UPDATE <value> when issuing DDL + :param onupdate: Optional string. If set, emit ON UPDATE <value> when issuing DDL for this constraint. Typical values include CASCADE, DELETE and RESTRICT. - ondelete - Optional string. If set, emit ON DELETE <value> when issuing DDL + :param ondelete: Optional string. If set, emit ON DELETE <value> when issuing DDL for this constraint. Typical values include CASCADE, DELETE and RESTRICT. - deferrable - Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when + :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when issuing DDL for this constraint. - initially - Optional string. If set, emit INITIALLY <value> when issuing DDL + :param initially: Optional string. If set, emit INITIALLY <value> when issuing DDL for this constraint. - use_alter - If True, do not emit this key as part of the CREATE TABLE + :param link_to_name: if True, the string name given in ``column`` is the rendered + name of the referenced column, not its locally assigned ``key``. + + :param use_alter: If True, do not emit this key as part of the CREATE TABLE definition. Instead, use ALTER TABLE after table creation to add the key. Useful for circular dependencies. + """ - super(ForeignKeyConstraint, self).__init__(name, deferrable, initially) self.__colnames = columns self.__refcolnames = refcolumns self.elements = util.OrderedSet() self.onupdate = onupdate self.ondelete = ondelete + self.link_to_name = link_to_name if self.name is None and use_alter: raise exc.ArgumentError("Alterable ForeignKey/ForeignKeyConstraint requires a name") self.use_alter = use_alter @@ -1247,7 +1258,7 @@ class ForeignKeyConstraint(Constraint): self.append_element(c, r) def append_element(self, col, refcol): - fk = ForeignKey(refcol, constraint=self, name=self.name, onupdate=self.onupdate, ondelete=self.ondelete, use_alter=self.use_alter) + fk = ForeignKey(refcol, constraint=self, name=self.name, onupdate=self.onupdate, ondelete=self.ondelete, use_alter=self.use_alter, link_to_name=self.link_to_name) fk._set_parent(self.table.c[col]) self._append_fk(fk) diff --git a/test/engine/reflection.py b/test/engine/reflection.py index 3c5ba0357..8e6a3df98 100644 --- a/test/engine/reflection.py +++ b/test/engine/reflection.py @@ -102,12 +102,8 @@ class ReflectionTest(TestBase, ComparesTables): t.create() dialect_module.ischema_names = {} try: - try: - m2 = MetaData(testing.db) - t2 = Table("test", m2, autoload=True) - assert False - except tsa.exc.SAWarning: - assert True + m2 = MetaData(testing.db) + self.assertRaises(tsa.exc.SAWarning, Table, "test", m2, autoload=True) @testing.emits_warning('Did not recognize type') def warns(): @@ -238,6 +234,59 @@ class ReflectionTest(TestBase, ComparesTables): finally: meta.drop_all() + def test_override_keys(self): + """test that columns can be overridden with a 'key', + and that ForeignKey targeting during reflection still works.""" + + + meta = MetaData(testing.db) + a1 = Table('a', meta, + Column('x', sa.Integer, primary_key=True), + Column('z', sa.Integer), + test_needs_fk=True + ) + b1 = Table('b', meta, + Column('y', sa.Integer, sa.ForeignKey('a.x')), + test_needs_fk=True + ) + meta.create_all() + try: + m2 = MetaData(testing.db) + a2 = Table('a', m2, Column('x', sa.Integer, primary_key=True, key='x1'), autoload=True) + b2 = Table('b', m2, autoload=True) + + assert a2.join(b2).onclause.compare(a2.c.x1==b2.c.y) + assert b2.c.y.references(a2.c.x1) + finally: + meta.drop_all() + + def test_nonreflected_fk_raises(self): + """test that a NoReferencedColumnError is raised when reflecting + a table with an FK to another table which has not included the target + column in its reflection. + + """ + meta = MetaData(testing.db) + a1 = Table('a', meta, + Column('x', sa.Integer, primary_key=True), + Column('z', sa.Integer), + test_needs_fk=True + ) + b1 = Table('b', meta, + Column('y', sa.Integer, sa.ForeignKey('a.x')), + test_needs_fk=True + ) + meta.create_all() + try: + m2 = MetaData(testing.db) + a2 = Table('a', m2, include_columns=['z'], autoload=True) + b2 = Table('b', m2, autoload=True) + + self.assertRaises(tsa.exc.NoReferencedColumnError, a2.join, b2) + finally: + meta.drop_all() + + @testing.exclude('mysql', '<', (4, 1, 1), 'innodb funkiness') def test_override_existing_fk(self): """test that you can override columns and specify new foreign keys to other reflected tables, @@ -351,11 +400,8 @@ class ReflectionTest(TestBase, ComparesTables): Column('pkg_id', sa.Integer, sa.ForeignKey('pkgs.pkg_id')), Column('slot', sa.String(128)), ) - try: - metadata.create_all() - assert False - except tsa.exc.InvalidRequestError, err: - assert str(err) == "Could not find table 'pkgs' with which to generate a foreign key" + + self.assertRaisesMessage(tsa.exc.InvalidRequestError, "Could not find table 'pkgs' with which to generate a foreign key", metadata.create_all) def test_composite_pks(self): """test reflection of a composite primary key""" |