Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
1.11% covered (danger)
1.11%
1 / 90
7.14% covered (danger)
7.14%
1 / 14
CRAP
0.00% covered (danger)
0.00%
0 / 1
Tags
1.11% covered (danger)
1.11%
1 / 90
7.14% covered (danger)
7.14%
1 / 14
2873.88
0.00% covered (danger)
0.00%
0 / 1
 __construct
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
1
 getByText
0.00% covered (danger)
0.00%
0 / 8
0.00% covered (danger)
0.00%
0 / 1
42
 matchText
0.00% covered (danger)
0.00%
0 / 3
0.00% covered (danger)
0.00%
0 / 1
2
 resourceSearch
0.00% covered (danger)
0.00%
0 / 3
0.00% covered (danger)
0.00%
0 / 1
56
 getForResource
0.00% covered (danger)
0.00%
0 / 13
0.00% covered (danger)
0.00%
0 / 1
110
 getListTagsForUser
0.00% covered (danger)
0.00%
0 / 3
0.00% covered (danger)
0.00%
0 / 1
30
 getForList
0.00% covered (danger)
0.00%
0 / 3
0.00% covered (danger)
0.00%
0 / 1
12
 getIsMeSubquery
0.00% covered (danger)
0.00%
0 / 15
0.00% covered (danger)
0.00%
0 / 1
2
 getTagList
0.00% covered (danger)
0.00%
0 / 9
0.00% covered (danger)
0.00%
0 / 1
72
 deleteByIdArray
0.00% covered (danger)
0.00%
0 / 5
0.00% covered (danger)
0.00%
0 / 1
6
 getDuplicates
0.00% covered (danger)
0.00%
0 / 3
0.00% covered (danger)
0.00%
0 / 1
6
 mergeTags
0.00% covered (danger)
0.00%
0 / 15
0.00% covered (danger)
0.00%
0 / 1
12
 fixDuplicateTag
0.00% covered (danger)
0.00%
0 / 7
0.00% covered (danger)
0.00%
0 / 1
12
 fixDuplicateTags
