def test_query_one(self):
        q = Session.query(User).\
                filter(User.name == 'ed').\
                    options(joinedload(User.addresses))

        q2 = serializer.loads(
                    serializer.dumps(q, -1),
                            users.metadata, Session)
        def go():
            eq_(q2.all(), [
                    User(name='ed', addresses=[Address(id=2),
                    Address(id=3), Address(id=4)])])

        self.assert_sql_count(testing.db, go, 1)

        eq_(q2.join(User.addresses).filter(Address.email
            == '[email protected]').value(func.count('*')), 1)
        u1 = Session.query(User).get(8)
        q = Session.query(Address).filter(Address.user
                == u1).order_by(desc(Address.email))
        q2 = serializer.loads(serializer.dumps(q, -1), users.metadata,
                              Session)
        eq_(q2.all(), [Address(email='[email protected]'),
            Address(email='[email protected]'),
            Address(email='[email protected]')])

    def test_query(self):
        q = Session.query(User).filter(User.name == "ed").options(eagerload(User.addresses))
        eq_(q.all(), [User(name="ed", addresses=[Address(id=2), Address(id=3), Address(id=4)])])

        q2 = serializer.loads(serializer.dumps(q, -1), users.metadata, Session)

        def go():
            eq_(q2.all(), [User(name="ed", addresses=[Address(id=2), Address(id=3), Address(id=4)])])

        self.assert_sql_count(testing.db, go, 1)

        eq_(q2.join(User.addresses).filter(Address.email == "[email protected]").value(func.count("*")), 1)

        u1 = Session.query(User).get(8)

        q = Session.query(Address).filter(Address.user == u1).order_by(desc(Address.email))
        q2 = serializer.loads(serializer.dumps(q, -1), users.metadata, Session)

        eq_(q2.all(), [Address(email="[email protected]"), Address(email="[email protected]"), Address(email="[email protected]")])

        q = Session.query(User).join(User.addresses).filter(Address.email.like("%fred%"))
        q2 = serializer.loads(serializer.dumps(q, -1), users.metadata, Session)
        eq_(q2.all(), [User(name="fred")])

        eq_(list(q2.values(User.id, User.name)), [(9, u"fred")])

 def get_data(self):
     """Go through every mapped table and dumps the data"""
     data = dict()
     for model_class in self.get_mapped_classes():
         query = self.db_session.query(model_class)
         data[model_class.__name__] = dumps(query.all())
     return data

 def test_query_two(self):
     q = Session.query(User).join(User.addresses).\
         filter(Address.email.like('%fred%'))
     q2 = serializer.loads(serializer.dumps(q, -1), users.metadata,
                           Session)
     eq_(q2.all(), [User(name='fred')])
     eq_(list(q2.values(User.id, User.name)), [(9, 'fred')])

    def test_expression(self):

        expr = select([users]).select_from(users.join(addresses)).limit(5)
        re_expr = serializer.loads(serializer.dumps(expr, -1), users.metadata, None)
        eq_(str(expr), str(re_expr))

        assert re_expr.bind is testing.db
        eq_(re_expr.execute().fetchall(), [(7, u"jack"), (8, u"ed"), (8, u"ed"), (8, u"ed"), (9, u"fred")])

 def get_data(self):
     """Go through every mapped class and dumps the data"""
     db = self.db()
     data = dict()
     for model in self.get_mapped_classes():
         query = db.session.query(model)
         data[model.__name__] = dumps(query.all())
     return data

 def _put_remote(self,otype,obj,oid,hid):
     remotehost = self.datacon.db.get(Host,hid)
     self.location[otype][oid] = remotehost
     res = self.send_request_owner(remotehost, otype.__name__, oid,"put", newobj=dumps(obj))
     logger.debug("[obm]: Inserting remotely at " + remotehost + " the object: " + str(obj))
     if res == "OK":
         return True
     else:
         logger.error(res)

    def test_aliases(self):
        u7, u8, u9, u10 = Session.query(User).order_by(User.id).all()

        ualias = aliased(User)
        q = Session.query(User, ualias).join((ualias, User.id < ualias.id)).filter(User.id<9).order_by(User.id, ualias.id)

        q2 = serializer.loads(serializer.dumps(q), users.metadata, Session)
        
        eq_(list(q2.all()), [(u7, u8), (u7, u9), (u7, u10), (u8, u9), (u8, u10)])

def dump(destination):
    dump_models = []  # List of models you want to dump
    serialized = list()
    for model in dump_models:
        print('Dumping {}'.format(model))
        serialized.append(unicode(dumps(db.session.query(model).all()), errors='ignore'))
    with open(destination, 'w') as f:
        f.writelines(json.dumps(serialized))
    print('Done.')

    def test_orm_join(self):
        from sqlalchemy.orm.util import join

        j = join(User, Address, User.addresses)

        j2 = serializer.loads(serializer.dumps(j, -1), users.metadata)
        assert j2.left is j.left
        assert j2.right is j.right
        assert j2._target_adapter._next

def backup_everything(session):
    meta = get_declarative_base().metadata
    backupdict = {}
    for table in reversed(meta.sorted_tables):
        query = session.query(table)
        queryresults = query.all()
        backupdict[table.name] = dumps(queryresults)

    return backupdict

