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 from __future__ import print_function
  18
  19 import datetime
  20 import uuid
  21
  22 import six
  23
  24 try:
  25     import migrate
  26 except ImportError:
  27     # Apparently sqlalchemy-migrate is not installed, so we assume
  28     # we must not need it
  29     # TODO: Better error handling here, or require sqlalchemy-migrate
  30     print("sqlalchemy-migrate not found... assuming we don't need it")
  31     print("I hope you aren't running the legacy migrations!")
  32
  33 import pytz
  34 import dateutil.tz
  35 from sqlalchemy import (MetaData, Table, Column, Boolean, SmallInteger,
  36                         Integer, Unicode, UnicodeText, DateTime,
  37                         ForeignKey, Date, Index)
  38 from sqlalchemy.exc import ProgrammingError
  39 from sqlalchemy.ext.declarative import declarative_base
  40 from sqlalchemy.sql import and_
  41 from sqlalchemy.schema import UniqueConstraint
  42
  43 from mediagoblin import oauth
  44 from mediagoblin.tools import crypto
  45 from mediagoblin.db.extratypes import JSONEncoded, MutationDict
  46 from mediagoblin.db.migration_tools import (
  47     RegisterMigration, inspect_table, replace_table_hack, model_iteration_hack)
  48 from mediagoblin.db.models import (MediaEntry, Collection, Comment, User,
  49                                    Privilege, Generator, LocalUser, Location,
  50                                    Client, RequestToken, AccessToken)
  51 from mediagoblin.db.extratypes import JSONEncoded, MutationDict
  52
  53
  54 MIGRATIONS = {}
  55
  56
  57 @RegisterMigration(1, MIGRATIONS)
  58 def ogg_to_webm_audio(db_conn):
  59     metadata = MetaData(bind=db_conn.bind)
  60
  61     file_keynames = Table('core__file_keynames', metadata, autoload=True,
  62                           autoload_with=db_conn.bind)
  63
  64     db_conn.execute(
  65         file_keynames.update().where(file_keynames.c.name == 'ogg').
  66             values(name='webm_audio')
  67     )
  68     db_conn.commit()
  69
  70
  71 @RegisterMigration(2, MIGRATIONS)
  72 def add_wants_notification_column(db_conn):
  73     metadata = MetaData(bind=db_conn.bind)
  74
  75     users = Table('core__users', metadata, autoload=True,
  76             autoload_with=db_conn.bind)
  77
  78     col = Column('wants_comment_notification', Boolean,
  79             default=True, nullable=True)
  80     col.create(users, populate_defaults=True)
  81     db_conn.commit()
  82
  83
  84 @RegisterMigration(3, MIGRATIONS)
  85 def add_transcoding_progress(db_conn):
  86     metadata = MetaData(bind=db_conn.bind)
  87
  88     media_entry = inspect_table(metadata, 'core__media_entries')
  89
  90     col = Column('transcoding_progress', SmallInteger)
  91     col.create(media_entry)
  92     db_conn.commit()
  93
  94
  95 class Collection_v0(declarative_base()):
  96     __tablename__ = "core__collections"
  97
  98     id = Column(Integer, primary_key=True)
  99     title = Column(Unicode, nullable=False)
 100     slug = Column(Unicode)
 101     created = Column(DateTime, nullable=False, default=datetime.datetime.now,
 102         index=True)
 103     description = Column(UnicodeText)
 104     creator = Column(Integer, ForeignKey(User.id), nullable=False)
 105     items = Column(Integer, default=0)
 106
 107 class CollectionItem_v0(declarative_base()):
 108     __tablename__ = "core__collection_items"
 109
 110     id = Column(Integer, primary_key=True)
 111     media_entry = Column(
 112         Integer, ForeignKey(MediaEntry.id), nullable=False, index=True)
 113     collection = Column(Integer, ForeignKey(Collection.id), nullable=False)
 114     note = Column(UnicodeText, nullable=True)
 115     added = Column(DateTime, nullable=False, default=datetime.datetime.now)
 116     position = Column(Integer)
 117
 118     ## This should be activated, normally.
 119     ## But this would change the way the next migration used to work.
 120     ## So it's commented for now.
 121     __table_args__ = (
 122         UniqueConstraint('collection', 'media_entry'),
 123         {})
 124
 125 collectionitem_unique_constraint_done = False
 126
 127 @RegisterMigration(4, MIGRATIONS)
 128 def add_collection_tables(db_conn):
 129     Collection_v0.__table__.create(db_conn.bind)
 130     CollectionItem_v0.__table__.create(db_conn.bind)
 131
 132     global collectionitem_unique_constraint_done
 133     collectionitem_unique_constraint_done = True
 134
 135     db_conn.commit()
 136
 137
 138 @RegisterMigration(5, MIGRATIONS)
 139 def add_mediaentry_collected(db_conn):
 140     metadata = MetaData(bind=db_conn.bind)
 141
 142     media_entry = inspect_table(metadata, 'core__media_entries')
 143
 144     col = Column('collected', Integer, default=0)
 145     col.create(media_entry)
 146     db_conn.commit()
 147
 148
 149 class ProcessingMetaData_v0(declarative_base()):
 150     __tablename__ = 'core__processing_metadata'
 151
 152     id = Column(Integer, primary_key=True)
 153     media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=False,
 154             index=True)
 155     callback_url = Column(Unicode)
 156
 157 @RegisterMigration(6, MIGRATIONS)
 158 def create_processing_metadata_table(db):
 159     ProcessingMetaData_v0.__table__.create(db.bind)
 160     db.commit()
 161
 162
 163 # Okay, problem being:
 164 #  Migration #4 forgot to add the uniqueconstraint for the
 165 #  new tables. While creating the tables from scratch had
 166 #  the constraint enabled.
 167 #
 168 # So we have four situations that should end up at the same
 169 # db layout:
 170 #
 171 # 1. Fresh install.
 172 #    Well, easy. Just uses the tables in models.py
 173 # 2. Fresh install using a git version just before this migration
 174 #    The tables are all there, the unique constraint is also there.
 175 #    This migration should do nothing.
 176 #    But as we can't detect the uniqueconstraint easily,
 177 #    this migration just adds the constraint again.
 178 #    And possibly fails very loud. But ignores the failure.
 179 # 3. old install, not using git, just releases.
 180 #    This one will get the new tables in #4 (now with constraint!)
 181 #    And this migration is just skipped silently.
 182 # 4. old install, always on latest git.
 183 #    This one has the tables, but lacks the constraint.
 184 #    So this migration adds the constraint.
 185 @RegisterMigration(7, MIGRATIONS)
 186 def fix_CollectionItem_v0_constraint(db_conn):
 187     """Add the forgotten Constraint on CollectionItem"""
 188
 189     global collectionitem_unique_constraint_done
 190     if collectionitem_unique_constraint_done:
 191         # Reset it. Maybe the whole thing gets run again
 192         # For a different db?
 193         collectionitem_unique_constraint_done = False
 194         return
 195
 196     metadata = MetaData(bind=db_conn.bind)
 197
 198     CollectionItem_table = inspect_table(metadata, 'core__collection_items')
 199
 200     constraint = UniqueConstraint('collection', 'media_entry',
 201         name='core__collection_items_collection_media_entry_key',
 202         table=CollectionItem_table)
 203
 204     try:
 205         constraint.create()
 206     except ProgrammingError:
 207         # User probably has an install that was run since the
 208         # collection tables were added, so we don't need to run this migration.
 209         pass
 210
 211     db_conn.commit()
 212
 213
 214 @RegisterMigration(8, MIGRATIONS)
 215 def add_license_preference(db):
 216     metadata = MetaData(bind=db.bind)
 217
 218     user_table = inspect_table(metadata, 'core__users')
 219
 220     col = Column('license_preference', Unicode)
 221     col.create(user_table)
 222     db.commit()
 223
 224
 225 @RegisterMigration(9, MIGRATIONS)
 226 def mediaentry_new_slug_era(db):
 227     """
 228     Update for the new era for media type slugs.
 229
 230     Entries without slugs now display differently in the url like:
 231       /u/cwebber/m/id=251/
 232
 233     ... because of this, we should back-convert:
 234      - entries without slugs should be converted to use the id, if possible, to
 235        make old urls still work
 236      - slugs with = (or also : which is now also not allowed) to have those
 237        stripped out (small possibility of breakage here sadly)
 238     """
 239
 240     def slug_and_user_combo_exists(slug, uploader):
 241         return db.execute(
 242             media_table.select(
 243                 and_(media_table.c.uploader==uploader,
 244                      media_table.c.slug==slug))).first() is not None
 245
 246     def append_garbage_till_unique(row, new_slug):
 247         """
 248         Attach junk to this row until it's unique, then save it
 249         """
 250         if slug_and_user_combo_exists(new_slug, row.uploader):
 251             # okay, still no success;
 252             # let's whack junk on there till it's unique.
 253             new_slug += '-' + uuid.uuid4().hex[:4]
 254             # keep going if necessary!
 255             while slug_and_user_combo_exists(new_slug, row.uploader):
 256                 new_slug += uuid.uuid4().hex[:4]
 257
 258         db.execute(
 259             media_table.update(). \
 260             where(media_table.c.id==row.id). \
 261             values(slug=new_slug))
 262
 263     metadata = MetaData(bind=db.bind)
 264
 265     media_table = inspect_table(metadata, 'core__media_entries')
 266
 267     for row in db.execute(media_table.select()):
 268         # no slug, try setting to an id
 269         if not row.slug:
 270             append_garbage_till_unique(row, six.text_type(row.id))
 271         # has "=" or ":" in it... we're getting rid of those
 272         elif u"=" in row.slug or u":" in row.slug:
 273             append_garbage_till_unique(
 274                 row, row.slug.replace(u"=", u"-").replace(u":", u"-"))
 275
 276     db.commit()
 277
 278
 279 @RegisterMigration(10, MIGRATIONS)
 280 def unique_collections_slug(db):
 281     """Add unique constraint to collection slug"""
 282     metadata = MetaData(bind=db.bind)
 283     collection_table = inspect_table(metadata, "core__collections")
 284     existing_slugs = {}
 285     slugs_to_change = []
 286
 287     for row in db.execute(collection_table.select()):
 288         # if duplicate slug, generate a unique slug
 289         if row.creator in existing_slugs and row.slug in \
 290            existing_slugs[row.creator]:
 291             slugs_to_change.append(row.id)
 292         else:
 293             if not row.creator in existing_slugs:
 294                 existing_slugs[row.creator] = [row.slug]
 295             else:
 296                 existing_slugs[row.creator].append(row.slug)
 297
 298     for row_id in slugs_to_change:
 299         new_slug = six.text_type(uuid.uuid4())
 300         db.execute(collection_table.update().
 301                    where(collection_table.c.id == row_id).
 302                    values(slug=new_slug))
 303     # sqlite does not like to change the schema when a transaction(update) is
 304     # not yet completed
 305     db.commit()
 306
 307     constraint = UniqueConstraint('creator', 'slug',
 308                                   name='core__collection_creator_slug_key',
 309                                   table=collection_table)
 310     constraint.create()
 311
 312     db.commit()
 313
 314 @RegisterMigration(11, MIGRATIONS)
 315 def drop_token_related_User_columns(db):
 316     """
 317     Drop unneeded columns from the User table after switching to using
 318     itsdangerous tokens for email and forgot password verification.
 319     """
 320     metadata = MetaData(bind=db.bind)
 321     user_table = inspect_table(metadata, 'core__users')
 322
 323     verification_key = user_table.columns['verification_key']
 324     fp_verification_key = user_table.columns['fp_verification_key']
 325     fp_token_expire = user_table.columns['fp_token_expire']
 326
 327     verification_key.drop()
 328     fp_verification_key.drop()
 329     fp_token_expire.drop()
 330
 331     db.commit()
 332
 333
 334 class CommentSubscription_v0(declarative_base()):
 335     __tablename__ = 'core__comment_subscriptions'
 336     id = Column(Integer, primary_key=True)
 337
 338     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 339
 340     media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=False)
 341
 342     user_id = Column(Integer, ForeignKey(User.id), nullable=False)
 343
 344     notify = Column(Boolean, nullable=False, default=True)
 345     send_email = Column(Boolean, nullable=False, default=True)
 346
 347
 348 class Notification_v0(declarative_base()):
 349     __tablename__ = 'core__notifications'
 350     id = Column(Integer, primary_key=True)
 351     type = Column(Unicode)
 352
 353     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 354
 355     user_id = Column(Integer, ForeignKey(User.id), nullable=False,
 356                      index=True)
 357     seen = Column(Boolean, default=lambda: False, index=True)
 358
 359
 360 class CommentNotification_v0(Notification_v0):
 361     __tablename__ = 'core__comment_notifications'
 362     id = Column(Integer, ForeignKey(Notification_v0.id), primary_key=True)
 363
 364     subject_id = Column(Integer, ForeignKey(Comment.id))
 365
 366
 367 class ProcessingNotification_v0(Notification_v0):
 368     __tablename__ = 'core__processing_notifications'
 369
 370     id = Column(Integer, ForeignKey(Notification_v0.id), primary_key=True)
 371
 372     subject_id = Column(Integer, ForeignKey(MediaEntry.id))
 373
 374
 375 @RegisterMigration(12, MIGRATIONS)
 376 def add_new_notification_tables(db):
 377     metadata = MetaData(bind=db.bind)
 378
 379     user_table = inspect_table(metadata, 'core__users')
 380     mediaentry_table = inspect_table(metadata, 'core__media_entries')
 381     mediacomment_table = inspect_table(metadata, 'core__media_comments')
 382
 383     CommentSubscription_v0.__table__.create(db.bind)
 384
 385     Notification_v0.__table__.create(db.bind)
 386     CommentNotification_v0.__table__.create(db.bind)
 387     ProcessingNotification_v0.__table__.create(db.bind)
 388
 389     db.commit()
 390
 391
 392 @RegisterMigration(13, MIGRATIONS)
 393 def pw_hash_nullable(db):
 394     """Make pw_hash column nullable"""
 395     metadata = MetaData(bind=db.bind)
 396     user_table = inspect_table(metadata, "core__users")
 397
 398     user_table.c.pw_hash.alter(nullable=True)
 399
 400     # sqlite+sqlalchemy seems to drop this constraint during the
 401     # migration, so we add it back here for now a bit manually.
 402     if db.bind.url.drivername == 'sqlite':
 403         constraint = UniqueConstraint('username', table=user_table)
 404         constraint.create()
 405
 406     db.commit()
 407
 408
 409 # oauth1 migrations
 410 class Client_v0(declarative_base()):
 411     """
 412         Model representing a client - Used for API Auth
 413     """
 414     __tablename__ = "core__clients"
 415
 416     id = Column(Unicode, nullable=True, primary_key=True)
 417     secret = Column(Unicode, nullable=False)
 418     expirey = Column(DateTime, nullable=True)
 419     application_type = Column(Unicode, nullable=False)
 420     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 421     updated = Column(DateTime, nullable=False, default=datetime.datetime.now)
 422
 423     # optional stuff
 424     redirect_uri = Column(JSONEncoded, nullable=True)
 425     logo_url = Column(Unicode, nullable=True)
 426     application_name = Column(Unicode, nullable=True)
 427     contacts = Column(JSONEncoded, nullable=True)
 428
 429     def __repr__(self):
 430         if self.application_name:
 431             return "<Client {0} - {1}>".format(self.application_name, self.id)
 432         else:
 433             return "<Client {0}>".format(self.id)
 434
 435 class RequestToken_v0(declarative_base()):
 436     """
 437         Model for representing the request tokens
 438     """
 439     __tablename__ = "core__request_tokens"
 440
 441     token = Column(Unicode, primary_key=True)
 442     secret = Column(Unicode, nullable=False)
 443     client = Column(Unicode, ForeignKey(Client_v0.id))
 444     user = Column(Integer, ForeignKey(User.id), nullable=True)
 445     used = Column(Boolean, default=False)
 446     authenticated = Column(Boolean, default=False)
 447     verifier = Column(Unicode, nullable=True)
 448     callback = Column(Unicode, nullable=False, default=u"oob")
 449     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 450     updated = Column(DateTime, nullable=False, default=datetime.datetime.now)
 451
 452 class AccessToken_v0(declarative_base()):
 453     """
 454         Model for representing the access tokens
 455     """
 456     __tablename__ = "core__access_tokens"
 457
 458     token = Column(Unicode, nullable=False, primary_key=True)
 459     secret = Column(Unicode, nullable=False)
 460     user = Column(Integer, ForeignKey(User.id))
 461     request_token = Column(Unicode, ForeignKey(RequestToken_v0.token))
 462     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 463     updated = Column(DateTime, nullable=False, default=datetime.datetime.now)
 464
 465
 466 class NonceTimestamp_v0(declarative_base()):
 467     """
 468         A place the timestamp and nonce can be stored - this is for OAuth1
 469     """
 470     __tablename__ = "core__nonce_timestamps"
 471
 472     nonce = Column(Unicode, nullable=False, primary_key=True)
 473     timestamp = Column(DateTime, nullable=False, primary_key=True)
 474
 475
 476 @RegisterMigration(14, MIGRATIONS)
 477 def create_oauth1_tables(db):
 478     """ Creates the OAuth1 tables """
 479
 480     Client_v0.__table__.create(db.bind)
 481     RequestToken_v0.__table__.create(db.bind)
 482     AccessToken_v0.__table__.create(db.bind)
 483     NonceTimestamp_v0.__table__.create(db.bind)
 484
 485     db.commit()
 486
 487 @RegisterMigration(15, MIGRATIONS)
 488 def wants_notifications(db):
 489     """Add a wants_notifications field to User model"""
 490     metadata = MetaData(bind=db.bind)
 491     user_table = inspect_table(metadata, "core__users")
 492     col = Column('wants_notifications', Boolean, default=True)
 493     col.create(user_table)
 494     db.commit()
 495
 496
 497
 498 @RegisterMigration(16, MIGRATIONS)
 499 def upload_limits(db):
 500     """Add user upload limit columns"""
 501     metadata = MetaData(bind=db.bind)
 502
 503     user_table = inspect_table(metadata, 'core__users')
 504     media_entry_table = inspect_table(metadata, 'core__media_entries')
 505
 506     col = Column('uploaded', Integer, default=0)
 507     col.create(user_table)
 508
 509     col = Column('upload_limit', Integer)
 510     col.create(user_table)
 511
 512     col = Column('file_size', Integer, default=0)
 513     col.create(media_entry_table)
 514
 515     db.commit()
 516
 517
 518 @RegisterMigration(17, MIGRATIONS)
 519 def add_file_metadata(db):
 520     """Add file_metadata to MediaFile"""
 521     metadata = MetaData(bind=db.bind)
 522     media_file_table = inspect_table(metadata, "core__mediafiles")
 523
 524     col = Column('file_metadata', MutationDict.as_mutable(JSONEncoded))
 525     col.create(media_file_table)
 526
 527     db.commit()
 528
 529 ###################
 530 # Moderation tables
 531 ###################
 532
 533 class ReportBase_v0(declarative_base()):
 534     __tablename__ = 'core__reports'
 535     id = Column(Integer, primary_key=True)
 536     reporter_id = Column(Integer, ForeignKey(User.id), nullable=False)
 537     report_content = Column(UnicodeText)
 538     reported_user_id = Column(Integer, ForeignKey(User.id), nullable=False)
 539     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 540     discriminator = Column('type', Unicode(50))
 541     resolver_id = Column(Integer, ForeignKey(User.id))
 542     resolved = Column(DateTime)
 543     result = Column(UnicodeText)
 544     __mapper_args__ = {'polymorphic_on': discriminator}
 545
 546
 547 class CommentReport_v0(ReportBase_v0):
 548     __tablename__ = 'core__reports_on_comments'
 549     __mapper_args__ = {'polymorphic_identity': 'comment_report'}
 550
 551     id = Column('id',Integer, ForeignKey('core__reports.id'),
 552                                                 primary_key=True)
 553     comment_id = Column(Integer, ForeignKey(Comment.id), nullable=True)
 554
 555
 556 class MediaReport_v0(ReportBase_v0):
 557     __tablename__ = 'core__reports_on_media'
 558     __mapper_args__ = {'polymorphic_identity': 'media_report'}
 559
 560     id = Column('id',Integer, ForeignKey('core__reports.id'), primary_key=True)
 561     media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=True)
 562
 563
 564 class UserBan_v0(declarative_base()):
 565     __tablename__ = 'core__user_bans'
 566     user_id = Column(Integer, ForeignKey(User.id), nullable=False,
 567                                          primary_key=True)
 568     expiration_date = Column(Date)
 569     reason = Column(UnicodeText, nullable=False)
 570
 571
 572 class Privilege_v0(declarative_base()):
 573     __tablename__ = 'core__privileges'
 574     id = Column(Integer, nullable=False, primary_key=True, unique=True)
 575     privilege_name = Column(Unicode, nullable=False, unique=True)
 576
 577
 578 class PrivilegeUserAssociation_v0(declarative_base()):
 579     __tablename__ = 'core__privileges_users'
 580     privilege_id = Column(
 581         'core__privilege_id',
 582         Integer,
 583         ForeignKey(User.id),
 584         primary_key=True)
 585     user_id = Column(
 586         'core__user_id',
 587         Integer,
 588         ForeignKey(Privilege.id),
 589         primary_key=True)
 590
 591
 592 PRIVILEGE_FOUNDATIONS_v0 = [{'privilege_name':u'admin'},
 593                             {'privilege_name':u'moderator'},
 594                             {'privilege_name':u'uploader'},
 595                             {'privilege_name':u'reporter'},
 596                             {'privilege_name':u'commenter'},
 597                             {'privilege_name':u'active'}]
 598
 599 # vR1 stands for "version Rename 1".  This only exists because we need
 600 # to deal with dropping some booleans and it's otherwise impossible
 601 # with sqlite.
 602
 603 class User_vR1(declarative_base()):
 604     __tablename__ = 'rename__users'
 605     id = Column(Integer, primary_key=True)
 606     username = Column(Unicode, nullable=False, unique=True)
 607     email = Column(Unicode, nullable=False)
 608     pw_hash = Column(Unicode)
 609     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 610     wants_comment_notification = Column(Boolean, default=True)
 611     wants_notifications = Column(Boolean, default=True)
 612     license_preference = Column(Unicode)
 613     url = Column(Unicode)
 614     bio = Column(UnicodeText)  # ??
 615     uploaded = Column(Integer, default=0)
 616     upload_limit = Column(Integer)
 617
 618
 619 @RegisterMigration(18, MIGRATIONS)
 620 def create_moderation_tables(db):
 621
 622     # First, we will create the new tables in the database.
 623     #--------------------------------------------------------------------------
 624     ReportBase_v0.__table__.create(db.bind)
 625     CommentReport_v0.__table__.create(db.bind)
 626     MediaReport_v0.__table__.create(db.bind)
 627     UserBan_v0.__table__.create(db.bind)
 628     Privilege_v0.__table__.create(db.bind)
 629     PrivilegeUserAssociation_v0.__table__.create(db.bind)
 630
 631     db.commit()
 632
 633     # Then initialize the tables that we will later use
 634     #--------------------------------------------------------------------------
 635     metadata = MetaData(bind=db.bind)
 636     privileges_table= inspect_table(metadata, "core__privileges")
 637     user_table = inspect_table(metadata, 'core__users')
 638     user_privilege_assoc = inspect_table(
 639         metadata, 'core__privileges_users')
 640
 641     # This section initializes the default Privilege foundations, that
 642     # would be created through the FOUNDATIONS system in a new instance
 643     #--------------------------------------------------------------------------
 644     for parameters in PRIVILEGE_FOUNDATIONS_v0:
 645         db.execute(privileges_table.insert().values(**parameters))
 646
 647     db.commit()
 648
 649     # This next section takes the information from the old is_admin and status
 650     # columns and converts those to the new privilege system
 651     #--------------------------------------------------------------------------
 652     admin_users_ids, active_users_ids, inactive_users_ids = (
 653         db.execute(
 654             user_table.select().where(
 655                 user_table.c.is_admin==True)).fetchall(),
 656         db.execute(
 657             user_table.select().where(
 658                 user_table.c.is_admin==False).where(
 659                 user_table.c.status==u"active")).fetchall(),
 660         db.execute(
 661             user_table.select().where(
 662                 user_table.c.is_admin==False).where(
 663                 user_table.c.status!=u"active")).fetchall())
 664
 665     # Get the ids for each of the privileges so we can reference them ~~~~~~~~~
 666     (admin_privilege_id, uploader_privilege_id,
 667      reporter_privilege_id, commenter_privilege_id,
 668      active_privilege_id) = [
 669         db.execute(privileges_table.select().where(
 670             privileges_table.c.privilege_name==privilege_name)).first()['id']
 671         for privilege_name in
 672             [u"admin",u"uploader",u"reporter",u"commenter",u"active"]
 673     ]
 674
 675     # Give each user the appopriate privileges depending whether they are an
 676     # admin, an active user or an inactive user ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 677     for admin_user in admin_users_ids:
 678         admin_user_id = admin_user['id']
 679         for privilege_id in [admin_privilege_id, uploader_privilege_id,
 680                             reporter_privilege_id, commenter_privilege_id,
 681                             active_privilege_id]:
 682             db.execute(user_privilege_assoc.insert().values(
 683                 core__privilege_id=admin_user_id,
 684                 core__user_id=privilege_id))
 685
 686     for active_user in active_users_ids:
 687         active_user_id = active_user['id']
 688         for privilege_id in [uploader_privilege_id, reporter_privilege_id,
 689                             commenter_privilege_id, active_privilege_id]:
 690             db.execute(user_privilege_assoc.insert().values(
 691                 core__privilege_id=active_user_id,
 692                 core__user_id=privilege_id))
 693
 694     for inactive_user in inactive_users_ids:
 695         inactive_user_id = inactive_user['id']
 696         for privilege_id in [uploader_privilege_id, reporter_privilege_id,
 697                              commenter_privilege_id]:
 698             db.execute(user_privilege_assoc.insert().values(
 699                 core__privilege_id=inactive_user_id,
 700                 core__user_id=privilege_id))
 701
 702     db.commit()
 703
 704     # And then, once the information is taken from is_admin & status columns
 705     # we drop all of the vestigial columns from the User table.
 706     #--------------------------------------------------------------------------
 707     if db.bind.url.drivername == 'sqlite':
 708         # SQLite has some issues that make it *impossible* to drop boolean
 709         # columns. So, the following code is a very hacky workaround which
 710         # makes it possible. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 711
 712         User_vR1.__table__.create(db.bind)
 713         db.commit()
 714         new_user_table = inspect_table(metadata, 'rename__users')
 715         replace_table_hack(db, user_table, new_user_table)
 716     else:
 717         # If the db is not run using SQLite, this process is much simpler ~~~~~
 718
 719         status = user_table.columns['status']
 720         email_verified = user_table.columns['email_verified']
 721         is_admin = user_table.columns['is_admin']
 722         status.drop()
 723         email_verified.drop()
 724         is_admin.drop()
 725
 726     db.commit()
 727
 728
 729 @RegisterMigration(19, MIGRATIONS)
 730 def drop_MediaEntry_collected(db):
 731     """
 732     Drop unused MediaEntry.collected column
 733     """
 734     metadata = MetaData(bind=db.bind)
 735
 736     media_collected= inspect_table(metadata, 'core__media_entries')
 737     media_collected = media_collected.columns['collected']
 738
 739     media_collected.drop()
 740
 741     db.commit()
 742
 743
 744 @RegisterMigration(20, MIGRATIONS)
 745 def add_metadata_column(db):
 746     metadata = MetaData(bind=db.bind)
 747
 748     media_entry = inspect_table(metadata, 'core__media_entries')
 749
 750     col = Column('media_metadata', MutationDict.as_mutable(JSONEncoded),
 751         default=MutationDict())
 752     col.create(media_entry)
 753
 754     db.commit()
 755
 756
 757 class PrivilegeUserAssociation_R1(declarative_base()):
 758     __tablename__ = 'rename__privileges_users'
 759     user = Column(
 760         "user",
 761         Integer,
 762         ForeignKey(User.id),
 763         primary_key=True)
 764     privilege = Column(
 765         "privilege",
 766         Integer,
 767         ForeignKey(Privilege.id),
 768         primary_key=True)
 769
 770 @RegisterMigration(21, MIGRATIONS)
 771 def fix_privilege_user_association_table(db):
 772     """
 773     There was an error in the PrivilegeUserAssociation table that allowed for a
 774     dangerous sql error. We need to the change the name of the columns to be
 775     unique, and properly referenced.
 776     """
 777     metadata = MetaData(bind=db.bind)
 778
 779     privilege_user_assoc = inspect_table(
 780         metadata, 'core__privileges_users')
 781
 782     # This whole process is more complex if we're dealing with sqlite
 783     if db.bind.url.drivername == 'sqlite':
 784         PrivilegeUserAssociation_R1.__table__.create(db.bind)
 785         db.commit()
 786
 787         new_privilege_user_assoc = inspect_table(
 788             metadata, 'rename__privileges_users')
 789         result = db.execute(privilege_user_assoc.select())
 790         for row in result:
 791             # The columns were improperly named before, so we switch the columns
 792             user_id, priv_id = row['core__privilege_id'], row['core__user_id']
 793             db.execute(new_privilege_user_assoc.insert().values(
 794                 user=user_id,
 795                 privilege=priv_id))
 796
 797         db.commit()
 798
 799         privilege_user_assoc.drop()
 800         new_privilege_user_assoc.rename('core__privileges_users')
 801
 802     # much simpler if postgres though!
 803     else:
 804         privilege_user_assoc.c.core__user_id.alter(name="privilege")
 805         privilege_user_assoc.c.core__privilege_id.alter(name="user")
 806
 807     db.commit()
 808
 809
 810 @RegisterMigration(22, MIGRATIONS)
 811 def add_index_username_field(db):
 812     """
 813     This migration has been found to be doing the wrong thing.  See
 814     the documentation in migration 23 (revert_username_index) below
 815     which undoes this for those databases that did run this migration.
 816
 817     Old description:
 818       This indexes the User.username field which is frequently queried
 819       for example a user logging in. This solves the issue #894
 820     """
 821     ## This code is left commented out *on purpose!*
 822     ##
 823     ## We do not normally allow commented out code like this in
 824     ## MediaGoblin but this is a special case: since this migration has
 825     ## been nullified but with great work to set things back below,
 826     ## this is commented out for historical clarity.
 827     #
 828     # metadata = MetaData(bind=db.bind)
 829     # user_table = inspect_table(metadata, "core__users")
 830     #
 831     # new_index = Index("ix_core__users_uploader", user_table.c.username)
 832     # new_index.create()
 833     #
 834     # db.commit()
 835     pass
 836
 837
 838 @RegisterMigration(23, MIGRATIONS)
 839 def revert_username_index(db):
 840     """
 841     Revert the stuff we did in migration 22 above.
 842
 843     There were a couple of problems with what we did:
 844      - There was never a need for this migration!  The unique
 845        constraint had an implicit b-tree index, so it wasn't really
 846        needed.  (This is my (Chris Webber's) fault for suggesting it
 847        needed to happen without knowing what's going on... my bad!)
 848      - On top of that, databases created after the models.py was
 849        changed weren't the same as those that had been run through
 850        migration 22 above.
 851
 852     As such, we're setting things back to the way they were before,
 853     but as it turns out, that's tricky to do!
 854     """
 855     metadata = MetaData(bind=db.bind)
 856     user_table = inspect_table(metadata, "core__users")
 857     indexes = dict(
 858         [(index.name, index) for index in user_table.indexes])
 859
 860     # index from unnecessary migration
 861     users_uploader_index = indexes.get(u'ix_core__users_uploader')
 862     # index created from models.py after (unique=True, index=True)
 863     # was set in models.py
 864     users_username_index = indexes.get(u'ix_core__users_username')
 865
 866     if users_uploader_index is None and users_username_index is None:
 867         # We don't need to do anything.
 868         # The database isn't in a state where it needs fixing
 869         #
 870         # (ie, either went through the previous borked migration or
 871         #  was initialized with a models.py where core__users was both
 872         #  unique=True and index=True)
 873         return
 874
 875     if db.bind.url.drivername == 'sqlite':
 876         # Again, sqlite has problems.  So this is tricky.
 877
 878         # Yes, this is correct to use User_vR1!  Nothing has changed
 879         # between the *correct* version of this table and migration 18.
 880         User_vR1.__table__.create(db.bind)
 881         db.commit()
 882         new_user_table = inspect_table(metadata, 'rename__users')
 883         replace_table_hack(db, user_table, new_user_table)
 884
 885     else:
 886         # If the db is not run using SQLite, we don't need to do crazy
 887         # table copying.
 888
 889         # Remove whichever of the not-used indexes are in place
 890         if users_uploader_index is not None:
 891             users_uploader_index.drop()
 892         if users_username_index is not None:
 893             users_username_index.drop()
 894
 895         # Given we're removing indexes then adding a unique constraint
 896         # which *we know might fail*, thus probably rolling back the
 897         # session, let's commit here.
 898         db.commit()
 899
 900         try:
 901             # Add the unique constraint
 902             constraint = UniqueConstraint(
 903                 'username', table=user_table)
 904             constraint.create()
 905         except ProgrammingError:
 906             # constraint already exists, no need to add
 907             db.rollback()
 908
 909     db.commit()
 910
 911 class Generator_R0(declarative_base()):
 912     __tablename__ = "core__generators"
 913     id = Column(Integer, primary_key=True)
 914     name = Column(Unicode, nullable=False)
 915     published = Column(DateTime, nullable=False, default=datetime.datetime.now)
 916     updated = Column(DateTime, nullable=False, default=datetime.datetime.now)
 917     object_type = Column(Unicode, nullable=False)
 918
 919 class ActivityIntermediator_R0(declarative_base()):
 920     __tablename__ = "core__activity_intermediators"
 921     id = Column(Integer, primary_key=True)
 922     type = Column(Unicode, nullable=False)
 923
 924     # These are needed for migration 29
 925     TABLENAMES = {
 926         "user": "core__users",
 927         "media": "core__media_entries",
 928         "comment": "core__media_comments",
 929         "collection": "core__collections",
 930     }
 931
 932 class Activity_R0(declarative_base()):
 933     __tablename__ = "core__activities"
 934     id = Column(Integer, primary_key=True)
 935     actor = Column(Integer, ForeignKey(User.id), nullable=False)
 936     published = Column(DateTime, nullable=False, default=datetime.datetime.now)
 937     updated = Column(DateTime, nullable=False, default=datetime.datetime.now)
 938     verb = Column(Unicode, nullable=False)
 939     content = Column(Unicode, nullable=True)
 940     title = Column(Unicode, nullable=True)
 941     generator = Column(Integer, ForeignKey(Generator_R0.id), nullable=True)
 942     object = Column(Integer,
 943                     ForeignKey(ActivityIntermediator_R0.id),
 944                     nullable=False)
 945     target = Column(Integer,
 946                     ForeignKey(ActivityIntermediator_R0.id),
 947                     nullable=True)
 948
 949
 950 @RegisterMigration(24, MIGRATIONS)
 951 def activity_migration(db):
 952     """
 953     Creates everything to create activities in GMG
 954     - Adds Activity, ActivityIntermediator and Generator table
 955     - Creates GMG service generator for activities produced by the server
 956     - Adds the activity_as_object and activity_as_target to objects/targets
 957     - Retroactively adds activities for what we can acurately work out
 958     """
 959     # Set constants we'll use later
 960     FOREIGN_KEY = "core__activity_intermediators.id"
 961     ACTIVITY_COLUMN = "activity"
 962
 963     # Create the new tables.
 964     ActivityIntermediator_R0.__table__.create(db.bind)
 965     Generator_R0.__table__.create(db.bind)
 966     Activity_R0.__table__.create(db.bind)
 967     db.commit()
 968
 969     # Initiate the tables we want to use later
 970     metadata = MetaData(bind=db.bind)
 971     user_table = inspect_table(metadata, "core__users")
 972     activity_table = inspect_table(metadata, "core__activities")
 973     generator_table = inspect_table(metadata, "core__generators")
 974     collection_table = inspect_table(metadata, "core__collections")
 975     media_entry_table = inspect_table(metadata, "core__media_entries")
 976     media_comments_table = inspect_table(metadata, "core__media_comments")
 977     ai_table = inspect_table(metadata, "core__activity_intermediators")
 978
 979
 980     # Create the foundations for Generator
 981     db.execute(generator_table.insert().values(
 982         name="GNU Mediagoblin",
 983         object_type="service",
 984         published=datetime.datetime.now(),
 985         updated=datetime.datetime.now()
 986     ))
 987     db.commit()
 988
 989     # Get the ID of that generator
 990     gmg_generator = db.execute(generator_table.select(
 991         generator_table.c.name==u"GNU Mediagoblin")).first()
 992
 993
 994     # Now we want to modify the tables which MAY have an activity at some point
 995     media_col = Column(ACTIVITY_COLUMN, Integer, ForeignKey(FOREIGN_KEY))
 996     media_col.create(media_entry_table)
 997
 998     user_col = Column(ACTIVITY_COLUMN, Integer, ForeignKey(FOREIGN_KEY))
 999     user_col.create(user_table)
