# Data Mapping A
class User(object):
    # id
    # first_name
    # last_name
    # user_name
    # password
    def __init__(self, first_name, last_name, user_name, password):
        self.first_name = first_name
        self.last_name = last_name
        self.user_name = user_name
        self.password = password        
# Data mapping C       
    def __repr__(self):
        return str(self)
    def __str__(self):
        return "<User %s: %s>" % (self.id, self.user_name)
# Data mapping D
    def _get_name(self):
        return "%s, %s" % (self.last_name, self.first_name)
    def _set_name(self, name):        
        try:
            self.last_name, self.first_name = name.split(', ')
        except:
            raise "InvalidFormat", "Please use the format 'last_name, first_name'"
    name = property(_get_name, _set_name)

    
# Tables
import sqlalchemy as sa
meta = sa.BoundMetaData('sqlite:///piggies2.db')
users = sa.Table("users", meta,
    sa.Column('id', sa.Integer, primary_key=True),
    sa.Column('user_name', sa.String),
    sa.Column('first_name', sa.String),
    sa.Column('last_name', sa.String),
    sa.Column('password', sa.String))
    
groups = sa.Table("groups", meta,
    sa.Column('id', sa.Integer, primary_key=True),
    sa.Column('group_name', sa.String))
    
membership = sa.Table("membership", meta,
    sa.Column('user_id', sa.Integer, sa.ForeignKey("users.id")),
    sa.Column('group_id', sa.Integer,  sa.ForeignKey("groups.id")),
    sa.Column('level', sa.String))
users.create()
groups.create()
membership.create()
# Some Data
users.insert((1, 'michael', 'Michael', 'Carter',  'secret')).execute()
users.insert((2, 'grig', 'Grig', 'Gheorghiu', 'unknown')).execute()                 
groups.insert((1, 'socal piggies')).execute()
groups.insert((2, 'Pomona College')).execute()
membership.insert((1, 1, 'member')).execute()
membership.insert((1, 2, 'student')).execute()
membership.insert((2, 1, 'organizer')).execute()

# Data Mapping B
User.mapper = sa.mapper(User, users)

s = sa.create_session()
u = User('michael', 'carter', 'carn', 'secret')
s.save(u)
s.flush()
u.id


class Group(object):
    def __init__(**kwargs):
        self.__dict__.update(kwargs)
    def __str__(self):
        return "<Group %s: %s>" % (self.id and self.id or '-', self.group_name)
    def __repr__(self): return str(self)
    
    
Group.mapper = sa.mapper(Group, groups)


# List Relations
# TODO: Add