Database connectivity changes in WHMCS 6.0

The mysql PHP extension was deprecated when PHP 5.5 was released in June 2013<ref>http://php.net/ChangeLog-5.php#5.5.0</ref> and is scheduled to be removed in PHP 7<ref>https://wiki.php.net/rfc/remove_deprecated_functionality_in_php7</ref>. WHMCS 6.0 introduces a new database connection and library to ensure compatibility with modern PHP environments and best practices.

New functionality

WHMCS 6.0 incorporates the Laravel framework 4.1's database component (WHMCS 7.0 incorporates 5.2). This library includes a Database Abstraction Layer (DBAL) called "Capsule" and an Object Relational Mapping (ORM) library called "Eloquent". The new DBAL is based on the PHP Data Objects (PDO MySQL) extension and uses WHMCS's existing configuration.php file. No configuration file changes are required to use the new database connection.

The Capsule DBAL component introduces two libraries to WHMCS, a query manager for running database queries and a schema manager for an abstracted API to table management. Capsule's underlying PDO connection is also available for advanced database usage. Capsule has three static methods to get to these components:

Capsule::table(string $tableName): Access the query manager for the given table.
Capsule::schema(): Access the schema manager for the WHMCS database.
Capsule::connection(): Access the connection manager to interact with the underlying database connection.

Note: WHMCS 6.0 makes two connections to the database. One connection is made through the legacy mysql extension to handle existing hooks, modules, and other customizations. The other connection is made by PDO to drive new DBAL and model based functionality in the program.

Deprecated functionality

The current SQL Helper Functions are present in WHMCS 6.0 and above, but are now deprecated and may be removed in a later version of the product:

select_query()
update_query()
insert_query()
full_query()

The mysql extension driven database connection is now deprecated and may be discontinued in a later version of the product. WHMCS encourages all third party developers to use the Capsule DBAL and PDO connection for all new database interaction.

Note: WHMCS 7.0 incorporates the Laravel framework's 5.2 database component, which deprecates as well as removes many features present in 4.1 Please see the upgrade guide for more information:

Laravel 5.2 Upgrade Guide

Using Capsule

Declare an alias to Laravel's database manager in your project file's use block to access Capsule:

<?php

use WHMCS\Database\Capsule;

// Run queries or modify tables as you like.

The Query Manager

Please see Laravel's query documentation for more information.

The Capsule::table(string $tableName) method provides access to the query manager. Declare it with the name of the table you wish to query as it's first parameter to interact with that table. The query manager has a wide range of functionality to perform advanced select, join, insert, update, and delete statements. Capsule's select calls return rows as stdClass objects.

Capsule escapes all input, so it is not necessary to add escaping slashes to variables passed to these methods.

All of Capsule's methods throw an exception on failure. Please place Capusle calls in try/catch blocks for graceful error handling and to avoid potential fatal errors in your hook, module, or other customization.

<?php

use WHMCS\Database\Capsule;

// Print all client first names using a simple select.

/** @var stdClass $client */
foreach (Capsule::table('tblclients')->get() as $client) {
    echo $client->firstname . PHP_EOL;
}

// Rename all clients named "John Deo" to "John Doe" using an update statement. 
try {
    $updatedUserCount = Capsule::table('tblclients')
        ->where('firstname', 'John')
        ->where('lastname', 'Deo')
        ->update(
            [
                'lastname' => 'Doe',
            ]
        );

    echo "Fixed {$updatedUserCount} misspelled last names.";
} catch (\Exception $e) {
    echo "I couldn't update client names. {$e->getMessage()}";
}

The Schema Manager

Use the Capsule::schema() method to access the schema manager to modify table schema if necessary. The schema manager has support for creating, dropping and truncating tables and for modifying columns, indexes, and keys.

Note: WHMCS does not recommend changing default table schema as that can affect product functionality.

<?php

use WHMCS\Database\Capsule;

// Create a new table.
try {
    Capsule::schema()->create(
        'my_table',
        function ($table) {
            /** @var \Illuminate\Database\Schema\Blueprint $table */
            $table->increments('id');
            $table->string('name');
            $table->integer('serial_number');
            $table->boolean('is_required');
            $table->timestamps();
        }
    );
} catch (\Exception $e) {
    echo "Unable to create my_table: {$e->getMessage()}";
}

The Connection Manager

The Capsule::connection() method provides low-level access to the database connection itself. Use it to initiate transactions with automatic commit and rollback or to access the underlying PDO connection to perform manual database queries outside the DBAL. The connection manager also has methods to retrieve query and schema managers.

<?php

use WHMCS\Database\Capsule;

// Perform potentially risky queries in a transaction for easy rollback.
try {
    Capsule::connection()->transaction(
        function ($connectionManager)
        {
            /** @var \Illuminate\Database\Connection $connectionManager */
            $connectionManager->table('my_table')->insert(
                [
                    'name' => $_POST['name'],
                    'serial_number' => $_POST['serialNumber'],
                    'is_required' => (int)(bool) $_POST['isRequired'],
                ]
            );
        }
    );
} catch (\Exception $e) {
    echo "Uh oh! Inserting didn't work, but I was able to rollback. {$e->getMessage()}";
}

Getting to PDO

Use the connection manager's getPdo() method to retrieve the underlying PDO connection instance. Use the PDO connection to perform manual queries and advanced database usage.

<?php

use WHMCS\Database\Capsule;

// Perform potentially risky queries in a transaction for easy rollback.    
$pdo = Capsule::connection()->getPdo();
$pdo->beginTransaction();

try {
    $statement = $pdo->prepare(
        'insert into my_table (name, serial_number, is_required) values (:name, :serialNumber, :isRequired)'
    );

    $statement->execute(
        [
            ':name' => $_POST['name'],
            ':serialNumber' => $_POST['serialNumber'],
            ':isRequired' => (bool) $_POST['isRequired'],
        ]
    );

    $pdo->commit();
} catch (\Exception $e) {
    echo "Uh oh! {$e->getMessage()}";
    $pdo->rollBack();
}

Troubleshooting

Exceptions

All Capsule methods throw an exception on failure. Catch these exceptions and analyze their messages and stack traces to help determine the nature of the failure. WHMCS recommends placing all database interactivity in try/catch blocks for graceful error handling.

The Capsule Query Log

The connection manager's getQueryLog() method returns an array of all queries made during the life of the page request. Queries are stored in the log as an array containing the query run, the parameter bindings passed to the query, and the time it took for the query to execute, measured in milliseconds.

<?php

use Illuminate\Database\Capsule\Manager as Capsule;

// Loop through each Capsule query made during the page request.
foreach (Capsule::connection()->getQueryLog() as $query) {
    echo "Query: {$query['query']}" . PHP_EOL;
    echo "Execution Time: {$query['time']}ms" . PHP_EOL;
    echo "Parameters: " . PHP_EOL;

    foreach ($query['bindings'] as $key => $value) {
        echo "{$key} => {$value}" . PHP_EOL;
    }
}

The WHMCS Activity Log

All uncaught PDO-based query failures, including those made by Capsule and manual PDO queries, are written to to the WHMCS activity log. View the system activity log to view the details of these failed queries.

External Links

References

Documentation

Interacting With The Database

Contents