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)