Webfiori Database Abstraction Layer

Database abstraction layer of WebFiori framework.

Content

• Supported PHP Versions
• Supported Databases
• Features
• Installation
• Usage
  • Connecting to Database
  • Running Basic SQL Queries
    • Insert Query
    • Select Query
    • Update Query
    • Delete Query
  • Building Database Structure
    • Creating Table Blueprint
    • Seeding Structure to Database
  • Creating Entity Classes and Using Them
    • Creating an Entity Class
    • Using Entity Class
  • Database Migrations
  • Database Seeders
  • Performance Monitoring
  • Transactions

Supported PHP Versions

Build Status

Supported Databases

• MySQL
• MSSQL

Features

• Building your database structure within PHP
• Fast and easy to use query builder
• Database abstraction which makes it easy to migrate your system to different DBMS
• Database migrations and seeders
• Performance monitoring and query analysis
• Entity mapping for object-relational mapping
• Transaction support with automatic rollback

Installation

To install the library using composer, add following dependency to composer.json: "webfiori/database":"*"

Usage

Connecting to Database

Connecting to a database is simple. First step is to define database connection information using the class ConnectionInfo. Later, the instance can be used to establish a connection to the database using the class Database.

use WebFiori\Database\ConnectionInfo;
use WebFiori\Database\Database;

// This assumes that MySQL is installed on localhost
// and root password is set to '123456' 
// and there is a schema with name 'testing_db'
$connection = new ConnectionInfo('mysql', 'root', '123456', 'testing_db');
$database = new Database($connection);

Running Basic SQL Queries

Most common SQL queries that will be executed in any relational DBMS are insert, select, update, and delete. Following examples shows how the 4 types can be constructed.

For every query, the table that the query will be executed on must be specified. To specify the table, use the method Database::table(string $tblName). The method will return an instance of the class AbstractQuery. The class AbstractQuery has many methods which are used to further build the query. Commonly used methods include the following:

• AbstractQuery::insert(array $cols): Construct an insert query.
• AbstractQuery::select(array $cols): Construct a select query.
• AbstractQuery::update(array $cols): Construct an update query.
• AbstractQuery::delete(): Construct a delete query.
• AbstractQuery::where($col, $val): Adds a condition to the query.

After building the query, the method AbstractQuery::execute() can be called to execute the query. If the query is a select query, the method will return an instance of the class ResultSet. The instance can be used to traverse the records that was returned by the DBMS.

Insert Query

Insert query is used to add records to the database. To execute an insert query, use the method AbstractQuery::insert(array $cols). The method accepts one parameter. The parameter is an associative array. The indices of the array are columns names and the values of the indices are the values that will be inserted.

$connection = new ConnectionInfo('mysql', 'root', '123456', 'testing_db');
$database = new Database($connection);

$database->table('posts')->insert([
    'title' => 'Super New Post',
    'author' => 'Me'
])->execute();

Select Query

A select query is used to fetch database records and use them in application logic. To execute a select query, use the method AbstractQuery::select(array $cols). The method accepts one optional parameter. The parameter is an array that holds the names of the columns that will be selected. In this case, the method AbstractQuery::execute() will return an object of type ResultSet. The result set will contain raw fetched records as big array that holds the actual records. Each record is stored as an associative array.

$connection = new ConnectionInfo('mysql', 'root', '123456', 'testing_db');
$database = new Database($connection);

// This assumes that we have a table called 'posts' in the database.
$resultSet = $database->table('posts')->select()->execute();

foreach ($resultSet as $record) {
    echo $record['title'];
}

It is possible to add a condition to the select query using the method AbstractQuery::where().

$connection = new ConnectionInfo('mysql', 'root', '123456', 'testing_db');
$database = new Database($connection);

// This assumes that we have a table called 'posts' in the database.
$resultSet = $database->table('posts')
                      ->select()
                      ->where('author', 'Ibrahim')
                      ->execute();

foreach ($resultSet as $record) {
    echo $record['title'];
}

Update Query

