1
from test.lib.testing import eq_, assert_raises, assert_raises_message
2
from test.lib import fixtures, testing
3
from sqlalchemy import Integer, String, ForeignKey, or_, and_, exc, select, func
4
from sqlalchemy.orm import mapper, relationship, backref, Session, joinedload
1
from sqlalchemy.testing import eq_, assert_raises, assert_raises_message
2
from sqlalchemy.testing import fixtures
3
from sqlalchemy import Integer, String, ForeignKey, or_, and_, exc, \
4
select, func, Boolean, case
5
from sqlalchemy.orm import mapper, relationship, backref, Session, \
7
from sqlalchemy import testing
6
from test.lib.schema import Table, Column
9
from sqlalchemy.testing.schema import Table, Column
183
205
eq_([john.age, jack.age, jill.age, jane.age], [15,27,19,27])
184
206
eq_(sess.query(User.age).order_by(User.id).all(), zip([15,27,19,27]))
208
def test_update_against_metadata(self):
209
User, users = self.classes.User, self.tables.users
213
sess.query(users).update({users.c.age: 29}, synchronize_session=False)
214
eq_(sess.query(User.age).order_by(User.id).all(), zip([29,29,29,29]))
186
216
def test_update_with_bindparams(self):
187
217
User = self.classes.User
196
226
eq_([john.age, jack.age, jill.age, jane.age], [25,37,29,27])
197
227
eq_(sess.query(User.age).order_by(User.id).all(), zip([25,37,29,27]))
229
def test_update_without_load(self):
230
User = self.classes.User
234
sess.query(User).filter(User.id == 3).\
235
update({'age': 44}, synchronize_session='fetch')
236
eq_(sess.query(User.age).order_by(User.id).all(), zip([25,47,44,37]))
199
238
def test_update_changes_resets_dirty(self):
200
239
User = self.classes.User
502
541
eq_(sess.query(Document.title).all(), zip(['baz']))
543
class UpdateDeleteFromTest(fixtures.MappedTest):
545
def define_tables(cls, metadata):
546
Table('users', metadata,
547
Column('id', Integer, primary_key=True),
549
Table('documents', metadata,
550
Column('id', Integer, primary_key=True),
551
Column('user_id', None, ForeignKey('users.id')),
552
Column('title', String(32)),
553
Column('flag', Boolean)
557
def setup_classes(cls):
558
class User(cls.Comparable):
561
class Document(cls.Comparable):
565
def insert_data(cls):
566
users = cls.tables.users
568
users.insert().execute([
575
documents = cls.tables.documents
577
documents.insert().execute([
578
dict(id=1, user_id=1, title='foo'),
579
dict(id=2, user_id=1, title='bar'),
580
dict(id=3, user_id=2, title='baz'),
581
dict(id=4, user_id=2, title='hoho'),
582
dict(id=5, user_id=3, title='lala'),
583
dict(id=6, user_id=3, title='bleh'),
587
def setup_mappers(cls):
588
documents, Document, User, users = (cls.tables.documents,
589
cls.classes.Document,
594
mapper(Document, documents, properties={
595
'user': relationship(User, backref='documents')
598
@testing.requires.update_from
599
def test_update_from_joined_subq_test(self):
600
Document = self.classes.Document
603
subq = s.query(func.max(Document.title).label('title')).\
604
group_by(Document.user_id).subquery()
606
s.query(Document).filter(Document.title == subq.c.title).\
607
update({'flag': True}, synchronize_session=False)
610
set(s.query(Document.id, Document.flag)),
612
(1, True), (2, None),
613
(3, None), (4, True),
614
(5, True), (6, None),
618
@testing.requires.update_where_target_in_subquery
619
def test_update_using_in(self):
620
Document = self.classes.Document
623
subq = s.query(func.max(Document.title).label('title')).\
624
group_by(Document.user_id).subquery()
626
s.query(Document).filter(Document.title.in_(subq)).\
627
update({'flag': True}, synchronize_session=False)
630
set(s.query(Document.id, Document.flag)),
632
(1, True), (2, None),
633
(3, None), (4, True),
634
(5, True), (6, None),
638
@testing.requires.update_where_target_in_subquery
639
@testing.requires.standalone_binds
640
def test_update_using_case(self):
641
Document = self.classes.Document
645
subq = s.query(func.max(Document.title).label('title')).\
646
group_by(Document.user_id).subquery()
648
# this would work with Firebird if you do literal_column('1')
650
case_stmt = case([(Document.title.in_(subq), True)], else_=False)
651
s.query(Document).update({'flag': case_stmt}, synchronize_session=False)
654
set(s.query(Document.id, Document.flag)),
656
(1, True), (2, False),
657
(3, False), (4, True),
658
(5, True), (6, False),
504
662
class ExpressionUpdateTest(fixtures.MappedTest):
506
664
def define_tables(cls, metadata):
702
class InheritTest(fixtures.DeclarativeMappedTest):
709
def setup_classes(cls):
710
Base = cls.DeclarativeBasic
713
__tablename__ = 'person'
714
id = Column(Integer, primary_key=True, test_needs_autoincrement=True)
715
type = Column(String(50))
716
name = Column(String(50))
718
class Engineer(Person):
719
__tablename__ = 'engineer'
720
id = Column(Integer, ForeignKey('person.id'), primary_key=True)
721
engineer_name = Column(String(50))
723
class Manager(Person):
724
__tablename__ = 'manager'
725
id = Column(Integer, ForeignKey('person.id'), primary_key=True)
726
manager_name = Column(String(50))
729
def insert_data(cls):
730
Engineer, Person, Manager = cls.classes.Engineer, \
731
cls.classes.Person, cls.classes.Manager
732
s = Session(testing.db)
734
Engineer(name='e1', engineer_name='e1'),
735
Manager(name='m1', manager_name='m1'),
736
Engineer(name='e2', engineer_name='e2'),
741
def test_illegal_metadata(self):
742
person = self.classes.Person.__table__
743
engineer = self.classes.Engineer.__table__
746
assert_raises_message(
747
exc.InvalidRequestError,
748
"This operation requires only one Table or entity be "
749
"specified as the target.",
750
sess.query(person.join(engineer)).update, {}
753
def test_update_subtable_only(self):
754
Engineer = self.classes.Engineer
755
s = Session(testing.db)
756
s.query(Engineer).update({'engineer_name': 'e5'})
759
s.query(Engineer.engineer_name).all(),
763
@testing.requires.update_from
764
def test_update_from(self):
765
Engineer = self.classes.Engineer
766
Person = self.classes.Person
767
s = Session(testing.db)
768
s.query(Engineer).filter(Engineer.id == Person.id).\
769
filter(Person.name == 'e2').update({'engineer_name': 'e5'})
772
set(s.query(Person.name, Engineer.engineer_name)),
773
set([('e1', 'e1', ), ('e2', 'e5')])
776
@testing.only_on('mysql', 'Multi table update')
777
def test_update_from_multitable(self):
778
Engineer = self.classes.Engineer
779
Person = self.classes.Person
780
s = Session(testing.db)
781
s.query(Engineer).filter(Engineer.id == Person.id).\
782
filter(Person.name == 'e2').update({Person.name: 'e22',
783
Engineer.engineer_name: 'e55'})
786
set(s.query(Person.name, Engineer.engineer_name)),
787
set([('e1', 'e1', ), ('e22', 'e55')])