import sys #needed for getting command line arguments
import time #determine how long this job took
import datetime #to make subtractions of two times
import re #regex checking
from jira_python import jira #working with jira

# Debug Status for Development
debug = False
# LogLevel 4=none, 3-1 different formats
loglevel = 4
# Hardcoded JIRA Base Website
jiraWebsite = "https://jira4it.hellmann.net/rest/api/2/"

if debug == True:
import pprint
loglevel = 1

# function: main
# param: jiraUser - User for Jira (text)
# jiraPassword - Password for Jira (text)
# jiraKey - current Jira Key (string)
# brief: the main function to create a pach
# description: This function creates the patch.
def main(jiraUser, jiraPassword, jiraKey):
if debug == True:
consolePrint(3, "")
#Start Information of the Script
startTime = time.clock()
startDate = datetime.datetime.now()
consolePrint(loglevel, "Start gathering Data at " + startDate.isoformat())

#build up the jira-Connection
jiraConnection = createJiraConnection(jiraUser, jiraPassword)
if debug == True:
printJiraConnectionDetails(jiraConnection)

#If jiraConnection is none, the connection doesn't work
if jiraConnection is not None:
#Use a sub-function to get information of the jira-Key.
jiraInfo = getJiraInfo(jiraConnection, jiraKey)
# we have - as expected - only one entry in the dict for the one jira-Key
if len(jiraInfo.keys()) == 1:
# Application Information
app = jiraInfo[jiraKey]["application"]
appID = app[:app.index("-")-1]
appName = app[app.index("-")+2:]
appAlias= jiraInfo[jiraKey]["alias"]
appInst = jiraInfo[jiraKey]["instance"]
wsName = jiraInfo[jiraKey]["workspace"]
schema = ''

# pprint.PrettyPrinter(indent=4).pprint(jiraInfo[jiraKey]["instance"])

# Debug-Ausgaben
consolePrint(loglevel, "")
consolePrint(loglevel, "Application ID : '" + appID + "'")
consolePrint(loglevel, "Application Name : '" + appName + "'")
consolePrint(loglevel, "Application Alias : '" + appAlias + "'")
consolePrint(loglevel, "Application Inst : '" + appInst + "'")
consolePrint(loglevel, "Workspace Name : '" + wsName + "'")
consolePrint(loglevel, "")

# get all files from the current JIRA-Key
jiraFiles = getJiraFiles(jiraConnection, jiraKey)

# sort alphabetical (DBO -> fXXX)
fileKeys = jiraFiles.keys()
fileKeys.sort()
fileCount = len(jiraFiles.keys())

if debug == True:
consolePrint(loglevel,"Found " + str(fileCount) + " Files referenced by " + jiraKey + "!")

# Write the file contents to disk
if fileCount > 0:
# Add file with APEX_INSTALL_APPLICATION calls
fplus = open('sql_plus.sql', 'w')

sqlPlus = ""
sqlPlus += "declare\n"
sqlPlus += " l_workspace_id number;\n"
sqlPlus += "begin\n"
sqlPlus += " select workspace_id into l_workspace_id\n"
sqlPlus += " from apex_workspaces\n"
sqlPlus += " where workspace = '" + wsName + "';\n"
sqlPlus += "\n"
sqlPlus += " apex_application_install.set_workspace_id(l_workspace_id);\n"
sqlPlus += " apex_application_install.set_application_id(" + appID + ");\n"
sqlPlus += " apex_application_install.set_application_name('" + appName + "');\n"
# sqlPlus += " apex_application_install.set_application_alias('" + ALIAS + "');\n"
if appInst.find("APEX5") != -1:
consolePrint(loglevel, "APEX5 Deployment!")
sqlPlus += " apex_application_install.set_auto_install_sup_obj(true);\n"
# sqlPlus += " apex_application_install.set_schema('" + schema + "');\n"
sqlPlus += " apex_application_install.generate_offset;\n"
sqlPlus += "end;\n"
sqlPlus += "/\n"
fplus.write(sqlPlus.encode('utf-8'))
fplus.close()

for fileName in fileKeys:

consolePrint(loglevel, "")
consolePrint(loglevel, "File : " + fileName)
modifyFiles(jiraInfo[jiraKey], fileName, jiraFiles[fileName]['content'])
# Append additional files to sql_plus file
fplus = open('sql_plus.sql', 'a')
fplus.write(("\n" + "@" + fileName[:fileName.rfind(".")] + ".sql\n").encode('utf-8'))
fplus.close()

