DoctrineDatabase::loadRole() - Code Metrics - Inspection of "EZP-31088: Refactored UrlWildcard GW to use Doctri..." - ezsystems/ezpublish-kernel - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — ezp-31088-refactor-content-mod... ( ab3ba3...3726c8 )

unknown

created 2020-03-02 15:59 UTC

DoctrineDatabase::loadRole() A

↳ Parent: DoctrineDatabase

Complexity

Conditions	1
Paths	1

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	1
nc	1
nop	2
dl	0
loc	16
rs	9.7333
c	0
b	0
f	0

<?php

/**
 * @copyright Copyright (C) eZ Systems AS. All rights reserved.
 * @license For full copyright and license information view LICENSE file distributed with this source code.
 */
declare(strict_types=1);

namespace eZ\Publish\Core\Persistence\Legacy\User\Role\Gateway;

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\FetchMode;
use Doctrine\DBAL\ParameterType;
use Doctrine\DBAL\Query\QueryBuilder;
use eZ\Publish\Core\Persistence\Legacy\User\Role\Gateway;
use eZ\Publish\SPI\Persistence\User\Policy;
use eZ\Publish\SPI\Persistence\User\RoleUpdateStruct;
use eZ\Publish\SPI\Persistence\User\Role;

/**
 * User Role gateway implementation using the Doctrine database.
 *
 * @internal Gateway implementation is considered internal. Use Persistence User Handler instead.
 *
 * @see \eZ\Publish\SPI\Persistence\User\Handler
 */
final class DoctrineDatabase extends Gateway
{
    /** @var \Doctrine\DBAL\Connection */
    private $connection;

    /** @var \Doctrine\DBAL\Platforms\AbstractPlatform */
    private $dbPlatform;

    /**
     * Construct from database handler.
     *
     * @throws \Doctrine\DBAL\DBALException
     */
    public function __construct(Connection $connection)
    {
        $this->connection = $connection;
        $this->dbPlatform = $this->connection->getDatabasePlatform();
    }

    public function createRole(Role $role): Role
    {
        // Role original ID is set when creating a draft from an existing role
        if ($role->status === Role::STATUS_DRAFT && $role->id) {
            $roleOriginalId = $role->id;
        } elseif ($role->status === Role::STATUS_DRAFT) {
            // Not using a constant here as this is legacy storage engine specific.
            // -1 means "Newly created role".
            $roleOriginalId = -1;
        } else {
            // Role::STATUS_DEFINED value is 0, which is the expected value for version column for this status.
            $roleOriginalId = Role::STATUS_DEFINED;
        }

        $query = $this->connection->createQueryBuilder();
        $query
            ->insert(self::ROLE_TABLE)
            ->values(
                [
                    'is_new' => $query->createPositionalParameter(0, ParameterType::INTEGER),
                    'name' => $query->createPositionalParameter(
                        $role->identifier,
                        ParameterType::STRING
                    ),
                    'value' => $query->createPositionalParameter(0, ParameterType::INTEGER),
                    'version' => $query->createPositionalParameter(
                    // Column name "version" is misleading here as it stores originalId when creating a draft from an existing role.
                    // But hey, this is legacy! :-)
                        $roleOriginalId,
                        ParameterType::STRING
                    ),
                ]
            );
        $query->execute();

        if (!isset($role->id) || (int)$role->id < 1 || $role->status === Role::STATUS_DRAFT) {
            $role->id = (int)$this->connection->lastInsertId(self::ROLE_SEQ);
        }

        $role->originalId = $roleOriginalId;

        return $role;
    }

    private function getLoadRoleQueryBuilder(): QueryBuilder
    {
        $query = $this->connection->createQueryBuilder();
        $query
            ->select(
                'r.id AS ezrole_id',
                'r.name AS ezrole_name',
                'r.version AS ezrole_version',
                'p.id AS ezpolicy_id',
                'p.function_name AS ezpolicy_function_name',
                'p.module_name AS ezpolicy_module_name',
                'p.original_id AS ezpolicy_original_id',
                'l.identifier AS ezpolicy_limitation_identifier',
                'v.value AS ezpolicy_limitation_value_value'
            )
            ->from(self::ROLE_TABLE, 'r')
            ->leftJoin('r', self::POLICY_TABLE, 'p', 'p.role_id = r.id')
            ->leftJoin('p', self::POLICY_LIMITATION_TABLE, 'l', 'l.policy_id = p.id')
            ->leftJoin('l', self::POLICY_LIMITATION_VALUE_TABLE, 'v', 'v.limitation_id = l.id');

        return $query;
    }

    public function loadRole(int $roleId, int $status = Role::STATUS_DEFINED): array
    {
        $query = $this->getLoadRoleQueryBuilder();
        $query
            ->where(
                $query->expr()->eq(
                    'r.id',
                    $query->createPositionalParameter($roleId, ParameterType::INTEGER)
                )
            )
            ->andWhere(
                $this->buildRoleDraftQueryConstraint($status, $query)
            );

        return $query->execute()->fetchAll(FetchMode::ASSOCIATIVE);
    }

    public function loadRoleByIdentifier(
        string $identifier,
        int $status = Role::STATUS_DEFINED
    ): array {
        $query = $this->getLoadRoleQueryBuilder();
        $query
            ->where(
                $query->expr()->eq(
                    'r.name',
                    $query->createPositionalParameter($identifier, ParameterType::STRING)
                )
            )
            ->andWhere(
                $this->buildRoleDraftQueryConstraint($status, $query)
            );

        $statement = $query->execute();

        return $statement->fetchAll(FetchMode::ASSOCIATIVE);
    }

