josantonius/database

Library for SQL database management to be used by several providers at the same time.

1.2.0 2018-04-19 00:01 UTC

README

Latest Stable Version Latest Unstable Version License Codacy Badge Total Downloads Travis PSR2 PSR4 CodeCov

Versión en español

SQL database management to be used by several providers at the same time.

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 😉

You can contact me on Twitter or through my email.