# along with this program. If not, see <http://www.gnu.org/licenses/>.
import datetime
+import uuid
from sqlalchemy import (MetaData, Table, Column, Boolean, SmallInteger,
Integer, Unicode, UnicodeText, DateTime,
ForeignKey)
from sqlalchemy.exc import ProgrammingError
from sqlalchemy.ext.declarative import declarative_base
+from sqlalchemy.sql import and_
from migrate.changeset.constraint import UniqueConstraint
+
+from mediagoblin.db.extratypes import JSONEncoded
from mediagoblin.db.migration_tools import RegisterMigration, inspect_table
-from mediagoblin.db.models import MediaEntry, Collection, User
+from mediagoblin.db.models import MediaEntry, Collection, User, MediaComment
MIGRATIONS = {}
- slugs with = (or also : which is now also not allowed) to have those
stripped out (small possibility of breakage here sadly)
"""
- import uuid
def slug_and_user_combo_exists(slug, uploader):
- # Technically returns the number of entries with this slug and user
- # that already exist
return db.execute(
media_table.select(
- media_table.c.uploader==uploader,
- media_table.c.slug==slug).count()).first().tbl_row_count
+ and_(media_table.c.uploader==uploader,
+ media_table.c.slug==slug))).first() is not None
def append_garbage_till_unique(row, new_slug):
"""
metadata = MetaData(bind=db.bind)
media_table = inspect_table(metadata, 'core__media_entries')
+
for row in db.execute(media_table.select()):
# no slug, try setting to an id
if not row.slug:
elif u"=" in row.slug or u":" in row.slug:
append_garbage_till_unique(
row, row.slug.replace(u"=", u"-").replace(u":", u"-"))
+
+ db.commit()
+
+
+@RegisterMigration(10, MIGRATIONS)
+def unique_collections_slug(db):
+ """Add unique constraint to collection slug"""
+ metadata = MetaData(bind=db.bind)
+ collection_table = inspect_table(metadata, "core__collections")
+ existing_slugs = {}
+ slugs_to_change = []
+
+ for row in db.execute(collection_table.select()):
+ # if duplicate slug, generate a unique slug
+ if row.creator in existing_slugs and row.slug in \
+ existing_slugs[row.creator]:
+ slugs_to_change.append(row.id)
+ else:
+ if not row.creator in existing_slugs:
+ existing_slugs[row.creator] = [row.slug]
+ else:
+ existing_slugs[row.creator].append(row.slug)
+
+ for row_id in slugs_to_change:
+ new_slug = unicode(uuid.uuid4())
+ db.execute(collection_table.update().
+ where(collection_table.c.id == row_id).
+ values(slug=new_slug))
+ # sqlite does not like to change the schema when a transaction(update) is
+ # not yet completed
+ db.commit()
+
+ constraint = UniqueConstraint('creator', 'slug',
+ name='core__collection_creator_slug_key',
+ table=collection_table)
+ constraint.create()
+
+ db.commit()
+
+@RegisterMigration(11, MIGRATIONS)
+def drop_token_related_User_columns(db):
+ """
+ Drop unneeded columns from the User table after switching to using
+ itsdangerous tokens for email and forgot password verification.
+ """
+ metadata = MetaData(bind=db.bind)
+ user_table = inspect_table(metadata, 'core__users')
+
+ verification_key = user_table.columns['verification_key']
+ fp_verification_key = user_table.columns['fp_verification_key']
+ fp_token_expire = user_table.columns['fp_token_expire']
+
+ verification_key.drop()
+ fp_verification_key.drop()
+ fp_token_expire.drop()
+
+ db.commit()
+
+
+class CommentSubscription_v0(declarative_base()):
+ __tablename__ = 'core__comment_subscriptions'
+ id = Column(Integer, primary_key=True)
+
+ created = Column(DateTime, nullable=False, default=datetime.datetime.now)
+
+ media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=False)
+
+ user_id = Column(Integer, ForeignKey(User.id), nullable=False)
+
+ notify = Column(Boolean, nullable=False, default=True)
+ send_email = Column(Boolean, nullable=False, default=True)
+
+
+class Notification_v0(declarative_base()):
+ __tablename__ = 'core__notifications'
+ id = Column(Integer, primary_key=True)
+ type = Column(Unicode)
+
+ created = Column(DateTime, nullable=False, default=datetime.datetime.now)
+
+ user_id = Column(Integer, ForeignKey(User.id), nullable=False,
+ index=True)
+ seen = Column(Boolean, default=lambda: False, index=True)
+
+
+class CommentNotification_v0(Notification_v0):
+ __tablename__ = 'core__comment_notifications'
+ id = Column(Integer, ForeignKey(Notification_v0.id), primary_key=True)
+
+ subject_id = Column(Integer, ForeignKey(MediaComment.id))
+
+
+class ProcessingNotification_v0(Notification_v0):
+ __tablename__ = 'core__processing_notifications'
+
+ id = Column(Integer, ForeignKey(Notification_v0.id), primary_key=True)
+
+ subject_id = Column(Integer, ForeignKey(MediaEntry.id))
+
+
+@RegisterMigration(12, MIGRATIONS)
+def add_new_notification_tables(db):
+ metadata = MetaData(bind=db.bind)
+
+ user_table = inspect_table(metadata, 'core__users')
+ mediaentry_table = inspect_table(metadata, 'core__media_entries')
+ mediacomment_table = inspect_table(metadata, 'core__media_comments')
+
+ CommentSubscription_v0.__table__.create(db.bind)
+
+ Notification_v0.__table__.create(db.bind)
+ CommentNotification_v0.__table__.create(db.bind)
+ ProcessingNotification_v0.__table__.create(db.bind)
+
+ db.commit()
+
+
+@RegisterMigration(13, MIGRATIONS)
+def pw_hash_nullable(db):
+ """Make pw_hash column nullable"""
+ metadata = MetaData(bind=db.bind)
+ user_table = inspect_table(metadata, "core__users")
+
+ user_table.c.pw_hash.alter(nullable=True)
+
+ # sqlite+sqlalchemy seems to drop this constraint during the
+ # migration, so we add it back here for now a bit manually.
+ if db.bind.url.drivername == 'sqlite':
+ constraint = UniqueConstraint('username', table=user_table)
+ constraint.create()
+
+ db.commit()
+
+
+# oauth1 migrations
+class Client_v0(declarative_base()):
+ """
+ Model representing a client - Used for API Auth
+ """
+ __tablename__ = "core__clients"
+
+ id = Column(Unicode, nullable=True, primary_key=True)
+ secret = Column(Unicode, nullable=False)
+ expirey = Column(DateTime, nullable=True)
+ application_type = Column(Unicode, nullable=False)
+ created = Column(DateTime, nullable=False, default=datetime.datetime.now)
+ updated = Column(DateTime, nullable=False, default=datetime.datetime.now)
+
+ # optional stuff
+ redirect_uri = Column(JSONEncoded, nullable=True)
+ logo_url = Column(Unicode, nullable=True)
+ application_name = Column(Unicode, nullable=True)
+ contacts = Column(JSONEncoded, nullable=True)
+
+ def __repr__(self):
+ if self.application_name:
+ return "<Client {0} - {1}>".format(self.application_name, self.id)
+ else:
+ return "<Client {0}>".format(self.id)
+
+class RequestToken_v0(declarative_base()):
+ """
+ Model for representing the request tokens
+ """
+ __tablename__ = "core__request_tokens"
+
+ token = Column(Unicode, primary_key=True)
+ secret = Column(Unicode, nullable=False)
+ client = Column(Unicode, ForeignKey(Client_v0.id))
+ user = Column(Integer, ForeignKey(User.id), nullable=True)
+ used = Column(Boolean, default=False)
+ authenticated = Column(Boolean, default=False)
+ verifier = Column(Unicode, nullable=True)
+ callback = Column(Unicode, nullable=False, default=u"oob")
+ created = Column(DateTime, nullable=False, default=datetime.datetime.now)
+ updated = Column(DateTime, nullable=False, default=datetime.datetime.now)
+
+class AccessToken_v0(declarative_base()):
+ """
+ Model for representing the access tokens
+ """
+ __tablename__ = "core__access_tokens"
+
+ token = Column(Unicode, nullable=False, primary_key=True)
+ secret = Column(Unicode, nullable=False)
+ user = Column(Integer, ForeignKey(User.id))
+ request_token = Column(Unicode, ForeignKey(RequestToken_v0.token))
+ created = Column(DateTime, nullable=False, default=datetime.datetime.now)
+ updated = Column(DateTime, nullable=False, default=datetime.datetime.now)
+
+
+class NonceTimestamp_v0(declarative_base()):
+ """
+ A place the timestamp and nonce can be stored - this is for OAuth1
+ """
+ __tablename__ = "core__nonce_timestamps"
+
+ nonce = Column(Unicode, nullable=False, primary_key=True)
+ timestamp = Column(DateTime, nullable=False, primary_key=True)
+
+
+@RegisterMigration(14, MIGRATIONS)
+def create_oauth1_tables(db):
+ """ Creates the OAuth1 tables """
+
+ Client_v0.__table__.create(db.bind)
+ RequestToken_v0.__table__.create(db.bind)
+ AccessToken_v0.__table__.create(db.bind)
+ NonceTimestamp_v0.__table__.create(db.bind)
+
+ db.commit()
+
+
+@RegisterMigration(15, MIGRATIONS)
+def wants_notifications(db):
+ """Add a wants_notifications field to User model"""
+ metadata = MetaData(bind=db.bind)
+ user_table = inspect_table(metadata, "core__users")
+
+ col = Column('wants_notifications', Boolean, default=True)
+ col.create(user_table)
+
+ db.commit()
+
+
+@RegisterMigration(16, MIGRATIONS)
+def upload_limits(db):
+ """Add user upload limit columns"""
+ metadata = MetaData(bind=db.bind)
+
+ user_table = inspect_table(metadata, 'core__users')
+ media_entry_table = inspect_table(metadata, 'core__media_entries')
+
+ col = Column('uploaded', Integer, default=0)
+ col.create(user_table)
+
+ col = Column('upload_limit', Integer)
+ col.create(user_table)
+
+ col = Column('file_size', Integer, default=0)
+ col.create(media_entry_table)
+
+ db.commit()
+
+
+@RegisterMigration(17, MIGRATIONS)
+def add_file_metadata(db):
+ """Add file_metadata to MediaFile"""
+ metadata = MetaData(bind=db.bind)
+ media_file_table = inspect_table(metadata, "core__mediafiles")
+
+ col = Column('file_metadata', JSONEncoded)
+ col.create(media_file_table)
+
+ db.commit()