    public function loadRoleDraftByRoleId(int $roleId): array
    {
        $query = $this->getLoadRoleQueryBuilder();
        $query
            ->where(
                $query->expr()->eq(
                // Column name "version" is misleading as it stores originalId when creating a draft from an existing role.
                // But hey, this is legacy! :-)
                    'r.version',
                    $query->createPositionalParameter($roleId, ParameterType::STRING)
                )
            );

        $statement = $query->execute();

        return $statement->fetchAll(FetchMode::ASSOCIATIVE);
    }

    public function loadRoles(int $status = Role::STATUS_DEFINED): array
    {
        $query = $this->getLoadRoleQueryBuilder();
        $query->where(
            $this->buildRoleDraftQueryConstraint($status, $query)
        );

        $statement = $query->execute();

        return $statement->fetchAll(FetchMode::ASSOCIATIVE);
    }

    public function loadRolesForContentObjects(
        array $contentIds,
        int $status = Role::STATUS_DEFINED
    ): array {
        $query = $this->connection->createQueryBuilder();
        $expr = $query->expr();
        $query
            ->select(
                'ur.contentobject_id AS ezuser_role_contentobject_id',
                'r.id AS ezrole_id',
                'r.name AS ezrole_name',
                'r.version AS ezrole_version',
                'p.id AS ezpolicy_id',
                'p.function_name AS ezpolicy_function_name',
                'p.module_name AS ezpolicy_module_name',
                'p.original_id AS ezpolicy_original_id',
                'l.identifier AS ezpolicy_limitation_identifier',
                'v.value AS ezpolicy_limitation_value_value'
            )
            ->from(self::USER_ROLE_TABLE, 'urs')
            ->leftJoin(
                'urs',
                self::ROLE_TABLE,
                'r',
                $expr->eq(
                // for drafts the "version" column contains the original role ID...
                    $status === Role::STATUS_DEFINED ? 'r.id' : 'r.version',
                    'urs.role_id'
                )
            )
            ->leftJoin('r', self::USER_ROLE_TABLE, 'ur', 'ur.role_id = r.id')
            ->leftJoin('r', self::POLICY_TABLE, 'p', 'p.role_id = r.id')
            ->leftJoin('p', self::POLICY_LIMITATION_TABLE, 'l', 'l.policy_id = p.id')
            ->leftJoin('l', self::POLICY_LIMITATION_VALUE_TABLE, 'v', 'v.limitation_id = l.id')
            ->where(
                $expr->in(
                    'urs.contentobject_id',
                    $query->createPositionalParameter($contentIds, Connection::PARAM_INT_ARRAY)
                )
            );

        return $query->execute()->fetchAll(FetchMode::ASSOCIATIVE);
    }

    public function loadRoleAssignment(int $roleAssignmentId): array
    {
        $query = $this->connection->createQueryBuilder();
        $query->select(
            'id',
            'contentobject_id',
            'limit_identifier',
            'limit_value',
            'role_id'
        )->from(
            self::USER_ROLE_TABLE
        )->where(
            $query->expr()->eq(
                'id',
                $query->createPositionalParameter($roleAssignmentId, ParameterType::INTEGER)
            )
        );

        $statement = $query->execute();

        return $statement->fetchAll(FetchMode::ASSOCIATIVE);
    }

    public function loadRoleAssignmentsByGroupId(int $groupId, bool $inherited = false): array
    {
        $query = $this->connection->createQueryBuilder();
        $query->select(
            'id',
            'contentobject_id',
            'limit_identifier',
            'limit_value',
            'role_id'
        )->from(
            self::USER_ROLE_TABLE
        );

        if ($inherited) {
            $groupIds = $this->fetchUserGroups($groupId);
            $groupIds[] = $groupId;
            $query->where(
                $query->expr()->in(
                    'contentobject_id',
                    $groupIds
                )
            );
        } else {
            $query->where(
                $query->expr()->eq(
                    'contentobject_id',
                    $query->createPositionalParameter($groupId, ParameterType::INTEGER)
                )
            );
        }

        $statement = $query->execute();

        return $statement->fetchAll(FetchMode::ASSOCIATIVE);
    }

    public function loadRoleAssignmentsByRoleId(int $roleId): array
    {
        $query = $this->connection->createQueryBuilder();
        $query->select(
            'id',
            'contentobject_id',
            'limit_identifier',
            'limit_value',
            'role_id'
        )->from(
            self::USER_ROLE_TABLE
        )->where(
            $query->expr()->eq(
                'role_id',
                $query->createPositionalParameter($roleId, ParameterType::INTEGER)
            )
        );

        $statement = $query->execute();

        return $statement->fetchAll(FetchMode::ASSOCIATIVE);
    }

    public function loadPoliciesByUserId(int $userId): array
    {
        $groupIds = $this->fetchUserGroups($userId);
        $groupIds[] = $userId;

        return $this->loadRolesForContentObjects($groupIds);
    }

    /**
     * Fetch all group IDs the user belongs to.
     *
     * This method will return Content ids of all ancestor Locations for the given $userId.
     * Note that not all of these might be used as user groups,
     * but we will need to check all of them.
     *
     * @param int $userId
     *
     * @return array
     */
    private function fetchUserGroups(int $userId): array
    {
        $nodeIDs = $this->getAncestorLocationIdsForUser($userId);

        if (empty($nodeIDs)) {
            return [];
        }

        $query = $this->connection->createQueryBuilder();
        $query
            ->select('c.id')
            ->from('ezcontentobject_tree', 't')
            ->innerJoin('t', 'ezcontentobject', 'c', 'c.id = t.contentobject_id')
            ->where(
                $query->expr()->in(
                    't.node_id',
                    $nodeIDs
                )
            );

        $statement = $query->execute();

        return $statement->fetchAll(FetchMode::COLUMN);
    }

