METADATA Table - Structure Update with JSON

[robyquin]

METADATA table

I noticed some aspects of this software that could be improved.

NOTE: The following suggestions are not caused by any malfunction.

Client:

JabRef 5.15--2024-07-10--1eb3493
Windows 10 10.0 amd64 
Java 21.0.2 
JavaFX 22.0.1+7

Server:

PostgreSQL 13.22 (Raspbian 13.22-0+deb11u1) on Raspbian 10.2.1-6+rpi1, 32-bit

Current METADATA table

erDiagram
METADATA {
        string KEY
        string VALUE
    }

Loading

1. METADATA table haven't a PRIMARY KEY
2. Fields KEY, VALUES aren't homogeneous,
   there are:
   • fileDirectoryLatex (user+machine): they can to be more than 1.
   • VersionDBStructure: versione DB Structure
   • databaseType: bib
   • grouping: similar to table

Suggestions fileDirectoryLatex ---> ConfigUsers

From pair KEY+VALUE to Table ConfigUsers

erDiagram
ConfigUsers {
        string machine PK
        string user PK
        string path_folder
    }

Loading

Current grouping (pair KEY+VALUE)

In this database version grouping (pair KEY+VALUE) must be unique, and represents hierarchical data.

grouping defines lateral menu in JabRef.

grouping currently looks like this:

erDiagram
grouping {
        integer Level
        string Type
        string Item_name
        integer Hierarchical_context
        string criteria
        string Keyword_Delimiter_**OR**_case_sensitive
        string Hierarchical_Keyword_Delimiter_**OR**_regular_expression
        integer unknown
        string Color
        string Icon
        string Description
    }

Loading

Attributes that define class group are two:

• Level defines hierarchy but without reference to its parent or children (Hierarchical and positional system)
• Type defines typology of group with own properties:
  • AllEntriesGroup
  • AutomaticPersonsGroup
  • AutomaticKeywordGroup
  • StaticGroup
  • Other

I found an ambiguity in columns 6 and 7, because conceptually different data is stored in them:

• Culumn 6: Keyword_Delimiter OR case_sensitive
• Culumn 7: Hierarchical_Keyword_Delimiter OR regular_expression

Suggestions grouping

grouping represents hierarchical data or nested data, so why don't we use json type?

• Not all databases handle json the same way, but Java can.
• Today there is a requirement to have only one hierarchical menu, in the near future it may be necessary to leave each user the possibility to create their own grouping.
  • we could embed the key into the table ConfigUsers

erDiagram
ConfigUsers {
        string machine PK
        string user PK
        string path_folder
        json grouping
    }

Loading

An example of structure json:

{
  "groups": [
    {
      "type": "AllEntriesGroup",
      "name": "AllEntries",
      "children": [
        {
          "type": "AutomaticPersonsGroup",
          "name": "By Author",
          "field": "author",
          "color": "0x800080ff",
          "icon": "ACCOUNT",
          "children": []
        },
        {
          "type": "AutomaticKeywordGroup",
          "name": "By Keywords",
          "field": "keywords",
          "color": "0x800080ff",
          "icon": "KEY_VARIANT",
          "children": []
        },
        {
          "type": "StaticGroup",
          "name": "Categories",
          "color": "0x800080ff",
          "icon": "FOLDER_MULTIPLE_PLUS",
          "children": [
            {
              "type": "StaticGroup",
              "name": "Article",
              "color": "0xff00ffff",
              "icon": "ACCOUNT_ALERT"
            },
            {
              "type": "SearchGroup",
              "name": "Book",
              "field": "book",
              "color": "0xd329c8ff",
              "icon": "FOLDER_MULTIPLE_PLUS"
            }
          ]
        }
      ]
    }
  ]
}

Hierarchical menu

Jabref Group - Menu

Json in databases:

• SQLite
• PostgreSQL
• MariaDB
• Oracle DB

[robyquin]

jabref/CHANGELOG.md

Line 209 in d1553ba

- We removed support for MySQL/MariaDB and Oracle. [#12990](https://github.com/JabRef/jabref/pull/12990)

[koppor]

Maybe first work on #10371 - to know about the nested structures and maybe come up with a refined schema (maybe supporting JSON for the metadata?)

[robyquin]

Maybe this could help us:

#9376 (comment)

[koppor]

Please first investigate the current code base - and not JabRef 5.15.

You can hook into the PR #11879 to improve/fix the synchronization.

---

Background

Current thinking of JabRef is to have a shared text file among all users. User-specific details are put into this shared text file. This file is then uploaded at overleaf or other projects. See https://blog.jabref.org/2025/12/04/overleaf-support/ for details.

I don't see how different BibTeX files (different users have different groupings) would work with overleaf-integration.

---

Maybe you could investigate JabRefOnline (https://github.com/JabRef/JabRefOnline), which has a different database schema and has more the thinking one user one bib file.

A possible next step here could be to work on JabRef / JabRefOnline sync.