Oracle Application Cloud Container: Creating a PHP REST Application Using Laravel and MySQL


Options



Before You Begin

Purpose

This tutorial shows you how to create a REST service in PHP using the Laravel framework and Oracle MySQL Cloud Service. You also learn how to deploy and test your application in Oracle Application Container Cloud Service.

Time to Complete

60 minutes

Background

Laravel is a free, open-source PHP web framework that saves you time and effort and makes web development easier. Laravel depends on a number of external packages for its functionality. To do this, it uses Composer as a dependency manager. So, you need to install Composer to make it easier create a new project and download all the dependencies.

Artisan

Laravel includes a command-line interface called Artisan. It provides a number of helpful commands that can assist you while you are building your application. To view a list of all available Artisan commands, you may use the list command:

php artisan list

Composer

Composer is a tool for dependency management in PHP. It allows you to declare the libraries your project depends on and it will manage (install/update) them for you. Composer is used in most of the modern PHP frameworks like Symfony and Laravel.

What Do You Need?

Creating an Oracle MySQL Cloud Service Instance

In this section, you create an Oracle MySQL Cloud Service instance using the user interface. When you create your Oracle MySQL Cloud Service instance, you need to provide a secure shell (SSH) public key to establish secure connections. If you don't have one, see the section "What do you need?" of this tutorial.

You need the public key when creating a service instance, and you need the paired private key when you access it using an ssh utility.

  1. Log in to your Oracle Cloud account. Enter your Identity Domain, User Name and Password.

  2. Click Menuand select Open the Service Console.

    MySQL action menu
    Description of this image
  3. Click Create Service.

  4. Enter or select the following information, and then click Next:

    • Service Name: TestDB
    • Service Description (optional)
    • Metering Frequency: Hourly
  5. Enter or select the following information and click Next:

    • Compute Shape: OC3-1.0 OCPU, 7.5GB RAM
    • SSH Public Key (Enter the public key that you generated in the previous section.)
    • Usable Database Storage(GB): 25
    • Administration User: root
    • Administration Password
    • Confirm Administration Password
    • Database Schema Name: mydatabase
    • Server Character Set: utf8mb4 - UTF-8 Unicode
    • Configure MySQL Enterprise Monitor: No
    • Backup Destination: None
  6. Review the details of your instance and click Create.

    When you return to the Oracle MySQL Cloud Service console, you will see your new database instance listed. It will have a status of Creating service... until the service creation is completed. Service instance creation will take a number of minutes.

    Oracle MySQL Cloud Service console - Services
    Description of this image
  7. Click the name of the database.

    Oracle MySQL Cloud Service console - Services
    Description of this image
  8. Write down the Public IP value.

    Project structure
    Description of this image

Connecting to MySQL Database

Oracle MySQL Cloud Service is built on top of infrastructure and functionality that are provided by Oracle Compute Cloud Service. When you create an instance of one of these services, all the Oracle Compute virtual machines required to support the service are provisioned and configured for you.

You can access the services and resources provided by the virtual machines by logging in to the machine through a Secure Shell (SSH). If the port for a resource provided by a virtual machine isn't directly accessible through the internet, you can access that resource by creating an SSH tunnel to a port in the virtual machines hosting the service instance.

This section describes two ways to create a tunnel: one using a Windows GUI client (PuTTY), and one then using the ssh utility at the command line for Linux systems.

To complete this section you need:

  • The public IP addess of your instance (for example, 192.0.2.1)
  • The port of your instance (for example, 3306)
  • The private key (.pkk file). The database administrator should provides the SSH private key used for creating the database instance.

