sqlalchemy.testing.assertsql.CompiledSQL

Here are the examples of the python api sqlalchemy.testing.assertsql.CompiledSQL taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.

38 Examples 7

Example 1

Project: sqlalchemy
Source File: test_constraints.py
View license
    @testing.provide_metadata
    def test_index_functional_create(self):
        metadata = self.metadata

        t = Table('sometable', metadata,
                  Column('id', Integer, primary_key=True),
                  Column('data', String(50))
                  )
        Index('myindex', t.c.data.desc())
        self.assert_sql_execution(
            testing.db,
            lambda: t.create(testing.db),
            CompiledSQL('CREATE TABLE sometable (id INTEGER NOT NULL, '
                        'data VARCHAR(50), PRIMARY KEY (id))'),
            CompiledSQL('CREATE INDEX myindex ON sometable (data DESC)')
        )

Example 2

Project: sqlalchemy
Source File: test_baked.py
View license
    def test_useget_cancels_eager(self):
        """test that a one to many lazyload cancels the unnecessary
        eager many-to-one join on the other side."""

        User = self.classes.User
        Address = self.classes.Address

        mapper(User, self.tables.users)
        mapper(Address, self.tables.addresses, properties={
            'user': relationship(
                User, lazy='joined',
                backref=backref('addresses', lazy='baked_select')
            )
        })

        sess = Session()
        u1 = sess.query(User).filter(User.id == 8).one()

        def go():
            eq_(u1.addresses[0].user, u1)
        self.assert_sql_execution(
            testing.db, go,
            CompiledSQL(
                "SELECT addresses.id AS addresses_id, addresses.user_id AS "
                "addresses_user_id, addresses.email_address AS "
                "addresses_email_address FROM addresses WHERE :param_1 = "
                "addresses.user_id",
                {'param_1': 8})
            )

Example 3

Project: sqlalchemy
Source File: test_baked.py
View license
    def test_useget_cancels_eager_propagated_present(self):
        """test that a one to many lazyload cancels the unnecessary
        eager many-to-one join on the other side, even when a propagated
        option is present."""

        User = self.classes.User
        Address = self.classes.Address

        mapper(User, self.tables.users)
        mapper(Address, self.tables.addresses, properties={
            'user': relationship(
                User, lazy='joined',
                backref=backref('addresses', lazy='baked_select')
            )
        })

        from sqlalchemy.orm.interfaces import MapperOption

        class MyBogusOption(MapperOption):
            propagate_to_loaders = True

        sess = Session()
        u1 = sess.query(User).options(MyBogusOption()).filter(User.id == 8).one()

        def go():
            eq_(u1.addresses[0].user, u1)
        self.assert_sql_execution(
            testing.db, go,
            CompiledSQL(
                "SELECT addresses.id AS addresses_id, addresses.user_id AS "
                "addresses_user_id, addresses.email_address AS "
                "addresses_email_address FROM addresses WHERE :param_1 = "
                "addresses.user_id",
                {'param_1': 8})
            )

Example 4

Project: sqlalchemy
Source File: test_bulk.py
View license
    def test_bulk_save_return_defaults(self):
        User, = self.classes("User",)

        s = Session()
        objects = [
            User(name="u1"),
            User(name="u2"),
            User(name="u3")
        ]
        assert 'id' not in objects[0].__dict__

        with self.sql_execution_asserter() as asserter:
            s.bulk_save_objects(objects, return_defaults=True)

        asserter.assert_(
            CompiledSQL(
                "INSERT INTO users (name) VALUES (:name)",
                [{'name': 'u1'}]
            ),
            CompiledSQL(
                "INSERT INTO users (name) VALUES (:name)",
                [{'name': 'u2'}]
            ),
            CompiledSQL(
                "INSERT INTO users (name) VALUES (:name)",
                [{'name': 'u3'}]
            ),
        )
        eq_(objects[0].__dict__['id'], 1)

Example 5

Project: sqlalchemy
Source File: test_bulk.py
View license
    def test_bulk_save_no_defaults(self):
        User, = self.classes("User",)

        s = Session()
        objects = [
            User(name="u1"),
            User(name="u2"),
            User(name="u3")
        ]
        assert 'id' not in objects[0].__dict__

        with self.sql_execution_asserter() as asserter:
            s.bulk_save_objects(objects)

        asserter.assert_(
            CompiledSQL(
                "INSERT INTO users (name) VALUES (:name)",
                [{'name': 'u1'}, {'name': 'u2'}, {'name': 'u3'}]
            ),
        )
        assert 'id' not in objects[0].__dict__

Example 6

Project: sqlalchemy
Source File: test_bulk.py
View license
    def test_bulk_save_updated_include_unchanged(self):
        User, = self.classes("User",)

        s = Session(expire_on_commit=False)
        objects = [
            User(name="u1"),
            User(name="u2"),
            User(name="u3")
        ]
        s.add_all(objects)
        s.commit()

        objects[0].name = 'u1new'
        objects[2].name = 'u3new'

        s = Session()
        with self.sql_execution_asserter() as asserter:
            s.bulk_save_objects(objects, update_changed_only=False)

        asserter.assert_(
            CompiledSQL(
                "UPDATE users SET name=:name WHERE "
                "users.id = :users_id",
                [{'users_id': 1, 'name': 'u1new'},
                 {'users_id': 2, 'name': 'u2'},
                 {'users_id': 3, 'name': 'u3new'}]
            )
        )

Example 7

Project: sqlalchemy
Source File: test_bulk.py
View license
    def test_bulk_update(self):
        User, = self.classes("User",)

        s = Session(expire_on_commit=False)
        objects = [
            User(name="u1"),
            User(name="u2"),
            User(name="u3")
        ]
        s.add_all(objects)
        s.commit()

        s = Session()
        with self.sql_execution_asserter() as asserter:
            s.bulk_update_mappings(
                User,
                [{'id': 1, 'name': 'u1new'},
                 {'id': 2, 'name': 'u2'},
                 {'id': 3, 'name': 'u3new'}]
            )

        asserter.assert_(
            CompiledSQL(
                "UPDATE users SET name=:name WHERE users.id = :users_id",
                [{'users_id': 1, 'name': 'u1new'},
                 {'users_id': 2, 'name': 'u2'},
                 {'users_id': 3, 'name': 'u3new'}]
            )
        )

Example 8

Project: sqlalchemy
Source File: test_bulk.py
View license
    def test_bulk_insert(self):
        User, = self.classes("User",)

        s = Session()
        with self.sql_execution_asserter() as asserter:
            s.bulk_insert_mappings(
                User,
                [{'id': 1, 'name': 'u1new'},
                 {'id': 2, 'name': 'u2'},
                 {'id': 3, 'name': 'u3new'}]
            )

        asserter.assert_(
            CompiledSQL(
                "INSERT INTO users (id, name) VALUES (:id, :name)",
                [{'id': 1, 'name': 'u1new'},
                 {'id': 2, 'name': 'u2'},
                 {'id': 3, 'name': 'u3new'}]
            )
        )

Example 9

