def finalizer():
del backend.connection
print("Dropping schema...")
meta = MetaData(engine)
meta.reflect()
meta.drop_all()
print("Done...")
def test_create_table(engine, bigquery_dataset):
meta = MetaData()
Table(
f"{bigquery_dataset}.test_table_create",
meta,
Column("integer_c", sqlalchemy.Integer, doc="column description"),
Column("float_c", sqlalchemy.Float),
Column("decimal_c", sqlalchemy.DECIMAL),
Column("string_c", sqlalchemy.String),
Column("text_c", sqlalchemy.Text),
Column("boolean_c", sqlalchemy.Boolean),
Column("timestamp_c", sqlalchemy.TIMESTAMP),
Column("datetime_c", sqlalchemy.DATETIME),
Column("date_c", sqlalchemy.DATE),
Column("time_c", sqlalchemy.TIME),
Column("binary_c", sqlalchemy.BINARY),
bigquery_description="test table description",
bigquery_friendly_name="test table name",
)
meta.create_all(engine)
meta.drop_all(engine)
# Test creating tables with declarative_base
Base = declarative_base()
class TableTest(Base):
__tablename__ = f"{bigquery_dataset}.test_table_create2"
integer_c = Column(sqlalchemy.Integer, primary_key=True)
float_c = Column(sqlalchemy.Float)
Base.metadata.create_all(engine)
Base.metadata.drop_all(engine)
def db(app, request):
"""
Returns session-wide initialised database.
Drops all existing tables - Meta follows Postgres FKs
"""
with app.app_context():
# Clear out any existing tables
metadata = MetaData(_db.engine)
metadata.reflect()
for table in metadata.tables.values():
for fk in table.foreign_keys:
_db.engine.execute(DropConstraint(fk.constraint))
metadata.drop_all()
_db.drop_all()
# ############################################
# There are 2 approaches, an empty database, or the same one that the app will use
# create the tables
# _db.create_all()
# or
# Use Alembic to load all of the DB revisions including supporting lookup data
# This is the path we'll use in NAMEX!!
# even though this isn't referenced directly, it sets up the internal configs that upgrade needs
migrate = Migrate(app, _db)
upgrade()
def _sql_backend(request,engine,**kwargs):
meta = MetaData(engine)
meta.reflect()
meta.drop_all()
#we enable foreign key checks for SQLITE
if str(engine.url).startswith('sqlite://'):
engine.connect().execute('pragma foreign_keys=ON')
if not 'ondelete' in kwargs:
kwargs['ondelete'] = 'CASCADE'
backend = SqlBackend(engine = engine,**kwargs)
backend.init_schema()
backend.create_schema()
def finalizer():
backend.rollback()
del backend.connection
print("Dropping schema...")
#we disable foreign key checks for SQLITE (as dropping tables with circular foreign keys won't work otherwise...)
if str(engine.url).startswith('sqlite://'):
engine.connect().execute('pragma foreign_keys=OFF')
meta = MetaData(engine)
meta.reflect()
meta.drop_all()
print("Done...")
request.addfinalizer(finalizer)
return backend
def _clean_database(self):
log.msg("cleaning database %s" % self.db_url)
engine = sqlalchemy.create_engine(self.db_url)
meta = MetaData()
# there are some tables for which reflection sometimes fails, but since
# we're just dropping them, we don't need actual schema - a fake
# table will do the trick
for table in [ 'buildrequests', 'builds',
'buildset_properties', 'buildsets', 'change_properties',
'change_files', 'change_links',
'changes', 'patches', 'sourcestamp_changes', 'sourcestamps',
'scheduler_changes', 'scheduler_upstream_buildsets',
'schedulers' ]:
sqlalchemy.Table(table, meta,
sqlalchemy.Column('tmp', sqlalchemy.Integer))
# load any remaining tables
meta.reflect(bind=engine)
# and drop them, if they exist
meta.drop_all(bind=engine, checkfirst=True)
engine.dispose()
def app(request):
"""
flask test application
:param request:
:return:
"""
# initialize flask application
app = create_app('test_app', config.TestConfig)
# initialize database
with app.app_context():
metadata = MetaData(db.engine)
metadata.reflect()
# drop database
metadata.drop_all()
# create tables based on the models
db.create_all()
# insert test data
create_dummy_data()
app = FlaskPytest(app)
yield app
# remove temporary tables in database
request.addfinalizer(metadata.drop_all)
def test_create_table(engine):
meta = MetaData()
table = Table('test_pybigquery.test_table_create',
meta,
Column('integer_c',
sqlalchemy.Integer,
doc="column description"),
Column('float_c', sqlalchemy.Float),
Column('decimal_c', sqlalchemy.DECIMAL),
Column('string_c', sqlalchemy.String),
Column('text_c', sqlalchemy.Text),
Column('boolean_c', sqlalchemy.Boolean),
Column('timestamp_c', sqlalchemy.TIMESTAMP),
Column('datetime_c', sqlalchemy.DATETIME),
Column('date_c', sqlalchemy.DATE),
Column('time_c', sqlalchemy.TIME),
Column('binary_c', sqlalchemy.BINARY),
bigquery_description="test table description",
bigquery_friendly_name="test table name")
meta.create_all(engine)
meta.drop_all(engine)
# Test creating tables with declarative_base
Base = declarative_base()
class TableTest(Base):
__tablename__ = 'test_pybigquery.test_table_create2'
integer_c = Column(sqlalchemy.Integer, primary_key=True)
float_c = Column(sqlalchemy.Float)
Base.metadata.create_all(engine)
Base.metadata.drop_all(engine)
def open_db(self, engine, mode='open'):
"mode: create, open, drop"
from sqlalchemy.ext.declarative import declarative_base, DeferredReflection
from sqlalchemy.schema import MetaData, DropConstraint
from sqlalchemy import event, Table
if mode != 'open':
# Clear out any existing tables
metadata = MetaData(engine)
metadata.reflect()
if engine.name != 'sqlite':
for table in metadata.tables.values():
for fk in table.foreign_keys:
engine.execute(DropConstraint(fk.constraint))
metadata.drop_all(engine)
if mode == 'drop': return
self.Base = declarative_base(cls=DeferredReflection)
self.Base.metadata.bind = engine
if mode == 'open':
@event.listens_for(Table, "column_reflect")
def column_reflect(inspector, table, column_info):
if table.metadata is self.Base.metadata:
if self.column_map and table.name in self.column_map:
column_info['key'] = self.column_map[table.name][
column_info['name']]
else:
column_info['key'] = column_info['name']
self.declare(reflect=mode == 'open')
if mode == 'create': self.Base.metadata.create_all(engine)
from sqlalchemy.orm import sessionmaker
self.Base.prepare(engine)
self.reflect_classes()
Session = sessionmaker(bind=engine)
self.session = Session()
def setupPackage():
os.environ['MONGO_URI'] = 'mongodb://localhost'
os.environ['MONGO_DB_NAME'] = 'royal_example'
os.environ['MONGO_DB_PREFIX'] = ''
# sqla extentsion setup.
global engine
alembic_config = Config()
alembic_config.set_main_option('script_location', 'example/ext/sqla/db')
alembic_config.set_main_option('sqlalchemy.url', mysql_uri)
engine = create_engine(mysql_uri)
try:
command.downgrade(alembic_config, 'base')
except:
log.exception("Migration downgrade failed, clearing all tables")
metadata = MetaData(engine)
metadata.reflect()
for table in metadata.tables.values():
for fk in table.foreign_keys:
engine.execute(DropConstraint(fk.constraint))
metadata.drop_all()
command.upgrade(alembic_config, 'head')
def setupPackage():
os.environ['MONGO_URI'] = 'mongodb://localhost'
os.environ['MONGO_DB_NAME'] = 'royal_example'
os.environ['MONGO_DB_PREFIX'] = ''
# sqla extentsion setup.
global engine
alembic_config = Config()
alembic_config.set_main_option('script_location',
'example/ext/sqla/db')
alembic_config.set_main_option('sqlalchemy.url', mysql_uri)
engine = create_engine(mysql_uri)
try:
command.downgrade(alembic_config, 'base')
except:
log.exception("Migration downgrade failed, clearing all tables")
metadata = MetaData(engine)
metadata.reflect()
for table in metadata.tables.values():
for fk in table.foreign_keys:
engine.execute(DropConstraint(fk.constraint))
metadata.drop_all()
command.upgrade(alembic_config, 'head')
def __thd_clean_database(self, conn):
# drop the known tables
model.Model.metadata.drop_all(bind=conn, checkfirst=True)
# see if we can find any other tables to drop
meta = MetaData(bind=conn)
meta.reflect()
meta.drop_all()
def __thd_clean_database(self, conn):
# drop the known tables
model.Model.metadata.drop_all(bind=conn, checkfirst=True)
# see if we can find any other tables to drop
meta = MetaData(bind=conn)
meta.reflect()
meta.drop_all()
def db(app): # pylint: disable=redefined-outer-name, invalid-name
"""Return a session-wide initialised database.
Drops all existing tables - Meta follows Postgres FKs
"""
with app.app_context():
# Clear out any existing tables
metadata = MetaData(_db.engine)
metadata.reflect()
for table in metadata.tables.values():
for fk in table.foreign_keys: # pylint: disable=invalid-name
_db.engine.execute(DropConstraint(fk.constraint))
metadata.drop_all()
_db.drop_all()
sequence_sql = """SELECT sequence_name FROM information_schema.sequences
WHERE sequence_schema='public'
"""
sess = _db.session()
for seq in [name for (name, ) in sess.execute(text(sequence_sql))]:
try:
sess.execute(text('DROP SEQUENCE public.%s ;' % seq))
print('DROP SEQUENCE public.%s ' % seq)
except Exception as err: # pylint: disable=broad-except
print(f'Error: {err}')
sess.commit()
# ############################################
# There are 2 approaches, an empty database, or the same one that the app will use
# create the tables
# _db.create_all()
# or
# Use Alembic to load all of the DB revisions including supporting lookup data
# This is the path we'll use in selfservice_api!!
# even though this isn't referenced directly,
# it sets up the internal configs that upgrade needs
Migrate(app, _db)
upgrade()
admin_user = {
'sub': '65a62428-6713-4e7d-8f12-99e56de58386',
'given_name': 'admin',
'family_name': 'ss',
'email': '*****@*****.**'
}
sess.execute(
text(
'INSERT INTO public."user"(' +
'created, modified, email, phone, first_name, last_name, oauth_id)'
+ "VALUES (now(), null, '" + admin_user['email'] + "', " +
"'123456789', '" + admin_user['given_name'] + "', " + " '" +
admin_user['family_name'] + "', '" + admin_user['sub'] +
"'); "))
sess.commit()
return _db
def drop_all_tables():
"""
remove all tables, not just those with metadata present in
this package
"""
Base.metadata.drop_all(bind=engine)
meta = MetaData()
meta.reflect(bind=engine)
meta.drop_all(bind=engine)
def db(app): # pylint: disable=redefined-outer-name, invalid-name
"""Return a session-wide initialised database.
Drops all existing tables - Meta follows Postgres FKs
"""
with app.app_context():
# clear all custom views
views_sql = """select table_name from INFORMATION_SCHEMA.views
WHERE table_schema = ANY (current_schemas(false))
"""
sess = _db.session()
for view in [name for (name, ) in sess.execute(text(views_sql))]:
try:
sess.execute(text('DROP VIEW public.%s ;' % view))
print('DROP VIEW public.%s ' % view)
except Exception as err: # pylint: disable=broad-except
print(f'Error: {err}')
sess.commit()
# Clear out any existing tables
metadata = MetaData(_db.engine)
metadata.reflect()
for table in metadata.tables.values():
for fk in table.foreign_keys: # pylint: disable=invalid-name
_db.engine.execute(DropConstraint(fk.constraint))
metadata.drop_all()
_db.drop_all()
sequence_sql = """SELECT sequence_name FROM information_schema.sequences
WHERE sequence_schema='public'
"""
sess = _db.session()
for seq in [name for (name, ) in sess.execute(text(sequence_sql))]:
try:
sess.execute(text('DROP SEQUENCE public.%s ;' % seq))
print('DROP SEQUENCE public.%s ' % seq)
except Exception as err: # pylint: disable=broad-except
print(f'Error: {err}')
sess.commit()
# ############################################
# There are 2 approaches, an empty database, or the same one that the app will use
# create the tables
# _db.create_all()
# or
# Use Alembic to load all of the DB revisions including supporting lookup data
# This is the path we'll use in auth_api!!
# even though this isn't referenced directly, it sets up the internal configs that upgrade needs
Migrate(app, _db)
upgrade()
return _db
def finalizer():
backend.rollback()
del backend.connection
print("Dropping schema...")
#we disable foreign key checks for SQLITE (as dropping tables with circular foreign keys won't work otherwise...)
if str(engine.url).startswith('sqlite://'):
engine.connect().execute('pragma foreign_keys=OFF')
meta = MetaData(engine)
meta.reflect()
meta.drop_all()
print("Done...")
def __thd_clean_database(self, conn):
# drop the known tables, although sometimes this misses dependencies
try:
model.Model.metadata.drop_all(bind=conn, checkfirst=True)
except sa.exc.ProgrammingError:
pass
# see if we can find any other tables to drop
meta = MetaData(bind=conn)
meta.reflect()
meta.drop_all()
def __thd_clean_database(self, conn):
# drop the known tables, although sometimes this misses dependencies
try:
model.Model.metadata.drop_all(bind=conn, checkfirst=True)
except sa.exc.ProgrammingError:
pass
# see if we can find any other tables to drop
meta = MetaData(bind=conn)
meta.reflect()
meta.drop_all()
def initialize_db(config_uri, options={}):
setup_logging(config_uri)
settings = get_appsettings(config_uri, options=options)
engine = engine_from_config(settings, 'sqlalchemy.')
# delete all tables
meta = MetaData()
meta.reflect(bind=engine)
meta.drop_all(engine)
upgrade_db(config_uri)
def setup_clean_db(app):
# Clear out any existing tables
from subscity.models.base import DB
# db_name = os.environ.get('DN_NAME')
engine = DB.get_engine(app)
metadata = MetaData(engine)
metadata.reflect()
# for table in metadata.tables.values():
# for fk in table.foreign_keys:
# engine.execute(DropConstraint(fk.constraint))
metadata.drop_all()
apply_migrations()
def db(app): # pylint: disable=redefined-outer-name, invalid-name
"""Return a session-wide initialised database.
Drops all existing tables - Meta follows Postgres FKs
"""
with app.app_context():
# Clear out any existing tables
metadata = MetaData(_db.engine)
metadata.reflect()
for table in metadata.tables.values():
for fk in table.foreign_keys: # pylint: disable=invalid-name
_db.engine.execute(DropConstraint(fk.constraint))
metadata.drop_all()
_db.drop_all()
sequence_sql = """SELECT sequence_name FROM information_schema.sequences
WHERE sequence_schema='public'
"""
sess = _db.session()
for seq in [name for (name, ) in sess.execute(text(sequence_sql))]:
try:
sess.execute(text('DROP SEQUENCE public.%s ;' % seq))
print('DROP SEQUENCE public.%s ' % seq)
except Exception as err: # NOQA pylint: disable=broad-except
print(f'Error: {err}')
sess.commit()
# ############################################
# There are 2 approaches, an empty database, or the same one that the app will use
# create the tables
# _db.create_all()
# or
# Use Alembic to load all of the DB revisions including supporting lookup data
# This is the path we'll use in legal_api!!
# even though this isn't referenced directly, it sets up the internal configs that upgrade
import sys
migrations_path = [
folder for folder in sys.path if 'pay-api/pay-api' in folder
]
if len(migrations_path) > 0:
migrations_path = migrations_path[0].replace(
'/pay-api/src', '/pay-api/migrations')
# Fix for windows.
else:
migrations_path = os.path.abspath('../../pay-api/migrations')
Migrate(app, _db, directory=migrations_path)
upgrade()
return _db
def initialize_db(config_uri, options={}):
setup_logging(config_uri)
settings = get_appsettings(config_uri, options=options)
engine = engine_from_config(settings, 'sqlalchemy.')
# delete all tables
meta = MetaData()
meta.reflect(bind=engine)
meta.drop_all(engine)
upgrade_db(config_uri)
def _init_tables(engine):
global _tables
logger.debug("Cleaning tables")
metadata = MetaData(bind=engine)
metadata.reflect()
logger.debug("drop all tables")
metadata.drop_all()
logger.debug("create all tables")
Base.metadata.create_all(bind=engine)
engine.dispose()
logger.debug("Tables cleaned")
metadata = MetaData(bind=engine)
metadata.reflect()
_tables = [table for table in metadata.tables.iterkeys()]
def test_create_table(engine, inspector):
meta = MetaData()
Table('test_pybigquery.test_table_create',
meta,
Column('integer_c', sqlalchemy.Integer, doc="column description"),
Column('float_c', sqlalchemy.Float),
Column('decimal_c', sqlalchemy.DECIMAL),
Column('string_c', sqlalchemy.String),
Column('text_c', sqlalchemy.Text),
Column('boolean_c', sqlalchemy.Boolean),
Column('timestamp_c', sqlalchemy.TIMESTAMP),
Column('datetime_c', sqlalchemy.DATETIME),
Column('date_c', sqlalchemy.DATE),
Column('time_c', sqlalchemy.TIME),
Column('binary_c', sqlalchemy.BINARY),
bigquery_description="test table description",
bigquery_friendly_name="test table name",
bigquery_cluster_by=["integer_c", "string_c"],
bigquery_partition_by="DATE(timestamp_c)",
bigquery_require_partition_filtering=True)
meta.create_all(engine)
# Validate index creation
indexes = inspector.get_indexes('test_pybigquery.test_table_create')
assert len(indexes) == 2
assert indexes[0] == {
'name': 'partition',
'column_names': ['timestamp_c'],
'unique': False
}
assert indexes[1] == {
'name': 'clustering',
'column_names': ['integer_c', 'string_c'],
'unique': False
}
meta.drop_all(engine)
# Test creating tables with declarative_base
Base = declarative_base()
class TableTest(Base):
__tablename__ = 'test_pybigquery.test_table_create2'
integer_c = Column(sqlalchemy.Integer, primary_key=True)
float_c = Column(sqlalchemy.Float)
Base.metadata.create_all(engine)
Base.metadata.drop_all(engine)
def test_connection(
ctx: object,
metadata: MetaData,
engine: Engine,
real_transaction: bool = False,
ctx_connection_attribute_name: str = '_test_fx_connection',
) -> typing.Generator:
"""Joining a SQLAlchemy session into an external transaction for test suit.
:param object ctx: Context object to inject test connection into attribute
:param MetaData metadata: SQLAlchemy schema metadata
:param bool real_transaction: (Optional) Whether to use engine as connection directly
or make separate connection. Default: `False`
:param str ctx_connection_attribute_name: (Optional) Attribute name for injecting
test connection to the context object
Default: `'_test_fx_connection'`
.. seealso::
Documentation of the SQLAlchemy session used in test suites.
<http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#joining-a-session-into-an-external-transaction-such-as-for-test-suites>
""" # noqa
if real_transaction:
metadata.create_all(engine)
try:
yield engine
finally:
metadata.drop_all(engine, checkfirst=True)
return
connection = engine.connect()
try:
metadata.drop_all(connection, checkfirst=True)
transaction = connection.begin()
try:
metadata.create_all(bind=connection)
setattr(ctx, ctx_connection_attribute_name, connection)
try:
yield connection
finally:
delattr(ctx, ctx_connection_attribute_name)
finally:
transaction.rollback()
finally:
connection.close()
engine.dispose()
def initialize_db(config_uri):
# setup_logging(config_uri)
# settings = get_appsettings(config_uri, options=options)
config = configparser.ConfigParser()
config.read(config_uri)
settings = dict(config.items("alembic"))
engine = engine_from_config(settings, 'sqlalchemy.')
# delete all tables
meta = MetaData()
meta.reflect(bind=engine)
meta.drop_all(engine)
upgrade_db(config_uri)
def initialize_db(config_uri):
# setup_logging(config_uri)
# settings = get_appsettings(config_uri, options=options)
config = configparser.ConfigParser()
config.read(config_uri)
settings = dict(config.items("alembic"))
engine = engine_from_config(settings, 'sqlalchemy.')
# delete all tables
meta = MetaData()
meta.reflect(bind=engine)
meta.drop_all(engine)
upgrade_db(config_uri)
def db(app): # pylint: disable=redefined-outer-name, invalid-name
"""Return a session-wide initialised database.
Drops all existing tables - Meta follows Postgres FKs
"""
with app.app_context():
# Clear out any existing tables
metadata = MetaData(_db.engine)
metadata.reflect()
for table in metadata.tables.values():
for fk in table.foreign_keys: # pylint: disable=invalid-name
with suppress(Exception):
_db.engine.execute(DropConstraint(fk.constraint))
with suppress(Exception):
metadata.drop_all()
with suppress(Exception):
_db.drop_all()
sequence_sql = """SELECT sequence_name FROM information_schema.sequences
WHERE sequence_schema='public'
"""
sess = _db.session()
for seq in [name for (name, ) in sess.execute(text(sequence_sql))]:
with suppress(Exception):
sess.execute(text('DROP SEQUENCE public.%s ;' % seq))
print('DROP SEQUENCE public.%s ' % seq)
sess.commit()
# ############################################
# There are 2 approaches, an empty database, or the same one that the app will use
# create the tables
# _db.create_all()
# or
# Use Alembic to load all of the DB revisions including supporting lookup data
# This is the path we'll use in legal_api!!
# even though this isn't referenced directly, it sets up the internal configs that upgrade needs
Migrate(app, _db)
upgrade()
return _db
def _sql_backend(request,engine):
meta = MetaData(engine)
meta.reflect()
meta.drop_all()
backend = SqlBackend(engine = engine)
backend.init_schema()
backend.create_schema()
def finalizer():
del backend.connection
print("Dropping schema...")
meta = MetaData(engine)
meta.reflect()
meta.drop_all()
print("Done...")
request.addfinalizer(finalizer)
return backend
def __thd_clean_database(self, conn):
# drop the known tables, although sometimes this misses dependencies
try:
model.Model.metadata.drop_all(bind=conn, checkfirst=True)
except sa.exc.ProgrammingError:
pass
# see if we can find any other tables to drop
try:
meta = MetaData(bind=conn)
meta.reflect()
meta.drop_all()
except Exception:
# sometimes this goes badly wrong; being able to see the schema
# can be a big help
if conn.engine.dialect.name == 'sqlite':
r = conn.execute("select sql from sqlite_master "
"where type='table'")
log.msg("Current schema:")
for row in r.fetchall():
log.msg(row.sql)
raise
def __thd_clean_database(self, conn):
# drop the known tables, although sometimes this misses dependencies
try:
model.Model.metadata.drop_all(bind=conn, checkfirst=True)
except sa.exc.ProgrammingError:
pass
# see if we can find any other tables to drop
try:
meta = MetaData(bind=conn)
meta.reflect()
meta.drop_all()
except Exception, e:
# sometimes this goes badly wrong; being able to see the schema
# can be a big help
if conn.engine.dialect.name == 'sqlite':
r = conn.execute("select sql from sqlite_master "
"where type='table'")
log.msg("Current schema:")
for row in r.fetchall():
log.msg(row.sql)
raise e
def do_cleardb(self, s):
'''
cleardb
Clears out the main database (not the settings db). This should only
be done while dofler is not running!
'''
from sqlalchemy.engine import reflection
from sqlalchemy.schema import (MetaData, Table, DropTable,
ForeignKeyConstraint, DropConstraint)
conn = db.engine.connect()
trans = conn.begin()
inspector = reflection.Inspector.from_engine(db.engine)
metadata = MetaData(db.engine)
metadata.reflect()
metadata.drop_all()
trans.commit()
conn.close()
vuln_db = '/opt/pvs/var/pvs/db/reports.db'
if os.path.exists(vuln_db):
os.system('service pvs stop')
os.remove(vuln_db)
os.system('service pvs start')
def do_cleardb(self, s):
'''
cleardb
Clears out the main database (not the settings db). This should only
be done while dofler is not running!
'''
from sqlalchemy.engine import reflection
from sqlalchemy.schema import (MetaData, Table, DropTable,
ForeignKeyConstraint, DropConstraint)
conn = db.engine.connect()
trans = conn.begin()
inspector = reflection.Inspector.from_engine(db.engine)
metadata = MetaData(db.engine)
metadata.reflect()
metadata.drop_all()
trans.commit()
conn.close()
vuln_db = '/opt/pvs/var/pvs/db/reports.db'
if os.path.exists(vuln_db):
os.system('service pvs stop')
os.remove(vuln_db)
os.system('service pvs start')
class IndexTime(NcBase,Base):
# __tablename__ = 'nc_index_time'
dataset_id = Column(ForeignKey(Dataset.id))
index = Column(Integer)
lower = Column(DateTime)
value = Column(DateTime)
upper = Column(DateTime)
@declared_attr
def dataset(cls):
return(relationship(Dataset,backref=cls.__tablename__))
class IndexSpatial(IndexBase,Base):
# __tablename__ = 'nc_index_spatial'
# id = Column(Integer,primary_key=True)
# dataset_id = Column(ForeignKey(Dataset.id))
row = Column(Integer)
col = Column(Integer)
geom = GeometryColumn(Polygon)
centroid = GeometryColumn(Point)
# dataset = relationship(Dataset,backref=__tablename__)
GeometryDDL(IndexSpatial.__table__)
try:
metadata.drop_all()
finally:
metadata.create_all()
def analyze(data,drop=False,load=False):
engine = create_engine('sqlite:////home/bkoziol/tmp/profiling.sqlite')
metadata = MetaData(bind=engine)
Base = declarative_base(metadata=metadata)
Session = sessionmaker(bind=engine)
class SqlBase(object):
@classmethod
def get_or_create(cls,s,kwds,commit=False):
qq = s.query(cls).filter_by(**kwds)
try:
obj = qq.one()
except NoResultFound:
obj = cls(**kwds)
s.add(obj)
if commit: s.commit()
return(obj)
class Scenario(SqlBase,Base):
__tablename__ = 'scenario'
sid = Column(Integer,primary_key=True)
name = Column(String,nullable=False)
class Function(SqlBase,Base):
__tablename__ = 'function'
fid = Column(Integer,primary_key=True)
name = Column(String,nullable=False)
class FileName(SqlBase,Base):
__tablename__ = 'filename'
fnid = Column(Integer,primary_key=True)
name = Column(String,nullable=False)
class Profile(SqlBase,Base):
__tablename__ = 'profile'
id = Column(Integer,primary_key=True)
sid = Column(Integer,ForeignKey(Scenario.sid))
fid = Column(Integer,ForeignKey(Function.fid))
fnid = Column(Integer,ForeignKey(FileName.fnid),nullable=True)
ncalls = Column(Integer,nullable=False)
tottime = Column(Float,nullable=False)
percall = Column(Float,nullable=False)
cumtime = Column(Float,nullable=False)
filename = relationship(FileName)
scenario = relationship(Scenario)
function = relationship(Function)
def report(self,total):
msg = [self.scenario.name]
try:
msg.append(self.filename.name)
except AttributeError:
msg.append('')
msg.append(self.function.name)
msg.append(str(self.tottime))
msg.append(str(self.tottime/float(total)))
msg.append(str(total))
return(','.join(msg))
@staticmethod
def report_headers():
return('scenario,filename,function,tottime,perctime,exetime')
if load:
if drop: metadata.drop_all(checkfirst=True)
metadata.create_all(checkfirst=True)
s = Session()
for kwds in data:
with open(os.path.join('/home/bkoziol/tmp',kwds['name']+'.txt'),'r') as out:
txt = out.read()
profiles = re.split('finished ::.*',txt)
profiles = profiles[0:-1]
for profile in profiles:
profile = profile.strip()
scenario_name = re.match('starting :: (.*)',profile).group(1)
scenario = Scenario.get_or_create(s,dict(name=scenario_name))
table = re.match('.*lineno\(function\)(.*)',profile,flags=re.DOTALL).group(1).strip()
lines = re.split('\n',table)
for line in lines:
line = line.strip()
# print line
elements = re.split(' {2,}',line)
if '{' in line and '}' in line:
filename = None
else:
try:
filename_name = re.match('.* (.*):.*',elements[4]).group(1)
except:
import ipdb;ipdb.set_trace()
filename = FileName.get_or_create(s,dict(name=filename_name))
rm = re.match('.*\((.*)\)|.*{(.*)}',elements[4])
if rm.group(1) is None:
function_name = rm.group(2)
else:
function_name = rm.group(1)
function = Function.get_or_create(s,dict(name=function_name))
obj = Profile()
obj.ncalls = elements[0]
obj.tottime = elements[1]
obj.percall = elements[2]
obj.cumtime = elements[3]
obj.filename = filename
obj.scenario = scenario
obj.function = function
s.add(obj)
s.commit()
else:
s = Session()
print(Profile.report_headers())
for scenario in s.query(Scenario):
## get the total time
total = s.query(func.sum(Profile.tottime)).filter_by(scenario=scenario)
total = total.one()[0]
## get the top ten time things
top = s.query(Profile).filter_by(scenario=scenario)
top = top.order_by(Profile.tottime.desc())
top = top.limit(10)
for obj in top:
print obj.report(total)
import ipdb;ipdb.set_trace()
def __thd_clean_database(self, conn):
# In general it's nearly impossible to do "bullet proof" database
# cleanup with SQLAlchemy that will work on a range of databases
# and they configurations.
#
# Following approaches were considered.
#
# 1. Drop Buildbot Model schema:
#
# model.Model.metadata.drop_all(bind=conn, checkfirst=True)
#
# Dropping schema from model is correct and working operation only
# if database schema is exactly corresponds to the model schema.
#
# If it is not (e.g. migration script failed or migration results in
# old version of model), then some tables outside model schema may be
# present, which may reference tables in the model schema.
# In this case either dropping model schema will fail (if database
# enforces referential integrity, e.g. PostgreSQL), or
# dropping left tables in the code below will fail (if database allows
# removing of tables on which other tables have references,
# e.g. SQLite).
#
# 2. Introspect database contents and drop found tables.
#
# meta = MetaData(bind=conn)
# meta.reflect()
# meta.drop_all()
#
# May fail if schema contains reference cycles (and Buildbot schema
# has them). Reflection looses metadata about how reference cycles
# can be teared up (e.g. use_alter=True).
# Introspection may fail if schema has invalid references
# (e.g. possible in SQLite).
#
# 3. What is actually needed here is accurate code for each engine
# and each engine configuration that will drop all tables,
# indexes, constraints, etc in proper order or in a proper way
# (using tables alternation, or DROP TABLE ... CASCADE, etc).
#
# Conclusion: use approach 2 with manually teared apart known
# reference cycles.
# pylint: disable=too-many-nested-blocks
try:
meta = MetaData(bind=conn)
# Reflect database contents. May fail, e.g. if table references
# non-existent table in SQLite.
meta.reflect()
# Table.foreign_key_constraints introduced in SQLAlchemy 1.0.
if sa_version()[:2] >= (1, 0):
# Restore `use_alter` settings to break known reference cycles.
# Main goal of this part is to remove SQLAlchemy warning
# about reference cycle.
# Looks like it's OK to do it only with SQLAlchemy >= 1.0.0,
# since it's not issued in SQLAlchemy == 0.8.0
# List of reference links (table_name, ref_table_name) that
# should be broken by adding use_alter=True.
table_referenced_table_links = [('buildsets', 'builds'),
('builds', 'buildrequests')]
for table_name, ref_table_name in table_referenced_table_links:
if table_name in meta.tables:
table = meta.tables[table_name]
for fkc in table.foreign_key_constraints:
if fkc.referred_table.name == ref_table_name:
fkc.use_alter = True
# Drop all reflected tables and indices. May fail, e.g. if
# SQLAlchemy wouldn't be able to break circular references.
# Sqlalchemy fk support with sqlite is not yet perfect, so we must deactivate fk during that
# operation, even though we made our possible to use use_alter
with withoutSqliteForeignKeys(conn.engine, conn):
meta.drop_all()
except Exception:
# sometimes this goes badly wrong; being able to see the schema
# can be a big help
if conn.engine.dialect.name == 'sqlite':
r = conn.execute("select sql from sqlite_master "
"where type='table'")
log.msg("Current schema:")
for row in r.fetchall():
log.msg(row.sql)
raise
class BaseTestCase(unittest.TestCase):
def setUp(self):
engine = create_engine('postgresql://postgres@localhost/pypet')
self.metadata = MetaData(bind=engine)
self.store_table = Table('store', self.metadata,
Column('store_id', types.Integer, primary_key=True),
Column('store_name', types.String),
Column('country_id', types.Integer,
ForeignKey('country.country_id')))
self.country_table = Table('country', self.metadata,
Column('country_id', types.Integer, primary_key=True),
Column('country_name', types.String),
Column('region_id', types.Integer,
ForeignKey('region.region_id')))
self.region_table = Table('region', self.metadata,
Column('region_id', types.Integer, primary_key=True),
Column('region_name', types.String))
self.product_table = Table('product', self.metadata,
Column('product_id', types.Integer, primary_key=True),
Column('product_name', types.String),
Column('product_category_id', types.Integer,
ForeignKey('product_category.product_category_id')))
self.product_category_table = Table('product_category', self.metadata,
Column('product_category_id', types.Integer, primary_key=True),
Column('product_category_name', types.String))
self.facts_table = Table('facts_table', self.metadata,
Column('store_id', types.Integer,
ForeignKey('store.store_id')),
Column('date', types.Date),
Column('product_id', types.Integer,
ForeignKey('product.product_id')),
Column('price', types.Float),
Column('qty', types.Integer))
agg_name = ('agg_time_month_product_product_store_store'
'_Unit Price_Quantity')
self.agg_by_month_table = Table(agg_name,
self.metadata,
Column('store_store', types.Integer,
ForeignKey('store.store_id')),
Column('time_month', types.Date),
Column('product_product', types.Integer,
ForeignKey('product.product_id')),
Column('Unit Price', types.Float),
Column('Quantity', types.Integer),
Column('fact_count', types.Integer))
agg_name = ('agg_time_year_store_country_product_product'
'_Unit Price_Quantity')
self.agg_by_year_country_table = Table(agg_name,
self.metadata,
Column('store_country', types.Integer,
ForeignKey('country.country_id')),
Column('time_year', types.Date),
Column('product_product', types.Integer,
ForeignKey('product.product_id')),
Column('Unit Price', types.Float),
Column('Quantity', types.Integer),
Column('fact_count', types.Integer))
self.metadata.create_all()
self.store_dim = Dimension('store', [
Hierarchy('default', [
Level('region', self.region_table.c.region_id,
self.region_table.c.region_name),
Level('country', self.country_table.c.country_id,
self.country_table.c.country_name),
Level('store', self.store_table.c.store_id,
self.store_table.c.store_name)])])
self.product_dim = Dimension('product', [
Hierarchy('default', [
Level('category',
self.product_category_table.c.product_category_id,
self.product_category_table.c
.product_category_name),
Level('product', self.product_table.c.product_id,
self.product_table.c.product_name)])])
self.time_dim = TimeDimension('time', self.facts_table.c.date,
['year', 'month', 'day'])
unit_price = Measure('Unit Price', self.facts_table.c.price,
aggregates.avg)
quantity = Measure('Quantity', self.facts_table.c.qty, aggregates.sum)
price = ((unit_price.aggregate_with(None) *
quantity.aggregate_with(None))
.aggregate_with(aggregates.sum).label('Price'))
self.cube = Cube(self.metadata, self.facts_table, [self.store_dim,
self.product_dim, self.time_dim], [unit_price, quantity, price],
fact_count_column=self.facts_table.c.qty)
self.region_table.insert({'region_id': 1, 'region_name':
'Europe'}).execute()
self.country_table.insert({'region_id': 1, 'country_name':
'France', 'country_id': 1}).execute()
self.country_table.insert({'region_id': 1, 'country_name':
'Germany', 'country_id': 2}).execute()
self.region_table.insert({'region_id': 2, 'region_name':
'America'}).execute()
self.country_table.insert({'region_id': 2, 'country_name':
'USA', 'country_id': 3}).execute()
self.country_table.insert({'region_id': 2, 'country_name':
'Canada', 'country_id': 4}).execute()
self.store_table.insert({
'store_id': 1,
'store_name': 'ACME.fr',
'country_id': 1}).execute()
self.store_table.insert({
'store_id': 2,
'store_name': 'ACME.de',
'country_id': 2}).execute()
self.store_table.insert({
'store_id': 3,
'store_name': 'Food Mart.fr',
'country_id': 1}).execute()
self.store_table.insert({
'store_id': 4,
'store_name': 'Food Mart.de',
'country_id': 2}).execute()
self.store_table.insert({
'store_id': 5,
'store_name': 'ACME.us',
'country_id': 3}).execute()
self.store_table.insert({
'store_id': 6,
'store_name': 'Food Mart.us',
'country_id': 3}).execute()
self.store_table.insert({
'store_id': 7,
'store_name': 'ACME.ca',
'country_id': 4}).execute()
self.store_table.insert({
'store_id': 8,
'store_name': 'Food Mart.ca',
'country_id': 4}).execute()
self.product_category_table.insert({
'product_category_id': 1,
'product_category_name': 'Vegetables'}).execute()
self.product_category_table.insert({
'product_category_id': 2,
'product_category_name': 'Shoes'}).execute()
self.product_table.insert({
'product_id': 1,
'product_category_id': 1,
'product_name': 'Carrots'}).execute()
self.product_table.insert({
'product_id': 2,
'product_category_id': 1,
'product_name': 'Bananas'}).execute()
self.product_table.insert({
'product_id': 3,
'product_category_id': 2,
'product_name': 'Red shoes'}).execute()
self.product_table.insert({
'product_id': 4,
'product_category_id': 2,
'product_name': 'Green shoes'}).execute()
self.product_table.insert({
'product_id': 5,
'product_category_id': 2,
'product_name': 'Blue shoes'}).execute()
years = cycle([2009, 2010, 2011])
months = cycle([1, 5, 8, 9, 11])
days = cycle([3, 12, 21, 29])
prices = iter(cycle([100, 500, 1000]))
quantities = iter(cycle([1, 5, 1, 2, 3, 20, 8]))
values = iter((date(*value) for value in izip(years, months, days)))
for value in self.product_table.select().with_only_columns([
self.product_table.c.product_id,
self.store_table.c.store_id]).execute():
self.facts_table.insert({
'product_id': value.product_id,
'store_id': value.store_id,
'date': next(values),
'qty': next(quantities),
'price': next(prices)}).execute()
results = (self.facts_table.select().with_only_columns([
(func.sum(self.facts_table.c.price *
self.facts_table.c.qty) /
func.sum(self.facts_table.c.qty))
.label('Unit Price'),
func.sum(self.facts_table.c.qty).label('Quantity'),
func.sum(self.facts_table.c.qty).label('fact_count'),
self.facts_table.c.product_id.label('product_product'),
self.facts_table.c.store_id.label('store_store'),
func.date_trunc('month',
self.facts_table.c.date).label('time_month')])
.group_by(func.date_trunc('month', self.facts_table.c.date),
self.facts_table.c.product_id,
self.facts_table.c.store_id)
.execute())
for res in results:
self.agg_by_month_table.insert().execute(dict(res))
second_agg = (self.facts_table.select().with_only_columns([
(func.sum(self.facts_table.c.price *
self.facts_table.c.qty) /
func.sum(self.facts_table.c.qty))
.label('Unit Price'),
func.sum(self.facts_table.c.qty).label('Quantity'),
func.sum(self.facts_table.c.qty).label('fact_count'),
self.facts_table.c.product_id.label('product_product'),
self.store_table.c.country_id.label('store_country'),
func.date_trunc('year',
self.facts_table.c.date).label('time_year')])
.where(self.facts_table.c.store_id == self.store_table.c.store_id)
.group_by(self.facts_table.c.product_id.label('product_product'),
self.store_table.c.country_id.label('store_country'),
func.date_trunc('year',
self.facts_table.c.date).label('time_year'))
.execute())
for res in second_agg:
self.agg_by_year_country_table.insert().execute(dict(res))
def tearDown(self):
self.metadata.drop_all()
def __thd_clean_database(self, conn):
# In general it's nearly impossible to do "bullet proof" database
# cleanup with SQLAlchemy that will work on a range of databases
# and they configurations.
#
# Following approaches were considered.
#
# 1. Drop Buildbot Model schema:
#
# model.Model.metadata.drop_all(bind=conn, checkfirst=True)
#
# Dropping schema from model is correct and working operation only
# if database schema is exactly corresponds to the model schema.
#
# If it is not (e.g. migration script failed or migration results in
# old version of model), then some tables outside model schema may be
# present, which may reference tables in the model schema.
# In this case either dropping model schema will fail (if database
# enforces referential integrity, e.g. PostgreSQL), or
# dropping left tables in the code below will fail (if database allows
# removing of tables on which other tables have references,
# e.g. SQLite).
#
# 2. Introspect database contents and drop found tables.
#
# meta = MetaData(bind=conn)
# meta.reflect()
# meta.drop_all()
#
# May fail if schema contains reference cycles (and Buildbot schema
# has them). Reflection looses metadata about how reference cycles
# can be teared up (e.g. use_alter=True).
# Introspection may fail if schema has invalid references
# (e.g. possible in SQLite).
#
# 3. What is actually needed here is accurate code for each engine
# and each engine configuration that will drop all tables,
# indexes, constraints, etc in proper order or in a proper way
# (using tables alternation, or DROP TABLE ... CASCADE, etc).
#
# Conclusion: use approach 2 with manually teared apart known
# reference cycles.
try:
meta = MetaData(bind=conn)
# Reflect database contents. May fail, e.g. if table references
# non-existent table in SQLite.
meta.reflect()
# Table.foreign_key_constraints introduced in SQLAlchemy 1.0.
if sa_version()[:2] >= (1, 0):
# Restore `use_alter` settings to break known reference cycles.
# Main goal of this part is to remove SQLAlchemy warning
# about reference cycle.
# Looks like it's OK to do it only with SQLAlchemy >= 1.0.0,
# since it's not issued in SQLAlchemy == 0.8.0
# List of reference links (table_name, ref_table_name) that
# should be broken by adding use_alter=True.
table_referenced_table_links = [
('buildsets', 'builds'), ('builds', 'buildrequests')]
for table_name, ref_table_name in table_referenced_table_links:
if table_name in meta.tables:
table = meta.tables[table_name]
for fkc in table.foreign_key_constraints:
if fkc.referred_table.name == ref_table_name:
fkc.use_alter = True
# Drop all reflected tables and indices. May fail, e.g. if
# SQLAlchemy wouldn't be able to break circular references.
# Sqlalchemy fk support with sqlite is not yet perfect, so we must deactivate fk during that
# operation, even though we made our possible to use use_alter
with withoutSqliteForeignKeys(conn.engine, conn):
meta.drop_all()
except Exception:
# sometimes this goes badly wrong; being able to see the schema
# can be a big help
if conn.engine.dialect.name == 'sqlite':
r = conn.execute("select sql from sqlite_master "
"where type='table'")
log.msg("Current schema:")
for row in r.fetchall():
log.msg(row.sql)
raise
class DbClient:
def __init__(self, db_url=environ.get("DB_URL"), new=False, echo=False):
self.engine = create_engine(db_url, echo=echo)
self.session_maker = sessionmaker(bind=self.engine)
self.metadata = MetaData(bind=self.engine)
spider_loader = spiderloader.SpiderLoader.from_settings(settings())
s_names = spider_loader.list()
self.spiders = tuple(spider_loader.load(name) for name in s_names)
# todo consider wrapping sqlalchemy.exc.OperationalError instead of using new parameter
if new:
create_database(self.engine.url)
self.create_tables(Base)
else:
self.metadata.reflect()
@staticmethod
def current_date():
# finds the latest day based on the mastercard definition
now = datetime.datetime.now(timezone('US/Eastern'))
today = now.date()
if now.hour < 14:
today -= datetime.timedelta(days=1)
return today
@contextmanager
def session_scope(self, commit=True):
"""Provide a transactional scope around a series of operations."""
session = self.session_maker()
try:
yield session
if commit:
session.commit()
except Exception:
session.rollback()
raise
finally:
session.close()
def create_tables(self, base):
base.metadata.create_all(self.engine)
with self.session_scope() as s:
providers = [s.provider for s in self.spiders]
for pid, p_name in enumerate(providers):
s.add(Provider(id=pid + 1, name=p_name))
self.update_currencies(p_name)
# todo differentiate between card currencies and transaction currencies
def missing(self, provider, end=None, num_days=363, currs=None):
with self.session_scope(commit=False) as s:
if not end:
end = self.current_date()
start = end - datetime.timedelta(days=num_days - 1)
spider = next(spider for spider in self.spiders
if spider.provider == provider)
if not currs:
currs = set(spider.fetch_avail_currs().keys())
avail_dates = (end - datetime.timedelta(days=x)
for x in range(num_days))
all_combos = ((x, y, z)
for x, y, z in product(currs, currs, avail_dates)
if x != y)
# noinspection PyUnresolvedReferences
not_missing = set(
s.query(Rate.card_code, Rate.trans_code, Rate.date).filter(
Rate.provider.has(name=provider)).filter(
Rate.date <= end).filter(Rate.date >= start).filter(
Rate.card_code.in_(currs)).filter(
Rate.trans_code.in_(currs)))
return (x for x in all_combos if x not in not_missing)
# todo multiprocessing to be implemented
@staticmethod
def combos_to_csv(file_count, results, out_path):
out_path = Path(out_path)
try:
out_path.mkdir()
except FileExistsError:
pass
paths = tuple(out_path / f'{i}.csv' for i in range(file_count))
for p in paths:
p.touch()
fs = []
try:
fs = tuple(p.open(mode='w') for p in paths)
for i, (card_c, trans_c, date) in enumerate(results):
std_date = date.strftime(std_date_fmt)
fs[i % file_count].write(f'{card_c},{trans_c},{std_date}\n')
finally:
for f in fs:
f.close()
def rates_from_csv(self, provider, in_path):
with self.session_scope() as s:
provider_id = (s.query(
Provider.id).filter_by(name=provider).first()[0])
for file in Path(in_path).glob('*.csv'):
print(file)
with file.open() as f:
data = csv.reader(f)
next(data) # skip header row #
rates = [
Rate(card_code=card_code,
trans_code=trans_code,
date=strpdate(date, fmt='%m/%d/%Y'),
provider_id=provider_id,
rate=rate)
for card_code, trans_code, date, rate in data
]
s.bulk_save_objects(rates)
s.commit()
def update_currencies(self, provider):
spider = next(s for s in self.spiders if s.provider == provider)
with self.session_scope() as s:
for alpha_code, name in spider.fetch_avail_currs().items():
try:
s.add(CurrencyCode(alpha_code=alpha_code, name=name))
s.commit()
except IntegrityError:
s.rollback()
def drop_all_tables(self):
self.metadata.drop_all()
def drop_database(self):
drop_database(self.engine.url)
def db_session():
metadata = MetaData(ssms.app.engine)
metadata.reflect()
session = ssms.app.Session()
yield session
metadata.drop_all()
class IndexTime(NcBase, Base):
# __tablename__ = 'nc_index_time'
dataset_id = Column(ForeignKey(Dataset.id))
index = Column(Integer)
lower = Column(DateTime)
value = Column(DateTime)
upper = Column(DateTime)
@declared_attr
def dataset(cls):
return (relationship(Dataset, backref=cls.__tablename__))
class IndexSpatial(IndexBase, Base):
# __tablename__ = 'nc_index_spatial'
# id = Column(Integer,primary_key=True)
# dataset_id = Column(ForeignKey(Dataset.id))
row = Column(Integer)
col = Column(Integer)
geom = GeometryColumn(Polygon)
centroid = GeometryColumn(Point)
# dataset = relationship(Dataset,backref=__tablename__)
GeometryDDL(IndexSpatial.__table__)
try:
metadata.drop_all()
finally:
metadata.create_all()
