Ejemplo n.º 1
0
def bootstrap_db(config_uri=None, with_migration=True):
    """Bring a blank database to a functional state."""

    db = get_session_maker()

    if with_migration:
        context = MigrationContext.configure(db().connection())
        db_version = context.get_current_revision()

        if db_version:
            sys.stderr.write('Database already initialized. Bailing out.\n')
            sys.exit(0)

        config = Config(config_uri)
        script_dir = ScriptDirectory.from_config(config)
        heads = script_dir.get_heads()

        if len(heads) > 1:
            sys.stderr.write('Error: migration scripts have more than one '
                             'head.\nPlease resolve the situation before '
                             'attempting to bootstrap the database.\n')
            sys.exit(2)

    import assembl.models
    get_metadata().create_all(db().connection())

    # Clean up the sccoped session to allow a later app instantiation.
    if with_migration and heads:
        context = MigrationContext.configure(db().connection())
        context._ensure_version_table()
        # The latter step seems optional?
        # I am unclear as to why we'd migrate after creating tables
        # on a clean database.
        context.stamp(script_dir, heads[0])
    return db
Ejemplo n.º 2
0
def setup_db():
    """Create database and required tables."""
    if not app.config['DATABASE_URI'].startswith('sqlite'):
        try:
            with create_engine(
                app.config['DATABASE_URI'],
            ).connect() as connection:
                connection.execute('CREATE DATABASE {0}'.format(
                    app.config['DATABASE_NAME']))
            print("Database created")
        except sqlalchemy.exc.OperationalError:
            pass
        except sqlalchemy.exc.ProgrammingError:
            # If database already exists
            pass

    engine = create_engine(app.config['SQLALCHEMY_DATABASE_URI'])
    conn = engine.connect()
    context = MigrationContext.configure(conn)
    current_ver = context.get_current_revision()
    if not current_ver:
        print("Automatic DB Upgrade")
        print("Press Ctrl+C when finished")
        upgrade()
        print("Upgrade completed. Press Ctrl+C and runserver again.")
Ejemplo n.º 3
0
def get_migration_context(connection, table_names):
    opts = {
        'include_symbol': partial(include_symbol, table_names),
        'compare_type': True,
        # 'compare_server_default': True  # we don't care about this
    }
    return MigrationContext.configure(connection, opts=opts)
Ejemplo n.º 4
0
def is_alembic_head():
    alembic_cfg = get_alembic_config()
    context = MigrationContext.configure(db_session.connection())
    script = ScriptDirectory.from_config(alembic_cfg)
    current_revision = context.get_current_revision()
    head_revision = script.get_current_head()
    return current_revision == head_revision
Ejemplo n.º 5
0
    def _fixture(self, m1, m2, include_schemas=False):
        self.metadata, model_metadata = m1, m2
        self.metadata.create_all(self.bind)

        with self.bind.connect() as conn:
            self.context = context = MigrationContext.configure(
                connection=conn,
                opts={
                    'compare_type': True,
                    'compare_server_default': True,
                    'target_metadata': model_metadata,
                    'upgrade_token': "upgrades",
                    'downgrade_token': "downgrades",
                    'alembic_module_prefix': 'op.',
                    'sqlalchemy_module_prefix': 'sa.',
                }
            )

            connection = context.bind
            autogen_context = {
                'imports': set(),
                'connection': connection,
                'dialect': connection.dialect,
                'context': context
                }
            diffs = []
            autogenerate._produce_net_changes(connection, model_metadata, diffs,
                                              autogen_context,
                                              object_filters=_default_object_filters,
                                              include_schemas=include_schemas
                                        )
            return diffs
Ejemplo n.º 6
0
def init_db():
    connection = SQLEngine.connect()
    context = MigrationContext.configure(connection)
    current_revision = context.get_current_revision()
    logger.boot('Database revision: %s', current_revision)
    if current_revision is None:
        DataBase.metadata.create_all(SQLEngine)

    config = Config(ALEMBIC_CONFIG)
    script = ScriptDirectory.from_config(config)
    head_revision = script.get_current_head()
    if current_revision is None or current_revision != head_revision:
        logger.boot('Upgrading database to version %s.', head_revision)
        command.upgrade(config, 'head')
        from option import Option
        session = Session()
        options = session.query(Option).first()
        if options is None:
            options = Option()
        options.version = head_revision
        session.add(options)
        from pulse import Pulse
        pulse = session.query(Pulse).first()
        if pulse is None:
            pulse = Pulse()
        session.add(pulse)
        session.commit()
Ejemplo n.º 7
0
def setup_database(alembic_config_path=None, destructive=False):
    """
    Setup database tables (if they do not yet exist).
    """
    if alembic_config_path and not os.path.isfile(alembic_config_path):
        raise UserError('Cannot find Alembic configuration: %s'
                        % alembic_config_path)

    bind = db.session.get_bind()

    if destructive:
        db.Base.metadata.drop_all(bind)

    if destructive or not bind.has_table(Assembly.__tablename__):
        # We assume our migrations will take care of everything if at least
        # the Assembly table exists.
        db.Base.metadata.create_all(bind)

    if alembic_config_path:
        context = MigrationContext.configure(db.session.connection())
        if destructive or context.get_current_revision() is None:
            # We need to close the current session before running Alembic.
            db.session.remove()
            alembic_config = alembic.config.Config(alembic_config_path)
            alembic.command.stamp(alembic_config, 'head')
Ejemplo n.º 8
0
    def check(self):
        engine = create_engine(self.url)

        context = MigrationContext.configure(engine)
        current_rev = context.get_current_revision()

        return self.head == current_rev
Ejemplo n.º 9
0
def bootstrap_db(config_uri=None, engine=None, with_migration=True):
    """Bring a blank database to a functional state."""
    if engine is None:
        engine = create_engine(config_uri)
    db.configure(bind=engine)

    if with_migration:
        context = MigrationContext.configure(engine.connect())
        db_version = context.get_current_revision()

        if db_version:
            sys.stderr.write('Database already initialized. Bailing out.\n')
            sys.exit(2)

        config = Config(config_uri)
        script_dir = ScriptDirectory.from_config(config)
        heads = script_dir.get_heads()

        if len(heads) > 1:
            sys.stderr.write('Error: migration scripts have more than one '
                             'head.\nPlease resolve the situation before '
                             'attempting to bootstrap the database.\n')
            sys.exit(2)

    metadata.create_all(engine)

    with transaction.manager:
        model = MyModel(name='one', value=1)
        db.add(model)

    # Clean up the sccoped session to allow a later app instantiation.
    db.remove()

    if with_migration and heads:
        command.stamp(config, 'head')
