mediagoblin/db/models.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 """
  18 TODO: indexes on foreignkeys, where useful.
  19 """
  20
  21 import logging
  22 import datetime
  23
  24 from sqlalchemy import Column, Integer, Unicode, UnicodeText, DateTime, \
  25         Boolean, ForeignKey, UniqueConstraint, PrimaryKeyConstraint, \
  26         SmallInteger
  27 from sqlalchemy.orm import relationship, backref, with_polymorphic
  28 from sqlalchemy.orm.collections import attribute_mapped_collection
  29 from sqlalchemy.sql.expression import desc
  30 from sqlalchemy.ext.associationproxy import association_proxy
  31 from sqlalchemy.util import memoized_property
  32
  33
  34 from mediagoblin.db.extratypes import PathTupleWithSlashes, JSONEncoded
  35 from mediagoblin.db.base import Base, DictReadAttrProxy
  36 from mediagoblin.db.mixin import UserMixin, MediaEntryMixin, \
  37         MediaCommentMixin, CollectionMixin, CollectionItemMixin
  38 from mediagoblin.tools.files import delete_media_files
  39 from mediagoblin.tools.common import import_component
  40
  41 # It's actually kind of annoying how sqlalchemy-migrate does this, if
  42 # I understand it right, but whatever.  Anyway, don't remove this :P
  43 #
  44 # We could do migration calls more manually instead of relying on
  45 # this import-based meddling...
  46 from migrate import changeset
  47
  48 _log = logging.getLogger(__name__)
  49
  50
  51 class User(Base, UserMixin):
  52     """
  53     TODO: We should consider moving some rarely used fields
  54     into some sort of "shadow" table.
  55     """
  56     __tablename__ = "core__users"
  57
  58     id = Column(Integer, primary_key=True)
  59     username = Column(Unicode, nullable=False, unique=True)
  60     # Note: no db uniqueness constraint on email because it's not
  61     # reliable (many email systems case insensitive despite against
  62     # the RFC) and because it would be a mess to implement at this
  63     # point.
  64     email = Column(Unicode, nullable=False)
  65     pw_hash = Column(Unicode, nullable=False)
  66     email_verified = Column(Boolean, default=False)
  67     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
  68     status = Column(Unicode, default=u"needs_email_verification", nullable=False)
  69     # Intented to be nullable=False, but migrations would not work for it
  70     # set to nullable=True implicitly.
  71     wants_comment_notification = Column(Boolean, default=True)
  72     license_preference = Column(Unicode)
  73     verification_key = Column(Unicode)
  74     is_admin = Column(Boolean, default=False, nullable=False)
  75     url = Column(Unicode)
  76     bio = Column(UnicodeText)  # ??
  77     fp_verification_key = Column(Unicode)
  78     fp_token_expire = Column(DateTime)
  79
  80     ## TODO
  81     # plugin data would be in a separate model
  82
  83     def __repr__(self):
  84         return '<{0} #{1} {2} {3} "{4}">'.format(
  85                 self.__class__.__name__,
  86                 self.id,
  87                 'verified' if self.email_verified else 'non-verified',
  88                 'admin' if self.is_admin else 'user',
  89                 self.username)
  90
  91     def delete(self, **kwargs):
  92         """Deletes a User and all related entries/comments/files/..."""
  93         # Collections get deleted by relationships.
  94
  95         media_entries = MediaEntry.query.filter(MediaEntry.uploader == self.id)
  96         for media in media_entries:
  97             # TODO: Make sure that "MediaEntry.delete()" also deletes
  98             # all related files/Comments
  99             media.delete(del_orphan_tags=False, commit=False)
 100
 101         # Delete now unused tags
 102         # TODO: import here due to cyclic imports!!! This cries for refactoring
 103         from mediagoblin.db.util import clean_orphan_tags
 104         clean_orphan_tags(commit=False)
 105
 106         # Delete user, pass through commit=False/True in kwargs
 107         super(User, self).delete(**kwargs)
 108         _log.info('Deleted user "{0}" account'.format(self.username))
 109
 110
 111 class MediaEntry(Base, MediaEntryMixin):
 112     """
 113     TODO: Consider fetching the media_files using join
 114     """
 115     __tablename__ = "core__media_entries"
 116
 117     id = Column(Integer, primary_key=True)
 118     uploader = Column(Integer, ForeignKey(User.id), nullable=False, index=True)
 119     title = Column(Unicode, nullable=False)
 120     slug = Column(Unicode)
 121     created = Column(DateTime, nullable=False, default=datetime.datetime.now,
 122         index=True)
 123     description = Column(UnicodeText) # ??
 124     media_type = Column(Unicode, nullable=False)
 125     state = Column(Unicode, default=u'unprocessed', nullable=False)
 126         # or use sqlalchemy.types.Enum?
 127     license = Column(Unicode)
 128     collected = Column(Integer, default=0)
 129
 130     fail_error = Column(Unicode)
 131     fail_metadata = Column(JSONEncoded)
 132
 133     transcoding_progress = Column(SmallInteger)
 134
 135     queued_media_file = Column(PathTupleWithSlashes)
 136
 137     queued_task_id = Column(Unicode)
 138
 139     __table_args__ = (
 140         UniqueConstraint('uploader', 'slug'),
 141         {})
 142
 143     get_uploader = relationship(User)
 144
 145     media_files_helper = relationship("MediaFile",
 146         collection_class=attribute_mapped_collection("name"),
 147         cascade="all, delete-orphan"
 148         )
 149     media_files = association_proxy('media_files_helper', 'file_path',
 150         creator=lambda k, v: MediaFile(name=k, file_path=v)
 151         )
 152
 153     attachment_files_helper = relationship("MediaAttachmentFile",
 154         cascade="all, delete-orphan",
 155         order_by="MediaAttachmentFile.created"
 156         )
 157     attachment_files = association_proxy("attachment_files_helper", "dict_view",
 158         creator=lambda v: MediaAttachmentFile(
 159             name=v["name"], filepath=v["filepath"])
 160         )
 161
 162     tags_helper = relationship("MediaTag",
 163         cascade="all, delete-orphan" # should be automatically deleted
 164         )
 165     tags = association_proxy("tags_helper", "dict_view",
 166         creator=lambda v: MediaTag(name=v["name"], slug=v["slug"])
 167         )
 168
 169     collections_helper = relationship("CollectionItem",
 170         cascade="all, delete-orphan"
 171         )
 172     collections = association_proxy("collections_helper", "in_collection")
 173
 174     ## TODO
 175     # fail_error
 176
 177     def get_comments(self, ascending=False):
 178         order_col = MediaComment.created
 179         if not ascending:
 180             order_col = desc(order_col)
 181         return self.all_comments.order_by(order_col)
 182
 183     def url_to_prev(self, urlgen):
 184         """get the next 'newer' entry by this user"""
 185         media = MediaEntry.query.filter(
 186             (MediaEntry.uploader == self.uploader)
 187             & (MediaEntry.state == u'processed')
 188             & (MediaEntry.id > self.id)).order_by(MediaEntry.id).first()
 189
 190         if media is not None:
 191             return media.url_for_self(urlgen)
 192
 193     def url_to_next(self, urlgen):
 194         """get the next 'older' entry by this user"""
 195         media = MediaEntry.query.filter(
 196             (MediaEntry.uploader == self.uploader)
 197             & (MediaEntry.state == u'processed')
 198             & (MediaEntry.id < self.id)).order_by(desc(MediaEntry.id)).first()
 199
 200         if media is not None:
 201             return media.url_for_self(urlgen)
 202
 203     @property
 204     def media_data(self):
 205         return getattr(self, self.media_data_ref)
 206
 207     def media_data_init(self, **kwargs):
 208         """
 209         Initialize or update the contents of a media entry's media_data row
 210         """
 211         media_data = self.media_data
 212
 213         if media_data is None:
 214             # Get the correct table:
 215             table = import_component(self.media_type + '.models:DATA_MODEL')
 216             # No media data, so actually add a new one
 217             media_data = table(**kwargs)
 218             # Get the relationship set up.
 219             media_data.get_media_entry = self
 220         else:
 221             # Update old media data
 222             for field, value in kwargs.iteritems():
 223                 setattr(media_data, field, value)
 224
 225     @memoized_property
 226     def media_data_ref(self):
 227         return import_component(self.media_type + '.models:BACKREF_NAME')
 228
 229     def __repr__(self):
 230         safe_title = self.title.encode('ascii', 'replace')
 231
 232         return '<{classname} {id}: {title}>'.format(
 233                 classname=self.__class__.__name__,
 234                 id=self.id,
 235                 title=safe_title)
 236
 237     def delete(self, del_orphan_tags=True, **kwargs):
 238         """Delete MediaEntry and all related files/attachments/comments
 239
 240         This will *not* automatically delete unused collections, which
 241         can remain empty...
 242
 243         :param del_orphan_tags: True/false if we delete unused Tags too
 244         :param commit: True/False if this should end the db transaction"""
 245         # User's CollectionItems are automatically deleted via "cascade".
 246         # Comments on this Media are deleted by cascade, hopefully.
 247
 248         # Delete all related files/attachments
 249         try:
 250             delete_media_files(self)
 251         except OSError, error:
 252             # Returns list of files we failed to delete
 253             _log.error('No such files from the user "{1}" to delete: '
 254                        '{0}'.format(str(error), self.get_uploader))
 255         _log.info('Deleted Media entry id "{0}"'.format(self.id))
 256         # Related MediaTag's are automatically cleaned, but we might
 257         # want to clean out unused Tag's too.
 258         if del_orphan_tags:
 259             # TODO: Import here due to cyclic imports!!!
 260             #       This cries for refactoring
 261             from mediagoblin.db.util import clean_orphan_tags
 262             clean_orphan_tags(commit=False)
 263         # pass through commit=False/True in kwargs
 264         super(MediaEntry, self).delete(**kwargs)
 265
 266
 267 class FileKeynames(Base):
 268     """
 269     keywords for various places.
 270     currently the MediaFile keys
 271     """
 272     __tablename__ = "core__file_keynames"
 273     id = Column(Integer, primary_key=True)
 274     name = Column(Unicode, unique=True)
 275
 276     def __repr__(self):
 277         return "<FileKeyname %r: %r>" % (self.id, self.name)
 278
 279     @classmethod
 280     def find_or_new(cls, name):
 281         t = cls.query.filter_by(name=name).first()
 282         if t is not None:
 283             return t
 284         return cls(name=name)
 285
 286
 287 class MediaFile(Base):
 288     """
 289     TODO: Highly consider moving "name" into a new table.
 290     TODO: Consider preloading said table in software
 291     """
 292     __tablename__ = "core__mediafiles"
 293
 294     media_entry = Column(
 295         Integer, ForeignKey(MediaEntry.id),
 296         nullable=False)
 297     name_id = Column(SmallInteger, ForeignKey(FileKeynames.id), nullable=False)
 298     file_path = Column(PathTupleWithSlashes)
 299
 300     __table_args__ = (
 301         PrimaryKeyConstraint('media_entry', 'name_id'),
 302         {})
 303
 304     def __repr__(self):
 305         return "<MediaFile %s: %r>" % (self.name, self.file_path)
 306
 307     name_helper = relationship(FileKeynames, lazy="joined", innerjoin=True)
 308     name = association_proxy('name_helper', 'name',
 309         creator=FileKeynames.find_or_new
 310         )
 311
 312
 313 class MediaAttachmentFile(Base):
 314     __tablename__ = "core__attachment_files"
 315
 316     id = Column(Integer, primary_key=True)
 317     media_entry = Column(
 318         Integer, ForeignKey(MediaEntry.id),
 319         nullable=False)
 320     name = Column(Unicode, nullable=False)
 321     filepath = Column(PathTupleWithSlashes)
 322     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 323
 324     @property
 325     def dict_view(self):
 326         """A dict like view on this object"""
 327         return DictReadAttrProxy(self)
 328
 329
 330 class Tag(Base):
 331     __tablename__ = "core__tags"
 332
 333     id = Column(Integer, primary_key=True)
 334     slug = Column(Unicode, nullable=False, unique=True)
 335
 336     def __repr__(self):
 337         return "<Tag %r: %r>" % (self.id, self.slug)
 338
 339     @classmethod
 340     def find_or_new(cls, slug):
 341         t = cls.query.filter_by(slug=slug).first()
 342         if t is not None:
 343             return t
 344         return cls(slug=slug)
 345
 346
 347 class MediaTag(Base):
 348     __tablename__ = "core__media_tags"
 349
 350     id = Column(Integer, primary_key=True)
 351     media_entry = Column(
 352         Integer, ForeignKey(MediaEntry.id),
 353         nullable=False, index=True)
 354     tag = Column(Integer, ForeignKey(Tag.id), nullable=False, index=True)
 355     name = Column(Unicode)
 356     # created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 357
 358     __table_args__ = (
 359         UniqueConstraint('tag', 'media_entry'),
 360         {})
 361
 362     tag_helper = relationship(Tag)
 363     slug = association_proxy('tag_helper', 'slug',
 364         creator=Tag.find_or_new
 365         )
 366
 367     def __init__(self, name=None, slug=None):
 368         Base.__init__(self)
 369         if name is not None:
 370             self.name = name
 371         if slug is not None:
 372             self.tag_helper = Tag.find_or_new(slug)
 373
 374     @property
 375     def dict_view(self):
 376         """A dict like view on this object"""
 377         return DictReadAttrProxy(self)
 378
 379
 380 class MediaComment(Base, MediaCommentMixin):
 381     __tablename__ = "core__media_comments"
 382
 383     id = Column(Integer, primary_key=True)
 384     media_entry = Column(
 385         Integer, ForeignKey(MediaEntry.id), nullable=False, index=True)
 386     author = Column(Integer, ForeignKey(User.id), nullable=False)
 387     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 388     content = Column(UnicodeText, nullable=False)
 389
 390     # Cascade: Comments are owned by their creator. So do the full thing.
 391     # lazy=dynamic: People might post a *lot* of comments,
 392     #     so make the "posted_comments" a query-like thing.
 393     get_author = relationship(User,
 394                               backref=backref("posted_comments",
 395                                               lazy="dynamic",
 396                                               cascade="all, delete-orphan"))
 397     get_entry = relationship(MediaEntry,
 398                              backref=backref("comments",
 399                                              lazy="dynamic",
 400                                              cascade="all, delete-orphan"))
 401
 402     # Cascade: Comments are somewhat owned by their MediaEntry.
 403     #     So do the full thing.
 404     # lazy=dynamic: MediaEntries might have many comments,
 405     #     so make the "all_comments" a query-like thing.
 406     get_media_entry = relationship(MediaEntry,
 407                                    backref=backref("all_comments",
 408                                                    lazy="dynamic",
 409                                                    cascade="all, delete-orphan"))
 410
 411
 412 class Collection(Base, CollectionMixin):
 413     """An 'album' or 'set' of media by a user.
 414
 415     On deletion, contained CollectionItems get automatically reaped via
 416     SQL cascade"""
 417     __tablename__ = "core__collections"
 418
 419     id = Column(Integer, primary_key=True)
 420     title = Column(Unicode, nullable=False)
 421     slug = Column(Unicode)
 422     created = Column(DateTime, nullable=False, default=datetime.datetime.now,
 423                      index=True)
 424     description = Column(UnicodeText)
 425     creator = Column(Integer, ForeignKey(User.id), nullable=False)
 426     # TODO: No of items in Collection. Badly named, can we migrate to num_items?
 427     items = Column(Integer, default=0)
 428
 429     # Cascade: Collections are owned by their creator. So do the full thing.
 430     get_creator = relationship(User,
 431                                backref=backref("collections",
 432                                                cascade="all, delete-orphan"))
 433
 434     __table_args__ = (
 435         UniqueConstraint('creator', 'slug'),
 436         {})
 437
 438     def get_collection_items(self, ascending=False):
 439         #TODO, is this still needed with self.collection_items being available?
 440         order_col = CollectionItem.position
 441         if not ascending:
 442             order_col = desc(order_col)
 443         return CollectionItem.query.filter_by(
 444             collection=self.id).order_by(order_col)
 445
 446
 447 class CollectionItem(Base, CollectionItemMixin):
 448     __tablename__ = "core__collection_items"
 449
 450     id = Column(Integer, primary_key=True)
 451     media_entry = Column(
 452         Integer, ForeignKey(MediaEntry.id), nullable=False, index=True)
 453     collection = Column(Integer, ForeignKey(Collection.id), nullable=False)
 454     note = Column(UnicodeText, nullable=True)
 455     added = Column(DateTime, nullable=False, default=datetime.datetime.now)
 456     position = Column(Integer)
 457
 458     # Cascade: CollectionItems are owned by their Collection. So do the full thing.
 459     in_collection = relationship(Collection,
 460                                  backref=backref(
 461                                      "collection_items",
 462                                      cascade="all, delete-orphan"))
 463
 464     get_media_entry = relationship(MediaEntry)
 465
 466     __table_args__ = (
 467         UniqueConstraint('collection', 'media_entry'),
 468         {})
 469
 470     @property
 471     def dict_view(self):
 472         """A dict like view on this object"""
 473         return DictReadAttrProxy(self)
 474
 475
 476 class ProcessingMetaData(Base):
 477     __tablename__ = 'core__processing_metadata'
 478
 479     id = Column(Integer, primary_key=True)
 480     media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=False,
 481             index=True)
 482     media_entry = relationship(MediaEntry,
 483             backref=backref('processing_metadata',
 484                 cascade='all, delete-orphan'))
 485     callback_url = Column(Unicode)
 486
 487     @property
 488     def dict_view(self):
 489         """A dict like view on this object"""
 490         return DictReadAttrProxy(self)
 491
 492
 493 class CommentSubscription(Base):
 494     __tablename__ = 'core__comment_subscriptions'
 495     id = Column(Integer, primary_key=True)
 496
 497     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 498
 499     media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=False)
 500     media_entry = relationship(MediaEntry,
 501                         backref=backref('comment_subscriptions',
 502                                         cascade='all, delete-orphan'))
 503
 504     user_id = Column(Integer, ForeignKey(User.id), nullable=False)
 505     user = relationship(User,
 506                         backref=backref('comment_subscriptions',
 507                                         cascade='all, delete-orphan'))
 508
 509     notify = Column(Boolean, nullable=False, default=True)
 510     send_email = Column(Boolean, nullable=False, default=True)
 511
 512     def __repr__(self):
 513         return ('<{classname} #{id}: {user} {media} notify: '
 514                 '{notify} email: {email}>').format(
 515             id=self.id,
 516             classname=self.__class__.__name__,
 517             user=self.user,
 518             media=self.media_entry,
 519             notify=self.notify,
 520             email=self.send_email)
 521
 522
 523 class Notification(Base):
 524     __tablename__ = 'core__notifications'
 525     id = Column(Integer, primary_key=True)
 526     type = Column(Unicode)
 527
 528     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 529
 530     user_id = Column(Integer, ForeignKey('core__users.id'), nullable=False,
 531                      index=True)
 532     seen = Column(Boolean, default=lambda: False, index=True)
 533     user = relationship(
 534         User,
 535         backref=backref('notifications', cascade='all, delete-orphan'))
 536
 537     __mapper_args__ = {
 538         'polymorphic_identity': 'notification',
 539         'polymorphic_on': type
 540     }
 541
 542     def __repr__(self):
 543         return '<{klass} #{id}: {user}: {subject} ({seen})>'.format(
 544             id=self.id,
 545             klass=self.__class__.__name__,
 546             user=self.user,
 547             subject=getattr(self, 'subject', None),
 548             seen='unseen' if not self.seen else 'seen')
 549
 550
 551 class CommentNotification(Notification):
 552     __tablename__ = 'core__comment_notifications'
 553     id = Column(Integer, ForeignKey(Notification.id), primary_key=True)
 554
 555     subject_id = Column(Integer, ForeignKey(MediaComment.id))
 556     subject = relationship(
 557         MediaComment,
 558         backref=backref('comment_notifications', cascade='all, delete-orphan'))
 559
 560     __mapper_args__ = {
 561         'polymorphic_identity': 'comment_notification'
 562     }
 563
 564
 565 class ProcessingNotification(Notification):
 566     __tablename__ = 'core__processing_notifications'
 567
 568     id = Column(Integer, ForeignKey(Notification.id), primary_key=True)
 569
 570     subject_id = Column(Integer, ForeignKey(MediaEntry.id))
 571     subject = relationship(
 572         MediaEntry,
 573         backref=backref('processing_notifications',
 574                         cascade='all, delete-orphan'))
 575
 576     __mapper_args__ = {
 577         'polymorphic_identity': 'processing_notification'
 578     }
 579
 580
 581 with_polymorphic(
 582     Notification,
 583     [ProcessingNotification, CommentNotification])
 584
 585 MODELS = [
 586     User, MediaEntry, Tag, MediaTag, MediaComment, Collection, CollectionItem,
 587     MediaFile, FileKeynames, MediaAttachmentFile, ProcessingMetaData,
 588     Notification, CommentNotification, ProcessingNotification,
 589     CommentSubscription]
 590
 591
 592 ######################################################
 593 # Special, migrations-tracking table
 594 #
 595 # Not listed in MODELS because this is special and not
 596 # really migrated, but used for migrations (for now)
 597 ######################################################
 598
 599 class MigrationData(Base):
 600     __tablename__ = "core__migrations"
 601
 602     name = Column(Unicode, primary_key=True)
 603     version = Column(Integer, nullable=False, default=0)
 604
 605 ######################################################
 606
 607
 608 def show_table_init(engine_uri):
 609     if engine_uri is None:
 610         engine_uri = 'sqlite:///:memory:'
 611     from sqlalchemy import create_engine
 612     engine = create_engine(engine_uri, echo=True)
 613
 614     Base.metadata.create_all(engine)
 615
 616
 617 if __name__ == '__main__':
 618     from sys import argv
 619     print repr(argv)
 620     if len(argv) == 2:
 621         uri = argv[1]
 622     else:
 623         uri = None
 624     show_table_init(uri)