X-Git-Url: https://vcs.fsf.org/?a=blobdiff_plain;f=mediagoblin%2Fdb%2Fmigrations.py;h=53df795458da2e6e4229d3b9233825520e87043f;hb=52a355b27541597fc155dab5e4885207b12a0a7b;hp=b25d577d7f479891fb1cab8fa63e1607903e5979;hpb=60a7eb9ca5f614a31f17158b791e1a6b7d7bf352;p=mediagoblin.git diff --git a/mediagoblin/db/migrations.py b/mediagoblin/db/migrations.py index b25d577d..53df7954 100644 --- a/mediagoblin/db/migrations.py +++ b/mediagoblin/db/migrations.py @@ -15,16 +15,19 @@ # along with this program. If not, see . import datetime +import uuid from sqlalchemy import (MetaData, Table, Column, Boolean, SmallInteger, Integer, Unicode, UnicodeText, DateTime, ForeignKey) from sqlalchemy.exc import ProgrammingError from sqlalchemy.ext.declarative import declarative_base +from sqlalchemy.sql import and_ from migrate.changeset.constraint import UniqueConstraint from mediagoblin.db.migration_tools import RegisterMigration, inspect_table -from mediagoblin.db.models import MediaEntry, Collection, User +from mediagoblin.db.models import (MediaEntry, Collection, User, + MediaComment, Privilege, ReportBase) MIGRATIONS = {} @@ -195,3 +198,257 @@ def add_license_preference(db): col = Column('license_preference', Unicode) col.create(user_table) db.commit() + + +@RegisterMigration(9, MIGRATIONS) +def mediaentry_new_slug_era(db): + """ + Update for the new era for media type slugs. + + Entries without slugs now display differently in the url like: + /u/cwebber/m/id=251/ + + ... because of this, we should back-convert: + - entries without slugs should be converted to use the id, if possible, to + make old urls still work + - slugs with = (or also : which is now also not allowed) to have those + stripped out (small possibility of breakage here sadly) + """ + + def slug_and_user_combo_exists(slug, uploader): + return db.execute( + media_table.select( + and_(media_table.c.uploader==uploader, + media_table.c.slug==slug))).first() is not None + + def append_garbage_till_unique(row, new_slug): + """ + Attach junk to this row until it's unique, then save it + """ + if slug_and_user_combo_exists(new_slug, row.uploader): + # okay, still no success; + # let's whack junk on there till it's unique. + new_slug += '-' + uuid.uuid4().hex[:4] + # keep going if necessary! + while slug_and_user_combo_exists(new_slug, row.uploader): + new_slug += uuid.uuid4().hex[:4] + + db.execute( + media_table.update(). \ + where(media_table.c.id==row.id). \ + values(slug=new_slug)) + + metadata = MetaData(bind=db.bind) + + media_table = inspect_table(metadata, 'core__media_entries') + + 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)) + # has "=" or ":" in it... we're getting rid of those + elif u"=" in row.slug or u":" in row.slug: + append_garbage_till_unique( + row, row.slug.replace(u"=", u"-").replace(u":", u"-")) + + db.commit() + + +@RegisterMigration(10, MIGRATIONS) +def unique_collections_slug(db): + """Add unique constraint to collection slug""" + metadata = MetaData(bind=db.bind) + collection_table = inspect_table(metadata, "core__collections") + existing_slugs = {} + slugs_to_change = [] + + for row in db.execute(collection_table.select()): + # if duplicate slug, generate a unique slug + if row.creator in existing_slugs and row.slug in \ + existing_slugs[row.creator]: + slugs_to_change.append(row.id) + else: + if not row.creator in existing_slugs: + existing_slugs[row.creator] = [row.slug] + else: + existing_slugs[row.creator].append(row.slug) + + for row_id in slugs_to_change: + new_slug = unicode(uuid.uuid4()) + db.execute(collection_table.update(). + where(collection_table.c.id == row_id). + values(slug=new_slug)) + # sqlite does not like to change the schema when a transaction(update) is + # not yet completed + db.commit() + + constraint = UniqueConstraint('creator', 'slug', + name='core__collection_creator_slug_key', + table=collection_table) + constraint.create() + + db.commit() + +@RegisterMigration(11, MIGRATIONS) +def drop_token_related_User_columns(db): + """ + Drop unneeded columns from the User table after switching to using + itsdangerous tokens for email and forgot password verification. + """ + metadata = MetaData(bind=db.bind) + user_table = inspect_table(metadata, 'core__users') + + + verification_key = user_table.columns['verification_key'] + fp_verification_key = user_table.columns['fp_verification_key'] + fp_token_expire = user_table.columns['fp_token_expire'] + + verification_key.drop() + fp_verification_key.drop() + fp_token_expire.drop() + + db.commit() + + +class CommentSubscription_v0(declarative_base()): + __tablename__ = 'core__comment_subscriptions' + id = Column(Integer, primary_key=True) + + created = Column(DateTime, nullable=False, default=datetime.datetime.now) + + media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=False) + + user_id = Column(Integer, ForeignKey(User.id), nullable=False) + + + notify = Column(Boolean, nullable=False, default=True) + send_email = Column(Boolean, nullable=False, default=True) + + +class Notification_v0(declarative_base()): + __tablename__ = 'core__notifications' + id = Column(Integer, primary_key=True) + type = Column(Unicode) + + created = Column(DateTime, nullable=False, default=datetime.datetime.now) + + user_id = Column(Integer, ForeignKey(User.id), nullable=False, + index=True) + seen = Column(Boolean, default=lambda: False, index=True) + + +class CommentNotification_v0(Notification_v0): + __tablename__ = 'core__comment_notifications' + id = Column(Integer, ForeignKey(Notification_v0.id), primary_key=True) + + subject_id = Column(Integer, ForeignKey(MediaComment.id)) + + +class ProcessingNotification_v0(Notification_v0): + __tablename__ = 'core__processing_notifications' + + id = Column(Integer, ForeignKey(Notification_v0.id), primary_key=True) + + subject_id = Column(Integer, ForeignKey(MediaEntry.id)) + + +@RegisterMigration(12, MIGRATIONS) +def add_new_notification_tables(db): + metadata = MetaData(bind=db.bind) + + user_table = inspect_table(metadata, 'core__users') + mediaentry_table = inspect_table(metadata, 'core__media_entries') + mediacomment_table = inspect_table(metadata, 'core__media_comments') + + CommentSubscription_v0.__table__.create(db.bind) + + Notification_v0.__table__.create(db.bind) + CommentNotification_v0.__table__.create(db.bind) + ProcessingNotification_v0.__table__.create(db.bind) + + +@RegisterMigration(13, MIGRATIONS) +def pw_hash_nullable(db): + """Make pw_hash column nullable""" + metadata = MetaData(bind=db.bind) + user_table = inspect_table(metadata, "core__users") + + user_table.c.pw_hash.alter(nullable=True) + + # sqlite+sqlalchemy seems to drop this constraint during the + # migration, so we add it back here for now a bit manually. + if db.bind.url.drivername == 'sqlite': + constraint = UniqueConstraint('username', table=user_table) + constraint.create() + +class ReportBase_v0(declarative_base()): + __tablename__ = 'core__reports' + id = Column(Integer, primary_key=True) + reporter_id = Column(Integer, ForeignKey(User.id), nullable=False) + report_content = Column(UnicodeText) + reported_user_id = Column(Integer, ForeignKey(User.id), nullable=False) + created = Column(DateTime, nullable=False, default=datetime.datetime.now) + discriminator = Column('type', Unicode(50)) + __mapper_args__ = {'polymorphic_on': discriminator} + +class CommentReport_v0(ReportBase_v0): + __tablename__ = 'core__reports_on_comments' + __mapper_args__ = {'polymorphic_identity': 'comment_report'} + + id = Column('id',Integer, ForeignKey('core__reports.id'), + primary_key=True) + comment_id = Column(Integer, ForeignKey(MediaComment.id), nullable=False) + +class MediaReport_v0(ReportBase_v0): + __tablename__ = 'core__reports_on_media' + __mapper_args__ = {'polymorphic_identity': 'media_report'} + + id = Column('id',Integer, ForeignKey('core__reports.id'), primary_key=True) + media_entry_id = Column(Integer, ForeignKey(MediaEntry.i + +class ArchivedReport_v0(ReportBase_v0): + __tablename__ = 'core__reports_archived' + __mapper_args__ = {'polymorphic_identity': 'archived_report'} + + id = Column('id',Integer, ForeignKey('core__reports.id')) + media_entry_id = Column(Integer, ForeignKey(MediaEntry.id)) + comment_id = Column(Integer, ForeignKey(MediaComment.id)) + resolver_id = Column(Integer, ForeignKey(User.id), nullable=False) + resolved_time = Column(DateTime) + result = Column(UnicodeText) + +class UserBan_v0(declarative_base()): + __tablename__ = 'core__user_bans' + user_id = Column('id',Integer, ForeignKey(User.id), nullable=False, + primary_key=True) + expiration_date = Column(DateTime) + reason = Column(UnicodeText, nullable=False) + +class Privilege_v0(declarative_base()): + __tablename__ = 'core__privileges' + id = Column(Integer, nullable=False, primary_key=True, unique=True) + privilege_name = Column(Unicode, nullable=False) + +class PrivilegeUserAssociation_v0(declarative_base()): + __tablename__ = 'core__privileges_users' + group_id = Column( + 'core__privilege_id', + Integer, + ForeignKey(User.id), + primary_key=True) + user_id = Column( + 'core__user_id', + Integer, + ForeignKey(Privilege.id), + primary_key=True) + +@RegisterMigration(14, MIGRATIONS) +def create_moderation_tables(db): + ReportBase_v0.__table__.create(db.bind) + CommentReport_v0.__table__.create(db.bind) + MediaReport_v0.__table__.create(db.bind) + ArchivedReport_v0.__table__.create(db.bind) + UserBan_v0.__table__.create(db.bind) + Privilege_v0.__table__.create(db.bind) + PrivilegeUserAssociation_v0.__table__.create(db.bind) + db.commit()