Database
========

GNU social has to store a large collection of data for
rapid search and retrieval.

GNU social can use different Relational Database
Management Systems, namely PostgreSQL and MariaDB.

The storage is interfaced using an [Object-Relational
Mapper (ORM)](https://en.wikipedia.org/wiki/Object%E2%80%93relational_mapping)
paradigm.
As the term ORM already hints at, this allows to
simplify the translation between database rows and the
PHP object model.

[_images/mapping_single_entity.png]: data:image/png;base64,[encoded data, 85824 characters]"

Transactions
------------
An _EntityManager_ and the underlying _UnitOfWork_
employ a strategy called [transactional write-behind](https://www.doctrine-project.org/projects/doctrine-orm/en/current/reference/architecture.html#transactional-write-behind)
that delays the execution of SQL statements in order to
execute them in the most efficient way and at the end
of a transaction so that all write locks are quickly
released. You should see Doctrine as a tool to
synchronize your in-memory objects with the database
in well defined [units of work](https://www.doctrine-project.org/projects/doctrine-orm/en/current/reference/architecture.html#the-unit-of-work).
Work with your objects and modify them as usual. For
the most part, Doctrine ORM already takes care of proper [transaction demarcation](https://www.doctrine-project.org/projects/doctrine-orm/en/current/reference/transactions-and-concurrency.html#transaction-demarcation)
for you: All the write operations (INSERT/UPDATE/DELETE)
are queued until _EntityManager#flush()_ is invoked which
wraps all of these changes in a single transaction.

Declaring an Entity
-------------------

```php
<?php
namespace Plugin\Embed\Entity;

use App\Core\Entity;
use DateTimeInterface;
class AttachmentEmbed extends Entity
{
    private int $attachment_id;
    private ?string $mimetype;
    private ?string $filename;
    private ?string $media_url;
    private \DateTimeInterface $modified;
    /* Getters and Setters */
    public static function schemaDef()
    {
        return [
            'name'   => 'attachment_embed',
            'fields' => [
                'attachment_id' => ['type' => 'int', 'not null' => true, 'description' => 'Embed for that URL/file'],
                'mimetype'      => ['type' => 'varchar', 'length' => 50, 'description' => 'mime type of resource'],
                'filename'      => ['type' => 'varchar', 'length' => 191, 'description' => 'file name of resource when available'],
                'media_url'     => ['type' => 'text', 'description' => 'URL for this Embed resource when applicable (photo, link)'],
                'modified'      => ['type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'],
            ],
            'primary key'  => ['attachment_id'],
            'foreign keys' => [
                'attachment_embed_attachment_id_fkey' => ['attachment', ['attachment_id' => 'id']],
            ],
        ];
    }
}
```

Retrieving an entity
--------------------

```php
$res = self::findBy('attachment_embed', ['attachment_id' => $attachment->getId(), null, 1, null);
if (count($res) === 1) {
    $object = $res[0];
}
```

Deleting an Entity
------------------
```php
$object->delete();
```

Creating an Entity
-----------------
```php
$embed_data['attachment_id'] = $attachment->getId();
DB::persist(Entity\AttachmentEmbed::create($embed_data));
DB::flush();
```

Querying the database
---------------------
When the ORM isn't powerful enough to satisfy your needs,
you can resort to [Doctrine QueryBuilder](https://www.doctrine-project.org/projects/doctrine-orm/en/current/reference/query-builder.html#the-querybuilder).

