29053227110

Committed 09 Jul 2026 10:00PM UTC coverage: 55.077%. First build

Build # 29053227110

Build Type

Pull #7887

github

Committed by

web-flow

Commit Message

Merge e1981442c into e4d7ad4bb

Pull Request Pull Request #7887: fix(crl): improve CRL generation performance and reliability

Coverage Stats

38 of 81 new or added lines in 5 files covered. (46.91%)

13142 of 23861 relevant lines covered (55.08%)

7.41 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

9.05

/lib/Db/CrlMapper.php

<?php

declare(strict_types=1);

/**
 * SPDX-FileCopyrightText: 2025 LibreCode coop and contributors
 * SPDX-License-Identifier: AGPL-3.0-or-later
 */

namespace OCA\Libresign\Db;

use DateTime;
use OCA\Libresign\Enum\CertificateEngineType;
use OCA\Libresign\Enum\CertificateType;
use OCA\Libresign\Enum\CRLReason;
use OCA\Libresign\Enum\CRLStatus;
use OCP\AppFramework\Db\DoesNotExistException;
use OCP\AppFramework\Db\QBMapper;
use OCP\DB\QueryBuilder\IQueryBuilder;
use OCP\IDBConnection;

/**
 * @template-extends QBMapper<Crl>
 */
class CrlMapper extends QBMapper {
        public function __construct(IDBConnection $db) {
                parent::__construct($db, 'libresign_crl');
        }

        public function findBySerialNumber(string $serialNumber): Crl {
                $qb = $this->db->getQueryBuilder();

                $qb->select('*')
                        ->from($this->getTableName())
                        ->where($qb->expr()->eq('serial_number', $qb->createNamedParameter($serialNumber)));

                /** @var Crl */
                return $this->findEntity($qb);
        }

        /**
         * Find all issued (non-revoked) certificates owned by a user
         *
         * @param string $owner User ID
         * @return array<Crl>
         */
        public function findIssuedByOwner(string $owner): array {
                $qb = $this->db->getQueryBuilder();

                $qb->select('*')
                        ->from($this->getTableName())
                        ->where($qb->expr()->eq('owner', $qb->createNamedParameter($owner)))
                        ->andWhere($qb->expr()->eq('status', $qb->createNamedParameter(CRLStatus::ISSUED->value)));

                return $this->findEntities($qb);
        }

        public function createCertificate(
                string $serialNumber,
                string $owner,
                string $engine,
                string $instanceId,
                int $generation,
                DateTime $issuedAt,
                ?DateTime $validTo = null,
                ?array $issuer = null,
                ?array $subject = null,
                CertificateType|string $certificateType = 'leaf',
        ): Crl {
                $certificate = new Crl();
                $certificate->setSerialNumber($serialNumber);
                $certificate->setOwner($owner);
                $certificate->setStatus(CRLStatus::ISSUED->value);
                $certificate->setIssuedAt($issuedAt);
                $certificate->setValidTo($validTo);
                $certificate->setEngine($engine);
                $certificate->setInstanceId($instanceId);
                $certificate->setGeneration($generation);
                $certificate->setIssuerFromArray($issuer);
                $certificate->setSubjectFromArray($subject);
                $certificate->setCertificateType($certificateType);

                /** @var Crl */
                return $this->insert($certificate);
        }

        public function revokeCertificate(
                string $serialNumber,
                CRLReason $reason = CRLReason::UNSPECIFIED,
                ?string $comment = null,
                ?string $revokedBy = null,
                ?DateTime $invalidityDate = null,
                ?int $crlNumber = null,
                ?DateTime $revokedAt = null,
        ): Crl {
                $certificate = $this->findBySerialNumber($serialNumber);
                return $this->revokeCertificateEntity(
                        $certificate,
                        $reason,
                        $comment,
                        $revokedBy,
                        $invalidityDate,
                        $crlNumber,
                        $revokedAt,
                );
        }