Ejemplo n.º 10
0
 def test_render_nothing(self):
     context = MigrationContext.configure(
         connection=self.bind.connect(),
         opts={
             'compare_type': True,
             'compare_server_default': True,
             'target_metadata': self.m1,
             'upgrade_token': "upgrades",
             'downgrade_token': "downgrades",
             'alembic_module_prefix': 'op.',
             'sqlalchemy_module_prefix': 'sa.',
         }
     )
     template_args = {}
     autogenerate._produce_migration_diffs(
         context, template_args, set(),
         include_symbol=lambda name, schema: False
     )
     eq_(re.sub(r"u'", "'", template_args['upgrades']),
         """### commands auto generated by Alembic - please adjust! ###
 pass
 ### end Alembic commands ###""")
     eq_(re.sub(r"u'", "'", template_args['downgrades']),
         """### commands auto generated by Alembic - please adjust! ###
 pass
 ### end Alembic commands ###""")
Ejemplo n.º 11
0
 def setup_class(cls):
     cls.bind = config.db
     cls.conn = cls.bind.connect()
     staging_env()
     cls.migration_context = MigrationContext.configure(
         connection=cls.conn, opts={"compare_type": True, "compare_server_default": True}
     )
Ejemplo n.º 12
0
    def setUp(self):
        self.conn = conn = self.bind.connect()
        ctx_opts = {
            'compare_type': True,
            'compare_server_default': True,
            'target_metadata': self.m2,
            'upgrade_token': "upgrades",
            'downgrade_token': "downgrades",
            'alembic_module_prefix': 'op.',
            'sqlalchemy_module_prefix': 'sa.',
        }
        if self.configure_opts:
            ctx_opts.update(self.configure_opts)
        self.context = context = MigrationContext.configure(
            connection=conn,
            opts=ctx_opts
        )

        connection = context.bind
        self.autogen_context = {
            'imports': set(),
            'connection': connection,
            'dialect': connection.dialect,
            'context': context
        }
Ejemplo n.º 13
0
    def __new__(cls):
        if cls.__instance is None:
            i = object.__new__(cls)

            i.SQLEngine = SQLEngine
            i.DataBase = DataBase
            i.Session = Session

            i.connection = SQLEngine.connect()
            i.context = MigrationContext.configure(i.connection)
            i.current_revision = i.context.get_current_revision()
            logger.boot('Database revision: %s', i.current_revision)

            i.config = Config(ALEMBIC_CONFIG)
            i.script = ScriptDirectory.from_config(i.config)
            i.head_revision = i.script.get_current_head()
            if i.current_revision is None or i.current_revision != i.head_revision:
                logger.boot('Upgrading database to version %s.', i.head_revision)
                command.upgrade(i.config, 'head')
                from option import Option
                from log import Log
                session = Session()
                options = session.query(Option).first()
                if options is None:
                    options = Option()
                    session.add(options)
                options.version = i.head_revision
                session.commit()
                i.current_revision = i.head_revision

            cls.__instance = i
            h = SQLAlchemyHandler()
            logger.addHandler(h)
            return cls.__instance
Ejemplo n.º 14
0
def main():
    if len(sys.argv) < 3:
        sys.stderr.write('Usage: %s CONFIG_URI {bootstrap | ALEMBIC_OPTS}\n'
                         % sys.argv[0])
        sys.exit(1)

    config_uri = sys.argv.pop(1)

    if sys.argv[1] == 'bootstrap':
        bootstrap_db(config_uri)
    else:
        engine = create_engine(config_uri)
        db.configure(bind=engine)
        context = MigrationContext.configure(engine.connect())
        db_version = context.get_current_revision()

        if not db_version:
            sys.stderr.write('Database not initialized.\n'
                             'Try this: "sortie-db-manage %s bootstrap"\n'
                             % config_uri)
            sys.exit(2)

        cmd = ['alembic', '-c', config_uri] + sys.argv[1:]

        print(subprocess.check_output(cmd))
Ejemplo n.º 15
0
def runserver():
    """This command is meant for development. If no configuration is found,
    we start the app listening from all hosts, from port 9999."""

    # Testig Alembic
    engine = create_engine(app.config['SQLALCHEMY_DATABASE_URI'])
    conn = engine.connect()
    context = MigrationContext.configure(conn)
    current_ver = context.get_current_revision()
    if not current_ver:
        print("Automatic DB Upgrade")
        print("Press Ctrl+C when finished")
        upgrade()
        print("Upgrade completed. Press Ctrl+C and runserver again.")

    try:
        from application import config
        PORT = config.Config.PORT
        DEBUG = config.Config.DEBUG
        HOST = config.Config.HOST
    except ImportError:
        DEBUG = False
        PORT = 9999
        HOST = '0.0.0.0'
    app.run(
        port=PORT,
        debug=DEBUG,
        host=HOST,
        threaded=True)
Ejemplo n.º 16
0
def main():
    if len(sys.argv) < 3:
        sys.stderr.write('Usage: %s CONFIG_URI {bootstrap | ALEMBIC_OPTS}\n'
                         % sys.argv[0])
        sys.exit(1)

    config_uri = sys.argv.pop(1)

    if sys.argv[1] == 'bootstrap':
        settings = get_appsettings(config_uri)
        configure_zmq(settings['changes.socket'], False)
        engine = configure_engine(settings, True)
        bootstrap_db(config_uri)
        mark_changed()
        transaction.commit()
    else:
        context = MigrationContext.configure(engine.connect())
        db_version = context.get_current_revision()

        if not db_version:
            sys.stderr.write('Database not initialized.\n'
                             'Try this: "assembl-db-manage %s bootstrap"\n'
                             % config_uri)
            sys.exit(2)

        cmd = ['alembic', '-c', config_uri] + sys.argv[1:]

        print(subprocess.check_output(cmd))