Project: sqlalchemy
Source File: test_bulk.py
View license
    def test_bulk_insert_render_nulls(self):
        Order, = self.classes("Order",)

        s = Session()
        with self.sql_execution_asserter() as asserter:
            s.bulk_insert_mappings(
                Order,
                [{'id': 1, 'description': 'u1new'},
                 {'id': 2, 'description': None},
                 {'id': 3, 'description': 'u3new'}],
                render_nulls=True
            )

        asserter.assert_(
            CompiledSQL(
                "INSERT INTO orders (id, description) VALUES (:id, :description)",
                [{'id': 1, 'description': 'u1new'},
                 {'id': 2, 'description': None},
                 {'id': 3, 'description': 'u3new'}]
            )
        )

Example 10

Project: sqlalchemy
Source File: test_bulk.py
View license
    def test_bulk_save_joined_inh_return_defaults(self):
        Person, Engineer, Manager, Boss = \
            self.classes('Person', 'Engineer', 'Manager', 'Boss')

        s = Session()
        objects = [
            Manager(name='m1', status='s1', manager_name='mn1'),
            Engineer(name='e1', status='s2', primary_language='l1'),
            Engineer(name='e2', status='s3', primary_language='l2'),
            Boss(
                name='b1', status='s3', manager_name='mn2',
                golf_swing='g1')
        ]
        assert 'person_id' not in objects[0].__dict__

        with self.sql_execution_asserter() as asserter:
            s.bulk_save_objects(objects, return_defaults=True)

        asserter.assert_(
            CompiledSQL(
                "INSERT INTO people (name, type) VALUES (:name, :type)",
                [{'type': 'manager', 'name': 'm1'}]
            ),
            CompiledSQL(
                "INSERT INTO managers (person_id, status, manager_name) "
                "VALUES (:person_id, :status, :manager_name)",
                [{'person_id': 1, 'status': 's1', 'manager_name': 'mn1'}]
            ),
            CompiledSQL(
                "INSERT INTO people (name, type) VALUES (:name, :type)",
                [{'type': 'engineer', 'name': 'e1'}]
            ),
            CompiledSQL(
                "INSERT INTO people (name, type) VALUES (:name, :type)",
                [{'type': 'engineer', 'name': 'e2'}]
            ),
            CompiledSQL(
                "INSERT INTO engineers (person_id, status, primary_language) "
                "VALUES (:person_id, :status, :primary_language)",
                [{'person_id': 2, 'status': 's2', 'primary_language': 'l1'},
                 {'person_id': 3, 'status': 's3', 'primary_language': 'l2'}]

            ),
            CompiledSQL(
                "INSERT INTO people (name, type) VALUES (:name, :type)",
                [{'type': 'boss', 'name': 'b1'}]
            ),
            CompiledSQL(
                "INSERT INTO managers (person_id, status, manager_name) "
                "VALUES (:person_id, :status, :manager_name)",
                [{'person_id': 4, 'status': 's3', 'manager_name': 'mn2'}]

            ),
            CompiledSQL(
                "INSERT INTO boss (boss_id, golf_swing) VALUES "
                "(:boss_id, :golf_swing)",
                [{'boss_id': 4, 'golf_swing': 'g1'}]
            )
        )
        eq_(objects[0].__dict__['person_id'], 1)
        eq_(objects[3].__dict__['person_id'], 4)
        eq_(objects[3].__dict__['boss_id'], 4)

Example 11

Project: sqlalchemy
Source File: test_bulk.py
View license
    def test_bulk_save_joined_inh_no_defaults(self):
        Person, Engineer, Manager, Boss = \
            self.classes('Person', 'Engineer', 'Manager', 'Boss')

        s = Session()
        with self.sql_execution_asserter() as asserter:
            s.bulk_save_objects([
                Manager(
                    person_id=1,
                    name='m1', status='s1', manager_name='mn1'),
                Engineer(
                    person_id=2,
                    name='e1', status='s2', primary_language='l1'),
                Engineer(
                    person_id=3,
                    name='e2', status='s3', primary_language='l2'),
                Boss(
                    person_id=4, boss_id=4,
                    name='b1', status='s3', manager_name='mn2',
                    golf_swing='g1')
            ],

            )

        # the only difference here is that common classes are grouped together.
        # at the moment it doesn't lump all the "people" tables from
        # different classes together.
        asserter.assert_(
            CompiledSQL(
                "INSERT INTO people (person_id, name, type) VALUES "
                "(:person_id, :name, :type)",
                [{'person_id': 1, 'type': 'manager', 'name': 'm1'}]
            ),
            CompiledSQL(
                "INSERT INTO managers (person_id, status, manager_name) "
                "VALUES (:person_id, :status, :manager_name)",
                [{'status': 's1', 'person_id': 1, 'manager_name': 'mn1'}]
            ),
            CompiledSQL(
                "INSERT INTO people (person_id, name, type) VALUES "
                "(:person_id, :name, :type)",
                [{'person_id': 2, 'type': 'engineer', 'name': 'e1'},
                 {'person_id': 3, 'type': 'engineer', 'name': 'e2'}]
            ),
            CompiledSQL(
                "INSERT INTO engineers (person_id, status, primary_language) "
                "VALUES (:person_id, :status, :primary_language)",
                [{'person_id': 2, 'status': 's2', 'primary_language': 'l1'},
                 {'person_id': 3, 'status': 's3', 'primary_language': 'l2'}]
            ),
            CompiledSQL(
                "INSERT INTO people (person_id, name, type) VALUES "
                "(:person_id, :name, :type)",
                [{'person_id': 4, 'type': 'boss', 'name': 'b1'}]
            ),
            CompiledSQL(
                "INSERT INTO managers (person_id, status, manager_name) "
                "VALUES (:person_id, :status, :manager_name)",
                [{'status': 's3', 'person_id': 4, 'manager_name': 'mn2'}]
            ),
            CompiledSQL(
                "INSERT INTO boss (boss_id, golf_swing) VALUES "
                "(:boss_id, :golf_swing)",
                [{'boss_id': 4, 'golf_swing': 'g1'}]
            )
        )

Example 12

Project: sqlalchemy
Source File: test_bulk.py
View license
    def test_bulk_insert_joined_inh_return_defaults(self):
        Person, Engineer, Manager, Boss = \
            self.classes('Person', 'Engineer', 'Manager', 'Boss')

        s = Session()
        with self.sql_execution_asserter() as asserter:
            s.bulk_insert_mappings(
                Boss,
                [
                    dict(
                        name='b1', status='s1', manager_name='mn1',
                        golf_swing='g1'
                    ),
                    dict(
                        name='b2', status='s2', manager_name='mn2',
                        golf_swing='g2'
                    ),
                    dict(
                        name='b3', status='s3', manager_name='mn3',
                        golf_swing='g3'
                    ),
                ], return_defaults=True
            )

        asserter.assert_(
            CompiledSQL(
                "INSERT INTO people (name) VALUES (:name)",
                [{'name': 'b1'}]
            ),
            CompiledSQL(
                "INSERT INTO people (name) VALUES (:name)",
                [{'name': 'b2'}]
            ),
            CompiledSQL(
                "INSERT INTO people (name) VALUES (:name)",
                [{'name': 'b3'}]
            ),
            CompiledSQL(
                "INSERT INTO managers (person_id, status, manager_name) "
                "VALUES (:person_id, :status, :manager_name)",
                [{'person_id': 1, 'status': 's1', 'manager_name': 'mn1'},
                 {'person_id': 2, 'status': 's2', 'manager_name': 'mn2'},
                 {'person_id': 3, 'status': 's3', 'manager_name': 'mn3'}]

            ),
            CompiledSQL(
                "INSERT INTO boss (boss_id, golf_swing) VALUES "
                "(:boss_id, :golf_swing)",
                [{'golf_swing': 'g1', 'boss_id': 1},
                 {'golf_swing': 'g2', 'boss_id': 2},
                 {'golf_swing': 'g3', 'boss_id': 3}]
            )
        )