Creating an SSH Tunnel Using PuTTY

  1. Open the PuTTY program.

    Putty configuration - Session panel
    Description of the image
  2. In the Host Name (or IP address) box, enter the public IP address (for example, 192.0.2.1) of the database instance. Leave the port number at the default 22.

    Host URL
    Description of the image
  3. Confirm that the Connection type option is set to SSH.
    Connection type SSH
    Description of the image
  4. In the Category tree, expand Connection if necessary and then click Data.
  5. In Auto-login username field, enter opc.

    PuTTY Configuration - Data panel.
    Description of the image
  6. Expand SSH, and then click Auth.
  7. Under Private key file for authentication, click Browse
  8. In the Select private key file window, click PuTTY Private Key Files (.ppk) to find and open the private key file that matches the public key used when the instance was created.
    PuTTY Configuration - Authentication panel.
    Description of the image
  9. In the Category tree, click Tunnels.
  10. In the Destination section:
    • Enter a port number in the Source port field (3306 in this example); this is the port on the local machine you'll connect to. It can be any port number you have available on your local machine.

      Note: if you have installed MySQL on your machine, then you probably have the default port 3306 in use, don't use this port.

    • In the Destination field enter public_ip_address:port (for example, 192.0.2.1:3306).
    PuTTY Configuration - Tunnels panel.
    Description of the image
  11. In the Category tree, click Session to display the Session panel again.
  12. In the Saved Sessions field, enter a name for this connection configuration. For the tutorial, type MySQLDB, and then click Save. When you open PuTTY the next time, you can load this configuration by selecting it and clicking Load.
    PuTTY Configuration - Session pannel.
    Description of the image
  13. Click Open to open the connection to the virtual machine (VM). If this is the first time you're connecting to the VM, then the PuTTY Security Alert window is displayed, prompting you to confirm the public key.

    Click Yes to continue connecting.

    Authenticate key prompt
    Description of the image
  14. When prompted, enter the passphrase for the key, if one was defined.

    Using username "opc".
    Authenticating with public key "rsa-key-20160107"
    Passphrase for key "rsa-key-20160107":
    ********************************************************************************
    *                                 Welcome to                                   *
    *                             MySQL Cloud Service                              *
    *                                     by                                       *
    *                                   Oracle                                     *
    *       If you are an unauthorised user please disconnect IMMEDIATELY          *
    ********************************************************************************
    ******************************* MySQL Information ******************************
    * Status:  RUNNING                                                             *
    * Version:   5.7.13                                                            *
    ********************************************************************************
    ************************** Storage Volume Information **************************
    * Volume      Used             Use%           Available   Size     Mounted on  *
    * MySQLlog    6.1G -------- 33%                     13G    20G   /u01/translog *
    * bin         2.6G ------- 28%                     6.7G   9.8G   /u01/bin      *
    * data        122M -- 1%                            24G    25G   /u01/data     *
    ********************************************************************************
    [opc@testdb2-mysql-1 ~]$ Using username "opc".
    ********************************************************************************
    *                                 Welcome to                                   *
    *                             MySQL Cloud Service                              *
    *                                     by                                       *
    *                                   Oracle                                     *
    *       If you are an unauthorised user please disconnect IMMEDIATELY          *
    ********************************************************************************
    ******************************* MySQL Information ******************************
    * Status:  RUNNING                                                             *
    * Version:   5.7.13                                                            *
    ********************************************************************************
    ************************** Storage Volume Information **************************
    * Volume      Used             Use%           Available   Size     Mounted on  *
    * MySQLlog    6.1G -------- 33%                     13G    20G   /u01/translog *
    * bin         2.6G ------- 28%                     6.7G   9.8G   /u01/bin      *
    * data        122M -- 1%                            24G    25G   /u01/data     *
    ********************************************************************************
    [opc@testdb2-mysql-1 ~]$
    

    The tunnel is now created. Any packets sent to the client's port 3306 will reach the VM's port 3306.

Creating an SSH Tunnel From a Command-Line Shell

  1. In a command line shell, set the file permissions of the private key file so that only you have access to it:

    $ chmod 600 private-key-file

    where private-key-file is the path to the SSH private key file that matches the public key used when your Oracle Database Cloud Service instance was created.

  2. Run the ssh utility:

    $ ssh -i private-key-file -L local-port:vm-ip-address:vm-port opc@vm-ip-address -N

    where:
    • private-key-file is the path to the SSH private key file that matches the public key used when your instance was created.
    • -L specifies that the local (client) port is to be forwarded to the remote host and port. This allocates a socket to listen to the local port. Whenever a connection is made to the local port, it is forwarded over the secure channel, and a connection is made to the host port from the remote machine.
    • Note: When you use an SSH client at the command-line, you can't use a private key saved in PuTTY (.ppk) format. If the only private key that you have is in PuTTY (.ppk) format, see the PuTTY documentation for instructions on how to convert it to OpenSSH format.
    • local-port is the port number on your client.
    • vm-ip-address is the IP address of the VM in x.x.x.x format. For an Oracle Database Cloud Service instance, you can use the public IP address of the instance.
    • vm-port is the port number on the VM to which you want to create a tunnel.
    • opc@vm-ip-address is the user account (opc) and the VM's IP address.
    • -N ensures that only the SSH tunnel is enabled. You won't be able to use the SSH session to run commands in the command shell.
    For example: $ ssh -i keys/id_rsa -L 1523:192.0.2.1:1521 opc@192.0.2.1
  3. If this is the first time you're connecting to the VM, then the ssh utility prompts you to confirm the public key. In response to the prompt, enter yes.
  4. At the prompt, enter the passphrase for the SSH key, if one was created.

    Now you are logged in, and you can use any resources of that server as if it were local.

