import datetime
import uuid
+import six
+
+if six.PY2:
+ import migrate
+
from sqlalchemy import (MetaData, Table, Column, Boolean, SmallInteger,
Integer, Unicode, UnicodeText, DateTime,
- ForeignKey, Date)
+ ForeignKey, Date, Index)
from sqlalchemy.exc import ProgrammingError
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import and_
-from migrate.changeset.constraint import UniqueConstraint
-
+from sqlalchemy.schema import UniqueConstraint
from mediagoblin.db.extratypes import JSONEncoded, MutationDict
-from mediagoblin.db.migration_tools import (RegisterMigration, inspect_table,
- replace_table)
-from mediagoblin.db.models import (MediaEntry, Collection, MediaComment, User,
- Privilege)
+from mediagoblin.db.migration_tools import (
+ RegisterMigration, inspect_table, replace_table_hack)
+from mediagoblin.db.models import (MediaEntry, Collection, MediaComment, User,
+ Privilege)
+from mediagoblin.db.extratypes import JSONEncoded, MutationDict
+
MIGRATIONS = {}
for row in db.execute(media_table.select()):
# no slug, try setting to an id
if not row.slug:
- append_garbage_till_unique(row, unicode(row.id))
+ append_garbage_till_unique(row, six.text_type(row.id))
# has "=" or ":" in it... we're getting rid of those
elif u"=" in row.slug or u":" in row.slug:
append_garbage_till_unique(
existing_slugs[row.creator].append(row.slug)
for row_id in slugs_to_change:
- new_slug = unicode(uuid.uuid4())
+ new_slug = six.text_type(uuid.uuid4())
db.execute(collection_table.update().
where(collection_table.c.id == row_id).
values(slug=new_slug))
db.commit()
-
@RegisterMigration(15, MIGRATIONS)
def wants_notifications(db):
"""Add a wants_notifications field to User model"""
admin_users_ids, active_users_ids, inactive_users_ids = (
db.execute(
user_table.select().where(
- user_table.c.is_admin==1)).fetchall(),
+ user_table.c.is_admin==True)).fetchall(),
db.execute(
user_table.select().where(
- user_table.c.is_admin==0).where(
+ user_table.c.is_admin==False).where(
user_table.c.status==u"active")).fetchall(),
db.execute(
user_table.select().where(
- user_table.c.is_admin==0).where(
+ user_table.c.is_admin==False).where(
user_table.c.status!=u"active")).fetchall())
# Get the ids for each of the privileges so we can reference them ~~~~~~~~~
# admin, an active user or an inactive user ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
for admin_user in admin_users_ids:
admin_user_id = admin_user['id']
- for privilege_id in [admin_privilege_id, uploader_privilege_id,
- reporter_privilege_id, commenter_privilege_id,
+ for privilege_id in [admin_privilege_id, uploader_privilege_id,
+ reporter_privilege_id, commenter_privilege_id,
active_privilege_id]:
db.execute(user_privilege_assoc.insert().values(
core__privilege_id=admin_user_id,
for active_user in active_users_ids:
active_user_id = active_user['id']
- for privilege_id in [uploader_privilege_id, reporter_privilege_id,
+ for privilege_id in [uploader_privilege_id, reporter_privilege_id,
commenter_privilege_id, active_privilege_id]:
db.execute(user_privilege_assoc.insert().values(
core__privilege_id=active_user_id,
for inactive_user in inactive_users_ids:
inactive_user_id = inactive_user['id']
- for privilege_id in [uploader_privilege_id, reporter_privilege_id,
+ for privilege_id in [uploader_privilege_id, reporter_privilege_id,
commenter_privilege_id]:
db.execute(user_privilege_assoc.insert().values(
core__privilege_id=inactive_user_id,
User_vR1.__table__.create(db.bind)
db.commit()
new_user_table = inspect_table(metadata, 'rename__users')
- replace_table(db,user_table, new_user_table)
+ replace_table_hack(db, user_table, new_user_table)
else:
# If the db is not run using SQLite, this process is much simpler ~~~~~
is_admin.drop()
db.commit()
+
+
+@RegisterMigration(19, MIGRATIONS)
+def drop_MediaEntry_collected(db):
+ """
+ Drop unused MediaEntry.collected column
+ """
+ metadata = MetaData(bind=db.bind)
+
+ media_collected= inspect_table(metadata, 'core__media_entries')
+ media_collected = media_collected.columns['collected']
+
+ media_collected.drop()
+
+ db.commit()
+
+
+@RegisterMigration(20, MIGRATIONS)
+def add_metadata_column(db):
+ metadata = MetaData(bind=db.bind)
+
+ media_entry = inspect_table(metadata, 'core__media_entries')
+
+ col = Column('media_metadata', MutationDict.as_mutable(JSONEncoded),
+ default=MutationDict())
+ col.create(media_entry)
+
+ db.commit()
+
+
+class PrivilegeUserAssociation_R1(declarative_base()):
+ __tablename__ = 'rename__privileges_users'
+ user = Column(
+ "user",
+ Integer,
+ ForeignKey(User.id),
+ primary_key=True)
+ privilege = Column(
+ "privilege",
+ Integer,
+ ForeignKey(Privilege.id),
+ primary_key=True)
+
+@RegisterMigration(21, MIGRATIONS)
+def fix_privilege_user_association_table(db):
+ """
+ There was an error in the PrivilegeUserAssociation table that allowed for a
+ dangerous sql error. We need to the change the name of the columns to be
+ unique, and properly referenced.
+ """
+ metadata = MetaData(bind=db.bind)
+
+ privilege_user_assoc = inspect_table(
+ metadata, 'core__privileges_users')
+
+ # This whole process is more complex if we're dealing with sqlite
+ if db.bind.url.drivername == 'sqlite':
+ PrivilegeUserAssociation_R1.__table__.create(db.bind)
+ db.commit()
+
+ new_privilege_user_assoc = inspect_table(
+ metadata, 'rename__privileges_users')
+ result = db.execute(privilege_user_assoc.select())
+ for row in result:
+ # The columns were improperly named before, so we switch the columns
+ user_id, priv_id = row['core__privilege_id'], row['core__user_id']
+ db.execute(new_privilege_user_assoc.insert().values(
+ user=user_id,
+ privilege=priv_id))
+
+ db.commit()
+
+ privilege_user_assoc.drop()
+ new_privilege_user_assoc.rename('core__privileges_users')
+
+ # much simpler if postgres though!
+ else:
+ privilege_user_assoc.c.core__user_id.alter(name="privilege")
+ privilege_user_assoc.c.core__privilege_id.alter(name="user")
+
+ db.commit()
+
+
+@RegisterMigration(22, MIGRATIONS)
+def add_index_username_field(db):
+ """
+ This migration has been found to be doing the wrong thing. See
+ the documentation in migration 23 (revert_username_index) below
+ which undoes this for those databases that did run this migration.
+
+ Old description:
+ This indexes the User.username field which is frequently queried
+ for example a user logging in. This solves the issue #894
+ """
+ ## This code is left commented out *on purpose!*
+ ##
+ ## We do not normally allow commented out code like this in
+ ## MediaGoblin but this is a special case: since this migration has
+ ## been nullified but with great work to set things back below,
+ ## this is commented out for historical clarity.
+ #
+ # metadata = MetaData(bind=db.bind)
+ # user_table = inspect_table(metadata, "core__users")
+ #
+ # new_index = Index("ix_core__users_uploader", user_table.c.username)
+ # new_index.create()
+ #
+ # db.commit()
+ pass
+
+
+@RegisterMigration(23, MIGRATIONS)
+def revert_username_index(db):
+ """
+ Revert the stuff we did in migration 22 above.
+
+ There were a couple of problems with what we did:
+ - There was never a need for this migration! The unique
+ constraint had an implicit b-tree index, so it wasn't really
+ needed. (This is my (Chris Webber's) fault for suggesting it
+ needed to happen without knowing what's going on... my bad!)
+ - On top of that, databases created after the models.py was
+ changed weren't the same as those that had been run through
+ migration 22 above.
+
+ As such, we're setting things back to the way they were before,
+ but as it turns out, that's tricky to do!
+ """
+ metadata = MetaData(bind=db.bind)
+ user_table = inspect_table(metadata, "core__users")
+ indexes = dict(
+ [(index.name, index) for index in user_table.indexes])
+
+ # index from unnecessary migration
+ users_uploader_index = indexes.get(u'ix_core__users_uploader')
+ # index created from models.py after (unique=True, index=True)
+ # was set in models.py
+ users_username_index = indexes.get(u'ix_core__users_username')
+
+ if users_uploader_index is None and users_username_index is None:
+ # We don't need to do anything.
+ # The database isn't in a state where it needs fixing
+ #
+ # (ie, either went through the previous borked migration or
+ # was initialized with a models.py where core__users was both
+ # unique=True and index=True)
+ return
+
+ if db.bind.url.drivername == 'sqlite':
+ # Again, sqlite has problems. So this is tricky.
+
+ # Yes, this is correct to use User_vR1! Nothing has changed
+ # between the *correct* version of this table and migration 18.
+ User_vR1.__table__.create(db.bind)
+ db.commit()
+ new_user_table = inspect_table(metadata, 'rename__users')
+ replace_table_hack(db, user_table, new_user_table)
+
+ else:
+ # If the db is not run using SQLite, we don't need to do crazy
+ # table copying.
+
+ # Remove whichever of the not-used indexes are in place
+ if users_uploader_index is not None:
+ users_uploader_index.drop()
+ if users_username_index is not None:
+ users_username_index.drop()
+
+ # Given we're removing indexes then adding a unique constraint
+ # which *we know might fail*, thus probably rolling back the
+ # session, let's commit here.
+ db.commit()
+
+ try:
+ # Add the unique constraint
+ constraint = UniqueConstraint(
+ 'username', table=user_table)
+ constraint.create()
+ except ProgrammingError:
+ # constraint already exists, no need to add
+ db.rollback()
+
+ db.commit()