def _migrate(url, repository, version, upgrade, err, **opts):
    engine = construct_engine(url, **opts)
    schema = ControlledSchema(engine, repository)
    version = _migrate_version(schema, version, upgrade, err)

    changeset = schema.changeset(version)
    for ver, change in changeset:
        nextver = ver + changeset.step
        print '%s -> %s... ' % (ver, nextver)

        if opts.get('preview_sql'):
            if isinstance(change, PythonScript):
                print change.preview_sql(url, changeset.step, **opts)
            elif isinstance(change, SqlScript):
                print change.source()

        elif opts.get('preview_py'):
            source_ver = max(ver, nextver)
            module = schema.repository.version(source_ver).script().module
            funcname = upgrade and "upgrade" or "downgrade"
            func = getattr(module, funcname)
            if isinstance(change, PythonScript):
                print inspect.getsource(func)
            else:
                raise UsageError("Python source can be only displayed"
                    " for python migration files")
        else:
            schema.runchange(ver, change, changeset.step)
            print 'done'

def load_environment(global_conf, app_conf):
    """\
    Configure the Pylons environment via the ``pylons.config`` object
    """

    # Pylons paths
    root = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
    paths = dict(root=root,
                 controllers=os.path.join(root, 'controllers'),
                 static_files=os.path.join(root, 'public'),
                 templates=[os.path.join(root, 'templates')])

    # Initialize config with the basic options
    config.init_app(
        global_conf,
        app_conf,
        package='openspending.ui',
        paths=paths)

    config['routes.map'] = routing.make_map()
    config['pylons.app_globals'] = app_globals.Globals()
    config['pylons.h'] = helpers

    # set log level in markdown
    markdown.logger.setLevel(logging.WARN)

    # SQLAlchemy
    engine = engine_from_config(config, 'openspending.db.')
    engine = construct_engine(engine)
    init_model(engine)

    # Configure Solr
    import openspending.lib.solr_util as solr
    solr.configure(config)

 def __init__(self, url, schema=None, reflectMetadata=True, row_type=dict):
     kw = {}
     if url.startswith('postgres'):
         kw['poolclass'] = NullPool
     self.lock = threading.RLock()
     self.local = threading.local()
     if '?' in url:
         url, query = url.split('?', 1)
         query = parse_qs(query)
         if schema is None:
             # le pop
             schema_qs = query.pop('schema', query.pop('searchpath', []))
             if len(schema_qs):
                 schema = schema_qs.pop()
         if len(query):
             url = url + '?' + urlencode(query, doseq=True)
     self.schema = schema
     engine = create_engine(url, **kw)
     self.url = url
     self.engine = construct_engine(engine)
     self.metadata = MetaData(schema=schema)
     self.metadata.bind = self.engine
     if reflectMetadata:
         self.metadata.reflect(self.engine)
     self.row_type = row_type
     self._tables = {}

def drop_version_control(url, repository, **opts):
    """%prog drop_version_control URL REPOSITORY_PATH

    Removes version control from a database.
    """
    engine = construct_engine(url, **opts)
    schema = ControlledSchema(engine, repository)
    schema.drop()

def create_model(url, repository, **opts):
    """%prog create_model URL REPOSITORY_PATH

    Dump the current database as a Python model to stdout.

    NOTE: This is EXPERIMENTAL.
    """  # TODO: get rid of EXPERIMENTAL label
    engine = construct_engine(url, **opts)
    declarative = opts.get('declarative', False)
    print ControlledSchema.create_model(engine, repository, declarative)

def compare_model_to_db(url, model, repository, **opts):
    """%prog compare_model_to_db URL MODEL REPOSITORY_PATH

    Compare the current model (assumed to be a module level variable
    of type sqlalchemy.MetaData) against the current database.

    NOTE: This is EXPERIMENTAL.
    """  # TODO: get rid of EXPERIMENTAL label
    engine = construct_engine(url, **opts)
    print ControlledSchema.compare_model_to_db(engine, model, repository)

def patched_with_engine(f, *a, **kw):
    url = a[0]
    engine = migrate_util.construct_engine(url, **kw)
    try:
        kw['engine'] = engine
        return f(*a, **kw)
    finally:
        if isinstance(engine, migrate_util.Engine) and engine is not url:
            migrate_util.log.debug('Disposing SQLAlchemy engine %s', engine)
            engine.dispose()