Creating the MySQL Database Connection in NetBeans

  1. Open NetBeans.

  2. Click the Services tab.

    NetBeans window - Services
    Description of the image
  3. Right click Databases and select New Connection...

    Services - New connection
    Description of the image
  4. Select MySQL (connector/ J driver) and click Next.

    New connection wizard - Driver selection
    Description of the image
  5. In the New Connection Wizard enter the following information:

    • Host: localhost
    • Port: 3306
    • Database: mydatabase
    • User Name: root
    • Password: <enter-your password>
    • JDBC URL: jdbc:mysql://localhost:3306/mysql?zeroDateTimeBehavior=convertToNull
    New connection wizard - Customize connection
    Description of the image
  6. Click Test Connection and if the connection is succeeded click Finish.

    New connection wizard - Test connection
    Description of the image

Creating the Database Objects

  1. Right click to the database connection and select Execute Command...

    Action menu of the conection - Execute command
    Description of the image
  2. Copy and paste the following script and click Run SQL.

    CREATE TABLE employees (
          id INTEGER NOT NULL AUTO_INCREMENT,
          firstName VARCHAR(255),
          lastName VARCHAR(255),
          email VARCHAR(255),
          phone VARCHAR(255),
          birthDate VARCHAR(10),
          title VARCHAR(255),
          department VARCHAR(255),
          PRIMARY KEY (id)
    	  ); 
    SQL window - Create employees table
    Description of the image
  3. Copy and paste the following script and click Run SQL.

    INSERT INTO employees (FIRSTNAME, LASTNAME, EMAIL, PHONE, BIRTHDATE, TITLE, DEPARTMENT) VALUES ('Hugh', 'Jast', 'Hugh.Jast@example.com', '730-715-4446', '1970-11-28' , 'National Data Strategist', 'Mobility'); 
    INSERT INTO employees (FIRSTNAME, LASTNAME, EMAIL, PHONE, BIRTHDATE, TITLE, DEPARTMENT) VALUES ('Toy', 'Herzog', 'Toy.Herzog@example.com', '769-569-1789','1961-08-08', 'Dynamic Operations Manager', 'Paradigm'); 
    INSERT INTO employees (FIRSTNAME, LASTNAME, EMAIL, PHONE, BIRTHDATE, TITLE, DEPARTMENT) VALUES ('Reed', 'Hahn', 'Reed.Hahn@example.com', '429-071-2018', '1977-02-05', 'Future Directives Facilitator', 'Quality'); 
    INSERT INTO employees (FIRSTNAME, LASTNAME, EMAIL, PHONE, BIRTHDATE, TITLE, DEPARTMENT) VALUES ('Novella', 'Bahringer', 'Novella.Bahringer@example.com', '293-596-3547', '1961-07-25' , 'Principal Factors Architect', 'Division'); 
    INSERT INTO employees (FIRSTNAME, LASTNAME, EMAIL, PHONE, BIRTHDATE, TITLE, DEPARTMENT) VALUES ('Zora', 'Sawayn', 'Zora.Sawayn@example.com', '923-814-0502', '1978-03-18' , 'Dynamic Marketing Designer', 'Security'); 
    SQL window - Insert employees records
    Description of the image

Creating the REST Service

Creating the Laravel Project

  1. Open a command-line window (or terminal in Linux).

  2. Go to the directory where the new project should reside.

  3. Create a Laravel project.

    laravel new employee-service 