Update query is used to update a single record or multiple records. To execute an update query, use the method AbstractQuery::update(array $cols). The method accepts one parameter. The parameter is an associative array. The indices of the array are columns names and the values of the indices are the updated values. Usually, for any update query, a where condition will be included. To include a where condition, the method AbstractQuery::where() can be used.

$connection = new ConnectionInfo('mysql', 'root', '123456', 'testing_db');
$database = new Database($connection);

$database->table('posts')->update([
    'title' => 'Super New Post By Ibrahim',
])->where('author', 'Ibrahim')
  ->andWhere('created-on', '2023-03-24')
  ->execute();

Delete Query

This query is used to delete specific record from the database. To execute delete query, use the method AbstractQuery::delete(). A where condition should be included to delete specific record. To include a where condition, the method AbstractQuery::where() can be used.

$connection = new ConnectionInfo('mysql', 'root', '123456', 'testing_db');
$database = new Database($connection);

$database->table('posts')->delete()->where('author', 'Ibrahim')->execute();

Building Database Structure

One of the features of the library is the ability to define database structure in the source code and later, seed the created structure to create database tables. The blueprint of tables are represented by the class Table. The main aim of the blueprint is to make sure that data types in database are represented correctly in the source code.

Creating Table Blueprint

Each blueprint must have following attributes defined:

• Name of the blueprint (database table name).
• Columns and their properties such as data type.
• Any relations with other tables.

The method Database::createBlueprint() is used to create a table based on connected DBMS. The method will return an instance of the class Table which can be used to further customize the blueprint.

use WebFiori\Database\ColOption;
use WebFiori\Database\DataType;

$database->createBlueprint('users_information')->addColumns([
    'id' => [
        ColOption::TYPE => DataType::INT,
        ColOption::SIZE => 5,
        ColOption::PRIMARY => true,
        ColOption::AUTO_INCREMENT => true
    ],
    'first-name' => [
        ColOption::TYPE => DataType::VARCHAR,
        ColOption::SIZE => 15
    ],
    'last-name' => [
        ColOption::TYPE => DataType::VARCHAR,
        ColOption::SIZE => 15
    ],
    'email' => [
        ColOption::TYPE => DataType::VARCHAR,
        ColOption::SIZE => 128
    ]
]);

Seeding Structure to Database

After creating all blueprints, a query must be structured and executed to create database tables. Building the query can be performed using the method Database::createTables(). After calling this method, the method Database::execute() must be called to create all database tables.

// Build the query
$database->createTables();

// Execute
$database->execute();

Creating Entity Classes and Using Them

Entity classes are classes which are based on blueprints (or tables). They can be used to map records of tables to objects. Every blueprint will have an instance of the class EntityMapper which can be used to create an entity class.

Entity classes that are generated using the class EntityMapper are special. They will have one static method with name map() which can automatically map a record to an instance of the entity.

Creating an Entity Class

First step in creating an entity is to have the blueprint at which the entity will be based on. From the blueprint, an instance of the class EntityMapper is generated. After having the instance, the properties of the entity is set such as its name, namespace and where it will be created. Finally, the method EntityMapper::create() can be invoked to write the source code of the class.

$blueprint = $database->getTable('users_information');

// Get entity mapper
$entityMapper = $blueprint->getEntityMapper();

// Set properties of the entity
$entityMapper->setEntityName('UserInformation');
$entityMapper->setNamespace('');
$entityMapper->setPath(__DIR__);

// Create the entity. The output will be the class 'UserInformation'.
$entityMapper->create();

Using Entity Class

Entity class can be used to map a record to an object. Each entity will have a special method called map(). The method accepts a single parameter which is an associative array that represents fetched record.

The result set instance has one of array methods which is called map($callback) This method acts exactly as the function array_map($callback, $array). The return value of the method is another result set with mapped records.

$resultSet = $database->table('users_information')
        ->select()
        ->execute();

$mappedSet = $resultSet->map(function (array $record) {
    return UserInformation::map($record);
});