        public function revokeCertificateEntity(
                Crl $certificate,
                CRLReason $reason = CRLReason::UNSPECIFIED,
                ?string $comment = null,
                ?string $revokedBy = null,
                ?DateTime $invalidityDate = null,
                ?int $crlNumber = null,
                ?DateTime $revokedAt = null,
        ): Crl {
                if (CRLStatus::from($certificate->getStatus()) !== CRLStatus::ISSUED) {
                        throw new \InvalidArgumentException('Certificate is not in issued status');
                }

                $certificate->setStatus(CRLStatus::REVOKED->value);
                $certificate->setReasonCode($reason->value);
                $certificate->setComment($comment !== '' ? $comment : null);
                $certificate->setRevokedBy($revokedBy);
                $certificate->setRevokedAt($revokedAt ?? new DateTime());
                $certificate->setInvalidityDate($invalidityDate);
                $certificate->setCrlNumber($crlNumber);

                /** @var Crl */
                return $this->update($certificate);
        }

        public function getRevokedCertificates(string $instanceId = '', int $generation = 0, string $engineType = ''): array {
                $qb = $this->db->getQueryBuilder();

                $qb->select('*')
                        ->from($this->getTableName())
                        ->where($qb->expr()->eq('status', $qb->createNamedParameter(CRLStatus::REVOKED->value)))
                        // Omit expired certificates from the CRL. Per RFC 5280, certificate
                        // consumers already reject expired certs on expiry grounds; omitting
                        // them keeps the CRL compact and generation fast.
                        ->andWhere(
                                $qb->expr()->orX(
                                        $qb->expr()->isNull('valid_to'),
                                        $qb->expr()->gte('valid_to', $qb->createNamedParameter(new \DateTime(), IQueryBuilder::PARAM_DATE)),
                                )
                        )
                        ->orderBy('revoked_at', 'DESC');

                if ($instanceId !== '') {
                        $qb->andWhere($qb->expr()->eq('instance_id', $qb->createNamedParameter($instanceId)));
                }
                if ($generation !== 0) {
                        $qb->andWhere($qb->expr()->eq('generation', $qb->createNamedParameter($generation, IQueryBuilder::PARAM_INT)));
                }
                if ($engineType !== '') {
                        $engineName = match($engineType) {
                                'o' => 'openssl',
                                'c' => 'cfssl',
                                'openssl', 'cfssl' => $engineType,
                                default => throw new \InvalidArgumentException("Invalid engine type: $engineType"),
                        };
                        $qb->andWhere($qb->expr()->eq('engine', $qb->createNamedParameter($engineName)));
                }

                return $this->findEntities($qb);
        }

        public function isInvalidAt(string $serialNumber, ?DateTime $checkDate = null): bool {
                $checkDate ??= new DateTime();

                try {
                        $certificate = $this->findBySerialNumber($serialNumber);
                } catch (DoesNotExistException) {
                        return false;
                }

                if ($certificate->isRevoked()) {
                        return true;
                }

                if ($certificate->getInvalidityDate() && $certificate->getInvalidityDate() <= $checkDate) {
                        return true;
                }

                return false;
        }

        public function cleanupExpiredCertificates(?DateTime $before = null): int {
                $before ??= new DateTime('-1 year');

                $qb = $this->db->getQueryBuilder();

                return $qb->delete($this->getTableName())
                        ->where($qb->expr()->isNotNull('valid_to'))
                        ->andWhere($qb->expr()->lt('valid_to', $qb->createNamedParameter($before, 'datetime')))
                        ->executeStatement();
        }

        public function getStatistics(): array {
                $qb = $this->db->getQueryBuilder();

                $result = $qb->select('status', $qb->func()->count('*', 'count'))
                        ->from($this->getTableName())
                        ->groupBy('status')
                        ->executeQuery();

                $stats = [];
                while ($row = $result->fetch()) {
                        $stats[$row['status']] = (int)$row['count'];
                }

                $result->closeCursor();
                return $stats;
        }

