Persistence / ORM

n2n Persistence forms the interface to the database and offers a comprehensive ORM (object-relational mapping) API, which almost completely adopts specification 2.1 of the JPA (Java Persistence API). An ORM offers you an object-oriented abstraction layer of the database. The basis of all database connections in n2n is PDO (PHP Data Objects).

Persistence Unit​

In app.ini under the [database] group you can register database connections (Persistence Units) for your application. The schema for the properties of a connection is {persistence_unit_name}.{property}.

[database : development]

default.dsn_uri = "mysql:server=localhost;dbname=testdb"
default.user = "root"
default.password = ""
default.transaction_isolation_level = "SERIALIZABLE"
default.dialect = "n2n\persistence\meta\dialect\mysql\MysqlDialect"

[database : live]

default.dsn_uri = "mysql:server=localhost;dbname=livedb"
default.user = "liveuser"
default.password = "livepw"
default.transaction_isolation_level = "SERIALIZABLE"
default.dialect = "n2n\persistence\meta\dialect\mysql\MysqlDialect"

In this example we register a Persistence Unit with the name "default" for the development and live mode. The property {persistence_unit_name}.dsn_uri corresponds to the parameter $dsn of PDO::__construct(). Via {persistence_unit_name}.transaction_isolation_level you select the isolation level that should apply to transactions of this Persistence Unit. The possible values are "READ UNCOMMITTED", "READ COMMITTED", "REPEATABLE READ" and "SERIALIZABLE". The default value is "SERIALIZABLE".

With {persistence_unit_name}.dialect you define the Dialect that matches the database. The Dialect forms an abstraction layer via which meta information about the database can be queried in a uniform way. This is needed both by the ORM API and the Meta API.

n2n provides the following implementations of Dialect by default.

Requesting a PDO Object​

As already mentioned, database connections in n2n are realized via PDO. In magical methods you can have an n2n\persistence\Pdo object of the Persistence Unit "default" passed to you.

#[Inject]
private PdoPool $pdoPool;

You must request n2n\persistence\Pdo objects of other Persistence Units via the n2n\persistence\PdoPool.

function examplePdo(): Pdo {
    return $this->pdoPool->getPdo('example');
}

In this example we request an n2n\persistence\Pdo object of the Persistence Unit with the name "db2".

n2n\persistence\Pdo extends PDO and offers additional methods.

Entity​

An Entity is a PHP class that is mapped to a single table in a relational database. Instances of this class correspond to the rows of the table and the properties to the columns. Entities are the core of the ORM and usually form the business logic of your application.

namespace atusch\bo;

use n2n\reflection\ObjectAdapter;

class Article extends ObjectAdapter {
    const TYPE_NEWS = 'news';
    const TYPE_COMMENT = 'comment';

    public int $id;
    public string $title;
    public string $text;
    public string $type = self::TYPE_NEWS;
    public bool $online = false;

    public function __construct($title) {
        $this->title = $title;
    }
}

All properties are recognized regardless of their visibility. You can, however, have individual properties ignored by annotating them with n2n\persistence\orm\annotation\AnnoTransient. We recommend that you create Entities that extend n2n\reflection\ObjectAdapter. The ObjectAdapter implements the static method ObjectAdapter::getClass(), which supports you when working with the EntityManager.

For the Article Entity in the example above, the following table would be conceivable:

CREATE TABLE `article` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(50) DEFAULT NULL,
  `text` TEXT,
  `type` ENUM('news','comment') DEFAULT 'news',
  `online` TINYINT(4) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

As you can see, the ORM automatically assumes that the property with the name "id" represents the primary key and that the values are generated by the database. How you can change this is described in the Id section of the article Configuring an Entity.

All Entity classes must be registered in app.ini under the [orm] group.

[orm]
entities\[\] = "atusch\bo\Article"
entities\[\] = "atusch\bo\User"

EntityManager​

