def set_reset_id(self, resetId):
     prt = self.passwordResetTable
     i = prt.insert()
     session = getSession()
     session.execute(i, params={'verification_id': resetId,
                                'user_id': self.userInfo.id})
     mark_changed(session)

    def remove_address(self, address):
        uet = self.userEmailTable
        d = uet.delete(sa.func.lower(uet.c.email) == address.lower())

        session = getSession()
        session.execute(d)
        mark_changed(session)

    def save_items(self):
        self.lgg.info("Saving...")
        sess = self.sess
        items = self.items
        known_items = self.known_items
        t = Item.__table__

        # 1. Assume all items are unchanged
        fil = exclude_filter()
        fil.append(t.c.path.like(self.start_dir + '%'))
        sess.execute(
            t.update().where(sa.and_(*fil)),
            {'state': ITEM_STATE_UNCHANGED}
        )
        # 2. Prepare
        updates = []
        inserts = []
        for p, d in items.items():
            if d['action'] == ACTION_UPDATE:
                updates.append({
                    'p': p,
                    'state': ITEM_STATE_NEED_ANALYSIS,
                    'mime_type': d['mime_enc'][0],
                    'encoding': d['mime_enc'][1],
                    'item_ctime': d['item_ctime'],
                    'item_mtime': d['item_mtime'],
                    'size': d['os_stat'].st_size,
                    'os_stat': {a: getattr(d['os_stat'], a) for a in STAT_ATTR},
                })
            elif d['action'] == ACTION_INSERT:
                inserts.append({
                    'path': p,
                    'state': ITEM_STATE_NEED_ANALYSIS,
                    'mime_type': d['mime_enc'][0],
                    'encoding': d['mime_enc'][1],
                    'item_ctime': d['item_ctime'],
                    'item_mtime': d['item_mtime'],
                    'size': d['os_stat'].st_size,
                    'os_stat': {a: getattr(d['os_stat'], a) for a in STAT_ATTR},
                })
        deletes = [k for k, v in known_items.items() if v is None]
        # 3. Update
        if updates:
            self.lgg.debug("Updating")
            upd = t.update().where(t.c.path == sa.bindparam('p'))
            sess.execute(upd, updates)
        # 4. inserts
        if inserts:
            self.lgg.debug("Inserting")
            ins = t.insert()
            sess.execute(ins, inserts)
        # 5. deletes
        if deletes:
            self.lgg.debug("Deleting")
            fil = exclude_filter()
            fil.append(t.c.path.in_(deletes))
            upd = t.update().where(sa.and_(*fil))
            sess.execute(upd, {'state': ITEM_STATE_NEED_DELETION})
        # 6. Flush
        mark_changed(sess)

def setup_and_teardown(app):
    """
    Run create mapping and purge queue before tests and clear out the
    DB tables after the test
    """
    import transaction
    from sqlalchemy import MetaData
    from zope.sqlalchemy import mark_changed
    # BEFORE THE TEST - just run CM for the TEST_TYPE by default
    create_mapping.run(app, collections=[TEST_TYPE], skip_indexing=True)
    app.registry[INDEXER_QUEUE].clear_queue()

    yield  # run the test

    # AFTER THE TEST
    session = app.registry[DBSESSION]
    connection = session.connection().connect()
    meta = MetaData(bind=session.connection(), reflect=True)
    for table in meta.sorted_tables:
        print('Clear table %s' % table)
        print('Count before -->', str(connection.scalar("SELECT COUNT(*) FROM %s" % table)))
        connection.execute(table.delete())
        print('Count after -->', str(connection.scalar("SELECT COUNT(*) FROM %s" % table)), '\n')
    session.flush()
    mark_changed(session())
    transaction.commit()

def run_migrations_online():
    if DBSession.bind is None:
        raise ValueError(
            "\nYou must run Kotti's migration using the 'kotti-migrate' script"
            "\nand not through 'alembic' directly."
            )

    transaction.begin()
    connection = DBSession.connection()

    context.configure(
        connection=connection,
        target_metadata=metadata,
        )

    try:
        context.run_migrations()
        mark_changed(DBSession())
    except:
        traceback.print_exc()
        transaction.abort()
    else:
        transaction.commit()
    finally:
        # connection.close()
        pass

