PHP Database Web API

Author: Marco Cesarato

Description

Dynamically generate RESTful APIs from the contents of a database table. Provides JSON, XML, and HTML. Supports most popular databases.

What problem this solves

Creating an API to access information within existing database tables is laborious task, when done as a bespoke task. This is often dealt with by exporting the contents of the database as CSV files, and providing downloads of them as a “good enough” solution.

How this solves it

Database Web API acts as a filter, sitting between a database and the browser, allowing users to interact with that database as if it was a native API. The column names function as the key names. This obviates the need for custom code for each database layer.

When Alternative PHP Cache (APC) is installed, parsed data is stored within APC, which accelerates its functionality substantially. While APC is not required, it is recommended highly.

Databases supported

4D
CUBRID
Firebird/Interbase
IBM
Informix
MS SQL Server
MySQL
ODBC and DB2
Oracle
PostgreSQL
SQLite

Requirements

PHP
Database
APC (optional)

Installation

Set the configuration on config.php (Follow the below example to register a new dataset in config.php
If you want config an auth system you must compile on the config the constant __AUTH__ as on the example below
If you want enable the auth system rename .htaccess_auth to .htaccess
Document the API

Configuration

Edit config.php to include a single instance of the following for each dataset (including as many instances as you have datasets):

EXAMPLE with explanation

define("__API_NAME__", "Database Web API"); // API Name
define("__BASE_DIR__", ""); // Dir relative to the http root where is located

define("__AUTH__",  serialize(array( // Set null for disable authentication
    'database' => 'dataset',
    'users' => array(
        'table' => 'users', // Table where users are stored
        'columns' => array(
            'id' => 'user_id',
            'password' => 'password',
            'dmin' => array('is_admin' => 'on') // Admin bypass all black/whitelists. Set NULL for disable
        ),
        'search' => array('user_id', 'email', 'username'), // Search user by these fields
        'check' => array('active' => 1) // Some validation checks. In this case if the column 'active' with value '1'. Set NULL for disable
    ),
    'callbacks' => array( // Functions stored in includes/callbacks.php that you can customize. Set NULL for disable (readonly)
        'sql_restriction' => 'callback_sql_restriction',
        'can_read' => 'callback_can_read',
        'can_write' => 'callback_can_write',
        'can_edit' => 'callback_can_edit',
        'can_delete' => 'callback_can_delete',
    ),
)));

define("__DATASETS__", serialize(array(
	'dataset' => array(
		'name' => 'database_name', // Database name
		'username' => 'user', // root is default
		'password' => 'passwd', // root is default
		'server' => 'localhost',  // localhost default
		'port' => 5432, // 3306 is default
		'type' => 'pgsql', // mysql is default
		'table_list' => array( // Tables's whitelist (Allow only the tables in this list, if empty allow all)
			'users'
		),
		'table_blacklist' => array( // Tables's blacklist
            'passwords'
		),
		'column_list' => array( // Columns's whitelist (Allow only the columns in this list, if empty allow all)
            'users' => array(
                'username',
                'name',
                'surname'
            )
		),
		'column_blacklist' => array( // Columns's blacklist
            'users' => array(
                'password',
            )
		),
	),
)));

_Note: All fields of __DATASETS__ (except the name of database) are optional and will default to the above._

Default dataset values:

array(
    'name' => null,
    'username' => 'root',
    'password' => 'root',
    'server' => 'localhost',
    'port' => 3306,
    'type' => 'mysql',
    'table_blacklist' => array(),
    'table_list' => array(),
    'column_blacklist' => array(),
    'column_list' => array(),
    'ttl' => 3600,
);

Callbacks

Callbacks availables (Prepared versions on includes/callbacks.php):

function callback_sql_restriction($table, $permission)
function callback_can_read($table)
function callback_can_write($table){
function callback_can_edit($table)
function callback_can_delete($table)

You can use this code fo have a database instance and the current user authenticated row:

$user = Auth::getUser(); // User row
$db = API::getDatabase('dataset'); // You can specify dataset. Return PDO Object

Note: All callbacks if return NULL will use default values with readonly permissions.

List

sql_restriction

Description: Return a string to append in where condition

Parameters: $table, $permission

Options of $permission:

case 'READ':
case 'WRITE':
case 'EDIT':
case 'DELETE':

Return

// All denied
$sql = "'1' = '0'";
// All allowed
$sql = "'1' = '1'";

Examples:

// Only Created
$sql = 'created_by = '.$user['id'];
// Only Team
$sql = 'created_by IN ('.implode(',',$teams_ids).')';

can_read

Description: Return if can GET/SELECT

Parameters: $table

Return: Boolean
can_write

Description: Return if can POST/INSERT

Parameters: $table

Return: Boolean
can_edit

Description: Return if can PUT/UPDATE

Parameters: $table

Return: Boolean
can_delete
Description: Return if can DELETE

Parameters: $table

Return: Boolean

Configuration

For implement the callbacks you need to add the callbacks array to the __AUTH__ constant:

'callbacks' => array( // Set NULL for disable (readonly)
     'sql_restriction' => 'callback_sql_restriction',
     'can_read' => 'callback_can_read',
     'can_write' => 'callback_can_write',
     'can_edit' => 'callback_can_edit',
     'can_delete' => 'callback_can_delete',
 ),

API Structure

Format availables:

JSON
XML
HTML (for debug)

Generic URL format for all kind of request:

Fetch all: /[token]/[database]/[table].[format]
Fetch all with limit: /[token]/[database]/[limit]/[table].[format]
Fetch: /[token]/[database]/[table]/[ID].[format]
Fetch search by coolumn: /[token]/[database]/[table]/[column]/[value].[format]

Advanced search:

Note: These examples are valid only for GET and PUT requests

Search single value

where[column]              = 1    // column = 1
where[column][=]           = 1    // column = 1
where[column][!]           = 1    // column != 1
where[column][>]           = 1    // column > 1
where[column][<]           = 1    // column < 1
where[column][%]           = "%1" // column LIKE "%1"

Search multiple values

where[column]              = array(1,5,7)     // IN (...) (IN can be equal to an OR)
where[column][=]           = array(1,5,7)     // IN (...) 
where[column][!]           = array(1,5,7)     // NOT IN (...)
where[column][>]           = array(1,2)       // column > 1 AND column > 2
where[column][<]           = array(1,2)       // column < 1 AND column < 2
where[column][%]           = array("%1","%2") // column LIKE "%1" AND column LIKE "%2"

Specify column's table

where['table.column'][=] = array(1,5,7)

Compare between two different table columns

where['table_a.column_a'] = 'table_b.column_b'

Compare between different columns of main table

where['column_a'] = 'table_a.column_b'
// OR
where['table_a.column_a'] = 'table_a.column_b'
    
// WRONG
where['column_a'] = 'column_b'

Additional parameters

order_by: column_name

Can be and array or a string

order_by = 'username, name, surname'
// OR
order_by = array('username', 'name', 'surname')

for more specific order direction

order_by['users.username'] = 'DESC'

direction: ASC or DESC (default ASC)
limit: max elements to retrieve

ex: /[database]/[tabel]/[colomn]/[value].[format]?order_by=[column]&direction=[direction]

Authentication

Authentication needed for browse the database.

The authentication permit to managed the privilege of the users (read, write, modify, delete)

Authentication: /auth/[password]/[id].[format]

Request example:

GET /auth/password/1265.json HTTP/1.1
Host: localhost

Response example:

[{"token": "b279fb1d0708ed81e7a194e0c5d928b6"}]

Example of token usage on GET, POST, PUT and DELETE requests:

GET /bfee499dfa1387648ec8ce9d621db120/database/users.json` HTTP/1.1
Host: localhost

GET Request

Retrieve data from dataset

Fetch all: /[token]/[database]/[table].[format]
Fetch all with limit: /[token]/[database]/[limit]/[table].[format]
Fetch: /[token]/[database]/[table]/[ID].[format]
Fetch search by column: /[token]/[database]/[table]/[column]/[value].[format]

Fetch all joining table:

join[table] = array(
	'on' => <column_id>,           // Column of the table joined
  	'value' => <value>,            // Column of main table or value
  	'method' => (left|inner|right) // Optional
)

Example with value:

join[users]['on'] = id
join[users]['value'] = 1
join[users]['method'] = 'INNER'

Example with column:

join[users]['on'] = id            // Column of the table joined
join[users]['value'] = user_id    // Column of the main table (no users)
join[users]['method'] = 'INNER'

Additional parameters

ex: /[database]/[table]/[column]/[value].[format]?order_by=[column]&direction=[direction]

Examples of GET requests:

GET /dataset/users.json HTTP/1.1
Host: localhost

GET /dataset/10/users.json HTTP/1.1
Host: localhost

GET /dataset/users/1.json HTTP/1.1
Host: localhost

GET /dataset/users/is_active/1.json?order_by=username&direction=desc HTTP/1.1
Host: localhost

POST Request

Insert data

Single insert:

Select the table on URL: /[database]/[table].[format]
Insert parameter: insert[<column>] = <value>

Multiple insert:

Select dataset on URL: /[database].[format]
Insert parameter: insert[<table>][] = <value>

Multiple insert on the same table:

Select dataset on URL: /[database].[format]
Insert parameter: insert[<table>][<$i>][<column>] = <value>

Examples of POST requests:

Single insert:

POST /dataset/users.json HTTP/1.1
Host: localhost
insert[username]=Marco&insert[email]=cesarato.developer@gmail.com&insert[password]=3vwjehvdfjhefejjvw&insert[is_active]=1

Multiple insert:

POST /dataset.json HTTP/1.1
Host: localhost
insert[users][id]=1000&insert[users][username]=Marco&insert[users][email]=cesarato.developer@gmail.com&insert[users][password]=3vwjehvdfjhefejjvw&insert[users][is_active]=1&
insert[admin][user_id]=1000

Multiple insert on the same table:

POST /dataset.json HTTP/1.1
Host: localhost
insert[users][0][username]=Marco&insert[users][0][email]=cesarato.developer@gmail.com&insert[users][0][password]=3vwjehvdfjhefejjvw&insert[users][0][is_active]=1&insert[users][1][username]=Brad&insert[users][1][email]=brad@gmail.com&insert[users][1][password]=erwerwerffweeqewrf&insert[users][1][is_active]=1

PUT Request

Update data

Single update:

Select the row on URL: /[database]/[table]/[id].[format]
Update parameter: update[<column>] = <value>

Multiple update:

Select the dataset on URL: /[database].[format]
Update parameter: update[<table>][values][<column>] = <value>
Multiple update parameter conditions: update[<table>][where][<column>] = <value>

Note: At the moment you can update only one row for table

Examples of PUT Requests:

Single Update:

PUT /dataset/users/1.json HTTP/1.1
Host: localhost
update['username']=Marco&update['email']=cesarato.developer@gmail.com&update['password']=3vwjehvdfjhefejjvw&update['is_active']=1

Multiple Update:

PUT /dataset.json HTTP/1.1
Host: localhost
update[users][values][username]=Marco&update[users][values][email]=cesarato.developer@gmail.com&update[users][where][id]=1&update[cities][values][name]=Padova&update[cities][where][id]=1

DELETE Request

Delete data

Select the row on table: /[database]/[table]/[id].[format]

Examples of DELETE Requests:

DELETE /dataset/users/1.json HTTP/1.1
Host: localhost

API Client

PHP API Client

Filename: apiclient.class.php

Class name: APIClient

Method	Params	Return	Description
getInstance	-	Void	Returns static reference to the class instance
fetch	$table, $format = 'json', $params = array()	Object	Fetch data
searchElement	$key, $value, $array	Object	Search object in array
filterBy	$key, $value, $array, $limit = null	Array	Filter results array by single key
filter	$value, $array, $limit = null	Array	Filter results array by multiple values

Usage

$api_client = APIClient::getInstance();

$api_client->DEBUG = true;
$api_client->URL = 'http://localhost';
$api_client->ACCESS_TOKEN = '4gw7j8erfgerf6werf8fwerf8erfwfer';
$api_client->DATASET = 'dataset';

$params = array(
    'where' => array(
        'type' => array('C', 'O', 'L'),
        'accounts_addresses.address' => array(
            '!' => '', // NOT NULL
        ),
    ),
    'join' => array(
        'accounts_addresses' => array(
            'on' => 'parent_id',
            'value' => 'id',
            'method' => 'LEFT'
        ),
        'accounts_agents' => array(
            'on' => 'parent_id',
            'value' => 'id'
        ),
    ),
    'order_by' => array(
        'address' => array(
            'table' => 'accounts_addresses',
            'direction' => 'DESC'
        ),
        'type' => array(
            'table' => 'accounts_addresses',
            'direction' => 'ASC'
        )
    ),
);
$records = $api_client->fetch('accounts', 'json', $params);

Credits

https://github.com/project-open-data/db-to-api

	Failed to load latest commit information.
	clients
	includes
	.gitignore
	.htaccess
	.htaccess_auth
	README.md
	config.php
	cover.png
	index.php

marcocesarato/Database-Web-API

Join GitHub today

Clone with HTTPS

Launching GitHub Desktop...

Launching GitHub Desktop...

Launching Xcode...

Launching Visual Studio...

README.md

PHP Database Web API

Description

What problem this solves

How this solves it

Databases supported

Requirements

Installation

Configuration

Callbacks

List

Configuration

API Structure

Format availables:

Generic URL format for all kind of request:

Advanced search:

Additional parameters

Authentication

GET Request

POST Request

PUT Request

DELETE Request

API Client

PHP API Client

Usage

Credits