Database

To communicate with the database, we use Doctrine DBAL. We provide an active database connection via the DI Container.

For simple queries you can use auxiliary methods provided by the Doctrine\DBAL\Connection class:

• fetchAll(string $query)
  Executes the SQL query and returns a multidimensional array with all found entries.
• insert(string $table, array $data)
  Creates a new table entry.
• update(string $table, array $data, array $identifier)
  Updates an existing table entry.
• delete(string $table, array $identifier)
  Deletes all entries from the table that are found using the $identifier.

If you want to use a value as a column name that also matches a MySQL internal keyword, the column name must be masked. For example, if you want the column of a table to have the name key, the query must contain `key`. For this purpose the method quoteIdentifier(string $identifier) is available in the Doctrine\DBAL\Connection class.

Complex queries can be mapped using the Query Builder. To create a query builder, you need to call the createQueryBuilder method, which is available in the Doctrine\DBAL\Connection class. Note that the query builder does not reset its internal state after it has been executed. This means that in most cases it makes sense to create a new instance of the query builder for each query.

The following example shows how to add the Doctrine DBAL connection to your class using a Service Provider:

namespace GXModules\<Vendor>\<Module>;

use Doctrine\DBAL\Connection;
use Gambio\Core\Application\DependencyInjection\AbstractModuleServiceProvider;
use GXModules\<Vendor>\<Module>\SampleClass;

/**
 * Class MyServiceProvider
 * @package GXModules\<Vendor>\<Module>
 */
class MyServiceProvider extends AbstractModuleServiceProvider
{
    /**
     * @inheritDoc
     */
    public function provides(): array
    {
        return [
            SampleClass::class,
        ];
    }

    /**
     * @inheritDoc
     */
    public function register(): void
    {
        $this->application->registerShared(SampleClass::class)
             ->addArgument(Connection::class);
    }
}