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)
  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     TYPES = {
 918         "user": User,
 919         "media": MediaEntry,
 920         "comment": Comment,
 921         "collection": Collection,
 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
1318     # Iterate through all activities doing the migration per activity.
1319     for activity in db.execute(activity_table.select()):
1320         # First do the "Activity.object" migration to "Activity.temp_object"
1321         # I need to get the object from the Activity, I can't use the old
1322         # Activity.get_object as we're in a migration.
1323         object_ai = db.execute(ai_table.select(
1324             ai_table.c.id==activity.object
1325         )).first()
1326
1327         object_ai_type = ActivityIntermediator_R0.TYPES[object_ai.type]
1328         object_ai_table = inspect_table(metadata, object_ai_type.__tablename__)
1329
1330         activity_object = db.execute(object_ai_table.select(
1331             object_ai_table.c.activity==object_ai.id
1332         )).first()
1333
1334         # now we need to create the GenericModelReference
1335         object_gmr = db.execute(gmr_table.insert().values(
1336             obj_pk=activity_object.id,
1337             model_type=object_ai_type.__tablename__
1338         ))
1339
1340         # Now set the ID of the GenericModelReference in the GenericForignKey
1341         db.execute(activity_table.update().values(
1342             temp_object=object_gmr.inserted_primary_key[0]
1343         ))
1344
1345         # Now do same process for "Activity.target" to "Activity.temp_target"
1346         # not all Activities have a target so if it doesn't just skip the rest
1347         # of this.
1348         if activity.target is None:
1349             continue
1350
1351         # Now get the target for the activity.
1352         target_ai = db.execute(ai_table.select(
1353             ai_table.c.id==activity.target
1354         )).first()
1355
1356         target_ai_type = ActivityIntermediator_R0.TYPES[target_ai.type]
1357         target_ai_table = inspect_table(metadata, target_ai_type.__tablename__)
1358
1359         activity_target = db.execute(target_ai_table.select(
1360             target_ai_table.c.activity==target_ai.id
1361         )).first()
1362
1363         # We now want to create the new target GenericModelReference
1364         target_gmr = db.execute(gmr_table.insert().values(
1365             obj_pk=activity_target.id,
1366             model_type=target_ai_type.__tablename__
1367         ))
1368
1369         # Now set the ID of the GenericModelReference in the GenericForignKey
1370         db.execute(activity_table.update().values(
1371             temp_object=target_gmr.inserted_primary_key[0]
1372         ))
1373
1374     # Commit to the database.
1375     db.commit()
1376
1377 @RegisterMigration(30, MIGRATIONS)
1378 def rename_and_remove_object_and_target(db):
1379     """
1380     Renames the new Activity.object and Activity.target fields and removes the
1381     old ones.
1382     """
1383     metadata = MetaData(bind=db.bind)
1384     activity_table = inspect_table(metadata, "core__activities")
1385
1386     # Firstly lets remove the old fields.
1387     old_object_column = activity_table.columns["object"]
1388     old_target_column = activity_table.columns["target"]
1389
1390     # Drop the tables.
1391     old_object_column.drop()
1392     old_target_column.drop()
1393
1394     # Now get the new columns.
1395     new_object_column = activity_table.columns["temp_object"]
1396     new_target_column = activity_table.columns["temp_target"]
1397
1398     # rename them to the old names.
1399     new_object_column.alter(name="object_id")
1400     new_target_column.alter(name="target_id")
1401
1402     # Commit the changes to the database.
1403     db.commit()
1404
1405 @RegisterMigration(31, MIGRATIONS)
1406 def remove_activityintermediator(db):
1407     """
1408     This removes the old specific ActivityIntermediator model which has been
1409     superseeded by the GenericForeignKey field.
1410     """
1411     metadata = MetaData(bind=db.bind)
1412
1413     # Remove the columns which reference the AI
1414     collection_table = inspect_table(metadata, "core__collections")
1415     collection_ai_column = collection_table.columns["activity"]
1416     collection_ai_column.drop()
1417
1418     media_entry_table = inspect_table(metadata, "core__media_entries")
1419     media_entry_ai_column = media_entry_table.columns["activity"]
1420     media_entry_ai_column.drop()
1421
1422     comments_table = inspect_table(metadata, "core__media_comments")
1423     comments_ai_column = comments_table.columns["activity"]
1424     comments_ai_column.drop()
1425
1426     user_table = inspect_table(metadata, "core__users")
1427     user_ai_column = user_table.columns["activity"]
1428     user_ai_column.drop()
1429
1430     # Drop the table
1431     ai_table = inspect_table(metadata, "core__activity_intermediators")
1432     ai_table.drop()
1433
1434     # Commit the changes
1435     db.commit()
1436
1437 ##
1438 # Migrations for converting the User model into a Local and Remote User
1439 # setup.
1440 ##
1441
1442 class LocalUser_V0(declarative_base()):
1443     __tablename__ = "core__local_users"
1444
1445     id = Column(Integer, ForeignKey(User.id), primary_key=True)
1446     username = Column(Unicode, nullable=False, unique=True)
1447     email = Column(Unicode, nullable=False)
1448     pw_hash = Column(Unicode)
1449
1450     wants_comment_notification = Column(Boolean, default=True)
1451     wants_notifications = Column(Boolean, default=True)
1452     license_preference = Column(Unicode)
1453     uploaded = Column(Integer, default=0)
1454     upload_limit = Column(Integer)
1455
1456 class RemoteUser_V0(declarative_base()):
1457     __tablename__ = "core__remote_users"
1458
1459     id = Column(Integer, ForeignKey(User.id), primary_key=True)
1460     webfinger = Column(Unicode, unique=True)
1461
1462 @RegisterMigration(32, MIGRATIONS)
1463 def federation_user_create_tables(db):
1464     """
1465     Create all the tables
1466     """
1467     # Create tables needed
1468     LocalUser_V0.__table__.create(db.bind)
1469     RemoteUser_V0.__table__.create(db.bind)
1470     db.commit()
1471
1472     metadata = MetaData(bind=db.bind)
1473     user_table = inspect_table(metadata, "core__users")
1474
1475     # Create the fields
1476     updated_column = Column(
1477         "updated",
1478         DateTime,
1479         default=datetime.datetime.utcnow
1480     )
1481     updated_column.create(user_table)
1482
1483     type_column = Column(
1484         "type",
1485         Unicode
1486     )
1487     type_column.create(user_table)
1488
1489     name_column = Column(
1490         "name",
1491         Unicode
1492     )
1493     name_column.create(user_table)
1494
1495     db.commit()
1496
1497 @RegisterMigration(33, MIGRATIONS)
1498 def federation_user_migrate_data(db):
1499     """
1500     Migrate the data over to the new user models
1501     """
1502     metadata = MetaData(bind=db.bind)
1503
1504     user_table = inspect_table(metadata, "core__users")
1505     local_user_table = inspect_table(metadata, "core__local_users")
1506
1507     for user in db.execute(user_table.select()):
1508         db.execute(local_user_table.insert().values(
1509             id=user.id,
1510             username=user.username,
1511             email=user.email,
1512             pw_hash=user.pw_hash,
1513             wants_comment_notification=user.wants_comment_notification,
1514             wants_notifications=user.wants_notifications,
1515             license_preference=user.license_preference,
1516             uploaded=user.uploaded,
1517             upload_limit=user.upload_limit
1518         ))
1519
1520         db.execute(user_table.update().where(user_table.c.id==user.id).values(
1521             updated=user.created,
1522             type=LocalUser.__mapper_args__["polymorphic_identity"]
1523         ))
1524
1525     db.commit()
1526
1527 class User_vR2(declarative_base()):
1528     __tablename__ = "rename__users"
1529
1530     id = Column(Integer, primary_key=True)
1531     url = Column(Unicode)
1532     bio = Column(UnicodeText)
1533     name = Column(Unicode)
1534     type = Column(Unicode)
1535     created = Column(DateTime, nullable=False, default=datetime.datetime.utcnow)
1536     updated = Column(DateTime, nullable=False, default=datetime.datetime.utcnow)
1537     location = Column(Integer, ForeignKey(Location.id))
1538
1539 @RegisterMigration(34, MIGRATIONS)
1540 def federation_remove_fields(db):
1541     """
1542     This removes the fields from User model which aren't shared
1543     """
1544     metadata = MetaData(bind=db.bind)
1545
1546     user_table = inspect_table(metadata, "core__users")
1547
1548     # Remove the columns moved to LocalUser from User
1549     username_column = user_table.columns["username"]
1550     username_column.drop()
1551
1552     email_column = user_table.columns["email"]
1553     email_column.drop()
1554
1555     pw_hash_column = user_table.columns["pw_hash"]
1556     pw_hash_column.drop()
1557
1558     license_preference_column = user_table.columns["license_preference"]
1559     license_preference_column.drop()
1560
1561     uploaded_column = user_table.columns["uploaded"]
1562     uploaded_column.drop()
1563
1564     upload_limit_column = user_table.columns["upload_limit"]
1565     upload_limit_column.drop()
1566
1567     # SQLLite can't drop booleans -.-
1568     if db.bind.url.drivername == 'sqlite':
1569         # Create the new hacky table
1570         User_vR2.__table__.create(db.bind)
1571         db.commit()
1572         new_user_table = inspect_table(metadata, "rename__users")
1573         replace_table_hack(db, user_table, new_user_table)
1574     else:
1575         wcn_column = user_table.columns["wants_comment_notification"]
1576         wcn_column.drop()
1577
1578         wants_notifications_column = user_table.columns["wants_notifications"]
1579         wants_notifications_column.drop()
1580
1581     db.commit()
1582
1583 @RegisterMigration(35, MIGRATIONS)
1584 def federation_media_entry(db):
1585     metadata = MetaData(bind=db.bind)
1586     media_entry_table = inspect_table(metadata, "core__media_entries")
1587
1588     # Add new fields
1589     public_id_column = Column(
1590         "public_id",
1591         Unicode,
1592         unique=True,
1593         nullable=True
1594     )
1595     public_id_column.create(
1596         media_entry_table,
1597         unique_name="media_public_id"
1598     )
1599
1600     remote_column = Column(
1601         "remote",
1602         Boolean,
1603         default=False
1604     )
1605     remote_column.create(media_entry_table)
1606
1607     updated_column = Column(
1608         "updated",
1609         DateTime,
1610         default=datetime.datetime.utcnow,
1611     )
1612     updated_column.create(media_entry_table)
1613
1614     # Data migration
1615     for entry in db.execute(media_entry_table.select()):
1616         db.execute(media_entry_table.update().values(
1617             updated=entry.created,
1618             remote=False
1619         ))
1620
1621     db.commit()
1622
1623 @RegisterMigration(36, MIGRATIONS)
1624 def create_oauth1_dummies(db):
1625     """
1626     Creates a dummy client, request and access tokens.
1627
1628     This is used when invalid data is submitted but real clients and
1629     access tokens. The use of dummy objects prevents timing attacks.
1630     """
1631     metadata = MetaData(bind=db.bind)
1632     client_table = inspect_table(metadata, "core__clients")
1633     request_token_table = inspect_table(metadata, "core__request_tokens")
1634     access_token_table = inspect_table(metadata, "core__access_tokens")
1635
1636     # Whilst we don't rely on the secret key being unique or unknown to prevent
1637     # unauthorized clients from using it to authenticate, we still as an extra
1638     # layer of protection created a cryptographically secure key individual to
1639     # each instance that should never be able to be known.
1640     client_secret = crypto.random_string(50)
1641     request_token_secret = crypto.random_string(50)
1642     request_token_verifier = crypto.random_string(50)
1643     access_token_secret = crypto.random_string(50)
1644
1645     # Dummy created/updated datetime object
1646     epoc_datetime = datetime.datetime.fromtimestamp(0)
1647
1648     # Create the dummy Client
1649     db.execute(client_table.insert().values(
1650         id=oauth.DUMMY_CLIENT_ID,
1651         secret=client_secret,
1652         application_type="dummy",
1653         created=epoc_datetime,
1654         updated=epoc_datetime
1655     ))
1656
1657     # Create the dummy RequestToken
1658     db.execute(request_token_table.insert().values(
1659         token=oauth.DUMMY_REQUEST_TOKEN,
1660         secret=request_token_secret,
1661         client=oauth.DUMMY_CLIENT_ID,
1662         verifier=request_token_verifier,
1663         created=epoc_datetime,
1664         updated=epoc_datetime,
1665         callback="oob"
1666     ))
1667
1668     # Create the dummy AccessToken
1669     db.execute(access_token_table.insert().values(
1670         token=oauth.DUMMY_ACCESS_TOKEN,
1671         secret=access_token_secret,
1672         request_token=oauth.DUMMY_REQUEST_TOKEN,
1673         created=epoc_datetime,
1674         updated=epoc_datetime
1675     ))
1676
1677     # Commit the changes
1678     db.commit()
1679
1680 @RegisterMigration(37, MIGRATIONS)
1681 def federation_collection_schema(db):
1682     """ Converts the Collection and CollectionItem """
1683     metadata = MetaData(bind=db.bind)
1684     collection_table = inspect_table(metadata, "core__collections")
1685     collection_items_table = inspect_table(metadata, "core__collection_items")
1686     media_entry_table = inspect_table(metadata, "core__media_entries")
1687     gmr_table = inspect_table(metadata, "core__generic_model_reference")
1688
1689     ##
1690     # Collection Table
1691     ##
1692
1693     # Add the fields onto the Collection model, we need to set these as
1694     # not null to avoid DB integreity errors. We will add the not null
1695     # constraint later.
1696     public_id_column = Column(
1697         "public_id",
1698         Unicode,
1699         unique=True
1700     )
1701     public_id_column.create(
1702         collection_table,
1703         unique_name="collection_public_id")
1704
1705     updated_column = Column(
1706         "updated",
1707         DateTime,
1708         default=datetime.datetime.utcnow
1709     )
1710     updated_column.create(collection_table)
1711
1712     type_column = Column(
1713         "type",
1714         Unicode,
1715     )
1716     type_column.create(collection_table)
1717
1718     db.commit()
1719
1720     # Iterate over the items and set the updated and type fields
1721     for collection in db.execute(collection_table.select()):
1722         db.execute(collection_table.update().where(
1723             collection_table.c.id==collection.id
1724         ).values(
1725             updated=collection.created,
1726             type="core-user-defined"
1727         ))
1728
1729     db.commit()
1730
1731     # Add the not null constraint onto the fields
1732     updated_column = collection_table.columns["updated"]
1733     updated_column.alter(nullable=False)
1734
1735     type_column = collection_table.columns["type"]
1736     type_column.alter(nullable=False)
1737
1738     db.commit()
1739
1740     # Rename the "items" to "num_items" as per the TODO
1741     num_items_field = collection_table.columns["items"]
1742     num_items_field.alter(name="num_items")
1743     db.commit()
1744
1745     ##
1746     # CollectionItem
1747     ##
1748     # Adding the object ID column, this again will have not null added later.
1749     object_id = Column(
1750         "object_id",
1751         Integer,
1752         ForeignKey(GenericModelReference_V0.id),
1753     )
1754     object_id.create(
1755         collection_items_table,
1756     )
1757
1758     db.commit()
1759
1760     # Iterate through and convert the Media reference to object_id
1761     for item in db.execute(collection_items_table.select()):
1762         # Check if there is a GMR for the MediaEntry
1763         object_gmr = db.execute(gmr_table.select(
1764             and_(
1765                 gmr_table.c.obj_pk == item.media_entry,
1766                 gmr_table.c.model_type == "core__media_entries"
1767             )
1768         )).first()
1769
1770         if object_gmr:
1771             object_gmr = object_gmr[0]
1772         else:
1773             # Create a GenericModelReference
1774             object_gmr = db.execute(gmr_table.insert().values(
1775                 obj_pk=item.media_entry,
1776                 model_type="core__media_entries"
1777             )).inserted_primary_key[0]
1778
1779         # Now set the object_id column to the ID of the GMR
1780         db.execute(collection_items_table.update().where(
1781             collection_items_table.c.id==item.id
1782         ).values(
1783             object_id=object_gmr
1784         ))
1785
1786     db.commit()
1787
1788     # Add not null constraint
1789     object_id = collection_items_table.columns["object_id"]
1790     object_id.alter(nullable=False)
1791
1792     db.commit()
1793
1794     # Now remove the old media_entry column
1795     media_entry_column = collection_items_table.columns["media_entry"]
1796     media_entry_column.drop()
1797
1798     db.commit()
1799
1800 @RegisterMigration(38, MIGRATIONS)
1801 def federation_actor(db):
1802     """ Renames refereces to the user to actor """
1803     metadata = MetaData(bind=db.bind)
1804
1805     # RequestToken: user -> actor
1806     request_token_table = inspect_table(metadata, "core__request_tokens")
1807     rt_user_column = request_token_table.columns["user"]
1808     rt_user_column.alter(name="actor")
1809
1810     # AccessToken: user -> actor
1811     access_token_table = inspect_table(metadata, "core__access_tokens")
1812     at_user_column = access_token_table.columns["user"]
1813     at_user_column.alter(name="actor")
1814
1815     # MediaEntry: uploader -> actor
1816     media_entry_table = inspect_table(metadata, "core__media_entries")
1817     me_user_column = media_entry_table.columns["uploader"]
1818     me_user_column.alter(name="actor")
1819
1820     # MediaComment: author -> actor
1821     media_comment_table = inspect_table(metadata, "core__media_comments")
1822     mc_user_column = media_comment_table.columns["author"]
1823     mc_user_column.alter(name="actor")
1824
1825     # Collection: creator -> actor
1826     collection_table = inspect_table(metadata, "core__collections")
1827     mc_user_column = collection_table.columns["creator"]
1828     mc_user_column.alter(name="actor")
1829
1830     # commit changes to db.
1831     db.commit()
1832
1833 class Graveyard_V0(declarative_base()):
1834     """ Where models come to die """
1835     __tablename__ = "core__graveyard"
1836
1837     id = Column(Integer, primary_key=True)
1838     public_id = Column(Unicode, nullable=True, unique=True)
1839
1840     deleted = Column(DateTime, nullable=False)
1841     object_type = Column(Unicode, nullable=False)
1842
1843     actor_id = Column(Integer, ForeignKey(GenericModelReference_V0.id))
1844
1845 @RegisterMigration(39, MIGRATIONS)
1846 def federation_graveyard(db):
1847     """ Introduces soft deletion to models
1848
1849     This adds a Graveyard model which is used to copy (soft-)deleted models to.
1850     """
1851     metadata = MetaData(bind=db.bind)
1852
1853     # Create the graveyard table
1854     Graveyard_V0.__table__.create(db.bind)
1855
1856     # Commit changes to the db
1857     db.commit()
1858
1859 @RegisterMigration(40, MIGRATIONS)
1860 def add_public_id(db):
1861     metadata = MetaData(bind=db.bind)
1862
1863     # Get the table
1864     activity_table = inspect_table(metadata, "core__activities")
1865     activity_public_id = Column(
1866         "public_id",
1867         Unicode,
1868         unique=True,
1869         nullable=True
1870     )
1871     activity_public_id.create(
1872         activity_table,
1873         unique_name="activity_public_id"
1874     )
1875
1876     # Commit this.
1877     db.commit()
1878
1879 class Comment_V0(declarative_base()):
1880     __tablename__ = "core__comment_links"
1881
1882     id = Column(Integer, primary_key=True)
1883     target_id = Column(
1884         Integer,
1885         ForeignKey(GenericModelReference_V0.id),
1886         nullable=False
1887     )
1888     comment_id = Column(
1889         Integer,
1890         ForeignKey(GenericModelReference_V0.id),
1891         nullable=False
1892     )
1893     added = Column(DateTime, nullable=False, default=datetime.datetime.utcnow)
1894
1895
1896 @RegisterMigration(41, MIGRATIONS)
1897 def federation_comments(db):
1898     """
1899     This reworks the MediaComent to be a more generic Comment model.
1900     """
1901     metadata = MetaData(bind=db.bind)
1902     textcomment_table = inspect_table(metadata, "core__media_comments")
1903     gmr_table = inspect_table(metadata, "core__generic_model_reference")
1904
1905     # First of all add the public_id field to the TextComment table
1906     comment_public_id_column = Column(
1907         "public_id",
1908         Unicode,
1909         unique=True
1910     )
1911     comment_public_id_column.create(
1912         textcomment_table,
1913         unique_name="public_id_unique"
1914     )
1915
1916     comment_updated_column = Column(
1917         "updated",
1918         DateTime,
1919     )
1920     comment_updated_column.create(textcomment_table)
1921
1922
1923     # First create the Comment link table.
1924     Comment_V0.__table__.create(db.bind)
1925     db.commit()
1926
1927     # now look up the comment table
1928     comment_table = inspect_table(metadata, "core__comment_links")
1929
1930     # Itierate over all the comments and add them to the link table.
1931     for comment in db.execute(textcomment_table.select()):
1932         # Check if there is a GMR to the comment.
1933         comment_gmr = db.execute(gmr_table.select().where(and_(
1934             gmr_table.c.obj_pk == comment.id,
1935             gmr_table.c.model_type == "core__media_comments"
1936         ))).first()
1937
1938         if comment_gmr:
1939             comment_gmr = comment_gmr[0]
1940         else:
1941             comment_gmr = db.execute(gmr_table.insert().values(
1942                 obj_pk=comment.id,
1943                 model_type="core__media_comments"
1944             )).inserted_primary_key[0]
1945
1946         # Get or create the GMR for the media entry
1947         entry_gmr = db.execute(gmr_table.select().where(and_(
1948             gmr_table.c.obj_pk == comment.media_entry,
1949             gmr_table.c.model_type == "core__media_entries"
1950         ))).first()
1951
1952         if entry_gmr:
1953             entry_gmr = entry_gmr[0]
1954         else:
1955             entry_gmr = db.execute(gmr_table.insert().values(
1956                 obj_pk=comment.media_entry,
1957                 model_type="core__media_entries"
1958             )).inserted_primary_key[0]
1959
1960         # Add the comment link.
1961         db.execute(comment_table.insert().values(
1962             target_id=entry_gmr,
1963             comment_id=comment_gmr,
1964             added=datetime.datetime.utcnow()
1965         ))
1966
1967         # Add the data to the updated field
1968         db.execute(textcomment_table.update().where(
1969             textcomment_table.c.id == comment.id
1970         ).values(
1971             updated=comment.created
1972         ))
1973     db.commit()
1974
1975     # Add not null constraint
1976     textcomment_update_column = textcomment_table.columns["updated"]
1977     textcomment_update_column.alter(nullable=False)
1978
1979     # Remove the unused fields on the TextComment model
1980     comment_media_entry_column = textcomment_table.columns["media_entry"]
1981     comment_media_entry_column.drop()
1982     db.commit()
1983
1984 @RegisterMigration(42, MIGRATIONS)
1985 def consolidate_reports(db):
1986     """ Consolidates the report tables into just one """
1987     metadata = MetaData(bind=db.bind)
1988
1989     report_table = inspect_table(metadata, "core__reports")
1990     comment_report_table = inspect_table(metadata, "core__reports_on_comments")
1991     media_report_table = inspect_table(metadata, "core__reports_on_media")
1992     gmr_table = inspect_table(metadata, "core__generic_model_reference")
1993
1994     # Add the GMR object field onto the base report table
1995     report_object_id_column = Column(
1996         "object_id",
1997         Integer,
1998         ForeignKey(GenericModelReference_V0.id),
1999     )
2000     report_object_id_column.create(report_table)
2001     db.commit()
2002
2003     # Iterate through the reports in the comment table and merge them in.
2004     for comment_report in db.execute(comment_report_table.select()):
2005         # Find a GMR for this if one exists.
2006         crgmr = db.execute(gmr_table.select().where(and_(
2007             gmr_table.c.obj_pk == comment_report.comment_id,
2008             gmr_table.c.model_type == "core__media_comments"
2009         ))).first()
2010
2011         if crgmr:
2012             crgmr = crgmr[0]
2013         else:
2014             crgmr = db.execute(gmr_table.insert().values(
2015                 gmr_table.c.obj_pk == comment_report.comment_id,
2016                 gmr_table.c.model_type == "core__media_comments"
2017             )).inserted_primary_key[0]
2018
2019         # Great now we can save this back onto the (base) report.
2020         db.execute(report_table.update().where(
2021             report_table.c.id == comment_report.id
2022         ).values(
2023             object_id=crgmr
2024         ))
2025
2026     # Iterate through the Media Reports and do the save as above.
2027     for media_report in db.execute(media_report_table.select()):
2028         # Find Mr. GMR :)
2029         mrgmr = db.execute(gmr_table.select().where(and_(
2030             gmr_table.c.obj_pk == media_report.media_entry_id,
2031             gmr_table.c.model_type == "core__media_entries"
2032         ))).first()
2033
2034         if mrgmr:
2035             mrgmr = mrgmr[0]
2036         else:
2037             mrgmr = db.execute(gmr_table.insert().values(
2038                 obj_pk=media_report.media_entry_id,
2039                 model_type="core__media_entries"
2040             )).inserted_primary_key[0]
2041
2042         # Save back on to the base.
2043         db.execute(report_table.update().where(
2044             report_table.c.id == media_report.id
2045         ).values(
2046             object_id=mrgmr
2047         ))
2048
2049     db.commit()
2050
2051     # Add the not null constraint
2052     report_object_id = report_table.columns["object_id"]
2053     report_object_id.alter(nullable=False)
2054
2055     # Now we can remove the fields we don't need anymore
2056     report_type = report_table.columns["type"]
2057     report_type.drop()
2058
2059     # Drop both MediaReports and CommentTable.
2060     comment_report_table.drop()
2061     media_report_table.drop()
2062
2063     # Commit we're done.
2064     db.commit()
2065
2066 @RegisterMigration(43, MIGRATIONS)
2067 def consolidate_notification(db):
2068     """ Consolidates the notification models into one """
2069     metadata = MetaData(bind=db.bind)
2070     notification_table = inspect_table(metadata, "core__notifications")
2071     cn_table = inspect_table(metadata, "core__comment_notifications")
2072     cp_table = inspect_table(metadata, "core__processing_notifications")
2073     gmr_table = inspect_table(metadata, "core__generic_model_reference")
2074
2075     # Add fields needed
2076     notification_object_id_column = Column(
2077         "object_id",
2078         Integer,
2079         ForeignKey(GenericModelReference_V0.id)
2080     )
2081     notification_object_id_column.create(notification_table)
2082     db.commit()
2083
2084     # Iterate over comments and move to notification base table.
2085     for comment_notification in db.execute(cn_table.select()):
2086         # Find the GMR.
2087         cngmr = db.execute(gmr_table.select().where(and_(
2088             gmr_table.c.obj_pk == comment_notification.subject_id,
2089             gmr_table.c.model_type == "core__media_comments"
2090         ))).first()
2091
2092         if cngmr:
2093             cngmr = cngmr[0]
2094         else:
2095             cngmr = db.execute(gmr_table.insert().values(
2096                 obj_pk=comment_notification.subject_id,
2097                 model_type="core__media_comments"
2098             )).inserted_primary_key[0]
2099
2100         # Save back on notification
2101         db.execute(notification_table.update().where(
2102             notification_table.c.id == comment_notification.id
2103         ).values(
2104             object_id=cngmr
2105         ))
2106     db.commit()
2107
2108     # Do the same for processing notifications
2109     for processing_notification in db.execute(cp_table.select()):
2110         cpgmr = db.execute(gmr_table.select().where(and_(
2111             gmr_table.c.obj_pk == processing_notification.subject_id,
2112             gmr_table.c.model_type == "core__processing_notifications"
2113         ))).first()
2114
2115         if cpgmr:
2116             cpgmr = cpgmr[0]
2117         else:
2118             cpgmr = db.execute(gmr_table.insert().values(
2119                 obj_pk=processing_notification.subject_id,
2120                 model_type="core__processing_notifications"
2121             )).inserted_primary_key[0]
2122
2123         db.execute(notification_table.update().where(
2124             notification_table.c.id == processing_notification.id
2125         ).values(
2126             object_id=cpgmr
2127         ))
2128     db.commit()
2129
2130     # Add the not null constraint
2131     notification_object_id = notification_table.columns["object_id"]
2132     notification_object_id.alter(nullable=False)
2133
2134     # Now drop the fields we don't need
2135     notification_type_column = notification_table.columns["type"]
2136     notification_type_column.drop()
2137
2138     # Drop the tables we no longer need
2139     cp_table.drop()
2140     cn_table.drop()
2141
2142     db.commit()