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