fGit = open('git_save.sh', 'w')
fGit.write(gitCommit(appID, appName, wsName, jiraFiles, jiraKey).encode('utf-8'))
fGit.close()
else:
consolePrint(1, "No files were given, no deployment possible")
sys.exit(1)
else:
consolePrint(1, "There are no or to much Patches to deploy (one allowed!)")
consolePrint(1, "Waiting jiraKeys : ")
for jiraKey in jiraInfo.keys():
consolePrint(1, " '" + jiraKey + "' !")
sys.exit(1)
else:
# No valid JIRA connection could be established!
consolePrint(1, "Connection to JIRA doesn't work! Wrong credentials maybe?")
sys.exit(1)
#end of the script
#at this point we iterated through all the patches

if debug == True:
#end the timer
endDate = datetime.datetime.now()
#clock the time
endTime = time.clock()
consolePrint(loglevel, "\nFinished gathering Files at " + endDate.isoformat() + " it took ")
#calculate the difference
consolePrint(loglevel, "%.2gs" % (endTime - startTime))
consolePrint(2, "")

# function: modifyFiles
# param: fileInfo - fileInfo (dict)
# fileName - fileName (string)
# fileContent - fileContent (string)
# brief: modifies content of the application files
# returns: void
# description: modifies attached files to produce spool content for error analysis
def modifyFiles(fileInfo, fileName, fileContent):
# Extract AppID and Name / WorkspaceName from JIRA Ticket
app = fileInfo["application"]
appID = app[:app.index("-")-1]
appName = app[app.index("-")+2:]
appInst = fileInfo["instance"]
wsName = fileInfo["workspace"]

fName = fileName
fData = fileContent

validFile = False

# Add Spool Output to content - just copy&pasted from Jochen
# Add an additional message that the original files were altered
newContent = ""
newContent += "------------------------------------------------------------\n"
newContent += "-- This file is altered! Should be deployed with SQL-Plus --\n"
newContent += "------------------------------------------------------------\n"
newContent += "\n"
newContent += "SPOOL " + fName[:fName.index(".")] + ".spool\n"
newContent += "ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1;\n"
newContent += "SET SQLBLANKLINES ON\n"

newContent = unicode(newContent)
# The Application file containing pages/images/etc.
if fName.find("f"+appID) != -1:
consolePrint(loglevel, "Application-File found!")
newContent += "\n"
if appInst.find("APEX4") != -1:
consolePrint(loglevel, "APEX4 Application File!")
newContent += "DELETE APEX_040200.WWV_FLOW_WORKSHEET_CONDITIONS where flow_id = '" + appID +"';\n"

# Append Application data
newContent += "\n"
newContent += "------------------------------------------------------------\n"
newContent += "-- Contents of Application-File --\n"
newContent += "------------------------------------------------------------\n"
newContent += "\n"
newContent += fData.decode('utf-8')
# Replace all Exit-Statements
newContent = newContent.replace("exit sql.sqlcode", "EXIT 0")
newContent = newContent.replace("EXIT SQL.SQLCODE", "EXIT 0")
validFile = True
elif fName.find("DBO_"+appID) != -1:
consolePrint(loglevel, "Database-File found!")
newContent += generateDB(appInst, wsName, fData)
# newContent += fData.decode('utf-8')
validFile = True

# Write content to file
if validFile==True:
f = open(fName, 'wb')
f.write(newContent.encode('ascii', 'xmlcharrefreplace'))
f.close()

# function: getJiraFiles
# param: jiraConnection - jira Connection (Object)
# jiraKeys - jira Key (string)
# brief: gather the files from the specified jira Key
# returns: dict of jira files (id->filename, content) found by the
# specified Key OR empty dict
# description: download jira files and extract content into dict (hash)
def getJiraFiles(jiraConnection, jiraKey):
# list of found files
jiraFiles = {}

#execute the jql - return value is JSON
respJson = jiraConnection.queryJson("issue/" + jiraKey)

#only do something, if there actually are attachments in JIRA
try:
if len(respJson["fields"]["attachment"]) > 0:
consolePrint(loglevel, "Found Attachment Entries!")
#-# iterate through all attachment and retrive the key of all attachment
for x in range(0, len(respJson["fields"]["attachment"])):

