Skip to content

database.md

Latest commit

 

History

History
694 lines (527 loc) · 26.8 KB

database.md

File metadata and controls

694 lines (527 loc) · 26.8 KB

Database

In this page:

Introduction

One of the important features of any web application is to have a simple-unified interface at which the developer can use to access application database. WebFiori framework has an abstract layer that provides the developer with all needed tools to create databases and performs queries on them. Currently, the abstraction layer supports MySQL and MSSQL database but there are plans to support more in the future.

Note: It is possible to connect to any database using PDO driver of PHP. The database layer helps in defining your database in easy way and also it helps in making the process of building SQL queries much simpler task.

The Idea

Each table in your database is represented by the class Table. Every table consist of columns and every column is represented by the class Column. Each table must be part of a schema (or database). The database is represented by the class Database. WebFiori framework has the class DB which adds extra functionality to the class Database. The database instance is used to connect to database and run queries on it.

In case of MySQL database, database tables represented by the class MySQLTable and table columns represented by the class MySQLColumn. In case of MSSQL, database tables represented by the class MSSQLTable and table columns represented by the class MSSQLColumn.

Initializing your Database

The following set of steps will show you how to create your database structure and connect to the database and execute queries. Overall, there are 3 steps in the process:

  • Adding connection information.
  • Creating database tables as classes.
  • Creating a class that acts as the database schema and adding tables to it.

Adding Connection Information

Database connections are represented by the class ConnectionInfo. Connection information is stored in the JSON configuration file at [APP_DIR]/Config/app-config.json. It is possible to store multiple connections. There are two ways to add connection information: editing the JSON config file directly or using the command line interface.

Adding connection information manually can be done by editing the app-config.json file. The CLI approach using php webfiori add:db-connection is recommended since connection information will be validated before being stored.

Creating Database Tables

MySQL Database tables represented by the class MySQLTable. Each table in the database must be represented as a sub class of this class. There are two ways at which the developer can create a class that represent a database table. One is a manual way and the other one is to use command line interface.

To create a table class manually, developer have to create new class that extend the class MySQLTable and add columns to it as needed. Assuming that the developer would like to place database tables in the folder App/Database with namespace App\Database. Also, assuming that the developer would like to create a table for keeping contacts information.

The constructor of the class accepts one parameter which is the name of the table as it appears in the database. Let's assume that the name of the table is contacts.

namespace App\Database;

use WebFiori\Database\MySql\MySQLTable;

class ContactsTable extends MySQLTable {
    public function __construct() {
        parent::__construct('contacts');
    }
}

After setting the name of the table, developer can start by adding columns to the table. There is more than one way to add columns to the table. The method MySQLTable::addColumns() can be used to add multiple columns at once. The method accepts an associative array. The indices of the array are columns names and the value of each index is a sub associative array that holds column properties.

namespace App\Database;

use WebFiori\Database\MySql\MySQLTable;
use WebFiori\Database\ColOption;
use WebFiori\Database\DataType;

class ContactsTable extends MySQLTable {
    public function __construct() {
        parent::__construct('contacts');
        
        $this->addColumns([
            'id' => [
                ColOption::TYPE => DataType::INT,
                ColOption::SIZE => 11,
                ColOption::PRIMARY => true,
                ColOption::AUTO_INCREMENT => true
            ],
            'name' => [
                ColOption::TYPE => DataType::VARCHAR,
                ColOption::SIZE => 50,
                ColOption::NULL => false
            ],
            'age' => [
                ColOption::TYPE => DataType::INT,
                ColOption::SIZE => 3,
            ],
            'mobile' => [
                ColOption::TYPE => DataType::VARCHAR,
                ColOption::SIZE => 15,
                ColOption::NULL => true
            ],
            'phone' => [
                ColOption::TYPE => DataType::VARCHAR,
                ColOption::SIZE => 15,
                ColOption::NULL => true
            ],
            'email' => [
                ColOption::TYPE => DataType::VARCHAR,
                ColOption::SIZE => 255,
                ColOption::NULL => true
            ]
        ]);
    }
}

