def revert_username_index(db):
"""
Revert the stuff we did in migration 22 above.
There were a couple of problems with what we did:
- There was never a need for this migration! The unique
constraint had an implicit b-tree index, so it wasn't really
needed. (This is my (Chris Webber's) fault for suggesting it
needed to happen without knowing what's going on... my bad!)
- On top of that, databases created after the models.py was
changed weren't the same as those that had been run through
migration 22 above.
As such, we're setting things back to the way they were before,
but as it turns out, that's tricky to do!
"""
metadata = MetaData(bind=db.bind)
user_table = inspect_table(metadata, "core__users")
indexes = dict(
[(index.name, index) for index in user_table.indexes])
# index from unnecessary migration
users_uploader_index = indexes.get(u'ix_core__users_uploader')
# index created from models.py after (unique=True, index=True)
# was set in models.py
users_username_index = indexes.get(u'ix_core__users_username')
if users_uploader_index is None and users_username_index is None:
# We don't need to do anything.
# The database isn't in a state where it needs fixing
#
# (ie, either went through the previous borked migration or
# was initialized with a models.py where core__users was both
# unique=True and index=True)
return
if db.bind.url.drivername == 'sqlite':
# Again, sqlite has problems. So this is tricky.
# Yes, this is correct to use User_vR1! Nothing has changed
# between the *correct* version of this table and migration 18.
User_vR1.__table__.create(db.bind)
db.commit()
new_user_table = inspect_table(metadata, 'rename__users')
replace_table_hack(db, user_table, new_user_table)
else:
# If the db is not run using SQLite, we don't need to do crazy
# table copying.
# Remove whichever of the not-used indexes are in place
if users_uploader_index is not None:
users_uploader_index.drop()
if users_username_index is not None:
users_username_index.drop()
# Given we're removing indexes then adding a unique constraint
# which *we know might fail*, thus probably rolling back the
# session, let's commit here.
db.commit()
try:
# Add the unique constraint
constraint = UniqueConstraint(
'username', table=user_table)
constraint.create()
except ProgrammingError:
# constraint already exists, no need to add
db.rollback()
db.commit()
def create_moderation_tables(db):
# First, we will create the new tables in the database.
#--------------------------------------------------------------------------
ReportBase_v0.__table__.create(db.bind)
CommentReport_v0.__table__.create(db.bind)
MediaReport_v0.__table__.create(db.bind)
UserBan_v0.__table__.create(db.bind)
Privilege_v0.__table__.create(db.bind)
PrivilegeUserAssociation_v0.__table__.create(db.bind)
db.commit()
# Then initialize the tables that we will later use
#--------------------------------------------------------------------------
metadata = MetaData(bind=db.bind)
privileges_table= inspect_table(metadata, "core__privileges")
user_table = inspect_table(metadata, 'core__users')
user_privilege_assoc = inspect_table(
metadata, 'core__privileges_users')
# This section initializes the default Privilege foundations, that
# would be created through the FOUNDATIONS system in a new instance
#--------------------------------------------------------------------------
for parameters in PRIVILEGE_FOUNDATIONS_v0:
db.execute(privileges_table.insert().values(**parameters))
db.commit()
# This next section takes the information from the old is_admin and status
# columns and converts those to the new privilege system
#--------------------------------------------------------------------------
admin_users_ids, active_users_ids, inactive_users_ids = (
db.execute(
user_table.select().where(
user_table.c.is_admin==True)).fetchall(),
db.execute(
user_table.select().where(
user_table.c.is_admin==False).where(
user_table.c.status==u"active")).fetchall(),
db.execute(
user_table.select().where(
user_table.c.is_admin==False).where(
user_table.c.status!=u"active")).fetchall())
# Get the ids for each of the privileges so we can reference them ~~~~~~~~~
(admin_privilege_id, uploader_privilege_id,
reporter_privilege_id, commenter_privilege_id,
active_privilege_id) = [
db.execute(privileges_table.select().where(
privileges_table.c.privilege_name==privilege_name)).first()['id']
for privilege_name in
[u"admin",u"uploader",u"reporter",u"commenter",u"active"]
]
# Give each user the appopriate privileges depending whether they are an
# admin, an active user or an inactive user ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
for admin_user in admin_users_ids:
admin_user_id = admin_user['id']
for privilege_id in [admin_privilege_id, uploader_privilege_id,
reporter_privilege_id, commenter_privilege_id,
active_privilege_id]:
db.execute(user_privilege_assoc.insert().values(
core__privilege_id=admin_user_id,
core__user_id=privilege_id))
for active_user in active_users_ids:
active_user_id = active_user['id']
for privilege_id in [uploader_privilege_id, reporter_privilege_id,
commenter_privilege_id, active_privilege_id]:
db.execute(user_privilege_assoc.insert().values(
core__privilege_id=active_user_id,
core__user_id=privilege_id))
for inactive_user in inactive_users_ids:
inactive_user_id = inactive_user['id']
for privilege_id in [uploader_privilege_id, reporter_privilege_id,
commenter_privilege_id]:
db.execute(user_privilege_assoc.insert().values(
core__privilege_id=inactive_user_id,
core__user_id=privilege_id))
db.commit()
# And then, once the information is taken from is_admin & status columns
# we drop all of the vestigial columns from the User table.
#--------------------------------------------------------------------------
if db.bind.url.drivername == 'sqlite':
# SQLite has some issues that make it *impossible* to drop boolean
# columns. So, the following code is a very hacky workaround which
# makes it possible. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
User_vR1.__table__.create(db.bind)
db.commit()
new_user_table = inspect_table(metadata, 'rename__users')
replace_table_hack(db, user_table, new_user_table)
else:
# If the db is not run using SQLite, this process is much simpler ~~~~~
status = user_table.columns['status']
email_verified = user_table.columns['email_verified']
is_admin = user_table.columns['is_admin']
status.drop()
email_verified.drop()
is_admin.drop()
db.commit()
