def insertRoles(roleList):
noOfEntries = len(roleList)
if noOfEntries <= 0:
return 0
dbHost, dbUser, dbPassword, dbName = getConfigParameters.getDBConnectionParameters()
maxRecordsInOneCommit = getConfigParameters.getMaxInsertsInOneCommit()
try:
mysqlDB = MySQLdb.connect(dbHost, dbUser, dbPassword, dbName)
mysqlDB.autocommit(False)
cursor = mysqlDB.cursor()
except:
print 'Cannot open connection to database '
return 0
pass
savepointStmt = "SAVEPOINT A"
count = 0
noOfCommits = 0
for entry in roleList:
userName = entry[0]
sphereType = entry[1]
sphereName = entry[2]
role = entry[3]
SQLStatement = "INSERT INTO user_roles (user_name, sphere_type, sphere_name, role) VALUES ('" \
+ userName + "', '" \
+ sphereType + "', '"\
+ sphereName + "', '" \
+ role + "')"
#print SQLStatement
try:
cursor.execute(SQLStatement)
cursor.execute(savepointStmt)
count = count + 1
except MySQLdb.IntegrityError, message:
#print 'Role exists for user(%s %s %s %s)' %(userName, sphereType, sphereName, role)
pass
if count >= maxRecordsInOneCommit:
count = 0
try:
mysqlDB.commit()
noOfCommits = noOfCommits + 1
except:
continue
def getGroupMembersFromDatabase(groupNames):
returnValue = []
noOfEntries = len(groupNames)
if noOfEntries <= 0:
return returnValue
dbHost, dbUser, dbPassword, dbName = getConfigParameters.getDBConnectionParameters()
try:
mysqlDB = MySQLdb.connect(dbHost, dbUser, dbPassword, dbName)
mysqlDB.autocommit(False)
cursor = mysqlDB.cursor()
except:
print 'Cannot open connection to database '
return returnValue
pass
sqlStmtPrefix = "SELECT user_name FROM user_group_mapping WHERE group_name = '"
for oneGroup in groupNames:
if oneGroup != '':
sqlStmt = sqlStmtPrefix + oneGroup + "'"
else:
continue
try:
cursor.execute(sqlStmt)
resultset = cursor.fetchall()
except:
continue
for entry in resultset:
userName = entry[0]
if userName != None:
newReturnEntry = [oneGroup, userName]
returnValue.append(newReturnEntry)
try:
cursor.close()
mysqlDB.close()
except:
pass
#for entry in returnValue:
# print entry
return returnValue
def insertUserGroupMapping(userGroupList):
dbHost, dbUser, dbPassword, dbName = getConfigParameters.getDBConnectionParameters()
maxRecordsInOneCommit = getConfigParameters.getMaxInsertsInOneCommit()
userName = ""
groupName = ""
count = 0
listSize = len(userGroupList)
if listSize <= 0:
return 0
savepointStmt = "SAVEPOINT A"
try:
mysqlDB = MySQLdb.connect(dbHost, dbUser, dbPassword, dbName)
mysqlDB.autocommit(False)
cursor = mysqlDB.cursor()
except:
return 0
noOfCommits = 0
cursor.execute(savepointStmt)
for tuple in userGroupList:
groupName = tuple[0]
userName = tuple[1]
SQLStatement = "INSERT INTO user_group_mapping (user_name, group_name) VALUES ('" \
+ userName + "', '" \
+ groupName + "')"
try:
cursor.execute(SQLStatement)
cursor.execute(savepointStmt)
count = count + 1
except MySQLdb.IntegrityError, message:
#print 'User %s is already present in group %s' % (userName, groupName)
pass
if count >= maxRecordsInOneCommit:
count = 0
try:
mysqlDB.commit()
noOfCommits = noOfCommits + 1
except:
continue
def getGroupAdminGroups():
returnValue = []
dbHost, dbUser, dbPassword, dbName = getConfigParameters.getDBConnectionParameters()
try:
mysqlDB = MySQLdb.connect(dbHost, dbUser, dbPassword, dbName)
cursor = mysqlDB.cursor()
except:
print 'Cannot open connection to database '
return returnValue
pass
sqlStatement = "SELECT name, admin_group FROM groups"
try:
cursor.execute(sqlStatement)
resultset = cursor.fetchall()
for record in resultset:
try:
groupName = record[0]
adminGroupName = record[1]
if adminGroupName != None:
oneEntry = [groupName, adminGroupName]
returnValue.append(oneEntry)
except:
print 'Error reading one record'
continue
except:
print 'Error while executing query to find group admin groups'
return returnValue
try:
cursor.close()
mysqlDB.close()
except:
pass
for entry in returnValue:
print entry
return returnValue
def insertEGroups(eGroupList):
noOfEntries = len(eGroupList)
if noOfEntries <= 0:
return 0
dbHost, dbUser, dbPassword, dbName = getConfigParameters.getDBConnectionParameters()
maxRecordsInOneCommit = getConfigParameters.getMaxInsertsInOneCommit()
try:
mysqlDB = MySQLdb.connect(dbHost, dbUser, dbPassword, dbName)
mysqlDB.autocommit(False)
cursor = mysqlDB.cursor()
except:
print 'Cannot open connection to database '
return 0
pass
savepointStmt = "SAVEPOINT A"
count = 0
noOfCommits = 0
for groupName in eGroupList:
SQLStatement = "INSERT INTO egroups(name) VALUES ('" \
+ groupName + "')"
#print SQLStatement
try:
cursor.execute(SQLStatement)
cursor.execute(savepointStmt)
count = count + 1
except MySQLdb.IntegrityError, message:
#print 'E-group %s exists..' %(groupName)
pass
if count >= maxRecordsInOneCommit:
count = 0
try:
mysqlDB.commit()
noOfCommits = noOfCommits + 1
except:
continue
def createGroupList():
dbHost, dbUser, dbPassword, dbName = getConfigParameters.getDBConnectionParameters()
returnValue = []
try:
mysqlDB = MySQLdb.connect(dbHost, dbUser, dbPassword, dbName)
cursor = mysqlDB.cursor()
except:
return 0
sqlStatement = " SELECT admin_group AS all_groups FROM groups UNION "\
"SELECT admin_group AS all_groups FROM project UNION "\
"SELECT admin_group AS all_groups FROM region "
try:
cursor.execute(sqlStatement)
resultset = cursor.fetchall()
for record in resultset:
try:
groupName = record[0]
if groupName != None:
returnValue.append(groupName)
except:
print 'Error reading one record'
continue
except:
print 'Error while executing query to find project admin groups'
return returnValue
try:
cursor.close()
mysqlDB.close()
except:
pass
for entry in returnValue:
print entry
return returnValue
def createProjectManageRoles():
dbHost, dbUser, dbPassword, dbName = getConfigParameters.getDBConnectionParameters()
try:
mysqlDB = MySQLdb.connect(dbHost, dbUser, dbPassword, dbName)
cursor = mysqlDB.cursor()
except:
print 'Cannot open connection to database '
return returnValue
pass
projectsAndAdminGroups = getProjectAdminGroups()
roleList = []
for entry in projectsAndAdminGroups:
projectName = entry[0]
adminGroup = entry[1]
if adminGroup != '':
usersList = manageUsersAndGroups.getGroupMembersFromDatabase([adminGroup])
for userGroupMapping in usersList:
groupName = userGroupMapping[0]
userName = userGroupMapping[1]
roleList.append([userName, 'project', projectName, 'manager'])
else:
pass
for entry in roleList:
print entry
insertRoles(roleList)
try:
cursor.close()
mysqlDB.close()
except:
pass
