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 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 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 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 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 get_existing_id_lookup(self):
     contact_table = table(
         "contacts",
         column('id'),
         column('former_contact_id'))
     stmt = select([contact_table.c.former_contact_id, contact_table.c.id])
     return {key: value for (key, value) in self.db_session.execute(stmt)}

    def add_bucket_grouping_to_query(self, q, q_entities, entity, mapped_entity):
        # Get min, max if not provided.
        entity_min = 0
        entity_max = 0
        if (not entity.has_key('min') or not entity.has_key('max')):
            entity_min, entity_max = self.get_entity_min_max(entity)

        # Override calculated min/max if values were provided.
        if entity.has_key('min'): entity_min = entity.get('min')
        if entity.has_key('max'): entity_max = entity.get('max')

        num_buckets = entity.get('num_buckets', 10)
        entity_range = entity_max - entity_min

        bucket_width = (entity_max - entity_min)/float(num_buckets)

        # Get bucket field entities.

        # Can use the line below in case db doesn't have width_bucket function.
        #bucket_id_entity = func.greatest(func.round( (((mapped_entity - entity_min)/entity_range) * num_buckets ) - .5) + 1, num_buckets).label(self.get_bucket_id_label(entity))
        bucket_id_entity = func.width_bucket(mapped_entity, entity_min, entity_max, num_buckets).label(self.get_bucket_id_label(entity))
        q_entities.add(bucket_id_entity)
        bucket_label_entity = case(
                [(bucket_id_entity == num_buckets + 1, '[' + cast( entity_max, String) + ', ...)')], 
                else_ = '[' + cast(entity_min + bucket_width * (bucket_id_entity - 1), String ) + ', ' + cast(entity_min + bucket_width * (bucket_id_entity), String) + ')'  ).label(entity['label'])
        q_entities.add(bucket_id_entity)
        q_entities.add(bucket_label_entity)

        q = q.group_by(column(bucket_id_entity._label), column(bucket_label_entity._label))

        return q

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 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 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 insert_placeholders(self):
        """Fill empty MailAddress with a placeholder.
        """
        mailaddress_table = table('mail_addresses',
                                  column("id"),
                                  column("address"))

        self.execute(mailaddress_table.update()
                     .values(address=EMPTY_ADDRESS_PLACEHOLDER)
                     .where(mailaddress_table.columns.address == None))

    def migrate_data(self):
        """Temporarily insert placeholders as repository_folder_title,
        the real value will be inserted by the 4633 upgradestep.
        """
        default_language = get_preferred_language_code()

        proposal_table = table("proposals", column("id"), column("repository_folder_title"), column("language"))

        self.execute(proposal_table.update().values(repository_folder_title=u"-"))
        self.execute(proposal_table.update().values(language=default_language))

    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 insert_default_modified(self):
        """Insert time of migration as last modified timestamp."""

        meeting_table = table(
            'meetings',
            column('id'),
            column('modified'),
        )

        self.execute(meeting_table.update().values(modified=utcnow_tz_aware()))

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'))])

    def get_contact_mapping(self):
        if not self._contact_mapping:
            contact_table = table(
                "contacts",
                column('id'),
                column('former_contact_id'))
            stmt = select([contact_table.c.former_contact_id, contact_table.c.id])
            self._contact_mapping = {key: value for (key, value)
                                     in self.db_session.execute(stmt)}

        return self._contact_mapping

    def add_decision_sequence_to_period(self):
        self.op.add_column(
            'periods',
            Column('decision_sequence_number', Integer, nullable=True))

        periods_table = table(
            'periods',
            column('id'), column('decision_sequence_number'))
        self.execute(periods_table.update().values(decision_sequence_number=0))

        self.op.alter_column('periods', 'decision_sequence_number',
                             existing_type=Integer,
                             nullable=False)

    def migrate_data(self):
        watcher_id_mapping = self.get_watcherid_userid_mapping()
        notifications_table = table(
            "notifications", column("id"), column("activity_id"), column("watcher_id"), column("userid")
        )

        notifications = self.connection.execute(notifications_table.select()).fetchall()
        for notification in notifications:
            userid = watcher_id_mapping[notification.watcher_id]
            self.execute(
                notifications_table.update()
                .values(userid=userid)
                .where(notifications_table.columns.id == notification.id)
            )

    def insert_org_units_group_id(self):
        """Insert the current org-unit's group_id for all committees.

        Bye default we use the users group. This choice is somewhat arbitrary,
        but id does not really matter since meeting is not in production when
        this upgrade is executed.

        """
        proposal_table = table("committees",
                               column("id"),
                               column("group_id"))
        group_id = get_current_org_unit().users_group.groupid

        self.execute(
            proposal_table.update().values(group_id=group_id))

    def migrate_held_state(self):
        """The state held lost its relevance and is equivalent to pending.

        The migrations thus resets all meetings in the state held to the state
        pending.

        """
        meeting_table = table("meetings",
                              column("id"),
                              column("workflow_state"))

        self.execute(
            meeting_table.update()
                         .values(workflow_state='pending')
                         .where(meeting_table.c.workflow_state == 'held'))