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
  27 from sqlalchemy.orm import relationship, backref
  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 from mediagoblin.db.base import Base, DictReadAttrProxy
  35 from mediagoblin.db.mixin import UserMixin, MediaEntryMixin, MediaCommentMixin, CollectionMixin, CollectionItemMixin
  36 from mediagoblin.tools.files import delete_media_files
  37 from mediagoblin.tools.common import import_component
  38
  39 # It's actually kind of annoying how sqlalchemy-migrate does this, if
  40 # I understand it right, but whatever.  Anyway, don't remove this :P
  41 #
  42 # We could do migration calls more manually instead of relying on
  43 # this import-based meddling...
  44 from migrate import changeset
  45
  46 _log = logging.getLogger(__name__)
  47
  48
  49 class User(Base, UserMixin):
  50     """
  51     TODO: We should consider moving some rarely used fields
  52     into some sort of "shadow" table.
  53     """
  54     __tablename__ = "core__users"
  55
  56     id = Column(Integer, primary_key=True)
  57     username = Column(Unicode, nullable=False, unique=True)
  58     # Note: no db uniqueness constraint on email because it's not
  59     # reliable (many email systems case insensitive despite against
  60     # the RFC) and because it would be a mess to implement at this
  61     # point.
  62     email = Column(Unicode, nullable=False)
  63     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
  64     pw_hash = Column(Unicode, nullable=False)
  65     email_verified = Column(Boolean, default=False)
  66     status = Column(Unicode, default=u"needs_email_verification", nullable=False)
  67     # Intented to be nullable=False, but migrations would not work for it
  68     # set to nullable=True implicitly.
  69     wants_comment_notification = Column(Boolean, default=True)
  70     license_preference = Column(Unicode)
  71     verification_key = Column(Unicode)
  72     is_admin = Column(Boolean, default=False, nullable=False)
  73     url = Column(Unicode)
  74     bio = Column(UnicodeText)  # ??
  75     fp_verification_key = Column(Unicode)
  76     fp_token_expire = Column(DateTime)
  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.email_verified else 'non-verified',
  86                 'admin' if self.is_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
 109 class MediaEntry(Base, MediaEntryMixin):
 110     """
 111     TODO: Consider fetching the media_files using join
 112     """
 113     __tablename__ = "core__media_entries"
 114
 115     id = Column(Integer, primary_key=True)
 116     uploader = Column(Integer, ForeignKey(User.id), nullable=False, index=True)
 117     title = Column(Unicode, nullable=False)
 118     slug = Column(Unicode)
 119     created = Column(DateTime, nullable=False, default=datetime.datetime.now,
 120         index=True)
 121     description = Column(UnicodeText) # ??
 122     media_type = Column(Unicode, nullable=False)
 123     state = Column(Unicode, default=u'unprocessed', nullable=False)
 124         # or use sqlalchemy.types.Enum?
 125     license = Column(Unicode)
 126     collected = Column(Integer, default=0)
 127
 128     fail_error = Column(Unicode)
 129     fail_metadata = Column(JSONEncoded)
 130
 131     transcoding_progress = Column(SmallInteger)
 132
 133     queued_media_file = Column(PathTupleWithSlashes)
 134
 135     queued_task_id = Column(Unicode)
 136
 137     __table_args__ = (
 138         UniqueConstraint('uploader', 'slug'),
 139         {})
 140
 141     get_uploader = relationship(User)
 142
 143     media_files_helper = relationship("MediaFile",
 144         collection_class=attribute_mapped_collection("name"),
 145         cascade="all, delete-orphan"
 146         )
 147     media_files = association_proxy('media_files_helper', 'file_path',
 148         creator=lambda k, v: MediaFile(name=k, file_path=v)
 149         )
 150
 151     attachment_files_helper = relationship("MediaAttachmentFile",
 152         cascade="all, delete-orphan",
 153         order_by="MediaAttachmentFile.created"
 154         )
 155     attachment_files = association_proxy("attachment_files_helper", "dict_view",
 156         creator=lambda v: MediaAttachmentFile(
 157             name=v["name"], filepath=v["filepath"])
 158         )
 159
 160     tags_helper = relationship("MediaTag",
 161         cascade="all, delete-orphan" # should be automatically deleted
 162         )
 163     tags = association_proxy("tags_helper", "dict_view",
 164         creator=lambda v: MediaTag(name=v["name"], slug=v["slug"])
 165         )
 166
 167     collections_helper = relationship("CollectionItem",
 168         cascade="all, delete-orphan"
 169         )
 170     collections = association_proxy("collections_helper", "in_collection")
 171
 172     ## TODO
 173     # fail_error
 174
 175     def get_comments(self, ascending=False):
 176         order_col = MediaComment.created
 177         if not ascending:
 178             order_col = desc(order_col)
 179         return self.all_comments.order_by(order_col)
 180
 181     def url_to_prev(self, urlgen):
 182         """get the next 'newer' entry by this user"""
 183         media = MediaEntry.query.filter(
 184             (MediaEntry.uploader == self.uploader)
 185             & (MediaEntry.state == u'processed')
 186             & (MediaEntry.id > self.id)).order_by(MediaEntry.id).first()
 187
 188         if media is not None:
 189             return media.url_for_self(urlgen)
 190
 191     def url_to_next(self, urlgen):
 192         """get the next 'older' entry by this user"""
 193         media = MediaEntry.query.filter(
 194             (MediaEntry.uploader == self.uploader)
 195             & (MediaEntry.state == u'processed')
 196             & (MediaEntry.id < self.id)).order_by(desc(MediaEntry.id)).first()
 197
 198         if media is not None:
 199             return media.url_for_self(urlgen)
 200
 201     @property
 202     def media_data(self):
 203         return getattr(self, self.media_data_ref)
 204
 205     def media_data_init(self, **kwargs):
 206         """
 207         Initialize or update the contents of a media entry's media_data row
 208         """
 209         media_data = self.media_data
 210
 211         if media_data is None:
 212             # Get the correct table:
 213             table = import_component(self.media_type + '.models:DATA_MODEL')
 214             # No media data, so actually add a new one
 215             media_data = table(**kwargs)
 216             # Get the relationship set up.
 217             media_data.get_media_entry = self
 218         else:
 219             # Update old media data
 220             for field, value in kwargs.iteritems():
 221                 setattr(media_data, field, value)
 222
 223     @memoized_property
 224     def media_data_ref(self):
 225         return import_component(self.media_type + '.models:BACKREF_NAME')
 226
 227     def __repr__(self):
 228         safe_title = self.title.encode('ascii', 'replace')
 229
 230         return '<{classname} {id}: {title}>'.format(
 231                 classname=self.__class__.__name__,
 232                 id=self.id,
 233                 title=safe_title)
 234
 235     def delete(self, del_orphan_tags=True, **kwargs):
 236         """Delete MediaEntry and all related files/attachments/comments
 237
 238         This will *not* automatically delete unused collections, which
 239         can remain empty...
 240
 241         :param del_orphan_tags: True/false if we delete unused Tags too
 242         :param commit: True/False if this should end the db transaction"""
 243         # User's CollectionItems are automatically deleted via "cascade".
 244         # Comments on this Media are deleted by cascade, hopefully.
 245
 246         # Delete all related files/attachments
 247         try:
 248             delete_media_files(self)
 249         except OSError, error:
 250             # Returns list of files we failed to delete
 251             _log.error('No such files from the user "{1}" to delete: '
 252                        '{0}'.format(str(error), self.get_uploader))
 253         _log.info('Deleted Media entry id "{0}"'.format(self.id))
 254         # Related MediaTag's are automatically cleaned, but we might
 255         # want to clean out unused Tag's too.
 256         if del_orphan_tags:
 257             # TODO: Import here due to cyclic imports!!!
 258             #       This cries for refactoring
 259             from mediagoblin.db.util import clean_orphan_tags
 260             clean_orphan_tags(commit=False)
 261         # pass through commit=False/True in kwargs
 262         super(MediaEntry, self).delete(**kwargs)
 263
 264
 265 class FileKeynames(Base):
 266     """
 267     keywords for various places.
 268     currently the MediaFile keys
 269     """
 270     __tablename__ = "core__file_keynames"
 271     id = Column(Integer, primary_key=True)
 272     name = Column(Unicode, unique=True)
 273
 274     def __repr__(self):
 275         return "<FileKeyname %r: %r>" % (self.id, self.name)
 276
 277     @classmethod
 278     def find_or_new(cls, name):
 279         t = cls.query.filter_by(name=name).first()
 280         if t is not None:
 281             return t
 282         return cls(name=name)
 283
 284
 285 class MediaFile(Base):
 286     """
 287     TODO: Highly consider moving "name" into a new table.
 288     TODO: Consider preloading said table in software
 289     """
 290     __tablename__ = "core__mediafiles"
 291
 292     media_entry = Column(
 293         Integer, ForeignKey(MediaEntry.id),
 294         nullable=False)
 295     name_id = Column(SmallInteger, ForeignKey(FileKeynames.id), nullable=False)
 296     file_path = Column(PathTupleWithSlashes)
 297
 298     __table_args__ = (
 299         PrimaryKeyConstraint('media_entry', 'name_id'),
 300         {})
 301
 302     def __repr__(self):
 303         return "<MediaFile %s: %r>" % (self.name, self.file_path)
 304
 305     name_helper = relationship(FileKeynames, lazy="joined", innerjoin=True)
 306     name = association_proxy('name_helper', 'name',
 307         creator=FileKeynames.find_or_new
 308         )
 309
 310
 311 class MediaAttachmentFile(Base):
 312     __tablename__ = "core__attachment_files"
 313
 314     id = Column(Integer, primary_key=True)
 315     media_entry = Column(
 316         Integer, ForeignKey(MediaEntry.id),
 317         nullable=False)
 318     name = Column(Unicode, nullable=False)
 319     filepath = Column(PathTupleWithSlashes)
 320     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 321
 322     @property
 323     def dict_view(self):
 324         """A dict like view on this object"""
 325         return DictReadAttrProxy(self)
 326
 327
 328 class Tag(Base):
 329     __tablename__ = "core__tags"
 330
 331     id = Column(Integer, primary_key=True)
 332     slug = Column(Unicode, nullable=False, unique=True)
 333
 334     def __repr__(self):
 335         return "<Tag %r: %r>" % (self.id, self.slug)
 336
 337     @classmethod
 338     def find_or_new(cls, slug):
 339         t = cls.query.filter_by(slug=slug).first()
 340         if t is not None:
 341             return t
 342         return cls(slug=slug)
 343
 344
 345 class MediaTag(Base):
 346     __tablename__ = "core__media_tags"
 347
 348     id = Column(Integer, primary_key=True)
 349     media_entry = Column(
 350         Integer, ForeignKey(MediaEntry.id),
 351         nullable=False, index=True)
 352     tag = Column(Integer, ForeignKey(Tag.id), nullable=False, index=True)
 353     name = Column(Unicode)
 354     # created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 355
 356     __table_args__ = (
 357         UniqueConstraint('tag', 'media_entry'),
 358         {})
 359
 360     tag_helper = relationship(Tag)
 361     slug = association_proxy('tag_helper', 'slug',
 362         creator=Tag.find_or_new
 363         )
 364
 365     def __init__(self, name=None, slug=None):
 366         Base.__init__(self)
 367         if name is not None:
 368             self.name = name
 369         if slug is not None:
 370             self.tag_helper = Tag.find_or_new(slug)
 371
 372     @property
 373     def dict_view(self):
 374         """A dict like view on this object"""
 375         return DictReadAttrProxy(self)
 376
 377
 378 class MediaComment(Base, MediaCommentMixin):
 379     __tablename__ = "core__media_comments"
 380
 381     id = Column(Integer, primary_key=True)
 382     media_entry = Column(
 383         Integer, ForeignKey(MediaEntry.id), nullable=False, index=True)
 384     author = Column(Integer, ForeignKey(User.id), nullable=False)
 385     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 386     content = Column(UnicodeText, nullable=False)
 387
 388     # Cascade: Comments are owned by their creator. So do the full thing.
 389     # lazy=dynamic: People might post a *lot* of comments,
 390     #     so make the "posted_comments" a query-like thing.
 391     get_author = relationship(User,
 392                               backref=backref("posted_comments",
 393                                               lazy="dynamic",
 394                                               cascade="all, delete-orphan"))
 395
 396     # Cascade: Comments are somewhat owned by their MediaEntry.
 397     #     So do the full thing.
 398     # lazy=dynamic: MediaEntries might have many comments,
 399     #     so make the "all_comments" a query-like thing.
 400     get_media_entry = relationship(MediaEntry,
 401                                    backref=backref("all_comments",
 402                                                    lazy="dynamic",
 403                                                    cascade="all, delete-orphan"))
 404
 405
 406 class Collection(Base, CollectionMixin):
 407     """An 'album' or 'set' of media by a user.
 408
 409     On deletion, contained CollectionItems get automatically reaped via
 410     SQL cascade"""
 411     __tablename__ = "core__collections"
 412
 413     id = Column(Integer, primary_key=True)
 414     title = Column(Unicode, nullable=False)
 415     slug = Column(Unicode)
 416     created = Column(DateTime, nullable=False, default=datetime.datetime.now,
 417                      index=True)
 418     description = Column(UnicodeText)
 419     creator = Column(Integer, ForeignKey(User.id), nullable=False)
 420     # TODO: No of items in Collection. Badly named, can we migrate to num_items?
 421     items = Column(Integer, default=0)
 422
 423     # Cascade: Collections are owned by their creator. So do the full thing.
 424     get_creator = relationship(User,
 425                                backref=backref("collections",
 426                                                cascade="all, delete-orphan"))
 427
 428     __table_args__ = (
 429         UniqueConstraint('creator', 'slug'),
 430         {})
 431
 432     def get_collection_items(self, ascending=False):
 433         #TODO, is this still needed with self.collection_items being available?
 434         order_col = CollectionItem.position
 435         if not ascending:
 436             order_col = desc(order_col)
 437         return CollectionItem.query.filter_by(
 438             collection=self.id).order_by(order_col)
 439
 440
 441 class CollectionItem(Base, CollectionItemMixin):
 442     __tablename__ = "core__collection_items"
 443
 444     id = Column(Integer, primary_key=True)
 445     media_entry = Column(
 446         Integer, ForeignKey(MediaEntry.id), nullable=False, index=True)
 447     collection = Column(Integer, ForeignKey(Collection.id), nullable=False)
 448     note = Column(UnicodeText, nullable=True)
 449     added = Column(DateTime, nullable=False, default=datetime.datetime.now)
 450     position = Column(Integer)
 451
 452     # Cascade: CollectionItems are owned by their Collection. So do the full thing.
 453     in_collection = relationship(Collection,
 454                                  backref=backref(
 455                                      "collection_items",
 456                                      cascade="all, delete-orphan"))
 457
 458     get_media_entry = relationship(MediaEntry)
 459
 460     __table_args__ = (
 461         UniqueConstraint('collection', 'media_entry'),
 462         {})
 463
 464     @property
 465     def dict_view(self):
 466         """A dict like view on this object"""
 467         return DictReadAttrProxy(self)
 468
 469
 470 class ProcessingMetaData(Base):
 471     __tablename__ = 'core__processing_metadata'
 472
 473     id = Column(Integer, primary_key=True)
 474     media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=False,
 475             index=True)
 476     media_entry = relationship(MediaEntry,
 477             backref=backref('processing_metadata',
 478                 cascade='all, delete-orphan'))
 479     callback_url = Column(Unicode)
 480
 481     @property
 482     def dict_view(self):
 483         """A dict like view on this object"""
 484         return DictReadAttrProxy(self)
 485
 486
 487 MODELS = [
 488     User, MediaEntry, Tag, MediaTag, MediaComment, Collection, CollectionItem, MediaFile, FileKeynames,
 489     MediaAttachmentFile, ProcessingMetaData]
 490
 491
 492 ######################################################
 493 # Special, migrations-tracking table
 494 #
 495 # Not listed in MODELS because this is special and not
 496 # really migrated, but used for migrations (for now)
 497 ######################################################
 498
 499 class MigrationData(Base):
 500     __tablename__ = "core__migrations"
 501
 502     name = Column(Unicode, primary_key=True)
 503     version = Column(Integer, nullable=False, default=0)
 504
 505 ######################################################
 506
 507
 508 def show_table_init(engine_uri):
 509     if engine_uri is None:
 510         engine_uri = 'sqlite:///:memory:'
 511     from sqlalchemy import create_engine
 512     engine = create_engine(engine_uri, echo=True)
 513
 514     Base.metadata.create_all(engine)
 515
 516
 517 if __name__ == '__main__':
 518     from sys import argv
 519     print repr(argv)
 520     if len(argv) == 2:
 521         uri = argv[1]
 522     else:
 523         uri = None
 524     show_table_init(uri)