Searching with Postgres

[LoayGhreeb]

Closes #11823
Closes #11798
Closes #10490

Resolves https://discourse.jabref.org/t/citations-missing-in-action-in-jabref-5-16/4525/11

Mandatory checks

• Change in CHANGELOG.md described in a way that is understandable for the average user (if applicable)
• Tests created for changes (if applicable)
• Manually tested changed features in running JabRef (always required)
• Screenshots added in PR description (for UI changes)
• Checked developer's documentation: Is the information available and up to date? If not, I outlined it in this pull request.
• Checked documentation: Is the information available and up to date? If not, I created an issue at https://github.com/JabRef/user-documentation/issues or, even better, I submitted a pull request to the documentation repository.

[InAnYan]

I have couple of questions:

1. Is the Postgres server one for every library?
2. One library - one DB?
3. What is the schema of the DB? If you can explain it shortly.
4. Where the Postgres data is stored? (Somwhere local to user, alongside BIB)

I was thinking if I can use this Postgres service as a vector database for my AI features

[koppor]

I have couple of questions:

1. Is the Postgres **server** one for every library?

1 for running JabRef

2. One library - one **DB**?

One table per library. We kept it very simple for the search.

3. What is the schema of the DB? If you can explain it shortly.

Very simple - just one table

                CREATE TABLE IF NOT EXISTS "%s" (
                    %s TEXT NOT NULL,
                    %s TEXT NOT NULL,
                    %s TEXT,
                    PRIMARY KEY (%s, %s)
                )
                """.formatted(tableName,
                PostgreConstants.ENTRY_ID,
                PostgreConstants.FIELD_NAME,
                PostgreConstants.FIELD_VALUE,
                PostgreConstants.ENTRY_ID,
                PostgreConstants.FIELD_NAME));

Feel free to create other tables :)

4. Where the Postgres data is stored? (Somwhere local to user, alongside BIB)

In the AppData directory. Even "temp". Thus, very fragile ^^. Not sure if one can change it.

Reason for the fragile setting: The solution we are using is intended for testing. The java-german channel said: WTF, please use DuckDB. But we could not manage to get DuckDB up and running that fast. Moreover, Postgres has more plugins for speed. And third, we also support Postgres for remote storage.

You could also think of hooking into the remote postgres. Meaning: Advanced features only work with a remote postgres. (Although I like using the local postgres database. Keeps complexity for the user to a minimum. Only 500 MB additional storage is needed...)

[github-actions]

The build for this PR is no longer available. Please visit https://builds.jabref.org/main/ for the latest build.

[Siedlerchr]

Yeah finally!

[calixtus]

Yay!