mediagoblin/db/models_v0.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 # WHAT IS THIS FILE?
  23 # ------------------
  24 #
  25 # Upon occasion, someone runs into this file and wonders why we have
  26 # both a models.py and a models_v0.py.
  27 #
  28 # The short of it is: you can ignore this file.
  29 #
  30 # The long version is, in two parts:
  31 #
  32 #  - We used to use MongoDB, then we switched to SQL and SQLAlchemy.
  33 #    We needed to convert peoples' databases; the script we had would
  34 #    switch them to the first version right after Mongo, convert over
  35 #    all their tables, then run any migrations that were added after.
  36 #
  37 #  - That script is now removed, but there is some discussion of
  38 #    writing a test that would set us at the first SQL migration and
  39 #    run everything after.  If we wrote that, this file would still be
  40 #    useful.  But for now, it's legacy!
  41 #
  42 ###########################################################################
  43
  44
  45 import datetime
  46 import sys
  47
  48 from sqlalchemy import (
  49     Column, Integer, Unicode, UnicodeText, DateTime, Boolean, ForeignKey,
  50     UniqueConstraint, PrimaryKeyConstraint, SmallInteger, Float)
  51 from sqlalchemy.ext.declarative import declarative_base
  52 from sqlalchemy.orm import relationship, backref
  53 from sqlalchemy.orm.collections import attribute_mapped_collection
  54 from sqlalchemy.ext.associationproxy import association_proxy
  55 from sqlalchemy.util import memoized_property
  56
  57 from mediagoblin.db.extratypes import PathTupleWithSlashes, JSONEncoded
  58 from mediagoblin.db.base import GMGTableBase, Session
  59
  60
  61 Base_v0 = declarative_base(cls=GMGTableBase)
  62
  63
  64 class User(Base_v0):
  65     """
  66     TODO: We should consider moving some rarely used fields
  67     into some sort of "shadow" table.
  68     """
  69     __tablename__ = "core__users"
  70
  71     id = Column(Integer, primary_key=True)
  72     username = Column(Unicode, nullable=False, unique=True)
  73     email = Column(Unicode, nullable=False)
  74     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
  75     pw_hash = Column(Unicode, nullable=False)
  76     email_verified = Column(Boolean, default=False)
  77     status = Column(Unicode, default=u"needs_email_verification", nullable=False)
  78     verification_key = Column(Unicode)
  79     is_admin = Column(Boolean, default=False, nullable=False)
  80     url = Column(Unicode)
  81     bio = Column(UnicodeText)  # ??
  82     fp_verification_key = Column(Unicode)
  83     fp_token_expire = Column(DateTime)
  84
  85     ## TODO
  86     # plugin data would be in a separate model
  87
  88
  89 class MediaEntry(Base_v0):
  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     queued_media_file = Column(PathTupleWithSlashes)
 111
 112     queued_task_id = Column(Unicode)
 113
 114     __table_args__ = (
 115         UniqueConstraint('uploader', 'slug'),
 116         {})
 117
 118     get_uploader = relationship(User)
 119
 120     media_files_helper = relationship("MediaFile",
 121         collection_class=attribute_mapped_collection("name"),
 122         cascade="all, delete-orphan"
 123         )
 124
 125     attachment_files_helper = relationship("MediaAttachmentFile",
 126         cascade="all, delete-orphan",
 127         order_by="MediaAttachmentFile.created"
 128         )
 129
 130     tags_helper = relationship("MediaTag",
 131         cascade="all, delete-orphan"
 132         )
 133
 134     def media_data_init(self, **kwargs):
 135         """
 136         Initialize or update the contents of a media entry's media_data row
 137         """
 138         session = Session()
 139
 140         media_data = session.query(self.media_data_table).filter_by(
 141             media_entry=self.id).first()
 142
 143         # No media data, so actually add a new one
 144         if media_data is None:
 145             media_data = self.media_data_table(
 146                 media_entry=self.id,
 147                 **kwargs)
 148             session.add(media_data)
 149         # Update old media data
 150         else:
 151             for field, value in kwargs.iteritems():
 152                 setattr(media_data, field, value)
 153
 154     @memoized_property
 155     def media_data_table(self):
 156         # TODO: memoize this
 157         models_module = self.media_type + '.models'
 158         __import__(models_module)
 159         return sys.modules[models_module].DATA_MODEL
 160
 161
 162 class FileKeynames(Base_v0):
 163     """
 164     keywords for various places.
 165     currently the MediaFile keys
 166     """
 167     __tablename__ = "core__file_keynames"
 168     id = Column(Integer, primary_key=True)
 169     name = Column(Unicode, unique=True)
 170
 171     def __repr__(self):
 172         return "<FileKeyname %r: %r>" % (self.id, self.name)
 173
 174     @classmethod
 175     def find_or_new(cls, name):
 176         t = cls.query.filter_by(name=name).first()
 177         if t is not None:
 178             return t
 179         return cls(name=name)
 180
 181
 182 class MediaFile(Base_v0):
 183     """
 184     TODO: Highly consider moving "name" into a new table.
 185     TODO: Consider preloading said table in software
 186     """
 187     __tablename__ = "core__mediafiles"
 188
 189     media_entry = Column(
 190         Integer, ForeignKey(MediaEntry.id),
 191         nullable=False)
 192     name_id = Column(SmallInteger, ForeignKey(FileKeynames.id), nullable=False)
 193     file_path = Column(PathTupleWithSlashes)
 194
 195     __table_args__ = (
 196         PrimaryKeyConstraint('media_entry', 'name_id'),
 197         {})
 198
 199     def __repr__(self):
 200         return "<MediaFile %s: %r>" % (self.name, self.file_path)
 201
 202     name_helper = relationship(FileKeynames, lazy="joined", innerjoin=True)
 203     name = association_proxy('name_helper', 'name',
 204         creator=FileKeynames.find_or_new
 205         )
 206
 207
 208 class MediaAttachmentFile(Base_v0):
 209     __tablename__ = "core__attachment_files"
 210
 211     id = Column(Integer, primary_key=True)
 212     media_entry = Column(
 213         Integer, ForeignKey(MediaEntry.id),
 214         nullable=False)
 215     name = Column(Unicode, nullable=False)
 216     filepath = Column(PathTupleWithSlashes)
 217     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 218
 219
 220 class Tag(Base_v0):
 221     __tablename__ = "core__tags"
 222
 223     id = Column(Integer, primary_key=True)
 224     slug = Column(Unicode, nullable=False, unique=True)
 225
 226     def __repr__(self):
 227         return "<Tag %r: %r>" % (self.id, self.slug)
 228
 229     @classmethod
 230     def find_or_new(cls, slug):
 231         t = cls.query.filter_by(slug=slug).first()
 232         if t is not None:
 233             return t
 234         return cls(slug=slug)
 235
 236
 237 class MediaTag(Base_v0):
 238     __tablename__ = "core__media_tags"
 239
 240     id = Column(Integer, primary_key=True)
 241     media_entry = Column(
 242         Integer, ForeignKey(MediaEntry.id),
 243         nullable=False, index=True)
 244     tag = Column(Integer, ForeignKey(Tag.id), nullable=False, index=True)
 245     name = Column(Unicode)
 246     # created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 247
 248     __table_args__ = (
 249         UniqueConstraint('tag', 'media_entry'),
 250         {})
 251
 252     tag_helper = relationship(Tag)
 253     slug = association_proxy('tag_helper', 'slug',
 254         creator=Tag.find_or_new
 255         )
 256
 257     def __init__(self, name=None, slug=None):
 258         Base_v0.__init__(self)
 259         if name is not None:
 260             self.name = name
 261         if slug is not None:
 262             self.tag_helper = Tag.find_or_new(slug)
 263
 264
 265 class MediaComment(Base_v0):
 266     __tablename__ = "core__media_comments"
 267
 268     id = Column(Integer, primary_key=True)
 269     media_entry = Column(
 270         Integer, ForeignKey(MediaEntry.id), nullable=False, index=True)
 271     author = Column(Integer, ForeignKey(User.id), nullable=False)
 272     created = Column(DateTime, nullable=False, default=datetime.datetime.now)
 273     content = Column(UnicodeText, nullable=False)
 274
 275     get_author = relationship(User)
 276
 277
 278 class ImageData(Base_v0):
 279     __tablename__ = "image__mediadata"
 280
 281     # The primary key *and* reference to the main media_entry
 282     media_entry = Column(Integer, ForeignKey('core__media_entries.id'),
 283         primary_key=True)
 284     get_media_entry = relationship("MediaEntry",
 285         backref=backref("image__media_data", cascade="all, delete-orphan"))
 286
 287     width = Column(Integer)
 288     height = Column(Integer)
 289     exif_all = Column(JSONEncoded)
 290     gps_longitude = Column(Float)
 291     gps_latitude = Column(Float)
 292     gps_altitude = Column(Float)
 293     gps_direction = Column(Float)
 294
 295
 296 class VideoData(Base_v0):
 297     __tablename__ = "video__mediadata"
 298
 299     # The primary key *and* reference to the main media_entry
 300     media_entry = Column(Integer, ForeignKey('core__media_entries.id'),
 301         primary_key=True)
 302     get_media_entry = relationship("MediaEntry",
 303         backref=backref("video__media_data", cascade="all, delete-orphan"))
 304
 305     width = Column(SmallInteger)
 306     height = Column(SmallInteger)
 307
 308
 309 class AsciiData(Base_v0):
 310     __tablename__ = "ascii__mediadata"
 311
 312     # The primary key *and* reference to the main media_entry
 313     media_entry = Column(Integer, ForeignKey('core__media_entries.id'),
 314         primary_key=True)
 315     get_media_entry = relationship("MediaEntry",
 316         backref=backref("ascii__media_data", cascade="all, delete-orphan"))
 317
 318
 319 class AudioData(Base_v0):
 320     __tablename__ = "audio__mediadata"
 321
 322     # The primary key *and* reference to the main media_entry
 323     media_entry = Column(Integer, ForeignKey('core__media_entries.id'),
 324         primary_key=True)
 325     get_media_entry = relationship("MediaEntry",
 326         backref=backref("audio__media_data", cascade="all, delete-orphan"))
 327
 328
 329 ######################################################
 330 # Special, migrations-tracking table
 331 #
 332 # Not listed in MODELS because this is special and not
 333 # really migrated, but used for migrations (for now)
 334 ######################################################
 335
 336 class MigrationData(Base_v0):
 337     __tablename__ = "core__migrations"
 338
 339     name = Column(Unicode, primary_key=True)
 340     version = Column(Integer, nullable=False, default=0)
 341
 342 ######################################################