mediagoblin/db/migrations.py

   1 # GNU MediaGoblin -- federated, autonomous media hosting
   2 # Copyright (C) 2011, 2012 MediaGoblin contributors.  See AUTHORS.
   3 #
   4 # This program is free software: you can redistribute it and/or modify
   5 # it under the terms of the GNU Affero General Public License as published by
   6 # the Free Software Foundation, either version 3 of the License, or
   7 # (at your option) any later version.
   8 #
   9 # This program is distributed in the hope that it will be useful,
  10 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  11 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12 # GNU Affero General Public License for more details.
  13 #
  14 # You should have received a copy of the GNU Affero General Public License
  15 # along with this program.  If not, see <http://www.gnu.org/licenses/>.
  16
  17 import datetime
  18 import uuid
  19
  20 from sqlalchemy import (MetaData, Table, Column, Boolean, SmallInteger,
  21                         Integer, Unicode, UnicodeText, DateTime,
  22                         ForeignKey)
  23 from sqlalchemy.exc import ProgrammingError
  24 from sqlalchemy.ext.declarative import declarative_base
  25 from sqlalchemy.sql import and_
  26 from migrate.changeset.constraint import UniqueConstraint
  27
  28 from mediagoblin.db.migration_tools import RegisterMigration, inspect_table
  29 from mediagoblin.db.models import (MediaEntry, Collection, User,
  30                                    MediaComment, Privilege, ReportBase)
  31
  32 MIGRATIONS = {}
  33
  34
  35 @RegisterMigration(1, MIGRATIONS)
  36 def ogg_to_webm_audio(db_conn):
  37     metadata = MetaData(bind=db_conn.bind)
  38
  39     file_keynames = Table('core__file_keynames', metadata, autoload=True,
  40                           autoload_with=db_conn.bind)
  41
  42     db_conn.execute(
  43         file_keynames.update().where(file_keynames.c.name == 'ogg').
  44             values(name='webm_audio')
  45     )
  46     db_conn.commit()
  47
  48
  49 @RegisterMigration(2, MIGRATIONS)
  50 def add_wants_notification_column(db_conn):
  51     metadata = MetaData(bind=db_conn.bind)
  52
  53     users = Table('core__users', metadata, autoload=True,
  54             autoload_with=db_conn.bind)
  55
  56     col = Column('wants_comment_notification', Boolean,
  57             default=True, nullable=True)
  58     col.create(users, populate_defaults=True)
  59     db_conn.commit()
  60
  61
  62 @RegisterMigration(3, MIGRATIONS)
  63 def add_transcoding_progress(db_conn):
  64     metadata = MetaData(bind=db_conn.bind)
  65
  66     media_entry = inspect_table(metadata, 'core__media_entries')
  67
  68     col = Column('transcoding_progress', SmallInteger)
  69     col.create(media_entry)
  70     db_conn.commit()
  71
  72
  73 class Collection_v0(declarative_base()):
  74     __tablename__ = "core__collections"
  75
  76     id = Column(Integer, primary_key=True)
  77     title = Column(Unicode, nullable=False)
  78     slug = Column(Unicode)
  79     created = Column(DateTime, nullable=False, default=datetime.datetime.now,
  80         index=True)
  81     description = Column(UnicodeText)
  82     creator = Column(Integer, ForeignKey(User.id), nullable=False)
  83     items = Column(Integer, default=0)
  84
  85 class CollectionItem_v0(declarative_base()):
  86     __tablename__ = "core__collection_items"
  87
  88     id = Column(Integer, primary_key=True)
  89     media_entry = Column(
  90         Integer, ForeignKey(MediaEntry.id), nullable=False, index=True)
  91     collection = Column(Integer, ForeignKey(Collection.id), nullable=False)
  92     note = Column(UnicodeText, nullable=True)
  93     added = Column(DateTime, nullable=False, default=datetime.datetime.now)
  94     position = Column(Integer)
  95
  96     ## This should be activated, normally.
  97     ## But this would change the way the next migration used to work.
  98     ## So it's commented for now.
  99     __table_args__ = (
 100         UniqueConstraint('collection', 'media_entry'),
 101         {})
 102
 103 collectionitem_unique_constraint_done = False
 104
 105 @RegisterMigration(4, MIGRATIONS)
 106 def add_collection_tables(db_conn):
 107     Collection_v0.__table__.create(db_conn.bind)
 108     CollectionItem_v0.__table__.create(db_conn.bind)
 109
 110     global collectionitem_unique_constraint_done
 111     collectionitem_unique_constraint_done = True
 112
 113     db_conn.commit()
 114
 115
 116 @RegisterMigration(5, MIGRATIONS)
 117 def add_mediaentry_collected(db_conn):
 118     metadata = MetaData(bind=db_conn.bind)
 119
 120     media_entry = inspect_table(metadata, 'core__media_entries')
 121
 122     col = Column('collected', Integer, default=0)
 123     col.create(media_entry)
 124     db_conn.commit()
 125
 126
 127 class ProcessingMetaData_v0(declarative_base()):
 128     __tablename__ = 'core__processing_metadata'
 129
 130     id = Column(Integer, primary_key=True)
 131     media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=False,
 132             index=True)
 133     callback_url = Column(Unicode)
 134
 135 @RegisterMigration(6, MIGRATIONS)
 136 def create_processing_metadata_table(db):
 137     ProcessingMetaData_v0.__table__.create(db.bind)
 138     db.commit()
 139
 140
 141 # Okay, problem being:
 142 #  Migration #4 forgot to add the uniqueconstraint for the
 143 #  new tables. While creating the tables from scratch had
 144 #  the constraint enabled.
 145 #
 146 # So we have four situations that should end up at the same
 147 # db layout:
 148 #
 149 # 1. Fresh install.
 150 #    Well, easy. Just uses the tables in models.py
 151 # 2. Fresh install using a git version just before this migration
 152 #    The tables are all there, the unique constraint is also there.
 153 #    This migration should do nothing.
 154 #    But as we can't detect the uniqueconstraint easily,
 155 #    this migration just adds the constraint again.
 156 #    And possibly fails very loud. But ignores the failure.
 157 # 3. old install, not using git, just releases.
 158 #    This one will get the new tables in #4 (now with constraint!)
 159 #    And this migration is just skipped silently.
 160 # 4. old install, always on latest git.
 161 #    This one has the tables, but lacks the constraint.
 162 #    So this migration adds the constraint.
 163 @RegisterMigration(7, MIGRATIONS)
 164 def fix_CollectionItem_v0_constraint(db_conn):
 165     """Add the forgotten Constraint on CollectionItem"""
 166
 167     global collectionitem_unique_constraint_done
 168     if collectionitem_unique_constraint_done:
 169         # Reset it. Maybe the whole thing gets run again
 170         # For a different db?
 171         collectionitem_unique_constraint_done = False
 172         return
 173
 174     metadata = MetaData(bind=db_conn.bind)
 175
 176     CollectionItem_table = inspect_table(metadata, 'core__collection_items')
 177
 178     constraint = UniqueConstraint('collection', 'media_entry',
 179         name='core__collection_items_collection_media_entry_key',
 180         table=CollectionItem_table)
 181
 182     try:
 183         constraint.create()
 184     except ProgrammingError:
 185         # User probably has an install that was run since the
 186         # collection tables were added, so we don't need to run this migration.
 187         pass
 188
 189     db_conn.commit()
 190
 191
 192 @RegisterMigration(8, MIGRATIONS)
 193 def add_license_preference(db):
 194     metadata = MetaData(bind=db.bind)
 195
 196     user_table = inspect_table(metadata, 'core__users')
 197
 198     col = Column('license_preference', Unicode)
 199     col.create(user_table)
 200     db.commit()
 201
 202
 203 @RegisterMigration(9, MIGRATIONS)
 204 def mediaentry_new_slug_era(db):
 205     """
 206     Update for the new era for media type slugs.
 207
 208     Entries without slugs now display differently in the url like:
 209       /u/cwebber/m/id=251/
 210
 211     ... because of this, we should back-convert:
 212      - entries without slugs should be converted to use the id, if possible, to
 213        make old urls still work
 214      - slugs with = (or also : which is now also not allowed) to have those
 215        stripped out (small possibility of breakage here sadly)
 216     """
 217
 218     def slug_and_user_combo_exists(slug, uploader):
 219         return db.execute(
 220             media_table.select(
 221                 and_(media_table.c.uploader==uploader,
 222                      media_table.c.slug==slug))).first() is not None
 223
 224     def append_garbage_till_unique(row, new_slug):
 225         """
 226         Attach junk to this row until it's unique, then save it
 227         """
 228         if slug_and_user_combo_exists(new_slug, row.uploader):
 229             # okay, still no success;
 230             # let's whack junk on there till it's unique.
 231             new_slug += '-' + uuid.uuid4().hex[:4]
 232             # keep going if necessary!
 233             while slug_and_user_combo_exists(new_slug, row.uploader):
 234                 new_slug += uuid.uuid4().hex[:4]
 235
 236         db.execute(
 237             media_table.update(). \
 238             where(media_table.c.id==row.id). \
 239             values(slug=new_slug))
 240
 241     metadata = MetaData(bind=db.bind)
 242
 243     media_table = inspect_table(metadata, 'core__media_entries')
 244
 245     for row in db.execute(media_table.select()):
 246         # no slug, try setting to an id
 247         if not row.slug:
 248             append_garbage_till_unique(row, unicode(row.id))
 249         # has "=" or ":" in it... we're getting rid of those
 250         elif u"=" in row.slug or u":" in row.slug:
 251             append_garbage_till_unique(
 252                 row, row.slug.replace(u"=", u"-").replace(u":", u"-"))
 253
 254     db.commit()
 255
 256
 257 @RegisterMigration(10, MIGRATIONS)
 258 def unique_collections_slug(db):
 259     """Add unique constraint to collection slug"""
 260     metadata = MetaData(bind=db.bind)
 261     collection_table = inspect_table(metadata, "core__collections")
 262     existing_slugs = {}
 263     slugs_to_change = []
 264
 265     for row in db.execute(collection_table.select()):
 266         # if duplicate slug, generate a unique slug
 267         if row.creator in existing_slugs and row.slug in \
 268            existing_slugs[row.creator]:
 269             slugs_to_change.append(row.id)
 270         else:
 271             if not row.creator in existing_slugs:
 272                 existing_slugs[row.creator] = [row.slug]
 273             else:
 274                 existing_slugs[row.creator].append(row.slug)
 275
 276     for row_id in slugs_to_change:
 277         new_slug = unicode(uuid.uuid4())
 278         db.execute(collection_table.update().
 279                    where(collection_table.c.id == row_id).
 280                    values(slug=new_slug))
 281     # sqlite does not like to change the schema when a transaction(update) is
 282     # not yet completed
 283     db.commit()
 284
 285     constraint = UniqueConstraint('creator', 'slug',
 286                                   name='core__collection_creator_slug_key',
 287                                   table=collection_table)
 288     constraint.create()
 289
 290     db.commit()
 291
 292 @RegisterMigration(11, MIGRATIONS)
 293 def drop_token_related_User_columns(db):
 294     """
 295     Drop unneeded columns from the User table after switching to using
 296     itsdangerous tokens for email and forgot password verification.
 297     """
 298     metadata = MetaData(bind=db.bind)
 299     user_table = inspect_table(metadata, 'core__users')
 300
 301
 302     verification_key = user_table.columns['verification_key']
 303     fp_verification_key = user_table.columns['fp_verification_key']
 304     fp_token_expire = user_table.columns['fp_token_expire']
 305
 306     verification_key.drop()
 307     fp_verification_key.drop()
 308     fp_token_expire.drop()
 309
 310     db.commit()
 311
 312
 313 class CommentSubscription_v0(declarative_base()):
 314     __tablename__ = 'core__comment_subscriptions'
 315     id = Column(Integer, primary_key=True)
 316
 317     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 318
 319     media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=False)
 320
 321     user_id = Column(Integer, ForeignKey(User.id), nullable=False)
 322
 323
 324     notify = Column(Boolean, nullable=False, default=True)
 325     send_email = Column(Boolean, nullable=False, default=True)
 326
 327
 328 class Notification_v0(declarative_base()):
 329     __tablename__ = 'core__notifications'
 330     id = Column(Integer, primary_key=True)
 331     type = Column(Unicode)
 332
 333     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 334
 335     user_id = Column(Integer, ForeignKey(User.id), nullable=False,
 336                      index=True)
 337     seen = Column(Boolean, default=lambda: False, index=True)
 338
 339
 340 class CommentNotification_v0(Notification_v0):
 341     __tablename__ = 'core__comment_notifications'
 342     id = Column(Integer, ForeignKey(Notification_v0.id), primary_key=True)
 343
 344     subject_id = Column(Integer, ForeignKey(MediaComment.id))
 345
 346
 347 class ProcessingNotification_v0(Notification_v0):
 348     __tablename__ = 'core__processing_notifications'
 349
 350     id = Column(Integer, ForeignKey(Notification_v0.id), primary_key=True)
 351
 352     subject_id = Column(Integer, ForeignKey(MediaEntry.id))
 353
 354
 355 @RegisterMigration(12, MIGRATIONS)
 356 def add_new_notification_tables(db):
 357     metadata = MetaData(bind=db.bind)
 358
 359     user_table = inspect_table(metadata, 'core__users')
 360     mediaentry_table = inspect_table(metadata, 'core__media_entries')
 361     mediacomment_table = inspect_table(metadata, 'core__media_comments')
 362
 363     CommentSubscription_v0.__table__.create(db.bind)
 364
 365     Notification_v0.__table__.create(db.bind)
 366     CommentNotification_v0.__table__.create(db.bind)
 367     ProcessingNotification_v0.__table__.create(db.bind)
 368
 369
 370 @RegisterMigration(13, MIGRATIONS)
 371 def pw_hash_nullable(db):
 372     """Make pw_hash column nullable"""
 373     metadata = MetaData(bind=db.bind)
 374     user_table = inspect_table(metadata, "core__users")
 375
 376     user_table.c.pw_hash.alter(nullable=True)
 377
 378     # sqlite+sqlalchemy seems to drop this constraint during the
 379     # migration, so we add it back here for now a bit manually.
 380     if db.bind.url.drivername == 'sqlite':
 381         constraint = UniqueConstraint('username', table=user_table)
 382         constraint.create()
 383
 384 class ReportBase_v0(declarative_base()):
 385     __tablename__ = 'core__reports'
 386     id = Column(Integer, primary_key=True)
 387     reporter_id = Column(Integer, ForeignKey(User.id), nullable=False)
 388     report_content = Column(UnicodeText)
 389     reported_user_id = Column(Integer, ForeignKey(User.id), nullable=False)
 390     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 391     discriminator = Column('type', Unicode(50))
 392     __mapper_args__ = {'polymorphic_on': discriminator}
 393
 394 class CommentReport_v0(ReportBase_v0):
 395     __tablename__ = 'core__reports_on_comments'
 396     __mapper_args__ = {'polymorphic_identity': 'comment_report'}
 397
 398     id = Column('id',Integer, ForeignKey('core__reports.id'),
 399                                                 primary_key=True)
 400     comment_id = Column(Integer, ForeignKey(MediaComment.id), nullable=False)
 401
 402 class MediaReport_v0(ReportBase_v0):
 403     __tablename__ = 'core__reports_on_media'
 404     __mapper_args__ = {'polymorphic_identity': 'media_report'}
 405
 406     id = Column('id',Integer, ForeignKey('core__reports.id'), primary_key=True)
 407     media_entry_id = Column(Integer, ForeignKey(MediaEntry.i
 408
 409 class ArchivedReport_v0(ReportBase_v0):
 410     __tablename__ = 'core__reports_archived'
 411     __mapper_args__ = {'polymorphic_identity': 'archived_report'}
 412
 413     id = Column('id',Integer, ForeignKey('core__reports.id'))
 414     media_entry_id = Column(Integer, ForeignKey(MediaEntry.id))
 415     comment_id = Column(Integer, ForeignKey(MediaComment.id))
 416     resolver_id = Column(Integer, ForeignKey(User.id), nullable=False)
 417     resolved_time = Column(DateTime)
 418     result = Column(UnicodeText)
 419
 420 class UserBan_v0(declarative_base()):
 421     __tablename__ = 'core__user_bans'
 422     user_id = Column('id',Integer, ForeignKey(User.id), nullable=False,
 423                                          primary_key=True)
 424     expiration_date = Column(DateTime)
 425     reason = Column(UnicodeText, nullable=False)
 426
 427 class Privilege_v0(declarative_base()):
 428     __tablename__ = 'core__privileges'
 429     id = Column(Integer, nullable=False, primary_key=True, unique=True)
 430     privilege_name = Column(Unicode, nullable=False)
 431
 432 class PrivilegeUserAssociation_v0(declarative_base()):
 433     __tablename__ = 'core__privileges_users'
 434     group_id = Column(
 435         'core__privilege_id',
 436         Integer,
 437         ForeignKey(User.id),
 438         primary_key=True)
 439     user_id = Column(
 440         'core__user_id',
 441         Integer,
 442         ForeignKey(Privilege.id),
 443         primary_key=True)
 444
 445 @RegisterMigration(14, MIGRATIONS)
 446 def create_moderation_tables(db):
 447     ReportBase_v0.__table__.create(db.bind)
 448     CommentReport_v0.__table__.create(db.bind)
 449     MediaReport_v0.__table__.create(db.bind)
 450     ArchivedReport_v0.__table__.create(db.bind)
 451     UserBan_v0.__table__.create(db.bind)
 452     Privilege_v0.__table__.create(db.bind)
 453     PrivilegeUserAssociation_v0.__table__.create(db.bind)
 454     db.commit()