import datetime
import uuid
+import six
+
+if six.PY2:
+ import migrate
+
from sqlalchemy import (MetaData, Table, Column, Boolean, SmallInteger,
Integer, Unicode, UnicodeText, DateTime,
- ForeignKey)
+ 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_hack)
+from mediagoblin.db.models import (MediaEntry, Collection, MediaComment, User,
+ Privilege)
+from mediagoblin.db.extratypes import JSONEncoded, MutationDict
-from mediagoblin.db.extratypes import JSONEncoded
-from mediagoblin.db.migration_tools import RegisterMigration, inspect_table
-from mediagoblin.db.models import MediaEntry, Collection, User, MediaComment
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))
CommentNotification_v0.__table__.create(db.bind)
ProcessingNotification_v0.__table__.create(db.bind)
+ db.commit()
+
@RegisterMigration(13, MIGRATIONS)
def pw_hash_nullable(db):
callback = Column(Unicode, nullable=False, default=u"oob")
created = Column(DateTime, nullable=False, default=datetime.datetime.now)
updated = Column(DateTime, nullable=False, default=datetime.datetime.now)
-
+
class AccessToken_v0(declarative_base()):
"""
Model for representing the access tokens
request_token = Column(Unicode, ForeignKey(RequestToken_v0.token))
created = Column(DateTime, nullable=False, default=datetime.datetime.now)
updated = Column(DateTime, nullable=False, default=datetime.datetime.now)
-
+
class NonceTimestamp_v0(declarative_base()):
"""
NonceTimestamp_v0.__table__.create(db.bind)
db.commit()
+
+@RegisterMigration(15, MIGRATIONS)
+def wants_notifications(db):
+ """Add a wants_notifications field to User model"""
+ metadata = MetaData(bind=db.bind)
+ user_table = inspect_table(metadata, "core__users")
+ col = Column('wants_notifications', Boolean, default=True)
+ col.create(user_table)
+ db.commit()
+
+
+
+@RegisterMigration(16, MIGRATIONS)
+def upload_limits(db):
+ """Add user upload limit columns"""
+ metadata = MetaData(bind=db.bind)
+
+ user_table = inspect_table(metadata, 'core__users')
+ media_entry_table = inspect_table(metadata, 'core__media_entries')
+
+ col = Column('uploaded', Integer, default=0)
+ col.create(user_table)
+
+ col = Column('upload_limit', Integer)
+ col.create(user_table)
+
+ col = Column('file_size', Integer, default=0)
+ col.create(media_entry_table)
+
+ db.commit()
+
+
+@RegisterMigration(17, MIGRATIONS)
+def add_file_metadata(db):
+ """Add file_metadata to MediaFile"""
+ metadata = MetaData(bind=db.bind)
+ media_file_table = inspect_table(metadata, "core__mediafiles")
+
+ col = Column('file_metadata', MutationDict.as_mutable(JSONEncoded))
+ col.create(media_file_table)
+
+ db.commit()
+
+###################
+# Moderation tables
+###################
+
+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))
+ resolver_id = Column(Integer, ForeignKey(User.id))
+ resolved = Column(DateTime)
+ result = Column(UnicodeText)
+ __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=True)
+
+
+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.id), nullable=True)
+
+
+class UserBan_v0(declarative_base()):
+ __tablename__ = 'core__user_bans'
+ user_id = Column(Integer, ForeignKey(User.id), nullable=False,
+ primary_key=True)
+ expiration_date = Column(Date)
+ 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, unique=True)
+
+
+class PrivilegeUserAssociation_v0(declarative_base()):
+ __tablename__ = 'core__privileges_users'
+ privilege_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)
+
+
+PRIVILEGE_FOUNDATIONS_v0 = [{'privilege_name':u'admin'},
+ {'privilege_name':u'moderator'},
+ {'privilege_name':u'uploader'},
+ {'privilege_name':u'reporter'},
+ {'privilege_name':u'commenter'},
+ {'privilege_name':u'active'}]
+
+
+# vR1 stands for "version Rename 1". This only exists because we need
+# to deal with dropping some booleans and it's otherwise impossible
+# with sqlite.
+
+class User_vR1(declarative_base()):
+ __tablename__ = 'rename__users'
+ id = Column(Integer, primary_key=True)
+ username = Column(Unicode, nullable=False, unique=True)
+ email = Column(Unicode, nullable=False)
+ pw_hash = Column(Unicode)
+ created = Column(DateTime, nullable=False, default=datetime.datetime.now)
+ wants_comment_notification = Column(Boolean, default=True)
+ wants_notifications = Column(Boolean, default=True)
+ license_preference = Column(Unicode)
+ url = Column(Unicode)
+ bio = Column(UnicodeText) # ??
+ uploaded = Column(Integer, default=0)
+ upload_limit = Column(Integer)
+
+
+@RegisterMigration(18, MIGRATIONS)
+def create_moderation_tables(db):
+
+ # First, we will create the new tables in the database.
+ #--------------------------------------------------------------------------
+ ReportBase_v0.__table__.create(db.bind)
+ CommentReport_v0.__table__.create(db.bind)
+ MediaReport_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()
+
+ # Then initialize the tables that we will later use
+ #--------------------------------------------------------------------------
+ metadata = MetaData(bind=db.bind)
+ privileges_table= inspect_table(metadata, "core__privileges")
+ user_table = inspect_table(metadata, 'core__users')
+ user_privilege_assoc = inspect_table(
+ metadata, 'core__privileges_users')
+
+ # This section initializes the default Privilege foundations, that
+ # would be created through the FOUNDATIONS system in a new instance
+ #--------------------------------------------------------------------------
+ for parameters in PRIVILEGE_FOUNDATIONS_v0:
+ db.execute(privileges_table.insert().values(**parameters))
+
+ db.commit()
+
+ # This next section takes the information from the old is_admin and status
+ # columns and converts those to the new privilege system
+ #--------------------------------------------------------------------------
+ admin_users_ids, active_users_ids, inactive_users_ids = (
+ db.execute(
+ user_table.select().where(
+ user_table.c.is_admin==True)).fetchall(),
+ db.execute(
+ user_table.select().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==False).where(
+ user_table.c.status!=u"active")).fetchall())
+
+ # Get the ids for each of the privileges so we can reference them ~~~~~~~~~
+ (admin_privilege_id, uploader_privilege_id,
+ reporter_privilege_id, commenter_privilege_id,
+ active_privilege_id) = [
+ db.execute(privileges_table.select().where(
+ privileges_table.c.privilege_name==privilege_name)).first()['id']
+ for privilege_name in
+ [u"admin",u"uploader",u"reporter",u"commenter",u"active"]
+ ]
+
+ # Give each user the appopriate privileges depending whether they are an
+ # 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,
+ active_privilege_id]:
+ db.execute(user_privilege_assoc.insert().values(
+ core__privilege_id=admin_user_id,
+ core__user_id=privilege_id))
+
+ for active_user in active_users_ids:
+ active_user_id = active_user['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,
+ core__user_id=privilege_id))
+
+ for inactive_user in inactive_users_ids:
+ inactive_user_id = inactive_user['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,
+ core__user_id=privilege_id))
+
+ db.commit()
+
+ # And then, once the information is taken from is_admin & status columns
+ # we drop all of the vestigial columns from the User table.
+ #--------------------------------------------------------------------------
+ if db.bind.url.drivername == 'sqlite':
+ # SQLite has some issues that make it *impossible* to drop boolean
+ # columns. So, the following code is a very hacky workaround which
+ # makes it possible. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+ 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, this process is much simpler ~~~~~
+
+ status = user_table.columns['status']
+ email_verified = user_table.columns['email_verified']
+ is_admin = user_table.columns['is_admin']
+ status.drop()
+ email_verified.drop()
+ 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()
projects / mediagoblin.git / blobdiff