AWSTemplateFormatVersion: 2010-09-09
Description: >-
  This CloudFormation sample template to creates Oracle and MySQL instances in a
  VPC. You will be billed for the AWS resources used if you create a stack from this template.
  This template does cover all AWS region. please add the intended region in the mappings if not listed below. 
Mappings:
  OracleEngineVersion:
    ap-northeast-1:
      ver: 18.0.0.0.ru-2020-04.rur-2020-04.r1
    ap-northeast-2:
      ver: 18.0.0.0.ru-2020-04.rur-2020-04.r1
    ap-south-1:
      ver: 18.0.0.0.ru-2020-04.rur-2020-04.r1
    ap-southeast-1:
      ver: 18.0.0.0.ru-2020-04.rur-2020-04.r1
    ap-southeast-2:
      ver: 18.0.0.0.ru-2020-04.rur-2020-04.r1
    eu-central-1:
      ver: 18.0.0.0.ru-2020-04.rur-2020-04.r1
    eu-west-1:
      ver: 18.0.0.0.ru-2020-04.rur-2020-04.r1
    sa-east-1:
      ver: 18.0.0.0.ru-2020-04.rur-2020-04.r1
    us-east-1:
      ver: 18.0.0.0.ru-2020-04.rur-2020-04.r1
    us-gov-west-1:
      ver: 18.0.0.0.ru-2020-04.rur-2020-04.r1
    us-west-1:
      ver: 18.0.0.0.ru-2020-04.rur-2020-04.r1
    us-west-2:
      ver: 18.0.0.0.ru-2020-04.rur-2020-04.r1
  OracleRDSInstanceType:
    ap-northeast-1:
      inst: db.t3.medium
    ap-northeast-2:
      inst: db.t3.medium
    ap-south-1:
      inst: db.t3.medium
    ap-southeast-1:
      inst: db.t3.medium
    ap-southeast-2:
      inst: db.t3.medium
    eu-central-1:
      inst: db.t3.medium
    eu-west-1:
      inst: db.t3.medium
    sa-east-1:
      inst: db.t3.medium
    us-east-1:
      inst: db.t3.medium
    us-gov-west-1:
      inst: db.t3.medium
    us-west-1:
      inst: db.t3.medium
    us-west-2:
      inst: db.t3.medium
Metadata:
  'AWS::CloudFormation::Interface':
    ParameterGroups:
      - Label:
          default: Oracle Database Configuration
        Parameters:
          - OracleDBName
          - OracleDBUsername
          - OracleDBPassword
      - Label:
          default: MySQL Database Configuration
        Parameters:
          - DBUsername
          - DBPassword
      - Label:
          default: Enter IP address for DB Security group Configuration
        Parameters:
          - ClientIP
Outputs:
  OracleJDBCConnectionString:
    Description: JDBC connection string for Oracle database
    Value: !Join
      - ''
      - - 'jdbc:oracle:thin:@'
        - !GetAtt
          - OracleDB
          - Endpoint.Address
        - ':'
        - !GetAtt
          - OracleDB
          - Endpoint.Port
        - ':'
        - !Ref OracleDBName
  MySQLJDBCConnectionString:
    Description: JDBC connection string for MySQL cluster
    Value: !Join
      - ''
      - - 'jdbc:mysql://'
        - !GetAtt
          - MySQLDB
          - Endpoint.Address
        - ':'
        - !GetAtt
          - MySQLDB
          - Endpoint.Port
  Regionname:
    Value: !Ref 'AWS::Region'
  StackName:
    Value: !Ref 'AWS::StackName'
