mediagoblin/db/migrations.py

   1 # GNU MediaGoblin -- federated, autonomous media hosting
   2 # Copyright (C) 2011, 2012 MediaGoblin contributors.  See AUTHORS.
   3 #
   4 # This program is free software: you can redistribute it and/or modify
   5 # it under the terms of the GNU Affero General Public License as published by
   6 # the Free Software Foundation, either version 3 of the License, or
   7 # (at your option) any later version.
   8 #
   9 # This program is distributed in the hope that it will be useful,
  10 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  11 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12 # GNU Affero General Public License for more details.
  13 #
  14 # You should have received a copy of the GNU Affero General Public License
  15 # along with this program.  If not, see <http://www.gnu.org/licenses/>.
  16
  17 import datetime
  18 import uuid
  19
  20 from sqlalchemy import (MetaData, Table, Column, Boolean, SmallInteger,
  21                         Integer, Unicode, UnicodeText, DateTime,
  22                         ForeignKey, Date)
  23 from sqlalchemy.exc import ProgrammingError
  24 from sqlalchemy.ext.declarative import declarative_base
  25 from sqlalchemy.sql import and_
  26 from migrate.changeset.constraint import UniqueConstraint
  27
  28
  29 from mediagoblin.db.extratypes import JSONEncoded, MutationDict
  30 from mediagoblin.db.migration_tools import (
  31     RegisterMigration, inspect_table, replace_table_hack)
  32 from mediagoblin.db.models import (MediaEntry, Collection, MediaComment, User,
  33         Privilege)
  34 from mediagoblin.db.extratypes import JSONEncoded, MutationDict
  35
  36 MIGRATIONS = {}
  37
  38
  39 @RegisterMigration(1, MIGRATIONS)
  40 def ogg_to_webm_audio(db_conn):
  41     metadata = MetaData(bind=db_conn.bind)
  42
  43     file_keynames = Table('core__file_keynames', metadata, autoload=True,
  44                           autoload_with=db_conn.bind)
  45
  46     db_conn.execute(
  47         file_keynames.update().where(file_keynames.c.name == 'ogg').
  48             values(name='webm_audio')
  49     )
  50     db_conn.commit()
  51
  52
  53 @RegisterMigration(2, MIGRATIONS)
  54 def add_wants_notification_column(db_conn):
  55     metadata = MetaData(bind=db_conn.bind)
  56
  57     users = Table('core__users', metadata, autoload=True,
  58             autoload_with=db_conn.bind)
  59
  60     col = Column('wants_comment_notification', Boolean,
  61             default=True, nullable=True)
  62     col.create(users, populate_defaults=True)
  63     db_conn.commit()
  64
  65
  66 @RegisterMigration(3, MIGRATIONS)
  67 def add_transcoding_progress(db_conn):
  68     metadata = MetaData(bind=db_conn.bind)
  69
  70     media_entry = inspect_table(metadata, 'core__media_entries')
  71
  72     col = Column('transcoding_progress', SmallInteger)
  73     col.create(media_entry)
  74     db_conn.commit()
  75
  76
  77 class Collection_v0(declarative_base()):
  78     __tablename__ = "core__collections"
  79
  80     id = Column(Integer, primary_key=True)
  81     title = Column(Unicode, nullable=False)
  82     slug = Column(Unicode)
  83     created = Column(DateTime, nullable=False, default=datetime.datetime.now,
  84         index=True)
  85     description = Column(UnicodeText)
  86     creator = Column(Integer, ForeignKey(User.id), nullable=False)
  87     items = Column(Integer, default=0)
  88
  89 class CollectionItem_v0(declarative_base()):
  90     __tablename__ = "core__collection_items"
  91
  92     id = Column(Integer, primary_key=True)
  93     media_entry = Column(
  94         Integer, ForeignKey(MediaEntry.id), nullable=False, index=True)
  95     collection = Column(Integer, ForeignKey(Collection.id), nullable=False)
  96     note = Column(UnicodeText, nullable=True)
  97     added = Column(DateTime, nullable=False, default=datetime.datetime.now)
  98     position = Column(Integer)
  99
 100     ## This should be activated, normally.
 101     ## But this would change the way the next migration used to work.
 102     ## So it's commented for now.
 103     __table_args__ = (
 104         UniqueConstraint('collection', 'media_entry'),
 105         {})
 106
 107 collectionitem_unique_constraint_done = False
 108
 109 @RegisterMigration(4, MIGRATIONS)
 110 def add_collection_tables(db_conn):
 111     Collection_v0.__table__.create(db_conn.bind)
 112     CollectionItem_v0.__table__.create(db_conn.bind)
 113
 114     global collectionitem_unique_constraint_done
 115     collectionitem_unique_constraint_done = True
 116
 117     db_conn.commit()
 118
 119
 120 @RegisterMigration(5, MIGRATIONS)
 121 def add_mediaentry_collected(db_conn):
 122     metadata = MetaData(bind=db_conn.bind)
 123
 124     media_entry = inspect_table(metadata, 'core__media_entries')
 125
 126     col = Column('collected', Integer, default=0)
 127     col.create(media_entry)
 128     db_conn.commit()
 129
 130
 131 class ProcessingMetaData_v0(declarative_base()):
 132     __tablename__ = 'core__processing_metadata'
 133
 134     id = Column(Integer, primary_key=True)
 135     media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=False,
 136             index=True)
 137     callback_url = Column(Unicode)
 138
 139 @RegisterMigration(6, MIGRATIONS)
 140 def create_processing_metadata_table(db):
 141     ProcessingMetaData_v0.__table__.create(db.bind)
 142     db.commit()
 143
 144
 145 # Okay, problem being:
 146 #  Migration #4 forgot to add the uniqueconstraint for the
 147 #  new tables. While creating the tables from scratch had
 148 #  the constraint enabled.
 149 #
 150 # So we have four situations that should end up at the same
 151 # db layout:
 152 #
 153 # 1. Fresh install.
 154 #    Well, easy. Just uses the tables in models.py
 155 # 2. Fresh install using a git version just before this migration
 156 #    The tables are all there, the unique constraint is also there.
 157 #    This migration should do nothing.
 158 #    But as we can't detect the uniqueconstraint easily,
 159 #    this migration just adds the constraint again.
 160 #    And possibly fails very loud. But ignores the failure.
 161 # 3. old install, not using git, just releases.
 162 #    This one will get the new tables in #4 (now with constraint!)
 163 #    And this migration is just skipped silently.
 164 # 4. old install, always on latest git.
 165 #    This one has the tables, but lacks the constraint.
 166 #    So this migration adds the constraint.
 167 @RegisterMigration(7, MIGRATIONS)
 168 def fix_CollectionItem_v0_constraint(db_conn):
 169     """Add the forgotten Constraint on CollectionItem"""
 170
 171     global collectionitem_unique_constraint_done
 172     if collectionitem_unique_constraint_done:
 173         # Reset it. Maybe the whole thing gets run again
 174         # For a different db?
 175         collectionitem_unique_constraint_done = False
 176         return
 177
 178     metadata = MetaData(bind=db_conn.bind)
 179
 180     CollectionItem_table = inspect_table(metadata, 'core__collection_items')
 181
 182     constraint = UniqueConstraint('collection', 'media_entry',
 183         name='core__collection_items_collection_media_entry_key',
 184         table=CollectionItem_table)
 185
 186     try:
 187         constraint.create()
 188     except ProgrammingError:
 189         # User probably has an install that was run since the
 190         # collection tables were added, so we don't need to run this migration.
 191         pass
 192
 193     db_conn.commit()
 194
 195
 196 @RegisterMigration(8, MIGRATIONS)
 197 def add_license_preference(db):
 198     metadata = MetaData(bind=db.bind)
 199
 200     user_table = inspect_table(metadata, 'core__users')
 201
 202     col = Column('license_preference', Unicode)
 203     col.create(user_table)
 204     db.commit()
 205
 206
 207 @RegisterMigration(9, MIGRATIONS)
 208 def mediaentry_new_slug_era(db):
 209     """
 210     Update for the new era for media type slugs.
 211
 212     Entries without slugs now display differently in the url like:
 213       /u/cwebber/m/id=251/
 214
 215     ... because of this, we should back-convert:
 216      - entries without slugs should be converted to use the id, if possible, to
 217        make old urls still work
 218      - slugs with = (or also : which is now also not allowed) to have those
 219        stripped out (small possibility of breakage here sadly)
 220     """
 221
 222     def slug_and_user_combo_exists(slug, uploader):
 223         return db.execute(
 224             media_table.select(
 225                 and_(media_table.c.uploader==uploader,
 226                      media_table.c.slug==slug))).first() is not None
 227
 228     def append_garbage_till_unique(row, new_slug):
 229         """
 230         Attach junk to this row until it's unique, then save it
 231         """
 232         if slug_and_user_combo_exists(new_slug, row.uploader):
 233             # okay, still no success;
 234             # let's whack junk on there till it's unique.
 235             new_slug += '-' + uuid.uuid4().hex[:4]
 236             # keep going if necessary!
 237             while slug_and_user_combo_exists(new_slug, row.uploader):
 238                 new_slug += uuid.uuid4().hex[:4]
 239
 240         db.execute(
 241             media_table.update(). \
 242             where(media_table.c.id==row.id). \
 243             values(slug=new_slug))
 244
 245     metadata = MetaData(bind=db.bind)
 246
 247     media_table = inspect_table(metadata, 'core__media_entries')
 248
 249     for row in db.execute(media_table.select()):
 250         # no slug, try setting to an id
 251         if not row.slug:
 252             append_garbage_till_unique(row, unicode(row.id))
 253         # has "=" or ":" in it... we're getting rid of those
 254         elif u"=" in row.slug or u":" in row.slug:
 255             append_garbage_till_unique(
 256                 row, row.slug.replace(u"=", u"-").replace(u":", u"-"))
 257
 258     db.commit()
 259
 260
 261 @RegisterMigration(10, MIGRATIONS)
 262 def unique_collections_slug(db):
 263     """Add unique constraint to collection slug"""
 264     metadata = MetaData(bind=db.bind)
 265     collection_table = inspect_table(metadata, "core__collections")
 266     existing_slugs = {}
 267     slugs_to_change = []
 268
 269     for row in db.execute(collection_table.select()):
 270         # if duplicate slug, generate a unique slug
 271         if row.creator in existing_slugs and row.slug in \
 272            existing_slugs[row.creator]:
 273             slugs_to_change.append(row.id)
 274         else:
 275             if not row.creator in existing_slugs:
 276                 existing_slugs[row.creator] = [row.slug]
 277             else:
 278                 existing_slugs[row.creator].append(row.slug)
 279
 280     for row_id in slugs_to_change:
 281         new_slug = unicode(uuid.uuid4())
 282         db.execute(collection_table.update().
 283                    where(collection_table.c.id == row_id).
 284                    values(slug=new_slug))
 285     # sqlite does not like to change the schema when a transaction(update) is
 286     # not yet completed
 287     db.commit()
 288
 289     constraint = UniqueConstraint('creator', 'slug',
 290                                   name='core__collection_creator_slug_key',
 291                                   table=collection_table)
 292     constraint.create()
 293
 294     db.commit()
 295
 296 @RegisterMigration(11, MIGRATIONS)
 297 def drop_token_related_User_columns(db):
 298     """
 299     Drop unneeded columns from the User table after switching to using
 300     itsdangerous tokens for email and forgot password verification.
 301     """
 302     metadata = MetaData(bind=db.bind)
 303     user_table = inspect_table(metadata, 'core__users')
 304
 305     verification_key = user_table.columns['verification_key']
 306     fp_verification_key = user_table.columns['fp_verification_key']
 307     fp_token_expire = user_table.columns['fp_token_expire']
 308
 309     verification_key.drop()
 310     fp_verification_key.drop()
 311     fp_token_expire.drop()
 312
 313     db.commit()
 314
 315
 316 class CommentSubscription_v0(declarative_base()):
 317     __tablename__ = 'core__comment_subscriptions'
 318     id = Column(Integer, primary_key=True)
 319
 320     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 321
 322     media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=False)
 323
 324     user_id = Column(Integer, ForeignKey(User.id), nullable=False)
 325
 326     notify = Column(Boolean, nullable=False, default=True)
 327     send_email = Column(Boolean, nullable=False, default=True)
 328
 329
 330 class Notification_v0(declarative_base()):
 331     __tablename__ = 'core__notifications'
 332     id = Column(Integer, primary_key=True)
 333     type = Column(Unicode)
 334
 335     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 336
 337     user_id = Column(Integer, ForeignKey(User.id), nullable=False,
 338                      index=True)
 339     seen = Column(Boolean, default=lambda: False, index=True)
 340
 341
 342 class CommentNotification_v0(Notification_v0):
 343     __tablename__ = 'core__comment_notifications'
 344     id = Column(Integer, ForeignKey(Notification_v0.id), primary_key=True)
 345
 346     subject_id = Column(Integer, ForeignKey(MediaComment.id))
 347
 348
 349 class ProcessingNotification_v0(Notification_v0):
 350     __tablename__ = 'core__processing_notifications'
 351
 352     id = Column(Integer, ForeignKey(Notification_v0.id), primary_key=True)
 353
 354     subject_id = Column(Integer, ForeignKey(MediaEntry.id))
 355
 356
 357 @RegisterMigration(12, MIGRATIONS)
 358 def add_new_notification_tables(db):
 359     metadata = MetaData(bind=db.bind)
 360
 361     user_table = inspect_table(metadata, 'core__users')
 362     mediaentry_table = inspect_table(metadata, 'core__media_entries')
 363     mediacomment_table = inspect_table(metadata, 'core__media_comments')
 364
 365     CommentSubscription_v0.__table__.create(db.bind)
 366
 367     Notification_v0.__table__.create(db.bind)
 368     CommentNotification_v0.__table__.create(db.bind)
 369     ProcessingNotification_v0.__table__.create(db.bind)
 370
 371     db.commit()
 372
 373
 374 @RegisterMigration(13, MIGRATIONS)
 375 def pw_hash_nullable(db):
 376     """Make pw_hash column nullable"""
 377     metadata = MetaData(bind=db.bind)
 378     user_table = inspect_table(metadata, "core__users")
 379
 380     user_table.c.pw_hash.alter(nullable=True)
 381
 382     # sqlite+sqlalchemy seems to drop this constraint during the
 383     # migration, so we add it back here for now a bit manually.
 384     if db.bind.url.drivername == 'sqlite':
 385         constraint = UniqueConstraint('username', table=user_table)
 386         constraint.create()
 387
 388     db.commit()
 389
 390
 391 # oauth1 migrations
 392 class Client_v0(declarative_base()):
 393     """
 394         Model representing a client - Used for API Auth
 395     """
 396     __tablename__ = "core__clients"
 397
 398     id = Column(Unicode, nullable=True, primary_key=True)
 399     secret = Column(Unicode, nullable=False)
 400     expirey = Column(DateTime, nullable=True)
 401     application_type = Column(Unicode, nullable=False)
 402     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 403     updated = Column(DateTime, nullable=False, default=datetime.datetime.now)
 404
 405     # optional stuff
 406     redirect_uri = Column(JSONEncoded, nullable=True)
 407     logo_url = Column(Unicode, nullable=True)
 408     application_name = Column(Unicode, nullable=True)
 409     contacts = Column(JSONEncoded, nullable=True)
 410
 411     def __repr__(self):
 412         if self.application_name:
 413             return "<Client {0} - {1}>".format(self.application_name, self.id)
 414         else:
 415             return "<Client {0}>".format(self.id)
 416
 417 class RequestToken_v0(declarative_base()):
 418     """
 419         Model for representing the request tokens
 420     """
 421     __tablename__ = "core__request_tokens"
 422
 423     token = Column(Unicode, primary_key=True)
 424     secret = Column(Unicode, nullable=False)
 425     client = Column(Unicode, ForeignKey(Client_v0.id))
 426     user = Column(Integer, ForeignKey(User.id), nullable=True)
 427     used = Column(Boolean, default=False)
 428     authenticated = Column(Boolean, default=False)
 429     verifier = Column(Unicode, nullable=True)
 430     callback = Column(Unicode, nullable=False, default=u"oob")
 431     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 432     updated = Column(DateTime, nullable=False, default=datetime.datetime.now)
 433
 434 class AccessToken_v0(declarative_base()):
 435     """
 436         Model for representing the access tokens
 437     """
 438     __tablename__ = "core__access_tokens"
 439
 440     token = Column(Unicode, nullable=False, primary_key=True)
 441     secret = Column(Unicode, nullable=False)
 442     user = Column(Integer, ForeignKey(User.id))
 443     request_token = Column(Unicode, ForeignKey(RequestToken_v0.token))
 444     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 445     updated = Column(DateTime, nullable=False, default=datetime.datetime.now)
 446
 447
 448 class NonceTimestamp_v0(declarative_base()):
 449     """
 450         A place the timestamp and nonce can be stored - this is for OAuth1
 451     """
 452     __tablename__ = "core__nonce_timestamps"
 453
 454     nonce = Column(Unicode, nullable=False, primary_key=True)
 455     timestamp = Column(DateTime, nullable=False, primary_key=True)
 456
 457
 458 @RegisterMigration(14, MIGRATIONS)
 459 def create_oauth1_tables(db):
 460     """ Creates the OAuth1 tables """
 461
 462     Client_v0.__table__.create(db.bind)
 463     RequestToken_v0.__table__.create(db.bind)
 464     AccessToken_v0.__table__.create(db.bind)
 465     NonceTimestamp_v0.__table__.create(db.bind)
 466
 467     db.commit()
 468
 469
 470 @RegisterMigration(15, MIGRATIONS)
 471 def wants_notifications(db):
 472     """Add a wants_notifications field to User model"""
 473     metadata = MetaData(bind=db.bind)
 474     user_table = inspect_table(metadata, "core__users")
 475     col = Column('wants_notifications', Boolean, default=True)
 476     col.create(user_table)
 477     db.commit()
 478
 479
 480
 481 @RegisterMigration(16, MIGRATIONS)
 482 def upload_limits(db):
 483     """Add user upload limit columns"""
 484     metadata = MetaData(bind=db.bind)
 485
 486     user_table = inspect_table(metadata, 'core__users')
 487     media_entry_table = inspect_table(metadata, 'core__media_entries')
 488
 489     col = Column('uploaded', Integer, default=0)
 490     col.create(user_table)
 491
 492     col = Column('upload_limit', Integer)
 493     col.create(user_table)
 494
 495     col = Column('file_size', Integer, default=0)
 496     col.create(media_entry_table)
 497
 498     db.commit()
 499
 500
 501 @RegisterMigration(17, MIGRATIONS)
 502 def add_file_metadata(db):
 503     """Add file_metadata to MediaFile"""
 504     metadata = MetaData(bind=db.bind)
 505     media_file_table = inspect_table(metadata, "core__mediafiles")
 506
 507     col = Column('file_metadata', MutationDict.as_mutable(JSONEncoded))
 508     col.create(media_file_table)
 509
 510     db.commit()
 511
 512 ###################
 513 # Moderation tables
 514 ###################
 515
 516 class ReportBase_v0(declarative_base()):
 517     __tablename__ = 'core__reports'
 518     id = Column(Integer, primary_key=True)
 519     reporter_id = Column(Integer, ForeignKey(User.id), nullable=False)
 520     report_content = Column(UnicodeText)
 521     reported_user_id = Column(Integer, ForeignKey(User.id), nullable=False)
 522     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 523     discriminator = Column('type', Unicode(50))
 524     resolver_id = Column(Integer, ForeignKey(User.id))
 525     resolved = Column(DateTime)
 526     result = Column(UnicodeText)
 527     __mapper_args__ = {'polymorphic_on': discriminator}
 528
 529
 530 class CommentReport_v0(ReportBase_v0):
 531     __tablename__ = 'core__reports_on_comments'
 532     __mapper_args__ = {'polymorphic_identity': 'comment_report'}
 533
 534     id = Column('id',Integer, ForeignKey('core__reports.id'),
 535                                                 primary_key=True)
 536     comment_id = Column(Integer, ForeignKey(MediaComment.id), nullable=True)
 537
 538
 539 class MediaReport_v0(ReportBase_v0):
 540     __tablename__ = 'core__reports_on_media'
 541     __mapper_args__ = {'polymorphic_identity': 'media_report'}
 542
 543     id = Column('id',Integer, ForeignKey('core__reports.id'), primary_key=True)
 544     media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=True)
 545
 546
 547 class UserBan_v0(declarative_base()):
 548     __tablename__ = 'core__user_bans'
 549     user_id = Column(Integer, ForeignKey(User.id), nullable=False,
 550                                          primary_key=True)
 551     expiration_date = Column(Date)
 552     reason = Column(UnicodeText, nullable=False)
 553
 554
 555 class Privilege_v0(declarative_base()):
 556     __tablename__ = 'core__privileges'
 557     id = Column(Integer, nullable=False, primary_key=True, unique=True)
 558     privilege_name = Column(Unicode, nullable=False, unique=True)
 559
 560
 561 class PrivilegeUserAssociation_v0(declarative_base()):
 562     __tablename__ = 'core__privileges_users'
 563     privilege_id = Column(
 564         'core__privilege_id',
 565         Integer,
 566         ForeignKey(User.id),
 567         primary_key=True)
 568     user_id = Column(
 569         'core__user_id',
 570         Integer,
 571         ForeignKey(Privilege.id),
 572         primary_key=True)
 573
 574
 575 PRIVILEGE_FOUNDATIONS_v0 = [{'privilege_name':u'admin'},
 576                             {'privilege_name':u'moderator'},
 577                             {'privilege_name':u'uploader'},
 578                             {'privilege_name':u'reporter'},
 579                             {'privilege_name':u'commenter'},
 580                             {'privilege_name':u'active'}]
 581
 582
 583 # vR1 stands for "version Rename 1".  This only exists because we need
 584 # to deal with dropping some booleans and it's otherwise impossible
 585 # with sqlite.
 586
 587 class User_vR1(declarative_base()):
 588     __tablename__ = 'rename__users'
 589     id = Column(Integer, primary_key=True)
 590     username = Column(Unicode, nullable=False, unique=True)
 591     email = Column(Unicode, nullable=False)
 592     pw_hash = Column(Unicode)
 593     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 594     wants_comment_notification = Column(Boolean, default=True)
 595     wants_notifications = Column(Boolean, default=True)
 596     license_preference = Column(Unicode)
 597     url = Column(Unicode)
 598     bio = Column(UnicodeText)  # ??
 599     uploaded = Column(Integer, default=0)
 600     upload_limit = Column(Integer)
 601
 602
 603 @RegisterMigration(18, MIGRATIONS)
 604 def create_moderation_tables(db):
 605
 606     # First, we will create the new tables in the database.
 607     #--------------------------------------------------------------------------
 608     ReportBase_v0.__table__.create(db.bind)
 609     CommentReport_v0.__table__.create(db.bind)
 610     MediaReport_v0.__table__.create(db.bind)
 611     UserBan_v0.__table__.create(db.bind)
 612     Privilege_v0.__table__.create(db.bind)
 613     PrivilegeUserAssociation_v0.__table__.create(db.bind)
 614
 615     db.commit()
 616
 617     # Then initialize the tables that we will later use
 618     #--------------------------------------------------------------------------
 619     metadata = MetaData(bind=db.bind)
 620     privileges_table= inspect_table(metadata, "core__privileges")
 621     user_table = inspect_table(metadata, 'core__users')
 622     user_privilege_assoc = inspect_table(
 623         metadata, 'core__privileges_users')
 624
 625     # This section initializes the default Privilege foundations, that
 626     # would be created through the FOUNDATIONS system in a new instance
 627     #--------------------------------------------------------------------------
 628     for parameters in PRIVILEGE_FOUNDATIONS_v0:
 629         db.execute(privileges_table.insert().values(**parameters))
 630
 631     db.commit()
 632
 633     # This next section takes the information from the old is_admin and status
 634     # columns and converts those to the new privilege system
 635     #--------------------------------------------------------------------------
 636     admin_users_ids, active_users_ids, inactive_users_ids = (
 637         db.execute(
 638             user_table.select().where(
 639                 user_table.c.is_admin==True)).fetchall(),
 640         db.execute(
 641             user_table.select().where(
 642                 user_table.c.is_admin==False).where(
 643                 user_table.c.status==u"active")).fetchall(),
 644         db.execute(
 645             user_table.select().where(
 646                 user_table.c.is_admin==False).where(
 647                 user_table.c.status!=u"active")).fetchall())
 648
 649     # Get the ids for each of the privileges so we can reference them ~~~~~~~~~
 650     (admin_privilege_id, uploader_privilege_id,
 651      reporter_privilege_id, commenter_privilege_id,
 652      active_privilege_id) = [
 653         db.execute(privileges_table.select().where(
 654             privileges_table.c.privilege_name==privilege_name)).first()['id']
 655         for privilege_name in
 656             [u"admin",u"uploader",u"reporter",u"commenter",u"active"]
 657     ]
 658
 659     # Give each user the appopriate privileges depending whether they are an
 660     # admin, an active user or an inactive user ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 661     for admin_user in admin_users_ids:
 662         admin_user_id = admin_user['id']
 663         for privilege_id in [admin_privilege_id, uploader_privilege_id,
 664                             reporter_privilege_id, commenter_privilege_id,
 665                             active_privilege_id]:
 666             db.execute(user_privilege_assoc.insert().values(
 667                 core__privilege_id=admin_user_id,
 668                 core__user_id=privilege_id))
 669
 670     for active_user in active_users_ids:
 671         active_user_id = active_user['id']
 672         for privilege_id in [uploader_privilege_id, reporter_privilege_id,
 673                             commenter_privilege_id, active_privilege_id]:
 674             db.execute(user_privilege_assoc.insert().values(
 675                 core__privilege_id=active_user_id,
 676                 core__user_id=privilege_id))
 677
 678     for inactive_user in inactive_users_ids:
 679         inactive_user_id = inactive_user['id']
 680         for privilege_id in [uploader_privilege_id, reporter_privilege_id,
 681                              commenter_privilege_id]:
 682             db.execute(user_privilege_assoc.insert().values(
 683                 core__privilege_id=inactive_user_id,
 684                 core__user_id=privilege_id))
 685
 686     db.commit()
 687
 688     # And then, once the information is taken from is_admin & status columns
 689     # we drop all of the vestigial columns from the User table.
 690     #--------------------------------------------------------------------------
 691     if db.bind.url.drivername == 'sqlite':
 692         # SQLite has some issues that make it *impossible* to drop boolean
 693         # columns. So, the following code is a very hacky workaround which
 694         # makes it possible. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 695
 696         User_vR1.__table__.create(db.bind)
 697         db.commit()
 698         new_user_table = inspect_table(metadata, 'rename__users')
 699         replace_table_hack(db, user_table, new_user_table)
 700     else:
 701         # If the db is not run using SQLite, this process is much simpler ~~~~~
 702
 703         status = user_table.columns['status']
 704         email_verified = user_table.columns['email_verified']
 705         is_admin = user_table.columns['is_admin']
 706         status.drop()
 707         email_verified.drop()
 708         is_admin.drop()
 709
 710     db.commit()
 711 @RegisterMigration(19, MIGRATIONS)
 712 def drop_MediaEntry_collected(db):
 713     """
 714     Drop unused MediaEntry.collected column
 715     """
 716     metadata = MetaData(bind=db.bind)
 717
 718     media_collected= inspect_table(metadata, 'core__media_entries')
 719     media_collected = media_collected.columns['collected']
 720
 721     media_collected.drop()
 722
 723     db.commit()
 724
 725 @RegisterMigration(20, MIGRATIONS)
 726 def add_work_metadata_column(db):
 727     metadata = MetaData(bind=db.bind)
 728
 729     media_file = inspect_table(metadata, 'core__mediafiles')
 730
 731     col = Column('work_metadata', MutationDict.as_mutable(JSONEncoded))
 732     col.create(media_file)
 733
 734     db.commit()