History Meta - issue with unique columns in the auto-generated history table.

[mmssix]

I've got an issue with the history tables created from a model with a unique=True column. The column is created fine in the history table, but it retains the unique index, making it impossible to add more than one history entry per row.

Im using the newest history meta file.

Example Code:

engine = create_engine("sqlite+pysqlite:///test.db", echo=True)
Base = declarative_base()


class Tickets(Base, Versioned):
    __tablename__ = "tickets"
    id = Column(Integer, primary_key=True)
    ticket = Column(String(10), index=True, unique=True)


Base.metadata.create_all(engine)

Output on start. Shows that the unique key is carried over:

2024-04-30 09:07:34,682 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-04-30 09:07:34,682 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("tickets")
2024-04-30 09:07:34,682 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-04-30 09:07:34,683 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("tickets")
2024-04-30 09:07:34,683 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-04-30 09:07:34,683 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("tickets_history")
2024-04-30 09:07:34,683 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-04-30 09:07:34,683 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("tickets_history")
2024-04-30 09:07:34,683 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-04-30 09:07:34,683 INFO sqlalchemy.engine.Engine 
CREATE TABLE tickets (
        id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
        ticket VARCHAR(10), 
        version INTEGER NOT NULL
)


2024-04-30 09:07:34,684 INFO sqlalchemy.engine.Engine [no key 0.00007s] ()
2024-04-30 09:07:34,685 INFO sqlalchemy.engine.Engine CREATE UNIQUE INDEX ix_tickets_ticket ON tickets (ticket)
2024-04-30 09:07:34,685 INFO sqlalchemy.engine.Engine [no key 0.00006s] ()
2024-04-30 09:07:34,687 INFO sqlalchemy.engine.Engine 
CREATE TABLE tickets_history (
        id INTEGER NOT NULL, 
        ticket VARCHAR(10), 
        version INTEGER NOT NULL, 
        changed DATETIME, 
        PRIMARY KEY (id, version)
)


2024-04-30 09:07:34,687 INFO sqlalchemy.engine.Engine [no key 0.00006s] ()
2024-04-30 09:07:34,691 INFO sqlalchemy.engine.Engine CREATE UNIQUE INDEX ix_tickets_history_ticket_history ON tickets_history (ticket)
2024-04-30 09:07:34,691 INFO sqlalchemy.engine.Engine [no key 0.00006s] ()
2024-04-30 09:07:34,692 INFO sqlalchemy.engine.Engine COMMIT

[zzzeek]

modify the recipe to disable unique on indexes in the history table:

diff --git a/examples/versioned_history/history_meta.py b/examples/versioned_history/history_meta.py
index 3f26832b9..e4c102c0a 100644
--- a/examples/versioned_history/history_meta.py
+++ b/examples/versioned_history/history_meta.py
@@ -59,6 +59,7 @@ def _history_mapper(local_mapper):
         for idx in history_table.indexes:
             if idx.name is not None:
                 idx.name += "_history"
+            idx.unique = False
 
         for orig_c, history_c in zip(
             local_mapper.local_table.c, history_table.c

[mmssix]

That did the trick Sir. Many thanks!