Parameters:
  ClientIP:
    AllowedPattern: '(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})/(\d{1,2})'
    ConstraintDescription: >-
      It must be a valid IP CIDR range of the form x.x.x.x/x. Suggest to enable
      access to your IP address only. Pls get your address using
      checkip.amazonaws.com or whatsmyip.org.
    Description: >-
      The IP address range that can be used to connect to the RDS instances from
      your local machine.It must be a valid IP CIDR range of the form
      x.x.x.x/x.Pls get your address using checkip.amazonaws.com or
      whatsmyip.org
    MaxLength: '18'
    MinLength: '9'
    Type: String
  OracleDBInstanceID:
    Default: oraclebyod
    Description: My database instance
    Type: String
    MinLength: '1'
    MaxLength: '63'
    AllowedPattern: '[a-zA-Z][a-zA-Z0-9]*'
  OracleDBName:
    AllowedPattern: '[a-zA-Z][a-zA-Z0-9]*'
    ConstraintDescription: >-
      must begin with a letter and contain a minimum of 4 alphanumeric
      characters.
    Default: ORCL
    Description: Enter Oracle Database name
    MaxLength: '64'
    MinLength: '4'
    Type: String
  OracleDBPassword:
    AllowedPattern: '[a-zA-Z0-9]*'
    ConstraintDescription: must contain only alphanumeric characters with minimum of 8 characters.
    Default: oraadmin123
    Description: Enter password for Oracle Admin user
    MaxLength: '41'
    MinLength: '8'
    NoEcho: 'true'
    Type: String
  OracleDBUsername:
    AllowedPattern: '[a-zA-Z][a-zA-Z0-9]*'
    ConstraintDescription: >-
      must begin with a letter and contain a minimum of 4 alphanumeric
      characters.
    Default: oraadmin
    Description: Enter database Admin username for Oracle
    MaxLength: '16'
    MinLength: '4'
    NoEcho: 'false'
    Type: String
  MySQLDBInstanceID:
    Default: mysqlbyod
    Description: My database instance
    Type: String
    MinLength: '1'
    MaxLength: '63'
    AllowedPattern: '[a-zA-Z][a-zA-Z0-9]*'
    ConstraintDescription: >-
      Must begin with a letter and must not end with a hyphen or contain two
      consecutive hyphens.
  MySQLDBName:
    Default: byod
    Description: My database
    Type: String
    MinLength: '1'
    MaxLength: '64'
    AllowedPattern: '[a-zA-Z][a-zA-Z0-9]*'
    ConstraintDescription: Must begin with a letter and contain only alphanumeric characters.
  DBInstanceClass:
    Default: db.t3.medium
    Description: DB instance class
    Type: String
    ConstraintDescription: Must select a valid DB instance type.
  DBAllocatedStorage:
    Default: '50'
    Description: The size of the database (GiB)
    Type: Number
    MinValue: '5'
    MaxValue: '1024'
    ConstraintDescription: must be between 20 and 65536 GiB.
  DBUsername:
    AllowedPattern: '[a-zA-Z][a-zA-Z0-9]*'
    ConstraintDescription: >-
      must begin with a letter and contain a minimum of 4 alphanumeric
      characters.
    Default: MySQLadmin
    Description: Enter database Admin username for MySQL
    MaxLength: '16'
    MinLength: '4'
    NoEcho: 'false'
    Type: String
  DBPassword:
    AllowedPattern: '[a-zA-Z0-9]*'
    ConstraintDescription: must contain only alphanumeric characters with minimum of 8 characters.
    Default: MYSQLadmin123
    Description: Enter password for MYSQL Admin user
    MaxLength: '41'
    MinLength: '8'
    NoEcho: 'true'
    Type: String
Conditions:
  HasKeyName: !Not
    - !Equals
      - ''
      - !Ref ClientIP
