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