Ejemplo n.º 17
0
    def test_migration(self):
        self.setup_base_db()
        # we have no alembic base revision
        self.assertTrue(self.current_db_revision() is None)

        # run the migration, afterwards the DB is stamped
        self.run_migration()
        db_revision = self.current_db_revision()
        self.assertTrue(db_revision is not None)

        # db revision matches latest alembic revision
        alembic_head = self.alembic_script().get_current_head()
        self.assertEqual(db_revision, alembic_head)

        # compare the db schema from a migrated database to
        # one created fresh from the model definitions
        opts = {
            'compare_type': db_compare_type,
            'compare_server_default': True,
        }
        with self.db.engine.connect() as conn:
            context = MigrationContext.configure(connection=conn, opts=opts)
            metadata_diff = compare_metadata(context, self.head_metadata)

        self.assertEqual(metadata_diff, [])
Ejemplo n.º 18
0
    def test_compare_metadata_include_object(self):
        metadata = self.m2

        def include_object(obj, name, type_, reflected, compare_to):
            if type_ == "table":
                return name in ("extra", "order")
            elif type_ == "column":
                return name != "amount"
            else:
                return True

        context = MigrationContext.configure(
            connection=self.bind.connect(),
            opts={
                'compare_type': True,
                'compare_server_default': True,
                'include_object': include_object,
            }
        )

        diffs = autogenerate.compare_metadata(context, metadata)

        eq_(diffs[0][0], 'remove_table')
        eq_(diffs[0][1].name, "extra")

        eq_(diffs[1][0], "add_column")
        eq_(diffs[1][1], None)
        eq_(diffs[1][2], "order")
        eq_(diffs[1][3], metadata.tables['order'].c.user_id)
Ejemplo n.º 19
0
    def setup_class(cls):
        staging_env()
        cls.bind = cls._get_bind()
        cls.m1 = cls._get_db_schema()
        cls.m1.create_all(cls.bind)
        cls.m2 = cls._get_model_schema()

        conn = cls.bind.connect()
        cls.context = context = MigrationContext.configure(
            connection=conn,
            opts={
                'compare_type': True,
                'compare_server_default':True,
                'target_metadata':cls.m2,
                'upgrade_token':"upgrades",
                'downgrade_token':"downgrades",
                'alembic_module_prefix':'op.',
                'sqlalchemy_module_prefix':'sa.',
            }
        )

        connection = context.bind
        cls.autogen_context = {
            'imports':set(),
            'connection':connection,
            'dialect':connection.dialect,
            'context':context
            }
Ejemplo n.º 20
0
def get_database_version():
    """
    Gets the current database revision (partial GUID).
    :rtype: str
    """
    context = MigrationContext.configure(meta.engine)
    return context.get_current_revision()
Ejemplo n.º 21
0
def ensure_db_version(config_uri, session_maker):
    """Exit if database is not up-to-date."""
    config = Config(config_uri)
    script_dir = ScriptDirectory.from_config(config)
    heads = script_dir.get_heads()

    if len(heads) > 1:
        sys.stderr.write('Error: migration scripts have more than one head.\n'
                         'Please resolve the situation before attempting to '
                         'start the application.\n')
        sys.exit(2)
    else:
        repo_version = heads[0] if heads else None

    context = MigrationContext.configure(session_maker()().connect())
    db_version = context.get_current_revision()

    if not db_version:
        sys.stderr.write('Database not initialized.\n'
                         'Try this: "assembl-db-manage %s bootstrap".\n'
                         % config_uri)
        sys.exit(2)

    if db_version != repo_version:
        sys.stderr.write('Stopping: DB version (%s) not up-to-date (%s).\n'
                         % (db_version, repo_version))
        sys.stderr.write('Try this: "assembl-db-manage %s upgrade head".\n'
                         % config_uri)
        sys.exit(2)
Ejemplo n.º 22
0
def test_migrations(database_uri):
    """
    Run all migrations and assert the result is up to date with the model
    definitions.
    """
    alembic_config = alembic.config.Config('migrations/alembic.ini')
    engine = create_engine(database_uri)

    with engine.begin() as connection:
        # http://alembic.readthedocs.org/en/latest/cookbook.html#sharing-a-connection-with-a-series-of-migration-commands-and-environments
        alembic_config.attributes['connection'] = connection

        if database_uri != 'sqlite://':
            db.Base.metadata.drop_all(connection)

        # Create initial schema by running the first migration.
        alembic.command.upgrade(alembic_config, 'ea660b66f26')

        # Add some database content to run the migrations on.
        add_database_content(connection)

        # Run the remaining migrations.
        alembic.command.upgrade(alembic_config, 'head')

        context = MigrationContext.configure(connection)
        assert not alembic.autogenerate.compare_metadata(
            context, db.Base.metadata)

    engine.dispose()
Ejemplo n.º 23
0
def create_session(db_url, debug=False, pool_recycle=3600):
    """ Create the Session object to use to query the database.

    :arg db_url: URL used to connect to the database. The URL contains
    information with regards to the database engine, the host to connect
    to, the user and password and the database name.
      ie: <engine>://<user>:<password>@<host>/<dbname>
    :kwarg debug: a boolean specifying wether we should have the verbose
        output of sqlalchemy or not.
    :return a Session that can be used to query the database.

    """
    engine = sa.create_engine(
        db_url, echo=debug,
        pool_recycle=pool_recycle,
        convert_unicode=True)
    session = scoped_session(sessionmaker(
        autocommit=False, autoflush=False, bind=engine))
    # check that the database's schema is up-to-date
    script_dir = ScriptDirectory.from_config(get_alembic_config(db_url))
    head_rev = script_dir.get_current_head()
    context = MigrationContext.configure(session.connection())
    current_rev = context.get_current_revision()
    if current_rev != head_rev:
        raise DatabaseNeedsUpgrade
    # everything looks good here
    return session
Ejemplo n.º 24
0
 def test_include_symbol(self):
     context = MigrationContext.configure(
         connection=self.bind.connect(),
         opts={
             'compare_type': True,
             'compare_server_default': True,
             'target_metadata': self.m2,
             'include_symbol': lambda name, schema=None:
             name in ('address', 'order'),
             'upgrade_token': "upgrades",
             'downgrade_token': "downgrades",
             'alembic_module_prefix': 'op.',
             'sqlalchemy_module_prefix': 'sa.',
         }
     )
     template_args = {}
     autogenerate._produce_migration_diffs(context, template_args, set())
     template_args['upgrades'] = \
         template_args['upgrades'].replace("u'", "'")
     template_args['downgrades'] = template_args['downgrades'].\
         replace("u'", "'")
     assert "alter_column('user'" not in template_args['upgrades']
     assert "alter_column('user'" not in template_args['downgrades']
     assert "alter_column('order'" in template_args['upgrades']
     assert "alter_column('order'" in template_args['downgrades']