Example 13

Project: sqlalchemy
Source File: test_cycles.py
View license
    def test_post_update_m2o(self):
        """A cycle between two rows, with a post_update on the many-to-one"""

        person, ball, Ball, Person = (self.tables.person,
                                self.tables.ball,
                                self.classes.Ball,
                                self.classes.Person)

        mapper(Ball, ball)
        mapper(Person, person, properties=dict(
            balls=relationship(Ball,
                           primaryjoin=ball.c.person_id == person.c.id,
                           remote_side=ball.c.person_id,
                           post_update=False,
                           cascade="all, delete-orphan"),
            favorite=relationship(Ball,
                              primaryjoin=person.c.favorite_ball_id == ball.c.id,
                              remote_side=person.c.favorite_ball_id,
                              post_update=True)))

        b = Ball(data='some data')
        p = Person(data='some data')
        p.balls.append(b)
        p.balls.append(Ball(data='some data'))
        p.balls.append(Ball(data='some data'))
        p.balls.append(Ball(data='some data'))
        p.favorite = b
        sess = create_session()
        sess.add(b)
        sess.add(p)

        self.assert_sql_execution(
            testing.db,
            sess.flush,
            RegexSQL("^INSERT INTO person", {'data':'some data'}),
            RegexSQL("^INSERT INTO ball", lambda c: {'person_id':p.id, 'data':'some data'}),
            RegexSQL("^INSERT INTO ball", lambda c: {'person_id':p.id, 'data':'some data'}),
            RegexSQL("^INSERT INTO ball", lambda c: {'person_id':p.id, 'data':'some data'}),
            RegexSQL("^INSERT INTO ball", lambda c: {'person_id':p.id, 'data':'some data'}),
            CompiledSQL("UPDATE person SET favorite_ball_id=:favorite_ball_id "
                        "WHERE person.id = :person_id",
                        lambda ctx:{'favorite_ball_id':p.favorite.id, 'person_id':p.id}
             ),
        )

        sess.delete(p)

        self.assert_sql_execution(
            testing.db,
            sess.flush,
            CompiledSQL("UPDATE person SET favorite_ball_id=:favorite_ball_id "
                "WHERE person.id = :person_id",
                lambda ctx: {'person_id': p.id, 'favorite_ball_id': None}),
            CompiledSQL("DELETE FROM ball WHERE ball.id = :id", None), # lambda ctx:[{'id': 1L}, {'id': 4L}, {'id': 3L}, {'id': 2L}])
            CompiledSQL("DELETE FROM person WHERE person.id = :id", lambda ctx:[{'id': p.id}])
        )

Example 14

Project: sqlalchemy
Source File: test_cycles.py
View license
    def test_post_update_o2m(self):
        """A cycle between two rows, with a post_update on the one-to-many"""

        person, ball, Ball, Person = (self.tables.person,
                                self.tables.ball,
                                self.classes.Ball,
                                self.classes.Person)


        mapper(Ball, ball)
        mapper(Person, person, properties=dict(
            balls=relationship(Ball,
                           primaryjoin=ball.c.person_id == person.c.id,
                           remote_side=ball.c.person_id,
                           cascade="all, delete-orphan",
                           post_update=True,
                           backref='person'),
            favorite=relationship(Ball,
                              primaryjoin=person.c.favorite_ball_id == ball.c.id,
                              remote_side=person.c.favorite_ball_id)))

        b = Ball(data='some data')
        p = Person(data='some data')
        p.balls.append(b)
        b2 = Ball(data='some data')
        p.balls.append(b2)
        b3 = Ball(data='some data')
        p.balls.append(b3)
        b4 = Ball(data='some data')
        p.balls.append(b4)
        p.favorite = b
        sess = create_session()
        sess.add_all((b,p,b2,b3,b4))

        self.assert_sql_execution(
            testing.db,
            sess.flush,
            CompiledSQL("INSERT INTO ball (person_id, data) "
             "VALUES (:person_id, :data)",
             {'person_id':None, 'data':'some data'}),

            CompiledSQL("INSERT INTO ball (person_id, data) "
             "VALUES (:person_id, :data)",
             {'person_id':None, 'data':'some data'}),

            CompiledSQL("INSERT INTO ball (person_id, data) "
             "VALUES (:person_id, :data)",
             {'person_id':None, 'data':'some data'}),

            CompiledSQL("INSERT INTO ball (person_id, data) "
             "VALUES (:person_id, :data)",
             {'person_id':None, 'data':'some data'}),

            CompiledSQL("INSERT INTO person (favorite_ball_id, data) "
             "VALUES (:favorite_ball_id, :data)",
             lambda ctx:{'favorite_ball_id':b.id, 'data':'some data'}),

             CompiledSQL("UPDATE ball SET person_id=:person_id "
              "WHERE ball.id = :ball_id",
              lambda ctx:[
                {'person_id':p.id,'ball_id':b.id},
                {'person_id':p.id,'ball_id':b2.id},
                {'person_id':p.id,'ball_id':b3.id},
                {'person_id':p.id,'ball_id':b4.id}
                ]
            ),

        )

        sess.delete(p)

        self.assert_sql_execution(testing.db, sess.flush,
            CompiledSQL("UPDATE ball SET person_id=:person_id "
                "WHERE ball.id = :ball_id",
                lambda ctx:[
                    {'person_id': None, 'ball_id': b.id},
                    {'person_id': None, 'ball_id': b2.id},
                    {'person_id': None, 'ball_id': b3.id},
                    {'person_id': None, 'ball_id': b4.id}
                ]
            ),
            CompiledSQL("DELETE FROM person WHERE person.id = :id",
             lambda ctx:[{'id':p.id}]),

            CompiledSQL("DELETE FROM ball WHERE ball.id = :id",
             lambda ctx:[{'id': b.id},
                         {'id': b2.id},
                         {'id': b3.id},
                         {'id': b4.id}])
        )

Example 15

Project: sqlalchemy
Source File: test_cycles.py
View license
    def test_post_update_m2o_detect_none(self):
        person, ball, Ball, Person = (
            self.tables.person,
            self.tables.ball,
            self.classes.Ball,
            self.classes.Person)

        mapper(Ball, ball, properties={
            'person': relationship(
                Person, post_update=True,
                primaryjoin=person.c.id == ball.c.person_id)
        })
        mapper(Person, person)

        sess = create_session(autocommit=False, expire_on_commit=True)
        sess.add(Ball(person=Person()))
        sess.commit()
        b1 = sess.query(Ball).first()

        # needs to be unloaded
        assert 'person' not in b1.__dict__
        b1.person = None

        self.assert_sql_execution(
            testing.db,
            sess.flush,
            CompiledSQL(
                "UPDATE ball SET person_id=:person_id WHERE ball.id = :ball_id",
                lambda ctx: {'person_id': None, 'ball_id': b1.id})
        )

        is_(b1.person, None)

Example 16

