def setUp(self):
template = """
[default]
name=dbexts_test
[jdbc]
name=dbexts_test
url=%s
user=%s
pwd=%s
driver=%s
"""
args = {}
for arg in self.factory.arguments:
args[arg[0]] = arg[1]
template = template % (args["url"], args["usr"], args["pwd"], args["driver"])
if hasattr(self, "datahandler"):
template += "\tdatahandler=%s" % (self.datahandler.__name__)
template = os.linesep.join(template.split())
try:
fp = open(tempfile.mktemp(), "w")
fp.write(template)
fp.close()
self.db = dbexts.dbexts(cfg=fp.name)
self.db.verbose = 0
self.db.raw("create table one (a int, b int, c varchar(32))")
self.db.raw("create table two (a int, b int, c varchar(32))")
finally:
try:
os.remove(fp.name)
except:
pass
def __init__(self, db=None, delimiter=";"):
cmd.Cmd.__init__(self)
if db is None or type(db) == type(""):
self.db = dbexts.dbexts(db)
else:
self.db = db
self.kw = {}
self.sqlbuffer = []
self.delimiter = delimiter
self.prompt = Prompt(self)
def __init__(self, db=None, delimiter=";"):
cmd.Cmd.__init__(self)
if db is None or type(db) == type(""):
self.db = dbexts.dbexts(db)
else:
self.db = db
self.kw = {}
self.sqlbuffer = []
self.delimiter = delimiter
self.prompt = Prompt(self)
def __init__(self, db=None, delimiter=";", comment=('#', '--')):
cmd.Cmd.__init__(self, completekey=None)
if db is None or type(db) == type(""):
self.db = dbexts.dbexts(db)
else:
self.db = db
self.kw = {}
self.sqlbuffer = []
self.comment = comment
self.delimiter = delimiter
self.prompt = Prompt(self)
def __init__(self, db=None, delimiter=";", comment=('#', '--')):
cmd.Cmd.__init__(self, completekey=None)
if db is None or type(db) == type(""):
self.db = dbexts.dbexts(db)
else:
self.db = db
self.kw = {}
self.sqlbuffer = []
self.comment = comment
self.delimiter = delimiter
self.prompt = Prompt(self)
def testdb():
d, u, p, v = "jdbc:mysql://localhost/sapdb", 'root', "", "org.gjt.mm.mysql.Driver"
db = zxJDBC.connect(d,u,p,v,CHARSET='utf-8')
c = db.cursor()
c.execute("SELECT * FROM makt");
ret = c.fetchone()
print 'one record: ', json.dumps(ret).decode('unicode-escape')
# print cursor.fetchall()
all = c.fetchall()
print all
print 'all records: ' , json.dumps(all).decode('unicode-escape')
print c.description
# dbextjs usage
mysqlcon = dbexts("mysqltest", "D:\jython\lib\dbexts.ini")
print mysqlcon.table()
print "中文打印测试"
c.close()
db.close()
def setUp(self):
template = """
[default]
name=dbexts_test
[jdbc]
name=dbexts_test
url=%s
user=%s
pwd=%s
driver=%s
"""
args = {}
for arg in self.factory.arguments:
args[arg[0]] = arg[1]
template = template % (args["url"], args["usr"], args["pwd"],
args["driver"])
if hasattr(self, "datahandler"):
template += "\tdatahandler=%s" % (self.datahandler.__name__)
template = os.linesep.join(template.split())
try:
fp = open(tempfile.mktemp(), "w")
fp.write(template)
fp.close()
self.db = dbexts.dbexts(cfg=fp.name)
self.db.verbose = 0
for table in ("one", "two"):
try:
self.db.raw("drop table %s" % (table))
except:
pass
self.db.raw("create table one (a int, b int, c varchar(32))")
self.db.raw("create table two (a int, b int, c varchar(32))")
finally:
try:
os.remove(fp.name)
except:
pass
def do_use(self, arg): """\nUse a new database connection.\n""" self.db = dbexts.dbexts(arg.strip()) return None
def do_use(self, arg):
"""\nUse a new database connection.\n"""
self.db = dbexts.dbexts(arg.strip())
return None
def sqlDeploy(
mAppName, mUser, mSYSPassword, mURL, mSourceZip, mUTILSSourceZip, mTempDir, mBaseDir, mSqlHomeDir, mExecuteScript
):
print "\n xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" + "\n"
try:
if mUser != "" and mSourceZip != "":
mDBEXTSConfig = os.path.join(mTempDir, "dbexts_SYS.ini")
if os.path.isfile(mDBEXTSConfig):
os.remove(mDBEXTSConfig)
generateDBEXTSConfig("SYS AS SYSDBA", mSYSPassword, mURL, mDBEXTSConfig)
mSqlTempScript = os.path.join(mTempDir, mAppName + ".sql")
if os.path.isfile(mSqlTempScript):
os.remove(mSqlTempScript)
mSqlTempDir = os.path.join(mTempDir, mAppName)
mUTILSSqlTempDir = ""
unzipFile(mSourceZip, mSqlTempDir)
if mUTILSSourceZip != "":
mUTILSSqlTempDir = os.path.join(mTempDir, mAppName + "_VIRTUAL7UTIL")
unzipFile(mUTILSSourceZip, mUTILSSqlTempDir)
mCreateSchema = false
mCreateTables = false
mUTILSCreateTables = false
mUpdateTables = false
mUTILSUpdateTables = false
mUpdateVersions = []
mUTILSUpdateVersions = []
d = dbexts.dbexts(dbname="SYS AS SYSDBA", cfg=mDBEXTSConfig)
d.raw("select count(*) from dba_users where upper(username) = upper('" + mUser + "')")
if int(d.results[0][0]) == 0:
mCreateSchema = true
mCreateTables = true
if mUTILSSourceZip != "":
mUTILSCreateTables = true
if not mCreateSchema:
existsConfigTable = false
existsUTILSConfigTable = false
dbVersion = None
dbUTILSVersion = None
d.raw(
"select count(*) from dba_tables where UPPER(OWNER)=UPPER('"
+ mUser
+ "') and UPPER(TABLE_NAME)=UPPER('CONFIG')"
)
existsConfigTable = int(d.results[0][0]) != 0
d.raw(
"select count(*) from dba_tables where UPPER(OWNER)=UPPER('"
+ mUser
+ "') and UPPER(TABLE_NAME)=UPPER('UTILS_CONFIG')"
)
existsUTILSConfigTable = int(d.results[0][0]) != 0
if existsConfigTable:
d.raw(
"select config_value from "
+ mUser
+ ".CONFIG where upper(config_key) = upper('SCHEMA_VERSION')"
)
dbVersion = d.results[0][0]
if existsUTILSConfigTable:
d.raw(
"select config_value from "
+ mUser
+ ".UTILS_CONFIG where upper(config_key) = upper('SCHEMA_VERSION')"
)
if mUTILSSourceZip == "":
dbVersion = d.results[0][0]
else:
dbUTILSVersion = d.results[0][0]
if dbVersion == None:
mCreateTables = true
else:
mUpdateVersions = getUpdateVersions(dbVersion, os.path.join(mSqlTempDir, "updates"))
if mUpdateVersions:
mUpdateTables = true
if mUTILSSqlTempDir != "":
if dbUTILSVersion == None:
mUTILSCreateTables = true
else:
mUTILSUpdateVersions = getUpdateVersions(
dbUTILSVersion, os.path.join(mUTILSSqlTempDir, "updates")
)
if mUTILSUpdateVersions:
mUTILSUpdateTables = true
# append to the sql script
f = open(mSqlTempScript, "a")
if mCreateSchema or mCreateTables:
appendFileContent(os.path.join(mBaseDir, "CREATE_" + mAppName + ".sql"), f)
if mCreateSchema:
print >> f, "ALTER SESSION SET CURRENT_SCHEMA = SYS"
print >> f, "@" + os.path.join(os.path.join(mSqlTempDir, "create"), "000_run_as_sys_user.sql") + ";"
if mUTILSCreateTables:
appendFileContent(os.path.join(mBaseDir, "CREATE_" + mAppName + "_VIRTUAL7UTIL.sql"), f)
print >> f, "ALTER SESSION SET CURRENT_SCHEMA = " + mUser + ";"
print >> f, "@" + os.path.join(
os.path.join(mUTILSSqlTempDir, "create"), "000_run_as_app_user.sql"
) + ";"
if mCreateTables:
print >> f, "ALTER SESSION SET CURRENT_SCHEMA = " + mUser + ";"
print >> f, "@" + os.path.join(os.path.join(mSqlTempDir, "create"), "000_run_as_app_user.sql") + ";"
if mUTILSUpdateTables:
print >> f, "ALTER SESSION SET CURRENT_SCHEMA = " + mUser + ";"
for version in mUTILSUpdateVersions:
appendFileContent(
os.path.join(mBaseDir, "UPDATE_" + mAppName + "_VIRTUAL7UTIL_" + version + ".sql"), f
)
print >> f, "@" + os.path.join(
os.path.join(mUTILSSqlTempDir, "updates"), "update_" + version + ".sql"
) + ";"
if mUpdateTables:
print >> f, "ALTER SESSION SET CURRENT_SCHEMA = " + mUser + ";"
for version in mUpdateVersions:
appendFileContent(os.path.join(mBaseDir, "UPDATE_" + mAppName + "_" + version + ".sql"), f)
print >> f, "@" + os.path.join(
os.path.join(mSqlTempDir, "updates"), "update_" + version + ".sql"
) + ";"
print >> f, ""
print >> f, "EXIT"
print >> f, ""
f.close()
print "\n ### ### ###: SQL Deploy \n"
runSqlScript(mURL, mSYSPassword, mSqlHomeDir, mSqlTempScript, mTempDir, mExecuteScript)
except Exception, e:
print e
def rm(file):
if os.path.exists(file):
os.remove(file)
from org.h2.tools import DeleteDbFiles
DeleteDbFiles.execute('work', 'states', True)
unzip('data/states.db.zip', 'work')
unzip('data/states.shp.zip', 'work')
rmshp('reprojected', 'work')
rmshp('widgets2', 'work')
# init h2 database
db = dbexts('h2', 'dbexts.ini')
def h2_drop(db, tbl):
db.isql('DROP TABLE IF EXISTS "%s"' % tbl)
db.isql('DROP TABLE IF EXISTS "%s_HATBOX"' % tbl)
h2_drop(db, 'widgets')
h2_drop(db, 'widgets2')
h2_drop(db, 'states2')
h2_drop(db, 'reprojected')
db.close()
# init postgresql database
rm('%s.prj' % name)
rm('%s.fix' % name)
def rm(file):
if os.path.exists(file):
os.remove(file)
from org.h2.tools import DeleteDbFiles
DeleteDbFiles.execute('work', 'states', True)
unzip('data/states.db.zip', 'work')
unzip('data/states.shp.zip', 'work')
rmshp('reprojected', 'work')
rmshp('widgets2', 'work')
# init h2 database
db = dbexts('h2', 'dbexts.ini')
def h2_drop(db, tbl):
db.isql('DROP TABLE IF EXISTS "%s"' % tbl)
db.isql('DROP TABLE IF EXISTS "%s_HATBOX"' % tbl)
h2_drop(db, 'widgets')
h2_drop(db, 'widgets2')
h2_drop(db, 'states2')
h2_drop(db, 'reprojected')
db.close()
# init postgresql database
def pg_drop(db, tbl):
try:
db.isql("DROP TABLE %s" % (tbl))
except:
def skipIfNoDB(id):
try:
db = dbexts(id, 'dbexts.ini')
except:
raise SkipTest()
def sqlDeploy(mAppName, mUser, mSYSPassword, mURL, mSourceZip, mUTILSSourceZip,
mTempDir, mBaseDir, mSqlHomeDir, mExecuteScript):
print '\n xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' + '\n'
try:
if (mUser != '' and mSourceZip != ''):
mDBEXTSConfig = os.path.join(mTempDir, 'dbexts_SYS.ini')
if (os.path.isfile(mDBEXTSConfig)):
os.remove(mDBEXTSConfig)
generateDBEXTSConfig('SYS AS SYSDBA', mSYSPassword, mURL,
mDBEXTSConfig)
mSqlTempScript = os.path.join(mTempDir, mAppName + '.sql')
if (os.path.isfile(mSqlTempScript)):
os.remove(mSqlTempScript)
mSqlTempDir = os.path.join(mTempDir, mAppName)
mUTILSSqlTempDir = ''
unzipFile(mSourceZip, mSqlTempDir)
if (mUTILSSourceZip != ''):
mUTILSSqlTempDir = os.path.join(mTempDir,
mAppName + '_VIRTUAL7UTIL')
unzipFile(mUTILSSourceZip, mUTILSSqlTempDir)
mCreateSchema = false
mCreateTables = false
mUTILSCreateTables = false
mUpdateTables = false
mUTILSUpdateTables = false
mUpdateVersions = []
mUTILSUpdateVersions = []
d = dbexts.dbexts(dbname='SYS AS SYSDBA', cfg=mDBEXTSConfig)
d.raw(
"select count(*) from dba_users where upper(username) = upper('"
+ mUser + "')")
if (int(d.results[0][0]) == 0):
mCreateSchema = true
mCreateTables = true
if (mUTILSSourceZip != ''):
mUTILSCreateTables = true
if (not mCreateSchema):
existsConfigTable = false
existsUTILSConfigTable = false
dbVersion = None
dbUTILSVersion = None
d.raw(
"select count(*) from dba_tables where UPPER(OWNER)=UPPER('"
+ mUser + "') and UPPER(TABLE_NAME)=UPPER('CONFIG')")
existsConfigTable = int(d.results[0][0]) != 0
d.raw(
"select count(*) from dba_tables where UPPER(OWNER)=UPPER('"
+ mUser + "') and UPPER(TABLE_NAME)=UPPER('UTILS_CONFIG')")
existsUTILSConfigTable = int(d.results[0][0]) != 0
if (existsConfigTable):
d.raw(
"select config_value from " + mUser +
".CONFIG where upper(config_key) = upper('SCHEMA_VERSION')"
)
dbVersion = d.results[0][0]
if (existsUTILSConfigTable):
d.raw(
"select config_value from " + mUser +
".UTILS_CONFIG where upper(config_key) = upper('SCHEMA_VERSION')"
)
if (mUTILSSourceZip == ''):
dbVersion = d.results[0][0]
else:
dbUTILSVersion = d.results[0][0]
if (dbVersion == None):
mCreateTables = true
else:
mUpdateVersions = getUpdateVersions(
dbVersion, os.path.join(mSqlTempDir, 'updates'))
if (mUpdateVersions):
mUpdateTables = true
if (mUTILSSqlTempDir != ''):
if (dbUTILSVersion == None):
mUTILSCreateTables = true
else:
mUTILSUpdateVersions = getUpdateVersions(
dbUTILSVersion,
os.path.join(mUTILSSqlTempDir, 'updates'))
if (mUTILSUpdateVersions):
mUTILSUpdateTables = true
# append to the sql script
f = open(mSqlTempScript, 'a')
if (mCreateSchema or mCreateTables):
appendFileContent(
os.path.join(mBaseDir, 'CREATE_' + mAppName + '.sql'), f)
if (mCreateSchema):
print >> f, "ALTER SESSION SET CURRENT_SCHEMA = SYS"
print >> f, "@" + os.path.join(
os.path.join(mSqlTempDir, "create"),
"000_run_as_sys_user.sql") + ";"
if (mUTILSCreateTables):
appendFileContent(
os.path.join(mBaseDir,
'CREATE_' + mAppName + '_VIRTUAL7UTIL.sql'),
f)
print >> f, "ALTER SESSION SET CURRENT_SCHEMA = " + mUser + ";"
print >> f, "@" + os.path.join(
os.path.join(mUTILSSqlTempDir, "create"),
"000_run_as_app_user.sql") + ";"
if (mCreateTables):
print >> f, "ALTER SESSION SET CURRENT_SCHEMA = " + mUser + ";"
print >> f, "@" + os.path.join(
os.path.join(mSqlTempDir, "create"),
"000_run_as_app_user.sql") + ";"
if (mUTILSUpdateTables):
print >> f, "ALTER SESSION SET CURRENT_SCHEMA = " + mUser + ";"
for version in mUTILSUpdateVersions:
appendFileContent(
os.path.join(
mBaseDir, "UPDATE_" + mAppName + "_VIRTUAL7UTIL_" +
version + ".sql"), f)
print >> f, "@" + os.path.join(
os.path.join(mUTILSSqlTempDir, "updates"),
"update_" + version + ".sql") + ";"
if (mUpdateTables):
print >> f, "ALTER SESSION SET CURRENT_SCHEMA = " + mUser + ";"
for version in mUpdateVersions:
appendFileContent(
os.path.join(
mBaseDir,
"UPDATE_" + mAppName + "_" + version + ".sql"), f)
print >> f, "@" + os.path.join(
os.path.join(mSqlTempDir, "updates"),
"update_" + version + ".sql") + ";"
print >> f, ""
print >> f, "EXIT"
print >> f, ""
f.close()
print '\n ### ### ###: SQL Deploy \n'
runSqlScript(mURL, mSYSPassword, mSqlHomeDir, mSqlTempScript, mTempDir,
mExecuteScript)
except Exception, e:
print e
def skipIfNoDB(id):
try:
db = dbexts(id, 'dbexts.ini')
except:
raise SkipTest()