        public function getRevocationStatistics(): array {
                $qb = $this->db->getQueryBuilder();

                $result = $qb->select('reason_code', $qb->func()->count('*', 'count'))
                        ->from($this->getTableName())
                        ->where($qb->expr()->eq('status', $qb->createNamedParameter(CRLStatus::REVOKED->value)))
                        ->andWhere($qb->expr()->isNotNull('reason_code'))
                        ->groupBy('reason_code')
                        ->executeQuery();

                $stats = [];
                while ($row = $result->fetch()) {
                        $reasonCode = (int)$row['reason_code'];
                        $reason = CRLReason::tryFrom($reasonCode);
                        $stats[$reasonCode] = [
                                'code' => $reasonCode,
                                'description' => $reason?->getDescription() ?? 'unknown',
                                'count' => (int)$row['count'],
                        ];
                }

                $result->closeCursor();
                return $stats;
        }

        /**
         * @return list<array{instanceId: string, generation: int, engineType: string}>
         */
        public function listGeneratedCrlScopes(): array {
                $qb = $this->db->getQueryBuilder();

                $qb->selectDistinct(['instance_id', 'generation', 'engine'])
                        ->from($this->getTableName())
                        ->where($qb->expr()->isNotNull('instance_id'))
                        ->andWhere($qb->expr()->isNotNull('generation'))
                        ->andWhere($qb->expr()->isNotNull('engine'))
                        ->andWhere($qb->expr()->neq('instance_id', $qb->createNamedParameter('')))
                        ->andWhere($qb->expr()->neq('engine', $qb->createNamedParameter('')))
                        ->orderBy('instance_id', 'ASC')
                        ->addOrderBy('generation', 'ASC')
                        ->addOrderBy('engine', 'ASC');

                $result = $qb->executeQuery();
                $scopes = [];
                while (($row = $result->fetchAssociative()) !== false) {
                        $engineType = CertificateEngineType::tryFromValue($row['engine']);
                        if ($engineType === null) {
                                continue;
                        }

                        $scopes[] = [
                                'instanceId' => (string)$row['instance_id'],
                                'generation' => (int)$row['generation'],
                                'engineType' => $engineType->value,
                        ];
                }

                $result->closeCursor();
                return $scopes;
        }

        public function getLastCrlNumber(string $instanceId, int $generation, string $engineType): int {
                $qb = $this->db->getQueryBuilder();

                $qb->select($qb->func()->max('crl_number'))
                        ->from($this->getTableName())
                        ->where($qb->expr()->eq('instance_id', $qb->createNamedParameter($instanceId)))
                        ->andWhere($qb->expr()->eq('generation', $qb->createNamedParameter($generation, IQueryBuilder::PARAM_INT)))
                        ->andWhere($qb->expr()->eq('engine', $qb->createNamedParameter($engineType)))
                        ->andWhere($qb->expr()->isNotNull('crl_number'));

                $result = $qb->executeQuery();
                $maxCrlNumber = $result->fetchOne();
                $result->closeCursor();

                return (int)($maxCrlNumber ?? 0);
        }