Ejemplo n.º 25
0
    def test_include_symbol(self):

        diffs = []

        def include_symbol(name, schema=None):
            return name in ('address', 'order')

        context = MigrationContext.configure(
            connection=self.bind.connect(),
            opts={
                'compare_type': True,
                'compare_server_default': True,
                'target_metadata': self.m2,
                'include_symbol': include_symbol,
            }
        )

        diffs = autogenerate.compare_metadata(
            context, context.opts['target_metadata'])

        alter_cols = set([
            d[2] for d in self._flatten_diffs(diffs)
            if d[0].startswith('modify')
        ])
        eq_(alter_cols, set(['order']))
Ejemplo n.º 26
0
 def versiondb(self):
     """Downgrade the database
     """
     engine = create_engine(self.url)
     conn = engine.connect()
     context = MigrationContext.configure(conn)
     return context.get_current_heads()
Ejemplo n.º 27
0
    def _setup_db_connection(self):
        super(SchemaMigration, self)._setup_db_connection()

        self.dialect_name = self.connection.dialect.name
        self.migration_context = MigrationContext.configure(self.connection)
        self.metadata = MetaData(self.connection, reflect=True)
        self.op = self._create_operations()
Ejemplo n.º 28
0
 def setup_database(self):
     context = MigrationContext.configure(self._database.store.connection())
     current_rev = context.get_current_revision()
     head_rev = self._script.get_current_head()
     if current_rev == head_rev:
         # We're already at the latest revision so there's nothing to do.
         return head_rev
     if current_rev is None:
         # No Alembic information is available.
         storm_version = self._get_storm_schema_version()
         if storm_version is None:
             # Initial database creation.
             Model.metadata.create_all(self._database.engine)
             self._database.commit()
             alembic.command.stamp(alembic_cfg, 'head')
         else:
             # The database was previously managed by Storm.
             if storm_version.version < LAST_STORM_SCHEMA_VERSION:
                 raise DatabaseError(
                     'Upgrades skipping beta versions is not supported.')
             # Run migrations to remove the Storm-specific table and upgrade
             # to SQLAlchemy and Alembic.
             alembic.command.upgrade(alembic_cfg, 'head')
     elif current_rev != head_rev:
         alembic.command.upgrade(alembic_cfg, 'head')
     return head_rev
Ejemplo n.º 29
0
    def test_compare_metadata_schema(self):
        metadata = self.m2

        context = MigrationContext.configure(
            connection=self.bind.connect(),
            opts={
                "include_schemas": True
            }
        )

        diffs = autogenerate.compare_metadata(context, metadata)

        eq_(
            diffs[0],
            ('add_table', metadata.tables['test_schema.item'])
        )

        eq_(diffs[1][0], 'remove_table')
        eq_(diffs[1][1].name, "extra")

        eq_(diffs[2][0], "add_column")
        eq_(diffs[2][1], "test_schema")
        eq_(diffs[2][2], "address")
        eq_(diffs[2][3], metadata.tables['test_schema.address'].c.street)

        eq_(diffs[3][0], "add_column")
        eq_(diffs[3][1], "test_schema")
        eq_(diffs[3][2], "order")
        eq_(diffs[3][3], metadata.tables['test_schema.order'].c.user_id)

        eq_(diffs[4][0][0], 'modify_nullable')
        eq_(diffs[4][0][5], False)
        eq_(diffs[4][0][6], True)
Ejemplo n.º 30
0
    def setup_class(cls):
        staging_env()
        cls.bind = sqlite_db()
        cls.m3 = _model_three()
        cls.m3.create_all(cls.bind)
        cls.m4 = _model_four()

        cls.empty_context = empty_context = MigrationContext.configure(
            connection = cls.bind.connect(),
            opts = {
                'compare_type':True,
                'compare_server_default':True,
                'target_metadata':cls.m3,
                'upgrade_token':"upgrades",
                'downgrade_token':"downgrades",
                'alembic_module_prefix':'op.',
                'sqlalchemy_module_prefix':'sa.'
            }
        )

        connection = empty_context.bind
        cls.autogen_empty_context = {
            'imports':set(),
            'connection':connection,
            'dialect':connection.dialect,
            'context':empty_context
            }
Ejemplo n.º 31
0
 def setup_class(cls):
     cls.bind = config.db
     cls.conn = cls.bind.connect()
     staging_env()
     context = MigrationContext.configure(
         connection=cls.conn,
         opts={
             'compare_type': True,
             'compare_server_default': True
         }
     )
     connection = context.bind
     cls.autogen_context = {
         'imports': set(),
         'connection': connection,
         'dialect': connection.dialect,
         'context': context,
         'opts': {
             'compare_type': True,
             'compare_server_default': True,
             'alembic_module_prefix': 'op.',
             'sqlalchemy_module_prefix': 'sa.',
         }
     }
Ejemplo n.º 32
0
    def _schema_init_and_update(self):
        """
        This method tries to create the database table and update the schema.

        :return: None
        """
        try:
            # Try to create the database
            metadata.create_all(self.engine)
        except OperationalError as exx:  # pragma: no cover
            log.info("{0!r}".format(exx))

        # Schema update
        conn = self.engine.connect()
        ctx = MigrationContext.configure(conn)
        op = Operations(ctx)
        try:
            # Try to add resolver column
            op.add_column(
                TABLE_NAME,
                Column('resolver',
                       String(length=column_length.get("resolver"))))
        except Exception as exx:  # pragma: no cover
            log.info("{0!r}".format(exx))
