Technical note book of Chatura Dilan

PHP Data Services – Tutorial Part I (Creating Services)

PHP Data Services (PHPDS) is developed for ease developers to create RESTful JSON based web services very fast and efficiency without writing PHP codes. By using the PHPDS interface developers can generate web services right from the databases they use.

In this tutorial, we are going to see how you can create a small system which is fully APIs driven and how to use PHPDS for create data services.

First let’s look at what is PHPDS. This project is initiated and coded by me (Chatura Dilan) for final year project of my Master’s degree.

What is PHP Data Services and API Manager Framework (PHPDS)?

PHPDS is a toolset which PHP developers can used to generate web services without writing any PHP codes. It guaranteed the secure and managed data access across databases, data transformation, documentation and validation.

What does the product PHPDS do?

In essence PHPDS helps PHP developers to create REST web services out of data stores such as databases very quickly and efficiently. PHPDS will handle many layers automatically which PHP developers need to do manually such as generating services, applying security, connect to social networks and describe the APIs.

What business use cases does it resolve?

The norm of the business is that “Data is worthless if you don’t communicate it”. PHPDS can be used in any business system to expose their data to public or in a secure manner. It can be used to solve any IT related problems, which reduce the development time and cost.

Can anyone use PHPDS?

Yes. PHPDS is licensed under the MIT license. This means that everyone is free to modify, distribute and republish the source code on the condition that the copyright notices are left intact. Everyone is also free to incorporate PHPDS into any commercial or closed source application.

Pre-requisites and Use Cases

Let’s get back to the tutorial.  To do this tutorial you need below pre-requisites

The minimum requirements are a webserver and a copy of PHPDS
• HTTP Server. For example: Apache. mod_rewrite is preferred, but by no means required.
• PHP 5.2.8 or greater
• MySQL 5 or greater

Currently PHPDS has only one extension which only support MySQL. Other extensions can be write and plugin to the PHPDS o support DBMSs other than MySQL
MySQL extension will require having MySQL 5 or grater

By the way if you have no idea about REST data services, please read http://www.ibm.com/developerworks/webservices/library/ws-restful/ before continue. You can learn more about JSON form here. http://www.json.org

The story of our system as following:

ABC icompany needs a system which is called ‘Tourism System’ and is used to get comment from their users about each cities of the country. Province is consist of several cities and users can login to the system through Facebook and add comments to cities in provinces. Admin can manage (add, edit, delete) the entities of the system such as comments, cities, provinces. The main use cases of the system  depicts in the following use case diagram.

usecase

Summery of the use cases

a. Users can register to the system via Facebook.

b. Registered users login via Facebook can view only his comments and add comments. Comments are stored by the user ID.

c. Everyone can view list of provinces, cities and comments. They can also view all the cities in a single province.

d. Admin can add, edit, delete provinces, cities and comments.

Let’s look at the ERD diagram of our database. Our database consists of five main tables.

erd-ds

The SQL schema of our database as following. Let’s call it ‘tourism’ database

