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