mediagoblin/db/models.py

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