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