Ejemplo n.º 33
0
 def test_include_symbol(self):
     context = MigrationContext.configure(
         connection=self.bind.connect(),
         opts={
             'compare_type': True,
             'compare_server_default': True,
             'target_metadata': self.m2,
             'include_symbol': lambda name, schema=None:
                                 name in ('address', 'order'),
             'upgrade_token': "upgrades",
             'downgrade_token': "downgrades",
             'alembic_module_prefix': 'op.',
             'sqlalchemy_module_prefix': 'sa.',
         }
     )
     template_args = {}
     autogenerate._produce_migration_diffs(context, template_args, set())
     template_args['upgrades'] = template_args['upgrades'].replace("u'", "'")
     template_args['downgrades'] = template_args['downgrades'].\
                                     replace("u'", "'")
     assert "alter_column('user'" not in template_args['upgrades']
     assert "alter_column('user'" not in template_args['downgrades']
     assert "alter_column('order'" in template_args['upgrades']
     assert "alter_column('order'" in template_args['downgrades']
Ejemplo n.º 34
0
    def test_render_nothing(self):
        context = MigrationContext.configure(connection=self.bind.connect(),
                                             opts={
                                                 'compare_type': True,
                                                 'compare_server_default':
                                                 True,
                                                 'target_metadata': self.m1,
                                                 'upgrade_token': "upgrades",
                                                 'downgrade_token':
                                                 "downgrades",
                                             })
        template_args = {}
        autogenerate._render_migration_diffs(context, template_args)

        eq_(
            re.sub(r"u'", "'", template_args['upgrades']),
            """### commands auto generated by Alembic - please adjust! ###
    pass
    ### end Alembic commands ###""")
        eq_(
            re.sub(r"u'", "'", template_args['downgrades']),
            """### commands auto generated by Alembic - please adjust! ###
    pass
    ### end Alembic commands ###""")
Ejemplo n.º 35
0
    def wrapper(context):
        # LIBRES this should be done by libres itself
        util = getUtility(ILibresUtility)
        dsn = util.get_dsn(utils.getSite())

        engine = create_engine(dsn, isolation_level='SERIALIZABLE')
        connection = engine.connect()
        transaction = connection.begin()
        try:
            context = MigrationContext.configure(connection)
            operations = Operations(context)

            metadata = MetaData(bind=engine)

            fn(operations, metadata)

            transaction.commit()

        except:
            transaction.rollback()
            raise

        finally:
            connection.close()
Ejemplo n.º 36
0
    def setUp(self):
        self.conn = config.db.connect()
        self.metadata = MetaData()
        t1 = Table(
            'foo', self.metadata,
            Column('id', Integer, primary_key=True),
            Column('data', String(50)),
            Column('x', Integer),
            mysql_engine='InnoDB'
        )
        t1.create(self.conn)

        self.conn.execute(
            t1.insert(),
            [
                {"id": 1, "data": "d1", "x": 5},
                {"id": 2, "data": "22", "x": 6},
                {"id": 3, "data": "8.5", "x": 7},
                {"id": 4, "data": "9.46", "x": 8},
                {"id": 5, "data": "d5", "x": 9}
            ]
        )
        context = MigrationContext.configure(self.conn)
        self.op = Operations(context)
Ejemplo n.º 37
0
    def generate(self, message: str, allow_empty: bool) -> None:
        """
        Generate upgrade scripts using alembic.
        """
        if not self.__exists():
            raise DBCreateException(
                'Tables have not been created yet, use create to create them!')

        # Verify that there are actual changes, and refuse to create empty migration scripts
        context = MigrationContext.configure(
            self.__config['database']['engine'].connect(),
            opts={'compare_type': True})
        diff = compare_metadata(context, metadata)
        if (not allow_empty) and (len(diff) == 0):
            raise DBCreateException(
                'There is nothing different between code and the DB, refusing to create migration!'
            )

        self.__alembic_cmd(
            'revision',
            '--autogenerate',
            '-m',
            message,
        )
Ejemplo n.º 38
0
    def test_compare_metadata_include_symbol(self):
        metadata = self.m2

        def include_symbol(table_name, schema_name):
            return table_name in ('extra', 'order')

        context = MigrationContext.configure(connection=self.bind.connect(),
                                             opts={
                                                 'compare_type': True,
                                                 'compare_server_default':
                                                 True,
                                                 'include_symbol':
                                                 include_symbol,
                                             })

        diffs = autogenerate.compare_metadata(context, metadata)

        eq_(diffs[0][0], 'remove_table')
        eq_(diffs[0][1].name, "extra")

        eq_(diffs[1][0], "add_column")
        eq_(diffs[1][1], None)
        eq_(diffs[1][2], "order")
        eq_(diffs[1][3], metadata.tables['order'].c.user_id)

        eq_(diffs[2][0][0], "modify_type")
        eq_(diffs[2][0][1], None)
        eq_(diffs[2][0][2], "order")
        eq_(diffs[2][0][3], "amount")
        eq_(repr(diffs[2][0][5]), "NUMERIC(precision=8, scale=2)")
        eq_(repr(diffs[2][0][6]), "Numeric(precision=10, scale=2)")

        eq_(diffs[2][1][0], 'modify_nullable')
        eq_(diffs[2][1][2], 'order')
        eq_(diffs[2][1][5], False)
        eq_(diffs[2][1][6], True)
Ejemplo n.º 39
0
def deleteChannel():
    """ Delete an instrument's data channel along with its data. Only instrument owners and system administrators can
    delete data channels

    .. :quickref: Delete data channel; Deletes an instrument data channel and its data

    :param: channelid: (Integer) unique instrument data channel identifier

    """
    Session = sessionmaker(bind=current_user.engineObj)
    session = Session()
    if('channelid' in request.form):
        channelid = request.form['channelid']
    elif('channelid' in request.args):
        channelid = request.args['channelid']
    else:
        raise InvalidUsage('No channel ID provided', status_code=500)
    try:
        result = session.query(daqbrokerDatabase.channels).filter_by(channelid=channelid).first()
        if current_user.type != 1:
            if result.chann.meta.username != current_user.username:
                raise InvalidUsage("You are not the instrument operator", status_code=400)
        session.delete(result)
        conn = current_user.engineObj.connect()
        ctx = MigrationContext.configure(conn)
        op = Operations(ctx)
        if result.channeltype == 1 or result.channeltype == 2:
            op.drop_column(result.chann.meta.Name + "_data", result.Name)
        else:
            op.drop_column(result.chann.meta.Name + "_custom", result.Name)
        conn.close()
        session.commit()
        return jsonify('done')
    except Exception as e:
        session.rollback()
        raise InvalidUsage('Error : ' + str(e), status_code=500)