Project: sqlalchemy
Source File: test_cycles.py
View license
    def test_one(self):
        """Post_update only fires off when needed.

        This test case used to produce many superfluous update statements,
        particularly upon delete

        """

        node, Node = self.tables.node, self.classes.Node


        mapper(Node, node, properties={
            'children': relationship(
                Node,
                primaryjoin=node.c.id==node.c.parent_id,
                cascade="all",
                backref=backref("parent", remote_side=node.c.id)
            ),
            'prev_sibling': relationship(
                Node,
                primaryjoin=node.c.prev_sibling_id==node.c.id,
                remote_side=node.c.id,
                uselist=False),
            'next_sibling': relationship(
                Node,
                primaryjoin=node.c.next_sibling_id==node.c.id,
                remote_side=node.c.id,
                uselist=False,
                post_update=True)})

        session = create_session()

        def append_child(parent, child):
            if parent.children:
                parent.children[-1].next_sibling = child
                child.prev_sibling = parent.children[-1]
            parent.children.append(child)

        def remove_child(parent, child):
            child.parent = None
            node = child.next_sibling
            node.prev_sibling = child.prev_sibling
            child.prev_sibling.next_sibling = node
            session.delete(child)
        root = Node('root')

        about = Node('about')
        cats = Node('cats')
        stories = Node('stories')
        bruce = Node('bruce')

        append_child(root, about)
        assert(about.prev_sibling is None)
        append_child(root, cats)
        assert(cats.prev_sibling is about)
        assert(cats.next_sibling is None)
        assert(about.next_sibling is cats)
        assert(about.prev_sibling is None)
        append_child(root, stories)
        append_child(root, bruce)
        session.add(root)
        session.flush()

        remove_child(root, cats)

        # pre-trigger lazy loader on 'cats' to make the test easier
        cats.children
        self.assert_sql_execution(
            testing.db,
            session.flush,
            AllOf(
            CompiledSQL("UPDATE node SET prev_sibling_id=:prev_sibling_id "
             "WHERE node.id = :node_id",
             lambda ctx:{'prev_sibling_id':about.id, 'node_id':stories.id}),

            CompiledSQL("UPDATE node SET next_sibling_id=:next_sibling_id "
             "WHERE node.id = :node_id",
             lambda ctx:{'next_sibling_id':stories.id, 'node_id':about.id}),

            CompiledSQL("UPDATE node SET next_sibling_id=:next_sibling_id "
             "WHERE node.id = :node_id",
             lambda ctx:{'next_sibling_id':None, 'node_id':cats.id}),
            ),

            CompiledSQL("DELETE FROM node WHERE node.id = :id",
             lambda ctx:[{'id':cats.id}])
        )

        session.delete(root)

        self.assert_sql_execution(
            testing.db,
            session.flush,
            CompiledSQL("UPDATE node SET next_sibling_id=:next_sibling_id "
                "WHERE node.id = :node_id",
                lambda ctx: [
                            {'node_id': about.id, 'next_sibling_id': None},
                            {'node_id': stories.id, 'next_sibling_id': None}
                        ]
            ),
            AllOf(
                CompiledSQL("DELETE FROM node WHERE node.id = :id",
                    lambda ctx:{'id':about.id}
                ),
                CompiledSQL("DELETE FROM node WHERE node.id = :id",
                    lambda ctx:{'id':stories.id}
                ),
                CompiledSQL("DELETE FROM node WHERE node.id = :id",
                    lambda ctx:{'id':bruce.id}
                ),
            ),
            CompiledSQL("DELETE FROM node WHERE node.id = :id",
                lambda ctx:{'id':root.id}
            ),
        )
        about = Node('about')
        cats = Node('cats')
        about.next_sibling = cats
        cats.prev_sibling = about
        session.add(about)
        session.flush()
        session.delete(about)
        cats.prev_sibling = None
        session.flush()

Example 17

Project: sqlalchemy
Source File: test_cycles.py
View license
    def test_one(self):
        child1, child2, child3, Parent, parent, Child1, Child2, Child3 = (self.tables.child1,
                                self.tables.child2,
                                self.tables.child3,
                                self.classes.Parent,
                                self.tables.parent,
                                self.classes.Child1,
                                self.classes.Child2,
                                self.classes.Child3)

        mapper(Parent, parent, properties={
            'c1s':relationship(Child1, primaryjoin=child1.c.parent_id==parent.c.id),
            'c2s':relationship(Child2, primaryjoin=child2.c.parent_id==parent.c.id),
            'c3s':relationship(Child3, primaryjoin=child3.c.parent_id==parent.c.id),

            'c1':relationship(Child1, primaryjoin=child1.c.id==parent.c.c1_id, post_update=True),
            'c2':relationship(Child2, primaryjoin=child2.c.id==parent.c.c2_id, post_update=True),
            'c3':relationship(Child3, primaryjoin=child3.c.id==parent.c.c3_id, post_update=True),
        })
        mapper(Child1, child1)
        mapper(Child2, child2)
        mapper(Child3, child3)

        sess = create_session()

        p1 = Parent('p1')
        c11, c12, c13 = Child1('c1'), Child1('c2'), Child1('c3')
        c21, c22, c23 = Child2('c1'), Child2('c2'), Child2('c3')
        c31, c32, c33 = Child3('c1'), Child3('c2'), Child3('c3')

        p1.c1s = [c11, c12, c13]
        p1.c2s = [c21, c22, c23]
        p1.c3s = [c31, c32, c33]
        sess.add(p1)
        sess.flush()

        p1.c1 = c12
        p1.c2 = c23
        p1.c3 = c31

        self.assert_sql_execution(
            testing.db,
            sess.flush,
            CompiledSQL(
                "UPDATE parent SET c1_id=:c1_id, c2_id=:c2_id, c3_id=:c3_id "
                "WHERE parent.id = :parent_id",
                lambda ctx: {'c2_id': c23.id, 'parent_id': p1.id,
                             'c1_id': c12.id, 'c3_id': c31.id}
            )
        )

        p1.c1 = p1.c2 = p1.c3 = None

        self.assert_sql_execution(
            testing.db,
            sess.flush,
            CompiledSQL(
                "UPDATE parent SET c1_id=:c1_id, c2_id=:c2_id, c3_id=:c3_id "
                "WHERE parent.id = :parent_id",
                lambda ctx: {'c2_id': None, 'parent_id': p1.id,
                             'c1_id': None, 'c3_id': None}
            )
        )

Example 18

Project: sqlalchemy
Source File: test_dynamic.py
View license
    def test_noload_append(self):
        # test that a load of User.addresses is not emitted
        # when flushing an append
        User, Address = self._user_address_fixture()

        sess = Session()
        u1 = User(name="jack", addresses=[Address(email_address="a1")])
        sess.add(u1)
        sess.commit()

        u1_id = u1.id
        sess.expire_all()

        u1.addresses.append(Address(email_address='a2'))

        self.assert_sql_execution(
            testing.db,
            sess.flush,
            CompiledSQL(
                "SELECT users.id AS users_id, users.name AS users_name "
                "FROM users WHERE users.id = :param_1",
                lambda ctx: [{"param_1": u1_id}]),
            CompiledSQL(
                "INSERT INTO addresses (user_id, email_address) "
                "VALUES (:user_id, :email_address)",
                lambda ctx: [{'email_address': 'a2', 'user_id': u1_id}]
            )
        )

Example 19

