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/>.
20 from sqlalchemy
import (MetaData
, Table
, Column
, Boolean
, SmallInteger
,
21 Integer
, Unicode
, UnicodeText
, DateTime
,
23 from sqlalchemy
.exc
import ProgrammingError
24 from sqlalchemy
.ext
.declarative
import declarative_base
25 from sqlalchemy
.sql
import and_
26 from migrate
.changeset
.constraint
import UniqueConstraint
29 from mediagoblin
.db
.extratypes
import JSONEncoded
30 from mediagoblin
.db
.migration_tools
import RegisterMigration
, inspect_table
31 from mediagoblin
.db
.models
import MediaEntry
, Collection
, User
, MediaComment
36 @RegisterMigration(1, MIGRATIONS
)
37 def ogg_to_webm_audio(db_conn
):
38 metadata
= MetaData(bind
=db_conn
.bind
)
40 file_keynames
= Table('core__file_keynames', metadata
, autoload
=True,
41 autoload_with
=db_conn
.bind
)
44 file_keynames
.update().where(file_keynames
.c
.name
== 'ogg').
45 values(name
='webm_audio')
50 @RegisterMigration(2, MIGRATIONS
)
51 def add_wants_notification_column(db_conn
):
52 metadata
= MetaData(bind
=db_conn
.bind
)
54 users
= Table('core__users', metadata
, autoload
=True,
55 autoload_with
=db_conn
.bind
)
57 col
= Column('wants_comment_notification', Boolean
,
58 default
=True, nullable
=True)
59 col
.create(users
, populate_defaults
=True)
63 @RegisterMigration(3, MIGRATIONS
)
64 def add_transcoding_progress(db_conn
):
65 metadata
= MetaData(bind
=db_conn
.bind
)
67 media_entry
= inspect_table(metadata
, 'core__media_entries')
69 col
= Column('transcoding_progress', SmallInteger
)
70 col
.create(media_entry
)
74 class Collection_v0(declarative_base()):
75 __tablename__
= "core__collections"
77 id = Column(Integer
, primary_key
=True)
78 title
= Column(Unicode
, nullable
=False)
79 slug
= Column(Unicode
)
80 created
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
,
82 description
= Column(UnicodeText
)
83 creator
= Column(Integer
, ForeignKey(User
.id), nullable
=False)
84 items
= Column(Integer
, default
=0)
86 class CollectionItem_v0(declarative_base()):
87 __tablename__
= "core__collection_items"
89 id = Column(Integer
, primary_key
=True)
91 Integer
, ForeignKey(MediaEntry
.id), nullable
=False, index
=True)
92 collection
= Column(Integer
, ForeignKey(Collection
.id), nullable
=False)
93 note
= Column(UnicodeText
, nullable
=True)
94 added
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
)
95 position
= Column(Integer
)
97 ## This should be activated, normally.
98 ## But this would change the way the next migration used to work.
99 ## So it's commented for now.
101 UniqueConstraint('collection', 'media_entry'),
104 collectionitem_unique_constraint_done
= False
106 @RegisterMigration(4, MIGRATIONS
)
107 def add_collection_tables(db_conn
):
108 Collection_v0
.__table__
.create(db_conn
.bind
)
109 CollectionItem_v0
.__table__
.create(db_conn
.bind
)
111 global collectionitem_unique_constraint_done
112 collectionitem_unique_constraint_done
= True
117 @RegisterMigration(5, MIGRATIONS
)
118 def add_mediaentry_collected(db_conn
):
119 metadata
= MetaData(bind
=db_conn
.bind
)
121 media_entry
= inspect_table(metadata
, 'core__media_entries')
123 col
= Column('collected', Integer
, default
=0)
124 col
.create(media_entry
)
128 class ProcessingMetaData_v0(declarative_base()):
129 __tablename__
= 'core__processing_metadata'
131 id = Column(Integer
, primary_key
=True)
132 media_entry_id
= Column(Integer
, ForeignKey(MediaEntry
.id), nullable
=False,
134 callback_url
= Column(Unicode
)
136 @RegisterMigration(6, MIGRATIONS
)
137 def create_processing_metadata_table(db
):
138 ProcessingMetaData_v0
.__table__
.create(db
.bind
)
142 # Okay, problem being:
143 # Migration #4 forgot to add the uniqueconstraint for the
144 # new tables. While creating the tables from scratch had
145 # the constraint enabled.
147 # So we have four situations that should end up at the same
151 # Well, easy. Just uses the tables in models.py
152 # 2. Fresh install using a git version just before this migration
153 # The tables are all there, the unique constraint is also there.
154 # This migration should do nothing.
155 # But as we can't detect the uniqueconstraint easily,
156 # this migration just adds the constraint again.
157 # And possibly fails very loud. But ignores the failure.
158 # 3. old install, not using git, just releases.
159 # This one will get the new tables in #4 (now with constraint!)
160 # And this migration is just skipped silently.
161 # 4. old install, always on latest git.
162 # This one has the tables, but lacks the constraint.
163 # So this migration adds the constraint.
164 @RegisterMigration(7, MIGRATIONS
)
165 def fix_CollectionItem_v0_constraint(db_conn
):
166 """Add the forgotten Constraint on CollectionItem"""
168 global collectionitem_unique_constraint_done
169 if collectionitem_unique_constraint_done
:
170 # Reset it. Maybe the whole thing gets run again
171 # For a different db?
172 collectionitem_unique_constraint_done
= False
175 metadata
= MetaData(bind
=db_conn
.bind
)
177 CollectionItem_table
= inspect_table(metadata
, 'core__collection_items')
179 constraint
= UniqueConstraint('collection', 'media_entry',
180 name
='core__collection_items_collection_media_entry_key',
181 table
=CollectionItem_table
)
185 except ProgrammingError
:
186 # User probably has an install that was run since the
187 # collection tables were added, so we don't need to run this migration.
193 @RegisterMigration(8, MIGRATIONS
)
194 def add_license_preference(db
):
195 metadata
= MetaData(bind
=db
.bind
)
197 user_table
= inspect_table(metadata
, 'core__users')
199 col
= Column('license_preference', Unicode
)
200 col
.create(user_table
)
204 @RegisterMigration(9, MIGRATIONS
)
205 def mediaentry_new_slug_era(db
):
207 Update for the new era for media type slugs.
209 Entries without slugs now display differently in the url like:
212 ... because of this, we should back-convert:
213 - entries without slugs should be converted to use the id, if possible, to
214 make old urls still work
215 - slugs with = (or also : which is now also not allowed) to have those
216 stripped out (small possibility of breakage here sadly)
219 def slug_and_user_combo_exists(slug
, uploader
):
222 and_(media_table
.c
.uploader
==uploader
,
223 media_table
.c
.slug
==slug
))).first() is not None
225 def append_garbage_till_unique(row
, new_slug
):
227 Attach junk to this row until it's unique, then save it
229 if slug_and_user_combo_exists(new_slug
, row
.uploader
):
230 # okay, still no success;
231 # let's whack junk on there till it's unique.
232 new_slug
+= '-' + uuid
.uuid4().hex[:4]
233 # keep going if necessary!
234 while slug_and_user_combo_exists(new_slug
, row
.uploader
):
235 new_slug
+= uuid
.uuid4().hex[:4]
238 media_table
.update(). \
239 where(media_table
.c
.id==row
.id). \
240 values(slug
=new_slug
))
242 metadata
= MetaData(bind
=db
.bind
)
244 media_table
= inspect_table(metadata
, 'core__media_entries')
246 for row
in db
.execute(media_table
.select()):
247 # no slug, try setting to an id
249 append_garbage_till_unique(row
, unicode(row
.id))
250 # has "=" or ":" in it... we're getting rid of those
251 elif u
"=" in row
.slug
or u
":" in row
.slug
:
252 append_garbage_till_unique(
253 row
, row
.slug
.replace(u
"=", u
"-").replace(u
":", u
"-"))
258 @RegisterMigration(10, MIGRATIONS
)
259 def unique_collections_slug(db
):
260 """Add unique constraint to collection slug"""
261 metadata
= MetaData(bind
=db
.bind
)
262 collection_table
= inspect_table(metadata
, "core__collections")
266 for row
in db
.execute(collection_table
.select()):
267 # if duplicate slug, generate a unique slug
268 if row
.creator
in existing_slugs
and row
.slug
in \
269 existing_slugs
[row
.creator
]:
270 slugs_to_change
.append(row
.id)
272 if not row
.creator
in existing_slugs
:
273 existing_slugs
[row
.creator
] = [row
.slug
]
275 existing_slugs
[row
.creator
].append(row
.slug
)
277 for row_id
in slugs_to_change
:
278 new_slug
= unicode(uuid
.uuid4())
279 db
.execute(collection_table
.update().
280 where(collection_table
.c
.id == row_id
).
281 values(slug
=new_slug
))
282 # sqlite does not like to change the schema when a transaction(update) is
286 constraint
= UniqueConstraint('creator', 'slug',
287 name
='core__collection_creator_slug_key',
288 table
=collection_table
)
293 @RegisterMigration(11, MIGRATIONS
)
294 def drop_token_related_User_columns(db
):
296 Drop unneeded columns from the User table after switching to using
297 itsdangerous tokens for email and forgot password verification.
299 metadata
= MetaData(bind
=db
.bind
)
300 user_table
= inspect_table(metadata
, 'core__users')
302 verification_key
= user_table
.columns
['verification_key']
303 fp_verification_key
= user_table
.columns
['fp_verification_key']
304 fp_token_expire
= user_table
.columns
['fp_token_expire']
306 verification_key
.drop()
307 fp_verification_key
.drop()
308 fp_token_expire
.drop()
313 class CommentSubscription_v0(declarative_base()):
314 __tablename__
= 'core__comment_subscriptions'
315 id = Column(Integer
, primary_key
=True)
317 created
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
)
319 media_entry_id
= Column(Integer
, ForeignKey(MediaEntry
.id), nullable
=False)
321 user_id
= Column(Integer
, ForeignKey(User
.id), nullable
=False)
323 notify
= Column(Boolean
, nullable
=False, default
=True)
324 send_email
= Column(Boolean
, nullable
=False, default
=True)
327 class Notification_v0(declarative_base()):
328 __tablename__
= 'core__notifications'
329 id = Column(Integer
, primary_key
=True)
330 type = Column(Unicode
)
332 created
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
)
334 user_id
= Column(Integer
, ForeignKey(User
.id), nullable
=False,
336 seen
= Column(Boolean
, default
=lambda: False, index
=True)
339 class CommentNotification_v0(Notification_v0
):
340 __tablename__
= 'core__comment_notifications'
341 id = Column(Integer
, ForeignKey(Notification_v0
.id), primary_key
=True)
343 subject_id
= Column(Integer
, ForeignKey(MediaComment
.id))
346 class ProcessingNotification_v0(Notification_v0
):
347 __tablename__
= 'core__processing_notifications'
349 id = Column(Integer
, ForeignKey(Notification_v0
.id), primary_key
=True)
351 subject_id
= Column(Integer
, ForeignKey(MediaEntry
.id))
354 @RegisterMigration(12, MIGRATIONS
)
355 def add_new_notification_tables(db
):
356 metadata
= MetaData(bind
=db
.bind
)
358 user_table
= inspect_table(metadata
, 'core__users')
359 mediaentry_table
= inspect_table(metadata
, 'core__media_entries')
360 mediacomment_table
= inspect_table(metadata
, 'core__media_comments')
362 CommentSubscription_v0
.__table__
.create(db
.bind
)
364 Notification_v0
.__table__
.create(db
.bind
)
365 CommentNotification_v0
.__table__
.create(db
.bind
)
366 ProcessingNotification_v0
.__table__
.create(db
.bind
)
369 @RegisterMigration(13, MIGRATIONS
)
370 def pw_hash_nullable(db
):
371 """Make pw_hash column nullable"""
372 metadata
= MetaData(bind
=db
.bind
)
373 user_table
= inspect_table(metadata
, "core__users")
375 user_table
.c
.pw_hash
.alter(nullable
=True)
377 # sqlite+sqlalchemy seems to drop this constraint during the
378 # migration, so we add it back here for now a bit manually.
379 if db
.bind
.url
.drivername
== 'sqlite':
380 constraint
= UniqueConstraint('username', table
=user_table
)
387 class Client_v0(declarative_base()):
389 Model representing a client - Used for API Auth
391 __tablename__
= "core__clients"
393 id = Column(Unicode
, nullable
=True, primary_key
=True)
394 secret
= Column(Unicode
, nullable
=False)
395 expirey
= Column(DateTime
, nullable
=True)
396 application_type
= Column(Unicode
, nullable
=False)
397 created
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
)
398 updated
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
)
401 redirect_uri
= Column(JSONEncoded
, nullable
=True)
402 logo_url
= Column(Unicode
, nullable
=True)
403 application_name
= Column(Unicode
, nullable
=True)
404 contacts
= Column(JSONEncoded
, nullable
=True)
407 if self
.application_name
:
408 return "<Client {0} - {1}>".format(self
.application_name
, self
.id)
410 return "<Client {0}>".format(self
.id)
412 class RequestToken_v0(declarative_base()):
414 Model for representing the request tokens
416 __tablename__
= "core__request_tokens"
418 token
= Column(Unicode
, primary_key
=True)
419 secret
= Column(Unicode
, nullable
=False)
420 client
= Column(Unicode
, ForeignKey(Client_v0
.id))
421 user
= Column(Integer
, ForeignKey(User
.id), nullable
=True)
422 used
= Column(Boolean
, default
=False)
423 authenticated
= Column(Boolean
, default
=False)
424 verifier
= Column(Unicode
, nullable
=True)
425 callback
= Column(Unicode
, nullable
=False, default
=u
"oob")
426 created
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
)
427 updated
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
)
429 class AccessToken_v0(declarative_base()):
431 Model for representing the access tokens
433 __tablename__
= "core__access_tokens"
435 token
= Column(Unicode
, nullable
=False, primary_key
=True)
436 secret
= Column(Unicode
, nullable
=False)
437 user
= Column(Integer
, ForeignKey(User
.id))
438 request_token
= Column(Unicode
, ForeignKey(RequestToken_v0
.token
))
439 created
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
)
440 updated
= Column(DateTime
, nullable
=False, default
=datetime
.datetime
.now
)
443 class NonceTimestamp_v0(declarative_base()):
445 A place the timestamp and nonce can be stored - this is for OAuth1
447 __tablename__
= "core__nonce_timestamps"
449 nonce
= Column(Unicode
, nullable
=False, primary_key
=True)
450 timestamp
= Column(DateTime
, nullable
=False, primary_key
=True)
453 @RegisterMigration(14, MIGRATIONS
)
454 def create_oauth1_tables(db
):
455 """ Creates the OAuth1 tables """
457 Client_v0
.__table__
.create(db
.bind
)
458 RequestToken_v0
.__table__
.create(db
.bind
)
459 AccessToken_v0
.__table__
.create(db
.bind
)
460 NonceTimestamp_v0
.__table__
.create(db
.bind
)