# 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
# You should have received a copy of the GNU Affero General Public License
# along with this program. If not, see <http://www.gnu.org/licenses/>.
-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()