AWSTemplateFormatVersion: '2010-09-09'
Description: "AWS CloudFormation Template for Building Lambda Function to Run Post Scripts on RDS. ***WARNING*** you will be billed for the AWS resources used if you create a stack from this template."
Metadata: 
  AWS::CloudFormation::Interface: 
    ParameterGroups: 
      - 
        Label: 
          default: "AWS VPC Configuration"
        Parameters: 
          - pVPCName
      - 
        Label: 
          default: "Amazon S3 Configuration"
        Parameters: 
          - pLambdaS3BucketName
          - pLambdaS3Prefix
          - pLambdaZipFileName
      - 
        Label: 
          default: "AWS Lambda Configuration"
        Parameters:
          - pLambdaSubnetName
          - pLambdaFunctionName
          - pLambdaHandlerName
    ParameterLabels: 
      pVPCName: 
        default: "Which VPC should this Lambda function be connected to?"
      pLambdaSubnetName: 
        default: "Which subnets from the above selected VPC should this Lambda function be connected to? (choose at least 2 subnets to enable HA)"
      pLambdaS3BucketName: 
        default: "What is the S3 bucket name for Lambda Zip file location? (Ex- awslambdardsoraclepost)"
      pLambdaS3Prefix: 
        default: "What is the Lambda Zip file location right after that bucket name i.e any folder name in s3 bucket? (Ex - awslambdafunction/)"
      pLambdaZipFileName: 
        default: "What is the Lambda Zip file Name? (Ex - awslambdapkg_rds_oracle_post.zip)"
      pLambdaFunctionName: 
        default: "What is the Lambda Function Name (Ex - awslambdardsoraclepostfunction)?"
      pLambdaHandlerName: 
        default: "What is the Lambda Handler Name (Ex - index.lambda_handler)?"
Parameters:
  pVPCName:
    Type: AWS::EC2::VPC::Id
  pLambdaSubnetName:
    Type: "List<AWS::EC2::Subnet::Id>"
  pLambdaS3BucketName:
    AllowedPattern: ^[0-9a-zA-Z]+([0-9a-zA-Z-]*[0-9a-zA-Z])*$
    Default: awslambdardsoraclepost
    Type: String
  pLambdaS3Prefix:
    AllowedPattern: "^[0-9a-zA-Z]+([\\/])*$"
    Default: awslambdafunction/
    Type: String
  pLambdaZipFileName:
    AllowedPattern: ^[0-12a-zA-Z-_\.]+([0-9a-zA-Z]*[0-9a-zA-Z])*$
    Default: awslambdapkg_rds_oracle_post.zip
    Type: String
  pLambdaFunctionName:
    AllowedPattern: ^[0-9a-zA-Z-]+([0-9a-zA-Z-]*[0-9a-zA-Z])*$
    Default: awslambdardsoraclepostfunction
    Type: String
  pLambdaHandlerName:
    AllowedPattern: ^[0-12a-zA-Z-_\.]+([0-9a-zA-Z]*[0-9a-zA-Z])*$
    Default: index.lambda_handler
    Type: String
