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 pymasar.service.service import (saveService, retrieveServices)
>>> from pymasar.service.serviceconfig import (saveServicePvGroup, retrieveServicePvGroups)
>>> from pymasar.pvgroup.pvgroup import (savePvGroup, retrievePvGroups)
>>> from pymasar.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 retrieveServiceConfigPVs(conn, configname, servicename=None):
"""
Retrieve pv list associated with given service config name.
>>> import sqlite3
>>> conn = sqlite3.connect(':memory:')
>>> from pymasar.service.service import (saveService, retrieveServices)
>>> from pymasar.service.serviceconfig import(saveServicePvGroup, retrieveServicePvGroups)
>>> from pymasar.pvgroup.pvgroup import (savePvGroup, retrievePvGroups)
>>> from pymasar.pvgroup.pv import (saveGroupPvs, retrieveGroupPvs)
>>> from pymasar.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 saveServiceConfig(conn, servicename, configname, configdesc=None, configversion=None, system=None):
"""
Link config attributes like name, description, ... with a given service name.
The service config name for each different service has to be unique.
>>> import sqlite3
>>> from pymasar.service.service import (saveService, retrieveServices)
>>> from pymasar.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()
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 '
if configdesc is None and configversion is None:
sql = sql + ') values(?,?,?,datetime("now"))'
cur.execute(sql, (None,serviceid,configname,))
elif configversion is None:
sql = sql + ' , service_config_desc ) values(?,?,?,datetime("now"),?)'
cur.execute(sql, (None,serviceid,configname,configdesc,))
else:
sql = sql + ' , service_config_desc, service_config_version ) values(?,?,?,datetime("now"),?, ?)'
cur.execute(sql, (None,serviceid,configname,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)
