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