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