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