# downloads the content from the file
# only saves sql files!
fileExt = respJson["fields"]["attachment"][x]["filename"]
if fileExt[fileExt.rfind(".")+1:].lower() == 'sql':
fileContent = jiraConnection.downloadJira(respJson["fields"]["attachment"][x]["content"])
#-# append the jiraKeys to the dict

jiraFiles.update(
{
respJson["fields"]["attachment"][x]["filename"] :
{
'content' : fileContent
}
}
)
# pprint.PrettyPrinter(indent=4).pprint(fileContent)
# print type(fileContent)
return jiraFiles
except KeyError as ke:
# we don't have a subkey named 'attachment'
consolePrint(1, "JSON Key " + str(ke) + " was not found!")
return {}

# function: getJiraInfo
# param: jiraConnection - jira Connection (Object)
# jiraKey - jira Key to search for (string)
# brief: gathers information for given jira Key
# returns: dict of information for jira Key
# description: TBD
def getJiraInfo(jiraConnection, jiraKey):
jiraInfo = {}

#execute the jql - return value is JSON
respJson = jiraConnection.queryJQL("key="+jiraKey)

#only do something, if there actually issues in the jql
try:
if len(respJson["issues"]) > 0:
consolePrint(loglevel, "Found Issue Entries!")
#-# iterate through all issues and retrive the key of all issues
for x in range(0, len(respJson["issues"])):
#-# append the jiraInfo to a dict
jiraInfo.update(
{
respJson["issues"][x]["key"]:
{
'instance' : respJson["issues"][x]["fields"]["customfield_14700"]["value"],
'application' : respJson["issues"][x]["fields"]["customfield_15703"],
'workspace' : "HELIOS",
'alias' : respJson["issues"][x]["fields"]["customfield_14634"]
}
}
)
return jiraInfo
except KeyError as ke:
# we don't have a subkey named 'issues'
consolePrint(1, "JSON Key " + str(ke) + " was not found!")
return {}

# function: printJiraConnectionDetails
# param: jiraConnection - jira Connection (Object)
# brief: prints all the Information about the jira Connection
def printJiraConnectionDetails(jiraConnection):
consolePrint(loglevel, "Arguments of Jira Connection")
consolePrint(loglevel, "- Api Point: " + jiraConnection.apiWebsite)
consolePrint(loglevel, "- User: " + jiraConnection.user)
consolePrint(loglevel, "- Password: " + jiraConnection.password)

# function: createJiraConnection
# param: jiraUser - jira User (text)
# jiraPassword - jira Password (text)
# brief: Initalizes the jira Connection
# returns: jiraConnection (Object)
# description: handles the initializiation of the jiraConnection
def createJiraConnection(jiraUser, jiraPassword):
jiraConnection = jira.jira( jiraUser,
jiraPassword,
jiraWebsite)
jiraTest = jiraConnection.testConnection()
if jiraTest == 'null':
jiraConnection = None
return jiraConnection

# function: consolePrint
# param: size - size in console (number)
# text - text to be printed (text)
# brief: prints information to the console
# description: pretty prints larger or smaller amounts of text to the console
# so the debugging or logging output is more understandable
def consolePrint(size, text):
if size == 3:
if debug == True:
print("############### DEBUG START ############### DEBUG START ###############")
print(text)
elif size == 2:
print(text)
if debug == True:
print("################ DEBUG END ################# DEBUG END ################")
elif size == 1:
# default just print text
print(text)

# function: printHelp
# brief: prints help about this script
def printHelp():
print("Usage: python " + sys.argv[0] + " jiraUser jiraPassword jiraKey")
sys.exit(1)

# function: gitCommit
# param: appID - application ID
# appName - application Name
# wsName - workspace Name
# files - files to save
# jiraKey - JIRA issue
# brief: Generates a full featured git-shell file
# returns: string
# description: Builds a git-shell file to simplify commits
def gitCommit(appID, appName, wsName, files, jiraKey):
gitCmd = ''

workspace = wsName.strip()
workspace = workspace.replace(" ","_")

# Set User Name and Mail for commit Messages
gitCmd += "git config user.email \"Hans-Hermann.Hunfeld@hellmann.com\"" + "\n"
gitCmd += "git config user.name \"apex-deploy-svc\"" + "\n"

# Change or create directory
gitCmd += "if [ ! -d \"" + workspace + "/" + appID + "\" ]; then" + "\n"
gitCmd += " mkdir -p " + workspace + "/" + appID + "" + "\n"
gitCmd += "fi" + "\n"

# Copy data to target directory
for fileName in files.keys():
gitCmd += "cp ../" + fileName + " ./" + workspace + "/" + appID + "\n"