    public function updateRole(RoleUpdateStruct $role): void
    {
        $query = $this->connection->createQueryBuilder();
        $query
            ->update(self::ROLE_TABLE)
            ->set(
                'name',
                $query->createPositionalParameter($role->identifier, ParameterType::STRING)
            )
            ->where(
                $query->expr()->eq(
                    'id',
                    $query->createPositionalParameter($role->id, ParameterType::INTEGER)
                )
            );
        $query->execute();
    }

    public function deleteRole(int $roleId, int $status = Role::STATUS_DEFINED): void
    {
        $query = $this->connection->createQueryBuilder();
        $expr = $query->expr();
        $query
            ->delete(self::ROLE_TABLE)
            ->where(
                $expr->eq(
                    'id',
                    $query->createPositionalParameter($roleId, ParameterType::INTEGER)
                )
            )
            ->andWhere(
                $this->buildRoleDraftQueryConstraint($status, $query, self::ROLE_TABLE)
            );

        if ($status !== Role::STATUS_DRAFT) {
            $this->deleteRoleAssignments($roleId);
        }
        $query->execute();
    }

    public function publishRoleDraft(int $roleDraftId, ?int $originalRoleId = null): void
    {
        $this->markRoleAsPublished($roleDraftId, $originalRoleId);
        $this->publishRolePolicies($roleDraftId, $originalRoleId);
    }

    public function addPolicy(int $roleId, Policy $policy): Policy
    {
        $query = $this->connection->createQueryBuilder();
        $query
            ->insert(self::POLICY_TABLE)
            ->values(
                [
                    'function_name' => $query->createPositionalParameter(
                        $policy->function,
                        ParameterType::STRING
                    ),
                    'module_name' => $query->createPositionalParameter(
                        $policy->module,
                        ParameterType::STRING
                    ),
                    'original_id' => $query->createPositionalParameter(
                        $policy->originalId ?? 0,
                        ParameterType::INTEGER
                    ),
                    'role_id' => $query->createPositionalParameter($roleId, ParameterType::INTEGER),
                ]
            );
        $query->execute();

        $policy->id = (int)$this->connection->lastInsertId(self::POLICY_SEQ);
        $policy->roleId = $roleId;

        // Handle the only valid non-array value "*" by not inserting
        // anything. Still has not been documented by eZ Systems. So we
        // assume this is the right way to handle it.
        if (is_array($policy->limitations)) {
            $this->addPolicyLimitations($policy->id, $policy->limitations);
        }

        return $policy;
    }

    public function addPolicyLimitations(int $policyId, array $limitations): void
    {
        foreach ($limitations as $identifier => $values) {
            $query = $this->connection->createQueryBuilder();
            $query
                ->insert(self::POLICY_LIMITATION_TABLE)
                ->values(
                    [
                        'identifier' => $query->createPositionalParameter(
                            $identifier,
                            ParameterType::STRING
                        ),
                        'policy_id' => $query->createPositionalParameter(
                            $policyId,
                            ParameterType::INTEGER
                        ),
                    ]
                );
            $query->execute();

            $limitationId = (int)$this->connection->lastInsertId(self::POLICY_LIMITATION_SEQ);

            foreach ($values as $value) {
                $query = $this->connection->createQueryBuilder();
                $query
                    ->insert(self::POLICY_LIMITATION_VALUE_TABLE)
                    ->values(
                        [
                            'value' => $query->createPositionalParameter(
                                $value,
                                ParameterType::STRING
                            ),
                            'limitation_id' => $query->createPositionalParameter(
                                $limitationId,
                                ParameterType::INTEGER
                            ),
                        ]
                    );
                $query->execute();
            }
        }
    }

    public function removePolicy(int $policyId): void
    {
        $this->removePolicyLimitations($policyId);

        $query = $this->connection->createQueryBuilder();
        $query
            ->delete(self::POLICY_TABLE)
            ->where(
                $query->expr()->eq(
                    'id',
                    $query->createPositionalParameter($policyId, ParameterType::INTEGER)
                )
            );
        $query->execute();
    }

    /**
     * @param int[] $limitationIds
     */
    private function deletePolicyLimitations(array $limitationIds): void
    {
        $query = $this->connection->createQueryBuilder();
        $query
            ->delete(self::POLICY_LIMITATION_TABLE)
            ->where(
                $query->expr()->in(
                    'id',
                    $query->createPositionalParameter(
                        $limitationIds,
                        Connection::PARAM_INT_ARRAY
                    )
                )
            );
        $query->execute();
    }

    /**
     * @param int[] $limitationValueIds
     */
    private function deletePolicyLimitationValues(array $limitationValueIds): void
    {
        $query = $this->connection->createQueryBuilder();
        $query
            ->delete(self::POLICY_LIMITATION_VALUE_TABLE)
            ->where(
                $query->expr()->in(
                    'id',
                    $query->createPositionalParameter(
                        $limitationValueIds,
                        Connection::PARAM_INT_ARRAY
                    )
                )
            );
        $query->execute();
    }

