#!/usr/bin/env python
"""A test suite for SAContext (http://sluggo.scrapping.cc/python/sacontext/).

Run with the --help option for usage.

Tested with SAContext 0.3.5 and SQLAlchemy 0.4.0b1 and 0.3.9.
"""
import optparse

import sacontext
import sqlalchemy as sa
from sqlalchemy.orm import mapper

USAGE = """%prog [uri]

Suggestions: 
  %prog
  %prog --echo --echo-pool
  %prog --echo --echo-pool --strategy=boundsession
  %prog --echo --echo-pool --strategy=boundsession --connectionbound"""

DESCRIPTION = """\
SAContext test suite for SQLAlchemy and Pylons.
'uri' is the database to connect to. (ANY EXISTING TABLES WILL BE DELETED!!)
If no URI is specified, a SQLite memory database will be used.
"""

def test1(uri, engine_args, strategy):
    sac = sacontext.SAContext(strategy)
    sac.add_engine(None, uri, engine_args)
    flintstones = sa.Table("Flintstones", sac.metadata,
        sa.Column("name", sa.String(255), primary_key=True),
        sa.Column("sex", sa.String(1), nullable=False),
        sa.Column("pastime", sa.String(255), nullable=False),
        )
    fc = flintstones.columns
    class Flintstone(object):
        def __repr__(self):
            return "<Flintstone '%s'>" % self.name
    mapper(Flintstone, flintstones, extension=sac.ext)
    sac.metadata.drop_all(sac.connectable)
    sac.metadata.create_all(sac.connectable)
    #### Insert the men using SQL.
    # TODO: SA needs "connectable" kw argument to insert(), etc.
    insert = lambda **params: sac.connectable.execute(
        flintstones.insert(), **params)
    insert(name="Fred", sex="M", pastime="bowling")
    insert(name="Barney", sex="M", pastime="being a buddy")
    insert(name="Bam-Bam", sex="M", pastime="clubbing things")
    insert(name="Dino", sex="M", pastime="eating")
    #### Insert the women using ORM.
    wilma = Flintstone()
    wilma.name = "Wilma"
    wilma.sex = "F"
    wilma.pastime = "[Wilma's pastime]"
    betty = Flintstone()
    betty.name = "Betty"
    betty.sex = "F"
    betty.pastime = "[Betty's pastime]"
    # No need to do sac.session.save(wilma) & betty due to sac.ext.
    sac.session.flush()
    #### SQL Select covering both men and women.
    if sacontext.SQLALCHEMY_0_4_API:
        sql = sa.select([fc.name], order_by=[fc.name], bind=sac.connectable)
    else:
        sql = sa.select([fc.name], order_by=[fc.name], engine=sac.connectable)
    names = [x[0] for x in sql.execute().fetchall()]
    assert "Fred" in names, names
    assert "Wilma" in names, names
    assert len(names) == 6, names
    #### ORM query for women.
    q = sac.query(Flintstone).filter(fc.sex=="F").order_by([fc.name])
    women = list(q)   # q.all() isn't in SQLAlchemy 0.3.7.
    assert women[0].name == "Betty", women
    assert women[1].name == "Wilma", women
    #### ORM query covering both men and women.
    q = sac.query(Flintstone).filter(fc.pastime=="clubbing things")
    clubbers = list(q)
    assert len(clubbers) == 1, clubbers
    assert clubbers[0].name == "Bam-Bam", clubbers

def test_pylons_parser():
    config = {
        "sqlalchemy.default.uri":          "sqlite:////tmp/x.sqlite",
        "sqlalchemy.default.echo":         "false",
        "sqlalchemy.default.pool_recycle": "-1",
        "sqlalchemy.engine2.uri":          "mysql://user@example.com/foo",
        "sqlalchemy.engine2.echo":         "true",
        "sqlalchemy.dburi":                "mssql://...",
        }
    parse_engine_options = sacontext.PylonsSAContext.parse_engine_options
    #### Test default engine.
    uri, options = parse_engine_options(config, "default")
    assert uri.startswith("sqlite:"), uri
    assert len(options) == 2, options
    assert options["echo"] is False, options
    assert options["pool_recycle"] == -1, options
    #### Test engine2.
    uri, options = parse_engine_options(config, "engine2")
    assert uri.startswith("mysql:"), uri
    assert len(options) == 1, options
    assert options["echo"] is True, options
    # Neither saw the legacy DBURI, good.
    #### Test an empty config. 
    uri, options = parse_engine_options({}, "broken")
    assert not uri, uri
    assert len(options) == 0, options

#### Main routine ####
class MyOptionParser(optparse.OptionParser):
    pass
    #def format_description(self, description):
    #    """Bypass paragraph flowing for description."""
    #    return description

def main():
    parser = MyOptionParser(usage=USAGE, description=DESCRIPTION)
    pao = parser.add_option
    pao("--echo", action="store_true", help="Display SQL statements")
    pao("--echo-results", action="store_true", 
        help="Display SQL statements & results")
    pao("--echo-pool", action="store_true", 
        help="echo connection pool activity")
    pao("--strategy", action="store", 
        help="""\
Specify ContextStrategy to use (defaults to 'boundmetadata', " "alternative
'boundsession')""")
    pao("--connectionbound", action="store_true", 
        help="Bind session to connections (use with --strategy=boundsession)")
    #pao("--pylons", action="store", default=None,
    #    help='Pylons mode ("0.9.6", "0.9.5", or "fake")')
    opts, args = parser.parse_args()
    if len(args) == 0:
        uri = "sqlite://"
    elif len(args) == 1:
        uri = args[0]
    else:
        parser.error("wrong number of command-line arguments")
    engine_args = {}
    if opts.echo_results:
        engine_args["echo"] = "debug"
    elif opts.echo:
        engine_args["echo"] = True
    
    if opts.echo_pool:
        engine_args["echo_pool"] = True
        
    #kwargs = {}
    #if opts.connectionbound:
    #    kwargs['connectionbound'] = True
    #kwargs['strategy'] = opts.strategy
    strategy = None
    if opts.strategy == "boundsession":
        strategy = sacontext.BoundSessionStrategy(
            connectionbound=opts.connectionbound)
    
    test1(uri, engine_args, strategy)
    test_pylons_parser()
    

if __name__ == "__main__":  main()