        /**
         * List CRL entries with pagination and filters
         *
         * @param int $page Page number (1-based)
         * @param int $length Number of items per page
         * @param array<string, mixed> $filter Filters to apply (status, engine, instance_id, owner, etc.)
         * @param array<string, string> $sort Sort fields and directions ['field' => 'ASC|DESC']
         * @return array{data: array<Crl>, total: int}
         */
        public function listWithPagination(
                int $page = 1,
                int $length = 100,
                array $filter = [],
                array $sort = [],
        ): array {
                $qb = $this->db->getQueryBuilder();

                $qb->select('*')
                        ->from($this->getTableName());

                if (!empty($filter['status'])) {
                        $qb->andWhere($qb->expr()->eq('status', $qb->createNamedParameter($filter['status'])));
                }

                if (!empty($filter['engine'])) {
                        $qb->andWhere($qb->expr()->eq('engine', $qb->createNamedParameter($filter['engine'])));
                }

                if (!empty($filter['instance_id'])) {
                        $qb->andWhere($qb->expr()->eq('instance_id', $qb->createNamedParameter($filter['instance_id'])));
                }

                if (!empty($filter['generation'])) {
                        $qb->andWhere($qb->expr()->eq('generation', $qb->createNamedParameter((int)$filter['generation'], IQueryBuilder::PARAM_INT)));
                }

                if (!empty($filter['owner'])) {
                        $qb->andWhere($qb->expr()->like('owner', $qb->createNamedParameter('%' . $this->db->escapeLikeParameter($filter['owner']) . '%')));
                }

                if (!empty($filter['serial_number'])) {
                        $qb->andWhere($qb->expr()->like('serial_number', $qb->createNamedParameter('%' . $this->db->escapeLikeParameter($filter['serial_number']) . '%')));
                }

                if (!empty($filter['revoked_by'])) {
                        $qb->andWhere($qb->expr()->like('revoked_by', $qb->createNamedParameter('%' . $this->db->escapeLikeParameter($filter['revoked_by']) . '%')));
                }

                $countQb = $this->db->getQueryBuilder();
                $countQb->select($countQb->func()->count('*', 'count'))
                        ->from($this->getTableName());

                if (!empty($filter['status'])) {
                        $countQb->andWhere($countQb->expr()->eq('status', $countQb->createNamedParameter($filter['status'])));
                }
                if (!empty($filter['engine'])) {
                        $countQb->andWhere($countQb->expr()->eq('engine', $countQb->createNamedParameter($filter['engine'])));
                }
                if (!empty($filter['instance_id'])) {
                        $countQb->andWhere($countQb->expr()->eq('instance_id', $countQb->createNamedParameter($filter['instance_id'])));
                }
                if (!empty($filter['generation'])) {
                        $countQb->andWhere($countQb->expr()->eq('generation', $countQb->createNamedParameter((int)$filter['generation'], IQueryBuilder::PARAM_INT)));
                }
                if (!empty($filter['owner'])) {
                        $countQb->andWhere($countQb->expr()->like('owner', $countQb->createNamedParameter('%' . $this->db->escapeLikeParameter($filter['owner']) . '%')));
                }
                if (!empty($filter['serial_number'])) {
                        $countQb->andWhere($countQb->expr()->like('serial_number', $countQb->createNamedParameter('%' . $this->db->escapeLikeParameter($filter['serial_number']) . '%')));
                }
                if (!empty($filter['revoked_by'])) {
                        $countQb->andWhere($countQb->expr()->like('revoked_by', $countQb->createNamedParameter('%' . $this->db->escapeLikeParameter($filter['revoked_by']) . '%')));
                }

                $total = (int)$countQb->executeQuery()->fetchOne();

                $allowedSortFields = [
                        'serial_number',
                        'owner',
                        'status',
                        'engine',
                        'issued_at',
                        'valid_to',
                        'revoked_at',
                        'reason_code',
                ];

                if (!empty($sort)) {
                        foreach ($sort as $field => $direction) {
                                if (!in_array($field, $allowedSortFields, true)) {
                                        continue;
                                }
                                $direction = strtoupper($direction) === 'DESC' ? 'DESC' : 'ASC';
                                $qb->addOrderBy($field, $direction);
                        }
                } else {
                        $qb->orderBy('revoked_at', 'DESC')
                                ->addOrderBy('issued_at', 'DESC');
                }

                $offset = ($page - 1) * $length;
                $qb->setFirstResult($offset)
                        ->setMaxResults($length);

                return [
                        'data' => $this->findEntities($qb),
                        'total' => $total,
                ];
        }
}

