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