Ejemplo n.º 40
0
def drop_airflow_models(connection):
    """
    Drops all airflow models.

    :param connection: SQLAlchemy Connection
    :return: None
    """
    from airflow.models.base import Base

    # Drop connection and chart - those tables have been deleted and in case you
    # run resetdb on schema with chart or users table will fail
    chart = Table('chart', Base.metadata)
    chart.drop(settings.engine, checkfirst=True)
    user = Table('user', Base.metadata)
    user.drop(settings.engine, checkfirst=True)
    users = Table('users', Base.metadata)
    users.drop(settings.engine, checkfirst=True)
    dag_stats = Table('dag_stats', Base.metadata)
    dag_stats.drop(settings.engine, checkfirst=True)
    session = Table('session', Base.metadata)
    session.drop(settings.engine, checkfirst=True)

    Base.metadata.drop_all(connection)
    # we remove the Tables here so that if resetdb is run metadata does not keep the old tables.
    Base.metadata.remove(session)
    Base.metadata.remove(dag_stats)
    Base.metadata.remove(users)
    Base.metadata.remove(user)
    Base.metadata.remove(chart)
    # alembic adds significant import time, so we import it lazily
    from alembic.migration import MigrationContext

    migration_ctx = MigrationContext.configure(connection)
    version = migration_ctx._version
    if has_table(connection, version):
        version.drop(connection)
Ejemplo n.º 41
0
def setup_db():
    """Create database and required tables."""
    if not app.config['DATABASE_URI'].startswith('sqlite'):
        try:
            with create_engine(
                    app.config['DATABASE_URI'], ).connect() as connection:
                connection.execute('CREATE DATABASE {0}'.format(
                    app.config['DATABASE_NAME']))
            print("Database created")
        except sqlalchemy.exc.OperationalError:
            pass
        except sqlalchemy.exc.ProgrammingError:
            # If database already exists
            pass

    engine = create_engine(app.config['SQLALCHEMY_DATABASE_URI'])
    conn = engine.connect()
    context = MigrationContext.configure(conn)
    current_ver = context.get_current_revision()
    if not current_ver:
        print("Automatic DB Upgrade")
        print("Press Ctrl+C when finished")
        upgrade()
        print("Upgrade completed. Press Ctrl+C and runserver again.")
Ejemplo n.º 42
0
    def test_compare_metadata_schema(self):
        metadata = self.m2

        context = MigrationContext.configure(
            connection=self.bind.connect(),
            opts={
                "include_schemas": True
            }
        )

        diffs = autogenerate.compare_metadata(context, metadata)

        eq_(
            diffs[0],
            ('add_table', metadata.tables['test_schema.item'])
        )

        eq_(diffs[1][0], 'remove_table')
        eq_(diffs[1][1].name, "extra")

        eq_(diffs[2][0], "add_column")
        eq_(diffs[2][1], "test_schema")
        eq_(diffs[2][2], "address")
        eq_(diffs[2][3], metadata.tables['test_schema.address'].c.street)

        eq_(diffs[3][0], "add_constraint")
        eq_(diffs[3][1].name, "uq_email")

        eq_(diffs[4][0], "add_column")
        eq_(diffs[4][1], "test_schema")
        eq_(diffs[4][2], "order")
        eq_(diffs[4][3], metadata.tables['test_schema.order'].c.user_id)

        eq_(diffs[5][0][0], 'modify_nullable')
        eq_(diffs[5][0][5], False)
        eq_(diffs[5][0][6], True)
Ejemplo n.º 43
0
def create_missing_database_entities(Model, engine):
    m = Model.metadata
    current_info = get_current_database_info(engine)

    print(current_info)

    conn = engine.connect()
    ctx = MigrationContext.configure(conn)
    op = Operations(ctx)

    print "metadata", m
    for table_name in m.tables:
        table = m.tables[table_name]
        if current_info.has_key(table_name):
            for col in table.columns:
                print "col", col
                if not col.name in current_info[table_name]:
                    print "    IN TABLE: %s CREATING COLUMN: %s"%(table_name, col.name)
                    op.add_column(table_name, mimic_column(col))
                    print "    ... done"
        else:
            args = [table_name] + map(mimic_column, list(table.columns))
            print "CREATING TABLE: " + repr(args)
            op.create_table(*args)
Ejemplo n.º 44
0
    def connect(engine: eng.Engine) -> None:
        """Instantiates an sqlalchemy connection shareable accross all seeders

        Args:
            engine: SqlAlchemy engine
        """

        #set engine
        Seeder._engine: engine.Engine = engine

        # set connection
        Seeder.connection = Seeder._engine.connect()

        # get operations context
        Seeder.operation = Operations(MigrationContext.configure(Seeder.connection))

        # get metadata from current connection
        Seeder.meta = MetaData(bind=Seeder.operation.get_bind())

        Seeder._base = automap_base()

        # reflect database
        Seeder._base.prepare(engine, reflect=True)
        Seeder.meta.reflect()
Ejemplo n.º 45
0
 def diff_db(self, output_sql=False):
     migrations = self.get_outstanding_migrations()
     if output_sql:
         commented_source_code = render_python_code(
             migrations.upgrade_ops,
             alembic_module_prefix='op2.',
             sqlalchemy_module_prefix="sqlalchemy.")
         uncommented_source_code = [
             i.strip() for i in commented_source_code.split('\n')
             if not i.strip().startswith('#')
         ]
         source_code = '\n'.join(['import sqlalchemy'] +
                                 uncommented_source_code)
         opts = {'as_sql': output_sql, 'target_metadata': metadata}
         with Operations.context(
                 MigrationContext.configure(connection=Session.connection(),
                                            opts=opts)) as op2:
             exec(source_code, globals(), locals())
         return uncommented_source_code
     else:
         migrations_required = migrations.upgrade_ops.as_diffs()
         if migrations_required:
             pprint.pprint(migrations_required, indent=2, width=20)
         return migrations_required