def connect(url):
    """ Create an engine for the given database URL. """
    kw = {}
    if url.startswith('postgres'):
        kw['pool_size'] = 1
    engine = create_engine(url, **kw)
    engine = construct_engine(engine)
    meta = MetaData()
    meta.bind = engine
    engine._metadata = meta
    return engine

def make_update_script_for_model(url, oldmodel, model, repository, **opts):
    """%prog make_update_script_for_model URL OLDMODEL MODEL REPOSITORY_PATH

    Create a script changing the old Python model to the new (current)
    Python model, sending to stdout.

    NOTE: This is EXPERIMENTAL.
    """  # TODO: get rid of EXPERIMENTAL label
    engine = construct_engine(url, **opts)
    print PythonScript.make_update_script_for_model(
        engine, oldmodel, model, repository, **opts)

    def preview_sql(self, url, step, **args):
        """Mock engine to store all executable calls in a string \
        and execute the step"""
        buf = StringIO()
        args['engine_arg_strategy'] = 'mock'
        args['engine_arg_executor'] = lambda s, p='': buf.write(s + p)
        engine = construct_engine(url, **args)

        self.run(engine, step)

        return buf.getvalue()

def db_version(url, repository, **opts):
    """%prog db_version URL REPOSITORY_PATH

    Show the current version of the repository with the given
    connection string, under version control of the specified
    repository.

    The url should be any valid SQLAlchemy connection string.
    """
    engine = construct_engine(url, **opts)
    schema = ControlledSchema(engine, repository)
    return schema.version

 def __init__(self, url):
     kw = {}
     if url.startswith('postgres'):
         kw['poolclass'] = NullPool
     engine = create_engine(url, **kw)
     self.lock = RLock()
     self.url = url
     self.engine = construct_engine(engine)
     self.metadata = MetaData()
     self.metadata.bind = self.engine
     self.metadata.reflect(self.engine)
     self._tables = {}

def update_db_from_model(url, model, repository, **opts):
    """%prog update_db_from_model URL MODEL REPOSITORY_PATH

    Modify the database to match the structure of the current Python
    model. This also sets the db_version number to the latest in the
    repository.

    NOTE: This is EXPERIMENTAL.
    """  # TODO: get rid of EXPERIMENTAL label
    engine = construct_engine(url, **opts)
    schema = ControlledSchema(engine, repository)
    schema.update_db_from_model(model)

def load_environment(global_conf, app_conf):
    """\
    Configure the Pylons environment via the ``pylons.config`` object
    """

    # Pylons paths
    root = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
    paths = dict(root=root,
                 controllers=os.path.join(root, 'controllers'),
                 static_files=os.path.join(root, 'public'),
                 templates=[os.path.join(root, 'templates')])

    # Initialize config with the basic options
    config.init_app(global_conf, app_conf, package='openspending.ui', paths=paths)

    config['routes.map'] = routing.make_map()
    config['pylons.app_globals'] = app_globals.Globals()
    config['pylons.h'] = helpers

    # set log level in markdown
    markdown.logger.setLevel(logging.WARN)

    # Establish celery loader:
    from openspending.command import celery

    # Translator (i18n)
    config['openspending.ui.translations'] = MultiDomainTranslator([config.get('lang', 'en')])
    translator = Translator(config['openspending.ui.translations'])
    def template_loaded(template):
        translator.setup(template)

    template_paths = [paths['templates'][0]]
    extra_template_paths = config.get('extra_template_paths', '')
    if extra_template_paths:
        # must be first for them to override defaults
        template_paths = extra_template_paths.split(',') + template_paths

    # Create the Genshi TemplateLoader
    config['pylons.app_globals'].genshi_loader = TemplateLoader(
        search_path=template_paths,
        auto_reload=True,
        callback=template_loaded
    )

    # SQLAlchemy
    engine = engine_from_config(config, 'openspending.db.')
    engine = construct_engine(engine)
    init_model(engine)

    # Configure Solr
    import openspending.lib.solr_util as solr
    solr.configure(config)

    def setup_database(self):
        """
        Configure the database based on the provided configuration
        file, but be sure to overwrite the url so that it will use
        sqlite in memory, irrespective of what the user has set in
        test.ini. Construct the sqlalchemy engine with versioning
        and initialise everything.
        """

        config['openspending.db.url'] = 'sqlite:///:memory:'
        engine = engine_from_config(config, 'openspending.db.')
        engine = construct_engine(engine)
        init_model(engine)

