X-Git-Url: https://vcs.fsf.org/?a=blobdiff_plain;f=mediagoblin%2Fdb%2Fmigrations.py;h=53df795458da2e6e4229d3b9233825520e87043f;hb=52a355b27541597fc155dab5e4885207b12a0a7b;hp=edaf56306670581027f998403ed13870efae79e5;hpb=839aeaa9441bba1c8764f03448edf49d5c0b59ca;p=mediagoblin.git diff --git a/mediagoblin/db/migrations.py b/mediagoblin/db/migrations.py index edaf5630..53df7954 100644 --- a/mediagoblin/db/migrations.py +++ b/mediagoblin/db/migrations.py @@ -1,5 +1,5 @@ # GNU MediaGoblin -- federated, autonomous media hosting -# Copyright (C) 2011 MediaGoblin contributors. See AUTHORS. +# Copyright (C) 2011, 2012 MediaGoblin contributors. See AUTHORS. # # This program is free software: you can redistribute it and/or modify # it under the terms of the GNU Affero General Public License as published by @@ -14,89 +14,441 @@ # You should have received a copy of the GNU Affero General Public License # along with this program. If not, see . -from mediagoblin.db.util import RegisterMigration -from mediagoblin.tools.text import cleaned_markdown_conversion +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 -def add_table_field(db, table_name, field_name, default_value): - """ - Add a new field to the table/collection named table_name. - The field will have the name field_name and the value default_value - """ - db[table_name].update( - {field_name: {'$exists': False}}, - {'$set': {field_name: default_value}}, - multi=True) +from mediagoblin.db.migration_tools import RegisterMigration, inspect_table +from mediagoblin.db.models import (MediaEntry, Collection, User, + MediaComment, Privilege, ReportBase) +MIGRATIONS = {} -# Please see mediagoblin/tests/test_migrations.py for some examples of -# basic migrations. +@RegisterMigration(1, MIGRATIONS) +def ogg_to_webm_audio(db_conn): + metadata = MetaData(bind=db_conn.bind) -@RegisterMigration(1) -def user_add_bio_html(database): - """ - Users now have richtext bios via Markdown, reflect appropriately. - """ - collection = database['users'] + file_keynames = Table('core__file_keynames', metadata, autoload=True, + autoload_with=db_conn.bind) - target = collection.find( - {'bio_html': {'$exists': False}}) + db_conn.execute( + file_keynames.update().where(file_keynames.c.name == 'ogg'). + values(name='webm_audio') + ) + db_conn.commit() - for document in target: - document['bio_html'] = cleaned_markdown_conversion( - document['bio']) - collection.save(document) +@RegisterMigration(2, MIGRATIONS) +def add_wants_notification_column(db_conn): + metadata = MetaData(bind=db_conn.bind) -@RegisterMigration(2) -def mediaentry_mediafiles_main_to_original(database): - """ - Rename "main" media file to "original". - """ - collection = database['media_entries'] - target = collection.find( - {'media_files.main': {'$exists': True}}) + users = Table('core__users', metadata, autoload=True, + autoload_with=db_conn.bind) - for document in target: - original = document['media_files'].pop('main') - document['media_files']['original'] = original + col = Column('wants_comment_notification', Boolean, + default=True, nullable=True) + col.create(users, populate_defaults=True) + db_conn.commit() - collection.save(document) +@RegisterMigration(3, MIGRATIONS) +def add_transcoding_progress(db_conn): + metadata = MetaData(bind=db_conn.bind) -@RegisterMigration(3) -def mediaentry_remove_thumbnail_file(database): - """ - Use media_files['thumb'] instead of media_entries['thumbnail_file'] - """ - database['media_entries'].update( - {'thumbnail_file': {'$exists': True}}, - {'$unset': {'thumbnail_file': 1}}, - multi=True) + media_entry = inspect_table(metadata, 'core__media_entries') + col = Column('transcoding_progress', SmallInteger) + col.create(media_entry) + db_conn.commit() -@RegisterMigration(4) -def mediaentry_add_queued_task_id(database): - """ - Add the 'queued_task_id' field for entries that don't have it. - """ - add_table_field(database, 'media_entries', 'queued_task_id', None) +class Collection_v0(declarative_base()): + __tablename__ = "core__collections" + + id = Column(Integer, primary_key=True) + title = Column(Unicode, nullable=False) + slug = Column(Unicode) + created = Column(DateTime, nullable=False, default=datetime.datetime.now, + index=True) + description = Column(UnicodeText) + creator = Column(Integer, ForeignKey(User.id), nullable=False) + items = Column(Integer, default=0) + +class CollectionItem_v0(declarative_base()): + __tablename__ = "core__collection_items" + + id = Column(Integer, primary_key=True) + media_entry = Column( + Integer, ForeignKey(MediaEntry.id), nullable=False, index=True) + collection = Column(Integer, ForeignKey(Collection.id), nullable=False) + note = Column(UnicodeText, nullable=True) + added = Column(DateTime, nullable=False, default=datetime.datetime.now) + position = Column(Integer) + + ## This should be activated, normally. + ## But this would change the way the next migration used to work. + ## So it's commented for now. + __table_args__ = ( + UniqueConstraint('collection', 'media_entry'), + {}) + +collectionitem_unique_constraint_done = False + +@RegisterMigration(4, MIGRATIONS) +def add_collection_tables(db_conn): + Collection_v0.__table__.create(db_conn.bind) + CollectionItem_v0.__table__.create(db_conn.bind) + + global collectionitem_unique_constraint_done + collectionitem_unique_constraint_done = True + + db_conn.commit() + + +@RegisterMigration(5, MIGRATIONS) +def add_mediaentry_collected(db_conn): + metadata = MetaData(bind=db_conn.bind) + + media_entry = inspect_table(metadata, 'core__media_entries') + + col = Column('collected', Integer, default=0) + col.create(media_entry) + db_conn.commit() + + +class ProcessingMetaData_v0(declarative_base()): + __tablename__ = 'core__processing_metadata' + + id = Column(Integer, primary_key=True) + media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=False, + index=True) + callback_url = Column(Unicode) -@RegisterMigration(5) -def mediaentry_add_fail_error_and_metadata(database): +@RegisterMigration(6, MIGRATIONS) +def create_processing_metadata_table(db): + ProcessingMetaData_v0.__table__.create(db.bind) + db.commit() + + +# Okay, problem being: +# Migration #4 forgot to add the uniqueconstraint for the +# new tables. While creating the tables from scratch had +# the constraint enabled. +# +# So we have four situations that should end up at the same +# db layout: +# +# 1. Fresh install. +# Well, easy. Just uses the tables in models.py +# 2. Fresh install using a git version just before this migration +# The tables are all there, the unique constraint is also there. +# This migration should do nothing. +# But as we can't detect the uniqueconstraint easily, +# this migration just adds the constraint again. +# And possibly fails very loud. But ignores the failure. +# 3. old install, not using git, just releases. +# This one will get the new tables in #4 (now with constraint!) +# And this migration is just skipped silently. +# 4. old install, always on latest git. +# This one has the tables, but lacks the constraint. +# So this migration adds the constraint. +@RegisterMigration(7, MIGRATIONS) +def fix_CollectionItem_v0_constraint(db_conn): + """Add the forgotten Constraint on CollectionItem""" + + global collectionitem_unique_constraint_done + if collectionitem_unique_constraint_done: + # Reset it. Maybe the whole thing gets run again + # For a different db? + collectionitem_unique_constraint_done = False + return + + metadata = MetaData(bind=db_conn.bind) + + CollectionItem_table = inspect_table(metadata, 'core__collection_items') + + constraint = UniqueConstraint('collection', 'media_entry', + name='core__collection_items_collection_media_entry_key', + table=CollectionItem_table) + + try: + constraint.create() + except ProgrammingError: + # User probably has an install that was run since the + # collection tables were added, so we don't need to run this migration. + pass + + db_conn.commit() + + +@RegisterMigration(8, MIGRATIONS) +def add_license_preference(db): + metadata = MetaData(bind=db.bind) + + user_table = inspect_table(metadata, 'core__users') + + col = Column('license_preference', Unicode) + col.create(user_table) + db.commit() + + +@RegisterMigration(9, MIGRATIONS) +def mediaentry_new_slug_era(db): """ - Add 'fail_error' and 'fail_metadata' fields to media entries + 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) """ - add_table_field(database, 'media_entries', 'fail_error', None) - add_table_field(database, 'media_entries', 'fail_metadata', {}) + 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(6) -def user_add_forgot_password_token_and_expires(database): + +@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): """ - Add token and expiration fields to help recover forgotten passwords + Drop unneeded columns from the User table after switching to using + itsdangerous tokens for email and forgot password verification. """ - add_table_field(database, 'users', 'fp_verification_key', None) - add_table_field(database, 'users', 'fp_token_expire', None) + 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()