def upgrade(pyramid_env): with context.begin_transaction(): op.add_column('file', sa.Column('file_identity', sa.String(64), index=True)) # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() hash_fs = get_hashfs() with transaction.manager: # document.creation_date? for fid, title, creation_date, data in db.execute( """SELECT file.id, document.title, document.creation_date, file.data FROM file JOIN document using(id)"""): data = BytesIO(data) data.seek(0) parts = title.split('.') extension = parts[-1] if len(parts) > 1 else None address = hash_fs.put(data, extension) creation_date = creation_date or datetime.now() creation_date = timegm(creation_date.timetuple()) if address.is_duplicate: creation_date = min(creation_date, path.getmtime(address.abspath)) utime(address.abspath, (creation_date, creation_date)) db.execute("UPDATE file SET file_identity='%s' WHERE id=%d" % (address.id, fid)) mark_changed() with context.begin_transaction(): op.drop_column('file', 'data') op.execute('vacuum full', {'isolation_level':'AUTOCOMMIT'})
def upgrade(pyramid_env): # Do stuff with the app's models here. from assembl import models as m from assembl.lib.history_mixin import HistoryMixin db = m.get_session_maker()() with transaction.manager: # first find duplicates. Lossy. for cls in m.Base.get_subclasses(): if (issubclass(cls, HistoryMixin) and cls == cls.base_polymorphic_class()): t = cls.__table__ base_ids_with_dups = db.query( t.c.base_id).filter(t.c.tombstone_date == None).group_by( t.c.base_id).having(func.count(t.c.id) > 1) for (base_id, ) in base_ids_with_dups: objs = db.query(cls).filter_by(base_id=base_id).order_by( cls.id).all() # keep the last one objs.pop() for obj in objs: obj.delete() mark_changed() # then create indices. with context.begin_transaction(): for cls in m.Base.get_subclasses(): if (issubclass(cls, HistoryMixin) and cls == cls.base_polymorphic_class()): op.execute(sa.schema.CreateIndex(cls.base_id_live_index()))
def downgrade(pyramid_env): from assembl import models as m db = m.get_session_maker()() with transaction.manager: changes = [] for (id, name, values) in db.execute( 'SELECT id, name, values FROM preferences'): values = loads(values or '{}') if 'default_permissions' in values: found = False for role, permissions in list(values['default_permissions'].items()): try: permissions.remove(P_OVERRIDE_SOCIAL_AUTOLOGIN) values['default_permissions'][role] = permissions found = True except ValueError: continue if found: changes.append({'id': id, 'pref_json': dumps(values)}) if changes: db.bulk_update_mappings(m.Preferences.__mapper__, changes) (permission_id,) = db.execute( "SELECT id FROM permission WHERE name='%s'" % ( P_OVERRIDE_SOCIAL_AUTOLOGIN)).first() db.execute("DELETE FROM discussion_permission WHERE permission_id="+str(permission_id)) db.execute("DELETE FROM permission WHERE id="+str(permission_id)) mark_changed()
def downgrade(pyramid_env): with transaction.manager: op.execute("UPDATE thematic SET video_description_top_id=video_description_side_id") mark_changed() with context.begin_transaction(): op.drop_column('thematic', 'video_description_side_id')
def downgrade(pyramid_env): if using_virtuoso(): with context.begin_transaction(): op.create_table('social_auth_account_temp', sa.Column('id', sa.Integer, primary_key=True), sa.Column('username', sa.String(200))) # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: db.execute("""INSERT INTO social_auth_account_temp SELECT id, username FROM social_auth_account WHERE username IS NOT NULL""") mark_changed() with context.begin_transaction(): op.drop_column('social_auth_account', 'username') op.add_column( 'social_auth_account', sa.Column('username', sa.String(200))) with transaction.manager: db.execute("""UPDATE social_auth_account SET username = ( SELECT username FROM social_auth_account_temp WHERE social_auth_account_temp.id = social_auth_account.id)""") mark_changed() with context.begin_transaction(): op.drop_table('social_auth_account_temp') else: with context.begin_transaction(): op.alter_column('social_auth_account', 'username', type_=sa.Unicode(200))
def delete_rows_with_missing_fkey(fkey, delete_missing=True): fkey = _as_fkey(fkey) if fkey.parent.nullable: return True session = get_session_maker()() source = fkey.parent.table target = fkey.column.table if source == target: target = alias(source) source_primary_key = primary_key_col(source) q = session.query(source_primary_key).outerjoin( target, fkey.parent == fkey.column).filter( target.c.id == None) count = q.count() if count: if delete_missing: with transaction.manager: #session.execute(source.delete(source.c.id.in_(q))) for (id,) in q: delete_row(session, source, id) mark_changed(session) else: print "There are %d ids in %s with dangling %s:" % ( count, source.name, fk_as_str(fkey)) print q.all() return False return True
def do_import_content(mbox, only_new=True): mbox = mbox.db.merge(mbox) mbox.db.add(mbox) if mbox.use_ssl: mailbox = IMAP4_SSL(host=mbox.host.encode('utf-8'), port=mbox.port) else: mailbox = IMAP4(host=mbox.host.encode('utf-8'), port=mbox.port) if 'STARTTLS' in mailbox.capabilities: #Always use starttls if server supports it mailbox.starttls() mailbox.login(mbox.username, mbox.password) mailbox.select(mbox.folder) command = "ALL" if only_new and mbox.last_imported_email_uid: command = "(UID %s:*)" % mbox.last_imported_email_uid search_status, search_result = mailbox.uid('search', None, command) email_ids = search_result[0].split() if only_new and mbox.last_imported_email_uid: # discard the first message, it should be the last imported email. del email_ids[0] def import_email(mailbox_obj, email_id): session = mailbox_obj.db() status, message_data = mailbox.uid('fetch', email_id, "(RFC822)") for response_part in message_data: if isinstance(response_part, tuple): message_string = response_part[1] (email_object, dummy, error) = mailbox_obj.parse_email(message_string) if error: raise Exception("error") session.add(email_object) mailbox_obj.last_imported_email_uid = \ email_ids[len(email_ids)-1] transaction.commit() mailbox_obj = Mailbox.get(id=mailbox_obj.id) if len(email_ids): new_emails = [import_email(mbox, email_id) for email_id in email_ids] discussion_id = mbox.discussion_id mailbox.close() mailbox.logout() mark_changed() transaction.commit() with transaction.manager: if len(email_ids): #We imported mails, we need to re-thread emails = Email.db().query(Email).filter( Email.discussion_id == discussion_id, ).options(joinedload_all(Email.parent)) Mailbox.thread_mails(emails) mark_changed()
def downgrade(pyramid_env): # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: permission_ids = dict( list(db.execute('select name, id from permission'))) new_names = set(chain(*extra_permissions.values())) for name in new_names: db.execute( "DELETE FROM discussion_permission WHERE permission_id=%d" % (permission_ids[name])) db.execute("DELETE from permission WHERE id=%d" % (permission_ids[name])) for source, target in renames.items(): db.execute("UPDATE permission SET name='%s' WHERE id = %d" % (source, permission_ids[target])) # the original role of r:owner discussion_permissions is lost. Only restore the permission itself for name in owner_correspondances.keys(): db.execute("INSERT INTO permission (name) VALUES ('%s')" % name) mark_changed() with context.begin_transaction(): op.alter_column('permission', 'name', existing_type=sa.String(), type_=sa.String(20))
def ensure_inheritance_of(cls): # Do not bother with tableless classes if not '__tablename__' in cls.__dict__: return base = cls first = None table = cls.__table__ for c in cls.mro(): if c == cls: continue if '__tablename__' in c.__dict__: if first is None: first = c base = c if base == cls: return basetable = base.__table__ db = get_session_maker()() poly_col = base.__mapper_args__['polymorphic_on'] if not isinstance(poly_col, Column): poly_col = basetable.c[poly_col] poly_id = cls.__mapper_args__['polymorphic_identity'] sub_poly_id = first.__mapper_args__['polymorphic_identity'] query = db.query(basetable.c.id).outerjoin(table, basetable.c.id==table.c.id).filter((poly_col==poly_id) & (table.c.id==None)) if query.count() > 0: with transaction.manager: db.execute(basetable.update().where(basetable.c.id.in_(query)).values(**{poly_col.name: sub_poly_id})) mark_changed(db)
def downgrade(pyramid_env): with context.begin_transaction(): op.add_column('idea_message_column', sa.Column('header_id', sa.Integer(), sa.ForeignKey('langstring.id'))) from assembl import models as m db = m.get_session_maker()() with transaction.manager: columns = db.query(m.IdeaMessageColumn).all() for column in columns: synthesis = column.get_column_synthesis() if synthesis is not None: header = synthesis.body.clone() # we need to clone here, otherwise the langstring is deleted with db.delete(synthesis) # because of the delete-orphan on the relationship and result to an Integrity error # because the langstring is still referenced from idea_message_column table. db.add(header) db.flush() # we can't use here: column.header_id = header.id # the mapper doesn't now about header_id and the change # will not be committed db.execute("""update idea_message_column set header_id = %d where id = %d""" % (header.id, column.id)) mark_changed() db.delete(synthesis)
def downgrade(pyramid_env): with context.begin_transaction(): op.add_column( 'thematic', sa.Column('title_id', sa.Integer, sa.ForeignKey('langstring.id'))) op.add_column( 'thematic', sa.Column('description_id', sa.Integer, sa.ForeignKey('langstring.id'))) op.add_column( 'question', sa.Column('title_id', sa.Integer, sa.ForeignKey('langstring.id'))) # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: db.execute( "UPDATE thematic SET title_id = (SELECT title_id FROM idea WHERE id=thematic.id AND sqla_type='thematic')" ) db.execute( "UPDATE thematic SET description_id = (SELECT description_id FROM idea WHERE id=thematic.id AND sqla_type='thematic')" ) db.execute( "UPDATE question SET title_id = (SELECT title_id FROM idea WHERE id=question.id AND sqla_type='question')" ) mark_changed() with context.begin_transaction(): op.drop_column('idea', 'title_id') op.drop_column('idea', 'description_id')
def downgrade(pyramid_env): with context.begin_transaction(): op.add_column( "discussion", sa.Column("preferred_locales", sa.String)) from assembl import models as m db = m.get_session_maker()() with transaction.manager: for (discussion_id, val) in db.execute( """SELECT discussion.id, preferences."values" FROM discussion JOIN preferences ON discussion.preferences_id = preferences.id"""): if val is not None: locales = json.loads(val).get("preferred_locales", None) if locales is not None: db.execute( sa.text("""UPDATE discussion SET preferred_locales = :locales WHERE id = :discussion_id""" ).bindparams( locales=" ".join(locales), discussion_id=discussion_id)) mark_changed()
def downgrade(pyramid_env): with context.begin_transaction(): op.add_column( 'thematic', sa.Column('title_id', sa.Integer, sa.ForeignKey('langstring.id'))) op.add_column( 'thematic', sa.Column('description_id', sa.Integer, sa.ForeignKey('langstring.id'))) op.add_column( 'question', sa.Column('title_id', sa.Integer, sa.ForeignKey('langstring.id'))) # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: db.execute("UPDATE thematic SET title_id = (SELECT title_id FROM idea WHERE id=thematic.id AND sqla_type='thematic')") db.execute("UPDATE thematic SET description_id = (SELECT description_id FROM idea WHERE id=thematic.id AND sqla_type='thematic')") db.execute("UPDATE question SET title_id = (SELECT title_id FROM idea WHERE id=question.id AND sqla_type='question')") mark_changed() with context.begin_transaction(): op.drop_column('idea', 'title_id') op.drop_column('idea', 'description_id')
def upgrade(pyramid_env): with context.begin_transaction(): op.add_column( 'thematic', sa.Column('video_description_side_id', sa.Integer, sa.ForeignKey('langstring.id'))) # Do stuff with the app's models here. from assembl import models db = models.get_session_maker()() with transaction.manager: current_top_langstrings = [ x for x in db.query(models.Thematic).all() if x.video_description_top is not None ] new_langstrings = map( lambda x: put_new_langstring(x, 'video_description_top', db), current_top_langstrings) for new_langstring in new_langstrings: db.add(new_langstring) mark_changed()
def downgrade(pyramid_env): with context.begin_transaction(): op.add_column('thematic', sa.Column('identifier', sa.String(60))) from assembl import models as m db = m.get_session_maker()() with transaction.manager: for discussion in db.query(m.Discussion): for phase in discussion.timeline_phases: if phase.is_thematics_table: if phase.identifier == 'voteSession': identifier = 'voteSession{}'.format( phase.vote_session.id) db.execute( 'UPDATE thematic SET identifier=:identifier WHERE id=:id', { 'identifier': identifier, 'id': phase.root_idea.id }) else: db.execute( 'UPDATE thematic SET identifier=:identifier WHERE id=:id', { 'identifier': phase.identifier, 'id': phase.root_idea.id }) mark_changed() with context.begin_transaction(): op.drop_column('discussion_phase', 'root_idea_id')
def downgrade(pyramid_env): if using_virtuoso(): with context.begin_transaction(): op.create_table('social_auth_account_temp', sa.Column('id', sa.Integer, primary_key=True), sa.Column('username', sa.String(200))) # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: db.execute("""INSERT INTO social_auth_account_temp SELECT id, username FROM social_auth_account WHERE username IS NOT NULL""") mark_changed() with context.begin_transaction(): op.drop_column('social_auth_account', 'username') op.add_column('social_auth_account', sa.Column('username', sa.String(200))) with transaction.manager: db.execute("""UPDATE social_auth_account SET username = ( SELECT username FROM social_auth_account_temp WHERE social_auth_account_temp.id = social_auth_account.id)""" ) mark_changed() with context.begin_transaction(): op.drop_table('social_auth_account_temp') else: with context.begin_transaction(): op.alter_column('social_auth_account', 'username', type_=sa.Unicode(200))
def upgrade(pyramid_env): with context.begin_transaction(): pass # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: langstring_ids = db.query(m.LangStringEntry.langstring_id).join( m.LangStringEntry.locale).filter( sa.func.length(m.LangStringEntry.value) == 0, ~m.LangStringEntry.is_machine_translated).order_by( m.LangStringEntry.langstring_id).all() langstring_ids = [str(id) for (id,) in langstring_ids] if langstring_ids: first = langstring_ids.pop(0) assert first == str(m.LangString.EMPTY_ID) while len(langstring_ids): subs = ", ".join(langstring_ids[:100]) db.execute("UPDATE content SET subject_id = %s WHERE subject_id IN (%s)" % (first, subs)) db.execute("UPDATE content SET body_id = %s WHERE body_id IN (%s)" % (first, subs)) db.execute("DELETE FROM langstring_entry WHERE langstring_id IN (%s)" % (subs,)) db.execute("DELETE FROM langstring WHERE id IN (%s)" % (subs,)) langstring_ids = langstring_ids[100:] mark_changed()
def rebuild_table(table, delete_missing=False): from virtuoso.alchemy import AddForeignKey, DropForeignKey print "rebuilding", table session = get_session_maker()() incoming = set(get_incoming_fks(table)) outgoing = set(table.foreign_keys) all_fkeys = incoming | outgoing self_ref = incoming & outgoing try: for fk in all_fkeys: if not delete_rows_with_missing_fkey(fk, delete_missing): print "There are missing keys, will not rebuild " + table.name return except Exception as e: traceback.print_exc() print "Could not delete missing keys" raise e # Booleans with NULL values for col in table.c: if isinstance(col.type, Boolean): session.execute( table.update().where(col == None).values(**{col.name: 0})) # Drop all keys for fk in all_fkeys: try: session.execute(DropForeignKey(fk)) except Exception as e: print "Could not drop fkey %s, maybe does not exist." % ( fk_as_str(fk), ) print e clone = clone_table(table, table.name + "_temp", False, False) clone.create(session.bind) column_names = [c.name for c in table.columns] sel = select([getattr(table.c, cname) for cname in column_names]) with transaction.manager: session.execute(clone.insert().from_select(column_names, sel)) mark_changed(session) session.execute(DropTable(table)) # Should we create it without outgoing first? table.create(session.bind) # self ref will make the insert fail. for fk in self_ref: try: session.execute(DropForeignKey(fk)) except Exception as e: print "Could not drop fkey %s, maybe does not exist." % ( fk_as_str(fk), ) print e sel = select([getattr(clone.c, cname) for cname in column_names]) with transaction.manager: session.execute(table.insert().from_select(column_names, sel)) mark_changed(session) session.execute(DropTable(clone)) if delete_missing: # Delete a second time, in case. for fk in outgoing: assert delete_rows_with_missing_fkey(fk, True), "OUCH" for fk in incoming: # includes self_ref session.execute(AddForeignKey(fk))
def do_import_content(mbox, only_new=True): if mbox.use_ssl: mailbox = IMAP4_SSL(host=mbox.host.encode('utf-8'), port=mbox.port) else: mailbox = IMAP4(host=mbox.host.encode('utf-8'), port=mbox.port) if 'STARTTLS' in mailbox.capabilities: #Always use starttls if server supports it mailbox.starttls() mailbox.login(mbox.username, mbox.password) mailbox.select(mbox.folder) command = "ALL" if only_new and mbox.last_imported_email_uid: command = "(UID %s:*)" % mbox.last_imported_email_uid search_status, search_result = mailbox.uid('search', None, command) email_ids = search_result[0].split() if only_new and mbox.last_imported_email_uid: # discard the first message, it should be the last imported email. del email_ids[0] def import_email(mailbox_obj, email_id): session = Email.db() mailbox_obj = session.merge(mailbox_obj) status, message_data = mailbox.uid('fetch', email_id, "(RFC822)") for response_part in message_data: if isinstance(response_part, tuple): message_string = response_part[1] (email_object, _) = mailbox_obj.parse_email(message_string) session.add(email_object) transaction.commit() mailbox_obj = Mailbox.get(id=mailbox_obj.id) if len(email_ids): new_emails = [import_email(mbox, email_id) for email_id in email_ids] mbox = Mailbox.get(id=mbox.id) mbox.last_imported_email_uid = \ email_ids[len(email_ids)-1] # TODO: remove this line, the property `last_import` does not persist. mbox.last_import = datetime.utcnow() mark_changed() transaction.commit() mailbox.close() mailbox.logout() if len(email_ids): #We imported mails, we need to re-thread emails = self.db.query(Email).filter( Email.source_id == self.id, ).options(joinedload_all(Email.post, Post.parent, Post.content)) self.thread_mails(emails)
def downgrade(pyramid_env): from assembl import models as m db = m.get_session_maker()() with context.begin_transaction(): op.create_table( "user_language_preference_temp", sa.Column("id", sa.Integer, primary_key=True), sa.Column("user_id", sa.Integer), sa.Column("lang_code", sa.String), sa.Column("preferred_order", sa.Integer), sa.Column("locale_id", sa.Integer), sa.Column("explicitly_defined", sa.Boolean, server_default="0")) with transaction.manager: from assembl.models.auth import LanguagePreferenceOrder op.execute("""INSERT INTO user_language_preference_temp (id, user_id, locale_id, preferred_order) SELECT id, user_id, locale_id, source_of_evidence FROM user_language_preference""") locale_ids = db.execute( """SELECT DISTINCT locale_id, locale.code FROM user_language_preference JOIN locale ON (locale.id=locale_id)""") for locale_id, locale_name in locale_ids: op.execute("UPDATE user_language_preference_temp SET lang_code = '%s' WHERE locale_id = %d" % ( locale_name, locale_id)) op.execute("""UPDATE user_language_preference_temp SET explicitly_defined = 1 WHERE preferred_order = %d""" % (LanguagePreferenceOrder.Explicit,)) op.execute("DELETE FROM user_language_preference") mark_changed() with context.begin_transaction(): op.add_column( 'user_language_preference', sa.Column( 'explicitly_defined', sa.Boolean, nullable=False, server_default=TextClause("0"))) op.add_column( 'user_language_preference', sa.Column( 'lang_code', sa.String(), nullable=False, server_default="")) op.drop_index( '%s_%s_user_language_preference_UNQC_user_id_locale_id' % ( config.get('db_schema'), config.get('db_user'))) op.create_index( '%s_%s_user_language_preference_UNQC_user_id_lang_code' % ( config.get('db_schema'), config.get('db_user')), 'user_language_preference', ['user_id', 'lang_code'], unique=True) op.drop_column('user_language_preference', 'source_of_evidence') op.drop_column('user_language_preference', 'translate_to') op.drop_column('user_language_preference', 'locale_id') with transaction.manager: op.execute("""INSERT INTO user_language_preference (id, user_id, lang_code, preferred_order, explicitly_defined) SELECT id, user_id, lang_code, preferred_order, explicitly_defined FROM user_language_preference_temp""") mark_changed() with context.begin_transaction(): op.drop_table("user_language_preference_temp")
def downgrade(pyramid_env): from assembl import models as m db = m.get_session_maker()() with transaction.manager: aaat = m.AbstractAgentAccount.__table__ db.execute(aaat.update().where(aaat.c.type == u'facebook_account' ).values(type="idprovider_agent_account")) db.execute('delete from facebook_account') mark_changed()
def drop_tables(app_settings, session): log.info('Dropping all tables.') session.close() try: get_metadata().drop_all(session.connection()) mark_changed() except Exception as e: raise Exception('Error dropping tables: %s' % e)
def downgrade(pyramid_env): with transaction.manager: from assembl import models as m db = m.get_session_maker()() db.execute( sa.text(update_string).bindparams( old_type='landing_page_module', val='resource') ) mark_changed()
def downgrade(pyramid_env): with transaction.manager: op.execute( "UPDATE thematic SET video_description_top_id=video_description_side_id" ) mark_changed() with context.begin_transaction(): op.drop_column('thematic', 'video_description_side_id')
def rebuild_table(table, delete_missing=False): from virtuoso.alchemy import AddForeignKey, DropForeignKey print "rebuilding", table session = get_session_maker()() incoming = set(get_incoming_fks(table)) outgoing = set(table.foreign_keys) all_fkeys = incoming | outgoing self_ref = incoming & outgoing try: for fk in all_fkeys: if not delete_rows_with_missing_fkey(fk, delete_missing): print "There are missing keys, will not rebuild " + table.name return except Exception as e: traceback.print_exc() print "Could not delete missing keys" raise e # Booleans with NULL values for col in table.c: if isinstance(col.type, Boolean): session.execute(table.update().where(col == None).values(**{col.name:0})) # Drop all keys for fk in all_fkeys: try: session.execute(DropForeignKey(fk)) except Exception as e: print "Could not drop fkey %s, maybe does not exist." % (fk_as_str(fk),) print e clone = clone_table(table, table.name+"_temp", False, False) clone.create(session.bind) column_names = [c.name for c in table.columns] sel = select([getattr(table.c, cname) for cname in column_names]) with transaction.manager: session.execute(clone.insert().from_select(column_names, sel)) mark_changed(session) session.execute(DropTable(table)) # Should we create it without outgoing first? table.create(session.bind) # self ref will make the insert fail. for fk in self_ref: try: session.execute(DropForeignKey(fk)) except Exception as e: print "Could not drop fkey %s, maybe does not exist." % (fk_as_str(fk),) print e sel = select([getattr(clone.c, cname) for cname in column_names]) with transaction.manager: session.execute(table.insert().from_select(column_names, sel)) mark_changed(session) session.execute(DropTable(clone)) if delete_missing: # Delete a second time, in case. for fk in outgoing: assert delete_rows_with_missing_fkey(fk, True), "OUCH" for fk in incoming: # includes self_ref session.execute(AddForeignKey(fk))
def downgrade(pyramid_env): langstring_idsequence = "%s.%s.langstring_idsequence" % ( config.get("db_schema"), config.get("db_user")) with context.begin_transaction(): op.drop_column("content", "body_id") op.drop_column("content", "subject_id") op.execute("delete from langstring_entry") op.execute("delete from langstring") op.execute("sequence_set('%s', 1, 0)" % langstring_idsequence) mark_changed()
def downgrade(pyramid_env): from assembl import models as m db = m.get_session_maker()() with transaction.manager: aaat = m.AbstractAgentAccount.__table__ db.execute( aaat.update().where(aaat.c.type == u'facebook_account').values( type="idprovider_agent_account")) db.execute('delete from facebook_account') mark_changed()
def downgrade(pyramid_env): with context.begin_transaction(): op.add_column('thematic', sa.Column('video_description_id', sa.Integer, sa.ForeignKey('langstring.id'))) with transaction.manager: op.execute("UPDATE thematic SET video_description_id = video_description_top_id") mark_changed() with context.begin_transaction(): op.drop_column('thematic', 'video_description_bottom_id') op.drop_column('thematic', 'video_description_top_id')
def upgrade(pyramid_env): # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: aaat = m.AbstractAgentAccount.__table__ q = db.query(aaat.c.id).join(m.IdentityProviderAccount.__table__).join( m.IdentityProvider.__table__).filter( m.IdentityProvider.name == u'facebook') db.execute(m.FacebookAccount.__table__.insert().from_select(['id'], q)) db.execute(aaat.update().where( aaat.c.id.in_(q)).values(type="facebook_account")) mark_changed()
def upgrade(pyramid_env): # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: aaat = m.AbstractAgentAccount.__table__ q = db.query(aaat.c.id ).join(m.IdentityProviderAccount.__table__ ).join(m.IdentityProvider.__table__ ).filter(m.IdentityProvider.name == u'facebook') db.execute(m.FacebookAccount.__table__.insert().from_select(['id'], q)) db.execute(aaat.update().where( aaat.c.id.in_(q)).values(type="facebook_account")) mark_changed()
def downgrade(pyramid_env): with context.begin_transaction(): op.add_column( 'idea', sa.Column('rdf_type', sa.String(60), nullable=False, server_default='idea:GenericIdeaNode')) op.add_column( 'idea_idea_link', sa.Column('rdf_type', sa.String(60), nullable=False, server_default='idea:InclusionRelation')) # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: different = list( db.execute("""SELECT idea.id, uriref.val FROM idea JOIN uriref ON (rdf_type_id=uriref.id) WHERE idea.rdf_type_id != 1""")) db.execute("UPDATE idea SET rdf_type_id=3 WHERE sqla_type='root_idea'") if different: ctx = jsonld_context() missing = [{ "id": id, "rdf_type": ctx.shrink_iri(uri) } for id, uri in different] db.bulk_update_mappings(m.Idea.__mapper__, missing) mark_changed() different = list( db.execute( """SELECT idea_idea_link.id, uriref.val FROM idea_idea_link JOIN uriref ON (rdf_type_id=uriref.id) WHERE idea_idea_link.rdf_type_id != 2""")) if different: ctx = jsonld_context() missing = [{ "id": id, "rdf_type": ctx.shrink_iri(uri) } for id, uri in different] db.bulk_update_mappings(m.IdeaLink.__mapper__, missing) mark_changed() with context.begin_transaction(): op.drop_column('idea', 'rdf_type_id') op.drop_column('idea_idea_link', 'rdf_type_id')
def upgrade(pyramid_env): with context.begin_transaction(): op.add_column('discussion', sa.Column('active_start_date', sa.DateTime)) op.add_column('discussion', sa.Column('active_end_date', sa.DateTime)) # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: # Transfer all extra_json dates to this column, if exists discussion_ids = db.query(m.Discussion.id, m.Discussion.slug, m.Discussion.preferences_id).all() d = m.Discussion.__table__ for d_id, slug, pref_id in discussion_ids: pref = db.query(m.Preferences).filter_by(id=pref_id).first() extra = pref['extra_json'] success = False if 'dates' in extra: dates = extra['dates'] print("Checking if both start and end dates from Discussion %s exist" % slug) if 'startDate' in dates and 'endDate' in dates: print("Migrating Discussion %s's dates to model from extra_json" % slug) try: start_date = datetime.strptime(dates['startDate'], "%Y-%m-%d") stop_date = datetime.strptime(dates['startDate'], "%Y-%m-%d") if start_date and stop_date: db.execute(d.update().where(d.c.id == d_id).values( active_start_date=start_date, active_end_date=stop_date)) success = True except: print("Failed to convert Discussion %s's dates to model" % slug) else: print("Not both start and end dates from Discussion %s are set! Skipped..." % slug) if not success: # Try to use phases print("Migrating Discussion %s's dates to model from phases" % slug) dates = db.query(sa.sql.func.min(m.TimelineEvent.start), sa.sql.func.max(m.TimelineEvent.end) ).filter_by(discussion_id=d_id).first() if dates: (start_date, end_date) = dates if (start_date and end_date and start_date < end_date): db.execute(d.update().where(d.c.id == d_id).values( active_start_date=start_date, active_end_date=end_date)) success = True else: print("Not both start and end dates from Discussion %s are set in phases! Skipped..." % slug) else: print("There are no set phases for Discussion %s to continue. Skipped..." % slug) mark_changed(db)
def upgrade(pyramid_env): # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: errors = list(db.execute( """SELECT idea_content_link.id, post.creator_id FROM idea_content_link JOIN post ON content_id = post.id WHERE idea_content_link.type = 'assembl:postLinkedToIdea' AND idea_content_link.creator_id=-1""")) for id, contributor in errors: db.execute( "UPDATE idea_content_link SET creator_id={contributor} WHERE id = {id}".format( id=id, contributor=contributor)) mark_changed()
def upgrade(pyramid_env): # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: errors = list( db.execute( """SELECT idea_content_link.id, post.creator_id FROM idea_content_link JOIN post ON content_id = post.id WHERE idea_content_link.type = 'assembl:postLinkedToIdea' AND idea_content_link.creator_id=-1""")) for id, contributor in errors: db.execute( "UPDATE idea_content_link SET creator_id={contributor} WHERE id = {id}" .format(id=id, contributor=contributor)) mark_changed()
def upgrade(pyramid_env): with context.begin_transaction(): op.add_column('idea_idea_link', sa.Column( 'rdf_temp', sa.String(60), nullable=False)) with context.begin_transaction(): op.execute('UPDATE idea_idea_link SET "rdf_temp" = "rdf_type"') op.execute('''UPDATE idea_idea_link SET "rdf_temp" = 'idea:InclusionRelation' WHERE "rdf_temp" = 'idea:GenericIdeaNode' ''') mark_changed() with context.begin_transaction(): op.drop_column('idea_idea_link', 'rdf_type') op.add_column('idea_idea_link', sa.Column( 'rdf_type', sa.String(60), nullable=False, server_default='idea:InclusionRelation')) op.drop_column('idea_idea_link', 'rdf_temp')
def upgrade(pyramid_env): with context.begin_transaction(): op.add_column( "content", sa.Column("subject_id", sa.Integer, sa.ForeignKey("langstring.id"))) op.add_column( "content", sa.Column("body_id", sa.Integer, sa.ForeignKey("langstring.id"))) langstring_idsequence = "%s.%s.langstring_idsequence" % ( config.get("db_schema"), config.get("db_user")) # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: discussion_locales = db.execute( "select id, preferred_locales from discussion") discussion_locales = { id: (locs or 'und').split(' ')[0] for (id, locs) in discussion_locales } locales = dict(list(db.execute("select code, id from locale"))) locale_id_for_discussion = { id: locales[loc] for (id, loc) in discussion_locales.iteritems() } for target in ("subject", "body"): posts = db.execute("select id, discussion_id, %s from content" % target) for post_id, discussion_id, content in posts: (langstring_id, ) = next( iter( db.execute("select sequence_next('%s')" % langstring_idsequence))) db.execute("INSERT into langstring values (%d)" % (langstring_id, )) db.execute( text( """INSERT into langstring_entry (langstring_id, locale_id, value) values (:langstring_id, :locale_id, :value)""").bindparams( langstring_id=langstring_id, locale_id=locale_id_for_discussion[discussion_id], value=content)) db.execute("UPDATE content set %s_id = %d WHERE id=%d" % (target, langstring_id, post_id)) mark_changed()
def downgrade(pyramid_env): with context.begin_transaction(): op.add_column('idea_idea_link', sa.Column( 'sqla_type', sa.String(60), nullable=False)) op.create_table('root_idea', sa.Column( 'id', sa.Integer, sa.ForeignKey( 'idea.id', ondelete='CASCADE', onupdate='CASCADE'), primary_key=True)) with context.begin_transaction(): op.execute('UPDATE idea SET "sqla_type" = "rdf_type"') op.execute('UPDATE idea_idea_link SET "sqla_type" = "rdf_type"') op.execute("INSERT INTO root_idea (id) SELECT id FROM idea WHERE sqla_type ='assembl:RootIdea'") mark_changed() with context.begin_transaction(): op.drop_column('idea_idea_link', 'rdf_type') op.drop_column('idea', 'rdf_type') op.drop_column('idea', 'last_modified')
def upgrade(pyramid_env): # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with context.begin_transaction(): # temporary fix vote_session_id foreign key op.drop_constraint(u'vote_specification_vote_session_id_fkey', 'vote_specification', type_='foreignkey') op.create_foreign_key(None, 'vote_specification', 'vote_session', ['vote_session_id'], ['id'], ondelete='CASCADE', onupdate='CASCADE') with transaction.manager: # remove all existing vote sessions (there is no vote session currently in prod) db.execute('DELETE FROM vote_session') mark_changed() with context.begin_transaction(): op.drop_column('vote_specification', 'vote_session_id') op.alter_column("vote_specification", "widget_id", nullable=False)
def upgrade(pyramid_env): from assembl import models as m with context.begin_transaction(): op.execute("DELETE FROM attachment WHERE type='vote_session_attachment'") op.execute('DELETE FROM idea_vote') op.execute('DELETE FROM token_category_specification') op.execute('DELETE FROM vote_specification') op.execute('DELETE FROM vote_session') # convert thematic to idea op.execute("UPDATE idea SET message_view_override='thread' WHERE hidden=false AND message_view_override IS NULL") op.execute("UPDATE idea SET message_view_override='survey' WHERE sqla_type='thematic' AND hidden=false") op.execute("UPDATE idea SET sqla_type='idea' WHERE sqla_type='thematic'") mark_changed() # this drop video module associated to survey ideas, we don't do migration to announcement op.drop_table('thematic') op.add_column('announce', sa.Column('quote_id', sa.Integer, sa.ForeignKey('langstring.id'))) op.drop_column("vote_session", "title_id") op.drop_column("vote_session", "sub_title_id") op.drop_column("vote_session", "instructions_section_title_id") op.drop_column("vote_session", "instructions_section_content_id") op.drop_column("vote_session", "discussion_phase_id") op.add_column("vote_session", sa.Column( 'idea_id', sa.Integer, sa.ForeignKey( m.Idea.id, onupdate="CASCADE", ondelete='CASCADE'), nullable=False, unique=True)) op.create_unique_constraint("discussion_phase_root_idea_id_key", "discussion_phase", ["root_idea_id"]) with transaction.manager: from assembl.indexing import join_transaction join_transaction() # delete all vote proposals and phase vote_session_phases = m.TimelineEvent.query.filter(m.TimelineEvent.identifier == 'voteSession').all() roots = [phase.root_idea for phase in vote_session_phases if phase.root_idea is not None] for root in roots: for child in root.get_children(): child.db.delete(child) root.db.delete(root) for phase in vote_session_phases: phase.db.delete(phase) # set messages_in_parent to False for all ideas for idea in m.Idea.query.filter(~m.Idea.sqla_type.in_(('question', 'vote_proposal'))): idea.messages_in_parent = False
def upgrade(pyramid_env): with context.begin_transaction(): pass # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: m.Permission.populate_db(db) db.flush() changes = [] for (id, name, values) in db.execute('SELECT id, name, values FROM preferences'): values = loads(values or '{}') if 'default_permissions' in values: found = False for role, permissions in list( values['default_permissions'].items()): if role not in base_roles: if P_OVERRIDE_SOCIAL_AUTOLOGIN not in permissions: permissions.append(P_OVERRIDE_SOCIAL_AUTOLOGIN) values['default_permissions'][role] = permissions found = True if found: changes.append({'id': id, 'pref_json': dumps(values)}) if changes: db.bulk_update_mappings(m.Preferences.__mapper__, changes) role_ids = [ id for (id, name) in db.execute('SELECT id, name FROM role') if name not in base_roles ] discussions = list(db.execute("SELECT id FROM discussion")) discussions = [id for (id, ) in discussions] (permission_id, ) = db.execute( "SELECT id FROM permission WHERE name='%s'" % (P_OVERRIDE_SOCIAL_AUTOLOGIN)).first() db.bulk_insert_mappings(m.DiscussionPermission, [{ 'discussion_id': d, 'role_id': r, 'permission_id': permission_id } for (d, r) in product(discussions, role_ids)]) mark_changed()
def downgrade(pyramid_env): with context.begin_transaction(): op.add_column('timeline_event', sa.Column('title', sa.Unicode())) op.add_column('timeline_event', sa.Column('description', sa.UnicodeText)) # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: # This assumes as single LangStringEntry per timeline event. # Not true in general, but enough to revert the upgrade. data = list( db.execute( """SELECT te.id, title.langstring_id, description.langstring_id, title.value, description.value FROM timeline_event AS te JOIN langstring_entry AS title ON title.langstring_id = te.title_id LEFT JOIN langstring_entry AS description ON description.langstring_id = te.description_id""" )) ids = [] if data: for ev_id, t_id, d_id, title, description in data: db.execute( 'UPDATE timeline_event SET title=:title, description=:desc WHERE id = :id', { "title": title, "desc": description, "id": ev_id }) ids.extend([str(t_id), str(d_id)]) mark_changed() with context.begin_transaction(): op.drop_column('timeline_event', 'title_id') op.drop_column('timeline_event', 'description_id') op.drop_column('timeline_event', 'image_url') # after title_id and description_id are gone if ids: ids = (",".join(ids), ) op.execute( "DELETE FROM langstring_entry WHERE langstring_id IN (%s)" % ids) op.execute("DELETE FROM langstring WHERE id IN (%s)" % ids) op.alter_column('timeline_event', 'title', nullable=False)
def downgrade(pyramid_env): # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: titles = list(db.execute("""SELECT id, title FROM document""")) with context.begin_transaction(): op.drop_column('document', 'title') op.add_column('document', sa.Column('title', sa.String(1024), server_default="")) with transaction.manager: docs = m.Document.__table__ for (id, title) in titles: db.execute(docs.update().where(docs.c.id == id).values( title=title.encode('utf8'))) mark_changed()
def drop_tables(app_settings, session): log.info('Dropping all tables.') if not using_virtuoso(): # postgres. Thank you to # http://stackoverflow.com/questions/5408156/how-to-drop-a-postgresql-database-if-there-are-active-connections-to-it session.close() session.execute("""SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '%s' AND pid <> pg_backend_pid()""" % (app_settings.get("db_database"))) try: for row in get_all_tables(app_settings, session): log.debug("Dropping table: %s" % row) session.execute("drop table \"%s\"" % row) mark_changed() except: raise Exception('Error dropping tables: %s' % (sys.exc_info()[1]))
def upgrade(pyramid_env): with context.begin_transaction(): op.alter_column('permission', 'name', existing_type=sa.String(20), type_=sa.String()) # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: role_ids = dict(list(db.execute('select name, id from role'))) if 'r:owner' not in role_ids: db.execute("INSERT INTO role (name) VALUES ('r:owner')") role_ids = dict(list(db.execute('select name, id from role'))) permission_ids = dict( list(db.execute('select name, id from permission'))) for before, after in owner_correspondances.items(): db.execute( "UPDATE discussion_permission SET role_id=%d, permission_id=%d WHERE permission_id=%d" % (role_ids['r:owner'], permission_ids[after], permission_ids[before])) db.execute("DELETE FROM permission WHERE id=%d" % permission_ids[before]) for source, target in renames.items(): db.execute("UPDATE permission SET name='%s' WHERE id = %d" % (target, permission_ids[source])) new_names = set(chain(*extra_permissions.values())) - set( permission_ids.keys()) for name in new_names: db.execute("INSERT INTO permission (name) VALUES ('%s')" % name) permission_ids = dict( list(db.execute('select name, id from permission'))) for source, targets in extra_permissions.items(): for target in targets: db.execute( """INSERT INTO discussion_permission (role_id, discussion_id, permission_id) SELECT role_id, discussion_id, %d FROM discussion_permission WHERE permission_id=%d""" % (permission_ids[target], permission_ids[source])) mark_changed()
def downgrade(pyramid_env): with context.begin_transaction(): op.add_column( 'timeline_event', sa.Column('title', sa.Unicode())) op.add_column( 'timeline_event', sa.Column('description', sa.UnicodeText)) # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: # This assumes as single LangStringEntry per timeline event. # Not true in general, but enough to revert the upgrade. data = list(db.execute( """SELECT te.id, title.langstring_id, description.langstring_id, title.value, description.value FROM timeline_event AS te JOIN langstring_entry AS title ON title.langstring_id = te.title_id LEFT JOIN langstring_entry AS description ON description.langstring_id = te.description_id""")) ids = [] if data: for ev_id, t_id, d_id, title, description in data: db.execute( 'UPDATE timeline_event SET title=:title, description=:desc WHERE id = :id', {"title": title, "desc": description, "id": ev_id}) ids.extend([str(t_id), str(d_id)]) mark_changed() with context.begin_transaction(): op.drop_column('timeline_event', 'title_id') op.drop_column('timeline_event', 'description_id') op.drop_column('timeline_event', 'image_url') # after title_id and description_id are gone if ids: ids = (",".join(ids),) op.execute( "DELETE FROM langstring_entry WHERE langstring_id IN (%s)" % ids) op.execute( "DELETE FROM langstring WHERE id IN (%s)" % ids) op.alter_column('timeline_event', 'title', nullable=False)
def upgrade(pyramid_env): with context.begin_transaction(): for f, stype in new_types.iteritems(): op.add_column( 'document', sa.Column(f+'_temp', stype)) op.execute("UPDATE document SET " + ", ".join(( ("{f}_temp = cast({f} as varchar)" if stype == sa.String else "{f}_temp = {f}").format(f=f) for (f, stype) in new_types.iteritems()))) mark_changed() # Why do two variants exist??? try: op.drop_index('ix_document_uri_id', 'document') except: try: op.drop_index('ix_%s_%s_document_uri_id' % ( config.get('db_schema'), config.get('db_user')), 'document') except: pass with context.begin_transaction(): for f in new_types: op.drop_column('document', f) for f, stype in new_types.iteritems(): op.add_column('document', sa.Column( f, stype, server_default="" if f in with_default else None)) op.execute("UPDATE document SET " + ",".join(( "{f} = {f}_temp".format(f=f) for f in new_types))) mark_changed() with context.begin_transaction(): op.execute( "CREATE UNIQUE INDEX ix_document_uri_id ON %s.%s.document (uri_id)" % ( config.get('db_schema'), config.get('db_user'))) for f in new_types: op.drop_column('document', f+"_temp")
def upgrade(pyramid_env): with context.begin_transaction(): op.add_column('file', sa.Column('file_size', sa.Integer)) from assembl import models as m from assembl.lib.hash_fs import get_hashfs db = m.get_session_maker()() hashfs = get_hashfs() with transaction.manager: for id, fileHash in db.execute('SELECT id, file_identity FROM file'): if not fileHash: continue hash = hashfs.get(fileHash) if not hash: print("Missing hash:", id, fileHash) continue size = path.getsize(hash.abspath.encode('ascii')) if not size: print("Missing size:", id, fileHash) continue db.execute('UPDATE file SET file_size=%d WHERE id=%d' % (size, id)) mark_changed()
def downgrade(pyramid_env): schema, user = config.get('db_schema'), config.get('db_user') with context.begin_transaction(): for tablename in tables: index_name = "%s_%s_%s_UNQC_base_id_tombstone_date" % ( schema, user, tablename) op.drop_constraint(index_name, tablename, schema=schema) op.add_column(tablename, sa.Column('is_tombstone', sa.SmallInteger, server_default="0")) # repopulate is_tombstone with transaction.manager: for tablename in tables: op.execute('UPDATE %s set "is_tombstone" = (CASE WHEN "tombstone_date" IS NULL THEN 0 ELSE 1 END)' % (tablename,)) mark_changed() with context.begin_transaction(): for tablename in tables: op.drop_column(tablename, "base_id") op.drop_column(tablename, "tombstone_date") op.drop_table(tablename+ID_TABLE) op.execute('ALTER TABLE %s ADD CHECK ("is_tombstone" IN (0, 1))'%(tablename,))
def upgrade(pyramid_env): from assembl import models as m db = m.get_session_maker()() with transaction.manager: for (locales, pref_id, pref) in db.execute( """SELECT discussion.preferred_locales, preferences.id, preferences."values" FROM discussion JOIN preferences ON discussion.preferences_id = preferences.id"""): if locales is not None: pref = {} if pref is None else json.loads(pref) pref["preferred_locales"] = [ strip_country(l) for l in locales.split()] db.execute(sa.text( 'UPDATE preferences SET "values"=:val where id=:pref_id' ).bindparams( val=json.dumps(pref), pref_id=pref_id)) mark_changed() with context.begin_transaction(): op.drop_column("discussion", "preferred_locales")
def upgrade(pyramid_env): with context.begin_transaction(): op.add_column('idea', sa.Column( 'rdf_type', sa.String(60), nullable=False, server_default='idea:GenericIdeaNode')) op.add_column('idea', sa.Column( 'last_modified', sa.types.TIMESTAMP)) op.add_column('idea_idea_link', sa.Column( 'rdf_type', sa.String(60), nullable=False, server_default='idea:InclusionRelation')) op.drop_table("root_idea") with context.begin_transaction(): op.execute('UPDATE idea SET "rdf_type" = "sqla_type"') op.execute('UPDATE idea_idea_link SET "rdf_type" = "sqla_type"') op.execute("UPDATE idea SET sqla_type = 'root_idea' WHERE sqla_type = 'assembl:RootIdea'") op.execute("UPDATE idea SET sqla_type = 'idea' WHERE sqla_type <> 'root_idea'") mark_changed() with context.begin_transaction(): op.drop_column('idea_idea_link', 'sqla_type')