def get_existing_id_lookup(self):
        org_role_table = table("org_roles",
                               column('id'),
                               column('organization_id'),
                               column('person_id'),
                               column('function'))

        contact_table = table(
            "contacts",
            column('id'),
            column('former_contact_id'))

        org_contact = aliased(contact_table, alias='org_contact')
        person_contact = aliased(contact_table, alias='person_contact')

        query = self.db_session.query(org_role_table, org_contact, person_contact)
        query = query.join(org_contact,
                           org_role_table.c.organization_id == org_contact.c.id)
        query = query.join(person_contact,
                           org_role_table.c.person_id == person_contact.c.id)

        mapping = {}

        for gever_row in query:
            key = u'{}:{}'.format(gever_row[5], gever_row[7])
            mapping[key] = gever_row[0]

        return mapping

    def migrate_data(self):
        activities_table = table(
            "activities", column("id"), column("kind"), column("summary"), column("title"), column("description")
        )

        activities_translation_table = table(
            "activities_translation",
            column("id"),
            column("locale"),
            column("title"),
            column("label"),
            column("summary"),
            column("description"),
        )

        activities = self.connection.execute(activities_table.select()).fetchall()
        for activity in activities:
            self.execute(
                activities_translation_table.insert(
                    values={
                        "id": activity.id,
                        "locale": DEFAULT_LOCALE,
                        "title": activity.title,
                        # the label column is new so we use the kind
                        # for existing entries
                        "label": activity.kind,
                        "summary": activity.summary,
                        "description": activity.description,
                    }
                )
            )

def _migrate_network_segments(engine):
    # Code similar to migrate_to_ml2.BaseMigrateToMl2.migrate_network_segments
    source_table = sa_expr.table('hyperv_network_bindings')
    source_segments = engine.execute(
        sa_expr.select(['*'], from_obj=source_table))
    ml2_segments = [dict(x) for x in source_segments]
    for segment in ml2_segments:
        _migrate_segment_dict(segment)

    if ml2_segments:
        ml2_network_segments = sa_expr.table('ml2_network_segments')
        op.execute(ml2_network_segments.insert(), ml2_segments)

def upgrade(active_plugins=None, options=None):
    op.add_column(
        'branches',
        sa.Column('restricted', sa.Boolean(), default=False)
    )

    bind = op.get_bind()

    branches_table = table(
        'branches',
        sa.Column('name', sa.String(100), nullable=True),
        sa.Column('restricted', sa.Boolean(), default=False)
    )

    bind.execute(
        branches_table.update().where(
            branches_table.c.name != 'master'
        ).values(restricted=False)
    )

    bind.execute(
        branches_table.update().where(
            branches_table.c.name == 'master'
        ).values(restricted=True)
    )

def upgrade():
    op.create_table(
        "planet_name", sa.Column("id", sa.Integer, primary_key=True), sa.Column("name", sa.String, nullable=False)
    )
    op.create_unique_constraint("uc_planet_name", "planet_name", ["name"])
    op.add_column(
        "libration", sa.Column("first_planet_name_id", sa.Integer, sa.ForeignKey("planet_name.id"), nullable=True)
    )
    op.add_column(
        "libration", sa.Column("second_planet_name_id", sa.Integer, sa.ForeignKey("planet_name.id"), nullable=True)
    )

    planet_name_table = table("planet_name", column("id", sa.Integer), column("name", sa.String))
    op.bulk_insert(
        planet_name_table,
        [
            {"id": 9, "name": "PLUTO"},
            {"id": 8, "name": "NEPTUNE"},
            {"id": 7, "name": "URANUS"},
            {"id": 6, "name": "SATURN"},
            {"id": 5, "name": "JUPITER"},
            {"id": 4, "name": "MARS"},
            {"id": 3, "name": "EARTHMOO"},
            {"id": 2, "name": "VENUS"},
            {"id": 1, "name": "MERCURY"},
        ],
    )

    op.execute("UPDATE libration SET first_planet_name_id=5, second_planet_name_id=6")

