byjg / anydataset-db
Anydataset Database Relational abstraction. Anydataset is an agnostic data source abstraction layer in PHP.
Installs: 24 899
Dependents: 2
Suggesters: 0
Security: 0
Stars: 3
Watchers: 2
Forks: 5
Open Issues: 2
Requires
- php: >=5.6.0
- ext-pdo: *
- byjg/anydataset: 4.0.*
- byjg/anydataset-array: 4.0.*
- byjg/uri: 2.1.*
- psr/cache: 1.0.*
Requires (Dev)
- byjg/cache-engine: 4.0.*
- phpunit/phpunit: 5.7.*|7.4.*
Suggests
- ext-curl: *
- ext-oci8: *
- ext-pdo_mysql: *
- ext-pdo_pgsql: *
- byjg/cache-engine: 4.0.*
This package is auto-updated.
Last update: 2022-05-01 00:07:56 UTC
README
Anydataset Database Relational abstraction. Anydataset is an agnostic data source abstraction layer in PHP.
See more about Anydataset here.
Features
- Connection based on URI
- Support and fix code tricks with several databases (MySQL, PostgresSql, MS SQL Server, etc)
- Natively supports Query Cache by implementing a PSR-6 interface
- Supports Connection Routes based on regular expression against the queries, that's mean a select in a table should be executed in a database and in another table should be executed in another (even if in different DB)
Connection Based on URI
The connection string for databases is based on URL.
See below the current implemented drivers:
{:.table}
Database | Connection String | Factory |
---|---|---|
Sqlite | sqlite:///path/to/file | getDbRelationalInstance() |
MySql/MariaDb | mysql://username:password@hostname:port/database | getDbRelationalInstance() |
Postgres | psql://username:password@hostname:port/database | getDbRelationalInstance() |
Sql Server (DbLib) | dblib://username:password@hostname:port/database | getDbRelationalInstance() |
Sql Server (Sqlsrv) | sqlsrv://username:password@hostname:port/database | getDbRelationalInstance() |
Oracle (OCI) | oci://username:password@hostname:port/database | getDbRelationalInstance() |
Oracle (OCI8) | oci8://username:password@hostname:port/database | getDbRelationalInstance() |
Generic PDO | pdo://username:password@pdo_driver?dsn= | getDbRelationalInstance() |
<?php $conn = \ByJG\AnyDataset\Db\Factory::getDbRelationalInstance("mysql://root:password@10.0.1.10/myschema");
Examples
Basic Query
<?php $dbDriver = \ByJG\AnyDataset\Db\Factory::getDbRelationalInstance('mysql://username:password@host/database'); $iterator = $dbDriver->getIterator('select * from table where field = :param', ['param' => 'value']); foreach ($iterator as $row) { // Do Something // $row->getField('field'); }
Updating in Relational databases
<?php $dbDriver = \ByJG\AnyDataset\Db\Factory::getDbRelationalInstance('mysql://username:password@host/database'); $dbDriver->execute( 'update table set other = :value where field = :param', [ 'value' => 'othervalue', 'param' => 'value of param' ] );
Inserting and Get Id
<?php $dbDriver = \ByJG\AnyDataset\Db\Factory::getDbRelationalInstance('mysql://username:password@host/database'); $id = $dbDriver->executeAndGetId( 'insert into table (field1, field2) values (:param1, :param2)', [ 'param1' => 'value1', 'param2' => 'value2' ] );
Database Transaction
<?php $dbDriver = \ByJG\AnyDataset\Db\Factory::getDbRelationalInstance('mysql://username:password@host/database'); $dbDriver->beginTransaction(); // ... Do your queries $dbDriver->commitTransaction(); // or rollbackTransaction()
Cache results
You can easily cache your results with the DbCached class; You need to add to your project an implementation of PSR-6. We suggested you add "byjg/cache".
<?php $dbDriver = \ByJG\AnyDataset\Db\Factory::getDbRelationalInstance('mysql://username:password@host/database'); $dbCached = new \ByJG\AnyDataset\Db\DbCached( $dbDriver, // The connection string $psrCacheEngine, // Any PSR-6 (Cache) implementation 30 // TTL (in seconds) ); // Use the DbCached instance instead the DbDriver $iterator = $dbCached->getIterator('select * from table where field = :param', ['param' => 'value']);
Load balance and connection pooling
The API have support for connection load balancing, connection pooling and persistent connection.
There is the Route class an DbDriverInterface implementation with route capabilities. Basically you have to define the routes and the system will choose the proper DbDriver based on your route definition.
Example:
<?php $dbDriver = new \ByJG\AnyDataset\Db\Route(); // Define the available connections (even different databases) $dbDriver ->addDbDriverInterface('route1', 'sqlite:///tmp/a.db') ->addDbDriverInterface('route2', 'sqlite:///tmp/b.db') ->addDbDriverInterface('route3', 'sqlite:///tmp/c.db') ; // Define the route $dbDriver ->addRouteForWrite('route1') ->addRouteForRead('route2', 'mytable') ->addRouteForRead('route3') ; // Query the database $iterator = $dbDriver->getIterator('select * from mytable'); // Will select route2 $iterator = $dbDriver->getIterator('select * from othertable'); // Will select route3 $dbDriver->execute('insert into table (a) values (1)'); // Will select route1;
The possible route types are:
- addRouteForWrite($routeName, $table = null): Filter any insert, update and delete. Optional specific table;
- addRouteForRead($routeName, $table = null): Filter any select. Optional specific table;
- addRouteForInsert($routeName, $table = null): Filter any insert. Optional specific table;
- addRouteForDelete($routeName, $table = null): Filter any delete. Optional specific table;
- addRouteForUpdate($routeName, $table = null): Filter any update. Optional specific table;
- addRouteForFilter($routeName, $field, $value): Filter any WHERE clause based on FIELD = VALUE
- addCustomRoute($routeName, $regEx): Filter by a custom regular expression.
Connecting To MySQL via SSL
Read here about create SSL mysql
<?php $sslCa = "/path/to/ca"; $sslKey = "/path/to/Key"; $sslCert = "/path/to/cert"; $sslCaPath = "/path"; $sslCipher = "DHE-RSA-AES256-SHA:AES128-SHA"; $verifySsl = 'false'; // or 'true'. Since PHP 7.1. $db = \ByJG\AnyDataset\Db\Factory::getDbRelationalInstance( "mysql://localhost/database?ca=$sslCa&key=$sslKey&cert=$sslCert&capath=$sslCaPath&verifyssl=$verifySsl&cipher=$sslCipher" ); $iterator = $db->getIterator('select * from table where field = :value', ['value' => 10]); foreach ($iterator as $row) { // Do Something // $row->getField('field'); }
Using IteratorFilter in order to get the SQL
You can use the IteratorFilter object to make easier create SQL
<?php // Create the IteratorFilter instance $filter = new \ByJG\AnyDataset\Core\IteratorFilter(); $filter->addRelation('field', \ByJG\AnyDataset\Enum\Relation::EQUAL, 10); // Generate the SQL $param = []; $formatter = new \ByJG\AnyDataset\Db\IteratorFilterSqlFormatter(); $sql = $formatter->format( $filter->getRawFilters(), 'mytable', $param, 'field1, field2' ); // Execute the Query $iterator = $db->getIterator($sql, $param);
Using IteratorFilter with Literal values
Sometimes you need an argument as a Literal value like a function or an explicit conversion.
In this case you have to create a class that expose the "__toString()" method
<?php // The class with the "__toString()" exposed class MyLiteral { //... public function __toString() { return "cast('10' as integer)"; } } // Create the literal instance $literal = new MyLiteral(); // Using the IteratorFilter: $filter = new \ByJG\AnyDataset\Core\IteratorFilter(); $filter->addRelation('field', \ByJG\AnyDataset\Core\Enum\Relation::EQUAL, $literal);
FreeDTS / DBlib Date format Issues
Date has the format "Jul 27 2016 22:00:00.860"
. The solution is:
Follow the solution: https://stackoverflow.com/questions/38615458/freetds-dateformat-issues
Generic PDO configuration
The generic PDO driver uses the format pdo://username:password@pdo_driver?dsn=<LITERAL PDO DSN>
and only need to be
used for drivers are not mapped into the anydataset-db
library.
Let's say we want to connect with the PDO Interbase/Firebase database. After install the PDO properly we need to create the connection string URI.
According to the Firebase documentation the PDO DSN is:
firebird:User=john;Password=mypass;Database=DATABASE.GDE;DataSource=localhost;Port=3050
and adapting to URI style we remove the information about the driver, user and password. Then we have:
$uri = new \ByJG\Util\Uri("pdo://john:mypass@firebird?dsn=" . url_encode('Database=DATABASE.GDE;DataSource=localhost;Port=3050'));
Don't forget we need to url_encode
the DSN parameter.
Install
Just type:
composer require "byjg/anydataset=4.0.*"
Running Unit tests
vendor/bin/phpunit
Running database tests
Run integration tests require you to have the databases up and running. We provided a basic docker-compose.yml
and you
can use to start the databases for test.
Running the databases
docker-compose up -d postgres mysql
Run the tests
phpunit testsdb/PdoMySqlTest.php
phpunit testsdb/PdoSqliteTest.php
phpunit testsdb/PdoPostgresTest.php
phpunit testsdb/PdoDblibTest.php
Optionally you can set the host and password used by the unit tests
export MYSQL_TEST_HOST=localhost # defaults to localhost export MYSQL_PASSWORD=newpassword # use '.' if want have a null password export PSQL_TEST_HOST=localhost # defaults to localhost export PSQL_PASSWORD=newpassword # use '.' if want have a null password export MSSQL_TEST_HOST=localhost # defaults to localhost export MSSQL_PASSWORD=Pa55word export SQLITE_TEST_HOST=/tmp/test.db # defaults to /tmp/test.db