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