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
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.")
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)
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
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
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()
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')
def check(self):
engine = create_engine(self.url)
context = MigrationContext.configure(engine)
current_rev = context.get_current_revision()
return self.head == current_rev
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')
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 ###""")
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}
)
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
}
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
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))
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)
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))
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, [])
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)
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
}
def get_database_version():
"""
Gets the current database revision (partial GUID).
:rtype: str
"""
context = MigrationContext.configure(meta.engine)
return context.get_current_revision()
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)
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()
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
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']
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']))
def versiondb(self):
"""Downgrade the database
"""
engine = create_engine(self.url)
conn = engine.connect()
context = MigrationContext.configure(conn)
return context.get_current_heads()
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()
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
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)
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
}
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.',
}
}
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))
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']
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 ###""")
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()
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)
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,
)
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)
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)
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)
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.")
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)
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)
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()
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
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'),
)
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')
def op(self):
ctx = MigrationContext.configure(self.engine)
return Operations(ctx)
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}')
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
def current_db_revision():
ctx = MigrationContext.configure(ENGINE.connect())
return ctx.get_current_revision()
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()))
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)
)
def db_revision(self):
with self.app_context():
conn = self.db.engine.connect()
context = MigrationContext.configure(conn)
return context.get_current_revision()
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
def setUp(self):
self.conn = self.bind.connect()
ctx = MigrationContext.configure(self.conn)
self.op = Operations(ctx)
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'"))
def _open(self):
"""
Create a connection and migration _context
"""
self._connection = self._engine.connect()
self._context = MigrationContext.configure(self._connection)
