Recommend this page to a friend! |
Download |
Info | Example | Files | Install with Composer | Download | Reputation | Support forum | Blog | Links |
Ratings | Unique User Downloads | Download Rankings | ||||
Not enough user ratings | Total: 478 | All time: 5,962 This week: 81 |
Version | License | PHP version | Categories | |||
database-web-api 0.6.133 | Free for non-comm... | 4 | PHP 5, Databases, Web services |
Description | Author | |
This package can provide access to database records via REST API. |
<?php |
![](cover.png)
Version: 0.7.0
Github: https://github.com/marcocesarato/Database-Web-API
Author: Marco Cesarato
Dynamically generate RESTful APIs from the contents of a database table. Provides JSON, XML, and HTML. Supports most popular databases.
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.
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.
Go to this link for go to the wiki of the platform and have a better informations about the usage of this platform and have more examples:
https://github.com/marcocesarato/Database-Web-API/wiki
Edit config.php
to include a single instance of the following for each dataset (including as many instances as you have datasets):
define("__API_NAME__", "Database Web API"); // API Name
Dataset configurations:
| Settings | Description | Default |
|------------------|------------------------------------------------------------------------------------|-----------|
| default | Default dataset | false |
| api | Accessible through API | true |
| name | Database Name | |
| username | Database Username | root |
| password | Database Password | root |
| server | Database Server Address | localhost |
| ttl | Cache time to live (set 1 for disable) | 3600 |
| port | Database Port | 3306 |
| type | Database Type (ex. mysql
, psql
ecc..) | mysql |
| table_list | Database Tables Whitelist (Allow only the tables in this list, if empty allow all) | null |
| table_blacklist | Database Tables Blacklist | null |
| table_docs | Database Documentation (ex. /dataset/docs/table.html) | array() |
| column_list | Database Columns's whitelist (Allow only the columns in this list, if empty allow all) | null |
| column_blacklist | Database Columns's blacklist | null |
define("__API_DATASETS__", serialize(array(
'dataset' => array(
'default' => true, // Default dataset
'api' => true, // Accessible from API request url (ex. is false if you have a different database for auth users)
'name' => 'database_name', // Database name
'username' => 'user', // root is default
'password' => 'passwd', // root is default
'ttl' => 1, // Cache time to live. Disable cache (1 second only)
'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'
),
'table_docs' => array(
/ @example
'table' => array(
"column" => array(
"description" => "Column description",
"example" => "1",
),
),
*/
), // For Autodocoumentation, url ex. /dataset/docs/table.html
'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 __API_DATASETS__
(except the name of database) are optional and will default to the above.
Here is a dataset example for a MySQL database named ?inspections,? accessed with a MySQL user named ?website? and a password of ?s3cr3tpa55w0rd,? with MySQL running on the same server as the website, with the standard port of 3306. All tables may be accessed by Database to API except for ?cache? and ?passwords,? and among the accessible tables, the ?password_hint? column may not be accessed via Database to API. All of this is registered to create an API named ?facility-inspections?.
array(
'default' => true,
'name' => 'inspections',
'username' => 'website',
'password' => 's3cr3tpa55w0rd',
'server' => 'localhost',
'ttl' => 1,
'port' => 3306,
'type' => 'mysql',
'table_docs' => array(),
'table_list' => array(),
'table_blacklist' => array('cache', 'passwords'),
'column_blacklist' => array('password_hint'),
'column_list' => array(),
);
Retrieving the contents of the table history within this dataset as JSON would be accomplished with a request for /facility-inspections/history.json. Note that it is the name of the dataset (facility-inspections) and not the name of the database (inspections) that is specified in the URL.
For a SQLite database, simply provide the path to the database in name.
For an Oracle database, you can either specify a service defined in tsnames.ora (e.g. dept_spending) or you can define an Oracle Instant Client connection string (e.g., //localhost:1521/dept_spending).
| Setting | Description | Type |
|-----------------|-------------------------------------------------------------------|-------|
| sqlite | When enabled store token on SQLite file | Bool |
| sqlite_database | SQLite filename (only if sqlite = true) | Text |
| api_database | Set database name where create api_table (Only if sqlite = false) (__DATASET__
) | Text |
| api_table | Set database table name where store tokens | Text |
| users | Users table to validate | Array |
| Setting | Description | Type |
|----------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|-------|
| database | Database where users are stored (__DATASET__
) | Bool |
| table | Users table name | Text |
| columns | 'id' => 'user_id' // Id column name<br>'username' => 'user_name' // Username column name<br>'password' => 'password' // Password column name<br>'admin' => array('is_admin' => 1) // Admin condition (can be null) | Array |
| search | Search condition | Text |
| check | Validation users condition (ex. is_active = 1) (can be null) | Text |
define("__API_AUTH__", serialize(array( // Set null for disable authentication
'sqlite' => false, // Enabled save token on SQLite file
'sqlite_database' => 'api_token', // SQLite filename (only with sqlite = true)
'api_database' => 'dataset', // Authentication database
'api_table' => 'api_authentications', // API token table name
'users' => array(
'database' => 'dataset', // Database where users are stored
'table' => 'users', // Table where users are stored
'columns' => array(
'id' => 'user_id', // Id column name
'username' => 'user_name', // Username column name
'password' => 'password', // Password column name
'admin' => array('is_admin' => 1) // Admin bypass condition. With this condition true API bypass all black/whitelists and permissions. 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
),
)));
Authentication needed for browse the database.
The authentication permit to managed the privilege of the users (read, write, modify, delete)
Set the header Auth-Account with the username/user id and Auth-Password with the encrypted password like this:
Request example:
GET /auth.json HTTP/1.1
Host: localhost
Auth-Account: marco.cesarato
Auth-Password: md5password
Response example:
[{"token": "b279fb1d0708ed81e7a194e0c5d928b6"}]
Example of token usage on GET, POST, PUT, PATCH and DELETE requests:
Set the header Access-Token with the token values received from auth request like this:
GET /database/users.json` HTTP/1.1
Host: localhost
Access-Token: b279fb1d0708ed81e7a194e0c5d928b6
Check authentication check is needed for verify if a token is valid.
Request example:
GET /auth/check.json HTTP/1.1
Host: localhost
Access-Token: b279fb1d0708ed81e7a194e0c5d928b6
Response example:
{
"user": {
"id": "1",
"role_id": "",
"is_admin": true
},
"response": {
"status": 200,
"message": "OK"
}
}
Retrieve data from dataset
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'
ex: /[database]/[table]/[column]/[value].[format]?order_by=[column]&direction=[direction]
Examples of GET requests:
GET /dataset/users.json HTTP/1.1
Host: localhost
Access-Token: b279fb1d0708ed81e7a194e0c5d928b6
GET /dataset/10/users.json HTTP/1.1
Host: localhost
Access-Token: b279fb1d0708ed81e7a194e0c5d928b6
GET /dataset/users/1.json HTTP/1.1
Host: localhost
Access-Token: b279fb1d0708ed81e7a194e0c5d928b6
GET /dataset/users/is_active/1.json?order_by=username&direction=desc HTTP/1.1
Host: localhost
Access-Token: b279fb1d0708ed81e7a194e0c5d928b6
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 >= 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 OR column > 2
where[column][<] = array(1,2) // column < 1 OR column < 2
where[column][>=] = array(1,2) // column >= 1 OR column >= 2
where[column][<=] = array(1,2) // column <= 1 OR column <= 2
where[column][%] = array("%1","%2") // column LIKE "%1" OR 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'
`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'
for cast a specific type
order_by['users.username::varchar'] = 'DESC'
order_by['users.id::int'] = 'DESC'
ex: /[database]/[tabel]/[colomn]/[value].[format]?order_by=[column]&direction=[direction]
PS: Work only with pgsql and mysql database type at the moment
For get auto-documentation of a database table:
For have a separated file where document your database you can use /docs.php
Insert data
Single insert:
Multiple insert:
Note: At the moment you can add only one row for table
Examples of POST requests:
Single insert:
POST /dataset/users.json HTTP/1.1
Host: localhost
Access-Token: b279fb1d0708ed81e7a194e0c5d928b6
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
Access-Token: b279fb1d0708ed81e7a194e0c5d928b6
insert[users][username]=Marco&insert[users][email]=cesarato.developer@gmail.com&insert[users][password]=3vwjehvdfjhefejjvw&insert[users][is_active]=1
POST /dataset.json HTTP/1.1
Host: localhost
Access-Token: b279fb1d0708ed81e7a194e0c5d928b6
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
Update data
Single update:
Multiple update:
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
Access-Token: b279fb1d0708ed81e7a194e0c5d928b6
update['username']=Marco&update['email']=cesarato.developer@gmail.com&update['password']=3vwjehvdfjhefejjvw&update['is_active']=1
Multi-table Update:
PUT /dataset.json HTTP/1.1
Host: localhost
Access-Token: b279fb1d0708ed81e7a194e0c5d928b6
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
Multiple Update:
PUT /dataset.json HTTP/1.1
Host: localhost
Access-Token: b279fb1d0708ed81e7a194e0c5d928b6
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&update[cities][][values][name]=Milano&update[cities][][where][id]=2
Delete data
Examples of DELETE Requests:
DELETE /dataset/users/1.json HTTP/1.1
Host: localhost
Access-Token: b279fb1d0708ed81e7a194e0c5d928b6
For write hooks you can use plugins/custom
folder or edit manually the examples on plugins/actions.php
or plugins/filters.php
Remember to name file like this pattern: [FILENAME].hooks.php
or it will not be included automatically (else you can include it manually)
You can use this code for 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
https://github.com/marcocesarato/Database-Web-API/wiki/3.2)-Hooks:-List
| Hook | Type | Description | Params | Return | |-----------------------|--------|-----------------------------------------------------------------|------------------------------------------------------------|--------| | sql_restriction | Filter | Add restriction on where conditions for each query | (string) $restriction (string) $table (string) $permission | String | | can_read | Filter | Return if can get/select | (bool) $permission = true | Bool | | can_write | Filter | Return if can post/insert | (bool) $permission = true | Bool | | can_edit | Filter | Return if can put/update | (bool) $permission = true | Bool | | can_delete | Filter | Return if can delete | (bool) $permission = true | Bool | | on_read | Filter | Result content returned on get/read | (array) $data (string) $table | Array | | on_write | Filter | Result content returned on post/write | (array) $data (string) $table | Array | | on_edit | Filter | Result content returned on put/edit | (array) $data (string) $table | Array | | on_delete | Filter | Get result content returned on delete | (array) $data (string) $table | Array | | render | Filter | Manipulate data response rendered | (array) $data (string) $query (string) $method | Array |
Filter: `sql_restriction`
Options of $permission:
case 'READ':
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).')';
Filename: apiclient.class.php
Class name: APIClient
| Method | Description | Type | Parameters | Return | | ---------------- | -------------------------------------------------- | ----------------- | ---------------------------------- | ---------------------------------------------- | | getInstance | | public<br>static | | Returns static reference to the class instance | | isConnected | Is Connected | public | | bool | | setUrl | Set Url | public<br>static | string $url | | | setAccessToken | Set Access token | public<br>static | string $token | | | setDataset | Set Dataset | public<br>static | string $dataset | | | setTimeout | Set Timeout | public<br>static | int $timeout = 15 | | | setExecutionTime | Set max execution time | public<br>static | int $time = 60 | | | get | Get data | public | string $table<br>array $where | bool<br>mixed | | insert | Insert data | public | array $params | bool<br>mixed | | update | Update data | public | array $params | bool<br>mixed | | replace | Replace data | public | array $params | bool<br>mixed | | delete | Delete data | public | string $table<br>array $params | bool<br>mixed | | searchElement | Search object in array | public | $array<br>$key<br>$value | mixed | | filterBy | Filter object in array | public | $key<br>$value<br>$array<br>$limit | mixed | | filter | Filter object in array | public | $values<br>$array<br>$limit | mixed |
https://github.com/project-open-data/db-to-api
<https://github.com/voku/php-hooks>
Files (58) |
File | Role | Description | ||
---|---|---|---|---|
clients (1 file) | ||||
includes (3 files, 2 directories) | ||||
plugins (4 files, 3 directories) | ||||
.cs.php | Example | Example script | ||
.htaccess | Data | Auxiliary data | ||
composer.json | Data | Auxiliary data | ||
composer.lock | Data | Auxiliary data | ||
config.php | Aux. | Auxiliary script | ||
cover.png | Data | Auxiliary data | ||
cover.png | Data | Auxiliary data | ||
docs.php | Aux. | Auxiliary script | ||
index.php | Example | Example script | ||
LICENSE | Lic. | License text | ||
logs.php | Example | Example script | ||
README.md | Doc. | Documentation | ||
test.php | Aux. | Auxiliary script | ||
TODO.md | Data | Auxiliary data |
Files (58) | / | includes |
File | Role | Description | ||
---|---|---|---|---|
classes (8 files, 1 directory) | ||||
template (2 files, 3 directories) | ||||
compatibility.php | Aux. | Auxiliary script | ||
functions.php | Example | Example script | ||
loader.php | Example | Example script |
Files (58) | / | includes | / | classes |
File | Role | Description | ||
---|---|---|---|---|
PDO (7 files) | ||||
API.php | Class | Class source | ||
Auth.php | Class | Class source | ||
DatabaseErrors.php | Class | Class source | ||
Dump.php | Class | Class source | ||
Hooks.php | Class | Class source | ||
Logger.php | Class | Class source | ||
Request.php | Class | Class source | ||
Response.php | Class | Class source |
Files (58) | / | includes | / | classes | / | PDO |
File | Role | Description |
---|---|---|
PDO.php | Class | Class source |
PDOStatement_mysql.class.php | Class | Class source |
PDOStatement_pgsql.class.php | Class | Class source |
PDOStatement_sqlite.class.php | Class | Class source |
PDO_mysql.class.php | Class | Class source |
PDO_pgsql.class.php | Class | Class source |
PDO_sqlite.class.php | Class | Class source |
Files (58) | / | includes | / | template |
File | Role | Description | ||
---|---|---|---|---|
css (7 files) | ||||
img (2 files) | ||||
js (1 file, 1 directory) | ||||
footer.php | Aux. | Auxiliary script | ||
header.php | Aux. | Auxiliary script |
Files (58) | / | includes | / | template | / | css |
File | Role | Description |
---|---|---|
bootstrap-grid.css | Data | Auxiliary data |
bootstrap-grid.min.css | Data | Auxiliary data |
bootstrap-reboot.css | Data | Auxiliary data |
bootstrap-reboot.min.css | Data | Auxiliary data |
bootstrap.css | Data | Auxiliary data |
bootstrap.min.css | Data | Auxiliary data |
main.css | Data | Auxiliary data |
Files (58) | / | includes | / | template | / | img |
File | Role | Description |
---|---|---|
glyphicons-halflings-white.png | Icon | Icon image |
glyphicons-halflings.png | Icon | Icon image |
Files (58) | / | includes | / | template | / | js |
Files (58) | / | includes | / | template | / | js | / | vendor |
File | Role | Description |
---|---|---|
bootstrap.bundle.js | Data | Auxiliary data |
bootstrap.bundle.min.js | Data | Auxiliary data |
bootstrap.js | Data | Auxiliary data |
bootstrap.min.js | Data | Auxiliary data |
jquery-3.2.1.min.js | Data | Auxiliary data |
modernizr-2.6.1-respond-1.1.0.min.js | Data | Auxiliary data |
Files (58) | / | plugins |
File | Role | Description | ||
---|---|---|---|---|
custom (1 file) | ||||
helpers (1 file) | ||||
tables (1 file) | ||||
actions.php | Example | Example script | ||
filters.php | Example | Example script | ||
functions.php | Aux. | Auxiliary script | ||
loader.php | Example | Example script |
The PHP Classes site has supported package installation using the Composer tool since 2013, as you may verify by reading this instructions page. |
Install with Composer |
Version Control | Unique User Downloads | Download Rankings | |||||||||||||||
100% |
|
|
User Comments (1) | |||||
|
Applications that use this package |
If you know an application of this package, send a message to the author to add a link here.