1 # GNU MediaGoblin -- federated, autonomous media hosting
2 # Copyright (C) 2011, 2012 MediaGoblin contributors. See AUTHORS.
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.
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.
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/>.
18 TODO: indexes on foreignkeys, where useful.
24 from sqlalchemy
import Column
, Integer
, Unicode
, UnicodeText
, DateTime
, \
25 Boolean
, ForeignKey
, UniqueConstraint
, PrimaryKeyConstraint
, \
27 from sqlalchemy
.orm
import relationship
, backref
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 from sqlalchemy
.util
import memoized_property
33 from mediagoblin
.db
.extratypes
import PathTupleWithSlashes
, JSONEncoded
34 from mediagoblin
.db
.base
import Base
, DictReadAttrProxy
35 from mediagoblin
.db
.mixin
import UserMixin
, MediaEntryMixin
, MediaCommentMixin
, CollectionMixin
, CollectionItemMixin
36 from mediagoblin
.tools
.files
import delete_media_files
37 from mediagoblin
.tools
.common
import import_component
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
42 # We could do migration calls more manually instead of relying on
43 # this import-based meddling...
44 from migrate
import changeset
46 _log
= logging
.getLogger(__name__
)
49 class User(Base
, UserMixin
):
51 TODO: We should consider moving some rarely used fields
52 into some sort of "shadow" table.
54 __tablename__
= "core__users"
56 id = Column(Integer
, primary_key
=True)
57 username
= Column(Unicode
, nullable
=False, unique
=True)
58 email
= Column(Unicode
, nullable
=False)
59 created
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
)
60 pw_hash
= Column(Unicode
, nullable
=False)
61 email_verified
= Column(Boolean
, default
=False)
62 status
= Column(Unicode
, default
=u
"needs_email_verification", nullable
=False)
63 # Intented to be nullable=False, but migrations would not work for it
64 # set to nullable=True implicitly.
65 wants_comment_notification
= Column(Boolean
, default
=True)
66 license_preference
= Column(Unicode
)
67 verification_key
= Column(Unicode
)
68 is_admin
= Column(Boolean
, default
=False, nullable
=False)
70 bio
= Column(UnicodeText
) # ??
71 fp_verification_key
= Column(Unicode
)
72 fp_token_expire
= Column(DateTime
)
75 # plugin data would be in a separate model
78 return '<{0} #{1} {2} {3} "{4}">'.format(
79 self
.__class
__.__name
__,
81 'verified' if self
.email_verified
else 'non-verified',
82 'admin' if self
.is_admin
else 'user',
85 def delete(self
, **kwargs
):
86 """Deletes a User and all related entries/comments/files/..."""
87 # Collections get deleted by relationships.
89 media_entries
= MediaEntry
.query
.filter(MediaEntry
.uploader
== self
.id)
90 for media
in media_entries
:
91 # TODO: Make sure that "MediaEntry.delete()" also deletes
92 # all related files/Comments
93 media
.delete(del_orphan_tags
=False, commit
=False)
95 # Delete now unused tags
96 # TODO: import here due to cyclic imports!!! This cries for refactoring
97 from mediagoblin
.db
.util
import clean_orphan_tags
98 clean_orphan_tags(commit
=False)
100 # Delete user, pass through commit=False/True in kwargs
101 super(User
, self
).delete(**kwargs
)
102 _log
.info('Deleted user "{0}" account'.format(self
.username
))
105 class MediaEntry(Base
, MediaEntryMixin
):
107 TODO: Consider fetching the media_files using join
109 __tablename__
= "core__media_entries"
111 id = Column(Integer
, primary_key
=True)
112 uploader
= Column(Integer
, ForeignKey(User
.id), nullable
=False, index
=True)
113 title
= Column(Unicode
, nullable
=False)
114 slug
= Column(Unicode
)
115 created
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
,
117 description
= Column(UnicodeText
) # ??
118 media_type
= Column(Unicode
, nullable
=False)
119 state
= Column(Unicode
, default
=u
'unprocessed', nullable
=False)
120 # or use sqlalchemy.types.Enum?
121 license
= Column(Unicode
)
122 collected
= Column(Integer
, default
=0)
124 fail_error
= Column(Unicode
)
125 fail_metadata
= Column(JSONEncoded
)
127 transcoding_progress
= Column(SmallInteger
)
129 queued_media_file
= Column(PathTupleWithSlashes
)
131 queued_task_id
= Column(Unicode
)
134 UniqueConstraint('uploader', 'slug'),
137 get_uploader
= relationship(User
)
139 media_files_helper
= relationship("MediaFile",
140 collection_class
=attribute_mapped_collection("name"),
141 cascade
="all, delete-orphan"
143 media_files
= association_proxy('media_files_helper', 'file_path',
144 creator
=lambda k
, v
: MediaFile(name
=k
, file_path
=v
)
147 attachment_files_helper
= relationship("MediaAttachmentFile",
148 cascade
="all, delete-orphan",
149 order_by
="MediaAttachmentFile.created"
151 attachment_files
= association_proxy("attachment_files_helper", "dict_view",
152 creator
=lambda v
: MediaAttachmentFile(
153 name
=v
["name"], filepath
=v
["filepath"])
156 tags_helper
= relationship("MediaTag",
157 cascade
="all, delete-orphan" # should be automatically deleted
159 tags
= association_proxy("tags_helper", "dict_view",
160 creator
=lambda v
: MediaTag(name
=v
["name"], slug
=v
["slug"])
163 collections_helper
= relationship("CollectionItem",
164 cascade
="all, delete-orphan"
166 collections
= association_proxy("collections_helper", "in_collection")
171 def get_comments(self
, ascending
=False):
172 order_col
= MediaComment
.created
174 order_col
= desc(order_col
)
175 return self
.all_comments
.order_by(order_col
)
177 def url_to_prev(self
, urlgen
):
178 """get the next 'newer' entry by this user"""
179 media
= MediaEntry
.query
.filter(
180 (MediaEntry
.uploader
== self
.uploader
)
181 & (MediaEntry
.state
== u
'processed')
182 & (MediaEntry
.id > self
.id)).order_by(MediaEntry
.id).first()
184 if media
is not None:
185 return media
.url_for_self(urlgen
)
187 def url_to_next(self
, urlgen
):
188 """get the next 'older' entry by this user"""
189 media
= MediaEntry
.query
.filter(
190 (MediaEntry
.uploader
== self
.uploader
)
191 & (MediaEntry
.state
== u
'processed')
192 & (MediaEntry
.id < self
.id)).order_by(desc(MediaEntry
.id)).first()
194 if media
is not None:
195 return media
.url_for_self(urlgen
)
198 def media_data(self
):
199 return getattr(self
, self
.media_data_ref
)
201 def media_data_init(self
, **kwargs
):
203 Initialize or update the contents of a media entry's media_data row
205 media_data
= self
.media_data
207 if media_data
is None:
208 # Get the correct table:
209 table
= import_component(self
.media_type
+ '.models:DATA_MODEL')
210 # No media data, so actually add a new one
211 media_data
= table(**kwargs
)
212 # Get the relationship set up.
213 media_data
.get_media_entry
= self
215 # Update old media data
216 for field
, value
in kwargs
.iteritems():
217 setattr(media_data
, field
, value
)
220 def media_data_ref(self
):
221 return import_component(self
.media_type
+ '.models:BACKREF_NAME')
224 safe_title
= self
.title
.encode('ascii', 'replace')
226 return '<{classname} {id}: {title}>'.format(
227 classname
=self
.__class
__.__name
__,
231 def delete(self
, del_orphan_tags
=True, **kwargs
):
232 """Delete MediaEntry and all related files/attachments/comments
234 This will *not* automatically delete unused collections, which
237 :param del_orphan_tags: True/false if we delete unused Tags too
238 :param commit: True/False if this should end the db transaction"""
239 # User's CollectionItems are automatically deleted via "cascade".
240 # Comments on this Media are deleted by cascade, hopefully.
242 # Delete all related files/attachments
244 delete_media_files(self
)
245 except OSError, error
:
246 # Returns list of files we failed to delete
247 _log
.error('No such files from the user "{1}" to delete: '
248 '{0}'.format(str(error
), self
.get_uploader
))
249 _log
.info('Deleted Media entry id "{0}"'.format(self
.id))
250 # Related MediaTag's are automatically cleaned, but we might
251 # want to clean out unused Tag's too.
253 # TODO: Import here due to cyclic imports!!!
254 # This cries for refactoring
255 from mediagoblin
.db
.util
import clean_orphan_tags
256 clean_orphan_tags(commit
=False)
257 # pass through commit=False/True in kwargs
258 super(MediaEntry
, self
).delete(**kwargs
)
261 class FileKeynames(Base
):
263 keywords for various places.
264 currently the MediaFile keys
266 __tablename__
= "core__file_keynames"
267 id = Column(Integer
, primary_key
=True)
268 name
= Column(Unicode
, unique
=True)
271 return "<FileKeyname %r: %r>" % (self
.id, self
.name
)
274 def find_or_new(cls
, name
):
275 t
= cls
.query
.filter_by(name
=name
).first()
278 return cls(name
=name
)
281 class MediaFile(Base
):
283 TODO: Highly consider moving "name" into a new table.
284 TODO: Consider preloading said table in software
286 __tablename__
= "core__mediafiles"
288 media_entry
= Column(
289 Integer
, ForeignKey(MediaEntry
.id),
291 name_id
= Column(SmallInteger
, ForeignKey(FileKeynames
.id), nullable
=False)
292 file_path
= Column(PathTupleWithSlashes
)
295 PrimaryKeyConstraint('media_entry', 'name_id'),
299 return "<MediaFile %s: %r>" % (self
.name
, self
.file_path
)
301 name_helper
= relationship(FileKeynames
, lazy
="joined", innerjoin
=True)
302 name
= association_proxy('name_helper', 'name',
303 creator
=FileKeynames
.find_or_new
307 class MediaAttachmentFile(Base
):
308 __tablename__
= "core__attachment_files"
310 id = Column(Integer
, primary_key
=True)
311 media_entry
= Column(
312 Integer
, ForeignKey(MediaEntry
.id),
314 name
= Column(Unicode
, nullable
=False)
315 filepath
= Column(PathTupleWithSlashes
)
316 created
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
)
320 """A dict like view on this object"""
321 return DictReadAttrProxy(self
)
325 __tablename__
= "core__tags"
327 id = Column(Integer
, primary_key
=True)
328 slug
= Column(Unicode
, nullable
=False, unique
=True)
331 return "<Tag %r: %r>" % (self
.id, self
.slug
)
334 def find_or_new(cls
, slug
):
335 t
= cls
.query
.filter_by(slug
=slug
).first()
338 return cls(slug
=slug
)
341 class MediaTag(Base
):
342 __tablename__
= "core__media_tags"
344 id = Column(Integer
, primary_key
=True)
345 media_entry
= Column(
346 Integer
, ForeignKey(MediaEntry
.id),
347 nullable
=False, index
=True)
348 tag
= Column(Integer
, ForeignKey(Tag
.id), nullable
=False, index
=True)
349 name
= Column(Unicode
)
350 # created = Column(DateTime, nullable=False, default=datetime.datetime.now)
353 UniqueConstraint('tag', 'media_entry'),
356 tag_helper
= relationship(Tag
)
357 slug
= association_proxy('tag_helper', 'slug',
358 creator
=Tag
.find_or_new
361 def __init__(self
, name
=None, slug
=None):
366 self
.tag_helper
= Tag
.find_or_new(slug
)
370 """A dict like view on this object"""
371 return DictReadAttrProxy(self
)
374 class MediaComment(Base
, MediaCommentMixin
):
375 __tablename__
= "core__media_comments"
377 id = Column(Integer
, primary_key
=True)
378 media_entry
= Column(
379 Integer
, ForeignKey(MediaEntry
.id), nullable
=False, index
=True)
380 author
= Column(Integer
, ForeignKey(User
.id), nullable
=False)
381 created
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
)
382 content
= Column(UnicodeText
, nullable
=False)
384 # Cascade: Comments are owned by their creator. So do the full thing.
385 # lazy=dynamic: People might post a *lot* of comments,
386 # so make the "posted_comments" a query-like thing.
387 get_author
= relationship(User
,
388 backref
=backref("posted_comments",
390 cascade
="all, delete-orphan"))
392 # Cascade: Comments are somewhat owned by their MediaEntry.
393 # So do the full thing.
394 # lazy=dynamic: MediaEntries might have many comments,
395 # so make the "all_comments" a query-like thing.
396 get_media_entry
= relationship(MediaEntry
,
397 backref
=backref("all_comments",
399 cascade
="all, delete-orphan"))
402 class Collection(Base
, CollectionMixin
):
403 """An 'album' or 'set' of media by a user.
405 On deletion, contained CollectionItems get automatically reaped via
407 __tablename__
= "core__collections"
409 id = Column(Integer
, primary_key
=True)
410 title
= Column(Unicode
, nullable
=False)
411 slug
= Column(Unicode
)
412 created
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
,
414 description
= Column(UnicodeText
)
415 creator
= Column(Integer
, ForeignKey(User
.id), nullable
=False)
416 # TODO: No of items in Collection. Badly named, can we migrate to num_items?
417 items
= Column(Integer
, default
=0)
419 # Cascade: Collections are owned by their creator. So do the full thing.
420 get_creator
= relationship(User
,
421 backref
=backref("collections",
422 cascade
="all, delete-orphan"))
425 UniqueConstraint('creator', 'slug'),
428 def get_collection_items(self
, ascending
=False):
429 #TODO, is this still needed with self.collection_items being available?
430 order_col
= CollectionItem
.position
432 order_col
= desc(order_col
)
433 return CollectionItem
.query
.filter_by(
434 collection
=self
.id).order_by(order_col
)
437 class CollectionItem(Base
, CollectionItemMixin
):
438 __tablename__
= "core__collection_items"
440 id = Column(Integer
, primary_key
=True)
441 media_entry
= Column(
442 Integer
, ForeignKey(MediaEntry
.id), nullable
=False, index
=True)
443 collection
= Column(Integer
, ForeignKey(Collection
.id), nullable
=False)
444 note
= Column(UnicodeText
, nullable
=True)
445 added
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
)
446 position
= Column(Integer
)
448 # Cascade: CollectionItems are owned by their Collection. So do the full thing.
449 in_collection
= relationship(Collection
,
452 cascade
="all, delete-orphan"))
454 get_media_entry
= relationship(MediaEntry
)
457 UniqueConstraint('collection', 'media_entry'),
462 """A dict like view on this object"""
463 return DictReadAttrProxy(self
)
466 class ProcessingMetaData(Base
):
467 __tablename__
= 'core__processing_metadata'
469 id = Column(Integer
, primary_key
=True)
470 media_entry_id
= Column(Integer
, ForeignKey(MediaEntry
.id), nullable
=False,
472 media_entry
= relationship(MediaEntry
,
473 backref
=backref('processing_metadata',
474 cascade
='all, delete-orphan'))
475 callback_url
= Column(Unicode
)
479 """A dict like view on this object"""
480 return DictReadAttrProxy(self
)
484 User
, MediaEntry
, Tag
, MediaTag
, MediaComment
, Collection
, CollectionItem
, MediaFile
, FileKeynames
,
485 MediaAttachmentFile
, ProcessingMetaData
]
488 ######################################################
489 # Special, migrations-tracking table
491 # Not listed in MODELS because this is special and not
492 # really migrated, but used for migrations (for now)
493 ######################################################
495 class MigrationData(Base
):
496 __tablename__
= "core__migrations"
498 name
= Column(Unicode
, primary_key
=True)
499 version
= Column(Integer
, nullable
=False, default
=0)
501 ######################################################
504 def show_table_init(engine_uri
):
505 if engine_uri
is None:
506 engine_uri
= 'sqlite:///:memory:'
507 from sqlalchemy
import create_engine
508 engine
= create_engine(engine_uri
, echo
=True)
510 Base
.metadata
.create_all(engine
)
513 if __name__
== '__main__':