Anydataset Database Relational abstraction. Anydataset is an agnostic data source abstraction layer in PHP.
See more about Anydataset here.
The connection string for databases is based on URL.
See below the current implemented drivers:
Database | Connection String | Factory |
---|---|---|
Sqlite | sqlite:///path/to/file | getDbRelationalInstance() |
MySql/MariaDb | mysql://username:[email protected]:port/database | getDbRelationalInstance() |
Postgres | psql://username:[email protected]:port/database | getDbRelationalInstance() |
Sql Server (DbLib) | dblib://username:[email protected]:port/database | getDbRelationalInstance() |
Sql Server (Sqlsrv) | sqlsrv://username:[email protected]:port/database | getDbRelationalInstance() |
Oracle (OCI) | oci://username:[email protected]:port/database | getDbRelationalInstance() |
Oracle (OCI8) | oci8://username:[email protected]:port/database | getDbRelationalInstance() |
Generic PDO | pdo://username:[email protected]_driver?dsn=LITERAL_PDO_DSN | getDbRelationalInstance() |
<?php
$conn = \ByJG\AnyDataset\Db\Factory::getDbRelationalInstance("mysql://root:[email protected]/myschema");
<?php
$dbDriver = \ByJG\AnyDataset\Db\Factory::getDbRelationalInstance('mysql://username:[email protected]/database');
$iterator = $dbDriver->getIterator('select * from table where field = :param', ['param' => 'value']);
foreach ($iterator as $row) {
// Do Something
// $row->getField('field');
}
<?php
$dbDriver = \ByJG\AnyDataset\Db\Factory::getDbRelationalInstance('mysql://username:[email protected]/database');
$dbDriver->execute(
'update table set other = :value where field = :param',
[
'value' => 'othervalue',
'param' => 'value of param'
]
);
<?php
$dbDriver = \ByJG\AnyDataset\Db\Factory::getDbRelationalInstance('mysql://username:[email protected]/database');
$id = $dbDriver->executeAndGetId(
'insert into table (field1, field2) values (:param1, :param2)',
[
'param1' => 'value1',
'param2' => 'value2'
]
);
<?php
$dbDriver = \ByJG\AnyDataset\Db\Factory::getDbRelationalInstance('mysql://username:[email protected]/database');
$dbDriver->beginTransaction();
// ... Do your queries
$dbDriver->commitTransaction(); // or rollbackTransaction()
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:[email protected]/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']);
You can pass parameter directly to the PDODriver by adding to the connection string a query parameter with the value.
e.g.
<?php
$uri = Uri::getInstanceFromUri("mysql://root:[email protected]")
->withQueryKeyValue(PDO::MYSQL_ATTR_COMPRESS, 1);
$db = Factory::getDbRelationalInstance($uri);
Special Parameters
AnyDatasetDB has some special parameters:
Parameter | Value | Description |
---|---|---|
DbPdoDriver::STATEMENT_CACHE | true | If this parameter is set with "true", anydataset will cache the last prepared queries. |
DbPdoDriver::DONT_PARSE_PARAM | any value | Is this parameter is set with any value, anydataset won't try to parse the SQL to find the values to bind the parameters. |
e.g.
$uri = Uri::getInstanceFromString("sqlite://" . $this->host)
->withQueryKeyValue(DbPdoDriver::STATEMENT_CACHE, "true")
->withQueryKeyValue(DbPdoDriver::DONT_PARSE_PARAM, "");
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:
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');
}
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);
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);
AnyDataset has a helper ByJG\AnyDataset\Db\DbFunctionsInterface
that can be adjustable for each Database connection you have. It helps to do specific iteraction like:
It is useful when you are working with different and don't want to hard code the information there. E.g.
$dbDriver = \ByJG\AnyDataset\Db\Factory::getDbRelationalInstance('...connection string...');
$dbHelper = $dbDriver->getDbHelper();
// This will return the proper SQL with the TOP 10
// based on the current connection
$sql = $dbHelper->top("select * from foo", 10);
// This will return the proper concatenation operation
// based on the current connection
$concat = $dbHelper->concat("'This is '", "field1", "'concatenated'");
// This will return the proper function to format a date field
// based on the current connection
// These are the formats availables:
// Y => 4 digits year (e.g. 2022)
// y => 2 digits year (e.g. 22)
// M => Month fullname (e.g. January)
// m => Month with leading zero (e.g. 01)
// Q => Quarter
// q => Quarter with leading zero
// D => Day with leading zero (e.g. 01)
// d => Day (e.g. 1)
// h => Hour 12 hours format (e.g. 11)
// H => Hour 24 hours format (e.g. 23)
// i => Minute leading zero
// s => Seconds leading zero
// a => a/p
// A => AM/PM
$date = $dbHelper->sqlDate("d-m-Y H:i", "some_field_date");
$date2 = $dbHelper->sqlDate(DbBaseFunctions::DMYH, "some_field_date"); // Same as above
// This will return the fields with proper field delimiter
// based on the current connection
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
The generic PDO driver uses the format pdo://username:[email protected]_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:[email protected]?dsn=" . url_encode('Database=DATABASE.GDE;DataSource=localhost;Port=3050'));
Don't forget we need to url_encode
the DSN parameter.
Just type:
composer require "byjg/anydataset=4.0.*"
vendor/bin/phpunit
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.
docker-compose up -d postgres mysql
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