def test_mapper_args_deferred(self):
"""test that __mapper_args__ is not called until *after*
table reflection"""
class User(DeferredReflection, fixtures.ComparableEntity, Base):
__tablename__ = "users"
@declared_attr
def __mapper_args__(cls):
return {"primary_key": cls.__table__.c.id}
DeferredReflection.prepare(testing.db)
with fixture_session() as sess:
sess.add_all([
User(name="G"),
User(name="Q"),
User(name="A"),
User(name="C"),
])
sess.commit()
eq_(
sess.query(User).order_by(User.name).all(),
[
User(name="A"),
User(name="C"),
User(name="G"),
User(name="Q"),
],
)
def _initialize_engine(self) -> Optional[Engine]:
if self.is_disabled():
logger.info(
f"Skipping {self} connection due to db_connection_attempts=={master_config.db_connection_attempts}"
)
return None
engine = create_engine(
"mssql+pyodbc://",
creator=self._initialize_connection,
echo=False, # Disable verbose logging of all SQL queries
fast_executemany=True, # Improve insert performance,
# see also https://github.com/mkleehammer/pyodbc/wiki/Features-beyond-the-DB-API#fast_executemany
pool_pre_ping=
True, # Always check status of database connections before using them,
# see also https://docs.sqlalchemy.org/en/13/core/pooling.html#disconnect-handling-pessimistic
)
try:
DeferredReflection.prepare(engine) # type: ignore
except NoSuchTableError as error:
message = (
f"Could not find expected table in {self}: {error}. "
f"Did you run the 'setup-database {self._database_type.name}' command to setup {self}?"
)
if self._ignore_missing_tables:
logger.debug(message)
else:
raise DatabaseConnectionFailure(message) from error
self.schema_base_class.metadata.bind = engine
return engine
def __init__(self, *args, **kwargs):
self.engine = engine_from_config(kwargs, prefix="sqlalchemy.")
# Tries to call the next object's __init__ in the inheritance
# If it throws an TypeError than we can assume the parent object is
# object or another object that doesn't accept *args and **kwargs. This
# is a bit of a draw back but works fine in this case.
try:
super(SQLAlchemyBase, self).__init__(*args, **kwargs)
except TypeError:
pass
# prepare expects the tables to exist in the database already
# this is kind of a hack. I'll need to think of a better way
# later on
try:
DeferredReflection.prepare(self.engine)
except NoSuchTableError:
self.init_db()
DeferredReflection.prepare(self.engine)
self._session = scoped_session(sessionmaker(autocommit=False,
autoflush=True,
expire_on_commit=False,
bind=self.engine))
Base.query = self._session.query_property()
def try_create_connection(
connection_string,
wait_for_db,
logger,
echo: bool = False,
reflections: bool = True) -> Tuple[Connection, Engine]:
engine = create_engine(connection_string, echo=echo)
if reflections:
DeferredReflection.prepare(engine)
if wait_for_db == 0:
max_wait = float('inf')
else:
max_wait = time.clock_gettime(time.CLOCK_MONOTONIC) + wait_for_db
for timeout in chain([1, 2, 5, 10, 30], repeat(60)):
try:
conn = engine.connect()
return conn, engine
except OperationalError:
# Important: Use %r to print the URL, passwords are hidden by the
# __repr__ of sqlalchemy.engine.URL
logger.warn("Could not connect to database %r", engine.url)
timeout = min(timeout,
max_wait - time.clock_gettime(time.CLOCK_MONOTONIC))
if timeout > 0:
logger.info("Waiting for %d seconds", timeout)
time.sleep(timeout)
else:
raise
def __init__(self):
try:
sqlalchemy.create_engine(cfg.pg_dbstring)
except sqlalchemy.exc.ArgumentError:
pass
self.engine = sqlalchemy.create_engine(cfg.pg_dbstring)
DeferredReflection.prepare(self.engine)
session_maker = sessionmaker(bind=self.engine)
self.session = session_maker()
def test_pk_fk(self):
class B(DeferredReflection, fixtures.ComparableEntity, Base):
__tablename__ = "b"
a = relationship("A")
class A(DeferredReflection, fixtures.ComparableEntity, Base):
__tablename__ = "a"
DeferredReflection.prepare(testing.db)
def test_basic_deferred(self):
class User(DeferredReflection, fixtures.ComparableEntity, Base):
__tablename__ = "users"
addresses = relationship("Address", backref="user")
class Address(DeferredReflection, fixtures.ComparableEntity, Base):
__tablename__ = "addresses"
DeferredReflection.prepare(testing.db)
self._roundtrip()
def lazy_reflection():
if not getattr(lazy_reflection, 'reflected', False):
try:
DeferredReflection.prepare(engine)
setattr(lazy_reflection, 'reflected', True)
except OperationalError as ex:
FlaskApp().app.logger.error(
'Cannot connect with database {}'.format(ex.args))
return False
return True
def storage_setup(self):
sql_cfg = settings.DATABASE['default']
dsn = '%(DIALECT)s+%(DRIVER)s://%(USER)s:%(PASS)s@%(HOST)s:%(PORT)s/%(NAME)s' % sql_cfg
sql_engine = create_engine(dsn)
## stop running syncdb at startup after development.
storage.sql.Base.metadata.create_all(sql_engine)
DeferredReflection.prepare(sql_engine)
Session = sessionmaker(bind=sql_engine)
self._sql = Session()
def test_string_resolution(self):
class User(DeferredReflection, fixtures.ComparableEntity, Base):
__tablename__ = "users"
items = relationship("Item", secondary="user_items")
class Item(DeferredReflection, fixtures.ComparableEntity, Base):
__tablename__ = "items"
DeferredReflection.prepare(testing.db)
self._roundtrip()
def test_redefine_fk_double(self):
class User(DeferredReflection, fixtures.ComparableEntity, Base):
__tablename__ = "users"
addresses = relationship("Address", backref="user")
class Address(DeferredReflection, fixtures.ComparableEntity, Base):
__tablename__ = "addresses"
user_id = Column(Integer, ForeignKey("users.id"))
DeferredReflection.prepare(testing.db)
self._roundtrip()
def storage_setup(self):
sql_cfg = settings.DATABASE['default']
dsn = '%(DIALECT)s+%(DRIVER)s://%(USER)s:%(PASS)s@%(HOST)s:%(PORT)s/%(NAME)s' % sql_cfg
sql_engine = create_engine(dsn)
## stop running syncdb at startup after development.
storage.sql.Base.metadata.create_all(sql_engine)
DeferredReflection.prepare(sql_engine)
Session = sessionmaker(bind=sql_engine)
self._sql = Session()
def init_helper(fp, base, session, deferred=False, tables=None):
"""Creates the database based on the given path"""
if fp == ':memory':
fp = ''
if fp:
fp = '/' + os.path.realpath(fp).replace(os.sep, os.sep * 2)
engine = create_engine('sqlite://{}'.format(fp))
if deferred:
DeferredReflection.prepare(engine)
base.metadata.bind = engine
base.metadata.create_all(tables=tables)
session.configure(bind=engine)
def test_cls_not_strong_ref(self):
class User(DeferredReflection, fixtures.ComparableEntity, Base):
__tablename__ = "users"
class Address(DeferredReflection, fixtures.ComparableEntity, Base):
__tablename__ = "addresses"
eq_(len(_DeferredMapperConfig._configs), 2)
del Address
gc_collect()
eq_(len(_DeferredMapperConfig._configs), 1)
DeferredReflection.prepare(testing.db)
assert not _DeferredMapperConfig._configs
def test_basic(self):
class Foo(DeferredReflection, fixtures.ComparableEntity, Base):
__tablename__ = "foo"
__mapper_args__ = {
"polymorphic_on": "type",
"polymorphic_identity": "foo",
}
class Bar(Foo):
__mapper_args__ = {"polymorphic_identity": "bar"}
DeferredReflection.prepare(testing.db)
self._roundtrip()
def test_add_subclass_column(self):
class Foo(DeferredReflection, fixtures.ComparableEntity, Base):
__tablename__ = "foo"
__mapper_args__ = {
"polymorphic_on": "type",
"polymorphic_identity": "foo",
}
class Bar(Foo):
__mapper_args__ = {"polymorphic_identity": "bar"}
bar_data = Column(String(30))
DeferredReflection.prepare(testing.db)
self._roundtrip()
def main(global_config, **settings):
""" This function returns a Pyramid WSGI application.
"""
engine = engine_from_config(settings, 'sqlalchemy.')
DBSession.configure(bind=engine)
Base.metadata.bind = engine
DeferredReflection.prepare(engine)
config = Configurator(settings=settings)
config.include('pyramid_chameleon')
config.include('cornice')
config.add_static_view('static', 'static', cache_max_age=3600)
config.add_route('home', '/')
config.scan()
return config.make_wsgi_app()
def test_add_pk_column(self):
class Foo(DeferredReflection, fixtures.ComparableEntity, Base):
__tablename__ = "foo"
__mapper_args__ = {
"polymorphic_on": "type",
"polymorphic_identity": "foo",
}
id = Column(Integer, primary_key=True)
class Bar(Foo):
__mapper_args__ = {"polymorphic_identity": "bar"}
DeferredReflection.prepare(testing.db)
self._roundtrip()
def _sql(self):
if not self.__sql:
sql_cfg = settings.DATABASE['default']
dsn = '%(DIALECT)s+%(DRIVER)s://%(USER)s:%(PASS)s@%(HOST)s:%(PORT)s/%(NAME)s' % sql_cfg
if sql_cfg['DIALECT'] == 'mysql':
dsn += '?charset=utf8'
sql_engine = create_engine(dsn)
elif sql_cfg['DIALECT'] == 'postgresql':
sql_engine = create_engine(dsn, client_encoding='utf8')
storage.sql.Base.metadata.create_all(sql_engine)
DeferredReflection.prepare(sql_engine)
Session = sessionmaker(
bind=sql_engine) #, autoflush=False) #, autocommit=True)
self.__sql = Session()
return self.__sql
def _sql(self):
if not self.__sql:
sql_cfg = settings.DATABASE['default']
dsn = '%(DIALECT)s+%(DRIVER)s://%(USER)s:%(PASS)s@%(HOST)s:%(PORT)s/%(NAME)s' % sql_cfg
if sql_cfg['DIALECT'] == 'mysql':
dsn += '?charset=utf8'
sql_engine = create_engine(dsn)
elif sql_cfg['DIALECT'] == 'postgresql':
sql_engine = create_engine(dsn, client_encoding='utf8')
storage.sql.Base.metadata.create_all(sql_engine)
DeferredReflection.prepare(sql_engine)
Session = sessionmaker(bind=sql_engine) #, autoflush=False) #, autocommit=True)
self.__sql = Session()
return self.__sql
def init_db(conn_str):
""" Initialize a connection to the database
:param conn_str: SQLAlchemy database URL
:return:
"""
engine = create_engine(conn_str)
_DBSession.bind = engine
Base.metadata.bind = engine
# we can reflect it ourselves from a database, using options
# such as 'only' to limit what tables we look at...
metadata = MetaData()
metadata.reflect(engine, only=['domain', 'mailbox', 'alias'])
# calling prepare() just sets up mapped classes and relationships.
DeferredReflection.prepare(engine)
Base.prepare(engine)
def setup():
global engine, connection, _setup_stack
if _setup_stack > 0:
return
try:
uri = os.environ['PYCROFT_DB_URI']
except KeyError:
raise RuntimeError("Environment variable PYCROFT_DB_URI must be "
"set to an SQLalchemy connection string.")
engine = create_engine(uri, poolclass=SingletonThreadPool)
connection = engine.connect()
drop_db_model(connection)
create_db_model(connection)
DeferredReflection.prepare(engine)
_setup_stack += 1
def main():
DeferredReflection.prepare(engine) # Autoload the tables from DB schema
try:
db_session.execute(
Device.__table__.update().values({'query_status': 'queue'})
)
db_session.commit()
except ProgrammingError:
db_session.rollback()
with Pool() as pool:
pool.map(
query_device,
db_session.query(
Device.id
).filter_by(
is_active=True
),
chunksize=1,
)
app.config.from_object('config.Config')
# prepare controller
for pack in filter(lambda item: re.search("\.py$", item),
os.listdir(controller.__path__[0])):
data = __import__(inspect.getmodulename("controller." + pack),
fromlist=['controller'])
for view in filter(lambda item: (re.search("^(?!Flask).*View$", item[0])),
inspect.getmembers(data, inspect.isclass)):
view[1].register(app)
# prepare database and model
engine = create_engine(app.config['DATABASE'])
Session = scoped_session(sessionmaker(bind=engine))
AppModel._set_session(Session)
DeferredReflection.prepare(engine)
mongo = MongoClient()
# for global action
@app.before_request
def before_request():
if (request.path == '/users/' and request.method == "POST"):
return
user = User.find_by(token=request.headers.get('Authorized-Token'))
if not user:
abort(401)
setattr(g, 'user', user)
setattr(g, 'mongo', MongoClient())
def _create_scheduling_row(self, parset_property):
""" """
# Link to receivers
receivers_on = parset_property.get("hd_receivers", [])
receivers_on += parset_property.get("nri_receivers", [])
if parset_property.get("xst_userfile", False):
# Add the xst option, which is not a proper receiver
receivers_on.append("xst")
if not np.all(np.isin(receivers_on, RECEIVERS)):
log.warning(
f"One of the receiver listed ({receivers_on}) does not belong to the predefined list ({RECEIVERS})."
)
receivers = self.session.query(ReceiverTable).filter(
ReceiverTable.name.in_(receivers_on)).all()
# scheduling_row = SchedulingTable(
# name=parset_property['name'],
# contact_name=parset_property['contactName'],
# contact_email=parset_property['contactEmail'],
# key_project_code=parset_property['topic'].split(' ', 1)[0],
# key_project_name=parset_property['topic'].split(' ', 1)[1],
# start_time=parset_property['startTime'].datetime,
# stop_time=parset_property['stopTime'].datetime,
# receivers=[ReceiverAssociation(receiver=receiver) for receiver in receivers],
# parset_file=self.parset
# )
scheduling_row = self.session.query(SchedulingTable).filter_by(
fileName=basename(self.parset)).first()
if scheduling_row is None:
username = "******" if parset_property[
"contactName"] == "" else parset_property["contactName"]
# Check if 'username' exists
if inspect(self.engine).has_table("nenufar_users"):
class NenufarUserTable(DeferredReflection, Base):
"""
Fake class for NenuFAR User Table
"""
__tablename__ = 'nenufar_users'
DeferredReflection.prepare(self.engine)
username_entry = self.session.query(
NenufarUserTable).filter_by(username=username).first()
if username_entry is None:
log.warning(
f"Username '{username}' not found in 'nenufar_users' table, skipping it."
)
raise UserNameNotFound(f"'{username}'")
# Sort out the topic
topic = parset_property.get("topic", "ES00 DEBUG")
if topic.lower().strip() == 'maintenance':
topic = "MAINTENANCE"
else:
# We have something like "ES00 DEBUG"
topic = topic.split(" ", 1)[1]
# Create the new row
scheduling_row = SchedulingTable(
name=parset_property['name'],
fileName=basename(self.parset),
path=dirname(self.parset),
startTime=parset_property["startTime"].datetime,
endTime=parset_property["stopTime"].datetime,
state="default_value",
topic=topic,
username=username,
receivers=[
ReceiverAssociation(receiver=receiver)
for receiver in receivers
])
is_new = True
log.debug(
f"Row of table 'scheduling' created for '{scheduling_row.name}'."
)
else:
# Only add the receiver association, the row already exists in scheduling table
[
ReceiverAssociation(receiver=receiver,
scheduling=scheduling_row)
for receiver in receivers
]
is_new = False
log.debug(
f"Row of table 'scheduling' updated for '{scheduling_row.name}'."
)
if "nickel" in receivers_on:
log.debug("Adding the association to NICKEL subbands.")
nickel_subbands = self.session.query(SubBandTable).filter(
SubBandTable.index.in_(
parset_property.get("nri_subbandList", []))).all()
[
SubBandNickelAssociation(subband=sb, scheduling=scheduling_row)
for sb in nickel_subbands
]
return scheduling_row, is_new