def SQLITE_DB_TEST_SETUP(): # DB SETUP __sqlitedb__ = os.environ["MASAR_SQLITE_DB"] try: conn = sqlite3.connect(__sqlitedb__) cur = conn.cursor() __sql__ = None if __sql__ is None: from pymasarsqlite.db.masarsqlite import SQL else: sqlfile = open(__sql__) SQL = sqlfile.read() if SQL is None: print('SQLite script is empty. Cannot create SQLite db.') raise IOError('SQLite script is empty. Cannot create SQLite db.') else: cur.executescript(SQL) cur.execute("PRAGMA main.page_size= 4096;") cur.execute("PRAGMA main.default_cache_size= 10000;") cur.execute("PRAGMA main.locking_mode=EXCLUSIVE;") cur.execute("PRAGMA main.synchronous=NORMAL;") cur.execute("PRAGMA main.journal_mode=WAL;") cur.execute("PRAGMA main.temp_store = MEMORY;") cur.execute('select name from sqlite_master where type=\'table\'') masarconf = 'SR_All_20140421' servicename = 'masar' pvgname = 'masarpvgroup' pvgdesc = 'this is my new pv group for masar service with same group name' pvs = [ "masarExample0000", "masarExample0001", "masarExampleBoUninit", "masarExampleMbboUninit", "masarExample0002", "masarExample0003", "masarExample0004", "masarExampleCharArray", "masarExampleShortArray", "masarExampleLongArray", "masarExampleStringArray", "masarExampleFloatArray", "masarExampleDoubleArray", "masarExampleMbboUninitTest" ] res = savePvGroup(conn, pvgname, func=pvgdesc) res = saveGroupPvs(conn, pvgname, pvs) pvgroups = retrievePvGroups(conn) saveService(conn, servicename, desc='test desc') saveServiceConfig(conn, servicename, masarconf, system='SR', status='active', configversion=20140420, configdesc='test desc') res = saveServicePvGroup(conn, masarconf, [pvgname]) pvlist = retrieveServiceConfigPVs(conn, masarconf, servicename=servicename) results = retrieveServiceConfigs(conn, servicename, masarconf) conn.commit() conn.close() 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 SQLITE_DB_TEST_SETUP(): # DB SETUP __sqlitedb__ = os.environ["MASAR_SQLITE_DB"] try: conn = sqlite3.connect(__sqlitedb__) cur = conn.cursor() __sql__ = None if __sql__ is None: from pymasarsqlite.db.masarsqlite import SQL else: sqlfile = open(__sql__) SQL = sqlfile.read() if SQL is None: print ('SQLite script is empty. Cannot create SQLite db.') raise IOError('SQLite script is empty. Cannot create SQLite db.') else: cur.executescript(SQL) cur.execute("PRAGMA main.page_size= 4096;") cur.execute("PRAGMA main.default_cache_size= 10000;") cur.execute("PRAGMA main.locking_mode=EXCLUSIVE;") cur.execute("PRAGMA main.synchronous=NORMAL;") cur.execute("PRAGMA main.journal_mode=WAL;") cur.execute("PRAGMA main.temp_store = MEMORY;") cur.execute('select name from sqlite_master where type=\'table\'') masarconf = 'SR_All_20140421' servicename = 'masar' pvgname = 'masarpvgroup' pvgdesc = 'this is my new pv group for masar service with same group name' pvs = ["masarExample0000", "masarExample0001", "masarExampleBoUninit", "masarExampleMbboUninit", "masarExample0002", "masarExample0003", "masarExample0004", "masarExampleCharArray", "masarExampleShortArray", "masarExampleLongArray", "masarExampleStringArray", "masarExampleFloatArray", "masarExampleDoubleArray", "masarExampleMbboUninitTest"] res = savePvGroup(conn, pvgname, func=pvgdesc) res = saveGroupPvs(conn, pvgname, pvs) pvgroups = retrievePvGroups(conn) saveService(conn, servicename, desc='test desc') saveServiceConfig(conn, servicename, masarconf, system='SR', status='active', configversion=20140420, configdesc='test desc') res = saveServicePvGroup(conn, masarconf, [pvgname]) pvlist = retrieveServiceConfigPVs(conn, masarconf, servicename=servicename) results = retrieveServiceConfigs(conn, servicename, masarconf) conn.commit() conn.close() 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 testUpdateMasarConfigStatus(self): app = QtGui.QApplication(sys.argv) ui = dbmanagerUI() ui.dbsource = 0 # Called normally with menu selection ui.databaseDefault.setText(os.environ["MASAR_SQLITE_DB"]) ui.databaseLineEdit.setText(os.environ["MASAR_SQLITE_DB"]) #ui.defaultsqlitedb() __sqlitedb__ = os.environ["MASAR_SQLITE_DB"] with sqlite3.connect(__sqlitedb__) as conn: masarconf = 'SR_All_20140421' servicename = 'masar' ui.updatemasarconfigstatus("inactive",1) results = retrieveServiceConfigs(conn, servicename, masarconf) self.assertEqual("inactive", results[1][5]) ui.updatemasarconfigstatus("active",1) results = retrieveServiceConfigs(conn, servicename, masarconf) self.assertEqual("active", results[1][5]) conn.commit()
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 testSaveMasarSqlite(self): app = QtGui.QApplication(sys.argv) ui = dbmanagerUI() ui.dbsource = 0 # Called normally with menu selection ui.databaseDefault.setText(os.environ["MASAR_SQLITE_DB"]) ui.databaseLineEdit.setText(os.environ["MASAR_SQLITE_DB"]) #ui.defaultsqlitedb() ui.test_in_progress_flag = 1 ui.savemasarsqlite() __sqlitedb__ = os.environ["MASAR_SQLITE_DB"] with sqlite3.connect(__sqlitedb__) as conn: masarconf = 'newcfgname' servicename = 'masar' results = retrieveServiceConfigs(conn, servicename, masarconf) self.assertEqual('newcfgname', results[1][1]) self.assertEqual('newcfgdesc', results[1][2]) conn.commit()
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 testSaveSQLiteServiceConfigs(self): # DB SETUP __sqlitedb__ = os.environ["MASAR_SQLITE_DB"] try: conn = sqlite3.connect(__sqlitedb__) cur = conn.cursor() __sql__ = None if __sql__ is None: from pymasarsqlite.db.masarsqlite import SQL else: sqlfile = open(__sql__) SQL = sqlfile.read() if SQL is None: print ('SQLite script is empty. Cannot create SQLite db.') raise IOError('SQLite script is empty. Cannot create SQLite db.') else: cur.executescript(SQL) cur.execute("PRAGMA main.page_size= 4096;") cur.execute("PRAGMA main.default_cache_size= 10000;") cur.execute("PRAGMA main.locking_mode=EXCLUSIVE;") cur.execute("PRAGMA main.synchronous=NORMAL;") cur.execute("PRAGMA main.journal_mode=WAL;") cur.execute("PRAGMA main.temp_store = MEMORY;") cur.execute('select name from sqlite_master where type=\'table\'') masarconf = 'SR_All_20140421' servicename = 'masar' pvgname = 'masarpvgroup' pvgdesc = 'this is my new pv group for masar service with same group name' pvs = ["masarExample0000", "masarExample0001", "masarExampleBoUninit", "masarExampleMbboUninit", "masarExample0002", "masarExample0003", "masarExample0004", "masarExampleCharArray", "masarExampleShortArray", "masarExampleLongArray", "masarExampleStringArray", "masarExampleFloatArray", "masarExampleDoubleArray", "masarExampleMbboUninitTest"] res = savePvGroup(conn, pvgname, func=pvgdesc) res = saveGroupPvs(conn, pvgname, pvs) pvgroups = retrievePvGroups(conn) saveService(conn, servicename, desc='test desc') conn.commit() conn.close() savePvGroups(self.parsed_json['pvgroups']) saveSQLiteServiceConfig(self.parsed_json) conn = sqlite3.connect(__sqlitedb__) pvgroups = retrievePvGroups(conn) self.assertEqual('BR_MG_Set_20130419', pvgroups[1][1]) self.assertEqual('Booster magnet power supply set points', pvgroups[1][2]) self.assertEqual(3, len(pvgroups[1][3].split(':'))) # Confirms correct date format self.assertEqual(3, len(pvgroups[1][3].split('-'))) # 2016-07-28 18:18:36 self.assertEqual(None, pvgroups[1][4]) configresult = retrieveServiceConfigs(conn) self.assertEqual('BR_MG_SCR_20130419', configresult[1][1]) self.assertEqual('BR ramping PS daily SCR setpoint', configresult[1][2]) self.assertEqual(3, len(configresult[1][3].split(':'))) # Confirms correct date format self.assertEqual(3, len(configresult[1][3].split('-'))) # 2016-07-28 18:18:36 self.assertEqual(None, configresult[1][4]) self.assertEqual('active', configresult[1][5]) except sqlite3.Error, e: print ("Error %s:" % e.args[0]) raise
def testConfiguration(self): channel = 'masarService' self.mc = masarClient.client(channelname=channel) # DB SETUP __sqlitedb__ = os.environ["MASAR_SQLITE_DB"] try: conn = sqlite3.connect(__sqlitedb__) cur = conn.cursor() __sql__ = None if __sql__ is None: from pymasarsqlite.db.masarsqlite import SQL else: sqlfile = open(__sql__) SQL = sqlfile.read() if SQL is None: print('SQLite script is empty. Cannot create SQLite db.') sys.exit() else: cur.executescript(SQL) cur.execute("PRAGMA main.page_size= 4096;") cur.execute("PRAGMA main.default_cache_size= 10000;") cur.execute("PRAGMA main.locking_mode=EXCLUSIVE;") cur.execute("PRAGMA main.synchronous=NORMAL;") cur.execute("PRAGMA main.journal_mode=WAL;") cur.execute("PRAGMA main.temp_store = MEMORY;") cur.execute('select name from sqlite_master where type=\'table\'') masarconf = 'SR_All_20140421' servicename = 'masar' pvgname = 'masarpvgroup' pvgdesc = 'this is my new pv group for masar service with same group name' pvs = ["masarExampleDoubleArray"] res = savePvGroup(conn, pvgname, func=pvgdesc) self.assertEqual([1], res) res2 = saveGroupPvs(conn, pvgname, pvs) with self.assertRaises(Exception) as context: saveGroupPvs(conn, "badname", pvs) self.assertEqual( context.exception.message, 'pv group name (badname) is not unique, or not exist.') self.assertEqual([1], res2) pvgroups = retrievePvGroups(conn) self.assertEqual(5, len(pvgroups[0])) self.assertEqual(1, pvgroups[0][0]) self.assertEqual(pvgname, pvgroups[0][1]) self.assertEqual(pvgdesc, pvgroups[0][2]) self.assertNotEqual(None, pvgroups[0][3]) # The following 2 tests are to confirm the date string is in the correct format self.assertEqual(3, len(pvgroups[0][3].split('-'))) self.assertEqual(3, len(pvgroups[0][3].split(':'))) self.assertEqual(None, pvgroups[0][4]) test_desc = 'test desc' test_system = 'SR' test_status = 'active' test_version = 20140420 saveService(conn, servicename, desc=test_desc) with self.assertRaises(Exception) as context: saveServiceConfig(conn, "bad servicename", masarconf, system=test_system, status=test_status, configversion=test_version, configdesc=test_desc) self.assertEqual( "service with given name (bad servicename) does not exist.", context.exception.message) with self.assertRaises(Exception) as context: saveServiceConfig(conn, servicename, masarconf, system=test_system, status="bad status", configversion=test_version, configdesc=test_desc) self.assertEqual( "Service status has to be either active, or inactive", context.exception.message) saveServiceConfig(conn, servicename, masarconf, system=test_system, status=test_status, configversion=test_version, configdesc=test_desc) with self.assertRaises(Exception) as context: saveServiceConfig(conn, servicename, masarconf, system=test_system, status=test_status, configversion=test_version, configdesc=test_desc) self.assertEqual('service config exists already.', context.exception.message) with self.assertRaises( IndexError ): # TODO: Should this be a more specific error message? saveServicePvGroup(conn, "bad config", [pvgname]) with self.assertRaises(Exception) as context: res3 = saveServicePvGroup( conn, masarconf, ["bad pvgname"]) # this test prints a message to console self.assertEqual( "given pv group name (bad pvgname) does not exist.", context.exception.message) res3 = saveServicePvGroup(conn, masarconf, [pvgname]) self.assertEqual([1], res3) with self.assertRaises(Exception) as context: pvlist = retrieveServiceConfigPVs( conn, masarconf, servicename="bad servicename") pvlist = retrieveServiceConfigPVs(conn, masarconf, servicename=servicename) self.assertEqual( context.exception.message, 'Given service (bad servicename) does not exist.') self.assertEqual(pvs, pvlist) self.expected_column_names = ('config_idx', 'config_name', 'config_desc', 'config_create_date', 'config_version', 'status') results = retrieveServiceConfigs(conn, "bad servicename", masarconf) self.assertSequenceEqual(self.expected_column_names, results[0]) self.assertEqual(1, len(results)) # IE: no data returned self.assertEqual(len(self.expected_column_names), len(results[0])) results = retrieveServiceConfigs(conn, servicename, "bad conf") self.assertSequenceEqual(self.expected_column_names, results[0]) self.assertEqual(1, len(results)) # IE: no data returned self.assertEqual(len(self.expected_column_names), len(results[0])) results = retrieveServiceConfigs(conn, servicename, masarconf) # Label tests self.assertSequenceEqual(self.expected_column_names, results[0]) self.assertEqual(2, len(results)) self.assertEqual(len(self.expected_column_names), len(results[0])) # Data tests self.assertEqual(masarconf, results[1][1]) self.assertEqual(test_desc, results[1][2]) self.assertNotEqual(None, results[1][3]) self.assertEqual(1, results[1][0]) self.assertEqual(len(self.expected_column_names), len(results[1])) # The following 2 tests are to confirm the date string is in the correct format self.assertEqual(3, len(results[1][3].split('-'))) self.assertEqual(3, len(results[1][3].split(':'))) self.assertEqual(test_version, results[1][4]) self.assertEqual(test_status, results[1][5]) conn.commit() conn.close() 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 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