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)