Project: sqlalchemy
Source File: test_dynamic.py
View license
    def test_noload_remove(self):
        # test that a load of User.addresses is not emitted
        # when flushing a remove
        User, Address = self._user_address_fixture()

        sess = Session()
        u1 = User(name="jack", addresses=[Address(email_address="a1")])
        a2 = Address(email_address='a2')
        u1.addresses.append(a2)
        sess.add(u1)
        sess.commit()

        u1_id = u1.id
        a2_id = a2.id
        sess.expire_all()

        u1.addresses.remove(a2)

        self.assert_sql_execution(
            testing.db,
            sess.flush,
            CompiledSQL(
                "SELECT addresses.id AS addresses_id, addresses.email_address "
                "AS addresses_email_address FROM addresses "
                "WHERE addresses.id = :param_1",
                lambda ctx: [{'param_1': a2_id}]
            ),
            CompiledSQL(
                "UPDATE addresses SET user_id=:user_id WHERE addresses.id = "
                ":addresses_id",
                lambda ctx: [{'addresses_id': a2_id, 'user_id': None}]
            ),
            CompiledSQL(
                "SELECT users.id AS users_id, users.name AS users_name "
                "FROM users WHERE users.id = :param_1",
                lambda ctx: [{"param_1": u1_id}]),
        )

Example 20

Project: sqlalchemy
Source File: test_lazy_relations.py
View license
    def test_lazyload_assert_expected_sql(self):
        self._fixture(True)
        City = self.classes.City
        sess = Session(testing.db)
        c1, c2 = sess.query(City).order_by(City.id).all()

        def go():
            eq_(
                [p.id for p in c2.people],
                []
            )

        self.assert_sql_execution(
            testing.db,
            go,
            CompiledSQL(
                "SELECT person.id AS person_id, person.city_id AS "
                "person_city_id FROM person "
                "WHERE person.city_id = :param_1 AND :param_2 = 0",
                {"param_1": 2, "param_2": 1}
            )
        )

Example 21

Project: sqlalchemy
Source File: test_lazy_relations.py
View license
    def test_lazyload_singlecast(self):
        Person = self.classes.Person
        Pet = self.classes.Pet

        s = Session()
        s.add_all([
            Person(id=5), Pet(id=1, person_id=5)
        ])
        s.commit()

        p1 = s.query(Person).first()

        with self.sql_execution_asserter() as asserter:
            p1.pets

        asserter.assert_(
            CompiledSQL(
                "SELECT pets.id AS pets_id, pets.person_id "
                "AS pets_person_id FROM pets "
                "WHERE pets.person_id = CAST(:param_1 AS INTEGER)",
                [{'param_1': 5}]
            )
        )

Example 22

Project: sqlalchemy
Source File: test_naturalpks.py
View license
    def test_rowswitch_doesntfire(self):
        User, Address, users, addresses = (self.classes.User,
                                self.classes.Address,
                                self.tables.users,
                                self.tables.addresses)

        mapper(User, users)
        mapper(Address, addresses, properties={
            'user': relationship(User, passive_updates=True)
        })

        sess = create_session()
        u1 = User(username='ed')
        a1 = Address(user=u1, email='[email protected]')

        sess.add(u1)
        sess.add(a1)
        sess.flush()

        sess.delete(u1)
        sess.delete(a1)

        u2 = User(username='ed')
        a2 = Address(user=u2, email='[email protected]', etc='foo')
        sess.add(u2)
        sess.add(a2)

        from sqlalchemy.testing.assertsql import CompiledSQL

        # test that the primary key columns of addresses are not
        # being updated as well, since this is a row switch.
        self.assert_sql_execution(
            testing.db, sess.flush, CompiledSQL(
                "UPDATE addresses SET etc=:etc WHERE "
                "addresses.username = :addresses_username AND"
                " addresses.email = :addresses_email", {
                    'etc': 'foo', 'addresses_username': 'ed',
                    'addresses_email': '[email protected]'}),
        )

Example 23

Project: sqlalchemy
Source File: test_of_type.py
View license
    def test_all_subq_query(self):
        A, B, B2, C, C2, D = self.classes('A', 'B', 'B2', 'C', 'C2', 'D')

        session = Session(testing.db)

        b_b2 = with_polymorphic(B, [B2], flat=True)
        c_c2 = with_polymorphic(C, [C2], flat=True)

        q = session.query(
            A
        ).options(
            subqueryload(
                A.bs.of_type(b_b2)
            ).subqueryload(
                b_b2.cs.of_type(c_c2)
            ).subqueryload(
                c_c2.ds
            )
        )

        self.assert_sql_execution(
            testing.db,
            q.all,
            CompiledSQL(
                "SELECT t_a.id AS t_a_id FROM t_a",
                {}
            ),
            CompiledSQL(
                "SELECT t_b_1.type AS t_b_1_type, t_b_1.id AS t_b_1_id, "
                "t_b_1.a_id AS t_b_1_a_id, t_b2_1.id AS t_b2_1_id, "
                "anon_1.t_a_id AS anon_1_t_a_id FROM "
                "(SELECT t_a.id AS t_a_id FROM t_a) AS anon_1 "
                "JOIN (t_b AS t_b_1 LEFT OUTER JOIN t_b2 AS t_b2_1 "
                "ON t_b_1.id = t_b2_1.id) ON anon_1.t_a_id = t_b_1.a_id "
                "ORDER BY anon_1.t_a_id",
                {}
            ),
            CompiledSQL(
                "SELECT t_c_1.type AS t_c_1_type, t_c_1.id AS t_c_1_id, "
                "t_c_1.b_id AS t_c_1_b_id, t_c2_1.id AS t_c2_1_id, "
                "t_b_1.id AS t_b_1_id FROM (SELECT t_a.id AS t_a_id FROM t_a) "
                "AS anon_1 JOIN (t_b AS t_b_1 LEFT OUTER JOIN t_b2 AS t_b2_1 "
                "ON t_b_1.id = t_b2_1.id) ON anon_1.t_a_id = t_b_1.a_id "
                "JOIN (t_c AS t_c_1 LEFT OUTER JOIN t_c2 AS t_c2_1 ON "
                "t_c_1.id = t_c2_1.id) ON t_b_1.id = t_c_1.b_id "
                "ORDER BY t_b_1.id",
                {}
            ),
            CompiledSQL(
                "SELECT t_d.id AS t_d_id, t_d.c_id AS t_d_c_id, "
                "t_c_1.id AS t_c_1_id "
                "FROM (SELECT t_a.id AS t_a_id FROM t_a) AS anon_1 "
                "JOIN (t_b AS t_b_1 LEFT OUTER JOIN t_b2 AS t_b2_1 "
                "ON t_b_1.id = t_b2_1.id) "
                "ON anon_1.t_a_id = t_b_1.a_id "
                "JOIN (t_c AS t_c_1 LEFT OUTER JOIN t_c2 AS t_c2_1 "
                "ON t_c_1.id = t_c2_1.id) "
                "ON t_b_1.id = t_c_1.b_id "
                "JOIN t_d ON t_c_1.id = t_d.c_id ORDER BY t_c_1.id",
                {}
            )
        )

Example 24

