#!/usr/bin/env python2.4
# encoding: utf-8
"""
sa test.py

Demonstration of unexpected behavior of secondary mapper in SQLAlchemy 0.2.7
"""

from sqlalchemy import *

metadata = BoundMetaData('sqlite:///test.db')
metadata.engine.echo = False
session = create_session()

def create_data():
    ro_table = Table('RO_User', metadata,
        Column('id', Integer),
        Column('name', String(40))
    )

    rw_table = Table('RW_User', metadata,
        Column('id', Integer),
        Column('name', String(40))
    )

    # Create new empty tables
    metadata.drop_all()
    metadata.create_all()
    
    # insert some user in ro_table
    ro_table.insert().execute({'id':1, 'name':'RO User 1'}, 
                                {'id':2, 'name':'RO User 2'}, 
                                {'id':3, 'name':'RO User 3'}, 
                                {'id':4, 'name':'RO User 4'})
    
    return (ro_table, rw_table)

class User(object):
    def __repr__(self):
        return "User %d: %s" % (self.id, self.name)

def main():
    
    ro_table, rw_table = create_data()
    
    pri_mapper = mapper(User, rw_table, primary_key=[rw_table.c.id])
    sec_mapper = mapper(User, ro_table, primary_key=[ro_table.c.id], non_primary=True)
    
    # A query from the User class
    qry_obj = session.query(User)
    # A query from primary mapper
    qry_pri = session.query(pri_mapper)
    # A query from secondary mapper
    qry_sec = session.query(sec_mapper)
    
    # Try to get User #1 from the query associated with the class
    # Returns None, as only rw_table is searched
    test = qry_obj.get(1)
    print 'From qry_obj: ', test
    
    # Idem from qry_pri
    test = qry_pri.get(1)
    print 'From qry_pri: ', test
    
    # Using secondary mapper loads the data from ro_table, as expected
    old_user = qry_sec.get(1)
    print 'From qry_sec:', old_user
    
    # As the mappers share the object cache, we can now get this user from qry_obj and qry_pri
    test = qry_pri.get(1)
    print 'From qry_pri (direct from cache): ', test
    
    
    # Create a new User using the primary mapper
    new_user = User()
    new_user.id = 9999
    new_user.name = 'New Created User 9999'
    session.save(new_user)
    session.flush()
    
    session.clear()
    
    # Check that User 9999 was created
    new_user = qry_obj.get(9999)
    print 'Created user: ', new_user
    
    # Get or old friend User 1
    old_user = qry_sec.get(1)
    
    # Update the two users
    new_user.name = 'modif #1 for user 9999'
    old_user.name = 'modif #1 for user 1'
    
    # This fails as flush try to UPDATE User 1 in RW_User (where it is not yet created)
    try:
        session.flush()
    except exceptions.FlushError, msg:
        print "FLUSH FAILED : ", msg
        
    session.clear()
    
    # Retry, but force old_user to be inserted in RW_User...
    new_user = qry_obj.get(9999)
    old_user = qry_sec.get(1)
    new_user.name = 'modif #2 for user 9999'
    old_user.name = 'modif #2 for user 1'
    
    # Looking at mapper.save_obj shows that object having _instance_key are updated
    # remove it to force an insert
    delattr(old_user, '_instance_key')
    
    session.flush()
    session.clear()
    
    # Our two users are now modified, and in RW_User
    print "User 1: ",qry_obj.get(1)
    print "User 9999: ",qry_obj.get(1)

if __name__ == '__main__':
    main()