Opened 10 years ago

Closed 9 years ago

#914 closed defect (wontfix)

Use named ForeignKeys in migrations

Reported by: Elrond Owned by:
Priority: critical Milestone: 0.8.0
Component: programming Keywords: test
Cc: Parent Tickets:

Description (last modified by Elrond)

db/migrations.py has this:

from mediagoblin.db.models import (MediaEntry, Collection, MediaComment, User,
        Privilege)

This is a big NO! Those are the current variansts of those tables. Not the versions needed in any old migration. Those current tables could have changed in a completely unexpected way and break old migrations.

If there are migration docs, please add a note about this.

The current main use of the above imports is code like this:

creator = Column(Integer, ForeignKey(User.id), nullable=False)

This can be easily fixed:

creator = Column(Integer, ForeignKey('core__users.id'), nullable=False)

When this is done, please take a look at add_new_notification_tables():

    user_table = inspect_table(metadata, 'core__users')
    mediaentry_table = inspect_table(metadata, 'core__media_entries')
    mediacomment_table = inspect_table(metadata, 'core__media_comments')

This code might be useless.

After fixing all of this, please make sure the migrations still work.

Change History (5)

comment:1 by Elrond, 10 years ago

Keywords: test added

comment:2 by Elrond, 10 years ago

Description: modified (diff)

comment:3 by Christopher Allan Webber, 10 years ago

Milestone: 0.7.00.8.0

I think this is important but can wait till next release. It's not good, but it's not broken by any current migrations. We should get to it by next release, though.

comment:4 by Jessica Tallon, 9 years ago

I attempted to use named foreign keys in my latest migration. This caused it to break, I attempted to use ForeignKey('core__users.id') instead of ForeignKey(User.id) however I got this traceback:

(mediagoblin)~/Documents/Workspace/mediagoblin> ./bin/gmg dbupdate
-> Updating main mediagoblin tables:
   + Running migration 24, "activity_migration"... Traceback (most recent call last):
  File "./bin/gmg", line 9, in <module>
    load_entry_point('mediagoblin==0.6.2.dev', 'console_scripts', 'gmg')()
  File "/Users/jessica/Documents/Workspace/mediagoblin/mediagoblin/gmg_commands/__init__.py", line 124, in main_cli
    args.func(args)
  File "/Users/jessica/Documents/Workspace/mediagoblin/mediagoblin/gmg_commands/dbupdate.py", line 147, in dbupdate
    run_dbupdate(app_config, global_config)
  File "/Users/jessica/Documents/Workspace/mediagoblin/mediagoblin/gmg_commands/dbupdate.py", line 120, in run_dbupdate
    run_all_migrations(db, app_config, global_config)
  File "/Users/jessica/Documents/Workspace/mediagoblin/mediagoblin/gmg_commands/dbupdate.py", line 142, in run_all_migrations
    migration_manager.init_or_migrate()
  File "/Users/jessica/Documents/Workspace/mediagoblin/mediagoblin/db/migration_tools.py", line 234, in init_or_migrate
    migration_func(self.session)
  File "/Users/jessica/Documents/Workspace/mediagoblin/mediagoblin/db/migrations.py", line 938, in activity_migration
    Activity_R0.__table__.create(db.bind)
  File "build/bdist.macosx-10.9-intel/egg/sqlalchemy/schema.py", line 616, in create
  File "build/bdist.macosx-10.9-intel/egg/sqlalchemy/engine/base.py", line 1479, in _run_visitor
  File "build/bdist.macosx-10.9-intel/egg/sqlalchemy/engine/base.py", line 1122, in _run_visitor
  File "build/bdist.macosx-10.9-intel/egg/sqlalchemy/sql/visitors.py", line 122, in traverse_single
  File "build/bdist.macosx-10.9-intel/egg/sqlalchemy/engine/ddl.py", line 89, in visit_table
  File "build/bdist.macosx-10.9-intel/egg/sqlalchemy/engine/base.py", line 662, in execute
  File "build/bdist.macosx-10.9-intel/egg/sqlalchemy/engine/base.py", line 714, in _execute_ddl
  File "build/bdist.macosx-10.9-intel/egg/sqlalchemy/sql/expression.py", line 2431, in compile
  File "build/bdist.macosx-10.9-intel/egg/sqlalchemy/schema.py", line 2958, in _compiler
  File "build/bdist.macosx-10.9-intel/egg/sqlalchemy/engine/interfaces.py", line 804, in __init__
  File "build/bdist.macosx-10.9-intel/egg/sqlalchemy/engine/interfaces.py", line 823, in process
  File "build/bdist.macosx-10.9-intel/egg/sqlalchemy/sql/visitors.py", line 80, in _compiler_dispatch
  File "build/bdist.macosx-10.9-intel/egg/sqlalchemy/sql/compiler.py", line 1982, in visit_create_table
  File "build/bdist.macosx-10.9-intel/egg/sqlalchemy/sql/compiler.py", line 2019, in create_table_constraints
  File "build/bdist.macosx-10.9-intel/egg/sqlalchemy/sql/compiler.py", line 2017, in <genexpr>
  File "build/bdist.macosx-10.9-intel/egg/sqlalchemy/sql/compiler.py", line 2025, in <genexpr>
  File "build/bdist.macosx-10.9-intel/egg/sqlalchemy/engine/interfaces.py", line 823, in process
  File "build/bdist.macosx-10.9-intel/egg/sqlalchemy/sql/visitors.py", line 80, in _compiler_dispatch
  File "build/bdist.macosx-10.9-intel/egg/sqlalchemy/dialects/sqlite/base.py", line 520, in visit_foreign_key_constraint
  File "build/bdist.macosx-10.9-intel/egg/sqlalchemy/util/langhelpers.py", line 612, in __get__
  File "build/bdist.macosx-10.9-intel/egg/sqlalchemy/schema.py", line 1474, in column
sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'core__activities.actor' could not find table 'core__users' with which to generate a foreign key to target column 'id'

When I got that I looked in the database and I can confirm core__users does exist and it does have an id column. Switching it back to ForeignKey(User.id) fixes this issue. The code to checkout to produce this is in commit b61519.

comment:5 by Christopher Allan Webber, 9 years ago

Resolution: wontfix
Status: newclosed

We're moving to Alembic anyway, so I think we can not worry about this. I'm closing it out.

Note: See TracTickets for help on using tickets.