mediagoblin/db/models.py

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