    private function loadPolicyLimitationValues(int $policyId): array
    {
        $query = $this->connection->createQueryBuilder();
        $query
            ->select(
                'l.id AS ezpolicy_limitation_id',
                'v.id AS ezpolicy_limitation_value_id'
            )
            ->from(self::POLICY_TABLE, 'p')
            ->leftJoin('p', self::POLICY_LIMITATION_TABLE, 'l', 'l.policy_id = p.id')
            ->leftJoin('l', self::POLICY_LIMITATION_VALUE_TABLE, 'v', 'v.limitation_id = l.id')
            ->where(
                $query->expr()->eq(
                    'p.id',
                    $query->createPositionalParameter($policyId, ParameterType::INTEGER)
                )
            );

        return $query->execute()->fetchAll(FetchMode::ASSOCIATIVE);
    }

    public function removePolicyLimitations(int $policyId): void
    {
        $limitationValues = $this->loadPolicyLimitationValues($policyId);

        $limitationIds = array_map(
            'intval',
            array_column($limitationValues, 'ezpolicy_limitation_id')
        );
        $limitationValueIds = array_map(
            'intval',
            array_column($limitationValues, 'ezpolicy_limitation_value_id')
        );

        if (!empty($limitationValueIds)) {
            $this->deletePolicyLimitationValues($limitationValueIds);
        }

        if (!empty($limitationIds)) {
            $this->deletePolicyLimitations($limitationIds);
        }
    }

    /**
     * Delete Role assignments to Users.
     */
    private function deleteRoleAssignments(int $roleId): void
    {
        $query = $this->connection->createQueryBuilder();
        $query
            ->delete(self::USER_ROLE_TABLE)
            ->where(
                $query->expr()->eq(
                    'role_id',
                    $query->createPositionalParameter($roleId, ParameterType::INTEGER)
                )
            );
        $query->execute();
    }

    /**
     * Load all Ancestor Location IDs of the given User Location.
     *
     * @param int $userId
     *
     * @return int[]
     */
    private function getAncestorLocationIdsForUser(int $userId): array
    {
        $query = $this->connection->createQueryBuilder();
        $query
            ->select('t.path_string')
            ->from('ezcontentobject_tree', 't')
            ->where(
                $query->expr()->eq(
                    't.contentobject_id',
                    $query->createPositionalParameter($userId, ParameterType::STRING)
                )
            );

        $paths = $query->execute()->fetchAll(FetchMode::COLUMN);
        $nodeIds = array_unique(
            array_reduce(
                array_map(
                    function ($pathString) {
                        return array_filter(explode('/', $pathString));
                    },
                    $paths
                ),
                'array_merge_recursive',
                []
            )
        );

        return array_map('intval', $nodeIds);
    }

    private function buildRoleDraftQueryConstraint(
        int $status,
        QueryBuilder $query,
        string $columnAlias = 'r'
    ): string {
        if ($status === Role::STATUS_DEFINED) {
            $draftCondition = $query->expr()->eq(
                "{$columnAlias}.version",
                $query->createPositionalParameter($status, ParameterType::INTEGER)
            );
        } else {
            // version stores original Role ID when Role is a draft...
            $draftCondition = $query->expr()->neq(
                "{$columnAlias}.version",
                $query->createPositionalParameter(Role::STATUS_DEFINED, ParameterType::INTEGER)
            );
        }

        return $draftCondition;
    }

    private function markRoleAsPublished(int $roleDraftId, ?int $originalRoleId): void
    {
        $query = $this->connection->createQueryBuilder();
        $query
            ->update(self::ROLE_TABLE)
            ->set(
                'version',
                $query->createPositionalParameter(Role::STATUS_DEFINED, ParameterType::INTEGER)
            );
        // Draft was created from an existing role, so published role must get the original ID.
        if ($originalRoleId !== null) {
            $query->set(
                'id',
                $query->createPositionalParameter($originalRoleId, ParameterType::INTEGER)
            );
        }

        $query->where(
            $query->expr()->eq(
                'id',
                $query->createPositionalParameter($roleDraftId, ParameterType::INTEGER)
            )
        );
        $query->execute();
    }

    private function publishRolePolicies(int $roleDraftId, ?int $originalRoleId): void
    {
        $policyQuery = $this->connection->createQueryBuilder();
        $policyQuery
            ->update(self::POLICY_TABLE)
            ->set(
                'original_id',
                $policyQuery->createPositionalParameter(0, ParameterType::INTEGER)
            );
        // Draft was created from an existing role, so published policies must get the original role ID.
        if ($originalRoleId !== null) {
            $policyQuery->set(
                'role_id',
                $policyQuery->createPositionalParameter($originalRoleId, ParameterType::INTEGER)
            );
        }

        $policyQuery->where(
            $policyQuery->expr()->eq(
                'role_id',
                $policyQuery->createPositionalParameter($roleDraftId, ParameterType::INTEGER)
            )
        );
        $policyQuery->execute();
    }
}


