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