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
, with_polymorphic
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
34 from mediagoblin
.db
.extratypes
import (PathTupleWithSlashes
, JSONEncoded
,
36 from mediagoblin
.db
.base
import Base
, DictReadAttrProxy
37 from mediagoblin
.db
.mixin
import UserMixin
, MediaEntryMixin
, \
38 MediaCommentMixin
, CollectionMixin
, CollectionItemMixin
39 from mediagoblin
.tools
.files
import delete_media_files
40 from mediagoblin
.tools
.common
import import_component
42 # It's actually kind of annoying how sqlalchemy-migrate does this, if
43 # I understand it right, but whatever. Anyway, don't remove this :P
45 # We could do migration calls more manually instead of relying on
46 # this import-based meddling...
47 from migrate
import changeset
49 _log
= logging
.getLogger(__name__
)
51 MutationDict
.associate_with(JSONEncoded
)
54 class User(Base
, UserMixin
):
56 TODO: We should consider moving some rarely used fields
57 into some sort of "shadow" table.
59 __tablename__
= "core__users"
61 id = Column(Integer
, primary_key
=True)
62 username
= Column(Unicode
, nullable
=False, unique
=True)
63 # Note: no db uniqueness constraint on email because it's not
64 # reliable (many email systems case insensitive despite against
65 # the RFC) and because it would be a mess to implement at this
67 email
= Column(Unicode
, nullable
=False)
68 pw_hash
= Column(Unicode
)
69 email_verified
= Column(Boolean
, default
=False)
70 created
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
)
71 status
= Column(Unicode
, default
=u
"needs_email_verification", nullable
=False)
72 # Intented to be nullable=False, but migrations would not work for it
73 # set to nullable=True implicitly.
74 wants_comment_notification
= Column(Boolean
, default
=True)
75 wants_notifications
= Column(Boolean
, default
=True)
76 license_preference
= Column(Unicode
)
77 is_admin
= Column(Boolean
, default
=False, nullable
=False)
79 bio
= Column(UnicodeText
) # ??
80 uploaded
= Column(Integer
, default
=0)
81 upload_limit
= Column(Integer
)
84 # plugin data would be in a separate model
87 return '<{0} #{1} {2} {3} "{4}">'.format(
88 self
.__class
__.__name
__,
90 'verified' if self
.email_verified
else 'non-verified',
91 'admin' if self
.is_admin
else 'user',
94 def delete(self
, **kwargs
):
95 """Deletes a User and all related entries/comments/files/..."""
96 # Collections get deleted by relationships.
98 media_entries
= MediaEntry
.query
.filter(MediaEntry
.uploader
== self
.id)
99 for media
in media_entries
:
100 # TODO: Make sure that "MediaEntry.delete()" also deletes
101 # all related files/Comments
102 media
.delete(del_orphan_tags
=False, commit
=False)
104 # Delete now unused tags
105 # TODO: import here due to cyclic imports!!! This cries for refactoring
106 from mediagoblin
.db
.util
import clean_orphan_tags
107 clean_orphan_tags(commit
=False)
109 # Delete user, pass through commit=False/True in kwargs
110 super(User
, self
).delete(**kwargs
)
111 _log
.info('Deleted user "{0}" account'.format(self
.username
))
116 Model representing a client - Used for API Auth
118 __tablename__
= "core__clients"
120 id = Column(Unicode
, nullable
=True, primary_key
=True)
121 secret
= Column(Unicode
, nullable
=False)
122 expirey
= Column(DateTime
, nullable
=True)
123 application_type
= Column(Unicode
, nullable
=False)
124 created
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
)
125 updated
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
)
128 redirect_uri
= Column(JSONEncoded
, nullable
=True)
129 logo_url
= Column(Unicode
, nullable
=True)
130 application_name
= Column(Unicode
, nullable
=True)
131 contacts
= Column(JSONEncoded
, nullable
=True)
134 if self
.application_name
:
135 return "<Client {0} - {1}>".format(self
.application_name
, self
.id)
137 return "<Client {0}>".format(self
.id)
139 class RequestToken(Base
):
141 Model for representing the request tokens
143 __tablename__
= "core__request_tokens"
145 token
= Column(Unicode
, primary_key
=True)
146 secret
= Column(Unicode
, nullable
=False)
147 client
= Column(Unicode
, ForeignKey(Client
.id))
148 user
= Column(Integer
, ForeignKey(User
.id), nullable
=True)
149 used
= Column(Boolean
, default
=False)
150 authenticated
= Column(Boolean
, default
=False)
151 verifier
= Column(Unicode
, nullable
=True)
152 callback
= Column(Unicode
, nullable
=False, default
=u
"oob")
153 created
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
)
154 updated
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
)
156 class AccessToken(Base
):
158 Model for representing the access tokens
160 __tablename__
= "core__access_tokens"
162 token
= Column(Unicode
, nullable
=False, primary_key
=True)
163 secret
= Column(Unicode
, nullable
=False)
164 user
= Column(Integer
, ForeignKey(User
.id))
165 request_token
= Column(Unicode
, ForeignKey(RequestToken
.token
))
166 created
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
)
167 updated
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
)
170 class NonceTimestamp(Base
):
172 A place the timestamp and nonce can be stored - this is for OAuth1
174 __tablename__
= "core__nonce_timestamps"
176 nonce
= Column(Unicode
, nullable
=False, primary_key
=True)
177 timestamp
= Column(DateTime
, nullable
=False, primary_key
=True)
180 class MediaEntry(Base
, MediaEntryMixin
):
182 TODO: Consider fetching the media_files using join
184 __tablename__
= "core__media_entries"
186 id = Column(Integer
, primary_key
=True)
187 uploader
= Column(Integer
, ForeignKey(User
.id), nullable
=False, index
=True)
188 title
= Column(Unicode
, nullable
=False)
189 slug
= Column(Unicode
)
190 created
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
,
192 description
= Column(UnicodeText
) # ??
193 media_type
= Column(Unicode
, nullable
=False)
194 state
= Column(Unicode
, default
=u
'unprocessed', nullable
=False)
195 # or use sqlalchemy.types.Enum?
196 license
= Column(Unicode
)
197 collected
= Column(Integer
, default
=0)
198 file_size
= Column(Integer
, default
=0)
200 fail_error
= Column(Unicode
)
201 fail_metadata
= Column(JSONEncoded
)
203 transcoding_progress
= Column(SmallInteger
)
205 queued_media_file
= Column(PathTupleWithSlashes
)
207 queued_task_id
= Column(Unicode
)
210 UniqueConstraint('uploader', 'slug'),
213 get_uploader
= relationship(User
)
215 media_files_helper
= relationship("MediaFile",
216 collection_class
=attribute_mapped_collection("name"),
217 cascade
="all, delete-orphan"
219 media_files
= association_proxy('media_files_helper', 'file_path',
220 creator
=lambda k
, v
: MediaFile(name
=k
, file_path
=v
)
223 attachment_files_helper
= relationship("MediaAttachmentFile",
224 cascade
="all, delete-orphan",
225 order_by
="MediaAttachmentFile.created"
227 attachment_files
= association_proxy("attachment_files_helper", "dict_view",
228 creator
=lambda v
: MediaAttachmentFile(
229 name
=v
["name"], filepath
=v
["filepath"])
232 tags_helper
= relationship("MediaTag",
233 cascade
="all, delete-orphan" # should be automatically deleted
235 tags
= association_proxy("tags_helper", "dict_view",
236 creator
=lambda v
: MediaTag(name
=v
["name"], slug
=v
["slug"])
239 collections_helper
= relationship("CollectionItem",
240 cascade
="all, delete-orphan"
242 collections
= association_proxy("collections_helper", "in_collection")
247 def get_comments(self
, ascending
=False):
248 order_col
= MediaComment
.created
250 order_col
= desc(order_col
)
251 return self
.all_comments
.order_by(order_col
)
253 def url_to_prev(self
, urlgen
):
254 """get the next 'newer' entry by this user"""
255 media
= MediaEntry
.query
.filter(
256 (MediaEntry
.uploader
== self
.uploader
)
257 & (MediaEntry
.state
== u
'processed')
258 & (MediaEntry
.id > self
.id)).order_by(MediaEntry
.id).first()
260 if media
is not None:
261 return media
.url_for_self(urlgen
)
263 def url_to_next(self
, urlgen
):
264 """get the next 'older' entry by this user"""
265 media
= MediaEntry
.query
.filter(
266 (MediaEntry
.uploader
== self
.uploader
)
267 & (MediaEntry
.state
== u
'processed')
268 & (MediaEntry
.id < self
.id)).order_by(desc(MediaEntry
.id)).first()
270 if media
is not None:
271 return media
.url_for_self(urlgen
)
273 def get_file_metadata(self
, file_key
, metadata_key
=None):
275 Return the file_metadata dict of a MediaFile. If metadata_key is given,
276 return the value of the key.
278 media_file
= MediaFile
.query
.filter_by(media_entry
=self
.id,
279 name
=unicode(file_key
)).first()
283 return media_file
.file_metadata
.get(metadata_key
, None)
285 return media_file
.file_metadata
287 def set_file_metadata(self
, file_key
, **kwargs
):
289 Update the file_metadata of a MediaFile.
291 media_file
= MediaFile
.query
.filter_by(media_entry
=self
.id,
292 name
=unicode(file_key
)).first()
294 file_metadata
= media_file
.file_metadata
or {}
296 for key
, value
in kwargs
.iteritems():
297 file_metadata
[key
] = value
299 media_file
.file_metadata
= file_metadata
303 def media_data(self
):
304 return getattr(self
, self
.media_data_ref
)
306 def media_data_init(self
, **kwargs
):
308 Initialize or update the contents of a media entry's media_data row
310 media_data
= self
.media_data
312 if media_data
is None:
313 # Get the correct table:
314 table
= import_component(self
.media_type
+ '.models:DATA_MODEL')
315 # No media data, so actually add a new one
316 media_data
= table(**kwargs
)
317 # Get the relationship set up.
318 media_data
.get_media_entry
= self
320 # Update old media data
321 for field
, value
in kwargs
.iteritems():
322 setattr(media_data
, field
, value
)
325 def media_data_ref(self
):
326 return import_component(self
.media_type
+ '.models:BACKREF_NAME')
329 safe_title
= self
.title
.encode('ascii', 'replace')
331 return '<{classname} {id}: {title}>'.format(
332 classname
=self
.__class
__.__name
__,
336 def delete(self
, del_orphan_tags
=True, **kwargs
):
337 """Delete MediaEntry and all related files/attachments/comments
339 This will *not* automatically delete unused collections, which
342 :param del_orphan_tags: True/false if we delete unused Tags too
343 :param commit: True/False if this should end the db transaction"""
344 # User's CollectionItems are automatically deleted via "cascade".
345 # Comments on this Media are deleted by cascade, hopefully.
347 # Delete all related files/attachments
349 delete_media_files(self
)
350 except OSError, error
:
351 # Returns list of files we failed to delete
352 _log
.error('No such files from the user "{1}" to delete: '
353 '{0}'.format(str(error
), self
.get_uploader
))
354 _log
.info('Deleted Media entry id "{0}"'.format(self
.id))
355 # Related MediaTag's are automatically cleaned, but we might
356 # want to clean out unused Tag's too.
358 # TODO: Import here due to cyclic imports!!!
359 # This cries for refactoring
360 from mediagoblin
.db
.util
import clean_orphan_tags
361 clean_orphan_tags(commit
=False)
362 # pass through commit=False/True in kwargs
363 super(MediaEntry
, self
).delete(**kwargs
)
366 class FileKeynames(Base
):
368 keywords for various places.
369 currently the MediaFile keys
371 __tablename__
= "core__file_keynames"
372 id = Column(Integer
, primary_key
=True)
373 name
= Column(Unicode
, unique
=True)
376 return "<FileKeyname %r: %r>" % (self
.id, self
.name
)
379 def find_or_new(cls
, name
):
380 t
= cls
.query
.filter_by(name
=name
).first()
383 return cls(name
=name
)
386 class MediaFile(Base
):
388 TODO: Highly consider moving "name" into a new table.
389 TODO: Consider preloading said table in software
391 __tablename__
= "core__mediafiles"
393 media_entry
= Column(
394 Integer
, ForeignKey(MediaEntry
.id),
396 name_id
= Column(SmallInteger
, ForeignKey(FileKeynames
.id), nullable
=False)
397 file_path
= Column(PathTupleWithSlashes
)
398 file_metadata
= Column(JSONEncoded
)
401 PrimaryKeyConstraint('media_entry', 'name_id'),
405 return "<MediaFile %s: %r>" % (self
.name
, self
.file_path
)
407 name_helper
= relationship(FileKeynames
, lazy
="joined", innerjoin
=True)
408 name
= association_proxy('name_helper', 'name',
409 creator
=FileKeynames
.find_or_new
413 class MediaAttachmentFile(Base
):
414 __tablename__
= "core__attachment_files"
416 id = Column(Integer
, primary_key
=True)
417 media_entry
= Column(
418 Integer
, ForeignKey(MediaEntry
.id),
420 name
= Column(Unicode
, nullable
=False)
421 filepath
= Column(PathTupleWithSlashes
)
422 created
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
)
426 """A dict like view on this object"""
427 return DictReadAttrProxy(self
)
431 __tablename__
= "core__tags"
433 id = Column(Integer
, primary_key
=True)
434 slug
= Column(Unicode
, nullable
=False, unique
=True)
437 return "<Tag %r: %r>" % (self
.id, self
.slug
)
440 def find_or_new(cls
, slug
):
441 t
= cls
.query
.filter_by(slug
=slug
).first()
444 return cls(slug
=slug
)
447 class MediaTag(Base
):
448 __tablename__
= "core__media_tags"
450 id = Column(Integer
, primary_key
=True)
451 media_entry
= Column(
452 Integer
, ForeignKey(MediaEntry
.id),
453 nullable
=False, index
=True)
454 tag
= Column(Integer
, ForeignKey(Tag
.id), nullable
=False, index
=True)
455 name
= Column(Unicode
)
456 # created = Column(DateTime, nullable=False, default=datetime.datetime.now)
459 UniqueConstraint('tag', 'media_entry'),
462 tag_helper
= relationship(Tag
)
463 slug
= association_proxy('tag_helper', 'slug',
464 creator
=Tag
.find_or_new
467 def __init__(self
, name
=None, slug
=None):
472 self
.tag_helper
= Tag
.find_or_new(slug
)
476 """A dict like view on this object"""
477 return DictReadAttrProxy(self
)
480 class MediaComment(Base
, MediaCommentMixin
):
481 __tablename__
= "core__media_comments"
483 id = Column(Integer
, primary_key
=True)
484 media_entry
= Column(
485 Integer
, ForeignKey(MediaEntry
.id), nullable
=False, index
=True)
486 author
= Column(Integer
, ForeignKey(User
.id), nullable
=False)
487 created
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
)
488 content
= Column(UnicodeText
, nullable
=False)
490 # Cascade: Comments are owned by their creator. So do the full thing.
491 # lazy=dynamic: People might post a *lot* of comments,
492 # so make the "posted_comments" a query-like thing.
493 get_author
= relationship(User
,
494 backref
=backref("posted_comments",
496 cascade
="all, delete-orphan"))
497 get_entry
= relationship(MediaEntry
,
498 backref
=backref("comments",
500 cascade
="all, delete-orphan"))
502 # Cascade: Comments are somewhat owned by their MediaEntry.
503 # So do the full thing.
504 # lazy=dynamic: MediaEntries might have many comments,
505 # so make the "all_comments" a query-like thing.
506 get_media_entry
= relationship(MediaEntry
,
507 backref
=backref("all_comments",
509 cascade
="all, delete-orphan"))
512 class Collection(Base
, CollectionMixin
):
513 """An 'album' or 'set' of media by a user.
515 On deletion, contained CollectionItems get automatically reaped via
517 __tablename__
= "core__collections"
519 id = Column(Integer
, primary_key
=True)
520 title
= Column(Unicode
, nullable
=False)
521 slug
= Column(Unicode
)
522 created
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
,
524 description
= Column(UnicodeText
)
525 creator
= Column(Integer
, ForeignKey(User
.id), nullable
=False)
526 # TODO: No of items in Collection. Badly named, can we migrate to num_items?
527 items
= Column(Integer
, default
=0)
529 # Cascade: Collections are owned by their creator. So do the full thing.
530 get_creator
= relationship(User
,
531 backref
=backref("collections",
532 cascade
="all, delete-orphan"))
535 UniqueConstraint('creator', 'slug'),
538 def get_collection_items(self
, ascending
=False):
539 #TODO, is this still needed with self.collection_items being available?
540 order_col
= CollectionItem
.position
542 order_col
= desc(order_col
)
543 return CollectionItem
.query
.filter_by(
544 collection
=self
.id).order_by(order_col
)
547 class CollectionItem(Base
, CollectionItemMixin
):
548 __tablename__
= "core__collection_items"
550 id = Column(Integer
, primary_key
=True)
551 media_entry
= Column(
552 Integer
, ForeignKey(MediaEntry
.id), nullable
=False, index
=True)
553 collection
= Column(Integer
, ForeignKey(Collection
.id), nullable
=False)
554 note
= Column(UnicodeText
, nullable
=True)
555 added
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
)
556 position
= Column(Integer
)
558 # Cascade: CollectionItems are owned by their Collection. So do the full thing.
559 in_collection
= relationship(Collection
,
562 cascade
="all, delete-orphan"))
564 get_media_entry
= relationship(MediaEntry
)
567 UniqueConstraint('collection', 'media_entry'),
572 """A dict like view on this object"""
573 return DictReadAttrProxy(self
)
576 class ProcessingMetaData(Base
):
577 __tablename__
= 'core__processing_metadata'
579 id = Column(Integer
, primary_key
=True)
580 media_entry_id
= Column(Integer
, ForeignKey(MediaEntry
.id), nullable
=False,
582 media_entry
= relationship(MediaEntry
,
583 backref
=backref('processing_metadata',
584 cascade
='all, delete-orphan'))
585 callback_url
= Column(Unicode
)
589 """A dict like view on this object"""
590 return DictReadAttrProxy(self
)
593 class CommentSubscription(Base
):
594 __tablename__
= 'core__comment_subscriptions'
595 id = Column(Integer
, primary_key
=True)
597 created
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
)
599 media_entry_id
= Column(Integer
, ForeignKey(MediaEntry
.id), nullable
=False)
600 media_entry
= relationship(MediaEntry
,
601 backref
=backref('comment_subscriptions',
602 cascade
='all, delete-orphan'))
604 user_id
= Column(Integer
, ForeignKey(User
.id), nullable
=False)
605 user
= relationship(User
,
606 backref
=backref('comment_subscriptions',
607 cascade
='all, delete-orphan'))
609 notify
= Column(Boolean
, nullable
=False, default
=True)
610 send_email
= Column(Boolean
, nullable
=False, default
=True)
613 return ('<{classname} #{id}: {user} {media} notify: '
614 '{notify} email: {email}>').format(
616 classname
=self
.__class
__.__name
__,
618 media
=self
.media_entry
,
620 email
=self
.send_email
)
623 class Notification(Base
):
624 __tablename__
= 'core__notifications'
625 id = Column(Integer
, primary_key
=True)
626 type = Column(Unicode
)
628 created
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
)
630 user_id
= Column(Integer
, ForeignKey('core__users.id'), nullable
=False,
632 seen
= Column(Boolean
, default
=lambda: False, index
=True)
635 backref
=backref('notifications', cascade
='all, delete-orphan'))
638 'polymorphic_identity': 'notification',
639 'polymorphic_on': type
643 return '<{klass} #{id}: {user}: {subject} ({seen})>'.format(
645 klass
=self
.__class
__.__name
__,
647 subject
=getattr(self
, 'subject', None),
648 seen
='unseen' if not self
.seen
else 'seen')
651 class CommentNotification(Notification
):
652 __tablename__
= 'core__comment_notifications'
653 id = Column(Integer
, ForeignKey(Notification
.id), primary_key
=True)
655 subject_id
= Column(Integer
, ForeignKey(MediaComment
.id))
656 subject
= relationship(
658 backref
=backref('comment_notifications', cascade
='all, delete-orphan'))
661 'polymorphic_identity': 'comment_notification'
665 class ProcessingNotification(Notification
):
666 __tablename__
= 'core__processing_notifications'
668 id = Column(Integer
, ForeignKey(Notification
.id), primary_key
=True)
670 subject_id
= Column(Integer
, ForeignKey(MediaEntry
.id))
671 subject
= relationship(
673 backref
=backref('processing_notifications',
674 cascade
='all, delete-orphan'))
677 'polymorphic_identity': 'processing_notification'
683 [ProcessingNotification
, CommentNotification
])
686 User
, Client
, RequestToken
, AccessToken
, NonceTimestamp
, MediaEntry
, Tag
,
687 MediaTag
, MediaComment
, Collection
, CollectionItem
, MediaFile
, FileKeynames
,
688 MediaAttachmentFile
, ProcessingMetaData
, Notification
, CommentNotification
,
689 ProcessingNotification
, CommentSubscription
]
692 Foundations are the default rows that are created immediately after the tables
693 are initialized. Each entry to this dictionary should be in the format of:
694 ModelConstructorObject:List of Dictionaries
695 (Each Dictionary represents a row on the Table to be created, containing each
696 of the columns' names as a key string, and each of the columns' values as a
699 ex. [NOTE THIS IS NOT BASED OFF OF OUR USER TABLE]
700 user_foundations = [{'name':u'Joanna', 'age':24},
701 {'name':u'Andrea', 'age':41}]
703 FOUNDATIONS = {User:user_foundations}
707 ######################################################
708 # Special, migrations-tracking table
710 # Not listed in MODELS because this is special and not
711 # really migrated, but used for migrations (for now)
712 ######################################################
714 class MigrationData(Base
):
715 __tablename__
= "core__migrations"
717 name
= Column(Unicode
, primary_key
=True)
718 version
= Column(Integer
, nullable
=False, default
=0)
720 ######################################################
723 def show_table_init(engine_uri
):
724 if engine_uri
is None:
725 engine_uri
= 'sqlite:///:memory:'
726 from sqlalchemy
import create_engine
727 engine
= create_engine(engine_uri
, echo
=True)
729 Base
.metadata
.create_all(engine
)
732 if __name__
== '__main__':