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