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         ActivityMixin
  40 from mediagoblin.tools.files import delete_media_files
  41 from mediagoblin.tools.common import import_component
  42
  43 # It's actually kind of annoying how sqlalchemy-migrate does this, if
  44 # I understand it right, but whatever.  Anyway, don't remove this :P
  45 #
  46 # We could do migration calls more manually instead of relying on
  47 # this import-based meddling...
  48 from migrate import changeset
  49
  50 _log = logging.getLogger(__name__)
  51
  52
  53
  54 class User(Base, UserMixin):
  55     """
  56     TODO: We should consider moving some rarely used fields
  57     into some sort of "shadow" table.
  58     """
  59     __tablename__ = "core__users"
  60
  61     id = Column(Integer, primary_key=True)
  62     username = Column(Unicode, nullable=False, unique=True)
  63     # Note: no db uniqueness constraint on email because it's not
  64     # reliable (many email systems case insensitive despite against
  65     # the RFC) and because it would be a mess to implement at this
  66     # point.
  67     email = Column(Unicode, nullable=False)
  68     pw_hash = Column(Unicode)
  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     url = Column(Unicode)
  76     bio = Column(UnicodeText)  # ??
  77     uploaded = Column(Integer, default=0)
  78     upload_limit = Column(Integer)
  79
  80     activity_as_object = Column(Integer,
  81                                 ForeignKey("core__activity_intermediators.id"))
  82     activity_as_target = Column(Integer,
  83                                 ForeignKey("core__activity_intermediators.id"))
  84
  85     ## TODO
  86     # plugin data would be in a separate model
  87
  88     def __repr__(self):
  89         return '<{0} #{1} {2} {3} "{4}">'.format(
  90                 self.__class__.__name__,
  91                 self.id,
  92                 'verified' if self.has_privilege(u'active') else 'non-verified',
  93                 'admin' if self.has_privilege(u'admin') else 'user',
  94                 self.username)
  95
  96     def delete(self, **kwargs):
  97         """Deletes a User and all related entries/comments/files/..."""
  98         # Collections get deleted by relationships.
  99
 100         media_entries = MediaEntry.query.filter(MediaEntry.uploader == self.id)
 101         for media in media_entries:
 102             # TODO: Make sure that "MediaEntry.delete()" also deletes
 103             # all related files/Comments
 104             media.delete(del_orphan_tags=False, commit=False)
 105
 106         # Delete now unused tags
 107         # TODO: import here due to cyclic imports!!! This cries for refactoring
 108         from mediagoblin.db.util import clean_orphan_tags
 109         clean_orphan_tags(commit=False)
 110
 111         # Delete user, pass through commit=False/True in kwargs
 112         super(User, self).delete(**kwargs)
 113         _log.info('Deleted user "{0}" account'.format(self.username))
 114
 115     def has_privilege(self, privilege, allow_admin=True):
 116         """
 117         This method checks to make sure a user has all the correct privileges
 118         to access a piece of content.
 119
 120         :param  privilege       A unicode object which represent the different
 121                                 privileges which may give the user access to
 122                                 content.
 123
 124         :param  allow_admin     If this is set to True the then if the user is
 125                                 an admin, then this will always return True
 126                                 even if the user hasn't been given the
 127                                 privilege. (defaults to True)
 128         """
 129         priv = Privilege.query.filter_by(privilege_name=privilege).one()
 130         if priv in self.all_privileges:
 131             return True
 132         elif allow_admin and self.has_privilege(u'admin', allow_admin=False):
 133             return True
 134
 135         return False
 136
 137     def is_banned(self):
 138         """
 139         Checks if this user is banned.
 140
 141             :returns                True if self is banned
 142             :returns                False if self is not
 143         """
 144         return UserBan.query.get(self.id) is not None
 145
 146
 147     def serialize(self, request):
 148         user = {
 149             "id": "acct:{0}@{1}".format(self.username, request.host),
 150             "preferredUsername": self.username,
 151             "displayName": "{0}@{1}".format(self.username, request.host),
 152             "objectType": self.object_type,
 153             "pump_io": {
 154                 "shared": False,
 155                 "followed": False,
 156             },
 157             "links": {
 158                 "self": {
 159                     "href": request.urlgen(
 160                             "mediagoblin.federation.user.profile",
 161                              username=self.username,
 162                              qualified=True
 163                              ),
 164                 },
 165                 "activity-inbox": {
 166                     "href": request.urlgen(
 167                             "mediagoblin.federation.inbox",
 168                             username=self.username,
 169                             qualified=True
 170                             )
 171                 },
 172                 "activity-outbox": {
 173                     "href": request.urlgen(
 174                             "mediagoblin.federation.feed",
 175                             username=self.username,
 176                             qualified=True
 177                             )
 178                 },
 179             },
 180         }
 181
 182         if self.bio:
 183             user.update({"summary": self.bio})
 184         if self.url:
 185             user.update({"url": self.url})
 186
 187         return user
 188
 189 class Client(Base):
 190     """
 191         Model representing a client - Used for API Auth
 192     """
 193     __tablename__ = "core__clients"
 194
 195     id = Column(Unicode, nullable=True, primary_key=True)
 196     secret = Column(Unicode, nullable=False)
 197     expirey = Column(DateTime, nullable=True)
 198     application_type = Column(Unicode, nullable=False)
 199     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 200     updated = Column(DateTime, nullable=False, default=datetime.datetime.now)
 201
 202     # optional stuff
 203     redirect_uri = Column(JSONEncoded, nullable=True)
 204     logo_url = Column(Unicode, nullable=True)
 205     application_name = Column(Unicode, nullable=True)
 206     contacts = Column(JSONEncoded, nullable=True)
 207
 208     def __repr__(self):
 209         if self.application_name:
 210             return "<Client {0} - {1}>".format(self.application_name, self.id)
 211         else:
 212             return "<Client {0}>".format(self.id)
 213
 214 class RequestToken(Base):
 215     """
 216         Model for representing the request tokens
 217     """
 218     __tablename__ = "core__request_tokens"
 219
 220     token = Column(Unicode, primary_key=True)
 221     secret = Column(Unicode, nullable=False)
 222     client = Column(Unicode, ForeignKey(Client.id))
 223     user = Column(Integer, ForeignKey(User.id), nullable=True)
 224     used = Column(Boolean, default=False)
 225     authenticated = Column(Boolean, default=False)
 226     verifier = Column(Unicode, nullable=True)
 227     callback = Column(Unicode, nullable=False, default=u"oob")
 228     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 229     updated = Column(DateTime, nullable=False, default=datetime.datetime.now)
 230
 231 class AccessToken(Base):
 232     """
 233         Model for representing the access tokens
 234     """
 235     __tablename__ = "core__access_tokens"
 236
 237     token = Column(Unicode, nullable=False, primary_key=True)
 238     secret = Column(Unicode, nullable=False)
 239     user = Column(Integer, ForeignKey(User.id))
 240     request_token = Column(Unicode, ForeignKey(RequestToken.token))
 241     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 242     updated = Column(DateTime, nullable=False, default=datetime.datetime.now)
 243
 244
 245 class NonceTimestamp(Base):
 246     """
 247         A place the timestamp and nonce can be stored - this is for OAuth1
 248     """
 249     __tablename__ = "core__nonce_timestamps"
 250
 251     nonce = Column(Unicode, nullable=False, primary_key=True)
 252     timestamp = Column(DateTime, nullable=False, primary_key=True)
 253
 254 class MediaEntry(Base, MediaEntryMixin):
 255     """
 256     TODO: Consider fetching the media_files using join
 257     """
 258     __tablename__ = "core__media_entries"
 259
 260     id = Column(Integer, primary_key=True)
 261     uploader = Column(Integer, ForeignKey(User.id), nullable=False, index=True)
 262     title = Column(Unicode, nullable=False)
 263     slug = Column(Unicode)
 264     created = Column(DateTime, nullable=False, default=datetime.datetime.now,
 265         index=True)
 266     description = Column(UnicodeText) # ??
 267     media_type = Column(Unicode, nullable=False)
 268     state = Column(Unicode, default=u'unprocessed', nullable=False)
 269         # or use sqlalchemy.types.Enum?
 270     license = Column(Unicode)
 271     file_size = Column(Integer, default=0)
 272
 273     fail_error = Column(Unicode)
 274     fail_metadata = Column(JSONEncoded)
 275
 276     transcoding_progress = Column(SmallInteger)
 277
 278     queued_media_file = Column(PathTupleWithSlashes)
 279
 280     queued_task_id = Column(Unicode)
 281
 282     __table_args__ = (
 283         UniqueConstraint('uploader', 'slug'),
 284         {})
 285
 286     get_uploader = relationship(User)
 287
 288     media_files_helper = relationship("MediaFile",
 289         collection_class=attribute_mapped_collection("name"),
 290         cascade="all, delete-orphan"
 291         )
 292     media_files = association_proxy('media_files_helper', 'file_path',
 293         creator=lambda k, v: MediaFile(name=k, file_path=v)
 294         )
 295
 296     attachment_files_helper = relationship("MediaAttachmentFile",
 297         cascade="all, delete-orphan",
 298         order_by="MediaAttachmentFile.created"
 299         )
 300     attachment_files = association_proxy("attachment_files_helper", "dict_view",
 301         creator=lambda v: MediaAttachmentFile(
 302             name=v["name"], filepath=v["filepath"])
 303         )
 304
 305     tags_helper = relationship("MediaTag",
 306         cascade="all, delete-orphan" # should be automatically deleted
 307         )
 308     tags = association_proxy("tags_helper", "dict_view",
 309         creator=lambda v: MediaTag(name=v["name"], slug=v["slug"])
 310         )
 311
 312     collections_helper = relationship("CollectionItem",
 313         cascade="all, delete-orphan"
 314         )
 315     collections = association_proxy("collections_helper", "in_collection")
 316     media_metadata = Column(MutationDict.as_mutable(JSONEncoded),
 317         default=MutationDict())
 318
 319     activity_as_object = Column(Integer,
 320                                 ForeignKey("core__activity_intermediators.id"))
 321     activity_as_target = Column(Integer,
 322                                 ForeignKey("core__activity_intermediators.id"))
 323
 324     ## TODO
 325     # fail_error
 326
 327     def get_comments(self, ascending=False):
 328         order_col = MediaComment.created
 329         if not ascending:
 330             order_col = desc(order_col)
 331         return self.all_comments.order_by(order_col)
 332
 333     def url_to_prev(self, urlgen):
 334         """get the next 'newer' entry by this user"""
 335         media = MediaEntry.query.filter(
 336             (MediaEntry.uploader == self.uploader)
 337             & (MediaEntry.state == u'processed')
 338             & (MediaEntry.id > self.id)).order_by(MediaEntry.id).first()
 339
 340         if media is not None:
 341             return media.url_for_self(urlgen)
 342
 343     def url_to_next(self, urlgen):
 344         """get the next 'older' entry by this user"""
 345         media = MediaEntry.query.filter(
 346             (MediaEntry.uploader == self.uploader)
 347             & (MediaEntry.state == u'processed')
 348             & (MediaEntry.id < self.id)).order_by(desc(MediaEntry.id)).first()
 349
 350         if media is not None:
 351             return media.url_for_self(urlgen)
 352
 353     def get_file_metadata(self, file_key, metadata_key=None):
 354         """
 355         Return the file_metadata dict of a MediaFile. If metadata_key is given,
 356         return the value of the key.
 357         """
 358         media_file = MediaFile.query.filter_by(media_entry=self.id,
 359                                                name=unicode(file_key)).first()
 360
 361         if media_file:
 362             if metadata_key:
 363                 return media_file.file_metadata.get(metadata_key, None)
 364
 365             return media_file.file_metadata
 366
 367     def set_file_metadata(self, file_key, **kwargs):
 368         """
 369         Update the file_metadata of a MediaFile.
 370         """
 371         media_file = MediaFile.query.filter_by(media_entry=self.id,
 372                                                name=unicode(file_key)).first()
 373
 374         file_metadata = media_file.file_metadata or {}
 375
 376         for key, value in kwargs.iteritems():
 377             file_metadata[key] = value
 378
 379         media_file.file_metadata = file_metadata
 380         media_file.save()
 381
 382     @property
 383     def media_data(self):
 384         return getattr(self, self.media_data_ref)
 385
 386     def media_data_init(self, **kwargs):
 387         """
 388         Initialize or update the contents of a media entry's media_data row
 389         """
 390         media_data = self.media_data
 391
 392         if media_data is None:
 393             # Get the correct table:
 394             table = import_component(self.media_type + '.models:DATA_MODEL')
 395             # No media data, so actually add a new one
 396             media_data = table(**kwargs)
 397             # Get the relationship set up.
 398             media_data.get_media_entry = self
 399         else:
 400             # Update old media data
 401             for field, value in kwargs.iteritems():
 402                 setattr(media_data, field, value)
 403
 404     @memoized_property
 405     def media_data_ref(self):
 406         return import_component(self.media_type + '.models:BACKREF_NAME')
 407
 408     def __repr__(self):
 409         safe_title = self.title.encode('ascii', 'replace')
 410
 411         return '<{classname} {id}: {title}>'.format(
 412                 classname=self.__class__.__name__,
 413                 id=self.id,
 414                 title=safe_title)
 415
 416     def delete(self, del_orphan_tags=True, **kwargs):
 417         """Delete MediaEntry and all related files/attachments/comments
 418
 419         This will *not* automatically delete unused collections, which
 420         can remain empty...
 421
 422         :param del_orphan_tags: True/false if we delete unused Tags too
 423         :param commit: True/False if this should end the db transaction"""
 424         # User's CollectionItems are automatically deleted via "cascade".
 425         # Comments on this Media are deleted by cascade, hopefully.
 426
 427         # Delete all related files/attachments
 428         try:
 429             delete_media_files(self)
 430         except OSError, error:
 431             # Returns list of files we failed to delete
 432             _log.error('No such files from the user "{1}" to delete: '
 433                        '{0}'.format(str(error), self.get_uploader))
 434         _log.info('Deleted Media entry id "{0}"'.format(self.id))
 435         # Related MediaTag's are automatically cleaned, but we might
 436         # want to clean out unused Tag's too.
 437         if del_orphan_tags:
 438             # TODO: Import here due to cyclic imports!!!
 439             #       This cries for refactoring
 440             from mediagoblin.db.util import clean_orphan_tags
 441             clean_orphan_tags(commit=False)
 442         # pass through commit=False/True in kwargs
 443         super(MediaEntry, self).delete(**kwargs)
 444
 445     def serialize(self, request, show_comments=True):
 446         """ Unserialize MediaEntry to object """
 447         author = self.get_uploader
 448         context = {
 449             "id": self.id,
 450             "author": author.serialize(request),
 451             "objectType": self.object_type,
 452             "url": self.url_for_self(request.urlgen),
 453             "image": {
 454                 "url": request.host_url + self.thumb_url[1:],
 455             },
 456             "fullImage":{
 457                 "url": request.host_url + self.original_url[1:],
 458             },
 459             "published": self.created.isoformat(),
 460             "updated": self.created.isoformat(),
 461             "pump_io": {
 462                 "shared": False,
 463             },
 464             "links": {
 465                 "self": {
 466                     "href": request.urlgen(
 467                         "mediagoblin.federation.object",
 468                         object_type=self.objectType,
 469                         id=self.id,
 470                         qualified=True
 471                     ),
 472                 },
 473
 474             }
 475         }
 476
 477         if self.title:
 478             context["displayName"] = self.title
 479
 480         if self.description:
 481             context["content"] = self.description
 482
 483         if self.license:
 484             context["license"] = self.license
 485
 486         if show_comments:
 487             comments = [
 488                 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                         object_type=self.object_type,
 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
 662     activity_as_object = Column(Integer,
 663                                 ForeignKey("core__activity_intermediators.id"))
 664     activity_as_target = Column(Integer,
 665                                 ForeignKey("core__activity_intermediators.id"))
 666
 667     def serialize(self, request):
 668         """ Unserialize to python dictionary for API """
 669         media = MediaEntry.query.filter_by(id=self.media_entry).first()
 670         author = self.get_author
 671         context = {
 672             "id": self.id,
 673             "objectType": self.object_type,
 674             "content": self.content,
 675             "inReplyTo": media.serialize(request, show_comments=False),
 676             "author": author.serialize(request)
 677         }
 678
 679         return context
 680
 681     def unserialize(self, data):
 682         """ Takes API objects and unserializes on existing comment """
 683         # Do initial checks to verify the object is correct
 684         required_attributes = ["content", "inReplyTo"]
 685         for attr in required_attributes:
 686             if attr not in data:
 687                 return False
 688
 689         # Validate inReplyTo has ID
 690         if "id" not in data["inReplyTo"]:
 691             return False
 692
 693         # Validate that the ID is correct
 694         try:
 695             media_id = int(data["inReplyTo"]["id"])
 696         except ValueError:
 697             return False
 698
 699         media = MediaEntry.query.filter_by(id=media_id).first()
 700         if media is None:
 701             return False
 702
 703         self.media_entry = media.id
 704         self.content = data["content"]
 705         return True
 706
 707
 708
 709 class Collection(Base, CollectionMixin):
 710     """An 'album' or 'set' of media by a user.
 711
 712     On deletion, contained CollectionItems get automatically reaped via
 713     SQL cascade"""
 714     __tablename__ = "core__collections"
 715
 716     id = Column(Integer, primary_key=True)
 717     title = Column(Unicode, nullable=False)
 718     slug = Column(Unicode)
 719     created = Column(DateTime, nullable=False, default=datetime.datetime.now,
 720                      index=True)
 721     description = Column(UnicodeText)
 722     creator = Column(Integer, ForeignKey(User.id), nullable=False)
 723     # TODO: No of items in Collection. Badly named, can we migrate to num_items?
 724     items = Column(Integer, default=0)
 725
 726     # Cascade: Collections are owned by their creator. So do the full thing.
 727     get_creator = relationship(User,
 728                                backref=backref("collections",
 729                                                cascade="all, delete-orphan"))
 730
 731     activity_as_object = Column(Integer,
 732                                 ForeignKey("core__activity_intermediators.id"))
 733     activity_as_target = Column(Integer,
 734                                 ForeignKey("core__activity_intermediators.id"))
 735
 736     __table_args__ = (
 737         UniqueConstraint('creator', 'slug'),
 738         {})
 739
 740     def get_collection_items(self, ascending=False):
 741         #TODO, is this still needed with self.collection_items being available?
 742         order_col = CollectionItem.position
 743         if not ascending:
 744             order_col = desc(order_col)
 745         return CollectionItem.query.filter_by(
 746             collection=self.id).order_by(order_col)
 747
 748
 749 class CollectionItem(Base, CollectionItemMixin):
 750     __tablename__ = "core__collection_items"
 751
 752     id = Column(Integer, primary_key=True)
 753     media_entry = Column(
 754         Integer, ForeignKey(MediaEntry.id), nullable=False, index=True)
 755     collection = Column(Integer, ForeignKey(Collection.id), nullable=False)
 756     note = Column(UnicodeText, nullable=True)
 757     added = Column(DateTime, nullable=False, default=datetime.datetime.now)
 758     position = Column(Integer)
 759
 760     # Cascade: CollectionItems are owned by their Collection. So do the full thing.
 761     in_collection = relationship(Collection,
 762                                  backref=backref(
 763                                      "collection_items",
 764                                      cascade="all, delete-orphan"))
 765
 766     get_media_entry = relationship(MediaEntry)
 767
 768     __table_args__ = (
 769         UniqueConstraint('collection', 'media_entry'),
 770         {})
 771
 772     @property
 773     def dict_view(self):
 774         """A dict like view on this object"""
 775         return DictReadAttrProxy(self)
 776
 777
 778 class ProcessingMetaData(Base):
 779     __tablename__ = 'core__processing_metadata'
 780
 781     id = Column(Integer, primary_key=True)
 782     media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=False,
 783             index=True)
 784     media_entry = relationship(MediaEntry,
 785             backref=backref('processing_metadata',
 786                 cascade='all, delete-orphan'))
 787     callback_url = Column(Unicode)
 788
 789     @property
 790     def dict_view(self):
 791         """A dict like view on this object"""
 792         return DictReadAttrProxy(self)
 793
 794
 795 class CommentSubscription(Base):
 796     __tablename__ = 'core__comment_subscriptions'
 797     id = Column(Integer, primary_key=True)
 798
 799     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 800
 801     media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=False)
 802     media_entry = relationship(MediaEntry,
 803                         backref=backref('comment_subscriptions',
 804                                         cascade='all, delete-orphan'))
 805
 806     user_id = Column(Integer, ForeignKey(User.id), nullable=False)
 807     user = relationship(User,
 808                         backref=backref('comment_subscriptions',
 809                                         cascade='all, delete-orphan'))
 810
 811     notify = Column(Boolean, nullable=False, default=True)
 812     send_email = Column(Boolean, nullable=False, default=True)
 813
 814     def __repr__(self):
 815         return ('<{classname} #{id}: {user} {media} notify: '
 816                 '{notify} email: {email}>').format(
 817             id=self.id,
 818             classname=self.__class__.__name__,
 819             user=self.user,
 820             media=self.media_entry,
 821             notify=self.notify,
 822             email=self.send_email)
 823
 824
 825 class Notification(Base):
 826     __tablename__ = 'core__notifications'
 827     id = Column(Integer, primary_key=True)
 828     type = Column(Unicode)
 829
 830     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 831
 832     user_id = Column(Integer, ForeignKey('core__users.id'), nullable=False,
 833                      index=True)
 834     seen = Column(Boolean, default=lambda: False, index=True)
 835     user = relationship(
 836         User,
 837         backref=backref('notifications', cascade='all, delete-orphan'))
 838
 839     __mapper_args__ = {
 840         'polymorphic_identity': 'notification',
 841         'polymorphic_on': type
 842     }
 843
 844     def __repr__(self):
 845         return '<{klass} #{id}: {user}: {subject} ({seen})>'.format(
 846             id=self.id,
 847             klass=self.__class__.__name__,
 848             user=self.user,
 849             subject=getattr(self, 'subject', None),
 850             seen='unseen' if not self.seen else 'seen')
 851
 852     def __unicode__(self):
 853         return u'<{klass} #{id}: {user}: {subject} ({seen})>'.format(
 854             id=self.id,
 855             klass=self.__class__.__name__,
 856             user=self.user,
 857             subject=getattr(self, 'subject', None),
 858             seen='unseen' if not self.seen else 'seen')
 859
 860
 861 class CommentNotification(Notification):
 862     __tablename__ = 'core__comment_notifications'
 863     id = Column(Integer, ForeignKey(Notification.id), primary_key=True)
 864
 865     subject_id = Column(Integer, ForeignKey(MediaComment.id))
 866     subject = relationship(
 867         MediaComment,
 868         backref=backref('comment_notifications', cascade='all, delete-orphan'))
 869
 870     __mapper_args__ = {
 871         'polymorphic_identity': 'comment_notification'
 872     }
 873
 874
 875 class ProcessingNotification(Notification):
 876     __tablename__ = 'core__processing_notifications'
 877
 878     id = Column(Integer, ForeignKey(Notification.id), primary_key=True)
 879
 880     subject_id = Column(Integer, ForeignKey(MediaEntry.id))
 881     subject = relationship(
 882         MediaEntry,
 883         backref=backref('processing_notifications',
 884                         cascade='all, delete-orphan'))
 885
 886     __mapper_args__ = {
 887         'polymorphic_identity': 'processing_notification'
 888     }
 889
 890 with_polymorphic(
 891     Notification,
 892     [ProcessingNotification, CommentNotification])
 893
 894 class ReportBase(Base):
 895     """
 896     This is the basic report object which the other reports are based off of.
 897
 898         :keyword    reporter_id         Holds the id of the user who created
 899                                             the report, as an Integer column.
 900         :keyword    report_content      Hold the explanation left by the repor-
 901                                             -ter to indicate why they filed the
 902                                             report in the first place, as a
 903                                             Unicode column.
 904         :keyword    reported_user_id    Holds the id of the user who created
 905                                             the content which was reported, as
 906                                             an Integer column.
 907         :keyword    created             Holds a datetime column of when the re-
 908                                             -port was filed.
 909         :keyword    discriminator       This column distinguishes between the
 910                                             different types of reports.
 911         :keyword    resolver_id         Holds the id of the moderator/admin who
 912                                             resolved the report.
 913         :keyword    resolved            Holds the DateTime object which descri-
 914                                             -bes when this report was resolved
 915         :keyword    result              Holds the UnicodeText column of the
 916                                             resolver's reasons for resolving
 917                                             the report this way. Some of this
 918                                             is auto-generated
 919     """
 920     __tablename__ = 'core__reports'
 921     id = Column(Integer, primary_key=True)
 922     reporter_id = Column(Integer, ForeignKey(User.id), nullable=False)
 923     reporter =  relationship(
 924         User,
 925         backref=backref("reports_filed_by",
 926             lazy="dynamic",
 927             cascade="all, delete-orphan"),
 928         primaryjoin="User.id==ReportBase.reporter_id")
 929     report_content = Column(UnicodeText)
 930     reported_user_id = Column(Integer, ForeignKey(User.id), nullable=False)
 931     reported_user = relationship(
 932         User,
 933         backref=backref("reports_filed_on",
 934             lazy="dynamic",
 935             cascade="all, delete-orphan"),
 936         primaryjoin="User.id==ReportBase.reported_user_id")
 937     created = Column(DateTime, nullable=False, default=datetime.datetime.now())
 938     discriminator = Column('type', Unicode(50))
 939     resolver_id = Column(Integer, ForeignKey(User.id))
 940     resolver = relationship(
 941         User,
 942         backref=backref("reports_resolved_by",
 943             lazy="dynamic",
 944             cascade="all, delete-orphan"),
 945         primaryjoin="User.id==ReportBase.resolver_id")
 946
 947     resolved = Column(DateTime)
 948     result = Column(UnicodeText)
 949     __mapper_args__ = {'polymorphic_on': discriminator}
 950
 951     def is_comment_report(self):
 952         return self.discriminator=='comment_report'
 953
 954     def is_media_entry_report(self):
 955         return self.discriminator=='media_report'
 956
 957     def is_archived_report(self):
 958         return self.resolved is not None
 959
 960     def archive(self,resolver_id, resolved, result):
 961         self.resolver_id   = resolver_id
 962         self.resolved   = resolved
 963         self.result     = result
 964
 965
 966 class CommentReport(ReportBase):
 967     """
 968     Reports that have been filed on comments.
 969         :keyword    comment_id          Holds the integer value of the reported
 970                                             comment's ID
 971     """
 972     __tablename__ = 'core__reports_on_comments'
 973     __mapper_args__ = {'polymorphic_identity': 'comment_report'}
 974
 975     id = Column('id',Integer, ForeignKey('core__reports.id'),
 976                                                 primary_key=True)
 977     comment_id = Column(Integer, ForeignKey(MediaComment.id), nullable=True)
 978     comment = relationship(
 979         MediaComment, backref=backref("reports_filed_on",
 980             lazy="dynamic"))
 981
 982
 983 class MediaReport(ReportBase):
 984     """
 985     Reports that have been filed on media entries
 986         :keyword    media_entry_id      Holds the integer value of the reported
 987                                             media entry's ID
 988     """
 989     __tablename__ = 'core__reports_on_media'
 990     __mapper_args__ = {'polymorphic_identity': 'media_report'}
 991
 992     id = Column('id',Integer, ForeignKey('core__reports.id'),
 993                                                 primary_key=True)
 994     media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=True)
 995     media_entry = relationship(
 996         MediaEntry,
 997         backref=backref("reports_filed_on",
 998             lazy="dynamic"))
 999
