class Node(Base):
__tablename__ = 'nodes'
__table_args__ = (
UniqueConstraint('cluster_id', 'hostname',
name='_hostname_cluster_uc'),
)
id = Column(Integer, primary_key=True)
uuid = Column(String(36), nullable=False,
default=lambda: str(uuid.uuid4()), unique=True)
#此节点位于那个集群
cluster_id = Column(Integer, ForeignKey('clusters.id', ondelete='CASCADE'))
group_id = Column(
Integer,
ForeignKey('nodegroups.id', ondelete='SET NULL'),
nullable=True
)
name = Column(Unicode(100))
#节点状态
status = Column(
Enum(*consts.NODE_STATUSES, name='node_status'),
nullable=False,
default=consts.NODE_STATUSES.discover
)
meta = Column(MutableDict.as_mutable(JSON), default={})
mac = Column(psql.MACADDR, nullable=False, unique=True)
ip = Column(psql.INET)
hostname = Column(String(255), nullable=False,
default="", server_default="")
manufacturer = Column(Unicode(50))
platform_name = Column(String(150))
kernel_params = Column(Text)
progress = Column(Integer, default=0)
os_platform = Column(String(150))
pending_addition = Column(Boolean, default=False)
pending_deletion = Column(Boolean, default=False)
changes = relationship("ClusterChanges", backref="node")
error_type = Column(String(100))
error_msg = Column(Text)
timestamp = Column(DateTime, nullable=False)
online = Column(Boolean, default=True)
labels = Column(
MutableDict.as_mutable(JSON), nullable=False, server_default='{}')
roles = Column(psql.ARRAY(String(consts.ROLE_NAME_MAX_SIZE)),
default=[], nullable=False, server_default='{}')
pending_roles = Column(psql.ARRAY(String(consts.ROLE_NAME_MAX_SIZE)),
default=[], nullable=False, server_default='{}')
primary_tags = Column(
MutableList.as_mutable(psql.ARRAY(String(consts.ROLE_NAME_MAX_SIZE))),
default=[], nullable=False, server_default='{}')
nic_interfaces = relationship("NodeNICInterface", backref="node",
cascade="all, delete-orphan",
order_by="NodeNICInterface.name")
bond_interfaces = relationship("NodeBondInterface", backref="node",
cascade="all, delete-orphan",
order_by="NodeBondInterface.name")
# hash function from raw node agent request data - for caching purposes
agent_checksum = Column(String(40), nullable=True)
ip_addrs = relationship("IPAddr", viewonly=True)
replaced_deployment_info = Column(MutableList.as_mutable(JSON), default=[])
replaced_provisioning_info = Column(
MutableDict.as_mutable(JSON), default={})
network_template = Column(MutableDict.as_mutable(JSON), default=None,
server_default=None, nullable=True)
extensions = Column(psql.ARRAY(String(consts.EXTENSION_NAME_MAX_SIZE)),
default=[], nullable=False, server_default='{}')
vms_conf = Column(MutableList.as_mutable(JSON),
default=[], server_default='[]', nullable=False)
attributes = Column(
MutableDict.as_mutable(JSON),
default={}, server_default='{}', nullable=False)
@property
def interfaces(self):
return self.nic_interfaces + self.bond_interfaces
@property
def uid(self):
return str(self.id)
@property
def offline(self):
return not self.online
@property
def network_data(self):
# TODO(enchantner): move to object
from nailgun.extensions.network_manager.manager import NetworkManager
return NetworkManager.get_node_networks(self)
@property
def needs_reprovision(self):
return self.status == 'error' and self.error_type == 'provision' and \
not self.pending_deletion
@property
def needs_redeploy(self):
return (
self.status in [
consts.NODE_STATUSES.error,
consts.NODE_STATUSES.provisioned,
consts.NODE_STATUSES.stopped
] or len(self.pending_roles)) and not self.pending_deletion
@property
def needs_redeletion(self):
return self.status == 'error' and self.error_type == 'deletion'
@property
def human_readable_name(self):
return self.name or self.mac
@property
def full_name(self):
return u'%s (id=%s, mac=%s)' % (self.name, self.id, self.mac)
@property
def all_roles(self):
"""Returns all roles, self.roles and self.pending_roles."""
return set(self.pending_roles + self.roles)
def _check_interface_has_required_params(self, iface):
return bool(iface.get('name') and iface.get('mac'))
def _clean_iface(self, iface):
# cleaning up unnecessary fields - set to None if bad
for param in ["max_speed", "current_speed"]:
val = iface.get(param)
if not (isinstance(val, int) and val >= 0):
val = None
iface[param] = val
return iface
def update_meta(self, data):
# helper for basic checking meta before updation
result = []
if "interfaces" in data:
for iface in data["interfaces"]:
if not self._check_interface_has_required_params(iface):
logger.warning(
"Invalid interface data: {0}. "
"Interfaces are not updated.".format(iface)
)
data["interfaces"] = self.meta.get("interfaces")
self.meta = data
return
result.append(self._clean_iface(iface))
data["interfaces"] = result
self.meta = data
def create_meta(self, data):
# helper for basic checking meta before creation
result = []
if "interfaces" in data:
for iface in data["interfaces"]:
if not self._check_interface_has_required_params(iface):
logger.warning(
"Invalid interface data: {0}. "
"Skipping interface.".format(iface)
)
continue
result.append(self._clean_iface(iface))
data["interfaces"] = result
self.meta = data
Column('extent', Geometry, nullable=True),
Column('name', String, nullable=False),
Column('srid', Integer, nullable=False),
Column('table_name', String, nullable=False),
Column('file_name', String, nullable=False),
Column('variable_id', Integer, ForeignKey('variable.id'), nullable=False),
Column('provider_id', Integer, ForeignKey('provider.id'), nullable=False),
Column('dataformat_id',
Integer,
ForeignKey('dataformat.id'),
nullable=False),
UniqueConstraint('start_time',
'end_time',
'level',
'variable_id',
'provider_id',
'level',
'extent',
'dataformat_id',
name='datagranule_unique_idx'))
mapper(DataGranule,
datagranule,
properties={
'variable': relationship(Variable),
'provider': relationship(Provider),
'dataformat': relationship(DataFormat)
})
rastertile = Table(
'rastertile', metadata, Column('id', Integer, primary_key=True),
class DagRun(Base, LoggingMixin):
"""
DagRun describes an instance of a Dag. It can be created
by the scheduler (for regular runs) or by an external trigger
"""
__tablename__ = "dag_run"
ID_PREFIX = 'scheduled__'
ID_FORMAT_PREFIX = ID_PREFIX + '{0}'
id = Column(Integer, primary_key=True)
dag_id = Column(String(ID_LEN))
execution_date = Column(UtcDateTime, default=timezone.utcnow)
start_date = Column(UtcDateTime, default=timezone.utcnow)
end_date = Column(UtcDateTime)
_state = Column('state', String(50), default=State.RUNNING)
run_id = Column(String(ID_LEN))
external_trigger = Column(Boolean, default=True)
conf = Column(PickleType)
dag = None
__table_args__ = (
Index('dag_id_state', dag_id, _state),
UniqueConstraint('dag_id', 'execution_date'),
UniqueConstraint('dag_id', 'run_id'),
)
def __repr__(self):
return (
'<DagRun {dag_id} @ {execution_date}: {run_id}, '
'externally triggered: {external_trigger}>'
).format(
dag_id=self.dag_id,
execution_date=self.execution_date,
run_id=self.run_id,
external_trigger=self.external_trigger)
def get_state(self):
return self._state
def set_state(self, state):
if self._state != state:
self._state = state
self.end_date = timezone.utcnow() if self._state in State.finished() else None
@declared_attr
def state(self):
return synonym('_state',
descriptor=property(self.get_state, self.set_state))
@classmethod
def id_for_date(cls, date, prefix=ID_FORMAT_PREFIX):
return prefix.format(date.isoformat()[:19])
@provide_session
def refresh_from_db(self, session=None):
"""
Reloads the current dagrun from the database
:param session: database session
"""
DR = DagRun
exec_date = func.cast(self.execution_date, DateTime)
dr = session.query(DR).filter(
DR.dag_id == self.dag_id,
func.cast(DR.execution_date, DateTime) == exec_date,
DR.run_id == self.run_id
).one()
self.id = dr.id
self.state = dr.state
@staticmethod
@provide_session
def find(dag_id=None, run_id=None, execution_date=None,
state=None, external_trigger=None, no_backfills=False,
session=None):
"""
Returns a set of dag runs for the given search criteria.
:param dag_id: the dag_id to find dag runs for
:type dag_id: int, list
:param run_id: defines the the run id for this dag run
:type run_id: str
:param execution_date: the execution date
:type execution_date: datetime.datetime
:param state: the state of the dag run
:type state: str
:param external_trigger: whether this dag run is externally triggered
:type external_trigger: bool
:param no_backfills: return no backfills (True), return all (False).
Defaults to False
:type no_backfills: bool
:param session: database session
:type session: sqlalchemy.orm.session.Session
"""
DR = DagRun
qry = session.query(DR)
if dag_id:
qry = qry.filter(DR.dag_id == dag_id)
if run_id:
qry = qry.filter(DR.run_id == run_id)
if execution_date:
if isinstance(execution_date, list):
qry = qry.filter(DR.execution_date.in_(execution_date))
else:
qry = qry.filter(DR.execution_date == execution_date)
if state:
qry = qry.filter(DR.state == state)
if external_trigger is not None:
qry = qry.filter(DR.external_trigger == external_trigger)
if no_backfills:
# in order to prevent a circular dependency
from airflow.jobs import BackfillJob
qry = qry.filter(DR.run_id.notlike(BackfillJob.ID_PREFIX + '%'))
dr = qry.order_by(DR.execution_date).all()
return dr
@provide_session
def get_task_instances(self, state=None, session=None):
"""
Returns the task instances for this dag run
"""
from airflow.models.taskinstance import TaskInstance # Avoid circular import
tis = session.query(TaskInstance).filter(
TaskInstance.dag_id == self.dag_id,
TaskInstance.execution_date == self.execution_date,
)
if state:
if isinstance(state, six.string_types):
tis = tis.filter(TaskInstance.state == state)
else:
# this is required to deal with NULL values
if None in state:
tis = tis.filter(
or_(TaskInstance.state.in_(state),
TaskInstance.state.is_(None))
)
else:
tis = tis.filter(TaskInstance.state.in_(state))
if self.dag and self.dag.partial:
tis = tis.filter(TaskInstance.task_id.in_(self.dag.task_ids))
return tis.all()
@provide_session
def get_task_instance(self, task_id, session=None):
"""
Returns the task instance specified by task_id for this dag run
:param task_id: the task id
"""
from airflow.models.taskinstance import TaskInstance # Avoid circular import
TI = TaskInstance
ti = session.query(TI).filter(
TI.dag_id == self.dag_id,
TI.execution_date == self.execution_date,
TI.task_id == task_id
).first()
return ti
def get_dag(self):
"""
Returns the Dag associated with this DagRun.
:return: DAG
"""
if not self.dag:
raise AirflowException("The DAG (.dag) for {} needs to be set"
.format(self))
return self.dag
@provide_session
def get_previous_dagrun(self, state=None, session=None):
# type: (Optional[str], Optional[Session]) -> Optional['DagRun']
"""The previous DagRun, if there is one"""
session = cast(Session, session) # mypy
filters = [
DagRun.dag_id == self.dag_id,
DagRun.execution_date < self.execution_date,
]
if state is not None:
filters.append(DagRun.state == state)
return session.query(DagRun).filter(
*filters
).order_by(
DagRun.execution_date.desc()
).first()
@provide_session
def get_previous_scheduled_dagrun(self, session=None):
"""The previous, SCHEDULED DagRun, if there is one"""
dag = self.get_dag()
return session.query(DagRun).filter(
DagRun.dag_id == self.dag_id,
DagRun.execution_date == dag.previous_schedule(self.execution_date)
).first()
@provide_session
def update_state(self, session=None):
"""
Determines the overall state of the DagRun based on the state
of its TaskInstances.
:return: State
"""
dag = self.get_dag()
tis = self.get_task_instances(session=session)
self.log.debug("Updating state for %s considering %s task(s)", self, len(tis))
for ti in list(tis):
# skip in db?
if ti.state == State.REMOVED:
tis.remove(ti)
else:
ti.task = dag.get_task(ti.task_id)
# pre-calculate
# db is faster
start_dttm = timezone.utcnow()
unfinished_tasks = self.get_task_instances(
state=State.unfinished(),
session=session
)
none_depends_on_past = all(not t.task.depends_on_past for t in unfinished_tasks)
none_task_concurrency = all(t.task.task_concurrency is None
for t in unfinished_tasks)
# small speed up
if unfinished_tasks and none_depends_on_past and none_task_concurrency:
# todo: this can actually get pretty slow: one task costs between 0.01-015s
no_dependencies_met = True
for ut in unfinished_tasks:
# We need to flag upstream and check for changes because upstream
# failures/re-schedules can result in deadlock false positives
old_state = ut.state
deps_met = ut.are_dependencies_met(
dep_context=DepContext(
flag_upstream_failed=True,
ignore_in_retry_period=True,
ignore_in_reschedule_period=True),
session=session)
if deps_met or old_state != ut.current_state(session=session):
no_dependencies_met = False
break
duration = (timezone.utcnow() - start_dttm).total_seconds() * 1000
Stats.timing("dagrun.dependency-check.{}".format(self.dag_id), duration)
leaf_task_ids = {t.task_id for t in dag.leaves}
leaf_tis = [ti for ti in tis if ti.task_id in leaf_task_ids]
# if all roots finished and at least one failed, the run failed
if not unfinished_tasks and any(
leaf_ti.state in {State.FAILED, State.UPSTREAM_FAILED} for leaf_ti in leaf_tis
):
self.log.info('Marking run %s failed', self)
self.set_state(State.FAILED)
dag.handle_callback(self, success=False, reason='task_failure',
session=session)
# if all leafs succeeded and no unfinished tasks, the run succeeded
elif not unfinished_tasks and all(
leaf_ti.state in {State.SUCCESS, State.SKIPPED} for leaf_ti in leaf_tis
):
self.log.info('Marking run %s successful', self)
self.set_state(State.SUCCESS)
dag.handle_callback(self, success=True, reason='success', session=session)
# if *all tasks* are deadlocked, the run failed
elif (unfinished_tasks and none_depends_on_past and
none_task_concurrency and no_dependencies_met):
self.log.info('Deadlock; marking run %s failed', self)
self.set_state(State.FAILED)
dag.handle_callback(self, success=False, reason='all_tasks_deadlocked',
session=session)
# finally, if the roots aren't done, the dag is still running
else:
self.set_state(State.RUNNING)
self._emit_duration_stats_for_finished_state()
# todo: determine we want to use with_for_update to make sure to lock the run
session.merge(self)
session.commit()
return self.state
def _emit_duration_stats_for_finished_state(self):
if self.state == State.RUNNING:
return
duration = (self.end_date - self.start_date)
if self.state is State.SUCCESS:
Stats.timing('dagrun.duration.success.{}'.format(self.dag_id), duration)
elif self.state == State.FAILED:
Stats.timing('dagrun.duration.failed.{}'.format(self.dag_id), duration)
@provide_session
def verify_integrity(self, session=None):
"""
Verifies the DagRun by checking for removed tasks or tasks that are not in the
database yet. It will set state to removed or add the task if required.
"""
from airflow.models.taskinstance import TaskInstance # Avoid circular import
dag = self.get_dag()
tis = self.get_task_instances(session=session)
# check for removed or restored tasks
task_ids = set()
for ti in tis:
task_instance_mutation_hook(ti)
task_ids.add(ti.task_id)
task = None
try:
task = dag.get_task(ti.task_id)
except AirflowException:
if ti.state == State.REMOVED:
pass # ti has already been removed, just ignore it
elif self.state is not State.RUNNING and not dag.partial:
self.log.warning("Failed to get task '{}' for dag '{}'. "
"Marking it as removed.".format(ti, dag))
Stats.incr(
"task_removed_from_dag.{}".format(dag.dag_id), 1, 1)
ti.state = State.REMOVED
is_task_in_dag = task is not None
should_restore_task = is_task_in_dag and ti.state == State.REMOVED
if should_restore_task:
self.log.info("Restoring task '{}' which was previously "
"removed from DAG '{}'".format(ti, dag))
Stats.incr("task_restored_to_dag.{}".format(dag.dag_id), 1, 1)
ti.state = State.NONE
session.merge(ti)
# check for missing tasks
for task in six.itervalues(dag.task_dict):
if task.start_date > self.execution_date and not self.is_backfill:
continue
if task.task_id not in task_ids:
Stats.incr(
"task_instance_created-{}".format(task.__class__.__name__),
1, 1)
ti = TaskInstance(task, self.execution_date)
task_instance_mutation_hook(ti)
session.add(ti)
try:
session.commit()
except IntegrityError as err:
self.log.info(str(err))
self.log.info(
'Hit IntegrityError while creating the TIs for %s - %s',
dag.dag_id, self.execution_date
)
self.log.info('Doing session rollback.')
session.rollback()
@staticmethod
def get_run(session, dag_id, execution_date):
"""
:param dag_id: DAG ID
:type dag_id: unicode
:param execution_date: execution date
:type execution_date: datetime
:return: DagRun corresponding to the given dag_id and execution date
if one exists. None otherwise.
:rtype: airflow.models.DagRun
"""
qry = session.query(DagRun).filter(
DagRun.dag_id == dag_id,
DagRun.external_trigger == False, # noqa
DagRun.execution_date == execution_date,
)
return qry.first()
@property
def is_backfill(self):
from airflow.jobs import BackfillJob
return (
self.run_id is not None and
self.run_id.startswith(BackfillJob.ID_PREFIX)
)
@classmethod
@provide_session
def get_latest_runs(cls, session):
"""Returns the latest DagRun for each DAG. """
subquery = (
session
.query(
cls.dag_id,
func.max(cls.execution_date).label('execution_date'))
.group_by(cls.dag_id)
.subquery()
)
dagruns = (
session
.query(cls)
.join(subquery,
and_(cls.dag_id == subquery.c.dag_id,
cls.execution_date == subquery.c.execution_date))
.all()
)
return dagruns
'''
class ConfigurationSetting(Base, HasFullTableCache):
"""An extra piece of site configuration.
A ConfigurationSetting may be associated with an
ExternalIntegration, a Library, both, or neither.
* The secret used by the circulation manager to sign OAuth bearer
tokens is not associated with an ExternalIntegration or with a
Library.
* The link to a library's privacy policy is associated with the
Library, but not with any particular ExternalIntegration.
* The "website ID" for an Overdrive collection is associated with
an ExternalIntegration (the Overdrive integration), but not with
any particular Library (since multiple libraries might share an
Overdrive collection).
* The "identifier prefix" used to determine which library a patron
is a patron of, is associated with both a Library and an
ExternalIntegration.
"""
__tablename__ = 'configurationsettings'
id = Column(Integer, primary_key=True)
external_integration_id = Column(Integer,
ForeignKey('externalintegrations.id'),
index=True)
library_id = Column(Integer, ForeignKey('libraries.id'), index=True)
key = Column(Unicode, index=True)
_value = Column(Unicode, name="value")
__table_args__ = (UniqueConstraint('external_integration_id', 'library_id',
'key'), )
_cache = HasFullTableCache.RESET
_id_cache = HasFullTableCache.RESET
def __repr__(self):
return u'<ConfigurationSetting: key=%s, ID=%d>' % (self.key, self.id)
@classmethod
def sitewide_secret(cls, _db, key):
"""Find or create a sitewide shared secret.
The value of this setting doesn't matter, only that it's
unique across the site and that it's always available.
"""
secret = ConfigurationSetting.sitewide(_db, key)
if not secret.value:
secret.value = os.urandom(24).encode('hex')
# Commit to get this in the database ASAP.
_db.commit()
return secret.value
@classmethod
def explain(cls, _db, include_secrets=False):
"""Explain all site-wide ConfigurationSettings."""
lines = []
site_wide_settings = []
for setting in _db.query(ConfigurationSetting).filter(
ConfigurationSetting.library == None).filter(
ConfigurationSetting.external_integration == None):
if not include_secrets and setting.key.endswith("_secret"):
continue
site_wide_settings.append(setting)
if site_wide_settings:
lines.append("Site-wide configuration settings:")
lines.append("---------------------------------")
for setting in sorted(site_wide_settings, key=lambda s: s.key):
if setting.value is None:
continue
lines.append("%s='%s'" % (setting.key, setting.value))
return lines
@classmethod
def sitewide(cls, _db, key):
"""Find or create a sitewide ConfigurationSetting."""
return cls.for_library_and_externalintegration(_db, key, None, None)
@classmethod
def for_library(cls, key, library):
"""Find or create a ConfigurationSetting for the given Library."""
_db = Session.object_session(library)
return cls.for_library_and_externalintegration(_db, key, library, None)
@classmethod
def for_externalintegration(cls, key, externalintegration):
"""Find or create a ConfigurationSetting for the given
ExternalIntegration.
"""
_db = Session.object_session(externalintegration)
return cls.for_library_and_externalintegration(_db, key, None,
externalintegration)
@classmethod
def _cache_key(cls, library, external_integration, key):
if library:
library_id = library.id
else:
library_id = None
if external_integration:
external_integration_id = external_integration.id
else:
external_integration_id = None
return (library_id, external_integration_id, key)
def cache_key(self):
return self._cache_key(self.library, self.external_integration,
self.key)
@classmethod
def for_library_and_externalintegration(cls, _db, key, library,
external_integration):
"""Find or create a ConfigurationSetting associated with a Library
and an ExternalIntegration.
"""
def create():
"""Function called when a ConfigurationSetting is not found in cache
and must be created.
"""
return get_one_or_create(_db,
ConfigurationSetting,
library=library,
external_integration=external_integration,
key=key)
# ConfigurationSettings are stored in cache based on their library,
# external integration, and the name of the setting.
cache_key = cls._cache_key(library, external_integration, key)
setting, ignore = cls.by_cache_key(_db, cache_key, create)
return setting
@hybrid_property
def value(self):
"""What's the current value of this configuration setting?
If not present, the value may be inherited from some other
ConfigurationSetting.
"""
if self._value:
# An explicitly set value always takes precedence.
return self._value
elif self.library and self.external_integration:
# This is a library-specific specialization of an
# ExternalIntegration. Treat the value set on the
# ExternalIntegration as a default.
return self.for_externalintegration(
self.key, self.external_integration).value
elif self.library:
# This is a library-specific setting. Treat the site-wide
# value as a default.
_db = Session.object_session(self)
return self.sitewide(_db, self.key).value
return self._value
@value.setter
def value(self, new_value):
if new_value is not None:
new_value = unicode(new_value)
self._value = new_value
@classmethod
def _is_secret(self, key):
"""Should the value of the given key be treated as secret?
This will have to do, in the absence of programmatic ways of
saying that a specific setting should be treated as secret.
"""
return any(key == x or key.startswith('%s_' % x)
or key.endswith('_%s' % x) or ("_%s_" % x) in key
for x in ('secret', 'password'))
@property
def is_secret(self):
"""Should the value of this key be treated as secret?"""
return self._is_secret(self.key)
def value_or_default(self, default):
"""Return the value of this setting. If the value is None,
set it to `default` and return that instead.
"""
if self.value is None:
self.value = default
return self.value
MEANS_YES = set(['true', 't', 'yes', 'y'])
@property
def bool_value(self):
"""Turn the value into a boolean if possible.
:return: A boolean, or None if there is no value.
"""
if self.value:
if self.value.lower() in self.MEANS_YES:
return True
return False
return None
@property
def int_value(self):
"""Turn the value into an int if possible.
:return: An integer, or None if there is no value.
:raise ValueError: If the value cannot be converted to an int.
"""
if self.value:
return int(self.value)
return None
@property
def float_value(self):
"""Turn the value into an float if possible.
:return: A float, or None if there is no value.
:raise ValueError: If the value cannot be converted to a float.
"""
if self.value:
return float(self.value)
return None
@property
def json_value(self):
"""Interpret the value as JSON if possible.
:return: An object, or None if there is no value.
:raise ValueError: If the value cannot be parsed as JSON.
"""
if self.value:
return json.loads(self.value)
return None
# As of this release of the software, this is our best guess as to
# which data sources should have their audiobooks excluded from
# lanes.
EXCLUDED_AUDIO_DATA_SOURCES_DEFAULT = [
DataSourceConstants.OVERDRIVE, DataSourceConstants.AXIS_360,
DataSourceConstants.RB_DIGITAL
]
@classmethod
def excluded_audio_data_sources(cls, _db):
"""List the data sources whose audiobooks should not be published in
feeds, either because this server can't fulfill them or the
expected client can't play them.
Most methods like this go into Configuration, but this one needs
to reference data model objects for its default value.
"""
value = cls.sitewide(
_db, Configuration.EXCLUDED_AUDIO_DATA_SOURCES).json_value
if value is None:
value = cls.EXCLUDED_AUDIO_DATA_SOURCES_DEFAULT
return value
class DETSESSO_table:
# connection string postgres"
internal_connection = Connection()
# create engine and metadata
engine = create_engine(internal_connection.conn_str(),
echo=False,
convert_unicode=True)
metadata = MetaData(engine)
# define tables
detsesso_table = Table(
'detsesso_table',
metadata,
Column('id_det_sesso', Integer, primary_key=True),
Column('sito', Text),
Column('num_individuo', Integer),
Column('glab_grado_imp', Integer),
Column('pmast_grado_imp', Integer),
Column('pnuc_grado_imp', Integer),
Column('pzig_grado_imp', Integer),
Column('arcsop_grado_imp', Integer),
Column('tub_grado_imp', Integer),
Column('pocc_grado_imp', Integer),
Column('inclfr_grado_imp', Integer),
Column('zig_grado_imp', Integer),
Column('msorb_grado_imp', Integer),
Column('glab_valori', Integer),
Column('pmast_valori', Integer),
Column('pnuc_valori', Integer),
Column('pzig_valori', Integer),
Column('arcsop_valori', Integer),
Column('tub_valori', Integer),
Column('pocc_valori', Integer),
Column('inclfr_valori', Integer),
Column('zig_valori', Integer),
Column('msorb_valori', Integer),
Column('palato_grado_imp', Integer),
Column('mfmand_grado_imp', Integer),
Column('mento_grado_imp', Integer),
Column('anmand_grado_imp', Integer),
Column('minf_grado_imp', Integer),
Column('brmont_grado_imp', Integer),
Column('condm_grado_imp', Integer),
Column('palato_valori', Integer),
Column('mfmand_valori', Integer),
Column('mento_valori', Integer),
Column('anmand_valori', Integer),
Column('minf_valori', Integer),
Column('brmont_valori', Integer),
Column('condm_valori', Integer),
Column('sex_cr_tot', Float(2, 3)),
Column('ind_cr_sex', String(100)),
Column('sup_p_I', String(1)),
Column('sup_p_II', String(1)),
Column('sup_p_III', String(1)),
Column('sup_p_sex', String(1)),
Column('in_isch_I', String(1)),
Column('in_isch_II', String(1)),
Column('in_isch_III', String(1)),
Column('in_isch_sex', String(1)),
Column('arco_c_sex', String(1)),
Column('ramo_ip_I', String(1)),
Column('ramo_ip_II', String(1)),
Column('ramo_ip_III', String(1)),
Column('ramo_ip_sex', String(1)),
Column('prop_ip_sex', String(1)),
Column('ind_bac_sex', String(100)),
# explicit/composite unique constraint. 'name' is optional.
UniqueConstraint('sito', 'num_individuo', name='ID_det_sesso_unico'))
metadata.create_all(engine)
class UT_table:
# connection string postgres"
internal_connection = Connection()
# create engine and metadata
engine = create_engine(internal_connection.conn_str(),
echo=False,
convert_unicode=True)
metadata = MetaData(engine)
# define tables
ut_table = Table(
'ut_table',
metadata,
Column('id_ut', Integer, primary_key=True), #0
Column('progetto', String(100)), #1
Column('nr_ut', Integer), #2
Column('ut_letterale', String(100)), #3
Column('def_ut', String(100)), #4
Column('descrizione_ut', Text), #5
Column('interpretazione_ut', String(100)), #6
Column('nazione', String(100)), #7
Column('regione', String(100)), #8
Column('provincia', String(100)), #9
Column('comune', String(100)), #10
Column('frazione', String(100)), #11
Column('localita', String(100)), #12
Column('indirizzo', String(100)), #13
Column('nr_civico', String(100)), #14
Column('carta_topo_igm', String(100)), #15
Column('carta_ctr', String(100)), #16
Column('coord_geografiche', String(100)), #17
Column('coord_piane', String(100)), #18
Column('quota', Float(3, 2)), #19
Column('andamento_terreno_pendenza', String(100)), #20
Column('utilizzo_suolo_vegetazione', String(100)), #21
Column('descrizione_empirica_suolo', Text), #22
Column('descrizione_luogo', Text), #23
Column('metodo_rilievo_e_ricognizione', String(100)), #24
Column('geometria', String(100)), #25
Column('bibliografia', Text), #26
Column('data', String(100)), #27
Column('ora_meteo', String(100)), #28
Column('responsabile', String(100)), #29
Column('dimensioni_ut', String(100)), #30
Column('rep_per_mq', String(100)), #31
Column('rep_datanti', String(100)), #32
Column('periodo_I', String(100)), #33
Column('datazione_I', String(100)), #34
Column('interpretazione_I', String(100)), #35
Column('periodo_II', String(100)), #36
Column('datazione_II', String(100)), #37
Column('interpretazione_II', String(100)), #38
Column('documentazione', Text), #39
Column('enti_tutela_vincoli', String(100)), #40
Column('indagini_preliminari', String(100)), #41
# explicit/composite unique constraint. 'name' is optional.
UniqueConstraint('progetto',
'nr_ut',
'ut_letterale',
name='ID_ut_unico'))
metadata.create_all(engine)
Base = declarative_base(cls=BaseMixin)
group_articles = Table(
'group_articles',
Base.metadata,
Column('article_id',
Integer,
ForeignKey('article.id', ondelete='CASCADE'),
nullable=False),
Column('group_id',
Integer,
ForeignKey('group.id', ondelete='CASCADE'),
nullable=False),
UniqueConstraint('article_id', 'group_id'),
)
group_files = Table(
'group_files',
Base.metadata,
Column('group_id',
Integer,
ForeignKey('group.id', ondelete='CASCADE'),
nullable=False),
Column('file_id',
Integer,
ForeignKey('file.id', ondelete='CASCADE'),
nullable=False),
UniqueConstraint('file_id', 'group_id'),
)
class Result(Base):
''' Data model for a job result. '''
__tablename__ = 'result'
__table_args__ = (UniqueConstraint('tracker_id',
'site_url',
name='tracker_id_site_url'), )
STATUS_TYPES = [(u'f', u'Found'), (u'n', u'Not Found'), (u'e', u'Error')]
id = Column(Integer, primary_key=True)
tracker_id = Column(String(255), nullable=False)
site_name = Column(String(255), nullable=False)
site_url = Column(String(255), nullable=False)
site_id = Column(Integer, nullable=False)
status = Column(ChoiceType(STATUS_TYPES), nullable=False)
image_file_id = Column(Integer,
ForeignKey('file.id', name='fk_image_file'),
nullable=True)
image_file = relationship('File',
lazy='joined',
backref='result',
uselist=False,
cascade='all')
error = Column(String(255), nullable=True)
html = Column(Text, nullable=True)
username = Column(String(255), nullable=False)
created_at = Column(DateTime, nullable=False, default=datetime.utcnow)
user_id = Column(Integer,
ForeignKey('user.id', name='fk_result_user'),
nullable=False)
def __init__(self,
tracker_id,
site_name,
site_url,
site_id,
status,
username,
user_id=None,
image_file_id=None,
thumb=None,
error=None,
html=None):
''' Constructor. '''
self.tracker_id = tracker_id
self.site_id = site_id
self.site_name = site_name
self.site_url = site_url
self.status = status
self.image_file_id = image_file_id
self.thumb = thumb
self.error = error
self.html = html
self.username = username
self.user_id = user_id
def as_dict(self):
''' Return dictionary representation of this result. '''
if self.image_file is not None:
image_file_url = self.image_file.url()
image_file_name = self.image_file.name
else:
image_file_url = None
image_file_name = None
return {
'created_at': self.created_at.isoformat(),
'error': self.error,
'html': self.html,
'id': self.id,
'image_file_id': self.image_file_id,
'image_file_url': image_file_url,
'image_file_name': image_file_name,
'site_id': self.site_id,
'site_name': self.site_name,
'site_url': self.site_url,
'status': self.status.code,
'tracker_id': self.tracker_id,
'username': self.username,
'user_id': self.user_id,
}
from sqlalchemy import Table, Column, MetaData, testing, ForeignKey, UniqueConstraint, \
CheckConstraint
from sqlalchemy.types import Integer, String, Boolean
from sqlalchemy.testing import fixtures
meta = MetaData()
customer_table = Table('customer', meta,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('email', String),
Column('verified', Boolean),
UniqueConstraint('email'))
order_table = Table('order', meta,
Column('id', Integer, primary_key=True),
Column('customer_id', Integer, ForeignKey('customer.id')),
Column('info', String),
Column('status', String, CheckConstraint("status in ('open', 'closed')")))
# Regression test for https://github.com/cockroachdb/cockroach/issues/26993
index_table = Table('index', meta,
Column('index', Integer, primary_key=True))
view_table = Table('view', meta,
Column('view', Integer, primary_key=True))
class IntrospectionTest(fixtures.TestBase):
def teardown_method(self, method):
meta.drop_all(testing.db)
def create_year_table(year):
"""
:type year: int
"""
# inspector = Inspector.from_engine(engine)
# table_names = inspector.get_table_names()
# from pyticas_tetres.db.model import Base
Base = declarative_base(bind=conn.engine)
# faverolles 10/8/2019 NOTE: HERE
tt_table = type(
'TravelTime%d' % year, (Base, ), {
'__tablename__':
'tt_%d' % year,
'id':
Column(Integer, primary_key=True, autoincrement=True),
'route_id':
Column(Integer,
ForeignKey(TTRoute.id, ondelete='CASCADE'),
nullable=False,
index=True),
'route':
relationship(TTRoute, backref=backref('tt_%d' % year)),
'time':
Column(DateTime, nullable=False),
'tt':
Column(Float, nullable=False),
'vmt':
Column(Float, nullable=True),
'speed':
Column(Float, nullable=True),
'vht':
Column(Float, nullable=True),
'dvh':
Column(Float, nullable=True),
'lvmt':
Column(Float, nullable=True),
'sv':
Column(Float, nullable=True),
'_tt_weathers':
relationship('TTWeather%d' % year, lazy='joined'),
'_tt_incidents':
relationship('TTIncident%d' % year, lazy='joined'),
'_tt_workzones':
relationship('TTWorkzone%d' % year, lazy='joined'),
'_tt_specialevents':
relationship('TTSpecialevent%d' % year, lazy='joined'),
'_tt_snowmanagements':
relationship('TTSnowmgmt%d' % year, lazy='joined'),
'__table_args__':
(UniqueConstraint('route_id', 'time', name='_tt_uc_%d' % year), )
})
tt_table.__repr__ = lambda self: \
'<TravelTime%d id="%d" route_id="%d" time="%s" tt="%.1f" vmt="%.1f" speed="%.1f>' % (
year, self.id, self.route_id, self.time, self.tt, self.vmt, self.speed)
tt_table.__str__ = tt_table.__repr__
noaa_table = type(
'Noaa%d' % year, (Base, ), {
'__tablename__':
'noaa_weather_%d' % year,
'id':
Column(Integer, primary_key=True, unique=True, autoincrement=True),
'usaf':
Column(VARCHAR(6), nullable=False),
'wban':
Column(VARCHAR(5), nullable=False),
'dtime':
Column(DateTime, nullable=False),
'precip':
Column(Float, nullable=True),
'precip_type':
Column(VARCHAR(4), nullable=True),
'precip_intensity':
Column(VARCHAR(4), nullable=True),
'precip_qc':
Column(VARCHAR(4), nullable=True),
'visibility':
Column(Float, nullable=True),
'visibility_qc':
Column(VARCHAR(4), nullable=True),
'obscuration':
Column(VARCHAR(4), nullable=True),
'descriptor':
Column(VARCHAR(4), nullable=True),
'air_temp':
Column(Float, nullable=True),
'air_temp_qc':
Column(VARCHAR(4), nullable=True),
'dew_point':
Column(Float, nullable=True),
'dew_point_qc':
Column(VARCHAR(4), nullable=True),
'relative_humidity':
Column(Float, nullable=True),
'wind_dir':
Column(Integer, nullable=True),
'wind_dir_qc':
Column(VARCHAR(4), nullable=True),
'wind_speed':
Column(Float, nullable=True),
'wind_speed_qc':
Column(VARCHAR(4), nullable=True),
'wind_gust':
Column(Float, nullable=True),
'wind_gust_qc':
Column(VARCHAR(4), nullable=True),
})
noaa_table.__repr__ = lambda self: \
'<NoaaWeather%d id="%d" usaf="%s" wban="%s" time="%s" precip="%s" precip_type="%s">' % (
year, self.id, self.usaf, self.wban, self.dtime, self.precip, self.precip_type)
noaa_table.__str__ = noaa_table.__repr__
tt_weather_table = type(
'TTWeather%d' % year, (Base, ), {
'__tablename__':
'tt_weather_%d' % year,
'id':
Column(Integer, primary_key=True, unique=True, autoincrement=True),
'tt_id':
Column(Integer,
ForeignKey('tt_%d.id' % year, ondelete='CASCADE'),
primary_key=True,
index=True),
'_tt':
relationship(tt_table, backref=backref('tt_weather_%d' % year)),
'weather_id':
Column(Integer,
ForeignKey('noaa_weather_%d.id' % year, ondelete='CASCADE'),
index=True),
'_weather':
relationship(noaa_table, backref=backref('tt_weather_%d' % year)),
'is_extended':
False,
})
setattr(tt_weather_table, 'oc_field', 'weather_id')
tt_weather_table.__repr__ = lambda self: \
'<TTWeather%d id="%d" tt_id="%d" weather_id="%d" precip="%s" precip_type="%s" precip_intensity="%s">' % (
year, self.id, self.tt_id, self.weather_id,
self._weather.precip if self._weather else 'N/A',
self._weather.precip_type if self._weather else 'N/A',
self._weather.precip_intensity if self._weather else 'N/A',)
tt_weather_table.__str__ = tt_weather_table.__repr__
tt_workzone_table = type(
'TTWorkzone%d' % year,
(Base, ),
{
'__tablename__':
'tt_workzone_%d' % year,
'id':
Column(Integer, primary_key=True, unique=True, autoincrement=True),
'tt_id':
Column(Integer,
ForeignKey('tt_%d.id' % year, ondelete='CASCADE'),
nullable=False,
index=True),
'_tt':
relationship(tt_table, backref=backref('tt_workzone_%d' % year)),
'workzone_id':
Column(Integer,
ForeignKey(WorkZone.id, ondelete='CASCADE'),
nullable=False,
index=True),
'_workzone':
relationship(WorkZone),
'loc_type':
Column(Integer, nullable=True),
'distance':
Column(Float, nullable=True), # in mile
'off_distance':
Column(Float, nullable=True), # in mile
'is_extended':
False,
})
setattr(tt_workzone_table, 'oc_field', 'workzone_id')
tt_workzone_table.__repr__ = lambda self: \
'<TTWorkZone%d id="%d" tt_id="%d" workzone_id="%d" loc_type="%d" distance="%.2f" off_distance="%.2f">' % (
year, self.id, self.tt_id, self.workzone_id, self.loc_type, self.distance, self.off_distance)
tt_workzone_table.__str__ = tt_workzone_table.__repr__
tt_specialevent_table = type(
'TTSpecialevent%d' % year,
(Base, ),
{
'__tablename__':
'tt_specialevent_%d' % year,
'id':
Column(Integer, primary_key=True, unique=True, autoincrement=True),
'tt_id':
Column(Integer,
ForeignKey('tt_%d.id' % year, ondelete='CASCADE'),
nullable=False,
index=True),
'_tt':
relationship(tt_table,
backref=backref('tt_specialevent_%d' % year)),
'specialevent_id':
Column(Integer,
ForeignKey(Specialevent.id, ondelete='CASCADE'),
nullable=False,
index=True),
'_specialevent':
relationship(Specialevent),
'distance':
Column(Float, nullable=False), # in mile
'event_type':
Column(CHAR, nullable=False),
'is_extended':
False,
})
setattr(tt_specialevent_table, 'oc_field', 'specialevent_id')
tt_specialevent_table.__repr__ = lambda self: \
'<TTSpecialEvent%d id="%d" tt_id="%d" specialevent_id="%d" distance="%d" event_type="%s">' % (
year, self.id, self.tt_id, self.specialevent_id, self.distance,
self.event_type)
tt_specialevent_table.__str__ = tt_specialevent_table.__repr__
tt_snowmgmt_table = type(
'TTSnowmgmt%d' % year,
(Base, ),
{
'__tablename__':
'tt_snowmgmt_%d' % year,
'id':
Column(Integer, primary_key=True, unique=True, autoincrement=True),
'tt_id':
Column(Integer,
ForeignKey('tt_%d.id' % year, ondelete='CASCADE'),
nullable=False,
index=True),
'_tt':
relationship(tt_table, backref=backref('tt_snowmgmt_%d' % year)),
'snowmgmt_id':
Column(Integer,
ForeignKey(SnowManagement.id, ondelete='CASCADE'),
nullable=False,
index=True),
'_snowmgmt':
relationship(SnowManagement),
'loc_type':
Column(Integer, nullable=True),
'distance':
Column(Float, nullable=True), # in mile
'off_distance':
Column(Float, nullable=True), # in mile
'road_status':
Column(Integer, nullable=True),
'recovery_level':
Column(Integer, nullable=True),
'is_extended':
False,
})
setattr(tt_snowmgmt_table, 'oc_field', 'snowmgmt_id')
tt_snowmgmt_table.__repr__ = lambda self: \
'<TTSnowManagement%d id="%d" tt_id="%d" snowmgmt_id="%d"' \
' loc_type="%d" distance="%.2f" off_distance="%.2f" road_status="%d">' % (
year, self.id, self.tt_id, self.snowmgmt_id,
self.loc_type, self.distance, self.off_distance, self.road_status)
tt_snowmgmt_table.__str__ = tt_snowmgmt_table.__repr__
tt_incident_table = type(
'TTIncident%d' % year, (Base, ), {
'__tablename__':
'tt_incident_%d' % year,
'id':
Column(Integer, primary_key=True, unique=True, autoincrement=True),
'tt_id':
Column(Integer,
ForeignKey('tt_%d.id' % year, ondelete='CASCADE'),
nullable=False,
index=True),
'_tt':
relationship(tt_table, backref=backref('tt_incident_%d' % year)),
'incident_id':
Column(Integer,
ForeignKey(Incident.id, ondelete='CASCADE'),
nullable=False,
index=True),
'_incident':
relationship(Incident),
'distance':
Column(Float, nullable=True),
'off_distance':
Column(Float, nullable=True),
'is_extended':
False,
})
setattr(tt_incident_table, 'oc_field', 'incident_id')
tt_incident_table.__repr__ = lambda self: \
'<TTIncidentInfo%d id="%d" tt_id="%d" incident_id="%d" distance="%.2f" off_distance="%.2f">' % (
year, self.id, self.tt_id, self.incident_id, self.distance, self.off_distance)
tt_incident_table.__str__ = tt_incident_table.__repr__
TTTables[year] = tt_table
TTWeatherTables[year] = tt_weather_table
TTWorkzoneTables[year] = tt_workzone_table
TTIncidentTables[year] = tt_incident_table
TTSnowmgmtTables[year] = tt_snowmgmt_table
TTSpecialeventTables[year] = tt_specialevent_table
# WeatherTables[year] = weather_table
NOAATables[year] = noaa_table
for _ in range(10):
try:
Base.metadata.create_all()
break
except Exception as ex:
# print('=-> exception occured when create yearly table : ', year)
time.sleep(1)
class Config:
sql_alchemy = [
UniqueConstraint("collection", "name"),
]
class Config:
sql_alchemy = [
UniqueConstraint("owner", "name"),
]
class User(Base):
__tablename__ = 'users'
__table_args__ = (UniqueConstraint('name', name='_users_uc'), )
id = Column(Integer, primary_key=True)
name = Column(String)
class DruidDatasource(Model, BaseDatasource):
"""ORM object referencing Druid datasources (tables)"""
__tablename__ = 'datasources'
type = 'druid'
query_langtage = 'json'
cluster_class = DruidCluster
metric_class = DruidMetric
column_class = DruidColumn
baselink = 'druiddatasourcemodelview'
# Columns
datasource_name = Column(String(255))
is_hidden = Column(Boolean, default=False)
fetch_values_from = Column(String(100))
cluster_name = Column(
String(250), ForeignKey('clusters.cluster_name'))
cluster = relationship(
'DruidCluster', backref='datasources', foreign_keys=[cluster_name])
user_id = Column(Integer, ForeignKey('ab_user.id'))
owner = relationship(
sm.user_model,
backref=backref('datasources', cascade='all, delete-orphan'),
foreign_keys=[user_id])
UniqueConstraint('cluster_name', 'datasource_name')
export_fields = (
'datasource_name', 'is_hidden', 'description', 'default_endpoint',
'cluster_name', 'offset', 'cache_timeout', 'params',
)
@property
def database(self):
return self.cluster
@property
def connection(self):
return str(self.database)
@property
def num_cols(self):
return [c.column_name for c in self.columns if c.is_num]
@property
def name(self):
return self.datasource_name
@property
def schema(self):
ds_name = self.datasource_name or ''
name_pieces = ds_name.split('.')
if len(name_pieces) > 1:
return name_pieces[0]
else:
return None
@property
def schema_perm(self):
"""Returns schema permission if present, cluster one otherwise."""
return utils.get_schema_perm(self.cluster, self.schema)
def get_perm(self):
return (
'[{obj.cluster_name}].[{obj.datasource_name}]'
'(id:{obj.id})').format(obj=self)
@property
def link(self):
name = escape(self.datasource_name)
return Markup('<a href="{self.url}">{name}</a>').format(**locals())
@property
def full_name(self):
return utils.get_datasource_full_name(
self.cluster_name, self.datasource_name)
@property
def time_column_grains(self):
return {
'time_columns': [
'all', '5 seconds', '30 seconds', '1 minute',
'5 minutes', '1 hour', '6 hour', '1 day', '7 days',
'week', 'week_starting_sunday', 'week_ending_saturday',
'month',
],
'time_grains': ['now'],
}
def __repr__(self):
return self.datasource_name
@renders('datasource_name')
def datasource_link(self):
url = '/superset/explore/{obj.type}/{obj.id}/'.format(obj=self)
name = escape(self.datasource_name)
return Markup('<a href="{url}">{name}</a>'.format(**locals()))
def get_metric_obj(self, metric_name):
return [
m.json_obj for m in self.metrics
if m.metric_name == metric_name
][0]
@classmethod
def import_obj(cls, i_datasource, import_time=None):
"""Imports the datasource from the object to the database.
Metrics and columns and datasource will be overridden if exists.
This function can be used to import/export dashboards between multiple
superset instances. Audit metadata isn't copies over.
"""
def lookup_datasource(d):
return db.session.query(DruidDatasource).filter(
DruidDatasource.datasource_name == d.datasource_name,
DruidCluster.cluster_name == d.cluster_name,
).first()
def lookup_cluster(d):
return db.session.query(DruidCluster).filter_by(
cluster_name=d.cluster_name).one()
return import_util.import_datasource(
db.session, i_datasource, lookup_cluster, lookup_datasource,
import_time)
@staticmethod
def version_higher(v1, v2):
"""is v1 higher than v2
>>> DruidDatasource.version_higher('0.8.2', '0.9.1')
False
>>> DruidDatasource.version_higher('0.8.2', '0.6.1')
True
>>> DruidDatasource.version_higher('0.8.2', '0.8.2')
False
>>> DruidDatasource.version_higher('0.8.2', '0.9.BETA')
False
>>> DruidDatasource.version_higher('0.8.2', '0.9')
False
"""
def int_or_0(v):
try:
v = int(v)
except (TypeError, ValueError):
v = 0
return v
v1nums = [int_or_0(n) for n in v1.split('.')]
v2nums = [int_or_0(n) for n in v2.split('.')]
v1nums = (v1nums + [0, 0, 0])[:3]
v2nums = (v2nums + [0, 0, 0])[:3]
return v1nums[0] > v2nums[0] or \
(v1nums[0] == v2nums[0] and v1nums[1] > v2nums[1]) or \
(v1nums[0] == v2nums[0] and v1nums[1] == v2nums[1] and v1nums[2] > v2nums[2])
def latest_metadata(self):
"""Returns segment metadata from the latest segment"""
logging.info('Syncing datasource [{}]'.format(self.datasource_name))
client = self.cluster.get_pydruid_client()
try:
results = client.time_boundary(datasource=self.datasource_name)
except IOError:
results = None
if results:
max_time = results[0]['result']['maxTime']
max_time = dparse(max_time)
else:
max_time = datetime.now()
# Query segmentMetadata for 7 days back. However, due to a bug,
# we need to set this interval to more than 1 day ago to exclude
# realtime segments, which triggered a bug (fixed in druid 0.8.2).
# https://groups.google.com/forum/#!topic/druid-user/gVCqqspHqOQ
lbound = (max_time - timedelta(days=7)).isoformat()
if not self.version_higher(self.cluster.druid_version, '0.8.2'):
rbound = (max_time - timedelta(1)).isoformat()
else:
rbound = max_time.isoformat()
segment_metadata = None
try:
segment_metadata = client.segment_metadata(
datasource=self.datasource_name,
intervals=lbound + '/' + rbound,
merge=self.merge_flag,
analysisTypes=[])
except Exception as e:
logging.warning('Failed first attempt to get latest segment')
logging.exception(e)
if not segment_metadata:
# if no segments in the past 7 days, look at all segments
lbound = datetime(1901, 1, 1).isoformat()[:10]
if not self.version_higher(self.cluster.druid_version, '0.8.2'):
rbound = datetime.now().isoformat()
else:
rbound = datetime(2050, 1, 1).isoformat()[:10]
try:
segment_metadata = client.segment_metadata(
datasource=self.datasource_name,
intervals=lbound + '/' + rbound,
merge=self.merge_flag,
analysisTypes=[])
except Exception as e:
logging.warning('Failed 2nd attempt to get latest segment')
logging.exception(e)
if segment_metadata:
return segment_metadata[-1]['columns']
def generate_metrics(self):
self.generate_metrics_for(self.columns)
def generate_metrics_for(self, columns):
metrics = {}
for col in columns:
metrics.update(col.get_metrics())
dbmetrics = (
db.session.query(DruidMetric)
.filter(DruidMetric.datasource_id == self.id)
.filter(or_(DruidMetric.metric_name == m for m in metrics))
)
dbmetrics = {metric.metric_name: metric for metric in dbmetrics}
for metric in metrics.values():
metric.datasource_id = self.id
if not dbmetrics.get(metric.metric_name, None):
with db.session.no_autoflush:
db.session.add(metric)
@classmethod
def sync_to_db_from_config(
cls,
druid_config,
user,
cluster,
refresh=True):
"""Merges the ds config from druid_config into one stored in the db."""
session = db.session
datasource = (
session.query(cls)
.filter_by(datasource_name=druid_config['name'])
.first()
)
# Create a new datasource.
if not datasource:
datasource = cls(
datasource_name=druid_config['name'],
cluster=cluster,
owner=user,
changed_by_fk=user.id,
created_by_fk=user.id,
)
session.add(datasource)
elif not refresh:
return
dimensions = druid_config['dimensions']
col_objs = (
session.query(DruidColumn)
.filter(DruidColumn.datasource_id == datasource.id)
.filter(or_(DruidColumn.column_name == dim for dim in dimensions))
)
col_objs = {col.column_name: col for col in col_objs}
for dim in dimensions:
col_obj = col_objs.get(dim, None)
if not col_obj:
col_obj = DruidColumn(
datasource_id=datasource.id,
column_name=dim,
groupby=True,
filterable=True,
# TODO: fetch type from Hive.
type='STRING',
datasource=datasource,
)
session.add(col_obj)
# Import Druid metrics
metric_objs = (
session.query(DruidMetric)
.filter(DruidMetric.datasource_id == datasource.id)
.filter(or_(DruidMetric.metric_name == spec['name']
for spec in druid_config['metrics_spec']))
)
metric_objs = {metric.metric_name: metric for metric in metric_objs}
for metric_spec in druid_config['metrics_spec']:
metric_name = metric_spec['name']
metric_type = metric_spec['type']
metric_json = json.dumps(metric_spec)
if metric_type == 'count':
metric_type = 'longSum'
metric_json = json.dumps({
'type': 'longSum',
'name': metric_name,
'fieldName': metric_name,
})
metric_obj = metric_objs.get(metric_name, None)
if not metric_obj:
metric_obj = DruidMetric(
metric_name=metric_name,
metric_type=metric_type,
verbose_name='%s(%s)' % (metric_type, metric_name),
datasource=datasource,
json=metric_json,
description=(
'Imported from the airolap config dir for %s' %
druid_config['name']),
)
session.add(metric_obj)
session.commit()
@staticmethod
def time_offset(granularity):
if granularity == 'week_ending_saturday':
return 6 * 24 * 3600 * 1000 # 6 days
return 0
# uses https://en.wikipedia.org/wiki/ISO_8601
# http://druid.io/docs/0.8.0/querying/granularities.html
# TODO: pass origin from the UI
@staticmethod
def granularity(period_name, timezone=None, origin=None):
if not period_name or period_name == 'all':
return 'all'
iso_8601_dict = {
'5 seconds': 'PT5S',
'30 seconds': 'PT30S',
'1 minute': 'PT1M',
'5 minutes': 'PT5M',
'1 hour': 'PT1H',
'6 hour': 'PT6H',
'one day': 'P1D',
'1 day': 'P1D',
'7 days': 'P7D',
'week': 'P1W',
'week_starting_sunday': 'P1W',
'week_ending_saturday': 'P1W',
'month': 'P1M',
}
granularity = {'type': 'period'}
if timezone:
granularity['timeZone'] = timezone
if origin:
dttm = utils.parse_human_datetime(origin)
granularity['origin'] = dttm.isoformat()
if period_name in iso_8601_dict:
granularity['period'] = iso_8601_dict[period_name]
if period_name in ('week_ending_saturday', 'week_starting_sunday'):
# use Sunday as start of the week
granularity['origin'] = '2016-01-03T00:00:00'
elif not isinstance(period_name, string_types):
granularity['type'] = 'duration'
granularity['duration'] = period_name
elif period_name.startswith('P'):
# identify if the string is the iso_8601 period
granularity['period'] = period_name
else:
granularity['type'] = 'duration'
granularity['duration'] = utils.parse_human_timedelta(
period_name).total_seconds() * 1000
return granularity
@staticmethod
def _metrics_and_post_aggs(metrics, metrics_dict):
all_metrics = []
post_aggs = {}
def recursive_get_fields(_conf):
_type = _conf.get('type')
_field = _conf.get('field')
_fields = _conf.get('fields')
field_names = []
if _type in ['fieldAccess', 'hyperUniqueCardinality',
'quantile', 'quantiles']:
field_names.append(_conf.get('fieldName', ''))
if _field:
field_names += recursive_get_fields(_field)
if _fields:
for _f in _fields:
field_names += recursive_get_fields(_f)
return list(set(field_names))
for metric_name in metrics:
metric = metrics_dict[metric_name]
if metric.metric_type != 'postagg':
all_metrics.append(metric_name)
else:
mconf = metric.json_obj
all_metrics += recursive_get_fields(mconf)
all_metrics += mconf.get('fieldNames', [])
if mconf.get('type') == 'javascript':
post_aggs[metric_name] = JavascriptPostAggregator(
name=mconf.get('name', ''),
field_names=mconf.get('fieldNames', []),
function=mconf.get('function', ''))
elif mconf.get('type') == 'quantile':
post_aggs[metric_name] = Quantile(
mconf.get('name', ''),
mconf.get('probability', ''),
)
elif mconf.get('type') == 'quantiles':
post_aggs[metric_name] = Quantiles(
mconf.get('name', ''),
mconf.get('probabilities', ''),
)
elif mconf.get('type') == 'fieldAccess':
post_aggs[metric_name] = Field(mconf.get('name'))
elif mconf.get('type') == 'constant':
post_aggs[metric_name] = Const(
mconf.get('value'),
output_name=mconf.get('name', ''),
)
elif mconf.get('type') == 'hyperUniqueCardinality':
post_aggs[metric_name] = HyperUniqueCardinality(
mconf.get('name'),
)
elif mconf.get('type') == 'arithmetic':
post_aggs[metric_name] = Postaggregator(
mconf.get('fn', '/'),
mconf.get('fields', []),
mconf.get('name', ''))
else:
post_aggs[metric_name] = CustomPostAggregator(
mconf.get('name', ''),
mconf)
return all_metrics, post_aggs
def values_for_column(self,
column_name,
limit=10000):
"""Retrieve some values for the given column"""
# TODO: Use Lexicographic TopNMetricSpec once supported by PyDruid
if self.fetch_values_from:
from_dttm = utils.parse_human_datetime(self.fetch_values_from)
else:
from_dttm = datetime(1970, 1, 1)
qry = dict(
datasource=self.datasource_name,
granularity='all',
intervals=from_dttm.isoformat() + '/' + datetime.now().isoformat(),
aggregations=dict(count=count('count')),
dimension=column_name,
metric='count',
threshold=limit,
)
client = self.cluster.get_pydruid_client()
client.topn(**qry)
df = client.export_pandas()
return [row[column_name] for row in df.to_records(index=False)]
def get_query_str(self, query_obj, phase=1, client=None):
return self.run_query(client=client, phase=phase, **query_obj)
def _add_filter_from_pre_query_data(self, df, dimensions, dim_filter):
ret = dim_filter
if df is not None and not df.empty:
new_filters = []
for unused, row in df.iterrows():
fields = []
for dim in dimensions:
f = Dimension(dim) == row[dim]
fields.append(f)
if len(fields) > 1:
term = Filter(type='and', fields=fields)
new_filters.append(term)
elif fields:
new_filters.append(fields[0])
if new_filters:
ff = Filter(type='or', fields=new_filters)
if not dim_filter:
ret = ff
else:
ret = Filter(type='and', fields=[ff, dim_filter])
return ret
def run_query( # noqa / druid
self,
groupby, metrics,
granularity,
from_dttm, to_dttm,
filter=None, # noqa
is_timeseries=True,
timeseries_limit=None,
timeseries_limit_metric=None,
row_limit=None,
inner_from_dttm=None, inner_to_dttm=None,
orderby=None,
extras=None, # noqa
select=None, # noqa
columns=None, phase=2, client=None, form_data=None,
order_desc=True):
"""Runs a query against Druid and returns a dataframe.
"""
# TODO refactor into using a TBD Query object
client = client or self.cluster.get_pydruid_client()
if not is_timeseries:
granularity = 'all'
inner_from_dttm = inner_from_dttm or from_dttm
inner_to_dttm = inner_to_dttm or to_dttm
# add tzinfo to native datetime with config
from_dttm = from_dttm.replace(tzinfo=DRUID_TZ)
to_dttm = to_dttm.replace(tzinfo=DRUID_TZ)
timezone = from_dttm.tzname()
query_str = ''
metrics_dict = {m.metric_name: m for m in self.metrics}
columns_dict = {c.column_name: c for c in self.columns}
all_metrics, post_aggs = self._metrics_and_post_aggs(
metrics,
metrics_dict)
aggregations = OrderedDict()
for m in self.metrics:
if m.metric_name in all_metrics:
aggregations[m.metric_name] = m.json_obj
rejected_metrics = [
m.metric_name for m in self.metrics
if m.is_restricted and
m.metric_name in aggregations.keys() and
not sm.has_access('metric_access', m.perm)
]
if rejected_metrics:
raise MetricPermException(
'Access to the metrics denied: ' + ', '.join(rejected_metrics),
)
# the dimensions list with dimensionSpecs expanded
dimensions = []
groupby = [gb for gb in groupby if gb in columns_dict]
for column_name in groupby:
col = columns_dict.get(column_name)
dim_spec = col.dimension_spec
if dim_spec:
dimensions.append(dim_spec)
else:
dimensions.append(column_name)
extras = extras or {}
qry = dict(
datasource=self.datasource_name,
dimensions=dimensions,
aggregations=aggregations,
granularity=DruidDatasource.granularity(
granularity,
timezone=timezone,
origin=extras.get('druid_time_origin'),
),
post_aggregations=post_aggs,
intervals=from_dttm.isoformat() + '/' + to_dttm.isoformat(),
)
filters = DruidDatasource.get_filters(filter, self.num_cols)
if filters:
qry['filter'] = filters
having_filters = self.get_having_filters(extras.get('having_druid'))
if having_filters:
qry['having'] = having_filters
order_direction = 'descending' if order_desc else 'ascending'
if len(groupby) == 0 and not having_filters:
del qry['dimensions']
client.timeseries(**qry)
elif (
not having_filters and
len(groupby) == 1 and
order_desc and
not isinstance(list(qry.get('dimensions'))[0], dict)
):
dim = list(qry.get('dimensions'))[0]
if timeseries_limit_metric:
order_by = timeseries_limit_metric
else:
order_by = list(qry['aggregations'].keys())[0]
# Limit on the number of timeseries, doing a two-phases query
pre_qry = deepcopy(qry)
pre_qry['granularity'] = 'all'
pre_qry['threshold'] = min(row_limit,
timeseries_limit or row_limit)
pre_qry['metric'] = order_by
pre_qry['dimension'] = dim
del pre_qry['dimensions']
client.topn(**pre_qry)
query_str += '// Two phase query\n// Phase 1\n'
query_str += json.dumps(
client.query_builder.last_query.query_dict, indent=2)
query_str += '\n'
if phase == 1:
return query_str
query_str += (
"// Phase 2 (built based on phase one's results)\n")
df = client.export_pandas()
qry['filter'] = self._add_filter_from_pre_query_data(
df,
qry['dimensions'], filters)
qry['threshold'] = timeseries_limit or 1000
if row_limit and granularity == 'all':
qry['threshold'] = row_limit
qry['dimension'] = list(qry.get('dimensions'))[0]
qry['dimension'] = dim
del qry['dimensions']
qry['metric'] = list(qry['aggregations'].keys())[0]
client.topn(**qry)
elif len(groupby) > 0:
# If grouping on multiple fields or using a having filter
# we have to force a groupby query
if timeseries_limit and is_timeseries:
order_by = metrics[0] if metrics else self.metrics[0]
if timeseries_limit_metric:
order_by = timeseries_limit_metric
# Limit on the number of timeseries, doing a two-phases query
pre_qry = deepcopy(qry)
pre_qry['granularity'] = 'all'
pre_qry['limit_spec'] = {
'type': 'default',
'limit': min(timeseries_limit, row_limit),
'intervals': (
inner_from_dttm.isoformat() + '/' +
inner_to_dttm.isoformat()),
'columns': [{
'dimension': order_by,
'direction': order_direction,
}],
}
client.groupby(**pre_qry)
query_str += '// Two phase query\n// Phase 1\n'
query_str += json.dumps(
client.query_builder.last_query.query_dict, indent=2)
query_str += '\n'
if phase == 1:
return query_str
query_str += (
"// Phase 2 (built based on phase one's results)\n")
df = client.export_pandas()
qry['filter'] = self._add_filter_from_pre_query_data(
df,
qry['dimensions'],
filters,
)
qry['limit_spec'] = None
if row_limit:
qry['limit_spec'] = {
'type': 'default',
'limit': row_limit,
'columns': [{
'dimension': (
metrics[0] if metrics else self.metrics[0]),
'direction': order_direction,
}],
}
client.groupby(**qry)
query_str += json.dumps(
client.query_builder.last_query.query_dict, indent=2)
return query_str
def query(self, query_obj):
qry_start_dttm = datetime.now()
client = self.cluster.get_pydruid_client()
query_str = self.get_query_str(
client=client, query_obj=query_obj, phase=2)
df = client.export_pandas()
if df is None or df.size == 0:
raise Exception(_('No data was returned.'))
df.columns = [
DTTM_ALIAS if c == 'timestamp' else c for c in df.columns]
is_timeseries = query_obj['is_timeseries'] \
if 'is_timeseries' in query_obj else True
if (
not is_timeseries and
DTTM_ALIAS in df.columns):
del df[DTTM_ALIAS]
# Reordering columns
cols = []
if DTTM_ALIAS in df.columns:
cols += [DTTM_ALIAS]
cols += [col for col in query_obj['groupby'] if col in df.columns]
cols += [col for col in query_obj['metrics'] if col in df.columns]
df = df[cols]
time_offset = DruidDatasource.time_offset(query_obj['granularity'])
def increment_timestamp(ts):
dt = utils.parse_human_datetime(ts).replace(
tzinfo=DRUID_TZ)
return dt + timedelta(milliseconds=time_offset)
if DTTM_ALIAS in df.columns and time_offset:
df[DTTM_ALIAS] = df[DTTM_ALIAS].apply(increment_timestamp)
return QueryResult(
df=df,
query=query_str,
duration=datetime.now() - qry_start_dttm)
@staticmethod
def get_filters(raw_filters, num_cols): # noqa
filters = None
for flt in raw_filters:
if not all(f in flt for f in ['col', 'op', 'val']):
continue
col = flt['col']
op = flt['op']
eq = flt['val']
cond = None
if op in ('in', 'not in'):
eq = [
types.replace('"', '').strip()
if isinstance(types, string_types)
else types
for types in eq]
elif not isinstance(flt['val'], string_types):
eq = eq[0] if eq and len(eq) > 0 else ''
is_numeric_col = col in num_cols
if is_numeric_col:
if op in ('in', 'not in'):
eq = [utils.string_to_num(v) for v in eq]
else:
eq = utils.string_to_num(eq)
if op == '==':
cond = Dimension(col) == eq
elif op == '!=':
cond = Dimension(col) != eq
elif op in ('in', 'not in'):
fields = []
# ignore the filter if it has no value
if not len(eq):
continue
elif len(eq) == 1:
cond = Dimension(col) == eq[0]
else:
for s in eq:
fields.append(Dimension(col) == s)
cond = Filter(type='or', fields=fields)
if op == 'not in':
cond = ~cond
elif op == 'regex':
cond = Filter(type='regex', pattern=eq, dimension=col)
elif op == '>=':
cond = Bound(col, eq, None, alphaNumeric=is_numeric_col)
elif op == '<=':
cond = Bound(col, None, eq, alphaNumeric=is_numeric_col)
elif op == '>':
cond = Bound(
col, eq, None,
lowerStrict=True, alphaNumeric=is_numeric_col,
)
elif op == '<':
cond = Bound(
col, None, eq,
upperStrict=True, alphaNumeric=is_numeric_col,
)
if filters:
filters = Filter(type='and', fields=[
cond,
filters,
])
else:
filters = cond
return filters
def _get_having_obj(self, col, op, eq):
cond = None
if op == '==':
if col in self.column_names:
cond = DimSelector(dimension=col, value=eq)
else:
cond = Aggregation(col) == eq
elif op == '>':
cond = Aggregation(col) > eq
elif op == '<':
cond = Aggregation(col) < eq
return cond
def get_having_filters(self, raw_filters):
filters = None
reversed_op_map = {
'!=': '==',
'>=': '<',
'<=': '>',
}
for flt in raw_filters:
if not all(f in flt for f in ['col', 'op', 'val']):
continue
col = flt['col']
op = flt['op']
eq = flt['val']
cond = None
if op in ['==', '>', '<']:
cond = self._get_having_obj(col, op, eq)
elif op in reversed_op_map:
cond = ~self._get_having_obj(col, reversed_op_map[op], eq)
if filters:
filters = filters & cond
else:
filters = cond
return filters
@classmethod
def query_datasources_by_name(
cls, session, database, datasource_name, schema=None):
return (
session.query(cls)
.filter_by(cluster_name=database.id)
.filter_by(datasource_name=datasource_name)
.all()
)
class Rule(db.Model, APIModel):
# Defines some properties on the fields:
# required: Is required for creating object via
# a POST request.
# editable: Can be edited via a PUT request.
# Defaults to False.
all_fields = {
'message': {
'required': True,
'editable': True
},
'references': {
'required': True,
'editable': False
},
'classtype': {
'required': True,
'editable': True
},
'sid': {
'required': True,
'editable': False
},
'rev': {
'required': True,
'editable': True
},
'date': {
'required': False,
'editable': False
},
'rule_format': {
'required': True,
'editable': False
},
'is_active': {
'required': False,
'editable': True
},
'notes': {
'required': False,
'editable': True
}
}
__tablename__ = 'rules'
id = db.Column(db.Integer, primary_key=True)
message = db.Column(db.String(140))
references = db.relationship('Reference', backref='rule', lazy='dynamic')
classtype = db.Column(db.String(50))
sid = db.Column(db.Integer)
rev = db.Column(db.Integer)
date = db.Column(db.DateTime(), default=datetime.utcnow)
rule_format = db.Column(db.String(500))
is_active = db.Column(db.Boolean)
notes = db.Column(db.String(140))
__table_args__ = (UniqueConstraint(sid, rev), )
def __init__(self,
msg=None,
classtype=None,
sid=None,
rev=None,
date=None,
rule_format=None,
**args):
self.message = msg
self.classtype = classtype
self.sid = sid
self.rev = rev
self.rule_format = rule_format
self.is_active = True
def insert_refs(self, refs):
for r in refs:
ref = Reference()
ref.rule = self
ref.text = r.decode('utf-8')
db.session.add(ref)
db.session.commit()
def to_dict(self):
return dict(sid=self.sid,
rev=self.rev,
msg=self.message,
classtype=self.classtype,
is_active=self.is_active)
def __repr__(self):
return '<Rule>{}'.format(self.to_dict())
def render(self):
"""
Takes Rule model and renders itself to plain text.
"""
msg = 'msg:"{}"'.format(self.message)
classtype = 'classtype:{}'.format(self.classtype)
sid = 'sid:{}'.format(self.sid)
rev = 'rev:{}'.format(self.rev)
reference = ''
for r in self.references:
reference += 'reference:{}; '.format(r.text)
# Remove trailing '; ' from references.
reference = reference[:-2]
return self.rule_format.format(msg=msg,
sid=sid,
rev=rev,
classtype=classtype,
reference=reference)
@classmethod
def renderall(cls):
"""
Renders latest revision of active rules.
This method must be called within a Flask app
context.
"""
rules = cls.query.filter_by(is_active=True).\
group_by(cls.sid).\
having(func.max(cls.rev))
return '\n\n'.join([ru.render() for ru in rules])
@classmethod
def bulk_import(cls, rulelist):
"""
Imports rules into the database.
This method must be called within a Flask app
context.
"""
cnt = 0
for ru in rulelist:
# Checking for rules with this sid.
if cls.query.\
filter_by(sid=ru['sid']).\
filter(cls.rev >= ru['rev']).count() == 0:
# All rules with this sid have lower rev number that
# the incoming one, or this is a new sid altogether.
rule = cls(**ru)
rule.insert_refs(ru['references'])
db.session.add(rule)
# Disabling older rules.
cls.query.\
filter_by(sid=ru['sid']).\
filter(cls.rev < ru['rev']).\
update({'is_active': False}, False)
cnt += 1
if cnt % 500 == 0:
print 'Imported {} rules so far...'.format(cnt)
print 'Finished Importing {} rules. Committing data'.format(cnt)
db.session.commit()
__mapper_args__ = {
"polymorphic_on":
case(
[
(isFromAllocineProvider, "allocine_venue_provider"),
],
else_="venue_provider",
),
"polymorphic_identity":
"venue_provider",
}
__table_args__ = (UniqueConstraint(
"venueId",
"providerId",
"venueIdAtOfferProvider",
name="unique_venue_provider",
), )
@staticmethod
def restize_integrity_error(internal_error):
if "unique_venue_provider" in str(internal_error.orig):
return ["global", "Votre lieu est déjà lié à cette source"]
return PcObject.restize_integrity_error(internal_error)
@property
def nOffers(self):
# pylint: disable=comparison-with-callable
return Offer.query.filter(Offer.venueId == self.venueId).filter(
Offer.lastProviderId == self.providerId).count()
class User(Base, BaseModel):
__tablename__ = "users"
__table_args__ = (UniqueConstraint("name", name="_users_uc"), )
id = Column(Integer, primary_key=True)
name = Column(String)
class DETETA_table:
# connection string postgres"
internal_connection = Connection()
# create engine and metadata
engine = create_engine(internal_connection.conn_str(),
echo=False,
convert_unicode=True)
metadata = MetaData(engine)
# define tables
deteta_table = Table(
'deteta_table',
metadata,
Column('id_det_eta', Integer, primary_key=True),
Column('sito', Text),
Column('nr_individuo', Integer),
Column('sinf_min', Integer),
Column('sinf_max', Integer),
Column('sinf_min_2', Integer),
Column('sinf_max_2', Integer),
Column('SSPIA', Integer),
Column('SSPIB', Integer),
Column('SSPIC', Integer),
Column('SSPID', Integer),
Column('sup_aur_min', Integer),
Column('sup_aur_max', Integer),
Column('sup_aur_min_2', Integer),
Column('sup_aur_max_2', Integer),
Column('ms_sup_min', Integer),
Column('ms_sup_max', Integer),
Column('ms_inf_min', Integer),
Column('ms_inf_max', Integer),
Column('usura_min', Integer),
Column('usura_max', Integer),
Column('Id_endo', Integer),
Column('Is_endo', Integer),
Column('IId_endo', Integer),
Column('IIs_endo', Integer),
Column('IIId_endo', Integer),
Column('IIIs_endo', Integer),
Column('IV_endo', Integer),
Column('V_endo', Integer),
Column('VI_endo', Integer),
Column('VII_endo', Integer),
Column('VIIId_endo', Integer),
Column('VIIIs_endo', Integer),
Column('IXd_endo', Integer),
Column('IXs_endo', Integer),
Column('Xd_endo', Integer),
Column('Xs_endo', Integer),
Column('endo_min', Integer),
Column('endo_max', Integer),
Column('volta_1', Integer),
Column('volta_2', Integer),
Column('volta_3', Integer),
Column('volta_4', Integer),
Column('volta_5', Integer),
Column('volta_6', Integer),
Column('volta_7', Integer),
Column('lat_6', Integer),
Column('lat_7', Integer),
Column('lat_8', Integer),
Column('lat_9', Integer),
Column('lat_10', Integer),
Column('volta_min', Integer),
Column('volta_max', Integer),
Column('ant_lat_min', Integer),
Column('ant_lat_max', Integer),
Column('ecto_min', Integer),
Column('ecto_max', Integer),
# explicit/composite unique constraint. 'name' is optional.
UniqueConstraint('sito', 'nr_individuo', name='ID_det_eta_unico'))
metadata.create_all(engine)
nullable=False),
Column('status',
Enum(name='resource_statuses', *RESOURCE_STATUSES),
nullable=False,
server_default='PENDING',
default='PENDING'),
Column('parent_zone_id', UUID, default=None, nullable=True),
Column('action',
Enum(name='actions', *ACTIONS),
default='CREATE',
server_default='CREATE',
nullable=False),
Column('pool_id', UUID, default=None, nullable=True),
Column('reverse_name', String(255), nullable=False),
Column('delayed_notify', Boolean, default=False),
UniqueConstraint('name', 'deleted', 'pool_id', name='unique_zone_name'),
ForeignKeyConstraint(['parent_zone_id'], ['zones.id'],
ondelete='SET NULL'),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
zone_attributes = Table('zone_attributes',
metadata,
Column('id',
UUID,
default=utils.generate_uuid,
primary_key=True),
Column('version', Integer, default=1, nullable=False),
Column('created_at',
DateTime,
class Location(BaseModel):
"""
Stores a location.
"""
__tablename__ = 'location'
__table_args__ = (
UniqueConstraint('lat',
'lon',
name='one_location_at_each_lat_lon_pair'),
CheckConstraint('lat Between -90 and 90', name='lat_bounds'),
CheckConstraint('lon Between -180 and 180', name='lon_bounds'),
)
# id
id = db.Column(db.Integer, primary_key=True)
source_id = db.Column(db.Integer)
version = db.Column(db.Integer, default=0)
# relationships
records = relationship("Record", cascade="all, delete-orphan")
points = relationship("Point", cascade="all, delete-orphan")
images = relationship("Image", cascade="all, delete-orphan")
# who
user_id = db.Column(db.Integer, ForeignKey('users.id'))
source = db.Column(db.String)
# where
lat = db.Column(db.Float, nullable=False, index=True)
lon = db.Column(db.Float, nullable=False, index=True)
original_lat = db.Column(db.Float, nullable=False)
original_lon = db.Column(db.Float, nullable=False)
# offset
# bearing from lat lon to center of field from lat lon
bearing = db.Column(db.Float, default=-1)
distance = db.Column(
db.Integer) # distance along bearing to center of field from lat lon
accuracy = db.Column(db.Integer)
country = db.Column(db.String)
continent = db.Column(db.String)
field = db.Column(db.String)
# when
date_created = db.Column(db.DateTime, default=db.func.now())
date_edited = db.Column(db.DateTime,
default=db.func.now(),
onupdate=db.func.now())
# use
use_validation = db.Column(db.Boolean, default=False, index=True)
use_validation_locked = db.Column(db.Boolean, default=False, index=True)
use_private = db.Column(db.Boolean, default=False, index=True)
use_deleted = db.Column(db.Boolean, default=False, index=True)
use_invalid = db.Column(db.Boolean, default=False, index=True)
use_invalid_reason = db.Column(db.String)
def __init__(self, *args, **kwargs):
# convert to float if str
self.lat = float(kwargs['lat'])
self.lon = float(kwargs['lon'])
self.original_lat = self.lat
self.original_lon = self.lon
assert abs(self.lat) < 90, 'lat exceeds bounds'
assert abs(self.lon) < 180, 'lon exceeds bounds'
super(Location, self).__init__(*args, **kwargs)
if self.bearing is not None and self.bearing != -1 and self.distance is not None and self.distance > 0:
self.offset(self.bearing, self.distance)
if 'use_validation' not in kwargs and 'use_validation_locked' not in kwargs:
self.use_validation = random.choice([True, False, False])
if self.use_validation:
self.use_validation_locked = random.choice([True, False])
self.check_neighbor_use()
# self.check_neighbor_field()
def check_neighbor_use(self, threshold=1000):
"""
Requires nearby samples to be used either for validation or training and not both.
First finds all nearby samples within a specific radius.
Next it determines the use of its neighbors. If there is no single use but a mix of uses,
do not accept the sample and review at a later time. If there is a single use, reassign
the current sample to the same use.
Finally clean up the validation_locked setting if necessary.
If failure to find single use, mark as invalid to trigger later review.
:param threshold: Integer in meters
:return: None
"""
assert threshold > 0
assert isinstance(self.lat, (int, long, float)), 'lat not number'
assert isinstance(self.lon, (int, long, float)), 'lon not number'
# get nearby locations to this location
nearby_locations = Location.within(self.lat, self.lon, threshold)
# if there are nearby locations
if len(nearby_locations) > 0:
use_validation = 0
use_training = 0
# get neighbors use
for location in nearby_locations:
# don't worry about these locations
if location.use_invalid or location.use_deleted:
continue
if location.use_validation:
use_validation += 1
else:
use_training += 1
# check if there is a mix of uses nearby
if abs(use_training - use_validation) == len(nearby_locations):
# apply the use to this sample
self.use_validation = use_validation > use_training
# after change clear validation_locked if use is for training
if not self.use_validation:
self.use_validation_locked = False
# else if not all one use mark it as invalid
# todo what is the best way to handle this? send message for review
else:
self.use_invalid = True
self.use_invalid_reason = '[Neighbor sample use is mix of training and validation]'
def check_neighbor_field(self, meters=100):
# get nearby locations to this location
nearby_locations = Location.within(self.lat, self.lon, meters)
# if there are nearby locations
if len(nearby_locations) > 0:
self.use_invalid = True
if self.use_invalid_reason is None:
self.use_invalid_reason = '[Same field as another location]'
else:
self.use_invalid_reason += '[Same field as another location]'
@classmethod
def within(cls, lat, lon, meters):
"""
Finds all samples within a radius of x meters from lat lon pair.
:param lat:
:param lon:
:param meters:
:return:
"""
assert isinstance(meters, (int, long, float)), 'lat not number'
assert isinstance(lat, (int, long, float)), 'lat not number'
assert isinstance(lon, (int, long, float)), 'lon not number'
assert meters > 0
assert abs(lat) < 90, 'lat exceeds bounds'
assert abs(lon) < 180, 'lon exceeds bounds'
# TODO paramterize
sql = "select * from (SELECT * FROM location WHERE abs(lon - %f) < %f and abs(lat - %f) < %f) as pt where st_distance_sphere(st_makepoint(pt.lon, pt.lat), st_makepoint(%f,%f)) " % (
float(lon), 0.1, float(lat), 0.1, float(lon), float(lat))
sql += "< %d " % meters
return db.session.query(Location).from_statement(text(sql)).all()
def offset(self, bearing, meters):
"""
Offsets the location to center of area.
:param bearing: direction float
:param meters: distance int
:return: None
"""
km = float(meters) / 1000.0
self.lat, self.lon = get_destination(self.lat, self.lon, bearing, km)
User = Table(
'users',
metadata,
Column('uuid', UUID(), primary_key=True),
Column('username', String(255), nullable=False),
Column('name', String(255), nullable=False),
Column('company', String(255), nullable=True),
Column('password', String(128), nullable=False),
Column('created_at', DateTime, nullable=False, server_default=func.now()),
Column('updated_at',
DateTime,
nullable=False,
server_default=func.now(),
onupdate=func.now()),
Column('deleted_at', DateTime, nullable=True),
UniqueConstraint('username', name='uq_user_username'),
)
CreditCard = table(
'credit_card',
Column('brand', String(20),
nullable=False), # The brand of the card, like 'visa'
Column('card_id', String(100),
nullable=False), # The ID of the card from Stripe
Column('country', String(1024),
nullable=False), # The Country of the card, like 'US'
Column('cvc_check', String(100),
nullable=False), # The CVC value from Stripe, like 'unchecked'
Column('expiration_month', Integer(),
nullable=False), # The month the card expires
Column('expiration_year', Integer(),
def candidate_subclass(class_name,
args,
table_name=None,
cardinality=None,
values=None):
"""
Creates and returns a Candidate subclass with provided argument names,
which are Context type. Creates the table in DB if does not exist yet.
Import using:
.. code-block:: python
from Asterisk.models import candidate_subclass
:param class_name: The name of the class, should be "camel case" e.g.
NewCandidate
:param args: A list of names of consituent arguments, which refer to the
Contexts--representing mentions--that comprise the candidate
:param table_name: The name of the corresponding table in DB; if not
provided, is converted from camel case by default, e.g. new_candidate
:param cardinality: The cardinality of the variable corresponding to the
Candidate. By default is 2 i.e. is a binary value, e.g. is or is not
a true mention.
"""
if table_name is None:
table_name = camel_to_under(class_name)
# If cardinality and values are None, default to binary classification
if cardinality is None and values is None:
values = [True, False]
cardinality = 2
# Else use values if present, and validate proper input
elif values is not None:
if cardinality is not None and len(values) != cardinality:
raise ValueError("Number of values must match cardinality.")
if None in values:
raise ValueError("`None` is a protected value.")
# Note that bools are instances of ints in Python...
if any(
[isinstance(v, int) and not isinstance(v, bool) for v in values]):
raise ValueError(
"Default usage of values is consecutive integers. Leave values unset if attempting to define values as integers."
)
cardinality = len(values)
# If cardinality is specified but not values, fill in with ints
elif cardinality is not None:
values = list(range(cardinality))
class_spec = (args, table_name, cardinality, values)
if class_name in candidate_subclasses:
if class_spec == candidate_subclasses[class_name][1]:
return candidate_subclasses[class_name][0]
else:
raise ValueError('Candidate subclass ' + class_name +
' already exists in memory with incompatible ' +
'specification: ' +
str(candidate_subclasses[class_name][1]))
else:
# Set the class attributes == the columns in the database
class_attribs = {
# Declares name for storage table
'__tablename__':
table_name,
# Connects candidate_subclass records to generic Candidate records
'id':
Column(Integer,
ForeignKey('candidate.id', ondelete='CASCADE'),
primary_key=True),
# Store values & cardinality information in the class only
'values':
values,
'cardinality':
cardinality,
# Polymorphism information for SQLAlchemy
'__mapper_args__': {
'polymorphic_identity': table_name
},
# Helper method to get argument names
'__argnames__':
args,
}
# Create named arguments, i.e. the entity mentions comprising the relation
# mention
# For each entity mention: id, cid ("canonical id"), and pointer to Context
unique_args = []
for arg in args:
# Primary arguments are constituent Contexts, and their ids
class_attribs[arg + '_id'] = Column(Integer,
ForeignKey('context.id',
ondelete='CASCADE'),
index=True)
class_attribs[arg] = relationship(
'Context',
backref=backref(table_name + '_' + arg + 's',
cascade_backrefs=False,
cascade='all, delete-orphan'),
cascade_backrefs=False,
foreign_keys=class_attribs[arg + '_id'])
unique_args.append(class_attribs[arg + '_id'])
# Canonical ids, to be set post-entity normalization stage
class_attribs[arg + '_cid'] = Column(String)
# Add unique constraints to the arguments
class_attribs['__table_args__'] = (UniqueConstraint(*unique_args), )
# Create class
C = type(class_name, (Candidate, ), class_attribs)
# Create table in DB
if not Asterisk_engine.dialect.has_table(Asterisk_engine, table_name):
C.__table__.create(bind=Asterisk_engine)
candidate_subclasses[class_name] = C, class_spec
return C
class LocalProductCompany3(Base1, ProductCompanyFrom):
__tablename__ = "mf_product_company2"
__table_args__ = (UniqueConstraint('product', 'company', 'gw_link'), )
from sqlalchemy import Column, String, UniqueConstraint, Enum
from sqlalchemy.orm import relationship
from fixtures.academic_degrees import EducationLevel
from provider.models.base import Base, make_join_table
provider_degree_table = make_join_table("provider", "degree")
class Degree(Base):
acronym = Column(String(16), unique=True, nullable=False)
name = Column(String(), nullable=False)
level = Column(Enum(EducationLevel), nullable=False)
providers = relationship("Provider",
secondary=provider_degree_table,
back_populates="degrees")
UniqueConstraint(Degree.acronym, Degree.name)
UniqueConstraint(Degree.acronym, Degree.level)
class ServerProductCompany(Base, ProductCompanyTo):
__tablename__ = "hub_products_test"
__table_args__ = (UniqueConstraint('name', 'company_name', 'website'), )
class User(ModelBase, BaseUser, UserMixin):
wifi_passwd_hash = Column(String)
# one to one from User to Account
account_id = Column(Integer,
ForeignKey("account.id"),
nullable=False,
index=True)
account = relationship("Account", backref=backref("user", uselist=False))
unix_account_id = Column(Integer,
ForeignKey('unix_account.id'),
nullable=True,
unique=True)
unix_account = relationship('UnixAccount') # backref not really needed.
address_id = Column(Integer,
ForeignKey(Address.id),
index=True,
nullable=False)
address = relationship(Address, backref=backref("inhabitants"))
room = relationship("Room",
backref=backref("users", viewonly=True),
sync_backref=False)
email_forwarded = Column(Boolean, server_default='True', nullable=False)
password_reset_token = Column(String, nullable=True)
def __init__(self, **kwargs):
password = kwargs.pop('password', None)
wifi_password = kwargs.pop('password', None)
super(User, self).__init__(**kwargs)
if password is not None:
self.password = password
if wifi_password is not None:
self.wifi_password = wifi_password
@hybrid_property
def has_custom_address(self):
"""Whether the user's address differs from their room's address.
If no room is assigned, returns ``False``.
"""
return self.address != self.room.address if self.room else False
# noinspection PyMethodParameters
@has_custom_address.expression
def has_custom_address(cls):
return and_(
cls.room_id.isnot(None),
exists(
select([
null()
]).select_from(Room).where(Room.id == cls.room_id).where(
Room.address_id != cls.address_id)))
@validates('login')
def validate_login(self, _, value):
assert not has_identity(
self
), "user already in the database - cannot change login anymore!"
return super(User, self).validate_login(_, value)
property_groups = relationship("PropertyGroup",
secondary=lambda: Membership.__table__,
viewonly=True)
@hybrid_method
def traffic_for_days(self, days):
from pycroft.model.traffic import TrafficVolume
return sum(
v.amount
for v in TrafficVolume.q.filter_by(user_id=self.id).filter(
TrafficVolume.timestamp >= (session.utcnow() -
timedelta(days - 1)).date()))
@traffic_for_days.expression
def traffic_for_days(self, days):
from pycroft.model.traffic import TrafficVolume
return select([func.sum(TrafficVolume.amount).label('amount')]) \
.where(
TrafficVolume.timestamp >= (session.utcnow() - timedelta(days-1)).date()
.where(TrafficVolume.user_id == self.id))
#: This is a relationship to the `current_property` view filtering out
#: the entries with `denied=True`.
current_properties = relationship(
'CurrentProperty',
primaryjoin='and_(User.id == foreign(CurrentProperty.user_id),'
'~CurrentProperty.denied)',
viewonly=True)
#: This is a relationship to the `current_property` view ignoring the
#: `denied` attribute.
current_properties_maybe_denied = relationship(
'CurrentProperty',
primaryjoin='User.id == foreign(CurrentProperty.user_id)',
viewonly=True)
@property
def current_properties_set(self) -> Set[str]:
"""A type-agnostic property giving the granted properties as a set of string.
Utilized in the web component's access control mechanism.
"""
return {p.property_name for p in self.current_properties}
@property
def wifi_password(self):
"""Store a hash of a given plaintext passwd for the user.
"""
if self.wifi_passwd_hash is not None and self.wifi_passwd_hash.startswith(
clear_password_prefix):
return self.wifi_passwd_hash.replace(clear_password_prefix, '', 1)
raise ValueError("Cleartext password not available.")
@hybrid_property
def has_wifi_access(self):
return self.wifi_passwd_hash is not None
@wifi_password.setter
def wifi_password(self, value):
self.wifi_passwd_hash = cleartext_password(value)
@staticmethod
def verify_and_get(login, plaintext_password):
try:
user = User.q.filter(User.login == func.lower(login)).one()
except NoResultFound:
return None
else:
return user if user.check_password(plaintext_password) else None
@hybrid_method
def active_memberships(self,
when: Optional[Interval] = None
) -> List[Membership]:
if when is None:
now = session.utcnow()
when = single(now)
return [
m for m in self.memberships
if when.overlaps(closed(m.begins_at, m.ends_at))
]
@active_memberships.expression
def active_memberships(cls, when=None):
return select([Membership]).select_from(join(cls, Membership)).where(
Membership.active(when))
@hybrid_method
def active_property_groups(self,
when: Optional[Interval] = None
) -> List[PropertyGroup]:
return object_session(self).query(PropertyGroup).join(
Membership).filter(Membership.active(when),
Membership.user_id == self.id).all()
@active_property_groups.expression
def active_property_groups(cls, when=None):
return select([PropertyGroup]).select_from(
join(PropertyGroup,
Membership).join(cls)).where(Membership.active(when))
@hybrid_method
def member_of(self,
group: PropertyGroup,
when: Optional[Interval] = None) -> bool:
return group in self.active_property_groups(when)
@member_of.expression
def member_of(cls, group, when=None):
return exists(
select([null()]).select_from(
PropertyGroup.__table__.join(
Membership.__table__,
PropertyGroup.id == Membership.group_id)).where(
and_(Membership.user_id == cls.id,
PropertyGroup.id == group.id,
Membership.active(when))))
@hybrid_method
def has_property(self,
property_name: str,
when: Optional[Interval] = None) -> bool:
if when is None:
now = session.utcnow()
when = single(now)
prop_granted_flags = [
group.property_grants[property_name]
for group in self.active_property_groups(when)
if property_name in group.property_grants
]
# In case of prop_granted_flags = []: Return False
# Else: Return True if all elements of prop_granted_flags are True
return all(prop_granted_flags) and any(prop_granted_flags)
@has_property.expression
def has_property(cls, prop, when=None):
# TODO Use joins
property_granted_select = select(
[null()],
from_obj=[
Property.__table__, PropertyGroup.__table__,
Membership.__table__
]).where(
and_(Property.name == prop,
Property.property_group_id == PropertyGroup.id,
PropertyGroup.id == Membership.group_id,
Membership.user_id == cls.id, Membership.active(when)))
#.cte("property_granted_select")
return and_(
not_(
exists(
property_granted_select.where(
Property.granted == false()))),
exists(property_granted_select.where(Property.granted == true()))
).self_group().label("has_property_" + prop)
@property
def permission_level(self) -> int:
return max((membership.group.permission_level
for membership in self.active_memberships()),
default=0)
@property
def email_internal(self):
return "{}@agdsn.me".format(self.login)
__table_args__ = (UniqueConstraint('swdd_person_id'), )
class LocalProject1(Base1, ProjectFrom):
__tablename__ = "cp_crawler_project"
__table_args__ = (UniqueConstraint('source', 'company', 'product'), )
class User(Base, BaseMixin, UserMixin):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
email = Column(String, unique=False)
tel = Column(String, unique=False)
password_hash = Column(String, nullable=False)
activate_code = Column(String)
otp = Column(String)
otp_timestamp = Column(DateTime)
last_active_time = Column(DateTime, default=datetime.datetime.utcnow)
club_id = Column(Integer,
ForeignKey('club.id', ondelete='CASCADE'),
nullable=False)
__table_args__ = (UniqueConstraint('email', 'club_id'), )
roles = relationship("Role",
secondary=association_user_role,
back_populates="users")
orders = relationship("Order",
backref="user",
cascade="all, delete",
order_by="desc(Order.time)")
def has_otp(self):
return self.otp and self.otp_timestamp
def is_otp_expire(self):
now = datetime.datetime.utcnow()
total_secs = (now - self.otp_timestamp).total_seconds()
logger.debug("seconds passed")
logger.debug(total_secs)
if total_secs > 600:
return True
else:
return False
def generate_otp(self):
self.otp = random_digits(6)
self.otp_timestamp = datetime.datetime.utcnow()
def use_one_time_password(self, otp):
if not self.otp_timestamp:
raise ValueError(_("Invalid one time password"))
now = datetime.datetime.utcnow()
if (now - self.otp_timestamp).total_seconds() > 600:
raise PasswordExpire()
if otp != self.otp:
raise PasswordInvalid()
if otp == self.otp:
self.one_time_password = None
self.otp_timestamp = None
def total_order_value(self):
total = 0
for o in self.orders:
total += o.total_price()
return total
def verify_passwd(self, passwd):
if self.password_hash == passwd:
return True
# try otp
if self.has_otp():
try:
self.use_one_time_password(passwd)
return True
finally:
pass
return False
def is_active(self):
return False if self.activate_code else True
def activate(self, code):
if code == self.activate_code:
self.activate_code = ""
def update_active_time(self):
self.last_active_time = datetime.datetime.now()
name = Column(AqStr(64), nullable=False)
vendor_id = Column(Integer, ForeignKey('vendor.id',
name='model_vendor_fk'),
nullable=False)
machine_type = Column(AqStr(16), nullable=False)
creation_date = deferred(Column(DateTime, default=datetime.now, nullable=False))
comments = deferred(Column(String(255)))
vendor = relation(Vendor)
model = Model.__table__
model.primary_key.name='model_pk'
model.append_constraint(UniqueConstraint('name','vendor_id',
name='model_name_vendor_uk'))
table = model
def populate(sess, *args, **kw):
mlist=sess.query(Model).all()
if not mlist:
f = [['ibm', 'hs20-884345u', 'blade'],
['ibm', 'ls20-8850pap', 'blade'],
['ibm', 'hs21-8853l5u', 'blade'],
['ibm', 'bce', 'chassis'],
['ibm', 'bch', 'chassis'],
['ibm', 'dx320-6388ac1', 'rackmount'], #one of the 4 in 1 types
['ibm', 'dx320-6388dau', 'rackmount'],
