Database

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.

Database credentials are stored in . Pagekit supports mysqland sqlite.

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();

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('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 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 in the next section.

Migrations

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

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

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 in the official Doctrine documentation.

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.

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:

Advanced query methods

Joins

Example:

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

The following methods are available (defined in the ).

ORM Query Builder: Additional methods

Example:

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

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>",
]);

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

ORM