Mer0me
commented
1 year ago We have 600k lines on oc_filecache table and file search is almost unusable, taking more than 30 seconds to show results. On top of that, Nextcloud unified search is looking for exact words, in the same order as user input which is not very efficient.
So, we have decided to write our own search form, outside of Nextcloud (added to the app menu using external sites app, as we are not able to make a real Nextcloud App), but using the same database (mariadb here). We've added some extra search functionalities :
- search by file id
- search by user name
- search between 2 dates
- search all the input terms in various order
- search is accent insensitive
- search is (almost) plural insensitive (final 's' is added or removed from each term to maximize chances to get wanted results)
- export results in CSV
Search is based on the user profile, so only the files shared to, or owned by the connected user appear on the results.
Surprisingly, our search engine is significantly faster than the official nextcloud one. It takes about 2 seconds to display results. There is certainly a lot of possible optimizations to do.
Feel free to comment, use, adapt this bunch of code as we are not going to make an app or whatever with it. If it may helps someone...
<?php
/*
Nextcloud alternative search engine offering :
- search by file id
- search by user name
- search between 2 dates
- search all the input terms in various order
- search is accent insensitive
- export results in CSV
search results are based on the user in $_SESSION["username"] variable
you have to write your own user authentication to make it works...
*/
session_start();
// function returning the icon corresponding to a mime type
function getImgExtension($fileName, $mime)
{
$file = explode('.', $fileName);
switch ($file[sizeof($file) - 1]) {
case 'doc':
return 'icons/doc.png';
case 'docx':
return 'icons/doc.png';
case 'jpg':
return 'icons/jpg.png';
case 'jpeg':
return 'icons/jpg.png';
case 'mp3':
return 'icons/mp3.png';
case 'pdf':
return 'icons/pdf.png';
case 'png':
return 'icons/png.png';
case 'ppt':
return 'icons/ppt.png';
case 'pptx':
return 'icons/ppt.png';
case 'csv':
return 'icons/csv.png';
case 'csv':
return 'icons/csv.png';
case 'csvx':
return 'icons/csv.png';
case 'zip':
return 'icons/zip.png';
case '7z':
return 'icons/zip.png';
case 'eml':
return 'icons/mail.png';
case 'rar':
return 'icons/rar.png';
case 'km':
return 'icons/mind.png';
default:
if ($mime == 2) {
return 'icons/folder.png';
} else {
return 'icons/unknown.png';
}
}
}
// Get rid of the accented letters
function stripAccents($str) {
return strtr(utf8_decode($str), utf8_decode("àáâãäçèéêëìíîïñòóôõöùúûüýÿÀÁÂÃÄÇÈÉÊËÌÍÎÏÑÒÓÔÕÖÙÚÛÜÝ'"), "aaaaaceeeeiiiinooooouuuuyyAAAAACEEEEIIIINOOOOOUUUUY ");
}
$bdd = mysqli_connect("host", "user", "password", "database");
if (!$bdd) {
die('Connection error (' . mysqli_connect_errno() . ') '
. mysqli_connect_error());
}
if (isset($_POST['documentName']) || isset($_POST['documentOwnerUsername'])) {
// Constructing the query used to retrieve all visible mount points to the user, excluding trash_bin
$sql = "SELECT `storage_id`, `root_id`, `user_id`, `mount_point`, `mount_id`, `f`.`path`, `mount_provider_class`,id FROM `oc_mounts` `m` INNER JOIN `oc_filecache` `f` ON `m`.`root_id` = `f`.`fileid` WHERE `user_id` = '" . $_SESSION["username"] . "' and path not like '%trashbin%' order by path asc";
$mounts = mysqli_query($bdd, $sql);
$paths = "(1=0";
$storage="";
while ($mount = mysqli_fetch_array($mounts)) {
if ($storage != $mount["storage_id"] || strpos($mount["path"], $path) === false) {
$paths .= " OR (path like '" . addslashes($mount["path"]) . "%' and storage=" . $mount["storage_id"] . " and oc_mounts.id=" . $mount["id"] . ")\n";
$storage = $mount["storage_id"];
$path = $mount["path"];
}
}
$paths .= ")";
// Constructing the query used to retrieve all files matching the search query (all terms must be present, in any order)
$criteres = "((1=1";
$debug = false;
if ($_POST['documentName'] != "") {
$termes = explode(" ", stripAccents(strtolower($_POST['documentName'])));
foreach ($termes as $terme) {
// lots of chained sql "replace" here because of normalization of filename
$criteres .= " and (concat(' ',replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(lower(name),'é','e'),'è','e'),'ë','e'),'ê','e'),'ô','o'),'à','a'),'ï','i'),'î','i'),'â','a'),'ù','u'),'\'',' '),'.',' '),' ') like '% " . addslashes($terme) . " %'\n";
// eventually removing trailing 's' to search term without plural
if (substr($terme, -1) == "s") {
$criteres .= " or concat(' ',replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(lower(name),'é','e'),'è','e'),'ë','e'),'ê','e'),'ô','o'),'à','a'),'ï','i'),'î','i'),'â','a'),'ù','u'),'\'',' '),'.',' '),' ') like '% " . addslashes(substr($terme, 0, -1)) . " %')\n";
} else {
// replacing trailing "aux" by "al", as plural of most word finishing by "al" is "aux" in french (cheval => chevaux)
if (substr($terme, -3) == "aux") {
$criteres .= " or concat(' ',replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(lower(name),'é','e'),'è','e'),'ë','e'),'ê','e'),'ô','o'),'à','a'),'ï','i'),'î','i'),'â','a'),'ù','u'),'\'',' '),'.',' '),' ') like '% " . addslashes(substr($terme, 0, -3)) . "al %')\n";
}
// adding a trailing 's' to search plural too
else {
$criteres .= " or concat(' ',replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(lower(name),'é','e'),'è','e'),'ë','e'),'ê','e'),'ô','o'),'à','a'),'ï','i'),'î','i'),'â','a'),'ù','u'),'\'',' '),'.',' '),' ') like '% " . addslashes($terme) . "_ %')\n";
}
}
}
}
// searching by fileid
$criteres .= ") or fileid='" . addslashes($_POST["documentName"]) . "')";
// base search query, excluding file versions and trashbin
$sqlid = "select fileid from oc_filecache where " . $criteres . " and path not like 'files_versions%' and path not like 'files_trashbin%'";;
// if dates have been specified, search only files modified between these dates
if ($_POST["date"] != "") {
$splDate = explode("&", $_POST['date']);
$dStart = DateTime::createFromFormat('d-m-Y', $splDate[0]);
$dEnd = DateTime::createFromFormat('d-m-Y', $splDate[1]);
$sqlid .= " AND mtime BETWEEN " . $dStart->getTimestamp() . " AND " . $dEnd->getTimestamp();
}
$resid = mysqli_query($bdd, $sqlid);
$listeid = "-1";
// Constructing the list of matching id, regardless of the user right access for the moment
while ($id = mysqli_fetch_array($resid)) {
$listeid .= "," . $id["fileid"];
}
// Filtering results with visible mount points
$sql = "select fileid,name,mtime,ldap_dn,mimetype,mount_point,path,oc_storages.id as proprio,parent from oc_filecache,oc_storages,oc_ldap_user_mapping,oc_mounts
where storage=numeric_id and (owncloud_name=substring_index(oc_storages.id,'::',-1) or (storage = 192 and owncloud_name='adminsi')) and oc_mounts.user_id='" . $_SESSION["username"] . "' and
" . $paths . " AND fileid in (" . $listeid . ")";
// If a username has been specified, filtering by username
if ($_POST["documentOwner"] != "") {
$sql .= " AND owncloud_name='" . $_POST["documentOwner"] . "' ";
}
// ordering results by modification time
$sql .= " order by mtime +0 desc";
$res = mysqli_query($bdd, $sql);
$nbdoc = mysqli_num_rows($res);
}
?>
<!doctype html>
<html lang="fr">
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<!-- Bootstrap CSS -->
<link href="bootstrap/css/bootstrap.min.css" rel="stylesheet">
<link href="transition.css" rel="stylesheet">
<link rel="stylesheet" type="text/css" href="https://cdn.jsdelivr.net/npm/daterangepicker/daterangepicker.css" />
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@tarekraafat/autocomplete.js@10.2.7/dist/css/autoComplete.min.css">
<title>Moteur de recherche cloud PMA</title>
</head>
<body id="body">
<?php
if (isset($_SESSION['username'])) { ?>
<div class="container">
<div class="row">
<a href="logout.php" style="text-align: right; color: grey;">Disconnect</a>
</div>
<div class="row">
<h1>Advanced search</h1>
</div>
<form method="POST" autocomplete="off">
<div class="row mt-2">
<div class="row">
<div class="col mt-3">
<label for="documentName" class="form-label" id="documentNameLabel">Filename or file id</label>
<input type="text" class="form-control" id="documentName" placeholder="space separated terms" name="documentName" value="<?php echo (isset($_POST['documentName']) ? $_POST['documentName'] : '') ?>">
</div>
<div class="col mt-3">
<label for="documentOwner" class="form-label">Owner</label>
<input type="text" name="documentOwner" id="documentOwner" class="form-control">
<span id="search_result"></span>
</div>
<div class="col m-3">
<div class="row">
<div class="col">
<label for="date" class="form-label">Modification time</label>
<div id="reportrange" style="background: #fff; cursor: pointer; padding: 5px 10px; border: 1px solid #ccc; width: 100%">
<i class="fa fa-calendar"></i>
<span></span> <i class="fa fa-caret-down"></i>
</div>
<input type="hidden" value="" name="date" id="date">
</div>
</div>
</div>
</div>
<div class="row">
<div class="col">
<input class="btn btn-primary" type="submit" value="Rechercher">
</div>
<div class="col-10"></div>
</div>
</form>
<div class="row mt-4">
<hr style="color:#932D30">
</div>
<?php if (isset($nbdoc)) { ?>
<div class="row mt-3">
<?= $nbdoc ?> result(s)
</div>
<div class="row mt-5 align-center">
<div class="col">
<?php
$_SESSION["csv"] = "CID;Filename;Owner;Modification time;Path\n";
// We use a LDAP backend, if you don't, you should modify this
foreach ($res as $r) {
$owner = explode(",", $r['ldap_dn'], 2);
$owner = str_replace("cn=", "", $owner[0]);
?>
<div class="card mt-2 mb-2">
<a href="<?= 'https://yourcloud.com/index.php/f/' . $r['fileid'] ?>" target="_blank">
<div class="card-body">
<h5 class="card-title"><img src="<?= getImgExtension($r['name'], $r['mimetype']) ?>" alt="" width="30" height="30"> <?= $r['name'] ?></h5>
<p class="card-text"><span style="color: 808080;">
<?php
// Computing the file path for this user when possible
$tchemin = explode("/", $r["path"]);
$chemin = "";
if (strpos($r["path"], "groupfolders") !== false) {
for ($i = 2; $i < sizeof($tchemin) - 1; $i++) {
$chemin .= $tchemin[$i] . "/";
}
} else {
$correspondance = false;
for ($i = 1; $i < sizeof($tchemin) - 1; $i++) {
$chemin .= $tchemin[$i] . "/";
if ($p[1] != $_SESSION["username"] && $tchemin[$i] == substr($r['mount_point'], strlen($_SESSION["username"]) + 8, -1)) {
$chemin = "";
$correspondance = true;
}
}
}
// If the shared file have been renamed, we cannot find the real path
if ($chemin == "") $chemin = "/";
if ($r["name"] == substr($r['mount_point'], strlen($_SESSION["username"]) + 8, -1)) {
$dans = " - <a href=\"https://yourcloud.com/index.php/apps/files/\">Root folder</a>";
$danscsv = "Root folder";
} else {
$dans = " - <a href=\"https://yourcloud.com/index.php/f/" . $r['parent'] . "\" target=\"_blank\">In the folder " . substr($r['mount_point'], strlen($_SESSION["username"]) + 8) . $chemin . "</a>";
$danscsv = "In the folder " . substr($r['mount_point'], strlen($_SESSION["username"]) + 8) . $chemin;
}
if ($p[1] != $_SESSION["username"] && !$correspondance && strpos($r["path"], "groupfolders") === false) {
$dans = " - <a href=\"https://yourcloud.com/index.php/f/" . $r['parent'] . "\" target=\"_blank\">In the folder " . substr($r['mount_point'], strlen($_SESSION["username"]) + 8) . ".../" . $tchemin[sizeof($tchemin) - 2] . "/</a>";
$danscsv = "In the folder " . substr($r['mount_point'], strlen($_SESSION["username"]) + 8) . ".../" . $tchemin[sizeof($tchemin) - 2] . "/";
}
echo ($owner == "admin") ? "Group folder" : mb_strtoupper($owner) ?> - Last modification : <?= date('d/m/Y H:i', $r['mtime']); ?> <?= $dans ?></span> </p>
</div>
</a>
</div>
<?php
$_SESSION["csv"] .= $r['fileid'] . ";" . $r['name'] . ";" . (($owner == "admin") ? "Group folder" : mb_strtoupper($owner)) . ";" . date('d/m/Y H:i', $r['mtime']) . ";" . $danscsv . "\n";
}
// you may need to write this script
echo ("<a href=\"csv.php\">Click here to get this list in CSV format</a>");
?>
</div>
</div>
<?php } ?>
</div>
</div>
<?php } else {
// You should write here your own login form
$_SESSION["username"]="sample_user";
} ?>
<script type="text/javascript" src="js/jquery.min.js"></script>
<script type="text/javascript" src="js/moment.min.js"></script>
<script type="text/javascript" src="js/daterangepicker.min.js"></script>
<script type="text/javascript" src="js/autoComplete.min.js"></script>
<script type="text/javascript">
$(function() {
// double date picker
var start = moment().subtract(365, 'days');
var end = moment();
function cb(start, end) {
$('#reportrange span').html(start.format('DD/MM/YYYY') + ' - ' + end.format('DD/MM/YYYY'));
document.getElementById('date').value = start.format('DD-MM-YYYY') + '&' + end.format('DD-MM-YYYY');
}
$('#reportrange').daterangepicker({
startDate: start,
endDate: end,
ranges: {
"Today": [moment(), moment()],
'Last 30 days': [moment().subtract(29, 'days'), moment()],
'This month': [moment().startOf('month'), moment().endOf('month')],
'Last month': [moment().subtract(1, 'month').startOf('month'), moment().subtract(1, 'month').endOf('month')],
'3 last months': [moment().subtract(3, 'month'), moment()],
'This year': [moment().startOf('year'), moment().endOf('year')],
'Last year': [moment().subtract(365, 'days'), moment()],
'Whenever': [moment().subtract(100, 'years'), moment()]
}
}, cb);
cb(start, end);
});
</script>
</div>
</body>
</html>
XueSheng-GIT
gennaios
icewind1991
starypatyk
⚠️ This issue respects the following points: ⚠️
Bug description
Global/Unified search for files and tags is slow if a lot of shares are present. Search result for fulltextsearch, collectives, talk, deck and mails popup nearly instantly, but result for files and tags takes ages until they appear (approx. 30 seconds). Tested accounts have approx. 600 shares (according to
oc_sharesshare_withcolumn. Talk is used a lot for sharing photos, which is probably the main reason for the amount of shares.Postgres log shows slow query... see below.
Doing the same on a cloned server instance without these shares (shares removed), the search results for files and tags appear within 2 seconds.
Just want to reference https://github.com/nextcloud/server/issues/23835 which really improved things in regards to search speed. Unfortunately it seems this issue is not fixed if a lot of shares are present.
Steps to reproduce
Expected behavior
Search result for files and tags should appear as fast as possible, even if a lot of shares are received.
Installation method
Community Manual installation with Archive
Operating system
Debian/Ubuntu
PHP engine version
PHP 8.1
Web server
Apache (supported)
Database engine version
PostgreSQL
Is this bug present after an update or on a fresh install?
Updated to a major version (ex. 22.2.3 to 23.0.1)
Are you using the Nextcloud Server Encryption module?
None
What user-backends are you using?
Configuration report
List of activated Apps
Nextcloud Signing status
Nextcloud Logs
No response
Additional info
Postgres log shows slow search query for files and tags: slow_search.log