def setUpClass(self): # will need to manually hack these or externalize, TBD self.adminschema = os.path.normpath("C:\gis\connections\oracle19c\stg\CSCL-ditCSsg1\mschell_private\mschell.sde") self.appschema = os.path.normpath("C:\gis\connections\oracle19c\stg\CSCL-ditCSsg1\cscl_pub.sde") # these should be fixed self.systemtable = 'SYSTEM.DOITT_SDE_TERM_SESSION' self.systemproceduresql = 'BEGIN SYSTEM.sp_doitt_term_sde_session(); END; ' self.systemsessionview = 'SYSTEM.V_DOITT_USER_SES_STATUS' # when forking off to call a sleep timer in pl/sql # we will let the new thread have a few seconds to import all of its # bloated dependencies and for the system view to refresh # this is obviously a terrible anti-pattern and I will regret this decision # (sorry) self.fudgetime = 10 # tuck away schema names for later use sql = """select username from user_users""" self.adminschemaname = cx_sde.selectavalue(self.adminschema ,sql) self.appschemaname = cx_sde.selectavalue(self.appschema ,sql) # me or my brother MR sql = """select UPPER(sys_context('USERENV','OS_USER')) from dual""" self.myosuser = cx_sde.selectavalue(self.adminschema ,sql)
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_findex(self): self.testfc.index('BIN') idxkount = cx_sde.selectavalue(self.testgdb.sdeconn ,self.testgdb.fetchsql('dummyindexcount.sql')) self.assertEqual(cx_sde.selectavalue(self.testgdb.sdeconn ,self.testgdb.fetchsql('dummyindexcount.sql')) ,1)
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 test_cselectavalue(self): sql = 'SELECT dummy FROM dual' sdereturn = cx_sde.selectavalue(self.sdeconn, sql) self.assertEqual(sdereturn, 'X')
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 test_dloadcondo_load(self): self.testtarget.loadcondo_load(self.datadirectory) sql = """select count(*) from condo_load""" sdereturn = cx_sde.selectavalue(self.sdeconn, sql) self.assertEqual(sdereturn, 9)
def test_egrantprivileges(self): self.assertEqual(self.testfc.grantprivileges(self.dummyuser ,'GRANT') ,0) self.assertEqual(cx_sde.selectavalue(self.testgdb.sdeconn ,self.testgdb.fetchsql('dummyuserprivcount.sql')) ,4)
def test_dselectnull(self): # should error. Its select a value, not select the void sql = 'SELECT NULL FROM dual' try: sdereturn = cx_sde.selectavalue(self.sdeconn, sql) except: pass else: self.assertFalse(sdereturn)
def test_aselect_from_system_table(self): # A - Admin user schemas can select from system table sql = 'SELECT count(*) from {0}'.format(self.systemtable) sdereturn = cx_sde.selectavalue(self.adminschema ,sql) self.assertGreaterEqual(sdereturn ,0 ,'Cant select from {0}'.format(self.systemtable))
def test_binsert_into_system_table(self): # B - Admin user schema can insert into 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) testsql = """select count(*) from {0} """ \ """where """ \ """ UPPER(username) = UPPER(sys_context('USERENV','CURRENT_USER')) """ \ """and UPPER(osuser) = UPPER(sys_context('USERENV','OS_USER')) """.format(self.systemtable) sdereturn = cx_sde.selectavalue(self.adminschema ,testsql) self.assertEqual(sdereturn ,1 ,'Cant insert into {0}'.format(self.systemtable)) # B - Admin user schema can delete from system table sql = """delete from {0} """ \ """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) sdereturn = cx_sde.selectavalue(self.adminschema ,testsql) self.assertEqual(sdereturn ,0 ,'Cant delete from {0}'.format(self.systemtable))
def checkconnection(self): check = False try: sdereturn = cx_sde.selectavalue( self.sdeconn, self.fetchsql('{0}'.format('dummysql.sql'))) if len(sdereturn) == 1: check = True except: check = False return check
def isadministratoractive(self): try: sdereturn = cx_sde.selectavalue( self.sdeconn, self.fetchsql('{0}'.format('isadministratoractive.sql'))) except: return False if sdereturn == 1: return True else: return False
def compress(self): states_removed = 0 if self.isadministrator(): if self.interpret(arcpy.Compress_management(self.sdeconn)) == 0: states_removed = cx_sde.selectavalue( self.sdeconn, self.fetchsql('{0}'.format('get_compress_states.sql'))) return states_removed
def databaseisready(self): for tablename in [ self.condoloadtable, self.plutoloadtable, self.condotable ]: sql = 'select count(*) from {0} where 1=1 '.format(tablename) try: kount = cx_sde.selectavalue(self.sdeconn, sql) except: print('failed to execute {0}'.format(sql)) return False return True
def setUpClass(self): self.sdeconn = os.environ['SDEFILE'] self.testgdb = gdb.Gdb() # c:\matt_projects\geodatabase-toiler\src\py\testdata\testdata.gpkg\main.BUILDING self.srctestfcdir = os.getcwd() + r'\\src\\py\\testdata\\' self.srctestfc = self.srctestfcdir + r'testdata.gpkg\main.BUILDING' self.testgdb.importfeatureclass(self.srctestfc ,'TOILERTESTFC') self.testfc = fc.Fc(self.testgdb ,'TOILERTESTFC') #get some non-oracle managed user on the DB to work with self.dummyuser = cx_sde.selectavalue(self.testgdb.sdeconn ,self.testgdb.fetchsql('dummyuser.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 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_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 isadministrator(self): if self.database == 'oracle': try: sdereturn = cx_sde.selectavalue( self.sdeconn, self.fetchsql('{0}'.format('isadministrator.sql'))) except: return False if sdereturn == 1: return True else: return False elif self.database == 'sqlserver': # bad! There has to be an arcpy env for this # I cant find it, looked for a whole 15 minutes and everything if self.sdeconn.lower().endswith('dbo.sde'): return True else: return False
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)