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
  29 from mediagoblin.db.extratypes import JSONEncoded
  30 from mediagoblin.db.migration_tools import RegisterMigration, inspect_table
  31 from mediagoblin.db.models import (MediaEntry, Collection, User,
  32                                    MediaComment, Privilege, ReportBase)
  33
  34 MIGRATIONS = {}
  35
  36
  37 @RegisterMigration(1, MIGRATIONS)
  38 def ogg_to_webm_audio(db_conn):
  39     metadata = MetaData(bind=db_conn.bind)
  40
  41     file_keynames = Table('core__file_keynames', metadata, autoload=True,
  42                           autoload_with=db_conn.bind)
  43
  44     db_conn.execute(
  45         file_keynames.update().where(file_keynames.c.name == 'ogg').
  46             values(name='webm_audio')
  47     )
  48     db_conn.commit()
  49
  50
  51 @RegisterMigration(2, MIGRATIONS)
  52 def add_wants_notification_column(db_conn):
  53     metadata = MetaData(bind=db_conn.bind)
  54
  55     users = Table('core__users', metadata, autoload=True,
  56             autoload_with=db_conn.bind)
  57
  58     col = Column('wants_comment_notification', Boolean,
  59             default=True, nullable=True)
  60     col.create(users, populate_defaults=True)
  61     db_conn.commit()
  62
  63
  64 @RegisterMigration(3, MIGRATIONS)
  65 def add_transcoding_progress(db_conn):
  66     metadata = MetaData(bind=db_conn.bind)
  67
  68     media_entry = inspect_table(metadata, 'core__media_entries')
  69
  70     col = Column('transcoding_progress', SmallInteger)
  71     col.create(media_entry)
  72     db_conn.commit()
  73
  74
  75 class Collection_v0(declarative_base()):
  76     __tablename__ = "core__collections"
  77
  78     id = Column(Integer, primary_key=True)
  79     title = Column(Unicode, nullable=False)
  80     slug = Column(Unicode)
  81     created = Column(DateTime, nullable=False, default=datetime.datetime.now,
  82         index=True)
  83     description = Column(UnicodeText)
  84     creator = Column(Integer, ForeignKey(User.id), nullable=False)
  85     items = Column(Integer, default=0)
  86
  87 class CollectionItem_v0(declarative_base()):
  88     __tablename__ = "core__collection_items"
  89
  90     id = Column(Integer, primary_key=True)
  91     media_entry = Column(
  92         Integer, ForeignKey(MediaEntry.id), nullable=False, index=True)
  93     collection = Column(Integer, ForeignKey(Collection.id), nullable=False)
  94     note = Column(UnicodeText, nullable=True)
  95     added = Column(DateTime, nullable=False, default=datetime.datetime.now)
  96     position = Column(Integer)
  97
  98     ## This should be activated, normally.
  99     ## But this would change the way the next migration used to work.
 100     ## So it's commented for now.
 101     __table_args__ = (
 102         UniqueConstraint('collection', 'media_entry'),
 103         {})
 104
 105 collectionitem_unique_constraint_done = False
 106
 107 @RegisterMigration(4, MIGRATIONS)
 108 def add_collection_tables(db_conn):
 109     Collection_v0.__table__.create(db_conn.bind)
 110     CollectionItem_v0.__table__.create(db_conn.bind)
 111
 112     global collectionitem_unique_constraint_done
 113     collectionitem_unique_constraint_done = True
 114
 115     db_conn.commit()
 116
 117
 118 @RegisterMigration(5, MIGRATIONS)
 119 def add_mediaentry_collected(db_conn):
 120     metadata = MetaData(bind=db_conn.bind)
 121
 122     media_entry = inspect_table(metadata, 'core__media_entries')
 123
 124     col = Column('collected', Integer, default=0)
 125     col.create(media_entry)
 126     db_conn.commit()
 127
 128
 129 class ProcessingMetaData_v0(declarative_base()):
 130     __tablename__ = 'core__processing_metadata'
 131
 132     id = Column(Integer, primary_key=True)
 133     media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=False,
 134             index=True)
 135     callback_url = Column(Unicode)
 136
 137 @RegisterMigration(6, MIGRATIONS)
 138 def create_processing_metadata_table(db):
 139     ProcessingMetaData_v0.__table__.create(db.bind)
 140     db.commit()
 141
 142
 143 # Okay, problem being:
 144 #  Migration #4 forgot to add the uniqueconstraint for the
 145 #  new tables. While creating the tables from scratch had
 146 #  the constraint enabled.
 147 #
 148 # So we have four situations that should end up at the same
 149 # db layout:
 150 #
 151 # 1. Fresh install.
 152 #    Well, easy. Just uses the tables in models.py
 153 # 2. Fresh install using a git version just before this migration
 154 #    The tables are all there, the unique constraint is also there.
 155 #    This migration should do nothing.
 156 #    But as we can't detect the uniqueconstraint easily,
 157 #    this migration just adds the constraint again.
 158 #    And possibly fails very loud. But ignores the failure.
 159 # 3. old install, not using git, just releases.
 160 #    This one will get the new tables in #4 (now with constraint!)
 161 #    And this migration is just skipped silently.
 162 # 4. old install, always on latest git.
 163 #    This one has the tables, but lacks the constraint.
 164 #    So this migration adds the constraint.
 165 @RegisterMigration(7, MIGRATIONS)
 166 def fix_CollectionItem_v0_constraint(db_conn):
 167     """Add the forgotten Constraint on CollectionItem"""
 168
 169     global collectionitem_unique_constraint_done
 170     if collectionitem_unique_constraint_done:
 171         # Reset it. Maybe the whole thing gets run again
 172         # For a different db?
 173         collectionitem_unique_constraint_done = False
 174         return
 175
 176     metadata = MetaData(bind=db_conn.bind)
 177
 178     CollectionItem_table = inspect_table(metadata, 'core__collection_items')
 179
 180     constraint = UniqueConstraint('collection', 'media_entry',
 181         name='core__collection_items_collection_media_entry_key',
 182         table=CollectionItem_table)
 183
 184     try:
 185         constraint.create()
 186     except ProgrammingError:
 187         # User probably has an install that was run since the
 188         # collection tables were added, so we don't need to run this migration.
 189         pass
 190
 191     db_conn.commit()
 192
 193
 194 @RegisterMigration(8, MIGRATIONS)
 195 def add_license_preference(db):
 196     metadata = MetaData(bind=db.bind)
 197
 198     user_table = inspect_table(metadata, 'core__users')
 199
 200     col = Column('license_preference', Unicode)
 201     col.create(user_table)
 202     db.commit()
 203
 204
 205 @RegisterMigration(9, MIGRATIONS)
 206 def mediaentry_new_slug_era(db):
 207     """
 208     Update for the new era for media type slugs.
 209
 210     Entries without slugs now display differently in the url like:
 211       /u/cwebber/m/id=251/
 212
 213     ... because of this, we should back-convert:
 214      - entries without slugs should be converted to use the id, if possible, to
 215        make old urls still work
 216      - slugs with = (or also : which is now also not allowed) to have those
 217        stripped out (small possibility of breakage here sadly)
 218     """
 219
 220     def slug_and_user_combo_exists(slug, uploader):
 221         return db.execute(
 222             media_table.select(
 223                 and_(media_table.c.uploader==uploader,
 224                      media_table.c.slug==slug))).first() is not None
 225
 226     def append_garbage_till_unique(row, new_slug):
 227         """
 228         Attach junk to this row until it's unique, then save it
 229         """
 230         if slug_and_user_combo_exists(new_slug, row.uploader):
 231             # okay, still no success;
 232             # let's whack junk on there till it's unique.
 233             new_slug += '-' + uuid.uuid4().hex[:4]
 234             # keep going if necessary!
 235             while slug_and_user_combo_exists(new_slug, row.uploader):
 236                 new_slug += uuid.uuid4().hex[:4]
 237
 238         db.execute(
 239             media_table.update(). \
 240             where(media_table.c.id==row.id). \
 241             values(slug=new_slug))
 242
 243     metadata = MetaData(bind=db.bind)
 244
 245     media_table = inspect_table(metadata, 'core__media_entries')
 246
 247     for row in db.execute(media_table.select()):
 248         # no slug, try setting to an id
 249         if not row.slug:
 250             append_garbage_till_unique(row, unicode(row.id))
 251         # has "=" or ":" in it... we're getting rid of those
 252         elif u"=" in row.slug or u":" in row.slug:
 253             append_garbage_till_unique(
 254                 row, row.slug.replace(u"=", u"-").replace(u":", u"-"))
 255
 256     db.commit()
 257
 258
 259 @RegisterMigration(10, MIGRATIONS)
 260 def unique_collections_slug(db):
 261     """Add unique constraint to collection slug"""
 262     metadata = MetaData(bind=db.bind)
 263     collection_table = inspect_table(metadata, "core__collections")
 264     existing_slugs = {}
 265     slugs_to_change = []
 266
 267     for row in db.execute(collection_table.select()):
 268         # if duplicate slug, generate a unique slug
 269         if row.creator in existing_slugs and row.slug in \
 270            existing_slugs[row.creator]:
 271             slugs_to_change.append(row.id)
 272         else:
 273             if not row.creator in existing_slugs:
 274                 existing_slugs[row.creator] = [row.slug]
 275             else:
 276                 existing_slugs[row.creator].append(row.slug)
 277
 278     for row_id in slugs_to_change:
 279         new_slug = unicode(uuid.uuid4())
 280         db.execute(collection_table.update().
 281                    where(collection_table.c.id == row_id).
 282                    values(slug=new_slug))
 283     # sqlite does not like to change the schema when a transaction(update) is
 284     # not yet completed
 285     db.commit()
 286
 287     constraint = UniqueConstraint('creator', 'slug',
 288                                   name='core__collection_creator_slug_key',
 289                                   table=collection_table)
 290     constraint.create()
 291
 292     db.commit()
 293
 294 @RegisterMigration(11, MIGRATIONS)
 295 def drop_token_related_User_columns(db):
 296     """
 297     Drop unneeded columns from the User table after switching to using
 298     itsdangerous tokens for email and forgot password verification.
 299     """
 300     metadata = MetaData(bind=db.bind)
 301     user_table = inspect_table(metadata, 'core__users')
 302
 303
 304     verification_key = user_table.columns['verification_key']
 305     fp_verification_key = user_table.columns['fp_verification_key']
 306     fp_token_expire = user_table.columns['fp_token_expire']
 307
 308     verification_key.drop()
 309     fp_verification_key.drop()
 310     fp_token_expire.drop()
 311
 312     db.commit()
 313
 314
 315 class CommentSubscription_v0(declarative_base()):
 316     __tablename__ = 'core__comment_subscriptions'
 317     id = Column(Integer, primary_key=True)
 318
 319     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 320
 321     media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=False)
 322
 323     user_id = Column(Integer, ForeignKey(User.id), nullable=False)
 324
 325
 326     notify = Column(Boolean, nullable=False, default=True)
 327     send_email = Column(Boolean, nullable=False, default=True)
 328
 329
 330 class Notification_v0(declarative_base()):
 331     __tablename__ = 'core__notifications'
 332     id = Column(Integer, primary_key=True)
 333     type = Column(Unicode)
 334
 335     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 336
 337     user_id = Column(Integer, ForeignKey(User.id), nullable=False,
 338                      index=True)
 339     seen = Column(Boolean, default=lambda: False, index=True)
 340
 341
 342 class CommentNotification_v0(Notification_v0):
 343     __tablename__ = 'core__comment_notifications'
 344     id = Column(Integer, ForeignKey(Notification_v0.id), primary_key=True)
 345
 346     subject_id = Column(Integer, ForeignKey(MediaComment.id))
 347
 348
 349 class ProcessingNotification_v0(Notification_v0):
 350     __tablename__ = 'core__processing_notifications'
 351
 352     id = Column(Integer, ForeignKey(Notification_v0.id), primary_key=True)
 353
 354     subject_id = Column(Integer, ForeignKey(MediaEntry.id))
 355
 356
 357 @RegisterMigration(12, MIGRATIONS)
 358 def add_new_notification_tables(db):
 359     metadata = MetaData(bind=db.bind)
 360
 361     user_table = inspect_table(metadata, 'core__users')
 362     mediaentry_table = inspect_table(metadata, 'core__media_entries')
 363     mediacomment_table = inspect_table(metadata, 'core__media_comments')
 364
 365     CommentSubscription_v0.__table__.create(db.bind)
 366
 367     Notification_v0.__table__.create(db.bind)
 368     CommentNotification_v0.__table__.create(db.bind)
 369     ProcessingNotification_v0.__table__.create(db.bind)
 370
 371
 372 @RegisterMigration(13, MIGRATIONS)
 373 def pw_hash_nullable(db):
 374     """Make pw_hash column nullable"""
 375     metadata = MetaData(bind=db.bind)
 376     user_table = inspect_table(metadata, "core__users")
 377
 378     user_table.c.pw_hash.alter(nullable=True)
 379
 380     # sqlite+sqlalchemy seems to drop this constraint during the
 381     # migration, so we add it back here for now a bit manually.
 382     if db.bind.url.drivername == 'sqlite':
 383         constraint = UniqueConstraint('username', table=user_table)
 384         constraint.create()
 385
 386 class ReportBase_v0(declarative_base()):
 387     __tablename__ = 'core__reports'
 388     id = Column(Integer, primary_key=True)
 389     reporter_id = Column(Integer, ForeignKey(User.id), nullable=False)
 390     report_content = Column(UnicodeText)
 391     reported_user_id = Column(Integer, ForeignKey(User.id), nullable=False)
 392     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 393     discriminator = Column('type', Unicode(50))
 394     __mapper_args__ = {'polymorphic_on': discriminator}
 395
 396 class CommentReport_v0(ReportBase_v0):
 397     __tablename__ = 'core__reports_on_comments'
 398     __mapper_args__ = {'polymorphic_identity': 'comment_report'}
 399
 400     id = Column('id',Integer, ForeignKey('core__reports.id'),
 401                                                 primary_key=True)
 402     comment_id = Column(Integer, ForeignKey(MediaComment.id), nullable=False)
 403
 404 class MediaReport_v0(ReportBase_v0):
 405     __tablename__ = 'core__reports_on_media'
 406     __mapper_args__ = {'polymorphic_identity': 'media_report'}
 407
 408     id = Column('id',Integer, ForeignKey('core__reports.id'), primary_key=True)
 409     media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=False)
 410
 411 class ArchivedReport_v0(ReportBase_v0):
 412     __tablename__ = 'core__reports_archived'
 413     __mapper_args__ = {'polymorphic_identity': 'archived_report'}
 414
 415     id = Column('id',Integer, ForeignKey('core__reports.id'), primary_key=True)
 416     media_entry_id = Column(Integer, ForeignKey(MediaEntry.id))
 417     comment_id = Column(Integer, ForeignKey(MediaComment.id))
 418     resolver_id = Column(Integer, ForeignKey(User.id), nullable=False)
 419     resolved_time = Column(DateTime)
 420     result = Column(UnicodeText)
 421
 422 class UserBan_v0(declarative_base()):
 423     __tablename__ = 'core__user_bans'
 424     user_id = Column('id',Integer, ForeignKey(User.id), nullable=False,
 425                                          primary_key=True)
 426     expiration_date = Column(DateTime)
 427     reason = Column(UnicodeText, nullable=False)
 428
 429 class Privilege_v0(declarative_base()):
 430     __tablename__ = 'core__privileges'
 431     id = Column(Integer, nullable=False, primary_key=True, unique=True)
 432     privilege_name = Column(Unicode, nullable=False, unique=True)
 433
 434 class PrivilegeUserAssociation_v0(declarative_base()):
 435     __tablename__ = 'core__privileges_users'
 436     group_id = Column(
 437         'core__privilege_id',
 438         Integer,
 439         ForeignKey(User.id),
 440         primary_key=True)
 441     user_id = Column(
 442         'core__user_id',
 443         Integer,
 444         ForeignKey(Privilege.id),
 445         primary_key=True)
 446
 447 @RegisterMigration(14, MIGRATIONS)
 448 def create_moderation_tables(db):
 449     ReportBase_v0.__table__.create(db.bind)
 450     CommentReport_v0.__table__.create(db.bind)
 451     MediaReport_v0.__table__.create(db.bind)
 452     ArchivedReport_v0.__table__.create(db.bind)
 453     UserBan_v0.__table__.create(db.bind)
 454     Privilege_v0.__table__.create(db.bind)
 455     PrivilegeUserAssociation_v0.__table__.create(db.bind)
 456     db.commit()
 457
 458
 459 # oauth1 migrations
 460 class Client_v0(declarative_base()):
 461     """
 462         Model representing a client - Used for API Auth
 463     """
 464     __tablename__ = "core__clients"
 465
 466     id = Column(Unicode, nullable=True, primary_key=True)
 467     secret = Column(Unicode, nullable=False)
 468     expirey = Column(DateTime, nullable=True)
 469     application_type = Column(Unicode, nullable=False)
 470     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 471     updated = Column(DateTime, nullable=False, default=datetime.datetime.now)
 472
 473     # optional stuff
 474     redirect_uri = Column(JSONEncoded, nullable=True)
 475     logo_url = Column(Unicode, nullable=True)
 476     application_name = Column(Unicode, nullable=True)
 477     contacts = Column(JSONEncoded, nullable=True)
 478
 479     def __repr__(self):
 480         if self.application_name:
 481             return "<Client {0} - {1}>".format(self.application_name, self.id)
 482         else:
 483             return "<Client {0}>".format(self.id)
 484
 485 class RequestToken_v0(declarative_base()):
 486     """
 487         Model for representing the request tokens
 488     """
 489     __tablename__ = "core__request_tokens"
 490
 491     token = Column(Unicode, primary_key=True)
 492     secret = Column(Unicode, nullable=False)
 493     client = Column(Unicode, ForeignKey(Client_v0.id))
 494     user = Column(Integer, ForeignKey(User.id), nullable=True)
 495     used = Column(Boolean, default=False)
 496     authenticated = Column(Boolean, default=False)
 497     verifier = Column(Unicode, nullable=True)
 498     callback = Column(Unicode, nullable=False, default=u"oob")
 499     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 500     updated = Column(DateTime, nullable=False, default=datetime.datetime.now)
 501
 502 class AccessToken_v0(declarative_base()):
 503     """
 504         Model for representing the access tokens
 505     """
 506     __tablename__ = "core__access_tokens"
 507
 508     token = Column(Unicode, nullable=False, primary_key=True)
 509     secret = Column(Unicode, nullable=False)
 510     user = Column(Integer, ForeignKey(User.id))
 511     request_token = Column(Unicode, ForeignKey(RequestToken_v0.token))
 512     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 513     updated = Column(DateTime, nullable=False, default=datetime.datetime.now)
 514
 515
 516 class NonceTimestamp_v0(declarative_base()):
 517     """
 518         A place the timestamp and nonce can be stored - this is for OAuth1
 519     """
 520     __tablename__ = "core__nonce_timestamps"
 521
 522     nonce = Column(Unicode, nullable=False, primary_key=True)
 523     timestamp = Column(DateTime, nullable=False, primary_key=True)
 524
 525
 526 @RegisterMigration(14, MIGRATIONS)
 527 def create_oauth1_tables(db):
 528     """ Creates the OAuth1 tables """
 529
 530     Client_v0.__table__.create(db.bind)
 531     RequestToken_v0.__table__.create(db.bind)
 532     AccessToken_v0.__table__.create(db.bind)
 533     NonceTimestamp_v0.__table__.create(db.bind)
 534
 535     db.commit()