def main(argv=sys.argv):
    if len(argv) != 2:
        usage(argv)
    config_uri = argv[1]
    settings = get_appsettings(config_uri)
    engine = engine_from_config(settings, 'sqlalchemy.')
    DBSession.configure(bind=engine)
    menu_items = DBSession.query(MenuItem)
    serialized_menu_items = dumps(menu_items.all())
    menus = DBSession.query(Menu)
    serialized_menus = dumps(menus.all()) 
    allergens = DBSession.query(Allergen)
    serialized_allergens = dumps(allergens.all())
    serialized_data = {"menus" : serialized_menus,
                       "menu_items" : serialized_menu_items,
                       "allergens" : serialized_allergens}
    file = open('./db_backup.txt', 'w+')
    pickle.dump(serialized_data, file)

    def test_annotated_one(self):
        j = join(users, addresses)._annotate({"foo": "bar"})
        query = select([addresses]).select_from(
            j
        )

        str(query)
        for prot in pickle_protocols():
            pickled_failing = serializer.dumps(
                j, prot)
            serializer.loads(pickled_failing, users.metadata, None)

    def test_query_three(self):
        ua = aliased(User)
        q = \
            Session.query(ua).join(ua.addresses).\
               filter(Address.email.like('%fred%'))
        q2 = serializer.loads(serializer.dumps(q, -1), users.metadata,
                              Session)
        eq_(q2.all(), [User(name='fred')])

        # try to pull out the aliased entity here...
        ua_2 = q2._entities[0].entity_zero.entity
        eq_(list(q2.values(ua_2.id, ua_2.name)), [(9, 'fred')])

    def saveToAppLog(self):
        if self.action:
            if self.value:
                esito = " ERRATO"
                how = "E"
            else:
                esito = " CORRETTO"
                how = "I"
            message = self.action + esito
        else:
            if params["session"].dirty:
                message = "UPDATE " + self.dao.__class__.__name__
            elif params["session"].new:
                message = "INSERT " + self.dao.__class__.__name__
            elif params["session"].deleted:
                message = "DELETE " + self.dao.__class__.__name__
            else:
                message = "UNKNOWN ACTION"

        when = datetime.datetime.now()
        where = params["schema"]
        whoID = params["usernameLoggedList"][0]
        utentedb = params["usernameLoggedList"][3]
        utente = params["usernameLoggedList"][1]

        if self.action:
            whatstr = self.value
        else:
            salvo = self.commit()
            if salvo:
                how = "I"
            else:
                how = "E"
            mapper = object_mapper(self.dao)
            pk = mapper.primary_key_from_instance(self.dao)
            whatstr = str(pk)

        app = ApplicationLog()
        app.schema = where
        app.message = message
        app.level = how
        print dumps(whatstr)
        app.strvalue = dumps(whatstr)
        app.registrazion_date = when
        app.utentedb = utentedb
        app.id_utente = whoID
        app.pkid = dumps(whatstr)
        print dumps(self.dao)
        app.object = dumps(self.dao)
        params["session"].add(app)
        self.commit()
        print "[LOG] %s id: %s da %s in %s in data %s" % (message, whatstr, utente, where, when.strftime("%d/%m/%Y"))

    def test_unicode(self):
        m = MetaData()
        t = Table(ue('\u6e2c\u8a66'), m,
                Column(ue('\u6e2c\u8a66_id'), Integer))

        expr = select([t]).where(t.c[ue('\u6e2c\u8a66_id')] == 5)

        expr2 = serializer.loads(serializer.dumps(expr, -1), m)

        self.assert_compile(
            expr2,
            ue('SELECT "\u6e2c\u8a66"."\u6e2c\u8a66_id" FROM "\u6e2c\u8a66" '
                'WHERE "\u6e2c\u8a66"."\u6e2c\u8a66_id" = :\u6e2c\u8a66_id_1'),
            dialect="default"
        )

def _backup_database(filepath):

    table_classes = Base._decl_class_registry.values()
    session = Session()

    for table_class in table_classes:
        if hasattr(table_class, '__tablename__'):
            query = session.query(table_class)
            serialized_data = dumps(query.all())

            new_filepath = filepath + 'database/' + table_class.__tablename__

            with open(new_filepath, 'w') as text_file:
                text_file.write(serialized_data)

    return

    def on_after_flush(self, app, session):
        """broadcast models events into the data-models channel

        channel: data-models
        event: <model-identifier>.<event>
        data: JSON representation of model

        <event> is one of ``create``, ``update``, ``delete``
        """
        request = session.request
        if not app.channels or not request:
            return
        for instance, event in session.changes():
            app.channels.publish(
                app.config['CHANNEL_DATAMODEL'],
                '%s.%s' % (instance.__class__.__name__.lower(), event),
                dumps(instance)
            )

 def __init__(self, expression=None, name=None):
     if expression is not None:
         self.expression = expression
         blob = dumps(expression)
         self.expr_blob = blob
     self.name = name

 def items_to_cache(self, query_id, items):
     if self.cache_enabled:
         self.cache.set(self.items_key(query_id), dumps(items),
                        time=self.cache_timeout)