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
  24 from sqlalchemy import (
  25     Column, Integer, Unicode, UnicodeText, DateTime, Boolean, ForeignKey,
  26     UniqueConstraint)
  27 from sqlalchemy.orm import relationship
  28 from sqlalchemy.orm.collections import attribute_mapped_collection
  29 from sqlalchemy.sql.expression import desc
  30 from sqlalchemy.ext.associationproxy import association_proxy
  31
  32 from mediagoblin.db.sql.extratypes import PathTupleWithSlashes, JSONEncoded
  33 from mediagoblin.db.sql.base import Base, DictReadAttrProxy
  34 from mediagoblin.db.mixin import UserMixin, MediaEntryMixin, MediaCommentMixin
  35
  36 # It's actually kind of annoying how sqlalchemy-migrate does this, if
  37 # I understand it right, but whatever.  Anyway, don't remove this :P
  38 #
  39 # We could do migration calls more manually instead of relying on
  40 # this import-based meddling...
  41 from migrate import changeset
  42
  43
  44 class SimpleFieldAlias(object):
  45     """An alias for any field"""
  46     def __init__(self, fieldname):
  47         self.fieldname = fieldname
  48
  49     def __get__(self, instance, cls):
  50         return getattr(instance, self.fieldname)
  51
  52     def __set__(self, instance, val):
  53         setattr(instance, self.fieldname, val)
  54
  55
  56 class User(Base, UserMixin):
  57     """
  58     TODO: We should consider moving some rarely used fields
  59     into some sort of "shadow" table.
  60     """
  61     __tablename__ = "users"
  62
  63     id = Column(Integer, primary_key=True)
  64     username = Column(Unicode, nullable=False, unique=True)
  65     email = Column(Unicode, nullable=False)
  66     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
  67     pw_hash = Column(Unicode, nullable=False)
  68     email_verified = Column(Boolean, default=False)
  69     status = Column(Unicode, default=u"needs_email_verification", nullable=False)
  70     verification_key = Column(Unicode)
  71     is_admin = Column(Boolean, default=False, nullable=False)
  72     url = Column(Unicode)
  73     bio = Column(UnicodeText)  # ??
  74     fp_verification_key = Column(Unicode)
  75     fp_token_expire = Column(DateTime)
  76
  77     ## TODO
  78     # plugin data would be in a separate model
  79
  80     _id = SimpleFieldAlias("id")
  81
  82
  83 class MediaEntry(Base, MediaEntryMixin):
  84     """
  85     TODO: Consider fetching the media_files using join
  86     """
  87     __tablename__ = "media_entries"
  88
  89     id = Column(Integer, primary_key=True)
  90     uploader = Column(Integer, ForeignKey('users.id'), nullable=False)
  91     title = Column(Unicode, nullable=False)
  92     slug = Column(Unicode)
  93     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
  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
 100     fail_error = Column(Unicode)
 101     fail_metadata = Column(JSONEncoded)
 102
 103     queued_media_file = Column(PathTupleWithSlashes)
 104
 105     queued_task_id = Column(Unicode)
 106
 107     __table_args__ = (
 108         UniqueConstraint('uploader', 'slug'),
 109         {})
 110
 111     get_uploader = relationship(User)
 112
 113     media_files_helper = relationship("MediaFile",
 114         collection_class=attribute_mapped_collection("name"),
 115         cascade="all, delete-orphan"
 116         )
 117     media_files = association_proxy('media_files_helper', 'file_path',
 118         creator=lambda k, v: MediaFile(name=k, file_path=v)
 119         )
 120
 121     attachment_files_helper = relationship("MediaAttachmentFile",
 122         cascade="all, delete-orphan",
 123         order_by="MediaAttachmentFile.created"
 124         )
 125     attachment_files = association_proxy("attachment_files_helper", "dict_view",
 126         creator=lambda v: MediaAttachmentFile(
 127             name=v["name"], filepath=v["filepath"])
 128         )
 129
 130     tags_helper = relationship("MediaTag",
 131         cascade="all, delete-orphan"
 132         )
 133     tags = association_proxy("tags_helper", "dict_view",
 134         creator=lambda v: MediaTag(name=v["name"], slug=v["slug"])
 135         )
 136
 137     ## TODO
 138     # media_data
 139     # fail_error
 140
 141     _id = SimpleFieldAlias("id")
 142
 143     def get_comments(self, ascending=False):
 144         order_col = MediaComment.created
 145         if not ascending:
 146             order_col = desc(order_col)
 147         return MediaComment.query.filter_by(
 148             media_entry=self.id).order_by(order_col)
 149
 150     def url_to_prev(self, urlgen):
 151         """get the next 'newer' entry by this user"""
 152         media = MediaEntry.query.filter(
 153             (MediaEntry.uploader == self.uploader)
 154             & (MediaEntry.state == 'processed')
 155             & (MediaEntry.id > self.id)).order_by(MediaEntry.id).first()
 156
 157         if media is not None:
 158             return media.url_for_self(urlgen)
 159
 160     def url_to_next(self, urlgen):
 161         """get the next 'older' entry by this user"""
 162         media = MediaEntry.query.filter(
 163             (MediaEntry.uploader == self.uploader)
 164             & (MediaEntry.state == 'processed')
 165             & (MediaEntry.id < self.id)).order_by(desc(MediaEntry.id)).first()
 166
 167         if media is not None:
 168             return media.url_for_self(urlgen)
 169
 170     @property
 171     def media_data(self):
 172         # TODO: Replace with proper code to read the correct table
 173         return {}
 174
 175     def media_data_init(self, **kwargs):
 176         # TODO: Implement this
 177         pass
 178
 179
 180 class MediaFile(Base):
 181     """
 182     TODO: Highly consider moving "name" into a new table.
 183     TODO: Consider preloading said table in software
 184     """
 185     __tablename__ = "mediafiles"
 186
 187     media_entry = Column(
 188         Integer, ForeignKey(MediaEntry.id),
 189         nullable=False, primary_key=True)
 190     name = Column(Unicode, primary_key=True)
 191     file_path = Column(PathTupleWithSlashes)
 192
 193     def __repr__(self):
 194         return "<MediaFile %s: %r>" % (self.name, self.file_path)
 195
 196
 197 class MediaAttachmentFile(Base):
 198     __tablename__ = "core__attachment_files"
 199
 200     id = Column(Integer, primary_key=True)
 201     media_entry = Column(
 202         Integer, ForeignKey(MediaEntry.id),
 203         nullable=False)
 204     name = Column(Unicode, nullable=False)
 205     filepath = Column(PathTupleWithSlashes)
 206     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 207
 208     @property
 209     def dict_view(self):
 210         """A dict like view on this object"""
 211         return DictReadAttrProxy(self)
 212
 213
 214 class Tag(Base):
 215     __tablename__ = "tags"
 216
 217     id = Column(Integer, primary_key=True)
 218     slug = Column(Unicode, nullable=False, unique=True)
 219
 220     def __repr__(self):
 221         return "<Tag %r: %r>" % (self.id, self.slug)
 222
 223     @classmethod
 224     def find_or_new(cls, slug):
 225         t = cls.query.filter_by(slug=slug).first()
 226         if t is not None:
 227             return t
 228         return cls(slug=slug)
 229
 230
 231 class MediaTag(Base):
 232     __tablename__ = "media_tags"
 233
 234     id = Column(Integer, primary_key=True)
 235     media_entry = Column(
 236         Integer, ForeignKey(MediaEntry.id),
 237         nullable=False)
 238     tag = Column(Integer, ForeignKey('tags.id'), nullable=False)
 239     name = Column(Unicode)
 240     # created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 241
 242     __table_args__ = (
 243         UniqueConstraint('tag', 'media_entry'),
 244         {})
 245
 246     tag_helper = relationship(Tag)
 247     slug = association_proxy('tag_helper', 'slug',
 248         creator=Tag.find_or_new
 249         )
 250
 251     def __init__(self, name=None, slug=None):
 252         Base.__init__(self)
 253         if name is not None:
 254             self.name = name
 255         if slug is not None:
 256             self.tag_helper = Tag.find_or_new(slug)
 257
 258     @property
 259     def dict_view(self):
 260         """A dict like view on this object"""
 261         return DictReadAttrProxy(self)
 262
 263
 264 class MediaComment(Base, MediaCommentMixin):
 265     __tablename__ = "media_comments"
 266
 267     id = Column(Integer, primary_key=True)
 268     media_entry = Column(
 269         Integer, ForeignKey('media_entries.id'), nullable=False)
 270     author = Column(Integer, ForeignKey('users.id'), nullable=False)
 271     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 272     content = Column(UnicodeText, nullable=False)
 273
 274     get_author = relationship(User)
 275
 276     _id = SimpleFieldAlias("id")
 277
 278
 279 MODELS = [
 280     User, MediaEntry, Tag, MediaTag, MediaComment]
 281
 282
 283 ######################################################
 284 # Special, migrations-tracking table
 285 #
 286 # Not listed in MODELS because this is special and not
 287 # really migrated, but used for migrations (for now)
 288 ######################################################
 289
 290 class MigrationData(Base):
 291     __tablename__ = "migrations"
 292
 293     name = Column(Unicode, primary_key=True)
 294     version = Column(Integer, nullable=False, default=0)
 295
 296 ######################################################
 297
 298
 299 def show_table_init(engine_uri):
 300     if engine_uri is None:
 301         engine_uri = 'sqlite:///:memory:'
 302     from sqlalchemy import create_engine
 303     engine = create_engine(engine_uri, echo=True)
 304
 305     Base.metadata.create_all(engine)
 306
 307
 308 if __name__ == '__main__':
 309     from sys import argv
 310     print repr(argv)
 311     if len(argv) == 2:
 312         uri = argv[1]
 313     else:
 314         uri = None
 315     show_table_init(uri)