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