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