Database

This chapter talks about the basics of configuring the database connection, creating tables, running database scripts from the extensions and manually building database queries.

Note To map your application data to database tables in a comfortable way, the recommended way is the Pagekit Object-relational mapper (ORM) which is described in its own chapter.

Configuration

Database credentials are stored in config.php. Pagekit supports mysqland sqlite.

'database' => [
    'connections' => [
      'mysql' => [
        'host' => 'localhost',
        'user' => 'root',
        'password' => 'PASSWORD',
        'dbname' => 'DATABASE',
        'prefix' => 'PREFIX_',
      ],
    ],
  ],
  ...

Working with database prefixes

All table names include the prefix of your Pagekit installation. To dynamically address tables in the backend, use the table name with the @ symbol as a placeholder for the prefix. As a convention you should start the table name with your extension name, e.g. options table for the foobar extension: @foobar_option

Database utility

You can manage your database schema using the database service utility (see the following examples).

$util = $this['db']->getUtility();

Check if tables exists

if ($util->tablesExist(['@table1', '@table2'])) {
  // tables exists
}

Create table

Use Utility::createTable($table, \Closure $callback) to create a table, the first parameter passed to the callback will be a Doctrine\DBAL\Schema\Table instance.

$util->createTable('@foobar_option', function($table) {
    $table->addColumn('id', 'integer', ['unsigned' => true, 'length' => 10, 'autoincrement' => true]);
    $table->addColumn('name', 'string', ['length' => 64, 'default' => '']);
    $table->addColumn('value', 'text');
    $table->addColumn('autoload', 'boolean', ['default' => false]);
    $table->setPrimaryKey(['id']);
    $table->addUniqueIndex(['name'], 'OPTION_NAME');
});

The $table object is an instance of \Doctrine\DBAL\Schema\Table. You can find its class reference in the official Doctrine documentation.

When creating a column using addColumn, you might want to look at the available data types and the availabe column options from the Doctrine documentation as well.

Creating a table is commonly done in the install hook of the scripts.php inside your extension. Read more about Migrations in the next section.

Migrations

Database migrations are defined in the 'updates' section of the scripts.php in your extension. A full example for the scripts.php can be found in the Hello extension. Remember to link that file from your composer.json so that it is actually executed:

    "extra": {
       "scripts": "scripts.php"
    },

Within the scripts.php, you can hook into different events of the extension lifecycle.

```
 /*
 * Runs all updates that are newer than the current version.
 *
 */
'updates' => [
    '0.5.0' => function ($app) {
        // executed when upgrading from a version older than 0.5.0
    },
    '0.9.0' => function ($app) {
        // executed when upgrading from a version older than 0.9.0
    },
],
```

Alter an existing table

To alter an existing table, use the existing tools of the underlying Doctrine DBAL. To add columns to an existing table, you can include the following snippets in one of the updates version hooks of your extension's scripts.php.

use Doctrine\DBAL\Schema\Comparator;
// ...
$util    = App::db()->getUtility();
$manager = $util->getSchemaManager();
if ($util->tableExists('@my_table')) {
    $tableOld = $util->getTable('@my_table');
    $table = clone $tableOld;
    $table->addColumn('title', 'string', ['length' => 255]);
    $comparator = new Comparator;
    $manager->alterTable($comparator->diffTable($tableOld, $table));
}

The $table object is an instance of \Doctrine\DBAL\Schema\Table. You can find its class reference in the official Doctrine documentation.

Queries

There are several ways of accessing the database. Pagekit offers an abstracting on the underlying MySQL or SQLite, so there is no need to use PDO or similar mechanisms.

1. Query builder

The QueryBuilder allows for a more comfortable way of creating queries.

Example:

$result = Application::db()->createQueryBuilder()->select('*')->from('@blog_post')->where('id = :id', ['id' => 1])->execute()->fetchAll();

Get a query builder object

use Pagekit\Application;
// ...
$query = Application::db()->createQueryBuilder();

Basic selects and conditions

Example:

// create query
$query = Application::db()->createQueryBuilder();
// fetch title and content of all blog posts that do not have any comments
$comments = $query
    ->select(['title', 'content'])
    ->from('@blog_post')
    ->where('comment_count = ?', [0])
    ->get();

Query execution

Aggregate functions

Example:

// create query
$query = $query = Application::db()->createQueryBuilder();
// determine total number of blog comments
$count = $query
    ->select(['comment_count'])
    ->from('@blog_post')
    ->sum('comment_count');

Advanced query methods

Joins

2. ORM Queries

When you have set up ORM in your extension, you can create very readable queries using your model class.

Example:

$result = Role::where(['id <> ?'], [Role::ROLE_ANONYMOUS])->orderBy('priority')->get();

The following methods are available (defined in the ModelTrait).

ORM Query Builder: Additional methods

Example:

$comments = Comment::query()->related(['post' => function ($query) {
    return $query->related('comments');
}])->get();

3. Raw queries

The plainest way to query the database is by sending raw queries to the database. This is basically just a wrapper around PDO.

$result = Application::db()->executeQuery('select * from @blog_post')->fetchAll();
$result = Application::db()->executeQuery('select * from @blog_post WHERE id = :id', ['id' => 1])->fetchAll();

Insert

Inserting data in the database can be done using the database connection instance that you can fetch via Application::db() (remember to use Pagekit\Application; at the top of your file).

Use the method insert($tableExpression, array $data, array $types = array())

Example:

Application::db()->insert('@system_page', [
    'title' => 'Home',
    'content' => "<p>Hello World</p>",
    'data' => '{"title":true}'
]);

When using ORM, you just need to create a new model instance and call the save() method.

ORM

With the Object-relational mapping (ORM) in Pagekit, you can bind a Model class to a database table. While this takes a few more lines to setup than the QueryBuilder, the ORM takes a lot of manual work out of your hands. Using ORM is the recommended way of managing how you store and retrieve your application data to and from the database. Read more about the Pagekit ORM.