foreach ($mappedSet as $record) {
    // $record is an object of type UserInformation
    echo $record->getFirstName() . ' ' . $record->getLastName() . "\n";
}

Database Migrations

Migrations allow you to version control your database schema changes. Each migration represents a specific change to your database structure.

use WebFiori\Database\Schema\AbstractMigration;
use WebFiori\Database\Database;

class CreateUsersTable extends AbstractMigration {
    
    public function up(Database $db): void {
        $db->createBlueprint('users')->addColumns([
            'id' => [
                ColOption::TYPE => DataType::INT,
                ColOption::PRIMARY => true,
                ColOption::AUTO_INCREMENT => true
            ],
            'name' => [
                ColOption::TYPE => DataType::VARCHAR,
                ColOption::SIZE => 100
            ],
            'email' => [
                ColOption::TYPE => DataType::VARCHAR,
                ColOption::SIZE => 150
            ]
        ]);
        
        $db->createTables();
        $db->execute();
        
    }
    
    public function down(Database $db): void {
        $db->setQuery("DROP TABLE users");
        $db->execute();
        
    }
}

Database Seeders

Seeders allow you to populate your database with sample or default data.

use WebFiori\Database\Schema\AbstractSeeder;
use WebFiori\Database\Database;

class UsersSeeder extends AbstractSeeder {
    
    public function run(Database $db): bool {
        $db->table('users')->insert([
            'name' => 'Administrator',
            'email' => 'admin@example.com'
        ])->execute();
        
        $db->table('users')->insert([
            'name' => 'John Doe',
            'email' => 'john@example.com'
        ])->execute();
        
    }
}

Performance Monitoring

The library includes built-in performance monitoring to help you identify slow queries and optimize database performance.

use WebFiori\Database\Performance\PerformanceOption;
use WebFiori\Database\Performance\PerformanceAnalyzer;

// Configure performance monitoring
$database->setPerformanceConfig([
    PerformanceOption::ENABLED => true,
    PerformanceOption::SLOW_QUERY_THRESHOLD => 50, // 50ms threshold
    PerformanceOption::SAMPLING_RATE => 1.0        // Monitor all queries
]);

// Execute some queries
$database->table('users')->select()->execute();
$database->table('posts')->select()->where('status', 'published')->execute();

// Analyze performance
$analyzer = $database->getPerformanceMonitor()->getAnalyzer();

echo "Total queries: " . $analyzer->getQueryCount() . "\n";
echo "Average execution time: " . $analyzer->getAverageTime() . "ms\n";
echo "Performance score: " . $analyzer->getScore() . "\n";
echo "Slow queries: " . $analyzer->getSlowQueryCount() . "\n";

// Check if performance needs improvement
if ($analyzer->getScore() === PerformanceAnalyzer::SCORE_NEEDS_IMPROVEMENT) {
    $slowQueries = $analyzer->getSlowQueries();
    foreach ($slowQueries as $metric) {
        echo "Slow query: " . $metric->getQuery() . " (" . $metric->getExecutionTimeMs() . "ms)\n";
    }
}

Transactions

Database transactions ensure that multiple operations are executed as a single unit of work. If any operation fails, all operations are rolled back.

$database->transaction(function (Database $db) {
    // Insert user
    $db->table('users')->insert([
        'name' => 'John Doe',
        'email' => 'john@example.com'
    ])->execute();
    
    // Insert user profile
    $db->table('user_profiles')->insert([
        'user_id' => $db->getLastInsertId(),
        'bio' => 'Software Developer'
    ])->execute();
    
    // If any query fails, the entire transaction is rolled back
});

You can also pass additional parameters to the transaction closure:

$userData = ['name' => 'Jane Doe', 'email' => 'jane@example.com'];

$database->transaction(function (Database $db, array $user) {
    $db->table('users')->insert($user)->execute();
    
    $db->table('user_profiles')->insert([
        'user_id' => $db->getLastInsertId(),
        'created_at' => date('Y-m-d H:i:s')
    ])->execute();
    
}, [$userData]);