def drop_tables(): """Drop all the tables in the database. This is distinct from db.drop_all() in that it isn't dependent on what SQLAlchemy thinks the schema should be; it just gets all the table names and drops them. This is important since in these test we are working with older versions of the schema. """ # Notes about the implementation: # # * SQLAlchemy's inspector can do a bit more than we're using it for here; # however, after fighting with all of the nitty-gritty details of # using it to construct a metadata object that would let us just do a # call to drop_all(), it was decided that it wsan't worth the trouble. # * The below constructs a raw SQL string programmatically. In general, # this should set off your spidey-sense, and is not condoned outside of # the test suite, or without _very_ careful consideration even within it. # However, in this case the inputs are controlled, so there is no risk # of malicious input. Furthermore, per the first bullet point, this was # far simpler than convincing SQLAlchemy to do the right thing. tablenames = db.inspect(db.engine).get_table_names() for tablename in tablenames: db.session.execute('DROP TABLE IF EXISTS "' + tablename + '" CASCADE') db.session.commit()
def check_db_schema(): """Verify that the database schema is present and up-to-date. If not, an error message is printed and the program is aborted. """ tablenames = db.inspect(db.engine).get_table_names() if 'alembic_version' not in tablenames: sys.exit("ERROR: Database schema is not initialized; have you run " "hil-admin db create?") actual_heads = {row[0] for row in db.session.query(AlembicVersion).all()} if _expected_heads() != actual_heads: sys.exit("ERROR: Database schema version is incorrect; try " "running hil-admin db upgrade heads.")
def get_db_state(): """Inspect the database, and return a representation of its contents The return value is a dictionary mapping table names to dictionaries containing two keys each: - 'schema', which describes the columns for that table. - 'rows', which is a list of all rows in the table. """ result = {} inspector = db.inspect(db.engine) metadata = db.MetaData() for name in inspector.get_table_names(): schema = inspector.get_columns(name) tbl = db.Table(name, metadata) inspector.reflecttable(tbl, None) rows = db.session.query(tbl).all() # the inspector gives us the schema as a list, and the rows # as a list of tuples. the columns in each row are matched # up with the schema by position, but this may vary # (acceptably) depending on when migration scripts are run. # So, we normalize this by converting both to dictionaries # with the column names as keys. row_dicts = [] for row in rows: row_dicts.append({}) for i in range(len(row)): row_dicts[-1][schema[i]['name']] = row[i] schema = dict((col['name'], col) for col in schema) result[name] = { 'schema': schema, 'rows': sorted(row_dicts), } return result
def upgrade(): metadata = db.inspect(db.engine).get_table_names() if 'mockswitch' in metadata: op.rename_table('mockswitch', 'mock_switch')
def upgrade(): db.session.close() metadata = db.inspect(db.engine).get_table_names() if 'powerconnect55xx' in metadata: op.rename_table('powerconnect55xx', 'power_connect55xx')