Project: sqlalchemy
Source File: test_versioning.py
View license
    def _test_insert_col(self, **kw):
        sess = self._fixture(**kw)

        f1 = self.classes.Foo(value='f1')
        sess.add(f1)

        statements = [
            # note that the assertsql tests the rule against
            # "default" - on a "returning" backend, the statement
            # includes "RETURNING"
            CompiledSQL(
                "INSERT INTO version_table (version_id, value) "
                "VALUES (1, :value)",
                lambda ctx: [{'value': 'f1'}]
            )
        ]
        if not testing.db.dialect.implicit_returning:
            # DBs without implicit returning, we must immediately
            # SELECT for the new version id
            statements.append(
                CompiledSQL(
                    "SELECT version_table.version_id "
                    "AS version_table_version_id "
                    "FROM version_table WHERE version_table.id = :param_1",
                    lambda ctx: [{"param_1": 1}]
                )
            )
        self.assert_sql_execution(testing.db, sess.flush, *statements)

Example 25

Project: sqlalchemy
Source File: test_versioning.py
View license
    def _test_update_col(self, **kw):
        sess = self._fixture(**kw)

        f1 = self.classes.Foo(value='f1')
        sess.add(f1)
        sess.flush()

        f1.value = 'f2'

        statements = [
            # note that the assertsql tests the rule against
            # "default" - on a "returning" backend, the statement
            # includes "RETURNING"
            CompiledSQL(
                "UPDATE version_table SET version_id=2, value=:value "
                "WHERE version_table.id = :version_table_id AND "
                "version_table.version_id = :version_table_version_id",
                lambda ctx: [
                    {
                        "version_table_id": 1,
                        "version_table_version_id": 1, "value": "f2"}]
            )
        ]
        if not testing.db.dialect.implicit_returning:
            # DBs without implicit returning, we must immediately
            # SELECT for the new version id
            statements.append(
                CompiledSQL(
                    "SELECT version_table.version_id "
                    "AS version_table_version_id "
                    "FROM version_table WHERE version_table.id = :param_1",
                    lambda ctx: [{"param_1": 1}]
                )
            )
        self.assert_sql_execution(testing.db, sess.flush, *statements)

Example 26

Project: sqlalchemy
Source File: test_versioning.py
View license
    def test_multi_update(self):
        sess = self._fixture()

        f1 = self.classes.Foo(value='f1')
        f2 = self.classes.Foo(value='f2')
        f3 = self.classes.Foo(value='f3')
        sess.add_all([f1, f2, f3])
        sess.flush()

        f1.value = 'f1a'
        f2.value = 'f2a'
        f3.value = 'f3a'

        statements = [
            # note that the assertsql tests the rule against
            # "default" - on a "returning" backend, the statement
            # includes "RETURNING"
            CompiledSQL(
                "UPDATE version_table SET version_id=2, value=:value "
                "WHERE version_table.id = :version_table_id AND "
                "version_table.version_id = :version_table_version_id",
                lambda ctx: [
                    {
                        "version_table_id": 1,
                        "version_table_version_id": 1, "value": "f1a"}]
            ),
            CompiledSQL(
                "UPDATE version_table SET version_id=2, value=:value "
                "WHERE version_table.id = :version_table_id AND "
                "version_table.version_id = :version_table_version_id",
                lambda ctx: [
                    {
                        "version_table_id": 2,
                        "version_table_version_id": 1, "value": "f2a"}]
            ),
            CompiledSQL(
                "UPDATE version_table SET version_id=2, value=:value "
                "WHERE version_table.id = :version_table_id AND "
                "version_table.version_id = :version_table_version_id",
                lambda ctx: [
                    {
                        "version_table_id": 3,
                        "version_table_version_id": 1, "value": "f3a"}]
            )
        ]
        if not testing.db.dialect.implicit_returning:
            # DBs without implicit returning, we must immediately
            # SELECT for the new version id
            statements.extend([
                CompiledSQL(
                    "SELECT version_table.version_id "
                    "AS version_table_version_id "
                    "FROM version_table WHERE version_table.id = :param_1",
                    lambda ctx: [{"param_1": 1}]
                ),
                CompiledSQL(
                    "SELECT version_table.version_id "
                    "AS version_table_version_id "
                    "FROM version_table WHERE version_table.id = :param_1",
                    lambda ctx: [{"param_1": 2}]
                ),
                CompiledSQL(
                    "SELECT version_table.version_id "
                    "AS version_table_version_id "
                    "FROM version_table WHERE version_table.id = :param_1",
                    lambda ctx: [{"param_1": 3}]
                )
            ])
        self.assert_sql_execution(testing.db, sess.flush, *statements)

Example 27

Project: sqlalchemy
Source File: test_versioning.py
View license
    def test_delete_col(self):
        sess = self._fixture()

        f1 = self.classes.Foo(value='f1')
        sess.add(f1)
        sess.flush()

        sess.delete(f1)

        statements = [
            # note that the assertsql tests the rule against
            # "default" - on a "returning" backend, the statement
            # includes "RETURNING"
            CompiledSQL(
                "DELETE FROM version_table "
                "WHERE version_table.id = :id AND "
                "version_table.version_id = :version_id",
                lambda ctx: [{"id": 1, "version_id": 1}]
            )
        ]
        self.assert_sql_execution(testing.db, sess.flush, *statements)

Example 28

Project: sqlalchemy
Source File: test_constraints.py
View license
    @testing.provide_metadata
    def test_pk_fk_constraint_create(self):
        metadata = self.metadata

        Table('employees', metadata,
              Column('id', Integer),
              Column('soc', String(40)),
              Column('name', String(30)),
              PrimaryKeyConstraint('id', 'soc')
              )
        Table('elements', metadata,
              Column('id', Integer),
              Column('stuff', String(30)),
              Column('emp_id', Integer),
              Column('emp_soc', String(40)),
              PrimaryKeyConstraint('id', name='elements_primkey'),
              ForeignKeyConstraint(['emp_id', 'emp_soc'],
                                   ['employees.id', 'employees.soc'])
              )
        self.assert_sql_execution(
            testing.db,
            lambda: metadata.create_all(checkfirst=False),
            CompiledSQL('CREATE TABLE employees ('
                        'id INTEGER NOT NULL, '
                        'soc VARCHAR(40) NOT NULL, '
                        'name VARCHAR(30), '
                        'PRIMARY KEY (id, soc)'
                        ')'
                        ),
            CompiledSQL('CREATE TABLE elements ('
                        'id INTEGER NOT NULL, '
                        'stuff VARCHAR(30), '
                        'emp_id INTEGER, '
                        'emp_soc VARCHAR(40), '
                        'CONSTRAINT elements_primkey PRIMARY KEY (id), '
                        'FOREIGN KEY(emp_id, emp_soc) '
                        'REFERENCES employees (id, soc)'
                        ')'
                        )
        )

Example 29

Project: sqlalchemy
Source File: test_constraints.py
View license
    @testing.force_drop_names('a', 'b')
    def test_fk_cant_drop_cycled_unnamed(self):
        metadata = MetaData()

        Table("a", metadata,
              Column('id', Integer, primary_key=True),
              Column('bid', Integer),
              ForeignKeyConstraint(["bid"], ["b.id"])
              )
        Table(
            "b", metadata,
            Column('id', Integer, primary_key=True),
            Column("aid", Integer),
            ForeignKeyConstraint(["aid"], ["a.id"]))
        metadata.create_all(testing.db)
        if testing.db.dialect.supports_alter:
            assert_raises_message(
                exc.CircularDependencyError,
                "Can't sort tables for DROP; an unresolvable foreign key "
                "dependency exists between tables: a, b.  Please ensure "
                "that the ForeignKey and ForeignKeyConstraint objects "
                "involved in the cycle have names so that they can be "
                "dropped using DROP CONSTRAINT.",
                metadata.drop_all, testing.db
            )
        else:
            with expect_warnings(
                    "Can't sort tables for DROP; an unresolvable "
                    "foreign key dependency "):
                with self.sql_execution_asserter() as asserter:
                    metadata.drop_all(testing.db, checkfirst=False)

            asserter.assert_(
                AllOf(
                    CompiledSQL("DROP TABLE a"),
                    CompiledSQL("DROP TABLE b")
                )
            )

