Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add Schema in PostgreSQL DB #9312

Closed
robyquin opened this issue Oct 25, 2022 · 7 comments · Fixed by #9347
Closed

Add Schema in PostgreSQL DB #9312

robyquin opened this issue Oct 25, 2022 · 7 comments · Fixed by #9347

Comments

@robyquin
Copy link
Contributor

Is your suggestion for improvement related to a problem? Please describe.

I have only one postgreSQL database with a lot of tables in public schema.

In my opinion, JabRef's table names are very generic and it's easy to find another application that uses names like METADATA, ENTRY or FIELD.

Describe the solution you want

The easiest way to solve the problem is to add a suffix to the names, for example:
jabref_ENTRY, jabref_FIELD, jabref_METADATA.

In postgreSQL it is possible to create tables in a different schema, for example: jabref

In my case I prefer this

Additional context

In my local 5.7 release I made what I described with a few changes to the code.

If you think it will be useful, I can share the patch.

Greetings!

@Siedlerchr
Copy link
Member

Thank you very much! It would be great if you could create a PR

@koppor
Copy link
Member

koppor commented Oct 25, 2022

The code should also be able to migrate old data

@robyquin
Copy link
Contributor Author

The code should also be able to migrate old data

At the moment the automatic migration could be dangerous: in database there is not enough information to determine if ENTRY, FIELD and METADATA tables belong to an old installation or to another application.

Maybe it's worth migrating manually?

@k3KAW8Pnf7mkmdSMPHz27

This comment was marked as outdated.

@robyquin
Copy link
Contributor Author

Who chooses to use a database is a team, or a single user who needs to access the same library from different locations, but in fact can be considered a team with non-simultaneous access.

In this specific transition phase from 5.7 to the future release, it became clear that the TEAM will have to plan and agree on two interventions:

  1. Migration of data from the old to the new database configuration
  2. The TEAM will need to update JabRef to the latest version.

Data Migration

At the moment, manual migration is already possible with the tools available.

Export from JabRef v5.7

  • Select all entries
  • File -> Export -> Save selected as plain BibTeX...

export_bib_0

Upgrade JabRef

Stop using previous versions and encourage the switch to the new version.

IDEA: Using a key-value pair in the METADATA table indicating the library version. (metadataversion=1)

The problem should also be reported during the installation of the new release with a request to backup the library.

Import into new JabRef release from BibTeX

  • Connect to shared library
  • File -> Import -> Import into current library
  • Select all entries
  • Import entries

import_bib_0

import_bib_1

Advance Methods

Finally, there are certainly advanced methods, but it can be dangerous to implement them at this stage.

I think that only after key-value pair (metadataversion = 1) is consolidated, then something automatic can be implemented.

@robyquin
Copy link
Contributor Author

robyquin commented Nov 3, 2022

I'm ready

  • I extended the implementation of the new database structure on PGSQL and MYSQL
  • I have added automatic data migration functionality from JabRef 5.7 to the future version

Structure changes

  • PGSQL: I added jabref schema, leaving table names unchanged.
  • MYSQL/MARIADB: I added prefix JABREF_ to the tables.

In any case, fields of the tables have remained unchanged.

  • ORACLE: I tried to leave the code unchanged as much as possible, but I couldn't test it to confirm.

checkBaseIntegrity (VersionDBStructure)

To check the integrity of the DB and to implement the automatic migration, it was necessary to add the variable VersionDBStructure in the table METADATA which indicates the version of the structure (useful also in the future).

  • VersionDBStructure = 0: indicates the structure of the JabRef5.7 version
  • VersionDBStructure = 1: indicates the structure of the future Release

Automatic migration

By comparing the VersionDBStructure read in the DB and the desired one, the automatic migration is performed or not.

IMPORTANT: To avoid data loss, old tables will not be deleted during migration. Old tables will need to be deleted manually.

@robyquin
Copy link
Contributor Author

robyquin commented Nov 6, 2022

Automatic migration

flowchart TB

STATUS[Status: VersionDBRequired = 1] --> Check1
Check1{{ENTRY, FIELD, METADATA\ntables exist?}}
NewInstallation[VersionDBStructure==null\nThis is a new installation]
NoMigration([No Migration])
MigrationMysqlStep1([Create JABREF_ENTRY\nCreate JABREF_FILED\nCreate JABREF_METADATA])
MigrationPgsqlStep1([Create jabref.ENTRY\nCreate jabref.FILED\nCreate jabref.METADATA])
MigrationStep2([`Insert into newTable Select * from oldTable`])
Check2{{VersionDBStructure exists in DB?}}
CheckError{{Return Error?}}
NoCompatibility([There isn't compatibility between old and new structure])
Compatibility([OK, Well done!])
PrevVer([VersionDBStructure=0])
GetVersionValue([Get VersionDBStructure Value])
Check3{{VersionDBStructure < VersionDBRequired}}
Check4{{VersionDBStructure > VersionDBRequired}}

Check1 -->|NO| NewInstallation --> NoMigration
Check1 -->|YES| Check2
Check2 -->|NO| PrevVer
PrevVer -->|Try Migration MySQL| MigrationMysqlStep1
PrevVer -->|Try Migration PgSQL| MigrationPgsqlStep1
MigrationMysqlStep1 --> MigrationStep2
MigrationPgsqlStep1 --> MigrationStep2
MigrationStep2 --> CheckError
CheckError -->|YES| NoCompatibility
CheckError -->|NO| Compatibility
Check2 -->|YES| GetVersionValue
GetVersionValue --> Check3 -->|Perform Migration| Migration([Future Migrations])
GetVersionValue --> Check4 -->UpdateClient([Upgrade Client JabRef])
Loading

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

Successfully merging a pull request may close this issue.

4 participants