def api_token_redeem():
    try:
        auth_token = request.form["auth_token"]

        token_code = request.form["token_code"]
    except KeyError as e:
        raise APIMissingField(e.args[0])

    caller = get_user_from_token(auth_token)

    conn = engine.connect()

    query = sql.select([Token]).where(Token.code == token_code).limit(1)
    res = conn.execute(query)
    try:
        token = res.fetchall()[0]
    except IndexError:
        raise APIInvalidField("token_code", 200)

    query1 = sql.update(Token).where(Token.id == token.id).values({
        Token.redeemed: True,
        Token.redeemer: caller.id
    })
    query2 = sql.update(User).where(User.id == caller.id).values({
        User.credits: caller.credits + token.value
    })
    res1 = conn.execute(query1)
    res2 = conn.execute(query2)

    if res1.inserted_primary_key and res2.inserted_primary_key:
        return make_response(jsonify({
            "status": "success",
            "message": "Token successfully redeemed",
            "new_balance": caller.credits + token.value
        }))

def update_changed(src_table, dim_table):
	"""Update changed records"""

	# create condition
	where_clause = and_(or_(src_table.c.name != dim_table.c.name, src_table.c.address != dim_table.c.address), dim_table.c.to_date == None)

	# join table based on their ids
	joined_table = src_table.join(dim_table, src_table.c.id == dim_table.c.id)

	# create statement for table with only changed records
	change_table = sql.select(src_table.c, whereclause=where_clause, from_obj=joined_table)

	# update records in dim table and create new records
	for row in change_table.execute():
		# create condition
		where_clause = and_(dim_table.c.id == row.id, dim_table.c.to_date == None)

		# create current timestamp
		timestamp = sql.functions.current_timestamp()

    	# update record
		sql.update(dim_table, whereclause=where_clause, values={dim_table.c.to_date:timestamp}).execute()
		print "Updating row", str(row)

    	# insert record
		engine.execute(dim_table.insert(), row)

def fix_foreign_links(session, oldnet, newnet):
    """
    Fix foreign keys that point to the network table

    When a network is split or multiple networks are merged, foreign keys
    must be updated accordingly. Do not use the size of the old network,
    as it has already been updated when this function gets called.
    """
    session.execute(
        update(AddressAssignment.__table__,
               values={'network_id': newnet.id})
        .where(and_(AddressAssignment.network_id == oldnet.id,
                    AddressAssignment.ip >= newnet.ip,
                    AddressAssignment.ip <= newnet.broadcast))
    )
    session.expire(oldnet, ['assignments'])
    session.expire(newnet, ['assignments'])

    session.execute(
        update(ARecord.__table__,
               values={'network_id': newnet.id})
        .where(and_(ARecord.network_id == oldnet.id,
                    ARecord.ip >= newnet.ip,
                    ARecord.ip <= newnet.broadcast))
    )
    session.expire(oldnet, ['dns_records'])
    session.expire(newnet, ['dns_records'])

def _add_shards(engine, table, dst_col, src_col):
    dialect = engine.url.get_dialect().name
    if dialect.startswith('mysql'):
        sql = "UPDATE %s SET %s = CONV(SUBSTR(%s, 1, 3), 16, 10)"
        engine.execute(sql % (table.name, dst_col.name, src_col.name))
    elif dialect.startswith('postgres'):
        sql = "UPDATE %s SET %s = ('x'||lpad(substr(%s::text, 1, 3), 8, '0')"\
            ")::bit(32)::int"
        engine.execute(sql % (table.name, dst_col.name, src_col.name))
    else:
        rows = select(columns=[table.c.id]).execute().fetchall()
        for r in rows:
            shard = int(r.id[0:3], 16)
            values = {dst_col.name: shard}
            update(table).where(table.id == r.id).values(values)