This table will be used to store basic information about contacts. It will act as an interface between the application and the actual database table.

Using Blueprints

For quick table creation without defining a separate class, use the createBlueprint() method:

$db = new Database($connection);

$db->createBlueprint('users')->addColumns([
    'id' => [
        ColOption::TYPE => DataType::INT,
        ColOption::PRIMARY => true,
        ColOption::AUTO_INCREMENT => true
    ],
    'username' => [
        ColOption::TYPE => DataType::VARCHAR,
        ColOption::SIZE => 50
    ],
    'email' => [
        ColOption::TYPE => DataType::VARCHAR,
        ColOption::SIZE => 150
    ]
]);

// Create the table
$db->table('users')->createTable()->execute();

This approach is useful for simple tables or when you don't need a reusable table class.

Using PHP 8 Attributes

You can define tables using PHP 8 attributes for a cleaner, more declarative approach:

namespace App\Infrastructure\Schema;

use WebFiori\Database\Attributes\Column;
use WebFiori\Database\Attributes\Table;
use WebFiori\Database\DataType;

#[Table(name: 'users')]
#[Column(name: 'id', type: DataType::INT, primary: true, autoIncrement: true)]
#[Column(name: 'username', type: DataType::VARCHAR, size: 50)]
#[Column(name: 'email', type: DataType::VARCHAR, size: 150)]
class UserTable {
}

Then build the table using AttributeTableBuilder:

use WebFiori\Database\Attributes\AttributeTableBuilder;

$table = AttributeTableBuilder::build(UserTable::class, 'mysql');
$db->addTable($table);
$db->table('users')->createTable()->execute();

You can also define foreign keys using attributes:

use WebFiori\Database\Attributes\Column;
use WebFiori\Database\Attributes\ForeignKey;
use WebFiori\Database\Attributes\Table;
use WebFiori\Database\DataType;

#[Table(name: 'posts')]
#[Column(name: 'id', type: DataType::INT, primary: true, autoIncrement: true)]
#[Column(name: 'title', type: DataType::VARCHAR, size: 200)]
class PostTable {
    #[Column(name: 'author-id', type: DataType::INT)]
    #[ForeignKey(table: 'users', column: 'id')]
    public int $authorId;
}

Creating Database Class

After creating tables as classes, developer have to add them to an instance of the class Database which represents the actual database instance. WebFiori framework have the class DB which adds extra functionality like the ability to automatically register multiple tables automatically. For this reason, the developer should use the class DB. Assuming that the name of the database class is TestingDatabase.

namespace App\Database;

use WebFiori\Framework\DB;

class TestingDatabase extends DB {
    public function __construct() {
        parent::__construct('connection-00');
    }
}

The constructor of the class accepts one parameter which is the name of the connection that will be used by database instance. After that, database table classes must be registered in order to perform queries on them. To do that, the developer can use the method DB::addTable() for registering single table or the method DB::register() to add multiple tables which belongs to same namespace.

namespace App\Database;

use WebFiori\Framework\DB;
use App\Database\ContactsTable;

class TestingDatabase extends DB {
    public function __construct() {
        parent::__construct('connection-00');
        
        $this->addTable(new ContactsTable());
    }
}

Now that the table is added, we can create an instance of the class TestingDatabase and start building queries as needed.

Database Queries

The library provides a query builder which can be used to build almost any type of query. All query builders extend the class AbstractQuery which acts as a base query builder. It has many methods to support the process of building queries. Note that the class Database acts as an interface for this class. To get the query builder instance, use the method Database::getQueryGenerator().

Insert Record

The method AbstractQuery::insert() is used to build an insert query for MySQL and MSSQL database. The following code sample shows how to use that method to create an insert query in case of MySQL database. It is used in same way in case of MSSQL.

$db = new TestingDatabase();
$db->table('contacts')->insert([
    'name' => 'Ibrahim BinAlshikh',
    'age' => 27,
    'mobile' => '+966554321000',
    'phone' => '+966136543456',
    'email' => 'xyz@example.com'
])->execute();