Via the EntityManager you can save (persist) Entities to the database and also query or delete them again. The EntityManager for the Persistence Unit "default" can be passed in all magical methods. You can, for example, implement a Lookupable ArticleDao that fetches the EntityManager via _init() and provides various methods for reading and managing Article Entities.

class ArticleDao implements RequestScoped {

    #[Inject]
    private EntityManager $em;

    /**
     * @param int $id
     * @return atusch\bo\Article
     */
    public function getArticleById($id) {
        return $this->em->find(Article::getClass(), $id);
    }

    public function saveArticle(Article $article) {
        $this->em->persist($article);
    }
}

This ArticleDao is now available in every magical method and can, for example, be used in an ArticleController.

public function doArticle(ArticleDao $articleDao, $id) {
    $article = $articleDao->getArticleById($id);
    if ($article === null) {
        throw new PageNotFoundException();
    }

    $this->forward('view\article.html', array('article' => $article));
}

You can request EntityManager objects for other Persistence Units via the PdoPool, just as with the Pdo objects.

private function _init(PdoPool $pdoPool) {
    $this->em = $pdoPool->getEntityManagerFactory("db2")->getExtended();
}

In this example we first fetch the EntityManagerFactory of the Persistence Unit "db2" and then request an EntityManager for this Persistence Unit via EntityManagerFactory::getExtended().

With EntityManagerFactory::getExtended() you request an "extended" EntityManager that is shared with the entire application. You can also create a new one via EntityManagerFactory::create() or request an EntityManager via EntityManagerFactory::getTransactional() that is only valid for the current transaction. In the articles Lifecycle and Transactions you can learn more about this concept.

Queries​

As can be seen in the example above, you can query a single Entity via its primary key using EntityManager::find().

/**
 * @param int $id
 * @return atusch\bo\Article
 */
public function getArticleById($id) {
    return $this->em->find(Article::getClass(), $id);
}

find() expects as its first parameter a ReflectionClass object of the Entity to be queried. If the desired Entity extends n2n\persistence\orm\ObjectAdapter, you can access the respective ReflectionClass object via ObjectAdapter::getClass().

If you want to execute more complex queries, you can fall back on either the Criteria API or NQL (n2n Query Language).

Criteria API​

A Criteria object describes a query. It describes what should be selected, under which conditions and in which order. The descriptions do not refer to database tables and columns, but to Entities and their properties. With Criteria::toQuery() a Query object can be formed, via which the query can be executed and the respective data can be read out.

To put together a simple Criteria that describes the query of a single Entity type under simple comparison conditions, EntityManager::createSimpleCriteria() is suitable.

/**
 * @param string $type
 * @param int $limit
 * @param int $num
 * @return Article[]
 */
public function getArticlesByType($type, $limit, $num) {
    $criteria = $this->em->createSimpleCriteria(Article::getClass(),
            array('online' => true, 'type' => $type),
            array('id' => 'DESC'), $limit, $num);
    return $criteria->toQuery()->fetchArray();
}

createSimpleCriteria() expects as its first parameter the ReflectionClass of the Entity you want to query (influences the SELECT and FROM sections in the SQL statement).

Via the second parameter you can define simple comparison conditions in the form property = value, where the array key is expected to be the name of the property and the array value the comparison value (influences the WHERE section in the SQL statement).

Via the third parameter you define the sort order (influences the ORDER BY section in the SQL statement). Via the last two parameters you can define how many Article objects should be returned at most (influences the LIMIT section in the SQL statement).

As soon as you have converted the Criteria into a Query, you can execute the query with Query::fetchArray() and have the result returned as an array. In the example above, Query::fetchArray() returns an array of Article objects. You could therefore call ArticleDao::getArticlesByType() with $articleDao->getArticlesByType('news', 0, 30) to query the first 30 articles sorted in reverse order by Article::$id, where Article::$online equals true and Article::$type equals "news".

If you expect only a single row as the result, you can also execute the query with Query::fetchSingle(). Query::fetchSingle() returns only a single row, while Query::fetchArray() would return an array with one row.

