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
  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
  38 # It's actually kind of annoying how sqlalchemy-migrate does this, if
  39 # I understand it right, but whatever.  Anyway, don't remove this :P
  40 #
  41 # We could do migration calls more manually instead of relying on
  42 # this import-based meddling...
  43 from migrate import changeset
  44
  45
  46 class User(Base, UserMixin):
  47     """
  48     TODO: We should consider moving some rarely used fields
  49     into some sort of "shadow" table.
  50     """
  51     __tablename__ = "core__users"
  52
  53     id = Column(Integer, primary_key=True)
  54     username = Column(Unicode, nullable=False, unique=True)
  55     email = Column(Unicode, nullable=False)
  56     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
  57     pw_hash = Column(Unicode, nullable=False)
  58     email_verified = Column(Boolean, default=False)
  59     status = Column(Unicode, default=u"needs_email_verification", nullable=False)
  60     # Intented to be nullable=False, but migrations would not work for it
  61     # set to nullable=True implicitly.
  62     wants_comment_notification = Column(Boolean, default=True)
  63     verification_key = Column(Unicode)
  64     is_admin = Column(Boolean, default=False, nullable=False)
  65     url = Column(Unicode)
  66     bio = Column(UnicodeText)  # ??
  67     fp_verification_key = Column(Unicode)
  68     fp_token_expire = Column(DateTime)
  69
  70     ## TODO
  71     # plugin data would be in a separate model
  72
  73     def __repr__(self):
  74         return '<{0} #{1} {2} {3} "{4}">'.format(
  75                 self.__class__.__name__,
  76                 self.id,
  77                 'verified' if self.email_verified else 'non-verified',
  78                 'admin' if self.is_admin else 'user',
  79                 self.username)
  80
  81
  82 class MediaEntry(Base, MediaEntryMixin):
  83     """
  84     TODO: Consider fetching the media_files using join
  85     """
  86     __tablename__ = "core__media_entries"
  87
  88     id = Column(Integer, primary_key=True)
  89     uploader = Column(Integer, ForeignKey(User.id), nullable=False, index=True)
  90     title = Column(Unicode, nullable=False)
  91     slug = Column(Unicode)
  92     created = Column(DateTime, nullable=False, default=datetime.datetime.now,
  93         index=True)
  94     description = Column(UnicodeText) # ??
  95     media_type = Column(Unicode, nullable=False)
  96     state = Column(Unicode, default=u'unprocessed', nullable=False)
  97         # or use sqlalchemy.types.Enum?
  98     license = Column(Unicode)
  99     collected = Column(Integer, default=0)
 100
 101     fail_error = Column(Unicode)
 102     fail_metadata = Column(JSONEncoded)
 103
 104     transcoding_progress = Column(SmallInteger)
 105
 106     queued_media_file = Column(PathTupleWithSlashes)
 107
 108     queued_task_id = Column(Unicode)
 109
 110     __table_args__ = (
 111         UniqueConstraint('uploader', 'slug'),
 112         {})
 113
 114     get_uploader = relationship(User)
 115
 116     media_files_helper = relationship("MediaFile",
 117         collection_class=attribute_mapped_collection("name"),
 118         cascade="all, delete-orphan"
 119         )
 120     media_files = association_proxy('media_files_helper', 'file_path',
 121         creator=lambda k, v: MediaFile(name=k, file_path=v)
 122         )
 123
 124     attachment_files_helper = relationship("MediaAttachmentFile",
 125         cascade="all, delete-orphan",
 126         order_by="MediaAttachmentFile.created"
 127         )
 128     attachment_files = association_proxy("attachment_files_helper", "dict_view",
 129         creator=lambda v: MediaAttachmentFile(
 130             name=v["name"], filepath=v["filepath"])
 131         )
 132
 133     tags_helper = relationship("MediaTag",
 134         cascade="all, delete-orphan"
 135         )
 136     tags = association_proxy("tags_helper", "dict_view",
 137         creator=lambda v: MediaTag(name=v["name"], slug=v["slug"])
 138         )
 139
 140     collections_helper = relationship("CollectionItem",
 141         cascade="all, delete-orphan"
 142         )
 143     collections = association_proxy("collections_helper", "in_collection")
 144
 145     ## TODO
 146     # media_data
 147     # fail_error
 148
 149     def get_comments(self, ascending=False):
 150         order_col = MediaComment.created
 151         if not ascending:
 152             order_col = desc(order_col)
 153         return MediaComment.query.filter_by(
 154             media_entry=self.id).order_by(order_col)
 155
 156     def url_to_prev(self, urlgen):
 157         """get the next 'newer' entry by this user"""
 158         media = MediaEntry.query.filter(
 159             (MediaEntry.uploader == self.uploader)
 160             & (MediaEntry.state == u'processed')
 161             & (MediaEntry.id > self.id)).order_by(MediaEntry.id).first()
 162
 163         if media is not None:
 164             return media.url_for_self(urlgen)
 165
 166     def url_to_next(self, urlgen):
 167         """get the next 'older' entry by this user"""
 168         media = MediaEntry.query.filter(
 169             (MediaEntry.uploader == self.uploader)
 170             & (MediaEntry.state == u'processed')
 171             & (MediaEntry.id < self.id)).order_by(desc(MediaEntry.id)).first()
 172
 173         if media is not None:
 174             return media.url_for_self(urlgen)
 175
 176     #@memoized_property
 177     @property
 178     def media_data(self):
 179         session = Session()
 180
 181         return session.query(self.media_data_table).filter_by(
 182             media_entry=self.id).first()
 183
 184     def media_data_init(self, **kwargs):
 185         """
 186         Initialize or update the contents of a media entry's media_data row
 187         """
 188         session = Session()
 189
 190         media_data = session.query(self.media_data_table).filter_by(
 191             media_entry=self.id).first()
 192
 193         # No media data, so actually add a new one
 194         if media_data is None:
 195             media_data = self.media_data_table(
 196                 media_entry=self.id,
 197                 **kwargs)
 198             session.add(media_data)
 199         # Update old media data
 200         else:
 201             for field, value in kwargs.iteritems():
 202                 setattr(media_data, field, value)
 203
 204     @memoized_property
 205     def media_data_table(self):
 206         # TODO: memoize this
 207         models_module = self.media_type + '.models'
 208         __import__(models_module)
 209         return sys.modules[models_module].DATA_MODEL
 210
 211     def __repr__(self):
 212         safe_title = self.title.encode('ascii', 'replace')
 213
 214         return '<{classname} {id}: {title}>'.format(
 215                 classname=self.__class__.__name__,
 216                 id=self.id,
 217                 title=safe_title)
 218
 219
 220 class FileKeynames(Base):
 221     """
 222     keywords for various places.
 223     currently the MediaFile keys
 224     """
 225     __tablename__ = "core__file_keynames"
 226     id = Column(Integer, primary_key=True)
 227     name = Column(Unicode, unique=True)
 228
 229     def __repr__(self):
 230         return "<FileKeyname %r: %r>" % (self.id, self.name)
 231
 232     @classmethod
 233     def find_or_new(cls, name):
 234         t = cls.query.filter_by(name=name).first()
 235         if t is not None:
 236             return t
 237         return cls(name=name)
 238
 239
 240 class MediaFile(Base):
 241     """
 242     TODO: Highly consider moving "name" into a new table.
 243     TODO: Consider preloading said table in software
 244     """
 245     __tablename__ = "core__mediafiles"
 246
 247     media_entry = Column(
 248         Integer, ForeignKey(MediaEntry.id),
 249         nullable=False)
 250     name_id = Column(SmallInteger, ForeignKey(FileKeynames.id), nullable=False)
 251     file_path = Column(PathTupleWithSlashes)
 252
 253     __table_args__ = (
 254         PrimaryKeyConstraint('media_entry', 'name_id'),
 255         {})
 256
 257     def __repr__(self):
 258         return "<MediaFile %s: %r>" % (self.name, self.file_path)
 259
 260     name_helper = relationship(FileKeynames, lazy="joined", innerjoin=True)
 261     name = association_proxy('name_helper', 'name',
 262         creator=FileKeynames.find_or_new
 263         )
 264
 265
 266 class MediaAttachmentFile(Base):
 267     __tablename__ = "core__attachment_files"
 268
 269     id = Column(Integer, primary_key=True)
 270     media_entry = Column(
 271         Integer, ForeignKey(MediaEntry.id),
 272         nullable=False)
 273     name = Column(Unicode, nullable=False)
 274     filepath = Column(PathTupleWithSlashes)
 275     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 276
 277     @property
 278     def dict_view(self):
 279         """A dict like view on this object"""
 280         return DictReadAttrProxy(self)
 281
 282
 283 class Tag(Base):
 284     __tablename__ = "core__tags"
 285
 286     id = Column(Integer, primary_key=True)
 287     slug = Column(Unicode, nullable=False, unique=True)
 288
 289     def __repr__(self):
 290         return "<Tag %r: %r>" % (self.id, self.slug)
 291
 292     @classmethod
 293     def find_or_new(cls, slug):
 294         t = cls.query.filter_by(slug=slug).first()
 295         if t is not None:
 296             return t
 297         return cls(slug=slug)
 298
 299
 300 class MediaTag(Base):
 301     __tablename__ = "core__media_tags"
 302
 303     id = Column(Integer, primary_key=True)
 304     media_entry = Column(
 305         Integer, ForeignKey(MediaEntry.id),
 306         nullable=False, index=True)
 307     tag = Column(Integer, ForeignKey(Tag.id), nullable=False, index=True)
 308     name = Column(Unicode)
 309     # created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 310
 311     __table_args__ = (
 312         UniqueConstraint('tag', 'media_entry'),
 313         {})
 314
 315     tag_helper = relationship(Tag)
 316     slug = association_proxy('tag_helper', 'slug',
 317         creator=Tag.find_or_new
 318         )
 319
 320     def __init__(self, name=None, slug=None):
 321         Base.__init__(self)
 322         if name is not None:
 323             self.name = name
 324         if slug is not None:
 325             self.tag_helper = Tag.find_or_new(slug)
 326
 327     @property
 328     def dict_view(self):
 329         """A dict like view on this object"""
 330         return DictReadAttrProxy(self)
 331
 332
 333 class MediaComment(Base, MediaCommentMixin):
 334     __tablename__ = "core__media_comments"
 335
 336     id = Column(Integer, primary_key=True)
 337     media_entry = Column(
 338         Integer, ForeignKey(MediaEntry.id), nullable=False, index=True)
 339     author = Column(Integer, ForeignKey(User.id), nullable=False)
 340     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 341     content = Column(UnicodeText, nullable=False)
 342
 343     get_author = relationship(User)
 344
 345
 346 class Collection(Base, CollectionMixin):
 347     __tablename__ = "core__collections"
 348
 349     id = Column(Integer, primary_key=True)
 350     title = Column(Unicode, nullable=False)
 351     slug = Column(Unicode)
 352     created = Column(DateTime, nullable=False, default=datetime.datetime.now,
 353         index=True)
 354     description = Column(UnicodeText)
 355     creator = Column(Integer, ForeignKey(User.id), nullable=False)
 356     items = Column(Integer, default=0)
 357
 358     get_creator = relationship(User)
 359
 360     def get_collection_items(self, ascending=False):
 361         order_col = CollectionItem.position
 362         if not ascending:
 363             order_col = desc(order_col)
 364         return CollectionItem.query.filter_by(
 365             collection=self.id).order_by(order_col)
 366
 367
 368 class CollectionItem(Base, CollectionItemMixin):
 369     __tablename__ = "core__collection_items"
 370
 371     id = Column(Integer, primary_key=True)
 372     media_entry = Column(
 373         Integer, ForeignKey(MediaEntry.id), nullable=False, index=True)
 374     collection = Column(Integer, ForeignKey(Collection.id), nullable=False)
 375     note = Column(UnicodeText, nullable=True)
 376     added = Column(DateTime, nullable=False, default=datetime.datetime.now)
 377     position = Column(Integer)
 378     in_collection = relationship("Collection")
 379
 380     get_media_entry = relationship(MediaEntry)
 381
 382     __table_args__ = (
 383         UniqueConstraint('collection', 'media_entry'),
 384         {})
 385
 386     @property
 387     def dict_view(self):
 388         """A dict like view on this object"""
 389         return DictReadAttrProxy(self)
 390
 391
 392 class ProcessingMetaData(Base):
 393     __tablename__ = 'core__processing_metadata'
 394
 395     id = Column(Integer, primary_key=True)
 396     media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=False,
 397             index=True)
 398     media_entry = relationship(MediaEntry,
 399             backref=backref('processing_metadata',
 400                 cascade='all, delete-orphan'))
 401     callback_url = Column(Unicode)
 402
 403     @property
 404     def dict_view(self):
 405         """A dict like view on this object"""
 406         return DictReadAttrProxy(self)
 407
 408
 409 MODELS = [
 410     User, MediaEntry, Tag, MediaTag, MediaComment, Collection, CollectionItem, MediaFile, FileKeynames,
 411     MediaAttachmentFile, ProcessingMetaData]
 412
 413
 414 ######################################################
 415 # Special, migrations-tracking table
 416 #
 417 # Not listed in MODELS because this is special and not
 418 # really migrated, but used for migrations (for now)
 419 ######################################################
 420
 421 class MigrationData(Base):
 422     __tablename__ = "core__migrations"
 423
 424     name = Column(Unicode, primary_key=True)
 425     version = Column(Integer, nullable=False, default=0)
 426
 427 ######################################################
 428
 429
 430 def show_table_init(engine_uri):
 431     if engine_uri is None:
 432         engine_uri = 'sqlite:///:memory:'
 433     from sqlalchemy import create_engine
 434     engine = create_engine(engine_uri, echo=True)
 435
 436     Base.metadata.create_all(engine)
 437
 438
 439 if __name__ == '__main__':
 440     from sys import argv
 441     print repr(argv)
 442     if len(argv) == 2:
 443         uri = argv[1]
 444     else:
 445         uri = None
 446     show_table_init(uri)