// insert into `contacts` (`name`, `age`, `mobile`, `phone`, `email`) values ('Ibrahim BinAlshikh', 27, '+966554321000', '+966136543456', 'xyz@example.com');

It is possible to insert multiple records using one insert call as follows:

$db->table('contacts')->insert([
    'cols' => ['name', 'age', 'mobile', 'phone', 'email'],
    'values' => [
        ['Contact 1', 33, '055434323', '0137665765', '123@example.com'],
        ['Contact 2', 22, '056246436', '0138732156', '1234@example.com'],
        ['Contact 3', 48, '051297647', '0136523489', '12345@example.com']
    ]
])->execute();

// insert into `contacts`
// (`name`, `age`, `mobile`, `phone`, `email`)
// values
// ('Contact 1', 33, '055434323', '0137665765', '123@example.com'),
// ('Contact 2', 22, '056246436', '0138732156', '1234@example.com'),
// ('Contact 3', 48, '051297647', '0136523489', '12345@example.com');

Update Record

The method AbstractQuery::update() is used to build update record query for MySQL and MSSQL database. The following code sample shows how to use that method to create an update record query with a condition.

$db = new TestingDatabase();
$db->table('contacts')->update([
    'age' => 44,
    'email' => 'new-email@example.com'
])->where('name', 'Contact 1')->execute();

// update `contacts` set `age` = 44, `email` = 'new-email@example.com' where `contacts`.`name` = 'Contact 1'

Delete Record

The method AbstractQuery::delete() is used to build a delete record query for MySQL and MSSQL database. The following code sample shows how to use that method to create a delete record query with a condition.

$db = new TestingDatabase();
$db->table('contacts')->delete()->where('name', 'Contact 1')->execute();

// delete from `contacts` where `contacts`.`name` = 'Contact 1'

Select

The method AbstractQuery::select() is used to build a select query.

$db = new TestingDatabase();
$db->table('contacts')->select()->execute();

// select * from `contacts`

Note: After building the query, the method Database::execute() or the method AbstractQuery::execute() must be called to run the query on the database.

The method Database::table() is used to specify the table at which the query will be based on. It is possible to select some columns by supplying an array that holds columns that will be selected.

$db = new TestingDatabase();
$db->table('contacts')->select(['name', 'age'])->execute();

// select `name`, `age` from `contacts`

Also, it is possible to give an alias for the column using the following syntax.

$db = new TestingDatabase();
$db->table('contacts')->select([
    'name' => 'full_name', 
    'age' => 'contact_age'
])->execute();

// select `name` as `full_name`, `age` as `contact_age` from `contacts`

Developer can also add a where condition to the query. There are 3 methods which can be used to add a where condition:

$db = new TestingDatabase();
$db->table('contacts')->select()->where('age', 15, '>')
                                ->andWhere('name', 'Ibrahim')->execute();

// select * from `contacts` where `age` > 15 and `name` = 'Ibrahim'

Raw SQL Queries

For complex queries or database-specific features, use the raw() method to execute raw SQL:

$db = new TestingDatabase();

// Simple raw query
$result = $db->raw("SELECT * FROM contacts WHERE age > 25")->execute();

// With parameters (prevents SQL injection)
$result = $db->raw(
    "SELECT * FROM contacts WHERE age > ? AND name LIKE ?",
    [25, '%Ibrahim%']
)->execute();

// Insert with raw SQL
$db->raw(
    "INSERT INTO contacts (name, email) VALUES (?, ?)",
    ['John Doe', 'john@example.com']
)->execute();

