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) paradigm. As the term ORM already hints at, this allows to simplify the translation between database rows and the PHP object model.

Transactions

An EntityManager and the underlying UnitOfWork employ a strategy called 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. Work with your objects and modify them as usual. For the most part, Doctrine ORM already takes care of proper 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
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

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

Deleting an Entity

$object->delete();

Creating an Entity

$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.