1000
1001     comments_col = Column(ACTIVITY_COLUMN, Integer, ForeignKey(FOREIGN_KEY))
1002     comments_col.create(media_comments_table)
1003
1004     collection_col = Column(ACTIVITY_COLUMN, Integer, ForeignKey(FOREIGN_KEY))
1005     collection_col.create(collection_table)
1006     db.commit()
1007
1008
1009     # Now we want to retroactively add what activities we can
1010     # first we'll add activities when people uploaded media.
1011     # these can't have content as it's not fesible to get the
1012     # correct content strings.
1013     for media in db.execute(media_entry_table.select()):
1014         # Now we want to create the intermedaitory
1015         db_ai = db.execute(ai_table.insert().values(
1016             type="media",
1017         ))
1018         db_ai = db.execute(ai_table.select(
1019             ai_table.c.id==db_ai.inserted_primary_key[0]
1020         )).first()
1021
1022         # Add the activity
1023         activity = {
1024             "verb": "create",
1025             "actor": media.uploader,
1026             "published": media.created,
1027             "updated": media.created,
1028             "generator": gmg_generator.id,
1029             "object": db_ai.id
1030         }
1031         db.execute(activity_table.insert().values(**activity))
1032
1033         # Add the AI to the media.
1034         db.execute(media_entry_table.update().values(
1035             activity=db_ai.id
1036         ).where(media_entry_table.c.id==media.id))
1037
1038     # Now we want to add all the comments people made
1039     for comment in db.execute(media_comments_table.select()):
1040         # Get the MediaEntry for the comment
1041         media_entry = db.execute(
1042             media_entry_table.select(
1043                 media_entry_table.c.id==comment.media_entry
1044         )).first()
1045
1046         # Create an AI for target
1047         db_ai_media = db.execute(ai_table.select(
1048             ai_table.c.id==media_entry.activity
1049         )).first().id
1050
1051         db.execute(
1052             media_comments_table.update().values(
1053                 activity=db_ai_media
1054         ).where(media_comments_table.c.id==media_entry.id))
1055
1056         # Now create the AI for the comment
1057         db_ai_comment = db.execute(ai_table.insert().values(
1058             type="comment"
1059         )).inserted_primary_key[0]
1060
1061         activity = {
1062             "verb": "comment",
1063             "actor": comment.author,
1064             "published": comment.created,
1065             "updated": comment.created,
1066             "generator": gmg_generator.id,
1067             "object": db_ai_comment,
1068             "target": db_ai_media,
1069         }
1070
1071         # Now add the comment object
1072         db.execute(activity_table.insert().values(**activity))
1073
1074         # Now add activity to comment
1075         db.execute(media_comments_table.update().values(
1076             activity=db_ai_comment
1077         ).where(media_comments_table.c.id==comment.id))
1078
1079     # Create 'create' activities for all collections
1080     for collection in db.execute(collection_table.select()):
1081         # create AI
1082         db_ai = db.execute(ai_table.insert().values(
1083             type="collection"
1084         ))
1085         db_ai = db.execute(ai_table.select(
1086             ai_table.c.id==db_ai.inserted_primary_key[0]
1087         )).first()
1088
1089         # Now add link the collection to the AI
1090         db.execute(collection_table.update().values(
1091             activity=db_ai.id
1092         ).where(collection_table.c.id==collection.id))
1093
1094         activity = {
1095             "verb": "create",
1096             "actor": collection.creator,
1097             "published": collection.created,
1098             "updated": collection.created,
1099             "generator": gmg_generator.id,
1100             "object": db_ai.id,
1101         }
1102
1103         db.execute(activity_table.insert().values(**activity))
1104
1105         # Now add the activity to the collection
1106         db.execute(collection_table.update().values(
1107             activity=db_ai.id
1108         ).where(collection_table.c.id==collection.id))
1109
1110     db.commit()
1111
1112 class Location_V0(declarative_base()):
1113     __tablename__ = "core__locations"
1114     id = Column(Integer, primary_key=True)
1115     name = Column(Unicode)
1116     position = Column(MutationDict.as_mutable(JSONEncoded))
1117     address = Column(MutationDict.as_mutable(JSONEncoded))
1118
1119 @RegisterMigration(25, MIGRATIONS)
1120 def add_location_model(db):
1121     """ Add location model """
1122     metadata = MetaData(bind=db.bind)
1123
1124     # Create location table
1125     Location_V0.__table__.create(db.bind)
1126     db.commit()
1127
1128     # Inspect the tables we need
1129     user = inspect_table(metadata, "core__users")
1130     collections = inspect_table(metadata, "core__collections")
1131     media_entry = inspect_table(metadata, "core__media_entries")
1132     media_comments = inspect_table(metadata, "core__media_comments")
1133
1134     # Now add location support to the various models
1135     col = Column("location", Integer, ForeignKey(Location_V0.id))
1136     col.create(user)
1137
1138     col = Column("location", Integer, ForeignKey(Location_V0.id))
1139     col.create(collections)
1140
1141     col = Column("location", Integer, ForeignKey(Location_V0.id))
1142     col.create(media_entry)
1143
1144     col = Column("location", Integer, ForeignKey(Location_V0.id))
1145     col.create(media_comments)
1146
1147     db.commit()
1148
1149 @RegisterMigration(26, MIGRATIONS)
1150 def datetime_to_utc(db):
1151     """ Convert datetime stamps to UTC """
1152     # Get the server's timezone, this is what the database has stored
1153     server_timezone = dateutil.tz.tzlocal()
1154
1155     ##
1156     # Look up all the timestamps and convert them to UTC
1157     ##
1158     metadata = MetaData(bind=db.bind)
1159
1160     def dt_to_utc(dt):
1161         # Add the current timezone
1162         dt = dt.replace(tzinfo=server_timezone)
1163
1164         # Convert to UTC
1165         return dt.astimezone(pytz.UTC)
1166
1167     # Convert the User model
1168     user_table = inspect_table(metadata, "core__users")
1169     for user in db.execute(user_table.select()):
1170         db.execute(user_table.update().values(
1171             created=dt_to_utc(user.created)
1172         ).where(user_table.c.id==user.id))
1173
1174     # Convert Client
1175     client_table = inspect_table(metadata, "core__clients")
1176     for client in db.execute(client_table.select()):
1177         db.execute(client_table.update().values(
1178             created=dt_to_utc(client.created),
1179             updated=dt_to_utc(client.updated)
1180         ).where(client_table.c.id==client.id))
1181
1182     # Convert RequestToken
1183     rt_table = inspect_table(metadata, "core__request_tokens")
1184     for request_token in db.execute(rt_table.select()):
1185         db.execute(rt_table.update().values(
1186             created=dt_to_utc(request_token.created),
1187             updated=dt_to_utc(request_token.updated)
1188         ).where(rt_table.c.token==request_token.token))
1189
1190     # Convert AccessToken
1191     at_table = inspect_table(metadata, "core__access_tokens")
1192     for access_token in db.execute(at_table.select()):
1193         db.execute(at_table.update().values(
1194             created=dt_to_utc(access_token.created),
1195             updated=dt_to_utc(access_token.updated)
1196         ).where(at_table.c.token==access_token.token))
1197
1198     # Convert MediaEntry
1199     media_table = inspect_table(metadata, "core__media_entries")
1200     for media in db.execute(media_table.select()):
1201         db.execute(media_table.update().values(
1202             created=dt_to_utc(media.created)
1203         ).where(media_table.c.id==media.id))
1204
1205     # Convert Media Attachment File
1206     media_attachment_table = inspect_table(metadata, "core__attachment_files")
1207     for ma in db.execute(media_attachment_table.select()):
1208         db.execute(media_attachment_table.update().values(
1209             created=dt_to_utc(ma.created)
1210         ).where(media_attachment_table.c.id==ma.id))
1211
1212     # Convert MediaComment
1213     comment_table = inspect_table(metadata, "core__media_comments")
1214     for comment in db.execute(comment_table.select()):
1215         db.execute(comment_table.update().values(
1216             created=dt_to_utc(comment.created)
1217         ).where(comment_table.c.id==comment.id))
1218
1219     # Convert Collection
1220     collection_table = inspect_table(metadata, "core__collections")
1221     for collection in db.execute(collection_table.select()):
1222         db.execute(collection_table.update().values(
1223             created=dt_to_utc(collection.created)
1224         ).where(collection_table.c.id==collection.id))
1225
1226     # Convert Collection Item
1227     collection_item_table = inspect_table(metadata, "core__collection_items")
1228     for ci in db.execute(collection_item_table.select()):
1229         db.execute(collection_item_table.update().values(
1230             added=dt_to_utc(ci.added)
1231         ).where(collection_item_table.c.id==ci.id))
1232
1233     # Convert Comment subscription
1234     comment_sub = inspect_table(metadata, "core__comment_subscriptions")
1235     for sub in db.execute(comment_sub.select()):
1236         db.execute(comment_sub.update().values(
1237             created=dt_to_utc(sub.created)
1238         ).where(comment_sub.c.id==sub.id))
1239
1240     # Convert Notification
1241     notification_table = inspect_table(metadata, "core__notifications")
1242     for notification in db.execute(notification_table.select()):
1243         db.execute(notification_table.update().values(
1244             created=dt_to_utc(notification.created)
1245         ).where(notification_table.c.id==notification.id))
1246
1247     # Convert ReportBase
1248     reportbase_table = inspect_table(metadata, "core__reports")
1249     for report in db.execute(reportbase_table.select()):
1250         db.execute(reportbase_table.update().values(
1251             created=dt_to_utc(report.created)
1252         ).where(reportbase_table.c.id==report.id))
1253
1254     # Convert Generator
1255     generator_table = inspect_table(metadata, "core__generators")
1256     for generator in db.execute(generator_table.select()):
1257         db.execute(generator_table.update().values(
1258             published=dt_to_utc(generator.published),
1259             updated=dt_to_utc(generator.updated)
1260         ).where(generator_table.c.id==generator.id))
1261
1262     # Convert Activity
1263     activity_table = inspect_table(metadata, "core__activities")
1264     for activity in db.execute(activity_table.select()):
1265         db.execute(activity_table.update().values(
1266             published=dt_to_utc(activity.published),
1267             updated=dt_to_utc(activity.updated)
1268         ).where(activity_table.c.id==activity.id))
1269
1270     # Commit this to the database
1271     db.commit()
1272
1273 ##
1274 # Migrations to handle migrating from activity specific foreign key to the
1275 # new GenericForeignKey implementations. They have been split up to improve
1276 # readability and minimise errors
1277 ##
1278
1279 class GenericModelReference_V0(declarative_base()):
1280     __tablename__ = "core__generic_model_reference"
1281
1282     id = Column(Integer, primary_key=True)
1283     obj_pk = Column(Integer, nullable=False)
1284     model_type = Column(Unicode, nullable=False)
1285
1286 @RegisterMigration(27, MIGRATIONS)
1287 def create_generic_model_reference(db):
1288     """ Creates the Generic Model Reference table """
1289     GenericModelReference_V0.__table__.create(db.bind)
1290     db.commit()
1291
1292 @RegisterMigration(28, MIGRATIONS)
1293 def add_foreign_key_fields(db):
1294     """
1295     Add the fields for GenericForeignKey to the model under temporary name,
1296     this is so that later a data migration can occur. They will be renamed to
1297     the origional names.
1298     """
1299     metadata = MetaData(bind=db.bind)
1300     activity_table = inspect_table(metadata, "core__activities")
1301
1302     # Create column and add to model.
1303     object_column = Column("temp_object", Integer, ForeignKey(GenericModelReference_V0.id))
1304     object_column.create(activity_table)
1305
1306     target_column = Column("temp_target", Integer, ForeignKey(GenericModelReference_V0.id))
1307     target_column.create(activity_table)
1308
1309     # Commit this to the database
1310     db.commit()
1311
1312 @RegisterMigration(29, MIGRATIONS)
1313 def migrate_data_foreign_keys(db):
1314     """
1315     This will migrate the data from the old object and target attributes which
1316     use the old ActivityIntermediator to the new temparay fields which use the
1317     new GenericForeignKey.
1318     """
1319
1320     metadata = MetaData(bind=db.bind)
1321     activity_table = inspect_table(metadata, "core__activities")
1322     ai_table = inspect_table(metadata, "core__activity_intermediators")
1323     gmr_table = inspect_table(metadata, "core__generic_model_reference")
1324
1325     # Iterate through all activities doing the migration per activity.
1326     for activity in model_iteration_hack(db, activity_table.select()):
1327         # First do the "Activity.object" migration to "Activity.temp_object"
1328         # I need to get the object from the Activity, I can't use the old
1329         # Activity.get_object as we're in a migration.
1330         object_ai = db.execute(ai_table.select(
1331             ai_table.c.id==activity.object
1332         )).first()
1333
1334         object_ai_type = ActivityIntermediator_R0.TABLENAMES[object_ai.type]
1335         object_ai_table = inspect_table(metadata, object_ai_type)
1336
1337         activity_object = db.execute(object_ai_table.select(
1338             object_ai_table.c.activity==object_ai.id
1339         )).first()
1340
1341         # If the object the activity is referecing doesn't revolve, then we
1342         # should skip it, it should be deleted when the AI table is deleted.
1343         if activity_object is None:
1344             continue
1345
1346         # now we need to create the GenericModelReference
1347         object_gmr = db.execute(gmr_table.insert().values(
1348             obj_pk=activity_object.id,
1349             model_type=object_ai_type
1350         ))
1351
1352         # Now set the ID of the GenericModelReference in the GenericForignKey
1353         db.execute(activity_table.update().values(
1354             temp_object=object_gmr.inserted_primary_key[0]
1355         ))
1356
1357         # Now do same process for "Activity.target" to "Activity.temp_target"
1358         # not all Activities have a target so if it doesn't just skip the rest
1359         # of this.
1360         if activity.target is None:
1361             continue
1362
1363         # Now get the target for the activity.
1364         target_ai = db.execute(ai_table.select(
1365             ai_table.c.id==activity.target
1366         )).first()
1367
1368         target_ai_type = ActivityIntermediator_R0.TABLENAMES[target_ai.type]
1369         target_ai_table = inspect_table(metadata, target_ai_type)
1370
1371         activity_target = db.execute(target_ai_table.select(
1372             target_ai_table.c.activity==target_ai.id
1373         )).first()
1374
1375         # It's quite possible that the target, alike the object could also have
1376         # been deleted, if so we should just skip it
1377         if activity_target is None:
1378             continue
1379
1380         # We now want to create the new target GenericModelReference
1381         target_gmr = db.execute(gmr_table.insert().values(
1382             obj_pk=activity_target.id,
1383             model_type=target_ai_type
1384         ))
1385
1386         # Now set the ID of the GenericModelReference in the GenericForignKey
1387         db.execute(activity_table.update().values(
1388             temp_object=target_gmr.inserted_primary_key[0]
1389         ))
1390
1391         # Commit to the database. We're doing it here rather than outside the
1392         # loop because if the server has a lot of data this can cause problems
1393         db.commit()
1394
1395 @RegisterMigration(30, MIGRATIONS)
1396 def rename_and_remove_object_and_target(db):
1397     """
1398     Renames the new Activity.object and Activity.target fields and removes the
1399     old ones.
1400     """
1401     metadata = MetaData(bind=db.bind)
1402     activity_table = inspect_table(metadata, "core__activities")
1403
1404     # Firstly lets remove the old fields.
1405     old_object_column = activity_table.columns["object"]
1406     old_target_column = activity_table.columns["target"]
1407
1408     # Drop the tables.
1409     old_object_column.drop()
1410     old_target_column.drop()
1411
1412     # Now get the new columns.
1413     new_object_column = activity_table.columns["temp_object"]
1414     new_target_column = activity_table.columns["temp_target"]
1415
1416     # rename them to the old names.
1417     new_object_column.alter(name="object_id")
1418     new_target_column.alter(name="target_id")
1419
1420     # Commit the changes to the database.
1421     db.commit()
1422
1423 @RegisterMigration(31, MIGRATIONS)
1424 def remove_activityintermediator(db):
1425     """
1426     This removes the old specific ActivityIntermediator model which has been
1427     superseeded by the GenericForeignKey field.
1428     """
1429     metadata = MetaData(bind=db.bind)
1430
1431     # Remove the columns which reference the AI
1432     collection_table = inspect_table(metadata, "core__collections")
1433     collection_ai_column = collection_table.columns["activity"]
1434     collection_ai_column.drop()
1435
1436     media_entry_table = inspect_table(metadata, "core__media_entries")
1437     media_entry_ai_column = media_entry_table.columns["activity"]
1438     media_entry_ai_column.drop()
1439
1440     comments_table = inspect_table(metadata, "core__media_comments")
1441     comments_ai_column = comments_table.columns["activity"]
1442     comments_ai_column.drop()
1443
1444     user_table = inspect_table(metadata, "core__users")
1445     user_ai_column = user_table.columns["activity"]
1446     user_ai_column.drop()
1447
1448     # Drop the table
1449     ai_table = inspect_table(metadata, "core__activity_intermediators")
1450     ai_table.drop()
1451
1452     # Commit the changes
1453     db.commit()
1454
1455 ##
1456 # Migrations for converting the User model into a Local and Remote User
1457 # setup.
1458 ##
1459
1460 class LocalUser_V0(declarative_base()):
1461     __tablename__ = "core__local_users"
1462
1463     id = Column(Integer, ForeignKey(User.id), primary_key=True)
1464     username = Column(Unicode, nullable=False, unique=True)
1465     email = Column(Unicode, nullable=False)
1466     pw_hash = Column(Unicode)
1467
1468     wants_comment_notification = Column(Boolean, default=True)
1469     wants_notifications = Column(Boolean, default=True)
1470     license_preference = Column(Unicode)
1471     uploaded = Column(Integer, default=0)
1472     upload_limit = Column(Integer)
1473
1474 class RemoteUser_V0(declarative_base()):
1475     __tablename__ = "core__remote_users"
1476
1477     id = Column(Integer, ForeignKey(User.id), primary_key=True)
1478     webfinger = Column(Unicode, unique=True)
1479
1480 @RegisterMigration(32, MIGRATIONS)
1481 def federation_user_create_tables(db):
1482     """
1483     Create all the tables
1484     """
1485     # Create tables needed
1486     LocalUser_V0.__table__.create(db.bind)
1487     RemoteUser_V0.__table__.create(db.bind)
1488     db.commit()
1489
1490     metadata = MetaData(bind=db.bind)
1491     user_table = inspect_table(metadata, "core__users")
1492
1493     # Create the fields
1494     updated_column = Column(
1495         "updated",
1496         DateTime,
1497         default=datetime.datetime.utcnow
1498     )
1499     updated_column.create(user_table)
1500
1501     type_column = Column(
1502         "type",
1503         Unicode
1504     )
1505     type_column.create(user_table)
1506
1507     name_column = Column(
1508         "name",
1509         Unicode
1510     )
1511     name_column.create(user_table)
1512
1513     db.commit()
1514
1515 @RegisterMigration(33, MIGRATIONS)
1516 def federation_user_migrate_data(db):
1517     """
1518     Migrate the data over to the new user models
1519     """
1520     metadata = MetaData(bind=db.bind)
1521
1522     user_table = inspect_table(metadata, "core__users")
1523     local_user_table = inspect_table(metadata, "core__local_users")
1524
1525     for user in model_iteration_hack(db, user_table.select()):
1526         db.execute(local_user_table.insert().values(
1527             id=user.id,
1528             username=user.username,
1529             email=user.email,
1530             pw_hash=user.pw_hash,
1531             wants_comment_notification=user.wants_comment_notification,
1532             wants_notifications=user.wants_notifications,
1533             license_preference=user.license_preference,
1534             uploaded=user.uploaded,
1535             upload_limit=user.upload_limit
1536         ))
1537
1538         db.execute(user_table.update().where(user_table.c.id==user.id).values(
1539             updated=user.created,
1540             type=LocalUser.__mapper_args__["polymorphic_identity"]
1541         ))
1542
1543         db.commit()
1544
1545 class User_vR2(declarative_base()):
1546     __tablename__ = "rename__users"
1547
1548     id = Column(Integer, primary_key=True)
1549     url = Column(Unicode)
1550     bio = Column(UnicodeText)
1551     name = Column(Unicode)
1552     type = Column(Unicode)
1553     created = Column(DateTime, nullable=False, default=datetime.datetime.utcnow)
1554     updated = Column(DateTime, nullable=False, default=datetime.datetime.utcnow)
1555     location = Column(Integer, ForeignKey(Location.id))
1556
1557 @RegisterMigration(34, MIGRATIONS)
1558 def federation_remove_fields(db):
1559     """
1560     This removes the fields from User model which aren't shared
1561     """
1562     metadata = MetaData(bind=db.bind)
1563
1564     user_table = inspect_table(metadata, "core__users")
1565
1566     # Remove the columns moved to LocalUser from User
1567     username_column = user_table.columns["username"]
1568     username_column.drop()
1569
1570     email_column = user_table.columns["email"]
1571     email_column.drop()
1572
1573     pw_hash_column = user_table.columns["pw_hash"]
1574     pw_hash_column.drop()
1575
1576     license_preference_column = user_table.columns["license_preference"]
1577     license_preference_column.drop()
1578
1579     uploaded_column = user_table.columns["uploaded"]
1580     uploaded_column.drop()
1581
1582     upload_limit_column = user_table.columns["upload_limit"]
1583     upload_limit_column.drop()
1584
1585     # SQLLite can't drop booleans -.-
1586     if db.bind.url.drivername == 'sqlite':
1587         # Create the new hacky table
1588         User_vR2.__table__.create(db.bind)
1589         db.commit()
1590         new_user_table = inspect_table(metadata, "rename__users")
1591         replace_table_hack(db, user_table, new_user_table)
1592     else:
1593         wcn_column = user_table.columns["wants_comment_notification"]
1594         wcn_column.drop()
1595
1596         wants_notifications_column = user_table.columns["wants_notifications"]
1597         wants_notifications_column.drop()
1598
1599     db.commit()
1600
1601 @RegisterMigration(35, MIGRATIONS)
1602 def federation_media_entry(db):
1603     metadata = MetaData(bind=db.bind)
1604     media_entry_table = inspect_table(metadata, "core__media_entries")
1605
1606     # Add new fields
1607     public_id_column = Column(
1608         "public_id",
1609         Unicode,
1610         unique=True,
1611         nullable=True
1612     )
1613     public_id_column.create(
1614         media_entry_table,
1615         unique_name="media_public_id"
1616     )
1617
1618     remote_column = Column(
1619         "remote",
1620         Boolean,
1621         default=False
1622     )
1623     remote_column.create(media_entry_table)
1624
1625     updated_column = Column(
1626         "updated",
1627         DateTime,
1628         default=datetime.datetime.utcnow,
1629     )
1630     updated_column.create(media_entry_table)
1631     db.commit()
1632
1633     # Data migration
1634     for entry in model_iteration_hack(db, media_entry_table.select()):
1635         db.execute(media_entry_table.update().values(
1636             updated=entry.created,
1637             remote=False
1638         ))
1639
1640         db.commit()
1641
1642 @RegisterMigration(36, MIGRATIONS)
1643 def create_oauth1_dummies(db):
1644     """
1645     Creates a dummy client, request and access tokens.
1646
1647     This is used when invalid data is submitted but real clients and
1648     access tokens. The use of dummy objects prevents timing attacks.
1649     """
1650     metadata = MetaData(bind=db.bind)
1651     client_table = inspect_table(metadata, "core__clients")
1652     request_token_table = inspect_table(metadata, "core__request_tokens")
1653     access_token_table = inspect_table(metadata, "core__access_tokens")
1654
1655     # Whilst we don't rely on the secret key being unique or unknown to prevent
1656     # unauthorized clients from using it to authenticate, we still as an extra
1657     # layer of protection created a cryptographically secure key individual to
1658     # each instance that should never be able to be known.
1659     client_secret = crypto.random_string(50)
1660     request_token_secret = crypto.random_string(50)
1661     request_token_verifier = crypto.random_string(50)
1662     access_token_secret = crypto.random_string(50)
1663
1664     # Dummy created/updated datetime object
1665     epoc_datetime = datetime.datetime.fromtimestamp(0)
1666
1667     # Create the dummy Client
1668     db.execute(client_table.insert().values(
1669         id=oauth.DUMMY_CLIENT_ID,
1670         secret=client_secret,
1671         application_type="dummy",
1672         created=epoc_datetime,
1673         updated=epoc_datetime
1674     ))
1675
1676     # Create the dummy RequestToken
1677     db.execute(request_token_table.insert().values(
1678         token=oauth.DUMMY_REQUEST_TOKEN,
1679         secret=request_token_secret,
1680         client=oauth.DUMMY_CLIENT_ID,
1681         verifier=request_token_verifier,
1682         created=epoc_datetime,
1683         updated=epoc_datetime,
1684         callback="oob"
1685     ))
1686
1687     # Create the dummy AccessToken
1688     db.execute(access_token_table.insert().values(
1689         token=oauth.DUMMY_ACCESS_TOKEN,
1690         secret=access_token_secret,
1691         request_token=oauth.DUMMY_REQUEST_TOKEN,
1692         created=epoc_datetime,
1693         updated=epoc_datetime
1694     ))
1695
1696     # Commit the changes
1697     db.commit()
1698
1699 @RegisterMigration(37, MIGRATIONS)
1700 def federation_collection_schema(db):
1701     """ Converts the Collection and CollectionItem """
1702     metadata = MetaData(bind=db.bind)
1703     collection_table = inspect_table(metadata, "core__collections")
1704     collection_items_table = inspect_table(metadata, "core__collection_items")
1705     media_entry_table = inspect_table(metadata, "core__media_entries")
1706     gmr_table = inspect_table(metadata, "core__generic_model_reference")
1707
1708     ##
1709     # Collection Table
1710     ##
1711
1712     # Add the fields onto the Collection model, we need to set these as
1713     # not null to avoid DB integreity errors. We will add the not null
1714     # constraint later.
1715     public_id_column = Column(
1716         "public_id",
1717         Unicode,
1718         unique=True
1719     )
1720     public_id_column.create(
1721         collection_table,
1722         unique_name="collection_public_id")
1723
1724     updated_column = Column(
1725         "updated",
1726         DateTime,
1727         default=datetime.datetime.utcnow
1728     )
1729     updated_column.create(collection_table)
1730
1731     type_column = Column(
1732         "type",
1733         Unicode,
1734     )
1735     type_column.create(collection_table)
1736
1737     db.commit()
1738
1739     # Iterate over the items and set the updated and type fields
1740     for collection in db.execute(collection_table.select()):
1741         db.execute(collection_table.update().where(
1742             collection_table.c.id==collection.id
1743         ).values(
1744             updated=collection.created,
1745             type="core-user-defined"
1746         ))
1747
1748     db.commit()
1749
1750     # Add the not null constraint onto the fields
1751     updated_column = collection_table.columns["updated"]
1752     updated_column.alter(nullable=False)
1753
1754     type_column = collection_table.columns["type"]
1755     type_column.alter(nullable=False)
1756
1757     db.commit()
1758
1759     # Rename the "items" to "num_items" as per the TODO
1760     num_items_field = collection_table.columns["items"]
1761     num_items_field.alter(name="num_items")
1762     db.commit()
1763
1764     ##
1765     # CollectionItem
1766     ##
1767     # Adding the object ID column, this again will have not null added later.
1768     object_id = Column(
1769         "object_id",
1770         Integer,
1771         ForeignKey(GenericModelReference_V0.id),
1772     )
1773     object_id.create(
1774         collection_items_table,
1775     )
1776
1777     db.commit()
1778
1779     # Iterate through and convert the Media reference to object_id
1780     for item in db.execute(collection_items_table.select()):
1781         # Check if there is a GMR for the MediaEntry
1782         object_gmr = db.execute(gmr_table.select(
1783             and_(
1784                 gmr_table.c.obj_pk == item.media_entry,
1785                 gmr_table.c.model_type == "core__media_entries"
1786             )
1787         )).first()
1788
1789         if object_gmr:
1790             object_gmr = object_gmr[0]
1791         else:
1792             # Create a GenericModelReference
1793             object_gmr = db.execute(gmr_table.insert().values(
1794                 obj_pk=item.media_entry,
1795                 model_type="core__media_entries"
1796             )).inserted_primary_key[0]
1797
1798         # Now set the object_id column to the ID of the GMR
1799         db.execute(collection_items_table.update().where(
1800             collection_items_table.c.id==item.id
1801         ).values(
1802             object_id=object_gmr
1803         ))
1804
1805     db.commit()
1806
1807     # Add not null constraint
1808     object_id = collection_items_table.columns["object_id"]
1809     object_id.alter(nullable=False)
1810
1811     db.commit()
1812
1813     # Now remove the old media_entry column
1814     media_entry_column = collection_items_table.columns["media_entry"]
1815     media_entry_column.drop()
1816
1817     db.commit()
1818
1819 @RegisterMigration(38, MIGRATIONS)
1820 def federation_actor(db):
1821     """ Renames refereces to the user to actor """
1822     metadata = MetaData(bind=db.bind)
1823
1824     # RequestToken: user -> actor
1825     request_token_table = inspect_table(metadata, "core__request_tokens")
1826     rt_user_column = request_token_table.columns["user"]
1827     rt_user_column.alter(name="actor")
1828
1829     # AccessToken: user -> actor
1830     access_token_table = inspect_table(metadata, "core__access_tokens")
1831     at_user_column = access_token_table.columns["user"]
1832     at_user_column.alter(name="actor")
1833
1834     # MediaEntry: uploader -> actor
1835     media_entry_table = inspect_table(metadata, "core__media_entries")
1836     me_user_column = media_entry_table.columns["uploader"]
1837     me_user_column.alter(name="actor")
1838
1839     # MediaComment: author -> actor
1840     media_comment_table = inspect_table(metadata, "core__media_comments")
1841     mc_user_column = media_comment_table.columns["author"]
1842     mc_user_column.alter(name="actor")
1843
1844     # Collection: creator -> actor
1845     collection_table = inspect_table(metadata, "core__collections")
1846     mc_user_column = collection_table.columns["creator"]
1847     mc_user_column.alter(name="actor")
1848
1849     # commit changes to db.
1850     db.commit()
1851
1852 class Graveyard_V0(declarative_base()):
1853     """ Where models come to die """
1854     __tablename__ = "core__graveyard"
1855
1856     id = Column(Integer, primary_key=True)
1857     public_id = Column(Unicode, nullable=True, unique=True)
1858
1859     deleted = Column(DateTime, nullable=False)
1860     object_type = Column(Unicode, nullable=False)
1861
1862     actor_id = Column(Integer, ForeignKey(GenericModelReference_V0.id))
1863
1864 @RegisterMigration(39, MIGRATIONS)
1865 def federation_graveyard(db):
1866     """ Introduces soft deletion to models
1867
1868     This adds a Graveyard model which is used to copy (soft-)deleted models to.
1869     """
1870     metadata = MetaData(bind=db.bind)
1871
1872     # Create the graveyard table
1873     Graveyard_V0.__table__.create(db.bind)
1874
1875     # Commit changes to the db
1876     db.commit()
1877
1878 @RegisterMigration(40, MIGRATIONS)
1879 def add_public_id(db):
1880     metadata = MetaData(bind=db.bind)
1881
1882     # Get the table
1883     activity_table = inspect_table(metadata, "core__activities")
1884     activity_public_id = Column(
1885         "public_id",
1886         Unicode,
1887         unique=True,
1888         nullable=True
1889     )
1890     activity_public_id.create(
1891         activity_table,
1892         unique_name="activity_public_id"
1893     )
1894
1895     # Commit this.
1896     db.commit()
1897
1898 class Comment_V0(declarative_base()):
1899     __tablename__ = "core__comment_links"
1900
1901     id = Column(Integer, primary_key=True)
1902     target_id = Column(
1903         Integer,
1904         ForeignKey(GenericModelReference_V0.id),
1905         nullable=False
1906     )
1907     comment_id = Column(
1908         Integer,
1909         ForeignKey(GenericModelReference_V0.id),
1910         nullable=False
1911     )
1912     added = Column(DateTime, nullable=False, default=datetime.datetime.utcnow)
1913
1914
1915 @RegisterMigration(41, MIGRATIONS)
1916 def federation_comments(db):
1917     """
1918     This reworks the MediaComent to be a more generic Comment model.
1919     """
1920     metadata = MetaData(bind=db.bind)
1921     textcomment_table = inspect_table(metadata, "core__media_comments")
1922     gmr_table = inspect_table(metadata, "core__generic_model_reference")
1923
1924     # First of all add the public_id field to the TextComment table
1925     comment_public_id_column = Column(
1926         "public_id",
1927         Unicode,
1928         unique=True
1929     )
1930     comment_public_id_column.create(
1931         textcomment_table,
1932         unique_name="public_id_unique"
1933     )
1934
1935     comment_updated_column = Column(
1936         "updated",
1937         DateTime,
1938     )
1939     comment_updated_column.create(textcomment_table)
1940
1941
1942     # First create the Comment link table.
1943     Comment_V0.__table__.create(db.bind)
1944     db.commit()
1945
1946     # now look up the comment table
1947     comment_table = inspect_table(metadata, "core__comment_links")
1948
1949     # Itierate over all the comments and add them to the link table.
1950     for comment in db.execute(textcomment_table.select()):
1951         # Check if there is a GMR to the comment.
1952         comment_gmr = db.execute(gmr_table.select().where(and_(
1953             gmr_table.c.obj_pk == comment.id,
1954             gmr_table.c.model_type == "core__media_comments"
1955         ))).first()
1956
1957         if comment_gmr:
1958             comment_gmr = comment_gmr[0]
1959         else:
1960             comment_gmr = db.execute(gmr_table.insert().values(
1961                 obj_pk=comment.id,
1962                 model_type="core__media_comments"
1963             )).inserted_primary_key[0]
1964
1965         # Get or create the GMR for the media entry
1966         entry_gmr = db.execute(gmr_table.select().where(and_(
1967             gmr_table.c.obj_pk == comment.media_entry,
1968             gmr_table.c.model_type == "core__media_entries"
1969         ))).first()
1970
1971         if entry_gmr:
1972             entry_gmr = entry_gmr[0]
1973         else:
1974             entry_gmr = db.execute(gmr_table.insert().values(
1975                 obj_pk=comment.media_entry,
1976                 model_type="core__media_entries"
1977             )).inserted_primary_key[0]
1978
1979         # Add the comment link.
1980         db.execute(comment_table.insert().values(
1981             target_id=entry_gmr,
1982             comment_id=comment_gmr,
1983             added=datetime.datetime.utcnow()
1984         ))
1985
1986         # Add the data to the updated field
1987         db.execute(textcomment_table.update().where(
1988             textcomment_table.c.id == comment.id
1989         ).values(
1990             updated=comment.created
1991         ))
1992     db.commit()
1993
1994     # Add not null constraint
1995     textcomment_update_column = textcomment_table.columns["updated"]
1996     textcomment_update_column.alter(nullable=False)
1997
1998     # Remove the unused fields on the TextComment model
1999     comment_media_entry_column = textcomment_table.columns["media_entry"]
2000     comment_media_entry_column.drop()
2001     db.commit()
2002
2003 @RegisterMigration(42, MIGRATIONS)
2004 def consolidate_reports(db):
2005     """ Consolidates the report tables into just one """
2006     metadata = MetaData(bind=db.bind)
2007
2008     report_table = inspect_table(metadata, "core__reports")
2009     comment_report_table = inspect_table(metadata, "core__reports_on_comments")
2010     media_report_table = inspect_table(metadata, "core__reports_on_media")
2011     gmr_table = inspect_table(metadata, "core__generic_model_reference")
2012
2013     # Add the GMR object field onto the base report table
2014     report_object_id_column = Column(
2015         "object_id",
2016         Integer,
2017         ForeignKey(GenericModelReference_V0.id),
2018         nullable=True,
2019     )
2020     report_object_id_column.create(report_table)
2021     db.commit()
2022
2023     # Iterate through the reports in the comment table and merge them in.
2024     for comment_report in db.execute(comment_report_table.select()):
2025         # If the comment is None it's been deleted, we should skip
2026         if comment_report.comment_id is None:
2027             continue
2028
2029         # Find a GMR for this if one exists.
2030         crgmr = db.execute(gmr_table.select().where(and_(
2031             gmr_table.c.obj_pk == comment_report.comment_id,
2032             gmr_table.c.model_type == "core__media_comments"
2033         ))).first()
2034
2035         if crgmr:
2036             crgmr = crgmr[0]
2037         else:
2038             crgmr = db.execute(gmr_table.insert().values(
2039                 obj_pk=comment_report.comment_id,
2040                 model_type="core__media_comments"
2041             )).inserted_primary_key[0]
2042
2043         # Great now we can save this back onto the (base) report.
2044         db.execute(report_table.update().where(
2045             report_table.c.id == comment_report.id
2046         ).values(
2047             object_id=crgmr
2048         ))
2049
2050     # Iterate through the Media Reports and do the save as above.
2051     for media_report in db.execute(media_report_table.select()):
2052         # If the media report is None then it's been deleted nd we should skip
2053         if media_report.media_entry_id is None:
2054             continue
2055
2056         # Find Mr. GMR :)
2057         mrgmr = db.execute(gmr_table.select().where(and_(
2058             gmr_table.c.obj_pk == media_report.media_entry_id,
2059             gmr_table.c.model_type == "core__media_entries"
2060         ))).first()
2061
2062         if mrgmr:
2063             mrgmr = mrgmr[0]
2064         else:
2065             mrgmr = db.execute(gmr_table.insert().values(
2066                 obj_pk=media_report.media_entry_id,
2067                 model_type="core__media_entries"
2068             )).inserted_primary_key[0]
2069
2070         # Save back on to the base.
2071         db.execute(report_table.update().where(
2072             report_table.c.id == media_report.id
2073         ).values(
2074             object_id=mrgmr
2075         ))
2076
2077     db.commit()
2078
2079     # Now we can remove the fields we don't need anymore
2080     report_type = report_table.columns["type"]
2081     report_type.drop()
2082
2083     # Drop both MediaReports and CommentTable.
2084     comment_report_table.drop()
2085     media_report_table.drop()
2086
2087     # Commit we're done.
2088     db.commit()
2089
2090 @RegisterMigration(43, MIGRATIONS)
2091 def consolidate_notification(db):
2092     """ Consolidates the notification models into one """
2093     metadata = MetaData(bind=db.bind)
2094     notification_table = inspect_table(metadata, "core__notifications")
2095     cn_table = inspect_table(metadata, "core__comment_notifications")
2096     cp_table = inspect_table(metadata, "core__processing_notifications")
2097     gmr_table = inspect_table(metadata, "core__generic_model_reference")
2098
2099     # Add fields needed
2100     notification_object_id_column = Column(
2101         "object_id",
2102         Integer,
2103         ForeignKey(GenericModelReference_V0.id)
2104     )
2105     notification_object_id_column.create(notification_table)
2106     db.commit()
2107
2108     # Iterate over comments and move to notification base table.
2109     for comment_notification in db.execute(cn_table.select()):
2110         # Find the GMR.
2111         cngmr = db.execute(gmr_table.select().where(and_(
2112             gmr_table.c.obj_pk == comment_notification.subject_id,
2113             gmr_table.c.model_type == "core__media_comments"
2114         ))).first()
2115
2116         if cngmr:
2117             cngmr = cngmr[0]
2118         else:
2119             cngmr = db.execute(gmr_table.insert().values(
2120                 obj_pk=comment_notification.subject_id,
2121                 model_type="core__media_comments"
2122             )).inserted_primary_key[0]
2123
2124         # Save back on notification
2125         db.execute(notification_table.update().where(
2126             notification_table.c.id == comment_notification.id
2127         ).values(
2128             object_id=cngmr
2129         ))
2130     db.commit()
2131
2132     # Do the same for processing notifications
2133     for processing_notification in db.execute(cp_table.select()):
2134         cpgmr = db.execute(gmr_table.select().where(and_(
2135             gmr_table.c.obj_pk == processing_notification.subject_id,
2136             gmr_table.c.model_type == "core__processing_notifications"
2137         ))).first()
2138
2139         if cpgmr:
2140             cpgmr = cpgmr[0]
2141         else:
2142             cpgmr = db.execute(gmr_table.insert().values(
2143                 obj_pk=processing_notification.subject_id,
2144                 model_type="core__processing_notifications"
2145             )).inserted_primary_key[0]
2146
2147         db.execute(notification_table.update().where(
2148             notification_table.c.id == processing_notification.id
2149         ).values(
2150             object_id=cpgmr
2151         ))
2152     db.commit()
2153
2154     # Add the not null constraint
2155     notification_object_id = notification_table.columns["object_id"]
2156     notification_object_id.alter(nullable=False)
2157
2158     # Now drop the fields we don't need
2159     notification_type_column = notification_table.columns["type"]
2160     notification_type_column.drop()
2161
2162     # Drop the tables we no longer need
2163     cp_table.drop()
2164     cn_table.drop()
2165
2166     db.commit()
2167
2168 @RegisterMigration(44, MIGRATIONS)
2169 def activity_cleanup(db):
2170     """
2171     This cleans up activities which are broken and have no graveyard object as
2172     well as removing the not null constraint on Report.object_id as that can
2173     be null when action has been taken to delete the reported content.
2174
2175     Some of this has been changed in previous migrations so we need to check
2176     if there is anything to be done, there might not be. It was fixed as part
2177     of the #5369 fix. Some past migrations could have broken on some people so
2178     needed to be fixed then however for some they would have run fine.
2179     """
2180     metadata = MetaData(bind=db.bind)
2181     report_table = inspect_table(metadata, "core__reports")
2182     activity_table = inspect_table(metadata, "core__activities")
2183     gmr_table = inspect_table(metadata, "core__generic_model_reference")
2184
2185     # Remove not null on Report.object_id
2186     object_id_column = report_table.columns["object_id"]
2187     if not object_id_column.nullable:
2188         object_id_column.alter(nullable=False)
2189     db.commit()
2190
2191     # Go through each activity and verify the object and if a target is
2192     # specified both exist.
2193     for activity in db.execute(activity_table.select()):
2194         # Get the GMR
2195         obj_gmr = db.execute(gmr_table.select().where(
2196             gmr_table.c.id == activity.object_id,
2197         )).first()
2198
2199         # Get the object the GMR points to, might be null.
2200         obj_table = inspect_table(metadata, obj_gmr.model_type)
2201         obj = db.execute(obj_table.select().where(
2202             obj_table.c.id == obj_gmr.obj_pk
2203         )).first()
2204
2205         if obj is None:
2206             # Okay we need to delete the activity and move to the next
2207             db.execute(activity_table.delete().where(
2208                 activity_table.c.id == activity.id
2209             ))
2210             continue
2211
2212         # If there is a target then check that too, if not that's fine
2213         if activity.target_id == None:
2214             continue
2215
2216         # Okay check the target is valid
2217         target_gmr = db.execute(gmr_table.select().where(
2218             gmr_table.c.id == activity.target_id
2219         )).first()
2220
2221         target_table = inspect_table(metadata, target_gmr.model_type)
2222         target = db.execute(target_table.select().where(
2223             target_table.c.id == target_gmr.obj_pk
2224         )).first()
2225
2226         # If it doesn't exist, delete the activity.
2227         if target is None:
2228             db.execute(activity_table.delete().where(
2229                 activity_table.c.id == activity.id
2230             ))