def upgrade(migrate_engine):
    TableBase.metadata.bind = migrate_engine

    Discussion.__table__.create()
    Comment.__table__.create()

    Artwork.__table__.c.discussion_id.nullable = True
    Artwork.__table__.c.discussion_id.create()
    User.__table__.c.discussion_id.nullable = True
    User.__table__.c.discussion_id.create()

    # Create a new discussion for each artwork and user
    conn = migrate_engine.connect()
    tr = conn.begin()

    for table in Artwork, User:
        for id, in conn.execute( sql.select([table.id]) ):
            res = conn.execute( sql.insert(Discussion.__table__) )
            discussion_id = res.inserted_primary_key[0]

            conn.execute(sql.update(
                table.__table__,
                table.__table__.c.id == id,
                dict(discussion_id=discussion_id),
            ))

    tr.commit()

    Artwork.__table__.c.discussion_id.alter(nullable=False)
    User.__table__.c.discussion_id.alter(nullable=False)

 def renameItem(self):
     self.nodeName(textToShow=self.treeWidget.currentItem().text(0))
     try:
         self.treeWidget.currentItem().parent().indexOfChild(self.treeWidget.currentItem())
     except:
         parentUniqueID = self.treeWidget.currentItem().statusTip(1)
         renNode = update(self.nodes).where(self.nodes.c.node_id == parentUniqueID).values(name=self.inputNodeName)
         renNode.execute()
         self.redraw()
     else:
         parentUniqueID = self.treeWidget.currentItem().parent().statusTip(0)
         childIndex = self.treeWidget.currentItem().statusTip(1)
         renSubNode = update(self.children).where(self.children.c.node_id == childIndex and self.children.c.father_id == parentUniqueID).values(name=self.inputNodeName)
         renSubNode.execute()
         self.redraw()
     del self.inputNodeName

def update_tag(id, obj):
    query = (
        update([tag_table])
        .where(tag_table.c.id == id)
        .values(**obj)
        .returning(tag_table.c.id))
    return query.execute().first()[0]

 def _update_atom_details(self, conn, ad, e_ad):
     e_ad.merge(ad)
     conn.execute(
         sql.update(self._tables.atomdetails)
         .where(self._tables.atomdetails.c.uuid == e_ad.uuid)
         .values(e_ad.to_dict())
     )

    def update_password(self, user_id, password, old_password=None, key=None):
        """Change the user password

        Args:
            user_id: user id
            password: new password

        Returns:
            True if the change was successful, False otherwise
        """
        if old_password is None:
            if key:
                #using a key, therefore we should check it
                if self._get_reset_code(user_id) == key:
                    self.clear_reset_code(user_id)
                else:
                    logger.error("bad key used for update password")
                    return False
            else:
                return False

        password_hash = ssha256(password)
        query = update(users).where(users.c.id == user_id)
        res = safe_execute(self._engine,
                           query.values(password_hash=password_hash))
        return res.rowcount == 1

def update_helper(ikey, table, session, obj, mapping):
    raise ValueError
    if hasattr(obj, '_alchemy_pk') and obj._alchemy_pk is not None:
        query = update(table)\
            .where(table.c.id==obj._alchemy_pk)\
            .values(**mapping)
        result = session.execute(query)

        if result.rowcount == 0:
            raise RuntimeError("Can't update object")
            # obj._alchemy_pk = None
            # return update_helper(session, obj, mapping)
    else:
        # TODO search for an object
        # query = select([table]).where(table.c.uri==obj.uri)
        # result = session.execute(query).fetchone()
        # if result:
        #     obj._alchemy_pk = result[0]
        #     return update_helper(session, obj, mapping)
        # else:

        # print "INSERT", obj, session.execute(select([table])).fetchall()
        aquery = table.insert().values(**mapping)
        result = session.execute(aquery)
        obj._alchemy_pk = result.inserted_primary_key[0]

def upgrade(migrate_engine):
    meta.bind = migrate_engine
    dialect = migrate_engine.url.get_dialect().name

    domains_table = Table('domains', meta, autoload=True)

    if dialect.startswith('sqlite'):
        # SQLite can't drop a constraint. Yay. This will be fun..

        # Create a new name column without the unique index
        name_tmp_column = Column('name_tmp', String(255))
        name_tmp_column.create(domains_table)

        # Copy the data over.
        query = update(domains_table).values(name_tmp=domains_table.c.name)
        migrate_engine.execute(query)

        # Delete the name column
        domains_table.c.name.drop()

        # Rename the name_tmp column to name
        domains_table.c.name_tmp.alter(name='name')
    elif dialect.startswith('postgresql'):
        constraint = UniqueConstraint('name', name='domains_name_key',
                                      table=domains_table)
        constraint.drop()
    else:
        constraint = UniqueConstraint('name', name='name', table=domains_table)
        constraint.drop()