Example 30

Project: sqlalchemy
Source File: test_constraints.py
View license
    def _assert_cyclic_constraint_supports_alter(self, metadata, auto=False):
        table_assertions = []
        if auto:
            table_assertions = [
                CompiledSQL('CREATE TABLE b ('
                            'id INTEGER NOT NULL, '
                            'aid INTEGER, '
                            'PRIMARY KEY (id)'
                            ')'
                            ),
                CompiledSQL(
                    'CREATE TABLE a ('
                    'id INTEGER NOT NULL, '
                    'bid INTEGER, '
                    'PRIMARY KEY (id)'
                    ')'
                )
            ]
        else:
            table_assertions = [
                CompiledSQL('CREATE TABLE b ('
                            'id INTEGER NOT NULL, '
                            'aid INTEGER, '
                            'PRIMARY KEY (id)'
                            ')'
                            ),
                CompiledSQL(
                    'CREATE TABLE a ('
                    'id INTEGER NOT NULL, '
                    'bid INTEGER, '
                    'PRIMARY KEY (id), '
                    'FOREIGN KEY(bid) REFERENCES b (id)'
                    ')'
                )
            ]

        assertions = [AllOf(*table_assertions)]
        fk_assertions = []
        fk_assertions.append(
            CompiledSQL('ALTER TABLE b ADD CONSTRAINT bfk '
                        'FOREIGN KEY(aid) REFERENCES a (id)')
        )
        if auto:
            fk_assertions.append(
                CompiledSQL('ALTER TABLE a ADD '
                            'FOREIGN KEY(bid) REFERENCES b (id)')
            )
        assertions.append(AllOf(*fk_assertions))

        with self.sql_execution_asserter() as asserter:
            metadata.create_all(checkfirst=False)
        asserter.assert_(*assertions)

        assertions = [
            CompiledSQL('ALTER TABLE b DROP CONSTRAINT bfk'),
            CompiledSQL("DROP TABLE a"),
            CompiledSQL("DROP TABLE b")
        ]

        with self.sql_execution_asserter() as asserter:
            metadata.drop_all(checkfirst=False),
        asserter.assert_(*assertions)

Example 31

Project: sqlalchemy
Source File: test_constraints.py
View license
    def _assert_cyclic_constraint_no_alter(
            self, metadata, auto=False, sqlite_warning=False):
        table_assertions = []
        if auto:
            table_assertions.append(
                DialectSQL(
                    'CREATE TABLE b ('
                    'id INTEGER NOT NULL, '
                    'aid INTEGER, '
                    'PRIMARY KEY (id), '
                    'CONSTRAINT bfk FOREIGN KEY(aid) REFERENCES a (id)'
                    ')'
                )
            )
            table_assertions.append(
                DialectSQL(
                    'CREATE TABLE a ('
                    'id INTEGER NOT NULL, '
                    'bid INTEGER, '
                    'PRIMARY KEY (id), '
                    'FOREIGN KEY(bid) REFERENCES b (id)'
                    ')'
                )
            )
        else:
            table_assertions.append(
                DialectSQL(
                    'CREATE TABLE b ('
                    'id INTEGER NOT NULL, '
                    'aid INTEGER, '
                    'PRIMARY KEY (id), '
                    'CONSTRAINT bfk FOREIGN KEY(aid) REFERENCES a (id)'
                    ')'
                )
            )

            table_assertions.append(
                DialectSQL(
                    'CREATE TABLE a ('
                    'id INTEGER NOT NULL, '
                    'bid INTEGER, '
                    'PRIMARY KEY (id), '
                    'FOREIGN KEY(bid) REFERENCES b (id)'
                    ')'
                )
            )

        assertions = [AllOf(*table_assertions)]

        with self.sql_execution_asserter() as asserter:
            metadata.create_all(checkfirst=False)
        asserter.assert_(*assertions)

        assertions = [AllOf(
            CompiledSQL("DROP TABLE a"),
            CompiledSQL("DROP TABLE b")
        )]

        if sqlite_warning:
            with expect_warnings("Can't sort tables for DROP; "):
                with self.sql_execution_asserter() as asserter:
                    metadata.drop_all(checkfirst=False),
        else:
            with self.sql_execution_asserter() as asserter:
                metadata.drop_all(checkfirst=False),
        asserter.assert_(*assertions)

Example 32

Project: sqlalchemy
Source File: test_constraints.py
View license
    @testing.force_drop_names("a", "b")
    def test_cycle_unnamed_fks(self):
        metadata = MetaData(testing.db)

        Table("a", metadata,
              Column('id', Integer, primary_key=True),
              Column('bid', Integer, ForeignKey("b.id")),
              )

        Table("b", metadata,
              Column('id', Integer, primary_key=True),
              Column("aid", Integer, ForeignKey("a.id")),
              )

        assertions = [
            AllOf(
                CompiledSQL(
                    'CREATE TABLE b ('
                    'id INTEGER NOT NULL, '
                    'aid INTEGER, '
                    'PRIMARY KEY (id)'
                    ')'
                ),
                CompiledSQL(
                    'CREATE TABLE a ('
                    'id INTEGER NOT NULL, '
                    'bid INTEGER, '
                    'PRIMARY KEY (id)'
                    ')'
                )
            ),
            AllOf(
                CompiledSQL('ALTER TABLE b ADD '
                            'FOREIGN KEY(aid) REFERENCES a (id)'),
                CompiledSQL('ALTER TABLE a ADD '
                            'FOREIGN KEY(bid) REFERENCES b (id)')
            ),
        ]
        with self.sql_execution_asserter() as asserter:
            metadata.create_all(checkfirst=False)

        if testing.db.dialect.supports_alter:
            asserter.assert_(*assertions)

            assert_raises_message(
                exc.CircularDependencyError,
                "Can't sort tables for DROP; an unresolvable foreign key "
                "dependency exists between tables: a, b.  "
                "Please ensure that the "
                "ForeignKey and ForeignKeyConstraint objects involved in the "
                "cycle have names so that they can be dropped using "
                "DROP CONSTRAINT.",
                metadata.drop_all, checkfirst=False
            )
        else:
            with expect_warnings(
                    "Can't sort tables for DROP; an unresolvable "
                    "foreign key dependency exists between tables"):
                with self.sql_execution_asserter() as asserter:
                    metadata.drop_all(checkfirst=False)

            asserter.assert_(
                AllOf(
                    CompiledSQL("DROP TABLE b"),
                    CompiledSQL("DROP TABLE a"),
                )
            )

Example 33