def upgrade(active_plugins=None, options=None):
    op.create_table(
        "story_types",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("created_at", sa.DateTime(), nullable=True),
        sa.Column("updated_at", sa.DateTime(), nullable=True),
        sa.Column("name", sa.String(50), nullable=True),
        sa.Column("icon", sa.String(50), nullable=True),
        sa.Column("restricted", sa.Boolean(), default=False),
        sa.Column("private", sa.Boolean(), default=False),
        sa.Column("visible", sa.Boolean(), default=True),
        sa.PrimaryKeyConstraint("id"),
        mysql_engine=MYSQL_ENGINE,
        mysql_charset=MYSQL_CHARSET,
    )

    bind = op.get_bind()

    story_types_table = table(
        "story_types",
        sa.Column("name", sa.String(50), nullable=True),
        sa.Column("icon", sa.String(50), nullable=True),
        sa.Column("restricted", sa.Boolean(), default=False),
        sa.Column("private", sa.Boolean(), default=False),
        sa.Column("visible", sa.Boolean(), default=True),
    )

    bind.execute(story_types_table.insert().values(name="bug", icon="fa-bug"))

    bind.execute(story_types_table.insert().values(name="feature", icon="fa-lightbulb-o", restricted=True))

    bind.execute(story_types_table.insert().values(name="private_vulnerability", icon="fa-lock", private=True))

    bind.execute(story_types_table.insert().values(name="public_vulnerability", icon="fa-bomb", visible=False))

    def migrate_data(self):
        meeting_table = table(
            "meetings",
            column("id"),
            column("date"),
            column("start_time"),
            column("end_time"),
            column("start"),
            column("end"),
        )

        meetings = self.connection.execute(meeting_table.select()).fetchall()
        for meeting in meetings:
            date = meeting.date
            start_time = meeting.start_time or datetime.min.time()
            end_time = meeting.end_time or datetime.min.time()

            start = datetime.combine(date, start_time)
            end = datetime.combine(date, end_time)

            self.execute(
                meeting_table.update()
                .values(start=start, end=end)
                .where(meeting_table.columns.id == meeting.id)
            )

def downgrade():
    op.create_table(
        'planet_name',
        sa.Column('id', sa.Integer, primary_key=True),
        sa.Column('name', sa.String, nullable=False),
    )
    op.create_unique_constraint('uc_planet_name', 'planet_name', ['name'])
    op.add_column('libration', sa.Column(
        'first_planet_name_id', sa.Integer, sa.ForeignKey('planet_name.id'), nullable=True
    ))
    op.add_column('libration', sa.Column(
        'second_planet_name_id', sa.Integer, sa.ForeignKey('planet_name.id'), nullable=True
    ))

    planet_name_table = table('planet_name', column('id', sa.Integer), column('name', sa.String))
    op.bulk_insert(planet_name_table, [
        {'id': 9, 'name': 'PLUTO'},
        {'id': 8, 'name': 'NEPTUNE'},
        {'id': 7, 'name': 'URANUS'},
        {'id': 6, 'name': 'SATURN'},
        {'id': 5, 'name': 'JUPITER'},
        {'id': 4, 'name': 'MARS'},
        {'id': 3, 'name': 'EARTHMOO'},
        {'id': 2, 'name': 'VENUS'},
        {'id': 1, 'name': 'MERCURY'},
    ])

    op.execute('UPDATE libration SET first_planet_name_id=5, second_planet_name_id=6')

    op.alter_column('libration', 'first_planet_name_id', nullable=False)
    op.alter_column('libration', 'second_planet_name_id', nullable=False)
    op.drop_constraint('libration_resonance_id_key', 'libration')
    op.create_unique_constraint('uc_resonance_planet_names', 'libration',
                                ['resonance_id', 'first_planet_name_id', 'second_planet_name_id'])

def upgrade(active_plugins=None, options=None):
    bind = op.get_bind()

    branches = list(bind.execute(
        sa.select(columns=['id', 'name', 'project_id'],
                  from_obj=sa.Table('branches', sa.MetaData()))))

    projects = list(bind.execute(
        sa.select(columns=['id'], from_obj=sa.Table('projects',
                                                    sa.MetaData()))))
    if len(projects) > 0:
        branch_dict = {}

        for branch in branches:
            branch_dict[(branch['project_id'], branch['name'])] = branch['id']

        tasks_table = table(
            'tasks',
            sa.Column('project_id', sa.Integer(), nullable=True),
            sa.Column('branch_id', sa.Integer(), nullable=True),
        )

        bind.execute(tasks_table.update().
                     where(tasks_table.c.project_id.is_(None)).
                     values(project_id=projects[0].id))

        for project in projects:
            bind.execute(
                tasks_table.update().
                where(tasks_table.c.project_id == project['id']).
                values(branch_id=branch_dict[(project['id'], "master")])
            )

    def migrate(self):
        self.add_userid_column()
        self.migrate_data()
        self.remove_watcherid_column()
        self.make_userid_column_required()

        self.notifications_table = table("notifications", column("id"), column("activity_id"), column("watcher_id"))

    def raise_column_length(self):

        # add new column with the new size
        self.op.add_column(
            'favorites',
            Column('tmp_plone_uid', String(UID_LENGTH)))

        # migrate_data
        _table = table(
            'favorites',
            column("id"),
            column('plone_uid'),
            column('tmp_plone_uid'))

        items = self.connection.execute(_table.select()).fetchall()
        for item in items:
            self.execute(
                _table.update()
                .values(tmp_plone_uid=item.plone_uid)
                .where(_table.columns.id == item.id)
            )

        # drop old column
        self.op.drop_column('favorites', 'plone_uid')

        # rename new column
        self.op.alter_column('favorites',
                             'tmp_plone_uid',
                             new_column_name='plone_uid')

    def add_meeting_sequence_to_period(self):
        self.op.add_column("periods", Column("meeting_sequence_number", Integer, nullable=True))

        periods_table = table("periods", column("id"), column("meeting_sequence_number"))
        self.execute(periods_table.update().values(meeting_sequence_number=0))

        self.op.alter_column("periods", "meeting_sequence_number", existing_type=Integer, nullable=False)

