Incorrect handling of complex indexes by Table.tometadata

[sqlalchemy-bot]

Migrated issue, originally created by Paweł Stiasny

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)

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• set milestone to "1.2"

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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]

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

that test case is perfect, covers several bases at once, thanks so much!

https://gerrit.sqlalchemy.org/#/c/zzzeek/sqlalchemy/+/614

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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

→ 31dd482

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed

[sqlalchemy-bot]

colladoman wrote:

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

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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

[sqlalchemy-bot]

colladoman wrote:

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!!

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

OK this is a totally new use case never seen before. Your minimal test case is at #4279.