Ejemplo n.º 46
0
def suspend_fk_constraints_for_col_alter(engine,
                                         table_name,
                                         column_name,
                                         referents=[]):
    """Detect foreign key constraints, drop, and recreate.

    This is used to guard against a column ALTER that on some backends
    cannot proceed unless foreign key constraints are not present.

    e.g.::

        from oslo_db.sqlalchemy.util import (
            suspend_fk_constraints_for_col_alter
        )

        with suspend_fk_constraints_for_col_alter(
            migrate_engine, "user_table",
            referents=[
                "local_user", "nonlocal_user", "project"
            ]):
            user_table.c.domain_id.alter(nullable=False)

    :param engine: a SQLAlchemy engine (or connection)

    :param table_name: target table name.  All foreign key constraints
     that refer to the table_name / column_name will be dropped and recreated.

    :param column_name: target column name.  all foreign key constraints
     which refer to this column, either partially or fully, will be dropped
     and recreated.

    :param referents: sequence of string table names to search for foreign
     key constraints.   A future version of this function may no longer
     require this argument, however for the moment it is required.

    """
    if (not ndb.ndb_status(engine)):
        yield
    else:
        with engine.connect() as conn:
            insp = inspect(conn)
            fks = []
            for ref_table_name in referents:
                for fk in insp.get_foreign_keys(ref_table_name):
                    if not fk.get('name'):
                        raise AssertionError("foreign key hasn't a name.")
                    if fk['referred_table'] == table_name and \
                            column_name in fk['referred_columns']:
                        fk['source_table'] = ref_table_name
                        if 'options' not in fk:
                            fk['options'] = {}
                        fks.append(fk)

            ctx = MigrationContext.configure(conn)
            op = Operations(ctx)

            for fk in fks:
                op.drop_constraint(fk['name'],
                                   fk['source_table'],
                                   type_="foreignkey")
            yield
            for fk in fks:
                op.create_foreign_key(
                    fk['name'],
                    fk['source_table'],
                    fk['referred_table'],
                    fk['constrained_columns'],
                    fk['referred_columns'],
                    onupdate=fk['options'].get('onupdate'),
                    ondelete=fk['options'].get('ondelete'),
                    deferrable=fk['options'].get('deferrable'),
                    initially=fk['options'].get('initially'),
                )
Ejemplo n.º 47
0
 def op(self):
     """Get an alembic operations context."""
     ctx = MigrationContext.configure(self.executable)
     return Operations(ctx)
Create Date: 2014-06-30 20:22:30.308131

