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): 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 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 upgrade(pyramid_env): from assembl import models as m from assembl.models.idea_content_link import ExtractStates, extract_states_identifiers with context.begin_transaction(): schema = config.get('db_schema') # User can be a machine op.add_column( 'user', sa.Column('is_machine', sa.Boolean(), default=False, server_default='0')) # Add the extract state: The extract can be Published or Submitted extract_states = sa.Enum(*extract_states_identifiers, name='extract_states') extract_states.create(op.get_bind()) op.add_column( 'extract', sa.Column( 'extract_state', sa.Enum(*extract_states_identifiers, name='extract_states'), nullable=False, default=ExtractStates.PUBLISHED.value, server_default=ExtractStates.PUBLISHED.value), schema=schema ) # Add the machine user db = m.get_session_maker()() with transaction.manager: from assembl.indexing import join_transaction join_transaction() m.User.populate_db(db)
def upgrade(pyramid_env): from assembl import models as m db = m.get_session_maker()() with transaction.manager: fk = next( iter(m.TimelineEvent.__table__.c.previous_event_id.foreign_keys)) rebuild_fkey(db, fk)
def downgrade(pyramid_env): with context.begin_transaction(): op.add_column( 'agent_email_account', sa.Column("preferred", sa.SmallInteger, default=False, server_default='0')) # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: # get from previous values db.execute("""UPDATE agent_email_account SET preferred=( SELECT abstract_agent_account.preferred FROM abstract_agent_account WHERE abstract_agent_account.id = agent_email_account.id AND abstract_agent_account."type" = 'agent_email_account')""") # Force update, transaction manager saw nothing aaa = db.query(m.Role).first() flag_modified(aaa, 'name') with context.begin_transaction(): db.execute('ALTER TABLE agent_email_account ADD CHECK (preferred IN (0, 1))') op.drop_column( 'abstract_agent_account', "preferred")
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 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 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(): op.execute( 'UPDATE langstring_entry SET value=NULL WHERE length("value")=0') # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: data = list(db.execute( """SELECT content.subject_id, content.id FROM content WHERE subject_id in ( SELECT subject_id FROM content GROUP BY subject_id HAVING count(id) > 1)""")) data.sort() original_ls = None original_ls_id = None for subject_id, content_id in data: if original_ls_id != subject_id: original_ls_id = subject_id original_ls = m.LangString.get(subject_id) continue new_langstring = original_ls.clone(db) db.flush() db.execute("UPDATE content SET subject_id = %d WHERE id = %d" % ( new_langstring.id, content_id))
def upgrade(pyramid_env): with context.begin_transaction(): op.create_table( 'edgesense_drupal_source', sa.Column('id', sa.Integer, sa.ForeignKey( 'post_source.id', onupdate='CASCADE', ondelete='CASCADE'), primary_key=True), sa.Column('node_source', sa.String(1024), nullable=False), sa.Column('node_root', sa.String(200)), sa.Column('user_source', sa.String(1024), nullable=False), sa.Column('comment_source', sa.String(1024), nullable=False), sa.Column('post_id_prepend', sa.String(100), nullable=False) ) op.create_table( 'source_specific_account', sa.Column('id', sa.Integer, sa.ForeignKey( 'abstract_agent_account.id', onupdate='CASCADE', ondelete='CASCADE'), primary_key=True), sa.Column('user_info', sa.Text), sa.Column('user_link', sa.String(1024)), sa.Column('user_id', sa.String(15), nullable=False), sa.Column('source_id', sa.Integer, sa.ForeignKey( 'edgesense_drupal_source.id', onupdate='CASCADE', ondelete='CASCADE'), nullable=False), ) # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: pass
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 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 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: # create landing_page_module_type table op.create_table( 'landing_page_module_type', sa.Column('id', sa.Integer, primary_key=True), sa.Column('type', sa.String(60), nullable=False), sa.Column('identifier', sa.String(30), nullable=False), sa.Column("title_id", sa.Integer, sa.ForeignKey("langstring.id")), sa.Column('default_order', sa.Float, nullable=False), sa.Column('editable_order', sa.Boolean, default=True), sa.Column('required', sa.Boolean, default=False), sa.schema.UniqueConstraint("title_id") ) # create landing_page_module table op.create_table( 'landing_page_module', sa.Column('id', sa.Integer, primary_key=True), sa.Column('type', sa.String(60), nullable=False), sa.Column('discussion_id', sa.Integer, sa.ForeignKey("discussion.id", ondelete="CASCADE", onupdate="CASCADE")), sa.Column("module_type_id", sa.Integer, sa.ForeignKey("landing_page_module_type.id", ondelete="CASCADE", onupdate="CASCADE")), sa.Column('configuration', sa.UnicodeText, nullable=False), sa.Column('order', sa.Float, nullable=False), sa.Column('enabled', sa.Boolean, default=False) )
def upgrade(pyramid_env): with context.begin_transaction(): op.create_table( 'agent_status_in_discussion', sa.Column('id', sa.Integer, primary_key=True), sa.Column('discussion_id', sa.Integer, sa.ForeignKey( "discussion.id", ondelete='CASCADE', onupdate='CASCADE')), sa.Column('profile_id', sa.Integer, sa.ForeignKey( "agent_profile.id", ondelete='CASCADE', onupdate='CASCADE')), sa.Column('last_visit', sa.DateTime), sa.Column('first_visit', sa.DateTime), sa.Column('first_subscribed', sa.DateTime), sa.Column('last_unsubscribed', sa.DateTime), sa.Column('user_created_on_this_discussion', sa.Boolean, server_default='0'), sa.schema.UniqueConstraint('discussion_id', 'profile_id') ) # Do stuff with the app's models here. from assembl import models as m from assembl.auth import R_PARTICIPANT db = m.get_session_maker()() now = datetime.utcnow() with transaction.manager: for (user_id, discussion_id) in db.query( m.LocalUserRole.user_id, m.LocalUserRole.discussion_id).join( m.Role).filter(m.Role.name == R_PARTICIPANT).distinct().all(): db.add(m.AgentStatusInDiscussion( profile_id=user_id, discussion_id=discussion_id, first_visit=now, last_visit=now, first_subscribed=now))
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: posts = db.query(m.WidgetPost).options( sa.orm.joinedload(m.WidgetPost.attachments)).filter( m.WidgetPost.metadata_raw.like('%://www.youtube.com/%')).all() for post in posts: url = post.metadata_json.get("inspiration_url", None) if not url: continue if not (url.startswith("https://www.youtube.com/") or url.startswith("http://www.youtube.com/")): # Should not happen, but elsewhere in metadata continue existing = {att.document.uri_id for att in post.attachments} if url in existing: continue document = db.query(m.Document).filter_by( uri_id=url, discussion_id=post.discussion_id).first() if not document: document = m.Document(uri_id=url, discussion_id=post.discussion_id) attachment = m.PostAttachment(discussion_id=post.discussion_id, creator_id=post.creator_id, document=document, attachmentPurpose='EMBED_ATTACHMENT', post=post) db.add(attachment) db.flush() # so document is available if repeated
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: synths = db.query(m.Synthesis).join( m.SubGraphIdeaAssociation, m.Idea, m.SynthesisPost).filter( (m.SynthesisPost.id != None) & (m.Idea.tombstone_date == None)).all() # reuse idea snapshots in this one case for synth in synths: snapshots = {} for assoc in synth.idea_assocs: idea = assoc.idea assoc.idea = idea.copy(True) snapshots[idea.id] = assoc.idea assoc.idea.tombstone_date = synth.creation_date # AND change the links. Sigh. synth.db.flush() snapshots = {id: idea.id for (id, idea) in snapshots.iteritems()} for link in synth.idea_links: assert link.is_tombstone id = link.source_id link.source_id = snapshots.get(id, id) id = link.target_id link.target_id = snapshots.get(id, id)
def upgrade(pyramid_env): from assembl import models as m from assembl.models.idea_content_link import ExtractStates, extract_states_identifiers with context.begin_transaction(): schema = config.get('db_schema') # User can be a machine op.add_column( 'user', sa.Column('is_machine', sa.Boolean(), default=False, server_default='0')) # Add the extract state: The extract can be Published or Submitted extract_states = sa.Enum(*extract_states_identifiers, name='extract_states') extract_states.create(op.get_bind()) op.add_column('extract', sa.Column('extract_state', sa.Enum(*extract_states_identifiers, name='extract_states'), nullable=False, default=ExtractStates.PUBLISHED.value, server_default=ExtractStates.PUBLISHED.value), schema=schema) # Add the machine user db = m.get_session_maker()() with transaction.manager: from assembl.indexing import join_transaction join_transaction() m.User.populate_db(db)
def upgrade(pyramid_env): with context.begin_transaction(): op.create_table( 'annotation_selector', sa.Column('id', sa.Integer, primary_key=True), sa.Column('extract_id', sa.Integer, sa.ForeignKey('extract.id', onupdate="CASCADE", ondelete="CASCADE"), nullable=False, index=True), sa.Column('type', sa.String(60)), sa.Column( 'refines_id', sa.Integer, sa.ForeignKey('annotation_selector.id', ondelete="CASCADE"))) op.execute(''' INSERT INTO annotation_selector (id, extract_id, type) SELECT id, extract_id, 'AnnotatorRange' FROM text_fragment_identifier''') op.create_foreign_key("text_fragment_identifier_id_fkey", "text_fragment_identifier", "annotation_selector", ["id"], ["id"], onupdate="CASCADE", ondelete="CASCADE") op.drop_column('text_fragment_identifier', 'extract_id') from assembl import models as m db = m.get_session_maker()() with transaction.manager: (maxid, ) = db.query('max(id) from annotation_selector').first() db.query("setval('annotation_selector_id_seq'::regclass, %d)" % (maxid, )).first()
def upgrade(pyramid_env): with context.begin_transaction(): op.create_table("locale", sa.Column("id", sa.Integer, primary_key=True), sa.Column("code", sa.String(20), unique=True), sa.Column("rtl", sa.Boolean, server_default="0")) op.create_table( "locale_label", sa.Column("id", sa.Integer, primary_key=True), sa.Column("named_locale_id", sa.Integer, sa.ForeignKey("locale.id", ondelete="CASCADE", onupdate="CASCADE"), nullable=False), sa.Column("locale_id_of_label", sa.Integer, sa.ForeignKey("locale.id", ondelete="CASCADE", onupdate="CASCADE"), nullable=False), sa.Column("name", sa.Unicode)) op.create_table("langstring", sa.Column('id', sa.Integer, primary_key=True)) op.create_table( "langstring_entry", sa.Column("id", sa.Integer, primary_key=True), sa.Column("langstring_id", sa.Integer, sa.ForeignKey("langstring.id", ondelete="CASCADE"), nullable=False, index=True), sa.Column("locale_id", sa.Integer, sa.ForeignKey("locale.id", ondelete="CASCADE", onupdate="CASCADE"), nullable=False), sa.Column("locale_identification_data", sa.String), sa.Column("locale_confirmed", sa.Boolean, server_default="0"), sa.Column("tombstone_date", sa.DateTime, server_default=None), sa.Column("value", sa.UnicodeText), sa.schema.UniqueConstraint("langstring_id", "locale_id", "tombstone_date")) # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() import simplejson as json names = json.load(open('assembl/nlp/data/language-names.json')) with transaction.manager: locales = {x[0] for x in names}.union({x[1] for x in names}) for l in locales: parts = l.split("_") rtl = parts[0] in rtl_locales or "_".join(parts[:2]) in rtl_locales db.add(m.Locale(code=l, rtl=rtl)) with transaction.manager: c = m.Locale.locale_collection for (l, t, n) in names: db.add( m.LocaleLabel(named_locale_id=c[l], locale_id_of_label=c[t], name=n))
def upgrade(pyramid_env): from assembl import models as m db = m.get_session_maker()() with transaction.manager: fk = next(iter( m.TimelineEvent.__table__.c.previous_event_id.foreign_keys)) rebuild_fkey(db, fk)
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): # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: for u in db.query(m.User).all(): u.creation_date = to_utc(u.creation_date).replace(tzinfo=None) if u.last_login: u.last_login = to_utc(u.last_login).replace(tzinfo=None) for w in db.query(m.CreativitySessionWidget).all(): settings = w.settings_json change = False for notification in settings.get('notifications', []): try: start = datetime.strptime( notification['start'], ISO_8601_FORMAT) notification['start'] = datetime.strftime( to_utc(start), ISO_8601_FORMAT) change = True end = notification.get('end', None) if end: end = datetime.strptime(end, ISO_8601_FORMAT) notification['end'] = datetime.strftime( to_utc(end), ISO_8601_FORMAT) except (ValueError, TypeError, KeyError): pass if change: w.settings_json = settings
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: dups = list( db.execute("""SELECT array_agg(id) FROM sub_graph_idea_association GROUP BY idea_id, sub_graph_id HAVING count(id) > 1""")) if dups: extras = list(chain(*[l[1:] for l in dups])) db.execute( 'DELETE FROM sub_graph_idea_association WHERE id IN (%s)' % (','.join(extras))) dups = list( db.execute( """SELECT array_agg(id) FROM sub_graph_idea_link_association GROUP BY idea_link_id, sub_graph_id HAVING count(id) > 1""")) if dups: extras = list(chain(*[l[1:] for l in dups])) db.execute( 'DELETE FROM sub_graph_idea_link_association WHERE id IN (%s)' % (','.join(extras))) with context.begin_transaction(): op.create_unique_constraint( "%s_%s_sub_graph_idea_association_UNQC_idea_id_sub_graph_id" % (config.get('db_schema'), config.get('db_user')), "sub_graph_idea_association", ["idea_id", "sub_graph_id"]) op.create_unique_constraint( "%s_%s_sub_graph_idea_link_association_UNQC_idea_link_id_sub_graph_id" % (config.get('db_schema'), config.get('db_user')), "sub_graph_idea_link_association", ["idea_link_id", "sub_graph_id"])
def upgrade(pyramid_env): with context.begin_transaction(): op.add_column('content', sa.Column('tombstone_date', sa.DateTime)) from assembl import models as m from assembl.auth import P_DELETE_MY_POST, P_DELETE_POST, P_ADD_POST, P_MODERATE from pyramid.security import Authenticated, Everyone db = m.get_session_maker()() with transaction.manager: # Give the P_DELETE_MY_POST permission to every role which already has the P_ADD_POST permission p_add_post = db.query(m.Permission).filter_by(name=P_ADD_POST).one() p_delete_my_post = db.query( m.Permission).filter_by(name=P_DELETE_MY_POST).one() dps = db.query( m.DiscussionPermission).filter_by(permission_id=p_add_post.id) for dp in dps: db.add( m.DiscussionPermission(discussion_id=dp.discussion_id, role_id=dp.role_id, permission_id=p_delete_my_post.id)) # Give the P_DELETE_POST permission to every role which already has the P_MODERATE permission p_moderate = db.query(m.Permission).filter_by(name=P_MODERATE).one() p_delete_post = db.query( m.Permission).filter_by(name=P_DELETE_POST).one() dps2 = db.query( m.DiscussionPermission).filter_by(permission_id=p_moderate.id) for dp in dps2: db.add( m.DiscussionPermission(discussion_id=dp.discussion_id, role_id=dp.role_id, permission_id=p_delete_post.id))
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): from assembl import models as m db = m.get_session_maker()() doc_re = re.compile(u'/data/Discussion/(?P<discussion_id>\d+)/documents/(?P<document_id>\d+)/data') with transaction.manager: # take the first sysadmin as creator sysadmin_role = db.query(m.Role).filter(m.Role.name == R_SYSADMIN).first() creator_id = m.User.default_db.query(m.User).join( m.User.roles).filter(m.Role.id == sysadmin_role.id)[0:1][0].id for thematic in db.query(m.Thematic).all(): if thematic.video_html_code: result = re.match(doc_re, thematic.video_html_code) if result: discussion_id = result.group('discussion_id') document_id = result.group('document_id') new_attachment = m.IdeaAttachment( idea=thematic, document_id=document_id, discussion_id=discussion_id, creator_id=creator_id, title=u'', attachmentPurpose=m.AttachmentPurpose.MEDIA_ATTACHMENT.value ) db.add(new_attachment) thematic.video_html_code = u'' db.flush()
def upgrade(pyramid_env): with context.begin_transaction(): op.add_column('content', sa.Column('tombstone_date', sa.DateTime)) from assembl import models as m from assembl.auth import P_DELETE_MY_POST, P_DELETE_POST, P_ADD_POST, P_MODERATE from pyramid.security import Authenticated, Everyone db = m.get_session_maker()() with transaction.manager: # Give the P_DELETE_MY_POST permission to every role which already has the P_ADD_POST permission p_add_post = db.query(m.Permission).filter_by(name=P_ADD_POST).one() p_delete_my_post = db.query(m.Permission).filter_by(name=P_DELETE_MY_POST).one() dps = db.query(m.DiscussionPermission).filter_by( permission_id=p_add_post.id) for dp in dps: db.add(m.DiscussionPermission( discussion_id = dp.discussion_id, role_id = dp.role_id, permission_id = p_delete_my_post.id)) # Give the P_DELETE_POST permission to every role which already has the P_MODERATE permission p_moderate = db.query(m.Permission).filter_by(name=P_MODERATE).one() p_delete_post = db.query(m.Permission).filter_by(name=P_DELETE_POST).one() dps2 = db.query(m.DiscussionPermission).filter_by( permission_id=p_moderate.id) for dp in dps2: db.add(m.DiscussionPermission( discussion_id = dp.discussion_id, role_id = dp.role_id, permission_id = p_delete_post.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 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: for d in db.query(m.Discussion).all(): d.get_participant_template()
def downgrade(pyramid_env): from virtuoso.textindex import TextIndex from assembl import models as m db = m.get_session_maker()() with transaction.manager: ti = TextIndex(m.Content.body) ti.drop(db.bind)
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: synths = db.query(m.Synthesis).join( m.SubGraphIdeaAssociation, m.Idea, m.SynthesisPost).filter( (m.SynthesisPost.id != None) & (m.Idea.tombstone_date == None)).all() # reuse idea snapshots in this one case for synth in synths: snapshots = {} for assoc in synth.idea_assocs: idea = assoc.idea assoc.idea = idea.copy(True) snapshots[idea.id] = assoc.idea assoc.idea.tombstone_date = synth.creation_date # AND change the links. Sigh. synth.db.flush() snapshots = {id: idea.id for (id, idea) in snapshots.items()} for link in synth.idea_links: assert link.is_tombstone id = link.source_id link.source_id = snapshots.get(id, id) id = link.target_id link.target_id = snapshots.get(id, id)
def downgrade(pyramid_env): from assembl import models as m db = m.get_session_maker()() with transaction.manager: ls = m.LangString.create(u'Video', 'en') ls.add_value(u'Vidéo', 'fr') db.add( m.LandingPageModuleType(identifier="VIDEO", title=ls, default_order=6.0, editable_order=True, required=False)) lpmt = db.query(m.LandingPageModuleType).filter( m.LandingPageModuleType.identifier == u"INTRODUCTION").first() if lpmt: ls = lpmt.title ls.add_entry( m.LangStringEntry(langstring=ls, value=u"Introduction", locale_id=m.Locale.get_id_of("en"))) ls.add_entry( m.LangStringEntry(langstring=ls, value=u"Introduction", locale_id=m.Locale.get_id_of("fr"))) db.flush()
def upgrade(pyramid_env): with context.begin_transaction(): op.add_column( 'abstract_agent_account', sa.Column("verified", sa.SmallInteger, default=False, server_default='0')) op.add_column('abstract_agent_account', sa.Column('email', sa.String(100), index=True)) # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: # Start with a blanket 0 db.execute("UPDATE abstract_agent_account SET verified=0") # get from previous values db.execute("""UPDATE abstract_agent_account SET email=( SELECT agent_email_account.email FROM agent_email_account WHERE abstract_agent_account.id = agent_email_account.id) WHERE abstract_agent_account."type" = 'agent_email_account'""") db.execute("""UPDATE abstract_agent_account SET verified=( SELECT agent_email_account.verified FROM agent_email_account WHERE abstract_agent_account.id = agent_email_account.id) WHERE abstract_agent_account."type" = 'agent_email_account'""") db.execute("""UPDATE abstract_agent_account SET verified=( SELECT identity_provider.trust_emails FROM identity_provider JOIN idprovider_agent_account ON ( idprovider_agent_account.provider_id = identity_provider.id) WHERE abstract_agent_account.id = idprovider_agent_account.id) WHERE abstract_agent_account."type" = 'idprovider_agent_account'""" ) db.flush() ipaccounts = db.query(m.IdentityProviderAccount).all() for ipaccount in ipaccounts: ipaccount.interpret_profile() if ipaccount.email: email_accounts = db.query( m.EmailAccount).filter_by(email=ipaccount.email).all() for email_account in email_accounts: if email_account.profile == ipaccount.profile: ipaccount.verified |= email_account.verified db.delete(email_account) elif ipaccount.verified and not email_account.verified: db.delete(email_account) else: # I checked that this case did not happen # in our existing databases ipaccount.profile.merge(email_account.profile) with context.begin_transaction(): db.execute( 'ALTER TABLE abstract_agent_account ADD CHECK (verified IN (0, 1))' ) op.drop_table('agent_email_account')
def upgrade(pyramid_env): with context.begin_transaction(): op.execute( 'UPDATE langstring_entry SET value=NULL WHERE length("value")=0') # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: data = list( db.execute("""SELECT content.subject_id, content.id FROM content WHERE subject_id in ( SELECT subject_id FROM content GROUP BY subject_id HAVING count(id) > 1)""")) data.sort() original_ls = None original_ls_id = None for subject_id, content_id in data: if original_ls_id != subject_id: original_ls_id = subject_id original_ls = m.LangString.get(subject_id) continue new_langstring = original_ls.clone(db) db.flush() db.execute("UPDATE content SET subject_id = %d WHERE id = %d" % (new_langstring.id, content_id))
def upgrade(pyramid_env): from assembl import models as m db = m.get_session_maker()() doc_re = re.compile( u'/data/Discussion/(?P<discussion_id>\d+)/documents/(?P<document_id>\d+)/data' ) with transaction.manager: # take the first sysadmin as creator sysadmin_role = db.query( m.Role).filter(m.Role.name == R_SYSADMIN).first() creator_id = m.User.default_db.query(m.User).join( m.User.roles).filter(m.Role.id == sysadmin_role.id)[0:1][0].id for thematic in db.query(m.Thematic).all(): if thematic.video_html_code: result = re.match(doc_re, thematic.video_html_code) if result: discussion_id = result.group('discussion_id') document_id = result.group('document_id') new_attachment = m.IdeaAttachment( idea=thematic, document_id=document_id, discussion_id=discussion_id, creator_id=creator_id, title=u'', attachmentPurpose=m.AttachmentPurpose.MEDIA_ATTACHMENT. value) db.add(new_attachment) thematic.video_html_code = u'' db.flush()
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 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: dups = list(db.execute( """SELECT array_agg(id) FROM sub_graph_idea_association GROUP BY idea_id, sub_graph_id HAVING count(id) > 1""")) if dups: extras = list(chain(*[l[1:] for l in dups])) db.execute( 'DELETE FROM sub_graph_idea_association WHERE id IN (%s)' % ( ','.join(extras))) dups = list(db.execute( """SELECT array_agg(id) FROM sub_graph_idea_link_association GROUP BY idea_link_id, sub_graph_id HAVING count(id) > 1""")) if dups: extras = list(chain(*[l[1:] for l in dups])) db.execute( 'DELETE FROM sub_graph_idea_link_association WHERE id IN (%s)' % ( ','.join(extras))) with context.begin_transaction(): op.create_unique_constraint( "%s_%s_sub_graph_idea_association_UNQC_idea_id_sub_graph_id" % ( config.get('db_schema'), config.get('db_user')), "sub_graph_idea_association", ["idea_id", "sub_graph_id"]) op.create_unique_constraint( "%s_%s_sub_graph_idea_link_association_UNQC_idea_link_id_sub_graph_id" % ( config.get('db_schema'), config.get('db_user')), "sub_graph_idea_link_association", ["idea_link_id", "sub_graph_id"])
def upgrade(pyramid_env): with context.begin_transaction(): op.create_table( 'feed_posts_source', sa.Column( 'id', sa.Integer, sa.ForeignKey( 'post_source.id', ondelete='CASCADE', onupdate='CASCADE'), primary_key=True), sa.Column( 'url', sa.String(1024), nullable=False), sa.Column( 'parser_full_class_name', sa.String(512), nullable=False)) op.create_table( 'weblink_user', sa.Column('id', sa.Integer, sa.ForeignKey( 'abstract_agent_account.id', ondelete='CASCADE', onupdate='CASCADE'), primary_key=True), sa.Column('user_link', sa.String(1024), unique=True)) # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: pass
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: # take the first sysadmin as creator sysadmin_role = db.query(m.Role).filter(m.Role.name == R_SYSADMIN).first() creator_id = m.User.default_db.query(m.User).join( m.User.roles).filter(m.Role.id == sysadmin_role.id)[0:1][0].id columns_headers = dict(list(db.execute( "SELECT id, header_id FROM idea_message_column"))) columns = db.query(m.IdeaMessageColumn).all() for column in columns: synthesis = column.get_column_synthesis() header_id = columns_headers.get(column.id, None) if header_id is not None and synthesis is None: name_en = column.name.closest_entry('en') or column.name.first_original() name_fr = column.name.closest_entry('fr') or column.name.first_original() subject_ls = m.LangString.create(u"Synthesis: {}".format(name_en.value), 'en') subject_ls.add_value(u"Synthèse : {}".format(name_fr.value), 'fr') body_ls = m.LangString.get(header_id) # don't clone, reuse the same langstring column.create_column_synthesis( subject=subject_ls, body=body_ls, creator_id=creator_id) with context.begin_transaction(): op.drop_column('idea_message_column', 'header_id')
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 upgrade(pyramid_env): from assembl import models as m db = m.get_session_maker()() with transaction.manager: orphans = db.query(m.Post).filter(m.Post.ancestry != '', m.Post.parent_id == None).all() for p in orphans: p.parent_id = int(p.ancestry.split(',')[-2])
def upgrade(pyramid_env): with context.begin_transaction(): op.create_table( "feed_posts_source", sa.Column( "id", sa.Integer, sa.ForeignKey("post_source.id", ondelete="CASCADE", onupdate="CASCADE"), primary_key=True, ), sa.Column("url", sa.String(1024), nullable=False), sa.Column("parser_full_class_name", sa.String(512), nullable=False), ) op.create_table( "weblink_user", sa.Column( "id", sa.Integer, sa.ForeignKey("abstract_agent_account.id", ondelete="CASCADE", onupdate="CASCADE"), primary_key=True, ), sa.Column("user_link", sa.String(1024), unique=True), ) # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: pass
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 upgrade(pyramid_env): with context.begin_transaction(): op.execute(""" UPDATE post p SET parent_id = COALESCE( ( SELECT new_post_parent.id AS new_post_parent_id FROM post AS post_to_correct JOIN post AS bad_post_parent ON (post_to_correct.parent_id = bad_post_parent.id) JOIN post AS new_post_parent ON (new_post_parent.message_id = bad_post_parent.message_id AND new_post_parent.id <> bad_post_parent.id) WHERE post_to_correct.parent_id IN ( SELECT MAX(post.id) as max_post_id FROM imported_post JOIN post ON (post.id=imported_post.id) GROUP BY message_id, source_id HAVING COUNT(post.id)>1 ) AND p.id = post_to_correct.id ), p.parent_id ) """) op.execute(""" DELETE FROM post WHERE post.id IN ( SELECT MAX(post.id) as max_post_id FROM imported_post JOIN post ON (post.id=imported_post.id) GROUP BY message_id, source_id HAVING COUNT(post.id)>1 ) """) op.add_column( 'imported_post', sa.Column( 'source_post_id', sa.Unicode(), nullable=False, index=True, )) op.execute(""" UPDATE imported_post p SET source_post_id = ( SELECT message_id FROM post WHERE p.id = post.id ) """) op.create_unique_constraint( config.get('db_schema') + "_" + config.get('db_user') + "_imported_post_UNQC_source_post_id_source_id", "imported_post", ["source_post_id", "source_id"]) pass # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: pass
def downgrade(pyramid_env): from virtuoso.textindex import TextIndex from assembl import models as m db = m.get_session_maker()() with transaction.manager: ti = TextIndex(m.Content.body, clusters=[m.Content.discussion_id]) ti.create(db.bind)
def upgrade(pyramid_env): with context.begin_transaction(): op.create_table( 'facebook_account', sa.Column('id', sa.Integer, sa.ForeignKey('idprovider_agent_account.id', onupdate='CASCADE', ondelete='CASCADE'), primary_key=True), sa.Column('app_id', sa.String(512))) op.create_table( 'facebook_source', sa.Column('id', sa.Integer, sa.ForeignKey('post_source.id', onupdate='CASCADE', ondelete='CASCADE'), primary_key=True), sa.Column('fb_source_id', sa.String(512), nullable=False), sa.Column('url_path', sa.String(1024)), sa.Column( 'creator_id', sa.Integer, sa.ForeignKey('facebook_account.id', onupdate='CASCADE', ondelete='CASCADE'))) op.create_table( 'facebook_post', sa.Column('id', sa.Integer, sa.ForeignKey('imported_post.id', onupdate='CASCADE', ondelete='CASCADE'), primary_key=True), sa.Column('attachment', sa.String(1024)), sa.Column('link_name', sa.Unicode(1024)), sa.Column('post_type', sa.String(20))) op.create_table( 'facebook_access_token', sa.Column('id', sa.Integer, primary_key=True), sa.Column( 'fb_account_id', sa.Integer, sa.ForeignKey('facebook_account.id', onupdate='CASCADE', ondelete='CASCADE')), sa.Column('token', sa.String(512), unique=True), sa.Column('expiration', sa.DateTime), sa.Column('token_type', sa.String(50)), sa.Column('object_name', sa.String(512)), sa.Column('object_fb_id', sa.String(512))) # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: pass
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(): op.drop_constraint(u'token_vote_specification_id_fkey', 'token_vote_specification', type_='foreignkey') op.create_foreign_key(None, 'token_vote_specification', 'vote_specification', ['id'], ['id'], ondelete='CASCADE', onupdate='CASCADE') op.drop_constraint(u'number_gauge_vote_specification_id_fkey', 'number_gauge_vote_specification', type_='foreignkey') op.create_foreign_key(None, 'number_gauge_vote_specification', 'vote_specification', ['id'], ['id'], ondelete='CASCADE', onupdate='CASCADE')
def upgrade(pyramid_env): from assembl import models as m from assembl.auth import R_PARTICIPANT db = m.get_session_maker()() with transaction.manager: db.query(m.UserTemplate).filter(m.UserTemplate.id.in_( db.query(m.UserTemplate.id).join(m.Role).filter( m.Role.name != R_PARTICIPANT).subquery()) ).delete(synchronize_session='fetch')
def upgrade(pyramid_env): with context.begin_transaction(): op.add_column('announce', sa.Column('title_id', sa.Integer(), sa.ForeignKey('langstring.id'))) op.add_column('announce', sa.Column('body_id', sa.Integer(), sa.ForeignKey('langstring.id'))) # Do stuff with the app's models here. from assembl import models as m from assembl.nlp.translation_service import LanguageIdentificationService db = m.get_session_maker()() # Disable idea reindexation from assembl.lib.sqla import BaseOps, orm_update_listener if sa.event.contains(BaseOps, 'after_update', orm_update_listener): sa.event.remove(BaseOps, 'after_update', orm_update_listener) with transaction.manager: ds = db.query(m.Discussion).all() locales_of_discussion = {d.id: d.discussion_locales for d in ds} langid_services = {d.id: LanguageIdentificationService(d) for d in ds if len(locales_of_discussion[d.id]) > 1} announcement_strings = db.execute( "SELECT id, title, body FROM announce") announcement_strings = {id: (title, body) for (id, title, body) in announcement_strings} for announcement in db.query(m.Announcement): candidate_langs = locales_of_discussion[announcement.discussion_id] (title, body) = announcement_strings[announcement.id] if len(candidate_langs) == 1: lang = candidate_langs[0] else: text = ' '.join(filter(None, ( title or '', sanitize_text(body or '')))) lang = None if text: # Use idea language for priors? lang, data = langid_services[announcement.discussion_id].identify(text) if not lang: print "***** Could not identify for announcement %d: %s" % (announcement.id, text) lang = candidate_langs[0] def as_lang_string(text): ls = m.LangString.create(text, lang) return ls if title: announcement.title = as_lang_string(title) if body: announcement.body = as_lang_string(body) with context.begin_transaction(): op.drop_column('announce', 'title') op.drop_column('announce', 'body')
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()