def _expect_default(self, c_expected, col, seq=None):
Table('t', self.metadata, col)
self.autogen_context.metadata = self.metadata
if seq:
seq._set_metadata(self.metadata)
self.metadata.create_all(config.db)
insp = Inspector.from_engine(config.db)
uo = ops.UpgradeOps(ops=[])
_compare_tables(
set([(None, 't')]), set([]),
insp, uo, self.autogen_context)
diffs = uo.as_diffs()
tab = diffs[0][1]
eq_(_render_server_default_for_compare(
tab.c.x.server_default, tab.c.x, self.autogen_context),
c_expected)
insp = Inspector.from_engine(config.db)
uo = ops.UpgradeOps(ops=[])
m2 = MetaData()
Table('t', m2, Column('x', BigInteger()))
self.autogen_context.metadata = m2
_compare_tables(
set([(None, 't')]), set([(None, 't')]),
insp, uo, self.autogen_context)
diffs = uo.as_diffs()
server_default = diffs[0][0][4]['existing_server_default']
eq_(_render_server_default_for_compare(
server_default, tab.c.x, self.autogen_context),
c_expected)
def _test_get_foreign_keys(self, schema=None):
meta = MetaData(testing.db)
(users, addresses) = createTables(meta, schema)
meta.create_all()
insp = Inspector(meta.bind)
try:
expected_schema = schema
# users
users_fkeys = insp.get_foreign_keys(users.name,
schema=schema)
fkey1 = users_fkeys[0]
self.assert_(fkey1['name'] is not None)
eq_(fkey1['referred_schema'], expected_schema)
eq_(fkey1['referred_table'], users.name)
eq_(fkey1['referred_columns'], ['user_id', ])
eq_(fkey1['constrained_columns'], ['parent_user_id'])
#addresses
addr_fkeys = insp.get_foreign_keys(addresses.name,
schema=schema)
fkey1 = addr_fkeys[0]
self.assert_(fkey1['name'] is not None)
eq_(fkey1['referred_schema'], expected_schema)
eq_(fkey1['referred_table'], users.name)
eq_(fkey1['referred_columns'], ['user_id', ])
eq_(fkey1['constrained_columns'], ['remote_user_id'])
finally:
addresses.drop()
users.drop()
def _test_get_foreign_keys(self, schema=None):
meta = MetaData(testing.db)
(users, addresses) = createTables(meta, schema)
meta.create_all()
insp = Inspector(meta.bind)
try:
expected_schema = schema
if schema is None:
try:
expected_schema = meta.bind.dialect.get_default_schema_name(
meta.bind)
except NotImplementedError:
expected_schema = None
# users
users_fkeys = insp.get_foreign_keys(users.name,
schema=schema)
fkey1 = users_fkeys[0]
self.assert_(fkey1['name'] is not None)
self.assertEqual(fkey1['referred_schema'], expected_schema)
self.assertEqual(fkey1['referred_table'], users.name)
self.assertEqual(fkey1['referred_columns'], ['user_id', ])
self.assertEqual(fkey1['constrained_columns'], ['parent_user_id'])
#addresses
addr_fkeys = insp.get_foreign_keys(addresses.name,
schema=schema)
fkey1 = addr_fkeys[0]
self.assert_(fkey1['name'] is not None)
self.assertEqual(fkey1['referred_schema'], expected_schema)
self.assertEqual(fkey1['referred_table'], users.name)
self.assertEqual(fkey1['referred_columns'], ['user_id', ])
self.assertEqual(fkey1['constrained_columns'], ['remote_user_id'])
finally:
addresses.drop()
users.drop()
def find_pending(db):
engine = sa.create_engine(db)
inspector = Inspector(engine)
# Newer buildbot has a "buildrequest_claims" table
if "buildrequest_claims" in inspector.get_table_names():
query = sa.text("""
SELECT buildername, count(*) FROM
buildrequests WHERE
complete=0 AND
submitted_at > :yesterday AND
submitted_at < :toonew AND
(select count(*) from buildrequest_claims where brid=id) = 0
GROUP BY buildername""")
# Older buildbot doesn't
else:
query = sa.text("""
SELECT buildername, count(*) FROM
buildrequests WHERE
complete=0 AND
claimed_at=0 AND
submitted_at > :yesterday AND
submitted_at < :toonew
GROUP BY buildername""")
result = engine.execute(
query,
yesterday=time.time() - 86400,
toonew=time.time() - 60
)
retval = result.fetchall()
return retval
def _test_get_indexes(self, schema=None):
meta = MetaData(testing.db)
(users, addresses) = createTables(meta, schema)
meta.create_all()
createIndexes(meta.bind, schema)
try:
# The database may decide to create indexes for foreign keys, etc.
# so there may be more indexes than expected.
insp = Inspector(meta.bind)
indexes = insp.get_indexes('users', schema=schema)
indexes.sort()
if testing.against('oracle'):
expected_indexes = [
{'unique': False,
'column_names': ['TEST1', 'TEST2'],
'name': 'USERS_T_IDX'}]
else:
expected_indexes = [
{'unique': False,
'column_names': ['test1', 'test2'],
'name': 'users_t_idx'}]
index_names = [d['name'] for d in indexes]
for e_index in expected_indexes:
self.assertTrue(e_index['name'] in index_names)
index = indexes[index_names.index(e_index['name'])]
for key in e_index:
self.assertEqual(e_index[key], index[key])
finally:
addresses.drop()
users.drop()
def _test_get_table_names(self, schema=None, table_type='table',
order_by=None):
meta = MetaData(testing.db)
(users, addresses) = createTables(meta, schema)
meta.create_all()
createViews(meta.bind, schema)
try:
insp = Inspector(meta.bind)
if table_type == 'view':
table_names = insp.get_view_names(schema)
table_names.sort()
answer = ['email_addresses_v', 'users_v']
else:
table_names = insp.get_table_names(schema,
order_by=order_by)
table_names.sort()
if order_by == 'foreign_key':
answer = ['users', 'email_addresses']
else:
answer = ['email_addresses', 'users']
self.assertEqual(table_names, answer)
finally:
dropViews(meta.bind, schema)
addresses.drop()
users.drop()
def _test_selfref_fk(self, recreate):
bar = Table(
'bar', self.metadata,
Column('id', Integer, primary_key=True),
Column('bar_id', Integer, ForeignKey('bar.id')),
Column('data', String(50)),
mysql_engine='InnoDB'
)
bar.create(self.conn)
self.conn.execute(bar.insert(), {'id': 1, 'data': 'x', 'bar_id': None})
self.conn.execute(bar.insert(), {'id': 2, 'data': 'y', 'bar_id': 1})
with self.op.batch_alter_table("bar", recreate=recreate) as batch_op:
batch_op.alter_column(
'data', new_column_name='newdata', existing_type=String(50))
insp = Inspector.from_engine(self.conn)
insp = Inspector.from_engine(self.conn)
eq_(
[(key['referred_table'],
key['referred_columns'], key['constrained_columns'])
for key in insp.get_foreign_keys('bar')],
[('bar', ['id'], ['bar_id'])]
)
def _expect_default(self, c_expected, col, seq=None):
Table('t', self.metadata, col)
if seq:
seq._set_metadata(self.metadata)
self.metadata.create_all(config.db)
insp = Inspector.from_engine(config.db)
diffs = []
_compare_tables(
set([(None, 't')]), set([]),
[],
insp, self.metadata, diffs, self.autogen_context)
tab = diffs[0][1]
eq_(_render_server_default_for_compare(
tab.c.x.server_default, tab.c.x, self.autogen_context),
c_expected)
insp = Inspector.from_engine(config.db)
diffs = []
m2 = MetaData()
Table('t', m2, Column('x', BigInteger()))
_compare_tables(
set([(None, 't')]), set([(None, 't')]),
[],
insp, m2, diffs, self.autogen_context)
server_default = diffs[0][0][4]['existing_server_default']
eq_(_render_server_default_for_compare(
server_default, tab.c.x, self.autogen_context),
c_expected)
def test_autogen(self):
m = sa.MetaData()
sa.Table('t', m, sa.Column('x', sa.Integer))
def process_revision_directives(context, rev, generate_revisions):
existing_upgrades = generate_revisions[0].upgrade_ops
existing_downgrades = generate_revisions[0].downgrade_ops
# model1 will run the upgrades, e.g. create the table,
# model2 will run the downgrades as upgrades, e.g. drop
# the table again
generate_revisions[:] = [
ops.MigrationScript(
util.rev_id(),
existing_upgrades,
ops.DowngradeOps(),
version_path=os.path.join(
_get_staging_directory(), "model1"),
head="model1@head"
),
ops.MigrationScript(
util.rev_id(),
existing_downgrades,
ops.DowngradeOps(),
version_path=os.path.join(
_get_staging_directory(), "model2"),
head="model2@head"
)
]
with self._env_fixture(process_revision_directives, m):
command.upgrade(self.cfg, "heads")
eq_(
Inspector.from_engine(self.engine).get_table_names(),
["alembic_version"]
)
command.revision(
self.cfg, message="some message",
autogenerate=True)
command.upgrade(self.cfg, "model1@head")
eq_(
Inspector.from_engine(self.engine).get_table_names(),
["alembic_version", "t"]
)
command.upgrade(self.cfg, "model2@head")
eq_(
Inspector.from_engine(self.engine).get_table_names(),
["alembic_version"]
)
def add_municipality_domain(context):
# Rename the columns
renames = (
('elections', 'total_municipalities', 'total_entities'),
('elections', 'counted_municipalities', 'counted_entities'),
('election_results', 'municipality_id', 'entity_id'),
('ballot_results', 'municipality_id', 'entity_id'),
)
for table, old, new in renames:
if context.has_column(table, old):
context.operations.alter_column(table, old, new_column_name=new)
# Add the new domain, see http://stackoverflow.com/a/14845740
table_names = []
inspector = Inspector(context.operations_connection)
if 'elections' in inspector.get_table_names(context.schema):
table_names.append('elections')
if 'election_compounds' in inspector.get_table_names(context.schema):
table_names.append('election_compounds')
if 'votes' in inspector.get_table_names(context.schema):
table_names.append('votes')
if 'archived_results' in inspector.get_table_names(context.schema):
table_names.append('archived_results')
old_type = Enum('federation', 'canton', name='domain_of_influence')
new_type = Enum('federation', 'canton', 'municipality',
name='domain_of_influence')
tmp_type = Enum('federation', 'canton', 'municipality',
name='_domain_of_influence')
tmp_type.create(context.operations.get_bind(), checkfirst=False)
for table_name in table_names:
context.operations.execute(
(
'ALTER TABLE {} ALTER COLUMN domain TYPE _domain_of_influence '
'USING domain::text::_domain_of_influence'
).format(table_name)
)
old_type.drop(context.operations.get_bind(), checkfirst=False)
new_type.create(context.operations.get_bind(), checkfirst=False)
for table_name in table_names:
context.operations.execute(
(
'ALTER TABLE {} ALTER COLUMN domain TYPE domain_of_influence '
'USING domain::text::domain_of_influence'
).format(table_name)
)
tmp_type.drop(context.operations.get_bind(), checkfirst=False)
def test_dont_reflect_autoindex(self):
meta = self.metadata
Table('foo', meta, Column('bar', String, primary_key=True))
meta.create_all()
inspector = Inspector(testing.db)
eq_(inspector.get_indexes('foo'), [])
eq_(
inspector.get_indexes('foo', include_auto_indexes=True),
[{
'unique': 1,
'name': 'sqlite_autoindex_foo_1',
'column_names': ['bar']}])
def test_create_index_with_schema(self):
"""Test creation of index with explicit schema"""
meta = self.metadata
Table(
'foo', meta, Column('bar', String, index=True),
schema='main')
meta.create_all()
inspector = Inspector(testing.db)
eq_(
inspector.get_indexes('foo', schema='main'),
[{'unique': 0, 'name': u'ix_main_foo_bar',
'column_names': [u'bar']}])
def test_dont_reflect_autoindex(self):
meta = MetaData(testing.db)
t = Table('foo', meta, Column('bar', String, primary_key=True))
meta.create_all()
from sqlalchemy.engine.reflection import Inspector
try:
inspector = Inspector(testing.db)
eq_(inspector.get_indexes('foo'), [])
eq_(inspector.get_indexes('foo',
include_auto_indexes=True), [{'unique': 1, 'name'
: 'sqlite_autoindex_foo_1', 'column_names': ['bar']}])
finally:
meta.drop_all()
def test_get_unique_constraints(self):
meta = self.metadata
Table(
'foo', meta, Column('f', Integer),
UniqueConstraint('f', name='foo_f'))
Table(
'bar', meta, Column('b', Integer),
UniqueConstraint('b', name='bar_b'),
prefixes=['TEMPORARY'])
meta.create_all()
inspector = Inspector(testing.db)
eq_(inspector.get_unique_constraints('foo'),
[{'column_names': [u'f'], 'name': u'foo_f'}])
eq_(inspector.get_unique_constraints('bar'),
[{'column_names': [u'b'], 'name': u'bar_b'}])
def test_dont_reflect_autoindex(self):
meta = MetaData(testing.db)
t = Table("foo", meta, Column("bar", String, primary_key=True))
meta.create_all()
from sqlalchemy.engine.reflection import Inspector
try:
inspector = Inspector(testing.db)
eq_(inspector.get_indexes("foo"), [])
eq_(
inspector.get_indexes("foo", include_auto_indexes=True),
[{"unique": 1, "name": "sqlite_autoindex_foo_1", "column_names": ["bar"]}],
)
finally:
meta.drop_all()
def handle(self, options, global_options, *args):
output_dir = os.path.join(options.output_dir, options.engine)
if not os.path.exists(output_dir):
os.makedirs(output_dir)
engine = get_engine(options, global_options)
if not args:
print "Failed! You should pass one or more tables name."
sys.exit(1)
inspector = Inspector.from_engine(engine)
tables = get_tables(global_options.apps_dir, tables=args,
engine=options.engine, settings_file=global_options.settings,
local_settings_file=global_options.local_settings)
for tablename, t in tables.items():
if global_options.verbose:
print '[%s] Dumpping %s...' % (options.engine, tablename)
filename = os.path.join(output_dir, tablename+'.txt')
if options.text:
format = 'txt'
else:
format = None
dump_table(t, filename, engine, delimiter=options.delimiter,
format=format, encoding=options.encoding, inspector=inspector)
def create_table(engine, tool):
""" checks if a table exists and create one if it doesn't """
inspector = Inspector.from_engine(engine)
tables = set(inspector.get_table_names())
if tool.__tablename__ not in tables:
Base.metadata.create_all(engine)
def handle(self, options, global_options, *args):
from sqlalchemy import Table
from uliweb.orm import reflect_table_model
engine = get_engine(options, global_options)
insp = Inspector.from_engine(engine)
if not args:
tables = insp.get_table_names()
else:
tables = args
mapping = {}
print '#coding=utf8'
print 'from uliweb.orm import *'
print 'from uliweb.i18n import ugettext_lazy as _'
print 'from uliweb.utils.common import get_var'
if options.oracle:
print 'from sqlalchemy.dialects.oracle import VARCHAR2'
mapping = {'str': 'VARCHAR2'}
print '\n'
meta = engine.metadata
for name in tables:
table = Table(name, meta)
try:
insp.reflecttable(table, None)
print reflect_table_model(table, mapping, without_id=not options.auto_id)
print '\n'
except Exception as e:
import traceback
traceback.print_exc()
def delete_all_tables(db):
"""Drops all tables in the database"""
conn = db.engine.connect()
transaction = conn.begin()
inspector = Inspector.from_engine(db.engine)
metadata = MetaData()
all_schema_tables = get_all_tables(db)
tables = []
all_fkeys = []
for schema, schema_tables in all_schema_tables.iteritems():
for table_name in schema_tables:
fkeys = [ForeignKeyConstraint((), (), name=fk['name'])
for fk in inspector.get_foreign_keys(table_name, schema=schema)
if fk['name']]
tables.append(Table(table_name, metadata, *fkeys, schema=schema))
all_fkeys.extend(fkeys)
for fkey in all_fkeys:
conn.execute(DropConstraint(fkey))
for table in tables:
conn.execute(DropTable(table))
for schema in all_schema_tables:
if schema != 'public':
conn.execute(DropSchema(schema))
transaction.commit()
def setup():
if harvest_source_table is None:
define_harvester_tables()
log.debug('Harvest tables defined in memory')
if model.package_table.exists():
if not harvest_source_table.exists():
# Create each table individually rather than
# using metadata.create_all()
harvest_source_table.create()
harvest_job_table.create()
harvest_object_table.create()
harvest_gather_error_table.create()
harvest_object_error_table.create()
log.debug('Harvest tables created')
else:
from ckan.model.meta import engine
log.debug('Harvest tables already exist')
# Check if existing tables need to be updated
inspector = Inspector.from_engine(engine)
columns = inspector.get_columns('harvest_source')
if not 'title' in [column['name'] for column in columns]:
log.debug('Harvest tables need to be updated')
migrate_v2()
else:
log.debug('Harvest table creation deferred')
def delete_all_tables(db):
"""Drops all tables in the database"""
conn = db.engine.connect()
transaction = conn.begin()
inspector = Inspector.from_engine(db.engine)
metadata = MetaData()
all_schema_tables = get_all_tables(db)
tables = []
all_fkeys = []
for schema, schema_tables in all_schema_tables.iteritems():
for table_name in schema_tables:
fkeys = [ForeignKeyConstraint((), (), name=fk['name'])
for fk in inspector.get_foreign_keys(table_name, schema=schema)
if fk['name']]
tables.append(Table(table_name, metadata, *fkeys, schema=schema))
all_fkeys.extend(fkeys)
for fkey in all_fkeys:
conn.execute(DropConstraint(fkey))
for table in tables:
conn.execute(DropTable(table))
for schema in all_schema_tables:
if schema != 'public':
row = conn.execute("""
SELECT 'DROP FUNCTION ' || ns.nspname || '.' || proname || '(' || oidvectortypes(proargtypes) || ')'
FROM pg_proc INNER JOIN pg_namespace ns ON (pg_proc.pronamespace = ns.oid)
WHERE ns.nspname = '{}' order by proname;
""".format(schema))
for stmt, in row:
conn.execute(stmt)
conn.execute(DropSchema(schema))
transaction.commit()
def _compare_default(self, t1, t2, col, rendered):
t1.create(self.bind, checkfirst=True)
insp = Inspector.from_engine(self.bind)
cols = insp.get_columns(t1.name)
ctx = self.autogen_context.migration_context
return ctx.impl.compare_server_default(None, col, rendered, cols[0]["default"])
def handle(self, options, global_options, *args):
from sqlalchemy import Table
from uliweb.orm import reflect_model
engine = get_engine(options, global_options)
insp = Inspector.from_engine(engine)
if not args:
tables = insp.get_table_names()
else:
tables = args
print '#coding=utf8'
print 'from uliweb.orm import *'
print 'from uliweb.i18n import ugettext_lazy as _'
print 'from uliweb.utils.common import get_var'
print '\n'
meta = engine.metadata
for name in tables:
table = Table(name, meta)
try:
insp.reflecttable(table, None)
print reflect_model(table)
print '\n'
except Exception as e:
import traceback
traceback.print_exc()
def __initializeConnection( self, dbPath ):
"""
Collects from the CS all the info needed to connect to the DB.
This should be in a base class eventually
"""
result = getDBParameters( dbPath )
if not result[ 'OK' ]:
raise Exception( 'Cannot get database parameters: %s' % result['Message'] )
dbParameters = result[ 'Value' ]
self.log.debug("db parameters: %s" % dbParameters)
self.host = dbParameters[ 'Host' ]
self.port = dbParameters[ 'Port' ]
self.user = dbParameters[ 'User' ]
self.password = dbParameters[ 'Password' ]
self.dbName = dbParameters[ 'DBName' ]
self.engine = create_engine( 'mysql://%s:%s@%s:%s/%s' % ( self.user,
self.password,
self.host,
self.port,
self.dbName ),
pool_recycle = 3600,
echo_pool = True,
echo = self.log.getLevel() == 'DEBUG')
self.sessionMaker_o = sessionmaker( bind = self.engine )
self.inspector = Inspector.from_engine( self.engine )
def upgrade():
c = get_context()
if isinstance(c.connection.engine.dialect, MySQLDialect):
insp = Inspector.from_engine(c.connection.engine)
for t in ['groups_permissions', 'groups_resources_permissions',
'users_groups', 'resources']:
for constraint in insp.get_foreign_keys(t):
if constraint['referred_columns'] == ['group_name']:
op.drop_constraint(constraint['name'], t,
type='foreignkey')
op.drop_column('groups', 'id')
op.alter_column('groups', 'group_name',
type_=sa.String(128),
existing_type=sa.String(50),
)
op.create_primary_key('groups_pkey', 'groups', cols=['group_name'])
if isinstance(c.connection.engine.dialect, MySQLDialect):
op.create_foreign_key(None, 'groups_permissions', 'groups',
remote_cols=['group_name'],
local_cols=['group_name'], onupdate='CASCADE',
ondelete='CASCADE')
op.create_foreign_key(None, 'groups_resources_permissions', 'groups',
remote_cols=['group_name'],
local_cols=['group_name'], onupdate='CASCADE',
ondelete='CASCADE')
op.create_foreign_key(None, 'users_groups', 'groups',
remote_cols=['group_name'],
local_cols=['group_name'], onupdate='CASCADE',
ondelete='CASCADE')
op.create_foreign_key(None, 'resources', 'groups',
remote_cols=['group_name'],
local_cols=['owner_group_name'], onupdate='CASCADE',
ondelete='SET NULL')
def _produce_net_changes(connection, metadata, diffs, autogen_context,
object_filters=(),
include_schemas=False):
inspector = Inspector.from_engine(connection)
# TODO: not hardcode alembic_version here ?
conn_table_names = set()
if include_schemas:
schemas = set(inspector.get_schema_names())
# replace default schema name with None
schemas.discard("information_schema")
# replace the "default" schema with None
schemas.add(None)
schemas.discard(connection.dialect.default_schema_name)
else:
schemas = [None]
for s in schemas:
tables = set(inspector.get_table_names(schema=s)).\
difference(['alembic_version'])
conn_table_names.update(zip([s] * len(tables), tables))
metadata_table_names = OrderedSet([(table.schema, table.name)
for table in metadata.sorted_tables])
_compare_tables(conn_table_names, metadata_table_names,
object_filters,
inspector, metadata, diffs, autogen_context)
def init_db(self):
engine = self.session.get_bind(mapper=None, clause=None)
inspector = Inspector.from_engine(engine)
if 'ab_user' not in inspector.get_table_names():
print "Security DB not found Creating..."
Base.metadata.create_all(engine)
print "Security DB Created"
self.migrate_db()
if self.session.query(Role).filter_by(name = self.auth_role_admin).first() is None:
role = Role()
role.name = self.auth_role_admin
self.session.add(role)
self.session.commit()
print "Inserted Role for public access", self.auth_role_admin
if not self.session.query(Role).filter_by(name = self.auth_role_public).first():
role = Role()
role.name = self.auth_role_public
self.session.add(role)
self.session.commit()
print "Inserted Role for public access", self.auth_role_public
if not self.session.query(User).all():
user = User()
user.first_name = 'Admin'
user.last_name = 'User'
user.username = '******'
user.password = '******'
user.active = True
user.role = self.session.query(Role).filter_by(name = self.auth_role_admin).first()
self.session.add(user)
self.session.commit()
print "Inserted initial Admin user"
print "Login using Admin/general"
def _test_get_primary_keys(self, schema=None):
meta = MetaData(testing.db)
(users, addresses) = createTables(meta, schema)
meta.create_all()
insp = Inspector(meta.bind)
try:
users_pkeys = insp.get_primary_keys(users.name,
schema=schema)
self.assertEqual(users_pkeys, ['user_id'])
addr_pkeys = insp.get_primary_keys(addresses.name,
schema=schema)
self.assertEqual(addr_pkeys, ['address_id'])
finally:
addresses.drop()
users.drop()
def handle(self, options, global_options, *args):
engine = get_engine(options, global_options)
if len(args) != 2:
print self.print_help(self.prog_name, 'dumptablefile')
sys.exit(1)
inspector = Inspector.from_engine(engine)
name = args[0]
tables = get_tables(global_options.apps_dir, tables=[name],
engine_name=options.engine, settings_file=global_options.settings,
local_settings_file=global_options.local_settings)
t = tables[name]
if global_options.verbose:
print '[%s] Dumpping %s...' % (options.engine, show_table(name, t, 0, 1)),
if options.text:
format = 'txt'
else:
format = None
t = dump_table(t, args[1], engine, delimiter=options.delimiter,
format=format, encoding=options.encoding, inspector=inspector,
engine_name=engine.engine_name)
if global_options.verbose:
print t
def __init__(self, sqla_conn, args, schema=None):
self.args = args
self.sqla_conn = sqla_conn
self.schema = schema
self.engine = sa.create_engine(sqla_conn)
self.meta = sa.MetaData(bind=self.engine) # excised schema=schema to prevent errors
self.meta.reflect(schema=self.schema)
self.inspector = Inspector(bind=self.engine)
self.conn = self.engine.connect()
self.tables = OrderedDict()
for tbl in self.meta.sorted_tables:
if any(fnmatch.fnmatch(tbl.name, each) for each in args.exclude_tables):
continue
tbl.db = self
# TODO: Replace all these monkeypatches with an instance assigment
tbl.find_n_rows = types.MethodType(_find_n_rows, tbl)
tbl.random_row_func = types.MethodType(_random_row_func, tbl)
tbl.fks = self.inspector.get_foreign_keys(tbl.name, schema=tbl.schema)
tbl.pk = self.inspector.get_primary_keys(tbl.name, schema=tbl.schema)
if not tbl.pk:
tbl.pk = [d['name'] for d in self.inspector.get_columns(tbl.name)]
tbl.filtered_by = types.MethodType(_filtered_by, tbl)
tbl.by_pk = types.MethodType(_by_pk, tbl)
tbl.pk_val = types.MethodType(_pk_val, tbl)
tbl.child_fks = []
estimate_rows = not(any(fnmatch.fnmatch(tbl.name, each)
for each in self.args.full_tables))
tbl.find_n_rows(estimate=estimate_rows)
self.tables[(tbl.schema, tbl.name)] = tbl
for ((tbl_schema, tbl_name), tbl) in self.tables.items():
constraints = args.config.get('constraints', {}).get(tbl_name, [])
for fk in (tbl.fks + constraints):
fk['constrained_schema'] = tbl_schema
fk['constrained_table'] = tbl_name # TODO: check against constrained_table
self.tables[(fk['referred_schema'], fk['referred_table'])].child_fks.append(fk)
def enumerate_tables(self, **tokens):
engine, sessions = self._acquire_engine(tokens)
inspector = Inspector.from_engine(engine)
return inspector.get_table_names()
def _process_view(
self,
dataset_name: str,
inspector: Inspector,
schema: str,
view: str,
sql_config: SQLAlchemyConfig,
) -> Iterable[Union[SqlWorkUnit, MetadataWorkUnit]]:
try:
columns = inspector.get_columns(view, schema)
except KeyError:
# For certain types of views, we are unable to fetch the list of columns.
self.report.report_warning(dataset_name,
"unable to get schema for this view")
schema_metadata = None
else:
schema_fields = self.get_schema_fields(dataset_name, columns)
schema_metadata = get_schema_metadata(
self.report,
dataset_name,
self.platform,
columns,
canonical_schema=schema_fields,
)
try:
# SQLALchemy stubs are incomplete and missing this method.
# PR: https://github.com/dropbox/sqlalchemy-stubs/pull/223.
view_info: dict = inspector.get_table_comment(
view, schema) # type: ignore
except NotImplementedError:
description: Optional[str] = None
properties: Dict[str, str] = {}
else:
description = view_info["text"]
# The "properties" field is a non-standard addition to SQLAlchemy's interface.
properties = view_info.get("properties", {})
try:
view_definition = inspector.get_view_definition(view, schema)
if view_definition is None:
view_definition = ""
else:
# Some dialects return a TextClause instead of a raw string,
# so we need to convert them to a string.
view_definition = str(view_definition)
except NotImplementedError:
view_definition = ""
properties["view_definition"] = view_definition
properties["is_view"] = "True"
dataset_urn = make_dataset_urn_with_platform_instance(
self.platform,
dataset_name,
self.config.platform_instance,
self.config.env,
)
dataset_snapshot = DatasetSnapshot(
urn=dataset_urn,
aspects=[StatusClass(removed=False)],
)
db_name = self.get_db_name(inspector)
yield from self.add_table_to_schema_container(dataset_urn, db_name,
schema)
if self.is_stateful_ingestion_configured():
cur_checkpoint = self.get_current_checkpoint(
self.get_default_ingestion_job_id())
if cur_checkpoint is not None:
checkpoint_state = cast(BaseSQLAlchemyCheckpointState,
cur_checkpoint.state)
checkpoint_state.add_view_urn(dataset_urn)
dataset_properties = DatasetPropertiesClass(
name=view,
description=description,
customProperties=properties,
)
dataset_snapshot.aspects.append(dataset_properties)
if schema_metadata:
dataset_snapshot.aspects.append(schema_metadata)
mce = MetadataChangeEvent(proposedSnapshot=dataset_snapshot)
wu = SqlWorkUnit(id=dataset_name, mce=mce)
self.report.report_workunit(wu)
yield wu
dpi_aspect = self.get_dataplatform_instance_aspect(
dataset_urn=dataset_urn)
if dpi_aspect:
yield dpi_aspect
subtypes_aspect = MetadataWorkUnit(
id=f"{view}-subtypes",
mcp=MetadataChangeProposalWrapper(
entityType="dataset",
changeType=ChangeTypeClass.UPSERT,
entityUrn=dataset_urn,
aspectName="subTypes",
aspect=SubTypesClass(typeNames=["view"]),
),
)
yield subtypes_aspect
if "view_definition" in properties:
view_definition_string = properties["view_definition"]
view_properties_aspect = ViewPropertiesClass(
materialized=False,
viewLanguage="SQL",
viewLogic=view_definition_string)
yield MetadataWorkUnit(
id=f"{view}-viewProperties",
mcp=MetadataChangeProposalWrapper(
entityType="dataset",
changeType=ChangeTypeClass.UPSERT,
entityUrn=dataset_urn,
aspectName="viewProperties",
aspect=view_properties_aspect,
),
)
yield from self._get_domain_wu(
dataset_name=dataset_name,
entity_urn=dataset_urn,
entity_type="dataset",
sql_config=sql_config,
)
def get_tables(apps_dir, apps=None, engine_name=None, tables=None,
settings_file='settings.ini', local_settings_file='local_settings.ini',
all=False):
from uliweb.core.SimpleFrame import get_apps, get_app_dir
from uliweb import orm
engine = orm.engine_manager[engine_name]
e = engine.options['connection_string']
dispatch.get(None, 'load_models')
old_models = orm.__models__.keys()
tables_map = {}
tables_mapping_only = {}
try:
for tablename, m in engine.models.items():
try:
x = orm.get_model(tablename, engine_name)
#convert dynamic model to mapping_only model
if hasattr(x, '__dynamic__') and getattr(x, '__dynamic__'):
x.__mapping_only__ = True
if hasattr(x, '__mapping_only__') and getattr(x, '__mapping_only__'):
tables_mapping_only[x.tablename] = True
except:
print("Error on Model [%s]" % tablename)
raise
tables_map[x.tablename] = tablename
except:
print("Problems to models like:", list(set(old_models) ^ set(orm.__models__.keys())))
raise
all_meta = MetaData()
meta = engine.metadata
insp = Inspector.from_engine(engine.engine)
def get_table(tablename):
table = Table(tablename, all_meta)
try:
insp.reflecttable(table, None)
except NoSuchTableError:
return
return table
all_tables = insp.get_table_names() + list(meta.tables.keys())
if apps:
t = {}
for tablename in all_tables:
if tablename in meta.tables:
table = meta.tables[tablename]
if hasattr(table, '__appname__') and table.__appname__ in apps:
t[tables_map.get(tablename, tablename)] = table
table.__mapping_only__ = tables_mapping_only.get(tablename, False)
elif tables:
t = {}
for tablename in tables:
if tablename in meta.tables:
table = meta.tables[tablename]
else:
try:
table = get_table(tablename)
if not table:
print("Table [%s] can't be found, it'll be skipped." % tablename)
continue
table.__appname__ = 'UNKNOWN'
except Exception as e:
import traceback
traceback.print_exc()
continue
t[tables_map.get(tablename, tablename)] = table
table.__mapping_only__ = tables_mapping_only.get(tablename, False)
else:
t = {}
if not all:
all_meta = engine.metadata
all_tables = meta.tables.keys()
for tablename in all_tables:
if tablename in meta:
table = meta.tables[tablename]
else:
try:
table = get_table(tablename)
table.__appname__ = 'UNKNOWN'
except Exception as e:
import traceback
traceback.print_exc()
continue
t[tables_map.get(tablename, tablename)] = table
table.__mapping_only__ = tables_mapping_only.get(tablename, False)
return t
def inspector(self):
return Inspector.from_engine(self.connection)
def verify_thd(conn):
metadata = sa.MetaData()
metadata.bind = conn
# Verify database contents.
# 'workers' table contents.
workers = sautils.Table('workers', metadata, autoload=True)
c = workers.c
q = sa.select(
[c.id, c.name, c.info]
).order_by(c.id)
self.assertEqual(
q.execute().fetchall(), [
(30, u'worker-1', u'{}'),
(31, u'worker-2', u'{"a": 1}'),
])
# 'builds' table contents.
builds = sautils.Table('builds', metadata, autoload=True)
c = builds.c
q = sa.select(
[c.id, c.number, c.builderid, c.buildrequestid, c.workerid,
c.masterid, c.started_at, c.complete_at, c.state_string,
c.results]
).order_by(c.id)
self.assertEqual(
q.execute().fetchall(), [
(40, 1, None, 20, 30, 10, 1000, None, u'state', None),
(41, 2, 50, 21, None, 11, 2000, 3000, u'state 2', 9),
])
# 'configured_workers' table contents.
configured_workers = sautils.Table(
'configured_workers', metadata, autoload=True)
c = configured_workers.c
q = sa.select(
[c.id, c.buildermasterid, c.workerid]
).order_by(c.id)
self.assertEqual(
q.execute().fetchall(), [
(60, 70, 30),
(61, 71, 31),
])
# 'connected_workers' table contents.
connected_workers = sautils.Table(
'connected_workers', metadata, autoload=True)
c = connected_workers.c
q = sa.select(
[c.id, c.masterid, c.workerid]
).order_by(c.id)
self.assertEqual(
q.execute().fetchall(), [
(80, 10, 30),
(81, 11, 31),
])
# Verify that there is no "slave"-named items in schema.
inspector = Inspector(conn)
def check_name(name, table_name, item_type):
if not name:
return
self.assertTrue(
u"slave" not in name.lower(),
msg=u"'slave'-named {type} in table '{table}': "
u"'{name}'".format(
type=item_type, table=table_name,
name=name))
# Check every table.
for table_name in inspector.get_table_names():
# Check table name.
check_name(table_name, table_name, u"table name")
# Check column names.
for column_info in inspector.get_columns(table_name):
check_name(column_info['name'], table_name, u"column")
# Check foreign key names.
for fk_info in inspector.get_foreign_keys(table_name):
check_name(fk_info['name'], table_name, u"foreign key")
# Check indexes names.
for index_info in inspector.get_indexes(table_name):
check_name(index_info['name'], table_name, u"index")
# Check primary keys constraints names.
pk_info = inspector.get_pk_constraint(table_name)
check_name(pk_info.get('name'), table_name, u"primary key")
# Test that no "slave"-named items present in schema
for name in inspector.get_schema_names():
self.assertTrue(u"slave" not in name.lower())
def get_apps():
inspector = Inspector.from_engine(engine)
schemas = set(inspector.get_schema_names())
schemas.discard('information_schema')
return schemas
def downgrade():
conn = op.get_bind()
inspector = Inspector.from_engine(conn)
tables = inspector.get_table_names()
if 'shops_has_storages' in tables:
op.drop_table('shops_has_storages')
from sqlalchemy import create_engine
from resources import dbs
dbd = 'mysql'
tdb = dbs[dbd]
engine=create_engine('%s://%s:%s@%s:%s/%s' % \
(dbd
, tdb['user'], tdb['pw']
,tdb['host'],tdb['port']
,tdb['db'] ))
#conn=engine.connect()
from sqlalchemy.engine.reflection import Inspector
insp = Inspector(engine) #(conn)
from sqlalchemy import MetaData
md = MetaData(engine)
md.reflect()
def get_table_info(tbl_nm):
"""returns a dict with keys 'attrib' for each col"""
for ti in insp.get_columns(tbl_nm):
yield ti
from sqlalchemy.sql import and_, select
from sqlalchemy import Table
flights = md.tables['flights']
def upgrade():
conn = op.get_bind()
inspector = Inspector.from_engine(conn)
tables = inspector.get_table_names()
if 'connection' not in tables:
op.create_table(
'connection', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('conn_id', sa.String(length=250), nullable=True),
sa.Column('conn_type', sa.String(length=500), nullable=True),
sa.Column('host', sa.String(length=500), nullable=True),
sa.Column('schema', sa.String(length=500), nullable=True),
sa.Column('login', sa.String(length=500), nullable=True),
sa.Column('password', sa.String(length=500), nullable=True),
sa.Column('port', sa.Integer(), nullable=True),
sa.Column('extra', sa.String(length=5000), nullable=True),
sa.PrimaryKeyConstraint('id'))
if 'dag' not in tables:
op.create_table(
'dag', sa.Column('dag_id', sa.String(length=250), nullable=False),
sa.Column('is_paused', sa.Boolean(), nullable=True),
sa.Column('is_subdag', sa.Boolean(), nullable=True),
sa.Column('is_active', sa.Boolean(), nullable=True),
sa.Column('last_scheduler_run', sa.DateTime(), nullable=True),
sa.Column('last_pickled', sa.DateTime(), nullable=True),
sa.Column('last_expired', sa.DateTime(), nullable=True),
sa.Column('scheduler_lock', sa.Boolean(), nullable=True),
sa.Column('pickle_id', sa.Integer(), nullable=True),
sa.Column('fileloc', sa.String(length=2000), nullable=True),
sa.Column('owners', sa.String(length=2000), nullable=True),
sa.PrimaryKeyConstraint('dag_id'))
if 'dag_pickle' not in tables:
op.create_table(
'dag_pickle', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('pickle', sa.PickleType(), nullable=True),
sa.Column('created_dttm', sa.DateTime(), nullable=True),
sa.Column('pickle_hash', sa.BigInteger(), nullable=True),
sa.PrimaryKeyConstraint('id'))
if 'import_error' not in tables:
op.create_table(
'import_error', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('timestamp', sa.DateTime(), nullable=True),
sa.Column('filename', sa.String(length=1024), nullable=True),
sa.Column('stacktrace', sa.Text(), nullable=True),
sa.PrimaryKeyConstraint('id'))
if 'job' not in tables:
op.create_table(
'job', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('dag_id', sa.String(length=250), nullable=True),
sa.Column('state', sa.String(length=20), nullable=True),
sa.Column('job_type', sa.String(length=30), nullable=True),
sa.Column('start_date', sa.DateTime(), nullable=True),
sa.Column('end_date', sa.DateTime(), nullable=True),
sa.Column('latest_heartbeat', sa.DateTime(), nullable=True),
sa.Column('executor_class', sa.String(length=500), nullable=True),
sa.Column('hostname', sa.String(length=500), nullable=True),
sa.Column('unixname', sa.String(length=1000), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_index('job_type_heart',
'job', ['job_type', 'latest_heartbeat'],
unique=False)
if 'known_event_type' not in tables:
op.create_table(
'known_event_type', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('know_event_type', sa.String(length=200), nullable=True),
sa.PrimaryKeyConstraint('id'))
if 'log' not in tables:
op.create_table(
'log', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('dttm', sa.DateTime(), nullable=True),
sa.Column('dag_id', sa.String(length=250), nullable=True),
sa.Column('task_id', sa.String(length=250), nullable=True),
sa.Column('event', sa.String(length=30), nullable=True),
sa.Column('execution_date', sa.DateTime(), nullable=True),
sa.Column('owner', sa.String(length=500), nullable=True),
sa.PrimaryKeyConstraint('id'))
if 'sla_miss' not in tables:
op.create_table(
'sla_miss',
sa.Column('task_id', sa.String(length=250), nullable=False),
sa.Column('dag_id', sa.String(length=250), nullable=False),
sa.Column('execution_date', sa.DateTime(), nullable=False),
sa.Column('email_sent', sa.Boolean(), nullable=True),
sa.Column('timestamp', sa.DateTime(), nullable=True),
sa.Column('description', sa.Text(), nullable=True),
sa.PrimaryKeyConstraint('task_id', 'dag_id', 'execution_date'))
if 'slot_pool' not in tables:
op.create_table('slot_pool',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('pool', sa.String(length=50), nullable=True),
sa.Column('slots', sa.Integer(), nullable=True),
sa.Column('description', sa.Text(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('pool'))
if 'task_instance' not in tables:
op.create_table(
'task_instance',
sa.Column('task_id', sa.String(length=250), nullable=False),
sa.Column('dag_id', sa.String(length=250), nullable=False),
sa.Column('execution_date', sa.DateTime(), nullable=False),
sa.Column('start_date', sa.DateTime(), nullable=True),
sa.Column('end_date', sa.DateTime(), nullable=True),
sa.Column('duration', sa.Integer(), nullable=True),
sa.Column('state', sa.String(length=20), nullable=True),
sa.Column('try_number', sa.Integer(), nullable=True),
sa.Column('hostname', sa.String(length=1000), nullable=True),
sa.Column('unixname', sa.String(length=1000), nullable=True),
sa.Column('job_id', sa.Integer(), nullable=True),
sa.Column('pool', sa.String(length=50), nullable=True),
sa.Column('queue', sa.String(length=50), nullable=True),
sa.Column('priority_weight', sa.Integer(), nullable=True),
sa.PrimaryKeyConstraint('task_id', 'dag_id', 'execution_date'))
op.create_index('ti_dag_state',
'task_instance', ['dag_id', 'state'],
unique=False)
op.create_index('ti_pool',
'task_instance', ['pool', 'state', 'priority_weight'],
unique=False)
op.create_index('ti_state_lkp',
'task_instance',
['dag_id', 'task_id', 'execution_date', 'state'],
unique=False)
if 'user' not in tables:
op.create_table(
'user', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('username', sa.String(length=250), nullable=True),
sa.Column('email', sa.String(length=500), nullable=True),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('username'))
if 'variable' not in tables:
op.create_table('variable',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('key', sa.String(length=250), nullable=True),
sa.Column('val', sa.Text(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('key'))
if 'chart' not in tables:
op.create_table(
'chart', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('label', sa.String(length=200), nullable=True),
sa.Column('conn_id', sa.String(length=250), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=True),
sa.Column('chart_type', sa.String(length=100), nullable=True),
sa.Column('sql_layout', sa.String(length=50), nullable=True),
sa.Column('sql', sa.Text(), nullable=True),
sa.Column('y_log_scale', sa.Boolean(), nullable=True),
sa.Column('show_datatable', sa.Boolean(), nullable=True),
sa.Column('show_sql', sa.Boolean(), nullable=True),
sa.Column('height', sa.Integer(), nullable=True),
sa.Column('default_params', sa.String(length=5000), nullable=True),
sa.Column('x_is_date', sa.Boolean(), nullable=True),
sa.Column('iteration_no', sa.Integer(), nullable=True),
sa.Column('last_modified', sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
), sa.PrimaryKeyConstraint('id'))
if 'known_event' not in tables:
op.create_table(
'known_event', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('label', sa.String(length=200), nullable=True),
sa.Column('start_date', sa.DateTime(), nullable=True),
sa.Column('end_date', sa.DateTime(), nullable=True),
sa.Column('user_id', sa.Integer(), nullable=True),
sa.Column('known_event_type_id', sa.Integer(), nullable=True),
sa.Column('description', sa.Text(), nullable=True),
sa.ForeignKeyConstraint(
['known_event_type_id'],
['known_event_type.id'],
), sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
), sa.PrimaryKeyConstraint('id'))
if 'xcom' not in tables:
op.create_table(
'xcom', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('key', sa.String(length=512), nullable=True),
sa.Column('value', sa.PickleType(), nullable=True),
sa.Column('timestamp',
sa.DateTime(),
default=func.now(),
nullable=False),
sa.Column('execution_date', sa.DateTime(), nullable=False),
sa.Column('task_id', sa.String(length=250), nullable=False),
sa.Column('dag_id', sa.String(length=250), nullable=False),
sa.PrimaryKeyConstraint('id'))
def upgrade():
c = get_context()
insp = Inspector.from_engine(c.connection.engine)
# existing migration
# pre naming convention keys
groups_permissions_pkey = "groups_permissions_pkey"
groups_pkey = "groups_pkey"
groups_resources_permissions_pkey = "groups_resources_permissions_pkey"
users_groups_pkey = "users_groups_pkey"
users_permissions_pkey = "users_permissions_pkey"
users_resources_permissions_pkey = "users_resources_permissions_pkey"
# inspected keys
groups_permissions_pkey = insp.get_pk_constraint(
"groups_permissions")["name"]
groups_pkey = insp.get_pk_constraint("groups")["name"]
groups_resources_permissions_pkey = insp.get_pk_constraint(
"groups_resources_permissions")["name"]
users_groups_pkey = insp.get_pk_constraint("users_groups")["name"]
users_permissions_pkey = insp.get_pk_constraint(
"users_permissions")["name"]
users_resources_permissions_pkey = insp.get_pk_constraint(
"users_resources_permissions")["name"]
op.drop_constraint("groups_pkey", "groups", type_="primary")
if isinstance(c.connection.engine.dialect, MySQLDialect):
op.add_column(
"groups",
sa.Column("id", sa.Integer, primary_key=True, autoincrement=False))
op.create_primary_key(groups_pkey, "groups", cols=["id"])
op.alter_column(
"groups",
"id",
type_=sa.Integer,
existing_type=sa.Integer,
autoincrement=True,
existing_autoincrement=False,
nullable=False,
)
else:
op.add_column(
"groups",
sa.Column("id", sa.Integer, primary_key=True, autoincrement=True))
op.create_primary_key(groups_pkey, "groups", cols=["id"])
if isinstance(c.connection.engine.dialect, MySQLDialect):
for t in [
"groups_permissions", "groups_resources_permissions",
"users_groups"
]:
for constraint in insp.get_foreign_keys(t):
if constraint["referred_columns"] == ["group_name"]:
op.drop_constraint(constraint["name"],
t,
type_="foreignkey")
for t in [
"users_resources_permissions", "users_permissions",
"users_groups"
]:
for constraint in insp.get_foreign_keys(t):
if constraint["referred_columns"] == ["user_name"]:
op.drop_constraint(constraint["name"],
t,
type_="foreignkey")
for constraint in insp.get_foreign_keys("resources"):
if constraint["referred_columns"] in [["user_name"],
["group_name"]]:
op.drop_constraint(constraint["name"],
"resources",
type_="foreignkey")
op.add_column(
"resources",
sa.Column(
"owner_user_id",
sa.Integer(),
sa.ForeignKey("users.id", onupdate="CASCADE", ondelete="SET NULL"),
),
)
op.add_column(
"resources",
sa.Column(
"owner_group_id",
sa.Integer(),
sa.ForeignKey("groups.id", onupdate="CASCADE",
ondelete="SET NULL"),
),
)
# update the data
op.execute("""update resources set owner_user_id =
(select id from users where users.user_name=owner_user_name)"""
) # noqa
op.execute("""update resources set owner_group_id =
(select id from users where users.user_name=owner_group_name)"""
) # noqa
# mysql is stupid as usual so we cant create FKEY and add PKEY later,
# need to set PKEY first and then set FKEY
if isinstance(c.connection.engine.dialect, MySQLDialect):
op.add_column("groups_permissions", sa.Column("group_id",
sa.Integer()))
else:
op.add_column(
"groups_permissions",
sa.Column(
"group_id",
sa.Integer(),
sa.ForeignKey(
"groups.id",
onupdate="CASCADE",
ondelete="CASCADE" # noqa # noqa
),
),
) # noqa
op.execute("""update groups_permissions set group_id =
(select id from groups where groups.group_name=groups_permissions.group_name)"""
) # noqa
op.drop_constraint(groups_permissions_pkey,
"groups_permissions",
type_="primary")
op.create_primary_key(groups_permissions_pkey,
"groups_permissions",
cols=["group_id", "perm_name"])
if isinstance(c.connection.engine.dialect, MySQLDialect):
op.create_foreign_key(
None,
"groups_permissions",
"groups",
remote_cols=["id"],
local_cols=["group_id"],
onupdate="CASCADE",
ondelete="CASCADE",
)
if isinstance(c.connection.engine.dialect, MySQLDialect):
op.add_column("groups_resources_permissions",
sa.Column("group_id", sa.Integer()))
else:
op.add_column(
"groups_resources_permissions",
sa.Column(
"group_id",
sa.Integer(),
sa.ForeignKey("groups.id",
onupdate="CASCADE",
ondelete="CASCADE"),
),
)
op.execute("""update groups_resources_permissions set group_id =
(select id from groups where groups.group_name=groups_resources_permissions.group_name)"""
) # noqa
op.drop_constraint(
groups_resources_permissions_pkey,
"groups_resources_permissions",
type_="primary",
)
op.create_primary_key(
groups_resources_permissions_pkey,
"groups_resources_permissions",
cols=["group_id", "resource_id", "perm_name"],
)
if isinstance(c.connection.engine.dialect, MySQLDialect):
op.create_foreign_key(
None,
"groups_resources_permissions",
"groups",
remote_cols=["id"],
local_cols=["group_id"],
onupdate="CASCADE",
ondelete="CASCADE",
)
if isinstance(c.connection.engine.dialect, MySQLDialect):
op.add_column("users_groups", sa.Column("group_id", sa.Integer()))
else:
op.add_column(
"users_groups",
sa.Column(
"group_id",
sa.Integer(),
sa.ForeignKey(
"groups.id",
onupdate="CASCADE",
ondelete="CASCADE" # noqa
),
),
) # noqa
op.execute("""update users_groups set group_id =
(select id from groups where groups.group_name=users_groups.group_name)""")
if isinstance(c.connection.engine.dialect, MySQLDialect):
op.add_column("users_groups", sa.Column("user_id", sa.Integer()))
else:
op.add_column(
"users_groups",
sa.Column(
"user_id",
sa.Integer(),
sa.ForeignKey(
"users.id",
onupdate="CASCADE",
ondelete="CASCADE" # noqa
),
),
) # noqa
op.execute("""update users_groups set user_id =
(select id from users where users.user_name=users_groups.user_name)""")
op.drop_constraint(users_groups_pkey, "users_groups", type="primary")
op.create_primary_key(users_groups_pkey,
"users_groups",
cols=["user_id", "group_id"])
if isinstance(c.connection.engine.dialect, MySQLDialect):
op.create_foreign_key(
None,
"users_groups",
"groups",
remote_cols=["id"],
local_cols=["group_id"],
onupdate="CASCADE",
ondelete="CASCADE",
)
op.create_foreign_key(
None,
"users_groups",
"users",
remote_cols=["id"],
local_cols=["user_id"],
onupdate="CASCADE",
ondelete="CASCADE",
)
if isinstance(c.connection.engine.dialect, MySQLDialect):
op.add_column("users_permissions", sa.Column("user_id", sa.Integer()))
else:
op.add_column(
"users_permissions",
sa.Column(
"user_id",
sa.Integer(),
sa.ForeignKey(
"users.id",
onupdate="CASCADE",
ondelete="CASCADE" # noqa
),
),
) # noqa
op.execute("""update users_permissions set user_id =
(select id from groups where groups.group_name=users_permissions.user_name)"""
) # noqa
op.drop_constraint(users_permissions_pkey,
"users_permissions",
type="primary")
op.create_primary_key(users_permissions_pkey,
"users_permissions",
cols=["user_id", "perm_name"])
if isinstance(c.connection.engine.dialect, MySQLDialect):
op.create_foreign_key(
None,
"users_permissions",
"users",
remote_cols=["id"],
local_cols=["user_id"],
onupdate="CASCADE",
ondelete="CASCADE",
)
if isinstance(c.connection.engine.dialect, MySQLDialect):
op.add_column("users_resources_permissions",
sa.Column("user_id", sa.Integer()))
else:
op.add_column(
"users_resources_permissions",
sa.Column(
"user_id",
sa.Integer(),
sa.ForeignKey("users.id",
onupdate="CASCADE",
ondelete="CASCADE"),
),
)
op.execute("""update users_resources_permissions set user_id =
(select id from users where users.user_name=users_resources_permissions.user_name)"""
) # noqa
op.drop_constraint(users_resources_permissions_pkey,
"users_resources_permissions",
type="primary")
op.create_primary_key(
users_resources_permissions_pkey,
"users_resources_permissions",
cols=["user_id", "resource_id", "perm_name"],
)
if isinstance(c.connection.engine.dialect, MySQLDialect):
op.create_foreign_key(
None,
"users_resources_permissions",
"users",
remote_cols=["id"],
local_cols=["user_id"],
onupdate="CASCADE",
ondelete="CASCADE",
)
op.drop_column("resources", "owner_user_name")
op.drop_column("resources", "owner_group_name")
op.drop_column("groups_permissions", "group_name")
op.drop_column("groups_resources_permissions", "group_name")
op.drop_column("users_resources_permissions", "user_name")
op.drop_column("users_groups", "group_name")
op.drop_column("users_groups", "user_name")
op.drop_column("users_permissions", "user_name")
def get_all_tables(db):
"""Returns a dict containing all tables grouped by schema"""
inspector = Inspector.from_engine(db.engine)
schemas = sorted(set(inspector.get_schema_names()) - {'information_schema'})
return dict(zip(schemas, (inspector.get_table_names(schema=schema) for schema in schemas)))
def downgrade():
conn = op.get_bind()
inspector = Inspector.from_engine(conn)
tables = inspector.get_table_names()
if 'products_has_associates' in tables:
op.drop_table('products_has_associates')
def get_columns(
cls, inspector: Inspector, table_name: str, schema: Optional[str]
) -> List[Dict[str, Any]]:
return inspector.get_columns(table_name, schema)
def exists_table(table_name):
"""check if table exists"""
engine = create_engine(get_db_url())
inspector = Inspector.from_engine(engine)
table = inspector.get_table_names()
return table_name in table
def upgrade():
# Define columns that need to be added/deleted
user_columns = [Column('gid', String),
Column('display_name', String)]
burst_columns = [Column('range1', String), Column('range2', String), Column('fk_simulation', Integer),
Column('fk_operation_group', Integer), Column('fk_metric_operation_group', Integer)]
op_column = Column('view_model_disk_size', Integer)
# Get tables
inspector = Inspector.from_engine(conn)
table_names = inspector.get_table_names()
tables = Base.metadata.tables
try:
op.rename_table('BURST_CONFIGURATIONS', 'BurstConfiguration')
# Dropping tables which don't exist in the new version
op.drop_table('MAPPED_LOOK_UP_TABLE_DATA')
op.drop_table('MAPPED_DATATYPE_MEASURE_DATA')
op.drop_table('MAPPED_SPATIAL_PATTERN_VOLUME_DATA')
op.drop_table('MAPPED_SIMULATION_STATE_DATA')
op.drop_table('WORKFLOW_STEPS')
op.drop_table('WORKFLOW_VIEW_STEPS')
# Dropping tables which will be repopulated from the H5 files
op.drop_table('MAPPED_COHERENCE_SPECTRUM_DATA')
op.drop_table('MAPPED_COMPLEX_COHERENCE_SPECTRUM_DATA')
op.drop_table('MAPPED_CONNECTIVITY_ANNOTATIONS_DATA')
op.drop_table('MAPPED_CONNECTIVITY_MEASURE_DATA')
op.drop_table('MAPPED_CONNECTIVITY_DATA')
op.drop_table('MAPPED_CORRELATION_COEFFICIENTS_DATA')
op.drop_table('MAPPED_COVARIANCE_DATA')
op.drop_table('MAPPED_CROSS_CORRELATION_DATA')
op.drop_table('MAPPED_FCD_DATA')
op.drop_table('MAPPED_FOURIER_SPECTRUM_DATA')
op.drop_table('MAPPED_INDEPENDENT_COMPONENTS_DATA')
op.drop_table('MAPPED_LOCAL_CONNECTIVITY_DATA')
op.drop_table('MAPPED_PRINCIPAL_COMPONENTS_DATA')
op.drop_table('MAPPED_PROJECTION_MATRIX_DATA')
op.drop_table('MAPPED_REGION_MAPPING_DATA')
op.drop_table('MAPPED_REGION_VOLUME_MAPPING_DATA')
op.drop_table('MAPPED_TIME_SERIES_REGION_DATA')
op.drop_table('MAPPED_TIME_SERIES_EEG_DATA')
op.drop_table('MAPPED_TIME_SERIES_MEG_DATA')
op.drop_table('MAPPED_TIME_SERIES_SEEG_DATA')
op.drop_table('MAPPED_TIME_SERIES_SURFACE_DATA')
op.drop_table('MAPPED_TIME_SERIES_VOLUME_DATA')
op.drop_table('MAPPED_TIME_SERIES_DATA')
op.drop_table('MAPPED_SENSORS_DATA')
op.drop_table('MAPPED_TRACTS_DATA')
op.drop_table('MAPPED_STIMULI_REGION_DATA')
op.drop_table('MAPPED_STIMULI_SURFACE_DATA')
op.drop_table('MAPPED_STRUCTURAL_MRI_DATA')
op.drop_table('MAPPED_SURFACE_DATA')
op.drop_table('MAPPED_VALUE_WRAPPER_DATA')
op.drop_table('MAPPED_VOLUME_DATA')
op.drop_table('MAPPED_WAVELET_COEFFICIENTS_DATA')
op.drop_table('DATA_TYPES_GROUPS')
op.drop_table('MAPPED_ARRAY_DATA')
op.drop_table('MAPPED_SPATIO_TEMPORAL_PATTERN_DATA')
op.drop_table('MAPPED_SPATIAL_PATTERN_DATA')
op.drop_table('WORKFLOWS')
# Delete migrate_version if exists
if 'migrate_version' in table_names:
op.drop_table('migrate_version')
except Exception as excep:
LOGGER.exception(excep)
# Migrating USERS table
if TvbProfile.current.db.SELECTED_DB == 'postgres':
op.add_column('USERS', user_columns[0])
op.add_column('USERS', user_columns[1])
op.create_unique_constraint('USERS_gid_key', 'USERS', ['gid'])
else:
with op.batch_alter_table('USERS', table_args=(UniqueConstraint('gid'),)) as batch_op:
batch_op.add_column(user_columns[0])
batch_op.add_column(user_columns[1])
users_table = tables['USERS']
user_ids = conn.execute("""SELECT U.id FROM "USERS" U""").fetchall()
for id in user_ids:
conn.execute(users_table.update().where(users_table.c.id == id[0]).
values({"gid": uuid.uuid4().hex, "display_name": users_table.c.username}))
conn.execute('COMMIT')
# Migrating BurstConfiguration table
burst_config_table = tables['BurstConfiguration']
for column in burst_columns:
op.add_column('BurstConfiguration', column)
try:
op.alter_column('BurstConfiguration', '_dynamic_ids', new_column_name='dynamic_ids')
op.alter_column('BurstConfiguration', '_simulator_configuration', new_column_name='simulator_gid')
conn.execute(burst_config_table.delete().where(burst_config_table.c.status == 'error'))
ranges = conn.execute("""SELECT OG.id, OG.range1, OG.range2 from "OPERATION_GROUPS" OG """).fetchall()
ranges_1 = []
ranges_2 = []
for r in ranges:
ranges_1.append(str(r[1]))
ranges_2.append(str(r[2]))
new_ranges_1 = _migrate_range_params(ranges_1)
new_ranges_2 = _migrate_range_params(ranges_2)
# Migrating Operation Groups
operation_groups_table = tables['OPERATION_GROUPS']
operation_groups = conn.execute("""SELECT * FROM "OPERATION_GROUPS" """).fetchall()
for op_g in operation_groups:
operation = conn.execute("""SELECT fk_operation_group, parameters, meta_data FROM "OPERATIONS" O """
"""WHERE O.fk_operation_group = """ + str(op_g[0])).fetchone()
burst_id = eval(operation[2])['Burst_Reference']
# Find if operation refers to an operation group or a metric operation group
if 'time_series' in operation[1]:
conn.execute(burst_config_table.update().where(burst_config_table.c.id == burst_id).
values({"fk_metric_operation_group": operation[0]}))
else:
conn.execute(burst_config_table.update().where(burst_config_table.c.id == burst_id).
values({"fk_operation_group": operation[0]}))
for i in range(len(ranges_1)):
range1 = str(new_ranges_1[i]).replace('\'', '')
range2 = str(new_ranges_2[i]).replace('\'', '')
_update_range_parameters(burst_config_table, operation_groups_table, range1, range2, ranges[i][0])
conn.execute('COMMIT')
except Exception as excep:
LOGGER.exception(excep)
# Finish BurstConfiguration migration by deleting unused column and adding foreign keys
with op.batch_alter_table('BurstConfiguration') as batch_op:
batch_op.drop_column('workflows_number')
batch_op.create_foreign_key('bc_fk_simulation', 'OPERATIONS', ['fk_simulation'], ['id'])
batch_op.create_foreign_key('bc_fk_operation_group', 'OPERATION_GROUPS', ['fk_operation_group'], ['id'])
batch_op.create_foreign_key('bc_metric_operation_group', 'OPERATION_GROUPS',
['fk_metric_operation_group'],['id'])
conn.execute('COMMIT')
# MIGRATING Operations
op_table = tables['OPERATIONS']
try:
burst_ref_metadata = conn.execute("""SELECT id, meta_data FROM "OPERATIONS" """
"""WHERE meta_data like '%%Burst_Reference%%' """).fetchall()
op.alter_column('OPERATIONS', 'parameters', new_column_name='view_model_gid')
for metadata in burst_ref_metadata:
metadata_dict = eval(str(metadata[1]))
conn.execute(op_table.update().where(op_table.c.id == metadata[0]).
values({'view_model_gid': json.dumps(metadata_dict['Burst_Reference'])}))
op.rename_table('BurstConfiguration', 'BURST_CONFIGURATION')
conn.execute('COMMIT')
except Exception as excep:
LOGGER.exception(excep)
with op.batch_alter_table('OPERATIONS') as batch_op:
batch_op.add_column(op_column)
batch_op.drop_column('meta_data')
conn.execute('COMMIT')
try:
op.drop_table('ALGORITHMS')
op.drop_table('ALGORITHM_CATEGORIES')
op.drop_table('DATA_TYPES')
except Exception as excep:
try:
conn.execute("""DROP TABLE if exists "ALGORITHMS" cascade; """)
conn.execute("""DROP TABLE if exists "ALGORITHM_CATEGORIES" cascade; """)
conn.execute("""DROP TABLE if exists "DATA_TYPES" cascade; """)
except Exception as excep:
LOGGER.exception(excep)
LOGGER.exception(excep)
def reflect_table(engine, tablename):
meta = MetaData()
table = Table(tablename, meta)
insp = Inspector.from_engine(engine)
insp.reflecttable(table, None)
return table
def get_engine(uri):
engine = create_engine(uri)
inspector = Inspector.from_engine(engine)
if not inspector.get_table_names():
create_database(engine)
return engine
level = Column("level", String(32), nullable=False)
term = Column("term", String(32), nullable=False)
type_of_match = Column("type_of_match", String(32), nullable=False)
# Now we've created the classes for the database, we'll associate the class with the database and create any missing tables
Messages.metadata.create_all(database)
if backup:
Messages.metadata.create_all(database_backup)
# database is init, now check and see if the fts table is there
inspector = Inspector.from_engine(database)
if "messages_fts" not in inspector.get_table_names():
import sys
acarshub_logging.log(
"Missing FTS TABLE! Aborting!", "database", level=LOG_LEVEL["ERROR"]
)
sys.exit(1)
# messages_idx = Table(
# "messages_fts",
# Messages.metadata,
# #,
# Column("rowid", Integer(), key="id", primary_key=True),
# Column("depa", String(32)),
# Column("dsta", String(32)),
def _process_table(
self,
dataset_name: str,
inspector: Inspector,
schema: str,
table: str,
sql_config: SQLAlchemyConfig,
) -> Iterable[Union[SqlWorkUnit, MetadataWorkUnit]]:
columns = self._get_columns(dataset_name, inspector, schema, table)
dataset_urn = make_dataset_urn_with_platform_instance(
self.platform,
dataset_name,
self.config.platform_instance,
self.config.env,
)
dataset_snapshot = DatasetSnapshot(
urn=dataset_urn,
aspects=[StatusClass(removed=False)],
)
if self.is_stateful_ingestion_configured():
cur_checkpoint = self.get_current_checkpoint(
self.get_default_ingestion_job_id())
if cur_checkpoint is not None:
checkpoint_state = cast(BaseSQLAlchemyCheckpointState,
cur_checkpoint.state)
checkpoint_state.add_table_urn(dataset_urn)
description, properties, location_urn = self.get_table_properties(
inspector, schema, table)
dataset_properties = DatasetPropertiesClass(
name=table,
description=description,
customProperties=properties,
)
dataset_snapshot.aspects.append(dataset_properties)
if location_urn:
external_upstream_table = UpstreamClass(
dataset=location_urn,
type=DatasetLineageTypeClass.COPY,
)
lineage_mcpw = MetadataChangeProposalWrapper(
entityType="dataset",
changeType=ChangeTypeClass.UPSERT,
entityUrn=dataset_snapshot.urn,
aspectName="upstreamLineage",
aspect=UpstreamLineage(upstreams=[external_upstream_table]),
)
lineage_wu = MetadataWorkUnit(
id=
f"{self.platform}-{lineage_mcpw.entityUrn}-{lineage_mcpw.aspectName}",
mcp=lineage_mcpw,
)
yield lineage_wu
pk_constraints: dict = inspector.get_pk_constraint(table, schema)
foreign_keys = self._get_foreign_keys(dataset_urn, inspector, schema,
table)
schema_fields = self.get_schema_fields(dataset_name, columns,
pk_constraints)
schema_metadata = get_schema_metadata(
self.report,
dataset_name,
self.platform,
columns,
pk_constraints,
foreign_keys,
schema_fields,
)
dataset_snapshot.aspects.append(schema_metadata)
db_name = self.get_db_name(inspector)
yield from self.add_table_to_schema_container(dataset_urn, db_name,
schema)
mce = MetadataChangeEvent(proposedSnapshot=dataset_snapshot)
wu = SqlWorkUnit(id=dataset_name, mce=mce)
self.report.report_workunit(wu)
yield wu
dpi_aspect = self.get_dataplatform_instance_aspect(
dataset_urn=dataset_urn)
if dpi_aspect:
yield dpi_aspect
subtypes_aspect = MetadataWorkUnit(
id=f"{dataset_name}-subtypes",
mcp=MetadataChangeProposalWrapper(
entityType="dataset",
changeType=ChangeTypeClass.UPSERT,
entityUrn=dataset_urn,
aspectName="subTypes",
aspect=SubTypesClass(typeNames=["table"]),
),
)
yield subtypes_aspect
yield from self._get_domain_wu(
dataset_name=dataset_name,
entity_urn=dataset_urn,
entity_type="dataset",
sql_config=sql_config,
)
Revises: 02_add_venue_fields_exc_genres
Create Date: 2020-12-24 12:37:52.424887
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.engine.reflection import Inspector
# revision identifiers, used by Alembic.
revision = '03_add_genre_table'
down_revision = '02_add_venue_fields_exc_genres'
branch_labels = None
depends_on = None
conn = op.get_bind()
inspector = Inspector.from_engine(conn)
tables = inspector.get_table_names()
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
if 'genres' not in tables:
genre = op.create_table(
'genres',
sa.Column('id', sa.Integer, nullable=False, primary_key=True),
sa.Column('name', sa.String, nullable=False),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('name'))
res = conn.execute("select * from genres")
results = res.fetchall()
def register_schema(providers: List[str],
db_name: str,
schema_base: DeclarativeMeta,
entity_type: str = 'stock'):
"""
function for register schema,please declare them before register
:param providers: the supported providers for the schema
:type providers:
:param db_name: database name for the schema
:type db_name:
:param schema_base:
:type schema_base:
:param entity_type: the schema related entity_type
:type entity_type:
:return:
:rtype:
"""
schemas = []
for item in schema_base._decl_class_registry.items():
cls = item[1]
if type(cls) == DeclarativeMeta:
# register provider to the schema
for provider in providers:
if issubclass(cls, Mixin):
cls.register_provider(provider)
if zvt_context.dbname_map_schemas.get(db_name):
schemas = zvt_context.dbname_map_schemas[db_name]
zvt_context.schemas.append(cls)
add_to_map_list(the_map=zvt_context.entity_map_schemas, key=entity_type, value=cls)
schemas.append(cls)
zvt_context.dbname_map_schemas[db_name] = schemas
for provider in providers:
# track in in _providers
if provider not in zvt_context.providers:
zvt_context.providers.append(provider)
if not zvt_context.provider_map_dbnames.get(provider):
zvt_context.provider_map_dbnames[provider] = []
zvt_context.provider_map_dbnames[provider].append(db_name)
zvt_context.dbname_map_base[db_name] = schema_base
# create the db & table
engine = get_db_engine(provider, db_name=db_name)
schema_base.metadata.create_all(engine)
session_fac = get_db_session_factory(provider, db_name=db_name)
session_fac.configure(bind=engine)
for provider in providers:
engine = get_db_engine(provider, db_name=db_name)
inspector = Inspector.from_engine(engine)
# create index for 'timestamp','entity_id','code','report_period','updated_timestamp
for table_name, table in iter(schema_base.metadata.tables.items()):
index_column_names = [index['name'] for index in inspector.get_indexes(table_name)]
logger.debug('engine:{},table:{},index:{}'.format(engine, table_name, index_column_names))
for col in ['timestamp', 'entity_id', 'code', 'report_period', 'created_timestamp', 'updated_timestamp']:
if col in table.c:
column = eval('table.c.{}'.format(col))
index = sqlalchemy.schema.Index('{}_{}_index'.format(table_name, col), column)
if index.name not in index_column_names:
index.create(engine)
for cols in [('timestamp', 'entity_id'), ('timestamp', 'code')]:
if (cols[0] in table.c) and (col[1] in table.c):
column0 = eval('table.c.{}'.format(col[0]))
column1 = eval('table.c.{}'.format(col[1]))
index = sqlalchemy.schema.Index('{}_{}_{}_index'.format(table_name, col[0], col[1]), column0,
column1)
if index.name not in index_column_names:
index.create(engine)
def init():
inspector = Inspector.from_engine(engine)
if not Quote.__tablename__ in inspector.get_table_names():
create()
def init():
# Models are imported here to prevent a circular import where we would
# import models and the models would import that db object just above us.
# They're also imported here in this function because they implicitly
# monkey-patch the threading module, and we might not need that if all we want
# from the namespace is something like app.version, like in repl.py for example.
from models import APIKey
from models import FeedGroup
from models import Feed
from models import Article
from models import Event
from resources import api_key
from resources import feeds
from resources import feedgroups
from resources import articles
api.add_resource(api_key.KeyCollection, "/keys")
api.add_resource(api_key.KeyResource, "/keys/<string:name>")
api.add_resource(feedgroups.FeedGroupCollection, "/feeds")
api.add_resource(feedgroups.FeedGroupResource, "/feeds/<string:groupname>")
api.add_resource(feedgroups.FeedGroupStop,
"/feeds/<string:groupname>/stop")
api.add_resource(feedgroups.FeedGroupStart,
"/feeds/<string:groupname>/start")
api.add_resource(feedgroups.FeedGroupArticles,
"/feeds/<string:groupname>/articles")
api.add_resource(feedgroups.FeedGroupSearch,
"/feeds/<string:groupname>/search/<string:terms>")
api.add_resource(feedgroups.FeedGroupCount,
"/feeds/<string:groupname>/count")
api.add_resource(feeds.FeedResource,
"/feeds/<string:groupname>/<string:name>")
api.add_resource(feeds.FeedArticleCollection,
"/feeds/<string:groupname>/<string:name>/articles")
api.add_resource(
feeds.FeedSearch,
"/feeds/<string:groupname>/<string:name>/search/<string:terms>")
api.add_resource(feeds.FeedStartResource,
"/feeds/<string:groupname>/<string:name>/start")
api.add_resource(feeds.FeedStopResource,
"/feeds/<string:groupname>/<string:name>/stop")
api.add_resource(articles.ArticleCollection, "/articles")
api.add_resource(articles.ArticleResource, "/articles/<string:uid>")
api.add_resource(articles.ArticleSearch, "/articles/search/<string:terms>")
api.add_resource(articles.ArticleCount, "/articles/count")
# Create the database schema if it's not already laid out.
inspector = Inspector.from_engine(db.engine)
tables = [table_name for table_name in inspector.get_table_names()]
if 'api_keys' not in tables:
db.create_all()
master = models.APIKey(name=app.config['MASTER_KEY_NAME'])
if app.config['MASTER_KEY']: master.key = app.config['MASTER_KEY']
else: master.key = master.generate_key_str()
print master.key
master.active = True
db.session.add(master)
db.session.commit()
def test_get_current_revision_doesnt_create_version_table(self):
context = self.make_one(connection=self.connection,
opts={'version_table': 'version_table'})
eq_(context.get_current_revision(), None)
insp = Inspector(self.connection)
assert ('version_table' not in insp.get_table_names())
class Db(object):
def __init__(self, sqla_conn, args, schema=None):
self.args = args
self.sqla_conn = sqla_conn
self.schema = schema
self.engine = sa.create_engine(sqla_conn)
self.meta = sa.MetaData(
bind=self.engine) # excised schema=schema to prevent errors
self.meta.reflect(schema=self.schema)
self.inspector = Inspector(bind=self.engine)
self.conn = self.engine.connect()
self.tables = OrderedDict()
for tbl in self.meta.sorted_tables:
tbl.db = self
# TODO: Replace all these monkeypatches with an instance assigment
tbl.find_n_rows = types.MethodType(_find_n_rows, tbl)
tbl.random_row_func = types.MethodType(_random_row_func, tbl)
tbl.fks = self.inspector.get_foreign_keys(tbl.name,
schema=tbl.schema)
tbl.pk = self.inspector.get_primary_keys(tbl.name,
schema=tbl.schema)
tbl.filtered_by = types.MethodType(_filtered_by, tbl)
tbl.by_pk = types.MethodType(_by_pk, tbl)
tbl.pk_val = types.MethodType(_pk_val, tbl)
tbl.exists = types.MethodType(_exists, tbl)
tbl.child_fks = []
tbl.find_n_rows(estimate=True)
self.tables[(tbl.schema, tbl.name)] = tbl
for ((tbl_schema, tbl_name), tbl) in self.tables.items():
for fk in tbl.fks:
fk['constrained_schema'] = tbl_schema
fk['constrained_table'] = tbl_name # TODO: check against constrained_table
self.tables[(fk['referred_schema'],
fk['referred_table'])].child_fks.append(fk)
def __repr__(self):
return "Db('%s')" % self.sqla_conn
def assign_target(self, target_db):
for ((tbl_schema, tbl_name), tbl) in self.tables.items():
tbl._random_row_gen_fn = types.MethodType(_random_row_gen_fn, tbl)
tbl.random_rows = tbl._random_row_gen_fn()
tbl.next_row = types.MethodType(_next_row, tbl)
target = target_db.tables[(tbl_schema, tbl_name)]
target.requested = deque()
target.required = deque()
if tbl.n_rows:
if self.args.logarithmic:
target.n_rows_desired = int(
math.pow(
10,
math.log10(tbl.n_rows) * self.args.fraction)) or 1
else:
target.n_rows_desired = int(
tbl.n_rows * self.args.fraction) or 1
else:
target.n_rows_desired = 0
target.source = tbl
tbl.target = target
target.completeness_score = types.MethodType(
_completeness_score, target)
logging.debug("assigned methods to %s" % target.name)
def confirm(self):
message = []
for (tbl_schema, tbl_name) in sorted(self.tables, key=lambda t: t[1]):
tbl = self.tables[(tbl_schema, tbl_name)]
message.append("Create %d rows from %d in %s.%s" %
(tbl.target.n_rows_desired, tbl.n_rows, tbl_schema
or '', tbl_name))
print("\n".join(sorted(message)))
if self.args.yes:
return True
response = input("Proceed? (Y/n) ").strip().lower()
return (not response) or (response[0] == 'y')
def create_row_in(self, source_row, target_db, target, prioritized=False):
logging.debug('create_row_in %s:%s ' %
(target.name, target.pk_val(source_row)))
row_exists = target.exists(**(dict(source_row)))
logging.debug("Row exists? %s" % str(row_exists))
if row_exists and not prioritized:
return
if not row_exists:
# make sure that all required rows are in parent table(s)
for fk in target.fks:
target_parent = target_db.tables[(fk['referred_schema'],
fk['referred_table'])]
slct = sa.sql.select([
target_parent,
])
any_non_null_key_columns = False
for (parent_col, child_col) in zip(fk['referred_columns'],
fk['constrained_columns']):
slct = slct.where(
target_parent.c[parent_col] == source_row[child_col])
if source_row[child_col] is not None:
any_non_null_key_columns = True
if any_non_null_key_columns:
target_parent_row = target_db.conn.execute(slct).first()
if not target_parent_row:
source_parent_row = self.conn.execute(slct).first()
self.create_row_in(source_parent_row, target_db,
target_parent)
ins = target.insert().values(**source_row)
target_db.conn.execute(ins)
target.n_rows += 1
for child_fk in target.child_fks:
child = self.tables[(child_fk['constrained_schema'],
child_fk['constrained_table'])]
slct = sa.sql.select([
child,
])
for (child_col, this_col) in zip(child_fk['constrained_columns'],
child_fk['referred_columns']):
slct = slct.where(child.c[child_col] == source_row[this_col])
if not prioritized:
slct = slct.limit(self.args.children)
for (n, desired_row) in enumerate(self.conn.execute(slct)):
if prioritized:
child.target.required.append((desired_row, prioritized))
elif (n == 0):
child.target.requested.appendleft(
(desired_row, prioritized))
else:
child.target.requested.append((desired_row, prioritized))
def create_subset_in(self, target_db):
for (tbl_name, pks) in self.args.force_rows.items():
if '.' in tbl_name:
(tbl_schema, tbl_name) = tbl_name.split('.', 1)
else:
tbl_schema = None
source = self.tables[(tbl_schema, tbl_name)]
for pk in pks:
source_row = source.by_pk(pk)
if source_row:
self.create_row_in(source_row,
target_db,
source.target,
prioritized=True)
else:
logging.warn("requested %s:%s not found in source db,"
"could not create" % (source.name, pk))
while True:
targets = sorted(target_db.tables.values(),
key=lambda t: t.completeness_score())
try:
target = targets.pop(0)
while not target.source.n_rows:
target = targets.pop(0)
except IndexError: # pop failure, no more tables
return
logging.debug("total n_rows in target: %d" % sum(
(t.n_rows for t in target_db.tables.values())))
logging.debug(
"target tables with 0 n_rows: %s" %
", ".join(t.name
for t in target_db.tables.values() if not t.n_rows))
logging.info("lowest completeness score (in %s) at %f" %
(target.name, target.completeness_score()))
if target.completeness_score() > 0.97:
return
(source_row, prioritized) = target.source.next_row()
self.create_row_in(source_row,
target_db,
target,
prioritized=prioritized)
def handle(self, options, global_options, *args):
from zipfile import ZipFile, ZIP_DEFLATED
from StringIO import StringIO
output_dir = os.path.join(options.output_dir, options.engine)
if not os.path.exists(output_dir):
os.makedirs(output_dir)
engine = get_engine(options, global_options)
if not args:
print "Failed! You should pass one or more tables name."
sys.exit(1)
zipfile = None
if options.zipfile:
zipfile = ZipFile(options.zipfile, 'w', compression=ZIP_DEFLATED)
inspector = Inspector.from_engine(engine)
tables = get_sorted_tables(
get_tables(global_options.apps_dir,
tables=args,
engine_name=options.engine,
settings_file=global_options.settings,
local_settings_file=global_options.local_settings))
_len = len(tables)
for i, (name, t) in enumerate(tables):
if global_options.verbose:
print '[%s] Dumpping %s...' % (options.engine,
show_table(name, t, i, _len)),
filename = os.path.join(output_dir, name + '.txt')
if options.text:
format = 'txt'
else:
format = None
#process zipfile
if options.zipfile:
fileobj = StringIO()
filename = os.path.basename(filename)
else:
fileobj = filename
t = dump_table(t,
fileobj,
engine,
delimiter=options.delimiter,
format=format,
encoding=options.encoding,
inspector=inspector,
engine_name=engine.engine_name)
#write zip content
if options.zipfile and zipfile:
zipfile.writestr(filename, fileobj.getvalue())
if global_options.verbose:
print t
if zipfile:
zipfile.close()
def get_table_names(cls, database: "Database", inspector: Inspector,
schema: Optional[str]) -> List[str]:
"""Need to disregard the schema for Sqlite"""
return sorted(inspector.get_table_names())
def get_columns(
cls, inspector: Inspector, table_name: str, schema: str
) -> List[dict]:
return inspector.get_columns(table_name, schema)
def downgrade():
conn = op.get_bind()
inspector = Inspector.from_engine(conn)
tables = inspector.get_table_names()
if 'sensor_instance' in tables:
op.drop_table('sensor_instance')
def inspector_under_test(engine_under_test):
from sqlalchemy.engine.reflection import Inspector
return Inspector.from_engine(engine_under_test)