1	<?php
2
3	declare(strict_types=1);
4
5	/**
6	* SPDX-FileCopyrightText: 2025 LibreCode coop and contributors
7	* SPDX-License-Identifier: AGPL-3.0-or-later
8	*/
9
10	namespace OCA\Libresign\Db;
11
12	use DateTime;
13	use OCA\Libresign\Enum\CertificateEngineType;
14	use OCA\Libresign\Enum\CertificateType;
15	use OCA\Libresign\Enum\CRLReason;
16	use OCA\Libresign\Enum\CRLStatus;
17	use OCP\AppFramework\Db\DoesNotExistException;
18	use OCP\AppFramework\Db\QBMapper;
19	use OCP\DB\QueryBuilder\IQueryBuilder;
20	use OCP\IDBConnection;
21
22	/**
23	* @template-extends QBMapper<Crl>
24	*/
25	class CrlMapper extends QBMapper {
26	public function __construct(IDBConnection $db) {
27	parent::__construct($db, 'libresign_crl');	13✔
28	}
29
30	public function findBySerialNumber(string $serialNumber): Crl {
31	$qb = $this->db->getQueryBuilder();	20✔
32
33	$qb->select('*')	20✔
34	->from($this->getTableName())	20✔
35	->where($qb->expr()->eq('serial_number', $qb->createNamedParameter($serialNumber)));	20✔
36
37	/** @var Crl */
38	return $this->findEntity($qb);	20✔
39	}
40
41	/**
42	* Find all issued (non-revoked) certificates owned by a user
43	*
44	* @param string $owner User ID
45	* @return array<Crl>
46	*/
47	public function findIssuedByOwner(string $owner): array {
48	$qb = $this->db->getQueryBuilder();	×
49
50	$qb->select('*')	×
51	->from($this->getTableName())	×
52	->where($qb->expr()->eq('owner', $qb->createNamedParameter($owner)))	×
53	->andWhere($qb->expr()->eq('status', $qb->createNamedParameter(CRLStatus::ISSUED->value)));	×
54
55	return $this->findEntities($qb);	×
56	}
57
58	public function createCertificate(
59	string $serialNumber,
60	string $owner,
61	string $engine,
62	string $instanceId,
63	int $generation,
64	DateTime $issuedAt,
65	?DateTime $validTo = null,
66	?array $issuer = null,
67	?array $subject = null,
68	CertificateType\|string $certificateType = 'leaf',
69	): Crl {
70	$certificate = new Crl();	46✔
71	$certificate->setSerialNumber($serialNumber);	46✔
72	$certificate->setOwner($owner);	46✔
73	$certificate->setStatus(CRLStatus::ISSUED->value);	46✔
74	$certificate->setIssuedAt($issuedAt);	46✔
75	$certificate->setValidTo($validTo);	46✔
76	$certificate->setEngine($engine);	46✔
77	$certificate->setInstanceId($instanceId);	46✔
78	$certificate->setGeneration($generation);	46✔
79	$certificate->setIssuerFromArray($issuer);	46✔
80	$certificate->setSubjectFromArray($subject);	46✔
81	$certificate->setCertificateType($certificateType);	46✔
82
83	/** @var Crl */
84	return $this->insert($certificate);	46✔
85	}
86
87	public function revokeCertificate(
88	string $serialNumber,
89	CRLReason $reason = CRLReason::UNSPECIFIED,
90	?string $comment = null,
91	?string $revokedBy = null,
92	?DateTime $invalidityDate = null,
93	?int $crlNumber = null,
94	?DateTime $revokedAt = null,
95	): Crl {
96	$certificate = $this->findBySerialNumber($serialNumber);	×
97	return $this->revokeCertificateEntity(	×
98	$certificate,	×
99	$reason,	×
100	$comment,	×
101	$revokedBy,	×
102	$invalidityDate,	×
103	$crlNumber,	×
104	$revokedAt,	×
105	);	×
106	}
107
108	public function revokeCertificateEntity(
109	Crl $certificate,
110	CRLReason $reason = CRLReason::UNSPECIFIED,
111	?string $comment = null,
112	?string $revokedBy = null,
113	?DateTime $invalidityDate = null,
114	?int $crlNumber = null,
115	?DateTime $revokedAt = null,
116	): Crl {
117	if (CRLStatus::from($certificate->getStatus()) !== CRLStatus::ISSUED) {	×
118	throw new \InvalidArgumentException('Certificate is not in issued status');	×
119	}
120
121	$certificate->setStatus(CRLStatus::REVOKED->value);	×
122	$certificate->setReasonCode($reason->value);	×
123	$certificate->setComment($comment !== '' ? $comment : null);	×
124	$certificate->setRevokedBy($revokedBy);	×
125	$certificate->setRevokedAt($revokedAt ?? new DateTime());	×
126	$certificate->setInvalidityDate($invalidityDate);	×
127	$certificate->setCrlNumber($crlNumber);	×
128
129	/** @var Crl */
130	return $this->update($certificate);	×
131	}
132
133	public function getRevokedCertificates(string $instanceId = '', int $generation = 0, string $engineType = ''): array {
134	$qb = $this->db->getQueryBuilder();	×
135
136	$qb->select('*')	×
137	->from($this->getTableName())	×
138	->where($qb->expr()->eq('status', $qb->createNamedParameter(CRLStatus::REVOKED->value)))	×
NEW 139	// Omit expired certificates from the CRL. Per RFC 5280, certificate	×
NEW 140	// consumers already reject expired certs on expiry grounds; omitting	×
NEW 141	// them keeps the CRL compact and generation fast.	×
NEW 142	->andWhere(	×
NEW 143	$qb->expr()->orX(	×
NEW 144	$qb->expr()->isNull('valid_to'),	×
NEW 145	$qb->expr()->gte('valid_to', $qb->createNamedParameter(new \DateTime(), IQueryBuilder::PARAM_DATE)),	×
NEW 146	)	×
NEW 147	)	×
148	->orderBy('revoked_at', 'DESC');	×
149
150	if ($instanceId !== '') {	×
151	$qb->andWhere($qb->expr()->eq('instance_id', $qb->createNamedParameter($instanceId)));	×
152	}
153	if ($generation !== 0) {	×
154	$qb->andWhere($qb->expr()->eq('generation', $qb->createNamedParameter($generation, IQueryBuilder::PARAM_INT)));	×
155	}
156	if ($engineType !== '') {	×
157	$engineName = match($engineType) {	×
158	'o' => 'openssl',	×
159	'c' => 'cfssl',	×
160	'openssl', 'cfssl' => $engineType,	×
161	default => throw new \InvalidArgumentException("Invalid engine type: $engineType"),	×
162	};	×
163	$qb->andWhere($qb->expr()->eq('engine', $qb->createNamedParameter($engineName)));	×
164	}
165
166	return $this->findEntities($qb);	×
167	}
168
169	public function isInvalidAt(string $serialNumber, ?DateTime $checkDate = null): bool {
170	$checkDate ??= new DateTime();	×
171
172	try {
173	$certificate = $this->findBySerialNumber($serialNumber);	×
174	} catch (DoesNotExistException) {	×
175	return false;	×
176	}
177
178	if ($certificate->isRevoked()) {	×
179	return true;	×
180	}
181
182	if ($certificate->getInvalidityDate() && $certificate->getInvalidityDate() <= $checkDate) {	×
183	return true;	×
184	}
185
186	return false;	×
187	}
188
189	public function cleanupExpiredCertificates(?DateTime $before = null): int {
190	$before ??= new DateTime('-1 year');	×
191
192	$qb = $this->db->getQueryBuilder();	×
193
194	return $qb->delete($this->getTableName())	×
195	->where($qb->expr()->isNotNull('valid_to'))	×
196	->andWhere($qb->expr()->lt('valid_to', $qb->createNamedParameter($before, 'datetime')))	×
197	->executeStatement();	×
198	}
199
200	public function getStatistics(): array {
201	$qb = $this->db->getQueryBuilder();	×
202
203	$result = $qb->select('status', $qb->func()->count('*', 'count'))	×
204	->from($this->getTableName())	×
205	->groupBy('status')	×
206	->executeQuery();	×
207
208	$stats = [];	×
209	while ($row = $result->fetch()) {	×
210	$stats[$row['status']] = (int)$row['count'];	×
211	}
212
213	$result->closeCursor();	×
214	return $stats;	×
215	}
216
217	public function getRevocationStatistics(): array {
218	$qb = $this->db->getQueryBuilder();	×
219
220	$result = $qb->select('reason_code', $qb->func()->count('*', 'count'))	×
221	->from($this->getTableName())	×
222	->where($qb->expr()->eq('status', $qb->createNamedParameter(CRLStatus::REVOKED->value)))	×
223	->andWhere($qb->expr()->isNotNull('reason_code'))	×
224	->groupBy('reason_code')	×
225	->executeQuery();	×
226
227	$stats = [];	×
228	while ($row = $result->fetch()) {	×
229	$reasonCode = (int)$row['reason_code'];	×
230	$reason = CRLReason::tryFrom($reasonCode);	×
231	$stats[$reasonCode] = [	×
232	'code' => $reasonCode,	×
233	'description' => $reason?->getDescription() ?? 'unknown',	×
234	'count' => (int)$row['count'],	×
235	];	×
236	}
237
238	$result->closeCursor();	×
239	return $stats;	×
240	}
241
242	/**
243	* @return list<array{instanceId: string, generation: int, engineType: string}>
244	*/
245	public function listGeneratedCrlScopes(): array {
246	$qb = $this->db->getQueryBuilder();	×
247
248	$qb->selectDistinct(['instance_id', 'generation', 'engine'])	×
249	->from($this->getTableName())	×
250	->where($qb->expr()->isNotNull('instance_id'))	×
251	->andWhere($qb->expr()->isNotNull('generation'))	×
252	->andWhere($qb->expr()->isNotNull('engine'))	×
253	->andWhere($qb->expr()->neq('instance_id', $qb->createNamedParameter('')))	×
254	->andWhere($qb->expr()->neq('engine', $qb->createNamedParameter('')))	×
255	->orderBy('instance_id', 'ASC')	×
256	->addOrderBy('generation', 'ASC')	×
257	->addOrderBy('engine', 'ASC');	×
258
259	$result = $qb->executeQuery();	×
260	$scopes = [];	×
261	while (($row = $result->fetchAssociative()) !== false) {	×
262	$engineType = CertificateEngineType::tryFromValue($row['engine']);	×
263	if ($engineType === null) {	×
264	continue;	×
265	}
266
267	$scopes[] = [	×
268	'instanceId' => (string)$row['instance_id'],	×
269	'generation' => (int)$row['generation'],	×
270	'engineType' => $engineType->value,	×
271	];	×
272	}
273
274	$result->closeCursor();	×
275	return $scopes;	×
276	}
277
278	public function getLastCrlNumber(string $instanceId, int $generation, string $engineType): int {
279	$qb = $this->db->getQueryBuilder();	×
280
281	$qb->select($qb->func()->max('crl_number'))	×
282	->from($this->getTableName())	×
283	->where($qb->expr()->eq('instance_id', $qb->createNamedParameter($instanceId)))	×
284	->andWhere($qb->expr()->eq('generation', $qb->createNamedParameter($generation, IQueryBuilder::PARAM_INT)))	×
285	->andWhere($qb->expr()->eq('engine', $qb->createNamedParameter($engineType)))	×
286	->andWhere($qb->expr()->isNotNull('crl_number'));	×
287
288	$result = $qb->executeQuery();	×
289	$maxCrlNumber = $result->fetchOne();	×
290	$result->closeCursor();	×
291
292	return (int)($maxCrlNumber ?? 0);	×
293	}
294
295	/**
296	* List CRL entries with pagination and filters
297	*
298	* @param int $page Page number (1-based)
299	* @param int $length Number of items per page
300	* @param array<string, mixed> $filter Filters to apply (status, engine, instance_id, owner, etc.)
301	* @param array<string, string> $sort Sort fields and directions ['field' => 'ASC\|DESC']
302	* @return array{data: array<Crl>, total: int}
303	*/
304	public function listWithPagination(
305	int $page = 1,
306	int $length = 100,
307	array $filter = [],
308	array $sort = [],
309	): array {
310	$qb = $this->db->getQueryBuilder();	×
311
312	$qb->select('*')	×
313	->from($this->getTableName());	×
314
315	if (!empty($filter['status'])) {	×
316	$qb->andWhere($qb->expr()->eq('status', $qb->createNamedParameter($filter['status'])));	×
317	}
318
319	if (!empty($filter['engine'])) {	×
320	$qb->andWhere($qb->expr()->eq('engine', $qb->createNamedParameter($filter['engine'])));	×
321	}
322
323	if (!empty($filter['instance_id'])) {	×
324	$qb->andWhere($qb->expr()->eq('instance_id', $qb->createNamedParameter($filter['instance_id'])));	×
325	}
326
327	if (!empty($filter['generation'])) {	×
328	$qb->andWhere($qb->expr()->eq('generation', $qb->createNamedParameter((int)$filter['generation'], IQueryBuilder::PARAM_INT)));	×
329	}
330
331	if (!empty($filter['owner'])) {	×
332	$qb->andWhere($qb->expr()->like('owner', $qb->createNamedParameter('%' . $this->db->escapeLikeParameter($filter['owner']) . '%')));	×
333	}
334
335	if (!empty($filter['serial_number'])) {	×
336	$qb->andWhere($qb->expr()->like('serial_number', $qb->createNamedParameter('%' . $this->db->escapeLikeParameter($filter['serial_number']) . '%')));	×
337	}
338
339	if (!empty($filter['revoked_by'])) {	×
340	$qb->andWhere($qb->expr()->like('revoked_by', $qb->createNamedParameter('%' . $this->db->escapeLikeParameter($filter['revoked_by']) . '%')));	×
341	}
342
343	$countQb = $this->db->getQueryBuilder();	×
344	$countQb->select($countQb->func()->count('*', 'count'))	×
345	->from($this->getTableName());	×
346
347	if (!empty($filter['status'])) {	×
348	$countQb->andWhere($countQb->expr()->eq('status', $countQb->createNamedParameter($filter['status'])));	×
349	}
350	if (!empty($filter['engine'])) {	×
351	$countQb->andWhere($countQb->expr()->eq('engine', $countQb->createNamedParameter($filter['engine'])));	×
352	}
353	if (!empty($filter['instance_id'])) {	×
354	$countQb->andWhere($countQb->expr()->eq('instance_id', $countQb->createNamedParameter($filter['instance_id'])));	×
355	}
356	if (!empty($filter['generation'])) {	×
357	$countQb->andWhere($countQb->expr()->eq('generation', $countQb->createNamedParameter((int)$filter['generation'], IQueryBuilder::PARAM_INT)));	×
358	}
359	if (!empty($filter['owner'])) {	×
360	$countQb->andWhere($countQb->expr()->like('owner', $countQb->createNamedParameter('%' . $this->db->escapeLikeParameter($filter['owner']) . '%')));	×
361	}
362	if (!empty($filter['serial_number'])) {	×
363	$countQb->andWhere($countQb->expr()->like('serial_number', $countQb->createNamedParameter('%' . $this->db->escapeLikeParameter($filter['serial_number']) . '%')));	×
364	}
365	if (!empty($filter['revoked_by'])) {	×
366	$countQb->andWhere($countQb->expr()->like('revoked_by', $countQb->createNamedParameter('%' . $this->db->escapeLikeParameter($filter['revoked_by']) . '%')));	×
367	}
368
369	$total = (int)$countQb->executeQuery()->fetchOne();	×
370
371	$allowedSortFields = [	×
372	'serial_number',	×
373	'owner',	×
374	'status',	×
375	'engine',	×
376	'issued_at',	×
377	'valid_to',	×
378	'revoked_at',	×
379	'reason_code',	×
380	];	×
381
382	if (!empty($sort)) {	×
383	foreach ($sort as $field => $direction) {	×
384	if (!in_array($field, $allowedSortFields, true)) {	×
385	continue;	×
386	}
387	$direction = strtoupper($direction) === 'DESC' ? 'DESC' : 'ASC';	×
388	$qb->addOrderBy($field, $direction);	×
389	}
390	} else {
391	$qb->orderBy('revoked_at', 'DESC')	×
392	->addOrderBy('issued_at', 'DESC');	×
393	}
394
395	$offset = ($page - 1) * $length;	×
396	$qb->setFirstResult($offset)	×
397	->setMaxResults($length);	×
398
399	return [	×
400	'data' => $this->findEntities($qb),	×
401	'total' => $total,	×
402	];	×
403	}
404	}

LibreSign / libresign / 29053227110

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous