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)
  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 from mediagoblin.db.migration_tools import RegisterMigration, inspect_table
  29 from mediagoblin.db.models import MediaEntry, Collection, User, MediaComment
  30
  31 MIGRATIONS = {}
  32
  33
  34 @RegisterMigration(1, MIGRATIONS)
  35 def ogg_to_webm_audio(db_conn):
  36     metadata = MetaData(bind=db_conn.bind)
  37
  38     file_keynames = Table('core__file_keynames', metadata, autoload=True,
  39                           autoload_with=db_conn.bind)
  40
  41     db_conn.execute(
  42         file_keynames.update().where(file_keynames.c.name == 'ogg').
  43             values(name='webm_audio')
  44     )
  45     db_conn.commit()
  46
  47
  48 @RegisterMigration(2, MIGRATIONS)
  49 def add_wants_notification_column(db_conn):
  50     metadata = MetaData(bind=db_conn.bind)
  51
  52     users = Table('core__users', metadata, autoload=True,
  53             autoload_with=db_conn.bind)
  54
  55     col = Column('wants_comment_notification', Boolean,
  56             default=True, nullable=True)
  57     col.create(users, populate_defaults=True)
  58     db_conn.commit()
  59
  60
  61 @RegisterMigration(3, MIGRATIONS)
  62 def add_transcoding_progress(db_conn):
  63     metadata = MetaData(bind=db_conn.bind)
  64
  65     media_entry = inspect_table(metadata, 'core__media_entries')
  66
  67     col = Column('transcoding_progress', SmallInteger)
  68     col.create(media_entry)
  69     db_conn.commit()
  70
  71
  72 class Collection_v0(declarative_base()):
  73     __tablename__ = "core__collections"
  74
  75     id = Column(Integer, primary_key=True)
  76     title = Column(Unicode, nullable=False)
  77     slug = Column(Unicode)
  78     created = Column(DateTime, nullable=False, default=datetime.datetime.now,
  79         index=True)
  80     description = Column(UnicodeText)
  81     creator = Column(Integer, ForeignKey(User.id), nullable=False)
  82     items = Column(Integer, default=0)
  83
  84 class CollectionItem_v0(declarative_base()):
  85     __tablename__ = "core__collection_items"
  86
  87     id = Column(Integer, primary_key=True)
  88     media_entry = Column(
  89         Integer, ForeignKey(MediaEntry.id), nullable=False, index=True)
  90     collection = Column(Integer, ForeignKey(Collection.id), nullable=False)
  91     note = Column(UnicodeText, nullable=True)
  92     added = Column(DateTime, nullable=False, default=datetime.datetime.now)
  93     position = Column(Integer)
  94
  95     ## This should be activated, normally.
  96     ## But this would change the way the next migration used to work.
  97     ## So it's commented for now.
  98     __table_args__ = (
  99         UniqueConstraint('collection', 'media_entry'),
 100         {})
 101
 102 collectionitem_unique_constraint_done = False
 103
 104 @RegisterMigration(4, MIGRATIONS)
 105 def add_collection_tables(db_conn):
 106     Collection_v0.__table__.create(db_conn.bind)
 107     CollectionItem_v0.__table__.create(db_conn.bind)
 108
 109     global collectionitem_unique_constraint_done
 110     collectionitem_unique_constraint_done = True
 111
 112     db_conn.commit()
 113
 114
 115 @RegisterMigration(5, MIGRATIONS)
 116 def add_mediaentry_collected(db_conn):
 117     metadata = MetaData(bind=db_conn.bind)
 118
 119     media_entry = inspect_table(metadata, 'core__media_entries')
 120
 121     col = Column('collected', Integer, default=0)
 122     col.create(media_entry)
 123     db_conn.commit()
 124
 125
 126 class ProcessingMetaData_v0(declarative_base()):
 127     __tablename__ = 'core__processing_metadata'
 128
 129     id = Column(Integer, primary_key=True)
 130     media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=False,
 131             index=True)
 132     callback_url = Column(Unicode)
 133
 134 @RegisterMigration(6, MIGRATIONS)
 135 def create_processing_metadata_table(db):
 136     ProcessingMetaData_v0.__table__.create(db.bind)
 137     db.commit()
 138
 139
 140 # Okay, problem being:
 141 #  Migration #4 forgot to add the uniqueconstraint for the
 142 #  new tables. While creating the tables from scratch had
 143 #  the constraint enabled.
 144 #
 145 # So we have four situations that should end up at the same
 146 # db layout:
 147 #
 148 # 1. Fresh install.
 149 #    Well, easy. Just uses the tables in models.py
 150 # 2. Fresh install using a git version just before this migration
 151 #    The tables are all there, the unique constraint is also there.
 152 #    This migration should do nothing.
 153 #    But as we can't detect the uniqueconstraint easily,
 154 #    this migration just adds the constraint again.
 155 #    And possibly fails very loud. But ignores the failure.
 156 # 3. old install, not using git, just releases.
 157 #    This one will get the new tables in #4 (now with constraint!)
 158 #    And this migration is just skipped silently.
 159 # 4. old install, always on latest git.
 160 #    This one has the tables, but lacks the constraint.
 161 #    So this migration adds the constraint.
 162 @RegisterMigration(7, MIGRATIONS)
 163 def fix_CollectionItem_v0_constraint(db_conn):
 164     """Add the forgotten Constraint on CollectionItem"""
 165
 166     global collectionitem_unique_constraint_done
 167     if collectionitem_unique_constraint_done:
 168         # Reset it. Maybe the whole thing gets run again
 169         # For a different db?
 170         collectionitem_unique_constraint_done = False
 171         return
 172
 173     metadata = MetaData(bind=db_conn.bind)
 174
 175     CollectionItem_table = inspect_table(metadata, 'core__collection_items')
 176
 177     constraint = UniqueConstraint('collection', 'media_entry',
 178         name='core__collection_items_collection_media_entry_key',
 179         table=CollectionItem_table)
 180
 181     try:
 182         constraint.create()
 183     except ProgrammingError:
 184         # User probably has an install that was run since the
 185         # collection tables were added, so we don't need to run this migration.
 186         pass
 187
 188     db_conn.commit()
 189
 190
 191 @RegisterMigration(8, MIGRATIONS)
 192 def add_license_preference(db):
 193     metadata = MetaData(bind=db.bind)
 194
 195     user_table = inspect_table(metadata, 'core__users')
 196
 197     col = Column('license_preference', Unicode)
 198     col.create(user_table)
 199     db.commit()
 200
 201
 202 @RegisterMigration(9, MIGRATIONS)
 203 def mediaentry_new_slug_era(db):
 204     """
 205     Update for the new era for media type slugs.
 206
 207     Entries without slugs now display differently in the url like:
 208       /u/cwebber/m/id=251/
 209
 210     ... because of this, we should back-convert:
 211      - entries without slugs should be converted to use the id, if possible, to
 212        make old urls still work
 213      - slugs with = (or also : which is now also not allowed) to have those
 214        stripped out (small possibility of breakage here sadly)
 215     """
 216
 217     def slug_and_user_combo_exists(slug, uploader):
 218         return db.execute(
 219             media_table.select(
 220                 and_(media_table.c.uploader==uploader,
 221                      media_table.c.slug==slug))).first() is not None
 222
 223     def append_garbage_till_unique(row, new_slug):
 224         """
 225         Attach junk to this row until it's unique, then save it
 226         """
 227         if slug_and_user_combo_exists(new_slug, row.uploader):
 228             # okay, still no success;
 229             # let's whack junk on there till it's unique.
 230             new_slug += '-' + uuid.uuid4().hex[:4]
 231             # keep going if necessary!
 232             while slug_and_user_combo_exists(new_slug, row.uploader):
 233                 new_slug += uuid.uuid4().hex[:4]
 234
 235         db.execute(
 236             media_table.update(). \
 237             where(media_table.c.id==row.id). \
 238             values(slug=new_slug))
 239
 240     metadata = MetaData(bind=db.bind)
 241
 242     media_table = inspect_table(metadata, 'core__media_entries')
 243
 244     for row in db.execute(media_table.select()):
 245         # no slug, try setting to an id
 246         if not row.slug:
 247             append_garbage_till_unique(row, unicode(row.id))
 248         # has "=" or ":" in it... we're getting rid of those
 249         elif u"=" in row.slug or u":" in row.slug:
 250             append_garbage_till_unique(
 251                 row, row.slug.replace(u"=", u"-").replace(u":", u"-"))
 252
 253     db.commit()
 254
 255
 256 @RegisterMigration(10, MIGRATIONS)
 257 def unique_collections_slug(db):
 258     """Add unique constraint to collection slug"""
 259     metadata = MetaData(bind=db.bind)
 260     collection_table = inspect_table(metadata, "core__collections")
 261     existing_slugs = {}
 262     slugs_to_change = []
 263
 264     for row in db.execute(collection_table.select()):
 265         # if duplicate slug, generate a unique slug
 266         if row.creator in existing_slugs and row.slug in \
 267            existing_slugs[row.creator]:
 268             slugs_to_change.append(row.id)
 269         else:
 270             if not row.creator in existing_slugs:
 271                 existing_slugs[row.creator] = [row.slug]
 272             else:
 273                 existing_slugs[row.creator].append(row.slug)
 274
 275     for row_id in slugs_to_change:
 276         new_slug = unicode(uuid.uuid4())
 277         db.execute(collection_table.update().
 278                    where(collection_table.c.id == row_id).
 279                    values(slug=new_slug))
 280     # sqlite does not like to change the schema when a transaction(update) is
 281     # not yet completed
 282     db.commit()
 283
 284     constraint = UniqueConstraint('creator', 'slug',
 285                                   name='core__collection_creator_slug_key',
 286                                   table=collection_table)
 287     constraint.create()
 288
 289     db.commit()
 290
 291 @RegisterMigration(11, MIGRATIONS)
 292 def drop_token_related_User_columns(db):
 293     """
 294     Drop unneeded columns from the User table after switching to using
 295     itsdangerous tokens for email and forgot password verification.
 296     """
 297     metadata = MetaData(bind=db.bind)
 298     user_table = inspect_table(metadata, 'core__users')
 299
 300     verification_key = user_table.columns['verification_key']
 301     fp_verification_key = user_table.columns['fp_verification_key']
 302     fp_token_expire = user_table.columns['fp_token_expire']
 303
 304     verification_key.drop()
 305     fp_verification_key.drop()
 306     fp_token_expire.drop()
 307
 308     db.commit()
 309
 310
 311 class CommentSubscription_v0(declarative_base()):
 312     __tablename__ = 'core__comment_subscriptions'
 313     id = Column(Integer, primary_key=True)
 314
 315     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 316
 317     media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=False)
 318
 319     user_id = Column(Integer, ForeignKey(User.id), nullable=False)
 320
 321     notify = Column(Boolean, nullable=False, default=True)
 322     send_email = Column(Boolean, nullable=False, default=True)
 323
 324
 325 class Notification_v0(declarative_base()):
 326     __tablename__ = 'core__notifications'
 327     id = Column(Integer, primary_key=True)
 328     type = Column(Unicode)
 329
 330     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 331
 332     user_id = Column(Integer, ForeignKey(User.id), nullable=False,
 333                      index=True)
 334     seen = Column(Boolean, default=lambda: False, index=True)
 335
 336
 337 class CommentNotification_v0(Notification_v0):
 338     __tablename__ = 'core__comment_notifications'
 339     id = Column(Integer, ForeignKey(Notification_v0.id), primary_key=True)
 340
 341     subject_id = Column(Integer, ForeignKey(MediaComment.id))
 342
 343
 344 class ProcessingNotification_v0(Notification_v0):
 345     __tablename__ = 'core__processing_notifications'
 346
 347     id = Column(Integer, ForeignKey(Notification_v0.id), primary_key=True)
 348
 349     subject_id = Column(Integer, ForeignKey(MediaEntry.id))
 350
 351
 352 @RegisterMigration(12, MIGRATIONS)
 353 def add_new_notification_tables(db):
 354     metadata = MetaData(bind=db.bind)
 355
 356     user_table = inspect_table(metadata, 'core__users')
 357     mediaentry_table = inspect_table(metadata, 'core__media_entries')
 358     mediacomment_table = inspect_table(metadata, 'core__media_comments')
 359
 360     CommentSubscription_v0.__table__.create(db.bind)
 361
 362     Notification_v0.__table__.create(db.bind)
 363     CommentNotification_v0.__table__.create(db.bind)
 364     ProcessingNotification_v0.__table__.create(db.bind)
 365
 366
 367 @RegisterMigration(13, MIGRATIONS)
 368 def pw_hash_nullable(db):
 369     """Make pw_hash column nullable"""
 370     metadata = MetaData(bind=db.bind)
 371     user_table = inspect_table(metadata, "core__users")
 372
 373     user_table.c.pw_hash.alter(nullable=True)
 374
 375     # sqlite+sqlalchemy seems to drop this constraint during the
 376     # migration, so we add it back here for now a bit manually.
 377     if db.bind.url.drivername == 'sqlite':
 378         constraint = UniqueConstraint('username', table=user_table)
 379         constraint.create()
 380
 381     db.commit()