def save_lvar_state(item, db=None):
dbconn = db if db else eva.core.db()
try:
_id = item.full_id if \
eva.core.config.enterprise_layout else item.item_id
if dbconn.execute(
sql('update lvar_state set set_time=:t,' +
' status=:status, value=:value where id=:id'),
t=item.set_time,
status=item.status,
value=item.value,
id=_id).rowcount:
logging.debug('%s state updated in db' % item.oid)
else:
dbconn.execute(
sql('insert into lvar_state (id, set_time, status, value) ' +
'values(:id, :t, :status, :value)'),
id=_id,
t=item.set_time,
status=item.status,
value=item.value)
logging.debug('{} state inserted into db'.format(item.oid))
return True
except:
logging.critical('db error')
eva.core.critical()
return False
def save_item_state(item, db=None):
dbconn = db if db else eva.core.db()
try:
_id = item.full_id if \
eva.core.config.enterprise_layout else item.item_id
if dbconn.execute(sql(
'update state set status=:status, value=:value where id=:id'),
status=item.status,
value=item.value,
id=_id).rowcount:
logging.debug('{} state updated in db'.format(item.oid))
else:
tp = ''
if item.item_type == 'unit':
tp = 'U'
elif item.item_type == 'sensor':
tp = 'S'
dbconn.execute(sql('insert into state (id, tp, status, value) ' +
'values(:id, :tp, :status, :value)'),
id=_id,
tp=tp,
status=item.status,
value=item.value)
logging.debug('{} state inserted into db'.format(item.oid))
return True
except:
logging.critical('db error')
return False
def create_user(user=None, password=None, key=None):
if user is None or password is None or key is None:
return False
kk = key if isinstance(key, list) else key.split(',')
for k in kk:
if k not in apikey.keys_by_id:
raise ResourceNotFound(f'API key {k}')
try:
dbconn = userdb()
row = dbconn.execute(sql('select k from users where u = :u'),
u=user).fetchone()
except:
eva.core.report_userdb_error()
if row:
raise ResourceAlreadyExists
try:
dbconn.execute(sql('insert into users(u, p, k) values (:u, :p, :k)'),
u=user,
p=crypt_password(password),
k=','.join(kk))
logging.info('User {} created, key: {}'.format(user, ','.join(kk)))
except:
eva.core.report_userdb_error()
return None
run_hook('create', user, password)
return {'user': user, 'key_id': key}
def save(self):
if not self.dynamic:
return False
data = self.serialize()
for d in [
'items', 'groups', 'items_ro', 'groups_ro', 'allow',
'hosts_allow', 'hosts_assign', 'pvt', 'rpvt'
]:
data[d] = ','.join(data[d])
dbconn = userdb()
try:
if not self.in_db:
# if save on exit is set, deleted key with the same name could
# still be present in the database
dbconn.execute(sql('delete from apikeys where k_id=:k_id'),
k_id=data['id'])
dbconn.execute(
sql('insert into apikeys(k_id, k, m, s, i,' +
' g, i_ro, g_ro, a,hal, has, pvt, rpvt) values ' +
'(:k_id, :k, :m, :s, :i, :g, :i_ro, :g_ro, :a, ' +
':hal, :has, :pvt, :rpvt)'),
k_id=data['id'],
k=data['key'],
m=1 if data['master'] else 0,
s=1 if data['sysfunc'] else 0,
i=data['items'],
g=data['groups'],
i_ro=data['items_ro'],
g_ro=data['groups_ro'],
a=data['allow'],
hal=data['hosts_allow'],
has=data['hosts_assign'],
pvt=data['pvt'],
rpvt=data['rpvt'])
else:
dbconn.execute(
sql('update apikeys set k=:k, s=:s, i=:i, g=:g, ' +
'i_ro=:i_ro, g_ro=:g_ro, a=:a, ' +
'hal=:hal, has=:has, pvt=:pvt, rpvt=:rpvt where ' +
'k_id=:k_id'),
k=self.key,
s=1 if data['sysfunc'] else 0,
i=data['items'],
g=data['groups'],
i_ro=data['items_ro'],
g_ro=data['groups_ro'],
a=data['allow'],
hal=data['hosts_allow'],
has=data['hosts_assign'],
pvt=data['pvt'],
rpvt=data['rpvt'],
k_id=data['id'])
except:
eva.core.report_userdb_error()
self.in_db = True
return True
def db_time_step_get(time_step_id):
time_step = ENGINE.execute(sql('''
SELECT timeStepId, unit, multiplier, divider
FROM time_steps WHERE timeStepId=:time_step_id
'''),
time_step_id=time_step_id).fetchone()
return time_step
def timestep_delete(time_step_id):
ENGINE.execute(sql('''
DELETE FROM time_steps
WHERE timeStepId=:time_step_id
'''),
time_step_id=time_step_id)
return jsonify(time_step_id)
def api_log_insert(call_id,
gw=None,
ip=None,
auth=None,
u=None,
utp=None,
ki=None,
func=None,
params=None):
dbconn = userdb()
dbt = dbconn.begin()
try:
dbconn.execute(sql(
'insert into api_log(id, t, gw, ip, auth, u, utp, ki, func, params)'
' values (:i, :t, :gw, :ip, :auth, :u, :utp, :ki, :func, :params)'
),
i=call_id,
t=time.time(),
gw=gw,
ip=ip,
auth=auth,
u=u,
utp=utp,
ki=ki,
func=func,
params=rapidjson.dumps(params)[:512])
dbt.commit()
except:
dbt.rollback()
logging.error('Unable to insert API call info into DB')
eva.core.log_traceback()
def db_parameter_get(parameter_id):
parameter = ENGINE.execute(sql('''
SELECT parameterId, variable, unit, parameterType
FROM parameters WHERE parameterId=:parameter_id
'''),
parameter_id=parameter_id).fetchone()
return parameter
def db_parameter_create(conn, data):
conn.execute(
sql('''
INSERT INTO parameters (parameterId, variable, unit, parameterType)
VALUES (:parameterId, :variable, :unit, :parameterType)
'''), **data)
return data
def db_location_create(conn, data):
assert 'name' in data and 'lat' in data and 'lon' in data, 'name, lat and lon is required for Location.'
conn.execute(sql('''
INSERT INTO locations (locationId, name, lat, lon)
VALUES (:locationId, :name, :lat, :lon)
'''), **data)
return data
def authenticate(user=None, password=None):
def _format_key(key):
try:
if isinstance(key, list):
kk = key
else:
kk = key.split(',')
if len(kk) == 1:
return kk[0]
else:
return apikey.create_combined_key(kk)
except Exception as e:
logging.error(e)
raise
if user is None or password is None:
raise AccessDenied('No login/password provided')
dbconn = userdb()
try:
r = dbconn.execute(sql('select k from users where u = :u and p = :p'),
u=user,
p=crypt_password(password)).fetchone()
except:
eva.core.report_userdb_error()
if r:
return _format_key(r.k), None
else:
k = msad_authenticate(user, password)
if k is None:
raise AccessDenied('Authentication failure')
else:
logging.debug(
f'user {user} authenticated via active directory, key id: {k}')
return _format_key(k), 'msad'
def parameter_delete(parameter_id):
ENGINE.execute(sql('''
DELETE FROM parameters
WHERE parameterId=:parameter_id
'''),
parameter_id=parameter_id)
return jsonify(parameter_id)
def get_network_data_ranges() -> list[NetworkDataDateRanges]:
"""Runs a query to get the network data ranges"""
engine = get_database_engine()
stmt = sql("""
select
fs.network_id,
min(fs.trading_interval) as scada_min,
max(fs.trading_interval) as scada_max
from facility_scada fs
where
fs.is_forecast is FALSE
group by fs.network_id;
""")
with engine.connect() as c:
results = list(c.execute(stmt))
if not results:
raise Exception(
"No results for data range query in update_network_data_ranges")
models = [
NetworkDataDateRanges(network=i[0], data_min=i[1], data_max=i[2])
for i in results
]
return models
def load_keys_from_db():
_keys = {}
_keys_by_id = {}
dbconn = userdb()
meta = sa.MetaData()
t_apikeys = sa.Table('apikeys', meta,
sa.Column('k_id', sa.String(64), primary_key=True),
sa.Column('k', sa.String(64)),
sa.Column('m', sa.Integer),
sa.Column('s', sa.Integer),
sa.Column('i', sa.String(1024)),
sa.Column('g', sa.String(1024)),
sa.Column('i_ro', sa.String(1024)),
sa.Column('g_ro', sa.String(1024)),
sa.Column('a', sa.String(256)),
sa.Column('hal', sa.String(1024)),
sa.Column('has', sa.String(1024)),
sa.Column('pvt', sa.String(1024)),
sa.Column('rpvt', sa.String(1024)))
try:
meta.create_all(dbconn)
except:
logging.critical('unable to create apikeys table in db')
return _keys, _keys_by_id
try:
result = dbconn.execute(sql('select * from apikeys'))
while True:
r = result.fetchone()
if not r: break
key = APIKey(r.k, r.k_id)
key.sysfunc = True if val_to_boolean(r.s) else False
for i, v in {
'item_ids': 'i',
'groups': 'g',
'item_ids_ro': 'i_ro',
'groups_ro': 'g_ro',
'allow': 'a',
'pvt_files': 'pvt',
'rpvt_uris': 'rpvt'
}.items():
setattr(
key, i,
list(filter(None, [j.strip() for j in r[v].split(',')])))
_hosts_allow = list(
filter(None, [j.strip() for j in r.hal.split(',')]))
key.hosts_allow = [IPNetwork(h) for h in _hosts_allow]
_hosts_assign = list(
filter(None, [x.strip() for x in r.has.split(',')]))
key.hosts_assign = \
[ IPNetwork(h) for h in _hosts_assign ]
key.dynamic = True
key.in_db = True
_keys[key.key] = key
_keys_by_id[key.key_id] = key
except:
eva.core.report_userdb_error(raise_exeption=False)
return _keys, _keys_by_id
def timeseries_delete(timeseries_id):
timeseries = ENGINE.execute(sql('''
DELETE FROM timeseries
WHERE timeseriesId=:timeseries_id
'''),
timeseries_id=timeseries_id)
CACHE.delete(timeseries_id)
CACHE.delete(f'f-timeseries_id')
return jsonify(timeseries_id)
def location_point_create():
data = request.get_json()
with ENGINE.begin() as conn:
location_id = data.get('locationId')
exists_id = conn.execute(sql('''
SELECT locationId FROM locations WHERE locationId=:location_id
'''), location_id=location_id).fetchone()
assert exists_id is None, f'Location already exists: {location_id}'
db_location_create(conn, data)
return jsonify(data)
def save_item_state(item, db=None):
if eva.core.config.state_to_registry:
return save_item_state_to_registry(item)
dbconn = db if db else eva.core.db()
dbt = dbconn.begin()
try:
_id = item.full_id if \
eva.core.config.enterprise_layout else item.item_id
if dbconn.execute(sql('update state set status=:status, value=:value, '
'set_time=:set_time, '
'ieid_b=:ieid_b, ieid_i=:ieid_i where id=:id'),
set_time=item.set_time,
ieid_b=item.ieid[0],
ieid_i=item.ieid[1],
status=item.status,
value=item.value,
id=_id).rowcount:
logging.debug('{} state updated in db'.format(item.oid))
else:
tp = ''
if item.item_type == 'unit':
tp = 'U'
elif item.item_type == 'sensor':
tp = 'S'
dbconn.execute(sql(
'insert into state (id, tp, set_time,'
' ieid_b, ieid_i, status, value) '
'values(:id, :tp, :set_time, :ieid_b, :ieid_i, :status, :value)'
),
id=_id,
tp=tp,
set_time=item.set_time,
ieid_b=item.ieid[0],
ieid_i=item.ieid[1],
status=item.status,
value=item.value)
logging.debug('{} state inserted into db'.format(item.oid))
dbt.commit()
return True
except:
dbt.rollback()
logging.critical('db error')
return False
def msad_get_cached_credentials(username, password):
if _d.msad_cache_time <= 0:
return
logging.debug(f'getting cached credentials for {username}')
r = userdb().execute(
sql('select cn from msad_cache where u=:u and p=:p and t>=:t'),
u=username,
p=crypt_password(password),
t=time.time() - _d.msad_cache_time).fetchone()
return r.cn if r else None
def msad_cache_cleaner(**kwargs):
logging.debug('cleaning MSAD cache')
dbconn = userdb()
dbt = dbconn.begin()
try:
dbconn.execute(sql('delete from msad_cache where t < :t'),
t=time.time() - _d.msad_cache_time)
dbt.commit()
except:
dbt.rollback()
raise
def save():
for i, k in keys_by_id.copy().items():
if k.config_changed and not k.save():
return False
dbconn = userdb()
try:
for k in keys_to_delete:
dbconn.execute(sql('delete from apikeys where k_id=:k_id'), k_id=k)
return True
except:
eva.core.report_db_error(raise_exeption=False)
def parameter_create():
data = request.get_json()
with ENGINE.begin() as conn:
parameter_id = data.get('parameterId')
exists_id = conn.execute(sql('''
SELECT parameterId FROM parameters WHERE parameterId=:parameter_id
'''),
parameter_id=parameter_id).fetchone()
assert exists_id is None, f'Parameter already exists: {parameter_id}'
db_parameter_create(conn, data)
return jsonify(data)
def timestep_create():
data = request.get_json()
with ENGINE.begin() as conn:
time_step_id = data.get('timeStepId')
exists_id = conn.execute(sql('''
SELECT timeStepId FROM time_steps WHERE timeStepId=:time_step_id
'''),
time_step_id=time_step_id).fetchone()
assert exists_id is None, f'TimeStep already exists: {time_step_id}'
db_time_step_create(conn, data)
return jsonify(data)
def db_time_step_create(conn, data):
if 'multiplier' not in data:
data['multiplier'] = 0
if 'divider' not in data:
data['divider'] = 0
conn.execute(
sql('''
INSERT INTO time_steps (timeStepId, unit, multiplier, divider)
VALUES (:timeStepId, :unit, :multiplier, :divider)
'''), **data)
return data
def get_user(user=None):
if not user: return None
try:
dbconn = userdb()
result = []
row = dbconn.execute(sql('select u, k from users where u=:u'),
u=user).fetchone()
except:
eva.core.report_userdb_error()
if not row: raise ResourceNotFound
return {'user': row.u, 'key_id': row.k}
def db_regular_grid_get(location_id):
import json
location = ENGINE.execute(sql('''
SELECT locationId, gridType, rows, columns, geoDatum, dataType, data, description
FROM grids WHERE locationId=:location_id
'''), location_id=location_id).fetchone()
if location:
location = dict(location)
location[location['dataType']] = json.loads(location['data'])
del location['dataType']
del location['data']
return location
def location_regular_grid_create():
data = request.get_json()
with ENGINE.begin() as conn:
location_id = data.get('locationId', None)
exists_id = conn.execute(sql('''
SELECT locationId FROM grids WHERE locationId=:location_id AND gridType='Regular'
'''), location_id=location_id).fetchone()
assert exists_id is None, f'Grid already exists: {location_id}'
db_regular_grid_create(conn, data)
del data['dataType']
del data['data']
return jsonify(data)
def db_timeseries_get(timeseries_id):
timeseries = CACHE.get(timeseries_id)
print("cached:", timeseries)
if timeseries is None:
timeseries = ENGINE.execute(sql('''
SELECT timeseriesId, moduleId, valueType, parameterId, locationId, timeseriesType, timeStepId
FROM timeseries WHERE timeseriesId=:timeseries_id
'''),
timeseries_id=timeseries_id).fetchone()
print(timeseries)
if timeseries:
CACHE.set_timeseries(timeseries_id, **timeseries)
return timeseries
def timeseries_list():
# TODO: Limiting and Skipping
q = ''
if len(request.query_string):
for key in request.args.to_dict().keys():
q += f'{" AND" if len(q) else "WHERE"} {key}=:{key}'
timeseries = ENGINE.execute(
sql('''
SELECT timeseriesId, moduleId, valueType, parameterId, locationId, timeseriesType, timeStepId
FROM timeseries
''' + q), **request.args.to_dict()).fetchall()
return jsonify([dict(i) for i in timeseries])
def authenticate(user=None, password=None):
if user is None or password is None:
raise AccessDenied('No login/password provided')
dbconn = userdb()
try:
r = dbconn.execute(sql('select k from users where u = :u and p = :p'),
u=user,
p=crypt_password(password)).fetchone()
except:
eva.core.report_userdb_error()
if not r:
raise AccessDenied('Authentication failure')
return r.k
def set_user_key(user=None, key=None):
if user is None or key is None or key not in apikey.keys_by_id:
return None
try:
dbconn = userdb()
if dbconn.execute(sql('update users set k = :k where u = :u'),
k=key,
u=user).rowcount:
logging.info('user {} key {} is set'.format(user, key))
return True
except:
eva.core.report_userdb_error()
raise ResourceNotFound
