Issue search for part of a word, case sensitivity

[johannes-nivus]

• Gitea version (or commit ref): 1.12.5 built with GNU Make 4.1, go1.14.9
• Git version: 2.25.1
• Operating system: Ubuntu 20.04.1 LTS, no docker
• Database (use [x]):
  • PostgreSQL
  • MySQL
  • MSSQL
  • SQLite
• Can you reproduce the bug at https://try.gitea.io:
  • Yes (provide example URL)
  • No
• Log gist:

Description

Searching for parts of a word in Issues is not possible. I tried both bleeve and db as indexer. Even worse: Using db, the search becomes case sensitive.
Since elastic search seems to have this problem as well (see #11977), I have not tried this yet.

For us it's really a problem, since German language uses many compound nouns. And you would always have to search for the exact composition now.

Since it seems to work in https://try.gitea.io, which search indexer and which database is used there?

Screenshots

All screenshots done with db as indexer, but search for part of word is not possible with bleeve as well.

[lunny]

try.gitea.io use sqlite as database.

[johannes-nivus]

try.gitea.io use sqlite as database.

Is there a possibility to migrate an existing database from postgres to sqlite? I had only chosen postgres because of a parallel Open Project installation.

[johannes-nivus]

Attention: I have neither experience with databases nor with go.
But nevertheless a question:
What would need to be done to use pg_trgm and the % operator for issue searching? Am I right that currently LIKE is used (which would explain the sudden case sensitivity with postgresql)?
If this would be possible at all, it would be nice to have this configurable in app.ini.

[johannes-nivus]

Is there a possibility to migrate an existing database from postgres to sqlite? I had only chosen postgres because of a parallel Open Project installation.

I managed to migrate from postgres to sqlite, by using
./gitea dump -c /path/to/app.ini -d sqlite3
Then I extracted only the database dump and used
sqlite3 gitea.db <gitea-db.sql
After editing app.ini and restarting gitea everything is working for now.

[ngugcx]

Why is part-word search not supported when using Postgres?

[ngugcx]

Is there a possibility to migrate an existing database from postgres to sqlite? I had only chosen postgres because of a parallel Open Project installation.

I managed to migrate from postgres to sqlite, by using
./gitea dump -c /path/to/app.ini -d sqlite3
Then I extracted only the database dump and used
sqlite3 gitea.db <gitea-db.sql
After editing app.ini and restarting gitea everything is working for now.

Did you meet this problem?

bash-5.0$ sqlite3 gitea.db <gitea-db.sql
Error: near line 16: no such function: setval
Error: near line 23: no such function: setval
Error: near line 30: no such function: setval
Error: near line 72: no such function: setval
Error: near line 78: no such function: setval
Error: near line 84: no such function: setval
Error: near line 116: no such function: setval
Error: near line 120: no such function: setval
Error: near line 153: no such function: setval
Error: near line 157: no such function: setval
Error: near line 161: no such function: setval
Error: near line 1380: no such function: setval

[lunny]

Why is part-word search not supported when using Postgres?

part word should have been supported by Postgres but not Case sensitivity. Maybe we need a PR to improve issue db indexer to support that.

[johannes-nivus]

part word should have been supported by Postgres but not Case sensitivity. Maybe we need a PR to improve issue db indexer to support that.

Part of word search had not been possible with Postgres when I opened the issue (see screenshots).
Postgres: No part of word, case sensitive.
Bleve: No part of word.

[johannes-nivus]

Did you meet this problem?

bash-5.0$ sqlite3 gitea.db <gitea-db.sql
Error: near line 16: no such function: setval
Error: near line 23: no such function: setval
Error: near line 30: no such function: setval
Error: near line 72: no such function: setval
Error: near line 78: no such function: setval
Error: near line 84: no such function: setval
Error: near line 116: no such function: setval
Error: near line 120: no such function: setval
Error: near line 153: no such function: setval
Error: near line 157: no such function: setval
Error: near line 161: no such function: setval
Error: near line 1380: no such function: setval

Yes, but I ignored it. And it worked.

[ngugcx]

Any update here?

[zeripath]

For postgres we would just have to emit ILIKE instead of LIKE

[zeripath]

However a cross dB solution would be to change

models.SearchIssueIDsByKeyword()

To use lower(...) on the fields and lower case the keyword too.

[zeripath]

This would be a three line pr

[ngugcx]

@zeripath , I prefer upper() over lower().
https://docs.microsoft.com/en-us/dotnet/fundamentals/code-analysis/quality-rules/ca1308
https://en.wikipedia.org/wiki/%C3%9F#Capital_form