// Complex queries
$result = $db->raw("
    SELECT c.name, COUNT(o.id) as order_count
    FROM contacts c
    LEFT JOIN orders o ON c.id = o.contact_id
    GROUP BY c.id
    HAVING order_count > ?
", [5])->execute();

Joins

The library supports different types of joins including inner join, left join, right join, and full outer join. The method AbstractQuery::join() is used to add joins to a query.

$db = new TestingDatabase();
$db->table('contacts')->select([
    'contacts.name',
    'contacts.email',
    'orders.total'
])->join([
    'table' => 'orders',
    'on' => [
        'contacts.id' => 'orders.contact_id'
    ]
])->execute();

// select `contacts`.`name`, `contacts`.`email`, `orders`.`total` 
// from `contacts` inner join `orders` on `contacts`.`id` = `orders`.`contact_id`

You can also specify the join type:

$db->table('contacts')->select()
   ->join([
       'table' => 'orders',
       'type' => 'left',
       'on' => [
           'contacts.id' => 'orders.contact_id'
       ]
   ])->execute();

Unions

The method AbstractQuery::union() can be used to combine results from multiple select queries.

$db = new TestingDatabase();

// First query
$query1 = $db->table('contacts')->select(['name', 'email'])
             ->where('age', 25, '>');

// Second query  
$query2 = $db->table('subscribers')->select(['name', 'email'])
             ->where('active', true);

// Union the queries
$query1->union($query2)->execute();

// (select `name`, `email` from `contacts` where `age` > 25) 
// union 
// (select `name`, `email` from `subscribers` where `active` = 1)

Transactions

Transactions ensure that a group of database operations either all succeed or all fail together. Use the transaction() method for atomic operations:

$db = new TestingDatabase();

$db->transaction(function (Database $db) {
    // Deduct from sender
    $sender = $db->table('accounts')->select()->where('id', 1)->execute()->fetch();
    $db->table('accounts')
       ->update(['balance' => $sender['balance'] - 100])
       ->where('id', 1)
       ->execute();
    
    // Add to receiver
    $receiver = $db->table('accounts')->select()->where('id', 2)->execute()->fetch();
    $db->table('accounts')
       ->update(['balance' => $receiver['balance'] + 100])
       ->where('id', 2)
       ->execute();
    
    // Log the transaction
    $db->table('transfers')->insert([
        'from_account' => 1,
        'to_account' => 2,
        'amount' => 100
    ])->execute();
});

Key features:

  • Automatic commit: If the callback completes without exceptions, changes are committed
  • Automatic rollback: If an exception is thrown, all changes are rolled back
  • Nested transactions: Supported via savepoints

Example with error handling:

try {
    $db->transaction(function (Database $db) {
        $balance = $db->table('accounts')
                      ->select(['balance'])
                      ->where('id', 1)
                      ->execute()
                      ->fetch()['balance'];
        
        if ($balance < 500) {
            throw new Exception('Insufficient funds');
        }
        
        // Proceed with transfer...
    });
    echo "Transfer successful";
} catch (Exception $e) {
    echo "Transfer failed: " . $e->getMessage();
    // All changes have been rolled back automatically
}

Working With Result Set

After building the query, it must be executed on the database. To execute a query, the method AbstractQuery::execute() can be used. Some queries will not return a result but in case of select query, there will be. This section explains how to work with database query results.

Retrieving Records

The execute() method returns a ResultSet object directly for select queries, making it easy to work with results.

$db = new TestingDatabase();
$result = $db->table('contacts')->select()->execute();

foreach($result as $record) {
   //Do something with the record
   echo "Name: " . $record['name'] . ", Email: " . $record['email'] . "\n";
}

You can also get specific information about the result set:

$result = $db->table('contacts')->select()->execute();

echo "Total records: " . $result->getRowsCount() . "\n";
echo "Columns: " . implode(', ', $result->getColsNames()) . "\n";

// Get all rows as array
$allRows = $result->getRows();

Mapping Records to Objects

It is possible to map the records to objects. To achieve this, the developer can use the method ResultSet::setMappingFunction(). This method is used to set a function which can use to manipulate the result set after fetching. The method must return an array that contains the records after mapping.

$db = new TestingDatabase();
$result = $db->table('contacts')->select()->execute();

$result->setMappingFunction(function ($dataset){
    $retVal = [];
    foreach($dataset as $record) {
        $contactObj = new Contact();
        $contactObj->setName($record['name']);
        $contactObj->setAge($record['age']);
        $contactObj->setMobile($record['mobile']);
        $contactObj->setPhone($record['phone']);
        $contactObj->setEmail($record['email']);
        
        $retVal[] = $contactObj;
    }
    return $retVal;
});

foreach($result as $record) {
   //Now the $record is an object of type Contact
   echo "Contact: " . $record->getName() . "\n";
}

Advanced Query Examples

Here are some more advanced examples of database operations:

// Complex where conditions
$result = $db->table('contacts')
             ->select()
             ->where('age', 18, '>=')
             ->andWhere('email', null, 'is not')
             ->orWhere('mobile', null, 'is not')
             ->execute();

// Ordering results
$result = $db->table('contacts')
             ->select()
             ->orderBy(['name' => 'ASC', 'age' => 'DESC'])
             ->execute();

// Limiting results
$result = $db->table('contacts')
             ->select()
             ->limit(10, 20) // LIMIT 20 OFFSET 10
             ->execute();

// Counting records
$result = $db->table('contacts')
             ->select(['count(*)' => 'total_contacts'])
             ->execute();

foreach($result as $row) {
    echo "Total contacts: " . $row['total_contacts'] . "\n";
}

Performance Monitoring

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

Enabling Performance Monitoring

use WebFiori\Database\Performance\PerformanceOption;

$db = new TestingDatabase();

$db->setPerformanceConfig([
    PerformanceOption::ENABLED => true,
    PerformanceOption::SLOW_QUERY_THRESHOLD => 100,  // ms
    PerformanceOption::WARNING_THRESHOLD => 50,      // ms
    PerformanceOption::SAMPLING_RATE => 1.0,         // 100% of queries
    PerformanceOption::MAX_SAMPLES => 1000
]);

Analyzing Performance

use WebFiori\Database\Performance\PerformanceAnalyzer;

// Execute some queries...
$db->table('users')->select()->execute();
$db->table('orders')->select()->where('status', 'pending')->execute();

// Get performance metrics
$analyzer = $db->getPerformanceMonitor()->getAnalyzer();

echo "Total queries: " . $analyzer->getQueryCount() . "\n";
echo "Total time: " . $analyzer->getTotalTime() . " ms\n";
echo "Average time: " . $analyzer->getAverageTime() . " ms\n";
echo "Slow queries: " . $analyzer->getSlowQueryCount() . "\n";
echo "Efficiency: " . $analyzer->getEfficiency() . "%\n";

Identifying Slow Queries

$slowQueries = $analyzer->getSlowQueries();

foreach ($slowQueries as $metric) {
    echo "Query: " . $metric->getQuery() . "\n";
    echo "Time: " . $metric->getExecutionTimeMs() . " ms\n";
    echo "Rows: " . $metric->getRowsAffected() . "\n";
}

Performance Score

The analyzer provides a performance score:

$score = $analyzer->getScore();

switch ($score) {
    case PerformanceAnalyzer::SCORE_EXCELLENT:
        echo "Excellent performance!";
        break;
    case PerformanceAnalyzer::SCORE_GOOD:
        echo "Good performance";
        break;
    case PerformanceAnalyzer::SCORE_NEEDS_IMPROVEMENT:
        echo "Consider optimizing slow queries";
        break;
}

Command Line Utilities

WebFiori framework provides extra commands using CLI which are related to database management. The commands can be used to automate some of the repetitive tasks such as creating new database table. In this section, you will find a summary about the available commands.

Adding Connection Using Command Line Interface

This way of adding database connections is recommended since connection information will be first validated before stored. To add new connection, simply run the command php webfiori add:db-connection. The following image shows the whole process of adding the connection using CLI.

Add connection command.

Creating Database Table

It is recommended to use command line interface in creating table classes. By using CLI, you only have to give database table properties as inputs and the class will be created automatically for you. To create a new database table class, simply run the command php webfiori create:table.

Add database table command.

Initializing Database Table

The command php webfiori create:table can be also used to initialize the table in database by selecting another option.

Initialize database table command.

Initialize database table command.

Related Articles