def connect(dbHost,dbPort,dbName,userName="",userPass=""):
"""Establish connection to MySQL db"""
try:
userName, userPass = esdb_auth.authToESMySQL(dbHost,userName,userPass,"adminLogin")
db = MySQLdb.connect(host=dbHost,port=dbPort,user=userName,passwd=userPass,db=dbName)
except:
print "WARNING: First attempt to connect to %s fail, try again"%dbHost
time.sleep(60)
try:
db = MySQLdb.connect(host=dbHost,port=dbPort,user=userName,passwd=userPass,db=dbName)
except:
print "ERROR: Second attemp to connect to %s fail, please investigate"%dbHost
gen_util.printExcept()
sys.exit(1)
return db
def connect(dbHost, dbPort, dbName, userName="", userPass=""):
"""Establish connection to MySQL db"""
try:
userName, userPass = esdb_auth.authToESMySQL(dbHost, userName,
userPass, "adminLogin")
db = MySQLdb.connect(host=dbHost,
port=dbPort,
user=userName,
passwd=userPass,
db=dbName)
except:
print "WARNING: First attempt to connect to %s fail, try again" % dbHost
time.sleep(60)
try:
db = MySQLdb.connect(host=dbHost,
port=dbPort,
user=userName,
passwd=userPass,
db=dbName)
except:
print "ERROR: Second attemp to connect to %s fail, please investigate" % dbHost
gen_util.printExcept()
sys.exit(1)
return db
def convert(args):
"""convert module allow user to convert EventStore DB from MySQL into
SQLite format and vice versa.
Examples:
- SQLite into MySQL
- convert.py -in sqlite sqlite.db -out mysql lnx151 -esdb EventStoreTMP
- MySQL into SQLite:
- convert.py -out sqlite sqlite.db -in mysql lnx151 -esdb EventStoreTMP
"""
usage="""\n
Usage: convert.py -in <sqlite or mysql> <fileName or hostName>
-out <sqlite or mysql> <fileName or hostName>
-esdb <EventStoreDBName>
Examples:
to convert SQLite into MySQL
convert.py -in sqlite sqlite.db -out mysql lnx151 -esdb EventStoreTMP
to convert MySQL into SQLite:
convert.py -out sqlite sqlite.db -in mysql lnx151 -esdb EventStoreTMP
"""
sqliteFile= "sqlite.db"
esdb = "EventStoreTMP"
readIn = ""
readInDB = ""
writeOut = ""
writeOutDB= ""
x = 1
inOut = 0
while x < len(args):
try:
tryDB=args[x]
if args[x] == "-in" :
readIn = args[x+1]
readInDB = args[x+2]
inOut+=1
if args[x] == "-out" :
writeOut = args[x+1]
writeOutDB = args[x+2]
inOut+=1
if args[x] == "-esdb" :
esdb = args[x+1]
x = x+1
except:
print usage
sys.exit()
if inOut!=2:
print usage
sys.exit()
writeSQLite = 0
whichDB = "mysql"
sqliteFile = readInDB
mysqlHost = writeOutDB
if string.find(string.lower(writeOut),"sqlite")!=-1:
writeSQLite = 1
whichDB = "sqlite"
sqliteFile = writeOutDB
mysqlHost = readInDB
# write out what we're doing
print "Start conversion (%s): %s -> %s"%(esdb,readInDB,writeOutDB)
sys.__stdout__.flush()
# make connection to EventStoreDB in MySQL
userMySQL, passwordMySQL = esdb_auth.authToESMySQL(mysqlHost)
tup = string.split(mysqlHost,":")
if len(tup)==2:
iHost = tup[0]
if re.search("[a-zA-Z]",tup[1]):
raise "Fail to decode port number from '%s'"%tup[1]
iPort = int(tup[1])
else:
iHost = tup[0]
iPort = 3306
mysqldb = MySQLdb.connect(host=iHost,port=iPort,user=userMySQL,passwd=passwordMySQL,db=esdb)
# remove sqliteFile if necessary
if writeSQLite:
if os.path.isfile(sqliteFile):
os.remove(sqliteFile)
# make connection to EventStoreDB in SQLite
sqlitedb = sqlite.connect(sqliteFile)
# make a choice from which DB we will read and to which we will write
if writeSQLite:
dbIn = mysqldb
dbOut = sqlitedb
dbTypeIn = "mysql"
dbTypeOut = "sqlite"
else:
dbIn = sqlitedb
dbOut = mysqldb
dbTypeIn = "sqlite"
dbTypeOut = "mysql"
# Drop/create all tables
dbLog = open('esdb.log','w')
sqlUtil = sql_util.SQLUtil(dbOut,dbTypeOut,dbLog)
sqlUtil.createTables()
# get table names from DB
# esInit = es_init.ESInit(dbIn,dbTypeIn,dbLog)
# tNames = esInit.dbNames
tNames = sqlUtil.getTableNames()
maxLength = 0
for esTable in tNames:
if maxLength<len(esTable):
maxLength=len(esTable)
maxLength+=1
cursorIn = dbIn.cursor()
cursorOut= dbOut.cursor()
for esTable in tNames:
empty = "."*(maxLength-len(esTable))
msg = esTable+empty
print msg,
sys.__stdout__.flush()
try:
query = "SELECT * FROM %s"%esTable
cursorIn.execute(query)
tup = cursorIn.fetchall()
for x in tup:
query="INSERT INTO %s VALUES ( "%esTable
for idx in xrange(0,len(x)):
query+="'%s'"%x[idx]
if idx!=len(x)-1:
query+=","
query+=" ) "
cursorOut.execute(query)
print " [done]"
except:
print " [fail]"
sys.exit(1)
# In the case of SQLite DB we need to invoke commit action
if writeSQLite:
sqlitedb.commit()
def ESDBConnector(dbHost,dbName,userName="",userPass="",isolationLevel="",dbPort="",dbSocket=""):
"""ESDBConnector connect to given host(MySQL) and/or file(SQLite) using dbName.
In the cae of SQLite user may pass isolationLevel:
- default mode, if no isolationLevel is passed will start db with BEGIN
- autocommit mode, isolationLeve='None'
- you may also pass: DEFERRED, IMMEDIATE or EXCLUSIVE levels
- DEFERRED deffer locks until first access
- IMMEDIATE implies lock for first BEGIN IMMEDIATE and doesn't allow
anything to write to DB, but allow to read
- EXCLUSIVE will lock entire DB.
We use IMMEDIATE for SQLite and READ COMMITTED isolation level for MySQL.
We also turn off autocommit.
@type dbHost: string
@type dbName: string
@type userName: string
@type userPass: string
@type isolationLevel: string
@type dbPort: integer
@type dbSocket: string
@param dbHost: name of the host, lnx151.lns.cornell.edu or db file name /sqlite.db
@param dbName: DB name, obsolete in the case of SQLite
@param userName: user name
@param userPass: password
@param isolationLevel: valid only for SQLite, setup isolation level
@param dbPort: port number, e.g. default for MysQL is 3306
@param dbSocket: socket name, e.g. /var/log/mysql=
@rtype: tuple (db, dbType)
@return: return object to underlying DB and its type, e.g. "sqlite" or "mysql"
"""
sqLite = 0
try:
ipAddress = socket.gethostbyname(dbHost)
except:
# if name resolution failed we assume that our dbHost is flat file name
curDir = os.path.split(dbHost)[0]
if not curDir: curDir=os.getcwd()
dirList = os.listdir(curDir)
if os.path.isfile(dbHost) or not dirList.count(dbHost):
sqLite=1
else:
print "Cannot resolve dbHost='%s'"%dbHost
sys.exit(1)
# read userName and password from user-specific file
if not sqLite:
# check login name and password to access EventStore MySQL DB.
userName, userPass = esdb_auth.authToESMySQL(dbHost,userName,userPass)
# connect to MySQL EventStoreDB
if sqLite:
if isolationLevel:
db = sqlite.connect(dbHost,timeout=15.0,isolation_level=isolationLevel)
else:
db = sqlite.connect(dbHost,timeout=15.0,isolation_level=None)
dbType = "sqlite"
else:
if not dbName and dbHost:
print "You are attempting to connect to host '%s'"%dbHost
print "But did not specified DB name '%s'"%dbName
sys.exit(1)
try:
if dbPort:
db = MySQLdb.connect(host=dbHost,user=userName,passwd=userPass,db=dbName,port=int(dbPort))
else:
db = MySQLdb.connect(host=dbHost,user=userName,passwd=userPass,db=dbName)
except:
print "WARNING: First attempt to connect to %s@%s:%s:%s fail, try again"%(dbName,dbHost,dbPort,dbSocket)
time.sleep(60)
try:
if dbPort:
db = MySQLdb.connect(host=dbHost,user=userName,passwd=userPass,db=dbName,port=int(dbPort))
else:
db = MySQLdb.connect(host=dbHost,user=userName,passwd=userPass,db=dbName)
except:
print "ERROR: Second attempt to connect to %s@%s:%s:%s fail, please investigate"%(dbName,dbHost,dbPort,dbSocket)
gen_util.printExcept()
sys.exit(1)
dbType = "mysql"
if userName!="eventstore": ## master DB user name
cursor = db.cursor()
# cursor.execute("SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE")
cursor.execute("SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED")
cursor.execute("SET AUTOCOMMIT=0")
cursor.close()
return (db,dbType)
