Converting legacy SQL

This page documents the conversion of legacy SQL to Chamilo 2, Symfony/Doctrine compatible queries.

What's the point?

Chamilo 1.11.x (and previous versions) had a progressive mix of hardcoded SQL (see example below), Database:: class queries like Database::insert(); and entity-based code (like we will now be using).

Hardcoded SQL queries make it difficult to apply wide-range modifications to database structure (like during the migration from one major version to the next) because table field names are everywhere and often look the same. For example, a field like last_edit in a table would probably return dozens or hundreds of matches in the code, making changes particularly dangerous. However, we need such changes if we want to be able to get to an ever cleaner database.

Database class queries are also an issue because, in the end, they are not attached to entities and will still execute hardcoded SQL. They are, however, closer to the entity-based approach and, as such, are easier to convert.

In the documentation below, we explain (with examples) how to convert from one of those legacy forms to the new entity-based syntax.

Legacy SQL

We take as example the search for users in Chamilo 1.11.x (usermanager.lib.php at line 4607 at some point during the 2.0 development)

                $num = Database::num_rows($result);
                if (0 === $num) {
                    $date = api_get_utc_datetime();
                    $sql = "INSERT INTO $userRelUserTable (user_id, friend_user_id, relation_type, last_edit)
                            VALUES ($subscribedUserId, $userId, $relationType, '$date')";
                    $result = Database::query($sql);
                    $affectedRows += Database::affected_rows($result);
                }

As we can see, we have some hardcoded SQL there, with 4 fields. The last_edit field is a timestamp marking the last edition time. In the new database structure, creation and edition are managed through a PHP trait in the entity, for almost all entities, making it unnecessary to indicate a create or edit time in the insertion through entities.

Only 3 fields remains.

Because we are using entities, we need to get entities in the insertion preparation, instead of IDs like we used to. So $userId will now need to be an entity, which we can obtain through Chamilo functions like api_get_user_entity($userId).

Also, we need to call, for each attribute of the entity (each field of the table) a specific setter, and we need to call them on a UserRelUser object (an entity instance).

The parenthesis around (new UserRelUser()) are used to force the instanciation of the object, and be able to chain calls on it (->setUser(...)->setFriend(...)->...).

$userRelUser = (new UserRelUser())
    ->setUser(api_get_user_entity($subscribedUserId))
    ->setFriend(api_get_user_entity($userId))
    ->setRelationType($relationType);
$em = Database::getManager();
$em->persist($userRelUser);
$em->flush();

Where $em is used to represent the "Entity Manager".

These last 3 lines are almost always the same when we don't use a Repository object (see below), and will set the entity "in stone" by saving it to the database.

Using a repository

Anothe example, where we can go slightly further by using a Repository object, is used below from the work.ajax.php file in public/main/inc/ajax/:

    $sql = "UPDATE $work_table SET
        url_connection = '".$url."',
        title_correction = '".$title."',
    WHERE iid = $itemId";
    Database::query($sql);

We can move to a repository-based version like in the following snippet.

    $repo = Container::getStudentPublicationRepository();

    /** @var CStudentPublication $work */
    $work = $repo->find($itemId);
    if (null != $work) {
        $work->setTitle('aa');
        $repo->update($work);
    }

or, if there is no repository getter available to use:

    $em = Database::getManager();
    $repo = $em->getRepository(CStudentPublication::class);

    /** @var CStudentPublication $work */
    $work = $repo->find($itemId);
    if (null !== $work) {
        $work->setTitle('aa');
        $em->persist($work);
        $em->flush();
    }

Note: In both cases, we used a comment to specify the new variable ($work) would be an instance of the CStudentPublication entity. This will help both your editor and code quality scripts like ecs understand the meaning of the variable and apply the right validations to it. If the @ORM\Entity() marker in the entity has been set to the repository class (e.g. @ORM\Entity(repositoryClass="Chamilo\CourseBundle\Repository\CStudentPublicationRepository"), this becomes automatic.