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')
Exemplo n.º 2
0
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()
Exemplo n.º 3
0
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)
Exemplo n.º 5
0
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")
Exemplo n.º 7
0
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))
Exemplo n.º 11
0
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'})
Exemplo n.º 13
0
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))
Exemplo n.º 16
0
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
Exemplo n.º 17
0
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
    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)
Exemplo n.º 20
0
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()
Exemplo n.º 21
0
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))
Exemplo n.º 24
0
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
Exemplo n.º 25
0
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):
    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()
Exemplo n.º 28
0
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))
Exemplo n.º 31
0
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()
Exemplo n.º 33
0
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)
Exemplo n.º 34
0
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)
Exemplo n.º 35
0
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()
Exemplo n.º 36
0
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')
Exemplo n.º 37
0
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))
Exemplo n.º 38
0
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"])
Exemplo n.º 41
0
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
Exemplo n.º 42
0
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])
Exemplo n.º 45
0
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 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
Exemplo n.º 47
0
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
Exemplo n.º 49
0
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 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)
Exemplo n.º 51
0
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(
            '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
Exemplo n.º 53
0
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()