Creating the Model Class

  1. On the command-line, go to the employee-service directory and execute the following command to create the Employee class.

    This command generates a model class at app/Employee.php.

    php artisan make:model Employee

    The plural name of the class will be used as the table name unless another name is explicitly specified. In this case, Eloquent will assume the Employee model stores records in the employees table. You can specify a custom table by defining a table property on your model: $table = 'employees_corp';

  2. Open the Employee class and set the $timestamps property to false.

    <?php
    
    namespace App;
    
    use Illuminate\Database\Eloquent\Model;
    
    class Employee extends Model
    {
    	public $timestamps = false;
    }

    By default, Eloquent expects created_at and updated_at columns exist on your tables. If you don't have these columns in your tables, you must set the $timestamps property on your model to false.

Creating the Controller

  1. In the command-line, execute the following command to create the EmployeeController class.

    This command generates a controller at app/Http/Controllers/EmployeeController.php. The controller will contain a method for each of the available resource operations.

    php artisan make:controller EmployeeController --resource
  2. Open the EmployeeController.php file in a text editor and add the Employee model class.

    use App\Employee;
  3. Implement the index method to get all the employees.

    public function index(){
        Log::info('::GET ALL EMPLOYEES::');	
    	$employees = Employee::all();	
    	return json_encode($employees);
    }                          
  4. Implement the store method to save a new employee in the database.

    public function store(Request $request){
        Log::info('::SAVE::');
    	$employee = new Employee;
        $employee->firstName = $request->firstName;
    	$employee->lastName = $request->lastName;
    	$employee->email = $request->email;
    	$employee->phone = $request->phone;
    	$employee->birthDate = $request->birthDate;
    	$employee->title = $request->title;
    	$employee->department = $request->dept;
            if( $employee->save() ){
                return response('{"status":"Employee created successfully!"}',201);
            }
    }
  5. Implement the update method.

    The save method may also be used to update models that already exist in the database. To update a model, you should retrieve it, set any attributes you want to update, and then call the save method.

    public function update(Request $request, $id){
        Log::info('::UPDATE::');
    	$employee = Employee::find($id);		
        $employee->firstName = $request->firstName;
    	$employee->lastName = $request->lastName;
    	$employee->email = $request->email;
    	$employee->phone = $request->phone;
    	$employee->birthDate = $request->birthDate;
    	$employee->title = $request->title;
    	$employee->department = $request->dept;	
    	if( $employee->save() ){
                return response('{"status":"Employee updated successfully!"}',200);
            }
    } 
  6. Implement the destroy method to delete an employee.

    public function destroy($id){
        Log::info('::DELETE::');
    	if (Employee::destroy($id)){
    		return response('{"status":"Employee deleted successfully!"}',200);
    	}
    }
  7. Add thesearchmethod.

    public function search($criteria, $value){
            Log::info('::SEARCH::');                          
    		if ($criteria=='department'){
    			$filter = 'DEPARTMENT'; 
    		}else if ($criteria=='lastname'){
    			$filter = 'LASTNAME';
    		}else if ($criteria=='title'){
    			$filter = 'TITLE';
    		}else{
    			throw new Exception('Filter criteria not valid');
    		}         
    		$employee = Employee::where($filter, '=', $value)->get();
    		return json_encode($employee);
    	}
  8. Remove the methods: show, create, and edit.

Registering the Routes to the Controller

  1. Open the \routes\web.php file in a text editor and add the following sentence:

    Route::resource('employees', 'EmployeeController', ['only'=> ['index','create','store','destroy','update']]);

    This single route declaration creates multiple routes to handle a variety of actions on the resource.

    Actions Handled By Resource Controller

    HTTP Method URI Controller method
    GET /employees index()
    POST /employees store(Request $request)
    PUT/PATCH /employees/{employeeID} update(Request $request, $id)
    DELETE /employees/{employeeID} destroy($id)
  2. Add the route for the search method.

    Route::get('/employees/{filter}/{value}','EmployeeController@search');

Adding the Client Service

  1. Extract the content of the employee-client.zip file in your local system.

  2. Copy the img directory into the public directory.

  3. Copy the employees.blade.php file into the \resources\views directory.

  4. Copy the freewall.js file (that you downloaded in the "What do you need?" section) in the js directory.

  5. Open the routes/web.phpfile, add the following code, and save the file.

    Route::get('/employees/client', function () {
        return view('employees');
    });

Preparing the Application for Cloud Deployment

When you upload your application to Oracle Application Container Cloud Service using the user interface, you must include a file called manifest.json in the application archive (.zip, .tgz, .tar.gz file). If you use the REST API to upload the application, this file is still required but doesn’t have to be in the archive.

The manifest.json file specifies the PHP version to use, the command, and optionally, you can include notes and application-specific release information.

  1. Create the start.sh script file and add the following content:

    echo "DB_CONNECTION=mysql" >> ./.env
    echo "DB_HOST=${MYSQLCS_CONNECT_STRING%:*}" >> ./.env
    echo "DB_PORT=${MYSQLCS_MYSQL_PORT}" >> ./.env
    echo "DB_DATABASE=${MYSQLCS_CONNECT_STRING##*/}" >> ./.env
    echo "DB_USERNAME=${MYSQLCS_USER_NAME}" >> ./.env
    echo "DB_PASSWORD=${MYSQLCS_USER_PASSWORD}" >> ./.env
    tail -f storage/logs/laravel.log &
    apache2-run public
    

    The start.sh script updates the .env file to define the Oracle MySQL Cloud Service credentials using the environment variables created when you add the MySQLCS service binding. The script also includes the tail command to show the logs and the Apache command(apache2-run public) to execute the application in Oracle Application Container Cloud Service.

    Note: By default, the Laravel framework logs errors and messages into the storage/logs/Laravel.log file. In order to ensure that errors, should they arise, are visible in Oracle Application Container Cloud Service you need to define the tail command in the script file.

  2. Create the manifest.json and add the following content:

    {
        "runtime": {
            "majorVersion": "7.0"
        },
        "command": "sh ./start.sh",
        "notes": "Employees REST PHP Application using MySQL"
    }
    
  3. Compress all the project files including the manifest.json file into a .zip file.

Deploying the Application to Oracle Application Container Cloud Service

  1. Log in to Oracle Cloud at http://cloud.oracle.com/. Enter your account credentials in the Identity Domain, User Name, and Password fields.

  2. In the Oracle Cloud Services dashboard, click the Action menu Menu, and select Application Container.

  3. In the Applications list view, click Create Application and select PHP.

  4. On the File Upload dialog box, select the .zip file you created in the previous section and click Open.

  5. Keep the default values in the Topology section and click Create.

    Create Application dialog box
    Description of this image

Adding the Database Service Binding

  1. In the Applications list view, click your application.

    Oracle Application Container Cloud Service home
    Description of this image
  2. Click the Deployments tab and in the Service Binding section, click Add.

    Deployments page
    Description of this image
  3. In the Add Service Binding dialog box, select MySQLCS Service in the Service Type field, enter or select values in the Service Name, Username, and Password fields, and click Save.

    Add Service Binding dialog box
    Description of this image
  4. In the Deployments dialog box, click Apply Edits.

    Deployments dialog box
    Description of this image

Testing the Application

  1. In the Application tab of the Oracle Application Container Cloud Service console click the URL of your application.

    Firefox window - Laravel welcome page
    Description of this image
  2. Call the GET method of the employees service by adding /employees at the end of the URL.

    Firefox window - Get request method of /employees resource
    Description of this image
  3. Add to the end of the URL: /client

    Firefox window - Employee client page
    Description of this image
  4. Test the Add New, Update, and Delete options.

Getting the logs

  1. Open the Oracle Application Container Cloud Service console.

  2. Select your application.

    Oracle Application Container Cloud Service console
    Description of this image
  3. Click Administration. and then click Logs

    Administration page
    Description of this image
  4. Click Get Log.

    Logs page
    Description of this image
  5. Wait a minute, and click the Refresh Refresh icon.

    Logs page - Get Log
    Description of this image
  6. Click the Expand Logs Expand Logs icon, then click server.out.zip

    Logs page - List of the logs files
    Description of this image
  7. Enter your cloud storage credentials, and click OK.

    Cloud storage credentials window
    Description of this image
  8. Select a location for the zip file and click Save.

    Save file window
    Description of this image
  9. Unzip the server.out.zip file and open the server.out file in a text editor.

Want to Learn More?