Commit | Line | Data |
---|---|---|
70b44584 | 1 | # GNU MediaGoblin -- federated, autonomous media hosting |
b781c3c9 | 2 | # Copyright (C) 2011, 2012 MediaGoblin contributors. See AUTHORS. |
70b44584 CAW |
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 | ||
29fdd3bb | 17 | import datetime |
34d8bc98 | 18 | import uuid |
29fdd3bb | 19 | |
e49b7e02 BP |
20 | import six |
21 | ||
88a9662b | 22 | from sqlalchemy import (MetaData, Table, Column, Boolean, SmallInteger, |
316e1dfd | 23 | Integer, Unicode, UnicodeText, DateTime, |
892eed59 | 24 | ForeignKey, Date, Index) |
0f14c362 | 25 | from sqlalchemy.exc import ProgrammingError |
316e1dfd | 26 | from sqlalchemy.ext.declarative import declarative_base |
ab1f65e6 | 27 | from sqlalchemy.sql import and_ |
e2cb0f86 | 28 | from sqlalchemy.schema import UniqueConstraint |
b781c3c9 | 29 | |
42dbb26a | 30 | from mediagoblin.db.extratypes import JSONEncoded, MutationDict |
0c875e1e CAW |
31 | from mediagoblin.db.migration_tools import ( |
32 | RegisterMigration, inspect_table, replace_table_hack) | |
2b7b9de3 | 33 | from mediagoblin.db.models import (MediaEntry, Collection, MediaComment, User, |
d70b7a51 | 34 | Privilege) |
2dd966b5 | 35 | from mediagoblin.db.extratypes import JSONEncoded, MutationDict |
b781c3c9 | 36 | |
b781c3c9 | 37 | |
3ea1cf36 | 38 | MIGRATIONS = {} |
b781c3c9 JK |
39 | |
40 | ||
41 | @RegisterMigration(1, MIGRATIONS) | |
42 | def ogg_to_webm_audio(db_conn): | |
43 | metadata = MetaData(bind=db_conn.bind) | |
44 | ||
45 | file_keynames = Table('core__file_keynames', metadata, autoload=True, | |
46 | autoload_with=db_conn.bind) | |
47 | ||
48 | db_conn.execute( | |
38c6d441 | 49 | file_keynames.update().where(file_keynames.c.name == 'ogg'). |
b781c3c9 JK |
50 | values(name='webm_audio') |
51 | ) | |
b1055401 | 52 | db_conn.commit() |
38c6d441 JW |
53 | |
54 | ||
55 | @RegisterMigration(2, MIGRATIONS) | |
56 | def add_wants_notification_column(db_conn): | |
57 | metadata = MetaData(bind=db_conn.bind) | |
58 | ||
59 | users = Table('core__users', metadata, autoload=True, | |
60 | autoload_with=db_conn.bind) | |
61 | ||
62 | col = Column('wants_comment_notification', Boolean, | |
c4869eff | 63 | default=True, nullable=True) |
38c6d441 | 64 | col.create(users, populate_defaults=True) |
b1055401 | 65 | db_conn.commit() |
64712915 JW |
66 | |
67 | ||
68 | @RegisterMigration(3, MIGRATIONS) | |
69 | def add_transcoding_progress(db_conn): | |
70 | metadata = MetaData(bind=db_conn.bind) | |
71 | ||
c4466cb4 | 72 | media_entry = inspect_table(metadata, 'core__media_entries') |
64712915 JW |
73 | |
74 | col = Column('transcoding_progress', SmallInteger) | |
75 | col.create(media_entry) | |
76 | db_conn.commit() | |
be5be115 | 77 | |
88a9662b | 78 | |
316e1dfd E |
79 | class Collection_v0(declarative_base()): |
80 | __tablename__ = "core__collections" | |
81 | ||
82 | id = Column(Integer, primary_key=True) | |
83 | title = Column(Unicode, nullable=False) | |
84 | slug = Column(Unicode) | |
85 | created = Column(DateTime, nullable=False, default=datetime.datetime.now, | |
86 | index=True) | |
87 | description = Column(UnicodeText) | |
88 | creator = Column(Integer, ForeignKey(User.id), nullable=False) | |
89 | items = Column(Integer, default=0) | |
90 | ||
91 | class CollectionItem_v0(declarative_base()): | |
92 | __tablename__ = "core__collection_items" | |
93 | ||
94 | id = Column(Integer, primary_key=True) | |
95 | media_entry = Column( | |
96 | Integer, ForeignKey(MediaEntry.id), nullable=False, index=True) | |
97 | collection = Column(Integer, ForeignKey(Collection.id), nullable=False) | |
98 | note = Column(UnicodeText, nullable=True) | |
99 | added = Column(DateTime, nullable=False, default=datetime.datetime.now) | |
100 | position = Column(Integer) | |
101 | ||
102 | ## This should be activated, normally. | |
103 | ## But this would change the way the next migration used to work. | |
104 | ## So it's commented for now. | |
0f14c362 E |
105 | __table_args__ = ( |
106 | UniqueConstraint('collection', 'media_entry'), | |
107 | {}) | |
108 | ||
109 | collectionitem_unique_constraint_done = False | |
316e1dfd | 110 | |
be5be115 | 111 | @RegisterMigration(4, MIGRATIONS) |
29fdd3bb | 112 | def add_collection_tables(db_conn): |
316e1dfd E |
113 | Collection_v0.__table__.create(db_conn.bind) |
114 | CollectionItem_v0.__table__.create(db_conn.bind) | |
29fdd3bb | 115 | |
0f14c362 E |
116 | global collectionitem_unique_constraint_done |
117 | collectionitem_unique_constraint_done = True | |
118 | ||
29fdd3bb AW |
119 | db_conn.commit() |
120 | ||
88a9662b | 121 | |
29fdd3bb | 122 | @RegisterMigration(5, MIGRATIONS) |
59fb87c9 | 123 | def add_mediaentry_collected(db_conn): |
be5be115 AW |
124 | metadata = MetaData(bind=db_conn.bind) |
125 | ||
c4466cb4 | 126 | media_entry = inspect_table(metadata, 'core__media_entries') |
be5be115 | 127 | |
d8984df8 | 128 | col = Column('collected', Integer, default=0) |
be5be115 AW |
129 | col.create(media_entry) |
130 | db_conn.commit() | |
5354f954 JW |
131 | |
132 | ||
316e1dfd E |
133 | class ProcessingMetaData_v0(declarative_base()): |
134 | __tablename__ = 'core__processing_metadata' | |
939d57a0 | 135 | |
316e1dfd E |
136 | id = Column(Integer, primary_key=True) |
137 | media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=False, | |
138 | index=True) | |
139 | callback_url = Column(Unicode) | |
939d57a0 | 140 | |
316e1dfd E |
141 | @RegisterMigration(6, MIGRATIONS) |
142 | def create_processing_metadata_table(db): | |
143 | ProcessingMetaData_v0.__table__.create(db.bind) | |
5354f954 | 144 | db.commit() |
0f14c362 | 145 | |
ea91c183 E |
146 | |
147 | # Okay, problem being: | |
148 | # Migration #4 forgot to add the uniqueconstraint for the | |
149 | # new tables. While creating the tables from scratch had | |
150 | # the constraint enabled. | |
151 | # | |
a64abbb1 | 152 | # So we have four situations that should end up at the same |
ea91c183 E |
153 | # db layout: |
154 | # | |
155 | # 1. Fresh install. | |
156 | # Well, easy. Just uses the tables in models.py | |
157 | # 2. Fresh install using a git version just before this migration | |
158 | # The tables are all there, the unique constraint is also there. | |
159 | # This migration should do nothing. | |
160 | # But as we can't detect the uniqueconstraint easily, | |
161 | # this migration just adds the constraint again. | |
162 | # And possibly fails very loud. But ignores the failure. | |
163 | # 3. old install, not using git, just releases. | |
164 | # This one will get the new tables in #4 (now with constraint!) | |
165 | # And this migration is just skipped silently. | |
166 | # 4. old install, always on latest git. | |
167 | # This one has the tables, but lacks the constraint. | |
a64abbb1 | 168 | # So this migration adds the constraint. |
0f14c362 E |
169 | @RegisterMigration(7, MIGRATIONS) |
170 | def fix_CollectionItem_v0_constraint(db_conn): | |
171 | """Add the forgotten Constraint on CollectionItem""" | |
172 | ||
173 | global collectionitem_unique_constraint_done | |
174 | if collectionitem_unique_constraint_done: | |
0f14c362 E |
175 | # Reset it. Maybe the whole thing gets run again |
176 | # For a different db? | |
177 | collectionitem_unique_constraint_done = False | |
178 | return | |
179 | ||
180 | metadata = MetaData(bind=db_conn.bind) | |
181 | ||
c4466cb4 | 182 | CollectionItem_table = inspect_table(metadata, 'core__collection_items') |
0f14c362 E |
183 | |
184 | constraint = UniqueConstraint('collection', 'media_entry', | |
185 | name='core__collection_items_collection_media_entry_key', | |
186 | table=CollectionItem_table) | |
187 | ||
188 | try: | |
189 | constraint.create() | |
78fd5581 CAW |
190 | except ProgrammingError: |
191 | # User probably has an install that was run since the | |
192 | # collection tables were added, so we don't need to run this migration. | |
193 | pass | |
194 | ||
0f14c362 | 195 | db_conn.commit() |
dc4dfbde MH |
196 | |
197 | ||
198 | @RegisterMigration(8, MIGRATIONS) | |
199 | def add_license_preference(db): | |
200 | metadata = MetaData(bind=db.bind) | |
201 | ||
0c871f81 | 202 | user_table = inspect_table(metadata, 'core__users') |
dc4dfbde | 203 | |
0c871f81 | 204 | col = Column('license_preference', Unicode) |
dc4dfbde MH |
205 | col.create(user_table) |
206 | db.commit() | |
e66431f4 CAW |
207 | |
208 | ||
209 | @RegisterMigration(9, MIGRATIONS) | |
210 | def mediaentry_new_slug_era(db): | |
211 | """ | |
212 | Update for the new era for media type slugs. | |
213 | ||
214 | Entries without slugs now display differently in the url like: | |
215 | /u/cwebber/m/id=251/ | |
216 | ||
217 | ... because of this, we should back-convert: | |
218 | - entries without slugs should be converted to use the id, if possible, to | |
219 | make old urls still work | |
220 | - slugs with = (or also : which is now also not allowed) to have those | |
221 | stripped out (small possibility of breakage here sadly) | |
222 | """ | |
e66431f4 CAW |
223 | |
224 | def slug_and_user_combo_exists(slug, uploader): | |
e66431f4 CAW |
225 | return db.execute( |
226 | media_table.select( | |
ab1f65e6 | 227 | and_(media_table.c.uploader==uploader, |
aecd65b7 | 228 | media_table.c.slug==slug))).first() is not None |
e66431f4 CAW |
229 | |
230 | def append_garbage_till_unique(row, new_slug): | |
231 | """ | |
232 | Attach junk to this row until it's unique, then save it | |
233 | """ | |
234 | if slug_and_user_combo_exists(new_slug, row.uploader): | |
235 | # okay, still no success; | |
236 | # let's whack junk on there till it's unique. | |
237 | new_slug += '-' + uuid.uuid4().hex[:4] | |
238 | # keep going if necessary! | |
239 | while slug_and_user_combo_exists(new_slug, row.uploader): | |
240 | new_slug += uuid.uuid4().hex[:4] | |
241 | ||
242 | db.execute( | |
243 | media_table.update(). \ | |
244 | where(media_table.c.id==row.id). \ | |
245 | values(slug=new_slug)) | |
246 | ||
247 | metadata = MetaData(bind=db.bind) | |
248 | ||
249 | media_table = inspect_table(metadata, 'core__media_entries') | |
0b7cdb6f | 250 | |
e66431f4 CAW |
251 | for row in db.execute(media_table.select()): |
252 | # no slug, try setting to an id | |
253 | if not row.slug: | |
e49b7e02 | 254 | append_garbage_till_unique(row, six.text_type(row.id)) |
e66431f4 CAW |
255 | # has "=" or ":" in it... we're getting rid of those |
256 | elif u"=" in row.slug or u":" in row.slug: | |
257 | append_garbage_till_unique( | |
258 | row, row.slug.replace(u"=", u"-").replace(u":", u"-")) | |
0b7cdb6f CAW |
259 | |
260 | db.commit() | |
34d8bc98 RE |
261 | |
262 | ||
263 | @RegisterMigration(10, MIGRATIONS) | |
264 | def unique_collections_slug(db): | |
265 | """Add unique constraint to collection slug""" | |
266 | metadata = MetaData(bind=db.bind) | |
267 | collection_table = inspect_table(metadata, "core__collections") | |
268 | existing_slugs = {} | |
269 | slugs_to_change = [] | |
270 | ||
271 | for row in db.execute(collection_table.select()): | |
272 | # if duplicate slug, generate a unique slug | |
273 | if row.creator in existing_slugs and row.slug in \ | |
274 | existing_slugs[row.creator]: | |
275 | slugs_to_change.append(row.id) | |
276 | else: | |
277 | if not row.creator in existing_slugs: | |
278 | existing_slugs[row.creator] = [row.slug] | |
279 | else: | |
280 | existing_slugs[row.creator].append(row.slug) | |
281 | ||
282 | for row_id in slugs_to_change: | |
e49b7e02 | 283 | new_slug = six.text_type(uuid.uuid4()) |
34d8bc98 RE |
284 | db.execute(collection_table.update(). |
285 | where(collection_table.c.id == row_id). | |
286 | values(slug=new_slug)) | |
287 | # sqlite does not like to change the schema when a transaction(update) is | |
288 | # not yet completed | |
289 | db.commit() | |
290 | ||
291 | constraint = UniqueConstraint('creator', 'slug', | |
292 | name='core__collection_creator_slug_key', | |
293 | table=collection_table) | |
294 | constraint.create() | |
295 | ||
296 | db.commit() | |
8ad734af | 297 | |
8ad734af | 298 | @RegisterMigration(11, MIGRATIONS) |
342f06f7 RE |
299 | def drop_token_related_User_columns(db): |
300 | """ | |
301 | Drop unneeded columns from the User table after switching to using | |
302 | itsdangerous tokens for email and forgot password verification. | |
303 | """ | |
304 | metadata = MetaData(bind=db.bind) | |
305 | user_table = inspect_table(metadata, 'core__users') | |
306 | ||
307 | verification_key = user_table.columns['verification_key'] | |
308 | fp_verification_key = user_table.columns['fp_verification_key'] | |
309 | fp_token_expire = user_table.columns['fp_token_expire'] | |
310 | ||
311 | verification_key.drop() | |
312 | fp_verification_key.drop() | |
313 | fp_token_expire.drop() | |
314 | ||
315 | db.commit() | |
257b8ab6 | 316 | |
5adb906a | 317 | |
2d7b6bde JW |
318 | class CommentSubscription_v0(declarative_base()): |
319 | __tablename__ = 'core__comment_subscriptions' | |
320 | id = Column(Integer, primary_key=True) | |
321 | ||
322 | created = Column(DateTime, nullable=False, default=datetime.datetime.now) | |
323 | ||
324 | media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=False) | |
325 | ||
326 | user_id = Column(Integer, ForeignKey(User.id), nullable=False) | |
327 | ||
328 | notify = Column(Boolean, nullable=False, default=True) | |
329 | send_email = Column(Boolean, nullable=False, default=True) | |
330 | ||
331 | ||
332 | class Notification_v0(declarative_base()): | |
333 | __tablename__ = 'core__notifications' | |
334 | id = Column(Integer, primary_key=True) | |
335 | type = Column(Unicode) | |
336 | ||
337 | created = Column(DateTime, nullable=False, default=datetime.datetime.now) | |
338 | ||
339 | user_id = Column(Integer, ForeignKey(User.id), nullable=False, | |
340 | index=True) | |
341 | seen = Column(Boolean, default=lambda: False, index=True) | |
342 | ||
343 | ||
344 | class CommentNotification_v0(Notification_v0): | |
345 | __tablename__ = 'core__comment_notifications' | |
346 | id = Column(Integer, ForeignKey(Notification_v0.id), primary_key=True) | |
347 | ||
348 | subject_id = Column(Integer, ForeignKey(MediaComment.id)) | |
349 | ||
350 | ||
351 | class ProcessingNotification_v0(Notification_v0): | |
352 | __tablename__ = 'core__processing_notifications' | |
353 | ||
354 | id = Column(Integer, ForeignKey(Notification_v0.id), primary_key=True) | |
355 | ||
356 | subject_id = Column(Integer, ForeignKey(MediaEntry.id)) | |
357 | ||
358 | ||
257b8ab6 | 359 | @RegisterMigration(12, MIGRATIONS) |
2d7b6bde JW |
360 | def add_new_notification_tables(db): |
361 | metadata = MetaData(bind=db.bind) | |
362 | ||
363 | user_table = inspect_table(metadata, 'core__users') | |
364 | mediaentry_table = inspect_table(metadata, 'core__media_entries') | |
365 | mediacomment_table = inspect_table(metadata, 'core__media_comments') | |
366 | ||
367 | CommentSubscription_v0.__table__.create(db.bind) | |
368 | ||
369 | Notification_v0.__table__.create(db.bind) | |
370 | CommentNotification_v0.__table__.create(db.bind) | |
371 | ProcessingNotification_v0.__table__.create(db.bind) | |
af4414a8 | 372 | |
e8eec575 CAW |
373 | db.commit() |
374 | ||
af4414a8 RE |
375 | |
376 | @RegisterMigration(13, MIGRATIONS) | |
8ad734af RE |
377 | def pw_hash_nullable(db): |
378 | """Make pw_hash column nullable""" | |
379 | metadata = MetaData(bind=db.bind) | |
380 | user_table = inspect_table(metadata, "core__users") | |
381 | ||
382 | user_table.c.pw_hash.alter(nullable=True) | |
383 | ||
15db1831 CAW |
384 | # sqlite+sqlalchemy seems to drop this constraint during the |
385 | # migration, so we add it back here for now a bit manually. | |
5a1be074 | 386 | if db.bind.url.drivername == 'sqlite': |
e4deacd9 RE |
387 | constraint = UniqueConstraint('username', table=user_table) |
388 | constraint.create() | |
389 | ||
8ad734af | 390 | db.commit() |
8ddd7769 | 391 | |
392 | ||
7271b062 | 393 | # oauth1 migrations |
8e3bf978 | 394 | class Client_v0(declarative_base()): |
7271b062 | 395 | """ |
396 | Model representing a client - Used for API Auth | |
397 | """ | |
398 | __tablename__ = "core__clients" | |
399 | ||
400 | id = Column(Unicode, nullable=True, primary_key=True) | |
401 | secret = Column(Unicode, nullable=False) | |
402 | expirey = Column(DateTime, nullable=True) | |
403 | application_type = Column(Unicode, nullable=False) | |
404 | created = Column(DateTime, nullable=False, default=datetime.datetime.now) | |
405 | updated = Column(DateTime, nullable=False, default=datetime.datetime.now) | |
406 | ||
407 | # optional stuff | |
408 | redirect_uri = Column(JSONEncoded, nullable=True) | |
409 | logo_url = Column(Unicode, nullable=True) | |
410 | application_name = Column(Unicode, nullable=True) | |
411 | contacts = Column(JSONEncoded, nullable=True) | |
412 | ||
413 | def __repr__(self): | |
414 | if self.application_name: | |
415 | return "<Client {0} - {1}>".format(self.application_name, self.id) | |
416 | else: | |
417 | return "<Client {0}>".format(self.id) | |
418 | ||
8e3bf978 | 419 | class RequestToken_v0(declarative_base()): |
7271b062 | 420 | """ |
421 | Model for representing the request tokens | |
422 | """ | |
423 | __tablename__ = "core__request_tokens" | |
424 | ||
425 | token = Column(Unicode, primary_key=True) | |
426 | secret = Column(Unicode, nullable=False) | |
8e3bf978 | 427 | client = Column(Unicode, ForeignKey(Client_v0.id)) |
7271b062 | 428 | user = Column(Integer, ForeignKey(User.id), nullable=True) |
429 | used = Column(Boolean, default=False) | |
430 | authenticated = Column(Boolean, default=False) | |
431 | verifier = Column(Unicode, nullable=True) | |
432 | callback = Column(Unicode, nullable=False, default=u"oob") | |
433 | created = Column(DateTime, nullable=False, default=datetime.datetime.now) | |
434 | updated = Column(DateTime, nullable=False, default=datetime.datetime.now) | |
93d805ad | 435 | |
8e3bf978 | 436 | class AccessToken_v0(declarative_base()): |
7271b062 | 437 | """ |
438 | Model for representing the access tokens | |
439 | """ | |
440 | __tablename__ = "core__access_tokens" | |
441 | ||
442 | token = Column(Unicode, nullable=False, primary_key=True) | |
443 | secret = Column(Unicode, nullable=False) | |
444 | user = Column(Integer, ForeignKey(User.id)) | |
8e3bf978 | 445 | request_token = Column(Unicode, ForeignKey(RequestToken_v0.token)) |
7271b062 | 446 | created = Column(DateTime, nullable=False, default=datetime.datetime.now) |
447 | updated = Column(DateTime, nullable=False, default=datetime.datetime.now) | |
93d805ad | 448 | |
7271b062 | 449 | |
8e3bf978 | 450 | class NonceTimestamp_v0(declarative_base()): |
7271b062 | 451 | """ |
452 | A place the timestamp and nonce can be stored - this is for OAuth1 | |
453 | """ | |
454 | __tablename__ = "core__nonce_timestamps" | |
455 | ||
456 | nonce = Column(Unicode, nullable=False, primary_key=True) | |
457 | timestamp = Column(DateTime, nullable=False, primary_key=True) | |
458 | ||
459 | ||
8ddd7769 | 460 | @RegisterMigration(14, MIGRATIONS) |
461 | def create_oauth1_tables(db): | |
462 | """ Creates the OAuth1 tables """ | |
463 | ||
7271b062 | 464 | Client_v0.__table__.create(db.bind) |
465 | RequestToken_v0.__table__.create(db.bind) | |
466 | AccessToken_v0.__table__.create(db.bind) | |
467 | NonceTimestamp_v0.__table__.create(db.bind) | |
8ddd7769 | 468 | |
469 | db.commit() | |
93d805ad | 470 | |
93d805ad RE |
471 | @RegisterMigration(15, MIGRATIONS) |
472 | def wants_notifications(db): | |
473 | """Add a wants_notifications field to User model""" | |
474 | metadata = MetaData(bind=db.bind) | |
475 | user_table = inspect_table(metadata, "core__users") | |
93d805ad RE |
476 | col = Column('wants_notifications', Boolean, default=True) |
477 | col.create(user_table) | |
045fe0ee | 478 | db.commit() |
479 | ||
63866d80 CAW |
480 | |
481 | ||
482 | @RegisterMigration(16, MIGRATIONS) | |
483 | def upload_limits(db): | |
484 | """Add user upload limit columns""" | |
485 | metadata = MetaData(bind=db.bind) | |
486 | ||
487 | user_table = inspect_table(metadata, 'core__users') | |
488 | media_entry_table = inspect_table(metadata, 'core__media_entries') | |
489 | ||
490 | col = Column('uploaded', Integer, default=0) | |
491 | col.create(user_table) | |
492 | ||
493 | col = Column('upload_limit', Integer) | |
494 | col.create(user_table) | |
495 | ||
496 | col = Column('file_size', Integer, default=0) | |
497 | col.create(media_entry_table) | |
498 | ||
499 | db.commit() | |
500 | ||
501 | ||
502 | @RegisterMigration(17, MIGRATIONS) | |
503 | def add_file_metadata(db): | |
504 | """Add file_metadata to MediaFile""" | |
505 | metadata = MetaData(bind=db.bind) | |
506 | media_file_table = inspect_table(metadata, "core__mediafiles") | |
507 | ||
508 | col = Column('file_metadata', MutationDict.as_mutable(JSONEncoded)) | |
509 | col.create(media_file_table) | |
510 | ||
511 | db.commit() | |
512 | ||
513 | ################### | |
514 | # Moderation tables | |
515 | ################### | |
516 | ||
2c901db0 | 517 | class ReportBase_v0(declarative_base()): |
518 | __tablename__ = 'core__reports' | |
519 | id = Column(Integer, primary_key=True) | |
520 | reporter_id = Column(Integer, ForeignKey(User.id), nullable=False) | |
521 | report_content = Column(UnicodeText) | |
522 | reported_user_id = Column(Integer, ForeignKey(User.id), nullable=False) | |
dfd66b78 | 523 | created = Column(DateTime, nullable=False, default=datetime.datetime.now) |
2c901db0 | 524 | discriminator = Column('type', Unicode(50)) |
c9068870 | 525 | resolver_id = Column(Integer, ForeignKey(User.id)) |
526 | resolved = Column(DateTime) | |
527 | result = Column(UnicodeText) | |
2c901db0 | 528 | __mapper_args__ = {'polymorphic_on': discriminator} |
529 | ||
63866d80 | 530 | |
2c901db0 | 531 | class CommentReport_v0(ReportBase_v0): |
532 | __tablename__ = 'core__reports_on_comments' | |
533 | __mapper_args__ = {'polymorphic_identity': 'comment_report'} | |
534 | ||
535 | id = Column('id',Integer, ForeignKey('core__reports.id'), | |
536 | primary_key=True) | |
6483b370 | 537 | comment_id = Column(Integer, ForeignKey(MediaComment.id), nullable=True) |
2c901db0 | 538 | |
045fe0ee | 539 | |
2c901db0 | 540 | class MediaReport_v0(ReportBase_v0): |
541 | __tablename__ = 'core__reports_on_media' | |
542 | __mapper_args__ = {'polymorphic_identity': 'media_report'} | |
543 | ||
544 | id = Column('id',Integer, ForeignKey('core__reports.id'), primary_key=True) | |
6483b370 | 545 | media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=True) |
2c901db0 | 546 | |
63866d80 | 547 | |
2c901db0 | 548 | class UserBan_v0(declarative_base()): |
549 | __tablename__ = 'core__user_bans' | |
0a24db84 | 550 | user_id = Column(Integer, ForeignKey(User.id), nullable=False, |
2c901db0 | 551 | primary_key=True) |
1bb367f6 | 552 | expiration_date = Column(Date) |
2c901db0 | 553 | reason = Column(UnicodeText, nullable=False) |
554 | ||
63866d80 | 555 | |
2c901db0 | 556 | class Privilege_v0(declarative_base()): |
557 | __tablename__ = 'core__privileges' | |
558 | id = Column(Integer, nullable=False, primary_key=True, unique=True) | |
559 | privilege_name = Column(Unicode, nullable=False, unique=True) | |
560 | ||
63866d80 | 561 | |
2c901db0 | 562 | class PrivilegeUserAssociation_v0(declarative_base()): |
563 | __tablename__ = 'core__privileges_users' | |
9519c0a9 | 564 | privilege_id = Column( |
dfd66b78 | 565 | 'core__privilege_id', |
566 | Integer, | |
567 | ForeignKey(User.id), | |
2c901db0 | 568 | primary_key=True) |
569 | user_id = Column( | |
dfd66b78 | 570 | 'core__user_id', |
571 | Integer, | |
572 | ForeignKey(Privilege.id), | |
2c901db0 | 573 | primary_key=True) |
574 | ||
63866d80 | 575 | |
9519c0a9 | 576 | PRIVILEGE_FOUNDATIONS_v0 = [{'privilege_name':u'admin'}, |
63866d80 CAW |
577 | {'privilege_name':u'moderator'}, |
578 | {'privilege_name':u'uploader'}, | |
579 | {'privilege_name':u'reporter'}, | |
580 | {'privilege_name':u'commenter'}, | |
581 | {'privilege_name':u'active'}] | |
9519c0a9 | 582 | |
583 | ||
8ac78593 CAW |
584 | # vR1 stands for "version Rename 1". This only exists because we need |
585 | # to deal with dropping some booleans and it's otherwise impossible | |
586 | # with sqlite. | |
587 | ||
9519c0a9 | 588 | class User_vR1(declarative_base()): |
589 | __tablename__ = 'rename__users' | |
590 | id = Column(Integer, primary_key=True) | |
591 | username = Column(Unicode, nullable=False, unique=True) | |
592 | email = Column(Unicode, nullable=False) | |
593 | pw_hash = Column(Unicode) | |
594 | created = Column(DateTime, nullable=False, default=datetime.datetime.now) | |
595 | wants_comment_notification = Column(Boolean, default=True) | |
596 | wants_notifications = Column(Boolean, default=True) | |
597 | license_preference = Column(Unicode) | |
598 | url = Column(Unicode) | |
599 | bio = Column(UnicodeText) # ?? | |
a4609dd3 CAW |
600 | uploaded = Column(Integer, default=0) |
601 | upload_limit = Column(Integer) | |
9519c0a9 | 602 | |
63866d80 | 603 | |
9519c0a9 | 604 | @RegisterMigration(18, MIGRATIONS) |
2c901db0 | 605 | def create_moderation_tables(db): |
9519c0a9 | 606 | |
607 | # First, we will create the new tables in the database. | |
608 | #-------------------------------------------------------------------------- | |
2c901db0 | 609 | ReportBase_v0.__table__.create(db.bind) |
610 | CommentReport_v0.__table__.create(db.bind) | |
611 | MediaReport_v0.__table__.create(db.bind) | |
2c901db0 | 612 | UserBan_v0.__table__.create(db.bind) |
613 | Privilege_v0.__table__.create(db.bind) | |
614 | PrivilegeUserAssociation_v0.__table__.create(db.bind) | |
25625107 | 615 | |
2c901db0 | 616 | db.commit() |
617 | ||
9519c0a9 | 618 | # Then initialize the tables that we will later use |
619 | #-------------------------------------------------------------------------- | |
6acf4ee6 | 620 | metadata = MetaData(bind=db.bind) |
9519c0a9 | 621 | privileges_table= inspect_table(metadata, "core__privileges") |
6acf4ee6 | 622 | user_table = inspect_table(metadata, 'core__users') |
9519c0a9 | 623 | user_privilege_assoc = inspect_table( |
624 | metadata, 'core__privileges_users') | |
625 | ||
626 | # This section initializes the default Privilege foundations, that | |
627 | # would be created through the FOUNDATIONS system in a new instance | |
628 | #-------------------------------------------------------------------------- | |
629 | for parameters in PRIVILEGE_FOUNDATIONS_v0: | |
630 | db.execute(privileges_table.insert().values(**parameters)) | |
631 | ||
6acf4ee6 | 632 | db.commit() |
633 | ||
9519c0a9 | 634 | # This next section takes the information from the old is_admin and status |
635 | # columns and converts those to the new privilege system | |
636 | #-------------------------------------------------------------------------- | |
637 | admin_users_ids, active_users_ids, inactive_users_ids = ( | |
638 | db.execute( | |
639 | user_table.select().where( | |
f1318b59 | 640 | user_table.c.is_admin==True)).fetchall(), |
9519c0a9 | 641 | db.execute( |
642 | user_table.select().where( | |
f1318b59 | 643 | user_table.c.is_admin==False).where( |
9519c0a9 | 644 | user_table.c.status==u"active")).fetchall(), |
645 | db.execute( | |
646 | user_table.select().where( | |
f1318b59 | 647 | user_table.c.is_admin==False).where( |
9519c0a9 | 648 | user_table.c.status!=u"active")).fetchall()) |
649 | ||
650 | # Get the ids for each of the privileges so we can reference them ~~~~~~~~~ | |
651 | (admin_privilege_id, uploader_privilege_id, | |
652 | reporter_privilege_id, commenter_privilege_id, | |
653 | active_privilege_id) = [ | |
654 | db.execute(privileges_table.select().where( | |
655 | privileges_table.c.privilege_name==privilege_name)).first()['id'] | |
656 | for privilege_name in | |
657 | [u"admin",u"uploader",u"reporter",u"commenter",u"active"] | |
658 | ] | |
659 | ||
660 | # Give each user the appopriate privileges depending whether they are an | |
0b1fcaeb | 661 | # admin, an active user or an inactive user ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |
9519c0a9 | 662 | for admin_user in admin_users_ids: |
663 | admin_user_id = admin_user['id'] | |
2b7b9de3 JT |
664 | for privilege_id in [admin_privilege_id, uploader_privilege_id, |
665 | reporter_privilege_id, commenter_privilege_id, | |
0b1fcaeb | 666 | active_privilege_id]: |
9519c0a9 | 667 | db.execute(user_privilege_assoc.insert().values( |
668 | core__privilege_id=admin_user_id, | |
669 | core__user_id=privilege_id)) | |
670 | ||
671 | for active_user in active_users_ids: | |
672 | active_user_id = active_user['id'] | |
2b7b9de3 | 673 | for privilege_id in [uploader_privilege_id, reporter_privilege_id, |
0b1fcaeb | 674 | commenter_privilege_id, active_privilege_id]: |
9519c0a9 | 675 | db.execute(user_privilege_assoc.insert().values( |
676 | core__privilege_id=active_user_id, | |
677 | core__user_id=privilege_id)) | |
678 | ||
679 | for inactive_user in inactive_users_ids: | |
680 | inactive_user_id = inactive_user['id'] | |
2b7b9de3 | 681 | for privilege_id in [uploader_privilege_id, reporter_privilege_id, |
0b1fcaeb | 682 | commenter_privilege_id]: |
9519c0a9 | 683 | db.execute(user_privilege_assoc.insert().values( |
684 | core__privilege_id=inactive_user_id, | |
685 | core__user_id=privilege_id)) | |
686 | ||
687 | db.commit() | |
688 | ||
e5196ff0 | 689 | # And then, once the information is taken from is_admin & status columns |
9519c0a9 | 690 | # we drop all of the vestigial columns from the User table. |
691 | #-------------------------------------------------------------------------- | |
692 | if db.bind.url.drivername == 'sqlite': | |
693 | # SQLite has some issues that make it *impossible* to drop boolean | |
694 | # columns. So, the following code is a very hacky workaround which | |
695 | # makes it possible. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | |
696 | ||
697 | User_vR1.__table__.create(db.bind) | |
698 | db.commit() | |
699 | new_user_table = inspect_table(metadata, 'rename__users') | |
0c875e1e | 700 | replace_table_hack(db, user_table, new_user_table) |
9519c0a9 | 701 | else: |
0b1fcaeb | 702 | # If the db is not run using SQLite, this process is much simpler ~~~~~ |
9519c0a9 | 703 | |
704 | status = user_table.columns['status'] | |
705 | email_verified = user_table.columns['email_verified'] | |
706 | is_admin = user_table.columns['is_admin'] | |
707 | status.drop() | |
708 | email_verified.drop() | |
709 | is_admin.drop() | |
93d805ad | 710 | |
7dfcc538 | 711 | db.commit() |
70bceff8 | 712 | |
c56a88b4 | 713 | |
7dfcc538 J |
714 | @RegisterMigration(19, MIGRATIONS) |
715 | def drop_MediaEntry_collected(db): | |
716 | """ | |
717 | Drop unused MediaEntry.collected column | |
718 | """ | |
719 | metadata = MetaData(bind=db.bind) | |
7dfcc538 | 720 | |
9412fffe | 721 | media_collected= inspect_table(metadata, 'core__media_entries') |
7dfcc538 J |
722 | media_collected = media_collected.columns['collected'] |
723 | ||
7dfcc538 | 724 | media_collected.drop() |
9412fffe | 725 | |
93d805ad | 726 | db.commit() |
2dd966b5 | 727 | |
70bceff8 | 728 | |
2dd966b5 | 729 | @RegisterMigration(20, MIGRATIONS) |
9f3dc83a | 730 | def add_metadata_column(db): |
2dd966b5 | 731 | metadata = MetaData(bind=db.bind) |
732 | ||
9f3dc83a | 733 | media_entry = inspect_table(metadata, 'core__media_entries') |
2dd966b5 | 734 | |
c8abeb58 | 735 | col = Column('media_metadata', MutationDict.as_mutable(JSONEncoded), |
736 | default=MutationDict()) | |
9f3dc83a | 737 | col.create(media_entry) |
2dd966b5 | 738 | |
739 | db.commit() | |
70bceff8 CAW |
740 | |
741 | ||
7918f86a | 742 | class PrivilegeUserAssociation_R1(declarative_base()): |
743 | __tablename__ = 'rename__privileges_users' | |
c56a88b4 | 744 | user = Column( |
987a6351 | 745 | "user", |
7918f86a | 746 | Integer, |
747 | ForeignKey(User.id), | |
748 | primary_key=True) | |
c56a88b4 | 749 | privilege = Column( |
987a6351 | 750 | "privilege", |
7918f86a | 751 | Integer, |
752 | ForeignKey(Privilege.id), | |
753 | primary_key=True) | |
754 | ||
70bceff8 | 755 | @RegisterMigration(21, MIGRATIONS) |
7918f86a | 756 | def fix_privilege_user_association_table(db): |
757 | """ | |
758 | There was an error in the PrivilegeUserAssociation table that allowed for a | |
759 | dangerous sql error. We need to the change the name of the columns to be | |
9adef07e | 760 | unique, and properly referenced. |
7918f86a | 761 | """ |
762 | metadata = MetaData(bind=db.bind) | |
763 | ||
764 | privilege_user_assoc = inspect_table( | |
765 | metadata, 'core__privileges_users') | |
7918f86a | 766 | |
713dde5b | 767 | # This whole process is more complex if we're dealing with sqlite |
c56a88b4 CAW |
768 | if db.bind.url.drivername == 'sqlite': |
769 | PrivilegeUserAssociation_R1.__table__.create(db.bind) | |
770 | db.commit() | |
771 | ||
772 | new_privilege_user_assoc = inspect_table( | |
773 | metadata, 'rename__privileges_users') | |
774 | result = db.execute(privilege_user_assoc.select()) | |
775 | for row in result: | |
776 | # The columns were improperly named before, so we switch the columns | |
777 | user_id, priv_id = row['core__privilege_id'], row['core__user_id'] | |
778 | db.execute(new_privilege_user_assoc.insert().values( | |
779 | user=user_id, | |
780 | privilege=priv_id)) | |
7918f86a | 781 | |
c56a88b4 CAW |
782 | db.commit() |
783 | ||
784 | privilege_user_assoc.drop() | |
785 | new_privilege_user_assoc.rename('core__privileges_users') | |
7918f86a | 786 | |
713dde5b | 787 | # much simpler if postgres though! |
c56a88b4 | 788 | else: |
987a6351 CAW |
789 | privilege_user_assoc.c.core__user_id.alter(name="privilege") |
790 | privilege_user_assoc.c.core__privilege_id.alter(name="user") | |
7918f86a | 791 | |
792 | db.commit() | |
892eed59 | 793 | |
bb12fb80 | 794 | |
892eed59 JT |
795 | @RegisterMigration(22, MIGRATIONS) |
796 | def add_index_username_field(db): | |
797 | """ | |
3a8d0e14 CAW |
798 | This migration has been found to be doing the wrong thing. See |
799 | the documentation in migration 23 (revert_username_index) below | |
800 | which undoes this for those databases that did run this migration. | |
892eed59 | 801 | |
3a8d0e14 CAW |
802 | Old description: |
803 | This indexes the User.username field which is frequently queried | |
804 | for example a user logging in. This solves the issue #894 | |
805 | """ | |
806 | ## This code is left commented out *on purpose!* | |
807 | ## | |
808 | ## We do not normally allow commented out code like this in | |
809 | ## MediaGoblin but this is a special case: since this migration has | |
810 | ## been nullified but with great work to set things back below, | |
811 | ## this is commented out for historical clarity. | |
812 | # | |
813 | # metadata = MetaData(bind=db.bind) | |
814 | # user_table = inspect_table(metadata, "core__users") | |
815 | # | |
816 | # new_index = Index("ix_core__users_uploader", user_table.c.username) | |
817 | # new_index.create() | |
818 | # | |
819 | # db.commit() | |
820 | pass | |
bb12fb80 CAW |
821 | |
822 | ||
823 | @RegisterMigration(23, MIGRATIONS) | |
824 | def revert_username_index(db): | |
825 | """ | |
3a8d0e14 CAW |
826 | Revert the stuff we did in migration 22 above. |
827 | ||
828 | There were a couple of problems with what we did: | |
829 | - There was never a need for this migration! The unique | |
830 | constraint had an implicit b-tree index, so it wasn't really | |
831 | needed. (This is my (Chris Webber's) fault for suggesting it | |
832 | needed to happen without knowing what's going on... my bad!) | |
833 | - On top of that, databases created after the models.py was | |
834 | changed weren't the same as those that had been run through | |
835 | migration 22 above. | |
836 | ||
837 | As such, we're setting things back to the way they were before, | |
838 | but as it turns out, that's tricky to do! | |
bb12fb80 CAW |
839 | """ |
840 | metadata = MetaData(bind=db.bind) | |
841 | user_table = inspect_table(metadata, "core__users") | |
a7800e6d CAW |
842 | indexes = dict( |
843 | [(index.name, index) for index in user_table.indexes]) | |
bb12fb80 | 844 | |
f2a6db90 CAW |
845 | # index from unnecessary migration |
846 | users_uploader_index = indexes.get(u'ix_core__users_uploader') | |
847 | # index created from models.py after (unique=True, index=True) | |
848 | # was set in models.py | |
849 | users_username_index = indexes.get(u'ix_core__users_username') | |
850 | ||
113d1a28 | 851 | if users_uploader_index is None and users_username_index is None: |
bb12fb80 CAW |
852 | # We don't need to do anything. |
853 | # The database isn't in a state where it needs fixing | |
854 | # | |
855 | # (ie, either went through the previous borked migration or | |
856 | # was initialized with a models.py where core__users was both | |
857 | # unique=True and index=True) | |
858 | return | |
859 | ||
860 | if db.bind.url.drivername == 'sqlite': | |
861 | # Again, sqlite has problems. So this is tricky. | |
862 | ||
863 | # Yes, this is correct to use User_vR1! Nothing has changed | |
864 | # between the *correct* version of this table and migration 18. | |
865 | User_vR1.__table__.create(db.bind) | |
866 | db.commit() | |
867 | new_user_table = inspect_table(metadata, 'rename__users') | |
868 | replace_table_hack(db, user_table, new_user_table) | |
869 | ||
870 | else: | |
e6288a68 CAW |
871 | # If the db is not run using SQLite, we don't need to do crazy |
872 | # table copying. | |
bb12fb80 CAW |
873 | |
874 | # Remove whichever of the not-used indexes are in place | |
3b104bbc | 875 | if users_uploader_index is not None: |
f2a6db90 | 876 | users_uploader_index.drop() |
3b104bbc | 877 | if users_username_index is not None: |
f2a6db90 | 878 | users_username_index.drop() |
1de794c6 CAW |
879 | |
880 | # Given we're removing indexes then adding a unique constraint | |
881 | # which *we know might fail*, thus probably rolling back the | |
882 | # session, let's commit here. | |
bb12fb80 CAW |
883 | db.commit() |
884 | ||
e6288a68 CAW |
885 | try: |
886 | # Add the unique constraint | |
887 | constraint = UniqueConstraint( | |
888 | 'username', table=user_table) | |
889 | constraint.create() | |
890 | except ProgrammingError: | |
891 | # constraint already exists, no need to add | |
ed0b981e | 892 | db.rollback() |
bb12fb80 CAW |
893 | |
894 | db.commit() |