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