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