| 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, Float) |
| 28 | from sqlalchemy.ext.declarative import declarative_base |
| 29 | from sqlalchemy.orm import relationship, backref |
| 30 | from sqlalchemy.orm.collections import attribute_mapped_collection |
| 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 GMGTableBase |
| 36 | from mediagoblin.db.sql.base import Session |
| 37 | |
| 38 | |
| 39 | Base_v0 = declarative_base(cls=GMGTableBase) |
| 40 | |
| 41 | |
| 42 | class User(Base_v0): |
| 43 | """ |
| 44 | TODO: We should consider moving some rarely used fields |
| 45 | into some sort of "shadow" table. |
| 46 | """ |
| 47 | __tablename__ = "core__users" |
| 48 | |
| 49 | id = Column(Integer, primary_key=True) |
| 50 | username = Column(Unicode, nullable=False, unique=True) |
| 51 | email = Column(Unicode, nullable=False) |
| 52 | created = Column(DateTime, nullable=False, default=datetime.datetime.now) |
| 53 | pw_hash = Column(Unicode, nullable=False) |
| 54 | email_verified = Column(Boolean, default=False) |
| 55 | status = Column(Unicode, default=u"needs_email_verification", nullable=False) |
| 56 | verification_key = Column(Unicode) |
| 57 | is_admin = Column(Boolean, default=False, nullable=False) |
| 58 | url = Column(Unicode) |
| 59 | bio = Column(UnicodeText) # ?? |
| 60 | fp_verification_key = Column(Unicode) |
| 61 | fp_token_expire = Column(DateTime) |
| 62 | |
| 63 | ## TODO |
| 64 | # plugin data would be in a separate model |
| 65 | |
| 66 | |
| 67 | class MediaEntry(Base_v0): |
| 68 | """ |
| 69 | TODO: Consider fetching the media_files using join |
| 70 | """ |
| 71 | __tablename__ = "core__media_entries" |
| 72 | |
| 73 | id = Column(Integer, primary_key=True) |
| 74 | uploader = Column(Integer, ForeignKey(User.id), nullable=False, index=True) |
| 75 | title = Column(Unicode, nullable=False) |
| 76 | slug = Column(Unicode) |
| 77 | created = Column(DateTime, nullable=False, default=datetime.datetime.now, |
| 78 | index=True) |
| 79 | description = Column(UnicodeText) # ?? |
| 80 | media_type = Column(Unicode, nullable=False) |
| 81 | state = Column(Unicode, default=u'unprocessed', nullable=False) |
| 82 | # or use sqlalchemy.types.Enum? |
| 83 | license = Column(Unicode) |
| 84 | |
| 85 | fail_error = Column(Unicode) |
| 86 | fail_metadata = Column(JSONEncoded) |
| 87 | |
| 88 | queued_media_file = Column(PathTupleWithSlashes) |
| 89 | |
| 90 | queued_task_id = Column(Unicode) |
| 91 | |
| 92 | __table_args__ = ( |
| 93 | UniqueConstraint('uploader', 'slug'), |
| 94 | {}) |
| 95 | |
| 96 | get_uploader = relationship(User) |
| 97 | |
| 98 | media_files_helper = relationship("MediaFile", |
| 99 | collection_class=attribute_mapped_collection("name"), |
| 100 | cascade="all, delete-orphan" |
| 101 | ) |
| 102 | |
| 103 | attachment_files_helper = relationship("MediaAttachmentFile", |
| 104 | cascade="all, delete-orphan", |
| 105 | order_by="MediaAttachmentFile.created" |
| 106 | ) |
| 107 | |
| 108 | tags_helper = relationship("MediaTag", |
| 109 | cascade="all, delete-orphan" |
| 110 | ) |
| 111 | |
| 112 | def media_data_init(self, **kwargs): |
| 113 | """ |
| 114 | Initialize or update the contents of a media entry's media_data row |
| 115 | """ |
| 116 | session = Session() |
| 117 | |
| 118 | media_data = session.query(self.media_data_table).filter_by( |
| 119 | media_entry=self.id).first() |
| 120 | |
| 121 | # No media data, so actually add a new one |
| 122 | if media_data is None: |
| 123 | media_data = self.media_data_table( |
| 124 | media_entry=self.id, |
| 125 | **kwargs) |
| 126 | session.add(media_data) |
| 127 | # Update old media data |
| 128 | else: |
| 129 | for field, value in kwargs.iteritems(): |
| 130 | setattr(media_data, field, value) |
| 131 | |
| 132 | @memoized_property |
| 133 | def media_data_table(self): |
| 134 | # TODO: memoize this |
| 135 | models_module = self.media_type + '.models' |
| 136 | __import__(models_module) |
| 137 | return sys.modules[models_module].DATA_MODEL |
| 138 | |
| 139 | |
| 140 | class FileKeynames(Base_v0): |
| 141 | """ |
| 142 | keywords for various places. |
| 143 | currently the MediaFile keys |
| 144 | """ |
| 145 | __tablename__ = "core__file_keynames" |
| 146 | id = Column(Integer, primary_key=True) |
| 147 | name = Column(Unicode, unique=True) |
| 148 | |
| 149 | def __repr__(self): |
| 150 | return "<FileKeyname %r: %r>" % (self.id, self.name) |
| 151 | |
| 152 | @classmethod |
| 153 | def find_or_new(cls, name): |
| 154 | t = cls.query.filter_by(name=name).first() |
| 155 | if t is not None: |
| 156 | return t |
| 157 | return cls(name=name) |
| 158 | |
| 159 | |
| 160 | class MediaFile(Base_v0): |
| 161 | """ |
| 162 | TODO: Highly consider moving "name" into a new table. |
| 163 | TODO: Consider preloading said table in software |
| 164 | """ |
| 165 | __tablename__ = "core__mediafiles" |
| 166 | |
| 167 | media_entry = Column( |
| 168 | Integer, ForeignKey(MediaEntry.id), |
| 169 | nullable=False) |
| 170 | name_id = Column(SmallInteger, ForeignKey(FileKeynames.id), nullable=False) |
| 171 | file_path = Column(PathTupleWithSlashes) |
| 172 | |
| 173 | __table_args__ = ( |
| 174 | PrimaryKeyConstraint('media_entry', 'name_id'), |
| 175 | {}) |
| 176 | |
| 177 | def __repr__(self): |
| 178 | return "<MediaFile %s: %r>" % (self.name, self.file_path) |
| 179 | |
| 180 | name_helper = relationship(FileKeynames, lazy="joined", innerjoin=True) |
| 181 | name = association_proxy('name_helper', 'name', |
| 182 | creator=FileKeynames.find_or_new |
| 183 | ) |
| 184 | |
| 185 | |
| 186 | class MediaAttachmentFile(Base_v0): |
| 187 | __tablename__ = "core__attachment_files" |
| 188 | |
| 189 | id = Column(Integer, primary_key=True) |
| 190 | media_entry = Column( |
| 191 | Integer, ForeignKey(MediaEntry.id), |
| 192 | nullable=False) |
| 193 | name = Column(Unicode, nullable=False) |
| 194 | filepath = Column(PathTupleWithSlashes) |
| 195 | created = Column(DateTime, nullable=False, default=datetime.datetime.now) |
| 196 | |
| 197 | |
| 198 | class Tag(Base_v0): |
| 199 | __tablename__ = "core__tags" |
| 200 | |
| 201 | id = Column(Integer, primary_key=True) |
| 202 | slug = Column(Unicode, nullable=False, unique=True) |
| 203 | |
| 204 | def __repr__(self): |
| 205 | return "<Tag %r: %r>" % (self.id, self.slug) |
| 206 | |
| 207 | @classmethod |
| 208 | def find_or_new(cls, slug): |
| 209 | t = cls.query.filter_by(slug=slug).first() |
| 210 | if t is not None: |
| 211 | return t |
| 212 | return cls(slug=slug) |
| 213 | |
| 214 | |
| 215 | class MediaTag(Base_v0): |
| 216 | __tablename__ = "core__media_tags" |
| 217 | |
| 218 | id = Column(Integer, primary_key=True) |
| 219 | media_entry = Column( |
| 220 | Integer, ForeignKey(MediaEntry.id), |
| 221 | nullable=False, index=True) |
| 222 | tag = Column(Integer, ForeignKey(Tag.id), nullable=False, index=True) |
| 223 | name = Column(Unicode) |
| 224 | # created = Column(DateTime, nullable=False, default=datetime.datetime.now) |
| 225 | |
| 226 | __table_args__ = ( |
| 227 | UniqueConstraint('tag', 'media_entry'), |
| 228 | {}) |
| 229 | |
| 230 | tag_helper = relationship(Tag) |
| 231 | slug = association_proxy('tag_helper', 'slug', |
| 232 | creator=Tag.find_or_new |
| 233 | ) |
| 234 | |
| 235 | def __init__(self, name=None, slug=None): |
| 236 | Base_v0.__init__(self) |
| 237 | if name is not None: |
| 238 | self.name = name |
| 239 | if slug is not None: |
| 240 | self.tag_helper = Tag.find_or_new(slug) |
| 241 | |
| 242 | |
| 243 | class MediaComment(Base_v0): |
| 244 | __tablename__ = "core__media_comments" |
| 245 | |
| 246 | id = Column(Integer, primary_key=True) |
| 247 | media_entry = Column( |
| 248 | Integer, ForeignKey(MediaEntry.id), nullable=False, index=True) |
| 249 | author = Column(Integer, ForeignKey(User.id), nullable=False) |
| 250 | created = Column(DateTime, nullable=False, default=datetime.datetime.now) |
| 251 | content = Column(UnicodeText, nullable=False) |
| 252 | |
| 253 | get_author = relationship(User) |
| 254 | |
| 255 | |
| 256 | class ImageData(Base_v0): |
| 257 | __tablename__ = "image__mediadata" |
| 258 | |
| 259 | # The primary key *and* reference to the main media_entry |
| 260 | media_entry = Column(Integer, ForeignKey('core__media_entries.id'), |
| 261 | primary_key=True) |
| 262 | get_media_entry = relationship("MediaEntry", |
| 263 | backref=backref("image__media_data", cascade="all, delete-orphan")) |
| 264 | |
| 265 | width = Column(Integer) |
| 266 | height = Column(Integer) |
| 267 | exif_all = Column(JSONEncoded) |
| 268 | gps_longitude = Column(Float) |
| 269 | gps_latitude = Column(Float) |
| 270 | gps_altitude = Column(Float) |
| 271 | gps_direction = Column(Float) |
| 272 | |
| 273 | |
| 274 | class VideoData(Base_v0): |
| 275 | __tablename__ = "video__mediadata" |
| 276 | |
| 277 | # The primary key *and* reference to the main media_entry |
| 278 | media_entry = Column(Integer, ForeignKey('core__media_entries.id'), |
| 279 | primary_key=True) |
| 280 | get_media_entry = relationship("MediaEntry", |
| 281 | backref=backref("video__media_data", cascade="all, delete-orphan")) |
| 282 | |
| 283 | width = Column(SmallInteger) |
| 284 | height = Column(SmallInteger) |
| 285 | |
| 286 | |
| 287 | ###################################################### |
| 288 | # Special, migrations-tracking table |
| 289 | # |
| 290 | # Not listed in MODELS because this is special and not |
| 291 | # really migrated, but used for migrations (for now) |
| 292 | ###################################################### |
| 293 | |
| 294 | class MigrationData(Base_v0): |
| 295 | __tablename__ = "core__migrations" |
| 296 | |
| 297 | name = Column(Unicode, primary_key=True) |
| 298 | version = Column(Integer, nullable=False, default=0) |
| 299 | |
| 300 | ###################################################### |