Use named ForeignKeys in migrations

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

[Christopher Allan Webber]

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.

[Jessica Tallon]

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.

[Christopher Allan Webber]

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