Incorrect handling of complex indexes by Table.tometadata

Issue #4147 resolved
Paweł Stiasny
created an issue

Current implementation Table.tometada doesn't correctly support functional and text indexes.

Consider the following test:

diff --git a/test/sql/test_metadata.py b/test/sql/test_metadata.py
index 45eb594..25ac689 100644
--- a/test/sql/test_metadata.py
+++ b/test/sql/test_metadata.py
@@ -1042,18 +1042,21 @@ class ToMetaDataTest(fixtures.TestBase, ComparesTables):

     def test_indexes(self):
         meta = MetaData()

         table = Table('mytable', meta,
                       Column('id', Integer, primary_key=True),
                       Column('data1', Integer, index=True),
                       Column('data2', Integer),
+                      Index('text', text('data1 + 1')),
                       )
-        Index('multi', table.c.data1, table.c.data2),
+        Index('multi', table.c.data1, table.c.data2)
+        Index('func', func.abs(table.c.data1))
+        Index('multi-func', table.c.data1, func.abs(table.c.data2))

         meta2 = MetaData()
         table_c = table.tometadata(meta2)

         def _get_key(i):
             return [i.name, i.unique] + \
                 sorted(i.kwargs.items()) + \
                 list(i.columns.keys())
  • The text expression index is ignored
  • func is improperly classified as the default index of a column and ignored
  • In multi-func the second component is incorrect (should be a Function, is a Column)

Comments (8)

  1. Michael Bayer repo owner

    well also that test should be improved to maintain func() and all that:

        def _get_key(i):
            return [i.name, i.unique] + \
                sorted(i.kwargs.items()) + \
                [str(col) for col in i.expressions]
    
  2. Michael Bayer repo owner

    Fully copy index expressions

    Fixed bug where the :meth:.Table.tometadata method would not properly accommodate :class:.Index objects that didn't consist of simple column expressions, such as indexes against a :func:.text construct, indexes that used SQL expressions or :attr:.func, etc. The routine now copies expressions fully to a new :class:.Index object while substituting all table-bound :class:.Column objects for those of the target table.

    Also refined the means by which tometadata() checks if an Index or UniqueConstraint is generated by a column-level flag, by propagating an attribute "_column_flag=True" to such indexes/constraints.

    Change-Id: I7ef1b8ea42f9933357ae35f241a5ba9838bac35b Fixes: #4147

    → <<cset 31dd4824d335>>

  3. colladoman

    I'm having lots of problems when I try to use "tometadata" in tables from Oracle that contain indexes. I've been using this to sync data from one database to other, but since sqlalchemy 1.2.0 i'm having this problem:

    Traceback (most recent call last):
      File "test.py", line 186, in <module>
        sources[source]=DBAccess.cSource(DBConfig.SOURCES[source], source_connections)
      File "/home/pyweb/projects/DBCacheSQLite/DBAccess.py", line 111, in __init__
        self.cache_metadata,
      File "/home/pyweb/projects/DBCacheSQLite/lib/python3.4/site-packages/sqlalchemy/sql/schema.py", line 911, in tometadata
        **index.kwargs)
      File "/home/pyweb/projects/DBCacheSQLite/lib/python3.4/site-packages/sqlalchemy/sql/schema.py", line 3447, in __init__
        self._set_parent(table)
      File "/home/pyweb/projects/DBCacheSQLite/lib/python3.4/site-packages/sqlalchemy/sql/schema.py", line 3458, in _set_parent
        table.description
    sqlalchemy.exc.ArgumentError: Index 'ix_bag_last' is against table 'bbags', and cannot be associated with table 'bbags'.
    

    I've tested that reverting the last part of tometadata method changes everything runs fine again (file sqlalchemy/sql/schema.py): From (1.2.0+)

                Index(index.name,
                      unique=index.unique,
                      *[_copy_expression(expr, self, table)
                        for expr in index.expressions],
                      _table=table,
                      **index.kwargs)
    

    to (1.1.8)

                Index(index.name,
                      unique=index.unique,
                      *[table.c[col] for col in index.columns.keys()],
                      **index.kwargs)
    

    Actually, I don't need indexes to be copied, but I would like to know if there's something I can do to get tables copied from one database to another like I used to

  4. Michael Bayer repo owner

    @colladoman best way is for you to provide the oracle CREATE TABLE /CREATE INDEX statements that produces these constructs in a minimal way to reproduce the issue, and then I will fix it. without a test case I can't fix.

  5. colladoman

    Here it is:

    Creation of table and index in Oracle:

    CREATE TABLE test (id NUMBER(10,0) NOT NULL ENABLE, eventtime date);
    
    CREATE INDEX ix_test ON test (eventtime);
    

    And here is a sample source code:

    #!/bin/env python3
    # -*- coding: utf-8 -*-
    from sqlalchemy import create_engine
    from sqlalchemy.orm import mapper, sessionmaker
    
    from sqlalchemy import Table, Column, Integer, String, DateTime, MetaData, ForeignKey,PrimaryKeyConstraint,UniqueConstraint
    
    test1_engine = create_engine('oracle://test:test@testdb:1524/test')
    test1_conn = test1_engine.connect()
    test1_metadata = MetaData(test1_engine)
    test1_Session_Maker = sessionmaker(bind=test1_engine)
    test1_session = test1_Session_Maker()
    
    test2_engine = create_engine('sqlite://')
    test2_conn = test2_engine.connect()
    test2_metadata = MetaData(test2_engine)
    test2_Session_Maker = sessionmaker(bind=test2_engine)
    test2_session = test2_Session_Maker()
    
    test1_table=Table(
        'test',
        test1_metadata,
        Column('id', Integer, primary_key=True),
        Column('eventtime', DateTime),
        schema='mad',
        autoload=True,
        autoload_with=test1_engine
    )
    
    
    test2_table = test1_table.tometadata(test2_metadata)
    

    This is the result in 1.2.8:

    Traceback (most recent call last):
      File "./test_new.py", line 31, in <module>
        test2_table = test1_table.tometadata(test2_metadata)
      File "/home/pyweb/projects/DBCacheSQLite/lib/python3.4/site-packages/sqlalchemy/sql/schema.py", line 911, in tometadata
        **index.kwargs)
      File "/home/pyweb/projects/DBCacheSQLite/lib/python3.4/site-packages/sqlalchemy/sql/schema.py", line 3447, in __init__
        self._set_parent(table)
      File "/home/pyweb/projects/DBCacheSQLite/lib/python3.4/site-packages/sqlalchemy/sql/schema.py", line 3458, in _set_parent
        table.description
    sqlalchemy.exc.ArgumentError: Index 'ix_test' is against table 'test', and cannot be associated with table 'test'.
    

    In 1.1.18, it runs perfectly. Thank you!!

  6. Log in to comment