def connect(url):
    """ Create an engine for the given database URL. """
    kw = {}
    if url.startswith('postgres'):
        #kw['pool_size'] = 5
        from sqlalchemy.pool import NullPool
        kw['poolclass'] = NullPool
    engine = create_engine(url, **kw)
    engine = construct_engine(engine)
    meta = MetaData()
    meta.bind = engine
    engine._metadata = meta
    return engine

def setup_package():
    '''
    Create a new, not scoped  global sqlalchemy session
    and rebind it to a new root transaction to which we can roll
    back. Otherwise :func:`adhocracy.model.init_model`
    will create as scoped session and invalidates
    the connection we need to begin a new root transaction.

    Return: The new root `connection`
    '''
    from sqlalchemy import engine_from_config
    from migrate.versioning.util import construct_engine
    config['openspending.db.url'] = 'sqlite:///:memory:'
    engine = engine_from_config(config, 'openspending.db.')
    engine = construct_engine(engine)
    init_model(engine)

def load_environment(global_conf, app_conf):
    """Configure the Pylons environment via the ``pylons.config``
    object
    """

    # Pylons paths
    root = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
    paths = dict(root=root,
                 controllers=os.path.join(root, 'controllers'),
                 static_files=os.path.join(root, 'public'),
                 templates=[os.path.join(root, 'templates')])

    # Initialize config with the basic options
    config.init_app(global_conf, app_conf, package='openspending.etl.ui', paths=paths)

    config['routes.map'] = make_map()
    config['pylons.app_globals'] = app_globals.Globals()
    config['pylons.h'] = helpers

    # set log level in markdown
    markdown.logger.setLevel(logging.WARN)

    ## redo template setup to use genshi.search_path (so remove std template setup)
    template_paths = [paths['templates'][0]]

    # Translator (i18n)
    config['openspending.etl.ui.translations'] = MultiDomainTranslator([config.get('lang', 'en')])
    translator = Translator(config['openspending.etl.ui.translations'])

    def template_loaded(template):
        translator.setup(template)

    config['pylons.app_globals'].genshi_loader = TemplateLoader(
        template_paths, auto_reload=True, callback=template_loaded)

    # SQLAlchemy
    engine = engine_from_config(config, 'openspending.db.')
    engine = construct_engine(engine)
    init_model(engine)

    # Configure ckan
    import openspending.etl.importer.ckan as ckan
    ckan.configure(config)

    # Configure Solr
    import openspending.lib.solr_util as solr
    solr.configure(config)

 def __init__(self, url, search_path):
     kw = {}
     if url.startswith('postgres'):
         kw['poolclass'] = NullPool
     engine = create_engine(url, **kw)
     self.lock = RLock()
     self.url = url
     self.search_path = search_path
     self.engine = construct_engine(engine)
     self.metadata = MetaData()
     self.metadata.bind = self.engine
     if self.search_path and url.startswith('postgres'):
         for schema in self.search_path:
             self.metadata.reflect(self.engine, schema=schema)
     else:
         self.metadata.reflect(self.engine)
     self._tables = {}

    def _migrate(self, repository, version, upgrade, **opts):
        engine = construct_engine(self.url, **opts)
        schema = api.ControlledSchema(engine, repository)
        version = self._migrate_version(schema, version, upgrade)

        yield "<h2>Migrating %s</h2>\n" % repository.id

        changeset = schema.changeset(version)
        if not changeset:
            yield "<p>This repository is already up to date.</p>\n"
            return

        for ver, change in changeset:
            nextver = ver + changeset.step
            doc = schema.repository.version(max(ver, nextver)).script().module.__doc__
            yield "<h3>Version %s -> %s - %s</h3>\n" % (ver, nextver, doc)
            for message in schema.runchange(ver, change, changeset.step):
                yield message
            yield "\n<p>Done!</p>\n"