1			<?php
2
3			/**
4			* @copyright Copyright (C) eZ Systems AS. All rights reserved.
5			* @license For full copyright and license information view LICENSE file distributed with this source code.
6			*/
7			declare(strict_types=1);
8
9			namespace eZ\Publish\Core\Persistence\Legacy\User\Role\Gateway;
10
11			use Doctrine\DBAL\Connection;
12			use Doctrine\DBAL\FetchMode;
13			use Doctrine\DBAL\ParameterType;
14			use Doctrine\DBAL\Query\QueryBuilder;
15			use eZ\Publish\Core\Persistence\Legacy\User\Role\Gateway;
16			use eZ\Publish\SPI\Persistence\User\Policy;
17			use eZ\Publish\SPI\Persistence\User\RoleUpdateStruct;
18			use eZ\Publish\SPI\Persistence\User\Role;
19
20			/**
21			* User Role gateway implementation using the Doctrine database.
22			*
23			* @internal Gateway implementation is considered internal. Use Persistence User Handler instead.
24			*
25			* @see \eZ\Publish\SPI\Persistence\User\Handler
26			*/
27			final class DoctrineDatabase extends Gateway
28			{
29			/** @var \Doctrine\DBAL\Connection */
30			private $connection;
31
32			/** @var \Doctrine\DBAL\Platforms\AbstractPlatform */
33			private $dbPlatform;
34
35			/**
36			* Construct from database handler.
37			*
38			* @throws \Doctrine\DBAL\DBALException
39			*/
40			public function __construct(Connection $connection)
41			{
42			$this->connection = $connection;
43			$this->dbPlatform = $this->connection->getDatabasePlatform();
44			}
45
46			public function createRole(Role $role): Role
47			{
48			// Role original ID is set when creating a draft from an existing role
49			if ($role->status === Role::STATUS_DRAFT && $role->id) {
50			$roleOriginalId = $role->id;
51			} elseif ($role->status === Role::STATUS_DRAFT) {
52			// Not using a constant here as this is legacy storage engine specific.
53			// -1 means "Newly created role".
54			$roleOriginalId = -1;
55			} else {
56			// Role::STATUS_DEFINED value is 0, which is the expected value for version column for this status.
57			$roleOriginalId = Role::STATUS_DEFINED;
58			}
59
60			$query = $this->connection->createQueryBuilder();
61			$query
62			->insert(self::ROLE_TABLE)
63			->values(
64			[
65			'is_new' => $query->createPositionalParameter(0, ParameterType::INTEGER),
66			'name' => $query->createPositionalParameter(
67			$role->identifier,
68			ParameterType::STRING
69			),
70			'value' => $query->createPositionalParameter(0, ParameterType::INTEGER),
71			'version' => $query->createPositionalParameter(
72			// Column name "version" is misleading here as it stores originalId when creating a draft from an existing role.
73			// But hey, this is legacy! :-)
74			$roleOriginalId,
75			ParameterType::STRING
76			),
77			]
78			);
79			$query->execute();
80
81			if (!isset($role->id) \|\| (int)$role->id < 1 \|\| $role->status === Role::STATUS_DRAFT) {
82			$role->id = (int)$this->connection->lastInsertId(self::ROLE_SEQ);
83			}
84
85			$role->originalId = $roleOriginalId;
86
87			return $role;
88			}
89
90			private function getLoadRoleQueryBuilder(): QueryBuilder
91			{
92			$query = $this->connection->createQueryBuilder();
93			$query
94			->select(
95			'r.id AS ezrole_id',
96			'r.name AS ezrole_name',
97			'r.version AS ezrole_version',
98			'p.id AS ezpolicy_id',
99			'p.function_name AS ezpolicy_function_name',
100			'p.module_name AS ezpolicy_module_name',
101			'p.original_id AS ezpolicy_original_id',
102			'l.identifier AS ezpolicy_limitation_identifier',
103			'v.value AS ezpolicy_limitation_value_value'
104			)
105			->from(self::ROLE_TABLE, 'r')
106			->leftJoin('r', self::POLICY_TABLE, 'p', 'p.role_id = r.id')
107			->leftJoin('p', self::POLICY_LIMITATION_TABLE, 'l', 'l.policy_id = p.id')
108			->leftJoin('l', self::POLICY_LIMITATION_VALUE_TABLE, 'v', 'v.limitation_id = l.id');
109
110			return $query;
111			}
112
113			public function loadRole(int $roleId, int $status = Role::STATUS_DEFINED): array
114			{
115			$query = $this->getLoadRoleQueryBuilder();
116			$query
117			->where(
118			$query->expr()->eq(
119			'r.id',
120			$query->createPositionalParameter($roleId, ParameterType::INTEGER)
121			)
122			)
123			->andWhere(
124			$this->buildRoleDraftQueryConstraint($status, $query)
125			);
126
127			return $query->execute()->fetchAll(FetchMode::ASSOCIATIVE);
128			}
129
130			public function loadRoleByIdentifier(
131			string $identifier,
132			int $status = Role::STATUS_DEFINED
133			): array {
134			$query = $this->getLoadRoleQueryBuilder();
135			$query
136			->where(
137			$query->expr()->eq(
138			'r.name',
139			$query->createPositionalParameter($identifier, ParameterType::STRING)
140			)
141			)
142			->andWhere(
143			$this->buildRoleDraftQueryConstraint($status, $query)
144			);
145
146			$statement = $query->execute();
147
148			return $statement->fetchAll(FetchMode::ASSOCIATIVE);
149			}
150
151			public function loadRoleDraftByRoleId(int $roleId): array
152			{
153			$query = $this->getLoadRoleQueryBuilder();
154			$query
155			->where(
156			$query->expr()->eq(
157			// Column name "version" is misleading as it stores originalId when creating a draft from an existing role.
158			// But hey, this is legacy! :-)
159			'r.version',
160			$query->createPositionalParameter($roleId, ParameterType::STRING)
161			)
162			);
163
164			$statement = $query->execute();
165
166			return $statement->fetchAll(FetchMode::ASSOCIATIVE);
167			}
168
169			public function loadRoles(int $status = Role::STATUS_DEFINED): array
170			{
171			$query = $this->getLoadRoleQueryBuilder();
172			$query->where(
173			$this->buildRoleDraftQueryConstraint($status, $query)
174			);
175
176			$statement = $query->execute();
177
178			return $statement->fetchAll(FetchMode::ASSOCIATIVE);
179			}
180
181			public function loadRolesForContentObjects(
182			array $contentIds,
183			int $status = Role::STATUS_DEFINED
184			): array {
185			$query = $this->connection->createQueryBuilder();
186			$expr = $query->expr();
187			$query
188			->select(
189			'ur.contentobject_id AS ezuser_role_contentobject_id',
190			'r.id AS ezrole_id',
191			'r.name AS ezrole_name',
192			'r.version AS ezrole_version',
193			'p.id AS ezpolicy_id',
194			'p.function_name AS ezpolicy_function_name',
195			'p.module_name AS ezpolicy_module_name',
196			'p.original_id AS ezpolicy_original_id',
197			'l.identifier AS ezpolicy_limitation_identifier',
198			'v.value AS ezpolicy_limitation_value_value'
199			)
200			->from(self::USER_ROLE_TABLE, 'urs')
201			->leftJoin(
202			'urs',
203			self::ROLE_TABLE,
204			'r',
205			$expr->eq(
206			// for drafts the "version" column contains the original role ID...
207			$status === Role::STATUS_DEFINED ? 'r.id' : 'r.version',
208			'urs.role_id'
209			)
210			)
211			->leftJoin('r', self::USER_ROLE_TABLE, 'ur', 'ur.role_id = r.id')
212			->leftJoin('r', self::POLICY_TABLE, 'p', 'p.role_id = r.id')
213			->leftJoin('p', self::POLICY_LIMITATION_TABLE, 'l', 'l.policy_id = p.id')
214			->leftJoin('l', self::POLICY_LIMITATION_VALUE_TABLE, 'v', 'v.limitation_id = l.id')
215			->where(
216			$expr->in(
217			'urs.contentobject_id',
218			$query->createPositionalParameter($contentIds, Connection::PARAM_INT_ARRAY)
219			)
220			);
221
222			return $query->execute()->fetchAll(FetchMode::ASSOCIATIVE);
223			}
224
225			public function loadRoleAssignment(int $roleAssignmentId): array
226			{
227			$query = $this->connection->createQueryBuilder();
228			$query->select(
229			'id',
230			'contentobject_id',
231			'limit_identifier',
232			'limit_value',
233			'role_id'
234			)->from(
235			self::USER_ROLE_TABLE
236			)->where(
237			$query->expr()->eq(
238			'id',
239			$query->createPositionalParameter($roleAssignmentId, ParameterType::INTEGER)
240			)
241			);
242
243			$statement = $query->execute();
244
245			return $statement->fetchAll(FetchMode::ASSOCIATIVE);
246			}
247
248			public function loadRoleAssignmentsByGroupId(int $groupId, bool $inherited = false): array
249			{
250			$query = $this->connection->createQueryBuilder();
251			$query->select(
252			'id',
253			'contentobject_id',
254			'limit_identifier',
255			'limit_value',
256			'role_id'
257			)->from(
258			self::USER_ROLE_TABLE
259			);
260
261			if ($inherited) {
262			$groupIds = $this->fetchUserGroups($groupId);
263			$groupIds[] = $groupId;
264			$query->where(
265			$query->expr()->in(
266			'contentobject_id',
267			$groupIds
268			)
269			);
270			} else {
271			$query->where(
272			$query->expr()->eq(
273			'contentobject_id',
274			$query->createPositionalParameter($groupId, ParameterType::INTEGER)
275			)
276			);
277			}
278
279			$statement = $query->execute();
280
281			return $statement->fetchAll(FetchMode::ASSOCIATIVE);
282			}
283
284			public function loadRoleAssignmentsByRoleId(int $roleId): array
285			{
286			$query = $this->connection->createQueryBuilder();
287			$query->select(
288			'id',
289			'contentobject_id',
290			'limit_identifier',
291			'limit_value',
292			'role_id'
293			)->from(
294			self::USER_ROLE_TABLE
295			)->where(
296			$query->expr()->eq(
297			'role_id',
298			$query->createPositionalParameter($roleId, ParameterType::INTEGER)
299			)
300			);
301
302			$statement = $query->execute();
303
304			return $statement->fetchAll(FetchMode::ASSOCIATIVE);
305			}
306
307			public function loadPoliciesByUserId(int $userId): array
308			{
309			$groupIds = $this->fetchUserGroups($userId);
310			$groupIds[] = $userId;
311
312			return $this->loadRolesForContentObjects($groupIds);
313			}
314
315			/**
316			* Fetch all group IDs the user belongs to.
317			*
318			* This method will return Content ids of all ancestor Locations for the given $userId.
319			* Note that not all of these might be used as user groups,
320			* but we will need to check all of them.
321			*
322			* @param int $userId
323			*
324			* @return array
325			*/
326			private function fetchUserGroups(int $userId): array
327			{
328			$nodeIDs = $this->getAncestorLocationIdsForUser($userId);
329
330			if (empty($nodeIDs)) {
331			return [];
332			}
333
334			$query = $this->connection->createQueryBuilder();
335			$query
336			->select('c.id')
337			->from('ezcontentobject_tree', 't')
338			->innerJoin('t', 'ezcontentobject', 'c', 'c.id = t.contentobject_id')
339			->where(
340			$query->expr()->in(
341			't.node_id',
342			$nodeIDs
343			)
344			);
345
346			$statement = $query->execute();
347
348			return $statement->fetchAll(FetchMode::COLUMN);
349			}
350
351			public function updateRole(RoleUpdateStruct $role): void
352			{
353			$query = $this->connection->createQueryBuilder();
354			$query
355			->update(self::ROLE_TABLE)
356			->set(
357			'name',
358			$query->createPositionalParameter($role->identifier, ParameterType::STRING)
359			)
360			->where(
361			$query->expr()->eq(
362			'id',
363			$query->createPositionalParameter($role->id, ParameterType::INTEGER)
364			)
365			);
366			$query->execute();
367			}
368
369			public function deleteRole(int $roleId, int $status = Role::STATUS_DEFINED): void
370			{
371			$query = $this->connection->createQueryBuilder();
372			$expr = $query->expr();
373			$query
374			->delete(self::ROLE_TABLE)
375			->where(
376			$expr->eq(
377			'id',
378			$query->createPositionalParameter($roleId, ParameterType::INTEGER)
379			)
380			)
381			->andWhere(
382			$this->buildRoleDraftQueryConstraint($status, $query, self::ROLE_TABLE)
383			);
384
385			if ($status !== Role::STATUS_DRAFT) {
386			$this->deleteRoleAssignments($roleId);
387			}
388			$query->execute();
389			}
390
391			public function publishRoleDraft(int $roleDraftId, ?int $originalRoleId = null): void
392			{
393			$this->markRoleAsPublished($roleDraftId, $originalRoleId);
394			$this->publishRolePolicies($roleDraftId, $originalRoleId);
395			}
396
397			public function addPolicy(int $roleId, Policy $policy): Policy
398			{
399			$query = $this->connection->createQueryBuilder();
400			$query
401			->insert(self::POLICY_TABLE)
402			->values(
403			[
404			'function_name' => $query->createPositionalParameter(
405			$policy->function,
406			ParameterType::STRING
407			),
408			'module_name' => $query->createPositionalParameter(
409			$policy->module,
410			ParameterType::STRING
411			),
412			'original_id' => $query->createPositionalParameter(
413			$policy->originalId ?? 0,
414			ParameterType::INTEGER
415			),
416			'role_id' => $query->createPositionalParameter($roleId, ParameterType::INTEGER),
417			]
418			);
419			$query->execute();
420
421			$policy->id = (int)$this->connection->lastInsertId(self::POLICY_SEQ);
422			$policy->roleId = $roleId;
423
424			// Handle the only valid non-array value "*" by not inserting
425			// anything. Still has not been documented by eZ Systems. So we
426			// assume this is the right way to handle it.
427			if (is_array($policy->limitations)) {
428			$this->addPolicyLimitations($policy->id, $policy->limitations);
429			}
430
431			return $policy;
432			}
433
434			public function addPolicyLimitations(int $policyId, array $limitations): void
435			{
436			foreach ($limitations as $identifier => $values) {
437			$query = $this->connection->createQueryBuilder();
438			$query
439			->insert(self::POLICY_LIMITATION_TABLE)
440			->values(
441			[
442			'identifier' => $query->createPositionalParameter(
443			$identifier,
444			ParameterType::STRING
445			),
446			'policy_id' => $query->createPositionalParameter(
447			$policyId,
448			ParameterType::INTEGER
449			),
450			]
451			);
452			$query->execute();
453
454			$limitationId = (int)$this->connection->lastInsertId(self::POLICY_LIMITATION_SEQ);
455
456			foreach ($values as $value) {
457			$query = $this->connection->createQueryBuilder();
458			$query
459			->insert(self::POLICY_LIMITATION_VALUE_TABLE)
460			->values(
461			[
462			'value' => $query->createPositionalParameter(
463			$value,
464			ParameterType::STRING
465			),
466			'limitation_id' => $query->createPositionalParameter(
467			$limitationId,
468			ParameterType::INTEGER
469			),
470			]
471			);
472			$query->execute();
473			}
474			}
475			}
476
477			public function removePolicy(int $policyId): void
478			{
479			$this->removePolicyLimitations($policyId);
480
481			$query = $this->connection->createQueryBuilder();
482			$query
483			->delete(self::POLICY_TABLE)
484			->where(
485			$query->expr()->eq(
486			'id',
487			$query->createPositionalParameter($policyId, ParameterType::INTEGER)
488			)
489			);
490			$query->execute();
491			}
492
493			/**
494			* @param int[] $limitationIds
495			*/
496			private function deletePolicyLimitations(array $limitationIds): void
497			{
498			$query = $this->connection->createQueryBuilder();
499			$query
500			->delete(self::POLICY_LIMITATION_TABLE)
501			->where(
502			$query->expr()->in(
503			'id',
504			$query->createPositionalParameter(
505			$limitationIds,
506			Connection::PARAM_INT_ARRAY
507			)
508			)
509			);
510			$query->execute();
511			}
512
513			/**
514			* @param int[] $limitationValueIds
515			*/
516			private function deletePolicyLimitationValues(array $limitationValueIds): void
517			{
518			$query = $this->connection->createQueryBuilder();
519			$query
520			->delete(self::POLICY_LIMITATION_VALUE_TABLE)
521			->where(
522			$query->expr()->in(
523			'id',
524			$query->createPositionalParameter(
525			$limitationValueIds,
526			Connection::PARAM_INT_ARRAY
527			)
528			)
529			);
530			$query->execute();
531			}
532
533			private function loadPolicyLimitationValues(int $policyId): array
534			{
535			$query = $this->connection->createQueryBuilder();
536			$query
537			->select(
538			'l.id AS ezpolicy_limitation_id',
539			'v.id AS ezpolicy_limitation_value_id'
540			)
541			->from(self::POLICY_TABLE, 'p')
542			->leftJoin('p', self::POLICY_LIMITATION_TABLE, 'l', 'l.policy_id = p.id')
543			->leftJoin('l', self::POLICY_LIMITATION_VALUE_TABLE, 'v', 'v.limitation_id = l.id')
544			->where(
545			$query->expr()->eq(
546			'p.id',
547			$query->createPositionalParameter($policyId, ParameterType::INTEGER)
548			)
549			);
550
551			return $query->execute()->fetchAll(FetchMode::ASSOCIATIVE);
552			}
553
554			public function removePolicyLimitations(int $policyId): void
555			{
556			$limitationValues = $this->loadPolicyLimitationValues($policyId);
557
558			$limitationIds = array_map(
559			'intval',
560			array_column($limitationValues, 'ezpolicy_limitation_id')
561			);
562			$limitationValueIds = array_map(
563			'intval',
564			array_column($limitationValues, 'ezpolicy_limitation_value_id')
565			);
566
567			if (!empty($limitationValueIds)) {
568			$this->deletePolicyLimitationValues($limitationValueIds);
569			}
570
571			if (!empty($limitationIds)) {
572			$this->deletePolicyLimitations($limitationIds);
573			}
574			}
575
576			/**
577			* Delete Role assignments to Users.
578			*/
579			private function deleteRoleAssignments(int $roleId): void
580			{
581			$query = $this->connection->createQueryBuilder();
582			$query
583			->delete(self::USER_ROLE_TABLE)
584			->where(
585			$query->expr()->eq(
586			'role_id',
587			$query->createPositionalParameter($roleId, ParameterType::INTEGER)
588			)
589			);
590			$query->execute();
591			}
592
593			/**
594			* Load all Ancestor Location IDs of the given User Location.
595			*
596			* @param int $userId
597			*
598			* @return int[]
599			*/
600			private function getAncestorLocationIdsForUser(int $userId): array
601			{
602			$query = $this->connection->createQueryBuilder();
603			$query
604			->select('t.path_string')
605			->from('ezcontentobject_tree', 't')
606			->where(
607			$query->expr()->eq(
608			't.contentobject_id',
609			$query->createPositionalParameter($userId, ParameterType::STRING)
610			)
611			);
612
613			$paths = $query->execute()->fetchAll(FetchMode::COLUMN);
614			$nodeIds = array_unique(
615			array_reduce(
616			array_map(
617			function ($pathString) {
618			return array_filter(explode('/', $pathString));
619			},
620			$paths
621			),
622			'array_merge_recursive',
623			[]
624			)
625			);
626
627			return array_map('intval', $nodeIds);
628			}
629
630			private function buildRoleDraftQueryConstraint(
631			int $status,
632			QueryBuilder $query,
633			string $columnAlias = 'r'
634			): string {
635			if ($status === Role::STATUS_DEFINED) {
636			$draftCondition = $query->expr()->eq(
637			"{$columnAlias}.version",
638			$query->createPositionalParameter($status, ParameterType::INTEGER)
639			);
640			} else {
641			// version stores original Role ID when Role is a draft...
642			$draftCondition = $query->expr()->neq(
643			"{$columnAlias}.version",
644			$query->createPositionalParameter(Role::STATUS_DEFINED, ParameterType::INTEGER)
645			);
646			}
647
648			return $draftCondition;
649			}
650
651			private function markRoleAsPublished(int $roleDraftId, ?int $originalRoleId): void
652			{
653			$query = $this->connection->createQueryBuilder();
654			$query
655			->update(self::ROLE_TABLE)
656			->set(
657			'version',
658			$query->createPositionalParameter(Role::STATUS_DEFINED, ParameterType::INTEGER)
659			);
660			// Draft was created from an existing role, so published role must get the original ID.
661			if ($originalRoleId !== null) {
662			$query->set(
663			'id',
664			$query->createPositionalParameter($originalRoleId, ParameterType::INTEGER)
665			);
666			}
667
668			$query->where(
669			$query->expr()->eq(
670			'id',
671			$query->createPositionalParameter($roleDraftId, ParameterType::INTEGER)
672			)
673			);
674			$query->execute();
675			}
676
677			private function publishRolePolicies(int $roleDraftId, ?int $originalRoleId): void
678			{
679			$policyQuery = $this->connection->createQueryBuilder();
680			$policyQuery
681			->update(self::POLICY_TABLE)
682			->set(
683			'original_id',
684			$policyQuery->createPositionalParameter(0, ParameterType::INTEGER)
685			);
686			// Draft was created from an existing role, so published policies must get the original role ID.
687			if ($originalRoleId !== null) {
688			$policyQuery->set(
689			'role_id',
690			$policyQuery->createPositionalParameter($originalRoleId, ParameterType::INTEGER)
691			);
692			}
693
694			$policyQuery->where(
695			$policyQuery->expr()->eq(
696			'role_id',
697			$policyQuery->createPositionalParameter($roleDraftId, ParameterType::INTEGER)
698			)
699			);
700			$policyQuery->execute();
701			}
702			}
703

ezsystems / ezpublish-kernel

Push — ezp-31088-refactor-content-mod... ( ab3ba3...3726c8 )

DoctrineDatabase::loadRole() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like