CREATE TABLE IF NOT EXISTS `authentications` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL COMMENT 'refer to users.id',
`provider` varchar(100) NOT NULL,
`provider_uid` varchar(255) NOT NULL,
`email` varchar(200) NOT NULL,
`display_name` varchar(150) NOT NULL,
`first_name` varchar(100) NOT NULL,
`last_name` varchar(100) NOT NULL,
`profile_url` varchar(300) NOT NULL,
`website_url` varchar(300) NOT NULL,
`created_at` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `provider_uid` (`provider_uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `cities` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`province_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `cities` (`id`, `name`, `province_id`) VALUES
(1, 'Colombo', 1),
(2, 'Gampaha', 1),
(3, 'Anuradhapura', 2),
(4, 'Polonnaruwa', 2),
(5, 'Kandy', 3);

CREATE TABLE IF NOT EXISTS `comments` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`comment` text NOT NULL,
`user_id` int(11) NOT NULL,
`city_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `provinces` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `provinces` (`id`, `name`) VALUES
(1, 'Western'),
(2, 'North Central'),
(3, 'Central'),
(4, 'Eastern');

CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(200) NOT NULL,
`password` varchar(200) NOT NULL,
`first_name` varchar(200) NOT NULL,
`last_name` varchar(200) NOT NULL,
`created_at` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Description of each tables
i. provinces ( has records of all provinces in Sri Lanka, A province can have multiple cities)
ii. cities (has records of cities. city is associate with a one province)
iii. users (users who are registered with the system, who can add comments.)
iv. authentications (autentication information about each users against social networking sites)
v. comments ( has records of comments added by user)

Getting started

1. Create a database called ‘tourism’ and import the SQL schema above to the database. (This database for our use case.)

2.  Download  PHP Data Service Framework binary form this link http://chaturadilan.github.io/PHP-Data-Services/

3. Create a folder called phpds (or any name you want) in your Apache Document Root folder and extract PHPDS to that folder

4. Create a database called ‘datas’ and select the /app/Config/Schema/datas.sql folder inside the app and import it to your MySQL database. (this is the system database of the PHPDS)

5.  Open  /app/Config/database.php file and change the following lines to mach with your databases settings.


public $default = array(
'datasource' => 'Database/Mysql',
'persistent' => false,
'host' => 'localhost',
'login' => 'root',
'password' => '',
'database' => 'datas',
'prefix' => ''
);

6. Provide write access to app/tmp/cache folder


chmod -R u+w app/tmp/cache

7. Now go to http://localhost/datas/ and you will get the login screen as below

screenshot1
8. Login to system as the administrator (default username: admin, password: admin). Please make sure to change the credentials of default admin login if you are using this in a production server.

screenshot2

9. First of all we need to create a connection to the database server which we are using for create data services. In our case it is our local host. If you are using a remote database please make sure it has the necessary permission to connect to web server which PHPDS is hosted.
To connect to the database server you need to create a data provider first.
Click on Data Provider in the Top Menu and click Add to create a new Data Provider.
Please enter data as below screen. Select provider type as MySQL and click on ‘Load Init Params’ button to load initial prams to the Params text box. Please fill the params JSON string according to your database serverscreenshot3
Click Save

screenshot4

10. Now it is the time to create a ‘Data App’. Before creating a data app it is good to know the what is a  Data App.

Data App
This is a logical application which holds all the Data Services and Data APIs for a particular business domain. In PHPDS, Admin user can create many Data Apps for many different applications. Data App consist of multiple Data Collections and each Data App has a unique alias in PHPDS.

 

11. Click on ‘Data App’ in the top menu and you will get the current data app list, which is empty at first. Click on ‘Add’ button to create a new Data App. Please enter the following data for the data app as shown in the below image. Please make sure alias of the data app should be unique to each data app and it should only contain alpha numeric characters. Check ‘Sandbox Mode’ true if you are at the phase of developing the data app. If you have set the ‘Sandbox Mode’ to true, you can access your services through the given secret key. Make sure to check the ‘Published’ checkbox also.

screenshot5

12. Click on Save button. Now you have created your first data app.

screenshot6

13. Now you need to create ‘Data Collections’ inside our Data App.

Data Collection
Data Collection is a connection to a particular database of a Data Provider which resides in Data App. Data collection has a unique alias which relevant to a particular Data App. When crating a Data Connection admin has to specify the database name as well as the Data Provider. Data App can have multiple Data Collections which associates with different types of Data Providers (which means different types of DBMS connections to databases).

14. Click on GoInto link of the Data App and you will get the list of  Data Collections which is associate with our data app. Currently it is empty. We need to create three Data Collections for our Data App. Our use cases has three main actors (user, public and admin), in this case we create three Data Collections for each actor. Click on Add and create three data collections as describe below. Please make sure the name of each Data Collection should be unique to each Data App and it should only contain alphanumeric characters.

Data Collection 1
Name -> Tourism Public  Service
Alias -> tourism-public
Data Provider -> (the provider you created earlier)
Database -> tourism
Published -> Yes

Data Collection 2
Name -> Tourism User Service
Alias -> tourism-user
Data Provider -> (the provider you created earlier)
Database -> tourism
Published -> Yes

Data Collection 3
Name -> Tourism Admin Service
Alias -> tourism-admin
Data Provider -> (the provider you created earlier)
Database -> tourism
Published -> Yes

When creating the Data Collection, please select the Data Provider which we created first and set the database as ‘tourism’. Please make sure the ‘Published’ checkbox is checked

screenshot7

14. After creating three Data Collections you will get three Data Collections similar to following.

screenshot8

15. Now you are done with Data Collections. Let’s create Data Methods for each Data Collection.

Data Method
Data Collection consists of different Data Methods. After creating Data Collection, PHPDS will automatically pickup its data tables and show what the operations a can be enabled to each data tables in a Data Collections. Admin can create CRUD (create, retrieve, update, delete) based operations or custom data operations using Data Methods.

Select ‘Tourism Public Service’ and click on ‘Go Into’ link. Now you are inside ‘Tourism Public Service’ Data Collection.
You can see there are two tabs. One is for custom methods and one is for CRUD methods.

For the public API we need only the read access of comments, provinces and districts. Select CRUD methods tab. You can see all the tables in the database is listed down in that tab. Please check Retrieve operations for comments, provinces and districts tables as following. You can also test it by clicking on the test link.

screenshot9

NOTE: since you have enabled ‘SandBox Mode’ in the Data App you can access to each service using the secret key which is provided when creating the Data App.

To access a service you need to construct following URL. As and example if have enabled the ‘Retrieve’ method and you need to access that service the URL will be

<Your host>/services/data/<Data App name>/<Data Collection name>/<Table name>

Eg: http://localhost/datas/services/data/tourism/tourism-public/cities

You cannot directly access to your service unless you have not provided the secret key and your app is not in ‘Sandbox Mode’. You can directly access the API by providing the secret as a URL param as following

http://localhost/datas/services/data/tourism/tourism-public/cities?secret=12345

screenshot11

If your dataset is huge you can provide limit and offset parameters to limit the element of your data set

http://localhost/datas/services/data/tourism/tourism-public/cities?secret=12345&limit=2&offset=1

You also can get a single record by providing the id of the record as following

<Your host>/services/data/<Data App name>/<Data Collection name>/<Tablename> /<id>

http://localhost/datas/services/data/tourism/tourism-public/cities/1

16. Now we need to get cities which is belong to particular  province, but it cannot be achieved using CRUD method. In this case we need to create a Custom Method using custom SQL query.

Click on Custom Tab.
Click ‘Add’ button to create a Custom Method.

screenshot12

Please make sure the Method name should consist of only alpha numeric characters and it should be unique to each Method. Alias normally is the name of the table which you use to get information. command is a SQL query which you executes. In SQL query you can add parameters (inside double curly brackets). In our scenario the SQL query would be

SELECT * FROM cities WHERE province_id={{province_id}}

Select HTTP method as GET since we are retrieving information. You can add multiple HTTP Method for a Method. Make the published button checked and click save.

In our custom method we have one parameter called province_id. We need to add a validation for that parameter. It is a required parameter and it should only represent a number. Once you save the Method, click on ‘Input Parameter’ link against the method. It will list down all the parameters which added to our SQL command.

Data Input Param
If admin creates custom data methods with input parameters, Data Input Params will be automatically generated by PHPDS system. After that admin can apply various input param validations to each Data Input Params (Whether it is required, or whether it is a number or email)

screenshot13

17. Select province_id and click ‘Edit’ . Change the validation type to numeric and check Is Required. Click ‘Save’

screenshot14

18. Now go back to the Method list using bottom breadcrumb. Click on Test link against the custom method you have just created.

When you access the URL http://localhost/datas/services/data/tourism/tourism-public/cities/getCitiesByProvince?secret=12345 you will get the following output.

screenshot15

Now provide the province_id in query string http://localhost/datas/services/data/tourism/tourism-public/cities/getCitiesByProvince?secret=12345&province_id=1

screenshot16

19. Now you have crated a web service for  public actor.

Select ‘Tourism User Service’ Data Collection and go to CRUD operation tab. (You can use bottom breadcrumb for easy navigation between, Data Apps, Data Collections, Methods and Input Parameters) enable operations as following for user actor.

screenshot17

Select ‘Tourism Admin Service’ Data Collection and go to CRUD operation tab. enable operations as following for admin actor

screenshot18

20. Congratulations! now you have created your first Data Service App.

In our next tutorial We will see how to create secure APIs for our services and Apply Social network authentications such as Facebook to our APIs. You can access next tutorial from this link http://technotes.dilan.me/?p=185


10 thoughts on “PHP Data Services – Tutorial Part I (Creating Services)

  1. I am not able to access the application. I downloaded and installed the files from the repository and modified the database.php. The url http://localhost/datas does not display any page. I have verified that my host server mod_rewrite enabled. I also noticed that i do not have a folder called tmp in the app/ dir. Is there some folders or files missing from the git repository?

    • Hi Nathanael, I added tmp folder to master. But it gets automatically created if the folder has permission.

  2. I’ve got this error:
    ———————————–

    Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘User.username’ in ‘where clause’

    SQL Query: SELECT `User`.`id`, `User`.`email`, `User`.`password`, `User`.`first_name`, `User`.`last_name`, `User`.`created_at` FROM `api_test`.`users` AS `User` WHERE `User`.`username` = ‘admin’ LIMIT 1

    Notice: If you want to customize this error message, create app\View\Errors\pdo_error.ctp

    Stack Trace
    CORE\Cake\Model\Datasource\DboSource.php line 460 → PDOStatement->execute(array)
    CORE\Cake\Model\Datasource\DboSource.php line 426 → DboSource->_execute(string, array)
    CORE\Cake\Model\Datasource\DboSource.php line 666 → DboSource->execute(string, array, array)
    CORE\Cake\Model\Datasource\DboSource.php line 1077 → DboSource->fetchAll(string, boolean)
    CORE\Cake\Model\Model.php line 2880 → DboSource->read(User, array)
    CORE\Cake\Model\Model.php line 2852 → Model->_readDataSource(string, array)
    CORE\Cake\Controller\Component\Auth\BaseAuthenticate.php line 114 → Model->find(string, array)
    CORE\Cake\Controller\Component\Auth\FormAuthenticate.php line 81 → BaseAuthenticate->_findUser(string, string)
    CORE\Cake\Controller\Component\AuthComponent.php line 752 → FormAuthenticate->authenticate(CakeRequest, CakeResponse)
    CORE\Cake\Controller\Component\AuthComponent.php line 599 → AuthComponent->identify(CakeRequest, CakeResponse)
    APP\Controller\UsersController.php line 59 → AuthComponent->login()
    [internal function] → UsersController->login()
    CORE\Cake\Controller\Controller.php line 490 → ReflectionMethod->invokeArgs(UsersController, array)
    CORE\Cake\Routing\Dispatcher.php line 187 → Controller->invokeAction(CakeRequest)
    CORE\Cake\Routing\Dispatcher.php line 162 → Dispatcher->_invoke(UsersController, CakeRequest, CakeResponse)
    APP\webroot\index.php line 110 → Dispatcher->dispatch(CakeRequest, CakeResponse)

  3. Hello Dilan

    This is something I was trying to find/develop myself. Thanks a lot!
    I have everything working, but I do have one question:

    I created the CRUD operations and now I want to create a record on the database. So I am using the Google Chrome app Postman to send a post request.

    I receive:

    {“message”: “Insert operation success!”, “code”: “809”}

    And when I check the database, there is a new record, but all of the columns are created with “null”. I tried to send the post request with parameters:

    http://127.0.0.1/phpData/services/data/Customers%20App%20Example/Customers%20Public%20API/customers?secret=1234567890&firstName=Alejandro&lastName=Garcia&age=30

    But still, only nulls. How should I pass the parameters?

    Thanks again! This is extremely useful!

    • Hi Alejandro,
      Have you send the JSON response correctly? Have you also send the Content-Type as application/json in the request header?

  4. Trying and trying but even with mod_rewrite, still getting

    “The requested URL /datas/users/login was not found on this server.”

    going to give up soon.

Leave a Reply

Your email address will not be published. Required fields are marked *