Download the PHP package activecollab/databaseconnection without Composer

DatabaseConnection Library

Purpose of this library is not to abstract the database, but to make work with MySQLi connections a bit easier. Features:

1. Results that can be easily iterated,
2. Results can be arrays of rows, or objects, loaded by a known class name, or by a class name read from the row field,
3. 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:

Selecting Records

To run SELECT queries from arguments, instead of writing your own SELECT query, use the following methods:

1. select() - maps to execute(),
2. selectFirstRow() - maps to executeFirstRow(),
3. selectFirstColumn() - maps to executeFirstColumn(),
4. selectFirstCell() - maps to executeFirstCell().

All methods accept following arguments:

1. $table_name - Name of the table. This is the only required argument,
2. $fields - List of fields that need to be fetched. Provide a string (single field), array of fields, or NULL (all fields),
3. $conditions - Query conditions. Provide a string, an array (pattern + arguments), or NULL,
4. $order_by_fields - List of fields that we want to order records by. Provide a string (single field), array of fields, or NULL (all fields).

Counting Records

DatabaseConnection lets you easily count records from the table:

By default, it returns number of all records from the table. To filter, you can provide $conditions argument:

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 *):

Running Queries from File

In order to run all queries from a file, use executeFromFile() method:

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:

First three arguments are required (MySQL hostname, username and password). Additionally, this function accepts:

1. Name of the database that needs to be selected. When not specified, database will not be selected,
2. 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:

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):

Casting

Unless specified differently, following conventions apply:

1. id and row_count fields are always cast to integers,
2. Fields with name ending with _id are cast to integers,
3. Fields with name starting with is_, has_, had_, was_, were_ and have_ are cast to boolean,
4. 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:

1. Point
2. Multi Point
3. Line String
4. Multi Line String
5. Linear Ring
6. Polygon
7. Multi Polygon
8. 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:

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:

You can get a list of hydrated objects by passing the name of the class to advancedExecute() method:

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:

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:

Helper Methods

Database connection instance offers a couple of methods that are helpful in everyday development:

1. databaseExists($database_name) - Return TRUE if database with a given name exists,
2. userExists($user_name) - Return TRUE if user with a given name exists.
3. getTableNames() - Return names of all tables that are in a database taht we are connected to,
4. tableExists($table_name) - Return TRUE if table with a given name exists in a database that we are connected to,
5. dropTable($table_name) - Drop a table.

Tests

To test a library you need to manually create a database:

Then from a project root execute following command:

To do

1. Use prepared statements for all queries that have extra arguments,
2. Enable library to use two database connections, one for writes, and the other for reads,
3. Properly handle MySQL has gone away errors and deadlocks (stubbed).
4. Consider support MySQL Native Driver, and features that it enables