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