def migrate_datas():
    from autonomie_base.models.base import DBSESSION
    session = DBSESSION()

    from alembic.context import get_bind
    conn = get_bind()

    for row in list(conn.execute('SELECT id, leaders FROM workshop')):
        if not row.leaders:
            continue

        try:
            leaders_list = json.loads(row.leaders)
        except ValueError:
            # This should not happen, but some dumps we use have a bare string
            # in leaders field.
            leaders_list = [row.leaders]

        req = sa.text("""
            UPDATE workshop SET
            description=CONCAT(
              'Formateurs: ',
              IFNULL(:leaders, ''),
              ' ',
              IFNULL(description, '')
            )
            WHERE id=:id_
            """)
        conn.execute(
            req,
            leaders=', '.join(leaders_list),
            id_=row.id,
        )
    mark_changed(session)
    session.flush()

 def testAbortAfterCommit(self):
     # This is a regression test which used to wedge the transaction
     # machinery when using PostgreSQL (and perhaps other) connections.
     # Basically, if a commit failed, there was no way to abort the
     # transaction. Leaving the transaction wedged.
     transaction.begin()
     session = Session()
     conn = session.connection()
     # At least PostgresSQL requires a rollback after invalid SQL is executed
     self.assertRaises(Exception, conn.execute, "BAD SQL SYNTAX")
     mark_changed(session)
     try:
         # Thus we could fail in commit
         transaction.commit()
     except:
         # But abort must succed (and actually rollback the base connection)
         transaction.abort()
         pass
     # Or the next transaction the next transaction will not be able to start!
     transaction.begin()
     session = Session()
     conn = session.connection()
     conn.execute("SELECT 1 FROM test_users")
     mark_changed(session)
     transaction.commit()

    def clear_nicknames(self):
        unt = self.nicknameTable
        d = unt.delete(unt.c.user_id == self.user_id)

        session = getSession()
        session.execute(d)
        mark_changed(session)

def app(request, wsgi, db_session):
    """
    (Functional Testing) Initiates a user request against a WSGI stack

    :param request: The pytest context
    :param wsgi: An initialized WSGI stack
    :param db_session: A database session for seting up pre-existing data

    :returns: a test app request against the WSGI instance
    """
    import transaction
    from webtest import TestApp
    from zope.sqlalchemy import mark_changed

    app = TestApp(wsgi)

    yield app

    with transaction.manager:
        # DELETE is dramatically faster than TRUNCATE
        # http://stackoverflow.com/a/11423886/148781
        # We also have to do this as a raw query becuase SA does
        # not have a way to invoke server-side cascade
        db_session.execute('DELETE FROM "location"')
        db_session.execute('DELETE FROM "site"')
        db_session.execute('DELETE FROM "study"')
        db_session.execute('DELETE FROM "specimentype"')
        db_session.execute('DELETE FROM "user"')
        mark_changed(db_session)

    def run(self):
        root_pwd = self.rc.g('auth.user_root.pwd')
        self._config.scan('pym')
        sess = self._sess

        # Create schema 'pym' and all models
        with transaction.manager:
            self._create_schema(sess)
        with transaction.manager:
            pym.models.create_all()

        with transaction.manager:
            # Users and stuff we need to setup the other modules
            pym.auth.setup.setup_basics(sess, root_pwd,
                schema_only=self.args.schema_only)
            sess.flush()
            # Setup each module
            pym.res.setup.setup(sess, schema_only=self.args.schema_only)
            sess.flush()
            pym.auth.setup.setup(sess, schema_only=self.args.schema_only)
            sess.flush()
            pym.tenants.setup.setup(sess, schema_only=self.args.schema_only)
            sess.flush()

            if self.args.alembic_config:
                alembic_cfg = Config(self.args.alembic_config)
                command.stamp(alembic_cfg, "head")

            mark_changed(sess)

    def set_groupEmailSetting(self, setting):
        """ Given a site_id, group_id and a setting, set the email_setting
            table.
        """
        if setting not in self.possible_settings:
            raise ValueError("Unknown setting %s" % setting)
        est = self.emailSettingTable
        and_ = sa.and_
        curr_setting = self.get_groupEmailSetting()
        if not curr_setting:
            iOrU = est.insert()
            d = {'user_id': self.userId,
                 'site_id': self.siteId,
                 'group_id': self.groupId,
                 'setting': setting}

        else:
            iOrU = est.update(and_(est.c.user_id == self.userId,
                                   sa.or_(est.c.site_id == self.siteId,
                                          est.c.site_id == ''),
                                   est.c.group_id == self.groupId))
            d = {'setting': setting, }

        session = getSession()
        session.execute(iOrU, params=d)
        mark_changed(session)

    def restore(self, target, with_children=True):
        """ Restores `target` from the trash

        By default `target` and all its children will be restored. Specify
        `with_children=False` if you want to only restore `target` and not its
        children.

        Note that this method will expire all objects in the current session.

        :param target: Content type to restore from the trash
        :type target: :class:`~yoshimi.content.ContentType`
        :param bool with_children: Whether to include children
        """
        if with_children:
            self._delete_trash_entries(target)
            self._set_content_status(
                target,
                Content.status.TRASHED,
                Content.status.AVAILABLE,
            )
            mark_changed(self._session)
            self._session.expire_all()
        else:
            self._session.delete(target.trash_info)
            target.status_id = target.status.AVAILABLE
            self._session.add(target)

    def clear_preferredEmail(self):
        uet = self.userEmailTable
        u = uet.update(uet.c.user_id == self.user_id)
        d = {'is_preferred': False}

        session = getSession()
        session.execute(u, params=d)
        mark_changed(session)

