mediagoblin/db/models.py

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