def __saveMasarData(conn, eventid, datas):
"""
save data of masar service, and associated those data with a given event id.
The data format is a tuple array like
[('pv name', 'string value', 'double value', 'long value', 'dbr type', 'isConnected',
'secondsPastEpoch', 'nanoSeconds', 'timeStampTag', 'alarmSeverity', 'alarmStatus', 'alarmMessage',
'is_array', 'array_value')]
Return masar_data_id[].
"""
checkConnection(conn)
sql = '''insert into masar_data
(masar_data_id, service_event_id, pv_name, s_value, d_value, l_value, dbr_type, isConnected,
ioc_timestamp, ioc_timestamp_nano, timestamp_tag, severity, status, alarmMessage, is_array)
values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) '''
masarid = []
try:
cur = conn.cursor()
for data in datas:
cur.execute(sql, (None, eventid, data[0], data[1], data[2], data[3], data[4], data[5], data[6], data[7], data[8], data[9], data[10], data[11], data[12]))
data_id = cur.lastrowid
if data[12]:
# Here we force pickle to use the efficient binary protocol
# (protocol=2). This means you absolutely must use an SQLite BLOB field
# and make sure you use sqlite3.Binary() to bind a BLOB parameter.
cur.execute("update masar_data set array_value = ? where masar_data_id = ?", (sqlite3.Binary(pickle.dumps(data[13], protocol=2)), data_id,))
masarid.append(data_id)
except:
raise
return masarid
def saveServiceConfigProp(conn, propname=None, propvalue=None, servicename=None, configname=None):
"""
save a service config property, for example, which system this config belongs to.
>>> import sqlite3
>>> from pymasarsqlite.service.service import (saveService, retrieveServices)
>>> from pymasarsqlite.service.serviceconfig import (saveServiceConfig, retrieveServiceConfigs)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> conn = sqlite3.connect(":memory:")
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> saveService(conn, 'masar1', desc='non-empty description')
1
>>> saveService(conn, 'masar2', desc='non-empty description')
2
>>> saveServiceConfig(conn, 'masar1', 'orbit C01', 'BPM horizontal readout for storage ring')
1
>>> saveServiceConfig(conn, 'masar2', 'booster orbit', 'Horizontal orbit for booster ring')
2
>>> saveServiceConfigProp(conn, 'system', 'SR', 'masar1', 'orbit C01')
1
>>> saveServiceConfigProp(conn, 'system', 'booster', 'masar2', 'booster orbit')
2
>>> saveServiceConfigProp(conn, 'system', 'booster', 'masar2', 'booster orbit')
2
>>> conn.close()
"""
if servicename is None or configname is None:
raise Exception("service or service config is empty. Can not associate the event with service and its config.")
if propname is None or propvalue is None:
raise Exception("Property name and value can not be empty.")
checkConnection(conn)
serviceconfigid = retrieveServiceConfigs(conn, servicename=servicename, configname=configname)[1:]
if len(serviceconfigid) > 0:
serviceconfigid = serviceconfigid[0][0]
else:
raise Exception("Can not find service config (%s) with service (%s)" % (configname, servicename))
sql = """
insert into service_config_prop (service_config_prop_id, service_config_id, service_config_prop_name, service_config_prop_value)
values (?, ?, ?, ?)
"""
try:
cur = conn.cursor()
# check this entity exists or not.
cur.execute(
"select service_config_prop_id from service_config_prop where service_config_id = ? and service_config_prop_name = ? and service_config_prop_value = ?",
(serviceconfigid, propname, propvalue),
)
result = cur.fetchone()
if result is None:
cur.execute(sql, (None, serviceconfigid, propname, propvalue))
result = cur.lastrowid
else:
result = result[0]
except sqlite3.Error, e:
print("Error %s" % e.args[0])
raise
def saveServiceConfigProp(conn, propname=None, propvalue=None, servicename=None, configname=None):
"""
save a service config property, for example, which system this config belongs to.
>>> import sqlite3
>>> from pymasarsqlite.service.service import (saveService, retrieveServices)
>>> from pymasarsqlite.service.serviceconfig import (saveServiceConfig, retrieveServiceConfigs)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> conn = sqlite3.connect(":memory:")
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> saveService(conn, 'masar1', desc='non-empty description')
1
>>> saveService(conn, 'masar2', desc='non-empty description')
2
>>> saveServiceConfig(conn, 'masar1', 'orbit C01', 'BPM horizontal readout for storage ring')
1
>>> saveServiceConfig(conn, 'masar2', 'booster orbit', 'Horizontal orbit for booster ring')
2
>>> saveServiceConfigProp(conn, 'system', 'SR', 'masar1', 'orbit C01')
1
>>> saveServiceConfigProp(conn, 'system', 'booster', 'masar2', 'booster orbit')
2
>>> saveServiceConfigProp(conn, 'system', 'booster', 'masar2', 'booster orbit')
2
>>> conn.close()
"""
if servicename is None or configname is None:
raise Exception('service or service config is empty. Can not associate the event with service and its config.')
if propname is None or propvalue is None:
raise Exception('Property name and value can not be empty.')
checkConnection(conn)
serviceconfigid = retrieveServiceConfigs(conn, servicename=servicename, configname=configname)[1:]
if len(serviceconfigid) > 0:
serviceconfigid = serviceconfigid[0][0]
else:
raise Exception('Can not find service config (%s) with service (%s)' %(configname, servicename))
sql = '''
insert into service_config_prop (service_config_prop_id, service_config_id, service_config_prop_name, service_config_prop_value)
values (?, ?, ?, ?)
'''
try:
cur = conn.cursor()
# check this entity exists or not.
cur.execute('select service_config_prop_id from service_config_prop where service_config_id = ? and service_config_prop_name = ? and service_config_prop_value = ?',
(serviceconfigid, propname, propvalue, ))
result = cur.fetchone()
if result is None:
cur.execute(sql, (None, serviceconfigid, propname, propvalue))
result = cur.lastrowid
else:
result = result[0]
except sqlite3.Error, e:
print ('Error %s' %e.args[0])
raise
def saveSnapshot(conn, data, servicename=None, configname=None, comment=None,approval=False):
"""
save a snapshot (masar event) with data.
The data format is a tuple array like
[('pv name', 'string value', 'double value', 'long value', 'dbr type', 'isConnected',
'secondsPastEpoch', 'nanoSeconds', 'timeStampTag', 'alarmSeverity', 'alarmStatus', 'alarmMessage',
'is_array', 'array_value')]
Return service_event_id, masar_data_id[].
>>> import sqlite3
>>> from pymasarsqlite.service.service import (saveService, retrieveServices)
>>> from pymasarsqlite.service.serviceconfig import (saveServiceConfig, retrieveServiceConfigs)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> conn = sqlite3.connect(":memory:")
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> saveService(conn, 'masar1', desc='non-empty description')
1
>>> saveService(conn, 'masar2', desc='non-empty description')
2
>>> saveServiceConfig(conn, 'masar1', 'orbit C01', 'BPM horizontal readout for storage ring')
1
>>> saveServiceConfig(conn, 'masar1', 'orbit C02', 'BPM horizontal readout for storage ring')
2
>>> saveServiceConfig(conn, 'masar2', 'orbit C01', 'BPM horizontal readout for storage ring')
3
>>> saveServiceConfig(conn, 'masar2', 'orbit C02', 'BPM horizontal readout for storage ring')
4
>>> result = retrieveServiceConfigs(conn, servicename='masar1', configname='orbit C01')
>>> data = [('SR:C01-BI:G02A<BPM:L1>Pos-X','12.2', 12.2, 12, 6, 1, 435686768234, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G02A<BPM:L2>Pos-X', '12.2', 12.2, 12, 6, 1, 435686768234, 3452345098734, 0, 0, 0, "", 1, [1.2,2.3, 3.4, 4.5]),
... ('SR:C01-BI:G04A<BPM:M1>Pos-X', '12.2', 12.2, 12, 6, 1, 435686768234, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G04B<BPM:M1>Pos-X', '12.2', 12.2, 12, 6, 1, 435686768234, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G06B<BPM:H1>Pos-X', '12.2', 12.2, 12, 6, 1, 435686768234, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G06B<BPM:H2>Pos-X', '12.2', 12.2, 12, 6, 1, 435686768234, 3452345098734, 0, 0, 0, "", 0, [])]
>>> saveSnapshot(conn, data, servicename='masar1', configname='orbit C01', comment='a service event')
(1, [1, 2, 3, 4, 5, 6])
>>> data = [('SR:C01-BI:G02A<BPM:L1>Pos-X','12.2', 12.2, 12, 6, 1, 564562342566, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G02A<BPM:L2>Pos-X', '12.2', 12.2, 12, 6, 1, 564562342566, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G04A<BPM:M1>Pos-X', '12.2', 12.2, 12, 6, 1, 564562342566, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G04B<BPM:M1>Pos-X', '12.2', 12.2, 12, 6, 1, 564562342566, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G06B<BPM:H1>Pos-X', '12.2', 12.2, 12, 6, 1, 564562342566, 3452345098734, 0, 0, 0, "", 1, [1.2,2.3, 3.4, 4.5]),
... ('SR:C01-BI:G06B<BPM:H2>Pos-X', '12.2', 12.2, 12, 6, 1, 564562342566, 3452345098734, 0, 0, 0, "", 0, [])]
>>> saveSnapshot(conn, data, servicename='masar1', configname='orbit C01', comment='a service event')
(2, [7, 8, 9, 10, 11, 12])
>>> conn.close()
"""
checkConnection(conn)
eventid = saveServiceEvent(conn, servicename, configname, comment=comment, approval=approval)
masarid = None
try:
masarid = __saveMasarData(conn, eventid, data)
except sqlite3.Error, e:
print ('Error %s' %e.args[0])
raise
def saveServiceEvent(conn, servicename, configname, comment=None, approval=False, username=None):
"""
save an event config, and associate this event with given service and service config.
>>> import sqlite3
>>> from pymasarsqlite.service.service import (saveService, retrieveServices)
>>> from pymasarsqlite.service.serviceconfig import (saveServiceConfig)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> conn = sqlite3.connect(":memory:")
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> saveService(conn, 'masar1', desc='non-empty description')
1
>>> saveService(conn, 'masar2', desc='non-empty description')
2
>>> saveServiceConfig(conn, 'masar1', 'orbit C01', 'BPM horizontal readout for storage ring')
1
>>> saveServiceConfig(conn, 'masar1', 'orbit C02', 'BPM horizontal readout for storage ring')
2
>>> saveServiceConfig(conn, 'masar2', 'orbit C01', 'BPM horizontal readout for storage ring')
3
>>> saveServiceConfig(conn, 'masar2', 'orbit C02', 'BPM horizontal readout for storage ring')
4
>>> result = retrieveServiceConfigs(conn, servicename='masar1', configname='orbit C01')
>>> saveServiceEvent(conn, servicename='masar1', configname='orbit C01', comment='a service event')
1
>>> conn.close()
"""
if servicename is None or configname is None:
raise Exception('service or service config is empty. Can not associate the event with service and its config.')
checkConnection(conn)
if configname is None:
raise Exception("service config name is not specified for this event.")
serviceconfigid = retrieveServiceConfigs(conn, servicename=servicename, configname=configname)[1:]
if len(serviceconfigid) > 0:
serviceconfigid = serviceconfigid[0][0]
else:
raise Exception('Can not find service config (%s) with service (%s)' %(configname, servicename))
sql = '''
insert into service_event(service_event_id, service_config_id, service_event_user_tag, service_event_UTC_time, service_event_approval, service_event_user_name)
values (?, ?, ?, datetime('now'), ?, ?)
'''
try:
cur = conn.cursor()
# each service event is a individual entity. Do not check the existence.
cur.execute(sql, (None, serviceconfigid, comment, approval, username))
except sqlite3.Error, e:
print ('Error %s' %e.args[0])
raise
def retrievePvGroups(conn, pvgroupname=None):
"""
Retrieve pv groups with given name from pv_group table.
It returns a tuple with format (pv_group_id, pv_group_name, pv_group_func, pvg_creation_date, version)
>>> import sqlite3
>>> from pymasarsqlite.pvgroup.pvgroup import (savePvGroup, retrievePvGroups)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> conn = sqlite3.connect(':memory:')
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> name = 'masar'
>>> desc = 'this is my first pv group for masar service'
>>> savePvGroup(conn, name, func=desc)
[1]
>>> name = 'masar2'
>>> desc = 'this is my new pv group for masar service with same group name'
>>> savePvGroup(conn, name, func=desc)
[2]
>>> pvgroups = retrievePvGroups(conn)
>>> for pvgroup in pvgroups:
... print (pvgroup[0], pvgroup[1])
(1, u'masar')
(2, u'masar2')
>>> pvgroups = retrievePvGroups(conn, 'masar')
>>> for pvgroup in pvgroups:
... print (pvgroup[0], pvgroup[1])
(1, u'masar')
>>> pvgroups = retrievePvGroups(conn, 'masar2')
>>> for pvgroup in pvgroups:
... print (pvgroup[0], pvgroup[1])
(2, u'masar2')
>>> retrievePvGroups(conn, 'masar3')
[]
>>> conn.close()
"""
checkConnection(conn)
cur = conn.cursor()
if pvgroupname is None:
cur.execute(
'select pv_group_id, pv_group_name, pv_group_func, pvg_creation_date, version from pv_group'
)
else:
cur.execute(
'select pv_group_id, pv_group_name, pv_group_func, pvg_creation_date, version from pv_group where pv_group_name = ?',
(pvgroupname, ))
dataset = cur.fetchall()
return dataset
def retrieveServices(conn, servicename=None):
"""
Retrieve all services stored in service table.
for example:
>>> import sqlite3
>>> from pymasarsqlite.service.service import (saveService, retrieveServices)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> conn = sqlite3.connect(':memory:')
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> saveService(conn, 'masar1', desc='non-empty description')
1
>>> saveService(conn, 'masar2')
2
>>> retrieveServices(conn)
[(1, u'masar1', u'non-empty description'), (2, u'masar2', u'')]
>>> retrieveServices(conn, 'masar1')
[(1, u'masar1', u'non-empty description')]
>>> retrieveServices(conn, 'masar3')
[]
>>> conn.close()
"""
checkConnection(conn)
result = None
try:
cursor = conn.cursor()
if servicename is None:
cursor.execute(
'select service_id, service_name, service_desc from service')
else:
cursor.execute(
'select service_id, service_name, service_desc from service where service_name = ?',
(servicename, ))
result = cursor.fetchall()
# queryResponse = cursor.fetchall()
# for row in queryResponse:
# result.append(''.join(row[0])) #force result into a string
# desc.append(''.join(row[1]))
except sqlite3.Error, e:
raise Exception("Error %s:" % e.args[0])
sys.exit(1)
def saveService(conn, name, desc=''):
"""
Save list services into service table.
The key word looks like:
name='my service name', desc='description for this service'
for example:
>>> import sqlite3
>>> from pymasarsqlite.service.service import (saveService, retrieveServices)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> conn = sqlite3.connect(':memory:')
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> saveService(conn, 'masar', desc='an example')
1
>>> retrieveServices(conn, 'masar')
[(1, u'masar', u'an example')]
>>> conn.close()
"""
checkConnection(conn)
serviceId = None
try:
cursor = conn.cursor()
# check this entity exists or not.
cursor.execute(
'select service_id from service where service_name = ? and service_desc = ?',
(name, desc))
serviceId = cursor.fetchone()
if serviceId is None:
cursor.execute(
'insert into service(service_id, service_name, service_desc) values (?,?,?)',
(None, name, desc))
serviceId = cursor.lastrowid
else:
serviceId = serviceId[0]
except sqlite3.Error, e:
print("Error %s:" % e.args[0])
raise
sys.exit(1)
def retrieveServices(conn, servicename=None):
"""
Retrieve all services stored in service table.
for example:
>>> import sqlite3
>>> from pymasarsqlite.service.service import (saveService, retrieveServices)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> conn = sqlite3.connect(':memory:')
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> saveService(conn, 'masar1', desc='non-empty description')
1
>>> saveService(conn, 'masar2')
2
>>> retrieveServices(conn)
[(1, u'masar1', u'non-empty description'), (2, u'masar2', u'')]
>>> retrieveServices(conn, 'masar1')
[(1, u'masar1', u'non-empty description')]
>>> retrieveServices(conn, 'masar3')
[]
>>> conn.close()
"""
checkConnection(conn)
result = None
try:
cursor = conn.cursor()
if servicename is None:
cursor.execute('select service_id, service_name, service_desc from service')
else:
cursor.execute('select service_id, service_name, service_desc from service where service_name = ?', (servicename,))
result = cursor.fetchall()
# queryResponse = cursor.fetchall()
# for row in queryResponse:
# result.append(''.join(row[0])) #force result into a string
# desc.append(''.join(row[1]))
except sqlite3.Error, e:
raise Exception("Error %s:" % e.args[0])
sys.exit(1)
def saveService(conn, name, desc=''):
"""
Save list services into service table.
The key word looks like:
name='my service name', desc='description for this service'
for example:
>>> import sqlite3
>>> from pymasarsqlite.service.service import (saveService, retrieveServices)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> conn = sqlite3.connect(':memory:')
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> saveService(conn, 'masar', desc='an example')
1
>>> retrieveServices(conn, 'masar')
[(1, u'masar', u'an example')]
>>> conn.close()
"""
checkConnection(conn)
serviceId = None
try:
cursor = conn.cursor()
# check this entity exists or not.
cursor.execute('select service_id from service where service_name = ? and service_desc = ?', (name, desc))
serviceId = cursor.fetchone()
if serviceId is None:
cursor.execute('insert into service(service_id, service_name, service_desc) values (?,?,?)', (None, name, desc))
serviceId = cursor.lastrowid
else:
serviceId = serviceId[0]
except sqlite3.Error, e:
print ("Error %s:" % e.args[0])
raise
sys.exit(1)
def __saveMasarData(conn, eventid, datas):
"""
save data of masar service, and associated those data with a given event id.
The data format is a tuple array like
[('pv name', 'string value', 'double value', 'long value', 'dbr type', 'isConnected',
'secondsPastEpoch', 'nanoSeconds', 'timeStampTag', 'alarmSeverity', 'alarmStatus', 'alarmMessage',
'is_array', 'array_value')]
Return masar_data_id[].
"""
checkConnection(conn)
sql = '''insert into masar_data
(masar_data_id, service_event_id, pv_name, s_value, d_value, l_value, dbr_type, isConnected,
ioc_timestamp, ioc_timestamp_nano, timestamp_tag, severity, status, alarmMessage, is_array)
values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) '''
masarid = []
try:
cur = conn.cursor()
for data in datas:
cur.execute(sql, (None, eventid, data[0], data[1], data[2],
data[3], data[4], data[5], data[6], data[7],
data[8], data[9], data[10], data[11], data[12]))
data_id = cur.lastrowid
if data[12]:
# Here we force pickle to use the efficient binary protocol
# (protocol=2). This means you absolutely must use an SQLite BLOB field
# and make sure you use sqlite3.Binary() to bind a BLOB parameter.
cur.execute(
"update masar_data set array_value = ? where masar_data_id = ?",
(
sqlite3.Binary(pickle.dumps(data[13], protocol=2)),
data_id,
))
masarid.append(data_id)
except:
raise
return masarid
def __retrieveMasarData(conn, eventid):
checkConnection(conn)
sql = '''
select pv_name, s_value, d_value, l_value, dbr_type, isConnected,
ioc_timestamp, ioc_timestamp_nano, timestamp_tag, severity, status, alarmMessage,
is_array, array_value
from masar_data where service_event_id = ?
'''
data = None
try:
cur = conn.cursor()
cur.execute(sql, (eventid, ))
data = cur.fetchall()
for i in range(len(data)):
res = data[i]
if res[13] != None:
result = pickle.loads(str(res[13]))
else:
result = []
data[i] = data[i][:13] + (result[:], )
except:
raise
return data
def __retrieveMasarData(conn, eventid):
checkConnection(conn)
sql = '''
select pv_name, s_value, d_value, l_value, dbr_type, isConnected,
ioc_timestamp, ioc_timestamp_nano, timestamp_tag, severity, status, alarmMessage,
is_array, array_value
from masar_data where service_event_id = ?
'''
data = None
try:
cur=conn.cursor()
cur.execute(sql, (eventid,))
data = cur.fetchall()
for i in range(len(data)):
res = data[i]
if res[13] != None:
result = pickle.loads(str(res[13]))
else:
result = []
data[i]=data[i][:13]+ (result[:],)
except:
raise
return data
def retrieveServicePvGroups(conn, configname, servicename=None):
"""
Retrieve pv group names belonging to a given service config name.
Return a tuple array with format [(pv_group_id, pv_group_name, service_config_name, service_name)].
>>> import sqlite3
>>> conn = sqlite3.connect(':memory:')
>>> from pymasarsqlite.service.service import (saveService, retrieveServices)
>>> from pymasarsqlite.service.serviceconfig import (saveServicePvGroup, retrieveServicePvGroups)
>>> from pymasarsqlite.pvgroup.pvgroup import (savePvGroup, retrievePvGroups)
>>> from pymasarsqlite.db.masarsqlite import SQL
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> serviceName1 = 'masar'
>>> serviceDesc1 = 'masar service description example'
>>> saveService(conn, serviceName1, serviceDesc1)
1
>>> serviceName2 = 'model'
>>> serviceDesc2 = 'model service description example'
>>> saveService(conn, serviceName2, serviceDesc2)
2
>>> masarconf1 = 'orbit C01'
>>> masardesc1 = 'BPM horizontal readout for storage ring C01'
>>> masarconf2 = 'orbit C02'
>>> masardesc2 = 'BPM horizontal readout for storage ring C02'
>>> saveServiceConfig(conn, serviceName1, masarconf1, masardesc1)
1
>>> saveServiceConfig(conn, serviceName1, masarconf2, masardesc2)
2
>>> modelconf1 = 'model conf 1'
>>> modeldesc1 = 'model conf desc 1'
>>> modelconf2 = 'model conf 2'
>>> modeldesc2 = 'model conf desc 2'
>>> saveServiceConfig(conn, serviceName2, modelconf1, modeldesc1)
3
>>> saveServiceConfig(conn, serviceName2, modelconf2, modeldesc2)
4
>>> result = retrieveServiceConfigs(conn, servicename='masar', configname='orbit C01')
>>> pvgname1 = 'masar1group'
>>> pvgdesc1 = 'this is my first pv group for masar service'
>>> savePvGroup(conn, pvgname1, func=pvgdesc1)
[1]
>>> pvgname2 = 'masar2group'
>>> pvgdesc2 = 'this is my new pv group for masar service with same group name'
>>> savePvGroup(conn, pvgname2, func=pvgdesc2)
[2]
>>> pvgroups = retrievePvGroups(conn)
>>> for pvgroup in pvgroups:
... print (pvgroup[0], pvgroup[1])
1 masar1group
2 masar2group
>>> pvgroups = retrievePvGroups(conn, pvgname1)
>>> for pvgroup in pvgroups:
... print (pvgroup[0], pvgroup[1])
1 masar1group
>>> pvgroups = retrievePvGroups(conn, pvgname2)
>>> for pvgroup in pvgroups:
... print (pvgroup[0], pvgroup[1])
2 masar2group
>>> saveServicePvGroup(conn, masarconf1, [pvgname1, pvgname2])
[1, 2]
>>> saveServicePvGroup(conn, masarconf2, [pvgname1, pvgname2])
[3, 4]
>>> saveServicePvGroup(conn, modelconf1, [pvgname1, pvgname2])
[5, 6]
>>> saveServicePvGroup(conn, modelconf2, [pvgname1, pvgname2])
[7, 8]
>>> retrieveServicePvGroups(conn, masarconf1)
[(1, u'masar1group', u'orbit C01', u'masar'), (2, u'masar2group', u'orbit C01', u'masar')]
>>> retrieveServicePvGroups(conn, masarconf2, serviceName1)
[(1, u'masar1group', u'orbit C02', u'masar'), (2, u'masar2group', u'orbit C02', u'masar')]
>>> retrieveServicePvGroups(conn, modelconf1)
[(1, u'masar1group', u'model conf 1', u'model'), (2, u'masar2group', u'model conf 1', u'model')]
>>> conn.close()
"""
if configname is None:
raise Exception('service config name is not specified')
sys.exit()
checkConnection(conn)
sql = ''' select pv_group.pv_group_id, pv_group.pv_group_name, service_config.service_config_name, service.service_name
from pv_group
left join pvgroup__serviceconfig using (pv_group_id)
left join service_config using (service_config_id)
left join service using (service_id)
where service_config.service_config_name = ?'''
results = None
try:
cur = conn.cursor()
if servicename is None:
cur.execute(sql, (configname, ))
else:
services = retrieveServices(conn, servicename)
if len(services) == 0:
raise Exception('Given service (%s) does not exist.' %
servicename)
else:
sql = sql + ' and service_config.service_id = ?'
cur.execute(sql, (configname, services[0][0]))
results = cur.fetchall()
except sqlite3.Error, e:
print("Error %s:" % e.args[0])
raise
sys.exit(1)
def saveServiceConfig(conn,
servicename,
configname,
configdesc=None,
configversion=None,
system=None,
status="active"):
"""
Link config attributes like name, description, ... with a given service name.
The service config name for each different service has to be unique.
By default, version for a new configuration is set to be 1, which means active.
>>> import sqlite3
>>> from pymasarsqlite.service.service import (saveService, retrieveServices)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> conn = sqlite3.connect(":memory:")
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> saveService(conn, 'masar1', desc='non-empty description')
1
>>> saveService(conn, 'masar2', desc='non-empty description')
2
>>> saveServiceConfig(conn, 'masar1', 'orbit C01-C03', 'BPM horizontal readout for storage ring')
1
>>> #saveServiceConfig(conn, 'masar1', 'orbit C01-C03', 'BPM horizontal readout for storage ring')
>>> saveServiceConfig(conn, 'masar2', 'orbit C01-C03', 'BPM horizontal readout for storage ring')
2
>>> saveServiceConfig(conn, 'masar2', 'RF', 'Storage ring RF system snapshot', system='SR')
3
>>> conn.close()
"""
if configname is None:
raise Exception('Service config name is not given')
sys.exit()
if status not in ["active", "inactive"]:
raise ValueError("Service status has to be either active, or inactive")
checkConnection(conn)
serviceid = retrieveServices(conn, servicename)
if len(serviceid) == 0:
raise Exception(
'''service with given name ({0}) does not exist.'''.format(
servicename))
sys.exit()
else:
serviceid = serviceid[0][0]
serviceconfigid = None
try:
cur = conn.cursor()
cur.execute(
'select service_config_id from service_config where service_config_name = ? and service_id = ?',
(
configname,
serviceid,
))
result = cur.fetchone()
if result is None:
sql = 'insert into service_config (service_config_id, service_id, service_config_name, service_config_create_date, service_config_status '
if configdesc is None and configversion is None:
sql = sql + ') values(?,?,?,datetime("now"), ?)'
cur.execute(sql, (None, serviceid, configname, status))
elif configversion is None:
sql = sql + ' , service_config_desc ) values(?,?,?,datetime("now"),?,?)'
cur.execute(sql, (
None,
serviceid,
configname,
status,
configdesc,
))
else:
sql = sql + ' , service_config_desc, service_config_version ) values(?,?,?,datetime("now"),?, ?,?)'
cur.execute(sql, (
None,
serviceid,
configname,
status,
configdesc,
configversion,
))
serviceconfigid = cur.lastrowid
__saveConfigProp(cur, serviceconfigid, system)
else:
# @todo: identify service config with given name exist already?
serviceconfigid = result[0]
raise Exception('service config exists already.')
# cur.execute('select service_config_id from service_config where service_id = ?',(serviceid,))
# print (cur.fetchall())
except sqlite3.Error, e:
print("Error %s:" % e.args[0])
raise
sys.exit(1)
def saveServicePvGroup(conn, configname, pvgroups):
"""
Assign pv groups to to a service config.
>>> import sqlite3
>>> conn = sqlite3.connect(':memory:')
>>> from pymasarsqlite.service.service import (saveService, retrieveServices)
>>> from pymasarsqlite.service.serviceconfig import(saveServicePvGroup, retrieveServicePvGroups)
>>> from pymasarsqlite.pvgroup.pvgroup import (savePvGroup, retrievePvGroups)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> serviceName1 = 'masar'
>>> serviceDesc1 = 'masar service description example'
>>> saveService(conn, serviceName1, serviceDesc1)
1
>>> serviceName2 = 'model'
>>> serviceDesc2 = 'model service description example'
>>> saveService(conn, serviceName2, serviceDesc2)
2
>>> masarconf1 = 'orbit C01'
>>> masardesc1 = 'BPM horizontal readout for storage ring C01'
>>> masarconf2 = 'orbit C02'
>>> masardesc2 = 'BPM horizontal readout for storage ring C02'
>>> saveServiceConfig(conn, serviceName1, masarconf1, masardesc1)
1
>>> saveServiceConfig(conn, serviceName1, masarconf2, masardesc2)
2
>>> modelconf1 = 'model conf 1'
>>> modeldesc1 = 'model conf desc 1'
>>> modelconf2 = 'model conf 2'
>>> modeldesc2 = 'model conf desc 2'
>>> saveServiceConfig(conn, serviceName2, modelconf1, modeldesc1)
3
>>> saveServiceConfig(conn, serviceName2, modelconf2, modeldesc2)
4
>>> result = retrieveServiceConfigs(conn, servicename='masar', configname='orbit C01')
>>> pvgname1 = 'masar1group'
>>> pvgdesc1 = 'this is my first pv group for masar service'
>>> savePvGroup(conn, pvgname1, func=pvgdesc1)
[1]
>>> pvgname2 = 'masar2group'
>>> pvgdesc2 = 'this is my new pv group for masar service with same group name'
>>> savePvGroup(conn, pvgname2, func=pvgdesc2)
[2]
>>> pvgroups = retrievePvGroups(conn)
>>> for pvgroup in pvgroups:
... print (pvgroup[0], pvgroup[1])
1 masar1group
2 masar2group
>>> pvgroups = retrievePvGroups(conn, pvgname1)
>>> for pvgroup in pvgroups:
... print (pvgroup[0], pvgroup[1])
1 masar1group
>>> pvgroups = retrievePvGroups(conn, pvgname2)
>>> for pvgroup in pvgroups:
... print (pvgroup[0], pvgroup[1])
2 masar2group
>>> saveServicePvGroup(conn, masarconf1, [pvgname1, pvgname2])
[1, 2]
>>> conn.close()
"""
if configname is None or len(pvgroups) == 0:
raise Exception('service config name or service name is empty.')
checkConnection(conn)
# get service config id
serviceconfigid = retrieveServiceConfigs(conn, configname=configname)[1][0]
pvg_serviceconfig_ids = []
pvg_ids = []
for pvgroup in pvgroups:
pvg_id = retrievePvGroups(conn, pvgroup)
if len(pvg_id) > 0:
pvg_ids.append(retrievePvGroups(conn, pvgroup)[0][0])
else:
print('given pv group name (%s) does not exist.' % pvgroup)
raise Exception('given pv group name (%s) does not exist.' %
pvgroup)
try:
cur = conn.cursor()
sql = 'select pvgroup__serviceconfig_id from pvgroup__serviceconfig where service_config_id = ?'
cur.execute(sql, (serviceconfigid, ))
result = cur.fetchone()
if result is None:
sql = 'insert into pvgroup__serviceconfig (pvgroup__serviceconfig_id, pv_group_id, service_config_id) values (?,?,?)'
for pvg_id in pvg_ids:
cur.execute(sql, (None, pvg_id, serviceconfigid))
pvg_serviceconfig_ids.append(cur.lastrowid)
else:
#pvg_serviceconfig_ids = pvg_serviceconfig_ids[0]
raise Exception('Service config has associated pv groups already.')
except sqlite3.Error, e:
print('Error %s', e.arg[0])
raise
def updateServiceEvent(conn, eventid, comment=None, approval=False, username=None):
"""
update the comment, approval status, and add user name for an existing event.
>>> import sqlite3
>>> from pymasarsqlite.service.service import (saveService, retrieveServices)
>>> from pymasarsqlite.service.serviceconfig import (saveServiceConfig)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> conn = sqlite3.connect(":memory:")
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> saveService(conn, 'masar1', desc='non-empty description')
1
>>> saveService(conn, 'masar2', desc='non-empty description')
2
>>> saveServiceConfig(conn, 'masar1', 'orbit C01', 'BPM horizontal readout for storage ring')
1
>>> saveServiceConfig(conn, 'masar1', 'orbit C02', 'BPM horizontal readout for storage ring')
2
>>> saveServiceConfig(conn, 'masar2', 'orbit C01', 'BPM horizontal readout for storage ring')
3
>>> saveServiceConfig(conn, 'masar2', 'orbit C02', 'BPM horizontal readout for storage ring')
4
>>> result = retrieveServiceConfigs(conn, servicename='masar1', configname='orbit C01')
>>> saveServiceEvent(conn, servicename='masar1', configname='orbit C01', comment='a service event',approval=True)
1
>>> saveServiceEvent(conn, servicename='masar1', configname='orbit C01', comment='end service event',approval=True)
2
>>> updateServiceEvent(conn, 1, comment='an updated service event', approval=True, username='******')
True
>>> results = retrieveServiceEvents(conn)
>>> for res in results[1:]:
... print (res[0], res[0], res[2], res[4])
1 1 an updated service event test user
2 2 end service event None
>>> conn.close()
"""
checkConnection(conn)
sqlsel = '''
SELECT service_event_user_tag, service_event_approval, service_event_user_name
FROM service_event
WHERE service_event_id = ?
'''
sql = '''
UPDATE service_event
SET service_event_user_tag = ?, service_event_approval=?, service_event_user_name = ?
WHERE service_event_id = ?
'''
try:
cur = conn.cursor()
cur.execute(sqlsel, (eventid,))
comment0, approval0, username0 = cur.fetchone()
if comment==None:
comment = comment0
if not approval:
approval = approval0
if username==None:
username = username0
# each service event is a individual entity. Do not check the existence.
cur.execute(sql, (comment, approval, username, eventid))
except sqlite3.Error, e:
print ('Error %s' %e.args[0])
raise
def retrieveServiceConfigs(conn,
servicename=None,
configname=None,
configversion=None,
system=None,
eventid=None):
"""
Retrieve service config attributes like name, description, ... with given service name.
If service config name is none, retrieve all configs belong to a service with a given service name.
If service name is none, retrieve all configs in service_config table.
It returns tuple list with [(service_config_id, service_config_name, service_config_desc, service_config_create_date,
service_config_version, and service_config_status)].
>>> import sqlite3
>>> from pymasarsqlite.service.service import (saveService, retrieveServices)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> conn = sqlite3.connect(":memory:")
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> saveService(conn, 'masar1', desc='non-empty description')
1
>>> saveService(conn, 'masar2', desc='non-empty description')
2
>>> saveServiceConfig(conn, 'masar1', 'orbit C01', 'BPM horizontal readout for storage ring')
1
>>> saveServiceConfig(conn, 'masar1', 'orbit C02', 'BPM horizontal readout for storage ring')
2
>>> saveServiceConfig(conn, 'masar2', 'orbit C01', 'BPM horizontal readout for storage ring')
3
>>> saveServiceConfig(conn, 'masar2', 'orbit C02', 'BPM horizontal readout for storage ring')
4
>>> saveServiceConfig(conn, 'masar2', 'RF snapshot', 'Storage ring RF system snapshot', system='SR')
5
>>> result = retrieveServiceConfigs(conn)
>>> print (result[1][0], result[1][1], result[1][5])
1 orbit C01 masar1
>>> print (result[2][0], result[2][1], result[2][5])
2 orbit C02 masar1
>>> print (result[3][0], result[3][1], result[3][5])
3 orbit C01 masar2
>>> print (result[4][0], result[4][1], result[4][5])
4 orbit C02 masar2
>>> result = retrieveServiceConfigs(conn, servicename='masar1')
>>> print (result[1][0], result[1][1])
1 orbit C01
>>> print (result[2][0], result[2][1])
2 orbit C02
>>> result = retrieveServiceConfigs(conn, configname='orbit C01')
>>> print (result[1][0], result[1][1], result[1][5])
1 orbit C01 masar1
>>> print (result[2][0], result[2][1], result[2][5])
3 orbit C01 masar2
>>> result = retrieveServiceConfigs(conn, servicename='masar1', configname='orbit C01')
>>> print (result[1][0], result[1][1])
1 orbit C01
>>> result = retrieveServiceConfigs(conn, system='SR1')
>>> result = retrieveServiceConfigs(conn, system='SR')
>>> print (result[1][0], ',', result[1][1], ',', result[1][2], ',', result[1][5])
5 , RF snapshot , Storage ring RF system snapshot , masar2
>>> result = retrieveServiceConfigs(conn, configname='RF snapshot', system='SR')
>>> print (result[1][0], ',', result[1][1], ',', result[1][2], ',', result[1][5])
5 , RF snapshot , Storage ring RF system snapshot , masar2
>>> result = retrieveServiceConfigs(conn, servicename='masar2', system='SR')
>>> print (result[1][0], ',', result[1][1], ',', result[1][2], ',', result[1][5])
5 , RF snapshot , Storage ring RF system snapshot , masar2
>>> result = retrieveServiceConfigs(conn, servicename='masar2', configname='RF snapshot', system='SR')
>>> print (result[1][0], ',', result[1][1], ',', result[1][2], ',', result[1][5])
5 , RF snapshot , Storage ring RF system snapshot , masar2
>>> result = retrieveServiceConfigs(conn, system='SR1')
>>> print (result)
[(u'service_config_id', u'service_config_name', u'service_config_desc', u'service_config_create_date', u'service_config_version', u'service_name')]
>>> conn.close()
"""
checkConnection(conn)
sql = '''
select service_config.service_config_id, service_config_name, service_config_desc, service_config_create_date,
service_config_version, service_config_status
from service_config '''
results = None
if eventid is not None:
sql += ' left join service_event using (service_config_id) where service_event_id = ?'
cur = conn.cursor()
cur.execute(sql, (eventid, ))
results = cur.fetchall()
else:
if configname != None:
configname = configname.replace("*", "%").replace("?", "_")
try:
cur = conn.cursor()
join = False
if system is not None:
join = True
joinsql = ' left join service_config_prop using (service_config_id) '
if configname is None and servicename is None:
if join:
sql = sql + joinsql + ' where (service_config_prop_name = "system" and service_config_prop_value like ?) '
cur.execute(sql, (system, ))
else:
cur.execute(sql)
elif servicename is None:
if join:
sql = sql + joinsql + ' where service_config_name like ? and '
sql = sql + ' service_config_prop_name = "system" and service_config_prop_value like ? '
cur.execute(sql, (
configname,
system,
))
else:
sql = sql + ' where service_config_name like ?'
cur.execute(sql, (configname, ))
elif configname is None:
if join:
sql = sql + joinsql + ' left join service using (service_id) where service.service_name = ? and '
sql += ' service_config_prop_name = "system" and service_config_prop_value like ? '
cur.execute(sql, (servicename, system))
else:
sql = sql + ' , service where service_config.service_id = service.service_id and service.service_name = ?'
cur.execute(sql, (servicename, ))
else:
if join:
sql = sql + joinsql + ' left join service using (service_id) '
sql = sql + ' where service_config_name like ? and service.service_name = ? and '
sql += ' (service_config_prop_name = "system" and service_config_prop_value like ?) '
# print (sql)
cur.execute(sql, (
configname,
servicename,
system,
))
else:
sql = sql + ', service where service_config.service_id = service.service_id and service_config_name like ? and service.service_name = ?'
cur.execute(sql, (
configname,
servicename,
))
results = cur.fetchall()
except:
raise
results = [
('config_idx', 'config_name', 'config_desc', 'config_create_date',
'config_version', 'status'),
] + results[:]
return results
def retrieveSnapshot(conn,
eventid=None,
start=None,
end=None,
comment=None,
approval=True):
"""
retrieve masar service data with given time frame and comment.
If end time is not given, use current time. If start time is not given,
get all data during past one week before end time.
Both start time and end time should be in UTC time format.
It returns data as a tuple array like below:
service_event_user_tag, service_event_UTC_time, service_config_name, service_name
[[('user tag', 'event UTC time', 'service config name', 'service name'),
('pv name', 'string value', 'double value', 'long value', 'dbr type', 'isConnected',
'secondsPastEpoch', 'nanoSeconds', 'timeStampTag', 'alarmSeverity', 'alarmStatus', 'alarmMessage',
'is_array', 'array_value')]
[('pv name', 'string value', 'double value', 'long value', 'dbr type', 'isConnected',
'secondsPastEpoch', 'nanoSeconds', 'timeStampTag', 'alarmSeverity', 'alarmStatus', 'alarmMessage',
'is_array', 'array_value')
('pv name', 'string value', 'double value', 'long value', 'dbr type', 'isConnected',
'secondsPastEpoch', 'nanoSeconds', 'timeStampTag', 'alarmSeverity', 'alarmStatus', 'alarmMessage',
'is_array', 'array_value')
...]
...
]
>>> import sqlite3
>>> from pymasarsqlite.service.service import (saveService, retrieveServices)
>>> from pymasarsqlite.service.serviceconfig import (saveServiceConfig, retrieveServiceConfigs)
>>> from pymasarsqlite.service.serviceevent import (updateServiceEvent)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> conn = sqlite3.connect(":memory:")
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> saveService(conn, 'masar1', desc='non-empty description')
1
>>> saveService(conn, 'masar2', desc='non-empty description')
2
>>> saveServiceConfig(conn, 'masar1', 'orbit C01', 'BPM horizontal readout for storage ring')
1
>>> saveServiceConfig(conn, 'masar1', 'orbit C02', 'BPM horizontal readout for storage ring')
2
>>> saveServiceConfig(conn, 'masar2', 'orbit C01', 'BPM horizontal readout for storage ring')
3
>>> saveServiceConfig(conn, 'masar2', 'orbit C02', 'BPM horizontal readout for storage ring')
4
>>> result = retrieveServiceConfigs(conn, servicename='masar1', configname='orbit C01')
>>> data = [('SR:C01-BI:G02A<BPM:L1>Pos-X','12.2', 12.2, 12, 6, 1, 435686768234, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G02A<BPM:L2>Pos-X', '12.2', 12.2, 12, 6, 1, 435686768234, 3452345098734, 0, 0, 0, "", 1, [1.2,2.3, 3.4, 4.5]),
... ('SR:C01-BI:G04A<BPM:M1>Pos-X', '12.2', 12.2, 12, 6, 1, 435686768234, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G04B<BPM:M1>Pos-X', '12.2', 12.2, 12, 6, 1, 435686768234, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G06B<BPM:H1>Pos-X', '12.2', 12.2, 12, 6, 1, 435686768234, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G06B<BPM:H2>Pos-X', '12.2', 12.2, 12, 6, 1, 435686768234, 3452345098734, 0, 0, 0, "", 0, [])]
>>> import datetime as dt
>>> import time
>>> start = dt.datetime.utcnow()
>>> time.sleep(1.0)
>>> saveSnapshot(conn, data, servicename='masar1', configname='orbit C01', comment='a service event')
(1, [1, 2, 3, 4, 5, 6])
>>> updateServiceEvent(conn, 1, approval=True, username='******')
True
>>> data = [('SR:C01-BI:G02A<BPM:L1>Pos-X','12.2', 12.2, 12, 6, 1, 564562342566, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G02A<BPM:L2>Pos-X', '12.2', 12.2, 12, 6, 1, 564562342566, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G04A<BPM:M1>Pos-X', '12.2', 12.2, 12, 6, 1, 564562342566, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G04B<BPM:M1>Pos-X', '12.2', 12.2, 12, 6, 1, 564562342566, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G06B<BPM:H1>Pos-X', '12.2', 12.2, 12, 6, 1, 564562342566, 3452345098734, 0, 0, 0, "", 1, [1.2,2.3, 3.4, 4.5]),
... ('SR:C01-BI:G06B<BPM:H2>Pos-X', '12.2', 12.2, 12, 6, 1, 564562342566, 3452345098734, 0, 0, 0, "", 0, [])]
>>> time.sleep(1.0)
>>> end1 = dt.datetime.utcnow()
>>> time.sleep(1.0)
>>> saveSnapshot(conn, data, servicename='masar1', configname='orbit C01', comment='a service event')
(2, [7, 8, 9, 10, 11, 12])
>>> updateServiceEvent(conn, 2, approval=True, username='******')
True
>>> time.sleep(1.0)
>>> end2 = dt.datetime.utcnow()
>>> datas = retrieveSnapshot(conn, start=start, end=end1)
>>> print (datas[1][0][0], ',', datas[1][0][2], ',', datas[1][0][3])
a service event , orbit C01 , masar1
>>> for data in datas[1][1:]:
... print (data)
(u'SR:C01-BI:G02A<BPM:L1>Pos-X', u'12.2', 12.2, 12, 6, 1, 435686768234L, 3452345098734L, 0, 0, 0, u'', 0, [])
(u'SR:C01-BI:G02A<BPM:L2>Pos-X', u'12.2', 12.2, 12, 6, 1, 435686768234L, 3452345098734L, 0, 0, 0, u'', 1, [1.2, 2.3, 3.4, 4.5])
(u'SR:C01-BI:G04A<BPM:M1>Pos-X', u'12.2', 12.2, 12, 6, 1, 435686768234L, 3452345098734L, 0, 0, 0, u'', 0, [])
(u'SR:C01-BI:G04B<BPM:M1>Pos-X', u'12.2', 12.2, 12, 6, 1, 435686768234L, 3452345098734L, 0, 0, 0, u'', 0, [])
(u'SR:C01-BI:G06B<BPM:H1>Pos-X', u'12.2', 12.2, 12, 6, 1, 435686768234L, 3452345098734L, 0, 0, 0, u'', 0, [])
(u'SR:C01-BI:G06B<BPM:H2>Pos-X', u'12.2', 12.2, 12, 6, 1, 435686768234L, 3452345098734L, 0, 0, 0, u'', 0, [])
>>> datasets = retrieveSnapshot(conn, start=start, end=end2)
>>> for dataset in datasets[1:]:
... print (dataset[0][0], ',', dataset[0][2], ',', dataset[0][3])
... for data in dataset[1:]:
... print (data)
a service event , orbit C01 , masar1
(u'SR:C01-BI:G02A<BPM:L1>Pos-X', u'12.2', 12.2, 12, 6, 1, 435686768234L, 3452345098734L, 0, 0, 0, u'', 0, [])
(u'SR:C01-BI:G02A<BPM:L2>Pos-X', u'12.2', 12.2, 12, 6, 1, 435686768234L, 3452345098734L, 0, 0, 0, u'', 1, [1.2, 2.3, 3.4, 4.5])
(u'SR:C01-BI:G04A<BPM:M1>Pos-X', u'12.2', 12.2, 12, 6, 1, 435686768234L, 3452345098734L, 0, 0, 0, u'', 0, [])
(u'SR:C01-BI:G04B<BPM:M1>Pos-X', u'12.2', 12.2, 12, 6, 1, 435686768234L, 3452345098734L, 0, 0, 0, u'', 0, [])
(u'SR:C01-BI:G06B<BPM:H1>Pos-X', u'12.2', 12.2, 12, 6, 1, 435686768234L, 3452345098734L, 0, 0, 0, u'', 0, [])
(u'SR:C01-BI:G06B<BPM:H2>Pos-X', u'12.2', 12.2, 12, 6, 1, 435686768234L, 3452345098734L, 0, 0, 0, u'', 0, [])
a service event , orbit C01 , masar1
(u'SR:C01-BI:G02A<BPM:L1>Pos-X', u'12.2', 12.2, 12, 6, 1, 564562342566L, 3452345098734L, 0, 0, 0, u'', 0, [])
(u'SR:C01-BI:G02A<BPM:L2>Pos-X', u'12.2', 12.2, 12, 6, 1, 564562342566L, 3452345098734L, 0, 0, 0, u'', 0, [])
(u'SR:C01-BI:G04A<BPM:M1>Pos-X', u'12.2', 12.2, 12, 6, 1, 564562342566L, 3452345098734L, 0, 0, 0, u'', 0, [])
(u'SR:C01-BI:G04B<BPM:M1>Pos-X', u'12.2', 12.2, 12, 6, 1, 564562342566L, 3452345098734L, 0, 0, 0, u'', 0, [])
(u'SR:C01-BI:G06B<BPM:H1>Pos-X', u'12.2', 12.2, 12, 6, 1, 564562342566L, 3452345098734L, 0, 0, 0, u'', 1, [1.2, 2.3, 3.4, 4.5])
(u'SR:C01-BI:G06B<BPM:H2>Pos-X', u'12.2', 12.2, 12, 6, 1, 564562342566L, 3452345098734L, 0, 0, 0, u'', 0, [])
>>> datasets = retrieveSnapshot(conn, start=end2)
>>> print (datasets[0][0])
(u'user tag', u'event UTC time', u'service config name', u'service name')
>>> print (datasets[0][1])
(u'pv name', u'string value', u'double value', u'long value', u'dbr type', u'isConnected', u'secondsPastEpoch', u'nanoSeconds', u'timeStampTag', u'alarmSeverity', u'alarmStatus', u'alarmMessage', u'is_array', u'array_value')
>>> conn.close()
"""
checkConnection(conn)
dataset = []
datahead = [[('user tag', 'event UTC time', 'service config name',
'service name'),
('pv name', 'string value', 'double value', 'long value',
'dbr type', 'isConnected', 'secondsPastEpoch', 'nanoSeconds',
'timeStampTag', 'alarmSeverity', 'alarmStatus',
'alarmMessage', 'is_array', 'array_value')]]
sql = '''
select service_event_user_tag, service_event_UTC_time, service_config_name, service_name
from service_event
left join service_config using (service_config_id)
left join service using (service_id)
where service_event_id = ?
'''
try:
if eventid:
data = __retrieveMasarData(conn, eventid)
# data = datahead + data[:]
cur = conn.cursor()
cur.execute(sql, (eventid, ))
result = cur.fetchall()
data = result[:] + data[:]
dataset.append(data)
else:
results = retrieveServiceEvents(conn,
start=start,
end=end,
comment=comment,
approval=approval)
# print ("event retults = ", results)
sql += ' and service_config_id = ? and service_event_approval = 1 '
for result in results[1:]:
data = __retrieveMasarData(conn, result[0])
# data = datahead + data[:]
cur = conn.cursor()
cur.execute(sql, (
result[0],
result[1],
))
result = cur.fetchall()
data = result[:] + data[:]
dataset.append(data)
dataset = datahead[:] + dataset[:]
except:
raise
return dataset
def savePvGroup(conn, name, **kws):
"""
Save pv groups with given name and description into pv_group table.
With current version, the pv group name has to be global unique.
If one pv group already exists and update is true, update its description.
The key word is optional, and looks like:
[func='function description for this pv group'] [, update=False] [, version = '0.0.1']
>>> import sqlite3
>>> from pymasarsqlite.pvgroup.pvgroup import (savePvGroup, retrievePvGroups)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> conn = sqlite3.connect(':memory:')
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> name = 'masar'
>>> desc = 'this is my first pv group for masar service'
>>> savePvGroup(conn, name, func=desc)
[1]
>>> desc = 'this is my new pv group for masar service with same group name'
>>> savePvGroup(conn, name, func=desc)
[1]
>>> name = 'masar2'
>>> desc = 'this is my new pv group for masar service with same group name'
>>> savePvGroup(conn, name, func=desc)
[2]
>>> conn.close()
"""
checkConnection(conn)
func = None
version = None
try:
func = kws['func']
except:
pass
try:
version = kws['version']
except:
pass
update = False
try:
update = kws['update']
except:
pass
cur = conn.cursor()
basic_sql = 'select pv_group_id from pv_group where pv_group_name = ?'
cur.execute(basic_sql, (name, ))
# basic_sql = 'select pv_group_id, pv_group_name, pv_group_func, pvg_creation_date, version from pv_group where pv_group_name = ?'
# if func is None and version is None:
# cur.execute(basic_sql, (name,))
# elif version is None:
# cur.execute(basic_sql + ' and pv_group_func = ?', (name, func))
# elif version is None:
# cur.execute(basic_sql + ' and version = ?', (name, version))
# else:
# cur.execute(basic_sql + ' and pv_group_func = ? and version = ?', (name, func, version))
pvgroups = cur.fetchall()
pvgroup_id = []
if len(pvgroups) == 0:
cur.execute(
'insert into pv_group(pv_group_id, pv_group_name, pv_group_func, pvg_creation_date, version) values (?,?,?,datetime("now"),?)',
(None, name, func, version))
pvgroup_id.append(cur.lastrowid)
else:
for pvgroup in pvgroups:
pvgroup_id.append(pvgroup[0])
if update:
cur.execute(
'UPDATE pv_group SET pv_group_func = ? WHERE pv_group_id = ?',
(pvgroup[0], ))
# pvgroup_id = pvgroups['id']
return pvgroup_id
def savePvs(conn, pvlist, pvdesc=None, update=False):
"""
This method saves all pvs in pvlist, and returns the pv_id (the primary key) list.
If one pv already exists, it retrieves its primary key, and update its description if update is true.
>>> import sqlite3
>>> from pymasarsqlite.pvgroup.pv import (savePvs, retrieveGroupPvs)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> conn = sqlite3.connect(":memory:")
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> pvs = ['SR:C01-BI:G02A<BPM:L1>Pos-X', 'SR:C01-BI:G02A<BPM:L2>Pos-X',
... 'SR:C01-BI:G04A<BPM:M1>Pos-X', 'SR:C01-BI:G04B<BPM:M1>Pos-X',
... 'SR:C01-BI:G06B<BPM:H1>Pos-X', 'SR:C01-BI:G06B<BPM:H2>Pos-X',
... 'SR:C02-BI:G02A<BPM:H1>Pos-X', 'SR:C02-BI:G02A<BPM:H2>Pos-X',
... 'SR:C02-BI:G04A<BPM:M1>Pos-X', 'SR:C02-BI:G04B<BPM:M1>Pos-X',
... 'SR:C02-BI:G06B<BPM:L1>Pos-X', 'SR:C02-BI:G06B<BPM:L2>Pos-X',
... 'SR:C03-BI:G02A<BPM:L1>Pos-X', 'SR:C03-BI:G02A<BPM:L2>Pos-X',
... 'SR:C03-BI:G04A<BPM:M1>Pos-X', 'SR:C03-BI:G04B<BPM:M1>Pos-X']
>>> savePvs(conn, pvs)
[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16]
>>> desc = ['Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G04 M1', 'Horizontal orbit from BPM at C01 G04 M1',
... 'Horizontal orbit from BPM at C01 G06 H1', 'Horizontal orbit from BPM at C01 G06 H2',
... 'Horizontal orbit from BPM at C02 G02 H1', 'Horizontal orbit from BPM at C02 G02 H2',
... 'Horizontal orbit from BPM at C02 G04 M1', 'Horizontal orbit from BPM at C02 G04 M1',
... 'Horizontal orbit from BPM at C02 G06 L1', 'Horizontal orbit from BPM at C01 G06 L2',
... 'Horizontal orbit from BPM at C03 G02 L1', 'Horizontal orbit from BPM at C03 G02 L2',
... 'Horizontal orbit from BPM at C03 G04 M1', 'Horizontal orbit from BPM at C03 G04 M1']
>>> savePvs(conn, pvs, desc)
[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16]
>>> tmp = cur.execute('select pv_id, pv_name, description from pv')
>>> dataset = cur.fetchall()
>>> for data in dataset:
... print (data)
(1, u'SR:C01-BI:G02A<BPM:L1>Pos-X', None)
(2, u'SR:C01-BI:G02A<BPM:L2>Pos-X', None)
(3, u'SR:C01-BI:G04A<BPM:M1>Pos-X', None)
(4, u'SR:C01-BI:G04B<BPM:M1>Pos-X', None)
(5, u'SR:C01-BI:G06B<BPM:H1>Pos-X', None)
(6, u'SR:C01-BI:G06B<BPM:H2>Pos-X', None)
(7, u'SR:C02-BI:G02A<BPM:H1>Pos-X', None)
(8, u'SR:C02-BI:G02A<BPM:H2>Pos-X', None)
(9, u'SR:C02-BI:G04A<BPM:M1>Pos-X', None)
(10, u'SR:C02-BI:G04B<BPM:M1>Pos-X', None)
(11, u'SR:C02-BI:G06B<BPM:L1>Pos-X', None)
(12, u'SR:C02-BI:G06B<BPM:L2>Pos-X', None)
(13, u'SR:C03-BI:G02A<BPM:L1>Pos-X', None)
(14, u'SR:C03-BI:G02A<BPM:L2>Pos-X', None)
(15, u'SR:C03-BI:G04A<BPM:M1>Pos-X', None)
(16, u'SR:C03-BI:G04B<BPM:M1>Pos-X', None)
>>> savePvs(conn, pvs, desc, update=True)
[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16]
>>> tmp = cur.execute('select pv_id, pv_name, description from pv')
>>> dataset = cur.fetchall()
>>> for data in dataset:
... print (data)
(1, u'SR:C01-BI:G02A<BPM:L1>Pos-X', u'Horizontal orbit from BPM at C01 G02 L1')
(2, u'SR:C01-BI:G02A<BPM:L2>Pos-X', u'Horizontal orbit from BPM at C01 G02 L2')
(3, u'SR:C01-BI:G04A<BPM:M1>Pos-X', u'Horizontal orbit from BPM at C01 G04 M1')
(4, u'SR:C01-BI:G04B<BPM:M1>Pos-X', u'Horizontal orbit from BPM at C01 G04 M1')
(5, u'SR:C01-BI:G06B<BPM:H1>Pos-X', u'Horizontal orbit from BPM at C01 G06 H1')
(6, u'SR:C01-BI:G06B<BPM:H2>Pos-X', u'Horizontal orbit from BPM at C01 G06 H2')
(7, u'SR:C02-BI:G02A<BPM:H1>Pos-X', u'Horizontal orbit from BPM at C02 G02 H1')
(8, u'SR:C02-BI:G02A<BPM:H2>Pos-X', u'Horizontal orbit from BPM at C02 G02 H2')
(9, u'SR:C02-BI:G04A<BPM:M1>Pos-X', u'Horizontal orbit from BPM at C02 G04 M1')
(10, u'SR:C02-BI:G04B<BPM:M1>Pos-X', u'Horizontal orbit from BPM at C02 G04 M1')
(11, u'SR:C02-BI:G06B<BPM:L1>Pos-X', u'Horizontal orbit from BPM at C02 G06 L1')
(12, u'SR:C02-BI:G06B<BPM:L2>Pos-X', u'Horizontal orbit from BPM at C01 G06 L2')
(13, u'SR:C03-BI:G02A<BPM:L1>Pos-X', u'Horizontal orbit from BPM at C03 G02 L1')
(14, u'SR:C03-BI:G02A<BPM:L2>Pos-X', u'Horizontal orbit from BPM at C03 G02 L2')
(15, u'SR:C03-BI:G04A<BPM:M1>Pos-X', u'Horizontal orbit from BPM at C03 G04 M1')
(16, u'SR:C03-BI:G04B<BPM:M1>Pos-X', u'Horizontal orbit from BPM at C03 G04 M1')
>>> conn.close()
"""
checkConnection(conn)
if len(pvlist) == 0 or (pvdesc != None and len(pvlist) != len(pvdesc)):
raise Exception(
'empty pv list or length of pv list and description does not match.'
)
sys.exit()
cur = conn.cursor()
pv_ids = []
for i in range(len(pvlist)):
pv_name = pvlist[i].strip()
cur.execute('select pv_id from pv where pv_name = ?', (pv_name, ))
pv_id = cur.fetchone()
desc = None
if pvdesc != None:
desc = pvdesc[i]
if pv_id is None:
cur.execute(
'insert into pv(pv_id, pv_name, description) values (?,?,?)',
(None, pv_name, desc))
pv_id = cur.lastrowid
# cur.execute('select pv_id from pv where pv_name = ?', (pvlist[i],))
# pv_id = cur.fetchone()
else:
pv_id = pv_id[0]
if update:
"update pv description"
cur.execute(
'UPDATE pv SET description=? WHERE pv_name = ? and pv_id=?',
(desc, pv_name, pv_id))
pv_ids.append(pv_id)
return pv_ids
def retrieveGroupPvs(conn, pvgroupid):
"""
This methods retrieves all pvs associated with a pv_group.
Return return a tuple array with like [(pv_name1, description1),(pv_name2, description2)].
for example:
>>> import sqlite3
>>> from __future__ import print_function
>>> from pymasarsqlite.pvgroup.pv import (savePvs, retrieveGroupPvs)
>>> from pymasarsqlite.pvgroup.pvgroup import(savePvGroup, retrievePvGroups)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> conn = sqlite3.connect(":memory:")
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> pvs = ['SR:C01-BI:G02A<BPM:L1>Pos-X', 'SR:C01-BI:G02A<BPM:L2>Pos-X',
... 'SR:C01-BI:G04A<BPM:M1>Pos-X', 'SR:C01-BI:G04B<BPM:M1>Pos-X',
... 'SR:C01-BI:G06B<BPM:H1>Pos-X', 'SR:C01-BI:G06B<BPM:H2>Pos-X',
... 'SR:C02-BI:G02A<BPM:H1>Pos-X', 'SR:C02-BI:G02A<BPM:H2>Pos-X',
... 'SR:C02-BI:G04A<BPM:M1>Pos-X', 'SR:C02-BI:G04B<BPM:M1>Pos-X',
... 'SR:C02-BI:G06B<BPM:L1>Pos-X', 'SR:C02-BI:G06B<BPM:L2>Pos-X',
... 'SR:C03-BI:G02A<BPM:L1>Pos-X', 'SR:C03-BI:G02A<BPM:L2>Pos-X',
... 'SR:C03-BI:G04A<BPM:M1>Pos-X', 'SR:C03-BI:G04B<BPM:M1>Pos-X']
>>> pvdesc = ['Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2']
>>> name = 'masar'
>>> desc = 'this is my first pv group for masar service'
>>> savePvGroup(conn, name, func=desc)
[1]
>>> pvgroups = retrievePvGroups(conn, name)
>>> for pvgroup in pvgroups:
... print (pvgroup[0], pvgroup[1])
1 masar
>>> saveGroupPvs(conn, name, pvs, pvdesc=pvdesc)
[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16]
>>> results = retrieveGroupPvs(conn, 1)
>>> for result in results:
... print (result)
(u'SR:C01-BI:G02A<BPM:L1>Pos-X', u'Horizontal orbit from BPM at C01 G02 L1')
(u'SR:C01-BI:G02A<BPM:L2>Pos-X', u'Horizontal orbit from BPM at C01 G02 L2')
(u'SR:C01-BI:G04A<BPM:M1>Pos-X', u'Horizontal orbit from BPM at C01 G02 L1')
(u'SR:C01-BI:G04B<BPM:M1>Pos-X', u'Horizontal orbit from BPM at C01 G02 L2')
(u'SR:C01-BI:G06B<BPM:H1>Pos-X', u'Horizontal orbit from BPM at C01 G02 L1')
(u'SR:C01-BI:G06B<BPM:H2>Pos-X', u'Horizontal orbit from BPM at C01 G02 L2')
(u'SR:C02-BI:G02A<BPM:H1>Pos-X', u'Horizontal orbit from BPM at C01 G02 L1')
(u'SR:C02-BI:G02A<BPM:H2>Pos-X', u'Horizontal orbit from BPM at C01 G02 L2')
(u'SR:C02-BI:G04A<BPM:M1>Pos-X', u'Horizontal orbit from BPM at C01 G02 L1')
(u'SR:C02-BI:G04B<BPM:M1>Pos-X', u'Horizontal orbit from BPM at C01 G02 L2')
(u'SR:C02-BI:G06B<BPM:L1>Pos-X', u'Horizontal orbit from BPM at C01 G02 L1')
(u'SR:C02-BI:G06B<BPM:L2>Pos-X', u'Horizontal orbit from BPM at C01 G02 L2')
(u'SR:C03-BI:G02A<BPM:L1>Pos-X', u'Horizontal orbit from BPM at C01 G02 L1')
(u'SR:C03-BI:G02A<BPM:L2>Pos-X', u'Horizontal orbit from BPM at C01 G02 L2')
(u'SR:C03-BI:G04A<BPM:M1>Pos-X', u'Horizontal orbit from BPM at C01 G02 L1')
(u'SR:C03-BI:G04B<BPM:M1>Pos-X', u'Horizontal orbit from BPM at C01 G02 L2')
>>> results = retrieveGroupPvs(conn, 2)
>>> print (results)
[]
>>> conn.close()
"""
checkConnection(conn)
cur = conn.cursor()
# if there is any query performance issue, the SQL command should use left join,
# fine tuning join order to get best performance.
# Here is an example using join
SQL = ''' select pv.pv_name, pv.description from pv
left join pv__pvgroup using (pv_id)
left join pv_group using (pv_group_id)
where pv_group.pv_group_id = ?
'''
# SQL = '''select pv.pv_name, pv.description from pv, pv__pvgroup, pv_group where pv.pv_id = pv__pvgroup.pv_id
# and pv__pvgroup.pv_group_id = pv_group.pv_group_id
# and pv_group.pv_group_id = ?
# '''
cur.execute(SQL, (pvgroupid,))
dataset = cur.fetchall()
return dataset
def retrieveServiceConfigPVs(conn, configname, servicename=None):
"""
Retrieve pv list associated with given service config name.
>>> import sqlite3
>>> conn = sqlite3.connect(':memory:')
>>> from pymasarsqlite.service.service import (saveService, retrieveServices)
>>> from pymasarsqlite.service.serviceconfig import(saveServicePvGroup, retrieveServicePvGroups)
>>> from pymasarsqlite.pvgroup.pvgroup import (savePvGroup, retrievePvGroups)
>>> from pymasarsqlite.pvgroup.pv import (saveGroupPvs, retrieveGroupPvs)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> c01pvs = ['SR:C01-BI:G02A<BPM:L1>Pos-X', 'SR:C01-BI:G02A<BPM:L2>Pos-X',
... 'SR:C01-BI:G04A<BPM:M1>Pos-X', 'SR:C01-BI:G04B<BPM:M1>Pos-X',
... 'SR:C01-BI:G06B<BPM:H1>Pos-X', 'SR:C01-BI:G06B<BPM:H2>Pos-X']
>>> c02pvs = ['SR:C02-BI:G02A<BPM:H1>Pos-X', 'SR:C02-BI:G02A<BPM:H2>Pos-X',
... 'SR:C02-BI:G04A<BPM:M1>Pos-X', 'SR:C02-BI:G04B<BPM:M1>Pos-X',
... 'SR:C02-BI:G06B<BPM:L1>Pos-X', 'SR:C02-BI:G06B<BPM:L2>Pos-X']
>>> c03pvs = ['SR:C03-BI:G02A<BPM:L1>Pos-X', 'SR:C03-BI:G02A<BPM:L2>Pos-X',
... 'SR:C03-BI:G04A<BPM:M1>Pos-X', 'SR:C03-BI:G04B<BPM:M1>Pos-X',
... 'SR:C03-BI:G06B<BPM:H1>Pos-X', 'SR:C03-BI:G06B<BPM:H2>Pos-X']
>>> c01desc = ['Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G04 M1', 'Horizontal orbit from BPM at C01 G04 M1',
... 'Horizontal orbit from BPM at C01 G06 H1', 'Horizontal orbit from BPM at C01 G06 H2']
>>> c02desc = ['Horizontal orbit from BPM at C02 G02 H1', 'Horizontal orbit from BPM at C02 G02 H2',
... 'Horizontal orbit from BPM at C02 G04 M1', 'Horizontal orbit from BPM at C02 G04 M1',
... 'Horizontal orbit from BPM at C02 G06 L1', 'Horizontal orbit from BPM at C01 G06 L2']
>>> c03desc = ['Horizontal orbit from BPM at C03 G02 L1', 'Horizontal orbit from BPM at C03 G02 L2',
... 'Horizontal orbit from BPM at C03 G04 M1', 'Horizontal orbit from BPM at C03 G04 M1',
... 'Horizontal orbit from BPM at C03 G06 H1', 'Horizontal orbit from BPM at C03 G06 H2']
>>> c123pvs = ['SR:C02-BI:G02A<BPM:H1>Pos-X', 'SR:C02-BI:G02A<BPM:H2>Pos-X',
... 'SR:C03-BI:G02A<BPM:L1>Pos-X', 'SR:C03-BI:G02A<BPM:L2>Pos-X',
... 'SR:C01-BI:G06B<BPM:H1>Pos-X', 'SR:C01-BI:G06B<BPM:H2>Pos-X']
>>> pvgname1 = 'pvg1'
>>> pvgdesc1 = 'this is my 1st pv group for masar service'
>>> savePvGroup(conn, pvgname1, func=pvgdesc1)
[1]
>>> pvgname2 = 'pvg2'
>>> pvgdesc2 = 'this is my 2nd pv group for masar service'
>>> savePvGroup(conn, pvgname2, func=pvgdesc2)
[2]
>>> pvgname3 = 'pvg3'
>>> pvgdesc3 = 'this is my 3rd pv group for masar service'
>>> savePvGroup(conn, pvgname3, func=pvgdesc3)
[3]
>>> pvgname4 = 'pvg4'
>>> pvgdesc4 = 'this is mixed pv group for masar service'
>>> savePvGroup(conn, pvgname4, func=pvgdesc4)
[4]
>>> saveGroupPvs(conn, pvgname1, c01pvs)
[1, 2, 3, 4, 5, 6]
>>> saveGroupPvs(conn, pvgname2, c02pvs)
[7, 8, 9, 10, 11, 12]
>>> saveGroupPvs(conn, pvgname3, c03pvs)
[13, 14, 15, 16, 17, 18]
>>> saveGroupPvs(conn, pvgname4, c123pvs)
[19, 20, 21, 22, 23, 24]
>>> sername1 = 'masar'
>>> serdesc1 = 'masar service description'
>>> sername2 = 'snapshot'
>>> serdesc2 = 'snapshot service description'
>>> sername3 = 'restore'
>>> serdesc3 = 'restore service description'
>>> saveService(conn, sername1, desc=serdesc1)
1
>>> saveService(conn, sername2, desc=serdesc2)
2
>>> saveService(conn, sername3, desc=serdesc3)
3
>>> confname1 = 'masar1'
>>> confdesc1 = 'masar1 desc'
>>> confname2 = 'masar2'
>>> confdesc2 = 'masar2 desc'
>>> confname3 = 'masar3'
>>> confdesc3 = 'masar3 desc'
>>> saveServiceConfig(conn, sername1, confname1, configdesc=confdesc1)
1
>>> saveServiceConfig(conn, sername1, confname2, configdesc=confdesc2)
2
>>> saveServiceConfig(conn, sername2, confname2, configdesc=confdesc2)
3
>>> saveServiceConfig(conn, sername2, confname3, configdesc=confdesc3)
4
>>> saveServiceConfig(conn, sername3, confname3, configdesc=confdesc3)
5
>>> saveServicePvGroup(conn, confname1, [pvgname1, pvgname2])
[1, 2]
>>> saveServicePvGroup(conn, confname2, [pvgname1, pvgname2, pvgname3])
[3, 4, 5]
>>> saveServicePvGroup(conn, confname3, [pvgname3, pvgname4])
[6, 7]
>>> retrieveServiceConfigPVs(conn, confname1, servicename=sername1)
[u'SR:C01-BI:G02A<BPM:L1>Pos-X', u'SR:C01-BI:G02A<BPM:L2>Pos-X', u'SR:C01-BI:G04A<BPM:M1>Pos-X', u'SR:C01-BI:G04B<BPM:M1>Pos-X', ...
u'SR:C01-BI:G06B<BPM:H1>Pos-X', u'SR:C01-BI:G06B<BPM:H2>Pos-X', u'SR:C02-BI:G02A<BPM:H1>Pos-X', u'SR:C02-BI:G02A<BPM:H2>Pos-X', ...
u'SR:C02-BI:G04A<BPM:M1>Pos-X', u'SR:C02-BI:G04B<BPM:M1>Pos-X', u'SR:C02-BI:G06B<BPM:L1>Pos-X', u'SR:C02-BI:G06B<BPM:L2>Pos-X']
>>> retrieveServiceConfigPVs(conn, confname3, servicename=sername1)
[]
>>> retrieveServiceConfigPVs(conn, confname1)
[u'SR:C01-BI:G02A<BPM:L1>Pos-X', u'SR:C01-BI:G02A<BPM:L2>Pos-X', u'SR:C01-BI:G04A<BPM:M1>Pos-X', u'SR:C01-BI:G04B<BPM:M1>Pos-X', ...
u'SR:C01-BI:G06B<BPM:H1>Pos-X', u'SR:C01-BI:G06B<BPM:H2>Pos-X', u'SR:C02-BI:G02A<BPM:H1>Pos-X', u'SR:C02-BI:G02A<BPM:H2>Pos-X', ...
u'SR:C02-BI:G04A<BPM:M1>Pos-X', u'SR:C02-BI:G04B<BPM:M1>Pos-X', u'SR:C02-BI:G06B<BPM:L1>Pos-X', u'SR:C02-BI:G06B<BPM:L2>Pos-X']
>>> retrieveServiceConfigPVs(conn, confname2)
[u'SR:C01-BI:G02A<BPM:L1>Pos-X', u'SR:C01-BI:G02A<BPM:L2>Pos-X', u'SR:C01-BI:G04A<BPM:M1>Pos-X', u'SR:C01-BI:G04B<BPM:M1>Pos-X', ...
u'SR:C01-BI:G06B<BPM:H1>Pos-X', u'SR:C01-BI:G06B<BPM:H2>Pos-X', u'SR:C02-BI:G02A<BPM:H1>Pos-X', u'SR:C02-BI:G02A<BPM:H2>Pos-X', ...
u'SR:C02-BI:G04A<BPM:M1>Pos-X', u'SR:C02-BI:G04B<BPM:M1>Pos-X', u'SR:C02-BI:G06B<BPM:L1>Pos-X', u'SR:C02-BI:G06B<BPM:L2>Pos-X', ...
u'SR:C03-BI:G02A<BPM:L1>Pos-X', u'SR:C03-BI:G02A<BPM:L2>Pos-X', u'SR:C03-BI:G04A<BPM:M1>Pos-X', u'SR:C03-BI:G04B<BPM:M1>Pos-X', ...
u'SR:C03-BI:G06B<BPM:H1>Pos-X', u'SR:C03-BI:G06B<BPM:H2>Pos-X']
>>> retrieveServiceConfigPVs(conn, confname3)
[u'SR:C01-BI:G06B<BPM:H1>Pos-X', u'SR:C01-BI:G06B<BPM:H2>Pos-X', u'SR:C02-BI:G02A<BPM:H1>Pos-X', ...
u'SR:C02-BI:G02A<BPM:H2>Pos-X', u'SR:C03-BI:G02A<BPM:L1>Pos-X', u'SR:C03-BI:G02A<BPM:L2>Pos-X', ...
u'SR:C03-BI:G04A<BPM:M1>Pos-X', u'SR:C03-BI:G04B<BPM:M1>Pos-X', u'SR:C03-BI:G06B<BPM:H1>Pos-X', ...
u'SR:C03-BI:G06B<BPM:H2>Pos-X']
"""
if configname is None:
raise Exception('service config name is not specified')
sys.exit()
checkConnection(conn)
sql = '''
select pv_name, pv_id from pv
left join pv__pvgroup using (pv_id)
left join pv_group using (pv_group_id)
left join pvgroup__serviceconfig using (pv_group_id)
left join service_config using (service_config_id)
where service_config.service_config_name = ? '''
pvlist = {}
try:
cur = conn.cursor()
if servicename is None:
cur.execute(sql, (configname, ))
else:
services = retrieveServices(conn, servicename)
if len(services) == 0:
raise Exception('Given service (%s) does not exist.' %
servicename)
else:
sql = sql + ' and service_config.service_id = ?'
cur.execute(sql, (configname, services[0][0]))
results = cur.fetchall()
for result in results:
pvlist[result[1]] = result[0]
except sqlite3.Error, e:
print("Error %s" % e.arg[0])
raise
def retrieveServiceEvents(conn,
configid=None,
eventid=None,
start=None,
end=None,
comment=None,
user=None,
approval=True):
"""
retrieve an service event with given user tag within given time frame.
If event id is given, get service event header information for that given event.
Both start and end time should be in UTC time format.
If end time is not specified, use current time. If start is not specified, use one week before end time.
It return a tuple array with format like:
[(service_event_id, service_config_id, service_event_user_tag, service_event_UTC_time, service_event_user_name)]
>>> import sqlite3
>>> from pymasarsqlite.service.service import (saveService, retrieveServices)
>>> from pymasarsqlite.service.serviceconfig import (saveServiceConfig)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> conn = sqlite3.connect(":memory:")
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> saveService(conn, 'masar1', desc='non-empty description')
1
>>> saveService(conn, 'masar2', desc='non-empty description')
2
>>> saveServiceConfig(conn, 'masar1', 'orbit C01', 'BPM horizontal readout for storage ring')
1
>>> saveServiceConfig(conn, 'masar1', 'orbit C02', 'BPM horizontal readout for storage ring')
2
>>> saveServiceConfig(conn, 'masar2', 'orbit C01', 'BPM horizontal readout for storage ring')
3
>>> saveServiceConfig(conn, 'masar2', 'orbit C02', 'BPM horizontal readout for storage ring')
4
>>> result = retrieveServiceConfigs(conn, servicename='masar1', configname='orbit C01')
>>> import datetime as dt
>>> saveServiceEvent(conn, servicename='masar1', configname='orbit C01', comment='a service event1',approval=True)
1
>>> start = dt.datetime.utcnow()
>>> import time
>>> time.sleep(1)
>>> saveServiceEvent(conn, servicename='masar1', configname='orbit C01', comment='a service event2',approval=True)
2
>>> end = dt.datetime.utcnow()
>>> time.sleep(1)
>>> saveServiceEvent(conn, servicename='masar1', configname='orbit C01', comment='a service event3',approval=True)
3
>>> results = retrieveServiceEvents(conn, comment='a service event1')
>>> for result in results[1:]:
... print (result[0], result[1], result[2])
1 1 a service event1
>>> results = retrieveServiceEvents(conn)
>>> for result in results[1:]:
... print (result[0], result[1], result[2])
1 1 a service event1
2 1 a service event2
3 1 a service event3
>>> results = retrieveServiceEvents(conn, start=start, end=end)
>>> for result in results[1:]:
... print (result[0], result[1], result[2])
2 1 a service event2
>>> conn.close()
"""
checkConnection(conn)
results = None
try:
cur = conn.cursor()
sql = '''
select service_event_id, service_config_id, service_event_user_tag, service_event_UTC_time,
service_event_user_name
from service_event where service_event_approval = 1
'''
if eventid is not None:
sql += ' and service_event_id = ?'
cur.execute(sql, (eventid, ))
else:
if comment != None:
comment = comment.replace("*", "%").replace("?", "_")
sql += ' and service_event_user_tag like "%s" ' % (comment)
if user != None:
user = user.replace("*", "%").replace("?", "_")
sql += ' and service_event_user_name like "%s" ' % (user)
if (start is None) and (end is None):
if configid is None:
cur.execute(sql, )
else:
sql += ' and service_config_id = ?'
cur.execute(sql, (configid, ))
else:
sql += ' and service_event_UTC_time > ? and service_event_UTC_time < ? '
if end is None:
end = dt.datetime.utcnow()
if start is None:
start = end - dt.timedelta(weeks=1)
if start > end:
raise Exception('Time range error')
if configid is None:
cur.execute(sql, (
start,
end,
))
else:
sql += ' and service_config_id = ? '
cur.execute(sql, (
start,
end,
configid,
))
results = cur.fetchall()
except:
raise
results = [
("event_id", "config_id", "comments", "event_time", "user_name"),
] + results[:]
return results
def saveGroupPvs(conn, pvgroupname, pvlist, pvdesc = None, update = False):
"""
This method saves all pvs in pvlist, and assigns those pvs to given pv group name. If update is true,
update the pvs in the pv group. By default, it uses existing pvs belonging to that group. Currently,
the update flag is a place holder to future usage. It is not allowed to append any pv to existing pv
group.
In general, once a pv group exists, it is not encourage to modify it. Create a new pv group
instead of appending any pv into existing one.
>>> import sqlite3
>>> from __future__ import print_function
>>> from pymasarsqlite.pvgroup.pv import (savePvs, retrieveGroupPvs)
>>> from pymasarsqlite.pvgroup.pvgroup import(savePvGroup, retrievePvGroups)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> conn = sqlite3.connect(":memory:")
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> pvs = ['SR:C01-BI:G02A<BPM:L1>Pos-X', 'SR:C01-BI:G02A<BPM:L2>Pos-X',
... 'SR:C01-BI:G04A<BPM:M1>Pos-X', 'SR:C01-BI:G04B<BPM:M1>Pos-X',
... 'SR:C01-BI:G06B<BPM:H1>Pos-X', 'SR:C01-BI:G06B<BPM:H2>Pos-X',
... 'SR:C02-BI:G02A<BPM:H1>Pos-X', 'SR:C02-BI:G02A<BPM:H2>Pos-X',
... 'SR:C02-BI:G04A<BPM:M1>Pos-X', 'SR:C02-BI:G04B<BPM:M1>Pos-X',
... 'SR:C02-BI:G06B<BPM:L1>Pos-X', 'SR:C02-BI:G06B<BPM:L2>Pos-X',
... 'SR:C03-BI:G02A<BPM:L1>Pos-X', 'SR:C03-BI:G02A<BPM:L2>Pos-X',
... 'SR:C03-BI:G04A<BPM:M1>Pos-X', 'SR:C03-BI:G04B<BPM:M1>Pos-X']
>>> pvdesc = ['Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2']
>>> name = 'masar'
>>> desc = 'this is my first pv group for masar service'
>>> savePvGroup(conn, name, func=desc)
[1]
>>> pvgroups = retrievePvGroups(conn, name)
>>> for pvgroup in pvgroups:
... print (pvgroup[0], pvgroup[1])
1 masar
>>> saveGroupPvs(conn, name, pvs, pvdesc=pvdesc)
[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16]
>>> saveGroupPvs(conn, name, pvs, pvdesc=pvdesc)
[]
>>> conn.close()
"""
checkConnection(conn)
cur=conn.cursor()
cur.execute('select pv_group_id from pv_group where pv_group_name = ?', (pvgroupname,))
pvgroupid = cur.fetchall()
if len(pvgroupid) != 1:
raise Exception('''pv group name ({0}) is not unique, or not exist.'''.format(pvgroupname))
sys.exit()
else:
pvgroupid = pvgroupid[0][0]
cur.execute('select pv__pvgroup_id from pv__pvgroup where pv_group_id = ?', (pvgroupid,))
pv_pvg_id = cur.fetchone()
pv_pvg_ids = []
if pv_pvg_id is None:
"the pv group with pvgroupid does not exist."
pv_ids = savePvs(conn, pvlist, pvdesc=pvdesc)
for pv_id in pv_ids:
cur.execute('select pv__pvgroup_id from pv__pvgroup where pv_id = ? and pv_group_id = ?', (pv_id, pvgroupid))
pv_pvg_id = cur.fetchone()
if pv_pvg_id is None:
cur.execute('insert into pv__pvgroup (pv__pvgroup_id, pv_id, pv_group_id) values (?,?,?)', (None, pv_id, pvgroupid))
pv_pvg_id = cur.lastrowid
else:
pv_pvg_id = pv_pvg_id[0]
pv_pvg_ids.append(pv_pvg_id)
elif update:
"the pv group with pvgroupid exists, and need to be updated."
else:
"the pv group with pvgroupid exists, and not need to be updated."
return pv_pvg_ids
def updateServiceEvent(conn,
eventid,
comment=None,
approval=False,
username=None):
"""
update the comment, approval status, and add user name for an existing event.
>>> import sqlite3
>>> from pymasarsqlite.service.service import (saveService, retrieveServices)
>>> from pymasarsqlite.service.serviceconfig import (saveServiceConfig)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> conn = sqlite3.connect(":memory:")
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> saveService(conn, 'masar1', desc='non-empty description')
1
>>> saveService(conn, 'masar2', desc='non-empty description')
2
>>> saveServiceConfig(conn, 'masar1', 'orbit C01', 'BPM horizontal readout for storage ring')
1
>>> saveServiceConfig(conn, 'masar1', 'orbit C02', 'BPM horizontal readout for storage ring')
2
>>> saveServiceConfig(conn, 'masar2', 'orbit C01', 'BPM horizontal readout for storage ring')
3
>>> saveServiceConfig(conn, 'masar2', 'orbit C02', 'BPM horizontal readout for storage ring')
4
>>> result = retrieveServiceConfigs(conn, servicename='masar1', configname='orbit C01')
>>> saveServiceEvent(conn, servicename='masar1', configname='orbit C01', comment='a service event',approval=True)
1
>>> saveServiceEvent(conn, servicename='masar1', configname='orbit C01', comment='end service event',approval=True)
2
>>> updateServiceEvent(conn, 1, comment='an updated service event', approval=True, username='******')
True
>>> results = retrieveServiceEvents(conn)
>>> for res in results[1:]:
... print (res[0], res[0], res[2], res[4])
1 1 an updated service event test user
2 2 end service event None
>>> conn.close()
"""
checkConnection(conn)
sqlsel = '''
SELECT service_event_user_tag, service_event_approval, service_event_user_name
FROM service_event
WHERE service_event_id = ?
'''
sql = '''
UPDATE service_event
SET service_event_user_tag = ?, service_event_approval=?, service_event_user_name = ?
WHERE service_event_id = ?
'''
try:
cur = conn.cursor()
cur.execute(sqlsel, (eventid, ))
comment0, approval0, username0 = cur.fetchone()
if comment == None:
comment = comment0
if not approval:
approval = approval0
if username == None:
username = username0
# each service event is a individual entity. Do not check the existence.
cur.execute(sql, (comment, approval, username, eventid))
except sqlite3.Error, e:
print('Error %s' % e.args[0])
raise
def saveServiceEvent(conn,
servicename,
configname,
comment=None,
approval=False,
username=None):
"""
save an event config, and associate this event with given service and service config.
>>> import sqlite3
>>> from pymasarsqlite.service.service import (saveService, retrieveServices)
>>> from pymasarsqlite.service.serviceconfig import (saveServiceConfig)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> conn = sqlite3.connect(":memory:")
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> saveService(conn, 'masar1', desc='non-empty description')
1
>>> saveService(conn, 'masar2', desc='non-empty description')
2
>>> saveServiceConfig(conn, 'masar1', 'orbit C01', 'BPM horizontal readout for storage ring')
1
>>> saveServiceConfig(conn, 'masar1', 'orbit C02', 'BPM horizontal readout for storage ring')
2
>>> saveServiceConfig(conn, 'masar2', 'orbit C01', 'BPM horizontal readout for storage ring')
3
>>> saveServiceConfig(conn, 'masar2', 'orbit C02', 'BPM horizontal readout for storage ring')
4
>>> result = retrieveServiceConfigs(conn, servicename='masar1', configname='orbit C01')
>>> saveServiceEvent(conn, servicename='masar1', configname='orbit C01', comment='a service event')
1
>>> conn.close()
"""
if servicename is None or configname is None:
raise Exception(
'service or service config is empty. Can not associate the event with service and its config.'
)
checkConnection(conn)
if configname is None:
raise Exception("service config name is not specified for this event.")
serviceconfigid = retrieveServiceConfigs(conn,
servicename=servicename,
configname=configname)[1:]
if len(serviceconfigid) > 0:
serviceconfigid = serviceconfigid[0][0]
else:
raise Exception('Can not find service config (%s) with service (%s)' %
(configname, servicename))
sql = '''
insert into service_event(service_event_id, service_config_id, service_event_user_tag, service_event_UTC_time, service_event_approval, service_event_user_name)
values (?, ?, ?, datetime('now'), ?, ?)
'''
try:
cur = conn.cursor()
# each service event is a individual entity. Do not check the existence.
cur.execute(sql, (None, serviceconfigid, comment, approval, username))
except sqlite3.Error, e:
print('Error %s' % e.args[0])
raise
def saveGroupPvs(conn, pvgroupname, pvlist, pvdesc=None, update=False):
"""
This method saves all pvs in pvlist, and assigns those pvs to given pv group name. If update is true,
update the pvs in the pv group. By default, it uses existing pvs belonging to that group. Currently,
the update flag is a place holder to future usage. It is not allowed to append any pv to existing pv
group.
In general, once a pv group exists, it is not encourage to modify it. Create a new pv group
instead of appending any pv into existing one.
>>> import sqlite3
>>> from __future__ import print_function
>>> from pymasarsqlite.pvgroup.pv import (savePvs, retrieveGroupPvs)
>>> from pymasarsqlite.pvgroup.pvgroup import(savePvGroup, retrievePvGroups)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> conn = sqlite3.connect(":memory:")
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> pvs = ['SR:C01-BI:G02A<BPM:L1>Pos-X', 'SR:C01-BI:G02A<BPM:L2>Pos-X',
... 'SR:C01-BI:G04A<BPM:M1>Pos-X', 'SR:C01-BI:G04B<BPM:M1>Pos-X',
... 'SR:C01-BI:G06B<BPM:H1>Pos-X', 'SR:C01-BI:G06B<BPM:H2>Pos-X',
... 'SR:C02-BI:G02A<BPM:H1>Pos-X', 'SR:C02-BI:G02A<BPM:H2>Pos-X',
... 'SR:C02-BI:G04A<BPM:M1>Pos-X', 'SR:C02-BI:G04B<BPM:M1>Pos-X',
... 'SR:C02-BI:G06B<BPM:L1>Pos-X', 'SR:C02-BI:G06B<BPM:L2>Pos-X',
... 'SR:C03-BI:G02A<BPM:L1>Pos-X', 'SR:C03-BI:G02A<BPM:L2>Pos-X',
... 'SR:C03-BI:G04A<BPM:M1>Pos-X', 'SR:C03-BI:G04B<BPM:M1>Pos-X']
>>> pvdesc = ['Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2']
>>> name = 'masar'
>>> desc = 'this is my first pv group for masar service'
>>> savePvGroup(conn, name, func=desc)
[1]
>>> pvgroups = retrievePvGroups(conn, name)
>>> for pvgroup in pvgroups:
... print (pvgroup[0], pvgroup[1])
1 masar
>>> saveGroupPvs(conn, name, pvs, pvdesc=pvdesc)
[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16]
>>> saveGroupPvs(conn, name, pvs, pvdesc=pvdesc)
[]
>>> conn.close()
"""
checkConnection(conn)
cur = conn.cursor()
cur.execute('select pv_group_id from pv_group where pv_group_name = ?',
(pvgroupname, ))
pvgroupid = cur.fetchall()
if len(pvgroupid) != 1:
raise Exception(
'''pv group name ({0}) is not unique, or not exist.'''.format(
pvgroupname))
sys.exit()
else:
pvgroupid = pvgroupid[0][0]
cur.execute('select pv__pvgroup_id from pv__pvgroup where pv_group_id = ?',
(pvgroupid, ))
pv_pvg_id = cur.fetchone()
pv_pvg_ids = []
if pv_pvg_id is None:
"the pv group with pvgroupid does not exist."
pv_ids = savePvs(conn, pvlist, pvdesc=pvdesc)
for pv_id in pv_ids:
cur.execute(
'select pv__pvgroup_id from pv__pvgroup where pv_id = ? and pv_group_id = ?',
(pv_id, pvgroupid))
pv_pvg_id = cur.fetchone()
if pv_pvg_id is None:
cur.execute(
'insert into pv__pvgroup (pv__pvgroup_id, pv_id, pv_group_id) values (?,?,?)',
(None, pv_id, pvgroupid))
pv_pvg_id = cur.lastrowid
else:
pv_pvg_id = pv_pvg_id[0]
pv_pvg_ids.append(pv_pvg_id)
elif update:
"the pv group with pvgroupid exists, and need to be updated."
else:
"the pv group with pvgroupid exists, and not need to be updated."
return pv_pvg_ids
def saveSnapshot(conn,
data,
servicename=None,
configname=None,
comment=None,
approval=False):
"""
save a snapshot (masar event) with data.
The data format is a tuple array like
[('pv name', 'string value', 'double value', 'long value', 'dbr type', 'isConnected',
'secondsPastEpoch', 'nanoSeconds', 'timeStampTag', 'alarmSeverity', 'alarmStatus', 'alarmMessage',
'is_array', 'array_value')]
Return service_event_id, masar_data_id[].
>>> import sqlite3
>>> from pymasarsqlite.service.service import (saveService, retrieveServices)
>>> from pymasarsqlite.service.serviceconfig import (saveServiceConfig, retrieveServiceConfigs)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> conn = sqlite3.connect(":memory:")
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> saveService(conn, 'masar1', desc='non-empty description')
1
>>> saveService(conn, 'masar2', desc='non-empty description')
2
>>> saveServiceConfig(conn, 'masar1', 'orbit C01', 'BPM horizontal readout for storage ring')
1
>>> saveServiceConfig(conn, 'masar1', 'orbit C02', 'BPM horizontal readout for storage ring')
2
>>> saveServiceConfig(conn, 'masar2', 'orbit C01', 'BPM horizontal readout for storage ring')
3
>>> saveServiceConfig(conn, 'masar2', 'orbit C02', 'BPM horizontal readout for storage ring')
4
>>> result = retrieveServiceConfigs(conn, servicename='masar1', configname='orbit C01')
>>> data = [('SR:C01-BI:G02A<BPM:L1>Pos-X','12.2', 12.2, 12, 6, 1, 435686768234, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G02A<BPM:L2>Pos-X', '12.2', 12.2, 12, 6, 1, 435686768234, 3452345098734, 0, 0, 0, "", 1, [1.2,2.3, 3.4, 4.5]),
... ('SR:C01-BI:G04A<BPM:M1>Pos-X', '12.2', 12.2, 12, 6, 1, 435686768234, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G04B<BPM:M1>Pos-X', '12.2', 12.2, 12, 6, 1, 435686768234, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G06B<BPM:H1>Pos-X', '12.2', 12.2, 12, 6, 1, 435686768234, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G06B<BPM:H2>Pos-X', '12.2', 12.2, 12, 6, 1, 435686768234, 3452345098734, 0, 0, 0, "", 0, [])]
>>> saveSnapshot(conn, data, servicename='masar1', configname='orbit C01', comment='a service event')
(1, [1, 2, 3, 4, 5, 6])
>>> data = [('SR:C01-BI:G02A<BPM:L1>Pos-X','12.2', 12.2, 12, 6, 1, 564562342566, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G02A<BPM:L2>Pos-X', '12.2', 12.2, 12, 6, 1, 564562342566, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G04A<BPM:M1>Pos-X', '12.2', 12.2, 12, 6, 1, 564562342566, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G04B<BPM:M1>Pos-X', '12.2', 12.2, 12, 6, 1, 564562342566, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G06B<BPM:H1>Pos-X', '12.2', 12.2, 12, 6, 1, 564562342566, 3452345098734, 0, 0, 0, "", 1, [1.2,2.3, 3.4, 4.5]),
... ('SR:C01-BI:G06B<BPM:H2>Pos-X', '12.2', 12.2, 12, 6, 1, 564562342566, 3452345098734, 0, 0, 0, "", 0, [])]
>>> saveSnapshot(conn, data, servicename='masar1', configname='orbit C01', comment='a service event')
(2, [7, 8, 9, 10, 11, 12])
>>> conn.close()
"""
checkConnection(conn)
eventid = saveServiceEvent(conn,
servicename,
configname,
comment=comment,
approval=approval)
masarid = None
try:
masarid = __saveMasarData(conn, eventid, data)
except sqlite3.Error, e:
print('Error %s' % e.args[0])
raise
def retrieveGroupPvs(conn, pvgroupid):
"""
This methods retrieves all pvs associated with a pv_group.
Return return a tuple array with like [(pv_name1, description1),(pv_name2, description2)].
for example:
>>> import sqlite3
>>> from __future__ import print_function
>>> from pymasarsqlite.pvgroup.pv import (savePvs, retrieveGroupPvs)
>>> from pymasarsqlite.pvgroup.pvgroup import(savePvGroup, retrievePvGroups)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> conn = sqlite3.connect(":memory:")
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> pvs = ['SR:C01-BI:G02A<BPM:L1>Pos-X', 'SR:C01-BI:G02A<BPM:L2>Pos-X',
... 'SR:C01-BI:G04A<BPM:M1>Pos-X', 'SR:C01-BI:G04B<BPM:M1>Pos-X',
... 'SR:C01-BI:G06B<BPM:H1>Pos-X', 'SR:C01-BI:G06B<BPM:H2>Pos-X',
... 'SR:C02-BI:G02A<BPM:H1>Pos-X', 'SR:C02-BI:G02A<BPM:H2>Pos-X',
... 'SR:C02-BI:G04A<BPM:M1>Pos-X', 'SR:C02-BI:G04B<BPM:M1>Pos-X',
... 'SR:C02-BI:G06B<BPM:L1>Pos-X', 'SR:C02-BI:G06B<BPM:L2>Pos-X',
... 'SR:C03-BI:G02A<BPM:L1>Pos-X', 'SR:C03-BI:G02A<BPM:L2>Pos-X',
... 'SR:C03-BI:G04A<BPM:M1>Pos-X', 'SR:C03-BI:G04B<BPM:M1>Pos-X']
>>> pvdesc = ['Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2']
>>> name = 'masar'
>>> desc = 'this is my first pv group for masar service'
>>> savePvGroup(conn, name, func=desc)
[1]
>>> pvgroups = retrievePvGroups(conn, name)
>>> for pvgroup in pvgroups:
... print (pvgroup[0], pvgroup[1])
1 masar
>>> saveGroupPvs(conn, name, pvs, pvdesc=pvdesc)
[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16]
>>> results = retrieveGroupPvs(conn, 1)
>>> for result in results:
... print (result)
(u'SR:C01-BI:G02A<BPM:L1>Pos-X', u'Horizontal orbit from BPM at C01 G02 L1')
(u'SR:C01-BI:G02A<BPM:L2>Pos-X', u'Horizontal orbit from BPM at C01 G02 L2')
(u'SR:C01-BI:G04A<BPM:M1>Pos-X', u'Horizontal orbit from BPM at C01 G02 L1')
(u'SR:C01-BI:G04B<BPM:M1>Pos-X', u'Horizontal orbit from BPM at C01 G02 L2')
(u'SR:C01-BI:G06B<BPM:H1>Pos-X', u'Horizontal orbit from BPM at C01 G02 L1')
(u'SR:C01-BI:G06B<BPM:H2>Pos-X', u'Horizontal orbit from BPM at C01 G02 L2')
(u'SR:C02-BI:G02A<BPM:H1>Pos-X', u'Horizontal orbit from BPM at C01 G02 L1')
(u'SR:C02-BI:G02A<BPM:H2>Pos-X', u'Horizontal orbit from BPM at C01 G02 L2')
(u'SR:C02-BI:G04A<BPM:M1>Pos-X', u'Horizontal orbit from BPM at C01 G02 L1')
(u'SR:C02-BI:G04B<BPM:M1>Pos-X', u'Horizontal orbit from BPM at C01 G02 L2')
(u'SR:C02-BI:G06B<BPM:L1>Pos-X', u'Horizontal orbit from BPM at C01 G02 L1')
(u'SR:C02-BI:G06B<BPM:L2>Pos-X', u'Horizontal orbit from BPM at C01 G02 L2')
(u'SR:C03-BI:G02A<BPM:L1>Pos-X', u'Horizontal orbit from BPM at C01 G02 L1')
(u'SR:C03-BI:G02A<BPM:L2>Pos-X', u'Horizontal orbit from BPM at C01 G02 L2')
(u'SR:C03-BI:G04A<BPM:M1>Pos-X', u'Horizontal orbit from BPM at C01 G02 L1')
(u'SR:C03-BI:G04B<BPM:M1>Pos-X', u'Horizontal orbit from BPM at C01 G02 L2')
>>> results = retrieveGroupPvs(conn, 2)
>>> print (results)
[]
>>> conn.close()
"""
checkConnection(conn)
cur = conn.cursor()
# if there is any query performance issue, the SQL command should use left join,
# fine tuning join order to get best performance.
# Here is an example using join
SQL = ''' select pv.pv_name, pv.description from pv
left join pv__pvgroup using (pv_id)
left join pv_group using (pv_group_id)
where pv_group.pv_group_id = ?
'''
# SQL = '''select pv.pv_name, pv.description from pv, pv__pvgroup, pv_group where pv.pv_id = pv__pvgroup.pv_id
# and pv__pvgroup.pv_group_id = pv_group.pv_group_id
# and pv_group.pv_group_id = ?
# '''
cur.execute(SQL, (pvgroupid, ))
dataset = cur.fetchall()
return dataset
def savePvs(conn, pvlist, pvdesc=None, update = False):
"""
This method saves all pvs in pvlist, and returns the pv_id (the primary key) list.
If one pv already exists, it retrieves its primary key, and update its description if update is true.
>>> import sqlite3
>>> from pymasarsqlite.pvgroup.pv import (savePvs, retrieveGroupPvs)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> conn = sqlite3.connect(":memory:")
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> pvs = ['SR:C01-BI:G02A<BPM:L1>Pos-X', 'SR:C01-BI:G02A<BPM:L2>Pos-X',
... 'SR:C01-BI:G04A<BPM:M1>Pos-X', 'SR:C01-BI:G04B<BPM:M1>Pos-X',
... 'SR:C01-BI:G06B<BPM:H1>Pos-X', 'SR:C01-BI:G06B<BPM:H2>Pos-X',
... 'SR:C02-BI:G02A<BPM:H1>Pos-X', 'SR:C02-BI:G02A<BPM:H2>Pos-X',
... 'SR:C02-BI:G04A<BPM:M1>Pos-X', 'SR:C02-BI:G04B<BPM:M1>Pos-X',
... 'SR:C02-BI:G06B<BPM:L1>Pos-X', 'SR:C02-BI:G06B<BPM:L2>Pos-X',
... 'SR:C03-BI:G02A<BPM:L1>Pos-X', 'SR:C03-BI:G02A<BPM:L2>Pos-X',
... 'SR:C03-BI:G04A<BPM:M1>Pos-X', 'SR:C03-BI:G04B<BPM:M1>Pos-X']
>>> savePvs(conn, pvs)
[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16]
>>> desc = ['Horizontal orbit from BPM at C01 G02 L1', 'Horizontal orbit from BPM at C01 G02 L2',
... 'Horizontal orbit from BPM at C01 G04 M1', 'Horizontal orbit from BPM at C01 G04 M1',
... 'Horizontal orbit from BPM at C01 G06 H1', 'Horizontal orbit from BPM at C01 G06 H2',
... 'Horizontal orbit from BPM at C02 G02 H1', 'Horizontal orbit from BPM at C02 G02 H2',
... 'Horizontal orbit from BPM at C02 G04 M1', 'Horizontal orbit from BPM at C02 G04 M1',
... 'Horizontal orbit from BPM at C02 G06 L1', 'Horizontal orbit from BPM at C01 G06 L2',
... 'Horizontal orbit from BPM at C03 G02 L1', 'Horizontal orbit from BPM at C03 G02 L2',
... 'Horizontal orbit from BPM at C03 G04 M1', 'Horizontal orbit from BPM at C03 G04 M1']
>>> savePvs(conn, pvs, desc)
[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16]
>>> tmp = cur.execute('select pv_id, pv_name, description from pv')
>>> dataset = cur.fetchall()
>>> for data in dataset:
... print (data)
(1, u'SR:C01-BI:G02A<BPM:L1>Pos-X', None)
(2, u'SR:C01-BI:G02A<BPM:L2>Pos-X', None)
(3, u'SR:C01-BI:G04A<BPM:M1>Pos-X', None)
(4, u'SR:C01-BI:G04B<BPM:M1>Pos-X', None)
(5, u'SR:C01-BI:G06B<BPM:H1>Pos-X', None)
(6, u'SR:C01-BI:G06B<BPM:H2>Pos-X', None)
(7, u'SR:C02-BI:G02A<BPM:H1>Pos-X', None)
(8, u'SR:C02-BI:G02A<BPM:H2>Pos-X', None)
(9, u'SR:C02-BI:G04A<BPM:M1>Pos-X', None)
(10, u'SR:C02-BI:G04B<BPM:M1>Pos-X', None)
(11, u'SR:C02-BI:G06B<BPM:L1>Pos-X', None)
(12, u'SR:C02-BI:G06B<BPM:L2>Pos-X', None)
(13, u'SR:C03-BI:G02A<BPM:L1>Pos-X', None)
(14, u'SR:C03-BI:G02A<BPM:L2>Pos-X', None)
(15, u'SR:C03-BI:G04A<BPM:M1>Pos-X', None)
(16, u'SR:C03-BI:G04B<BPM:M1>Pos-X', None)
>>> savePvs(conn, pvs, desc, update=True)
[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16]
>>> tmp = cur.execute('select pv_id, pv_name, description from pv')
>>> dataset = cur.fetchall()
>>> for data in dataset:
... print (data)
(1, u'SR:C01-BI:G02A<BPM:L1>Pos-X', u'Horizontal orbit from BPM at C01 G02 L1')
(2, u'SR:C01-BI:G02A<BPM:L2>Pos-X', u'Horizontal orbit from BPM at C01 G02 L2')
(3, u'SR:C01-BI:G04A<BPM:M1>Pos-X', u'Horizontal orbit from BPM at C01 G04 M1')
(4, u'SR:C01-BI:G04B<BPM:M1>Pos-X', u'Horizontal orbit from BPM at C01 G04 M1')
(5, u'SR:C01-BI:G06B<BPM:H1>Pos-X', u'Horizontal orbit from BPM at C01 G06 H1')
(6, u'SR:C01-BI:G06B<BPM:H2>Pos-X', u'Horizontal orbit from BPM at C01 G06 H2')
(7, u'SR:C02-BI:G02A<BPM:H1>Pos-X', u'Horizontal orbit from BPM at C02 G02 H1')
(8, u'SR:C02-BI:G02A<BPM:H2>Pos-X', u'Horizontal orbit from BPM at C02 G02 H2')
(9, u'SR:C02-BI:G04A<BPM:M1>Pos-X', u'Horizontal orbit from BPM at C02 G04 M1')
(10, u'SR:C02-BI:G04B<BPM:M1>Pos-X', u'Horizontal orbit from BPM at C02 G04 M1')
(11, u'SR:C02-BI:G06B<BPM:L1>Pos-X', u'Horizontal orbit from BPM at C02 G06 L1')
(12, u'SR:C02-BI:G06B<BPM:L2>Pos-X', u'Horizontal orbit from BPM at C01 G06 L2')
(13, u'SR:C03-BI:G02A<BPM:L1>Pos-X', u'Horizontal orbit from BPM at C03 G02 L1')
(14, u'SR:C03-BI:G02A<BPM:L2>Pos-X', u'Horizontal orbit from BPM at C03 G02 L2')
(15, u'SR:C03-BI:G04A<BPM:M1>Pos-X', u'Horizontal orbit from BPM at C03 G04 M1')
(16, u'SR:C03-BI:G04B<BPM:M1>Pos-X', u'Horizontal orbit from BPM at C03 G04 M1')
>>> conn.close()
"""
checkConnection(conn)
if len(pvlist) == 0 or (pvdesc != None and len(pvlist) != len(pvdesc)):
raise Exception('empty pv list or length of pv list and description does not match.')
sys.exit()
cur = conn.cursor()
pv_ids = []
for i in range(len(pvlist)):
pv_name = pvlist[i]
cur.execute('select pv_id from pv where pv_name = ?', (pv_name,))
pv_id = cur.fetchone()
desc = None
if pvdesc!=None:
desc = pvdesc[i]
if pv_id is None:
cur.execute('insert into pv(pv_id, pv_name, description) values (?,?,?)', (None, pv_name, desc))
pv_id = cur.lastrowid
# cur.execute('select pv_id from pv where pv_name = ?', (pvlist[i],))
# pv_id = cur.fetchone()
else:
pv_id = pv_id[0]
if update:
"update pv description"
cur.execute('UPDATE pv SET description=? WHERE pv_name = ? and pv_id=?', (desc, pv_name, pv_id))
pv_ids.append(pv_id)
return pv_ids
def retrieveServiceConfigProps(conn, propname=None, servicename=None, configname=None):
"""
retrieve a service config property, for example, which system this config belongs to.
>>> import sqlite3
>>> from pymasarsqlite.service.service import (saveService, retrieveServices)
>>> from pymasarsqlite.service.serviceconfig import (saveServiceConfig, retrieveServiceConfigs)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> conn = sqlite3.connect(":memory:")
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> saveService(conn, 'masar1', desc='non-empty description')
1
>>> saveService(conn, 'masar2', desc='non-empty description')
2
>>> saveServiceConfig(conn, 'masar1', 'orbit C01', 'BPM horizontal readout for storage ring')
1
>>> saveServiceConfig(conn, 'masar2', 'booster orbit', 'Horizontal orbit for booster ring')
2
>>> saveServiceConfigProp(conn, 'system', 'SR', 'masar1', 'orbit C01')
1
>>> saveServiceConfigProp(conn, 'system', 'booster', 'masar2', 'booster orbit')
2
>>> retrieveServiceConfigProps(conn)[1:]
[(1, 1, u'system', u'SR'), (2, 2, u'system', u'booster')]
>>> retrieveServiceConfigProps(conn, propname='system')[1:]
[(1, 1, u'system', u'SR'), (2, 2, u'system', u'booster')]
>>> retrieveServiceConfigProps(conn, propname='system', servicename='masar1', configname='orbit C01')[1:]
[(1, 1, u'system', u'SR')]
>>> retrieveServiceConfigProps(conn, propname='system', servicename='masar2', configname='booster orbit')[1:]
[(2, 2, u'system', u'booster')]
>>> conn.close()
"""
checkConnection(conn)
serviceconfigids = retrieveServiceConfigs(conn, servicename=servicename, configname=configname)[1:]
serviceconfigid = []
if len(serviceconfigids) > 0:
for ids in serviceconfigids:
serviceconfigid.append(ids[0])
else:
raise Exception('Can not find service config (%s) with service (%s)' %(configname, servicename))
sql = '''
select service_config_prop_id, service_config_id, service_config_prop_name, service_config_prop_value from service_config_prop
'''
results = []
try:
for configid in serviceconfigid:
cur = conn.cursor()
if configid is None and propname is None:
cur.execute(sql)
elif serviceconfigid is None:
cur.execute(sql + ' where service_config_prop_name like ?', (propname,))
elif propname is None:
cur.execute(sql + ' where service_config_id = ?', (configid,))
else:
cur.execute(sql + ' where service_config_id = ? and service_config_prop_name like ?', (configid, propname,))
result = cur.fetchall()
if len(result) > 0:
results.append(result[0])
else:
print ('Did not find matched service config property.')
except sqlite3.Error, e:
print ('Error %s' %e.args[0])
raise
def retrieveServiceConfigProps(conn, propname=None, servicename=None, configname=None):
"""
retrieve a service config property, for example, which system this config belongs to.
>>> import sqlite3
>>> from pymasarsqlite.service.service import (saveService, retrieveServices)
>>> from pymasarsqlite.service.serviceconfig import (saveServiceConfig, retrieveServiceConfigs)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> conn = sqlite3.connect(":memory:")
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> saveService(conn, 'masar1', desc='non-empty description')
1
>>> saveService(conn, 'masar2', desc='non-empty description')
2
>>> saveServiceConfig(conn, 'masar1', 'orbit C01', 'BPM horizontal readout for storage ring')
1
>>> saveServiceConfig(conn, 'masar2', 'booster orbit', 'Horizontal orbit for booster ring')
2
>>> saveServiceConfigProp(conn, 'system', 'SR', 'masar1', 'orbit C01')
1
>>> saveServiceConfigProp(conn, 'system', 'booster', 'masar2', 'booster orbit')
2
>>> retrieveServiceConfigProps(conn)[1:]
[(1, 1, u'system', u'SR'), (2, 2, u'system', u'booster')]
>>> retrieveServiceConfigProps(conn, propname='system')[1:]
[(1, 1, u'system', u'SR'), (2, 2, u'system', u'booster')]
>>> retrieveServiceConfigProps(conn, propname='system', servicename='masar1', configname='orbit C01')[1:]
[(1, 1, u'system', u'SR')]
>>> retrieveServiceConfigProps(conn, propname='system', servicename='masar2', configname='booster orbit')[1:]
[(2, 2, u'system', u'booster')]
>>> conn.close()
"""
checkConnection(conn)
serviceconfigids = retrieveServiceConfigs(conn, servicename=servicename, configname=configname)[1:]
serviceconfigid = []
if len(serviceconfigids) > 0:
for ids in serviceconfigids:
serviceconfigid.append(ids[0])
else:
raise Exception("Can not find service config (%s) with service (%s)" % (configname, servicename))
sql = """
select service_config_prop_id, service_config_id, service_config_prop_name, service_config_prop_value from service_config_prop
"""
results = []
try:
for configid in serviceconfigid:
cur = conn.cursor()
if configid is None and propname is None:
cur.execute(sql)
elif serviceconfigid is None:
cur.execute(sql + " where service_config_prop_name like ?", (propname,))
elif propname is None:
cur.execute(sql + " where service_config_id = ?", (configid,))
else:
cur.execute(
sql + " where service_config_id = ? and service_config_prop_name like ?", (configid, propname)
)
result = cur.fetchall()
if len(result) > 0:
results.append(result[0])
else:
print("Did not find matched service config property.")
except sqlite3.Error, e:
print("Error %s" % e.args[0])
raise
def retrieveSnapshot(conn, eventid=None,start=None, end=None, comment=None,approval=True):
"""
retrieve masar service data with given time frame and comment.
If end time is not given, use current time. If start time is not given,
get all data during past one week before end time.
Both start time and end time should be in UTC time format.
It returns data as a tuple array like below:
service_event_user_tag, service_event_UTC_time, service_config_name, service_name
[[('user tag', 'event UTC time', 'service config name', 'service name'),
('pv name', 'string value', 'double value', 'long value', 'dbr type', 'isConnected',
'secondsPastEpoch', 'nanoSeconds', 'timeStampTag', 'alarmSeverity', 'alarmStatus', 'alarmMessage',
'is_array', 'array_value')]
[('pv name', 'string value', 'double value', 'long value', 'dbr type', 'isConnected',
'secondsPastEpoch', 'nanoSeconds', 'timeStampTag', 'alarmSeverity', 'alarmStatus', 'alarmMessage',
'is_array', 'array_value')
('pv name', 'string value', 'double value', 'long value', 'dbr type', 'isConnected',
'secondsPastEpoch', 'nanoSeconds', 'timeStampTag', 'alarmSeverity', 'alarmStatus', 'alarmMessage',
'is_array', 'array_value')
...]
...
]
>>> import sqlite3
>>> from pymasarsqlite.service.service import (saveService, retrieveServices)
>>> from pymasarsqlite.service.serviceconfig import (saveServiceConfig, retrieveServiceConfigs)
>>> from pymasarsqlite.service.serviceevent import (updateServiceEvent)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> conn = sqlite3.connect(":memory:")
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> saveService(conn, 'masar1', desc='non-empty description')
1
>>> saveService(conn, 'masar2', desc='non-empty description')
2
>>> saveServiceConfig(conn, 'masar1', 'orbit C01', 'BPM horizontal readout for storage ring')
1
>>> saveServiceConfig(conn, 'masar1', 'orbit C02', 'BPM horizontal readout for storage ring')
2
>>> saveServiceConfig(conn, 'masar2', 'orbit C01', 'BPM horizontal readout for storage ring')
3
>>> saveServiceConfig(conn, 'masar2', 'orbit C02', 'BPM horizontal readout for storage ring')
4
>>> result = retrieveServiceConfigs(conn, servicename='masar1', configname='orbit C01')
>>> data = [('SR:C01-BI:G02A<BPM:L1>Pos-X','12.2', 12.2, 12, 6, 1, 435686768234, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G02A<BPM:L2>Pos-X', '12.2', 12.2, 12, 6, 1, 435686768234, 3452345098734, 0, 0, 0, "", 1, [1.2,2.3, 3.4, 4.5]),
... ('SR:C01-BI:G04A<BPM:M1>Pos-X', '12.2', 12.2, 12, 6, 1, 435686768234, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G04B<BPM:M1>Pos-X', '12.2', 12.2, 12, 6, 1, 435686768234, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G06B<BPM:H1>Pos-X', '12.2', 12.2, 12, 6, 1, 435686768234, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G06B<BPM:H2>Pos-X', '12.2', 12.2, 12, 6, 1, 435686768234, 3452345098734, 0, 0, 0, "", 0, [])]
>>> import datetime as dt
>>> import time
>>> start = dt.datetime.utcnow()
>>> time.sleep(1.0)
>>> saveSnapshot(conn, data, servicename='masar1', configname='orbit C01', comment='a service event')
(1, [1, 2, 3, 4, 5, 6])
>>> updateServiceEvent(conn, 1, approval=True, username='******')
True
>>> data = [('SR:C01-BI:G02A<BPM:L1>Pos-X','12.2', 12.2, 12, 6, 1, 564562342566, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G02A<BPM:L2>Pos-X', '12.2', 12.2, 12, 6, 1, 564562342566, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G04A<BPM:M1>Pos-X', '12.2', 12.2, 12, 6, 1, 564562342566, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G04B<BPM:M1>Pos-X', '12.2', 12.2, 12, 6, 1, 564562342566, 3452345098734, 0, 0, 0, "", 0, []),
... ('SR:C01-BI:G06B<BPM:H1>Pos-X', '12.2', 12.2, 12, 6, 1, 564562342566, 3452345098734, 0, 0, 0, "", 1, [1.2,2.3, 3.4, 4.5]),
... ('SR:C01-BI:G06B<BPM:H2>Pos-X', '12.2', 12.2, 12, 6, 1, 564562342566, 3452345098734, 0, 0, 0, "", 0, [])]
>>> time.sleep(1.0)
>>> end1 = dt.datetime.utcnow()
>>> time.sleep(1.0)
>>> saveSnapshot(conn, data, servicename='masar1', configname='orbit C01', comment='a service event')
(2, [7, 8, 9, 10, 11, 12])
>>> updateServiceEvent(conn, 2, approval=True, username='******')
True
>>> time.sleep(1.0)
>>> end2 = dt.datetime.utcnow()
>>> datas = retrieveSnapshot(conn, start=start, end=end1)
>>> print (datas[1][0][0], ',', datas[1][0][2], ',', datas[1][0][3])
a service event , orbit C01 , masar1
>>> for data in datas[1][1:]:
... print (data)
(u'SR:C01-BI:G02A<BPM:L1>Pos-X', u'12.2', 12.2, 12, 6, 1, 435686768234L, 3452345098734L, 0, 0, 0, u'', 0, [])
(u'SR:C01-BI:G02A<BPM:L2>Pos-X', u'12.2', 12.2, 12, 6, 1, 435686768234L, 3452345098734L, 0, 0, 0, u'', 1, [1.2, 2.3, 3.4, 4.5])
(u'SR:C01-BI:G04A<BPM:M1>Pos-X', u'12.2', 12.2, 12, 6, 1, 435686768234L, 3452345098734L, 0, 0, 0, u'', 0, [])
(u'SR:C01-BI:G04B<BPM:M1>Pos-X', u'12.2', 12.2, 12, 6, 1, 435686768234L, 3452345098734L, 0, 0, 0, u'', 0, [])
(u'SR:C01-BI:G06B<BPM:H1>Pos-X', u'12.2', 12.2, 12, 6, 1, 435686768234L, 3452345098734L, 0, 0, 0, u'', 0, [])
(u'SR:C01-BI:G06B<BPM:H2>Pos-X', u'12.2', 12.2, 12, 6, 1, 435686768234L, 3452345098734L, 0, 0, 0, u'', 0, [])
>>> datasets = retrieveSnapshot(conn, start=start, end=end2)
>>> for dataset in datasets[1:]:
... print (dataset[0][0], ',', dataset[0][2], ',', dataset[0][3])
... for data in dataset[1:]:
... print (data)
a service event , orbit C01 , masar1
(u'SR:C01-BI:G02A<BPM:L1>Pos-X', u'12.2', 12.2, 12, 6, 1, 435686768234L, 3452345098734L, 0, 0, 0, u'', 0, [])
(u'SR:C01-BI:G02A<BPM:L2>Pos-X', u'12.2', 12.2, 12, 6, 1, 435686768234L, 3452345098734L, 0, 0, 0, u'', 1, [1.2, 2.3, 3.4, 4.5])
(u'SR:C01-BI:G04A<BPM:M1>Pos-X', u'12.2', 12.2, 12, 6, 1, 435686768234L, 3452345098734L, 0, 0, 0, u'', 0, [])
(u'SR:C01-BI:G04B<BPM:M1>Pos-X', u'12.2', 12.2, 12, 6, 1, 435686768234L, 3452345098734L, 0, 0, 0, u'', 0, [])
(u'SR:C01-BI:G06B<BPM:H1>Pos-X', u'12.2', 12.2, 12, 6, 1, 435686768234L, 3452345098734L, 0, 0, 0, u'', 0, [])
(u'SR:C01-BI:G06B<BPM:H2>Pos-X', u'12.2', 12.2, 12, 6, 1, 435686768234L, 3452345098734L, 0, 0, 0, u'', 0, [])
a service event , orbit C01 , masar1
(u'SR:C01-BI:G02A<BPM:L1>Pos-X', u'12.2', 12.2, 12, 6, 1, 564562342566L, 3452345098734L, 0, 0, 0, u'', 0, [])
(u'SR:C01-BI:G02A<BPM:L2>Pos-X', u'12.2', 12.2, 12, 6, 1, 564562342566L, 3452345098734L, 0, 0, 0, u'', 0, [])
(u'SR:C01-BI:G04A<BPM:M1>Pos-X', u'12.2', 12.2, 12, 6, 1, 564562342566L, 3452345098734L, 0, 0, 0, u'', 0, [])
(u'SR:C01-BI:G04B<BPM:M1>Pos-X', u'12.2', 12.2, 12, 6, 1, 564562342566L, 3452345098734L, 0, 0, 0, u'', 0, [])
(u'SR:C01-BI:G06B<BPM:H1>Pos-X', u'12.2', 12.2, 12, 6, 1, 564562342566L, 3452345098734L, 0, 0, 0, u'', 1, [1.2, 2.3, 3.4, 4.5])
(u'SR:C01-BI:G06B<BPM:H2>Pos-X', u'12.2', 12.2, 12, 6, 1, 564562342566L, 3452345098734L, 0, 0, 0, u'', 0, [])
>>> datasets = retrieveSnapshot(conn, start=end2)
>>> print (datasets[0][0])
(u'user tag', u'event UTC time', u'service config name', u'service name')
>>> print (datasets[0][1])
(u'pv name', u'string value', u'double value', u'long value', u'dbr type', u'isConnected', u'secondsPastEpoch', u'nanoSeconds', u'timeStampTag', u'alarmSeverity', u'alarmStatus', u'alarmMessage', u'is_array', u'array_value')
>>> conn.close()
"""
checkConnection(conn)
dataset = []
datahead = [[('user tag', 'event UTC time', 'service config name', 'service name'),
('pv name', 'string value', 'double value', 'long value', 'dbr type', 'isConnected',
'secondsPastEpoch', 'nanoSeconds', 'timeStampTag', 'alarmSeverity', 'alarmStatus', 'alarmMessage',
'is_array', 'array_value')]]
sql = '''
select service_event_user_tag, service_event_UTC_time, service_config_name, service_name
from service_event
left join service_config using (service_config_id)
left join service using (service_id)
where service_event_id = ?
'''
try:
if eventid:
data= __retrieveMasarData(conn, eventid)
# data = datahead + data[:]
cur = conn.cursor()
cur.execute(sql, (eventid,))
result =cur.fetchall()
data = result[:] + data[:]
dataset.append(data)
else:
results = retrieveServiceEvents(conn, start=start, end=end, comment=comment, approval=approval)
# print ("event retults = ", results)
sql += ' and service_config_id = ? and service_event_approval = 1 '
for result in results[1:]:
data= __retrieveMasarData(conn, result[0])
# data = datahead + data[:]
cur = conn.cursor()
cur.execute(sql, (result[0], result[1],))
result =cur.fetchall()
data = result[:] + data[:]
dataset.append(data)
dataset = datahead[:] + dataset[:]
except:
raise
return dataset
def retrieveServiceEvents(conn, configid=None, eventid=None, start=None, end=None, comment=None, user=None, approval=True):
"""
retrieve an service event with given user tag within given time frame.
If event id is given, get service event header information for that given event.
Both start and end time should be in UTC time format.
If end time is not specified, use current time. If start is not specified, use one week before end time.
It return a tuple array with format like:
[(service_event_id, service_config_id, service_event_user_tag, service_event_UTC_time, service_event_user_name)]
>>> import sqlite3
>>> from pymasarsqlite.service.service import (saveService, retrieveServices)
>>> from pymasarsqlite.service.serviceconfig import (saveServiceConfig)
>>> from pymasarsqlite.db.masarsqlite import (SQL)
>>> conn = sqlite3.connect(":memory:")
>>> cur = conn.cursor()
>>> result = cur.executescript(SQL)
>>> saveService(conn, 'masar1', desc='non-empty description')
1
>>> saveService(conn, 'masar2', desc='non-empty description')
2
>>> saveServiceConfig(conn, 'masar1', 'orbit C01', 'BPM horizontal readout for storage ring')
1
>>> saveServiceConfig(conn, 'masar1', 'orbit C02', 'BPM horizontal readout for storage ring')
2
>>> saveServiceConfig(conn, 'masar2', 'orbit C01', 'BPM horizontal readout for storage ring')
3
>>> saveServiceConfig(conn, 'masar2', 'orbit C02', 'BPM horizontal readout for storage ring')
4
>>> result = retrieveServiceConfigs(conn, servicename='masar1', configname='orbit C01')
>>> import datetime as dt
>>> saveServiceEvent(conn, servicename='masar1', configname='orbit C01', comment='a service event1',approval=True)
1
>>> start = dt.datetime.utcnow()
>>> import time
>>> time.sleep(1)
>>> saveServiceEvent(conn, servicename='masar1', configname='orbit C01', comment='a service event2',approval=True)
2
>>> end = dt.datetime.utcnow()
>>> time.sleep(1)
>>> saveServiceEvent(conn, servicename='masar1', configname='orbit C01', comment='a service event3',approval=True)
3
>>> results = retrieveServiceEvents(conn, comment='a service event1')
>>> for result in results[1:]:
... print (result[0], result[1], result[2])
1 1 a service event1
>>> results = retrieveServiceEvents(conn)
>>> for result in results[1:]:
... print (result[0], result[1], result[2])
1 1 a service event1
2 1 a service event2
3 1 a service event3
>>> results = retrieveServiceEvents(conn, start=start, end=end)
>>> for result in results[1:]:
... print (result[0], result[1], result[2])
2 1 a service event2
>>> conn.close()
"""
checkConnection(conn)
results = None
try:
cur = conn.cursor()
sql = '''
select service_event_id, service_config_id, service_event_user_tag, service_event_UTC_time,
service_event_user_name
from service_event where service_event_approval = 1
'''
if eventid is not None:
sql += ' and service_event_id = ?'
cur.execute(sql, (eventid,))
else:
if comment != None:
comment = comment.replace("*","%").replace("?","_")
sql += ' and service_event_user_tag like "%s" ' %(comment)
if user != None:
user = user.replace("*","%").replace("?","_")
sql += ' and service_event_user_name like "%s" ' %(user)
if (start is None) and (end is None):
if configid is None:
cur.execute(sql, )
else:
sql += ' and service_config_id = ?'
cur.execute(sql, (configid, ))
else:
sql += ' and service_event_UTC_time > ? and service_event_UTC_time < ? '
if end is None:
end = dt.datetime.utcnow()
if start is None:
start = end - dt.timedelta(weeks=1)
if start > end:
raise Exception('Time range error')
if configid is None:
cur.execute(sql, (start, end, ))
else:
sql += ' and service_config_id = ? '
cur.execute(sql, (start, end, configid, ))
results = cur.fetchall()
except:
raise
results = [("event_id", "config_id", "comments", "event_time", "user_name"), ] + results[:]
return results
