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
  24 from sqlalchemy import Column, Integer, Unicode, UnicodeText, DateTime, \
  25         Boolean, ForeignKey, UniqueConstraint, PrimaryKeyConstraint, \
  26         SmallInteger, Date
  27 from sqlalchemy.orm import relationship, backref, with_polymorphic
  28 from sqlalchemy.orm.collections import attribute_mapped_collection
  29 from sqlalchemy.sql.expression import desc
  30 from sqlalchemy.ext.associationproxy import association_proxy
  31 from sqlalchemy.util import memoized_property
  32
  33 from mediagoblin.db.extratypes import (PathTupleWithSlashes, JSONEncoded,
  34                                        MutationDict)
  35 from mediagoblin.db.base import Base, DictReadAttrProxy
  36 from mediagoblin.db.mixin import UserMixin, MediaEntryMixin, \
  37         MediaCommentMixin, CollectionMixin, CollectionItemMixin
  38 from mediagoblin.tools.files import delete_media_files
  39 from mediagoblin.tools.common import import_component
  40
  41 # It's actually kind of annoying how sqlalchemy-migrate does this, if
  42 # I understand it right, but whatever.  Anyway, don't remove this :P
  43 #
  44 # We could do migration calls more manually instead of relying on
  45 # this import-based meddling...
  46 from migrate import changeset
  47
  48 _log = logging.getLogger(__name__)
  49
  50
  51
  52 class User(Base, UserMixin):
  53     """
  54     TODO: We should consider moving some rarely used fields
  55     into some sort of "shadow" table.
  56     """
  57     __tablename__ = "core__users"
  58
  59     id = Column(Integer, primary_key=True)
  60     username = Column(Unicode, nullable=False, unique=True)
  61     # Note: no db uniqueness constraint on email because it's not
  62     # reliable (many email systems case insensitive despite against
  63     # the RFC) and because it would be a mess to implement at this
  64     # point.
  65     email = Column(Unicode, nullable=False)
  66     pw_hash = Column(Unicode)
  67     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
  68     # Intented to be nullable=False, but migrations would not work for it
  69     # set to nullable=True implicitly.
  70     wants_comment_notification = Column(Boolean, default=True)
  71     wants_notifications = Column(Boolean, default=True)
  72     license_preference = Column(Unicode)
  73     url = Column(Unicode)
  74     bio = Column(UnicodeText)  # ??
  75     uploaded = Column(Integer, default=0)
  76     upload_limit = Column(Integer)
  77
  78     ## TODO
  79     # plugin data would be in a separate model
  80
  81     def __repr__(self):
  82         return '<{0} #{1} {2} {3} "{4}">'.format(
  83                 self.__class__.__name__,
  84                 self.id,
  85                 'verified' if self.has_privilege(u'active') else 'non-verified',
  86                 'admin' if self.has_privilege(u'admin') else 'user',
  87                 self.username)
  88
  89     def delete(self, **kwargs):
  90         """Deletes a User and all related entries/comments/files/..."""
  91         # Collections get deleted by relationships.
  92
  93         media_entries = MediaEntry.query.filter(MediaEntry.uploader == self.id)
  94         for media in media_entries:
  95             # TODO: Make sure that "MediaEntry.delete()" also deletes
  96             # all related files/Comments
  97             media.delete(del_orphan_tags=False, commit=False)
  98
  99         # Delete now unused tags
 100         # TODO: import here due to cyclic imports!!! This cries for refactoring
 101         from mediagoblin.db.util import clean_orphan_tags
 102         clean_orphan_tags(commit=False)
 103
 104         # Delete user, pass through commit=False/True in kwargs
 105         super(User, self).delete(**kwargs)
 106         _log.info('Deleted user "{0}" account'.format(self.username))
 107
 108     def has_privilege(self, privilege, allow_admin=True):
 109         """
 110         This method checks to make sure a user has all the correct privileges
 111         to access a piece of content.
 112
 113         :param  privilege       A unicode object which represent the different
 114                                 privileges which may give the user access to
 115                                 content.
 116
 117         :param  allow_admin     If this is set to True the then if the user is
 118                                 an admin, then this will always return True
 119                                 even if the user hasn't been given the
 120                                 privilege. (defaults to True)
 121         """
 122         priv = Privilege.query.filter_by(privilege_name=privilege).one()
 123         if priv in self.all_privileges:
 124             return True
 125         elif allow_admin and self.has_privilege(u'admin', allow_admin=False):
 126             return True
 127
 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         context = {
 442             "id": self.id,
 443             "author": author.serialize(request),
 444             "objectType": self.objectType,
 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                         objectType=self.objectType,
 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 = [comment.serialize(request) for comment in self.get_comments()]
 481             total = len(comments)
 482             context["replies"] = {
 483                 "totalItems": total,
 484                 "items": comments,
 485                 "url": request.urlgen(
 486                         "mediagoblin.federation.object.comments",
 487                         objectType=self.objectType,
 488                         id=self.id,
 489                         qualified=True
 490                         ),
 491             }
 492
 493         return context
 494
 495     def unserialize(self, data):
 496         """ Takes API objects and unserializes on existing MediaEntry """
 497         if "displayName" in data:
 498             self.title = data["displayName"]
 499
 500         if "content" in data:
 501             self.description = data["content"]
 502
 503         if "license" in data:
 504             self.license = data["license"]
 505
 506         return True
 507
 508 class FileKeynames(Base):
 509     """
 510     keywords for various places.
 511     currently the MediaFile keys
 512     """
 513     __tablename__ = "core__file_keynames"
 514     id = Column(Integer, primary_key=True)
 515     name = Column(Unicode, unique=True)
 516
 517     def __repr__(self):
 518         return "<FileKeyname %r: %r>" % (self.id, self.name)
 519
 520     @classmethod
 521     def find_or_new(cls, name):
 522         t = cls.query.filter_by(name=name).first()
 523         if t is not None:
 524             return t
 525         return cls(name=name)
 526
 527
 528 class MediaFile(Base):
 529     """
 530     TODO: Highly consider moving "name" into a new table.
 531     TODO: Consider preloading said table in software
 532     """
 533     __tablename__ = "core__mediafiles"
 534
 535     media_entry = Column(
 536         Integer, ForeignKey(MediaEntry.id),
 537         nullable=False)
 538     name_id = Column(SmallInteger, ForeignKey(FileKeynames.id), nullable=False)
 539     file_path = Column(PathTupleWithSlashes)
 540     file_metadata = Column(MutationDict.as_mutable(JSONEncoded))
 541
 542     __table_args__ = (
 543         PrimaryKeyConstraint('media_entry', 'name_id'),
 544         {})
 545
 546     def __repr__(self):
 547         return "<MediaFile %s: %r>" % (self.name, self.file_path)
 548
 549     name_helper = relationship(FileKeynames, lazy="joined", innerjoin=True)
 550     name = association_proxy('name_helper', 'name',
 551         creator=FileKeynames.find_or_new
 552         )
 553
 554
 555 class MediaAttachmentFile(Base):
 556     __tablename__ = "core__attachment_files"
 557
 558     id = Column(Integer, primary_key=True)
 559     media_entry = Column(
 560         Integer, ForeignKey(MediaEntry.id),
 561         nullable=False)
 562     name = Column(Unicode, nullable=False)
 563     filepath = Column(PathTupleWithSlashes)
 564     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 565
 566     @property
 567     def dict_view(self):
 568         """A dict like view on this object"""
 569         return DictReadAttrProxy(self)
 570
 571
 572 class Tag(Base):
 573     __tablename__ = "core__tags"
 574
 575     id = Column(Integer, primary_key=True)
 576     slug = Column(Unicode, nullable=False, unique=True)
 577
 578     def __repr__(self):
 579         return "<Tag %r: %r>" % (self.id, self.slug)
 580
 581     @classmethod
 582     def find_or_new(cls, slug):
 583         t = cls.query.filter_by(slug=slug).first()
 584         if t is not None:
 585             return t
 586         return cls(slug=slug)
 587
 588
 589 class MediaTag(Base):
 590     __tablename__ = "core__media_tags"
 591
 592     id = Column(Integer, primary_key=True)
 593     media_entry = Column(
 594         Integer, ForeignKey(MediaEntry.id),
 595         nullable=False, index=True)
 596     tag = Column(Integer, ForeignKey(Tag.id), nullable=False, index=True)
 597     name = Column(Unicode)
 598     # created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 599
 600     __table_args__ = (
 601         UniqueConstraint('tag', 'media_entry'),
 602         {})
 603
 604     tag_helper = relationship(Tag)
 605     slug = association_proxy('tag_helper', 'slug',
 606         creator=Tag.find_or_new
 607         )
 608
 609     def __init__(self, name=None, slug=None):
 610         Base.__init__(self)
 611         if name is not None:
 612             self.name = name
 613         if slug is not None:
 614             self.tag_helper = Tag.find_or_new(slug)
 615
 616     @property
 617     def dict_view(self):
 618         """A dict like view on this object"""
 619         return DictReadAttrProxy(self)
 620
 621
 622 class MediaComment(Base, MediaCommentMixin):
 623     __tablename__ = "core__media_comments"
 624
 625     id = Column(Integer, primary_key=True)
 626     media_entry = Column(
 627         Integer, ForeignKey(MediaEntry.id), nullable=False, index=True)
 628     author = Column(Integer, ForeignKey(User.id), nullable=False)
 629     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 630     content = Column(UnicodeText, nullable=False)
 631
 632     # Cascade: Comments are owned by their creator. So do the full thing.
 633     # lazy=dynamic: People might post a *lot* of comments,
 634     #     so make the "posted_comments" a query-like thing.
 635     get_author = relationship(User,
 636                               backref=backref("posted_comments",
 637                                               lazy="dynamic",
 638                                               cascade="all, delete-orphan"))
 639     get_entry = relationship(MediaEntry,
 640                              backref=backref("comments",
 641                                              lazy="dynamic",
 642                                              cascade="all, delete-orphan"))
 643
 644     # Cascade: Comments are somewhat owned by their MediaEntry.
 645     #     So do the full thing.
 646     # lazy=dynamic: MediaEntries might have many comments,
 647     #     so make the "all_comments" a query-like thing.
 648     get_media_entry = relationship(MediaEntry,
 649                                    backref=backref("all_comments",
 650                                                    lazy="dynamic",
 651                                                    cascade="all, delete-orphan"))
 652
 653     def serialize(self, request):
 654         """ Unserialize to python dictionary for API """
 655         media = MediaEntry.query.filter_by(id=self.media_entry).first()
 656         author = self.get_author
 657         context = {
 658             "id": self.id,
 659             "objectType": "comment",
 660             "content": self.content,
 661             "inReplyTo": media.serialize(request, show_comments=False),
 662             "author": author.serialize(request)
 663         }
 664
 665         return context
 666
 667     def unserialize(self, data):
 668         """ Takes API objects and unserializes on existing comment """
 669         # Do initial checks to verify the object is correct
 670         required_attributes = ["content", "inReplyTo"]
 671         for attr in required_attributes:
 672             if attr not in data:
 673                 return False
 674
 675         # Validate inReplyTo has ID
 676         if "id" not in data["inReplyTo"]:
 677             return False
 678
 679         # Validate that the ID is correct
 680         try:
 681             media_id = int(data["inReplyTo"]["id"])
 682         except ValueError:
 683             return False
 684
 685         media = MediaEntry.query.filter_by(id=media_id).first()
 686         if media is None:
 687             return False
 688
 689         self.media_entry = media.id
 690         self.content = data["content"]
 691         return True
 692
 693
 694
 695 class Collection(Base, CollectionMixin):
 696     """An 'album' or 'set' of media by a user.
 697
 698     On deletion, contained CollectionItems get automatically reaped via
 699     SQL cascade"""
 700     __tablename__ = "core__collections"
 701
 702     id = Column(Integer, primary_key=True)
 703     title = Column(Unicode, nullable=False)
 704     slug = Column(Unicode)
 705     created = Column(DateTime, nullable=False, default=datetime.datetime.now,
 706                      index=True)
 707     description = Column(UnicodeText)
 708     creator = Column(Integer, ForeignKey(User.id), nullable=False)
 709     # TODO: No of items in Collection. Badly named, can we migrate to num_items?
 710     items = Column(Integer, default=0)
 711
 712     # Cascade: Collections are owned by their creator. So do the full thing.
 713     get_creator = relationship(User,
 714                                backref=backref("collections",
 715                                                cascade="all, delete-orphan"))
 716
 717     __table_args__ = (
 718         UniqueConstraint('creator', 'slug'),
 719         {})
 720
 721     def get_collection_items(self, ascending=False):
 722         #TODO, is this still needed with self.collection_items being available?
 723         order_col = CollectionItem.position
 724         if not ascending:
 725             order_col = desc(order_col)
 726         return CollectionItem.query.filter_by(
 727             collection=self.id).order_by(order_col)
 728
 729     def __repr__(self):
 730         safe_title = self.title.encode('ascii', 'replace')
 731         return '<{classname} #{id}: {title} by {creator}>'.format(
 732             id=self.id,
 733             classname=self.__class__.__name__,
 734             creator=self.creator,
 735             title=safe_title)
 736
 737
 738 class CollectionItem(Base, CollectionItemMixin):
 739     __tablename__ = "core__collection_items"
 740
 741     id = Column(Integer, primary_key=True)
 742     media_entry = Column(
 743         Integer, ForeignKey(MediaEntry.id), nullable=False, index=True)
 744     collection = Column(Integer, ForeignKey(Collection.id), nullable=False)
 745     note = Column(UnicodeText, nullable=True)
 746     added = Column(DateTime, nullable=False, default=datetime.datetime.now)
 747     position = Column(Integer)
 748
 749     # Cascade: CollectionItems are owned by their Collection. So do the full thing.
 750     in_collection = relationship(Collection,
 751                                  backref=backref(
 752                                      "collection_items",
 753                                      cascade="all, delete-orphan"))
 754
 755     get_media_entry = relationship(MediaEntry)
 756
 757     __table_args__ = (
 758         UniqueConstraint('collection', 'media_entry'),
 759         {})
 760
 761     @property
 762     def dict_view(self):
 763         """A dict like view on this object"""
 764         return DictReadAttrProxy(self)
 765
 766     def __repr__(self):
 767         return '<{classname} #{id}: Entry {entry} in {collection}>'.format(
 768             id=self.id,
 769             classname=self.__class__.__name__,
 770             collection=self.collection,
 771             entry=self.media_entry)
 772
 773
 774 class ProcessingMetaData(Base):
 775     __tablename__ = 'core__processing_metadata'
 776
 777     id = Column(Integer, primary_key=True)
 778     media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=False,
 779             index=True)
 780     media_entry = relationship(MediaEntry,
 781             backref=backref('processing_metadata',
 782                 cascade='all, delete-orphan'))
 783     callback_url = Column(Unicode)
 784
 785     @property
 786     def dict_view(self):
 787         """A dict like view on this object"""
 788         return DictReadAttrProxy(self)
 789
 790
 791 class CommentSubscription(Base):
 792     __tablename__ = 'core__comment_subscriptions'
 793     id = Column(Integer, primary_key=True)
 794
 795     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 796
 797     media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=False)
 798     media_entry = relationship(MediaEntry,
 799                         backref=backref('comment_subscriptions',
 800                                         cascade='all, delete-orphan'))
 801
 802     user_id = Column(Integer, ForeignKey(User.id), nullable=False)
 803     user = relationship(User,
 804                         backref=backref('comment_subscriptions',
 805                                         cascade='all, delete-orphan'))
 806
 807     notify = Column(Boolean, nullable=False, default=True)
 808     send_email = Column(Boolean, nullable=False, default=True)
 809
 810     def __repr__(self):
 811         return ('<{classname} #{id}: {user} {media} notify: '
 812                 '{notify} email: {email}>').format(
 813             id=self.id,
 814             classname=self.__class__.__name__,
 815             user=self.user,
 816             media=self.media_entry,
 817             notify=self.notify,
 818             email=self.send_email)
 819
 820
 821 class Notification(Base):
 822     __tablename__ = 'core__notifications'
 823     id = Column(Integer, primary_key=True)
 824     type = Column(Unicode)
 825
 826     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 827
 828     user_id = Column(Integer, ForeignKey('core__users.id'), nullable=False,
 829                      index=True)
 830     seen = Column(Boolean, default=lambda: False, index=True)
 831     user = relationship(
 832         User,
 833         backref=backref('notifications', cascade='all, delete-orphan'))
 834
 835     __mapper_args__ = {
 836         'polymorphic_identity': 'notification',
 837         'polymorphic_on': type
 838     }
 839
 840     def __repr__(self):
 841         return '<{klass} #{id}: {user}: {subject} ({seen})>'.format(
 842             id=self.id,
 843             klass=self.__class__.__name__,
 844             user=self.user,
 845             subject=getattr(self, 'subject', None),
 846             seen='unseen' if not self.seen else 'seen')
 847
 848     def __unicode__(self):
 849         return u'<{klass} #{id}: {user}: {subject} ({seen})>'.format(
 850             id=self.id,
 851             klass=self.__class__.__name__,
 852             user=self.user,
 853             subject=getattr(self, 'subject', None),
 854             seen='unseen' if not self.seen else 'seen')
 855
 856
 857 class CommentNotification(Notification):
 858     __tablename__ = 'core__comment_notifications'
 859     id = Column(Integer, ForeignKey(Notification.id), primary_key=True)
 860
 861     subject_id = Column(Integer, ForeignKey(MediaComment.id))
 862     subject = relationship(
 863         MediaComment,
 864         backref=backref('comment_notifications', cascade='all, delete-orphan'))
 865
 866     __mapper_args__ = {
 867         'polymorphic_identity': 'comment_notification'
 868     }
 869
 870
 871 class ProcessingNotification(Notification):
 872     __tablename__ = 'core__processing_notifications'
 873
 874     id = Column(Integer, ForeignKey(Notification.id), primary_key=True)
 875
 876     subject_id = Column(Integer, ForeignKey(MediaEntry.id))
 877     subject = relationship(
 878         MediaEntry,
 879         backref=backref('processing_notifications',
 880                         cascade='all, delete-orphan'))
 881
 882     __mapper_args__ = {
 883         'polymorphic_identity': 'processing_notification'
 884     }
 885
 886 with_polymorphic(
 887     Notification,
 888     [ProcessingNotification, CommentNotification])
 889
 890 class ReportBase(Base):
 891     """
 892     This is the basic report object which the other reports are based off of.
 893
 894         :keyword    reporter_id         Holds the id of the user who created
 895                                             the report, as an Integer column.
 896         :keyword    report_content      Hold the explanation left by the repor-
 897                                             -ter to indicate why they filed the
 898                                             report in the first place, as a
 899                                             Unicode column.
 900         :keyword    reported_user_id    Holds the id of the user who created
 901                                             the content which was reported, as
 902                                             an Integer column.
 903         :keyword    created             Holds a datetime column of when the re-
 904                                             -port was filed.
 905         :keyword    discriminator       This column distinguishes between the
 906                                             different types of reports.
 907         :keyword    resolver_id         Holds the id of the moderator/admin who
 908                                             resolved the report.
 909         :keyword    resolved            Holds the DateTime object which descri-
 910                                             -bes when this report was resolved
 911         :keyword    result              Holds the UnicodeText column of the
 912                                             resolver's reasons for resolving
 913                                             the report this way. Some of this
 914                                             is auto-generated
 915     """
 916     __tablename__ = 'core__reports'
 917     id = Column(Integer, primary_key=True)
 918     reporter_id = Column(Integer, ForeignKey(User.id), nullable=False)
 919     reporter =  relationship(
 920         User,
 921         backref=backref("reports_filed_by",
 922             lazy="dynamic",
 923             cascade="all, delete-orphan"),
 924         primaryjoin="User.id==ReportBase.reporter_id")
 925     report_content = Column(UnicodeText)
 926     reported_user_id = Column(Integer, ForeignKey(User.id), nullable=False)
 927     reported_user = relationship(
 928         User,
 929         backref=backref("reports_filed_on",
 930             lazy="dynamic",
 931             cascade="all, delete-orphan"),
 932         primaryjoin="User.id==ReportBase.reported_user_id")
 933     created = Column(DateTime, nullable=False, default=datetime.datetime.now())
 934     discriminator = Column('type', Unicode(50))
 935     resolver_id = Column(Integer, ForeignKey(User.id))
 936     resolver = relationship(
 937         User,
 938         backref=backref("reports_resolved_by",
 939             lazy="dynamic",
 940             cascade="all, delete-orphan"),
 941         primaryjoin="User.id==ReportBase.resolver_id")
 942
 943     resolved = Column(DateTime)
 944     result = Column(UnicodeText)
 945     __mapper_args__ = {'polymorphic_on': discriminator}
 946
 947     def is_comment_report(self):
 948         return self.discriminator=='comment_report'
 949
 950     def is_media_entry_report(self):
 951         return self.discriminator=='media_report'
 952
 953     def is_archived_report(self):
 954         return self.resolved is not None
 955
 956     def archive(self,resolver_id, resolved, result):
 957         self.resolver_id   = resolver_id
 958         self.resolved   = resolved
 959         self.result     = result
 960
 961
 962 class CommentReport(ReportBase):
 963     """
 964     Reports that have been filed on comments.
 965         :keyword    comment_id          Holds the integer value of the reported
 966                                             comment's ID
 967     """
 968     __tablename__ = 'core__reports_on_comments'
 969     __mapper_args__ = {'polymorphic_identity': 'comment_report'}
 970
 971     id = Column('id',Integer, ForeignKey('core__reports.id'),
 972                                                 primary_key=True)
 973     comment_id = Column(Integer, ForeignKey(MediaComment.id), nullable=True)
 974     comment = relationship(
 975         MediaComment, backref=backref("reports_filed_on",
 976             lazy="dynamic"))
 977
 978
 979 class MediaReport(ReportBase):
 980     """
 981     Reports that have been filed on media entries
 982         :keyword    media_entry_id      Holds the integer value of the reported
 983                                             media entry's ID
 984     """
 985     __tablename__ = 'core__reports_on_media'
 986     __mapper_args__ = {'polymorphic_identity': 'media_report'}
 987
 988     id = Column('id',Integer, ForeignKey('core__reports.id'),
 989                                                 primary_key=True)
 990     media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=True)
 991     media_entry = relationship(
 992         MediaEntry,
 993         backref=backref("reports_filed_on",
 994             lazy="dynamic"))
 995
 996 class UserBan(Base):
 997     """
 998     Holds the information on a specific user's ban-state. As long as one of
 999         these is attached to a user, they are banned from accessing mediagoblin.
1000         When they try to log in, they are greeted with a page that tells them
1001         the reason why they are banned and when (if ever) the ban will be
1002         lifted
1003
1004         :keyword user_id          Holds the id of the user this object is
1005                                     attached to. This is a one-to-one
1006                                     relationship.
1007         :keyword expiration_date  Holds the date that the ban will be lifted.
1008                                     If this is null, the ban is permanent
1009                                     unless a moderator manually lifts it.
1010         :keyword reason           Holds the reason why the user was banned.
1011     """
1012     __tablename__ = 'core__user_bans'
1013
1014     user_id = Column(Integer, ForeignKey(User.id), nullable=False,
1015                                                         primary_key=True)
1016     expiration_date = Column(Date)
1017     reason = Column(UnicodeText, nullable=False)
1018
1019
1020 class Privilege(Base):
1021     """
1022     The Privilege table holds all of the different privileges a user can hold.
1023     If a user 'has' a privilege, the User object is in a relationship with the
1024     privilege object.
1025
1026         :keyword privilege_name   Holds a unicode object that is the recognizable
1027                                     name of this privilege. This is the column
1028                                     used for identifying whether or not a user
1029                                     has a necessary privilege or not.
1030
1031     """
1032     __tablename__ = 'core__privileges'
1033
1034     id = Column(Integer, nullable=False, primary_key=True)
1035     privilege_name = Column(Unicode, nullable=False, unique=True)
1036     all_users = relationship(
1037         User,
1038         backref='all_privileges',
1039         secondary="core__privileges_users")
1040
1041     def __init__(self, privilege_name):
1042         '''
1043         Currently consructors are required for tables that are initialized thru
1044         the FOUNDATIONS system. This is because they need to be able to be con-
1045         -structed by a list object holding their arg*s
1046         '''
1047         self.privilege_name = privilege_name
1048
1049     def __repr__(self):
1050         return "<Privilege %s>" % (self.privilege_name)
1051
1052
1053 class PrivilegeUserAssociation(Base):
1054     '''
1055     This table holds the many-to-many relationship between User and Privilege
1056     '''
1057
1058     __tablename__ = 'core__privileges_users'
1059
1060     user = Column(
1061         "user",
1062         Integer,
1063         ForeignKey(User.id),
1064         primary_key=True)
1065     privilege = Column(
1066         "privilege",
1067         Integer,
1068         ForeignKey(Privilege.id),
1069         primary_key=True)
1070
1071 MODELS = [
1072     User, MediaEntry, Tag, MediaTag, MediaComment, Collection, CollectionItem,
1073     MediaFile, FileKeynames, MediaAttachmentFile, ProcessingMetaData,
1074     Notification, CommentNotification, ProcessingNotification, Client,
1075     CommentSubscription, ReportBase, CommentReport, MediaReport, UserBan,
1076         Privilege, PrivilegeUserAssociation,
1077     RequestToken, AccessToken, NonceTimestamp]
1078
1079 """
1080  Foundations are the default rows that are created immediately after the tables
1081  are initialized. Each entry to  this dictionary should be in the format of:
1082                  ModelConstructorObject:List of Dictionaries
1083  (Each Dictionary represents a row on the Table to be created, containing each
1084   of the columns' names as a key string, and each of the columns' values as a
1085   value)
1086
1087  ex. [NOTE THIS IS NOT BASED OFF OF OUR USER TABLE]
1088     user_foundations = [{'name':u'Joanna', 'age':24},
1089                         {'name':u'Andrea', 'age':41}]
1090
1091     FOUNDATIONS = {User:user_foundations}
1092 """
1093 privilege_foundations = [{'privilege_name':u'admin'},
1094                                                 {'privilege_name':u'moderator'},
1095                                                 {'privilege_name':u'uploader'},
1096                                                 {'privilege_name':u'reporter'},
1097                                                 {'privilege_name':u'commenter'},
1098                                                 {'privilege_name':u'active'}]
1099 FOUNDATIONS = {Privilege:privilege_foundations}
1100
1101 ######################################################
1102 # Special, migrations-tracking table
1103 #
1104 # Not listed in MODELS because this is special and not
1105 # really migrated, but used for migrations (for now)
1106 ######################################################
1107
1108 class MigrationData(Base):
1109     __tablename__ = "core__migrations"
1110
1111     name = Column(Unicode, primary_key=True)
1112     version = Column(Integer, nullable=False, default=0)
1113
1114 ######################################################
1115
1116
1117 def show_table_init(engine_uri):
1118     if engine_uri is None:
1119         engine_uri = 'sqlite:///:memory:'
1120     from sqlalchemy import create_engine
1121     engine = create_engine(engine_uri, echo=True)
1122
1123     Base.metadata.create_all(engine)
1124
1125
1126 if __name__ == '__main__':
1127     from sys import argv
1128     print repr(argv)
1129     if len(argv) == 2:
1130         uri = argv[1]
1131     else:
1132         uri = None
1133     show_table_init(uri)