josantonius / database
Library for SQL database management to be used by several providers at the same time.
Requires
- php: ^5.6 || ^7.0
- eliasis-framework/eliasis: ^1.1.3
Requires (Dev)
- friendsofphp/php-cs-fixer: ^2.3 || ^2.8
- phpmd/phpmd: ^2.6
- phpunit/phpunit: ^5.7 || ^6.0
- squizlabs/php_codesniffer: ^3.0
README
SQL database management to be used by several providers at the same time.
- Requirements
- Installation
- Quick Start
- Get connection
- CREATE TABLE
- SELECT
- INSERT INTO
- UPDATE
- REPLACE
- DELETE
- TRUNCATE TABLE
- DROP TABLE
- Tests
- TODO
- Exception Handler
- Contribute
- Repository
- License
- Copyright
Requirements
This library is supported by PHP versions 5.6 or higher and is compatible with HHVM versions 3.0 or higher.
Installation
The preferred way to install this extension is through Composer.
To install PHP Database library, simply:
$ composer require Josantonius/Database
The previous command will only install the necessary files, if you prefer to download the entire source code you can use:
$ composer require Josantonius/Database --prefer-source
You can also clone the complete repository with Git:
$ git clone https://github.com/Josantonius/PHP-Database.git
Or install it manually:
Download Database.php, Provider.php, PDOprovider.php, MSSQLprovider.php and DBException.php:
$ wget https://raw.githubusercontent.com/Josantonius/PHP-Database/master/src/Database.php
$ wget https://raw.githubusercontent.com/Josantonius/PHP-Database/master/src/Provider/Provider.php
$ wget https://raw.githubusercontent.com/Josantonius/PHP-Database/master/src/Provider/PDOprovider.php
$ wget https://raw.githubusercontent.com/Josantonius/PHP-Database/master/src/Provider/MSSQLprovider.php
$ wget https://raw.githubusercontent.com/Josantonius/PHP-Database/master/src/Exception/DBException.php
Get connection
- Get connection:
Database::getConnection($id, $provider, $host, $user, $name, $password, $settings);
Attribute | Description | Type | Required | Default |
---|---|---|---|---|
$id | Database unique ID. | string | Yes | |
$provider | Name of provider class. | string | No | null |
$host | Database host. | string | No | null |
$user | Database user. | string | No | null |
$name | Database name. | string | No | null |
$password | Database password . | string | No | null |
Attribute | Key | Description | Type | Required | Default |
---|---|---|---|---|---|
$settings | Database options. | array | No | null | |
$settings | 'port' | Database port. | string | No | |
$settings | 'charset' | Database charset. | string | No |
# Return (object) → object with the connection
$db = Database::getConnection( 'identifier', # Unique identifier 'PDOprovider', # Database provider name 'localhost', # Database server 'db-user', # Database user 'db-name', # Database name 'password', # Database password array('charset' => 'utf8') ); $externalDB = Database::getConnection( 'external', # Unique identifier 'PDOprovider', # Database provider name 'http://site.com', # Database server 'db-user', # Database user 'db-name', # Database name 'password', # Database password array('charset' => 'utf8') ); // And once the connection is established: $db = Database::getConnection('identifier'); $externalDB = Database::getConnection('external');
Query
- Process query and prepare it for the provider:
$db->query($query, $statements, $result);
Attribute | Description | Type | Required | Default |
---|---|---|---|---|
$query | Query. | string | Yes | |
$statements | Statements. | array | No | null |
$result | Query result; 'obj', 'array_num', 'array_assoc', 'rows', 'id'. | string | No | 'obj' |
# Return (mixed) → result as object, array, int...
# throws [DBException] → invalid query type
$db->query( 'CREATE TABLE test ( id INT(6) PRIMARY KEY, name VARCHAR(30) NOT NULL, email VARCHAR(50) )' ); $db->query( 'SELECT id, name, email FROM test', false, 'array_assoc' // array_assoc, obj, array_num ); $statements[] = [1, "Many"]; $statements[] = [2, "many@email.com"]; $db->query( 'INSERT INTO test (name, email) VALUES (?, ?)', $statements, 'id' // id, rows );
CREATE TABLE
- CREATE TABLE statement:
$db->create($data) ->table($table) ->foreing($id) ->reference($table) ->on($table) ->actions($action) ->engine($type) ->charset($type) ->execute();
Method | Attribute | Description | Type | Required | Default |
---|---|---|---|---|---|
$data | Column name and configuration for data types. | array | Yes | ||
table() | Set database table name. | method | Yes | ||
$table | Table name. | string | Yes | ||
foreing() | Set foreing key. | method | No | ||
$id | Column id. | string | Yes | ||
reference() | Set reference for foreing keys. | method | No | ||
$table | Table name. | array | Yes | ||
on() | Set database table name. | method | No | ||
$table | Table name. | array | Yes | ||
actions() | Set actions when delete or update for foreing key. | method | No | ||
$action | Action when delete or update. | array | Yes | ||
engine() | Set table engine. | method | No | ||
$type | Engine type. | string | Yes | ||
charset() | Set table charset. | method | No | ||
$type | Charset type. | string | Yes | ||
execute() | Execute query. | method | Yes |
# Return (boolean)
$params = [ 'id' => 'INT(6) PRIMARY KEY', 'name' => 'VARCHAR(30) NOT NULL', 'email' => 'VARCHAR(50)' ]; $query = $db->create($params) ->table('test') ->execute(); $db->create($params) ->table('test_two') ->foreing('id') ->reference('id') ->on('test') ->actions('ON DELETE CASCADE ON UPDATE CASCADE') ->engine('innodb') ->charset('utf8') ->execute();
SELECT
- SELECT statement:
$db->select($columns) ->from($table) ->where($clauses, $statements) ->order($type) ->limit($number) ->execute($result);
Method | Attribute | Description | Type | Required | Default |
---|---|---|---|---|---|
$columns | Column/s name. | mixed | No | '*' | |
from() | Set database table name. | method | Yes | ||
$table | Table name. | string | Yes | ||
where() | Where clauses. | method | No | ||
$clauses | Column name and value. | mixed | Yes | ||
$statements | Statements. | array | No | null | |
order() | Order. | method | No | ||
$type | Query sort parameters. | string | Yes | ||
limit() | Limit. | method | No | ||
$number | Number. | int | Yes | ||
execute() | Execute query. | method | Yes | ||
$result | Query result; 'obj', 'array_num', 'array_assoc', 'rows'. | string | No | 'obj' |
# Return (mixed) → query result (object, array, int...) or rows affected
#SELECT all $db->select() ->from('test') ->execute('array_num'); #SELECT with all params $db->select(['id', 'name']) ->from('test') ->where(['id = 4885', 'name = "Joe"']) ->order(['id DESC', 'name ASC']) ->limit(1) ->execute('obj'); #SELECT with statements $statements[] = [1, 3008]; $statements[] = [2, 'Manny']; $db->select('name') ->from('test') ->where('id = ? OR name = ?', $statements) ->execute('rows'); #Other version of SELECT with statements $statements[] = [':id', 8, 'int']; $statements[] = [':email', null, 'null']; $clauses = [ 'id = :id', 'email = :email' ]; $db->select('name') ->from('test') ->where($clauses, $statements) ->execute('rows');
INSERT INTO
- INSERT INTO statement:
$db->insert($data, $statements) ->in($table) ->execute($result);
Method | Attribute | Description | Type | Required | Default |
---|---|---|---|---|---|
$data | Column name and value. | array | Yes | ||
$statements | Statements. | array | No | null | |
in() | Set database table name. | method | Yes | ||
$table | Table name. | string | Yes | ||
execute() | Execute query. | method | Yes | ||
$result | Query result; 'rows', 'id'. | string | No | 'rows' |
# Return (int) → rows affected or last row affected ID
#INSERT INTO basic example $data = [ "name" => "Isis", "email" => "isis@email.com", ]; $db->insert($data) ->in('test') ->execute(); #INSERT INTO with statements $data = [ "name" => "?", "email" => "?", ]; $statements[] = [1, "Isis"]; $statements[] = [2, "isis@email.com"]; $db->insert($data, $statements) ->in('test') ->execute('rows'); #Other version of INSERT INTO with statements $data = [ "name" => ":name", "email" => ":email", ]; $statements[] = [":name", "Isis", "str"]; $statements[] = [":email", "isis@email.com", "str"]; $db->insert($data, $statements) ->in('test') ->execute('id');
UPDATE
- UPDATE statement:
$db->update($data, $statements) ->in($table) ->where($clauses, $statements) ->execute();
Method | Attribute | Description | Type | Required | Default |
---|---|---|---|---|---|
$data | Column name and value. | array | Yes | ||
$statements | Statements. | array | No | null | |
in() | Set database table name. | method | Yes | ||
$table | Table name. | string | Yes | ||
where() | Where clauses. | method | No | ||
$clauses | Column name and value. | mixed | Yes | ||
$statements | Statements. | array | No | null | |
execute() | Execute query. | method | Yes |
# Return (int) → rows affected
#UPDATE basic example $data = [ 'name' => 'Isis', 'email' => 'isis@email.com', ]; $db->update($data) ->in('test') ->execute(); #UPDATE with WHERE $data = [ 'name' => 'Manny', 'email' => 'manny@email.com', ]; $clauses = [ 'name = "isis"', 'email = "isis@email.com"' ]; $db->update($data) ->in('test') ->where($clauses) ->execute(); #UPDATE with statements $data = [ 'name' => '?', 'email' => '?', ]; $statements['data'][] = [1, 'Isis']; $statements['data'][] = [2, 'isis@email.com']; $clauses = 'id = ? AND name = ? OR name = ?'; $statements['clauses'][] = [3, 4883]; $statements['clauses'][] = [4, 'Isis']; $statements['clauses'][] = [5, 'Manny']; $db->update($data, $statements['data']) ->in('test') ->where($clauses, $statements['clauses']) ->execute(); #Other version of UPDATE with statements $data = [ 'name' => ':new_name', 'email' => ':new_email', ]; $statements['data'][] = [':new_name', 'Manny', 'str']; $statements['data'][] = [':new_email', 'manny@email.com', 'str']; $clauses = 'name = :name1 OR name = :name2'; $statements['clauses'][] = [':name1', 'Isis', 'str']; $statements['clauses'][] = [':name2', 'Manny', 'str']; $db->update($data, $statements['data']) ->in('test') ->where($clauses, $statements['clauses']) ->execute();
REPLACE
- Replace a row in a table if it exists or insert a new row if not exist:
$db->replace($data, $statements) ->from($table) ->execute($result);
Method | Attribute | Description | Type | Required | Default |
---|---|---|---|---|---|
$data | Column name and value. | array | Yes | ||
$statements | Statements. | array | No | null | |
from() | Set database table name. | method | Yes | ||
$table | Table name. | string | Yes | ||
execute() | Execute query. | method | Yes | ||
$result | Query result; 'rows', 'id'. | string | No | 'rows' |
# Return (int) → rows affected or last row affected ID
#REPLACE basic example $data = [ 'id' => 3008, 'name' => 'Manny', 'email' => 'manny@email.com', ]; $db->replace($data) ->from('test') ->execute(); #UPDATE with statements $data = [ 'id' => 4889, 'name' => ':name', 'email' => ':email', ]; $statements[] = [':name', 'Manny']; $statements[] = [':email', 'manny@email.com']; $db->replace($data, $statements) ->from('test') ->execute('rows'); #Other version of UPDATE with statements $data = [ 'id' => 2, 'name' => '?', 'email' => '?', ]; $statements[] = [1, 'Manny']; $statements[] = [2, 'manny@email.com']; $db->replace($data, $statements) ->from('test') ->execute('id');
DELETE
- DELETE statement:
$db->delete($data, $statements) ->from($table) ->where($clauses, $statements) ->execute();
Method | Attribute | Description | Type | Required | Default |
---|---|---|---|---|---|
$data | Column name and value. | array | Yes | ||
$statements | Statements. | array | No | null | |
from() | Set database table name. | method | Yes | ||
$table | Table name. | string | Yes | ||
where() | Where clauses. | method | No | ||
$clauses | Column name and value. | mixed | Yes | ||
$statements | Statements. | array | No | null | |
execute() | Execute query. | method | Yes |
# Return (int) → rows affected
#DELETE all $db->delete() ->from('test') ->execute(); #DELETE with WHERE $clauses = [ 'id = 4884', 'name = "isis"', 'email = "isis@email.com"', ]; $db->delete() ->from('test') ->where($clauses) ->execute(); #DELETE with statements $clauses = 'id = :id AND name = :name1 OR name = :name2'; $statements[] = [':id', 4885]; $statements[] = [':name1', 'Isis']; $statements[] = [':name2', 'Manny']; $db->delete() ->from('test') ->where($clauses, $statements) ->execute(); #Other version of DELETE with statements $clauses = 'id = :id AND name = :name1 OR name = :name2'; $statements[] = [':id', 4886, 'int']; $statements[] = [':name1', 'Isis', 'src']; $statements[] = [':name2', 'Manny', 'src']; $db->delete() ->from('test_table') ->where($clauses, $statements) ->execute();
TRUNCATE TABLE
- TRUNCATE TABLE statement:
$db->truncate() ->table($table) ->execute();
Method | Attribute | Description | Type | Required | Default |
---|---|---|---|---|---|
table() | Set database table name. | method | Yes | ||
$table | Table name. | string | Yes | ||
execute() | Execute query. | method | Yes |
# Return (boolean)
$db->truncate() ->table('test') ->execute();
DROP TABLE
- DROP TABLE statement:
$db->drop() ->table($table) ->execute();
Method | Attribute | Description | Type | Required | Default |
---|---|---|---|---|---|
table() | Set database table name. | method | Yes | ||
$table | Table name. | string | Yes | ||
execute() | Execute query. | method | Yes |
# Return (boolean)
$db->drop() ->table('test') ->execute();
Quick Start
To use this class with Composer:
require __DIR__ . '/vendor/autoload.php'; use Josantonius\Database\Database;
Or If you installed it manually, use it:
require_once __DIR__ . '/Database.php'; use Josantonius\Database\Database;
Tests
To run tests you just need composer and to execute the following:
$ git clone https://github.com/Josantonius/PHP-Database.git
$ cd PHP-Database
$ composer install
Run unit tests with PHPUnit:
$ composer phpunit
Run PSR2 code standard tests with PHPCS:
$ composer phpcs
Run PHP Mess Detector tests to detect inconsistencies in code style:
$ composer phpmd
Run all previous tests:
$ composer tests
☑ TODO
- Add new feature.
- Improve tests.
- Improve documentation.
- Refactor code for disabled code style rules. See phpmd.xml and .php_cs.dist.
Exception Handler
This library uses exception handler that you can customize.
Contribute
If you would like to help, please take a look at the list of issues or the To Do checklist.
Pull requests
- Fork and clone.
- Run the command
composer install
to install the dependencies. This will also install the dev dependencies. - Run the command
composer fix
to excute code standard fixers. - Run the tests.
- Create a branch, commit, push and send me a pull request.
Repository
The file structure from this repository was created with PHP-Skeleton.
License
This project is licensed under MIT license. See the LICENSE file for more info.
Copyright
2017 - 2018 Josantonius, josantonius.com
If you find it useful, let me know 😉