Resources:
  rSG:
    Type: 'AWS::EC2::SecurityGroup'
    Properties:
      GroupDescription: Security group for Lambda which will execute the post database creation steps
      GroupName: post_lambda_sg
      SecurityGroupEgress:
        - CidrIp: 0.0.0.0/0
          Description: Allow all outbound traffic by default
          IpProtocol: '-1'
      VpcId: !Ref pVPCName
  rRole:
    Type: AWS::IAM::Role
    Properties:
      AssumeRolePolicyDocument:
        Statement:
          - Effect: Allow
            Principal:
              Service:
                - lambda.amazonaws.com
            Action:
              - sts:AssumeRole
      ManagedPolicyArns:
        - "arn:aws:iam::aws:policy/service-role/AWSLambdaVPCAccessExecutionRole"
      Description: IAM role to provide needed permission for Lambda function to execute and store logs in cloudwatch
  rLayer:
    Type: AWS::Lambda::LayerVersion
    Properties:
      CompatibleRuntimes:
        - python3.7
      Content:
        S3Bucket: !Ref pLambdaS3BucketName
        S3Key: !Sub '${pLambdaS3Prefix}${pLambdaZipFileName}'
      LayerName: OracleClientLayer
      LicenseInfo: MIT
      Description: Layer with oracle client utilities like sqlplus,cx_Oracle and SQL Scripts
  rLG:
    Type: AWS::Logs::LogGroup
    DependsOn: rFunction
    Properties: 
      LogGroupName: !Sub '/aws/lambda/${pLambdaFunctionName}'
      RetentionInDays: 7
  rFunction: 
    Type: AWS::Lambda::Function
    Properties:
      FunctionName: !Ref 'pLambdaFunctionName'
      Description: Run SQL Scripts on RDS Oracle Database using AWS Lambda function
      Handler: !Ref 'pLambdaHandlerName'
      Role: !GetAtt 'rRole.Arn'
      Runtime: python3.7
      MemorySize: 256
      Timeout: 900
      Environment: 
        Variables:
          LD_LIBRARY_PATH: /opt
          PATH: /opt
          PYTHONPATH: /opt
      Code:
        ZipFile: |
          import cfnresponse
          import cx_Oracle
          import subprocess

          def lambda_handler(event, context):

              responseData = {}
              if event['RequestType'] == 'Delete':
                responseData = {'Message': 'SUCCESSFUL!!'}
                cfnresponse.send(event, context, cfnresponse.SUCCESS, responseData, "None")
              try:

                """
                Database Information 
                """
              
                uname = str.format(event['ResourceProperties']['uname'])
                userpwd = str.format(event['ResourceProperties']['userpwd'])
                sid = str.format(event['ResourceProperties']['sid'])
                port = str.format(event['ResourceProperties']['port'])
                endpoint = str.format(event['ResourceProperties']['endpoint'])

                       
                """
                Run sql commands against a database using cx_Oracle.
                Refer https://pypi.org/project/cx-Oracle/ for more information about cx_Oracle
                """

                dsn = cx_Oracle.makedsn(endpoint, port, sid)
                connectString = uname + '/' + userpwd + '@' + dsn 
                connection = cx_Oracle.connect(connectString)
                print(connection.version)
                cursor = connection.cursor()
                cursor.execute("select * from v$instance")
                row = cursor.fetchone()
                print('Output From cx_Oracle', row)


                """
                Run sql commands against a database using sqlplus.
                """

                sqlplus_script="""
                select * from v$instance;
                start /opt/SqlScript.sql
                exit
                """

                p = subprocess.Popen(['/opt/sqlplus',connectString],stdin=subprocess.PIPE,
                stdout=subprocess.PIPE,stderr=subprocess.PIPE)
                (stdout,stderr) = p.communicate(sqlplus_script.encode('utf-8'))
                stdout_lines = stdout.decode('utf-8').split("\n")
                stderr_lines = stderr.decode('utf-8').split("\n")
                print ('Output From SqlPlus', stdout_lines)
                print ('Output From SqlPlus', stderr_lines)

                print('Completed Execution')
                responseData = {'Message': 'SUCCESSFUL!!'}
                cfnresponse.send(event, context, cfnresponse.SUCCESS, responseData, "CustomResourcePhysicalID")

              except Exception as e:
                print('Exception: ' + str(e))
                responseData = {'Message': 'FAILED!!'}
                cfnresponse.send(event, context, cfnresponse.FAILED, responseData, "CustomResourcePhysicalID")
      Layers:
        - !Ref rLayer
      VpcConfig:
        SecurityGroupIds:
                  - !GetAtt 
                      - rSG
                      - GroupId
        SubnetIds: !Ref pLambdaSubnetName
Outputs:
  orFunction:
    Value: !GetAtt rFunction.Arn
    Export:
      Name: post-scripts-Lambda-arn
  orSG:
    Value: !GetAtt 
          - rSG
          - GroupId
    Export:
      Name: rSG-GroupId