#!/usr/bin/env python
"""A demo of SQLAlchemy 0.4 with and without the object-relational mapper (ORM).

Updated 2007-09-12 by Mike Orr. Public domain.
"""
import sys
import pkg_resources
pkg_resources.require("SQLAlchemy>=0.4.0beta3")
import sqlalchemy as sa
from sqlalchemy import orm
from common import *

DSN = "sqlite:///flintstones.sqlite"  # In the current working directory.
#DSN = "sqlite:////absolute/filename.sqlite"  # Absolute path (four slashes).
#DSN = "sqlite:///:memory:"
#DSN = "mysql://user:password@localhost/test"

verbose_args = ["--debug", "--verbose", "--echo"]
VERBOSE = len(sys.argv) > 1 and sys.argv[1] in verbose_args
if VERBOSE:
    print "Verbose mode, will echo all SQL statements executed."
    print "DSN = ", DSN
    print

### Set up the database.

engine = sa.create_engine(DSN, convert_unicode=True, echo=VERBOSE)
meta = sa.MetaData()
session = orm.create_session(bind=engine, transactional=True, autoflush=True)

# SQL table objects.
flintstones = sa.Table("Flintstones", meta,
    sa.Column("name", sa.types.String(255), primary_key=True),
    sa.Column("sex", sa.types.String(1), nullable=False),
    sa.Column("pastime", sa.types.String(255), nullable=False),
    )
fc = flintstones.columns
    
# ORM classes.
# Flintstone class imported above.
orm.mapper(Flintstone, flintstones)
fq = session.query(Flintstone).order_by([Flintstone.name])
# If we were using a session context mapper we could do 'Flintstone.query'.

# Create the tables.
meta.drop_all(bind=engine, checkfirst=True)
meta.create_all(bind=engine)

### Add data

# Insert the men using SQL.
session.execute(flintstones.insert(), [
    {"name": "Fred", "sex": "M", "pastime": "bowling"},
    {"name": "Barney", "sex": "M", "pastime": "being a buddy"},
    {"name": "Bam-Bam", "sex": "M", "pastime": "clubbing things"},
    {"name": "Dino", "sex": "M", "pastime": "eating"},
    ])

# Insert the women using ORM.
wilma = Flintstone("Wilma", "F", "[Wilma's pastime]")
betty = Flintstone("Betty", "F", "[Betty's pastime]")
pebbles = Flintstone("Pebbles", "F", "eating")
session.save(wilma)
session.save(betty)
session.save(pebbles)
session.commit()

### Helper class for report

class Model(ReportModel):
    def exists(self, who):
        # A SQL query.
        sql = sa.select([fc.name], fc.name==who)
        records = session.execute(sql).fetchall()
        return bool(records)
        
    def get_women_names(self):
        # An ORM query.
        women_query = fq.filter_by(sex="F")
        return [x.name for x in women_query]

    def get_roster(self):
        return fq.all()

    def get_bam_bam_pastime(self):
        return fq.get("Bam-Bam").pastime

    def count_sex(self, sex):
        return fq.filter_by(sex=sex).count()

    def sex_change_fred(self):
        # An ORM update.
        fred = fq.get("Fred")
        fred.sex = "F"
        session.flush()  # Write ORM changes to database.
        # Not committing.

    def sex_change_all_men(self):
        # A SQL update.  This is faster than the ORM when making the
        # same change to many records.
        sql = flintstones.update(values={fc.sex: "F"})
        session.execute(sql)
        # Not committing.

    def sex_change_undo(self):
        session.rollback()

### Main routine

def main():
    model = Model()
    report = Report(model)
    report.main()

if __name__ == "__main__":  main()