0.00% covered (danger)
0.00%
0 / 2
0.00% covered (danger)
0.00%
0 / 1
6
1<?php
2
3/**
4 * Table Definition for tags
5 *
6 * PHP version 8
7 *
8 * Copyright (C) Villanova University 2010.
9 *
10 * This program is free software; you can redistribute it and/or modify
11 * it under the terms of the GNU General Public License version 2,
12 * as published by the Free Software Foundation.
13 *
14 * This program is distributed in the hope that it will be useful,
15 * but WITHOUT ANY WARRANTY; without even the implied warranty of
16 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
17 * GNU General Public License for more details.
18 *
19 * You should have received a copy of the GNU General Public License
20 * along with this program; if not, write to the Free Software
21 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
22 *
23 * @category VuFind
24 * @package  Db_Table
25 * @author   Demian Katz <demian.katz@villanova.edu>
26 * @license  http://opensource.org/licenses/gpl-2.0.php GNU General Public License
27 * @link     https://vufind.org Main Site
28 */
29
30namespace VuFind\Db\Table;
31
32use Laminas\Db\Adapter\Adapter;
33use Laminas\Db\Sql\Expression;
34use Laminas\Db\Sql\Predicate\Predicate;
35use Laminas\Db\Sql\Select;
36use VuFind\Db\Row\RowGateway;
37use VuFind\Db\Service\DbServiceAwareInterface;
38use VuFind\Db\Service\DbServiceAwareTrait;
39use VuFind\Db\Service\ResourceTagsServiceInterface;
40use VuFind\Db\Service\TagServiceInterface;
41
42use function count;
43use function is_callable;
44
45/**
46 * Table Definition for tags
47 *
48 * @category VuFind
49 * @package  Db_Table
50 * @author   Demian Katz <demian.katz@villanova.edu>
51 * @license  http://opensource.org/licenses/gpl-2.0.php GNU General Public License
52 * @link     https://vufind.org Main Site
53 */
54class Tags extends Gateway implements DbServiceAwareInterface
55{
56    use DbServiceAwareTrait;
57
58    /**
59     * Constructor
60     *
61     * @param Adapter       $adapter       Database adapter
62     * @param PluginManager $tm            Table manager
63     * @param array         $cfg           Laminas configuration
64     * @param RowGateway    $rowObj        Row prototype object (null for default)
65     * @param bool          $caseSensitive Are tags case sensitive?
66     * @param string        $table         Name of database table to interface with
67     */
68    public function __construct(
69        Adapter $adapter,
70        PluginManager $tm,
71        $cfg,
72        ?RowGateway $rowObj = null,
73        protected $caseSensitive = false,
74        $table = 'tags'
75    ) {
76        parent::__construct($adapter, $tm, $cfg, $rowObj, $table);
77    }
78
79    /**
80     * Get the row associated with a specific tag string.
81     *
82     * @param string $tag           Tag to look up.
83     * @param bool   $create        Should we create the row if it does not exist?
84     * @param bool   $firstOnly     Should we return the first matching row (true)
85     * or the entire result set (in case of multiple matches)?
86     * @param ?bool  $caseSensitive Should tags be case sensitive? (null to use configured default)
87     *
88     * @return mixed Matching row/result set if found or created, null otherwise.
89     */
90    public function getByText($tag, $create = true, $firstOnly = true, $caseSensitive = null)
91    {
92        $cs = $caseSensitive ?? $this->caseSensitive;
93        $result = $this->getDbService(TagServiceInterface::class)->getTagsByText($tag, $cs);
94        if (count($result) == 0 && $create) {
95            $row = $this->createRow();
96            $row->tag = $cs ? $tag : mb_strtolower($tag, 'UTF8');
97            $row->save();
98            return $firstOnly ? $row : [$row];
99        }
100        return $firstOnly ? $result[0] ?? null : $result;
101    }
102
103    /**
104     * Get the tags that match a string
105     *
106     * @param string $text          Tag to look up.
107     * @param string $sort          Sort/search parameter
108     * @param int    $limit         Maximum number of tags
109     * @param ?bool  $caseSensitive Should tags be case sensitive? (null to use configured default)
110     *
111     * @return array Array of \VuFind\Db\Row\Tags objects
112     */
113    public function matchText($text, $sort = 'alphabetical', $limit = 100, $caseSensitive = null)
114    {
115        $callback = function ($select) use ($text) {
116            $select->where->literal('lower(tag) like lower(?)', [$text . '%']);
117            // Discard tags assigned to a user list.
118            $select->where->isNotNull('resource_tags.resource_id');
119        };
120        return $this->getTagList($sort, $limit, $callback, $caseSensitive);
121    }
122
123    /**
124     * Get all resources associated with the provided tag query.
125     *
126     * @param string $q             Search query
127     * @param string $source        Record source (optional limiter)
128     * @param string $sort          Resource field to sort on (optional)
129     * @param int    $offset        Offset for results
130     * @param int    $limit         Limit for results (null for none)
131     * @param bool   $fuzzy         Are we doing an exact or fuzzy search?
132     * @param ?bool  $caseSensitive Should search be case sensitive? (null to use configured default)
133     *
134     * @return array
135     */
136    public function resourceSearch(
137        $q,
138        $source = null,
139        $sort = null,
140        $offset = 0,
141        $limit = null,
142        $fuzzy = true,
143        $caseSensitive = null
144    ) {
145        $cb = function ($select) use ($q, $source, $sort, $offset, $limit, $fuzzy, $caseSensitive) {
146            $columns = [
147                new Expression(
148                    'DISTINCT(?)',
149                    ['resource.id'],
150                    [Expression::TYPE_IDENTIFIER]
151                ),
152            ];
153            $select->columns($columns);
154            $select->join(
155                ['rt' => 'resource_tags'],
156                'tags.id = rt.tag_id',
157                []
158            );
159            $select->join(
160                ['resource' => 'resource'],
161                'rt.resource_id = resource.id',
162                Select::SQL_STAR
163            );
164            if ($fuzzy) {
165                $select->where->literal('lower(tags.tag) like lower(?)', [$q]);
166            } elseif (!($caseSensitive ?? $this->caseSensitive)) {
167                $select->where->literal('lower(tags.tag) = lower(?)', [$q]);
168            } else {
169                $select->where->equalTo('tags.tag', $q);
170            }
171            // Discard tags assigned to a user list.
172            $select->where->isNotNull('rt.resource_id');
173
174            if (!empty($source)) {
175                $select->where->equalTo('source', $source);
176            }
177
178            if (!empty($sort)) {
179                Resource::applySort($select, $sort, 'resource', $columns);
180            }
181
182            if ($offset > 0) {
183                $select->offset($offset);
184            }
185            if (null !== $limit) {
186                $select->limit($limit);
187            }
188        };
189
190        return $this->select($cb);
191    }
192
193    /**
194     * Get tags associated with the specified resource.
195     *
196     * @param string $id            Record ID to look up
197     * @param string $source        Source of record to look up
198     * @param int    $limit         Max. number of tags to return (0 = no limit)
199     * @param int    $list          ID of list to load tags from (null for no
200     * restriction, true for on ANY list, false for on NO list)
201     * @param int    $user          ID of user to load tags from (null for all users)
202     * @param string $sort          Sort type ('count' or 'tag')
203     * @param int    $userToCheck   ID of user to check for ownership (this will
204     * not filter the result list, but rows owned by this user will have an is_me
205     * column set to 1)
206     * @param ?bool  $caseSensitive Should tags be case sensitive? (null to use configured default)
207     *
208     * @return array
209     */
210    public function getForResource(
211        $id,
212        $source = DEFAULT_SEARCH_BACKEND,
213        $limit = 0,
214        $list = null,
215        $user = null,
216        $sort = 'count',
217        $userToCheck = null,
218        $caseSensitive = null
219    ) {
220        return $this->select(
221            function ($select) use (
222                $id,
223                $source,
224                $limit,
225                $list,
226                $user,
227                $sort,
228                $userToCheck,
229                $caseSensitive
230            ) {
231                // If we're looking for ownership, create sub query to merge in
232                // an "is_me" flag value if the selected resource is tagged by
233                // the specified user.
234                if (!empty($userToCheck)) {
235                    $subq = $this->getIsMeSubquery($id, $source, $userToCheck);
236                    $select->join(
237                        ['subq' => $subq],
238                        'tags.id = subq.tag_id',
239                        [
240                            // is_me will either be null (not owned) or the ID
241                            // of the tag (owned by the current user).
242                            'is_me' => new Expression(
243                                'MAX(?)',
244                                ['subq.tag_id'],
245                                [Expression::TYPE_IDENTIFIER]
246                            ),
247                        ],
248                        Select::JOIN_LEFT
249                    );
250                }
251                // SELECT (do not add table prefixes)
252                $select->columns(
253                    [
254                        'id',
255                        'tag' => ($caseSensitive ?? $this->caseSensitive)
256                            ? 'tag' : new Expression('lower(tag)'),
257                        'cnt' => new Expression(
258                            'COUNT(DISTINCT(?))',
259                            ['rt.user_id'],
260                            [Expression::TYPE_IDENTIFIER]
261                        ),
262                    ]
263                );
264                $select->join(
265                    ['rt' => 'resource_tags'],
266                    'rt.tag_id = tags.id',
267                    []
268                );
269                $select->join(
270                    ['r' => 'resource'],
271                    'rt.resource_id = r.id',
272                    []
273                );
274                $select->where(['r.record_id' => $id, 'r.source' => $source]);
275                $select->group(['tags.id', 'tag']);
276
277                if ($sort == 'count') {
278                    $select->order(['cnt DESC', new Expression('lower(tags.tag)')]);
279                } elseif ($sort == 'tag') {
280                    $select->order([new Expression('lower(tags.tag)')]);
281                }
282
283                if ($limit > 0) {
284                    $select->limit($limit);
285                }
286                if ($list === true) {
287                    $select->where->isNotNull('rt.list_id');
288                } elseif ($list === false) {
289                    $select->where->isNull('rt.list_id');
290                } elseif (null !== $list) {
291                    $select->where->equalTo('rt.list_id', $list);
292                }
293                if (null !== $user) {
294                    $select->where->equalTo('rt.user_id', $user);
295                }
296            }
297        );
298    }
299
300    /**
301     * Get a list of all tags generated by the user in favorites lists. Note that
302     * the returned list WILL NOT include tags attached to records that are not
303     * saved in favorites lists.
304     *
305     * @param string $userId        User ID to look up.
306     * @param string $resourceId    Filter for tags tied to a specific resource (null for no filter).
307     * @param int    $listId        Filter for tags tied to a specific list (null for no filter).
308     * @param string $source        Filter for tags tied to a specific record source (null for no filter).
309     * @param ?bool  $caseSensitive Should tags be case sensitive? (null to use configured default)
310     *
311     * @return \Laminas\Db\ResultSet\AbstractResultSet
312     */
313    public function getListTagsForUser(
314        $userId,
315        $resourceId = null,
316        $listId = null,
317        $source = null,
318        $caseSensitive = null
319    ) {
320        $callback = function ($select) use ($userId, $resourceId, $listId, $source, $caseSensitive) {
321            $select->columns(
322                [
323                    'id' => new Expression(
324                        'min(?)',
325                        ['tags.id'],
326                        [Expression::TYPE_IDENTIFIER]
327                    ),
328                    'tag' => ($caseSensitive ?? $this->caseSensitive)
329                        ? 'tag' : new Expression('lower(tag)'),
330                    'cnt' => new Expression(
331                        'COUNT(DISTINCT(?))',
332                        ['rt.resource_id'],
333                        [Expression::TYPE_IDENTIFIER]
334                    ),
335                ]
336            );
337            $select->join(
338                ['rt' => 'resource_tags'],
339                'tags.id = rt.tag_id',
340                []
341            );
342            $select->join(
343                ['r' => 'resource'],
344                'rt.resource_id = r.id',
345                []
346            );
347            $select->join(
348                ['ur' => 'user_resource'],
349                'r.id = ur.resource_id',
350                []
351            );
352            $select->group(['tag'])->order([new Expression('lower(tag)')]);
353
354            $select->where->equalTo('ur.user_id', $userId)
355                ->equalTo('rt.user_id', $userId)
356                ->equalTo(
357                    'ur.list_id',
358                    'rt.list_id',
359                    Predicate::TYPE_IDENTIFIER,
360                    Predicate::TYPE_IDENTIFIER
361                );
362
363            if (null !== $source) {
364                $select->where->equalTo('r.source', $source);
365            }
366
367            if (null !== $resourceId) {
368                $select->where->equalTo('r.record_id', $resourceId);
369            }
370            if (null !== $listId) {
371                $select->where->equalTo('rt.list_id', $listId);
372            }
373        };
374        return $this->select($callback);
375    }
376
377    /**
378     * Get tags assigned to a user list.
379     *
380     * @param int   $listId        List ID
381     * @param ?int  $userId        User ID to look up (null for no filter).
382     * @param ?bool $caseSensitive Should tags be case sensitive? (null to use configured default)
383     *
384     * @return \Laminas\Db\ResultSet\AbstractResultSet
385     */
386    public function getForList($listId, $userId = null, $caseSensitive = null)
387    {
388        $callback = function ($select) use ($listId, $userId, $caseSensitive) {
389            $select->columns(
390                [
391                    'id' => new Expression(
392                        'min(?)',
393                        ['tags.id'],
394                        [Expression::TYPE_IDENTIFIER]
395                    ),
396                    'tag' => ($caseSensitive ?? $this->caseSensitive)
397                        ? 'tag' : new Expression('lower(tag)'),
398                ]
399            );
400            $select->join(
401                ['rt' => 'resource_tags'],
402                'tags.id = rt.tag_id',
403                []
404            );
405            $select->where->equalTo('rt.list_id', $listId);
406            $select->where->isNull('rt.resource_id');
407            if ($userId) {
408                $select->where->equalTo('rt.user_id', $userId);
409            }
410            $select->group(['tag'])->order([new Expression('lower(tag)')]);
411        };
412        return $this->select($callback);
413    }
414
415    /**
416     * Get a subquery used for flagging tag ownership (see getForResource).
417     *
418     * @param string $id          Record ID to look up
419     * @param string $source      Source of record to look up
420     * @param int    $userToCheck ID of user to check for ownership
421     *
422     * @return Select
423     */
424    protected function getIsMeSubquery($id, $source, $userToCheck)
425    {
426        $sub = new Select('resource_tags');
427        $sub->columns(['tag_id'])
428            ->join(
429                // Convert record_id to resource_id
430                ['r' => 'resource'],
431                'resource_id = r.id',
432                []
433            )
434            ->where(
435                [
436                    'r.record_id' => $id,
437                    'r.source' => $source,
438                    'user_id' => $userToCheck,
439                ]
440            );
441        return $sub;
442    }
443
444    /**
445     * Get a list of tags based on a sort method ($sort)
446     *
447     * @param string   $sort          Sort/search parameter
448     * @param int      $limit         Maximum number of tags (default = 100, < 1 = no limit)
449     * @param callback $extra_where   Extra code to modify $select (null for none)
450     * @param ?bool    $caseSensitive Should tags be case sensitive? (null to use configured default)
451     *
452     * @return array Tag details.
453     */
454    public function getTagList($sort, $limit = 100, $extra_where = null, $caseSensitive = null)
455    {
456        $callback = function ($select) use ($sort, $limit, $extra_where, $caseSensitive) {
457            $select->columns(
458                [
459                    'id',
460                    'tag' => ($caseSensitive ?? $this->caseSensitive)
461                        ? 'tag' : new Expression('lower(tag)'),
462                    'cnt' => new Expression(
463                        'COUNT(DISTINCT(?))',
464                        ['resource_tags.resource_id'],
465                        [Expression::TYPE_IDENTIFIER]
466                    ),
467                    'posted' => new Expression(
468                        'MAX(?)',
469                        ['resource_tags.posted'],
470                        [Expression::TYPE_IDENTIFIER]
471                    ),
472                ]
473            );
474            $select->join(
475                'resource_tags',
476                'tags.id = resource_tags.tag_id',
477                []
478            );
479            if (is_callable($extra_where)) {
480                $extra_where($select);
481            }
482            $select->group(['tags.id', 'tags.tag']);
483            switch ($sort) {
484                case 'alphabetical':
485                    $select->order([new Expression('lower(tags.tag)'), 'cnt DESC']);
486                    break;
487                case 'popularity':
488                    $select->order(['cnt DESC', new Expression('lower(tags.tag)')]);
489                    break;
490                case 'recent':
491                    $select->order(
492                        [
493                            'posted DESC',
494                            'cnt DESC',
495                            new Expression('lower(tags.tag)'),
496                        ]
497                    );
498                    break;
499            }
500            // Limit the size of our results
501            if ($limit > 0) {
502                $select->limit($limit);
503            }
504        };
505
506        $tagList = [];
507        foreach ($this->select($callback) as $t) {
508            $tagList[] = [
509                'tag' => $t->tag,
510                'cnt' => $t->cnt,
511            ];
512        }
513        return $tagList;
514    }
515
516    /**
517     * Delete a group of tags.
518     *
519     * @param array $ids IDs of tags to delete.
520     *
521     * @return void
522     */
523    public function deleteByIdArray($ids)
524    {
525        // Do nothing if we have no IDs to delete!
526        if (empty($ids)) {
527            return;
528        }
529
530        $callback = function ($select) use ($ids) {
531            $select->where->in('id', $ids);
532        };
533        $this->delete($callback);
534    }
535
536    /**
537     * Get a list of duplicate tags (this should never happen, but past bugs
538     * and the introduction of case-insensitive tags have introduced problems).
539     *
540     * @param ?bool $caseSensitive Should tags be case sensitive? (null to use configured default)
541     *
542     * @return mixed
543     */
544    public function getDuplicates($caseSensitive = null)
545    {
546        $callback = function ($select) use ($caseSensitive) {
547            $select->columns(
548                [
549                    'tag' => new Expression(
550                        'MIN(?)',
551                        ['tag'],
552                        [Expression::TYPE_IDENTIFIER]
553                    ),
554                    'cnt' => new Expression(
555                        'COUNT(?)',
556                        ['tag'],
557                        [Expression::TYPE_IDENTIFIER]
558                    ),
559                    'id' => new Expression(
560                        'MIN(?)',
561                        ['id'],
562                        [Expression::TYPE_IDENTIFIER]
563                    ),
564                ]
565            );
566            $select->group(
567                ($caseSensitive ?? $this->caseSensitive) ? 'tag' : new Expression('lower(tag)')
568            );
569            $select->having('COUNT(tag) > 1');
570        };
571        return $this->select($callback);
572    }
573
574    /**
575     * Support method for fixDuplicateTag() -- merge $source into $target.
576     *
577     * @param string $target Target ID
578     * @param string $source Source ID
579     *
580     * @return void
581     */
582    protected function mergeTags($target, $source)
583    {
584        // Don't merge a tag with itself!
585        if ($target === $source) {
586            return;
587        }
588        $table = $this->getDbTable('ResourceTags');
589        $resourceTagsService = $this->getDbService(ResourceTagsServiceInterface::class);
590        $result = $table->select(['tag_id' => $source]);
591
592        foreach ($result as $current) {
593            // Move the link to the target ID:
594            $resourceTagsService->createLink(
595                $current->resource_id,
596                $target,
597                $current->user_id,
598                $current->list_id,
599                $current->getPosted()
600            );
601
602            // Remove the duplicate link:
603            $table->delete($current->toArray());
604        }
605
606        // Remove the source tag:
607        $this->delete(['id' => $source]);
608    }
609
610    /**
611     * Support method for fixDuplicateTags()
612     *
613     * @param string $tag           Tag to deduplicate.
614     * @param ?bool  $caseSensitive Should tags be case sensitive? (null to use configured default)
615     *
616     * @return void
617     */
618    protected function fixDuplicateTag($tag, $caseSensitive = null)
619    {
620        // Make sure this really is a duplicate.
621        $result = $this->getDbService(TagServiceInterface::class)
622            ->getTagsByText($tag, $caseSensitive ?? $this->caseSensitive);
623        if (count($result) < 2) {
624            return;
625        }
626
627        $first = $result[0];
628        foreach ($result as $current) {
629            $this->mergeTags($first->getId(), $current->getId());
630        }
631    }
632
633    /**
634     * Repair duplicate tags in the database (if any).
635     *
636     * @param ?bool $caseSensitive Should tags be case sensitive? (null to use configured default)
637     *
638     * @return void
639     */
640    public function fixDuplicateTags($caseSensitive = null)
641    {
642        foreach ($this->getDuplicates($caseSensitive) as $dupe) {
643            $this->fixDuplicateTag($dupe->tag, $caseSensitive);
644        }
645    }
646}