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, Date
  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 from mediagoblin.db.extratypes import (PathTupleWithSlashes, JSONEncoded,
  34                                        MutationDict)
  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
  52 class User(Base, UserMixin):
  53     """
  54     TODO: We should consider moving some rarely used fields
  55     into some sort of "shadow" table.
  56     """
  57     __tablename__ = "core__users"
  58
  59     id = Column(Integer, primary_key=True)
  60     username = Column(Unicode, nullable=False, unique=True)
  61     # Note: no db uniqueness constraint on email because it's not
  62     # reliable (many email systems case insensitive despite against
  63     # the RFC) and because it would be a mess to implement at this
  64     # point.
  65     email = Column(Unicode, nullable=False)
  66     pw_hash = Column(Unicode)
  67     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
  68     # Intented to be nullable=False, but migrations would not work for it
  69     # set to nullable=True implicitly.
  70     wants_comment_notification = Column(Boolean, default=True)
  71     wants_notifications = Column(Boolean, default=True)
  72     license_preference = Column(Unicode)
  73     url = Column(Unicode)
  74     bio = Column(UnicodeText)  # ??
  75     uploaded = Column(Integer, default=0)
  76     upload_limit = Column(Integer)
  77
  78     ## TODO
  79     # plugin data would be in a separate model
  80
  81     def __repr__(self):
  82         return '<{0} #{1} {2} {3} "{4}">'.format(
  83                 self.__class__.__name__,
  84                 self.id,
  85                 'verified' if self.has_privilege(u'active') else 'non-verified',
  86                 'admin' if self.has_privilege(u'admin') else 'user',
  87                 self.username)
  88
  89     def delete(self, **kwargs):
  90         """Deletes a User and all related entries/comments/files/..."""
  91         # Collections get deleted by relationships.
  92
  93         media_entries = MediaEntry.query.filter(MediaEntry.uploader == self.id)
  94         for media in media_entries:
  95             # TODO: Make sure that "MediaEntry.delete()" also deletes
  96             # all related files/Comments
  97             media.delete(del_orphan_tags=False, commit=False)
  98
  99         # Delete now unused tags
 100         # TODO: import here due to cyclic imports!!! This cries for refactoring
 101         from mediagoblin.db.util import clean_orphan_tags
 102         clean_orphan_tags(commit=False)
 103
 104         # Delete user, pass through commit=False/True in kwargs
 105         super(User, self).delete(**kwargs)
 106         _log.info('Deleted user "{0}" account'.format(self.username))
 107
 108     def has_privilege(self,*priv_names):
 109         """
 110         This method checks to make sure a user has all the correct privileges
 111         to access a piece of content.
 112
 113         :param  priv_names      A variable number of unicode objects which rep-
 114                                 -resent the different privileges which may give
 115                                 the user access to this content. If you pass
 116                                 multiple arguments, the user will be granted
 117                                 access if they have ANY of the privileges
 118                                 passed.
 119         """
 120         if len(priv_names) == 1:
 121             priv = Privilege.query.filter(
 122                 Privilege.privilege_name==priv_names[0]).one()
 123             return (priv in self.all_privileges)
 124         elif len(priv_names) > 1:
 125             return self.has_privilege(priv_names[0]) or \
 126                 self.has_privilege(*priv_names[1:])
 127         return False
 128
 129     def is_banned(self):
 130         """
 131         Checks if this user is banned.
 132
 133             :returns                True if self is banned
 134             :returns                False if self is not
 135         """
 136         return UserBan.query.get(self.id) is not None
 137
 138
 139 class Client(Base):
 140     """
 141         Model representing a client - Used for API Auth
 142     """
 143     __tablename__ = "core__clients"
 144
 145     id = Column(Unicode, nullable=True, primary_key=True)
 146     secret = Column(Unicode, nullable=False)
 147     expirey = Column(DateTime, nullable=True)
 148     application_type = Column(Unicode, nullable=False)
 149     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 150     updated = Column(DateTime, nullable=False, default=datetime.datetime.now)
 151
 152     # optional stuff
 153     redirect_uri = Column(JSONEncoded, nullable=True)
 154     logo_url = Column(Unicode, nullable=True)
 155     application_name = Column(Unicode, nullable=True)
 156     contacts = Column(JSONEncoded, nullable=True)
 157
 158     def __repr__(self):
 159         if self.application_name:
 160             return "<Client {0} - {1}>".format(self.application_name, self.id)
 161         else:
 162             return "<Client {0}>".format(self.id)
 163
 164 class RequestToken(Base):
 165     """
 166         Model for representing the request tokens
 167     """
 168     __tablename__ = "core__request_tokens"
 169
 170     token = Column(Unicode, primary_key=True)
 171     secret = Column(Unicode, nullable=False)
 172     client = Column(Unicode, ForeignKey(Client.id))
 173     user = Column(Integer, ForeignKey(User.id), nullable=True)
 174     used = Column(Boolean, default=False)
 175     authenticated = Column(Boolean, default=False)
 176     verifier = Column(Unicode, nullable=True)
 177     callback = Column(Unicode, nullable=False, default=u"oob")
 178     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 179     updated = Column(DateTime, nullable=False, default=datetime.datetime.now)
 180
 181 class AccessToken(Base):
 182     """
 183         Model for representing the access tokens
 184     """
 185     __tablename__ = "core__access_tokens"
 186
 187     token = Column(Unicode, nullable=False, primary_key=True)
 188     secret = Column(Unicode, nullable=False)
 189     user = Column(Integer, ForeignKey(User.id))
 190     request_token = Column(Unicode, ForeignKey(RequestToken.token))
 191     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 192     updated = Column(DateTime, nullable=False, default=datetime.datetime.now)
 193
 194
 195 class NonceTimestamp(Base):
 196     """
 197         A place the timestamp and nonce can be stored - this is for OAuth1
 198     """
 199     __tablename__ = "core__nonce_timestamps"
 200
 201     nonce = Column(Unicode, nullable=False, primary_key=True)
 202     timestamp = Column(DateTime, nullable=False, primary_key=True)
 203
 204
 205 class MediaEntry(Base, MediaEntryMixin):
 206     """
 207     TODO: Consider fetching the media_files using join
 208     """
 209     __tablename__ = "core__media_entries"
 210
 211     id = Column(Integer, primary_key=True)
 212     uploader = Column(Integer, ForeignKey(User.id), nullable=False, index=True)
 213     title = Column(Unicode, nullable=False)
 214     slug = Column(Unicode)
 215     created = Column(DateTime, nullable=False, default=datetime.datetime.now,
 216         index=True)
 217     description = Column(UnicodeText) # ??
 218     media_type = Column(Unicode, nullable=False)
 219     state = Column(Unicode, default=u'unprocessed', nullable=False)
 220         # or use sqlalchemy.types.Enum?
 221     license = Column(Unicode)
 222     file_size = Column(Integer, default=0)
 223
 224     fail_error = Column(Unicode)
 225     fail_metadata = Column(JSONEncoded)
 226
 227     transcoding_progress = Column(SmallInteger)
 228
 229     queued_media_file = Column(PathTupleWithSlashes)
 230
 231     queued_task_id = Column(Unicode)
 232
 233     __table_args__ = (
 234         UniqueConstraint('uploader', 'slug'),
 235         {})
 236
 237     get_uploader = relationship(User)
 238
 239     media_files_helper = relationship("MediaFile",
 240         collection_class=attribute_mapped_collection("name"),
 241         cascade="all, delete-orphan"
 242         )
 243     media_files = association_proxy('media_files_helper', 'file_path',
 244         creator=lambda k, v: MediaFile(name=k, file_path=v)
 245         )
 246
 247     attachment_files_helper = relationship("MediaAttachmentFile",
 248         cascade="all, delete-orphan",
 249         order_by="MediaAttachmentFile.created"
 250         )
 251     attachment_files = association_proxy("attachment_files_helper", "dict_view",
 252         creator=lambda v: MediaAttachmentFile(
 253             name=v["name"], filepath=v["filepath"])
 254         )
 255
 256     tags_helper = relationship("MediaTag",
 257         cascade="all, delete-orphan" # should be automatically deleted
 258         )
 259     tags = association_proxy("tags_helper", "dict_view",
 260         creator=lambda v: MediaTag(name=v["name"], slug=v["slug"])
 261         )
 262
 263     collections_helper = relationship("CollectionItem",
 264         cascade="all, delete-orphan"
 265         )
 266     collections = association_proxy("collections_helper", "in_collection")
 267     media_metadata = Column(MutationDict.as_mutable(JSONEncoded),
 268         default=MutationDict())
 269
 270     ## TODO
 271     # fail_error
 272
 273     def get_comments(self, ascending=False):
 274         order_col = MediaComment.created
 275         if not ascending:
 276             order_col = desc(order_col)
 277         return self.all_comments.order_by(order_col)
 278
 279     def url_to_prev(self, urlgen):
 280         """get the next 'newer' entry by this user"""
 281         media = MediaEntry.query.filter(
 282             (MediaEntry.uploader == self.uploader)
 283             & (MediaEntry.state == u'processed')
 284             & (MediaEntry.id > self.id)).order_by(MediaEntry.id).first()
 285
 286         if media is not None:
 287             return media.url_for_self(urlgen)
 288
 289     def url_to_next(self, urlgen):
 290         """get the next 'older' entry by this user"""
 291         media = MediaEntry.query.filter(
 292             (MediaEntry.uploader == self.uploader)
 293             & (MediaEntry.state == u'processed')
 294             & (MediaEntry.id < self.id)).order_by(desc(MediaEntry.id)).first()
 295
 296         if media is not None:
 297             return media.url_for_self(urlgen)
 298
 299     def get_file_metadata(self, file_key, metadata_key=None):
 300         """
 301         Return the file_metadata dict of a MediaFile. If metadata_key is given,
 302         return the value of the key.
 303         """
 304         media_file = MediaFile.query.filter_by(media_entry=self.id,
 305                                                name=unicode(file_key)).first()
 306
 307         if media_file:
 308             if metadata_key:
 309                 return media_file.file_metadata.get(metadata_key, None)
 310
 311             return media_file.file_metadata
 312
 313     def set_file_metadata(self, file_key, **kwargs):
 314         """
 315         Update the file_metadata of a MediaFile.
 316         """
 317         media_file = MediaFile.query.filter_by(media_entry=self.id,
 318                                                name=unicode(file_key)).first()
 319
 320         file_metadata = media_file.file_metadata or {}
 321
 322         for key, value in kwargs.iteritems():
 323             file_metadata[key] = value
 324
 325         media_file.file_metadata = file_metadata
 326         media_file.save()
 327
 328     @property
 329     def media_data(self):
 330         return getattr(self, self.media_data_ref)
 331
 332     def media_data_init(self, **kwargs):
 333         """
 334         Initialize or update the contents of a media entry's media_data row
 335         """
 336         media_data = self.media_data
 337
 338         if media_data is None:
 339             # Get the correct table:
 340             table = import_component(self.media_type + '.models:DATA_MODEL')
 341             # No media data, so actually add a new one
 342             media_data = table(**kwargs)
 343             # Get the relationship set up.
 344             media_data.get_media_entry = self
 345         else:
 346             # Update old media data
 347             for field, value in kwargs.iteritems():
 348                 setattr(media_data, field, value)
 349
 350     @memoized_property
 351     def media_data_ref(self):
 352         return import_component(self.media_type + '.models:BACKREF_NAME')
 353
 354     def __repr__(self):
 355         safe_title = self.title.encode('ascii', 'replace')
 356
 357         return '<{classname} {id}: {title}>'.format(
 358                 classname=self.__class__.__name__,
 359                 id=self.id,
 360                 title=safe_title)
 361
 362     def delete(self, del_orphan_tags=True, **kwargs):
 363         """Delete MediaEntry and all related files/attachments/comments
 364
 365         This will *not* automatically delete unused collections, which
 366         can remain empty...
 367
 368         :param del_orphan_tags: True/false if we delete unused Tags too
 369         :param commit: True/False if this should end the db transaction"""
 370         # User's CollectionItems are automatically deleted via "cascade".
 371         # Comments on this Media are deleted by cascade, hopefully.
 372
 373         # Delete all related files/attachments
 374         try:
 375             delete_media_files(self)
 376         except OSError, error:
 377             # Returns list of files we failed to delete
 378             _log.error('No such files from the user "{1}" to delete: '
 379                        '{0}'.format(str(error), self.get_uploader))
 380         _log.info('Deleted Media entry id "{0}"'.format(self.id))
 381         # Related MediaTag's are automatically cleaned, but we might
 382         # want to clean out unused Tag's too.
 383         if del_orphan_tags:
 384             # TODO: Import here due to cyclic imports!!!
 385             #       This cries for refactoring
 386             from mediagoblin.db.util import clean_orphan_tags
 387             clean_orphan_tags(commit=False)
 388         # pass through commit=False/True in kwargs
 389         super(MediaEntry, self).delete(**kwargs)
 390
 391
 392 class FileKeynames(Base):
 393     """
 394     keywords for various places.
 395     currently the MediaFile keys
 396     """
 397     __tablename__ = "core__file_keynames"
 398     id = Column(Integer, primary_key=True)
 399     name = Column(Unicode, unique=True)
 400
 401     def __repr__(self):
 402         return "<FileKeyname %r: %r>" % (self.id, self.name)
 403
 404     @classmethod
 405     def find_or_new(cls, name):
 406         t = cls.query.filter_by(name=name).first()
 407         if t is not None:
 408             return t
 409         return cls(name=name)
 410
 411
 412 class MediaFile(Base):
 413     """
 414     TODO: Highly consider moving "name" into a new table.
 415     TODO: Consider preloading said table in software
 416     """
 417     __tablename__ = "core__mediafiles"
 418
 419     media_entry = Column(
 420         Integer, ForeignKey(MediaEntry.id),
 421         nullable=False)
 422     name_id = Column(SmallInteger, ForeignKey(FileKeynames.id), nullable=False)
 423     file_path = Column(PathTupleWithSlashes)
 424     file_metadata = Column(MutationDict.as_mutable(JSONEncoded))
 425
 426     __table_args__ = (
 427         PrimaryKeyConstraint('media_entry', 'name_id'),
 428         {})
 429
 430     def __repr__(self):
 431         return "<MediaFile %s: %r>" % (self.name, self.file_path)
 432
 433     name_helper = relationship(FileKeynames, lazy="joined", innerjoin=True)
 434     name = association_proxy('name_helper', 'name',
 435         creator=FileKeynames.find_or_new
 436         )
 437
 438
 439 class MediaAttachmentFile(Base):
 440     __tablename__ = "core__attachment_files"
 441
 442     id = Column(Integer, primary_key=True)
 443     media_entry = Column(
 444         Integer, ForeignKey(MediaEntry.id),
 445         nullable=False)
 446     name = Column(Unicode, nullable=False)
 447     filepath = Column(PathTupleWithSlashes)
 448     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 449
 450     @property
 451     def dict_view(self):
 452         """A dict like view on this object"""
 453         return DictReadAttrProxy(self)
 454
 455
 456 class Tag(Base):
 457     __tablename__ = "core__tags"
 458
 459     id = Column(Integer, primary_key=True)
 460     slug = Column(Unicode, nullable=False, unique=True)
 461
 462     def __repr__(self):
 463         return "<Tag %r: %r>" % (self.id, self.slug)
 464
 465     @classmethod
 466     def find_or_new(cls, slug):
 467         t = cls.query.filter_by(slug=slug).first()
 468         if t is not None:
 469             return t
 470         return cls(slug=slug)
 471
 472
 473 class MediaTag(Base):
 474     __tablename__ = "core__media_tags"
 475
 476     id = Column(Integer, primary_key=True)
 477     media_entry = Column(
 478         Integer, ForeignKey(MediaEntry.id),
 479         nullable=False, index=True)
 480     tag = Column(Integer, ForeignKey(Tag.id), nullable=False, index=True)
 481     name = Column(Unicode)
 482     # created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 483
 484     __table_args__ = (
 485         UniqueConstraint('tag', 'media_entry'),
 486         {})
 487
 488     tag_helper = relationship(Tag)
 489     slug = association_proxy('tag_helper', 'slug',
 490         creator=Tag.find_or_new
 491         )
 492
 493     def __init__(self, name=None, slug=None):
 494         Base.__init__(self)
 495         if name is not None:
 496             self.name = name
 497         if slug is not None:
 498             self.tag_helper = Tag.find_or_new(slug)
 499
 500     @property
 501     def dict_view(self):
 502         """A dict like view on this object"""
 503         return DictReadAttrProxy(self)
 504
 505
 506 class MediaComment(Base, MediaCommentMixin):
 507     __tablename__ = "core__media_comments"
 508
 509     id = Column(Integer, primary_key=True)
 510     media_entry = Column(
 511         Integer, ForeignKey(MediaEntry.id), nullable=False, index=True)
 512     author = Column(Integer, ForeignKey(User.id), nullable=False)
 513     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 514     content = Column(UnicodeText, nullable=False)
 515
 516     # Cascade: Comments are owned by their creator. So do the full thing.
 517     # lazy=dynamic: People might post a *lot* of comments,
 518     #     so make the "posted_comments" a query-like thing.
 519     get_author = relationship(User,
 520                               backref=backref("posted_comments",
 521                                               lazy="dynamic",
 522                                               cascade="all, delete-orphan"))
 523     get_entry = relationship(MediaEntry,
 524                              backref=backref("comments",
 525                                              lazy="dynamic",
 526                                              cascade="all, delete-orphan"))
 527
 528     # Cascade: Comments are somewhat owned by their MediaEntry.
 529     #     So do the full thing.
 530     # lazy=dynamic: MediaEntries might have many comments,
 531     #     so make the "all_comments" a query-like thing.
 532     get_media_entry = relationship(MediaEntry,
 533                                    backref=backref("all_comments",
 534                                                    lazy="dynamic",
 535                                                    cascade="all, delete-orphan"))
 536
 537
 538 class Collection(Base, CollectionMixin):
 539     """An 'album' or 'set' of media by a user.
 540
 541     On deletion, contained CollectionItems get automatically reaped via
 542     SQL cascade"""
 543     __tablename__ = "core__collections"
 544
 545     id = Column(Integer, primary_key=True)
 546     title = Column(Unicode, nullable=False)
 547     slug = Column(Unicode)
 548     created = Column(DateTime, nullable=False, default=datetime.datetime.now,
 549                      index=True)
 550     description = Column(UnicodeText)
 551     creator = Column(Integer, ForeignKey(User.id), nullable=False)
 552     # TODO: No of items in Collection. Badly named, can we migrate to num_items?
 553     items = Column(Integer, default=0)
 554
 555     # Cascade: Collections are owned by their creator. So do the full thing.
 556     get_creator = relationship(User,
 557                                backref=backref("collections",
 558                                                cascade="all, delete-orphan"))
 559
 560     __table_args__ = (
 561         UniqueConstraint('creator', 'slug'),
 562         {})
 563
 564     def get_collection_items(self, ascending=False):
 565         #TODO, is this still needed with self.collection_items being available?
 566         order_col = CollectionItem.position
 567         if not ascending:
 568             order_col = desc(order_col)
 569         return CollectionItem.query.filter_by(
 570             collection=self.id).order_by(order_col)
 571
 572
 573 class CollectionItem(Base, CollectionItemMixin):
 574     __tablename__ = "core__collection_items"
 575
 576     id = Column(Integer, primary_key=True)
 577     media_entry = Column(
 578         Integer, ForeignKey(MediaEntry.id), nullable=False, index=True)
 579     collection = Column(Integer, ForeignKey(Collection.id), nullable=False)
 580     note = Column(UnicodeText, nullable=True)
 581     added = Column(DateTime, nullable=False, default=datetime.datetime.now)
 582     position = Column(Integer)
 583
 584     # Cascade: CollectionItems are owned by their Collection. So do the full thing.
 585     in_collection = relationship(Collection,
 586                                  backref=backref(
 587                                      "collection_items",
 588                                      cascade="all, delete-orphan"))
 589
 590     get_media_entry = relationship(MediaEntry)
 591
 592     __table_args__ = (
 593         UniqueConstraint('collection', 'media_entry'),
 594         {})
 595
 596     @property
 597     def dict_view(self):
 598         """A dict like view on this object"""
 599         return DictReadAttrProxy(self)
 600
 601
 602 class ProcessingMetaData(Base):
 603     __tablename__ = 'core__processing_metadata'
 604
 605     id = Column(Integer, primary_key=True)
 606     media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=False,
 607             index=True)
 608     media_entry = relationship(MediaEntry,
 609             backref=backref('processing_metadata',
 610                 cascade='all, delete-orphan'))
 611     callback_url = Column(Unicode)
 612
 613     @property
 614     def dict_view(self):
 615         """A dict like view on this object"""
 616         return DictReadAttrProxy(self)
 617
 618
 619 class CommentSubscription(Base):
 620     __tablename__ = 'core__comment_subscriptions'
 621     id = Column(Integer, primary_key=True)
 622
 623     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 624
 625     media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=False)
 626     media_entry = relationship(MediaEntry,
 627                         backref=backref('comment_subscriptions',
 628                                         cascade='all, delete-orphan'))
 629
 630     user_id = Column(Integer, ForeignKey(User.id), nullable=False)
 631     user = relationship(User,
 632                         backref=backref('comment_subscriptions',
 633                                         cascade='all, delete-orphan'))
 634
 635     notify = Column(Boolean, nullable=False, default=True)
 636     send_email = Column(Boolean, nullable=False, default=True)
 637
 638     def __repr__(self):
 639         return ('<{classname} #{id}: {user} {media} notify: '
 640                 '{notify} email: {email}>').format(
 641             id=self.id,
 642             classname=self.__class__.__name__,
 643             user=self.user,
 644             media=self.media_entry,
 645             notify=self.notify,
 646             email=self.send_email)
 647
 648
 649 class Notification(Base):
 650     __tablename__ = 'core__notifications'
 651     id = Column(Integer, primary_key=True)
 652     type = Column(Unicode)
 653
 654     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 655
 656     user_id = Column(Integer, ForeignKey('core__users.id'), nullable=False,
 657                      index=True)
 658     seen = Column(Boolean, default=lambda: False, index=True)
 659     user = relationship(
 660         User,
 661         backref=backref('notifications', cascade='all, delete-orphan'))
 662
 663     __mapper_args__ = {
 664         'polymorphic_identity': 'notification',
 665         'polymorphic_on': type
 666     }
 667
 668     def __repr__(self):
 669         return '<{klass} #{id}: {user}: {subject} ({seen})>'.format(
 670             id=self.id,
 671             klass=self.__class__.__name__,
 672             user=self.user,
 673             subject=getattr(self, 'subject', None),
 674             seen='unseen' if not self.seen else 'seen')
 675
 676
 677 class CommentNotification(Notification):
 678     __tablename__ = 'core__comment_notifications'
 679     id = Column(Integer, ForeignKey(Notification.id), primary_key=True)
 680
 681     subject_id = Column(Integer, ForeignKey(MediaComment.id))
 682     subject = relationship(
 683         MediaComment,
 684         backref=backref('comment_notifications', cascade='all, delete-orphan'))
 685
 686     __mapper_args__ = {
 687         'polymorphic_identity': 'comment_notification'
 688     }
 689
 690
 691 class ProcessingNotification(Notification):
 692     __tablename__ = 'core__processing_notifications'
 693
 694     id = Column(Integer, ForeignKey(Notification.id), primary_key=True)
 695
 696     subject_id = Column(Integer, ForeignKey(MediaEntry.id))
 697     subject = relationship(
 698         MediaEntry,
 699         backref=backref('processing_notifications',
 700                         cascade='all, delete-orphan'))
 701
 702     __mapper_args__ = {
 703         'polymorphic_identity': 'processing_notification'
 704     }
 705
 706 with_polymorphic(
 707     Notification,
 708     [ProcessingNotification, CommentNotification])
 709
 710 class ReportBase(Base):
 711     """
 712     This is the basic report object which the other reports are based off of.
 713
 714         :keyword    reporter_id         Holds the id of the user who created
 715                                             the report, as an Integer column.
 716         :keyword    report_content      Hold the explanation left by the repor-
 717                                             -ter to indicate why they filed the
 718                                             report in the first place, as a
 719                                             Unicode column.
 720         :keyword    reported_user_id    Holds the id of the user who created
 721                                             the content which was reported, as
 722                                             an Integer column.
 723         :keyword    created             Holds a datetime column of when the re-
 724                                             -port was filed.
 725         :keyword    discriminator       This column distinguishes between the
 726                                             different types of reports.
 727         :keyword    resolver_id         Holds the id of the moderator/admin who
 728                                             resolved the report.
 729         :keyword    resolved            Holds the DateTime object which descri-
 730                                             -bes when this report was resolved
 731         :keyword    result              Holds the UnicodeText column of the
 732                                             resolver's reasons for resolving
 733                                             the report this way. Some of this
 734                                             is auto-generated
 735     """
 736     __tablename__ = 'core__reports'
 737     id = Column(Integer, primary_key=True)
 738     reporter_id = Column(Integer, ForeignKey(User.id), nullable=False)
 739     reporter =  relationship(
 740         User,
 741         backref=backref("reports_filed_by",
 742             lazy="dynamic",
 743             cascade="all, delete-orphan"),
 744         primaryjoin="User.id==ReportBase.reporter_id")
 745     report_content = Column(UnicodeText)
 746     reported_user_id = Column(Integer, ForeignKey(User.id), nullable=False)
 747     reported_user = relationship(
 748         User,
 749         backref=backref("reports_filed_on",
 750             lazy="dynamic",
 751             cascade="all, delete-orphan"),
 752         primaryjoin="User.id==ReportBase.reported_user_id")
 753     created = Column(DateTime, nullable=False, default=datetime.datetime.now())
 754     discriminator = Column('type', Unicode(50))
 755     resolver_id = Column(Integer, ForeignKey(User.id))
 756     resolver = relationship(
 757         User,
 758         backref=backref("reports_resolved_by",
 759             lazy="dynamic",
 760             cascade="all, delete-orphan"),
 761         primaryjoin="User.id==ReportBase.resolver_id")
 762
 763     resolved = Column(DateTime)
 764     result = Column(UnicodeText)
 765     __mapper_args__ = {'polymorphic_on': discriminator}
 766
 767     def is_comment_report(self):
 768         return self.discriminator=='comment_report'
 769
 770     def is_media_entry_report(self):
 771         return self.discriminator=='media_report'
 772
 773     def is_archived_report(self):
 774         return self.resolved is not None
 775
 776     def archive(self,resolver_id, resolved, result):
 777         self.resolver_id   = resolver_id
 778         self.resolved   = resolved
 779         self.result     = result
 780
 781
 782 class CommentReport(ReportBase):
 783     """
 784     Reports that have been filed on comments.
 785         :keyword    comment_id          Holds the integer value of the reported
 786                                             comment's ID
 787     """
 788     __tablename__ = 'core__reports_on_comments'
 789     __mapper_args__ = {'polymorphic_identity': 'comment_report'}
 790
 791     id = Column('id',Integer, ForeignKey('core__reports.id'),
 792                                                 primary_key=True)
 793     comment_id = Column(Integer, ForeignKey(MediaComment.id), nullable=True)
 794     comment = relationship(
 795         MediaComment, backref=backref("reports_filed_on",
 796             lazy="dynamic"))
 797
 798
 799 class MediaReport(ReportBase):
 800     """
 801     Reports that have been filed on media entries
 802         :keyword    media_entry_id      Holds the integer value of the reported
 803                                             media entry's ID
 804     """
 805     __tablename__ = 'core__reports_on_media'
 806     __mapper_args__ = {'polymorphic_identity': 'media_report'}
 807
 808     id = Column('id',Integer, ForeignKey('core__reports.id'),
 809                                                 primary_key=True)
 810     media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=True)
 811     media_entry = relationship(
 812         MediaEntry,
 813         backref=backref("reports_filed_on",
 814             lazy="dynamic"))
 815
 816 class UserBan(Base):
 817     """
 818     Holds the information on a specific user's ban-state. As long as one of
 819         these is attached to a user, they are banned from accessing mediagoblin.
 820         When they try to log in, they are greeted with a page that tells them
 821         the reason why they are banned and when (if ever) the ban will be
 822         lifted
 823
 824         :keyword user_id          Holds the id of the user this object is
 825                                     attached to. This is a one-to-one
 826                                     relationship.
 827         :keyword expiration_date  Holds the date that the ban will be lifted.
 828                                     If this is null, the ban is permanent
 829                                     unless a moderator manually lifts it.
 830         :keyword reason           Holds the reason why the user was banned.
 831     """
 832     __tablename__ = 'core__user_bans'
 833
 834     user_id = Column(Integer, ForeignKey(User.id), nullable=False,
 835                                                         primary_key=True)
 836     expiration_date = Column(Date)
 837     reason = Column(UnicodeText, nullable=False)
 838
 839
 840 class Privilege(Base):
 841     """
 842     The Privilege table holds all of the different privileges a user can hold.
 843     If a user 'has' a privilege, the User object is in a relationship with the
 844     privilege object.
 845
 846         :keyword privilege_name   Holds a unicode object that is the recognizable
 847                                     name of this privilege. This is the column
 848                                     used for identifying whether or not a user
 849                                     has a necessary privilege or not.
 850
 851     """
 852     __tablename__ = 'core__privileges'
 853
 854     id = Column(Integer, nullable=False, primary_key=True)
 855     privilege_name = Column(Unicode, nullable=False, unique=True)
 856     all_users = relationship(
 857         User,
 858         backref='all_privileges',
 859         secondary="core__privileges_users")
 860
 861     def __init__(self, privilege_name):
 862         '''
 863         Currently consructors are required for tables that are initialized thru
 864         the FOUNDATIONS system. This is because they need to be able to be con-
 865         -structed by a list object holding their arg*s
 866         '''
 867         self.privilege_name = privilege_name
 868
 869     def __repr__(self):
 870         return "<Privilege %s>" % (self.privilege_name)
 871
 872
 873 class PrivilegeUserAssociation(Base):
 874     '''
 875     This table holds the many-to-many relationship between User and Privilege
 876     '''
 877
 878     __tablename__ = 'core__privileges_users'
 879
 880     user = Column(
 881         "user",
 882         Integer,
 883         ForeignKey(User.id),
 884         primary_key=True)
 885     privilege = Column(
 886         "privilege",
 887         Integer,
 888         ForeignKey(Privilege.id),
 889         primary_key=True)
 890
 891 MODELS = [
 892     User, MediaEntry, Tag, MediaTag, MediaComment, Collection, CollectionItem,
 893     MediaFile, FileKeynames, MediaAttachmentFile, ProcessingMetaData,
 894     Notification, CommentNotification, ProcessingNotification, Client,
 895     CommentSubscription, ReportBase, CommentReport, MediaReport, UserBan,
 896         Privilege, PrivilegeUserAssociation,
 897     RequestToken, AccessToken, NonceTimestamp]
 898
 899 """
 900  Foundations are the default rows that are created immediately after the tables
 901  are initialized. Each entry to  this dictionary should be in the format of:
 902                  ModelConstructorObject:List of Dictionaries
 903  (Each Dictionary represents a row on the Table to be created, containing each
 904   of the columns' names as a key string, and each of the columns' values as a
 905   value)
 906
 907  ex. [NOTE THIS IS NOT BASED OFF OF OUR USER TABLE]
 908     user_foundations = [{'name':u'Joanna', 'age':24},
 909                         {'name':u'Andrea', 'age':41}]
 910
 911     FOUNDATIONS = {User:user_foundations}
 912 """
 913 privilege_foundations = [{'privilege_name':u'admin'},
 914                                                 {'privilege_name':u'moderator'},
 915                                                 {'privilege_name':u'uploader'},
 916                                                 {'privilege_name':u'reporter'},
 917                                                 {'privilege_name':u'commenter'},
 918                                                 {'privilege_name':u'active'}]
 919 FOUNDATIONS = {Privilege:privilege_foundations}
 920
 921 ######################################################
 922 # Special, migrations-tracking table
 923 #
 924 # Not listed in MODELS because this is special and not
 925 # really migrated, but used for migrations (for now)
 926 ######################################################
 927
 928 class MigrationData(Base):
 929     __tablename__ = "core__migrations"
 930
 931     name = Column(Unicode, primary_key=True)
 932     version = Column(Integer, nullable=False, default=0)
 933
 934 ######################################################
 935
 936
 937 def show_table_init(engine_uri):
 938     if engine_uri is None:
 939         engine_uri = 'sqlite:///:memory:'
 940     from sqlalchemy import create_engine
 941     engine = create_engine(engine_uri, echo=True)
 942
 943     Base.metadata.create_all(engine)
 944
 945
 946 if __name__ == '__main__':
 947     from sys import argv
 948     print repr(argv)
 949     if len(argv) == 2:
 950         uri = argv[1]
 951     else:
 952         uri = None
 953     show_table_init(uri)