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