gitCmd += "cp ../sql_plus.sql ./" + workspace + "/" + appID + "" + "\n"
gitCmd += "echo '" + workspace + "/" + appID + "" + "'\n"
gitCmd += "ls -al ./" + workspace + "/" + appID + "" + "\n"

gitCmd += "if [ -n \"$(git ls-files --other --exclude-standard --directory | egrep -v '/$')\" ]; then" + "\n"
gitCmd += " git add -A" + "\n"
gitCmd += " git commit -m \"JIRA Issue " + jiraKey + " : " + "Deployed '" + appID + "' from Workspace " + workspace + "\"" + "\n"
gitCmd += " git push origin master" + "\n"
gitCmd += "else" + "\n"
gitCmd += " if [ -n \"$(git status | egrep -v '/$')\" ]; then" + "\n"
gitCmd += " git add -A" + "\n"
gitCmd += " git commit -m \"JIRA Issue " + jiraKey + " : " + "Deployed '" + appID + "' from Workspace " + workspace + "\"" + "\n"
gitCmd += " git push origin master" + "\n"
gitCmd += " else" + "\n"
gitCmd += " echo 'No Changes!'" + "\n"
gitCmd += " fi" + "\n"
gitCmd += "fi" + "\n"

return gitCmd

# function: generateDB
# param: data - dbo content
# wsName - name of the workspace
# appInst - value of the apex instance
# brief: Generates a complete DBO-File
# returns: content (string)
# description: Modifies content with begin / end and checks for errors
def generateDB(appInst, wsName, data):
# Set exception handling methods
dboContent = "WHENEVER SQLERROR EXIT 0 ROLLBACK;\n"
dboContent += "\n"
# find Schema, Workspace and correct owner
dboContent += "DECLARE\n"
dboContent += " use_schema varchar2(255) := 'SYS';\n"
dboContent += " cur_schema varchar2(255) := 'SYS';\n"
dboContent += " ws_found number := 0;\n"
dboContent += "BEGIN\n"
dboContent += " SELECT COUNT(DISTINCT SCHEMA) into ws_found FROM apex_workspace_schemas where WORKSPACE_NAME = '" + wsName + "';\n"
dboContent += " IF ws_found = 0 THEN\n"
dboContent += " RAISE_APPLICATION_ERROR(-20000, 'No Workspace Found!');\n"
dboContent += " ELSIF ws_found = 1 THEN\n"
dboContent += " SELECT DISTINCT SCHEMA INTO use_schema FROM apex_workspace_schemas where WORKSPACE_NAME = '" + wsName + "';\n"
dboContent += " ELSE\n"
dboContent += " RAISE_APPLICATION_ERROR(-20000, 'Too many Workspaces Found!');\n"
dboContent += " END IF;\n"
dboContent += " EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = ' || use_schema;\n"
dboContent += " select sys_context('USER_SCHEMA','CURRENT_SCHEMA') into cur_schema from dual;\n"
dboContent += " IF cur_schema = 'SYS' THEN\n"
dboContent += " RAISE_APPLICATION_ERROR(-20000, 'No Schema Found!');\n"
dboContent += " END IF;\n"
dboContent += "END;\n"
dboContent += "/\n"
dboContent += "\n"

content = data.decode('utf-8')

# Add a disclaimer to differentiate between script and file content
dboContent += "\n"
dboContent += "------------------------------------------------------------\n"
dboContent += "-- Contents of DatabaseObject-File --\n"
dboContent += "------------------------------------------------------------\n"
dboContent += "\n"

# Append given content
dboContent += content
# End with force commit
if appInst.find("APEX4") != -1:
consolePrint(loglevel, "APEX4 Database-File!")
dboContent += "\ncommit;\n"

return dboContent;

#This is calles, if you just try to open the file, without calling the main
#function explcitly.
if __name__ == '__main__':
#check if there are enough arguments to proceed
if len(sys.argv) <> 4:
if debug == False:
#if we don't have all arguments, and we're not debugging, print the
#help function
printHelp()
else:
jiraUser = sys.argv[1]
jiraPassword = sys.argv[2]
jiraKey = sys.argv[3]
if debug == True:
#if we are debugging, we'll be lazy and just take these variables instead
consolePrint(loglevel, "DEBUG MODE - ACTIVATE jira-Variables!")
# we should now have everything we need to get started.
main(jiraUser, jiraPassword, jiraKey)