Resources:
  AttachGateway:
    Properties:
      InternetGatewayId: !Ref InternetGateway
      VpcId: !Ref VPC
    Type: 'AWS::EC2::VPCGatewayAttachment'
  DBSubnet1:
    Properties:
      AvailabilityZone: !Select
        - '0'
        - !GetAZs ''
      CidrBlock: 10.0.0.0/26
      Tags:
        - Key: Application
          Value: !Ref 'AWS::StackId'
      VpcId: !Ref VPC
    Type: 'AWS::EC2::Subnet'
  DBSubnet2:
    Properties:
      AvailabilityZone: !Select
        - '1'
        - !GetAZs ''
      CidrBlock: 10.0.0.64/26
      Tags:
        - Key: Application
          Value: !Ref 'AWS::StackId'
      VpcId: !Ref VPC
    Type: 'AWS::EC2::Subnet'
  InternetGateway:
    Properties:
      Tags:
        - Key: Application
          Value: !Ref 'AWS::StackId'
    Type: 'AWS::EC2::InternetGateway'
  MyDBSubnetGroup:
    Properties:
      DBSubnetGroupDescription: Subnet available for the Oracle Instance
      SubnetIds:
        - !Ref DBSubnet1
        - !Ref DBSubnet2
    Type: 'AWS::RDS::DBSubnetGroup'
  OracleDB:
    Properties:
      AllocatedStorage: '10'
      AvailabilityZone: !GetAtt
        - DBSubnet1
        - AvailabilityZone
      DBInstanceClass: !FindInMap
        - OracleRDSInstanceType
        - !Ref 'AWS::Region'
        - inst
      DBInstanceIdentifier: !Ref OracleDBInstanceID
      DBName: !Ref OracleDBName
      DBSubnetGroupName: !Ref MyDBSubnetGroup
      Engine: oracle-ee
      EngineVersion: !FindInMap
        - OracleEngineVersion
        - !Ref 'AWS::Region'
        - ver
      LicenseModel: bring-your-own-license
      MasterUserPassword: !Ref OracleDBPassword
      MasterUsername: !Ref OracleDBUsername
      MultiAZ: 'false'
      PubliclyAccessible: 'false'
      StorageType: gp2
      Tags:
        - Key: Application
          Value: !Ref 'AWS::StackId'
      VPCSecurityGroups:
        - !Ref myVPCSecurityGroup
    Type: 'AWS::RDS::DBInstance'
  MySQLDB:
    Properties:
      DBInstanceIdentifier: !Ref MySQLDBInstanceID
      DBName: !Ref MySQLDBName
      DBInstanceClass: !Ref DBInstanceClass
      AllocatedStorage: !Ref DBAllocatedStorage
      AvailabilityZone: !GetAtt
        - DBSubnet1
        - AvailabilityZone
      DBSubnetGroupName: !Ref MyDBSubnetGroup
      Engine: MySQL
      EngineVersion: 8.0.16
      MasterUsername: !Ref DBUsername
      MasterUserPassword: !Ref DBPassword
      MultiAZ: 'false'
      PubliclyAccessible: 'false'
      Tags:
        - Key: Application
          Value: !Ref 'AWS::StackId'
      VPCSecurityGroups:
        - !Ref myVPCSecurityGroup
    Type: 'AWS::RDS::DBInstance'
  Route:
    DependsOn: AttachGateway
    Properties:
      DestinationCidrBlock: 0.0.0.0/0
      GatewayId: !Ref InternetGateway
      RouteTableId: !Ref RouteTable
    Type: 'AWS::EC2::Route'
  RouteTable:
    Properties:
      Tags:
        - Key: Application
          Value: !Ref 'AWS::StackId'
      VpcId: !Ref VPC
    Type: 'AWS::EC2::RouteTable'
  SubnetRouteTableAssociation:
    Properties:
      RouteTableId: !Ref RouteTable
      SubnetId: !Ref DBSubnet1
    Type: 'AWS::EC2::SubnetRouteTableAssociation'
  SubnetRouteTableAssociation1:
    Properties:
      RouteTableId: !Ref RouteTable
      SubnetId: !Ref DBSubnet1
    Type: 'AWS::EC2::SubnetRouteTableAssociation'
  VPC:
    Properties:
      CidrBlock: 10.0.0.0/24
      EnableDnsHostnames: 'true'
      EnableDnsSupport: 'true'
      Tags:
        - Key: Application
          Value: !Ref 'AWS::StackId'
        - Key: Name
          Value: !Ref 'AWS::StackName'
    Type: 'AWS::EC2::VPC'
  myVPCSecurityGroup:
    Properties:
      GroupDescription: Security group for migration instances.
      SecurityGroupIngress:
        - CidrIp: !Ref ClientIP
          FromPort: '3306'
          IpProtocol: tcp
          ToPort: '3306'
        - CidrIp: !Ref ClientIP
          FromPort: '1521'
          IpProtocol: tcp
          ToPort: '1521'
      VpcId: !Ref VPC
    Type: 'AWS::EC2::SecurityGroup'