Project: sqlalchemy
Source File: test_constraints.py
View license
    @testing.force_drop_names("a", "b")
    def test_cycle_named_fks(self):
        metadata = MetaData(testing.db)

        Table("a", metadata,
              Column('id', Integer, primary_key=True),
              Column('bid', Integer, ForeignKey("b.id")),
              )

        Table("b", metadata,
              Column('id', Integer, primary_key=True),
              Column(
                  "aid", Integer,
                  ForeignKey("a.id", use_alter=True, name='aidfk')),
              )

        assertions = [
            AllOf(
                CompiledSQL(
                    'CREATE TABLE b ('
                    'id INTEGER NOT NULL, '
                    'aid INTEGER, '
                    'PRIMARY KEY (id)'
                    ')'
                ),
                CompiledSQL(
                    'CREATE TABLE a ('
                    'id INTEGER NOT NULL, '
                    'bid INTEGER, '
                    'PRIMARY KEY (id), '
                    'FOREIGN KEY(bid) REFERENCES b (id)'
                    ')'
                )
            ),
            CompiledSQL('ALTER TABLE b ADD CONSTRAINT aidfk '
                        'FOREIGN KEY(aid) REFERENCES a (id)'),
        ]
        with self.sql_execution_asserter() as asserter:
            metadata.create_all(checkfirst=False)

        if testing.db.dialect.supports_alter:
            asserter.assert_(*assertions)

            with self.sql_execution_asserter() as asserter:
                metadata.drop_all(checkfirst=False)

            asserter.assert_(
                CompiledSQL("ALTER TABLE b DROP CONSTRAINT aidfk"),
                AllOf(
                    CompiledSQL("DROP TABLE b"),
                    CompiledSQL("DROP TABLE a"),
                )
            )
        else:
            with self.sql_execution_asserter() as asserter:
                metadata.drop_all(checkfirst=False)

            asserter.assert_(
                AllOf(
                    CompiledSQL("DROP TABLE b"),
                    CompiledSQL("DROP TABLE a"),
                ),
            )

Example 34

Project: sqlalchemy
Source File: test_constraints.py
View license
    @testing.requires.check_constraints
    @testing.provide_metadata
    def test_check_constraint_create(self):
        metadata = self.metadata

        Table('foo', metadata,
              Column('id', Integer, primary_key=True),
              Column('x', Integer),
              Column('y', Integer),
              CheckConstraint('x>y'))
        Table('bar', metadata,
              Column('id', Integer, primary_key=True),
              Column('x', Integer, CheckConstraint('x>7')),
              Column('z', Integer)
              )

        self.assert_sql_execution(
            testing.db,
            lambda: metadata.create_all(checkfirst=False),
            AllOf(
                CompiledSQL('CREATE TABLE foo ('
                            'id INTEGER NOT NULL, '
                            'x INTEGER, '
                            'y INTEGER, '
                            'PRIMARY KEY (id), '
                            'CHECK (x>y)'
                            ')'
                            ),
                CompiledSQL('CREATE TABLE bar ('
                            'id INTEGER NOT NULL, '
                            'x INTEGER CHECK (x>7), '
                            'z INTEGER, '
                            'PRIMARY KEY (id)'
                            ')'
                            )
            )
        )

Example 35

Project: sqlalchemy
Source File: test_constraints.py
View license
    @testing.provide_metadata
    def test_unique_constraint_create(self):
        metadata = self.metadata

        Table('foo', metadata,
              Column('id', Integer, primary_key=True),
              Column('value', String(30), unique=True))
        Table('bar', metadata,
              Column('id', Integer, primary_key=True),
              Column('value', String(30)),
              Column('value2', String(30)),
              UniqueConstraint('value', 'value2', name='uix1')
              )

        self.assert_sql_execution(
            testing.db,
            lambda: metadata.create_all(checkfirst=False),
            AllOf(
                CompiledSQL('CREATE TABLE foo ('
                            'id INTEGER NOT NULL, '
                            'value VARCHAR(30), '
                            'PRIMARY KEY (id), '
                            'UNIQUE (value)'
                            ')'),
                CompiledSQL('CREATE TABLE bar ('
                            'id INTEGER NOT NULL, '
                            'value VARCHAR(30), '
                            'value2 VARCHAR(30), '
                            'PRIMARY KEY (id), '
                            'CONSTRAINT uix1 UNIQUE (value, value2)'
                            ')')
            )
        )

Example 36

Project: sqlalchemy
Source File: test_constraints.py
View license
    @testing.provide_metadata
    def test_index_create(self):
        metadata = self.metadata

        employees = Table('employees', metadata,
                          Column('id', Integer, primary_key=True),
                          Column('first_name', String(30)),
                          Column('last_name', String(30)),
                          Column('email_address', String(30)))

        i = Index('employee_name_index',
                  employees.c.last_name, employees.c.first_name)
        assert i in employees.indexes

        i2 = Index('employee_email_index',
                   employees.c.email_address, unique=True)
        assert i2 in employees.indexes

        self.assert_sql_execution(
            testing.db,
            lambda: metadata.create_all(checkfirst=False),
            RegexSQL("^CREATE TABLE"),
            AllOf(
                CompiledSQL('CREATE INDEX employee_name_index ON '
                            'employees (last_name, first_name)', []),
                CompiledSQL('CREATE UNIQUE INDEX employee_email_index ON '
                            'employees (email_address)', [])
            )
        )

Example 37

Project: sqlalchemy
Source File: test_constraints.py
View license
    @testing.provide_metadata
    def test_index_create_camelcase(self):
        """test that mixed-case index identifiers are legal"""

        metadata = self.metadata

        employees = Table('companyEmployees', metadata,
                          Column('id', Integer, primary_key=True),
                          Column('firstName', String(30)),
                          Column('lastName', String(30)),
                          Column('emailAddress', String(30)))

        Index('employeeNameIndex',
              employees.c.lastName, employees.c.firstName)

        Index('employeeEmailIndex',
              employees.c.emailAddress, unique=True)

        self.assert_sql_execution(
            testing.db, lambda: metadata.create_all(
                checkfirst=False), RegexSQL("^CREATE TABLE"), AllOf(
                CompiledSQL(
                    'CREATE INDEX "employeeNameIndex" ON '
                    '"companyEmployees" ("lastName", "firstName")', []),
                CompiledSQL(
                    'CREATE UNIQUE INDEX "employeeEmailIndex" ON '
                    '"companyEmployees" ("emailAddress")', [])))

Example 38

Project: sqlalchemy
Source File: test_constraints.py
View license
    @testing.provide_metadata
    def test_index_create_inline(self):
        # test an index create using index=True, unique=True

        metadata = self.metadata

        events = Table('events', metadata,
                       Column('id', Integer, primary_key=True),
                       Column('name', String(30), index=True, unique=True),
                       Column('location', String(30), index=True),
                       Column('sport', String(30)),
                       Column('announcer', String(30)),
                       Column('winner', String(30)))

        Index('sport_announcer', events.c.sport, events.c.announcer,
              unique=True)
        Index('idx_winners', events.c.winner)

        eq_(
            set(ix.name for ix in events.indexes),
            set(['ix_events_name', 'ix_events_location',
                 'sport_announcer', 'idx_winners'])
        )

        self.assert_sql_execution(
            testing.db,
            lambda: events.create(testing.db),
            RegexSQL("^CREATE TABLE events"),
            AllOf(
                CompiledSQL('CREATE UNIQUE INDEX ix_events_name ON events '
                         '(name)'),
                CompiledSQL('CREATE INDEX ix_events_location ON events '
                         '(location)'),
                CompiledSQL('CREATE UNIQUE INDEX sport_announcer ON events '
                         '(sport, announcer)'),
                CompiledSQL('CREATE INDEX idx_winners ON events (winner)'),
            )
        )