/**
 * @param string $userName
 * @return User
 */
public function getUserByUserName($userName) {
    return $this->em->createSimpleCriteria(User::getClass(), array('userName' => $userName))
            ->toQuery()->fetchSingle();
}

In this example fetchSingle() returns the user with the passed user name. If the result is empty, null is returned. If the result comprises several rows, a QueryConflictException is thrown.

With EntityManager::createCriteria() you can also create an "empty" Criteria and describe the query yourself. This allows you to formulate more complex queries.

/**
 * @param array $types
 * @return Article[]
 */
public function getArticlesByTypes(array $types) {
    $criteria = $this->em->createCriteria();
    $criteria->select('a')
            ->from(Article::getClass(), 'a')
            ->where()->match('a.online', '=', true)->andMatch('a.type', 'IN', $types);
    return $criteria->toQuery()->fetchArray();
}

You could call ArticleDao::getArticleDao() from the example above with $articleDao->getArticlesByTypes(array('news', 'comment')) to query all Article objects where Article::$online equals true and Article::$type equals "news" or "comment".

This article only offers a short introduction to the Criteria API. You can find detailed information in the article Criteria API / NQL.

NQL (n2n Query Language)​

The n2n Query Language is syntactically similar to SQL statements, but, like the Criteria API, refers to Entities and their properties instead of database tables and columns. With EntityManager::createNqlCriteria() you can create a Criteria object based on NQL.

/**
 * @param string $type
 * @param int $limit
 * @param int $num
 * @return Article[]
 */
public function getArticlesByType($type, $limit, $num) {
    $criteria = $this->em->createNqlCriteria(
        'SELECT a FROM Article a WHERE a.online = :online AND a.type = :type',
        array('online' => true, 'type' => $type));
    return $criteria->limit(0, 30)->toQuery()->fetchAll();
}

In this example we implemented the method ArticleDao::getArticlesByType(), from the example further above, based on NQL. EntityManager::createNqlCriteria() expects as its first parameter the NQL string and as its second parameter the values of the placeholders that you used in your NQL statement.

You can also set placeholder values via Query::setParameter(). The difference between these two variants is explained in the Placeholders section of the article Criteria API / NQL.

Writing​

Via EntityManager::persist() you can save Entities to the database and delete them with EntityManager::remove(). Operations that lead to database mutations can only be executed within a transaction. The easiest way is to manage transactions in the controller. Via ControllerAdapter::beginTransaction() you can start a transaction and complete it with ControllerAdapter::commit().

public function doCreateArticle(ArticleDao $articleDao) {
    $this->beginTransaction();

    $article = new Article('Lorem ipsum');
    $article->setText('Lorem ipsum dolor...');
    $article->setType(Article::TYPE_COMMENT);

    $articleDao->saveArticle($article);

    $this->commit();
}

ArticleDao::saveArticle() can look like this:

public function saveArticle(Article $article) {
    $this->em->persist($article);
}

The INSERT and DELETE statements are executed as soon as the transaction is completed (COMMIT). When completing a transaction, all changes that you made to Entities are also updated in the database (UPDATE statement).

public function doExample(ArticleDao $articleDao) {
    $this->beginTransaction();
    
    $article = $articleDao->getArticleById(1);
    $article->setTitle('New title');
    
    $this->commit();
}

In this example the title is automatically updated in the database as soon as ControllerAdapter::commit() is called (an UPDATE statement is issued).

Logger​

If you are interested in which SQL statements the EntityManager generates and sends, you can have them output via the n2n\persistence\PdoLogger. The PdoLogger logs all database accesses of a Pdo object. You can access the respective PdoLogger via Pdo::getLogger().

public function saveArticle(Article $article) {
    $this->em->persist($article);
    
    var_dump($this->em->getPdo()->getLogger()->getEntries());
}

In this example we fetch the Pdo used by the EntityManager via EntityManager::getPdo() and access the desired log information via PdoLogger::getEntries().