Пример #1
0
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
Пример #2
0
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
Пример #3
0
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()
Пример #4
0
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)