def downgrade():
    op.add_column('request',
            sa.Column('float_base_payout', sa.Float, default=0.0)
    )
    request = table('request',
            column('id', sa.Integer),
            column('base_payout', sa.Numeric(precision=15, scale=2)),
            column('float_base_payout', sa.Float),
    )
    conn = op.get_bind()
    requests_sel = select([request.c.id, request.c.base_payout])
    requests = conn.execute(requests_sel)
    for request_id, decimal_payout in requests:
        decimal_payout = decimal_payout / 1000000
        float_payout = float(decimal_payout)
        update_stmt = update(request)\
                .where(request.c.id == request_id)\
                .values({
                    'float_base_payout': float_payout,
                })
        conn.execute(update_stmt)
    requests.close()
    op.drop_column('request', 'base_payout')
    op.alter_column('request',
            column_name='numeric_base_payout',
            new_column_name='base_payout',
            existing_type=sa.Float,
            existing_server_default=0.0)

    def update_session():
        now = datetime.datetime.now()
        if 17 <= now.hour <= 23:
            lower_limit = datetime.datetime(
                datetime.datetime.today().year,
                datetime.datetime.today().month,
                datetime.datetime.today().day,
                19
            )
            upper_limit = datetime.datetime(
                datetime.datetime.today().year,
                datetime.datetime.today().month,
                datetime.datetime.today().day + 1,
                7
            )

            db.session.execute(
                sql.update(Session).where(
                    Session.id == 1
                ).values(
                    upper_limit=upper_limit,
                    lower_limit=lower_limit
                )
            )
            db.session.commit()

 def save_logbook(self, book):
     try:
         logbooks = self._tables.logbooks
         with self._engine.begin() as conn:
             q = (sql.select([logbooks]).
                  where(logbooks.c.uuid == book.uuid))
             row = conn.execute(q).first()
             if row:
                 e_lb = self._converter.convert_book(row)
                 self._converter.populate_book(conn, e_lb)
                 e_lb.merge(book)
                 conn.execute(sql.update(logbooks)
                              .where(logbooks.c.uuid == e_lb.uuid)
                              .values(e_lb.to_dict()))
                 for fd in book:
                     e_fd = e_lb.find(fd.uuid)
                     if e_fd is None:
                         e_lb.add(fd)
                         self._insert_flow_details(conn, fd, e_lb.uuid)
                     else:
                         self._update_flow_details(conn, fd, e_fd)
                 return e_lb
             else:
                 conn.execute(sql.insert(logbooks, book.to_dict()))
                 for fd in book:
                     self._insert_flow_details(conn, fd, book.uuid)
                 return book
     except sa_exc.DBAPIError:
         exc.raise_with_cause(
             exc.StorageFailure,
             "Failed saving logbook '%s'" % book.uuid)