def migrate_datas():
    from autonomie_base.models.base import DBSESSION
    session = DBSESSION()
    from alembic.context import get_bind
    conn = get_bind()
    op.execute('update customer set civilite="M. et Mme" where civilite="mr&mme"')
    from zope.sqlalchemy import mark_changed
    mark_changed(session)

    def unverify_address(self):
        uet = self.userEmailTable
        u = uet.update(sa.func.lower(uet.c.email) == self.email.lower())
        d = {'verified_date': None}

        session = getSession()
        session.execute(u, params=d)
        mark_changed(session)

 def clear_reset_ids(self):
     prt = self.passwordResetTable
     u = prt.update(sa.and_(prt.c.user_id == self.userInfo.id,
                             prt.c.reset == None))  # lint:ok
     d = datetime.datetime.utcnow().replace(tzinfo=pytz.utc)
     session = getSession()
     session.execute(u, params={'reset': d})
     mark_changed(session)

 def add_request(self, requestId, userId, message, siteId, groupId):
     now = datetime.now(UTC)
     i = self.requestTable.insert()
     d = {"request_id": requestId, "user_id": userId,
          "message": message, "site_id": siteId,
          "group_id": groupId, "request_date": now}
     session = getSession()
     session.execute(i, params=d)
     mark_changed(session)

def migrate_datas():
    from autonomie_base.models.base import DBSESSION
    session = DBSESSION()
    #from alembic.context import get_bind
    #conn = get_bind()
    op.execute(
        "UPDATE event SET signup_mode = 'closed' WHERE signup_mode = '';"
    )
    mark_changed(session)

 def do_stamp(rev, context, revision=revision):
     current = context._current_rev()
     if revision is None:
         revision = context.script.get_current_head()
     elif revision == 'None':
         revision = None
     context._update_current_rev(current, revision)
     mark_changed(DBSession())
     return []

 def update_hidden_post_table(self, postId, dt, userId, reason):
     i = self.hiddenPostTable.insert()
     session = getSession()
     d = {'post_id': postId,
          'date_hidden': dt,
          'hiding_user': userId,
          'reason': reason}
     session.execute(i, params=d)
     mark_changed(session)