#49 Matching schema upgrade version numbers to version dates

[KeviZ]

Hi,
I have a small home library instance running on BiblioteQ 20190315 version and its DB schema.
How could I best update schema to reflect most recent 20200909 updates and so could use the latest BiblioteQ version?
20200909 update schema sql file refers to version numbers instead of dates, I wonder which of those schema updates should I apply, and if you see any challenges going through the update route step by step.
I have minimal DB knowledge, but can follow clear directions of course with common sense.
Thank you for your kind help in advance,
keviz

[Guess Who]

Hi over there.

a. Produce a copy of your database and store it away.
b. Download the latest version of BQ from https://github.com/textbrowser/biblioteq/releases.
c. Launch BQ.
d. Open your database via BQ.
e. Options -> Upgrade SQLite Schema.

Please report your results.

[KeviZ]

Hi, thank you for quick reply, and apologies for me being silly...
The instance runs on the built in PostgreSQL server (9.3.25, cannot be changed) of my Synology NAS, for such scenario I have not seen schema upgrade option in BQ Options menu.
Any cure for such scenario?

[KeviZ]

What I did is I opened update_schema sql file of latest BQ from the SQL folder, but schema updates refer to version numbers (eg. 3.4, etc.) in the file, while BQ is versioned by dates, so do not know which version number equals to BQ version 20190315 in the file, to be able to know which sections I shall run for the existing DB...
When I ran all queries, some of them returned with error, and eventually some data was lost in between (eg. photos of covers got missed). When I connected to existing DB with latest BQ, it did not show up any items.
I do not have test environments and such, so trial and error would be quite time consuming...
I hope these details give more insight to my problem.

[Guess Who]

OK, you're using PQ. You should not have executed all of the queries. You should only execute queries which correspond to each BQ release. For example, if the release is 2019.01.01, execute the PQ statements for that release. Also, execute PQ statements which precede the current BQ release up to the PQ release which corresponds to your previous BQ version. Or, execute the deltas if you have deltas.

First: https://github.com/textbrowser/biblioteq/blob/master/SQL/README. Or, create a backup of your database. Backups are the responsibility of the operators.

The document https://github.com/textbrowser/biblioteq/blob/master/SQL/postgresql_update_schema.sql describes the PQ schema changes. Changes are dated except for really ancient changes. Ancient changes are version dated.

Good luck.

[KeviZ]

Thank you for the guidance, found and ran only the applicable schema update parts successfully, and now I can use and see my DB with latest BQ. Tons of gratitude and thanks!