diff options
| -rw-r--r-- | mediagoblin/db/migrations.py | 126 | ||||
| -rw-r--r-- | mediagoblin/db/models.py | 40 |
2 files changed, 146 insertions, 20 deletions
diff --git a/mediagoblin/db/migrations.py b/mediagoblin/db/migrations.py index 0e0ee6be..3102801e 100644 --- a/mediagoblin/db/migrations.py +++ b/mediagoblin/db/migrations.py @@ -22,6 +22,8 @@ import six if six.PY2: import migrate +import pytz +import dateutil.tz from sqlalchemy import (MetaData, Table, Column, Boolean, SmallInteger, Integer, Unicode, UnicodeText, DateTime, ForeignKey, Date, Index) @@ -1123,3 +1125,127 @@ def add_location_model(db): col.create(media_comments) db.commit() + +@RegisterMigration(26, MIGRATIONS) +def datetime_to_utc(db): + """ Convert datetime stamps to UTC """ + # Get the server's timezone, this is what the database has stored + server_timezone = dateutil.tz.tzlocal() + + ## + # Look up all the timestamps and convert them to UTC + ## + metadata = MetaData(bind=db.bind) + + def dt_to_utc(dt): + # Add the current timezone + dt = dt.replace(tzinfo=server_timezone) + + # Convert to UTC + return dt.astimezone(pytz.UTC) + + # Convert the User model + user_table = inspect_table(metadata, "core__users") + for user in db.execute(user_table.select()): + db.execute(user_table.update().values( + created=dt_to_utc(user.created) + ).where(user_table.c.id==user.id)) + + # Convert Client + client_table = inspect_table(metadata, "core__clients") + for client in db.execute(client_table.select()): + db.execute(client_table.update().values( + created=dt_to_utc(client.created), + updated=dt_to_utc(client.updated) + ).where(client_table.c.id==client.id)) + + # Convert RequestToken + rt_table = inspect_table(metadata, "core__request_tokens") + for request_token in db.execute(rt_table.select()): + db.execute(client_table.update().values( + created=dt_to_utc(request_token.created), + updated=dt_to_utc(request_token.updated) + ).where(rt_table.c.id==request_token.id)) + + # Convert AccessToken + at_table = inspect_table(metadata, "core__access_tokens") + for access_token in db.execute(at_table.select()): + db.execute(at_table.update().values( + created=dt_to_utc(acess_token.created), + updated=dt_to_utc(access_token.updated) + ).where(at_table.c.id==access_token.id)) + + # Convert MediaEntry + media_table = inspect_table(metadata, "core__media_entries") + for media in db.execute(media_table.select()): + db.execute(media_table.update().values( + created=dt_to_utc(media.created) + ).where(media_table.c.id==media.id)) + + # Convert Media Attachment File + media_attachment_table = inspect_table(metadata, "core__attachment_files") + for ma in db.execute(media_attachment_table.select()): + db.execute(media_attachment_table.update().values( + created=dt_to_utc(ma.created) + ).where(media_attachment_table.c.id==ma.id)) + + # Convert MediaComment + comment_table = inspect_table(metadata, "core__media_comments") + for comment in db.execute(comment_table.select()): + db.execute(comment_table.update().values( + created=dt_to_utc(comment.created) + ).where(comment_table.c.id==comment.id)) + + # Convert Collection + collection_table = inspect_table(metadata, "core__collections") + for collection in db.execute(collection_table.select()): + db.execute(collection_table.update().values( + created=dt_to_utc(collection.created) + ).where(collection_table.c.id==collection.id)) + + # Convert Collection Item + collection_item_table = inspect_table(metadata, "core__collection_items") + for ci in db.execute(collection_item_table.select()): + db.execute(collection_item_table.update().values( + added=dt_to_utc(ci.added) + ).where(collection_item_table.c.id==ci.id)) + + # Convert Comment subscription + comment_sub = inspect_table(metadata, "core__comment_subscriptions") + for sub in db.execute(comment_sub.select()): + db.execute(comment_sub.update().values( + created=dt_to_utc(sub.created) + ).where(comment_sub.c.id==sub.id)) + + # Convert Notification + notification_table = inspect_table(metadata, "core__notifications") + for notification in db.execute(notification_table.select()): + db.execute(notifiction_table.update().values( + created=db_to_utc(notification.created) + ).where(notification_table.c.id==notification.id)) + + # Convert ReportBase + reportbase_table = inspect_table(metadata, "core__reports") + for report in db.execute(reportbase_table.select()): + db.execute(reportbase_table.update().values( + created=dt_to_utc(report.created) + ).where(reportbase_table.c.id==report.id)) + + # Convert Generator + generator_table = inspect_table(metadata, "core__generators") + for generator in db.execute(generator_table.select()): + db.execute(generator_table.update().values( + published=dt_to_utc(generator.published), + updated=dt_to_utc(generator.updated) + ).where(generator_table.c.id==generator.id)) + + # Convert Activity + activity_table = inspect_table(metadata, "core__activities") + for activity in db.execute(activity_table.select()): + db.execute(activity_table.update().values( + published=dt_to_utc(activity.published), + updated=dt_to_utc(activity.updated) + ).where(activity_table.c.id==activity.id)) + + # Commit this to the database + db.commit() diff --git a/mediagoblin/db/models.py b/mediagoblin/db/models.py index 96123cf6..e8fb17a7 100644 --- a/mediagoblin/db/models.py +++ b/mediagoblin/db/models.py @@ -136,7 +136,7 @@ class User(Base, UserMixin): # point. email = Column(Unicode, nullable=False) pw_hash = Column(Unicode) - created = Column(DateTime, nullable=False, default=datetime.datetime.now) + created = Column(DateTime, nullable=False, default=datetime.datetime.utcnow) # Intented to be nullable=False, but migrations would not work for it # set to nullable=True implicitly. wants_comment_notification = Column(Boolean, default=True) @@ -276,8 +276,8 @@ class Client(Base): secret = Column(Unicode, nullable=False) expirey = Column(DateTime, nullable=True) application_type = Column(Unicode, nullable=False) - created = Column(DateTime, nullable=False, default=datetime.datetime.now) - updated = Column(DateTime, nullable=False, default=datetime.datetime.now) + created = Column(DateTime, nullable=False, default=datetime.datetime.utcnow) + updated = Column(DateTime, nullable=False, default=datetime.datetime.utcnow) # optional stuff redirect_uri = Column(JSONEncoded, nullable=True) @@ -305,8 +305,8 @@ class RequestToken(Base): authenticated = Column(Boolean, default=False) verifier = Column(Unicode, nullable=True) callback = Column(Unicode, nullable=False, default=u"oob") - created = Column(DateTime, nullable=False, default=datetime.datetime.now) - updated = Column(DateTime, nullable=False, default=datetime.datetime.now) + created = Column(DateTime, nullable=False, default=datetime.datetime.utcnow) + updated = Column(DateTime, nullable=False, default=datetime.datetime.utcnow) get_client = relationship(Client) @@ -320,8 +320,8 @@ class AccessToken(Base): secret = Column(Unicode, nullable=False) user = Column(Integer, ForeignKey(User.id)) request_token = Column(Unicode, ForeignKey(RequestToken.token)) - created = Column(DateTime, nullable=False, default=datetime.datetime.now) - updated = Column(DateTime, nullable=False, default=datetime.datetime.now) + created = Column(DateTime, nullable=False, default=datetime.datetime.utcnow) + updated = Column(DateTime, nullable=False, default=datetime.datetime.utcnow) get_requesttoken = relationship(RequestToken) @@ -345,7 +345,7 @@ class MediaEntry(Base, MediaEntryMixin): uploader = Column(Integer, ForeignKey(User.id), nullable=False, index=True) title = Column(Unicode, nullable=False) slug = Column(Unicode) - created = Column(DateTime, nullable=False, default=datetime.datetime.now, + created = Column(DateTime, nullable=False, default=datetime.datetime.utcnow, index=True) description = Column(UnicodeText) # ?? media_type = Column(Unicode, nullable=False) @@ -681,7 +681,7 @@ class MediaAttachmentFile(Base): nullable=False) name = Column(Unicode, nullable=False) filepath = Column(PathTupleWithSlashes) - created = Column(DateTime, nullable=False, default=datetime.datetime.now) + created = Column(DateTime, nullable=False, default=datetime.datetime.utcnow) @property def dict_view(self): @@ -715,7 +715,7 @@ class MediaTag(Base): nullable=False, index=True) tag = Column(Integer, ForeignKey(Tag.id), nullable=False, index=True) name = Column(Unicode) - # created = Column(DateTime, nullable=False, default=datetime.datetime.now) + # created = Column(DateTime, nullable=False, default=datetime.datetime.utcnow) __table_args__ = ( UniqueConstraint('tag', 'media_entry'), @@ -746,7 +746,7 @@ class MediaComment(Base, MediaCommentMixin): media_entry = Column( Integer, ForeignKey(MediaEntry.id), nullable=False, index=True) author = Column(Integer, ForeignKey(User.id), nullable=False) - created = Column(DateTime, nullable=False, default=datetime.datetime.now) + created = Column(DateTime, nullable=False, default=datetime.datetime.utcnow) content = Column(UnicodeText, nullable=False) location = Column(Integer, ForeignKey("core__locations.id")) get_location = relationship("Location", lazy="joined") @@ -840,7 +840,7 @@ class Collection(Base, CollectionMixin): id = Column(Integer, primary_key=True) title = Column(Unicode, nullable=False) slug = Column(Unicode) - created = Column(DateTime, nullable=False, default=datetime.datetime.now, + created = Column(DateTime, nullable=False, default=datetime.datetime.utcnow, index=True) description = Column(UnicodeText) creator = Column(Integer, ForeignKey(User.id), nullable=False) @@ -898,7 +898,7 @@ class CollectionItem(Base, CollectionItemMixin): Integer, ForeignKey(MediaEntry.id), nullable=False, index=True) collection = Column(Integer, ForeignKey(Collection.id), nullable=False) note = Column(UnicodeText, nullable=True) - added = Column(DateTime, nullable=False, default=datetime.datetime.now) + added = Column(DateTime, nullable=False, default=datetime.datetime.utcnow) position = Column(Integer) # Cascade: CollectionItems are owned by their Collection. So do the full thing. @@ -950,7 +950,7 @@ class CommentSubscription(Base): __tablename__ = 'core__comment_subscriptions' id = Column(Integer, primary_key=True) - created = Column(DateTime, nullable=False, default=datetime.datetime.now) + created = Column(DateTime, nullable=False, default=datetime.datetime.utcnow) media_entry_id = Column(Integer, ForeignKey(MediaEntry.id), nullable=False) media_entry = relationship(MediaEntry, @@ -981,7 +981,7 @@ class Notification(Base): id = Column(Integer, primary_key=True) type = Column(Unicode) - created = Column(DateTime, nullable=False, default=datetime.datetime.now) + created = Column(DateTime, nullable=False, default=datetime.datetime.utcnow) user_id = Column(Integer, ForeignKey('core__users.id'), nullable=False, index=True) @@ -1087,7 +1087,7 @@ class ReportBase(Base): lazy="dynamic", cascade="all, delete-orphan"), primaryjoin="User.id==ReportBase.reported_user_id") - created = Column(DateTime, nullable=False, default=datetime.datetime.now()) + created = Column(DateTime, nullable=False, default=datetime.datetime.utcnow) discriminator = Column('type', Unicode(50)) resolver_id = Column(Integer, ForeignKey(User.id)) resolver = relationship( @@ -1231,8 +1231,8 @@ class Generator(Base): id = Column(Integer, primary_key=True) name = Column(Unicode, nullable=False) - published = Column(DateTime, default=datetime.datetime.now) - updated = Column(DateTime, default=datetime.datetime.now) + published = Column(DateTime, default=datetime.datetime.utcnow) + updated = Column(DateTime, default=datetime.datetime.utcnow) object_type = Column(Unicode, nullable=False) def __repr__(self): @@ -1329,8 +1329,8 @@ class Activity(Base, ActivityMixin): actor = Column(Integer, ForeignKey("core__users.id"), nullable=False) - published = Column(DateTime, nullable=False, default=datetime.datetime.now) - updated = Column(DateTime, nullable=False, default=datetime.datetime.now) + published = Column(DateTime, nullable=False, default=datetime.datetime.utcnow) + updated = Column(DateTime, nullable=False, default=datetime.datetime.utcnow) verb = Column(Unicode, nullable=False) content = Column(Unicode, nullable=True) title = Column(Unicode, nullable=True) |