"""

# revision identifiers, used by Alembic.
revision = '38239ae5d254'
down_revision = '1931a5603650'

from alembic.migration import MigrationContext
from alembic.operations import Operations
import sqlalchemy as sa
from sqlalchemy.orm import sessionmaker
import transaction

from assembl.lib import config

admin_engine = sa.create_engine('virtuoso://*****:*****@VOSU')
conn = admin_engine.connect()
admin_context = MigrationContext.configure(conn)
op = Operations(admin_context)


def upgrade(pyramid_env):
    with admin_context.begin_transaction():
        op.add_column('WS.WS.SYS_DAV_RES', sa.Column('RES_SIZE', sa.Integer))


def downgrade(pyramid_env):
    with admin_context.begin_transaction():
        op.drop_column('WS.WS.SYS_DAV_RES', 'RES_SIZE')
Ejemplo n.º 49
0
 def op(self):
     ctx = MigrationContext.configure(self.engine)
     return Operations(ctx)
Ejemplo n.º 50
0
import os

from dotenv import load_dotenv
from sqlalchemy import create_engine, orm, MetaData, Table, Column, String
from alembic.migration import MigrationContext
from alembic.operations import Operations

load_dotenv()

PG_URL = os.environ.get('PG_URL')
PG_TABLE = os.environ.get('PG_TABLE')

engine = create_engine(PG_URL)
context = MigrationContext.configure(engine.connect())
operation = Operations(context)
operation.add_column(PG_TABLE, Column('contact_phone_normalized', String(100)))

metadata = MetaData()
metadata.reflect(bind=engine)
keys = metadata.tables[PG_TABLE].columns.keys()

print(f'All columns of the table "{PG_TABLE}": {keys}')
Ejemplo n.º 51
0
def setup(context, drop):
    """ Initialize a database for openFRED data.

    Connect to the database specified in the `[openFRED]` section of oemof's
    configuration file and set the database up to hold openFRED data.
    This means that the configured schema is created if it doesn't already
    exists. The same holds for the tables necessary to store openFRED data
    inside the schema.
    """
    section = context.obj["db"]["section"]
    schema = oemof.db.config.get(section, "schema")
    engine = oemof.db.engine(section)
    inspector = inspect(engine)
    metadata = MetaData(schema=schema, bind=engine, reflect=(not drop))
    classes = mapped_classes(metadata)

    if drop == "schema":
        with engine.connect() as connection:
            connection.execute(
                "DROP SCHEMA IF EXISTS {} CASCADE".format(schema))
    elif drop == "tables":
        classes["__Base__"].metadata.drop_all(engine)
    if schema not in inspector.get_schema_names():
        engine.execute(CreateSchema(schema))

    with engine.connect() as connection:
        connection.execute("CREATE EXTENSION IF NOT EXISTS postgis;")
        connection.execute("CREATE EXTENSION IF NOT EXISTS postgis_topology;")
    classes["__Base__"].metadata.create_all(engine)

    with db_session(engine) as session:
        timespan = classes["Timespan"]
        try:
            ts = (session.query(timespan).filter_by(start=None,
                                                    stop=None).one_or_none())
        except MRF:
            click.echo("Multiple timestamps found which have no `start` "
                       "and/or `stop` values.\nAborting.")
        ts = ts or classes["Timespan"]()
        session.add(ts)
        session.flush()

        context = MigrationContext.configure(session.connection())
        ops = Operations(context)
        ops.alter_column(
            table_name=str(classes["Series"].__table__.name),
            column_name="timespan_id",
            server_default=str(ts.id),
            schema=schema,
        )

        constraint_name = "singular_null_timestamp_constraint"
        if not [
                c for c in timespan.__table__.constraints
                if c.name == constraint_name
        ]:
            constraint = CheckConstraint(
                "(id = {}) OR ".format(ts.id) +
                "(start IS NOT NULL AND stop IS NOT NULL)",
                name=constraint_name,
            )
            timespan.__table__.append_constraint(constraint)
            session.execute(AddConstraint(constraint))

    return classes
Ejemplo n.º 52
0
def current_db_revision():
    ctx = MigrationContext.configure(ENGINE.connect())
    return ctx.get_current_revision()
Ejemplo n.º 53
0
 def __init__(self, models, engine, output, verbosity):
     self.models = models
     self.engine = engine
     self.output = output
     self.verbosity = verbosity
     self.op = Operations(MigrationContext.configure(engine.connect()))
Ejemplo n.º 54
0
def get_current_revision():
    engine = create_engine(app.config.get("SQLALCHEMY_DATABASE_URI"))
    conn = engine.connect()
    context = MigrationContext.configure(conn)
    current_rev = context.get_current_revision()
    return current_rev
    def test_database_schema_and_sqlalchemy_model_are_in_sync(self):
        all_meta_data = MetaData()
        for (table_name, table) in airflow_base.metadata.tables.items():
            all_meta_data._add_table(table_name, table.schema, table)

        # create diff between database schema and SQLAlchemy model
        mc = MigrationContext.configure(engine.connect())
        diff = compare_metadata(mc, all_meta_data)

        # known diffs to ignore
        ignores = [
            # ignore tables created by celery
            lambda t: (t[0] == 'remove_table' and
                       t[1].name == 'celery_taskmeta'),
            lambda t: (t[0] == 'remove_table' and
                       t[1].name == 'celery_tasksetmeta'),

            # ignore indices created by celery
            lambda t: (t[0] == 'remove_index' and
                       t[1].name == 'task_id'),
            lambda t: (t[0] == 'remove_index' and
                       t[1].name == 'taskset_id'),

            # Ignore all the fab tables
            lambda t: (t[0] == 'remove_table' and
                       t[1].name == 'ab_permission'),
            lambda t: (t[0] == 'remove_table' and
                       t[1].name == 'ab_register_user'),
            lambda t: (t[0] == 'remove_table' and
                       t[1].name == 'ab_role'),
            lambda t: (t[0] == 'remove_table' and
                       t[1].name == 'ab_permission_view'),
            lambda t: (t[0] == 'remove_table' and
                       t[1].name == 'ab_permission_view_role'),
            lambda t: (t[0] == 'remove_table' and
                       t[1].name == 'ab_user_role'),
            lambda t: (t[0] == 'remove_table' and
                       t[1].name == 'ab_user'),
            lambda t: (t[0] == 'remove_table' and
                       t[1].name == 'ab_view_menu'),

            # Ignore all the fab indices
            lambda t: (t[0] == 'remove_index' and
                       t[1].name == 'permission_id'),
            lambda t: (t[0] == 'remove_index' and
                       t[1].name == 'name'),
            lambda t: (t[0] == 'remove_index' and
                       t[1].name == 'user_id'),
            lambda t: (t[0] == 'remove_index' and
                       t[1].name == 'username'),
            lambda t: (t[0] == 'remove_index' and
                       t[1].name == 'field_string'),
            lambda t: (t[0] == 'remove_index' and
                       t[1].name == 'email'),
            lambda t: (t[0] == 'remove_index' and
                       t[1].name == 'permission_view_id'),

            # from test_security unit test
            lambda t: (t[0] == 'remove_table' and
                       t[1].name == 'some_model'),
        ]
        for ignore in ignores:
            diff = [d for d in diff if not ignore(d)]

        self.assertFalse(
            diff,
            'Database schema and SQLAlchemy model are not in sync: ' + str(diff)
        )
Ejemplo n.º 56
0
    def db_revision(self):
        with self.app_context():
            conn = self.db.engine.connect()

            context = MigrationContext.configure(conn)
            return context.get_current_revision()
Ejemplo n.º 57
0
        def comp(engine):
            # use compare_model_to_db, which gets everything but indexes
            with engine.connect() as conn:
                diff = compare_metadata(MigrationContext.configure(conn),
                                        self.db.model.metadata)

            if engine.dialect.name == 'mysql':
                # MySQL/MyISAM does not support foreign keys, which is expected.
                diff = [d for d in diff if d[0] != 'add_fk']

            if diff:
                return diff

            # check indexes manually
            insp = sa.inspect(engine)
            # unique, name, column_names
            diff = []
            for tbl in self.db.model.metadata.sorted_tables:
                exp = sorted([
                    dict(name=idx.name,
                         unique=idx.unique and 1 or 0,
                         column_names=sorted([c.name for c in idx.columns]))
                    for idx in tbl.indexes
                ],
                             key=lambda x: x['name'])

                # include implied indexes on postgres and mysql
                if engine.dialect.name == 'mysql':
                    implied = [
                        idx for (tname, idx) in self.db.model.implied_indexes
                        if tname == tbl.name
                    ]
                    exp = sorted(exp + implied, key=lambda k: k["name"])

                got = sorted(insp.get_indexes(tbl.name),
                             key=lambda x: x['name'])
                if exp != got:
                    got_names = {idx['name'] for idx in got}
                    exp_names = {idx['name'] for idx in exp}
                    got_info = dict((idx['name'], idx) for idx in got)
                    exp_info = dict((idx['name'], idx) for idx in exp)
                    for name in got_names - exp_names:
                        diff.append(
                            f"got unexpected index {name} on table {tbl.name}: "
                            f"{repr(got_info[name])}")
                    for name in exp_names - got_names:
                        diff.append(
                            f"missing index {name} on table {tbl.name}")
                    for name in got_names & exp_names:
                        gi = dict(name=name,
                                  unique=got_info[name]['unique'] and 1 or 0,
                                  column_names=sorted(
                                      got_info[name]['column_names']))
                        ei = exp_info[name]
                        if gi != ei:
                            diff.append(
                                f"index {name} on table {tbl.name} differs: "
                                f"got {gi}; exp {ei}")
            if diff:
                return "\n".join(diff)
            return None
Ejemplo n.º 58
0
 def setUp(self):
     self.conn = self.bind.connect()
     ctx = MigrationContext.configure(self.conn)
     self.op = Operations(ctx)
Ejemplo n.º 59
0
 def test_alter_enum(self):
     context = MigrationContext.configure(connection=self.conn)
     with context.begin_transaction(_per_migration=True):
         with context.autocommit_block():
             context.execute(text("ALTER TYPE mood ADD VALUE 'soso'"))
Ejemplo n.º 60
0
 def _open(self):
     """
     Create a connection and migration _context
     """
     self._connection = self._engine.connect()
     self._context = MigrationContext.configure(self._connection)