activecollab / databaseconnection
A couple of simple helper methods wrapped around MySQLi connection
Installs: 61 959
Dependents: 6
Suggesters: 0
Security: 0
Stars: 0
Watchers: 3
Forks: 1
Open Issues: 1
Requires
- php: >=8.0
- ext-json: *
- ext-mbstring: *
- ext-mysqli: *
- activecollab/containeraccess: ^2.0
- activecollab/datevalue: ^2.0
- psr/log: ^1.0.0
Requires (Dev)
- friendsofphp/php-cs-fixer: ^2.0
- phpunit/phpunit: ~9.0
- pimple/pimple: ~3.5.0
README
Purpose of this library is not to abstract the database, but to make work with MySQLi connections a bit easier. Features:
- Results that can be easily iterated,
- Results can be arrays of rows, or objects, loaded by a known class name, or by a class name read from the row field,
- Automatic value casting based on field name.
What's the thinking behind yet another database abstraction layer? Focus and history. This library has been part of Active Collab for many years, so it works really well. On the other hand, it's simple - works only with MySQL, can be read and understood in an hour and still manages to save you a lot of time.
Getting the Data
This library makes query execution quick and easy. You can fetch all records, only first record, only first column or only first cell (first column of the first record). Here's a couple of examples:
<?php use ActiveCollab\DatabaseConnection\Connection\MysqliConnection; use MySQLi; use RuntimeException; $database_link = new MySQLi('localhost', 'root', '', 'activecollab_database_connection_test'); if ($database_link->connect_error) { throw new RuntimeException('Failed to connect to database. MySQL said: ' . $database_link->connect_error); } $connection = new MysqliConnection($database_link); // List all writers foreach ($connection->execute('SELECT `id`, `name` FROM `writers` WHERE `name` = ? ORDER BY `id`', 'Leo Tolstoy') as $row) { print '#' . $row['id'] . ' ' . $row['name'] . "\n"; } // Get the first cell of the first row (so we can print Tolstoy's birthday) print $connection->executeFirstCell('SELECT `birthday` FROM `writers` WHERE `name` = ? LIMIT 0, 1', 'Leo Tolstoy'); // Get everything that we have on Leo Tolstoy print_r($connection->executeFirstRow('SELECT * FROM `writers` WHERE `name` = ?', 'Leo Tolstoy')); // Show names of all authors print_r($connection->executeFirstColumn('SELECT `name` FROM `writers` ORDER BY `name`'));
Selecting Records
To run SELECT
queries from arguments, instead of writing your own SELECT
query, use the following methods:
select()
- maps toexecute()
,selectFirstRow()
- maps toexecuteFirstRow()
,selectFirstColumn()
- maps toexecuteFirstColumn()
,selectFirstCell()
- maps toexecuteFirstCell()
.
All methods accept following arguments:
$table_name
- Name of the table. This is the only required argument,$fields
- List of fields that need to be fetched. Provide a string (single field), array of fields, orNULL
(all fields),$conditions
- Query conditions. Provide a string, an array (pattern + arguments), orNULL
,$order_by_fields
- List of fields that we want to order records by. Provide a string (single field), array of fields, orNULL
(all fields).
$writers = $connection->select('writers', ['id', 'name'], ['birthday >= ?', '1821-11-11'], 'name');
Counting Records
DatabaseConnection lets you easily count records from the table:
$num = $connection->count('writers');
By default, it returns number of all records from the table. To filter, you can provide $conditions
argument:
$num = $connection->count('writers', "`name` = 'Leo Tolstoy'"); $num = $this->connection->count('writers', ['name = ?', 'Leo Tolstoy']);
count()
method also assumes that there's id
primary key in the table, so it prepares query as COUNT(id)
. Name of
the column that we count agains can also be changed (even to *
):
$num = $this->connection->count('writers', null, '*'); $num = $this->connection->count('writers', null, 'name')
Running Queries from File
In order to run all queries from a file, use executeFromFile()
method:
$connection->executeFromFile('/path/to/file');
Note: This method is not implemented to handle large data dumps. Use mysqldump
instead, or another specialized backup utility instead.
Connection Factory
Quickest way to connect is to use ConnectionFactory
class:
<?php use ActiveCollab\DatabaseConnection\Connection\MysqliConnection; use ActiveCollab\DatabaseConnection\ConnectionFactory; $connection = (new ConnectionFactory())->mysqli('localhost', 'root', '', 'activecollab_database_connection_test', 'utf8mb4'); if ($connection instanceof MysqliConnection) { print_r($connection->getTableNames()); }
First three arguments are required (MySQL hostname, username and password). Additionally, this function accepts:
- Name of the database that needs to be selected. When not specified, database will not be selected,
- Connection encoding that we would like to enforce. When not specified, default connection encoding will be used.
Batch Inserts
Batch insert utility helps you prepare and insert a lot of rows of the same type. Example:
// Insert 3 rows per INSERT query in `writers` table $batch_insert = $connection->batchInsert('writers', ['name', 'birthday'], 3); $batch_insert->insert('Leo Tolstoy', new DateTime('1828-09-09')); // No insert $batch_insert->insert('Alexander Pushkin', new DateTime('1799-06-06')); // No insert $batch_insert->insert('Fyodor Dostoyevsky', new DateTime('1821-11-11')); // Insert $batch_insert->insert('Anton Chekhov', new DateTime('1860-01-29')); // No insert $batch_insert->done(); // Insert remaining rows and close the batch insert
Note: Calling insert
, insertEscaped
, flush
or done
methods once batch is done will throw RuntimeException
.
Batch insert can also be used to replace records (uses REPLACE INTO
instead of INSERT INTO queries):
$batch_replace = $this->connection->batchInsert('writers', ['name', 'birthday'], 3, ConnectionInterface::REPLACE);
Casting
Unless specified differently, following conventions apply:
id
androw_count
fields are always cast to integers,- Fields with name ending with
_id
are cast to integers, - Fields with name starting with
is_
,has_
,had_
,was_
,were_
andhave_
are cast to boolean, - Fields with name ending with
_at
or_on
are cast to DateValue.
Spatial data
Library works with spatial data types, as defined by OpenGIS. Supported types:
- Point
- Multi Point
- Line String
- Multi Line String
- Linear Ring
- Polygon
- Multi Polygon
- Geometry Collection
Each supported type is defined as a separate value object, under \\ActiveCollab\DatabaseConnection\Spatial
namespace. To specify which fields should be treated as spatial, instruct value caster to use \\ActiveCollab\DatabaseConnection\Record\ValueCasterInterface::CAST_SPATIAL
. Example:
$rows = $this->connection->execute('SELECT ST_AsText(`geometry`) AS "geometry" FROM `example`'); $rows->setValueCaster( new ValueCaster( [ 'geometry' => ValueCasterInterface::CAST_SPATIAL, ] ) );
Object Hydration
This library enables quick and easy object hydration. To hydrate objects, you'll need a class that implements \ActiveCollab\DatabaseConnection\Record\LoadFromRow
interface, for example:
<?php use ActiveCollab\DatabaseConnection\Record\LoadFromRow; use DateTime; class Writer implements LoadFromRow { /** * @var array */ private $row; /** * @param array $row */ public function loadFromRow(array $row) { $this->row = $row; } /** * @return integer */ public function getId() { return $this->row['id']; } /** * @return string */ public function getName() { return $this->row['name']; } /** * @return DateTime */ public function getBirthday() { return new DateTime($this->row['birthday']); } }
You can get a list of hydrated objects by passing the name of the class to advancedExecute()
method:
<?php use ActiveCollab\DatabaseConnection\Connection\MysqliConnection; use DateTime; use MySQLi; use RuntimeException; $database_link = new MySQLi('localhost', 'root', '', 'activecollab_database_connection_test'); if ($database_link->connect_error) { throw new RuntimeException('Failed to connect to database. MySQL said: ' . $database_link->connect_error); } $connection = new MysqliConnection($database_link); foreach ($this->connection->advancedExecute('SELECT * FROM `writers` ORDER BY `id`', null, ConnectionInterface::LOAD_ALL_ROWS, ConnectionInterface::RETURN_OBJECT_BY_CLASS, '\ActiveCollab\DatabaseConnection\Test\Fixture\Writer') as $writer) { print '#' . $writer->getId() . ' ' . $writer->getName() . ' (' . $writer->getBirthday()->format('Y-m-d') . ')'; }
If you store objects of multiple types in the same table, you can tell advancedExecute()
method where to look for the class name. This example assumes that we store the full class name in type
field:
<?php use ActiveCollab\DatabaseConnection\Connection\MysqliConnection; use DateTime; use MySQLi; use RuntimeException; $database_link = new MySQLi('localhost', 'root', '', 'activecollab_database_connection_test'); if ($database_link->connect_error) { throw new RuntimeException('Failed to connect to database. MySQL said: ' . $database_link->connect_error); } $connection = new MysqliConnection($database_link); foreach ($this->connection->advancedExecute('SELECT * FROM `writers` ORDER BY `id`', null, ConnectionInterface::LOAD_ALL_ROWS, ConnectionInterface::RETURN_OBJECT_BY_FIELD, 'type') as $writer) { print '#' . $writer->getId() . ' ' . $writer->getName() . ' (' . $writer->getBirthday()->format('Y-m-d') . ')'; }
Object Hydration + DIC
If objects that you are hydrating implement ActiveCollab\ContainerAccess\ContainerAccessInterface
interface, and you pass an Interop\Container\ContainerInterface
instance to advancedExecute()
method, container will be provided to all instances that result hydrates:
use ActiveCollab\DatabaseConnection\Test\Fixture\Container; use ActiveCollab\DatabaseConnection\Test\Fixture\WriterWithContainer; use Psr\Container\ContainerInterface; $container = new Container([ 'dependency' => 'it works!' ]); $result = $this->connection->advancedExecute('SELECT * FROM `writers` ORDER BY `id`', null, ConnectionInterface::LOAD_ALL_ROWS, ConnectionInterface::RETURN_OBJECT_BY_CLASS, WriterWithContainer::class, null, $container); foreach ($result as $writer) { print $writer->dependency . "\n"; // Prints 'it works!' }
Helper Methods
Database connection instance offers a couple of methods that are helpful in everyday development:
databaseExists($database_name)
- ReturnTRUE
if database with a given name exists,userExists($user_name)
- ReturnTRUE
if user with a given name exists.getTableNames()
- Return names of all tables that are in a database taht we are connected to,tableExists($table_name)
- ReturnTRUE
if table with a given name exists in a database that we are connected to,dropTable($table_name)
- Drop a table.
Tests
To test a library you need to manually create a database:
CREATE DATABASE activecollab_database_connection_test DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
Then from a project root execute following command:
phpunit
To do
- Use prepared statements for all queries that have extra arguments,
- Enable library to use two database connections, one for writes, and the other for reads,
- Properly handle MySQL has gone away errors and deadlocks (stubbed).
- Consider support MySQL Native Driver, and features that it enables