def test_hnoterminate_own(self): # H - Admin user attempt to terminate own session and os_user does not succeed sql = """insert into {0} (username, osuser) """ \ """select '{1}' """ \ """ ,UPPER(sys_context('USERENV','OS_USER')) """ \ """from dual """.format(self.systemtable ,self.adminschemaname) sdereturn = cx_sde.execute_immediate(self.adminschema ,sql) Popen(["C:/Progra~1/ArcGIS/Pro/bin/Python/scripts/propy.bat", "./src/py/test_doitt_sde_term_session_sleeper.py", self.adminschema]) time.sleep(self.fudgetime) # get count from doitt dba session view # these exact numbers are tricky, the sleep timer in Oracle seems to # producee an active and an inactive thread, so count 2 # plus there is this here session making the call, so 3, maybe? sql = """select count(*) """ \ """from {0} """ \ """where """ \ """ UPPER(client_user) = UPPER(sys_context('USERENV','OS_USER')) """ \ """and UPPER(db_user) = '{1}' """ \ """and UPPER(status) IN ('ACTIVE','INACTIVE')""".format(self.systemsessionview ,self.adminschemaname) sdereturnb4 = cx_sde.selectavalue(self.adminschema ,sql) #print('got {0} sessions before'.format(sdereturnb4)) self.assertGreaterEqual(sdereturnb4 ,2 ,'Didnt get a session going from {0}'.format(self.adminschemaname)) # call systemproceduresql, should not kill adminschema/osuser sdereturn = cx_sde.execute_immediate(self.adminschema ,self.systemproceduresql) # get count again sdereturn = cx_sde.selectavalue(self.adminschema ,sql) # print('got {0} sessions after'.format(sdereturn)) self.assertGreaterEqual(sdereturn ,2 ,'Should not have killed sessions from osuser and {0}'.format(self.adminschema)) sql = """delete from {0} """ \ """where """ \ """ UPPER(username) = '{1}' """ \ """and UPPER(osuser) = UPPER(sys_context('USERENV','OS_USER')) """.format(self.systemtable ,self.adminschemaname) sdereturn = cx_sde.execute_immediate(self.adminschema ,sql)
def test_gnoterminate_appschema(self): # G - Admin user does not terminate a session from self os_user when specifying application schema and some other os_user sql = """insert into {0} (username, osuser) """ \ """select '{1}' """ \ """ ,UPPER(sys_context('USERENV','OS_USER')) || 'XX' """ \ """from dual """.format(self.systemtable ,self.appschemaname) sdereturn = cx_sde.execute_immediate(self.adminschema ,sql) Popen(["C:/Progra~1/ArcGIS/Pro/bin/Python/scripts/propy.bat", "./src/py/test_doitt_sde_term_session_sleeper.py", self.appschema]) time.sleep(self.fudgetime) # get count from doitt dba session view sql = """select count(*) """ \ """from {0} """ \ """where """ \ """ UPPER(client_user) = UPPER(sys_context('USERENV','OS_USER')) """ \ """and UPPER(db_user) = '{1}' """ \ """and UPPER(status) IN ('ACTIVE','INACTIVE')""".format(self.systemsessionview ,self.appschemaname) sdereturnb4 = cx_sde.selectavalue(self.adminschema ,sql) #print('got {0} sessions before'.format(sdereturn)) self.assertGreaterEqual(sdereturnb4 ,1 ,'Didnt get a session going from {0}'.format(self.appschemaname)) # call systemproceduresql, should not kill appschema/osuser sdereturn = cx_sde.execute_immediate(self.adminschema ,self.systemproceduresql) # get count again, assert equal to b4 sdereturn = cx_sde.selectavalue(self.adminschema ,sql) self.assertEqual(sdereturn ,sdereturnb4 ,'Should not have killed sessions from phony osuserXX and {0}'.format(self.adminschema)) sql = """delete from {0} """ \ """where """ \ """ UPPER(username) = '{1}' """ \ """and UPPER(osuser) = UPPER(sys_context('USERENV','OS_USER')) || 'XX' """.format(self.systemtable ,self.appschemaname) sdereturn = cx_sde.execute_immediate(self.adminschema ,sql)
def config_gdb(self): # https://desktop.arcgis.com/en/arcmap/10.7/manage-data/gdbs-in-oracle/configuration-keywords.htm # just doing 1 val, risking it from sql sdereturn = cx_sde.execute_immediate( self.sdeconn, self.fetchsql('update_geometry_storage.sql')) # https://desktop.arcgis.com/en/arcmap/10.7/manage-data/gdbs-in-oracle/update-open-cursors.htm # similar going rogue sdereturn = cx_sde.execute_immediate( self.sdeconn, self.fetchsql('upsert_open_cursors.sql'))
def loadcondo(self): self.delete(self.condotable) # insert into condo # (condo_base_bbl # ,condo_billing_bbl) # select distinct # a.condo_base_bbl # ,a.condo_billing_bbl # from # condo_load a # join # pluto_load b # on # a.condo_billing_bbl = b.bbl sql = 'insert into {0} '.format(self.condotable) sql += '(condo_base_bbl ,condo_billing_bbl) ' sql += 'select distinct a.condo_base_bbl, a.condo_billing_bbl ' sql += 'from {0} a '.format(self.condoloadtable) sql += 'join {0} b '.format(self.plutoloadtable) sql += 'on a.condo_billing_bbl = b.bbl ' sdereturn = cx_sde.execute_immediate(self.sdeconn, sql) sql = 'select count(*) from {0}'.format(self.condotable) self.bblcount = cx_sde.selectavalue(self.sdeconn, sql)
def main(sdefile): #11g DBMS_LOCK.sleep sql = """begin """ \ """ sys.DBMS_SESSION.sleep({0}); """ \ """end; """.format(sleeptimer) sdereturn = cx_sde.execute_immediate(sdefile, sql)
def test_jdmlcommit(self): sql = 'create table test_cx_sde_foo as select * from dual' sdereturn = cx_sde.execute_immediate(self.sdeconn, sql) sql = """update test_cx_sde_foo a set a.dummy = 'Z'""" sdereturn = cx_sde.execute_immediate(self.sdeconn, sql) sql = """select count(*) from test_cx_sde_foo a where a.dummy = 'Z'""" sdereturn = cx_sde.selectavalue(self.sdeconn, sql) self.assertEqual(sdereturn, 1) sql = 'drop table test_cx_sde_foo' sdereturn = cx_sde.execute_immediate(self.sdeconn, sql)
def tearDownClass(self): # goes badly on reruns if fails leave detritus for my os_user in here sql = """delete from {0} """ \ """where """ \ """ UPPER(osuser) LIKE UPPER(sys_context('USERENV','OS_USER')) || '%' """.format(self.systemtable) sdereturn = cx_sde.execute_immediate(self.adminschema ,sql)
def test_bexecute_immediate(self): #sql returns a list with 2 Xs sql = 'SELECT dummy from dual UNION ALL select dummy from dual' sdereturn = cx_sde.execute_immediate(self.sdeconn, sql) self.assertIsInstance(sdereturn, list) self.assertEqual(len(sdereturn), 2)
def test_aexecute_immediate(self): #sql returns a single X sql = 'SELECT dummy from dual' sdereturn = cx_sde.execute_immediate(self.sdeconn, sql) self.assertEqual(len(sdereturn), 1) self.assertEqual(sdereturn[0], 'X')
def test_ianonymousblock(self): #refer to /src/sql/dummy.sql for anonymous pl/sql block style dummyfile = os.path.join( pathlib.Path(__file__).parent.parent, 'sql_oracle', 'dummy.sql') # avoid stripping new lines and other formatting here, allow comments with open(dummyfile, 'r') as sqlfile: sql = sqlfile.read() sdereturn = cx_sde.execute_immediate(self.sdeconn, sql)
def test_kexecutestatements(self): sql = 'create table test_cx_sde_foo2 as select * from dual' sdereturn = cx_sde.execute_immediate(self.sdeconn, sql) sqls = [] sqls.append("""insert into test_cx_sde_foo2 values('A') """) sqls.append("""insert into test_cx_sde_foo2 values('B') """) sqls.append("""insert into test_cx_sde_foo2 values('C') """) sdereturn = cx_sde.execute_statements(self.sdeconn, sqls) sql = """select count(*) from test_cx_sde_foo2 """ sdereturn = cx_sde.selectavalue(self.sdeconn, sql) self.assertEqual(sdereturn, 4) sql = 'drop table test_cx_sde_foo2 ' sdereturn = cx_sde.execute_immediate(self.sdeconn, sql)
def test_cupdate_system_table(self): # C - Admin user schema can update system table sql = """insert into {0} (username, osuser) """ \ """select sys_context('USERENV','CURRENT_USER') """ \ """ ,sys_context('USERENV','OS_USER') """ \ """from dual """.format(self.systemtable) sdereturn = cx_sde.execute_immediate(self.adminschema ,sql) sql = """update {0} """ \ """set username = username || 'XX' """ \ """ ,osuser = osuser || 'XX' """ \ """where """ \ """ UPPER(username) = UPPER(sys_context('USERENV','CURRENT_USER')) """ \ """and UPPER(osuser) = UPPER(sys_context('USERENV','OS_USER')) """.format(self.systemtable) sdereturn = cx_sde.execute_immediate(self.adminschema ,sql) testsql = """select count(*) from {0} """ \ """where """ \ """ UPPER(username) = UPPER(sys_context('USERENV','CURRENT_USER')) || 'XX' """ \ """and UPPER(osuser) = UPPER(sys_context('USERENV','OS_USER')) || 'XX' """.format(self.systemtable) sdereturn = cx_sde.selectavalue(self.adminschema ,testsql) self.assertEqual(sdereturn ,1 ,'Cant update {0}'.format(self.systemtable)) sql = """delete from {0} """ \ """where """ \ """ UPPER(username) = UPPER(sys_context('USERENV','CURRENT_USER')) || 'XX' """ \ """and UPPER(osuser) = UPPER(sys_context('USERENV','OS_USER')) || 'XX' """.format(self.systemtable) sdereturn = cx_sde.execute_immediate(self.adminschema ,sql)
def setUpClass(self): self.teardowntestdata = 'Y' self.datadirectory = os.path.join( pathlib.Path(__file__).parent, 'data', 'testdata') self.sqldirectory = os.path.join( pathlib.Path(__file__).parent, 'sql_oracle', 'definition') self.sdeconn = os.environ['SDEFILE'] self.badsdeconn = os.path.join( pathlib.Path(__file__).parent, 'data', 'testdata', 'phony.sde') self.testcondofile = 'condo_testfixtures.csv' self.testplutosql = 'plutocondo_testfixtures.sql' shutil.copyfile(os.path.join(self.datadirectory, self.testcondofile), os.path.join(self.datadirectory, 'condo.csv')) shutil.copyfile(os.path.join(self.datadirectory, self.testplutosql), os.path.join(self.datadirectory, 'plutocondo.sql')) self.testtarget = condoloader.CondoLoader() with open(os.path.join(self.sqldirectory, 'teardown.sql'), 'r') as sqlfile: self.teardownsql = sqlfile.read() sdereturn = cx_sde.execute_immediate(self.sdeconn, self.teardownsql) with open(os.path.join(self.sqldirectory, 'schema.sql'), 'r') as sqlfile: self.schemasql = sqlfile.read() sdereturn = cx_sde.execute_immediate(self.sdeconn, self.schemasql)
def checkmodules(self): logging.info( 'checking database modules required for an Enterprise Geodatabase') try: sdereturn = cx_sde.execute_immediate( self.sdeconn, self.fetchsql('gdb_requirements.sql')) except: logging.error('modules issues reported') #RAE from anonymous pl/sql block, probably a dumb pattern here #self.logger.error('sql returns: %s', sdereturn) return False return True
def tearDownClass(self): try: os.remove(os.path.join(self.datadirectory, 'condo.csv')) except: pass try: os.remove(os.path.join(self.datadirectory, 'plutocondo.sql')) except: pass if self.teardowntestdata == 'Y': sdereturn = cx_sde.execute_immediate(self.sdeconn, self.teardownsql)
def test_ecantinsert_into_system_table(self): # E - Non-admin schema cannot insert, update, delete from system table sql = """insert into {0} (username, osuser) """ \ """select sys_context('USERENV','CURRENT_USER') """ \ """ ,sys_context('USERENV','OS_USER') """ \ """from dual """.format(self.systemtable) sqlstatus = True try: sdereturn = cx_sde.execute_immediate(self.appschema ,sql) except: sqlstatus = False self.assertFalse(sqlstatus,'Non admin schema wrote to {0}'.format(self.systemtable))
def test_fspoolsql(self): if self.geodatabase.administrator: # only admin user is gonna have privileges to select dictionary views # in any expected future self.geodatabase.spoolsql('start') self.geodatabase.spoolsql('stop') sdereturn = cx_sde.selectavalue( self.sdeconn, 'select count(*) from spoolsdesql') self.assertGreaterEqual(sdereturn, 0) sdereturn = cx_sde.execute_immediate(self.sdeconn, 'drop table spoolsdesql') else: # winning self.assertTrue(True)
def test_dinsert_duplicates(self): # D - Admin user schema cannot insert duplicate schema/os_user pair sql = """insert into {0} (username, osuser) """ \ """select sys_context('USERENV','CURRENT_USER') """ \ """ ,sys_context('USERENV','OS_USER') """ \ """from dual """ \ """union all """ \ """select sys_context('USERENV','CURRENT_USER') """ \ """ ,sys_context('USERENV','OS_USER') """ \ """from dual """.format(self.systemtable) # TODO: get this right some day #self.assertRaises(ValueError,cx_sde.execute_immediate(self.adminschema # ,sql)) sqlstatus = True try: sdereturn = cx_sde.execute_immediate(self.adminschema ,sql) except: sqlstatus = False self.assertFalse(sqlstatus,'Inserted duplicates into {0}'.format(self.systemtable))
def spoolsql(self, startorstop='start'): sdereturn = cx_sde.execute_immediate( self.sdeconn, self.fetchsql('spool_sql_{0}.sql'.format(startorstop)))
def delete(self, tablename): sql = 'delete from {0}'.format(tablename) sdereturn = cx_sde.execute_immediate(self.sdeconn, sql)
def test_fterminate_appschema(self): # F - Admin user can terminate a session from self os_user connected from an application schema sql = """insert into {0} (username, osuser) """ \ """select '{1}' """ \ """ ,UPPER(sys_context('USERENV','OS_USER')) """ \ """from dual """.format(self.systemtable ,self.appschemaname) sdereturn = cx_sde.execute_immediate(self.adminschema ,sql) Popen(["C:/Progra~1/ArcGIS/Pro/bin/Python/scripts/propy.bat", "./src/py/test_doitt_sde_term_session_sleeper.py", self.appschema]) time.sleep(self.fudgetime) # get count from doitt dba session view sql = """select count(*) """ \ """from {0} """ \ """where """ \ """ UPPER(client_user) = UPPER(sys_context('USERENV','OS_USER')) """ \ """and UPPER(db_user) = '{1}' """ \ """and UPPER(status) IN ('ACTIVE','INACTIVE')""".format(self.systemsessionview ,self.appschemaname) sdereturn = cx_sde.selectavalue(self.adminschema ,sql) #print('+++++++++++++++++++++++') #print('got {0} sessions before'.format(sdereturn)) #print('+++++++++++++++++++++++') self.assertGreaterEqual(sdereturn ,1 ,'Didnt get a session going from {0}'.format(self.appschemaname)) #time.sleep(self.fudgetime) # call systemproceduresql sdereturn = cx_sde.execute_immediate(self.adminschema ,self.systemproceduresql) # get count again, assert equal to 0 sdereturn = cx_sde.selectavalue(self.adminschema ,sql) print('+++++++++++++++++++++++') print('got {0} sessions after'.format(sdereturn)) print(sql) print('+++++++++++++++++++++++') time.sleep(self.fudgetime) time.sleep(self.fudgetime) self.assertEqual(sdereturn ,0 ,'Didnt kill sessions from {0}'.format(self.appschemaname)) sql = """delete from {0} """ \ """where """ \ """ UPPER(username) = '{1}' """ \ """and UPPER(osuser) = UPPER(sys_context('USERENV','OS_USER')) """.format(self.systemtable ,self.appschemaname) sdereturn = cx_sde.execute_immediate(self.adminschema ,sql)