def downgrade():
    # Add ship and pilot transformer columns back to division
    op.add_column('division', sa.Column('ship_transformer', sa.PickleType))
    op.add_column('division', sa.Column('pilot_transformer', sa.PickleType))
    # Convert transformerrefs back to the old columns
    conn = op.get_bind()
    columns = [
        transformerref.c.division_id,
        transformerref.c.attribute_name,
        transformerref.c.transformer,
    ]
    transformer_sel = select(columns)\
            .where(or_(
                    transformerref.c.attribute_name == 'ship_type',
                    transformerref.c.attribute_name == 'pilot',
            ))
    transformer_rows = conn.execute(transformer_sel)
    for division_id, attribute_name, transformer in transformer_rows:
        if attribute_name == 'ship_type':
            colname = 'ship'
            transformer_class = evesrp.transformers.ShipTransformer
        elif attribute_name == 'pilot':
            colname = 'pilot'
            transformer_class = evesrp.transformers.PilotTransformer
        colname += '_transformer'
        transformer = transformer_class(transformer.name, transformer.slug)
        update_stmt = update(division)\
                .where(division.c.id == division_id)\
                .values({
                        colname: transformer
                })
        conn.execute(update_stmt)
    transformer_rows.close()
    # Drop the transformerref table. This is going to be lossy.
    op.drop_table('transformerref')

 def _updateResources(self):
     query = {'status':self.computer_status,'is_mac': self.is_mac}
     if self.computer_status == 0:
         query['update0'] = functions.now()
     else:
         query['update1'] = functions.now()
     self._simpleQuery(update(schema.computers,schema.computers.c.computername==self.computer_id,values=query))  

    def _upsert_generic(self, table, items, annotations):
        """Upsert a batch of items one at a time, trying INSERT then UPDATE.

        This is a tremendously inefficient way to write a batch of items,
        but it's guaranteed to work without special cooperation from the
        database.  For MySQL we use the much improved _upsert_onduplicatekey.
        """
        userid = items[0].get("userid")
        num_created = 0
        for item in items:
            assert item.get("userid") == userid
            try:
                # Try to insert the item.
                # If it already exists, this fails with an integrity error.
                query = insert(table).values(**item)
                self.execute(query, item, annotations).close()
                num_created += 1
            except IntegrityError:
                # Update the item.
                # Use the table's primary key fields in the WHERE clause,
                # and put all other fields into the UPDATE clause.
                item = item.copy()
                query = update(table)
                for key in table.primary_key:
                    try:
                        query = query.where(key == item.pop(key.name))
                    except KeyError:
                        msg = "Item is missing primary key column %r"
                        raise ValueError(msg % (key.name,))
                query = query.values(**item)
                self.execute(query, item, annotations).close()
        return num_created

    async def _test_execute(self, engines, binds):
        test_table1 = self.test_models['db1'].test_table1
        test_table2 = self.test_models['db2'].test_table2
        async with Session(engines, binds) as session:
            q = sql.insert(test_table1).values(id=5, title='test_title')
            result = await session.execute(q)
            self.assertEqual(result.lastrowid, 5)
            q = sql.select(test_table1.c).where(test_table1.c.id == 5)
            result = await session.execute(q)
            self.assertEqual(result.rowcount, 1)
            result = list(result)
            self.assertEqual(result[0]['id'], 5)
            self.assertEqual(result[0]['title'], 'test_title')

            q = sql.update(test_table1).where(test_table1.c.id == 5).\
                    values(title='test_title2')
            result = await session.execute(q)
            self.assertEqual(result.rowcount, 1)
            q = sql.select(test_table1.c).\
                    where(test_table1.c.id == 5)
            result = await session.execute(q)
            self.assertEqual(result.rowcount, 1)
            result = list(result)
            self.assertEqual(result[0]['id'], 5)
            self.assertEqual(result[0]['title'], 'test_title2')

            q = sql.delete(test_table1).where(test_table1.c.id == 5)
            result = await session.execute(q)
            self.assertEqual(result.rowcount, 1)
            q = sql.select(test_table1.c).\
                    where(test_table1.c.id == 5)
            result = await session.execute(q)
            self.assertEqual(result.rowcount, 0)

def atomic_add(obj, column, delta, expire=False):
    """Performs an atomic add (or subtract) of the given column on the
    object.  This updates the object in place for reflection but does
    the real add on the server to avoid race conditions.  This assumes
    that the database's '+' operation is atomic.

    If `expire` is set to `True`, the value is expired and reloaded instead
    of added of the local value.  This is a good idea if the value should
    be used for reflection.
    """
    sess = orm.object_session(obj) or session
    mapper = orm.object_mapper(obj)
    pk = mapper.primary_key_from_instance(obj)
    assert len(pk) == 1, 'atomic_add not supported for classes with ' \
                         'more than one primary key'

    val = orm.attributes.get_attribute(obj, column)
    if expire:
        orm.attributes.instance_state(obj).expire_attributes([column])
    else:
        orm.attributes.set_committed_value(obj, column, val + delta)

    table = mapper.tables[0]
    stmt = sql.update(table, mapper.primary_key[0] == pk[0], {
        column:     table.c[column] + delta
    })
    sess.execute(stmt)

    def _set_loaned_flag(self, flag):
        """Sets loaned flag in current collection and all associated movies.

        :param flag: if True and there are loaned movies in the collection
            already, exception will be raised (whole collection cannot be
            loaned if one of the movies is not available).
            Please also remember to create new entry in loans table later (no
            need to do that if flag is False).
        """

        session = object_session(self)

        if flag:  # loaning whole collection
            loaned_movies = session.execute(
                select([tables.movies.columns.movie_id]).where(
                    and_(
                        tables.movies.columns.collection_id == self.collection_id, tables.movies.columns.loaned == True
                    )
                )
            ).fetchall()
            if loaned_movies:
                log.error("cannot loan it, collection contains loaned movie(s): %s", loaned_movies)
                raise Exception("loaned movies in the collection already")

        self._loaned = flag
        update_query = update(tables.movies, tables.movies.columns.collection_id == self.collection_id)
        session.execute(update_query, params={"loaned": flag})