def _migrate_port_bindings(engine):
    # Code similar to migrate_to_ml2.BaseMigrateToMl2.migrate_port_bindings
    port_segment_map = _get_port_segment_map(engine)
    port_binding_ports = sa_expr.table('portbindingports')
    source_bindings = engine.execute(
        sa_expr.select(['*'], from_obj=port_binding_ports))
    ml2_bindings = [dict(x) for x in source_bindings]
    for binding in ml2_bindings:
        binding['vif_type'] = portbindings.VIF_TYPE_HYPERV
        binding['driver'] = HYPERV
        segment = port_segment_map.get(binding['port_id'])
        if segment:
            binding['segment'] = segment
    if ml2_bindings:
        ml2_port_bindings = sa_expr.table('ml2_port_bindings')
        op.execute(ml2_port_bindings.insert(), ml2_bindings)

    def create_default_periods_for_committees(self):
        self.committees_table = table("committees", column("id"))
        self.date = date.today()
        self.end_of_year = date(self.date.year, 12, 31)

        for committee in self.connection.execute(
                self.committees_table.select()).fetchall():
            self.create_initial_period(committee)

    def insert_default_value(self, tablename):
        contact_table = table(
            tablename,
            column("id"),
            column("is_active"))

        self.connection.execute(
            contact_table.update().values(is_active=True))

def view(name, metadata, selectable):
    t = table(name)

    for c in selectable.c:
        c._make_proxy(t)

    CreateView(name, selectable).execute_at('after-create', metadata)
    DropView(name).execute_at('before-drop', metadata)
    return t

    def get_watcherid_userid_mapping(self):
        mapping = {}
        watchers_table = table("watchers", column("id"), column("user_id"))

        watchers = self.connection.execute(watchers_table.select()).fetchall()
        for watcher in watchers:
            mapping[watcher.id] = watcher.user_id

        return mapping

    def initialize(cls, url, initialize_data=True, initialize_schema=True):
        """Initialize the database.

        This includes the schema, the materialized views, the custom
        functions, and the initial content.
        """
        if url in cls.engine_for_url:
            engine = cls.engine_for_url[url]
            return engine, engine.connect()

        engine = cls.engine(url)
        if initialize_schema:
            cls.initialize_schema(engine)
        connection = engine.connect()

        # Check if the recursive equivalents function exists already.
        query = select(
            [literal_column('proname')]
        ).select_from(
            table('pg_proc')
        ).where(
            literal_column('proname')=='fn_recursive_equivalents'
        )
        result = connection.execute(query)
        result = list(result)

        # If it doesn't, create it.
        if not result and initialize_data:
            resource_file = os.path.join(
                cls.resource_directory(), cls.RECURSIVE_EQUIVALENTS_FUNCTION
            )
            if not os.path.exists(resource_file):
                raise IOError("Could not load recursive equivalents function from %s: file does not exist." % resource_file)
            sql = open(resource_file).read()
            connection.execute(sql)

        if initialize_data:
            session = Session(connection)
            cls.initialize_data(session)

        if connection:
            connection.close()

        if initialize_schema and initialize_data:
            # Only cache the engine if all initialization has been performed.
            #
            # Some pieces of code (e.g. the script that runs
            # migrations) have a legitimate need to bypass some of the
            # initialization, but normal operation of the site
            # requires that everything be initialized.
            #
            # Until someone tells this method to initialize
            # everything, we can't short-circuit this method with a
            # cache.
            cls.engine_for_url[url] = engine
        return engine, engine.connect()

    def migrate_data(self):
        """Temporarily insert placeholders as dossier_reference_numbers,
        the real value will be inserted by the 4603 upgradestep.
        """
        proposal_table = table("proposals",
                               column("id"),
                               column("dossier_reference_number"))

        self.execute(
            proposal_table.update().values(dossier_reference_number=u'-'))

def add_admin_user():
    stmt = table('users',
            column('username'),
            column('_password_hash')).insert().\
                values\
                  (
                    username=sa.bindparam('username'),
                    _password_hash=sa.bindparam('_password_hash'),
                  )
    op.get_bind().execute(stmt,[dict(username='admin',_password_hash=pw('admin'))])