From f695290205a9d0edcd55817cdfaa35096fd0420c Mon Sep 17 00:00:00 2001
From: colemanw <coleman@civicrm.org>
Date: Thu, 7 Sep 2023 13:32:04 -0400
Subject: [PATCH] Api4 - Enable file attachments to be displayed in SearchKit
---
CRM/Core/BAO/File.php | 23 +++
CRM/Core/DAO/EntityFile.php | 8 +-
.../Incremental/php/FiveSixtySeven.php | 1 +
.../Incremental/sql/5.67.alpha1.mysql.tpl | 3 +
CRM/Utils/File.php | 7 +-
Civi/Api4/EntityFile.php | 23 +++
Civi/Api4/File.php | 2 +-
Civi/Api4/Query/Api4Query.php | 1 +
Civi/Api4/Query/Api4SelectQuery.php | 8 +-
.../Spec/Provider/FileGetSpecProvider.php | 139 ++++++++++++++++++
api/v3/Attachment.php | 4 +
xml/schema/Core/EntityFile.xml | 4 +
12 files changed, 218 insertions(+), 5 deletions(-)
create mode 100644 Civi/Api4/EntityFile.php
create mode 100644 Civi/Api4/Service/Spec/Provider/FileGetSpecProvider.php
diff --git a/CRM/Core/BAO/File.php b/CRM/Core/BAO/File.php
index b1672b4aa0..3db9618489 100644
--- a/CRM/Core/BAO/File.php
+++ b/CRM/Core/BAO/File.php
@@ -834,4 +834,27 @@ HEREDOC;
return FALSE;
}
+ /**
+ * FIXME: Incomplete pseudoconstant for EntityFile.entity_table
+ *
+ * The `EntityFile` table serves 2 purposes:
+ * 1. As a many-to-many bridge table for entities that support multiple attachments
+ * 2. As a redundant copy of the value of custom fields of type File
+ *
+ * The 2nd use isn't really a bridge entity, and doesn't even make much sense
+ * (what purpose does it serve other than as a dummy value to use in file download links).
+ * Including the 2nd in this function would blow up the possible values for `entity_table`
+ * and make ACL clauses quite slow. So until someone comes up with a better idea,
+ * this only returns values relevant to the 1st.
+ *
+ * @return array
+ */
+ public static function getEntityTables(): array {
+ return [
+ 'civicrm_activity' => ts('Activity'),
+ 'civicrm_case' => ts('Case'),
+ 'civicrm_note' => ts('Note'),
+ ];
+ }
+
}
diff --git a/CRM/Core/DAO/EntityFile.php b/CRM/Core/DAO/EntityFile.php
index 21383d0788..325a58ffc8 100644
--- a/CRM/Core/DAO/EntityFile.php
+++ b/CRM/Core/DAO/EntityFile.php
@@ -6,7 +6,7 @@
*
* Generated from xml/schema/CRM/Core/EntityFile.xml
* DO NOT EDIT. Generated by CRM_Core_CodeGen
- * (GenCodeChecksum:abc8d2d096560e9cf5e96f385bb6c4a6)
+ * (GenCodeChecksum:21c0d34d03ae7f8aad9706aab1793656)
*/
/**
@@ -42,7 +42,7 @@ class CRM_Core_DAO_EntityFile extends CRM_Core_DAO {
/**
* physical tablename for entity being joined to file, e.g. civicrm_contact
*
- * @var string|null
+ * @var string
* (SQL type: varchar(64))
* Note that values will be retrieved from the database as a string.
*/
@@ -136,6 +136,7 @@ class CRM_Core_DAO_EntityFile extends CRM_Core_DAO {
'type' => CRM_Utils_Type::T_STRING,
'title' => ts('Entity Table'),
'description' => ts('physical tablename for entity being joined to file, e.g. civicrm_contact'),
+ 'required' => TRUE,
'maxlength' => 64,
'size' => CRM_Utils_Type::BIG,
'usage' => [
@@ -149,6 +150,9 @@ class CRM_Core_DAO_EntityFile extends CRM_Core_DAO {
'entity' => 'EntityFile',
'bao' => 'CRM_Core_DAO_EntityFile',
'localizable' => 0,
+ 'pseudoconstant' => [
+ 'callback' => 'CRM_Core_BAO_File::getEntityTables',
+ ],
'add' => '1.5',
],
'entity_id' => [
diff --git a/CRM/Upgrade/Incremental/php/FiveSixtySeven.php b/CRM/Upgrade/Incremental/php/FiveSixtySeven.php
index 17bdac0316..71a2ae32c7 100644
--- a/CRM/Upgrade/Incremental/php/FiveSixtySeven.php
+++ b/CRM/Upgrade/Incremental/php/FiveSixtySeven.php
@@ -29,6 +29,7 @@ class CRM_Upgrade_Incremental_php_FiveSixtySeven extends CRM_Upgrade_Incremental
*/
public function upgrade_5_67_alpha1($rev): void {
$this->addTask(ts('Upgrade DB to %1: SQL', [1 => $rev]), 'runSql', $rev);
+ $this->addTask('Make EntityFile.entity_table required', 'alterColumn', 'civicrm_entity_file', 'entity_table', "varchar(64) NOT NULL COMMENT 'physical tablename for entity being joined to file, e.g. civicrm_contact'");
$this->addExtensionTask('Enable Authx extension', ['authx'], 1101);
$this->addExtensionTask('Enable Afform extension', ['org.civicrm.afform'], 1102);
}
diff --git a/CRM/Upgrade/Incremental/sql/5.67.alpha1.mysql.tpl b/CRM/Upgrade/Incremental/sql/5.67.alpha1.mysql.tpl
index daf0e19721..8780d46248 100644
--- a/CRM/Upgrade/Incremental/sql/5.67.alpha1.mysql.tpl
+++ b/CRM/Upgrade/Incremental/sql/5.67.alpha1.mysql.tpl
@@ -1 +1,4 @@
{* file to handle db changes in 5.67.alpha1 during upgrade *}
+
+{* NULL values would be nonsensical and useless - no reason to keep them *}
+DELETE FROM civicrm_entity_file WHERE entity_table IS NULL;
diff --git a/CRM/Utils/File.php b/CRM/Utils/File.php
index e030be409b..833204adbf 100644
--- a/CRM/Utils/File.php
+++ b/CRM/Utils/File.php
@@ -20,6 +20,11 @@
*/
class CRM_Utils_File {
+ /**
+ * Used to remove md5 hash that was injected into uploaded file names.
+ */
+ const HASH_REMOVAL_PATTERN = '/_[a-f0-9]{32}\./';
+
/**
* Given a file name, determine if the file contents make it an ascii file
*
@@ -405,7 +410,7 @@ class CRM_Utils_File {
*/
public static function cleanFileName($name) {
// replace the last 33 character before the '.' with null
- $name = preg_replace('/(_[\w]{32})\./', '.', $name);
+ $name = preg_replace(self::HASH_REMOVAL_PATTERN, '.', $name);
return $name;
}
diff --git a/Civi/Api4/EntityFile.php b/Civi/Api4/EntityFile.php
new file mode 100644
index 0000000000..bb05421e18
--- /dev/null
+++ b/Civi/Api4/EntityFile.php
@@ -0,0 +1,23 @@
+<?php
+/*
+ +--------------------------------------------------------------------+
+ | Copyright CiviCRM LLC. All rights reserved. |
+ | |
+ | This work is published under the GNU AGPLv3 license with some |
+ | permitted exceptions and without any warranty. For full license |
+ | and copyright information, see https://civicrm.org/licensing |
+ +--------------------------------------------------------------------+
+ */
+namespace Civi\Api4;
+
+/**
+ * EntityFile Bridge.
+ *
+ * @searchable bridge
+ * @since 5.67
+ * @package Civi\Api4
+ */
+class EntityFile extends Generic\DAOEntity {
+ use Generic\Traits\EntityBridge;
+
+}
diff --git a/Civi/Api4/File.php b/Civi/Api4/File.php
index 7d635e6588..43ee2b07fe 100644
--- a/Civi/Api4/File.php
+++ b/Civi/Api4/File.php
@@ -13,7 +13,7 @@ namespace Civi\Api4;
/**
* File entity.
*
- * @searchable none
+ * @searchable secondary
* @since 5.41
* @package Civi\Api4
*/
diff --git a/Civi/Api4/Query/Api4Query.php b/Civi/Api4/Query/Api4Query.php
index 15f7050cf3..a40cfd4a67 100644
--- a/Civi/Api4/Query/Api4Query.php
+++ b/Civi/Api4/Query/Api4Query.php
@@ -147,6 +147,7 @@ abstract class Api4Query {
}
$this->apiFieldSpec[$path] = $field + [
'name' => $path,
+ 'path' => $path,
'type' => 'Extra',
'entity' => NULL,
'implicit_join' => NULL,
diff --git a/Civi/Api4/Query/Api4SelectQuery.php b/Civi/Api4/Query/Api4SelectQuery.php
index 95de27e36e..80d3ad5920 100644
--- a/Civi/Api4/Query/Api4SelectQuery.php
+++ b/Civi/Api4/Query/Api4SelectQuery.php
@@ -352,8 +352,9 @@ class Api4SelectQuery extends Api4Query {
* @param bool $strict
* In strict mode, this will throw an exception if the field doesn't exist
*
- * @return array|null
+ * @return array|bool|null
* @throws \CRM_Core_Exception
+ * @throws UnauthorizedException
*/
public function getField($expr, $strict = FALSE) {
// If the expression contains a pseudoconstant filter like activity_type_id:label,
@@ -380,6 +381,11 @@ class Api4SelectQuery extends Api4Query {
return $field;
}
+ public function getFieldSibling(array $field, string $siblingFieldName) {
+ $prefix = ($field['explicit_join'] ? $field['explicit_join'] . '.' : '') . ($field['implicit_join'] ? $field['implicit_join'] . '.' : '');
+ return $this->getField($prefix . $siblingFieldName);
+ }
+
/**
* Check the "gatekeeper" permissions for performing "get" on a given entity.
*
diff --git a/Civi/Api4/Service/Spec/Provider/FileGetSpecProvider.php b/Civi/Api4/Service/Spec/Provider/FileGetSpecProvider.php
new file mode 100644
index 0000000000..517eb61aeb
--- /dev/null
+++ b/Civi/Api4/Service/Spec/Provider/FileGetSpecProvider.php
@@ -0,0 +1,139 @@
+<?php
+
+/*
+ +--------------------------------------------------------------------+
+ | Copyright CiviCRM LLC. All rights reserved. |
+ | |
+ | This work is published under the GNU AGPLv3 license with some |
+ | permitted exceptions and without any warranty. For full license |
+ | and copyright information, see https://civicrm.org/licensing |
+ +--------------------------------------------------------------------+
+ */
+
+namespace Civi\Api4\Service\Spec\Provider;
+
+use Civi\Api4\Query\Api4SelectQuery;
+use Civi\Api4\Service\Spec\FieldSpec;
+use Civi\Api4\Service\Spec\RequestSpec;
+
+/**
+ * @service
+ * @internal
+ */
+class FileGetSpecProvider extends \Civi\Core\Service\AutoService implements Generic\SpecProviderInterface {
+
+ public function modifySpec(RequestSpec $spec): void {
+ $field = new FieldSpec('file_name', $spec->getEntity(), 'String');
+ $field->setLabel(ts('Filename'))
+ ->setTitle(ts('Filename'))
+ ->setColumnName('uri')
+ ->setDescription(ts('Name of uploaded file'))
+ ->setType('Extra')
+ ->addOutputFormatter([__CLASS__, 'formatFileName']);
+ // Uncomment this line and delete the function `formatFileName` when bumping min mysql version
+ // ->setSqlRenderer([__CLASS__, 'renderFileName']);
+ $spec->addFieldSpec($field);
+
+ $field = new FieldSpec('url', $spec->getEntity(), 'String');
+ $field->setLabel(ts('Download Url'))
+ ->setTitle(ts('File Url'))
+ ->setColumnName('id')
+ ->setDescription(ts('Url at which this file can be downloaded'))
+ ->setType('Extra')
+ ->setSqlRenderer([__CLASS__, 'renderFileUrl'])
+ ->addOutputFormatter([__CLASS__, 'formatFileUrl']);
+ $spec->addFieldSpec($field);
+
+ $field = new FieldSpec('icon', $spec->getEntity(), 'String');
+ $field->setLabel(ts('Icon'))
+ ->setTitle(ts('Filetype Icon'))
+ ->setColumnName('mime_type')
+ ->setDescription(ts('Icon associated with this filetype'))
+ ->setType('Extra')
+ ->addOutputFormatter([__CLASS__, 'formatFileIcon']);
+ $spec->addFieldSpec($field);
+
+ $field = new FieldSpec('is_image', $spec->getEntity(), 'Boolean');
+ $field->setLabel(ts('Is Image'))
+ ->setTitle(ts('File is Image'))
+ ->setColumnName('mime_type')
+ ->setDescription(ts('Is this a recognized image type file'))
+ ->setType('Extra')
+ ->setSqlRenderer([__CLASS__, 'renderFileIsImage']);
+ $spec->addFieldSpec($field);
+ }
+
+ public static function formatFileName(&$uri) {
+ if (version_compare(\CRM_Upgrade_Incremental_General::MIN_INSTALL_MYSQL_VER, 8, '>=')) {
+ // Warning to make the unit test fail after we bump min sql version to one that supports REGEX_REPLACE
+ \CRM_Core_Error::deprecatedWarning('Update FileGetSpecProvider to use renderFileName instead of formatFileName.');
+ }
+ if ($uri && is_string($uri)) {
+ $uri = \CRM_Utils_File::cleanFileName($uri);
+ }
+ }
+
+ /**
+ * Unused until we bump min sql version to 8
+ * @see formatFileName
+ */
+ public static function renderFileName(array $field): string {
+ // MySql doesn't use preg delimiters
+ $pattern = str_replace('/', "'", \CRM_Utils_File::HASH_REMOVAL_PATTERN);
+ return "REGEX_REPLACE({$field['sql_name']}, $pattern, '.')";
+ }
+
+ public static function renderFileUrl(array $idField, Api4SelectQuery $query): string {
+ // Getting a link to the file requires the `entity_id` from the `civicrm_entity_file` table
+ // If the file was implicitly joined, the joined-from-entity has the id we want
+ if ($idField['implicit_join']) {
+ $joinField = $query->getField($idField['implicit_join']);
+ $entityIdField = $query->getFieldSibling($joinField, 'id');
+ }
+ // If it's explicitly joined FROM another entity, get the id of the parent
+ elseif ($idField['explicit_join']) {
+ $parent = $query->getJoinParent($idField['explicit_join']);
+ $joinPrefix = $parent ? "$parent." : '';
+ $entityIdField = $query->getField($joinPrefix . 'id');
+ }
+ // If it's explicitly joined TO another entity, use the id of the other
+ if (!isset($entityIdField)) {
+ foreach ($query->getExplicitJoins() as $join) {
+ if ($join['bridge'] === 'EntityFile') {
+ $entityIdField = $query->getField($join['alias'] . '.id');
+ }
+ }
+ }
+ if (isset($entityIdField)) {
+ return "CONCAT('civicrm/file?reset=1&id=', $idField[sql_name], '&eid=', $entityIdField[sql_name])";
+ }
+ // Guess we couldn't find an `entity_id` in the query. This function could probably be improved.
+ return "NULL";
+ }
+
+ public static function renderFileIsImage(array $mimeTypeField, Api4SelectQuery $query): string {
+ $uriField = $query->getFieldSibling($mimeTypeField, 'uri');
+ return "IF(($mimeTypeField[sql_name] LIKE 'image/%') AND ($uriField[sql_name] NOT LIKE '%.unknown'), 1, 0)";
+ }
+
+ public static function formatFileUrl(&$value) {
+ $args = [];
+ // renderFileUrl() will have formatted the output in-sql to `civicrm/file?reset=1&id=id&eid=entity_id`
+ if (is_string($value) && str_contains($value, '?')) {
+ parse_str(explode('?', $value)[1], $args);
+ $value .= '&fcs=' . \CRM_Core_BAO_File::generateFileHash($args['eid'], $args['id']);
+ $value = (string) \Civi::url('frontend://' . $value, 'a');
+ }
+ }
+
+ public static function formatFileIcon(&$value) {
+ if (is_string($value)) {
+ $value = \CRM_Utils_File::getIconFromMimeType($value);
+ }
+ }
+
+ public function applies($entity, $action): bool {
+ return $entity === 'File' && $action === 'get';
+ }
+
+}
diff --git a/api/v3/Attachment.php b/api/v3/Attachment.php
index aa408d1bf5..5e16486707 100644
--- a/api/v3/Attachment.php
+++ b/api/v3/Attachment.php
@@ -205,6 +205,8 @@ function _civicrm_api3_attachment_delete_spec(&$spec) {
unset($spec['id']['api.required']);
$entityFileFields = CRM_Core_DAO_EntityFile::fields();
$spec['entity_table'] = $entityFileFields['entity_table'];
+ // Historically this field had no pseudoconstant and APIv3 can't handle it
+ $spec['entity_table']['pseudoconstant'] = NULL;
$spec['entity_table']['title'] = CRM_Utils_Array::value('title', $spec['entity_table'], 'Entity Table') . ' (write-once)';
$spec['entity_id'] = $entityFileFields['entity_id'];
$spec['entity_id']['title'] = CRM_Utils_Array::value('title', $spec['entity_id'], 'Entity ID') . ' (write-once)';
@@ -464,6 +466,8 @@ function _civicrm_api3_attachment_getfields() {
$spec['description'] = $fileFields['description'];
$spec['upload_date'] = $fileFields['upload_date'];
$spec['entity_table'] = $entityFileFields['entity_table'];
+ // Historically this field had no pseudoconstant and APIv3 can't handle it
+ $spec['entity_table']['pseudoconstant'] = NULL;
// Would be hard to securely handle changes.
$spec['entity_table']['title'] = CRM_Utils_Array::value('title', $spec['entity_table'], 'Entity Table') . ' (write-once)';
$spec['entity_id'] = $entityFileFields['entity_id'];
diff --git a/xml/schema/Core/EntityFile.xml b/xml/schema/Core/EntityFile.xml
index e2dc7bbd6b..20d75001d1 100644
--- a/xml/schema/Core/EntityFile.xml
+++ b/xml/schema/Core/EntityFile.xml
@@ -26,8 +26,12 @@
<name>entity_table</name>
<title>Entity Table</title>
<type>varchar</type>
+ <required>true</required>
<length>64</length>
<comment>physical tablename for entity being joined to file, e.g. civicrm_contact</comment>
+ <pseudoconstant>
+ <callback>CRM_Core_BAO_File::getEntityTables</callback>
+ </pseudoconstant>
<add>1.5</add>
</field>
<field>
--
2.25.1