Oracle Application Container Cloud Service: Building a RESTful API with PHP


Options



Before You Begin

Purpose

This tutorial shows you how to create a PHP REST API using an Oracle Database Cloud Service instance and deploy the application to Oracle Application Container Cloud Service. You also learn how to log errors and messages using STDOUT and how to get the logs from Oracle Application Container Cloud Service.

Time to Complete

40 minutes

Background

Representational state transfer (REST) is an architectural design for designing communication and operational channels among networked applications. The REST design is commonly implemented while building web HTTP APIs. As REST APIs interact through HTTP requests, they provide heterogeneous interoperability. RESTful APIs are best used when a single web page has to show data from multiple partners.

PHP is a popular general-purpose scripting language that is especially suited to web development. PHP can be used to develop the RESTful APIs or the clients to consume the APIs.

Scenario

In this tutorial, you build a basic RESTful API that implements the CRUD (Create, Read, Update, and Delete) operations on a employee table in a Oracle Database Cloud Service instance, using plain PHP without dependency on any framework.

The php RESTful application responds to the following endpoints:

Path Description
GET: /employees Gets all the employees.
GET: /employees/{searchType}/{searchValue} Gets the employees that match the search criteria.
POST: /employees Adds an employee.
PUT: /employees/{id} Updates an employee.
DELETE: /employees/{id} Removes an employee.

This tutorial covers two ways to test your RESTful API, one using the Postman tool and other using an HTML Client. The HTML client is provided in the next section. The zip file contains a index.html file and seven .png images. This client is developed using JQuery, Ajax and CSS.

What Do You Need?

Setting Up the Database and the Objects

In this section, you create a connection to your Oracle Database Cloud Service instance from the Oracle SQL Developer and execute the scripts to create the employee table and the employee sequence.

Connecting Oracle SQL Developer to Your Database

  1. Open Oracle SQL Developer.

  2. Click the View menu and select SSH.

  3. Right-click SSH Hosts and click New SSH Host...

    SSH Host window - New SSH Host
    Description of this image

  4. Enter the following information and click OK.

    • Name:
    • Host: Enter the public ip address of your Oracle Database Cloud Service instance.
    • Username: opc
    • Select Use key file.
    • Click Browse and select the private key file.
    • Select Add a local port forward.
    • Name:
    • Host: localhost
    • Port:1521
    • Select Use specific local port 1523
    New SSH host window
    Description of this image

  5. Right-click to the new host connection and click Connect.

    Action menu - Connect
    Description of this image

  6. Enter the passphrase.

    SSH authorization required dialog box
    Description of this image

  7. Right-click Connections and select New Connection.

    Connections context menu
    Description of this image

  8. In the New / Select Database Connection dialog box, enter values in the following fields, and then click Test:

    • Connection Name
    • Username
    • Password
    • Connection Type: SSH
    • Role: default
    • Port forward:Local(DBCS)
    • Service name
    New /Select Database Connection dialog box
    Description of this image

  9. Click Save, and then click Connect.

Creating the Database Objects

  1. Right-click the connection and select Open SQL worksheet.

    Connection context menu
    Description of this image

  2. Copy the following script into the SQL worksheet to create the EMPLOYEE table and the sequence named EMPLOYEE_SEQ: 

    CREATE TABLE EMPLOYEE (
      ID INTEGER NOT NULL,
      FIRSTNAME VARCHAR(255),
      LASTNAME VARCHAR(255),
      EMAIL VARCHAR(255),
      PHONE VARCHAR(255),
      BIRTHDATE VARCHAR(10),
      TITLE VARCHAR(255),
      DEPARTMENT VARCHAR(255),
      PRIMARY KEY (ID)
	  ); 


CREATE SEQUENCE EMPLOYEE_SEQ
 START WITH     100
 INCREMENT BY   1;

  3. Click Run Statement Run Statement and then click Commit Commit.

Building the REST Service

Creating a Resource Representation Class

In this section, you create a class named Employee to represent the state of the employee objects with the properties: first name, last name, birth date, phone, e-mail, department, and title.

  1. Create a folder in your local system to contain all your project files.

  2. Create a PHP class named Employee.php.

  3. Add the following properties to define the employee objects.

    public $id;
public $firstName;
public $lastName;
public $birthDate;
public $phone;
public $email;
public $dept;
public $title;

  4. Add the set method and save the file.

    public function set($arrayParams) {
        if (!empty($arrayParams['id'])) {
            $this->id = $arrayParams['id'];
        }
        $this ->firstName = $arrayParams['firstName'];
        $this->lastName = $arrayParams['lastName'];
        $this->birthDate = $arrayParams['birthDate'];
        $this->phone = $arrayParams['phone'];
        $this->email = $arrayParams['email'];
        $this->dept = $arrayParams['dept'];
        $this->title = $arrayParams['title'];
}

    PHP doesn't have a way to automatically convert the JSON parameters to a custom object.  The set method will be useful to set the employee's properties with the JSON parameters from the request.

Creating the Persistence Layer

In this section, you create the EmployeeStorage class, that contains the methods to get, save, update, and delete the employees data from the database.

  1. Create a PHP class named EmployeeStorage.php.

  2. Include the Employee.php class after the <?php tag. 

    include("Employee.php");

  3. Create the private $conn variable and add the constructor.

    To create the connection to an Oracle Database Cloud Service instance from an application deployed in Oracle Application Container Cloud Service you can get the username, password, and connector descriptor from the environment variables (DBAAS_USER_NAME, DBAAS_USER_PASSWORD, and DBAAS_DEFAULT_CONNECT_DESCRIPTOR). These variables are created when you add the database service binding, see the Adding the Database Service Binding section. 

    private $conn;

    public function __construct() {
        $userName = getenv('DBAAS_USER_NAME') ? getenv('DBAAS_USER_NAME') : 'oracle';
        $password = getenv('DBAAS_USER_PASSWORD') ? getenv('DBAAS_USER_PASSWORD') : 'oracle';
        $default_descriptor = getenv('DBAAS_DEFAULT_CONNECT_DESCRIPTOR') ? 
                              getenv('DBAAS_DEFAULT_CONNECT_DESCRIPTOR') : 'localhost:1523/PDB1.smdev.oraclecloud.internal';
        $this->conn = oci_connect($userName, $password, $default_descriptor);
        if (!$this->conn) {
            $e = oci_error();
            trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
            echo "connection error. Failed.....";
        }
    }

  4. Add the methods getAll, search, delete, update,and save. 

    public function getAll() {
        $data = Array();
        $query = 'SELECT * FROM EMPLOYEE';

        $stid = oci_parse($this->conn, $query);
        oci_execute($stid);

        while ($row = oci_fetch_array($stid, OCI_ASSOC + OCI_RETURN_NULLS)) {
            $employee = new Employee();
            $employee->id = $row['ID'];
            $employee->firstName = $row['FIRSTNAME'];
            $employee->lastName = $row['LASTNAME'];
            $employee->phone = $row['PHONE'];
            $employee->birthDate = $row['BIRTHDATE'];
            $employee->title = $row['TITLE'];
            $employee->dept = $row['DEPARTMENT'];
            $employee->email = $row['EMAIL'];
            array_push($data, $employee);
        }
        return $data;
    }

    public function search($criteria, $value) {
        $data = Array();
	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');
	}			
	$query = 'SELECT * FROM EMPLOYEE WHERE ' . $filter . '=:value';
        $stmt = oci_parse($this->conn, $query);
        oci_bind_by_name($stmt, ':value', $value, -1);
        oci_execute($stmt);
        while ($row = oci_fetch_array($stmt, OCI_ASSOC + OCI_RETURN_NULLS)) {
            $employee = new Employee();
            $employee->id = $row['ID'];
            $employee->firstName = $row['FIRSTNAME'];
            $employee->lastName = $row['LASTNAME'];
            $employee->phone = $row['PHONE'];
            $employee->birthDate = $row['BIRTHDATE'];
            $employee->title = $row['TITLE'];
            $employee->dept = $row['DEPARTMENT'];
            $employee->email = $row['EMAIL'];
            array_push($data, $employee);
        }
        return $data;
    }

    public function delete($id) {
        $query = 'DELETE FROM EMPLOYEE WHERE ID = :id';
        $stmt = oci_parse($this->conn, $query);
        oci_bind_by_name($stmt, ':id', $id, -1);
        oci_execute($stmt);
    }

    public function update($employee) {
        $query = 'UPDATE EMPLOYEE SET FIRSTNAME=:firstName, LASTNAME=:lastName, PHONE=:phone, BIRTHDATE=:birthdate, TITLE=:title, DEPARTMENT=:department, EMAIL=:email '
                . 'WHERE ID=:id';
        $stmt = oci_parse($this->conn, $query);
        oci_bind_by_name($stmt, ':firstName', $employee->firstName);
        oci_bind_by_name($stmt, ':lastName', $employee->lastName);
        oci_bind_by_name($stmt, ':phone', $employee->phone);
        oci_bind_by_name($stmt, ':birthdate', $employee->birthDate);
        oci_bind_by_name($stmt, ':title', $employee->title);
        oci_bind_by_name($stmt, ':department', $employee->dept);
        oci_bind_by_name($stmt, ':email', $employee->email);
        oci_bind_by_name($stmt, ':id', $employee->id);
        oci_execute($stmt);
    }

    public function save($employee) {
        $query = 'INSERT INTO EMPLOYEE (ID, FIRSTNAME, LASTNAME, EMAIL, PHONE, BIRTHDATE, TITLE, DEPARTMENT) '
                . 'VALUES(employee_seq.nextval,:firstName,:lastName,:email,:phone,:birthdate,:title,:department)';
        $stmt = oci_parse($this->conn, $query);
        oci_bind_by_name($stmt, ':firstName', $employee->firstName);
        oci_bind_by_name($stmt, ':lastName', $employee->lastName);
        oci_bind_by_name($stmt, ':phone', $employee->phone);
        oci_bind_by_name($stmt, ':birthdate', $employee->birthDate);
        oci_bind_by_name($stmt, ':title', $employee->title);
        oci_bind_by_name($stmt, ':department', $employee->dept);
        oci_bind_by_name($stmt, ':email', $employee->email);
         oci_execute($stmt);
    }

Creating the RESTful Web Service Controller

In this section, you create the php routine to process the HTTP requests and return the JSON response.

  1. Create a PHP file named employees-rest-api.php

  2. Include the EmployeeStorage.php class after the <?php tag. 

    include("EmployeeStorage.php");

  3. Define STDOUT for use with fwrite.

  4. Add the $verb variable to store the request method using the $_SERVER array.

    $verb = $_SERVER['REQUEST_METHOD'];

  5. Create the $url_pieces array to hold the pathname information.

    $url_pieces = explode('/', $_SERVER['PATH_INFO']);

  6. Limit the support requests to /employees and throw an exception if is not.

    if ($url_pieces[1] != 'employees') {
    throw new Exception('Unknown endpoint', 404);
}

  7. Add the following code to process the different request methods (GET, POST, PUT, DELETE, and OPTIONS):

    $employeeStorage = new EmployeeStorage ();
$data;
switch ($verb) {
    case 'GET':
        $filter;
        $value;
        if (!empty($url_pieces[2])) {
            $filter = $url_pieces[2];
            $value = $url_pieces[3];
            $data = $employeeStorage->search($filter, $value);
        } else {
            $data = $employeeStorage->getAll();
        }
        break;
    case 'POST':
        $params = json_decode(file_get_contents("php://input"), true);
        if (!$params) {
            throw new Exception("Data missing or invalid");
        }
        $employee = new Employee();
        $employee->set($params);
        $employeeStorage->save($employee);
        $data = '{"status":"OK"}';
        http_response_code(201);
        break;
    case 'PUT':
        $params = json_decode(file_get_contents("php://input"), true);
        if (!$params) {
            throw new Exception("Data missing or invalid");
        }
        $id;
        if (!empty($url_pieces[2])) {
            $id = $url_pieces[2];
        }
        $employee = new Employee();
        $employee->id = $id;
        $employee->set($params);
        $employeeStorage->update($employee);
        $data = '{"status":"OK"}';
        http_response_code(200);
        break;
    case 'DELETE':
        $id;
        if (!empty($url_pieces[2])) {
            $id = $url_pieces[2];
            $employeeStorage->delete($id);
        }
        $data = '{"status":"OK"}';
        http_response_code(200);
        break;
    case 'OPTIONS':
        http_response_code(200);
        $data = '{"status":"OK"}';
        break;
    default:
        throw new Exception('Method Not Supported', 405);
}

    When we send a request thought AJAX using the methods: POST, PUT, and DELETE a verification request is made to the server. This request uses the OPTIONS method and sends the headers to determine whether or not it will allow requests of this type. If the response is OK then the real request (POST, PUT or DELETE) is sent. This is the reason why the OPTIONS method is implemented.

  8. Add the following headers to support cross-domain requests. 

    header("Content-Type: application/json");
header('Access-Control-Allow-Origin:*');
header('Access-Control-Allow-Methods:GET, POST, OPTIONS, PUT, PATCH, DELETE');
header('Access-Control-Allow-Headers:X-Requested-With,content-type');
header('Access-Control-Allow-Credentials:true');

  9. Print the $data array using the json_encode method. 

    echo json_encode($data);

  10. Close STDOUT file descriptor.

    fclose(STDOUT);

Adding the HTML Client

In this section, you add the client files to your project and you update the index.html file to connect the client to your RESTful application.

Extract the contents of the employee-client.zip file in the root directory of your project.

Note: Make sure the index.html is in the root directory of your project.

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 and optionally, you can include notes and application-specific release information.

  1. Create the timer.sh script file to simulate an application log.

    while true; do echo 'date'; sleep 5; done

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

    # Fork background process that writes to time.log to simulate an application
# log one might want to monitor
touch time.log
sh ./timer.sh > time.log &
# Tailing a file of interest will sent its contents to stdout which is captured
# by ACCS
tail -f time.log &

# Launch Apache Server to start serving up our PHP application--this is only
# necessary because we provided a start command in the manifest.json
apache2-run

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

    {
    "runtime": {
        "majorVersion": "7.1"
    },
    "command": "sh ./start.sh",
    "notes": "PHP REST Sample Application"
}

  4. Compress the project files (including the .php files, the index.html, and the images), the script files (start.sh, and timer.sh),  and the manifest.json file in a file named phpEmployeeAPI.zip.

    Project files
    Description of this image

Deploying the Application to Oracle Application Container Cloud Service

  1. In a web browser, go to https://cloud.oracle.com/home and click Sign In.

  2. From the Cloud Account menu, select Traditional Cloud Account, select your data center, and click My Services.

  3. Enter your identity domain and click Go.

  4. Enter your cloud account credentials and click Sign In.

  5. In the Oracle Cloud My Services dashboard, click Action Action menu icon, click Services, and select Application Container.

  6. In the Applications page, click Create Application, and select PHP.

  7. Enter a name for your application, select Upload Archive, and click Browse.

  8. In the File Upload dialog box, select the phpEmployeeAPI.zip file that you created in the previous section, and click Open.

  9. In the Create Application dialog box, keep the default values and click Create.

    Create Application dialog box
    Description of this image

Adding the Database Service Binding

  1. In the Applications page, click your application.

    Oracle Application Container Cloud Service Applications page
    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 Database Cloud Service in the Service Type field, enter or select values in the Service Name, Username, and Password fields. Click Save.

    Add Service Binding dialog box
    Description of this image

  4. In the Application page, click Apply Edits.

    Deployments - Changes Not Saved dialog box
    Description of this image

  5. In the Apply Changes dialog box, click Restart.

  6. Click Applications.

    7. On the Applications page, the application apaas-deploy-release-rolling status is displayed until the deployment is complete.

Testing the REST Service

Testing the RESTful API with the Postman Tool

  1. Open the Postman tool.

  2. Select the POST method and enter the URL of your RESTful API.

    Note: Replace <identity-domain> with the entity domain of your cloud account.

    URL:

    https://employees-api-<identity-domain>.apaas.us2.oraclecloud.com/employees-rest-api.php/employees

  3. Click the Body tab, select raw, then select the JSON(application/json) format, enter the following body request, and click Send.

    {"firstName":"Laura",
"lastName":"Jones",
"email":"laura.jones@example.com",
"phone":"1203123",
"birthDate":"1975-05-14",
"title":"Developer Manager",
"dept":"IT"}
    Postman POST method
    Description of this image

  4. Select the GET method and click Send.

    Postman GET method
    Description of this image

  5. Select the PUT method, add the ID of the new employee to the end of the URL:

    https://employees-api-<identity-domain>.apaas.us2.oraclecloud.com/employees-rest-api.php/employees/100

  6. Click Body and enter the following request body:

    {"firstName":"Laura",
"lastName":"Jones",
"email":"laura.jones@example.com",
"phone":"1203123",
"birthDate":"1975-05-14",
"title":"Manager",
"dept":"Sales"}
    Postman PUT method
    Description of this image

  7. Execute the GET request again.

    You can see the new value of title and dept.

    Postman GET method
    Description of this image

  8. Select the DELETE method and click Send.

    Postman DELETE method
    Description of this image

    You can execute the GET method again to verify the employee is deleted.

Testing the RESTful API with the HTML Client

  1. Click the URL to open your application.

    Deployments dialog box
    Description of this image

  2. Click Add New.

    Employees client - Search page
    Description of this image

  3. Fill the form and click Add.

    Employees client - Add new form
    Description of this image

  4. Select the card of the new employee.

    Employees client - Search page
    Description of this image

  5. Click Edit.

    Employees client - Details page
    Description of this image

  6. Change the value of the Department field and click Update.

    Employees client - Edit page
    Description of this image

    The employee card is updated.

    Employees client - Search page
    Description of this image

  7. Click the card of the employee and click Delete.

    Employees client - Details page
    Description of this image

  8. Click Delete and then click Ok to confirm.

    Delete confirmation
    Description of this image
    Deployments dialog box
    Description of this image

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 Get Log button.

  5. Select the Instance from which you want get the logs.

    Logs page - Select instance dialog box
    Description of this image

  6. Wait a minute, and click then Refresh Refresh.

  7. Click the log file you want to download.

    Logs page - Logs table
    Description of this image

  8. In the Save As dialog box, select a location for the zip file, and click Save.

  9. Extract the contents of the zip file and open the server.out file in a text editor.

Want to Learn More?