1000 class UserBan(Base):
1001     """
1002     Holds the information on a specific user's ban-state. As long as one of
1003         these is attached to a user, they are banned from accessing mediagoblin.
1004         When they try to log in, they are greeted with a page that tells them
1005         the reason why they are banned and when (if ever) the ban will be
1006         lifted
1007
1008         :keyword user_id          Holds the id of the user this object is
1009                                     attached to. This is a one-to-one
1010                                     relationship.
1011         :keyword expiration_date  Holds the date that the ban will be lifted.
1012                                     If this is null, the ban is permanent
1013                                     unless a moderator manually lifts it.
1014         :keyword reason           Holds the reason why the user was banned.
1015     """
1016     __tablename__ = 'core__user_bans'
1017
1018     user_id = Column(Integer, ForeignKey(User.id), nullable=False,
1019                                                         primary_key=True)
1020     expiration_date = Column(Date)
1021     reason = Column(UnicodeText, nullable=False)
1022
1023
1024 class Privilege(Base):
1025     """
1026     The Privilege table holds all of the different privileges a user can hold.
1027     If a user 'has' a privilege, the User object is in a relationship with the
1028     privilege object.
1029
1030         :keyword privilege_name   Holds a unicode object that is the recognizable
1031                                     name of this privilege. This is the column
1032                                     used for identifying whether or not a user
1033                                     has a necessary privilege or not.
1034
1035     """
1036     __tablename__ = 'core__privileges'
1037
1038     id = Column(Integer, nullable=False, primary_key=True)
1039     privilege_name = Column(Unicode, nullable=False, unique=True)
1040     all_users = relationship(
1041         User,
1042         backref='all_privileges',
1043         secondary="core__privileges_users")
1044
1045     def __init__(self, privilege_name):
1046         '''
1047         Currently consructors are required for tables that are initialized thru
1048         the FOUNDATIONS system. This is because they need to be able to be con-
1049         -structed by a list object holding their arg*s
1050         '''
1051         self.privilege_name = privilege_name
1052
1053     def __repr__(self):
1054         return "<Privilege %s>" % (self.privilege_name)
1055
1056
1057 class PrivilegeUserAssociation(Base):
1058     '''
1059     This table holds the many-to-many relationship between User and Privilege
1060     '''
1061
1062     __tablename__ = 'core__privileges_users'
1063
1064     user = Column(
1065         "user",
1066         Integer,
1067         ForeignKey(User.id),
1068         primary_key=True)
1069     privilege = Column(
1070         "privilege",
1071         Integer,
1072         ForeignKey(Privilege.id),
1073         primary_key=True)
1074
1075 class Generator(Base):
1076     """ Information about what created an activity """
1077     __tablename__ = "core__generators"
1078
1079     id = Column(Integer, primary_key=True)
1080     name = Column(Unicode, nullable=False)
1081     published = Column(DateTime, default=datetime.datetime.now)
1082     updated = Column(DateTime, default=datetime.datetime.now)
1083     object_type = Column(Unicode, nullable=False)
1084
1085     def serialize(self, request):
1086         return {
1087             "id": self.id,
1088             "displayName": self.name,
1089             "published": self.published.isoformat(),
1090             "updated": self.updated.isoformat(),
1091             "objectType": self.object_type,
1092         }
1093
1094     def unserialize(self, data):
1095         if "displayName" in data:
1096             self.name = data["displayName"]
1097
1098
1099 class ActivityIntermediator(Base):
1100     """
1101     This is used so that objects/targets can have a foreign key back to this
1102     object and activities can a foreign key to this object. This objects to be
1103     used multiple times for the activity object or target and also allows for
1104     different types of objects to be used as an Activity.
1105     """
1106     __tablename__ = "core__activity_intermediators"
1107
1108     id = Column(Integer, primary_key=True)
1109     type = Column(Unicode, nullable=False)
1110
1111     TYPES = {
1112         "user": User,
1113         "media": MediaEntry,
1114         "comment": MediaComment,
1115         "collection": Collection,
1116     }
1117
1118     def _find_model(self, obj):
1119         """ Finds the model for a given object """
1120         for key, model in self.TYPES.items():
1121             if isinstance(obj, model):
1122                 return key, model
1123
1124         return None, None
1125
1126     def set_object(self, obj):
1127         """ This sets itself as the object for an activity """
1128         key, model = self._find_model(obj)
1129         if key is None:
1130             raise ValueError("Invalid type of object given")
1131
1132         # First set self as activity
1133         obj.activity_as_object = self.id
1134         self.type = key
1135
1136     @property
1137     def get_object(self):
1138         """ Finds the object for an activity """
1139         if self.type is None:
1140             return None
1141
1142         model = self.TYPES[self.type]
1143         return model.query.filter_by(activity_as_object=self.id).first()
1144
1145     def set_target(self, obj):
1146         """ This sets itself as the target for an activity """
1147         key, model = self._find_model(obj)
1148         if key is None:
1149             raise ValueError("Invalid type of object given")
1150
1151         obj.activity_as_target = self.id
1152         self.type = key
1153
1154     @property
1155     def get_target(self):
1156         """ Gets the target for an activity """
1157         if self.type is None:
1158             return None
1159
1160         model = self.TYPES[self.type]
1161         return model.query.filter_by(activity_as_target=self.id).first()
1162
1163     def save(self, *args, **kwargs):
1164         if self.type not in self.TYPES.keys():
1165             raise ValueError("Invalid type set")
1166         Base.save(self, *args, **kwargs)
1167
1168 class Activity(Base, ActivityMixin):
1169     """
1170     This holds all the metadata about an activity such as uploading an image,
1171     posting a comment, etc.
1172     """
1173     __tablename__ = "core__activities"
1174
1175     id = Column(Integer, primary_key=True)
1176     actor = Column(Integer,
1177                    ForeignKey("core__users.id"),
1178                    nullable=False)
1179     published = Column(DateTime, nullable=False, default=datetime.datetime.now)
1180     updated = Column(DateTime, nullable=False, default=datetime.datetime.now)
1181     verb = Column(Unicode, nullable=False)
1182     content = Column(Unicode, nullable=True)
1183     title = Column(Unicode, nullable=True)
1184     generator = Column(Integer,
1185                        ForeignKey("core__generators.id"),
1186                        nullable=True)
1187     object = Column(Integer,
1188                     ForeignKey("core__activity_intermediators.id"),
1189                     nullable=False)
1190     target = Column(Integer,
1191                     ForeignKey("core__activity_intermediators.id"),
1192                     nullable=True)
1193
1194     get_actor = relationship(User,
1195         foreign_keys="Activity.actor", post_update=True)
1196     get_generator = relationship(Generator)
1197
1198     def set_object(self, *args, **kwargs):
1199         if self.object is None:
1200             ai = ActivityIntermediator()
1201             ai.set_object(*args, **kwargs)
1202             ai.save()
1203             self.object = ai.id
1204             return
1205
1206         ai = ActivityIntermediator.query.filter_by(id=self.object).first()
1207         ai.set_object(*args, **kwargs)
1208         ai.save()
1209
1210     @property
1211     def get_object(self):
1212         return self.object.get_object
1213
1214     def set_target(self, *args, **kwargs):
1215         if self.target is None:
1216             ai = ActivityIntermediator()
1217             ai.set_target(*args, **kwargs)
1218             ai.save()
1219             self.object = ai.id
1220             return
1221
1222         ai = ActivityIntermediator.query.filter_by(id=self.target).first()
1223         ai.set_object(*args, **kwargs)
1224         ai.save()
1225
1226     @property
1227     def get_target(self):
1228         if self.target is None:
1229             return None
1230
1231         return self.target.get_target
1232
1233     def save(self, set_updated=True, *args, **kwargs):
1234         if set_updated:
1235             self.updated = datetime.datetime.now()
1236         super(Activity, self).save(*args, **kwargs)
1237
1238 MODELS = [
1239     User, MediaEntry, Tag, MediaTag, MediaComment, Collection, CollectionItem,
1240     MediaFile, FileKeynames, MediaAttachmentFile, ProcessingMetaData,
1241     Notification, CommentNotification, ProcessingNotification, Client,
1242     CommentSubscription, ReportBase, CommentReport, MediaReport, UserBan,
1243         Privilege, PrivilegeUserAssociation,
1244     RequestToken, AccessToken, NonceTimestamp,
1245     Activity, ActivityIntermediator, Generator]
1246
1247 """
1248  Foundations are the default rows that are created immediately after the tables
1249  are initialized. Each entry to  this dictionary should be in the format of:
1250                  ModelConstructorObject:List of Dictionaries
1251  (Each Dictionary represents a row on the Table to be created, containing each
1252   of the columns' names as a key string, and each of the columns' values as a
1253   value)
1254
1255  ex. [NOTE THIS IS NOT BASED OFF OF OUR USER TABLE]
1256     user_foundations = [{'name':u'Joanna', 'age':24},
1257                         {'name':u'Andrea', 'age':41}]
1258
1259     FOUNDATIONS = {User:user_foundations}
1260 """
1261 privilege_foundations = [{'privilege_name':u'admin'},
1262                                                 {'privilege_name':u'moderator'},
1263                                                 {'privilege_name':u'uploader'},
1264                                                 {'privilege_name':u'reporter'},
1265                                                 {'privilege_name':u'commenter'},
1266                                                 {'privilege_name':u'active'}]
1267 FOUNDATIONS = {Privilege:privilege_foundations}
1268
1269 ######################################################
1270 # Special, migrations-tracking table
1271 #
1272 # Not listed in MODELS because this is special and not
1273 # really migrated, but used for migrations (for now)
1274 ######################################################
1275
1276 class MigrationData(Base):
1277     __tablename__ = "core__migrations"
1278
1279     name = Column(Unicode, primary_key=True)
1280     version = Column(Integer, nullable=False, default=0)
1281
1282 ######################################################
1283
1284
1285 def show_table_init(engine_uri):
1286     if engine_uri is None:
1287         engine_uri = 'sqlite:///:memory:'
1288     from sqlalchemy import create_engine
1289     engine = create_engine(engine_uri, echo=True)
1290
1291     Base.metadata.create_all(engine)
1292
1293
1294 if __name__ == '__main__':
1295     from sys import argv
1296     print repr(argv)
1297     if len(argv) == 2:
1298         uri = argv[1]
1299     else:
1300         uri = None
1301     show_table_init(uri)